pears.NetReportShiftHours

CREATE PROCEDURE "pears"."NetReportShiftHours"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__Hr" CHAR(100),"Job__1S" CHAR(100),"Shift__2S" CHAR(100),"Hours__RMT" DOUBLE ) 
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  DECLARE "rTitle" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "Cos"(
    "CompanyID" CHAR(20) NULL,
    "xDate" DATE 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";
  UPDATE "Cos" SET "xDate" = "IQXNetCompanyStartDate"("companyid");
  SELECT "rTitle",
    "p"."description" AS "ShiftDesc",
    "string"(CASE "dow"("s"."shiftdate") WHEN 1 THEN 'Sunday ' WHEN 7 THEN 'Saturday '
    ELSE 'Weekday '
    END,"st"."description") AS "ShiftType",
    "sum"("getshiftlength"("s"."timefrom","s"."timeto","s"."breakminutes")) AS "ShiftHours"
    FROM "tempshift" AS "s" KEY JOIN "tempshiftplan" AS "p" KEY LEFT OUTER JOIN "tempshifttype" AS "st"
      ,"tempshiftplan" AS "p" KEY JOIN "vacancy" KEY JOIN "employment" JOIN "cos" ON "employment"."companyid" = "cos"."companyid"
    WHERE "s"."state" <> 'C' AND "s"."shiftdate" BETWEEN "pstart" AND "pend" AND "s"."shiftdate" >= "cos"."xDate"
    GROUP BY GROUPING sets((),("ShiftDesc"),("ShiftType"),(("ShiftDesc"),("ShiftType"),"rTitle"))
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportShiftHours" IS 
{CREATE PROCEDURE pears."NetReportShiftHours"(IN pWebUserID CHAR(20),IN From__D CHAR(20),IN To__D CHAR(20))
RESULT(Title__Hr CHAR(100),Job__1S CHAR(100),Shift__2S CHAR(100),Hours__RMT DOUBLE)
BEGIN
  DECLARE pstart DATE;
  DECLARE pend DATE;
  DECLARE rTitle CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE Cos(
    CompanyID CHAR(20) NULL,
    xDate DATE 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;
  UPDATE Cos SET xDate = IQXNetCompanyStartDate(companyid);
  SELECT rTitle,
    p.description AS ShiftDesc,
    string(CASE dow(s.shiftdate) WHEN 1 THEN 'Sunday ' WHEN 7 THEN 'Saturday '
    ELSE 'Weekday '
    END,st.description) AS ShiftType,
    SUM(getshiftlength(s.timefrom,s.timeto,s.breakminutes)) AS ShiftHours
    FROM tempshift AS s KEY JOIN tempshiftplan AS p KEY LEFT OUTER JOIN tempshifttype AS st
    ,tempshiftplan AS p KEY JOIN vacancy KEY JOIN employment JOIN cos ON employment.companyid = cos.companyid
    WHERE s.state <> 'C' AND s.shiftdate BETWEEN pstart AND pend AND s.shiftdate >= cos.xDate
    GROUP BY GROUPING sets((),(ShiftDesc),(ShiftType),(ShiftDesc,ShiftType,rTitle))
END
}