====== pears.NetClientVacanciesCandidateHistory ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
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*;
*/
}