Create New Inquiry Front Accounting

Base on the article Add Menu on “Items and Inventory” Module FrontAccounting, we want to create an inquiry for that menu. Firstly create query, open file “\inventory\includes\db\items_locations_db.inc”, then add below code.

 //Add by : Andik  
 function get_loc_details_all($description)  
 {  
  $sql = "SELECT stock.loc_code, stock.location_name,  
  reorders.stock_id, reorders.reorder_level, item.description  
  FROM ".TB_PREF."locations stock"  
  ." INNER JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=stock.loc_code"  
  ." INNER JOIN ".TB_PREF."stock_master item ON item.stock_id=reorders.stock_id"  
  ." WHERE stock.fixed_asset = 0 and item.mb_flag <> 'D'"  
  ." and item.description like " .db_escape("%" . $description . "%")  
  ." ORDER BY reorders.stock_id, reorders.loc_code";  
  return db_query($sql,"an item reorder could not be retreived");  
 }  
 //end  

After this, create a new file on directory “inventory\inquiry” and name it “stock_onhand.php” and then add code below.

 <?php $page_security = 'SA_ITEMSSTATVIEW'; $path_to_root = "../.."; include_once($path_to_root . "/includes/session.inc"); $js = ""; if ($SysPrefs->use_popup_windows && $SysPrefs->use_popup_search)  
  $js .= get_js_open_window(900, 500);  
 page(_($help_context = "Inventory Item On-Hand"), "", false, "", $js);  
 include_once($path_to_root . "/includes/date_functions.inc");  
 include_once($path_to_root . "/includes/ui.inc");  
 include_once($path_to_root . "/includes/data_checks.inc");  
 include_once($path_to_root . "/inventory/includes/inventory_db.inc");  
 include_once($path_to_root . "/includes/db/manufacturing_db.inc");  
 check_db_has_stock_items(_("There are no items defined in the system."));  
 $mode = get_company_pref('no_item_list');  
 if ($mode != 0)  
  $js = get_js_set_combo_item();  
 else  
  $js = get_js_select_combo_item();  
 if(get_post("search")) {  
  $Ajax->activate("item_tbl");  
 }  
 start_form(false, false, $_SERVER['PHP_SELF'] . "?" . $_SERVER['QUERY_STRING']);  
 start_table(TABLESTYLE_NOBORDER);  
 start_row();  
 text_cells(_("Description"), "description");  
 check_cells(_('Also no qty :'), 'also_noqty', check_value('also_noqty'));  
 submit_cells("search", _("Search"), "", _("Search items"), "default");  
 end_row();  
 end_table();  
 end_form();  
 div_start("item_tbl");  
 start_form();  
 $loc_details = get_loc_details_all(get_post("description"));  
 start_table(TABLESTYLE);  
 $th = array(_("Stock ID"),_("Description"),_("Location"),  
  _("Quantity On Hand"), _("Re-Order Level"),  
  _("Demand"), _("Available"), _("On Order"));  
 table_header($th);  
 while ($myrow = db_fetch($loc_details))  
 {  
 alt_table_row_color($k);  
 $dec = get_qty_dec($myrow['stock_id']);  
 $demand_qty = get_demand_qty($myrow['stock_id'], $myrow["loc_code"]);  
  $demand_qty += get_demand_asm_qty($myrow['stock_id'], $myrow["loc_code"]);  
 $qoh = get_qoh_on_date($myrow['stock_id'], $myrow["loc_code"]);  
 $qoo = get_on_porder_qty($myrow['stock_id'], $myrow["loc_code"]);  
  $qoo += get_on_worder_qty($myrow['stock_id'], $myrow["loc_code"]);  
 if(get_post('also_noqty') == true)  
  {  
 label_cell($myrow["stock_id"]);  
  label_cell($myrow["description"]);  
  label_cell($myrow["location_name"]);  
  qty_cell($qoh, false, $dec);  
  qty_cell($myrow["reorder_level"], false, $dec);  
  qty_cell($demand_qty, false, $dec);  
  qty_cell($qoh - $demand_qty, false, $dec);  
  qty_cell($qoo, false, $dec);  
  end_row();  
 }  
  else  
  {  
  if ($qoh != 0 || $demand_qty != 0 || $qoo != 0)  
  {  
 label_cell($myrow["stock_id"]);  
  label_cell($myrow["description"]);  
  label_cell($myrow["location_name"]);  
  qty_cell($qoh, false, $dec);  
  qty_cell($myrow["reorder_level"], false, $dec);  
  qty_cell($demand_qty, false, $dec);  
  qty_cell($qoh - $demand_qty, false, $dec);  
  qty_cell($qoo, false, $dec);  
  end_row();  
 }  
  }  
 }  
 end_table();  
 end_form();  
 div_end();  
 end_page();  

It’s done, and this is the result.