SHARE
TWEET

Untitled

a guest Jan 22nd, 2019 97 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Очистка prices_all от соединений к старому TD
  2. /*
  3. UPDATE prices_all
  4. SET pa_art_id = NULL, pa_ga_id = NULL;
  5. */
  6.  
  7. -- Запрос на добавление id нашего каталога к запчастям из прайсов
  8. UPDATE prices_all
  9. LEFT JOIN tecdoc_2014_2q_europe.ART_LOOKUP ON ART_LOOKUP.ARL_SEARCH_NUMBER = prices_all.art_num
  10. LEFT JOIN tecdoc_2014_2q_europe.ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
  11. LEFT JOIN brand_groups ON brand_groups.BRA_ID = ARTICLES.ART_SUP_ID
  12. LEFT JOIN tecdoc_2014_2q_europe.LINK_ART_GA ON ARTICLES.ART_ID = LINK_ART_GA.LAG_ART_ID
  13. INNER JOIN dok_ga ON dok_ga.GA_ID = LINK_ART_GA.LAG_GA_ID
  14. INNER JOIN dok_razdel ON dok_ga.parent_id = dok_razdel.id
  15. SET prices_all.pa_art_id = ARTICLES.ART_ID, prices_all.pa_ga_id = dok_ga.GA_ID, prices_all.id_dok_izdelie = dok_razdel.id
  16. WHERE ART_LOOKUP.ARL_KIND = 1 AND prices_all.brand_id = brand_groups.parent_id;
  17. -- ********************************************************************
  18.  
  19.  
  20.  
  21. -- Обновление seo структуры автомобилей и изделий для них
  22.  
  23. /* Структура авто и изделий в seo */
  24. /*
  25. TRUNCATE TABLE seo_available;
  26. INSERT INTO seo_available (SA_izdelie_id, SA_marka_id, SA_model_id)
  27. SELECT
  28. seo_izdelie.id,
  29. seo_marki.id,
  30. seo_model.id
  31. FROM seo_izdelie
  32. CROSS JOIN seo_model
  33. INNER JOIN seo_marki ON seo_model.parent_id = seo_marki.id
  34. WHERE seo_izdelie.dok_razdel_id IS NOT NULL
  35. ORDER BY seo_izdelie.id, seo_marki.id, seo_model.id;
  36. */
  37.  
  38.  
  39. -- Расчет цен по медиане и их запись в prices_all.id
  40.  
  41. /* Очистка и заполнение промежуточной таблицы price_median */
  42. TRUNCATE TABLE prices_median;
  43. INSERT INTO prices_median (art_num, brand_id, row_number, hash_art, sup_pattern_id, price)
  44. SELECT
  45. d.art_num,
  46. d.brand_id,
  47. IF(@prev=d.hash_art, @rownum:=@rownum+1, @rownum:=1) AS row_number,
  48. @prev:=d.hash_art,
  49. d.supplier,
  50. d.price
  51. FROM
  52.                                (SELECT
  53.                                sp.hash_brand_artnum AS hash_art,
  54.                                sp.art_num,
  55.                                sp.brand_id,
  56.                                sp.supplier,
  57.                                sp.price
  58.                                FROM supplier_price as sp
  59.                                LEFT JOIN supplier_price_avalaible as spa ON sp.`hash` = spa.`hash`
  60.                                                              LEFT JOIN supplier_pattern spat ON spa.price_id = spat.id
  61.                                                              LEFT JOIN supplier_storage ss ON spa.storage_id = ss.id
  62.                                WHERE spa.avalaible > 0 AND spat.active = 1 AND sp.brand_id IS NOT NULL AND ss.no_active_price IS NULL
  63.                                GROUP BY hash_art, sp.supplier
  64.                                ORDER BY hash_art, price) AS d,
  65.                 (SELECT @rownum:=0, @prev:=NULL) r;
  66.  
  67.  
  68.  
  69. /* Обновление срдених цен в prices_all на основе медианы */
  70. UPDATE
  71. prices_all AS pac,
  72. (SELECT
  73. res.art_num,
  74. res.brand_id,
  75. CAST(CASE
  76. WHEN res.total_rows = 1 THEN res.price
  77. WHEN res.total_rows = 2 AND res.price < 300 THEN IF((MAX(res.price)-MIN(res.price))*100/MIN(res.price) < 40, AVG(res.price), MIN(res.price))
  78. WHEN res.total_rows = 2 AND (res.price BETWEEN 300 AND 800) THEN IF((MAX(res.price)-MIN(res.price))*100/MIN(res.price) < 20, AVG(res.price), MIN(res.price))
  79. WHEN res.total_rows = 2 AND res.price > 800 THEN IF((MAX(res.price)-MIN(res.price))*100/MIN(res.price) < 15, AVG(res.price), MIN(res.price))
  80. WHEN res.total_rows > 2 THEN AVG(res.price)
  81. END AS DECIMAL(10,2)) AS median_price
  82. FROM                  
  83. (
  84.                                SELECT t1.row_number, t1.hash_art, t1.art_num, t1.brand_id, t1.price, t2.total_rows
  85.                                FROM prices_median AS t1
  86.                                LEFT JOIN
  87.                                                                                               (
  88.                                                                               SELECT count(*) AS total_rows, d.hash_art
  89.                                                                               FROM prices_median AS d
  90.                                                                               GROUP BY d.hash_art
  91.                                                                                               ) AS t2
  92.                                ON t1.hash_art = t2.hash_art
  93.                                WHERE 1=1
  94.                                AND t1.row_number>=t2.total_rows/2 AND t1.row_number<=t2.total_rows/2+1
  95. ) AS res
  96. WHERE res.total_rows > 0
  97. GROUP BY res.hash_art) AS source
  98. SET pac.price_sredn = source.median_price
  99. WHERE source.art_num = pac.art_num AND source.brand_id = pac.brand_id;
  100.  
  101. /* Запись торговых цен в prices_all на основе средней цены и структуру наценок */
  102. UPDATE prices_all AS pac,
  103. parse_rule_median AS prm
  104. SET pac.price_torg = CAST(pac.price_sredn + (pac.price_sredn * prm.margin/100) AS DECIMAL(10,2))
  105. WHERE ROUND(pac.price_sredn) > prm.min_price AND ROUND(pac.price_sredn) <= prm.max_price;
  106. -- ********************************************************************
  107.  
  108. -- Плагин установки цен на аккумуляторы с наценкой 14%
  109. UPDATE prices_all pa
  110. LEFT JOIN
  111. (SELECT sp.art_num, sp.brand_id, MIN(sp.price) minn
  112. FROM yii_doc.supplier_price sp
  113. LEFT JOIN yii_doc.supplier_price_avalaible spa ON sp.`hash` = spa.`hash`
  114. WHERE
  115. spa.avalaible > 0
  116. GROUP BY sp.hash_brand_artnum
  117. ) sp ON pa.art_num = sp.art_num AND pa.brand_id = sp.brand_id
  118.  
  119. SET pa.price_torg = minn + minn * 0.11
  120.  
  121. WHERE
  122. pa.pa_ga_id = 1
  123. AND pa.avalaible_gen IN (0,1)
  124. AND minn IS NOT NULL
  125. ;
  126.  
  127. -- Установление рознычных цен на уровне закупочных по товарам на складе
  128. UPDATE prices_all pa
  129. LEFT JOIN supplier_price sp ON pa.art_num = sp.art_num AND pa.brand_id = sp.brand_id
  130. LEFT JOIN supplier_price_avalaible spa ON sp.`hash` = spa.`hash`
  131. SET pa.price_torg = sp.price
  132. WHERE sp.supplier = 38 AND spa.avalaible > 0;
  133.  
  134. /* Новый запрос - работает и в сторону удаления статуса 1 и в сторону установки статуса 1. Плюс подсчитывает количество запчастей */
  135. UPDATE seo_available AS target
  136. LEFT JOIN
  137. (SELECT
  138. source.id AS id,
  139. IF(COUNT(DISTINCT prices_all.id) > 0, 1, null) AS avail,
  140. COUNT(DISTINCT prices_all.id) AS counting,
  141. CEIL(MIN(CASE WHEN prices_all.avalaible_gen = 1 THEN prices_all.price_torg END)) AS min_price
  142. FROM seo_available AS source
  143. LEFT JOIN seo_izdelie ON source.SA_izdelie_id = seo_izdelie.id
  144. INNER JOIN dok_ga ON seo_izdelie.dok_razdel_id = dok_ga.parent_id
  145. INNER JOIN link_short_model ON source.SA_model_id = link_short_model.ID_SEO_MOD
  146. LEFT JOIN tecdoc_2014_2q_europe.TYPES ON link_short_model.MOD_ID = TYPES.TYP_MOD_ID
  147. LEFT JOIN tecdoc_2014_2q_europe.LINK_LA_TYP ON TYPES.TYP_ID = LINK_LA_TYP.LAT_TYP_ID AND LINK_LA_TYP.LAT_GA_ID = dok_ga.GA_ID
  148. LEFT JOIN tecdoc_2014_2q_europe.LINK_ART ON LINK_LA_TYP.LAT_LA_ID = LINK_ART.LA_ID
  149. LEFT JOIN prices_all ON LINK_ART.LA_ART_ID = prices_all.pa_art_id
  150. WHERE prices_all.avalaible_gen IN (0,1)
  151. GROUP BY source.id) AS tbl ON target.id = tbl.id
  152.  
  153. SET
  154. target.SA_available = tbl.avail,
  155. target.SA_count = tbl.counting,
  156. target.min_price = tbl.min_price;
  157. -- ********************************************************************
RAW Paste Data
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
 
Top