pears.NetProvTimesheetSendForApproval

CREATE PROCEDURE "pears"."NetProvTimesheetSendForApproval"( IN "pWebUserID" CHAR(20),IN "ptempprovtimesheetid" CHAR(20),IN "pfaxnumber" CHAR(20),IN "pemailaddress" CHAR(100) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "returnMsg" CHAR(250);
  DECLARE "faxdomain" long VARCHAR;
  IF "pfaxnumber" IS NOT NULL OR "pemailaddress" IS NOT NULL THEN
    IF "pfaxnumber" IS NOT NULL AND "pfaxnumber" <> '' THEN
      SET "faxdomain" = (SELECT FIRST "EmailFaxAddress" FROM "params");
      SET "faxdomain" = "replace"("faxdomain",'#',"pfaxnumber");
      SET "faxdomain" = "replace"("faxdomain",' ','');
      SET "faxdomain" = "replace"("faxdomain",'(','');
      SET "faxdomain" = "replace"("faxdomain",')','');
      SET "faxdomain" = "replace"("faxdomain",'+','');
      INSERT INTO "IQXNetAction"( "IQXNetActionID","IQXNetUserID","ActionType","TargetType","ActionState","WhenRequested","TargetID","ActionAddress" ) VALUES
        ( "uniquekey"("ptempprovtimesheetid"),"pWebUserID",'SendApproveEfax','T',0,"NOW"(),"ptempprovtimesheetid","faxdomain" ) ;
      SET "returnMsg" = '0:~Success'
    END IF;
    IF "pemailaddress" IS NOT NULL AND "pemailaddress" <> '' THEN
      INSERT INTO "IQXNetAction"( "IQXNetActionID","IQXNetUserID","ActionType","TargetType","ActionState","WhenRequested","TargetID","ActionAddress" ) VALUES
        ( "uniquekey"("ptempprovtimesheetid"),"pWebUserID",'SendApproveEmail','T',0,"NOW"(),"ptempprovtimesheetid","pemailaddress" ) ;
      SET "returnMsg" = '0:~Success'
    END IF
  ELSE SET "returnMsg" = '99:~No Fax number or Email Address provided.'
  END IF;
  SELECT "returnMsg"
END /* DOC
2016-12-05 PC test and doc
2018-07-26 PC include improved error handler
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid pTempProvTimesheetID
valid pWebUserID, invalid pTempProvTimesheetID, invalid pfaxnumber, pemailaddress
valid pWebUserID, invalid pTempProvTimesheetID, pfaxnumber, invalid pemailaddress
valid pWebUserID, invalid pTempProvTimesheetID, pemailaddress, invalid pfaxnumber
valid pWebUserID, invalid pTempProvTimesheetID, pemailaddress, pfaxnumber
 
*/
/* TEST
call NetTestSetup('');
delete from iqxnetaction where iqxnetuserid='test.candidate';
select * from NetProvTimesheetSendForApproval(null,null,null,null);
expect 99*;
select * from NetProvTimesheetSendForApproval('test.candidate',null,null,null);
expect 99*;
select * from NetProvTimesheetSendForApproval('test.candidate','TEST',null,null);
expect 99*;
select * from NetProvTimesheetSendForApproval('test.candidate','TEST','0999999999',null);
expect 0*;
select * from NetProvTimesheetSendForApproval('test.candidate','TEST',null,'[email protected]');
expect 0*;
select top 1 * from iqxnetaction where iqxnetuserid='test.candidate' order by iqxnetactionid desc;
expect ActionType=SendApproveEmail,TargetType=T,TargetID=TEST,[email protected];
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetSendForApproval" IS 
{CREATE PROCEDURE pears."NetProvTimesheetSendForApproval"( IN pWebUserID CHAR(20), IN ptempprovtimesheetid CHAR(20), IN pfaxnumber CHAR(20), IN pemailaddress CHAR(100) )
RESULT(pResult CHAR(250))
BEGIN
 
  DECLARE returnMsg CHAR(250);
  DECLARE faxdomain long VARCHAR;
 
  IF pfaxnumber IS NOT NULL OR pemailaddress IS NOT NULL THEN
    IF pfaxnumber IS NOT NULL AND pfaxnumber != '' THEN
      SET faxdomain =  (SELECT FIRST EmailFaxAddress FROM params);
      SET faxdomain = REPLACE(faxdomain,'#',pfaxnumber);
      SET faxdomain = REPLACE(faxdomain,' ','');
      SET faxdomain = REPLACE(faxdomain,'(','');
      SET faxdomain = REPLACE(faxdomain,')','');
      SET faxdomain = REPLACE(faxdomain,'+','');
      INSERT INTO IQXNetAction(IQXNetActionID, IQXNetUserID,  ActionType, TargetType, ActionState, WhenRequested, TargetID, ActionAddress)    
      VALUES( uniquekey(ptempprovtimesheetid), pWebUserID, 'SendApproveEfax', 'T', 0, NOW(), ptempprovtimesheetid, faxdomain);
      SET returnMsg = '0:~Success';
    END IF;
 
    IF pemailaddress IS NOT NULL AND pemailaddress != '' THEN
      INSERT INTO IQXNetAction(IQXNetActionID, IQXNetUserID,  ActionType, TargetType, ActionState, WhenRequested, TargetID, ActionAddress)    
      VALUES( uniquekey(ptempprovtimesheetid), pWebUserID, 'SendApproveEmail', 'T', 0, NOW(), ptempprovtimesheetid, pemailaddress);
      SET returnMsg = '0:~Success'
    END IF;
  ELSE
    SET returnMsg =  '99:~No Fax number or Email Address provided.';
  END IF;
 
  SELECT returnMsg;
END
 
/* DOC
2016-12-05 PC test and doc
2018-07-26 PC include improved error handler
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid pTempProvTimesheetID
    valid pWebUserID, invalid pTempProvTimesheetID, invalid pfaxnumber, pemailaddress
    valid pWebUserID, invalid pTempProvTimesheetID, pfaxnumber, invalid pemailaddress
    valid pWebUserID, invalid pTempProvTimesheetID, pemailaddress, invalid pfaxnumber
    valid pWebUserID, invalid pTempProvTimesheetID, pemailaddress, pfaxnumber
 
*/
 
/* TEST
call NetTestSetup('');
delete from iqxnetaction where iqxnetuserid='test.candidate';
select * from NetProvTimesheetSendForApproval(null,null,null,null);
expect 99*;
select * from NetProvTimesheetSendForApproval('test.candidate',null,null,null);
expect 99*;
select * from NetProvTimesheetSendForApproval('test.candidate','TEST',null,null);
expect 99*;
select * from NetProvTimesheetSendForApproval('test.candidate','TEST','0999999999',null);
expect 0*;
select * from NetProvTimesheetSendForApproval('test.candidate','TEST',null,'[email protected]');
expect 0*;
select top 1 * from iqxnetaction where iqxnetuserid='test.candidate' order by iqxnetactionid desc;
expect ActionType=SendApproveEmail,TargetType=T,TargetID=TEST,[email protected];
 
*/
}