! -> Note
$ -> Affected files
+03-Dec-2009 Joe Hunt
+! Moved all SQL statements from PHP files into relevant *_db.inc files.
+ Included inventory folder
+$ /admin/payment_terms.php
+ /admin/printers.php
+ /admin/print_profiles.php
+ /admin/shipping_companies.php
+ /admin/db/company_db.inc
+ /admin/db/printers_db.inc
+ /admin/db/shipping_db.inc
+ /dimensions/includes/dimens_db.inc
+ /gl/gl_bank.php
+ /gl/includes/db/gl_db_accounts.inc
+ /gl/includes/db/gl_db_account_types.inc
+ /gl/includes/db/gl_db_bank_accounts.inc
+ /gl/includes/db/gl_db_curriencies.inc
+ /gl/manage/bank_accounts.php
+ /gl/manage/currencies.php
+ /gl/manage/gl_accounts.php
+ /gl/manage/gl_account_classes.php
+ /gl/manage/gl_account_types.php
+ /inventory/purchasing_data.php
+ /inventory/includes/inventory_db.inc
+ /inventory/includes/db/items_category_db.inc
+ /inventory/includes/db/items_db.inc
+ /inventory/includes/db/items_locations_db.inc
+ /inventory/includes/db/items_purchases_db.inc (New file)
+ /inventory/includes/db/movement_types_db.inc
+ /inventory/inquiry/stock_movements.php
+ /inventory/manage/items_php
+ /inventory/manage/item_categories.php
+ /inventory/manage/locations.php
+ /inventory/manage/movement_types.php
+
02-Dec-2009 Janusz Dobrowolski
! Merged changed from trunk up to 2.2.1 ---------------------------------------
return db_query($sql,"could not get payment terms");
}
-function customer_has_terms($selected_id)
+function key_in_foreign_table($id, $table, $key, $escaped=false)
{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE payment_terms = ".db_escape($selected_id);
- $result = db_query($sql,"check failed");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-function supplier_has_terms($selected_id)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE payment_terms = ".db_escape($selected_id);
- $result = db_query($sql,"check failed");
+ if (!$escaped)
+ $id = db_escape($id);
+ $sql= "SELECT COUNT(*) FROM ".TB_PREF."$table WHERE $key = $id";
+ $result = db_query($sql,"check $table relations failed");
$myrow = db_fetch_row($result);
return ($myrow[0] > 0);
}
return db_query($sql,"could not get printing profile");
}
-function printer_has_profile($id)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."print_profiles WHERE printer = ".db_escape($id);
- $result = db_query($sql,"check printers relations failed");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
?>
\ No newline at end of file
return db_fetch($result);
}
-//--------------------------------------------------------------------------------------
-
-function sales_orders_has_shipper($selected_id)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE ship_via=".db_escape($selected_id);
-
- $result = db_query($sql,"check failed");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-//--------------------------------------------------------------------------------------
-
-function debtor_trans_has_shipper($selected_id)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE ship_via=".db_escape($selected_id);
-
- $result = db_query($sql,"check failed");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
?>
\ No newline at end of file
if ($Mode == 'Delete')
{
// PREVENT DELETES IF DEPENDENT RECORDS IN debtors_master
- if (customer_has_terms($selected_id))
+ if (key_in_foreign_table($selected_id, 'debtors_master', 'payment_terms'))
{
display_error(_("Cannot delete this payment term, because customer accounts have been created referring to this term."));
}
else
{
- if (supplier_has_terms($selected_id))
+ if (key_in_foreign_table($selected_id, 'suppliers', 'payment_terms'))
{
display_error(_("Cannot delete this payment term, because supplier accounts have been created referring to this term"));
}
{
// check if selected profile is used by any user
if ($name=='') return 0; // cannot delete system default profile
- $sql = "SELECT * FROM ".TB_PREF."users WHERE print_profile=".db_escape($name);
- $res = db_query($sql,'cannot check printing profile usage');
- return db_num_rows($res);
+ return key_in_foreign_table($name, 'users', 'print_profile');
}
//-------------------------------------------------------------------------------------------
if ( get_post('submit'))
{
// PREVENT DELETES IF DEPENDENT RECORDS IN print_profiles
- if (printer_has_profile($selected_id))
+ if (key_in_foreign_table($selected_id, 'print_profiles', 'printer'))
{
display_error(_("Cannot delete this printer definition, because print profile have been created using it."));
}
{
// PREVENT DELETES IF DEPENDENT RECORDS IN 'sales_orders'
- if (sales_orders_has_shipper($selected_id))
+ if (key_in_foreign_table($selected_id, 'sales_orders', 'ship_via'))
{
$cancel_delete = 1;
display_error(_("Cannot delete this shipping company because sales orders have been created using this shipper."));
else
{
// PREVENT DELETES IF DEPENDENT RECORDS IN 'debtor_trans'
-
- if (debtor_trans_has_shipper($selected_id))
+ if (key_in_foreign_table($selected_id, 'debtor_trans', 'ship_via'))
{
$cancel_delete = 1;
display_error(_("Cannot delete this shipping company because invoices have been created using this shipping company."));
function dimension_has_payments($id)
{
- $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE dimension_id = ".db_escape($id);
+ $sql = "SELECT COUNT(*) FROM ".TB_PREF."gl_trans WHERE dimension_id = ".db_escape($id)
+ . " OR dimension2_id = ".db_escape($id);
$res = db_query($sql, "Transactions could not be calculated");
$row = db_fetch_row($res);
- return ($row[0] != 0.0);
+ return ($row[0] > 0);
}
function dimension_is_closed($id)
db_query($sql, "could not delete account type");
}
-function account_class_in_account_types($id)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."chart_types
- WHERE class_id=".db_escape($id);
- $result = db_query($sql, "could not query chart master");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-function account_type_in_chart_master($type)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."chart_master
- WHERE account_type=$type";
- $result = db_query($sql, "could not query chart master");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-function account_type_in_parent($type)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."chart_types
- WHERE parent=$type";
- $result = db_query($sql, "could not query chart types");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
?>
\ No newline at end of file
display_db_error("could not retreive the account name for $code", $sql, true);
}
-function gl_account_in_transactions($acc)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."gl_trans WHERE account=$acc";
- $result = db_query($sql,"Couldn't test for existing transactions");
-
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
function gl_account_in_company_defaults($acc)
{
$sql= "SELECT COUNT(*) FROM ".TB_PREF."company WHERE debtors_act=$acc
return ($myrow[0] > 0);
}
-function gl_account_in_bank_accounts($acc)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."bank_accounts WHERE account_code=$acc";
- $result = db_query($sql,"Couldn't test for bank accounts");
-
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
function gl_account_in_stock_category($acc)
{
$sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_category WHERE
//---------------------------------------------------------------------------------------------
-function bank_account_in_transactions($acc)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."bank_trans WHERE bank_act=$acc";
- $result = db_query($sql,"check failed");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-//---------------------------------------------------------------------------------------------
-
-function bank_account_in_sales_pos($acc)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_pos WHERE pos_account=$acc";
- $result = db_query($sql,"check failed");
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-//---------------------------------------------------------------------------------------------
-
function add_quick_entry($description, $type, $base_amount, $base_desc)
{
$sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc)
return db_query($sql, "could not get currencies");
}
-//---------------------------------------------------------------------------------------------
-
-function currency_in_debtors($curr)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE curr_code = $curr";
- $result = db_query($sql);
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-function currency_in_suppliers($curr)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE curr_code = $curr";
- $result = db_query($sql);
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-function currency_in_bank_accounts($curr)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."bank_accounts WHERE bank_curr_code = $curr";
- $result = db_query($sql);
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
-
-function currency_in_company($curr)
-{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."company WHERE curr_default = $curr";
- $result = db_query($sql);
- $myrow = db_fetch_row($result);
- return ($myrow[0] > 0);
-}
?>
\ No newline at end of file
$acc = db_escape($selected_id);
// PREVENT DELETES IF DEPENDENT RECORDS IN 'bank_trans'
- if (bank_account_in_transactions($acc))
+ if (key_in_foreign_table($acc, 'bank_trans', 'bank_act', true))
{
$cancel_delete = 1;
display_error(_("Cannot delete this bank account because transactions have been created using this account."));
}
- if (bank_account_in_sales_pos($acc))
+ if (key_in_foreign_table($acc, 'sales_pos', 'pos_account', true))
{
$cancel_delete = 1;
display_error(_("Cannot delete this bank account because POS definitions have been created using this account."));
$curr = db_escape($selected_id);
// PREVENT DELETES IF DEPENDENT RECORDS IN debtors_master
- if (currency_in_debtors($curr))
+ if (key_in_foreign_table($curr, 'debtors_master', 'curr_code', true))
{
display_error(_("Cannot delete this currency, because customer accounts have been created referring to this currency."));
return false;
}
- if (currency_in_suppliers($curr))
+ if (key_in_foreign_table($curr, 'suppliers', 'curr_code', true))
{
display_error(_("Cannot delete this currency, because supplier accounts have been created referring to this currency."));
return false;
}
-
- if (currency_in_company($curr))
+
+ if (key_in_foreign_table($curr, 'company', 'curr_default', true))
{
display_error(_("Cannot delete this currency, because the company preferences uses this currency."));
return false;
}
// see if there are any bank accounts that use this currency
- if (currenty_in_bank_accounts($curr))
+ if (key_in_foreign_table($curr, 'bank_accounts', 'bank_curr_code', true))
{
display_error(_("Cannot delete this currency, because thre are bank accounts that use this currency."));
return false;
{
if ($selected_id == -1)
return false;
- if (account_class_in_account_types($selected_id))
+ if (key_in_foreign_table($selected_id, 'chart_types', 'class_id'))
{
display_error(_("Cannot delete this account class because GL account types have been created referring to it."));
return false;
return false;
$type = db_escape($selected_id);
- if (account_type_in_chart_master($type))
+ if (key_in_foreign_table($type, 'chart_master', 'account_type', true))
{
display_error(_("Cannot delete this account group because GL accounts have been created referring to it."));
return false;
}
- if (account_type_in_parent($type))
+ if (key_in_foreign_table($type, 'chart_types', 'parent', true))
{
display_error(_("Cannot delete this account group because GL account groups have been created referring to it."));
return false;
return false;
$acc = db_escape($selected_account);
- if (gl_account_in_transactions($acc))
+ if (key_in_foreign_table($acc, 'gl_trans', 'account', true))
{
display_error(_("Cannot delete this account because transactions have been created using this account."));
return false;
display_error(_("Cannot delete this account because it is used as one of the company default GL accounts."));
return false;
}
-
- if (gl_account_in_bank_accounts($acc))
+
+ if (key_in_foreign_table($acc, 'bank_accounts', 'account_code', true))
{
display_error(_("Cannot delete this account because it is used by a bank account."));
return false;
db_query($sql,"an item category could not be deleted");
}
+function get_item_categories($show_inactive)
+{
+ $sql = "SELECT c.*, t.name as tax_name FROM ".TB_PREF."stock_category c, "
+ .TB_PREF."item_tax_types t WHERE c.dflt_tax_type=t.id";
+ if (!$show_inactive) $sql .= " AND !c.inactive";
+
+ return db_query($sql, "could not get stock categories");
+}
+
function get_item_category($id)
{
$sql="SELECT * FROM ".TB_PREF."stock_category WHERE category_id=".db_escape($id);
return db_query($sql,"items could not be retreived");
}
+function item_in_foreign_codes($stock_id)
+{
+ $sqls= array(
+ "SELECT COUNT(*) FROM "
+ .TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id) =>
+ _('Cannot delete this item because there are stock movements that refer to this item.'),
+ "SELECT COUNT(*) FROM "
+ .TB_PREF."bom WHERE component=".db_escape($stock_id)=>
+ _('Cannot delete this item record because there are bills of material that require this part as a component.'),
+ "SELECT COUNT(*) FROM "
+ .TB_PREF."sales_order_details WHERE stk_code=".db_escape($stock_id) =>
+ _('Cannot delete this item because there are existing purchase order items for it.'),
+ "SELECT COUNT(*) FROM "
+ .TB_PREF."purch_order_details WHERE item_code=".db_escape($stock_id)=>
+ _('Cannot delete this item because there are existing purchase order items for it.')
+ );
+
+ $msg = '';
+
+ foreach($sqls as $sql=>$err) {
+ $result = db_query($sql, "could not query stock usage");
+ $myrow = db_fetch_row($result);
+ if ($myrow[0] > 0)
+ {
+ $msg = $err; break;
+ }
+ }
+ if ($msg == '') {
+
+ $kits = get_where_used($stock_id);
+ $num_kits = db_num_rows($kits);
+ if ($num_kits) {
+ $msg = _("This item cannot be deleted because some code aliases
+ or foreign codes was entered for it, or there are kits defined
+ using this item as component")
+ .':<br>';
+
+ while($num_kits--) {
+ $kit = db_fetch($kits);
+ $msg .= "'".$kit[0]."'";
+ if ($num_kits) $msg .= ',';
+ }
+
+ }
+ }
+ return $msg;
+}
?>
\ No newline at end of file
//------------------------------------------------------------------------------------
+function get_item_locations($show_inactive)
+{
+ $sql = "SELECT * FROM ".TB_PREF."locations";
+ if (!$show_inactive) $sql .= " WHERE !inactive";
+ return db_query($sql, "could not query locations");;
+}
+
+//------------------------------------------------------------------------------------
+
function set_reorder_level($stock_id, $loc_code, $reorder_level)
{
$sql = "UPDATE ".TB_PREF."loc_stock SET reorder_level = $reorder_level
return db_query($sql,"an item reorder could not be retreived");
}
-//------------------------------------------------------------------------------------
-
?>
\ No newline at end of file
db_query($sql, "could not delete item movement type");
}
+function get_stock_movements($stock_id, $StockLocation, $BeforeDate, $AfterDate)
+{
+ $before_date = date2sql($BeforeDate);
+ $after_date = date2sql($AfterDate);
+ $sql = "SELECT type, trans_no, tran_date, person_id, qty, reference
+ FROM ".TB_PREF."stock_moves
+ WHERE loc_code=".db_escape($StockLocation)."
+ AND tran_date >= '". $after_date . "'
+ AND tran_date <= '" . $before_date . "'
+ AND stock_id = ".db_escape($stock_id) . " ORDER BY tran_date,trans_id";
+ return db_query($sql, "could not query stock moves");
+}
+
+function get_stock_movements_before($stock_id, $StockLocation, $AfterDate)
+{
+ $after_date = date2sql($AfterDate);
+ $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id) . "
+ AND loc_code=".db_escape( $StockLocation) . "
+ AND tran_date < '" . $after_date . "'";
+ $before_qty = db_query($sql, "The starting quantity on hand could not be calculated");
+
+ $before_qty_row = db_fetch_row($before_qty);
+ return $before_qty_row[0];
+}
+
+function movement_types_in_stock_moves($selected_id)
+{
+ $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves
+ WHERE type=" . ST_INVADJUST. " AND person_id=".db_escape($selected_id);
+
+ $result = db_query($sql, "could not query stock moves");
+ $myrow = db_fetch_row($result);
+ return ($myrow[0] > 0);
+}
?>
\ No newline at end of file
include_once($path_to_root . "/inventory/includes/db/items_category_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_trans_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_prices_db.inc");
+include_once($path_to_root . "/inventory/includes/db/items_purchases_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_codes_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_locations_db.inc");
$before_date = date2sql($_POST['BeforeDate']);
$after_date = date2sql($_POST['AfterDate']);
-$sql = "SELECT type, trans_no, tran_date, person_id, qty, reference
- FROM ".TB_PREF."stock_moves
- WHERE loc_code=".db_escape($_POST['StockLocation'])."
- AND tran_date >= '". $after_date . "'
- AND tran_date <= '" . $before_date . "'
- AND stock_id = ".db_escape($_POST['stock_id']) . " ORDER BY tran_date,trans_id";
-$result = db_query($sql, "could not query stock moves");
-
-check_db_error("The stock movements for the selected criteria could not be retrieved",$sql);
+$result = get_stock_movements($_POST['stock_id'], $_POST['StockLocation'],
+ $_POST['BeforeDate'], $_POST['AfterDate']);
div_start('doc_tbl');
start_table($table_style);
table_header($th);
-$sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($_POST['stock_id']) . "
- AND loc_code=".db_escape( $_POST['StockLocation']) . "
- AND tran_date < '" . $after_date . "'";
-$before_qty = db_query($sql, "The starting quantity on hand could not be calculated");
-
-$before_qty_row = db_fetch_row($before_qty);
-$after_qty = $before_qty = $before_qty_row[0];
+$before_qty = get_stock_movements_before($_POST['stock_id'], $_POST['StockLocation'], $_POST['AfterDate']);
+
+$after_qty = $before_qty;
+/*
if (!isset($before_qty_row[0]))
{
$after_qty = $before_qty = 0;
}
-
+*/
start_row("class='inquirybg'");
label_cell("<b>"._("Quantity on hand before") . " " . $_POST['AfterDate']."</b>", "align=center colspan=5");
label_cell(" ", "colspan=2");
elseif ($myrow["type"] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT)
{
// get the supplier name
- $sql = "SELECT supp_name FROM ".TB_PREF."suppliers WHERE supplier_id = '" . $myrow["person_id"] . "'";
- $supp_result = db_query($sql,"check failed");
-
- $supp_row = db_fetch($supp_result);
+ $supp_name = get_supplier_name($myrow["person_id"]);
- if (strlen($supp_row['supp_name']) > 0)
- $person = $supp_row['supp_name'];
+ if (strlen($supp_name) > 0)
+ $person = $supp_name;
}
elseif ($myrow["type"] == ST_LOCTRANSFER || $myrow["type"] == ST_INVADJUST)
{
{
// PREVENT DELETES IF DEPENDENT RECORDS IN 'stock_master'
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE category_id=".db_escape($selected_id);
- $result = db_query($sql, "could not query stock master");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'stock_master', 'category_id'))
{
display_error(_("Cannot delete this item category because items have been created using this item category."));
}
}
//----------------------------------------------------------------------------------
-$sql = "SELECT c.*, t.name as tax_name FROM ".TB_PREF."stock_category c, "
- .TB_PREF."item_tax_types t WHERE c.dflt_tax_type=t.id";
-if (!check_value('show_inactive')) $sql .= " AND !c.inactive";
-
-$result = db_query($sql, "could not get stock categories");
+$result = get_item_categories(check_value('show_inactive'));
start_form();
start_table("$table_style width=80%");
function check_usage($stock_id, $dispmsg=true)
{
- $sqls= array(
- "SELECT COUNT(*) FROM "
- .TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id) =>
- _('Cannot delete this item because there are stock movements that refer to this item.'),
- "SELECT COUNT(*) FROM "
- .TB_PREF."bom WHERE component=".db_escape($stock_id)=>
- _('Cannot delete this item record because there are bills of material that require this part as a component.'),
- "SELECT COUNT(*) FROM "
- .TB_PREF."sales_order_details WHERE stk_code=".db_escape($stock_id) =>
- _('Cannot delete this item because there are existing purchase order items for it.'),
- "SELECT COUNT(*) FROM "
- .TB_PREF."purch_order_details WHERE item_code=".db_escape($stock_id)=>
- _('Cannot delete this item because there are existing purchase order items for it.')
- );
-
- $msg = '';
-
- foreach($sqls as $sql=>$err) {
- $result = db_query($sql, "could not query stock usage");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
- {
- $msg = $err; break;
- }
- }
-
- if ($msg == '') {
+ $msg = item_in_foreign_codes($stock_id);
- $kits = get_where_used($stock_id);
- $num_kits = db_num_rows($kits);
- if ($num_kits) {
- $msg = _("This item cannot be deleted because some code aliases
- or foreign codes was entered for it, or there are kits defined
- using this item as component")
- .':<br>';
-
- while($num_kits--) {
- $kit = db_fetch($kits);
- $msg .= "'".$kit[0]."'";
- if ($num_kits) $msg .= ',';
- }
-
- }
- }
if ($msg != '') {
if($dispmsg) display_error($msg);
return false;
function can_delete($selected_id)
{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves WHERE loc_code=".db_escape($selected_id);
- $result = db_query($sql, "could not query stock moves");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'stock_moves', 'loc_code'))
{
display_error(_("Cannot delete this location because item movements have been created using this location."));
return false;
}
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."workorders WHERE loc_code=".db_escape($selected_id);
- $result = db_query($sql, "could not query work orders");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'workorders', 'loc_code'))
{
display_error(_("Cannot delete this location because it is used by some work orders records."));
return false;
}
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE default_location='$selected_id'";
- $result = db_query($sql, "could not query customer branches");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'cust_branch', 'default_location'))
{
display_error(_("Cannot delete this location because it is used by some branch records as the default location to deliver from."));
return false;
}
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."bom WHERE loc_code=".db_escape($selected_id);
- $result = db_query($sql, "could not query bom");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'bom', 'loc_code'))
{
display_error(_("Cannot delete this location because it is used by some related records in other tables."));
return false;
}
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."grn_batch WHERE loc_code=".db_escape($selected_id);
- $result = db_query($sql, "could not query grn batch");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+
+ if (key_in_foreign_table($selected_id, 'grn_batch', 'loc_code'))
{
display_error(_("Cannot delete this location because it is used by some related records in other tables."));
return false;
}
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."purch_orders WHERE into_stock_location=".db_escape($selected_id);
- $result = db_query($sql, "could not query purch orders");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'purch_orders', 'into_stock_location'))
{
display_error(_("Cannot delete this location because it is used by some related records in other tables."));
return false;
}
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE from_stk_loc=".db_escape($selected_id);
- $result = db_query($sql, "could not query sales orders");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'sales_orders', 'from_stk_loc'))
{
display_error(_("Cannot delete this location because it is used by some related records in other tables."));
return false;
}
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_pos WHERE pos_location=".db_escape($selected_id);
- $result = db_query($sql, "could not query sales pos");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (key_in_foreign_table($selected_id, 'sales_pos', 'pos_location'))
{
display_error(_("Cannot delete this location because it is used by some related records in other tables."));
return false;
$_POST['show_inactive'] = $sav;
}
-$sql = "SELECT * FROM ".TB_PREF."locations";
-if (!check_value('show_inactive')) $sql .= " WHERE !inactive";
-$result = db_query($sql, "could not query locations");;
+$result = get_item_locations(check_value('show_inactive'));
start_form();
start_table($table_style);
function can_delete($selected_id)
{
- $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves
- WHERE type=" . ST_INVADJUST. " AND person_id=".db_escape($selected_id);
-
- $result = db_query($sql, "could not query stock moves");
- $myrow = db_fetch_row($result);
- if ($myrow[0] > 0)
+ if (movement_types_in_stock_moves($selected_id))
{
display_error(_("Cannot delete this inventory movement type because item transactions have been created referring to it."));
return false;
{
if ($Mode == 'ADD_ITEM')
{
-
- $sql = "INSERT INTO ".TB_PREF."purch_data (supplier_id, stock_id, price, suppliers_uom,
- conversion_factor, supplier_description) VALUES (";
- $sql .= db_escape($_POST['supplier_id']).", ".db_escape($_POST['stock_id']). ", "
- .input_num('price',0) . ", ".db_escape( $_POST['suppliers_uom'] ). ", "
- .input_num('conversion_factor') . ", "
- .db_escape($_POST['supplier_description']) . ")";
-
- db_query($sql,"The supplier purchasing details could not be added");
+ add_item_purchasing_data($_POST['supplier_id'], $_POST['stock_id'], input_num('price',0),
+ $_POST['suppliers_uom'], input_num('conversion_factor'), $_POST['supplier_description']);
display_notification(_("This supplier purchasing data has been added."));
- } else
+ }
+ else
{
- $sql = "UPDATE ".TB_PREF."purch_data SET price=" . input_num('price',0) . ",
- suppliers_uom=".db_escape($_POST['suppliers_uom']) . ",
- conversion_factor=" . input_num('conversion_factor') . ",
- supplier_description=" . db_escape($_POST['supplier_description']) . "
- WHERE stock_id=".db_escape($_POST['stock_id']) . " AND
- supplier_id=".db_escape($selected_id);
- db_query($sql,"The supplier purchasing details could not be updated");
-
+ update_item_purchasing_data($selected_id, $_POST['stock_id'], input_num('price',0),
+ $_POST['suppliers_uom'], input_num('conversion_factor'), $_POST['supplier_description']);
display_notification(_("Supplier purchasing data has been updated."));
}
$Mode = 'RESET';
if ($Mode == 'Delete')
{
-
- $sql = "DELETE FROM ".TB_PREF."purch_data WHERE supplier_id=".db_escape($selected_id)."
- AND stock_id=".db_escape($_POST['stock_id']);
- db_query($sql,"could not delete purchasing data");
-
+ delete_item_purchasing_data($selected_id, $_POST['stock_id']);
display_notification(_("The purchasing data item has been sucessfully deleted."));
$Mode = 'RESET';
}
}
else
{
-
- $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name,"
- .TB_PREF."suppliers.curr_code
- FROM ".TB_PREF."purch_data INNER JOIN ".TB_PREF."suppliers
- ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id
- WHERE stock_id = ".db_escape($_POST['stock_id']);
-
- $result = db_query($sql, "The supplier purchasing details for the selected part could not be retrieved");
- div_start('price_table');
+ $result = get_items_purchasing_data($_POST['stock_id']);
+ div_start('price_table');
if (db_num_rows($result) == 0)
{
display_note(_("There is no purchasing data set up for the part selected"));
$dec2 = 6;
if ($Mode =='Edit')
{
-
- $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name FROM ".TB_PREF."purch_data
- INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id
- WHERE ".TB_PREF."purch_data.supplier_id=".db_escape($selected_id)."
- AND ".TB_PREF."purch_data.stock_id=".db_escape($_POST['stock_id']);
-
- $result = db_query($sql, "The supplier purchasing details for the selected supplier and item could not be retrieved");
-
- $myrow = db_fetch($result);
+ $myrow = get_item_purchasing_data($selected_id, $_POST['stock_id']);
$supp_name = $myrow["supp_name"];
$_POST['price'] = price_decimal_format($myrow["price"], $dec2);
projects / fa-stable.git / commitdiff