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;
16 display: inline-block;
29 background:{$colors[1]};
32 background:{$colors[2]};
45 function dashboard($sel_app)
48 if (is_object($sel_app) && !$_SESSION["wa_current_user"]->check_application_access($selected_app))
52 function chart_update(el, id)
54 var xhr = XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject('Microsoft.XMLHTTP');
55 xhr.onreadystatechange=function()
57 if (xhr.readyState==4 && xhr.status==200)
59 var xxx = document.createElement('div');
60 xxx.innerHTML = xhr.responseText;
61 document.getElementById(id).innerHTML = xxx.querySelector('#'+id).innerHTML;
64 xhr.open('POST', location.pathname, true);
65 xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
66 xhr.send('id='+id+'&'+el.id+'='+el.value+'&sel_app=$sel_app');
69 //check_for_overdue_recurrent_invoices();
70 if ($sel_app == "orders")
71 display_customer_topten();
72 elseif ($sel_app == "AP")
73 display_supplier_topten();
74 elseif ($sel_app == "stock")
75 display_stock_topten();
76 elseif ($sel_app == "manuf")
77 display_stock_topten(1);
78 elseif ($sel_app == "assets")
79 display_stock_topten(2);
80 elseif ($sel_app == "proj")
81 display_dimension_topten();
82 elseif ($sel_app == "GL")
88 function display_title($title, $id=false, $type = false, $per = '', $num = false)
93 echo "<span class='headingtext'>$title</span>";
98 echo "<span style='margin-left:10px;text-align:right;'>";
99 $options = array('select_submit' => true );
103 $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8");
104 echo array_selector($name, $per, $sel1, $options);
109 $name = "select_$id";
110 $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables"));
111 echo array_selector($name, $type, $sel, $options);
113 if (list_updated($name))
114 $Ajax->activate($id);
117 echo "<span style='margin-left:10px;text-align:right;'>";
120 $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8");
121 echo "<select id='per_$id' name='per_$id' style='border:1px solid #bbb;color:#555;' onchange='chart_update(this, \"$id\");'>";
122 foreach ($sel1 as $i => $s)
124 echo "<option value='$i'";
125 if ($num && $i == $num)
129 echo "</select> $per\n";
133 $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables"));
134 echo "<select id='select_$id' name='select_$id' style='border:1px solid #bbb;color:#555;' onchange='chart_update(this, \"$id\");'>";
135 foreach ($sel as $i => $s)
137 echo "<option value='$i'";
138 if ($type && $i == $type)
151 function table($width='100%', $twidth=false)
153 $tstyle = ($twidth != false) ? "style='width:$twidth;'" : "";
154 echo "<table width='$width'><tr valign=top><td align='center' $tstyle>";
157 function td($width=false)
159 $style = ($width != false) ? "style='width:$width;'" : "";
160 echo "</td><td align='center' $style>\n";
165 echo "</td></tr></table>\n";
168 function headers($labels)
171 foreach ($labels as $label)
172 echo "<td class='tableheader'>$label</td>\n";
176 function display_customer_topten()
178 table('100%', '25%');
179 echo "<div class='square square1'>"._('Customers')."<p class='span1'>";
180 echo get_num_customers()."</p></div>";
182 echo "<div class='square square1'>"._('Branches')."<p class='span1'>";
183 echo get_num_branches()."</p></div>";
185 echo "<div class='square square1'>"._('Salesmen')."<p class='span1'>";
186 echo get_num_salesmen()."</p></div>";
188 echo "<div class='square square2'>"._('Overdue Invoices')."<p class='span1'>";
189 echo get_num_overdue_sales()."</p></div>";
191 $pg = new chart('horizontalBar', 'c1');
192 if (isset($_POST['select_c1']))
193 $pg->type = $_POST['select_c1'];
194 $today = calc_today();
195 table('100%', '50%');
196 $title = customer_top($today, 10, 66, $pg);
197 $total = customer_aging($today, 66);
199 source_graphic($today, $title[0], _("Customer"), $pg);
201 echo "<div class='square square1'>"._('Sales Total')."<p class='span1'>";
202 echo number_format2($title[1])."</p></div>";
204 echo "<div class='square square1'>"._('Aging Total')."<p class='span1'>";
205 echo number_format2($total)."</p></div>";
207 table('100%', '100%');
208 customer_recurrent_invoices($today);
209 customer_trans($today);
213 function display_supplier_topten()
215 table('100%', '25%');
216 echo "<div class='square square2'>"._('Suppliers')."<p class='span1'>";
217 echo get_num_suppliers()."</p></div>";
219 echo "<div class='square square2'>"._('New Orders')."<p class='span1'>";
220 echo get_new_purch_orders()."</p></div>";
222 echo "<div class='square square2'>"._('Invoices')."<p class='span1'>";
223 echo get_num_supp_invoices()."</p></div>";
225 echo "<div class='square square2'>"._('Overdue Invoices')."<p class='span1'>";
226 echo get_num_overdue_purch()."</p></div>";
228 $pg = new chart('horizontalBar', 's1');
229 if (isset($_POST['select_s1']))
230 $pg->type = $_POST['select_s1'];
231 $today = calc_today();
232 table('100%', '50%');
233 $title = supplier_top($today, 10, 66, $pg);
234 $total = supplier_aging($today, 66);
236 source_graphic($today, $title[0], _("Supplier"), $pg);
238 echo "<div class='square square2'>"._('Purchable Total')."<p class='span1'>";
239 echo number_format2($title[1])."</p></div>";
241 echo "<div class='square square2'>"._('Aging Total')."<p class='span1'>";
242 echo number_format2($total)."</p></div>";
244 table('100%', '100%');
245 supplier_trans($today);
249 function display_stock_topten($type=0)
253 table('100%', '25%');
254 echo "<div class='square square1'>"._('Items')."<p class='span1'>";
255 echo get_num_items()."</p></div>";
257 echo "<div class='square square1'>"._('Locations')."<p class='span1'>";
258 echo get_num_locations()."</p></div>";
260 echo "<div class='square square1'>"._('Kits')."<p class='span1'>";
261 echo get_num_kits()."</p></div>";
263 echo "<div class='square square2'>"._('Below Reorder Level')."<p class='span1'>";
264 echo get_num_reorder()."</p></div>";
269 table('100%', '25%');
270 echo "<div class='square square1'>"._('Assembled Items')."<p class='span1'>";
271 echo get_num_assembled()."</p></div>";
273 echo "<div class='square square1'>"._('Manufactured Items')."<p class='span1'>";
274 echo get_num_manufactured()."</p></div>";
276 echo "<div class='square square1'>"._('Work Centres')."<p class='span1'>";
277 echo get_num_workcentres()."</p></div>";
279 echo "<div class='square square1'>"._('Open Workorders')."<p class='span1'>";
280 echo get_open_workorders()."</p></div>";
285 table('100%', '25%');
286 echo "<div class='square square1'>"._('Fixed Assets')."<p class='span1'>";
287 echo get_num_fixed_assets()."</p></div>";
289 echo "<div class='square square1'>"._('Locations')."<p class='span1'>";
290 echo get_num_fixed_locations()."</p></div>";
292 echo "<div class='square square1'>"._('Categories')."<p class='span1'>";
293 echo get_num_fixed_categories()."</p></div>";
295 echo "<div class='square square1'>"._('Fixed Asset Classes')."<p class='span1'>";
296 echo get_num_fixed_classes()."</p></div>";
299 $pg = new chart('bar', 'i1');
300 if (isset($_POST['select_i1']))
301 $pg->type = $_POST['select_i1'];
302 table('100%', '50%');
303 $today = calc_today();
304 $title = stock_top($today, 5, 66, $type, $pg);
306 echo "<div class='square square1'>"._('Total Sales')."<p class='span1'>";
307 echo number_format2($title[1])."</p></div>";
309 echo "<div class='square square2'>"._('Total Costs')."<p class='span1'>";
310 echo number_format2($title[2])."</p></div>";
314 $source = _("Fixed Assets");
316 $source = _("Manufacturing");
318 $source = _("Items");
319 source_graphic($today, $title[0], $source, $pg);
320 echo "<div class='square'><span>"._('Total Results')."<br />";
321 echo number_format2($title[3])."</div>";
323 table('100%', '100%');
324 stock_below_reorder($today, $type);
328 function display_dimension_topten()
330 table('100%', '25%');
331 echo "<div class='square square1'>"._('Dimensions')."<p class='span1'>";
332 echo get_num_dimensions()."</p></div>";
334 echo "<div class='square square1'>"._('Type 2 Dimensions')."<p class='span1'>";
335 echo get_dim_type2()."</p></div>";
337 $res = get_dim_total();
338 $s = $res[0] < 0.0 ? 2 : 1;
339 echo "<div class='square square{$s}'>"._('Dimensions Total Balance')."<p class='span1'>";
340 echo number_format2($res[0])."</p></div>";
342 $s = $res[1] < 0.0 ? 2 : 1;
343 echo "<div class='square square{$s}'>"._('Dimensions Total Result')."<p class='span1'>";
344 echo number_format2($res[1])."</p></div>";
346 $pg = new chart('bar', 'd1');
347 if (isset($_POST['select_d1']))
348 $pg->type = $_POST['select_d1'];
349 table('100%', '50%');
350 $today = calc_today();
351 $title = dimension_top($today, 5, 66, $pg);
353 source_graphic($today, $title, _("Dimension"), $pg);
357 function display_gl_info()
359 $today = calc_today();
360 table('100%', '25%');
361 echo "<div class='square square1'>"._('Receivables')."<p class='span1'>";
362 echo number_format2(customer_balance($today))."</p></div>";
364 echo "<div class='square square2'>"._('Payables')."<p class='span1'>";
365 echo number_format2(-supplier_balance($today))."</p></div>";
367 echo "<div class='square square1'>"._('Todays Deposits')."<p class='span1'>";
368 echo number_format2(get_today_deposits($today))."</p></div>";
370 echo "<div class='square square2'>"._('Todays Payments')."<p class='span1'>";
371 echo number_format2(get_today_payments($today))."</p></div>";
373 $pg = new chart('halfDonut', 'g1');
374 if (isset($_POST['select_g1']))
375 $pg->type = $_POST['select_g1'];
376 table('100%', '50%');
377 $title = gl_top($today, 66, $pg);
378 source_graphic($today, $title, _("Class"), $pg);
379 gl_month_performance($today, 66, 5);
381 gl_week_performance($today, 66, 4);
383 bank_balance($today, 66);
387 function display_all()
389 $today = calc_today();
391 table('100%', '25%');
392 echo "<div class='square square1'>"._('Users')."<p class='span1'>";
393 echo get_num_users()."</p></div>";
395 echo "<div class='square square1'>"._('Extensions')."<p class='span1'>";
396 echo get_num_extensions()."</p></div>";
398 echo "<div class='square square1'>"._('Languages')."<p class='span1'>";
399 echo get_num_languages()."</p></div>";
401 echo "<div class='square square1'>"._('Database Size')."<p class='span1'>";
402 echo get_database_size()."</p></div>";
404 $pg = new chart('horizontalBar', 'c2');
405 if (isset($_POST['select_c2']))
406 $pg->type = $_POST['select_c2'];
407 table('100%', '50%');
408 $title = customer_top($today, 3, 66, $pg);
409 source_graphic($today, $title[0], _("Customer"), $pg);
410 $pg = new chart('horizontalBar', 's2');
411 if (isset($_POST['select_s2']))
412 $pg->type = $_POST['select_s2'];
413 $title = supplier_top($today, 3, 66, $pg);
414 source_graphic($today, $title[0], _("Supplier"), $pg);
415 $pg = new chart('bar', 'i2');
416 if (isset($_POST['select_i2']))
417 $pg->type = $_POST['select_i2'];
418 $title = stock_top($today, 3, 66, 0, $pg);
419 source_graphic($today, $title[0], _("Items"), $pg);
421 dimension_top($today, 3, 66);
422 $pg = new chart('pie', 'd2');
423 if (isset($_POST['select_d2']))
424 $pg->type = $_POST['select_d2'];
425 $title = gl_top($today, 66, $pg);
426 source_graphic($today, $title, _("Class"), $pg);
427 stock_top($today, 3, 66, 2);
428 stock_top($today, 3, 66, 1);
432 function customer_top($today, $limit=10, $width='33', &$pg=null)
434 $begin = date2sql(begin_fiscalyear());
435 $today = date2sql($today);
436 $sql = "SELECT SUM((ov_amount + ov_discount) * rate * IF(trans.type = ".ST_CUSTCREDIT.", -1, 1)) AS total,d.debtor_no, d.name FROM ".TB_PREF."debtor_trans AS trans, ".TB_PREF."debtors_master AS d WHERE trans.debtor_no=d.debtor_no
437 AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_CUSTCREDIT.")
438 AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by d.debtor_no ORDER BY total DESC, d.debtor_no
440 $result = db_query($sql);
441 $title = sprintf(_("Top %s customers in fiscal year"), $limit);
442 display_title($title);
443 $th = array(_("Customer"), _("Amount"));
444 start_table(TABLESTYLE, "width='$width%'");
446 check_page_security('SA_SALESTRANSVIEW');
447 $k = 0; //row colour counter
449 $names = $totals = array();
451 while ($myrow = db_fetch($result))
453 $sales += round($myrow['total']);
456 alt_table_row_color($k);
457 $name = $myrow["debtor_no"]." ".htmlspecialchars_decode($myrow["name"]);
459 amount_cell($myrow['total']);
463 $totals[] = round($myrow['total']);
470 $pg->setLabels($names);
471 $pg->addSerie(_('Sales'), $totals);
474 return array($title, $sales);
477 function supplier_top($today, $limit=10, $width='33', &$pg=null)
479 $begin = date2sql(begin_fiscalyear());
480 $today = date2sql($today);
481 $sql = "SELECT SUM((trans.ov_amount + trans.ov_discount) * rate) AS total, s.supplier_id, s.supp_name FROM
482 ".TB_PREF."supp_trans AS trans, ".TB_PREF."suppliers AS s WHERE trans.supplier_id=s.supplier_id
483 AND (trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.")
484 AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by s.supplier_id ORDER BY total DESC, s.supplier_id
486 $result = db_query($sql);
487 $title = sprintf(_("Top %s suppliers in fiscal year"), $limit);
488 display_title($title);
489 $th = array(_("Supplier"), _("Amount"));
490 start_table(TABLESTYLE, "width='$width%'");
492 check_page_security('SA_SUPPTRANSVIEW');
493 $k = 0; //row colour counter
495 $names = $totals = array();
497 while ($myrow = db_fetch($result))
499 $total += $myrow['total'];
502 alt_table_row_color($k);
503 $name = $myrow["supplier_id"]." ".htmlspecialchars_decode($myrow["supp_name"]);
505 amount_cell($myrow['total']);
509 $totals[] = round($myrow['total']);
516 $pg->setLabels($names);
517 $pg->addSerie(_('Purchases'), $totals);
520 return array($title, $total);
523 function stock_top($today, $limit=10, $width='33', $type=0, &$pg=null)
526 $sec = 'SA_ASSETSANALYTIC';
528 $sec = 'SA_WORKORDERANALYTIC';
530 $sec = 'SA_ITEMSTRANSVIEW';
531 $begin = date2sql(begin_fiscalyear());
532 $today = date2sql($today);
535 $sql = "SELECT SUM((trans.unit_price * trans.quantity) * d.rate) AS total, s.stock_id, s.description,
536 SUM(trans.quantity) AS qty, SUM((s.material_cost + s.overhead_cost + s.labour_cost) * trans.quantity) AS costs FROM
537 ".TB_PREF."debtor_trans_details AS trans, ".TB_PREF."stock_master AS s, ".TB_PREF."debtor_trans AS d
538 WHERE trans.stock_id=s.stock_id AND trans.debtor_trans_type=d.type AND trans.debtor_trans_no=d.trans_no
539 AND (d.type = ".ST_SALESINVOICE." OR d.type = ".ST_CUSTCREDIT.") ";
543 $sql = "SELECT SUM(m.qty * (s.material_cost + s.labour_cost + s.overhead_cost)) AS total, s.stock_id, s.description,
544 SUM(qty) AS qty FROM ".TB_PREF."stock_master AS s, ".TB_PREF."stock_moves AS m
545 WHERE s.stock_id=m.stock_id ";
547 $sql .= "AND s.mb_flag='M' AND m.type <> ".ST_CUSTDELIVERY." AND m.type <> ".ST_CUSTCREDIT." ";
549 $sql .= "AND s.mb_flag='F' ";
552 $sql .= "AND tran_date >= '$begin' ";
553 $sql .= "AND tran_date <= '$today' GROUP by s.stock_id ORDER BY total DESC, s.stock_id LIMIT $limit";
554 $result = db_query($sql);
556 $title = sprintf(_("Top %s Manufactured Items in fiscal year"), $limit);
558 $title = sprintf(_("Top %s Fixed Assets"), $limit);
560 $title = sprintf(_("Top %s Sold Items in fiscal year"), $limit);
561 display_title($title);
563 $th = array(_("Item"), _("Sales"), _("Costs"), _("Results"), _("Quantity"));
565 $th = array(_("Item"), _("Amount"), _("Quantity"));
566 start_table(TABLESTYLE, "width='$width%'");
568 check_page_security($sec);
569 $k = 0; //row colour counter
571 $names = $totals = $costs = $results = array();
572 $sales = $cost = $res = 0;
573 while ($myrow = db_fetch($result))
575 $sales += $myrow['total'];
578 $cost += $myrow['costs'];
579 $res += ($myrow['total'] - $myrow['costs']);
583 alt_table_row_color($k);
584 $name = $myrow["description"];
586 amount_cell($myrow['total']);
589 amount_cell($myrow['costs']);
590 amount_cell($myrow['total'] - $myrow['costs']);
592 qty_cell($myrow['qty']);
596 $totals[] = round($myrow['total']);
599 $costs[] = round($myrow['costs']);
600 $results[] = round($myrow['total'] - $myrow['costs']);
608 $pg->setLabels($names);
609 $pg->addSerie(_('Sales'), $totals);
612 $pg->addSerie(_('Costs'), $costs);
613 $pg->addSerie(_('Results'), $results, 8);
617 return array($title, $sales, $cost, $res);
620 function dimension_top($today, $limit=10, $width='33', &$pg=null)
622 $begin = date2sql(begin_fiscalyear());
623 $today = date2sql($today);
625 $sql = "SELECT SUM(IF(c.ctype > 3, -amount, 0)) AS result, SUM(IF(c.ctype < 4, amount, 0)) AS balance, d.reference, d.name FROM ".TB_PREF."gl_trans g
626 LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code
627 LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type
628 LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id)
629 LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid
630 WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
631 AND tran_date <= '$today' AND d.reference IS NOT NULL GROUP BY d.reference ORDER BY d.reference DESC LIMIT $limit";
632 $result = db_query($sql, "Transactions could not be calculated");
633 $title = sprintf(_("Top %s Dimensions in fiscal year"), $limit);
634 display_title($title);
635 $th = array(_("Dimension"), _("Balance"), _("Result"));
636 start_table(TABLESTYLE, "width='$width%'");
638 check_page_security('SA_DIMTRANSVIEW');
639 $k = 0; //row colour counter
640 $names = $balances = $results = array();
641 while ($myrow = db_fetch($result))
643 alt_table_row_color($k);
644 $name = $myrow['reference']." ".$myrow["name"];
646 amount_cell($myrow['balance']);
647 amount_cell($myrow['result']);
651 $balances[] = round(abs($myrow['balance']));
652 $results[] = round(abs($myrow['result']));
658 $pg->setLabels($names);
659 $pg->setValues(true);
660 $pg->addSerie(_('Balance'), $balances);
661 $pg->addSerie(_('Result'), $results);
667 function gl_top($today, $width='33', &$pg=null)
669 $begin = date2sql(begin_fiscalyear());
670 $today = date2sql($today);
671 $sql = "SELECT SUM(IF(c.ctype > 3, -amount, amount)) AS total, c.class_name, c.ctype FROM
672 ".TB_PREF."gl_trans,".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
673 ".TB_PREF."chart_class AS c WHERE
674 account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
675 AND IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
676 AND tran_date <= '$today' GROUP BY c.cid ORDER BY c.cid";
677 $result = db_query($sql, "Transactions could not be calculated");
678 $title = _("Class Balances");
679 display_title($title);
680 start_table(TABLESTYLE2, "width='$width%'");
681 check_page_security('SA_GLANALYTIC');
683 $names = $totals = array();
684 while ($myrow = db_fetch($result))
686 if ($myrow['ctype'] > 3)
688 $total += round($myrow['total']);
691 $names[] = $myrow['class_name'];
692 $totals[] = round(abs($myrow['total']));
695 label_row($myrow['class_name'], number_format2($myrow['total'], user_price_dec()),
696 "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
698 $calculated = _("Calculated Return");
699 label_row(" ", "");
700 label_row($calculated, number_format2($total, user_price_dec()),
701 "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
704 $names[] = $calculated;
706 $pg->setLabels($names);
707 $pg->addSerie(_('Totals'), $totals);
708 $pg->setValues(true);
709 $pg->setDTitle(number_format2($total));
715 function gl_week_performance($today, $width="33", $weeks=4)
719 $pg = new chart('bar', 'g3');
720 if (isset($_POST['select_g3']))
721 $pg->type = $_POST['select_g3'];
722 if (isset($_POST['per_g3']))
723 $weeks = $_POST['per_g3'];
724 $begin = date2sql(begin_fiscalyear());
725 $today1 = date2sql($today);
726 $sep = $SysPrefs->dateseps[user_date_sep()];
727 $sql = "SELECT week_name, sales, costs
728 FROM(SELECT DATE_FORMAT(tran_date, '%X{$sep}%V') AS week_name,
729 SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales,
730 SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM
731 ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
732 ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6)
733 AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
734 AND tran_date >= '$begin' AND tran_date <= '$today1'
735 GROUP BY week_name ORDER BY week_name DESC LIMIT 0, $weeks) b
736 GROUP BY week_name ORDER BY week_name ASC";
737 $result = db_query($sql, "Transactions could not be calculated");
738 $title = sprintf(_("Last %s weeks Performance"), $weeks);
739 check_page_security('SA_GLANALYTIC');
740 $week_names = $sales = $costs = $results = array();
741 while ($myrow = db_fetch($result))
743 $week_names[] = $myrow['week_name'];
744 $sales[] = round($myrow['sales']);
745 $costs[] = round($myrow['costs']);
746 $results[] = round($myrow['sales'] - $myrow['costs']);
748 $pg->setLabels($week_names);
749 $pg->addSerie(_('Sales'), $sales, 'bar');
750 $pg->addSerie(_('Costs'), $costs, 'bar');
751 $pg->addSerie(_('Results'), $results, 8);
752 source_graphic($today, $title, _("Week"), $pg, _("Weeks"), $weeks);
755 function gl_month_performance($today, $width="33", $months=5)
758 $pg = new chart('bar', 'g4');
759 if (isset($_POST['select_g4']))
760 $pg->type = $_POST['select_g4'];
761 if (isset($_POST['per_g4']))
762 $months = $_POST['per_g4'];
763 $begin = date2sql(begin_fiscalyear());
764 $today1 = date2sql($today);
765 $sep = $SysPrefs->dateseps[user_date_sep()];
766 $sql = "SELECT month_name, sales, costs
767 FROM(SELECT DATE_FORMAT(tran_date, '%Y{$sep}%m') AS month_name,
768 SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales,
769 SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM
770 ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
771 ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6)
772 AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
773 AND tran_date >= '$begin' AND tran_date <= '$today1'
774 GROUP BY month_name ORDER BY month_name DESC LIMIT 0, $months) b
775 GROUP BY month_name ORDER BY month_name ASC";
776 $result = db_query($sql, "Transactions could not be calculated");
777 $title = sprintf(_("Last %s Months Performance"), $months);
778 check_page_security('SA_GLANALYTIC');
779 $month_names = $sales = $costs = $results = array();
780 while ($myrow = db_fetch($result))
782 $month_names[] = $myrow['month_name'];
783 $sales[] = round($myrow['sales']);
784 $costs[] = round($myrow['costs']);
785 $results[] = round($myrow['sales'] - $myrow['costs']);
787 $pg->setLabels($month_names);
788 $pg->addSerie(_('Sales'), $sales, 'bar');
789 $pg->addSerie(_('Costs'), $costs, 'bar');
790 $pg->addSerie(_('Results'), $results, 8);
791 source_graphic($today, $title, _("Month"), $pg, _("Months"), $months);
794 function customer_aging($today, $width="33")
796 $pg = new chart('bar', 'c3');
797 if (isset($_POST['select_c3']))
798 $pg->type = $_POST['select_c3'];
799 $today1 = date2sql($today);
800 $past1 = get_company_pref('past_due_days');
802 // removed - debtor_trans.alloc from all summations
803 $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
804 $value = "IFNULL($sign*((trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount -
805 trans.alloc)*trans.rate),0)";
806 $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)";
807 $sql = "SELECT Sum($value) AS Balance,
808 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due,
809 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
810 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
811 FROM ".TB_PREF."debtors_master debtor
812 LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today1' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY.","
813 .TB_PREF."payment_terms terms,"
814 .TB_PREF."credit_status credit_status
816 debtor.payment_terms = terms.terms_indicator
817 AND debtor.credit_status = credit_status.id";
818 $sql .= " AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA;
819 $result = db_query($sql,"The customer details could not be retrieved");
821 $row = db_fetch($result);
823 $title = _("Total Customers Aged Analysis");
824 check_page_security('SA_SALESTRANSVIEW');
826 $past1a = $past1 + 1;
827 $past2a = $past2 + 1;
828 $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+");
829 $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']),
830 round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2']));
831 $total = round($row['Balance']);
832 $pg->setLabels($names);
833 $pg->addSerie(_('Balances'), $balances);
834 source_graphic($today, $title, _('Days'), $pg);
838 function customer_balance($today)
840 $today = date2sql($today);
841 $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
842 $value = "IFNULL($sign*((trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount -
843 trans.alloc)*trans.rate),0)";
844 $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)";
845 $sql = "SELECT Sum($value) AS Balance
846 FROM ".TB_PREF."debtors_master debtor
847 LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY."
848 WHERE ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA;
849 $result = db_query($sql,"The customer details could not be retrieved");
850 $row = db_fetch($result);
854 function supplier_aging($today, $width="33")
856 $pg = new chart('bar', 's3');
857 if (isset($_POST['select_s3']))
858 $pg->type = $_POST['select_s3'];
859 $today1 = date2sql($today);
860 $past1 = get_company_pref('past_due_days');
862 // removed - debtor_trans.alloc from all summations
863 $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.",
864 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate,
865 (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)";
866 $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)";
867 $sql = "SELECT Sum($value) AS Balance,
868 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due,
869 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
870 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
871 FROM ".TB_PREF."suppliers supplier, ".TB_PREF."supp_trans trans
872 WHERE supplier.supplier_id = trans.supplier_id
873 AND trans.tran_date <= '$today1'
874 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA."
875 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
876 $result = db_query($sql,"The supplier details could not be retrieved");
878 $row = db_fetch($result);
880 $title = _("Total Suppliers Aged Analysis");
881 check_page_security('SA_SUPPLIERANALYTIC');
883 $past1a = $past1 + 1;
884 $past2a = $past2 + 1;
885 $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+");
886 $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']),
887 round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2']));
888 $total = round($row['Balance']);
889 $pg->setLabels($names);
890 $pg->addSerie(_('Balances'), $balances);
891 source_graphic($today, $title, _('Days'), $pg);
895 function supplier_balance($today)
897 $today = date2sql($today);
898 $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.",
899 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate,
900 (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)";
901 $sql = "SELECT Sum($value) AS Balance
902 FROM ".TB_PREF."suppliers supplier, ".TB_PREF."supp_trans trans
903 WHERE supplier.supplier_id = trans.supplier_id
904 AND trans.tran_date <= '$today'
905 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA."
906 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
907 $result = db_query($sql,"The supplier details could not be retrieved");
908 $row = db_fetch($result);
912 function cash_flow($today)
914 global $date_system, $SysPrefs;
916 $pg = new chart('spline', 'g5');
917 if (isset($_POST['select_g5']))
918 $pg->type = $_POST['select_g5'];
919 if (isset($_POST['per_g5']))
920 $months = $_POST['per_g5'];
921 if (!is_date_in_fiscalyear($today))
922 $today = end_fiscalyear();
923 $today1 = begin_month($today);
924 $today1 = add_months($today, -$months+1);
925 list($da, $mo, $yr) = explode_date_to_dmy($today1);
926 if ($date_system == 1)
927 list($yr, $mo, $da) = jalali_to_gregorian($yr, $mo, $da);
928 elseif ($date_system == 2)
929 list($yr, $mo, $da) = islamic_to_gregorian($yr, $mo, $da);
933 for ($i = 0; $i < $months; $i++)
934 $date[$i] = date('Y-m-d',mktime(0,0,0,$mo + $i,1,$yr));
936 $result = get_bank_accounts();
937 $total = array_fill(0, $months, 0);
938 $balance = array_fill(0, $months, 0);
939 while ($account=db_fetch($result))
942 for ($i = 0; $i < $months; $i++)
943 $sql .= " SUM(CASE WHEN trans_date < '$date[$i]' THEN amount ELSE 0 END) AS per0".($i+1).",";
944 $sql = substr($sql, 0, -1);
945 $sql .= " FROM ".TB_PREF."bank_trans
946 WHERE bank_act=".$account['id'];
947 $res = db_query($sql, "Transactions for bank account could not be calculated");
948 $bal = db_fetch($res);
949 $is_home = is_company_currency($account['bank_curr_code']);
950 for ($i = 1; $i <= $months; $i++)
951 $balance[$i - 1] = $bal['per0'.$i];
952 for ($i = 0; $i < $months; $i++)
955 $balance[$i] = to_home_currency($balance[$i], $account['bank_curr_code'], sql2date($date[$i]));
956 $total[$i] += $balance[$i];
959 $sep = $SysPrefs->dateseps[user_date_sep()];
960 for ($i = 0; $i < $months; $i++)
962 $y = substr($date[$i], 0, 4);
963 $m = substr($date[$i], 5, 2);
964 $date[$i] = $y.$sep.$m;
965 $total[$i] = round($total[$i]);
967 $title = sprintf(_("Last %s Months Cash Flow"), $months);
968 check_page_security('SA_BANKREP');
969 $pg->setLabels($date);
970 $pg->addSerie(_('Balances'), $total, false, true);
971 source_graphic($today, $title, _("Month"), $pg, _("Months"), $months);
974 function source_graphic($today, $title, $x_axis, $pg, $per = '', $num = false)
978 //$today = sql2date($today);
979 display_title("$title ($today)", $pg->id, $pg->type, $per, $num);
982 display_note(_("No Data available yet!"), 1);
985 if ($pg->type == 'horizontalBar')
987 $pg->setYTitle($x_axis);
988 $pg->setXTitle(_("Amount"));
992 $pg->setXTitle($x_axis);
993 $pg->setYTitle(_("Amount"));
1001 function customer_trans($today)
1003 $today = date2sql($today);
1005 $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, debtor.debtor_no,
1006 debtor.name, branch.br_name, debtor.curr_code,
1007 (trans.ov_amount + trans.ov_gst + trans.ov_freight
1008 + trans.ov_freight_tax + trans.ov_discount) AS total,
1009 (trans.ov_amount + trans.ov_gst + trans.ov_freight
1010 + trans.ov_freight_tax + trans.ov_discount - trans.alloc) AS remainder,
1011 DATEDIFF('$today', trans.due_date) AS days
1012 FROM ".TB_PREF."debtor_trans as trans, ".TB_PREF."debtors_master as debtor,
1013 ".TB_PREF."cust_branch as branch
1014 WHERE debtor.debtor_no = trans.debtor_no AND trans.branch_code = branch.branch_code
1015 AND trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
1016 + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA."
1017 AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC";
1018 $result = db_query($sql);
1019 $title = db_num_rows($result) . _(" overdue Sales Invoices");
1020 display_title($title);
1021 $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Customer"), _("Branch"), _("Currency"),
1022 _("Total"), _("Remainder"), _("Days"));
1023 start_table(TABLESTYLE, "width=90%");
1025 $k = 0; //row colour counter
1026 while ($myrow = db_fetch($result))
1028 alt_table_row_color($k);
1029 label_cell(get_trans_view_str(ST_SALESINVOICE, $myrow["trans_no"]));
1030 label_cell($myrow['reference']);
1031 label_cell(sql2date($myrow['tran_date']));
1032 label_cell(sql2date($myrow['due_date']));
1033 $name = $myrow["debtor_no"]." ".$myrow["name"];
1035 label_cell($myrow['br_name']);
1036 label_cell($myrow['curr_code']);
1037 amount_cell($myrow['total']);
1038 amount_cell($myrow['remainder']);
1039 label_cell($myrow['days'], "align='right'");
1045 function calculate_next_invoice($myrow)
1047 if ($myrow["last_sent"] == '0000-00-00')
1048 $next = sql2date($myrow["begin"]);
1050 $next = sql2date($myrow["last_sent"]);
1051 $next = add_months($next, $myrow['monthly']);
1052 $next = add_days($next, $myrow['days']);
1053 return add_days($next,-1);
1056 function customer_recurrent_invoices($today)
1058 $result = get_recurrent_invoices($today);
1059 $title = _("Overdue Recurrent Invoices");
1060 display_title($title);
1061 $th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Next invoice"));
1062 start_table(TABLESTYLE, "width=90%");
1065 while ($myrow = db_fetch($result))
1067 if (!$myrow['overdue'])
1069 alt_table_row_color($k);
1071 label_cell($myrow["description"]);
1072 label_cell(get_customer_trans_view_str(ST_SALESORDER, $myrow["order_no"]));
1073 if ($myrow["debtor_no"] == 0)
1077 label_cell(get_sales_group_name($myrow["group_no"]));
1081 label_cell(get_customer_name($myrow["debtor_no"]));
1082 label_cell(get_branch_name($myrow['group_no']));
1084 label_cell(calculate_next_invoice($myrow), "align='center'");
1090 function supplier_trans($today)
1092 $today = date2sql($today);
1093 $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, s.supplier_id,
1094 s.supp_name, s.curr_code,
1095 (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS total,
1096 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) AS remainder,
1097 DATEDIFF('$today', trans.due_date) AS days
1098 FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as s
1099 WHERE s.supplier_id = trans.supplier_id
1100 AND trans.type = ".ST_SUPPINVOICE." AND (ABS(trans.ov_amount + trans.ov_gst +
1101 trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA."
1102 AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC";
1103 $result = db_query($sql);
1104 $title = db_num_rows($result) . _(" overdue Purchase Invoices");
1105 display_title($title);
1106 $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Supplier"), _("Currency"), _("Total"),
1107 _("Remainder"), _("Days"));
1108 start_table(TABLESTYLE, "width=90%");
1110 $k = 0; //row colour counter
1111 while ($myrow = db_fetch($result))
1113 alt_table_row_color($k);
1114 label_cell(get_trans_view_str(ST_SUPPINVOICE, $myrow["trans_no"]));
1115 label_cell($myrow['reference']);
1116 label_cell(sql2date($myrow['tran_date']));
1117 label_cell(sql2date($myrow['due_date']));
1118 $name = $myrow["supplier_id"]." ".$myrow["supp_name"];
1120 label_cell($myrow['curr_code']);
1121 amount_cell($myrow['total']);
1122 amount_cell($myrow['remainder']);
1123 label_cell($myrow['days'], "align='right'");
1129 function stock_below_reorder($today, $type)
1131 $MB = ($type == 0 ? 'B' : ($type == 1 ? 'M' : 'F'));
1132 $today = date2sql($today);
1134 $sql = "SELECT st.loc_code, l.location_name, st.stock_id, sm.description, reorders.reorder_level AS r, SUM(st.qty) AS q
1135 FROM ".TB_PREF."stock_moves st
1136 LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id
1137 LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id
1138 LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND
1139 reorders.stock_id = st.stock_id
1140 LEFT JOIN ".TB_PREF."locations l ON l.loc_code = st.loc_code
1141 WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B'
1142 GROUP BY sm.stock_id HAVING q <= r";
1143 $result = db_query($sql,"an item reorder could not be retreived");
1144 $title = db_num_rows($result) . _(" items are below Reorder Level");
1145 display_title($title);
1146 $th = array("#", _("Location"), _("Stock Id"), _('Description'), _("Reorder"), _("qty"));
1147 start_table(TABLESTYLE, "width=90%");
1149 $k = 0; //row colour counter
1150 while ($myrow = db_fetch($result))
1152 alt_table_row_color($k);
1153 label_cell($myrow['loc_code']);
1154 label_cell($myrow['location_name']);
1155 label_cell($myrow['stock_id']);
1156 label_cell($myrow['description']);
1157 $dec = get_qty_dec($myrow['stock_id']);
1158 qty_cell($myrow['r'], false, $dec);
1159 qty_cell($myrow['q'], false, $dec);
1165 function bank_balance($today, $width)
1167 $today = date2sql($today);
1168 $sql = "SELECT bank_act, bank_account_name, bank_curr_code, SUM(amount) balance FROM ".TB_PREF."bank_trans bt
1169 INNER JOIN ".TB_PREF."bank_accounts ba ON bt.bank_act = ba.id
1170 WHERE trans_date <= '$today'
1172 GROUP BY bank_act, bank_account_name
1173 ORDER BY bank_account_name";
1174 $result = db_query($sql);
1175 $title = _("Bank Account Balances");
1176 display_title($title);
1177 $th = array(_("Account"), _("Currency"), _("Balance"));
1178 start_table(TABLESTYLE, "width='$width%'");
1180 $k = 0; //row colour counter
1181 while ($myrow = db_fetch($result))
1183 alt_table_row_color($k);
1184 label_cell(viewer_link($myrow["bank_account_name"], 'gl/inquiry/bank_inquiry.php?bank_account='.$myrow["bank_act"]));
1185 label_cell($myrow["bank_curr_code"]);
1186 amount_cell($myrow['balance']);
1192 function get_num_customers()
1194 $sql = "SELECT count(*) FROM ".TB_PREF."debtors_master";
1195 $result = db_query($sql, "Fail in count customers");
1196 $row = db_fetch($result);
1200 function get_num_branches()
1202 $sql = "SELECT count(*) FROM ".TB_PREF."cust_branch";
1203 $result = db_query($sql, "Fail in count branches");
1204 $row = db_fetch($result);
1208 function get_num_salesmen()
1210 $sql = "SELECT count(*) FROM ".TB_PREF."salesman";
1211 $result = db_query($sql, "Fail in count salesmen");
1212 $row = db_fetch($result);
1216 function get_num_overdue_sales()
1218 $today = date2sql(calc_today());
1220 $sql = "SELECT COUNT(trans.trans_no)
1221 FROM ".TB_PREF."debtor_trans as trans
1222 WHERE trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
1223 + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA."
1224 AND DATEDIFF('$today', trans.due_date) > 0";
1225 $result = db_query($sql);
1226 $row = db_fetch($result);
1230 function get_num_suppliers()
1232 $sql = "SELECT count(*) FROM ".TB_PREF."suppliers";
1233 $result = db_query($sql, "Fail in count suppliers");
1234 $row = db_fetch($result);
1238 function get_new_purch_orders()
1240 $sql = "SELECT count(*) FROM ".TB_PREF."purch_orders po, ".TB_PREF."purch_order_details pd WHERE po.order_no = pd.order_no AND
1241 quantity_received < quantity_ordered";
1242 $result = db_query($sql, "Fail in count purch orders");
1243 $row = db_fetch($result);
1247 function get_num_supp_invoices()
1249 $sql = "SELECT count(*) FROM ".TB_PREF."supp_trans WHERE (ov_amount+ov_discount+ov_gst - alloc) <> 0";
1250 $result = db_query($sql, "Fail in count purch invoices");
1251 $row = db_fetch($result);
1255 function get_num_overdue_purch()
1257 $today = date2sql(calc_today());
1259 $sql = "SELECT COUNT(*) FROM ".TB_PREF."supp_trans WHERE (ov_amount+ov_discount+ov_gst - alloc) > ".FLOAT_COMP_DELTA." AND DATEDIFF('$today', due_date) > 0";
1260 $result = db_query($sql, "Fail in count purch invoices");
1261 $row = db_fetch($result);
1265 function get_num_items()
1267 $sql = "SELECT count(*) FROM ".TB_PREF."stock_master";
1268 $result = db_query($sql, "Fail in count stock master");
1269 $row = db_fetch($result);
1273 function get_num_locations()
1275 $sql = "SELECT count(*) FROM ".TB_PREF."locations";
1276 $result = db_query($sql, "Fail in count locations");
1277 $row = db_fetch($result);
1281 function get_num_kits()
1283 $sql="SELECT count(*) FROM ".TB_PREF."item_codes";
1285 $result = db_query($sql, "Fail in count kits");
1286 $row = db_fetch($result);
1290 function get_num_reorder()
1293 $today = date2sql(calc_today());
1295 $sql = "SELECT st.stock_id, reorders.reorder_level AS r, SUM(st.qty) AS q
1296 FROM ".TB_PREF."stock_moves st
1297 LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id
1298 LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id
1299 LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND
1300 reorders.stock_id = st.stock_id
1301 WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B'
1302 GROUP BY st.stock_id HAVING q <= r";
1303 $result = db_query($sql,"an item reorder could not be retreived");
1304 return db_num_rows($result);
1307 function get_num_assembled()
1309 $begin = date2sql(begin_fiscalyear());
1310 $today = date2sql(calc_today());
1311 $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ASSEMBLY." AND released_date >= '$begin'
1312 AND released_date <= '$today'";
1313 $result = db_query($sql, "Fail in count workorders");
1314 $row = db_fetch($result);
1318 function get_num_manufactured()
1320 $begin = date2sql(begin_fiscalyear());
1321 $today = date2sql(calc_today());
1322 $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ADVANCED." AND released_date >= '$begin'
1323 AND released_date <= '$today'";
1324 $result = db_query($sql, "Fail in count workorders");
1325 $row = db_fetch($result);
1329 function get_num_workcentres()
1331 $sql = "SELECT count(*) FROM ".TB_PREF."workcentres";
1332 $result = db_query($sql, "Fail in count work centres");
1333 $row = db_fetch($result);
1337 function get_open_workorders()
1339 $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 0";
1340 $result = db_query($sql, "Fail in count workorders");
1341 $row = db_fetch($result);
1345 function get_num_fixed_assets()
1347 $sql = "SELECT count(*) FROM ".TB_PREF."stock_master WHERE mb_flag = 'F'";
1348 $result = db_query($sql, "Fail in count fixed assets");
1349 $row = db_fetch($result);
1353 function get_num_fixed_locations()
1355 $sql = "SELECT count(*) FROM ".TB_PREF."stock_master s LEFT JOIN ".TB_PREF."loc_stock l ON s.stock_id = l.stock_id
1356 WHERE mb_flag = 'F'";
1357 $result = db_query($sql, "Fail in count locations");
1358 $row = db_fetch($result);
1362 function get_num_fixed_categories()
1364 $sql = "SELECT count(*) FROM ".TB_PREF."stock_category c LEFT JOIN ".TB_PREF."stock_master s ON c.category_id = s.category_id
1365 WHERE mb_flag = 'F'";
1366 $result = db_query($sql, "Fail in count categories");
1367 $row = db_fetch($result);
1371 function get_num_fixed_classes()
1373 $sql = "SELECT count(*) FROM ".TB_PREF."stock_fa_class";
1374 $result = db_query($sql, "Fail in count fa classes");
1375 $row = db_fetch($result);
1379 function get_num_dimensions()
1381 $sql = "SELECT count(*) FROM ".TB_PREF."dimensions";
1382 $result = db_query($sql, "Fail in count dimensions");
1383 $row = db_fetch($result);
1387 function get_dim_type2()
1389 $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE type_ = 2";
1390 $result = db_query($sql, "Fail in count simensions 2");
1391 $row = db_fetch($result);
1395 function get_dim_closed()
1397 $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE closed = 1";
1398 $result = db_query($sql, "Fail in count closed dimensions");
1399 $row = db_fetch($result);
1403 function get_dim_total()
1405 $begin = date2sql(begin_fiscalyear());
1406 $today = date2sql(calc_today());
1408 $sql = "SELECT SUM(IF(c.ctype > 3, -amount, 0)) AS result, SUM(IF(c.ctype < 4, amount, 0)) AS balance, d.reference, d.name FROM ".TB_PREF."gl_trans g
1409 LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code
1410 LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type
1411 LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id)
1412 LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid
1413 WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
1414 AND tran_date <= '$today' AND d.reference IS NOT NULL GROUP BY d.reference";
1416 $result = db_query($sql, "Transactions could not be calculated");
1417 while ($row = db_fetch($result))
1419 $bal += $row['balance'];
1420 $res += $row['result'];
1422 return array(round($bal), round($res));
1425 function get_today_deposits($today)
1427 $today= date2sql($today);
1428 $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba
1429 WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount > 0.0";
1430 $result = db_query($sql, "could not retrieve today deposits");
1431 if ($result == false)
1435 $row = db_fetch($result);
1440 function get_today_payments($today)
1442 $today= date2sql($today);
1443 $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba
1444 WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount < 0.0";
1445 $result = db_query($sql, "could not retrieve today deposits");
1446 if ($result == false)
1450 $row = db_fetch($result);
1455 function get_num_users()
1457 $sql = "SELECT count(*) FROM ".TB_PREF."users";
1458 $result = db_query($sql, "could not get num users");
1459 $row = db_fetch($result);
1463 function get_num_extensions()
1465 global $installed_extensions;
1466 return count($installed_extensions);
1469 function get_num_languages()
1471 global $installed_languages;
1472 return count($installed_languages);
1475 function get_database_size()
1477 global $db_connections;
1478 $c = user_company();
1479 $dbase = $db_connections[$c]['dbname'];
1480 $tb_pref = $db_connections[$c]['tbpref'];
1481 $sql = "SHOW TABLE STATUS FROM $dbase";
1482 if (!empty($tb_pref))
1483 $sql .= " LIKE '{$tb_pref}%'";
1484 $result = db_query($sql, "could not get database size");
1486 while ($row = db_fetch($result)) {
1487 $size += $row['Data_length'];
1488 $size += $row['Index_length'];
1490 return number_format2(($size / 1024 / 1024), 2).' MB';
1493 function calc_today()
1496 if (!is_date_in_fiscalyear($today))
1497 $today = end_fiscalyear();