最近kintone漬け。
といっても、新しい機能を追加していくような楽しい作業ではなく、運用中のアプリの不具合対応（デバッグ）と細かい改良ばかりで、少し疲れてきた。
そんな中で、kintoneのデータの加工・集計をラクにやる方法はないものか？ と調べていたら、
kintone でSQLを使う
という記事を発見。
alasqlというJavascript用データベースを使うと、kintoneのデータをSQLで集計できるじゃん。これすごい。
SUM(Group By)で合計値を求めたり、複数のアプリのデータをJoinで組み合わせたりできる。
（逆に言うと、こんな簡単なことがkintone標準機能ではできなかったりするのだが…）
"kintone alasql"でググるとそこそこ記事が出てくるので、もしかしてkintoneガチ勢には当たり前の話？
早速、工数管理アプリに入っている工数明細をプロジェクト単位・個人単位でSUMする画面を作ってみたので、備忘のために記事に残しておく。
元データはこんな感じ。
 |
| Figure.1 集計元のkintoneアプリ |
設計方針
データは、event.recordsではなくAPIで取得する（event.recordsは最大でも100件しかデータが取れないので、集計目的では使えない）。 APIは生で叩くのではなく、kintone-rest-api-clientを利用。（getAllRecordsWithCursorが最強。みんな使うといいと思う。）
データの表示にはjQuery DataTablesを利用する。DataTablesを使って多機能な一覧画面を作ろう！ – cybozu developer network とかが参考になる。
動作環境
アプリ設定画面の「JavaScript / CSSでカスタマイズ」にて以下URLを指定。
https://js.cybozu.com/jquery/3.4.0/jquery.min.js https://js.cybozu.com/datatables/v1.10.19/js/jquery.dataTables.min.js https://unpkg.com/@kintone/rest-api-client@latest/umd/KintoneRestAPIClient.min.js
https://js.cybozu.com/datatables/v1.10.19/css/jquery.dataTables.min.css
また、http://alasql.org/ から alasql.min.js をDLし、PC用のJavaScriptファイルにアップロードしておく（alasqlのzipをダウンロードして解凍し、distディレクトリから取り出せば良い）。
もう1点、DataTablesの見栄えをkintoneに近づけるため、DataTablesをkintoneライクにする魔法のコード - Qiitaからkintone-datatables.cssをDLし、PC用のCSSファイルにアップロードしておくとよい（作者の方に感謝）。
サンプルコード HTML
アプリに一覧「プロジェクト別集計」を作成しておく。
レコード一覧の表示形式を「カスタマイズ」にし、以下のHTMLを記述。
サンプルコード js
/*
* project_summary.js
* 工数管理アプリのデータをプロジェクト別・個人別に集計
*
* required: jquery.dataTables.min.js
* https://js.cybozu.com/datatables/v1.10.19/js/jquery.dataTables.min.js
*
* required: kintone-rest-api-client
* https://unpkg.com/@kintone/rest-api-client@latest/umd/KintoneRestAPIClient.min.js
*
* requeired: alasql.min.js
* http://alasql.org/
*/
(function() {
"use strict";
kintone.events.on(['app.record.index.show'], function(event){
if(event.viewName !== 'プロジェクト別集計') return;
// Initialize kintone-rest-api-client
const client = new KintoneRestAPIClient();
var myTable;
var appId = kintone.app.getId();
var query = kintone.app.getQuery();
// APIでデータ取得
client.record.getAllRecordsWithCursor({
app: appId,
query: query,
}).then(function(response){
// kintoneのrecords(json)を、alasqlで扱える配列に変換
function convertToRows(records) {
var rows = records.map(function(record){
var keys = Object.keys(record);
var row = {};
keys.map(function(key){
switch (key) {
case '担当者氏名':
// '担当者氏名'フィールドからname(=氏名)を取り出す
row['担当者氏名'] = record[key].value[0]['name'];
break;
case '作業担当部署':
// '作業担当部署'フィールドからname(=部署名)を取り出す
row['作業担当部署'] = record[key].value[0]['name'];
break;
default:
// 他のフィールドはvalueを取り出す。数値の場合はNumberに変換
row[key] = record[key].type === 'NUMBER' ? Number(record[key].value) : record[key].value;
}
});
return row;
});
return rows;
}
var recordSet = convertToRows(response);
// sum([工数]) by alasql
var sqlResult = alasql(
"SELECT [プロジェクトコード], [作業担当部署], [担当者氏名], SUM([工数計_人月]) AS [工数計_人月] FROM ? GROUP BY [プロジェクトコード], [作業担当部署], [担当者氏名]",
[recordSet]
);
// 表に表示するカラム（alasqlの出力結果の項目名）
var columns = [
'プロジェクトコード',
'作業担当部署',
'担当者氏名',
'工数計_人月',
];
// 表のタイトルを好きにいじれるよう、別変数で定義。このサンプルではあまり意味がない。
var titles = {
プロジェクトコード: 'プロジェクトコード',
作業担当部署: '作業担当部署',
担当者氏名: '担当者氏名',
工数計_人月: '工数計',
};
// DataTables初期化
myTable = $('#myTable').DataTable({
// DataTableの設定値はお好みで。
destroy: true,
pageLength: 100,
lengthChange: false,
searching: false,
info: false,
data: sqlResult.map(function(record){
return columns.reduce(function(data, column){
data[column] = record[column];
return data;
}, {
レコード番号: record['レコード番号']
});
}),
columns: columns.map(function(column){
return {
title: this[column],
data: column
}
}, titles)
});
});
});
})();
動作イメージ
サンプルの動作結果はこんな感じ。
 |
| Figure.2 カスタマイズ結果 |
画像にモザイクがかかりまくっていて何がなんだか分からないが、結果はとても良好。パフォーマンスも今のところ申し分ない。
今回はSUMしか使っていないが、そのうちJoinも使ってみたい。
