2 /**********************************************************************
3 Copyright (C) FrontAccounting, LLC.
4 Released under the terms of the GNU General Public License, GPL,
5 as published by the Free Software Foundation, either version 3
6 of the License, or (at your option) any later version.
7 This program is distributed in the hope that it will be useful,
8 but WITHOUT ANY WARRANTY; without even the implied warranty of
9 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
10 See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
11 ***********************************************************************/
12 //$colors = array('#008cc9','#4db625','#ef5500','#eef100','#05c6e6', '#5ee66a'); // Current in Use
13 $colors = Chart::$palette;
14 $align = $_SESSION['language']->dir == 'rtl' ? 'right' : 'left';
17 display: inline-block;
20 padding: 3px 5px 0 5px;
30 background:{$colors[1]};
33 background:{$colors[2]};
46 function dashboard($sel_app)
49 if (is_object($sel_app) && !$_SESSION["wa_current_user"]->check_application_access($selected_app))
53 function chart_update(el, id)
55 var xhr = XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject('Microsoft.XMLHTTP');
56 xhr.onreadystatechange=function()
58 if (xhr.readyState==4 && xhr.status==200)
60 var xxx = document.createElement('div');
61 xxx.innerHTML = xhr.responseText;
62 document.getElementById(id).innerHTML = xxx.querySelector('#'+id).innerHTML;
65 xhr.open('POST', location.pathname, true);
66 xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
67 xhr.send('id='+id+'&'+el.id+'='+el.value+'&sel_app=$sel_app');
70 //check_for_overdue_recurrent_invoices();
71 if ($sel_app == "orders")
72 display_customer_topten();
73 elseif ($sel_app == "AP")
74 display_supplier_topten();
75 elseif ($sel_app == "stock")
76 display_stock_topten();
77 elseif ($sel_app == "manuf")
78 display_stock_topten(1);
79 elseif ($sel_app == "assets")
80 display_stock_topten(2);
81 elseif ($sel_app == "proj")
82 display_dimension_topten();
83 elseif ($sel_app == "GL")
89 function display_title($title, $id=false, $type = false, $per = '', $num = false)
94 echo "<span class='headingtext'>$title</span>";
99 echo "<span style='margin-left:10px;text-align:right;'>";
100 $options = array('select_submit' => true );
104 $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8");
105 echo array_selector($name, $per, $sel1, $options);
110 $name = "select_$id";
111 $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables"));
112 echo array_selector($name, $type, $sel, $options);
114 if (list_updated($name))
115 $Ajax->activate($id);
118 echo "<span style='margin-left:10px;text-align:right;'>";
121 $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8");
122 echo "<select id='per_$id' name='per_$id' style='border:1px solid #bbb;color:#555;' onchange='chart_update(this, \"$id\");'>";
123 foreach ($sel1 as $i => $s)
125 echo "<option value='$i'";
126 if ($num && $i == $num)
130 echo "</select> $per\n";
134 $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables"));
135 echo "<select id='select_$id' name='select_$id' style='border:1px solid #bbb;color:#555;' onchange='chart_update(this, \"$id\");'>";
136 foreach ($sel as $i => $s)
138 echo "<option value='$i'";
139 if ($type && $i == $type)
152 function table($width='100%', $twidth=false)
154 $tstyle = ($twidth != false) ? "style='width:$twidth;'" : "";
155 echo "<table width='$width'><tr valign=top><td align='center' $tstyle>";
158 function td($width=false)
160 $style = ($width != false) ? "style='width:$width;'" : "";
161 echo "</td><td align='center' $style>\n";
166 echo "</td></tr></table>\n";
169 function headers($labels)
172 foreach ($labels as $label)
173 echo "<td class='tableheader'>$label</td>\n";
177 function display_customer_topten()
179 table('100%', '25%');
180 echo "<div class='square square1'>"._('Customers')."<p class='span1'>";
181 echo get_num_customers()."</p></div>";
183 echo "<div class='square square1'>"._('Branches')."<p class='span1'>";
184 echo get_num_branches()."</p></div>";
186 echo "<div class='square square1'>"._('Salesmen')."<p class='span1'>";
187 echo get_num_salesmen()."</p></div>";
189 echo "<div class='square square2'>"._('Overdue Invoices')."<p class='span1'>";
190 echo get_num_overdue_sales()."</p></div>";
192 $pg = new chart('horizontalBar', 'c1');
193 if (isset($_POST['select_c1']))
194 $pg->type = $_POST['select_c1'];
195 $today = calc_today();
196 table('100%', '50%');
197 $title = customer_top($today, 10, 66, $pg);
198 $total = customer_aging($today, 66);
200 source_graphic($today, $title[0], _("Customer"), $pg);
202 echo "<div class='square square1'>"._('Sales Total')."<p class='span1'>";
203 echo number_format2($title[1])."</p></div>";
205 echo "<div class='square square1'>"._('Aging Total')."<p class='span1'>";
206 echo number_format2($total)."</p></div>";
208 table('100%', '100%');
209 customer_recurrent_invoices($today);
210 customer_trans($today);
214 function display_supplier_topten()
216 table('100%', '25%');
217 echo "<div class='square square2'>"._('Suppliers')."<p class='span1'>";
218 echo get_num_suppliers()."</p></div>";
220 echo "<div class='square square2'>"._('New Orders')."<p class='span1'>";
221 echo get_new_purch_orders()."</p></div>";
223 echo "<div class='square square2'>"._('Invoices')."<p class='span1'>";
224 echo get_num_supp_invoices()."</p></div>";
226 echo "<div class='square square2'>"._('Overdue Invoices')."<p class='span1'>";
227 echo get_num_overdue_purch()."</p></div>";
229 $pg = new chart('horizontalBar', 's1');
230 if (isset($_POST['select_s1']))
231 $pg->type = $_POST['select_s1'];
232 $today = calc_today();
233 table('100%', '50%');
234 $title = supplier_top($today, 10, 66, $pg);
235 $total = supplier_aging($today, 66);
237 source_graphic($today, $title[0], _("Supplier"), $pg);
239 echo "<div class='square square2'>"._('Purchasable Total')."<p class='span1'>";
240 echo number_format2($title[1])."</p></div>";
242 echo "<div class='square square2'>"._('Aging Total')."<p class='span1'>";
243 echo number_format2($total)."</p></div>";
245 table('100%', '100%');
246 supplier_trans($today);
250 function display_stock_topten($type=0)
254 table('100%', '25%');
255 echo "<div class='square square1'>"._('Items')."<p class='span1'>";
256 echo get_num_items()."</p></div>";
258 echo "<div class='square square1'>"._('Locations')."<p class='span1'>";
259 echo get_num_locations()."</p></div>";
261 echo "<div class='square square1'>"._('Kits')."<p class='span1'>";
262 echo get_num_kits()."</p></div>";
264 echo "<div class='square square2'>"._('Below Reorder Level')."<p class='span1'>";
265 echo get_num_reorder()."</p></div>";
270 table('100%', '25%');
271 echo "<div class='square square1'>"._('Assembled Items')."<p class='span1'>";
272 echo get_num_assembled()."</p></div>";
274 echo "<div class='square square1'>"._('Manufactured Items')."<p class='span1'>";
275 echo get_num_manufactured()."</p></div>";
277 echo "<div class='square square1'>"._('Work Centres')."<p class='span1'>";
278 echo get_num_workcentres()."</p></div>";
280 echo "<div class='square square1'>"._('Open Workorders')."<p class='span1'>";
281 echo get_open_workorders()."</p></div>";
286 table('100%', '25%');
287 echo "<div class='square square1'>"._('Fixed Assets')."<p class='span1'>";
288 echo get_num_fixed_assets()."</p></div>";
290 echo "<div class='square square1'>"._('Locations')."<p class='span1'>";
291 echo get_num_fixed_locations()."</p></div>";
293 echo "<div class='square square1'>"._('Categories')."<p class='span1'>";
294 echo get_num_fixed_categories()."</p></div>";
296 echo "<div class='square square1'>"._('Fixed Asset Classes')."<p class='span1'>";
297 echo get_num_fixed_classes()."</p></div>";
300 $pg = new chart('bar', 'i1');
301 if (isset($_POST['select_i1']))
302 $pg->type = $_POST['select_i1'];
303 table('100%', '50%');
304 $today = calc_today();
305 $title = stock_top($today, 5, 66, $type, $pg);
307 echo "<div class='square square1'>"._('Total Sales')."<p class='span1'>";
308 echo number_format2($title[1])."</p></div>";
310 echo "<div class='square square2'>"._('Total Costs')."<p class='span1'>";
311 echo number_format2($title[2])."</p></div>";
315 $source = _("Fixed Assets");
317 $source = _("Manufacturing");
319 $source = _("Items");
320 source_graphic($today, $title[0], $source, $pg);
321 echo "<div class='square'><span>"._('Total Results')."<br />";
322 echo number_format2($title[3])."</div>";
324 table('100%', '100%');
325 stock_below_reorder($today, $type);
329 function display_dimension_topten()
331 table('100%', '25%');
332 echo "<div class='square square1'>"._('Dimensions')."<p class='span1'>";
333 echo get_num_dimensions()."</p></div>";
335 echo "<div class='square square1'>"._('Type 2 Dimensions')."<p class='span1'>";
336 echo get_dim_type2()."</p></div>";
338 $res = get_dim_total();
339 $s = $res[0] < 0.0 ? 2 : 1;
340 echo "<div class='square square{$s}'>"._('Dimensions Total Balance')."<p class='span1'>";
341 echo number_format2($res[0])."</p></div>";
343 $s = $res[1] < 0.0 ? 2 : 1;
344 echo "<div class='square square{$s}'>"._('Dimensions Total Result')."<p class='span1'>";
345 echo number_format2($res[1])."</p></div>";
347 $pg = new chart('bar', 'd1');
348 if (isset($_POST['select_d1']))
349 $pg->type = $_POST['select_d1'];
350 table('100%', '50%');
351 $today = calc_today();
352 $title = dimension_top($today, 5, 66, $pg);
354 source_graphic($today, $title, _("Dimension"), $pg);
358 function display_gl_info()
360 $today = calc_today();
361 table('100%', '25%');
362 echo "<div class='square square1'>"._('Receivables')."<p class='span1'>";
363 echo number_format2(customer_balance($today))."</p></div>";
365 echo "<div class='square square2'>"._('Payables')."<p class='span1'>";
366 echo number_format2(-supplier_balance($today))."</p></div>";
368 echo "<div class='square square1'>"._('Todays Deposits')."<p class='span1'>";
369 echo number_format2(get_today_deposits($today))."</p></div>";
371 echo "<div class='square square2'>"._('Todays Payments')."<p class='span1'>";
372 echo number_format2(get_today_payments($today))."</p></div>";
374 $pg = new chart('halfDonut', 'g1');
375 if (isset($_POST['select_g1']))
376 $pg->type = $_POST['select_g1'];
377 table('100%', '50%');
378 $title = gl_top($today, 66, $pg);
379 source_graphic($today, $title, _("Class"), $pg);
380 gl_month_performance($today, 66, 5);
382 gl_week_performance($today, 66, 4);
384 bank_balance($today, 66);
388 function display_all()
390 $today = calc_today();
392 table('100%', '25%');
393 echo "<div class='square square1'>"._('Users')."<p class='span1'>";
394 echo get_num_users()."</p></div>";
396 echo "<div class='square square1'>"._('Extensions')."<p class='span1'>";
397 echo get_num_extensions()."</p></div>";
399 echo "<div class='square square1'>"._('Languages')."<p class='span1'>";
400 echo get_num_languages()."</p></div>";
402 echo "<div class='square square1'>"._('Database Size')."<p class='span1'>";
403 echo get_database_size()."</p></div>";
405 $pg = new chart('horizontalBar', 'c2');
406 if (isset($_POST['select_c2']))
407 $pg->type = $_POST['select_c2'];
408 table('100%', '50%');
409 $title = customer_top($today, 3, 66, $pg);
410 source_graphic($today, $title[0], _("Customer"), $pg);
411 $pg = new chart('horizontalBar', 's2');
412 if (isset($_POST['select_s2']))
413 $pg->type = $_POST['select_s2'];
414 $title = supplier_top($today, 3, 66, $pg);
415 source_graphic($today, $title[0], _("Supplier"), $pg);
416 $pg = new chart('bar', 'i2');
417 if (isset($_POST['select_i2']))
418 $pg->type = $_POST['select_i2'];
419 $title = stock_top($today, 3, 66, 0, $pg);
420 source_graphic($today, $title[0], _("Items"), $pg);
422 dimension_top($today, 3, 66);
423 $pg = new chart('pie', 'd2');
424 if (isset($_POST['select_d2']))
425 $pg->type = $_POST['select_d2'];
426 $title = gl_top($today, 66, $pg);
427 source_graphic($today, $title, _("Class"), $pg);
428 stock_top($today, 3, 66, 2);
429 stock_top($today, 3, 66, 1);
433 function customer_top($today, $limit=10, $width='33', &$pg=null)
435 $begin = date2sql(begin_fiscalyear());
436 $today = date2sql($today);
437 $sql = "SELECT SUM((ov_amount + ov_discount) * rate * IF(trans.type = ".ST_CUSTCREDIT.", -1, 1)) AS total,d.debtor_no, d.name FROM ".TB_PREF."debtor_trans AS trans, ".TB_PREF."debtors_master AS d WHERE trans.debtor_no=d.debtor_no
438 AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_CUSTCREDIT.")
439 AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by d.debtor_no ORDER BY total DESC, d.debtor_no
441 $result = db_query($sql);
442 $title = sprintf(_("Top %s customers in fiscal year"), $limit);
443 display_title($title);
444 $th = array(_("Customer"), _("Amount"));
445 start_table(TABLESTYLE, "width='$width%'");
447 check_page_security('SA_SALESTRANSVIEW');
448 $k = 0; //row colour counter
449 $names = $totals = array();
451 while ($myrow = db_fetch($result))
453 $sales += round($myrow['total']);
454 alt_table_row_color($k);
455 $name = $myrow["debtor_no"]." ".htmlspecialchars_decode($myrow["name"]);
457 amount_cell($myrow['total']);
461 $totals[] = round($myrow['total']);
467 $pg->setLabels($names);
468 $pg->addSerie(_('Sales'), $totals);
471 return array($title, $sales);
474 function supplier_top($today, $limit=10, $width='33', &$pg=null)
476 $begin = date2sql(begin_fiscalyear());
477 $today = date2sql($today);
478 $sql = "SELECT SUM((trans.ov_amount + trans.ov_discount) * rate) AS total, s.supplier_id, s.supp_name FROM
479 ".TB_PREF."supp_trans AS trans, ".TB_PREF."suppliers AS s WHERE trans.supplier_id=s.supplier_id
480 AND (trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.")
481 AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by s.supplier_id ORDER BY total DESC, s.supplier_id
483 $result = db_query($sql);
484 $title = sprintf(_("Top %s suppliers in fiscal year"), $limit);
485 display_title($title);
486 $th = array(_("Supplier"), _("Amount"));
487 start_table(TABLESTYLE, "width='$width%'");
489 check_page_security('SA_SUPPTRANSVIEW');
490 $k = 0; //row colour counter
491 $names = $totals = array();
493 while ($myrow = db_fetch($result))
495 $total += $myrow['total'];
496 alt_table_row_color($k);
497 $name = $myrow["supplier_id"]." ".htmlspecialchars_decode($myrow["supp_name"]);
499 amount_cell($myrow['total']);
503 $totals[] = round($myrow['total']);
509 $pg->setLabels($names);
510 $pg->addSerie(_('Purchases'), $totals);
513 return array($title, $total);
516 function stock_top($today, $limit=10, $width='33', $type=0, &$pg=null)
519 $sec = 'SA_ASSETSANALYTIC';
521 $sec = 'SA_WORKORDERANALYTIC';
523 $sec = 'SA_ITEMSTRANSVIEW';
524 $begin = date2sql(begin_fiscalyear());
525 $today = date2sql($today);
528 $sql = "SELECT SUM((trans.unit_price * trans.quantity) * d.rate) AS total, s.stock_id, s.description,
529 SUM(trans.quantity) AS qty, SUM((s.material_cost + s.overhead_cost + s.labour_cost) * trans.quantity) AS costs FROM
530 ".TB_PREF."debtor_trans_details AS trans, ".TB_PREF."stock_master AS s, ".TB_PREF."debtor_trans AS d
531 WHERE trans.stock_id=s.stock_id AND trans.debtor_trans_type=d.type AND trans.debtor_trans_no=d.trans_no
532 AND (d.type = ".ST_SALESINVOICE." OR d.type = ".ST_CUSTCREDIT.") ";
536 $sql = "SELECT SUM(m.qty * (s.material_cost + s.labour_cost + s.overhead_cost)) AS total, s.stock_id, s.description,
537 SUM(qty) AS qty FROM ".TB_PREF."stock_master AS s, ".TB_PREF."stock_moves AS m
538 WHERE s.stock_id=m.stock_id ";
540 $sql .= "AND s.mb_flag='M' AND m.type <> ".ST_CUSTDELIVERY." AND m.type <> ".ST_CUSTCREDIT." ";
542 $sql .= "AND s.mb_flag='F' ";
545 $sql .= "AND tran_date >= '$begin' ";
546 $sql .= "AND tran_date <= '$today' GROUP by s.stock_id ORDER BY total DESC, s.stock_id LIMIT $limit";
547 $result = db_query($sql);
549 $title = sprintf(_("Top %s Manufactured Items in fiscal year"), $limit);
551 $title = sprintf(_("Top %s Fixed Assets"), $limit);
553 $title = sprintf(_("Top %s Sold Items in fiscal year"), $limit);
554 display_title($title);
556 $th = array(_("Item"), _("Sales"), _("Costs"), _("Results"), _("Quantity"));
558 $th = array(_("Item"), _("Amount"), _("Quantity"));
559 start_table(TABLESTYLE, "width='$width%'");
561 check_page_security($sec);
562 $k = 0; //row colour counter
563 $names = $totals = $costs = $results = array();
564 $sales = $cost = $res = 0;
565 while ($myrow = db_fetch($result))
567 $sales += $myrow['total'];
570 $cost += $myrow['costs'];
571 $res += ($myrow['total'] - $myrow['costs']);
573 alt_table_row_color($k);
574 $name = $myrow["description"];
576 amount_cell($myrow['total']);
579 amount_cell($myrow['costs']);
580 amount_cell($myrow['total'] - $myrow['costs']);
582 qty_cell($myrow['qty']);
586 $totals[] = round($myrow['total']);
589 $costs[] = round($myrow['costs']);
590 $results[] = round($myrow['total'] - $myrow['costs']);
597 $pg->setLabels($names);
598 $pg->addSerie(_('Sales'), $totals);
601 $pg->addSerie(_('Costs'), $costs);
602 $pg->addSerie(_('Results'), $results, 8);
606 return array($title, $sales, $cost, $res);
609 function dimension_top($today, $limit=10, $width='33', &$pg=null)
611 $begin = date2sql(begin_fiscalyear());
612 $today = date2sql($today);
614 $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
615 LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code
616 LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type
617 LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id)
618 LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid
619 WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
620 AND tran_date <= '$today' AND d.reference IS NOT NULL GROUP BY d.reference ORDER BY result DESC LIMIT $limit";
621 $result = db_query($sql, "Transactions could not be calculated");
622 $title = sprintf(_("Top %s Dimensions in fiscal year"), $limit);
623 display_title($title);
624 $th = array(_("Dimension"), _("Balance"), _("Result"));
625 start_table(TABLESTYLE, "width='$width%'");
627 check_page_security('SA_DIMTRANSVIEW');
628 $k = 0; //row colour counter
629 $names = $balances = $results = array();
630 while ($myrow = db_fetch($result))
632 alt_table_row_color($k);
633 $name = $myrow['reference']." ".$myrow["name"];
635 amount_cell($myrow['balance']);
636 amount_cell($myrow['result']);
640 $balances[] = round(abs($myrow['balance']));
641 $results[] = round(abs($myrow['result']));
647 $pg->setLabels($names);
648 $pg->setValues(true);
649 $pg->addSerie(_('Balance'), $balances);
650 $pg->addSerie(_('Result'), $results);
656 function gl_top($today, $width='33', &$pg=null)
658 $begin = date2sql(begin_fiscalyear());
659 $today = date2sql($today);
660 $sql = "SELECT SUM(IF(c.ctype > 3, -amount, amount)) AS total, c.class_name, c.ctype FROM
661 ".TB_PREF."gl_trans,".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
662 ".TB_PREF."chart_class AS c WHERE
663 account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
664 AND IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
665 AND tran_date <= '$today' GROUP BY c.cid ORDER BY c.cid";
666 $result = db_query($sql, "Transactions could not be calculated");
667 $title = _("Class Balances");
668 display_title($title);
669 start_table(TABLESTYLE2, "width='$width%'");
670 check_page_security('SA_GLANALYTIC');
672 $names = $totals = array();
673 while ($myrow = db_fetch($result))
675 if ($myrow['ctype'] > 3)
677 $total += round($myrow['total']);
680 $names[] = $myrow['class_name'];
681 $totals[] = round(abs($myrow['total']));
684 label_row($myrow['class_name'], number_format2($myrow['total'], user_price_dec()),
685 "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
687 $calculated = _("Calculated Return");
688 label_row(" ", "");
689 label_row($calculated, number_format2($total, user_price_dec()),
690 "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
693 $names[] = $calculated;
695 $pg->setLabels($names);
696 $pg->addSerie(_('Totals'), $totals);
697 $pg->setValues(true);
698 $pg->setDTitle(number_format2($total));
704 function gl_week_performance($today, $width="33", $weeks=4)
708 $pg = new chart('bar', 'g3');
709 if (isset($_POST['select_g3']))
710 $pg->type = $_POST['select_g3'];
711 if (isset($_POST['per_g3']))
712 $weeks = $_POST['per_g3'];
713 $begin = date2sql(begin_fiscalyear());
714 $today1 = date2sql($today);
715 $sep = $SysPrefs->dateseps[user_date_sep()];
716 $sql = "SELECT week_name, sales, costs
717 FROM(SELECT DATE_FORMAT(tran_date, '%X{$sep}%V') AS week_name,
718 SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales,
719 SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM
720 ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
721 ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6)
722 AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
723 AND tran_date >= '$begin' AND tran_date <= '$today1'
724 GROUP BY week_name ORDER BY week_name DESC LIMIT 0, $weeks) b
725 GROUP BY week_name ORDER BY week_name ASC";
726 $result = db_query($sql, "Transactions could not be calculated");
727 $title = sprintf(_("Last %s weeks Performance"), $weeks);
728 check_page_security('SA_GLANALYTIC');
729 $week_names = $sales = $costs = $results = array();
730 while ($myrow = db_fetch($result))
732 $week_names[] = $myrow['week_name'];
733 $sales[] = round($myrow['sales']);
734 $costs[] = round($myrow['costs']);
735 $results[] = round($myrow['sales'] - $myrow['costs']);
737 $pg->setLabels($week_names);
738 $pg->addSerie(_('Sales'), $sales, 'bar');
739 $pg->addSerie(_('Costs'), $costs, 'bar');
740 $pg->addSerie(_('Results'), $results, 'spline');
741 source_graphic($today, $title, _("Week"), $pg, _("Weeks"), $weeks);
744 function gl_month_performance($today, $width="33", $months=5)
747 $pg = new chart('bar', 'g4');
748 if (isset($_POST['select_g4']))
749 $pg->type = $_POST['select_g4'];
750 if (isset($_POST['per_g4']))
751 $months = $_POST['per_g4'];
752 $begin = date2sql(begin_fiscalyear());
753 $today1 = date2sql($today);
754 $sep = $SysPrefs->dateseps[user_date_sep()];
755 $sql = "SELECT month_name, sales, costs
756 FROM(SELECT DATE_FORMAT(tran_date, '%Y{$sep}%m') AS month_name,
757 SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales,
758 SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM
759 ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t,
760 ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6)
761 AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid
762 AND tran_date >= '$begin' AND tran_date <= '$today1'
763 GROUP BY month_name ORDER BY month_name DESC LIMIT 0, $months) b
764 GROUP BY month_name ORDER BY month_name ASC";
765 $result = db_query($sql, "Transactions could not be calculated");
766 $title = sprintf(_("Last %s Months Performance"), $months);
767 check_page_security('SA_GLANALYTIC');
768 $month_names = $sales = $costs = $results = array();
769 while ($myrow = db_fetch($result))
771 $month_names[] = $myrow['month_name'];
772 $sales[] = round($myrow['sales']);
773 $costs[] = round($myrow['costs']);
774 $results[] = round($myrow['sales'] - $myrow['costs']);
776 $pg->setLabels($month_names);
777 $pg->addSerie(_('Sales'), $sales, 'bar');
778 $pg->addSerie(_('Costs'), $costs, 'bar');
779 $pg->addSerie(_('Results'), $results, 'spline');
780 source_graphic($today, $title, _("Month"), $pg, _("Months"), $months);
783 function customer_aging($today, $width="33")
785 $pg = new chart('bar', 'c3');
786 if (isset($_POST['select_c3']))
787 $pg->type = $_POST['select_c3'];
788 $today1 = date2sql($today);
789 $past1 = get_company_pref('past_due_days');
792 $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
794 $value = "$sign*(IF(trans.prep_amount, trans.prep_amount,
795 ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount)) - trans.alloc)*trans.rate";
797 $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)";
798 $sql = "SELECT Sum($value) AS Balance,
799 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due,
800 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
801 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
802 FROM ".TB_PREF."debtors_master debtor
803 LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today1' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY.","
804 .TB_PREF."payment_terms terms,"
805 .TB_PREF."credit_status credit_status
807 debtor.payment_terms = terms.terms_indicator
808 AND debtor.credit_status = credit_status.id";
809 $sql .= " AND ABS(IF(trans.prep_amount, trans.prep_amount, ABS(trans.ov_amount) + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA;
810 $result = db_query($sql,"The customer details could not be retrieved");
812 $row = db_fetch($result);
814 $title = _("Total Customers Aged Analysis");
815 check_page_security('SA_SALESTRANSVIEW');
817 $past1a = $past1 + 1;
818 $past2a = $past2 + 1;
819 $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+");
820 $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']),
821 round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2']));
822 $total = round($row['Balance']);
823 $pg->setLabels($names);
824 $pg->addSerie(_('Balances'), $balances);
825 source_graphic($today, $title, _('Days'), $pg);
829 function customer_balance($today)
831 $today = date2sql($today);
832 $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)";
833 $value = "IFNULL($sign*((trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount -
834 trans.alloc)*trans.rate),0)";
835 $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)";
836 $sql = "SELECT Sum($value) AS Balance
837 FROM ".TB_PREF."debtors_master debtor
838 LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY."
839 WHERE ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
840 $result = db_query($sql,"The customer details could not be retrieved");
841 $row = db_fetch($result);
845 function supplier_aging($today, $width="33")
847 $pg = new chart('bar', 's3');
848 if (isset($_POST['select_s3']))
849 $pg->type = $_POST['select_s3'];
850 $today1 = date2sql($today);
851 $past1 = get_company_pref('past_due_days');
853 // removed - debtor_trans.alloc from all summations
854 $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.",
855 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate,
856 (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)";
857 $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)";
858 $sql = "SELECT Sum($value) AS Balance,
859 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due,
860 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1,
861 Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2
862 FROM ".TB_PREF."suppliers supp
863 LEFT JOIN ".TB_PREF."supp_trans trans ON supp.supplier_id = trans.supplier_id AND trans.tran_date <= '$today1'
864 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA."
865 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
866 $result = db_query($sql,"The supplier details could not be retrieved");
868 $row = db_fetch($result);
870 $title = _("Total Suppliers Aged Analysis");
871 check_page_security('SA_SUPPLIERANALYTIC');
873 $past1a = $past1 + 1;
874 $past2a = $past2 + 1;
875 $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+");
876 $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']),
877 round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2']));
878 $total = round($row['Balance']);
879 $pg->setLabels($names);
880 $pg->addSerie(_('Balances'), $balances);
881 source_graphic($today, $title, _('Days'), $pg);
885 function supplier_balance($today)
887 $today = date2sql($today);
888 $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.",
889 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate,
890 (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)";
891 $sql = "SELECT Sum($value) AS Balance
892 FROM ".TB_PREF."suppliers supplier, ".TB_PREF."supp_trans trans
893 WHERE supplier.supplier_id = trans.supplier_id
894 AND trans.tran_date <= '$today'
895 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA."
896 AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA;
897 $result = db_query($sql,"The supplier details could not be retrieved");
898 $row = db_fetch($result);
902 function cash_flow($today)
904 global $date_system, $SysPrefs;
906 $pg = new chart('spline', 'g5');
907 if (isset($_POST['select_g5']))
908 $pg->type = $_POST['select_g5'];
909 if (isset($_POST['per_g5']))
910 $months = $_POST['per_g5'];
911 if (!is_date_in_fiscalyear($today))
912 $today = end_fiscalyear();
913 $today1 = begin_month($today);
914 $today1 = add_months($today, -$months+1);
915 list($da, $mo, $yr) = explode_date_to_dmy($today1);
916 if ($date_system == 1)
917 list($yr, $mo, $da) = jalali_to_gregorian($yr, $mo, $da);
918 elseif ($date_system == 2)
919 list($yr, $mo, $da) = islamic_to_gregorian($yr, $mo, $da);
923 for ($i = 0; $i < $months; $i++)
924 $date[$i] = date('Y-m-d',mktime(0,0,0,$mo + $i,1,$yr));
926 $result = get_bank_accounts();
927 $total = array_fill(0, $months, 0);
928 $balance = array_fill(0, $months, 0);
929 while ($account=db_fetch($result))
932 for ($i = 0; $i < $months; $i++)
933 $sql .= " SUM(CASE WHEN trans_date < '$date[$i]' THEN amount ELSE 0 END) AS per0".($i+1).",";
934 $sql = substr($sql, 0, -1);
935 $sql .= " FROM ".TB_PREF."bank_trans
936 WHERE bank_act=".$account['id'];
937 $res = db_query($sql, "Transactions for bank account could not be calculated");
938 $bal = db_fetch($res);
939 $is_home = is_company_currency($account['bank_curr_code']);
940 for ($i = 1; $i <= $months; $i++)
941 $balance[$i - 1] = $bal['per0'.$i];
942 for ($i = 0; $i < $months; $i++)
945 $balance[$i] = to_home_currency($balance[$i], $account['bank_curr_code'], sql2date($date[$i]));
946 $total[$i] += $balance[$i];
949 $sep = $SysPrefs->dateseps[user_date_sep()];
950 for ($i = 0; $i < $months; $i++)
952 $y = substr($date[$i], 0, 4);
953 $m = substr($date[$i], 5, 2);
954 $date[$i] = $y.$sep.$m;
955 $total[$i] = round($total[$i]);
957 $title = sprintf(_("Last %s Months Cash Flow"), $months);
958 check_page_security('SA_BANKREP');
959 $pg->setLabels($date);
960 $pg->addSerie(_('Balances'), $total, false, true);
961 source_graphic($today, $title, _("Month"), $pg, _("Months"), $months);
964 function source_graphic($today, $title, $x_axis, $pg, $per = '', $num = false)
968 //$today = sql2date($today);
969 display_title("$title ($today)", $pg->id, $pg->type, $per, $num);
972 display_note(_("No Data available yet!"), 1);
975 if ($pg->type == 'horizontalBar')
977 $pg->setYTitle($x_axis);
978 $pg->setXTitle(_("Amount"));
982 $pg->setXTitle($x_axis);
983 $pg->setYTitle(_("Amount"));
991 function customer_trans($today)
993 $today = date2sql($today);
995 $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, debtor.debtor_no,
996 debtor.name, branch.br_name, debtor.curr_code,
997 (trans.ov_amount + trans.ov_gst + trans.ov_freight
998 + trans.ov_freight_tax + trans.ov_discount) AS total,
999 (trans.ov_amount + trans.ov_gst + trans.ov_freight
1000 + trans.ov_freight_tax + trans.ov_discount - trans.alloc) AS remainder,
1001 DATEDIFF('$today', trans.due_date) AS days
1002 FROM ".TB_PREF."debtor_trans as trans, ".TB_PREF."debtors_master as debtor,
1003 ".TB_PREF."cust_branch as branch
1004 WHERE debtor.debtor_no = trans.debtor_no AND trans.branch_code = branch.branch_code
1005 AND trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
1006 + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA."
1007 AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC";
1008 $result = db_query($sql);
1009 $title = db_num_rows($result) . _(" overdue Sales Invoices");
1010 display_title($title);
1011 $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Customer"), _("Branch"), _("Currency"),
1012 _("Total"), _("Remainder"), _("Days"));
1013 start_table(TABLESTYLE, "width=90%");
1015 $k = 0; //row colour counter
1016 while ($myrow = db_fetch($result))
1018 alt_table_row_color($k);
1019 label_cell(get_trans_view_str(ST_SALESINVOICE, $myrow["trans_no"]));
1020 label_cell($myrow['reference']);
1021 label_cell(sql2date($myrow['tran_date']));
1022 label_cell(sql2date($myrow['due_date']));
1023 $name = $myrow["debtor_no"]." ".$myrow["name"];
1025 label_cell($myrow['br_name']);
1026 label_cell($myrow['curr_code']);
1027 amount_cell($myrow['total']);
1028 amount_cell($myrow['remainder']);
1029 label_cell($myrow['days'], "align='right'");
1035 function calculate_next_invoice($myrow)
1037 if ($myrow["last_sent"] == '0000-00-00')
1038 $next = sql2date($myrow["begin"]);
1040 $next = sql2date($myrow["last_sent"]);
1041 $next = add_months($next, $myrow['monthly']);
1042 $next = add_days($next, $myrow['days']);
1043 return add_days($next,-1);
1046 function customer_recurrent_invoices($today)
1048 $result = get_recurrent_invoices($today);
1049 $title = _("Overdue Recurrent Invoices");
1050 display_title($title);
1051 $th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Next invoice"));
1052 start_table(TABLESTYLE, "width=90%");
1055 while ($myrow = db_fetch($result))
1057 if (!$myrow['overdue'])
1059 alt_table_row_color($k);
1061 label_cell($myrow["description"]);
1062 label_cell(get_customer_trans_view_str(ST_SALESORDER, $myrow["order_no"]));
1063 if ($myrow["debtor_no"] == 0)
1067 label_cell(get_sales_group_name($myrow["group_no"]));
1071 label_cell(get_customer_name($myrow["debtor_no"]));
1072 label_cell(get_branch_name($myrow['group_no']));
1074 label_cell(calculate_next_invoice($myrow), "align='center'");
1080 function supplier_trans($today)
1082 $today = date2sql($today);
1083 $sql = "SELECT trans.trans_no, trans.reference, trans.tran_date, trans.due_date, s.supplier_id,
1084 s.supp_name, s.curr_code,
1085 (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS total,
1086 (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) AS remainder,
1087 DATEDIFF('$today', trans.due_date) AS days
1088 FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as s
1089 WHERE s.supplier_id = trans.supplier_id
1090 AND trans.type = ".ST_SUPPINVOICE." AND (ABS(trans.ov_amount + trans.ov_gst +
1091 trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA." ORDER BY days DESC";
1092 $result = db_query($sql);
1093 $title = db_num_rows($result) . _(" unpaid Purchase Invoices");
1094 display_title($title);
1095 $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Supplier"), _("Currency"), _("Total"),
1096 _("Remainder"), _("Days"));
1097 start_table(TABLESTYLE, "width=90%");
1099 $k = 0; //row colour counter
1101 while ($myrow = db_fetch($result))
1103 if ($myrow['days'] > 0)
1105 start_row("class='overduebg'");
1109 alt_table_row_color($k);
1110 label_cell(get_trans_view_str(ST_SUPPINVOICE, $myrow["trans_no"]));
1111 label_cell($myrow['reference']);
1112 label_cell(sql2date($myrow['tran_date']));
1113 label_cell(sql2date($myrow['due_date']));
1114 $name = $myrow["supplier_id"]." ".$myrow["supp_name"];
1116 label_cell($myrow['curr_code']);
1117 amount_cell($myrow['total']);
1118 amount_cell($myrow['remainder']);
1119 label_cell($myrow['days'], "align='right'");
1124 display_note(_("Marked items are overdue."), 1, 0, "class='overduefg'");
1128 function stock_below_reorder($today, $type)
1130 $MB = ($type == 0 ? 'B' : ($type == 1 ? 'M' : 'F'));
1131 $today = date2sql($today);
1133 $sql = "SELECT st.loc_code, l.location_name, st.stock_id, sm.description, reorders.reorder_level AS r, SUM(st.qty) AS q
1134 FROM ".TB_PREF."stock_moves st
1135 LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id
1136 LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id
1137 LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND
1138 reorders.stock_id = st.stock_id
1139 LEFT JOIN ".TB_PREF."locations l ON l.loc_code = st.loc_code
1140 WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B'
1141 GROUP BY sm.stock_id HAVING q <= r";
1142 $result = db_query($sql,"an item reorder could not be retreived");
1143 $title = db_num_rows($result) . _(" items are below Reorder Level");
1144 display_title($title);
1145 $th = array("#", _("Location"), _("Stock Id"), _('Description'), _("Reorder"), _("qty"));
1146 start_table(TABLESTYLE, "width=90%");
1148 $k = 0; //row colour counter
1149 while ($myrow = db_fetch($result))
1151 alt_table_row_color($k);
1152 label_cell($myrow['loc_code']);
1153 label_cell($myrow['location_name']);
1154 label_cell($myrow['stock_id']);
1155 label_cell($myrow['description']);
1156 $dec = get_qty_dec($myrow['stock_id']);
1157 qty_cell($myrow['r'], false, $dec);
1158 qty_cell($myrow['q'], false, $dec);
1164 function bank_balance($today, $width)
1166 $today = date2sql($today);
1167 $sql = "SELECT bank_act, bank_account_name, bank_curr_code, SUM(amount) balance FROM ".TB_PREF."bank_trans bt
1168 INNER JOIN ".TB_PREF."bank_accounts ba ON bt.bank_act = ba.id
1169 WHERE trans_date <= '$today'
1171 GROUP BY bank_act, bank_account_name
1172 ORDER BY bank_account_name";
1173 $result = db_query($sql);
1174 $title = _("Bank Account Balances");
1175 display_title($title);
1176 $th = array(_("Account"), _("Currency"), _("Balance"));
1177 start_table(TABLESTYLE, "width='$width%'");
1179 $k = 0; //row colour counter
1180 while ($myrow = db_fetch($result))
1182 alt_table_row_color($k);
1183 label_cell(viewer_link($myrow["bank_account_name"], 'gl/inquiry/bank_inquiry.php?bank_account='.$myrow["bank_act"]));
1184 label_cell($myrow["bank_curr_code"]);
1185 amount_cell($myrow['balance']);
1191 function get_num_customers()
1193 $sql = "SELECT count(*) FROM ".TB_PREF."debtors_master";
1194 $result = db_query($sql, "Fail in count customers");
1195 $row = db_fetch($result);
1199 function get_num_branches()
1201 $sql = "SELECT count(*) FROM ".TB_PREF."cust_branch";
1202 $result = db_query($sql, "Fail in count branches");
1203 $row = db_fetch($result);
1207 function get_num_salesmen()
1209 $sql = "SELECT count(*) FROM ".TB_PREF."salesman";
1210 $result = db_query($sql, "Fail in count salesmen");
1211 $row = db_fetch($result);
1215 function get_num_overdue_sales()
1217 $today = date2sql(calc_today());
1219 $sql = "SELECT COUNT(trans.trans_no)
1220 FROM ".TB_PREF."debtor_trans as trans
1221 WHERE trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
1222 + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA."
1223 AND DATEDIFF('$today', trans.due_date) > 0";
1224 $result = db_query($sql);
1225 $row = db_fetch($result);
1229 function get_num_suppliers()
1231 $sql = "SELECT count(*) FROM ".TB_PREF."suppliers";
1232 $result = db_query($sql, "Fail in count suppliers");
1233 $row = db_fetch($result);
1237 function get_new_purch_orders()
1239 $sql = "SELECT count(*) FROM ".TB_PREF."purch_orders po, ".TB_PREF."purch_order_details pd WHERE po.order_no = pd.order_no AND
1240 quantity_received < quantity_ordered";
1241 $result = db_query($sql, "Fail in count purch orders");
1242 $row = db_fetch($result);
1246 function get_num_supp_invoices()
1248 $sql = "SELECT count(*) FROM ".TB_PREF."supp_trans WHERE (ov_amount+ov_discount+ov_gst - alloc) <> 0";
1249 $result = db_query($sql, "Fail in count purch invoices");
1250 $row = db_fetch($result);
1254 function get_num_overdue_purch()
1256 $today = date2sql(calc_today());
1258 $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";
1259 $result = db_query($sql, "Fail in count purch invoices");
1260 $row = db_fetch($result);
1264 function get_num_items()
1266 $sql = "SELECT count(*) FROM ".TB_PREF."stock_master";
1267 $result = db_query($sql, "Fail in count stock master");
1268 $row = db_fetch($result);
1272 function get_num_locations()
1274 $sql = "SELECT count(*) FROM ".TB_PREF."locations";
1275 $result = db_query($sql, "Fail in count locations");
1276 $row = db_fetch($result);
1280 function get_num_kits()
1282 $sql="SELECT count(*) FROM ".TB_PREF."item_codes";
1284 $result = db_query($sql, "Fail in count kits");
1285 $row = db_fetch($result);
1289 function get_num_reorder()
1292 $today = date2sql(calc_today());
1294 $sql = "SELECT st.stock_id, reorders.reorder_level AS r, SUM(st.qty) AS q
1295 FROM ".TB_PREF."stock_moves st
1296 LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id
1297 LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id
1298 LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND
1299 reorders.stock_id = st.stock_id
1300 WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B'
1301 GROUP BY st.stock_id HAVING q <= r";
1302 $result = db_query($sql,"an item reorder could not be retreived");
1303 return db_num_rows($result);
1306 function get_num_assembled()
1308 $begin = date2sql(begin_fiscalyear());
1309 $today = date2sql(calc_today());
1310 $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ASSEMBLY." AND released_date >= '$begin'
1311 AND released_date <= '$today'";
1312 $result = db_query($sql, "Fail in count workorders");
1313 $row = db_fetch($result);
1317 function get_num_manufactured()
1319 $begin = date2sql(begin_fiscalyear());
1320 $today = date2sql(calc_today());
1321 $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ADVANCED." AND released_date >= '$begin'
1322 AND released_date <= '$today'";
1323 $result = db_query($sql, "Fail in count workorders");
1324 $row = db_fetch($result);
1328 function get_num_workcentres()
1330 $sql = "SELECT count(*) FROM ".TB_PREF."workcentres";
1331 $result = db_query($sql, "Fail in count work centres");
1332 $row = db_fetch($result);
1336 function get_open_workorders()
1338 $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 0";
1339 $result = db_query($sql, "Fail in count workorders");
1340 $row = db_fetch($result);
1344 function get_num_fixed_assets()
1346 $sql = "SELECT count(*) FROM ".TB_PREF."stock_master WHERE mb_flag = 'F'";
1347 $result = db_query($sql, "Fail in count fixed assets");
1348 $row = db_fetch($result);
1352 function get_num_fixed_locations()
1354 $sql = "SELECT count(*) FROM ".TB_PREF."stock_master s LEFT JOIN ".TB_PREF."loc_stock l ON s.stock_id = l.stock_id
1355 WHERE mb_flag = 'F'";
1356 $result = db_query($sql, "Fail in count locations");
1357 $row = db_fetch($result);
1361 function get_num_fixed_categories()
1363 $sql = "SELECT count(*) FROM ".TB_PREF."stock_category c LEFT JOIN ".TB_PREF."stock_master s ON c.category_id = s.category_id
1364 WHERE mb_flag = 'F'";
1365 $result = db_query($sql, "Fail in count categories");
1366 $row = db_fetch($result);
1370 function get_num_fixed_classes()
1372 $sql = "SELECT count(*) FROM ".TB_PREF."stock_fa_class";
1373 $result = db_query($sql, "Fail in count fa classes");
1374 $row = db_fetch($result);
1378 function get_num_dimensions()
1380 $sql = "SELECT count(*) FROM ".TB_PREF."dimensions";
1381 $result = db_query($sql, "Fail in count dimensions");
1382 $row = db_fetch($result);
1386 function get_dim_type2()
1388 $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE type_ = 2";
1389 $result = db_query($sql, "Fail in count simensions 2");
1390 $row = db_fetch($result);
1394 function get_dim_closed()
1396 $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE closed = 1";
1397 $result = db_query($sql, "Fail in count closed dimensions");
1398 $row = db_fetch($result);
1402 function get_dim_total()
1404 $begin = date2sql(begin_fiscalyear());
1405 $today = date2sql(calc_today());
1407 $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
1408 LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code
1409 LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type
1410 LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id)
1411 LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid
1412 WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00')
1413 AND tran_date <= '$today' AND d.reference IS NOT NULL GROUP BY d.reference";
1415 $result = db_query($sql, "Transactions could not be calculated");
1416 while ($row = db_fetch($result))
1418 $bal += $row['balance'];
1419 $res += $row['result'];
1421 return array(round($bal), round($res));
1424 function get_today_deposits($today)
1426 $today= date2sql($today);
1427 $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba
1428 WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount > 0.0";
1429 $result = db_query($sql, "could not retrieve today deposits");
1430 if ($result == false)
1434 $row = db_fetch($result);
1439 function get_today_payments($today)
1441 $today= date2sql($today);
1442 $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba
1443 WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount < 0.0";
1444 $result = db_query($sql, "could not retrieve today deposits");
1445 if ($result == false)
1449 $row = db_fetch($result);
1454 function get_num_users()
1456 $sql = "SELECT count(*) FROM ".TB_PREF."users";
1457 $result = db_query($sql, "could not get num users");
1458 $row = db_fetch($result);
1462 function get_num_extensions()
1464 global $installed_extensions;
1465 return count($installed_extensions);
1468 function get_num_languages()
1470 global $installed_languages;
1471 return count($installed_languages);
1474 function get_database_size()
1476 global $db_connections;
1477 $c = user_company();
1478 $dbase = $db_connections[$c]['dbname'];
1479 $tb_pref = $db_connections[$c]['tbpref'];
1480 $sql = "SHOW TABLE STATUS FROM $dbase";
1481 if (!empty($tb_pref))
1482 $sql .= " LIKE '{$tb_pref}%'";
1483 $result = db_query($sql, "could not get database size");
1485 while ($row = db_fetch($result)) {
1486 $size += $row['Data_length'];
1487 $size += $row['Index_length'];
1489 return number_format2(($size / 1024 / 1024), 2).' MB';
1492 function calc_today()
1495 if (!is_date_in_fiscalyear($today))
1496 $today = end_fiscalyear();