fjzENV4B

1/* Поставить на ночь после выкладки!
2
3DROP TABLE IF EXISTS products_attributes_temp;
4CREATE TABLE products_attributes_temp (
5  `products_attributes_id` INT UNSIGNED NOT NULL,
6  `article_supplier`       VARCHAR(255) NOT NULL,
7  `brandswelove_id`        INT UNSIGNED NOT NULL,
8  `suppliers_id`           INT UNSIGNED NOT NULL,
9  `color_id`               INT UNSIGNED NOT NULL,
10  `ls_id`                  INT UNSIGNED NOT NULL DEFAULT 0,
11  `growth_id`              INT UNSIGNED NOT NULL DEFAULT 0,
12  `age_id`                 INT UNSIGNED NOT NULL DEFAULT 0,
13  `ru_id`                  INT UNSIGNED NOT NULL DEFAULT 0,
14  `size_id`                INT UNSIGNED NOT NULL DEFAULT 0,
15  `gh_id`                  INT UNSIGNED NOT NULL DEFAULT 0,
16  `volume_id`              INT UNSIGNED NOT NULL DEFAULT 0,
17  PRIMARY KEY (`products_attributes_id`),
18  INDEX `my_index` (`article_supplier`, `brandswelove_id`, `suppliers_id`, `color_id`, `ls_id`, `growth_id`, `age_id`, `ru_id`, `size_id`, `gh_id`, `volume_id`)
19)
20  COMMENT = 'Временная таблица для задачи 24576'
21  COLLATE = 'utf8_general_ci'
22  ENGINE = InnoDB;
23
24DROP PROCEDURE IF EXISTS fill_products_attributes_temp;
25DELIMITER $$
26CREATE PROCEDURE fill_products_attributes_temp()
27  BEGIN
28    SET @limitValue = 250000;
29
30    SELECT
31      MAX(pa.products_attributes_id),
32      MIN(pa.products_attributes_id)
33    INTO
34      @maxValue,
35      @startValue
36    FROM products_attributes AS pa;
37
38    REPEAT
39
40      INSERT IGNORE INTO products_attributes_temp
41        SELECT
42          pa.products_attributes_id,
43          pa.article_supplier,
44          p.brandswelove_id,
45          c.suppliers_id,
46          pao_color.products_options_values_id  AS color_id,
47
48          pao_ls.products_options_values_id     AS ls_id,
49          pao_growth.products_options_values_id AS growth_id,
50          pao_age.products_options_values_id    AS age_id,
51          pao_ru.products_options_values_id     AS ru_id,
52          pao_size.products_options_values_id   AS size_id,
53          pao_gh.products_options_values_id     AS gh_id,
54          pao_volume.products_options_values_id AS volume_id
55        FROM `products_attributes` AS `pa`
56          JOIN `products` AS `p` ON p.products_id = pa.products_id
57          JOIN `products_to_categories` AS `ptc` ON ptc.products_id = p.products_id
58          JOIN `categories` AS `c` ON c.categories_id = ptc.categories_id
59
60          JOIN `products_attributes_options` AS `pao_color` ON pao_color.products_attributes_id = pa.products_attributes_id AND pao_color.products_options_id = 3
61
62          LEFT JOIN `products_attributes_options` AS `pao_ls` ON pao_ls.products_attributes_id = pa.products_attributes_id AND pao_ls.products_options_id = 15
63          LEFT JOIN `products_attributes_options` AS `pao_growth` ON pao_growth.products_attributes_id = pa.products_attributes_id AND pao_growth.products_options_id = 11
64          LEFT JOIN `products_attributes_options` AS `pao_age` ON pao_age.products_attributes_id = pa.products_attributes_id AND pao_age.products_options_id = 12
65          LEFT JOIN `products_attributes_options` AS `pao_ru` ON pao_ru.products_attributes_id = pa.products_attributes_id AND pao_ru.products_options_id = 1
66          LEFT JOIN `products_attributes_options` AS `pao_size` ON pao_size.products_attributes_id = pa.products_attributes_id AND pao_size.products_options_id = 28
67          LEFT JOIN `products_attributes_options` AS `pao_gh` ON pao_gh.products_attributes_id = pa.products_attributes_id AND pao_gh.products_options_id = 25
68          LEFT JOIN `products_attributes_options` AS `pao_volume` ON pao_volume.products_attributes_id = pa.products_attributes_id AND pao_volume.products_options_id = 19
69
70        WHERE
71          pa.products_attributes_id >= @startValue
72          AND pa.products_attributes_id < @startValue + @limitValue
73          AND COALESCE(pa.article_supplier, '') != ''
74          AND COALESCE(p.brandswelove_id, 0) != 0;
75
76      SET @startValue = @startValue + @limitValue;
77    UNTIL @startValue > @maxValue
78    END REPEAT;
79  END $$
80DELIMITER ;
81
82CALL fill_products_attributes_temp();
83DROP PROCEDURE IF EXISTS fill_products_attributes_temp;
84
85REPLACE INTO products_attributes_legacy
86  SELECT
87    pat.products_attributes_id,
88    t.min_products_attributes_id
89  FROM products_attributes_temp AS pat
90    JOIN (
91           SELECT
92             MIN(pat.products_attributes_id) AS min_products_attributes_id,
93             pat.article_supplier,
94             pat.brandswelove_id,
95             pat.suppliers_id,
96             pat.color_id,
97             pat.ls_id,
98             pat.growth_id,
99             pat.age_id,
100             pat.ru_id,
101             pat.size_id,
102             pat.gh_id,
103             pat.volume_id
104           FROM products_attributes_temp AS pat
105           GROUP BY pat.article_supplier, pat.brandswelove_id, pat.suppliers_id, pat.color_id, pat.ls_id, pat.growth_id, pat.age_id, pat.ru_id, pat.size_id, pat.gh_id, pat.volume_id
106         ) AS t ON
107                  pat.article_supplier = t.article_supplier
108                  AND pat.brandswelove_id = t.brandswelove_id
109                  AND pat.suppliers_id = t.suppliers_id
110                  AND pat.color_id = t.color_id
111
112                  AND pat.ls_id = t.ls_id
113                  AND pat.growth_id = t.growth_id
114                  AND pat.age_id = t.age_id
115                  AND pat.ru_id = t.ru_id
116                  AND pat.size_id = t.size_id
117                  AND pat.gh_id = t.gh_id
118                  AND pat.volume_id = t.volume_id;
119
120INSERT IGNORE INTO products_attributes_groups (products_attributes_article_supplier, brandswelove_id, suppliers_id, products_attributes_options_color_id)
121  SELECT
122    pat.article_supplier,
123    pat.brandswelove_id,
124    pat.suppliers_id,
125    pat.color_id
126  FROM products_attributes_temp AS pat
127  GROUP BY pat.article_supplier, pat.brandswelove_id, pat.suppliers_id, pat.color_id;
128
129REPLACE INTO products_attributes_to_products_attributes_groups
130  SELECT
131    pat.products_attributes_id,
132    pag.products_attributes_groups_id
133  FROM products_attributes_temp AS pat
134    JOIN products_attributes_groups AS pag
135      ON
136        pat.article_supplier = pag.products_attributes_article_supplier
137        AND pat.brandswelove_id = pag.brandswelove_id
138        AND pat.suppliers_id = pag.suppliers_id
139        AND pat.color_id = pag.products_attributes_options_color_id;
140
141DROP TABLE IF EXISTS products_attributes_temp;
142
143UPDATE classifier_binds_to_products_attributes AS cbtpa
144  JOIN products_attributes_legacy AS pal ON pal.products_attributes_id = cbtpa.object_id
145  JOIN (
146         SELECT
147           pal.products_attributes_parent_id,
148           MAX(pal.products_attributes_id) AS products_attributes_id
149         FROM products_attributes_legacy AS pal
150         GROUP BY pal.products_attributes_parent_id
151       ) AS t ON t.products_attributes_parent_id = pal.products_attributes_parent_id
152  JOIN classifier_binds_to_products_attributes AS cbtpa_new ON cbtpa_new.object_id = t.products_attributes_id
153SET
154  cbtpa.classifier_categories_id    = cbtpa_new.classifier_categories_id,
155  cbtpa.classifier_subcategories_id = cbtpa_new.classifier_subcategories_id,
156  cbtpa.classifier_orientations_id  = cbtpa_new.classifier_orientations_id,
157  cbtpa.classifier_accessories_id   = cbtpa_new.classifier_accessories_id;
158
159
160  INSERT IGNORE INTO products_attributes_total
161  SELECT pac.products_attributes_id, COUNT(opn.id)
162  FROM products_attributes_to_categories pac
163    LEFT JOIN orders_products_new opn ON opn.products_attributes_id = pac.products_attributes_id
164  GROUP BY pac.products_attributes_id
165;
166
167*/