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 and o.owner_id = r.id order by s.ID DESC',
        ];
        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['NAME']){
            $cond['r.NAME']=['like',$param['NAME']];
        }
        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);
        $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,s.ID,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');
        // $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;
    }

    /**
     * 保存信息
     * @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;
            ownId 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_OWNER("ID", "NAME","ID_CARD_NUMBER","MOBILE_NUMBER") VALUES (DSSC3.SEQ_W_DW_NON_MOTOR_OWNER.nextval,\''.$data['NAME'].'\',\''.$data['ID_CARD_NUMBER'].'\',\''.$data['MOBILE_NUMBER'].'\') returning ID into ownId;
            INSERT INTO DSSC3.W_DW_NON_MOTOR("ID", "RFID_ID","PLATE_NO","CAR_BRAND","CAR_TYPE","OWNER_ID") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, tagId,\''.$data['PLATE_NO'].'\',\''.$data['CAR_BRAND'].'\',\''.$data['CAR_TYPE'].'\',ownId);
        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);
        if(!$r){
            oci_rollback($conn);
            return CatchResponse::fail('修改失败');
        }
        $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.'\' ';
        $stid2 = oci_parse($conn, $sql);
        $r2 = oci_execute($stid2);
        if(!$r2){
            oci_rollback($conn);
            return CatchResponse::fail('修改失败');
        }
        //查车主ID
        $vehicle_sql='SELECT o.OWNER_ID FROM DSSC3.W_DW_NON_MOTOR o WHERE o.RFID_ID =\''.$id.'\' ';
        $owner_id='';
        $stid = oci_parse($conn, $vehicle_sql);
        oci_define_by_name($stid, 'OWNER_ID', $owner_id);
        oci_execute($stid);
        oci_fetch($stid);

        $sql='UPDATE DSSC3.W_DW_NON_MOTOR_OWNER SET MOBILE_NUMBER=\''.$params['MOBILE_NUMBER'].'\',NAME=\''.$params['NAME'].'\',ID_CARD_NUMBER=\''.$params['ID_CARD_NUMBER'].'\' WHERE ID = \''.$owner_id.'\' ';

        $stid3 = oci_parse($conn, $sql);
        $r3 = oci_execute($stid3);
        if(!$r3){
            oci_rollback($conn);
            return CatchResponse::fail('修改失败');
        }
        oci_commit($conn);
        $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';

         //查车主ID
         $sql='SELECT OWNER_ID 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['OWNER_ID'];
         }
         $owner_ids=implode(',',$rows);
         $sql='DELETE FROM DSSC3.W_DW_NON_MOTOR_OWNER WHERE ID in ('.$owner_ids.')';
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        if(!$r){
            oci_rollback($conn);
            return CatchResponse::fail('删除失败');
        }
        
        $sql='DELETE FROM DSSC3.W_DW_RFID_TAGS WHERE ID in ('.$id.')';
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        if(!$r){
            oci_rollback($conn);
            return CatchResponse::fail('删除失败');
        }
        $sql='DELETE FROM DSSC3.W_DW_NON_MOTOR WHERE RFID_ID in ('.$id.')';
        $stid = oci_parse($conn, $sql);
        $r = oci_execute($stid);
        if(!$r){
            oci_rollback($conn);
            return CatchResponse::fail('删除失败');
        }
        oci_commit($conn);

       
        pushDataToRedisList($delete_data);
       
        return CatchResponse::success(true);
    }
    /**
     * 导出
     *
     * @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 = []; //设备数据
        //循环读取每一张表
        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;
                ownId 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_OWNER("ID", "NAME","ID_CARD_NUMBER","MOBILE_NUMBER") VALUES (DSSC3.SEQ_W_DW_NON_MOTOR_OWNER.nextval,:col4,:col5,:col6) returning ID into ownId;
                INSERT INTO DSSC3.W_DW_NON_MOTOR("ID", "RFID_ID","PLATE_NO","CAR_BRAND","CAR_TYPE","OWNER_ID") VALUES (DSSC3.SEQ_W_DW_RFID_TAGS.nextval, tagId,:col7,:col8,:col9,ownId);
            

            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()); 
                $arr['NAME'] = trim($sheet->getCell("G" . $j)->getFormattedValue()); 
                $arr['ID_CARD_NUMBER'] = trim($sheet->getCell("H" . $j)->getFormattedValue()); 
                $arr['MOBILE_NUMBER'] = trim($sheet->getCell("I" . $j)->getFormattedValue()); 
                // var_dump($arr);
                //变量绑定 
                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['NAME']);
                oci_bind_by_name($stmt, ':col5', $arr['ID_CARD_NUMBER']);
                oci_bind_by_name($stmt, ':col6', $arr['MOBILE_NUMBER']);
                oci_bind_by_name($stmt, ':col6', $arr['PLATE_NO']);
                oci_bind_by_name($stmt, ':col6', $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);
                     
                 }

                // $r=$this->execSaveVehicle($arr);
                // if($r){
                //     $arr['DATA_TYPE']='vehicle_save';
                //     pushDataToRedisList($arr);
                //     $success++;
                // }else{
                //     $fail++;
                // }
                // var_dump($arr);
            //    array_push($data,$arr);
            }
            oci_free_statement($stmt);
        }
        // array_unique($data, SORT_REGULAR);
        // // var_dump($data);return CatchResponse::success();
        // $count = $this->deviceModel->limit(100)->insertAll($data);
        // if ($success = $count) {
        oci_close($conn); 
        return CatchResponse::success('共' . $total . '条数据,成功' . $success . '条，失败' . $fail . '条');

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