====== pears.NetContactList ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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=test99@test.com;

*/
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=test99@test.com;

*/
}
</code>