pears.NetGetEmail

CREATE FUNCTION "pears"."NetGetEmail"( IN "cpe" CHAR(2),IN "recid" CHAR(20) ) 
RETURNS CHAR(250)
//IQXWeb
BEGIN
  DECLARE "rv" CHAR(250);
  SELECT FIRST "replace"("phone"."number",'*','') INTO "rv" FROM "phone" KEY JOIN "phonetype" WHERE "phone"."whoid" = "recid" AND "phone"."who" = (IF "cpe" = 'E' THEN 'CP' ELSE "cpe" endif)
    AND "phonetype"."name" = 'E-mail' ORDER BY 1 ASC;
  RETURN("rv")
END /* TEST
call NetTestSetup('');
delete from phone where phoneid like 'test%';
insert into phone (phoneid, phonetypeid, who, whoid, number) values ('Test1',(select top 1 phonetypeid from phonetype where name like 'E-Mail' and contact=1),'CP','TEST','[email protected]');
insert into phone (phoneid, phonetypeid, who, whoid, number) values ('Test2',(select top 1 phonetypeid from phonetype where name like 'E-Mail' and company=1),'C','TEST','[email protected]');
select NetGetEmail(null,null);
expect;
select NetGetEmail('x','f99');
expect;
select isnull(NetGetEmail('x','TEST'),'empty');
expect empty;
select NetGetEmail('C','x');
expect;
select NetGetEmail('C','TEST');
expect [email protected];
select NetGetEmail('E','TEST');
expect [email protected];
delete from phone where phoneid like 'test%';
*/
/* DOC
2016-08-25 PC tests and document
2020-04-21 JW Rewritten for inlining.
 
inputs:
cpe - phone.who
recid - phone.whoid
 
outputs:
rv - return value
 
tests:
null cpe and null recid
incorrect cpe incorrect recid
incorrect cpe correct recid
correct cpe (not E) incorrect recid
correct cpe (not E) correct recid
correct cpe (E) correct recid
*/