includes/dashboard.inc

   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 //$colors = array('#008cc9','#4db625','#ef5500','#eef100','#05c6e6', '#5ee66a'); // Current in Use
  13 $colors = Chart::$palette;
  14 $style = "<style>
  15 div.square {
  16         display: inline-block;
  17         width: 200px;
  18         height:40px;
  19         padding: 3px 0 0 5px;
  20         font-size:12px;
  21         font-weight:normal;
  22         text-align: left;
  23         line-height: 1.2;
  24         color:#fff;
  25         border-radius:6px;
  26 }
  27
  28 div.square1 {
  29         background:{$colors[1]};
  30 }
  31 div.square2 {
  32         background:{$colors[2]};
  33 }
  34
  35 .span1 {
  36         width:200px;
  37         padding:0;
  38         margin:0;
  39         font-size:16px;
  40         font-weight:bold;
  41         text-align: center;
  42 }
  43 </style>";
  44
  45 function dashboard($sel_app)
  46 {
  47         global  $style;
  48         if (is_object($sel_app) && !$_SESSION["wa_current_user"]->check_application_access($selected_app))
  49                 return;
  50         echo $style;
  51         echo "<script>
  52         function chart_update(el, id)
  53         {
  54                 var xhr = XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject('Microsoft.XMLHTTP');
  55                 xhr.onreadystatechange=function()
  56                 {
  57                         if (xhr.readyState==4 && xhr.status==200)
  58                         {
  59                                 var xxx = document.createElement('div');
  60                                 xxx.innerHTML = xhr.responseText;
  61                                 document.getElementById(id).innerHTML = xxx.querySelector('#'+id).innerHTML;
  62                         }
  63                 }
  64                 xhr.open('POST', location.pathname, true);
  65                 xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
  66                 xhr.send('id='+id+'&'+el.id+'='+el.value+'&sel_app=$sel_app');
  67         }
  68         </script>";
  69         //check_for_overdue_recurrent_invoices();
  70         if ($sel_app == "orders")
  71                 display_customer_topten();
  72         elseif ($sel_app == "AP")
  73                 display_supplier_topten();
  74         elseif ($sel_app == "stock")
  75                 display_stock_topten();
  76         elseif ($sel_app == "manuf")
  77                 display_stock_topten(1);
  78         elseif ($sel_app == "assets")
  79                 display_stock_topten(2);
  80         elseif ($sel_app == "proj")
  81                 display_dimension_topten();
  82         elseif ($sel_app == "GL")
  83                 display_gl_info();
  84         else
  85                 display_all();
  86 }
  87
  88 function display_title($title, $id=false, $type = false, $per = '', $num = false)
  89 {
  90         br();
  91         start_form();
  92         echo "<div>";
  93         echo "<span class='headingtext'>$title</span>";
  94         if ($id)
  95         {
  96                 /*
  97                 global $Ajax;
  98                 echo "<span style='margin-left:10px;text-align:right;'>";
  99                 $options = array('select_submit' => true );
 100                 if (!empty($per))
 101                 {
 102                         $name = "per_$id";
 103                         $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8");
 104                         echo array_selector($name, $per, $sel1, $options);
 105                         echo " $per\n";
 106                 }
 107                 else
 108                 {
 109                         $name = "select_$id";
 110                         $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables"));
 111                         echo array_selector($name, $type, $sel, $options);
 112                 }
 113                 if (list_updated($name))
 114                         $Ajax->activate($id);
 115                 echo "</span>";
 116                 */
 117                 echo "<span style='margin-left:10px;text-align:right;'>";
 118                 if (!empty($per))
 119                 {
 120                         $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8");
 121                         echo "<select id='per_$id' name='per_$id' style='border:1px solid #bbb;color:#555;' onchange='chart_update(this, \"$id\");'>";
 122                         foreach ($sel1 as $i => $s)
 123                         {
 124                                 echo "<option value='$i'";
 125                                 if ($num && $i == $num)
 126                                         echo " selected";
 127                                 echo ">$s</option>";
 128                         }
 129                         echo "</select> $per\n";
 130                 }
 131                 else
 132                 {
 133                         $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables"));
 134                         echo "<select id='select_$id' name='select_$id' style='border:1px solid #bbb;color:#555;' onchange='chart_update(this, \"$id\");'>";
 135                         foreach ($sel as $i => $s)
 136                         {
 137                                 echo "<option value='$i'";
 138                                 if ($type && $i == $type)
 139                                         echo " selected";
 140                                 echo ">$s</option>";
 141                         }
 142                         echo "</select>\n";
 143                 }
 144                 echo "</span>";
 145         }
 146         echo "</div>\n";
 147         end_form();
 148         br();
 149 }
 150
 151 function table($width='100%', $twidth=false)
 152 {
 153         $tstyle = ($twidth != false) ? "style='width:$twidth;'" : "";
 154         echo "<table width='$width'><tr valign=top><td align='center' $tstyle>";
 155 }
 156
 157 function td($width=false)
 158 {
 159         $style = ($width != false) ? "style='width:$width;'" : "";
 160         echo "</td><td align='center' $style>\n";
 161 }
 162
 163 function table_end()
 164 {
 165         echo "</td></tr></table>\n";
 166 }
 167
 168 function headers($labels)
 169 {
 170         echo "<tr>\n";
 171         foreach ($labels as $label)
 172                 echo "<td class='tableheader'>$label</td>\n";
 173         echo "</tr>\n";
 174 }
 175
 176 function display_customer_topten()
 177 {
 178         table('100%', '25%');
 179         echo "<div class='square square1'>"._('Customers')."<p class='span1'>";
 180         echo get_num_customers()."</p></div>";
 181         td('25%');
 182         echo "<div class='square square1'>"._('Branches')."<p class='span1'>";
 183         echo get_num_branches()."</p></div>";
 184         td('25%');
 185         echo "<div class='square square1'>"._('Salesmen')."<p class='span1'>";
 186         echo get_num_salesmen()."</p></div>";
 187         td('25%');
 188         echo "<div class='square square2'>"._('Overdue Invoices')."<p class='span1'>";
 189         echo get_num_overdue_sales()."</p></div>";
 190         table_end();
 191         $pg = new chart('horizontalBar', 'c1');
 192         if (isset($_POST['select_c1']))
 193                 $pg->type = $_POST['select_c1'];
 194         $today = calc_today();
 195         table('100%', '50%');
 196         $title = customer_top($today, 10, 66, $pg);
 197         $total = customer_aging($today, 66);
 198         td('50%');
 199         source_graphic($today, $title[0], _("Customer"), $pg);
 200         br(3);
 201         echo "<div class='square square1'>"._('Sales Total')."<p class='span1'>";
 202         echo number_format2($title[1])."</p></div>";
 203         br(3);
 204         echo "<div class='square square1'>"._('Aging Total')."<p class='span1'>";
 205         echo number_format2($total)."</p></div>";
 206         table_end();
 207         table('100%', '100%');
 208         customer_recurrent_invoices($today);
 209         customer_trans($today);
 210         table_end();
 211 }
 212
 213 function display_supplier_topten()
 214 {
 215         table('100%', '25%');
 216         echo "<div class='square square2'>"._('Suppliers')."<p class='span1'>";
 217         echo get_num_suppliers()."</p></div>";
 218         td('25%');
 219         echo "<div class='square square2'>"._('New Orders')."<p class='span1'>";
 220         echo get_new_purch_orders()."</p></div>";
 221         td('25%');
 222         echo "<div class='square square2'>"._('Invoices')."<p class='span1'>";
 223         echo get_num_supp_invoices()."</p></div>";
 224         td('25%');
 225         echo "<div class='square square2'>"._('Overdue Invoices')."<p class='span1'>";
 226         echo get_num_overdue_purch()."</p></div>";
 227         table_end();
 228         $pg = new chart('horizontalBar', 's1');
 229         if (isset($_POST['select_s1']))
 230                 $pg->type = $_POST['select_s1'];
 231         $today = calc_today();
 232         table('100%', '50%');
 233         $title = supplier_top($today, 10, 66, $pg);
 234         $total = supplier_aging($today, 66);
 235         td('50%');
 236         source_graphic($today, $title[0], _("Supplier"), $pg);
 237         br(3);
 238         echo "<div class='square square2'>"._('Purchable Total')."<p class='span1'>";
 239         echo number_format2($title[1])."</p></div>";
 240         br(3);
 241         echo "<div class='square square2'>"._('Aging Total')."<p class='span1'>";
 242         echo number_format2($total)."</p></div>";
 243         table_end();
 244         table('100%', '100%');
 245         supplier_trans($today);
 246         table_end();
 247 }
 248
 249 function display_stock_topten($type=0)
 250 {
 251         if ($type == 0)
 252         {
 253                 table('100%', '25%');
 254                 echo "<div class='square square1'>"._('Items')."<p class='span1'>";
 255                 echo get_num_items()."</p></div>";
 256                 td('25%');
 257                 echo "<div class='square square1'>"._('Locations')."<p class='span1'>";
 258                 echo get_num_locations()."</p></div>";
 259                 td('25%');
 260                 echo "<div class='square square1'>"._('Kits')."<p class='span1'>";
 261                 echo get_num_kits()."</p></div>";
 262                 td('25%');
 263                 echo "<div class='square square2'>"._('Below Reorder Level')."<p class='span1'>";
 264                 echo get_num_reorder()."</p></div>";
 265                 table_end();
 266         }
 267         elseif ($type == 1)
 268         {
 269                 table('100%', '25%');
 270                 echo "<div class='square square1'>"._('Assembled Items')."<p class='span1'>";
 271                 echo get_num_assembled()."</p></div>";
 272                 td('25%');
 273                 echo "<div class='square square1'>"._('Manufactured Items')."<p class='span1'>";
 274                 echo get_num_manufactured()."</p></div>";
 275                 td('25%');
 276                 echo "<div class='square square1'>"._('Work Centres')."<p class='span1'>";
 277                 echo get_num_workcentres()."</p></div>";
 278                 td('25%');
 279                 echo "<div class='square square1'>"._('Open Workorders')."<p class='span1'>";
 280                 echo get_open_workorders()."</p></div>";
 281                 table_end();
 282         }
 283         elseif ($type == 2)
 284         {
 285                 table('100%', '25%');
 286                 echo "<div class='square square1'>"._('Fixed Assets')."<p class='span1'>";
 287                 echo get_num_fixed_assets()."</p></div>";
 288                 td('25%');
 289                 echo "<div class='square square1'>"._('Locations')."<p class='span1'>";
 290                 echo get_num_fixed_locations()."</p></div>";
 291                 td('25%');
 292                 echo "<div class='square square1'>"._('Categories')."<p class='span1'>";
 293                 echo get_num_fixed_categories()."</p></div>";
 294                 td('25%');
 295                 echo "<div class='square square1'>"._('Fixed Asset Classes')."<p class='span1'>";
 296                 echo get_num_fixed_classes()."</p></div>";
 297                 table_end();
 298         }
 299         $pg = new chart('bar', 'i1');
 300         if (isset($_POST['select_i1']))
 301                 $pg->type = $_POST['select_i1'];
 302         table('100%', '50%');
 303         $today = calc_today();
 304         $title = stock_top($today, 5, 66, $type, $pg);
 305         table('50%', '25%');
 306         echo "<div class='square square1'>"._('Total Sales')."<p class='span1'>";
 307         echo number_format2($title[1])."</p></div>";
 308         td('25%');
 309         echo "<div class='square square2'>"._('Total Costs')."<p class='span1'>";
 310         echo number_format2($title[2])."</p></div>";
 311         table_end();
 312         td('50%');
 313         if ($type == 2)
 314                 $source = _("Fixed Assets");
 315         elseif ($type == 1)
 316                 $source = _("Manufacturing");
 317         else
 318                 $source = _("Items");
 319         source_graphic($today, $title[0], $source, $pg);
 320         echo "<div class='square'><span>"._('Total Results')."<br />";
 321         echo number_format2($title[3])."</div>";
 322         table_end();
 323         table('100%', '100%');
 324         stock_below_reorder($today, $type);
 325         table_end();
 326 }
 327
 328 function display_dimension_topten()
 329 {
 330         table('100%', '25%');
 331         echo "<div class='square square1'>"._('Dimensions')."<p class='span1'>";
 332         echo get_num_dimensions()."</p></div>";
 333         td('25%');
 334         echo "<div class='square square1'>"._('Type 2 Dimensions')."<p class='span1'>";
 335         echo get_dim_type2()."</p></div>";
 336         td('25%');
 337         $res = get_dim_total();
 338         $s = $res[0] < 0.0 ? 2 : 1;
 339         echo "<div class='square square{$s}'>"._('Dimensions Total Balance')."<p class='span1'>";
 340         echo number_format2($res[0])."</p></div>";
 341         td('25%');
 342         $s = $res[1] < 0.0 ? 2 : 1;
 343         echo "<div class='square square{$s}'>"._('Dimensions Total Result')."<p class='span1'>";
 344         echo number_format2($res[1])."</p></div>";
 345         table_end();
 346         $pg = new chart('bar', 'd1');
 347         if (isset($_POST['select_d1']))
 348                 $pg->type = $_POST['select_d1'];
 349         table('100%', '50%');
 350         $today = calc_today();
 351         $title = dimension_top($today, 5, 66, $pg);
 352         td('50%');
 353         source_graphic($today, $title, _("Dimension"), $pg);
 354         table_end();
 355 }
 356
 357 function display_gl_info()
 358 {
 359         $today = calc_today();
 360         table('100%', '25%');
 361         echo "<div class='square square1'>"._('Receivables')."<p class='span1'>";
 362         echo number_format2(customer_balance($today))."</p></div>";
 363         td('25%');
 364         echo "<div class='square square2'>"._('Payables')."<p class='span1'>";
 365         echo number_format2(-supplier_balance($today))."</p></div>";
 366         td('25%');
 367         echo "<div class='square square1'>"._('Todays Deposits')."<p class='span1'>";
 368         echo number_format2(get_today_deposits($today))."</p></div>";
 369         td('25%');
 370         echo "<div class='square square2'>"._('Todays Payments')."<p class='span1'>";
 371         echo number_format2(get_today_payments($today))."</p></div>";
 372         table_end();
 373         $pg = new chart('halfDonut', 'g1');
 374         if (isset($_POST['select_g1']))
 375                 $pg->type = $_POST['select_g1'];
 376         table('100%', '50%');
 377         $title = gl_top($today, 66, $pg);
 378         source_graphic($today, $title, _("Class"), $pg);
 379         gl_month_performance($today, 66, 5);
 380         td('50%');
 381         gl_week_performance($today, 66, 4);
 382         cash_flow($today);
 383         bank_balance($today, 66);
 384         table_end();
 385 }
 386
 387 function display_all()
 388 {
 389         $today = calc_today();
 390
 391         table('100%', '25%');
 392         echo "<div class='square square1'>"._('Users')."<p class='span1'>";
 393         echo get_num_users()."</p></div>";
 394         td('25%');
 395         echo "<div class='square square1'>"._('Extensions')."<p class='span1'>";
 396         echo get_num_extensions()."</p></div>";
 397         td('25%');
 398         echo "<div class='square square1'>"._('Languages')."<p class='span1'>";
 399         echo get_num_languages()."</p></div>";
 400         td('25%');
 401         echo "<div class='square square1'>"._('Database Size')."<p class='span1'>";
 402         echo get_database_size()."</p></div>";
 403         table_end();
 404         $pg = new chart('horizontalBar', 'c2');
 405         if (isset($_POST['select_c2']))
 406                 $pg->type = $_POST['select_c2'];
 407         table('100%', '50%');
 408         $title = customer_top($today, 3, 66, $pg);
 409         source_graphic($today, $title[0], _("Customer"), $pg);
 410         $pg = new chart('horizontalBar', 's2');
 411         if (isset($_POST['select_s2']))
 412                 $pg->type = $_POST['select_s2'];
 413         $title = supplier_top($today, 3, 66, $pg);
 414         source_graphic($today, $title[0], _("Supplier"), $pg);
 415         $pg = new chart('bar', 'i2');
 416         if (isset($_POST['select_i2']))
 417                 $pg->type = $_POST['select_i2'];
 418         $title = stock_top($today, 3, 66, 0, $pg);
 419         source_graphic($today, $title[0], _("Items"), $pg);
 420         td('50%');
 421         dimension_top($today, 3, 66);
 422         $pg = new chart('pie', 'd2');
 423         if (isset($_POST['select_d2']))
 424                 $pg->type = $_POST['select_d2'];
 425         $title = gl_top($today, 66, $pg);
 426         source_graphic($today, $title, _("Class"), $pg);
 427         stock_top($today, 3, 66, 2);
 428         stock_top($today, 3, 66, 1);
 429         table_end();
 430 }
 431
 432 function customer_top($today, $limit=10, $width='33', &$pg=null)
 433 {
 434         $begin = date2sql(begin_fiscalyear());
 435         $today = date2sql($today);
 436         $sql = "SELECT SUM((ov_amount + ov_discount) * rate * IF(trans.type = ".ST_CUSTCREDIT.", -1, 1)) AS total,d.debtor_no, d.name FROM ".TB_PREF."debtor_trans AS trans, ".TB_PREF."debtors_master AS d WHERE trans.debtor_no=d.debtor_no
 437                 AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_CUSTCREDIT.")
 438                 AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by d.debtor_no ORDER BY total DESC, d.debtor_no
 439                 LIMIT $limit";
 440         $result = db_query($sql);
 441         $title = sprintf(_("Top %s customers in fiscal year"), $limit);
 442         display_title($title);
 443         $th = array(_("Customer"), _("Amount"));
 444         start_table(TABLESTYLE, "width='$width%'");
 445         headers($th);
 446         check_page_security('SA_SALESTRANSVIEW');
 447         $k = 0; //row colour counter
 448         $i = 1;
 449         $names = $totals = array();
 450         $sales = 0;
 451         while ($myrow = db_fetch($result))
 452         {
 453                 $sales += round($myrow['total']);
 454                 if ($i++ < $limit)
 455                 {
 456                         alt_table_row_color($k);
 457                         $name = $myrow["debtor_no"]." ".htmlspecialchars_decode($myrow["name"]);
 458                         label_cell($name);
 459                         amount_cell($myrow['total']);
 460                         if ($pg != null)
 461                         {
 462                                 $names[] = $name;
 463                                 $totals[] = round($myrow['total']);
 464                         }
 465                         end_row();
 466                 }
 467         }
 468         if ($pg != null)
 469         {
 470                 $pg->setLabels($names);
 471                 $pg->addSerie(_('Sales'), $totals);
 472         }
 473         end_table(2);
 474         return array($title, $sales);
 475 }
 476
 477 function supplier_top($today, $limit=10, $width='33', &$pg=null)
 478 {
 479         $begin = date2sql(begin_fiscalyear());
 480         $today = date2sql($today);
 481         $sql = "SELECT SUM((trans.ov_amount + trans.ov_discount) * rate) AS total, s.supplier_id, s.supp_name FROM
 482                 ".TB_PREF."supp_trans AS trans, ".TB_PREF."suppliers AS s WHERE trans.supplier_id=s.supplier_id
 483                 AND (trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.")
 484                 AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by s.supplier_id ORDER BY total DESC, s.supplier_id
 485                 LIMIT $limit";
 486         $result = db_query($sql);
 487         $title = sprintf(_("Top %s suppliers in fiscal year"), $limit);
 488         display_title($title);
 489         $th = array(_("Supplier"), _("Amount"));
 490         start_table(TABLESTYLE, "width='$width%'");
 491         headers($th);
 492         check_page_security('SA_SUPPTRANSVIEW');
 493         $k = 0; //row colour counter
 494         $i = 1;
 495         $names = $totals = array();
 496         $total = 0;
 497         while ($myrow = db_fetch($result))
 498         {
 499                 $total += $myrow['total'];
 500                 if ($i++ < $limit)
 501                 {
 502                         alt_table_row_color($k);
 503                         $name = $myrow["supplier_id"]." ".htmlspecialchars_decode($myrow["supp_name"]);
 504                         label_cell($name);
 505                         amount_cell($myrow['total']);
 506                         if ($pg != null)
 507                         {
 508                                 $names[] = $name;
 509                                 $totals[] = round($myrow['total']);
 510                         }
 511                         end_row();
 512                 }
 513         }
 514         if ($pg != null)
 515         {
 516                 $pg->setLabels($names);
 517                 $pg->addSerie(_('Purchases'), $totals);
 518         }
 519         end_table(2);
 520         return array($title, $total);
 521 }
 522
 523 function stock_top($today, $limit=10, $width='33', $type=0, &$pg=null)
 524 {
 525         if ($type == 2)
 526                 $sec = 'SA_ASSETSANALYTIC';
 527         elseif ($type == 1)
 528                 $sec = 'SA_WORKORDERANALYTIC';
 529         else
 530                 $sec = 'SA_ITEMSTRANSVIEW';
 531         $begin = date2sql(begin_fiscalyear());
 532         $today = date2sql($today);
 533         if ($type == 0)
 534         {
 535                 $sql = "SELECT SUM((trans.unit_price * trans.quantity) * d.rate) AS total, s.stock_id, s.description,
 536                         SUM(trans.quantity) AS qty, SUM((s.material_cost + s.overhead_cost + s.labour_cost) * trans.quantity) AS costs FROM
 537                         ".TB_PREF."debtor_trans_details AS trans, ".TB_PREF."stock_master AS s, ".TB_PREF."debtor_trans AS d
 538                         WHERE trans.stock_id=s.stock_id AND trans.debtor_trans_type=d.type AND trans.debtor_trans_no=d.trans_no
 539                         AND (d.type = ".ST_SALESINVOICE." OR d.type = ".ST_CUSTCREDIT.") ";
 540         }
 541         else
 542         {
 543                 $sql = "SELECT SUM(m.qty * (s.material_cost + s.labour_cost + s.overhead_cost)) AS total, s.stock_id, s.description,
 544                         SUM(qty) AS qty FROM ".TB_PREF."stock_master AS s, ".TB_PREF."stock_moves AS m
 545                         WHERE s.stock_id=m.stock_id ";
 546                 if ($type == 1)
 547                         $sql .= "AND s.mb_flag='M' AND m.type <> ".ST_CUSTDELIVERY." AND m.type <> ".ST_CUSTCREDIT." ";
 548                 elseif ($type == 2)
 549                         $sql .= "AND s.mb_flag='F' ";
 550         }
 551         if ($type != 2)
 552                 $sql .= "AND tran_date >= '$begin' ";
 553         $sql .= "AND tran_date <= '$today' GROUP by s.stock_id ORDER BY total DESC, s.stock_id LIMIT $limit";
 554         $result = db_query($sql);
 555         if ($type == 1)
 556                 $title = sprintf(_("Top %s Manufactured Items in fiscal year"), $limit);
 557         elseif ($type == 2)
 558                 $title = sprintf(_("Top %s Fixed Assets"), $limit);
 559         else
 560                 $title = sprintf(_("Top %s Sold Items in fiscal year"), $limit);
 561         display_title($title);
 562         if ($type == 0)
 563                 $th = array(_("Item"), _("Sales"), _("Costs"), _("Results"), _("Quantity"));
 564         else
 565                 $th = array(_("Item"), _("Amount"), _("Quantity"));
 566         start_table(TABLESTYLE, "width='$width%'");
 567         headers($th);
 568         check_page_security($sec);
 569         $k = 0; //row colour counter
 570         $i = 1;
 571         $names = $totals = $costs = $results = array();
 572         $sales = $cost = $res = 0;
 573         while ($myrow = db_fetch($result))
 574         {
 575                 $sales += $myrow['total'];
 576                 if ($type == 0)
 577                 {
 578                         $cost += $myrow['costs'];
 579                         $res += ($myrow['total'] - $myrow['costs']);
 580                 }
 581                 if ($i++ < $limit)
 582                 {
 583                         alt_table_row_color($k);
 584                         $name = $myrow["description"];
 585                         label_cell($name);
 586                         amount_cell($myrow['total']);
 587                         if ($type == 0)
 588                         {
 589                                 amount_cell($myrow['costs']);
 590                                 amount_cell($myrow['total'] - $myrow['costs']);
 591                         }
 592                         qty_cell($myrow['qty']);
 593                         if ($pg != NULL)
 594                         {
 595                                 $names[] = $name;
 596                                 $totals[] = round($myrow['total']);
 597                                 if ($type == 0)
 598                                 {
 599                                         $costs[] = round($myrow['costs']);
 600                                         $results[] = round($myrow['total'] - $myrow['costs']);
 601                                 }
 602                         }
 603                         end_row();
 604                 }
 605         }
 606         if ($pg != null)
 607         {
 608                 $pg->setLabels($names);
 609                 $pg->addSerie(_('Sales'), $totals);
 610                 if ($type == 0)
 611                 {
 612                         $pg->addSerie(_('Costs'), $costs);
 613                         $pg->addSerie(_('Results'), $results, 8);
 614                 }
 615         }
 616         end_table(2);
 617         return array($title, $sales, $cost, $res);
 618 }
 619
 620 function dimension_top($today, $limit=10, $width='33', &$pg=null)
 621 {
 622         $begin = date2sql(begin_fiscalyear());
 623         $today = date2sql($today);
 624
 625         $sql = "SELECT SUM(IF(c.ctype > 3, -amount, 0)) AS result, SUM(IF(c.ctype < 4, amount, 0)) AS balance, d.reference, d.name FROM ".TB_PREF."gl_trans g
 626                 LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code
 627                 LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type
 628                 LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id)
 629                 LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid
 630                 WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
 631                 AND tran_date <= '$today'  AND d.reference IS NOT NULL GROUP BY d.reference ORDER BY d.reference DESC LIMIT $limit";
 632         $result = db_query($sql, "Transactions could not be calculated");
 633         $title = sprintf(_("Top %s Dimensions in fiscal year"), $limit);
 634         display_title($title);
 635         $th = array(_("Dimension"), _("Balance"), _("Result"));
 636         start_table(TABLESTYLE, "width='$width%'");
 637         headers($th);
 638         check_page_security('SA_DIMTRANSVIEW');
 639         $k = 0; //row colour counter
 640         $names = $balances = $results = array();
 641         while ($myrow = db_fetch($result))
 642         {
 643                 alt_table_row_color($k);
 644                 $name = $myrow['reference']." ".$myrow["name"];
 645                 label_cell($name);
 646                 amount_cell($myrow['balance']);
 647                 amount_cell($myrow['result']);
 648                 if ($pg != null)
 649                 {
 650                         $names[] = $name;
 651                         $balances[] = round(abs($myrow['balance']));
 652                         $results[] = round(abs($myrow['result']));
 653                 }
 654                 end_row();
 655         }
 656         if ($pg != null)
 657         {
 658                 $pg->setLabels($names);
 659                 $pg->setValues(true);
 660                 $pg->addSerie(_('Balance'), $balances);
 661                 $pg->addSerie(_('Result'), $results);
 662         }
 663         end_table(2);
 664         return $title;
 665 }
 666
 667 function gl_top($today, $width='33', &$pg=null)
 668 {
 669         $begin = date2sql(begin_fiscalyear());
 670         $today = date2sql($today);
 671         $sql = "SELECT SUM(IF(c.ctype > 3, -amount, amount)) AS total, c.class_name, c.ctype FROM
 672                 ".TB_PREF."gl_trans,".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
 673                 ".TB_PREF."chart_class AS c WHERE
 674                 account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
 675                 AND IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
 676                 AND tran_date <= '$today' GROUP BY c.cid ORDER BY c.cid";
 677         $result = db_query($sql, "Transactions could not be calculated");
 678         $title = _("Class Balances");
 679         display_title($title);
 680         start_table(TABLESTYLE2, "width='$width%'");
 681         check_page_security('SA_GLANALYTIC');
 682         $total = 0;
 683         $names = $totals = array();
 684         while ($myrow = db_fetch($result))
 685         {
 686                 if ($myrow['ctype'] > 3)
 687                 {
 688                         $total += round($myrow['total']);
 689                         if ($pg != null)
 690                         {
 691                                 $names[] = $myrow['class_name'];
 692                                 $totals[] = round(abs($myrow['total']));
 693                         }
 694                 }
 695                 label_row($myrow['class_name'], number_format2($myrow['total'], user_price_dec()),
 696                         "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
 697         }
 698         $calculated = _("Calculated Return");
 699         label_row("&nbsp;", "");
 700         label_row($calculated, number_format2($total, user_price_dec()),
 701                 "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
 702         if ($pg != null)
 703         {
 704                 $names[] = $calculated;
 705                 $totals[] = $total;
 706                 $pg->setLabels($names);
 707                 $pg->addSerie(_('Totals'), $totals);
 708                 $pg->setValues(true);
 709                 $pg->setDTitle(number_format2($total));
 710         }
 711         end_table(2);
 712         return $title;
 713 }
 714
 715 function gl_week_performance($today, $width="33", $weeks=4)
 716 {
 717         global $SysPrefs;
 718
 719         $pg = new chart('bar', 'g3');
 720         if (isset($_POST['select_g3']))
 721                 $pg->type = $_POST['select_g3'];
 722         if (isset($_POST['per_g3']))
 723                 $weeks = $_POST['per_g3'];
 724         $begin = date2sql(begin_fiscalyear());
 725         $today1 = date2sql($today);
 726         $sep = $SysPrefs->dateseps[user_date_sep()];
 727         $sql = "SELECT week_name, sales, costs
 728                 FROM(SELECT DATE_FORMAT(tran_date, '%X{$sep}%V') AS week_name,
 729                         SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales,
 730                         SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM
 731                         ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
 732                         ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6)
 733                         AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
 734                         AND tran_date >= '$begin' AND tran_date <= '$today1'
 735                         GROUP BY week_name ORDER BY week_name DESC LIMIT 0, $weeks) b
 736                 GROUP BY week_name ORDER BY week_name ASC";
 737         $result = db_query($sql, "Transactions could not be calculated");
 738         $title = _("Last $weeks weeks Performance");
 739         check_page_security('SA_GLANALYTIC');
 740         $week_names = $sales = $costs = $results = array();
 741         while ($myrow = db_fetch($result))
 742         {
 743                 $week_names[] = $myrow['week_name'];
 744                 $sales[] = round($myrow['sales']);
 745                 $costs[] = round($myrow['costs']);
 746                 $results[] = round($myrow['sales'] - $myrow['costs']);
 747         }
 748         $pg->setLabels($week_names);
 749         $pg->addSerie(_('Sales'), $sales, 'bar');
 750         $pg->addSerie(_('Costs'), $costs, 'bar');
 751         $pg->addSerie(_('Results'), $results, 8);
 752         source_graphic($today, $title, _("Week"), $pg, _("Weeks"), $weeks);
 753 }
 754
 755 function gl_month_performance($today, $width="33", $months=5)
 756 {
 757         global $SysPrefs;
 758         $pg = new chart('bar', 'g4');
 759         if (isset($_POST['select_g4']))
 760                 $pg->type = $_POST['select_g4'];
 761         if (isset($_POST['per_g4']))
 762                 $months = $_POST['per_g4'];
 763         $begin = date2sql(begin_fiscalyear());
 764         $today1 = date2sql($today);
 765         $sep = $SysPrefs->dateseps[user_date_sep()];
 766         $sql = "SELECT month_name, sales, costs
 767                 FROM(SELECT DATE_FORMAT(tran_date, '%Y{$sep}%m') AS month_name,
 768                         SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales,
 769                         SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM
 770                         ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
 771                         ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6)
 772                         AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
 773                         AND tran_date >= '$begin' AND tran_date <= '$today1'
 774                         GROUP BY month_name ORDER BY month_name DESC LIMIT 0, $months) b
 775                 GROUP BY month_name ORDER BY month_name ASC";
 776         $result = db_query($sql, "Transactions could not be calculated");
 777         $title = _("Last $months Months Performance");
 778         check_page_security('SA_GLANALYTIC');
 779         $month_names = $sales = $costs = $results = array();
 780         while ($myrow = db_fetch($result))
 781         {
 782                 $month_names[] = $myrow['month_name'];
 783                 $sales[] = round($myrow['sales']);
 784                 $costs[] = round($myrow['costs']);
 785                 $results[] = round($myrow['sales'] - $myrow['costs']);
 786         }
 787         $pg->setLabels($month_names);
 788         $pg->addSerie(_('Sales'), $sales, 'bar');
 789         $pg->addSerie(_('Costs'), $costs, 'bar');
 790         $pg->addSerie(_('Results'), $results, 8);
 791         source_graphic($today, $title, _("Month"), $pg, _("Months"), $months);
 792 }
 793
 794 function customer_aging($today, $width="33")
 795 {
 796         $pg = new chart('bar', 'c3');
 797         if (isset($_POST['select_c3']))
 798                 $pg->type = $_POST['select_c3'];
 799         $today1 = date2sql($today);
 800         $past1 = get_company_pref('past_due_days');
 801         $past2 = 2 * $past1;
 802         // removed - debtor_trans.alloc from all summations
 803         $sign = "IF(`type` IN(".implode(',',  array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
 804         $value = "IFNULL($sign*((trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount -
 805                 trans.alloc)*trans.rate),0)";
 806         $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)";
 807     $sql = "SELECT Sum($value) AS Balance,
 808                                 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due,
 809                                 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
 810                                 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
 811                         FROM ".TB_PREF."debtors_master debtor
 812                                  LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today1' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY.","
 813                                  .TB_PREF."payment_terms terms,"
 814                                  .TB_PREF."credit_status credit_status
 815                         WHERE
 816                                         debtor.payment_terms = terms.terms_indicator
 817                                 AND debtor.credit_status = credit_status.id";
 818                 $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA;
 819     $result = db_query($sql,"The customer details could not be retrieved");
 820
 821     $row = db_fetch($result);
 822
 823         $title = _("Total Customers Aged Analysis");
 824         check_page_security('SA_SALESTRANSVIEW');
 825         $names = array();
 826         $past1a = $past1 + 1;
 827         $past2a = $past2 + 1;
 828         $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+");
 829         $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']),
 830                 round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2']));
 831         $total = round($row['Balance']);
 832         $pg->setLabels($names);
 833         $pg->addSerie(_('Balances'), $balances);
 834         source_graphic($today, $title, _('Days'), $pg);
 835         return $total;
 836 }
 837
 838 function customer_balance($today)
 839 {
 840         $today = date2sql($today);
 841         $sign = "IF(`type` IN(".implode(',',  array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
 842         $value = "IFNULL($sign*((trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount -
 843                 trans.alloc)*trans.rate),0)";
 844         $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)";
 845     $sql = "SELECT Sum($value) AS Balance
 846                         FROM ".TB_PREF."debtors_master debtor
 847                                  LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY."
 848                         WHERE ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA;
 849     $result = db_query($sql,"The customer details could not be retrieved");
 850     $row = db_fetch($result);
 851         return $row[0];
 852 }
 853
 854 function supplier_aging($today, $width="33")
 855 {
 856         $pg = new chart('bar', 's3');
 857         if (isset($_POST['select_s3']))
 858                 $pg->type = $_POST['select_s3'];
 859         $today1 = date2sql($today);
 860         $past1 = get_company_pref('past_due_days');
 861         $past2 = 2 * $past1;
 862         // removed - debtor_trans.alloc from all summations
 863         $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.",
 864         (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate,
 865         (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)";
 866         $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)";
 867     $sql = "SELECT Sum($value) AS Balance,
 868                                 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due,
 869                                 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
 870                                 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
 871                         FROM ".TB_PREF."suppliers supplier, ".TB_PREF."supp_trans trans
 872                                 WHERE supplier.supplier_id = trans.supplier_id
 873                         AND trans.tran_date <= '$today1'
 874                         AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA."
 875                         AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
 876     $result = db_query($sql,"The supplier details could not be retrieved");
 877
 878     $row = db_fetch($result);
 879
 880         $title = _("Total Suppliers Aged Analysis");
 881         check_page_security('SA_SUPPLIERANALYTIC');
 882         $names = array();
 883         $past1a = $past1 + 1;
 884         $past2a = $past2 + 1;
 885         $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+");
 886         $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']),
 887                 round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2']));
 888         $total = round($row['Balance']);
 889         $pg->setLabels($names);
 890         $pg->addSerie(_('Balances'), $balances);
 891         source_graphic($today, $title, _('Days'), $pg);
 892         return $total;
 893 }
 894
 895 function supplier_balance($today)
 896 {
 897         $today = date2sql($today);
 898         $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.",
 899         (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate,
 900         (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)";
 901     $sql = "SELECT Sum($value) AS Balance
 902                         FROM ".TB_PREF."suppliers supplier, ".TB_PREF."supp_trans trans
 903                                 WHERE supplier.supplier_id = trans.supplier_id
 904                         AND trans.tran_date <= '$today'
 905                         AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA."
 906                         AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
 907     $result = db_query($sql,"The supplier details could not be retrieved");
 908     $row = db_fetch($result);
 909         return $row[0];
 910 }
 911
 912 function cash_flow($today)
 913 {
 914         global $date_system, $SysPrefs;
 915         $months = 6;
 916         $pg = new chart('spline', 'g5');
 917         if (isset($_POST['select_g5']))
 918                 $pg->type = $_POST['select_g5'];
 919         if (isset($_POST['per_g5']))
 920                 $months = $_POST['per_g5'];
 921         if (!is_date_in_fiscalyear($today))
 922                 $today = end_fiscalyear();
 923         $today1 = begin_month($today);
 924         $today1 = add_months($today, -$months+1);
 925         list($da, $mo, $yr) = explode_date_to_dmy($today1);
 926         if ($date_system == 1)
 927                 list($yr, $mo, $da) = jalali_to_gregorian($yr, $mo, $da);
 928         elseif ($date_system == 2)
 929                 list($yr, $mo, $da) = islamic_to_gregorian($yr, $mo, $da);
 930
 931         $date = array();
 932
 933         for ($i = 0; $i < $months; $i++)
 934                 $date[$i] = date('Y-m-d',mktime(0,0,0,$mo + $i,1,$yr));
 935
 936         $result = get_bank_accounts();
 937         $total =  array_fill(0, $months, 0);
 938         $balance = array_fill(0, $months, 0);
 939         while ($account=db_fetch($result))
 940         {
 941                 $sql = "SELECT";
 942                 for ($i = 0; $i < $months; $i++)
 943                         $sql .= " SUM(CASE WHEN trans_date < '$date[$i]' THEN amount ELSE 0 END) AS per0".($i+1).",";
 944                 $sql = substr($sql, 0, -1);
 945                 $sql .= " FROM ".TB_PREF."bank_trans
 946                         WHERE bank_act=".$account['id'];
 947                 $res = db_query($sql, "Transactions for bank account could not be calculated");
 948                 $bal = db_fetch($res);
 949                 $is_home = is_company_currency($account['bank_curr_code']);
 950                 for ($i = 1; $i <= $months; $i++)
 951                         $balance[$i - 1] = $bal['per0'.$i];
 952                 for ($i = 0; $i < $months; $i++)
 953                 {
 954                         if (!$is_home)
 955                                 $balance[$i] = to_home_currency($balance[$i], $account['bank_curr_code'], sql2date($date[$i]));
 956                         $total[$i] += $balance[$i];
 957                 }
 958         }
 959         $sep = $SysPrefs->dateseps[user_date_sep()];
 960         for ($i = 0; $i < $months; $i++)
 961         {
 962                 $y = substr($date[$i], 0, 4);
 963                 $m = substr($date[$i], 5, 2);
 964                 $date[$i] = $y.$sep.$m;
 965                 $total[$i] = round($total[$i]);
 966         }
 967         $title = _("Last $months Months Cash Flow");
 968         check_page_security('SA_BANKREP');
 969         $pg->setLabels($date);
 970         $pg->addSerie(_('Balances'), $total, false, true);
 971         source_graphic($today, $title, _("Month"), $pg, _("Months"), $months);
 972 }
 973
 974 function source_graphic($today, $title, $x_axis, $pg, $per = '', $num = false)
 975 {
 976         if (!empty($per))
 977                 div_start($pg->id);
 978         //$today = sql2date($today);
 979         display_title("$title ($today)", $pg->id, $pg->type, $per, $num);
 980         if ($pg->isEmpty())
 981         {
 982                 display_note(_("No Data available yet!"), 1);
 983                 return;
 984         }
 985         if ($pg->type == 'horizontalBar')
 986         {
 987                 $pg->setYTitle($x_axis);
 988                 $pg->setXTitle(_("Amount"));
 989         }
 990         else
 991         {
 992                 $pg->setXTitle($x_axis);
 993                 $pg->setYTitle(_("Amount"));
 994         }
 995         if (empty($per))
 996                 div_start($pg->id);
 997         $pg->display();
 998         div_end();
 999 }
1000
1001 function customer_trans($today)
1002 {
1003         $today = date2sql($today);
1004
1005         $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, debtor.debtor_no,
1006                 debtor.name, branch.br_name, debtor.curr_code,
1007                 (trans.ov_amount + trans.ov_gst + trans.ov_freight
1008                         + trans.ov_freight_tax + trans.ov_discount)     AS total,
1009                 (trans.ov_amount + trans.ov_gst + trans.ov_freight
1010                         + trans.ov_freight_tax + trans.ov_discount - trans.alloc) AS remainder,
1011                 DATEDIFF('$today', trans.due_date) AS days
1012                 FROM ".TB_PREF."debtor_trans as trans, ".TB_PREF."debtors_master as debtor,
1013                         ".TB_PREF."cust_branch as branch
1014                 WHERE debtor.debtor_no = trans.debtor_no AND trans.branch_code = branch.branch_code
1015                         AND trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
1016                         + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA."
1017                         AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC";
1018         $result = db_query($sql);
1019         $title = db_num_rows($result) . _(" overdue Sales Invoices");
1020         display_title($title);
1021         $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Customer"), _("Branch"), _("Currency"),
1022                 _("Total"), _("Remainder"),     _("Days"));
1023         start_table(TABLESTYLE, "width=90%");
1024         headers($th);
1025         $k = 0; //row colour counter
1026         while ($myrow = db_fetch($result))
1027         {
1028                 alt_table_row_color($k);
1029                 label_cell(get_trans_view_str(ST_SALESINVOICE, $myrow["trans_no"]));
1030                 label_cell($myrow['reference']);
1031                 label_cell(sql2date($myrow['tran_date']));
1032                 label_cell(sql2date($myrow['due_date']));
1033                 $name = $myrow["debtor_no"]." ".$myrow["name"];
1034                 label_cell($name);
1035                 label_cell($myrow['br_name']);
1036                 label_cell($myrow['curr_code']);
1037                 amount_cell($myrow['total']);
1038                 amount_cell($myrow['remainder']);
1039                 label_cell($myrow['days'], "align='right'");
1040                 end_row();
1041         }
1042         end_table(2);
1043 }
1044
1045 function calculate_next_invoice($myrow)
1046 {
1047         if ($myrow["last_sent"] == '0000-00-00')
1048                 $next = sql2date($myrow["begin"]);
1049         else
1050                 $next = sql2date($myrow["last_sent"]);
1051         $next = add_months($next, $myrow['monthly']);
1052         $next = add_days($next, $myrow['days']);
1053         return add_days($next,-1);
1054 }
1055
1056 function customer_recurrent_invoices($today)
1057 {
1058         $result = get_recurrent_invoices($today);
1059         $title = _("Overdue Recurrent Invoices");
1060         display_title($title);
1061         $th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Next invoice"));
1062         start_table(TABLESTYLE, "width=90%");
1063         headers($th);
1064         $k = 0;
1065         while ($myrow = db_fetch($result))
1066         {
1067                 if (!$myrow['overdue'])
1068                         continue;
1069                 alt_table_row_color($k);
1070
1071                 label_cell($myrow["description"]);
1072                 label_cell(get_customer_trans_view_str(ST_SALESORDER, $myrow["order_no"]));
1073                 if ($myrow["debtor_no"] == 0)
1074                 {
1075                         label_cell("");
1076
1077                         label_cell(get_sales_group_name($myrow["group_no"]));
1078                 }
1079                 else
1080                 {
1081                         label_cell(get_customer_name($myrow["debtor_no"]));
1082                         label_cell(get_branch_name($myrow['group_no']));
1083                 }
1084                 label_cell(calculate_next_invoice($myrow),  "align='center'");
1085                 end_row();
1086         }
1087         end_table(2);
1088 }
1089
1090 function supplier_trans($today)
1091 {
1092         $today = date2sql($today);
1093         $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, s.supplier_id,
1094                 s.supp_name, s.curr_code,
1095                 (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS total,
1096                 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) AS remainder,
1097                 DATEDIFF('$today', trans.due_date) AS days
1098                 FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as s
1099                 WHERE s.supplier_id = trans.supplier_id
1100                         AND trans.type = ".ST_SUPPINVOICE." AND (ABS(trans.ov_amount + trans.ov_gst +
1101                                 trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA."
1102                         AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC";
1103         $result = db_query($sql);
1104         $title = db_num_rows($result) . _(" overdue Purchase Invoices");
1105         display_title($title);
1106         $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Supplier"), _("Currency"), _("Total"),
1107                 _("Remainder"), _("Days"));
1108         start_table(TABLESTYLE, "width=90%");
1109         headers($th);
1110         $k = 0; //row colour counter
1111         while ($myrow = db_fetch($result))
1112         {
1113                 alt_table_row_color($k);
1114                 label_cell(get_trans_view_str(ST_SUPPINVOICE, $myrow["trans_no"]));
1115                 label_cell($myrow['reference']);
1116                 label_cell(sql2date($myrow['tran_date']));
1117                 label_cell(sql2date($myrow['due_date']));
1118                 $name = $myrow["supplier_id"]." ".$myrow["supp_name"];
1119                 label_cell($name);
1120                 label_cell($myrow['curr_code']);
1121                 amount_cell($myrow['total']);
1122                 amount_cell($myrow['remainder']);
1123                 label_cell($myrow['days'], "align='right'");
1124                 end_row();
1125         }
1126         end_table(2);
1127 }
1128
1129 function stock_below_reorder($today, $type)
1130 {
1131         $MB = ($type == 0 ? 'B' : ($type == 1 ? 'M' : 'F'));
1132         $today = date2sql($today);
1133
1134     $sql = "SELECT st.loc_code, l.location_name, st.stock_id, sm.description, reorders.reorder_level AS r, SUM(st.qty) AS q
1135                 FROM ".TB_PREF."stock_moves st
1136                 LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id
1137                 LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id
1138                 LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND
1139                         reorders.stock_id = st.stock_id
1140                 LEFT JOIN ".TB_PREF."locations l ON l.loc_code = st.loc_code
1141           WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B'
1142                         GROUP BY sm.stock_id HAVING q <= r";
1143         $result = db_query($sql,"an item reorder could not be retreived");
1144         $title = db_num_rows($result) . _(" items are below Reorder Level");
1145         display_title($title);
1146         $th = array("#", _("Location"), _("Stock Id"), _('Description'), _("Reorder"), _("qty"));
1147         start_table(TABLESTYLE, "width=90%");
1148         headers($th);
1149         $k = 0; //row colour counter
1150         while ($myrow = db_fetch($result))
1151         {
1152                 alt_table_row_color($k);
1153                 label_cell($myrow['loc_code']);
1154                 label_cell($myrow['location_name']);
1155                 label_cell($myrow['stock_id']);
1156                 label_cell($myrow['description']);
1157                 $dec = get_qty_dec($myrow['stock_id']);
1158                 qty_cell($myrow['r'], false, $dec);
1159                 qty_cell($myrow['q'], false, $dec);
1160                 end_row();
1161         }
1162         end_table(2);
1163 }
1164
1165 function bank_balance($today, $width)
1166 {
1167         $today = date2sql($today);
1168         $sql = "SELECT bank_act, bank_account_name, bank_curr_code, SUM(amount) balance FROM ".TB_PREF."bank_trans bt
1169                     INNER JOIN ".TB_PREF."bank_accounts ba ON bt.bank_act = ba.id
1170                     WHERE trans_date <= '$today'
1171                     AND inactive <> 1
1172                     GROUP BY bank_act, bank_account_name
1173                                 ORDER BY bank_account_name";
1174         $result = db_query($sql);
1175         $title = _("Bank Account Balances");
1176         display_title($title);
1177         $th = array(_("Account"), _("Currency"), _("Balance"));
1178         start_table(TABLESTYLE, "width='$width%'");
1179         headers($th);
1180         $k = 0; //row colour counter
1181         while ($myrow = db_fetch($result))
1182         {
1183                 alt_table_row_color($k);
1184                 label_cell(viewer_link($myrow["bank_account_name"], 'gl/inquiry/bank_inquiry.php?bank_account='.$myrow["bank_act"]));
1185                 label_cell($myrow["bank_curr_code"]);
1186                 amount_cell($myrow['balance']);
1187                 end_row();
1188         }
1189         end_table(1);
1190 }
1191
1192 function get_num_customers()
1193 {
1194         $sql = "SELECT count(*) FROM ".TB_PREF."debtors_master";
1195         $result = db_query($sql, "Fail in count customers");
1196     $row = db_fetch($result);
1197         return $row[0];
1198 }
1199
1200 function get_num_branches()
1201 {
1202         $sql = "SELECT count(*) FROM ".TB_PREF."cust_branch";
1203         $result = db_query($sql, "Fail in count branches");
1204     $row = db_fetch($result);
1205         return $row[0];
1206 }
1207
1208 function get_num_salesmen()
1209 {
1210         $sql = "SELECT count(*) FROM ".TB_PREF."salesman";
1211         $result = db_query($sql, "Fail in count salesmen");
1212     $row = db_fetch($result);
1213         return $row[0];
1214 }
1215
1216 function get_num_overdue_sales()
1217 {
1218         $today = date2sql(calc_today());
1219
1220         $sql = "SELECT COUNT(trans.trans_no)
1221                 FROM ".TB_PREF."debtor_trans as trans
1222                 WHERE trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
1223                         + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA."
1224                         AND DATEDIFF('$today', trans.due_date) > 0";
1225         $result = db_query($sql);
1226     $row = db_fetch($result);
1227         return $row[0];
1228 }
1229
1230 function get_num_suppliers()
1231 {
1232         $sql = "SELECT count(*) FROM ".TB_PREF."suppliers";
1233         $result = db_query($sql, "Fail in count suppliers");
1234     $row = db_fetch($result);
1235         return $row[0];
1236 }
1237
1238 function get_new_purch_orders()
1239 {
1240         $sql = "SELECT count(*) FROM ".TB_PREF."purch_orders po, ".TB_PREF."purch_order_details pd WHERE po.order_no = pd.order_no AND
1241                 quantity_received < quantity_ordered";
1242         $result = db_query($sql, "Fail in count purch orders");
1243     $row = db_fetch($result);
1244         return $row[0];
1245 }
1246
1247 function get_num_supp_invoices()
1248 {
1249         $sql = "SELECT count(*) FROM ".TB_PREF."supp_trans WHERE (ov_amount+ov_discount+ov_gst - alloc) <> 0";
1250         $result = db_query($sql, "Fail in count purch invoices");
1251     $row = db_fetch($result);
1252         return $row[0];
1253 }
1254
1255 function get_num_overdue_purch()
1256 {
1257         $today = date2sql(calc_today());
1258
1259         $sql = "SELECT COUNT(*) FROM ".TB_PREF."supp_trans WHERE (ov_amount+ov_discount+ov_gst - alloc) > ".FLOAT_COMP_DELTA." AND DATEDIFF('$today', due_date) > 0";
1260         $result = db_query($sql, "Fail in count purch invoices");
1261     $row = db_fetch($result);
1262         return $row[0];
1263 }
1264
1265 function get_num_items()
1266 {
1267         $sql = "SELECT count(*) FROM ".TB_PREF."stock_master";
1268         $result = db_query($sql, "Fail in count stock master");
1269     $row = db_fetch($result);
1270         return $row[0];
1271 }
1272
1273 function get_num_locations()
1274 {
1275         $sql = "SELECT count(*) FROM ".TB_PREF."locations";
1276         $result = db_query($sql, "Fail in count locations");
1277     $row = db_fetch($result);
1278         return $row[0];
1279 }
1280
1281 function get_num_kits()
1282 {
1283         $sql="SELECT count(*) FROM ".TB_PREF."item_codes";
1284
1285         $result = db_query($sql, "Fail in count kits");
1286     $row = db_fetch($result);
1287         return $row[0];
1288 }
1289
1290 function get_num_reorder()
1291 {
1292         $MB = 'B';
1293         $today = date2sql(calc_today());
1294
1295     $sql = "SELECT st.stock_id, reorders.reorder_level AS r, SUM(st.qty) AS q
1296                 FROM ".TB_PREF."stock_moves st
1297                 LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id
1298                 LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id
1299                 LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND
1300                         reorders.stock_id = st.stock_id
1301           WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B'
1302                         GROUP BY st.stock_id HAVING q <= r";
1303         $result = db_query($sql,"an item reorder could not be retreived");
1304         return db_num_rows($result);
1305 }
1306
1307 function get_num_assembled()
1308 {
1309         $begin = date2sql(begin_fiscalyear());
1310         $today = date2sql(calc_today());
1311         $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ASSEMBLY." AND released_date >= '$begin'
1312                 AND released_date <= '$today'";
1313         $result = db_query($sql, "Fail in count workorders");
1314     $row = db_fetch($result);
1315         return $row[0];
1316 }
1317
1318 function get_num_manufactured()
1319 {
1320         $begin = date2sql(begin_fiscalyear());
1321         $today = date2sql(calc_today());
1322         $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ADVANCED." AND released_date >= '$begin'
1323                 AND released_date <= '$today'";
1324         $result = db_query($sql, "Fail in count workorders");
1325     $row = db_fetch($result);
1326         return $row[0];
1327 }
1328
1329 function get_num_workcentres()
1330 {
1331         $sql = "SELECT count(*) FROM ".TB_PREF."workcentres";
1332         $result = db_query($sql, "Fail in count work centres");
1333     $row = db_fetch($result);
1334         return $row[0];
1335 }
1336
1337 function get_open_workorders()
1338 {
1339         $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 0";
1340         $result = db_query($sql, "Fail in count workorders");
1341     $row = db_fetch($result);
1342         return $row[0];
1343 }
1344
1345 function get_num_fixed_assets()
1346 {
1347         $sql = "SELECT count(*) FROM ".TB_PREF."stock_master WHERE mb_flag = 'F'";
1348         $result = db_query($sql, "Fail in count fixed assets");
1349     $row = db_fetch($result);
1350         return $row[0];
1351 }
1352
1353 function get_num_fixed_locations()
1354 {
1355         $sql = "SELECT count(*) FROM ".TB_PREF."stock_master s LEFT JOIN ".TB_PREF."loc_stock l ON s.stock_id = l.stock_id
1356                 WHERE mb_flag = 'F'";
1357         $result = db_query($sql, "Fail in count locations");
1358     $row = db_fetch($result);
1359         return $row[0];
1360 }
1361
1362 function get_num_fixed_categories()
1363 {
1364         $sql = "SELECT count(*) FROM ".TB_PREF."stock_category c LEFT JOIN ".TB_PREF."stock_master s ON c.category_id = s.category_id
1365                 WHERE mb_flag = 'F'";
1366         $result = db_query($sql, "Fail in count categories");
1367     $row = db_fetch($result);
1368         return $row[0];
1369 }
1370
1371 function get_num_fixed_classes()
1372 {
1373         $sql = "SELECT count(*) FROM ".TB_PREF."stock_fa_class";
1374         $result = db_query($sql, "Fail in count fa classes");
1375     $row = db_fetch($result);
1376         return $row[0];
1377 }
1378
1379 function get_num_dimensions()
1380 {
1381         $sql = "SELECT count(*) FROM ".TB_PREF."dimensions";
1382         $result = db_query($sql, "Fail in count dimensions");
1383     $row = db_fetch($result);
1384         return $row[0];
1385 }
1386
1387 function get_dim_type2()
1388 {
1389         $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE type_ = 2";
1390         $result = db_query($sql, "Fail in count simensions 2");
1391     $row = db_fetch($result);
1392         return $row[0];
1393 }
1394
1395 function get_dim_closed()
1396 {
1397         $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE closed = 1";
1398         $result = db_query($sql, "Fail in count closed dimensions");
1399     $row = db_fetch($result);
1400         return $row[0];
1401 }
1402
1403 function get_dim_total()
1404 {
1405         $begin = date2sql(begin_fiscalyear());
1406         $today = date2sql(calc_today());
1407
1408         $sql = "SELECT SUM(IF(c.ctype > 3, -amount, 0)) AS result, SUM(IF(c.ctype < 4, amount, 0)) AS balance, d.reference, d.name FROM ".TB_PREF."gl_trans g
1409                 LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code
1410                 LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type
1411                 LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id)
1412                 LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid
1413                 WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
1414                 AND tran_date <= '$today'  AND d.reference IS NOT NULL GROUP BY d.reference";
1415         $bal = $res = 0.0;
1416         $result = db_query($sql, "Transactions could not be calculated");
1417         while ($row = db_fetch($result))
1418         {
1419                 $bal += $row['balance'];
1420                 $res += $row['result'];
1421         }
1422         return array(round($bal), round($res));
1423 }
1424
1425 function get_today_deposits($today)
1426 {
1427         $today= date2sql($today);
1428         $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba
1429                 WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount > 0.0";
1430         $result = db_query($sql, "could not retrieve today deposits");
1431         if ($result == false)
1432                 return 0;
1433         else
1434         {
1435                 $row = db_fetch($result);
1436                 return $row[0];
1437         }
1438 }
1439
1440 function get_today_payments($today)
1441 {
1442         $today= date2sql($today);
1443         $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba
1444                 WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount < 0.0";
1445         $result = db_query($sql, "could not retrieve today deposits");
1446         if ($result == false)
1447                 return 0;
1448         else
1449         {
1450                 $row = db_fetch($result);
1451                 return $row[0];
1452         }
1453 }
1454
1455 function get_num_users()
1456 {
1457         $sql = "SELECT count(*) FROM ".TB_PREF."users";
1458         $result = db_query($sql, "could not get num users");
1459     $row = db_fetch($result);
1460         return $row[0];
1461 }
1462
1463 function get_num_extensions()
1464 {
1465         global $installed_extensions;
1466         return count($installed_extensions);
1467 }
1468
1469 function get_num_languages()
1470 {
1471         global $installed_languages;
1472         return count($installed_languages);
1473 }
1474
1475 function get_database_size()
1476 {
1477         global $db_connections;
1478         $c = user_company();
1479         $dbase = $db_connections[$c]['dbname'];
1480         $tb_pref = $db_connections[$c]['tbpref'];
1481         $sql = "SHOW TABLE STATUS FROM $dbase";
1482         if (!empty($tb_pref))
1483                 $sql .= " LIKE '{$tb_pref}%'";
1484         $result = db_query($sql, "could not get database size");
1485     $size = 0;
1486     while ($row = db_fetch($result)) {
1487                 $size += $row['Data_length'];
1488                 $size += $row['Index_length'];
1489     }
1490         return number_format2(($size / 1024 / 1024), 2).' MB';
1491 }
1492
1493 function calc_today()
1494 {
1495         $today = Today();
1496         if (!is_date_in_fiscalyear($today))
1497                 $today = end_fiscalyear();
1498         return $today;
1499 }
1500