pears.NetOwnerContactList

CREATE PROCEDURE "pears"."NetOwnerContactList"( IN "pWebUserID" CHAR(20),IN "pCompanyID" CHAR(20) ) 
RESULT( "Value" CHAR(20),"Descrip" CHAR(100) ) 
BEGIN
  IF NOT EXISTS(SELECT * FROM "company" WHERE "companyid" = "pCompanyID") THEN
    SELECT '0','No Contacts Available';
    RETURN
  END IF;
  SELECT "e"."employmentid",
    "p"."name"
    FROM "employment" AS "e"
      KEY JOIN "person" AS "p"
    WHERE "e"."temp" = 0
    AND "e"."leavedate" IS NULL
    AND "p"."status" IN( 'L','C' ) 
    AND "e"."companyid" = "pCompanyID"
    ORDER BY "p"."keyname" ASC
END /* DOC
2016-08-22 PC Provide a list of contacts for creating a contact event for a Company
2018-09-14 JW Performance improvement for pulse
2018-09-28 IW-686 Changed order by from surname to keyname
 
Inputs:
pCompanyID - company id to create list
 
Outputs:
Value - EmploymentID
Descrip - Person Name
 
Test:
Check with empty CompanyID
check with incorrect CompanyID
Check with Correct CompanyID
*/
/* TEST
call NetTestSetup('');
delete from contactevent where contacteventid like 'TEST%';
insert into contactclass (classcode,classdescrip) on existing update values ('~~','Test Class');
update person set status='L' where personid='TEST';
select * from "NetOwnerContactEventsSet"('test.owner','TEST1','n','~~','test','test','test',dateformat(now(*),'dd/mm/yyyy'),'TestDescription','TestNote',dateformat(dateadd(hh,1,now(*)),'dd/mm/yyyy'),'TestOutcome',1);
expect pResult=0:~Success;
select * from NetOwnerContactList('TEST.OWNER',null);
expect value=0;
select * from NetOwnerContactList('TEST.OWNER','rr');
expect value=0;
select top 1 * from NetOwnerContactList('TEST.OWNER','TEST') where value='test';
expect Value=TEST,Descrip=test;
expect EOF;
update person set status='P' where personid='TEST';
delete from contactevent where contacteventid like 'TEST%';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerContactList" IS 
{CREATE PROCEDURE pears."NetOwnerContactList"(IN pWebUserID CHAR(20), IN pCompanyID CHAR(20))
RESULT(VALUE CHAR(20),Descrip CHAR(100) )
BEGIN
  IF NOT EXISTS (SELECT * FROM company WHERE companyid=pCompanyID) THEN
    SELECT '0','No Contacts Available';
    RETURN;
  END IF;
  SELECT
    e.employmentid,
    p.name
  FROM
    employment e
    KEY JOIN person p
  WHERE
    e.temp =0
    AND e.leavedate IS NULL
    AND p.status IN ('L','C')
    AND e.companyid=pCompanyID
  ORDER BY
    p.keyname ASC
END
 
/* DOC
2016-08-22 PC Provide a list of contacts for creating a contact event for a Company
2018-09-14 JW Performance improvement for pulse
2018-09-28 IW-686 Changed order by from surname to keyname
 
Inputs:
    pCompanyID - company id to create list
 
Outputs:
    Value - EmploymentID
    Descrip - Person Name
 
Test:
    Check with empty CompanyID
    check with incorrect CompanyID
    Check with Correct CompanyID
*/
 
/* TEST
call NetTestSetup('');
delete from contactevent where contacteventid like 'TEST%';
insert into contactclass (classcode,classdescrip) on existing update values ('~~','Test Class');
update person set status='L' where personid='TEST';
select * from "NetOwnerContactEventsSet"('test.owner','TEST1','n','~~','test','test','test',dateformat(now(*),'dd/mm/yyyy'),'TestDescription','TestNote',dateformat(dateadd(hh,1,now(*)),'dd/mm/yyyy'),'TestOutcome',1);
expect pResult=0:~Success;
select * from NetOwnerContactList('TEST.OWNER',null);
expect value=0;
select * from NetOwnerContactList('TEST.OWNER','rr');
expect value=0;
select top 1 * from NetOwnerContactList('TEST.OWNER','TEST') where value='test';
expect Value=TEST,Descrip=test;
expect EOF;
update person set status='P' where personid='TEST';
delete from contactevent where contacteventid like 'TEST%';
*/
}