====== pears.NetProvTimesheetShiftCancel ======
<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"."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';
*/
}
</code>