[ic] UserTag to return correct price for a given quantity using new pricing table
Kevin Walsh
interchange-users@interchange.redhat.com
Fri Apr 19 06:27:00 2002
>
> The PostgreSQL table structure:
>
> Database qty_pricing qty_pricing.txt __SQLDSN__
> ifdef SQLUSER
> Database qty_pricing USER __SQLUSER__
> endif
> ifdef SQLPASS
> Database qty_pricing PASS __SQLPASS__
> endif
> Database qty_pricing KEY qty_pricing_id
> Database qty_pricing AUTO_NUMBER 1
> Database qty_pricing COLUMN_DEF "qty_pricing_id=int NOT NULL PRIMARY KEY"
> Database qty_pricing COLUMN_DEF "sku=VARCHAR(64)"
> Database qty_pricing COLUMN_DEF "price_group=VARCHAR(12)"
> Database qty_pricing COLUMN_DEF "quantity=int DEFAULT 0 NOT NULL"
> Database qty_pricing COLUMN_DEF "price=VARCHAR(12)"
> Database qty_pricing INDEX sku
> Database qty_pricing INDEX price_group
>
This sort of thing has always worked well for me.
Note that this table also makes it nice and easy to dump the price
breaks onto the flypage. Instead of all that messing about working
out whether q5 == q2 etc., you just loop through the table and dump
whatever pops out. If the q5 value should be the same as the q2,
then the merchant need not enter a q5 value at all.
Also, I would suggest changing the type of your 'price' column
from VARCHAR to something a little more numeric. Although there's
nothing wrong with what you have done in this context, your choice
of VARCHAR just doesn't look right. :-)
>
> Future enhancements:
> Add price_group feature, if the product belongs to a price group then
> lookup based on both the sku and price_group and pick the lower of the
> two prices for the given quantity.
>
Another future enhancement to your tag: A price_schedule column
would allow you to offer different price breaks for different
classes of user. You could have 'retail', 'dealer' and 'employee'
price breaks, for instance. Perhaps this is what you intended your
price_group column to be used for, although it didn't read that way
to me.
>
> Add flag to control break behavior, if a flag is set in the productdb
> then only return a price for an exact quantity being ordered and ignore
> the last exceeded break point. (Emulate how the quantity lookup works
> out of the box)
>
I can't see that being too useful.
>
> I hope someone else finds this useful. Thanks again for everyone's help
>
Thanks for posting your work. The person who was attempting to
charge "\$20" for Playpal code should take some notes.
--
_/ _/ _/_/_/_/ _/ _/ _/_/_/ _/ _/
_/_/_/ _/_/ _/ _/ _/ _/_/ _/ K e v i n W a l s h
_/ _/ _/ _/ _/ _/ _/ _/_/ kevin@cursor.biz
_/ _/ _/_/_/_/ _/ _/_/_/ _/ _/