pears.NetTimesheets

CREATE PROCEDURE "pears"."NetTimesheets"( IN "pWebUserID" CHAR(20),IN "pSlice" INTEGER DEFAULT 0,IN "pSliceSize" INTEGER DEFAULT 20,IN "pStartDate" CHAR(20) DEFAULT NULL,IN "pEndDate" CHAR(20) DEFAULT NULL ) 
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),"payrollnumber" CHAR(20),"hasScannedImage" SMALLINT,"awaitsApproval" SMALLINT ) 
// IQXWeb
BEGIN
  DECLARE "userClass" CHAR(20);
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  DECLARE @companycount INTEGER;
  DECLARE "DateRangeStart" DATE;
  DECLARE "DateRangeEnd" DATE;
  DECLARE "divid" CHAR(20);
  SET "x" = "pSliceSize";
  SET "y" = "x"*"pSlice"+1;
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  SET "DateRangeStart" = "isnull"("pStartDate","dateadd"("yy",-10,"now"()));
  SET "DateRangeEnd" = "isnull"("pEndDate","dateadd"("yy",1,"now"()));
  IF "userClass" = 'CANDIDATE' THEN
    SELECT top "x" START at "y" "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","isnull"("employment"."position","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",
      "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "CompanyAddress",
      "t"."theirref","person"."payrollnumber","ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",
      (IF "t"."estatus" = 3 THEN 1 ELSE 0 endif) AS "awaitsApproval"
      FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy","placement" KEY JOIN "employment" KEY JOIN "company"
        ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
        ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "iqxnetuserlink"
      WHERE "weekenddate" BETWEEN "DateRangeStart" AND "DateRangeEnd" AND "iqxnetuserlink"."iqxnetuserid" = "pwebuserid"
      ORDER BY "weekenddate" DESC,"companyname" ASC,"t"."serialnumber" ASC
  ELSE IF "userClass" = 'AGENCY' THEN
      SELECT DISTINCT top "x" START at "y" "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","isnull"("employment"."position","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",
        "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "CompanyAddress",
        "t"."theirref","person"."payrollnumber","ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",
        (IF "t"."estatus" = 3 THEN 1 ELSE 0 endif) AS "awaitsApproval"
        FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy","placement" 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 "weekenddate" BETWEEN "DateRangeStart" AND "DateRangeEnd" AND "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid")
        ORDER BY "weekenddate" DESC,"tempname" ASC,"t"."serialnumber" ASC
    ELSE IF "userClass" = 'OWNER' THEN
        SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
        SELECT DISTINCT top "x" START at "y" "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","isnull"("employment"."position","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",
          "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "CompanyAddress",
          "t"."theirref","person"."payrollnumber","ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",
          (IF "t"."estatus" = 3 THEN 1 ELSE 0 endif) AS "awaitsApproval"
          FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy","placement" KEY JOIN "employment" KEY JOIN "company"
            ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
            ,"temptimesheet" AS "t" KEY JOIN "person"
          WHERE "weekenddate" BETWEEN "DateRangeStart" AND "DateRangeEnd" AND "company"."divisionid" = "divid" AND "person"."divisionid" = "divid"
          ORDER BY "weekenddate" DESC,"t"."serialnumber" DESC
      ELSE -- CLIENT
        SELECT DISTINCT top "x" START at "y" "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","isnull"("employment"."position","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",
          "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "CompanyAddress","t"."theirref","person"."payrollnumber","ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",(IF "t"."estatus" = 3 THEN 1 ELSE 0 endif) AS "awaitsApproval"
          FROM "temptimesheet" AS "t"
            KEY JOIN "placement"
            KEY JOIN "vacancy"
            ,"placement"
            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 "weekenddate" BETWEEN "DateRangeStart" AND "DateRangeEnd" AND "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid")
          ORDER BY "weekenddate" DESC,"tempname" ASC,"t"."serialnumber" ASC
      END IF
    END IF
  END IF
END /* DOC
2017-11-15 PC IW-311 position is drawn from the employment then the vacancy
2018-02-06 PC add date parameters
2018-09-11 PC test routines and declare divid
2018-11-27 MHS: now respects imageblobid. 
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes.  Also updated order by.
 
*/
/* TEST
select * from NetTimesheets('test.candidate',0,10,null,null);
expect temptimesheetid=TEST,serialnumber=99;
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheets" IS 
{CREATE PROCEDURE pears."NetTimesheets"(IN pWebUserID CHAR(20),IN pSlice INTEGER DEFAULT 0,IN pSliceSize INTEGER DEFAULT 20, IN pStartDate CHAR(20) DEFAULT NULL,IN pEndDate CHAR(20) DEFAULT NULL)
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), payrollnumber CHAR(20), hasScannedImage SMALLINT, awaitsApproval SMALLINT)
// IQXWeb
BEGIN
  DECLARE userClass CHAR(20);
  DECLARE x INTEGER;
  DECLARE y INTEGER;
  DECLARE @companycount INTEGER;
  DECLARE DateRangeStart DATE;
  DECLARE DateRangeEnd DATE;
  DECLARE divid CHAR(20);
  SET x=pSliceSize;
  SET y=x*pSlice+1;
  SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  SET DateRangeStart=isnull(pStartDate,dateadd(yy,-10,now(*)));
  SET DateRangeEnd=isnull(pEndDate,dateadd(yy,1,now(*)));
  IF userClass = 'CANDIDATE' THEN
    SELECT top x START at y t.temptimesheetid,t.serialnumber,string(person.surname,', ',person.forenames) AS tempname,isnull(employment.position,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,
      NetGetVacancySiteDetails(vacancy.vacancyID) AS CompanyAddress,
      t.theirref, person.payrollnumber, ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage,
      (IF t.estatus=3 THEN 1 ELSE 0 endif) AS awaitsApproval
      FROM temptimesheet AS t KEY JOIN placement KEY JOIN vacancy,placement KEY JOIN employment KEY JOIN company
      ,temptimesheet AS t KEY JOIN tempdesk
      ,temptimesheet AS t KEY JOIN person KEY JOIN iqxnetuserlink
      WHERE weekenddate BETWEEN DateRangeStart AND DateRangeEnd AND iqxnetuserlink.iqxnetuserid = pwebuserid
      ORDER BY
        weekenddate DESC,companyname ASC,t.serialnumber ASC
  ELSE IF userClass = 'AGENCY' THEN
      SELECT DISTINCT top x START at y t.temptimesheetid,t.serialnumber,string(person.surname,', ',person.forenames) AS tempname,isnull(employment.position,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,
        NetGetVacancySiteDetails(vacancy.vacancyID) AS CompanyAddress,
        t.theirref, person.payrollnumber, ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage,
        (IF t.estatus=3 THEN 1 ELSE 0 endif) AS awaitsApproval
        FROM temptimesheet AS t KEY JOIN placement KEY JOIN vacancy, placement 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 weekenddate BETWEEN DateRangeStart AND DateRangeEnd AND iqxnetuserlink.iqxnetuserid = pwebuserid AND weekenddate >= IQXNetCompanyStartDate(agcomp.companyid)
        ORDER BY
            weekenddate DESC,tempname ASC,t.serialnumber ASC
    ELSE IF userClass = 'OWNER' THEN
        SET divid=(SELECT FIRST staff.divisionid FROM iqxnetuser KEY JOIN staff WHERE iqxnetuser.iqxnetuserid = pwebuserid);
        SELECT DISTINCT top x START at y t.temptimesheetid,t.serialnumber,string(person.surname,', ',person.forenames) AS tempname,isnull(employment.position,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,
          NetGetVacancySiteDetails(vacancy.vacancyID) AS CompanyAddress,
          t.theirref, person.payrollnumber, ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage,
          (IF t.estatus=3 THEN 1 ELSE 0 endif) AS awaitsApproval
          FROM temptimesheet AS t KEY JOIN placement KEY JOIN vacancy,placement KEY JOIN employment KEY JOIN company
          ,temptimesheet AS t KEY JOIN tempdesk
          ,temptimesheet AS t KEY JOIN person
          WHERE weekenddate BETWEEN DateRangeStart AND DateRangeEnd AND  company.divisionid = divid AND person.divisionid = divid ORDER BY
          weekenddate DESC,t.serialnumber DESC
    ELSE -- CLIENT
      SELECT DISTINCT top x START at y t.temptimesheetid,t.serialnumber,string(person.surname,', ',person.forenames) AS tempname,isnull(employment.position,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,
          NetGetVacancySiteDetails(vacancy.vacancyID) AS CompanyAddress,t.theirref, person.payrollnumber, ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage, (IF t.estatus=3 THEN 1 ELSE 0 endif) AS awaitsApproval
        FROM 
        temptimesheet AS t 
            KEY JOIN placement 
            KEY JOIN vacancy,
            placement 
            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 weekenddate BETWEEN DateRangeStart AND DateRangeEnd AND iqxnetuserlink.iqxnetuserid = pwebuserid AND weekenddate >= IQXNetCompanyStartDate(company.companyid)
        ORDER BY
            weekenddate DESC,tempname ASC,t.serialnumber ASC
      END IF
    END IF
  END IF
END
 
/* DOC
2017-11-15 PC IW-311 position is drawn from the employment then the vacancy
2018-02-06 PC add date parameters
2018-09-11 PC test routines and declare divid
2018-11-27 MHS: now respects imageblobid. 
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes.  Also updated order by.
 
*/
 
/* TEST
select * from NetTimesheets('test.candidate',0,10,null,null);
expect temptimesheetid=TEST,serialnumber=99;
 
*/
}