<?php

namespace app\api\controller;

use app\common\helper\HttpHelper;
use Qiniu\Http\Request;
use qx\Qx;
use app\common\controller\Api;
use app\common\model\User;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;
use function GuzzleHttp\Psr7\copy_to_string;

/**
 * 验证接口
 */
class Test extends Api
{
    protected $noNeedLogin = '*';
    protected $layout = '';
    protected $error = null;
    protected $url = "https://api.njgn.com/api/";

    public function _initialize()
    {
        parent::_initialize();
    }

    public function index()
    {
        $qx = new Qx();
        // $info = $qx->geturl("http://openapi.qxwz.com/rest/linkx.devices.list/ack/U48c2jnr9901?pageNo=1&pageSize=2000");
        $info = $qx->geturl("http://openapi.qxwz.com/rest/linkx.devices.data/ack/U48c2jnr9901?deviceId=1000000792&startTime=1642651366000&endTime=1644379366000");
        var_dump(json_decode($info, true));
        // exit;
        $this->success('', json_decode($info, true));
        // $api = new Api();
        // var_dump($api->getPreviewUrl('1ef84de21c0e4aa19cde1c17b38bd065'));
    }

    /**
     * 导出
     */
    public function export()
    {
        //获取数据
        $list = Db::name("user")->field("id,username,mobile,createtime")->select();
        //vendor('PHPExcel.PHPExcel');
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        //实例化
        $objExcel = new \PHPExcel();
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        //设置内容
        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter = explode(',', "A,B,C,D");
        $arrHeader = array('ID', '姓名', '手机号', "创建时间");
        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };
        //填充表格信息
        foreach ($list as $k => $v) {
            $k += 2;
            //表格内容
            $objActSheet->setCellValue('A' . $k, $v['id']);
            $objActSheet->setCellValue('B' . $k, $v['name']);
            $objActSheet->setCellValue('C' . $k, $v['mobile']);
            $objActSheet->setCellValue('D' . $k, date('Y-m-d H:i:s', $v['createtime']));
            //$objActSheet->setCellValue('E' . $k, $v['goods_images']);
            // 图片生成
            //$objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
            //$objDrawing[$k]->setPath(ROOT_PATH."public/static/image/playbtn.png");
            // 设置宽度高度
            //$objDrawing[$k]->setHeight(40);//照片高度
            //$objDrawing[$k]->setWidth(40); //照片宽度
            // 设置图片要插入的单元格
            //$objDrawing[$k]->setCoordinates('C' . $k);
            // 图片偏移距离
            //$objDrawing[$k]->setOffsetX(30);
            //$objDrawing[$k]->setOffsetY(12);
            //$objDrawing[$k]->setWorksheet($objExcel->getActiveSheet());

            // 表格高度
            //$objActSheet->getRowDimension($k)->setRowHeight(20);
        }
        /*
        $width = array(20, 20, 15, 10, 10, 30, 10, 15);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[5]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[0]);
        $objActSheet->getColumnDimension('D')->setWidth($width[5]);
        $objActSheet->getColumnDimension('E')->setWidth($width[5]);
         */
        $outfile = md5("扫描文本" . time()) . ".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

    /**
     * 批量导入地区
     */
    public function import_village()
    {
        $param = $this->request->param();
        if (!empty($param['code'])) {
            $data = Db::name("area_code")->where("pcode", $param['code'])->where("code", "gt", 532503005000)->select();
            if (!empty($data)) {
//                $this->success("", $data);
                foreach ($data as $k => $v) {
                    $w1 = [];
                    $w1 = [
                        "name" => $v['name'],
                        "province_id" => 1,//云南省
                        "state_id" => 1,//红河州
                        "county_id" => 1,//蒙自市
                    ];
                    $id = Db::name("reservoir_street")->where($w1)->value("id");
                    if (!empty($id)) {
                        $createtime =time();

                        $village = Db::name("area_code")->where(["pcode"=>$v['code']])->select();
                        if(!empty($village)){
                            foreach ($village as $vk=>$vv){
                                $insert_data = [];
                                $insert_data = [
                                    "name"=>$vv['name'],
                                    "province_id" => 1,//云南省
                                    "state_id" => 1,//红河州
                                    "county_id" => 1,//蒙自市
                                    "street_id"=>$id,
                                ];
                                $count = Db::name("reservoir_village")->where($insert_data)->count();
                                if($count==0){
                                    $insert_data["createtime"] = $createtime;
                                    Db::name("reservoir_village")->insertGetId($insert_data);
                                }
                            }
                        }

                    }
                }
            }
        }
        $this->error("无数据");
    }

    public function testallqwe111(){
        $postdata=[
         "mode"=>"db",
            "j_username"=>"lxslj",
            "j_password"=>"LxslJ@2023",
            "validateCode"=>"nsyc"
        ];

        $postres=$this->send_get("http://60.161.5.61:8087/web/login.do?mode=db&j_username=lxslj&j_password=LxslJ@2023&validateCode=nsyc");
        print_r($postres);return;
    }

    public function testallqwe(){

        $postres=$this->send_get("http://60.161.5.61:8087/web/realdata/realdataquerydata.do?stcdlist='80235810','80235820'&type=0");
        $postres=json_decode($postres,true);
        $res="{&quot;base_imei&quot;:&quot;FH8100330391&quot;,&quot;vx&quot;:-0.0000010,&quot;vy&quot;:0.0000010,&quot;vz&quot;:0.0,&quot;isOriginEnu&quot;:1,&quot;datum_n&quot;:219.6912,&quot;rover_imei&quot;:&quot;FH8100330463&quot;,&quot;bx&quot;:-1286959.901,&quot;by&quot;:5721394.5157,&quot;bz&quot;:2501503.0149,&quot;datum_e&quot;:2.713,&quot;datum_z&quot;:2501692.98,&quot;e&quot;:2.7105,&quot;datum_x&quot;:-1286937.4426,&quot;datum_y&quot;:5721282.3106,&quot;datum_u&quot;:-30.1577,&quot;calc_type&quot;:1,&quot;n&quot;:219.6843,&quot;sat_num&quot;:23,&quot;accz&quot;:0.0,&quot;accy&quot;:0.0,&quot;u&quot;:-30.1636,&quot;x&quot;:-1286937.4396,&quot;y&quot;:5721282.3084,&quot;accx&quot;:0.0,&quot;z&quot;:2501692.9711,&quot;calc_time&quot;:1697536212000}";
        print_r($res);return;
        foreach ($postres['rows'] as $k=>$v){
           print_r( $postres['rows'][$k]['39']);
        }
    }
    function test123(){
        // 创建一个cURL资源
        $curl = curl_init();
// 设置请求的URL
        $url = "http://60.161.5.61:8087/web/login.do?mode=db&j_username=lxslj&j_password=LxslJ@2023&validateCode=nsyc";
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL,$url);
        curl_setopt($ch, CURLOPT_HEADER, true);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $result = curl_exec($ch);
        $header_size = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
        $header = substr($result,0,$header_size);
        $body = substr($result,$header_size);
        curl_close($ch);
// 获取响应标头中的cookie内容
        preg_match_all('/^Set-Cookie:\s*([^;]*)/mi', $header, $matches);
        $cookies = array();
        foreach ($matches[1] as $cookie) {
            parse_str($cookie, $cookieArr);
            $cookies = array_merge($cookies, $cookieArr);
        }
// 打印cookie内容
       return $cookies['JSESSIONID'];
    }

    public function send_get111($url){
        $token=$this->test123();
        date_default_timezone_set('PRC');
            $headerArray =array("Content-Type: application/json","Cookie:JSESSIONID=:$token");
        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $this->url.$url);
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl,CURLOPT_HTTPHEADER,$headerArray);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST,FALSE);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER,true);
        curl_setopt($curl, CURLINFO_HEADER_OUT, true);
        $output = curl_exec($curl);
        curl_close($curl);
        return $output;
    }

    function send_get($url)
    {
        $res=$this->test123();
        $cookie="Cookie:JSESSIONID=$res";
        $cookies['JSESSIONID']=$res;
        $options = array(
            'http' => array(
                'method' => "GET", //or GET
                'header' => $cookie,
                'timeout' => 15 * 60 // 超时时间（单位:s）
            )
        );
        $context = stream_context_create($options);
        $result = file_get_contents($url, false, $context);
        return $result;
    }

    public function test12123(){
        $db2 = Db::connect(config('database.db2'));
        $result=$db2->name('pptn_r')->select();
        print_r($result);
    }

    public function teser(){
        $res='{"data":"{&quot;base_imei&quot;:&quot;FH8100330391&quot;,&quot;vx&quot;:0.0,&quot;vy&quot;:-0.0000010,&quot;vz&quot;:0.0,&quot;isOriginEnu&quot;:1,&quot;datum_n&quot;:199.2261,&quot;rover_imei&quot;:&quot;FH8100330457&quot;,&quot;bx&quot;:-1286959.96,&quot;by&quot;:5721394.2866,&quot;bz&quot;:2501512.7974,&quot;datum_e&quot;:-44.9157,&quot;datum_z&quot;:2501695.025,&quot;e&quot;:-44.9157,&quot;datum_x&quot;:-1286898.4617,&quot;datum_y&quot;:5721325.5553,&quot;datum_u&quot;:-2.1102,&quot;calc_type&quot;:1,&quot;n&quot;:199.2304,&quot;sat_num&quot;:24,&quot;accz&quot;:0.0,&quot;accy&quot;:0.0,&quot;u&quot;:-2.0777,&quot;x&quot;:-1286898.4679,&quot;y&quot;:5721325.5829,&quot;accx&quot;:0.0,&quot;z&quot;:2501695.0418,&quot;calc_time&quot;:1697073531000}"}';
        $rer=json_decode($res,true);
        $resdata=$rer['data'];
        $resjsondata=json_decode($resdata,true);
        print_r($resdata);
    }


    public function select_sensor_point_by_project(){
        $url="sensor_point/select_sensor_point_by_project?projectId=1254";
        $res=$this->posturl($url);
        $res=json_decode($res,true);

        foreach ($res['result'] as $k=>$v) {
            if($res['result'][$k]['sensorId']!='274165'){
                $data=[
                    'equipment_id'=>$res['result'][$k]['sensorId'],
                    'dx_value'=>$res['result'][$k]['temporary'],
                    'reporttime'=>time(),
                    'createtime'=>time()
                ];
                print_r($res);return;
                Db::name('reservoir_dam_isotonic')->insert($data);
            }
        }
        print_r("请求成功");
    }


    public function select_sensors_data_by_time(){
        $url="sensor_point/select_sensors_data_by_time?projectId=1254";
        $res=$this->posturl($url);
        $res=json_decode($res,true);

        foreach ($res['result'] as $k=>$v) {
            if($res['result'][$k]['sensorId']!='274165'){
                $data=[
                    'equipment_id'=>$res['result'][$k]['sensorId'],
                    'dx_value'=>$res['result'][$k]['temporary'],
                    'reporttime'=>time(),
                    'createtime'=>time()
                ];
                print_r($res);return;
                Db::name('reservoir_dam_isotonic')->insert($data);
            }
        }
        print_r("请求成功");
    }



    /**
     * 获取token
     * @return mixed
     */
    public function getToken(){
        $appId = '896126276027285504';
        $appSecret = 'e2eff0c7c0674daad4dde6bdab44be18';
        $data=["appId"=>$appId,"appSecret"=>$appSecret];
        $res=send_post("https://api.njgn.com/common_api/api_access_token/get_access_token",$data);
        $res=json_decode($res,true);
        return $res['result'];
    }


    /**
     * 数据请求接口
     * @return void
     */
    public function posturl($url){
        $token = $this->getToken();
        $headerArray =array("Content-Type: application/json","cookie:generator=$token");
        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $this->url.$url);
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl,CURLOPT_HTTPHEADER,$headerArray);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST,FALSE);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER,true);
        curl_setopt($curl, CURLINFO_HEADER_OUT, true);
        $output = curl_exec($curl);
        curl_close($curl);
        return $output;
    }

    public function testss(){
        $res="{&quot;base_imei&quot;:&quot;FH8100330391&quot;,&quot;vx&quot;:0.0,&quot;vy&quot;:0.0,&quot;vz&quot;:0.0,&quot;isOriginEnu&quot;:1,&quot;datum_n&quot;:88.585,&quot;rover_imei&quot;:&quot;FH8100330393&quot;,&quot;bx&quot;:-1286960.08,&quot;by&quot;:5721394.8189,&quot;bz&quot;:2501503.014,&quot;datum_e&quot;:1.2944,&quot;datum_z&quot;:2501583.5621,&quot;e&quot;:1.2902,&quot;datum_x&quot;:-1286953.238,&quot;datum_y&quot;:5721358.5038,&quot;datum_u&quot;:-2.1511,&quot;calc_type&quot;:1,&quot;n&quot;:88.5848,&quot;sat_num&quot;:24,&quot;accz&quot;:0.0,&quot;accy&quot;:0.0,&quot;u&quot;:-2.1374,&quot;x&quot;:-1286953.2368,&quot;y&quot;:5721358.5171,&quot;accx&quot;:0.0,&quot;z&quot;:2501583.5674,&quot;calc_time&quot;:1697542827000}";
        $new_res=str_replace("&quot;",'"',$res);
        $new_res=json_decode($new_res,true);
        var_dump($new_res);
    }


    public function getgnss(){
        $seeding_time= $this->request->param("data");
        $new_res=str_replace("&quot;",'"',$seeding_time);
        $new_res=json_decode($new_res,true);
        $insertdata=[
            'base_imei'=>$new_res['base_imei'],
            'rover_imei'=>$new_res['rover_imei'],
            'calc_time'=>$new_res['calc_time'],
            'calc_type'=>$new_res['calc_type'],
            'sat_num'=>$new_res['sat_num'],
            'e'=>$new_res['e'],
            'n'=>$new_res['n'],
            'u'=>$new_res['u'],
            'x'=>$new_res['x'],
            'y'=>$new_res['y'],
            'z'=>$new_res['z'],
            'vx'=>$new_res['vx'],
            'vy'=>$new_res['vy'],
            'vz'=>$new_res['vz'],
            'accx'=>$new_res['accx'],
            'accy'=>$new_res['accy'],
            'accz'=>$new_res['accz'],
            'isOriginEnu'=>$new_res['isOriginEnu'],
            'datum_e'=>$new_res['datum_e'],
            'datum_n'=>$new_res['datum_n'],
            'datum_u'=>$new_res['datum_u'],
            'datum_x'=>$new_res['datum_x'],
            'datum_y'=>$new_res['datum_y'],
            'datum_z'=>$new_res['datum_z'],
            'fft'=>$new_res['fft'],
            'sat_info'=>$new_res['sat_info']
        ];
       Db::name('reservoir_displacement')->insert($insertdata);
        //wrp_reservoir_dam_displacement
        $damdata=[
            'number'=>$new_res['rover_imei'],
            'horizontal'=>bcsub(bcmul($new_res['datum_e'],1000,2),bcmul($new_res['e'],1000,2),2),
            'horizontalY'=>bcsub(bcmul($new_res['datum_n'],1000,2),bcmul($new_res['n'],1000,2),2),
            'vertical'=>bcsub(bcmul($new_res['datum_u'],1000,2),bcmul($new_res['u'],1000,2),2),
            'reservoir_id'=>40,
            'status'=>0,
            'warning'=>0,
            'actual'=>0,
            'predictive'=>0,
            'risk'=>'',
            'createtime'=>time(),
            'reporttime'=>$new_res['calc_time'],
            'issend'=>0,
            'updatetime'=>time(),
        ];
        Db::name("reservoir_dam_displacement")->insert($damdata);
//        file_put_contents("2222222222.txt",date("Y-m-d H:i:s").":".($seeding_time)."\r\n",FILE_APPEND);
        return "ok";
    }
    public function testtt(){
        $jin_time = Db::connect("database.db2")->name("river_r")->limit(10)->select();
        print_r($jin_time);
    }
    /**
     * 2、（四信物联）第一步：把以前水位数据拉一下
     */
    public function step01_water_level()
    {
        $w = [];
        $w["type"] = ["=", "1"];//硬件类型 1=水位计
        $w["company_type"] = ["=", "7"];//设备商 1=千寻，2=匠星
        $field = "deviceId,apiKey,reservoir_id,type,company_type,servicetime";
        $list = Db::name('reservoir_equipment')->where($w)->field($field)->order("reservoir_id asc")->select();
        $data = [];

        foreach ($list as $k => $v) {
            $startTime = "";//开始时间
            $endTime = "";//结束时间
            $servicetime = "";//设备接口有数据时间（每个设备固定值）
            $servicetime = strtotime($v['servicetime']);//某日凌晨时间戳  （$v['servicetime']具体某日）

            //匠星有效查询一天内（开始日期和结束日期要是同一天，而不是24小时内）

            //查最新一次设备更新数据时间
            $ww = [];
            $ww["number"] = ["=", $v['deviceId']];
            $ww["reservoir_id"] = ["=", $v['reservoir_id']];
            $last_createtime = Db::name("reservoir_rain_water_level")->where($ww)->order("createtime desc")->value("createtime");

            if ($last_createtime < $servicetime) {
                //数据中时间比接口开始时间都往前，就只能从接口开始有数据时间去采数据；
                $startTime = $servicetime; //接口开始有数据时间（某日凌晨时间戳）
                $start_time=date("Y-m-d H:i:s", $startTime);
                $endTime=$startTime+ 24 * 3600 - 1;
                $end_time=date("Y-m-d H:i:s", $endTime);
            } else {
                $startTime = $last_createtime + 1;
                $start_time=date("Y-m-d H:i:s", $startTime);
                $endTime=$startTime+ 24 * 3600 - 1;
                $end_time=date("Y-m-d H:i:s", $endTime);
            }

            $jin_time = Db::connect("database.db2")
                ->name("river_r")
                ->where('TM', '>', $start_time)
                ->where('STCD',  $v['deviceId'])
                ->where('TM', '<', $end_time)
                ->select();

            if ($jin_time) {
                //增加预警记录
                //增加预警记录 @pcl：2023-08-23修改
                //获得水库设置的预警值
                $waterWaing = Db::name('reservoir_threshold')
                    ->where(['reservoir_id' => $v['reservoir_id'], 'deviceId' => $v['deviceId']])
                    ->find();
                //$this->warning($v['deviceId'], $info);
                foreach ($jin_time as $k2 => $v2) {
                    $data[] = [
                        'reservoir_id' => $v['reservoir_id'],
                        'water_level' => $v2['RZ'],
                        'createtime' => strtotime($v2['TM']),
                        'reporttime' => strtotime($v2['TM']),
                        'number' => $v['deviceId']
                    ];
                    //增加预警记录 @pcl：2023-08-23修改
                    if (!empty($waterWaing)) {
                        $config = json_decode($waterWaing['config'], true);
                        $warr = explode(',', $config['value']);
                        if (!empty($warr[1]) && $v2['RZ'] > $warr[1]) {
                            $wninsert = [
                                'value' => $v2['RZ'],
                                'warning' => $warr[0] . ',' . $warr[1],
                                'equipment_id' => $v['deviceId'],
                                'reservoir_id' => $v['reservoir_id'],
                                'createtime' => strtotime($v2['TM']) / 1000,
                                'reporttime' => strtotime($v2['TM'])
                            ];
                            //判断更新时间是否重复
                            $rescks = Db::name('reservoir_warning_waterlevel')
                                ->where(['equipment_id' => $v['deviceId'], 'reservoir_id' => $v['reservoir_id'], 'reportTime' => $v2['reportTime']])
                                ->find();
                            if (empty($rescks)) {
                                Db::name('reservoir_warning_waterlevel')->insert($wninsert);
                            }
                        }
                    }//if
                }//foreach
            }
        }
        $succ_num = Db::name('reservoir_rain_water_level')->insertAll($data);
        echo $succ_num . "/" . count($data);
    }
}