nbfd_tp3/Home/Lib/Action/NingboFangdaoSyncDataAction.class.php

<?php



class NingboFangdaoSyncDataAction extends Action {
			


 
	public  function kafka_index(  ){
		
		$broker_list = C('KAFKA_BROKER_LIST');
		
		if (empty($broker_list)) {
			exit("KAFKA_BROKER_LIST must be config!".PHP_EOL);
		}
		$group = C('SYNC_DATA_KAFKA_GROUP');
		if (empty($group)) {
			exit("SYNC_DATA_KAFKA_GROUP must be config!".PHP_EOL);
		}
		$topics = C('SYNC_DATA_KAFKA_TOPIC');
		if (empty($topics)) {
			exit("SYNC_DATA_KAFKA_TOPIC must be config!".PHP_EOL);
		}
		$topics = explode(',',$topics);
		
		// 从 topic ：rlstation_rfid_location 取轨迹
		$conf = new RdKafka\Conf();
		// Set a rebalance callback to log partition assignments (optional)（当有新的消费进程加入或者退出消费组时，kafka 会自动重新分配分区给消费者进程，这里注册了一个回调函数，当分区被重新分配时触发）
		$conf->setRebalanceCb(function (RdKafka\KafkaConsumer $kafka, $err, array $partitions = null) {
		    switch ($err) {
		        case RD_KAFKA_RESP_ERR__ASSIGN_PARTITIONS:
		            echo "Assign: ";
		            var_dump($partitions);
		            $kafka->assign($partitions);
		            break;
		
		         case RD_KAFKA_RESP_ERR__REVOKE_PARTITIONS:
		             echo "Revoke: ";
		             var_dump($partitions);
		             $kafka->assign(NULL);
		             break;
		
		         default:
		            throw new \Exception($err);
		    }
		});
		// Configure the group.id. All consumer with the same group.id will consume（ 配置groud.id 具有相同 group.id 的consumer 将会处理不同分区的消息，所以同一个组内的消费者数量如果订阅了一个topic， 那么消费者进程的数量多于这个topic 分区的数量是没有意义的。）
		// different partitions.
		$conf->set('group.id', $group);
		// Initial list of Kafka brokers（添加 kafka集群服务器地址）
		
		$conf->set('metadata.broker.list', $broker_list);
		$topicConf = new RdKafka\TopicConf();
		// Set where to start consuming messages when there is no initial offset in
		// offset store or the desired offset is out of range.
		// 'smallest': start from the beginning
		$topicConf->set('auto.offset.reset', 'latest');
		// Set the configuration to use for subscribed/assigned topics
		$conf->setDefaultTopicConf($topicConf);
		$consumer = new RdKafka\KafkaConsumer($conf);
		
		
		
		// 订阅轨迹数据topic
		$consumer->subscribe($topics);
		
		$config = C('ORACLE_CONFIG');
		if (empty($config)) {
			exit("ORACLE_CONFIG must be config!".PHP_EOL);
		}
		$host= $config['host'];
		$port=  $config['port'];
		$instance_name= $config['instance_name'];
		$username= $config['username'];
		$password=  $config['password'];
		
		/*
		$host= '192.168.100.23';
		$port=   '1521';
		$instance_name= 'helowin';
		$username=  'DSSC3';
		$password=  'Rliandssc3';
		*/
		$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);
		}
		
		while (true) {
			//var_dump($conn);
		    $message = $consumer->consume(120*1000);
		
		    switch ($message->err) {
		        case RD_KAFKA_RESP_ERR_NO_ERROR:
						
						 $data = json_decode($message->payload,true);
						 if( $data ){
							
							$this->addDataToNingbo($data,$conn);
		
							 //$this->addRfidDataToRenlian($data);
						 }
		            break;
		        case RD_KAFKA_RESP_ERR__PARTITION_EOF:
		            //echo "No more messages; will wait for more\n";
		            break;
		        case RD_KAFKA_RESP_ERR__TIMED_OUT:
		            echo "Timed out\n";
		            break;
		        default:
					    echo "default break";
						$this->debug_log( 'default_Log', $message->errstr() );
						$this->debug_log( 'default_Log', $message->err );
		            break;
		    }
		}
	}
	
 
	private  function addDataToNingbo( $data, $conn ){
		$type_arr=explode('_',$data['DATA_TYPE']);
		if($type_arr[0]=='vehicle'){
			 switch ($type_arr['1']) {
		      case 'save':
				 	if(!isset($data['NAME'])){
						$data['NAME']='绑定信息';
					}
				 	if(!isset($data['ID_CARD_NUMBER'])){
						$data['ID_CARD_NUMBER']='123456';
					}
				 	if(!isset($data['MOBILE_NUMBER'])){
						$data['MOBILE_NUMBER']='18888888888';
					}
				 	if(!isset($data['HOME_ADDRESS'])){
						$data['HOME_ADDRESS']='宁波';
					}
				 	$vehicle_sql='SELECT o.RFID_ID FROM DSSC3.W_DW_NON_MOTOR o WHERE o.PLATE_NO =\''.$data['OLD_NO'].'\' ';
				   $rfid_id='';
				   $stid = oci_parse($conn, $vehicle_sql);
				   oci_define_by_name($stid, 'RFID_ID', $rfid_id);
				   oci_execute($stid);
				   oci_fetch($stid);
				 	if($rfid_id){
						//如果存在则更新
						if(isset($data['RFID_SN']) && $data['RFID_SN']!='00000000' ){
							 $sql='UPDATE DSSC3.W_DW_RFID_TAGS  SET RFID_SN = \''.$data['RFID_SN'].'\' WHERE   ID = '.$rfid_id;
						   $stid = oci_parse($conn, $sql);
						   $r = oci_execute($stid);
						}
					   $sql='UPDATE DSSC3.W_DW_NON_MOTOR  SET CAR_BRAND=\''.$data['CAR_BRAND'].'\',CAR_TYPE=\''.$data['CAR_TYPE'].'\',CAR_VIN=\''.$data['CAR_VIN'].'\' WHERE PLATE_NO = \''.$data['PLATE_NO'].'\' ';
					   $stid2 = oci_parse($conn, $sql);
					   $r2 = oci_execute($stid2);
						//查车主ID
					   $vehicle_sql='SELECT o.OWNER_ID FROM DSSC3.W_DW_NON_MOTOR o WHERE o.PLATE_NO =\''.$data['PLATE_NO'].'\' ';
					   $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=\''.$data['MOBILE_NUMBER'].'\',NAME=\''.$data['NAME'].'\',ID_CARD_NUMBER=\''.$data['ID_CARD_NUMBER'].'\',HOME_ADDRESS=\''.$data['HOME_ADDRESS'].'\' WHERE ID = \''.$owner_id.'\' ';
					   $stid3 = oci_parse($conn, $sql);
					   $r3 = oci_execute($stid3);
					}else{
						$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","HOME_ADDRESS") VALUES (DSSC3.SEQ_W_DW_NON_MOTOR_OWNER.nextval,\''.$data['NAME'].'\',\''.$data['ID_CARD_NUMBER'].'\',\''.$data['MOBILE_NUMBER'].'\',\''.$data['HOME_ADDRESS'].'\') returning ID into ownId;
										 INSERT INTO DSSC3.W_DW_NON_MOTOR("ID", "RFID_ID","PLATE_NO","CAR_BRAND","CAR_TYPE","CAR_VIN","OWNER_ID") VALUES (DSSC3.SEQ_W_DW_NON_MOTOR.nextval, tagId,\''.$data['PLATE_NO'].'\',\''.$data['CAR_BRAND'].'\',\''.$data['CAR_TYPE'].'\',\''.$data['CAR_VIN'].'\',ownId);
									end;';
						$stid = oci_parse($conn, $sql);
						$r = oci_execute($stid);
						oci_free_statement($stid);
					}
				   
		        break;        
		      case 'update':
				 	if(!isset($data['NAME'])){
						$data['NAME']='绑定信息';
					}
				 	if(!isset($data['ID_CARD_NUMBER'])){
						$data['ID_CARD_NUMBER']='123456';
					}
				 	if(!isset($data['MOBILE_NUMBER'])){
						$data['MOBILE_NUMBER']='18888888888';
					}
				 	if(!isset($data['HOME_ADDRESS'])){
						$data['HOME_ADDRESS']='宁波';
					}
					$vehicle_sql='SELECT o.RFID_ID FROM DSSC3.W_DW_NON_MOTOR o WHERE o.PLATE_NO =\''.$data['OLD_NO'].'\' ';
				   $rfid_id='';
				   $stid = oci_parse($conn, $vehicle_sql);
				   oci_define_by_name($stid, 'RFID_ID', $rfid_id);
				   oci_execute($stid);
				   oci_fetch($stid);
					//更新
					if(isset($data['RFID_SN']) && $data['RFID_SN']!='00000000' ){
						 $sql='UPDATE DSSC3.W_DW_RFID_TAGS  SET RFID_SN = \''.$data['RFID_SN'].'\' WHERE   ID = '.$rfid_id;
					   $stid = oci_parse($conn, $sql);
					   $r = oci_execute($stid);
					}
				   $sql='UPDATE DSSC3.W_DW_NON_MOTOR  SET PLATE_NO = \''.$data['PLATE_NO'].'\',CAR_BRAND=\''.$data['CAR_BRAND'].'\',CAR_TYPE=\''.$data['CAR_TYPE'].'\',CAR_VIN=\''.$data['CAR_VIN'].'\' WHERE PLATE_NO = \''.$data['OLD_NO'].'\' ';
				   $stid2 = oci_parse($conn, $sql);
				   $r2 = oci_execute($stid2);
					//查车主ID
				   $vehicle_sql='SELECT o.OWNER_ID FROM DSSC3.W_DW_NON_MOTOR o WHERE o.PLATE_NO =\''.$data['OLD_NO'].'\' ';
				   $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=\''.$data['MOBILE_NUMBER'].'\',NAME=\''.$data['NAME'].'\',ID_CARD_NUMBER=\''.$data['ID_CARD_NUMBER'].'\',HOME_ADDRESS=\''.$data['HOME_ADDRESS'].'\' WHERE ID = \''.$owner_id.'\' ';
				   $stid3 = oci_parse($conn, $sql);
				   $r3 = oci_execute($stid3);
		      	 break;
				case 'delete':
				 	$rfid_ids=[];
				   $owner_ids=[];
				   $str=implode('\',\'',$data['PLATE_NO']);
		 		   $sql='SELECT RFID_ID,OWNER_ID FROM DSSC3.W_DW_NON_MOTOR  WHERE PLATE_NO in (\''.$str.'\')';
		     	  $stid = oci_parse($conn, $sql);
		     	  $r = oci_execute($stid);
		  		 	while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
		            $rfid_ids[]=$row['RFID_ID'];
						 $owner_ids[]=$row['OWNER_ID'];
		  			}
				 	$owner_str=implode('\',\'',$owner_ids);
					$sql='DELETE FROM DSSC3.W_DW_NON_MOTOR_OWNER WHERE ID in ('.$owner_str.')';
		         $stid = oci_parse($conn, $sql);
		         $r = oci_execute($stid);
				 
				 	$rfid_str=implode('\',\'',$rfid_ids);
				 	$sql='DELETE FROM DSSC3.W_DW_RFID_TAGS WHERE ID in (\''.$rfid_str.'\')';
				 	$stid = oci_parse($conn, $sql);
				 	$r = oci_execute($stid);
				 	$sql='DELETE FROM DSSC3.W_DW_NON_MOTOR WHERE PLATE_NO in (\''.$str.'\')';
				 	$stid = oci_parse($conn, $sql);
				 	$r = oci_execute($stid);
				 	break;
		      default:
		     	  break;
		   }
		}elseif($type_arr[0]=='station'){
			 switch ($type_arr['1']) {
		      case 'save':
				 	$data['created_at']=time();
				   M('stations')->createAdd($data);
		        break;        
		      case 'update':
					$where=array(
						'shortcode'=>$data['update_code'],
					);
				 	M('stations')->createSave($where,$data);
		      	  break;
				case 'delete':
				 	M('stations')->where(['shortcode'=>['in',$data['delete_code']]])->delete();
				 	break;
		      default:
		     	  break;
		   }
		}
		return;
	}
	
 
	public  function test(  ){
		$str='{"PLATE_NO":"6583421","RFID_SN":"45332","CAR TYPE":4,"CAR_BRAND":7,"NAME":"\u5f20\u4e09","ID_CARD_NUMBER":"4123334199508267321","MOBILE_NUMBER":"17834234","INSTA_DATE":"2023-06-22 10:30:59","INSTALLER":1,"DATA_TYPE":"vehicle_save"}
		';
		$data=json_decode($str,true);
		var_dump($data);exit;
		$type_arr=explode('_',$data['DATA_TYPE']);
		if($type_arr[0]=='vehicle'){
			 switch ($type_arr['1']) {
		      case 'save':
				   $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);
		        break;        
		      case 'update':
					//更新
		        $sql='UPDATE DSSC3.W_DW_NON_MOTOR o,DSSC3.W_DW_RFID_TAGS s SET s.RFID_SN = \''.$data['RFID_SN'].'\' WHERE o.PLATE_NO =\''.$data['OLD_NO'].'\'  AND o.rfid_id = s.id  ';
		        $stid = oci_parse($conn, $sql);
		        $r = oci_execute($stid);
		        $sql='UPDATE DSSC3.W_DW_NON_MOTOR  SET PLATE_NO = \''.$data['PLATE_NO'].'\',CAR_BRAND=\''.$data['CAR_BRAND'].'\',CAR_TYPE=\''.$data['CAR_TYPE'].'\' WHERE PLATE_NO = \''.$data['OLD_NO'].'\' ';
		        $stid2 = oci_parse($conn, $sql);
		        $r2 = oci_execute($stid2);
				 //查车主ID
		        $vehicle_sql='SELECT o.OWNER_ID FROM DSSC3.W_DW_NON_MOTOR o WHERE o.PLATE_NO =\''.$data['OLD_NO'].'\' ';
		        $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=\''.$data['MOBILE_NUMBER'].'\',NAME=\''.$data['NAME'].'\',ID_CARD_NUMBER=\''.$data['ID_CARD_NUMBER'].'\' WHERE ID = \''.$owner_id.'\' ';
		        $stid3 = oci_parse($conn, $sql);
		        $r3 = oci_execute($stid3);
		      	 break;
				case 'delete':
				 	$rfid_ids=[];
				   $owner_ids=[];
				   $str=implode('\',\'',$data['PLATE_NO']);
		 		   $sql='SELECT RFID_ID,OWNER_ID FROM DSSC3.W_DW_NON_MOTOR  WHERE PLATE_NO in (\''.$str.'\')';
		     	  $stid = oci_parse($conn, $sql);
		     	  $r = oci_execute($stid);
		  		 	while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
		            $rfid_ids[]=$row['RFID_ID'];
						 $owner_ids[]=$row['OWNER_ID'];
		  			}
				 	$owner_str=implode('\',\'',$owner_ids);
					$sql='DELETE FROM DSSC3.W_DW_NON_MOTOR_OWNER WHERE ID in ('.$owner_str.')';
		         $stid = oci_parse($conn, $sql);
		         $r = oci_execute($stid);
				 
				 	$rfid_str=implode('\',\'',$rfid_ids);
				 	$sql='DELETE FROM DSSC3.W_DW_RFID_TAGS WHERE ID in (\''.$rfid_str.'\')';
				 	$stid = oci_parse($conn, $sql);
				 	$r = oci_execute($stid);
				 	$sql='DELETE FROM DSSC3.W_DW_NON_MOTOR WHERE PLATE_NO in (\''.$str.'\')';
				 	$stid = oci_parse($conn, $sql);
				 	$r = oci_execute($stid);
				 	break;
		      default:
		     	  break;
		   }
		}elseif($type_arr[0]=='station'){
			 switch ($type_arr['1']) {
		      case 'save':
				 	$data['created_at']=time();
				   M('stations')->createAdd($data);
		        break;        
		      case 'update':
					$where=array(
						'shortcode'=>$data['update_code'],
					);
				 	M('stations')->createSave($where,$data);
		      	  break;
				case 'delete':
				 	M('stations')->where(['shortcode'=>['in',$data['delete_code']]])->delete();
				 	break;
		      default:
		     	  break;
		   }
		}
		return;
	}
	

}