pears.MatchShifts

CREATE PROCEDURE "pears"."MatchShifts"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pvacancyid" CHAR(20),IN "pexplicit" SMALLINT,IN "pexactgrade" SMALLINT DEFAULT 0 ) 
BEGIN
  DECLARE "gradeEq" CHAR(10);
  IF "pexactgrade" = 1 THEN
    SET "gradeEq" = '=='
  ELSE
    SET "gradeEq" = ''
  END IF;
  UPDATE "tempmatchcandidate" SET "matchscore" = 0 WHERE "vacancyid" = "pvacancyid";
  FOR "SHIFTFOR" AS "SHIFTCUR" no scroll cursor FOR
    SELECT "m"."tempmatchcandidateid" AS "pmatchcandid",
      "trim"(
      "isnull"(
      "left"("personavailableforplan"("m"."personid","s"."shiftdate","s"."timefrom","s"."timeto","s"."recoveryhours","s"."minutes","pexplicit","v"."departmentid",
      "ifnull"((SELECT FIRST "secondaryagencyid" FROM "pay_employee" WHERE "personid" = "m"."personid"),"s"."essentialskill",NULL),
      "gradeEq"+"nullif"("trim"("s"."essentialskillgradeid"),''),"s"."essentialskillchoicelist",NULL),1),'')) AS "avail",
      IF "avail" = '' THEN 'M' ELSE "avail" endif AS "availresult",
      CASE "availresult" WHEN 'A' THEN
        1.02
      WHEN 'G' THEN 1.01 WHEN 'M' THEN 1.0 ELSE 0.0 END AS "successscore" FROM "tempmatchcandidate" AS "m" JOIN "person" AS "p" ON "m"."personid" = "p"."personid"
        ,"tempmatchplan" AS "mp" JOIN "tempshiftplan" AS "s" ON "mp"."tempshiftplanid" = "s"."tempshiftplanid"
        ,"tempshiftplan" AS "s" KEY JOIN "vacancy" AS "v"
      WHERE "m"."vacancyid" = "pvacancyid" AND("p"."status" NOT IN( '.','#' ) )
      AND "masterrostertempallowed"("m"."personid","s"."masterrostershiftid") = 1
      AND NOT "m"."personid"
       = any(SELECT "x"."person2id" FROM "vacancy" AS "v" JOIN "person" AS "p" ON "v"."recipientid" = "p"."personid"
          JOIN "personincompatibility" AS "x" ON "p"."personid" = "x"."person1id" WHERE "v"."vacancyid" = "pvacancyid")
      AND "mp"."vacancyid" = "pvacancyid"
      AND "successscore" > 0.0 FOR READ ONLY do
    UPDATE "tempmatchcandidate" SET "matchscore" = "matchscore"+"successscore" WHERE "tempmatchcandidateid" = "pmatchcandid"
  END FOR
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."MatchShifts" IS 
{CREATE PROCEDURE MatchShifts 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN pvacancyid CHAR(20),IN pexplicit SMALLINT,IN pexactgrade SMALLINT DEFAULT 0 ) 
BEGIN
  DECLARE gradeEq CHAR(10);
  IF pexactgrade = 1 THEN
    SET gradeEq = '=='
  ELSE
    SET gradeEq = ''
  END IF;
  UPDATE tempmatchcandidate SET matchscore = 0 WHERE vacancyid = pvacancyid;
  FOR SHIFTFOR AS SHIFTCUR no scroll cursor FOR
    SELECT m.tempmatchcandidateid AS pmatchcandid,
      TRIM(
      isnull(
      "left"(personavailableforplan(m.personid,s.shiftdate,s.timefrom,s.timeto,s.recoveryhours,s.minutes,pexplicit,v.departmentid,
      ifnull((SELECT FIRST secondaryagencyid FROM pay_employee WHERE personid = m.personid),s.essentialskill,NULL),
      gradeEq+NULLIF(TRIM(s.essentialskillgradeid),''),s.essentialskillchoicelist,NULL),1),'')) AS avail,
      IF avail = '' THEN 'M' ELSE avail endif AS availresult,
      CASE availresult WHEN 'A' THEN
        1.02
      WHEN 'G' THEN 1.01 WHEN 'M' THEN 1.0 ELSE 0.0 END AS successscore FROM tempmatchcandidate AS m JOIN person AS p ON m.personid = p.personid
        ,tempmatchplan AS mp JOIN tempshiftplan AS s ON mp.tempshiftplanid = s.tempshiftplanid
        ,tempshiftplan AS s KEY JOIN vacancy AS v
      WHERE m.vacancyid = pvacancyid AND(p.status NOT IN( '.','#' ) )
      AND masterrostertempallowed(m.personid,s.masterrostershiftid)=1
      AND NOT m.personid
       = any(SELECT x.person2id FROM vacancy AS v JOIN person AS p ON v.recipientid = p.personid
          JOIN personincompatibility AS x ON p.personid = x.person1id WHERE v.vacancyid = pvacancyid)
      AND mp.vacancyid = pvacancyid
      AND successscore > 0.0 FOR READ ONLY do
    UPDATE tempmatchcandidate SET matchscore = matchscore+successscore WHERE tempmatchcandidateid = pmatchcandid
  END FOR
END
}