pears.PersonKeyWordsUpdate

CREATE PROCEDURE "pears"."PersonKeyWordsUpdate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN @ResetAll tinyint DEFAULT 0 ) 
BEGIN
  IF @ResetAll = 0 THEN
    UPDATE "PersonKeyWords"
      SET "StandardKeyWords" = (SELECT "string"("p"."salutation",' ',"p"."Surname",' ',"p"."ForeNames",' ',"p"."AppNumber",' ',"p"."Addr1",' ',"p"."Addr2",' ',"p"."Addr3",' ',"p"."Town",' ',"p"."County",' ',"p"."PostCode",' ',"PayrollNumber",' ',"NI",' ',
        (SELECT "list"("n"."number",' ') FROM "Phone" AS "n" WHERE "n"."Who" = 'P' AND "n"."number" LIKE '% %' AND "n"."WhoID" = "p"."PersonID"),' ',
        (SELECT "list"("replace"("n"."number",' ',''),' ') FROM "Phone" AS "n" WHERE "n"."Who" = 'P' AND "n"."WhoID" = "p"."PersonID")) FROM "Person" AS "p" WHERE "p"."PersonID" = "PersonKeyWords"."PersonID"),
      "TagKeyWords" = "nullif"("PersonKeyWordsQuestion"("PersonKeyWords"."PersonID"),''),
      "CustomKeyWords" = "nullif"("PersonKeyWordsCustom"("PersonKeyWords"."PersonID"),''),
      "RefreshRequired" = 0,
      "CustomRefreshRequired" = 0,
      "TagRefreshRequired" = 0 // this IS included abouve, so does NOT need TO be done again
      WHERE "RefreshRequired" = 1;
    // carry OUT any tag ONLY updates required 
    UPDATE "PersonKeyWords"
      SET "TagKeyWords" = "PersonKeyWordsQuestion"("PersonKeyWords"."PersonID"),
      "TagRefreshRequired" = 0
      WHERE "TagRefreshRequired" = 1;
    UPDATE "PersonKeyWords"
      SET "CustomKeyWords" = "nullif"("PersonKeyWordsCustom"("PersonKeyWords"."PersonID"),''),
      "CustomRefreshRequired" = 0
      WHERE "CustomRefreshRequired" = 1
  ELSEIF @ResetAll = 1 THEN
    UPDATE "PersonKeyWords" 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"."PersonKeyWordsUpdate" IS 
{CREATE PROCEDURE PersonKeyWordsUpdate 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN @ResetAll tinyint DEFAULT 0)
BEGIN
    IF @ResetAll = 0 THEN
        UPDATE PersonKeyWords
            SET
            StandardKeyWords = (SELECT string(p.salutation, ' ' ,p.Surname,' ',p.ForeNames,' ',p.AppNumber,' ',p.Addr1,' ',p.Addr2,' ',p.Addr3,' ',p.Town,' ',p.County,' ',p.PostCode, ' ', PayrollNumber, ' ', NI, ' ',
                (SELECT list(n.number,' ') FROM Phone n WHERE n.Who = 'P' AND n.number LIKE '% %' AND n.WhoID = p.PersonID), ' ',
                (SELECT list(REPLACE(n.number,' ',''),' ') FROM Phone n WHERE n.Who = 'P' AND n.WhoID = p.PersonID)) FROM Person p WHERE p.PersonID = PersonKeyWords.PersonID), 
           TagKeyWords = NULLIF(PersonKeyWordsQuestion(PersonKeyWords.PersonID),''),
           CustomKeyWords = NULLIF(PersonKeyWordsCustom(PersonKeyWords.PersonID),''),
           RefreshRequired = 0, 
           CustomRefreshRequired = 0, 
           TagRefreshRequired = 0 // this IS included abouve, so does NOT need TO be done again
        WHERE RefreshRequired = 1;
        // carry OUT any tag ONLY updates required 
         UPDATE PersonKeyWords
           SET
            TagKeyWords = PersonKeyWordsQuestion(PersonKeyWords.PersonID),
            TagRefreshRequired = 0 
         WHERE TagRefreshRequired = 1;
         UPDATE PersonKeyWords
           SET
            CustomKeyWords = NULLIF(PersonKeyWordsCustom(PersonKeyWords.PersonID),''),
            CustomRefreshRequired = 0 
         WHERE CustomRefreshRequired = 1;
ELSEIF @ResetAll = 1 
    THEN UPDATE PersonKeyWords SET RefreshRequired = 1; // ALL records will be refreshed ON the NEXT triggering OF the PROCEDURE (normally BY the event) 
END IF;
END
}