Using SQLite to maintain your monthly bills.

2010-08-24 6 min read Linux

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 <a class="zem_slink" title="Database" rel="wikipedia" href="http://en.wikipedia.org/wiki/Database">database schema with the following command:

  <td>
    <div class="text codecolorer">
      CREATE TABLE bills(name varchar(20), due_dt text, p_dt text, amount integer);
    </div>
  </td>
</tr>
1

So, I built on the knowledge gathered on <a class="zem_slink" title="SQLite" rel="homepage" href="http://sqlite.org/">SQLite. And here is a small script that I wrote:

  <td>
    <div class="bash codecolorer">
      <span class="co0">#!/bin/bash -</span><br /> <span class="co0">#===============================================================================</span><br /> <span class="co0">#</span><br /> <span class="co0">#          FILE:  bills.sh</span><br /> <span class="co0">#</span><br /> <span class="co0">#         USAGE:  ./bills.sh</span><br /> <span class="co0">#</span><br /> <span class="co0">#   DESCRIPTION:  Complete bill management</span><br /> <span class="co0">#</span><br /> <span class="co0">#       OPTIONS:  ---</span><br /> <span class="co0">#  REQUIREMENTS:  ---</span><br /> <span class="co0">#          BUGS:  ---</span><br /> <span class="co0">#         NOTES:  ---</span><br /> <span class="co0">#        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></span><br /> <span class="co0">#       COMPANY:  Individual</span><br /> <span class="co0">#       VERSION:  1.0</span><br /> <span class="co0">#       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></span><br /> <span class="co0">#      REVISION:  ---</span><br /> <span class="co0">#===============================================================================</span><br /> <br /> <span class="co0">#Directory where you created the file.</span><br /> <span class="re2">dir</span>=<span class="st0">"<span class="es2">$HOME</span>/Bills"</span><br /> <br /> <span class="co0">#The file that will be used as schema.</span><br /> <br /> <span class="re2">file</span>=bills<br /> <br /> <span class="co0">#The table that you created above.</span><br /> <br /> <span class="re2">table</span>=bills<br /> <br /> <span class="co0">#Here are the names of the accounts. Dont use the first one.</span><br /> <span class="re2">names</span>=<span class="br0">&#40;</span>Junk A B C D E<span class="br0">&#41;</span><br /> <br /> <span class="co0">#Here are the respective dates.</span><br /> <span class="re2">due_dts</span>=<span class="br0">&#40;</span><span class="nu0"></span> 01 02 03 04 05<span class="br0">&#41;</span><br /> <br /> <span class="re2">menu_items</span>=<span class="br0">&#40;</span><br /> <span class="st0">"1. View all pending"</span><br /> <span class="st0">"2. Make payment"</span><br /> <span class="st0">"3. Add pending payment"</span><br /> <span class="st0">"4. Display the Schema"</span><br /> <span class="br0">&#41;</span><br /> <span class="re2">menu_function</span>=<span class="br0">&#40;</span> display_pending make_pay insert_due display_schema<span class="br0">&#41;</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  display_menu</span><br /> <span class="co0">#   DESCRIPTION:  Display the menu</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> display_menu <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw1">for</span> i <span class="kw1">in</span> <span class="sy0">`</span><span class="kw2">seq</span> <span class="nu0"></span> <span class="co1">${#menu_items[@]}</span><span class="sy0">`</span><br /> <span class="kw1">do</span><br /> <span class="kw3">echo</span> <span class="co1">${menu_items[$i]}</span><br /> <span class="kw1">done</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function display_menu  ----------</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  insert_due</span><br /> <span class="co0">#   DESCRIPTION:  Insert a pending payment</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> insert_due <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="re2">mn</span>=$<span class="br0">&#40;</span><span class="kw2">date</span> +<span class="sy0">%</span>m<span class="br0">&#41;</span><br /> <span class="kw1">for</span> i <span class="kw1">in</span> <span class="sy0">`</span><span class="kw2">seq</span> <span class="nu0">1</span> <span class="co1">${#names[@]}</span><span class="sy0">`</span><br /> <span class="kw1">do</span><br /> <span class="kw3">echo</span> <span class="re1">$i</span> <span class="co1">${names[$i]}</span><br /> <span class="kw1">done</span><br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Enter selection :: "</span> selection<br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Enter the amount ::"</span> amt<br /> <span class="kw1">if</span> <span class="br0">&#91;</span> <span class="co1">${due_dts[$selection]}</span> -le  <span class="sy0">`</span><span class="kw2">date</span> +<span class="st0">"%d"</span><span class="sy0">`</span> <span class="br0">&#93;</span><br /> <span class="kw1">then</span><br /> <span class="re2">mn</span>=$<span class="br0">&#40;</span><span class="kw3">echo</span> <span class="re1">$mn</span> <span class="sy0">|</span><span class="kw2">sed</span> <span class="co3">\'</span>s<span class="sy0">/</span>^<span class="nu0"></span><span class="sy0">*//</span><span class="co3">\'</span><span class="br0">&#41;</span><br /> <span class="kw3">let</span> <span class="re2">mn</span>=<span class="re1">$mn</span>+<span class="nu0">1</span><br /> <span class="br0">&#91;</span> <span class="re1">$mn</span> <span class="re5">-le</span> <span class="nu0">9</span> <span class="br0">&#93;</span> <span class="sy0">&</span>amp;<span class="sy0">&</span>amp; <span class="re2">mn</span>=<span class="st0">"0<span class="es2">$mn</span>"</span><br /> <span class="kw3">echo</span> <span class="re1">$mn</span><br /> <span class="kw1">fi</span><br /> <span class="re2">dd</span>=$<span class="br0">&#40;</span><span class="kw3">echo</span> <span class="sy0">`</span><span class="kw2">date</span> +<span class="st0">"%Y-"</span><span class="sy0">`</span><span class="re1">$mn</span>-<span class="co1">${due_dts[$selection]}</span> 00:00:00<span class="br0">&#41;</span><br /> <br /> <span class="kw3">echo</span> <span class="st0">"insert into <span class="es2">$table</span> values ("</span><span class="co1">${names[$selection]}</span><span class="st0">", "</span><span class="re1">$dd</span><span class="st0">", "</span><span class="st0">", "</span><span class="re1">$amt</span><span class="st0">");"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span><br /> <span class="br0">&#125;</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  display_schema</span><br /> <span class="co0">#   DESCRIPTION:  Display the schema for the table</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> <br /> display_schema <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw3">echo</span> <span class="st0">".schema <span class="es2">$table</span>"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function display_schema  ----------</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  make_pay</span><br /> <span class="co0">#   DESCRIPTION:  Update the payment in the database</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> make_pay <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw3">echo</span> <span class="st0">"Select one of the following :"</span><br /> <span class="kw1">for</span> i <span class="kw1">in</span> <span class="sy0">`</span><span class="kw2">seq</span> <span class="nu0">1</span> <span class="co1">${#names[@]}</span><span class="sy0">`</span><br /> <span class="kw1">do</span><br /> <span class="kw3">echo</span> <span class="re1">$i</span> <span class="co1">${names[$i]}</span><br /> <span class="kw1">done</span><br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Enter selection :: "</span> selection<br /> <span class="re2">dd</span>=$<span class="br0">&#40;</span><span class="kw3">echo</span> <span class="sy0">`</span><span class="kw2">date</span> +<span class="st0">"%Y-%m-%d %H:%M:%S"</span><span class="sy0">`</span><span class="br0">&#41;</span><br /> <br /> <span class="kw3">echo</span> <span class="st0">"update <span class="es2">$table</span> set p_dt="</span><span class="re1">$dd</span><span class="st0">" where name="</span><span class="co1">${names[$selection]}</span><span class="st0">" and p_dt="</span><span class="st0">";"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function make_pay  ----------</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  display_pending</span><br /> <span class="co0">#   DESCRIPTION:  Display all the pending payments</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> display_pending <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw3">echo</span> <span class="st0">"select name,amount,due_dt from <span class="es2">$table</span> where p_dt="</span><span class="st0">";"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span> <span class="sy0">|</span> <span class="kw2">sed</span> <span class="re5">-e</span> <span class="co3">\'</span>s<span class="sy0">/|/</span>\\t\\t--<span class="sy0">/</span>g<span class="co3">\'</span> <span class="re5">-e</span> <span class="co3">\'</span>s<span class="sy0">/</span>00:.<span class="sy0">*//</span><span class="co3">\'</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function display_pending  ----------</span><br /> <br /> <span class="kw3">cd</span> <span class="re1">$dir</span><br /> display_menu<br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Please make a selection :: "</span> input<br /> <span class="br0">&#40;</span><span class="br0">&#40;</span>input--<span class="br0">&#41;</span><span class="br0">&#41;</span><br /> <span class="kw3">eval</span> <span class="co1">${menu_function[$input]}</span>
    </div>
  </td>
</tr>
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

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.

\"Enhanced
comments powered by Disqus