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 catchAdmin\device\model\StationPhoto;
use catcher\Utils;
use catcher\library\excel\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use catchAdmin\device\excel\StationExport;
use think\facade\Db;
use PDO;
class Station extends CatchController
{
    protected $stationModel;
    
    public function __construct(StationModel $stationModel)
    {
        $this->stationModel = $stationModel;
    }
    
    /**
     * 列表
     * @time 2022年01月20日 10:09
     * @param Request $request 
     */
    public function index(Request $request) 
    {
        // header('Content-Type: text/html; charset=utf-8');
        // header('Content-Type: text/html; charset=gb2312');
            $param=$request->param();
            // var_dump($param);
            $cond=[];
            if($param['shortcode']){
                $cond['DEVICE_CODE']=['like',$param['shortcode']];
            }
            if($param['name']){
                $param['name'] = mb_convert_encoding($param['name'],  'GBK','UTF-8');
                $cond['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('DSSC2.ADM_DEV',$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.UPDATE_DATE,\'yyyy-mm-dd hh24:mi:ss\') UPDATE_DATE');
            
            
            foreach($rows as &$val){
                $val['DEVICE_NAME'] = mb_convert_encoding($val['DEVICE_NAME'], 'UTF-8', 'GBK');
                $val['IS_ONLINE_TEXT']=$val['IS_ONLINE']?'在线':'离线';
                // $val['UPDATE_DATE'] = mb_convert_encoding($val['UPDATE_DATE'], 'UTF-8', 'GBK');
                // $val['CREATE_DATE'] = mb_convert_encoding($val['CREATE_DATE'], 'UTF-8', 'GBK');
                $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;
        // $field = $request->get('field')?:'id';
        // $order = $request->get('order')?:'desc';
        // return CatchResponse::paginate($this->stationModel->getStationList($field,$order));
    }
    
    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('DSSC2.W_DW_RF_RECORD',$cond,'a.RF_FLAGID,a.RF_STAT,to_char(a.RF_DATE,\'yyyy-mm-dd hh24:mi:ss\') RF_DATE');
        foreach($rows as &$val){
            $val['RF_DATE'] = mb_convert_encoding($val['RF_DATE'], 'UTF-8', 'GBK');
        }
        $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;
   
    }
    /**
     * 保存信息
     * @time 2022年01月20日 10:09
     * @param Request $request 
     */
    public function save(Request $request) : \think\Response
    {
        $data = $request->post();
        //判断参数
        if(!$data['department_id']){
            return CatchResponse::fail('请选择所属部门');
        }
        if(!$data['model']){
            return CatchResponse::fail('请选择基站型号');
        }
        $data['mac'] = trim($data['mac']);
        if(!$data['mac']){
            return CatchResponse::fail('请输入基站Mac');
        }
        if (!preg_match('/^[0-9a-zA-Z]{6,15}$/i',$data['mac'])){
            return CatchResponse::fail('基站为6-15位字母数字');
        }
         //基站简码
         $data['shortcode'] = substr($data['mac'],-6,6);
         //校验重复
         if($this->stationModel->where('mac',$data['mac'])->count()){
             return CatchResponse::fail('该基站已存在');
         }
        //开启事务
        Db::startTrans();
        //开局状态
        if($data['type'] == 1){
            $data['open_status'] = 0;
        }else{
            $data['open_status'] = 1;
            //开局时间
            $data['open_time'] = date('Y-m-d H:i:s');
            //开局员工
            $data['open_user_id'] = $data['creator_id'];
        }
        $station_id = $this->stationModel->storeBy($data);
        if(!$station_id){
            return CatchResponse::fail('添加失败');
        }
         //基站图片
         if(isset($data['install_photo'])){
            $photo = array(
                'url'=>$data['install_photo'],
                'type'=>'station',
                'station_id'=>$station_id,
                'creator_id'=>$data['creator_id']
            );
            $res = (new StationPhoto())->storeBy($photo);
            if(!$res){
                Db::rollback();
                return CatchResponse::fail('添加失败');
            }
        }
        Db::commit();
        return CatchResponse::success();
    }
    
    /**
     * 读取
     * @time 2022年01月20日 10:09
     * @param $id 
     */
    public function read($id) : \think\Response
    {
        return CatchResponse::success($this->stationModel->findBy($id));
    }
    
    /**
     * 更新
     * @time 2022年01月20日 10:09
     * @param Request $request 
     * @param $id
     */
    public function update(Request $request, $id) : \think\Response
    {
        return CatchResponse::success($this->stationModel->updateBy($id, $request->post()));
    }
    
    /**
     * 删除
     * @time 2022年01月20日 10:09
     * @param $id
     */
    public function delete($id) : \think\Response
    {
        return CatchResponse::success($this->stationModel->deleteBy($id,true));
    }
     /**
     * 导出
     *
     * @time 2022年02月15日
     * @param Excel $excel
     * @param StationExport $stationExport
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @return \think\response\Json
     */
    public function export_station(Excel $excel, StationExport $StationExport)
    {
        //  var_dump(Utils::publicPath('export/users'));//导出路径
        return CatchResponse::success($excel->save($StationExport, Utils::publicPath('export/stations'), 'local', '基站列表'));
    }
      /**
     * 导入设备
     *
     * @time 2022年02月15日
     * @param Excel $excel
     * @param DeviceExport $deviceExport
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @return \think\response\Json
     */
    public function import_station(Request $request)
    {
        $url = $request->post('url');
        if (!$url) {
            return CatchResponse::fail('请上传文件');
        }
        $depart_id = $request->post('depart_id');
        if (!$depart_id) {
            return CatchResponse::fail('请选择部门');
        }
        $model_id = $request->post('model_id');
        if (!$model_id) {
            return CatchResponse::fail('请选择型号');
        }
        $creator_id = $request->post('creator_id');
        //解析地址
        $parse_url = parse_url($url)['path'];
        //载入excel表格 
        $objPHPExcel = IOFactory::load(public_path() . $parse_url);
        // var_dump($objPHPExcel);
        //获取表名，一维数组，值是表名。如：array('sheet1', 'sheet2', 'sheet3')
        // $nameArr = $objPHPExcel->getSheetNames();
        // var_dump($nameArr);
        //获取表的数量
        $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 <= 2) {
                continue;
            }
            $total += $highestRow - 2;
            for ($j = 3; $j <= $highestRow; $j++) {
                $arr = array(); //每条基站信息
                $arr['mac'] = trim($sheet->getCell("A" . $j)->getFormattedValue());  //imei
                if ($arr['mac'] && mb_strlen($arr['mac']) != 12) {
                    $fail++;
                    $msg = '导入基站：' . $arr['mac'] . '失败：mac编号格式不正确';
                    $this->importFailLog($msg);
                    continue;
                }
                if ($arr['mac']) {
                    $isHas = $this->stationModel->where('mac',$arr['mac'])->count();
                    if($isHas){
                        $fail++;
                        $msg = '导入基站：' . $arr['mac'] . '失败：mac编号已存在';
                        $this->importFailLog($msg);
                        continue;
                    }
                }
                $arr['remark']   = trim($sheet->getCell("B" . $j)->getFormattedValue()); //备注
                $arr['department_id'] = $depart_id;
                $arr['shortcode'] = substr($arr['mac'],-6);
                $arr['creator_id'] = $creator_id;
                $arr['model'] = $model_id;
                $arr['name'] = '基站'.substr($arr['mac'],-4);
                $arr['created_at'] = time();
                $arr['updated_at'] = time();
               array_push($data,$arr);
            }
        }
        array_unique($data, SORT_REGULAR);
        // var_dump($data);return CatchResponse::success();
        $count = $this->stationModel->limit(100)->insertAll($data);
        if ($success = $count) {

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

        }
        return CatchResponse::success(['error' => true, 'msg' => '导入失败']);
    }
    /**
     * 导入设备失败日志
     */
    public function importFailLog($msg)
    {
        $file = runtime_path() . '/log/' . date("Ymd", time()) . "/import_stations_fail.log";
        $folder = dirname($file);
        if (!is_dir($folder)) {
            mkdir($folder, 0777, true);
        }
        file_put_contents($file, '[' . date('Y-m-d H:i:s') . ']' . $msg . PHP_EOL, FILE_APPEND);
    }

//      ┏┛ ┻━━━━━┛ ┻┓
//      ┃　　　　　　 ┃
//      ┃　　　━　　　┃
//      ┃　┳┛　  ┗┳　┃
//      ┃　　　　　　 ┃
//      ┃　　　┻　　　┃
//      ┃　　　　　　 ┃
//      ┗━┓　　　┏━━━┛
//        ┃　　　┃   神兽保佑
//        ┃　　　┃   代码无BUG！
//        ┃　　　┗━━━━━━━━━┓
//        ┃　　　　　　　    ┣┓
//        ┃　　　　         ┏┛
//        ┗━┓ ┓ ┏━━━┳ ┓ ┏━┛
//          ┃ ┫ ┫   ┃ ┫ ┫
//          ┗━┻━┛   ┗━┻━┛
}