Changed Report Statements to accept selector Outstanding Only

[fa-stable.git] / gl / includes / db / gl_db_trans.inc

diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc
index 45d8eb085a788d0a44c494e8472d47915195e131..e64169676bd0bee655a8e5c7dfd4c96af020f0b4 100644 (file)
--- a/gl/includes/db/gl_db_trans.inc
+++ b/gl/includes/db/gl_db_trans.inc
@@ -337,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  
@@ -401,20 +401,44 @@ function void_trans_tax_details($type, $type_no)
        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=".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_CUSTCREDIT.", net_amount*ex_rate,0)) net_output,
-                               SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)*
-                               IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE." || 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_CUSTCREDIT.", 0, net_amount*ex_rate)) net_input,
+                               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_CUSTCREDIT.", 0, amount*ex_rate)) collectible,
+                               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
@@ -469,7 +493,7 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true)
        }
                // store tax details if the gl account is a tax account
                add_gl_tax_details($journal_item->code_id, 
-                       ($journal_item->amount < 0.0 ? ST_BANKDEPOSIT : ST_BANKPAYMENT), $trans_id, -$journal_item->amount, 1, $date_, $memo_);
+                       ST_JOURNAL, $trans_id, $journal_item->amount, 1, $date_, $memo_);
        }
        
        if ($new) {
@@ -506,7 +530,7 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true)
                }
                        // store tax details if the gl account is a tax account
                        add_gl_tax_details($journal_item->code_id, 
-                               ($journal_item->amount < 0.0 ? ST_BANKDEPOSIT : ST_BANKPAYMENT), $trans_id, $journal_item->amount, 1, $reversingDate, $memo_);
+                               ST_JOURNAL, $trans_id, $journal_item->amount, 1, $reversingDate, $memo_);
        }
 
        add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_);
@@ -548,6 +572,22 @@ 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, $use_transaction=true)
@@ -606,6 +646,7 @@ function get_sql_for_journal_inquiry()
        }
        $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,
@@ -629,6 +670,9 @@ function get_sql_for_journal_inquiry()
        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');
        }