|
|
Posted by Jerry Stuckle on 05/11/05 18:59
Jerry Stuckle wrote:
> 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.
>
>
Oops, I read it again. You're talking about discounts, not prices.
So - just get the retail price first. Then apply discounts in the order
I mentioned.
One other thought here - are you sure you don't have to take the
greatest of the discounts (if any). For instance, if the contract
discount is 5% but the current group discount is 10%, which do you take?
As a buyer, I'd want the 10% discount.
This case could be easier to do in a single SQL statement - but I'd
still do it 3 times and use PHP for the logic.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|