After heavy testing a new set of nested subtypes in COA, Monthly Breakdown, Balance...

diff --git a/CHANGELOG.txt b/CHANGELOG.txt
index d22d272c8b7d3b1651c0ffed3deb2c2d7aa88546..bcec00496a0a6566fc8adf7c5e830683a685c45c 100644 (file)
--- a/CHANGELOG.txt
+++ b/CHANGELOG.txt
@@ -19,6 +19,15 @@ Legend:
 ! -> Note
 $ -> Affected files
 
+27-May-2009 Joe Hunt
+# After heavy testing a new set of nested subtypes in COA, Monthly Breakdown, Balance Sheet and P&L Statement.
+$ /gl/includes/db/gl_db_accounts.inc
+  /gl/includes/db/gl_db_account_types.inc
+  /reporting/rep701.php
+  /reporting/rep705.php
+  /reporting/rep706.php
+  /reporting/rep707.php
+
 26-May-2009 Joe Hunt
 # Again fixed nested subtypes in account types correctly in COA, Monthly Breakdown, Balance Sheet and P&L Statement
 $ /gl/includes/db/gl_db_accounts.inc

diff --git a/gl/includes/db/gl_db_account_types.inc b/gl/includes/db/gl_db_account_types.inc
index 39af247fb8b078085c9ae40da8e27aaab8b3d0a4..f5375bbddb8414602b702310777f25edba48192f 100644 (file)
--- a/gl/includes/db/gl_db_account_types.inc
+++ b/gl/includes/db/gl_db_account_types.inc
@@ -51,20 +51,6 @@ function get_account_type_name($id)
        return $row[0];
 }
 
-
-function get_account_types_all($balance=-1)
-{
-       $sql = "SELECT ".TB_PREF."chart_types.name AS AccountTypeName,".TB_PREF."chart_types.parent,".TB_PREF."chart_types.id AS AccountType,
-               ".TB_PREF."chart_class.class_name AS AccountClassName
-               FROM ".TB_PREF."chart_types, ".TB_PREF."chart_class
-               WHERE ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid";
-       if ($balance != -1)
-               $sql .= " AND ".TB_PREF."chart_class.balance_sheet=$balance";
-       $sql .= " ORDER BY ".TB_PREF."chart_class.cid, IF(parent > -1,parent,".TB_PREF."chart_types.id), parent";
-
-       return db_query($sql, "could not get gl types");
-}
-
 function delete_account_type($id)
 {
        $sql = "DELETE FROM ".TB_PREF."chart_types WHERE id = $id";

diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc
index 380d5b442e1cca62585f39c0566c975ddb365da6..b544094e325551c51f0afd1fb79f308ff349513e 100644 (file)
--- a/gl/includes/db/gl_db_accounts.inc
+++ b/gl/includes/db/gl_db_accounts.inc
@@ -49,22 +49,20 @@ function get_gl_accounts($from=null, $to=null)
        return db_query($sql, "could not get gl accounts");
 }
 
-function get_gl_accounts_in_type($type)
+function get_gl_accounts_all($balance=-1)
 {
-       $sql = "SELECT * FROM ".TB_PREF."chart_master WHERE account_type=$type ORDER BY account_code";
+        $sql = "SELECT ".TB_PREF."chart_master.account_code, ".TB_PREF."chart_master.account_name, ".TB_PREF."chart_types.name AS AccountTypeName,".TB_PREF."chart_types.id AS AccountType,
+               ".TB_PREF."chart_types.parent, ".TB_PREF."chart_class.class_name AS AccountClassName
+               FROM ".TB_PREF."chart_types INNER JOIN ".TB_PREF."chart_class ON ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid
+               LEFT JOIN ".TB_PREF."chart_master ON ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id ";
+       if ($balance != -1)
+               $sql .= "WHERE ".TB_PREF."chart_class.balance_sheet=$balance ";                                 
+        $sql .= "ORDER BY ".TB_PREF."chart_class.cid, IF(parent > 0,parent,".TB_PREF."chart_types.id), 
+               IF(parent > 0,".TB_PREF."chart_types.id, parent), ".TB_PREF."chart_master.account_code";
 
        return db_query($sql, "could not get gl accounts");
 }
 
-function num_accounts_in_type($type, $parent)
-{
-       $sql = "SELECT COUNT(*) FROM ".TB_PREF."chart_master WHERE account_type=$type OR account_type=$parent";
-
-       $result = db_query($sql, "could not get gl accounts");
-       $row = db_fetch_row($result);
-       return $row[0];
-}
-
 function get_gl_account($code)
 {
        $sql = "SELECT * FROM ".TB_PREF."chart_master WHERE account_code='$code'";

diff --git a/reporting/rep701.php b/reporting/rep701.php
index 6a44328efee127c786589cc51f49b31be3ea64ff..4f49b549fbf91c399fc69bc6ec93aff30487d2f9 100644 (file)
--- a/reporting/rep701.php
+++ b/reporting/rep701.php
@@ -60,34 +60,31 @@ function print_Chart_of_Accounts()
        $classname = '';
        $group = '';
 
-       $types = get_account_types_all();
+       $accounts = get_gl_accounts_all();
 
-       while ($type=db_fetch($types))
+       while ($account=db_fetch($accounts))
        {
-               if (!num_accounts_in_type($type['AccountType'], $type['parent']))
-                       continue;
-               if ($type['AccountTypeName'] != $group)
+               if ($account['AccountTypeName'] != $group)
                {
                        if ($classname != '')
                                $rep->row -= 4;
-                       if ($type['AccountClassName'] != $classname)
+                       if ($account['AccountClassName'] != $classname)
                        {
                                $rep->Font('bold');
-                               $rep->TextCol(0, 4, $type['AccountClassName']);
+                               $rep->TextCol(0, 4, $account['AccountClassName']);
                                $rep->Font();
                                //$rep->row -= ($rep->lineHeight + 4);
                                $rep->NewLine();
                        }
-                       $group = $type['AccountTypeName'];
-                       $rep->TextCol(0, 4, $type['AccountTypeName']);
+                       $group = $account['AccountTypeName'];
+                       $rep->TextCol(0, 4, $account['AccountTypeName']);
                        //$rep->Line($rep->row - 4);
                        //$rep->row -= ($rep->lineHeight + 4);
                        $rep->NewLine();
                }
-               $classname = $type['AccountClassName'];
+               $classname = $account['AccountClassName'];
                
-               $accounts = get_gl_accounts_in_type($type['AccountType']);
-               while ($account=db_fetch($accounts))
+               if ($account['account_code'] != null)
                {
                        if ($showbalance == 1)
                        {

diff --git a/reporting/rep705.php b/reporting/rep705.php
index 8516da4f1ec4e6458dc95357bc0d905077dd4150..b3927d4ce1c038d35b571e0b6bba3ad136513ac2 100644 (file)
--- a/reporting/rep705.php
+++ b/reporting/rep705.php
@@ -198,13 +198,22 @@ function print_annual_expense_breakdown()
        $level = 0;
        $last = -1;
 
-       $types = get_account_types_all(0);
+       $accounts = get_gl_accounts_all(0);
 
-       while ($type = db_fetch($types))
+       while ($account=db_fetch($accounts))
        {
-               if (!num_accounts_in_type($type['AccountType'], $type['parent']))
+               if ($account['account_code'] == null && $account['parent'] > 0)
                        continue;
-               if ($type['AccountClassName'] != $classname)
+
+               if ($account['account_code'] != null)
+               {
+                       $bal = getPeriods($year, $account["account_code"], $dimension, $dimension2);
+                       if (!$bal['per01'] && !$bal['per02'] && !$bal['per03'] && !$bal['per04'] &&
+                               !$bal['per05'] && !$bal['per06'] && !$bal['per07'] && !$bal['per08'] &&
+                               !$bal['per09'] && !$bal['per10'] && !$bal['per11'] && !$bal['per12'])
+                               continue;
+               }
+               if ($account['AccountClassName'] != $classname)
                {
                        if ($classname != '')
                        {
@@ -212,13 +221,13 @@ function print_annual_expense_breakdown()
                        }
                }
 
-               if ($type['AccountTypeName'] != $typename[$level])
+               if ($account['AccountTypeName'] != $typename[$level])
                {
                        if ($typename[$level] != '')
                        {
                                for ( ; $level >= 0, $typename[$level] != ''; $level--) 
                                {
-                                       if ($type['parent'] == $closing[$level] || $type['parent'] == $last || $type['parent'] <= 0)
+                                       if ($account['parent'] == $closing[$level] || $account['parent'] < $last)
                                        {
                                                $rep->row += 6;
                                                $rep->Line($rep->row);
@@ -252,34 +261,28 @@ function print_annual_expense_breakdown()
                                        $closeclass = false;
                                }
                        }
-                       if ($type['AccountClassName'] != $classname)
+                       if ($account['AccountClassName'] != $classname)
                        {
                                $rep->Font('bold');
-                               $rep->TextCol(0, 5, $type['AccountClassName']);
+                               $rep->TextCol(0, 5, $account['AccountClassName']);
                                $rep->Font();
                                $rep->NewLine();
                        }
                        $level++;
-                       if ($type['parent'] != $last)
-                               $last = $type['parent'];
-                       $typename[$level] = $type['AccountTypeName'];
-                       $closing[$level] = $type['parent'];
+                       if ($account['parent'] != $last)
+                               $last = $account['parent'];
+                       $typename[$level] = $account['AccountTypeName'];
+                       $closing[$level] = $account['parent'];
                        $rep->row -= 4;
-                       $rep->TextCol(0, 5, $type['AccountTypeName']);
+                       $rep->TextCol(0, 5, $account['AccountTypeName']);
                        $rep->row -= 4;
                        $rep->Line($rep->row);
                        $rep->NewLine();
                }
-               $classname = $type['AccountClassName'];
+               $classname = $account['AccountClassName'];
 
-               $accounts = get_gl_accounts_in_type($type['AccountType']);
-               while ($account=db_fetch($accounts))
+               if ($account['account_code'] != null)
                {
-                       $bal = getPeriods($year, $account["account_code"], $dimension, $dimension2);
-                       if (!$bal['per01'] && !$bal['per02'] && !$bal['per03'] && !$bal['per04'] &&
-                               !$bal['per05'] && !$bal['per06'] && !$bal['per07'] && !$bal['per08'] &&
-                               !$bal['per09'] && !$bal['per10'] && !$bal['per11'] && !$bal['per12'])
-                               continue;
                        $balance = array(1 => $bal['per01'], $bal['per02'], $bal['per03'], $bal['per04'],
                                $bal['per05'], $bal['per06'], $bal['per07'], $bal['per08'],
                                $bal['per09'], $bal['per10'], $bal['per11'], $bal['per12']);
@@ -305,20 +308,20 @@ function print_annual_expense_breakdown()
                        }
                }       
        }
-       if ($type['AccountClassName'] != $classname)
+       if ($account['AccountClassName'] != $classname)
        {
                if ($classname != '')
                {
                        $closeclass = true;
                }
        }
-       if ($type['AccountTypeName'] != $typename[$level])
+       if ($account['AccountTypeName'] != $typename[$level])
        {
                if ($typename[$level] != '')
                {
                        for ( ; $level >= 0, $typename[$level] != ''; $level--) 
                        {
-                               if ($type['parent'] == $closing[$level] || $type['parent'] == $last || $type['parent'] <= 0)
+                               if ($account['parent'] == $closing[$level] || $account['parent'] < $last)
                                {
                                        $rep->row += 6;
                                        $rep->Line($rep->row);

diff --git a/reporting/rep706.php b/reporting/rep706.php
index e69fc0dba48854aeaa3a6c09b6d8ea5f2e4c95bf..a63f14bb69c9f30133be6bbcf1c298dc2aa85417 100644 (file)
--- a/reporting/rep706.php
+++ b/reporting/rep706.php
@@ -118,19 +118,29 @@ function print_balance_sheet()
        $typeclose = array(0,0,0,0,0,0,0,0,0,0);
        $typename = array('','','','','','','','','','');
        $closing = array(-1,-1,-1,-1,-1,-1,-1,-1,-1,-1);
+       $parent = array(-1,-1,-1,-1,-1,-1,-1,-1,-1,-1);
        $level = 0;
        $last = -1;
        
        $closeclass = false;
        $rep->NewLine();
 
-       $types = get_account_types_all(1);
+       $accounts = get_gl_accounts_all(1);
 
-       while ($type=db_fetch($types))
+       while ($account=db_fetch($accounts))
        {
-               if (!num_accounts_in_type($type['AccountType'], $type['parent']))
+               if ($account['account_code'] == null && $account['parent'] > 0)
                        continue;
-               if ($type['AccountClassName'] != $classname)
+               if ($account['account_code'] != null)
+               {
+                       $prev_balance = get_gl_balance_from_to("", $from, $account["account_code"], $dimension, $dimension2);
+
+                       $curr_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2);
+
+                       if (!$prev_balance && !$curr_balance)
+                               continue;
+               }
+               if ($account['AccountClassName'] != $classname)
                {
                        if ($classname != '')
                        {
@@ -138,16 +148,16 @@ function print_balance_sheet()
                        }
                }
 
-               if ($type['AccountTypeName'] != $typename[$level])
+               if ($account['AccountTypeName'] != $typename[$level])
                {
                        //$rep->NewLine();
-                       //$rep->TextCol(0, 5,   "level = $level, closing[level] = ".$closing[$level].", type[parent] = ".$type['parent']." last = ".$last);
+                       //$rep->TextCol(0, 5,   "type = ".$account['AccountType'].", level = $level, closing[0]-[1]-[2]-[3] = ".$closing[0]." ".$closing[1]." ".$closing[2]." ".$closing[3]." type[parent] = ".$account['parent']." last = ".$last);
                        //$rep->NewLine();
                        if ($typename[$level] != '')
                        {
                                for ( ; $level >= 0, $typename[$level] != ''; $level--) 
                                {
-                                       if ($type['parent'] == $closing[$level] || $type['parent'] == $last || $type['parent'] <= 0)
+                                       if ($account['parent'] == $closing[$level] || $account['parent'] < $last)
                                        {
                                                $rep->row += 6;
                                                $rep->Line($rep->row);
@@ -187,36 +197,28 @@ function print_balance_sheet()
                                        $closeclass = false;
                                }
                        }
-                       if ($type['AccountClassName'] != $classname)
+                       if ($account['AccountClassName'] != $classname)
                        {
                                $rep->Font('bold');
-                               $rep->TextCol(0, 5, $type['AccountClassName']);
+                               $rep->TextCol(0, 5, $account['AccountClassName']);
                                $rep->Font();
                                $rep->NewLine();
                        }
                        $level++;
-                       if ($type['parent'] != $last)
-                               $last = $type['parent'];
-                       $typename[$level] = $type['AccountTypeName'];
-                       $closing[$level] = $type['parent'];
+                       if ($account['parent'] != $last)
+                               $last = $account['parent'];
+                       $typename[$level] = $account['AccountTypeName'];
+                       $closing[$level] = $account['parent'];
                        $rep->row -= 4;
-                       $rep->TextCol(0, 5, $type['AccountTypeName']);
+                       $rep->TextCol(0, 5, $account['AccountTypeName']);
                        $rep->row -= 4;
                        $rep->Line($rep->row);
                        $rep->NewLine();
                }
-               $classname = $type['AccountClassName'];
+               $classname = $account['AccountClassName'];
 
-               $accounts = get_gl_accounts_in_type($type['AccountType']);
-               while ($account=db_fetch($accounts))
+               if ($account['account_code'] != null)
                {
-                       $prev_balance = get_gl_balance_from_to("", $from, $account["account_code"], $dimension, $dimension2);
-
-                       $curr_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2);
-
-                       if (!$prev_balance && !$curr_balance)
-                               continue;
-
                        for ($i = 0; $i <= $level; $i++)
                        {
                                $typeopen[$i] += $prev_balance;
@@ -242,20 +244,20 @@ function print_balance_sheet()
                        }
                }       
        }
-       if ($type['AccountClassName'] != $classname)
+       if ($account['AccountClassName'] != $classname)
        {
                if ($classname != '')
                {
                        $closeclass = true;
                }
        }
-       if ($type['AccountTypeName'] != $typename[$level])
+       if ($account['AccountTypeName'] != $typename[$level])
        {
                if ($typename[$level] != '')
                {
                        for ( ; $level >= 0, $typename[$level] != ''; $level--) 
                        {
-                               if ($type['parent'] == $closing[$level] || $type['parent'] == $last || $type['parent'] <= 0)
+                               if ($account['parent'] == $closing[$level] || $account['parent'] < $last)
                                {
                                        $rep->row += 6;
                                        $rep->Line($rep->row);

diff --git a/reporting/rep707.php b/reporting/rep707.php
index 7799ffeaf573bd88ea98d12f3457c01617ddcfa0..5dd9c1b2e282484590a78c4f4885f381820cec11 100644 (file)
--- a/reporting/rep707.php
+++ b/reporting/rep707.php
@@ -154,14 +154,26 @@ function print_profit_and_loss_statement()
        $salesacc = 0.0;
        $last = -1;
 
-       $types = get_account_types_all(0);
+       $accounts = get_gl_accounts_all(0);
 
-       while ($type=db_fetch($types))
+       while ($account=db_fetch($accounts))
        {
-               if (!num_accounts_in_type($type['AccountType'], $type['parent']))
+               if ($account['account_code'] == null && $account['parent'] > 0)
                        continue;
 
-               if ($type['AccountClassName'] != $classname)
+               if ($account['account_code'] != null)
+               {
+                       $per_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2);
+
+                       if ($compare == 2)
+                               $acc_balance = get_budget_trans_from_to($begin, $end, $account["account_code"], $dimension, $dimension2);
+                       else
+                               $acc_balance = get_gl_trans_from_to($begin, $end, $account["account_code"], $dimension, $dimension2);
+                       if (!$per_balance && !$acc_balance)
+                               continue;
+               }
+
+               if ($account['AccountClassName'] != $classname)
                {
                        if ($classname != '')
                        {
@@ -169,13 +181,13 @@ function print_profit_and_loss_statement()
                        }
                }
 
-               if ($type['AccountTypeName'] != $typename[$level])
+               if ($account['AccountTypeName'] != $typename[$level])
                {
                        if ($typename[$level] != '')
                        {
                                for ( ; $level >= 0, $typename[$level] != ''; $level--) 
                                {
-                                       if ($type['parent'] == $closing[$level] || $type['parent'] == $last || $type['parent'] <= 0)
+                                       if ($account['parent'] == $closing[$level] || $account['parent'] < $last)
                                        {
                                                $rep->row += 6;
                                                $rep->Line($rep->row);
@@ -215,37 +227,28 @@ function print_profit_and_loss_statement()
                                        $closeclass = false;
                                }
                        }
-                       if ($type['AccountClassName'] != $classname)
+                       if ($account['AccountClassName'] != $classname)
                        {
                                $rep->Font('bold');
-                               $rep->TextCol(0, 5, $type['AccountClassName']);
+                               $rep->TextCol(0, 5, $account['AccountClassName']);
                                $rep->Font();
                                $rep->NewLine();
                        }
                        $level++;
-                       if ($type['parent'] != $last)
-                               $last = $type['parent'];
-                       $typename[$level] = $type['AccountTypeName'];
-                       $closing[$level] = $type['parent'];
+                       if ($account['parent'] != $last)
+                               $last = $account['parent'];
+                       $typename[$level] = $account['AccountTypeName'];
+                       $closing[$level] = $account['parent'];
                        $rep->row -= 4;
-                       $rep->TextCol(0, 5, $type['AccountTypeName']);
+                       $rep->TextCol(0, 5, $account['AccountTypeName']);
                        $rep->row -= 4;
                        $rep->Line($rep->row);
                        $rep->NewLine();
                }
-               $classname = $type['AccountClassName'];
+               $classname = $account['AccountClassName'];
 
-               $accounts = get_gl_accounts_in_type($type['AccountType']);
-               while ($account=db_fetch($accounts))
+               if ($account['account_code'] != null)
                {
-                       $per_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2);
-
-                       if ($compare == 2)
-                               $acc_balance = get_budget_trans_from_to($begin, $end, $account["account_code"], $dimension, $dimension2);
-                       else
-                               $acc_balance = get_gl_trans_from_to($begin, $end, $account["account_code"], $dimension, $dimension2);
-                       if (!$per_balance && !$acc_balance)
-                               continue;
                        $per_balance *= -1;
                        $acc_balance *= -1;
                
@@ -272,20 +275,20 @@ function print_profit_and_loss_statement()
                        }
                }       
        }
-       if ($type['AccountClassName'] != $classname)
+       if ($account['AccountClassName'] != $classname)
        {
                if ($classname != '')
                {
                        $closeclass = true;
                }
        }
-       if ($type['AccountTypeName'] != $typename[$level])
+       if ($account['AccountTypeName'] != $typename[$level])
        {
                if ($typename[$level] != '')
                {
                        for ( ; $level >= 0, $typename[$level] != ''; $level--) 
                        {
-                               if ($type['parent'] == $closing[$level] || $type['parent'] == $last || $type['parent'] <= 0)
+                               if ($account['parent'] == $closing[$level] || $account['parent'] < $last)
                                {
                                        $rep->row += 6;
                                        $rep->Line($rep->row);