Search options (Page 168 of 246)

SELECT trans.*
      ,ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total
      ,cust.name AS DebtorName
      , cust.address
      , cust.curr_code
      , cust.tax_id
      , com.memo_
      , bank_act
      , bank_accounts.bank_name
      , bank_accounts.bank_account_name
      , bank_accounts.account_type AS BankTransType
      , bank_accounts.bank_curr_code
      , bank_trans.amount AS bank_amount 
FROM 1_debtor_trans trans 
    LEFT JOIN 1_comments com ON trans.type=com.type AND trans.trans_no=com.id 
    LEFT JOIN 1_shippers shippers ON shippers.shipper_id=trans.ship_via
    , 1_debtors_master cust
    , 1_bank_trans bank_trans
    , 1_bank_accounts bank_accounts
WHERE trans.trans_no='360' 
  AND trans.type='2' 
  AND trans.debtor_no=cust.debtor_no 
  AND bank_trans.trans_no ='360' 
  AND bank_trans.type=2 
  AND bank_trans.amount != 0 
  AND bank_accounts.id=bank_trans.bank_act;

--- 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);

--- 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'] != '' 

SELECT COUNT(DISTINCT gl.tran_date, a.gl_seq, gl.type, gl.type_no) FROM 1_gl_trans as gl
         LEFT JOIN 1_audit_trail as a ON
            (gl.type=a.type AND gl.type_no=a.trans_no)
         LEFT JOIN 1_comments as com ON
            (gl.type=com.type AND gl.type_no=com.id)
         LEFT JOIN 1_refs as refs ON
            (gl.type=refs.type AND gl.type_no=refs.id)
         LEFT JOIN 1_users as u ON
            a.user=u.id
      WHERE (gl.tran_date >= '2015-03-08'
        AND gl.tran_date <= '2015-04-08'
        AND gl.amount!=0 AND gl.type='0');

            return "SELECT COUNT($group) FROM $from $where";

CREATE TABLE `1_comments` (
  `type` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL DEFAULT '0',
  `date_` date DEFAULT '0000-00-00',
  `memo_` tinytext,
  KEY `type_and_id` (`type`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

php -r 'var_dump(function_exists("gzfile"));' 
bool(true) 
php -r 'var_dump(function_exists("gzopen"));' 
bool(false) 
php -r 'var_dump(function_exists("gzopen64"));' 
bool(true)