Top Level Namespace

Defined Under Namespace

Modules: Billinfo

Instance Method Summary collapse

Instance Method Details

#billavginc(info_hash) ⇒ Object 



106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/billinfo_methods.rb', line 106

def billavginc(info_hash)
	begin
		db = SQLite3::Database.open('billinfo.db')
		db.transaction
		@avginc = @total_amt / @total_rows
		update_avginc =  db.execute2 "SELECT Amount from bills WHERE Type = :Type AND Year = :Year", info_hash[:category], info_hash[:year]
		update_avginc.each do |line|
			db.execute2 "UPDATE bills SET AvgInc = :AvgInc WHERE Type = :Type AND Year = :Year", @avginc, info_hash[:category], info_hash[:year]
			end
		if db.changes == 1
			alter = "change"
		else
			alter = "changes"
		end
		puts db.changes.to_s + " #{alter} made " + "for #{info_hash[:year].to_s}"
		db.commit
	rescue SQLite3::Exception => e
		puts "error here " , e
	ensure
		db.close if db
	end
end

#billavgx(info_hash) ⇒ Object 



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/billinfo_methods.rb', line 129

def billavgx(info_hash)
	begin
		db = SQLite3::Database.open('billinfo.db')
		db.results_as_hash = true
		db.transaction
		specific_amt =  db.prepare "SELECT Amount FROM bills WHERE Type = :Type AND Year = :Year" 
		specific_amt.execute info_hash[:category], info_hash[:year]
		specific_amt.each do |specific_row|
			if @total_rows == 1
				avgx = @total_amt / @total_rows
			elsif @total_rows > 1
				avgx = (@total_amt - specific_row[0]) / (@total_rows - 1)
			else
				puts "insufficient entries"
				return "insufficient entries"
			end	
			db.execute2 "UPDATE bills SET AvgX = :AvgX WHERE Amount = :Amount AND Type = :Type AND Year = :Year", avgx, specific_row[0], info_hash[:category], info_hash[:year]
		end
		db.commit
	rescue SQLite3::Exception => e
		puts "error here " , e
	ensure
		specific_amt.close if specific_amt
		db.close if db
	end
end

#billdb(info_hash) ⇒ Object 



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
# File 'lib/billinfo_methods.rb', line 56

def billdb(info_hash)
	begin
		db = SQLite3::Database.open('billinfo.db')
		db.transaction
		db.execute2 "CREATE table if not exists bills(Id INTEGER PRIMARY KEY, Type TEXT, Month TEXT, Year INTEGER, Amount FLOAT, AvgInc FLOAT, AvgX FLOAT, Total FLOAT)"
		db.execute2 "INSERT into bills(Type, Month, Year, Amount) values(:Type, :Month, :Year, :Amount)", info_hash[:category], info_hash[:month], info_hash[:year], info_hash[:amt]
		db.commit
		puts db.changes.to_s + " entry made"
	rescue SQLite3::Exception => e
		puts e
		db.rollback
	ensure
		db.close if db
	end
end

#billtotal(info_hash) ⇒ Object 



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/billinfo_methods.rb', line 72

def billtotal(info_hash)
	begin
		db = SQLite3::Database.open('billinfo.db')
		db.transaction
		get_amt =  db.execute2 "SELECT sum(Amount) from bills WHERE Type = :Type AND Year = :Year", info_hash[:category], info_hash[:year]
		db.execute2 "UPDATE bills SET Total = :Total WHERE Type = :Type AND Year = :Year", get_amt[1][0], info_hash[:category], info_hash[:year]
		@total_amt=get_amt[1][0]
				if db.changes == 1
			alter = "change"
		else
			alter = "changes"
		end
		puts db.changes.to_s + " #{alter} made to " + info_hash[:category].to_s + " for " + info_hash[:year].to_s
		db.commit
	rescue SQLite3::Exception => e
		puts "error here " , e
	ensure
		db.close if db
	end
	@total_amt
end

#create_categories(new_category = nil) ⇒ Object 



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
 
# File 'lib/billinfo_methods.rb', line 6

def create_categories(new_category = nil)
	unless File.file?('billinfo.db')
		categories = ['electricity','water','cable','streaming','rent','food','clothes','out \'n about']
		begin
			db = SQLite3::Database.open('billinfo.db')
			db.transaction
			db.execute2 "CREATE table if not exists categories(Type TEXT PRIMARY KEY)"
			categories.each { |cat| db.execute2 "INSERT into categories(Type) values(:Type)", cat }
			db.commit
		rescue SQLite3::Exception => e
			puts e
			db.rollback
		ensure
			db.close if db
		end
	end
	if new_category != nil
		begin 
			db = SQLite3::Database.open('billinfo.db')
			db.transaction
			db.execute2 "INSERT into categories(Type) values(:new_category)", new_category
			db.commit
			puts db.changes.to_s + " new category added"
		rescue SQLite3::Exception => e
			puts e
			db.rollback
		ensure
			db.close if db
		end
	end
end

#observations(info_hash) ⇒ Object 



94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/billinfo_methods.rb', line 94

def observations(info_hash)
	begin
		db = SQLite3::Database.open('billinfo.db')
		total_rows =  db.execute2 "SELECT COUNT(*) from bills WHERE Type = :Type AND Year = :Year", info_hash[:category], info_hash[:year]
	rescue SQLite3::Exception => e
		puts "error here " , e
	ensure
		db.close if db
	end
	@total_rows=total_rows[1][0]
end

#print_standard_categories ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/billinfo_methods.rb', line 38

def print_standard_categories
	begin
		db = SQLite3::Database.open('billinfo.db')
		db.results_as_hash = true
		categories_to_print = []
		categories = db.prepare "SELECT Type FROM categories"
		categories.execute
		categories.each { |cat| categories_to_print << cat[0] }
		@categories_to_print = categories_to_print
	rescue SQLite3::Exception => e
		puts e
	ensure
		categories.close if categories
		db.close if db
	end
	@categories_to_print
end

#printinfo(info_hash) ⇒ Object 



175
176
177
178
179
180
181
182
183
184
185
186
187
 
# File 'lib/billinfo_methods.rb', line 175

def printinfo(info_hash)
	begin
		db = SQLite3::Database.open('billinfo.db')
		db_print = db.execute2 "SELECT * FROM bills WHERE Type = :Type AND Year = :Year", info_hash[:category], info_hash[:year]
		db_print.each do |line|
			puts "%s %s %s %s %s [%s | %s] %s" % [line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7], line[8]]
		end
	rescue SQLite3::Exception => e
		puts e
	ensure
		db.close if db
	end
end

#uniq_yrs ⇒ Object 



156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'lib/billinfo_methods.rb', line 156

def uniq_yrs
	begin
		db = SQLite3::Database.open('billinfo.db')
		yrs = []
		db_print = db.execute2 "SELECT Year FROM bills"
		db_print.each do |yr|
			yrs << yr[0]
		end
		yrs.shift
		@yrs = yrs.uniq
	rescue SQLite3::Exception => e
		puts e
	ensure
		db.close if db
	end
	p @yrs.class
	@yrs
end