Reseller store

[Legacy_Lazarus Magni]

               We have a system that ties items but into loot dividers to a store (actually two, one for lower lvl gear and one for higher), but it doesn't quite work right.

It uses a MySQL database table to store the information persistently (i.e. between server restarts).

Each of the tables stores 100 items. The original intent was that as items were divided, the stores would fill up. Once they reached capacity (100 items), the oldest item would be replaced by the newest.

Unfortunately what seems to happen is that the most expesive items replace the lesser. It does fill up to 100 properly, but as more expensive items are added they push out the lesser. Regardless of their age in the store.

Anyone have any ideas here?

This is the script:

#include "aps_include"
#include "conf_module"
const int K_COMMERCE_MIN_VALUE = 1;
const int K_COMMERCE_MAX_VALUE = 500000;
const string K_COMMERCE_TAG = "CommerceSeller"; // low value reseller store tag
const string K_LOOTRESELLER_TAG = "LootReseller"; // high value reseller store tag
const string K_DONATION_TAG = "jk_donationchest"; // donation chest tag
const string K_COMMERCE_TABLE = "reseller";
const string K_RESELLER_TABLE = "reseller2";
const string K_DONATION_TABLE = "donate";
//const string K_DIVIDER_TAG = "LootDivider" // For reference only
//----------------  PROTOTYPES  -----------------------------------------
// * oUser: The one using the divider
// * oDivider: The device used to divide the loot
void DivideLoot(object oUser, object oDivider);
// * Used in OnModuleLoad to activate a Persistent merchant.
// * sTag is the tag of the merchant (not the creature)
void ActivateShop(string sTag);
// * Call this to update the DB with the new shop's content
void DBSaveShopInventory(object oShop);

//---------------  DEFINITIONS  -----------------------------------------
int ValidateItem(object oItem)
{
   if(GetHasInventory(oItem) || GetPlotFlag(oItem)) return FALSE;
   switch(GetBaseItemType(oItem))
   {
      case BASE_ITEM_GOLD:
      case BASE_ITEM_HEALERSKIT:
      case BASE_ITEM_KEY:
      case BASE_ITEM_POTIONS:
      case BASE_ITEM_SPELLSCROLL:
      case BASE_ITEM_THIEVESTOOLS:
      case BASE_ITEM_TORCH:
      case BASE_ITEM_TRAPKIT:
      case BASE_ITEM_BOOK:
      case BASE_ITEM_SCROLL: return FALSE;
   }
   return TRUE;
}

void DivideLoot(object oUser, object oDivider)
{
   object oItem = GetFirstItemInInventory(oDivider);
   int iGold = 0;
   string sShopTag;
   object oShop, oShopMatch, oCopy;
   int nCom = 0, nRes = 0, nIden;
   while(GetIsObjectValid(oItem))
   {
      if(ValidateItem(oItem))
      {
          SetIdentified(oItem, TRUE);
          int nValue = GetGoldPieceValue(oItem);
          if(nValue > 0)
          {
             if(nValue <= K_COMMERCE_MAX_VALUE && nValue >= K_COMMERCE_MIN_VALUE)
             {
                sShopTag = K_COMMERCE_TAG;
                ++nCom;
             }
             else if(nValue > K_COMMERCE_MAX_VALUE)
             {
                sShopTag = K_LOOTRESELLER_TAG;
                ++nRes;
             }
             iGold += nValue/2;
             oShop = GetObjectByTag(sShopTag);
             oShopMatch = GetItemPossessedBy(oShop, GetTag(oItem));
             if(oShopMatch != OBJECT_INVALID) DestroyObject(oShopMatch);
             oCopy = CopyItem(oItem, oShop);
          }
          DestroyObject(oItem);
      }
      else DestroyObject(oItem);
      oItem = GetNextItemInInventory(oDivider);
   }
   object oParty = GetFirstFactionMember(oUser, TRUE);
   int iParty = 0;
   while(GetIsObjectValid(oParty))
   {
       iParty++;
       oParty = GetNextFactionMember(oUser, TRUE);
   }
   iGold /= iParty;
   if(iGold < 0) iGold = 1;
   oParty = GetFirstFactionMember(oUser, TRUE);
   while(GetIsObjectValid(oParty))
   {
       GiveGoldToCreature(oParty, iGold);
       oParty = GetNextFactionMember(oUser, TRUE);
   }
   DeleteLocalObject(oDivider, "LastUser");
   if(nCom > 0) DelayCommand(0.3, DBSaveShopInventory(GetObjectByTag(K_COMMERCE_TAG)));
   if(nRes > 0) DelayCommand(1.1, DBSaveShopInventory(GetObjectByTag(K_LOOTRESELLER_TAG)));
}
void DBSaveShopInventory(object oShop)
{
   if(!GetIsObjectValid(oShop)) return;
   string sTag = GetTag(oShop);
   string sTable = K_COMMERCE_TABLE;
   if(sTag == K_LOOTRESELLER_TAG) sTable = K_RESELLER_TABLE;
   else if(sTag == K_DONATION_TAG) sTable = K_DONATION_TABLE;
   // Delete all DB entries for the passed store
   SQLExecDirect("TRUNCATE TABLE "+sTable);
   int nNth = 0;
   object oItem = GetFirstItemInInventory(oShop); // This returns the last item put in inventory
   while(oItem != OBJECT_INVALID)
   {
      // Loop over store items starting from newest to oldest,
      // and delete all items older than the 100 first ones, then
      // store the left items in DB, nNth being the id(or age) of the item.
      // This means that the higher the ID, the older the item.
      if(++nNth > MODULE_MAX_SAVED_SHOP_ITEMS) DestroyObject(oItem);
      else
      {
         SQLExecDirect("INSERT INTO "+sTable+" SET id="+IntToString(nNth)+", ref='"+GetResRef(oItem)+
         "', stack="+IntToString(GetItemStackSize(oItem))+", charges=" + IntToString(GetItemCharges(oItem)));
      }
      oItem = GetNextItemInInventory(oShop);
   }
}
void DBLoadShopInventory(object oShop)
{
   string sTag = GetTag(oShop);
   string sTable = K_COMMERCE_TABLE;
   if(sTag == K_LOOTRESELLER_TAG) sTable = K_RESELLER_TABLE;
   else if(sTag == K_DONATION_TAG) sTable = K_DONATION_TABLE;
   object oItem;
   int nCharges;
   // Retrieve DB items from oldest to newest in order
   // to preserve the store item order.
   SQLExecDirect("SELECT ref, stack, charges FROM "+sTable+" ORDER BY id DESC");
   while(SQLFetch())
   {
      oItem = CreateItemOnObject(SQLGetData(1), oShop, StringToInt(SQLGetData(2)));
      nCharges = StringToInt(SQLGetData(3));
      if(nCharges > 0) SetItemCharges(oItem, nCharges);
      SetIdentified(oItem, TRUE);
   }
}
void ActivateShops()
{
   DBLoadShopInventory(GetObjectByTag(K_COMMERCE_TAG));
   DelayCommand(1.3, DBLoadShopInventory(GetObjectByTag(K_LOOTRESELLER_TAG)));
   DelayCommand(2.2, DBLoadShopInventory(GetObjectByTag(K_DONATION_TAG)));
}
//void main(){}
               
               

               
            

[Legacy_FunkySwerve]

               Paste a describe of the table and I'll have a look. On a very quick scan, my first inclination is to suspect the TRUNCATE. We've never wiped entries that way - much better to DELETE FROM, I suspect, though I'll have to read up on it to be sure..

Funky
               
               

               

                     Modifié par FunkySwerve, 19 septembre 2013 - 03:14 .
                     
                  

            

[Legacy_Lazarus Magni]

               Is this what you mean Funky?

id   TINYINT(3)      NN UN
ref   VARCHAR(16)    PK NN
id   SMALLINT(5)   NN UN   Default ‘1’
charges   TINYINT   NN UN   Default ‘0’
               
               

               
            

[Legacy_FunkySwerve]

               Basically, yeah. I'll take a look if that's all of them. Sorry, I tend to forget that not everyone does command-line. In command line, you would just type:
describe <table name>;
to get a printout of it's description. For example:

mysql> describe pcvars;
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
| Field    | Type                                                      | Null | Key | Default           | Extra |
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
| pcv_uid  | varchar(32)                                               | NO   | PRI | NULL              |       |
| pcv_type | enum('int','float','string','object','vector','location') | NO   | PRI | NULL              |       |
| pcv_key  | varchar(64)                                               | NO   | PRI | NULL              |       |
| pcv_val  | text                                                      | YES  |     | NULL              |       |
| pcv_last | timestamp                                                 | NO   |     | CURRENT_TIMESTAMP |       |
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
5 rows in set (0.00 sec)

I'll get back to you on that code this evening, when I have some free time.

Funky
               
               

               
            

[Legacy_Lazarus Magni]

               No rush, and thanks for looking into this Funky.
               
               

               
            

[Legacy_FunkySwerve]

               Ok, it's not the TRUNCATE - that's actually a good use for it, save for the fact that you NEED that table, with a date column, to sort items by date, and DELETE FROM accordingly. You seem to be relying on GetFirst/NextItemInInventory to keep an accurate chronology, and it won't. Anything that causes the shop to reacquire items will also result in a reordering. By the sound of it, the shop is arranging them in order of value, though I haven't tested that. Either way, you should really be using SQL to ORDER BY a date field, if it's dates you care about.

Funky
               
               

               
            

[Legacy_Lazarus Magni]

               I am pretty sure it is sorting by value because, when I clear the table, and restart, initially we get all kinds of items in the shop (rings, amulets, ect...) But after it hits 100 items it starts pushing out less valuable for more. And because of our item guidelines, certain items are allowed to be higher gp value than others (particularially armors, robes, and weapons), subsequently (depending on player activity) in not to long, the shop only has those more expensive items.

Any chance you could post up some script for this? I did not make this script, but sadly the scripter who did (who did some incredible things for us) is no longer available (and it's way beyond my capacity.)
               
               

               
            

[Legacy_FunkySwerve]

               No, sorry, it'd mean a rewrite of your system, and I don't have enough dev time for even my own server at the moment. You could use this as an opportunity to learn, however - I learned by doing, myself. '>

Funky
               
               

               
            

[Legacy_Lazarus Magni]

               Heh, well thanks for pointing the way Funky. I work full time, have an amazing girlfriend, amazing friends and family, my "home" to work on, so... spending months or years how to learn this (especially since it is so far removed from what I have already spent years and thousands of dollars learning educationally... eg. physical sciences like botany and microbiology;;; IT stuff is a whole nother world...) As soon as I have that time again I will get right on that.

Untill then I will seek the help from the NWN1 community to help us... It's just beyond my capacity to do I fear.

I have never claimed to be some super guru, NWN expert. In fact all of the amazing things we have acomplished with Av3 are due to some amazingly gifted players, scripters, and builders.... I am a sad excuse for any of the above.  Which is exactly why I commend the NWN community for what we have done. Especially in my generation of hosting Av (Av1 and 2 had much more knowledgable and gifted hosts).... everything we have done, and much has been amazing, is due to the outstanding help we have had from, yes the Av3 community, but even more so the NWN 1 community at large.

In fact that is a big part of why I love this game. This game allow us to do things with it, and express our creativity, and what we as players/consumers think should be as a video game we want to play. Those with the knowledge to make these things happen for the PWs out there, are what keep this dream alive. If the "powers that be" want to help this to be manifested I know (from past experience) it will happen, and the NWN 1 community will step forward to make it so. If not, so be it,  not all good ideas come to fruition.

Your pointing the way is appreciated though Funky.
               
               

               

                     Modifié par Lazarus Magni, 21 septembre 2013 - 01:32 .
                     
                  

            

[Legacy_WhiZard]

               GetFirst/GetNextItemInInventory() should be going newest to oldest for each inventory tab of the store (only for stores do these tabs matter).  Weapons should be the first tab (that means they are gotten first) and weapon prices usually exceed the price for other items, which is why you are noticing lower priced items being displaced