pears.NetCandidateRegistrations [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetCandidateRegistrations ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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';
*/
}
</code>