pears.NetTimesheetEndDate

CREATE FUNCTION "pears"."NetTimesheetEndDate"( IN "isProvisional" tinyint,IN "byShift" tinyint,IN "id" CHAR(20) ) 
RETURNS DATE
BEGIN
  DECLARE "lastShiftdate" DATE;
  DECLARE "Weekenddate" DATE;
  DECLARE "DeskType" CHAR(1);
  DECLARE "ExtNo" INTEGER; -- 2 denotes completed provisonal - can look up tempprovtimesheetshift
  DECLARE "VacID" CHAR(20);
  DECLARE "PersID" CHAR(20);
  SET "lastShiftdate" = NULL;
  SET "Weekenddate" = NULL;
  IF "isProvisional" = 1 THEN
    SELECT "d"."desktype","t"."extnumber","t"."vacancyid","t"."personid","weekmonthenddate"("t"."period","t"."periodlength") INTO "DeskType",
      "ExtNo","VacID","PersID",
      "Weekenddate" FROM "tempprovtimesheet" AS "t" KEY JOIN "tempdesk" AS "d" WHERE "t"."tempprovtimesheetid" = "id";
    IF "DeskType" = 'S' AND "byShift" = 1 THEN
      IF "ExtNo" = 2 THEN
        SELECT "max"("s"."shiftdate") INTO "lastShiftdate" FROM "tempprovtimesheetshift" AS "t" KEY JOIN "tempshift" AS "s" WHERE "t"."tempprovtimesheetid" = "id"
          AND "s"."state" IN( 'P','B' ) AND "s"."shiftdate" BETWEEN "Weekenddate"-6 AND "Weekenddate"
      ELSE
        SELECT "max"("s"."shiftdate") INTO "lastShiftdate" FROM "tempshift" AS "s" WHERE "s"."personid" = "PersID" AND "s"."vacancyid" = "VacID"
          AND "s"."state" IN( 'P','B' ) AND "s"."shiftdate" BETWEEN "Weekenddate"-6 AND "Weekenddate"
      END IF
    END IF
  ELSE SELECT "d"."desktype","isnull"("t"."enddate","weekmonthenddate"("t"."period","t"."periodlength")) INTO "DeskType",
      "Weekenddate" FROM "temptimesheet" AS "t" KEY JOIN "tempdesk" AS "d" WHERE "t"."temptimesheetid" = "id";
    IF "DeskType" = 'S' AND "byShift" = 1 THEN
      SELECT "max"("s"."shiftdate") INTO "lastShiftdate" FROM "temptimesheetline" AS "t" KEY JOIN "tempshift" AS "s" WHERE "t"."temptimesheetid" = "id"
        AND "s"."state" = 'W'
    END IF END IF;
  RETURN "isnull"("lastShiftdate","Weekenddate")
END /* DOC
2017-06-26 PC Test and doc
2017-07-11 PC change ExtNo from smallint to int
*/
/* 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.candidate';
update tempdesk set desktype='S' where tempdeskid='test';
update tempshift set temptimesheetid='test',state='W' where tempshiftid='test';
insert into temptimesheet (temptimesheetid,placementid,period,periodlength,serialnumber,tempdeskid,personid,estatus) on existing skip values ('TEST','TEST',weekcontaining(now(*)),'W',99,'test','TEST',3);
insert into temptimesheetline (temptimesheetlineid,temptimesheetid,tempshiftid,temppaybandid) on existing update values ('test','test','test','test1');
select isnull(NetTimesheetEndDate(null,null,null),'1900-01-01') as A;
expect A=01/01/1900;
select isnull(NetTimesheetEndDate(0,null,null),'1900-01-01') as B;
expect B=01/01/1900;
select isnull(NetTimesheetEndDate(0,0,null),'1900-01-01') as C;
expect C=01/01/1900;
select (if dateformat(isnull(NetTimesheetEndDate(0,1,'TEST'),'1900-01-01'),'dd/mm/yyyy') = dateformat(today(*),'dd/mm/yyyy') then 1 else 0 endif) as D;
expect D=1;
select isnull(NetTimesheetEndDate(1,0,null),'1900-01-01') as E;
expect E=01/01/1900;
select isnull(NetTimesheetEndDate(1,1,null),'1900-01-01') as F;
expect F=01/01/1900;
*/