X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdashboard.inc;h=57db155207a056a12c96ca085ac297548192441a;hb=9044444ee1933dc684c6aab26ac718e65ce8c370;hp=3beaf98eafc2662642024f51ca486581e561fe39;hpb=bed24f8171bceca69abe9b31ffb98d9ddae6943f;p=fa-stable.git diff --git a/includes/dashboard.inc b/includes/dashboard.inc index 3beaf98e..57db1552 100644 --- a/includes/dashboard.inc +++ b/includes/dashboard.inc @@ -9,242 +9,511 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ +//$colors = array('#008cc9','#4db625','#ef5500','#eef100','#05c6e6', '#5ee66a'); // Current in Use +$colors = Chart::$palette; +$align = $_SESSION['language']->dir == 'rtl' ? 'right' : 'left'; +$style = ""; - if (!$_SESSION["wa_current_user"]->check_application_access($selected_app)) +function dashboard($sel_app) +{ + global $style; + if (is_object($sel_app) && !$_SESSION["wa_current_user"]->check_application_access($selected_app)) return; - // first have a look through the directory, - // and remove old temporary pdfs and pngs - $dir = company_path(). '/pdf_files'; - - if ($d = @opendir($dir)) { - while (($file = readdir($d)) !== false) { - if (!is_file($dir.'/'.$file) || $file == 'index.php') continue; - // then check to see if this one is too old - $ftime = filemtime($dir.'/'.$file); - // seems 3 min is enough for any report download, isn't it? - if (time()-$ftime > 180){ - unlink($dir.'/'.$file); + echo $style; + echo ""; //check_for_overdue_recurrent_invoices(); - if ($selected_app == "orders") + if ($sel_app == "orders") display_customer_topten(); - elseif ($selected_app == "AP") + elseif ($sel_app == "AP") display_supplier_topten(); - elseif ($selected_app == "stock") + elseif ($sel_app == "stock") display_stock_topten(); - elseif ($selected_app == "manuf") + elseif ($sel_app == "manuf") display_stock_topten(1); - elseif ($selected_app == "assets") + elseif ($sel_app == "assets") display_stock_topten(2); - elseif ($selected_app == "proj") + elseif ($sel_app == "proj") display_dimension_topten(); - elseif ($selected_app == "GL") + elseif ($sel_app == "GL") display_gl_info(); else display_all(); } -function display_title($title) +function display_title($title, $id=false, $type = false, $per = '', $num = false) { br(); - display_heading($title); + start_form(); + echo "
"; + echo "$title"; + if ($id) + { + /* + global $Ajax; + echo ""; + $options = array('select_submit' => true ); + if (!empty($per)) + { + $name = "per_$id"; + $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8"); + echo array_selector($name, $per, $sel1, $options); + echo " $per\n"; + } + else + { + $name = "select_$id"; + $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables")); + echo array_selector($name, $type, $sel, $options); + } + if (list_updated($name)) + $Ajax->activate($id); + echo ""; + */ + echo ""; + if (!empty($per)) + { + $sel1 = array(3 => "3", 4 => "4", 5 => "5", 6 => "6", 7 => "7", 8=> "8"); + echo " $per\n"; + } + else + { + $sel = array(1 => _("Vertical bars"), 2 => _("Horizontal bars"), 3 => _("Dots"), 4 => _("Lines"), 5 => _("Pie"), 6 => _("Donut"), 7 => _("Half Donut"), 8 => _("Splines"), 9 => _("Tables")); + echo "\n"; + } + echo ""; + } + echo "
\n"; + end_form(); br(); - //echo "$title\n"; } -function table_one() +function table($width='100%', $twidth=false) { - echo ""; - echo "\n"; + foreach ($labels as $label) + echo "\n"; + echo "\n"; +} - table_one(); - $today = Today(); +function display_customer_topten() +{ + table('100%', '25%'); + echo "
"._('Customers')."

"; + echo get_num_customers()."

"; + td('25%'); + echo "
"._('Branches')."

"; + echo get_num_branches()."

"; + td('25%'); + echo "
"._('Salesmen')."

"; + echo get_num_salesmen()."

"; + td('25%'); + echo "
"._('Overdue Invoices')."

"; + echo get_num_overdue_sales()."

"; + table_end(); + $pg = new chart('horizontalBar', 'c1'); + if (isset($_POST['select_c1'])) + $pg->type = $_POST['select_c1']; + $today = calc_today(); + table('100%', '50%'); $title = customer_top($today, 10, 66, $pg); - customer_trans($today); - table_two(); - source_graphic($today, $title, _("Customer"), $pg, _("Sales")); + $total = customer_aging($today, 66); + td('50%'); + source_graphic($today, $title[0], _("Customer"), $pg); + br(3); + echo "
"._('Sales Total')."

"; + echo number_format2($title[1])."

"; + br(3); + echo "
"._('Aging Total')."

"; + echo number_format2($total)."

"; + table_end(); + table('100%', '100%'); customer_recurrent_invoices($today); + customer_trans($today); table_end(); } function display_supplier_topten() { - $pg = new graph(); - - $today = Today(); - table_one(); + table('100%', '25%'); + echo "
"._('Suppliers')."

"; + echo get_num_suppliers()."

"; + td('25%'); + echo "
"._('New Orders')."

"; + echo get_new_purch_orders()."

"; + td('25%'); + echo "
"._('Invoices')."

"; + echo get_num_supp_invoices()."

"; + td('25%'); + echo "
"._('Overdue Invoices')."

"; + echo get_num_overdue_purch()."

"; + table_end(); + $pg = new chart('horizontalBar', 's1'); + if (isset($_POST['select_s1'])) + $pg->type = $_POST['select_s1']; + $today = calc_today(); + table('100%', '50%'); $title = supplier_top($today, 10, 66, $pg); + $total = supplier_aging($today, 66); + td('50%'); + source_graphic($today, $title[0], _("Supplier"), $pg); + br(3); + echo "
"._('Purchasable Total')."

"; + echo number_format2($title[1])."

"; + br(3); + echo "
"._('Aging Total')."

"; + echo number_format2($total)."

"; + table_end(); + table('100%', '100%'); supplier_trans($today); - table_two(); - source_graphic($today, $title, _("Supplier"), $pg, _("Purchase")); table_end(); } function display_stock_topten($type=0) { - $pg = new graph(); - - table_one(); - $today = Today(); - $title = stock_top($today, 10, 66, $type, $pg); - table_two(); + if ($type == 0) + { + table('100%', '25%'); + echo "
"._('Items')."

"; + echo get_num_items()."

"; + td('25%'); + echo "
"._('Locations')."

"; + echo get_num_locations()."

"; + td('25%'); + echo "
"._('Kits')."

"; + echo get_num_kits()."

"; + td('25%'); + echo "
"._('Below Reorder Level')."

"; + echo get_num_reorder()."

"; + table_end(); + } + elseif ($type == 1) + { + table('100%', '25%'); + echo "
"._('Assembled Items')."

"; + echo get_num_assembled()."

"; + td('25%'); + echo "
"._('Manufactured Items')."

"; + echo get_num_manufactured()."

"; + td('25%'); + echo "
"._('Work Centres')."

"; + echo get_num_workcentres()."

"; + td('25%'); + echo "
"._('Open Workorders')."

"; + echo get_open_workorders()."

"; + table_end(); + } + elseif ($type == 2) + { + table('100%', '25%'); + echo "
"._('Fixed Assets')."

"; + echo get_num_fixed_assets()."

"; + td('25%'); + echo "
"._('Locations')."

"; + echo get_num_fixed_locations()."

"; + td('25%'); + echo "
"._('Categories')."

"; + echo get_num_fixed_categories()."

"; + td('25%'); + echo "
"._('Fixed Asset Classes')."

"; + echo get_num_fixed_classes()."

"; + table_end(); + } + $pg = new chart('bar', 'i1'); + if (isset($_POST['select_i1'])) + $pg->type = $_POST['select_i1']; + table('100%', '50%'); + $today = calc_today(); + $title = stock_top($today, 5, 66, $type, $pg); + table('50%', '25%'); + echo "
"._('Total Sales')."

"; + echo number_format2($title[1])."

"; + td('25%'); + echo "
"._('Total Costs')."

"; + echo number_format2($title[2])."

"; + table_end(); + td('50%'); if ($type == 2) $source = _("Fixed Assets"); elseif ($type == 1) $source = _("Manufacturing"); else $source = _("Items"); - source_graphic($today, $title, $source, $pg, ($type == 0 ? _("Sales") : _("Amount")), - ($type == 0 ? _("Costs") : null)); + source_graphic($today, $title[0], $source, $pg); + echo "
"._('Total Results')."
"; + echo number_format2($title[3])."
"; + table_end(); + table('100%', '100%'); + stock_below_reorder($today, $type); table_end(); } function display_dimension_topten() { - $pg = new graph(); - - table_one(); - $today = Today(); - $title = dimension_top($today, 10, 66, $pg); - table_two(); - source_graphic($today, $title, _("Dimension"), $pg, _("Performance"), null, 5); + table('100%', '25%'); + echo "
"._('Dimensions')."

"; + echo get_num_dimensions()."

"; + td('25%'); + echo "
"._('Type 2 Dimensions')."

"; + echo get_dim_type2()."

"; + td('25%'); + $res = get_dim_total(); + $s = $res[0] < 0.0 ? 2 : 1; + echo "
"._('Dimensions Total Balance')."

"; + echo number_format2($res[0])."

"; + td('25%'); + $s = $res[1] < 0.0 ? 2 : 1; + echo "
"._('Dimensions Total Result')."

"; + echo number_format2($res[1])."

"; + table_end(); + $pg = new chart('bar', 'd1'); + if (isset($_POST['select_d1'])) + $pg->type = $_POST['select_d1']; + table('100%', '50%'); + $today = calc_today(); + $title = dimension_top($today, 5, 66, $pg); + td('50%'); + source_graphic($today, $title, _("Dimension"), $pg); table_end(); } function display_gl_info() { - $pg = new graph(); - - table_one(); - $today = Today(); + $today = calc_today(); + table('100%', '25%'); + echo "
"._('Receivables')."

"; + echo number_format2(customer_balance($today))."

"; + td('25%'); + echo "
"._('Payables')."

"; + echo number_format2(-supplier_balance($today))."

"; + td('25%'); + echo "
"._('Todays Deposits')."

"; + echo number_format2(get_today_deposits($today))."

"; + td('25%'); + echo "
"._('Todays Payments')."

"; + echo number_format2(get_today_payments($today))."

"; + table_end(); + $pg = new chart('halfDonut', 'g1'); + if (isset($_POST['select_g1'])) + $pg->type = $_POST['select_g1']; + table('100%', '50%'); $title = gl_top($today, 66, $pg); + source_graphic($today, $title, _("Class"), $pg); + gl_month_performance($today, 66, 5); + td('50%'); + gl_week_performance($today, 66, 4); + cash_flow($today); bank_balance($today, 66); - table_two(); - source_graphic($today, $title, _("Class"), $pg, _("Amount"), null, 5); - gl_performance($today, 66, 5); table_end(); } function display_all() { - $today = Today(); - $pg = new graph(); + $today = calc_today(); - table_one(); + table('100%', '25%'); + echo "
"._('Users')."

"; + echo get_num_users()."

"; + td('25%'); + echo "
"._('Extensions')."

"; + echo get_num_extensions()."

"; + td('25%'); + echo "
"._('Languages')."

"; + echo get_num_languages()."

"; + td('25%'); + echo "
"._('Database Size')."

"; + echo get_database_size()."

"; + table_end(); + $pg = new chart('horizontalBar', 'c2'); + if (isset($_POST['select_c2'])) + $pg->type = $_POST['select_c2']; + table('100%', '50%'); $title = customer_top($today, 3, 66, $pg); - source_graphic($today, $title, _("Customer"), $pg, _("Sales")); + source_graphic($today, $title[0], _("Customer"), $pg); + $pg = new chart('horizontalBar', 's2'); + if (isset($_POST['select_s2'])) + $pg->type = $_POST['select_s2']; $title = supplier_top($today, 3, 66, $pg); - source_graphic($today, $title, _("Supplier"), $pg, _("Purchases")); + source_graphic($today, $title[0], _("Supplier"), $pg); + $pg = new chart('bar', 'i2'); + if (isset($_POST['select_i2'])) + $pg->type = $_POST['select_i2']; $title = stock_top($today, 3, 66, 0, $pg); - source_graphic($today, $title, _("Items"), $pg, _("Sales"), _("Costs")); - table_two(); + source_graphic($today, $title[0], _("Items"), $pg); + td('50%'); dimension_top($today, 3, 66); + $pg = new chart('pie', 'd2'); + if (isset($_POST['select_d2'])) + $pg->type = $_POST['select_d2']; $title = gl_top($today, 66, $pg); - source_graphic($today, $title, _("Class"), $pg, _("Amount"), null, 5); + source_graphic($today, $title, _("Class"), $pg); stock_top($today, 3, 66, 2); stock_top($today, 3, 66, 1); table_end(); } -function customer_top($today, $limit=10, $width="33", &$pg=null) +function customer_top($today, $limit=10, $width='33', &$pg=null) { - $begin = begin_fiscalyear(); - $begin1 = date2sql($begin); - $today1 = date2sql($today); - $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 + $begin = date2sql(begin_fiscalyear()); + $today = date2sql($today); + $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 AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_CUSTCREDIT.") - AND tran_date >= '$begin1' AND tran_date <= '$today1' GROUP by d.debtor_no ORDER BY total DESC, d.debtor_no + AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by d.debtor_no ORDER BY total DESC, d.debtor_no LIMIT $limit"; $result = db_query($sql); - $title = _("Top $limit customers in fiscal year"); + $title = sprintf(_("Top %s customers in fiscal year"), $limit); display_title($title); $th = array(_("Customer"), _("Amount")); start_table(TABLESTYLE, "width='$width%'"); - table_header($th); + headers($th); check_page_security('SA_SALESTRANSVIEW'); $k = 0; //row colour counter - $i = 0; + $names = $totals = array(); + $sales = 0; while ($myrow = db_fetch($result)) { + $sales += round($myrow['total']); alt_table_row_color($k); - $name = $myrow["debtor_no"]." ".$myrow["name"]; + $name = $myrow["debtor_no"]." ".htmlspecialchars_decode($myrow["name"]); label_cell($name); amount_cell($myrow['total']); if ($pg != null) { - $pg->x[$i] = $name; - $pg->y[$i] = $myrow['total']; + $names[] = $name; + $totals[] = round($myrow['total']); } - $i++; end_row(); } + if ($pg != null) + { + $pg->setLabels($names); + $pg->addSerie(_('Sales'), $totals); + } end_table(2); - return $title; + return array($title, $sales); } -function supplier_top($today, $limit=10, $width="33", &$pg=null) +function supplier_top($today, $limit=10, $width='33', &$pg=null) { - $begin = begin_fiscalyear(); - $begin1 = date2sql($begin); - $today1 = date2sql($today); + $begin = date2sql(begin_fiscalyear()); + $today = date2sql($today); $sql = "SELECT SUM((trans.ov_amount + trans.ov_discount) * rate) AS total, s.supplier_id, s.supp_name FROM ".TB_PREF."supp_trans AS trans, ".TB_PREF."suppliers AS s WHERE trans.supplier_id=s.supplier_id AND (trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.") - AND tran_date >= '$begin1' AND tran_date <= '$today1' GROUP by s.supplier_id ORDER BY total DESC, s.supplier_id + AND tran_date >= '$begin' AND tran_date <= '$today' GROUP by s.supplier_id ORDER BY total DESC, s.supplier_id LIMIT $limit"; $result = db_query($sql); - $title = _("Top $limit suppliers in fiscal year"); + $title = sprintf(_("Top %s suppliers in fiscal year"), $limit); display_title($title); $th = array(_("Supplier"), _("Amount")); start_table(TABLESTYLE, "width='$width%'"); - table_header($th); + headers($th); check_page_security('SA_SUPPTRANSVIEW'); $k = 0; //row colour counter - $i = 0; + $names = $totals = array(); + $total = 0; while ($myrow = db_fetch($result)) { + $total += $myrow['total']; alt_table_row_color($k); - $name = $myrow["supplier_id"]." ".$myrow["supp_name"]; + $name = $myrow["supplier_id"]." ".htmlspecialchars_decode($myrow["supp_name"]); label_cell($name); amount_cell($myrow['total']); if ($pg != null) { - $pg->x[$i] = $name; - $pg->y[$i] = $myrow['total']; + $names[] = $name; + $totals[] = round($myrow['total']); } - $i++; end_row(); } + if ($pg != null) + { + $pg->setLabels($names); + $pg->addSerie(_('Purchases'), $totals); + } end_table(2); - return $title; + return array($title, $total); } -function stock_top($today, $limit=10, $width="33", $type=0, &$pg=null) +function stock_top($today, $limit=10, $width='33', $type=0, &$pg=null) { if ($type == 2) $sec = 'SA_ASSETSANALYTIC'; @@ -252,9 +521,8 @@ function stock_top($today, $limit=10, $width="33", $type=0, &$pg=null) $sec = 'SA_WORKORDERANALYTIC'; else $sec = 'SA_ITEMSTRANSVIEW'; - $begin = begin_fiscalyear(); - $begin1 = date2sql($begin); - $today1 = date2sql($today); + $begin = date2sql(begin_fiscalyear()); + $today = date2sql($today); if ($type == 0) { $sql = "SELECT SUM((trans.unit_price * trans.quantity) * d.rate) AS total, s.stock_id, s.description, @@ -269,195 +537,455 @@ function stock_top($today, $limit=10, $width="33", $type=0, &$pg=null) SUM(qty) AS qty FROM ".TB_PREF."stock_master AS s, ".TB_PREF."stock_moves AS m WHERE s.stock_id=m.stock_id "; if ($type == 1) - $sql .= "AND s.mb_flag='M' "; + $sql .= "AND s.mb_flag='M' AND m.type <> ".ST_CUSTDELIVERY." AND m.type <> ".ST_CUSTCREDIT." "; elseif ($type == 2) $sql .= "AND s.mb_flag='F' "; } - $sql .= "AND tran_date >= '$begin1' AND tran_date <= '$today1' GROUP by s.stock_id ORDER BY total DESC, s.stock_id - LIMIT $limit"; + if ($type != 2) + $sql .= "AND tran_date >= '$begin' "; + $sql .= "AND tran_date <= '$today' GROUP by s.stock_id ORDER BY total DESC, s.stock_id LIMIT $limit"; $result = db_query($sql); if ($type == 1) - $title = _("Top $limit Manufactured Items in fiscal year"); + $title = sprintf(_("Top %s Manufactured Items in fiscal year"), $limit); elseif ($type == 2) - $title = _("Top $limit Fixed Assets in fiscal year"); + $title = sprintf(_("Top %s Fixed Assets"), $limit); else - $title = _("Top $limit Sold Items in fiscal year"); + $title = sprintf(_("Top %s Sold Items in fiscal year"), $limit); display_title($title); if ($type == 0) - $th = array(_("Item"), _("Sales"), _("Costs"), _("Quantity")); + $th = array(_("Item"), _("Sales"), _("Costs"), _("Results"), _("Quantity")); else $th = array(_("Item"), _("Amount"), _("Quantity")); start_table(TABLESTYLE, "width='$width%'"); - table_header($th); + headers($th); check_page_security($sec); $k = 0; //row colour counter - $i = 0; + $names = $totals = $costs = $results = array(); + $sales = $cost = $res = 0; while ($myrow = db_fetch($result)) { + $sales += $myrow['total']; + if ($type == 0) + { + $cost += $myrow['costs']; + $res += ($myrow['total'] - $myrow['costs']); + } alt_table_row_color($k); $name = $myrow["description"]; label_cell($name); amount_cell($myrow['total']); if ($type == 0) + { amount_cell($myrow['costs']); + amount_cell($myrow['total'] - $myrow['costs']); + } qty_cell($myrow['qty']); if ($pg != NULL) { - $pg->x[$i] = $name; - $pg->y[$i] = $myrow['total']; + $names[] = $name; + $totals[] = round($myrow['total']); if ($type == 0) - $pg->z[$i] = $myrow['costs']; + { + $costs[] = round($myrow['costs']); + $results[] = round($myrow['total'] - $myrow['costs']); + } } - $i++; end_row(); } + if ($pg != null) + { + $pg->setLabels($names); + $pg->addSerie(_('Sales'), $totals); + if ($type == 0) + { + $pg->addSerie(_('Costs'), $costs); + $pg->addSerie(_('Results'), $results, 8); + } + } end_table(2); - return $title; + return array($title, $sales, $cost, $res); } -function dimension_top($today, $limit=10, $width="33", &$pg=null) +function dimension_top($today, $limit=10, $width='33', &$pg=null) { + $begin = date2sql(begin_fiscalyear()); + $today = date2sql($today); - $begin = begin_fiscalyear(); - $begin1 = date2sql($begin); - $today1 = date2sql($today); - $sql = "SELECT SUM(-t.amount) AS total, d.reference, d.name FROM - ".TB_PREF."gl_trans AS t,".TB_PREF."dimensions AS d WHERE - (t.dimension_id = d.id OR t.dimension2_id = d.id) AND - t.tran_date >= '$begin1' AND t.tran_date <= '$today1' GROUP BY d.id ORDER BY total DESC LIMIT $limit"; + $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 + LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code + LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type + LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id) + LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid + WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00') + AND tran_date <= '$today' AND d.reference IS NOT NULL GROUP BY d.reference ORDER BY result DESC LIMIT $limit"; $result = db_query($sql, "Transactions could not be calculated"); - $title = _("Top $limit Dimensions in fiscal year"); + $title = sprintf(_("Top %s Dimensions in fiscal year"), $limit); display_title($title); - $th = array(_("Dimension"), _("Amount")); + $th = array(_("Dimension"), _("Balance"), _("Result")); start_table(TABLESTYLE, "width='$width%'"); - table_header($th); + headers($th); check_page_security('SA_DIMTRANSVIEW'); $k = 0; //row colour counter - $i = 0; + $names = $balances = $results = array(); while ($myrow = db_fetch($result)) { alt_table_row_color($k); $name = $myrow['reference']." ".$myrow["name"]; label_cell($name); - amount_cell($myrow['total']); + amount_cell($myrow['balance']); + amount_cell($myrow['result']); if ($pg != null) { - $pg->x[$i] = $name; - $pg->y[$i] = abs($myrow['total']); + $names[] = $name; + $balances[] = round(abs($myrow['balance'])); + $results[] = round(abs($myrow['result'])); } - $i++; end_row(); } + if ($pg != null) + { + $pg->setLabels($names); + $pg->setValues(true); + $pg->addSerie(_('Balance'), $balances); + $pg->addSerie(_('Result'), $results); + } end_table(2); return $title; } -function gl_top($today, $width="33", &$pg=null) +function gl_top($today, $width='33', &$pg=null) { - $begin = begin_fiscalyear(); - $begin1 = date2sql($begin); - $today1 = date2sql($today); - $sql = "SELECT SUM(amount) AS total, c.class_name, c.ctype FROM + $begin = date2sql(begin_fiscalyear()); + $today = date2sql($today); + $sql = "SELECT SUM(IF(c.ctype > 3, -amount, amount)) AS total, c.class_name, c.ctype FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t, ".TB_PREF."chart_class AS c WHERE account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid - AND IF(c.ctype > 3, tran_date >= '$begin1', tran_date >= '0000-00-00') - AND tran_date <= '$today1' GROUP BY c.cid ORDER BY c.cid"; + AND IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00') + AND tran_date <= '$today' GROUP BY c.cid ORDER BY c.cid"; $result = db_query($sql, "Transactions could not be calculated"); $title = _("Class Balances"); display_title($title); start_table(TABLESTYLE2, "width='$width%'"); check_page_security('SA_GLANALYTIC'); - $i = 0; $total = 0; + $names = $totals = array(); while ($myrow = db_fetch($result)) { if ($myrow['ctype'] > 3) { - $total += $myrow['total']; - $myrow['total'] = -$myrow['total']; + $total += round($myrow['total']); if ($pg != null) { - $pg->x[$i] = $myrow['class_name']; - $pg->y[$i] = abs($myrow['total']); + $names[] = $myrow['class_name']; + $totals[] = round(abs($myrow['total'])); } - $i++; } label_row($myrow['class_name'], number_format2($myrow['total'], user_price_dec()), "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right"); } $calculated = _("Calculated Return"); label_row(" ", ""); - label_row($calculated, number_format2(-$total, user_price_dec()), + label_row($calculated, number_format2($total, user_price_dec()), "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right"); if ($pg != null) { - $pg->x[$i] = $calculated; - $pg->y[$i] = -$total; + $names[] = $calculated; + $totals[] = $total; + $pg->setLabels($names); + $pg->addSerie(_('Totals'), $totals); + $pg->setValues(true); + $pg->setDTitle(number_format2($total)); } end_table(2); return $title; } -function gl_performance($today, $width="33", $weeks=5) +function gl_week_performance($today, $width="33", $weeks=4) { - $pg = new graph(); + global $SysPrefs; + + $pg = new chart('bar', 'g3'); + if (isset($_POST['select_g3'])) + $pg->type = $_POST['select_g3']; + if (isset($_POST['per_g3'])) + $weeks = $_POST['per_g3']; + $begin = date2sql(begin_fiscalyear()); + $today1 = date2sql($today); + $sep = $SysPrefs->dateseps[user_date_sep()]; + $sql = "SELECT week_name, sales, costs + FROM(SELECT DATE_FORMAT(tran_date, '%X{$sep}%V') AS week_name, + SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales, + SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM + ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t, + ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6) + AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid + AND tran_date >= '$begin' AND tran_date <= '$today1' + GROUP BY week_name ORDER BY week_name DESC LIMIT 0, $weeks) b + GROUP BY week_name ORDER BY week_name ASC"; + $result = db_query($sql, "Transactions could not be calculated"); + $title = sprintf(_("Last %s weeks Performance"), $weeks); + check_page_security('SA_GLANALYTIC'); + $week_names = $sales = $costs = $results = array(); + while ($myrow = db_fetch($result)) + { + $week_names[] = $myrow['week_name']; + $sales[] = round($myrow['sales']); + $costs[] = round($myrow['costs']); + $results[] = round($myrow['sales'] - $myrow['costs']); + } + $pg->setLabels($week_names); + $pg->addSerie(_('Sales'), $sales, 'bar'); + $pg->addSerie(_('Costs'), $costs, 'bar'); + $pg->addSerie(_('Results'), $results, 'spline'); + source_graphic($today, $title, _("Week"), $pg, _("Weeks"), $weeks); +} - $begin = begin_fiscalyear(); - $begin1 = date2sql($begin); +function gl_month_performance($today, $width="33", $months=5) +{ + global $SysPrefs; + $pg = new chart('bar', 'g4'); + if (isset($_POST['select_g4'])) + $pg->type = $_POST['select_g4']; + if (isset($_POST['per_g4'])) + $months = $_POST['per_g4']; + $begin = date2sql(begin_fiscalyear()); $today1 = date2sql($today); - $sql = "SELECT CONCAT(YEAR(tran_date), '/', WEEK(tran_date)) AS week_name, - SUM(IF (c.ctype = 4, amount * -1, 0)) AS sales, - SUM(IF (c.ctype = 6, amount, 0)) AS costs - FROM + $sep = $SysPrefs->dateseps[user_date_sep()]; + $sql = "SELECT month_name, sales, costs + FROM(SELECT DATE_FORMAT(tran_date, '%Y{$sep}%m') AS month_name, + SUM(IF(c.ctype = 4, amount * -1, 0)) AS sales, + SUM(IF(c.ctype = 6, amount, 0)) AS costs FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master AS a, ".TB_PREF."chart_types AS t, - ".TB_PREF."chart_class AS c WHERE (c.ctype = 4 OR c.ctype = 6) - AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid - AND IF(c.ctype > 3, tran_date >= '$begin1', tran_date >= '0000-00-00') - AND tran_date <= '$today1' - GROUP BY week_name - ORDER BY YEAR(tran_date) DESC, WEEK(tran_date) DESC limit 0, $weeks"; + ".TB_PREF."chart_class AS c WHERE(c.ctype = 4 OR c.ctype = 6) + AND account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid + AND tran_date >= '$begin' AND tran_date <= '$today1' + GROUP BY month_name ORDER BY month_name DESC LIMIT 0, $months) b + GROUP BY month_name ORDER BY month_name ASC"; $result = db_query($sql, "Transactions could not be calculated"); - $title = _("Last $weeks weeks Performance"); + $title = sprintf(_("Last %s Months Performance"), $months); check_page_security('SA_GLANALYTIC'); - $i = 0; + $month_names = $sales = $costs = $results = array(); while ($myrow = db_fetch($result)) { - $pg->x[$i] = $myrow['week_name']; - $pg->y[$i] = $myrow['sales']; - $pg->z[$i] = $myrow['costs']; - $i++; + $month_names[] = $myrow['month_name']; + $sales[] = round($myrow['sales']); + $costs[] = round($myrow['costs']); + $results[] = round($myrow['sales'] - $myrow['costs']); } - $pg->x = array_reverse($pg->x); - $pg->y = array_reverse($pg->y); - $pg->z = array_reverse($pg->z); - - source_graphic($today, $title, _("Week"), $pg, _("Sales"), _("Costs"), 1); + $pg->setLabels($month_names); + $pg->addSerie(_('Sales'), $sales, 'bar'); + $pg->addSerie(_('Costs'), $costs, 'bar'); + $pg->addSerie(_('Results'), $results, 'spline'); + source_graphic($today, $title, _("Month"), $pg, _("Months"), $months); +} + +function customer_aging($today, $width="33") +{ + $pg = new chart('bar', 'c3'); + if (isset($_POST['select_c3'])) + $pg->type = $_POST['select_c3']; + $today1 = date2sql($today); + $past1 = get_company_pref('past_due_days'); + $past2 = 2 * $past1; + + $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)"; + + $value = "$sign*(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)*trans.rate"; + + $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)"; + $sql = "SELECT Sum($value) AS Balance, + Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due, + Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1, + Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2 + FROM ".TB_PREF."debtors_master debtor + LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today1' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY."," + .TB_PREF."payment_terms terms," + .TB_PREF."credit_status credit_status + WHERE + debtor.payment_terms = terms.terms_indicator + AND debtor.credit_status = credit_status.id"; + $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; + $result = db_query($sql,"The customer details could not be retrieved"); + + $row = db_fetch($result); + + $title = _("Total Customers Aged Analysis"); + check_page_security('SA_SALESTRANSVIEW'); + $names = array(); + $past1a = $past1 + 1; + $past2a = $past2 + 1; + $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+"); + $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']), + round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2'])); + $total = round($row['Balance']); + $pg->setLabels($names); + $pg->addSerie(_('Balances'), $balances); + source_graphic($today, $title, _('Days'), $pg); + return $total; +} + +function customer_balance($today) +{ + $today = date2sql($today); + $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT))."), -1, 1)"; + $value = "IFNULL($sign*((trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount - + trans.alloc)*trans.rate),0)"; + $due = "IF (trans.type=".ST_SALESINVOICE.", trans.due_date, trans.tran_date)"; + $sql = "SELECT Sum($value) AS Balance + FROM ".TB_PREF."debtors_master debtor + LEFT JOIN ".TB_PREF."debtor_trans trans ON trans.tran_date <= '$today' AND debtor.debtor_no = trans.debtor_no AND trans.type <> ".ST_CUSTDELIVERY." + WHERE ABS(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA; + $result = db_query($sql,"The customer details could not be retrieved"); + $row = db_fetch($result); + return $row[0]; +} + +function supplier_aging($today, $width="33") +{ + $pg = new chart('bar', 's3'); + if (isset($_POST['select_s3'])) + $pg->type = $_POST['select_s3']; + $today1 = date2sql($today); + $past1 = get_company_pref('past_due_days'); + $past2 = 2 * $past1; + // removed - debtor_trans.alloc from all summations + $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.", + (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate, + (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)"; + $due = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_SUPPCREDIT.",trans.due_date,trans.tran_date)"; + $sql = "SELECT Sum($value) AS Balance, + Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > 0,$value,0)) AS Due, + Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past1,$value,0)) AS Overdue1, + Sum(IF ((TO_DAYS('$today1') - TO_DAYS($due)) > $past2,$value,0)) AS Overdue2 + FROM ".TB_PREF."suppliers supp + LEFT JOIN ".TB_PREF."supp_trans trans ON supp.supplier_id = trans.supplier_id AND trans.tran_date <= '$today1' + AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA." + AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA; + $result = db_query($sql,"The supplier details could not be retrieved"); + + $row = db_fetch($result); + + $title = _("Total Suppliers Aged Analysis"); + check_page_security('SA_SUPPLIERANALYTIC'); + $names = array(); + $past1a = $past1 + 1; + $past2a = $past2 + 1; + $names = array(_('Current'),"1-$past1", "$past1a-$past2", "$past2+"); + $balances = array(round($row['Balance']-$row['Due']), round($row['Due'] - $row['Overdue1']), + round($row['Overdue1'] - $row['Overdue2']), round($row['Overdue2'])); + $total = round($row['Balance']); + $pg->setLabels($names); + $pg->addSerie(_('Balances'), $balances); + source_graphic($today, $title, _('Days'), $pg); + return $total; +} + +function supplier_balance($today) +{ + $today = date2sql($today); + $value = "IF (trans.type=".ST_SUPPINVOICE." OR trans.type=".ST_BANKDEPOSIT.", + (trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) * trans.rate, + (trans.ov_amount + trans.ov_gst + trans.ov_discount + trans.alloc) * trans.rate)"; + $sql = "SELECT Sum($value) AS Balance + FROM ".TB_PREF."suppliers supplier, ".TB_PREF."supp_trans trans + WHERE supplier.supplier_id = trans.supplier_id + AND trans.tran_date <= '$today' + AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) > ".FLOAT_COMP_DELTA." + AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA; + $result = db_query($sql,"The supplier details could not be retrieved"); + $row = db_fetch($result); + return $row[0]; +} + +function cash_flow($today) +{ + global $date_system, $SysPrefs; + $months = 6; + $pg = new chart('spline', 'g5'); + if (isset($_POST['select_g5'])) + $pg->type = $_POST['select_g5']; + if (isset($_POST['per_g5'])) + $months = $_POST['per_g5']; + if (!is_date_in_fiscalyear($today)) + $today = end_fiscalyear(); + $today1 = begin_month($today); + $today1 = add_months($today, -$months+1); + list($da, $mo, $yr) = explode_date_to_dmy($today1); + if ($date_system == 1) + list($yr, $mo, $da) = jalali_to_gregorian($yr, $mo, $da); + elseif ($date_system == 2) + list($yr, $mo, $da) = islamic_to_gregorian($yr, $mo, $da); + + $date = array(); + + for ($i = 0; $i < $months; $i++) + $date[$i] = date('Y-m-d',mktime(0,0,0,$mo + $i,1,$yr)); + + $result = get_bank_accounts(); + $total = array_fill(0, $months, 0); + $balance = array_fill(0, $months, 0); + while ($account=db_fetch($result)) + { + $sql = "SELECT"; + for ($i = 0; $i < $months; $i++) + $sql .= " SUM(CASE WHEN trans_date < '$date[$i]' THEN amount ELSE 0 END) AS per0".($i+1).","; + $sql = substr($sql, 0, -1); + $sql .= " FROM ".TB_PREF."bank_trans + WHERE bank_act=".$account['id']; + $res = db_query($sql, "Transactions for bank account could not be calculated"); + $bal = db_fetch($res); + $is_home = is_company_currency($account['bank_curr_code']); + for ($i = 1; $i <= $months; $i++) + $balance[$i - 1] = $bal['per0'.$i]; + for ($i = 0; $i < $months; $i++) + { + if (!$is_home) + $balance[$i] = to_home_currency($balance[$i], $account['bank_curr_code'], sql2date($date[$i])); + $total[$i] += $balance[$i]; + } + } + $sep = $SysPrefs->dateseps[user_date_sep()]; + for ($i = 0; $i < $months; $i++) + { + $y = substr($date[$i], 0, 4); + $m = substr($date[$i], 5, 2); + $date[$i] = $y.$sep.$m; + $total[$i] = round($total[$i]); + } + $title = sprintf(_("Last %s Months Cash Flow"), $months); + check_page_security('SA_BANKREP'); + $pg->setLabels($date); + $pg->addSerie(_('Balances'), $total, false, true); + source_graphic($today, $title, _("Month"), $pg, _("Months"), $months); } -function source_graphic($today, $title, $x_axis, $pg, $graphic1, $graphic2=null, $type=2) +function source_graphic($today, $title, $x_axis, $pg, $per = '', $num = false) { - if (count($pg->y) ==0 || (count($pg->y) == 1 && $pg->y[0] == 0)) + if (!empty($per)) + div_start($pg->id); + //$today = sql2date($today); + display_title("$title ($today)", $pg->id, $pg->type, $per, $num); + if ($pg->isEmpty()) + { + display_note(_("No Data available yet!"), 1); return; - display_title("$title ($today)"); - //$pg->title = $title . " - " . $today; - $pg->axis_x = $x_axis; - $pg->axis_y = _("Amount"); - $pg->graphic_1 = $graphic1; - if ($graphic2 != null) - $pg->graphic_2 = $graphic2; - $pg->type = $type; - $pg->skin = 1; - $pg->built_in = false; - $filename = company_path(). "/pdf_files/". uniqid("").".png"; - $pg->display($filename, true); - start_table(TABLESTYLE); - start_row(); - echo ""; - end_row(); - end_table(1); + } + if ($pg->type == 'horizontalBar') + { + $pg->setYTitle($x_axis); + $pg->setXTitle(_("Amount")); + } + else + { + $pg->setXTitle($x_axis); + $pg->setYTitle(_("Amount")); + } + if (empty($per)) + div_start($pg->id); + $pg->display(); + div_end(); } function customer_trans($today) @@ -482,8 +1010,8 @@ function customer_trans($today) display_title($title); $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Customer"), _("Branch"), _("Currency"), _("Total"), _("Remainder"), _("Days")); - start_table(TABLESTYLE); - table_header($th); + start_table(TABLESTYLE, "width=90%"); + headers($th); $k = 0; //row colour counter while ($myrow = db_fetch($result)) { @@ -521,8 +1049,8 @@ function customer_recurrent_invoices($today) $title = _("Overdue Recurrent Invoices"); display_title($title); $th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Next invoice")); - start_table(TABLESTYLE, "width=70%"); - table_header($th); + start_table(TABLESTYLE, "width=90%"); + headers($th); $k = 0; while ($myrow = db_fetch($result)) { @@ -560,19 +1088,25 @@ function supplier_trans($today) FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as s WHERE s.supplier_id = trans.supplier_id AND trans.type = ".ST_SUPPINVOICE." AND (ABS(trans.ov_amount + trans.ov_gst + - trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA." - AND DATEDIFF('$today', trans.due_date) > 0 ORDER BY days DESC"; + trans.ov_discount) - trans.alloc) > ".FLOAT_COMP_DELTA." ORDER BY days DESC"; $result = db_query($sql); - $title = db_num_rows($result) . _(" overdue Purchase Invoices"); + $title = db_num_rows($result) . _(" unpaid Purchase Invoices"); display_title($title); $th = array("#", _("Ref."), _("Date"), _("Due Date"), _("Supplier"), _("Currency"), _("Total"), _("Remainder"), _("Days")); - start_table(TABLESTYLE); - table_header($th); + start_table(TABLESTYLE, "width=90%"); + headers($th); $k = 0; //row colour counter + $due = false; while ($myrow = db_fetch($result)) { - alt_table_row_color($k); + if ($myrow['days'] > 0) + { + start_row("class='overduebg'"); + $due = true; + } + else + alt_table_row_color($k); label_cell(get_trans_view_str(ST_SUPPINVOICE, $myrow["trans_no"])); label_cell($myrow['reference']); label_cell(sql2date($myrow['tran_date'])); @@ -585,13 +1119,52 @@ function supplier_trans($today) label_cell($myrow['days'], "align='right'"); end_row(); } + end_table(); + if ($due) + display_note(_("Marked items are overdue."), 1, 0, "class='overduefg'"); + br(); +} + +function stock_below_reorder($today, $type) +{ + $MB = ($type == 0 ? 'B' : ($type == 1 ? 'M' : 'F')); + $today = date2sql($today); + + $sql = "SELECT st.loc_code, l.location_name, st.stock_id, sm.description, reorders.reorder_level AS r, SUM(st.qty) AS q + FROM ".TB_PREF."stock_moves st + LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id + LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id + LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND + reorders.stock_id = st.stock_id + LEFT JOIN ".TB_PREF."locations l ON l.loc_code = st.loc_code + WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B' + GROUP BY sm.stock_id HAVING q <= r"; + $result = db_query($sql,"an item reorder could not be retreived"); + $title = db_num_rows($result) . _(" items are below Reorder Level"); + display_title($title); + $th = array("#", _("Location"), _("Stock Id"), _('Description'), _("Reorder"), _("qty")); + start_table(TABLESTYLE, "width=90%"); + headers($th); + $k = 0; //row colour counter + while ($myrow = db_fetch($result)) + { + alt_table_row_color($k); + label_cell($myrow['loc_code']); + label_cell($myrow['location_name']); + label_cell($myrow['stock_id']); + label_cell($myrow['description']); + $dec = get_qty_dec($myrow['stock_id']); + qty_cell($myrow['r'], false, $dec); + qty_cell($myrow['q'], false, $dec); + end_row(); + } end_table(2); } function bank_balance($today, $width) { $today = date2sql($today); - $sql = "SELECT bank_act, bank_account_name, SUM(amount) balance FROM ".TB_PREF."bank_trans bt + $sql = "SELECT bank_act, bank_account_name, bank_curr_code, SUM(amount) balance FROM ".TB_PREF."bank_trans bt INNER JOIN ".TB_PREF."bank_accounts ba ON bt.bank_act = ba.id WHERE trans_date <= '$today' AND inactive <> 1 @@ -600,16 +1173,327 @@ function bank_balance($today, $width) $result = db_query($sql); $title = _("Bank Account Balances"); display_title($title); - $th = array(_("Account"), _("Balance")); + $th = array(_("Account"), _("Currency"), _("Balance")); start_table(TABLESTYLE, "width='$width%'"); - table_header($th); + headers($th); $k = 0; //row colour counter while ($myrow = db_fetch($result)) { alt_table_row_color($k); label_cell(viewer_link($myrow["bank_account_name"], 'gl/inquiry/bank_inquiry.php?bank_account='.$myrow["bank_act"])); + label_cell($myrow["bank_curr_code"]); amount_cell($myrow['balance']); end_row(); } end_table(1); -} \ No newline at end of file +} + +function get_num_customers() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."debtors_master"; + $result = db_query($sql, "Fail in count customers"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_branches() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."cust_branch"; + $result = db_query($sql, "Fail in count branches"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_salesmen() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."salesman"; + $result = db_query($sql, "Fail in count salesmen"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_overdue_sales() +{ + $today = date2sql(calc_today()); + + $sql = "SELECT COUNT(trans.trans_no) + FROM ".TB_PREF."debtor_trans as trans + WHERE trans.type = ".ST_SALESINVOICE." AND (trans.ov_amount + trans.ov_gst + trans.ov_freight + + trans.ov_freight_tax + trans.ov_discount - trans.alloc) > ".FLOAT_COMP_DELTA." + AND DATEDIFF('$today', trans.due_date) > 0"; + $result = db_query($sql); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_suppliers() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."suppliers"; + $result = db_query($sql, "Fail in count suppliers"); + $row = db_fetch($result); + return $row[0]; +} + +function get_new_purch_orders() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."purch_orders po, ".TB_PREF."purch_order_details pd WHERE po.order_no = pd.order_no AND + quantity_received < quantity_ordered"; + $result = db_query($sql, "Fail in count purch orders"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_supp_invoices() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."supp_trans WHERE (ov_amount+ov_discount+ov_gst - alloc) <> 0"; + $result = db_query($sql, "Fail in count purch invoices"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_overdue_purch() +{ + $today = date2sql(calc_today()); + + $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"; + $result = db_query($sql, "Fail in count purch invoices"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_items() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."stock_master"; + $result = db_query($sql, "Fail in count stock master"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_locations() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."locations"; + $result = db_query($sql, "Fail in count locations"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_kits() +{ + $sql="SELECT count(*) FROM ".TB_PREF."item_codes"; + + $result = db_query($sql, "Fail in count kits"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_reorder() +{ + $MB = 'B'; + $today = date2sql(calc_today()); + + $sql = "SELECT st.stock_id, reorders.reorder_level AS r, SUM(st.qty) AS q + FROM ".TB_PREF."stock_moves st + LEFT JOIN ".TB_PREF."voided v ON st.type=v.type AND st.trans_no=v.id + LEFT JOIN ".TB_PREF."stock_master sm ON sm.stock_id = st.stock_id + LEFT JOIN ".TB_PREF."loc_stock reorders ON reorders.loc_code=st.loc_code AND + reorders.stock_id = st.stock_id + WHERE ISNULL(v.id) AND tran_date <= '$today' AND sm.mb_flag = 'B' + GROUP BY st.stock_id HAVING q <= r"; + $result = db_query($sql,"an item reorder could not be retreived"); + return db_num_rows($result); +} + +function get_num_assembled() +{ + $begin = date2sql(begin_fiscalyear()); + $today = date2sql(calc_today()); + $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ASSEMBLY." AND released_date >= '$begin' + AND released_date <= '$today'"; + $result = db_query($sql, "Fail in count workorders"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_manufactured() +{ + $begin = date2sql(begin_fiscalyear()); + $today = date2sql(calc_today()); + $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 1 AND type = ".WO_ADVANCED." AND released_date >= '$begin' + AND released_date <= '$today'"; + $result = db_query($sql, "Fail in count workorders"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_workcentres() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."workcentres"; + $result = db_query($sql, "Fail in count work centres"); + $row = db_fetch($result); + return $row[0]; +} + +function get_open_workorders() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."workorders WHERE closed = 0"; + $result = db_query($sql, "Fail in count workorders"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_fixed_assets() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."stock_master WHERE mb_flag = 'F'"; + $result = db_query($sql, "Fail in count fixed assets"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_fixed_locations() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."stock_master s LEFT JOIN ".TB_PREF."loc_stock l ON s.stock_id = l.stock_id + WHERE mb_flag = 'F'"; + $result = db_query($sql, "Fail in count locations"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_fixed_categories() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."stock_category c LEFT JOIN ".TB_PREF."stock_master s ON c.category_id = s.category_id + WHERE mb_flag = 'F'"; + $result = db_query($sql, "Fail in count categories"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_fixed_classes() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."stock_fa_class"; + $result = db_query($sql, "Fail in count fa classes"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_dimensions() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."dimensions"; + $result = db_query($sql, "Fail in count dimensions"); + $row = db_fetch($result); + return $row[0]; +} + +function get_dim_type2() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE type_ = 2"; + $result = db_query($sql, "Fail in count simensions 2"); + $row = db_fetch($result); + return $row[0]; +} + +function get_dim_closed() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."dimensions WHERE closed = 1"; + $result = db_query($sql, "Fail in count closed dimensions"); + $row = db_fetch($result); + return $row[0]; +} + +function get_dim_total() +{ + $begin = date2sql(begin_fiscalyear()); + $today = date2sql(calc_today()); + + $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 + LEFT JOIN ".TB_PREF."chart_master AS a ON g.account = a.account_code + LEFT JOIN ".TB_PREF."chart_types AS t ON t.id = a.account_type + LEFT JOIN ".TB_PREF."dimensions AS d ON (g.dimension_id = d.id OR g.dimension2_id = d.id) + LEFT JOIN ".TB_PREF."chart_class AS c ON t.class_id = c.cid + WHERE IF(c.ctype > 3, tran_date >= '$begin', tran_date >= '0000-00-00') + AND tran_date <= '$today' AND d.reference IS NOT NULL GROUP BY d.reference"; + $bal = $res = 0.0; + $result = db_query($sql, "Transactions could not be calculated"); + while ($row = db_fetch($result)) + { + $bal += $row['balance']; + $res += $row['result']; + } + return array(round($bal), round($res)); +} + +function get_today_deposits($today) +{ + $today= date2sql($today); + $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba + WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount > 0.0"; + $result = db_query($sql, "could not retrieve today deposits"); + if ($result == false) + return 0; + else + { + $row = db_fetch($result); + return $row[0]; + } +} + +function get_today_payments($today) +{ + $today= date2sql($today); + $sql="SELECT SUM(gl.amount) FROM ".TB_PREF."gl_trans gl, ".TB_PREF."bank_accounts ba + WHERE gl.account = ba.account_code AND gl.tran_date = '$today' AND gl.amount < 0.0"; + $result = db_query($sql, "could not retrieve today deposits"); + if ($result == false) + return 0; + else + { + $row = db_fetch($result); + return $row[0]; + } +} + +function get_num_users() +{ + $sql = "SELECT count(*) FROM ".TB_PREF."users"; + $result = db_query($sql, "could not get num users"); + $row = db_fetch($result); + return $row[0]; +} + +function get_num_extensions() +{ + global $installed_extensions; + return count($installed_extensions); +} + +function get_num_languages() +{ + global $installed_languages; + return count($installed_languages); +} + +function get_database_size() +{ + global $db_connections; + $c = user_company(); + $dbase = $db_connections[$c]['dbname']; + $tb_pref = $db_connections[$c]['tbpref']; + $sql = "SHOW TABLE STATUS FROM $dbase"; + if (!empty($tb_pref)) + $sql .= " LIKE '{$tb_pref}%'"; + $result = db_query($sql, "could not get database size"); + $size = 0; + while ($row = db_fetch($result)) { + $size += $row['Data_length']; + $size += $row['Index_length']; + } + return number_format2(($size / 1024 / 1024), 2).' MB'; +} + +function calc_today() +{ + $today = Today(); + if (!is_date_in_fiscalyear($today)) + $today = end_fiscalyear(); + return $today; +} +
\n"; // outer table + $tstyle = ($twidth != false) ? "style='width:$twidth;'" : ""; + echo "
"; } -function table_two() +function td($width=false) { - echo "\n"; -} + $style = ($width != false) ? "style='width:$width;'" : ""; + echo "\n"; +} function table_end() { echo "
\n"; } -function display_customer_topten() +function headers($labels) { - $pg = new graph(); + echo "
$label
"; - echo "$title"; - echo "