pears.NetCandidateRegistrations

CREATE PROCEDURE "pears"."NetCandidateRegistrations"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "DepartmentID" CHAR(10),"PersonID" CHAR(20),"DepartmentName" CHAR(100),"TempJobs" SMALLINT,"PermJobs" SMALLINT,"Details" CHAR(100),"SortOrder" CHAR(20) ) 
// IQXWeb
BEGIN
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  SELECT DISTINCT
    "department"."departmentid",
    "search"."personid",
    "department"."name" AS "DeptName",
    "isnull"("search"."temp",0) AS "TempJobs",
    "isnull"("search"."permanent",0) AS "PermJobs",
    "isnull"("string"((IF "TempJobs" <> 0 THEN 'Temp jobs' endif),(IF "TempJobs"*"Permjobs" <> 0 THEN ', ' endif),(IF "PermJobs" <> 0 THEN 'Perm jobs' endif)),'Not Registered') AS "details",
    "department"."sortorder" AS "SO"
    FROM "department" LEFT OUTER JOIN "search" ON "search"."departmentid" = "department"."departmentid" AND "search"."personid" = "pPersonID"
    WHERE "department"."searchable" = 1
    AND "department"."publishtoweb" = 1
    AND("department"."divisionid" IS NULL OR "department"."divisionid" = any(SELECT "divisionid" FROM "person" WHERE "personid" = "pPersonID"))
    AND "pPersonID" IS NOT NULL -- If pPersonID is null return empty result
    ORDER BY "SO" ASC,
    "DeptName" ASC
END /* DOC
2016-12-05 PC test and doc
2017-09-25 PC modified to address duplicates
2018-5-3 VHS/MHS restricted departments to global or matching person division
2018-06-19 PC changes to nomenclature to avoid problems with derived table in test procedures
2018-12-21 MHS if multiple person links, only show the one explicit in pPersonID
 
Tests:
invalid pWebUserID
Valid pWebUserID not registered
valid pWebUserID registered
 
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
update department set searchable=null,publishtoweb=null where departmentid='~~';
delete from search where personid='test';
select * from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect EOF;
update department set searchable=1,publishtoweb=1 where departmentid='~~';
select *,replace(details,' ','!') as A from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect DepartmentID=~~,DepartmentName=test,TempJobs=0,PermJobs=0,A=Not!Registered;
insert into search (searchid,temp,permanent,personid,departmentid) values ('test.candidate',1,0,'TEST','~~');
select *,replace(details,' ','!') as B from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect DepartmentID=~~,DepartmentName=test,TempJobs=1,PermJobs=0,B=Temp!jobs;
update search set permanent=1 where personid='test' and departmentid='~~';
select *,replace(replace(details,' ','!'),',','^') as C from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect DepartmentID=~~,DepartmentName=test,TempJobs=1,PermJobs=1,C=Temp!jobs^!Perm!jobs;
update department set searchable=null,publishtoweb=null where departmentid='~~';
delete from search where personid='test';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateRegistrations" IS 
{CREATE PROCEDURE pears."NetCandidateRegistrations"( IN pWebUserID CHAR(20), IN pPersonID CHAR(20) DEFAULT NULL ) 
RESULT( DepartmentID CHAR(10),PersonID CHAR(20),DepartmentName CHAR(100),TempJobs SMALLINT,PermJobs SMALLINT,Details CHAR(100),SortOrder CHAR(20)) 
// IQXWeb
BEGIN
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  SELECT DISTINCT
    department.departmentid,
    SEARCH.personid,
    department.name AS DeptName,
    isnull(SEARCH.temp,0) AS TempJobs,
    isnull(SEARCH.permanent,0) AS PermJobs,
    isnull(string((IF TempJobs <> 0 THEN 'Temp jobs' endif ),(IF TempJobs * Permjobs <> 0 THEN ', ' endif ),(IF PermJobs <> 0 THEN 'Perm jobs' endif )),'Not Registered') AS details,
    department.sortorder AS SO
  FROM 
    department LEFT OUTER JOIN SEARCH ON SEARCH.departmentid = department.departmentid AND SEARCH.personid = pPersonID
  WHERE 
    department.searchable = 1 
    AND department.publishtoweb = 1
    AND (department.divisionid IS NULL OR department.divisionid IN (SELECT divisionid FROM person WHERE personid=pPersonID))
    AND pPersonID IS NOT NULL  -- If pPersonID is null return empty result
  ORDER BY 
    SO ASC,
    DeptName ASC
END
/* DOC
2016-12-05 PC test and doc
2017-09-25 PC modified to address duplicates
2018-5-3 VHS/MHS restricted departments to global or matching person division
2018-06-19 PC changes to nomenclature to avoid problems with derived table in test procedures
2018-12-21 MHS if multiple person links, only show the one explicit in pPersonID
 
Tests:
    invalid pWebUserID
    Valid pWebUserID not registered
    valid pWebUserID registered
 
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
update department set searchable=null,publishtoweb=null where departmentid='~~';
delete from search where personid='test';
select * from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect EOF;
update department set searchable=1,publishtoweb=1 where departmentid='~~';
select *,replace(details,' ','!') as A from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect DepartmentID=~~,DepartmentName=test,TempJobs=0,PermJobs=0,A=Not!Registered;
insert into search (searchid,temp,permanent,personid,departmentid) values ('test.candidate',1,0,'TEST','~~');
select *,replace(details,' ','!') as B from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect DepartmentID=~~,DepartmentName=test,TempJobs=1,PermJobs=0,B=Temp!jobs;
update search set permanent=1 where personid='test' and departmentid='~~';
select *,replace(replace(details,' ','!'),',','^') as C from NetCandidateRegistrations('test.candidate') where DepartmentID='~~';
expect DepartmentID=~~,DepartmentName=test,TempJobs=1,PermJobs=1,C=Temp!jobs^!Perm!jobs;
update department set searchable=null,publishtoweb=null where departmentid='~~';
delete from search where personid='test';
*/
}