IQX Quick Menu

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
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
}
pears.NetReportTimesheetsExpanded

Original SQL

iqx
