pears.NetAgencyContact

CREATE PROCEDURE "pears"."NetAgencyContact"( IN "pWebUserID" CHAR(20) ) 
RESULT( "employmentid" CHAR(20),"directphone" CHAR(250),"mobile" CHAR(250),"email" CHAR(250),"directfax" CHAR(250),"cascademethod" CHAR(1),"cascadeaddress" CHAR(250) ) 
//IQXWeb
BEGIN
  SELECT top 1
    "employment"."employmentid",
    "getphone"('CP','Direct Telephone',"employment"."employmentid"),
    "getphone"('CP','Mobile',"employment"."employmentid"),
    "netgetemail"('CP',"employment"."employmentid"),
    "getphone"('CP','Direct Fax',"employment"."employmentid"),
    "cascadedeliveryaddress"."deliverymethod",
    "cascadedeliveryaddress"."address"
    FROM "employment" KEY JOIN "iqxnetuserlink"
      ,"employment" LEFT OUTER JOIN "cascadedeliveryaddress" ON "employment"."companyid" = "cascadedeliveryaddress"."companyid"
    WHERE "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"
    ORDER BY "isnull"("employment"."leavedate","today"()) ASC
END /* DOC
2016-10-10 PC add Doc and test sections
2018-04-20 PC revise tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries, and test routine
 
*/
/* TEST
call NetTestSetup('');
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='test.candidate';
select isnull(cascadeaddress,'!') as A,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,[email protected],A=!;
insert into cascadedeliveryaddress values ('TEST','E','[email protected]');
select isnull(cascadeaddress,'!') as B,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,[email protected],[email protected];
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='test.candidate';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyContact" IS 
{CREATE PROCEDURE pears."NetAgencyContact"(IN pWebUserID CHAR(20))
RESULT(employmentid CHAR(20),directphone CHAR(250),mobile CHAR(250),email CHAR(250),directfax CHAR(250),cascademethod CHAR(1),cascadeaddress CHAR(250))
//IQXWeb
BEGIN
    SELECT top 1 
        employment.employmentid,
        getphone('CP','Direct Telephone',employment.employmentid),
        getphone('CP','Mobile',employment.employmentid),
        netgetemail('CP',employment.employmentid),
        getphone('CP','Direct Fax',employment.employmentid),
        cascadedeliveryaddress.deliverymethod,
        cascadedeliveryaddress.address
    FROM 
        employment KEY JOIN iqxnetuserlink,
        employment LEFT OUTER JOIN cascadedeliveryaddress ON employment.companyid = cascadedeliveryaddress.companyid
    WHERE 
        iqxnetuserlink.iqxnetuserid = pWebUserID
    ORDER BY 
        isnull(employment.leavedate,today(*))
END
 
/* DOC
2016-10-10 PC add Doc and test sections
2018-04-20 PC revise tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries, and test routine
 
*/
 
/* TEST
call NetTestSetup('');
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='test.candidate';
select isnull(cascadeaddress,'!') as A,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,[email protected],A=!;
insert into cascadedeliveryaddress values ('TEST','E','[email protected]');
select isnull(cascadeaddress,'!') as B,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,[email protected],[email protected];
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='test.candidate';
*/
}