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
  13 function dashboard($sel_app)
  14 {
  15         $selected_app = $sel_app;
  16
  17
  18         if (is_object($sel_app) && !$_SESSION["wa_current_user"]->check_application_access($selected_app))
  19                 return;
  20         // first have a look through the directory,
  21         // and remove old temporary pdfs and pngs
  22         $dir = company_path(). '/pdf_files';
  23
  24         if ($d = @opendir($dir)) {
  25                 while (($file = readdir($d)) !== false) {
  26                         if (!is_file($dir.'/'.$file) || $file == 'index.php') continue;
  27                 // then check to see if this one is too old
  28                         $ftime = filemtime($dir.'/'.$file);
  29                  // seems 3 min is enough for any report download, isn't it?
  30                         if (time()-$ftime > 180){
  31                                 unlink($dir.'/'.$file);
  32                         }
  33                 }
  34                 closedir($d);
  35         }
  36
  37         //check_for_overdue_recurrent_invoices();
  38         if ($selected_app == "orders")
  39                 display_customer_topten();
  40         elseif ($selected_app == "AP")
  41                 display_supplier_topten();
  42         elseif ($selected_app == "stock")
  43                 display_stock_topten();
  44         elseif ($selected_app == "manuf")
  45                 display_stock_topten(1);
  46         elseif ($selected_app == "assets")
  47                 display_stock_topten(2);
  48         elseif ($selected_app == "proj")
  49                 display_dimension_topten();
  50         elseif ($selected_app == "GL")
  51                 display_gl_info();
  52         else
  53                 display_all();
  54 }
  55
  56 function display_title($title)
  57 {
  58         br();
  59         display_heading($title);
  60         br();
  61         //echo "<tr><td colspan=$colspan class='headingtext' style='background-color:#fff;text-align:center;border:0;height:40px;'>$title</td></tr>\n";
  62 }
  63
  64 function table_one()
  65 {
  66         echo "<table width='100%'>";
  67         echo "<tr valign=top><td style='width:50%'>\n"; // outer table
  68 }
  69
  70 function table_two()
  71 {
  72         echo "</td><td style='width:50%'>\n";
  73 }
  74
  75 function table_end()
  76 {
  77         echo "</td></tr></table>\n";
  78 }
  79
  80 function display_customer_topten()
  81 {
  82         $pg = new graph();
  83
  84         table_one();
  85         $today = Today();
  86         $title = customer_top($today, 10, 66, $pg);
  87         customer_trans($today);
  88         table_two();
  89         source_graphic($today, $title, _("Customer"), $pg, _("Sales"));
  90         customer_recurrent_invoices($today);
  91         table_end();
  92 }
  93
  94 function display_supplier_topten()
  95 {
  96         $pg = new graph();
  97
  98         $today = Today();
  99         table_one();
 100         $title = supplier_top($today, 10, 66, $pg);
 101         supplier_trans($today);
 102         table_two();
 103         source_graphic($today, $title, _("Supplier"), $pg, _("Purchase"));
 104         table_end();
 105 }
 106
 107 function display_stock_topten($type=0)
 108 {
 109         $pg = new graph();
 110
 111         table_one();
 112         $today = Today();
 113         $title = stock_top($today, 10, 66, $type, $pg);
 114         table_two();
 115         if ($type == 2)
 116                 $source = _("Fixed Assets");
 117         elseif ($type == 1)
 118                 $source = _("Manufacturing");
 119         else
 120                 $source = _("Items");
 121         source_graphic($today, $title, $source, $pg, ($type == 0 ? _("Sales") : _("Amount")),
 122                 ($type == 0 ? _("Costs") : null));
 123         table_end();
 124 }
 125
 126 function display_dimension_topten()
 127 {
 128         $pg = new graph();
 129
 130         table_one();
 131         $today = Today();
 132         $title = dimension_top($today, 10, 66, $pg);
 133         table_two();
 134         source_graphic($today, $title, _("Dimension"), $pg, _("Performance"), null, 5);
 135         table_end();
 136 }
 137
 138 function display_gl_info()
 139 {
 140         $pg = new graph();
 141
 142         table_one();
 143         $today = Today();
 144         $title = gl_top($today, 66, $pg);
 145         bank_balance($today, 66);
 146         table_two();
 147         source_graphic($today, $title, _("Class"), $pg, _("Amount"), null, 5);
 148         gl_performance($today, 66, 5);
 149         table_end();
 150 }
 151
 152 function display_all()
 153 {
 154         $today = Today();
 155         $pg = new graph();
 156
 157         table_one();
 158         $title = customer_top($today, 3, 66, $pg);
 159         source_graphic($today, $title, _("Customer"), $pg, _("Sales"));
 160         $pg = new graph();
 161         $title = supplier_top($today, 3, 66, $pg);
 162         source_graphic($today, $title, _("Supplier"), $pg, _("Purchases"));
 163         $pg = new graph();
 164         $title = stock_top($today, 3, 66, 0, $pg);
 165         source_graphic($today, $title, _("Items"), $pg, _("Sales"), _("Costs"));
 166         table_two();
 167         dimension_top($today, 3, 66);
 168         $pg = new graph();
 169         $title = gl_top($today, 66, $pg);
 170         source_graphic($today, $title, _("Class"), $pg, _("Amount"), null, 5);
 171         stock_top($today, 3, 66, 2);
 172         stock_top($today, 3, 66, 1);
 173         table_end();
 174 }
 175
 176 function customer_top($today, $limit=10, $width="33", &$pg=null)
 177 {
 178         $begin = begin_fiscalyear();
 179         $begin1 = date2sql($begin);
 180         $today1 = date2sql($today);
 181         $sql = "SELECT SUM((ov_amount + ov_discount) * rate * IF(trans.type = ".ST_CUSTCREDIT.", -1, 1)) AS total,d.debtor_no, d.name FROM
 182                 ".TB_PREF."debtor_trans AS trans, ".TB_PREF."debtors_master AS d WHERE trans.debtor_no=d.debtor_no
 183                 AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_CUSTCREDIT.")
 184                 AND tran_date >= '$begin1' AND tran_date <= '$today1' GROUP by d.debtor_no ORDER BY total DESC, d.debtor_no
 185                 LIMIT $limit";
 186         $result = db_query($sql);
 187         $title = sprintf(_("Top %s customers in fiscal year"), $limit);
 188         display_title($title);
 189         $th = array(_("Customer"), _("Amount"));
 190         start_table(TABLESTYLE, "width='$width%'");
 191         table_header($th);
 192         check_page_security('SA_SALESTRANSVIEW');
 193         $k = 0; //row colour counter
 194         $i = 0;
 195         while ($myrow = db_fetch($result))
 196         {
 197                 alt_table_row_color($k);
 198                 $name = $myrow["debtor_no"]." ".$myrow["name"];
 199                 label_cell($name);
 200                 amount_cell($myrow['total']);
 201                 if ($pg != null)
 202                 {
 203                         $pg->x[$i] = $name;
 204                         $pg->y[$i] = $myrow['total'];
 205                 }
 206                 $i++;
 207                 end_row();
 208         }
 209         end_table(2);
 210         return $title;
 211 }
 212
 213 function supplier_top($today, $limit=10, $width="33", &$pg=null)
 214 {
 215         $begin = begin_fiscalyear();
 216         $begin1 = date2sql($begin);
 217         $today1 = date2sql($today);
 218         $sql = "SELECT SUM((trans.ov_amount + trans.ov_discount) * rate) AS total, s.supplier_id, s.supp_name FROM
 219                 ".TB_PREF."supp_trans AS trans, ".TB_PREF."suppliers AS s WHERE trans.supplier_id=s.supplier_id
 220                 AND (trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.")
 221                 AND tran_date >= '$begin1' AND tran_date <= '$today1' GROUP by s.supplier_id ORDER BY total DESC, s.supplier_id
 222                 LIMIT $limit";
 223         $result = db_query($sql);
 224         $title = sprintf(_("Top %s suppliers in fiscal year"), $limit);
 225         display_title($title);
 226         $th = array(_("Supplier"), _("Amount"));
 227         start_table(TABLESTYLE, "width='$width%'");
 228         table_header($th);
 229         check_page_security('SA_SUPPTRANSVIEW');
 230         $k = 0; //row colour counter
 231         $i = 0;
 232         while ($myrow = db_fetch($result))
 233         {
 234                 alt_table_row_color($k);
 235                 $name = $myrow["supplier_id"]." ".$myrow["supp_name"];
 236                 label_cell($name);
 237                 amount_cell($myrow['total']);
 238                 if ($pg != null)
 239                 {
 240                         $pg->x[$i] = $name;
 241                         $pg->y[$i] = $myrow['total'];
 242                 }
 243                 $i++;
 244                 end_row();
 245         }
 246         end_table(2);
 247         return $title;
 248 }
 249
 250 function stock_top($today, $limit=10, $width="33", $type=0, &$pg=null)
 251 {
 252         if ($type == 2)
 253                 $sec = 'SA_ASSETSANALYTIC';
 254         elseif ($type == 1)
 255                 $sec = 'SA_WORKORDERANALYTIC';
 256         else
 257                 $sec = 'SA_ITEMSTRANSVIEW';
 258         $begin = begin_fiscalyear();
 259         $begin1 = date2sql($begin);
 260         $today1 = date2sql($today);
 261         if ($type == 0)
 262         {
 263                 $sql = "SELECT SUM((trans.unit_price * trans.quantity) * d.rate) AS total, s.stock_id, s.description,
 264                         SUM(trans.quantity) AS qty, SUM((s.material_cost + s.overhead_cost + s.labour_cost) * trans.quantity) AS costs FROM
 265                         ".TB_PREF."debtor_trans_details AS trans, ".TB_PREF."stock_master AS s, ".TB_PREF."debtor_trans AS d
 266                         WHERE trans.stock_id=s.stock_id AND trans.debtor_trans_type=d.type AND trans.debtor_trans_no=d.trans_no
 267                         AND (d.type = ".ST_SALESINVOICE." OR d.type = ".ST_CUSTCREDIT.") ";
 268         }
 269         else
 270         {
 271                 $sql = "SELECT SUM(m.qty * (s.material_cost + s.labour_cost + s.overhead_cost)) AS total, s.stock_id, s.description,
 272                         SUM(qty) AS qty FROM ".TB_PREF."stock_master AS s, ".TB_PREF."stock_moves AS m
 273                         WHERE s.stock_id=m.stock_id ";
 274                 if ($type == 1)
 275                         $sql .= "AND s.mb_flag='M' AND m.type <> ".ST_CUSTDELIVERY." AND m.type <> ".ST_CUSTCREDIT." ";
 276                 elseif ($type == 2)
 277                         $sql .= "AND s.mb_flag='F' ";
 278         }
 279         if ($type != 2)
 280                 $sql .= "AND tran_date >= '$begin1' ";
 281         $sql .= "AND tran_date <= '$today1' GROUP by s.stock_id ORDER BY total DESC, s.stock_id
 282                 LIMIT $limit";
 283         $result = db_query($sql);
 284         if ($type == 1)
 285                 $title = sprintf(_("Top %s Manufactured Items in fiscal year"), $limit);
 286         elseif ($type == 2)
 287                 $title = sprintf(_("Top %s Fixed Assets"), $limit);
 288         else
 289                 $title = sprintf(_("Top %s Sold Items in fiscal year"), $limit);
 290         display_title($title);
 291         if ($type == 0)
 292                 $th = array(_("Item"), _("Sales"), _("Costs"), _("Quantity"));
 293         else
 294                 $th = array(_("Item"), _("Amount"), _("Quantity"));
 295         start_table(TABLESTYLE, "width='$width%'");
 296         table_header($th);
 297         check_page_security($sec);
 298         $k = 0; //row colour counter
 299         $i = 0;
 300         while ($myrow = db_fetch($result))
 301         {
 302                 alt_table_row_color($k);
 303                 $name = $myrow["description"];
 304                 label_cell($name);
 305                 amount_cell($myrow['total']);
 306                 if ($type == 0)
 307                         amount_cell($myrow['costs']);
 308                 qty_cell($myrow['qty']);
 309                 if ($pg != NULL)
 310                 {
 311                         $pg->x[$i] = $name;
 312                         $pg->y[$i] = $myrow['total'];
 313                         if ($type == 0)
 314                                 $pg->z[$i] = $myrow['costs'];
 315                 }
 316                 $i++;
 317                 end_row();
 318         }
 319         end_table(2);
 320         return $title;
 321 }
 322
 323 function dimension_top($today, $limit=10, $width="33", &$pg=null)
 324 {
 325
 326         $begin = begin_fiscalyear();
 327         $begin1 = date2sql($begin);
 328         $today1 = date2sql($today);
 329         $sql = "SELECT SUM(-t.amount) AS total, d.reference, d.name FROM
 330                 ".TB_PREF."gl_trans AS t,".TB_PREF."dimensions AS d WHERE
 331                 (t.dimension_id = d.id OR t.dimension2_id = d.id) AND
 332                 t.tran_date >= '$begin1' AND t.tran_date <= '$today1' GROUP BY d.id ORDER BY total DESC LIMIT $limit";
 333         $result = db_query($sql, "Transactions could not be calculated");
 334         $title = sprintf(_("Top %s Dimensions in fiscal year"), $limit);
 335         display_title($title);
 336         $th = array(_("Dimension"), _("Amount"));
 337         start_table(TABLESTYLE, "width='$width%'");
 338         table_header($th);
 339         check_page_security('SA_DIMTRANSVIEW');
 340         $k = 0; //row colour counter
 341         $i = 0;
 342         while ($myrow = db_fetch($result))
 343         {
 344                 alt_table_row_color($k);
 345                 $name = $myrow['reference']." ".$myrow["name"];
 346                 label_cell($name);
 347                 amount_cell($myrow['total']);
 348                 if ($pg != null)
 349                 {
 350                         $pg->x[$i] = $name;
 351                         $pg->y[$i] = abs($myrow['total']);
 352                 }
 353                 $i++;
 354                 end_row();
 355         }
 356         end_table(2);
 357         return $title;
 358 }
 359
 360 function gl_top($today, $width="33", &$pg=null)
 361 {
 362         $begin = begin_fiscalyear();
 363         $begin1 = date2sql($begin);
 364         $today1 = date2sql($today);
 365         $sql = "SELECT SUM(amount) AS total, c.class_name, c.ctype FROM
 366                 ".TB_PREF."gl_trans,".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
 367                 ".TB_PREF."chart_class AS c WHERE
 368                 account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
 369                 AND IF(c.ctype > 3, tran_date >= '$begin1', tran_date >= '0000-00-00')
 370                 AND tran_date <= '$today1' GROUP BY c.cid ORDER BY c.cid";
 371         $result = db_query($sql, "Transactions could not be calculated");
 372         $title = _("Class Balances");
 373         display_title($title);
 374         start_table(TABLESTYLE2, "width='$width%'");
 375         check_page_security('SA_GLANALYTIC');
 376         $i = 0;
 377         $total = 0;
 378         while ($myrow = db_fetch($result))
 379         {
 380                 if ($myrow['ctype'] > 3)
 381                 {
 382                         $total += $myrow['total'];
 383                         $myrow['total'] = -$myrow['total'];
 384                         if ($pg != null)
 385                         {
 386                                 $pg->x[$i] = $myrow['class_name'];
 387                                 $pg->y[$i] = abs($myrow['total']);
 388                         }
 389                         $i++;
 390                 }
 391                 label_row($myrow['class_name'], number_format2($myrow['total'], user_price_dec()),
 392                         "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
 393         }
 394         $calculated = _("Calculated Return");
 395         label_row("&nbsp;", "");
 396         label_row($calculated, number_format2(-$total, user_price_dec()),
 397                 "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
 398         if ($pg != null)
 399         {
 400                 $pg->x[$i] = $calculated;
 401                 $pg->y[$i] = -$total;
 402         }
 403         end_table(2);
 404         return $title;
 405 }
 406
 407 function gl_performance($today, $width="33", $weeks=5)
 408 {
 409         global $SysPrefs;
 410         $pg = new graph();
 411
 412         $begin = begin_fiscalyear();
 413         $begin1 = date2sql($begin);
 414         $today1 = date2sql($today);
 415         $sep = $SysPrefs->dateseps[user_date_sep()];
 416         $sql = "SELECT week_name, sales, costs
 417                 FROM(SELECT DATE_FORMAT(tran_date, '%Y{$sep}%u') AS week_name,
 418                         SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales,
 419                         SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM
 420                         ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
 421                         ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6)
 422                         AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
 423                         AND tran_date >= '$begin1' AND tran_date <= '$today1'
 424                         GROUP BY week_name ORDER BY week_name DESC LIMIT 0, $weeks) b
 425                 GROUP BY week_name ORDER BY week_name ASC";
 426         $result = db_query($sql, "Transactions could not be calculated");
 427         $title = _("Last $weeks weeks Performance");
 428         check_page_security('SA_GLANALYTIC');
 429         $i = 0;
 430         while ($myrow = db_fetch($result))
 431         {
 432                 $pg->x[$i] = $myrow['week_name'];
 433                 $pg->y[$i] = $myrow['sales'];
 434                 $pg->z[$i] = $myrow['costs'];
 435                 $i++;
 436         }
 437
 438         source_graphic($today, $title, _("Week"), $pg, _("Sales"), _("Costs"), 1);
 439 }
 440
 441 function source_graphic($today, $title, $x_axis, $pg, $graphic1, $graphic2=null, $type=2)
 442 {
 443         if (count($pg->y) ==0 || (count($pg->y) == 1 && $pg->y[0] == 0))
 444                 return;
 445         display_title("$title ($today)");
 446         //$pg->title     = $title . " - " . $today;
 447         $pg->axis_x    = $x_axis;
 448         $pg->axis_y    = _("Amount");
 449         $pg->graphic_1 = $graphic1;
 450         if ($graphic2 != null)
 451                 $pg->graphic_2 = $graphic2;
 452         $pg->type      = $type;
 453         $pg->skin      = 1;
 454         $pg->built_in  = false;
 455         $filename = company_path(). "/pdf_files/". random_id().".png";
 456         $pg->display($filename, true);
 457         start_table(TABLESTYLE);
 458         start_row();
 459         echo "<td>";
 460         echo "<img src='$filename' border='0' alt='$title'>";
 461         echo "</td>";
 462         end_row();
 463         end_table(1);
 464 }
 465
 466 function customer_trans($today)
 467 {
 468         $today = date2sql($today);
 469
 470         $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, debtor.debtor_no,
 471                 debtor.name, branch.br_name, debtor.curr_code,
 472                 (trans.ov_amount + trans.ov_gst + trans.ov_freight
 473                         + trans.ov_freight_tax + trans.ov_discount)     AS total,
 474                 (trans.ov_amount + trans.ov_gst + trans.ov_freight
 475                         + trans.ov_freight_tax + trans.ov_discount - trans.alloc) AS remainder,
 476                 DATEDIFF('$today', trans.due_date) AS days
 477                 FROM ".TB_PREF."debtor_trans as trans, ".TB_PREF."debtors_master as debtor,
 478                         ".TB_PREF."cust_branch as branch
 479                 WHERE debtor.debtor_no = trans.debtor_no AND trans.branch_code = branch.branch_code
 480                         AND trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
 481                         + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA."
 482                         AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC";
 483         $result = db_query($sql);
 484         $title = db_num_rows($result) . _(" overdue Sales Invoices");
 485         display_title($title);
 486         $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Customer"), _("Branch"), _("Currency"),
 487                 _("Total"), _("Remainder"),     _("Days"));
 488         start_table(TABLESTYLE);
 489         table_header($th);
 490         $k = 0; //row colour counter
 491         while ($myrow = db_fetch($result))
 492         {
 493                 alt_table_row_color($k);
 494                 label_cell(get_trans_view_str(ST_SALESINVOICE, $myrow["trans_no"]));
 495                 label_cell($myrow['reference']);
 496                 label_cell(sql2date($myrow['tran_date']));
 497                 label_cell(sql2date($myrow['due_date']));
 498                 $name = $myrow["debtor_no"]." ".$myrow["name"];
 499                 label_cell($name);
 500                 label_cell($myrow['br_name']);
 501                 label_cell($myrow['curr_code']);
 502                 amount_cell($myrow['total']);
 503                 amount_cell($myrow['remainder']);
 504                 label_cell($myrow['days'], "align='right'");
 505                 end_row();
 506         }
 507         end_table(2);
 508 }
 509
 510 function calculate_next_invoice($myrow)
 511 {
 512         if ($myrow["last_sent"] == '0000-00-00')
 513                 $next = sql2date($myrow["begin"]);
 514         else
 515                 $next = sql2date($myrow["last_sent"]);
 516         $next = add_months($next, $myrow['monthly']);
 517         $next = add_days($next, $myrow['days']);
 518         return add_days($next,-1);
 519 }
 520
 521 function customer_recurrent_invoices($today)
 522 {
 523         $result = get_recurrent_invoices($today);
 524         $title = _("Overdue Recurrent Invoices");
 525         display_title($title);
 526         $th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Next invoice"));
 527         start_table(TABLESTYLE, "width=70%");
 528         table_header($th);
 529         $k = 0;
 530         while ($myrow = db_fetch($result))
 531         {
 532                 if (!$myrow['overdue'])
 533                         continue;
 534                 alt_table_row_color($k);
 535
 536                 label_cell($myrow["description"]);
 537                 label_cell(get_customer_trans_view_str(ST_SALESORDER, $myrow["order_no"]));
 538                 if ($myrow["debtor_no"] == 0)
 539                 {
 540                         label_cell("");
 541
 542                         label_cell(get_sales_group_name($myrow["group_no"]));
 543                 }
 544                 else
 545                 {
 546                         label_cell(get_customer_name($myrow["debtor_no"]));
 547                         label_cell(get_branch_name($myrow['group_no']));
 548                 }
 549                 label_cell(calculate_next_invoice($myrow),  "align='center'");
 550                 end_row();
 551         }
 552         end_table(2);
 553 }
 554
 555 function supplier_trans($today)
 556 {
 557         $today = date2sql($today);
 558         $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, s.supplier_id,
 559                 s.supp_name, s.curr_code,
 560                 (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS total,
 561                 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) AS remainder,
 562                 DATEDIFF('$today', trans.due_date) AS days
 563                 FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as s
 564                 WHERE s.supplier_id = trans.supplier_id
 565                         AND trans.type = ".ST_SUPPINVOICE." AND (ABS(trans.ov_amount + trans.ov_gst +
 566                                 trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA."
 567                         AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC";
 568         $result = db_query($sql);
 569         $title = db_num_rows($result) . _(" overdue Purchase Invoices");
 570         display_title($title);
 571         $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Supplier"), _("Currency"), _("Total"),
 572                 _("Remainder"), _("Days"));
 573         start_table(TABLESTYLE);
 574         table_header($th);
 575         $k = 0; //row colour counter
 576         while ($myrow = db_fetch($result))
 577         {
 578                 alt_table_row_color($k);
 579                 label_cell(get_trans_view_str(ST_SUPPINVOICE, $myrow["trans_no"]));
 580                 label_cell($myrow['reference']);
 581                 label_cell(sql2date($myrow['tran_date']));
 582                 label_cell(sql2date($myrow['due_date']));
 583                 $name = $myrow["supplier_id"]." ".$myrow["supp_name"];
 584                 label_cell($name);
 585                 label_cell($myrow['curr_code']);
 586                 amount_cell($myrow['total']);
 587                 amount_cell($myrow['remainder']);
 588                 label_cell($myrow['days'], "align='right'");
 589                 end_row();
 590         }
 591         end_table(2);
 592 }
 593
 594 function bank_balance($today, $width)
 595 {
 596         $today = date2sql($today);
 597         $sql = "SELECT bank_act, bank_account_name, bank_curr_code, SUM(amount) balance FROM ".TB_PREF."bank_trans bt
 598                     INNER JOIN ".TB_PREF."bank_accounts ba ON bt.bank_act = ba.id
 599                     WHERE trans_date <= '$today'
 600                     AND inactive <> 1
 601                     GROUP BY bank_act, bank_account_name
 602                                 ORDER BY bank_account_name";
 603         $result = db_query($sql);
 604         $title = _("Bank Account Balances");
 605         display_title($title);
 606         $th = array(_("Account"), _("Currency"), _("Balance"));
 607         start_table(TABLESTYLE, "width='$width%'");
 608         table_header($th);
 609         $k = 0; //row colour counter
 610         while ($myrow = db_fetch($result))
 611         {
 612                 alt_table_row_color($k);
 613                 label_cell(viewer_link($myrow["bank_account_name"], 'gl/inquiry/bank_inquiry.php?bank_account='.$myrow["bank_act"]));
 614                 label_cell($myrow["bank_curr_code"]);
 615                 amount_cell($myrow['balance']);
 616                 end_row();
 617         }
 618         end_table(1);
 619 }