pears.VacancyKeyWordsUpdate

CREATE PROCEDURE "pears"."VacancyKeyWordsUpdate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN @ResetAll tinyint DEFAULT 0 ) 
BEGIN
  IF @ResetAll = 0 THEN
    UPDATE "VacancyKeyWords"
      SET "StandardKeyWords" = (SELECT "string"("v"."refcode",' ',"v"."position",' ',"c"."Name",' ',"c"."ClientCode",' ',"c"."Addr1",' ',"c"."Addr2",' ',"c"."Addr3",' ',"c"."Town",' ',"c"."County",' ',"c"."PostCode",' ',
        "v"."siteemail",' ',"v"."sitename",' ',"v"."sitecontact",' ',"p"."forenames",' ',"p"."surname",' ',
        (SELECT "list"("n"."number",' ') FROM "Phone" AS "n" WHERE "n"."Who" = 'C' AND "n"."number" LIKE '% %' AND "n"."WhoID" = "c"."CompanyID"),' ',
        (SELECT "list"("replace"("n"."number",' ',''),' ') FROM "Phone" AS "n" WHERE "n"."Who" = 'C' AND "n"."WhoID" = "c"."companyid"))),
      "TagKeyWords" = "nullif"("VacancyKeyWordsQuestion"("VacancyKeyWords"."VacancyID"),''),
      "CustomKeyWords" = "nullif"("VacancyKeyWordsCustom"("VacancyKeyWords"."VacancyID"),''),
      "RefreshRequired" = 0,
      "CustomRefreshRequired" = 0,
      "TagRefreshRequired" = 0 FROM // this IS included abouve, so does NOT need TO be done again 
      "VacancyKeyWords" JOIN "vacancy" AS "v" ON("VacancyKeyWords"."VacancyID" = "v"."vacancyid"),"vacancy" AS "v" KEY JOIN "employment" AS "e" KEY JOIN("company" AS "c","person" AS "p")
      WHERE "RefreshRequired" = 1;
    UPDATE "VacancyKeyWords"
      SET "TagKeyWords" = "VacancyKeyWordsQuestion"("VacancyKeyWords"."VacancyID"),
      "TagRefreshRequired" = 0
      WHERE "TagRefreshRequired" = 1;
    UPDATE "VacancyKeyWords"
      SET "CustomKeyWords" = "nullif"("VacancyKeyWordsCustom"("VacancyKeyWords"."VacancyID"),''),
      "CustomRefreshRequired" = 0
      WHERE "CustomRefreshRequired" = 1
  ELSEIF @ResetAll = 1 THEN
    UPDATE "VacancyKeyWords" SET "RefreshRequired" = 1 // ALL records will be refreshed ON the NEXT triggering OF the PROCEDURE (normally BY the event) 
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."VacancyKeyWordsUpdate" IS 
{CREATE PROCEDURE VacancyKeyWordsUpdate 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN @ResetAll tinyint DEFAULT 0)
BEGIN
    IF @ResetAll = 0 THEN
        UPDATE VacancyKeyWords
            SET
            StandardKeyWords = (SELECT string(v.refcode,' ' ,v.position,' ',c.Name,' ',c.ClientCode,' ',c.Addr1,' ',c.Addr2,' ',c.Addr3,' ',c.Town,' ',c.County,' ',c.PostCode, ' ',
           v.siteemail,' ' ,v.sitename,' ',v.sitecontact,' ' , p.forenames,' ', p.surname,' ', 
                (SELECT list(n.number,' ') FROM Phone n WHERE n.Who = 'C' AND n.number LIKE '% %' AND n.WhoID = c.CompanyID), ' ',
                (SELECT list(REPLACE(n.number,' ',''),' ') FROM Phone n  WHERE n.Who = 'C' AND n.WhoID =c.companyid ))), 
           TagKeyWords = NULLIF(VacancyKeyWordsQuestion(VacancyKeyWords.VacancyID),''),
           CustomKeyWords = NULLIF(VacancyKeyWordsCustom(VacancyKeyWords.VacancyID),''),
           RefreshRequired = 0, 
           CustomRefreshRequired = 0, 
           TagRefreshRequired = 0 // this IS included abouve, so does NOT need TO be done again 
           FROM VacancyKeyWords JOIN vacancy v ON (VacancyKeyWords.VacancyID =v.vacancyid) , vacancy v KEY JOIN employment e KEY JOIN (company c, person p) 
        WHERE RefreshRequired = 1;
         UPDATE VacancyKeyWords 
             SET
             TagKeyWords = VacancyKeyWordsQuestion(VacancyKeyWords.VacancyID), 
            TagRefreshRequired = 0   
        WHERE TagRefreshRequired = 1;
         UPDATE VacancyKeyWords 
             SET
             CustomKeyWords = NULLIF(VacancyKeyWordsCustom(VacancyKeyWords.VacancyID),''), 
            CustomRefreshRequired = 0   
        WHERE CustomRefreshRequired = 1;
ELSEIF @ResetAll = 1 
    THEN UPDATE VacancyKeyWords SET RefreshRequired = 1; // ALL records will be refreshed ON the NEXT triggering OF the PROCEDURE (normally BY the event) 
END IF;
END
}