Class: Comcalc::Database

Inherits:
Object
  • Object
show all
Defined in:
lib/comcalc/db.rb

Constant Summary collapse

DATABASE = 
SQLite3::Database.open "#{path}/wgdb.db"

Instance Method Summary collapse

Instance Method Details

#add_new_person(id, first_name, last_name, balance, created_at) ⇒ Object 



60
61
62
 
# File 'lib/comcalc/db.rb', line 60

def add_new_person(id, first_name, last_name, balance, created_at)
  DATABASE.execute("INSERT INTO person (id, first_name, last_name, balance, created_at) VALUES (?,?,?,?,?)",[id, first_name, last_name, balance, created_at])
end

#get_all_persons ⇒ Object

show statistics



50
51
52
53
54
 
# File 'lib/comcalc/db.rb', line 50

def get_all_persons
  DATABASE.execute("SELECT id, first_name, last_name FROM person").each do |x|
    puts "use: #{x[0]} for #{x[1]}"
  end
end

#get_amount_from_bill(id) ⇒ Object 



40
41
42
 
# File 'lib/comcalc/db.rb', line 40

def get_amount_from_bill(id)
  DATABASE.execute("SELECT value FROM bill where id=#{id}")
end

#get_involved_ppl(id) ⇒ Object 



44
45
46
 
# File 'lib/comcalc/db.rb', line 44

def get_involved_ppl(id)
  DATABASE.execute("SELECT person_id FROM involved_in_bill where bill_id=#{id}")
end

#get_last_bill_id ⇒ Object 



64
65
66
67
 
# File 'lib/comcalc/db.rb', line 64

def get_last_bill_id
  call = DATABASE.execute("SELECT id from bill")
  call.last[0]
end

#get_market ⇒ Object 



69
70
71
72
73
 
# File 'lib/comcalc/db.rb', line 69

def get_market
  DATABASE.execute("SELECT id, name FROM market").each do |x|
    puts "use: #{x[0]} for #{x[1]}"
  end
end

#get_name_by_id(id) ⇒ Object 



99
100
101
 
# File 'lib/comcalc/db.rb', line 99

def get_name_by_id(id)
  DATABASE.execute("SELECT first_name from person where id=#{id}")
end

#get_person_id_from_bill(id) ⇒ Object 



31
32
33
 
# File 'lib/comcalc/db.rb', line 31

def get_person_id_from_bill id
  DATABASE.execute("SELECT person_id from bill where id=#{id}")
end

#insert_into_bill(id, value, date, market_id, person_id, note) ⇒ Object 



56
57
58
 
# File 'lib/comcalc/db.rb', line 56

def insert_into_bill(id, value, date, market_id, person_id, note)
  DATABASE.execute("INSERT INTO bill (id, value, date, market_id, person_id, note) VALUES (?,?,?,?,?,?)",[id, value, date, market_id, person_id, note])
end

#involved_in_bill(id, person_id, bill_id) ⇒ Object 



87
88
89
 
# File 'lib/comcalc/db.rb', line 87

def involved_in_bill(id,person_id,bill_id)
  DATABASE.execute(("INSERT INTO involved_in_bill (id, person_id, bill_id) VALUES (?,?,?)"),[id,person_id,bill_id])
end

#monthly_data(column, table) ⇒ Object 



91
92
93
 
# File 'lib/comcalc/db.rb', line 91

def monthly_data(column, table)
  DATABASE.execute("SELECT #{column} from #{table} where date > date('now','-1 month')")
end

#monthly_data_person(column, table, person_id) ⇒ Object 



95
96
97
 
# File 'lib/comcalc/db.rb', line 95

def monthly_data_person(column, table, person_id)
  DATABASE.execute("SELECT #{column} from #{table} where date > date('now','-1 month') and person_id=#{person_id}")
end

#query(table, column, value) ⇒ Object 



13
14
15
16
17
18
 
# File 'lib/comcalc/db.rb', line 13

def query(table, column, value)
  call = DATABASE.prepare ("SELECT * FROM #{table} WHERE #{column}=?")
  call.execute(value).each do |row|
    puts row.join "\s"
  end
end

#query_spec(column1 = '', column2 = '', column3 = '', table) ⇒ Object 



20
21
22
23
24
25
26
27
28
29
 
# File 'lib/comcalc/db.rb', line 20

def query_spec(column1='',column2='',column3='', table)
  # check for solution with arbirtraty params
  # *?
  DATABASE.execute("SELECT #{column1}, #{column2}, #{column3} FROM #{table}").each do |x|
    x.each do |y|
      print "#{y} "
    end
    puts "\n"
  end
end

#refactored_get_single_value(column, table, condition, term) ⇒ Object 



36
37
38
 
# File 'lib/comcalc/db.rb', line 36

def refactored_get_single_value(column, table, condition, term)
  DATABASE.execute("SELECT #{column} FROM #{table} where #{condition}=#{term}")
end

#update_balance_involved(id, new_balance) ⇒ Object 



83
84
85
 
# File 'lib/comcalc/db.rb', line 83

def update_balance_involved(id, new_balance)
  DATABASE.execute("update person set balance=balance-#{new_balance} where id=#{id}")
end

#update_balance_payer(id, new_balance) ⇒ Object 



79
80
81
 
# File 'lib/comcalc/db.rb', line 79

def update_balance_payer(id, new_balance)
  DATABASE.execute("update person set balance=balance+#{new_balance} where id=#{id}")
end

#update_market_count(id) ⇒ Object 



75
76
77
 
# File 'lib/comcalc/db.rb', line 75

def update_market_count(id)
  DATABASE.execute("update market set count=count+1 where id=#{id}")
end