HOWTO - Create Custom Merchant with Custom Prices

[Zengez]

Ok, so i finally succeeded in creating a custom merchant with custom prices on my linux box, and although all the information is here, it seems to be spread out a bit and i thought i'd do a quick write-up and synthisize the info for anyone who doesn't already know how to do this.... worst case it just get's burried in the back of the forum topic's lists or gets deleted /shrug



Ok, this tutorial is going to be using straight-up mysql prompt on the linux box, which is what i was using... never downloaded any of the mysql front end or anything but i'd imagine it's much the same, so they should all more or less work with the commands i'll give as examples...


First off, you need to make yer custom merchant's merchantid table... this is simple enough...

First, you need all the itemid's of the items you're going to put on your merchant... this is simple enough; I did it by logging into the game and doing a #finditem (item name) to get the item id # for the item... if you like you can type it into the mysql cmd line using


SELECT id from ITEMS WHERE name = '(item name)';


If you only know part of the name, use a Like command


SELECT id from ITEMS WHERE name LIKE '(part of item name)%';


Note that the % should be where the part of the name you don't know is.... so if you want to know something like an iron ration, or a iron ration or something you could do '%ration' for example...

This also means you can glue bits and peices together with %'s between, ... % just means to the command 'and something else where i don't know what' so if you were lookin for an item named Blighted Robe you could do a Like Bl%ted%b% and it would come up, since the first % includes all possible characters between Bl and ted... so Bl igh ted would be returned... this will help if you remember bits and peices of an item name.

Ok, once you get the item returned (whether in game or in the above search query) it should have the item id off to the left, a number... for example 1233 is a blighted robe (random one i seem to have accidently memorized... not that i play necros or anything ;))

Now, repeate these steps and write down a list of all the item id's that you want to have on yer merchant...

Next; open up a text editor (or, if your non ghetto, try a real editor, i used notepad until i realised i was gonna be doin this alot, then i switched over to ultraedit32... anything that can save in .sql or .txt will work however)

ok, type in 'use (name of database)' in my case my DataBase (DB) is named eq, so i have 'use eq' at the top... not really sure if this is needed, but i find mysql to do wierd things so redundancy never hurts.

Go to the next line and type;


insert into merchantlist (merchantid,slot,item) values;


after values type in (merchantid-number, slot, itemidnumber); Make sure to add the semi-colon or your gonna be in a world of hurt

So lets assume i want to impliment a backpack (itemid 17969 in my DB) into merchantid# 56008, on slot 1 of the merchant, the line would look like;


insert into merchantlist (merchantid,slot,item) values (56008,1,17969);


Repeate this step changing the slot number only to create a complete merchant list... i.e. keep the merchant id number the same for every item you want the same merchant to be selling.

Thus if i want a merchant to sell a bunch of bags it'd be...


insert into merchantlist (merchantid,slot,item) values (56008,1,17969);
insert into merchantlist (merchantid,slot,item) values (56008,2,17403);
insert into merchantlist (merchantid,slot,item) values (56008,3,17359);

Where 17969, 17403, and 17359 are all bags that i wish the same vendor to be selling.

Ok, once you finish your file with every item you wish assigned a slot number, save the file as something you can easily remember, and bring up your mysql cmd line on your linux box...

Type in source /(location of file)/filename

So, in the above case, had i saved the file as 'bagmerchant.txt' under the folder 'eq' i'd type


source /eq/bagmerchant.txt;


This pre-supposes you have the file saved on the same computer, which isn't necessarily true for your linux box... if it's saved elsewhere you should know how to specify the location of the file to your own box, but it'll vary with your network setup, so that's for somewhere else... for the sake of this tutorial i suggest just putting it on the same box if you can /shrug

Ok, congradulations, you have now created your own merchantid with all of your own custom merchant information.

Next step is spawning the merchant:

Go in-game with a gm-flaged character with the #spawn ability. Type #spawn to get the order of information you need to place, for more info on this, see this great How-To: http://www.eqemulator.net/forums/viewtopic.php?t=8225

Now you should see how to create a spawn... simply put in the desired race/hp/level etc, for the sake of this tutorial the only important fields are the (Class) and the (Merchant_id) fields.

In order to create your vendor, you must put 41 as the desired class (merchant) and the merchant_id must be the same id you specified in the above file... thus had you used merchant_id of 56007, you must imput 56007 as the merchant_id field.

So, if i were to spawn a human female merchant with the name "My Merchant" selling the above bags at lvl 75 with 1 million hp it would look like;


#spawn My_Merchant 1 75 1 1000000 1 41 0 0 56008


This makes a vendor named My Merchant (_ become spaces) of race 1 (human) level 75 material 1 (see spawn tutorial for more info on this) with 1000000 hit points, gender 1 (female) class 41 (merchant) wielding no visual weapons and a merchant id of 56008.

Congradulations, you have spawned a vendor!!

Now to keep the vendor in that place you spawned it, and to make it stay there when the zone shuts down and comes back up again, type #npcspawn create and then #npcspawn add, now the vendor is in your spawn database and will remain until deleted!

See next Post for information on how to customize item costs and sellback value

[Zengez]

Ok, so you got your merchant up and want to now set new prices for the items you've put on him... or maybe you want items he sells to sell back for roughly the same price he sells them for... or maybe you just want to totally revamp your item DB's cost/sellback values all together.... here's how!

To understand how to do this i'll write a short bit on the conceptual bit (such as it is) to make it clear to even the least code-experienced of us...

Under the "Items" database (the data that contains all the info for all the items of your world) there are a series of collums, each having a value asigned for every item; thus every item has a 'NODROP' collum and every item has 'Damage' collum, even if it's a ration that is both dropable and has no damage value to it.

The way the information is then gathered, is it takes the value from these collumns to judge whether or not that item has relevant parts to draw from... thus if an item is NODROP, it's NODROP collumn will be set to represent as such (255 i believe?), and likewise if it is not, it will be flagged with a different number which says as such (1 is not NODROP) So if you look up the NODROP collumn on a "ration" you will recieve "1" as the answer... because it is not nodrop. Likewise, in the case of damage, the effective damage of the "ration" is 0, because it has no damage.

Ok, now, assuming you understand that, we can now manipulate the database as we see fit. I will give two types of change, one for making a certain item different in cost, and the other to manipulate the entire DB according to some formula.


Ok, first off, if you wish to make a certain item sell for a certain value, you must look up said item to either get the proper full name, or even better the item-id number (some items, such as bone chips, have the same name as a dozen other items, but if you want only the non-lore bone chips, you'd need to use the item-id number.)

Let's assume you're going to use the item "Blighted Robe" (Item-ID 1233 in my DB) and you just put it on your customized merchant and were discouraged when it sold for 2 copper.... so now you want to change the item cost. Well, theres two ways to do this, either set it a flat rate you think is accurate, or devise some type of formula in order to assign it a value based on it's characteristics of your choosing.

So, if you wished to set it a flat rate, it's a simple matter of 'updating' the cost collumn of the item; as followes


UPDATE items SET cost = (item cost in copper) Where id = 1233;


This ensures that the item you just looked up by it's ID (1233, Blighted Robe) is set to the cost you typed in, since it will be the only one to match the item id where clause.... Basically this line says "Find all items where the item id is 1233, and replace the cost value with this number." Since item id numbers are unique (in theory) you will only replace the intended item's cost. It is important to note that this cost is in copper... so changing it to '130' won't mean it's 130 plat, but rather 1 gold and 3 silver (130 copper)

Ok, now lets say you want to update it using a formula... the first step is to devise the formula you wish to create by using the relevant statistics you wish to base it upon.

The easy way to do this is to go to your mysql cmd line and type "Pager less;" (i believe thats correct, might be "Set Pager Less;") and then type


Select * from items;


This will take a minute because you're basically asking it to give you everything in the item table, but if the pager is set to less, it will allow you to scroll through the database, and in this case, allow you to look at the name of every collumn.

Using this you can see the names of every collumn at the begining, then using simple logic and a basic comprehension of everquest you can pull out fields that matter to you, once you've done that, it's time to build the equation!

To build the equation first decide what things you wish to be valued at what level... i suggest doing everything in terms of platinum so you can multiply by a constant.

Let's say you decide all stat points (aagi, acha, asta, astr, adex, awis, aint) are going to be 1 plat each, and ac is 2 plat per ac, 1 plat per resist, and .1 plat per hp or mana, now you need to put it together using the collumn names to yield an equation... you should come out to;


((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr+pr +mr)+(2*ac)+(.1*(hp+mana)))*1000;


I added in extra ()'s to make it clear... at first we add together all the stat points (each multiplied by the 1 plat, which makes it just itself) then we add each of the resist points (also 1 each). After that we add together 2 times the ac of the item, followed by .1 times the mana plus the hp, giving us our desired amount... Multiply this all by 1000 (to change our copper to plat... i.e. if we cranked that equation through and got 5plat as a desired result, we'd actually end up with 5 copper if we didn't multiply by 1000)

Alright, now we have our equation Cheer!

Now it's a simple matter of updating the cost like so;


UPDATE items SET cost = (equation) where id = (item id);


So in our case it would look like


UPDATE items SET cost = ((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr+pr +mr)+(2*ac)+(.1*(hp+mana)))*1000 where id = 1233;


And your done.

Now, if you want to make this change globally (i.e. to your entire DB) there is only 2 things you need to change.

First, you take out the where clause, and replace it with whatever restrictions you may want to implore.... so if you want one formula for weapons, a different one for armor, and a third for bags, use Where bagtype = 1 to specify for bags, where damage > 0 and/or delay >0 to specify weapons, and where damage =0 and/or delay = 0 and bagtype =0 to specify armor

If you wish to string together where clauses to form a restricted domain, such as in the case of specifying armor, use caps to specify... once again not sure if this matters or not but redundancy helps

So, in the case of armor;


... Where damage = 0 AND delay =0 AND bagtype=0;


Finally, the other thing you want to change, is to add in a constant into your equation. This will prevent items that are accidently caught in your equation's where clause from being turned to 0 because they have none of the stats you used to assign a value to... Thus if you used the above equation and applied it globally, things like vellium bars would be free since they have no stats, resist, ac, or hp/mana.

Thus an equation should have +(some constant), take the above equation and manipulate it to give a base value of 1, would yield;


UPDATE items SET cost = (1+((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr +pr+mr)+(2*ac)+(.1*(hp+mana)))*1000) Where Delay>0 AND damage>0;


That makes all weapons (items with damage greater than 0 and delay greater than 0) apply to that equation for cost, and any weapon without stats/resists/hp/mana/ac would have a cost of 1plat (FYI this equation would be poor due to it's total negation of taking into account what dmg/del the weapon actually has... see next post for helpful hints)

Ok, so you have your equation, you have an added constant to make sure items aren't free, and you know how the where clause works... you're all set to rip through your DB and change costs at will... PLEASE DEAR GOD AND FOR THE LORD JEBUS REMEMBER TO BACKUP YOUR DATABASE !!! if you manage to code some great stuff, then screw something up and frag yer db, you'll have to resource the item file from scratch and start all over, and that's a huge headache... make sure to backup your DB after every SUCCESSFUL major code change... and prefferably before every major codeing attempt... but make sure to play-test it, don't just assume it works... bugs happen and mistakes are made.

As a final note, i said i would include how to customize sellback value... it is simply the exact same as the above using the collumn 'merchantprice' instead of cost... thus you could write


UPDATE items SET merchantprice = (1+((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr +pr+mr)+(2*ac)+(.1*(hp+mana)))*1000) Where Delay>0 AND damage>0;


And sell values would be set accordingly... However, since most servers would like for items to be sold at some sort of fraction of the item's cost value... simply do exactly that, and use (for example)


UPDATE items SET merchantprice = cost;


If you want it to be some fraction of the cost, simply put the fraction multiplied against the cost... for the example of .5


UPDATE items SET merchantprice = .5*cost;



Ok all, it's midnight and i gotta get up for class in 6 hours, so i'm gonna stop it here... will check in tomorrow to see if i can add anymore or reply to posts that may need some help... Should get in my helpful hints/FAQ/Common debugging section in tomorrow i hope...

Hope this helps!

[Zengez]

Ok, here's some Helpful Hints i've found/thought of/used or whatnot, use them as you will, don't if you don't want to, they're just suggustions.

Setting Item Costs:

When setting item costs and sellback values, keep in mind your not just setting the cost of items and sellback of items from vendors, but also the sellback value of found items. If you put this too high and then wonder why newbs are running around with 1k plat at lvl 10, it might be because your weapon formula suddenly turned that rusty battle axe into a 200p sell instead of a 2 gold sell... or likewise with other items, try to keep in mind the progression of money and what you're doing to the sellback value's effect on that progression of income.

About setting weapon costs, the best way (in my opinion) is to take a ratio of damage and delay. This allows for what the damage and delay of the weapon actually is, while at the same time, also accounting for each's relative strength... thus if you set a cost based entirely on teh damage, items like bloodpoint (10/21) would be considerably less money than weighted axe (45/150) even though the former is a considerably better weapon. To do this, simply put the ratio litterally into the code, thus


UPDATE items SET cost = damage/delay Where damage > 0 AND delay>0;


Obviously you'll want to add in more than that, or most all weapons would be less than 1copper, but thats for you to decide for your own server.

That being said, i have an alternate system of code i wrote to devise a dynamic 'formula' for the items on my DB you may or may not find useful. What i wanted was a way to judge each aspect of an item and have it assigned a price value, not only based on what it's stat is (like in the above formula); but i also wanted the relative goodness of the stat to be taken into account... therefore an item with +30 to all stats, in my opinion, should have each stat worth more plat, than one with +5 to stamina. This created my desired effect of making newbie items affordable at only a few plat, while making the crazy ubr items become massively expensive (slowing the lvl and effort required to attain them) at 5k+plat or even higher.

Here's the outline i came up with, in which i set the item's cost to 0, then built it up according to it's stats and what tier they fell on...


-- startup set basis value only for items which have relevant properties (i.e. ones with stats, resists, ac, damage, delay, hp/mana regen, hp/mana, or skill mods)
update items set cost = 0 Where (ac+aagi+acha+asta+astr+adex+awis+aint+cr+fr+pr+dr +mr+hp+mana+hastepercent+damage+delay+hpregen+mana regen+skillmodvalue) > 0 AND bagtype=0;

-- add a temp column to aid calcs
alter table items add column jn_stats integer;

-- add cost based on AC
update items set cost = cost + 1 * ac where ac > 0 and ac <= 20;
update items set cost = cost + 2 * ac where ac > 20 and ac <= 30;
update items set cost = cost + 3 * ac where ac > 30 and ac <= 40;
update items set cost = cost + 5 * ac where ac > 40;

-- add cost based on stats
update items set jn_stats = (aagi+acha+asta+astr+adex+awis+aint);
update items set cost = cost + 2 * jn_stats where jn_stats > 0 and jn_stats <= 30;
update items set cost = cost + 4 * jn_stats where jn_stats > 30 and jn_stats <= 50;
update items set cost = cost + 7 * jn_stats where jn_stats > 50 and jn_stats <= 70;
update items set cost = cost + 10 * jn_stats where jn_stats > 70;


-- add cost based on resists
update items set jn_stats = (cr+fr+pr+dr+mr);
update items set cost = cost + 1 * jn_stats where jn_stats > 0 and jn_stats <= 15;
update items set cost = cost + 2 * jn_stats where jn_stats > 15 and jn_stats <= 30;
update items set cost = cost + 4 * jn_stats where jn_stats > 30 and jn_stats <= 45;
update items set cost = cost + 5 * jn_stats where jn_stats > 45;

-- add cost based on hp and mana
update items set jn_stats = (hp+mana);
update items set cost = cost + 1 * jn_stats where jn_stats > 0 and jn_stats <= 70;
update items set cost = cost + 2 * jn_stats where jn_stats > 70 and jn_stats <= 100;
update items set cost = cost + 3 * jn_stats where jn_stats > 100 and jn_stats <= 130;
update items set cost = cost + 5 * jn_stats where jn_stats > 130;

--add cost based on haste
update items set jn_stats = (hastepercent);
update items set cost = cost + 50 * jn_stats where jn_stats > 0 and jn_stats <= 10;
update items set cost = cost + 100 * jn_stats where jn_stats > 10 and jn_stats <= 20;
update items set cost = cost + 200 * jn_stats where jn_stats > 20 and jn_stats <= 30;
update items set cost = cost + 300 * jn_stats where jn_stats > 30;

--add cost based on damage and delay
update items set jn_stats = (damage*100/delay);
update items set cost = cost + .1 * jn_stats where jn_stats > 0 and jn_stats <= 15;
update items set cost = cost + 1 * jn_stats where jn_stats > 15 and jn_stats <= 30;
update items set cost = cost + 5 * jn_stats where jn_stats > 30 and jn_stats <= 60;
update items set cost = cost + 15 * jn_stats where jn_stats > 60 and jn_stats <= 100;
update items set cost = cost + 30 * jn_stats where jn_stats > 100;

--add cost based on mana regen
update items set jn_stats = (manaregen);
update items set cost = cost + 100 * jn_stats where jn_stats > 0 and jn_stats <= 1;
update items set cost = cost + 300 * jn_stats where jn_stats > 1 and jn_stats <= 3;
update items set cost = cost + 500 * jn_stats where jn_stats > 3 and jn_stats <= 5;
update items set cost = cost + 700 * jn_stats where jn_stats > 5;

--add cost based on hp regen
update items set jn_stats = (hpregen);
update items set cost = cost + 100 * jn_stats where jn_stats > 0 and jn_stats <= 1;
update items set cost = cost + 300 * jn_stats where jn_stats > 1 and jn_stats <= 3;
update items set cost = cost + 500 * jn_stats where jn_stats > 3 and jn_stats <= 5;
update items set cost = cost + 700 * jn_stats where jn_stats > 5;

--add cost based on skill mod value
update items set jn_stats = (skillmodvalue);
update items set cost = cost + 30 * jn_stats where jn_stats > 0 and jn_stats <= 2;
update items set cost = cost + 45 * jn_stats where jn_stats > 2 and jn_stats <= 4;
update items set cost = cost + 60 * jn_stats where jn_stats > 4 and jn_stats <= 7;
update items set cost = cost + 75 * jn_stats where jn_stats > 7;

-- clean up
alter table items drop column jn_stats;
-- set price for any missed in calculations to 1
update items set cost = 1 where cost = 0;
--Apply modifier to change copper into plat values
update items set cost = cost * 1000 Where (ac+aagi+acha+asta+astr+adex+awis+aint+cr+fr+pr+dr +mr+hp+mana+hastepercent+damage+delay+hpregen+mana regen+skillmodvalue) > 0 AND bagtype=0;
-- Make All Items Sellable
update items set NODROP = 1;
-- Apply sellback modifier;
update items set merchantprice = .8*cost;



For this i came up with 4 tiers of items... newb, medium, high, and crazy ubr. Each tier had a range of it's relative stat, take for example ac.


-- add cost based on AC
update items set cost = cost + 1 * ac where bagtype = 0 and ac <= 10;
update items set cost = cost + 2 * ac where bagtype = 0 and ac > 10 and ac <= 20;
update items set cost = cost + 3 * ac where bagtype = 0 and ac > 20 and ac <= 40;
update items set cost = cost + 5 * ac where bagtype = 0 and ac > 40;


This says "Where ac is less than 10, this costs 1 plat per ac" which means if the ac is less than 10, it's a 'newb item' on my gear list, and falls into the cheapest catagory. Likewise, if it's ac is between 10 and 20 (including 20 as the endpoint) it's 2*ac, because it's a medium level item... etc...

At the end i have set merchant price equal to my new cost, so that items will sell back roughly for what they are bought for, and future items, if found, will sell for what they would have bought for, allowing the player to work toward upgrading their gear even when they find items that aren't useful to them.

All of this is entirely my choice, and so far i haven't gotten to play-test it yet, still working on seeing how the money influx/outflux ratio is handling the weapon dmg/del cost fix (rusty weapons are selling for 15p on my server currently) but, the outline is right there if you wish to borrow/use it to easily add/remove/change the way the multipliers are handled, go for it.

More to come later.

EDIT:: Re-posted my own suggestion for item cost database for which i made it not wipe out all items cost, so things like gems and bars of metal will now retain their originol prices, while only items with relevant stats (like ability stats or hp/mana etc etc) get costs revamped, hope that helps

[Zengez]

FAQ/Debugging:

I don't know a whole lot about coding and debugging since i just restarted programming about 3 days ago after a 4+year break, but i'll do what i can (and i'm sure others will chip in too if/once they read your questions)

Common problems i've encountered:

Q: Ok, so i made up my merchant_id, and i created a whole new custom merchant.... but when i source it like you said i get a whole lot of "Error duplicate entry" lines, whats going on?

A: This means you are trying to source a Merchant_ID that already exsists... this could be caused by either you picked an ID that already exsisted (i started with 56007 since i am pretty sure theres nothing after that in the PEQ DB) or you already sourced that ID at least once, which 'created' it, and thus you're trying to source over an already exsisting ID.

To fix this is simple, but dangerous... if you are absolutely certain that the merchant_id is of no use to you, simply delete it from the field by typing


delete * from merchantlist where merchant_id = (merchant_id you want to get rid of);


This will kill all input of this merchant_id, however this is permenant, and could be pretty annoying if you accidently just wiped out a general merchant and now all the general merchants in the game are selling your new customized merchant item tables instead of the food and water most newbies are expecting there... My suggestion; simply change the merchant_id you're trying to source in to something else.

Q: I'm having trouble getting effects on my DB to work... i do everything exactly as everyone says to, but it doesn't seem to do anything, one way or the other... what have i done wrong?!

A: Sometimes, especially with linux boxes that aren't rebooted often, things get mysteriously stashed somewhere and don't leave... i learned this one the hardway, was driving me nuts for days until i finally physically restarted my computer to force a re-write to get things to all work out...

Before reaching for that power button however, make sure the changes you made actually took effect. You can do this by simply selecting the field you wished to change to make sure it's indeed changed to what you wished.

Take, for example, that you tried to reset item costs, and then log in and find things not changed at all... go to your mysql cmd line and type;


Select cost,merchantprice from items where name = '(name of item you know the price of)';


This should pull up the merchantprice and the cost of the item, and if you're like me and reset them both (especially as i reset merchantprice to equal the cost) things should become real clear real fast if the change took effect. If these numbers do NOT match the value they should (i.e. look alot like the value you see them for in the regular game, not your custom price) then something got screwed up along the way between the writing of the code and the sourcing/updating of the DB. I'd recommend going back through and taking a look at the .txt or .sql file you sourced in (the advantage of doing it this way), or if you simply did an update command, maybe trying it again (you may have gotten a syntax error so the equation never ran). However this could exsasberate the problem, so do so at your own risk.

The other option is that the values are indeed what they are suppose to be but don't seem to be doing anything for you even though they are there stareing you in the face on teh computer screen... This is what i was talking about earlier, where sometimes the previous values get stored somewhere and never updated for whatever reason, even despite a sever reboot (yesterday i left my server down 8 hours after making a change and it still didn't stick when i booted it back up) Try to reboot the server, leaving it down for about 5 minutes between shutting it down and starting it up... or if it's not an integral part of your network (like it is for me) you can skip that and simply power it off, count to fifteen, and power it on... if all else fails, this will reset all those crazy residual cached things (And for those that know what they're doing, i tried a sync as well and still didn't do anything for me... /shrug)

Q: Wow, this is the best thing ever, can i put this on my website?

A: Yes. (now if only someone would ask this! hehe ego-stroke!)

More to come later, as people ask me questions i'll try to throw up the more "Frequently asked" ones on this... who woulda thought?

[Koshoji]

This was a sweet tutorial Zengez!

Thanks a million; it'll keep me busy for quite a while I'm sure.

[sylverwolf]

I realize this is an older post but I just found it and for the greater majority, it works great :)

I been play testing and almost everything is smack on. there was one minor flaw tho. arrows cost an insane amt of plat. it may have something to do with the warnings whe sourcing too.

update items set jn_stats = (damage*100/delay);

arrows are x damage and always 0 delay. to alleviate this i tailored it to this

update items set jn_stats = 0
update items set jn_stats = (damage*100/delay) where delay > 0;

useable weapons will always have a delay and this will filter arrows out of the equation :)

hope this helps

[Hellefire]

Nice. It seems pretty straightforward, even for a non-coder (I've been programming for a long time and sometimes forget to tone down explanations for non-coders until I see the glazed look in their eyes). I'm just getting into EQemu coding, and that was a great write-up!

Aaron