gl/includes/db/gl_db_bank_accounts.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 //---------------------------------------------------------------------------------------------
  13 function clear_dflt_curr_account($curr_code) {
  14           $sql = "UPDATE ".TB_PREF."bank_accounts SET dflt_curr_act=0 WHERE bank_curr_code="
  15           .db_escape($curr_code);
  16           db_query($sql, "could not update default currency account");
  17 }
  18
  19 function add_bank_account($account_code, $account_type, $bank_account_name,
  20         $bank_name, $bank_account_number, $bank_address, $bank_curr_code,
  21         $dflt_curr_act, $bank_charge_act)
  22 {
  23         begin_transaction(__FUNCTION__, func_get_args());
  24         if($dflt_curr_act)      // only one default account for any currency
  25           clear_dflt_curr_account($bank_curr_code);
  26
  27         $sql = "INSERT INTO ".TB_PREF."bank_accounts (account_code, account_type,
  28                 bank_account_name, bank_name, bank_account_number, bank_address,
  29                 bank_curr_code, dflt_curr_act, bank_charge_act)
  30                 VALUES (".db_escape($account_code).", ".db_escape($account_type).", "
  31                 .db_escape($bank_account_name).", ".db_escape($bank_name).", "
  32                 .db_escape($bank_account_number).",".db_escape($bank_address).
  33                 ", ".db_escape($bank_curr_code).", ".db_escape($dflt_curr_act).", ".db_escape($bank_charge_act).")";
  34
  35         db_query($sql, "could not add a bank account for $account_code");
  36         $result =  db_insert_id();
  37         commit_transaction();
  38         return $result;
  39 }
  40
  41 //---------------------------------------------------------------------------------------------
  42
  43 function update_bank_account($id, $account_code, $account_type, $bank_account_name,
  44         $bank_name, $bank_account_number, $bank_address, $bank_curr_code, $dflt_curr_act, $bank_charge_act)
  45 {
  46         begin_transaction(__FUNCTION__, func_get_args());
  47
  48         if($dflt_curr_act)      // only one default account for any currency
  49           clear_dflt_curr_account($bank_curr_code);
  50
  51         $sql = "UPDATE ".TB_PREF."bank_accounts SET account_type = ".db_escape($account_type).",
  52                 account_code=".db_escape($account_code).",
  53                 bank_account_name=".db_escape($bank_account_name).", bank_name=".db_escape($bank_name).",
  54                 bank_account_number=".db_escape($bank_account_number).", bank_curr_code=".db_escape($bank_curr_code).",
  55                 bank_address=".db_escape($bank_address).",
  56                 dflt_curr_act=".db_escape($dflt_curr_act).", bank_charge_act=".db_escape($bank_charge_act)."
  57                 WHERE id = ".db_escape($id);
  58
  59         db_query($sql, "could not update bank account for $account_code");
  60
  61         commit_transaction();
  62 }
  63
  64 //---------------------------------------------------------------------------------------------
  65
  66 function delete_bank_account($id)
  67 {
  68         begin_transaction(__FUNCTION__, func_get_args());
  69         $sql = "DELETE FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
  70
  71         db_query($sql,"could not delete bank account for $id");
  72         commit_transaction();
  73 }
  74
  75
  76 //---------------------------------------------------------------------------------------------
  77
  78 function get_bank_account($id)
  79 {
  80         $sql = "SELECT * FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
  81
  82         $result = db_query($sql, "could not retreive bank account for $id");
  83
  84         return db_fetch($result);
  85 }
  86
  87 //---------------------------------------------------------------------------------------------
  88
  89 function get_bank_accounts($show_inactive=false)
  90 {
  91         $sql = "SELECT account.*, gl_account.account_name
  92                 FROM ".TB_PREF."bank_accounts account, ".TB_PREF."chart_master gl_account
  93                 WHERE account.account_code = gl_account.account_code";
  94         if (!$show_inactive) $sql .= " AND !account.inactive";
  95         $sql .= " ORDER BY account_code, bank_curr_code";
  96
  97         return db_query($sql,"could not get bank accounts");
  98 }
  99
 100 //---------------------------------------------------------------------------------------------
 101
 102 function get_bank_gl_account($id)
 103 {
 104         $sql = "SELECT account_code FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
 105
 106         $result = db_query($sql, "could not retreive bank account for $id");
 107
 108         $bank_account = db_fetch($result);
 109
 110         return $bank_account['account_code'];
 111 }
 112
 113 //---------------------------------------------------------------------------------------------
 114
 115 function get_bank_charge_account($id)
 116 {
 117         $sql = "SELECT bank_charge_act FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
 118
 119         $result = db_query($sql, "could not retreive bank account for $id");
 120
 121         $bank_account = db_fetch($result);
 122
 123         return $bank_account['bank_charge_act'];
 124 }
 125
 126 //---------------------------------------------------------------------------------------------
 127
 128 function add_quick_entry($description, $type, $base_amount, $base_desc, $bal_type, $usage)
 129 {
 130         begin_transaction(__FUNCTION__, func_get_args());
 131         $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc, bal_type, `usage`)
 132         VALUES (".db_escape($description).", ".db_escape($type).", "
 133                 .db_escape($base_amount).", ".db_escape($base_desc).", ".db_escape($bal_type).", ".db_escape($usage).")";
 134
 135         db_query($sql, "could not insert quick entry for $description");
 136
 137         $result = db_insert_id();
 138         commit_transaction();
 139         return $result;
 140 }
 141
 142 //---------------------------------------------------------------------------------------------
 143
 144 function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc, $bal_type, $usage)
 145 {
 146         begin_transaction(__FUNCTION__, func_get_args());
 147         $sql = "UPDATE ".TB_PREF."quick_entries SET description = ".db_escape($description).",
 148                 type=".db_escape($type).", base_amount=".db_escape($base_amount)
 149                 .", base_desc=".db_escape($base_desc).", bal_type=".db_escape($bal_type).", `usage`=".db_escape($usage)."
 150                 WHERE id = ".db_escape($selected_id);
 151
 152         db_query($sql, "could not update quick entry for $selected_id");
 153         commit_transaction();
 154 }
 155
 156 //---------------------------------------------------------------------------------------------
 157
 158 function delete_quick_entry($selected_id)
 159 {
 160         begin_transaction(__FUNCTION__, func_get_args());
 161         $sql = "DELETE FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 162
 163         db_query($sql,"could not delete quick entry $selected_id");
 164         commit_transaction();
 165 }
 166
 167 //---------------------------------------------------------------------------------------------
 168
 169 function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2, $memo)
 170 {
 171         begin_transaction(__FUNCTION__, func_get_args());
 172
 173         $sql = "INSERT INTO ".TB_PREF."quick_entry_lines
 174                 (qid, action, dest_id, amount, dimension_id, dimension2_id, memo)
 175         VALUES
 176                 (".db_escape($qid).", ".db_escape($action).",".db_escape($dest_id).",
 177                         ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).", ".db_escape($memo).")";
 178
 179         db_query($sql, "could not insert quick entry line for $qid");
 180
 181         commit_transaction();
 182 }
 183
 184 //---------------------------------------------------------------------------------------------
 185
 186 function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2, $memo)
 187 {
 188         begin_transaction(__FUNCTION__, func_get_args());
 189
 190         $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = ".db_escape($qid)
 191                 .", action=".db_escape($action).",
 192                 dest_id=".db_escape($dest_id).", amount=".db_escape($amount)
 193                 .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2).", memo=".db_escape($memo)."
 194                 WHERE id = ".db_escape($selected_id);
 195
 196         db_query($sql, "could not update quick entry line for $selected_id");
 197
 198         commit_transaction();
 199 }
 200
 201 //---------------------------------------------------------------------------------------------
 202
 203 function delete_quick_entry_line($selected_id)
 204 {
 205         begin_transaction(__FUNCTION__, func_get_args());
 206
 207         $sql = "DELETE FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);
 208         db_query($sql,"could not delete quick entry line $selected_id");
 209
 210         commit_transaction();
 211 }
 212 //---------------------------------------------------------------------------------------------
 213
 214
 215 function has_quick_entries($type=null)
 216 {
 217         $sql = "SELECT id FROM ".TB_PREF."quick_entries";
 218         if ($type != null)
 219                 $sql .= " WHERE type=".db_escape($type);
 220
 221         $result = db_query($sql, "could not retreive quick entries");
 222         return db_num_rows($result) > 0;
 223 }
 224
 225 function get_quick_entries($type = null)
 226 {
 227         $sql = "SELECT * FROM ".TB_PREF."quick_entries";
 228         if ($type != null)
 229                 $sql .= " WHERE type=".db_escape($type);
 230         $sql .= " ORDER BY description";
 231
 232         return db_query($sql, "could not retreive quick entries");
 233 }
 234
 235 function get_quick_entry($selected_id)
 236 {
 237         $sql = "SELECT * FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 238
 239         $result = db_query($sql, "could not retreive quick entry $selected_id");
 240
 241         return db_fetch($result);
 242 }
 243
 244 function get_quick_entry_lines($qid)
 245 {
 246         $sql = "SELECT line.*, coa.account_name, taxtype.name as tax_name
 247                 FROM ".TB_PREF."quick_entry_lines line
 248                         LEFT JOIN ".TB_PREF."chart_master coa ON line.dest_id = coa.account_code
 249                         LEFT JOIN ".TB_PREF."tax_types taxtype ON line.dest_id = taxtype.id
 250                 WHERE
 251                         qid=".db_escape($qid)." ORDER by id";
 252
 253         return db_query($sql, "could not retreive quick entries");
 254 }
 255
 256 function has_quick_entry_lines($qid)
 257 {
 258         $sql = "SELECT id FROM ".TB_PREF."quick_entry_lines WHERE qid=".db_escape($qid);
 259
 260         $result = db_query($sql, "could not retreive quick entries");
 261         return db_num_rows($result) > 0;
 262 }
 263
 264 //---------------------------------------------------------------------------------------------
 265
 266 function get_quick_entry_line($selected_id)
 267 {
 268         $sql = "SELECT * FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);
 269
 270         $result = db_query($sql, "could not retreive quick entry for $selected_id");
 271
 272         return db_fetch($result);
 273 }
 274
 275 //---------------------------------------------------------------------------------------------
 276
 277 function get_max_reconciled($date, $bank_account)
 278 {
 279         $date = date2sql($date);
 280          // temporary fix to enable fix of invalid entries made in 2.2RC
 281         if ($date == 0) $date = '0000-00-00';
 282
 283         $sql = "SELECT MAX(reconciled) as last_date,
 284                          SUM(IF(reconciled<='$date', amount, 0)) as end_balance,
 285                          SUM(IF(reconciled<'$date', amount, 0)) as beg_balance,
 286                          SUM(amount) as total
 287                 FROM ".TB_PREF."bank_trans trans
 288                 WHERE bank_act=".db_escape($bank_account);
 289         //      ." AND trans.reconciled IS NOT NULL";
 290
 291         return db_query($sql,"Cannot retrieve reconciliation data");
 292
 293 }
 294
 295 //---------------------------------------------------------------------------------------------
 296
 297 function get_ending_reconciled($bank_account, $bank_date)
 298 {
 299         $sql = "SELECT ending_reconcile_balance
 300                 FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($bank_account)
 301                 . " AND last_reconciled_date=".db_escape($bank_date);
 302         $result = db_query($sql,"Cannot retrieve last reconciliation");
 303         return db_fetch($result);
 304 }
 305
 306 //---------------------------------------------------------------------------------------------
 307
 308 function get_sql_for_bank_account_reconcile($bank_account, $date)
 309 {
 310         $sql = "SELECT  bt.type, trans_no, ref, trans_date,
 311                                 amount, person_id, person_type_id, reconciled, bt.id, c.memo_
 312                 FROM ".TB_PREF."bank_trans bt
 313                 LEFT JOIN ".TB_PREF."comments c ON c.type = bt.type AND c.id = bt.trans_no
 314                 WHERE bank_act = ".db_escape($bank_account) . "
 315                         AND (reconciled IS NULL OR reconciled='". date2sql($date) ."')
 316                         AND amount != 0
 317                 ORDER BY trans_date, bt.id";
 318
 319         return $sql;
 320 }
 321
 322 //---------------------------------------------------------------------------------------------
 323
 324 function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_date, $end_balance, $bank_account)
 325 {
 326         begin_transaction(__FUNCTION__, func_get_args());
 327
 328         $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value"
 329                 ." WHERE id=".db_escape($reconcile_id);
 330
 331         db_query($sql, "Can't change reconciliation status");
 332         // save last reconcilation status (date, end balance)
 333     $sql2 = "UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='"
 334                         .date2sql($reconcile_date)."',
 335             ending_reconcile_balance=$end_balance
 336                         WHERE id=".db_escape($bank_account);
 337
 338         db_query($sql2,"Error updating reconciliation information");
 339
 340         commit_transaction();
 341 }
 342 //---------------------------------------------------------------------------------------------
 343
 344 function get_default_bank_account($curr=null)
 345 {
 346         /* default bank account is selected as first found account from:
 347                 . default account in $curr if any
 348                 . first defined account in $curr if any
 349                 . default account in home currency
 350                 . first defined account in home currency
 351         */
 352         $home_curr = get_company_pref('curr_default');
 353         if (!isset($curr))
 354                 $curr = $home_curr;
 355
 356         $sql = "SELECT b.*, b.bank_curr_code='$home_curr' as fall_back FROM "
 357                 .TB_PREF."bank_accounts b"
 358                 ." WHERE b.bank_curr_code=".db_escape($curr)
 359                 ." OR b.bank_curr_code='$home_curr'
 360                 ORDER BY fall_back, dflt_curr_act desc";
 361
 362         $result = db_query($sql, "could not retreive default bank account");
 363
 364         return db_fetch($result);
 365 }
 366
 367 function get_default_customer_bank_account($cust_id)
 368 {
 369         $sql = "SELECT curr_code FROM ".TB_PREF."debtors_master WHERE debtor_no=".db_escape($cust_id);
 370         $result = db_query($sql, "could not retreive default customer currency code");
 371         $row = db_fetch_row($result);
 372         $ba = get_default_bank_account($row[0]);
 373         return $ba['id'];
 374 }
 375
 376 function get_default_supplier_bank_account($supplier_id)
 377 {
 378         $sql = "SELECT curr_code FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id);
 379         $result = db_query($sql, "could not retreive default supplier currency code");
 380         $row = db_fetch_row($result);
 381         $ba = get_default_bank_account($row[0]);
 382         return $ba['id'];
 383 }
 384 //---------------------------------------------------------------------------------------------
 385 //
 386 //      This function returns current allowed bank payment limit, or null if limit is not set.
 387 //      As of FA 2.3 check is done only on cash account, for which limit is bank amount as of the date selected.
 388 //
 389 function get_bank_account_limit($account, $date, $user=null)
 390 {
 391 //      $user = current_user();
 392
 393         $bankacc = get_bank_account($account);
 394         if ($bankacc['account_type'] != BT_CASH)
 395                 return null;
 396         return max(0, get_balance_before_for_bank_account($account, add_days($date,1))); // in case of manco made before the check was implemented
 397 }
 398
 399 function identify_bank_account($account_number)
 400 {
 401         $number = db_escape($account_number);
 402         $sql = "(SELECT id AS id, ".BO_COMPANY." AS type FROM ".TB_PREF."bank_accounts WHERE REPLACE(bank_account_number,' ', '')=$number)";
 403         $sql .= " UNION (SELECT supplier_id AS id, ".BO_SUPPLIER." AS type FROM ".TB_PREF."suppliers WHERE REPLACE(bank_account,' ', '')=$number)";
 404         $sql .= " UNION (SELECT branch_code AS id, ".BO_CUSTBRANCH." AS type FROM ".TB_PREF."cust_branch WHERE REPLACE(bank_account,' ', '')=$number)";
 405
 406         $result = db_query($sql, 'bank account identification failed');
 407
 408         if (db_num_rows($result))
 409                 return db_fetch($result);
 410         else
 411                 return null;
 412 }
 413
 414 //----------------------------------------------------------------------------------
 415
 416 function get_bank_account_currency($id)
 417 {
 418         $sql= "SELECT bank_curr_code FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
 419         $result = db_query($sql, "retreive bank account currency");
 420
 421         $myrow = db_fetch_row($result);
 422         return $myrow[0];
 423 }
 424