Topics & Developments related to accounting in Tiki:
Marc Laporte asked Lindon in mid April 2010 to look into payment features in Tiki to manage accounting of the Tiki Association ๐
Here is an example of how to add entries and have basic calculations
http://profiles.tiki.org/Time_Sheet
Thinking there should be one main page with tabs across the top for the main areas. Probably the following tabs/areas:
Users should be able to:
User will need to be able to set:
Current plan is to base this feature on trackers, permissions and categories as described below:
Two trackers would be automatically set up when the accounting feature is enabled: Transactions and Counterparties. Transactions is the main tracker with Counterparties used to provide a dropdown list of counterparties for the counterparty field in the Transcations tracker (so the Counterparty tracker is not absolutely necessary). The Transactions tracker would have the following fields:
Field | Type | Comments |
---|---|---|
Id | auto-increment | auto transaction number |
Entry | numeric | for double-entry accounting so that 2 or more tracker items that are part of the same entry would have the same entry number |
Date | jscalendar | date of transaction |
Counterparty | item link | |
Amount | currency amount | enter either a positive or negative number. Don't use debit/credit terminology. |
Account | category | |
Description | text field | |
Long description | textarea | not absolutely necessary |
Permissions would be set for each field with one group (Acctng-view) with rights to see the items and the other (Acctng-edit) with editing rights.
The account structure would be set through categories. The first two levels would be preset as follows:
Top Level | Level 1 | Level 2, etc. |
---|---|---|
Accounting | Assets | Asset accounts defined by user |
Liabilities | Liability accounts defined by user | |
Revenues | Revenue accounts defined by user | |
Expenses | Expense accounts defined by user | |
Equity | Equity accounts defined by user |
Some of the next tasks would be:
Introduced in Tiki5, see doc: Payment
Marc Laporte: We have Tiki payment which lets you handle membership and the shopping cart. But it's not really accounting. I think we could do book keeping with trackers though
cc-take2 project aims to:
In order to allow payments with other sources than PayPal (the only one implemented in Tiki5, afaik, so far), we have to decide whether to:
http://mods.tiki.org/details.php?type=features&mod=tinvoice
cdrwhite set up a simple accounting system for the Computer-Club Siebengebirge e.V. in 2002. When the intranet was converted from the old code to tiki 1.x, the accounting was integrated but never actually committed to trunk for a couple of reasons:
Currently this system is being integrated into tiki.
So far, the following functionality already exists:
The following features need polishing:
The following features are missing:
A working alpha version has been committed to trunk (r30948), the missing parts will be appended bit by bit.
CREATE TABLE `tiki_acct_book` ( `bookId` int(10) unsigned NOT NULL AUTO_INCREMENT, `bookName` varchar(255) NOT NULL, `bookClosed` enum('y','n') NOT NULL DEFAULT 'n', `bookStartDate` date NOT NULL, `bookEndDate` date NOT NULL, `bookCurrency` varchar(3) NOT NULL DEFAULT 'EUR', `bookCurrencyPos` int(11) NOT NULL, `bookDecimals` int(11) NOT NULL DEFAULT '2', `bookDecPoint` varchar(1) NOT NULL DEFAULT ',', `bookThousand` varchar(1) NOT NULL DEFAULT '.', `exportSeparator` varchar(4) NOT NULL DEFAULT ';', `exportEOL` varchar(4) NOT NULL DEFAULT 'LF', `exportQuote` varchar(4) NOT NULL DEFAULT '"', `bookAutoTax` enum('y','n') NOT NULL DEFAULT 'y', PRIMARY KEY (`bookId`) );
This table contains the core definition of a book, its name, start and end date (only booking beetween those dates should be allowed, currently not enforced). When the book is marked as closed, transactions should no longer be possible but viewing the data and generating reports or exporting data should still be allowed.
Also some display settings which are specific for each book are set here: the currency and the position of the currency symbol, the number of decimals and the decimal point an thousands separator.
The export settings are the defaults for all CSV exports: The separator between fields, the EndOfLine (CR,LF or CRLF) and the Quote for text fields.
The last option is a flag which defines if automated tax deduction should be enabled (For example, if a client pays me 119 EUR to my bank Account, I have to book the bank account on debit side with 119 EUR and split credit into 100 EUR for the "profits" account and 19 EUR for the "VAT received" account. This will always be the same for the normal profits account, so the splitting can be done automagically.
CREATE TABLE `tiki_acct_account` ( `accountBookId` int(10) unsigned NOT NULL, `accountId` int(10) unsigned NOT NULL DEFAULT '0', `accountName` varchar(255) NOT NULL, `accountNotes` text NOT NULL, `accountBudget` double NOT NULL DEFAULT '0', `accountLocked` int(1) NOT NULL DEFAULT '0', `accountTax` int(11) NOT NULL DEFAULT '0', `accountUserId` int(8) NOT NULL DEFAULT '0', PRIMARY KEY (`accountBookId`,`accountId`), KEY `accountTax` (`accountTax`) );
Each account has a number. There are common numbering schemata for accounts for different purposes (like SKR03 or SKR04 for companies in Germany). This unique number is also the accounts id. The account name should describe the accounts purpose, a longer text can be stored in the notes field. If there is a budget planned for this account, it can be stored as well, allowing to see if the business plan for a year is working out as well. Accounts which have been already used can't be deleted as they are still needed for reference. Declaring them as "locked" will prevent usage in the booking mask. The tax automation rule associated with the account can be defined here as well. An account can also be associated with a user id, allowing a user who has no other rights in the current book to view his own personal account (we use this in our membership management, each member has one account, so it does not create confusion, if people pay their membership fees for a year in advance or quarterly or monthly).
CREATE TABLE `tiki_acct_journal` ( `journalBookId` int(10) unsigned NOT NULL, `journalId` int(10) unsigned NOT NULL AUTO_INCREMENT, `journalDate` date NOT NULL DEFAULT '0000-00-00', `journalDescription` varchar(255) NOT NULL, `journalCancelled` int(1) NOT NULL DEFAULT '0', `journalTs` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`journalBookId`,`journalId`) ); CREATE TABLE `tiki_acct_item` ( `itemJournalId` int(10) unsigned NOT NULL DEFAULT '0', `itemAccountId` int(10) unsigned NOT NULL DEFAULT '0', `itemType` int(1) NOT NULL DEFAULT '-1', `itemAmount` double NOT NULL DEFAULT '0', `itemText` varchar(255) NOT NULL DEFAULT '', `itemTs` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`itemJournalId`,`itemAccountId`,`itemType`) );
As one side of a statement (either credit or debit) can be split into several accounts, it is necessary to have two tables here. The Journal contains the data for the whole entry, which is mainly a unique id, the booking date, a text description for the whole process and if the entry has been cancelled.
For each entry in the journal, there must be at least 2 entries in the item table using the same journal id to create a valid entry. The item type is either -1 (debit) or 1 (credit). As both sides of an entry must always be equal, a simple query can be used to determine incomplete/defective entries by multiplying amount (which is always positive) by type and summing them up for each journal entry.
Especially when there are multiple entries on one side, the additional text can be used. For example, the amount transferred to the landlord usually consists of multiple parts, but is transferred in one amount from the bank account. So on credit side, there is only one account, the bank account but on debit side there are multiple accounts which are all in the same range when using a standard account schema. So we could state for example why in december we are transferring a different amount for gas or electricity than in the other months before which makes the journal easier to read later on.
One big problem with accounting is the topic of authenticity. Using timestamps, later alterations could be detected more easily (however a clever accountant would also manipulate those ๐ )
CREATE TABLE `tiki_acct_bankaccount` ( `bankBookId` int(10) unsigned NOT NULL, `bankAccountId` int(10) unsigned NOT NULL, `externalNumber` int(10) NOT NULL, `bankCountry` varchar(2) NOT NULL, `bankCode` varchar(11) NOT NULL, `bankIBAN` varchar(63) NOT NULL, `bankBIC` varchar(63) NOT NULL, `bankDelimeter` varchar(15) NOT NULL DEFAULT ';', `bankDecPoint` varchar(1) NOT NULL DEFAULT ',', `bankThousand` varchar(1) NOT NULL DEFAULT '.', `bankHasHeader` tinyint(1) NOT NULL DEFAULT '1', `fieldNameAccount` varchar(63) NOT NULL, `fieldNameBookingDate` varchar(63) NOT NULL, `formatBookingDate` varchar(31) NOT NULL, `fieldNameValueDate` varchar(63) NOT NULL, `formatValueDate` varchar(31) NOT NULL, `fieldNameBookingText` varchar(63) NOT NULL, `fieldNameReason` varchar(63) NOT NULL, `fieldNameCounterpartName` varchar(63) NOT NULL, `fieldNameCounterpartAccount` varchar(63) NOT NULL, `fieldNameCounterpartBankCode` varchar(63) NOT NULL, `fieldNameAmount` varchar(63) NOT NULL, `amountType` int(10) unsigned NOT NULL, `fieldNameAmountSign` varchar(63) NOT NULL, `SignPositive` varchar(7) NOT NULL, `SignNegative` varchar(7) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`bankBookId`,`bankAccountId`) );
This is actually the import specification for CSV/text bank statements. Generally speaking, if there is an external source triggering transactions on one account in the book, here the rules for importing data provided by the external "supplier" can be specified.
First of all, the internal book and account id are specified. The next fields, the external number (your bank account number, currently maximum of 10 digits), the country code, bank code and the international IBAN and BIC are specified for (future) exports of transactions (cdrwhite is dreaming of a dtaus generation tool or a link to finTS applications but lacks the time for that).
The next fields define the delimeter, decimal point, thousands separator of the source file. it also specifies, if the file provided by the bank has a header line with field names or not (cdrwhite: maybe this should be changed to int to allow multiple header lines, but I have not seen german banks so far which do that).
The field names define how your bank is labelling the different columns. Not all fields must be present at all times but at least one date (value preferred over booking date) should be there and of course, the reason field is the easiest way of seeing what the sender of the money wanted to tell. The booking text usually defines the type of transaction like debit, credit, fees, salary, if provided at all. The counterpart data will be used in automagically assigning booking templates to well known transactions (like the recurring payment to the landlord or our 50+ monthly membership fees).
The amount field is also a very important field for automated processing of a bank statement, however 4 out of 5 german banks I work with have different ways of specifying the amount. The simplest way is having positive and negative amounts, some banks use two columns for positive and negative amounts (with and without -) and some use a separate column stating S for debit and H for credit. This is why we also need the amount type and sign fields. (todo: fix inconsistent capitalization)
CREATE TABLE IF NOT EXISTS `tiki_acct_statement` ( `statementBookId` int(10) unsigned NOT NULL, `statementAccountId` int(10) unsigned NOT NULL DEFAULT '0', `statementId` int(10) unsigned NOT NULL AUTO_INCREMENT, `statementBookingDate` date NOT NULL, `statementValueDate` date NOT NULL, `statementBookingText` varchar(255) NOT NULL, `statementReason` varchar(255) NOT NULL, `statementCounterpart` varchar(63) NOT NULL, `statementCounterpartAccount` varchar(63) NOT NULL, `statementCounterpartBankCode` varchar(63) NOT NULL, `statementAmount` double NOT NULL, `statementJournalId` int(10) unsigned NOT NULL DEFAULT '0', `statementStackId` int(11) NOT NULL, PRIMARY KEY (`statementBookId`,`statementAccountId`,`statementId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
This contains the data from all bank statements after they have been imported. The moment, a statement is converted into an entry in the stack, the stackId is set to the id of the entry in the stack and as soon as it goes to the journal, the journalId is set to the journalId. This allows easy referencing of the bank statements with the actual bookings. In this table all fields which make sense for import are defined with field names and field order defined by us ๐
CREATE TABLE `tiki_acct_stack` ( `stackBookId` int(10) unsigned NOT NULL, `stackId` int(10) unsigned NOT NULL AUTO_INCREMENT, `stackDate` date NOT NULL DEFAULT '0000-00-00', `stackDescription` varchar(255) NOT NULL, `stackTs` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`stackBookId`,`stackId`) ); CREATE TABLE `tiki_acct_stackitem` ( `stackItemStackId` int(10) unsigned NOT NULL DEFAULT '0', `stackItemAccountId` int(10) unsigned NOT NULL DEFAULT '0', `stackItemType` int(1) NOT NULL DEFAULT '-1', `stackItemAmount` double NOT NULL DEFAULT '0', `stackItemText` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`stackItemStackId`,`stackItemAccountId`,`stackItemType`) );
The stack is a kind of journal "light" where transactions can be altered or deleted. Only when they are finally approved, they go to the journal and can't be changed any more. The data structure is almost the same as for the journal/item tables, only the timestamps and "Cancelled" fields are missing, as they are not used.
By splitting the rights to book into the stack and into the real journal, a 4-eyes-policy can be established or less qualified people can do the stupid typing stuff and the accountant just has to check/correct their data before promoting stuff to the journal.
CREATE TABLE `tiki_acct_tax` ( `taxBookId` int(10) unsigned NOT NULL, `taxId` int(10) unsigned NOT NULL AUTO_INCREMENT, `taxText` varchar(63) NOT NULL, `taxAmount` double NOT NULL DEFAULT '0', `taxIsFix` enum('y','n') NOT NULL DEFAULT 'n', PRIMARY KEY (`taxBookId`,`taxId`) );
This table defines the different types of taxes for automated splitting. The default type 0 (no tax relevance) does not have to be defined.
Taxes can be specified individually for each book and globally (for all books of the site by setting taxBookId to 0). The tax amount is usually a factor/percentage, but it might also be possible to have a fixed amount (Which would be funny, having a fixed tax of 1 EUR, even if the amount is only 1 Cent).
This allows me to specify the VAT I have to pay (Vorsteuer, which I can later deduct from the VAT i collected from my customers), where we actually have 2 of them 7% and 19% (plus of course some special stuff for inter european building contracts and so on) and also the VAT i have to collect from my customers (Umsatzsteuer). Each entry has a unique Id and can be referenced by the appropriate accounts.
Contains the core functionality (more text to follow, but its documented in the code. Really!)
Create books and view the books I have access to.
This is the main page for each book, here you can see a list of accounts and trigger various actions. This will become the main dashboard for an accountant when working in one book.
Manage the actual accounts
Actually does the booking of a statement
This is a general export routine (so far only CSV, but later on other formats like print(html) or jquery.Sheet should be handled here
Allowing the stack as "sandbox" or to implement a 4-eyes-policy
This permission should be assigned globally to alkl accountants who can create close a book
This permission should be assigned as object permission on each book. The people in the corresponding group can create/edit/lock accounts and even delete them if they are unused
This (object) permission allows creating actual entries in the journal or transfer entries from the stack to the journal
This object permission only allows creation of new entries in the stack, where statements can be changed. They only become valid statements after being confirmed (moved to the journal by someone with tiki_p_acct_book
This object permission allows importing statements from external accounts as CSV/text files
This object permission allows creating and editing templates for typical transactions which are recurring but a lot of work to type in