====== pears.NetProvTimesheetShifts ======
<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 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';

*/
}
</code>