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