7C4gXU05

1SET @@group_concat_max_len = 10240;
2
3
4DROP TABLE IF EXISTS _layout_template;
5CREATE TABLE _layout_template (
6  `template_name` VARCHAR(100) NOT NULL,
7  `content_types` SET ('article','articles_index','articles_listing','contactform','error','index','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_home','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
8  `module_name` VARCHAR(100) NOT NULL,
9  `position_name` VARCHAR(100) NOT NULL,
10  `module_order` INT(11) UNSIGNED NOT NULL,
11  `content_id` INT(11) UNSIGNED NOT NULL,
12  `parameter_name` VARCHAR(100) NOT NULL,
13  `parameter_value` VARCHAR(2000) NOT NULL,
14  KEY `template_name` (`template_name`,`module_name`,`position_name`,`module_order`,`content_id`,`parameter_name`)
15) ENGINE=INNODB DEFAULT CHARSET=utf8;
16
17
18DELETE FROM _layout_template; INSERT INTO _layout_template
19SELECT template_name, GROUP_CONCAT(page_name ORDER BY page_name), module_name, position_name, module_order, content_id, IF(parameter_name IS NULL, '', parameter_name), IF(parameter_value IS NULL, '', parameter_value)
20FROM predefined_page_layouts_v
21LEFT JOIN predefined_module_config_parameter_values ON predefined_page_layouts_v.id = predefined_page_layout_id
22WHERE predefined_layout_name = 'default'
23GROUP BY template_name, module_name, position_name, module_order, content_id, parameter_name, parameter_value;
24/*
25
26SELECT tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id, 
27GROUP_CONCAT(tc.content_types ORDER BY tc.content_types SEPARATOR ' => ') AS content_types,
28GROUP_CONCAT(tc.parameter_name ORDER BY tc.content_types SEPARATOR ' => ') AS parameter_name,
29GROUP_CONCAT(tc.parameter_value ORDER BY tc.content_types SEPARATOR ' => ') AS parameter_value
30FROM _layout_template AS tc, _layout_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
37AND !(tc.position_name = 'main' AND tc.content_types = tc.module_name)
38AND !(tc.position_name = 'main' AND tc.content_types IN ('articles_listing', 'articles_index,articles_listing', 'articles_listing,articles_listing' ) AND tc.module_name = 'articlelist')
39AND !(tc.position_name = 'main' AND tc.content_types IN ('page_index', 'page,page_index') AND tc.module_name = 'page')
40AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list') AND tc.module_name = 'ws_categorylist')
41AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_products_list') AND tc.module_name = 'ws_productlist')
42
43AND ((sc.parameter_name = tc.parameter_name AND sc.parameter_value != tc.parameter_value) OR (sc.parameter_name = '' AND tc.parameter_value != '') OR sc.content_types != tc.content_types)
44
45GROUP BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id
46ORDER BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id;*/
47
48
49DROP TABLE IF EXISTS _pages_sets;
50CREATE TABLE _pages_sets (
51  `template_name` VARCHAR(100) NOT NULL,
52  `ct_bit_count` INT(11) UNSIGNED NOT NULL,
53  `content_types` SET ('article','articles_index','articles_listing','contactform','error','index','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_home','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
54  KEY `template_name` (`template_name`)
55) ENGINE=INNODB DEFAULT CHARSET=utf8;
56
57DELETE FROM _pages_sets; INSERT INTO _pages_sets
58SELECT template_name, BIT_COUNT(content_types), content_types FROM (
59	SELECT DISTINCT _layout_template.template_name, _layout_template.content_types
60	FROM _layout_template, (SELECT template_name, MAX(LENGTH(content_types)) AS ct_length FROM _layout_template GROUP BY template_name) AS template_ct_lengths
61	WHERE _layout_template.template_name = template_ct_lengths.template_name
62	AND LENGTH(_layout_template.content_types) = template_ct_lengths.ct_length
63) as cts
64ORDER BY template_name;
65
66
67SELECT tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id, 
68MAKE_SET(~tc.content_types, 'article','articles_index','articles_listing','contactform','error','index','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_home','ws_product','ws_products_list','ws_shoppingcart') as missing_cts, tc.content_types,
69IF(parameter_name != '', GROUP_CONCAT(CONCAT("'", parameter_name, "'", ':', "'", parameter_value, "'")), '') AS configuration
70FROM _layout_template AS tc, _pages_sets as sc
71WHERE sc.template_name = tc.template_name
72AND sc.content_types != tc.content_types
73
74AND !(tc.position_name = 'main' AND tc.content_types = tc.module_name)
75AND !(tc.position_name = 'main' AND tc.content_types IN ('articles_listing', 'articles_index,articles_listing', 'articles_listing,articles_listing', 'articles_index,articles_listing,index') AND tc.module_name = 'articlelist')
76AND !(tc.position_name = 'main' AND tc.content_types IN ('page_index', 'page,page_index', 'index,page', 'index,page,page_index') AND tc.module_name = 'page')
77AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list') AND tc.module_name = 'ws_categorylist')
78AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_products_list', 'ws_categories_list') AND tc.module_name = 'ws_productlist')
79
80GROUP BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id, tc.content_types
81ORDER BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id;