pears.NetTimesheet

CREATE PROCEDURE "pears"."NetTimesheet"( IN "pWebUserID" CHAR(20),IN "pTempTimesheetID" CHAR(20) ) 
RESULT( "temptimesheetid" CHAR(20),"serialnumber" CHAR(20),"timesheettype" CHAR(1),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"weekenddate" DATE,"completedby" CHAR(50),"completedat" "datetime","companyaddress" CHAR(250),"payrollnumber" CHAR(20),"hasScannedImage" SMALLINT,"TSPeriod" INTEGER,"WeekEndingDate" CHAR(10),"OrderedBy" CHAR(250) ) 
// IQXWeb
BEGIN
  SELECT "t"."temptimesheetid","t"."serialnumber",
    IF "tempdesk"."desktype" = 'S' OR "tempdesk"."ShiftsOnContractTimesheet" = 1 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",
    "string"("person"."surname",', ',"person"."forenames") AS "tempname",
    "isnull"("employment"."position","vacancy"."position"),
    "company"."name" AS "companyname",
    "weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
    "isnull"((SELECT FIRST "i"."name" FROM "tempprovtimesheethistory" AS "h" JOIN "iqxnetuser" AS "i" ON "h"."externaluserid" = "i"."iqxnetuserid"
      WHERE "h"."temptimesheetid" = "t"."temptimesheetid" AND "h"."newstatus" = 100),(SELECT top 1 "s"."name" FROM "tempprovtimesheethistory" AS "h" LEFT OUTER JOIN "staff" AS "s" ON "h"."staffid" = "s"."staffid" WHERE "h"."temptimesheetid" = "t"."temptimesheetid" AND "h"."newstatus" = 100)) AS "completedby",
    "isnull"((SELECT FIRST "h"."whenentered" FROM "tempprovtimesheethistory" AS "h" JOIN "iqxnetuser" AS "i" ON "h"."externaluserid" = "i"."iqxnetuserid"
      WHERE "h"."temptimesheetid" = "t"."temptimesheetid" AND "h"."newstatus" = 100 ORDER BY "whenentered" DESC),(SELECT top 1 "h"."whenentered" FROM "tempprovtimesheethistory" AS "h" LEFT OUTER JOIN "staff" AS "s" ON "h"."staffid" = "s"."staffid" WHERE "h"."temptimesheetid" = "t"."temptimesheetid" AND "h"."newstatus" = 100 ORDER BY "whenentered" DESC),NULL) AS "completedat",
    "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "companyaddress","person"."payrollnumber",
    "ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",
    "period",
    "dateformat"("weekenddate"("round"("period"/100,0),"period"-("round"("period"/100,0))*100),'yyyy-mm-dd'),
    "isnull"((SELECT "list"(DISTINCT "orderedby") FROM "tempshiftplan" AS "pl" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid"),'N/R') AS "OrderedByList"
    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 "t"."temptimesheetid" = "ptemptimesheetid" AND "nethaspermission"("pWebUserID","person"."personid","company"."companyid") = 1
END /* DOC
2017-04-11 PC IW-204 Period added
2017-04-26 PC IW-204 week ending date added
2017-06-22 PC Test and doc
2017-10-23 PC ASA-12 RC2
2017-10-27 PC ASA-11 Person who ordered shift
2017-11-15 PC IW-311 position is drawn from the employment, then the vacancy
2018-04-30 PC modify to over-ride default work pattern where shifts are present
2018-05-29 MHS IW-553 Make it obey tempdesk.ShiftsOnContractTimesheet rather than the presence of shifts. Simplify tests and make pass.
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
*/
/* TEST
call NetTestSetup('');
insert into temptimesheet (temptimesheetid,placementid,period,periodlength,serialnumber,tempdeskid,personid) on existing update defaults off values ('TEST','TEST',weekcontaining(now(*)),'W',99,'test','TEST');
select serialnumber from NetTimesheet('test.candidate','TEST');
expect serialnumber=99;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheet" IS 
{CREATE PROCEDURE pears."NetTimesheet"(IN pWebUserID CHAR(20),IN pTempTimesheetID CHAR(20))
RESULT(temptimesheetid CHAR(20),serialnumber CHAR(20),timesheettype CHAR(1),tempname CHAR(60),POSITION CHAR(60),companyname CHAR(60),weekenddate DATE,completedby CHAR(50),completedat datetime, companyaddress CHAR(250), payrollnumber CHAR(20), hasScannedImage SMALLINT, TSPeriod INTEGER,WeekEndingDate CHAR(10),OrderedBy CHAR(250))
// IQXWeb
BEGIN
  SELECT t.temptimesheetid,t.serialnumber,
    IF tempdesk.desktype = 'S' OR tempdesk.ShiftsOnContractTimesheet = 1 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,
    string(person.surname,', ',person.forenames) AS tempname,
    isnull(employment.position,vacancy.position),
    company.name AS companyname,
    weekmonthenddate(t.period,t.periodlength) AS weekenddate,
    isnull((SELECT FIRST i.name FROM tempprovtimesheethistory AS h JOIN iqxnetuser AS i ON h.externaluserid = i.iqxnetuserid
      WHERE h.temptimesheetid = t.temptimesheetid AND h.newstatus = 100),(SELECT top 1 s.name FROM tempprovtimesheethistory h LEFT OUTER JOIN staff s ON h.staffid=s.staffid WHERE h.temptimesheetid = t.temptimesheetid AND h.newstatus = 100)) AS completedby,
    isnull((SELECT FIRST h.whenentered FROM tempprovtimesheethistory AS h JOIN iqxnetuser AS i ON h.externaluserid = i.iqxnetuserid
      WHERE h.temptimesheetid = t.temptimesheetid AND h.newstatus = 100 ORDER BY whenentered DESC),(SELECT top 1 h.whenentered FROM tempprovtimesheethistory h LEFT OUTER JOIN staff s ON h.staffid=s.staffid WHERE h.temptimesheetid = t.temptimesheetid AND h.newstatus = 100 ORDER BY whenentered DESC),NULL) AS completedat,
    NetGetVacancySiteDetails(vacancy.vacancyID) AS companyaddress, person.payrollnumber,
    ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage,
    period,
    dateformat(weekenddate(round(period/100,0),period-(round(period/100,0))*100),'yyyy-mm-dd'),
    isnull((SELECT list(DISTINCT orderedby) FROM tempshiftplan pl KEY JOIN tempshift KEY JOIN temptimesheet ts WHERE ts.temptimesheetid=t.temptimesheetid),'N/R') AS OrderedByList
    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 t.temptimesheetid = ptemptimesheetid AND nethaspermission(pWebUserID,person.personid,company.companyid)=1
END
 
/* DOC
2017-04-11 PC IW-204 Period added
2017-04-26 PC IW-204 week ending date added
2017-06-22 PC Test and doc
2017-10-23 PC ASA-12 RC2
2017-10-27 PC ASA-11 Person who ordered shift
2017-11-15 PC IW-311 position is drawn from the employment, then the vacancy
2018-04-30 PC modify to over-ride default work pattern where shifts are present
2018-05-29 MHS IW-553 Make it obey tempdesk.ShiftsOnContractTimesheet rather than the presence of shifts. Simplify tests and make pass.
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
*/
 
/* TEST
call NetTestSetup('');
insert into temptimesheet (temptimesheetid,placementid,period,periodlength,serialnumber,tempdeskid,personid) on existing update defaults off values ('TEST','TEST',weekcontaining(now(*)),'W',99,'test','TEST');
select serialnumber from NetTimesheet('test.candidate','TEST');
expect serialnumber=99;
*/
}