====== pears.NetProvTimesheetSet ======
<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"."NetProvTimesheetSet"( in "pWebUserID" char(20),in "ptempprovtimesheetid" char(20),in "pTheirRef" char(100) default null,in "pDescription" long varchar default null,in "pHasScannedImage" smallint default null,in "qanswers" long varchar default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "cid" char(20);
  declare "pid" char(20);
  select "e"."companyid","t"."personid" into "cid","pid"
    from "tempprovtimesheet" as "t" key join "vacancy" key join "employment" as "e"
    where "t"."tempprovtimesheetid" = "ptempprovtimesheetid";
  if "NetHasPermission"("pWebUserID","pid","cid") = 0 then
    select '99:~Permission denied';
    return
  end if;
  if "pTheirRef" is not null or "pDescription" is not null then
    update "tempprovtimesheet" set "theirref" = "isnull"("ucase"("ptheirref"),"theirref"),"description" = "isnull"("pDescription","description") where "tempprovtimesheetid" = "ptempprovtimesheetid"
  end if;
  call "IQXNetSaveQuestionnaire"("ptempprovtimesheetid","qanswers",'T'); // Note T parameter because this question group could have questions from other taglocations
  select '0:~Success'
end /* DOC
2016-12-06 PC doc and test
2018-07-26 PC include improved error handler

Tests:
invalid pWebUserID
valid pWebUserID, invalid ptempprovtimesheet
valid pWebUserID, ptempprovtimesheet, invalid everything else
valid pWebUserID, ptempprovtimesheet, everything else
*/
/* 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.candidate';
select * from NetProvTimesheetSet(null,null,null,null,null,null);
expect 99*;
select * from NetProvTimesheetSet('test.candidate',null,null,null,null,null);
expect 99*;
select * from NetProvTimesheetSet('test.candidate','TEST',null,null,null,null);
expect 0*;
select * from NetProvTimesheetSet('test.candidate','TEST','testRef',null,null,null);
expect 0*;
select * from tempprovtimesheet where tempprovtimesheetid='TEST';
expect theirref=TESTREF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetSet" IS 
{create PROCEDURE pears."NetProvTimesheetSet"(in pWebUserID char(20),in ptempprovtimesheetid char(20),in pTheirRef char(100) default null, in pDescription long varchar default null, in pHasScannedImage smallint default null, in "qanswers" long varchar default null)
result(pResult char(250))
// IQXWeb
begin
  declare cid char(20);
  declare pid char(20);
  select e.companyid,t.personid into cid,pid 
  from tempprovtimesheet as t key join vacancy key join employment as e
  where t.tempprovtimesheetid=ptempprovtimesheetid;
  if NetHasPermission(pWebUserID,pid,cid)=0 then
    select '99:~Permission denied';
    return
  end if;
  if pTheirRef is not null or pDescription is not null then
    update tempprovtimesheet set theirref = isnull(ucase(ptheirref),theirref),description=isnull(pDescription,description) where tempprovtimesheetid = ptempprovtimesheetid
  end if;
  call "IQXNetSaveQuestionnaire"("ptempprovtimesheetid","qanswers",'T');  // Note T parameter because this question group could have questions from other taglocations
  select '0:~Success'
end

/* DOC
2016-12-06 PC doc and test
2018-07-26 PC include improved error handler

Tests:
    invalid pWebUserID
    valid pWebUserID, invalid ptempprovtimesheet
    valid pWebUserID, ptempprovtimesheet, invalid everything else
    valid pWebUserID, ptempprovtimesheet, everything else
*/

/* 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.candidate';
select * from NetProvTimesheetSet(null,null,null,null,null,null);
expect 99*;
select * from NetProvTimesheetSet('test.candidate',null,null,null,null,null);
expect 99*;
select * from NetProvTimesheetSet('test.candidate','TEST',null,null,null,null);
expect 0*;
select * from NetProvTimesheetSet('test.candidate','TEST','testRef',null,null,null);
expect 0*;
select * from tempprovtimesheet where tempprovtimesheetid='TEST';
expect theirref=TESTREF;
*/
}
</code>