Customer Special Price: expired but still grabbed

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 - TimSchofield - 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 :-)