Projet

Général

Profil

PROD V(22) ACTUEL » 5_quiz_1_The_Policies_and_Practices_of_Remuneration_v2.sql

Tarek AOUADI, 15/08/2025 18:06

 
1
use salarymarket;
2
-- Insert into solution_quiz for Quiz 1
3
INSERT INTO solution_quiz (id, quizCode, quiz_image, averagetime)
4
VALUES 
5
  (1, 'quiz_1_The_Policies_and_Practices_of_Remuneration',
6
   'Questionnaire 1 -Les Politiques et pratiques de Rémunération.jpg',
7
   15);
8
-- Insert into solution_quiz_translate for Quiz 1 (French and English)
9
INSERT INTO solution_quiz_translate (quizCode, title, description, short_description, languageId)
10
VALUES 
11
  ('quiz_1_The_Policies_and_Practices_of_Remuneration', 
12
   'Questionnaire 1 -Les Politiques et pratiques de Rémunération',
13
   'Exploration des politiques et pratiques de rémunération au sein de l\'organisation',
14
   'Exploration des politiques et pratiques de rémunération au sein de l\'organisation',
15
    2),
16
  ('quiz_1_The_Policies_and_Practices_of_Remuneration', 
17
   'Quiz 1 - Remuneration Policies and Practices',
18
   'Exploration of remuneration policies and practices within the organization',
19
   'Exploration of remuneration policies and practices within the organization',
20
    1);
21
--  Insert into solution_quiz_section for Quiz 1
22
INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order)
23
VALUES
24
  ('Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 1),
25
  ('Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 2),
26
  ('Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 3),
27
  ('Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 4);
28
-- 4. Insert translations for quiz sections (English then French)
29
INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)
30
VALUES
31
  ('Remuneration_Policies', 'Remuneration Policies', 1),
32
  ('Hiring_Salaries', 'Hiring Salaries', 1),
33
  ('Variable_Remuneration', 'Variable Remuneration', 1),
34
  ('Seniority_Retribution', 'Seniority Retribution', 1);
35
INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)
36
VALUES
37
  ('Remuneration_Policies', 'Politiques de rémunération', 2),
38
  ('Hiring_Salaries', 'Salaires d''embauche', 2),
39
  ('Variable_Remuneration', 'Rémunération variable', 2),
40
  ('Seniority_Retribution', 'Rétribution de l''ancienneté', 2);
41
-- Insert into solution_quiz_section_questions for Quiz 1
42
-- Section "Remuneration Policies" (Questions 1–3)
43
INSERT INTO solution_quiz_section_questions 
44
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
45
VALUES
46
  ('Q1-RP-1', 'Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL,      'CU', 1,  'Pie'),
47
  ('Q1-RP-2', 'Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 'Q1-RP-1','CU', 2,  'Bar'),
48
  ('Q1-RP-3', 'Remuneration_Policies', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 'Q1-RP-1','CM', 3,  'Bar');
49
-- Section "Hiring Salaries" (Questions 4–6)
50
INSERT INTO solution_quiz_section_questions 
51
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
52
VALUES
53
  ('Q1-HS-4', 'Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL, 'CM', 4, 'Bar'),
54
  ('Q1-HS-5', 'Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL, 'CU', 5, 'Pie'),
55
  ('Q1-HS-6', 'Hiring_Salaries', 'quiz_1_The_Policies_and_Practices_of_Remuneration', 'Q1-HS-5','CM', 6, 'tab_num');
56
-- Section "Variable Remuneration" (Questions 7–11)
57
INSERT INTO solution_quiz_section_questions 
58
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
59
VALUES
60
  ('Q1-VR-7',  'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL,      'CU', 7,  'Pie'),
61
  ('Q1-VR-8',  'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-VR-7','CM_all', 8,  'Table'),
62
  ('Q1-VR-9',  'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-VR-7','CM', 9,  'Bar'),
63
  ('Q1-VR-10', 'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration',NULL,'CM', 10, 'tab_percent'),
64
  ('Q1-VR-11', 'Variable_Remuneration', 'quiz_1_The_Policies_and_Practices_of_Remuneration',NULL,'CM', 11, 'Bar');
65
-- Section "Seniority Retribution" (Questions 12–15)
66
INSERT INTO solution_quiz_section_questions 
67
  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)
68
VALUES
69
  ('Q1-SR-12', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration', NULL, 'CU', 12, 'Pie'),
70
  ('Q1-SR-13', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-SR-12','CU', 13, 'Bar'),
71
  ('Q1-SR-14', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-SR-12','CM', 14, 'Bar'),
72
  ('Q1-SR-15', 'Seniority_Retribution', 'quiz_1_The_Policies_and_Practices_of_Remuneration','Q1-SR-12','CU', 15, 'Bar');
73
--  Insert translations for questions in Quiz 1
74
-- French translations
75
INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)
76
VALUES
77
  ('Q1-RP-1', 'Quelle est la politique générale de rémunération de votre entreprise ?', 2),
78
  ('Q1-RP-2', 'À quelle fréquence cette politique est-elle révisée ?', 2),
79
  ('Q1-RP-3', 'Quels sont les objectifs principaux de votre politique de rémunération ?', 2),
80
  ('Q1-HS-4', 'Quels critères utilisez-vous pour déterminer les salaires d''embauche ?', 2),
81
  ('Q1-HS-5', 'Proposez-vous des salaires d''embauche différenciés selon les métiers et disciplines ?', 2),
82
  ('Q1-HS-6', 'Veuillez noter les salaires d''entrées par fonction', 2),
83
  ('Q1-VR-7', 'Votre entreprise propose-t-elle une rémunération variable (primes, bonus) ?', 2),
84
  ('Q1-VR-8', 'Quelles sont les catégories de collaborateurs concernées par cette rémunération variable ?', 2),
85
  ('Q1-VR-9', 'Quels sont les types de rémunérations variables proposés ?', 2),
86
  ('Q1-VR-10', 'En cas de note, quel pourcentage accordez-vous en termes de poids aux critères suivants ?', 2),
87
  ('Q1-VR-11', 'Comment votre entreprise communique-t-elle les critères et les objectifs de la rémunération variable aux employés ?', 2),
88
  ('Q1-SR-12', 'Votre entreprise tient-elle compte de l''ancienneté dans sa politique de rémunération ?', 2),
89
  ('Q1-SR-13', 'À partir de combien d''années, le collaborateur est-il éligible à la rétribution à l''ancienneté ?', 2),
90
  ('Q1-SR-14', 'Qu''offrez-vous comme rétribution à l''ancienneté ?', 2),
91
  ('Q1-SR-15', 'En cas de rétribution monétaire relative à l''ancienneté, quelles modalités pratiquez-vous ?', 2);
92
-- English translations
93
INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)
94
VALUES
95
  ('Q1-RP-1', 'What is your company''s general remuneration policy?', 1),
96
  ('Q1-RP-2', 'How frequently is this policy reviewed?', 1),
97
  ('Q1-RP-3', 'What are the main objectives of your remuneration policy?', 1),
98
  ('Q1-HS-4', 'What criteria do you use to determine hiring salaries?', 1),
99
  ('Q1-HS-5', 'Do you offer differentiated hiring salaries by job and discipline?', 1),
100
  ('Q1-HS-6', 'Please specify the entry-level salaries by function', 1),
101
  ('Q1-VR-7', 'Does your company offer variable remuneration (bonuses, incentives)?', 1),
102
  ('Q1-VR-8', 'Which categories of employees are covered by this variable remuneration?', 1),
103
  ('Q1-VR-9', 'What types of variable remunerations are offered?', 1),
104
  ('Q1-VR-10', 'If applicable, what percentage weight do you assign to the following criteria?', 1),
105
  ('Q1-VR-11', 'How does your company communicate the criteria and objectives of variable remuneration to employees?', 1),
106
  ('Q1-SR-12', 'Does your company consider seniority in its remuneration policy?', 1),
107
  ('Q1-SR-13', 'After how many years is an employee eligible for seniority retribution?', 1),
108
  ('Q1-SR-14', 'What form of seniority retribution do you offer?', 1),
109
  ('Q1-SR-15', 'For monetary retribution related to seniority, what modalities do you apply?', 1);
110
--  Insert into solution_reponses_items 
111
INSERT INTO solution_reponses_items (itemCode, itemName)
112
VALUES
113
  ('formal-policy','Formal remuneration policy'),
114
  ('informal-policy', 'Informal remuneration policy'),
115
  ('every-two-years','Every two years'),
116
  ('every-three-years','Every three years'),
117
  ('every-five-years','Every five years'),
118
  ('talent-attraction','Talent attraction and retention'),
119
  ('practice-management', 'Better management and consistency of practices'),
120
  ('regulatory-compliance', 'Compliance with regulations'),
121
  ('experience-skills', 'Candidate experience and skills'),
122
  ('market-comparison', 'Market comparison'),
123
  ('company-budget',  'Company budget'),
124
  ('responsibility-level',  'Job responsibility level'),
125
  ('executives',  'Executives'),
126
  ('managers', 'Managers'),
127
  ('sales', 'Sales'),
128
  ('professionals','Professionals'),
129
  ('technical-staff', 'Technical staff'),
130
  ('administrative','Administrative staff'),
131
  ('labor',  'Labor force'),
132
  ('annual-bonus', 'Annual bonus'),
133
  ('project-bonus', 'Project performance bonus'),
134
  ('stock-options',  'Stock options'),
135
  ('written-documents','Written documents'),
136
  ('individual-interviews', 'Individual interviews'),
137
  ('not-communicated', 'Not communicated'),
138
  ('3-years',  '3 years'),
139
  ('5-years',  '5 years'),
140
  ('7-years',  '7 years'),
141
  ('10-years', '10 years'),
142
  ('automatic-increase',   'Automatic salary increase'),
143
  ('seniority-bonus', 'Seniority bonus'),
144
  ('extra-vacation',  'Extra vacation days'),
145
  ('fixed-amount','Fixed amount'),
146
  ('percentage-of-salary','Percentage of annual salary'),
147
  ('yes', 'Yes'),
148
  ('no', 'No'),
149
  ('individual-performance',  'Individual performance'),
150
  ('team-performance',  'Team performance'),
151
  ('company-performance', 'Company performance'),
152
  
153
  ('other-please-specify', 'Autre (veuillez préciser)'),
154
  ('annually', 'Annually');
155
--  Link items to questions via solution_quiz_section_questions_items
156
INSERT INTO solution_quiz_section_questions_items (itemCode, questionCode, itemParentId, isOther)
157
VALUES
158
  -- Q1-RP-1
159
  ('formal-policy', 'Q1-RP-1', NULL, 0),
160
  ('informal-policy', 'Q1-RP-1', NULL, 0),
161
  -- Q1-RP-2
162
  ('annually', 'Q1-RP-2', 'formal-policy', 0),
163
  ('every-two-years', 'Q1-RP-2', 'formal-policy', 0),
164
  ('every-three-years', 'Q1-RP-2', 'formal-policy', 0),
165
  ('every-five-years', 'Q1-RP-2', 'formal-policy', 0),
166
  ('other-please-specify', 'Q1-RP-2', 'formal-policy', 1),
167
 
168
  -- Q1-RP-3
169
  ('talent-attraction', 'Q1-RP-3','formal-policy', 0),
170
  ('practice-management', 'Q1-RP-3', 'formal-policy', 0),
171
  ('regulatory-compliance', 'Q1-RP-3', 'formal-policy', 0),
172
  ('other-please-specify', 'Q1-RP-3','formal-policy', 1),
173
  -- Q1-HS-4
174
  ('experience-skills', 'Q1-HS-4', NULL, 0),
175
  ('market-comparison', 'Q1-HS-4', NULL, 0),
176
  ('company-budget', 'Q1-HS-4', NULL, 0),
177
  ('responsibility-level', 'Q1-HS-4', NULL, 0),
178
  ('other-please-specify', 'Q1-HS-4', NULL, 1),
179
  -- Q1-HS-5
180
  ('yes', 'Q1-HS-5', NULL, 0),
181
  ('no', 'Q1-HS-5', NULL, 0),
182
  -- Q1-HS-6
183
 ('executives', 'Q1-HS-6', 'yes', 1),
184
  ('managers', 'Q1-HS-6', 'yes', 1),
185
  ('sales', 'Q1-HS-6', 'yes', 1),
186
  ('professionals', 'Q1-HS-6', 'yes', 1),
187
  ('technical-staff', 'Q1-HS-6', 'yes', 1),
188
  ('administrative', 'Q1-HS-6','yes', 1),
189
  
190
  ('labor', 'Q1-HS-6', 'yes', 1),
191
  -- Q1-VR-7
192
  ('yes', 'Q1-VR-7', NULL, 0),
193
  ('no', 'Q1-VR-7', NULL, 0),
194
  -- Q1-VR-8
195
  ('executives', 'Q1-VR-8', 'yes', 0),
196
  ('managers', 'Q1-VR-8', 'yes', 0),
197
  ('sales', 'Q1-VR-8', 'yes', 0),
198
  ('professionals', 'Q1-VR-8', 'yes', 0),
199
  ('technical-staff', 'Q1-VR-8', 'yes', 0),
200
  ('administrative', 'Q1-VR-8','yes', 0),
201
  ('labor', 'Q1-VR-8', 'yes', 0),
202
  -- Q1-VR-9
203
  ('annual-bonus', 'Q1-VR-9', 'yes', 0),
204
  ('project-bonus', 'Q1-VR-9', 'yes', 0),
205
  ('stock-options', 'Q1-VR-9', 'yes', 0),
206
  ('other-please-specify', 'Q1-VR-9','yes', 1),
207
  -- Q1-VR-10
208
  ('individual-performance', 'Q1-VR-10', 'yes', 1),
209
  ('team-performance', 'Q1-VR-10', 'yes', 1),
210
  ('company-performance', 'Q1-VR-10', 'yes', 1),
211
  ('other-please-specify', 'Q1-VR-10', 'yes', 1),
212
  -- Q1-VR-11
213
  ('written-documents', 'Q1-VR-11', NULL, 0),
214
  ('individual-interviews', 'Q1-VR-11', NULL, 0),
215
  ('not-communicated', 'Q1-VR-11', NULL, 0),
216
  ('other-please-specify', 'Q1-VR-11', NULL, 1),
217
  -- Q1-SR-12
218
  ('yes', 'Q1-SR-12', NULL, 0),
219
  ('no', 'Q1-SR-12', NULL, 0),
220
  -- Q1-SR-13
221
  ('3-years', 'Q1-SR-13', 'yes', 0),
222
  ('5-years', 'Q1-SR-13', 'yes', 0),
223
  ('7-years', 'Q1-SR-13', 'yes', 0),
224
  ('10-years', 'Q1-SR-13', 'yes', 0),
225
  ('other-please-specify', 'Q1-SR-13','yes', 1),
226
  -- Q1-SR-14
227
  ('automatic-increase', 'Q1-SR-14', 'yes', 0),
228
  ('seniority-bonus', 'Q1-SR-14', 'yes', 0),
229
  ('extra-vacation', 'Q1-SR-14', 'yes', 0),
230
  ('other-please-specify', 'Q1-SR-14', 'yes', 1),
231
  -- Q1-SR-15
232
  ('fixed-amount', 'Q1-SR-15','yes', 0),
233
  ('percentage-of-salary', 'Q1-SR-15', 'yes', 0),
234
  ('other-please-specify', 'Q1-SR-15', 'yes', 1);
235
-- Insert into solution_quiz_question_items_translate for item value translations
236
INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)
237
VALUES
238
  ('formal-policy','Formal remuneration policy',1),
239
  ('informal-policy', 'Informal remuneration policy',1),
240
  ('every-two-years','Every two years', 1),
241
  ('every-three-years',  'Every three years', 1),
242
  ('every-five-years','Every five years', 1),
243
  ('talent-attraction', 'Talent attraction and retention', 1),
244
  ('practice-management','Better management and consistency of practices', 1),
245
  ('regulatory-compliance','Compliance with regulations', 1),
246
  ('experience-skills','Candidate experience and skills', 1),
247
  ('market-comparison','Market comparison', 1),
248
  ('company-budget', 'Company budget', 1),
249
  ('responsibility-level','Job responsibility level',1),
250
  ('executives', 'Executives',1),
251
  ('managers', 'Managers', 1),
252
  ('sales', 'Sales',  1),
253
  ('professionals','Professionals',1),
254
  ('technical-staff','Technical staff',1),
255
  ('administrative','Administrative staff', 1),
256
  ('labor','Labor force',1),
257
  ('annual-bonus','Annual bonus', 1),
258
  ('project-bonus','Project performance bonus',1),
259
  ('stock-options','Stock options', 1),
260
  ('written-documents','Written documents',1),
261
  ('individual-interviews','Individual interviews',1),
262
  ('not-communicated','Not communicated',1),
263
  ('3-years','3 years',1),
264
  ('5-years','5 years',1),
265
  ('7-years','7 years', 1),
266
  ('10-years','10 years',1),
267
  ('automatic-increase','Automatic salary increase',1),
268
  ('seniority-bonus','Seniority bonus', 1),
269
  ('extra-vacation','Extra vacation days',1),
270
  ('fixed-amount','Fixed amount', 1),
271
  ('percentage-of-salary','Percentage of annual salary', 1),
272

    
273
  ('yes', 'Yes', 1),
274
  ('no', 'No', 1),
275
  ('individual-performance',  'Individual performance',   1),
276
  ('team-performance',   'Team performance', 1),
277
  ('company-performance', 'Company performance',  1),
278
('other-please-specify','Other (Please Specify)', 1),
279
  ('annually', 'Annually',  1);
280
  
281
-- insert with French language
282
INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)
283
VALUES
284
  ('formal-policy',            'Politique de rémunération formelle',            2),
285
  ('informal-policy',          'Politique de rémunération informelle',          2),
286
  ('every-two-years',          'Tous les deux ans',                             2),
287
  ('every-three-years',        'Tous les trois ans',                            2),
288
  ('every-five-years',         'Tous les cinq ans',                             2),
289
  ('talent-attraction',        'Attraction et rétention des talents',           2),
290
  ('practice-management',      'Meilleure gestion et cohérence des pratiques',  2),
291
  ('regulatory-compliance',    'Conformité avec les réglementations',           2),
292
  ('experience-skills',        'Expérience et compétences du candidat',         2),
293
  ('market-comparison',        'Comparaison avec le marché',                    2),
294
  ('company-budget',           'Budget de l''entreprise',                       2),
295
  ('responsibility-level',     'Niveau de responsabilité du poste',             2),
296
  ('executives',               'Dirigeants',                                    2),
297
  ('managers',                 'Managers',                                      2),
298
  ('sales',                    'Commercial',                                    2),
299
  ('professionals',            'Professionnels',                                2),
300
  ('technical-staff',          'Assistanat Technique',                           2),
301
  ('administrative',           'Assistanat  Professionnel',                                 2),
302
  ('labor',                    'Main d''œuvre',                                 2),
303
  ('annual-bonus',             'Primes annuelles',        2),
304
  ('project-bonus',            'Bonus de performance par projet',               2),
305
  ('stock-options',            'Actions ou stock-options',                      2),
306
  ('written-documents',        'Documents écrits',                              2),
307
  ('individual-interviews',    'Entretiens individuels',                        2),
308
  ('not-communicated',         'Non communiqués',                               2),
309
  ('3-years',                  '3 ans',                                         2),
310
  ('5-years',                  '5 ans',                                         2),
311
  ('7-years',                  '7 ans',                                         2),
312
  ('10-years',                 '10 ans',                                        2),
313
  ('automatic-increase',       'Augmentation salariale automatique',            2),
314
  ('seniority-bonus',          'Prime d''ancienneté',                           2),
315
  ('extra-vacation',           'Jours de congés supplémentaires',               2),
316
  ('fixed-amount',             'Montant fixe (forfaitaire)',     2),
317
  ('percentage-of-salary',     'Pourcentage du salaire annuel',                 2),
318
  ('yes',                      'Oui',                                           2),
319
  ('no',                       'Non',                                           2),
320
  ('individual-performance',   'Performance individuelle',                      2),
321
  ('team-performance',         'Performance d’équipe',                          2),
322
  ('company-performance',      'Performance de l’entreprise',                   2),
323
 ('other-please-specify', 'Autre (veuillez préciser)', 2),
324
  ('annually',                 'Annuellement',                                  2);
(18-18/25)