小槌へ移行（コード）

先日書いた、家計簿の移行に使ったコード。

エクスポートしたデータからdealsテーブルへインポートするデータを抽出しTSVで出力する

# TSV format
# 1.type ("Deal")
# 2.user_id (2)
# 3.date
# 4.daily_seq
# 5.summary
#

pre_date  = ''
daily_seq = 0

ARGF.each do |line|
    items = ['Deal', '2']

    rows   = line.chomp.split(/\t/)
    date    = rows[0]
    summary = rows[1]
    code    = rows[2]
    plus    = rows[3]
    minus   = rows[4]

    if summary.size <= 0
        next
    end

    date.gsub!(/(\d{4})(\d{2})(\d{2})/, '\1-\2-\3')
    
    if date == pre_date
        daily_seq += 1
    else
        daily_seq = 1
        pre_date = date
    end

    items << date
    items << daily_seq
    items << summary

    puts items.join("\t")
end

エクスポートしたデータからdealsテーブルへインポートするデータを抽出しTSVで出力する

#
# 1.usr_id (2)
# 3.account_seq
# 3.date
# 4.daily_seq
# 5.ammount
# 6.out
# 7.in
#

conv_entries = {
    '1101' => [41, 23], # 給与
    '1102' => [42, 23], # 賞与
    '1104' => [43, 23], # 雑収入
    '1103' => [43, 23], # 年金
    '1105' => [43, 23], # その他
    '1201' => [23, 24], # 食費
    '1202' => [23, 25], # 住居
    '1203' => [23, 26], # 光熱・水道
    '1204' => [23, 27], # 被服
    '1205' => [23, 28], # 保健・医療
    '1206' => [23, 39], # 教育
    '1207' => [23, 33], # 教養・娯楽
    '1208' => [23, 30], # 交際
    '1209' => [23, 31], # 交通・通信
    '1213' => [23, 37], # その他
    }

pre_date  = ''
daily_seq = 0
account_seq = 9

ARGF.each do |line|
    items = ['2']

    rows    = line.chomp.split(/\t/)
    date    = rows[0]
    summary = rows[1]
    code    = rows[2]
    plus    = rows[4]
    minus   = rows[5]

    date.gsub!(/(\d{4})(\d{2})(\d{2})/, '\1-\2-\3')

    unless conv_entries[code]
        next
    end

    if date == pre_date
        daily_seq += 1
    else
        daily_seq = 1
        pre_date = date
    end

    items << account_seq
    items << date
    items << daily_seq
    items << ((plus.to_i <= 0) ? minus : plus)
    items << conv_entries[code][0] 
    items << conv_entries[code][1] 

    account_seq += 1

    puts items.join("\t")
end

TSVデータを読み込んでdealsテーブルにデータをロードする

db = SQLite3::Database.new('kozuchi_development.db')

sql = <<EOD
INSERT INTO deals
(
    type,
    user_id,
    date,
    daily_seq,
    summary,
    confirmed,
    created_at,
    updated_at
)
VALUES
(
    'Deal',
    2,
    :date,
    :daily_seq,
    :summary,
    't',
    datetime('now', 'localtime'),
    datetime('now', 'localtime')
)
EOD

ARGF.each do |line|
    items = line.chomp.split(/\t/)
    date      = items[2]
    daily_seq = items[3]
    summary   = items[4]
    db.execute(sql, :date => date, :daily_seq => daily_seq, :summary => summary)
end

db.close

TSVデータを読み込んでaccount_entriesテーブルにデータをロードする

require 'sqlite3'

db = SQLite3::Database.new('kozuchi_development.db')

sql = <<EOD
INSERT INTO account_entries 
(user_id, account_id, deal_id, amount, date, daily_seq)
VALUES
(2, :account_id, :deal_id, :amount, :date, :daily_seq)
EOD

ARGF.each do |line|
    items = line.chomp.split(/\t/)

    deal_id     = items[1].to_i
    date        = items[2]
    daily_seq   = items[3].to_i
    amount      = items[4].to_i
    ac_id_minus = items[5].to_i
    ac_id_plus  = items[6].to_i

    db.execute(sql, :account_id => ac_id_minus, :deal_id => deal_id,
               :amount => (amount * -1), :date => date, :daily_seq => daily_seq)
    db.execute(sql, :account_id => ac_id_plus, :deal_id => deal_id,
               :amount => amount, :date => date, :daily_seq => daily_seq)

end

db.close

user_id はSQLに直に書いているので、TSVに出力しなくてもよかった。