SkeyMedia

We've all seen it. Some 3rd party database you've inherited from someone else was simply never designed with scalability and portability in mind. There's no hope to change it, as it will break the original design and thus the original application that it is supporting.

In a recent case for me, I had the problem of dealing with a 'Quantity Discount' table. ie, Buy one and its normal price, buy 2 or more and it's a dollar less per item. The table I had to work with was a disaster. There were no data pairs for pricing and quantity. Instead, it was one row per item number with 9 qty fields and 9 corresponding pricing fields similar to this:

itemno, offno, qty1, qty2, ... qty9, pri1, pri2, ... pri9

• qty1 matched with pri1, qty2 matched with pri2 and so forth through the 9 iterations.
• itemno is the item number/sku in the database inventory table
• offno is relating to the offer code, or promo code. Depending on the promo, certain products are priced differently.
• the max discount field in the qty ranges was denoted by 999999 in the last field.
• any non-used qty ranges after the max were filled in with 0's.
• Prices are stored in whole numbers (x100) so as to eliminate decimal points. ($19.99 was stored as 1999)

The current setup simply printed out every price, line item by line item, until the 999999 item was reached, and then 'or more' was added on to the end of it.

What the client wanted to display, was Only the upsale prices. Ie, if 1 and 2 items were regular price, but 3 or more were at a discount, all they wanted to show was the main price as usual, along with «Buy 3 or more, $XX.XX». this posed even a more difficult problem when there were ranges in the prices, ie, Buy 1-2 at normal price (no display - same as regular price), Buy 3-4, $XX.XX, Buy 5 or more, $XX.XX

One stored procedure, one temp db table, and one two-dimensional indexed array later, I came up with the solution (with the help of a couple fine members on a dev mailing list I might add)

So, I waved goodbye to Function multiprice, and created Function multiprice_new

Here's the code I used to pull this off using Classic ASP as the scripting language, and MS SQL2000 as the DBMS.

This function takes on the item number (itemno), as well as the generic price of the single item cost (sPrice) from the main script.

Function multiprice_new(itemno, sPrice)
 
  Dim myqty()
  Dim myprice()
 
  Set cn = Server.CreateObject("ADODB.Connection")
  Set cmd = Server.CreateObject("ADODB.Command")
  
  cn.Open DB
  Set cmd.ActiveConnection = cn
  cmd.CommandText = "multiprice_new"
  cmd.CommandType = adCmdStoredproc
  Set objRs = Server.CreateObject("ADODB.Recordset")
 
  xitemno2 = trim(itemno)
  xitemno2 = Replace(Space(8-len(xitemno2))," ","0") & xitemno2     ' this fixes some formatting issues on the itemno
  src = trim(session("src"))    ' this passes in the source code for the correct promo pricing
 
  With cmd
    .Parameters.Append cmd.CreateParameter("@itemno", adVarChar, adParamInput, 25, xitemno2)
    .Parameters.Append cmd.CreateParameter("@src", adVarChar, adParamInput, 25, src)
    .Parameters.Append cmd.CreateParameter("@qty", adInteger, adParamOutput, , 0)
    .Parameters.Append cmd.CreateParameter("@price", adInteger, adParamOutput, , 0)
    
  End with
 
  
  Set objRS = cmd.Execute
  x= 0
  Do While Not objRs.EOF
 
    ReDim Preserve  myqty(x)
    ReDIm Preserve  myprice(x)
 
    myqty(x) = objRs(0)
    myprice(x) = objRs(1)/100    ' remember, the prices were stored with no cents, so this generates the cents portion of the price
 
        objRs.MoveNext
    x = x+1
    Loop
 
  
  Dim qtyranges(2,9), price, rangecount
    
  price = 0
  rangecount = 0
 
  limiter = UBound(myprice)
 
  for j = 0 to limiter
    price = myprice(j)
    i = j + 1
  
    If i<limiter then
      Do While price = myprice(i)
        i = i+1        
      Loop
    End if
 
    qtyranges(0,rangecount) = j
    qtyranges(1,rangecount) = i
    qtyranges(2,rangecount) = price
 
    rangecount = rangecount+1
    j = i
  Next
 
  
  for j = 0 to rangecount-1
    If qtyranges(0,j) = 0 Then
      qtyranges(0,j) = 1
    End If
    
    If sPrice < (CDbl(qtyranges(2,j)) + CDbl(.01)) Then
      ' skip...  this is not a cheaper price than normal.  They only want to show the discounted qty ranges.
    else
      response.write "Buy " & qtyranges(0,j)
      
      If qtyranges(1,j) <> "" Then
        If qtyranges(1,j) = limiter+1 Then
          response.write " or more"
        Else
          If qtyranges(0,j) = qtyranges(1,j) then
            ' DO nothing (avoids Buy 1-1,)
          Else
            response.write "-" & qtyranges(1,j)
          End if
        End if
      End If
      
      response.write ", Pay $"& qtyranges(2,j) & " each<br />"
    End if
  next
 
  response.write "<br /><br />"
 
    objRS.Close  
  cn.Close()
  Set cn = Nothing
 
  multiprice_new=Success
 
End function
</limiter>

This function called the following stored procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
ALTER  procedure multiprice_new
(
@itemno varchar(25),
@src varchar(25),
@qty int output,
@price int output
)
 
as
 
set nocount on
 
begin
 
declare @t table
(
itemno varchar(25),
qty int,
price int
)
 
insert into @t
 
select itemno, cast(qty1 as int), pri1
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty2 as int), pri2
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty3 as int), pri3
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty4 as int), pri4
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty5 as int), pri5
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty6 as int), pri6
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty7 as int), pri7
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty8 as int), pri8
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
UNION ------------
select itemno, cast(qty9 as int), pri9
from multiprice
where itemno = '' + @itemno + ''
and offno = '' + @src + ''
 
delete from @t
where price = 0
 
select qty, price
from @t
order by qty asc
 
set nocount off
 
end
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 

Finally, to call it from our main product display page, we simply call the function:

itemno = request("itemno")
sPrice = **some other function here that gets pricing from inventory table (along with the product details, etc) **
Ssuccess=multiprice_new(itemno, sPrice)

And that will print out something like:

Your Price: $14.98
Buy 4 or more, Pay $10.98 each

So, what did we do here?

We had a problem table that would normally be pretty useless to us.
We made our own temp table that would be worthwhile to us, filling it in line by line from the bad table via unions for each of the qty/price pairs based on item number. The real problem here, is that is is impossible to group by from the original table to get the desired result. Thus, we made our own table and via the select inserts, can put the data how it can be usable to us.

Once we had a workable table, we created a loop to set up our ranges of quantities. 1,2,3; or 1-4,5-10, 11 or more. No matter what the data, our loop will create our starting integer (i) along with its matching trailing integer (j) and that ranges corresponding price (price).

Finally, we check to see if the price is less than the standard price to make sure its actually a better deal, and thus, an upsell based on quantity. If not, skip. If it is, print out the range and its price. Loop through until all fields are exhausted and the 999999 item has been reached, thus 'or more'.
We also do a check to make sure that the item range is not the same as itself («Buy 1-1, $XX.XX») as that just looks stupid and is not a range, afterall. I put a little comment in the loop above about this.

With some clever looping, and a lot of coding, the desired result has been achieved.

Next time you're handed a database table that seems impossible to get the data you need from it, don't fret. There's always a way to get what you need so long as you have access to the data at all. A little converting, kicking and punching, and you will be able to print out just about anything in any format a client desires. Even if that client is your Uncle Charlie.

Comments, thoughts, and suggestions appreciated as always.

Big thanks to Matt Warden for coming up with the qtyranges() array concept which, with some tweaking, was the key to making this successful.

-Brian