· 8 years ago · Jan 17, 2018, 10:16 AM
1DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_all_promotions`;
2CREATE TEMPORARY TABLE IF NOT EXISTS `cso_reports_store_promotions_all_promotions` ( promotion_id INT(11) NOT NULL, PRIMARY KEY (promotion_id) ) ENGINE = InnoDb;
3
4DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_items`;
5CREATE 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 = InnoDb ;
6
7DROP TEMPORARY TABLE IF EXISTS `cso_reports_store_promotions_retail_reduction` ;
8CREATE 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 = InnoDb ;
9
10INSERT 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);
11INSERT 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 ;
12
13INSERT `cso_reports_store_promotions_retail_reduction` ( upc, stationid, date, shift, promotionid, qty, amount )
14 SELECT SQL_NO_CACHE
15 pd.upc,
16 p.stationid,
17 p.date,
18 p.shift,
19 p.promotionid,
20 MAX(pd.Quantity) AS qty,
21 SUM(pd.Amount / pd.Quantity) AS amount
22 FROM pricechange p INNER JOIN pricechangedetailed pd ON pd.pricechangeid = p.id
23 INNER JOIN Stations st ON st.id = p.stationid AND st.userid = '894'
24 INNER JOIN PromotionsStations s ON s.stationid = st.Id AND s.promotionid = p.promotionid
25 INNER JOIN `cso_reports_store_promotions_items` i ON i.upc = pd.upc AND i.promotionid = p.promotionid
26 WHERE p.promotionid IN
27 ('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')
28 AND p.shift > 0 AND `p`.`date` >= '2017-12-16' AND `p`.`date` <= '2018-01-16' AND `st`.`id` IN ('6567')
29 GROUP BY p.StationId, pd.UPC, p.Date, p.shift, p.PromotionId;