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 ***********************************************************************/
13 function dashboard($sel_app)
15 $selected_app = $sel_app;
18 if (is_object($sel_app) && !$_SESSION["wa_current_user"]->check_application_access($selected_app))
20 // first have a look through the directory,
21 // and remove old temporary pdfs and pngs
22 $dir = company_path(). '/pdf_files';
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);
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")
56 function display_title($title)
59 display_heading($title);
61 //echo "<tr><td colspan=$colspan class='headingtext' style='background-color:#fff;text-align:center;border:0;height:40px;'>$title</td></tr>\n";
66 echo "<table width='100%'>";
67 echo "<tr valign=top><td style='width:50%'>\n"; // outer table
72 echo "</td><td style='width:50%'>\n";
77 echo "</td></tr></table>\n";
80 function display_customer_topten()
86 $title = customer_top($today, 10, 66, $pg);
87 customer_trans($today);
89 source_graphic($today, $title, _("Customer"), $pg, _("Sales"));
90 customer_recurrent_invoices($today);
94 function display_supplier_topten()
100 $title = supplier_top($today, 10, 66, $pg);
101 supplier_trans($today);
103 source_graphic($today, $title, _("Supplier"), $pg, _("Purchase"));
107 function display_stock_topten($type=0)
113 $title = stock_top($today, 10, 66, $type, $pg);
116 $source = _("Fixed Assets");
118 $source = _("Manufacturing");
120 $source = _("Items");
121 source_graphic($today, $title, $source, $pg, ($type == 0 ? _("Sales") : _("Amount")),
122 ($type == 0 ? _("Costs") : null));
126 function display_dimension_topten()
132 $title = dimension_top($today, 10, 66, $pg);
134 source_graphic($today, $title, _("Dimension"), $pg, _("Performance"), null, 5);
138 function display_gl_info()
144 $title = gl_top($today, 66, $pg);
145 bank_balance($today, 66);
147 source_graphic($today, $title, _("Class"), $pg, _("Amount"), null, 5);
148 gl_performance($today, 66, 5);
152 function display_all()
158 $title = customer_top($today, 3, 66, $pg);
159 source_graphic($today, $title, _("Customer"), $pg, _("Sales"));
161 $title = supplier_top($today, 3, 66, $pg);
162 source_graphic($today, $title, _("Supplier"), $pg, _("Purchases"));
164 $title = stock_top($today, 3, 66, 0, $pg);
165 source_graphic($today, $title, _("Items"), $pg, _("Sales"), _("Costs"));
167 dimension_top($today, 3, 66);
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);
176 function customer_top($today, $limit=10, $width="33", &$pg=null)
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
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%'");
192 check_page_security('SA_SALESTRANSVIEW');
193 $k = 0; //row colour counter
195 while ($myrow = db_fetch($result))
197 alt_table_row_color($k);
198 $name = $myrow["debtor_no"]." ".$myrow["name"];
200 amount_cell($myrow['total']);
204 $pg->y[$i] = $myrow['total'];
213 function supplier_top($today, $limit=10, $width="33", &$pg=null)
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
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%'");
229 check_page_security('SA_SUPPTRANSVIEW');
230 $k = 0; //row colour counter
232 while ($myrow = db_fetch($result))
234 alt_table_row_color($k);
235 $name = $myrow["supplier_id"]." ".$myrow["supp_name"];
237 amount_cell($myrow['total']);
241 $pg->y[$i] = $myrow['total'];
250 function stock_top($today, $limit=10, $width="33", $type=0, &$pg=null)
253 $sec = 'SA_ASSETSANALYTIC';
255 $sec = 'SA_WORKORDERANALYTIC';
257 $sec = 'SA_ITEMSTRANSVIEW';
258 $begin = begin_fiscalyear();
259 $begin1 = date2sql($begin);
260 $today1 = date2sql($today);
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.") ";
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 ";
275 $sql .= "AND s.mb_flag='M' AND m.type <> ".ST_CUSTDELIVERY." AND m.type <> ".ST_CUSTCREDIT." ";
277 $sql .= "AND s.mb_flag='F' ";
280 $sql .= "AND tran_date >= '$begin1' ";
281 $sql .= "AND tran_date <= '$today1' GROUP by s.stock_id ORDER BY total DESC, s.stock_id
283 $result = db_query($sql);
285 $title = sprintf(_("Top %s Manufactured Items in fiscal year"), $limit);
287 $title = sprintf(_("Top %s Fixed Assets"), $limit);
289 $title = sprintf(_("Top %s Sold Items in fiscal year"), $limit);
290 display_title($title);
292 $th = array(_("Item"), _("Sales"), _("Costs"), _("Quantity"));
294 $th = array(_("Item"), _("Amount"), _("Quantity"));
295 start_table(TABLESTYLE, "width='$width%'");
297 check_page_security($sec);
298 $k = 0; //row colour counter
300 while ($myrow = db_fetch($result))
302 alt_table_row_color($k);
303 $name = $myrow["description"];
305 amount_cell($myrow['total']);
307 amount_cell($myrow['costs']);
308 qty_cell($myrow['qty']);
312 $pg->y[$i] = $myrow['total'];
314 $pg->z[$i] = $myrow['costs'];
323 function dimension_top($today, $limit=10, $width="33", &$pg=null)
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%'");
339 check_page_security('SA_DIMTRANSVIEW');
340 $k = 0; //row colour counter
342 while ($myrow = db_fetch($result))
344 alt_table_row_color($k);
345 $name = $myrow['reference']." ".$myrow["name"];
347 amount_cell($myrow['total']);
351 $pg->y[$i] = abs($myrow['total']);
360 function gl_top($today, $width="33", &$pg=null)
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');
378 while ($myrow = db_fetch($result))
380 if ($myrow['ctype'] > 3)
382 $total += $myrow['total'];
383 $myrow['total'] = -$myrow['total'];
386 $pg->x[$i] = $myrow['class_name'];
387 $pg->y[$i] = abs($myrow['total']);
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");
394 $calculated = _("Calculated Return");
395 label_row(" ", "");
396 label_row($calculated, number_format2(-$total, user_price_dec()),
397 "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
400 $pg->x[$i] = $calculated;
401 $pg->y[$i] = -$total;
407 function gl_performance($today, $width="33", $weeks=5)
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');
430 while ($myrow = db_fetch($result))
432 $pg->x[$i] = $myrow['week_name'];
433 $pg->y[$i] = $myrow['sales'];
434 $pg->z[$i] = $myrow['costs'];
438 source_graphic($today, $title, _("Week"), $pg, _("Sales"), _("Costs"), 1);
441 function source_graphic($today, $title, $x_axis, $pg, $graphic1, $graphic2=null, $type=2)
443 if (count($pg->y) ==0 || (count($pg->y) == 1 && $pg->y[0] == 0))
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;
454 $pg->built_in = false;
455 $filename = company_path(). "/pdf_files/". random_id().".png";
456 $pg->display($filename, true);
457 start_table(TABLESTYLE);
460 echo "<img src='$filename' border='0' alt='$title'>";
466 function customer_trans($today)
468 $today = date2sql($today);
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);
490 $k = 0; //row colour counter
491 while ($myrow = db_fetch($result))
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"];
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'");
510 function calculate_next_invoice($myrow)
512 if ($myrow["last_sent"] == '0000-00-00')
513 $next = sql2date($myrow["begin"]);
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);
521 function customer_recurrent_invoices($today)
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%");
530 while ($myrow = db_fetch($result))
532 if (!$myrow['overdue'])
534 alt_table_row_color($k);
536 label_cell($myrow["description"]);
537 label_cell(get_customer_trans_view_str(ST_SALESORDER, $myrow["order_no"]));
538 if ($myrow["debtor_no"] == 0)
542 label_cell(get_sales_group_name($myrow["group_no"]));
546 label_cell(get_customer_name($myrow["debtor_no"]));
547 label_cell(get_branch_name($myrow['group_no']));
549 label_cell(calculate_next_invoice($myrow), "align='center'");
555 function supplier_trans($today)
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);
575 $k = 0; //row colour counter
576 while ($myrow = db_fetch($result))
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"];
585 label_cell($myrow['curr_code']);
586 amount_cell($myrow['total']);
587 amount_cell($myrow['remainder']);
588 label_cell($myrow['days'], "align='right'");
594 function bank_balance($today, $width)
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'
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%'");
609 $k = 0; //row colour counter
610 while ($myrow = db_fetch($result))
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']);