예제 #1
0
        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);
        }
예제 #2
0
        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);
        }
예제 #3
0
        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);
        }