pears.NetProvTimesheet

CREATE PROCEDURE "pears"."NetProvTimesheet"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20) ) 
RESULT( "tempprovtimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"companyaddress" CHAR(120),"weekenddate" DATE,"timesheettype" CHAR(1),"completed" SMALLINT,"theirref" CHAR(100),"theirrefrequired" SMALLINT,"completedby" CHAR(50),"completedat" "datetime","payrollnumber" CHAR(20),"allowedToAuthorise" SMALLINT,"allowedToApprove" SMALLINT,"description" long VARCHAR,"hasScannedImage" SMALLINT,"ShiftsOnContractTimesheet" SMALLINT,"TSPeriod" INTEGER,"WeekEndingDate" CHAR(10),"OrderedBy" CHAR(255) ) 
// IQXWeb
BEGIN
  DECLARE "userClass" CHAR(20);
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  SELECT "t"."tempprovtimesheetid",
    "t"."serialnumber",
    "string"("person"."surname",', ',"person"."forenames") AS "tempname",
    "isnull"((SELECT "position" FROM "placement" KEY JOIN "employment" WHERE "placement"."placementid" = "t"."placementid"),"vacancy"."position"),
    "company"."name" AS "companyname",
    "NetGetVacancySiteDetails"("vacancy"."vacancyID") AS "companyaddress",
    "NetTimesheetEndDate"(1,1,"t"."tempprovtimesheetid") AS "weekenddate",
    IF "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN
      'C'
    ELSE
      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
    endif AS "timesheettype",
    IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif AS "completed",
    "isnull"("t"."theirref","GetPlacementTheirRef"("person"."personid","vacancy"."vacancyid")) AS "theirref",
    IF "userClass" = 'CLIENT' THEN "companyaccount"."theirrefrequired" ELSE 0 endif,
    "isnull"((SELECT FIRST "i"."name" FROM "tempprovtimesheethistory" AS "h" JOIN "iqxnetuser" AS "i" ON "h"."externaluserid" = "i"."iqxnetuserid"
      WHERE "h"."tempprovtimesheetid" = "t"."tempprovtimesheetid" AND "h"."newstatus" = 2),(SELECT top 1 "s"."name" FROM "tempprovtimesheethistory" AS "h" LEFT OUTER JOIN "staff" AS "s" ON "h"."staffid" = "s"."staffid" WHERE "h"."tempprovtimesheetid" = "t"."tempprovtimesheetid" AND "h"."newstatus" = 2)) AS "completedby",
    "isnull"((SELECT FIRST "h"."whenentered" FROM "tempprovtimesheethistory" AS "h" JOIN "iqxnetuser" AS "i" ON "h"."externaluserid" = "i"."iqxnetuserid"
      WHERE "h"."tempprovtimesheetid" = "t"."tempprovtimesheetid" AND "h"."newstatus" = 2 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"."tempprovtimesheetid" = "t"."tempprovtimesheetid" AND "h"."newstatus" = 2 ORDER BY "whenentered" DESC),NULL) AS "completedat",
    "person"."payrollnumber",
    IF "userClass" = 'CLIENT' THEN "NetVacancyRoleAllowed"("pWebUserID","vacancy"."vacancyid",'AUTHORISETIMESHEETS') ELSE 0 endif AS "allowedToAuthorise",
    IF "userClass" = 'CLIENT' THEN "NetVacancyRoleAllowed"("pWebUserID","vacancy"."vacancyid",'APPROVETIMESHEETS') ELSE 0 endif AS "allowedToApprove",
    "t"."description",
    "ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",
    "tempdesk"."ShiftsOnContractTimesheet","t"."period",
    "dateformat"("weekenddate"("round"("t"."period"/100,0),"t"."period"-("round"("t"."period"/100,0))*100),'yyyy-mm-dd'),
    "isnull"((SELECT "list"(DISTINCT "orderedby") FROM "tempshiftplan" AS "pl" KEY JOIN "tempshift" KEY JOIN "tempprovtimesheetline" AS "ts" WHERE "ts"."tempprovtimesheetid" = "t"."tempprovtimesheetid"),'N/R') AS "OrderedByList"
    FROM "tempprovtimesheet" AS "t"
      KEY JOIN "vacancy"
      KEY JOIN "employment"
      KEY JOIN "company"
      ,"tempprovtimesheet" AS "t"
      KEY JOIN "tempdesk"
      ,"tempprovtimesheet" AS "t"
      KEY JOIN "person"
      ,"company"
      KEY LEFT OUTER JOIN "companyaccount"
    WHERE "t"."tempprovtimesheetid" = "ptempprovtimesheetid"
    AND "nethaspermission"("pWebUserID","person"."personid","company"."companyid") = 1
    AND "t"."extnumber" > 0
END /* DOC
2016-08-31 PC doc and test
2017-04-11 PC IW-204 period added
2017-04-26 PC IW-204 Week ending date added
2017-10-23 PC ASA-12 RC2
2017-11-09 LM EOF test part works
2017-11-09 LM all test stages work
2017-11-15 PC IW-311 draw position from employment then vacancy
2017-11-17 LM made changes to the procedure such that there are deletes at the beginning and end of the procedure, and an insert after the first EOF.
2018-11-27 MHS: now respects imageblobid. Made tests pass (really need a rewrite).
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes
 
tests:
extnumber=0
valid timesheetid for Candidate where timesheettype C
valid timesheetid for Candidate where timesheettype S
valid timesheetid for Candidate where timesheettype C (other C)
valid timesheetid for Candidate where timesheettype T
valid timesheetid for Client without authorise or approve rights
valid timesheetid for Client with authorise and approve rights
*/
/* TEST
call NetTestSetup('');
update tempdesk set desktype='W',timesheetperiod='W' where tempdeskid='TEST';
update tempprovtimesheet set extnumber=1 where tempprovtimesheetid='test';
select tempprovtimesheetid = 'TEST';
update vacancy set tempjobtypeid='TEST' where vacancyid='TEST';
update vacancy set position='test' where vacancyid='test';
update employment set position = 'test' where employmentid = 'test';
update placement set worknormalhours=null where placementid='TEST';
select * from NetProvTimesheet( 'test.candidate','test' );
expect position=test, timesheettype=C;
update tempdesk set desktype='S' where tempdeskid='TEST';
insert into tempratescript(tempratescriptid, description) on existing update defaults off values('test', 'test');
update tempjobtype set tempratescriptid = 'test' where tempjobtypeid = 'test';
 
select * from NetProvTimesheet( 'test.candidate','test' );
expect position=test,timesheettype=S;
update tempdesk set desktype='W' where tempdeskid='TEST';
select * from NetProvTimesheet( 'test.candidate','test' );
expect position=test,timesheettype=C;
update placement set worknormalhours=5 where placementid='TEST';
 
insert into vacancyrole (vacancyroleid,description,externalcode) on existing update defaults off values('TEST','test','authorisetimesheets');
insert into vacancyroleallocation on existing update defaults off values ('TEST','TEST','TEST');
select * from NetProvTimesheet( 'test.client','test' );
expect position=test,timesheettype=T,allowedtoauthorise=1,allowedtoapprove=1;
update tempdesk set desktype=null,timesheetperiod=null where tempdeskid='TEST';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheet" IS 
{CREATE PROCEDURE pears."NetProvTimesheet"( IN pWebUserID CHAR(20),IN pTempProvTimesheetID CHAR(20) ) 
RESULT( tempprovtimesheetid CHAR(20),serialnumber CHAR(20),tempname CHAR(60),POSITION CHAR(60),companyname CHAR(60),companyaddress CHAR(120),weekenddate DATE,timesheettype CHAR(1),completed SMALLINT,theirref CHAR(100),theirrefrequired SMALLINT,completedby CHAR(50),completedat datetime, payrollnumber CHAR(20), allowedToAuthorise SMALLINT, allowedToApprove SMALLINT, description long VARCHAR, hasScannedImage SMALLINT, ShiftsOnContractTimesheet SMALLINT, TSPeriod INTEGER, WeekEndingDate CHAR(10),OrderedBy CHAR(255) ) 
// IQXWeb
BEGIN
  DECLARE userClass CHAR(20);
  SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  SELECT 
    t.tempprovtimesheetid,
    t.serialnumber,
    string(person.surname,', ',person.forenames) AS tempname,
    isnull((SELECT POSITION FROM placement KEY JOIN employment WHERE placement.placementid = t.placementid) ,vacancy.position),
    company.name AS companyname,
    NetGetVacancySiteDetails(vacancy.vacancyID) AS companyaddress,
    NetTimesheetEndDate(1,1,t.tempprovtimesheetid) AS weekenddate,
    IF NetVacancyHasRateScript(t.tempjobtypeid,t.vacancyid) = 0 THEN 
       'C'
    ELSE 
        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
    endif AS timesheettype,
    IF t.extnumber = 2 THEN 1 ELSE 0 endif AS completed,
    isnull(t.theirref,GetPlacementTheirRef(person.personid,vacancy.vacancyid)) AS theirref,
    IF userClass='CLIENT' THEN companyaccount.theirrefrequired ELSE 0 endif,
    isnull((SELECT FIRST i.name FROM tempprovtimesheethistory AS h JOIN iqxnetuser AS i ON h.externaluserid = i.iqxnetuserid
      WHERE h.tempprovtimesheetid = t.tempprovtimesheetid AND h.newstatus = 2),(SELECT top 1 s.name FROM tempprovtimesheethistory h LEFT OUTER JOIN staff s ON h.staffid=s.staffid WHERE h.tempprovtimesheetid = t.tempprovtimesheetid AND h.newstatus = 2)) AS completedby,
    isnull((SELECT FIRST h.whenentered FROM tempprovtimesheethistory AS h JOIN iqxnetuser AS i ON h.externaluserid = i.iqxnetuserid
      WHERE h.tempprovtimesheetid = t.tempprovtimesheetid AND h.newstatus = 2 ORDER BY whenentered DESC),(SELECT top 1 h.whenentered FROM tempprovtimesheethistory h LEFT OUTER JOIN staff s ON h.staffid=s.staffid WHERE h.tempprovtimesheetid = t.tempprovtimesheetid AND h.newstatus = 2 ORDER BY whenentered DESC),NULL) AS completedat,
    person.payrollnumber,
    IF userClass='CLIENT' THEN NetVacancyRoleAllowed(pWebUserID,vacancy.vacancyid,'AUTHORISETIMESHEETS') ELSE 0 endif AS allowedToAuthorise,
    IF userClass='CLIENT' THEN NetVacancyRoleAllowed(pWebUserID,vacancy.vacancyid,'APPROVETIMESHEETS') ELSE 0 endif AS allowedToApprove,
    t.description,
    ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage,
    tempdesk.ShiftsOnContractTimesheet,t.period,
    dateformat(weekenddate(round(t.period/100,0),t.period-(round(t.period/100,0))*100),'yyyy-mm-dd'),
    isnull((SELECT list(DISTINCT orderedby) FROM tempshiftplan pl KEY JOIN tempshift KEY JOIN tempprovtimesheetline ts WHERE ts.tempprovtimesheetid=t.tempprovtimesheetid),'N/R') AS OrderedByList
  FROM 
    tempprovtimesheet AS t 
        KEY JOIN vacancy 
        KEY JOIN employment 
        KEY JOIN company,
    tempprovtimesheet AS t 
        KEY JOIN tempdesk,
    tempprovtimesheet AS t 
        KEY JOIN person,
    company 
        KEY LEFT OUTER JOIN companyaccount
  WHERE 
    t.tempprovtimesheetid = ptempprovtimesheetid 
    AND nethaspermission(pWebUserID,person.personid,company.companyid) = 1 
    AND t.extnumber > 0
END
 
/* DOC
2016-08-31 PC doc and test
2017-04-11 PC IW-204 period added
2017-04-26 PC IW-204 Week ending date added
2017-10-23 PC ASA-12 RC2
2017-11-09 LM EOF test part works
2017-11-09 LM all test stages work
2017-11-15 PC IW-311 draw position from employment then vacancy
2017-11-17 LM made changes to the procedure such that there are deletes at the beginning and end of the procedure, and an insert after the first EOF.
2018-11-27 MHS: now respects imageblobid. Made tests pass (really need a rewrite).
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes
 
tests:
    extnumber=0
    valid timesheetid for Candidate where timesheettype C
    valid timesheetid for Candidate where timesheettype S
    valid timesheetid for Candidate where timesheettype C (other C)
    valid timesheetid for Candidate where timesheettype T
    valid timesheetid for Client without authorise or approve rights
    valid timesheetid for Client with authorise and approve rights
*/
/* TEST
call NetTestSetup('');
update tempdesk set desktype='W',timesheetperiod='W' where tempdeskid='TEST';
update tempprovtimesheet set extnumber=1 where tempprovtimesheetid='test';
select tempprovtimesheetid = 'TEST';
update vacancy set tempjobtypeid='TEST' where vacancyid='TEST';
update vacancy set position='test' where vacancyid='test';
update employment set position = 'test' where employmentid = 'test';
update placement set worknormalhours=null where placementid='TEST';
select * from NetProvTimesheet( 'test.candidate','test' );
expect position=test, timesheettype=C;
update tempdesk set desktype='S' where tempdeskid='TEST';
insert into tempratescript(tempratescriptid, description) on existing update defaults off values('test', 'test');
update tempjobtype set tempratescriptid = 'test' where tempjobtypeid = 'test';
 
select * from NetProvTimesheet( 'test.candidate','test' );
expect position=test,timesheettype=S;
update tempdesk set desktype='W' where tempdeskid='TEST';
select * from NetProvTimesheet( 'test.candidate','test' );
expect position=test,timesheettype=C;
update placement set worknormalhours=5 where placementid='TEST';
 
insert into vacancyrole (vacancyroleid,description,externalcode) on existing update defaults off values('TEST','test','authorisetimesheets');
insert into vacancyroleallocation on existing update defaults off values ('TEST','TEST','TEST');
select * from NetProvTimesheet( 'test.client','test' );
expect position=test,timesheettype=T,allowedtoauthorise=1,allowedtoapprove=1;
update tempdesk set desktype=null,timesheetperiod=null where tempdeskid='TEST';
 
*/
}