pears.NetProvTimesheetShiftCancel

CREATE PROCEDURE "pears"."NetProvTimesheetShiftCancel"( IN "pWebUserID" CHAR(20),IN "ptempshiftid" CHAR(250) ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "userClass" CHAR(20);
  DECLARE "userID" CHAR(50);
  DECLARE "pCancelReason" CHAR(5);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  SELECT "iqxnetuserclassid","loginid" INTO "userClass","userID" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID";
  SET "pCancelReason" = "isnull"((SELECT "WPKMaintainGetSwitchValue"('TSSHIFTCANCELREASON','','L')),'~');
  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 "ptempshiftid" = any(SELECT "s"."tempshiftid" FROM "tempshift" AS "s" KEY JOIN "person" AS "p" JOIN "IDs" ON "p"."divisionid" = "IDs"."ID") THEN
      SELECT '98:~Permission denied';
      RETURN
    END IF
  ELSE IF NOT "pTempShiftID" = any(
      SELECT "t"."TempShiftID" FROM "tempshift" AS "t" KEY JOIN "person" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid" UNION
      SELECT "t"."TempShiftID" FROM "tempshift" 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"."TempShiftID" FROM "tempshift" 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;
  UPDATE "tempshift" SET "state" = 'C',"cancelreason" = "pCancelReason","whencancelled" = CURRENT TIMESTAMP,"whocancelled" = "userstaffid",
    "clientconfirmed" = 1,"tempconfirmed" = 1,"cbill" = 0,"cpay" = 0,"crefill" = 0,
    "Note" = 'Cancelled on online timesheet complete by '+"userID"
    WHERE "tempshiftid" = "ptempshiftid";
  SELECT '0:~Success'
END /* DOC
2016-12-06 PC doc and test
2017-11-15 PC IQ-294 Parameterize cancel reason. Requires SHIFTCANCELDEFAULT inserted in Switches - use the text of the status you require in the description 
2018-04-19 PC modified after testing. allow multiple cancel defaults - one for each class of user. Require shiftcancelcandidate,shiftcancelclient,shiftcancelowner
2018-07-26 PC include improved error handler
2020-01-23 AW updated to use the general settings
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid ptempshiftid
valid pWebUserID, ptempshiftid as owner
valid pWebUserID, ptempshiftid as client
 
*/
/* 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';
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
update tempshift set personid='TEST' where tempshiftid='TEST';
select * from NetProvTimesheetShiftCancel(null,null);
expect 99*;
select * from NetProvTimesheetShiftCancel('test.owner',null);
expect 0*;
select * from NetProvTimesheetShiftCancel('test.owner','TEST');
expect pResult=0*;
select * from tempshift where tempshiftid='TEST';
expect TempShiftID=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 tempshift set state='.' where tempshiftid='TEST';
select * from NetProvTimesheetShiftCancel('test.client','TEST');
expect pResult=0*;
select * from tempshift where tempshiftid='TEST';
expect TempShiftID=TEST;
update staff set divisionid=null where staffid='TEST';
update person set divisionid=null where personid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetShiftCancel" IS 
{CREATE PROCEDURE pears."NetProvTimesheetShiftCancel"(IN pWebUserID CHAR(20), IN ptempshiftid CHAR(250))
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE userClass CHAR(20);
  DECLARE userID CHAR(50);
  DECLARE pCancelReason CHAR(5);
 
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  SELECT iqxnetuserclassid, loginid INTO userClass, userID FROM iqxnetuser WHERE iqxnetuserid = pWebUserID;
  SET "pCancelReason" = "isnull"((SELECT WPKMaintainGetSwitchValue('TSSHIFTCANCELREASON','','L')),'~');
 
  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 ptempshiftid = any(SELECT s.tempshiftid FROM tempshift AS s KEY JOIN person AS p JOIN IDs ON p.divisionid = IDs.ID) THEN
      SELECT '98:~Permission denied';
      RETURN
    END IF
  ELSE
    IF NOT pTempShiftID = any(
      SELECT t.TempShiftID FROM tempshift AS t KEY JOIN person KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pwebuserid UNION
      SELECT t.TempShiftID FROM tempshift 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.TempShiftID FROM tempshift 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;
 
  UPDATE tempshift SET state = 'C',cancelreason = pCancelReason, whencancelled = CURRENT TIMESTAMP, whocancelled = userstaffid,
      clientconfirmed = 1, tempconfirmed = 1, cbill = 0, cpay = 0, crefill = 0, 
      Note = 'Cancelled on online timesheet complete by '+userID 
      WHERE tempshiftid = ptempshiftid;
 
  SELECT '0:~Success'
END
 
/* DOC
2016-12-06 PC doc and test
2017-11-15 PC IQ-294 Parameterize cancel reason. Requires SHIFTCANCELDEFAULT inserted in Switches - use the text of the status you require in the description 
2018-04-19 PC modified after testing. allow multiple cancel defaults - one for each class of user. Require shiftcancelcandidate,shiftcancelclient,shiftcancelowner
2018-07-26 PC include improved error handler
2020-01-23 AW updated to use the general settings
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid ptempshiftid
    valid pWebUserID, ptempshiftid as owner
    valid pWebUserID, ptempshiftid as client
 
*/
 
/* 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';
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
update tempshift set personid='TEST' where tempshiftid='TEST';
select * from NetProvTimesheetShiftCancel(null,null);
expect 99*;
select * from NetProvTimesheetShiftCancel('test.owner',null);
expect 0*;
select * from NetProvTimesheetShiftCancel('test.owner','TEST');
expect pResult=0*;
select * from tempshift where tempshiftid='TEST';
expect TempShiftID=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 tempshift set state='.' where tempshiftid='TEST';
select * from NetProvTimesheetShiftCancel('test.client','TEST');
expect pResult=0*;
select * from tempshift where tempshiftid='TEST';
expect TempShiftID=TEST;
update staff set divisionid=null where staffid='TEST';
update person set divisionid=null where personid='TEST';
*/
}