====== pears.NetProvTimesheetShiftsSelect ======
<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"."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;

*/
}
</code>