====== pears.NetTimesheetEndDate ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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;
*/
</code>