144 lines
6.3 KiB
Markdown
144 lines
6.3 KiB
Markdown
README
|
|
======
|
|
|
|
TreasureDB is a plain SQLite database application to observe and trace the finances of a
|
|
non-profit registered society or club. The ideal state of balance is zero both in debit
|
|
and in credit because they even out. In Germany, such clubs may not accumulate reserves.
|
|
|
|
Basics
|
|
------
|
|
|
|
As it is a SQLite3 database with all consistency and calculatory logic built in, you only
|
|
need the `sqlite3` binary or any sqlite3 GUI software to run it. Please note the tools
|
|
beyond that are not yet portable. A part of them require the bash and a unix/linux system.
|
|
|
|
1. Ensure sqlite3 is installed
|
|
|
|
1. Run the test suite: `bash t/schema.sh` (on Linux and unix systems with Bash installed)
|
|
|
|
# Or go by foot and test manually a little bit further
|
|
treasuredb $ export TRSRDB_SQLITE_FILE="..." # wherever you want to save it
|
|
treasuredb $ cat schema/{tables,*/*}.sql | sqlite3 $TRSRDB_SQLITE_FILE
|
|
treasuredb $ sqlite3 $TRSRDB_SQLITE_FILE
|
|
SQLite version 3.8.7.1 2014-10-29 13:59:56
|
|
Enter ".help" for usage hints.
|
|
sqlite> .read t/schema.sql
|
|
...
|
|
|
|
1. Use it:
|
|
|
|
```
|
|
export TRSRDB_SQLITE_FILE="..." # wherever you want to save it
|
|
cat schema/{tables,*/*}.sql | sqlite3 $TRSRDB_SQLITE_FILE
|
|
./trsr $COMMAND
|
|
```
|
|
|
|
`$COMMAND` can be one of the following:
|
|
|
|
* `charge` - input a batch of csv lines representing incoming and outgoing payments
|
|
* `ct` - charge, then make transfer (interactive assistent)
|
|
* `cts` - like ct, but output finally status of all
|
|
* `ctr` - like ct, but output finally reports of all
|
|
* `report` - get only reports
|
|
* `sql` - execute sqlite3 shell with all necessary pragmata and with line output
|
|
* `status` - get only statuses
|
|
* `transfer` - only make transfers (interactive assistent)
|
|
* `tr` - transfer, and output report
|
|
* `ts` - transfer, and output statuses
|
|
|
|
Input to `./trsr charge`
|
|
------------------------
|
|
|
|
You can separate the fields of each line by a comma and optional whitespace, or at least one whitespace. The order of columns is:
|
|
|
|
* The booking date in format YYYY-MM-DD,
|
|
* The account name,
|
|
* The value if it is a debit,
|
|
* The value if it is a credit,
|
|
* The purpose (in the case of a debit, it must start with bill ID and colon),
|
|
* Optional: "<< Comma-separated list of credit IDs used to pay the debt" or ">> Comma-separated list of bill IDs the booked income is used for", respectively
|
|
|
|
### Multi-line purposes
|
|
|
|
Multi-line purposes must either be surrounded by " (escape literal " by doubling it), or started in the next line and terminated with an empty one.
|
|
|
|
### How to book an incoming payment
|
|
|
|
In the credit column must be a value greater than 0. Cent must be passed as a decimal part, i.e. "100" really mean 100.00, not 1.00! In the debit column you MAY input a name starting with a letter (otherwise it needs to be just "+"), by which you can refer to a credit in lines below instead of the number.
|
|
|
|
### How to enter a target credit
|
|
|
|
A target credit is defined as initially equal 0.
|
|
|
|
### How to make an internal debt
|
|
|
|
A debit, recognized by an amount in the debit column, can have a target credit name or number in the credit column.
|
|
|
|
### How to book an outgoing payment
|
|
|
|
An outgoing payment must not have a target credit. Leave that field empty.
|
|
|
|
HTTP interface
|
|
--------------
|
|
|
|
Treasure DB includes a rudimentary HTTP interface. It requires Mojolicious. The interface
|
|
is developped and tested on version 7.13 onwards.
|
|
|
|
You can start it with `./trsr server`. It will stay in the foreground. To start it in daemon mode,
|
|
use `./trsr server >/tmp/trsr_db.log 2>&1 & disown` in the bash shell.
|
|
|
|
After first start, you will need to create a user: `./httpuser -a $username -g 2`.
|
|
The link that will be output leads you to an extended login form. Please enter a (good) password
|
|
twice, so typos are unlikely. The -g (or --grade) argument can be 0, 1 or 2:
|
|
|
|
* 2 is admin who has complete read-write access.
|
|
* 1 means auditor with complete view without writing permission.
|
|
* 0 means auditor who can view only the type-less accounts, e.g.
|
|
the club account is recommended to be without type, or(!) an account
|
|
named identically.
|
|
|
|
|
|
How does it work?
|
|
-----------------
|
|
|
|
There are four tables: Account, Credit, Debit and Transfers.
|
|
|
|
Account table stores the static details, i.e. the type of account, the record identifier for
|
|
another table that stores more information about the account owner, and the IBAN for outgoing
|
|
bank transfers.
|
|
|
|
The Credit table stores bank transfers from the member to the club, and also the target
|
|
records of internal transfers to the club and other members performing commercial services for
|
|
it. The value of the former sort is initially >0 and should not be altered afterwards.
|
|
The latter's value always starts at 0 and is increased automatically by transfers linked to the
|
|
according debts.
|
|
|
|
The Debit table stores the receipts documenting a debt, their link to the credit record of the
|
|
respective recipient as well as to the account from which it is paid.
|
|
|
|
The Transfer stores when which amount is transferred from which credit record to settle which debt.
|
|
So, a debt can be associated with n source credits, and a souce credit can settle multiple debts.
|
|
Transfers cannot be altered once inserted, they need to be revoked and re-entered. This is due to
|
|
triggers that have to rebalance the involved accounts. Whenever the view Balance contains positive
|
|
values for both credit and debt in a line, this indicates transfers yet to be entered.
|
|
|
|
Using the sqlite3 database purposefully means two things:
|
|
|
|
1. Keep `ReconstructedBankStatement` view in sync with the actual statements received from your bank
|
|
by entering the incoming and outcoming bank transfers in the Credit or Debit table, respectively,
|
|
best as soon as they show up.
|
|
|
|
2. Ideally, all records in the `Balance` view must be 0 in each of debit, credit and promised
|
|
columns. If neither debit nor credit is zero in the same record, you need to make internal transfers
|
|
linking records from the `CurrentDebts` and from the `AvailableCredits` tables. Otherwise,
|
|
ensure that members make the transfers they are obligated to and return any money that is paid
|
|
too much unless the indicated purposes, if any, also refer to dues in certain future.
|
|
|
|
License
|
|
-------
|
|
|
|
Copyright (c) 2016, Florian Heß.
|
|
All rights reserved.
|
|
|
|
See details in LICENSE file containing the General Public license, version 3.
|