====== pears.NetReportShiftHours ======
<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"."NetReportShiftHours"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__Hr" char(100),"Job__1S" char(100),"Shift__2S" char(100),"Hours__RMT" double ) 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "rTitle" char(100);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    "xDate" date null,
    ) not transactional;
  set "pstart" = "iqxnetstringtodate"("From__D");
  set "pend" = "iqxnetstringtodate"("To__D");
  set "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  -- Get all the Companies the WebUser is Associated with
  insert into "Cos"( "CompanyID" ) 
    select distinct "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  update "Cos" set "xDate" = "IQXNetCompanyStartDate"("companyid");
  select "rTitle",
    "p"."description" as "ShiftDesc",
    "string"(case "dow"("s"."shiftdate") when 1 then 'Sunday ' when 7 then 'Saturday '
    else 'Weekday '
    end,"st"."description") as "ShiftType",
    "sum"("getshiftlength"("s"."timefrom","s"."timeto","s"."breakminutes")) as "ShiftHours"
    from "tempshift" as "s" key join "tempshiftplan" as "p" key left outer join "tempshifttype" as "st"
      ,"tempshiftplan" as "p" key join "vacancy" key join "employment" join "cos" on "employment"."companyid" = "cos"."companyid"
    where "s"."state" <> 'C' and "s"."shiftdate" between "pstart" and "pend" and "s"."shiftdate" >= "cos"."xDate"
    group by grouping sets((),("ShiftDesc"),("ShiftType"),(("ShiftDesc"),("ShiftType"),"rTitle"))
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportShiftHours" IS 
{create PROCEDURE pears."NetReportShiftHours"(in pWebUserID char(20),in From__D char(20),in To__D char(20))
result(Title__Hr char(100),Job__1S char(100),Shift__2S char(100),Hours__RMT double)
begin
  declare pstart date;
  declare pend date;
  declare rTitle char(100);
  declare local temporary table Cos(
    CompanyID char(20) null,
    xDate date null,
    ) not transactional;
  set pstart=iqxnetstringtodate(From__D);
  set pend=iqxnetstringtodate(To__D);
  set rTitle=dateformat(pstart,'dd/mm/yyyy')+' - '+dateformat(pend,'dd/mm/yyyy');
  -- Get all the Companies the WebUser is Associated with
  insert into Cos( CompanyID) 
    select distinct companyid from employment key join iqxnetuserlink where iqxnetuserid = pWebUserID;
  update Cos set xDate = IQXNetCompanyStartDate(companyid);
  select rTitle,
    p.description as ShiftDesc,
    string(case dow(s.shiftdate) when 1 then 'Sunday ' when 7 then 'Saturday '
    else 'Weekday '
    end,st.description) as ShiftType,
    sum(getshiftlength(s.timefrom,s.timeto,s.breakminutes)) as ShiftHours
    from tempshift as s key join tempshiftplan as p key left outer join tempshifttype as st
    ,tempshiftplan as p key join vacancy key join employment join cos on employment.companyid = cos.companyid
    where s.state <> 'C' and s.shiftdate between pstart and pend and s.shiftdate >= cos.xDate
    group by grouping sets((),(ShiftDesc),(ShiftType),(ShiftDesc,ShiftType,rTitle))
end
}
</code>