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