1. /*
  2. Used variables, set up as you like.
  3. Thank you, Rochet.
  4. */
  5. SET
  6. @ENTRY = 120000,
  7. @NAME = "Honorable Kills",
  8. @SUBNAME = "",
  9. @MODEL = 21572,
  11. @TEXT_ID = 40000;
  13. -- Delete existing data
  14. DELETE FROM world.creature_template WHERE entry = @ENTRY ;
  15. DELETE FROM world.npc_text WHERE ID BETWEEN @TEXT_ID AND @TEXT_ID+2;
  16. DELETE FROM world.gossip_menu WHERE entry BETWEEN 41000 AND 41002;
  17. DELETE FROM world.gossip_menu_option WHERE menu_id BETWEEN 41000 AND 41002;
  19. -- Creature
  20. INSERT INTO world.creature_template (entry, modelid1, NAME, subname, IconName, gossip_menu_id, minlevel, maxlevel, faction, npcflag, speed_walk, speed_run, scale, rank, unit_class, unit_flags, TYPE, type_flags, InhabitType, RegenHealth, flags_extra, AiName) VALUES
  21. (@ENTRY, @MODEL, @NAME, @SUBNAME, "Directions", 41000, 71, 71, 35, 3, 1, 1.14286, 1.25, 1, 1, 2, 7, 138936390, 3, 1, 2, "");
  23. -- Link text to gossip menu.
  24. INSERT INTO world.gossip_menu (entry, text_id) VALUES
  25. (41000, @TEXT_ID),
  26. (41001, @TEXT_ID+1),
  27. (41002, @TEXT_ID+2);
  29. -- Add the text'.
  30. INSERT INTO world.npc_text (ID, text0_0, em0_1) VALUES
  31. (@TEXT_ID, "Greetings.", 0),
  32. (@TEXT_ID+1, "Top kills all time.", 0),
  33. (@TEXT_ID+2, "Top kills today.", 0);
  35. -- Add the menu.
  36. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text) VALUES
  37. (41000, 1, 0, 'Top kills', 1, 1, 41001, 0, 0, 0, NULL),
  38. (41000, 2, 0, 'Top kills today', 1, 1, 41002, 0, 0, 0, NULL);
  40. -- Populate lifetime kills with already existing data
  41. SET @id = 0;
  42. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text)
  43. SELECT 41001, @id:=@id+1, 0, CONCAT(NAME, ' - lifetime kills: ', totalkills), 1, 1, 0, 0, 0, 0, NULL
  44. FROM characters.Characters AS C
  45. ORDER BY totalKills DESC
  46. LIMIT 30;
  48. -- Add a back button
  49. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text) VALUES
  50. (41001, @id+1, 0, '[Back]', 1, 1, 41000, 0, 0, 0, NULL);
  52. -- Populate today kills with the already exiting data
  53. SET @id = 0;
  54. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text)
  55. SELECT 41002, @id:=@id+1, 0, CONCAT(NAME, ' - today kills: ', todaykills), 1, 1, 0, 0, 0, 0, NULL
  56. FROM characters.Characters AS C
  57. ORDER BY todaykills DESC
  58. LIMIT 30;
  60. -- Add a back button
  61. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text) VALUES
  62. (41002, @id+1, 0, '[Back]', 1, 1, 41000, 0, 0, 0, NULL);
  64. -- Change delimiter, so we can use ';' in the trigger.
  65. DELIMITER //
  66. -- Trigger that will update the todaykills/totalkill whenever
  67. -- a character kill is updated.
  68. DROP TRIGGER IF EXISTS characters.tr_update_kills//
  69. CREATE TRIGGER characters.tr_update_kills
  70. AFTER UPDATE
  71. ON characters.Characters
  72. FOR EACH ROW
  73. BEGIN
  74. IF NEW.todaykills <> OLD.todaykills THEN
  75. DELETE FROM world.gossip_menu_option
  76. WHERE menu_id = 41002;
  78. SET @id = 0;
  79. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text)
  80. SELECT 41002, @id:=@id+1, 0, CONCAT(NAME, ' - today kills: ', todaykills), 1, 1, 0, 0, 0, 0, NULL
  81. FROM characters.Characters AS C
  82. ORDER BY todaykills DESC
  83. LIMIT 30;
  85. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text) VALUES
  86. (41002, @id+1, 0, '[Back]', 1, 1, 41000, 0, 0, 0, NULL);
  87. END IF;
  88. IF NEW.totalkills <> OLD.totalkills THEN
  89. DELETE FROM world.gossip_menu_option
  90. WHERE menu_id = 41001;
  92. SET @id = 0;
  93. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text)
  94. SELECT 41001, @id:=@id+1, 0, CONCAT(NAME, ' - lifetime kills: ', totalkills), 1, 1, 0, 0, 0, 0, NULL
  95. FROM characters.Characters AS C
  96. ORDER BY totalKills DESC
  97. LIMIT 30;
  99. INSERT INTO world.gossip_menu_option (menu_id, id, option_icon, option_text, option_id, npc_option_npcflag, action_menu_id, action_poi_id, box_coded, box_money, box_text) VALUES
  100. (41001, @id+1, 0, '[Back]', 1, 1, 41000, 0, 0, 0, NULL);
  101. END IF;
  102. END//
  104. DELIMITER ;