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