2 # pem -- GNU Personal Expenses Manager inspired simpler script
3 # Copyright (C) 2015-2020 Sergey Matveev (stargrave@stargrave.org)
5 # This program is free software; you can redistribute it and/or modify
6 # it under the terms of the GNU General Public License as published by
7 # the Free Software Foundation; either version 3 of the License, or
8 # (at your option) any later version.
10 # This program is distributed in the hope that it will be useful,
11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with this program. If not, see <http://www.gnu.org/licenses/>.
18 # This script is simpler version of GNU PEM software. It uses sqlite3 as
21 # Schema that is used for it:
23 # CREATE TABLE expenses (
24 # id INTEGER PRIMARY KEY,
25 # dt DATETIME NOT NULL,
26 # earned NUMERIC NOT NULL,
27 # spent NUMERIC NOT NULL,
31 # id INTEGER PRIMARY KEY,
35 # id INTEGER PRIMARY KEY,
38 # FOREIGN KEY(expense_id) REFERENCES expenses(id),
39 # FOREIGN KEY(tag_id) REFERENCES tags(id)
43 DB=$HOME/secure/money.db
48 [ -n "$dt" ] || dt="-1 day"
49 $SQLITE -column -header $DB "
51 date(expenses.dt) AS \"when\",
52 expenses.earned - expenses.spent AS amount,
53 group_concat(tags.name, ',') AS tags,
54 expenses.descr AS \"what\"
56 LEFT JOIN m2m ON m2m.expense_id=expenses.id
57 LEFT JOIN tags ON m2m.tag_id=tags.id
62 $SQLITE -column -header $DB "
64 SUM(earned) AS earned,
66 SUM(earned) - SUM(spent) AS balance
77 if echo $money | grep -q "^-"; then
78 earned=$(echo $money | sed 's/^-//')
82 $SQLITE $DB "INSERT INTO expenses
83 (dt, earned, spent, descr) VALUES
84 (datetime('now'), $earned, $spent, '$descr')"
85 last_id=$($SQLITE $DB "SELECT id FROM expenses ORDER BY id DESC LIMIT 1")
86 for tag in $(echo $tags | sed 's/,/ /g'); do
87 tag_id=$($SQLITE $DB "SELECT id FROM tags WHERE name='$tag'")
88 if [ "$tag_id" = "" ]; then
89 $SQLITE $DB "INSERT INTO tags (name) VALUES ('$tag')"
90 tag_id=$($SQLITE $DB "SELECT id FROM tags WHERE name='$tag'")
92 $SQLITE $DB "INSERT INTO m2m (expense_id, tag_id) VALUES ($last_id, $tag_id)"