gl/inquiry/balance_sheet.php

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 $page_security = 'SA_GLANALYTIC';
  13 $path_to_root="../..";
  14
  15 include_once($path_to_root . "/includes/session.inc");
  16
  17 include_once($path_to_root . "/includes/date_functions.inc");
  18 include_once($path_to_root . "/includes/ui.inc");
  19 include_once($path_to_root . "/includes/data_checks.inc");
  20
  21 include_once($path_to_root . "/gl/includes/gl_db.inc");
  22
  23 $js = "";
  24 if ($use_date_picker)
  25         $js = get_js_date_picker();
  26
  27 page(_($help_context = "Balance Sheet Drilldown"), false, false, "", $js);
  28
  29 //----------------------------------------------------------------------------------------------------
  30 // Ajax updates
  31
  32 if (get_post('Show'))
  33 {
  34         $Ajax->activate('balance_tbl');
  35 }
  36
  37 if (isset($_GET["TransFromDate"]))
  38         $_POST["TransFromDate"] = $_GET["TransFromDate"];
  39 if (isset($_GET["TransToDate"]))
  40         $_POST["TransToDate"] = $_GET["TransToDate"];
  41 if (isset($_GET["AccGrp"]))
  42         $_POST["AccGrp"] = $_GET["AccGrp"];
  43
  44 //----------------------------------------------------------------------------------------------------
  45
  46 function is_of_account_type($accttype,$typeslist)
  47 {
  48         return in_array($accttype, $typeslist);
  49 }
  50
  51 function get_child_account_types($acctype)
  52 {
  53         global $parentsarr;
  54         $parentsarr = array();
  55         $childernsarr = array();
  56         $list = '';
  57         array_push($parentsarr, $acctype);
  58     while (sizeof($parentsarr)>0)
  59     {
  60                 $parent = array_pop($parentsarr);
  61                 array_push($childernsarr,$parent);
  62                 pushchilds($parent);
  63         }
  64         $list = substr($list,0,-1);
  65         return $childernsarr;
  66 }
  67
  68 function pushchilds($parent)
  69 {
  70         global $parentsarr;
  71
  72         $sql = "SELECT id FROM  ".TB_PREF."chart_types WHERE parent=".$parent;
  73         $result = db_query($sql,"Query failed");
  74         while ($myrow=db_fetch($result))
  75         {
  76                 array_push($parentsarr, $myrow['id']);
  77         }
  78 }
  79
  80 function inquiry_controls()
  81 {
  82
  83     start_table("class='tablestyle_noborder'");
  84         date_cells(_("As at:"), 'TransToDate');
  85         submit_cells('Show',_("Show"),'','', 'default');
  86     end_table();
  87
  88         hidden('TransFromDate');
  89         hidden('AccGrp');
  90
  91 }
  92
  93 function print_balance_sheet()
  94 {
  95         global $comp_path, $path_to_root, $table_style;
  96
  97         $from = begin_fiscalyear();
  98         $to = $_POST['TransToDate'];
  99
 100         $dim = get_company_pref('use_dimension');
 101         $dimension = $dimension2 = 0;
 102
 103         $classname = '';
 104         $classopen = 0.0;
 105         $classperiod = 0.0;
 106         $classclose = 0.0;
 107         $assetsopen = 0.0;
 108         $assetsperiod = 0.0;
 109         $assetsclose = 0.0;
 110         $equityopen = 0.0;
 111         $equityperiod = 0.0;
 112         $equityclose = 0.0;
 113         $lopen = 0.0;
 114         $lperiod = 0.0;
 115         $lclose = 0.0;
 116
 117         $typeopen = array(0,0,0,0,0,0,0,0,0,0);
 118         $typeperiod = array(0,0,0,0,0,0,0,0,0,0);
 119         $typeclose = array(0,0,0,0,0,0,0,0,0,0);
 120         $typename = array('','','','','','','','','','');
 121         $acctype = array('','','','','','','','','','');
 122         $closing = array(-1,-1,-1,-1,-1,-1,-1,-1,-1,-1);
 123         //$parent = array(-1,-1,-1,-1,-1,-1,-1,-1,-1,-1);
 124         $level = 0;
 125         $last = -1;
 126
 127         $closeclass = false;
 128         $ctype = 0;
 129         $convert = 1;
 130
 131         $accounts = get_gl_accounts_all(1);
 132
 133         div_start('balance_tbl');
 134
 135         start_table("width=30% $table_style");
 136
 137         while ($account=db_fetch($accounts))
 138         {
 139                 if ($account['account_code'] == null && $account['parent'] > 0)
 140                         continue;
 141
 142                 //Check for confirming the account type
 143                 if (isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0) )
 144                 {
 145                         $sub_types_arr = get_child_account_types($_POST['AccGrp']);
 146                         if (!is_of_account_type($account['AccountType'], $sub_types_arr))
 147                                 continue;
 148                 }
 149
 150                 if ($account['account_code'] != null)
 151                 {
 152                         $prev_balance = get_gl_balance_from_to("", $from, $account["account_code"], $dimension, $dimension2);
 153
 154                         $curr_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2);
 155
 156                         if (!$prev_balance && !$curr_balance)
 157                                 continue;
 158                 }
 159                 if ($account['AccountClassName'] != $classname)
 160                 {
 161                         if ($classname != '')
 162                         {
 163                                 $closeclass = true;
 164                         }
 165                 }
 166                 if ($account['AccountTypeName'] != $typename[$level])
 167                 {
 168                         if ($typename[$level] != '')
 169                         {
 170                                 for ( ; $level >= 0, $typename[$level] != ''; $level--)
 171                                 {
 172                                         if ($account['parent'] == $closing[$level] || $account['parent'] < $last || $account['parent'] <= 0 || $closeclass)
 173                                         {
 174
 175                                                 $url = "<a href='$path_to_root/gl/inquiry/balance_sheet.php?TransFromDate="
 176                                                         . $from . "&TransToDate=" . $to
 177                                                         . "&AccGrp=" . $acctype[$level] . "'>" . $typename[$level] . "</a>";
 178
 179                                                 alt_table_row_color($k);
 180                                                 label_cell($url);
 181                                                 amount_cell($typeclose[$level] * $convert);
 182                                                 end_row();
 183
 184                                                 $typeopen[$level] = $typeperiod[$level] = $typeclose[$level] = 0.0;
 185                                         }
 186                                         else
 187                                                 break;
 188                                 }
 189
 190                                 if ($closeclass)
 191                                 {
 192                                         start_row("class='inquirybg' style='font-weight:bold'");
 193                                         label_cell(_('Total') . " " . $classname);
 194                                         amount_cell($classclose * $convert, true);
 195                                         end_row();
 196
 197                                         if ($ctype == CL_EQUITY)
 198                                         {
 199                                                 $equityopen += $classopen;
 200                                                 $equityperiod += $classperiod;
 201                                                 $equityclose += $classclose;
 202                                         }
 203                                         if ($ctype == CL_LIABILITIES)
 204                                         {
 205                                                 $lopen += $classopen;
 206                                                 $lperiod += $classperiod;
 207                                                 $lclose += $classclose;
 208                                         }
 209                                         $assetsopen += $classopen;
 210                                         $assetsperiod += $classperiod;
 211                                         $assetsclose += $classclose;
 212                                         $classopen = $classperiod = $classclose = 0.0;
 213
 214                                         $closeclass = false;
 215                                 }
 216                         }
 217                         if ($account['AccountClassName'] != $classname)
 218                         {
 219                                 if (isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0))
 220                                         table_section_title($account['AccountTypeName']);
 221                                 else
 222                                         table_section_title($account['AccountClassName']);
 223                         }
 224                         $level++;
 225                         if ($account['parent'] != $last)
 226                                 $last = $account['parent'];
 227                         $typename[$level] = $account['AccountTypeName'];
 228
 229                         $acctype[$level] = $account['AccountType'];
 230
 231                         $closing[$level] = $account['parent'];
 232
 233                 }
 234                 $classname = $account['AccountClassName'];
 235                 $classtype = $account['AccountType'];
 236                 $ctype = $account['ClassType'];
 237                 $convert = get_class_type_convert($ctype);
 238
 239                 if ($account['account_code'] != null)
 240                 {
 241                         for ($i = 0; $i <= $level; $i++)
 242                         {
 243                                 $typeopen[$i] += $prev_balance;
 244                                 $typeperiod[$i] += $curr_balance;
 245                                 $typeclose[$i] = $typeopen[$i] + $typeperiod[$i];
 246                         }
 247                         $classopen += $prev_balance;
 248                         $classperiod += $curr_balance;
 249                         $classclose = $classopen + $classperiod;
 250
 251                         //Show accounts details only for drill down and direct child of Account Group
 252                         if ( isset($_POST['AccGrp']) && ($account['AccountType'] == $_POST['AccGrp']))
 253                         {
 254                                 $url = "<a href='$path_to_root/gl/inquiry/gl_account_inquiry.php?TransFromDate="
 255                                         . $from . "&TransToDate=" . $to
 256                                         . "&account=" . $account['account_code'] . "'>" . $account['account_code']
 257                                         ." ". $account['account_name'] ."</a>";
 258
 259                                 start_row("class='stockmankobg'");
 260                                 label_cell($url);
 261                                 amount_cell(($curr_balance + $prev_balance) * $convert);
 262                                 end_row();
 263                         }
 264                 }
 265         }
 266
 267         if ($account['AccountClassName'] != $classname)
 268         {
 269                 if ($classname != '')
 270                 {
 271                         $closeclass = true;
 272                 }
 273         }
 274         if ($account['AccountTypeName'] != $typename[$level])
 275         {
 276
 277                 if ($typename[$level] != '')
 278                 {
 279                         for ( ; $level >= 0, $typename[$level] != ''; $level--)
 280                         {
 281                                 if ($account['parent'] == $closing[$level] || $account['parent'] < $last || $account['parent'] <= 0 || $closeclass)
 282                                 {
 283                                         //Inside drill down, no hyperlink
 284                                         if (isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0) && ($acctype[$level] == $_POST['AccGrp']))
 285                                         {
 286                                                 start_row("class='inquirybg' style='font-weight:bold'");
 287                                                 label_cell(_('Total') . " " .$typename[$level]);
 288                                                 amount_cell($typeclose[$level] * $convert);
 289                                                 end_row();
 290                                         }
 291                                         else
 292                                         {
 293                                                 $url = "<a href='$path_to_root/gl/inquiry/balance_sheet.php?TransFromDate="
 294                                                         . $from . "&TransToDate=" . $to
 295                                                         . "&AccGrp=" . $acctype[$level] . "'>" . $typename[$level] . "</a>";
 296
 297                                                 alt_table_row_color($k);
 298                                                 label_cell($url);
 299                                                 amount_cell($typeclose[$level] * $convert);
 300                                                 end_row();
 301                                         }
 302
 303                                         $typeopen[$level] = $typeperiod[$level] = $typeclose[$level] = 0.0;
 304
 305                                 }
 306                                 else
 307                                         break;
 308
 309                         }
 310
 311                         if (($closeclass) && !(isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0)) )
 312                         {
 313                                 $calculateopen = -$assetsopen - $classopen;
 314                                 $calculateperiod = -$assetsperiod - $classperiod;
 315                                 $calculateclose = -$assetsclose  - $classclose;
 316                                 if ($ctype == CL_EQUITY)
 317                                 {
 318                                         $equityopen += $classopen;
 319                                         $equityperiod += $classperiod;
 320                                         $equityclose += $classclose;
 321                                 }
 322
 323                                 alt_table_row_color($k);
 324                                 label_cell(_('Calculated Return'));
 325                                 amount_cell($calculateclose * $convert);
 326                                 end_row();
 327
 328                                 start_row("class='inquirybg' style='font-weight:bold'");
 329                                 label_cell(_('Total') . " " . $classname);
 330                                 amount_cell(-$assetsclose * $convert);
 331                                 end_row();
 332
 333                                 if ($equityopen != 0.0 || $equityperiod != 0.0 || $equityclose != 0.0 ||
 334                                         $lopen != 0.0 || $lperiod != 0.0 || $lclose != 0.0)
 335                                 {
 336                                         alt_table_row_color($k);
 337                                         label_cell(_('Total') . " " . _('Liabilities') . _(' and ') . _('Equities'));
 338                                         amount_cell(($lclose + $equityclose + $calculateclose) * -1);
 339                                         end_row();
 340                                 }
 341                         }
 342                 }
 343         }
 344         end_table(1); // outer table
 345         div_end();
 346 }
 347
 348 //----------------------------------------------------------------------------------------------------
 349
 350 start_form();
 351
 352 inquiry_controls();
 353
 354 print_balance_sheet();
 355
 356 end_form();
 357
 358 end_page();
 359
 360 ?>
 361