====== pears.NetProvTimesheet ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
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';
*/
}