· 5 years ago · Jul 14, 2020, 09:04 AM
1IF NOT EXISTS (
2 SELECT 1
3 FROM dbo.Lists
4 WHERE ListName = 'Who is responsible for safety in the workplace?'
5 )
6BEGIN
7 DECLARE @ListValuesId INT;
8 DECLARE @AnswerId INT;
9
10 EXEC LIST_INSERT @AV_LISTNAME = N'Who is responsible for safety in the workplace?',
11 @AV_DESCRIPTION = N'',
12 @AV_CREATEDUSERID = 1,
13 @AV_UPDATEDUSERID = 1,
14 @AV_LISTID = @ListValuesId OUTPUT;
15
16 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
17 @AV_VALUENAME = N'a. One Staff',
18 @AV_DESCRIPTION = N'',
19 @AV_CREATEDUSERID = 1,
20 @AV_UPDATEDUSERID = 1;
21
22 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
23 @AV_VALUENAME = N'b. Everyone',
24 @AV_DESCRIPTION = N'',
25 @AV_CREATEDUSERID = 1,
26 @AV_UPDATEDUSERID = 1,
27 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
28
29 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
30 @AV_VALUENAME = N'c. The Supervisor',
31 @AV_DESCRIPTION = N'',
32 @AV_CREATEDUSERID = 1,
33 @AV_UPDATEDUSERID = 1;
34
35 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
36 @AV_VALUENAME = N'd. Only permanent employees',
37 @AV_DESCRIPTION = N'',
38 @AV_CREATEDUSERID = 1,
39 @AV_UPDATEDUSERID = 1;
40
41 UPDATE OneStaff.HealthSafetyQuestionare
42 SET Question = @ListValuesId,
43 Answer = @AnswerId
44 WHERE HealthSafetyQuestionareId = '1';
45END
46GO
47
48IF NOT EXISTS (
49 SELECT 1
50 FROM dbo.Lists
51 WHERE ListName = 'What is the most common cause of injuries?'
52 )
53BEGIN
54 DECLARE @ListValuesId INT;
55 DECLARE @AnswerId INT;
56
57 EXEC LIST_INSERT @AV_LISTNAME = N'What is the most common cause of injuries?',
58 @AV_DESCRIPTION = N'',
59 @AV_CREATEDUSERID = 1,
60 @AV_UPDATEDUSERID = 1,
61 @AV_LISTID = @ListValuesId OUTPUT;
62
63 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
64 @AV_VALUENAME = N'a. Poor Housekeeping',
65 @AV_DESCRIPTION = N'',
66 @AV_CREATEDUSERID = 1,
67 @AV_UPDATEDUSERID = 1,
68 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
69
70 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
71 @AV_VALUENAME = N'b. Electrocution',
72 @AV_DESCRIPTION = N'',
73 @AV_CREATEDUSERID = 1,
74 @AV_UPDATEDUSERID = 1;
75
76 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
77 @AV_VALUENAME = N'c. Pratical Jokes',
78 @AV_DESCRIPTION = N'',
79 @AV_CREATEDUSERID = 1,
80 @AV_UPDATEDUSERID = 1;
81
82 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
83 @AV_VALUENAME = N'd. Freak Accidents',
84 @AV_DESCRIPTION = N'',
85 @AV_CREATEDUSERID = 1,
86 @AV_UPDATEDUSERID = 1;
87
88 UPDATE OneStaff.HealthSafetyQuestionare
89 SET Question = @ListValuesId,
90 Answer = @AnswerId
91 WHERE HealthSafetyQuestionareId = '2';
92END
93GO
94
95IF NOT EXISTS (
96 SELECT 1
97 FROM dbo.Lists
98 WHERE ListName = 'Name 3 common types of hazards in the workplace:'
99 )
100BEGIN
101 DECLARE @ListValuesId INT;
102 DECLARE @AnswerId INT;
103
104 EXEC LIST_INSERT @AV_LISTNAME = N'Name 3 common types of hazards in the workplace:',
105 @AV_DESCRIPTION = N'',
106 @AV_CREATEDUSERID = 1,
107 @AV_UPDATEDUSERID = 1,
108 @AV_LISTID = @ListValuesId OUTPUT;
109
110 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
111 @AV_VALUENAME = N'a. Working at heights',
112 @AV_DESCRIPTION = N'',
113 @AV_CREATEDUSERID = 1,
114 @AV_UPDATEDUSERID = 1;
115
116 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
117 @AV_VALUENAME = N'b. Moving vehicles',
118 @AV_DESCRIPTION = N'',
119 @AV_CREATEDUSERID = 1,
120 @AV_UPDATEDUSERID = 1;
121
122 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
123 @AV_VALUENAME = N'c. Manual handling',
124 @AV_DESCRIPTION = N'',
125 @AV_CREATEDUSERID = 1,
126 @AV_UPDATEDUSERID = 1;
127
128 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
129 @AV_VALUENAME = N'd. All of the above',
130 @AV_DESCRIPTION = N'',
131 @AV_CREATEDUSERID = 1,
132 @AV_UPDATEDUSERID = 1,
133 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
134
135 UPDATE OneStaff.HealthSafetyQuestionare
136 SET Question = @ListValuesId,
137 Answer = @AnswerId
138 WHERE HealthSafetyQuestionareId = '3';
139END
140GO
141
142IF NOT EXISTS (
143 SELECT 1
144 FROM dbo.Lists
145 WHERE ListName = 'What is the best control in Hazard Management?'
146 )
147BEGIN
148 DECLARE @ListValuesId INT;
149 DECLARE @AnswerId INT;
150
151 EXEC LIST_INSERT @AV_LISTNAME = N'What is the best control in Hazard Management?',
152 @AV_DESCRIPTION = N'',
153 @AV_CREATEDUSERID = 1,
154 @AV_UPDATEDUSERID = 1,
155 @AV_LISTID = @ListValuesId OUTPUT;
156
157 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
158 @AV_VALUENAME = N'a. Updating my Facebook status',
159 @AV_DESCRIPTION = N'',
160 @AV_CREATEDUSERID = 1,
161 @AV_UPDATEDUSERID = 1;
162
163 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
164 @AV_VALUENAME = N'b. Ignoring it',
165 @AV_DESCRIPTION = N'',
166 @AV_CREATEDUSERID = 1,
167 @AV_UPDATEDUSERID = 1;
168
169 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
170 @AV_VALUENAME = N'c. Elimination',
171 @AV_DESCRIPTION = N'',
172 @AV_CREATEDUSERID = 1,
173 @AV_UPDATEDUSERID = 1,
174 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
175
176 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
177 @AV_VALUENAME = N'd. Using safety gear',
178 @AV_DESCRIPTION = N'',
179 @AV_CREATEDUSERID = 1,
180 @AV_UPDATEDUSERID = 1;
181
182 UPDATE OneStaff.HealthSafetyQuestionare
183 SET Question = @ListValuesId,
184 Answer = @AnswerId
185 WHERE HealthSafetyQuestionareId = '4';
186END
187GO
188
189IF NOT EXISTS (
190 SELECT 1
191 FROM dbo.Lists
192 WHERE ListName = 'What does PPE stand for?'
193 )
194BEGIN
195 DECLARE @ListValuesId INT;
196 DECLARE @AnswerId INT;
197
198 EXEC LIST_INSERT @AV_LISTNAME = N'What does PPE stand for?',
199 @AV_DESCRIPTION = N'',
200 @AV_CREATEDUSERID = 1,
201 @AV_UPDATEDUSERID = 1,
202 @AV_LISTID = @ListValuesId OUTPUT;
203
204 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
205 @AV_VALUENAME = N'a. Personal Protective Equipment',
206 @AV_DESCRIPTION = N'',
207 @AV_CREATEDUSERID = 1,
208 @AV_UPDATEDUSERID = 1,
209 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
210
211 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
212 @AV_VALUENAME = N'b. Purple People Eater',
213 @AV_DESCRIPTION = N'',
214 @AV_CREATEDUSERID = 1,
215 @AV_UPDATEDUSERID = 1;
216
217 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
218 @AV_VALUENAME = N'c. Performance Protection Equipment',
219 @AV_DESCRIPTION = N'',
220 @AV_CREATEDUSERID = 1,
221 @AV_UPDATEDUSERID = 1;
222
223 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
224 @AV_VALUENAME = N'd. Private Protection Equipment',
225 @AV_DESCRIPTION = N'',
226 @AV_CREATEDUSERID = 1,
227 @AV_UPDATEDUSERID = 1;
228
229 UPDATE OneStaff.HealthSafetyQuestionare
230 SET Question = @ListValuesId,
231 Answer = @AnswerId
232 WHERE HealthSafetyQuestionareId = '5';
233END
234GO
235
236IF NOT EXISTS (
237 SELECT 1
238 FROM dbo.Lists
239 WHERE ListName = 'When should you carry out a Hazard and Risk check?'
240 )
241BEGIN
242 DECLARE @ListValuesId INT;
243 DECLARE @AnswerId INT;
244
245 EXEC LIST_INSERT @AV_LISTNAME = N'When should you carry out a Hazard and Risk check?',
246 @AV_DESCRIPTION = N'',
247 @AV_CREATEDUSERID = 1,
248 @AV_UPDATEDUSERID = 1,
249 @AV_LISTID = @ListValuesId OUTPUT;
250
251 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
252 @AV_VALUENAME = N'a. Once a week',
253 @AV_DESCRIPTION = N'',
254 @AV_CREATEDUSERID = 1,
255 @AV_UPDATEDUSERID = 1;
256
257 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
258 @AV_VALUENAME = N'b. Before you start a job',
259 @AV_DESCRIPTION = N'',
260 @AV_CREATEDUSERID = 1,
261 @AV_UPDATEDUSERID = 1,
262 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
263
264 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
265 @AV_VALUENAME = N'c. At the end of the day',
266 @AV_DESCRIPTION = N'',
267 @AV_CREATEDUSERID = 1,
268 @AV_UPDATEDUSERID = 1;
269
270 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
271 @AV_VALUENAME = N'd. Never, its not your responsibilty',
272 @AV_DESCRIPTION = N'',
273 @AV_CREATEDUSERID = 1,
274 @AV_UPDATEDUSERID = 1;
275
276 UPDATE OneStaff.HealthSafetyQuestionare
277 SET Question = @ListValuesId,
278 Answer = @AnswerId
279 WHERE HealthSafetyQuestionareId = '6';
280END
281GO
282
283IF NOT EXISTS (
284 SELECT 1
285 FROM dbo.Lists
286 WHERE ListName = 'What should you do if you see hazard or incident?'
287 )
288BEGIN
289 DECLARE @ListValuesId INT;
290 DECLARE @AnswerId INT;
291
292 EXEC LIST_INSERT @AV_LISTNAME = N'What should you do if you see hazard or incident?',
293 @AV_DESCRIPTION = N'',
294 @AV_CREATEDUSERID = 1,
295 @AV_UPDATEDUSERID = 1,
296 @AV_LISTID = @ListValuesId OUTPUT;
297
298 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
299 @AV_VALUENAME = N'a. Ignore it',
300 @AV_DESCRIPTION = N'',
301 @AV_CREATEDUSERID = 1,
302 @AV_UPDATEDUSERID = 1;
303
304 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
305 @AV_VALUENAME = N'b. Tell your mates',
306 @AV_DESCRIPTION = N'',
307 @AV_CREATEDUSERID = 1,
308 @AV_UPDATEDUSERID = 1;
309
310 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
311 @AV_VALUENAME = N'c. Pretend you didn''t notice it',
312 @AV_DESCRIPTION = N'',
313 @AV_CREATEDUSERID = 1,
314 @AV_UPDATEDUSERID = 1;
315
316 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
317 @AV_VALUENAME = N'd. Report it to your supervisor immediately and inform OneStaff',
318 @AV_DESCRIPTION = N'',
319 @AV_CREATEDUSERID = 1,
320 @AV_UPDATEDUSERID = 1,
321 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
322
323 UPDATE OneStaff.HealthSafetyQuestionare
324 SET Question = @ListValuesId,
325 Answer = @AnswerId
326 WHERE HealthSafetyQuestionareId = '7';
327END
328GO
329
330IF NOT EXISTS (
331 SELECT 1
332 FROM dbo.Lists
333 WHERE ListName = 'Before every task you should'
334 )
335BEGIN
336 DECLARE @ListValuesId INT;
337 DECLARE @AnswerId INT;
338
339 EXEC LIST_INSERT @AV_LISTNAME = N'Before every task you should',
340 @AV_DESCRIPTION = N'',
341 @AV_CREATEDUSERID = 1,
342 @AV_UPDATEDUSERID = 1,
343 @AV_LISTID = @ListValuesId OUTPUT;
344
345 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
346 @AV_VALUENAME = N'a. Stop Look Think',
347 @AV_DESCRIPTION = N'',
348 @AV_CREATEDUSERID = 1,
349 @AV_UPDATEDUSERID = 1,
350 @AV_LISTVALUEID = @AnswerId OUTPUT;-- Answer
351
352 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
353 @AV_VALUENAME = N'b. Call Supervisor',
354 @AV_DESCRIPTION = N'',
355 @AV_CREATEDUSERID = 1,
356 @AV_UPDATEDUSERID = 1;
357
358 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
359 @AV_VALUENAME = N'c. Talk to my recruiter',
360 @AV_DESCRIPTION = N'',
361 @AV_CREATEDUSERID = 1,
362 @AV_UPDATEDUSERID = 1;
363
364 EXEC LIST_VALUE_INSERT @AV_LISTID = @ListValuesId,
365 @AV_VALUENAME = N'd. Call a friend',
366 @AV_DESCRIPTION = N'',
367 @AV_CREATEDUSERID = 1,
368 @AV_UPDATEDUSERID = 1;
369
370 UPDATE OneStaff.HealthSafetyQuestionare
371 SET Question = @ListValuesId,
372 Answer = @AnswerId
373 WHERE HealthSafetyQuestionareId = '8';
374END
375GO
376
377IF EXISTS (
378 SELECT 1
379 FROM dbo.ListValues
380 WHERE ValueName = 'd. Report it to your supervisor immediately and in'
381 )
382BEGIN
383 UPDATE dbo.ListValues
384 SET ValueName = 'd. Report it to your supervisor immediately and inform OneStaff'
385 WHERE ValueName = 'd. Report it to your supervisor immediately and in'
386END
387GO
388
389ALTER PROCEDURE [OneStaff].[Portal_GetHealthSafetyQuestionare] (@ApplicantId INT)
390AS
391/*
392 History
393 001 - Ibnu Abbas 14/07/2020 - Add Select Answer Applicant
394*/
395BEGIN
396 -- BEGIN CUSTOM 001 --
397 DECLARE @Question1 INT,
398 @Question2 INT,
399 @Question3 INT,
400 @Question4 INT,
401 @Question5 INT,
402 @Question6 INT,
403 @Question7 INT,
404 @Question8 INT,
405 @Score DECIMAL(18, 2);
406
407 SELECT @Question1 = Question1,
408 @Question2 = Question2,
409 @Question3 = Question3,
410 @Question4 = Question4,
411 @Question5 = Question5,
412 @Question6 = Question6,
413 @Question7 = Question7,
414 @Question8 = Question8,
415 @Score = Score
416 FROM dbo.ApplicantSectorDefinedColumns
417 WHERE ApplicantID = @ApplicantId
418
419 IF (@Score IS NOT NULL)
420 BEGIN
421 IF OBJECT_ID('tempdb.dbo.##TempHealtSafety', 'U') IS NOT NULL
422 DROP TABLE ##TempHealtSafety;
423
424 CREATE TABLE ##TempHealtSafety (
425 TempId INT IDENTITY PRIMARY KEY,
426 Id INT,
427 Question NVARCHAR(200),
428 Answer INT,
429 QuestionOrder INT,
430 AnswerApplicant INT
431 );
432
433 INSERT INTO ##TempHealtSafety (
434 Id,
435 Question,
436 Answer,
437 QuestionOrder,
438 AnswerApplicant
439 )
440 SELECT Has.Question AS Id,
441 L.ListName AS Question,
442 Has.Answer AS Answer,
443 Has.QuestionOrder,
444 0 AS AnswerApplicant
445 FROM OneStaff.HealthSafetyQuestionare HAS
446 INNER JOIN Lists L ON Has.Question = L.ListId
447 ORDER BY Has.QuestionOrder ASC
448
449 DECLARE @RowCount INT = (
450 SELECT count(*)
451 FROM ##TempHealtSafety
452 )
453 DECLARE @CNT INT = 1
454
455 WHILE @CNT <= @RowCount
456 BEGIN
457 DECLARE @AnsweredApplicant INT;
458
459 IF @CNT = 1
460 BEGIN
461 SET @AnsweredApplicant = @Question1
462 END
463 ELSE IF @CNT = 2
464 BEGIN
465 SET @AnsweredApplicant = @Question2
466 END
467 ELSE IF @CNT = 3
468 BEGIN
469 SET @AnsweredApplicant = @Question3
470 END
471 ELSE IF @CNT = 4
472 BEGIN
473 SET @AnsweredApplicant = @Question4
474 END
475 ELSE IF @CNT = 5
476 BEGIN
477 SET @AnsweredApplicant = @Question5
478 END
479 ELSE IF @CNT = 6
480 BEGIN
481 SET @AnsweredApplicant = @Question6
482 END
483 ELSE IF @CNT = 7
484 BEGIN
485 SET @AnsweredApplicant = @Question7
486 END
487 ELSE IF @CNT = 8
488 BEGIN
489 SET @AnsweredApplicant = @Question8
490 END
491
492 UPDATE ##TempHealtSafety
493 SET AnswerApplicant = @AnsweredApplicant
494 WHERE TempId = @CNT
495
496 SET @CNT = @CNT + 1;
497 END
498
499 SELECT *
500 FROM ##TempHealtSafety
501 ORDER BY QuestionOrder DESC
502 END
503 ELSE
504 BEGIN
505 SELECT Has.Question AS Id,
506 L.ListName AS Question,
507 Has.Answer AS Answer,
508 Has.QuestionOrder,
509 0 AS AnswerApplicant
510 FROM OneStaff.HealthSafetyQuestionare HAS
511 INNER JOIN Lists L ON Has.Question = L.ListId
512 ORDER BY Has.QuestionOrder DESC
513 END
514 -- END CUSTOM 001
515END