pears.NetTimesheetsForApproval

CREATE PROCEDURE "pears"."NetTimesheetsForApproval"( 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,"timesheethours" DOUBLE,"companyaddress" CHAR(250),"theirref" CHAR(50),
  "payrollnumber" CHAR(20),"hasScannedImage" SMALLINT ) 
// IQXWeb
BEGIN
  DECLARE "userClass" CHAR(20);
  DECLARE "divid" CHAR(20);
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  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",
      (SELECT "sum"("l"."unitspaid") FROM "temptimesheetline" AS "l" KEY JOIN "temppayband" AS "b" WHERE "l"."temptimesheetid" = "t"."temptimesheetid" AND "b"."unit" LIKE 'hour%') AS "timesheethours",
      "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "CompanyAddress",
      "t"."theirref","person"."payrollnumber","ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage"
      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 "t"."estatus" = 3 AND "company"."divisionid" = "divid" AND "person"."divisionid" = "divid"
      ORDER BY "t"."serialnumber" DESC
  ELSE IF "userClass" = 'CLIENT' 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"("unitscharged"*"chargerate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",
        (SELECT "sum"("l"."unitscharged") FROM "temptimesheetline" AS "l" KEY JOIN "temppayband" AS "b" WHERE "l"."temptimesheetid" = "t"."temptimesheetid" AND "b"."unit" LIKE 'hour%') AS "timesheethours",
        "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "CompanyAddress","t"."theirref","person"."payrollnumber","ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage"
        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 "t"."estatus" = 3 AND "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid")
        AND "NetVacancyRoleAllowed"("pWebUserID","vacancy"."vacancyid",'APPROVETIMESHEETS') = 1
        ORDER BY "weekenddate" DESC,"tempname" ASC,"t"."serialnumber" ASC
    END IF
  END IF
END /* TEST
update temptimesheet set estatus=3 where temptimesheetid='test';
select * from NetTimesheetsForApproval('test.owner');
expect temptimesheetid=TEST,serialnumber=99;
select * from NetTimesheetsForApproval('test.client');
expect temptimesheetid=TEST,serialnumber=99;
update temptimesheet set estatus=0 where temptimesheetid='test';
*/
/* DOC
2018-09-12 PC test routines
2018-11-27 MHS: now respects imageblobid. Made tests pass.
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes.  Also updated order by
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheetsForApproval" IS 
{CREATE PROCEDURE pears."NetTimesheetsForApproval"(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,timesheethours DOUBLE,companyaddress CHAR(250),theirref CHAR(50), 
payrollnumber CHAR(20),hasScannedImage SMALLINT )
// IQXWeb
BEGIN
  DECLARE userClass CHAR(20);
  DECLARE divid CHAR(20);
  SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  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,
        (SELECT SUM(l.unitspaid) FROM temptimesheetline l KEY JOIN temppayband b WHERE l.temptimesheetid = t.temptimesheetid AND b.unit LIKE 'hour%') AS timesheethours,
        NetGetVacancySiteDetails(vacancy.vacancyID) AS CompanyAddress,
        t.theirref, person.payrollnumber, ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage
        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 t.estatus=3 AND company.divisionid = divid AND person.divisionid = divid ORDER BY
        t.serialnumber DESC
  ELSE IF userClass = 'CLIENT' 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(unitscharged*chargerate) FROM temptimesheetline WHERE temptimesheetid = t.temptimesheetid) AS timesheettotal,
        (SELECT SUM(l.unitscharged) FROM temptimesheetline l KEY JOIN temppayband b WHERE l.temptimesheetid = t.temptimesheetid AND b.unit LIKE 'hour%') AS timesheethours,
        NetGetVacancySiteDetails(vacancy.vacancyID) AS CompanyAddress,t.theirref, person.payrollnumber, ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage
        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 t.estatus=3 AND iqxnetuserlink.iqxnetuserid = pwebuserid AND weekenddate >= IQXNetCompanyStartDate(company.companyid)
        AND NetVacancyRoleAllowed(pWebUserID,vacancy.vacancyid,'APPROVETIMESHEETS') = 1
        ORDER BY
            weekenddate DESC,tempname ASC,t.serialnumber ASC
  END IF
  END IF
 END
 
/* TEST
update temptimesheet set estatus=3 where temptimesheetid='test';
select * from NetTimesheetsForApproval('test.owner');
expect temptimesheetid=TEST,serialnumber=99;
select * from NetTimesheetsForApproval('test.client');
expect temptimesheetid=TEST,serialnumber=99;
update temptimesheet set estatus=0 where temptimesheetid='test';
*/
 
/* DOC
2018-09-12 PC test routines
2018-11-27 MHS: now respects imageblobid. Made tests pass.
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes.  Also updated order by
 
*/
}