pears.NetOwnerCompanySearch

CREATE PROCEDURE "pears"."NetOwnerCompanySearch"( 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( "CompanyID" CHAR(20),"Name" CHAR(61),"Address" CHAR(100),"Status" CHAR(20),"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"
    "c"."companyid" AS "CompanyID",
    "c"."name" AS "Name",
    "c"."town" AS "Address",
    "companystatus"."name" AS "CompStatus",
    "c"."alert" AS "Alert",
    (SELECT "count"() FROM "favourites" WHERE "type" = 'C' AND "ID" = "c"."companyid" AND "Staffid" = "pStaffID") AS "IsFavourite"
    FROM "company" AS "c" KEY LEFT OUTER JOIN "companystatus"
    WHERE "isnull"("c"."divisionid",'') = any(SELECT "DivisionID" FROM "DashboardStaffDivisions"())
    AND "c"."status" LIKE "SearchState"
    AND "c"."status" <> '.'
    AND "SearchValue" <> ''
    AND "c"."keyname" LIKE "SearchValue"+'%'
    ORDER BY "c"."KeyName" ASC
END /* DOC
2016-09-19 PC alter routine to allow search, and paging
2018-05-16 GJ Updated to use MHS function to obey divisions & tidied up test
 
inputs:
pWebUserID
SearchValue
SearchCategory
pSlice
pSliceSize
 
outputs:
CompanyID
Name
address
status
Alert
IsFavourite
 
tests:
null pWebUserID
invalid pWebUserID
null SearchValue and pSearchCategory
create test category
null SearchValue and test pSearchCategory
incorrect SearchValue and test pSearchCategory
create three test divisions
check default division 
check assigned divisions
check all divisions
check isFavourite
 
*/
/* TEST
call NetTestSetup('');
update company set divisionid=null, keyname='testxxxyyyzzz' where companyid='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 "NetOwnerCompanySearch"( null,null,null,0, 20);
expect EOF;
select * from "NetOwnerCompanySearch"( 'z99',null,null,0, 20);
expect EOF;
update staff set divisionid='TEST',DivisionAccess=0 where staffid='TEST';
select * from "NetOwnerCompanySearch"( 'test.owner',null,null,0, 20);
expect EOF;
update company set divisionid='TEST' where companyid='test';
select * from "NetOwnerCompanySearch"( 'test.owner',null,null,0, 20);
expect EOF;
update staff set DivisionAccess=1 where staffid='TEST';
select * from "NetOwnerCompanySearch"( 'test.owner','testxxxyyyzzz','P',0, 20);
expect CompanyID=TEST;
select * from "NetOwnerCompanySearch"( 'test.owner','testxxxyyyzzz','P',0, 20) where companyid='TEST';
expect CompanyID=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 "NetOwnerCompanySearch"( 'test.owner','testxxxyyyzzz','P',0, 20) where companyid='TEST';
expect CompanyID=TEST,IsFavourite=1;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCompanySearch" IS 
{CREATE PROCEDURE pears."NetOwnerCompanySearch"( 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( "CompanyID" CHAR(20),"Name" CHAR(61),"Address" CHAR(100),"Status" CHAR(20),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
  c.companyid AS CompanyID,
  c.name AS Name,
  c.town AS Address,
  companystatus.name AS CompStatus,
  c.alert AS Alert,
  (SELECT COUNT(*) FROM favourites WHERE "type"='C' AND ID=c.companyid AND Staffid=pStaffID) AS IsFavourite
  FROM company c KEY LEFT OUTER JOIN companystatus
  WHERE
    isnull(c.divisionid,'') IN (SELECT DivisionID FROM DashboardStaffDivisions())
    AND c.status LIKE SearchState
    AND c.status <> '.'
    AND SearchValue <> ''
    AND c.keyname LIKE SearchValue+'%'
  ORDER BY c.KeyName ASC
END
 
/* DOC
2016-09-19 PC alter routine to allow search, and paging
2018-05-16 GJ Updated to use MHS function to obey divisions & tidied up test
 
inputs:
    pWebUserID
    SearchValue
    SearchCategory
    pSlice
    pSliceSize
 
outputs:
    CompanyID
    Name
    address
    status
    Alert
    IsFavourite
 
tests:
    null pWebUserID
    invalid pWebUserID
    null SearchValue and pSearchCategory
    create test category
    null SearchValue and test pSearchCategory
    incorrect SearchValue and test pSearchCategory
    create three test divisions
    check default division 
    check assigned divisions
    check all divisions
    check isFavourite
 
*/
 
/* TEST
call NetTestSetup('');
update company set divisionid=null, keyname='testxxxyyyzzz' where companyid='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 "NetOwnerCompanySearch"( null,null,null,0, 20);
expect EOF;
select * from "NetOwnerCompanySearch"( 'z99',null,null,0, 20);
expect EOF;
update staff set divisionid='TEST',DivisionAccess=0 where staffid='TEST';
select * from "NetOwnerCompanySearch"( 'test.owner',null,null,0, 20);
expect EOF;
update company set divisionid='TEST' where companyid='test';
select * from "NetOwnerCompanySearch"( 'test.owner',null,null,0, 20);
expect EOF;
update staff set DivisionAccess=1 where staffid='TEST';
select * from "NetOwnerCompanySearch"( 'test.owner','testxxxyyyzzz','P',0, 20);
expect CompanyID=TEST;
select * from "NetOwnerCompanySearch"( 'test.owner','testxxxyyyzzz','P',0, 20) where companyid='TEST';
expect CompanyID=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 "NetOwnerCompanySearch"( 'test.owner','testxxxyyyzzz','P',0, 20) where companyid='TEST';
expect CompanyID=TEST,IsFavourite=1;
*/
}