webERP Forum
Customer Special Price: expired but still grabbed - Printable Version

+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (http://www.weberp.org/forum/forumdisplay.php?fid=1)
+--- Forum: Problems / Bugs? (http://www.weberp.org/forum/forumdisplay.php?fid=8)
+--- Thread: Customer Special Price: expired but still grabbed (/showthread.php?tid=8407)

Pages: 1 2


Customer Special Price: expired but still grabbed - HDeriauFF - 08-29-2020

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


RE: Customer Special Price: expired but still grabbed - falkoner - 08-29-2020

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

Tim


RE: Customer Special Price: expired but still grabbed - HDeriauFF - 08-29-2020

Hi Tim

Thanks!


RE: Customer Special Price: expired but still grabbed - HDeriauFF - 08-29-2020

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";



RE: Customer Special Price: expired but still grabbed - phil - 08-29-2020

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


RE: Customer Special Price: expired but still grabbed - HDeriauFF - 08-30-2020

Thank you Phil!


RE: Customer Special Price: expired but still grabbed - phil - 08-30-2020

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!


RE: Customer Special Price: expired but still grabbed - HDeriauFF - 09-22-2020

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


RE: Customer Special Price: expired but still grabbed - HDeriauFF - 09-24-2020

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


RE: Customer Special Price: expired but still grabbed - phil - 09-24-2020

Thanks :-)