Thanks for your participation, boxygen. You have made my day 
/Joe
]]>The line numbers stated by @boxygen are for the FA 2.4 version of reporting/rep301.php. The equivalent line numbers for FA 2.3 are 187 to 196.
]]>For backwards compatibility in 2.4 we will just change the value $use_costed_values to 1 in config.default.php, so all new users will automatically get this option.
Should they prefer the old method for some reason, they can set this value to 0.
Thanks @boxygen for helping with this.
/Joe
]]>Replace lines from 209-218 with following lines
$UnitCost = getAverageCost($trans['stock_id'], $location, $date);
$ItemTotal = $trans['QtyOnHand'] * $UnitCost;
Hence now no need of $use_costed_values in config.php
]]>Committed to my repo after synching the whitespaces first so that the actual changes alone can be visible clearly.
]]>Thanks for your participation, boxygen. You have made my day
/Joe
]]>Will look into this and optimize it. Forget my earlier files. Will remove them.
/Joe
]]><?php
/**********************************************************************
Copyright (C) FrontAccounting, LLC.
Released under the terms of the GNU General Public License, GPL,
as published by the Free Software Foundation, either version 3
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision: 2.1 $
// Creator: boxygen
// date_: 2017-05-12
// Title: Costed Inventory Movements
// ----------------------------------------------------------------
$path_to_root="..";
include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/ui/ui_input.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/sales/includes/db/sales_types_db.inc");
include_once($path_to_root . "/inventory/includes/inventory_db.inc");
//----------------------------------------------------------------------------------------------------
inventory_movements();
function get_domestic_price($myrow, $stock_id)
{
if ($myrow['type'] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT)
{
$price = $myrow['price'];
if ($myrow['person_id'] > 0)
{
// Do we have foreign currency?
$supp = get_supplier($myrow['person_id']);
$currency = $supp['curr_code'];
$ex_rate = get_exchange_rate_to_home_currency($currency, sql2date($myrow['tran_date']));
$price /= $ex_rate;
}
}
else
$price = $myrow['standard_cost']; // Item Adjustments just have the real cost
return $price;
}
function fetch_items($category=0)
{
$sql = "SELECT stock_id, stock.description AS name,
stock.category_id,units,
cat.description
FROM ".TB_PREF."stock_master stock LEFT JOIN ".TB_PREF."stock_category cat ON stock.category_id=cat.category_id
WHERE mb_flag <> 'D'";
if ($category != 0)
$sql .= " AND cat.category_id = ".db_escape($category);
$sql .= " ORDER BY stock.category_id, stock_id";
return db_query($sql,"No transactions were returned");
}
function trans_qty($stock_id, $location=null, $from_date, $to_date, $inward = true)
{
if ($from_date == null)
$from_date = Today();
$from_date = date2sql($from_date);
if ($to_date == null)
$to_date = Today();
$to_date = date2sql($to_date);
$sql = "SELECT ".($inward ? '' : '-')."SUM(qty) FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)."
AND tran_date >= '$from_date'
AND tran_date <= '$to_date' AND type <> ".ST_LOCTRANSFER;
if ($location != '')
$sql .= " AND loc_code = ".db_escape($location);
if ($inward)
$sql .= " AND qty > 0 ";
else
$sql .= " AND qty < 0 ";
$result = db_query($sql, "QOH calculation failed");
$myrow = db_fetch_row($result);
return $myrow[0];
}
function avg_unit_cost($stock_id, $location=null, $to_date)
{
if ($to_date == null)
$to_date = Today();
$to_date = date2sql($to_date);
$sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)."
AND tran_date < '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;
if ($location != '')
$sql .= " AND loc_code = ".db_escape($location);
$sql .= " ORDER BY tran_date";
$result = db_query($sql, "No standard cost transactions were returned");
if ($result == false)
return 0;
$qty = $tot_cost = 0;
while ($row=db_fetch($result))
{
$qty += $row['qty'];
$price = get_domestic_price($row, $stock_id);
$tran_cost = $price * $row['qty'];
$tot_cost += $tran_cost;
}
if ($qty != 0)
return $tot_cost/ $qty;
else
return 0; //by Faisal
}
//----------------------------------------------------------------------------------------------------
function trans_qty_unit_cost($stock_id, $location=null, $from_date, $to_date, $inward = true)
{
if ($from_date == null)
$from_date = Today();
$from_date = date2sql($from_date);
if ($to_date == null)
$to_date = Today();
$to_date = date2sql($to_date);
$sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)."
AND tran_date <= '$to_date' AND tran_date >= '$from_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;
if ($location != '')
$sql .= " AND loc_code = ".db_escape($location);
if ($inward)
$sql .= " AND qty > 0 ";
else
$sql .= " AND qty < 0 ";
$sql .= " ORDER BY tran_date";
$result = db_query($sql, "No standard cost transactions were returned");
if ($result == false)
return 0;
$qty = $tot_cost = 0;
while ($row=db_fetch($result))
{
$qty += $row['qty'];
$price = get_domestic_price($row, $stock_id);
$tran_cost = $row['qty'] * $price;
$tot_cost += $tran_cost;
}
if ($qty != 0)
return $tot_cost/ $qty;
else
return 0; //by Faisal
}
//----------------------------------------------------------------------------------------------------
function inventory_movements()
{
global $path_to_root;
$from_date = $_POST['PARAM_0'];
$to_date = $_POST['PARAM_1'];
$category = $_POST['PARAM_2'];
$location = $_POST['PARAM_3'];
$comments = $_POST['PARAM_4'];
$orientation = $_POST['PARAM_5'];
$destination = $_POST['PARAM_6'];
if ($destination)
include_once($path_to_root . "/reporting/includes/excel_report.inc");
else
include_once($path_to_root . "/reporting/includes/pdf_report.inc");
$orientation = ($orientation ? 'L' : 'P');
if ($category == ALL_NUMERIC)
$category = 0;
if ($category == 0)
$cat = _('All');
else
$cat = get_category_name($category);
if ($location == '')
$loc = _('All');
else
$loc = get_location_name($location);
$cols = array(0, 60, 130, 160, 185, 215, 250, 275, 305, 340, 365, 395, 430, 455, 485, 520);
$headers = array(_('Category'), _('Description'), _('UOM'), '', '', _('OpeningStock'), '', '',_('StockIn'), '', '', _('Delivery'), '', '', _('ClosingStock'));
$headers2 = array("", "", "", _("QTY"), _("Rate"), _("Value"), _("QTY"), _("Rate"), _("Value"), _("QTY"), _("Rate"), _("Value"), _("QTY"), _("Rate"), _("Value"));
$aligns = array('left', 'left', 'left', 'right', 'right', 'right', 'right','right' ,'right', 'right', 'right','right', 'right', 'right', 'right');
$params = array( 0 => $comments,
1 => array('text' => _('Period'), 'from' => $from_date, 'to' => $to_date),
2 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
3 => array('text' => _('Location'), 'from' => $loc, 'to' => ''));
$rep = new FrontReport(_('Costed Inventory Movements'), "CostedInventoryMovements", user_pagesize(), 8, $orientation);
if ($orientation == 'L')
recalculate_cols($cols);
$rep->Font();
$rep->Info($params, $cols, $headers2, $aligns, $cols, $headers, $aligns);
$rep->NewPage();
$totval_open = $totval_in = $totval_out = $totval_close = 0;
$result = fetch_items($category);
$dec = user_price_dec();
$catgor = '';
while ($myrow=db_fetch($result))
{
if ($catgor != $myrow['description'])
{
$rep->NewLine(2);
$rep->fontSize += 2;
$rep->TextCol(0, 3, $myrow['category_id'] . " - " . $myrow['description']);
$catgor = $myrow['description'];
$rep->fontSize -= 2;
$rep->NewLine();
}
$qoh_start = get_qoh_on_date($myrow['stock_id'], $location, add_days($from_date, -1));
$qoh_end = get_qoh_on_date($myrow['stock_id'], $location, $to_date);
$inward = trans_qty($myrow['stock_id'], $location, $from_date, $to_date);
$outward = trans_qty($myrow['stock_id'], $location, $from_date, $to_date, false);
$openCost = avg_unit_cost($myrow['stock_id'], $location, $from_date);
$unitCost = avg_unit_cost($myrow['stock_id'], $location, add_days($to_date, 1));
if ($qoh_start == 0 && $inward == 0 && $outward == 0 && $qoh_end == 0)
continue;
$rep->NewLine();
$rep->TextCol(0, 1, $myrow['stock_id']);
$rep->TextCol(1, 2, $myrow['name']);
$rep->TextCol(2, 3, $myrow['units']);
$rep->AmountCol(3, 4, $qoh_start, get_qty_dec($myrow['stock_id']));
$rep->AmountCol(4, 5, $openCost, $dec);
$openCost *= $qoh_start;
$totval_open += $openCost;
$rep->AmountCol(5, 6, $openCost);
if($inward>0){
$rep->AmountCol(6, 7, $inward, get_qty_dec($myrow['stock_id']));
$unitCost_in = trans_qty_unit_cost($myrow['stock_id'], $location, $from_date, $to_date);
$rep->AmountCol(7, 8, $unitCost_in,$dec);
$unitCost_in *= $inward;
$totval_in += $unitCost_in;
$rep->AmountCol(8, 9, $unitCost_in);
}
if($outward>0){
$rep->AmountCol(9, 10, $outward, get_qty_dec($myrow['stock_id']));
$unitCost_out = trans_qty_unit_cost($myrow['stock_id'], $location, $from_date, $to_date, false);
$rep->AmountCol(10, 11, $unitCost_out,$dec);
$unitCost_out *= $outward;
$totval_out += $unitCost_out;
$rep->AmountCol(11, 12, $unitCost_out);
}
$rep->AmountCol(12, 13, $qoh_end, get_qty_dec($myrow['stock_id']));
$rep->AmountCol(13, 14, $unitCost,$dec);
$unitCost *= $qoh_end;
$totval_close += $unitCost;
$rep->AmountCol(14, 15, $unitCost);
$rep->NewLine(0, 1);
}
$rep->Line($rep->row - 4);
$rep->NewLine(2);
$rep->TextCol(0, 1, _("Total"));
$rep->AmountCol(5, 6, $totval_open);
$rep->AmountCol(8, 9, $totval_in);
$rep->AmountCol(11, 12, $totval_out);
$rep->AmountCol(14, 15, $totval_close);
$rep->Line($rep->row - 4);
$rep->End();
}
?>
<?php
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision: 2.0 $
// Creator: Joe Hunt
// date_: 2005-05-19
// Title: Inventory Valuation
// ----------------------------------------------------------------
$path_to_root="..";
include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_category_db.inc");
//----------------------------------------------------------------------------------------------------
print_inventory_valuation_report();
function get_domestic_price($myrow, $stock_id)
{
if ($myrow['type'] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT)
{
$price = $myrow['price'];
if ($myrow['person_id'] > 0)
{
// Do we have foreign currency?
$supp = get_supplier($myrow['person_id']);
$currency = $supp['curr_code'];
$ex_rate = get_exchange_rate_to_home_currency($currency, sql2date($myrow['tran_date']));
$price /= $ex_rate;
}
}
else
$price = $myrow['standard_cost']; // Item Adjustments just have the real cost
return $price;
}
function getAverageCost($stock_id, $to_date)
{
if ($to_date == null)
$to_date = Today();
$to_date = date2sql($to_date);
$sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)."
AND tran_date <= '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;
$sql .= " ORDER BY tran_date";
$result = db_query($sql, "No standard cost transactions were returned");
if ($result == false)
return 0;
$qty = $old_qty = $count = $old_std_cost = $tot_cost = 0;
while ($row=db_fetch($result))
{
$qty += $row['qty'];
$price = get_domestic_price($row, $stock_id);
//$old_std_cost = $row['standard_cost'];
$tran_cost = $row['qty'] * $price;
$tot_cost += $tran_cost;
//$count++;
$old_qty = $qty;
}
if ($qty == 0)
return 0;
return $tot_cost / $qty;
}
function getTransactions($category, $location, $date)
{
$date = date2sql($date);
$sql = "SELECT ".TB_PREF."stock_master.category_id,
".TB_PREF."stock_category.description AS cat_description,
".TB_PREF."stock_master.stock_id,
".TB_PREF."stock_master.units,
".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive,
".TB_PREF."stock_moves.loc_code,
SUM(".TB_PREF."stock_moves.qty) AS QtyOnHand,
".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS UnitCost,
SUM(".TB_PREF."stock_moves.qty) *(".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost) AS ItemTotal
FROM ".TB_PREF."stock_master,
".TB_PREF."stock_category,
".TB_PREF."stock_moves
WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id
AND ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id
AND ".TB_PREF."stock_master.mb_flag<>'D'
AND ".TB_PREF."stock_moves.tran_date <= '$date'
GROUP BY ".TB_PREF."stock_master.category_id,
".TB_PREF."stock_category.description, ";
if ($location != 'all')
$sql .= TB_PREF."stock_moves.loc_code, ";
$sql .= TB_PREF."stock_master.stock_id,
".TB_PREF."stock_master.description
HAVING SUM(".TB_PREF."stock_moves.qty) != 0";
if ($category != 0)
$sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category);
if ($location != 'all')
$sql .= " AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location);
$sql .= " ORDER BY ".TB_PREF."stock_master.category_id,
".TB_PREF."stock_master.stock_id";
return db_query($sql,"No transactions were returned");
}
//----------------------------------------------------------------------------------------------------
function print_inventory_valuation_report()
{
global $path_to_root, $use_costed_values;;
$date = $_POST['PARAM_0'];
$category = $_POST['PARAM_1'];
$location = $_POST['PARAM_2'];
$detail = $_POST['PARAM_3'];
$comments = $_POST['PARAM_4'];
$orientation = $_POST['PARAM_5'];
$destination = $_POST['PARAM_6'];
if ($destination)
include_once($path_to_root . "/reporting/includes/excel_report.inc");
else
include_once($path_to_root . "/reporting/includes/pdf_report.inc");
$detail = !$detail;
$dec = user_price_dec();
$orientation = ($orientation ? 'L' : 'P');
if ($category == ALL_NUMERIC)
$category = 0;
if ($category == 0)
$cat = _('All');
else
$cat = get_category_name($category);
if ($location == ALL_TEXT)
$location = 'all';
if ($location == 'all')
$loc = _('All');
else
$loc = get_location_name($location);
$cols = array(0, 75, 225, 250, 350, 450, 515);
$headers = array(_('Category'), '', _('UOM'), _('Quantity'), _('Unit Cost'), _('Value'));
$aligns = array('left', 'left', 'left', 'right', 'right', 'right');
$params = array( 0 => $comments,
1 => array('text' => _('End Date'), 'from' => $date, 'to' => ''),
2 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
3 => array('text' => _('Location'), 'from' => $loc, 'to' => ''));
$rep = new FrontReport(_('Inventory Valuation Report'), "InventoryValReport", user_pagesize(), 9, $orientation);
if ($orientation == 'L')
recalculate_cols($cols);
$rep->Font();
$rep->Info($params, $cols, $headers, $aligns);
$rep->NewPage();
$res = getTransactions($category, $location, $date);
$total = $grandtotal = 0.0;
$catt = '';
while ($trans=db_fetch($res))
{
if ($catt != $trans['cat_description'])
{
if ($catt != '')
{
if ($detail)
{
$rep->NewLine(2, 3);
$rep->TextCol(0, 4, _('Total'));
}
$rep->AmountCol(5, 6, $total, $dec);
if ($detail)
{
$rep->Line($rep->row - 2);
$rep->NewLine();
}
$rep->NewLine();
$total = 0.0;
}
$rep->TextCol(0, 1, $trans['category_id']);
$rep->TextCol(1, 2, $trans['cat_description']);
$catt = $trans['cat_description'];
if ($detail)
$rep->NewLine();
}
if (isset($use_costed_values) && $use_costed_values==1)
{
$UnitCost = getAverageCost($trans['stock_id'], $date);
$ItemTotal = $trans['QtyOnHand'] * $UnitCost;
}
else
{
$UnitCost = $trans['UnitCost'];
$ItemTotal = $trans['ItemTotal'];
}
if ($detail)
{
$rep->NewLine();
$rep->fontSize -= 2;
$rep->TextCol(0, 1, $trans['stock_id']);
$rep->TextCol(1, 2, $trans['description'].($trans['inactive']==1 ? " ("._("Inactive").")" : ""), -1);
$rep->TextCol(2, 3, $trans['units']);
$rep->AmountCol(3, 4, $trans['QtyOnHand'], get_qty_dec($trans['stock_id']));
$dec2 = 0;
price_decimal_format($UnitCost, $dec2);
$rep->AmountCol(4, 5, $UnitCost, $dec2);
$rep->AmountCol(5, 6, $ItemTotal, $dec);
$rep->fontSize += 2;
}
$total += $ItemTotal;
$grandtotal += $ItemTotal;
}
if ($detail)
{
$rep->NewLine(2, 3);
$rep->TextCol(0, 4, _('Total'));
}
$rep->Amountcol(5, 6, $total, $dec);
if ($detail)
{
$rep->Line($rep->row - 2);
$rep->NewLine();
}
$rep->NewLine(2, 1);
$rep->TextCol(0, 4, _('Grand Total'));
$rep->AmountCol(5, 6, $grandtotal, $dec);
$rep->Line($rep->row - 4);
$rep->NewLine();
$rep->End();
}
?>
]]>Finally got all reports reconciled with each other as well as with GL.
However I am not claiming so please have a critical look.
I edited the function get_domestic_price() in both rep301 and rep308.
Now both of them are reconciled with each other as well as GL whether you set the flag $use_costed_value to 1 or 0.
]]>Please change with the following Code from Line 81 to 93
$qty += $row['qty'];
$price = $row['standard_cost']; //get_domestic_price($row, $stock_id, $qty, $old_std_cost, $old_qty);
//$old_std_cost = $row['standard_cost'];
$tran_cost = $row['qty'] * $price;
$tot_cost += $tran_cost;
//$count++;
$old_qty = $qty;
}
if ($qty == 0)
return 0;
return $tot_cost / $qty;
Here again there is no use of function get_domestic_price.
After committing this change the Costed Inventory Valuation by rep301 is same as that of rep308.
But both these Inventory Valuations are not reconciling with that of Trial Balance.
While with value of $use_costed_value = 0 the Inventory Valuation of rep301 reconciles with Trial Balance.
So now we are in a fix whether to reconcile rep301 with rep308 or with GL.
We need to further look into it minutely that these two reports shall also match with GL.
/Faisal
]]>There is a flag in config.php, called $use_costed_values that is set to 0 by default. In that case the standard cost set in the stock_master table (the last average standard cost) is used all over the period. This is only used in rep301.php
If this flag is set to 1, it will use the costed values all over the period, and the total will be the same as in the Costed Inventory Movements.
The reason for setting this variable $use_costed_values to 0 (default) is that this also can cope with manual changes in the standard costs.
I guess we use the same algorithms for Costed Values in both rep 301 and 308. Right?
/Joe
]]>Its a great pleasure for me that I have contributed to FA.
We can't change rep301 because it is reconciled with the Inventory Valuation given by Trial Balance.
I am looking into this report further to have it reconciled with rep301 also.
/Faisal
]]>I guess your idea is better. The question is if we should change rep301 too in accordance to this. What do you think?
I have changed the copyright to FrontAccounting LLC and inserted your name as the creator. I have also optimized the report.
Please rename the file to extension php.
Can I commit this report and also in 2.4?
/Joe
]]>