pears.NetReportUniTimesheets

CREATE PROCEDURE "pears"."NetReportUniTimesheets"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Company__1" CHAR(100),"Address__2" CHAR(100),"Department__3" CHAR(100),"Candidate__4" CHAR(100),"Contact" CHAR(100),"Job_Title" CHAR(100),"Start_Date__D" DATE,"End_Date__D" DATE,"Hours__RMT" DOUBLE,"Gross_Pay__RMT" DOUBLE,"Net_Charge__RMT" DOUBLE,"Charge_Inc_VAT__RMT" DOUBLE,"Details__Eat" CHAR(100) ) 
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" AS "CompanyName",
    "company"."addr1" AS "Address",
    "Employment"."Department",
    "Person"."Name" AS "Candidate",
    "Contact"."Name" AS "ContactName",
    "PEmp"."Position",
    "Pemp"."startdate" AS "EmpStartdate",
    "Pemp"."leavedate" AS "EmpLeavedate",
    (SELECT "sum"("UnitsCharged") FROM "TempTimeSheetLine" KEY JOIN "TempPayBand" WHERE "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID" AND "TempPayBand"."Unit" LIKE 'Hour%') AS "TotHours",
    (SELECT "sum"("UnitsPaid"*"PayRate") FROM "TempTimeSheetLine" WHERE "TempTimeSheetID" = "TempTimeSheet"."TempTimesheetID") AS "TotPaid",
    (SELECT "sum"("UnitsCharged"*"ChargeRate") FROM "TempTimesheetLine" WHERE "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID") AS "TotCharged",
    ("TotCharged"*"VATRate") AS "ChargeWVAT",'NetReportUniTimesheetLines&pTimesheetID='+"temptimesheet"."temptimesheetid" AS "Details"
    FROM "TempTimeSheet"
      KEY JOIN("Placement" KEY JOIN("Employment" AS "PEmp" KEY JOIN "Person","Vacancy"
      KEY JOIN("Employment" KEY JOIN("Person" AS "Contact",("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")
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportUniTimesheets" IS 
{CREATE PROCEDURE pears."NetReportUniTimesheets"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Company__1" CHAR(100),"Address__2" CHAR(100),"Department__3" CHAR(100),"Candidate__4" CHAR(100),"Contact" CHAR(100),"Job_Title" CHAR(100),"Start_Date__D" DATE,"End_Date__D" DATE,"Hours__RMT" DOUBLE,"Gross_Pay__RMT" DOUBLE,"Net_Charge__RMT" DOUBLE,"Charge_Inc_VAT__RMT" DOUBLE,"Details__Eat" CHAR(100) ) 
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" AS "CompanyName",
    "company"."addr1" AS "Address",
    "Employment"."Department",
    "Person"."Name" AS "Candidate",
    "Contact"."Name" AS "ContactName",
    "PEmp"."Position",
    "Pemp"."startdate" AS "EmpStartdate",
    "Pemp"."leavedate" AS "EmpLeavedate",
    (SELECT "sum"("UnitsCharged") FROM "TempTimeSheetLine" KEY JOIN "TempPayBand" WHERE "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID" AND "TempPayBand"."Unit" LIKE 'Hour%') AS "TotHours",
    (SELECT "sum"("UnitsPaid"*"PayRate") FROM "TempTimeSheetLine" WHERE "TempTimeSheetID" = "TempTimeSheet"."TempTimesheetID") AS "TotPaid",
    (SELECT "sum"("UnitsCharged"*"ChargeRate") FROM "TempTimesheetLine" WHERE "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID") AS "TotCharged",
    ("TotCharged"*"VATRate") AS "ChargeWVAT",'NetReportUniTimesheetLines&pTimesheetID='+"temptimesheet"."temptimesheetid" AS "Details"
    FROM "TempTimeSheet"
      KEY JOIN("Placement" KEY JOIN("Employment" AS "PEmp" KEY JOIN "Person","Vacancy"
      KEY JOIN("Employment" KEY JOIN("Person" AS "Contact",("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")
END
}