Life is Really Short, Have Your Life!!

ござ先輩の主に技術的なメモ

Dockerのnginx-proxyでSSL化対応

何番煎じかわかりませんが。あっさり出来たのでメモ。

nginx-proxy 側の docker-compose.yaml

version: '3'

services:
  rg-proxy:
    image: jwilder/nginx-proxy:latest
    container_name: rg-proxy
    privileged: true
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./certs:/etc/nginx/certs:ro
      - ./vhost:/etc/nginx/vhost.d
      - /var/run/docker.sock:/tmp/docker.sock:ro
      - ./html:/usr/share/nginx/html
    restart: always
    environment:
      TZ: Asia/Tokyo
  letsencrypt-nginx:
    image: jrcs/letsencrypt-nginx-proxy-companion
    container_name: letsencrypt-nginx
    privileged: true
    volumes:
      - ./certs:/etc/nginx/certs:rw
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - ./vhost:/etc/nginx/vhost.d
      - ./html:/usr/share/nginx/html
    environment:
      NGINX_PROXY_CONTAINER: rg-proxy
    restart: always

networks:
  default:
    external:
      name: sample_network

ポイントは、Let's Encrypt側の環境変数「NGINX_PROXY_CONTAINER: rg-proxy」です。volumes_from の例が多かったですが、Ver3から非推奨になりました。ログを見たらこの環境変数を設定してコンテナ名称を入れないとダメというエラーが出たので、設定したら動きました。

アプリ側のコンテナ

下記3つの環境変数をセットすれば動くようです。

  • VIRTUAL_HOST
  • LETSENCRYPT_HOST
  • LETSENCRYPT_EMAIL

IT企画をちゃんとやりたい勉強会

っていうのをやろうとしているんだけど・・・

プロジェクトの話を載せていくとNDA的にデンジャラスだし、講義っぽくなると「オレの方法論はこれだぜベイビーロック・ミー・ベイビー」みたいな感じになって寒くなる。

要件定義系はどうしてもメソドロジーの勉強会要素が強くなってしまうけど、いい感じに出せる事例を持ってきて上手いこと出来ないかな。

思案中。

Dockerコンテナでリバースプロキシ

github.com

nginx-proxyを使った構成

nginxはリバースプロキシの1つだけにして、後はサービスのコンテナを起動してリバースプロキシ側で色々頑張ろうと思ったのですが、nginx-proxyのDockerイメージも各々のWebアプリのNginx設定に対応しきれていないようなので...

こういう感じで、nginx-proxyには単純にバーチャルホストベースで割り振ってもらうだけにして、「nginx-proxy」⇔「nginx⇔各アプリのサービス」という仕組みにすると簡単にできました。この方式ならば、各々のWebアプリコンテナの稼働が確認できれば、後はnetworkとvirtualhostに追加するだけなので、結構柔軟です。

各コンテナごとに専用のnginxを立てるのは無駄な気もしなくてはないですが、取りあえずはこれで。はい。

本家のnginx-proxyのfastsgi設定が怪しいかも

github.com

VIRTUAL_PROTO=fastcgi を設定するとfastcgi用の設定ファイルがnginx-proxyコンテナに書かれるんですが、上記のコメントにあるように「include fastcgi.conf」で指定しているfastcgi.confがありません。多分fastcgi_paramsのtypoだと思われます。

売上を顧客の締日で集計するSQLを簡単に書く方法

SQLそれ自体は一番最後にあります。

detail.chiebukuro.yahoo.co.jp

天才的なソリューションだった。驚いた。日付から締日を引き算すれば、何月締めであるかを判定できるとは。シカクいアタマをマルくする必要性を痛感した。

締日集計のめんどいところは、締日がN件あること。5日、10日、15日、20日、25日、月末の6パターンある場合、2017年12月の締日別の合計は以下の期間を元に算出する必要がある。

2017年12月の締日別集計期間

締日 集計期間
5日 2017/11/06〜2017/12/05
10日 2017/11/11〜2017/12/10
15日 2017/11/16〜2017/12/15
20日 2017/11/21〜2017/12/20
25日 2017/11/26〜2017/12/25
月末 2017/12/01〜2017/12/31

当たり前だが、月末の末日は変化する。28、29、30、31の4通りある。締日別で売上を見たいというのは、上記の集計期間別に売上総額を見て、入金予定の算段を立てる目安として使われる事が多い。

この締日別の集計期間をどう作るべきか悩んでいた。最低最悪のソリューションだと締日別にSQLを投げてUNIONするのはありえへんし、CASE-WHENで締日を見てクロス集計するのも無駄に複雑でだるい。そこに彗星の如く現れたのが「日付から締日を引き算」するというSQLだ。実際にやってみた。

伝票日付から締日を引くSQL(サンプル)

select
	customers.duedate,
	earnings.billdate,
	(earnings.billdate - interval customers.duedate DAY) as monthly
from
	earnings
	JOIN customers ON customers.id = earnings.customer_id
where
	year(earnings.billdate) = 2017
group by
	earnings.billdate,customers.duedate

MySQLの場合、日付型のデータにおいてはINTERVALを与えると引き算(足し算)してくれる。実際に引いてみたら、たしかにこれで集計できることがわかった。あとは簡単。DATE_FORMATで年月を割り出し、そいつでGroupByするだけだ。

締日別売上集計SQL

select
	duedate,
	DATE_FORMAT((billdate - interval duedate DAY),'%Y年%m月') as monthly,
	sum(subtotal)
from
	earnings
	JOIN customers ON customers.id = earnings.customer_id
group by
	monthly,duedate
order by
	monthly,duedate

手元のMacで8万行の売上データが507msで集計できた。

追記(2017.12.26 17:30)

duedate(締日)に31が登録されている場合(うちのシステムは、末締めの場合31という数値を入力して表現することになっている)月末が31日じゃないものについてはバグになってしまう。11/30から31を引いたら、10/30になってしまい11月伝票が10月伝票になってしまうことに気がついたので、このSQLはクソ。駄目です。

というか、末締めの場合は締日の引き算が不要。その日付の年月が締め対象になる。というわけで、そこをCASE-WHENで場合分けすればOKだった。以下のSQLなら大丈夫なはず。

select
	duedate,
	case 
	  when duedate = 31 then DATE_FORMAT(billdate,'%Y年%m月')
	  else DATE_FORMAT((billdate - interval duedate DAY),'%Y年%m月') 
	end as monthly,
	sum(subtotal)
from
	earnings
	JOIN customers ON customers.id = earnings.customer_id
group by
	monthly,duedate
order by
	monthly,duedate

追記(2018.03.23 14:40)

バグが有ることが判明しました。

20日締で5/25付の顧客は6月付にならなければなりません。5/21〜6/20 が期間なので。でも、5/25 で 締日が20日の場合、単純に日付から締日を引き算してしまうと、5/5になり、5月付になってしまいます。5/25は6月付やん。アカンわ。

「日付の日にち」>「締日」だったら日付の年月を1ヶ月加算、「日付の日にち」<=「締日」はその日付の年月を請求月とする。これが正しいようです。末締の場合は何も加算する必要が無いので注意が必要です。

締日5日で検証
締日 日付 請求月
5日(2017/10/06〜2017/11/5) 2017/10/25 11月締
5日(2017/10/06〜2017/11/5) 2017/10/4 10月締
締日10日で検証
締日 日付 請求月
10日(2017/10/11〜2017/11/10) 2017/10/9 10月締
10日(2017/10/11〜2017/11/10) 2017/10/31 11月締
締日15日で検証
締日 日付 請求月
15日(2017/10/16〜2017/11/15) 2017/10/9 10月締
15日(2017/10/16〜2017/11/15) 2017/10/31 11月締
締日20日で検証
締日 日付 請求月
20日(2017/10/21〜2017/11/20) 2017/10/9 10月締
20日(2017/10/21〜2017/11/20) 2017/10/24 11月締
締日25日で検証
締日 日付 請求月
25日(2017/10/26〜2017/11/25) 2017/11/10 11月締
25日(2017/10/26〜2017/11/20) 2017/11/28 12月締

良さそうですね。締日よりも先の日付(締日は含まない)だったら、1ヶ月先が請求月。正しい気がします。

というわけで、締日集計のSQLの改訂版です。

SELECT
	CASE 
	  WHEN duedate = 31 THEN DATE_FORMAT(billdate,'%Y年%m月')
	  WHEN duedate < 31 AND DAY(billdate) <= duedate THEN DATE_FORMAT(billdate,'%Y年%m月')
	  WHEN duedate < 31 AND DAY(billdate) > duedate THEN DATE_FORMAT(DATE_ADD(billdate, INTERVAL 1 MONTH),'%Y年%m月')
	END AS monthly,
	duedate,
	sum(subtotal)
FROM
	earnings
	JOIN customers ON customers.id = earnings.customer_id
WHERE
	duedate > 0
GROUP BY
	monthly,duedate
ORDER BY
	monthly,duedate

念のため、締日での請求月の算出動作確認用SQLも貼り付けておきます。

SELECT
	billdate, -- 伝票日付
	duedate, -- 顧客の締日(1〜31)
	CASE 
	  WHEN duedate = 31 THEN DATE_FORMAT(billdate,'%Y年%m月') -- 末締は伝票月=請求月
	  WHEN duedate < 31 AND DAY(billdate) <= duedate THEN DATE_FORMAT(billdate,'%Y年%m月') -- 末締以外で、伝票日 <= 締日なら、伝票月=請求月
	  WHEN duedate < 31 AND DAY(billdate) > duedate THEN DATE_FORMAT(DATE_ADD(billdate, INTERVAL 1 MONTH),'%Y年%m月')  -- 末締以外で、伝票日 > 締日なら、伝票月=請求月の翌月
	END AS monthly
FROM
	earnings
	JOIN customers ON customers.id = earnings.customer_id
WHERE
	duedate > 0
GROUP BY
	billdate,duedate

SQLを学習できるWebサービスを作りました。

www.start-sql.net

2017年度の振り返り

自分一人が食っていく分には困らないけど、事業を作っていくのは本当に大変だなと改めて実感。サービスを販売して軌道に乗せるためには、年商1000万は無いとだめ。年間100万で売るなら10本、年間1万円にするなら100本売らないと販管費や広告宣伝費等を含めるとペイしない。ここまで育て上げるのに使うお金は、1000万では効かない。自分の人件費等を含めたら。

業務委託で自分のスキルを売るのが、手っ取り早く売上になって定期的にお金が入ってくるので、すごく楽です。僕自身も(かなり自由にやらせて頂いてますが)お世話になっている。スキルがあれば買ってくれる人は、どこかしらにいる。スキルの切り売り感は個人的には全く無いし、何かしら新しい発見があるよね、問題意識さえ持っていれば。いつまでもこのままお世話になるのは嫌だけど、生命維持装置のようなもので、なかなか外すことが困難。

サービスを販売するための原資や時間を捻出する為に、自分のリソースを売りながらチャンスを掴もうとしている。ニーズがない商品を作ってもしょうがないので、ニーズを探すために色んな所へ顔を出した。

チャンスの女神の前髪が見えてきたのが、2017年度のハイライト。SIビジネスを変えうるお話を頂いたし、昔から諦めきれない販売管理でも多くの引き合いを頂いた。2018年はシーズを育ててソリューションへと結実させて新しいステージへと進む。

前途洋々、頑張れおれ。

Androidでバーコードリーダーを作る

Andoridでは、だいたい2年ぐらい前からGoogle公式でバーコードをカメラで解析するAPIが搭載されています。

Barcode Detection with the Mobile Vision API

このAPIがクセがあって「カメラに写っているバーコード全てを認識できる」というものになっています。それはそれですごいのだけれども、「ここが読取り範囲だよ」という範囲を指定して単一のバーコードしか読まないようにする事が出来ない。

そこを制御してくれるのが、下記のGitHubのライブラリでした。導入すごい簡単でした。
github.com

Android6.0以上から、実行時にカメラへのアクセス許可が必要になった。そのハンドリングをするコードを書かないと動かないので、ご注意を。