がとらぼ

CREATE TABLE timelog (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`nid` varchar(16) NOT NULL,
`dtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`finout` bit(1) NOT NULL DEFAULT 0,
`flag` bit(1) NOT NULL DEFAULT 1,
PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE card (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`nid` varchar(16) NOT NULL,
`uid` int(10) NOT NULL,
`stdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`endate` datetime NOT NULL DEFAULT '2199-12-31 23:59:59',
`flag` bit(1) NOT NULL DEFAULT 1,
PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE user (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`uname` varchar(32) NOT NULL,
`uruby` varchar(32) NOT NULL,
`stdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`endate` datetime NOT NULL DEFAULT '2199-12-31 23:59:59',
`flag` bit(1) NOT NULL DEFAULT 1,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE junk (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`nid` varchar(16) NOT NULL,
`dtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB;

<?php
header( 'Expires: Fri, 1 Jab 2010 00:00:00 GMT' );
header( 'Last-Modified: ' . gmdate( 'D, d M Y H:i:s' ) . ' GMT' );
header( 'Cache-Control: no-store, no-cache, must-revalidate' );
header( 'Cache-Control: post-check=0, pre-check=0', false );
header( 'Pragma: no-cache' );

$json_string = file_get_contents('php://input');
$data = json_decode($json_string);
if (json_last_error() != JSON_ERROR_NONE){
    echo "不正です";
    exit;
}
$nid = $data->nid;

$dsn = 'mysql:dbname=timerecorder;host=localhost;charset=utf8mb4';
$user = 'timemgr';         //MySQL用timemgrアカウント
$password = 'パスワード';    //MySQL用timemgrアカウントのパスワード
$conn = new PDO($dsn, $user, $password);
//$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "select * from card 
            where nid = '" . $nid . "' 
                and stdate < NOW() 
                and endate > NOW() 
                and flag = 1;";

$stmt = $conn->query($sql);
$results = $stmt->fetchall();
if (empty($results)){
    $sql = "insert into junk 
                (nid) 
                value 
                ('" . $nid . "');";

    $conn->exec($sql);
    $msg = "みとうろくカードです";
} else {
    foreach ($results as $row) {
    $uid = $row['uid'];
    }
    $sql = "SELECT * FROM user 
                where id = '" . $uid . "' 
                    and stdate < NOW() 
                    and endate > NOW() 
                    and flag = 1;";

    $stmta = $conn->query($sql);
    $resultsa = $stmta->fetchall();
    if (empty($resultsa)){
        $msg = "ユーザーみとうろく";
    } else {
        //以下有効カードの場合の処理
        foreach ($resultsa as $rowa) {
            $uname = $rowa['uname'];
            $uruby = $rowa['uruby'];
        }
        //タイムログ処理
        //nidの最終記録を確認
        $sql = "select * from timelog 
                    where nid = '" . $nid . "' 
                        and dtime < NOW() 
                        and flag = 1 
                    order by dtime desc limit 1;";

        $stmta = $conn->query($sql);
        $resultsa = $stmta->fetchall();
        if (empty($resultsa)){
        $finout = 1; //記録が無ければ出勤=1から
            $dtime = '2000/01/01 00:00:00';
            $msg = $uruby . "さん、しゅっきん。";
        } else {
            foreach ($resultsa as $rowa) {
                $lid = $rowa['id'];
                $dtime = $rowa['dtime'];
                $finout = $rowa['finout']; //bitカラムの値の扱い要注意
            }
            if($finout == 1){ //直前の有効レコードが出勤
                $finout = 0;
                //前回の記録が出勤 日付をまたいでいたら警告
                if (mktime(0,0,0) > strtotime($dtime)){
                    $msg = $uruby . "さん、たいきん。しゅっきんがきのうです。かくにんしてください。";
                } else {
                    $msg = $uruby . "さん、たいきん";
                }
            } else { //直前の有効レコードが退勤
                $finout = 1;
                //前回の記録が退勤 それが本日なら警告
                if (strtotime($dtime) > strtotime(date("Y/m/d 00:00:00"))){
                    $msg = $uruby . "さん、しゅっきん。ぜんかいのたいきんがほんじつです。かくにんして下さい。";
                } else {
                    $msg = $uruby . "さん、しゅっきん。";
                }
            }
        }
        //最終記録が30秒以内の場合は最終を取り消し
        // さらに今回分も無効レコードとして登録(とにかく記録はする)
        if ((time() - strtotime($dtime)) < 30 ){
            $sql = "update timelog 
                        set flag = 0 
                            where id = $lid;";

            $conn->exec($sql);
            $sql = "insert into timelog 
                        (nid, finout, flag) 
                        value 
                        ('" . $nid . "', " . $finout . ", 0);";

            $conn->exec($sql);
            if ($finout = 0){
                $sfinout = "しゅっきん";
            } else {
                $sfinout = "たいきん";
            }
            $msg = $uruby. "さん、30秒いないの" . $sfinout . "、とりけし";
        } else {
            //記録を付ける
            $sql = "insert into timelog 
                        (nid, finout) 
                        value 
                        ('" . $nid . "', " . $finout . ");";

            $conn->exec($sql); 
        }
    }
}
unset($conn);
echo $msg;
?>

#!/bin/sh
HOSTPATH=example.com/timerecorder/db_recorder.php
LOGFILE=/var/log/card.log
TMP=/tmp/tmp.wav

echo none > /sys/class/leds/nanopi:blue:status/trigger

while :
do
    nid=$(nfc-poll | grep NFCID2 | cut -d: -f 2 | sed 's/ //g')

    if ! [ x"$nid" = x ];then
        echo `date '+%Y/%m/%d %H:%M:%S'` $nid >>$LOGFILE
        RESP=`curl http://$HOSTPATH -s -X POST -H "Content-Type: application/json" -d '{"nid": "'$nid'"}' 2&>/dev/null`
        echo heartbeat > /sys/class/leds/nanopi:blue:status/trigger
        echo $RESP
        echo $RESP | open_jtalk -m /usr/share/hts-voice/mei/mei_normal.htsvoice -x /var/lib/mecab/dic/open-jtalk/naist-jdic -ow $TMP && aplay --quiet $TMP
        rm -f $TMP
        echo none > /sys/class/leds/nanopi:blue:status/trigger
        sleep 1
    fi
done