pears.NetClientVacanciesCandidateHistory

CREATE PROCEDURE "pears"."NetClientVacanciesCandidateHistory"( 
  IN "pWebUserID" CHAR(20),
  IN "pSlice" INTEGER DEFAULT 0,
  IN "pSliceSize" INTEGER DEFAULT 20,
  IN "pPersonID" CHAR(20),
  IN "pCompanyID" CHAR(20),
  IN "pOrderBy" CHAR(255) DEFAULT 'HistoryDate desc',
  IN "pFilterPosition" CHAR(255) DEFAULT '%',
  IN "pFilterStartDate" "dateTime",
  IN "pFilterEndDate" "dateTime",
  IN "pFilterCandidateRejectionReason" CHAR(255) DEFAULT '%',
  IN "pFilterOutcome" CHAR(255) DEFAULT '%' ) 
RESULT( 
  "Position" CHAR(50),
  "HistoryDate" "datetime",
  "CandidateRejectionReason" CHAR(100),
  "Outcome" CHAR(50),
  "IsFinal" SMALLINT ) 
// IQXNet
BEGIN
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  SET "x" = "pSliceSize";
  SET "y" = "x"*"pSlice"+1;
  SELECT top "x" START at "y"
    "v"."position" AS "Position",
    "min"("DATETIME"("ph"."historydate"+"ph"."historyTime")) AS "HistoryDate",
    "pr"."rejectionreason" AS "CandidateRejectionReason",
    "sh"."name" AS "Outcome",
    "isnull"("sh"."Final",0) AS "IsFinal"
    FROM "progress" AS "pr"
      KEY JOIN "vacancy" AS "v"
      KEY JOIN "employment" AS "e"
      ,"progress" AS "pr" KEY JOIN "progresshistory" AS "ph"
      LEFT OUTER JOIN "status" AS "sh" ON "sh"."type" = 'R' AND "sh"."status" = "ph"."status"
    WHERE "pr"."personid" = "pPersonID"
    AND "e"."CompanyID" = "pCompanyID"
    GROUP BY "ph"."progressid","Position","CandidateRejectionReason","Outcome","IsFinal"
    ORDER BY "HistoryDate" DESC
END /* DOC
2016-12-13 PC create a progress history list to allow a client to determine fitness for submission to a shortlist
2018-09-01 PC/ET IW-683 simplify sort order and remove search fields, changed linking to use key join
2020-05-27 GJ IW-1187 Fixed the proc so it will only show progress history for the relevant client, not EVERYONES progress history.
2020-06-24 GJ JW IW-1196 Procedure now returns the first one of each type also added IsFinal column so it can be indicated on the front end.
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid pPersonID
valid pWebUserID, pPersonID no progress history
valid pWebUserID, pPersonID, progress history
*/
/* 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';
update person set divisionid='TEST' where personid='TEST';
update staff set divisionid='TEST' where staffid='TEST';
insert into progress (progressid,vacancyid,status,staffid,personid,rejectionreason) on existing update values ('TEST','TEST','#','TEST','TEST','test');
insert into progresshistory (progresshistoryid,progressid,status,historydate,historytime,staffid) on existing update  values ('TEST','TEST','#',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'hh:mm:ss.sss'),'TEST');
select * from NetClientVacanciesCandidateHistory('test.client',0,10,'TEST','position asc','test',today(*)-1,today(*)+1,'test','Placed by other agency');
expect Position=test,Outcome=Placed*;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesCandidateHistory" IS 
{CREATE PROCEDURE pears."NetClientVacanciesCandidateHistory"(
    IN "pWebUserID" CHAR(20),
    IN pSlice INTEGER DEFAULT 0,
    IN pSliceSize INTEGER DEFAULT 20,
    IN "pPersonID" CHAR(20),
    IN "pCompanyID" CHAR(20),
    IN "pOrderBy" CHAR(255) DEFAULT 'HistoryDate desc',
    IN "pFilterPosition" CHAR(255) DEFAULT '%',
    IN "pFilterStartDate" dateTime,
    IN "pFilterEndDate" dateTime,
    IN "pFilterCandidateRejectionReason" CHAR(255) DEFAULT '%',
    IN "pFilterOutcome" CHAR(255) DEFAULT '%'
) 
 
RESULT(
    "Position" CHAR(50),
    "HistoryDate" datetime,
    "CandidateRejectionReason" CHAR(100),
    "Outcome" CHAR(50),
    "IsFinal" SMALLINT
)
 
// IQXNet
BEGIN
 
DECLARE x INTEGER;
DECLARE y INTEGER;
SET x = pSliceSize;
SET y = x * pSlice + 1;
 
SELECT  top x START at y
    v.position AS POSITION,
    MIN(DATETIME(ph.historydate + ph.historyTime)) AS "HistoryDate",
    pr.rejectionreason AS "CandidateRejectionReason",
    sh.name AS "Outcome",
    isnull(sh.Final,0) AS IsFinal
FROM
    progress pr
    KEY JOIN vacancy v
    KEY JOIN employment e,
    progress pr KEY JOIN progresshistory ph
    LEFT JOIN STATUS sh ON sh.type='R' AND sh.status=ph.status
WHERE 
    pr.personid = "pPersonID"
    AND
    e.CompanyID = "pCompanyID"
GROUP BY 
    ph.progressid, POSITION, CandidateRejectionReason, Outcome, IsFinal
ORDER BY
    HistoryDate DESC
END
 
/* DOC
2016-12-13 PC create a progress history list to allow a client to determine fitness for submission to a shortlist
2018-09-01 PC/ET IW-683 simplify sort order and remove search fields, changed linking to use key join
2020-05-27 GJ IW-1187 Fixed the proc so it will only show progress history for the relevant client, not EVERYONES progress history.
2020-06-24 GJ JW IW-1196 Procedure now returns the first one of each type also added IsFinal column so it can be indicated on the front end.
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid pPersonID
    valid pWebUserID, pPersonID no progress history
    valid pWebUserID, pPersonID, progress history
*/
 
/* 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';
update person set divisionid='TEST' where personid='TEST';
update staff set divisionid='TEST' where staffid='TEST';
insert into progress (progressid,vacancyid,status,staffid,personid,rejectionreason) on existing update values ('TEST','TEST','#','TEST','TEST','test');
insert into progresshistory (progresshistoryid,progressid,status,historydate,historytime,staffid) on existing update  values ('TEST','TEST','#',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'hh:mm:ss.sss'),'TEST');
select * from NetClientVacanciesCandidateHistory('test.client',0,10,'TEST','position asc','test',today(*)-1,today(*)+1,'test','Placed by other agency');
expect Position=test,Outcome=Placed*;
*/
}