pears.NetProvTimesheetShiftsSelect

CREATE PROCEDURE "pears"."NetProvTimesheetShiftsSelect"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20),IN "pShiftList" long VARCHAR ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "i" SMALLINT;
  DECLARE "dummy" SMALLINT;
  DECLARE "s" CHAR(30);
  DECLARE "userClass" CHAR(20);
  DECLARE "divid" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'OWNER' THEN
    INSERT INTO "IDs"( "ID" ) 
      SELECT DISTINCT "s"."divisionid" FROM "iqxnetuser" AS "i" KEY JOIN "staff" AS "s" WHERE "i"."iqxnetuserid" = "pwebuserid";
    IF NOT "ptempprovtimesheetid" = any(SELECT "t"."tempprovtimesheetid" FROM "tempprovtimesheet" AS "t" KEY JOIN "person" AS "p" JOIN "IDs" ON "p"."divisionid" = "IDs"."ID") THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  ELSE IF NOT "pTempProvTimesheetID" = any(
      SELECT "t"."TempProvTimesheetID" FROM "tempprovtimesheet" AS "t" KEY JOIN "person" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid" UNION
      SELECT "t"."TempProvTimesheetID" FROM "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" UNION
      SELECT "t"."TempProvTimesheetID" FROM "tempprovtimesheet" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" AS "vacemp" KEY JOIN "company" KEY JOIN "employment" KEY JOIN "iqxnetuserlink"
        WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid") THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF END IF;
  DELETE FROM "tempprovtimesheetshift" WHERE "tempprovtimesheetid" = "pTempprovtimesheetid";
  SET "pShiftList" = "replace"("pShiftList",'Shift_','');
  "looplabel": loop
    SET "i" = "charindex"(',',"pShiftList");
    IF "i" = 0 THEN
      SET "s" = "pshiftlist"
    ELSE
      SET "s" = "left"("pshiftlist","i"-1);
      SET "pshiftlist" = "right"("pshiftlist","length"("pshiftlist")-"i")
    END IF;
    SET "s" = "trim"("s");
    IF "s" <> '' THEN
      BEGIN
        INSERT INTO "tempprovtimesheetshift"( "tempprovtimesheetid","tempshiftid" ) VALUES( "ptempprovtimesheetid","s" ) 
      exception
        WHEN others THEN
          SET "dummy" = 0
      END
    ELSE leave "looplabel"
    END IF;
    IF "i" = 0 THEN
      leave "looplabel"
    END IF
  END loop "looplabel";
  SELECT '0:~Success'
END /* DOC
2017-02-14 PC test and doc sections added
2018-07-26 PC include improved error handler also small change to avoid infinite loop on no valid entries in pShiftList
 
Tests:
all invalid
pWebUserID valid, invlid pTempProvTimesheetID, pShiftList
pWebUserID valid, pTempProvTimesheetID, invlid pShiftList
all valid, userclass=owner but incorrect division
all valid, userclass=owner correct division
all valid, userclass=candidate but incorrect timesheet
all valid, userclass=candidate correct timesheet
*/
/* 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.owner';
select * from NetProvTimesheetShiftsSelect(null,null,null);
expect pResult=99*;
select * from NetProvTimesheetShiftsSelect('test.owner',null,null);
expect pResult=0*;
select * from NetProvTimesheetShiftsSelect('test.owner','test','test');
expect pResult=0*;
select * from NetProvTimesheetShiftsSelect('test.owner','test','shift_test');
expect pResult=0*;
select * from tempprovtimesheetshift where tempprovtimesheetid='test';
expect TempShiftID=test;
expect EOF;
update staff set divisionid='test' where staffid='TEST';
select * from NetProvTimesheetShiftsSelect('test.owner','test','test');
expect pResult=0:~Success;
update person set divisionid='TEST' where personid='TEST';
select * from NetProvTimesheetShiftsSelect('test.owner','test','test');
expect pResult=0:~Success;
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetShiftsSelect" IS 
{CREATE PROCEDURE pears."NetProvTimesheetShiftsSelect"(IN pWebUserID CHAR(20),IN pTempProvTimesheetID CHAR(20),IN pShiftList long VARCHAR)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE i SMALLINT;
  DECLARE dummy SMALLINT;
  DECLARE s CHAR(30);
  DECLARE userClass CHAR(20);
  DECLARE divid CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  IF userClass = 'OWNER' THEN
    INSERT INTO IDs( ID) 
      SELECT DISTINCT s.divisionid FROM iqxnetuser AS i KEY JOIN staff AS s WHERE i.iqxnetuserid = pwebuserid;
    IF NOT ptempprovtimesheetid = any(SELECT t.tempprovtimesheetid FROM tempprovtimesheet AS t KEY JOIN person AS p JOIN IDs ON p.divisionid = IDs.ID) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  ELSE
    IF NOT pTempProvTimesheetID = any(
      SELECT t.TempProvTimesheetID FROM tempprovtimesheet AS t KEY JOIN person KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pwebuserid UNION
      SELECT t.TempProvTimesheetID FROM 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 UNION
      SELECT t.TempProvTimesheetID FROM tempprovtimesheet AS t KEY JOIN vacancy KEY JOIN employment AS vacemp KEY JOIN company KEY JOIN employment KEY JOIN iqxnetuserlink
        WHERE iqxnetuserlink.iqxnetuserid = pwebuserid) THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  END IF;
 
  DELETE FROM tempprovtimesheetshift WHERE tempprovtimesheetid = pTempprovtimesheetid;
  SET pShiftList=REPLACE(pShiftList,'Shift_','');
  looplabel: loop
    SET i=charindex(',',pShiftList);
    IF i = 0 THEN
      SET s=pshiftlist
    ELSE
      SET s="left"(pshiftlist,i-1);
      SET pshiftlist="right"(pshiftlist,LENGTH(pshiftlist)-i)
    END IF;
    SET s=TRIM(s);
    IF s <> '' THEN
      BEGIN
        INSERT INTO tempprovtimesheetshift( tempprovtimesheetid,tempshiftid) VALUES( ptempprovtimesheetid,s) 
      exception
        WHEN others THEN
          SET dummy=0
      END
    ELSE 
      leave looplabel
    END IF;
    IF i = 0 THEN
      leave looplabel
    END IF
  END loop looplabel;
  SELECT '0:~Success'
END
 
/* DOC
2017-02-14 PC test and doc sections added
2018-07-26 PC include improved error handler also small change to avoid infinite loop on no valid entries in pShiftList
 
Tests:
    all invalid
    pWebUserID valid, invlid pTempProvTimesheetID, pShiftList
    pWebUserID valid, pTempProvTimesheetID, invlid pShiftList
    all valid, userclass=owner but incorrect division
    all valid, userclass=owner correct division
    all valid, userclass=candidate but incorrect timesheet
    all valid, userclass=candidate correct timesheet
*/
 
/* 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.owner';
select * from NetProvTimesheetShiftsSelect(null,null,null);
expect pResult=99*;
select * from NetProvTimesheetShiftsSelect('test.owner',null,null);
expect pResult=0*;
select * from NetProvTimesheetShiftsSelect('test.owner','test','test');
expect pResult=0*;
select * from NetProvTimesheetShiftsSelect('test.owner','test','shift_test');
expect pResult=0*;
select * from tempprovtimesheetshift where tempprovtimesheetid='test';
expect TempShiftID=test;
expect EOF;
update staff set divisionid='test' where staffid='TEST';
select * from NetProvTimesheetShiftsSelect('test.owner','test','test');
expect pResult=0:~Success;
update person set divisionid='TEST' where personid='TEST';
select * from NetProvTimesheetShiftsSelect('test.owner','test','test');
expect pResult=0:~Success;
 
*/
}