pears.NetClientRequirementShortList

CREATE PROCEDURE "pears"."NetClientRequirementShortList"( IN "pWebUserID" CHAR(20),IN "pRowID" CHAR(20),IN "IsShift" CHAR(1) ) 
RESULT( "PersonName" CHAR(60),"PersonID" CHAR(20),"PersonStatus" CHAR(50),"Requested" CHAR(1),"Rejected" CHAR(1),"WithHeld" CHAR(1) ) 
// 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",
    (SELECT "count"() FROM "diary" WHERE "description" LIKE 'Requested candidate%' AND "personid" = "pe"."personid" AND "vacancyid" = "p"."vacancyid") AS "Requested",
    (SELECT "count"() FROM "diary" WHERE "description" LIKE 'Shift Candidate Rejected%' AND "personid" = "pe"."personid" AND "vacancyid" = "p"."vacancyid") AS "Rejected",
    (SELECT "count"() FROM "withholds" AS "w" WHERE "w"."personid" = "p"."personid" AND("p"."vacancyid" = "w"."vacancyid" OR "e"."companyid" = "w"."companyid")) AS "WithHeld"
    FROM "person" AS "pe"
      KEY JOIN "progress" AS "p"
      KEY JOIN "vacancy" AS "vac"
      KEY JOIN "employment" AS "em"
      KEY JOIN "company"
      KEY JOIN "employment" AS "e"
      KEY JOIN "iqxnetuserlink" AS "i"
      ,"progress" AS "p"
      JOIN "status" AS "s" ON "s"."status" = "p"."status" AND "s"."type" = 'R'
    WHERE "i"."iqxnetuserid" = "pWebUserID"
    AND "p"."vacancyid" = "VacID"
    AND "s"."name" <> 'placed'
    AND "Rejected" = 0
    AND "WithHeld" = 0
END /* DOC
2016-11-30 PC add requested / rejected, and add doc and test
2017-11-17 PC IW-266 remove candidate if Withheld    
2019-02-18 PC/ET IW-817 remove "no data"
2019-02-22 PC modify to expand to all vacancies for company, not just consultant
2019-02-27 PC remove duplicates
 
Tests:
valid pWebUserID, Rowid, IsShift=0 with no events
valid pWebUserID, Rowid, IsShift=0 with requested
valid pWebUserID, Rowid, IsShift=0 with rejected
valid pWebUserID, Rowid, IsShift=1 with requested and rejected
*/
/* 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.client';
insert into progress (progressid,vacancyid,personid,status,staffid) on existing update values ('TEST','TEST','TEST','#','TEST');
delete from withholds where WithHoldsID='TEST';
delete from diary where personid='test' and vacancyid='test';
select * from NetClientRequirementShortList('test.client','TEST',null);
expect EOF;
update status set publishtoweb=1 where type='R' and status in ('A','#'); 
select * from NetClientRequirementShortList('test.client','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*,Requested=0,Rejected=0;
insert into diary (diaryid,staffid,personid,vacancyid,diaryfrom,description) on existing update values ('TEST','TEST','TEST','TEST',dateformat(now(*),'yyyy-mm-dd'),'Requested Candidate');
select * from NetClientRequirementShortList('test.client','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*,Requested=1,Rejected=0;
insert into withholds (WithHoldsID,PersonID,CompanyID) values ('TEST','TEST','TEST');
select * from NetClientRequirementShortList('test.client','TEST',0);
expect EOF;
update withholds set CompanyID=null,VacancyID='TEST' where WithHoldsID='TEST';
select * from NetClientRequirementShortList('test.client','TEST',0);
expect EOF;
delete from withholds where WithHoldsID='TEST';
insert into diary (diaryid,staffid,personid,vacancyid,diaryfrom,description) on existing update values ('TEST2','TEST','TEST','TEST',dateformat(now(*),'yyyy-mm-dd'),'Shift Candidate Rejected');
select * from NetClientRequirementShortList('test.client','TEST',0);
expect EOF;
select * from NetClientRequirementShortList('test.client','TEST',1);
expect EOF;
 
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientRequirementShortList" IS 
{CREATE PROCEDURE pears."NetClientRequirementShortList"(IN pWebUserID CHAR(20),IN pRowID CHAR(20),IN IsShift CHAR(1))
RESULT(PersonName CHAR(60),PersonID CHAR(20),PersonStatus CHAR(50),Requested CHAR(1),Rejected CHAR(1),WithHeld CHAR(1))
// 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,
    (SELECT COUNT(*) FROM diary WHERE description LIKE 'Requested candidate%' AND personid=pe.personid AND vacancyid=p.vacancyid) AS Requested,
    (SELECT COUNT(*) FROM diary WHERE description LIKE 'Shift Candidate Rejected%' AND personid=pe.personid AND vacancyid=p.vacancyid) AS Rejected,
    (SELECT COUNT(*) FROM withholds w WHERE w.personid=p.personid AND (p.vacancyid=w.vacancyid OR e.companyid=w.companyid)) AS WithHeld
FROM 
    person pe
        KEY JOIN progress p
        KEY JOIN vacancy vac
        KEY JOIN employment em
        KEY JOIN company
        KEY JOIN "employment" AS "e"
        KEY JOIN "iqxnetuserlink" AS "i",
    progress p
        JOIN STATUS s ON s.status=p.status AND s.type='R'
WHERE
    "i"."iqxnetuserid" = pWebUserID
    AND p.vacancyid=VacID
    AND s.name !='placed'
    AND Rejected = 0
    AND WithHeld=0
END
 
/* DOC
2016-11-30 PC add requested / rejected, and add doc and test
2017-11-17 PC IW-266 remove candidate if Withheld    
2019-02-18 PC/ET IW-817 remove "no data"
2019-02-22 PC modify to expand to all vacancies for company, not just consultant
2019-02-27 PC remove duplicates
 
Tests:
    valid pWebUserID, Rowid, IsShift=0 with no events
    valid pWebUserID, Rowid, IsShift=0 with requested
    valid pWebUserID, Rowid, IsShift=0 with rejected
    valid pWebUserID, Rowid, IsShift=1 with requested and rejected
*/
 
/* 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.client';
insert into progress (progressid,vacancyid,personid,status,staffid) on existing update values ('TEST','TEST','TEST','#','TEST');
delete from withholds where WithHoldsID='TEST';
delete from diary where personid='test' and vacancyid='test';
select * from NetClientRequirementShortList('test.client','TEST',null);
expect EOF;
update status set publishtoweb=1 where type='R' and status in ('A','#'); 
select * from NetClientRequirementShortList('test.client','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*,Requested=0,Rejected=0;
insert into diary (diaryid,staffid,personid,vacancyid,diaryfrom,description) on existing update values ('TEST','TEST','TEST','TEST',dateformat(now(*),'yyyy-mm-dd'),'Requested Candidate');
select * from NetClientRequirementShortList('test.client','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*,Requested=1,Rejected=0;
insert into withholds (WithHoldsID,PersonID,CompanyID) values ('TEST','TEST','TEST');
select * from NetClientRequirementShortList('test.client','TEST',0);
expect EOF;
update withholds set CompanyID=null,VacancyID='TEST' where WithHoldsID='TEST';
select * from NetClientRequirementShortList('test.client','TEST',0);
expect EOF;
delete from withholds where WithHoldsID='TEST';
insert into diary (diaryid,staffid,personid,vacancyid,diaryfrom,description) on existing update values ('TEST2','TEST','TEST','TEST',dateformat(now(*),'yyyy-mm-dd'),'Shift Candidate Rejected');
select * from NetClientRequirementShortList('test.client','TEST',0);
expect EOF;
select * from NetClientRequirementShortList('test.client','TEST',1);
expect EOF;
 
 
*/
}