今回作成したフローとその概要
セサミの電子錠で開閉の記録を取得するフローをつくりました。
セサミスマートロック
SesameのAPIを使って開閉の記録を取得した後、先月の各日付で最初と最後の開閉記録のみを抽出します。
結果のレコードをエクセルファイルに貼り付けます。
APIの詳細は、下記のリンクを参照
セサミAPI
フローの解説（先頭から順番に解説します）
マッパー（１番目）
先月の年と月を「yyyy-MM-」の形式で取得します。（現在が１月の場合、先月は去年の１２月になるため年も必要。）
現在の日時をもとに、DateSplitを使って年と月にだけにした後、DateTimeExで日を1日に設定します。
次のDateCalcでその日付に-1日を足すと先月の月末の日付になるため、この日付から先月の年と月をDateSplitを使って取得します。
DateSplitからの出力とconstからの「-」をconcatでつなぐことで、先月の年と月「yyyy-MM-」を構成します。
これをフロー変数「SengetsuYearAndMonth（String型）」に入れます。
REST（２番目）
SesameのWebAPIを使用して入退出記録を取得します。
プロパティを以下の通りに設定します。
- 「基本」タブ
- コネクションを使用：いいえ
- URL（コネクション情報の中にあります）：https://app.candyhouse.co/api/sesame2/「sesamiのUUID」/history?page=0&lg=「取得したいログの件数」
- HTTPメソッド：GET
- リダイレクトを処理：いいえ
- 「送信ヘッダー」タブ
- ヘッダー名：x-api-key
- データ型：String
- 値：「sesamiのAPIKEY」
下図では「lg=300」でログを300件取得する設定にしています。
マッパー（３番目）
取得した項目のうち「timestamp」（開閉が行われた時間）はUNIX時間で表現されているので、見やすいようにUnixTimeToDateを使ってDateTime型に直します。
RecordFilter （4番目）
typeの値が10と11のレコードは通知なので除外します。
RecordFilter （5番目）
取得したレコード300件には先月以外のレコードも含まれているので、それらを除外します。
フロー変数「SengetsuYearAndMonth」に入れていた先月の年と月をDateTime型に直した日時に含んでいるかどうかで判定します。
※DateTime型でyyyy-MM-の並びになるのは先頭の年と月だけです。
RecordSQL（6番目）
各日付で最初と最後の開閉記録のみをRecordSQLを使って抽出します。
実行するSQLクエリは次の通りになります。
WITH Summary AS (
SELECT
SUBSTRING(field[5], 1, 10) AS DateOnly,
MIN(field[5]) AS EarliestTime,
MAX(field[5]) AS LatestTime
FROM
in[1]
GROUP BY
DateOnly
)
SELECT
in[1].field[1],
in[1].field[2],
in[1].field[3],
in[1].field[4],
in[1].field[5],
Summary.DateOnly,
Summary.EarliestTime,
Summary.LatestTime
FROM
in[1]
JOIN
Summary
ON
in[1].field[5] = Summary.EarliestTime
OR in[1].field[5] = Summary.LatestTime
ExcelOutput（7番目）
テンプレートなどに抽出したレコードを貼り付けます。
ExcelOutput からExcelbuilderを起動して出力予定のファイルにレコードの貼り付け先を設定します。
下図の「追加」ボタンからレコードを貼り付ける場所を設定して、以下の手順で設定を行います。
- レコード列を選択してください：$A$2:$H$2（２列目のレコードが入る列）
- レコード名を入力してください：=$A$1:$H$1（１行目のタイトル行）
- 1レコードあたりの行数を入力してください：1
- レコード範囲を拡張しますか？：「はい」ボタンを押してください。
- 拡張時に使用する行を選択してください：$A$2:$H$2
その後「フィールド名取得」ボタンを押してフィールド名を一括で取得します。
※下図はフィールド名の取得まで完了した状態になります。
これで、RecordSQL（6番目）のSELECT文にある8つの項目がそのままの順番で出力されます。
プロパティの「ファイルを更新」を「いいえ」にすることで、テンプレートなどにレコードが入らなくなり、繰り返し使えるようになります。
FilePut（8番目）
レコードの入ったエクセルファイルを出力します。
結果
FilePut（8番目）プロパティの「ファイルパス」で設定した場所に出力されたファイルを確認すると、先月の開閉記録が各日付ごとに取得できています。また、エクセルに出力した場合、取得した値を使った関数を設定することができます。