pears.NetCandProvTimesheets [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetCandProvTimesheets ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetCandProvTimesheets"( in "pWebUserID" char(20) ) 
result( "tempprovtimesheetid" char(20),"serialnumber" char(20),"tempname" char(60),"position" char(60),"companyname" char(60),"weekenddate" date,"timesheettype" char(1),"completed" smallint,"placementid" char(20) ) 
// IQXWeb
begin
  select "t"."tempprovtimesheetid",
    "t"."serialnumber",
    "string"("person"."surname",', ',"person"."forenames") as "tempname",
    "vacancy"."position",
    "company"."name" as "companyname",
    "NetTimesheetEndDate"(1,1,"t"."tempprovtimesheetid") as "weekenddate",
    if "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 then 'C'
    else 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
    endif as "timesheettype",
    if "t"."extnumber" = 2 then 1 else 0 endif as "completed",
    "t"."placementid"
    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"
      key join "iqxnetuserlink"
    where "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"
    and "t"."extnumber" > 0
    and "weekenddate" > current date-80
    order by "weekenddate" asc,
    "t"."serialnumber" asc,
    "companyname" asc
end /* DOC
2016-12-05 PC test and doc
2017-10-23 PC add placementid for popup
2018-07-24 PC test routines

Tests:
invalid pWebUserID
valid pWebUserID extno=0
valid pWebUserID extno=2
valid pWebUserID date < 80
valid pWebUserID, date 

*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
select * from NetCandProvTimesheets(null);
expect EOF;
select * from NetCandProvTimesheets('test.candidate');
expect position=test,companyname=test,completed=1;
update tempprovtimesheet set extnumber=2 where tempprovtimesheetid like 'TEST%';
update tempprovtimesheet set period=weekcontaining(dateadd(mm,-3,now(*))) where tempprovtimesheetid = 'TEST';
update tempprovtimesheet set period=weekcontaining(now(*)) where tempprovtimesheetid = 'TEST2';
select * from NetCandProvTimesheets('test.candidate');
expect position=test,companyname=test;

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandProvTimesheets" IS 
{create PROCEDURE pears."NetCandProvTimesheets"(in pWebUserID char(20))
result(tempprovtimesheetid char(20),serialnumber char(20),tempname char(60),position char(60),companyname char(60),weekenddate date,timesheettype char(1),completed smallint,"placementid" char(20))
// IQXWeb
begin
  select 
    t.tempprovtimesheetid,
    t.serialnumber,
    string(person.surname,', ',person.forenames) as tempname,
    vacancy.position,
    company.name as companyname,
    NetTimesheetEndDate(1,1,t.tempprovtimesheetid) as weekenddate,
    if NetVacancyHasRateScript(t.tempjobtypeid,t.vacancyid) = 0 then 'C'
    else 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
    endif as timesheettype,if t.extnumber = 2 then 1 else 0
    endif as completed,
    t.placementid 
  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 
        key join iqxnetuserlink
  where 
    iqxnetuserlink.iqxnetuserid = pWebUserID 
    and t.extnumber > 0
    and weekenddate > current date-80 
  order by
    weekenddate asc,
    t.serialnumber asc,
    companyname asc
end

/* DOC
2016-12-05 PC test and doc
2017-10-23 PC add placementid for popup
2018-07-24 PC test routines

Tests:
    invalid pWebUserID
    valid pWebUserID extno=0
    valid pWebUserID extno=2
    valid pWebUserID date < 80
    valid pWebUserID, date 

*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
select * from NetCandProvTimesheets(null);
expect EOF;
select * from NetCandProvTimesheets('test.candidate');
expect position=test,companyname=test,completed=1;
update tempprovtimesheet set extnumber=2 where tempprovtimesheetid like 'TEST%';
update tempprovtimesheet set period=weekcontaining(dateadd(mm,-3,now(*))) where tempprovtimesheetid = 'TEST';
update tempprovtimesheet set period=weekcontaining(now(*)) where tempprovtimesheetid = 'TEST2';
select * from NetCandProvTimesheets('test.candidate');
expect position=test,companyname=test;

*/
}
</code>