Search options (Page 24 of 26)

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();
}

?>

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

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.

<?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();
}

?>

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

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?

In Post 16 bogeyman has given the link to download

http://stuff.w3shaman.com/others/fa-autocomplete.zip

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.

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

Any help expected @anoopmb?