<?php
namespace catchAdmin\device\controller;
use catcher\base\CatchRequest as Request;
use catcher\CatchResponse;
use catcher\base\CatchController;
// use catchAdmin\device\model\RfRecord as rfRecordModel;
// use catchAdmin\device\model\Station as stationModel;
// use catchAdmin\device\model\StationPhoto;
use catcher\Utils;
use catcher\library\excel\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Env;
use think\facade\Db;
use PDO;
class Station extends CatchController
{
// protected $stationModel;
// protected $rfRecordModel;
// public function __construct(rfRecordModel $rfRecordModel,stationModel $stationModel)
// {
// $this->rfRecordModel = $rfRecordModel;
// $this->stationModel = $stationModel;
// }
/**
* 列表
* @time 2022年01月20日 10:09
* @param Request $request
*/
public function index(Request $request)
{
$param=$request->param();
$cond=[];
if($param['shortcode']){
$cond['a.DEVICE_CODE']=['like',$param['shortcode']];
}
if($param['name']){
// $param['name'] = mb_convert_encoding($param['name'], 'GBK','UTF-8');
$cond['a.DEVICE_NAME']=['like',$param['name']];
}
$count=queryOracleCount('DSSC2.ADM_DEV',$cond);
$cond['page']=isset($param['page'])?$param['page']:1;
$cond['limit']=isset($param['limit'])?$param['limit']:10;
$rows=queryOracleSelect('(SELECT * FROM DSSC2.ADM_DEV ORDER BY ID DESC) a',$cond,'a.ID,a.IS_ONLINE,a.LOGIN_NAME,a.DEVICE_CODE,a.DEVICE_NAME,a.OWNER_CODE,a.DEVICE_IP,a.DEVICE_PORT,to_char(a.CREATE_DATE,\'yyyy-mm-dd hh24:mi:ss\') CREATE_DATE');
foreach($rows as &$val){
$val['IS_ONLINE_TEXT']=$val['IS_ONLINE']?'在线':'离线';
$findCond=[
'DEVICE_CODE'=>['=',$val['DEVICE_CODE']]
];
$info=queryOracleFind('DSSC2.ADM_DEV_RFID_CHN',$findCond);
$val['longitude']=$info['GPS_X'];
$val['latitude']=$info['GPS_Y'];
}
$response=[
'code'=>10000,
'message'=>'查询成功',
'count'=>$count,
'data'=>$rows,
'current'=>isset($param['page'])?(int)$param['page']:1,
'limit'=>isset($param['limit'])?(int)$param['limit']:10,
];
return $response;
}
/**
* 所有列表
* @time 2022年01月20日 10:09
* @param Request $request
*/
public function getAllList(Request $request)
{
$cond=[
'_string'=>'a.DEVICE_CODE = r.DEVICE_CODE',
];
$cond['page']=1;
$cond['limit']=1000;
$conn = null;
$tableName='(SELECT * FROM DSSC2.ADM_DEV ORDER BY ID DESC) a,DSSC2.ADM_DEV_RFID_CHN r';
$field='a.ID,a.IS_ONLINE,a.LOGIN_NAME,a.DEVICE_CODE,a.DEVICE_NAME,a.OWNER_CODE,to_char(a.UPDATE_DATE,\'yyyy-mm-dd hh24:mi:ss\') UPDATE_DATE,r.GPS_X,r.GPS_Y';
$host= Env::get('oracle.hostname', '127.0.0.1');
$port= Env::get('oracle.hostport', '1521');
$instance_name= Env::get('oracle.instance', 'ORCL');
$username= Env::get('oracle.username', 'root');
$password= Env::get('oracle.password', 'root');
// $conn = oci_connect('用户名', '密码', '远程数据库名(eg.//192.168.1.133/orcl)');
$conn = oci_connect($username, $password, $host.':'.$port.'/'. $instance_name,'AL32UTF8');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$start=($cond['page']-1)*$cond['limit'];
$end=$cond['page']*$cond['limit'];
$whereStr=buildQueryCond2($cond);
$sql='SELECT * FROM ( SELECT '.$field.',ROWNUM RN FROM '.$tableName.' WHERE ROWNUM <='.$end.' '.$whereStr.' ) WHERE RN >'. $start;
// var_dump($sql);
// 查询并渲染
$stid = oci_parse($conn, $sql);
$r = oci_execute($stid);
$rows=[];
while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
if($row['GPS_Y'] && $row['GPS_X']){
$wgsLoc = \algorithm\Geometry::wgsTOgcj((float)$row['GPS_Y'],(float)$row['GPS_X']);
$lngLat = \algorithm\Geometry::convertGcj02ToBd09($wgsLoc['lat'],$wgsLoc['lng']);
$row['GPS_X']=$lngLat['lng'];
$row['GPS_Y']=$lngLat['lat'];
$rows[]=$row;
}
}
oci_free_statement($stid);
$response=[
'code'=>10000,
'message'=>'查询成功',
'data'=>$rows,
];
return $response;
}
/**
* 保存信息
* @time 2022年01月20日 10:55
* @param Request $request
*/
public function save(Request $request)
{
$params=$request->param();
$params['LOGIN_NAME']=strtoupper($params['LOGIN_NAME']);
$r=$this->execSaveStation($params);
return CatchResponse::success($r);
}
private function execSaveStation($data){
$conn=getOracleConnect();
$sql='select DEVICE_CODE from "DSSC2"."ADM_DEV" where DEVICE_CODE in (select max(DEVICE_CODE) from "DSSC2"."ADM_DEV")';
$stid = oci_parse($conn, $sql);
$r = oci_execute($stid);
$result = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
$deivce_code=$result['DEVICE_CODE']+1;
$time=date('Y-m-d H:i:s');
//插入基站信息表
$sql='INSERT INTO "DSSC2"."ADM_DEV"("ID", "DEVICE_CODE","DEVICE_NAME", "DEVICE_CATEGORY", "DEVICE_TYPE","OWNER_CODE","LOGIN_TYPE","LOGIN_NAME","IS_ONLINE","CREATE_DATE","UPDATE_DATE")
VALUES(DSSC2.SEQ_ADM_DEV.nextval,\''.$deivce_code.'\',\''.$data['DEVICE_NAME'].'\',32,\'13\',\'001\',\'6\',\''.$data['LOGIN_NAME'].'\',0,TO_DATE(\''.$time.'\', \'SYYYY-MM-DD HH24:MI:SS\'),TO_DATE(\''.$time.'\', \'SYYYY-MM-DD HH24:MI:SS\'))';
$stid = oci_parse($conn, $sql);
$r = oci_execute($stid);
//插入基站信息表
$sql='INSERT INTO "DSSC2"."ADM_DEV_RFID_CHN"("ID", "DEVICE_CODE","CHANNEL_NAME", "STAT" ,"GPS_X","GPS_Y","TYPE")
VALUES(DSSC2.SEQ_ADM_DEV_RFID_CHN.nextval,\''.$deivce_code.'\',\''.$data['DEVICE_NAME'].'\',1,\''.$data['longitude'].'\',\''.$data['latitude'].'\',1)';
$stid = oci_parse($conn, $sql);
$r = oci_execute($stid);
oci_free_statement($stid);
return $r;
}
/**
* 更新
* @time 2022年01月20日 10:55
* @param Request $request
* @param $id
*/
public function update(Request $request, $id)
{
$params=$request->param();
$params['LOGIN_NAME']=strtoupper($params['LOGIN_NAME']);
//UPDATE DSSC2.ADM_DEV A SET A.DEVICE_NAME = '1014测试插入4' WHERE A.DEVICE_CODE = '79933';
//UPDATE DSSC2.ADM_DEV_RFID_CHN A SET A.CHANNEL_NAME = '1014测试插入4' WHERE A.DEVICE_CODE ='79933';
$conn=getOracleConnect();
//更新
$sql='UPDATE DSSC2.ADM_DEV SET DEVICE_NAME = \''.$params['DEVICE_NAME'].'\',LOGIN_NAME=\''.$params['LOGIN_NAME'].'\' WHERE DEVICE_CODE = \''.$id.'\' ';
$stid = oci_parse($conn, $sql);
$r = oci_execute($stid);
$sql='UPDATE DSSC2.ADM_DEV_RFID_CHN SET CHANNEL_NAME = \''.$params['DEVICE_NAME'].'\',GPS_X='.$params['longitude'].',GPS_Y='.$params['latitude'].' WHERE DEVICE_CODE = \''.$id.'\' ';
$stid2 = oci_parse($conn, $sql);
$r2 = oci_execute($stid2);
return CatchResponse::success('修改成功');
}
/**
* 删除
* @time 2022年01月20日 10:55
* @param $id
*/
public function delete($id) : \think\Response
{
// $ids=Utils::stringToArrayBy($id);
$conn=getOracleConnect();
$sql='DELETE FROM DSSC2.ADM_DEV WHERE DEVICE_CODE in ('.$id.')';
$stid = oci_parse($conn, $sql);
$r = oci_execute($stid);
$sql='DELETE FROM DSSC2.ADM_DEV_RFID_CHN WHERE DEVICE_CODE in ('.$id.')';
$stid = oci_parse($conn, $sql);
$r = oci_execute($stid);
return CatchResponse::success($r);
}
public function getdeviceListByStation(Request $request){
$params=$request->param();
if(!$params['station_code']){
return CatchResponse::success('');
}
$cond=[];
if($params['station_code']){
$cond['RF_ID']=['=',$params['station_code']];
}
$start_time = date('Y-m-d 00:00:00',time());
$end_time = date('Y-m-d 23:59:59',time());
if(isset($params['timeRange']) && $params['timeRange'] != ''){
$start_time=date('Y-m-d H:i:s',strtotime($params['timeRange'][0]));
$end_time=date('Y-m-d H:i:s',strtotime($params['timeRange'][1]));
$cond['RF_DATE']=['timeRange',$start_time,$end_time];
}
$count=queryOracleCount('DSSC2.W_DW_RF_RECORD',$cond);
$cond['page']=isset($param['page'])?$param['page']:1;
$cond['limit']=isset($param['limit'])?$param['limit']:10;
$rows=queryOracleSelect('(SELECT * FROM DSSC2.W_DW_RF_RECORD ORDER BY ID DESC) a',$cond,'a.RF_FLAGID,a.RF_STAT,to_char(a.RF_DATE,\'yyyy-mm-dd hh24:mi:ss\') RF_DATE');
foreach($rows as &$val){
//状态: 0- 未知,1 - 进入,2 - 离开
if($val['RF_STAT']==1){
$val['RF_STAT_TEXT']='进入';
}elseif($val['RF_STAT']==2){
$val['RF_STAT_TEXT']='离开';
}else{
$val['RF_STAT_TEXT']='未知';
}
}
$response=[
'code'=>10000,
'message'=>'查询成功',
'count'=>$count,
'data'=>$rows,
'current'=>isset($params['page'])?(int)$params['page']:1,
'limit'=>isset($params['limit'])?(int)$params['limit']:10,
];
return $response;
// $cond=[];
// $params=$request->param();
// if(!$params['station_code']){
// return CatchResponse::success('');
// }
// if($params['station_code']){
// $cond[]=['RF_ID','=',$params['station_code']];
// }
// $start_time = date('Y-m-d 00:00:00',time());
// $end_time = date('Y-m-d 23:59:59',time());
// if(isset($params['timeRange']) && $params['timeRange'] != ''){
// $start_time=date('Y-m-d H:i:s',strtotime($params['timeRange'][0]));
// $end_time=date('Y-m-d H:i:s',strtotime($params['timeRange'][1]));
// $cond[]=['RF_DATE','between',[$start_time,$end_time]];
// }
// $list=$this->rfRecordModel->getList($cond);
// return CatchResponse::paginate($list);
}
/**
* 导入设备
*
* @time 2022年02月15日
* @param Excel $excel
* @param DeviceExport $deviceExport
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @return \think\response\Json
*/
public function importStation(Request $request)
{
$url = $request->post('url');
if (!$url) {
return CatchResponse::fail('请上传文件');
}
$creator_id = $request->post('creator_id');
//解析地址
$parse_url = parse_url($url)['path'];
//载入excel表格
$objPHPExcel = IOFactory::load(public_path() . $parse_url);
//获取表名，一维数组，值是表名。如：array('sheet1', 'sheet2', 'sheet3')
// $nameArr = $objPHPExcel->getSheetNames();
//获取表的数量
$sheetCount = $objPHPExcel->getSheetCount();
$fail = 0; //失败条数
$success = 0; //成功条数
$total = 0; //总共设备数
$data = []; //设备数据
//循环读取每一张表
for ($index = 0; $index < $sheetCount; $index++) {
//设置当前要读取的表
$sheet = $objPHPExcel->getSheet($index); //excel中的第一张sheet
// var_dump($sheet);exit;
$highestRow = $sheet->getHighestRow(); // 取得总行数
// var_dump($highestRow);
if ($highestRow <= 1) {
continue;
}
$total += $highestRow - 1;
for ($j = 2; $j <= $highestRow; $j++) {
$arr = array(); //每条设备信息
$arr['LOGIN_NAME'] = strtoupper(trim($sheet->getCell("A" . $j)->getFormattedValue()));
$arr['longitude'] = trim($sheet->getCell("B" . $j)->getFormattedValue());
$arr['latitude'] = trim($sheet->getCell("C" . $j)->getFormattedValue());
$arr['DEVICE_NAME'] = trim($sheet->getCell("D" . $j)->getFormattedValue());
$r=$this->execSaveStation($arr);
if($r){
$success++;
}else{
$fail++;
}
// var_dump($arr);
// array_push($data,$arr);
}
}
// array_unique($data, SORT_REGULAR);
// // var_dump($data);return CatchResponse::success();
// $count = $this->deviceModel->limit(100)->insertAll($data);
// if ($success = $count) {
return CatchResponse::success('共' . $total . '条数据,成功' . $success . '条，失败' . $fail . '条');
// }
// return CatchResponse::success(['error' => true, 'msg' => '导入失败']);
}
}