catch_nbfd_admin/catch/device/controller/Station.php

<?php

namespace catchAdmin\device\controller;

use catcher\base\CatchRequest as Request;
use catcher\CatchResponse;
use catcher\base\CatchController;
use catchAdmin\device\model\Station as stationModel;
use catcher\Utils;
use catcher\library\excel\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Env;
use think\facade\Db;
use PDO;
use think\facade\Cache;
class Station extends CatchController
{
    protected $stationModel;
    
    public function __construct(StationModel $stationModel)
    {
        $this->stationModel = $stationModel;
    }
    
    /**
     * 列表
     * @time 2022年10月26日 10:32
     * @param Request $request 
     */
    public function index(Request $request) : \think\Response
    {
        return CatchResponse::paginate($this->stationModel->getList());
    }
    
    /**
     * 保存信息
     * @time 2022年10月26日 10:32
     * @param Request $request 
     */
    public function save(Request $request) : \think\Response
    {
        $params=$request->post();
        $shortcode=$this->stationModel->order('shortcode','desc')->value('shortcode');
        $params['shortcode']=$shortcode+1;
        // $gcjLoc = \algorithm\Geometry::convertBd09ToGcj02((float)$params['latitude'],(float)$params['longitude']);
        $gcjLoc = \algorithm\Geometry::wgsTOgcj((float)$params['latitude'],(float)$params['longitude']);
        $params['longitude']=$gcjLoc['lng'];
        $params['latitude']=$gcjLoc['lat'];
        $params['DATA_TYPE']='station_save';
        pushDataToRedisList($params);
        return CatchResponse::success($this->stationModel->storeBy($params));
    }
    
    /**
     * 读取
     * @time 2022年10月26日 10:32
     * @param $id 
     */
    public function read($id) : \think\Response
    {
        return CatchResponse::success($this->stationModel->findBy($id));
    }
    
    /**
     * 更新
     * @time 2022年10月26日 10:32
     * @param Request $request 
     * @param $id
     */
    public function update(Request $request, $id) : \think\Response
    {
        $params=$request->post();
        // $gcjLoc = \algorithm\Geometry::convertBd09ToGcj02((float)$params['latitude'],(float)$params['longitude']);
        $gcjLoc = \algorithm\Geometry::wgsTOgcj((float)$params['latitude'],(float)$params['longitude']);
        $params['longitude']=$gcjLoc['lng'];
        $params['latitude']=$gcjLoc['lat'];
        $params['update_id']=$id;
        $params['DATA_TYPE']='station_update';
        pushDataToRedisList($params);
        return CatchResponse::success($this->stationModel->updateBy($id, $params));
    }
    
    /**
     * 删除
     * @time 2022年10月26日 10:32
     * @param $id
     */
    public function delete($id) : \think\Response
    {
        $ids=explode(',',$id);
        $shortcode=$this->stationModel->whereIn('id',$ids)->column('shortcode');
        $params['delete_code']=$shortcode;
        $params['DATA_TYPE']='station_save';
        pushDataToRedisList($params);
        return CatchResponse::success($this->stationModel->deleteBy($id,true));
    }

    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($params['page'])?$params['page']:1;
        $cond['limit']=isset($params['limit'])?$params['limit']:10;
        
        $rows=queryOracleSelect('(SELECT * FROM DSSC2.W_DW_RF_RECORD  ORDER BY RF_DATE 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 = []; //设备数据
        //循环读取每一张表
        $shortcode=$this->stationModel->order('shortcode','desc')->value('shortcode');
        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['mac'] = strtoupper(trim($sheet->getCell("A" . $j)->getFormattedValue())); 
                if(empty($arr['mac']) ||  strlen($arr['mac'])!=8){
                    $fail++;
                    debug_log('importStationError','MAC格式格式不正确,MAC为：'.$arr['mac']);
                    continue;
                }
                $arr['longitude'] = trim($sheet->getCell("B" . $j)->getFormattedValue());  
                $arr['latitude'] = trim($sheet->getCell("C" . $j)->getFormattedValue());  
                $arr['name'] = trim($sheet->getCell("D" . $j)->getFormattedValue());  
                $arr['created_at']=time();

                $shortcode++;
                $arr['shortcode']=$shortcode;
               
                array_push($data,$arr);
                $arr['DATA_TYPE']='station_save';
                pushDataToRedisList($arr);
               
            }
        }
        // array_unique($data, SORT_REGULAR);
        // // var_dump($data);return CatchResponse::success();
        $count = $this->stationModel->limit(100)->insertAll($data);
        if ($count) {

            return CatchResponse::success('共' . $total . '条数据,成功' . $count . '条，失败' . ($total-$count) . '条');

        }
        return CatchResponse::success(['error' => true, 'msg' => '导入失败']);
    }

    /**
     * 基站地图额
     * @time 2022年01月20日 10:09
     * @param Request $request 
     */
    public function getMapList(Request $request) 
    {
        return CatchResponse::paginate($this->stationModel->getMapList());
    }
    
    /**
     * 所有列表
     * @time 2022年01月20日 10:09
     * @param Request $request 
     */
    public function getAllList(Request $request) 
    {
        $params=$request->param();
        $start=$params['start']?$params['start']*4000:0;
        $limit=$params['limit']?:4000;
        $total=$this->stationModel->count();
        return CatchResponse::success(['list'=>$this->stationModel->getAllList($start,$limit),'total'=>$total]);
    }
    /**
     * 导出列表
     * @time 2022年01月20日 10:09
     * @param Request $request 
     */
    public function getExportList(Request $request) 
    {
        return CatchResponse::success($this->stationModel->getExportList());
    }
    public function getAllListForTrans(Request $request) {
        $start=0;
        $limit=40000;
        $list=$this->stationModel->getAllList($start,$limit);
        $rows=[];
        foreach($list as $val){
            $item=['key'=>$val['mac'],'label'=>$val['name'] ];
            $rows[]=$item;
        }
        return CatchResponse::success($rows);
    }
}