pears.NetCandidateDisplayHolidayPay

CREATE PROCEDURE "pears"."NetCandidateDisplayHolidayPay"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "HolidayYear" CHAR(50),"WeeksWorked" CHAR(20),"WeeksHolidayEarned" CHAR(20),
  "WeeksHolidayTaken" CHAR(20),"WeeksHolidayDue" CHAR(20),
  "WeeklyHours" CHAR(20),"WeeklyPay" CHAR(20),"PayInLieu" CHAR(20),
  "DaysWorked" CHAR(20),"WeekNo" CHAR(20) ) 
// IQXWeb
BEGIN
  DECLARE "ThisYear" INTEGER;
  DECLARE "ThisWeek" INTEGER;
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  SET "ThisYear" = "left"("weekContaining"(CURRENT DATE),4);
  SET "ThisWeek" = "right"("weekContaining"(CURRENT DATE),2);
  SELECT "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",104), -- holiday year
    "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",101), -- weeks worked 
    "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",121), -- weeks holiday earned
    "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",122), -- weeks holiday taken
    "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",123), -- weeks holiday due
    "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",111), -- 12 week average weekly hours
    "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",113), -- 12 week average weekly pay
    "tempholidaycalc"("p"."personid","ThisYear","ThisWeek",131), -- pay-in-lieu earned
    "trim"("str"("round"("tempholidaycalc"("p"."personid","ThisYear","ThisWeek",4)*"tempholidaycalc"("p"."personid",2018,8,2),2),20,2)), -- average days worked
    "trim"("str"("ThisWeek")) -- week number
    FROM "person" AS "p" WHERE "p"."personid" = "pPersonID"
END /* DOC
2018-07-30 PC IW-528 display holiday pay details
2018-10-22 MHS IW-456 multi-cand fix and tests
*/
/* TEST
select * from NetCandidateDisplayHolidayPay(null);
expect eof;
select * from NetCandidateDisplayHolidayPay('test.candidate');
expect row;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDisplayHolidayPay" IS 
{CREATE PROCEDURE pears."NetCandidateDisplayHolidayPay"(IN pWebUserID CHAR(20), IN pPersonID CHAR(20) DEFAULT NULL)
RESULT(HolidayYear CHAR(50),WeeksWorked CHAR(20),WeeksHolidayEarned CHAR(20),
     WeeksHolidayTaken CHAR(20),WeeksHolidayDue CHAR(20),
     WeeklyHours CHAR(20),WeeklyPay CHAR(20),PayInLieu CHAR(20),
     DaysWorked CHAR(20),WeekNo CHAR(20))  
// IQXWeb
BEGIN 
  DECLARE ThisYear INT;
  DECLARE ThisWeek INT;
 
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
 
  SET ThisYear=LEFT(weekContaining(CURRENT DATE),4);
  SET ThisWeek=RIGHT(weekContaining(CURRENT DATE),2);
 
  SELECT  
    tempholidaycalc(p.personid,ThisYear,ThisWeek,104), -- holiday year
    tempholidaycalc(p.personid,ThisYear,ThisWeek,101), -- weeks worked 
    tempholidaycalc(p.personid,ThisYear,ThisWeek,121), -- weeks holiday earned
    tempholidaycalc(p.personid,ThisYear,ThisWeek,122), -- weeks holiday taken
    tempholidaycalc(p.personid,ThisYear,ThisWeek,123), -- weeks holiday due
    tempholidaycalc(p.personid,ThisYear,ThisWeek,111), -- 12 week average weekly hours
    tempholidaycalc(p.personid,ThisYear,ThisWeek,113), -- 12 week average weekly pay
    tempholidaycalc(p.personid,ThisYear,ThisWeek,131), -- pay-in-lieu earned
    TRIM(str(round(tempholidaycalc(p.personid,ThisYear,ThisWeek,4)*tempholidaycalc(p.personid,2018,8,2),2),20,2)), -- average days worked
    TRIM(str(ThisWeek)) -- week number
  FROM 
    person p WHERE p.personid=pPersonID;
 
END
 
/* DOC
2018-07-30 PC IW-528 display holiday pay details
2018-10-22 MHS IW-456 multi-cand fix and tests
*/
 
/* TEST
select * from NetCandidateDisplayHolidayPay(null);
expect eof;
select * from NetCandidateDisplayHolidayPay('test.candidate');
expect row;
*/
}