|
|
Posted by Jerry Stuckle on 05/11/05 18:55
duncan.lovett@litho.co.uk wrote:
> MySQL 4.0
> PHP 4
> Dreamweaver MX 2004
>
> I am currently working on a project which has taken a bit of a complex
> turn and left me a little puzzled, so any help would be appreciated.
>
> I have created a B2B ecommerce site for an IT based company, only
> account holders can purchase products off the site, this is controled
> by a login system linked to a table in MySQL containing customer code,
> privilages etc.
>
> I am at a point where discount structures have been requested to be
> implemented into the sytem. I have been briefed that offline purchases
> are put through an old DOS based system and discounts are worked out in
> three different ways, as follows:
>
> Customer account number is checked, then -
>
> - if products purchased belong to a particular 'Group' related to the
> customer number, a discount is applied
>
> - next, an individual product discount table is checked. If customer
> number and product code is in the table, a discount is applied, and
> this over-rides the previous discount.
>
> - last, customer number and stock code are checked to see if they are
> in a 'contract price' structure, where my client has agreed to sell the
> customers an item for a fixed price over a particular time. This
> over-rides all previous discounts.
>
> I want to recreate this system on the website to display correct prices
> depending on the discounts the user is entitled to.
>
> This seems quite complex to me, and I don't know SQL well enough to
> create a query to pull the results I need, so any ideas would be very
> much appreciated.
>
> The tables in my database are constructed like so:
>
> table: Products
> ---------------------------------------
> | prodGroup | prodCode | extra fields |
> ---------------------------------------
>
> table: Customers
> ---------------------------
> | custCode | extra fields |
> ---------------------------
>
> table: GroupDiscount
> -----------------------------------
> | custCode | prodGroup | Discount |
> -----------------------------------
>
> table: ItemDiscount
> ----------------------------------
> | custCode | prodCode | Discount |
> ----------------------------------
>
> table: ContractDiscount
> -----------------------------------------------
> | custCode | prodCode | Discount | expiryDate |
> -----------------------------------------------
>
> I am fine with setting up a query to pull discounts from one table, am
> very unsure how to set up a sequence to check through the tables and
> find the correct discount...
>
> Is this something one query could achieve or is a bit of PHP coding
> needed as well?
>
> your answers will be appreciated,
>
> Duncan
>
Duncan,
You possibly could do this in SQL. But I think it would be a lot clear
and easier to code if you use some PHP.
Personally, I'd do it in reverse sequence, i.e.
if contract price exists
use it
else
if individual product price exists
use it
else
if group price exists
use it
else
use retail price
The advantage here is fewer MySQL queries. If you use the order you
had, you'd always have to make 4 queries. This way you'll make no more
than 4 queries, but may make fewer.
The logic shouldn't be more than a few lines of code.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|