-
Notifications
You must be signed in to change notification settings - Fork 0
/
expense
executable file
·145 lines (118 loc) · 3.64 KB
/
expense
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
134
135
136
137
138
139
140
141
142
143
144
145
#! /usr/bin/env ruby
# run by $ ./expense
require 'pg'
require 'io/console'
class CLI
def initialize
@application = ExpenseData.new
end
def run(arguments)
case arguments.first
when 'list'
@application.list_expenses
when 'add'
amount = arguments[1]
memo = arguments[2]
# Kernel.abort will exit with code 1 and a message
abort "You must provide an amount and memo." unless amount && memo
@application.add_expenses(amount, memo)
when 'search'
query = arguments[1]
abort "You must provide an seach term." unless query
@application.search_expenses(query)
when 'delete'
id = arguments[1]
abort "You must provide an id of the record to delete." unless id
@application.delete_expense(id)
when 'clear'
puts "This will remove all expenses. Are you sure? (y/n)"
answer = $stdin.getch
exit 0 if answer.downcase != 'y'
@application.delete_all_expenses
else
display_help
end
end
def display_help
puts %(
An expense recording system
Commands:
add AMOUNT MEMO [DATE] - record a new expense
clear - delete all expenses
list - list all expenses
delete NUMBER - remove expense with id NUMBER
search QUERY - list expenses with a matching memo field
)
end
end
class ExpenseData
def initialize
%x(docker start ruby-postgres) # having the cli start the container
@db = PG.connect(dbname: 'expenses',
user: 'postgres',
host: '127.0.0.1', # localhost (or your ip)
password: 'mysecret',
port: 5432)
setup_schema
end
def list_expenses
result = @db.exec('SELECT * FROM expenses ORDER BY created_on DESC;')
abort "There are no expenses to list" if result.ntuples == 0
display_count(result)
display_expenses(result)
end
def add_expenses(amount, memo)
sql = "INSERT INTO expenses (amount, memo)
VALUES ($1, $2);"
@db.exec_params(sql, [amount, memo]).values
end
def search_expenses(query)
sql = "SELECT * FROM expenses WHERE memo ILIKE $1"
result = @db.exec_params(sql, ["%#{query}%"])
abort "There are no expenses to list" if result.ntuples == 0
display_count(result)
display_expenses(result)
end
def delete_expense(id)
sql = "SELECT * FROM expenses WHERE id = $1"
result = @db.exec_params(sql, [id])
abort "There is no expense with the id '#{id}'" if result.ntuples != 1
result = @db.exec_params(sql, [id])
puts "The following expense has been deleted:"
display_expenses(result)
end
def delete_all_expenses
@db.exec("DELETE FROM expenses;")
puts "All expenses have been deleted."
end
def display_count(result)
puts "There is #{result.ntuples} expense" if result.ntuples == 1
puts "There are #{result.ntuples} expenses" if result.ntuples > 1
end
def setup_schema
sql = "SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'expenses';"
result = @db.exec(sql)
if result[0]['count'] == '0'
schema = File.read('schema.sql')
@db.exec(schema)
end
end
private
def display_expenses(expenses)
total = 0.0
expenses.each do |row|
columns = [ row["id"].rjust(3),
row["created_on"][0..10].rjust(10),
row["amount"].rjust(12),
row["memo"] ]
total += row["amount"].to_f
puts columns.join(" | ")
end
puts '-' * 50
puts "Total #{total.round(2).to_s.rjust(26)}"
end
end
CLI.new.run(ARGV)