Its my pleasure. Thanks for giving us such a rich platform.
576 05/15/2017 05:08:46 am
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
577 05/14/2017 11:57:04 am
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
REP308
<?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();
}
?>
578 05/14/2017 11:54:54 am
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
REP301
<?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();
}
?>
579 05/14/2017 11:42:50 am
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
Hurrah,
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.
580 05/14/2017 11:03:49 am
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
There is again a logical Bug in rep301. If we set $use_costed_value = 1 it will call function getAverageCost(). In this function the algorithm to calculate Average Cost is not Weighted Average.
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
581 05/14/2017 05:21:04 am
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
Thanks 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
582 05/12/2017 09:42:03 pm
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
The Exchange Rate Factor will only affect when we pick the rate from Supplier's table. But doing that will again disturb this report. Since I am picking the rate from stock_moves table and this rate is already adjusted by the exchange rate at the time of transaction so no need to take into account the factor of exchange rate.
583 05/12/2017 09:38:34 pm
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
I have made few adjustments to this report.
1. Forced to return only the standard cost of the transaction instead of any supplier price or any other factor from the function get_domestic_price()
2. The Weighted Average Cost Calculation was not done in two functions avg_unit_cost() and trans_qty_unit_cost()
Now this report is reconciled means it is showing the result Opening Stock + Stock In - Delivers = Closing Stock
But it has few assumptions.
The Closing Stock Value may differ from Stock Valuation Report because in this report the base is only Transactional Cost i.e. standard_cost from stock_moves table. While the Stock Valuation report depends on latest Avg Cost of the item.
Please check and confirm.
584 05/12/2017 09:30:57 pm
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
<?php
/**********************************************************************
Copyright (C) Boxygen, 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.0 $
// Creator: Jujuk, Joe Hunt
// date_: 2011-05-24
// Title: Stock 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, $qty, $old_std_cost, $old_qty)
{
if ($myrow['type'] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT)
{
$price = $myrow['price'];
if ($myrow['type'] == ST_SUPPRECEIVE)
{
// Has the supplier invoice increased the receival price?
$sql = "SELECT DISTINCT act_price FROM ".TB_PREF."purch_order_details pod INNER JOIN ".TB_PREF."grn_batch grn ON pod.order_no =
grn.purch_order_no WHERE grn.id = ".$myrow['trans_no']." AND pod.item_code = '$stock_id'";
$result = db_query($sql, "Could not retrieve act_price from purch_order_details");
$row = db_fetch_row($result);
if ($row[0] > 0 AND $row[0] <> $myrow['price'])
$price = $row[0];
}
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;
}
}
elseif ($myrow['type'] != ST_INVADJUST) // calcutale the price from avg. price
$price = ($myrow['standard_cost'] * $qty - $old_std_cost * $old_qty) / $myrow['qty'];
else
$price = $myrow['standard_cost']; // Item Adjustments just have the real cost
$price = $myrow['standard_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 = $old_qty = $count = $old_std_cost = $tot_cost = 0;
while ($row=db_fetch($result))
{
$qty += $row['qty'];
$price = get_domestic_price($row, $stock_id, $qty, $old_std_cost, $old_qty);
$old_std_cost = $row['standard_cost'];
$tran_cost = $price * $row['qty'];
$tot_cost += $tran_cost;
$count++;
$old_qty = $qty;
}
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 = $count = $old_qty = $old_std_cost = $tot_cost = 0;
while ($row=db_fetch($result))
{
$qty += $row['qty'];
$price = get_domestic_price($row, $stock_id, $qty, $old_std_cost, $old_qty);
$tran_cost = $row['qty'] * $price;
$tot_cost += $tran_cost;
$count++;
$old_std_cost = $row['standard_cost'];
$old_qty = $qty;
}
if ($count == 0)
return 0;
return $tot_cost / $qty;
}
//----------------------------------------------------------------------------------------------------
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();
}
?>
585 05/12/2017 07:07:06 pm
Re: Report Redirect in New Tab (9 replies, posted in Reporting)
Dear @apmuthu thats really not practical. Sometimes we need to compare reports so quickly that saving a report locally and then opening them again in a pdf reader is cumbersome.
I went into the code and finally got the solution
In utils.js file Commenting Line no 127 and adding below line solves the problem
127 //window.open(data,'REP_WINDOW','toolbar=no,scrollbars=yes,resizable=yes,menubar=no'); 128 window.open(data);
This will work when in Preferences the option to view reports in pop up window is checked.
Hope this may help others too.
Regards.
586 05/12/2017 03:43:43 pm
Re: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
Actually I implemented this ERP in an organization. The CFO is a chartered accountant. He put me in a difficult position that how to reconcile the COGS reflecting in Profit and Loss through Inventory Movement.
He said that COGS shall be come through formula Opening Inventory + Stock In - Closing Inventory.
For that purpose I had to review this report but the report itself is not correct.
So Please try to check the Code and Correct this bug.
Thanks
587 05/11/2017 07:47:39 am
Topic: Costed Inventory Movement Report is not Reconciled (23 replies, posted in Report Bugs here)
Hello,
If you login here Costed Inventory Movement
with id: admin
pass: 1234
company: gp
You will find that the Report is not Reconciled
i.e. Opening Stock + Stock In - Deliveries IS NOT EQUAL TO Closing Stock
I tried with different categories also and found the same Non Reconciled.
If you try the Item Category 4x4 Ltr. This is the simplest. You will find the difference in Rates that are causing the difference.
Any Ideas
588 05/11/2017 06:37:37 am
Re: Report Redirect in New Tab (9 replies, posted in Reporting)
I can't understand your reply @apmuthu.
When we need to compare 2 or three reports each other we can't because FA opens new report in the same window of previous report. Is there any solution to it?
589 05/05/2017 03:22:31 pm
Re: HRM Attendance (3 replies, posted in Reporting)
You can try Payroll Module added by @Notrinos to FA2.4
590 05/05/2017 03:19:16 pm
Re: FA WITH AUTOCOMPLETE DOWNLOAD (36 replies, posted in FA Modifications)
In Post 16 bogeyman has given the link to download
http://stuff.w3shaman.com/others/fa-autocomplete.zip
591 03/22/2017 10:42:36 am
Topic: Memo Details doesn't appear in Full in Bank Statement (1 replies, posted in Reporting)
Is it possible that Memo details appear in full in Bank Statement. It doesn't matter if it goes for next line.
592 02/19/2017 06:58:22 pm
Re: Report Redirect in New Tab (9 replies, posted in Reporting)
I have tried both chrome and firefox.
In my preferences I have marked checked Use popup window to display reports:
Both firefox and chrome offers some plugins to manage popups but even if the report is opened in a new tab. When I click another report the same NEW TAB is updated instead of opening NEW REPORT in another NEW TAB.
I have just changed the email address and now receiving emails.
593 02/17/2017 01:34:22 pm
Re: Report Redirect in New Tab (9 replies, posted in Reporting)
Please help me to do it through code.
While using the browser feature the problem is that if any new report is opened the browser update the same NEW TAB just like it used to update the POP UP window.
I want to open each time a report button is clicked it goes to new tab.
By the way I am not getting forum responses in my email. Is there any problem?
594 02/15/2017 07:00:36 am
Topic: Report Redirect in New Tab (9 replies, posted in Reporting)
I have uncheck Use Popup for reports in my preferences.
Now report is opening in the same tab of application.
How can I modify it so that report shall open in new tab.
Any ideas?
595 02/15/2017 06:57:38 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
Thats great I will test it and will respond.
596 02/09/2017 07:12:02 pm
Topic: Discount as Absolute Value (0 replies, posted in FA Modifications)
On Sales Order Entry Form the Line Discount is in Percentage. I want to add another text box that should take input the Line Discount as Absolute Value.
Upon Lost Focus of that Text Box the Discount Percentage is automatically calculated and updated in the Discount Percentage Box through Javascript.
Can any one help me in that regard?
1. From where can I add another text box?
2. From where can I configure the Javascript code to update Percentage Box through Absolute Value Discount.
Regards.
597 02/03/2017 04:49:46 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
By the way while installing with 2.4RC1 I am receiving following error.
Package 'Payroll' is incompatible with current application version and cannot be activated. Check Install/Activate page for newer package version.
With 2.3.25 it installed successfully but had problem in accessing pages.
598 02/03/2017 04:32:27 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
Oh yes I installed with 2.3.25.
I think 2.4 doesn't have stable release till now. Shouldn't you have it for 2.3.25 as well?
Regards
599 02/01/2017 05:57:28 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
I installed this extension. It is giving following errors on all pages
Undefined property: sys_prefs::$use_popup_windows in file: D:\xampp\htdocs\boxygen\modules\Payroll\emp_payslip.php at line 24
Call to undefined function user_use_date_picker() in D:\xampp\htdocs\boxygen\modules\Payroll\emp_payslip.php on line 26
600 01/29/2017 08:26:36 am
Re: FA WITH AUTOCOMPLETE DOWNLOAD (36 replies, posted in FA Modifications)
Any help expected @anoopmb?