catch_nbfd_admin/app/common.php

<?php
// 应用公共文件
use think\facade\Env;
use think\facade\Db;
use Aliyun\OTS\Consts\OperationTypeConst;
use Aliyun\OTS\Consts\PrimaryKeyTypeConst;
use Aliyun\OTS\Consts\RowExistenceExpectationConst;
use Aliyun\OTS\OTSClient;

    /**
     * 查询Gps股轨迹 表格存储
     */
    function queryTableStoreGpsRoute($options){
        
        if(!isset($options['device_number']) &&  $options['device_number'] !=''){
            return array('success'=>false,'message'=>'缺少设备号');
        }
        $device_number=$options['device_number'];
        if(!isset($options['start_time']) &&  $options['start_time']>0){
            return array('success'=>false,'message'=>'缺少起始时间');
        }
        $start_time=$options['start_time'];
        if(!isset($options['end_time']) &&  $options['end_time']>0){
            $end_time=time();
        }else{
            $end_time=$options['end_time'];
        }
        
        if(!isset($options['table_name']) &&  !$options['table_name']){
            return array('success'=>false,'message'=>'缺少表名参数');
        }
        $table_name=$options['table_name'];

        $table_config=Db::table('sys_config')->where('type','table_store')->column('fieldValue','field');
        if(!isset($table_config['EndPoint']) || !isset($table_config['AccessKeyID']) || !isset($table_config['AccessKeySecret']) || !isset($table_config['InstanceName'])){
            return array('success'=>false,'message'=>'未配置表格存储');
        }
        $otsClient = new OTSClient (array (
            'EndPoint' => $table_config['EndPoint'],
            'AccessKeyID' => $table_config['AccessKeyID'],
            'AccessKeySecret' => $table_config['AccessKeySecret'],
            'InstanceName' =>  $table_config['InstanceName'],
        ));
        $otsClient->getClientConfig()->debugLogHandler = null;
        $otsClient->getClientConfig()->errorLogHandler = null;
        // var_dump($otsClient);
        ini_set('memory_limit', '512M');

        $startPK = array (
            array('DeviceNumber',$device_number),
            array('Timestamp', $start_time)
            // array('Timestamp', 1620885120)
        );
        $endPK = array (
            array('DeviceNumber', $device_number),
            array('Timestamp', $end_time)
            // array('Timestamp', 1620885441)
        );
        if(isset($options['limit']) &&  $options['limit']>0){
            $limit = $options['limit']; 
        }else{
            $limit = 5500; 
        }
        

       
        $rows=[];
        while (! empty ($startPK) && $limit > 0) {

            $request = array (
                'table_name' => $table_name,
                'max_versions' => 1,
                'direction' => 'BACKWARD', // 方向可以为 FORWARD 或者 BACKWARD
                'inclusive_start_primary_key' => $endPK, // 开始主键
                'exclusive_end_primary_key' => $startPK, // 结束主键
                'limit' => $limit
            );
            
            
            $response = [];
            try {
                $response = $otsClient->getRange($request);
               
            } catch (\Exception $e) {
               var_dump($e->getMessage());
            }
            
            if (!isset($response['rows'])){
                return CatchResponse::success([]);
            }
            
            foreach ($response['rows'] as $rowData) {
                    // $str=($limit-4000)/2000;
                    $item=[];
                    foreach($rowData['primary_key'] as $keyItem){
                        $item[$keyItem[0]]=$keyItem[1];
                    }
                    foreach($rowData['attribute_columns'] as $colItem){
                        $item[$colItem[0]]=$colItem[1];
                    }
                    $item['PassTime']=date('Y-m-d H:i:s',$item['Timestamp']);
                    $rows[]=$item;
                    $limit --;

              // 处理每一行数据
            }
          
              $startPK = $response['next_start_primary_key'];
              // 如果 next_start_primary_key 不为空并且 limit > 0 则循环继续
        }
        // if(!$rows){
        //     return array('success'=>true,'message'=>'查询成功','data'=>[]);
        // }
        // $arr= [];
        
        // foreach($rows  as $k=> $v){
        //     // if( !isset($v['Latitude']) ||  !isset($v['Longitude']) || $v['Latitude']<0.065 || $v['Longitude']<0.065){
        //     //     continue;
        //     // }
        //     array_push($arr, $rows[$k]);
        // }
        return array('success'=>true,'message'=>'查询成功','data'=>$rows);
    }
    /**
     * 添加轨迹到表格存储
     * @param $data 存储数据
     * @param $options 配置信息
     */
    function addGpsRouteToTs($data,$options){
        if(!isset($options['table_name']) &&  !$options['table_name']){
            return array('success'=>false,'message'=>'缺少表名参数');
        }
        // 判断主键字段是否存在
        if(!isset($options['primary_key']) || !$options['primary_key']){
            return array('success'=>false,'message'=>'primary_key not exists');
        }
        $primary_keys = explode(',',$options['primary_key']);
        if(empty($primary_keys)){
            return array('success'=>false,'message'=>'primary_key format error,right format is a String like："Key1,Key2"');
        }
        $table_name=$options['table_name'];


        $table_config=Db::table('sys_config')->where('type','table_store')->column('fieldValue','field');
        if(!isset($table_config['EndPoint']) || !isset($table_config['AccessKeyID']) || !isset($table_config['AccessKeySecret']) || !isset($table_config['InstanceName'])){
            return array('success'=>false,'message'=>'未配置表格存储');
        }
        $otsClient = new OTSClient (array (
            'EndPoint' => $table_config['EndPoint'],
            'AccessKeyID' => $table_config['AccessKeyID'],
            'AccessKeySecret' => $table_config['AccessKeySecret'],
            'InstanceName' =>  $table_config['InstanceName'],
  
        ));
        //
        $otsClient->getClientConfig()->debugLogHandler = null; //关闭日志函数打印
        $otsClient->getClientConfig()->errorLogHandler = null;
        $rows=array();
        foreach($data as $value){
          
            $fullKey = true; // 主键齐全
            $primary_info = array(); // 主键信息
            $attribute_info=array();
            // 跳过主键不全的数据
            foreach($primary_keys as $key){
                if(!$value[$key]){
                    $fullKey = false;
                    break;
                }
                // $primary_info[$key] = $value[$key];
                $primary_info[] = [$key,$value[$key]];
                unset($value[$key]);
            }

            if(!$fullKey){
                continue;
            }
            //转换多余参数 键值对格式
            foreach($value as $key=>$val){
                $attribute_info[]=[$key,$val];
            }
            $row= array ( // 第一行
                'operation_type' => OperationTypeConst::CONST_PUT,            //操作是PUT
                'condition' => RowExistenceExpectationConst::CONST_IGNORE,
                'primary_key' =>  $primary_info,
                'attribute_columns' => $attribute_info
            );
            $rows[]=$row;
        }
        $request = array (
            'tables' => array (
                array (
                    'table_name' => $table_name,
                    'rows' =>$rows,
                )
            )
        );
        // $response =[];
        $response = $otsClient->batchWriteRow($request);
        $res=processWriteError($response);
        
        return $res;

    }
    /**
     * 判断服务器返回是否有错误 
     */
    function processWriteError($response){
        $hasError = 0;

        foreach ($response['tables'] as $tableData) {
            // 处理这个表下的PutRow返回的结果
            $putRows = $tableData['rows'];
            foreach ($putRows as $rowData) {
              if ($rowData['is_ok']) {
                continue;
              } else {
                $hasError++;
                debug_log('addGpsToTsError',$tableData['table_name'].' error_code: '.$rowData['error']['code'].' message: '.$rowData['error']['message']);
                //print "Error: {$rowData['error']['code']} {$rowData['error']['message']}\n";
              }
            }
          
        }
        if($hasError > 0){
            return array('success'=>false);
        }else{
            return array('success'=>true);
        }
    }
    /**
     * 检测是否进出围栏 
     * @param $info 定位信息
     * @param $device_info 设备信息
     */
    function checkIsCrollFence($info, $device_info){
        $log_filename = 'pushFenceMsgToYys';
        $app_id = Env::get('wechat.wechat_appid');
        $appsecret =Env::get('wechat.wechat_appsecret');
        $template_id = Env::get('wechat.wx_fence_template_id');
        if (!$app_id || !$appsecret) {
            debug_log($log_filename,'WECHAT_APPID and WECHAT_APPSECRET required');
            return array('success'=>false,'message'=>'WECHAT_APPID and WECHAT_APPSECRET required');
        }
        if (!$info['Longitude'] || !$info['Latitude']) {
            debug_log($log_filename,$info['DeviceId'] .' location incomplete');
            return array('success'=>false,'message'=>$info['DeviceId'] .' location incomplete');
        }
        //设备未绑定车辆不检测
        if (!$device_info['bind_id']) {
            debug_log('pushFenceMsgToYys',$info['DeviceId'].' has not bind any vehicle');
            return array('success'=>false,'message'=>$info['DeviceId'].' has not bind any vehicle');
        }
        $vehicle_info=Db::table('vehicles')->where('id',$device_info['bind_id'])->find();
        if(!$vehicle_info || !$vehicle_info['user_id']){
            debug_log('pushFenceMsgToYys',$vehicle_info['id'].' vehicle has not bind any user');
            return array('success'=>false,'message'=>$vehicle_info['id'].' vehicle has not bind any user');
        }
        // 是否跨越围栏
        $is_cross = false;		
        $fence_model = Db::table('fences');
        $user_model = Db::table('users');
        $alarm_report_model = Db::table('alarm_report');
        // $wx_tmp =  new \Jiaruan\WxTmp();
        $wx_tmp = new \Jiaruan\WxTmp();
        $time = date('H:i:s'); // 当前时间
        // 保存到 alarm_report 表中
        $user_info=Db::table('users')->where(array('id'=>$vehicle_info['user_id']))->find();
        
        $fence_alarm_data = array(
            'device_number' => $device_info['imei'],
            'alarm_time' => date('Y-m-d H:i:s',$info['DeviceTime']),
            'creator_id' => $device_info['creator_id'],
            'created_at' => null,
            'longitude' => $info['Longitude'],
            'latitude' => $info['Latitude'],
            'is_sent' => 1,//默认1 需要推送
            'fence_id' => null,
            'alarm_type' => null, // 进围栏-fence_in,出围栏-fence_out
        );
        $lnglat = [ // 基站坐标
            'lng' => $info['Longitude'],
            'lat' => $info['Latitude'],
        ];
        // 查出所有平台围栏
        $level_str = Db::table('departments')->where(array('id'=>$device_info['department_id']))->value('level');
        $levelArr=explode('-',$level_str);
        array_push($levelArr,$device_info['department_id']);

        $where=[
            ['departments', 'in', $levelArr],
            ['fence_type', '=', 0],
        ];
        $fence_list = $fence_model->where($where)->select();
        // var_dump($fence_list);
        foreach ($fence_list as $fence) {
            $check_in = false; // 检查进围栏
            $check_out = false;// 检查出围栏
            $need_sent_alarm=false;
            if ( ($fence['is_check_in']!=0) && !empty($fence['in_fence_time_area'])) {
                $check_in = true;
            }
            if ( ($fence['is_check_out']!=0) && !empty($fence['out_fence_time_area'])) {
                $check_out = true;
            }
            // 没有开启进出检测的跳过
            if (!$check_in && !$check_out) {
                continue;
            }
            // 没有设置部门和推送用户的跳过
            if (empty($fence['departments']) || empty($fence['push_users'])) {
                continue;
            }

            
 
            $cond=[
                ['id', 'in',json_decode($fence['push_users'], true)],
            ];
            $push_users = $user_model->where($cond)->column('realname','wx_open_id');//第二个字段为索引
            if (empty($push_users)) {
                continue;
            }
            $content = ''; // 推送内容
            // 围栏形状数据为空跳过
            $fence_info = json_decode($fence['fence_info'], true);
            if (empty($fence_info)) {
                continue;
            }
            // 在检查时间内，根据围栏形状和基站位置判断是否在围栏内
            $is_in_fence = isCrossFence($fence['fence_shape'], $fence_info, $lnglat);
            $fence_alarm_data['fence_id'] = $fence['id'];
            // 进围栏
            $sent_interval=$fence['sent_interval'];
            $check_cond=[
                ['device_number', '=',$fence_alarm_data['device_number']],
                ['fence_id', '=',$fence_alarm_data['fence_id']],
                ['created_at', '>',time()-$sent_interval],
            ];

            if ($check_in && $is_in_fence) {
                $res = bmap_geocoding($info['Latitude'],$info['Longitude']);
                if($res['success']){
                    $fence_alarm_data['address'] = $res['address'];
                }else{
                    $fence_alarm_data['address'] = $info['Longitude'].','.$info['Latitude'];
                }
                // 检查是否在推送时间段
                $time_area = json_decode($fence['in_fence_time_area'], true);
                foreach ($time_area as $area) {
                    if ($time < $area[0] || $time > $area[1]) {
                        continue;
                    }
                    $is_cross = true;
                    $need_sent_alarm=true;
                    break;
                }

                if($need_sent_alarm){
                    // 添加到上报记录表
                    $fence_alarm_data['alarm_type'] = 'fence_in';
                    $fence_alarm_data['created_at'] = time();
                    
                    
                    // $check_cond['alarm_type']='fence_in';
                    // $check_cond['is_sent']=1;

                    $check_cond[]=['alarm_type','=','fence_in'];
                    $check_cond[]=['is_sent','=','1'];
                    
                    //检测是否需要推送
  
                    if(Db::table('alarm_report')->where($check_cond)->count()){
                        $fence_alarm_data['is_sent']=0;
                        $alarm_report_model->save($fence_alarm_data);
                        debug_log($log_filename,'add alarm record failed,Msg：fence_in 围栏推送间隔未达到 device：'.$fence_alarm_data['device_number'].' fence_id:'.$fence_alarm_data['fence_id']);
                        continue;
                    }

                    $insertId = $alarm_report_model->insertGetId($fence_alarm_data);
                    //保存告警记录
                    // saveAlarmRecords($fence_alarm_data);
                    // 推送内容
                    $content = "检测到有设备在{$fence_alarm_data['alarm_time']}进入围栏({$fence['name']})范围，当前位于{$fence_alarm_data['address']}";
                    if (!$insertId) {
                        debug_log($log_filename,'add alarm record failed,SQL：'.$alarm_report_model->getLastSql());
                    }
                }
                
            } else if ($check_out && !$is_in_fence) { // 出围栏
                
                $res = bmap_geocoding($info['Latitude'],$info['Longitude']);
                if($res['success']){
                    $fence_alarm_data['address'] = $res['address'];
                }else{
                    $fence_alarm_data['address'] = $info['Longitude'].','.$info['Latitude'];
                }
                // 检查是否在推送时间段
                $time_area = json_decode($fence['out_fence_time_area'], true);
                foreach ($time_area as $area) {
                    if ($time < $area[0] || $time > $area[1]) {
                        continue;
                    }
                    $is_cross = true;
                    $need_sent_alarm=true;
                    
                }
                if($need_sent_alarm){
                    // 添加到上报记录表
                    $fence_alarm_data['alarm_type'] = 'fence_out';
                    $fence_alarm_data['created_at'] = time();
                    
                    $check_cond[]=['alarm_type','=','fence_out'];
                    $check_cond[]=['is_sent','=','1'];
                    // $check_cond['alarm_type']='fence_out';
                    // $check_cond['is_sent']=1;
                    //检测是否需要推送
                    if(Db::table('alarm_report')->where($check_cond)->count()){
                        $fence_alarm_data['is_sent']=0;
                        $alarm_report_model->save($fence_alarm_data);
                        debug_log($log_filename,'add alarm record failed,Msg：fence_in 围栏推送间隔未达到 device：'.$fence_alarm_data['device_number'].' fence_id:'.$fence_alarm_data['fence_id']);
                        continue;
                    }
                    $insertId = $alarm_report_model->insertGetId($fence_alarm_data);
                    //保存告警记录
                    // saveAlarmRecords($fence_alarm_data);
                    // 推送内容
                    $content = "检测到有设备在{$fence_alarm_data['alarm_time']}离开围栏({$fence['name']})范围，当前位于{$fence_alarm_data['address']}";
                    
                    if (!$insertId) {
                        debug_log($log_filename,'add alarm record failed,SQL：'.$alarm_report_model->getLastSql());
                    }
                }
                
            }
            // 不存在推送内容说明未跨越围栏，跳过
            if (!$content) {
                continue;
            }
            
            $wx_push_data = [
                'template_id' => $template_id,
                'data' => [
                    'first' => [ 'value' => $content, 'color' => '#173177' ],
                    'keyword1' => [ 'value' => $info['DeviceId'], 'color' => '#173177' ],
                    'keyword2' => [ 'value' => $user_info['realname'], 'color' => '#173177' ],
                    'keyword3' => [ 'value' => $fence['name'], 'color' => '#173177' ],
                    'keyword4' => [ 'value' => date('Y-m-d H:i:s',$info['DeviceTime']), 'color' => '#173177' ],
                    'remark' => [ 'value' => '', 'color' => '#173177' ],
                ],
            ];	
            
            // 开始推送
            foreach ($push_users as $push_key=>$push_value) {
                if(!$push_key){
                    continue;
                }
                $wx_push_data['touser'] = $push_key;
                // 开始推送
                $res='待完成';
                // $res = $wx_tmp->sendMessage($wx_push_data);

                $res= $wx_tmp->sendMessage($wx_push_data);
                $push_res_data = array(
                        'username'=>$push_value,
                        'device_number'=> $device_info['imei'],
                        'result'=> json_encode($res),
                        'created_at'=>time(),
                        'alarm_id'=>$insertId,
                        'alarm_type'=>$fence_alarm_data['alarm_type'],
                        'creator_id'=>$fence_alarm_data['creator_id']
                );
                Db::table('wx_push_result_log')->save($push_res_data);
            }
        }



        return array('success'=>true,'message'=>'围栏检测完成,结果:'.$is_cross);
    }   
    /**
     * 是否跨越围栏 
     * @param  $shape 围栏类型
     * @param  $fence_info 围栏信息
     * @param  $lnglat 当前位置
     */
    function isCrossFence($shape,$fence_info,$lnglat){
        $is_cross = false;

        if ($shape == 'circle') {
            // 计算基站位置到圆形围栏中心点的距离
            $len = \algorithm\Geometry::distanceBetween2BdPoints($fence_info['center'], $lnglat); // 单位：km
            // 小于圆形围栏半径
            if ($len * 1000 < $fence_info['radius']) {
                $is_cross = true;
            }
        } else if ($shape == 'polygon') {
            // 判断是否在多边形范围内
            $isIn =\algorithm\Geometry::isInPolygon($fence_info, $lnglat);
            if ($isIn) {
                $is_cross = true;
            }				
        }

        return $is_cross;
    }
     /**
     * 百度经纬度解析 
     * @param $lat
     * @param $lng
     */
    function bmap_geocoding($lat,$lng){
        /*
            http://lbsyun.baidu.com/index.php?title=uri/api/web
        */
        $addr = json_decode(file_get_contents("http://api.map.baidu.com/geocoder?location={$lat},{$lng}&coord_type=wgs84&output=json&ak=2Gk7v8Gcb6PkgDZytoqsfdxuTGrQgfgB"), true);

        if($addr['status']=='OK'){
            $address=$addr['result']['formatted_address'];
            return array('success'=>true,'address'=>$address);
        }else{
            return array('success'=>false,'address'=>'');
        }

    }
     /**
     * debug日志 
     */
    function debug_log($filename,$data){
        if(Env::get('APP_DEBUG')){
            $file = runtime_path() . '/log/'.date("Y-m-d", time()) ."/".$filename.".log";
            $folder=dirname($file);
            if (!is_dir($folder)){
                mkdir($folder,0777,true);
            }
            if(is_array($data)){
                $data = json_encode($data);
            }
            file_put_contents($file, '[' . date('Y-m-d H:i:s') . ']' . $data . PHP_EOL,FILE_APPEND);
        }
    }

    /**
     * api日志 
     */
    function api_log($filename,$data){
    
            $file = runtime_path() . '/log/'.date("Ymd", time()) ."/".$filename.".log";
            $folder=dirname($file);
            if (!is_dir($folder)){
                mkdir($folder,0777,true);
            }
            if(is_array($data)){
                $data = json_encode($data);
            }
            file_put_contents($file, '[' . date('Y-m-d H:i:s') . ']' . $data . PHP_EOL,FILE_APPEND);
        
    }
    /**
     　*postt请求
    */
    function curl_http_post($data,$url,$ssl){
        $curl = curl_init();
        curl_setopt($curl,CURLOPT_URL,$url);
        if($ssl){
            curl_setopt($curl,CURLOPT_SSL_VERIFYPEER,0);
            curl_setopt($curl,CURLOPT_SSL_VERIFYHOST,1);
            curl_setopt($curl,CURLOPT_SSLVERSION,3);
        }
        // curl_setopt($curl,CURLOPT_USERAGENT,$_SERVER["HTTP_USER_AGENT"]);
        curl_setopt($curl,CURLOPT_AUTOREFERER,1);
        curl_setopt($curl,CURLOPT_POST,1);
        curl_setopt($curl,CURLOPT_POSTFIELDS,$data);
        curl_setopt($curl,CURLOPT_TIMEOUT,30);
        curl_setopt($curl,CURLOPT_HEADER,0);
        curl_setopt($curl,CURLOPT_RETURNTRANSFER,1);
        $tmpInfo = curl_exec($curl);
        if(curl_errno($curl)){
            dump(curl_error($curl));
            return false;
        }
        curl_close($curl);
        return $tmpInfo;
    }

    /**
     * 短信发送 
     */
    function send_sms_with_config( $mobile, $content, $sms_config, $info ){
		
        $config = array();
        /*url示例
        $url="https://$this->ServerIP:$this->ServerPort/$this->SoftVersion/Accounts/$this->AccountSid/SMS/TemplateSMS?sig=$sig";
        */
        //沙盒环境（应用开发调试）：sandboxapp.cloopen.com
        //生产环境（应用上线使用）：app.cloopen.com
        $serverIP = 'app.cloopen.com';
        
        //sdk版本号
        $softVersion = '2013-12-26';
        //请求端口，一般为8883
        $serverPort='8883';
        //主账号,对应开官网发者主账号下的 ACCOUNT SID
        $accountSid= $sms_config['accountSid'];
        //$accountSid="8aaf0708754a3ef201755a063f070592";
        $config['accountSid']= $accountSid;
        //密钥
        $config['accountToken'] = $sms_config['accountToken'];
        //$config['accountToken'] = "324fb91d40cf472580dc8dc4d9a3e0d5";
        //应用Id，在官网应用列表中点击应用，对应应用详情中的APP ID
        $config['appId'] = $sms_config['appId'];
        //$config['appId'] = "8aaf070875774c6d0175910e26fc0bf9";
        //时间戳
        $batch = date("YmdHis");
        //验证信息编码
        $config['authen'] = base64_encode($accountSid . ":" . $batch);
        //加密REST API验证参数
        $sig = strtoupper(md5($accountSid . $config['accountToken'] . $batch));
        //拼接url
        $config['api_send_url'] = "https://$serverIP:$serverPort/$softVersion/Accounts/$accountSid/SMS/TemplateSMS?sig=".$sig;
        
        $postArr = array (
            'to'=>$mobile,
            'templateId' => $content['tplno'],
            'appId'=>$config['appId'],
            'datas'=>$content['tpldata'],
        );
        $postArr = json_encode($postArr);
        $authen = $config['authen'];
        $header = array("Accept:application/json","Content-Type:application/json;charset=utf-8","Authorization:$authen");
        $url = $config['api_send_url'];
        //$result = $this->curlPost( $url , $postArr, $header);
        //初始化curl
        $ch = curl_init();
        //参数设置  
        $res= curl_setopt ($ch, CURLOPT_URL,$url);  
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, FALSE);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
        curl_setopt ($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $postArr);
        curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch,CURLOPT_HTTPHEADER,$header);
        $result = curl_exec ($ch);
        curl_close($ch);
        
        $result = json_decode($result,true);
        //var_dump($result);
        $data=array(
                    'recipient'=>$mobile,
                    'content'=>$content['info'],
                    // 'type'=>'sms',
                    'created_at'=>time(),
                    'creator_id'=>0,
                    // 'sent_time'=>time(),
        );
        if($result['statusCode']==='000000'){
            $data['sent_result']='发送成功';
            $res = Db::name('sms_send_log')->insert($data);
            return array('success'=>true,'message'=>'发送成功');
        }else{
            $data['sent_result']=json_encode($result,JSON_UNESCAPED_UNICODE);
            $res = Db::name('sms_send_log')->insert($data);
            return array('success'=>false,'errorCode'=>$result['statusCode'],'message'=>$result['statusMsg'],'data'=>$data);
        }
        
    }

     /**
     * 获取短信配置 
     */
    function get_sms_config( $type ){
        $key="wxt_sms_send_config_".$type;
        $sms_cache_config =S($key);
        
        $sms_config=array();
        
        if(!$sms_cache_config){
            $config_id=Db::name('sms_config')->where(array('name'=>$type,'pid'=>'0'))->getField('id');
            if(!$config_id){
                return array('result'=>'fail','message'=>'未配置'.$type);
            }
            $config_list=Db::name('sms_config')->where(array('pid'=>$config_id))->select();
            if(!$config_list){
                return array('result'=>'fail','message'=>'未配置参数'.$type);
            }
            foreach($config_list as $val){
                $sms_config[$val['key']]=$val['value'];
            }

            S($key,json_encode($sms_config),60);
        }else{
            $sms_config=json_decode($sms_cache_config,true);
        }
        
        return $sms_config;
    }
    /**
     * 请求wifi定位信息
     */
    function requestWifiLBS($routeInfo){
        if(Env::get('map.wifiwayz')){
            return requestWifiLBS_wayz($routeInfo);
        }
        if(!$routeInfo){
            return array(
                'success' => false,
                'msg' => '空数据'
            );
        }
        if(!isset( $routeInfo['WifiMacs']) ){
            return array(
                'success' => false,
                'msg' => '无wifi信息'
            );
        }
        api_log('gaode_wifi_lbs',$routeInfo['WifiMacs']);
        $WifiMacs = $routeInfo['WifiMacs'];
        $userKey = '';
        if(!$userKey){
            $userKey = 'f107b0b3a513e6e37c6fb0424bed6633';
        }
        $t1=microtime(true);
        //$url = 'http://api.cellocation.com:81/loc/?wl='  . $WifiMacs .'&output=json&coord=gcj02';
        $url = 'http://apilocate.amap.com/position?accesstype=1&imei=' . $routeInfo['DeviceNumber'] . '&macs=' . $WifiMacs . '&key=' . $userKey;
        $curl = curl_init($url);
        
        curl_setopt($curl, CURLOPT_TIMEOUT, 1);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        $response = curl_exec($curl);
        
        if (curl_errno($curl)) {
            $errmsg = curl_error($curl);
            curl_close($curl);
            return array(
                'success' => false,
                'msg' => $errmsg
            );
        }

        curl_close($curl);
        api_log('gaode_wifi_lbs',$routeInfo['DeviceNumber']."返回结果".$response);
        $response = json_decode($response, true);
        
        if($response['info'] != 'OK' || count($response['result']) < 2 ){
            return array(
                'success' => false,
                'msg' => $response['info']
            );
        }
        
        $result = $response['result'];
        $location = $response['result']['location'];
        $location = explode(',', $location);
        $lngLat = \algorithm\Geometry::convertGcj02ToBd09($location[1],$location[0]);
        $response = array(
            'lon' => $lngLat['lng'],
            'lat' => $lngLat['lat'],
            'address' => $result['desc']
        );
        $t2=microtime(true);
        api_log('gaode_wifi_lbs','t3:---'. ($t2-$t1));
        
        return array(
            'success' => true,
            'data' => $response
        );
        
        
    }

    /**
     * 请求wifi定位信息（WAYZ融合定位）
     */
    function requestWifiLBS_wayz($routeInfo){
        if(!$routeInfo){
            return array(
                'success' => false,
                'msg' => '空数据'
            );
        }
        if(!isset( $routeInfo['WifiMacs']) ){
            return array(
                'success' => false,
                'msg' => '无wifi信息'
            );
        }
        api_log('wayz_wifi_lbs',$routeInfo['WifiMacs']);
        //当前毫秒时间戳
        list($msec, $sec) = explode(' ', microtime());
        $msectime = (float)sprintf('%.0f', (floatval($msec) + floatval($sec)) * 1000);
        //鉴权方式 采用 access_key 的方式，在 URL 的 Query Param 中添加 access_key。
        $access_key = 'VOiuxRnGhlJsAzVzhaU2Hen24zvwuWck';
        //处理WiFi信息
        $WifiMacs = $routeInfo['WifiMacs'];
        $WifiMacs = explode('|',$WifiMacs);
        $wifis = [];
        $countWifi=count($WifiMacs);
        $isFilter=Env::get('map.isfilter',false);
        $filterSignal=Env::get('map.signal',80);//过滤信号强度
        $filterTotal=Env::get('map.mactotal',10);;//wifi个数大于次数才开始过滤
        $total=0;//wifimacs上报 默认按强度排序 可直接计算不过滤前$filterTotal个
        foreach($WifiMacs as $v){
            $total++;
            $w = explode(',',$v);
            $wifi = array(
                "timestamp"=> intval($msectime),
                "signalStrength"=> abs($w[1]),
                "macAddress"=> $w[0],
                //"ssid"=> "AiMap",
                //"frequency"=> 0,
                //"channel"=> 0,
                //"connected"=> true
            );
            //判断是否需要过滤
            if(  $isFilter &&  ($total>$filterTotal) &&  ($wifi['signalStrength'] >  $filterSignal )){
                continue;
            }
            array_push($wifis,$wifi);
        }
        $asset = array(
            "id"=>date('YmdHis'),
            "manufacturer"=>"hzrl",
            "model"=>"校园卡",
            "imeiMd5"=> md5($routeInfo['DeviceNumber']),
            "macAddress"=>$routeInfo['DeviceNumber'],
            "serialNumber"=> "",
            "uniqueId"=> $routeInfo['DeviceNumber'],
            /*"os"=>array(
                "type"=> "",
                "version"=> ""
            )*/
        );
        $url = "https://api.newayz.com/location/hub/v1/track_points?access_key=".$access_key."&field_masks=location.position,location.address,location.place";
        //var_dump($url);
        //发送数据data
        $data = array(
            'timestamp'=>intval($msectime),
            'id'=>date('YmdHis'),
            'asset'=>$asset,
            'location'=>array(
                "wifis"=>$wifis
            )
        );
        $data = json_encode( $data );
        
        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
        curl_setopt($curl, CURLOPT_HEADER, 0);
        curl_setopt($curl, CURLOPT_HTTPHEADER,
                    array(
                        'Content-Type: application/json; charset=utf-8',
                        'Content-Length:' . strlen($data)
                    )
                   );
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        $response = curl_exec($curl);
        $errorno = curl_errno($curl);
        if ($errorno) {
            return array('success' => false, 'data' => $errorno);
        }
        
        api_log('wayz_wifi_lbs',$routeInfo['DeviceNumber']."返回结果".$response);
        curl_close($curl);
        $response = json_decode($response, true);
        if(isset($response['code'])){
            return array(
                'success' => false,
                'msg' => $response['message']
            );
        }
        $location = $response['location'];
        $position = $location['position'];
        $point = $position['point'];

        $lngLat = \algorithm\Geometry::convertGcj02ToBd09($point['latitude'],$point['longitude']);
        $response = array(
            'lon' => $lngLat['lng'],
            'lat' => $lngLat['lat'],
            'address' => $location['address']['name']
        );
        //echo '--------way end---------';
        return array(
            'success' => true,
            'data' => $response
        );
    }    

     /**
     * 请求wifi定位信息（高德批量）
     */
    function requestWifiLBS_gaode_batch($routeArr){
      
        if(!$routeArr){
            return array(
                'success' => false,
                'msg' => '空数据'
            );
        }
        $mh = curl_multi_init(); 
        $curlArray = array();
        $t1=microtime(true);
	     
        foreach($routeArr as $key=>$routeInfo){
            if(!isset( $routeInfo['WifiMacs'])){
                continue;
            }
             

            $WifiMacs = $routeInfo['WifiMacs'];
            $userKey = 'f107b0b3a513e6e37c6fb0424bed6633';

            $ch = curl_init();
            $url = 'http://apilocate.amap.com/position?accesstype=1&imei=' . $routeInfo['DeviceNumber'] . '&macs=' . $WifiMacs . '&key=' . $userKey;
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_TIMEOUT, 3);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_multi_add_handle($mh,  $ch); 
            $curlArray[$key] = $ch;
            
            
        }
        // echo count($curlArray);
        //api_log('gaode_wifi_lbs','count:--'. (count($curlArray)));
        $running = NULL; 
        do { 
           // usleep(10000); 
            curl_multi_exec($mh,$running); 
        } while($running > 0); 
        $response_data=array();
        $success=0;
        $fail=0;
        foreach($curlArray as $key => $info) { 
                // $res[$key] = curl_multi_getcontent($curlArray[$key]); 
                $result = curl_multi_getcontent($curlArray[$key]); 
                $response = json_decode($result,true);
               
                if($response['info'] != 'OK' || count($response['result']) < 2 ){
                    $fail++;
                    $item=array(
                        'success' => false,
                        'msg' => '定位信息解析失败'
                    );
                }else{
                    $success++;
                    $result = $response['result'];
                    $location = $result['location'];
                    $location = explode(',', $location);
                    $lngLat = \algorithm\Geometry::convertGcj02ToBd09($location[1],$location[0]);
                    $address = array(
                        'lon' => $lngLat['lng'],
                        'lat' => $lngLat['lat'],
                        'address' => $result['desc']
                    );
                    $item=array(
                        'success' => true,
                        'data' => $address
                    );
    
                }
                $response_data[$key]=$item;

        } 
        // api_log('gaode_wifi_lbs','result:---success:--'.$success.'--fail:--'. $fail);
        foreach($curlArray as $key => $info){ 
                curl_multi_remove_handle($mh, $curlArray[$key]); 
        } 
        curl_multi_close($mh);  
        //  $t3=microtime(true);
        //  api_log('gaode_wifi_lbs','t3:---'. ($t3-$t1));
        //  echo 't3:---'. ($t3-$t2).PHP_EOL;
        return $response_data;
 
        
    }
    /**
     * 批量请求wifi定位信息（WAYZ融合定位）
     */
    function requestWifiLBS_wayz_batch($routeArr){
        if(!$routeArr){
            return array(
                'success' => false,
                'msg' => '空数据'
            );
        }

        //当前毫秒时间戳
        list($msec, $sec) = explode(' ', microtime());
        $msectime = (float)sprintf('%.0f', (floatval($msec) + floatval($sec)) * 1000);
        //鉴权方式 采用 access_key 的方式，在 URL 的 Query Param 中添加 access_key。
        $access_key = 'VOiuxRnGhlJsAzVzhaU2Hen24zvwuWck';


       
        $post_data=array();
        $batch_keys=array();
        $isFilter=Env::get('map.isfilter',true);//
        $filterSignal=Env::get('map.signal',85);//过滤信号强度
        $filterTotal=Env::get('map.mactotal',5);;//wifi个数大于次数才开始过滤

        foreach($routeArr as $k=>$routeInfo){
            $asset = array(
                "id"=>date('YmdHis'),
                "manufacturer"=>"hzrl",
                "model"=>"校园卡",
                "imeiMd5"=> md5($routeInfo['DeviceNumber']),
                "macAddress"=>$routeInfo['DeviceNumber'],
                "serialNumber"=> "",
                "uniqueId"=> $routeInfo['DeviceNumber'],
                /*"os"=>array(
                    "type"=> "",
                    "version"=> ""
                )*/
            );
            //处理WiFi信息
            $WifiMacs = $routeInfo['WifiMacs'];
            $WifiMacs = explode('|',$WifiMacs);
            $wifis = [];
            $countWifi=count($WifiMacs);

            
            $total=0;//wifimacs上报 默认按强度排序 可直接计算不过滤前$filterTotal个
            foreach($WifiMacs as $v){
                $total++;
                $w = explode(',',$v);
                $wifi = array(
                    "timestamp"=> intval($msectime),
                    "signalStrength"=> abs($w[1]),
                    "macAddress"=> $w[0],
                );
                //判断是否需要过滤
                if( $isFilter &&  ($total>$filterTotal) &&  ($wifi['signalStrength'] >  $filterSignal )){
                    continue;
                }
                array_push($wifis,$wifi);
            }
            if(count($wifis)<=3){
                continue;
            }
            $data = array(
                'timestamp'=>intval($msectime),
                'id'=>date('YmdHis'),
                'asset'=>$asset,
                'location'=>array(
                    "wifis"=>$wifis
                )
            );
            //$post_data[$k]=$data;
            array_push($post_data,$data);
            array_push($batch_keys,$k);
        }
        // var_dump($batch_keys);
        $url = "https://api.newayz.com/location/hub/v1/track_points?batch=true&access_key=".$access_key."&field_masks=location.position,location.address,location.place";
        //var_dump($url);
        //发送数据data
        
        $post_data = json_encode( $post_data );

        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl, CURLOPT_POSTFIELDS, $post_data);
        curl_setopt($curl, CURLOPT_HEADER, 0);
        curl_setopt($curl, CURLOPT_HTTPHEADER,
                    array(
                        'Content-Type: application/json; charset=utf-8',
                        'Content-Length:' . strlen($post_data)
                    )
                   );
        // curl_setopt($curl, CURLOPT_HTTPHEADER, array('Accept-Encoding: gzip, deflate'));
        // curl_setopt($curl, CURLOPT_ENCODING, "gzip");
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        $response = curl_exec($curl);
        $errorno = curl_errno($curl);
        if ($errorno) {
            return array('success' => false, 'data' => $errorno);
        }

        // api_log('wayz_wifi_lbs',$routeInfo['DeviceNumber']."返回结果".$response);
        curl_close($curl);
        if(count($batch_keys)==1){
            $response = [json_decode($response, true)];
        }else{
            $response = json_decode($response, true);
        }
        
        $response_data=array();
        foreach($response as $key=>$value){
            // api_log('wayz_wifi_lbs',"返回结果".$key);
            $response_key=$batch_keys[$key];
            // api_log('wayz_wifi_lbs_batch',$routeArr[$response_key]['DeviceNumber'].' macs: '.$routeArr[$response_key]['WifiMacs']);
            // api_log('wayz_wifi_lbs_batch',$routeArr[$response_key]['DeviceNumber']." 返回结果: ".json_encode($value,JSON_UNESCAPED_UNICODE));
            $item=[];
            // var_dump($value);
            if(!isset($value['location'])  ){
                $item=array(
                    'success' => false,
                    'msg' => '定位信息解析失败'
                );
            }else{
                $location = $value['location'];
                if(!isset($location['position']) ){
                    $item=array(
                        'success' => false,
                        'msg' =>'定位信息解析失败'
                    );
                }else{
                    $position = $location['position'];
                    $point = $position['point'];
    
                    $lngLat = \algorithm\Geometry::convertGcj02ToBd09($point['latitude'],$point['longitude']);
                    $address = array(
                        'lon' => $lngLat['lng'],
                        'lat' => $lngLat['lat'],
                        // 'address' => $location['address']['name']
                    );
                    if(isset($location['address']['name'])){
                        $address['address']=$location['address']['name'];
                    }
                    
                    $item=array(
                        'success' => true,
                        'data' => $address
                    );
                }

            }
            
            $response_data[$response_key]=$item;
        }
        
        return $response_data;
    }    
    /**
     * 百度地图过滤异常点
     * @time 2021年05月18日 10:15
     * @param Request $request 
     */
    function bmapFilterOutliers($rows){
        if(!$rows){
            return array();
        }
        $response_data=[];
        $point1=[];
        $speedArr=[];
        $timestamp=0;
        for($i=0;$i<count($rows);$i++){
            if($i==0){
                //当前最新的一个定位保留
                $timestamp=$rows[$i]['Timestamp'];
                array_push($response_data, $rows[$i]);
                $point1=['lng'=>$rows[$i]['Longitude'],'lat'=>$rows[$i]['Latitude']];
                continue;
            }
            $point2=['lng'=>$rows[$i]['Longitude'],'lat'=>$rows[$i]['Latitude']];
            $distance=\algorithm\Geometry::distanceBetween2BdPoints($point1,$point2);//单位km
            $time=($timestamp-$rows[$i]['Timestamp'])/3600;//单位h
            $speed=$distance/$time;//计算速度  超过130的默认为异常点
            $speedArr[]=$speed;
            if($rows[$i]['SignalType']=='WiFi'){
                $length=count($speedArr);
                if($length>=5){
                    $filterSpeed=($speedArr[$length-1]+$speedArr[$length-2]+$speedArr[$length-3]+$speedArr[$length-4]+$speedArr[$length-5])/5*1.5;//超过平均速度 1.5倍
                }else{
                    $filterSpeed=50;
                }
                if($filterSpeed<10){
                    $filterSpeed=10;
                }
                if($speed<$filterSpeed){
                    $timestamp=$rows[$i]['Timestamp'];
                    $point1=$point2;
                    array_push($response_data, $rows[$i]);
                }else{
                    array_pop($speedArr);//去除无效速度
                }
            }else{
                $timestamp=$rows[$i]['Timestamp'];
                $point1=$point2;
                //只有wifi点需要过滤 gps点不过滤
                array_push($response_data, $rows[$i]);
            }
            
        }
        return bmapReverseFilterOutliers($response_data);
    }
    /**
     * 百度地图过滤异常点
     * @time 2021年05月18日 10:15
     * @param Request $request 
     */
    function bmapReverseFilterOutliers($rows){
        if(!$rows){
            return array();
        }
        $response_data=[];
        $point1=[];
        $speedArr=[];
        $timestamp=0;
        for($i=count($rows)-1;$i>=0;$i--){
            if($i==count($rows)-1){
                //当前最新的一个定位保留
                array_push($response_data, $rows[$i]);
                $timestamp=$rows[$i]['Timestamp'];
                $point1=['lng'=>$rows[$i]['Longitude'],'lat'=>$rows[$i]['Latitude']];
                continue;
            }
            $point2=['lng'=>$rows[$i]['Longitude'],'lat'=>$rows[$i]['Latitude']];
            $distance=\algorithm\Geometry::distanceBetween2BdPoints($point1,$point2);//单位km
            $time=($rows[$i]['Timestamp']-$timestamp)/3600;//单位h
            $speed=$distance/$time;//计算速度  超过130的默认为异常点
            $speedArr[]=$speed;
            if($rows[$i]['SignalType']=='WiFi'){
                $length=count($speedArr);
                if($length>=5){
                    $filterSpeed=($speedArr[$length-1]+$speedArr[$length-2]+$speedArr[$length-3]+$speedArr[$length-4]+$speedArr[$length-5])/5*1.5;//超过平均速度 1.5倍
                }else{
                    $filterSpeed=50;
                }
                // test_log('filt_map','['.$rows[$i]['PassTime'].'] filterSpeed: '. $filterSpeed.'speed: '. $speed.' time: '. $time);
                // if($filterSpeed>50){
                //     $filterSpeed=50;
                // }
                if($filterSpeed<10){
                    $filterSpeed=10;
                }
                if($speed<$filterSpeed){
                    $point1=$point2;
                     $timestamp=$rows[$i]['Timestamp'];
                    array_push($response_data, $rows[$i]);
                }else{
                    array_pop($speedArr);//去除无效速度
                }
            }else{
                $point1=$point2;
                 $timestamp=$rows[$i]['Timestamp'];
                //只有wifi点需要过滤 gps点不过滤
                array_push($response_data, $rows[$i]);
            }
            
        }
        $res_data=[];
        for($i=count($response_data)-1;$i>=0;$i--){
            array_push($res_data, $response_data[$i]);
        }
        return $res_data;

    }


    /**
     * 查询oracle数据库 select
     * @time 2021年05月18日 10:15
     * @param Request $request 
     */
    function queryOracleSelect($tableName,$cond,$field='*'){
        $conn = null;
        if(!isset($cond['page'])){
            $cond['page']=1;
        }
        if(!isset($cond['limit'])){
            $cond['limit']=10;
        }
        $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 (SELECT * FROM '.$tableName.' '.$whereStr.' ORDER BY ID DESC) a WHERE ROWNUM <='.$end.' ) WHERE RN >='. $start;
        $sql='SELECT * FROM ( SELECT '.$field.',ROWNUM RN FROM '.$tableName.' WHERE ROWNUM <='.$end.' '.$whereStr.'  ) WHERE RN >'. $start;
        // var_dump($sql);
        // return [];
        // 查询并渲染
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        $rows=[];
        while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {

            $rows[]=$row;
        }
        oci_free_statement($stid);
        oci_close($conn);
        return $rows;
        // $conn = new PDO("oci:dbname=//".$host.":".$port."/".$instance_name,$username,$password);// PDO方式
        // $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       
        // $start=($cond['page']-1)*$cond['limit'];
        // $end=$cond['page']*$cond['limit'];

        // $whereStr=buildQueryCond($cond);
        // // SELECT * FROM ( SELECT a.*,to_char(a.UPDATE_DATE,'yyyy-mm-dd hh24:mi:ss') UPDATE_TIME,ROWNUM RN FROM (SELECT * FROM DSSC2.ADM_DEV  ORDER BY ID DESC) a  WHERE ROWNUM <=10 ) WHERE RN >=0

        // $sql = 'SELECT * FROM ( SELECT '.$field.',ROWNUM RN FROM (SELECT * FROM '.$tableName.' '.$whereStr.' ORDER BY ID DESC) a WHERE ROWNUM <='.$end.' ) WHERE RN >='. $start;
        // // var_dump($sql); 
        // $res = $conn -> query($sql);
        // $rows = $res -> fetchAll(PDO::FETCH_ASSOC);
        // if ($conn){
        //     $conn = null;
        // }
        // $rows=[];
        
    }
    /**
     * 查询oracle数据库 count
     * @time 2021年05月18日 10:15
     * @param Request $request 
     */
    function queryOracleFind($tableName,$cond){
        $conn = null;
        if(!$cond){
            return [];
        }
        $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 = new PDO("oci:dbname=//".$host.":".$port."/".$instance_name,$username,$password);// PDO方式
        // $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $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);
        }
        $sql='SELECT *  FROM  '.$tableName.' WHERE ';
        foreach($cond as $key=>$val){
            $sql .=$key.' '.$val[0].' '.$val[1];
        }
        // var_dump($sql);
        // 查询并渲染
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        $rows=[];
        while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
            $rows[]=$row;
        }
        
        oci_free_statement($stid);
        oci_close($conn);
        return $rows[0];
        
    }
    /**
     * 查询oracle数据库 count
     * @time 2021年05月18日 10:15
     * @param Request $request 
     */
    function queryOracleCount($tableName,$cond){
        $conn = null;
        $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 = new PDO("oci:dbname=//".$host.":".$port."/".$instance_name,$username,$password);// PDO方式
        // $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // var_dump($cond);
       
        // $whereStr=buildQueryCond($cond);
        // $countQuery='SELECT count(*) as count FROM  '.$tableName.' '.$whereStr;
        // // var_dump($countQuery);
        // // return 1111;
        // $countRes= $conn -> query($countQuery)->fetch(PDO::FETCH_ASSOC);

        $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);
        }
        $whereStr=buildQueryCond($cond);
        $countQuery='SELECT count(*) as count FROM  '.$tableName.' '.$whereStr;
        // var_dump($countQuery);
        // return 0;
        // 查询并渲染
        $stid = oci_parse($conn, $countQuery);
        $r = oci_execute($stid);
        $rows=[];
        while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {

            $rows[]=$row;
        }
        // var_dump($rows);
        oci_free_statement($stid);
        oci_close($conn);
        return (int)$rows[0]['COUNT'];
    }
    
    function buildQueryCond($cond){
        $whereStr='';
        foreach($cond as $key=>$val){
            
            if( $key!='page' && $key!='limit' && $val[1]!=''){
                if($key=='_string'){
                    if($whereStr==''){
                        $whereStr .='WHERE '.$val;
                    }else{
                        $whereStr .=' AND '.$val;
                    }
                   
                    continue;
                }
                if($val[0]=='like'){
                    if($whereStr==''){
                        $whereStr .='WHERE '.$key.' '.$val[0].' \'%'.$val[1].'%\'';
                    }else{
                        $whereStr .=' AND '.$key.' '.$val[0].' \'%'.$val[1].'%\'';
                    }
                }else if($val[0]=='timeRange'){
                    if($whereStr==''){
                        $whereStr .='WHERE '.$key.' >= to_date(\''.$val[1].'\', \'yyyy-mm-dd hh24:mi:ss\') AND '.$key.' <= to_date(\''.$val[2].'\', \'yyyy-mm-dd hh24:mi:ss\')';
                    }else{
                        $whereStr .=' AND '.$key.' >= to_date(\''.$val[1].'\', \'yyyy-mm-dd hh24:mi:ss\') AND '.$key.' <= to_date(\''.$val[2].'\', \'yyyy-mm-dd hh24:mi:ss\')';
                    }
                }else{
                    if($whereStr==''){
                        $whereStr .='WHERE '.$key.' '.$val[0].' \''.$val[1].'\'';
                    }else{
                        $whereStr .=' AND '.$key.' '.$val[0].' \''.$val[1].'\'';
                    }
                }
            }
        }
        return $whereStr;
    }

    function buildQueryCond2($cond){
        $whereStr='';
        foreach($cond as $key=>$val){
            
            if( $key!='page' && $key!='limit' && $val[1]!=''){
                if($key=='_string'){
                    $whereStr .=' AND '.$val;
                    continue;
                }
                if($val[0]=='like'){
                    $whereStr .=' AND '.$key.' '.$val[0].' \'%'.$val[1].'%\'';
                }else if($val[0]=='timeRange'){
                    $whereStr .=' AND '.$key.' >= to_date(\''.$val[1].'\', \'yyyy-mm-dd hh24:mi:ss\') AND '.$key.' <= to_date(\''.$val[2].'\', \'yyyy-mm-dd hh24:mi:ss\')';
                }else{
                    $whereStr .=' AND '.$key.' '.$val[0].' \''.$val[1].'\'';
   
                }
            }
        }
        return $whereStr;
    }

    function getOracleConnect(){
        $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($username, $password, $host.':'.$port.'/'. $instance_name,'AL32UTF8');
        if (!$conn) {
            $e = oci_error();
            trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
        }
        return $conn;
    }