Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Customer Special Price: expired but still grabbed
08-29-2020, 06:32 AM, (This post was last modified: 08-29-2020, 06:52 AM by HDeriauFF.)
#1
Customer Special Price: expired but still grabbed
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
Reply
08-29-2020, 07:26 AM,
#2
RE: Customer Special Price: expired but still grabbed
It looks like a bug. If nobody else picks it up sooner I will look at it this weekend.

Tim
Reply
08-29-2020, 08:41 AM,
#3
RE: Customer Special Price: expired but still grabbed
Hi Tim

Thanks!
Reply
08-29-2020, 08:51 AM, (This post was last modified: 08-29-2020, 08:53 AM by HDeriauFF.)
#4
RE: Customer Special Price: expired but still grabbed
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";
Reply
08-29-2020, 05:03 PM,
#5
RE: Customer Special Price: expired but still grabbed
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
Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Reply
08-30-2020, 11:11 AM,
#6
RE: Customer Special Price: expired but still grabbed
Thank you Phil!
Reply
08-30-2020, 12:32 PM, (This post was last modified: 08-30-2020, 03:25 PM by phil.)
#7
RE: Customer Special Price: expired but still grabbed
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 Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Reply
09-22-2020, 02:20 AM, (This post was last modified: 09-22-2020, 02:24 AM by HDeriauFF.)
#8
RE: Customer Special Price: expired but still grabbed
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
Reply
09-24-2020, 09:50 AM, (This post was last modified: 09-24-2020, 09:55 AM by HDeriauFF.)
#9
RE: Customer Special Price: expired but still grabbed
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


Attached Files
.php   GetPrice.php (Size: 7.87 KB / Downloads: 1)
Reply
09-24-2020, 10:00 AM,
#10
RE: Customer Special Price: expired but still grabbed
Thanks :-)
Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)