|
1
|
use salarymarket;
|
|
2
|
CREATE TABLE solution_questionnaire (
|
|
3
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
4
|
name VARCHAR(255) NOT NULL,
|
|
5
|
description Text null,
|
|
6
|
countryCode VARCHAR(255) NOT NULL,
|
|
7
|
`order` INT NOT NULL,
|
|
8
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
9
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
10
|
FOREIGN KEY (countryCode) REFERENCES solution_setting_country(countryCode) ON DELETE CASCADE
|
|
11
|
);
|
|
12
|
INSERT INTO solution_questionnaire (id, name, description, countryCode, `order`)
|
|
13
|
VALUES
|
|
14
|
(1, 'Questionnaire 1 -Les Politiques et pratiques de Rémunération','Exploration des politiques et pratiques de rémunération au sein de l\'organisation' , 'TN', 1),
|
|
15
|
(2, 'Questionnaire 2 -Led Pratiques de Révision Salariale','Analyse des pratiques de révision salariale et leur impact sur les employés', 'TN', 2),
|
|
16
|
(3, 'Questionnaire 3 -Attraction, Rétention et fin de contrat','Évaluation des stratégies d\'attraction et de rétention des talents, ainsi que des processus de fin de contrat', 'TN', 3),
|
|
17
|
(4, 'Questionnaire 4 -Les Véhicules de Société','Examen des politiques concernant l\'utilisation des véhicules de société', 'TN', 4);
|
|
18
|
|
|
19
|
CREATE TABLE solution_setting_country (
|
|
20
|
id INT AUTO_INCREMENT UNIQUE,
|
|
21
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
22
|
active boolean,
|
|
23
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
24
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
25
|
);
|
|
26
|
|
|
27
|
CREATE TABLE solution_setting_country_translate (
|
|
28
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
29
|
Label TEXT,
|
|
30
|
countryCode VARCHAR(255) NOT NULL,
|
|
31
|
languageId INT NOT NULL,
|
|
32
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
33
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
34
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
35
|
FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
36
|
);
|
|
37
|
|
|
38
|
CREATE TABLE solution_setting_business_sector (
|
|
39
|
id INT AUTO_INCREMENT UNIQUE,
|
|
40
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
41
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
42
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
43
|
);
|
|
44
|
CREATE TABLE solution_setting_business_sector_translate (
|
|
45
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
46
|
Label TEXT,
|
|
47
|
businessSectorCode VARCHAR(255) NOT NULL,
|
|
48
|
languageId INT NOT NULL,
|
|
49
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
50
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
51
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
52
|
FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
53
|
);
|
|
54
|
|
|
55
|
CREATE TABLE solution_setting_sales_turnover (
|
|
56
|
id INT AUTO_INCREMENT UNIQUE,
|
|
57
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
58
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
59
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
60
|
);
|
|
61
|
CREATE TABLE solution_setting_sales_turnover_translate (
|
|
62
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
63
|
Label TEXT,
|
|
64
|
salesTurnoverCode VARCHAR(255) NOT NULL,
|
|
65
|
languageId INT NOT NULL,
|
|
66
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
67
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
68
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
69
|
FOREIGN KEY (salesTurnoverCode) REFERENCES solution_setting_sales_turnover(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
70
|
);
|
|
71
|
|
|
72
|
CREATE TABLE solution_setting_company_size (
|
|
73
|
id INT AUTO_INCREMENT UNIQUE,
|
|
74
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
75
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
76
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
77
|
);
|
|
78
|
CREATE TABLE solution_setting_company_size_translate (
|
|
79
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
80
|
Label TEXT,
|
|
81
|
companySizeCode VARCHAR(255) NOT NULL,
|
|
82
|
languageId INT NOT NULL,
|
|
83
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
84
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
85
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
86
|
FOREIGN KEY (companySizeCode) REFERENCES solution_setting_company_size(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
87
|
);
|
|
88
|
|
|
89
|
CREATE TABLE solution_setting_geographic_extent (
|
|
90
|
id INT AUTO_INCREMENT UNIQUE,
|
|
91
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
92
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
93
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
94
|
);
|
|
95
|
CREATE TABLE solution_setting_geographic_extent_translate (
|
|
96
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
97
|
Label TEXT,
|
|
98
|
geographicExtentCode VARCHAR(255) NOT NULL,
|
|
99
|
languageId INT NOT NULL,
|
|
100
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
101
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
102
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
103
|
FOREIGN KEY (geographicExtentCode) REFERENCES solution_setting_geographic_extent(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
104
|
);
|
|
105
|
|
|
106
|
CREATE TABLE solution_setting_value_chain (
|
|
107
|
id INT AUTO_INCREMENT UNIQUE,
|
|
108
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
109
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
110
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
111
|
);
|
|
112
|
CREATE TABLE solution_setting_value_chain_translate (
|
|
113
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
114
|
Label TEXT,
|
|
115
|
valueChainCode VARCHAR(255) NOT NULL,
|
|
116
|
languageId INT NOT NULL,
|
|
117
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
118
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
119
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
120
|
FOREIGN KEY (valueChainCode) REFERENCES solution_setting_value_chain(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
121
|
);
|
|
122
|
|
|
123
|
CREATE TABLE solution_setting_town(
|
|
124
|
id INT AUTO_INCREMENT UNIQUE,
|
|
125
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
126
|
countryCode VARCHAR(255) NOT NULL,
|
|
127
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
128
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
129
|
FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
130
|
);
|
|
131
|
CREATE TABLE solution_setting_town_translate (
|
|
132
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
133
|
Label TEXT,
|
|
134
|
townCode VARCHAR(255) NOT NULL,
|
|
135
|
languageId INT NOT NULL,
|
|
136
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
137
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
138
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
139
|
FOREIGN KEY (townCode) REFERENCES solution_setting_town(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
140
|
);
|
|
141
|
|
|
142
|
CREATE TABLE solution_setting_sub_sector (
|
|
143
|
id INT AUTO_INCREMENT UNIQUE,
|
|
144
|
code VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
145
|
businessSectorCode VARCHAR(255) NOT NULL,
|
|
146
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
147
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
148
|
FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
149
|
);
|
|
150
|
CREATE TABLE solution_setting_sub_sector_translate (
|
|
151
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
152
|
Label TEXT,
|
|
153
|
subSectorCode VARCHAR(255) NOT NULL,
|
|
154
|
languageId INT NOT NULL,
|
|
155
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
156
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
157
|
FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
158
|
FOREIGN KEY (subSectorCode) REFERENCES solution_setting_sub_sector(code) ON DELETE CASCADE ON UPDATE CASCADE
|
|
159
|
);
|
|
160
|
|
|
161
|
ALTER TABLE company
|
|
162
|
ADD COLUMN companySizeCode VARCHAR(255),
|
|
163
|
ADD COLUMN valueChainCode VARCHAR(255),
|
|
164
|
ADD COLUMN businessSectorCode VARCHAR(255),
|
|
165
|
ADD COLUMN geographicExtentCode VARCHAR(255),
|
|
166
|
ADD COLUMN salesTurnoverCode VARCHAR(255),
|
|
167
|
ADD COLUMN countryCode VARCHAR(255),
|
|
168
|
ADD FOREIGN KEY (companySizeCode) REFERENCES solution_setting_company_size(code) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
169
|
ADD FOREIGN KEY (valueChainCode) REFERENCES solution_setting_value_chain(code) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
170
|
ADD FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
171
|
ADD FOREIGN KEY (geographicExtentCode) REFERENCES solution_setting_geographic_extent(code) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
172
|
ADD FOREIGN KEY (salesTurnoverCode) REFERENCES solution_setting_sales_turnover(code) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
173
|
ADD FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
174
|
|
|
175
|
INSERT INTO solution_setting_country (id, code, active) VALUES
|
|
176
|
(1, 'TN', 1),
|
|
177
|
(2, 'FR', 1);
|
|
178
|
INSERT INTO solution_setting_country_translate (id, label, countryCode, languageId) VALUES
|
|
179
|
(1, 'Tunisia', 'TN', 1), -- Traduction en anglais pour le pays TN
|
|
180
|
(2, 'Tunisie', 'TN', 2), -- Traduction en français pour le pays TN
|
|
181
|
(3, 'France', 'FR', 1), -- Traduction en anglais pour le pays FR
|
|
182
|
(4, 'France', 'FR', 2); -- Traduction en français pour le pays FR
|
|
183
|
|
|
184
|
INSERT INTO solution_setting_business_sector (id, code) VALUES
|
|
185
|
(1, 'BS001'),
|
|
186
|
(2, 'BS002'),
|
|
187
|
(3, 'BS003'),
|
|
188
|
(4, 'BS004');
|
|
189
|
|
|
190
|
INSERT INTO solution_setting_business_sector_translate (id, label, businessSectorCode, languageId) VALUES
|
|
191
|
(1, 'IT', 'BS001', 1),
|
|
192
|
(2, 'Informatique', 'BS001', 2),
|
|
193
|
(3, 'Automobile', 'BS002', 1),
|
|
194
|
(4, 'Automobile', 'BS002', 2),
|
|
195
|
(5, 'Pharmaceutical industry', 'BS003', 1),
|
|
196
|
(6, 'Industrie pharmaceutique', 'BS003', 2),
|
|
197
|
(7, 'Agrifood', 'BS004', 1),
|
|
198
|
(8, 'Agroalimentaire', 'BS004', 2);
|
|
199
|
|
|
200
|
INSERT INTO solution_setting_sales_turnover (id, code) VALUES
|
|
201
|
(1, 'CA001'),
|
|
202
|
(2, 'CA002'),
|
|
203
|
(3, 'CA003'),
|
|
204
|
(4, 'CA004'),
|
|
205
|
(5, 'CA005');
|
|
206
|
|
|
207
|
INSERT INTO solution_setting_sales_turnover_translate (id, label, salesTurnoverCode, languageId) VALUES
|
|
208
|
(1, '1 - 10000', 'CA001', 1),
|
|
209
|
(2, '1 - 10000', 'CA001', 2),
|
|
210
|
(3, '10000 - 100000', 'CA002', 1),
|
|
211
|
(4, '10000 - 100000', 'CA002', 2),
|
|
212
|
(5, '100000 - 500000', 'CA003', 1),
|
|
213
|
(6, '100000 - 500000', 'CA003', 2),
|
|
214
|
(7, '500000 - 1000000', 'CA004', 1),
|
|
215
|
(8, '500000 - 1000000', 'CA004', 2),
|
|
216
|
(9, 'Over 100,000', 'CA005', 1),
|
|
217
|
(10, 'Plus de 100000', 'CA005', 2);
|
|
218
|
|
|
219
|
INSERT INTO solution_setting_company_size (id, code) VALUES
|
|
220
|
(1, 'CS001'),
|
|
221
|
(2, 'CS002'),
|
|
222
|
(3, 'CS003'),
|
|
223
|
(4, 'CS004'),
|
|
224
|
(5, 'CS005'),
|
|
225
|
(6, 'CS006'),
|
|
226
|
(7, 'CS007'),
|
|
227
|
(8, 'CS008'),
|
|
228
|
(9, 'CS009'),
|
|
229
|
(10, 'CS0010');
|
|
230
|
|
|
231
|
INSERT INTO solution_setting_company_size_translate (id, label, companySizeCode, languageId) VALUES
|
|
232
|
(1, 'Less than 10', 'CS001', 1),
|
|
233
|
(2, 'Moins de 10', 'CS001', 2),
|
|
234
|
(3, 'Between 11 and 20', 'CS002', 1),
|
|
235
|
(4, 'Entre 11 et 20', 'CS002', 2),
|
|
236
|
(5, 'Between 21 and 30', 'CS003', 1),
|
|
237
|
(6, 'Entre 21 et 30', 'CS003', 2),
|
|
238
|
(7, 'Between 31 and 50', 'CS004', 1),
|
|
239
|
(8, 'Entre 31 et 50', 'CS004', 2),
|
|
240
|
(9, 'Between 51 and 100', 'CS005', 1),
|
|
241
|
(10, 'Entre 51 et 100', 'CS005', 2),
|
|
242
|
(11, 'Between 101 and 300', 'CS006', 1),
|
|
243
|
(12, 'Entre 101 et 300', 'CS006', 2),
|
|
244
|
(13, 'Between 301 and 500', 'CS007', 1),
|
|
245
|
(14, 'Entre 301 et 500', 'CS007', 2),
|
|
246
|
(15, 'Between 501 and 1000', 'CS008', 1),
|
|
247
|
(16, 'Entre 501 et 1000', 'CS008', 2),
|
|
248
|
(17, 'Between 1001 and 2000', 'CS009', 1),
|
|
249
|
(18, 'Entre 1001 et 2000', 'CS009', 2),
|
|
250
|
(19, 'Over 2001', 'CS0010', 1),
|
|
251
|
(20, 'Plus de 2001', 'CS0010', 2);
|
|
252
|
|
|
253
|
INSERT INTO solution_setting_value_chain (id, code) VALUES
|
|
254
|
(1, 'VC001'),
|
|
255
|
(2, 'VC002');
|
|
256
|
|
|
257
|
INSERT INTO solution_setting_value_chain_translate (id, label, valueChainCode, languageId) VALUES
|
|
258
|
(1, 'Full control of the entire value chain', 'VC001', 1),
|
|
259
|
(2, 'Contrôle total de toute la chaine de valeur', 'VC001', 2),
|
|
260
|
(3, 'Partial control of the value chain', 'VC002', 1),
|
|
261
|
(4, 'Contrôle partiel de la chaine de valeur', 'VC002', 2);
|
|
262
|
|
|
263
|
INSERT INTO solution_setting_geographic_extent (id, code) VALUES
|
|
264
|
(1, 'GE001'),
|
|
265
|
(2, 'GE002'),
|
|
266
|
(3, 'GE003'),
|
|
267
|
(4, 'GE004');
|
|
268
|
|
|
269
|
INSERT INTO solution_setting_geographic_extent_translate (id, label, geographicExtentCode, languageId) VALUES
|
|
270
|
(1, 'Present in one country', 'GE001', 1),
|
|
271
|
(2, 'Présent sur un seul pays', 'GE001', 2),
|
|
272
|
(3, 'Present in several countries of the same region', 'GE002', 1),
|
|
273
|
(4, 'Présent sur plusieurs pays d’une même région', 'GE002', 2),
|
|
274
|
(5, 'Present in several countries on two different continents', 'GE003', 1),
|
|
275
|
(6, 'Présent sur plusieurs pays de deux continents différents', 'GE003', 2),
|
|
276
|
(7, 'Present on more than two continents', 'GE004', 1),
|
|
277
|
(8, 'Présent sur plus de deux continents', 'GE004', 2);
|
|
278
|
|
|
279
|
|
|
280
|
|
|
281
|
|