pears.NetCandidateRegistration

CREATE PROCEDURE "pears"."NetCandidateRegistration"( IN "pWebUserID" CHAR(20),IN "pDepartmentID" CHAR(10),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "departmentid" CHAR(10),"personid" CHAR(20),"departmentname" CHAR(100),"temp" SMALLINT,"perm" SMALLINT ) 
//IQXNet
BEGIN
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  SELECT "department"."departmentid",
    "isnull"("search"."personid","pPersonID") AS "personid",
    "department"."name",
    "search"."temp",
    "search"."permanent"
    FROM "department" LEFT OUTER JOIN "search" ON "department"."departmentid" = "search"."departmentid" AND "search"."personid" = "pPersonID"
    WHERE "department"."departmentid" = "pDepartmentID"
END /* DOC
2016-12-05 PC test and doc
2018-12-21 MHS if multiple person links, only show the registration linked to explicit pPersonID
Sort tests
 
Inputs:
pWebUserID
pDepartmentID
 
Outputs:
departmentid
personid
departmentname
temp
perm
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid pDepartmentID
valid pWebUserID, pDepartmentID, no depts
valid pWebUserID, pDepartmentID, temp
valid pWebUserID, pDepartmentID, perm
*/
/* TEST
call NetTestSetup('');
select * from NetCandidateRegistration(null,null); 
expect EOF;
select * from NetCandidateRegistration('test.candidate',null); 
expect EOF;
delete from search where personid='TEST';
select *,isnull(temp,'9') as A,isnull(perm,'9') as B from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,A=9,B=9;
insert into search (searchid,temp,permanent,personid,departmentid) values ('test.candidate',1,0,'TEST','~~');
select *,isnull(temp,'9') as C,isnull(perm,'9') as D from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,C=1,D=0;
update search set permanent=1 where personid='test' and departmentid='~~';
select *,isnull(temp,'9') as E,isnull(perm,'9') as F from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,E=1,F=1;
delete from search where personid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateRegistration" IS 
{CREATE PROCEDURE pears."NetCandidateRegistration"( IN pWebUserID CHAR(20),IN pDepartmentID CHAR(10), IN pPersonID CHAR(20) DEFAULT NULL ) 
RESULT( departmentid CHAR(10),personid CHAR(20),departmentname CHAR(100),temp SMALLINT,perm SMALLINT ) 
//IQXNet
BEGIN
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  SELECT department.departmentid,
    isnull(SEARCH.personid, pPersonID) AS personid,
    department.name,
    SEARCH.temp,
    SEARCH.permanent
    FROM department LEFT OUTER JOIN SEARCH ON department.departmentid = SEARCH.departmentid AND SEARCH.personid = pPersonID
    WHERE department.departmentid = pDepartmentID
END
 
/* DOC
2016-12-05 PC test and doc
2018-12-21 MHS if multiple person links, only show the registration linked to explicit pPersonID
           Sort tests
 
Inputs:
    pWebUserID
    pDepartmentID
 
Outputs:
    departmentid
    personid
    departmentname
    temp
    perm
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid pDepartmentID
    valid pWebUserID, pDepartmentID, no depts
    valid pWebUserID, pDepartmentID, temp
    valid pWebUserID, pDepartmentID, perm
*/
 
/* TEST
call NetTestSetup('');
select * from NetCandidateRegistration(null,null); 
expect EOF;
select * from NetCandidateRegistration('test.candidate',null); 
expect EOF;
delete from search where personid='TEST';
select *,isnull(temp,'9') as A,isnull(perm,'9') as B from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,A=9,B=9;
insert into search (searchid,temp,permanent,personid,departmentid) values ('test.candidate',1,0,'TEST','~~');
select *,isnull(temp,'9') as C,isnull(perm,'9') as D from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,C=1,D=0;
update search set permanent=1 where personid='test' and departmentid='~~';
select *,isnull(temp,'9') as E,isnull(perm,'9') as F from NetCandidateRegistration('test.candidate','~~'); 
expect departmentid=~~,personid=TEST,departmentname=test,E=1,F=1;
delete from search where personid='TEST';
*/
}