pears.NetReportShiftFill

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
}