public bool UpsertItemOwner(int _ItemID, int _SuffixID, VF.SQLPlayerID _PlayerID, VF.SQLIngameItemID?_LatestItemInfo = null, DateTime?_DateAquired = null)
{
if (_LatestItemInfo.HasValue == false)
{
_LatestItemInfo = new SQLIngameItemID(0);
}
if (_DateAquired.HasValue == false)
{
_DateAquired = DateTime.MaxValue;
}
var conn = OpenConnection();
try
{
using (var cmd = new NpgsqlCommand("INSERT INTO ItemOwnerTable(ItemID, SuffixID, PlayerID, DateAquired, LatestItemInfo) VALUES(:ItemID, :SuffixID, :PlayerID, :DateAquired, :LatestItemInfo) ON CONFLICT ON CONSTRAINT itemownertable_pkey DO UPDATE SET DateAquired = EXCLUDED.DateAquired, LatestItemInfo = EXCLUDED.LatestItemInfo", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("ItemID", NpgsqlDbType.Integer)).Value = _ItemID;
cmd.Parameters.Add(new NpgsqlParameter("SuffixID", NpgsqlDbType.Integer)).Value = _SuffixID;
cmd.Parameters.Add(new NpgsqlParameter("PlayerID", NpgsqlDbType.Integer)).Value = (int)_PlayerID;
cmd.Parameters.Add(new NpgsqlParameter("DateAquired", NpgsqlDbType.Timestamp)).Value = _DateAquired.Value;
cmd.Parameters.Add(new NpgsqlParameter("LatestItemInfo", NpgsqlDbType.Integer)).Value = (int)_LatestItemInfo.Value;
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows == 1)
{
return(true);
}
}
}
finally
{
CloseConnection();
}
return(false);
}
public int GenerateNewPlayerGearDataEntry(PlayerData.GearData _GearData, WowVersionEnum _WowVersion)
{
if (_GearData == null)
{
return(0);
}
Dictionary <ItemSlot, SQLIngameItemID> ingameItemIDs = new Dictionary <ItemSlot, SQLIngameItemID>();
foreach (ItemSlot slot in Enum.GetValues(typeof(ItemSlot)))
{
ingameItemIDs.Add(slot, new SQLIngameItemID(0));
}
foreach (var item in _GearData.Items)
{
SQLIngameItemID itemID = GenerateNewIngameItemEntry(new SQLIngameItemInfo(item.Value));
ingameItemIDs.AddOrSet(item.Key, itemID);
}
int gearDataEntryID = 0;
var conn = OpenConnection();
try
{
using (var cmd = new NpgsqlCommand("INSERT INTO playergeartable(id, head, neck, shoulder, shirt, chest, belt, legs, feet, wrist, gloves, finger_1, finger_2, trinket_1, trinket_2, back, main_hand, off_hand, ranged, tabard) VALUES (DEFAULT, :Head, :Neck, :Shoulder, :Shirt, :Chest, :Belt, :Legs, :Feet, :Wrist, :Gloves, :Finger_1, :Finger_2, :Trinket_1, :Trinket_2, :Back, :Main_Hand, :Off_Hand, :Ranged, :Tabard) RETURNING id", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("Head", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Head];
cmd.Parameters.Add(new NpgsqlParameter("Neck", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Neck];
cmd.Parameters.Add(new NpgsqlParameter("Shoulder", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Shoulder];
cmd.Parameters.Add(new NpgsqlParameter("Shirt", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Shirt];
cmd.Parameters.Add(new NpgsqlParameter("Chest", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Chest];
cmd.Parameters.Add(new NpgsqlParameter("Belt", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Belt];
cmd.Parameters.Add(new NpgsqlParameter("Legs", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Legs];
cmd.Parameters.Add(new NpgsqlParameter("Feet", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Feet];
cmd.Parameters.Add(new NpgsqlParameter("Wrist", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Wrist];
cmd.Parameters.Add(new NpgsqlParameter("Gloves", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Gloves];
cmd.Parameters.Add(new NpgsqlParameter("Finger_1", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Finger_1];
cmd.Parameters.Add(new NpgsqlParameter("Finger_2", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Finger_2];
cmd.Parameters.Add(new NpgsqlParameter("Trinket_1", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Trinket_1];
cmd.Parameters.Add(new NpgsqlParameter("Trinket_2", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Trinket_2];
cmd.Parameters.Add(new NpgsqlParameter("Back", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Back];
cmd.Parameters.Add(new NpgsqlParameter("Main_Hand", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Main_Hand];
cmd.Parameters.Add(new NpgsqlParameter("Off_Hand", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Off_Hand];
cmd.Parameters.Add(new NpgsqlParameter("Ranged", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Ranged];
cmd.Parameters.Add(new NpgsqlParameter("Tabard", NpgsqlDbType.Integer)).Value = (int)ingameItemIDs[ItemSlot.Tabard];
using (var reader = cmd.ExecuteReader())
{
if (reader.Read() == true)
{
gearDataEntryID = reader.GetInt32(0);
}
}
}
if (gearDataEntryID != 0 && _WowVersion != WowVersionEnum.Vanilla)
{
//There may be gems we should add to the geardata!
Dictionary <ItemSlot, SQLGemInfo> gems = new Dictionary <ItemSlot, SQLGemInfo>();
foreach (var item in _GearData.Items)
{
if (item.Value.GemIDs != null)
{
SQLGemInfo gemInfo = new SQLGemInfo(item.Value.GemIDs);
if (gemInfo.IsNull() == false)
{
gems.Add(item.Key, gemInfo);
}
}
}
if (gems.Count > 0)
{
GenerateNewPlayerGearGemEntries(gearDataEntryID, gems);
}
}
}
finally
{
CloseConnection();
}
return(gearDataEntryID);
}
public bool GetIngameItems(IEnumerable <SQLIngameItemID> _IngameItems, out Dictionary <SQLIngameItemID, SQLIngameItemInfo> _ResultItems)
{
var distinctItems = _IngameItems.Distinct();
int[] itemsArray = new int[distinctItems.Count()];
int itemsCounter = 0;
foreach (var item in distinctItems)
{
if (item.ID > 0)
{
itemsArray[itemsCounter++] = item.ID;
}
}
if (itemsCounter != itemsArray.Length)
{
int[] oldItemsArray = itemsArray;
itemsArray = new int[itemsCounter];
for (int i = 0; i < itemsCounter; ++i)
{
itemsArray[i] = oldItemsArray[i];
}
}
var conn = OpenConnection();
try
{
const int ID_COLUMN = 0;
const int ITEMID_COLUMN = 1;
const int ENCHANTID_COLUMN = 2;
const int SUFFIXID_COLUMN = 3;
const int UNIQUEID_COLUMN = 4;
using (var cmd = new NpgsqlCommand("SELECT id, itemid, enchantid, suffixid, uniqueid FROM IngameItemTable WHERE id = ANY(:IDs)", conn))
{
{
var idArrayParam = new NpgsqlParameter("IDs", NpgsqlDbType.Array | NpgsqlDbType.Integer);
idArrayParam.Value = itemsArray;
cmd.Parameters.Add(idArrayParam);
}
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows == true)
{
_ResultItems = new Dictionary <SQLIngameItemID, SQLIngameItemInfo>();
while (reader.Read())
{
SQLIngameItemID sqlID = new SQLIngameItemID(reader.GetInt32(ID_COLUMN));
SQLIngameItemInfo itemInfo = new SQLIngameItemInfo();
itemInfo.ItemID = reader.GetInt32(ITEMID_COLUMN);
itemInfo.EnchantID = reader.GetInt32(ENCHANTID_COLUMN);
itemInfo.SuffixID = reader.GetInt32(SUFFIXID_COLUMN);
itemInfo.UniqueID = reader.GetInt32(UNIQUEID_COLUMN);
_ResultItems.AddOrSet(sqlID, itemInfo);
}
return(true);
}
}
}
}
finally
{
CloseConnection();
}
_ResultItems = null;
return(false);
}