catch_nbfd_admin/extend/tdengine/TdOperate.php

<?php
declare(strict_types=1);

namespace tdengine;

use tdengine\TdUtils;

class TdOperate
{
    protected $database;
    protected $username;
    protected $password;
    protected $httpHeader;
    protected $apiUrl;

    public function __construct() 
    {
        /**
         * 井盖
         * 库
         *     DROP DATABASE manhole
         *     CREATE DATABASE IF NOT EXISTS manhole keep 360 update 1 precision "us" REPLICA 2;
         *     CREATE DATABASE IF NOT EXISTS manhole keep 360 update 1 precision "us";
         *     KEEP是该数据库的数据保留多长天数，缺省是3650天(10年)，数据库会自动删除超过时限的数据；
         *     UPDATE 标志数据库支持更新相同时间戳数据；    
         * 
         * 统计每个设备心跳次数 一个设备一天一条 day天
         *     # drop table stats_heartbeat;
         *     create table stats_heartbeat (ts timestamp, devicenumber BINARY(32), day INT, num INT, lastTime INT, area INT, creator INT);
         * 
         * 告警月表 ts(天) 每个设备每天1条记录 按区域区分
         *     create table stats_alarm_month(ts timestamp, devicenumber BINARY(32), day INT, area INT, pry INT, flood INT, open INT, low INT, sub_flood INT, sub_low INT, creator INT);
         * 
         * 告警年表 ts(月) 每个设备每月1条记录 按区域区分
         *     create table stats_alarm_year(ts timestamp, devicenumber BINARY(32), month INT, area INT, pry INT, flood INT, open INT, low INT, sub_flood INT, sub_low INT, creator INT);
         * 
         */
        $this->initConfig();
    }

    /**
     * 初始化配置
     */
    protected function initConfig() {
        $td_config = \config('tdengine');

        if (empty($td_config) || empty($td_config['database']) || empty($td_config['username']) || empty($td_config['password']) || empty($td_config['api_url'])) 
        {
            $dsnErr = <<<EOL
                [TDENGINE]
                DATABASE = database
                USERNAME = username
                PASSWORD = password
                API_URL = http://127.0.0.1:6041/rest/sql
            EOL;

            throw new \think\Exception("ENV TDENGINE configure error, ".PHP_EOL."{$dsnErr}");
        }
        
        $this->database = $td_config['database'];
        $this->username = $td_config['username'];
        $this->password = $td_config['password'];
        $this->apiUrl  = $td_config['api_url'];
        $this->httpHeader = [
            "Content-Type: application/x-www-form-urlencoded",
            "Authorization: Basic " . base64_encode("$this->username:$this->password"),
            "Expect: ",
        ]; 
    }

    /**
     * 保存心跳次数
     * @param String|Int $deviceNumber 设备资产编号
     * @param String|Int $date 心跳日期
     * @param Array $data ['area'=> 110001,'creator'=>1]
     * @return Array ['status' => 'succ']  ['status' => 'error', 'desc' => 'errorrrrrr']
     */
    public function saveHeartbeatCount($deviceNumber, $date, Array $data) {
        /*
        * select ts,num from manhole.stats_heartbeat where devicenumber='14046452270' and day=100 and area=1100001 and creator=1;
        * insert into manhole.stats_heartbeat values(1609987092000000, '14046452270', 100, 2, 1609987092, 1100001, 1);
        */
        if (empty($deviceNumber) || empty($date) || empty($data['area']) || empty($data['creator'])) {
            return ['status' => 'error', 'desc' => 'deviceNumber, date, area, creator required'];
        }
        $deviceNumber = strtoupper($deviceNumber);
        $area = $data['area'];
        $creator = $data['creator'];

        if (!strtotime($date)) {
            $day = strtotime(date('Y-m-d', $date));
        } else {
            $day = strtotime(date('Y-m-d', strtotime($date)));
        }
        if (!$day) {
            return ['status' => 'error', 'desc' => 'date format error'];
        }
        // 查询
        $sql = "select ts,num from {$this->database}.stats_heartbeat where devicenumber='{$deviceNumber}' and day={$day} and area={$area} and creator={$creator};";
        $res = TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);
        if ($res['status'] != 'succ') {
            return $res;
        }
        // 存在多个值
        if ($res['rows'] > 1) {
            $res['status'] = 'error';
            $res['desc'] = 'multiple records';
            return $res;
        }

        $ts = 0;
        $num = 1;
        $last_time = $this->formatTimestamp($date, 10);
        if ($res['rows'] == 1) {//修改
            $ts = $res['data'][0][0];
            $ts = strtotime($ts) . substr($ts, -6);//'2021-01-07 11:01:25.920001' -> unix时间戳
            $num = $res['data'][0][1] + 1;
        } else if ($res['rows'] == 0) {//新增
            // 时间需要保存微妙6位，防重
            list($us, ) = explode(" ", microtime());
            $us = str_pad((String)($us * 1000000), 6, '0', STR_PAD_LEFT);
            $ts = $last_time . $us;
        }
        
        $sql = "insert into {$this->database}.stats_heartbeat values({$ts}, '{$deviceNumber}', {$day}, {$num}, {$last_time}, {$area}, {$creator});";
        return TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);
    }

    /**
     * 统计一个设备一段时间的心跳次数
     * @param String|Int $deviceNumber 设备编号
     * @param String|Int $startTime 开始时间
     * @param String|Int $endTime 结束时间
     * @return Array ['status' => 'succ', 'data' => ['sum' => 10]] | ['status' => 'error', 'desc' => 'errorrrrrr']
     */
    public function getHeartbeatCount($deviceNumber, $startTime, $endTime) {        
        /**
         * select sum(num) from manhole.stats_heartbeat where ts>1509948800000000 and ts<1709948800000000 and devicenumber='14046452270';
         */
        if (empty($deviceNumber)) {
            return ['status' => 'error', 'desc' => 'device number required'];
        }
        $deviceNumber = strtoupper($deviceNumber);
        $startTime = $this->formatTimestamp($startTime);
        $endTime = $this->formatTimestamp($endTime);
        if (!$startTime || !$endTime) {
            return ['status' => 'error', 'desc' => 'startTime or endTime format error'];
        }
        
        $sql = "select sum(num) from {$this->database}.stats_heartbeat where ts > {$startTime} and ts < {$endTime} and devicenumber = '{$deviceNumber}';";
        $res = TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);
        
        if ($res['status'] == 'succ') {
            return ['status' => 'succ', 'data' => ['sum' => !isset($res['data'][0][0]) ? 0 : $res['data'][0][0]]];
        }
        return $res;
    }

    /**
     * 获取某天在线设备数量
     * @param String $day 查询日期 '2021-02-01'
     * @param String $offlineTime 离线时间 '2021-02-01 01:00:00'
     * @param Array $other 其他条件数组：['creator'=>[1,2,...], 'area'=>[101,102,...]]
     */
    public function getOnlineDeviceCountByDate($day, $offlineTime, $other = [])
    {
        /**
         * select count(*) from {$this->database}.stats_heartbeat where day='1613750400' and lasttime > {$offline_time}
         *  and ( creator = 1 or creator = 2 ) and ( area = 101 or area = 102 ) 
         */
        $day = strtotime(date('Y-m-d',strtotime($day)));
        if (!$day) {
            return ['status' => 'error', 'desc' => '查询日期不存在或格式错误，正确格式: 2021-2-1'];
        }
        $offlineTime = strtotime($offlineTime);
        if (!$offlineTime) {
            return ['status' => 'error', 'desc' => '离线时间不存在或格式错误，正确格式: 2021-2-1 01:00:00'];
        }
        $sql = "select count(*) from {$this->database}.stats_heartbeat where day={$day} and lasttime > {$offlineTime}";

        $area = $other['area'] ?? [];
        if (count($area) != 0) {
            $sql .= " and (";
            foreach ($area as $val) {
                $sql .= " area={$val} or";
            }
            $sql = substr($sql, 0, -2) . ")";
        }

        $creator = $other['creator'] ?? [];
        if (count($creator) != 0) {
            $sql .= " and (";
            foreach ($creator as $val) {
                $sql .= " creator={$val} or";
            }
            $sql = substr($sql, 0, -2) . ")";
        }

        if (isset($other['devicenumber'])) {
            $sql .= " and devicenumber='{$other['devicenumber']}' ";
        }

        $sql .= ';';
        // var_dump($sql);
        $res = TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);
        if ($res['status'] == 'succ') {
            return ['status' => 'succ', 'data' => ['count' => !isset($res['data'][0][0]) ? 0 : $res['data'][0][0]]];
        }
        return $res;
    }

    /**
     * 保存日/月告警数据
     * @param String|Int $deviceNumber 设备编号
     * @param String|Int $date 日期
     * @param Array $data ['area' => 1102, 'pry' => 1, 'flood' => 1, 'open' => 0, 'low' => 1, 'sub_flood' => 1, 'sub_low' => 1,]
     * @param String $type 日期类型：day | month
     * @return Array ['status' => 'succ', 'data' => [] | ['status' => 'error', 'desc' => 'error']
     */
    public function saveAlarmCount($deviceNumber, $date,Array $data, $type = 'day') {
        /* 
         * select * from manhole.stats_alarm_month where devicenumber='14046452270' and day=1612108800 and area=1001 and creator=1;
         * insert into manhole.stats_alarm_month values(1609987092000000, '14046452270', 1612108800, 1001, 1, 2, 3, 1);
         */
        // 设备编号
        if (empty($deviceNumber) || empty($data['area']) || empty($data['creator'])) {
            return ['status' => 'error', 'desc' => 'deviceNumber, area, creator required'];
        }
        $deviceNumber = strtoupper($deviceNumber);
        $area = $data['area'];
        $creator = $data['creator'];
        // 日期
        if ($type == 'day') { // 日
            $date = !strtotime($date) ? strtotime(date('Y-m-d', $date)) : strtotime(date('Y-m-d', strtotime($date)));
            $table = 'stats_alarm_month';
        } elseif ($type == 'month') { // 月
            $date = !strtotime($date) ? strtotime(date('Y-m', $date)) : strtotime(date('Y-m', strtotime($date)));
            $table = 'stats_alarm_year';
        } else {
            return ['status' => 'error', 'desc' => '不支持的类型'];
        }
        if (!$date) {
            return ['status' => 'error', 'desc' => 'date format error'];
        }
        // 数据
        $sql = "select ts,pry,flood,open,low,sub_flood,sub_low from {$this->database}.{$table} where devicenumber='{$deviceNumber}'" .
                " and {$type}={$date} and area={$area} and creator={$creator};";
        $res = TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);

        $update['ts'] = 0;
        $update['creator'] = $creator;
        $update['pry'] = isset($data['pry']) ? $data['pry'] : 0;
        $update['flood'] = isset($data['flood']) ? $data['flood'] : 0;
        $update['open'] = isset($data['open']) ? $data['open'] : 0;
		$update['low'] = isset($data['low']) ? $data['low'] : 0;
		$update['sub_flood'] = isset($data['sub_flood']) ? $data['sub_flood'] : 0;
		$update['sub_low'] = isset($data['sub_low']) ? $data['sub_low'] : 0;
        $last_time = $this->formatTimestamp($date, 10);
        
        if ($res['status'] == 'succ') {
            if ($res['rows'] == 1) {//修改
                $update['ts'] = $res['data'][0][0];
                $update['ts'] = strtotime($update['ts']) . substr($update['ts'], -6);//'2021-01-07 11:01:25.920001' -> unix时间戳
                
                $update['pry'] += $res['data'][0][1];
                $update['flood'] += $res['data'][0][2];
                $update['open'] += $res['data'][0][3];
				$update['low'] += $res['data'][0][4];
				$update['sub_flood'] += $res['data'][0][5];
				$update['sub_low'] += $res['data'][0][6];
            } else if ($res['rows'] == 0) {//新增
                //时间需要保存微妙6位，防重
                list($us, ) = explode(" ", microtime());
                $us = str_pad((String)($us * 1000000), 6, '0', STR_PAD_LEFT);
                $update['ts'] = $last_time . $us;
            } else {//存在多个值
                $res['status'] = 'error';
                $res['desc'] = 'multiple records';
                return $res;
            }
        } else {
            return $res;
        }
        
        $sql = "insert into {$this->database}.{$table}(ts,devicenumber,{$type},area,pry,flood,open,low,sub_flood,sub_low,creator) values({$update['ts']}, '{$deviceNumber}'," .
            "{$date}, {$data['area']}, {$update['pry']}, {$update['flood']}, {$update['open']}, {$update['low']}, {$update['sub_flood']}, {$update['sub_low']}, {$update['creator']});";

        return TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);
    }

    /**
     * 按天/月查询报警设备数
     * @param String $date 查询日期：2021-01-15 | 2021-01
     * @param String $type 日期类型：day | month
     * @param Array $area 所属区域
     * @param Array $creator 创建人
     * @return Array ['status' => 'succ', 'data' => ['count' => 10]] | ['status' => 'error', 'desc' => 'errorrrrrr']
     */
    public function getAlarmDeviceCount($date, $type = 'day', $area = [], $creator = [], $devicenumber = '') {
        /**
         * select count(*) from manhole.stats_alarm_month where ts > 1509948800000000 and ts < 1709948800000000 and (area=1001 or area=1002) and (creator=1 or creator=2);
         */
        if ($type == 'day') { // 日
            $startTime = !strtotime($date) ? strtotime(date('Y-m-d', $date)) : strtotime(date('Y-m-d', strtotime($date)));
            $endTime = $startTime + 86400;
            $table = 'stats_alarm_month';
        } elseif ($type == 'month') { // 月
            $startTime = !strtotime($date) ? strtotime(date('Y-m', $date)) : strtotime(date('Y-m', strtotime($date)));
            $endTime = strtotime(date('Y-m',$startTime) .' +1 month ');
            $table = 'stats_alarm_year';
        } else {
            return ['status' => 'error', 'desc' => '不支持的类型'];
        }
        $startTime = $this->formatTimestamp($startTime);
        $endTime = $this->formatTimestamp($endTime);

        $sql = "select count(*) from {$this->database}.{$table} where ts > {$startTime} and ts < {$endTime}";

        if (count($area) != 0) {
            $sql .= " and (";
            foreach ($area as $val) {
                $sql .= " area={$val} or";
            }
            $sql = substr($sql, 0, -2) . ")";
        }
        if (count($creator) != 0) {
            $sql .= " and (";
            foreach ($creator as $val) {
                $sql .= " creator={$val} or";
            }
            $sql = substr($sql, 0, -2) . ")";
        }
        if ($devicenumber) {
            $sql .= " and devicenumber='{$devicenumber}' ";
        }
        $sql .= ";";
        // var_dump($sql);
        $res = TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);
        if ($res['status'] == 'succ') {
            return ['status' => 'succ', 'data' => ['count' => !isset($res['data'][0][0]) ? 0 : $res['data'][0][0]]];
        }
        return $res;
    }

    /**
     * 按天/月查询[设备]报警分类数
     * @param String $startTime 开始时间
     * @param String $endTime 结束时间
     * @param String $deviceNumber 设备号
     * @param Array $aree 所属区域
     * @return Array ['status' => 'succ', 'data' => ['pry' => 10, 'flood' => 10, 'open' => 10]]
     */
    public function getAlarmTypeCount($startTime, $endTime, $deviceNumber = '', $area = [], $creator = [])
    {
        /**
         * select sum(pry),sum(flood),sum(open),sum(low),sum(sub_flood),sum(sub_low)
         * from manhole.stats_alarm_month where ts > 1509948800000000 and ts < 1709948800000000 and area = 1001 and devicenumber = 11111;
         */        
        $startTime = $this->formatTimestamp($startTime);
        $endTime = $this->formatTimestamp($endTime);
        if (!$startTime || !$endTime) {
            return ['status' => 'error', 'desc' => 'startTime or endTime format error'];
        }

        $sql = "select sum(pry),sum(flood),sum(open),sum(low),sum(sub_flood),sum(sub_low) from {$this->database}.stats_alarm_month where ts>{$startTime} and ts<{$endTime}";
        if (!empty($deviceNumber)) {
            $deviceNumber = strtoupper($deviceNumber);
            $sql .= " and deviceNumber='{$deviceNumber}'";
        }
        if (count($area) != 0) {
            $sql .= " and (";
            foreach ($area as $val) {
                $sql .= " area={$val} or";
            }
            $sql = substr($sql, 0, -2) . ")";
        }
        if (count($creator) != 0) {
            $sql .= " and (";
            foreach ($creator as $val) {
                $sql .= " creator={$val} or";
            }
            $sql = substr($sql, 0, -2) . ")";
        }
        $sql .= ";";
        // var_dump($sql);
        $res = TdUtils::httpRequest($this->apiUrl, $sql, 'POST', $this->httpHeader);
        
        if ($res['status'] == 'succ') {
            return ['status' => 'succ', 
                'data' => [
                    'pry' => !isset($res['data'][0][0]) ? 0 : $res['data'][0][0],
                    'flood' => !isset($res['data'][0][1]) ? 0 : $res['data'][0][1],
                    'open' => !isset($res['data'][0][2]) ? 0 : $res['data'][0][2],
                    'low' => !isset($res['data'][0][3]) ? 0 : $res['data'][0][3],
                    'sub_flood' => !isset($res['data'][0][4]) ? 0 : $res['data'][0][4],
                    'sub_low' => !isset($res['data'][0][5]) ? 0 : $res['data'][0][5],
                ]
            ];
        }
        return $res;
        
    }

    /**
     * 转换为查询条件ts的时间戳（右补0）
     * @param String|Int $date 日期 | 时间戳
     * @param Int $len 时间戳长度
     */
    public function formatTimestamp($date,int $len = 16)
    {
        $date = (String)$date;
        $strDT = strtotime($date);
        if ($strDT && strlen((String)$strDT) === 10) {
            $date = $strDT;
        }
        return str_pad((String)$date, $len, '0');
    }

}