====== pears.NetOwnerActionDialog ======
<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"."NetOwnerActionDialog"( in "pWebUserID" char(20),in "pObjectID" char(20) default null ) 
result( "number" char(200),"PhoneCapabilities" char(20),"PhoneType" char(100) ) 
begin
  declare "NodeAddress" long varchar;
  set "NodeAddress" = (select top 1 "addr1"+(if "isnull"("Addr2",'') <> '' then ', '+"Addr2" endif)+(if "isnull"("Addr3",'') <> '' then ', '+"Addr3" endif)+(if "isnull"("Town",'') <> '' then ', '+"Town" endif)+(if "isnull"("postcode",'') <> '' then ', '+"postcode" endif) from "person" where "personid" = "pObjectID");
  if "isnull"("NodeAddress",'') = '' then
    set "NodeAddress" = (select top 1 "addr1"+(if "isnull"("Addr2",'') <> '' then ', '+"Addr2" endif)+(if "isnull"("Addr3",'') <> '' then ', '+"Addr3" endif)+(if "isnull"("Town",'') <> '' then ', '+"Town" endif)+(if "isnull"("postcode",'') <> '' then ', '+"postcode" endif) from "company" key join "employment" where "employmentid" = "pObjectID")
  end if;
  if "isnull"("NodeAddress",'') = '' then
    set "NodeAddress" = (select top 1 "addr1"+(if "isnull"("Addr2",'') <> '' then ', '+"Addr2" endif)+(if "isnull"("Addr3",'') <> '' then ', '+"Addr3" endif)+(if "isnull"("Town",'') <> '' then ', '+"Town" endif)+(if "isnull"("postcode",'') <> '' then ', '+"postcode" endif) from "company" where "companyid" = "pObjectID")
  end if;
  select distinct "p"."number",
    (select "list"("t"."capabilities" order by "t"."capabilities" asc) as "PhoneCapabilities" from "phone" as "ph" key join "phonetype" as "t" where "ph"."whoid" = "p"."whoid" and "ph"."number" = "p"."number"),
    (select "list"("t"."name" order by "t"."name" asc) as "PhoneType" from "phone" as "ph" key join "phonetype" as "t" where "ph"."whoid" = "p"."whoid" and "ph"."number" = "p"."number")
    from "phone" as "p"
    where "whoid" = "pObjectID" union
  select top 1(if "isnull"("NodeAddress",'') <> '' then "NodeAddress" endif),(if "isnull"("NodeAddress",'') <> '' then 'A' endif),(if "isnull"("NodeAddress",'') <> '' then 'Address' endif)
end /* DOC
2016-08-20 PC

test:
select single entry phone
select double entry phone
select email
select person address
select company address
select contact address

*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null,personid=null where IQXNetUserLinkID in ('test.agency','test.client','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST',personid='TEST' where IQXNetUserLinkID='test.candidate';
update person set addr1='TestAddr1',addr2='TestAddr2',town='TestTown',postcode='XX9 9XX' where personid='TEST';
select replace(replace(number,' ','!'),',','~') as A,replace(PhoneCapabilities,' ','!') as B,replace(PhoneType,' ','!') as C from NetOwnerActionDialog('test.owner','test');
expect A=0499!999!995,B=D,C=Direct!Telephone;
expect A=0999!999!994,B=DS,C=Mobile;
expect A=test70@test.com,B=E,C=E-mail;
expect A=0999!999!993,B=F,C=Direct!Fax;
expect A=TestAddr1~!TestAddr2~!TestTown~!XX9!9XX,B=A,C=Address;
Expect EOF;
update person set addr1=null,addr2=null,town=null,postcode=null where personid='TEST';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerActionDialog" IS 
{create PROCEDURE pears."NetOwnerActionDialog"(in pWebUserID char(20),in pObjectID char(20) default null)
RESULT(number char(200),PhoneCapabilities char(20),PhoneType char(100))
BEGIN
    declare NodeAddress long varchar;
    set NodeAddress=(select top 1 addr1+(if isnull(Addr2,'')!='' then ', '+Addr2 endif)+(if isnull(Addr3,'')!='' then ', '+Addr3 endif)+(if isnull(Town,'')!='' then ', '+Town endif)+(if isnull(postcode,'')!='' then ', '+postcode endif) from person where personid=pObjectID);
    if isnull(NodeAddress,'')='' then 
        set NodeAddress=(select top 1 addr1+(if isnull(Addr2,'')!='' then ', '+Addr2 endif)+(if isnull(Addr3,'')!='' then ', '+Addr3 endif)+(if isnull(Town,'')!='' then ', '+Town endif)+(if isnull(postcode,'')!='' then ', '+postcode endif) from company key join employment where employmentid=pObjectID);
    end if;
    if isnull(NodeAddress,'')='' then 
        set NodeAddress=(select top 1 addr1+(if isnull(Addr2,'')!='' then ', '+Addr2 endif)+(if isnull(Addr3,'')!='' then ', '+Addr3 endif)+(if isnull(Town,'')!='' then ', '+Town endif)+(if isnull(postcode,'')!='' then ', '+postcode endif) from company where companyid=pObjectID);
    end if;

	select distinct p.number, 
(select list(t.capabilities order by t.capabilities) as PhoneCapabilities from phone ph key join phonetype t  where ph.whoid=p.whoid and ph.number=p.number),
(select list(t.name order by t.name) as PhoneType from phone ph key join phonetype t  where ph.whoid=p.whoid and ph.number=p.number)
from phone p
where whoid=pObjectID
UNION 
select top 1 (if isnull(NodeAddress,'')!='' then NodeAddress endif),(if isnull(NodeAddress,'')!='' then 'A' endif),(if isnull(NodeAddress,'')!='' then 'Address' endif)

END

/* DOC
2016-08-20 PC

test:
    select single entry phone
    select double entry phone
    select email
    select person address
    select company address
    select contact address

*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null,personid=null where IQXNetUserLinkID in ('test.agency','test.client','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST',personid='TEST' where IQXNetUserLinkID='test.candidate';
update person set addr1='TestAddr1',addr2='TestAddr2',town='TestTown',postcode='XX9 9XX' where personid='TEST';
select replace(replace(number,' ','!'),',','~') as A,replace(PhoneCapabilities,' ','!') as B,replace(PhoneType,' ','!') as C from NetOwnerActionDialog('test.owner','test');
expect A=0499!999!995,B=D,C=Direct!Telephone;
expect A=0999!999!994,B=DS,C=Mobile;
expect A=test70@test.com,B=E,C=E-mail;
expect A=0999!999!993,B=F,C=Direct!Fax;
expect A=TestAddr1~!TestAddr2~!TestTown~!XX9!9XX,B=A,C=Address;
Expect EOF;
update person set addr1=null,addr2=null,town=null,postcode=null where personid='TEST';
*/
}
</code>