pears.NetCandProvTimesheets

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;
 
*/
}