public static string NpcName()
{
var result = "";
if (Storage.UnitTemplates.IsEmpty())
return String.Empty;
if (!Settings.SQLOutputFlag.HasAnyFlagBit(SQLOutput.creature_template))
return string.Empty;
const string tableName = "creature_template";
if (SQLConnector.Enabled)
{
var rowsUpd = new List<QueryBuilder.SQLUpdateRow>();
foreach (var npcName in Storage.UnitTemplates)
{
var query = string.Format("SELECT name FROM {0}.creature_template WHERE entry={1};",
Settings.TDBDatabase, npcName.Key);
using (var reader = SQLConnector.ExecuteQuery(query))
{
if (reader.HasRows) // possible update
{
while (reader.Read())
{
var row = new QueryBuilder.SQLUpdateRow();
if (!Utilities.EqualValues(reader.GetValue(0), npcName.Value.Item1.Name))
row.AddValue("name", npcName.Value.Item1.Name);
if (Utilities.EqualValues(reader.GetValue(0), npcName.Value.Item1.Name) && npcName.Value.Item1.femaleName != null)
row.AddValue("femaleName", npcName.Value.Item1.femaleName);
row.AddWhere("entry", npcName.Key);
row.Table = tableName;
if (row.ValueCount != 0)
rowsUpd.Add(row);
}
}
}
}
result += new QueryBuilder.SQLUpdate(rowsUpd).Build();
}
return result;
}
public static string Gossip()
{
// TODO: This should be rewritten
if (Storage.Gossips.IsEmpty())
{
return(string.Empty);
}
var result = "";
// `gossip`
if (Settings.SQLOutputFlag.HasAnyFlagBit(SQLOutput.gossip_menu))
{
if (SQLConnector.Enabled)
{
var query =
new StringBuilder(string.Format("SELECT `entry`,`text_id` FROM {0}.`gossip_menu` WHERE ",
Settings.TDBDatabase));
foreach (Tuple <uint, uint> gossip in Storage.Gossips.Keys())
{
query.Append("(`entry`=").Append(gossip.Item1).Append(" AND ");
query.Append("`text_id`=").Append(gossip.Item2).Append(") OR ");
}
query.Remove(query.Length - 4, 4).Append(";");
var rows = new List <QueryBuilder.SQLInsertRow>();
using (var reader = SQLConnector.ExecuteQuery(query.ToString()))
{
if (reader != null)
{
while (reader.Read())
{
var values = new object[2];
var count = reader.GetValues(values);
if (count != 2)
{
break; // error in query
}
var entry = Convert.ToUInt32(values[0]);
var textId = Convert.ToUInt32(values[1]);
// our table is small, 2 fields and both are PKs; no need for updates
if (!Storage.Gossips.ContainsKey(Tuple.Create(entry, textId)))
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("entry", entry);
row.AddValue("text_id", textId);
row.Comment = StoreGetters.GetName(StoreNameType.Unit,
// BUG: GOs can send gossips too
(int)entry, false);
rows.Add(row);
}
}
}
}
result += new QueryBuilder.SQLInsert("gossip_menu", rows, 2).Build();
}
else
{
var rows = new List <QueryBuilder.SQLInsertRow>();
foreach (var gossip in Storage.Gossips)
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("entry", gossip.Key.Item1);
row.AddValue("text_id", gossip.Key.Item2);
row.Comment = StoreGetters.GetName(Utilities.ObjectTypeToStore(gossip.Value.Item1.ObjectType),
(int)gossip.Value.Item1.ObjectEntry, false);
rows.Add(row);
}
result += new QueryBuilder.SQLInsert("gossip_menu", rows, 2).Build();
}
}
// `gossip_menu_option`
if (Settings.SQLOutputFlag.HasAnyFlagBit(SQLOutput.gossip_menu_option))
{
if (SQLConnector.Enabled)
{
var rowsIns = new List <QueryBuilder.SQLInsertRow>();
var rowsUpd = new List <QueryBuilder.SQLUpdateRow>();
foreach (var gossip in Storage.Gossips)
{
if (gossip.Value.Item1.GossipOptions == null)
{
continue;
}
foreach (var gossipOption in gossip.Value.Item1.GossipOptions)
{
var query = // 0 1 2 3 4 5 6
string.Format(
"SELECT menu_id,id,option_icon,box_coded,box_money,box_text,option_text " +
"FROM {2}.gossip_menu_option WHERE menu_id={0} AND id={1};", gossip.Key.Item1,
gossipOption.Index, Settings.TDBDatabase);
using (var reader = SQLConnector.ExecuteQuery(query))
{
if (reader.HasRows) // possible update
{
while (reader.Read())
{
var row = new QueryBuilder.SQLUpdateRow();
if (!Utilities.EqualValues(reader.GetValue(2), gossipOption.OptionIcon))
{
row.AddValue("option_icon", gossipOption.OptionIcon);
}
if (!Utilities.EqualValues(reader.GetValue(3), gossipOption.Box))
{
row.AddValue("box_coded", gossipOption.Box);
}
if (!Utilities.EqualValues(reader.GetValue(4), gossipOption.RequiredMoney))
{
row.AddValue("box_money", gossipOption.RequiredMoney);
}
if (!Utilities.EqualValues(reader.GetValue(5), gossipOption.BoxText))
{
row.AddValue("box_text", gossipOption.BoxText);
}
if (!Utilities.EqualValues(reader.GetValue(6), gossipOption.OptionText))
{
row.AddValue("option_text", gossipOption.OptionText);
}
row.AddWhere("menu_id", gossip.Key.Item1);
row.AddWhere("id", gossipOption.Index);
row.Comment =
StoreGetters.GetName(
Utilities.ObjectTypeToStore(gossip.Value.Item1.ObjectType),
(int)gossip.Value.Item1.ObjectEntry, false);
row.Table = "gossip_menu_option";
if (row.ValueCount != 0)
{
rowsUpd.Add(row);
}
}
}
else // insert
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("menu_id", gossip.Key.Item1);
row.AddValue("id", gossipOption.Index);
row.AddValue("option_icon", gossipOption.OptionIcon);
row.AddValue("option_text", gossipOption.OptionText);
row.AddValue("box_coded", gossipOption.Box);
row.AddValue("box_money", gossipOption.RequiredMoney);
row.AddValue("box_text", gossipOption.BoxText);
row.Comment =
StoreGetters.GetName(Utilities.ObjectTypeToStore(gossip.Value.Item1.ObjectType),
(int)gossip.Value.Item1.ObjectEntry, false);
rowsIns.Add(row);
}
}
}
}
result += new QueryBuilder.SQLInsert("gossip_menu_option", rowsIns, 2).Build() +
new QueryBuilder.SQLUpdate(rowsUpd).Build();
}
else
{
var rows = new List <QueryBuilder.SQLInsertRow>();
foreach (var gossip in Storage.Gossips)
{
if (gossip.Value.Item1.GossipOptions != null)
{
foreach (var gossipOption in gossip.Value.Item1.GossipOptions)
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("menu_id", gossip.Key.Item1);
row.AddValue("id", gossipOption.Index);
row.AddValue("option_icon", gossipOption.OptionIcon);
row.AddValue("option_text", gossipOption.OptionText);
row.AddValue("box_coded", gossipOption.Box);
row.AddValue("box_money", gossipOption.RequiredMoney);
row.AddValue("box_text", gossipOption.BoxText);
row.Comment =
StoreGetters.GetName(Utilities.ObjectTypeToStore(gossip.Value.Item1.ObjectType),
(int)gossip.Value.Item1.ObjectEntry, false);
rows.Add(row);
}
}
}
result += new QueryBuilder.SQLInsert("gossip_menu_option", rows, 2).Build();
}
}
return(result);
}
public static string DefenseMessage()
{
if (Storage.DefenseMessages.IsEmpty())
{
return(String.Empty);
}
if (!Settings.SQLOutputFlag.HasAnyFlagBit(SQLOutput.defense_message))
{
return(string.Empty);
}
const string tableName = "defense_message";
var rows = new List <QueryBuilder.SQLInsertRow>();
foreach (var message in Storage.DefenseMessages)
{
foreach (var messageValue in message.Value)
{
var row = new QueryBuilder.SQLInsertRow();
var query = new StringBuilder(string.Format("SELECT Id FROM {1}.broadcast_text WHERE MaleText='{0}' OR FemaleText='{0}';", MySqlHelper.DoubleQuoteString(messageValue.Item1.text), Settings.TDBDatabase));
string broadcastTextId = "";
if (Settings.DevMode)
{
using (var reader = SQLConnector.ExecuteQuery(query.ToString()))
{
if (reader != null)
{
while (reader.Read())
{
var values = new object[1];
var count = reader.GetValues(values);
if (count != 1)
{
break; // error in query
}
if (!String.IsNullOrWhiteSpace(broadcastTextId))
{
broadcastTextId += " - " + Convert.ToInt32(values[0]);
}
else
{
broadcastTextId += Convert.ToInt32(values[0]);
}
}
}
}
}
row.AddValue("ZoneId", message.Key);
row.AddValue("Id", "x", false, true);
row.AddValue("Text", messageValue.Item1.text);
if (Settings.DevMode)
{
row.AddValue("BroadcastTextId", broadcastTextId);
}
rows.Add(row);
}
}
return(new QueryBuilder.SQLInsert(tableName, rows, 1, false).Build());
}
public static string CreatureText()
{
if (Storage.CreatureTexts.IsEmpty())
{
return(string.Empty);
}
if (!Settings.SQLOutputFlag.HasAnyFlagBit(SQLOutput.creature_text))
{
return(string.Empty);
}
// For each sound and emote, if the time they were send is in the +1/-1 seconds range of
// our texts, add that sound and emote to our Storage.CreatureTexts
foreach (var text in Storage.CreatureTexts)
{
// For each text
foreach (var textValue in text.Value)
{
// For each emote
foreach (var emote in Storage.Emotes)
{
// Emote packets always have a sender (guid);
// skip this one if it was sent by a different creature
if (emote.Key.GetEntry() != text.Key)
{
continue;
}
foreach (var emoteValue in emote.Value)
{
var timeSpan = textValue.Item2 - emoteValue.Item2;
if (timeSpan != null && timeSpan.Value.Duration() <= TimeSpan.FromSeconds(1))
{
textValue.Item1.Emote = emoteValue.Item1;
}
}
}
// For each sound
foreach (var sound in Storage.Sounds)
{
var timeSpan = textValue.Item2 - sound.Item2;
if (timeSpan != null && timeSpan.Value.Duration() <= TimeSpan.FromSeconds(1))
{
textValue.Item1.Sound = sound.Item1;
}
}
}
}
/* can't use compare DB without knowing values of groupid or id
* var entries = Storage.CreatureTexts.Keys.ToList();
* var creatureTextDb = SQLDatabase.GetDict<uint, CreatureText>(entries);
*/
const string tableName = "creature_text";
var rows = new List <QueryBuilder.SQLInsertRow>();
foreach (var text in Storage.CreatureTexts)
{
foreach (var textValue in text.Value)
{
var row = new QueryBuilder.SQLInsertRow();
var query = new StringBuilder(string.Format("SELECT Id FROM {1}.broadcast_text WHERE MaleText='{0}' OR FemaleText='{0}';", MySqlHelper.DoubleQuoteString(textValue.Item1.Text), Settings.TDBDatabase));
string broadcastTextId = "";
if (Settings.DevMode)
{
using (var reader = SQLConnector.ExecuteQuery(query.ToString()))
{
if (reader != null)
{
while (reader.Read())
{
var values = new object[1];
var count = reader.GetValues(values);
if (count != 1)
{
break; // error in query
}
if (!String.IsNullOrWhiteSpace(broadcastTextId))
{
broadcastTextId += " - " + Convert.ToInt32(values[0]);
}
else
{
broadcastTextId += Convert.ToInt32(values[0]);
}
}
}
}
}
row.AddValue("entry", text.Key);
row.AddValue("groupid", "x", false, true);
row.AddValue("id", "x", false, true);
row.AddValue("text", textValue.Item1.Text);
row.AddValue("type", textValue.Item1.Type);
row.AddValue("language", textValue.Item1.Language);
row.AddValue("probability", 100.0);
row.AddValue("emote", textValue.Item1.Emote);
row.AddValue("duration", 0);
row.AddValue("sound", textValue.Item1.Sound);
if (Settings.DevMode)
{
row.AddValue("BroadcastTextID", broadcastTextId);
}
row.AddValue("comment", textValue.Item1.Comment);
rows.Add(row);
}
}
return(new QueryBuilder.SQLInsert(tableName, rows, 1, false).Build());
}
public static string QuestObjective()
{
var result = "";
if (Storage.QuestObjectives.IsEmpty())
{
return(String.Empty);
}
if (!Settings.SQLOutputFlag.HasAnyFlagBit(SQLOutput.quest_template))
{
return(String.Empty);
}
var entries = Storage.QuestObjectives.Keys();
var templatesDb = SQLDatabase.GetDict <uint, QuestInfoObjective>(entries, "Id");
result += SQLUtil.CompareDicts(Storage.QuestObjectives, templatesDb, StoreNameType.QuestObjective, "Id");
var rowsIns = new List <QueryBuilder.SQLInsertRow>();
var rowsUpd = new List <QueryBuilder.SQLUpdateRow>();
foreach (var questObjectives in Storage.QuestObjectives)
{
foreach (var visualEffectIds in questObjectives.Value.Item1.VisualEffectIds)
{
if (SQLConnector.Enabled)
{
var query = string.Format("SELECT `VisualEffect`, `VerifiedBuild` FROM {0}.quest_visual_effect WHERE `Id`={1} AND `Index`={2};",
Settings.TDBDatabase, questObjectives.Key, visualEffectIds.Index);
using (var reader = SQLConnector.ExecuteQuery(query))
{
if (reader.HasRows) // possible update
{
while (reader.Read())
{
var row = new QueryBuilder.SQLUpdateRow();
if (!Utilities.EqualValues(reader.GetValue(0), visualEffectIds.VisualEffect))
{
row.AddValue("VisualEffect", visualEffectIds.VisualEffect);
}
if (!Utilities.EqualValues(reader.GetValue(1), questObjectives.Value.Item1.VerifiedBuild))
{
row.AddValue("VerifiedBuild", questObjectives.Value.Item1.VerifiedBuild);
}
row.AddWhere("Id", questObjectives.Key);
row.AddWhere("Index", visualEffectIds.Index);
row.Table = "quest_visual_effect";
if (row.ValueCount != 0)
{
rowsUpd.Add(row);
}
}
}
else // insert
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("Id", questObjectives.Key);
row.AddValue("Index", visualEffectIds.Index);
row.AddValue("VisualEffect", visualEffectIds.VisualEffect);
row.AddValue("VerifiedBuild", questObjectives.Value.Item1.VerifiedBuild);
rowsIns.Add(row);
}
}
}
else // insert
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("Id", questObjectives.Key);
row.AddValue("Index", visualEffectIds.Index);
row.AddValue("VisualEffect", visualEffectIds.VisualEffect);
row.AddValue("VerifiedBuild", questObjectives.Value.Item1.VerifiedBuild);
rowsIns.Add(row);
}
}
}
result += new QueryBuilder.SQLInsert("quest_visual_effect", rowsIns, 2).Build() + new QueryBuilder.SQLUpdate(rowsUpd).Build();
return(result);
}
public static string Gossip()
{
if (Storage.Gossips.IsEmpty)
{
return(String.Empty);
}
// `creature_template`
var gossipIds = new Dictionary <uint, UnitGossip>();
foreach (var gossip in Storage.Gossips)
{
if (gossip.Value.ObjectType != ObjectType.Unit)
{
continue;
}
// no support for entries with multiple gossips (i.e changed by script)
if (gossipIds.ContainsKey(gossip.Value.ObjectEntry))
{
continue;
}
UnitGossip a;
a.GossipId = gossip.Key.Item1;
gossipIds.Add(gossip.Value.ObjectEntry, a);
}
var entries = gossipIds.Keys.ToList();
var gossipIdsDb = SQLDatabase.GetDict <uint, UnitGossip>(entries);
var result = SQLUtil.CompareDicts(gossipIds, gossipIdsDb, StoreNameType.Unit);
// `gossip`
if (SQLConnector.Enabled)
{
var query = new StringBuilder("SELECT `entry`,`text_id` FROM `world`.`gossip_menu` WHERE ");
foreach (Tuple <uint, uint> gossip in Storage.Gossips.Keys)
{
query.Append("(`entry`=").Append(gossip.Item1).Append(" AND ");
query.Append("`text_id`=").Append(gossip.Item2).Append(") OR ");
}
query.Remove(query.Length - 4, 4).Append(";");
var rows = new List <QueryBuilder.SQLInsertRow>();
using (var reader = SQLConnector.ExecuteQuery(query.ToString()))
{
if (reader != null)
{
while (reader.Read())
{
var values = new object[2];
var count = reader.GetValues(values);
if (count != 2)
{
break; // error in query
}
var entry = Convert.ToUInt32(values[0]);
var textId = Convert.ToUInt32(values[1]);
// our table is small, 2 fields and both are PKs; no need for updates
if (!Storage.Gossips.ContainsKey(Tuple.Create(entry, textId)))
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("entry", entry);
row.AddValue("text_id", textId);
row.Comment = StoreGetters.GetName(StoreNameType.Unit, // BUG: GOs can send gossips too
(int)entry, false);
rows.Add(row);
}
}
}
}
result += new QueryBuilder.SQLInsert("gossip_menu", rows, 2).Build();
}
else
{
var rows = new List <QueryBuilder.SQLInsertRow>();
foreach (var gossip in Storage.Gossips)
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("entry", gossip.Key.Item1);
row.AddValue("text_id", gossip.Key.Item2);
row.Comment = StoreGetters.GetName(Utilities.ObjectTypeToStore(gossip.Value.ObjectType),
(int)gossip.Value.ObjectEntry, false);
rows.Add(row);
}
result += new QueryBuilder.SQLInsert("gossip_menu", rows, 2).Build();
}
// `gossip_menu_option`
if (SQLConnector.Enabled)
{
var rowsIns = new List <QueryBuilder.SQLInsertRow>();
var rowsUpd = new List <QueryBuilder.SQLUpdateRow>();
foreach (var gossip in Storage.Gossips)
{
if (gossip.Value.GossipOptions == null)
{
continue;
}
foreach (var gossipOption in gossip.Value.GossipOptions)
{
var query = // 0 1 2 3 4 5 6
string.Format("SELECT menu_id,id,option_icon,box_coded,box_money,box_text,option_text " +
"FROM world.gossip_menu_option WHERE menu_id={0} AND id={1};", gossip.Key.Item1,
gossipOption.Index);
using (var reader = SQLConnector.ExecuteQuery(query))
{
if (reader.HasRows) // possible update
{
while (reader.Read())
{
var row = new QueryBuilder.SQLUpdateRow();
if (!Utilities.EqualValues(reader.GetValue(2), gossipOption.OptionIcon))
{
row.AddValue("option_icon", gossipOption.OptionIcon);
}
if (!Utilities.EqualValues(reader.GetValue(3), gossipOption.Box))
{
row.AddValue("box_coded", gossipOption.Box);
}
if (!Utilities.EqualValues(reader.GetValue(4), gossipOption.RequiredMoney))
{
row.AddValue("box_money", gossipOption.RequiredMoney);
}
if (!Utilities.EqualValues(reader.GetValue(5), gossipOption.BoxText))
{
row.AddValue("box_text", gossipOption.BoxText);
}
if (!Utilities.EqualValues(reader.GetValue(6), gossipOption.OptionText))
{
row.AddValue("option_text", gossipOption.OptionText);
}
row.AddWhere("menu_id", gossip.Key.Item1);
row.AddWhere("id", gossipOption.Index);
row.Comment =
StoreGetters.GetName(Utilities.ObjectTypeToStore(gossip.Value.ObjectType),
(int)gossip.Value.ObjectEntry, false);
row.Table = "gossip_menu_option";
if (row.ValueCount != 0)
{
rowsUpd.Add(row);
}
}
}
else // insert
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("menu_id", gossip.Key.Item1);
row.AddValue("id", gossipOption.Index);
row.AddValue("option_icon", gossipOption.OptionIcon);
row.AddValue("option_text", gossipOption.OptionText);
row.AddValue("box_coded", gossipOption.Box);
row.AddValue("box_money", gossipOption.RequiredMoney);
row.AddValue("box_text", gossipOption.BoxText);
row.Comment = StoreGetters.GetName(Utilities.ObjectTypeToStore(gossip.Value.ObjectType),
(int)gossip.Value.ObjectEntry, false);
rowsIns.Add(row);
}
}
}
}
result += new QueryBuilder.SQLInsert("gossip_menu_option", rowsIns, 2).Build() +
new QueryBuilder.SQLUpdate(rowsUpd).Build();
}
else
{
var rows = new List <QueryBuilder.SQLInsertRow>();
foreach (var gossip in Storage.Gossips)
{
if (gossip.Value.GossipOptions != null)
{
foreach (var gossipOption in gossip.Value.GossipOptions)
{
var row = new QueryBuilder.SQLInsertRow();
row.AddValue("menu_id", gossip.Key.Item1);
row.AddValue("id", gossipOption.Index);
row.AddValue("option_icon", gossipOption.OptionIcon);
row.AddValue("option_text", gossipOption.OptionText);
row.AddValue("box_coded", gossipOption.Box);
row.AddValue("box_money", gossipOption.RequiredMoney);
row.AddValue("box_text", gossipOption.BoxText);
row.Comment = StoreGetters.GetName(Utilities.ObjectTypeToStore(gossip.Value.ObjectType),
(int)gossip.Value.ObjectEntry, false);
rows.Add(row);
}
}
}
result += new QueryBuilder.SQLInsert("gossip_menu_option", rows, 2).Build();
}
return(result);
}
