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 = <<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'); } }