Bank Account Reconciliation

Thats exactly right. Except I also plan on maintaining a reconciled date, and ending balance (which would become the beginning balance for the next reconciliation)

I've written a page like this before, from scratch, and in PHP, so the logic and complexity arent my issue. My issue is doing it within the constraints of your coding practices.

How bout this? Here's the code, maybe you can tell me why it doesn't retrieve the ShowReconciled value when I try to get it from the $_POST array:

<?php

$page_security = 8;
$path_to_root="..";
include_once($path_to_root . "/includes/session.inc");

include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/ui.inc");
include_once($path_to_root . "/includes/data_checks.inc");

include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/includes/banking.inc");

$js = "";
if ($use_popup_windows)
    $js .= get_js_open_window(800, 500);
if ($use_date_picker)
    $js .= get_js_date_picker();
page(_("Reconcile Bank Account"), false, false, "", $js);

check_db_has_bank_accounts(_("There are no bank accounts defined in the system."));

//-----------------------------------------------------------------------------------
// Ajax updates
//
if (get_post('Show'))
{
    $Ajax->activate('trans_tbl');
}
//------------------------------------------------------------------------------------------------


start_form();
start_table("class='tablestyle_noborder'");
start_row();
bank_accounts_list_cells(_("Account:"), 'bank_account', null);

date_cells(_("From:"), 'TransAfterDate', '', null, -30);
date_cells(_("To:"), 'TransToDate');
//Added 1/8/09
yesno_list_cells(_("Show Reconciled:"), 'ShowReconciled', 1);

submit_cells('Show',_("Show"),'','', true);
end_row();
end_table();
end_form();

//------------------------------------------------------------------------------------------------

//Added 1/8/09 DEBUG
$show_reconciled=get_post('ShowReconciled');
echo ">>>>>>>>".$show_reconciled."<<<<<<<<<<<";

$date_after = date2sql($_POST['TransAfterDate']);
$date_to = date2sql($_POST['TransToDate']);

if (!isset($_POST['bank_account']))
    $_POST['bank_account'] = "";
    $sql = "SELECT ".TB_PREF."bank_trans.*,name AS BankTransType FROM ".TB_PREF."bank_trans, ".TB_PREF."bank_trans_types
    WHERE ".TB_PREF."bank_trans.reconciled=0 and ".TB_PREF."bank_trans.bank_act = '" . $_POST['bank_account'] . "'
    AND trans_date >= '$date_after'
    AND trans_date <= '$date_to'
    AND ".TB_PREF."bank_trans_types.id = ".TB_PREF."bank_trans.bank_trans_type_id
    ORDER BY trans_date,".TB_PREF."bank_trans.id";
$result = db_query($sql,"The transactions for '" . $_POST['bank_account'] . "' could not be retrieved");

div_start('trans_tbl');
$act = get_bank_account($_POST["bank_account"]);
display_heading($act['bank_account_name']." - ".$act['bank_curr_code']);

start_table($table_style);

$th = array(_("Type"), _("#"), _("Reference"), _("Type"), _("Date"),
    _("Debit"), _("Credit"), _("Balance"), _("Person/Item"), "");
table_header($th);

$sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act='" . $_POST['bank_account'] . "'
    AND trans_date < '$date_after'";
$before_qty = db_query($sql, "The starting balance on hand could not be calculated");

start_row("class='inquirybg'");
label_cell("<b>"._("Opening Balance")." - ".$_POST['TransAfterDate']."</b>", "colspan=5");
$bfw_row = db_fetch_row($before_qty);
$bfw = $bfw_row[0];
display_debit_or_credit_cells($bfw);
label_cell("");

end_row();
$running_total = $bfw;
$j = 1;
$k = 0; //row colour counter
while ($myrow = db_fetch($result))
{

    alt_table_row_color($k);

    $running_total += $myrow["amount"];

    $trandate = sql2date($myrow["trans_date"]);
    label_cell(systypes::name($myrow["type"]));
    label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"]));
    label_cell(get_trans_view_str($myrow["type"],$myrow["trans_no"],$myrow['ref']));
    label_cell($myrow["BankTransType"]);
    label_cell($trandate);
    display_debit_or_credit_cells($myrow["amount"]);
    amount_cell($running_total);
    label_cell(payment_person_types::person_name($myrow["person_type_id"],$myrow["person_id"]));
    label_cell(get_gl_view_str($myrow["type"], $myrow["trans_no"]));
    end_row();
    //Removed by Rob Mallon on 1/8/09
    //if ($j == 12)
    //{
    //    $j = 1;
    //    table_header($th);
    //}
    //$j++;
}
//end of while loop

start_row("class='inquirybg'");
label_cell("<b>" . _("Ending Balance")." - ". $_POST['TransToDate']. "</b>", "colspan=5");
display_debit_or_credit_cells($running_total);
label_cell("");
end_row();
end_table(2);
div_end();
//------------------------------------------------------------------------------------------------

end_page();

?>

All I did so far was what I explained in my first post... I added a Yes/No select to the form, and tried to display it's value in a debugging echo statement.

I understand that $show_reconciled will have no value the first time the page is displayed, but I can't understand why $show_reconciled has no value when the 'Show' button is used to submit the form.

(In case you missed it in my first post, I am using /gl/inquiry/bank_inquiry.php as my base page. With the exception of the changes I mentioned, this page should look very familiar to you

Thanks for your quick response. I am the business owner as well as a PHP developer. I am EXTREMELY interested in using this program to run my business, but the lack of bank account reconciliation is a deal breaker. I figured adding it myself would be good for me, as well as the entire FrontAccounting community.

Thanks. I will try that when I get home.

I am using this software to replace quickbooks. I have gone from one location to 2, and by May I will have 5 locations, so quickbooks was no longer able to suite my needs.

I dont really know how to answer your question... Why wouldnt everyone want to be able to reconcile their bank account? My bank account gl account matches my bank statement line-for-line. I reconcile my account once a week at least.

When I get the application finished I will let you know. I would be glad to share it if you'd like to use it.

Thanks for your quick responses. As a programmer and a business owner, its nice to know there's support out there!

I have technicians who work for me. They collect customer checks throughout the week, and enter the invoices into the system. All customer payments are put in a 'Bank Deposit Holding' bank account.

At the end of the week the checks are sent to me and I make a deposit, and a transfer entry from 'Bank Deposit Holding' to 'Checking Account'. This way my deposit entry matches my actual bank deposit.

In addition, some goods are purchased for resale locally, some online, some phone orders using a debit card. The same is true for other expenses.

This makes for lots of transactions, many happening before they are entered into the system, and inevitably some are missed. Bank account reconciliation ensures that if there are debits to my bank account that I am unaware of I can go back and make the correct entries in the system so that my books match my bank's records. Is this that uncommon?! I guess justifying this feature seems strange to me. But I've always done business this way, so it's all I know.

It's not going to hurt anything by being there... and from what I've been reading online, the lack of bank account reconciliation is one of the reasons why people don't adopt this software. I'm sure you've read this article, but I'll post it here in case:

http://www.nmqb.co.uk/open-source-billing-applications/

You guys come out on top, but your lack of reconciliation was one of two reasons it wasn't chosen... 

Your software is free to use, but I make money using it, and I love to program. If I can contribute to your success by offering this enhancement, it would be my pleasure. It's the least I can do.

Rob

I'm done with what I set out to accomplish. It completely suites my needs but for it to be a fully featured utility it should have a little more flexibility.

As the program works now, there is no method for postponing a reconciliation. This can be frustrating if you begin the process, and then need to go back and make entries to correct balance errors. Currently, this would mean that you need to abandon your current reconciliation, make your entries, and then start the reconciliation over.

For me, this is usually not a big deal, since i reconcile every week or so (it's addicting... sometimes I do it every day. Anyone in my situation knows what I mean.)

That being said, it works great for me, it's very simple to use. So how do I get this to you so you?

To install, it requires the following PHP changes:
the menu entry in /applications/generalledger.php
the new page: /gl/bank_account_reconcile.php

and the following MySQL changes:
the column reconciled int(1) at the end of the bank_trans table
the columns last_reconciled_date timestamp,beginning_reconcile_balance double, and ending_reconcile_balance double at the end of the bank_accounts table

A few notes... for the most part, the code is clean, and I tried to adhere to your coding practices, use your ui functions, etc..., but because it uses javascript for tracking running totals as the user clicks the checkboxes, I needed to create my own checkbox element (not the check_cells function). Also the javascript is a little sloppy in places.. I was having trouble with string to number conversion, rounding, etc... and the final result was the best I could get it. It's right, but occasionally it displays more than 2 decimal places and I cant seem to make that stop (ie 250.35000 instead of 250.35). Honestly, once the numbers were calculating right, I just left it alone. With as touchy as javascript is, I didnt even want to mess with cleaning it up.

Feel free to clean it up, or enhance it as you see fit. I am excited to share this with you.
Rob

Thank you very much Rob, I will start immediately fixing it into the core source at 2.1 unstable.

/Joe

Yes, I detected that. The beginning and ending balance was not updated into the table bank_accounts.
The file you copied from was from release 2.0.6, therefore I had to change the file a bit. I also eliminated some warnings. But it doesn't matter. This is only minor things, so please resend the file as you have it and I will extract the changes into the 2.1 file.
I think this is nice work, Rob.

/Joe

This was great work and I spent most of my day in this module yesterday entering 4 months data for my country. The bank rec worked flawlessly.

Another reason we need a bank reconcliation is that if we take our trial balance to an external accountant to prepare our books and tax returns at year end is that he will want to see a bank reconciliation report to demonstrate that all of our data is included.

So what about a Bank Reconciliation Summary Report to take to him?
I hope I get this right - Something like:

1. Bank statement closing balance: $xxx

Add: Unpresented Payments:
Payment 1 $xxx
Payment 2 $xxx
...
Payment n $xxx
2. Total Unpresented Payments   $xxx

Less: Unpresented Deposits:
Deposit 1 $xxx
Deposit 2 $xxx
...
Deposit n $xxx
3. Total Unpresented Deposits $xxx

4. Calculated Reconciled Balance (1+2-3) $yyy
5. Cashbook Computer Balance $yyy

6. Difference (4-5) $0.00

I wish I knew how to program in php or I would have a go at this. I guess you would need to select the bank account and reconciliation date (from the drop down in the existing bank rec) befre printing the report.

It is nice to be able to attach this report to each bank statement and your accountant will get really excited and start recommending FA to his clients!