public GEItem getOfferByPlayerSlot(Player p, byte slot) { /* * Gives the player their GrandExchange Items both Buying and Selling are here. * Returns a GEItem[] of all Item's array based on slot id, null array index = space */ long playerHash = p.getLoginDetails().getLongName(); try { int itemId; int amount; int price; bool isSoldNull; bool isBoughtNull; int sold; int bought; int collectedItem; int collectedGold; int overpaid; bool aborted; GEItem geItem = null; SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); SQLiteVdbe preparedStatement = new SQLiteVdbe(db, "SELECT itemId, amount, price, bought, NULL sold, collectedItem, collectedGold, overpaid, aborted FROM grandExchangeBuying WHERE playerHash = ? AND slot = ? UNION SELECT itemId, amount, price, NULL bought, sold, collectedItem, collectedGold, overpaid, aborted FROM grandExchangeSelling WHERE playerHash = ? AND slot = ?"); preparedStatement.Reset(); preparedStatement.BindLong(1, playerHash); preparedStatement.BindInteger(2, slot); preparedStatement.BindLong(3, playerHash); preparedStatement.BindInteger(4, slot); while (preparedStatement.ExecuteStep() != Sqlite3.SQLITE_DONE) { if(preparedStatement.GetLastError() != "") { misc.WriteError("[GrandExchange SQL Error]: " + preparedStatement.GetLastError()); return null; } itemId = preparedStatement.Result_Int(0); amount = preparedStatement.Result_Int(1); price = preparedStatement.Result_Int(2); isBoughtNull = string.IsNullOrEmpty(preparedStatement.Result_Text(3)); isSoldNull = string.IsNullOrEmpty(preparedStatement.Result_Text(4)); collectedItem = preparedStatement.Result_Int(5); collectedGold = preparedStatement.Result_Int(6); overpaid = preparedStatement.Result_Int(7); aborted = Convert.ToBoolean(preparedStatement.Result_Int(8)); if (isSoldNull && !isBoughtNull) { bought = Convert.ToInt32(preparedStatement.Result_Text(3)); geItem = new BuyOffer(itemId, amount, price, bought, collectedItem, collectedGold, overpaid, slot, aborted, playerHash); } else if (isBoughtNull && !isSoldNull) { sold = Convert.ToInt32(preparedStatement.Result_Text(4)); geItem = new SellOffer(itemId, amount, price, sold, collectedItem, collectedGold, overpaid, slot, aborted, playerHash); } else { misc.WriteError("[GrandExchange Error]: sold or bought both are NULL? how this happen?"); } } db.CloseDatabase(); return geItem; } catch (Exception e) { misc.WriteError("[GrandExchange Error]: " + e.Message); } return null; }
public GEItem getOfferByPlayerSlot(Player p, byte slot) { /* * Gives the player their GrandExchange Items both Buying and Selling are here. * Returns a GEItem[] of all Item's array based on slot id, null array index = space */ long playerHash = p.getLoginDetails().getLongName(); try { int itemId; int amount; int price; bool isSoldNull; bool isBoughtNull; int sold; int bought; int collectedItem; int collectedGold; int overpaid; bool aborted; GEItem geItem = null; SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); SQLiteVdbe preparedStatement = new SQLiteVdbe(db, "SELECT itemId, amount, price, bought, NULL sold, collectedItem, collectedGold, overpaid, aborted FROM grandExchangeBuying WHERE playerHash = ? AND slot = ? UNION SELECT itemId, amount, price, NULL bought, sold, collectedItem, collectedGold, overpaid, aborted FROM grandExchangeSelling WHERE playerHash = ? AND slot = ?"); preparedStatement.Reset(); preparedStatement.BindLong(1, playerHash); preparedStatement.BindInteger(2, slot); preparedStatement.BindLong(3, playerHash); preparedStatement.BindInteger(4, slot); while (preparedStatement.ExecuteStep() != Sqlite3.SQLITE_DONE) { if (preparedStatement.GetLastError() != "") { misc.WriteError("[GrandExchange SQL Error]: " + preparedStatement.GetLastError()); return(null); } itemId = preparedStatement.Result_Int(0); amount = preparedStatement.Result_Int(1); price = preparedStatement.Result_Int(2); isBoughtNull = string.IsNullOrEmpty(preparedStatement.Result_Text(3)); isSoldNull = string.IsNullOrEmpty(preparedStatement.Result_Text(4)); collectedItem = preparedStatement.Result_Int(5); collectedGold = preparedStatement.Result_Int(6); overpaid = preparedStatement.Result_Int(7); aborted = Convert.ToBoolean(preparedStatement.Result_Int(8)); if (isSoldNull && !isBoughtNull) { bought = Convert.ToInt32(preparedStatement.Result_Text(3)); geItem = new BuyOffer(itemId, amount, price, bought, collectedItem, collectedGold, overpaid, slot, aborted, playerHash); } else if (isBoughtNull && !isSoldNull) { sold = Convert.ToInt32(preparedStatement.Result_Text(4)); geItem = new SellOffer(itemId, amount, price, sold, collectedItem, collectedGold, overpaid, slot, aborted, playerHash); } else { misc.WriteError("[GrandExchange Error]: sold or bought both are NULL? how this happen?"); } } db.CloseDatabase(); return(geItem); } catch (Exception e) { misc.WriteError("[GrandExchange Error]: " + e.Message); } return(null); }
protected void processSales() { OrderedDictionary soldBoughtItems = new OrderedDictionary(); /* * First of all the old system was not possible to do with SQL * Would of required to do a SQL Query for each itemId sold and bought by all players * That would of ment over 10,000 items x 2 (buyers+sellers) so 20,000 queries to process. * * The new system I created to fix this. * Works like this it everytime this runs matches up 1 GE Buying/Selling auction. * Then loops it 100 times or until no more matches are possible. * * TODO: Match Buyer's of larger item quantity with Sellers with larger quanity instead of Sellers with * any item quantity. (this requires editing the query below to a better suited query). */ BuyOffer buyer = null; SellOffer seller = null; int itemId; int itemAmount; int price; int sold; int bought; int collectedItem; int collectedGold; int overpaid; byte slot; long playerHash; SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); for (int processNumSales = 0; processNumSales < 100; processNumSales++) { soldBoughtItems.Clear(); //Can't do more then 1 sale at a time, LIMIT 100 wont work since it will pick duplicate sellers. DataTable dt = db.ExecuteQuery("SELECT S.itemId AS sell_itemId, S.amount AS sell_amount, S.price AS sell_price, S.sold AS sell_sold, S.collectedItem AS sell_collectedItem, S.collectedGold AS sell_collectedGold, S.overpaid AS sell_overpaid, S.slot AS sell_slot, S.playerHash AS sell_playerHash, B.itemId AS buy_itemId, B.amount AS buy_amount, B.price AS buy_price, B.bought AS buy_bought, B.collectedItem AS buy_collectedItem, B.collectedGold AS buy_collectedGold, B.overpaid AS buy_overpaid, B.slot AS buy_slot, B.playerHash AS buy_playerHash FROM grandExchangeBuying AS B, grandExchangeSelling AS S ON B.itemId = S.itemId AND B.aborted = 0 AND S.aborted = 0 AND B.price >= S.price AND S.sold < S.amount AND B.bought < B.amount ORDER BY B.price DESC LIMIT 1"); if (dt.Rows.Count == 0) { db.CloseDatabase(); return; } else { itemId = Convert.ToInt32(dt.Rows[0]["sell_itemId"]); itemAmount = Convert.ToInt32(dt.Rows[0]["sell_amount"]); price = Convert.ToInt32(dt.Rows[0]["sell_price"]); sold = Convert.ToInt32(dt.Rows[0]["sell_sold"]); collectedItem = Convert.ToInt32(dt.Rows[0]["sell_collectedItem"]); collectedGold = Convert.ToInt32(dt.Rows[0]["sell_collectedGold"]); overpaid = Convert.ToInt32(dt.Rows[0]["sell_overpaid"]); slot = Convert.ToByte(dt.Rows[0]["sell_slot"]); playerHash = (long)dt.Rows[0]["sell_playerHash"]; seller = new SellOffer(itemId, itemAmount, price, sold, collectedItem, collectedGold, overpaid, slot, playerHash); itemId = Convert.ToInt32(dt.Rows[0]["buy_itemId"]); itemAmount = Convert.ToInt32(dt.Rows[0]["buy_amount"]); price = Convert.ToInt32(dt.Rows[0]["buy_price"]); bought = Convert.ToInt32(dt.Rows[0]["buy_bought"]); collectedItem = Convert.ToInt32(dt.Rows[0]["buy_collectedItem"]); collectedGold = Convert.ToInt32(dt.Rows[0]["buy_collectedGold"]); overpaid = Convert.ToInt32(dt.Rows[0]["buy_overpaid"]); slot = Convert.ToByte(dt.Rows[0]["buy_slot"]); playerHash = (long)dt.Rows[0]["buy_playerHash"]; buyer = new BuyOffer(itemId, itemAmount, price, bought, collectedItem, collectedGold, overpaid, slot, playerHash); } if (seller == null || buyer == null) continue; int amountToBuy = buyer.getTotalAmount() - buyer.getAmountTraded(); int amountToSell = seller.getTotalAmount() - seller.getAmountTraded(); // This check will never happen. SQL Query will not allow it.. Just old code I left in. if (amountToBuy <= 0 || amountToSell <= 0) continue; int amount = (amountToBuy > amountToSell) ? amountToSell : amountToBuy; // Buys from Seller a random amount of a item. amount = misc.random(1, amount); // Buyer will pay minimum what the seller wants. int amountBuyerOverpaid = (buyer.getPriceEach() - seller.getPriceEach()) * amount; // buyer is paying more than the seller wants, therefore MAY recieve this amount as a refund. bool buyerKeepsRefund = misc.random(1) == 0; // if 0, the buyer gets a refund, if its 1...the seller gets more. buyer.setAmountTraded(buyer.getAmountTraded() + amount); seller.setAmountTraded(seller.getAmountTraded() + amount); /** * How much refunded gold Buyer gets in addition to his previous refunded gold. * or * How much of the Buyer's overpaid gold that they couldn't keep goes as extra profit to the Seller. */ if (buyerKeepsRefund && amountBuyerOverpaid > 0) buyer.setAmountOverpaid(buyer.getAmountOverpaid() + amountBuyerOverpaid); else if (!buyerKeepsRefund && amountBuyerOverpaid > 0) seller.setAmountOverpaid(seller.getAmountOverpaid() + amountBuyerOverpaid); // Shows amount of Item Buyer bought in Slot 1 minus how much he already took out. if (buyer.getAmountItemsLeftToCollect() > 0) buyer.setSlot1(new Item(buyer.getItem(), buyer.getAmountItemsLeftToCollect())); //Shows amount of Gold Buyer has in Slot 2 from previous refunded money plus how much he maybe will be refunded this sale. if ((buyer.getAmountCollectedGold() < buyer.getAmountOverpaid()) && (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold()) > 0) buyer.setSlot2(new Item(995, (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold()))); //Shows amount of Gold Seller in Slot 2 has minus how much he already took out. if (seller.getAmountGoldLeftToCollect() > 0) seller.setSlot2(new Item(995, seller.getAmountGoldLeftToCollect())); Player buyerP = Server.getPlayerForName(buyer.getPlayerName()); Player sellerP = Server.getPlayerForName(seller.getPlayerName()); //Update both Buyer and Seller's Slots in the database try { db.ExecuteNonQuery("UPDATE grandExchangeBuying SET bought = " + buyer.getAmountTraded() + ", overpaid = " + buyer.getAmountOverpaid() + " WHERE slot = " + buyer.getSlot() + " AND playerHash = " + buyer.getPlayerHash()); db.ExecuteNonQuery("UPDATE grandExchangeSelling SET sold = " + seller.getAmountTraded() + ", overpaid = " + seller.getAmountOverpaid() + " WHERE slot = " + seller.getSlot() + " AND playerHash = " + seller.getPlayerHash()); } catch (Exception e) { //Instantly skip this sale if any database errors occurred. //If one of those queries works and other fails then you will have a dupe -_-. //TODO: A fix would be to keep retrying both queries until both successful. misc.WriteError("[GrandExchange SQL Error]: " + e.Message); continue; } /* * TODO: Fix When player is offline and logs in after item is bought. He won't see messages below.. * Add something to Packets.cs (sendLogin() method) to process this. * Maybe a (boolean in playerSaves for GE update?) */ if (buyerP != null) { buyerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated."); buyerP.getPackets().updateGEProgress(buyer); if (buyerP.getGESession() != null) { Item[] items = { buyer.getSlot1(), buyer.getSlot2() }; buyerP.getPackets().sendItems(-1, -1757, 523 + buyer.getSlot(), items); } } /* * TODO: Fix When player is offline and logs in after item is bought. He won't see messages below.. * Add something to Packets.cs (sendLogin() method) to process this. * Maybe a (boolean in playerSaves for GE update?) */ if (sellerP != null) { sellerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated."); sellerP.getPackets().updateGEProgress(seller); if (sellerP.getGESession() != null) { Item[] items = { seller.getSlot1(), seller.getSlot2() }; sellerP.getPackets().sendItems(-1, -1757, 523 + seller.getSlot(), items); } } } }
protected void processSales() { OrderedDictionary soldBoughtItems = new OrderedDictionary(); /* * First of all the old system was not possible to do with SQL * Would of required to do a SQL Query for each itemId sold and bought by all players * That would of ment over 10,000 items x 2 (buyers+sellers) so 20,000 queries to process. * * The new system I created to fix this. * Works like this it everytime this runs matches up 1 GE Buying/Selling auction. * Then loops it 100 times or until no more matches are possible. * * TODO: Match Buyer's of larger item quantity with Sellers with larger quanity instead of Sellers with * any item quantity. (this requires editing the query below to a better suited query). */ BuyOffer buyer = null; SellOffer seller = null; int itemId; int itemAmount; int price; int sold; int bought; int collectedItem; int collectedGold; int overpaid; byte slot; long playerHash; SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); for (int processNumSales = 0; processNumSales < 100; processNumSales++) { soldBoughtItems.Clear(); //Can't do more then 1 sale at a time, LIMIT 100 wont work since it will pick duplicate sellers. DataTable dt = db.ExecuteQuery("SELECT S.itemId AS sell_itemId, S.amount AS sell_amount, S.price AS sell_price, S.sold AS sell_sold, S.collectedItem AS sell_collectedItem, S.collectedGold AS sell_collectedGold, S.overpaid AS sell_overpaid, S.slot AS sell_slot, S.playerHash AS sell_playerHash, B.itemId AS buy_itemId, B.amount AS buy_amount, B.price AS buy_price, B.bought AS buy_bought, B.collectedItem AS buy_collectedItem, B.collectedGold AS buy_collectedGold, B.overpaid AS buy_overpaid, B.slot AS buy_slot, B.playerHash AS buy_playerHash FROM grandExchangeBuying AS B, grandExchangeSelling AS S ON B.itemId = S.itemId AND B.aborted = 0 AND S.aborted = 0 AND B.price >= S.price AND S.sold < S.amount AND B.bought < B.amount ORDER BY B.price DESC LIMIT 1"); if (dt.Rows.Count == 0) { db.CloseDatabase(); return; } else { itemId = Convert.ToInt32(dt.Rows[0]["sell_itemId"]); itemAmount = Convert.ToInt32(dt.Rows[0]["sell_amount"]); price = Convert.ToInt32(dt.Rows[0]["sell_price"]); sold = Convert.ToInt32(dt.Rows[0]["sell_sold"]); collectedItem = Convert.ToInt32(dt.Rows[0]["sell_collectedItem"]); collectedGold = Convert.ToInt32(dt.Rows[0]["sell_collectedGold"]); overpaid = Convert.ToInt32(dt.Rows[0]["sell_overpaid"]); slot = Convert.ToByte(dt.Rows[0]["sell_slot"]); playerHash = (long)dt.Rows[0]["sell_playerHash"]; seller = new SellOffer(itemId, itemAmount, price, sold, collectedItem, collectedGold, overpaid, slot, playerHash); itemId = Convert.ToInt32(dt.Rows[0]["buy_itemId"]); itemAmount = Convert.ToInt32(dt.Rows[0]["buy_amount"]); price = Convert.ToInt32(dt.Rows[0]["buy_price"]); bought = Convert.ToInt32(dt.Rows[0]["buy_bought"]); collectedItem = Convert.ToInt32(dt.Rows[0]["buy_collectedItem"]); collectedGold = Convert.ToInt32(dt.Rows[0]["buy_collectedGold"]); overpaid = Convert.ToInt32(dt.Rows[0]["buy_overpaid"]); slot = Convert.ToByte(dt.Rows[0]["buy_slot"]); playerHash = (long)dt.Rows[0]["buy_playerHash"]; buyer = new BuyOffer(itemId, itemAmount, price, bought, collectedItem, collectedGold, overpaid, slot, playerHash); } if (seller == null || buyer == null) { continue; } int amountToBuy = buyer.getTotalAmount() - buyer.getAmountTraded(); int amountToSell = seller.getTotalAmount() - seller.getAmountTraded(); // This check will never happen. SQL Query will not allow it.. Just old code I left in. if (amountToBuy <= 0 || amountToSell <= 0) { continue; } int amount = (amountToBuy > amountToSell) ? amountToSell : amountToBuy; // Buys from Seller a random amount of a item. amount = misc.random(1, amount); // Buyer will pay minimum what the seller wants. int amountBuyerOverpaid = (buyer.getPriceEach() - seller.getPriceEach()) * amount; // buyer is paying more than the seller wants, therefore MAY recieve this amount as a refund. bool buyerKeepsRefund = misc.random(1) == 0; // if 0, the buyer gets a refund, if its 1...the seller gets more. buyer.setAmountTraded(buyer.getAmountTraded() + amount); seller.setAmountTraded(seller.getAmountTraded() + amount); /** * How much refunded gold Buyer gets in addition to his previous refunded gold. * or * How much of the Buyer's overpaid gold that they couldn't keep goes as extra profit to the Seller. */ if (buyerKeepsRefund && amountBuyerOverpaid > 0) { buyer.setAmountOverpaid(buyer.getAmountOverpaid() + amountBuyerOverpaid); } else if (!buyerKeepsRefund && amountBuyerOverpaid > 0) { seller.setAmountOverpaid(seller.getAmountOverpaid() + amountBuyerOverpaid); } // Shows amount of Item Buyer bought in Slot 1 minus how much he already took out. if (buyer.getAmountItemsLeftToCollect() > 0) { buyer.setSlot1(new Item(buyer.getItem(), buyer.getAmountItemsLeftToCollect())); } //Shows amount of Gold Buyer has in Slot 2 from previous refunded money plus how much he maybe will be refunded this sale. if ((buyer.getAmountCollectedGold() < buyer.getAmountOverpaid()) && (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold()) > 0) { buyer.setSlot2(new Item(995, (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold()))); } //Shows amount of Gold Seller in Slot 2 has minus how much he already took out. if (seller.getAmountGoldLeftToCollect() > 0) { seller.setSlot2(new Item(995, seller.getAmountGoldLeftToCollect())); } Player buyerP = Server.getPlayerForName(buyer.getPlayerName()); Player sellerP = Server.getPlayerForName(seller.getPlayerName()); //Update both Buyer and Seller's Slots in the database try { db.ExecuteNonQuery("UPDATE grandExchangeBuying SET bought = " + buyer.getAmountTraded() + ", overpaid = " + buyer.getAmountOverpaid() + " WHERE slot = " + buyer.getSlot() + " AND playerHash = " + buyer.getPlayerHash()); db.ExecuteNonQuery("UPDATE grandExchangeSelling SET sold = " + seller.getAmountTraded() + ", overpaid = " + seller.getAmountOverpaid() + " WHERE slot = " + seller.getSlot() + " AND playerHash = " + seller.getPlayerHash()); } catch (Exception e) { //Instantly skip this sale if any database errors occurred. //If one of those queries works and other fails then you will have a dupe -_-. //TODO: A fix would be to keep retrying both queries until both successful. misc.WriteError("[GrandExchange SQL Error]: " + e.Message); continue; } /* * TODO: Fix When player is offline and logs in after item is bought. He won't see messages below.. * Add something to Packets.cs (sendLogin() method) to process this. * Maybe a (boolean in playerSaves for GE update?) */ if (buyerP != null) { buyerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated."); buyerP.getPackets().updateGEProgress(buyer); if (buyerP.getGESession() != null) { Item[] items = { buyer.getSlot1(), buyer.getSlot2() }; buyerP.getPackets().sendItems(-1, -1757, 523 + buyer.getSlot(), items); } } /* * TODO: Fix When player is offline and logs in after item is bought. He won't see messages below.. * Add something to Packets.cs (sendLogin() method) to process this. * Maybe a (boolean in playerSaves for GE update?) */ if (sellerP != null) { sellerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated."); sellerP.getPackets().updateGEProgress(seller); if (sellerP.getGESession() != null) { Item[] items = { seller.getSlot1(), seller.getSlot2() }; sellerP.getPackets().sendItems(-1, -1757, 523 + seller.getSlot(), items); } } } }