X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=reporting%2Frep101.php;h=3fee1e8f00c59deefcce79ac951c44d8d697c02c;hb=0489317205141deaecefe3a7243d11a3e38a51d0;hp=772d33f0cc6aec6a0199746f284dec5ed74a3c25;hpb=ebc600101ceab69c06eac4b1bd4d1782af45de05;p=fa-stable.git diff --git a/reporting/rep101.php b/reporting/rep101.php index 772d33f0..3fee1e8f 100644 --- a/reporting/rep101.php +++ b/reporting/rep101.php @@ -23,6 +23,7 @@ include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/data_checks.inc"); include_once($path_to_root . "/gl/includes/gl_db.inc"); +include_once($path_to_root . "/sales/includes/db/customers_db.inc"); //---------------------------------------------------------------------------------------------------- @@ -31,36 +32,38 @@ print_customer_balances(); function get_open_balance($debtorno, $to, $convert) { - $to = date2sql($to); + if($to) + $to = date2sql($to); - $sql = "SELECT SUM(IF(".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.", (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + - ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount)"; + $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE.", + (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)"; if ($convert) $sql .= " * rate"; $sql .= ", 0)) AS charges, - SUM(IF(".TB_PREF."debtor_trans.type <> ".ST_SALESINVOICE.", (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + - ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount)"; + SUM(IF(t.type <> ".ST_SALESINVOICE.", + (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount)"; if ($convert) $sql .= " * rate"; $sql .= " * -1, 0)) AS credits, - SUM(".TB_PREF."debtor_trans.alloc"; + SUM(t.alloc"; if ($convert) $sql .= " * rate"; $sql .= ") AS Allocated, - SUM(IF(".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.", (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + - ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc)"; + SUM(IF(t.type = ".ST_SALESINVOICE.", + (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount - t.alloc)"; if ($convert) $sql .= " * rate"; $sql .= ", - ((".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + - ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) * -1 + ".TB_PREF."debtor_trans.alloc)"; + ((t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1 + t.alloc)"; if ($convert) $sql .= " * rate"; $sql .= ")) AS OutStanding - FROM ".TB_PREF."debtor_trans - WHERE ".TB_PREF."debtor_trans.tran_date < '$to' - AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)." - AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY." GROUP BY debtor_no"; + FROM ".TB_PREF."debtor_trans t + WHERE t.debtor_no = ".db_escape($debtorno) + ." AND t.type <> ".ST_CUSTDELIVERY; + if ($to) + $sql .= " AND t.tran_date < '$to'"; + $sql .= " GROUP BY debtor_no"; $result = db_query($sql,"No transactions were returned"); return db_fetch($result); @@ -91,24 +94,28 @@ function get_transactions($debtorno, $from, $to) function print_customer_balances() { - global $path_to_root, $systypes_array; - - $from = $_POST['PARAM_0']; - $to = $_POST['PARAM_1']; - $fromcust = $_POST['PARAM_2']; - $currency = $_POST['PARAM_3']; - $comments = $_POST['PARAM_4']; - $destination = $_POST['PARAM_5']; + global $path_to_root, $systypes_array; + + $from = $_POST['PARAM_0']; + $to = $_POST['PARAM_1']; + $fromcust = $_POST['PARAM_2']; + $currency = $_POST['PARAM_3']; + $no_zeros = $_POST['PARAM_4']; + $comments = $_POST['PARAM_5']; + $orientation = $_POST['PARAM_6']; + $destination = $_POST['PARAM_7']; if ($destination) include_once($path_to_root . "/reporting/includes/excel_report.inc"); else include_once($path_to_root . "/reporting/includes/pdf_report.inc"); - if ($fromcust == ALL_NUMERIC) + $orientation = ($orientation ? 'L' : 'P'); + + if ($fromcust == ALL_TEXT) $cust = _('All'); else $cust = get_customer_name($fromcust); - $dec = user_price_dec(); + $dec = user_price_dec(); if ($currency == ALL_TEXT) { @@ -118,6 +125,9 @@ function print_customer_balances() else $convert = false; + if ($no_zeros) $nozeros = _('Yes'); + else $nozeros = _('No'); + $cols = array(0, 100, 130, 190, 250, 320, 385, 450, 515); $headers = array(_('Trans Type'), _('#'), _('Date'), _('Due Date'), _('Charges'), _('Credits'), @@ -128,41 +138,49 @@ function print_customer_balances() $params = array( 0 => $comments, 1 => array('text' => _('Period'), 'from' => $from, 'to' => $to), 2 => array('text' => _('Customer'), 'from' => $cust, 'to' => ''), - 3 => array('text' => _('Currency'), 'from' => $currency, 'to' => '')); - - $rep = new FrontReport(_('Customer Balances'), "CustomerBalances", user_pagesize()); + 3 => array('text' => _('Currency'), 'from' => $currency, 'to' => ''), + 4 => array('text' => _('Suppress Zeros'), 'from' => $nozeros, 'to' => '')); + $rep = new FrontReport(_('Customer Balances'), "CustomerBalances", user_pagesize(), 9, $orientation); + if ($orientation == 'L') + $rep->recalculate_cols($cols); $rep->Font(); $rep->Info($params, $cols, $headers, $aligns); - $rep->Header(); + $rep->NewPage(); $grandtotal = array(0,0,0,0); $sql = "SELECT debtor_no, name, curr_code FROM ".TB_PREF."debtors_master "; - if ($fromcust != ALL_NUMERIC) + if ($fromcust != ALL_TEXT) $sql .= "WHERE debtor_no=".db_escape($fromcust); $sql .= " ORDER BY name"; $result = db_query($sql, "The customers could not be retrieved"); + $num_lines = 0; while ($myrow = db_fetch($result)) { - if (!$convert && $currency != $myrow['curr_code']) - continue; + if (!$convert && $currency != $myrow['curr_code']) continue; + + $bal = get_open_balance($myrow['debtor_no'], $from, $convert); + $init[0] = $init[1] = 0.0; + $init[0] = round2(abs($bal['charges']), $dec); + $init[1] = round2(Abs($bal['credits']), $dec); + $init[2] = round2($bal['Allocated'], $dec); + $init[3] = round2($bal['OutStanding'], $dec);; + + $res = get_transactions($myrow['debtor_no'], $from, $to); + if ($no_zeros && db_num_rows($res) == 0) continue; + + $num_lines++; $rep->fontSize += 2; $rep->TextCol(0, 2, $myrow['name']); if ($convert) $rep->TextCol(2, 3, $myrow['curr_code']); $rep->fontSize -= 2; - $bal = get_open_balance($myrow['debtor_no'], $from, $convert); - $init[0] = $init[1] = 0.0; $rep->TextCol(3, 4, _("Open Balance")); - $init[0] = round2(abs($bal['charges']), $dec); $rep->AmountCol(4, 5, $init[0], $dec); - $init[1] = round2(Abs($bal['credits']), $dec); $rep->AmountCol(5, 6, $init[1], $dec); - $init[2] = round2($bal['Allocated'], $dec); $rep->AmountCol(6, 7, $init[2], $dec); - $init[3] = round2($bal['OutStanding'], $dec);; $rep->AmountCol(7, 8, $init[3], $dec); $total = array(0,0,0,0); for ($i = 0; $i < 4; $i++) @@ -171,12 +189,12 @@ function print_customer_balances() $grandtotal[$i] += $init[$i]; } $rep->NewLine(1, 2); - $res = get_transactions($myrow['debtor_no'], $from, $to); if (db_num_rows($res)==0) continue; $rep->Line($rep->row + 4); while ($trans = db_fetch($res)) { + if ($no_zeros && floatcmp($trans['TotalAmount'], $trans['Allocated']) == 0) continue; $rep->NewLine(1, 2); $rep->TextCol(0, 1, $systypes_array[$trans['type']]); $rep->TextCol(1, 2, $trans['reference']); @@ -224,8 +242,8 @@ function print_customer_balances() $rep->TextCol(0, 3, _('Total')); for ($i = 0; $i < 4; $i++) $rep->AmountCol($i + 4, $i + 5, $total[$i], $dec); - $rep->Line($rep->row - 4); - $rep->NewLine(2); + $rep->Line($rep->row - 4); + $rep->NewLine(2); } $rep->fontSize += 2; $rep->TextCol(0, 3, _('Grand Total')); @@ -234,7 +252,7 @@ function print_customer_balances() $rep->AmountCol($i + 4, $i + 5, $grandtotal[$i], $dec); $rep->Line($rep->row - 4); $rep->NewLine(); - $rep->End(); + $rep->End(); } -?> \ No newline at end of file +?>