====== pears.NetReportSpend ======
<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"."NetReportSpend"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__H" char(100),"Company__1" char(100),"Location__2" char(150),"Job_Title__3" char(100),"Timesheet_Count__RT" integer,"Hours__RMT" double,"Net_Charge__RMT" double,"Charge_Inc_VAT__RMT" double ) 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "pStartPeriod" integer;
  declare "pEndPeriod" integer;
  declare "rTitle" char(100);
  declare "VATRate" decimal(9,2);
  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');
  set "pStartPeriod" = "weekcontaining"("pstart");
  set "pEndPeriod" = "weekcontaining"("pend");
  set "VATRate" = (select "VATRate" from "IQacVATRate" where "Vatcode" = (select "DefaultVATCode" from "IQacParams"));
  set "VATRate" = (("VATRate"/100)+1);
  -- 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"."Name",
    "string"("Company"."Addr1",', ',"Company"."Town") as "Location",
    "Vacancy"."Position",
    "count"(distinct "TempTimeSheet"."TempTimeSheetID") as "NumTimeSheets",
    "sum"("TempTimeSheetLine"."UnitsCharged") as "Hours",
    "sum"("TempTimeSheetLine"."UnitsCharged"*"TempTimeSheetLine"."ChargeRate") as "Charge",
    ("Charge"*"VATRate") as "ChargeWVAT"
    from "TempTimeSheet"
      key join("TempTimeSheetLine","Placement" key join("Employment" as "PEmp" key join "Person"
      ,"Vacancy" key join("Employment" key join("Company" join "cos" on "company"."companyid" = "cos"."companyid"))))
    where "TempTimeSheet"."TransferBatch" >= 1
    and "TempTimeSheet"."Period" between "pStartPeriod" and "pEndPeriod"
    and "TempTimeSheet"."PeriodLength" = 'W'
    and "weekmonthenddate"("TempTimeSheet"."period","TempTimeSheet"."periodlength") >= "IQXNetCompanyStartDate"("company"."companyid")
    group by "Company"."name","Company"."Addr1","Company"."Town","Vacancy"."Position"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportSpend" IS 
{create procedure pears.NetReportSpend(in pWebUserID char(20),in From__D char(20),in To__D char(20))
result(Title__H char(100),Company__1 char(100),Location__2 char(150),Job_Title__3 char(100),Timesheet_Count__RT integer,Hours__RMT double,Net_Charge__RMT double,Charge_Inc_VAT__RMT double)
begin
  declare pstart date;
  declare pend date;
  declare pStartPeriod integer;
  declare pEndPeriod integer;
  declare rTitle char(100);
  declare VATRate decimal(9,2);
  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');
  set pStartPeriod=weekcontaining(pstart);
  set pEndPeriod=weekcontaining(pend);
  set VATRate=(select VATRate from IQacVATRate where Vatcode = (select DefaultVATCode from IQacParams));
  set VATRate=((VATRate/100)+1);
  -- 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.Name,
    string(Company.Addr1,', ',Company.Town) as Location,
    Vacancy.Position,
    count(distinct TempTimeSheet.TempTimeSheetID) as NumTimeSheets,
    sum(TempTimeSheetLine.UnitsCharged) as Hours,
    sum(TempTimeSheetLine.UnitsCharged*TempTimeSheetLine.ChargeRate) as Charge,
    (Charge*VATRate) as ChargeWVAT
    from TempTimeSheet
    key join(TempTimeSheetLine,Placement key join(Employment as PEmp key join Person
    ,Vacancy key join(Employment key join(Company join cos on company.companyid = cos.companyid))))
    where TempTimeSheet.TransferBatch >= 1
    and TempTimeSheet.Period between pStartPeriod and pEndPeriod
    and TempTimeSheet.PeriodLength = 'W'
    and weekmonthenddate(TempTimeSheet.period,TempTimeSheet.periodlength) >= IQXNetCompanyStartDate(company.companyid)
    group by Company.name,Company.Addr1,Company.Town,Vacancy.Position
end
}
</code>