====== pears.NetReportShiftFill ======
<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"."NetReportShiftFill"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__H" char(100),"CoID__H" char(20),"Company__1A" char(100),"Address__2" char(150),"xDate__DH" date,"Shifts_Added__RT" integer,"Filled__RT" integer,"Unfilled__RT" integer,"Fill_Percentage__RMT" double ) 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "rTitle" char(100);
  declare local temporary table "Cos"(
    "CompanyID" char(20) 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";
  select "rTitle",
    "company"."companyid",
    "company"."name" as "CompanyName",
    "GetCompanyAddressOnLine"("company"."companyid") as "CompanyAddress",
    "IQXNetCompanyStartDate"("Company"."companyid") as "NetStart",
    "sum"((select "count"() from "tempshiftplan" as "p" key join "vacancy" key join "employment"
      where "employment"."companyid" = "company"."companyid" and "p"."shiftdate" between "pstart" and "pend" and "p"."shiftdate" >= "NetStart")) as "ShiftsEntered",
    "sum"((select "count"() from "tempshift" as "s" key join "tempshiftplan" as "p" key join "vacancy" key join "employment"
      where "employment"."companyid" = "company"."companyid" and "s"."state" <> 'C' and "p"."shiftdate" between "pstart" and "pend" and "p"."shiftdate" >= "NetStart")) as "ShiftsFilled",
    "ShiftsEntered"-"ShiftsFilled" as "ShiftsUnfilled",
    if "ShiftsEntered" > 0 then 100.0*(1.0*"ShiftsFilled"/"ShiftsEntered") else 0.0 endif as "FillPercentage"
    from "company" join "cos" on "company"."companyid" = "cos"."companyid"
    group by grouping sets((),("rTitle","company"."companyid","CompanyName","CompanyAddress","NetStart"))
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportShiftFill" IS 
{create procedure pears.NetReportShiftFill(in pWebUserID char(20),in From__D char(20),in To__D char(20))
result(Title__H char(100),CoID__H char(20),Company__1A char(100),Address__2 char(150),xDate__DH date,Shifts_Added__RT integer,Filled__RT integer,Unfilled__RT integer,Fill_Percentage__RMT double)
begin
  declare pstart date;
  declare pend date;
  declare rTitle char(100);
  declare local temporary table Cos(
    CompanyID char(20) 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;
  select rTitle,
    company.companyid,
    company.name as CompanyName,
    GetCompanyAddressOnLine(company.companyid) as CompanyAddress,
    IQXNetCompanyStartDate(Company.companyid) as NetStart,
    sum((select count(*) from tempshiftplan as p key join vacancy key join employment
      where employment.companyid = company.companyid and p.shiftdate between pstart and pend and p.shiftdate >= NetStart)) as ShiftsEntered,
    sum((select count(*) from tempshift as s key join tempshiftplan as p key join vacancy key join employment
      where employment.companyid = company.companyid and s.state <> 'C' and p.shiftdate between pstart and pend and p.shiftdate >= NetStart)) as ShiftsFilled,
    ShiftsEntered-ShiftsFilled as ShiftsUnfilled,
    if ShiftsEntered > 0 then 100.0*(1.0*ShiftsFilled/ShiftsEntered) else 0.0
    endif as FillPercentage from company join cos on company.companyid = cos.companyid
    group by grouping sets((),(rTitle,company.companyid,CompanyName,CompanyAddress,NetStart))
end
}
</code>