paloma blog

NWエンジニアやってます。技術の備忘など。Pythonもちょっと。タイトルは好きなカクテルから。

月次の支払い集計ツールを作る スクリプト化する編

一旦今年前半の集計は完了しましたが、
月次で運用するためにスクリプト化します。

手動でやってた処理はこんなところですね。

  1. 文字コード変換
  2. 定期代、ヘッダ、フッタ削除
  3. INSERT変換ツール実行
  4. INSERT実行
  5. 集計用sql発行

明細はログイン処理とかあるのでまだ手動で落とします。
今回は7月分の支払いを挿入します。
ややこしいですが、CSVファイルは8月のものになります。

処理詳細

1. 文字コード変換

nfkコマンドで一発。
man見たら日本製のツールなんですね。

2. 定期代削除

プラス不要なヘッダ、フッタ削除
shellでは厳しそうなのでツール作ります。

  • shaping.py
import sys

if len(sys.argv) != 3:
    pass
else:
    print(' '.join(['Usage:', 'python', sys.argv[0], 'CSV file']))
    sys.exit()

monthlycsv = sys.argv[1]

with open(monthlycsv, 'r') as f:
    # 行削除のためリスト型で格納
    reader = f.readlines()

with open(monthlycsv, 'w') as f:
    # 不要ヘッダ、フッタ削除
    for line in reader[2:-3]:
      if '21340' in line:
          pass
      else:
          f.write(line)
3. INSERTファイル作成

前回のINSERT変換用スクリプトを使います。
前回はCSVファイルべた書きでしたが、引数から読むように修正しました。
コードは割愛。

4. INSERT実行

前回のコマンドの通り。
3を実行するだけです。

5. 集計SQL発行

前回のコマンドの通り。

それでは各処理をスクリプト化します。

上記をまとめたシェル

  • insertmonth.sh
#!/bin/bash
# $1 は月のcsvファイル

# 文字コード変換
nkf -w --overwrite $1

# 不要ヘッダ、フッタ削除
python3 shaping.py $1

# 固定費挿入
# 先月分最終日に挿入のためー1する
lastmonth=0$(($(echo $1 | cut -c 6-6)-1))

echo $(date +"%Y/%m/%d" -d"`date +"%Y${lastmonth}01"` 1day ago + 1 month"),家賃,61000 >> $1
echo $(date +"%Y/%m/%d" -d"`date +"%Y${lastmonth}01"` 1day ago + 1 month"),光熱費,10000 >> $1

# インサート文作成
python3 insertsql.py $1

# インサート実行
sqlite3 Credit_expenses_2019.db <  $1".sql"

# 集計ファイル作成
sqlfile="syukei"${lastmonth}".sql"

# フォーマットをコピー
cat syukei01.sql | sed "s/2019\/[0-9][0-9]/2019\/${lastmonth}/g" > $sqlfile

# セレクト処理実行
sqlite3 Credit_expenses_2019.db < $sqlfile

引数まみれのスクリプトになってしまいました。
可変でやろうとしているから仕方がないですが、
変数に入れる等してもう少し綺麗になる気もする...

7月分をINSERTしてみましょう

くどいですが、「7月分の支払い結果」なので8月のファイルになります。
(私の使っているカード会社の仕様です)

masashi@DESKTOP-N095TND:~/expenses$ bash insertmonth.sh 201908.csv
INSERT INTO expenses VALUES('2019/06/30','ヤフージャパン','498');
INSERT INTO expenses VALUES('2019/07/01','J:COM サービス利用料','5431');
INSERT INTO expenses VALUES('2019/07/02','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/02','イトーヨーカドー','1780');
INSERT INTO expenses VALUES('2019/07/04','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/05','イトーヨーカドー 食品','2308');
INSERT INTO expenses VALUES('2019/07/06','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/07','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/09','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/11','06月分 au電話利用料','3049');
INSERT INTO expenses VALUES('2019/07/11','ミーナ津田沼','3175');
INSERT INTO expenses VALUES('2019/07/12','イオンリテール','3184');
INSERT INTO expenses VALUES('2019/07/14','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/15','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/17','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/17','イトーヨーカドー 食品','2168');
INSERT INTO expenses VALUES('2019/07/19','JR東日本モバイルSuica','3000');
INSERT INTO expenses VALUES('2019/07/19','JR東日本みどりの窓口(びゅうプラザ)','13210');
INSERT INTO expenses VALUES('2019/07/21','JR東海','13100');
INSERT INTO expenses VALUES('2019/07/21','戸田家','1750');
INSERT INTO expenses VALUES('2019/07/21','NETFLIX.COM','1296');
INSERT INTO expenses VALUES('2019/07/22','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/24','イトーヨーカドー 食品','2199');
INSERT INTO expenses VALUES('2019/07/25','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/26','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/28','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/29','JR西日本 5489サービス','16350');
INSERT INTO expenses VALUES('2019/07/29','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/30','イトーヨーカドー 食品','2472');
INSERT INTO expenses VALUES('2019/07/01','DIGITALOCEAN.COM (DIGITALOCEAN.)','118');
INSERT INTO expenses VALUES('2019/07/11','CROP (0570001832 )','2142');
INSERT INTO expenses VALUES('2019/07/31','家賃','61000');
INSERT INTO expenses VALUES('2019/07/31','光熱費','10000');
決済内容        合計
----------  ----------
SUICA       29000
その他         65959
住宅費         71000
通信費         1414
食費          6359
-           173732
masashi@DESKTOP-N095TND:~/expenses$

OKですね!
集計結果まで出力してくれて楽ちんです。
7月は旅行行ったり夏季休暇用の切符買ったりして気持ち高くつきましたね。

途中の処理について

INSERT文がちゃんと変換されて出ているのでうまくいっていますが、
(出力するようにしておいてよかった)
一応ファイル確認します。

  • 元のファイル

SJISなのでWSL上では文字化けしています。

masashi@DESKTOP-N095TND:~/expenses$ head 201908.csv
؁@u@l,4980-0594-4494-3***,rlabb`qcNVbN
2019/06/30,C^[lbgCjVAeBu,1798,,,,
2019/06/30,t[Wp,498,,,,
2019/07/01,iFbnl@T[rXp,5431,,,,
2019/07/02,iq{oCr,2000,,,,
2019/07/02,Cg[[Jh[,1780,,,,
2019/07/04,iq{oCr,2000,,,,
2019/07/05,Cg[[Jh[@Hi,2308,,,,
2019/07/06,iq{oCr,2000,,,,
2019/07/07,iq{oCr,2000,,,,
masashi@DESKTOP-N095TND:~/expenses$

1行目が消えて日本語で確認できるようになっています。

masashi@DESKTOP-N095TND:~/expenses$ head 201908.csv
2019/06/30,ヤフージャパン,498,,,,
2019/07/01,J:COM サービス利用料,5431,,,,
2019/07/02,JR東日本モバイルSuica,2000,,,,
2019/07/02,イトーヨーカドー,1780,,,,
2019/07/04,JR東日本モバイルSuica,2000,,,,
2019/07/05,イトーヨーカドー 食品,2308,,,,
2019/07/06,JR東日本モバイルSuica,2000,,,,
2019/07/07,JR東日本モバイルSuica,2000,,,,
2019/07/09,JR東日本モバイルSuica,2000,,,,
2019/07/10,JR東日本モバイルSuica,21340,,,,

まとめ

ブログ上は1発でいったように書きましたが、
細かな処理をちょろちょろやっているので単体ごとの試験が大変でした。

8月分ファイルをダウンロードしたら、windows で落としたファイルがWSLで見えず、
しばらく頓挫していました。(前回は見えていたんですが、Win updateとか?)
結局WSL側のマウント領域からコピーして対応できました。

あと集計SQLファイルは先月分の支払いを集計するので
固定費の挿入月とCSVファイルの指定が混ざって混乱してしまい、
いろいろチューニングしました。
無理やり1ファイルでやろうとしたからなのですが、
おとなしくファイル分ければよかったですね。

また、出力はほとんどノーカットで書いたので、生活垂れ流しで恥ずかしいですw

あとは年末か年明けに今年の総決算をしたいですね。
(お給料少ないので質素に暮らしてるのがばれちゃう)

残りの作業としてはDBのバックアップをどうするか考えつつ
スクリプトリファクタリングをしていこうと思います。

今月から増税で定期代が変わったのでshaping.pyはいじらないといけませんねw

追記

ここまで書いてて気づきましたが、通信費の合計が安すぎておかしいですね。
原因を追究しますが、合計があっていればしばらくは良しとします…。

参考サイト

Windows Subsystem for Linux(WSL)でWindowsのファイルにアクセス - lisz-works

  • 本シリーズ

月次の支払い集計ツールを作る SQL準備編 - paloma blog

月次の支払い集計ツールを作る 2019前半を集計する編 - paloma blog