From 0bf933423b9645bcb57390c478d4fdaf0c895049 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Tue, 1 Dec 2009 16:55:24 +0000 Subject: [PATCH] Moved all SQL statements from PHP files into relevant *_db.inc files. included gl folder --- CHANGELOG.txt | 52 +++++++++- dimensions/includes/dimensions_db.inc | 72 ++++++++++++++ dimensions/includes/dimensions_ui.inc | 9 +- dimensions/inquiry/search_dimensions.php | 53 +---------- gl/bank_account_reconcile.php | 44 ++------- gl/gl_budget.php | 73 ++------------ gl/includes/db/gl_db_account_types.inc | 26 +++++ gl/includes/db/gl_db_accounts.inc | 110 ++++++++++++++++++++++ gl/includes/db/gl_db_bank_accounts.inc | 95 +++++++++++++++++++ gl/includes/db/gl_db_bank_trans.inc | 27 ++++++ gl/includes/db/gl_db_currencies.inc | 31 ++++++ gl/includes/db/gl_db_rates.inc | 10 ++ gl/includes/db/gl_db_trans.inc | 115 +++++++++++++++++++++-- gl/inquiry/bank_inquiry.php | 17 +--- gl/inquiry/journal_inquiry.php | 40 +------- gl/manage/bank_accounts.php | 21 +---- gl/manage/currencies.php | 22 +---- gl/manage/exchange_rates.php | 5 +- gl/manage/gl_account_classes.php | 6 +- gl/manage/gl_account_types.php | 12 +-- gl/manage/gl_accounts.php | 77 +++------------ gl/view/gl_trans_view.php | 10 +- 22 files changed, 581 insertions(+), 346 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 26aeb4b5..e8708a75 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,8 +19,58 @@ Legend: ! -> Note $ -> Affected files +01-Dec-2009 Joe Hunt +! Moved all SQL statements from PHP files into relevant *_db.inc files. + included gl folder +$ /dimensions/includes/dimensions_db.inc + /dimensions/includes/dimensions_ui.inc + /dimensions/inquiry/search_dimensions.php + /gl/bank_account_reconcile.pph + /gl/gl_budget.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_bank_trans.inc + /gl/includes/db/gl_db_currencies.inc + /gl/includes/db/gl_db_rates.inc + /gl/includes/db/gl_db_trans.inc + /gl/inquiry/bank_inquiry.php + /gl/inquiry/journal_inquiry.php + /gl/manage/bank_accounts.php + /gl/manage/currencies.php + /gl/manage/exchange_rates.php + /gl/manage/gl_accounts.php + /gl/manage/gl_account_classes.php + /gl/manage/gl_account_types.php + /gl/view/gl_trans_view.php + +30-Nov-2009 Joe Hunt +! Moved all SQL statements from PHP files into relevant *_db.inc files. + admin folder +$ /admin/attachments.php + /admin/create_coy.php + /admin/fiscalyears.php + /admin/gl_setup.php + /admin/payment_terms.php + /admin/printers.php + /admin/shipping_companies.php + /admin/view_print_transaction.php + /admin/db/attachments_db.inc (New file) + /admin/db/company_db.inc + /admin/db/fiscalyears_db.inc (New file) + /admin/db/maintenance_db.inc + /admin/db/printers_db.inc + /admin/db/shipping_db.inc (New file) + /admin/db/transactions_db.inc (New file) + /includes/date_functions.inc + /reporting/includes/excel_report.inc + /reporting/includes/pdf_report.inc + + + + ------------------------------- Release 2.2 ---------------------------------- -18-Nov-2009 +18-Nov-2009 Joe Hunt ! Release 2.2 $ config.default.php # Allow null references in trans to show up in gl_trans_view diff --git a/dimensions/includes/dimensions_db.inc b/dimensions/includes/dimensions_db.inc index 8a4f7a54..3335623d 100644 --- a/dimensions/includes/dimensions_db.inc +++ b/dimensions/includes/dimensions_db.inc @@ -150,4 +150,76 @@ function reopen_dimension($id) db_query($sql, "could not reopen dimension"); } +//-------------------------------------------------------------------------------------- + +function get_dimension_balance_all($id, $from, $to) +{ + $from = date2sql($from); + $to = date2sql($to); + $sql = "SELECT account, ".TB_PREF."chart_master.account_name, sum(amount) AS amt FROM + ".TB_PREF."gl_trans,".TB_PREF."chart_master WHERE + ".TB_PREF."gl_trans.account = ".TB_PREF."chart_master.account_code AND + (dimension_id = $id OR dimension2_id = $id) AND + tran_date >= '$from' AND tran_date <= '$to' GROUP BY account"; + return db_query($sql, "Transactions could not be calculated"); +} + +//-------------------------------------------------------------------------------------- + +function get_dimension_balance($id, $from, $to) +{ + $id = db_escape($id); + $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE tran_date >= '" . + date2sql($from) . "' AND + tran_date <= '" . date2sql($to) . "' AND (dimension_id = " . + $id." OR dimension2_id = " .$id.")"; + $res = db_query($sql, "Sum of transactions could not be calculated"); + $row = db_fetch_row($res); + + return $row[0]; +} + +//-------------------------------------------------------------------------------------- + +function get_sql_for_search_dimensions($dim) +{ + $sql = "SELECT dim.id, + dim.reference, + dim.name, + dim.type_, + dim.date_, + dim.due_date, + dim.closed + FROM ".TB_PREF."dimensions as dim WHERE id > 0"; + + if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") + { + $sql .= " AND reference LIKE ".db_escape("%". $_POST['OrderNumber'] . "%"); + } else { + + if ($dim == 1) + $sql .= " AND type_=1"; + + if (isset($_POST['OpenOnly'])) + { + $sql .= " AND closed=0"; + } + + if (isset($_POST['type_']) && ($_POST['type_'] > 0)) + { + $sql .= " AND type_=".db_escape($_POST['type_']); + } + + if (isset($_POST['OverdueOnly'])) + { + $today = date2sql(Today()); + + $sql .= " AND due_date < '$today'"; + } + + $sql .= " AND date_ >= '" . date2sql($_POST['FromDate']) . "' + AND date_ <= '" . date2sql($_POST['ToDate']) . "'"; + } + return $sql; +} ?> \ No newline at end of file diff --git a/dimensions/includes/dimensions_ui.inc b/dimensions/includes/dimensions_ui.inc index 6ac85524..edff4b6d 100644 --- a/dimensions/includes/dimensions_ui.inc +++ b/dimensions/includes/dimensions_ui.inc @@ -17,14 +17,7 @@ function display_dimension_balance($id, $from, $to) { global $path_to_root, $table_style; - $from = date2sql($from); - $to = date2sql($to); - $sql = "SELECT account, ".TB_PREF."chart_master.account_name, sum(amount) AS amt FROM - ".TB_PREF."gl_trans,".TB_PREF."chart_master WHERE - ".TB_PREF."gl_trans.account = ".TB_PREF."chart_master.account_code AND - (dimension_id = $id OR dimension2_id = $id) AND - tran_date >= '$from' AND tran_date <= '$to' GROUP BY account"; - $result = db_query($sql, "Transactions could not be calculated"); + $result = get_dimension_balance_all($id, $from, $to); if (db_num_rows($result) == 0) { diff --git a/dimensions/inquiry/search_dimensions.php b/dimensions/inquiry/search_dimensions.php index 29b58efe..49c2dc0b 100644 --- a/dimensions/inquiry/search_dimensions.php +++ b/dimensions/inquiry/search_dimensions.php @@ -97,21 +97,14 @@ function view_link($row) return get_dimensions_trans_view_str(ST_DIMENSION, $row["id"]); } -function is_closed($row) +function sum_dimension($row) { - return $row['closed'] ? _('Yes') : _('No'); + return get_dimension_balance($row['id'], $_POST['FromDate'], $_POST['ToDate']); } -function sum_dimension($row) +function is_closed($row) { - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE tran_date >= '" . - date2sql($_POST['FromDate']) . "' AND - tran_date <= '" . date2sql($_POST['ToDate']) . "' AND (dimension_id = " . - $row['id']." OR dimension2_id = " .$row['id'].")"; - $res = db_query($sql, "Sum of transactions could not be calculated"); - $row = db_fetch_row($res); - - return $row[0]; + return $row['closed'] ? _('Yes') : _('No'); } function is_overdue($row) @@ -128,43 +121,7 @@ function edit_link($row) "/dimensions/dimension_entry.php?trans_no=" . $row["id"], ICON_EDIT); } -$sql = "SELECT dim.id, - dim.reference, - dim.name, - dim.type_, - dim.date_, - dim.due_date, - dim.closed - FROM ".TB_PREF."dimensions as dim WHERE id > 0"; - -if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") -{ - $sql .= " AND reference LIKE ".db_escape("%". $_POST['OrderNumber'] . "%"); -} else { - - if ($dim == 1) - $sql .= " AND type_=1"; - - if (isset($_POST['OpenOnly'])) - { - $sql .= " AND closed=0"; - } - - if (isset($_POST['type_']) && ($_POST['type_'] > 0)) - { - $sql .= " AND type_=".db_escape($_POST['type_']); - } - - if (isset($_POST['OverdueOnly'])) - { - $today = date2sql(Today()); - - $sql .= " AND due_date < '$today'"; - } - - $sql .= " AND date_ >= '" . date2sql($_POST['FromDate']) . "' - AND date_ <= '" . date2sql($_POST['ToDate']) . "'"; -} +$sql = get_sql_for_search_dimensions($dim); $cols = array( _("#") => array('fun'=>'view_link'), diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php index c06b9d5b..f59ee696 100644 --- a/gl/bank_account_reconcile.php +++ b/gl/bank_account_reconcile.php @@ -118,17 +118,10 @@ function change_tpl_flag($reconcile_id) $_POST['bank_date'] = date2sql(get_post('reconcile_date')); $reconcile_value = check_value("rec_".$reconcile_id) ? ("'".$_POST['bank_date'] ."'") : 'NULL'; - $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value" - ." WHERE id=".db_escape($reconcile_id); - - db_query($sql, "Can't change reconciliation status"); - // save last reconcilation status (date, end balance) - $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='" - .date2sql($_POST["reconcile_date"])."', - ending_reconcile_balance=".input_num("end_balance") - ." WHERE id=".db_escape($_POST["bank_account"]); - - $result = db_query($sql2,"Error updating reconciliation information"); + + update_reconciled_values($reconcile_id, $reconcile_value, $_POST['reconcile_date'], + input_num('end_balance'), $_POST['bank_account']); + $Ajax->activate('reconciled'); $Ajax->activate('difference'); return true; @@ -177,19 +170,7 @@ bank_reconciliation_list_cells(_("Bank Statement:"), get_post('bank_account'), end_row(); end_table(); -$date = date2sql(get_post('reconcile_date')); - // temporary fix to enable fix of invalid entries made in 2.2RC -if ($date == 0) $date = '0000-00-00'; - -$sql = "SELECT MAX(reconciled) as last_date, - SUM(IF(reconciled<='$date', amount, 0)) as end_balance, - SUM(IF(reconciled<'$date', amount, 0)) as beg_balance, - SUM(amount) as total - FROM ".TB_PREF."bank_trans trans - WHERE bank_act=".db_escape($_POST['bank_account']); -// ." AND trans.reconciled IS NOT NULL"; - -$result = db_query($sql,"Cannot retrieve reconciliation data"); +$result = get_max_reconciled(get_post('reconcile_date'), $_POST['bank_account']); if ($row = db_fetch($result)) { $_POST["reconciled"] = price_format($row["end_balance"]-$row["beg_balance"]); @@ -200,11 +181,8 @@ if ($row = db_fetch($result)) { $_POST["end_balance"] = price_format($row["end_balance"]); if (get_post('bank_date')) { // if it is the last updated bank statement retrieve ending balance - $sql = "SELECT ending_reconcile_balance - FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($_POST['bank_account']) - . " AND last_reconciled_date=".db_escape($_POST['bank_date']); - $result = db_query($sql,"Cannot retrieve last reconciliation"); - $row = db_fetch($result); + + $row = get_ending_reconciled($_POST['bank_account'], $_POST['bank_date']); if($row) { $_POST["end_balance"] = price_format($row["ending_reconcile_balance"]); } @@ -245,13 +223,7 @@ echo "
"; if (!isset($_POST['bank_account'])) $_POST['bank_account'] = ""; -$sql = "SELECT type, trans_no, ref, trans_date, - amount, person_id, person_type_id, reconciled, id - FROM ".TB_PREF."bank_trans - WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($_POST['bank_account']) . " - AND (reconciled IS NULL OR reconciled='". $date ."') - ORDER BY trans_date,".TB_PREF."bank_trans.id"; -// or ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id"; +$sql = get_sql_for_bank_account_reconcile($_POST['bank_account'], get_post('reconcile_date')); $act = get_bank_account($_POST["bank_account"]); display_heading($act['bank_account_name']." - ".$act['bank_curr_code']); diff --git a/gl/gl_budget.php b/gl/gl_budget.php index 2fc74cb4..26c64862 100644 --- a/gl/gl_budget.php +++ b/gl/gl_budget.php @@ -11,76 +11,19 @@ ***********************************************************************/ $page_security = 'SA_BUDGETENTRY'; $path_to_root = ".."; -include($path_to_root . "/includes/session.inc"); +include_once($path_to_root . "/includes/session.inc"); add_js_file('budget.js'); page(_($help_context = "Budget Entry")); -include($path_to_root . "/includes/ui.inc"); -include($path_to_root . "/gl/includes/gl_db.inc"); +include_once($path_to_root . "/includes/ui.inc"); +include_once($path_to_root . "/gl/includes/gl_db.inc"); include_once($path_to_root . "/includes/data_checks.inc"); +include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); -check_db_has_gl_account_groups(_("There are no account groups defined. Please define at least one account group before entering accounts.")); - -//------------------------------------------------------------------------------------- - -function exists_gl_budget($date_, $account, $dimension, $dimension2) -{ - $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) - ." AND tran_date='$date_' AND - dimension_id=".db_escape($dimension)." AND dimension2_id=".db_escape($dimension2); - $result = db_query($sql, "Cannot retreive a gl transaction"); - - return (db_num_rows($result) > 0); -} - -function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $amount) -{ - $date = date2sql($date_); - - if (exists_gl_budget($date, $account, $dimension, $dimension2)) - $sql = "UPDATE ".TB_PREF."budget_trans SET amount=".db_escape($amount) - ." WHERE account=".db_escape($account) - ." AND dimension_id=".db_escape($dimension) - ." AND dimension2_id=".db_escape($dimension2) - ." AND tran_date='$date'"; - else - $sql = "INSERT INTO ".TB_PREF."budget_trans (tran_date, - account, dimension_id, dimension2_id, amount, memo_) VALUES ('$date', - ".db_escape($account).", ".db_escape($dimension).", " - .db_escape($dimension2).", ".db_escape($amount).", '')"; - - db_query($sql, "The GL budget transaction could not be saved"); -} -function delete_gl_budget_trans($date_, $account, $dimension, $dimension2) -{ - $date = date2sql($date_); - - $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) - ." AND dimension_id=".db_escape($dimension) - ." AND dimension2_id=".db_escape($dimension2) - ." AND tran_date='$date'"; - db_query($sql, "The GL budget transaction could not be deleted"); -} - -function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0) -{ - - $from = date2sql($from_date); - $to = date2sql($to_date); - - $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans - WHERE account=".db_escape($account) - ." AND tran_date >= '$from' AND tran_date <= '$to' - AND dimension_id = ".db_escape($dimension) - ." AND dimension2_id = ".db_escape($dimension2); - $result = db_query($sql,"No budget accounts were returned"); - - $row = db_fetch_row($result); - return $row[0]; -} +check_db_has_gl_account_groups(_("There are no account groups defined. Please define at least one account group before entering accounts.")); //------------------------------------------------------------------------------------- @@ -151,11 +94,7 @@ if (db_has_gl_accounts()) table_header($th); $year = $_POST['fyear']; if (get_post('update') == '') { - $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($year); - - $result = db_query($sql, "could not get current fiscal year"); - - $fyear = db_fetch($result); + $fyear = get_fiscalyear($year); $_POST['begin'] = sql2date($fyear['begin']); $_POST['end'] = sql2date($fyear['end']); } diff --git a/gl/includes/db/gl_db_account_types.inc b/gl/includes/db/gl_db_account_types.inc index 1c25db71..4eb22d3e 100644 --- a/gl/includes/db/gl_db_account_types.inc +++ b/gl/includes/db/gl_db_account_types.inc @@ -113,4 +113,30 @@ 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 518f577f..3c29cd3a 100644 --- a/gl/includes/db/gl_db_accounts.inc +++ b/gl/includes/db/gl_db_accounts.inc @@ -104,5 +104,115 @@ 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 + OR pyt_discount_act=$acc + OR creditors_act=$acc + OR bank_charge_act=$acc + OR exchange_diff_act=$acc + OR profit_loss_year_act=$acc + OR retained_earnings_act=$acc + OR freight_act=$acc + OR default_sales_act=$acc + OR default_sales_discount_act=$acc + OR default_prompt_payment_act=$acc + OR default_inventory_act=$acc + OR default_cogs_act=$acc + OR default_adj_act=$acc + OR default_inv_sales_act=$acc + OR default_assembly_act=$acc"; + $result = db_query($sql,"Couldn't test for default company GL codes"); + + $myrow = db_fetch_row($result); + 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 + dflt_inventory_act=$acc + OR dflt_cogs_act=$acc + OR dflt_adjustment_act=$acc + OR dflt_sales_act=$acc"; + $result = db_query($sql,"Couldn't test for existing stock category GL codes"); + + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + +function gl_account_in_stock_master($acc) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE + inventory_account=$acc + OR cogs_account=$acc + OR adjustment_account=$acc + OR sales_account=$acc"; + $result = db_query($sql,"Couldn't test for existing stock GL codes"); + + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + +function gl_account_in_tax_types($acc) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."tax_types WHERE sales_gl_code=$acc OR purchasing_gl_code=$acc"; + $result = db_query($sql,"Couldn't test for existing tax GL codes"); + + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + +function gl_account_in_cust_branch($acc) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE + sales_account=$acc + OR sales_discount_account=$acc + OR receivables_account=$acc + OR payment_discount_account=$acc"; + $result = db_query($sql,"Couldn't test for existing cust branch GL codes"); + + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + +function gl_account_in_suppliers($acc) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE + purchase_account=$acc + OR payment_discount_account=$acc + OR payable_account=$acc"; + $result = db_query($sql,"Couldn't test for existing suppliers GL codes"); + + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + +function gl_account_in_quick_entry_lines($acc) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."quick_entry_lines WHERE + dest_id=$acc AND UPPER(LEFT(action, 1)) <> 'T'"; + $result = db_query($sql,"Couldn't test for existing Quick Entry Line GL codes"); + + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} ?> \ No newline at end of file diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index a86b2ec4..4377ec29 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -75,6 +75,20 @@ function get_bank_account($id) } //--------------------------------------------------------------------------------------------- + +function get_bank_accounts($show_inactive) +{ + $sql = "SELECT account.*, gl_account.account_name + FROM ".TB_PREF."bank_accounts account, ".TB_PREF."chart_master gl_account + WHERE account.account_code = gl_account.account_code"; + if (!$show_inactive) $sql .= " AND !account.inactive"; + $sql .= " ORDER BY account_code, bank_curr_code"; + + return db_query($sql,"could not get bank accounts"); +} + +//--------------------------------------------------------------------------------------------- + function get_bank_gl_account($id) { $sql = "SELECT account_code FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id); @@ -88,6 +102,26 @@ 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) @@ -220,6 +254,67 @@ function get_quick_entry_line($selected_id) //--------------------------------------------------------------------------------------------- +function get_max_reconciled($date, $bank_account) +{ + $date = date2sql($date); + // temporary fix to enable fix of invalid entries made in 2.2RC + if ($date == 0) $date = '0000-00-00'; + + $sql = "SELECT MAX(reconciled) as last_date, + SUM(IF(reconciled<='$date', amount, 0)) as end_balance, + SUM(IF(reconciled<'$date', amount, 0)) as beg_balance, + SUM(amount) as total + FROM ".TB_PREF."bank_trans trans + WHERE bank_act=".db_escape($bank_account); + // ." AND trans.reconciled IS NOT NULL"; + + return db_query($sql,"Cannot retrieve reconciliation data"); + +} + +//--------------------------------------------------------------------------------------------- + +function get_ending_reconciled($bank_account, $bank_date) +{ + $sql = "SELECT ending_reconcile_balance + FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($bank_account) + . " AND last_reconciled_date=".db_escape($bank_date); + $result = db_query($sql,"Cannot retrieve last reconciliation"); + return db_fetch($result); +} + +//--------------------------------------------------------------------------------------------- + +function get_sql_for_bank_account_reconcile($bank_account, $date) +{ + $sql = "SELECT type, trans_no, ref, trans_date, + amount, person_id, person_type_id, reconciled, id + FROM ".TB_PREF."bank_trans + WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . " + AND (reconciled IS NULL OR reconciled='". date2sql($date) ."') + ORDER BY trans_date,".TB_PREF."bank_trans.id"; +// or ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id"; + return $sql; +} + +//--------------------------------------------------------------------------------------------- + +function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_date, $end_balance, $bank_account) +{ + $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value" + ." WHERE id=".db_escape($reconcile_id); + + db_query($sql, "Can't change reconciliation status"); + // save last reconcilation status (date, end balance) + $sql2 = "UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='" + .date2sql($reconcile_date)."', + ending_reconcile_balance=$end_balance + WHERE id=".db_escape($bank_account); + + db_query($sql2,"Error updating reconciliation information"); +} +//--------------------------------------------------------------------------------------------- + function get_default_bank_account($curr) { /* default bank account is selected as first found account from: diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index ad369861..37c173c0 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -83,6 +83,33 @@ function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id= //---------------------------------------------------------------------------------------- +function get_bank_trans_for_bank_account($bank_account, $from, $to) +{ + $from = date2sql($from); + $to = date2sql($to); + $sql = "SELECT ".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans + WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . " + AND trans_date >= '$from' + AND trans_date <= '$to' + ORDER BY trans_date,".TB_PREF."bank_trans.id"; + + return db_query($sql,"The transactions for '" . $bank_account . "' could not be retrieved"); +} + +//---------------------------------------------------------------------------------------- + +function get_balance_before_for_bank_account($bank_account, $from) +{ + $from = date2sql($from); + $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act=" + .db_escape($bank_account) . " + AND trans_date < '$from'"; + $before_qty = db_query($sql, "The starting balance on hand could not be calculated"); + $bfw_row = db_fetch_row($before_qty); + return $bfw_row[0]; +} +//---------------------------------------------------------------------------------------- + function get_gl_trans_value($account, $type, $trans_no) { $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE account=" diff --git a/gl/includes/db/gl_db_currencies.inc b/gl/includes/db/gl_db_currencies.inc index eee35e1c..e13a9144 100644 --- a/gl/includes/db/gl_db_currencies.inc +++ b/gl/includes/db/gl_db_currencies.inc @@ -70,4 +70,35 @@ function get_currencies($all=false) //--------------------------------------------------------------------------------------------- +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/includes/db/gl_db_rates.inc b/gl/includes/db/gl_db_rates.inc index c6d5c530..02a78229 100644 --- a/gl/includes/db/gl_db_rates.inc +++ b/gl/includes/db/gl_db_rates.inc @@ -146,4 +146,14 @@ function get_ecb_rate($curr_b) return $val; } // end function get_ecb_rate +//----------------------------------------------------------------------------- + +function get_sql_for_exchange_rates() +{ + $sql = "SELECT date_, rate_buy, id FROM " + .TB_PREF."exchange_rates " + ."WHERE curr_code=".db_escape($_POST['curr_abrev'])." + ORDER BY date_ DESC"; + return $sql; +} ?> \ No newline at end of file diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index af06cedf..cdfa8186 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -140,13 +140,13 @@ function get_gl_transactions($from_date, $to_date, $trans_no=0, function get_gl_trans($type, $trans_id) { - $sql = "SELECT ".TB_PREF."gl_trans.*, " - .TB_PREF."chart_master.account_name FROM " - .TB_PREF."gl_trans, ".TB_PREF."chart_master - WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account - AND ".TB_PREF."gl_trans.type=".db_escape($type) - ." AND ".TB_PREF."gl_trans.type_no=".db_escape($trans_id); - + $sql = "SELECT gl.*, cm.account_name, IF(ISNULL(refs.reference), '', refs.reference) AS reference FROM " + .TB_PREF."gl_trans as gl + LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code + LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id)" + ." WHERE gl.type= ".db_escape($type) + ." AND gl.type_no = ".db_escape($trans_id) + ." ORDER BY counter"; return db_query($sql, "The gl transactions could not be retrieved"); } @@ -269,6 +269,64 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $row = db_fetch_row($result); return $row[0]; } +//------------------------------------------------------------------------------------- + +function exists_gl_budget($date_, $account, $dimension, $dimension2) +{ + $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND tran_date='$date_' AND + dimension_id=".db_escape($dimension)." AND dimension2_id=".db_escape($dimension2); + $result = db_query($sql, "Cannot retreive a gl transaction"); + + return (db_num_rows($result) > 0); +} + +function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $amount) +{ + $date = date2sql($date_); + + if (exists_gl_budget($date, $account, $dimension, $dimension2)) + $sql = "UPDATE ".TB_PREF."budget_trans SET amount=".db_escape($amount) + ." WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; + else + $sql = "INSERT INTO ".TB_PREF."budget_trans (tran_date, + account, dimension_id, dimension2_id, amount, memo_) VALUES ('$date', + ".db_escape($account).", ".db_escape($dimension).", " + .db_escape($dimension2).", ".db_escape($amount).", '')"; + + db_query($sql, "The GL budget transaction could not be saved"); +} + +function delete_gl_budget_trans($date_, $account, $dimension, $dimension2) +{ + $date = date2sql($date_); + + $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; + db_query($sql, "The GL budget transaction could not be deleted"); +} + +function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0) +{ + + $from = date2sql($from_date); + $to = date2sql($to_date); + + $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans + WHERE account=".db_escape($account) + ." AND tran_date >= '$from' AND tran_date <= '$to' + AND dimension_id = ".db_escape($dimension) + ." AND dimension2_id = ".db_escape($dimension2); + $result = db_query($sql,"No budget accounts were returned"); + + $row = db_fetch_row($result); + return $row[0]; +} //-------------------------------------------------------------------------------- // Stores journal/bank transaction tax details if applicable @@ -505,4 +563,47 @@ function void_journal_trans($type, $type_no, $use_transaction=true) commit_transaction(); } +function get_sql_for_journal_inquiry() +{ + // Tom Hallman 11 Nov 2009 + // IF(gl.type = 1... statement is for deposits/payments that may not actually result + // in a deposit, such as when a fix is made. Without that statement (and the + // joining of the bank_trans table), the fix deposit/payment amount would show up + // incorrectly as only the positive side of the fix. + $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq, + gl.tran_date, + gl.type, + gl.type_no, + refs.reference, + IF(gl.type = 1 OR gl.type = 2, + bank_trans.amount, + SUM(IF(gl.amount>0, gl.amount,0))) as amount, + com.memo_, + IF(ISNULL(u.user_id),'',u.user_id) as user_id + FROM ".TB_PREF."gl_trans as gl + LEFT JOIN ".TB_PREF."audit_trail as a ON + (gl.type=a.type AND gl.type_no=a.trans_no) + LEFT JOIN ".TB_PREF."comments as com ON + (gl.type=com.type AND gl.type_no=com.id) + LEFT JOIN ".TB_PREF."refs as refs ON + (gl.type=refs.type AND gl.type_no=refs.id) + LEFT JOIN ".TB_PREF."users as u ON + a.user=u.id + LEFT JOIN ".TB_PREF."bank_trans as bank_trans ON + (gl.type=bank_trans.type AND gl.type_no=bank_trans.trans_no) + WHERE gl.tran_date >= '" . date2sql($_POST['FromDate']) . "' + AND gl.tran_date <= '" . date2sql($_POST['ToDate']) . "' + AND gl.amount!=0"; + if (isset($_POST['Ref']) && $_POST['Ref'] != "") { + $sql .= " AND reference LIKE '%". $_POST['Ref'] . "%'"; + } + if (get_post('filterType') != -1) { + $sql .= " AND gl.type=".get_post('filterType'); + } + if (!check_value('AlsoClosed')) { + $sql .= " AND gl_seq=0"; + } + $sql .= " GROUP BY gl.type, gl.type_no"; + return $sql; +} ?> \ No newline at end of file diff --git a/gl/inquiry/bank_inquiry.php b/gl/inquiry/bank_inquiry.php index b9dcf28e..c37e4184 100644 --- a/gl/inquiry/bank_inquiry.php +++ b/gl/inquiry/bank_inquiry.php @@ -53,18 +53,10 @@ end_form(); //------------------------------------------------------------------------------------------------ - -$date_after = date2sql($_POST['TransAfterDate']); -$date_to = date2sql($_POST['TransToDate']); if (!isset($_POST['bank_account'])) $_POST['bank_account'] = ""; -$sql = "SELECT ".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans - WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($_POST['bank_account']) . " - AND trans_date >= '$date_after' - AND trans_date <= '$date_to' - ORDER BY trans_date,".TB_PREF."bank_trans.id"; -$result = db_query($sql,"The transactions for '" . $_POST['bank_account'] . "' could not be retrieved"); +$result = get_bank_trans_for_bank_account($_POST['bank_account'], $_POST['TransAfterDate'], $_POST['TransToDate']); div_start('trans_tbl'); $act = get_bank_account($_POST["bank_account"]); @@ -76,15 +68,10 @@ $th = array(_("Type"), _("#"), _("Reference"), _("Date"), _("Debit"), _("Credit"), _("Balance"), _("Person/Item"), ""); table_header($th); -$sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act=" - .db_escape($_POST['bank_account']) . " - AND trans_date < '$date_after'"; -$before_qty = db_query($sql, "The starting balance on hand could not be calculated"); +$bfw = get_balance_before_for_bank_account($_POST['bank_account'], $_POST['TransAfterDate']); start_row("class='inquirybg'"); label_cell(""._("Opening Balance")." - ".$_POST['TransAfterDate']."", "colspan=4"); -$bfw_row = db_fetch_row($before_qty); -$bfw = $bfw_row[0]; display_debit_or_credit_cells($bfw); label_cell(""); label_cell("", "colspan=2"); diff --git a/gl/inquiry/journal_inquiry.php b/gl/inquiry/journal_inquiry.php index f2aee15b..b7a787a9 100644 --- a/gl/inquiry/journal_inquiry.php +++ b/gl/inquiry/journal_inquiry.php @@ -111,45 +111,7 @@ function edit_link($row) ICON_EDIT) : ''; } -// Tom Hallman 11 Nov 2009 -// IF(gl.type = 1... statement is for deposits/payments that may not actually result -// in a deposit, such as when a fix is made. Without that statement (and the -// joining of the bank_trans table), the fix deposit/payment amount would show up -// incorrectly as only the positive side of the fix. -$sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq, - gl.tran_date, - gl.type, - gl.type_no, - refs.reference, - IF(gl.type = 1 OR gl.type = 2, - bank_trans.amount, - SUM(IF(gl.amount>0, gl.amount,0))) as amount, - com.memo_, - IF(ISNULL(u.user_id),'',u.user_id) as user_id - FROM ".TB_PREF."gl_trans as gl - LEFT JOIN ".TB_PREF."audit_trail as a ON - (gl.type=a.type AND gl.type_no=a.trans_no) - LEFT JOIN ".TB_PREF."comments as com ON - (gl.type=com.type AND gl.type_no=com.id) - LEFT JOIN ".TB_PREF."refs as refs ON - (gl.type=refs.type AND gl.type_no=refs.id) - LEFT JOIN ".TB_PREF."users as u ON - a.user=u.id - LEFT JOIN ".TB_PREF."bank_trans as bank_trans ON - (gl.type=bank_trans.type AND gl.type_no=bank_trans.trans_no) - WHERE gl.tran_date >= '" . date2sql($_POST['FromDate']) . "' - AND gl.tran_date <= '" . date2sql($_POST['ToDate']) . "' - AND gl.amount!=0"; -if (isset($_POST['Ref']) && $_POST['Ref'] != "") { - $sql .= " AND reference LIKE '%". $_POST['Ref'] . "%'"; -} -if (get_post('filterType') != -1) { - $sql .= " AND gl.type=".get_post('filterType'); -} -if (!check_value('AlsoClosed')) { - $sql .= " AND gl_seq=0"; -} -$sql .= " GROUP BY gl.type, gl.type_no"; +$sql = get_sql_for_journal_inquiry(); $cols = array( _("#") => array('fun'=>'journal_pos', 'align'=>'center'), diff --git a/gl/manage/bank_accounts.php b/gl/manage/bank_accounts.php index ae1dc41e..64a96f8f 100644 --- a/gl/manage/bank_accounts.php +++ b/gl/manage/bank_accounts.php @@ -66,18 +66,13 @@ elseif( $Mode == 'Delete') $acc = db_escape($selected_id); // PREVENT DELETES IF DEPENDENT RECORDS IN 'bank_trans' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."bank_trans WHERE bank_act=$acc"; - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (bank_account_in_transactions($acc)) { $cancel_delete = 1; display_error(_("Cannot delete this bank account because transactions have been created using this account.")); } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_pos WHERE pos_account=$acc"; - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + + if (bank_account_in_sales_pos($acc)) { $cancel_delete = 1; display_error(_("Cannot delete this bank account because POS definitions have been created using this account.")); @@ -99,15 +94,7 @@ if ($Mode == 'RESET') /* Always show the list of accounts */ -$sql = "SELECT account.*, gl_account.account_name - FROM ".TB_PREF."bank_accounts account, ".TB_PREF."chart_master gl_account - WHERE account.account_code = gl_account.account_code"; -if (!check_value('show_inactive')) $sql .= " AND !account.inactive"; -$sql .= " ORDER BY account_code, bank_curr_code"; - -$result = db_query($sql,"could not get bank accounts"); - -check_db_error("The bank accounts set up could not be retreived", $sql); +$result = get_bank_accounts(check_value('show_inactive')); start_form(); start_table("$table_style width='80%'"); diff --git a/gl/manage/currencies.php b/gl/manage/currencies.php index 9bf6a9cd..99f80bdd 100644 --- a/gl/manage/currencies.php +++ b/gl/manage/currencies.php @@ -89,38 +89,26 @@ function check_can_delete() $curr = db_escape($selected_id); // PREVENT DELETES IF DEPENDENT RECORDS IN debtors_master - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE curr_code = $curr"; - $result = db_query($sql); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (currency_in_debtors($curr)) { display_error(_("Cannot delete this currency, because customer accounts have been created referring to this currency.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE curr_code = $curr"; - $result = db_query($sql); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (currency_in_suppliers($curr)) { display_error(_("Cannot delete this currency, because supplier accounts have been created referring to this currency.")); return false; } - - $sql= "SELECT COUNT(*) FROM ".TB_PREF."company WHERE curr_default = $curr"; - $result = db_query($sql); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + + if (currency_in_company($curr)) { 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 - $sql= "SELECT COUNT(*) FROM ".TB_PREF."bank_accounts WHERE bank_curr_code = $curr"; - $result = db_query($sql); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (currenty_in_bank_accounts($curr)) { display_error(_("Cannot delete this currency, because thre are bank accounts that use this currency.")); return false; diff --git a/gl/manage/exchange_rates.php b/gl/manage/exchange_rates.php index a22b7152..9ea1c17f 100644 --- a/gl/manage/exchange_rates.php +++ b/gl/manage/exchange_rates.php @@ -184,10 +184,7 @@ if ($_POST['curr_abrev'] != get_global_curr_code()) set_global_curr_code($_POST['curr_abrev']); -$sql = "SELECT date_, rate_buy, id FROM " - .TB_PREF."exchange_rates " - ."WHERE curr_code=".db_escape($_POST['curr_abrev'])." - ORDER BY date_ DESC"; +$sql = get_sql_for_exchange_rates(); $cols = array( _("Date to Use From") => 'date', diff --git a/gl/manage/gl_account_classes.php b/gl/manage/gl_account_classes.php index bc141c37..d90a9524 100644 --- a/gl/manage/gl_account_classes.php +++ b/gl/manage/gl_account_classes.php @@ -71,11 +71,7 @@ function can_delete($selected_id) { if ($selected_id == -1) return false; - $sql= "SELECT COUNT(*) FROM ".TB_PREF."chart_types - WHERE class_id=$selected_id"; - $result = db_query($sql, "could not query chart master"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (account_class_in_account_types($selected_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 ac3b32cf..cad41fbb 100644 --- a/gl/manage/gl_account_types.php +++ b/gl/manage/gl_account_types.php @@ -79,21 +79,13 @@ function can_delete($selected_id) return false; $type = db_escape($selected_id); - $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); - if ($myrow[0] > 0) + if (account_type_in_chart_master($type)) { display_error(_("Cannot delete this account group because GL accounts have been created referring to it.")); return false; } - $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); - if ($myrow[0] > 0) + if (account_type_in_parent($type)) { 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 e9efad86..5a277c6a 100644 --- a/gl/manage/gl_accounts.php +++ b/gl/manage/gl_accounts.php @@ -108,104 +108,55 @@ function can_delete($selected_account) return false; $acc = db_escape($selected_account); - $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); - if ($myrow[0] > 0) + if (gl_account_in_transactions($acc)) { display_error(_("Cannot delete this account because transactions have been created using this account.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."company WHERE debtors_act=$acc - OR pyt_discount_act=$acc - OR creditors_act=$acc - OR freight_act=$acc - OR default_sales_act=$acc - OR default_sales_discount_act=$acc - OR default_prompt_payment_act=$acc - OR default_inventory_act=$acc - OR default_cogs_act=$acc - OR default_adj_act=$acc - OR default_inv_sales_act=$acc - OR default_assembly_act=$acc"; - $result = db_query($sql,"Couldn't test for default company GL codes"); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (gl_account_in_company_defaults($acc)) { display_error(_("Cannot delete this account because it is used as one of the company default GL accounts.")); return false; } - $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); - if ($myrow[0] > 0) + if (gl_account_in_bank_accounts($acc)) { display_error(_("Cannot delete this account because it is used by a bank account.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE - inventory_account=$acc - OR cogs_account=$acc - OR adjustment_account=$acc - OR sales_account=$acc"; - $result = db_query($sql,"Couldn't test for existing stock GL codes"); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (gl_account_in_stock_category($acc)) + { + display_error(_("Cannot delete this account because it is used by one or more Item Categories.")); + return false; + } + + if (gl_account_in_stock_master($acc)) { display_error(_("Cannot delete this account because it is used by one or more Items.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."tax_types WHERE sales_gl_code=$acc OR purchasing_gl_code=$acc"; - $result = db_query($sql,"Couldn't test for existing tax GL codes"); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (gl_account_in_tax_types($acc)) { display_error(_("Cannot delete this account because it is used by one or more Taxes.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE - sales_account=$acc - OR sales_discount_account=$acc - OR receivables_account=$acc - OR payment_discount_account=$acc"; - $result = db_query($sql,"Couldn't test for existing cust branch GL codes"); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (gl_account_in_cust_branch($acc)) { display_error(_("Cannot delete this account because it is used by one or more Customer Branches.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE - purchase_account=$acc - OR payment_discount_account=$acc - OR payable_account=$acc"; - $result = db_query($sql,"Couldn't test for existing suppliers GL codes"); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (gl_account_in_suppliers($acc)) { display_error(_("Cannot delete this account because it is used by one or more suppliers.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."quick_entry_lines WHERE - dest_id=$acc AND UPPER(LEFT(action, 1)) <> 'T'"; - $result = db_query($sql,"Couldn't test for existing suppliers GL codes"); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (gl_account_in_quick_entry_lines($acc)) { display_error(_("Cannot delete this account because it is used by one or more Quick Entry Lines.")); return false; diff --git a/gl/view/gl_trans_view.php b/gl/view/gl_trans_view.php index 32276c3a..852080fe 100644 --- a/gl/view/gl_trans_view.php +++ b/gl/view/gl_trans_view.php @@ -47,15 +47,7 @@ function display_gl_heading($myrow) end_table(1); } -$sql = "SELECT gl.*, cm.account_name, IF(ISNULL(refs.reference), '', refs.reference) AS reference FROM " - .TB_PREF."gl_trans as gl - LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code - LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id)" - ." WHERE gl.type= ".db_escape($_GET['type_id']) - ." AND gl.type_no = ".db_escape($_GET['trans_no']) - ." ORDER BY counter"; -$result = db_query($sql,"could not get transactions"); -//alert("sql = ".$sql); +$result = get_gl_trans($_GET['type_id'], $_GET['trans_no']); if (db_num_rows($result) == 0) { -- 2.30.2