ASTERIA Warpを使って、電子錠Sesameから開閉情報の取得とレコードの抽出ーVersion1

今回作成したフローとその概要

セサミの電子錠で開閉の記録を取得するフローをつくりました。
セサミスマートロック
SesameのAPIを使って開閉の記録を取得した後、先月の各日付で最初と最後の開閉記録のみを抽出します。
結果のレコードをエクセルファイルに貼り付けます。
APIの詳細は、下記のリンクを参照
セサミAPI

フローの解説(先頭から順番に解説します)

マッパー(1番目)

先月の年と月を「yyyy-MM-」の形式で取得します。(現在が1月の場合、先月は去年の12月になるため年も必要。)
現在の日時をもとに、DateSplitを使って年と月にだけにした後、DateTimeExで日を1日に設定します。

次のDateCalcでその日付に-1日を足すと先月の月末の日付になるため、この日付から先月の年と月をDateSplitを使って取得します。
DateSplitからの出力とconstからの「-」をconcatでつなぐことで、先月の年と月「yyyy-MM-」を構成します。
これをフロー変数「SengetsuYearAndMonth(String型)」に入れます。

REST(2番目)

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件取得する設定にしています。

マッパー(3番目)

取得した項目のうち「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を起動して出力予定のファイルにレコードの貼り付け先を設定します。

下図の「追加」ボタンからレコードを貼り付ける場所を設定して、以下の手順で設定を行います。

  1. レコード列を選択してください:$A$2:$H$2(2列目のレコードが入る列)
  2. レコード名を入力してください:=$A$1:$H$1(1行目のタイトル行)
  3. 1レコードあたりの行数を入力してください:1
  4. レコード範囲を拡張しますか?:「はい」ボタンを押してください。
  5. 拡張時に使用する行を選択してください:$A$2:$H$2

その後「フィールド名取得」ボタンを押してフィールド名を一括で取得します。
※下図はフィールド名の取得まで完了した状態になります。

これで、RecordSQL(6番目)のSELECT文にある8つの項目がそのままの順番で出力されます。

プロパティの「ファイルを更新」を「いいえ」にすることで、テンプレートなどにレコードが入らなくなり、繰り返し使えるようになります。

FilePut(8番目)

レコードの入ったエクセルファイルを出力します。

結果

FilePut(8番目)プロパティの「ファイルパス」で設定した場所に出力されたファイルを確認すると、先月の開閉記録が各日付ごとに取得できています。また、エクセルに出力した場合、取得した値を使った関数を設定することができます。