pears.NetClientRequirementShortListPlaced

CREATE PROCEDURE "pears"."NetClientRequirementShortListPlaced"( IN "pWebUserID" CHAR(20),IN "pRowID" CHAR(20),IN "IsShift" CHAR(1),IN "IsCurrent" CHAR(1) ) 
RESULT( "PersonName" CHAR(60),"PersonID" CHAR(20),"PersonStatus" CHAR(20),"StartDate" CHAR(20),"LeaveDate" CHAR(20),"CurrentLeave" DATE ) 
// IQXWeb
BEGIN
  DECLARE "VacID" CHAR(20);
  SET "VacID" = "pRowID";
  IF "IsShift" = 1 THEN
    SET "VacID" = (SELECT "VacancyID" FROM "TempShiftPlan" WHERE "tempshiftplanid" = "pRowID")
  END IF;
  IF NOT EXISTS(SELECT * FROM "placement" AS "p" KEY JOIN "employment" KEY JOIN "person" WHERE "p"."vacancyid" = "VacID") AND NOT EXISTS(SELECT * FROM "tempshift" AS "s" KEY JOIN "person" AS "pe" WHERE "s"."vacancyid" = "VacID" AND "s"."state" IN( 'W','B','P' ) ) THEN
    SELECT '','','','','',NULL;
    RETURN
  END IF;
  IF "isCurrent" = 'Y' THEN
    IF "IsShift" = 1 THEN
      SELECT "pe"."name",
        "pe"."personid",
        (CASE "S"."state" WHEN 'B' THEN 'Confirmed' WHEN 'W' THEN 'Worked' WHEN 'P' THEN 'Pending' END) AS "PersonStatus",
        "s"."shiftdate" AS "StartDate",
        "s"."shiftdate" AS "LeaveDate",
        "isnull"("s"."shiftdate",CURRENT DATE) AS "currentleave"
        FROM "tempshift" AS "s"
          KEY JOIN "person" AS "pe"
        WHERE "s"."tempshiftplanid" = "pRowID"
        AND "s"."state" IN( 'W','B','P' ) 
        ORDER BY "currentleave" DESC
    ELSE
      SELECT "pe"."name" AS "PersonName",
        "pe"."personid",
        'Placed' AS "PersonStatus",
        "e"."startdate" AS "StartDate",
        "e"."leavedate" AS "LeaveDate",
        "isnull"("e"."leavedate",CURRENT DATE) AS "currentleave"
        FROM "placement" AS "p"
          KEY JOIN "employment" AS "e"
          KEY JOIN "person" AS "pe"
        WHERE "p"."vacancyid" = "VacID" AND "currentleave" >= CURRENT DATE
        ORDER BY "currentleave" DESC,"PersonName" ASC
    END IF
  ELSE IF "IsShift" = 1 THEN
      SELECT DISTINCT
        "pe"."name",
        "pe"."personid",
        '' AS "PersonStatus",
        (SELECT "min"("sm"."shiftdate") FROM "tempshift" AS "sm" WHERE "sm"."vacancyid" = "s"."vacancyid" AND "sm"."personid" = "pe"."personid" AND "sm"."state" IN( 'W','B','P' ) AND "isnull"("s"."shiftdate",CURRENT DATE) < CURRENT DATE AND "sm"."state" IN( 'W','B','P' ) ),
        (SELECT "max"("sm"."shiftdate") FROM "tempshift" AS "sm" WHERE "sm"."vacancyid" = "s"."vacancyid" AND "sm"."personid" = "pe"."personid" AND "sm"."state" IN( 'W','B','P' ) AND "isnull"("s"."shiftdate",CURRENT DATE) < CURRENT DATE AND "sm"."state" IN( 'W','B','P' ) ),
        NULL AS "currentleave"
        FROM "tempshift" AS "s"
          KEY JOIN "person" AS "pe"
        WHERE "s"."vacancyid" = "VacID"
        AND "s"."state" IN( 'W','B','P' ) 
        AND "isnull"("s"."shiftdate",CURRENT DATE) < CURRENT DATE
    ELSE
      SELECT "pe"."name" AS "PersonName",
        "pe"."personid",
        'Placed' AS "PersonStatus",
        "e"."startdate" AS "StartDate",
        "e"."leavedate" AS "LeaveDate",
        "isnull"("e"."leavedate",CURRENT DATE) AS "currentleave"
        FROM "placement" AS "p"
          KEY JOIN "employment" AS "e"
          KEY JOIN "person" AS "pe"
        WHERE "p"."vacancyid" = "VacID"
        AND "currentleave" < CURRENT DATE
        ORDER BY "currentleave" DESC,"PersonName" ASC
    END IF
  END IF
END /* DOC
2016-11-30 PC add doc and test
2017-10-17 PC add employment dates
2018-03-23 JB IW-408
2018-05-02 PC,VS IW-408 revise to key on shiftid
2018-05-31 PC IW-565 condense rows into one with max and min dates
2019-01-10 PC resolve schema mismatch
2019-02-18 PC/ET IW-817 removed "no Data"
 
Tests:
valid pWebUserID, Rowid, IsShift=0
valid pWebUserID, Rowid, IsShift=1
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientRequirementShortListPlaced" IS 
{CREATE PROCEDURE pears."NetClientRequirementShortListPlaced"(IN pWebUserID CHAR(20),IN pRowID CHAR(20),IN IsShift CHAR(1),IN IsCurrent CHAR(1))
RESULT(PersonName CHAR(60),PersonID CHAR(20),PersonStatus CHAR(20),StartDate CHAR(20),LeaveDate CHAR(20),CurrentLeave DATE)
// IQXWeb
BEGIN
    DECLARE VacID CHAR(20);
    SET VacID=pRowID;	
 
    IF IsShift=1  THEN
        SET "VacID" = (SELECT VacancyID FROM "TempShiftPlan" WHERE "tempshiftplanid" = pRowID);
    END IF;
    IF NOT EXISTS( SELECT * FROM  placement p KEY JOIN employment  KEY JOIN person WHERE    p.vacancyid=VacID) AND NOT EXISTS(SELECT * FROM     tempshift s KEY JOIN person pe WHERE    s.vacancyid=VacID AND s.state IN ('W','B','P')) THEN
        SELECT '','','','','',NULL;    
        RETURN;
    END IF;
 
    IF isCurrent='Y' THEN
        IF IsShift=1  THEN
            SELECT
                pe.name,
                pe.personid, 
                (CASE S."state" WHEN 'B' THEN 'Confirmed' WHEN 'W' THEN 'Worked' WHEN 'P' THEN 'Pending' END CASE) AS PersonStatus,
                s.shiftdate AS "StartDate",
                s.shiftdate AS "LeaveDate",
                isnull(s.shiftdate, CURRENT DATE) AS currentleave
            FROM 
                tempshift s 
                    KEY JOIN person pe
            WHERE
                s.tempshiftplanid=pRowID
                AND s.state IN ('W','B','P')
            ORDER BY 
                currentleave  DESC
        ELSE
            SELECT
                pe.name AS PersonName,
                pe.personid, 
                'Placed' AS PersonStatus,
                e.startdate AS "StartDate",
                e.leavedate AS "LeaveDate",
                isnull(e.leavedate, CURRENT DATE) AS currentleave
            FROM 
                placement p
                KEY JOIN employment e
            KEY JOIN person pe
        WHERE
            p.vacancyid=VacID AND currentleave >= CURRENT DATE
        ORDER BY 
            currentleave  DESC, PersonName ASC
        END IF;
    ELSE
        IF IsShift=1  THEN
            SELECT DISTINCT
                pe.name,
                pe.personid, 
                '' AS PersonStatus,
                ( SELECT MIN(sm.shiftdate) FROM tempshift sm WHERE sm.vacancyid=s.vacancyid AND sm.personid=pe.personid AND sm.state IN ('W','B','P') AND isnull(s.shiftdate, CURRENT DATE) < CURRENT DATE AND sm.state IN ('W','B','P')),
                ( SELECT MAX(sm.shiftdate) FROM tempshift sm WHERE sm.vacancyid=s.vacancyid AND sm.personid=pe.personid AND sm.state IN ('W','B','P') AND isnull(s.shiftdate, CURRENT DATE) < CURRENT DATE AND sm.state IN ('W','B','P')),
                NULL AS currentleave
            FROM 
                tempshift s 
                    KEY JOIN person pe
            WHERE
                s.vacancyid=VacID 
                AND s.state IN ('W','B','P') 
                AND isnull(s.shiftdate, CURRENT DATE) < CURRENT DATE   
        ELSE
            SELECT
                pe.name AS PersonName,
                pe.personid, 
                'Placed' AS PersonStatus,
                e.startdate AS "StartDate",
                e.leavedate AS "LeaveDate",
                isnull(e.leavedate, CURRENT DATE) AS currentleave
            FROM 
                placement p
                    KEY JOIN employment e
                    KEY JOIN person pe
            WHERE
                p.vacancyid=VacID 
                AND currentleave < CURRENT DATE
            ORDER BY 
                currentleave  DESC, PersonName ASC
        END IF;
    END IF;
END
 
/* DOC
2016-11-30 PC add doc and test
2017-10-17 PC add employment dates
2018-03-23 JB IW-408
2018-05-02 PC,VS IW-408 revise to key on shiftid
2018-05-31 PC IW-565 condense rows into one with max and min dates
2019-01-10 PC resolve schema mismatch
2019-02-18 PC/ET IW-817 removed "no Data"
 
Tests:
    valid pWebUserID, Rowid, IsShift=0
    valid pWebUserID, Rowid, IsShift=1
*/
}