pears.NetOwnerRequirementShortListPlaced

CREATE PROCEDURE "pears"."NetOwnerRequirementShortListPlaced"( IN "pWebUserID" CHAR(20),IN "pRowID" CHAR(20),IN "IsShift" CHAR(1) ) 
RESULT( "PersonName" CHAR(60),"PersonID" CHAR(20),"PersonStatus" CHAR(50) ) 
// IQXWeb
BEGIN
  DECLARE "VacID" CHAR(20);
  IF "IsShift" = 0 THEN
    SET "VacID" = "pRowID"
  END IF;
  IF "IsShift" = 1 THEN
    SET "VacID" = (SELECT "VacancyID" FROM "TempShiftPlan" WHERE "tempshiftplanid" = "pRowID")
  END IF;
  SELECT DISTINCT
    "pe"."name",
    "pe"."personid",
    "s"."name" AS "PersonStatus"
    FROM "placement" AS "p"
      KEY JOIN "employment" AS "e"
      KEY JOIN "person" AS "pe"
      LEFT OUTER JOIN "status" AS "s" ON "pe"."status" = "s"."status" AND "s"."type" = 'P'
    WHERE "p"."vacancyid" = "VacID"
    ORDER BY "pe"."name" ASC
END /* DOC
2016-11-30 PC add doc and test
2017-11-01 PC RC2 modify to refer to placement table directly, include state and make available for all consultants. Also check count and return 'no data' if no records
2018-09-02 PC IW-685 simplify status column
 
Inputs:
pWebUserID
pRowID
IsShift
 
Outputs:
PersonName
PersonID
PersonStatus
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid Rowid
valid pWebUserID, Rowid invalid IsShift
valid pWebUserID, Rowid, IsShift=0
valid pWebUserID, Rowid, IsShift=1
*/
/* 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.owner';
update iqxnetuser set staffid=null where staffid='TEST';
update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.OWNER';
insert into progress (progressid,vacancyid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');
 
update vacancy set staffid='TEST' where vacancyid='TEST';
select * from NetOwnerRequirementShortListPlaced(null,null,null);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner',null,0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','zz',0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status='X' where personid='test';
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status=null where personid='test';
insert into tempshiftprogress (tempshiftprogressid,tempshiftplanid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementShortListPlaced" IS 
{CREATE PROCEDURE pears."NetOwnerRequirementShortListPlaced"(IN pWebUserID CHAR(20),IN pRowID CHAR(20),IN IsShift CHAR(1))
RESULT(PersonName CHAR(60),PersonID CHAR(20),PersonStatus CHAR(50))
// IQXWeb
BEGIN
  DECLARE VacID CHAR(20);
  IF IsShift=0  THEN
    SET VacID=pRowID;	
  END IF;
  IF IsShift=1  THEN
    SET "VacID" = (SELECT VacancyID FROM "TempShiftPlan" WHERE "tempshiftplanid" = pRowID);
  END IF;
  SELECT DISTINCT
    pe.name,
    pe.personid, 
    s.name AS PersonStatus
FROM 
    placement p
    KEY JOIN employment e
    KEY JOIN person pe
    LEFT JOIN STATUS s ON pe.status=s.status AND s.type='P'
WHERE
    p.vacancyid=VacID
ORDER BY 
    pe.name ASC 
END
 
/* DOC
2016-11-30 PC add doc and test
2017-11-01 PC RC2 modify to refer to placement table directly, include state and make available for all consultants. Also check count and return 'no data' if no records
2018-09-02 PC IW-685 simplify status column
 
Inputs:
    pWebUserID
    pRowID
    IsShift
 
Outputs:
    PersonName
    PersonID
    PersonStatus
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid Rowid
    valid pWebUserID, Rowid invalid IsShift
    valid pWebUserID, Rowid, IsShift=0
    valid pWebUserID, Rowid, IsShift=1
*/
 
/* 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.owner';
update iqxnetuser set staffid=null where staffid='TEST';
update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.OWNER';
insert into progress (progressid,vacancyid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');
 
update vacancy set staffid='TEST' where vacancyid='TEST';
select * from NetOwnerRequirementShortListPlaced(null,null,null);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner',null,0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','zz',0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status='X' where personid='test';
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status=null where personid='test';
insert into tempshiftprogress (tempshiftprogressid,tempshiftplanid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
 
*/
}