スマレジからデータ取り出し

palpan開発部

娘のパン屋さんは無事開業を迎えて、ほぼ順調な滑り出しです。

来年の消費税引き上げで、小売業では軽減税率が適用される見通しです。パン屋さんも販売だけなら8%の軽減、イートインのある店舗での飲食をすれば10%となるようで、複雑な仕組みになっています。この軽減税率対応のレジには「軽減税率対策補助金」が設定されており、対策（購入）費用の2/3から1/2の補助が受けられます。専用ソフトを搭載したモバイル端末なら1/2、レシートプリンタなら1/3の価格で買えるわけですので、これを利用しない手はありません。

いくら補助金が付いてもスーパーにあるようなPOSレジは高価なので、iPadとレシートプリンタおよび軽減税率対応ソフトウェアを導入しました。軽減税率対応レジソフトウェアを自分で作ってもダメで、指定ベンダ製のアプリケーションが補助の対象となります。で、我が家ではスマレジを導入することにしました。レジとしては、最低レシート発行と売上記録ができればよいので、無償のスタンダードプランです。無料でも実際に店舗で使うのには全然問題なく、店舗が増えるようならグレードを上げてもPOSレジより安くつきます。

さて、「レシート発行と売上記録ができればよい」とは書きましたが、せっかくデータ化するのであれば仕入れや支払いなどもパソコンで処理したくなります。また、Webでの商品紹介にもデータが必要なので、現状のままでは2重管理になりユーザに最も嫌われる姿です。そこで、スマレジとのデータ連携ができないかと機能を探っていたら、バックアップという機能があり自分のPOSレジデータをダウンロードすることができます。バックアップデータはメールの添付となっているので、少々気味悪い気がしますが仕方無いでしょう。企業の方はマネしないでくださいってところでしょうか。

取得したデータはsqliteデータベースでした。他所様で設計したデータベーススキーマを解説する訳にはいきませんので、ここではデータの取り出し方だけに絞ります。取得したデータは次の場所に保存しています。

$ mkdir -p ~/smageri/pos
$ cp pos.sqlite ~/smageri/pos/
$ cd ~/smaregi

欲しいのは売上データです。売上データはTransactionHeadテーブルとTransactionDetailに分割して格納されています。TransactionHeadテーブルには販売日時、総額、値引き額、税額などがあります。また、TransactionDetailテーブルには商品、価格、商品分類、税抜価格、税額などがあります。商品マスターはProductテーブルとして存在し、そのProductIdがTransactionDetailに関連づけられていますが、TransactionDetailにはProductNameとして商品名も記録されており、これは正規化されていないのではないかと思いました。しかし、よく考えるとTransactionDetailに記録した時点でレシートとして印刷されていますので、後から商品マスタを書き換えるとユーザが持っている証憑と合わせられなくなることは一大事です。こういったトランザクションデータは、販売時点の記録をしっかり残しておくのがよいのでしょう。

データの取り出しには、次のようなsqlファイルを作成しました。

TransactionHeadテーブル

$ vi head.sql

.mode csv
select transactionHeadId,transactionDateTime,subtotal,discountPrice,total,tax from TransactionHead;

TransactionDetailテーブル

$ vi detail.sql

.mode csv
select TransactionHeadId,TransactionDetailId,ProductId,ProductName,Price,quantity from TransactionDetail;

これをコマンドラインで実行します。

$ sqlite3 pos/pos.sqlite < head.sql > ./head.csv
$ sqlite3 pos/pos.sqlite < detail.sql > ./detail.csv

実行結果は次のようになりました。

$ vi head.csv
(省略) 
210,"2018-12-08 16:51:19",680.0,68.0,612.0,45.0
211,"2018-12-08 16:52:04",300.0,,300.0,22.0
212,"2018-12-08 16:55:52",830.0,,830.0,61.0
213,"2018-12-08 17:12:53",460.0,,460.0,34.0
214,"2018-12-09 10:51:16",3350.0,,3350.0,248.0
215,"2018-12-09 11:03:06",2180.0,,2180.0,161.0
216,"2018-12-09 11:04:21",860.0,,860.0,63.0
217,"2018-12-09 11:05:41",760.0,,760.0,56.0
(省略)

$ vi detail.csv
(省略)
84,1,93,"バタール",280.0,1.0
84,2,94,"フィセル",150.0,1.0
84,3,98,"ベーコンエピ",210.0,1.0
84,4,110,"シュトレーン風ブレッド",200.0,2.0
84,6,113,"チリコンカン",220.0,1.0
84,7,114,"ウインナーパン",200.0,1.0
85,1,97,"ガーリックフランス",220.0,3.0
85,2,120,"クッキー 5個入り",430.0,1.0
85,3,117,"ラスク プレーン",150.0,1.0
85,4,110,"シュトレーン風ブレッド",200.0,2.0
85,5,113,"チリコンカン",220.0,2.0
(省略)

出来上がったcsvファイルを自分のデータベースに読み込めば、色々応用ができそうです。先日作成したOpenWeatherMapで天候情報を定時記録していますので、次は需要予測に挑戦です。


    

     2018-12-07 09:00:01 
     1852899 
     曇り               
         11 
           71 
         1023 
    

    

     2018-12-07 12:00:03 
     1852899 
     雨まじりの雪       
      10.67 
           66 
         1024 
    

    

     2018-12-07 15:00:01 
     1852899 
     曇り               
       9.47 
           61 
         1024 
    

    

     2018-12-07 18:00:01 
     1852899 
     曇り               
          7 
           65 
         1025 
    

    

     2018-12-07 21:00:02 
     1852899 
     曇り               
       5.47 
           60 
         1026 
    

    

     2018-12-08 00:00:01 
     1852899 
     曇り               
          5 
           65 
         1027 
    

    

     2018-12-08 03:00:01 
     1852899 
     曇り               
          5 
           65 
         1028 
    

    

     2018-12-08 06:00:01 
     1852899 
     本曇り             
       8.11 
          100 
         1036 
    

    

     2018-12-08 09:00:01 
     1852899 
     曇り               
          4 
           60 
         1029 
    

    

     2018-12-08 12:00:01 
     1852899 
     曇り               
          6 
           52 
         1029

生データではなんなので、もう少し粉飾してから書くことにします。