[ic] The best How-To? An Approach for Displaying all Quantity Pricing Breaks

[interchange-users@icdevgroup.org (Ron Phipps)]

> From: Barry Treahy, Jr.
> 
> Jonathan Clark wrote:
> 
> >>I'm looking for the best 'How-To' approach on this, not necessarily
> >>asking for the coded details, though I would certainly not look a
gift
> >>horse in the mouth...
> >>
> >>Our new pricing system now has five pricing tiers whose quantity
> >>discount structure, which consists of 15 quantity breaks, is linked
to
> >>each product.  Because each of our products have assigned one of
these
> >>five different discount tiers, I had decided to create a table that
has
> >>the tier codes along with the quantity code and the discount
percentage
> >>for that quantity.  When a customer is viewing the flypage, I will
have
> >>an additional link where by they can view the quantity pricing for
that
> >>product, the question is what would be the best approach?
> >>
> >>Since I already have the SKU, and therefore the tire code, I would
like
> >>to pull the appropriate row from the tier table and walk the
columns,
> >>without hardcoding each column in a loop, and present for each
quantity
> >>break the discounted price...
> >>
> >>In the past, with a single tier code, I did it manually with ITL and
> >>HTML, and it was ugly and hard to maintain, so I suspect that I'll
be
> >>best off doing this all with talbes and embedded Perl but the
looping
> >>and walking through the columns of a record, can that be done?  At
least
> >>if I know it is possible, then I can forge ahead with figuring out
the
> >>how....
> >>
> >>
> >
> >Maybe the qty pricing usertag at
> >http://www.interchange.rtfm.info/downloads/usertags/ will be of help.
> This
> >was posted to the user list some time ago by Ron Phipps, I think I
also
> >posted one previous to that.
> >
> >
> Thanks Jonathan.  What I need to do is a tad more complex that just an
> alternate pricing table.

Hey Barry,

The code Jonathan linked to actually is a little bit more then an
alternate pricing table.  It allows for a table to grow long instead of
adding columns for the different price breaks so there is no need to
read the field names.  Kevin Walsh suggested I do it this way so that I
did not have to have a ton of columns and I would not have to modify the
table structure each time a new quantity break was needed.  With this
structure you can easily pull all the price breaks for a tier and loop
over them for display and you only need to know about 3 columns.

> 
> Take the following Scenario:
> 
> SKU A has a price TIER A and a base price of $10
> SKU B has a price TIER A and a base price of $20
> SKU C has a price TIER B and a base price of $15
> SKU D has a price TIER C and a base price of $30
> 
> Price TIER A has the following quantity discounts, Q1=0%, Q10=6%,
> Q25=12%, Q50=18%, Q100=25%
> Price TIER B has the following quantity discounts, Q1=0%, Q10=5%,
> Q25=10%, Q50=14%, Q100=18%
> Price TIER C has the following quantity discounts, Q1=0%, Q10=4%,
> Q25=8%, Q50=12%, Q100=15%
> 
> Obviously these are all hypothetical SKU's and TIER's but you get the
> idea and each are loaded into their products and tiers table, the
> products table which everyone is familiar with, with a additional TIER
> column, and then a tier table which consists of the same primary keyed
> column called TIER and then Q1 through Q25000 quantity break columns
> with the appropriate disacount for each tier and quantity column.

Do your tiers have different quantity break points from tier to tier?
How many different break points/columns will you have.

Maybe the way to do it would be similar to the example, but with a
modification for base price and your discount instead of the actual
price at that break.  Have your table defined like so:

tier	price	break	discount
A	10	1	0
A	10	10	.06
A	10	25	.12
A	10	50	.18
A	10	100	.25
B	20	1	0
B	20	10	.05
B	20	25	.10
B	20	50	.14
B	20	100	.18
C	15	1	0
C	15	10	.04
C	15	25	.08
C	15	50	.12
C	15	100	.15
(continue for d)

> 
> Ultimately, what I want to end up doing is taking the [item-code]
value
> from the flypage, creating a secondary flypage which using the tier
code
> and then pulling the record from the tier table, presenting for that
SKU
> what the 1-9 price, 10-24 price, 25-49 price, and so on buy looping
the
> tier row for each column...  Lastly, I'll create a tag that the
> CommonAdjust can use to properly pull and calculate the proper price
> based on quantity and tiering.

This is definitely all do-able and I would steer you in the direction of
a table like above so that you do not have a ton of columns and your
code will be much simpler.  You can pull all the price breaks for tier B
with a simple: SELECT break, discount FROM tier WHERE tier='B'

> 
> So, that's where I'm at and looking that the example URL you did
> provide, I have some ideas now on how to access the tables within Perl
> but what isn't clear is how to do it without hard coding the number of
> columns to parse.  For example, is it possible to pull the 'header'
> record of the table that provides the name of the columns that  can
> parse for names and also to count...

I did not find an answer to this question when I originally intended to
do it the way you describe.  The first time around I defined the column
list in a variable which could be defined in the catalog.cfg, but this
was still using a static list of columns.

> Is that a clearer picture of the dragon I'm attempting to slay?  At a
> minimum, if someone knows of some good reference points that
illustrate
> examples of IC table referencing and manipulation, without SQL, that I
> can learn from that would be awesome...
> 
> Barry
> 

Best of luck Barry!
-Ron