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.
Related articles by Zemanta
- 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)
Link to this post!
You must log in to post a comment.