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

[Barry Treahy, Jr.]

Ron Phipps wrote:

>>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.
>
Yes, I see the benefits for this solution when there are a small number 
of SKU's and quantity breaks, but I would have to believe that this 
breaks down a bit when you are dealing with 15 quantity breaks per SKU 
and you have over 20,000 SKU's...

>>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.
>
Of the five tiers, they all use and have the same number of quantity 
breaks, a total of 15 for each tier.

>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)
>
Ok, I can see how this could work because I could loop on the tier, 
sorted by break which would be much simplier than the column approach 
and since in my case I would not have a price column, since that is a 
product related item that I would be pulling from the products table, 
this suggested direction could work much easier...

>  
>
>>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'
>  
>
Yeap, I see that from earlier...

>>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.
>  
>
Yeap, buy redesigning the tier table to run the contents from the rows, 
that removes the goofy question I asked...

Thanks for your insights...

Best regards,

Barry

-- 

Barry Treahy, Jr  *  Midwest Microwave  *  Vice President & CIO 

E-mail: Treahy@mmaz.com * Phone: 480/314-1320 * FAX: 480/661-7028