-- Очистка prices_all от соединений к старому TD
/*
UPDATE prices_all
SET pa_art_id = NULL, pa_ga_id = NULL;
*/

-- Запрос на добавление id нашего каталога к запчастям из прайсов
UPDATE prices_all
LEFT JOIN tecdoc_2014_2q_europe.ART_LOOKUP ON ART_LOOKUP.ARL_SEARCH_NUMBER = prices_all.art_num
LEFT JOIN tecdoc_2014_2q_europe.ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
LEFT JOIN brand_groups ON brand_groups.BRA_ID = ARTICLES.ART_SUP_ID
LEFT JOIN tecdoc_2014_2q_europe.LINK_ART_GA ON ARTICLES.ART_ID = LINK_ART_GA.LAG_ART_ID
INNER JOIN dok_ga ON dok_ga.GA_ID = LINK_ART_GA.LAG_GA_ID
INNER JOIN dok_razdel ON dok_ga.parent_id = dok_razdel.id
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
WHERE ART_LOOKUP.ARL_KIND = 1 AND prices_all.brand_id = brand_groups.parent_id;
-- ********************************************************************



-- Обновление seo структуры автомобилей и изделий для них

/* Структура авто и изделий в seo */
/*
TRUNCATE TABLE seo_available;
INSERT INTO seo_available (SA_izdelie_id, SA_marka_id, SA_model_id)
SELECT
seo_izdelie.id,
seo_marki.id,
seo_model.id
FROM seo_izdelie
CROSS JOIN seo_model
INNER JOIN seo_marki ON seo_model.parent_id = seo_marki.id
WHERE seo_izdelie.dok_razdel_id IS NOT NULL
ORDER BY seo_izdelie.id, seo_marki.id, seo_model.id;
*/


-- Расчет цен по медиане и их запись в prices_all.id

/* Очистка и заполнение промежуточной таблицы price_median */
TRUNCATE TABLE prices_median;
INSERT INTO prices_median (art_num, brand_id, row_number, hash_art, sup_pattern_id, price)
SELECT
d.art_num,
d.brand_id,
IF(@prev=d.hash_art, @rownum:=@rownum+1, @rownum:=1) AS row_number,
@prev:=d.hash_art,
d.supplier,
d.price
FROM
                               (SELECT
                               sp.hash_brand_artnum AS hash_art,
                               sp.art_num,
                               sp.brand_id,
                               sp.supplier,
                               sp.price
                               FROM supplier_price as sp
                               LEFT JOIN supplier_price_avalaible as spa ON sp.`hash` = spa.`hash`
															 LEFT JOIN supplier_pattern spat ON spa.price_id = spat.id
															 LEFT JOIN supplier_storage ss ON spa.storage_id = ss.id
                               WHERE spa.avalaible > 0 AND spat.active = 1 AND sp.brand_id IS NOT NULL AND ss.no_active_price IS NULL
                               GROUP BY hash_art, sp.supplier
                               ORDER BY hash_art, price) AS d,
                (SELECT @rownum:=0, @prev:=NULL) r;



/* Обновление срдених цен в prices_all на основе медианы */
UPDATE
prices_all AS pac,
(SELECT
res.art_num,
res.brand_id,
CAST(CASE
WHEN res.total_rows = 1 THEN res.price
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))
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))
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))
WHEN res.total_rows > 2 THEN AVG(res.price)
END AS DECIMAL(10,2)) AS median_price
FROM
(
                               SELECT t1.row_number, t1.hash_art, t1.art_num, t1.brand_id, t1.price, t2.total_rows
                               FROM prices_median AS t1
                               LEFT JOIN
                                                                                              (
                                                                              SELECT count(*) AS total_rows, d.hash_art
                                                                              FROM prices_median AS d
                                                                              GROUP BY d.hash_art
                                                                                              ) AS t2
                               ON t1.hash_art = t2.hash_art
                               WHERE 1=1
                               AND t1.row_number>=t2.total_rows/2 AND t1.row_number<=t2.total_rows/2+1
) AS res
WHERE res.total_rows > 0
GROUP BY res.hash_art) AS source
SET pac.price_sredn = source.median_price
WHERE source.art_num = pac.art_num AND source.brand_id = pac.brand_id;

/* Запись торговых цен в prices_all на основе средней цены и структуру наценок */
UPDATE prices_all AS pac,
parse_rule_median AS prm
SET pac.price_torg = CAST(pac.price_sredn + (pac.price_sredn * prm.margin/100) AS DECIMAL(10,2))
WHERE ROUND(pac.price_sredn) > prm.min_price AND ROUND(pac.price_sredn) <= prm.max_price;
-- ********************************************************************

-- Плагин установки цен на аккумуляторы с наценкой 14%
UPDATE prices_all pa
LEFT JOIN
(SELECT sp.art_num, sp.brand_id, MIN(sp.price) minn
FROM yii_doc.supplier_price sp
LEFT JOIN yii_doc.supplier_price_avalaible spa ON sp.`hash` = spa.`hash`
WHERE
spa.avalaible > 0
GROUP BY sp.hash_brand_artnum
) sp ON pa.art_num = sp.art_num AND pa.brand_id = sp.brand_id

SET pa.price_torg = minn + minn * 0.11

WHERE
pa.pa_ga_id = 1
AND pa.avalaible_gen IN (0,1)
AND minn IS NOT NULL
;

-- Установление рознычных цен на уровне закупочных по товарам на складе
UPDATE prices_all pa
LEFT JOIN supplier_price sp ON pa.art_num = sp.art_num AND pa.brand_id = sp.brand_id
LEFT JOIN supplier_price_avalaible spa ON sp.`hash` = spa.`hash`
SET pa.price_torg = sp.price
WHERE sp.supplier = 38 AND spa.avalaible > 0;

/* Новый запрос - работает и в сторону удаления статуса 1 и в сторону установки статуса 1. Плюс подсчитывает количество запчастей */
UPDATE seo_available AS target
LEFT JOIN
(SELECT
source.id AS id,
IF(COUNT(DISTINCT prices_all.id) > 0, 1, null) AS avail,
COUNT(DISTINCT prices_all.id) AS counting,
CEIL(MIN(CASE WHEN prices_all.avalaible_gen = 1 THEN prices_all.price_torg END)) AS min_price
FROM seo_available AS source
LEFT JOIN seo_izdelie ON source.SA_izdelie_id = seo_izdelie.id
INNER JOIN dok_ga ON seo_izdelie.dok_razdel_id = dok_ga.parent_id
INNER JOIN link_short_model ON source.SA_model_id = link_short_model.ID_SEO_MOD
LEFT JOIN tecdoc_2014_2q_europe.TYPES ON link_short_model.MOD_ID = TYPES.TYP_MOD_ID
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
LEFT JOIN tecdoc_2014_2q_europe.LINK_ART ON LINK_LA_TYP.LAT_LA_ID = LINK_ART.LA_ID
LEFT JOIN prices_all ON LINK_ART.LA_ART_ID = prices_all.pa_art_id
WHERE prices_all.avalaible_gen IN (0,1)
GROUP BY source.id) AS tbl ON target.id = tbl.id

SET
target.SA_available = tbl.avail,
target.SA_count = tbl.counting,
target.min_price = tbl.min_price;
-- ********************************************************************