Zabbix + Freee 勤怠 = タイムカード?

勤怠管理は入力するほうもチェックするほうも面倒くさいですよね。

ＰＣの電源のアップ・ダウンで自動で勤怠簿を作ってくれる機械というものが巷では売られています。ほとんどの社員がＰＣの電源のアップ・ダウン = 出社・退社になるうちの会社ではぴったりと思って値段を見ると１０万円近い。１０名足らずの社員の勤怠簿のためには流石に割に合わない。

うちの会社では法律で税制が変わると勝手に法律の施工月から給与計算に反映してくれたり、社員が自分で給与明細をオンラインで確認できたりして便利だったのでFreee勤怠というサービスを利用しています。最近Freeeのドキュメントを読んでいるとFreee勤怠API(https://www.freee.co.jp/hr/api/)なるものが公開されていた。

業務でZabbixという監視ツールを使う機会が多い私にとって自動勤怠入力システムを作るしかない状況ができていたので作ってみました。

１． Zabbixの設定

zabbixサーバのインストールについては他のいろんなサイトにのっているのでぐぐってインストールしてください。うちのインストール環境は以下の通りです。
・OS: CentOS7
・Zabbix Server 3.4.8 (zabbix-web-mysql, zabbix-web,zabbix-server-mysql,zabbix-web-japanese)
・Zabbix Agent 3.4.6 (Zabbix公式サイト - Agentダウンロード)

まずZabbixサーバのインストールで入ったMariaDBに勤怠用のデータベースとテーブルを作ります。

 # mysql
 > CREATE DATABASE kintai;
 > GRANT all on kintai.* to freee@localhost identified by 'freee';
 > CREATE TABLE kintai.arrival (`id` MEDIUMINT NOT NULL AUTO_INCREMENT, `user` VARCHAR(30) NOT NULL, `date` DATETIME NOT NULL, PRIMARY KEY (id));
 > CREATE TABLE kintai.leave (`id` MEDIUMINT NOT NULL AUTO_INCREMENT, `user` VARCHAR(30) NOT NULL, `date` DATETIME NOT NULL, PRIMARY KEY (id));

次にZabbixサーバで/etc/zabbix/zabbix_server.confのAlertScriptsPathを確認してください。

 # cat /etc/zabbix/zabbix_server.conf
 AlertScriptsPath=/usr/lib/zabbix/alertscripts

そこに先ほど作成したテーブルにデータを入れるだけのスクリプトを作成します。動けばいい程度なので適当です。

# vi /usr/lib/zabbix/alertscripts/insertDB
 #!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Data::Dumper;

my $DB_NAME = 'kintai';
my $DB_USER = 'freee';
my $DB_PASS = 'freee';
my $DB_HOST = 'localhost';

if (@ARGV != 3 || ($ARGV[0] ne 'arrival' && $ARGV[0] ne 'leave')) {
exit 1;
}

my $type = $ARGV[0];
my $user = $ARGV[1];
my $date = $ARGV[2];
my @values = ($user, $date);

my $dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME;host=$DB_HOST;port=3306", $DB_USER, $DB_PASS);
if (!defined($dbh)) {
exit 1;
}

my $sth = $dbh->prepare("INSERT INTO `$type`(`user`, `date`) values(?, ?)");
$sth->execute(@values);
exit

zabbix管理画面に入って管理のメディアタイムで以下のようなメディアタイプを作成します。

つぎに管理のユーザーで通知用ユーザを作りメディアタブで以下のようなメディアを割り当てます。

デフォルトのテンプレート”Template OS Windows”にイベントログSystemをAgentから送ってもらうアイテムを追加します。

同じテンプレート”Template OS Windows”のトリガーに起動時、停止時に出力されるログを設定します。

Windowsの停止では決まったログがなく仕方なく"カスタマー エクスペリエンス向上プログラムのユーザー ログオフ通知"で代用してます。

最後にアクションを設定します。条件には先ほど作ったトリガーをトリガー=で選択してください。実行内容については以下のように設定してください。アラートスクリプトは渡せるパラメータが限られているので件名とメッセージに必要情報をいれるのがコツです。

最後にＰＣをホストに登録します。表示名にはあとでFreee連携するのに使うのでのFreeeのメールアドレスを入れてください。

そして先ほど変更した"Template OS Windows"を適用してください。

2. Freee勤怠登録設定

まず、Freee APIのアクセストークンを取得するのページの通りにスーパーユーザーでログインして"APP ID", "Secret", "Authorization code"を確認してください。

次に以下のコマンドを実行してrefresh tokenを取得してください。

# curl -XPOST 'https://api.freee.co.jp/oauth/token' -d "grant_type=authorization_code" -d "client_id={確認したAPP ID}" -d "client_secret={確認したSecret}" -d "authorization_code={確認したAuthorization code}" -d "redirect_uri=urn:ietf:wg:oauth:2.0:oob" '

成功したら返ってくるレスポンスのjsonに含まれている"refresh_token"の値を"/etc/kintai/token.data"に

# echo {取得したrefresh_token} > /etc/kintai/token.data

同じくjsonに含まれている"access_token"の値を使って以下のコマンドを実行し"company id"を確認してください。

# curl -XGET 'https://api.freee.co.jp/hr/api/v1/users/me' -H "Authorization: Bearer {取得したaccess token}"

バッチ処理で前日のDBの勤怠情報をFreeeにAPIで登録するスクリプトを作成します。

当たり前ですがOS停止するとzabbix-agentが先に停止して停止のログがzabbixサーバに送られてくるのが次回起動のときになるので出社した社員の前回退社日の勤怠を入力するようになっています。

# vi /usr/local/sbin/kintai

#!/usr/bin/perl

use strict;
use warnings;
use Data::Dumper;
use JSON;
use LWP::UserAgent;
use DBI;
use POSIX;
use Net::LDAP;

# 勤怠情報DB
my $DB_NAME = 'kintai';
my $DB_USER = 'freee';
my $DB_PASS = 'freee';
my $DB_HOST = 'localhost';
my $TBL_NAME = 'arrival';
my $TBL_NAME2 = 'leave';

# Freee API
my $file = '/etc/kintai/token.data';
my $url = "https://api.freee.co.jp/oauth";
my $api_url = "https://api.freee.co.jp/hr/api/v1";
my $client_id = "{Freeeのページで確認したAPP ID}";
my $client_secret = "{Freeeのページで確認したSecret}";
my $company_id = "{APIで確認したcompany id}";

sub refresh
{
my $refresh_token;
open FH, "<$file";
while () {
chomp;
if ($_) {
$refresh_token = $_;
}
}
close FH;

if (!$refresh_token) {
return;
}

my %data = (
"grant_type" => 'refresh_token',
"client_id" => $client_id,
"client_secret" => $client_secret,
"refresh_token" => $refresh_token
);
my $ua = LWP::UserAgent->new;
my $res = $ua->post($url.'/token', [%data]);

my $count = 0;
while ($res->is_redirect) {
return undef if ($count++ > 5);
$res = $ua->post($res->request->uri.'/token/', [%data]);
}
my $rd = decode_json($res->content);
my $access_token = $rd->{'access_token'};
$refresh_token = $rd->{"refresh_token"};

open FH, ">$file";
print FH $refresh_token;
close FH;

return $access_token;
}

my $list = {};

# 日付取得

my ($s, $min, $h, $d, $m, $y) = localtime(time);

$y += 1900;

$m += 1;

# 出社社員リスト取得
my @arrivals = ();
my $filter = sprintf("WHERE `date`>='%04d-%02d-%02d 00:00:00' AND `date`<='%04d-%02d-%02d 23:59:59'", $y, $m, $d, $y, $m, $d);
my $sql = "SELECT `user` AS `ARRIVAL` FROM `$TBL_NAME` $filter GROUP BY `user`;";
my $dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME;host=$DB_HOST;port=3306", $DB_USER, $DB_PASS) or die "err: $!";
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $d = $sth->fetchrow_hashref) {
push(@arrivals, $d->{'ARRIVAL'});
}
$sth->finish();

# 前回退社時間リスト取得
foreach my $user (@arrivals) {
$filter = sprintf("WHERE `date`<'%04d-%02d-%02d 00:00:00' AND `user`='$user'", $y, $m, $d);
$sql = "SELECT `user`, MAX(`date`) AS `LEAVE` FROM `$TBL_NAME2` $filter;";
$sth = $dbh->prepare($sql);
$sth->execute();
while (my $d = $sth->fetchrow_hashref) {
if ($d->{'LEAVE'} =~ /^([0-9]{4}-[0-9]{2}-[0-9]{2}) ([0-9]{2})(:[0-9]{2}:[0-9]{2})$/) {
$list->{$user}->{'clock_out_at'} = "$1T$2$3".".000+09:00";
}
}
$sth->finish();
}

# 前回退社日情報取得
foreach my $u (keys(%{$list})) {
if (!defined($list->{$u}->{'clock_out_at'})) {
next;
}
my $ty;
my $tm;
my $td;
my $a;
my $b;

if ($list->{$u}->{'clock_out_at'} =~ /^([0-9]{4})-([0-9]{2})-([0-9]{2})T([0-9]{2}):([0-9]{2}):([0-9]{2}).*$/) {
$ty = $1;
$tm = $2;
$td = $3;
$list->{$u}->{'today'} = sprintf("%04d%02d%02d", $ty, $tm, $td);
$b = $4 * 3600 + $5 * 60 + $6;
}
if (!defined($ty) || !defined($tm) || !defined($td)) {
next;
}

# 前回退社日出社時間リスト取得
$filter = sprintf("WHERE `date`>='%04d-%02d-%02d 00:00:00' AND `date`<='%04d-%02d-%02d 23:59:59' AND `user`='$u'", $ty, $tm, $td, $ty, $tm, $td);
$sql = "SELECT `user`, MIN(`date`) AS `ARRIVAL` FROM `$TBL_NAME` $filter GROUP BY `user`;";
$dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME;host=$DB_HOST;port=3306", $DB_USER, $DB_PASS) or die "err: $!";
$sth = $dbh->prepare($sql);
$sth->execute();
while (my $d = $sth->fetchrow_hashref) {
if ($d->{'ARRIVAL'} =~ /^([0-9]{4}-[0-9]{2}-[0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})$/) {
$list->{$u}->{'clock_in_at'} = "$1T$2:$3:$4".".000+09:00";
$a = 3600 * $2 + 60 * $3 + $4;
last;
}
}
$sth->finish();
# 前回退社日最終起動時間リスト取得
$sql = "SELECT `user`, MAX(`date`) AS `ARRIVAL` FROM `$TBL_NAME` $filter GROUP BY `user`;";
$dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME;host=$DB_HOST;port=3306", $DB_USER, $DB_PASS) or die "err: $!";
$sth = $dbh->prepare($sql);
$sth->execute();
while (my $d = $sth->fetchrow_hashref) {
if ($d->{'ARRIVAL'} =~ /^([0-9]{4}-[0-9]{2}-[0-9]{2}) ([0-9]{2})(:[0-9]{2}:[0-9]{2})$/) {
$list->{$u}->{'last_boot'} = "$1T$2$3".".000+09:00";
last;
}
}
$sth->finish();

# 休憩時間設定
if ($a < 43200 && $b > 46800) {
my $break_in = sprintf("%04d-%02d-%02dT12:00:00.000+09:00", $ty, $tm, $td);
my $break_out = sprintf("%04d-%02d-%02dT13:00:00.000+09:00", $ty, $tm, $td);
my $break = [{'clock_in_at' => $break_in, 'clock_out_at' => $break_out}];
$list->{$u}->{'break_records'} = $break;
}
}

if (!$list) {
exit;
}

# Freee IDリスト取得
my $access_token = refresh();
if (!$access_token) {
exit 1;
}
my $ua = LWP::UserAgent->new;
my $res = $ua->get("$api_url/companies/$company_id/employees", "Authorization" => "Bearer $access_token");
if ($res->code != 200) {
exit 1;
}
my $data = decode_json($res->content);
my %freee = ();
foreach my $d (@{$data}) {
if (defined($d->{'email'}) && defined($d->{'id'})) {
$freee{$d->{'email'}} = $d->{'id'};
}
}

# Freee 勤怠情報登録
foreach my $u (keys(%{$list})) {
if (!defined($freee{$u}) || !defined($list->{$u}->{'clock_in_at'}) || !defined($list->{$u}->{'clock_out_at'}) || !defined($list->{$u}->{'last_boot'}) || !defined($list->{$u}->{'today'})) {
next;
}
my $id = $freee{$u};
my $d = {
"clock_in_at" => $list->{$u}->{'clock_in_at'},
"clock_out_at" => $list->{$u}->{'clock_out_at'}
};
if (defined($list->{$u}->{'break_records'})) {
$d->{'break_records'} = $list->{$u}->{'break_records'};
}
my $a;
my $b;
my $l;
if ($list->{$u}->{'last_boot'} =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}T([0-9]{2}):([0-9]{2}):([0-9]{2}).*$/) {
$l = 3600 * $1 + 60 * $2 + $3;
}
if ($d->{'clock_in_at'} =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}T([0-9]{2}):([0-9]{2}):([0-9]{2}).*$/) {
$a = 3600 * $1 + 60 * $2 + $3;
}
if ($d->{'clock_out_at'} =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}T([0-9]{2}):([0-9]{2}):([0-9]{2}).*$/) {
$b = 3600 * $1 + 60 * $2 + $3;
}
if (!defined($a) || !defined($b) || !defined($l) || $l >= $b || $a >= $b) {
next;
}

my $req = new HTTP::Request(PUT => "$api_url/employees/$id/work_records/".$list->{$u}->{'today'});
$req->content(encode_json($d));
$req->header("Authorization" => "Bearer $access_token");
$req->header("Content-Type" => "application/json");
$res = $ua->request($req);
}
exit

最後にcronで15時に実行するようにします。

退社ログが次回の出社時に送られてくるのでこの時間設定です。

 # vi /etc/cron.d/kintai
 00 15 * * * root /usr/local/sbin/kintai