Y84BUhi4

1SET @@group_concat_max_len = 10240;
2
3DROP TABLE IF EXISTS _flatten_template;
4CREATE TABLE _flatten_template (
5  `template_name` VARCHAR(100) NOT NULL,
6  `content_types` VARCHAR(1000) NOT NULL,
7  `module_name` VARCHAR(100) NOT NULL,
8  `position_name` VARCHAR(100) NOT NULL,
9  `module_order` INT(11) UNSIGNED NOT NULL,
10  `content_id` INT(11) UNSIGNED NOT NULL,
11  `configuration` VARCHAR(10000) NOT NULL,
12  KEY `template_name` (`template_name`,`module_name`,`position_name`,`module_order`,`content_id`)
13) ENGINE=INNODB DEFAULT CHARSET=utf8;
14
15DELETE FROM _flatten_template; INSERT INTO _flatten_template
16SELECT template_name, GROUP_CONCAT(page_name ORDER BY page_name), module_name, position_name, module_order, content_id, IF(configuration IS NULL, '', configuration) FROM (
17	SELECT template_name, page_name, module_name, position_name, module_order, content_id,
18		GROUP_CONCAT(CONCAT("'", parameter_name, "'", ':', "'", parameter_value, "'")) AS configuration,
19		GROUP_CONCAT(CONCAT(parameter_name, MD5(parameter_value))) AS md5_configuration
20	FROM predefined_page_layouts_v
21	LEFT JOIN predefined_module_config_parameter_values ON predefined_page_layouts_v.id = predefined_page_layout_id
22	WHERE predefined_layout_name = 'default'
23	GROUP BY predefined_page_layouts_v.id
24) AS concatenated_configuration
25GROUP BY template_name, module_name, position_name, module_order, content_id, md5_configuration;
26	
27SELECT tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id, 
28GROUP_CONCAT(tc.content_types ORDER BY tc.content_types SEPARATOR ' => ') AS content_types,
29GROUP_CONCAT(tc.configuration ORDER BY tc.content_types SEPARATOR '=> ') AS configuration
30FROM _flatten_template AS tc, _flatten_template AS sc
31WHERE sc.template_name = tc.template_name
32AND sc.module_name = tc.module_name
33AND sc.position_name = tc.position_name
34AND sc.module_order = tc.module_order
35AND sc.content_id = tc.content_id
36/*AND sc.content_types = tc.content_types)*/
37
38AND !(sc.position_name = 'main' AND sc.content_types = sc.module_name)
39AND !(sc.position_name = 'main' AND sc.content_types IN ('articles_listing', 'articles_index,articles_listing', 'articles_listing,articles_listing' ) AND sc.module_name = 'articlelist')
40AND !(sc.position_name = 'main' AND sc.content_types IN ('page_index', 'page,page_index') AND sc.module_name = 'page')
41AND !(sc.position_name = 'main' AND sc.content_types IN ('ws_categories_list') AND sc.module_name = 'ws_categorylist')
42AND !(sc.position_name = 'main' AND sc.content_types IN ('ws_products_list') AND sc.module_name = 'ws_productlist')
43
44AND (sc.configuration != tc.configuration /*OR sc.content_types != tc.content_types*/)
45
46GROUP BY template_name, module_name, position_name, module_order, content_id
47ORDER BY template_name, module_name, position_name, module_order, content_id;