pears.NetProvTimesheetTimes

CREATE PROCEDURE "pears"."NetProvTimesheetTimes"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20) ) 
RESULT( "placementid" CHAR(20),"weekstartdate" DATE,"shiftdate" DATE,"dayticked" SMALLINT,"tickednormalhours" DOUBLE,"hours" DOUBLE,"timefrom" TIME ) 
// IQXWeb
BEGIN
  SELECT "p"."placementid",
    "weekmonthenddate"("t"."period","t"."periodlength")-6 AS "weekstartdate",
    "date"("dateadd"("day","r"."row_num"-1,"weekstartdate")) AS "shiftdate",
    (CASE "dow"("shiftdate")-1 WHEN 0 THEN "p"."worksunday" WHEN 1 THEN "p"."workmonday" WHEN 2 THEN "p"."worktuesday" WHEN 3 THEN "p"."workwednesday"
    WHEN 4 THEN "p"."workthursday" WHEN 5 THEN "p"."workfriday" WHEN 6 THEN "p"."worksaturday" END) AS "dayticked",
    (IF "isnull"("dayticked",0) = 1 THEN "p"."worknormalhours" ELSE NULL endif) AS "tickednormalhours",
    (IF "isnull"("v"."workcancelled",0) = 0 THEN "isnull"("v"."workhours","tickednormalhours") ELSE NULL endif) AS "hours",
    "isnull"("v"."workstarttime","p"."workstarttime") AS "timefrom"
    FROM("placement" AS "p" LEFT OUTER JOIN "placementdayvariation" AS "v"
      ON "p"."placementid" = "v"."placementid" AND "v"."variationdate" = "shiftdate")
      ,"placement" AS "p" JOIN "dbo"."rowgenerator" AS "r" ON "r"."row_num" BETWEEN 1 AND 7
      ,"placement" AS "p" KEY JOIN "tempprovtimesheet" AS "t"
      ,"placement" AS "p" KEY JOIN "employment" AS "e"
    WHERE "t"."tempprovtimesheetid" = "pTempProvTimesheetID"
    AND "shiftdate" BETWEEN "isnull"("e"."startdate","shiftdate") AND "isnull"("e"."leavedate","shiftdate")
END /* DOC
2017-06-12 PC Test and doc
 
*/
/* 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';
select * from NetProvTimesheetTimes(null,null);
expect EOF;
select * from NetProvTimesheetTimes('test.candidate',null);
expect EOF;
select * from NetProvTimesheetTimes('test.candidate','TEST');
expect placementid=TEST;
update tempprovtimesheet set period= WeekContaining(today(*)) where tempprovtimesheetid='test';
select (if charindex(dateformat(today(*),'yyyy-mm-dd'),list(shiftdate,'~')) > 0 then 1 else 0 endif) as A from NetProvTimesheetTimes('test.candidate','TEST');
expect A=1;
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetTimes" IS 
{CREATE PROCEDURE pears."NetProvTimesheetTimes"(IN pWebUserID CHAR(20),IN pTempProvTimesheetID CHAR(20))
RESULT(placementid CHAR(20),weekstartdate DATE,shiftdate DATE,dayticked SMALLINT,tickednormalhours DOUBLE,hours DOUBLE,timefrom TIME)
// IQXWeb
BEGIN
  SELECT p.placementid,
    weekmonthenddate(t.period,t.periodlength)-6 AS weekstartdate,
    "date"(dateadd(DAY,r.row_num-1,weekstartdate)) AS shiftdate,
    (CASE dow(shiftdate)-1 WHEN 0 THEN p.worksunday WHEN 1 THEN p.workmonday WHEN 2 THEN p.worktuesday WHEN 3 THEN p.workwednesday
    WHEN 4 THEN p.workthursday WHEN 5 THEN p.workfriday WHEN 6 THEN p.worksaturday END) AS dayticked,
    (IF isnull(dayticked,0) = 1 THEN p.worknormalhours ELSE NULL
    endif) AS tickednormalhours,(IF isnull(v.workcancelled,0) = 0 THEN isnull(v.workhours,tickednormalhours) ELSE NULL
    endif) AS hours,isnull(v.workstarttime,p.workstarttime) AS timefrom
    FROM(placement AS p LEFT OUTER JOIN placementdayvariation AS v
    ON p.placementid = v.placementid AND v.variationdate = shiftdate)
    ,placement AS p JOIN dbo.rowgenerator AS r ON r.row_num BETWEEN 1 AND 7
    ,placement AS p KEY JOIN tempprovtimesheet AS t
    ,placement AS p KEY JOIN employment AS e
    WHERE t.tempprovtimesheetid = pTempProvTimesheetID
    AND shiftdate BETWEEN isnull(e.startdate,shiftdate) AND isnull(e.leavedate,shiftdate)
END
 
/* DOC
2017-06-12 PC Test and doc
 
*/
 
/* 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';
select * from NetProvTimesheetTimes(null,null);
expect EOF;
select * from NetProvTimesheetTimes('test.candidate',null);
expect EOF;
select * from NetProvTimesheetTimes('test.candidate','TEST');
expect placementid=TEST;
update tempprovtimesheet set period= WeekContaining(today(*)) where tempprovtimesheetid='test';
select (if charindex(dateformat(today(*),'yyyy-mm-dd'),list(shiftdate,'~')) > 0 then 1 else 0 endif) as A from NetProvTimesheetTimes('test.candidate','TEST');
expect A=1;
 
*/
}