function render($id, $title)
{
$sql = 'SELECT * FROM (SELECT max(trans_date) `Week End`, ' . 'concat(cast(case when weekofyear(trans_date) = 1 and month(trans_date)=12 then year(trans_date) + 1 else year(trans_date) end as char),cast(weekofyear(trans_date) as char)) `Week No`, ' . 'sum(case when weekday(trans_date)=0 then gross_output else 0 end) Monday, ' . 'sum(case when weekday(trans_date)=1 then gross_output else 0 end) Tuesday, ' . 'sum(case when weekday(trans_date)=2 then gross_output else 0 end) Wednesday, ' . 'sum(case when weekday(trans_date)=3 then gross_output else 0 end) Thursday, ' . 'sum(case when weekday(trans_date)=4 then gross_output else 0 end) Friday, ' . 'sum(case when weekday(trans_date)=5 then gross_output else 0 end) Saturday, ' . 'sum(case when weekday(trans_date)=6 then gross_output else 0 end) Sunday ' . 'FROM (SELECT bt.trans_date trans_date, ' . 'sum((ttd.net_amount+ttd.amount)*ex_rate) gross_output, ' . 'sum(ttd.net_amount*ex_rate) net_output, ' . 'sum(ttd.amount*ex_rate) payable ' . 'FROM ' . TB_PREF . 'bank_trans bt ' . 'INNER JOIN ' . TB_PREF . 'cust_allocations ca ' . 'ON bt.type = ca.trans_type_from ' . 'AND bt.trans_no = ca.trans_no_from ' . 'INNER JOIN ' . TB_PREF . 'debtor_trans dt ' . 'ON dt.type = ca.trans_type_from ' . 'AND dt.trans_no = ca.trans_no_from ' . 'INNER JOIN ' . TB_PREF . 'debtors_master dm ' . 'ON dt.debtor_no = dm.debtor_no ' . 'INNER JOIN ' . TB_PREF . 'trans_tax_details ttd ' . 'ON ttd.trans_type = ca.trans_type_to ' . 'AND ttd.trans_no = ca.trans_no_to ' . 'INNER JOIN ' . TB_PREF . 'tax_types tt ' . 'ON tt.id = ttd.tax_type_id ';
if ($this->data_filter != '') {
$sql .= ' WHERE ' . $this->data_filter;
}
$sql .= ' GROUP BY bt.trans_date ' . ') days ' . 'GROUP BY concat(cast(case when weekofyear(trans_date) = 1 and month(trans_date)=12 then year(trans_date) + 1 else year(trans_date) end as char),cast(weekofyear(trans_date) as char)) ' . 'ORDER BY max(trans_date) desc ';
if (isset($this->top)) {
$sql .= ' limit ' . $this->top;
}
$sql .= ") weeks ORDER BY `Week End`";
$result = db_query($sql) or die(_('Error getting daily sales data'));
$rows = array();
//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(array('label' => 'Week End', 'type' => 'string'), array('label' => 'Monday', 'type' => 'number'), array('label' => 'Tuesday', 'type' => 'number'), array('label' => 'Wednesday', 'type' => 'number'), array('label' => 'Thursday', 'type' => 'number'), array('label' => 'Friday', 'type' => 'number'), array('label' => 'Saturday', 'type' => 'number'), array('label' => 'Sunday', 'type' => 'number'));
$rows = array();
while ($r = db_fetch_assoc($result)) {
$temp = array();
// the following line will used to slice the Pie chart
$temp[] = array('v' => (string) $r['Week End'], 'f' => sql2date($r['Week End']));
$temp[] = array('v' => (double) $r['Monday'], 'f' => number_format2($r['Monday'], user_price_dec()));
$temp[] = array('v' => (double) $r['Tuesday'], 'f' => number_format2($r['Tuesday'], user_price_dec()));
$temp[] = array('v' => (double) $r['Wednesday'], 'f' => number_format2($r['Wednesday'], user_price_dec()));
$temp[] = array('v' => (double) $r['Thursday'], 'f' => number_format2($r['Thursday'], user_price_dec()));
$temp[] = array('v' => (double) $r['Friday'], 'f' => number_format2($r['Friday'], user_price_dec()));
$temp[] = array('v' => (double) $r['Saturday'], 'f' => number_format2($r['Saturday'], user_price_dec()));
$temp[] = array('v' => (double) $r['Sunday'], 'f' => number_format2($r['Sunday'], user_price_dec()));
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
$js = "google.load('visualization', '1', {'packages':['corechart','table']});\ngoogle.setOnLoadCallback(drawChart" . $id . ");\nfunction drawChart" . $id . "() {\n var data = new google.visualization.DataTable(" . $jsonTable . ");\n var options = {";
if ($this->graph_type != 'Table') {
$js .= "height: 300, ";
}
$js .= "title: '" . $title . "'\n };\n var chart" . $id . " = new google.visualization." . $this->graph_type . "(document.getElementById('widget_div_" . $id . "'));\n chart" . $id . ".draw(data, options);\n}";
add_js_source($js);
}
function render($id, $title)
{
$sql = '';
if (isset($this->orderby)) {
$sql .= 'SELECT `Week End`, `Week no`, `Gross Sales`, `Net Sales`, `Tax` ' . 'FROM (';
}
$sql .= 'SELECT max(bt.trans_date) `Week End`, ' . 'weekofyear(bt.trans_date) `Week no`, ' . 'sum((ttd.net_amount+ttd.amount)*ex_rate) `Gross Sales`, ' . 'sum(ttd.net_amount*ex_rate) `Net Sales`, ' . 'sum(ttd.amount*ex_rate) `Tax` ' . 'FROM ' . TB_PREF . 'bank_trans bt ' . 'INNER JOIN ' . TB_PREF . 'cust_allocations ca ' . 'ON bt.type = ca.trans_type_from ' . 'AND bt.trans_no = ca.trans_no_from ' . 'INNER JOIN ' . TB_PREF . 'debtor_trans dt ' . 'ON dt.type = ca.trans_type_from ' . 'AND dt.trans_no = ca.trans_no_from ' . 'INNER JOIN ' . TB_PREF . 'debtors_master dm ' . 'ON dt.debtor_no = dm.debtor_no ' . 'INNER JOIN ' . TB_PREF . 'trans_tax_details ttd ' . 'ON ttd.trans_type = ca.trans_type_to ' . 'AND ttd.trans_no = ca.trans_no_to ' . 'INNER JOIN ' . TB_PREF . 'tax_types tt ' . 'ON tt.id = ttd.tax_type_id ';
if ($this->data_filter != '') {
$sql .= ' WHERE ' . $this->data_filter;
}
$sql .= ' GROUP BY weekofyear(bt.trans_date) ';
if (isset($this->orderby)) {
$sql .= ') items order by `' . $this->orderby . '` ' . $this->orderby_seq;
}
if (isset($this->top)) {
$sql .= ' limit ' . $this->top;
}
$result = db_query($sql) or die(_('Error getting weekly sales data'));
$rows = array();
//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(array('label' => 'Week End', 'type' => 'string'), array('label' => 'Gross Sales', 'type' => 'number'));
$rows = array();
while ($r = db_fetch_assoc($result)) {
$temp = array();
// the following line will used to slice the Pie chart
$temp[] = array('v' => (string) $r['Week End'], 'f' => sql2date($r['Week End']));
$temp[] = array('v' => (double) $r['Gross Sales'], 'f' => number_format2($r['Gross Sales'], user_price_dec()));
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
$js = "google.load('visualization', '1', {'packages':['corechart','table']});\ngoogle.setOnLoadCallback(drawChart" . $id . ");\nfunction drawChart" . $id . "() {\n var data = new google.visualization.DataTable(" . $jsonTable . ");\n var options = {";
if ($this->graph_type != 'Table') {
$js .= "height: 300, ";
}
$js .= "title: '" . $title . "'\n };\n var chart" . $id . " = new google.visualization." . $this->graph_type . "(document.getElementById('widget_div_" . $id . "'));\n chart" . $id . ".draw(data, options);\n}";
add_js_source($js);
}
function render($id, $title)
{
global $path_to_root;
include_once $path_to_root . "/reporting/includes/class.graphic.inc";
$today = date2sql(Today());
if (!isset($data->days_past)) {
$this->days_past = 30;
}
if (!isset($data->days_future)) {
$this->days_future = 30;
}
$sql = "SELECT bank_act, bank_account_name, trans_date, amount" . " FROM (" . " SELECT bank_act, bank_account_name, null trans_date, SUM(amount) amount" . " FROM " . TB_PREF . "bank_trans bt" . " INNER JOIN " . TB_PREF . "bank_accounts ba ON bt.bank_act = ba.id" . " WHERE bank_act = " . $this->bank_act . " AND trans_date < now() - INTERVAL " . $this->days_past . " DAY" . " GROUP BY bank_act, bank_account_name" . " UNION ALL" . " SELECT bank_act, bank_account_name, trans_date, SUM(amount) amount" . " FROM 0_bank_trans bt" . " INNER JOIN " . TB_PREF . "bank_accounts ba ON bt.bank_act = ba.id" . " WHERE bank_act = " . $this->bank_act . " AND trans_date < now() + INTERVAL " . $this->days_future . " DAY" . " AND trans_date > now() - INTERVAL " . $this->days_past . " DAY" . " GROUP BY bank_act, trans_date, bank_account_name" . " ) trans" . " ORDER BY bank_account_name, trans_date";
$result = db_query($sql);
$rows = array();
//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(array('label' => 'Date', 'type' => 'string'), array('label' => 'Balance', 'type' => 'number'));
$rows = array();
$total = 0;
$last_day = 0;
$date = add_days(Today(), -$this->days_past);
$balance_date = $date;
while ($r = db_fetch_assoc($result)) {
if ($r['trans_date'] == null) {
$total = $r['amount'];
} else {
$balance_date = sql2date($r['trans_date']);
while (date1_greater_date2($balance_date, $date)) {
$temp = array();
$temp[] = array('v' => (string) $date, 'f' => $date);
$temp[] = array('v' => (double) $total, 'f' => number_format2($total, user_price_dec()));
$rows[] = array('c' => $temp);
$date = add_days($date, 1);
}
$total += $r['amount'];
$temp = array();
$temp[] = array('v' => (string) $balance_date, 'f' => $balance_date);
$temp[] = array('v' => (double) $total, 'f' => number_format2($total, user_price_dec()));
$rows[] = array('c' => $temp);
$date = $balance_date;
}
}
$end_date = add_days(Today(), $this->days_future);
while (date1_greater_date2($end_date, $date)) {
$temp = array();
$temp[] = array('v' => (string) $date, 'f' => $date);
$temp[] = array('v' => (double) $total, 'f' => number_format2($total, user_price_dec()));
$rows[] = array('c' => $temp);
$last_day++;
$date = add_days($date, 1);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
$js = "google.load('visualization', '1', {'packages':['corechart','table']});\ngoogle.setOnLoadCallback(drawChart" . $id . ");\nfunction drawChart" . $id . "() {\n var data = new google.visualization.DataTable(" . $jsonTable . ");\n var options = {";
if ($this->graph_type != 'Table') {
$js .= "height: 300, ";
}
$js .= "title: '" . $title . "'\n };\n var chart" . $id . " = new google.visualization." . $this->graph_type . "(document.getElementById('widget_div_" . $id . "'));\n chart" . $id . ".draw(data, options);\n}";
add_js_source($js);
}
function submit_js_confirm($name, $msg, $set = true)
{
global $Ajax;
$js = "_validate.{$name}=" . ($set ? "function(){ return confirm('" . strtr($msg, array("\n" => '\\n')) . "');};" : 'null;');
if (in_ajax()) {
$Ajax->addScript(true, $js);
} else {
add_js_source($js);
}
}