Added missing field for bank charges in bank_trans, rewritten bank transaction views...

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

diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc
index 6eebecc0975645583272bfcf8299eae3d6ca725e..12238c4044472a40bb8aeda56394895143630d05 100644 (file)
--- a/gl/includes/db/gl_db_bank_accounts.inc
+++ b/gl/includes/db/gl_db_bank_accounts.inc
@@ -1,12 +1,12 @@
 <?php
 /**********************************************************************
     Copyright (C) FrontAccounting, LLC.
 <?php
 /**********************************************************************
     Copyright (C) FrontAccounting, LLC.

-       Released under the terms of the GNU General Public License, GPL, 
-       as published by the Free Software Foundation, either version 3 
+       Released under the terms of the GNU General Public License, GPL,
+       as published by the Free Software Foundation, either version 3

        of the License, or (at your option) any later version.
     This program is distributed in the hope that it will be useful,
     but WITHOUT ANY WARRANTY; without even the implied warranty of
        of the License, or (at your option) any later version.
     This program is distributed in the hope that it will be useful,
     but WITHOUT ANY WARRANTY; without even the implied warranty of

-    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
+    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
 //---------------------------------------------------------------------------------------------
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
 //---------------------------------------------------------------------------------------------


@@ -20,6 +20,7 @@ function add_bank_account($account_code, $account_type, $bank_account_name,
        $bank_name, $bank_account_number, $bank_address, $bank_curr_code, 
        $dflt_curr_act, $bank_charge_act)
 {
        $bank_name, $bank_account_number, $bank_address, $bank_curr_code, 
        $dflt_curr_act, $bank_charge_act)
 {

+       begin_transaction(__FUNCTION__, func_get_args());

        if($dflt_curr_act)      // only one default account for any currency
          clear_dflt_curr_account($bank_curr_code);
 
        if($dflt_curr_act)      // only one default account for any currency
          clear_dflt_curr_account($bank_curr_code);
 


@@ -32,6 +33,9 @@ function add_bank_account($account_code, $account_type, $bank_account_name,
                ", ".db_escape($bank_curr_code).", ".db_escape($dflt_curr_act).", ".db_escape($bank_charge_act).")";
 
        db_query($sql, "could not add a bank account for $account_code");
                ", ".db_escape($bank_curr_code).", ".db_escape($dflt_curr_act).", ".db_escape($bank_charge_act).")";
 
        db_query($sql, "could not add a bank account for $account_code");

+       $result =  db_insert_id();
+       commit_transaction();
+       return $result;

 }
 
 //---------------------------------------------------------------------------------------------
 }
 
 //---------------------------------------------------------------------------------------------


@@ -39,6 +43,8 @@ function add_bank_account($account_code, $account_type, $bank_account_name,
 function update_bank_account($id, $account_code, $account_type, $bank_account_name, 
        $bank_name, $bank_account_number, $bank_address, $bank_curr_code, $dflt_curr_act, $bank_charge_act)
 {
 function update_bank_account($id, $account_code, $account_type, $bank_account_name, 
        $bank_name, $bank_account_number, $bank_address, $bank_curr_code, $dflt_curr_act, $bank_charge_act)
 {

+       begin_transaction(__FUNCTION__, func_get_args());
+

        if($dflt_curr_act)      // only one default account for any currency
          clear_dflt_curr_account($bank_curr_code);
 
        if($dflt_curr_act)      // only one default account for any currency
          clear_dflt_curr_account($bank_curr_code);
 


@@ -51,15 +57,19 @@ function update_bank_account($id, $account_code, $account_type, $bank_account_na
                WHERE id = ".db_escape($id);
 
        db_query($sql, "could not update bank account for $account_code");
                WHERE id = ".db_escape($id);
 
        db_query($sql, "could not update bank account for $account_code");

+
+       commit_transaction();

 }
 
 //---------------------------------------------------------------------------------------------
 
 function delete_bank_account($id)
 {
 }
 
 //---------------------------------------------------------------------------------------------
 
 function delete_bank_account($id)
 {

+       begin_transaction(__FUNCTION__, func_get_args());

        $sql = "DELETE FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
 
        db_query($sql,"could not delete bank account for $id");
        $sql = "DELETE FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
 
        db_query($sql,"could not delete bank account for $id");

+       commit_transaction();

 }
 
 
 }
 
 


@@ -78,8 +88,8 @@ function get_bank_account($id)
 
 function get_bank_accounts($show_inactive=false)
 {
 
 function get_bank_accounts($show_inactive=false)
 {

-       $sql = "SELECT account.*, gl_account.account_name 
-               FROM ".TB_PREF."bank_accounts account, ".TB_PREF."chart_master gl_account 
+       $sql = "SELECT account.*, gl_account.account_name
+               FROM ".TB_PREF."bank_accounts account, ".TB_PREF."chart_master gl_account

                WHERE account.account_code = gl_account.account_code";
        if (!$show_inactive) $sql .= " AND !account.inactive";
        $sql .= " ORDER BY account_code, bank_curr_code";
                WHERE account.account_code = gl_account.account_code";
        if (!$show_inactive) $sql .= " AND !account.inactive";
        $sql .= " ORDER BY account_code, bank_curr_code";


@@ -115,69 +125,89 @@ function get_bank_charge_account($id)
 
 //---------------------------------------------------------------------------------------------
 
 
 //---------------------------------------------------------------------------------------------
 

-function add_quick_entry($description, $type, $base_amount, $base_desc, $bal_type)
+function add_quick_entry($description, $type, $base_amount, $base_desc, $bal_type, $usage)

 {
 {

-       $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc, bal_type) 
+       begin_transaction(__FUNCTION__, func_get_args());
+       $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc, bal_type, `usage`) 

        VALUES (".db_escape($description).", ".db_escape($type).", "
        VALUES (".db_escape($description).", ".db_escape($type).", "

-               .db_escape($base_amount).", ".db_escape($base_desc).", ".db_escape($bal_type).")";
+               .db_escape($base_amount).", ".db_escape($base_desc).", ".db_escape($bal_type).", ".db_escape($usage).")";

 
        db_query($sql, "could not insert quick entry for $description");
 
        db_query($sql, "could not insert quick entry for $description");

+
+       $result = db_insert_id();
+       commit_transaction();
+       return $result;

 }
 
 //---------------------------------------------------------------------------------------------
 
 }
 
 //---------------------------------------------------------------------------------------------
 

-function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc, $bal_type)
+function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc, $bal_type, $usage)

 {
 {

+       begin_transaction(__FUNCTION__, func_get_args());

        $sql = "UPDATE ".TB_PREF."quick_entries SET description = ".db_escape($description).",
                type=".db_escape($type).", base_amount=".db_escape($base_amount)
        $sql = "UPDATE ".TB_PREF."quick_entries SET description = ".db_escape($description).",
                type=".db_escape($type).", base_amount=".db_escape($base_amount)

-               .", base_desc=".db_escape($base_desc).", bal_type=".db_escape($bal_type)."
+               .", base_desc=".db_escape($base_desc).", bal_type=".db_escape($bal_type).", `usage`=".db_escape($usage)."

                WHERE id = ".db_escape($selected_id);
 
        db_query($sql, "could not update quick entry for $selected_id");
                WHERE id = ".db_escape($selected_id);
 
        db_query($sql, "could not update quick entry for $selected_id");

+       commit_transaction();

 }
 
 //---------------------------------------------------------------------------------------------
 
 function delete_quick_entry($selected_id)
 {
 }
 
 //---------------------------------------------------------------------------------------------
 
 function delete_quick_entry($selected_id)
 {

+       begin_transaction(__FUNCTION__, func_get_args());

        $sql = "DELETE FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 
        db_query($sql,"could not delete quick entry $selected_id");
        $sql = "DELETE FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 
        db_query($sql,"could not delete quick entry $selected_id");

+       commit_transaction();

 }
 
 //---------------------------------------------------------------------------------------------
 
 }
 
 //---------------------------------------------------------------------------------------------
 

-function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2)
+function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2, $memo)

 {
 {

+       begin_transaction(__FUNCTION__, func_get_args());
+

        $sql = "INSERT INTO ".TB_PREF."quick_entry_lines 
        $sql = "INSERT INTO ".TB_PREF."quick_entry_lines 

-               (qid, action, dest_id, amount, dimension_id, dimension2_id) 
+               (qid, action, dest_id, amount, dimension_id, dimension2_id, memo) 

        VALUES 
                (".db_escape($qid).", ".db_escape($action).",".db_escape($dest_id).",
        VALUES 
                (".db_escape($qid).", ".db_escape($action).",".db_escape($dest_id).",

-                       ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).")";
+                       ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).", ".db_escape($memo).")";

 
        db_query($sql, "could not insert quick entry line for $qid");
 
        db_query($sql, "could not insert quick entry line for $qid");

+
+       commit_transaction();

 }
 
 //---------------------------------------------------------------------------------------------
 
 }
 
 //---------------------------------------------------------------------------------------------
 

-function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2)
+function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2, $memo)

 {
 {

+       begin_transaction(__FUNCTION__, func_get_args());
+

        $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = ".db_escape($qid)
                .", action=".db_escape($action).",
                dest_id=".db_escape($dest_id).", amount=".db_escape($amount)
        $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = ".db_escape($qid)
                .", action=".db_escape($action).",
                dest_id=".db_escape($dest_id).", amount=".db_escape($amount)

-               .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2)." 
+               .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2).", memo=".db_escape($memo)."

                WHERE id = ".db_escape($selected_id);
 
        db_query($sql, "could not update quick entry line for $selected_id");
                WHERE id = ".db_escape($selected_id);
 
        db_query($sql, "could not update quick entry line for $selected_id");

+
+       commit_transaction();

 }
 
 //---------------------------------------------------------------------------------------------
 
 function delete_quick_entry_line($selected_id)
 {
 }
 
 //---------------------------------------------------------------------------------------------
 
 function delete_quick_entry_line($selected_id)
 {

-       $sql = "DELETE FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);
+       begin_transaction(__FUNCTION__, func_get_args());

 
 

+       $sql = "DELETE FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);

        db_query($sql,"could not delete quick entry line $selected_id");
        db_query($sql,"could not delete quick entry line $selected_id");

+
+       commit_transaction();

 }
 //---------------------------------------------------------------------------------------------
 
 }
 //---------------------------------------------------------------------------------------------
 


@@ -209,17 +239,14 @@ function get_quick_entry($selected_id)
        $result = db_query($sql, "could not retreive quick entry $selected_id");
 
        return db_fetch($result);
        $result = db_query($sql, "could not retreive quick entry $selected_id");
 
        return db_fetch($result);

-}      
+}

 
 function get_quick_entry_lines($qid)
 {
 
 function get_quick_entry_lines($qid)
 {

-       $sql = "SELECT ".TB_PREF."quick_entry_lines.*, ".TB_PREF."chart_master.account_name,
-                       ".TB_PREF."tax_types.name as tax_name
-               FROM ".TB_PREF."quick_entry_lines
-               LEFT JOIN ".TB_PREF."chart_master ON
-                       ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."chart_master.account_code
-               LEFT JOIN ".TB_PREF."tax_types ON
-                       ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."tax_types.id
+       $sql = "SELECT line.*, coa.account_name, taxtype.name as tax_name
+               FROM ".TB_PREF."quick_entry_lines line
+                       LEFT JOIN ".TB_PREF."chart_master coa ON line.dest_id = coa.account_code
+                       LEFT JOIN ".TB_PREF."tax_types taxtype ON line.dest_id = taxtype.id

                WHERE 
                        qid=".db_escape($qid)." ORDER by id";
 
                WHERE 
                        qid=".db_escape($qid)." ORDER by id";
 


@@ -280,13 +307,15 @@ function get_ending_reconciled($bank_account, $bank_date)
 
 function get_sql_for_bank_account_reconcile($bank_account, $date)
 {
 
 function get_sql_for_bank_account_reconcile($bank_account, $date)
 {

-       $sql = "SELECT  type, trans_no, ref, trans_date, 
-                               amount, person_id, person_type_id, reconciled, id
-               FROM ".TB_PREF."bank_trans
-               WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . "
+       $sql = "SELECT  bt.type, trans_no, ref, trans_date,
+                               amount, charge, person_id, person_type_id, reconciled, bt.id, c.memo_
+               FROM ".TB_PREF."bank_trans bt
+               LEFT JOIN ".TB_PREF."comments c ON c.type = bt.type AND c.id = bt.trans_no
+               WHERE bank_act = ".db_escape($bank_account) . "

                        AND (reconciled IS NULL OR reconciled='". date2sql($date) ."')
                        AND (reconciled IS NULL OR reconciled='". date2sql($date) ."')

-               ORDER BY trans_date,".TB_PREF."bank_trans.id";
-// or  ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
+                       AND amount != 0
+               ORDER BY trans_date, bt.id";
+

        return $sql;
 }
 
        return $sql;
 }
 


@@ -294,6 +323,8 @@ function get_sql_for_bank_account_reconcile($bank_account, $date)
 
 function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_date, $end_balance, $bank_account)
 {
 
 function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_date, $end_balance, $bank_account)
 {

+       begin_transaction(__FUNCTION__, func_get_args());
+

        $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value"
                ." WHERE id=".db_escape($reconcile_id);
 
        $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value"
                ." WHERE id=".db_escape($reconcile_id);
 


@@ -305,6 +336,8 @@ function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_da
                        WHERE id=".db_escape($bank_account);
 
        db_query($sql2,"Error updating reconciliation information");
                        WHERE id=".db_escape($bank_account);
 
        db_query($sql2,"Error updating reconciliation information");

+
+       commit_transaction();

 }
 //---------------------------------------------------------------------------------------------
 
 }
 //---------------------------------------------------------------------------------------------