Using SQLite to maintain your monthly bills.

I was looking for a small application to maintain my every month bills. The application had to be small and script-able. I basically wanted to keep track of the bills that have been generated and pending payment and the bills that have been paid.

First we will create the database schema with the following command:

1	CREATE TABLE bills(name varchar(20), due_dt text, p_dt text, amount integer);

So, I built on the knowledge gathered on SQLite. And here is a small script that I wrote:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

#!/bin/bash -
#===============================================================================
#
#          FILE: bills.sh
#
#         USAGE: ./bills.sh
#
#   DESCRIPTION: Complete bill management
#
#       OPTIONS: ---
# REQUIREMENTS: ---
#          BUGS: ---
#         NOTES: ---
#        AUTHOR: <a class="zem_slink" title="Amit Agarwal" rel="homepage" href="http://amit-agarwal.co.in">Amit Agarwal</a> (AKA), <a class="linkification-ext" title="Linkification: mailto:amit.agarwal@amit-agarwal.co.in" href="mailto:amit.agarwal@amit-agarwal.co.in">amit.agarwal@amit-agarwal.co.in</a>
#       COMPANY: Individual
#       VERSION: 1.0
#       CREATED: 08/12/2010 08:28:05 AM <a class="zem_slink" title="Indian Standard Time" rel="geolocation" href="http://maps.google.com/maps?ll=23.1827777778,75.7772222222&spn=0.1,0.1&q=23.1827777778,75.7772222222%20%28Indian%20Standard%20Time%29&t=h">IST</a>
#      REVISION: ---
#===============================================================================

#Directory where you created the file.
dir="$HOME/Bills"

#The file that will be used as schema.

file=bills

#The table that you created above.

table=bills

#Here are the names of the accounts. Dont use the first one.
names=(Junk A B C D E)

#Here are the respective dates.
due_dts=(0 01 02 03 04 05)

menu_items=(
"1. View all pending"
"2. Make payment"
"3. Add pending payment"
"4. Display the Schema"
)
menu_function=( display_pending make_pay insert_due display_schema)

#=== FUNCTION ================================================================
#          NAME: display_menu
#   DESCRIPTION: Display the menu
#    PARAMETERS:
#       RETURNS:
#===============================================================================
display_menu ()
{
for i in `seq 0 ${#menu_items[@]}`
do
echo ${menu_items[$i]}
done
}    # ---------- end of function display_menu ----------

#=== FUNCTION ================================================================
#          NAME: insert_due
#   DESCRIPTION: Insert a pending payment
#    PARAMETERS:
#       RETURNS:
#===============================================================================
insert_due ()
{
mn=$(date +%m)
for i in `seq 1 ${#names[@]}`
do
echo $i ${names[$i]}
done
read -p "Enter selection :: " selection
read -p "Enter the amount ::" amt
if [ ${due_dts[$selection]} -le `date +"%d"` ]
then
mn=$(echo $mn |sed \'s/^0*//\')
let mn=$mn+1
[ $mn -le 9 ] && mn="0$mn"
echo $mn
fi
dd=$(echo `date +"%Y-"`$mn-${due_dts[$selection]} 00:00:00)

echo "insert into $table values ("${names[$selection]}", "$dd", "", "$amt");"|sqlite3 $file
}

#=== FUNCTION ================================================================
#          NAME: display_schema
#   DESCRIPTION: Display the schema for the table
#    PARAMETERS:
#       RETURNS:
#===============================================================================

display_schema ()
{
echo ".schema $table"|sqlite3 $file
}    # ---------- end of function display_schema ----------

#=== FUNCTION ================================================================
#          NAME: make_pay
#   DESCRIPTION: Update the payment in the database
#    PARAMETERS:
#       RETURNS:
#===============================================================================
make_pay ()
{
echo "Select one of the following :"
for i in `seq 1 ${#names[@]}`
do
echo $i ${names[$i]}
done
read -p "Enter selection :: " selection
dd=$(echo `date +"%Y-%m-%d %H:%M:%S"`)

echo "update $table set p_dt="$dd" where name="${names[$selection]}" and p_dt="";"|sqlite3 $file
}    # ---------- end of function make_pay ----------

#=== FUNCTION ================================================================
#          NAME: display_pending
#   DESCRIPTION: Display all the pending payments
#    PARAMETERS:
#       RETURNS:
#===============================================================================
display_pending ()
{
echo "select name,amount,due_dt from $table where p_dt="";"|sqlite3 $file | sed -e \'s/|/\\t\\t--/g\' -e \'s/00:.*//\'
}    # ---------- end of function display_pending ----------

cd $dir
display_menu
read -p "Please make a selection :: " input
((input--))
eval ${menu_function[$input]}

Hope this will save you the effort to write a new application. Feel free to use this program/application. Modify this or whatever. If you are distributing this, please give due credit.

How to dump out SQL from an SQLite database (posterous.mclov.in)
SQLabs announces SQLiteManager 3.5 – Powerful SQlite Database Manager (themactrack.com)
Using SQLite (oreilly.com)
Mongrel2 Sqlite3 Config System Working (sheddingbikes.com)

$\"Enhanced$

Posts

Using SQLite to maintain your monthly bills.

Like this:

Related

Related articles by Zemanta

Socialize

Like this:

Related