pears.NetOwnerContactSearch

CREATE PROCEDURE "pears"."NetOwnerContactSearch"( IN "pWebUserID" CHAR(20),IN "SearchValue" CHAR(20) DEFAULT '%',IN "SearchState" CHAR(5) DEFAULT NULL,IN "pSlice" INTEGER DEFAULT 1,IN "pSliceSize" INTEGER DEFAULT 20 ) 
RESULT( "EmploymentID" CHAR(20),"Name" CHAR(61),"CompanyName" CHAR(100),"address" CHAR(60),"Jobtitle" CHAR(50),"status" CHAR(50),"Alert" CHAR(255),"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"
    "e"."employmentid" AS "EmploymentID",
    "p"."forenames"+' '+"p"."surname" AS "Name",
    "c"."name" AS "CompanyName",
    "c"."town" AS "address",
    "e"."position" AS "JobTitle",
    "companystatus"."name" AS "status",
    "c"."alert" AS "Alert",
    IF(SELECT "count"() FROM "favourites" WHERE "ID" = "e"."employmentid" AND "Staffid" = "pStaffID") > 0 THEN 1 ELSE 0 endif AS "IsFavourite"
    FROM "person" AS "p" KEY JOIN "employment" AS "e" KEY JOIN "company" AS "c" KEY LEFT OUTER JOIN "companystatus"
    WHERE "isnull"("c"."divisionid",'') = any(SELECT "DivisionID" FROM "DashboardStaffDivisions"())
    AND "c"."status" LIKE "SearchState"
    AND "p"."status" = 'L'
    AND "SearchValue" <> ''
    AND "p"."keyname" LIKE "SearchValue"+'%'
    ORDER BY "p"."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
 
inputs:
pWebUserID
SearchValue
SearchState
pSlice
pSliceSize
 
outputs:
EmploymentID
Name
CompanyName
address
Jobtitle
status
Alert
IsFavourite
 
tests:
null pWebUserID
invalid pWebUserID
null SearchValue and pSearchState
create test category
null SearchValue and test pSearchState
incorrect SearchValue and test pSearchState
create three test divisions
check default division 
check assigned divisions
check all divisions
check isFavourite
 
*/
/* TEST
call NetTestSetup('');
update company set divisionid=null where companyid='test';
update person set keyname='testxxxyyyzzz',status='L' where personid='test';
update staff set divisionid=null,DivisionAccess=0 where staffid='TEST';
delete from divisionaccess where staffid='TEST';
delete from favourites where staffid='TEST';
select * from "NetOwnerContactSearch"( null,null,null,0, 20);
expect EOF;
select * from "NetOwnerContactSearch"( 'z99',null,null,0, 20);
expect EOF;
update staff set divisionid='Test',DivisionAccess=0 where staffid='TEST';
select * from "NetOwnerContactSearch"( 'test.owner',null,null,0, 20);
expect EOF;
update company set divisionid='Test' where companyid='test';
select * from "NetOwnerContactSearch"( 'test.owner',null,null,0, 20);
expect EOF;
select * from "NetOwnerContactSearch"( 'test.owner','testxxxyyyzzz',null,0, 20);
expect EmploymentID=TEST,IsFavourite=0;
insert into favourites (favouritesid, parent, id, staffid, description, type) on existing update defaults off values ('TEST',null,'TEST','TEST','Test Fav','C');
select * from "NetOwnerContactSearch"( 'test.owner','testxxxyyyzzz',null,0, 20);
expect EmploymentID=TEST,IsFavourite=1;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerContactSearch" IS 
{CREATE PROCEDURE pears."NetOwnerContactSearch"( IN "pWebUserID" CHAR(20),IN "SearchValue" CHAR(20) DEFAULT '%',IN SearchState CHAR(5) DEFAULT NULL,IN pSlice INTEGER DEFAULT 1, IN pSliceSize INTEGER DEFAULT 20 ) 
RESULT( "EmploymentID" CHAR(20),"Name" CHAR(61),"CompanyName" CHAR(100),address CHAR(60),Jobtitle CHAR(50),"status" CHAR(50),Alert CHAR(255),"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
  e.employmentid AS EmploymentID,
  p.forenames + ' ' + p.surname AS Name,
  c.name AS CompanyName,
  c.town AS address,
  e.position AS JobTitle,
  companystatus.name AS STATUS,
  c.alert AS Alert,
  IF (SELECT COUNT(*) FROM favourites WHERE ID=e.employmentid AND Staffid=pStaffID) >0 THEN 1 ELSE 0 endif AS IsFavourite
  FROM person p KEY JOIN employment e KEY JOIN company c KEY LEFT OUTER JOIN companystatus 
  WHERE 
    isnull(c.divisionid,'') IN (SELECT DivisionID FROM DashboardStaffDivisions())
    AND c.status LIKE SearchState
    AND p.status = 'L'
    AND SearchValue <> ''
    AND p.keyname LIKE SearchValue+'%'
  ORDER BY p.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
 
inputs:
    pWebUserID
    SearchValue
    SearchState
    pSlice
    pSliceSize
 
outputs:
    EmploymentID
    Name
    CompanyName
    address
    Jobtitle
    status
    Alert
    IsFavourite
 
tests:
    null pWebUserID
    invalid pWebUserID
    null SearchValue and pSearchState
    create test category
    null SearchValue and test pSearchState
    incorrect SearchValue and test pSearchState
    create three test divisions
    check default division 
    check assigned divisions
    check all divisions
    check isFavourite
 
*/
 
/* TEST
call NetTestSetup('');
update company set divisionid=null where companyid='test';
update person set keyname='testxxxyyyzzz',status='L' where personid='test';
update staff set divisionid=null,DivisionAccess=0 where staffid='TEST';
delete from divisionaccess where staffid='TEST';
delete from favourites where staffid='TEST';
select * from "NetOwnerContactSearch"( null,null,null,0, 20);
expect EOF;
select * from "NetOwnerContactSearch"( 'z99',null,null,0, 20);
expect EOF;
update staff set divisionid='Test',DivisionAccess=0 where staffid='TEST';
select * from "NetOwnerContactSearch"( 'test.owner',null,null,0, 20);
expect EOF;
update company set divisionid='Test' where companyid='test';
select * from "NetOwnerContactSearch"( 'test.owner',null,null,0, 20);
expect EOF;
select * from "NetOwnerContactSearch"( 'test.owner','testxxxyyyzzz',null,0, 20);
expect EmploymentID=TEST,IsFavourite=0;
insert into favourites (favouritesid, parent, id, staffid, description, type) on existing update defaults off values ('TEST',null,'TEST','TEST','Test Fav','C');
select * from "NetOwnerContactSearch"( 'test.owner','testxxxyyyzzz',null,0, 20);
expect EmploymentID=TEST,IsFavourite=1;
*/
}