pears.NetClientPreviousEmployeeJobs

CREATE PROCEDURE "pears"."NetClientPreviousEmployeeJobs"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) ) 
RESULT( "Position" CHAR(250),"StartDate" DATE,"FinishDate" DATE ) 
// IQXWeb
BEGIN
  SELECT "e"."position",
    "e"."startdate",
    "e"."leavedate" AS "finishdate"
    FROM "employment" AS "e"
    WHERE "e"."personid" = "pPersonID"
    AND "e"."startdate" >= "IQXNetCompanyStartDate"("e"."companyid")
    AND "e"."companyid" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    ORDER BY "e"."startdate" DESC
END /* DOC
 
2019-02-21 PC IW-830 display candidates previous employments as popup
 
*/
/* TEST
call NetTestSetup('');
select * from NetClientPreviousEmployeeJobs('TEST','TEST');
expect EOF;
update employment set startdate=current date where personid='test';
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='TEST';
select * from NetClientPreviousEmployeeJobs('TEST','TEST');
expect position=TestPosition;
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientPreviousEmployeeJobs" IS 
{CREATE PROCEDURE pears."NetClientPreviousEmployeeJobs"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) ) 
RESULT( "Position" CHAR(250),"StartDate" DATE,"FinishDate" DATE ) 
// IQXWeb
BEGIN
    SELECT 
        e.position,
        e.startdate,
        e.leavedate AS finishdate
    FROM 
        employment e 
    WHERE
        e.personid = pPersonID 
        AND e.startdate >= IQXNetCompanyStartDate(e.companyid)
        AND e.companyid = any(SELECT companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID)
    ORDER BY 
        e.startdate DESC
END
 
/* DOC
 
2019-02-21 PC IW-830 display candidates previous employments as popup
 
*/
 
/* TEST
call NetTestSetup('');
select * from NetClientPreviousEmployeeJobs('TEST','TEST');
expect EOF;
update employment set startdate=current date where personid='test';
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='TEST';
select * from NetClientPreviousEmployeeJobs('TEST','TEST');
expect position=TestPosition;
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='TEST';
*/
}