pears.NetReportGenevaShiftsAndInvoices

CREATE PROCEDURE "pears"."NetReportGenevaShiftsAndInvoices"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Shift_Ref__1" CHAR(10),"Date_Requested__2" CHAR(10),"Time_Requested" CHAR(5),"Ordered_By" CHAR(50),"Date_Of_Shift__3" CHAR(10),"Shift_Times" CHAR(20),"Position__4" CHAR(30),"Speciality__5" CHAR(30),"Hospital__6" CHAR(50),"Staff_Name__7" CHAR(50),"Confirmed_With" CHAR(50),"Order_Number" CHAR(20),"Cancelled" CHAR(10),"Invoice_Number__8" CHAR(20) ) 
-- 20120809 RC2 with Shift Ref
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  DECLARE "sites" long VARCHAR;
  DECLARE "rTitle" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "Cos"(
    "CompanyID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "Cos"( "CompanyID" ) SELECT DISTINCT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink"
      WHERE "iqxnetuserid" = "pWebUserID";
  SET "pstart" = "iqxnetstringtodate"("From__D");
  SET "pend" = "iqxnetstringtodate"("To__D");
  SET "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  SELECT "rTitle"+' - '+"c"."name",
    "tp"."shiftserialnumber" AS "ShiftSerial",
    "dateformat"("t"."whenentered",'dd/mm/yyyy') AS "DateRequested",
    "dateformat"("t"."whenentered",'hh:nn') AS "TimeRequested",
    "tp"."orderedby" AS "OrderedBy",
    "dateformat"("t"."shiftdate",'dd/mm/yyyy') AS "DateOfShift",
    CAST("dateformat"("t"."timefrom",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("t"."timeto",'hh:nn') AS CHAR) AS "ShiftTimes",
    "v"."position" AS "Position",
    (SELECT "td"."name" FROM "tempdesk" AS "td" WHERE "td"."tempdeskid" = "v"."tempdeskid") AS "Speciality",
    "v"."sitename" AS "Hospital",
    "isnull"("p"."name",'Unfilled') AS "StaffName",
    "t"."confirmedWith" AS "ConfirmedWith",
    "v"."theirref" AS "OrderNumber",
    "dateformat"("t"."whencancelled",'dd/mm/yyyy') AS "Cancelled",
    "d"."ourref" AS "Invoiced"
    FROM "vacancy" AS "v"
      KEY JOIN "TempShift" AS "t"
      KEY JOIN "person" AS "p"
      LEFT OUTER JOIN "tempshiftplan" AS "tp" ON "tp"."tempshiftplanid" = "t"."tempshiftplanid"
      LEFT OUTER JOIN "temptimesheet" AS "ts" ON "t"."temptimesheetid" = "ts"."temptimesheetid"
      LEFT OUTER JOIN "iqacdocument" AS "d" ON "ts"."temptimesheetid" = "d"."xrefid"
      LEFT OUTER JOIN "staff" AS "s" ON "s"."staffid" = "t"."staffid"
      LEFT OUTER JOIN "employment" AS "e" ON "e"."employmentid" = "v"."employmentid"
      KEY JOIN "company" AS "c"
    WHERE "t"."shiftdate" BETWEEN "pstart" AND "pend"
    AND "c"."companyid" = any(SELECT * FROM "Cos")
    ORDER BY "t"."shiftdate" ASC,
    "shifttimes" ASC,
    "v"."position" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportGenevaShiftsAndInvoices" IS 
{CREATE PROCEDURE pears."NetReportGenevaShiftsAndInvoices"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Shift_Ref__1" CHAR(10),"Date_Requested__2" CHAR(10),"Time_Requested" CHAR(5),"Ordered_By" CHAR(50),"Date_Of_Shift__3" CHAR(10),"Shift_Times" CHAR(20),"Position__4" CHAR(30),"Speciality__5" CHAR(30),"Hospital__6" CHAR(50),"Staff_Name__7" CHAR(50),"Confirmed_With" CHAR(50),"Order_Number" CHAR(20),"Cancelled" CHAR(10),"Invoice_Number__8" CHAR(20) ) 
-- 20120809 RC2 with Shift Ref
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  DECLARE "sites" long VARCHAR;
  DECLARE "rTitle" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "Cos"(
    "CompanyID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "Cos"( "CompanyID" ) SELECT DISTINCT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink"
      WHERE "iqxnetuserid" = "pWebUserID";
  SET "pstart" = "iqxnetstringtodate"("From__D");
  SET "pend" = "iqxnetstringtodate"("To__D");
  SET "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  SELECT "rTitle"+' - '+"c"."name",
    "tp"."shiftserialnumber" AS "ShiftSerial",
    "dateformat"("t"."whenentered",'dd/mm/yyyy') AS "DateRequested",
    "dateformat"("t"."whenentered",'hh:nn') AS "TimeRequested",
    "tp"."orderedby" AS "OrderedBy",
    "dateformat"("t"."shiftdate",'dd/mm/yyyy') AS "DateOfShift",
    CAST("dateformat"("t"."timefrom",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("t"."timeto",'hh:nn') AS CHAR) AS "ShiftTimes",
    "v"."position" AS "Position",
    (SELECT "td"."name" FROM "tempdesk" AS "td" WHERE "td"."tempdeskid" = "v"."tempdeskid") AS "Speciality",
    "v"."sitename" AS "Hospital",
    "isnull"("p"."name",'Unfilled') AS "StaffName",
    "t"."confirmedWith" AS "ConfirmedWith",
    "v"."theirref" AS "OrderNumber",
    "dateformat"("t"."whencancelled",'dd/mm/yyyy') AS "Cancelled",
    "d"."ourref" AS "Invoiced"
    FROM "vacancy" AS "v"
      KEY JOIN "TempShift" AS "t"
      KEY JOIN "person" AS "p"
      LEFT OUTER JOIN "tempshiftplan" AS "tp" ON "tp"."tempshiftplanid" = "t"."tempshiftplanid"
      LEFT OUTER JOIN "temptimesheet" AS "ts" ON "t"."temptimesheetid" = "ts"."temptimesheetid"
      LEFT OUTER JOIN "iqacdocument" AS "d" ON "ts"."temptimesheetid" = "d"."xrefid"
      LEFT OUTER JOIN "staff" AS "s" ON "s"."staffid" = "t"."staffid"
      LEFT OUTER JOIN "employment" AS "e" ON "e"."employmentid" = "v"."employmentid"
      KEY JOIN "company" AS "c"
    WHERE "t"."shiftdate" BETWEEN "pstart" AND "pend"
    AND "c"."companyid" = any(SELECT * FROM "Cos")
    ORDER BY "t"."shiftdate" ASC,
    "shifttimes" ASC,
    "v"."position" ASC
END
}