pears.NetProvTimesheetSet

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;
*/
}