+ 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);
+ $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 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 = sprintf(_("Last %s Months Performance"), $months);
+ check_page_security('SA_GLANALYTIC');
+ $month_names = $sales = $costs = $results = array();
+ while ($myrow = db_fetch($result))
+ {
+ $month_names[] = $myrow['month_name'];
+ $sales[] = round($myrow['sales']);
+ $costs[] = round($myrow['costs']);
+ $results[] = round($myrow['sales'] - $myrow['costs']);
+ }
+ $pg->setLabels($month_names);
+ $pg->addSerie(_('Sales'), $sales, 'bar');
+ $pg->addSerie(_('Costs'), $costs, 'bar');
+ $pg->addSerie(_('Results'), $results, 8);
+ 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;
+ // removed - debtor_trans.alloc from all summations
+ $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,
+ 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(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 supplier, ".TB_PREF."supp_trans trans
+ WHERE supplier.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, $per = '', $num = false)
+{
+ 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);