pears.NetTimesheetShifts

CREATE PROCEDURE "pears"."NetTimesheetShifts"( IN "pWebUserID" CHAR(20),IN "pTempTimesheetID" CHAR(20) ) 
RESULT( "tempshiftid" CHAR(20),"shiftdate" DATE,"timefrom" TIME,"timeto" TIME,"breakminutes" SMALLINT,"description" CHAR(50),"weekenddate" DATE,"shiftduration" DOUBLE,"referencecode" CHAR(20) ) 
// IQXWeb
BEGIN
  SELECT "s"."tempshiftid","s"."shiftdate","s"."timefrom","s"."timeto","s"."breakminutes",
    "p"."description","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
    "getshiftlength"("s"."timefrom","s"."timeto","s"."breakminutes") AS "shiftduration",
    (IF "s"."referencecode" = 'Self-Booked' THEN '' ELSE "s"."referenceCode" endif)
    FROM "tempshift" AS "s" KEY JOIN "temptimesheet" AS "t"
      ,"tempshift" AS "s" KEY LEFT OUTER JOIN "tempshiftplan" AS "p"
      ,"temptimesheet" AS "t" KEY JOIN "person"
    WHERE "t"."temptimesheetid" = "pTempTimesheetID"
    AND "s"."shiftdate" BETWEEN "weekenddate"-6 AND "weekenddate"
    ORDER BY "s"."shiftdate" ASC,"s"."timefrom" ASC
END /* DOC
2018-01-24 PC MS-1 allow Self Booked to be overriden by text in a switch
2018-01-29 MHS Simplified above: 'Self-Booked' description always suppressed
 
*/
/* TEST
update tempshift set temptimesheetid='test' where tempshiftid='test';
select * from NetTimesheetShifts("pWebUserID" = '',"pTempTimesheetID" = 'test');
expect tempshiftid=TEST;
update tempshift set temptimesheetid=null where tempshiftid='test';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheetShifts" IS 
{CREATE PROCEDURE pears."NetTimesheetShifts"(IN pWebUserID CHAR(20),IN pTempTimesheetID CHAR(20))
RESULT(tempshiftid CHAR(20),shiftdate DATE,timefrom TIME,timeto TIME,breakminutes SMALLINT,description CHAR(50),weekenddate DATE,shiftduration DOUBLE, referencecode CHAR(20))
// IQXWeb
BEGIN
 
  SELECT s.tempshiftid,s.shiftdate,s.timefrom,s.timeto,s.breakminutes,
    p.description,weekmonthenddate(t.period,t.periodlength) AS weekenddate,
    getshiftlength(s.timefrom,s.timeto,s.breakminutes) AS shiftduration,
    (IF s.referencecode = 'Self-Booked' THEN '' ELSE s.referenceCode endif)
    FROM tempshift AS s KEY JOIN temptimesheet AS t
    ,tempshift AS s KEY LEFT OUTER JOIN tempshiftplan AS p
    ,temptimesheet AS t KEY JOIN person
    WHERE t.temptimesheetid = pTempTimesheetID
    AND s.shiftdate BETWEEN weekenddate-6 AND weekenddate ORDER BY
    s.shiftdate ASC,s.timefrom ASC
END
 
/* DOC
2018-01-24 PC MS-1 allow Self Booked to be overriden by text in a switch
2018-01-29 MHS Simplified above: 'Self-Booked' description always suppressed
 
*/
 
/* TEST
update tempshift set temptimesheetid='test' where tempshiftid='test';
select * from NetTimesheetShifts("pWebUserID" = '',"pTempTimesheetID" = 'test');
expect tempshiftid=TEST;
update tempshift set temptimesheetid=null where tempshiftid='test';
*/
}