pears.NetContactList

CREATE PROCEDURE "pears"."NetContactList"( IN "pWebUserID" CHAR(20) ) 
RESULT( "EmploymentID" CHAR(20),"CompanyName" CHAR(100),"PersonName" CHAR(100),"DirectPhone" CHAR(250),"Mobile" CHAR(250),"Email" CHAR(250),"DirectFax" CHAR(250),"Position" CHAR(100),"Department" CHAR(100),"LoginID" CHAR(100) ) 
//IQXWeb
BEGIN
  SELECT "employment"."employmentid",
    "company"."name" AS "companyname",
    "person"."name" AS "personname",
    "getphone"('CP','Direct Telephone',"employment"."employmentid"),
    "getphone"('CP','Mobile',"employment"."employmentid"),
    "getphone"('CP','E-mail',"employment"."employmentid"),
    "getphone"('CP','Direct Fax',"employment"."employmentid"),
    "employment"."position",
    "employment"."department",
    "iqxnetuser"."loginid"
    FROM "employment" KEY JOIN("company","person")
      ,"employment" KEY JOIN "iqxnetuserlink" KEY JOIN "iqxnetuser"
    WHERE "employment"."companyid" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    ORDER BY "person"."keyname" ASC,"company"."keyname" ASC
END /* DOC
2016-08-25 PC doc and test
 
inputs:
pWebUserID
 
outputs:
EmploymentID
CompanyName
PersonName
DirectPhone
Mobile
Email
DirectFax
Position
Department
LoginID
 
test:
null pWebUserID
invalid pWebUserID
valid pWebUserID without web login
valid pWebUserID with web login
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client'; 
select * from NetContactList(null);
expect EOF;
select * from NetContactList('zz');
expect EOF;
select employmentid,companyname,personname,replace(directphone,' ','!') as DT,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as DF,Position,LoginID from NetContactList('test.candidate');
expect EOF;
select employmentid,companyname,personname,replace(directphone,' ','!') as DT,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as DF,Position,LoginID from NetContactList('test.client') where LoginID='test.user.client';
expect employmentid=TEST,companyname=test,personname=test,Position=TestPosition,[email protected];
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetContactList" IS 
{CREATE PROCEDURE pears."NetContactList"(IN pWebUserID CHAR(20))
RESULT(EmploymentID CHAR(20),CompanyName CHAR(100),PersonName CHAR(100),DirectPhone CHAR(250),Mobile CHAR(250),Email CHAR(250),DirectFax CHAR(250),POSITION CHAR(100),Department CHAR(100),LoginID CHAR(100))
//IQXWeb
BEGIN
  SELECT employment.employmentid,
    company.name AS companyname,
    person.name AS personname,
    getphone('CP','Direct Telephone',employment.employmentid),
    getphone('CP','Mobile',employment.employmentid),
    getphone('CP','E-mail',employment.employmentid),
    getphone('CP','Direct Fax',employment.employmentid),
    employment.position,
    employment.department,
    iqxnetuser.loginid
    FROM employment KEY JOIN(company,person)
    ,employment KEY JOIN iqxnetuserlink KEY JOIN iqxnetuser
    WHERE employment.companyid = any(SELECT companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID) ORDER BY
    person.keyname ASC,company.keyname ASC
END
 
/* DOC
2016-08-25 PC doc and test
 
inputs:
    pWebUserID
 
outputs:
    EmploymentID
    CompanyName
    PersonName
    DirectPhone
    Mobile
    Email
    DirectFax
    Position
    Department
    LoginID
 
test:
    null pWebUserID
    invalid pWebUserID
    valid pWebUserID without web login
    valid pWebUserID with web login
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client'; 
select * from NetContactList(null);
expect EOF;
select * from NetContactList('zz');
expect EOF;
select employmentid,companyname,personname,replace(directphone,' ','!') as DT,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as DF,Position,LoginID from NetContactList('test.candidate');
expect EOF;
select employmentid,companyname,personname,replace(directphone,' ','!') as DT,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as DF,Position,LoginID from NetContactList('test.client') where LoginID='test.user.client';
expect employmentid=TEST,companyname=test,personname=test,Position=TestPosition,[email protected];
 
*/
}