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 bank_account_in_transactions($acc)
 106 {
 107         $sql= "SELECT COUNT(*) FROM ".TB_PREF."bank_trans WHERE bank_act=$acc";
 108         $result = db_query($sql,"check failed");
 109         $myrow = db_fetch_row($result);
 110         return ($myrow[0] > 0);
 111 }
 112
 113 //---------------------------------------------------------------------------------------------
 114
 115 function bank_account_in_sales_pos($acc)
 116 {
 117         $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_pos WHERE pos_account=$acc";
 118         $result = db_query($sql,"check failed");
 119         $myrow = db_fetch_row($result);
 120         return ($myrow[0] > 0);
 121 }
 122
 123 //---------------------------------------------------------------------------------------------
 124
 125 function add_quick_entry($description, $type, $base_amount, $base_desc)
 126 {
 127         $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc)
 128         VALUES (".db_escape($description).", ".db_escape($type).", "
 129                 .db_escape($base_amount).", ".db_escape($base_desc).")";
 130
 131         db_query($sql, "could not insert quick entry for $description");
 132 }
 133
 134 //---------------------------------------------------------------------------------------------
 135
 136 function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc)
 137 {
 138         $sql = "UPDATE ".TB_PREF."quick_entries SET description = ".db_escape($description).",
 139                 type=".db_escape($type).", base_amount=".db_escape($base_amount)
 140                 .", base_desc=".db_escape($base_desc)."
 141                 WHERE id = ".db_escape($selected_id);
 142
 143         db_query($sql, "could not update quick entry for $selected_id");
 144 }
 145
 146 //---------------------------------------------------------------------------------------------
 147
 148 function delete_quick_entry($selected_id)
 149 {
 150         $sql = "DELETE FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 151
 152         db_query($sql,"could not delete quick entry $selected_id");
 153 }
 154
 155 //---------------------------------------------------------------------------------------------
 156
 157 function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2)
 158 {
 159         $sql = "INSERT INTO ".TB_PREF."quick_entry_lines
 160                 (qid, action, dest_id, amount, dimension_id, dimension2_id)
 161         VALUES
 162                 ($qid, ".db_escape($action).",".db_escape($dest_id).",
 163                         ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).")";
 164
 165         db_query($sql, "could not insert quick entry line for $qid");
 166 }
 167
 168 //---------------------------------------------------------------------------------------------
 169
 170 function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2)
 171 {
 172         $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = ".db_escape($qid)
 173                 .", action=".db_escape($action).",
 174                 dest_id=".db_escape($dest_id).", amount=".db_escape($amount)
 175                 .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2)."
 176                 WHERE id = ".db_escape($selected_id);
 177
 178         db_query($sql, "could not update quick entry line for $selected_id");
 179 }
 180
 181 //---------------------------------------------------------------------------------------------
 182
 183 function delete_quick_entry_line($selected_id)
 184 {
 185         $sql = "DELETE FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);
 186
 187         db_query($sql,"could not delete quick entry line $selected_id");
 188 }
 189 //---------------------------------------------------------------------------------------------
 190
 191
 192 function has_quick_entries($type=null)
 193 {
 194         $sql = "SELECT id FROM ".TB_PREF."quick_entries";
 195         if ($type != null)
 196                 $sql .= " WHERE type=".db_escape($type);
 197
 198         $result = db_query($sql, "could not retreive quick entries");
 199         return db_num_rows($result) > 0;
 200 }
 201
 202 function get_quick_entries($type = null)
 203 {
 204         $sql = "SELECT * FROM ".TB_PREF."quick_entries";
 205         if ($type != null)
 206                 $sql .= " WHERE type=".db_escape($type);
 207         $sql .= " ORDER BY description";
 208
 209         return db_query($sql, "could not retreive quick entries");
 210 }
 211
 212 function get_quick_entry($selected_id)
 213 {
 214         $sql = "SELECT * FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id);
 215
 216         $result = db_query($sql, "could not retreive quick entry $selected_id");
 217
 218         return db_fetch($result);
 219 }
 220
 221 function get_quick_entry_lines($qid)
 222 {
 223         $sql = "SELECT ".TB_PREF."quick_entry_lines.*, ".TB_PREF."chart_master.account_name,
 224                         ".TB_PREF."tax_types.name as tax_name
 225                 FROM ".TB_PREF."quick_entry_lines
 226                 LEFT JOIN ".TB_PREF."chart_master ON
 227                         ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."chart_master.account_code
 228                 LEFT JOIN ".TB_PREF."tax_types ON
 229                         ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."tax_types.id
 230                 WHERE
 231                         qid=".db_escape($qid)." ORDER by id";
 232
 233         return db_query($sql, "could not retreive quick entries");
 234 }
 235
 236 function has_quick_entry_lines($qid)
 237 {
 238         $sql = "SELECT id FROM ".TB_PREF."quick_entry_lines WHERE qid=".db_escape($qid);
 239
 240         $result = db_query($sql, "could not retreive quick entries");
 241         return db_num_rows($result) > 0;
 242 }
 243
 244 //---------------------------------------------------------------------------------------------
 245
 246 function get_quick_entry_line($selected_id)
 247 {
 248         $sql = "SELECT * FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id);
 249
 250         $result = db_query($sql, "could not retreive quick entry for $selected_id");
 251
 252         return db_fetch($result);
 253 }
 254
 255 //---------------------------------------------------------------------------------------------
 256
 257 function get_max_reconciled($date, $bank_account)
 258 {
 259         $date = date2sql($date);
 260          // temporary fix to enable fix of invalid entries made in 2.2RC
 261         if ($date == 0) $date = '0000-00-00';
 262
 263         $sql = "SELECT MAX(reconciled) as last_date,
 264                          SUM(IF(reconciled<='$date', amount, 0)) as end_balance,
 265                          SUM(IF(reconciled<'$date', amount, 0)) as beg_balance,
 266                          SUM(amount) as total
 267                 FROM ".TB_PREF."bank_trans trans
 268                 WHERE bank_act=".db_escape($bank_account);
 269         //      ." AND trans.reconciled IS NOT NULL";
 270
 271         return db_query($sql,"Cannot retrieve reconciliation data");
 272
 273 }
 274
 275 //---------------------------------------------------------------------------------------------
 276
 277 function get_ending_reconciled($bank_account, $bank_date)
 278 {
 279         $sql = "SELECT ending_reconcile_balance
 280                 FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($bank_account)
 281                 . " AND last_reconciled_date=".db_escape($bank_date);
 282         $result = db_query($sql,"Cannot retrieve last reconciliation");
 283         return db_fetch($result);
 284 }
 285
 286 //---------------------------------------------------------------------------------------------
 287
 288 function get_sql_for_bank_account_reconcile($bank_account, $date)
 289 {
 290         $sql = "SELECT  type, trans_no, ref, trans_date,
 291                                 amount, person_id, person_type_id, reconciled, id
 292                 FROM ".TB_PREF."bank_trans
 293                 WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($bank_account) . "
 294                         AND (reconciled IS NULL OR reconciled='". date2sql($date) ."')
 295                 ORDER BY trans_date,".TB_PREF."bank_trans.id";
 296 // or   ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id";
 297         return $sql;
 298 }
 299
 300 //---------------------------------------------------------------------------------------------
 301
 302 function update_reconciled_values($reconcile_id, $reconcile_value, $reconcile_date, $end_balance, $bank_account)
 303 {
 304         $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value"
 305                 ." WHERE id=".db_escape($reconcile_id);
 306
 307         db_query($sql, "Can't change reconciliation status");
 308         // save last reconcilation status (date, end balance)
 309     $sql2 = "UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='"
 310                         .date2sql($reconcile_date)."',
 311             ending_reconcile_balance=$end_balance
 312                         WHERE id=".db_escape($bank_account);
 313
 314         db_query($sql2,"Error updating reconciliation information");
 315 }
 316 //---------------------------------------------------------------------------------------------
 317
 318 function get_default_bank_account($curr)
 319 {
 320         /* default bank account is selected as first found account from:
 321                 . default account in $curr if any
 322                 . first defined account in $curr if any
 323                 . default account in home currency
 324                 . first defined account in home currency
 325         */
 326         $home_curr = get_company_pref('curr_default');
 327
 328         $sql = "SELECT b.*, b.bank_curr_code='$home_curr' as fall_back FROM "
 329                 .TB_PREF."bank_accounts b"
 330                 ." WHERE b.bank_curr_code=".db_escape($curr)
 331                 ." OR b.bank_curr_code='$home_curr'
 332                 ORDER BY fall_back, dflt_curr_act desc";
 333
 334         $result = db_query($sql, "could not retreive default bank account");
 335
 336         return db_fetch($result);
 337 }
 338 ?>