webERP Forum
StockMovements.php: wrong results when customer has several branches - 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: StockMovements.php: wrong results when customer has several branches (/showthread.php?tid=8325)



StockMovements.php: wrong results when customer has several branches - HDeriauFF - 02-09-2019

a stock movement report for an item sold to one branch of a customer with multiple branches shows repeating rows for all branches

Line 73: changed from:
Code:
$sql = "SELECT stockmoves.stockid,
                systypes.typename,
                stockmoves.type,
                stockmoves.transno,
                stockmoves.trandate,
                stockmoves.userid,
                stockmoves.debtorno,
                stockmoves.branchcode,
                custbranch.brname,
                stockmoves.qty,
                stockmoves.reference,
                stockmoves.price,
                stockmoves.discountpercent,
                stockmoves.newqoh,
                stockmaster.decimalplaces
        FROM stockmoves
        INNER JOIN systypes ON stockmoves.type=systypes.typeid
        INNER JOIN stockmaster ON stockmoves.stockid=stockmaster.stockid
        INNER JOIN custbranch ON stockmoves.debtorno=custbranch.debtorno
        WHERE  stockmoves.loccode='" . $_POST['StockLocation'] . "'
        AND stockmoves.trandate >= '". $SQLAfterDate . "'
        AND stockmoves.stockid = '" . $StockID . "'
        AND stockmoves.trandate <= '" . $SQLBeforeDate . "'
        AND hidemovt=0
        ORDER BY stkmoveno DESC";

to:

Code:
$sql = "SELECT stockmoves.stockid,
                systypes.typename,
                stockmoves.type,
                stockmoves.transno,
                stockmoves.trandate,
                stockmoves.userid,
                stockmoves.debtorno,
                stockmoves.branchcode,
                custbranch.brname,
                stockmoves.qty,
                stockmoves.reference,
                stockmoves.price,
                stockmoves.discountpercent,
                stockmoves.newqoh,
                stockmaster.decimalplaces
        FROM stockmoves
        INNER JOIN systypes ON stockmoves.type=systypes.typeid
        INNER JOIN stockmaster ON stockmoves.stockid=stockmaster.stockid
        INNER JOIN custbranch ON stockmoves.debtorno=custbranch.debtorno
        WHERE  stockmoves.loccode='" . $_POST['StockLocation'] . "'
        AND stockmoves.trandate >= '". $SQLAfterDate . "'
        AND stockmoves.stockid = '" . $StockID . "'
        AND stockmoves.trandate <= '" . $SQLBeforeDate . "'
        AND stockmoves.branchcode = custbranch.branchcode
        AND hidemovt=0
        ORDER BY stkmoveno DESC";

attached before and after


RE: StockMovements.php: wrong results when customer has several branches - VortecCPI - 02-09-2019

Nice catch -- Thank you.


RE: StockMovements.php: wrong results when customer has several branches - TurboPT - 02-13-2019

@HDeriauFF

...is the custbranch JOIN your addition to get the custbranch.brname ?

I don't see that custbranch is in the repository as a part of that query.
Not saying that it is wrong, but appears to be a custom change?

This is the current query in the repository for StockMovements:
Code:
$SQL = "SELECT stockmoves.stockid,
                systypes.typename,
                stockmoves.stkmoveno,
                stockmoves.type,
                stockmoves.transno,
                stockmoves.trandate,
                stockmoves.userid,
                stockmoves.debtorno,
                stockmoves.branchcode,
                stockmoves.qty,
                stockmoves.reference,
                stockmoves.price,
                stockmoves.discountpercent,
                stockmoves.newqoh,
                stockmoves.narrative,
                stockmaster.decimalplaces,
                stockmaster.controlled,
                stockmaster.serialised
        FROM stockmoves
        INNER JOIN systypes
            ON stockmoves.type=systypes.typeid
        INNER JOIN stockmaster
            ON stockmoves.stockid=stockmaster.stockid
        WHERE  stockmoves.loccode='" . $_POST['StockLocation'] . "'
            AND stockmoves.trandate >= '" . $SQLAfterDate . "'
            AND stockmoves.stockid = '" . $StockID . "'
            AND stockmoves.trandate <= '" . $SQLBeforeDate . "'
            AND hidemovt=0
        ORDER BY stkmoveno DESC";

...if it happens to have been added as part of another post, then please add a link. I'm trying to catch-up on other forum changes/submissions since December.


RE: StockMovements.php: wrong results when customer has several branches - TimSchofield - 04-13-2019

It does appear to be a custom change, but it is also a good change so I have added it, and sent a pull request.

Tim


RE: StockMovements.php: wrong results when customer has several branches - HDeriauFF - 04-15-2019

Yes it was a custom change, that's actually its addition that made me discovered the issue.

Thanks!