Free Items

[Mrwalsh]

For a while I was having a problem with free items, so I made a simple solution (albeit not a perfect one) in SQL:

update items set cost = ( 10 * ( reclevel + hp + ac + mana + 100 ) ) where reclevel > 0;

That takes any item with a recommended level, adds the reclevel, hp, ac, and mana together, adds 100 to it (in case those values were zero) and multiplies that by 10. It actually gives a pretty fair price estimate.

To catch any other items from being free:

update items set cost = 1000 where cost = 0;

Easy as that. Not a perfect fix, but better than the players running around with freebies.

EDIT:

Okay, this one works for all items, skipping bags. Food will be pretty cheap, but for my world that doesn't bother me. It accounts for haste in a big way (about 100 platinum per 1%, so Flowing Black Silk Sash costs around 2000 platinum).

update items set cost = ( 100 * ( reclevel + reqlevel + ( hp * 10 ) + ( ac * 10 ) + ( mana * 10 ) + ( aagi * 10 ) + ( acha * 10 ) + ( asta * 10 ) + ( astr * 10 ) + ( adex * 10 ) + ( awis * 10 ) + 1 ) + ( hasteproclvl * 100000 ) ) where ( bagtype = 0 );

This will probably end up with some odd prices, but with this formula I've gotten Flowing Black Silk Sash to the price I intended, so the rest should even out. Note that cost is in copper pieces.

EDIT:

Now, for items with resists. The above formulas ignored them, but the next one is designed to be ran AFTER you run the updated version (the one with haste). The command line can only take so many characters so I had to make this one after. It assumes that each point of resistance is worth 10 platinum. (I'm trying to encourage hunting.)

update items set cost = ( cost + ( cr * 10000 ) + ( dr * 10000 ) + ( pr * 10000 ) + ( mr * 10000 ) + (fr * 10000 ) ) where ( bagtype = 0 ) and ( ( cr + dr + pr + mr + fr ) > 0 );

The very last part checks to see if there is actually any value in the resists. If not, it skips the item, since the above command already figured the cost.

EDIT:

I've put the commands into .sql format. You can get them here.

EDIT:

As this stands, this will set your horse (and likely drogmor, haven't checked) prices to less than 2 gold.

EDIT:

Included in the file: it now fixes mount prices, back to relatively normal prices.

[Lurker_005]

hmm not somethig I've seen anyone look into before. Guess having the item fields easily acessable instead of a blob makes this a lot easier.

One comment on the 0 cost items. Some were intended as guide items, that is why they were free. They were only avaliable to GM/guides from vendors in CSHome. Also some were set to zero on live so vendors would not buy them, at least that is what it looked like for items that vendors would not buy.

Finally keep in mind that there is a "profit" multiplier for vendors. You have the item price that is the basis for buying and selling, plus a faction type multiplier, and when selling there is another multiplier (sellrate) in the item data that is applied to the price.

[Mrwalsh]

Yes, I know that some items were intended to cost zero - but a few items, player-items, were selling for free. Not to mention the prices were much lower than I cared for, so I figured hey. I also know there's a multiplier etc, but this does seem to do the trick quite well, actually. With my piss poor dark elf faction, Flowing Black Silk Sash runs me 2.5k from a vendor.

I am satisfied with how it's worked so far. I'll probably be adding to the .sql file to replace all guide/GM items back at zero cost, but other than that it works just nicely. For me, anyway.

- blasterpack777, *GM*-Impossible, Blue Ontario

[Muuss]

Smart idea
You could avoid to give nodrop items a price.
'aint' statment is missing in the 3rd query and it overrides all the costs, even those which yet have been set.

I suggest not running the first query which sets 1000 to 'cost', and merge with it the second one :

update items set cost = 1000 + ( 100 * ( reclevel + reqlevel + ( hp * 10 ) + ( ac * 10 ) + ( mana * 10 ) + ( aagi * 10 ) + ( acha * 10 ) + ( asta * 10 ) + ( astr * 10 ) + ( adex * 10 ) + ( awis * 10 ) + ( aint * 10) + 1 ) + ( hasteproclvl * 100000 ) ) where bagtype=0 and cost=0;

by the same time, the last query also affects the items manually set, so lets merge it too.

update items set cost = 1000 + ( 100 * ( reclevel + reqlevel + ( hp * 10 ) + ( ac * 10 ) + ( mana * 10 ) + ( aagi * 10 ) + ( acha * 10 ) + ( asta * 10 ) + ( astr * 10 ) + ( adex * 10 ) + ( awis * 10 ) + ( aint * 10) + 1 ) + ( hasteproclvl * 100000 ) )+ ( cr+dr+pr+mr+fr) * 10000 ) where bagtype=0 and cost=0

I haven't tested this since i m at work with no eqemu DB available, but at least you ll get the idea. (still missing : the nodrop test.)

[Mrwalsh]

Ah, very cool, why did I not think of adding the resistances together? Doh. On that note, could add the stat values together as well to shorten it even further. What code would I use to avoid nodrops? This is my first attempt at SQL, so I don't know a lot of the syntax.

[Muuss]

Well, with the nodrop test, and a small factorisation :

update items set cost = 1000*(1+reclevel+reqlevel+hp +ac+mana+aagi+acha+asta+astr+adex+awis+aint+hasteproclvl*100+(cr+dr+pr+mr+fr) *10+100*(spellid+5)/(spellid+5)) where bagtype=0 and cost=0 and nodrop=0 and damage=0 and delay=0

this sets
1 pp
+ 1 pp per stat/ac point
+ 1 pp per mana/hp point
+ 1 pp per reclevel/reqlevel
+ 100 pp per %haste
+ 10 pp per resistance point
+ 100 pp if the item as a spell attached to it

for each item that
- isnt a bag
- costs 0
- isnt nodrop
- isnt a weapon

for the weapons, do the same, but add a calculation with the ratio damage/delay (i havn't added it since items that arent weapons have a delay of 0 which would raise an error in the division).

update items set cost = 1000*(1+reclevel+reqlevel+hp +ac+mana+aagi+acha+asta+astr+adex+awis+aint+hasteproclvl*100+(cr+dr+pr+mr+fr) *10+100*(spellid+5)/(spellid+5)+500*damage/delay) where cost=0 and nodrop=0 and damage>0 and delay>0

this sets
1 pp
+ 1 pp per stat/ac point
+ 1 pp per mana/hp point
+ 1 pp per reclevel/reqlevel
+ 100 pp per %haste
+ 10 pp per resistance point
+ 100 pp if the item as a spell attached to it
+ 1000pp * ratio damage/delay (ex : 9/18 = 500pp)

for each item that
- isnt a bag
- costs 0
- isnt nodrop
- isnt a weapon

calculation of the price linked to the ratio needs to be reworked, it's working for lowbie weapons, but totally inaccurate for high end ones (16/18 would cost 888 pp), tho, most of the weapons with such a ratio are nodrop.

[Mrwalsh]

Oh, cool, thanks, I think that might work. I'm going for a bit of an expensive server (encourage hunting!) so I'll probably make it somewhere between 5 and 10pp per stat and hp. We'll see. Thanks again!

[Squiffy]

Great ideas. I was thinking of doing something similar and was sitting there looking at PCs on a few items on my server and trying to derive a formula that would match their prices closely.

I may just use this formula to set all item prices, not just the price=0 ones.

Thanks, my formula was looking similar, but I wasn't sure what to do about haste and procs and such.

Only thing I can see is what about items that have, say, a worn effect and a triggered effect? Like a focus and worn effect.

I dunno, I'm hoping to eventually have vendors up on my server selling near every item in the game (that I want people to have access to) for prices that suit. So they could buy those nodrop Elemental Legs, or whatevs. Speaking of which, will a vendor sell a NoDrop item?

will a vendor sell a NoDrop item?

yep the sure will

[Squiffy]

Kickass. They won't buy them back, though, I presume?

[Mrwalsh]

No, they won't. They will sell but not buy nodrops.