inventory/inquiry/stock_movements.php

   1 <?php
   2
   3
   4 $page_security = 2;
   5 $path_to_root="../..";
   6 include($path_to_root . "/includes/db_pager.inc");
   7 include_once($path_to_root . "/includes/session.inc");
   8
   9 include_once($path_to_root . "/includes/date_functions.inc");
  10 include_once($path_to_root . "/includes/banking.inc");
  11 include_once($path_to_root . "/sales/includes/sales_db.inc");
  12
  13 include_once($path_to_root . "/includes/ui.inc");
  14 $js = "";
  15 if ($use_popup_windows)
  16         $js .= get_js_open_window(800, 500);
  17 if ($use_date_picker)
  18         $js .= get_js_date_picker();
  19
  20 page(_("Inventory Item Movement"), false, false, "", $js);
  21 //------------------------------------------------------------------------------------------------
  22
  23 check_db_has_stock_items(_("There are no items defined in the system."));
  24
  25 if (isset($_GET['stock_id']))
  26 {
  27         $_POST['stock_id'] = $_GET['stock_id'];
  28 }
  29
  30 start_form(false, true);
  31
  32 if (!isset($_POST['stock_id']))
  33         $_POST['stock_id'] = get_global_stock_item();
  34
  35 start_table("class='tablestyle_noborder'");
  36
  37 stock_items_list_cells(_("Item:"), 'stock_id', $_POST['stock_id']);
  38
  39 locations_list_cells(_("From Location:"), 'StockLocation', null);
  40
  41 date_cells(_("From:"), 'AfterDate', '', null, -30);
  42 date_cells(_("To:"), 'BeforeDate');
  43
  44 submit_cells('ShowMoves',_("Show Movements"),'',_('Refresh Inquiry'), true);
  45 end_table();
  46 end_form();
  47
  48 set_global_stock_item($_POST['stock_id']);
  49 $item_dec = get_qty_dec($_POST['stock_id']);
  50 //
  51 //      Get summary displayed in headewr and footer.
  52 //
  53 function get_summary(&$table)
  54 {
  55         global $Ajax, $item_dec;
  56
  57         $sql = "SELECT
  58                 Sum(qty) as sum,
  59                 Sum(IF(qty>0, qty, 0)) as in_qty,
  60                 Sum(IF(qty<0, -qty, 0)) as out_qty
  61         FROM ".TB_PREF."stock_moves
  62         WHERE loc_code='" . $_POST['StockLocation'] . "'
  63         AND tran_date >= '". date2sql($_POST['AfterDate']) . "'
  64         AND tran_date <= '" . date2sql($_POST['BeforeDate']) . "'
  65         AND stock_id = '" . $_POST['stock_id'] . "'";
  66         " GROUP BY rec";
  67         $result = db_query($sql, "cannot retrieve stock moves");
  68
  69         $qty = db_fetch($result);
  70
  71         $sum['beg'] = get_qoh_on_date($_POST['stock_id'], $_POST['StockLocation'],
  72                 add_days($_POST['AfterDate'], -1));
  73         $sum['in'] = $qty['in_qty'];
  74         $sum['out'] = $qty['out_qty'];
  75         $sum['end'] = $sum['beg'] + $qty['sum'];
  76         $sum['dec'] = $item_dec = get_qty_dec($_POST['stock_id']);
  77
  78         $table->sum = $sum;
  79  }
  80 //-----------------------------------------------------------------------------
  81
  82 function systype_name($row)
  83 {
  84         return systypes::name($row["type"]);
  85 }
  86
  87 function trans_view($row)
  88 {
  89         return  get_trans_view_str($row["type"], $row["trans_no"]);
  90 }
  91
  92 function show_details($row)
  93 {
  94         $person = $row["person_id"];
  95         $gl_posting = "";
  96
  97         if (($row["type"] == 13) || ($row["type"] == 11))
  98         {
  99                 $cust_row = get_customer_details_from_trans($row["type"], $row["trans_no"]);
 100
 101                 if (strlen($cust_row['name']) > 0)
 102                         $person = $cust_row['name'] . " (" . $cust_row['br_name'] . ")";
 103
 104         }
 105         elseif ($row["type"] == 25 || $row['type'] == 21)
 106         {
 107                 // get the supplier name
 108                 $sql = "SELECT supp_name FROM ".TB_PREF."suppliers WHERE supplier_id = '" . $row["person_id"] . "'";
 109                 $supp_result = db_query($sql,"check failed");
 110
 111                 $supp_row = db_fetch($supp_result);
 112
 113                 if (strlen($supp_row['supp_name']) > 0)
 114                         $person = $supp_row['supp_name'];
 115         }
 116         elseif ($row["type"] == systypes::location_transfer() || $row["type"] == systypes::inventory_adjustment())
 117         {
 118                 // get the adjustment type
 119                 $movement_type = get_movement_type($row["person_id"]);
 120                 $person = $movement_type["name"];
 121         }
 122         elseif ($row["type"]==systypes::work_order() || $row["type"] == 28  ||
 123                 $row["type"] == 29)
 124         {
 125                 $person = "";
 126         }
 127         return $person;
 128 }
 129
 130 $total_out = 0;
 131 $total_in = 0;
 132
 133 function qty_in($row)
 134 {
 135         $q = $row["qty"];
 136         return $q <= 0 ? '' : $q;
 137 }
 138
 139 function qty_out($row)
 140 {
 141         $q = -$row["qty"];
 142         return $q <= 0 ? '' : $q;
 143 }
 144 /*
 145 function show_qoh($row)
 146 {
 147         $qoh =& $_SESSION['qoh'];
 148         $qoh += $row['qty'];
 149         return $qoh;
 150 }
 151 */
 152 function before_status($pager)
 153 {
 154         $r[] =
 155                 array( "<b>"._("Quantity on hand before") . " " . $_POST['AfterDate']
 156                 .':'."</b>", "align='right' colspan=5");
 157         if($pager->sum['beg']>=0) {
 158                 $r[] = array (number_format2($pager->sum['beg'], $pager->sum['dec']),
 159                         "align='right'");
 160                 $r[] = array("&nbsp;");
 161         } else {
 162                 $r[] = array("&nbsp;");
 163                 $r[] = array (number_format2($pager->sum['beg'], $pager->sum['dec']),
 164                         "align='right'");
 165         }
 166         return $r;
 167 }
 168
 169 function after_status($pager)
 170 {
 171         $r[] =
 172                 array( "<b>"._("Quantity on hand after") . " " . $_POST['BeforeDate']
 173                 .':'."</b>", "align='right' colspan=5");
 174         if($pager->sum['end']>=0) {
 175                 $r[] = array (number_format2($pager->sum['end'], $pager->sum['dec']),
 176                         "align='right'");
 177                 $r[] = array("&nbsp;");
 178         } else {
 179                 $r[] = array("&nbsp;");
 180                 $r[] = array (number_format2($pager->sum['end'], $pager->sum['dec']),
 181                         "align='right'");
 182         }
 183         return $r;
 184 }
 185 //-----------------------------------------------------------------------------
 186
 187 $before_date = date2sql($_POST['BeforeDate']);
 188 $after_date = date2sql($_POST['AfterDate']);
 189
 190 $sql = "SELECT
 191         type,
 192         trans_no,
 193         reference,
 194         tran_date,
 195         person_id,
 196         qty
 197         FROM ".TB_PREF."stock_moves
 198         WHERE loc_code='" . $_POST['StockLocation'] . "'
 199         AND tran_date >= '". $after_date . "'
 200         AND tran_date <= '" . $before_date . "'
 201         AND stock_id = '" . $_POST['stock_id'] . "'";
 202
 203 $cols = array(
 204         _("Type") => array('fun'=>'systype_name' ),
 205         _("#") => array('fun'=>'trans_view' ),
 206         _("Reference"),
 207         _("Date") => array('date', 'ord'=>'desc'),
 208         _("Detail") => array('fun'=>'show_details' ),
 209         _("Quantity In") => array('type'=>'qty', 'dec'=> $item_dec, 'insert'=>true,'fun'=>'qty_in' ),
 210         _("Quantity Out") => array('type'=>'qty', 'dec'=> $item_dec,'insert'=>true,'fun'=>'qty_out' ),
 211 //      _("Quantity On Hand") => array('insert'=>true,'type'=>'amount', 'fun'=>'show_qoh' )
 212 );
 213
 214 $table =& new_db_pager('doc_tbl', $sql, $cols);
 215 $table->set_header('before_status');
 216 $table->set_footer('after_status');
 217
 218 if (!$table->ready)  // new sql query - update summary
 219         get_summary($table);
 220
 221 if (get_post('ShowMoves')) {
 222         $table->set_sql($sql);
 223         $table->set_columns($cols);
 224 }
 225 start_form();
 226
 227 display_db_pager($table);
 228
 229 end_form();
 230 end_page();
 231 ?>