pears.NetSearchJobs

CREATE PROCEDURE "pears"."NetSearchJobs"( IN "pWebUserID" CHAR(20),IN "pVacancyType" CHAR(1) DEFAULT NULL,IN "pMinPay" DECIMAL(7,2) DEFAULT 0,IN "pMaxPay" DECIMAL(7,2) DEFAULT 1000000,IN "pLocation" CHAR(30) DEFAULT '%',IN "pKeywordSearch" CHAR(50),IN "pPageNo" INTEGER DEFAULT 1,IN "pPageSize" INTEGER DEFAULT 20 ) 
RESULT( "vacancyid" CHAR(20),"position" CHAR(60),"VacType" CHAR(20),"OurRef" CHAR(10),"TempRate" CHAR(10),"AnnualisedPay" INTEGER ) 
-- 20150929 Job search for Change
BEGIN
  DECLARE "SearchLocation" CHAR(30);
  DECLARE "PayThresholdHourly" DECIMAL(7,2);
  DECLARE "PayThresholdDaily" DECIMAL(7,2);
  DECLARE "AnnualisationFactorHourly" REAL;
  DECLARE "AnnualisationFactorDaily" REAL;
  DECLARE "AnnualisationFactorYearly" REAL;
  DECLARE "StartRecord" INTEGER;
  DECLARE "SampleSize" INTEGER;
  SET "SearchLocation" = "isnull"("pLocation",'%');
  SET "PayThresholdHourly" = 60;
  SET "PayThresholdDaily" = 2000;
  SET "AnnualisationFactorHourly" = 35*52;
  SET "AnnualisationFactorDaily" = 240;
  SET "AnnualisationFactorYearly" = 1;
  SET "StartRecord" = ("isnull"("pPageNo",1)-1)*"pPageSize"+1;
  SET "SampleSize" = "isnull"("pPageSize",20);
  SELECT top "SampleSize" START at "StartRecord"
    "v"."vacancyid",
    "v"."position",
    (IF "v"."temp" = 1 THEN 'temporary/interim'
    ELSE 'permanent'
    endif) AS "VacancyType",
    "v"."refcode",
    (IF "v"."temp" = 1 THEN CAST("GetTempRate"('P','V',"vacancyid") AS DECIMAL(7,2)) ELSE NULL endif) AS "TempRate",
    "isnull"("v"."salary",("TempRate"*"SearchPayFactor"),0) AS "PayAnnualised",
    (IF "TempRate" < "PayThresholdHourly" THEN "AnnualisationFactorHourly"
    ELSE IF "TempRate" > "PayThresholdDaily" THEN "AnnualisationFactorYearly"
      ELSE "AnnualisationFactorDaily"
      endif
    endif) AS "SearchPayFactor",
    "v"."whenentered"
    FROM "vacancy" AS "v"
    WHERE CAST("v"."temp" AS CHAR(1)) LIKE "isnull"("pVacancyType",'%')
    AND "PayAnnualised" BETWEEN "isnull"("pMinPay",0) AND "isnull"("pMaxPay",1000000)
    AND("isnull"("v"."town",'%') LIKE "SearchLocation" OR "isnull"("v"."county",'%') LIKE "SearchLocation" OR "isnull"("v"."country",'%') LIKE "SearchLocation")
    AND "isnull"("notes",'%%') LIKE '%'+"pKeywordSearch"+'%'
    ORDER BY "v"."whenentered" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetSearchJobs" IS 
{CREATE PROCEDURE pears."NetSearchJobs"( IN "pWebUserID" CHAR(20),IN "pVacancyType" CHAR(1) DEFAULT NULL,IN "pMinPay" DECIMAL(7,2) DEFAULT 0,IN "pMaxPay" DECIMAL(7,2) DEFAULT 1000000,IN "pLocation" CHAR(30) DEFAULT '%',IN "pKeywordSearch" CHAR(50),IN "pPageNo" INTEGER DEFAULT 1,IN "pPageSize" INTEGER DEFAULT 20 ) 
RESULT( "vacancyid" CHAR(20),"position" CHAR(60),"VacType" CHAR(20),"OurRef" CHAR(10),"TempRate" CHAR(10),"AnnualisedPay" INTEGER ) 
-- 20150929 Job search for Change
BEGIN
  DECLARE "SearchLocation" CHAR(30);
  DECLARE "PayThresholdHourly" DECIMAL(7,2);
  DECLARE "PayThresholdDaily" DECIMAL(7,2);
  DECLARE "AnnualisationFactorHourly" REAL;
  DECLARE "AnnualisationFactorDaily" REAL;
  DECLARE "AnnualisationFactorYearly" REAL;
  DECLARE "StartRecord" INTEGER;
  DECLARE "SampleSize" INTEGER;
  SET "SearchLocation" = "isnull"("pLocation",'%');
  SET "PayThresholdHourly" = 60;
  SET "PayThresholdDaily" = 2000;
  SET "AnnualisationFactorHourly" = 35*52;
  SET "AnnualisationFactorDaily" = 240;
  SET "AnnualisationFactorYearly" = 1;
  SET "StartRecord" = ("isnull"("pPageNo",1)-1)*"pPageSize"+1;
  SET "SampleSize" = "isnull"("pPageSize",20);
  SELECT top "SampleSize" START at "StartRecord"
    "v"."vacancyid",
    "v"."position",
    (IF "v"."temp" = 1 THEN 'temporary/interim'
    ELSE 'permanent'
    endif) AS "VacancyType",
    "v"."refcode",
    (IF "v"."temp" = 1 THEN CAST("GetTempRate"('P','V',"vacancyid") AS DECIMAL(7,2)) ELSE NULL endif) AS "TempRate",
    "isnull"("v"."salary",("TempRate"*"SearchPayFactor"),0) AS "PayAnnualised",
    (IF "TempRate" < "PayThresholdHourly" THEN "AnnualisationFactorHourly"
    ELSE IF "TempRate" > "PayThresholdDaily" THEN "AnnualisationFactorYearly"
      ELSE "AnnualisationFactorDaily"
      endif
    endif) AS "SearchPayFactor",
    "v"."whenentered"
    FROM "vacancy" AS "v"
    WHERE CAST("v"."temp" AS CHAR(1)) LIKE "isnull"("pVacancyType",'%')
    AND "PayAnnualised" BETWEEN "isnull"("pMinPay",0) AND "isnull"("pMaxPay",1000000)
    AND("isnull"("v"."town",'%') LIKE "SearchLocation" OR "isnull"("v"."county",'%') LIKE "SearchLocation" OR "isnull"("v"."country",'%') LIKE "SearchLocation")
    AND "isnull"("notes",'%%') LIKE '%'+"pKeywordSearch"+'%'
    ORDER BY "v"."whenentered" ASC
END
}