API tools faq
paste
zotov-vs

Untitled

zotov-vs
Aug 18th, 2020
138
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 16.45 KB | None | 0 0
raw download clone embed print report
  1. SET @ДатаОгрузки = DATE_ADD(NOW(),INTERVAL -1 DAY);
  2. SET @ДатаОгрузкиДляНомера = DATE_FORMAT(@ДатаОгрузки, '%y%m%d');
  3.  
  4. WITH
  5.  
  6. t1 AS  (
  7. SELECT
  8. ia.products_attributes_id
  9. , i.suppliers_id
  10. , CONCAT(@ДатаОгрузкиДляНомера, '-', IF(ii.is_agency = 'Y', 0, ii.suppliers_id)) AS incomes_number
  11. , COUNT(*) AS cnt    FROM marking_shelves AS ms
  12. JOIN marking_zones AS mz ON mz.marking_zones_id = ms.marking_zones_id
  13. JOIN entities_types AS et ON et.entities_types_id = mz.entities_types_id
  14. JOIN marking_shelves_to_objects AS mso ON mso.marking_shelves_id = ms.marking_shelves_id
  15. JOIN incomes_items_view_1c AS ii ON ii.incomes_items_id = mso.object_id
  16. JOIN incomes_attributes AS ia ON ia.incomes_attributes_id = ii.incomes_attributes_id
  17. JOIN incomes AS i ON i.incomes_id = ia.incomes_id
  18. WHERE mso.deleted = 0
  19. AND ms.marking_shelves_name IN(9, 10, 11, 12, 13)
  20. AND mz.marking_zones_id = 55 -- ПУ
  21.  
  22. GROUP BY ia.products_attributes_id, i.suppliers_id
  23.  
  24. )
  25. ,
  26. t2 AS (
  27. SELECT
  28.     IFNULL(
  29.             (SELECT GROUP_CONCAT(DISTINCT CONCAT('https://i.mamsy.ru/images/product_images/popup_images/', i.name))
  30.             FROM  products_images AS p_i
  31.                 JOIN images AS i ON i.images_id = p_i.images_id
  32.             WHERE p_i.products_attributes_id = pa.products_attributes_id
  33.             ), '') AS 'Фото'
  34.     , pa.products_name AS 'Наименование'
  35.     , pd.products_name_label AS 'НаименованиеСайт'
  36.     , IFNULL(pa.article_supplier,'')  AS 'НоменклатураПоставщика'
  37.     , IFNULL(p.products_model,'') AS 'Артикул'
  38.     , IFNULL(p31.products_options_values_name, '') AS 'ВКомплекте'
  39.     , IFNULL(b.brandswelove_name, '') AS 'НаименованиеБренда'
  40.     , IFNULL(p03.products_options_values_name, '') AS 'ЦветТорг12'
  41.     , IFNULL(p16.products_options_values_name, '') AS 'Цвет'
  42.     , IFNULL(p19.products_options_values_name, '') AS 'Объем'
  43.     , IFNULL(p15.products_options_values_name, '') AS 'РазмерЭтикетка'
  44.     , IFNULL(p01.products_options_values_name, '') AS 'RU'
  45.     , IFNULL(p11.products_options_values_name, '') AS 'Рост'
  46.     , IFNULL(p26.products_options_values_name, '') AS 'ДлинаСтельки'
  47.     , IFNULL(p25.products_options_values_name, '') AS 'ОбхватГоловы'
  48.     , IFNULL(p12.products_options_values_name, '') AS 'Возраст'
  49.     , IFNULL(p02.products_options_values_name, '') AS 'Пол'
  50.     , IFNULL(b.brandswelove_alt_text, '') AS 'Бренд'
  51.     , IFNULL(p.products_expiration_date, '') AS 'СрокГодности'
  52.     , IFNULL(p35.products_options_values_name, '-') AS 'ГодПроизводства'
  53.     , IFNULL(p27.products_options_values_name, '') AS 'СезонКоллекция'
  54.     , IFNULL((SELECT MAX(pcpc.name) FROM products_countries AS pcp
  55.             JOIN country AS pcpc ON pcpc.country_id = pcp.country_id
  56.             WHERE pcp.products_id = p.products_id AND pcp.type = 'design'),'')  AS 'СтранаДизайн'
  57.     , IFNULL((SELECT MAX(pcpc.name) FROM products_countries AS pcp
  58.             JOIN country AS pcpc ON pcpc.country_id = pcp.country_id
  59.             WHERE pcp.products_id = p.products_id AND pcp.type = 'production'),'') AS 'СтранаПроизводство'
  60.     , REGEXP_REPLACE(pd.products_description, '<\\/?\\w+>', '')  AS 'Описание'
  61.     , IFNULL(p17.products_options_values_name, '') AS 'Состав'
  62.     , IFNULL(p21.products_options_values_name, '') AS 'ОбхватТалии'
  63.     , IFNULL(p22.products_options_values_name, '') AS 'ОбхватБедер'
  64.     , IFNULL(p29.products_options_values_name, '') AS 'ОбхватГруди'
  65.     , IFNULL(p20.products_options_values_name, '') AS 'ДлинаИзделия'
  66.     , pa.products_weight AS 'ВесБрутто'
  67.     , IFNULL(p36.products_options_values_name, '') AS 'ВесИзделия'
  68.     , IFNULL(p28.products_options_values_name, '') AS 'РазмерИзделия'
  69.     , IFNULL(p.products_dimensions, '') AS 'Габариты'
  70.     , IFNULL((
  71.                 SELECT GROUP_CONCAT(DISTINCT br.barcode)
  72.                 FROM products_barcodes AS pb
  73.                     JOIN barcodes AS br ON br.barcodes_id = pb.barcodes_id
  74.                 WHERE pb.products_attributes_id = pa.products_attributes_id
  75.                 ), '') AS 'Штрихкод'
  76.     , '' AS 'НомерГТД' -- IFNULL(pdd.declaration, '') AS 'НомерГТД'
  77.     , p.products_quantity AS 'Резерв'
  78.     , p.products_quantity_min AS 'МинимальноеКоличество'
  79.     , p.products_quantity_max AS 'МаксимальноеКоличество'
  80.     , pp.products_original_purchase_price AS 'НачальнаяЗакупочнаяЦена'
  81.     , pp.products_trade_price AS 'ОптоваяЦена'
  82.     , IFNULL(IF(p.entities_types_id = 47
  83.             , (SELECT LAST_VALUE(ia.price_purchase) OVER (PARTITION BY ia.products_attributes_id ORDER BY ia.incomes_attributes_id)
  84.                 FROM incomes_attributes AS ia WHERE ia.products_attributes_id = pa.products_attributes_id
  85.                 GROUP BY ia.products_attributes_id
  86.             )
  87.             , pp.products_purchase_price)
  88.             , 1000000)  AS 'ЗакупочнаяЦена'
  89.     , pp.products_price AS 'ЦенаПродажи'
  90.     , pp.products_old_price  AS 'РыночнаяЦена'
  91.     , pp.products_minimal_price AS 'МинимальнаяЦена'
  92.     , pp.products_promo_price AS 'ПромоЦена'
  93.     , IFNULL(IF(p.entities_types_id = 47
  94.             , (SELECT LAST_VALUE(ia.vat) OVER (PARTITION BY ia.products_attributes_id ORDER BY ia.incomes_attributes_id)
  95.                 FROM incomes_attributes AS ia WHERE ia.products_attributes_id = pa.products_attributes_id
  96.                 GROUP BY ia.products_attributes_id
  97.             )
  98.             , pp.products_VAT)
  99.             , 20) AS 'НДС'
  100.     , IFNULL(IF(p.entities_types_id = 47
  101.             , (SELECT LAST_VALUE(ia.vat_sale) OVER (PARTITION BY ia.products_attributes_id ORDER BY ia.incomes_attributes_id)
  102.                 FROM incomes_attributes AS ia WHERE ia.products_attributes_id = pa.products_attributes_id
  103.                 GROUP BY ia.products_attributes_id
  104.             )
  105.             , pp.products_VAT_sale)
  106.             , 20) AS 'НДСПродажи'
  107.     , IFNULL(p.only_prepaid, 0) AS 'ТолькоПоПредоплате'
  108.     , IFNULL((SELECT
  109.                         GROUP_CONCAT(DISTINCT dt.delivery_type_name)
  110.                     FROM products_delivery_restrictions AS pdr
  111.                     LEFT JOIN delivery_types AS dt ON dt.delivery_type_id = pdr.delivery_type_id
  112.                     WHERE pdr.products_id = p.products_id
  113.                     ), '') AS 'ВариантДоставки'
  114.     , IFNULL(po.products_code,'') AS 'Код'
  115.     , IFNULL(po.products_comment, '') AS 'Коммент'
  116.     , IFNULL(p32.products_options_values_name, '') AS 'Плотность'
  117.     , IFNULL(p33.products_options_values_name, '') AS 'Диаметр'
  118.     , IFNULL(p.individual_order, 0) AS 'ОтдельныйЗаказ'
  119.     , IFNULL(p.no_return, 0) AS 'НеПодлежитВозврату'
  120.     , IFNULL(p.in_assortment, '') AS 'ВАссортименте'
  121.     , IFNULL(pao.certificate_details, '') AS 'РеквизитыСертификатаКачества'
  122.     , IFNULL(pao.manufacturer, '') AS 'Производитель'
  123.     , IFNULL(pao.manufacturer_address, '') AS 'АдресПроизводства'
  124.     , IFNULL(pao.need_re_photo, 0) AS 'НеобходимаФотосъемка'
  125.     , '' AS 'СтранаГТД' -- IFNULL(cc.code_alpha_3, '') AS 'СтранаГТД'
  126.     , cs.classifier_subcategories_name  AS 'ПодкатегорияТовара'
  127.     , clOrientation.classifier_lists_name AS 'Направленность'
  128.     , clAccessories.classifier_lists_name AS 'Принадлежность'
  129.     , po.hs_code AS 'HScode'
  130.     , po.loaded_sort AS 'ПорядокCтрок'
  131.     , pd.products_name_english AS 'НаименованиеEnglish'
  132.     , '-' AS 'СсылкиНаВнешниеЦены'
  133.     , pp.products_prepaid_discount AS 'СкидкаПриПредоплате'
  134.     , '-' AS 'КорзинаТовара'
  135.     , pa.products_attributes_id AS 'attribute_id'
  136.     , IFNULL(
  137.             (SELECT i.real_name
  138.             FROM  products_images AS p_i
  139.                 JOIN images AS i ON i.images_id = p_i.images_id
  140.             WHERE p_i.products_attributes_id = pa.products_attributes_id
  141.                 AND p_i.image_nr = 1
  142.             ), '') AS 'image_real'
  143.     , IFNULL(
  144.             (SELECT CONCAT('https://i.mamsy.ru/images/product_images/popup_images/', i.name)
  145.             FROM  products_images AS p_i
  146.                 JOIN images AS i ON i.images_id = p_i.images_id
  147.             WHERE p_i.products_attributes_id = pa.products_attributes_id
  148.                 AND p_i.image_nr = 1
  149.             ), '') AS 'ГлавноеФото'
  150.     , IF(pa.products_status IN(1,2,9), 0, 1)AS 'Выключить'
  151.     , '-' AS 'ЦенаПродажиВнешняя'
  152.     , '-' AS 'ЗачеркнутаяЦенаВнешняя'
  153.     , '-' AS 'ПромоЦенаВнешняя'
  154.     , '-' AS 'ПерсонализированнаяЦенаНелояльногоКлиентаВнешняя'
  155.     , pa.products_attributes_id
  156.     , IFNULL(pal.products_attributes_parent_id, pa.products_attributes_id) AS products_attributes_parent_id
  157.     , CASE
  158.             WHEN p.entities_types_id = 47 THEN 'Статичный'
  159.             WHEN p.entities_types_id = 46 THEN 'Акционный'
  160.             WHEN p.entities_types_id = 189 THEN 'Оффлайн'
  161.             WHEN p.entities_types_id = 262 THEN 'Виртуальный'
  162.             ELSE '¯\_(ツ)_/¯'
  163.         END AS 'ТипТовара'
  164.        
  165.    
  166.    
  167. FROM products_attributes AS pa
  168.     INNER JOIN t1 AS t1 ON t1.products_attributes_id = pa.products_attributes_id
  169.     INNER JOIN products AS p ON p.products_id = pa.products_id
  170.     INNER JOIN products_description AS pd ON pd.products_id = p.products_id
  171.     LEFT JOIN brands_we_love AS b ON b.brandswelove_id = p.brandswelove_id
  172.    
  173.     LEFT JOIN products_other AS po ON po.products_attributes_id = pa.products_attributes_id
  174.     LEFT JOIN products_attributes_other AS pao ON pao.products_attributes_id = pa.products_attributes_id
  175.    
  176.     LEFT JOIN products_attributes_legacy AS pal ON pal.products_attributes_id = pa.products_attributes_id
  177.  
  178.     LEFT JOIN products_prices AS pp ON pp.products_attributes_id = pa.products_attributes_id
  179. --  LEFT JOIN products_declarations AS pdd ON pdd.products_declarations_id = pp.products_declaration
  180. --  LEFT JOIN country AS cc ON cc.country_id = pdd.declaration_country_id
  181.    
  182.     LEFT JOIN classifier_binds_to_products_attributes AS cbpa ON cbpa.object_id = pa.products_attributes_id
  183.     LEFT JOIN classifier_subcategories AS cs ON cs.classifier_subcategories_id = cbpa.classifier_subcategories_id
  184.     LEFT JOIN classifier_lists AS clOrientation ON clOrientation.classifier_lists_id = cbpa.classifier_orientations_id
  185.     LEFT JOIN classifier_lists AS clAccessories ON clAccessories.classifier_lists_id = cbpa.classifier_accessories_id
  186.    
  187.     LEFT JOIN products_attributes_options AS pao01  ON pao01.products_attributes_id = pa.products_attributes_id AND pao01.products_options_id = 1  
  188.     LEFT JOIN products_options_values as p01 on p01.products_options_values_id = pao01.products_options_values_id
  189.     LEFT JOIN products_attributes_options AS pao02  ON pao02.products_attributes_id = pa.products_attributes_id AND pao02.products_options_id = 2  
  190.     LEFT JOIN products_options_values as p02 on p02.products_options_values_id = pao02.products_options_values_id
  191.     LEFT JOIN products_attributes_options AS pao03  ON pao03.products_attributes_id = pa.products_attributes_id AND pao03.products_options_id = 3  
  192.     LEFT JOIN products_options_values as p03 on p03.products_options_values_id = pao03.products_options_values_id
  193.     LEFT JOIN products_attributes_options AS pao11  ON pao11.products_attributes_id = pa.products_attributes_id AND pao11.products_options_id = 11
  194.     LEFT JOIN products_options_values as p11 on p11.products_options_values_id = pao11.products_options_values_id
  195.     LEFT JOIN products_attributes_options AS pao12  ON pao12.products_attributes_id = pa.products_attributes_id AND pao12.products_options_id = 12
  196.     LEFT JOIN products_options_values as p12 on p12.products_options_values_id = pao12.products_options_values_id
  197.     LEFT JOIN products_attributes_options AS pao15  ON pao15.products_attributes_id = pa.products_attributes_id AND pao15.products_options_id = 15
  198.     LEFT JOIN products_options_values as p15 on p15.products_options_values_id = pao15.products_options_values_id
  199.     LEFT JOIN products_attributes_options AS pao16  ON pao16.products_attributes_id = pa.products_attributes_id AND pao16.products_options_id = 16
  200.     LEFT JOIN products_options_values as p16 on p16.products_options_values_id = pao16.products_options_values_id
  201.     LEFT JOIN products_attributes_options AS pao17  ON pao17.products_attributes_id = pa.products_attributes_id AND pao17.products_options_id = 17
  202.     LEFT JOIN products_options_values as p17 on p17.products_options_values_id = pao17.products_options_values_id
  203.     LEFT JOIN products_attributes_options AS pao19  ON pao19.products_attributes_id = pa.products_attributes_id AND pao19.products_options_id = 19
  204.     LEFT JOIN products_options_values as p19 on p19.products_options_values_id = pao19.products_options_values_id
  205.     LEFT JOIN products_attributes_options AS pao20  ON pao20.products_attributes_id = pa.products_attributes_id AND pao20.products_options_id = 20
  206.     LEFT JOIN products_options_values as p20 on p20.products_options_values_id = pao20.products_options_values_id
  207.     LEFT JOIN products_attributes_options AS pao21  ON pao21.products_attributes_id = pa.products_attributes_id AND pao21.products_options_id = 21
  208.     LEFT JOIN products_options_values as p21 on p21.products_options_values_id = pao21.products_options_values_id
  209.     LEFT JOIN products_attributes_options AS pao22  ON pao22.products_attributes_id = pa.products_attributes_id AND pao22.products_options_id = 22
  210.     LEFT JOIN products_options_values as p22 on p22.products_options_values_id = pao22.products_options_values_id
  211.     LEFT JOIN products_attributes_options AS pao25  ON pao25.products_attributes_id = pa.products_attributes_id AND pao25.products_options_id = 25
  212.     LEFT JOIN products_options_values as p25 on p25.products_options_values_id = pao25.products_options_values_id
  213.     LEFT JOIN products_attributes_options AS pao26  ON pao26.products_attributes_id = pa.products_attributes_id AND pao26.products_options_id = 26
  214.     LEFT JOIN products_options_values as p26 on p26.products_options_values_id = pao26.products_options_values_id
  215.     LEFT JOIN products_attributes_options AS pao27  ON pao27.products_attributes_id = pa.products_attributes_id AND pao27.products_options_id = 27
  216.     LEFT JOIN products_options_values as p27 on p27.products_options_values_id = pao27.products_options_values_id
  217.     LEFT JOIN products_attributes_options AS pao28  ON pao28.products_attributes_id = pa.products_attributes_id AND pao28.products_options_id = 28
  218.     LEFT JOIN products_options_values as p28 on p28.products_options_values_id = pao28.products_options_values_id
  219.     LEFT JOIN products_attributes_options AS pao29  ON pao29.products_attributes_id = pa.products_attributes_id AND pao29.products_options_id = 29
  220.     LEFT JOIN products_options_values as p29 on p29.products_options_values_id = pao29.products_options_values_id
  221.     LEFT JOIN products_attributes_options AS pao31  ON pao31.products_attributes_id = pa.products_attributes_id AND pao31.products_options_id = 31
  222.     LEFT JOIN products_options_values as p31 on p31.products_options_values_id = pao31.products_options_values_id
  223.     LEFT JOIN products_attributes_options AS pao32  ON pao32.products_attributes_id = pa.products_attributes_id AND pao32.products_options_id = 32
  224.     LEFT JOIN products_options_values as p32 on p32.products_options_values_id = pao32.products_options_values_id
  225.     LEFT JOIN products_attributes_options AS pao33  ON pao33.products_attributes_id = pa.products_attributes_id AND pao33.products_options_id = 33
  226.     LEFT JOIN products_options_values as p33 on p33.products_options_values_id = pao33.products_options_values_id
  227.     LEFT JOIN products_attributes_options AS pao35  ON pao35.products_attributes_id = pa.products_attributes_id AND pao35.products_options_id = 35
  228.     LEFT JOIN products_options_values as p35 on p35.products_options_values_id = pao35.products_options_values_id
  229.     LEFT JOIN products_attributes_options AS pao36  ON pao36.products_attributes_id = pa.products_attributes_id AND pao36.products_options_id = 36
  230.     LEFT JOIN products_options_values as p36 on p36.products_options_values_id = pao36.products_options_values_id
  231.     LEFT JOIN products_attributes_options AS pao37  ON pao37.products_attributes_id = pa.products_attributes_id AND pao37.products_options_id = 37
  232.     LEFT JOIN products_options_values as p37 on p37.products_options_values_id = pao37.products_options_values_id
  233.     LEFT JOIN products_attributes_options AS pao38  ON pao38.products_attributes_id = pa.products_attributes_id AND pao38.products_options_id = 38
  234.     LEFT JOIN products_options_values as p38 on p38.products_options_values_id = pao38.products_options_values_id
  235. )  
  236.  
  237. SELECT
  238. t1.suppliers_id
  239. , t1.incomes_number
  240. , t2.*
  241. , SUM(t1.cnt) AS cnt
  242. FROM t2
  243. JOIN t1 ON t1.products_attributes_id = t2.products_attributes_id
  244. GROUP BY t1.suppliers_id, t2.products_attributes_parent_id, t1.incomes_number
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!