--- /dev/null
+#!/bin/sh -e
+# pem -- GNU Personal Expenses Manager inspired simpler script
+# Copyright (C) 2015 Sergey Matveev (stargrave@stargrave.org)
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+# This script is simpler version of GNU PEM software. It uses sqlite3 as
+# a database storage.
+#
+# Schema that is used for it:
+#
+# CREATE TABLE expenses (
+# id INTEGER PRIMARY KEY,
+# dt DATETIME NOT NULL,
+# earned NUMERIC NOT NULL,
+# spent NUMERIC NOT NULL,
+# descr TEXT NOT NULL
+# );
+# CREATE TABLE tags (
+# id INTEGER PRIMARY KEY,
+# name TEXT NOT NULL
+# );
+# CREATE TABLE m2m (
+# id INTEGER PRIMARY KEY,
+# expense_id INTEGER,
+# tag_id INTEGER,
+# FOREIGN KEY(expense_id) REFERENCES expenses(id),
+# FOREIGN KEY(tag_id) REFERENCES tags(id)
+# );
+
+SQLITE=sqlite3
+DB=$HOME/secure/money.db
+
+case "$1" in
+s)
+ dt="$2"
+ [ -n "$dt" ] || dt="-1 day"
+ $SQLITE -column -header $DB "
+ SELECT
+ date(expenses.dt) AS \"when\",
+ expenses.earned - expenses.spent AS amount,
+ group_concat(tags.name, ',') AS tags,
+ expenses.descr AS \"what\"
+ FROM expenses
+ LEFT JOIN m2m ON m2m.expense_id=expenses.id
+ LEFT JOIN tags ON m2m.tag_id=tags.id
+ GROUP BY expenses.id"
+ ;;
+t)
+ $SQLITE -column -header $DB "
+ SELECT
+ SUM(earned) AS earned,
+ SUM(spent) AS spent,
+ SUM(earned) - SUM(spent) AS balance
+ FROM expenses"
+ ;;
+*)
+ spent=0
+ earned=0
+ descr="$1"
+ money="$2"
+ tags="$3"
+ [ -n "$descr" ]
+ [ -n "$money" ]
+ if echo $money | grep -q "^-"; then
+ earned=$(echo $money | sed 's/^-//')
+ else
+ spent=$money
+ fi
+ $SQLITE $DB "INSERT INTO expenses
+ (dt, earned, spent, descr) VALUES
+ (datetime('now'), $earned, $spent, '$descr')"
+ last_id=$($SQLITE $DB "SELECT id FROM expenses ORDER BY id DESC LIMIT 1")
+ for tag in $(echo $tags | sed 's/,/ /g'); do
+ tag_id=$($SQLITE $DB "SELECT id FROM tags WHERE name='$tag'")
+ if [ "$tag_id" = "" ]; then
+ $SQLITE $DB "INSERT INTO tags (name) VALUES ('$tag')"
+ tag_id=$($SQLITE $DB "SELECT id FROM tags WHERE name='$tag'")
+ fi
+ $SQLITE $DB "INSERT INTO m2m (expense_id, tag_id) VALUES ($last_id, $tag_id)"
+ done
+ ;;
+esac