function get_transactions()
{
$date_after = date2sql($_POST['TransAfterDate']);
$date_to = date2sql($_POST['TransToDate']);
$sql = "SELECT debtor_trans.*,debtors_master.name AS CustName," . "debtors_master.curr_code AS CustCurrCode," . "(debtor_trans.ov_amount + debtor_trans.ov_gst + " . "debtor_trans.ov_freight + debtor_trans.ov_freight_tax + " . "debtor_trans.ov_discount) AS " . "TotalAmount, debtor_trans.alloc AS Allocated, " . "((debtor_trans.type = 10) " . "AND debtor_trans.due_date < '" . date2sql(Today()) . "') AS OverDue " . "FROM debtor_trans, debtors_master " . "WHERE debtors_master.debtor_no = debtor_trans.debtor_no " . "AND debtor_trans.tran_date >= '{$date_after}' " . "AND debtor_trans.tran_date <= '{$date_to}'";
if ($_POST['customer_id'] != reserved_words::get_all()) {
$sql .= " AND debtor_trans.debtor_no = '" . $_POST['customer_id'] . "'";
}
if ($_POST['filterType'] != reserved_words::get_all()) {
if ($_POST['filterType'] == '1') {
$sql .= " AND (debtor_trans.type = 10 OR debtor_trans.type = 1) ";
} elseif ($_POST['filterType'] == '2') {
$sql .= " AND (debtor_trans.type = 10) ";
} elseif ($_POST['filterType'] == '3') {
$sql .= " AND (debtor_trans.type = " . systypes::cust_payment() . " OR debtor_trans.type = 2) ";
} elseif ($_POST['filterType'] == '4') {
$sql .= " AND debtor_trans.type = 11 ";
} elseif ($_POST['filterType'] == '5') {
$sql .= " AND debtor_trans.type = 13 ";
}
if ($_POST['filterType'] == '2') {
$today = date2sql(Today());
$sql .= " AND debtor_trans.due_date < '{$today}' " . "AND (debtor_trans.ov_amount + debtor_trans.ov_gst + " . "debtor_trans.ov_freight_tax + debtor_trans.ov_freight + " . "debtor_trans.ov_discount - debtor_trans.alloc > 0) ";
}
}
$sql .= " ORDER BY debtor_trans.tran_date DESC,debtor_trans.type," . "debtor_trans.trans_no ";
return db_query($sql, "No transactions were returned");
}
function get_transactions($debtorno, $from, $to)
{
$from = date2sql($from);
$to = date2sql($to);
$sql = "SELECT " . TB_PREF . "debtor_trans.*,\n\t\t(" . TB_PREF . "debtor_trans.ov_amount + " . TB_PREF . "debtor_trans.ov_gst + " . TB_PREF . "debtor_trans.ov_freight + \n\t\t" . TB_PREF . "debtor_trans.ov_freight_tax + " . TB_PREF . "debtor_trans.ov_discount)\n\t\tAS TotalAmount, " . TB_PREF . "debtor_trans.alloc AS Allocated,\n\t\t((" . TB_PREF . "debtor_trans.type = " . ST_SALESINVOICE . ")\n\t\tAND " . TB_PREF . "debtor_trans.due_date < '{$to}') AS OverDue\n \tFROM " . TB_PREF . "debtor_trans\n \tWHERE " . TB_PREF . "debtor_trans.tran_date >= '{$from}'\n\t\tAND " . TB_PREF . "debtor_trans.tran_date <= '{$to}'\n\t\tAND " . TB_PREF . "debtor_trans.debtor_no = " . db_escape($debtorno) . "\n\t\tAND " . TB_PREF . "debtor_trans.type <> " . ST_CUSTDELIVERY . "\n \tORDER BY " . TB_PREF . "debtor_trans.tran_date";
return db_query($sql, "No transactions were returned");
}
function GetSalesmanTrans($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT DISTINCT " . TB_PREF . "debtor_trans.*,\n\t\tov_amount+ov_discount AS InvoiceTotal,\n\t\t" . TB_PREF . "debtors_master.name AS DebtorName, " . TB_PREF . "debtors_master.curr_code, " . TB_PREF . "cust_branch.br_name,\n\t\t" . TB_PREF . "cust_branch.contact_name, " . TB_PREF . "salesman.*\n\t\tFROM " . TB_PREF . "debtor_trans, " . TB_PREF . "debtors_master, " . TB_PREF . "sales_orders, " . TB_PREF . "cust_branch,\n\t\t\t" . TB_PREF . "salesman\n\t\tWHERE " . TB_PREF . "sales_orders.order_no=" . TB_PREF . "debtor_trans.order_\n\t\t AND " . TB_PREF . "sales_orders.branch_code=" . TB_PREF . "cust_branch.branch_code\n\t\t AND " . TB_PREF . "cust_branch.salesman=" . TB_PREF . "salesman.salesman_code\n\t\t AND " . TB_PREF . "debtor_trans.debtor_no=" . TB_PREF . "debtors_master.debtor_no\n\t\t AND (" . TB_PREF . "debtor_trans.type=" . ST_SALESINVOICE . " OR " . TB_PREF . "debtor_trans.type=" . ST_CUSTCREDIT . ")\n\t\t AND " . TB_PREF . "debtor_trans.tran_date>='{$fromdate}'\n\t\t AND " . TB_PREF . "debtor_trans.tran_date<='{$todate}'\n\t\tORDER BY " . TB_PREF . "salesman.salesman_code, " . TB_PREF . "debtor_trans.tran_date";
return db_query($sql, "Error getting order details");
}
function show_results()
{
global $path_to_root;
/*Now get the transactions */
div_start('trans_tbl');
start_table(TABLESTYLE);
$netAmounts = check_value('NetAmounts');
if ($netAmounts) {
$net = _("Net");
} else {
$net = _("Gross");
}
$th = array(_("Type"), _("Description"), _("Tax") . "<br>" . _("Amount"), $net . "<br>" . _("Outputs") . "/" . _("Inputs"));
table_header($th);
$k = 0;
$total = 0;
$bdate = date2sql($_POST['TransFromDate']);
$edate = date2sql($_POST['TransToDate']);
$taxes = get_tax_cash_summary($_POST['TransFromDate'], $_POST['TransToDate']);
while ($tx = db_fetch($taxes)) {
$payable = $tx['payable'];
$collectible = $tx['collectible'];
$net = $collectible + $payable;
$total += $net;
alt_table_row_color($k);
label_cell($tx['name'] . " " . $tx['rate'] . "%");
label_cell(_("Charged on sales") . " (" . _("Output Tax") . "):");
amount_cell($payable);
if ($netAmounts) {
amount_cell($tx['net_output']);
} else {
amount_cell($tx['gross_output']);
}
end_row();
alt_table_row_color($k);
label_cell($tx['name'] . " " . $tx['rate'] . "%");
label_cell(_("Paid on purchases") . " (" . _("Input Tax") . "):");
amount_cell($collectible);
if ($netAmounts) {
amount_cell($tx['net_input']);
} else {
amount_cell($tx['gross_input']);
}
end_row();
alt_table_row_color($k);
label_cell("<b>" . $tx['name'] . " " . $tx['rate'] . "%</b>");
label_cell("<b>" . _("Net payable or collectible") . ":</b>");
amount_cell($net, true);
label_cell("");
end_row();
}
alt_table_row_color($k);
label_cell("");
label_cell("<b>" . _("Total payable or refund") . ":</b>");
amount_cell($total, true);
label_cell("");
end_row();
end_table(2);
div_end();
}
function get_transactions()
{
global $db;
$date_after = date2sql($_POST['TransAfterDate']);
$date_to = date2sql($_POST['TransToDate']);
// Sherifoz 22.06.03 Also get the description
$sql = "SELECT supp_trans.type, supp_trans.trans_no,\n \tsupp_trans.tran_date, supp_trans.reference, supp_trans.supp_reference,\n \t(supp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount) AS TotalAmount, supp_trans.alloc AS Allocated,\n\t\t((supp_trans.type = 20 OR supp_trans.type = 21) AND supp_trans.due_date < '" . date2sql(Today()) . "') AS OverDue,\n \t(ABS(supp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount - supp_trans.alloc) <= 0.005) AS Settled,\n\t\tsuppliers.curr_code, suppliers.supp_name, supp_trans.due_date\n \tFROM supp_trans, suppliers\n \tWHERE suppliers.supplier_id = supp_trans.supplier_id\n \tAND supp_trans.tran_date >= '{$date_after}'\n \tAND supp_trans.tran_date <= '{$date_to}'";
if ($_POST['supplier_id'] != reserved_words::get_all()) {
$sql .= " AND supp_trans.supplier_id = '" . $_POST['supplier_id'] . "'";
}
if (isset($_POST['filterType']) && $_POST['filterType'] != reserved_words::get_all()) {
if ($_POST['filterType'] == '1') {
$sql .= " AND (supp_trans.type = 20 OR supp_trans.type = 2)";
} elseif ($_POST['filterType'] == '2') {
$sql .= " AND supp_trans.type = 20 ";
} elseif ($_POST['filterType'] == '3') {
$sql .= " AND (supp_trans.type = 22 OR supp_trans.type = 1) ";
} elseif ($_POST['filterType'] == '4' || $_POST['filterType'] == '5') {
$sql .= " AND supp_trans.type = 21 ";
}
if ($_POST['filterType'] == '2' || $_POST['filterType'] == '5') {
$today = date2sql(Today());
$sql .= " AND supp_trans.due_date < '{$today}' ";
}
}
$sql .= " ORDER BY supp_trans.tran_date";
return db_query($sql, "No supplier transactions were returned");
}
function render($id, $title)
{
global $path_to_root;
include_once $path_to_root . "/includes/ui.inc";
$today = date2sql(Today());
$sql = "SELECT bank_act, bank_account_name, 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";
if ($this->data_filter != '') {
$sql .= ' AND ' . $this->data_filter;
}
$sql .= " GROUP BY bank_act, bank_account_name" . " ORDER BY bank_account_name";
$result = db_query($sql);
br();
$th = array(_("Account"), _("Balance"));
start_table(TABLESTYLE, "width=98%");
table_header($th);
$k = 0;
//row colour counter
while ($myrow = db_fetch($result)) {
alt_table_row_color($k);
label_cell($myrow["bank_account_name"]);
amount_cell($myrow['balance']);
end_row();
}
end_table(1);
}
function GetSalesmanTrans($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT trans.*,\n\t\t\tov_amount+ov_discount AS InvoiceTotal,\n\t\t\tcust.name AS DebtorName,\n\t\t\tcust.curr_code,\n\t\t\tbranch.br_name,\n\t\t\tbranch.contact_name,\n\t\t\tsorder.customer_ref,\n\t\t\tsalesman.*\n\t\tFROM " . TB_PREF . "debtor_trans trans,\n\t\t\t " . TB_PREF . "debtors_master cust,\n\t\t\t " . TB_PREF . "sales_orders sorder,\n\t\t\t " . TB_PREF . "cust_branch branch,\n\t\t\t " . TB_PREF . "salesman salesman\n\t\tWHERE sorder.order_no=trans.order_\n\t\t AND sorder.branch_code=branch.branch_code\n\t\t AND branch.salesman=salesman.salesman_code\n\t\t AND trans.debtor_no=cust.debtor_no\n\t\t AND (trans.type=" . ST_SALESINVOICE . " OR trans.type=" . ST_CUSTCREDIT . ")\n\t\t AND trans.tran_date>='{$fromdate}'\n\t\t AND trans.tran_date<='{$todate}'\n\t\tGROUP BY salesman.salesman_code, trans.tran_date, trans.`type`, trans.trans_no";
return db_query($sql, "Error getting order details");
}
function getTransactions($supplier_id, $date)
{
$date = date2sql($date);
$sql = "SELECT supp_trans.*, sys_types.type_name,\n\t\t\t\t(supp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount)\n\t\t\t\tAS TotalAmount, supp_trans.alloc AS Allocated,\n\t\t\t\t((supp_trans.type = 20)\n\t\t\t\t\tAND supp_trans.due_date < '{$date}') AS OverDue\n \t\t\tFROM supp_trans, sys_types\n \t\t\tWHERE supp_trans.tran_date <= '{$date}' AND supp_trans.supplier_id = '{$supplier_id}'\n \t\t\t\tAND supp_trans.type = sys_types.type_id\n \t\t\t\tORDER BY supp_trans.tran_date";
$TransResult = db_query($sql, "No transactions were returned");
return $TransResult;
}
function get_transactions()
{
$data_after = date2sql($_POST['TransAfterDate']);
$date_to = date2sql($_POST['TransToDate']);
$sql = "SELECT debtor_trans.*,\n\t\tdebtors_master.name AS CustName, debtors_master.curr_code AS CustCurrCode,\n \t(debtor_trans.ov_amount + debtor_trans.ov_gst + " . "debtor_trans.ov_freight + debtor_trans.ov_freight_tax + debtor_trans.ov_discount)\n\t\tAS TotalAmount,\n\t\tdebtor_trans.alloc AS Allocated,\n\t\t((debtor_trans.type = 10)\n\t\tAND debtor_trans.due_date < '" . date2sql(Today()) . "') AS OverDue\n \tFROM debtor_trans, debtors_master\n \tWHERE debtors_master.debtor_no = debtor_trans.debtor_no\n\t\t\tAND (debtor_trans.ov_amount + debtor_trans.ov_gst + " . "debtor_trans.ov_freight + debtor_trans.ov_freight_tax + debtor_trans.ov_discount != 0)\n \t\tAND debtor_trans.tran_date >= '{$data_after}'\n \t\tAND debtor_trans.tran_date <= '{$date_to}'";
if ($_POST['customer_id'] != reserved_words::get_all()) {
$sql .= " AND debtor_trans.debtor_no = '" . $_POST['customer_id'] . "'";
}
if (isset($_POST['filterType']) && $_POST['filterType'] != reserved_words::get_all()) {
if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2') {
$sql .= " AND debtor_trans.type = 10 ";
} elseif ($_POST['filterType'] == '3') {
$sql .= " AND debtor_trans.type = " . systypes::cust_payment();
} elseif ($_POST['filterType'] == '4') {
$sql .= " AND debtor_trans.type = 11 ";
}
if ($_POST['filterType'] == '2') {
$today = date2sql(Today());
$sql .= " AND debtor_trans.due_date < '{$today}'\n\t\t\t\tAND (round(abs(debtor_trans.ov_amount + " . "debtor_trans.ov_gst + debtor_trans.ov_freight + " . "debtor_trans.ov_freight_tax + debtor_trans.ov_discount) - debtor_trans.alloc,6) > 0) ";
}
} else {
$sql .= " AND debtor_trans.type != 13 ";
}
if (!check_value('showSettled')) {
$sql .= " AND (round(abs(debtor_trans.ov_amount + debtor_trans.ov_gst + " . "debtor_trans.ov_freight + debtor_trans.ov_freight_tax + " . "debtor_trans.ov_discount) - debtor_trans.alloc,6) != 0) ";
}
$sql .= " ORDER BY debtor_trans.tran_date";
return db_query($sql, "No transactions were returned");
}
function GetSalesmanTrans($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT DISTINCT debtor_trans.*,\n\t\tov_amount+ov_discount AS InvoiceTotal,\n\t\tdebtors_master.name AS DebtorName, debtors_master.curr_code, cust_branch.br_name,\n\t\tcust_branch.contact_name, salesman.*\n\t\tFROM debtor_trans, debtors_master, sales_orders, cust_branch,\n\t\t\tsalesman\n\t\tWHERE sales_orders.order_no=debtor_trans.order_\n\t\t AND sales_orders.branch_code=cust_branch.branch_code\n\t\t AND cust_branch.salesman=salesman.salesman_code\n\t\t AND debtor_trans.debtor_no=debtors_master.debtor_no\n\t\t AND (debtor_trans.type=10 OR debtor_trans.type=11)\n\t\t AND debtor_trans.tran_date>='{$fromdate}'\n\t\t AND debtor_trans.tran_date<='{$todate}'\n\t\tORDER BY salesman.salesman_code, debtor_trans.tran_date";
return db_query($sql, "Error getting order details");
}
function get_bank_transactions($from, $to, $account)
{
$from = date2sql($from);
$to = date2sql($to);
$sql = "SELECT " . TB_PREF . "bank_trans.* FROM " . TB_PREF . "bank_trans\n\t\tWHERE " . TB_PREF . "bank_trans.bank_act = '{$account}'\n\t\tAND trans_date >= '{$from}'\n\t\tAND trans_date <= '{$to}'\n\t\tORDER BY trans_date," . TB_PREF . "bank_trans.id";
return db_query($sql, "The transactions for '{$account}' could not be retrieved");
}
function getTaxTransactions($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,\n\t taxrec.net_amount*ex_rate AS net_amount,\n\t\t\t\tIF(taxrec.trans_type=" . ST_BANKPAYMENT . " OR taxrec.trans_type=" . ST_BANKDEPOSIT . ", \n\t\t\t\t\tIF(gl.person_type_id<>" . PT_MISC . ", gl.memo_, gl.person_id), \n\t\t\t\t\tIF(ISNULL(supp.supp_name), debt.name, supp.supp_name)) as name,\n\t\t\t\tbranch.br_name\n\t\tFROM " . TB_PREF . "trans_tax_details taxrec\n\t\tLEFT JOIN " . TB_PREF . "tax_types tt\n\t\t\tON taxrec.tax_type_id=tt.id\n\t\tLEFT JOIN " . TB_PREF . "gl_trans gl \n\t\t\tON taxrec.trans_type=gl.type AND taxrec.trans_no=gl.type_no AND gl.amount<>0 AND\n\t\t\t(tt.purchasing_gl_code=gl.account OR tt.sales_gl_code=gl.account)\n\t\tLEFT JOIN " . TB_PREF . "supp_trans strans\n\t\t\tON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type\n\t\tLEFT JOIN " . TB_PREF . "suppliers as supp ON strans.supplier_id=supp.supplier_id\n\t\tLEFT JOIN " . TB_PREF . "debtor_trans dtrans\n\t\t\tON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type\n\t\tLEFT JOIN " . TB_PREF . "debtors_master as debt ON dtrans.debtor_no=debt.debtor_no\n\t\tLEFT JOIN " . TB_PREF . "cust_branch as branch ON dtrans.branch_code=branch.branch_code\n\t\tWHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0)\n\t\t\tAND taxrec.trans_type <> " . ST_CUSTDELIVERY . "\n\t\t\tAND taxrec.tran_date >= '{$fromdate}'\n\t\t\tAND taxrec.tran_date <= '{$todate}'\n\t\tORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";
return db_query($sql, "No transactions were returned");
}
function getTransactions($from, $to)
{
$from = date2sql($from);
$to = date2sql($to);
$sql = "SELECT " . TB_PREF . "grn_batch.id batch_no,\n\t\t\t" . TB_PREF . "grn_batch.supplier_id, \n " . TB_PREF . "purch_order_details.*,\n " . TB_PREF . "stock_master.description,\n\t\t\t" . TB_PREF . "grn_items.qty_recd,\n\t\t\t" . TB_PREF . "grn_items.quantity_inv,\n\t\t\t" . TB_PREF . "grn_items.id grn_item_id\n FROM " . TB_PREF . "stock_master,\n " . TB_PREF . "purch_order_details,\n " . TB_PREF . "grn_batch,\n\t\t\t" . TB_PREF . "grn_items \n WHERE " . TB_PREF . "stock_master.stock_id=" . TB_PREF . "purch_order_details.item_code\n AND " . TB_PREF . "grn_batch.purch_order_no=" . TB_PREF . "purch_order_details.order_no\n\t\tAND " . TB_PREF . "grn_batch.id = " . TB_PREF . "grn_items.grn_batch_id \n\t\tAND " . TB_PREF . "grn_items.po_detail_item = " . TB_PREF . "purch_order_details.po_detail_item\n AND " . TB_PREF . "grn_items.qty_recd>0\n AND " . TB_PREF . "grn_batch.delivery_date>='{$from}'\n AND " . TB_PREF . "grn_batch.delivery_date<='{$to}'\n ORDER BY " . TB_PREF . "stock_master.stock_id, " . TB_PREF . "grn_batch.delivery_date";
return db_query($sql, "No transactions were returned");
}
function getPeriods($yr, $mo, $id, $dimension, $dimension2, $balance = false)
{
$begin = date2sql(begin_fiscalyear());
$date13 = date('Y-m-d', mktime(0, 0, 0, $mo + 12, 1, $yr));
$date12 = date('Y-m-d', mktime(0, 0, 0, $mo + 11, 1, $yr));
$date11 = date('Y-m-d', mktime(0, 0, 0, $mo + 10, 1, $yr));
$date10 = date('Y-m-d', mktime(0, 0, 0, $mo + 9, 1, $yr));
$date09 = date('Y-m-d', mktime(0, 0, 0, $mo + 8, 1, $yr));
$date08 = date('Y-m-d', mktime(0, 0, 0, $mo + 7, 1, $yr));
$date07 = date('Y-m-d', mktime(0, 0, 0, $mo + 6, 1, $yr));
$date06 = date('Y-m-d', mktime(0, 0, 0, $mo + 5, 1, $yr));
$date05 = date('Y-m-d', mktime(0, 0, 0, $mo + 4, 1, $yr));
$date04 = date('Y-m-d', mktime(0, 0, 0, $mo + 3, 1, $yr));
$date03 = date('Y-m-d', mktime(0, 0, 0, $mo + 2, 1, $yr));
$date02 = date('Y-m-d', mktime(0, 0, 0, $mo + 1, 1, $yr));
$date01 = date('Y-m-d', mktime(0, 0, 0, $mo, 1, $yr));
if (!$balance) {
$sql = "SELECT SUM(CASE WHEN trans_date >= '{$date01}' AND trans_date < '{$date02}' THEN amount ELSE 0 END) AS per01,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date02}' AND trans_date < '{$date03}' THEN amount ELSE 0 END) AS per02,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date03}' AND trans_date < '{$date04}' THEN amount ELSE 0 END) AS per03,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date04}' AND trans_date < '{$date05}' THEN amount ELSE 0 END) AS per04,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date05}' AND trans_date < '{$date06}' THEN amount ELSE 0 END) AS per05,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date06}' AND trans_date < '{$date07}' THEN amount ELSE 0 END) AS per06,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date07}' AND trans_date < '{$date08}' THEN amount ELSE 0 END) AS per07,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date08}' AND trans_date < '{$date09}' THEN amount ELSE 0 END) AS per08,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date09}' AND trans_date < '{$date10}' THEN amount ELSE 0 END) AS per09,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date10}' AND trans_date < '{$date11}' THEN amount ELSE 0 END) AS per10,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date11}' AND trans_date < '{$date12}' THEN amount ELSE 0 END) AS per11,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date12}' AND trans_date < '{$date13}' THEN amount ELSE 0 END) AS per12,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$begin}' AND trans_date < '{$date13}' THEN amount ELSE 0 END) AS ytd,\n\t\t \t\tSUM(CASE WHEN trans_date >= '{$date01}' AND trans_date < '{$date13}' THEN amount ELSE 0 END) AS mon12\n \t\t\tFROM " . TB_PREF . "bank_trans\n\t\t\t\tWHERE bank_act='{$id}'";
} else {
$sql = "SELECT SUM(CASE WHEN trans_date < '{$date01}' THEN amount ELSE 0 END) AS per01,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date02}' THEN amount ELSE 0 END) AS per02,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date03}' THEN amount ELSE 0 END) AS per03,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date04}' THEN amount ELSE 0 END) AS per04,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date05}' THEN amount ELSE 0 END) AS per05,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date06}' THEN amount ELSE 0 END) AS per06,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date07}' THEN amount ELSE 0 END) AS per07,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date08}' THEN amount ELSE 0 END) AS per08,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date09}' THEN amount ELSE 0 END) AS per09,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date10}' THEN amount ELSE 0 END) AS per10,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date11}' THEN amount ELSE 0 END) AS per11,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date12}' THEN amount ELSE 0 END) AS per12,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$begin}' THEN amount ELSE 0 END) AS ytd,\n\t\t \t\tSUM(CASE WHEN trans_date < '{$date01}' THEN amount ELSE 0 END) AS mon12\n \t\t\tFROM " . TB_PREF . "bank_trans\n\t\t\t\tWHERE bank_act='{$id}'";
}
if ($dimension != 0) {
$sql .= " AND dimension_id = " . ($dimension < 0 ? 0 : db_escape($dimension));
}
if ($dimension2 != 0) {
$sql .= " AND dimension2_id = " . ($dimension2 < 0 ? 0 : db_escape($dimension2));
}
$result = db_query($sql, "Transactions for bank account {$id} could not be calculated");
return db_fetch($result);
}
function getTransactions($supplier, $date)
{
$date = date2sql($date);
$dec = user_price_dec();
$sql = "SELECT " . TB_PREF . "supp_trans.supp_reference,\n\t\t\t" . TB_PREF . "supp_trans.tran_date,\n\t\t\t" . TB_PREF . "supp_trans.due_date,\n\t\t\t" . TB_PREF . "supp_trans.trans_no,\n\t\t\t" . TB_PREF . "supp_trans.type,\n\t\t\t" . TB_PREF . "supp_trans.rate,\n\t\t\t(ABS(" . TB_PREF . "supp_trans.ov_amount) + ABS(" . TB_PREF . "supp_trans.ov_gst) - " . TB_PREF . "supp_trans.alloc) AS Balance,\n\t\t\t(ABS(" . TB_PREF . "supp_trans.ov_amount) + ABS(" . TB_PREF . "supp_trans.ov_gst) ) AS TranTotal\n\t\tFROM " . TB_PREF . "supp_trans\n\t\tWHERE " . TB_PREF . "supp_trans.supplier_id = '" . $supplier . "'\n\t\tAND ROUND(ABS(" . TB_PREF . "supp_trans.ov_amount),{$dec}) + ROUND(ABS(" . TB_PREF . "supp_trans.ov_gst),{$dec}) - \n\t\tROUND(" . TB_PREF . "supp_trans.alloc,{$dec}) != 0\n\t\tAND " . TB_PREF . "supp_trans.tran_date <='" . $date . "'\n\t\tORDER BY " . TB_PREF . "supp_trans.type,\n\t\t\t" . TB_PREF . "supp_trans.trans_no";
return db_query($sql, "No transactions were returned");
}
function getSuppInvTax($taxtype, $from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT SUM(unit_price * quantity * supp_trans.rate), SUM(amount*supp_trans.rate)\n\t\tFROM supp_invoice_items, supp_invoice_tax_items, supp_trans\n\t\t\t\tWHERE supp_invoice_items.supp_trans_type>=20\n\t\t\t\t\tAND supp_invoice_items.supp_trans_type<=21\n\t\t\t\t\tAND supp_invoice_items.supp_trans_no=supp_invoice_tax_items.supp_trans_no\n\t\t\t\t\tAND supp_invoice_items.supp_trans_type=supp_invoice_tax_items.supp_trans_type\n\t\t\t\t\tAND supp_invoice_items.supp_trans_no=supp_trans.trans_no\n\t\t\t\t\tAND supp_invoice_items.supp_trans_type=supp_trans.type\n\t\t\t\t\tAND supp_invoice_tax_items.tax_type_id={$taxtype}\n\t\t\t\t\tAND supp_trans.tran_date >= '{$fromdate}'\n\t\t\t\t\tAND supp_trans.tran_date <= '{$todate}'";
$result = db_query($sql, "No transactions were returned");
return db_fetch_row($result);
}
function getTransactions($supplier_id, $from, $to)
{
$from = date2sql($from);
$to = date2sql($to);
$sql = "SELECT " . TB_PREF . "supp_trans.*,\n\t\t\t\t(" . TB_PREF . "supp_trans.ov_amount + " . TB_PREF . "supp_trans.ov_gst + " . TB_PREF . "supp_trans.ov_discount)\n\t\t\t\tAS TotalAmount, " . TB_PREF . "supp_trans.alloc AS Allocated,\n\t\t\t\t((" . TB_PREF . "supp_trans.type = " . ST_SUPPINVOICE . ")\n\t\t\t\t\tAND " . TB_PREF . "supp_trans.due_date < '{$to}') AS OverDue\n \t\t\tFROM " . TB_PREF . "supp_trans\n \t\t\tWHERE " . TB_PREF . "supp_trans.tran_date >= '{$from}' AND " . TB_PREF . "supp_trans.tran_date <= '{$to}' \n \t\t\tAND " . TB_PREF . "supp_trans.supplier_id = '{$supplier_id}' AND " . TB_PREF . "supp_trans.ov_amount!=0\n \t\t\t\tORDER BY " . TB_PREF . "supp_trans.tran_date";
$TransResult = db_query($sql, "No transactions were returned");
return $TransResult;
}
function getTransactions($supplier_id, $date)
{
$date = date2sql($date);
$sql = "SELECT SUM((ov_amount+ov_discount)*rate) AS Turnover\n\t\tFROM " . TB_PREF . "supp_trans\n\t\tWHERE supplier_id=" . db_escape($supplier_id) . "\n\t\tAND (type=" . ST_SUPPINVOICE . " OR type=" . ST_SUPPCREDIT . ")\n\t\tAND tran_date >='{$date}'";
$result = db_query($sql, "No transactions were returned");
$row = db_fetch_row($result);
return $row[0];
}
function getTransactions($debtorno, $branchcode, $date)
{
$date = date2sql($date);
$sql = "SELECT SUM((ov_amount+ov_freight+ov_discount)*rate) AS Turnover\n\t\tFROM debtor_trans\n\t\tWHERE debtor_no='{$debtorno}'\n\t\tAND branch_code='{$branchcode}'\n\t\tAND (type=10 or type=11)\n\t\tAND trandate >='{$date}'";
$result = db_query($sql, "No transactions were returned");
$row = db_fetch_row($result);
return $row[0];
}
function getTaxTransactions($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,\n\t taxrec.net_amount*ex_rate AS net_amount,\n\t\t\t\tIF(ISNULL(supp.supp_name), debt.name, supp.supp_name) as name,\n\t\t\t\tbranch.br_name\n\t\tFROM " . TB_PREF . "trans_tax_details taxrec\n\t\tLEFT JOIN " . TB_PREF . "tax_types tt\n\t\t\tON taxrec.tax_type_id=tt.id\n\t\tLEFT JOIN " . TB_PREF . "supp_trans strans\n\t\t\tON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type\n\t\tLEFT JOIN " . TB_PREF . "suppliers as supp ON strans.supplier_id=supp.supplier_id\n\t\tLEFT JOIN " . TB_PREF . "debtor_trans dtrans\n\t\t\tON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type\n\t\tLEFT JOIN " . TB_PREF . "debtors_master as debt ON dtrans.debtor_no=debt.debtor_no\n\t\tLEFT JOIN " . TB_PREF . "cust_branch as branch ON dtrans.branch_code=branch.branch_code\n\t\tWHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0)\n\t\t\tAND taxrec.trans_type <> " . ST_CUSTDELIVERY . "\n\t\t\tAND taxrec.tran_date >= '{$fromdate}'\n\t\t\tAND taxrec.tran_date <= '{$todate}'\n\t\tORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";
//display_error($sql);
return db_query($sql, "No transactions were returned");
}
function getTransactions($debtorno, $branchcode, $date)
{
$date = date2sql($date);
$sql = "SELECT SUM((ov_amount+ov_freight+ov_discount)*rate) AS Turnover\n\t\tFROM " . TB_PREF . "debtor_trans\n\t\tWHERE debtor_no=" . db_escape($debtorno) . "\n\t\tAND branch_code=" . db_escape($branchcode) . "\n\t\tAND (type=" . ST_SALESINVOICE . " OR type=" . ST_CUSTCREDIT . ")\n\t\tAND tran_date >='{$date}'";
$result = db_query($sql, "No transactions were returned");
$row = db_fetch_row($result);
return $row[0];
}
function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimension = 0, $dimension2 = 0)
{
$from = date2sql($from_date);
$to = date2sql($to_date);
$sql = "SELECT SUM(amount) FROM budget_trans\n\t\tWHERE account='{$account}' AND tran_date >= '{$from}' AND tran_date <= '{$to}'\n\t\t AND dimension_id = {$dimension} AND dimension2_id = {$dimension2}";
$result = db_query($sql, "No budget accounts were returned");
$row = db_fetch_row($result);
return $row[0];
}
function getTransactions($from, $to)
{
$from = date2sql($from);
$to = date2sql($to);
$sql = "SELECT purch.order_no,\n\t\tsupp.supp_name, purch_det.description,\n\t\tpurch_det.quantity_ordered, purch_det.unit_price\n\n\t\tFROM " . TB_PREF . "purch_orders purch,\n\t\t" . TB_PREF . "purch_order_details purch_det,\n\t\t" . TB_PREF . "suppliers supp\n\t\tWHERE supp.supplier_id = purch.supplier_id\n\t\tAND purch.order_no = purch_det.order_no\n\t\tAND purch.ord_date>='{$from}'\n\t\tAND purch.ord_date<='{$to}'\n\n\t\t";
$sql .= " GROUP BY purch.order_no,\n\t\tsupp.supp_name, purch_det.description\n\t\tORDER BY supp.supp_name";
//display_notification($sql);
return db_query($sql, "No transactions were returned");
}
function get_invoices($supplier_id, $to)
{
$todate = date2sql($to);
$PastDueDays1 = get_company_pref('past_due_days');
$PastDueDays2 = 2 * $PastDueDays1;
// Revomed allocated from sql
$sql = "SELECT sys_types.type_name, \n\t\t\tsupp_trans.reference, \n\t\t\tsupp_trans.tran_date, \n\t\t\t(supp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount) as Balance,\n\t\t\tIF (payment_terms.days_before_due > 0,\n\t\t\t\tCASE WHEN TO_DAYS('{$todate}') - TO_DAYS(supp_trans.tran_date) >= payment_terms.days_before_due \n\t\t\t\tTHEN \n\t\t\t\t\tsupp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount \n\t\t\t\tELSE\n\t\t\t\t\t0 \n\t\t\t\tEND,\n\t\t\t\t\n\t\t\t\tCASE WHEN TO_DAYS('{$todate}') - TO_DAYS(DATE_ADD(DATE_ADD(supp_trans.tran_date, \n\t\t\t\t\tINTERVAL 1 MONTH), INTERVAL (payment_terms.day_in_following_month - \n\t\t\t\t\tDAYOFMONTH(supp_trans.tran_date)) DAY)) >= 0 \n\t\t\t\tTHEN \n\t\t\t\t\tsupp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount \n\t\t\t\tELSE \n\t\t\t\t\t0 \n\t\t\t\tEND\n\t\t\t) AS Due,\n\t\t\tIF (payment_terms.days_before_due > 0,\n\t\t\t\tCASE WHEN TO_DAYS('{$todate}') - TO_DAYS(supp_trans.tran_date) > payment_terms.days_before_due \n\t\t\t\t\tAND TO_DAYS('{$todate}') - TO_DAYS(supp_trans.tran_date) >= (payment_terms.days_before_due + {$PastDueDays1}) \n\t\t\t\tTHEN \n\t\t\t\t\tsupp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount \n\t\t\t\tELSE \n\t\t\t\t\t0 \n\t\t\t\tEND,\n\n\t\t\t\tCASE WHEN TO_DAYS('{$todate}') - TO_DAYS(DATE_ADD(DATE_ADD(supp_trans.tran_date, \n\t\t\t\t\tINTERVAL 1 MONTH), INTERVAL (payment_terms.day_in_following_month - \n\t\t\t\t\tDAYOFMONTH(supp_trans.tran_date)) DAY)) >= {$PastDueDays1} \n\t\t\t\tTHEN \n\t\t\t\t\tsupp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount \n\t\t\t\tELSE \n\t\t\t\t\t0 \n\t\t\t\tEND\n\t\t\t) AS Overdue1,\n\t\t\tIF (payment_terms.days_before_due > 0,\n\t\t\t\tCASE WHEN TO_DAYS('{$todate}') - TO_DAYS(supp_trans.tran_date) > payment_terms.days_before_due \n\t\t\t\t\tAND TO_DAYS('{$todate}') - TO_DAYS(supp_trans.tran_date) >= (payment_terms.days_before_due + {$PastDueDays2}) \n\t\t\t\tTHEN \n\t\t\t\t\tsupp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount \n\t\t\t\tELSE \n\t\t\t\t\t0 \n\t\t\t\tEND,\n\n\t\t\t\tCASE WHEN TO_DAYS('{$todate}') - TO_DAYS(DATE_ADD(DATE_ADD(supp_trans.tran_date,\n\t\t\t\t\tINTERVAL 1 MONTH), INTERVAL (payment_terms.day_in_following_month - \n\t\t\t\t\tDAYOFMONTH(supp_trans.tran_date)) DAY)) >= {$PastDueDays2} \n\t\t\t\tTHEN \n\t\t\t\t\tsupp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount \n\t\t\t\tELSE \n\t\t\t\t\t0 \n\t\t\t\tEND\n\t\t\t) AS Overdue2\n\t \n\t \t\tFROM suppliers, \n\t\t\t\tpayment_terms, \n\t\t\t\tsupp_trans, \n\t\t\t\tsys_types\n\t \n\t \t\tWHERE sys_types.type_id = supp_trans.type \n\t\t\t\tAND suppliers.payment_terms = payment_terms.terms_indicator \n\t\t\t\tAND suppliers.supplier_id = supp_trans.supplier_id\n\t\t\t\tAND supp_trans.supplier_id = {$supplier_id} \n\t\t\t\tAND supp_trans.tran_date <= '{$todate}' \n\t\t\t\tAND ABS(supp_trans.ov_amount + supp_trans.ov_gst + supp_trans.ov_discount) > 0.004\n\t\t\t\tORDER BY supp_trans.tran_date";
return db_query($sql, "The supplier details could not be retrieved");
}
function render($id, $title)
{
global $path_to_root;
include_once $path_to_root . "/includes/ui.inc";
include_once $path_to_root . "/reporting/includes/class.graphic.inc";
if (!defined('FLOAT_COMP_DELTA')) {
define('FLOAT_COMP_DELTA', 0.004);
}
if (!isset($this->top)) {
$this->top = 10;
}
$begin = begin_fiscalyear();
$today = Today();
$begin1 = date2sql($begin);
$today1 = date2sql($today);
$sql = "SELECT SUM((trans.ov_amount + trans.ov_discount) * rate) AS total, s.supplier_id, s.supp_name FROM\n " . TB_PREF . "supp_trans AS trans, " . TB_PREF . "suppliers AS s WHERE trans.supplier_id=s.supplier_id\n AND (trans.type = " . ST_SUPPINVOICE . " OR trans.type = " . ST_SUPPCREDIT . ")\n AND tran_date >= '{$begin1}' AND tran_date <= '{$today1}' ";
if ($this->data_filter != '') {
$sql .= ' AND ' . $this->data_filter;
}
$sql .= "GROUP by s.supplier_id ORDER BY total DESC, s.supplier_id " . " LIMIT " . $this->top;
$result = db_query($sql);
if ($this->graph_type == 'Table') {
$th = array(_("Supplier"), _("Amount"));
start_table(TABLESTYLE, "width=98%");
table_header($th);
$k = 0;
//row colour counter
while ($myrow = db_fetch($result)) {
alt_table_row_color($k);
$name = $myrow["supplier_id"] . " " . $myrow["supp_name"];
label_cell($name);
amount_cell($myrow['total']);
end_row();
}
end_table(1);
} else {
$pg = new graph();
$i = 0;
while ($myrow = db_fetch($result)) {
$name = $myrow["supplier_id"] . " " . $myrow["supp_name"];
$pg->x[$i] = $name;
$pg->y[$i] = $myrow['total'];
$i++;
}
$pg->title = $title;
$pg->axis_x = _("Supplier");
$pg->axis_y = _("Amount");
$pg->graphic_1 = $today;
$pg->type = 2;
$pg->skin = 1;
$pg->built_in = false;
$filename = company_path() . "/pdf_files/" . uniqid("") . ".png";
$pg->display($filename, true);
echo "<img src='{$filename}' border='0' alt='{$title}' style='max-width:100%'>";
}
}
function getTransactions($from)
{
$fromdate = date2sql($from);
$sql = "SELECT a.*, DATE_FORMAT(a.tran_date, '%m-%d-%Y') as tranDate, c.*, d.*, v.type as IsVoid from " . TB_PREF . "debtor_trans a \n\t\t\tLEFT JOIN " . TB_PREF . "voided v ON v.type = a.type and v.id=a.trans_no\n\t\t\tINNER JOIN " . TB_PREF . "cust_branch c on a.branch_code=c.branch_code AND a.debtor_no=c.debtor_no \n\t\t\tINNER JOIN " . TB_PREF . "customized d on a.type=d.type AND a.trans_no=d.type_no\n\t\t\twhere a.tran_date >= '{$fromdate}' AND a.payment_terms = 4 AND a.type=" . ST_SALESINVOICE . "";
if ($imc != 0) {
$sql .= " and " . TB_PREF . "salesman.salesman_code =" . db_escape($imc);
}
//display_notification($sql);
return db_query($sql, "No transactions were returned");
}
function getTransactions($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT DISTINCT a.*, b.customized_no, b.check_num, v.type as IsVoid, \n DATE_FORMAT(a.tran_date, '%d-%b') as tranDate \n from " . TB_PREF . "gl_trans a \n INNER JOIN " . TB_PREF . "customized b \n on a.type=b.type AND a.type_no=b.type_no\n LEFT JOIN " . TB_PREF . "voided v ON v.type = a.type and v.id=a.type_no where a.type=" . ST_DISBURSEMENT . " \n OR a.type=" . ST_SUPPAYMENT . " AND a.tran_date >='{$fromdate}' AND a.tran_date <='{$todate}' \n ORDER BY CAST(b.customized_no as UNSIGNED)";
//if ($imc != 0)
// $sql .= " and ".TB_PREF."salesman.salesman_code =".db_escape($imc);
//display_notification($sql);
return db_query($sql, "No transactions were returned");
}
function getTransactions($from, $to)
{
$fromdate = date2sql($from);
$todate = date2sql($to);
$sql = "SELECT DISTINCT a.*, b.customized_no, b.check_num, v.type, DATE_FORMAT(a.tran_date, '%d-%b') as tranDate from " . TB_PREF . "gl_trans a inner join " . TB_PREF . "customized b on a.type=b.type AND a.type_no=b.type_no\n LEFT JOIN " . TB_PREF . "voided v ON v.type = a.type and v.id=a.type_no where a.type=" . ST_DISBURSEMENT . " AND a.tran_date >='{$fromdate}' AND a.tran_date <='{$todate}' ORDER BY b.customized_no ASC";
//if ($imc != 0)
// $sql .= " and ".TB_PREF."salesman.salesman_code =".db_escape($imc);
//display_notification($sql);
return db_query($sql, "No transactions were returned");
}
function render($id, $title)
{
global $path_to_root;
include_once $path_to_root . "/reporting/includes/class.graphic.inc";
$begin = begin_fiscalyear();
$today = Today();
$begin1 = date2sql($begin);
$today1 = date2sql($today);
$sql = "SELECT SUM(amount) AS total, c.class_name, c.ctype FROM\n " . TB_PREF . "gl_trans," . TB_PREF . "chart_master AS a, " . TB_PREF . "chart_types AS t,\n " . TB_PREF . "chart_class AS c WHERE\n account = a.account_code AND a.account_type = t.id AND t.class_id = c.cid\n AND IF(c.ctype > 3, tran_date >= '{$begin1}', tran_date >= '0000-00-00')\n AND tran_date <= '{$today1}' ";
if ($this->data_filter != '') {
$sql .= ' AND ' . $this->data_filter;
}
$sql .= " GROUP BY c.cid ORDER BY c.cid";
$result = db_query($sql, "Transactions could not be calculated");
$calculated = _("Calculated Return");
if ($this->graph_type == 'Table') {
start_table(TABLESTYLE2, "width=98%");
$total = 0;
while ($myrow = db_fetch($result)) {
if ($myrow['ctype'] > 3) {
$total += $myrow['total'];
$myrow['total'] = -$myrow['total'];
}
label_row($myrow['class_name'], number_format2($myrow['total'], user_price_dec()), "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
}
label_row(" ", "");
label_row($calculated, number_format2(-$total, user_price_dec()), "class='label' style='font-weight:bold;'", "style='font-weight:bold;' align=right");
end_table(1);
} else {
$pg = new graph();
$i = 0;
$total = 0;
while ($myrow = db_fetch($result)) {
if ($myrow['ctype'] > 3) {
$total += $myrow['total'];
$myrow['total'] = -$myrow['total'];
$pg->x[$i] = $myrow['class_name'];
$pg->y[$i] = abs($myrow['total']);
$i++;
}
}
$pg->x[$i] = $calculated;
$pg->y[$i] = -$total;
$pg->title = $title;
$pg->axis_x = _("Class");
$pg->axis_y = _("Amount");
$pg->graphic_1 = $today;
$pg->type = 5;
$pg->skin = 1;
$pg->built_in = false;
$filename = company_path() . "/pdf_files/" . uniqid("") . ".png";
$pg->display($filename, true);
echo "<img src='{$filename}' border='0' alt='{$title}' style='max-width:100%'>";
}
}
function render($id, $title)
{
global $path_to_root;
include_once $path_to_root . "/reporting/includes/class.graphic.inc";
if (!defined('FLOAT_COMP_DELTA')) {
define('FLOAT_COMP_DELTA', 0.004);
}
if (!isset($this->top)) {
$this->top = 10;
}
$begin = begin_fiscalyear();
$today = Today();
$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" . " AND (trans.type = " . ST_SALESINVOICE . " OR trans.type = " . ST_CUSTCREDIT . ")" . " AND tran_date >= '{$begin1}' AND tran_date <= '{$today1}'";
if ($this->data_filter != '') {
$sql .= ' AND ' . $this->data_filter;
}
$sql .= " GROUP by d.debtor_no ORDER BY total DESC, d.debtor_no " . " LIMIT " . $this->top;
$result = db_query($sql);
if ($this->graph_type == 'Table') {
$th = array(null, _("Customer"), _("Amount"));
start_table(TABLESTYLE, "width=98%");
table_header($th);
$k = 0;
//row colour counter
$i = 0;
while ($myrow = db_fetch($result)) {
alt_table_row_color($k);
label_cell(viewer_link($myrow["debtor_no"], 'sales/inquiry/customer_inquiry.php?customer_id=' . $myrow["debtor_no"]));
label_cell(viewer_link($myrow["name"], 'sales/inquiry/customer_inquiry.php?customer_id=' . $myrow["debtor_no"]));
amount_cell($myrow['total']);
end_row();
}
end_table(1);
} else {
$pg = new graph();
$i = 0;
while ($myrow = db_fetch($result)) {
$pg->x[$i] = $myrow["debtor_no"] . " " . $myrow["name"];
$pg->y[$i] = $myrow['total'];
$i++;
}
$pg->title = $title;
$pg->axis_x = _("Customer");
$pg->axis_y = _("Amount");
$pg->graphic_1 = $today;
$pg->type = 2;
$pg->skin = 1;
$pg->built_in = false;
$filename = company_path() . "/pdf_files/" . uniqid("") . ".png";
$pg->display($filename, true);
echo "<img src='{$filename}' border='0' alt='{$title}' style='max-width:100%'>";
}
}