pears.NetOwnerCandidateSearch

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
 
*/
}