The initial release will use the base authentication system already released for use with Nexista. I've modeled the directory layout off of the one used by the symfony development framework due to its great documentation and explanation, but that's all I used from symfony.
Data Model
Accounts- You cannot delete an account if there are transactions in it the account (enforced by InnoDB FK constraint).
- Account deletion cannot be undone.
- Account numbers correspond to the account type
- Accounts are cached to an xml file for faster processing.
- 1 Entry per transaction in the general ledger
- 2 or more entry amounts per journal entry, must balance to zero
(only two are currently supported in the application, but the database can support as many as needed) - Entry Amounts related to a journal entry are all deleted when the journal entry itself is deleted (as well as the related general ledger transaction).
Transactions
Transactions are what go in the ledger, they are single sided and they adjust the running balance for each account. A transaction can be created by importing account transactions, as well as posting the transactions from an existing journal entry. If you import a transaction, it is not yet related to any journal entries. You can create a journal entry from that transaction, and the two will then be related. After that, you will not be able to only delete the transaction, you'd have to delete the journal, which will then also delete the transaction which is related to it. When you create a journal entry from a single transaction, the journal entry form will be pre-populated with the transaction data (even so far as to limit your options based upon the criteria supplied by the transaction).
Posting a journal entry to the general ledger is easy. Each journal entry that has not yet been posted to the ledger will have a "+" sign next to it. Click that, and the entry will post.
To-Do
- Be able to recalculate balances all at once.
- Test balance recalculations
- i18n
Test in Internet Explorer- works- A check to see if related entries and transactions are in sync.
- On journal entry page - need to match credit and debit rows by more than the amount and account id, but what else is there? Need a unique id for the entry amounts table to fully support multiple credits or debits in a single journal entry.
- Reports:
- Cash flow - functional
- Profit and loss - needs work
- Balance sheet - functional
- Test test test test test, you get the idea. Especially test the posting of credit-side transactions to the ledger, it appears that some have an incorrect affect on the account balance. Make a similar balance to make sure the transactions have a balancing act themselves, depending on the normal balance of each account.
Support more than one account per debit or credit in one journal entry. For example: withdrawal from bank account for $967.23 for payroll. Need to credit bank account and debit staff wages AS WELL AS taxes. Really a must have for the initial release.DONE!!! Now need to document the limits of how this works.Fix paginationGood enough for now.- Get balance recalculation working.
Create journal entries from ledger transactions, populating form with transaction data for one side of the equation- DONEAutomatically create transactions in the general ledger whenever a journal entry is made.- CANCELLED
The code is almost ready for release. I reworked the data model a little bit and changed to InnoDB as a storage engine for MySQL (using another DB won't be difficult because of the simplicity of the model. Most of the generalization is complete: I've set the db table references as configurable variables, started to localize with i18n, and have done a lot of testing.
Directory Layout - ( a la symfony )
apps/
batch/
config/
cache/ build/ output/
data/
doc/
lib/
templates/
web/
Apps
pbooks/ config/ data/ sql/ xml/ templates/
URL Sitemap
Company Information
accounts/
journal/
ledger/
reports/
functions/
PBooks only supports one company per installation, per database set. It is recommended to set your web server root to the "web" directory of Pbooks. It is also recommended to only run one company per domain or sub-domain, though it is possible to run multiple companies using the same code base. It is possible for separate companies to share user tables, but each company must have separate tables for accounts, entries, entry amounts, and the general ledger.
The 4 core bookkeeping database tables default to the following names:
- pbaccounts
- pbentries
- pbentryamounts
- pbgeneralledger
However, each company must be stored in separate tables. The table names are currently set in pbooksprepend.php, stored as entities, and loaded / parsed with each query. As mentioned at the Nexista project home, we're now testing out pdo as a data access abstraction layer. (Its faster!) The table names are set in pbooksprepend.php so that they can be based upon the following factors: the user who logged in, the domain to which they logged into, and the path that the software is being accessed from. While not all these capabilities are currently available, they are design requirements for the following reasons:
- Some users must have access to multiple companies (accountants, professional bookkeepers, etc.)
- Account access should use SSL, and the cost and technical requirements (unique, static ips) of setting up SSL for voluminous numbers of domains is unrealistic
- General flexibility
NOTE ABOUT USER ACCESS TO MULTIPLE COMPANIES
For discussion, think about this in terms of blogs. One user may have access to several blogs (or books), even though the individual blogs (or books) use different tables. The user authentication tables must contain information about which tables (or blogs, or books) the user's have access to. This doesn't fit in with my understanding of how RBAC works, so I checked out how Wordpress does it. They have a usermeta table with the following keys:
tableprefixcapabilities
tableprefixuser_levelGood, bad, ugly??
This is really the goal of PBooks right now: to implement core accounting rules extremely well - nothing else at the moment:
- Entries must balance out to zero (double-entry accounting).
- Assets must equal liabilities plus equity (the accounting equation).
So how do we implement these rules? An ACID database is nice but can only do so much. These types of business rules must be coded with logic. Where do we put them? Not in the database model nor in the presentation layer, that would be too confining (though at some point they may be "present" at either of those places - for example, showing a user that an entry is out of balance).
At the same time, I don't believe in enforcing these rules so much that novice users can't tinker with a program, that is a useful training and teaching method. So the goal is to make the rules very efficient and effective, but flexible at the same time. Let's take these two rules one at a time:
Double-entry: How do I make a software component handle this business rule really well? I would make the component able to check, re-check, and cache whether a journal entry is in or out of balance. If an entry is in balance it should be more difficult to take it out of balance, and vice-versa: if its out of balance, let's make it easy to put it into balance. Should the data model include whether an account is in or out of balance? Maybe, that might speed things up at a small price.
Also, it would be good to prevent an out-of-balance entry from causing further chaos. For example, an out-of-balance entry should not be allowed to post to the ledger.
Accounting Equation: Hopefully the business rules from the double entry system would take care of any potential problems in the ledger. However, should they occur - like accounts not reconciling or the equation not balancing, the problem should be easy to find, and potentially fix. Journal - Where business transactions can be recorded or entered into the database manually. Two or more entries are required, at least one debit and at least one credit, and the sum of the debits must equal the sum of the credits. General journal page at wikipedia.Ledger - The ledger contains summarized transaction information, including transaction amount, dates, and memos. General ledger page at wikipedia.Entry - An entry is one of the base components which comprise the double-entry journal system. A journal entry has two "sides", a debit and a credit side, and the two must be equal. Transaction - A transaction is symbolic of the reallocation of value from one account to another for a business purpose.
References
http://www.netmba.com/accounting/fin/accounts/chart/
http://www.dwmbeancounter.com/tutorial/lesson05.html