From f3166c65874df53c9e76e53b946e9527d6a26754 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Thu, 24 Jan 2013 11:11:59 +0100 Subject: [PATCH] New report, Inventory Movements, added to FrontAccounting core. --- reporting/rep307.php | 174 +++++++++++++++++++++++++++++++++++++ reporting/reports_main.php | 8 ++ 2 files changed, 182 insertions(+) create mode 100644 reporting/rep307.php diff --git a/reporting/rep307.php b/reporting/rep307.php new file mode 100644 index 0000000..9ac9c82 --- /dev/null +++ b/reporting/rep307.php @@ -0,0 +1,174 @@ +. +***********************************************************************/ +$page_security = 'SA_ITEMSVALREP'; +// ---------------------------------------------------------------- +// $ Revision: 2.0 $ +// Creator: Jujuk +// 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 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'"; + + 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 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 == ALL_TEXT) +// $location = ''; + if ($location == '') + $loc = _('All'); + else + $loc = get_location_name($location); + + //$cols = array(0, 100, 300, 365, 440, 540, 640, 715); + $cols = array(0, 60, 220, 240, 310, 380, 450, 520); + + $headers = array(_('Category'), _('Description'), _('UOM'), _('Opening'), _('Quantity In'), _('Quantity Out'), _('Balance')); + + $aligns = array('left', 'left', 'left', '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(_('Inventory Movements'), "InventoryMovements", user_pagesize(), 9, $orientation); + if ($orientation == 'L') + recalculate_cols($cols); + + $rep->Font(); + $rep->Info($params, $cols, $headers, $aligns); + $rep->NewPage(); + + $result = fetch_items($category); + + $catgor = ''; + while ($myrow=db_fetch($result)) + { + if ($catgor != $myrow['description']) + { + $rep->Line($rep->row - $rep->lineHeight); + $rep->NewLine(2); + $rep->fontSize += 2; + $rep->TextCol(0, 3, $myrow['category_id'] . " - " . $myrow['description']); + $catgor = $myrow['description']; + $rep->fontSize -= 2; + $rep->NewLine(); + } + $rep->NewLine(); + $rep->TextCol(0, 1, $myrow['stock_id']); + $rep->TextCol(1, 2, $myrow['name']); + $rep->TextCol(2, 3, $myrow['units']); + $qoh_start= $inward = $outward = $qoh_end = 0; + + $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); + + $rep->AmountCol(3, 4, $qoh_start, get_qty_dec($myrow['stock_id'])); + $rep->AmountCol(4, 5, $inward, get_qty_dec($myrow['stock_id'])); + $rep->AmountCol(5, 6, $outward, get_qty_dec($myrow['stock_id'])); + $rep->AmountCol(6, 7, $qoh_end, get_qty_dec($myrow['stock_id'])); + + $rep->NewLine(0, 1); + } + $rep->Line($rep->row - 4); + + $rep->NewLine(); + $rep->End(); +} + +?> \ No newline at end of file diff --git a/reporting/reports_main.php b/reporting/reports_main.php index 1acc8af..53925ca 100644 --- a/reporting/reports_main.php +++ b/reporting/reports_main.php @@ -218,6 +218,14 @@ $reports->addReport(RC_INVENTORY, 303, _('Stock &Check Sheets'), _('Comments') => 'TEXTBOX', _('Orientation') => 'ORIENTATION', _('Destination') => 'DESTINATION')); +$reports->addReport(RC_INVENTORY, 307, _('Inventory &Movement Report'), + array( _('Start Date') => 'DATEBEGINM', + _('End Date') => 'DATEENDM', + _('Inventory Category') => 'CATEGORIES', + _('Location') => 'LOCATIONS', + _('Comments') => 'TEXTBOX', + _('Orientation') => 'ORIENTATION', + _('Destination') => 'DESTINATION')); $reports->addReport(RC_INVENTORY, 304, _('Inventory &Sales Report'), array( _('Start Date') => 'DATEBEGINM', _('End Date') => 'DATEENDM', -- 2.30.2