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)
  22 {
  23         if($dflt_curr_act)      // only one default account for any currency
  24           clear_dflt_curr_account($bank_curr_code);
  25
  26         $sql = "INSERT INTO ".TB_PREF."bank_accounts (account_code, account_type,
  27                 bank_account_name, bank_name, bank_account_number, bank_address,
  28                 bank_curr_code, dflt_curr_act)
  29                 VALUES (".db_escape($account_code).", ".db_escape($account_type).", "
  30                 .db_escape($bank_account_name).", ".db_escape($bank_name).", "
  31                 .db_escape($bank_account_number).",".db_escape($bank_address).
  32                 ", ".db_escape($bank_curr_code).", ".db_escape($dflt_curr_act).")";
  33
  34         db_query($sql, "could not add a bank account for $account_code");
  35 }
  36
  37 //---------------------------------------------------------------------------------------------
  38
  39 function update_bank_account($id, $account_code, $account_type, $bank_account_name,
  40         $bank_name, $bank_account_number, $bank_address, $bank_curr_code, $dflt_curr_act)
  41 {
  42         if($dflt_curr_act)      // only one default account for any currency
  43           clear_dflt_curr_account($bank_curr_code);
  44
  45         $sql = "UPDATE ".TB_PREF."bank_accounts SET account_type = ".db_escape($account_type).",
  46                 account_code=".db_escape($account_code).",
  47                 bank_account_name=".db_escape($bank_account_name).", bank_name=".db_escape($bank_name).",
  48                 bank_account_number=".db_escape($bank_account_number).", bank_curr_code=".db_escape($bank_curr_code).",
  49                 bank_address=".db_escape($bank_address).",
  50                 dflt_curr_act=".db_escape($dflt_curr_act)
  51                 ." WHERE id = ".db_escape($id);
  52
  53         db_query($sql, "could not update bank account for $account_code");
  54 }
  55
  56 //---------------------------------------------------------------------------------------------
  57
  58 function delete_bank_account($id)
  59 {
  60         $sql = "DELETE FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
  61
  62         db_query($sql,"could not delete bank account for $id");
  63 }
  64
  65
  66 //---------------------------------------------------------------------------------------------
  67
  68 function get_bank_account($id)
  69 {
  70         $sql = "SELECT * FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
  71
  72         $result = db_query($sql, "could not retreive bank account for $id");
  73
  74         return db_fetch($result);
  75 }
  76
  77 //---------------------------------------------------------------------------------------------
  78
  79 function get_bank_accounts($show_inactive)
  80 {
  81         $sql = "SELECT account.*, gl_account.account_name
  82                 FROM ".TB_PREF."bank_accounts account, ".TB_PREF."chart_master gl_account
  83                 WHERE account.account_code = gl_account.account_code";
  84         if (!$show_inactive) $sql .= " AND !account.inactive";
  85         $sql .= " ORDER BY account_code, bank_curr_code";
  86
  87         return db_query($sql,"could not get bank accounts");
  88 }
  89
  90 //---------------------------------------------------------------------------------------------
  91
  92 function get_bank_gl_account($id)
  93 {
  94         $sql = "SELECT account_code FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id);
  95
  96         $result = db_query($sql, "could not retreive bank account for $id");
  97
  98         $bank_account = db_fetch($result);
  99
 100         return $bank_account['account_code'];
 101 }
 102
 103 //---------------------------------------------------------------------------------------------
 104
 105 function add_quick_entry($description, $type, $base_amount, $base_desc, $bal_type)
 106 {
 107         $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc, bal_type)
 108         VALUES (".db_escape($description).", ".db_escape($type).", "
 109                 .db_escape($base_amount).", ".db_escape($base_desc).", ".db_escape($bal_type).")";
 110
 111         db_query($sql, "could not insert quick entry for $description");
 112 }
 113
 114 //---------------------------------------------------------------------------------------------
 115
 116 function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc, $bal_type)
 117 {
 118         $sql = "UPDATE ".TB_PREF."quick_entries SET description = ".db_escape($description).",
 119                 type=".db_escape($type).", base_amount=".db_escape($base_amount)
 120                 .", base_desc=".db_escape($base_desc).", bal_type=".db_escape($bal_type)."
 121                 WHERE id = ".db_escape($selected_id);
 122
 123         db_query($sql, "could not update quick entry for $selected_id");
 124 }
 125
 126 //---------------------------------------------------------------------------------------------
 127
 128 function delete_quick_entry($selected_id)
 129 {
 130         $sql = "DELETE FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 131
 132         db_query($sql,"could not delete quick entry $selected_id");
 133 }
 134
 135 //---------------------------------------------------------------------------------------------
 136
 137 function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2)
 138 {
 139         $sql = "INSERT INTO ".TB_PREF."quick_entry_lines
 140                 (qid, action, dest_id, amount, dimension_id, dimension2_id)
 141         VALUES
 142                 (".db_escape($qid).", ".db_escape($action).",".db_escape($dest_id).",
 143                         ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).")";
 144
 145         db_query($sql, "could not insert quick entry line for $qid");
 146 }
 147
 148 //---------------------------------------------------------------------------------------------
 149
 150 function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2)
 151 {
 152         $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = ".db_escape($qid)
 153                 .", action=".db_escape($action).",
 154                 dest_id=".db_escape($dest_id).", amount=".db_escape($amount)
 155                 .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2)."
 156                 WHERE id = ".db_escape($selected_id);
 157
 158         db_query($sql, "could not update quick entry line for $selected_id");
 159 }
 160
 161 //---------------------------------------------------------------------------------------------
 162
 163 function delete_quick_entry_line($selected_id)
 164 {
 165         $sql = "DELETE FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);
 166
 167         db_query($sql,"could not delete quick entry line $selected_id");
 168 }
 169 //---------------------------------------------------------------------------------------------
 170
 171
 172 function has_quick_entries($type=null)
 173 {
 174         $sql = "SELECT id FROM ".TB_PREF."quick_entries";
 175         if ($type != null)
 176                 $sql .= " WHERE type=".db_escape($type);
 177
 178         $result = db_query($sql, "could not retreive quick entries");
 179         return db_num_rows($result) > 0;
 180 }
 181
 182 function get_quick_entries($type = null)
 183 {
 184         $sql = "SELECT * FROM ".TB_PREF."quick_entries";
 185         if ($type != null)
 186                 $sql .= " WHERE type=".db_escape($type);
 187         $sql .= " ORDER BY description";
 188
 189         return db_query($sql, "could not retreive quick entries");
 190 }
 191
 192 function get_quick_entry($selected_id)
 193 {
 194         $sql = "SELECT * FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 195
 196         $result = db_query($sql, "could not retreive quick entry $selected_id");
 197
 198         return db_fetch($result);
 199 }
 200
 201 function get_quick_entry_lines($qid)
 202 {
 203         $sql = "SELECT ".TB_PREF."quick_entry_lines.*, ".TB_PREF."chart_master.account_name,
 204                         ".TB_PREF."tax_types.name as tax_name
 205                 FROM ".TB_PREF."quick_entry_lines
 206                 LEFT JOIN ".TB_PREF."chart_master ON
 207                         ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."chart_master.account_code
 208                 LEFT JOIN ".TB_PREF."tax_types ON
 209                         ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."tax_types.id
 210                 WHERE
 211                         qid=".db_escape($qid)." ORDER by id";
 212
 213         return db_query($sql, "could not retreive quick entries");
 214 }
 215
 216 function has_quick_entry_lines($qid)
 217 {
 218         $sql = "SELECT id FROM ".TB_PREF."quick_entry_lines WHERE qid=".db_escape($qid);
 219
 220         $result = db_query($sql, "could not retreive quick entries");
 221         return db_num_rows($result) > 0;
 222 }
 223
 224 //---------------------------------------------------------------------------------------------
 225
 226 function get_quick_entry_line($selected_id)
 227 {
 228         $sql = "SELECT * FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);
 229
 230         $result = db_query($sql, "could not retreive quick entry for $selected_id");
 231
 232         return db_fetch($result);
 233 }
 234
 235 //---------------------------------------------------------------------------------------------
 236
 237 function get_max_reconciled($date, $bank_account)
 238 {
 239         $date = date2sql($date);
 240          // temporary fix to enable fix of invalid entries made in 2.2RC
 241         if ($date == 0) $date = '0000-00-00';
 242
 243         $sql = "SELECT MAX(reconciled) as last_date,
 244                          SUM(IF(reconciled<='$date', amount, 0)) as end_balance,
 245                          SUM(IF(reconciled<'$date', amount, 0)) as beg_balance,
 246                          SUM(amount) as total
 247                 FROM ".TB_PREF."bank_trans trans
 248                 WHERE bank_act=".db_escape($bank_account);
 249         //      ." AND trans.reconciled IS NOT NULL";
 250
 251         return db_query($sql,"Cannot retrieve reconciliation data");
 252
 253 }
 254
 255 //---------------------------------------------------------------------------------------------
 256
 257 function get_ending_reconciled($bank_account, $bank_date)
 258 {
 259         $sql = "SELECT ending_reconcile_balance
 260                 FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($bank_account)
 261                 . " AND last_reconciled_date=".db_escape($bank_date);
 262         $result = db_query($sql,"Cannot retrieve last reconciliation");
 263         return db_fetch($result);
 264 }
 265
 266 //---------------------------------------------------------------------------------------------
 267
 268 function get_sql_for_bank_account_reconcile($bank_account, $date)
 269 {
 270         $sql = "SELECT  type, trans_no, ref, trans_date,
 271                                 amount, person_id, person_type_id, reconciled, id
 272                 FROM ".TB_PREF."bank_trans
 273                 WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . "
 274                         AND (reconciled IS NULL OR reconciled='". date2sql($date) ."')
 275                 ORDER BY trans_date,".TB_PREF."bank_trans.id";
 276 // or   ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
 277         return $sql;
 278 }
 279
 280 //---------------------------------------------------------------------------------------------
 281
 282 function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_date, $end_balance, $bank_account)
 283 {
 284         $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value"
 285                 ." WHERE id=".db_escape($reconcile_id);
 286
 287         db_query($sql, "Can't change reconciliation status");
 288         // save last reconcilation status (date, end balance)
 289     $sql2 = "UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='"
 290                         .date2sql($reconcile_date)."',
 291             ending_reconcile_balance=$end_balance
 292                         WHERE id=".db_escape($bank_account);
 293
 294         db_query($sql2,"Error updating reconciliation information");
 295 }
 296 //---------------------------------------------------------------------------------------------
 297
 298 function get_default_bank_account($curr)
 299 {
 300         /* default bank account is selected as first found account from:
 301                 . default account in $curr if any
 302                 . first defined account in $curr if any
 303                 . default account in home currency
 304                 . first defined account in home currency
 305         */
 306         $home_curr = get_company_pref('curr_default');
 307
 308         $sql = "SELECT b.*, b.bank_curr_code='$home_curr' as fall_back FROM "
 309                 .TB_PREF."bank_accounts b"
 310                 ." WHERE b.bank_curr_code=".db_escape($curr)
 311                 ." OR b.bank_curr_code='$home_curr'
 312                 ORDER BY fall_back, dflt_curr_act desc";
 313
 314         $result = db_query($sql, "could not retreive default bank account");
 315
 316         return db_fetch($result);
 317 }
 318
 319 function get_default_customer_bank_account($cust_id)
 320 {
 321         $sql = "SELECT curr_code FROM ".TB_PREF."debtors_master WHERE debtor_no=".db_escape($cust_id);
 322         $result = db_query($sql, "could not retreive default customer currency code");
 323         $row = db_fetch($result);
 324         $ba = get_default_bank_account($row['curr_code']);
 325         return $ba['id'];
 326 }
 327
 328 function get_default_supplier_bank_account($supplier_id)
 329 {
 330         $sql = "SELECT curr_code FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id);
 331         $result = db_query($sql, "could not retreive default supplier currency code");
 332         $row = db_fetch($result);
 333         $ba = get_default_bank_account($row['curr_code']);
 334         return $ba['id'];
 335 }
 336 //---------------------------------------------------------------------------------------------
 337 //
 338 //      This function returns current allowed bank payment limit, or null if limit is not set.
 339 //      As of FA 2.3 check is done only on cash account, for which limit is bank amount as of the date selected.
 340 //
 341 function get_bank_account_limit($account, $date, $user=null)
 342 {
 343 //      $user = current_user();
 344
 345         $bankacc = get_bank_account($account);
 346         if ($bankacc['account_type'] != BT_CASH)
 347                 return null;
 348         return max(0, get_balance_before_for_bank_account($account, add_days($date,1))); // in case of manco made before the check was implemented
 349 }
 350
 351 ?>