pears.NetReportSpend

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
}