|
1
|
use salarymarket;
|
|
2
|
-- quiz 5 Éléments garantis et Allocations”
|
|
3
|
-- 1) Quiz definition
|
|
4
|
INSERT INTO solution_quiz (id, quizCode, quiz_image, averagetime)
|
|
5
|
VALUES
|
|
6
|
(5, 'quiz_5_Elements_Garantis_Allocations',
|
|
7
|
'Allocations en espèces.jpg',
|
|
8
|
20);
|
|
9
|
-- 2) Quiz translations
|
|
10
|
INSERT INTO solution_quiz_translate (quizCode, title, description, short_description, languageId)
|
|
11
|
VALUES
|
|
12
|
('quiz_5_Elements_Garantis_Allocations',
|
|
13
|
'Questionnaire 5 – Éléments garantis et allocations',
|
|
14
|
'Étude des éléments garantis et allocations en espèces offerts par l’entreprise',
|
|
15
|
'Étude des éléments garantis et allocations en espèces',
|
|
16
|
2),
|
|
17
|
('quiz_5_Elements_Garantis_Allocations',
|
|
18
|
'Quiz 5 – Guaranteed Elements and Cash Allowances',
|
|
19
|
'Survey of guaranteed elements and cash allowances provided by the company',
|
|
20
|
'Survey of guaranteed elements and cash allowances',
|
|
21
|
1);
|
|
22
|
-- 3) Sections
|
|
23
|
INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order)
|
|
24
|
VALUES
|
|
25
|
('Fixed_Salary_Months', 'quiz_5_Elements_Garantis_Allocations', 1),
|
|
26
|
('Transport_Fee_Contribution', 'quiz_5_Elements_Garantis_Allocations', 2),
|
|
27
|
('Transport_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 3),
|
|
28
|
('Housing_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 4),
|
|
29
|
('Personal_Loans_Details', 'quiz_5_Elements_Garantis_Allocations', 5),
|
|
30
|
('Social_Association_Indemnities', 'quiz_5_Elements_Garantis_Allocations', 6);
|
|
31
|
-- 4) Section translations
|
|
32
|
INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)
|
|
33
|
VALUES
|
|
34
|
-- English
|
|
35
|
('Fixed_Salary_Months', 'Fixed Salary Months', 1),
|
|
36
|
('Transport_Fee_Contribution', 'Transport Fee Contribution', 1),
|
|
37
|
('Transport_Allocation_Details', 'Transport Allowance Details', 1),
|
|
38
|
('Housing_Allocation_Details', 'Housing Allowance Details', 1),
|
|
39
|
('Personal_Loans_Details', 'Personal Loans Details', 1),
|
|
40
|
('Social_Association_Indemnities', 'Club & Association Indemnities',1),
|
|
41
|
-- French
|
|
42
|
('Fixed_Salary_Months', 'Nombre de mois de salaire', 2),
|
|
43
|
('Transport_Fee_Contribution', 'Participation frais transport',2),
|
|
44
|
('Transport_Allocation_Details', 'Détails allocation transport',2),
|
|
45
|
('Housing_Allocation_Details', 'Détails allocation logement', 2),
|
|
46
|
('Personal_Loans_Details', 'Détails prêts personnels', 2),
|
|
47
|
('Social_Association_Indemnities', 'Indemnités clubs & associations',2);
|
|
48
|
-- 5) Questions & chart types
|
|
49
|
INSERT INTO solution_quiz_section_questions
|
|
50
|
(questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
|
|
51
|
VALUES
|
|
52
|
-- Section 1
|
|
53
|
('Q5-FSM-1', 'Fixed_Salary_Months', 'quiz_5_Elements_Garantis_Allocations', NULL, 'CU', 1, 'Pie'),
|
|
54
|
-- Section 2
|
|
55
|
('Q5-TFC-2', 'Transport_Fee_Contribution', 'quiz_5_Elements_Garantis_Allocations', NULL, 'CU', 2, 'Pie'),
|
|
56
|
('Q5-TFC-3', 'Transport_Fee_Contribution', 'quiz_5_Elements_Garantis_Allocations', 'Q5-TFC-2','CM',3, 'Table'),
|
|
57
|
-- Section 3
|
|
58
|
('Q5-TAD-4', 'Transport_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', NULL, 'CU', 4, 'Pie'),
|
|
59
|
('Q5-TAD-5', 'Transport_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-TAD-4','CM',5, 'Table'),
|
|
60
|
('Q5-TAD-6', 'Transport_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-TAD-4','CM',6, 'Pie'),
|
|
61
|
('Q5-TAD-7', 'Transport_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-TAD-6','CM',7, 'Bar'),
|
|
62
|
-- Section 4
|
|
63
|
('Q5-HAD-8', 'Housing_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', NULL, 'CU', 8, 'Pie'),
|
|
64
|
('Q5-HAD-9', 'Housing_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-HAD-8','CM',9, 'Table'),
|
|
65
|
('Q5-HAD-10', 'Housing_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-HAD-8','CU',10, 'Pie'),
|
|
66
|
('Q5-HAD-11', 'Housing_Allocation_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-HAD-10','CM',11,'Bar'),
|
|
67
|
-- Section 5
|
|
68
|
('Q5-PLD-12','Personal_Loans_Details', 'quiz_5_Elements_Garantis_Allocations', NULL, 'CU',12, 'Pie'),
|
|
69
|
('Q5-PLD-13','Personal_Loans_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-PLD-12','CM',13,'Table'),
|
|
70
|
('Q5-PLD-14','Personal_Loans_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-PLD-12','CM',14,'Bar'),
|
|
71
|
('Q5-PLD-15','Personal_Loans_Details', 'quiz_5_Elements_Garantis_Allocations', 'Q5-PLD-12','CM',15,'Bar'),
|
|
72
|
-- Section 6
|
|
73
|
('Q5-SAI-16','Social_Association_Indemnities', 'quiz_5_Elements_Garantis_Allocations', NULL, 'CU',16, 'Pie'),
|
|
74
|
('Q5-SAI-17','Social_Association_Indemnities', 'quiz_5_Elements_Garantis_Allocations', 'Q5-SAI-16','CM',17,'Table'),
|
|
75
|
('Q5-SAI-18','Social_Association_Indemnities', 'quiz_5_Elements_Garantis_Allocations', NULL, 'CU',18, 'Pie'),
|
|
76
|
('Q5-SAI-19','Social_Association_Indemnities', 'quiz_5_Elements_Garantis_Allocations', 'Q5-SAI-18','CM',19,'Table');
|
|
77
|
-- French translations for Quiz 5 questions
|
|
78
|
INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)
|
|
79
|
VALUES
|
|
80
|
('Q5-FSM-1', 'Nombre de mois de salaire de base fixes versés annuellement', 2),
|
|
81
|
('Q5-TFC-2', 'Votre entreprise contribue-t-elle aux frais de transport (exp bus de la société…)?', 2),
|
|
82
|
('Q5-TFC-3', 'Si oui, quels niveaux hiérarchiques sont concernés ?', 2),
|
|
83
|
('Q5-TAD-4', 'Votre entreprise fournit-elle une allocation de transport ?', 2),
|
|
84
|
('Q5-TAD-5', 'Si oui, quels niveaux hiérarchiques sont concernés ?', 2),
|
|
85
|
('Q5-TAD-6', 'Sous quelle forme est accordée cette allocation ?', 2),
|
|
86
|
('Q5-TAD-7', 'Quel est le montant moyen mensuel de cette allocation de transport ?', 2),
|
|
87
|
('Q5-HAD-8', 'Votre entreprise fournit-elle une allocation de logement ?', 2),
|
|
88
|
('Q5-HAD-9', 'Si oui, quels niveaux hiérarchiques sont concernés ?', 2),
|
|
89
|
('Q5-HAD-10', 'Si oui, sous quelle forme est accordée cette allocation logement ?', 2),
|
|
90
|
('Q5-HAD-11', 'Quel est le montant moyen mensuel ?', 2),
|
|
91
|
('Q5-PLD-12','Votre entreprise accorde-t-elle des prêts personnels ?', 2),
|
|
92
|
('Q5-PLD-13','Si oui, quels niveaux hiérarchiques sont concernés ?', 2),
|
|
93
|
('Q5-PLD-14','Montant maximum du prêt (multiple du salaire) ?', 2),
|
|
94
|
('Q5-PLD-15','Délai maximum de remboursement (mois) ?', 2),
|
|
95
|
('Q5-SAI-16','Indemnités clubs sociaux/sportifs ?', 2),
|
|
96
|
('Q5-SAI-17','Si oui, quels niveaux hiérarchiques sont concernés ?', 2),
|
|
97
|
('Q5-SAI-18','Indemnités adhésion association professionnelle ?', 2),
|
|
98
|
('Q5-SAI-19','Si oui, quels niveaux hiérarchiques sont concernés ?', 2);
|
|
99
|
-- English translations for Quiz 5 questions
|
|
100
|
INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)
|
|
101
|
VALUES
|
|
102
|
('Q5-FSM-1', 'Number of fixed base-salary months paid annually', 1),
|
|
103
|
('Q5-TFC-2', 'Does your company contribute to transport costs?', 1),
|
|
104
|
('Q5-TFC-3', 'If yes, which hierarchical levels are covered?', 1),
|
|
105
|
('Q5-TAD-4', 'Does your company provide a transport allowance?', 1),
|
|
106
|
('Q5-TAD-5', 'If yes, which hierarchical levels are covered?', 1),
|
|
107
|
('Q5-TAD-6', 'In what form is this allowance granted?', 1),
|
|
108
|
('Q5-TAD-7', 'What is the average monthly amount?', 1),
|
|
109
|
('Q5-HAD-8', 'Does your company provide a housing allowance?', 1),
|
|
110
|
('Q5-HAD-9', 'If yes, which hierarchical levels are covered?', 1),
|
|
111
|
('Q5-HAD-10', 'In what form is the housing allowance granted?', 1),
|
|
112
|
('Q5-HAD-11', 'What is the average monthly amount?', 1),
|
|
113
|
('Q5-PLD-12','Does your company grant personal loans?', 1),
|
|
114
|
('Q5-PLD-13','If yes, which hierarchical levels are covered?', 1),
|
|
115
|
('Q5-PLD-14','Maximum loan amount (multiple of salary)?', 1),
|
|
116
|
('Q5-PLD-15','Maximum repayment term (months)?', 1),
|
|
117
|
('Q5-SAI-16','Does your company pay club/sport indemnities?', 1),
|
|
118
|
('Q5-SAI-17','If yes, which hierarchical levels are covered?', 1),
|
|
119
|
('Q5-SAI-18','Does your company pay professional association indemnities?', 1),
|
|
120
|
('Q5-SAI-19','If yes, which hierarchical levels are covered?', 1);
|
|
121
|
|
|
122
|
|
|
123
|
-- 7) New response‐items for Quiz 5
|
|
124
|
INSERT INTO solution_reponses_items (itemCode, itemName)
|
|
125
|
VALUES
|
|
126
|
('commercial','commercial'),
|
|
127
|
('12_months', '12'),
|
|
128
|
('13_months', '13'),
|
|
129
|
('14_months', '14'),
|
|
130
|
('other_months', 'Other (Please Specify)'),
|
|
131
|
('transport_means', 'Company transport means'),
|
|
132
|
('fixed_housing', 'Fixed housing allowance'),
|
|
133
|
('housing_provision','Housing provided'),
|
|
134
|
('other_option', 'Other (Please Specify)'),
|
|
135
|
('reimbursement_actual_costs', 'Reimbursement of actual costs');
|
|
136
|
|
|
137
|
-- 8) Translations for those new items
|
|
138
|
INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)
|
|
139
|
VALUES
|
|
140
|
-- months
|
|
141
|
('12_months', '12', 1),
|
|
142
|
('12_months', '12', 2),
|
|
143
|
|
|
144
|
('13_months', '13', 1),
|
|
145
|
('13_months', '13', 2),
|
|
146
|
|
|
147
|
('14_months', '14', 1),
|
|
148
|
('14_months', '14', 2),
|
|
149
|
|
|
150
|
('other_months', 'Other (Please Specify)', 1),
|
|
151
|
('other_months', 'Autres (précisez)', 2),
|
|
152
|
-- transport
|
|
153
|
('transport_means', 'Company transport means', 1),
|
|
154
|
('transport_means', 'Mise à disposition de moyens de transport (navette…)', 2),
|
|
155
|
-- housing
|
|
156
|
('fixed_housing', 'Fixed housing allowance', 1),
|
|
157
|
('fixed_housing', 'Mise à disposition partielle ou totale d''un logement',2),
|
|
158
|
|
|
159
|
('housing_provision','Housing provided', 1),
|
|
160
|
('housing_provision','Mise à disposition partielle ou totale d\'un logement',2),
|
|
161
|
-- other
|
|
162
|
('other_option', 'Other (Please Specify)', 1),
|
|
163
|
('other_option', 'Autres (précisez)', 2),
|
|
164
|
-- reimbursement
|
|
165
|
('reimbursement_actual_costs', 'Reimbursement of actual costs', 1),
|
|
166
|
('reimbursement_actual_costs', 'Remboursement des frais réels', 2),
|
|
167
|
|
|
168
|
('commercial', 'Commercial staff', 1),
|
|
169
|
('commercial', 'Personnel commercial', 2);
|
|
170
|
|
|
171
|
-- 9) Link items to Quiz 5 questions (ordered Q1 → Q19)
|
|
172
|
INSERT INTO solution_quiz_section_questions_items (itemCode, questionCode, itemParentId, isOther)
|
|
173
|
VALUES
|
|
174
|
-- Q5-FSM-1
|
|
175
|
('12_months', 'Q5-FSM-1', NULL, 0),
|
|
176
|
('13_months', 'Q5-FSM-1', NULL, 0),
|
|
177
|
('14_months', 'Q5-FSM-1', NULL, 0),
|
|
178
|
('other_months', 'Q5-FSM-1', NULL, 1),
|
|
179
|
-- Q5-TFC-2
|
|
180
|
('yes', 'Q5-TFC-2', NULL, 0),
|
|
181
|
('no', 'Q5-TFC-2', NULL, 0),
|
|
182
|
-- Q5-TFC-3
|
|
183
|
('executives', 'Q5-TFC-3', 'yes', 0),
|
|
184
|
('middle_managers', 'Q5-TFC-3', 'yes', 0),
|
|
185
|
('managers', 'Q5-TFC-3', 'yes', 0),
|
|
186
|
('commercial', 'Q5-TFC-3', 'yes', 0),
|
|
187
|
('professionals', 'Q5-TFC-3', 'yes', 0),
|
|
188
|
('support_tech', 'Q5-TFC-3', 'yes', 0),
|
|
189
|
('support_professional','Q5-TFC-3', 'yes', 0),
|
|
190
|
('labor', 'Q5-TFC-3', 'yes', 0),
|
|
191
|
|
|
192
|
-- Q5-TAD-4
|
|
193
|
('yes', 'Q5-TAD-4', NULL, 0),
|
|
194
|
('no', 'Q5-TAD-4', NULL, 0),
|
|
195
|
-- Q5-TAD-5
|
|
196
|
('executives', 'Q5-TAD-5', 'yes', 0),
|
|
197
|
('middle_managers', 'Q5-TAD-5', 'yes', 0),
|
|
198
|
('managers', 'Q5-TAD-5', 'yes', 0),
|
|
199
|
('commercial', 'Q5-TAD-5', 'yes', 0),
|
|
200
|
('professionals', 'Q5-TAD-5', 'yes', 0),
|
|
201
|
('support_tech', 'Q5-TAD-5', 'yes', 0),
|
|
202
|
('support_professional','Q5-TAD-5', 'yes', 0),
|
|
203
|
('labor', 'Q5-TAD-5', 'yes', 0),
|
|
204
|
-- Q5-TAD-6
|
|
205
|
('fixed-amount', 'Q5-TAD-6', NULL, 0),
|
|
206
|
('reimbursement_actual_costs', 'Q5-TAD-6', NULL, 0),
|
|
207
|
('transport_means', 'Q5-TAD-6', NULL, 0),
|
|
208
|
-- Q5-TAD-7
|
|
209
|
('executives', 'Q5-TAD-7', 'fixed-amount', 0),
|
|
210
|
('middle_managers', 'Q5-TAD-7', 'fixed-amount', 0),
|
|
211
|
('managers', 'Q5-TAD-7', 'fixed-amount', 0),
|
|
212
|
('commercial', 'Q5-TAD-7', 'fixed-amount', 0),
|
|
213
|
('professionals', 'Q5-TAD-7', 'fixed-amount', 0),
|
|
214
|
('support_tech', 'Q5-TAD-7', 'fixed-amount', 0),
|
|
215
|
('support_professional','Q5-TAD-7', 'fixed-amount', 0),
|
|
216
|
('labor', 'Q5-TAD-7', 'fixed-amount', 0),
|
|
217
|
-- Q5-HAD-8
|
|
218
|
('yes', 'Q5-HAD-8', NULL, 0),
|
|
219
|
('no', 'Q5-HAD-8', NULL, 0),
|
|
220
|
-- Q5-HAD-9
|
|
221
|
('executives', 'Q5-HAD-9', 'yes', 0),
|
|
222
|
('middle_managers', 'Q5-HAD-9', 'yes', 0),
|
|
223
|
('managers', 'Q5-HAD-9', 'yes', 0),
|
|
224
|
('commercial', 'Q5-HAD-9', 'yes', 0),
|
|
225
|
('professionals', 'Q5-HAD-9', 'yes', 0),
|
|
226
|
('support_tech', 'Q5-HAD-9', 'yes', 0),
|
|
227
|
('support_professional','Q5-HAD-9', 'yes', 0),
|
|
228
|
('labor', 'Q5-HAD-9', 'yes', 0),
|
|
229
|
-- Q5-HAD-10
|
|
230
|
('fixed-amount', 'Q5-HAD-10', NULL, 0),
|
|
231
|
('housing_provision','Q5-HAD-10', NULL, 0),
|
|
232
|
-- Q5-HAD-11
|
|
233
|
('executives', 'Q5-HAD-11', 'fixed-amount', 0),
|
|
234
|
('middle_managers', 'Q5-HAD-11', 'fixed-amount', 0),
|
|
235
|
('managers', 'Q5-HAD-11', 'fixed-amount', 0),
|
|
236
|
('commercial', 'Q5-HAD-11', 'fixed-amount', 0),
|
|
237
|
('professionals', 'Q5-HAD-11', 'fixed-amount', 0),
|
|
238
|
('support_tech', 'Q5-HAD-11', 'fixed-amount', 0),
|
|
239
|
('support_professional','Q5-HAD-11', 'fixed-amount', 0),
|
|
240
|
('labor', 'Q5-HAD-11', 'fixed-amount', 0),
|
|
241
|
-- Q5-PLD-12
|
|
242
|
('yes', 'Q5-PLD-12', NULL, 0),
|
|
243
|
('no', 'Q5-PLD-12', NULL, 0),
|
|
244
|
-- Q5-PLD-13
|
|
245
|
('executives', 'Q5-PLD-13', 'yes', 0),
|
|
246
|
('middle_managers', 'Q5-PLD-13', 'yes', 0),
|
|
247
|
('managers', 'Q5-PLD-13', 'yes', 0),
|
|
248
|
('commercial', 'Q5-PLD-13', 'yes', 0),
|
|
249
|
('professionals', 'Q5-PLD-13', 'yes', 0),
|
|
250
|
('support_tech', 'Q5-PLD-13', 'yes', 0),
|
|
251
|
('support_professional','Q5-PLD-13', 'yes', 0),
|
|
252
|
('labor', 'Q5-PLD-13', 'yes', 0),
|
|
253
|
-- Q5-PLD-14
|
|
254
|
('executives', 'Q5-PLD-14', 'yes', 0),
|
|
255
|
('middle_managers', 'Q5-PLD-14', 'yes', 0),
|
|
256
|
('managers', 'Q5-PLD-14', 'yes', 0),
|
|
257
|
('commercial', 'Q5-PLD-14', 'yes', 0),
|
|
258
|
('professionals', 'Q5-PLD-14', 'yes', 0),
|
|
259
|
('support_tech', 'Q5-PLD-14', 'yes', 0),
|
|
260
|
('support_professional','Q5-PLD-14', 'yes', 0),
|
|
261
|
('labor', 'Q5-PLD-14', 'yes', 0),
|
|
262
|
-- Q5-PLD-15
|
|
263
|
('executives', 'Q5-PLD-15', 'yes', 0),
|
|
264
|
('middle_managers', 'Q5-PLD-15', 'yes', 0),
|
|
265
|
('managers', 'Q5-PLD-15', 'yes', 0),
|
|
266
|
('commercial', 'Q5-PLD-15', 'yes', 0),
|
|
267
|
('professionals', 'Q5-PLD-15', 'yes', 0),
|
|
268
|
('support_tech', 'Q5-PLD-15', 'yes', 0),
|
|
269
|
('support_professional','Q5-PLD-15', 'yes', 0),
|
|
270
|
('labor', 'Q5-PLD-15', 'yes', 0),
|
|
271
|
-- Q5-SAI-16
|
|
272
|
('yes', 'Q5-SAI-16', NULL, 0),
|
|
273
|
('no', 'Q5-SAI-16', NULL, 0),
|
|
274
|
-- Q5-SAI-17
|
|
275
|
('executives', 'Q5-SAI-17', 'yes', 0),
|
|
276
|
('middle_managers', 'Q5-SAI-17', 'yes', 0),
|
|
277
|
('managers', 'Q5-SAI-17', 'yes', 0),
|
|
278
|
('commercial', 'Q5-SAI-17', 'yes', 0),
|
|
279
|
('professionals', 'Q5-SAI-17', 'yes', 0),
|
|
280
|
('support_tech', 'Q5-SAI-17', 'yes', 0),
|
|
281
|
('support_professional','Q5-SAI-17', 'yes', 0),
|
|
282
|
('labor', 'Q5-SAI-17', 'yes', 0),
|
|
283
|
-- Q5-SAI-18
|
|
284
|
('yes', 'Q5-SAI-18', NULL, 0),
|
|
285
|
('no', 'Q5-SAI-18', NULL, 0),
|
|
286
|
-- Q5-SAI-19
|
|
287
|
('executives', 'Q5-SAI-19', 'yes', 0),
|
|
288
|
('middle_managers', 'Q5-SAI-19', 'yes', 0),
|
|
289
|
('managers', 'Q5-SAI-19', 'yes', 0),
|
|
290
|
('commercial', 'Q5-SAI-19', 'yes', 0),
|
|
291
|
('professionals', 'Q5-SAI-19', 'yes', 0),
|
|
292
|
('support_tech', 'Q5-SAI-19', 'yes', 0),
|
|
293
|
('support_professional','Q5-SAI-19', 'yes', 0),
|
|
294
|
('labor', 'Q5-SAI-19', 'yes', 0);
|