pears.NetProvTimesheetShifts

CREATE PROCEDURE "pears"."NetProvTimesheetShifts"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20) ) 
RESULT( "tempshiftid" CHAR(20),"shiftdate" DATE,"timefrom" TIME,"timeto" TIME,"breakminutes" SMALLINT,"tick" SMALLINT,"description" CHAR(50),"weekenddate" DATE,"shiftduration" DOUBLE,"referencecode" CHAR(20),"tempshifttypeid" CHAR(2),"tempshifttypedesc" CHAR(50) ) 
// IQXWeb
BEGIN
  DECLARE "ThisYear" INTEGER;
  DECLARE "ThisWeek" INTEGER;
  SET "ThisYear" = "weekcontaining"("now"())/100;
  SET "ThisWeek" = "weekcontaining"("now"())-("weekcontaining"("now"())/100)*100;
  SELECT "s"."tempshiftid","s"."shiftdate","s"."timefrom","s"."timeto","s"."breakminutes",
    (IF "t"."extnumber" = 2 THEN(SELECT FIRST 1 FROM "tempprovtimesheetshift" WHERE "tempprovtimesheetid" = "t"."tempprovtimesheetid" AND "tempshiftid" = "s"."tempshiftid") ELSE 1 endif) AS "tick",
    "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),
    "s"."tempshifttypeid","tempshifttype"."description"
    FROM "tempshift" AS "s" JOIN "tempprovtimesheet" AS "t" ON "s"."personid" = "t"."personid" AND "s"."vacancyid" = "t"."vacancyid"
      ,"tempshift" AS "s" KEY LEFT OUTER JOIN "tempshiftplan" AS "p"
      ,"tempshift" AS "s" KEY LEFT OUTER JOIN "tempshifttype"
      ,"tempprovtimesheet" AS "t" KEY JOIN "person"
    WHERE "t"."tempprovtimesheetid" = "pTempProvTimesheetID"
    AND "t"."extnumber" > 0 AND "s"."state" IN( 'P','B' ) AND "tick" = 1
    AND "s"."shiftdate" BETWEEN "weekenddate"-6 AND "weekenddate"
    ORDER BY "s"."shiftdate" ASC,"s"."timefrom" ASC
END /* DOC
2016-12-06 PC doc and test. Does not test correctly because of weekenddate not having parameters. Should be weekenddate(ThisYear,ThisWeek)
2018-01-24 PC MS-1 allow Self Booked to be overriden by text in a switch
2018-05-29 MHS Change to ALWAYS suppress 'Self-Booked' in reference
 
Inputs:
pWebUserID
pTempProvTimesheetID
 
Outputs:
tempshiftid
shiftdate
timefrom
timeto
breakminutes
tick
description
weekenddate
shiftduration
referencecode
tempshifttypeid
tempshifttypedesc
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid pTempProvTimesheetID
valid pWebUserID, pTempProvTimesheetID
 
*/
/* TEST
call NetTestSetup('');
 
insert into tempshifttype (tempshifttypeid,description) on existing update defaults off  values ('~~','test');
update tempshift set personid='TEST',state='B',tempshifttypeid='~~' where tempshiftid='TEST';
update tempprovtimesheet set extnumber=1 where tempprovtimesheetid='TEST';
select * from NetProvTimesheetShifts(null,null);
expect EOF;
select * from NetProvTimesheetShifts('test.candidate2',null);
expect EOF;
select * from NetProvTimesheetShifts('test.candidate','TEST2');
expect EOF;
update tempshift set tempshifttypeid=null where tempshiftid='TEST';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetShifts" IS 
{CREATE PROCEDURE pears."NetProvTimesheetShifts"(IN pWebUserID CHAR(20),IN pTempProvTimesheetID CHAR(20))
RESULT(tempshiftid CHAR(20),shiftdate DATE,timefrom TIME,timeto TIME,breakminutes SMALLINT,tick SMALLINT,description CHAR(50),weekenddate DATE,shiftduration DOUBLE, referencecode CHAR(20), tempshifttypeid CHAR(2), tempshifttypedesc CHAR(50) )
// IQXWeb
BEGIN
  DECLARE ThisYear INT;
  DECLARE ThisWeek INT;
 
  SET ThisYear = weekcontaining(now(*))/100;
  SET ThisWeek = weekcontaining(now(*))-(weekcontaining(now(*))/100)*100;
 
  SELECT s.tempshiftid,s.shiftdate,s.timefrom,s.timeto,s.breakminutes,
    (IF t.extnumber = 2 THEN(SELECT FIRST 1 FROM tempprovtimesheetshift WHERE tempprovtimesheetid = t.tempprovtimesheetid AND tempshiftid = s.tempshiftid) ELSE 1
    endif) AS tick,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),
    s.tempshifttypeid, tempshifttype.description
    FROM tempshift AS s JOIN tempprovtimesheet AS t ON s.personid = t.personid AND s.vacancyid = t.vacancyid,
    tempshift AS s KEY LEFT OUTER JOIN tempshiftplan AS p,
    tempshift AS s KEY LEFT OUTER JOIN tempshifttype,
    tempprovtimesheet AS t KEY JOIN person
    WHERE t.tempprovtimesheetid = pTempProvTimesheetID
    AND t.extnumber > 0 AND s.state IN( 'P','B') AND tick = 1
    AND s.shiftdate BETWEEN weekenddate-6 AND weekenddate 
  ORDER BY
    s.shiftdate ASC,s.timefrom ASC
END
 
/* DOC
2016-12-06 PC doc and test. Does not test correctly because of weekenddate not having parameters. Should be weekenddate(ThisYear,ThisWeek)
2018-01-24 PC MS-1 allow Self Booked to be overriden by text in a switch
2018-05-29 MHS Change to ALWAYS suppress 'Self-Booked' in reference
 
Inputs:
    pWebUserID
    pTempProvTimesheetID
 
Outputs:
    tempshiftid
    shiftdate
    timefrom
    timeto
    breakminutes
    tick
    description
    weekenddate
    shiftduration
    referencecode
    tempshifttypeid
    tempshifttypedesc
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid pTempProvTimesheetID
    valid pWebUserID, pTempProvTimesheetID
 
*/
 
/* TEST
call NetTestSetup('');
 
insert into tempshifttype (tempshifttypeid,description) on existing update defaults off  values ('~~','test');
update tempshift set personid='TEST',state='B',tempshifttypeid='~~' where tempshiftid='TEST';
update tempprovtimesheet set extnumber=1 where tempprovtimesheetid='TEST';
select * from NetProvTimesheetShifts(null,null);
expect EOF;
select * from NetProvTimesheetShifts('test.candidate2',null);
expect EOF;
select * from NetProvTimesheetShifts('test.candidate','TEST2');
expect EOF;
update tempshift set tempshifttypeid=null where tempshiftid='TEST';
 
*/
}