pears.NetProvTimesheetSetTheirRef

CREATE PROCEDURE "pears"."NetProvTimesheetSetTheirRef"( IN "pWebUserID" CHAR(20),IN "ptempprovtimesheetid" CHAR(20),IN "pTheirRef" CHAR(100) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  IF NOT "ptempprovtimesheetid"
     = any(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;
  UPDATE "tempprovtimesheet" SET "theirref" = "ucase"("ptheirref") WHERE "tempprovtimesheetid" = "ptempprovtimesheetid";
  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 pTheirRef
all valid
 
*/
/* 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 NetProvTimesheetSetTheirRef(null,null,null);
expect 99*;
select * from NetProvTimesheetSetTheirRef('test.candidate',null,null);
expect 0*;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST',null);
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=z;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST','testRef');
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=TESTREF;
 
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetSetTheirRef" IS 
{CREATE PROCEDURE pears."NetProvTimesheetSetTheirRef"(IN pWebUserID CHAR(20),IN ptempprovtimesheetid CHAR(20),IN pTheirRef CHAR(100) DEFAULT NULL)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  IF NOT ptempprovtimesheetid
     = any(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;
  UPDATE tempprovtimesheet SET theirref = ucase(ptheirref) WHERE tempprovtimesheetid = ptempprovtimesheetid;
  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 pTheirRef
    all valid
 
*/
 
/* 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 NetProvTimesheetSetTheirRef(null,null,null);
expect 99*;
select * from NetProvTimesheetSetTheirRef('test.candidate',null,null);
expect 0*;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST',null);
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=z;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST','testRef');
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=TESTREF;
 
 
*/
}