====== pears.NetOwnerCandidateSearch ======
<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"."NetOwnerCandidateSearch"( in "pWebUserID" char(20),in "SearchValue" char(100) default '%',in "SearchState" char(20) default null,in "pSlice" integer default 1,in "pSliceSize" integer default 20 ) 
result( "PersonID" char(20),"Name" char(61),"Address" char(100),"Status" char(50),"Alert" char(250),"IsFavourite" integer ) 
// IQXWeb
begin
  declare "x" integer;
  declare "y" integer;
  declare "pStaffID" char(20);
  set "pStaffID" = (select first "StaffID" from "iqxnetuser" where "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  set "x" = "pSliceSize";
  set "y" = "x"*"pSlice"+1;
  set "SearchValue" = "trim"("replace"("isnull"("SearchValue",''),'*',''));
  if "trim"("isnull"("SearchState",'')) = '' then
    set "SearchState" = '%'
  end if;
  select top "x" start at "y"
    "p"."personid" as "PersonID",
    "p"."forenames"+' '+"p"."surname" as "Name",
    "p"."town" as "Address",
    "status"."name" as "PersStatus",
    "p"."alert" as "Alert",
    (select "count"() from "favourites" where "type" = 'P' and "ID" = "p"."personid" and "Staffid" = "pStaffID") as "IsFavourite"
    from "person" as "p" join "status" on "status"."status" = "p"."status" and "type" = 'P'
    where "isnull"("p"."divisionid",'') = any(select "DivisionID" from "DashboardStaffDivisions"())
    and "p"."status" like "SearchState"
    and "p"."status" <> '.'
    and "status"."publishtoweb" = 1
    and "SearchValue" <> ''
    and "p"."keyname" like "SearchValue"+'%'
    order by "KeyName" asc
end /* DOC
2016-09-19 PC alter routine to allow search, and paging
2017-04-10 PC alter to allow all categories
2018-05-16 GJ Updated to use MHS function to obey divisions & tidied up test

tests:
null pWebUserID
invalid pWebUserID
null SearchValue and pSearchCategory
check isFavourite
*/
/* TEST

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCandidateSearch" IS 
{create PROCEDURE pears."NetOwnerCandidateSearch"( in "pWebUserID" char(20),in "SearchValue" char(100) default '%',in SearchState char(20) default null,in pSlice integer default 1, in pSliceSize integer default 20 ) 
result( "PersonID" char(20),"Name" char(61),"Address" char(100),"Status" char(50),Alert char(250),"IsFavourite" integer ) 
// IQXWeb
begin
declare x integer;
declare y integer;
declare pStaffID char(20);

set pStaffID = (select first StaffID from iqxnetuser where iqxnetuser.iqxnetuserid = pWebUserID);
set x=pSliceSize;
set y=x*pSlice+1;
set SearchValue = trim(replace(isnull(SearchValue,''),'*',''));

if trim(isnull(SearchState,''))='' then
  set SearchState='%';
end if;

select top x start at y
  p.personid as PersonID,
  p.forenames + ' ' + p.surname as Name,
  p.town as Address,
  status.name as PersStatus,
  p.alert as Alert,
  (select count(*) from favourites where "type"='P' and ID=p.personid and Staffid=pStaffID) as IsFavourite
  from person p join status on status.status=p.status and type='P'
  where 
    isnull(p.divisionid,'') in (select DivisionID from DashboardStaffDivisions())
    and p.status like SearchState
    and p.status <> '.'
    and status.publishtoweb = 1
    and SearchValue <> ''
    and p.keyname like SearchValue + '%'
  order by KeyName asc
end

/* DOC
2016-09-19 PC alter routine to allow search, and paging
2017-04-10 PC alter to allow all categories
2018-05-16 GJ Updated to use MHS function to obey divisions & tidied up test

tests:
    null pWebUserID
    invalid pWebUserID
    null SearchValue and pSearchCategory
    check isFavourite
*/

/* TEST

*/
}
</code>