Eric Creese wrote:
> I have an ecom app I put together sometime ago. I need to allow for tired
> pricing for a customer so when some one updates their basket I need to 1.) do
> a check on the product and see if it has tiered pricing. 2.) if it does I
> need to determine the correct tier. 3.) apply the update to the basket.
The first step is to generalise the logic which goes hand in hand
with database normalization: make every product have tiered
pricing. Some product will have only one tier for all quantities,
but now you can use one set of logic for all products :)
> <!----table headers--->
> <table border="0" width="95%" align="center" cellspacing="0" cellpadding="3">
Please make code easy to read for us. Layout like alignment and
fonts is not relevant for the problem you have, which is
application logic, so just remove it. Remember, everything that
makes it easier for us to answer increases the chance you get an
answer.
Besides, you should be using CSS for the layout :)
> <cflock scope="SESSION" timeout="30">
> <cfscript>
> NumberItems = ArrayLen(Session.arrBasket);
> ProductID = Session.arrBasket[Request.idx][1];
> ProductName = Session.arrBasket[Request.idx][2];
> Price = Session.arrBasket[Request.idx][3];
> Qty = Session.arrBasket[Request.idx][4];
> </cfscript>
Price and ProductName are not independent properties, they depend
on ProductID and Qty. Do you really want to store them
redundantly in the session? I think it would be easier to just
store ProductID and Qty in the session and get the rest from the
database when you need it.
With a typical data structure of a product table (productID,
productName) and a price table (priceID, productID, prodFrom,
prodTo, price) getting the values for dsplay in the basket would
have a logic like:
<cfset variables.productlist = ''>
<cfset variables.qtylist = ''>
<cflock session ...>
<cfloop from="1" to="#ArrayLen(Session.arrBasket)#"
index="variables.idx">
<cfset
ListAppend(variables.productlist,Session.arrBasket[variables.idx][1])>
<cfset
ListAppend(variables.qtylist,Session.arrBasket[variables.idx][4])>
</cfloop>
</cflock>
<cfif ListLen(variables.productlist)>
<cfquery name="basket">
SELECT productID, product.productName, price.price
FROM product INNER JOIN price USING productID
WHERE productID = <cfqueryparam
value="#ListFirst(variables.productlist)#">
AND <cfqueryparam value="#ListFirst(variables.qtylist)#">
BETWEEN price.prodFrom AND price.prodTo
<cfloop from="2" to="#ListLen(variables.productlist)#"
index="#variables.idx#">
UNION
SELECT productID, product.productName, price.price
FROM product INNER JOIN price USING productID
WHERE productID = <cfqueryparam
value="#ListGetAt(variables.productlist, variables.idx)#">
AND <cfqueryparam value="#ListGetAt(variables.qtylist,
variables.idx)#"> BETWEEN price.prodfrom AND price.prodto
</cfloop>
</cfquery>
<cfelse>
something>
</cfif>
One caveat: BETWEEN is inclusive, so be carefull not to create
overlapping ranges.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:206162
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
