さくらIoT x node-RED on Bluemix で会議室の空き状況を表示させるまでの3日間(3日目)

[1日目](http://qiita.com/kkoiwai/items/196cf8136c5a633493c0) では、IBM BluemixにNode-REDを導入し、WebSocketでデータを受信して、Ambientというグラフ表示サービスにデータを連携するところまで進みました。
[2日目](http://qiita.com/kkoiwai/items/7b311708c42dbdae6aca)では、受信したデータをDBにとりあえず保存するのと、Webサイトを作るところまで行きました。
[2.5日目](http://qiita.com/kkoiwai/items/d058c47ef75101088f93)で、Arduinoを実際に接続してみました。
今回、3日目では、とりあえず保存したデータを読める形にして、利用率を計算するところまで行きたいと思います。


目次

- Cloudantに保存したデータを使って統計を取るための設定
 - Viewの作成
- CloudantからNode-REDでデータを取り出す
 - デフォルトのCloudantノードではviewを使えないぽい
 - APIを直接叩く
 - Bluemixの環境設定を読むための設定
- 取り出したデータを使って利用率を算出する（途中
    - 検索クエリの設定
    - 利用率の算出方法


---

##Cloudantに保存したデータを使って統計を取るための設定
###Viewの作成

Cloudantは、 いわゆるno-SQLのデータベースで、jsonを投げればとりあえず何でも保存してくれます。そこからテーブルのように情報を整形して表示させるには、Viewを作る必要があるようです。Viewというのは、リレーショナルデータベースのViewと同じ概念だと思います。

BluemixでNode-REDを立ち上げた際には、かならずCloudant DBがセットでついてきます。まず、Bluemixのダッシュボードから、「LAUNCH」を押して、Cloudantの設定画面を開きます。
<img width="1081" alt="スクリーンショット 2017-01-06 20.34.19.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/2e61ec47-bf90-0705-f06a-eb8b7473028e.png">

---

「Database」を押して、データベースの一覧を表示すると、2日目に適当に名前をつけたDatabase、「iot_data」が存在していることが分かります。
<img width="610" alt="スクリーンショット 2017-01-06 20.50.37.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/95f99c60-ce30-bfec-d1de-68b48f27e484.png">

そしたら、Design Documents の右の「＋」マークをクリックして、「New　View」を選択します、

<img width="564" alt="スクリーンショット 2017-01-06 20.51.59.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/68a1715f-9075-634b-1195-dda8dda44151.png">

---

こんな感じで、functionを作りました、これもコードを置いておくのでよかったらコピペしてください。
見れば分かるとおり、payloadの中のchannelごとの情報を一つずつ調べ、channel番号とタイムスタンプの配列をKeyにして、Viewに追加しています。さくらIoT Platformの仕様では、1回の送信で16チャンネル分の情報まで送れるので、複数の値が保存されていることがあるので、普通に読もうとすると大変だったりします。そういった場合でも、forEachを使うことで、1つのDocument(Cloudantに保存されたjsonのかたまり）から複数の行をViewに追加することができます。これは便利。
<img width="564" alt="スクリーンショット 2017-01-06 21.17.05.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/7a37ee99-30c8-9ee6-01c7-4678e47a2ed2.png">


```js
function (doc) {
   doc.payload.channels.forEach(function(v) {
      emit([v.channel, doc.datetime], v.value);
  });
}
```

---

これで、下の緑のボタンを押すと、テーブルっぽくデータが見れるようになります。
<img width="683" alt="スクリーンショット 2017-01-06 21.18.53.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/fc7ff44b-5f99-2b9d-61a5-0246a71fe7dc.png">

さぁ、このViewをつかってNode-REDで・・・と行きたいのですが、思ったより簡単ではありませんでした。続きます。

---

##Cloudantからデータを取り出す
###デフォルトのCloudantノードではviewを使えないぽい

Node-REDの画面に戻り、cloudantの（検索のほうの）ノードを追加し、設定を開いてみます。

<img width="502" alt="スクリーンショット 2017-01-06 21.21.42.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/0d55cedd-482d-ae2d-b615-c80249b84df8.png">

あれ・・・ Viewの検索ができない・・・。

いろいろいじってみたのですが、うまくいきませんでした。
そういうときのためのAPIです。直接叩いてデータをもらっちゃいましょう、

---

###APIを直接叩く

Cloudantの中身は、APIで簡単に読むことができます。今回作成したViewは、
https://xxx..（超長いURL）..xx-bluemix.cloudant.com/iot_data/_design/Views/_view/channels
にアクセスすることで取得できます。URLは、BluemixダッシュボードのNode-REDの環境変数から取得できます。

<img width="1063" alt="スクリーンショット 2017-01-07 9.18.16.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/5cf88329-6f95-2078-b487-e820fb2efbcd.png">

---

実際にSafariでアクセスしてみました。URLを入力すると、赤い背景で、URLにパスワードが含まれていますという警告画面が出ます。びっくりしますが、続行しても大丈夫です。

![スクリーンショット 2017-01-06 21.39.50.png](https://qiita-image-store.s3.amazonaws.com/0/135431/7ed9fcaf-4b53-cb72-9238-4a64a3f6df46.png)

---

一旦このURLを直打ちして、結果を表示できるか試してみましょう。
Node-REDで inject→function→http request→debugとノードをつなげて、functionの中で、msg.urlに先ほどのURL, msg.methodにGETを指定します。
これもよかったら下記をコピペしてみてください。

```js
[{"id":"f09115e4.71d7c","type":"inject","z":"396465eb.1d8e22","name":"実行","topic":"","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"x":109,"y":1409,"wires":[["96d60a9c.3ff388"]]},{"id":"96d60a9c.3ff388","type":"function","z":"396465eb.1d8e22","name":"API URL設定","func":"msg.url = \"https://もにゃもにゃ-bluemix:もにゃもにゃ-bluemix.cloudant.com/iot_data/_design/Views/_view/channels\"\nmsg.method = \"GET\";\nreturn msg;","outputs":1,"noerr":0,"x":257.14282417297363,"y":1407.5713195800781,"wires":[["d78c3247.f398b8"]]},{"id":"d78c3247.f398b8","type":"http request","z":"396465eb.1d8e22","name":"","method":"use","ret":"txt","url":"","tls":"","x":429.7142925262451,"y":1408.9996643066406,"wires":[["7dfd3438.b68a94"]]},{"id":"7dfd3438.b68a94","type":"debug","z":"396465eb.1d8e22","name":"","active":true,"console":"false","complete":"payload","x":597,"y":1408,"wires":[]}]
```

<img width="727" alt="スクリーンショット 2017-01-07 9.50.42.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/e4508df1-4439-81e5-cf41-d89562e2b049.png">
<img width="946" alt="スクリーンショット 2017-01-07 9.52.02.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/b4ef7d33-e1c1-47a3-9d33-cb2cc37d529f.png">
実行してみると、ちゃんと結果が返ってきていることが分かります。
<img width="384" alt="スクリーンショット 2017-01-07 9.52.51.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/8738f995-3527-ec59-b53b-67af6e7b7510.png">

---

###Bluemixの環境設定を読むための設定

このままURLを直打ちして使い続けるのもよいのですが、せっかく環境設定に入っているので、再利用しやすいように、環境設定を読めるようにします。
[how do i get at my vcap variables from node-red?](https://developer.ibm.com/answers/questions/170246/how-do-i-get-at-my-vcap-variables-from-node-red.html)によれば、BluemixのNode-REDではデフォルトでは環境変数を参照できないので、 bluemix-settings.js を編集する必要があるとのこと。
なので、はたまたGitHubのレポジトリに戻ります。
下記のように、52行目にある、```functionGlobalContent: { }, ``` のカッコの中に、 ```process: process``` と記入し、commitします。
Node-REDが再起動するまで数分待ちます。

<img width="518" alt="スクリーンショット 2017-01-06 20.44.44.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/9a03fe9a-5ea1-e666-57fe-2ce869da565c.png">

---

Node-REDに戻り、先ほど作成した、functionノードを開き、下記のようにコードを変えます。

<img width="707" alt="スクリーンショット 2017-01-07 12.51.19.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/0388e64d-9553-d882-9b37-819d23e91cb5.png">

```js
bm_env = JSON.parse(context.global.process.env["VCAP_SERVICES"]);
cloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;
msg.url = cloudant_url + "/iot_data/_design/Views/_view/channels"
msg.method = "GET";
return msg;
```

もしくは、下記をインポートしてください。

```js
[{"id":"a249c0d2.4e60d","type":"inject","z":"396465eb.1d8e22","name":"実行","topic":"","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"x":112,"y":1536,"wires":[["9360c52a.d3d39"]]},{"id":"9360c52a.d3d39","type":"function","z":"396465eb.1d8e22","name":"API URL設定","func":"bm_env = JSON.parse(context.global.process.env[\"VCAP_SERVICES\"]);\ncloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;\nmsg.url = cloudant_url + \"/iot_data/_design/Views/_view/channels\"\nmsg.method = \"GET\";\nreturn msg;","outputs":1,"noerr":0,"x":260.14282417297363,"y":1534.5713195800781,"wires":[["43805aab.5905ec"]]},{"id":"43805aab.5905ec","type":"http request","z":"396465eb.1d8e22","name":"","method":"use","ret":"txt","url":"","tls":"","x":432.7142925262451,"y":1535.9996643066406,"wires":[["d35ddabc.6da91"]]},{"id":"d35ddabc.6da91","type":"debug","z":"396465eb.1d8e22","name":"","active":true,"console":"false","complete":"payload","x":600,"y":1535,"wires":[]},{"id":"6c22d320.d18a3c","type":"comment","z":"396465eb.1d8e22","name":"Bluemixの環境設定を参照するバージョン","info":"","x":177,"y":1480,"wires":[]}]
```

先ほどと同じように実行して、debug画面に結果が表示されることを確認します。

<img width="1067" alt="スクリーンショット 2017-01-07 12.54.13.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/ce07f2cb-4f9e-c98d-0a7e-4bf8bdab8170.png">

---

##取り出したデータを使って利用率を算出する

<img width="668" alt="スクリーンショット 2017-01-07 20.21.32.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/1d85b69f-5022-0784-bbc7-a36480744fcc.png">

まずはこちらをコピペしてみてください。

```js
[{"id":"a249c0d2.4e60d","type":"inject","z":"396465eb.1d8e22","name":"実行","topic":"","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"x":112,"y":1536,"wires":[["9360c52a.d3d39"]]},{"id":"9360c52a.d3d39","type":"function","z":"396465eb.1d8e22","name":"API URL設定","func":"bm_env = JSON.parse(context.global.process.env[\"VCAP_SERVICES\"]);\ncloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;\ncloudant_view = \"/iot_data/_design/Views/_view/channels\";\nchannel_id=3;\n\nnow = new Date();\nprev_day = new Date();\nprev_day.setDate(now.getDate() - 1);\nstart_ms=new Date(prev_day.getFullYear(), prev_day.getMonth(), prev_day.getDate(),prev_day.getHours(),0);\nend_ms=new Date(now.getFullYear(), now.getMonth(), now.getDate(),now.getHours(),0);\n\nstart_time =start_ms.toString();\nend_time =end_ms.toString();\nmsg.url = cloudant_url +cloudant_view +\n        '?startkey=['+ channel_id + ',\"' + start_time + '\"]' +\n          '&endkey=['+ channel_id + ',\"' + end_time + '\"]';\nmsg.method = \"GET\";\nmsg.cl_stat = {\n    \"channel_id\":channel_id,\n    \"start_time\":start_time,\n    \"end_time\":end_time\n};\nreturn msg;","outputs":1,"noerr":0,"x":260.14282417297363,"y":1534.5713195800781,"wires":[["43805aab.5905ec"]]},{"id":"43805aab.5905ec","type":"http request","z":"396465eb.1d8e22","name":"","method":"use","ret":"txt","url":"","tls":"","x":432.7142925262451,"y":1535.9996643066406,"wires":[["1e1cf214.6462e6"]]},{"id":"22c110b7.273ce","type":"function","z":"396465eb.1d8e22","name":"統計情報の更新","func":"channel_id=msg.cl_stat.channel_id;\nstart_time=new Date(msg.cl_stat.start_time);\nend_time=new Date(msg.cl_stat.end_time);\n\n\nused_msec = 0;\nlast_closed = null;\n\nmsg.payload.rows.forEach(function( elm ) {\n    // keyに保存された日付をパース\n    dt = new Date(elm.key[1]);\n    \n    if(elm.value===0 && used_msec ===0 && last_closed ===null  ){\n        // ドアが開いた＝利用終了 かつ初エントリ\n        // 利用時間を計算して加算\n        // 初回なのでカウント開始時間（start_time）からの差\n        used_msec = dt - start_time;\n        \n        // 利用時間開始をリセット\n        last_closed = 0;\n        \n    } else if(elm.value===0 && last_closed !== 0 ){\n        // ドアが開いた＝利用終了\n        // 利用時間を計算して加算\n        used_msec += dt - last_closed;\n        \n        // 利用時間開始をリセット\n        last_closed = 0;\n        \n    } else if(elm.value==1 && !last_closed ) {\n        // ドアが閉まった＝利用開始 last_closedが0/null=連続していない１\n        last_closed = dt;\n    }\n    \n});\n\n// もし最後のエントリが１の場合（end_time 時点でドアが閉まっている場合\nif(last_closed){\n    // 利用時間を計算して加算\n    used_msec += end_time - last_closed;\n}\n\nmsg.payload = {\n    \"data_type\" : \"usage_stats\",\n    \"channel_id\" : channel_id,\n    \"start_time\":start_time.toString(),\n    \"end_time\":end_time.toString(),\n    \"used_msec\" : used_msec,\n    \"used_mins\" : used_msec / 1000 / 60,\n    \"usage\" : used_msec /(end_time-start_time)\n};\n\n\nreturn msg;","outputs":1,"noerr":0,"x":390,"y":1615,"wires":[["ce0ab3b.34cfad"]]},{"id":"1e1cf214.6462e6","type":"json","z":"396465eb.1d8e22","name":"","x":226.5,"y":1615,"wires":[["22c110b7.273ce"]]},{"id":"ce0ab3b.34cfad","type":"debug","z":"396465eb.1d8e22","name":"","active":true,"console":"false","complete":"payload","x":571,"y":1615,"wires":[]}]
```

Cloudant DBへのクエリを作るfunction部分と、その後、jsonパースして、利用率を算出するfunction部分、それぞれ説明します。

---

### クエリの作成部分

```js
bm_env = JSON.parse(context.global.process.env["VCAP_SERVICES"]);
cloudant_url= bm_env.cloudantNoSQLDB[0].credentials.url;
cloudant_view = "/iot_data/_design/Views/_view/channels";
channel_id=3;

now = new Date();
start_ms=new Date(now.getFullYear(), now.getMonth(), now.getDate(),now.getHours()-1,0);
end_ms=new Date(now.getFullYear(), now.getMonth(), now.getDate(),now.getHours(),0);

start_time =start_ms.toString();
end_time =end_ms.toString();
msg.url = cloudant_url +cloudant_view +
        '?startkey=['+ channel_id + ',"' + start_time + '"]' +
          '&endkey=['+ channel_id + ',"' + end_time + '"]';
msg.method = "GET";
msg.cl_stat = {
    "channel_id":channel_id,
    "start_time":start_time,
    "end_time":end_time
};
return msg;
```

cloudantのキーは、channelと時間の配列になっています。今回は、直近24時間で、どれだけ会議室が利用されていたか（ドアが閉まっていたか）をパーセントで計算しようと思います。
たとえば、今日が1月２日 0時(GMT)で、channel 0 のドアの利用率を計算したいすると、 ```start_key``` は ```[0, 2017-01-01T00:00:00.000Z]``` , ```end_key``` は ```[0,2017-01-02T00:00:00.000Z]``` になります。これを、先ほどのCloudant APIのURLの最後に、引数として設定して、クエリを実行します。

---

### 利用率の算出部分
ドアが閉まった時間を記録しておいて、ドアが開いたらその時間から逆算して、何分間ドアが閉まっていたかを計算するだけの簡単な仕組みです。

さくらIoT Platformから送られ、Cloudantに保存されているのは、 こんなデータです。

| ドア番号 | 日付 | ステータス |
|:-:|:-:|:-:|
|  A | 1/1 8:00  |  closed |
|  B | 1/1 8:15  |  closed |
|  B | 1/1 8:45  |  open |
|  A | 1/1 9:00  |  open |

ここから、ドアAは1時間利用されている、ドアBは30分利用されているといった感じで、利用時間を計算する必要があります。
もちろん、０時の時点でドアが閉まっていた場合も考慮しないと行けないです。ステータスがダブって記録された場合も一応考慮しておきます。
あと、今回は、算出対象期間中1回もドアが開閉しなかったケースは考えない(=利用されていなかった）ことにさせてください。

---

```js
channel_id=msg.cl_stat.channel_id;
start_time=new Date(msg.cl_stat.start_time);
end_time=new Date(msg.cl_stat.end_time);


used_msec = 0;
last_closed = null;

msg.payload.rows.forEach(function( elm ) {
    // keyに保存された日付をパース
    dt = new Date(elm.key[1]);
    
    if(elm.value===0 && used_msec ===0 && last_closed ===null  ){
        // ドアが開いた＝利用終了 かつ初エントリ
        // 利用時間を計算して加算
        // 初回なのでカウント開始時間（start_time）からの差
        used_msec = dt - start_time;
        
        // 利用時間開始をリセット
        last_closed = 0;
        
    } else if(elm.value===0 && last_closed !== 0 ){
        // ドアが開いた＝利用終了
        // 利用時間を計算して加算
        used_msec += dt - last_closed;
        
        // 利用時間開始をリセット
        last_closed = 0;
        
    } else if(elm.value==1 && !last_closed ) {
        // ドアが閉まった＝利用開始 last_closedが0/null=連続していない１
        last_closed = dt;
    }
    
});

// もし最後のエントリが１の場合（end_time 時点でドアが閉まっている場合
if(last_closed){
    // 利用時間を計算して加算
    used_msec += end_time - last_closed;
}

msg.payload = {
    "data_type" : "usage_stats",
    "channel_id" : channel_id,
    "start_time":start_time.toString(),
    "end_time":end_time.toString(),
    "used_msec" : used_msec,
    "used_mins" : used_msec / 1000 / 60,
    "usage" : used_msec /(end_time-start_time)
};


return msg;
```

---

実際に実行してみると、無事計算されたようです。たぶん。

<img width="306" alt="スクリーンショット 2017-01-07 21.00.25.png" src="https://qiita-image-store.s3.amazonaws.com/0/135431/777822d0-abb8-2a1e-b797-a518a33e0ef2.png">

あとは、毎日早朝にこのスクリプトを実行するなり、この値をCloudantに保存し直すなり、煮たり焼いたりしていただければ。

そろそろ時間になりましたので、さくらIoT x node-RED on Bluemix で会議室の空き状況を表示させるまでの3日間、これで終わりにしておきます。

何かの参考になればなによりです。

---
##ご静聴？ありがとうございました。