catch_nbfd_admin/catch/yunying/controller/Vehicle.php

<?php

namespace catchAdmin\yunying\controller;

use catcher\base\CatchRequest as Request;
use catcher\CatchResponse;
use catcher\base\CatchController;
// use catchAdmin\yunying\model\Vehicle as vehicleModel;


use catchAdmin\yunying\excel\VehiclesExport;
use catchAdmin\system\model\SysDictData;
use catcher\Utils;
use catcher\library\excel\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Env;
use think\facade\Db;
class Vehicle extends CatchController
{
    // protected $vehicleModel;
    
    // public function __construct(VehicleModel $vehicleModel)
    // {
    //     $this->vehicleModel = $vehicleModel;
    // }
    
    /**
     * 列表
     * @time 2022年01月20日 10:42
     * @param Request $request 
     */
    public function index(Request $request)
    {
        $param=$request->param();
        //联表条件o.rfid_id = s.id and o.owner_id = r.id 
        $cond=[
            '_string'=>'o.rfid_id = s.id',
        ];
        if($param['PLATE_NO']){
            $cond['o.PLATE_NO']=['like',$param['PLATE_NO']];
        }
        if($param['ID_CARD_NUMBER']){
            $cond['r.ID_CARD_NUMBER']=['like',$param['ID_CARD_NUMBER']];
        }
        if($param['MOBILE_NUMBER']){
            $cond['r.MOBILE_NUMBER']=['like',$param['MOBILE_NUMBER']];
        }
        if($param['RFID_SN']){
            $cond['s.RFID_SN']=['like',$param['RFID_SN']];
        }

        $count=queryOracleCount('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_NON_MOTOR_OWNER r,DSSC3.W_DW_RFID_TAGS s',$cond);
        // $count=queryOracleCount('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_RFID_TAGS s',$cond);
        // $count=100000;
        $cond['page']=isset($param['page'])?$param['page']:1;
        $cond['limit']=isset($param['limit'])?$param['limit']:10;
        $rows=queryOracleSelect('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_NON_MOTOR_OWNER r,DSSC3.W_DW_RFID_TAGS s',$cond,'o.PLATE_NO,o.CAR_TYPE,o.CAR_BRAND,o.CAR_VIN,s.RFID_SN,s.INSTALLER,to_char(s.INSTA_DATE,\'yyyy-mm-dd hh24:mi:ss\') INSTA_DATE,r.NAME,r.ID_CARD_NUMBER,r.MOBILE_NUMBER,r.HOME_ADDRESS');
        // $rows=queryOracleSelect('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_RFID_TAGS s',$cond,'o.PLATE_NO,o.CAR_TYPE,o.CAR_BRAND,s.ID,s.RFID_SN,s.INSTALLER,to_char(s.INSTA_DATE,\'yyyy-mm-dd hh24:mi:ss\') INSTA_DATE');
        foreach($rows as &$val){
            $val['CAR_BRAND_TEXT']=(new SysDictData())->getValueByCode('CAR_BRAND_OPTION',$val['CAR_BRAND']);
            $val['CAR_TYPE_TEXT']=(new SysDictData())->getValueByCode('CAR_TYPE_OPTION',$val['CAR_TYPE']);
            $val['CARD_NUMBER_STR']=formatDataCardNumber($val['ID_CARD_NUMBER']);
        }
        $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;
    }

    public function getMapList(Request $request)
    {
        $param=$request->param();
        //联表条件o.rfid_id = s.id and o.owner_id = r.id 
        $cond=[
            '_string'=>'o.rfid_id = s.id',
        ];
        $keywords=$param['keywords'];
        if($keywords){
           $cond['_string']= '(s.RFID_SN like \'%'.$keywords.'%\' OR  o.PLATE_NO like \'%'.$keywords.'%\') AND o.rfid_id = s.id';
        }

        $count=queryOracleCount('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_RFID_TAGS s',$cond);

        $cond['page']=isset($param['page'])?$param['page']:1;
        $cond['limit']=isset($param['limit'])?$param['limit']:10;
        $rows=queryOracleSelect('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_RFID_TAGS s',$cond,'o.PLATE_NO,o.CAR_TYPE,o.CAR_BRAND,s.ID,s.RFID_SN,s.INSTALLER,to_char(s.INSTA_DATE,\'yyyy-mm-dd hh24:mi:ss\') INSTA_DATE');
        $conn=getOracleConnect();
        foreach($rows as &$val){
            $val['CAR_BRAND_TEXT']=(new SysDictData())->getValueByCode('CAR_BRAND_OPTION',$val['CAR_BRAND']);
            $val['CAR_TYPE_TEXT']=(new SysDictData())->getValueByCode('CAR_TYPE_OPTION',$val['CAR_TYPE']);


            $route_sql='select * from(SELECT RF_ID,to_char(RF_DATE,\'yyyy-mm-dd hh24:mi:ss\') RF_DATE FROM DSSC2.W_DW_RF_RECORD WHERE RF_FLAGID =\''.$val['RFID_SN'].'\' ORDER BY RF_DATE DESC )where rownum < 2';
          
          
            $stid = oci_parse($conn, $route_sql);
            oci_define_by_name($stid, 'RF_ID', $val['mac']);
            oci_define_by_name($stid, 'RF_DATE',  $val['online_time']);
            oci_execute($stid);
            oci_fetch($stid);
            oci_free_statement($stid);
            $staion_info =Db::table('stations')->where('mac',$val['mac'])->find();
            if($staion_info){
                $wgsLoc = \algorithm\Geometry::gcj02ToWgs84((float)$staion_info['latitude'],(float)$staion_info['longitude']);
                $val['longitude']=$wgsLoc['lng'];
                $val['latitude']=$wgsLoc['lat'];
                $val['address']=$staion_info['name'];
            }else{
                $val['longitude']='';
                $val['latitude']='';
                $val['address']='';

            }
           
        }
        $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;
    }
    public function getRfidTagsList(Request $request) {
        $param=$request->param();
        //联表条件o.rfid_id = s.id and o.owner_id = r.id 
        $cond=[];
        if($param['RFID_SN']){
            $cond['s.RFID_SN']=['like',$param['RFID_SN']];
        }
        $count=queryOracleCount('DSSC3.W_DW_RFID_TAGS s',$cond);
        $cond['page']=isset($param['page'])?$param['page']:1;
        $cond['limit']=isset($param['limit'])?$param['limit']:10;
        // $rows=queryOracleSelect('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_NON_MOTOR_OWNER r,DSSC3.W_DW_RFID_TAGS s',$cond,'o.PLATE_NO,s.RFID_SN,r. NAME,r.ID_CARD_NUMBER,r.MOBILE_NUMBER,r.HOME_ADDRESS');
        $rows=queryOracleSelect('DSSC3.W_DW_RFID_TAGS s',$cond,'s.ID,s.RFID_SN,s.RFID_TYPE');
        foreach($rows as &$val){
            // $val['CAR_BRAND_TEXT']=(new SysDictData())->getValueByCode('CAR_BRAND_OPTION',$val['CAR_BRAND']);
            $val['RFID_TYPE_TEXT']=(new SysDictData())->getValueByCode('RFID_TYPE_OPTION',$val['RFID_TYPE']);
            if( isset($param['bw_id']) && $param['bw_id'] ){
                $val['isAdd']=Db::table('rfid_with_bw')->where('bw_id',$param['bw_id'])->where('rfid',$val['RFID_SN'])->count();
            }
        }
        $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;
    }
    // public function indexAdd(Request $request) 
    // {
    //     ini_set('memory_limit','3072M');
    //     set_time_limit(0);
    //     $cond=[
    //         '_string'=>'o.rfid_id = s.id',
    //     ];
    //     // $count=queryOracleCount('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_NON_MOTOR_OWNER r,DSSC3.W_DW_RFID_TAGS s',$cond);
    //     $count=queryOracleCount('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_RFID_TAGS s',$cond);
    //     $to=ceil($count/10000);
    //     var_dump($to);
    //     for($i=1;$i<=$to;$i++){
    //         var_dump($i);
    //         $cond['page']=$i;
    //         $cond['limit']=10000;
    //         // $rows=queryOracleSelect('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_NON_MOTOR_OWNER r,DSSC3.W_DW_RFID_TAGS s',$cond,'o.PLATE_NO,s.RFID_SN,r. NAME,r.ID_CARD_NUMBER,r.MOBILE_NUMBER,r.HOME_ADDRESS');
    //         $rows=queryOracleSelect('DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_RFID_TAGS s',$cond,'o.PLATE_NO,o.CAR_TYPE,o.CAR_BRAND,s.ID,s.RFID_SN,s.INSTALLER,to_char(s.INSTA_DATE,\'yyyy-mm-dd hh24:mi:ss\') INSTA_DATE');
    //         // var_dump(count($rows));
    //         $data=[];
    //         foreach($rows as &$val){
    //             $item=array(
    //                 'license_plate'=>$val['PLATE_NO'],
    //                 'car_type'=>$val['CAR_TYPE'],
    //                 'brand_id'=>(int)$val['CAR_BRAND'],
    //                 'rfid_sn'=>$val['RFID_SN'],
    //                 'installer'=>$val['INSTALLER'],
    //                 'license_time'=>$val['INSTA_DATE'],
    //             );
    //             array_push($data,$item);
    //         }

            
    //         $success = Db::table('vehicles')->limit(1000)->insertAll($data);
    //         var_dump($success);
    //     }
 
       
    // }

    /**
     * 保存信息
     * @time 2022年01月20日 10:42
     * @param Request $request 
     */
    public function save(Request $request)
    {
  
        $installer=$request->user()->realname;
        $param=$request->param();
        $param['RFID_SN']=strtoupper($param['RFID_SN']);
        $param['PLATE_NO']=strtoupper($param['PLATE_NO']);
        $param['INSTA_DATE']=date('Y-m-d H:i:s',time());
        $param['INSTALLER']=$installer;
        $r=$this->execSaveVehicle($param);
        $param['DATA_TYPE']='vehicle_save';
        pushDataToRedisList($param);
        return CatchResponse::success($r);
    }
    private function execSaveVehicle($data){
        $conn=getOracleConnect();
        
        
        $sql='declare
            tagId number;
        begin
            INSERT INTO DSSC3.W_DW_RFID_TAGS("ID", "RFID_SN","INSTALLER","INSTA_DATE") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, \''.$data['RFID_SN'].'\',\''.$data['INSTALLER'].'\', TO_DATE(\''.$data['INSTA_DATE'].'\', \'SYYYY-MM-DD HH24:MI:SS\'))  returning ID into tagId;
            INSERT INTO DSSC3.W_DW_NON_MOTOR("ID", "RFID_ID","PLATE_NO","CAR_BRAND","CAR_TYPE") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, tagId,\''.$data['PLATE_NO'].'\',\''.$data['CAR_BRAND'].'\',\''.$data['CAR_TYPE'].'\');
        end;';
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        oci_free_statement($stid);
        return $r;
    }
    
    
    /**
     * 更新
     * @time 2022年01月20日 10:42
     * @param Request $request 
     * @param $id
     */
    public function update(Request $request, $id) : \think\Response
    {
        $params=$request->post();
        $conn=getOracleConnect();
        //查老的车牌
        $vehicle_sql='SELECT o.PLATE_NO FROM DSSC3.W_DW_NON_MOTOR o WHERE o.RFID_ID =\''.$id.'\' ';
        $OLD_NO='';
        $stid = oci_parse($conn, $vehicle_sql);
        oci_define_by_name($stid, 'PLATE_NO', $OLD_NO);
        oci_execute($stid);
        oci_fetch($stid);
        $params['OLD_NO']=$OLD_NO;

        $params['RFID_SN']=strtoupper($params['RFID_SN']);
        $params['PLATE_NO']=strtoupper($params['PLATE_NO']);
        // var_dump($params);
       
        // //更新
        $sql='UPDATE DSSC3.W_DW_RFID_TAGS  SET RFID_SN = \''.$params['RFID_SN'].'\' WHERE ID = \''.$id.'\' ';
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        
        $sql='UPDATE DSSC3.W_DW_NON_MOTOR  SET PLATE_NO = \''.$params['PLATE_NO'].'\',CAR_BRAND=\''.$params['CAR_BRAND'].'\',CAR_TYPE=\''.$params['CAR_TYPE'].'\' WHERE RFID_ID = \''.$id.'\' ';
        // var_dump($sql);
        $stid2 = oci_parse($conn, $sql);
        $r2 = oci_execute($stid2);
        
        $params['DATA_TYPE']='vehicle_update';
        pushDataToRedisList($params);

        return CatchResponse::success('修改成功');

    }
    
    /**
     * 删除
     * @time 2022年01月20日 10:42
     * @param $id
     */
    public function delete($id) : \think\Response
    {
        $conn=getOracleConnect();

        $sql='SELECT PLATE_NO FROM DSSC3.W_DW_NON_MOTOR  WHERE RFID_ID in ('.$id.')';
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        $rows=[];
        while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
            $rows[]=$row['PLATE_NO'];
        }
        $delete_data['PLATE_NO']=$rows;
        $delete_data['DATA_TYPE']='vehicle_delete';


        $sql='DELETE FROM DSSC3.W_DW_RFID_TAGS WHERE ID in ('.$id.')';
      
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);

        $sql='DELETE FROM DSSC3.W_DW_NON_MOTOR WHERE RFID_ID in ('.$id.')';
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);

        pushDataToRedisList($delete_data);
        return CatchResponse::success($r);
    }
    /**
     * 导出
     *
     * @time 2022年01月22日
     * @param Excel $excel
     * @param VehicleExport $vehicleExport
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @return \think\response\Json
     */
    public function export_vehicle(Excel $excel, VehiclesExport $VehicleExport)
    {
        return CatchResponse::success($excel->save($VehicleExport, Utils::publicPath('export/vehicles'), 'local', '车辆列表'));
    }
    /**
     * 导入车辆
     *
     * @time 2022年02月15日
     * @param Excel $excel
     * @param DeviceExport $deviceExport
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @return \think\response\Json
     */
    public function importVehicle(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 = []; //设备数据
        //循环读取每一张表
        $conn=getOracleConnect();
        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;
            }
            $brand_type_id = Db::table("sys_dict_type")->where('code', 'CAR_BRAND_OPTION')->value('id');
            $car_type_id = Db::table("sys_dict_type")->where('code', 'CAR_TYPE_OPTION')->value('id');
            

            $total += $highestRow - 1;
            // $sql='declare
            //     tagId number;
            // begin
            //     INSERT INTO DSSC3.W_DW_RFID_TAGS("ID", "RFID_SN","INSTALLER","INSTA_DATE") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, \''.$arr['RFID_SN'].'\',\''.$arr['INSTALLER'].'\', TO_DATE(\''.$arr['INSTA_DATE'].'\', \'SYYYY-MM-DD HH24:MI:SS\'))  returning ID into tagId;
            //     INSERT INTO DSSC3.W_DW_NON_MOTOR("ID", "RFID_ID","PLATE_NO","CAR_BRAND","CAR_TYPE") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, tagId,\''.$arr['PLATE_NO'].'\',\''.$arr['CAR_BRAND'].'\',\''.$arr['CAR_TYPE'].'\');
            // end;';
            // 预编译SQL语句
            $sql='declare
                tagId number;
            begin
                INSERT INTO DSSC3.W_DW_RFID_TAGS("ID", "RFID_SN","INSTALLER","INSTA_DATE") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, :col1,:col2, TO_DATE(:col3, \'SYYYY-MM-DD HH24:MI:SS\'))  returning ID into tagId;
                INSERT INTO DSSC3.W_DW_NON_MOTOR("ID", "RFID_ID","PLATE_NO","CAR_BRAND","CAR_TYPE") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, tagId,:col4,:col5,:col6);
            end;';
            $stmt = oci_parse($conn, $sql);
            for ($j = 2; $j <= $highestRow; $j++) {
                $arr = array(); //每条设备信息
                $arr['PLATE_NO'] = strtoupper(trim($sheet->getCell("A" . $j)->getFormattedValue())); 
                if(empty($arr['PLATE_NO']) ||  strlen($arr['PLATE_NO'])!=13){
                    $fail++;
                    debug_log('importVehicleError','车牌格式不正确,车牌号为：'.$arr['PLATE_NO']);
                    continue;
                }
                $brand = trim($sheet->getCell("B" . $j)->getFormattedValue());  
                $brand_id=Db::table("sys_dict_data")->where('type_id', $brand_type_id)->whereLike('value','%'.$brand.'%')->cache(60)->value('code');
                $arr['CAR_BRAND']=$brand_id?$brand_id:'99';
                
                $type = trim($sheet->getCell("C" . $j)->getFormattedValue());  
                $car_type=Db::table("sys_dict_data")->where('type_id', $car_type_id)->whereLike('value','%'.$type.'%')->cache(60)->value('code');
                $arr['CAR_TYPE']=$car_type?$car_type:'4';
                $arr['RFID_SN'] = trim($sheet->getCell("D" . $j)->getFormattedValue());  
                if(empty($arr['RFID_SN']) ||  strlen($arr['RFID_SN'])!=8){
                    $fail++;
                    debug_log('importVehicleError','标签格式格式不正确,标签为：'.$arr['RFID_SN']);
                    continue;
                }
                $arr['INSTA_DATE'] = trim($sheet->getCell("E" . $j)->getFormattedValue());  
                $arr['INSTALLER'] = trim($sheet->getCell("F" . $j)->getFormattedValue()); 

                //变量绑定 
                oci_bind_by_name($stmt, ':col1', $arr['RFID_SN']);
                oci_bind_by_name($stmt, ':col2', $arr['INSTALLER']);
                oci_bind_by_name($stmt, ':col3', $arr['INSTA_DATE']);
                oci_bind_by_name($stmt, ':col4', $arr['PLATE_NO']);
                oci_bind_by_name($stmt, ':col5', $arr['CAR_BRAND']);
                oci_bind_by_name($stmt, ':col6', $arr['CAR_TYPE']);

                // 添加到批处理
                oci_execute($stmt, OCI_DEFAULT);

                $arr['DATA_TYPE']='vehicle_save';
                pushDataToRedisList($arr);
                $success++;
                if( ($j%1000 == 0) || $j==$highestRow){
                    // 提交事务
                    $res=oci_commit($conn);
                }
                
            }
            // 释放资源
            oci_free_statement($stmt);
        }
        //关闭连接
        oci_close($conn); 
        return CatchResponse::success('共' . $total . '条数据,成功' . $success . '条，失败' . $fail . '条');

       
    }
    
}