====== pears.NetProvTimesheetComplete ======
<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"."NetProvTimesheetComplete"( in "pWebUserID" char(20),in "ptempprovtimesheetid" char(20),in "pInstruction" char(20) default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "oldstat" smallint;
  declare "newstat" smallint;
  declare "statdesc" 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 "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 "person" 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;
  if "ucase"("isnull"("pInstruction",'')) = 'REVERSE' then
    set "newstat" = 1;
    set "oldstat" = 2;
    set "statdesc" = 'Timesheet Un-filled'
  else
    set "newstat" = 2;
    set "oldstat" = 1;
    set "statdesc" = 'Timesheet Filled'
  end if;
  update "tempprovtimesheet" set "extnumber" = "newstat" where "tempprovtimesheetid" = "ptempprovtimesheetid";
  if @@rowcount = 1 then
    insert into "tempprovtimesheethistory"( "tempprovtimesheethistoryid","tempprovtimesheetid","serialnumber","description","oldstatus","newstatus","staffid","whenentered","externaluserid" ) 
      select "uniquekey"("ptempprovtimesheetid"),"ptempprovtimesheetid","serialnumber","statdesc","oldstat","newstat","userstaffid",current timestamp,"pwebuserid" from "tempprovtimesheet" where "tempprovtimesheetid" = "ptempprovtimesheetid";
    select '0:~Success'
  else
    select '98:~Timesheet already finalised or deleted'
  end if
end /* DOC
2016-08-31 PC test and doc
2017-11-14 LM inserted select extnumber from temprovtime where tempprovtimesheetid = 'test'; 
expect extnumber=1.
2017-11-14 LM changed the order by to "order by" desc to get the latest timesheet.
2018-07-25 PC include improved error handler

inputs:
pWebUserID
ptempprovtimesheetid
pInstruction

outputs:
pResult

tests:
invalid timesheet for an owner
timesheet does not belong to pWebUserID
null instruction
reverse timesheet
completed timesheet
*/
/* TEST
call NetTestSetup('');
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';
insert into tempprovtimesheet(tempprovtimesheetid, period, periodlength, personid) on existing update defaults off values('test', string(dateformat(today(*),'yyyy'),'01'), 'W', 'test' );
insert into tempprovtimesheetline(tempprovtimesheetlineid, tempprovtimesheetid, TempPayBandID,) on existing update defaults off values('test', 'test', 'test1');
insert into tempprovtimesheethistory(tempprovtimesheethistoryid, tempprovtimesheetid, oldstatus, newstatus, description) on existing update defaults off values('test', 'test', 1, 2, 'Timesheet Filled');
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update staff set divisionid='test' where staffid='TEST';
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update person set divisionid='TEST' where personid='test';
select count (*) as rc from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect rc=0;
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0*;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test';
expect oldstatus=1,newstatus=2,Descrip=Timesheet!Filled;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect EOF;
select * from netprovtimesheetcomplete('test.owner','test','REVERSE');
expect pResult=0*;
select extnumber from tempprovtimesheet where tempprovtimesheetid = 'test';
expect extnumber=1;
select oldstatus from tempprovtimesheethistory where tempprovtimesheetid = 'test' order by whenentered desc;
expect oldstatus=2;
select top 1 oldstatus,newstatus,replace(replace(description,' ','!'),'-','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test' order by whenentered desc;
expect oldstatus=2,newstatus=1,Descrip=Timesheet!Un!filled;
expect EOF;
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';



*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetComplete" IS 
{create PROCEDURE pears."NetProvTimesheetComplete"(in pWebUserID char(20),in ptempprovtimesheetid char(20),in pInstruction char(20) default null)
result(pResult char(250))
// IQXWeb
begin
  declare oldstat smallint;
  declare newstat smallint;
  declare statdesc 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 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 person 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;
  
  if ucase(isnull(pInstruction,'')) = 'REVERSE' then
    set newstat=1;
    set oldstat=2;
    set statdesc='Timesheet Un-filled'
  else
    set newstat=2;
    set oldstat=1;
    set statdesc='Timesheet Filled'
  end if;
  update tempprovtimesheet set extnumber = newstat where tempprovtimesheetid = ptempprovtimesheetid;
  if @@rowcount = 1 then
    insert into tempprovtimesheethistory( tempprovtimesheethistoryid,tempprovtimesheetid,serialnumber,description,oldstatus,newstatus,staffid,whenentered,externaluserid) 
      select uniquekey(ptempprovtimesheetid),ptempprovtimesheetid,serialnumber,statdesc,oldstat,newstat,userstaffid,current timestamp,pwebuserid from tempprovtimesheet where tempprovtimesheetid = ptempprovtimesheetid;
    select '0:~Success'
  else
    select '98:~Timesheet already finalised or deleted'
  end if;
end

/* DOC
2016-08-31 PC test and doc
2017-11-14 LM inserted select extnumber from temprovtime where tempprovtimesheetid = 'test'; 
expect extnumber=1.
2017-11-14 LM changed the order by to "order by" desc to get the latest timesheet.
2018-07-25 PC include improved error handler

inputs:
    pWebUserID
    ptempprovtimesheetid
    pInstruction

outputs:
    pResult

tests:
    invalid timesheet for an owner
    timesheet does not belong to pWebUserID
    null instruction
    reverse timesheet
    completed timesheet
*/

/* TEST
call NetTestSetup('');
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';
insert into tempprovtimesheet(tempprovtimesheetid, period, periodlength, personid) on existing update defaults off values('test', string(dateformat(today(*),'yyyy'),'01'), 'W', 'test' );
insert into tempprovtimesheetline(tempprovtimesheetlineid, tempprovtimesheetid, TempPayBandID,) on existing update defaults off values('test', 'test', 'test1');
insert into tempprovtimesheethistory(tempprovtimesheethistoryid, tempprovtimesheetid, oldstatus, newstatus, description) on existing update defaults off values('test', 'test', 1, 2, 'Timesheet Filled');
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update staff set divisionid='test' where staffid='TEST';
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0:~Success;
update person set divisionid='TEST' where personid='test';
select count (*) as rc from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect rc=0;
select * from netprovtimesheetcomplete('test.owner','test',null);
expect pResult=0*;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test';
expect oldstatus=1,newstatus=2,Descrip=Timesheet!Filled;
select oldstatus,newstatus,replace(description,' ','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test2';
expect EOF;
select * from netprovtimesheetcomplete('test.owner','test','REVERSE');
expect pResult=0*;
select extnumber from tempprovtimesheet where tempprovtimesheetid = 'test';
expect extnumber=1;
select oldstatus from tempprovtimesheethistory where tempprovtimesheetid = 'test' order by whenentered desc;
expect oldstatus=2;
select top 1 oldstatus,newstatus,replace(replace(description,' ','!'),'-','!') as Descrip from tempprovtimesheethistory where tempprovtimesheetid='test' order by whenentered desc;
expect oldstatus=2,newstatus=1,Descrip=Timesheet!Un!filled;
expect EOF;
delete from tempprovtimesheetline where tempprovtimesheetid = 'test';
delete from tempprovtimesheet where tempprovtimesheetid = 'test';
delete from tempprovtimesheethistory where tempprovtimesheetid = 'test';



*/
}
</code>