pears.NetReportTimesheetsExpanded

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetReportTimesheetsExpanded"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Number__1" CHAR(20),"Temp" CHAR(60),"Position" CHAR(60),"Client__B" CHAR(100),"End_Date__DXA" DATE,"Pay_Type" CHAR(100),"Unit" CHAR(100),"Units__R" DOUBLE,"Rate__MR" DOUBLE,"Total__MR" DOUBLE ) 
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  DECLARE "pStartPeriod" INTEGER;
  DECLARE "pEndPeriod" INTEGER;
  DECLARE "rTitle" CHAR(100);
  DECLARE "userClass" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" 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 "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'CANDIDATE' THEN
    INSERT INTO "IDs"( "ID" ) 
      SELECT DISTINCT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
    SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
      "b"."description",
      "tl"."unitspaid",
      "tl"."payrate",
      "tl"."unitspaid"*"tl"."payrate"
      FROM "temppayband" AS "b" KEY JOIN "temptimesheetline" AS "tl" KEY JOIN "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
        ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
        ,"temptimesheet" AS "t" KEY JOIN "person"
      WHERE "t"."personid" = any(SELECT "id" FROM "ids")
      AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
      AND "t"."PeriodLength" = 'W'
      GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","b"."description","tl"."unitspaid","tl"."payrate"))
  ELSE IF "userClass" = 'AGENCY' THEN
      INSERT INTO "IDs"( "ID" ) 
        SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
      SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        "b"."description",
        "tl"."unitspaid",
        "tl"."payrate",
        "tl"."unitspaid"*"tl"."payrate"
        FROM "temppayband" AS "b" KEY JOIN "temptimesheetline" AS "tl" KEY JOIN "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
          ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
          ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp"
        WHERE "agcomp"."companyid" = any(SELECT "id" FROM "ids")
        AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid")
        AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
        AND "t"."PeriodLength" = 'W'
        GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","b"."description","tl"."unitspaid","tl"."payrate"))
    ELSE -- CLIENT
      INSERT INTO "IDs"( "ID" ) 
        SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
      SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        "b"."description",
        "tl"."unitscharged",
        "tl"."chargerate",
        "tl"."unitscharged"*"tl"."chargerate"
        FROM "temppayband" AS "b" KEY JOIN "temptimesheetline" AS "tl" KEY JOIN "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
          ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
          ,"temptimesheet" AS "t" KEY JOIN "person"
        WHERE "company"."companyid" = any(SELECT "id" FROM "ids")
        AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid")
        AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
        AND "t"."PeriodLength" = 'W'
        GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"t"."serialnumber","tempname","vacancy"."position","companyname","t"."temptimesheetid","b"."description","tl"."unitspaid","tl"."payrate"))
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportTimesheetsExpanded" IS 
{CREATE PROCEDURE pears."NetReportTimesheetsExpanded"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Number__1" CHAR(20),"Temp" CHAR(60),"Position" CHAR(60),"Client__B" CHAR(100),"End_Date__DXA" DATE,"Pay_Type" CHAR(100),"Unit" CHAR(100),"Units__R" DOUBLE,"Rate__MR" DOUBLE,"Total__MR" DOUBLE ) 
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  DECLARE "pStartPeriod" INTEGER;
  DECLARE "pEndPeriod" INTEGER;
  DECLARE "rTitle" CHAR(100);
  DECLARE "userClass" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" 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 "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'CANDIDATE' THEN
    INSERT INTO "IDs"( "ID" ) 
      SELECT DISTINCT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
    SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
      "b"."description",
      "tl"."unitspaid",
      "tl"."payrate",
      "tl"."unitspaid"*"tl"."payrate"
      FROM "temppayband" AS "b" KEY JOIN "temptimesheetline" AS "tl" KEY JOIN "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
        ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
        ,"temptimesheet" AS "t" KEY JOIN "person"
      WHERE "t"."personid" = any(SELECT "id" FROM "ids")
      AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
      AND "t"."PeriodLength" = 'W'
      GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","b"."description","tl"."unitspaid","tl"."payrate"))
  ELSE IF "userClass" = 'AGENCY' THEN
      INSERT INTO "IDs"( "ID" ) 
        SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
      SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        "b"."description",
        "tl"."unitspaid",
        "tl"."payrate",
        "tl"."unitspaid"*"tl"."payrate"
        FROM "temppayband" AS "b" KEY JOIN "temptimesheetline" AS "tl" KEY JOIN "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
          ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
          ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp"
        WHERE "agcomp"."companyid" = any(SELECT "id" FROM "ids")
        AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid")
        AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
        AND "t"."PeriodLength" = 'W'
        GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","b"."description","tl"."unitspaid","tl"."payrate"))
    ELSE -- CLIENT
      INSERT INTO "IDs"( "ID" ) 
        SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
      SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        "b"."description",
        "tl"."unitscharged",
        "tl"."chargerate",
        "tl"."unitscharged"*"tl"."chargerate"
        FROM "temppayband" AS "b" KEY JOIN "temptimesheetline" AS "tl" KEY JOIN "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
          ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
          ,"temptimesheet" AS "t" KEY JOIN "person"
        WHERE "company"."companyid" = any(SELECT "id" FROM "ids")
        AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid")
        AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
        AND "t"."PeriodLength" = 'W'
        GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"t"."serialnumber","tempname","vacancy"."position","companyname","t"."temptimesheetid","b"."description","tl"."unitspaid","tl"."payrate"))
    END IF
  END IF
END
}