· 8 years ago · Nov 27, 2017, 03:22 PM
1/* TPL-SITE Full.sql */
2
3SET @@group_concat_max_len = 1024000;
4
5DROP TABLE IF EXISTS _full_template;
6CREATE TABLE _full_template (
7 `site_id` INT(11) UNSIGNED NOT NULL,
8 `site_name` VARCHAR(100) NOT NULL,
9 `predefined_page_layout_id` INT(11) UNSIGNED NOT NULL,
10 `template_name` VARCHAR(100) NOT NULL,
11 `content_type` ENUM ('article','articles_listing','contactform','error','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
12 `module_name` VARCHAR(100) NOT NULL,
13 `position_name` VARCHAR(100) NOT NULL,
14 `module_order` INT(11) UNSIGNED NOT NULL,
15 `content_id` INT(11) UNSIGNED NOT NULL,
16 `parameter_name` VARCHAR(255),
17 `parameter_value` VARCHAR(2000),
18 KEY `template_name` (`site_id`,`template_name`,`module_name`,`position_name`,`module_order`,`content_id`,`parameter_name`)
19) ENGINE=INNODB DEFAULT CHARSET=utf8;
20
21DELETE FROM _full_template; INSERT INTO _full_template
22SELECT site_id, site_name, predefined_page_layouts_v.id, predefined_page_layouts_v.template_name, page_name, module_name, position_name, module_order, content_id, parameter_name, parameter_value
23FROM site_layouts_v, sites_templates, predefined_page_layouts_v
24LEFT JOIN predefined_module_config_parameter_values ON predefined_page_layouts_v.id = predefined_page_layout_id
25WHERE predefined_layout_name = 'default'
26AND layout_name = 'active'
27AND site_layouts_v.id = sites_templates.site_layout_id
28AND predefined_page_layouts_v.template_name = sites_templates.template_name;
29
30
31DROP TABLE IF EXISTS _full_site;
32CREATE TABLE _full_site (
33 `site_id` INT(11) UNSIGNED NOT NULL,
34 `site_name` VARCHAR(100) NOT NULL,
35 `page_layout_id` INT(11) UNSIGNED NOT NULL,
36 `template_name` VARCHAR(100) NOT NULL,
37 `content_type` ENUM ('article','articles_listing','contactform','error','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
38 `module_name` VARCHAR(100) NOT NULL,
39 `position_name` VARCHAR(100) NOT NULL,
40 `module_order` INT(11) UNSIGNED NOT NULL,
41 `content_id` INT(11) UNSIGNED NOT NULL,
42 `parameter_name` VARCHAR(255),
43 `parameter_value` VARCHAR(2000),
44 KEY `template_name` (`site_id`,`template_name`,`module_name`,`position_name`,`module_order`,`content_id`,`parameter_name`)
45) ENGINE=INNODB DEFAULT CHARSET=utf8;
46
47DELETE FROM _full_site; INSERT INTO _full_site
48SELECT page_layouts_v.site_id, page_layouts_v.site_name, page_layouts_v.id, template_name, page_name, module_name, position_name, module_order, content_id, parameter_name, parameter_value
49FROM sites_templates, page_layouts_v
50LEFT JOIN module_config_parameter_values ON page_layouts_v.id = page_layout_id
51WHERE page_layouts_v.site_layout_id = sites_templates.site_layout_id
52AND page_layouts_v.layout_name = 'active';
53
54
55SET @SITE_ID = 'all';
56SET @MODULE = 'all';
57SET @PARAMETER = 'all';
58
59
60DROP TABLE IF EXISTS _full_diffs;
61CREATE TABLE _full_diffs (
62 `diff` VARCHAR(100) NOT NULL,
63 `site_id` INT(11) UNSIGNED NOT NULL,
64 `site_name` VARCHAR(100) NOT NULL,
65 `template_name` VARCHAR(100) NOT NULL,
66 `content_type` ENUM ('article','articles_listing','contactform','error','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
67 `module_name` VARCHAR(100) NOT NULL,
68 `position_name` VARCHAR(100) NOT NULL,
69 `module_order` INT(11) UNSIGNED NOT NULL,
70 `parameter_name` VARCHAR(255),
71 `template_value` VARCHAR(2000),
72 `site_value` VARCHAR(2000),
73 `tpl_ref_id` INT(11) UNSIGNED,
74 `site_ref_id` INT(11) UNSIGNED,
75 KEY `template_name` (`diff`, `site_id`,`module_name`,`position_name`,`module_order`, `parameter_name`)
76) ENGINE=INNODB DEFAULT CHARSET=utf8;
77
78DELETE FROM _full_diffs;
79
80INSERT INTO _full_diffs
81/* Missing instance on site level */
82SELECT 'MISSING SITE INSTANCE' AS diff, ft.site_id, ft.site_name, ft.template_name, ft.content_type, ft.module_name, ft.position_name, ft.module_order,
83 ft.parameter_name, ft.parameter_value AS template_value, fs.parameter_value AS site_value,
84 ft.predefined_page_layout_id AS tpl_ref_id, fs.page_layout_id AS site_ref_id
85FROM _full_template AS ft
86LEFT JOIN _full_site AS fs
87 ON ft.site_id = fs.site_id
88 AND ft.template_name = fs.template_name
89 AND ft.content_type = fs.content_type
90 AND ft.module_name = fs.module_name
91 AND ft.position_name = fs.position_name
92 AND ft.module_order = fs.module_order
93WHERE (ft.site_id = @SITE_ID OR 'all' = @SITE_ID)
94AND (ft.module_name = @MODULE OR 'all' = @MODULE)
95AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
96AND fs.page_layout_id IS NULL;
97
98INSERT INTO _full_diffs
99/* Missing instance parameters or different parameters on site level */
100SELECT DISTINCT IF(ft.parameter_name = fs.parameter_name, 'DIFF SITE PARAM', 'MISSING SITE PARAM') AS diff,
101 ft.site_id, ft.site_name, ft.template_name, ft.content_type, ft.module_name, ft.position_name, ft.module_order,
102 ft.parameter_name, ft.parameter_value AS template_value, fs.parameter_value AS site_value,
103 ft.predefined_page_layout_id AS tpl_ref_id, spl.page_layout_id AS site_ref_id
104FROM _full_site AS spl, _full_template AS ft
105LEFT JOIN _full_site AS fs
106 ON ft.site_id = fs.site_id
107 AND ft.template_name = fs.template_name
108 AND ft.content_type = fs.content_type
109 AND ft.module_name = fs.module_name
110 AND ft.position_name = fs.position_name
111 AND ft.module_order = fs.module_order
112 AND ft.parameter_name = fs.parameter_name
113WHERE (ft.site_id = @SITE_ID OR 'all' = @SITE_ID)
114AND (ft.module_name = @MODULE OR 'all' = @MODULE)
115AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
116 AND ft.site_id = spl.site_id
117 AND ft.template_name = spl.template_name
118 AND ft.content_type = spl.content_type
119 AND ft.module_name = spl.module_name
120 AND ft.position_name = spl.position_name
121 AND ft.module_order = spl.module_order
122AND (
123 (ft.parameter_name IS NOT NULL AND fs.parameter_name IS NULL)
124 OR (ft.parameter_name = fs.parameter_name AND ft.parameter_value != fs.parameter_value)
125);
126
127INSERT INTO _full_diffs
128/* Existing instance on site level */
129SELECT 'EXISTS SITE INSTANCE' AS diff, fs.site_id, fs.site_name, fs.template_name, fs.content_type, fs.module_name, fs.position_name, fs.module_order,
130 fs.parameter_name, fs.parameter_value AS site_value, ft.parameter_value AS template_value,
131 ft.predefined_page_layout_id AS tpl_ref_id, fs.page_layout_id AS site_ref_id
132FROM _full_template AS ft
133RIGHT JOIN _full_site AS fs
134 ON ft.site_id = fs.site_id
135 AND ft.template_name = fs.template_name
136 AND ft.content_type = fs.content_type
137 AND ft.module_name = fs.module_name
138 AND ft.position_name = fs.position_name
139 AND ft.module_order = fs.module_order
140WHERE (fs.site_id = @SITE_ID OR 'all' = @SITE_ID)
141AND (fs.module_name = @MODULE OR 'all' = @MODULE)
142AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
143AND ft.predefined_page_layout_id IS NULL;
144
145INSERT INTO _full_diffs
146/* Existing instance parameters or different parameters on site level */
147SELECT DISTINCT IF(fs.parameter_name = fs.parameter_name, 'DIFF TPL PARAM', 'MISSING TPL PARAM') AS diff,
148 fs.site_id, fs.site_name, fs.template_name, fs.content_type, fs.module_name, fs.position_name, fs.module_order,
149 fs.parameter_name, fs.parameter_value AS site_value, ft.parameter_value AS template_value,
150 ft.predefined_page_layout_id AS tpl_ref_id, fs.page_layout_id AS site_ref_id
151FROM _full_template AS ft
152RIGHT JOIN _full_site AS fs
153 ON ft.site_id = fs.site_id
154 AND ft.template_name = fs.template_name
155 AND ft.content_type = fs.content_type
156 AND ft.module_name = fs.module_name
157 AND ft.position_name = fs.position_name
158 AND ft.module_order = fs.module_order
159 AND ft.parameter_name = fs.parameter_name
160WHERE (fs.site_id = @SITE_ID OR 'all' = @SITE_ID)
161AND (fs.module_name = @MODULE OR 'all' = @MODULE)
162AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
163AND (
164 (ft.parameter_name IS NULL AND fs.parameter_name IS NOT NULL)
165 OR (ft.parameter_name IS NOT NULL AND fs.parameter_name IS NOT NULL AND ft.parameter_value != fs.parameter_value)
166);
167
168
169SET @NR = 0;
170SELECT @NR:=@NR+1 AS nr, GROUP_CONCAT(DISTINCT diff) AS diff, site_id, site_name, template_name, content_types,
171 module_name, position_name, GROUP_CONCAT(DISTINCT module_order) AS module_order,
172 IF(parameter_name = '', '', GROUP_CONCAT(CONCAT("'", parameter_name, "':'", template_value, "' ['", site_value, "']"))) AS configuration
173 /*, tpl_refs_id, site_refs_id*/
174FROM (
175 SELECT diff, site_id, site_name, template_name,
176 GROUP_CONCAT(content_type ORDER BY content_type) AS content_types, module_name, position_name,
177 GROUP_CONCAT(DISTINCT module_order) AS module_order,
178 IF(parameter_name IS NULL, '', parameter_name) AS parameter_name,
179 IF(template_value IS NULL, '', template_value) AS template_value, IF(site_value IS NULL, '', site_value) AS site_value
180 /*, GROUP_CONCAT(tpl_ref_id ORDER BY content_type) AS tpl_refs_id, GROUP_CONCAT(site_ref_id ORDER BY content_type) AS site_refs_id*/
181 FROM _full_diffs
182 WHERE !(module_name IN ('articlebox', 'newsletter_signup_box', 'rssbox', 'ws_shoppingcart_box', 'ws_creditcards','searchbox') AND position_name IN ('right', 'left')) /* Article boxes messing with order in left and right positions. Should be included in auto-transfer data script */
183 AND !(module_name IN ('ws_categorytree') AND parameter_name IN ('display_title', 'title')) /* Webshop Configuration */
184 AND !(module_name IN ('menu') AND parameter_name IN ('display_menu_title', 'title')) /* Override of menu element */
185 AND !(module_name IN ('menu') AND parameter_name IN ('menuselect_id', 'menuselect_type')) /* Override of menu element for fixed content_type */
186 AND !(module_name IN ('menu') AND parameter_name IN ('start_level', 'end_level')) /* Override of menu element for split menu */
187 AND !(position_name = 'main') /* Custom overrides of main position */
188 AND !(module_name IN ('article', 'articlelist') AND parameter_name IN ('display_created_by', 'display_created_date', 'display_updated_by', 'display_updated_date', 'pagination', 'num_of_articles')) /* Configuration of Article category content */
189 AND !(module_name IN ('generic')) /* Custom generic content changes or repositions */
190 AND !(module_name IN ('searchbox') AND position_name IN ('top_right')) /* Custom searchbox repositions */
191 AND !(module_name IN ('logo') AND position_name IN ('top_left', 'top', 'top_right')) /* Custom searchbox repositions */
192 AND !(module_name IN ('addressbar', 'addressbar2')) /* Custom addressbar and addressbar2 changes or repositions */
193 AND !(module_name IN ('branding_text') AND site_id IN (600205,600214,600228,600364,600390,600428,600534,600603,600605,600606,600616,600678,600703,600720,600736,600758,601535,602057,601953,601900,601632,602064)) /* They don't want branding_text */
194 AND !(position_name IN ('main') AND site_id IN (600166, 600796, 600798, 600804, 600857)) /* They have menu in main area */
195 AND !(position_name IN ('left') AND module_name IN ('ws_categorytree') AND site_id IN (601968)) /* Globy and ws_categorytree in left position */
196 AND !(position_name IN ('right') AND module_name IN ('loginbox') AND site_id IN (602080)) /* LEIF ANDREAS KVITVANG and loginbox in right position */
197 GROUP BY diff, site_id, module_name, position_name, /*module_order,*/ parameter_name, template_value, site_value
198 ORDER BY site_id, position_name, module_order
199) AS layouts
200WHERE 1
201/*and module_name in ('addressbar', 'addressbar2')*/
202GROUP BY site_id, module_name, position_name, /*module_order,*/ content_types/*, tpl_refs_id, site_refs_id*/
203ORDER BY diff, position_name, site_id, template_name, position_name, module_order, module_name, configuration;