pears.NetOwnerActionDialog

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 [email protected],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 [email protected],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';
*/
}