X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=gl%2Fincludes%2Fdb%2Fgl_db_bank_trans.inc;h=37c173c0aea059b82d06a5c8adb9abb87e1c5387;hb=49d2702ed98abe7a564a4abb4c8c5fa225f362e8;hp=2c47f2badb4aa907b87a18ce1890f99c7bfc00c7;hpb=257a9e6800a4ba8bdfada30bf9eeb7ac47a73a12;p=fa-stable.git diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index 2c47f2ba..37c173c0 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -1,5 +1,14 @@ . +***********************************************************************/ //---------------------------------------------------------------------------------- // add a bank transaction @@ -7,7 +16,7 @@ // $date_ is display date (non-sql) function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, - $amount, $person_type_id, $person_id, $currency="", $err_msg="") + $amount, $person_type_id, $person_id, $currency="", $err_msg="", $rate=0) { $sqlDate = date2sql($date_); @@ -15,7 +24,10 @@ function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, if ($currency != "") { $bank_account_currency = get_bank_account_currency($bank_act); - $to_bank_currency = get_exchange_rate_from_to($currency, $bank_account_currency, $date_); + if ($rate == 0) + $to_bank_currency = get_exchange_rate_from_to($currency, $bank_account_currency, $date_); + else + $to_bank_currency = 1 / $rate; $amount_bank = ($amount / $to_bank_currency); } else @@ -29,7 +41,8 @@ function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, trans_date, amount, person_type_id, person_id) "; $sql .= "VALUES ($type, $trans_no, '$bank_act', ".db_escape($ref).", '$sqlDate', - $amount_bank, $person_type_id, ". db_escape($person_id).")"; + ".db_escape($amount_bank).", ".db_escape($person_type_id) + .", ". db_escape($person_id).")"; if ($err_msg == "") $err_msg = "The bank transaction could not be inserted"; @@ -41,8 +54,8 @@ function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, function exists_bank_trans($type, $type_no) { - $sql = "SELECT trans_no FROM ".TB_PREF."bank_trans WHERE type=$type - AND trans_no=$type_no"; + $sql = "SELECT trans_no FROM ".TB_PREF."bank_trans WHERE type=".db_escape($type) + ." AND trans_no=".db_escape($type_no); $result = db_query($sql, "Cannot retreive a bank transaction"); return (db_num_rows($result) > 0); @@ -56,13 +69,13 @@ function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id= FROM ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts WHERE ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act "; if ($type != null) - $sql .= " AND type=$type "; + $sql .= " AND type=".db_escape($type); if ($trans_no != null) - $sql .= " AND ".TB_PREF."bank_trans.trans_no = $trans_no "; + $sql .= " AND ".TB_PREF."bank_trans.trans_no = ".db_escape($trans_no); if ($person_type_id != null) - $sql .= " AND ".TB_PREF."bank_trans.person_type_id = $person_type_id "; + $sql .= " AND ".TB_PREF."bank_trans.person_type_id = ".db_escape($person_type_id); if ($person_id != null) - $sql .= " AND ".TB_PREF."bank_trans.person_id = '$person_id'"; + $sql .= " AND ".TB_PREF."bank_trans.person_id = ".db_escape($person_id); $sql .= " ORDER BY trans_date, ".TB_PREF."bank_trans.id"; return db_query($sql, "query for bank transaction"); @@ -70,9 +83,38 @@ function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id= //---------------------------------------------------------------------------------------- +function get_bank_trans_for_bank_account($bank_account, $from, $to) +{ + $from = date2sql($from); + $to = date2sql($to); + $sql = "SELECT ".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans + WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . " + AND trans_date >= '$from' + AND trans_date <= '$to' + ORDER BY trans_date,".TB_PREF."bank_trans.id"; + + return db_query($sql,"The transactions for '" . $bank_account . "' could not be retrieved"); +} + +//---------------------------------------------------------------------------------------- + +function get_balance_before_for_bank_account($bank_account, $from) +{ + $from = date2sql($from); + $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act=" + .db_escape($bank_account) . " + AND trans_date < '$from'"; + $before_qty = db_query($sql, "The starting balance on hand could not be calculated"); + $bfw_row = db_fetch_row($before_qty); + return $bfw_row[0]; +} +//---------------------------------------------------------------------------------------- + function get_gl_trans_value($account, $type, $trans_no) { - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE account='$account' AND type=$type AND type_no=$trans_no"; + $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE account=" + .db_escape($account)." AND type=".db_escape($type) + ." AND type_no=".db_escape($trans_no); $result = db_query($sql, "query for gl trans value"); @@ -88,7 +130,7 @@ function void_bank_trans($type, $type_no, $nested=false) begin_transaction(); $sql = "UPDATE ".TB_PREF."bank_trans SET amount=0 - WHERE type=$type AND trans_no=$type_no"; + WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); $result = db_query($sql, "could not void bank transactions for type=$type and trans_no=$type_no"); @@ -96,14 +138,14 @@ function void_bank_trans($type, $type_no, $nested=false) // in case it's a customer trans - probably better to check first void_cust_allocations($type, $type_no); - void_customer_trans_tax_details($type, $type_no); void_customer_trans($type, $type_no); // in case it's a supplier trans - probably better to check first void_supp_allocations($type, $type_no); - void_supp_invoice_tax_items($type, $type_no); void_supp_trans($type, $type_no); + void_trans_tax_details($type, $type_no); + if (!$nested) commit_transaction(); }