· 5 years ago · Mar 17, 2020, 01:46 AM
1--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
2IF NOT EXISTS (
3 SELECT 1
4 FROM sys.columns
5 WHERE Name = N'PortalState'
6 AND Object_ID = Object_ID(N'dbo.ApplicantSectorDefinedColumns')
7 )
8BEGIN
9 ALTER TABLE [dbo].[ApplicantSectorDefinedColumns] ADD [PortalState] [INT] NULL
10
11 ALTER TABLE [dbo].[ApplicantSectorDefinedColumns] ADD CONSTRAINT [APPLICANTSECTORDEFINEDCOLUMNS_PORTALSTATE_FK] FOREIGN KEY ([PortalState]) REFERENCES [dbo].[ListValues] ([ListValueId])
12END
13GO
14
15/****** Object: StoredProcedure [Portal].[USERS_GROUP_BY_SECTOR] Script Date: 30/12/2019 13:57:46 ******/
16SET ANSI_NULLS ON
17GO
18
19SET QUOTED_IDENTIFIER ON
20GO
21
22DROP PROCEDURE IF EXISTS [McArthur].[Portal_USERS_GROUP_BY_SECTOR]
23GO
24
25 CREATE PROCEDURE [McArthur].[Portal_USERS_GROUP_BY_SECTOR] @SECTORS NVARCHAR(1000)
26 AS
27 BEGIN
28 SELECT DISTINCT U.UserId,
29 u.EmailAddress,
30 U.UserFullName
31 FROM dbo.Users U
32 INNER JOIN dbo.UserGroups UG ON UG.UserId = U.UserId
33 INNER JOIN dbo.Groups G ON G.GroupId = UG.GroupId
34 INNER JOIN dbo.Sectors S ON S.SectorName = G.GroupName
35 WHERE S.SectorId IN (
36 SELECT ITEM
37 FROM DBO.split_createTableFromString(@SECTORS, ',')
38 )
39 END
40GO
41
42DROP PROCEDURE IF EXISTS [Portal].[APPLICANT_INSERT]
43GO
44 CREATE PROCEDURE [Portal].[APPLICANT_INSERT] (
45 @RDBApplicantID INT = NULL,
46 @WEBSITEUSERID INT,
47 -- PERSON
48 @NAMETITLEID INT = NULL,
49 @PERSONNAME NVARCHAR(100) = NULL,
50 @SURNAME NVARCHAR(100) = NULL,
51 -- PHONES
52 @EMAILADDRESS NVARCHAR(150),
53 @DAYPHONE NVARCHAR(150) = NULL,
54 @EVEPHONE NVARCHAR(150) = NULL,
55 @MOBPHONE NVARCHAR(150) = NULL,
56 @HOMEPHONE NVARCHAR(150) = NULL,
57 -- ADDRESS
58 @STREET NVARCHAR(100) = NULL,
59 @DISTRICT NVARCHAR(100) = NULL,
60 @BUILDING NVARCHAR(100) = NULL,
61 @COUNTYVALUEID INT = NULL,
62 @COUNTRYVALUEID INT = NULL,
63 @POSTCODE NVARCHAR(50) = NULL,
64 @CITY NVARCHAR(100) = NULL,
65 -- EMPLOYMENT
66 @JOBTITLE NVARCHAR(100) = NULL,
67 @EMPLOYMENTTYPEIDS NVARCHAR(MAX) = NULL,
68 @CURRENTPACKAGE INT = NULL,
69 @MINPACKAGE INT = NULL,
70 -- BEGIN CUSTOM 001 --
71 -- PORTAL STATE
72 @PORTALSTATEID INT = NULL,
73 -- WORK INTEREST
74 @SECTORS NVARCHAR(255) = NULL
75 )
76 -- END CUSTOM 001 --
77 AS
78 /* History
79 001 - Adi - 30/12/2019 - Add Parameter, Add Employement Type, Custom Sector Object
80*/
81 BEGIN
82 DECLARE @OBJECTID INT,
83 @OBJECTTYPEID INT,
84 @FILEASORDER INT,
85 @FILEAS NVARCHAR(255)
86
87 --Get the applicant objecttypeid
88 SELECT @OBJECTTYPEID = OBJECTTYPEID
89 FROM ObjectTypes
90 WHERE SYSTEMCODE = 'APP'
91
92 --Get the fileas in the correct format for the website userid (0 = Surname, Forename / 1 = Forname Surname)
93 SELECT @FILEASORDER = FILEASORDER
94 FROM UserPreferences
95 WHERE UserId = @WEBSITEUSERID;
96
97 IF @FILEASORDER = 0
98 BEGIN
99 SET @FILEAS = @SURNAME + ', ' + @PERSONNAME
100 END
101 ELSE
102 BEGIN
103 SET @FILEAS = @PERSONNAME + ' ' + @SURNAME
104 END
105
106 IF (
107 @RDBApplicantID IS NULL
108 OR @RDBApplicantID = 0
109 )
110 BEGIN
111 INSERT INTO OBJECTS (
112 OBJECTTYPEID,
113 FILEAS,
114 FLAGTEXT,
115 CREATEDUSERID,
116 CREATEDON
117 )
118 VALUES (
119 @OBJECTTYPEID,
120 @FILEAS,
121 'Website registered user',
122 @WEBSITEUSERID,
123 GETDATE()
124 )
125
126 SET @OBJECTID = SCOPE_IDENTITY()
127
128 EXEC OBJECT_INDEXWORD_GENERATE @OBJECTID,
129 @FILEAS,
130 @WEBSITEUSERID,
131 @WEBSITEUSERID;
132 END
133 ELSE
134 BEGIN
135 SET @OBJECTID = @RDBApplicantID
136 END
137
138 -- BEGIN CUSTOM 001 --
139 -- Insert into all sectors that the website user has access to
140 --INSERT INTO SECTOROBJECTS
141 -- ( SECTORID ,
142 -- OBJECTID ,
143 -- CREATEDUSERID ,
144 -- CREATEDON ,
145 -- UPDATEDUSERID
146 -- )
147 -- SELECT SECTORID ,
148 -- @OBJECTID ,
149 -- @WEBSITEUSERID ,
150 -- GETDATE() ,
151 -- @WEBSITEUSERID
152 -- FROM SECTORUSERS
153 -- WHERE USERID = @WEBSITEUSERID
154 -- AND SECTORID NOT IN ( SELECT SECTORID
155 -- FROM SectorObjects
156 -- WHERE OBJECTID = @OBJECTID )
157 -- END CUSTOM 001 --
158 -- Insert phone and email addresses
159 DECLARE @COMMUNICATIONTYPEID INT,
160 @PRIMARYEMAILADDRESSPHONEID INT
161
162 -- Get the commtype for email address
163 SELECT @COMMUNICATIONTYPEID = MIN(COMMUNICATIONTYPEID)
164 FROM CommunicationTypes
165 WHERE SYSTEMCODE = 'COMM_TYP_EMAIL'
166
167 IF NOT EXISTS (
168 SELECT *
169 FROM PHONES
170 WHERE OBJECTID = @OBJECTID
171 AND NUM = @EMAILADDRESS
172 )
173 BEGIN
174 INSERT INTO PHONES (
175 OBJECTID,
176 COMMUNICATIONTYPEID,
177 NUM,
178 NUMTRIMMED,
179 COMMENTS,
180 CREATEDUSERID,
181 CREATEDON
182 )
183 VALUES (
184 @OBJECTID,
185 @COMMUNICATIONTYPEID,
186 @EMAILADDRESS,
187 @EMAILADDRESS,
188 'Also website username',
189 @WEBSITEUSERID,
190 GETDATE()
191 )
192
193 SET @PRIMARYEMAILADDRESSPHONEID = SCOPE_IDENTITY()
194 END
195 ELSE
196 BEGIN
197 SELECT @PRIMARYEMAILADDRESSPHONEID = PHONEID
198 FROM PHONES
199 WHERE OBJECTID = @OBJECTID
200 AND NUM = @EMAILADDRESS
201 END
202
203 -- Get the commtype for daytime phone number
204 IF @DAYPHONE IS NOT NULL
205 BEGIN
206 IF NOT EXISTS (
207 SELECT *
208 FROM PHONES
209 WHERE OBJECTID = @OBJECTID
210 AND NUM = @DAYPHONE
211 )
212 BEGIN
213 SELECT @COMMUNICATIONTYPEID = MIN(COMMUNICATIONTYPEID)
214 FROM CommunicationTypes
215 WHERE SYSTEMCODE = 'COMM_TYP_DAYPHONE'
216
217 INSERT INTO PHONES (
218 OBJECTID,
219 COMMUNICATIONTYPEID,
220 NUM,
221 COMMENTS,
222 CREATEDUSERID,
223 CREATEDON
224 )
225 VALUES (
226 @OBJECTID,
227 @COMMUNICATIONTYPEID,
228 @DAYPHONE,
229 'Use for applicant calls',
230 @WEBSITEUSERID,
231 GETDATE()
232 )
233 END
234 END
235
236 -- Get the commtype for evening phone number
237 IF @EVEPHONE IS NOT NULL
238 BEGIN
239 IF NOT EXISTS (
240 SELECT *
241 FROM PHONES
242 WHERE OBJECTID = @OBJECTID
243 AND NUM = @EVEPHONE
244 )
245 BEGIN
246 SELECT @COMMUNICATIONTYPEID = MIN(COMMUNICATIONTYPEID)
247 FROM CommunicationTypes
248 WHERE SYSTEMCODE = 'COMM_TYP_EVENPHONE'
249
250 INSERT INTO PHONES (
251 OBJECTID,
252 COMMUNICATIONTYPEID,
253 NUM,
254 CREATEDUSERID,
255 CREATEDON
256 )
257 VALUES (
258 @OBJECTID,
259 @COMMUNICATIONTYPEID,
260 @EVEPHONE,
261 @WEBSITEUSERID,
262 GETDATE()
263 )
264 END
265 END
266
267 -- Get the commtype for mobile phone number
268 IF @MOBPHONE IS NOT NULL
269 BEGIN
270 IF NOT EXISTS (
271 SELECT *
272 FROM PHONES
273 WHERE OBJECTID = @OBJECTID
274 AND NUM = @MOBPHONE
275 )
276 BEGIN
277 SELECT @COMMUNICATIONTYPEID = MIN(COMMUNICATIONTYPEID)
278 FROM CommunicationTypes
279 WHERE SYSTEMCODE = 'COMM_TYP_MOBILE'
280
281 INSERT INTO PHONES (
282 OBJECTID,
283 COMMUNICATIONTYPEID,
284 NUM,
285 CREATEDUSERID,
286 CREATEDON
287 )
288 VALUES (
289 @OBJECTID,
290 @COMMUNICATIONTYPEID,
291 @MOBPHONE,
292 @WEBSITEUSERID,
293 GETDATE()
294 )
295 END
296 END
297
298 -- Get the commtype for home phone number
299 IF @HOMEPHONE IS NOT NULL
300 BEGIN
301 IF NOT EXISTS (
302 SELECT *
303 FROM PHONES
304 WHERE OBJECTID = @OBJECTID
305 AND NUM = @HOMEPHONE
306 )
307 BEGIN
308 SELECT @COMMUNICATIONTYPEID = MIN(COMMUNICATIONTYPEID)
309 FROM CommunicationTypes
310 WHERE SYSTEMCODE = 'COMM_TYP_HOME_PHONE'
311
312 INSERT INTO PHONES (
313 OBJECTID,
314 COMMUNICATIONTYPEID,
315 NUM,
316 CREATEDUSERID,
317 CREATEDON
318 )
319 VALUES (
320 @OBJECTID,
321 @COMMUNICATIONTYPEID,
322 @HOMEPHONE,
323 @WEBSITEUSERID,
324 GETDATE()
325 )
326 END
327 END
328
329 -- Insert the basic personal details
330 IF NOT EXISTS (
331 SELECT *
332 FROM PERSON
333 WHERE PERSONID = @OBJECTID
334 )
335 BEGIN
336 INSERT INTO PERSON (
337 PERSONID,
338 PERSONNAME,
339 SURNAME,
340 TITLEVALUEID,
341 SALUTATION,
342 NOTES,
343 CREATEDUSERID,
344 CREATEDON
345 )
346 VALUES (
347 @OBJECTID,
348 @PERSONNAME,
349 @SURNAME,
350 @NAMETITLEID,
351 @PERSONNAME,
352 'Created by website',
353 @WEBSITEUSERID,
354 GETDATE()
355 )
356 END
357
358 -- Insert the home address
359 IF NOT EXISTS (
360 SELECT *
361 FROM [ADDRESS]
362 WHERE OBJECTID = @OBJECTID
363 )
364 BEGIN
365 DECLARE @HOMEADDRESSTYPEID INT
366
367 SET @HOMEADDRESSTYPEID = (
368 SELECT AddressTypeId
369 FROM dbo.AddressTypes
370 WHERE dbo.AddressTypes.SystemCode = 'ADDR_TYP_HOME'
371 )
372
373 INSERT INTO [ADDRESS] (
374 OBJECTID,
375 BUILDING,
376 STREET,
377 DISTRICT,
378 CITY,
379 COUNTYVALUEID,
380 CountryValueId,
381 POSTCODE,
382 ADDRESSTYPEID,
383 CREATEDUSERID,
384 CREATEDON,
385 UPDATEDUSERID
386 )
387 VALUES (
388 @OBJECTID,
389 @BUILDING,
390 @STREET,
391 @DISTRICT,
392 @CITY,
393 @COUNTYVALUEID,
394 @COUNTRYVALUEID,
395 @POSTCODE,
396 @HOMEADDRESSTYPEID,
397 @WEBSITEUSERID,
398 GETDATE(),
399 @WEBSITEUSERID
400 )
401 END
402
403 -- Insert the applicant record
404 IF NOT EXISTS (
405 SELECT *
406 FROM APPLICANTS
407 WHERE APPLICANTID = @OBJECTID
408 )
409 BEGIN
410 DECLARE @NEWSTATUSID INT
411
412 SELECT @NEWSTATUSID = MIN(ApplicantStatusId)
413 FROM ApplicantStatus
414 WHERE SYSTEMCODE = 'APP_STT_NEW'
415
416 INSERT INTO APPLICANTS (
417 APPLICANTID,
418 STATUSID,
419 STATUSDATE,
420 PRIMARYEMAILADDRESSPHONEID,
421 CREATEDUSERID,
422 CREATEDON,
423 SourceId
424 )
425 VALUES (
426 @OBJECTID,
427 @NEWSTATUSID,
428 GETDATE(),
429 @PRIMARYEMAILADDRESSPHONEID,
430 @WEBSITEUSERID,
431 GETDATE(),
432 44
433 )
434 END
435 ELSE
436 BEGIN
437 IF NOT EXISTS (
438 SELECT *
439 FROM APPLICANTS
440 WHERE APPLICANTID = @OBJECTID
441 AND PRIMARYEMAILADDRESSPHONEID = @PRIMARYEMAILADDRESSPHONEID
442 )
443 BEGIN
444 UPDATE APPLICANTS
445 SET PRIMARYEMAILADDRESSPHONEID = @PRIMARYEMAILADDRESSPHONEID,
446 UPDATEDON = GETDATE(),
447 UpdatedUserId = @WEBSITEUSERID
448 WHERE APPLICANTID = @OBJECTID
449 END
450 END
451
452 -- BEGIN CUSTOM 001 --
453 -- Insert the applicant employee types
454 IF @EMPLOYMENTTYPEIDS IS NOT NULL
455 INSERT INTO dbo.ApplicantEmploymentTypes (
456 ApplicantId,
457 EmploymentTypeId,
458 CreatedUserId,
459 CreatedOn
460 )
461 SELECT @OBJECTID,
462 ITEM,
463 @WEBSITEUSERID,
464 GETDATE()
465 FROM DBO.split_createTableFromString(@EMPLOYMENTTYPEIDS, ',')
466
467 -- Insert the applicant sector defined column
468 IF @PORTALSTATEID IS NOT NULL
469 INSERT INTO ApplicantSectorDefinedColumns (
470 APPLICANTID,
471 CREATEDUSERID,
472 CREATEDON,
473 PORTALSTATE
474 )
475 VALUES (
476 @OBJECTID,
477 @WEBSITEUSERID,
478 GETDATE(),
479 @PORTALSTATEID
480 )
481
482 -- Insert the sectorobjects column
483 IF @SECTORS IS NOT NULL
484 INSERT INTO SECTOROBJECTS (
485 SECTORID,
486 OBJECTID,
487 CREATEDUSERID,
488 CREATEDON,
489 UPDATEDUSERID
490 )
491 SELECT A.Item,
492 @OBJECTID,
493 @WEBSITEUSERID,
494 GETDATE(),
495 @WEBSITEUSERID
496 FROM DBO.split_createTableFromString(@SECTORS, ',') A
497
498 -- END CUSTOM 001 --
499 -- Must return the object ID for INSERT
500 RETURN @OBJECTID
501 END
502GO
503
504DROP PROCEDURE IF EXISTS [Portal].[GetAttributesByDescription]
505GO
506 CREATE PROCEDURE [Portal].[GetAttributesByDescription] (@Description NVARCHAR(100) = NULL)
507 AS
508 BEGIN
509 IF @Description IS NULL
510 SET @Description = 'Portal Divisions';
511
512 WITH CTE (
513 AttributeMasterId,
514 ParentAttributeMasterId,
515 Description
516 )
517 AS (
518 SELECT AM.AttributeMasterId,
519 AM.ParentAttributeMasterId,
520 AM.Description
521 FROM AttributeMaster AM
522 WHERE AM.Description = @Description
523
524 UNION ALL
525
526 SELECT AM.AttributeMasterId,
527 AM.ParentAttributeMasterId,
528 AM.Description
529 FROM AttributeMaster AM
530 INNER JOIN CTE ON AM.ParentAttributeMasterId = CTE.AttributeMasterId
531 )
532 SELECT A.AttributeMasterId,
533 A.Description,
534 A.ParentAttributeMasterId
535 FROM CTE A
536 WHERE ParentAttributeMasterId IS NOT NULL
537 ORDER BY Description
538 END
539GO
540
541DROP PROCEDURE IF EXISTS [McArthur].[Portal_GetAttributeInterestByDescription]
542GO
543 CREATE PROCEDURE [McArthur].[Portal_GetAttributeInterestByDescription] (
544 @description NVARCHAR(25) = NULL,
545 @code NVARCHAR(25) = NULL
546 )
547 AS
548 BEGIN
549 SET @description = 'Portal Divisions';
550
551 SELECT DISTINCT AM2.Description,
552 AM1.AttributeMasterId,
553 AM1.Description AS Area,
554 A.Description AS Division,
555 S.SystemCode,
556 S.SectorId
557 FROM dbo.Attributes A
558 INNER JOIN dbo.Sectors S ON S.SectorName = A.Notes
559 INNER JOIN dbo.AttributeMaster AM1 ON AM1.AttributeMasterId = A.AttributeMasterId
560 INNER JOIN dbo.AttributeMaster AM2 ON AM1.ParentAttributeMasterId = AM2.AttributeMasterId
561 WHERE AM2.Description = @description
562 AND AM1.Description = @code
563 END
564GO
565
566-------------- Template Email Notification For Group Consultant ---------------
567IF NOT EXISTS (
568 SELECT 1
569 FROM dbo.HtmlTemplates
570 WHERE SystemCode = 'JOBSHOP_NOTIFICATION_NEW_CANDIDATE'
571 )
572BEGIN
573 INSERT [dbo].[HtmlTemplates] (
574 [SystemCode],
575 [HtmlDocument],
576 [CreatedUserId],
577 [CreatedOn],
578 [UpdatedUserId],
579 [UpdatedOn],
580 [HtmlTemplateDescription],
581 [Subject]
582 )
583 VALUES (
584 N'JOBSHOP_NOTIFICATION_NEW_CANDIDATE',
585 N'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
586 <html xmlns="http://www.w3.org/1999/xhtml">
587
588 <head>
589 <meta content="en-gb" http-equiv="Content-Language" />
590 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
591 <title>[SectorName] Candidate Registration</title>
592 <style type="text/css">
593 .style1 {
594 font-family: Arial, Helvetica, sans-serif;
595 font-size: 10pt;
596 }
597 </style>
598 </head>
599
600 <body class="style1"> Dear [Consultant],<br /><br />
601 Please see below candidate registration information.<br /><br />
602 <table>
603 <tr><td>Title: [Title]</td></tr>
604 <tr><td>First Name: [FirstName]</td></tr>
605 <tr><td>Surname: [Surname]</td></tr>
606 <tr><td>Street: [Street]</td></tr>
607 <tr><td>Town/City: [City]</td></tr>
608 <tr><td>State: [State]</td></tr>
609 <tr><td>Country: [Country]</td></tr>
610 <tr><td>Postcode: [Postcode]</td></tr>
611 <tr><td>Email: [Email]</td></tr>
612 <tr><td>Mobile Phone: [MobilePhone]</td></tr>
613 <tr><td>Employment Types: [Emptype]</td></tr>
614 <tr><td>Willing Work On: [Area]</td></tr>
615 <tr><td>Interest Division: [Division]</td></tr>
616 </table>
617 <br />
618 Please note, replies to this email will not be processed.
619 </body>
620
621 </html>'
622 ,
623 1,
624 GETDATE(),
625 NULL,
626 GETDATE(),
627 N'NOTIFICATION_NEW_CANDIDATE',
628 NULL
629 )
630END
631GO