X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_trans.inc;h=e64169676bd0bee655a8e5c7dfd4c96af020f0b4;hb=380ae03e2df9602d07d156ae93b87cbe59b01b3a;hp=111d4e331a8a579f899cc6dac0e54d0fc19f5dca;hpb=8b3bc81bdb2a8fceca3007aff0c8a43467d6e5b7;p=fa-stable.git diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 111d4e33..e6416967 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -50,11 +50,13 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension $sql .= ") "; - $sql .= "VALUES ($type, $trans_id, '$date', - '$account', $dimension, $dimension2, ".db_escape($memo_).", $amount_in_home_currency"; + $sql .= "VALUES (".db_escape($type).", ".db_escape($trans_id).", '$date', + ".db_escape($account).", ".db_escape($dimension).", " + .db_escape($dimension2).", ".db_escape($memo_).", " + .db_escape($amount_in_home_currency); if ($person_type_id != null) - $sql .= ", $person_type_id, ". db_escape($person_id); + $sql .= ", ".db_escape($person_type_id).", ". db_escape($person_id); $sql .= ") "; @@ -95,31 +97,40 @@ 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) + $account=null, $dimension=0, $dimension2=0, $filter_type=null, + $amount_min=null, $amount_max=null) { $from = date2sql($from_date); $to = date2sql($to_date); - $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master + $sql = "SELECT ".TB_PREF."gl_trans.*, " + .TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, " + .TB_PREF."chart_master WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account AND tran_date >= '$from' AND tran_date <= '$to'"; if ($trans_no > 0) - $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE '%$trans_no'"; + $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE ".db_escape('%'.$trans_no); if ($account != null) - $sql .= " AND ".TB_PREF."gl_trans.account = '$account'"; + $sql .= " AND ".TB_PREF."gl_trans.account = ".db_escape($account); - if ($dimension > 0) - $sql .= " AND ".TB_PREF."gl_trans.dimension_id = $dimension"; + if ($dimension != 0) + $sql .= " AND ".TB_PREF."gl_trans.dimension_id = ".($dimension<0?0:db_escape($dimension)); - if ($dimension2 > 0) - $sql .= " AND ".TB_PREF."gl_trans.dimension2_id = $dimension2"; + if ($dimension2 != 0) + $sql .= " AND ".TB_PREF."gl_trans.dimension2_id = ".($dimension2<0?0:db_escape($dimension2)); if ($filter_type != null AND is_numeric($filter_type)) - $sql .= " AND ".TB_PREF."gl_trans.type= $filter_type"; + $sql .= " AND ".TB_PREF."gl_trans.type= ".db_escape($filter_type); + + if ($amount_min != null) + $sql .= " AND ABS(".TB_PREF."gl_trans.amount) >= ABS(".db_escape($amount_min).")"; + + if ($amount_max != null) + $sql .= " AND ABS(".TB_PREF."gl_trans.amount) <= ABS(".db_escape($amount_max).")"; - $sql .= " ORDER BY tran_date"; + $sql .= " ORDER BY tran_date, counter"; return db_query($sql, "The transactions for could not be retrieved"); } @@ -129,10 +140,13 @@ function get_gl_transactions($from_date, $to_date, $trans_no=0, function get_gl_trans($type, $trans_id) { - $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master - WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account - AND ".TB_PREF."gl_trans.type=$type AND ".TB_PREF."gl_trans.type_no=$trans_id"; - + $sql = "SELECT gl.*, cm.account_name, IF(ISNULL(refs.reference), '', refs.reference) AS reference FROM " + .TB_PREF."gl_trans as gl + LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code + LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id)" + ." WHERE gl.type= ".db_escape($type) + ." AND gl.type_no = ".db_escape($trans_id) + ." ORDER BY counter"; return db_query($sql, "The gl transactions could not be retrieved"); } @@ -140,12 +154,14 @@ function get_gl_trans($type, $trans_id) function get_gl_wo_cost_trans($trans_id, $person_id=-1) { - $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master + $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM " + .TB_PREF."gl_trans, ".TB_PREF."chart_master WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account - AND ".TB_PREF."gl_trans.type=".systypes::work_order()." AND ".TB_PREF."gl_trans.type_no=$trans_id - AND ".TB_PREF."gl_trans.person_type_id=".payment_person_types::WorkOrder(); + AND ".TB_PREF."gl_trans.type=".ST_WORKORDER + ." AND ".TB_PREF."gl_trans.type_no=".db_escape($trans_id)." + AND ".TB_PREF."gl_trans.person_type_id=".PT_WORKORDER; if ($person_id != -1) - $sql .= " AND ".TB_PREF."gl_trans.person_id=$person_id"; + $sql .= " AND ".TB_PREF."gl_trans.person_id=".db_escape($person_id); $sql .= " AND amount < 0"; return db_query($sql, "The gl transactions could not be retrieved"); @@ -162,10 +178,10 @@ function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $d $sql .= " AND tran_date > '$from'"; if ($to_date != "") $sql .= " AND tran_date < '$to'"; - if ($dimension > 0) - $sql .= " AND dimension_id = $dimension"; - if ($dimension2 > 0) - $sql .= " AND dimension2_id = $dimension2"; + 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, "The starting balance for account $account could not be calculated"); @@ -186,10 +202,10 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim $sql .= " AND tran_date >= '$from'"; if ($to_date != "") $sql .= " AND tran_date <= '$to'"; - if ($dimension > 0) - $sql .= " AND dimension_id = $dimension"; - if ($dimension2 > 0) - $sql .= " AND dimension2_id = $dimension2"; + 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 account $account could not be calculated"); @@ -200,17 +216,20 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim //---------------------------------------------------------------------------------------------------- function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true) { - $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance - FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,".TB_PREF."chart_types, ".TB_PREF."chart_class - WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id + $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, + SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance + FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master," + .TB_PREF."chart_types, ".TB_PREF."chart_class + WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND " + .TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND"; if ($account != null) - $sql .= " account='$account' AND"; - if ($dimension > 0) - $sql .= " dimension_id=$dimension AND"; - if ($dimension2 > 0) - $sql .= " dimension2_id=$dimension2 AND"; + $sql .= " account=".db_escape($account)." AND"; + if ($dimension != 0) + $sql .= " dimension_id = ".($dimension<0?0:db_escape($dimension))." AND"; + if ($dimension2 != 0) + $sql .= " dimension2_id = ".($dimension2<0?0:db_escape($dimension2))." AND"; $from_date = date2sql($from); if ($from_incl) $sql .= " tran_date >= '$from_date' AND"; @@ -236,15 +255,73 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $to = date2sql($to_date); $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans - WHERE account='$account' "; + WHERE account=".db_escape($account); if ($from_date != "") $sql .= " AND tran_date >= '$from' "; if ($to_date != "") $sql .= " AND tran_date <= '$to' "; - if ($dimension > 0) - $sql .= " AND dimension_id = $dimension"; - if ($dimension2 > 0) - $sql .= " AND dimension2_id = $dimension2"; + 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,"No budget accounts were returned"); + + $row = db_fetch_row($result); + return $row[0]; +} +//------------------------------------------------------------------------------------- + +function exists_gl_budget($date_, $account, $dimension, $dimension2) +{ + $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND tran_date='$date_' AND + dimension_id=".db_escape($dimension)." AND dimension2_id=".db_escape($dimension2); + $result = db_query($sql, "Cannot retreive a gl transaction"); + + return (db_num_rows($result) > 0); +} + +function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $amount) +{ + $date = date2sql($date_); + + if (exists_gl_budget($date, $account, $dimension, $dimension2)) + $sql = "UPDATE ".TB_PREF."budget_trans SET amount=".db_escape($amount) + ." WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; + else + $sql = "INSERT INTO ".TB_PREF."budget_trans (tran_date, + account, dimension_id, dimension2_id, amount, memo_) VALUES ('$date', + ".db_escape($account).", ".db_escape($dimension).", " + .db_escape($dimension2).", ".db_escape($amount).", '')"; + + db_query($sql, "The GL budget transaction could not be saved"); +} + +function delete_gl_budget_trans($date_, $account, $dimension, $dimension2) +{ + $date = date2sql($date_); + + $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; + db_query($sql, "The GL budget transaction could not be deleted"); +} + +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 ".TB_PREF."budget_trans + WHERE account=".db_escape($account) + ." AND tran_date >= '$from' AND tran_date <= '$to' + AND dimension_id = ".db_escape($dimension) + ." AND dimension2_id = ".db_escape($dimension2); $result = db_query($sql,"No budget accounts were returned"); $row = db_fetch_row($result); @@ -260,8 +337,8 @@ function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, if(!$tax_type) return; // $gl_code is not tax account $tax = get_tax_type($tax_type); - //if ($gl_code == $tax['sales_gl_code']) - // $amount = -$amount; + if ($gl_code == $tax['sales_gl_code']) + $amount = -$amount; // we have to restore net amount as we cannot know the base amount if ($tax['rate'] == 0) { // display_warning(_("You should not post gl transactions @@ -291,7 +368,7 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included included_in_price, net_amount, amount, memo) VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'" .date2sql($tran_date)."',".db_escape($tax_id)."," - .$rate.",".$ex_rate.",".($included ? 1:0)."," + .db_escape($rate).",".db_escape($ex_rate).",".($included ? 1:0)."," .db_escape($net_amount)."," .db_escape($amount).",".db_escape($memo).")"; @@ -302,10 +379,11 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included function get_trans_tax_details($trans_type, $trans_no) { - $sql = "SELECT ".TB_PREF."trans_tax_details.*, ".TB_PREF."tax_types.name AS tax_type_name + $sql = "SELECT ".TB_PREF."trans_tax_details.*, " + .TB_PREF."tax_types.name AS tax_type_name FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types - WHERE trans_type = $trans_type - AND trans_no = $trans_no + WHERE trans_type = ".db_escape($trans_type)." + AND trans_no = ".db_escape($trans_no)." AND (net_amount != 0 OR amount != 0) AND ".TB_PREF."tax_types.id = ".TB_PREF."trans_tax_details.tax_type_id"; @@ -317,33 +395,57 @@ function get_trans_tax_details($trans_type, $trans_no) function void_trans_tax_details($type, $type_no) { $sql = "UPDATE ".TB_PREF."trans_tax_details SET amount=0, net_amount=0 - WHERE trans_no=$type_no - AND trans_type=$type"; + WHERE trans_no=".db_escape($type_no) + ." AND trans_type=".db_escape($type); db_query($sql, "The transaction tax details could not be voided"); } +//---------------------------------------------------------------------------------------- + +function clear_trans_tax_details($type, $type_no) +{ + $sql = "DELETE FROM ".TB_PREF."trans_tax_details + WHERE trans_no=".db_escape($type_no) + ." AND trans_type=".db_escape($type); + + db_query($sql, "The transaction tax details could not be cleared"); +} + function get_tax_summary($from, $to) { $fromdate = date2sql($from); $todate = date2sql($to); $sql = "SELECT - SUM(IF(trans_type=11 || trans_type=20,-1,1)* - IF(trans_type=2 || trans_type=10 || trans_type=11, net_amount*ex_rate,0)) net_output, - SUM(IF(trans_type=11 || trans_type=20,-1,1)* - IF(trans_type=2 || trans_type=10 || trans_type=11, amount*ex_rate,0)) payable, - SUM(IF(trans_type=11 || trans_type=20,-1,1)* - IF(trans_type=2 || trans_type=10 || trans_type=11, 0, net_amount*ex_rate)) net_input, - SUM(IF(trans_type=11 || trans_type=20,-1,1)* - IF(trans_type=2 || trans_type=10 || trans_type=11, 0, amount*ex_rate)) collectible, + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE + ." || trans_type=".ST_JOURNAL.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", net_amount*ex_rate,0)) net_output, + + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE + ." || trans_type=".ST_JOURNAL.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", amount*ex_rate,0)) payable, + + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", 0, net_amount*ex_rate)) net_input, + + SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)* + IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE + ." || (trans_type=".ST_JOURNAL ." AND amount<0)" + ." || trans_type=".ST_CUSTCREDIT.", 0, amount*ex_rate)) collectible, taxrec.rate, ttype.id, ttype.name FROM ".TB_PREF."tax_types ttype, ".TB_PREF."trans_tax_details taxrec WHERE taxrec.tax_type_id=ttype.id - AND taxrec.trans_type != 13 + AND taxrec.trans_type != ".ST_CUSTDELIVERY." AND taxrec.tran_date >= '$fromdate' AND taxrec.tran_date <= '$todate' GROUP BY ttype.id"; @@ -354,23 +456,26 @@ function get_tax_summary($from, $to) //-------------------------------------------------------------------------------- // Write/update journal entries. // -function write_journal_entries(&$cart, $reverse) +function write_journal_entries(&$cart, $reverse, $use_transaction=true) { + global $Refs; + $date_ = $cart->tran_date; $ref = $cart->reference; $memo_ = $cart->memo_; - $new = $cart->order_id == 0; $trans_type = $cart->trans_type; - $trans_id = $new ? get_next_trans_no($trans_type) : $cart->order_id; + $new = $cart->order_id == 0; + + if ($new) + $cart->order_id = get_next_trans_no($trans_type); + + $trans_id = $cart->order_id; - begin_transaction(); + if ($use_transaction) + begin_transaction(); - if($new) { - $cart->order_id = $trans_id; - } else { - void_journal_trans($trans_type, $trans_id, true); - delete_comments($trans_type, $trans_id); - } + if(!$new) + void_journal_trans($trans_type, $trans_id, false); foreach ($cart->gl_items as $journal_item) { @@ -388,19 +493,19 @@ function write_journal_entries(&$cart, $reverse) } // store tax details if the gl account is a tax account add_gl_tax_details($journal_item->code_id, - ($journal_item->amount < 0.0 ? 2 : 1), $trans_id, -$journal_item->amount, 1, $date_, $memo_); + ST_JOURNAL, $trans_id, $journal_item->amount, 1, $date_, $memo_); } if ($new) { add_comments($trans_type, $trans_id, $date_, $memo_); - references::save($trans_type, $trans_id, $ref); - } else + $Refs->save($trans_type, $trans_id, $ref); + } else { update_comments($trans_type, $trans_id, null, $memo_); + $Refs->update($trans_type, $trans_id, $ref); + } add_audit_trail($trans_type, $trans_id, $date_); - commit_transaction(); - if ($reverse) { //$reversingDate = date(user_date_display(), @@ -425,15 +530,18 @@ function write_journal_entries(&$cart, $reverse) } // store tax details if the gl account is a tax account add_gl_tax_details($journal_item->code_id, - ($journal_item->amount < 0.0 ? 2 : 1), $trans_id, $journal_item->amount, 1, $date, $memo_); + ST_JOURNAL, $trans_id, $journal_item->amount, 1, $reversingDate, $memo_); } add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_); - references::save($trans_type, $trans_id_reverse, $ref); + $Refs->save($trans_type, $trans_id_reverse, $ref); add_audit_trail($trans_type, $trans_id_reverse, $reversingDate); } + if ($use_transaction) + commit_transaction(); + return $trans_id; } @@ -441,7 +549,8 @@ function write_journal_entries(&$cart, $reverse) function exists_gl_trans($type, $trans_id) { - $sql = "SELECT type_no FROM ".TB_PREF."gl_trans WHERE type=$type AND type_no=$trans_id"; + $sql = "SELECT type_no FROM ".TB_PREF."gl_trans WHERE type=".db_escape($type) + ." AND type_no=".db_escape($trans_id); $result = db_query($sql, "Cannot retreive a gl transaction"); return (db_num_rows($result) > 0); @@ -454,7 +563,8 @@ function void_gl_trans($type, $trans_id, $nested=false) if (!$nested) begin_transaction(); - $sql = "UPDATE ".TB_PREF."gl_trans SET amount=0 WHERE type=$type AND type_no=$trans_id"; + $sql = "UPDATE ".TB_PREF."gl_trans SET amount=0 WHERE type=".db_escape($type) + ." AND type_no=".db_escape($trans_id); db_query($sql, "could not void gl transactions for type=$type and trans_no=$trans_id"); @@ -462,17 +572,114 @@ function void_gl_trans($type, $trans_id, $nested=false) commit_transaction(); } +//-------------------------------------------------------------------------------------------------- + +function clear_gl_trans($type, $trans_id, $nested=false) +{ + if (!$nested) + begin_transaction(); + + $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE type=".db_escape($type) + ." AND type_no=".db_escape($trans_id); + + db_query($sql, "could not clear gl transactions for type=$type and trans_no=$trans_id"); + + if (!$nested) + commit_transaction(); +} + //---------------------------------------------------------------------------------------- -function void_journal_trans($type, $type_no) +function void_journal_trans($type, $type_no, $use_transaction=true) { - begin_transaction(); + if ($use_transaction) + begin_transaction(); void_bank_trans($type, $type_no, true); // void_gl_trans($type, $type_no, true); // this is done above // void_trans_tax_details($type, $type_no); // ditto - commit_transaction(); + if ($use_transaction) + commit_transaction(); } +function get_sql_for_journal_inquiry() +{ +/* + // Tom Hallman 11 Nov 2009 + // IF(gl.type = 1... statement is for deposits/payments that may not actually result + // in a deposit, such as when a fix is made. Without that statement (and the + // joining of the bank_trans table), the fix deposit/payment amount would show up + // incorrectly as only the positive side of the fix. + $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq, + gl.tran_date, + gl.type, + gl.type_no, + refs.reference, + IF(gl.type = 1 OR gl.type = 2, + bank_trans.amount, + SUM(IF(gl.amount>0, gl.amount,0))) as amount, + com.memo_, + IF(ISNULL(u.user_id),'',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) + 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."bank_trans as bank_trans ON + (gl.type=bank_trans.type AND gl.type_no=bank_trans.trans_no) + WHERE gl.tran_date >= '" . date2sql($_POST['FromDate']) . "' + AND gl.tran_date <= '" . date2sql($_POST['ToDate']) . "' + AND gl.amount!=0"; + if (isset($_POST['Ref']) && $_POST['Ref'] != "") { + $sql .= " AND reference LIKE '%". $_POST['Ref'] . "%'"; + } + if (get_post('filterType') != -1) { + $sql .= " AND gl.type=".get_post('filterType'); + } + if (!check_value('AlsoClosed')) { + $sql .= " AND gl_seq=0"; + } + $sql .= " GROUP BY gl.type, gl.type_no"; +*/ + + $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq, + gl.tran_date, + gl.type, + gl.type_no, + refs.reference, + SUM(IF(gl.amount>0, gl.amount,0)) as amount, + com.memo_, + IF(ISNULL(u.user_id),'',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) + 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 + WHERE gl.tran_date >= '" . date2sql($_POST['FromDate']) . "' + AND gl.tran_date <= '" . date2sql($_POST['ToDate']) . "' + AND gl.amount!=0"; + if (isset($_POST['Ref']) && $_POST['Ref'] != "") { + $sql .= " AND reference LIKE '%". $_POST['Ref'] . "%'"; + } + if (isset($_POST['Memo']) && $_POST['Memo'] != "") { + $sql .= " AND com.memo_ LIKE '%". $_POST['Memo'] . "%'"; + } + if (get_post('filterType') != -1) { + $sql .= " AND gl.type=".get_post('filterType'); + } + if (!check_value('AlsoClosed')) { + $sql .= " AND gl_seq=0"; + } + $sql .= " GROUP BY gl.type, gl.type_no"; + return $sql; +} ?> \ No newline at end of file