The sql in the function getAverageCost() defined in the file reporting/rep301.php (Inventory Valuation Report) is like:
SELECT move.*, IF(ISNULL(supplier.supplier_id), debtor.debtor_no, supplier.supplier_id) person_id
FROM 1_stock_moves move
LEFT JOIN 1_supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type
LEFT JOIN 1_grn_batch grn ON grn.id=move.trans_no AND 25=move.type
LEFT JOIN 1_suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id
LEFT JOIN 1_debtor_trans cust_trans ON cust_trans.trans_no=move.trans_no AND cust_trans.type=move.type
LEFT JOIN 1_debtors_master debtor ON cust_trans.debtor_no=debtor.debtor_no
AND move.tran_date < '2017-11-27'
AND standard_cost > 0.001
AND qty <> 0
AND move.type <> 16 -- ST_LOCTRANSFER
ORDER BY tran_date;
This means that when the purchase invoice is made on 2017-11-27, the sql above computes the average cost to just before the said date and not including it. If a subsequent date is used for the query, then all is well (save for the irrational value at times not obeying the decimal limit).
@joe: must we correct line 65 in it:
AND move.tran_date < '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND move.type <> ".ST_LOCTRANSFER;
AND move.tran_date <= '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND move.type <> ".ST_LOCTRANSFER;
so that it takes into consideration all transaction up until the end of the day and not to just before the date?
The decimals issue can be corrected by appropriately formatting the return value at the end of the said function or where it it used in line 211 in the report although line 229 uses it without assignment when $details is present. The culprit is line 357 in includes/current_user.inc for irrational numbers with large number of decimal places
when the value of $dec becomes 14. It is best hard coded to 2.