Takaharu

Thought I'd share the project that I'm currently working on at home - an accounting application.
This came about because my mum can't find an accounting application that does everything that she wants properly and that also works with bank statements.
The progress has been a little slow as I've been doing it in my spare time when I'm in the mood to program and I tend to get distracted easily lol.

Anywho, when you start up the application you're presented with the following screen (well, actually, I've clicked the "all processed" button by this point):


This is just VB.net talking to an SQL database but the app will do all of the working out for you in a tidy GUI.
The processed buttons I'll come to later. The table that you see here actually only holds bare data: ID, date, description, type, account ID, VAT ID, supplier ID, invoice ID, business (bit), processed (bit), category ID and taxable (bit). In the app, however, I've done all of the workings out in the SQL. For example, rather than presenting a customer ID, it displays the customer's name using a different table for reference.
The "amount excluding VAT" is a calculated column.

When you double-click on any of the transactions it brings up the following dialog:

Here, the transaction is detailed in full, where the user can update the record. Validation galore here as there are quite a few things that need to be correct. For example, the amount can't be missing and must be numerical. If anything fails validation an appropriate message box is displayed. This took me friggin' ages to do as I had to get it to populate all of the drop-down boxes and display the value that is currently in the database. I got it wrong a few times.
For simplicity, I've used the invoice ID here instead of any other display and, to help, I've included a "browse" button. This brings up this bad boy:

The left dialog is what you're presented with after clicking the browse button. As you can see, all of the customers are displayed. When you double-click on one of the customers it displays a list of invoices associated with the selected customer. Clicking ok or double-clicking the chosen invoice will populate the transaction editor. Both of these dialogs will talk to each other so the invoice selector will populate its corresponding tranaction editor, regardless of how many are open.

The query builder was quite a chore but it didn't take as long as you might think:

As you can tell, each of the parameters to search by are optional. I also validated the crap out of it, checking to ensure that the fields are filled in if selected and filled in correctly.
"Category", in case you're wondering, is really just an additional filter. The user can add categories like "office supplies" or "bank transfers" so that they can categorise transactions and find them easier in the future.

Under the File menu you'll find... well, not that much of interest yet, but it's where the importer is currently "hidden":

Simply put, you download your bank statement from your bank (currently only supports Nationwide) in CSV format and browse to the file using this importer. It re-arranges the details in the CSV to the table displayed above for you to look over.
When you click import, it imports all of the transactions, checking for dupes in the database. If any dupes are found, a messagebox is displayed with the number of dupes found and successful transactions imported then they're listed in a separate dialog. From here, you choose which of the detected dupes you wish to keep and which ones you want to dispose of. When testing this out, I imported approximately 500 transactions, de-duped in under 2 seconds.
To process the transactions that are to be imported I used a stored procedure, which checks to see if the transaction already exists. If it doesn't, it puts it in the transactions table and marks it as unprocessed. If it does exist, it puts it in a separate table. If the table isn't empty when the user attempts to import additional transactions, it'll stop the user from importing any more and presents the dupes dialog instead.

One other thing to mention is that whenever the application would normally throw up an error message that I haven't accounted for, for example, if the syntax of an SQL query is incorrect, then the error message is sent to a text document (stored in My Documents), date & time stamped and the user is presented with a message box telling them that an error has occurred.

This application still has a long way to go. I still need to:

• Fix the next & back buttons on the main dialog. Because of the sheer quantity of transactions that can be displayed the application is liable to time out if there are several thousand transactions. Instead, I've added a next & back button so that only 50 transactions are displayed at a time.
• Add more options to the options dialog. Curently, there's only one option - the business flag.
• Make an invoice dialog - this will include a column that stores the path to a document
• Make options to relate transactions. This will probably be a context menu that will have options like "show all transactions relating to this customer"
• Make a VAT managing dialog. Add/remove VAT amounts, quite simply.
• Make a supplier manager. Name, contact details, etc
• Make a customer manager. Like the supplier manager, only for customers
• Experiment with the "total" column. I need to work out what totals it makes - whether it's the total of displayed amounts or whether it's a running total from the oldest transaction
• Add more totals. I'd imagine that the totals for the VAT, overall totals and maybe some other totals will be requested, like the totals for a specific time period
• Make a categories dialog. Luckily, that'll only be "name"
• Anything else that crops up.

So, what do you think?
This app isn't designed to be public, by the way.

__________________

skylineaddict

I think it's pretty damn awesome. I wish I knew a language other than Java because my dad wanted a program similar to this (Him being in Zambia means he can't use a lot of the business software that's out there right now).

__________________
Couldn't come up with anything snappy to say, so I'll just be awkward.

Takaharu

To be fair, it's more tedious than anything else. Once you know how to communicate to SQL using VB.net (in this instance) or C#.net and you know the basics of SQL (simple select queries, what a sub-query is and how to make a stored procedure) then the language is pretty much just a GUI.

Modesty aside, you'd also have to take into consideration what the end-user might do and throw in some work-arounds for things you over-think but being a programmer of any language, you'd need to take these things into consideration anyway.

I'd suggest looking into ASP.net if you're more of a web programmer - that'll quite happily do the job.

__________________

WxMan1

All of that can be done in Java. Java has its own database, i.e., DERBY.

In fact, if you establish the proper DB connection strings you can access a running Java DB via .NET.

That being said, the Java DB is absolutely necessary to be running prior to the .NET app hitting it (obviously you need the proper connection string).

Why is that good? You can implement a DB (if you need one) using Java RE w/out having to install SQL outright. OR you can deal w/intracacies and nuances of portable and relocatable MS SQL.