gl/inquiry/profit_loss.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 = "Profit & Loss Drilldown"), false, false, "", $js);
  28
  29 //----------------------------------------------------------------------------------------------------
  30 // Ajax updates
  31
  32 if (get_post('Show'))
  33 {
  34         $Ajax->activate('pl_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["Compare"]))
  42         $_POST["Compare"] = $_GET["Compare"];
  43 if (isset($_GET["AccGrp"]))
  44         $_POST["AccGrp"] = $_GET["AccGrp"];
  45 if (isset($_GET["DrillLevel"]))
  46         $_POST["DrillLevel"] = $_GET["DrillLevel"];
  47 else
  48         $_POST["DrillLevel"] = 1; // Root level
  49
  50 //----------------------------------------------------------------------------------------------------
  51
  52 function is_of_account_type($accttype,$typeslist)
  53 {
  54         return in_array($accttype, $typeslist);
  55 }
  56
  57 function get_child_account_types($acctype)
  58 {
  59         global $parentsarr;
  60         $parentsarr = array();
  61         $childernsarr = array();
  62         $list = '';
  63         array_push($parentsarr, $acctype);
  64     while (sizeof($parentsarr)>0)
  65     {
  66                 $parent = array_pop($parentsarr);
  67                 array_push($childernsarr,$parent);
  68                 pushchilds($parent);
  69         }
  70         $list = substr($list,0,-1);
  71         return $childernsarr;
  72 }
  73
  74 function pushchilds($parent)
  75 {
  76         global $parentsarr;
  77
  78         $sql = "SELECT id FROM  ".TB_PREF."chart_types WHERE parent=".$parent;
  79         $result = db_query($sql,"Query failed");
  80         while ($myrow=db_fetch($result))
  81         {
  82                 array_push($parentsarr, $myrow['id']);
  83         }
  84 }
  85 function Achieve($d1, $d2)
  86 {
  87         if ($d1 == 0 && $d2 == 0)
  88                 return 0;
  89         elseif ($d2 == 0)
  90                 return 999;
  91         $ret = ($d1 / $d2 * 100.0);
  92         if ($ret > 999)
  93                 $ret = 999;
  94         return $ret;
  95 }
  96
  97 function inquiry_controls()
  98 {
  99     start_table("class='tablestyle_noborder'");
 100
 101     date_cells(_("From:"), 'TransFromDate', '', null, -30);
 102         date_cells(_("To:"), 'TransToDate');
 103
 104         //Compare Combo
 105         global $sel;
 106         $sel = array(_("Accumulated"), _("Period Y-1"), _("Budget"));
 107         echo "<td>Comapre To:</td>\n";
 108         echo "<td>";
 109         echo array_selector('Compare', null, $sel);
 110         echo "</td>\n";
 111
 112         submit_cells('Show',_("Show"),'','', 'default');
 113     end_table();
 114
 115         hidden('AccGrp');
 116 }
 117
 118 //----------------------------------------------------------------------------------------------------
 119
 120 function print_profit_and_loss()
 121 {
 122         global $comp_path, $path_to_root, $table_style, $sel;
 123
 124         $dim = get_company_pref('use_dimension');
 125         $dimension = $dimension2 = 0;
 126
 127         $from = $_POST['TransFromDate'];
 128         $to = $_POST['TransToDate'];
 129         $compare = $_POST['Compare'];
 130         $drilllevel = $_POST["DrillLevel"];
 131         $nextDrillLevel = $drilllevel + 1;
 132
 133         $dec = 0;
 134         $pdec = user_percent_dec();
 135
 136         if ($compare == 0 || $compare == 2)
 137         {
 138                 $end = $to;
 139                 if ($compare == 2)
 140                 {
 141                         $begin = $from;
 142                 }
 143                 else
 144                         $begin = begin_fiscalyear();
 145         }
 146         elseif ($compare == 1)
 147         {
 148                 $begin = add_months($from, -12);
 149                 $end = add_months($to, -12);
 150         }
 151
 152         $classname = '';
 153
 154         $typeper = array(0,0,0,0,0,0,0,0,0,0);
 155         $typeacc = array(0,0,0,0,0,0,0,0,0,0);
 156         $typename = array('','','','','','','','','','');
 157         $acctype = array('','','','','','','','','','');
 158         $closing = array(-1,-1,-1,-1,-1,-1,-1,-1,-1,-1);
 159         $level = 0;
 160
 161         $classper = 0.0;
 162         $classacc = 0.0;
 163         $salesper = 0.0;
 164         $salesacc = 0.0;
 165         $last = -1;
 166
 167         $closeclass = false;
 168         $convert = 1;
 169         $ctype = 0;
 170
 171         //For drill down, prepare the list of sub account types
 172         if (isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0) )
 173                 $sub_types_arr = get_child_account_types($_POST['AccGrp']);
 174
 175         $accounts = get_gl_accounts_all(0);
 176
 177         div_start('pl_tbl');
 178
 179         start_table("width=50% $table_style");
 180
 181         $tableheader =  "<tr>
 182         <td class='tableheader'>" . _("Group/Account Name") . "</td>
 183         <td class='tableheader'>" . _("Period") . "</td>
 184                 <td class='tableheader'>" . $sel[$compare] . "</td>
 185                 <td class='tableheader'>" . _("Achieved %") . "</td>
 186         </tr>";
 187
 188         while ($account=db_fetch($accounts))
 189         {
 190                 if ($account['account_code'] == null && $account['parent'] > 0)
 191                         continue;
 192
 193                 //Check for confirming the account type
 194                 if (isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0) )
 195                 {
 196                         if (!is_of_account_type($account['AccountType'], $sub_types_arr))
 197                                 continue;
 198                 }
 199
 200                 if ($account['account_code'] != null)
 201                 {
 202                         $per_balance = get_gl_trans_from_to($from, $to, $account["account_code"], $dimension, $dimension2);
 203
 204                         if ($compare == 2)
 205                                 $acc_balance = get_budget_trans_from_to($begin, $end, $account["account_code"], $dimension, $dimension2);
 206                         else
 207                                 $acc_balance = get_gl_trans_from_to($begin, $end, $account["account_code"], $dimension, $dimension2);
 208                         if (!$per_balance && !$acc_balance)
 209                                 continue;
 210                 }
 211
 212                 if ($account['AccountClassName'] != $classname)
 213                 {
 214                         if ($classname != '')
 215                         {
 216                                 $closeclass = true;
 217                         }
 218                 }
 219
 220                 if ($account['AccountTypeName'] != $typename[$level] || $closeclass )
 221                 {
 222                         if ($typename[$level] != '')
 223                         {
 224                                 for ( ; $level >= 0, $typename[$level] != ''; $level--)
 225                                 {
 226                                         if ($account['parent'] == $closing[$level] || $account['parent'] < $last || $account['parent'] <= 0 || $closeclass)
 227                                         {
 228                                                 // Display Groups only of Specific Drill Level
 229                                                 if ($level == $drilllevel && ($typeper[$level] != 0 || $typeacc[$level] != 0 ))
 230                                                 {
 231                                                         $url = "<a href='$path_to_root/gl/inquiry/profit_loss.php?TransFromDate="
 232                                                                 . $from . "&TransToDate=" . $to . "&Compare=" . $compare
 233                                                                 . "&AccGrp=" . $acctype[$level] . "&DrillLevel=".$nextDrillLevel."'>" . $typename[$level] ."</a>";
 234
 235                                                         alt_table_row_color($k);
 236                                                         label_cell($url);
 237                                                         amount_cell($typeper[$level] * $convert);
 238                                                         amount_cell($typeacc[$level] * $convert);
 239                                                         amount_cell(Achieve($typeper[$level], $typeacc[$level]));
 240                                                         end_row();
 241                                                 }
 242
 243                                                 $typeper[$level] = $typeacc[$level] = 0.0;
 244                                         }
 245                                         else
 246                                                 break;
 247                                 }
 248                                 if ($closeclass)
 249                                 {
 250                                         start_row("class='inquirybg' style='font-weight:bold'");
 251                                         label_cell(_('Total') . " " . $classname);
 252                                         amount_cell($classper * $convert);
 253                                         amount_cell($classacc * $convert);
 254                                         amount_cell(Achieve($classper, $classacc));
 255                                         end_row();
 256
 257                                         $salesper += $classper;
 258                                         $salesacc += $classacc;
 259                                         $classper = $classacc = 0.0;
 260
 261                                         $closeclass = false;
 262                                 }
 263                         }
 264                         if ($account['AccountClassName'] != $classname)
 265                         {
 266                                 if (isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0))
 267                                         table_section_title($account['AccountTypeName'],4);
 268                                 else
 269                                         table_section_title($account['AccountClassName'],4);
 270
 271                                 echo $tableheader;
 272                         }
 273                         $level++;
 274                         if ($account['parent'] != $last)
 275                                 $last = $account['parent'];
 276                         $typename[$level] = $account['AccountTypeName'];
 277                         $acctype[$level] = $account['AccountType'];
 278                         $closing[$level] = $account['parent'];
 279                 }
 280                 $classname = $account['AccountClassName'];
 281                 $ctype = $account['ClassType'];
 282                 $convert = get_class_type_convert($ctype);
 283
 284                 if ($account['account_code'] != null)
 285                 {
 286                         //$per_balance *= -1;
 287                         //$acc_balance *= -1;
 288
 289                         for ($i = 0; $i <= $level; $i++)
 290                         {
 291                                 $typeper[$i] += $per_balance;
 292                                 $typeacc[$i] += $acc_balance;
 293                         }
 294                         $classper += $per_balance;
 295                         $classacc += $acc_balance;
 296
 297                         //Show accounts details only for drill down and direct child of Account Group
 298                         if ( isset($_POST['AccGrp']) && ($account['AccountType'] == $_POST['AccGrp']))
 299                         {
 300                                 $url = "<a href='$path_to_root/gl/inquiry/gl_account_inquiry.php?TransFromDate="
 301                                         . $from . "&TransToDate=" . $to
 302                                         . "&account=" . $account['account_code'] . "'>" . $account['account_code']
 303                                         ." ". $account['account_name'] ."</a>";
 304
 305                                 start_row("class='stockmankobg'");
 306                                 label_cell($url);
 307                                 amount_cell($per_balance * $convert);
 308                                 amount_cell($acc_balance * $convert);
 309                                 amount_cell(Achieve($per_balance, $acc_balance));
 310                                 end_row();
 311                         }
 312                 }
 313         }
 314         if ($account['AccountClassName'] != $classname)
 315         {
 316                 if ($classname != '')
 317                 {
 318                         $closeclass = true;
 319                 }
 320         }
 321         if ($account['AccountTypeName'] != $typename[$level])
 322         {
 323                 if ($typename[$level] != '')
 324                 {
 325                         for ( ; $level >= 0, $typename[$level] != ''; $level--)
 326                         {
 327                                 if ($account['parent'] == $closing[$level] || $account['parent'] < $last || $account['parent'] <= 0 || $closeclass)
 328                                 {
 329
 330                                         //Inside drill down, Show summary of the specific account group
 331                                         if (isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0) && ($acctype[$level] == $_POST['AccGrp']))
 332                                         {
 333                                                 start_row("class='inquirybg' style='font-weight:bold'");
 334                                                 label_cell(_('Total') . " " .$typename[$level]);
 335                                                 amount_cell($typeper[$level] * $convert);
 336                                                 amount_cell($typeacc[$level] * $convert);
 337                                                 amount_cell(Achieve($typeper[$level], $typeacc[$level]));
 338                                                 end_row();
 339                                         }
 340                                          // Display Groups only of Specific Drill Level
 341                                         elseif ($level == $drilllevel  && ($typeper[$level] != 0 || $typeacc[$level] != 0 ))
 342                                         {
 343                                                 $url = "<a href='$path_to_root/gl/inquiry/profit_loss.php?TransFromDate="
 344                                                         . $from . "&TransToDate=" . $to . "&Compare=" . $compare
 345                                                         . "&AccGrp=" . $acctype[$level] . "&DrillLevel=".$nextDrillLevel."'>" . $typename[$level] ."</a>";
 346
 347                                                 alt_table_row_color($k);
 348                                                 label_cell($url);
 349                                                 amount_cell($typeper[$level] * $convert);
 350                                                 amount_cell($typeacc[$level] * $convert);
 351                                                 amount_cell(Achieve($typeper[$level], $typeacc[$level]));
 352                                                 end_row();
 353                                         }
 354
 355                                         $typeper[$level] = $typeacc[$level] = 0.0;
 356                                 }
 357                                 else
 358                                         break;
 359                         }
 360
 361                         if (($closeclass) && !(isset($_POST['AccGrp']) && (strlen($_POST['AccGrp']) > 0)) )
 362                         {
 363                                 $calculateper = $salesper + $classper;
 364                                 $calculateacc = $salesacc + $classacc;
 365
 366                                 start_row("class='inquirybg' style='font-weight:bold'");
 367                                 label_cell(_('Total') . " " . $classname);
 368                                 amount_cell($classper * $convert);
 369                                 amount_cell($classacc * $convert);
 370                                 amount_cell(Achieve($classper, $classacc));
 371                                 end_row();
 372
 373                                 start_row("class='inquirybg' style='font-weight:bold'");
 374                                 label_cell(_('Calculated Return'));
 375                                 amount_cell($calculateper *-1);
 376                                 amount_cell($calculateacc * -1);
 377                                 amount_cell(Achieve($calculateper, $calculateacc));
 378                                 end_row();
 379
 380                         }
 381                 }
 382         }
 383         end_table(1); // outer table
 384         div_end();
 385 }
 386
 387 //----------------------------------------------------------------------------------------------------
 388
 389 start_form();
 390
 391 inquiry_controls();
 392
 393 print_profit_and_loss();
 394
 395 end_form();
 396
 397 end_page();
 398
 399 ?>
 400