====== pears.NetOwnerContactEvents ======
<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"."NetOwnerContactEvents"( in "pWebUserID" char(20),
  in "pSlice" integer default 0,
  in "pSliceSize" integer default 20,
  in "pStaffID" char(20) default null,
  in "pEmploymentID" char(20) default null,
  in "pPersonID" char(20) default null,
  in "pVacancyID" char(20) default null,
  in "pCompanyID" char(20) default null,
  in "pAllEvents" char(1) default null,
  in "pFilterDescription" char(100) default '%',
  in "pFilterTimeStart" date,
  in "pFilterTimeEnd" date,
  in "pFilterClassType" char(50) default '%',
  in "pFilterPriority" char(1) default '%',
  in "pFilterOutcome" long varchar default '%',
  in "pFilterNotes" long varchar default '%',
  in "pFilterPersonName" char(50) default '%',
  in "pFilterContactName" char(60) default '%',
  in "pFilterStaffName" char(60) default '%',
  in "pFilterPosition" char(60) default '%',
  in "pFilterCompanyName" char(60) default '%',
  in "pOrderBy" char(255) default '' ) 
result( 
  "ContactEventID" char(20),
  "Description" char(100),
  "ContactEventTime" "datetime",
  "ClassType" char(50),
  "Priority" char(1),
  "Outcome" long varchar,
  "Notes" long varchar,
  "PersonName" char(50),
  "ContactName" char(60),
  "StaffName" char(60),
  "Position" char(60),
  "CompanyName" char(60) ) 
begin
  // IQXNet
  declare "x" integer;
  declare "y" integer;
  set "x" = "pSliceSize";
  set "y" = "x"*"pSlice"+1;
  case when "pCompanyID" is null and "pPersonID" is null and "pEmploymentID" is null and "pVacancyID" is null then return
  when "pPersonID" is not null then /* Contact events for a Person record*/
    select top "x" start at "y"
      "ce"."contacteventid",
      "ce"."description",
      "datetime"("ce"."contactdate"+"ce"."contacttime") as "ContactEventTime",
      "c"."classdescrip" as "ClassType",
      "isnull"("ce"."priority",1) as "Priority",
      "ce"."outcome",
      "ce"."notes" as "Notes",
      "p"."name" as "PersonName",
      "cp"."name" as "ContactName",
      "s"."name" as "StaffName",
      "v"."position" as "Position",
      "co"."name" as "CompanyName"
      from "contactevent" as "ce" key join "staff" as "s"
        ,"contactevent" as "ce" key join "contactclass" as "c"
        ,"contactevent" as "ce" key join "person" as "p"
        ,"contactevent" as "ce" key left outer join("employment" as "e" key join("company" as "co","person" as "cp"))
        ,"contactevent" as "ce" key left outer join("vacancy" as "v")
        ,"contactevent" as "ce" left outer join(select "DivisionID" from "DashboardStaffDivisions"()) as "d" on("ce"."DivisionID" is null or "ce"."DivisionID" = "d"."DivisionID")
      where "ce"."personid" = "pPersonID"
      and((select "u"."IQXNetUserID" from "IQXNetUser" as "u" where "u"."StaffID" = "ce"."StaffID") = "pWebUserID" or "pAllEvents" = 1)
      order by "ce"."contactdate" desc,
      "ce"."contacttime" desc
  when "pEmploymentID" is not null then /* Contact events for a Contact record*/
    select top "x" start at "y"
      "ce"."contacteventid",
      "ce"."description",
      "datetime"("ce"."contactdate"+"ce"."contacttime") as "ContactEventTime",
      "c"."classdescrip" as "ClassType",
      "isnull"("ce"."priority",1) as "Priority",
      "ce"."outcome",
      "ce"."notes" as "Notes",
      "p"."name" as "PersonName",
      "cp"."name" as "ContactName",
      "s"."name" as "StaffName",
      "v"."position" as "Position",
      "co"."name" as "CompanyName"
      from "contactevent" as "ce" key join "staff" as "s"
        ,"contactevent" as "ce" key join "contactclass" as "c"
        ,"contactevent" as "ce" key left outer join "person" as "p"
        ,"contactevent" as "ce" key join("employment" as "e" key join("company" as "co","person" as "cp"))
        ,"contactevent" as "ce" key left outer join("vacancy" as "v")
        ,"contactevent" as "ce" left outer join(select "DivisionID" from "DashboardStaffDivisions"()) as "d" on("ce"."DivisionID" is null or "ce"."DivisionID" = "d"."DivisionID")
      where "ce"."EmploymentID" = "pEmploymentID"
      and((select "u"."IQXNetUserID" from "IQXNetUser" as "u" where "u"."StaffID" = "ce"."StaffID") = "pWebUserID" or "pAllEvents" = 1)
      order by "ce"."contactdate" desc,
      "ce"."contacttime" desc
  when "pEmploymentID" is null and "pCompanyID" is not null then /* Contact events for a Company record*/
    select top "x" start at "y"
      "ce"."contacteventid",
      "ce"."description",
      "datetime"("ce"."contactdate"+"ce"."contacttime") as "ContactEventTime",
      "c"."classdescrip" as "ClassType",
      "isnull"("ce"."priority",1) as "Priority",
      "ce"."outcome",
      "ce"."notes" as "Notes",
      "p"."name" as "PersonName",
      "cp"."name" as "ContactName",
      "s"."name" as "StaffName",
      "v"."position" as "Position",
      "co"."name" as "CompanyName"
      from "contactevent" as "ce" key join "staff" as "s"
        ,"contactevent" as "ce" key join "contactclass" as "c"
        ,"contactevent" as "ce" key left outer join "person" as "p"
        ,"contactevent" as "ce" key join("employment" as "e" key join("company" as "co","person" as "cp"))
        ,"contactevent" as "ce" key left outer join("vacancy" as "v")
        ,"contactevent" as "ce" left outer join(select "DivisionID" from "DashboardStaffDivisions"()) as "d" on("ce"."DivisionID" is null or "ce"."DivisionID" = "d"."DivisionID")
      where "e"."CompanyID" = "pCompanyID"
      and((select "u"."IQXNetUserID" from "IQXNetUser" as "u" where "u"."StaffID" = "ce"."StaffID") = "pWebUserID" or "pAllEvents" = 1)
      order by "ce"."contactdate" desc,
      "ce"."contacttime" desc
  when "pVacancyID" is not null then /* Contact events for a Vacancy record*/
    select top "x" start at "y"
      "ce"."contacteventid",
      "ce"."description",
      "datetime"("ce"."contactdate"+"ce"."contacttime") as "ContactEventTime",
      "c"."classdescrip" as "ClassType",
      "isnull"("ce"."priority",1) as "Priority",
      "ce"."outcome",
      "ce"."notes" as "Notes",
      "p"."name" as "PersonName",
      "cp"."name" as "ContactName",
      "s"."name" as "StaffName",
      "v"."position" as "Position",
      "co"."name" as "CompanyName"
      from "contactevent" as "ce" key join "staff" as "s"
        ,"contactevent" as "ce" key join "contactclass" as "c"
        ,"contactevent" as "ce" key left outer join "person" as "p"
        ,"contactevent" as "ce" key left outer join("employment" as "e" key join("company" as "co","person" as "cp"))
        ,"contactevent" as "ce" key join("vacancy" as "v")
        ,"contactevent" as "ce" left outer join(select "DivisionID" from "DashboardStaffDivisions"()) as "d" on("ce"."DivisionID" is null or "ce"."DivisionID" = "d"."DivisionID")
      where "e"."VacancyID" = "pVacancyID"
      and((select "u"."IQXNetUserID" from "IQXNetUser" as "u" where "u"."StaffID" = "ce"."StaffID") = "pWebUserID" or "pAllEvents" = 1)
      order by "ce"."contactdate" desc,
      "ce"."contacttime" desc
  end case
end /* TEST
call NetTestSetup('');
delete from contactevent where contacteventid in ('Test1','Test2','Test3');
delete from vacancy where vacancyid = 'TEST2';
delete from employment where employmentid = 'TEST2';

update staff set name='TestStaff' where staffid='TEST';
insert into person (personid,name,keyname,forenames,surname) on existing update defaults off values ('TEST2','test2','TEST2','test2','test2');
insert into person (personid,name,keyname,forenames,surname) on existing update defaults off values ('TEST3','test3','TEST3','test3','test3');
insert into employment (employmentid,companyid,personid) on existing update defaults off values ('TEST','TEST','TEST');
insert into employment (employmentid,companyid,personid) on existing update defaults off values ('TEST2','TEST','TEST2');
insert into vacancy (vacancyid,departmentid,employmentid,tempdeskid,position) on existing update defaults off values ('TEST','~~','TEST','TEST','test');
insert into vacancy (vacancyid,departmentid,employmentid,tempdeskid,position) on existing update defaults off values ('TEST2','~~','TEST','TEST','test2');
insert into contactevent (contacteventid,staffid,personid,vacancyid,employmentid,contactdate,contacttime,description,outcome,classcode,who,notes,whenentered,whoentered,priority) on existing update values ('TEST1','TEST','TEST','TEST','TEST',now(*),now(*),'TestEntry','TestEntryOutcome','~~','TEST','TestEntryNotes',now(*),'TEST.OWNER','1');
insert into contactevent (contacteventid,staffid,personid,vacancyid,employmentid,contactdate,contacttime,description,outcome,classcode,who,notes,whenentered,whoentered,priority) on existing update values ('TEST2','TEST','TEST','TEST','TEST2',now(*),dateadd(mi,-10,now(*)),'TestEntry2','TestEntryOutcome2','~~','TEST','TestEntryNotes2',now(*),'TEST.OWNER','5');
insert into contactevent (contacteventid,staffid,personid,vacancyid,employmentid,contactdate,contacttime,description,outcome,classcode,who,notes,whenentered,whoentered,priority) on existing update values ('TEST3','TEST','TEST3','TEST2','TEST2',now(*),dateadd(mi,-10,now(*)),'TestEntry3','TestEntryOutcome3','~~','TEST','TestEntryNotes3',now(*),'TEST.OWNER','5');

select * from NetOwnerContactEvents('TEST.OWNER',0,50,null,null,'test',null,null,null,'testentry',today(*),today(*),'Test','%','TestEntryOutcome','TestEntryNotes','test','test','TestStaff','test','test','description asc');
expect ContactEventID=TEST1,ClassType=Test*,StaffName=TestStaff,ContactName=test,Description=TestEntry;
delete from contactevent where contacteventid in ('Test1','Test2','Test3');
delete from vacancy where vacancyid = 'TEST2';
delete from employment where employmentid = 'TEST2';

*/
/* DOC
2016-07-25 PC Provide master list for Owner diary
2016-08-11 PC pCompanyID parameter and pAllEvents parameter added
2016-08-22 GJ pSlice and pSliceSize added
2016-09-26 PC change sort order to ignore priority, and order on contact date first, with the most recent at the top.
2017-04-24 ET Changed filter to include events on the start and end dates.
2018-09-14 JW Performance improvement for pulse

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerContactEvents" IS 
{create PROCEDURE pears."NetOwnerContactEvents"(in pWebUserID char(20),
    in pSlice integer default 0,
    in pSliceSize integer default 20,
    in pStaffID char(20) default null,
    in pEmploymentID char(20) default null,
    in pPersonID char(20) default null,
    in pVacancyID char(20) default null,
    in pCompanyID char(20) default null,
    in pAllEvents char(1) default null,
	in pFilterDescription char(100) default '%',
    in pFilterTimeStart date,
    in pFilterTimeEnd date,
    in pFilterClassType char(50) default '%',
    in pFilterPriority char(1) default '%',
    in pFilterOutcome long varchar default '%',
    in pFilterNotes long varchar default '%',
    in pFilterPersonName char(50) default '%',
    in pFilterContactName char(60) default '%',
    in pFilterStaffName char(60) default '%',
    in pFilterPosition char(60) default '%',
    in pFilterCompanyName char(60) default '%',
    in pOrderBy char(255) default ''
    )

RESULT(ContactEventID char(20),
    Description char(100),
    ContactEventTime datetime,
    ClassType char(50),
    Priority char(1),
    Outcome long varchar,
    Notes long varchar,
    PersonName char(50),
    ContactName char(60),
    StaffName char(60),
    Position char(60),
    CompanyName char(60)
    )

BEGIN
// IQXNet
  declare x integer;
  declare y integer;
  set x=pSliceSize;
  set y=x*pSlice+1;
case when pCompanyID is null and pPersonID is null and pEmploymentID is null and pVacancyID is null then return

     when pPersonID is not null then /* Contact events for a Person record*/
		  select top x start at y 
			ce.contacteventid,
			ce.description,
			datetime(ce.contactdate + ce.contacttime) as ContactEventTime,
			c.classdescrip as ClassType,
			isnull(ce.priority,1) as Priority,
			ce.outcome, 
			ce.notes as "Notes",
			p.name as PersonName,
			cp.name as ContactName,
			s.name as StaffName,
			v.position as Position,
			co.name as CompanyName
		  from 
			contactevent ce key join staff s,
			contactevent ce key join contactclass c,
			contactevent ce key join person p,
			contactevent ce key left outer join (employment e key join (company co, person cp)),
			contactevent ce key left outer join (vacancy v),
			contactevent ce left join (select DivisionID from DashboardStaffDivisions()) as d on (ce.DivisionID is null or ce.DivisionID = d.DivisionID)
		  where
		   ce.personid = pPersonID and 
		   ((select u.IQXNetUserID from IQXNetUser u where u.StaffID = ce.StaffID) = pWebUserID or pAllEvents=1 )
		  order by
			ce.contactdate desc,
			ce.contacttime desc
			
     when pEmploymentID is not null then /* Contact events for a Contact record*/
		  select top x start at y 
			ce.contacteventid,
			ce.description,
			datetime(ce.contactdate + ce.contacttime) as ContactEventTime,
			c.classdescrip as ClassType,
			isnull(ce.priority,1) as Priority,
			ce.outcome, 
			ce.notes as "Notes",
			p.name as PersonName,
			cp.name as ContactName,
			s.name as StaffName,
			v.position as Position,
			co.name as CompanyName
		  from 
			contactevent ce key join staff s,
			contactevent ce key join contactclass c,
			contactevent ce key left outer join person p,
			contactevent ce key join (employment e key join (company co, person cp)),
			contactevent ce key left outer join (vacancy v),
			contactevent ce left join (select DivisionID from DashboardStaffDivisions()) as d on (ce.DivisionID is null or ce.DivisionID = d.DivisionID)
		  where
		   ce.EmploymentID = pEmploymentID and 
		   ((select u.IQXNetUserID from IQXNetUser u where u.StaffID = ce.StaffID) = pWebUserID or pAllEvents=1 )
		  order by
			ce.contactdate desc,
			ce.contacttime desc		
			
     when pEmploymentID is null and pCompanyID is not null then /* Contact events for a Company record*/
		  select top x start at y 
			ce.contacteventid,
			ce.description,
			datetime(ce.contactdate + ce.contacttime) as ContactEventTime,
			c.classdescrip as ClassType,
			isnull(ce.priority,1) as Priority,
			ce.outcome, 
			ce.notes as "Notes",
			p.name as PersonName,
			cp.name as ContactName,
			s.name as StaffName,
			v.position as Position,
			co.name as CompanyName
		  from 
			contactevent ce key join staff s,
			contactevent ce key join contactclass c,
			contactevent ce key left outer join person p,
			contactevent ce key join (employment e key join (company co, person cp)),
			contactevent ce key left outer join (vacancy v),
			contactevent ce left join (select DivisionID from DashboardStaffDivisions()) as d on (ce.DivisionID is null or ce.DivisionID = d.DivisionID)
		  where
		   e.CompanyID = pCompanyID and 
		   ((select u.IQXNetUserID from IQXNetUser u where u.StaffID = ce.StaffID) = pWebUserID or pAllEvents=1 )
		  order by
			ce.contactdate desc,
			ce.contacttime desc	
			
     when pVacancyID is not null then /* Contact events for a Vacancy record*/
		  select top x start at y 
			ce.contacteventid,
			ce.description,
			datetime(ce.contactdate + ce.contacttime) as ContactEventTime,
			c.classdescrip as ClassType,
			isnull(ce.priority,1) as Priority,
			ce.outcome, 
			ce.notes as "Notes",
			p.name as PersonName,
			cp.name as ContactName,
			s.name as StaffName,
			v.position as Position,
			co.name as CompanyName
		  from 
			contactevent ce key join staff s,
			contactevent ce key join contactclass c,
			contactevent ce key left outer join person p,
			contactevent ce key left outer join (employment e key join (company co, person cp)),
			contactevent ce key join (vacancy v),
			contactevent ce left join (select DivisionID from DashboardStaffDivisions()) as d on (ce.DivisionID is null or ce.DivisionID = d.DivisionID)
		  where
		   e.VacancyID = pVacancyID and 
		   ((select u.IQXNetUserID from IQXNetUser u where u.StaffID = ce.StaffID) = pWebUserID or pAllEvents=1 )
		  order by
			ce.contactdate desc,
			ce.contacttime desc				
end case;
			
END

/* TEST
call NetTestSetup('');
delete from contactevent where contacteventid in ('Test1','Test2','Test3');
delete from vacancy where vacancyid = 'TEST2';
delete from employment where employmentid = 'TEST2';

update staff set name='TestStaff' where staffid='TEST';
insert into person (personid,name,keyname,forenames,surname) on existing update defaults off values ('TEST2','test2','TEST2','test2','test2');
insert into person (personid,name,keyname,forenames,surname) on existing update defaults off values ('TEST3','test3','TEST3','test3','test3');
insert into employment (employmentid,companyid,personid) on existing update defaults off values ('TEST','TEST','TEST');
insert into employment (employmentid,companyid,personid) on existing update defaults off values ('TEST2','TEST','TEST2');
insert into vacancy (vacancyid,departmentid,employmentid,tempdeskid,position) on existing update defaults off values ('TEST','~~','TEST','TEST','test');
insert into vacancy (vacancyid,departmentid,employmentid,tempdeskid,position) on existing update defaults off values ('TEST2','~~','TEST','TEST','test2');
insert into contactevent (contacteventid,staffid,personid,vacancyid,employmentid,contactdate,contacttime,description,outcome,classcode,who,notes,whenentered,whoentered,priority) on existing update values ('TEST1','TEST','TEST','TEST','TEST',now(*),now(*),'TestEntry','TestEntryOutcome','~~','TEST','TestEntryNotes',now(*),'TEST.OWNER','1');
insert into contactevent (contacteventid,staffid,personid,vacancyid,employmentid,contactdate,contacttime,description,outcome,classcode,who,notes,whenentered,whoentered,priority) on existing update values ('TEST2','TEST','TEST','TEST','TEST2',now(*),dateadd(mi,-10,now(*)),'TestEntry2','TestEntryOutcome2','~~','TEST','TestEntryNotes2',now(*),'TEST.OWNER','5');
insert into contactevent (contacteventid,staffid,personid,vacancyid,employmentid,contactdate,contacttime,description,outcome,classcode,who,notes,whenentered,whoentered,priority) on existing update values ('TEST3','TEST','TEST3','TEST2','TEST2',now(*),dateadd(mi,-10,now(*)),'TestEntry3','TestEntryOutcome3','~~','TEST','TestEntryNotes3',now(*),'TEST.OWNER','5');

select * from NetOwnerContactEvents('TEST.OWNER',0,50,null,null,'test',null,null,null,'testentry',today(*),today(*),'Test','%','TestEntryOutcome','TestEntryNotes','test','test','TestStaff','test','test','description asc');
expect ContactEventID=TEST1,ClassType=Test*,StaffName=TestStaff,ContactName=test,Description=TestEntry;
delete from contactevent where contacteventid in ('Test1','Test2','Test3');
delete from vacancy where vacancyid = 'TEST2';
delete from employment where employmentid = 'TEST2';

*/

/* DOC
2016-07-25 PC Provide master list for Owner diary
2016-08-11 PC pCompanyID parameter and pAllEvents parameter added
2016-08-22 GJ pSlice and pSliceSize added
2016-09-26 PC change sort order to ignore priority, and order on contact date first, with the most recent at the top.
2017-04-24 ET Changed filter to include events on the start and end dates.
2018-09-14 JW Performance improvement for pulse

*/
}
</code>