From: Sergey Matveev <stargrave@stargrave.org>
Date: Thu, 29 Oct 2015 08:38:16 +0000 (+0300)
Subject: Initial revision
X-Git-Url: http://www.git.stargrave.org/?p=pem.git;a=commitdiff_plain;h=0287278e02928ff0179c9a08db6cdcb8709b0ace

Initial revision
---

0287278e02928ff0179c9a08db6cdcb8709b0ace
diff --git a/pem b/pem
new file mode 100755
index 0000000..1a350ef
--- /dev/null
+++ b/pem
@@ -0,0 +1,94 @@
+#!/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