The following is the main fix for the journal inquiry issue since we need to remove NULLs in the SQLs:
--- gl/includes/db/gl_db_trans.inc Fri Jan 16 08:56:28 1970
+++ gl/includes/db/gl_db_trans.inc Fri Jan 16 08:56:28 1970
@@ -605,14 +605,14 @@
function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false)
{
- $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
+ $sql = "SELECT COALESCE(a.gl_seq,0) as gl_seq,
gl.tran_date,
gl.type,
gl.type_no,
- refs.reference,
+ COALESCE(refs.reference,'') AS reference,
SUM(IF(gl.amount>0, gl.amount,0)) as amount,
- com.memo_,
- IF(ISNULL(u.user_id),'',u.user_id) as user_id
+ COALESCE(com.memo_,'') AS memo_,
+ COALESCE(u.user_id,'') as user_id
FROM ".TB_PREF."gl_trans as gl
LEFT JOIN ".TB_PREF."audit_trail as a ON
(gl.type=a.type AND gl.type_no=a.trans_no)
@@ -625,11 +625,11 @@
WHERE gl.tran_date >= '" . date2sql($from) . "'
AND gl.tran_date <= '" . date2sql($to) . "'
AND gl.amount!=0";
- if ($ref) {
- $sql .= " AND reference LIKE ". db_escape("%$ref%");
+ if (!empty($ref)) {
+ $sql .= " AND refs.reference LIKE '%". db_escape($ref) . "%'";
}
- if ($memo) {
- $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%");
+ if (!empty($memo)) {
+ $sql .= " AND com.memo_ LIKE '%". db_escape($memo) . "%'";
}
if ($filter != -1) {
$sql .= " AND gl.type=".db_escape($filter);
Now we also need to tolerate NULLs in fields in GROUP BY statements which otherwise evaluate to a count of 0 records in the db_pager.inc and make sure that no negative LIMIT is part of the SELECT clause since the first value of the page is 0 and on deducting 1 for previous page will return a negative number causing the sql to end in LIMIT -10, 10:
--- includes/db_pager.inc Fri Jan 16 08:56:28 1970
+++ includes/db_pager.inc Fri Jan 16 08:56:28 1970
@@ -325,6 +325,7 @@
$page_len = $this->page_len;
$offset = ($this->curr_page - 1) * $page_len;
+ $offset = ($offset < 0 ? 0 : $offset);
$sql .= " LIMIT $offset, $page_len";
@@ -345,11 +346,19 @@
return false;
$row = db_fetch_row($result);
$this->rec_count = $row[0];
+ // 2015-04-09 apmuthu: try original sql since count = 0 if group by clause has NULLs
+ if (!$row[0] && !empty($this->group)) {
+ $sql = $this->_sql_gen(false);
+ $result = db_query($sql, 'Error reading record set');
+ if ($result == false)
+ return false;
+ $this->rec_count = db_num_rows($result);
+ }
$this->max_page = $this->page_len ?
ceil($this->rec_count/$this->page_len) : 0;
if ($go_debug) { // FIX - need column name parsing, but for now:
- // check if field names are set explicite in col def
+ // check if field names are set explicitly in col def
// for all initially ordered columns
foreach ($this->columns as $col) {
if (isset($col['ord']) && $col['ord'] != ''
Post's attachmentsJournal_Inquiry_patch.zip 56 kb, 1 downloads since 2015-04-08
You don't have the permssions to download the attachments of this post.
