X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_trans.inc;h=15e2c84a36f2f023e46454b2d84e8dd16bd89c2d;hb=64f24ae3a63762d18b1ab6408263ec6ca1e14fb2;hp=4749b41354c7c8c277f8e5a679e4a9b3ae2eb6fe;hpb=ae038fe92d264f26d66fd8e0917d7eb845bd65bb;p=fa-stable.git diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 4749b413..15e2c84a 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -17,7 +17,7 @@ // if $currency is not set, then defaults to no conversion function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_, - $amount, $currency=null, $person_type_id=null, $person_id=null, $err_msg="", $rate=0) + $amount, $currency=null, $person_type_id=null, $person_id=null, $rate=0) { global $SysPrefs; @@ -63,10 +63,7 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension $sql .= ") "; - if ($err_msg == "") - $err_msg = "The GL transaction could not be inserted"; - - db_query($sql, $err_msg); + db_query($sql, "The GL transaction could not be inserted"); return $amount_in_home_currency; } @@ -77,12 +74,12 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension // $amount is in HOME currency function add_gl_trans_std_cost($type, $trans_id, $date_, $account, $dimension, $dimension2, - $memo_, $amount, $person_type_id=null, $person_id=null, $err_msg="") + $memo_, $amount, $person_type_id=null, $person_id=null) { if ($amount != 0) return add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_, - $amount, null, $person_type_id, $person_id, $err_msg); + $amount, null, $person_type_id, $person_id); else return 0; } @@ -96,7 +93,7 @@ function add_gl_balance($type, $trans_id, $date_, $amount, $person_type_id=null, { error_log(sprintf( _("Rounding error %s encountered for trans_type:%s,trans_no:%s"), $amount, $type, $trans_id)); return add_gl_trans($type, $trans_id, $date_, get_company_pref('exchange_diff_act'), 0, 0, "", - $amount, null, $person_type_id, $person_id, "The balanced GL transaction could not be inserted"); + $amount, null, $person_type_id, $person_id); } else return 0; } @@ -104,75 +101,85 @@ function add_gl_balance($type, $trans_id, $date_, $amount, $person_type_id=null, //-------------------------------------------------------------------------------- function get_gl_transactions($from_date, $to_date, $trans_no=0, - $account=null, $dimension=0, $dimension2=0, $filter_type=null, - $amount_min=null, $amount_max=null, $person_id=null) + $account=null, $dimension=0, $dimension2=0, $filter_type=null, + $amount_min=null, $amount_max=null, $person_type=null, $person_id=null, $memo='') { - global $SysPrefs; + global $SysPrefs; - $from = date2sql($from_date); - $to = date2sql($to_date); + $from = date2sql($from_date); + $to = date2sql($to_date); - $sql = "SELECT gl.*, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode, - IFNULL(IFNULL(sup.supp_name, debt.name), bt.person_id) as person_name, - IFNULL(gl.person_id, IFNULL(sup.supplier_id, debt.debtor_no)) as person_id, - IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date, - coa.account_name, ref.reference - FROM " - .TB_PREF."gl_trans gl - LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id AND v.type=gl.type + $sql = "SELECT gl.*, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode, + IFNULL(IFNULL(sup.supp_name, debt.name), bt.person_id) as person_name, + IFNULL(gl.person_id, IFNULL(sup.supplier_id, IFNULL(debt.debtor_no, bt.person_id))) as person_id, + IF(gl.person_id, gl.person_type_id, IF(sup.supplier_id,". PT_SUPPLIER . "," . "IF(debt.debtor_no," . PT_CUSTOMER . "," . + "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_id, + IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date, + coa.account_name, ref.reference, IF(ISNULL(c.memo_), gl.memo_, CONCAT(gl.memo_,' ',c.memo_)) AS memo + FROM " + .TB_PREF."gl_trans gl + LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id AND v.type=gl.type - LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=st.supplier_id) - LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND gl.person_id=grn.supplier_id - LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=dt.debtor_no) + LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=st.supplier_id) + LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." + LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=dt.debtor_no) - LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id OR grn.supplier_id=sup.supplier_id - LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code - LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no + LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id + LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code + LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no - LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0 - AND bt.person_type_id=gl.person_type_id AND bt.person_id=gl.person_id + LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0 + AND (bt.person_id != '' AND !ISNULL(bt.person_id)) - LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no - LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq) - LEFT JOIN ".TB_PREF."users u ON a.user=u.id + LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no + LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq) + LEFT JOIN ".TB_PREF."users u ON a.user=u.id + LEFT JOIN ".TB_PREF."comments c ON c.id=gl.type_no AND c.type=gl.type - LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no," - .TB_PREF."chart_master coa - WHERE coa.account_code=gl.account - AND ISNULL(v.date_) - AND gl.tran_date >= '$from' - AND gl.tran_date <= '$to' - AND gl.amount <> 0"; + LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no," + .TB_PREF."chart_master coa + WHERE coa.account_code=gl.account + AND ISNULL(v.date_) + AND gl.tran_date >= '$from' + AND gl.tran_date <= '$to' + AND gl.amount <> 0"; - if ($person_id) - $sql .= " AND gl.person_id=".db_escape($person_id); + if ($trans_no > 0) + $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no); - if ($trans_no > 0) - $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);; + if ($account != null) + $sql .= " AND gl.account = ".db_escape($account); - if ($account != null) - $sql .= " AND gl.account = ".db_escape($account); + if ($dimension != 0) + $sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); - if ($dimension > 0) - $sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); + if ($dimension2 != 0) + $sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)); - if ($dimension2 > 0) - $sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)); + if ($filter_type != null) + $sql .= " AND gl.type IN (" . $filter_type .")"; - if ($filter_type != null AND is_numeric($filter_type)) - $sql .= " AND gl.type= ".db_escape($filter_type); + if ($amount_min != null) + $sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")"; + + if ($amount_max != null) + $sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")"; - if ($amount_min != null) - $sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")"; - - if ($amount_max != null) - $sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")"; + if ($memo) + $sql .= " AND (gl.memo_ LIKE ". db_escape("%$memo%") . " OR c.memo_ LIKE " . db_escape("%$memo%") . ")"; - $sql .= " ORDER BY tran_date, counter"; + $sql .= " GROUP BY counter"; - return db_query($sql, "The transactions for could not be retrieved"); -} + $sql .= " HAVING TRUE"; + if ($person_type != 0) + $sql .= " AND person_type_id=".db_escape($person_type); + if ($person_id != 0) + $sql .= " AND person_id=".db_escape($person_id); + $sql .= " ORDER BY tran_date, counter"; + + return db_query($sql, "The transactions for could not be retrieved"); +} //-------------------------------------------------------------------------------- @@ -419,7 +426,7 @@ function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimensio //-------------------------------------------------------------------------------- // Stores journal/bank transaction tax details if applicable // -function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, $date, $memo, $included=0, $net_amount = null) +function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, $date, $memo, $included=0, $net_amount = null, $tax_group_id=null) { $tax_type = is_tax_account($gl_code); if(!$tax_type) return; // $gl_code is not tax account @@ -437,7 +444,7 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, } } add_trans_tax_details($trans_type, $trans_no, $tax['id'], $tax['rate'], $included, - $amount, $net_amount, $ex_rate, $date, $memo, null); + $amount, $net_amount, $ex_rate, $date, $memo, null, $tax_group_id); } @@ -447,7 +454,7 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, // actual tax type rate. // function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included, - $amount, $net_amount, $ex_rate, $tran_date, $memo, $reg_type=null) + $amount, $net_amount, $ex_rate, $tran_date, $memo, $reg_type=null, $tax_group_id=null, $vat_category=0) { // guess tax register if not set if (!isset($reg_type)) @@ -456,12 +463,18 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included $sql = "INSERT INTO ".TB_PREF."trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, - included_in_price, net_amount, amount, memo, reg_type) + included_in_price, net_amount, amount, memo, reg_type, tax_group_id, vat_category) VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'" - .date2sql($tran_date)."',".db_escape($tax_id)."," - .db_escape($rate).",".db_escape($ex_rate).",".($included ? 1:0)."," + .date2sql($tran_date)."'," + .(int)($tax_id)."," + .(float)($rate)."," + .(float)($ex_rate).",".($included ? 1:0)."," .db_escape($net_amount)."," - .db_escape($amount).",".db_escape($memo).",".db_escape($reg_type, true).")"; + .db_escape($amount)."," + .db_escape($memo)."," + .db_escape($reg_type, true)."," + .db_escape($tax_group_id)."," + .db_escape($vat_category).")"; db_query($sql, "Cannot save trans tax details"); @@ -470,19 +483,17 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included function get_trans_tax_details($trans_type, $trans_no) { - $sql = "SELECT tax_details.*, - tax_type.name AS tax_type_name, - tax_details.rate AS effective_rate, - tax_type.rate AS rate - FROM ".TB_PREF."trans_tax_details tax_details, - ".TB_PREF."tax_types tax_type - WHERE - trans_type = ".db_escape($trans_type)." - AND trans_no = ".db_escape($trans_no)." - AND (net_amount != 0 OR amount != 0) - AND tax_type.id = tax_details.tax_type_id"; - - return db_query($sql, "The transaction tax details could not be retrieved"); + $sql = "SELECT tax_details.*, SUM(tax_details.amount) amount, SUM(tax_details.net_amount) as net_amount, + tax_type.name AS tax_type_name, tax_type.rate AS rate + FROM ".TB_PREF."trans_tax_details tax_details + LEFT JOIN ".TB_PREF."tax_types tax_type ON tax_type.id = tax_details.tax_type_id + WHERE + trans_type = ".db_escape($trans_type)." + AND trans_no = ".db_escape($trans_no)." + AND (net_amount != 0 OR amount != 0) + GROUP BY tax_type_id"; + + return db_query($sql, "The transaction tax details could not be retrieved"); } //---------------------------------------------------------------------------------------- @@ -513,30 +524,36 @@ function get_tax_summary($from, $to, $also_zero_purchases=false) $todate = date2sql($to); $sql = "SELECT - SUM(IF(trans_type=".ST_SUPPCREDIT.",-1,1)* + SUM(IF(trans_type=".ST_CUSTCREDIT.",-1,1)* IF((reg_type=".TR_OUTPUT.")" - ." || ((trans_type IN(".ST_SUPPINVOICE.",".ST_SUPPCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) - ), net_amount*ex_rate,0) + ." || ((trans_type IN(".ST_SALESINVOICE.",".ST_CUSTCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) + AND (tgroup.tax_area=".TA_EU." OR (tgroup.tax_area=".TA_EXPORT." AND taxrec.vat_category=".VC_SERVICES.") + OR taxrec.vat_category=".VC_REVERSE."))" + .($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate") + ."), net_amount*ex_rate,0) ) net_output, - SUM(IF(trans_type=".ST_SUPPCREDIT.",-1,1)* + SUM(IF(trans_type=".ST_CUSTCREDIT.",-1,1)* IF((reg_type=".TR_OUTPUT.")" - ." || ((trans_type IN(".ST_SUPPINVOICE.",".ST_SUPPCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) + ." || ((trans_type IN(".ST_SALESINVOICE.",".ST_CUSTCREDIT.") OR (trans_type=".ST_JOURNAL." AND reg_type=".TR_INPUT.")) + AND (tgroup.tax_area=".TA_EU." OR (tgroup.tax_area=".TA_EXPORT." AND taxrec.vat_category=".VC_SERVICES.") + OR taxrec.vat_category=".VC_REVERSE.") ), amount*ex_rate,0)) payable, SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)* IF(reg_type=".TR_INPUT . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate") - .", net_amount*ex_rate, 0)) net_input, + ." AND vat_category!=".VC_NONDEDUCT.", net_amount*ex_rate, 0)) net_input, SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)* IF(reg_type=".TR_INPUT . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate ") - .", amount*ex_rate, 0)) collectible, + ." AND vat_category!=".VC_NONDEDUCT.", amount*ex_rate, 0)) collectible, taxrec.rate, ttype.id, ttype.name FROM ".TB_PREF."trans_tax_details taxrec LEFT JOIN ".TB_PREF."tax_types ttype ON taxrec.tax_type_id=ttype.id + LEFT JOIN ".TB_PREF."tax_groups tgroup ON taxrec.tax_group_id=tgroup.id WHERE taxrec.trans_type IN (".implode(',', array(ST_SALESINVOICE, ST_CUSTCREDIT, ST_SUPPINVOICE, ST_SUPPCREDIT, ST_JOURNAL)).") AND taxrec.tran_date >= '$fromdate' @@ -591,70 +608,55 @@ function clear_gl_trans($type, $trans_id, $nested=false) } function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false, - $user_id=null, $contractor_id=null, $dimension=null) + $user_id=null) { - - $sql = "SELECT IFNULL(a.gl_seq,0) as gl_seq, - gl.tran_date, - gl.type as trans_type, - gl.type_no as trans_no, - IFNULL(MAX(supp.supp_name), MAX(cust.name)) as name, - IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference, - refs.reference," - .($dimension ? " -SUM(IF(dim.dimension in(gl.dimension_id,gl.dimension2_id), gl.amount, 0)) as amount,":" SUM(IF(gl.amount>0, gl.amount,0)) as amount,") - ."com.memo_, - IF(ISNULL(u.user_id),'',u.user_id) as user_id"; - - if ($contractor_id > 0) { - $sql.= ", st.supplier_id, dt.debtor_no "; - } - - $sql.= " 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) - LEFT JOIN ".TB_PREF."comments as com ON - (gl.type=com.type AND gl.type_no=com.id) - LEFT JOIN ".TB_PREF."refs as refs ON - (gl.type=refs.type AND gl.type_no=refs.id) - LEFT JOIN ".TB_PREF."users as u ON - a.user=u.id - LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no - LEFT JOIN ".TB_PREF."debtors_master cust ON gl.person_type_id=2 AND gl.person_id=cust.debtor_no - LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no - LEFT JOIN ".TB_PREF."suppliers supp ON gl.person_type_id=3 AND gl.person_id=supp.supplier_id" - .($dimension ? - " LEFT JOIN (SELECT type, type_no, MAX(IFNULL(dimension_id, dimension2_id)) dimension FROM ".TB_PREF."gl_trans GROUP BY type, type_no) dim - ON gl.type=dim.type AND gl.type_no=dim.type_no" : '') - ." WHERE gl.tran_date >= '" . date2sql($from) . "' - AND gl.tran_date <= '" . date2sql($to) . "' - AND gl.amount!=0"; - if ($ref) { - $sql .= " AND refs.reference LIKE ". db_escape("%$ref%"); - } - if ($memo) { - $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%"); - } - if ($filter != -1) { - $sql .= " AND gl.type=".db_escape($filter); - } - if (!$alsoclosed) { - $sql .= " AND gl_seq=0"; - } - else - $sql .= " AND NOT ISNULL(a.gl_seq)"; - - if ($user_id != null) - $sql .= " AND user_id = ".db_escape($user_id); - - if ($contractor_id > 0) { - $sql.= " AND (dt.debtor_no =".$contractor_id; - $sql.= " OR st.supplier_id =".$contractor_id.") "; - } - - if ($dimension != null) - $sql .= " AND dim.dimension = ".db_escape($dimension); - - $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no"; - - return $sql; + $sql = "SELECT IFNULL(a.gl_seq,0) as gl_seq, + gl.tran_date, + gl.type as trans_type, + gl.type_no as trans_no, + IFNULL(gl.person_id, IFNULL(st.supplier_id, IFNULL(grn.supplier_id, IFNULL(dt.debtor_no, bt.person_id)))) as person_id, + IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference, + refs.reference, + IF(gl.type=".ST_BANKTRANSFER.",MAX(gl.amount),SUM(IF(gl.amount>0, gl.amount,0))) as amount, + com.memo_, + IF(ISNULL(u.user_id),'',u.user_id) as user_id, + IF(gl.person_id, gl.person_type_id, IF(!ISNULL(st.supplier_id) OR !ISNULL(grn.supplier_id),". PT_SUPPLIER . "," . "IF(dt.debtor_no," . PT_CUSTOMER . "," . + "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_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) + LEFT JOIN ".TB_PREF."comments as com ON + (gl.type=com.type AND gl.type_no=com.id) + LEFT JOIN ".TB_PREF."refs as refs ON + (gl.type=refs.type AND gl.type_no=refs.id) + LEFT JOIN ".TB_PREF."users as u ON + a.user=u.id + LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." + LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0 + AND (bt.person_id != '' AND !ISNULL(bt.person_id)) + LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no + LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no + WHERE gl.tran_date >= '" . date2sql($from) . "' + AND gl.tran_date <= '" . date2sql($to) . "' + AND gl.amount!=0"; + if ($ref) { + $sql .= " AND refs.reference LIKE ". db_escape("%$ref%"); + } + if ($memo) { + $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%"); + } + if ($filter != -1) { + $sql .= " AND gl.type=".db_escape($filter); + } + if (!$alsoclosed) { + $sql .= " AND gl_seq=0"; + } + else + $sql .= " AND NOT ISNULL(a.gl_seq)"; + + if ($user_id != null) + $sql .= " AND user_id = ".db_escape($user_id); + + $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no"; + return $sql; }