From cc9972b02f2bf4ece12da3431266e85e96872fb1 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Wed, 2 Dec 2009 23:46:11 +0000 Subject: [PATCH] Moved all SQL statements from PHP files into relevant *_db.inc files. Included inventory folder --- CHANGELOG.txt | 34 +++++++++++++ admin/db/company_db.inc | 16 ++---- admin/db/printers_db.inc | 8 --- admin/db/shipping_db.inc | 22 --------- admin/payment_terms.php | 4 +- admin/print_profiles.php | 4 +- admin/printers.php | 2 +- admin/shipping_companies.php | 5 +- dimensions/includes/dimensions_db.inc | 5 +- gl/includes/db/gl_db_account_types.inc | 26 ---------- gl/includes/db/gl_db_accounts.inc | 18 ------- gl/includes/db/gl_db_bank_accounts.inc | 20 -------- gl/includes/db/gl_db_currencies.inc | 33 ------------- gl/manage/bank_accounts.php | 4 +- gl/manage/currencies.php | 10 ++-- gl/manage/gl_account_classes.php | 2 +- gl/manage/gl_account_types.php | 4 +- gl/manage/gl_accounts.php | 6 +-- inventory/includes/db/items_category_db.inc | 9 ++++ inventory/includes/db/items_db.inc | 47 ++++++++++++++++++ inventory/includes/db/items_locations_db.inc | 11 ++++- inventory/includes/db/movement_types_db.inc | 34 +++++++++++++ inventory/includes/inventory_db.inc | 1 + inventory/inquiry/stock_movements.php | 33 ++++--------- inventory/manage/item_categories.php | 11 +---- inventory/manage/items.php | 44 +---------------- inventory/manage/locations.php | 45 ++++------------- inventory/manage/movement_types.php | 7 +-- inventory/purchasing_data.php | 51 ++++---------------- 29 files changed, 195 insertions(+), 321 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index c1e27e33..96990eb0 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,40 @@ Legend: ! -> 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 --------------------------------------- diff --git a/admin/db/company_db.inc b/admin/db/company_db.inc index c95ef8de..22fc2d20 100644 --- a/admin/db/company_db.inc +++ b/admin/db/company_db.inc @@ -193,18 +193,12 @@ function get_payment_terms_all($show_inactive) 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); } diff --git a/admin/db/printers_db.inc b/admin/db/printers_db.inc index b873ee2e..43e13b4c 100644 --- a/admin/db/printers_db.inc +++ b/admin/db/printers_db.inc @@ -106,12 +106,4 @@ function get_print_profile($name) 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 diff --git a/admin/db/shipping_db.inc b/admin/db/shipping_db.inc index b447426e..7082b5b5 100644 --- a/admin/db/shipping_db.inc +++ b/admin/db/shipping_db.inc @@ -68,26 +68,4 @@ function get_shipper($selected_id) 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 diff --git a/admin/payment_terms.php b/admin/payment_terms.php index c6a98794..a6aa77e7 100644 --- a/admin/payment_terms.php +++ b/admin/payment_terms.php @@ -74,13 +74,13 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') 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")); } diff --git a/admin/print_profiles.php b/admin/print_profiles.php index 5b387534..59f9ec80 100644 --- a/admin/print_profiles.php +++ b/admin/print_profiles.php @@ -77,9 +77,7 @@ function check_delete($name) { // 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')) diff --git a/admin/printers.php b/admin/printers.php index cd44a8e1..3334d6b7 100644 --- a/admin/printers.php +++ b/admin/printers.php @@ -59,7 +59,7 @@ if ($Mode == 'Delete') { // 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.")); } diff --git a/admin/shipping_companies.php b/admin/shipping_companies.php index 9422d846..fa7b2641 100644 --- a/admin/shipping_companies.php +++ b/admin/shipping_companies.php @@ -53,7 +53,7 @@ if ($Mode == 'Delete') { // 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.")); @@ -61,8 +61,7 @@ if ($Mode == 'Delete') 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.")); diff --git a/dimensions/includes/dimensions_db.inc b/dimensions/includes/dimensions_db.inc index 3335623d..54ece7c4 100644 --- a/dimensions/includes/dimensions_db.inc +++ b/dimensions/includes/dimensions_db.inc @@ -122,10 +122,11 @@ function dimension_has_deposits($id) 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) diff --git a/gl/includes/db/gl_db_account_types.inc b/gl/includes/db/gl_db_account_types.inc index 4eb22d3e..1c25db71 100644 --- a/gl/includes/db/gl_db_account_types.inc +++ b/gl/includes/db/gl_db_account_types.inc @@ -113,30 +113,4 @@ function delete_account_class($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 diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc index 3c29cd3a..279a55be 100644 --- a/gl/includes/db/gl_db_accounts.inc +++ b/gl/includes/db/gl_db_accounts.inc @@ -104,15 +104,6 @@ function get_gl_account_name($code) 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 @@ -137,15 +128,6 @@ function gl_account_in_company_defaults($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 diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index 4377ec29..7d79858b 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -102,26 +102,6 @@ function get_bank_gl_account($id) //--------------------------------------------------------------------------------------------- -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) diff --git a/gl/includes/db/gl_db_currencies.inc b/gl/includes/db/gl_db_currencies.inc index e13a9144..a4ddf95a 100644 --- a/gl/includes/db/gl_db_currencies.inc +++ b/gl/includes/db/gl_db_currencies.inc @@ -68,37 +68,4 @@ function get_currencies($all=false) 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 diff --git a/gl/manage/bank_accounts.php b/gl/manage/bank_accounts.php index 64a96f8f..5ec75616 100644 --- a/gl/manage/bank_accounts.php +++ b/gl/manage/bank_accounts.php @@ -66,13 +66,13 @@ elseif( $Mode == 'Delete') $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.")); diff --git a/gl/manage/currencies.php b/gl/manage/currencies.php index 99f80bdd..856bc2b2 100644 --- a/gl/manage/currencies.php +++ b/gl/manage/currencies.php @@ -89,26 +89,26 @@ function check_can_delete() $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; diff --git a/gl/manage/gl_account_classes.php b/gl/manage/gl_account_classes.php index d90a9524..9e7e1362 100644 --- a/gl/manage/gl_account_classes.php +++ b/gl/manage/gl_account_classes.php @@ -71,7 +71,7 @@ function can_delete($selected_id) { 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; diff --git a/gl/manage/gl_account_types.php b/gl/manage/gl_account_types.php index cad41fbb..8efa766e 100644 --- a/gl/manage/gl_account_types.php +++ b/gl/manage/gl_account_types.php @@ -79,13 +79,13 @@ function can_delete($selected_id) 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; diff --git a/gl/manage/gl_accounts.php b/gl/manage/gl_accounts.php index 5a277c6a..7b1937fd 100644 --- a/gl/manage/gl_accounts.php +++ b/gl/manage/gl_accounts.php @@ -108,7 +108,7 @@ function can_delete($selected_account) 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; @@ -119,8 +119,8 @@ function can_delete($selected_account) 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; diff --git a/inventory/includes/db/items_category_db.inc b/inventory/includes/db/items_category_db.inc index 11f9486d..27a4ed6b 100644 --- a/inventory/includes/db/items_category_db.inc +++ b/inventory/includes/db/items_category_db.inc @@ -64,6 +64,15 @@ function delete_item_category($id) 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); diff --git a/inventory/includes/db/items_db.inc b/inventory/includes/db/items_db.inc index b0553726..534d8b71 100644 --- a/inventory/includes/db/items_db.inc +++ b/inventory/includes/db/items_db.inc @@ -109,4 +109,51 @@ function get_items() 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") + .':
'; + + while($num_kits--) { + $kit = db_fetch($kits); + $msg .= "'".$kit[0]."'"; + if ($num_kits) $msg .= ','; + } + + } + } + return $msg; +} ?> \ No newline at end of file diff --git a/inventory/includes/db/items_locations_db.inc b/inventory/includes/db/items_locations_db.inc index b0372971..4a12c671 100644 --- a/inventory/includes/db/items_locations_db.inc +++ b/inventory/includes/db/items_locations_db.inc @@ -63,6 +63,15 @@ function get_item_location($item_location) //------------------------------------------------------------------------------------ +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 @@ -83,6 +92,4 @@ function get_loc_details($stock_id) return db_query($sql,"an item reorder could not be retreived"); } -//------------------------------------------------------------------------------------ - ?> \ No newline at end of file diff --git a/inventory/includes/db/movement_types_db.inc b/inventory/includes/db/movement_types_db.inc index 210cad43..1862f7b1 100644 --- a/inventory/includes/db/movement_types_db.inc +++ b/inventory/includes/db/movement_types_db.inc @@ -49,4 +49,38 @@ function delete_movement_type($type_id) 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 diff --git a/inventory/includes/inventory_db.inc b/inventory/includes/inventory_db.inc index d7b20c9d..34f7b3d9 100644 --- a/inventory/includes/inventory_db.inc +++ b/inventory/includes/inventory_db.inc @@ -16,6 +16,7 @@ include_once($path_to_root . "/includes/manufacturing.inc"); 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"); diff --git a/inventory/inquiry/stock_movements.php b/inventory/inquiry/stock_movements.php index 61388dd1..e8f6a257 100644 --- a/inventory/inquiry/stock_movements.php +++ b/inventory/inquiry/stock_movements.php @@ -62,15 +62,8 @@ set_global_stock_item($_POST['stock_id']); $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); @@ -79,19 +72,16 @@ $th = array(_("Type"), _("#"), _("Reference"), _("Date"), _("Detail"), 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(""._("Quantity on hand before") . " " . $_POST['AfterDate']."", "align=center colspan=5"); label_cell(" ", "colspan=2"); @@ -147,13 +137,10 @@ while ($myrow = db_fetch($result)) 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) { diff --git a/inventory/manage/item_categories.php b/inventory/manage/item_categories.php index a12f56ff..d8dbb69e 100644 --- a/inventory/manage/item_categories.php +++ b/inventory/manage/item_categories.php @@ -67,10 +67,7 @@ if ($Mode == 'Delete') { // 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.")); } @@ -94,11 +91,7 @@ if (list_updated('mb_flag')) { } //---------------------------------------------------------------------------------- -$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%"); diff --git a/inventory/manage/items.php b/inventory/manage/items.php index ac042933..5e72e405 100644 --- a/inventory/manage/items.php +++ b/inventory/manage/items.php @@ -210,50 +210,8 @@ if (get_post('clone')) { 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") - .':
'; - - while($num_kits--) { - $kit = db_fetch($kits); - $msg .= "'".$kit[0]."'"; - if ($num_kits) $msg .= ','; - } - - } - } if ($msg != '') { if($dispmsg) display_error($msg); return false; diff --git a/inventory/manage/locations.php b/inventory/manage/locations.php index 6dae2fa3..1deb1f20 100644 --- a/inventory/manage/locations.php +++ b/inventory/manage/locations.php @@ -71,69 +71,46 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') 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; @@ -162,9 +139,7 @@ if ($Mode == 'RESET') $_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); diff --git a/inventory/manage/movement_types.php b/inventory/manage/movement_types.php index 259f6dce..edec494b 100644 --- a/inventory/manage/movement_types.php +++ b/inventory/manage/movement_types.php @@ -56,12 +56,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') 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; diff --git a/inventory/purchasing_data.php b/inventory/purchasing_data.php index b77a92bd..7bb09a54 100644 --- a/inventory/purchasing_data.php +++ b/inventory/purchasing_data.php @@ -55,26 +55,14 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') { 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'; @@ -85,11 +73,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') 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'; } @@ -130,15 +114,8 @@ if ($mb_flag == -1) } 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")); @@ -186,15 +163,7 @@ else $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); -- 2.30.2