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