pears.NetAgencyCandidates

CREATE PROCEDURE "pears"."NetAgencyCandidates"( IN "pWebUserID" CHAR(20) ) 
RESULT( "PersonID" CHAR(20),"CandidateName" CHAR(100),"CandidateReference" CHAR(100),"CandidateStatus" CHAR(50) ) 
// IQXWeb
BEGIN
  DECLARE @SecAgID CHAR(20);
  SET @SecAgID = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY "employment"."employmentid" ASC);
  SELECT "person"."personid",
    "person"."name",
    "pay_employee"."secondaryagencyref",
    (SELECT "name" FROM "secondaryagencycandidatestatus" WHERE "secondaryagencycandidatestatusid" = "pay_employee"."secondaryagencycandidatestatus") AS "status"
    FROM "person" KEY JOIN "pay_employee"
    WHERE "pay_employee"."secondaryagencyid" = @secagid
    AND "person"."status" = 'A'
    ORDER BY "person"."keyname" ASC
END /* DOC
present a list of candidates who have worked for an agency in the past
2016-11-29 PC Doc and test
2018-4-17 MS hard-coded status='A' since that is standard and descriptions vary
Worked on tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries
*/
/* TEST
call NetTestSetup('');
update person set status='A' where personid='TEST';
delete from pay_employee where personid='TEST';
select * from NetAgencyCandidates('test.agency');
expect EOF;
insert into pay_employee(personid,secondaryagencyid,secondaryagencyref) values ('TEST','TEST','TestRef');
select * from NetAgencyCandidates('test.agency');
expect PersonID=TEST,CandidateName=test,CandidateReference=TestRef;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCandidates" IS 
{CREATE PROCEDURE pears."NetAgencyCandidates"(IN pWebUserID CHAR(20))
RESULT(PersonID CHAR(20),CandidateName CHAR(100),CandidateReference CHAR(100), CandidateStatus CHAR(50))
// IQXWeb
BEGIN
  DECLARE @SecAgID CHAR(20);
  SET @SecAgID=(SELECT top 1 employment.companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserlink.iqxnetuserid = pwebuserid ORDER BY employment.employmentid);
  SELECT person.personid,
    person.name,
    pay_employee.secondaryagencyref,
    (SELECT name FROM secondaryagencycandidatestatus WHERE secondaryagencycandidatestatusid=pay_employee.secondaryagencycandidatestatus) AS STATUS
    FROM person KEY JOIN pay_employee
  WHERE 
    pay_employee.secondaryagencyid = @secagid 
    AND person.status='A'
  ORDER BY
    person.keyname ASC
END
 
/* DOC
present a list of candidates who have worked for an agency in the past
2016-11-29 PC Doc and test
2018-4-17 MS hard-coded status='A' since that is standard and descriptions vary
          Worked on tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries
*/
 
/* TEST
call NetTestSetup('');
update person set status='A' where personid='TEST';
delete from pay_employee where personid='TEST';
select * from NetAgencyCandidates('test.agency');
expect EOF;
insert into pay_employee(personid,secondaryagencyid,secondaryagencyref) values ('TEST','TEST','TestRef');
select * from NetAgencyCandidates('test.agency');
expect PersonID=TEST,CandidateName=test,CandidateReference=TestRef;
*/
}