IQX Quick Menu

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetProvTimesheets"( IN "pWebUserID" CHAR(20),IN "pForAuthorise" SMALLINT DEFAULT 0,IN "pSlice" INTEGER DEFAULT 0,IN "pSliceSize" INTEGER DEFAULT 20,IN "pStartDate" CHAR(20) DEFAULT NULL,IN "pEndDate" CHAR(20) DEFAULT NULL ) 
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,"payrollnumber" CHAR(20),"timesheethours" DOUBLE,"timesheetamount" DOUBLE,"hasScannedImage" SMALLINT,"awaitsAuthorisation" SMALLINT,"canApprove" SMALLINT ) 
// IQXWeb
BEGIN
  DECLARE "userClass" CHAR(20);
  DECLARE "sRole" CHAR(20);
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  DECLARE "DateRangeStart" DATE;
  DECLARE "DateRangeEnd" DATE;
  SET "x" = "pSliceSize";
  SET "y" = "x"*"pSlice"+1;
  SET "DateRangeStart" = "isnull"("pStartDate","dateadd"("yy",-1,"now"()));
  SET "DateRangeEnd" = "isnull"("pEndDate","dateadd"("yy",1,"now"()));
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'CANDIDATE' THEN
    SELECT top "x" START at "y"
      "t"."tempprovtimesheetid",
      "t"."serialnumber",
      "string"("person"."surname",', ',"person"."forenames") AS "tempname",
      "isnull"((SELECT "position" FROM "placement" KEY JOIN "employment" WHERE "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",
      0 AS "theirrefrequired",
      "person"."payrollnumber",
      0 AS "timesheethours",
      0 AS "timesheetamount",
      "ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",
      (IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif) AS "awaitsAuthorisation",0 AS "canApprove"
      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" BETWEEN "DateRangeStart" AND "DateRangeEnd"
      ORDER BY "weekenddate" DESC,"companyname" ASC,"t"."serialnumber" ASC
  ELSE IF "userClass" = 'AGENCY' THEN
      SELECT top "x" START at "y"
        "t"."tempprovtimesheetid",
        "t"."serialnumber",
        "string"("person"."surname",', ',"person"."forenames") AS "tempname",
        "isnull"((SELECT "position" FROM "placement" KEY JOIN "employment" WHERE "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' 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",0 AS "theirrefrequired","person"."payrollnumber",0 AS "timesheethours",0 AS "timesheetamount",
        "ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",(IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif) AS "awaitsAuthorisation",0 AS "canApprove"
        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 "pay_employee"
          KEY JOIN "company" AS "agcomp"
          KEY JOIN "employment" AS "agemp"
          KEY JOIN "iqxnetuserlink"
        WHERE "iqxnetuserlink"."iqxnetuserid" = "pWebUserID" AND "t"."extnumber" > 0
        AND "weekenddate" BETWEEN "DateRangeStart" AND "DateRangeEnd"
        ORDER BY "weekenddate" DESC,"tempname" ASC,"t"."serialnumber" ASC
    ELSE IF "userClass" = 'CLIENT' THEN
        IF "pForAuthorise" = 1 THEN
          SET "sRole" = 'AUTHORISETIMESHEETS'
        ELSE
          SET "sRole" = 'FILLTIMESHEETS'
        END IF;
        SELECT top "x" START at "y"
          "t"."tempprovtimesheetid",
          "t"."serialnumber",
          "string"("person"."surname",', ',"person"."forenames") AS "tempname",
          "isnull"((SELECT "position" FROM "placement" KEY JOIN "employment" WHERE "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' 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",
          "companyaccount"."theirrefrequired","person"."payrollnumber",
          IF "pForAuthorise" = 1 THEN(SELECT "sum"("l"."unitscharged") FROM "tempprovtimesheetline" AS "l" KEY JOIN "temppayband" AS "b" WHERE "l"."tempprovtimesheetid" = "t"."tempprovtimesheetid" AND "b"."unit" LIKE 'hour%') ELSE 0 endif AS "timesheethours",
          IF "pForAuthorise" = 1 THEN(SELECT "sum"("l"."unitscharged"*"l"."chargerate") FROM "tempprovtimesheetline" AS "l" WHERE "l"."tempprovtimesheetid" = "t"."tempprovtimesheetid") ELSE 0 endif AS "timesheetamount",
          "ifnull"("nullif"("string"("t"."imageblobid","t"."ScanPathFileName"),''),0,1) AS "hasScannedImage",(IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif) AS "awaitsAuthorisation","NetVacancyRoleAllowed"("pWebUserID","vacancy"."vacancyid",'APPROVETIMESHEETS') AS "canApprove"
          FROM "tempprovtimesheet" AS "t"
            KEY JOIN "vacancy"
            KEY JOIN "employment"
            KEY JOIN "company"
            KEY JOIN "employment" AS "allemps"
            KEY JOIN "iqxnetuserlink"
            ,"tempprovtimesheet" AS "t"
            KEY JOIN "tempdesk"
            ,"tempprovtimesheet" AS "t"
            KEY JOIN "person"
            ,"company"
            KEY LEFT OUTER JOIN "companyaccount"
          WHERE "iqxnetuserlink"."iqxnetuserid" = "pWebUserID" AND "t"."extnumber" > 0
          AND "weekenddate" BETWEEN "DateRangeStart" AND "DateRangeEnd"
          AND "NetVacancyRoleAllowed"("pWebUserID","vacancy"."vacancyid","sRole") = 1
          AND("pForAuthorise" = 0 OR "t"."extnumber" = 2)
          ORDER BY "weekenddate" DESC,"tempname" ASC,"t"."serialnumber" ASC
      END IF
    END IF
  END IF
END /* DOC
2016-09-02 PC test and doc
2017-03-21 PC extend candidate data set from 100 days to 365 days
2017-11-15 PC IW=311 draw position from employment then vacancy
2017-11-17 LM deleted test data at the beginning and the end of the test routine.  Inserts were also added after the first set of deletes.
2018-07-26 PC modify test routines
2018-11-27 MHS: now respects imageblobid. 
2018-12-11 PC modify paging and default dates, alter tests
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes.  Also updated order by.
test:
invalid pWebUserID
valid pWebUserID, invalid pAuthorise as Candidate
valid pWebUserID, invalid pAuthorise as Client
valid pWebUserID, pAuthorise as Client
valid pWebUserID, invalid pAuthorise as Agency
 
*/
/* TEST
call NetTestSetup('');
 
update tempprovtimesheet set period=weekcontaining(dateadd(mm,-4,now(*))) where tempprovtimesheetid = 'TEST';
update tempprovtimesheet set period=weekcontaining(now(*)) where tempprovtimesheetid = 'TEST2';
update tempprovtimesheet set extnumber=1 where tempprovtimesheetid = 'test' or tempprovtimesheetid = 'test2';
update tempprovtimesheet set vacancyid='test' where tempprovtimesheetid = 'test' or tempprovtimesheetid = 'test2';
select * from NetProvTimesheets(null,null);
expect EOF;
update employment set position='test' where employmentid='TEST';
select * from NetProvTimesheets('test.candidate', 1) where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2, position=test;
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempprovtimesheet set extnumber=2 where tempprovtimesheetid = 'test' or tempprovtimesheetid = 'test2';
select * from NetProvTimesheets('test.client',null) where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2;
insert into vacancyrole (vacancyroleid,externalcode,description) on existing update defaults off values ('TEST','~','AUTHORISETIMESHEETS');
insert into vacancyroleallocation (vacancyid,employmentid,vacancyroleid) on existing update defaults off values ('TEST','TEST','TEST');
select * from NetProvTimesheets('test.client',null) where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2;
select * from NetProvTimesheets('test.client','1') where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2;
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
insert into pay_employee (personid,secondaryagencyid)on existing update defaults off values ('TEST','TEST');
select * from NetProvTimesheets('test.agency',null) where tempprovtimesheetid like 'TEST%';
expect position=test;
update employment set position='TestPositionEdit' where employmentid='TEST';
select position from employment where employmentid = 'TEST';
 
expect position=TestPositionEdit;
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
select * from NetProvTimesheets( 'test.client',null ) where tempprovtimesheetid like 'TEST%';
expect position=TestPositionEdit;
update employment set position='TestPosition' where employmentid='TEST';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheets" IS 
{CREATE PROCEDURE pears."NetProvTimesheets"(IN pWebUserID CHAR(20), pForAuthorise SMALLINT DEFAULT 0,IN pSlice INTEGER DEFAULT 0,IN pSliceSize INTEGER DEFAULT 20, IN pStartDate CHAR(20) DEFAULT NULL,IN pEndDate CHAR(20) DEFAULT NULL)
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, payrollnumber CHAR(20),timesheethours DOUBLE,timesheetamount DOUBLE,hasScannedImage SMALLINT, awaitsAuthorisation SMALLINT, canApprove SMALLINT )
// IQXWeb
BEGIN
  DECLARE userClass CHAR(20);
  DECLARE sRole CHAR(20);
  DECLARE x INTEGER;
  DECLARE y INTEGER;
  DECLARE DateRangeStart DATE;
  DECLARE DateRangeEnd DATE;
  SET x=pSliceSize;
  SET y=x*pSlice+1;
  SET DateRangeStart=isnull(pStartDate,dateadd(yy,-1,now(*)));
  SET DateRangeEnd=isnull(pEndDate,dateadd(yy,1,now(*)));
  SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  IF userClass = 'CANDIDATE' THEN
    SELECT top x START at y 
        t.tempprovtimesheetid,
        t.serialnumber,
        string(person.surname,', ',person.forenames) AS tempname,
        isnull((SELECT POSITION FROM placement KEY JOIN employment WHERE 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, 
        0 AS theirrefrequired, 
        person.payrollnumber, 
        0 AS timesheethours, 
        0 AS timesheetamount,
        ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage, 
        (IF t.extnumber=2 THEN 1 ELSE 0 endif) AS awaitsAuthorisation, 0 AS canApprove
    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 BETWEEN DateRangeStart AND DateRangeEnd
    ORDER BY
        weekenddate DESC,companyname ASC,t.serialnumber ASC
  ELSE IF userClass = 'AGENCY' THEN
    SELECT top x START at y 
    t.tempprovtimesheetid,
    t.serialnumber,
    string(person.surname,', ',person.forenames) AS tempname,
    isnull((SELECT POSITION FROM placement KEY JOIN employment WHERE 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' 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, 0 AS theirrefrequired, person.payrollnumber, 0 AS timesheethours, 0 AS timesheetamount,
    ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage, (IF t.extnumber=2 THEN 1 ELSE 0 endif) AS awaitsAuthorisation, 0 AS canApprove    
    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 pay_employee 
            KEY JOIN company AS agcomp 
            KEY JOIN employment AS agemp 
            KEY JOIN iqxnetuserlink
    WHERE iqxnetuserlink.iqxnetuserid = pWebUserID AND t.extnumber > 0
    AND weekenddate BETWEEN DateRangeStart AND DateRangeEnd
    ORDER BY
        weekenddate DESC,tempname ASC,t.serialnumber ASC
  ELSE IF userClass = 'CLIENT' THEN   
    IF pForAuthorise=1 THEN
      SET sRole='AUTHORISETIMESHEETS'
    ELSE
      SET sRole='FILLTIMESHEETS'
    END IF;
    SELECT top x START at y 
    t.tempprovtimesheetid,
    t.serialnumber,
    string(person.surname,', ',person.forenames) AS tempname,
    isnull((SELECT POSITION FROM placement KEY JOIN employment WHERE 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' 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,
    companyaccount.theirrefrequired, person.payrollnumber,
    IF pForAuthorise=1 THEN (SELECT SUM(l.unitscharged) FROM tempprovtimesheetline l KEY JOIN temppayband b WHERE l.tempprovtimesheetid = t.tempprovtimesheetid AND b.unit LIKE 'hour%') ELSE 0 endif AS timesheethours,
    IF pForAuthorise=1 THEN (SELECT SUM(l.unitscharged * l.chargerate) FROM tempprovtimesheetline l WHERE l.tempprovtimesheetid = t.tempprovtimesheetid) ELSE 0 endif AS timesheetamount,
    ifnull(NULLIF(string(t.imageblobid,t.ScanPathFileName),''),0,1) AS hasScannedImage, (IF t.extnumber=2 THEN 1 ELSE 0 endif) AS awaitsAuthorisation, NetVacancyRoleAllowed(pWebUserID,vacancy.vacancyid,'APPROVETIMESHEETS') AS canApprove 
    FROM 
        tempprovtimesheet AS t 
            KEY JOIN vacancy 
            KEY JOIN employment 
            KEY JOIN company 
            KEY JOIN employment AS allemps 
            KEY JOIN iqxnetuserlink,
        tempprovtimesheet AS t 
            KEY JOIN tempdesk,
        tempprovtimesheet AS t 
            KEY JOIN person,
        company 
            KEY LEFT OUTER JOIN companyaccount
    WHERE iqxnetuserlink.iqxnetuserid = pWebUserID AND t.extnumber > 0
    AND weekenddate BETWEEN DateRangeStart AND DateRangeEnd
    AND NetVacancyRoleAllowed(pWebUserID,vacancy.vacancyid,sRole)=1
    AND (pForAuthorise=0 OR t.extnumber=2)
    ORDER BY
        weekenddate DESC,tempname ASC,t.serialnumber ASC
    END IF
    END IF
  END IF
END
 
/* DOC
2016-09-02 PC test and doc
2017-03-21 PC extend candidate data set from 100 days to 365 days
2017-11-15 PC IW=311 draw position from employment then vacancy
2017-11-17 LM deleted test data at the beginning and the end of the test routine.  Inserts were also added after the first set of deletes.
2018-07-26 PC modify test routines
2018-11-27 MHS: now respects imageblobid. 
2018-12-11 PC modify paging and default dates, alter tests
2019-06-12 ET IW-890 now using new function NetGetVacancySiteDetails to get the site address for all user classes.  Also updated order by.
test:
    invalid pWebUserID
    valid pWebUserID, invalid pAuthorise as Candidate
    valid pWebUserID, invalid pAuthorise as Client
    valid pWebUserID, pAuthorise as Client
    valid pWebUserID, invalid pAuthorise as Agency
 
*/
 
/* TEST
call NetTestSetup('');
 
update tempprovtimesheet set period=weekcontaining(dateadd(mm,-4,now(*))) where tempprovtimesheetid = 'TEST';
update tempprovtimesheet set period=weekcontaining(now(*)) where tempprovtimesheetid = 'TEST2';
update tempprovtimesheet set extnumber=1 where tempprovtimesheetid = 'test' or tempprovtimesheetid = 'test2';
update tempprovtimesheet set vacancyid='test' where tempprovtimesheetid = 'test' or tempprovtimesheetid = 'test2';
select * from NetProvTimesheets(null,null);
expect EOF;
update employment set position='test' where employmentid='TEST';
select * from NetProvTimesheets('test.candidate', 1) where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2, position=test;
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempprovtimesheet set extnumber=2 where tempprovtimesheetid = 'test' or tempprovtimesheetid = 'test2';
select * from NetProvTimesheets('test.client',null) where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2;
insert into vacancyrole (vacancyroleid,externalcode,description) on existing update defaults off values ('TEST','~','AUTHORISETIMESHEETS');
insert into vacancyroleallocation (vacancyid,employmentid,vacancyroleid) on existing update defaults off values ('TEST','TEST','TEST');
select * from NetProvTimesheets('test.client',null) where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2;
select * from NetProvTimesheets('test.client','1') where tempprovtimesheetid like 'TEST%';
expect tempprovtimesheetid=TEST2;
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
insert into pay_employee (personid,secondaryagencyid)on existing update defaults off values ('TEST','TEST');
select * from NetProvTimesheets('test.agency',null) where tempprovtimesheetid like 'TEST%';
expect position=test;
update employment set position='TestPositionEdit' where employmentid='TEST';
select position from employment where employmentid = 'TEST';
 
expect position=TestPositionEdit;
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
select * from NetProvTimesheets( 'test.client',null ) where tempprovtimesheetid like 'TEST%';
expect position=TestPositionEdit;
update employment set position='TestPosition' where employmentid='TEST';
 
*/
}
pears.NetProvTimesheets

Original SQL

iqx
