gl/bank_account_reconcile.php

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU Affero General Public License,
   5         AGPL, as published by the Free Software Foundation, either version
   6         3 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/agpl-3.0.html>.
  11 ***********************************************************************/
  12 /* Author Rob Mallon */
  13 $page_security = 8;
  14 $path_to_root="..";
  15 include_once($path_to_root . "/includes/session.inc");
  16
  17 include_once($path_to_root . "/includes/date_functions.inc");
  18 include_once($path_to_root . "/includes/ui.inc");
  19 include_once($path_to_root . "/includes/data_checks.inc");
  20
  21 include_once($path_to_root . "/gl/includes/gl_db.inc");
  22 include_once($path_to_root . "/includes/banking.inc");
  23
  24 //Added 1/9/09
  25 if (isset($_POST["command"]) && $_POST["command"]=="Reconcile") {
  26         //echo "Made it\n";
  27     $reconcile_id = strtok($_POST["reconcile_idtokens"], "|");
  28     while ($reconcile_id !== false) {
  29                 $formfield="reconcile".$reconcile_id;
  30                 if (!isset($_POST[$formfield]))
  31                         $_POST[$formfield] = 0;
  32                 $reconcile_value=$_POST[$formfield];
  33                 $sql="UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value WHERE id=$reconcile_id";
  34                 $result = db_query($sql,"Error setting reconcile flag on trans id $reconcile_id");
  35                 $reconcile_id = strtok("|");
  36     }
  37     $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='".date2sql($_POST["reconcile_date"])."',
  38         ending_reconcile_balance=".$_POST["reconcile_ending_balance"]." WHERE id=".$_POST["bank_account"];
  39         $result = db_query($sql2,"Error updating reconciliation information");
  40 }
  41
  42 $js = "";
  43 if ($use_popup_windows)
  44         $js .= get_js_open_window(800, 500);
  45 if ($use_date_picker)
  46         $js .= get_js_date_picker();
  47 page(_("Reconcile Bank Account"), false, false, "", $js);
  48
  49 ?>
  50 <script>
  51 function handleReconcileClick(form,reconcile_value,reconcile_amount) {
  52        var val1=form.reconcile_difference.value*1;
  53        val1=(Math.round(val1*100))/100;
  54        var val2=reconcile_amount*1;
  55        val2=(Math.round(val2*100))/100;
  56        var val4=form.reconcile_amount.value*1;
  57        val4=(Math.round(val4*100))/100;
  58 //Unchecking a line
  59        if (reconcile_value=='1') {
  60                 val3=(Math.round((val1-val2)*100))/100;
  61                form.reconcile_difference.value=val3;
  62                 val5=(Math.round((val4+val2)*100))/100;
  63                form.reconcile_amount.value=val5;
  64 //Checking a line
  65        } else {
  66                 val3=(Math.round((val1+val2)*100))/100;
  67                form.reconcile_difference.value=val3;
  68                 val5=(Math.round((val4-val2)*100))/100;
  69                form.reconcile_amount.value=val5;
  70        }
  71
  72 }
  73
  74 function handleReconcileDifference(form) {
  75         form.reconcile_difference.value=Number(form.reconcile_ending_balance.value)-Number(form.reconcile_beginning_balance.value);
  76 }
  77
  78 </script>
  79 <?php
  80
  81 check_db_has_bank_accounts(_("There are no bank accounts defined in the system."));
  82
  83 //-----------------------------------------------------------------------------------
  84 // Ajax updates
  85 //
  86 if (get_post('Show'))
  87 {
  88        $Ajax->activate('trans_tbl');
  89 }
  90 //------------------------------------------------------------------------------------------------
  91 //Added 1/9/09
  92 start_form();
  93 start_table("class='tablestyle_noborder'");
  94 start_row();
  95 bank_accounts_list_cells(_("Account:"), 'bank_account', null);
  96
  97 date_cells(_("From:"), 'TransAfterDate', '', null, -30);
  98 date_cells(_("To:"), 'TransToDate');
  99 //Added 1/8/09
 100 if (!isset($_POST['ShowReconciled']))
 101         $_POST['ShowReconciled'] = 0;
 102 $show_reconciled = $_POST['ShowReconciled'];
 103 yesno_list_cells(_("Show Reconciled:"), 'ShowReconciled');
 104
 105 submit_cells('Show',_("Show"),'','', false);
 106 end_row();
 107 end_table();
 108 end_form();
 109 //Added 1/9/09
 110 $reconcile_sql="SELECT last_reconciled_date,ending_reconcile_balance FROM ".TB_PREF."bank_accounts WHERE id=".$_POST['bank_account'];
 111 $reconcile_result = db_query($reconcile_sql,"Error");
 112 if ($reconcile_row = db_fetch($reconcile_result)) {
 113         echo "<hr>";
 114         start_table("class='tablestyle_noborder'");
 115         start_row();
 116         label_cell("<b>Last Reconciled Date: </b>");
 117         label_cell("<b>".sql2date($reconcile_row["last_reconciled_date"])."</b>");
 118         label_cell("&nbsp;&nbsp;&nbsp;&nbsp;");
 119         label_cell("<b>Last Reconciled Ending Balance: </b>");
 120         amount_cell($reconcile_row["ending_reconcile_balance"],true);
 121         end_row();
 122         end_table();
 123 } else {
 124     echo "Error: $reconcile_sql<br>";
 125 }
 126 start_form(false,false,"", 'reconcileform');
 127 start_table($table_style);
 128 $th = array(_("Reconcile Date"), _("Beginning<br>Balance"), _("Ending<br>Balance"), _("Reconciled<br>Amount"), _("Difference"));
 129 table_header($th);
 130 start_row();
 131 date_cells("", "reconcile_date");
 132 text_cells("", "reconcile_beginning_balance", $reconcile_row["ending_reconcile_balance"], 7, "", false, "", "", "onchange='handleReconcileDifference(this.form);'");
 133 text_cells("", "reconcile_ending_balance", 0,7, "", false, "", "", "onchange='handleReconcileDifference(this.form);'");
 134 text_cells("", "reconcile_amount", 0, 7, "", false, "", "", "READONLY");
 135 text_cells("", "reconcile_difference", 0, 7, "", false, "", "", "READONLY");
 136 end_row();
 137 end_table();
 138 echo "<hr>";
 139 //------------------------------------------------------------------------------------------------
 140
 141 $date_after = date2sql($_POST['TransAfterDate']);
 142 $date_to = date2sql($_POST['TransToDate']);
 143
 144 if (!isset($_POST['bank_account']))
 145     $_POST['bank_account'] = "";
 146
 147 //Modified 1/9/09
 148 $sql = "SELECT ".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans
 149         WHERE ".TB_PREF."bank_trans.bank_act = '" . $_POST['bank_account'] . "'
 150         AND trans_date >= '$date_after'
 151         AND trans_date <= '$date_to'";
 152 //Added line 1/9/09
 153 if ($show_reconciled=='0')
 154         $sql .= " AND ".TB_PREF."bank_trans.reconciled=0 ";
 155 $sql .= " ORDER BY trans_date,".TB_PREF."bank_trans.id";
 156
 157 $result = db_query($sql,"The transactions for '" . $_POST['bank_account'] . "' could not be retrieved");
 158
 159 div_start('trans_tbl');
 160 $act = get_bank_account($_POST["bank_account"]);
 161 display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);
 162
 163 //Added 1/9/09
 164
 165 hidden('command', 'Reconcile');
 166 hidden('Show', 'Show');
 167 hidden('bank_account', $_POST["bank_account"]);
 168 hidden('TransAfterDate',$_POST["TransAfterDate"]);
 169 hidden('TransToDate',$_POST["TransToDate"]);
 170 hidden('ShowReconciled',$_POST["ShowReconciled"]);
 171
 172 start_table($table_style);
 173
 174 $th = array(_("Type"), _("#"), _("Reference"), _("Date"),
 175        _("Debit"), _("Credit"), _("Person/Item"), "", "X");
 176 table_header($th);
 177 $idtokens="";
 178 $k = 0; //row colour counter
 179 while ($myrow = db_fetch($result))
 180 {
 181         $idtokens.=$myrow["id"]."|";
 182         alt_table_row_color($k);
 183
 184         $trandate = sql2date($myrow["trans_date"]);
 185         //Added 1/9/09
 186     $reconcile_check_name="reconcile".$myrow["id"];
 187     label_cell(systypes::name($myrow["type"]));
 188     label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"]));
 189     label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"],$myrow['ref']));
 190     label_cell($trandate);
 191     display_debit_or_credit_cells($myrow["amount"]);
 192     //amount_cell($running_total);
 193     label_cell(payment_person_types::person_name($myrow["person_type_id"],$myrow["person_id"]));
 194     label_cell(get_gl_view_str($myrow["type"], $myrow["trans_no"]));
 195         //Added 1/9/09 I needed the javascript action onclick, so I made my own checkbox
 196     echo "<td>";
 197     echo "<input type='checkbox' name='$reconcile_check_name' ";
 198     if ($myrow["reconciled"]==1) echo "CHECKED ";
 199     echo "value='1' ";
 200     echo "onclick=\"javascript:handleReconcileClick(this.form,this.checked,'".$myrow["amount"]."');\">";
 201     echo "</td>";
 202     //check_cells('', $reconcile_check_name, $myrow["reconciled"], false);
 203     end_row();
 204     //Removed by Rob Mallon on 1/8/09
 205     //if ($j == 12)
 206     //{
 207     //      $j = 1;
 208     //      table_header($th);
 209     //}
 210     //$j++;
 211 }
 212 //end of while loop
 213
 214 end_table(2);
 215 hidden('reconcile_idtokens',$idtokens);
 216 submit_center('Reconcile',_("Reconcile"),true,'', false);
 217
 218 div_end();
 219 end_form();
 220
 221 //------------------------------------------------------------------------------------------------
 222
 223 end_page();
 224
 225 ?>