Global fixes in SQL queries aimed to make them more readible and easier for maintence.

[fa-stable.git] / gl / includes / db / gl_db_accounts.inc

diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc
index 25bc84fce7e6b058e500eb450c2d97c1d0c2cd57..de487be2d4581a256929bad7e0901532c422dd9f 100644 (file)
--- a/gl/includes/db/gl_db_accounts.inc
+++ b/gl/includes/db/gl_db_accounts.inc
@@ -36,13 +36,15 @@ function delete_gl_account($code)
 
 function get_gl_accounts($from=null, $to=null, $type=null)
 {
-       $sql = "SELECT ".TB_PREF."chart_master.*,".TB_PREF."chart_types.name AS AccountTypeName
-               FROM ".TB_PREF."chart_master,".TB_PREF."chart_types
-               WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id";
+       $sql = "SELECT coa.*, act_type.name AS AccountTypeName
+               FROM "
+                       .TB_PREF."chart_master coa,"
+                       .TB_PREF."chart_types act_type
+               WHERE coa.account_type=act_type.id";
        if ($from != null)
-               $sql .= " AND ".TB_PREF."chart_master.account_code >= ".db_escape($from);
+               $sql .= " AND coa.account_code >= ".db_escape($from);
        if ($to != null)
-               $sql .= " AND ".TB_PREF."chart_master.account_code <= ".db_escape($to);
+               $sql .= " AND coa.account_code <= ".db_escape($to);
        if ($type != null)
                $sql .= " AND account_type=".db_escape($type);
        $sql .= " ORDER BY account_code";
@@ -60,11 +62,14 @@ function get_gl_account($code)
 
 function is_account_balancesheet($code)
 {
-       $sql = "SELECT ".TB_PREF."chart_class.ctype FROM ".TB_PREF."chart_class, "
-               .TB_PREF."chart_types, ".TB_PREF."chart_master
-               WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id AND
-               ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid
-               AND ".TB_PREF."chart_master.account_code=".db_escape($code);
+       $sql = "SELECT act_class.ctype
+               FROM "
+                       .TB_PREF."chart_class act_class, "
+                       .TB_PREF."chart_types act_type, "
+                       .TB_PREF."chart_master coa
+               WHERE coa.account_type=act_type.id
+                       AND     act_type.class_id=act_class.cid
+                       AND coa.account_code=".db_escape($code);
 
        $result = db_query($sql,"could not retreive the account class for $code");
        $row = db_fetch_row($result);
@@ -88,7 +93,9 @@ function get_gl_account_name($code)
 
 function gl_account_in_company_defaults($acc)
 {
-       $sql= "SELECT COUNT(*) FROM ".TB_PREF."sys_prefs WHERE (name='debtors_act' 
+       $sql= "SELECT COUNT(*) 
+       FROM ".TB_PREF."sys_prefs
+       WHERE (name='debtors_act' 
                OR name='pyt_discount_act'
                OR name='creditors_act' 
                OR name='bank_charge_act' 
@@ -115,11 +122,12 @@ function gl_account_in_stock_category($acc)
 {
        $acc = db_escape($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";
+       $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);
@@ -130,11 +138,12 @@ function gl_account_in_stock_master($acc)
 {
        $acc = db_escape($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";
+       $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);
@@ -145,7 +154,10 @@ function gl_account_in_tax_types($acc)
 {
        $acc = db_escape($acc);
 
-       $sql= "SELECT COUNT(*) FROM ".TB_PREF."tax_types WHERE sales_gl_code=$acc OR purchasing_gl_code=$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);
@@ -156,11 +168,12 @@ function gl_account_in_cust_branch($acc)
 {
        $acc = db_escape($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";
+       $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);
@@ -171,10 +184,11 @@ function gl_account_in_suppliers($acc)
 {
        $acc = db_escape($acc);
 
-       $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE 
-               purchase_account=$acc
-               OR payment_discount_account=$acc
-               OR payable_account=$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);
@@ -185,8 +199,10 @@ function gl_account_in_quick_entry_lines($acc)
 {
        $acc = db_escape($acc);
 
-       $sql= "SELECT COUNT(*) FROM ".TB_PREF."quick_entry_lines WHERE 
-               dest_id=$acc AND UPPER(LEFT(action, 1)) <> 'T'";
+       $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);
@@ -198,8 +214,13 @@ function gl_account_in_quick_entry_lines($acc)
 //
 function is_subledger_account($account)
 {
-       $sql = "SELECT 1 FROM ".TB_PREF."cust_branch WHERE receivables_account=".db_escape($account)
-       ." UNION SELECT -1 FROM ".TB_PREF."suppliers WHERE payable_account=".db_escape($account);
+       $sql = "SELECT 1 
+               FROM ".TB_PREF."cust_branch
+               WHERE receivables_account=".db_escape($account)
+       ." UNION
+               SELECT -1 
+               FROM ".TB_PREF."suppliers 
+               WHERE payable_account=".db_escape($account);
 
        $result = db_query($sql,"Couldn't test AR/AP account");
        $myrow = db_fetch_row($result);
@@ -209,10 +230,14 @@ function is_subledger_account($account)
 function get_subaccount_data($code_id, $person_id)
 {
        $sql = "SELECT debtor_ref as name, branch_code as id 
-               FROM ".TB_PREF."cust_branch branch LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
-               WHERE branch.receivables_account=".db_escape($code_id)." AND d.debtor_no=".db_escape($person_id)
-               ." UNION SELECT supp_ref as name, '' as id FROM ".TB_PREF."suppliers supp
-               WHERE supplier_id=".db_escape($person_id)." AND payable_account=".db_escape($code_id);
+               FROM ".TB_PREF."cust_branch branch 
+                       LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
+               WHERE branch.receivables_account=".db_escape($code_id)
+                       ." AND d.debtor_no=".db_escape($person_id)
+               ." UNION SELECT supp_ref as name, '' as id 
+                       FROM ".TB_PREF."suppliers supp
+                       WHERE supplier_id=".db_escape($person_id)." 
+                               AND payable_account=".db_escape($code_id);
        $result = db_query($sql, 'cannot retrieve counterparty name');
 
        return  db_fetch($result);
@@ -220,10 +245,15 @@ function get_subaccount_data($code_id, $person_id)
 
 function get_subaccount_name($code_id, $person_id)
 {
-       $sql = "SELECT debtor_ref as ref FROM ".TB_PREF."cust_branch branch LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
-               WHERE branch.receivables_account=".db_escape($code_id)." AND d.debtor_no=".db_escape($person_id)
-               ." UNION SELECT supp_ref as ref FROM ".TB_PREF."suppliers supp
-               WHERE payable_account=".db_escape($code_id)." AND supplier_id=".db_escape($person_id);
+       $sql = "SELECT debtor_ref as ref
+               FROM ".TB_PREF."cust_branch branch
+                       LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no
+               WHERE branch.receivables_account=".db_escape($code_id)
+                       ." AND d.debtor_no=".db_escape($person_id)
+               ." UNION SELECT supp_ref as ref
+                       FROM ".TB_PREF."suppliers supp
+                       WHERE payable_account=".db_escape($code_id)." 
+                               AND supplier_id=".db_escape($person_id);
        $result = db_query($sql, 'cannot retrieve counterparty name');
        $row = db_fetch($result);