az6SXMwt

1DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions` ;
2CREATE TEMPORARY TABLE `cso_reports_store_promotions` ( `id` INT NOT NULL AUTO_INCREMENT, `upc` BIGINT(15) NOT NULL DEFAULT 0, `name` VARCHAR(255) NOT NULL DEFAULT '', `promotionid` int(11) NOT NULL default '0', `stationid` int(11) NOT NULL default '0', `qty` int(11) NOT NULL default '0', `itemsqty` int(11) NOT NULL default '0', `cost` decimal(10,4) NOT NULL DEFAULT '0.0000', `gpmretail` DECIMAL(10,4) NOT NULL DEFAULT '0.0000', `amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00', `netpricechange` DECIMAL(10,4) NOT NULL DEFAULT '0.0000', `gpm` DECIMAL (6,2) NOT NULL DEFAULT '0.00', `profit` DECIMAL(10,2) NOT NULL DEFAULT '0.00', `costexceptions` int(11) NOT NULL default '0', `costexceptionsamount` DECIMAL (6,2) NOT NULL DEFAULT '0.00', `participation` DECIMAL (6,2) NOT NULL DEFAULT '0.00', `buydownbysales` DECIMAL(10,4) NOT NULL DEFAULT '0.0000', `buydownbypurch` DECIMAL(10,4) NOT NULL DEFAULT '0.0000', `type` INT NOT NULL DEFAULT 0, `report_subtotal_01` VARCHAR(10) NOT NULL DEFAULT '', INDEX `idx_report_subtotal_01` (`report_subtotal_01` ASC), `report_subtotal_02` INT NOT NULL DEFAULT 0, INDEX `idx_report_subtotal_02` (`report_subtotal_02` ASC), PRIMARY KEY (id), INDEX Find(stationid, upc, promotionid) ) ENGINE = MEMORY ;
3
4DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_retail_reduction` ;
5CREATE TEMPORARY TABLE `cso_reports_store_promotions_retail_reduction` ( `id` INT NOT NULL AUTO_INCREMENT, `upc` bigint(13) NOT NULL default '0', `shift` int(11) NOT NULL default '0', `date` date default '0000-00-00', `stationid` int(11) NOT NULL default '0', `promotionid` int(11) NOT NULL default '0', `qty` int(11) NOT NULL default '0', `amount` decimal(10,2) not null default '0.00', `price_each_mix_type` TINYINT(1) NOT NULL DEFAULT 0, `price_quantity` INT(2) NOT NULL DEFAULT 1, `total` decimal(10,2) not null default '0.00', `type` int NOT NULL DEFAULT 0, `report_subtotal_01` VARCHAR(10) NOT NULL DEFAULT '', INDEX `idx_report_subtotal_01` (`report_subtotal_01` ASC), `report_subtotal_02` INT NOT NULL DEFAULT 0, INDEX `idx_report_subtotal_02` (`report_subtotal_02` ASC), PRIMARY KEY (id) ) ENGINE = MEMORY ;
6
7DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_items`;
8CREATE TEMPORARY TABLE `cso_reports_store_promotions_items` ( promotionid int(11) NOT NULL default '0', upc bigint(15) NOT NULL DEFAULT 0, inventoryupc bigint(15), inventoryquantity int(5) default 1, type int NOT NULL DEFAULT 1, PRIMARY KEY (promotionid, upc, inventoryupc) ) ENGINE = MEMORY ;
9
10DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_purchases`;
11CREATE TEMPORARY TABLE `cso_reports_store_promotions_purchases` ( id int(11) NOT NULL AUTO_INCREMENT, promotionid INT NOT NULL default 0, upc BIGINT(15) NOT NULL DEFAULT 0, stationid int(11) default 0, stationname varchar(255) not null default '', vin varchar(11) DEFAULT '', date date default '0000-00-00', shift int(11) default 0, vendorid int(11) default 0, vendorname varchar(255) DEFAULT '', creationdate datetime DEFAULT '0000-00-00 00', invoiceid varchar(32) NOT NULL DEFAULT '0', qty int(11) default 0, cost decimal(10,4) DEFAULT '0.0000', negotiatedcost decimal(10,4) DEFAULT '0.0000', costreduction decimal(10,4) DEFAULT '0.0000', PRIMARY KEY (id) ) ENGINE = MEMORY ;
12
13DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_netpricechange`;
14CREATE TEMPORARY TABLE `cso_reports_store_promotions_netpricechange` ( promotionid INT NOT NULL default 0, stationid int(11) default 0, reduction decimal(10,4) DEFAULT '0.0000', PRIMARY KEY (promotionid, stationid) ) ENGINE = MEMORY ;
15
16DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_tmp`;
17CREATE TEMPORARY TABLE `cso_reports_store_promotions_tmp` ( id int(11) NOT NULL AUTO_INCREMENT, promotionid INT NOT NULL default 0, stationid int(11) default 0, reduction decimal(10,4) DEFAULT '0.0000', PRIMARY KEY (id) ) ENGINE = MEMORY ;
18
19DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_station_items`;
20CREATE TEMPORARY TABLE `cso_reports_store_promotions_station_items` ( upc BIGINT(15) NOT NULL DEFAULT 0, promotionid INT NOT NULL default 0, stationid INT(11) DEFAULT 0, PRIMARY KEY (upc, stationid, promotionid) ) ENGINE = MEMORY ;
21
22DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_particip`;
23CREATE TEMPORARY TABLE `cso_reports_store_promotions_particip` ( promotionid INT NOT NULL default 0, participation DECIMAL(10,2) NOT NULL default 0.00, PRIMARY KEY (promotionid) ) ENGINE = MEMORY;
24
25DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_all_promotions`;
26CREATE TEMPORARY TABLE IF NOT EXISTS `cso_reports_store_promotions_all_promotions` ( promotion_id INT(11) NOT NULL, PRIMARY KEY (promotion_id) ) ENGINE = MEMORY;
27
28DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_retail_on_date_price_each_mix`;
29CREATE TEMPORARY TABLE IF NOT EXISTS `cso_reports_store_promotions_retail_on_date_price_each_mix`( `upc` BIGINT(13) NOT NULL DEFAULT 0, `stationid` INT(11) NOT NULL DEFAULT '0', `date` DATE DEFAULT '0000-00-00', `retail` DECIMAL(10, 2) NOT NULL DEFAULT '0.00', PRIMARY KEY (upc,stationid,date) ) ENGINE = MEMORY;
30
31DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_max_station_logs`;
32CREATE TEMPORARY TABLE IF NOT EXISTS cso_reports_store_promotions_max_station_logs( `stationid` INT(11) NOT NULL DEFAULT '0', `upc` BIGINT(13) NOT NULL DEFAULT 0, `max_id` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (stationid, upc, max_id) ) ENGINE = MEMORY;
33
34DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_retail_on_date`;
35CREATE TEMPORARY TABLE IF NOT EXISTS `cso_reports_store_promotions_retail_on_date`( `stationid` INT(11) NOT NULL DEFAULT '0', `upc` BIGINT(13) NOT NULL DEFAULT 0, `date` DATE DEFAULT '0000-00-00', `pricequantity` INT(2) NOT NULL DEFAULT '1', `retail` DECIMAL(10, 2) NOT NULL DEFAULT '0.00', PRIMARY KEY (stationid, upc, date, pricequantity) ) ENGINE = MEMORY;
36
37DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_max_plu_batch_history`;
38CREATE TEMPORARY TABLE IF NOT EXISTS `cso_reports_store_promotions_max_plu_batch_history`( `stationid` INT(11) NOT NULL DEFAULT '0', `upc` BIGINT(13) NOT NULL DEFAULT 0, `pricequantity` INT(2) NOT NULL DEFAULT 1, `max_id` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (stationid, upc, pricequantity, max_id) ) ENGINE = MEMORY;
39
40DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_item_relation`;
41CREATE TEMPORARY TABLE IF NOT EXISTS `cso_reports_store_promotions_item_relation` ( `upc` BIGINT(15) NOT NULL DEFAULT 0, `related_upc` BIGINT(15) NOT NULL DEFAULT 0, PRIMARY KEY (upc,related_upc) ) ENGINE = MEMORY;
42
43INSERT INTO `cso_reports_store_promotions_all_promotions` (promotion_id) SELECT Id AS promotion_id FROM Promotions WHERE AccountId = 894 AND Name != '' AND Id IN(149103,215729,215731,296016,296021,553423,296019,296009,323650,295725,295799,295813,510595,422851,323798,327402,570163,295831,295849,295857,323074,325697,331161,340505,366849,387535,441077,382699,387533,387532,387547,477868,441081,382702,425652,493333,434524,381497,395972,432680,443401,443900,432917,443410,443890,465643,466249,429025,429024,297436,342365,311334,311323,311316,474343,266831,266833,389125,431993,266835,311317,311320,365628,269768,365629,266839,292768,444516,342378,295869,295872,387542,295865,266851,338290,266853,262265,316453,230596,294644,266856,308222,262777,264637,264641,264642,264646,264649,264650,272688,519895,298121,461905,461898,461894,461899,502619,388472,461847,461869,502618,388468,461855,595452,595425,461833,388469,461829,388466,502625,461841,461830,502628,595465,548245,461715,352744,461717,352746,461886,502637,595471,502636,461893,502642,461882,502639,461883,595479,548242,461711,461714,298956,298952,425569,527332,595418,425573,527335,595406,554262,469183,502186,573610,573616,573575,573601,572653,573556,573560,392551,388489,477005,271125,549318,477026,549322,388492,570228,549328,549321,570220,477014,534472,424777,477899,590346,585707,524182,393725,581382,395503,455938,422855,477889,523651,446219,502184,491492,513621,570204,577334,585706,473603,372283,269151,549264,253683,565440,549282,549277,425463,549273,549294,549291,549289,251561,549292,393716,393713,549301,401252,527871,570168,573625,570208,272672,359244,524284,560619,524281,425633,509152,572479,332465,462355,509155,429306,509158,572469,258760,462354,429307,592555,332479,592564,332481,521071,592567,592559,592570,592577,193621,521068,368526,592580,592574,432244,432621,432617,398249,398263,524307,577362,477856,444227,498941,485524,554511,498935,485527,486016,486049,584261,584264,503449,503464,350174,350172,592586,503467,592587,592588,503456,592593,592594,503473,592599,503470,503474,298923,298919,592603,503476,592604,592606,348728,592623,592616,593413,592608,592635,592610,350048,592618,350044,592629,348730,460827,460858,548166,456186,460818,332442,460855,332445,455821,425459,455817,425458,548182,460863,348726,460850,460833,460871,348722,548164,456181,592639,298941,592650,298945,592659,332451,592672,332455,592662,592677,592643,592654,547957,591940,435838,509185,592016,591963,509183,591972,478417,591995,435836,509188,591997,592001,509177,592004,478412,549375,477631,549384,477629,511960,477634,549388,477632,511970,549403,477628,511966,462361,361901,462365,365077,462367,365078,462371,361891,462373,361906,462379,365079,462389,365081,462393,361894,477106,462439,477107,462397,549379,462399,477110,477113,462455,554329,549385,477118,462445,549394,549405,477119,462405,477124,462458,549408,462429,477125,477643,549412,462463,477130,478088,549411,549421,477640,511961,549441,462462,477133,478081,511964,549430,549432,477638,511967,549436,477646,511975,549433,549418,549439,148421,586637,478424,478433,557844,509173,557824,509171,478421,478427,557854,509164,557832,509161,460874,400155,460883,400164,460902,400214,460913,400228,592872,554776,503572,592885,503567,554805,554833,554826,460925,554835,503521,503519,460882,471999,592843,554850,592864,460943,503488,554847,592865,554871,592866,460931,554857,592902,503603,554877,503494,460895,472002,592869,554772,592851,592879,554815,554841,592889,554883,593012,503585,554910,593431,503584,554914,593041,554938,554925,460929,554947,503557,503525,460911,472007,592926,554967,554965,460945,503489,592956,554976,554973,460937,554958,503609,503498,460922,472010,592992,592980,554959,593004,554911,592929,592971,593024,554919,554950,475877,558639,502468,502487,461813,461799,595393,502489,549303,549306,549223,461791,461809,332429,332431,502469,502544,564159,502541,325438,298973,298975,332435,332439,298977,502462,502492,461783,461771,595377,502495,549309,549313,549217,461765,461779,298984,298980,513647,502463,502549,564121,502499,502477,502564,390511,340724,502568,502478,502472,298991,502475,502555,390507,502558,425658,509506,478976,461754,502607,332473,332471,502609,548274,461758,461749,502601,298964,298960,502604,548268,461753,425636,579725,425635,524263,444232,524269,524266,477928,524256,560590,524251,425630,474346,435072,524262,560614,560607,524259,477796,428152,498421,586761,586639,586641,425621,444217,560692,354525,266857,266858,280228,440428,440430,552592,550194,552595,550182,332483,581436,461735,456377,461738,456379,139951,524311,425655,570213,480413,477029,420572,477032,420575,477049,365086,228860,365088,503434,371308,503425,371304,503437,477055,420578,477052,420576,570216,477058,394073,477871,577323,524287,477814,577328,524289,477817,577331,511085,472013,471993,485494,485500,388508,456578,456212,518143,144420,332466,332467,251542,251546);
44INSERT `cso_reports_store_promotions_particip` (promotionid, participation) VALUES ('149103', '0'),('215729', '100'),('215731', '100'),('296016', '96.3'),('296021', '96.3'),('553423', '100'),('296019', '96.3'),('296009', '96.3'),('323650', '100'),('295725', '96.3'),('295799', '96.3'),('295813', '96.3'),('510595', '100'),('422851', '100'),('323798', '100'),('327402', '100'),('570163', '100'),('295831', '96.3'),('295849', '96.3'),('295857', '96.3'),('323074', '100'),('325697', '100'),('331161', '100'),('340505', '100'),('366849', '100'),('387535', '100'),('441077', '100'),('382699', '100'),('387533', '100'),('387532', '100'),('387547', '100'),('477868', '100'),('441081', '100'),('382702', '100'),('425652', '100'),('493333', '100'),('434524', '100'),('381497', '100'),('395972', '100'),('432680', '100'),('443401', '100'),('443900', '100'),('432917', '100'),('443410', '100'),('443890', '100'),('465643', '100'),('466249', '100'),('429025', '100'),('429024', '100'),('297436', '100'),('342365', '96.3'),('311334', '100'),('311323', '100'),('311316', '0'),('474343', '100'),('266831', '94.55'),('266833', '94.55'),('389125', '100'),('431993', '100'),('266835', '94.55'),('311317', '100'),('311320', '100'),('365628', '100'),('269768', '100'),('365629', '100'),('266839', '94.44'),('292768', '91.23'),('444516', '100'),('342378', '100'),('295869', '96.3'),('295872', '96.3'),('387542', '100'),('295865', '100'),('266851', '94.55'),('338290', '100'),('266853', '94.55'),('262265', '100'),('316453', '100'),('230596', '96.43'),('294644', '96.3'),('266856', '94.55'),('308222', '96.43'),('262777', '91.53'),('264637', '91.53'),('264641', '91.53'),('264642', '91.53'),('264646', '91.53'),('264649', '91.53'),('264650', '91.53'),('272688', '92.86'),('519895', '100'),('298121', '100'),('461905', '100'),('461898', '100'),('461894', '100'),('461899', '100'),('502619', '100'),('388472', '100'),('461847', '100'),('461869', '100'),('502618', '100'),('388468', '100'),('461855', '100'),('595452', '100'),('595425', '100'),('461833', '100'),('388469', '100'),('461829', '100'),('388466', '100'),('502625', '100'),('461841', '100'),('461830', '100'),('502628', '100'),('595465', '100'),('548245', '100'),('461715', '100'),('352744', '100'),('461717', '100'),('352746', '100'),('461886', '100'),('502637', '100'),('595471', '100'),('502636', '100'),('461893', '100'),('502642', '100'),('461882', '100'),('502639', '100'),('461883', '100'),('595479', '100'),('548242', '100'),('461711', '100'),('461714', '100'),('298956', '100'),('298952', '96.36'),('425569', '100'),('527332', '100'),('595418', '100'),('425573', '100'),('527335', '100'),('595406', '100'),('554262', '100'),('469183', '100'),('502186', '100'),('573610', '100'),('573616', '100'),('573575', '100'),('573601', '100'),('572653', '100'),('573556', '100'),('573560', '100'),('392551', '100'),('388489', '100'),('477005', '100'),('271125', '100'),('549318', '100'),('477026', '100'),('549322', '100'),('388492', '100'),('570228', '100'),('549328', '100'),('549321', '100'),('570220', '100'),('477014', '100'),('534472', '100'),('424777', '100'),('477899', '100'),('590346', '100'),('585707', '98.18'),('524182', '100'),('393725', '100'),('581382', '100'),('395503', '100'),('455938', '100'),('422855', '100'),('477889', '100'),('523651', '100'),('446219', '100'),('502184', '100'),('491492', '100'),('513621', '100'),('570204', '100'),('577334', '100'),('585706', '98.18'),('473603', '100'),('372283', '100'),('269151', '96.36'),('549264', '100'),('253683', '100'),('565440', '100'),('549282', '100'),('549277', '100'),('425463', '100'),('549273', '100'),('549294', '100'),('549291', '100'),('549289', '100'),('251561', '85.48'),('549292', '100'),('393716', '100'),('393713', '100'),('549301', '100'),('401252', '100'),('527871', '100'),('570168', '100'),('573625', '100'),('570208', '100'),('272672', '100'),('359244', '100'),('524284', '100'),('560619', '100'),('524281', '100'),('425633', '100'),('509152', '100'),('572479', '100'),('332465', '100'),('462355', '100'),('509155', '100'),('429306', '100'),('509158', '100'),('572469', '100'),('258760', '100'),('462354', '100'),('429307', '100'),('592555', '100'),('332479', '100'),('592564', '100'),('332481', '100'),('521071', '100'),('592567', '100'),('592559', '100'),('592570', '100'),('592577', '100'),('193621', '100'),('521068', '100'),('368526', '100'),('592580', '100'),('592574', '100'),('432244', '100'),('432621', '100'),('432617', '100'),('398249', '100'),('398263', '100'),('524307', '100'),('577362', '100'),('477856', '100'),('444227', '100'),('498941', '100'),('485524', '100'),('554511', '100'),('498935', '100'),('485527', '100'),('486016', '100'),('486049', '100'),('584261', '100'),('584264', '100'),('503449', '100'),('503464', '100'),('350174', '100'),('350172', '100'),('592586', '100'),('503467', '100'),('592587', '100'),('592588', '100'),('503456', '100'),('592593', '100'),('592594', '100'),('503473', '100'),('592599', '100'),('503470', '100'),('503474', '100'),('298923', '100'),('298919', '96.36'),('592603', '100'),('503476', '100'),('592604', '100'),('592606', '100'),('348728', '100'),('592623', '100'),('592616', '100'),('593413', '100'),('592608', '100'),('592635', '100'),('592610', '100'),('350048', '100'),('592618', '100'),('350044', '100'),('592629', '100'),('348730', '100'),('460827', '100'),('460858', '100'),('548166', '100'),('456186', '100'),('460818', '100'),('332442', '100'),('460855', '100'),('332445', '100'),('455821', '100'),('425459', '100'),('455817', '100'),('425458', '100'),('548182', '100'),('460863', '100'),('348726', '100'),('460850', '100'),('460833', '100'),('460871', '100'),('348722', '100'),('548164', '100'),('456181', '100'),('592639', '100'),('298941', '100'),('592650', '100'),('298945', '96.36'),('592659', '100'),('332451', '100'),('592672', '100'),('332455', '100'),('592662', '100'),('592677', '100'),('592643', '100'),('592654', '100'),('547957', '100'),('591940', '100'),('435838', '100'),('509185', '100'),('592016', '100'),('591963', '100'),('509183', '100'),('591972', '100'),('478417', '100'),('591995', '100'),('435836', '100'),('509188', '100'),('591997', '100'),('592001', '100'),('509177', '100'),('592004', '100'),('478412', '100'),('549375', '100'),('477631', '100'),('549384', '100'),('477629', '100'),('511960', '100'),('477634', '0'),('549388', '100'),('477632', '100'),('511970', '100'),('549403', '100'),('477628', '100'),('511966', '100'),('462361', '100'),('361901', '100'),('462365', '100'),('365077', '100'),('462367', '100'),('365078', '100'),('462371', '100'),('361891', '100'),('462373', '100'),('361906', '100'),('462379', '100'),('365079', '100'),('462389', '100'),('365081', '100'),('462393', '100'),('361894', '100'),('477106', '100'),('462439', '100'),('477107', '100'),('462397', '100'),('549379', '100'),('462399', '100'),('477110', '100'),('477113', '100'),('462455', '100'),('554329', '100'),('549385', '100'),('477118', '100'),('462445', '100'),('549394', '100'),('549405', '100'),('477119', '100'),('462405', '100'),('477124', '100'),('462458', '100'),('549408', '100'),('462429', '100'),('477125', '100'),('477643', '100'),('549412', '100'),('462463', '100'),('477130', '100'),('478088', '100'),('549411', '100'),('549421', '100'),('477640', '100'),('511961', '100'),('549441', '100'),('462462', '100'),('477133', '100'),('478081', '100'),('511964', '100'),('549430', '100'),('549432', '100'),('477638', '100'),('511967', '100'),('549436', '100'),('477646', '100'),('511975', '100'),('549433', '100'),('549418', '100'),('549439', '100'),('148421', '0'),('586637', '100'),('478424', '100'),('478433', '100'),('557844', '100'),('509173', '100'),('557824', '100'),('509171', '100'),('478421', '100'),('478427', '100'),('557854', '100'),('509164', '100'),('557832', '100'),('509161', '100'),('460874', '100'),('400155', '100'),('460883', '100'),('400164', '100'),('460902', '100'),('400214', '100'),('460913', '100'),('400228', '100'),('592872', '100'),('554776', '100'),('503572', '100'),('592885', '100'),('503567', '100'),('554805', '100'),('554833', '100'),('554826', '100'),('460925', '100'),('554835', '100'),('503521', '100'),('503519', '100'),('460882', '100'),('471999', '100'),('592843', '100'),('554850', '100'),('592864', '100'),('460943', '100'),('503488', '100'),('554847', '100'),('592865', '100'),('554871', '100'),('592866', '100'),('460931', '100'),('554857', '100'),('592902', '100'),('503603', '100'),('554877', '100'),('503494', '100'),('460895', '100'),('472002', '100'),('592869', '100'),('554772', '100'),('592851', '100'),('592879', '100'),('554815', '100'),('554841', '100'),('592889', '100'),('554883', '100'),('593012', '100'),('503585', '100'),('554910', '100'),('593431', '100'),('503584', '100'),('554914', '100'),('593041', '100'),('554938', '100'),('554925', '100'),('460929', '100'),('554947', '100'),('503557', '100'),('503525', '100'),('460911', '100'),('472007', '100'),('592926', '100'),('554967', '100'),('554965', '100'),('460945', '100'),('503489', '100'),('592956', '100'),('554976', '100'),('554973', '100'),('460937', '100'),('554958', '100'),('503609', '100'),('503498', '100'),('460922', '100'),('472010', '100'),('592992', '100'),('592980', '100'),('554959', '100'),('593004', '100'),('554911', '100'),('592929', '100'),('592971', '100'),('593024', '100'),('554919', '100'),('554950', '100'),('475877', '100'),('558639', '100'),('502468', '100'),('502487', '100'),('461813', '100'),('461799', '100'),('595393', '100'),('502489', '100'),('549303', '100'),('549306', '100'),('549223', '100'),('461791', '100'),('461809', '100'),('332429', '100'),('332431', '100'),('502469', '100'),('502544', '100'),('564159', '100'),('502541', '100'),('325438', '100'),('298973', '100'),('298975', '100'),('332435', '100'),('332439', '100'),('298977', '100'),('502462', '100'),('502492', '100'),('461783', '100'),('461771', '100'),('595377', '100'),('502495', '100'),('549309', '100'),('549313', '100'),('549217', '100'),('461765', '100'),('461779', '100'),('298984', '100'),('298980', '100'),('513647', '100'),('502463', '100'),('502549', '100'),('564121', '100'),('502499', '100'),('502477', '100'),('502564', '100'),('390511', '100'),('340724', '100'),('502568', '100'),('502478', '100'),('502472', '100'),('298991', '100'),('502475', '100'),('502555', '100'),('390507', '100'),('502558', '100'),('425658', '100'),('509506', '100'),('478976', '100'),('461754', '100'),('502607', '100'),('332473', '100'),('332471', '100'),('502609', '100'),('548274', '100'),('461758', '100'),('461749', '100'),('502601', '100'),('298964', '100'),('298960', '96.36'),('502604', '100'),('548268', '100'),('461753', '100'),('425636', '100'),('579725', '100'),('425635', '100'),('524263', '100'),('444232', '100'),('524269', '100'),('524266', '100'),('477928', '100'),('524256', '100'),('560590', '100'),('524251', '100'),('425630', '100'),('474346', '100'),('435072', '100'),('524262', '100'),('560614', '100'),('560607', '100'),('524259', '100'),('477796', '100'),('428152', '100'),('498421', '100'),('586761', '100'),('586639', '100'),('586641', '100'),('425621', '100'),('444217', '100'),('560692', '100'),('354525', '100'),('266857', '94.55'),('266858', '94.74'),('280228', '96.36'),('440428', '100'),('440430', '100'),('552592', '100'),('550194', '100'),('552595', '100'),('550182', '100'),('332483', '100'),('581436', '100'),('461735', '100'),('456377', '100'),('461738', '100'),('456379', '100'),('139951', '0'),('524311', '100'),('425655', '100'),('570213', '100'),('480413', '100'),('477029', '100'),('420572', '100'),('477032', '100'),('420575', '100'),('477049', '100'),('365086', '100'),('228860', '98.15'),('365088', '100'),('503434', '100'),('371308', '100'),('503425', '100'),('371304', '100'),('503437', '100'),('477055', '100'),('420578', '100'),('477052', '100'),('420576', '100'),('570216', '100'),('477058', '100'),('394073', '100'),('477871', '100'),('577323', '100'),('524287', '100'),('477814', '100'),('577328', '100'),('524289', '100'),('477817', '100'),('577331', '100'),('511085', '100'),('472013', '100'),('471993', '100'),('485494', '100'),('485500', '100'),('388508', '100'),('456578', '100'),('456212', '100'),('518143', '100'),('144420', '0'),('332466', '100'),('332467', '100'),('251542', '100'),('251546', '100');
45
46INSERT IGNORE INTO `cso_reports_store_promotions_items` (promotionid, upc, inventoryquantity) SELECT pi.PromotionId, pi.UPC AS upc, 1 FROM PromotionsItems pi INNER JOIN `cso_reports_store_promotions_all_promotions` ap ON ap.promotion_id = pi.PromotionId ;
47
48
49
50INSERT `cso_reports_store_promotions_retail_reduction` ( upc, stationid, date, shift, promotionid, qty, amount )
51  SELECT SQL_NO_CACHE
52    pd.upc,
53    p.stationid,
54    p.date,
55    p.shift,
56    p.promotionid,
57    MAX(pd.Quantity)             AS qty,
58    SUM(pd.Amount / pd.Quantity) AS amount
59  FROM pricechange p INNER JOIN pricechangedetailed pd ON pd.pricechangeid = p.id
60    INNER JOIN Stations st ON st.id = p.stationid AND st.userid = '894'
61    INNER JOIN PromotionsStations s ON s.stationid = st.Id AND s.promotionid = p.promotionid
62    INNER JOIN `cso_reports_store_promotions_items` i ON i.upc = pd.upc AND i.promotionid = p.promotionid
63  WHERE p.promotionid IN
64        ('149103', '215729', '215731', '296016', '296021', '553423', '296019', '296009', '323650', '295725', '295799', '295813', '510595', '422851', '323798', '327402', '570163', '295831', '295849', '295857', '323074', '325697', '331161', '340505', '366849', '387535', '441077', '382699', '387533', '387532', '387547', '477868', '441081', '382702', '425652', '493333', '434524', '381497', '395972', '432680', '443401', '443900', '432917', '443410', '443890', '465643', '466249', '429025', '429024', '297436', '342365', '311334', '311323', '311316', '474343', '266831', '266833', '389125', '431993', '266835', '311317', '311320', '365628', '269768', '365629', '266839', '292768', '444516', '342378', '295869', '295872', '387542', '295865', '266851', '338290', '266853', '262265', '316453', '230596', '294644', '266856', '308222', '262777', '264637', '264641', '264642', '264646', '264649', '264650', '272688', '519895', '298121', '461905', '461898', '461894', '461899', '502619', '388472', '461847', '461869', '502618', '388468', '461855', '595452', '595425', '461833', '388469', '461829', '388466', '502625', '461841', '461830', '502628', '595465', '548245', '461715', '352744', '461717', '352746', '461886', '502637', '595471', '502636', '461893', '502642', '461882', '502639', '461883', '595479', '548242', '461711', '461714', '298956', '298952', '425569', '527332', '595418', '425573', '527335', '595406', '554262', '469183', '502186', '573610', '573616', '573575', '573601', '572653', '573556', '573560', '392551', '388489', '477005', '271125', '549318', '477026', '549322', '388492', '570228', '549328', '549321', '570220', '477014', '534472', '424777', '477899', '590346', '585707', '524182', '393725', '581382', '395503', '455938', '422855', '477889', '523651', '446219', '502184', '491492', '513621', '570204', '577334', '585706', '473603', '372283', '269151', '549264', '253683', '565440', '549282', '549277', '425463', '549273', '549294', '549291', '549289', '251561', '549292', '393716', '393713', '549301', '401252', '527871', '570168', '573625', '570208', '272672', '359244', '524284', '560619', '524281', '425633', '509152', '572479', '332465', '462355', '509155', '429306', '509158', '572469', '258760', '462354', '429307', '592555', '332479', '592564', '332481', '521071', '592567', '592559', '592570', '592577', '193621', '521068', '368526', '592580', '592574', '432244', '432621', '432617', '398249', '398263', '524307', '577362', '477856', '444227', '498941', '485524', '554511', '498935', '485527', '486016', '486049', '584261', '584264', '503449', '503464', '350174', '350172', '592586', '503467', '592587', '592588', '503456', '592593', '592594', '503473', '592599', '503470', '503474', '298923', '298919', '592603', '503476', '592604', '592606', '348728', '592623', '592616', '593413', '592608', '592635', '592610', '350048', '592618', '350044', '592629', '348730', '460827', '460858', '548166', '456186', '460818', '332442', '460855', '332445', '455821', '425459', '455817', '425458', '548182', '460863', '348726', '460850', '460833', '460871', '348722', '548164', '456181', '592639', '298941', '592650', '298945', '592659', '332451', '592672', '332455', '592662', '592677', '592643', '592654', '547957', '591940', '435838', '509185', '592016', '591963', '509183', '591972', '478417', '591995', '435836', '509188', '591997', '592001', '509177', '592004', '478412', '549375', '477631', '549384', '477629', '511960', '477634', '549388', '477632', '511970', '549403', '477628', '511966', '462361', '361901', '462365', '365077', '462367', '365078', '462371', '361891', '462373', '361906', '462379', '365079', '462389', '365081', '462393', '361894', '477106', '462439', '477107', '462397', '549379', '462399', '477110', '477113', '462455', '554329', '549385', '477118', '462445', '549394', '549405', '477119', '462405', '477124', '462458', '549408', '462429', '477125', '477643', '549412', '462463', '477130', '478088', '549411', '549421', '477640', '511961', '549441', '462462', '477133', '478081', '511964', '549430', '549432', '477638', '511967', '549436', '477646', '511975', '549433', '549418', '549439', '148421', '586637', '478424', '478433', '557844', '509173', '557824', '509171', '478421', '478427', '557854', '509164', '557832', '509161', '460874', '400155', '460883', '400164', '460902', '400214', '460913', '400228', '592872', '554776', '503572', '592885', '503567', '554805', '554833', '554826', '460925', '554835', '503521', '503519', '460882', '471999', '592843', '554850', '592864', '460943', '503488', '554847', '592865', '554871', '592866', '460931', '554857', '592902', '503603', '554877', '503494', '460895', '472002', '592869', '554772', '592851', '592879', '554815', '554841', '592889', '554883', '593012', '503585', '554910', '593431', '503584', '554914', '593041', '554938', '554925', '460929', '554947', '503557', '503525', '460911', '472007', '592926', '554967', '554965', '460945', '503489', '592956', '554976', '554973', '460937', '554958', '503609', '503498', '460922', '472010', '592992', '592980', '554959', '593004', '554911', '592929', '592971', '593024', '554919', '554950', '475877', '558639', '502468', '502487', '461813', '461799', '595393', '502489', '549303', '549306', '549223', '461791', '461809', '332429', '332431', '502469', '502544', '564159', '502541', '325438', '298973', '298975', '332435', '332439', '298977', '502462', '502492', '461783', '461771', '595377', '502495', '549309', '549313', '549217', '461765', '461779', '298984', '298980', '513647', '502463', '502549', '564121', '502499', '502477', '502564', '390511', '340724', '502568', '502478', '502472', '298991', '502475', '502555', '390507', '502558', '425658', '509506', '478976', '461754', '502607', '332473', '332471', '502609', '548274', '461758', '461749', '502601', '298964', '298960', '502604', '548268', '461753', '425636', '579725', '425635', '524263', '444232', '524269', '524266', '477928', '524256', '560590', '524251', '425630', '474346', '435072', '524262', '560614', '560607', '524259', '477796', '428152', '498421', '586761', '586639', '586641', '425621', '444217', '560692', '354525', '266857', '266858', '280228', '440428', '440430', '552592', '550194', '552595', '550182', '332483', '581436', '461735', '456377', '461738', '456379', '139951', '524311', '425655', '570213', '480413', '477029', '420572', '477032', '420575', '477049', '365086', '228860', '365088', '503434', '371308', '503425', '371304', '503437', '477055', '420578', '477052', '420576', '570216', '477058', '394073', '477871', '577323', '524287', '477814', '577328', '524289', '477817', '577331', '511085', '472013', '471993', '485494', '485500', '388508', '456578', '456212', '518143', '144420', '332466', '332467', '251542', '251546')
65        AND p.shift > 0 AND `p`.`date` >= '2017-12-16' AND `p`.`date` <= '2018-01-16' AND `st`.`id` IN ('6567')
66  GROUP BY p.StationId, pd.UPC, p.Date, p.shift, p.PromotionId;