====== pears.NetProvTimesheetRateSet ======
<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"."NetProvTimesheetRateSet"( in "pWebUserID" char(20),in "pTempProvTimesheetLineID" char(250),in "pUnitDescription" char(250) default null,in "pUnits" char(250) default null,in "pRate" char(250) default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "i" smallint;
  declare "isExp" smallint;
  declare "isnewline" smallint;
  declare "newlineid" char(20);
  declare "ptempprovtimesheetid" char(20);
  declare "userClass" char(20);
  declare "divid" char(20);
  declare local temporary table "IDs"(
    "ID" char(20) null,
    ) not transactional;
  if "pTempProvTimesheetLineID" like 'Copy%' then
    set "isnewline" = 1
  else
    set "isnewline" = 0
  end if;
  set "i" = "charindex"('_',"pTempProvTimesheetLineID");
  if "i" > 0 then
    set "pTempProvTimesheetLineID" = "right"("pTempProvTimesheetLineID","length"("pTempProvTimesheetLineID")-"i")
  end if;
  set "ptempprovtimesheetid" = (select first "tempprovtimesheetid" from "tempprovtimesheetline" where "tempprovtimesheetlineid" = "pTempProvTimesheetLineID");
  select(if "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' or "isnull"("temppayband"."isexpenses",0) = 1 then 1 else 0 endif)
    into "isExp" from "tempprovtimesheetline" key join "temppayband"
    where "tempprovtimesheetline"."tempprovtimesheetlineid" = "ptempprovtimesheetlineid";
  if "isExp" = 0 then -- Can only edit rate if expenses
    set "pRate" = null
  end if;
  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 "isnewline" = 1 then
    set "newlineid" = "uniquekey"("pTempProvTimesheetLineID");
    insert into "tempprovtimesheetline"( "tempprovtimesheetlineid","tempprovtimesheetid","temppaybandid","linenumber","unitspaid","unitscharged","payrate","chargerate",
      "description" ) select "newlineid","ptempprovtimesheetid","temppaybandid",
        "isnull"((select 1+"max"("linenumber") from "tempprovtimesheetline" where "tempprovtimesheetid" = "ptempprovtimesheetid"),1),
        "isnull"("punits","unitspaid"),"isnull"("punits","unitscharged"),"isnull"("prate","payrate"),"isnull"("prate","chargerate"),
        "isnull"("punitdescription","description") from "tempprovtimesheetline" where "tempprovtimesheetlineid" = "ptempprovtimesheetlineid"
  else
    update "tempprovtimesheetline" set "unitspaid" = "isnull"("punits","unitspaid"),"unitscharged" = "isnull"("punits","unitscharged"),"payrate" = "isnull"("prate","payrate"),"chargerate" = "isnull"("prate","chargerate"),
      "description" = "isnull"("punitdescription","description") where "tempprovtimesheetlineid" = "pTempProvTimesheetLineID"
  end if;
  select '0:~Success'
end /* DOC
2016-09-01 PC doc and test

inputs:
pWebUserID
pTempProvTimesheetLineID
pUnitDescription
pUnits
pRate

outputs:
pResult

test:
edit a timesheet that owner does not own
edit an existing non-expense line that an owner owns
edit an existing expense line
edit an existing line that an client owns
duplicate a line

*/
/* TEST
call NetTestSetup('');
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
select * from NetProvTimesheetRateSet('test.owner','test1','zz',8,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz,unitspaid=8,unitscharged=8,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.owner','test2','zz2',9,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test2';
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
select * from NetProvTimesheetRateSet('test.client','test1','zz3',7,17);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.client','copy_test1','zz4',null,null);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where description like 'zz%' order by linenumber;
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
expect description=zz4,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect EOF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetRateSet" IS 
{create PROCEDURE pears."NetProvTimesheetRateSet"(in pWebUserID char(20),in pTempProvTimesheetLineID char(250),in pUnitDescription char(250) default null,in pUnits char(250) default null,in pRate char(250) default null)
result(pResult char(250))
// IQXWeb
begin
  declare i smallint;
  declare isExp smallint;
  declare isnewline smallint;
  declare newlineid char(20);
  declare ptempprovtimesheetid char(20);
  declare userClass char(20);
  declare divid char(20);
  declare local temporary table IDs(
    ID char(20) null,
    ) not transactional;
  if pTempProvTimesheetLineID like 'Copy%' then
    set isnewline=1
  else
    set isnewline=0
  end if;
  set i=charindex('_',pTempProvTimesheetLineID);
  if i > 0 then
    set pTempProvTimesheetLineID="right"(pTempProvTimesheetLineID,length(pTempProvTimesheetLineID)-i)
  end if;
  set ptempprovtimesheetid=(select first tempprovtimesheetid from tempprovtimesheetline where tempprovtimesheetlineid = pTempProvTimesheetLineID);
  
  select (if isnull(temppayband.payrollflag,'') = 'EXPENSES' or isnull(temppayband.isexpenses,0) = 1 then 1 else 0 endif) 
  into isExp from tempprovtimesheetline key join temppayband 
  where tempprovtimesheetline.tempprovtimesheetlineid = ptempprovtimesheetlineid;  
  if isExp=0 then  -- Can only edit rate if expenses
    set pRate=null;
  end if;
  
  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 isnewline = 1 then
    set newlineid=uniquekey(pTempProvTimesheetLineID);
    insert into tempprovtimesheetline( tempprovtimesheetlineid,tempprovtimesheetid,temppaybandid,linenumber,unitspaid,unitscharged,payrate,chargerate,
      description) select newlineid,ptempprovtimesheetid,temppaybandid,
        isnull((select 1+max(linenumber) from tempprovtimesheetline where tempprovtimesheetid = ptempprovtimesheetid),1),
        isnull(punits,unitspaid),isnull(punits,unitscharged),isnull(prate,payrate),isnull(prate,chargerate),
        isnull(punitdescription,description) from tempprovtimesheetline where tempprovtimesheetlineid = ptempprovtimesheetlineid
  else
    update tempprovtimesheetline set unitspaid = isnull(punits,unitspaid),unitscharged = isnull(punits,unitscharged),payrate = isnull(prate,payrate),chargerate = isnull(prate,chargerate),
      description = isnull(punitdescription,description) where tempprovtimesheetlineid = pTempProvTimesheetLineID
  end if;
  select '0:~Success'
end

/* DOC
2016-09-01 PC doc and test

inputs:
    pWebUserID
    pTempProvTimesheetLineID
    pUnitDescription
    pUnits
    pRate

outputs:
    pResult

test:
    edit a timesheet that owner does not own
    edit an existing non-expense line that an owner owns
    edit an existing expense line
    edit an existing line that an client owns
    duplicate a line
    
*/

/* TEST
call NetTestSetup('');
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
select * from NetProvTimesheetRateSet('test.owner','test1','zz',8,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz,unitspaid=8,unitscharged=8,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.owner','test2','zz2',9,23);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test2';
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
select * from NetProvTimesheetRateSet('test.client','test1','zz3',7,17);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where TempProvTimeSheetLineID='test1';
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
select * from NetProvTimesheetRateSet('test.client','copy_test1','zz4',null,null);
expect 0*;
select description,unitspaid,unitscharged,payrate,chargerate from tempprovtimesheetline where description like 'zz%' order by linenumber;
expect description=zz3,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect description=zz2,unitspaid=9,unitscharged=9,payrate=23,chargerate=23;
expect description=zz4,unitspaid=7,unitscharged=7,payrate=10,chargerate=11;
expect EOF;
*/
}
</code>