class CodeRunner::Budget

Constants

DATABASES

Public Class Methods

init_sqlite(folder) click to toggle source

Initialize the sqlite database that stores the user choices for external_account and sub_account

# File lib/treasurer/account_choices.rb, line 6
  def self.init_sqlite(folder)
    require 'sqlite3'
    FileUtils.makedirs(folder + '/sqlite')
    Dir.chdir(folder + '/sqlite') do 
      db = sqlitedb(Dir.pwd) 
      _rows = db.execute <<-SQL
        create table external_accounts (
          eid INTEGER primary key,
          external_account text
        );
      SQL
      _rows = db.execute <<-SQL
        create table sub_accounts (
          sid INTEGER primary key,
          sub_account text,
          eid INTEGER,
          foreign key (eid) references external_accounts (eid)
        );
      SQL
      _rows = db.execute <<-SQL
        create table choices (
          id INTEGER primary key,
          signature text,
          sid INTEGER,
          foreign key (sid) references sub_accounts (sid)
        );
      SQL

    end
  end
sqlitedb(folder) click to toggle source
# File lib/treasurer/account_choices.rb, line 39
def self.sqlitedb(folder)
  DATABASES[folder] ||= SQLite3::Database.new folder + "/treasurer.db"
end

Public Instance Methods

add_sqlite_choices(chosen) click to toggle source
# File lib/treasurer/account_choices.rb, line 126
def add_sqlite_choices(chosen)
  sid = sqlite_sid(chosen)
  sqlitedb.execute(
    'INSERT INTO choices' + 
    '(signature, sid) VALUES (?, ?)', 
    [signature.inspect, sid]
  )
end
external_account() click to toggle source

All transactions occur between two accounts. One of those accounts is always physical (e.g. bank account, loan, credit card) and the other can be either physical or a virtual account of the users choice, e.g. Food or Petrol or Energy, or maybe WeeklyBudget and LongTermBudget etc.

# File lib/treasurer/account_choices.rb, line 303
def external_account
  unless @runner
    raise "No runner for " + data_line
  end
  if not @external_account
    @external_account = (
      (ch = get_sqlite_choices)[:external_account] or
      (ch = get_old_choices)[:external_account] or
      (ch = get_new_choices)[:external_account]
    )
    @sub_account = ch[:sub_account]
    #if not @external_account
      #raise "No external account for #{data_line}"
    #end
  end
  return @external_account
end
get_new_choices() click to toggle source

Get new choices from the user interactively.

# File lib/treasurer/account_choices.rb, line 161
def get_new_choices
  ext_account = nil
  chosen = false
  transactions = runner.component_run_list.values.sort_by{|r| r.date}
  idx = transactions.index(self)
  ff = Proc.new{|float| float ? sprintf("%8.2f", float) : " "*8}
  format = Proc.new{|runs| runs.map{|r| 
    #begin
      sprintf("%70s %s %8s %8s %8s %-12s", r.description[0,70], r.date.to_s, ff.call(r.deposit), ff.call(r.withdrawal), ff.call(r.balance), r.account)
    #rescue
      #p r
      #p r.data_line
      #exit
    #end
  }.join("\n")}
  format_choices = Proc.new{|chs| chs.map{|k,v| Terminal::LIGHT_GREEN + k + ":" + Terminal.default_colour + v}.join(" ")}
  Dir.chdir(@runner.root_folder) do
    sym = nil
    print_transactions = Proc.new do
          puts format.call(transactions.slice([idx-30, 0].max, 30))
          puts Terminal::LIGHT_GREEN + format.call([transactions[idx]]) + "<-----" + Terminal.default_colour
          sz = transactions.size
          puts format.call(transactions.slice([idx+1, sz-1].min, 10))
    end
    while not chosen
      Hash.phoenix('external_accounts.rb') do |account_hash|
        #account_hash.each{|k,v| v[:name] = v[:name].to_sym} #Fixes an earlier bug
        #choices = account_arr.size.times.map{|i| [i,account_arr[i][:name]]}
        choices = account_hash.map{|k,v| [v[:sym], k]}.to_h
        choices["-"] = "Transfer"
        puts Terminal.default_colour
        print_transactions.call
        #format = Proc.new{|runs| runs.map{|r| r.signature.map{|d| d.to_s}.join(",")}.join("\n")}
        puts
        puts format_choices.call(choices)
        puts
        puts "Please choose from the above external accounts for this transaction." +
          "If you wish to add a new account type 0. To quit type q. " +
              "To start again for this transaction, type z. To mark it as transfer between" +
              "two non-external accounts, press -."
        while not chosen
          require 'io/console'
          choice = STDIN.getch
          if choice == "q"
            throw :quit_data_entry
          elsif choice == "0"
            puts "Please type the name of the new account"
            name = STDIN.gets.chomp.to_sym
            puts "Please enter a symbol to represent this account (e.g. digit, letter, punctuation)."
            sym = false
            until sym
              sym = STDIN.getch
              if choices.keys.include? sym
                puts "This symbol is taken"
                sym = false
              end
            end
            account_hash[name] = {name: name, sym: sym, sub_accounts: {}}
            #choices_hash[@id] = name
            chosen = name
          elsif choice == "z"
            chosen = false
            break
          elsif choice == "-"
            ext_account = "Transfer"
            chosen = "Transfer"
            break
          elsif not choices.keys.include? choice
            puts "Error: this symbol does not correspond to an account"
          else
            chosen = choices[choice] #account_hash[choice][:name]
          end
        end
        #Hash.phoenix('account_choices.rb') do |choices_hash|
        #choices_hash[data_line] = {external_account: chosen}
        #end
      end
      next if not chosen
      if ext_account and chosen
          break
      end
      ext_account = chosen
      chosen = false
      Hash.phoenix('external_accounts.rb') do |account_hash|
        #choices = account_arr.size.times.map{|i| [i,account_arr[i][:name]]}
        sub_accounts = account_hash[ext_account][:sub_accounts]
        sub_accounts.each{|k,v| v[:name] = v[:name].to_sym} #Fixes an earlier bug
        choices = sub_accounts.map{|k,v| [v[:sym], k]}.to_h
        #puts "-" * data_line.size
        print_transactions.call
        puts
        puts format_choices.call(choices)
        puts
        puts "Please choose from the above sub-accounts for this transaction. " + 
          "If you wish to add a new sub account, type 0. To quit, type q. " +
          "To start again for this transaction, type z"
        while not chosen
          require 'io/console'
          choice = STDIN.getch
          if choice == "q"
            throw :quit_data_entry
          elsif choice == "0"
            puts "Please type the name of the new sub account"
            name = STDIN.gets.chomp.to_sym
            puts "Please enter a symbol to represent this account (e.g. digit, letter, punctuation)."
            sym = false
            until sym
              sym = STDIN.getch
              if choices.keys.include? sym
                puts "This symbol is taken"
                sym = false
              end
            end
            sub_accounts[name] = {name: name, sym: sym}
            #choices_hash[@id] = name
            chosen = name
          elsif choice == "z"
            chosen = false
            ext_account = nil
            break
          elsif not choices.keys.include? choice
            puts "Error: this symbol does not correspond to a sub-account"
          else
            chosen = choices[choice] #sub_accounts[choice][:name]
          end
        end
      end
      next if not chosen
      #Hash.phoenix('account_choices.rb') do |choices_hash|
        #choices_hash[signature] = {external_account: ext_account, sub_account: chosen}
      #end
    end #while not chosen
    add_sqlite_choices({external_account: ext_account, sub_account: chosen})
  end
  {external_account: ext_account, sub_account: chosen}
end
get_old_choices() click to toggle source

Get stored choices from the old flat files

# File lib/treasurer/account_choices.rb, line 48
def get_old_choices
  chosen = false
  Hash.phoenix(@runner.root_folder + '/account_choices.rb') do |choices_hash|
    if choices_hash[signature]
      chosen = choices_hash[signature]
    elsif choices_hash[data_line] 
      #choices_hash[data_line][:external_account] =
      #choices_hash[data_line][:external_account].to_sym #fixes earlier bug
      #choices_hash[data_line][:sub_account] =
      #choices_hash[data_line][:sub_account].to_sym #fixes earlier bug
      chosen = choices_hash[data_line]
      choices_hash[signature] = choices_hash[data_line]
      choices_hash.delete(data_line)
    elsif choices_hash[old_data_line]
      chosen = choices_hash[old_data_line]
      choices_hash[signature] = choices_hash[old_data_line]
    end
  end
  if chosen
    puts "ADDING TO DB"
    add_sqlite_choices(chosen)
  end
  chosen||{}
end
get_sqlite_choices() click to toggle source

Get stored choices from the sqlite database

# File lib/treasurer/account_choices.rb, line 136
def get_sqlite_choices
  rows = sqlitedb.execute(
    'SELECT external_account, sub_account ' +
    'FROM choices ' +
    'LEFT JOIN sub_accounts  ON ' +
    'sub_accounts.sid = choices.sid '  +
    'LEFT JOIN external_accounts  ON ' +
    'external_accounts.eid = sub_accounts.eid ' +
    'WHERE signature = ?', 
    signature.inspect
  )
  #pp "RRRRROOO", rows
  if rows.size == 1
    return {
      external_account: eval(rows[0][0]),
      sub_account: eval(rows[0][1]),
    }
  elsif rows.size > 1
    raise "Duplicate signatures in sqlitedb"
  else
    return {}
  end
end
sqlite_eid(account_spec) click to toggle source
# File lib/treasurer/account_choices.rb, line 73
def sqlite_eid(account_spec)
  eid = nil
  until eid
    rows = sqlitedb.execute(
      'SELECT (eid) ' +
      'FROM external_accounts ' +
      'WHERE external_account = ? ', 
      account_spec[:external_account].inspect
    )
    pp 'ROWSSS', rows
    if rows.size < 1
      sqlitedb.execute(
        'INSERT INTO external_accounts  ' + 
        '(external_account) VALUES (?)', 
        account_spec[:external_account].inspect
      )
    elsif rows.size > 1
      raise "Duplicate external_accounts"
    else
      eid=rows[0][0]
      raise TypeError.new("Bad eid: #{eid.inspect}") unless eid.kind_of? Integer 
    end
  end
  eid
end
sqlite_sid(account_spec) click to toggle source
# File lib/treasurer/account_choices.rb, line 99
def sqlite_sid(account_spec)
  eid = sqlite_eid(account_spec)
  sid = nil
  until sid
    rows = sqlitedb.execute(
      'SELECT (sid) ' +
      'FROM sub_accounts ' +
      'WHERE sub_account = ? AND eid= ?', 
      [account_spec[:sub_account].inspect, eid]
    )
    pp 'ROWSSS', rows
    if rows.size < 1
      sqlitedb.execute(
        'INSERT INTO sub_accounts  ' + 
        '(sub_account, eid) VALUES (?, ?)', 
        [account_spec[:sub_account].inspect, eid]
      )
    elsif rows.size > 1
      raise "Duplicate sub_accounts"
    else
      sid=rows[0][0]
      raise TypeError.new("Bad sid: #{sid.inspect}") unless sid.kind_of? Integer 
    end
  end
  sid
end
sqlitedb() click to toggle source
# File lib/treasurer/account_choices.rb, line 43
def sqlitedb
  self.class.sqlitedb(@runner.root_folder + '/sqlite')
end
sub_account() click to toggle source

All transactions have a subaccount. For transactions between physical accounts this will almost always be just 'Transfer' or similar, but virtual accounts will have more meaningful sub_accounts, e.g. Food might have Groceries, EatingOut or WeeklyBudget might have Transport, Food, Clothes… depending on the users preferred way of organising things. The sub_accounts are primarily a labelling exercise, but done well can be very helpful in showing a breakdown of expenditure.

# File lib/treasurer/account_choices.rb, line 329
def sub_account
  return @sub_account if @sub_account
  puts "SIGNATURE ", signature.inspect
  Dir.chdir(@runner.root_folder) do
    external_account until (
      choices = nil
      Hash.phoenix('account_choices.rb'){|choices_hash| choices = choices_hash[signature]}
      #p [choices, data_line]
      choices
    ) and choices[:sub_account]
    @sub_account = choices[:sub_account]
  end
end