webERP Forum

Full Version: Customer Special Price: expired but still grabbed
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2
Hi Folks

Are you able to recreate the following issue:

in table "prices", item shows the following:
 
price = 4.90 | debtorno = | startdate = 2018-06-25 | enddate = 0000-00-00
price = 4.00 | debtorno = 103 | startdate = 2020-01-24 | enddate = 2020-08-13
 
when creating an order for customer 103, the item price is 4.00 instead of 4.90, despite the special price being expired

looking at the logic in includes/GetPrice.inc, it appears to be using the query at line 59

Thank you for your input!

webERP version 4.14.1
It looks like a bug. If nobody else picks it up sooner I will look at it this weekend.

Tim
Hi Tim

Thanks!
This seems to correct the issue:

Line 59: change 


Code:
$sql = "SELECT prices.price,
        prices.startdate
        FROM prices,
            debtorsmaster
        WHERE debtorsmaster.salestype=prices.typeabbrev
        AND debtorsmaster.debtorno ='" . $DebtorNo . "'
        AND prices.stockid = '" . $StockID . "'
        AND prices.currabrev = debtorsmaster.currcode
        AND prices.debtorno=debtorsmaster.debtorno
        AND prices.branchcode=''
        AND prices.startdate <='" . Date('Y-m-d') . "'
        AND prices.enddate >='0000-00-00'
        ORDER BY prices.startdate DESC";
 to:
Code:
$sql = "SELECT prices.price,
        prices.startdate
        FROM prices,
            debtorsmaster
        WHERE debtorsmaster.salestype=prices.typeabbrev
        AND debtorsmaster.debtorno !='" . $DebtorNo . "'
        AND prices.stockid = '" . $StockID . "'
        AND prices.currabrev = debtorsmaster.currcode
        AND prices.debtorno=debtorsmaster.debtorno
        AND prices.branchcode=''
        AND prices.startdate <='" . Date('Y-m-d') . "'
        AND prices.enddate >='0000-00-00'
        ORDER BY prices.startdate DESC";
Well I think we need to refer to - DebtorNo as we are getting the salestype from the debtorsmaster. Perhaps we should be looking for a salestype (price list) match within the start /end date .... I am thinking this query should be:

Code:
$sql = "SELECT prices.price,
        prices.startdate
        FROM prices,
            debtorsmaster
        WHERE debtorsmaster.salestype=prices.typeabbrev
        AND debtorsmaster.debtorno ='" . $DebtorNo . "'
        AND prices.stockid = '" . $StockID . "'
        AND prices.currabrev = debtorsmaster.currcode
        AND prices.debtorno=debtorsmaster.debtorno
        AND prices.branchcode=''
        AND prices.startdate <='" . Date('Y-m-d') . "'
        AND prices.enddate >='" . Date('Y-m-d') . "'
        ORDER BY prices.startdate DESC";

This shouldn't find a match unless the price end date is after the current date
Thank you Phil!
No it's still not right as we are joining on the prices.debtorno=debtorsmaster.debtorno - should not have that join there. I need to look more closely at this!
I think this is it ... removing the join on debtors master

Code:
$sql = "SELECT prices.price
FROM prices,
debtorsmaster
WHERE debtorsmaster.salestype=prices.typeabbrev
AND debtorsmaster.debtorno='" . $DebtorNo . "'
AND prices.stockid = '" . $StockID . "'
AND prices.currabrev = debtorsmaster.currcode
AND prices.branchcode=''
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate >='" . Date('Y-m-d') . "'
ORDER BY prices.startdate DESC";

Not tested!
Phil

The series of queries used in GetPrice.inc still seems to need some attention

tested with an item with

price = 2.25 | debtorno = | startdate = 2018-06-25 | enddate = 0000-00-00
price = 2.04 | debtorno = 103 | startdate = 2020-05-03 | enddate = 2020-07-20
price = 2.13 | debtorno = 103 | startdate = 2020-07-021 | enddate = 0000-00-00

we get 2.25 instead of 2.13
After taking a closer look at the code, I'm suspecting we are missing a query:

query 1 looks for a price for (debtor + branch) within [start - end]
query 2 looks for a most recent price for (debtor + branch)  within [start - 0000-00-00]
query 3 looks for price for (debtor - no branch) within [start - end]

I'm suspecting we might need to insert (after query 3 at line 44) an extra query for (debtor - no branch) within [start - 0000-00-00]


Code:
    $sql = "SELECT prices.price
FROM prices,
debtorsmaster
WHERE debtorsmaster.salestype=prices.typeabbrev
AND debtorsmaster.debtorno='" . $DebtorNo . "'
AND prices.stockid = '" . $StockID . "'
AND prices.currabrev = debtorsmaster.currcode
AND prices.debtorno=debtorsmaster.debtorno
AND prices.branchcode=''
AND prices.startdate <='" . Date('Y-m-d') . "'
AND prices.enddate ='0000-00-00'
ORDER BY prices.startdate DESC";

attached my full file (extension changed to .php since .inc is not allowed) with the change
Thanks :-)
Pages: 1 2