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"."NetTimesheetsAll"( IN "pWebUserID" CHAR(20) ) 
RESULT( "temptimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"weekenddate" DATE,"timesheettype" CHAR(1),"timesheettotal" DOUBLE,"companyaddress" CHAR(250),"theirref" CHAR(50) ) 
BEGIN
  DECLARE "userClass" CHAR(20);
  DECLARE @companycount INTEGER;
  DECLARE "divid" CHAR(20);
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'CANDIDATE' THEN
    SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
      IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
        AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
          AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
          OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
        ELSE 'C'
        endif endif AS "timesheettype",
      (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress",
      "t"."theirref"
      FROM "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 "iqxnetuserlink"
      WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid"
      ORDER BY "t"."serialnumber" DESC
  ELSE IF "userClass" = 'AGENCY' THEN
      SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
          AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
            AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
            OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
          ELSE 'C'
          endif endif AS "timesheettype",
        (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress",
        "t"."theirref"
        FROM "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" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink"
        WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid")
        ORDER BY "t"."serialnumber" DESC
    ELSE IF "userClass" = 'OWNER' THEN
        SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
        SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
          IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
            AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
              AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
              OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
            ELSE 'C'
            endif endif AS "timesheettype",
          (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress",
          "t"."theirref"
          FROM "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"."divisionid" = "divid" AND "person"."divisionid" = "divid"
          ORDER BY "t"."serialnumber" DESC
      ELSE -- CLIENT
        SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
        SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
          IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
            AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
              AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
              OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
            ELSE 'C'
            endif endif AS "timesheettype",
          (SELECT "sum"("unitscharged"*"chargerate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",
          (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid")
          ELSE ''
          endif) AS "CompanyAddress","t"."theirref"
          FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "employment" AS "allemps" KEY JOIN "iqxnetuserlink"
            ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
            ,"temptimesheet" AS "t" KEY JOIN "person"
          WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid")
          ORDER BY "t"."serialnumber" DESC
      END IF
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheetsAll" IS 
{CREATE PROCEDURE pears."NetTimesheetsAll"( IN "pWebUserID" CHAR(20) ) 
RESULT( "temptimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"weekenddate" DATE,"timesheettype" CHAR(1),"timesheettotal" DOUBLE,"companyaddress" CHAR(250),"theirref" CHAR(50) ) 
BEGIN
  DECLARE "userClass" CHAR(20);
  DECLARE @companycount INTEGER;
  DECLARE "divid" CHAR(20);
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'CANDIDATE' THEN
    SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
      IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
        AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
          AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
          OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
        ELSE 'C'
        endif endif AS "timesheettype",
      (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress",
      "t"."theirref"
      FROM "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 "iqxnetuserlink"
      WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid"
      ORDER BY "t"."serialnumber" DESC
  ELSE IF "userClass" = 'AGENCY' THEN
      SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
          AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
            AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
            OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
          ELSE 'C'
          endif endif AS "timesheettype",
        (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress",
        "t"."theirref"
        FROM "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" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink"
        WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid")
        ORDER BY "t"."serialnumber" DESC
    ELSE IF "userClass" = 'OWNER' THEN
        SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
        SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
          IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
            AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
              AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
              OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
            ELSE 'C'
            endif endif AS "timesheettype",
          (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress",
          "t"."theirref"
          FROM "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"."divisionid" = "divid" AND "person"."divisionid" = "divid"
          ORDER BY "t"."serialnumber" DESC
      ELSE -- CLIENT
        SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
        SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
          IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
            AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
              AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
              OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
            ELSE 'C'
            endif endif AS "timesheettype",
          (SELECT "sum"("unitscharged"*"chargerate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",
          (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid")
          ELSE ''
          endif) AS "CompanyAddress","t"."theirref"
          FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "employment" AS "allemps" KEY JOIN "iqxnetuserlink"
            ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
            ,"temptimesheet" AS "t" KEY JOIN "person"
          WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid")
          ORDER BY "t"."serialnumber" DESC
      END IF
    END IF
  END IF
END
}
pears.NetTimesheetsAll

Original SQL

iqx
