<?php
namespace model;
use \model;
use \model\collocation;
use \model\ypApi;
use \model\excel;
/**
 * Class procate
 * @package model
 */
class carmodelna extends model
{
	public $proExpModelfields = [];

	public function __construct()
    {
		//综合导出
    	$this->proExpModelfields = [
    		'base_year_id'=>['value'=>lg('年份'), 'en'=>'Year', 'cn'=>'年份'],
			'base_make_name'=>['value'=>lg('品牌'), 'en'=>'Make', 'cn'=>'品牌'],
			'base_model_name'=>['value'=>lg('车型'), 'en'=>'Model', 'cn'=>'车型'],
        ];
	    parent::__construct();
	}

	/**
	  * @method 汽车品牌
	  * @author soul
	  */
	public function getBrand()
	{
		$where = [
			'GROUP'=>['brand_id'],
			'ORDER'=>['brand_initial','brand_name']
		];
		return $this->db->select('m_cn_model', ['brand_id', 'brand_name'], $where);
	}

	public function getBrandLetter()
	{
		$rows =  $this->db->select('m_cn_model(mk)', ['mk_f'=>raw('DISTINCT UPPER(LEFT(brand_initial, 1))')], ['ORDER'=>raw('UPPER(LEFT(brand_initial, 1))')]);
		return array_column($rows, 'mk_f');
	}
	

	
	/**
	  * @method 北美车型列表
	  * @author hongzhang
	  */
	public function getModelList($params)
	{
		$filters = $params['filters'];
		$page = empty($params['page'])? 1: $params['page'];
		$pagesize = $params['rows'];
		$sidx = empty($params['sidx'])? '': $params['sidx'];
		$sord = empty($params['sord'])? 'asc': $params['sord'];
		$orderby = empty($params['orderby'])? 'base_year_id DESC,base_make_name,base_model_name': $params['orderby'];

		$fields = '*';
		$table = 'na_vehicle_base';
		$where = $this->filter2medoo($filters,function(&$field,&$op,&$data){
        });
        $where = $this->listWhere($where,$page,$pagesize,$sidx,$sord,$orderby);

		if($page>0 && $pagesize>0){
            $pages = $this->paging($table,$where,$page,$pagesize);
			$where["LIMIT"] = [$pages['limit'],$pagesize];
		}
		$rows = $this->db->select($table,$fields,$where);
		$data = [
			'page'=> $page,
			'total'=> $pages['total'],
			'records' => $pages['records'],
			'rows' => $rows
		];
		return $data;
	}


	public function getModelReport($params, $rules)
	{
		$filters = $params['filters'];
		$page = 1;
		$pagesize = 1000;
		$sidx = empty($params['sidx'])? '': $params['sidx'];
		$sord = empty($params['sord'])? 'asc': $params['sord'];
		$orderby = 'brand_initial,brand_name';

		$fields = ['brand_id', 'brand_name', 'mod3_count'=>raw('count(*)')];
		$table = 'm_cn_model(mod)';
		$where = $this->filter2medoo($filters,function(&$field,&$op,&$data){
        });
        $where = $this->listWhere($where,$page,$pagesize,$sidx,$sord,$orderby);
		$where['GROUP'] = 'brand_id';
		if(!empty($params['brand_id'])) $where['brand_id'] = $params['brand_id'];
		if(!empty($params['keys'])) $where['mod3_id'] = explode(',', $params['keys']);
		if($page>0 && $pagesize>0){
            $pages = $this->paging($table,$where,$page,$pagesize);
			$where["LIMIT"] = [$pages['limit'],$pagesize];
		}
		
		$cateBrand = [];
		$cateids = [];
		if(!empty($rules['p_cate']))
		{
			foreach(explode(',', $rules['p_cate']) as $k=>$v)
			{
				$cateids[] = (int) $v;
			}
			$cateids = array_unique($cateids);
			$sql = sprintf("SELECT p_cate, brand_id, COUNT(DISTINCT pml_mod3_id) count FROM `pro_model_cn_link` 
			JOIN m_cn_model ON pml_mod3_id = mod3_id
			JOIN `pro` ON p_id = pml_p_id where p_cate in (%s)
			GROUP BY p_cate, brand_id", implode(',', $cateids));
			$rows = $this->db->query($sql)->fetchAll();
			foreach($rows as $v)
			{
				$cateBrand[$v['brand_id']][$v['p_cate']] = $v['count'];
			}
		}
		$brandArr = $this->db->select($table,$fields,$where);
		foreach($brandArr as $k=>$v)
		{
			foreach($cateids as $id)
			{
				$v['cate_'.$id] = empty($cateBrand[$v['brand_id']][$id]) || empty($v['mod3_count'])? '': sprintf("%.2f%%", $cateBrand[$v['brand_id']][$id]/$v['mod3_count']*100);
			}
			$brandArr[$k] = $v;
		}

		$data = [
			'page'=> $page,
			'total'=> $pages['total'],
			'records' => $pages['records'],
			'rows' => $brandArr
		];
		return $data;
	}
	

	/**
	  * @method 同步中国车型
	  * @author soul
	  */
	public function uploadModel()
	{
		ini_set('memory_limit', \mvc::$cfg['bigtable_memory_limit']);
		set_time_limit(0);
		$table = 'm_cn_model';
		$ypApi = new ypApi();
		$modArr = $ypApi->Get('client/getYpcModel');
		$comfileds = [];
		foreach($modArr as $k=>$v)
		{
			$v['mod3_atime'] = date('Y-m-d', $v['mod3_atime']);
			$v['mod3_etime'] = date('Y-m-d', $v['mod3_etime']);
			$newV = [
				'brand_id'=>$v['brand_id'],
				'brand_code'=>$v['brand_code'],
				'brand_name'=>$v['brand_name'],
				'brand_initial'=>$v['brand_initial'],
				'make_id'=>$v['make_id'],
				'make_name'=>$v['make_name'],
				'mod1_id'=>$v['mod1_id'],
				'mod1_name'=>$v['mod1_name'],
				'mod2_id'=>$v['mod2_id'],
				'mod2_name'=>$v['mod2_name'],
				'mod2_start_year'=>$v['mod2_start_year'],
				'mod2_end_year'=>$v['mod2_end_year'],
				'mod2_pc'=>$v['mod2_pc'],
				'mod2_cv'=>$v['mod2_cv'],
				'mod3_id'=>$v['mod3_id'],
				'mod3_name'=>$v['mod3_name'],
				'mod3_start_year'=>$v['mod3_start_year'],
				'mod3_end_year'=>$v['mod3_end_year'],
				'mod3_engine'=>$v['mod3_engine'],
				'mod3_kw'=>$v['mod3_kw'],
				'mod3_cc'=>$v['mod3_cc'],
				'mod3_transmission'=>$v['mod3_transmission'],
				'mod3_fuel'=>$v['mod3_fuel'],
				'mod3_body'=>$v['mod3_body'],
				'mod3_front_brake'=>$v['mod3_front_brake'],
				'mod3_rear_brake'=>$v['mod3_rear_brake'],
				'mod3_parking_brake'=>$v['mod3_parking_brake'],
				'mod3_parking_brake'=>$v['mod3_parking_brake'],
				'mod3_chassis'=>$v['mod3_chassis'],
				'mod3_atime'=>$v['mod3_atime'],
				'mod3_etime'=>$v['mod3_etime']
			];
			if(empty($comfileds))
			{
				$comfileds = $newV;
				unset($comfileds['mod3_atime']);
				unset($comfileds['mod3_atime']);
				$comfileds = array_keys($comfileds);
			}
			$newModelArr[$newV['mod3_id']] = $newV;
		}
		
		$oldModelArr = $this->db->select($table, '*', []);
		$oldModelArr = array_column($oldModelArr, null, 'mod3_id');

		//开始比较
		$comResArr = [];
		$addAny = [];
		$this->db->Begin();
		foreach($newModelArr as $mod3Id=>$newV)
		{
			if(!empty($oldModelArr[$mod3Id]))
			{
				$change = $newV;
				$same = true;
				$oldV = $oldModelArr[$mod3Id];
				foreach($comfileds as $filed)
				{
					if($newV[$filed] != $oldV[$filed])
					{
						$same = false;
						$change[$filed] = [
							'val'=>$oldV[$filed].' 改为 '.$newV[$filed],
							'color'=>'FF0000'
						];
					}
				}
				if(!$same)
				{
					$change['act'] = ['val'=>'修改', 'color'=>'FFA500'];
					$comResArr[] = $change;
					$this->db->update($table, $newV, ['mod3_id'=>$newV['mod3_id']]);
				}
			}
			else
			{
				$addAny[] = $newV;
				$change = $newV;
				$change['act'] = ['val'=>'新增', 'color'=>'008000'];
				$comResArr[] = $change;
			}
		}
		
		$delIdArr = [];
		foreach($oldModelArr as $mod3Id=>$oldV)
		{
			if(empty($newModelArr[$mod3Id]))
			{
				$delIdArr[] = $mod3Id;
				$change = $oldV;
				$change['act'] = ['val'=>'删除', 'color'=>'FF0000'];
				$comResArr[] = $change;
			}
		}
		if(!empty($addAny))
			$this->db->insert($table, $addAny);
		if(!empty($delIdArr))
			$this->db->delete($table, ['mod3_id'=>$delIdArr]);

		//将比较结果导出excel
		//比较的字段
		$headArr = [
			'mod3_id'=>'车型ID',
			'act'=>'类型',
			'brand_name'=>'品牌',
			'make_name'=>'生产商',
			'mod2_name'=>'车代',
			'mod3_name'=>'车型',
			'mod3_start_year'=>'开始年',
			'mod3_end_year'=>'结束年',
			'mod3_engine'=>'发动机',
			'mod3_kw'=>'功率',
			'mod3_cc'=>'排量',
			'mod3_transmission'=>'变速器',
			'mod3_drive'=>'驱动方式',
			'mod3_fuel'=>'燃料',
			'mod3_body'=>'车身形式',
			'mod3_front_brake'=>'前制动器',
			'mod3_rear_brake'=>'后制动器',
			'mod3_parking_brake'=>'驻车制动'
		];
		
		$filepath = \mvc::$cfg['dir']['files'].'/excel/cnmodel/';
		if(!is_dir($filepath)) \helper\file::mkdir($filepath, 0777);
		$filename = getOpuser().'-'.date('YmdHis').'.xls';
		$url = \mvc::$cfg['path']['files'].'/excel/cnmodel/'.$filename;
		$file =  $filepath.$filename;

		$dataArr = $comResArr;
		$dataArr['fields'] = $headArr;
		$excel = new excel();
		$excel->writer($dataArr, $file, true);
		$this->db->End();
		$return = [
			'url'=>$url,
			'filename'=>$filename
		];
		return $return;
	}

	public function getChkIdsByParams($param)
	{	
		$rows  = [];
		$fromid = (int)$param['fromid'];
		if(!$fromid && !$param['mod']) return [];
		if($param['mod']=='oe'){
			$rows = $this->db->select('oe_model_cn_link','oml_mod3_id',['oml_oe_id'=>(int)$param['fromid']]);
		}elseif ($param['mod']=='pro') {
			$rows = $this->db->select('pro_model_cn_link','pml_mod3_id',['pml_p_id'=>(int)$param['fromid']]);
		}elseif ($param['mod']=='protemp') {
			$rows = $this->db->select('pro_model_cn_link_temp','pmlt_mod3_id',['pmlt_p_id'=>(int)$param['fromid']]);
		}
		return $rows;
	}

	public function getMod3Des($id)
	{
		$modArr = $this->db->get('m_cn_model', '*', ['mod3_id'=>(int)$id]);
		return $modArr;
	}

	/**
	 * @method 获取带筛选条件的品牌列表
	 * @author hongzhang
	 * @copyright 2026-01-09
	 */
	public function getBrandListByFilter($param = [])
	{
		$table = 'm_cn_model';
		$join = [
			'[><]oe_model_cn_link(oml)'=>['mod3_id'=>'oml_mod3_id'],
			// '[><]oe_item'=>['oml.oml_oe_id'=>'oe_id']
		];
		$where['ORDER'] = ['brand_initial','brand_name'];
		$where['GROUP'] = 'brand_id';
		switch($param['m_type']){
			case '1':
				$where['mod2_pc'] = 1;
				break;
			case '2':
				$where['mod2_cv'] = 1;
				break;
			case 'hot':
				$v = $this->db->get('ckv','v',['k'=>'hot_cnbrands']);
				if($v){
					$ids = json_decode($v, true);
				}
				$where['brand_id'] = $ids;
				break;
		}
		$arr =  $this->db->select($table, $join, ['brand_id','brand_name','brand_code','brand_initial'=>raw('LEFT(brand_initial, 1)')], $where);
		foreach ($arr as $key => &$val) {
			if($val['brand_code']){
				$val['brand_logo'] = 'https://www.paojd.cn/static/images/chebiao/70/'.$val['brand_code'].'.png';
			}else{
				$val['brand_logo'] = \mvc::$cfg['urls']['outfiles'].'/static/images/nologo.png';
			}
			
		}
		unset($val);
		return $arr;
	}

	public function getMakeListByFilter($param = [])
	{
		$table = 'm_cn_model';
		$join = [
			'[><]oe_model_cn_link(oml)'=>['mod3_id'=>'oml_mod3_id'],
			// '[><]oe_item'=>['oml.oml_oe_id'=>'oe_id']
		];
		$where['brand_id'] = $param['brand_id'];
		$where['ORDER'] = ['make_name'];
		$where['GROUP'] = 'make_id';
		switch($param['m_type']){
			case '1':
				$where['mod2_pc'] = 1;
				break;
			case '2':
				$where['mod2_cv'] = 1;
				break;
		}
		return $this->db->select($table, $join, ['make_id','make_name'], $where);	
	}

	public function getMod1ListByFilter($param = [])
	{
		$table = 'm_cn_model';
		$join = [
			'[><]oe_model_cn_link(oml)'=>['mod3_id'=>'oml_mod3_id'],
			// '[><]oe_item'=>['oml.oml_oe_id'=>'oe_id']
		];
		if($param['brand_id']){
			$where['brand_id'] = $param['brand_id'];
		}
		
		if($param['make_id']){
			$where['make_id'] = $param['make_id'];
		}
		$where['ORDER'] = ['mod1_name'];
		$where['GROUP'] = 'mod1_id';
		switch($param['m_type']){
			case '1':
				$where['mod2_pc'] = 1;
				break;
			case '2':
				$where['mod2_cv'] = 1;
				break;
		}
		return $this->db->select($table, $join, ['mod1_id','mod1_name','make_id','make_name'], $where);	
	}

	/**
	 * @method 获取带筛选条件的车系列表
	 * @author hongzhang
	 * @copyright 2026-01-10
	 */
	public function getMod2ListByFilter($param = [])
	{
		$table = 'm_cn_model';
		$join = [
			'[><]oe_model_cn_link(oml)'=>['mod3_id'=>'oml_mod3_id'],
			// '[><]oe_item'=>['oml.oml_oe_id'=>'oe_id']
		];
		if($param['brand_id']){
			$where['brand_id'] = $param['brand_id'];
		}
		if($param['mod1_id']){
			$where['mod1_id'] = $param['mod1_id'];
		}
		// $where['brand_id'] = $param['brand_id'];
		$where['GROUP'] = 'mod2_id';
		switch($param['m_type']){
			case '1':
				$where['mod2_pc'] = 1;
				break;
			case '2':
				$where['mod2_cv'] = 1;
				break;
		}
		return $this->db->select($table, $join, ['mod2_id', 'mod2_name','mod1_id','mod1_name','mod2_start_year','mod2_end_year'], $where);
	}

	/**
	 * @method 根据参数筛选车型（品牌+车系   发动机号）
	 * @author hongzhang
	 * @copyright 2026-01-10
	 */
	public function getModelCarListByFilter($param = [])
	{	
		//分页
		$page = $param['page']?:1;
        $pagesize = $_COOKIE['pagesize']?:10;

		$table = 'm_cn_model';
		$join = [
			'[><]oe_model_cn_link(oml)'=>['mod3_id'=>'oml_mod3_id'],
			'[><]oe_item'=>['oml.oml_oe_id'=>'oe_id']
		];
		if($param['brand_id']){
			$where['brand_id'] = $param['brand_id'];
		}
		if($param['mod2_id']){
			$where['mod2_id'] = $param['mod2_id'];
		}
		if($param['mod3_engine']){
			$where['mod3_engine[~]'] = $param['mod3_engine'];
		}
		if($param['mod3_start_year']){
			$where['mod3_start_year'] = $param['mod3_start_year'];
		}
		if($param['mod3_end_year']){
			$where['mod3_end_year'] = $param['mod3_end_year'];
		}
		if($param['mod3_cc']){
			$where['mod3_cc'] = $param['mod3_cc'];	
		}
		if($param['mod3_drive']){
			$where['mod3_drive'] = $param['mod3_drive'];	
		}
		$where['GROUP'] = 'mod3_id';
		$count = $this->db->count($table,$join,'*',$where);
		if($count>0){
			$where['LIMIT'] = [($page-1)*$pagesize,$pagesize];
			$arr =  $this->db->select($table,$join,'*', $where);
		}
		return  [
            'page'=>$page,
            'pagesize'=>$pagesize,
            'recordCount'=>$count?:0,
            'list'=>$arr?:[]
        ];
	}

	public function getBrandInfo($brand_id)
	{
		$arr = $this->db->get('m_cn_model', ['brand_id','brand_code','brand_name'], ['brand_id'=>(int)$brand_id]);
		$arr['brand_logo'] = !empty($arr['brand_code'])? 'https://www.paojd.cn/static/images/chebiao/70/'.$arr['brand_code'].'.png': \mvc::$cfg['urls']['outfiles'].'/static/images/nologo.png';
		return $arr;
	}

	public function getCarDetail($mod3_id)
	{
		$modArr = $this->db->get('m_cn_model', '*', ['mod3_id'=>(int)$mod3_id]);
		$modArr['brand_logo'] = !empty($modArr['brand_code'])? 'https://www.paojd.cn/static/images/chebiao/70/'.$modArr['brand_code'].'.png': \mvc::$cfg['urls']['outfiles'].'/static/images/nologo.png';
		return $modArr;
	}

	public function getModInfo($mod1_id)
	{
		$arr = $this->db->get('m_cn_model', '*', ['mod1_id'=>(int)$mod1_id,'GROUP'=>'mod1_id']);
		return $arr;
	}

	public function getModelAutoValues($search_field, $filters)
	{	
		$refield = [
			'engine'=>'mod3_engine',
			'year_from'=>'mod3_start_year',
			'year_to'=>'mod3_end_year',
			'mod3_engine'=>'mod3_engine',
			'mod3_start_year'=>'mod3_start_year',
			'mod3_end_year'=>'mod3_end_year',
			'mod3_kw'=>'mod3_kw',
			'mod3_cc'=>'mod3_cc',
			'mod3_drive'=>'mod3_drive'
		];
		
		if(!isset($refield[$search_field])) return [];

		$where = [];

		// 处理搜索值
		$searchValue = $filters['search_value'] ?? '';
		if (!empty($searchValue) && isset($refield[$search_field])) {
			$where[$refield[$search_field] . '[~]'] = $searchValue;
		}

		// 检查用户输入的值是否直接匹配
		$userInputValue = $filters['user_input_value'] ?? '';
		$exactMatchExists = false;
		
		if (!empty($userInputValue) && isset($refield[$search_field])) {
			// 检查用户输入的值是否确实存在
			$exactWhere = $where; // 复制当前条件
			$exactWhere[$refield[$search_field]] = $userInputValue;
			$exactWhere['LIMIT'] = 1;
			
			$join = [
				'[><]oe_model_cn_link(oml)'=>['mod3_id'=>'oml_mod3_id'],
				'[><]oe_item'=>['oml.oml_oe_id'=>'oe_id']
			];
			
			$exactResult = $this->db->select('m_cn_model', $join, [$refield[$search_field]], $exactWhere);
			
			if (!empty($exactResult)) {
				$exactMatchExists = true;
			}
		}
	
	

		foreach ($filters as $field => $val) {
			if(!$val) continue;
			 // 跳过特殊字段
			if (in_array($field, ['search_field', 'search_value','user_input_value'])) {
				continue;
			}
			switch ($field) {
				case 'brand_id':
					$where['brand_id'] = (int)$val;
					break;
				case 'make_id':
					$where['make_id'] = (int)$val;
					break;
				case 'mod1_id':
					$where['mod1_id'] = (int)$val;
					break;
				case 'mod2_id':
					$where['mod2_id'] = (int)$val;
					break;
				case 'mod3_engine':
				case 'engine':
					$where['mod3_engine'] = $val;
					break;
				case 'mod3_start_year':
				case 'year_from':
					$where['mod3_start_year'] = (int)$val;
					break;
				case 'mod3_end_year':
				case 'year_to':
					$where['mod3_end_year'] = (int)$val;
					break;
				case 'mod3_cc':
					$where['mod3_cc'] = (int)$val;
					break;
				case 'mod3_drive':
					$where['mod3_drive'] = $val;
					break;
			}
		}
		$join = [
			'[><]oe_model_cn_link(oml)'=>['mod3_id'=>'oml_mod3_id'],
			'[><]oe_item'=>['oml.oml_oe_id'=>'oe_id']
		];
		$where[$refield[$search_field].'[!]'] = '';
		$where['LIMIT'] = 100;
		$where['GROUP'] = $refield[$search_field];
		$rows = $this->db->select('m_cn_model',$join,[$refield[$search_field].'(value)'], $where);
		if ($exactMatchExists && !empty($userInputValue)) {
			$found = false;
			foreach ($rows as $row) {
				if (strtolower($row['value']) === strtolower($userInputValue)) {
					$found = true;
					break;
				}
			}
			
			if (!$found) {
				// 将用户输入的值插入到结果数组的第一位
				array_unshift($rows, ['value' => $userInputValue]);
			}
		}
		return $rows;
	}

	/**
	 * @method 获取所有零件对应的北美车型固定筛选项
	 * @author hongzhang
	 * @copyright 2026-04-08
	 */
	public function getCarmodelnaFilterList($params=[]){
		$table = 'na_vehicle_base';
		$year = isset($params['year']) ? (int)$params['year'] : 0;
		$baseMakeId = isset($params['make_id']) ? (int)$params['make_id'] : 0;
		$where = "base_id IN (SELECT DISTINCT oml_base_id FROM oe_model_na oml)";
		if(!$year){
			$sql = sprintf(
				"SELECT DISTINCT base_year_id FROM %s WHERE %s ORDER BY base_year_id DESC",
				$table,
				$where
			);
			$arr = $this->db->query($sql)->fetchAll();
			return array_column($arr,'base_year_id');
		}
		if($year && !$baseMakeId){
			$sql = sprintf(
				"SELECT DISTINCT base_make_id,base_make_name FROM %s WHERE %s AND base_year_id = %d ORDER BY base_make_name ASC",
				$table,
				$where,
				$year
			);
			$arr = $this->db->query($sql)->fetchAll();
			return array_values($arr);
		}
		if($year && $baseMakeId){
			$sql = sprintf(
				"SELECT DISTINCT base_model_id,base_model_name FROM %s WHERE %s AND base_year_id = %d AND base_make_id = %d ORDER BY base_model_name ASC",
				$table,
				$where,
				$year,
				$baseMakeId
			);
			$arr = $this->db->query($sql)->fetchAll();
			return array_values($arr);
		}
		return [];
	}

	/**
	 * @method 获取所选北美车型对应的发动机选项列表
	 * @author hongzhang
	 * @copyright 2026-04-08
	 */
	public function getEngineOptions($params=[]){
		//获取北美车型的baseid
		$year = $params['year'];
		$makeId = $params['make_id'];
		$modelId = $params['model_id'];
		if(!$year || !$makeId || !$modelId){
			return [];
		}
		$baseId = $this->db->get('na_vehicle_base','base_id',['base_year_id'=>$year,'base_make_id'=>$makeId,'base_model_id'=>$modelId]);
		if(!$baseId){
			return [];
		}
		// 获取配置的前端显示格式
		$format = \mvc::$cfg['NA_CARMODEL']['ENGINE']['format']??[];
		// 获取该车型下所有发动机列表
		$sql1Where = sprintf("nv.veh_base_id = %d",$baseId);
		// 根据配置的显示格式对选项进行排序
		$sql1Order =  sprintf("eng_base_block_type,eng_base_cylinders,eng_base_liter,eng_base_cc,eng_base_cid");
		if(count($format) > 0){
			$sql1Order .= sprintf(",%s",implode(',',$format));
		}
		$sql1 = sprintf(
			"SELECT DISTINCT eng_id,eng_base_block_type,eng_base_cylinders,eng_base_liter,eng_base_cc,eng_base_cid,eng_vin_name,eng_fuel_type,eng_cylinder_head_type,eng_aspiration 
			 FROM na_vehicle nv
			 LEFT JOIN na_vehicle_engine nve ON nv.veh_id = nve.vee_mod_id
			 LEFT JOIN na_entity_engine nee ON nve.vee_eng_id = nee.eng_id
			 WHERE %s ORDER BY %s",$sql1Where,$sql1Order);
		$allEngineList = $this->db->query($sql1)->fetchAll();
		// 没有关联发动机的项，代表oml_id对应的这条项的发动机是全选的
		$sql2 = sprintf("SELECT oml_id FROM oe_model_na WHERE oml_base_id = %d AND oml_id NOT IN (SELECT DISTINCT ops_oml_id FROM oe_model_na_link WHERE ops_type = 'engine')",$baseId);
		$allSelectedEngineList = $this->db->query($sql2)->fetchAll();
		$allSelectedEngineList = array_column($allSelectedEngineList,'oml_id');
		// 关联了发动机的项，则代表oml_id只包含了关联的发动机
		$sql3 = sprintf(
			"SELECT oml_id,eng_id 
			 FROM oe_model_na oml
			 JOIN oe_model_na_link l1 ON oml.oml_id = l1.ops_oml_id AND l1.ops_type = 'engine'
			 LEFT JOIN na_entity_engine nee ON l1.ops_dataid = nee.eng_id
			 WHERE oml_base_id = %d",$baseId);
		$engineOmlList = $this->db->query($sql3)->fetchAll();
		$engineToOml = [];
		foreach($engineOmlList as $item){
			if(!$engineToOml[$item['eng_id']]){
				$engineToOml[$item['eng_id']] = [];
			}
			if(!in_array($item['oml_id'],$engineToOml[$item['eng_id']])){
				$engineToOml[$item['eng_id']][] = $item['oml_id'];
			}
		}
		foreach($allEngineList as &$item){
			if(count($allSelectedEngineList) > 0){
				// 先添加全选发动机的项
				$item['oml_id'] = $allSelectedEngineList;
			}
			if($engineToOml[$item['eng_id']]){
				$item['oml_id'] = array_merge($item['oml_id']??[],$engineToOml[$item['eng_id']]);
			}
			// 任何情况都展示base信息
			$content = sprintf('%s%s',$item['eng_base_block_type'], $item['eng_base_cylinders']);
			if ($item['eng_base_liter'] != '-') {
				$content = sprintf('%s %sL', $content, $item['eng_base_liter']);
			}
			if ($item['eng_base_cc'] != '-') {
				$content = sprintf('%s %scc', $content, $item['eng_base_cc']);
			}
			if ($item['eng_base_cid'] != '-') {
				$content = sprintf('%s %scid', $content, $item['eng_base_cid']);
			}
			foreach($format as $v){
				switch($v){
					case'eng_vin_name':
						if ($item['eng_vin_name'] != '-') {
							$content = sprintf('%s | %s', $content, $item['eng_vin_name']);
						}
					break;
					case'eng_fuel_type':
						if ($item['eng_fuel_type'] != '-') {
							$content = sprintf('%s | %s', $content, $item['eng_fuel_type']);
						}
					break;
					case'eng_cylinder_head_type':
						if ($item['eng_cylinder_head_type'] != '-') {
							$content = sprintf('%s | %s', $content, $item['eng_cylinder_head_type']);
						}
					break;
					case'eng_aspiration':
						if ($item['eng_aspiration'] != '-') {
							$content = sprintf('%s | %s', $content, $item['eng_aspiration']);
						}
					break;
				}
			}
			$item['engine_show'] = $content;
		}
		//过滤掉oml_id为空的项,返回最终的筛选列表
		$engineOptions = array_values(array_filter($allEngineList,function($item){
			return isset($item['oml_id']);
		}));
		return $engineOptions;
	} 

	/**
	 * @method 获取所选北美车型对应的子车型选项列表
	 * @author hongzhang
	 * @copyright 2026-04-08
	 */
	public function getSubModelOptions($params=[]){
		//获取北美车型的baseid
		$year = $params['year'];
		$makeId = $params['make_id'];
		$modelId = $params['model_id'];
		if(!$year || !$makeId || !$modelId){
			return [];
		}
		$baseId = $this->db->get('na_vehicle_base','base_id',['base_year_id'=>$year,'base_make_id'=>$makeId,'base_model_id'=>$modelId]);
		if(!$baseId){
			return [];
		}
		// 获取该车型下所有子车型列表
		$allSubmodelList = $this->db->select('na_vehicle',['veh_submodel_id','veh_submodel_name'],['veh_base_id'=>$baseId,'GROUP'=>'veh_submodel_id','ORDER'=>'veh_submodel_name']);
		// 没有关联子车型的项，代表oml_id对应的这条项的子车型是全选的
		$sql1 = sprintf("SELECT oml_id FROM oe_model_na WHERE oml_base_id = %d AND oml_id NOT IN (SELECT DISTINCT ops_oml_id FROM oe_model_na_link WHERE ops_cat = 'SubModel')",$baseId);
		$allSelectedOmlList = $this->db->query($sql1)->fetchAll();
		$allSelectedOmlList = array_column($allSelectedOmlList,'oml_id');
		// 关联了子车型的项，则代表oml_id只包含了关联的子车型
		$sql2 = sprintf(
			"SELECT  oml_id,vss_dataid AS veh_submodel_id
			 FROM oe_model_na oml
			 JOIN oe_model_na_link l1 ON oml.oml_id = l1.ops_oml_id AND l1.ops_cat = 'SubModel'
			 LEFT JOIN na_vehicle_single vss ON l1.ops_dataid = vss.vss_auto_id AND vss.vss_type = 'SubModel'
			 WHERE oml_base_id = %d ",$baseId);
		$submodelOmlList = $this->db->query($sql2)->fetchAll();
		$submodellToOml = [];
		foreach($submodelOmlList as $item){
			if(!$submodellToOml[$item['veh_submodel_id']]){
				$submodellToOml[$item['veh_submodel_id']] = [];
			}
			// 获取不同子车型veh_submodel_id对应的oml_id列表
			if(!in_array($item['oml_id'],$submodellToOml[$item['veh_submodel_id']])){
				$submodellToOml[$item['veh_submodel_id']][] = $item['oml_id'];
			}
		}
		foreach($allSubmodelList as &$item){
			if(count($allSelectedOmlList) > 0){
				// 先添加全选子车型的项
				$item['oml_id'] = $allSelectedOmlList;
			}
			if($submodellToOml[$item['veh_submodel_id']]){
				$item['oml_id'] = array_merge($item['oml_id']??[],$submodellToOml[$item['veh_submodel_id']]);
			}
		}
		// 过滤掉oml_id为空的项,返回最终的筛选列表
		$submodelOptions = array_values(array_filter($allSubmodelList, function($item){
			return isset($item['oml_id']);
		}));
		return $submodelOptions;
	}

	/**
	 * @method 获取所选北美车型对应的车身选项列表
	 * @author hongzhang
	 * @copyright 2026-04-09
	 */
	public function getBodystyleOptions($params=[]){
		//获取北美车型的baseid
		$year = $params['year'];
		$makeId = $params['make_id'];
		$modelId = $params['model_id'];
		if(!$year || !$makeId || !$modelId){
			return [];
		}
		$baseId = $this->db->get('na_vehicle_base','base_id',['base_year_id'=>$year,'base_make_id'=>$makeId,'base_model_id'=>$modelId]);
		if(!$baseId){
			return [];
		}
		// 获取该基础车型下所有的车身数据列表
		$sql1 = sprintf(
			"SELECT DISTINCT body_id,body_doors_name,body_type_name
			 FROM na_vehicle nv
			 LEFT JOIN na_vehicle_bodystyle nvb ON nv.veh_id = nvb.vbs_mod_id
			 LEFT JOIN na_entity_bodystyle neb ON nvb.vbs_body_id = neb.body_id
			 WHERE nv.veh_base_id = %d ORDER BY body_doors_name,body_type_name",$baseId);
		$allBodystyleList = $this->db->query($sql1)->fetchAll();
		// 没有关联车身信息的项，代表oml_id对应的这条项的车身是全选的
		$sql2 = sprintf("SELECT oml_id FROM oe_model_na WHERE oml_base_id = %d AND oml_id NOT IN (SELECT DISTINCT ops_oml_id FROM oe_model_na_link WHERE ops_type = 'bodystyle')",$baseId);
		$allSelectedOmlList = $this->db->query($sql2)->fetchAll();
		$allSelectedOmlList = array_column($allSelectedOmlList,'oml_id');
		// 关联了车身信息的项，则代表oml_id只包含了关联的车身
		$sql3 = sprintf(
			"SELECT oml_id,body_id 
			 FROM oe_model_na oml
			 JOIN oe_model_na_link l1 ON oml.oml_id = l1.ops_oml_id AND l1.ops_type = 'bodystyle'
			 LEFT JOIN na_entity_bodystyle neb ON l1.ops_dataid = neb.body_id
			 WHERE oml_base_id = %d",$baseId);
		$bodystyleOmlList = $this->db->query($sql3)->fetchAll();
		$bodystyleToOml = [];
		foreach($bodystyleOmlList as $item){
			if(!$bodystyleToOml[$item['body_id']]){
				$bodystyleToOml[$item['body_id']] = [];
			}
			if(!in_array($item['oml_id'],$bodystyleToOml[$item['body_id']])){
				$bodystyleToOml[$item['body_id']][] = $item['oml_id'];
			}
		}
		foreach($allBodystyleList as &$item){
			if(count($allSelectedOmlList) > 0){
				// 先添加全选车身的项
				$item['oml_id'] = $allSelectedOmlList;
			}
			if($bodystyleToOml[$item['body_id']]){
				$item['oml_id'] = array_merge($item['oml_id']??[],$bodystyleToOml[$item['body_id']]);
			}
			$content = sprintf('%s doors %s',$item['body_doors_name'], $item['body_type_name']);
			$item['bodystyle_show'] = $content;
		}
		// 过滤掉oml_id为空的项,返回最终的筛选列表
		$bodystyleOptions = array_values(array_filter($allBodystyleList, function($item){
			return isset($item['oml_id']);
		}));
		return $bodystyleOptions;
	}

	/**
	 * @method 获取所选北美车型对应的变速器选项列表
	 * @author hongzhang
	 * @copyright 2026-04-09
	 */
	public function getTransmissionOptions($params=[]){
		//获取北美车型的baseid
		$year = $params['year'];
		$makeId = $params['make_id'];
		$modelId = $params['model_id'];
		if(!$year || !$makeId || !$modelId){
			return [];
		}
		$baseId = $this->db->get('na_vehicle_base','base_id',['base_year_id'=>$year,'base_make_id'=>$makeId,'base_model_id'=>$modelId]);
		if(!$baseId){
			return [];
		}
		// 取该基础车型下所有的变速器选项列表
		$sql1 = sprintf(
			"SELECT DISTINCT tran_id,tran_type_name,tran_num_speeds,tran_control_type
			 FROM na_vehicle nv
			 LEFT JOIN na_vehicle_transmission nvt ON nv.veh_id = nvt.vbt_mod_id
			 LEFT JOIN na_entity_transmission net ON nvt.vbt_tran_id = net.tran_id
			 WHERE nv.veh_base_id = %d ORDER BY tran_type_name,tran_num_speeds,tran_control_type",$baseId);
		$allTransmissionList = $this->db->query($sql1)->fetchAll();
		// 没有关联变速器信息的项，则代表oml_id对应的这条项的变速器是全选的
		$sql2 = sprintf("SELECT oml_id FROM oe_model_na WHERE oml_base_id = %d AND oml_id NOT IN (SELECT DISTINCT ops_oml_id FROM oe_model_na_link WHERE ops_type = 'transmission')",$baseId);
		$allSelectedTransmissionList = $this->db->query($sql2)->fetchAll();
		$allSelectedTransmissionList = array_column($allSelectedTransmissionList,'oml_id');
		// 关联了变速器信息的项，则代表oml_id只包含了关联的变速器
		$sql3 = sprintf(
			"SELECT oml_id,tran_id 
			 FROM oe_model_na oml
			 JOIN oe_model_na_link l1 ON oml.oml_id = l1.ops_oml_id AND l1.ops_type = 'transmission'
			 LEFT JOIN na_entity_transmission net ON l1.ops_dataid = net.tran_id
			 WHERE oml_base_id = %d",$baseId);
		$transmissionOmlList = $this->db->query($sql3)->fetchAll();
		$transmissionToOml = [];
		foreach($transmissionOmlList as $item){
			if(!$transmissionToOml[$item['tran_id']]){
				$transmissionToOml[$item['tran_id']] = [];
			}
			if(!in_array($item['oml_id'],$transmissionToOml[$item['tran_id']])){
				$transmissionToOml[$item['tran_id']][] = $item['oml_id'];
			}
		}
		foreach($allTransmissionList as &$item){
			if(count($allSelectedTransmissionList) > 0){
				// 先添加全选变速器的项
				$item['oml_id'] = $allSelectedTransmissionList;
			}
			if($transmissionToOml[$item['tran_id']]){
				$item['oml_id'] = array_merge($item['oml_id']??[],$transmissionToOml[$item['tran_id']]);
			}
			$content = sprintf('%s %s %s',$item['tran_type_name'], $item['tran_num_speeds'], $item['tran_control_type']);
			$item['transmission_show'] = $content;
		}
		// 过滤掉oml_id为空的项,返回最终的筛选列表
		$transmissionOptions = array_values(array_filter($allTransmissionList, function($item){
			return isset($item['oml_id']);
		}));
		return $transmissionOptions;
	}

	/**
	 * @method 获取所选北美车型对应的驱动列表
	 * @author hongzhang
	 * @copyright 2026-04-09
	 */
	public function getDriveOptions($params=[]){
		//获取北美车型的baseid
		$year = $params['year'];
		$makeId = $params['make_id'];
		$modelId = $params['model_id'];
		if(!$year || !$makeId || !$modelId){
			return [];
		}
		$baseId = $this->db->get('na_vehicle_base','base_id',['base_year_id'=>$year,'base_make_id'=>$makeId,'base_model_id'=>$modelId]);
		if(!$baseId){
			return [];
		}
		// 获取该基础车型下所有的驱动选项列表
		$sql1 = sprintf(
			"SELECT DISTINCT vss_dataid AS drive_id,vss_name AS drive_show
			 FROM na_vehicle nv
			 LEFT JOIN na_vehicle_single nvs ON nv.veh_id = nvs.vss_mod_id AND nvs.vss_type = 'DriveType'
			 WHERE nv.veh_base_id = %d ORDER BY vss_name",$baseId);
		$allDriveList = $this->db->query($sql1)->fetchAll();
		// 没有关联驱动信息的项，则代表oml_id对应的这条项的驱动是全选的
		$sql2 = sprintf("SELECT oml_id FROM oe_model_na WHERE oml_base_id = %d AND oml_id NOT IN (SELECT DISTINCT ops_oml_id FROM oe_model_na_link WHERE ops_cat = 'DriveType')",$baseId);
		$allSelectedDriveList = $this->db->query($sql2)->fetchAll();
		$allSelectedDriveList = array_column($allSelectedDriveList,'oml_id');
		// 关联了驱动信息的项，则代表oml_id只包含了关联的驱动
		$sql3 = sprintf(
			"SELECT oml_id,vss_dataid AS drive_id
			 FROM oe_model_na oml
			 JOIN oe_model_na_link l1 ON oml.oml_id = l1.ops_oml_id AND l1.ops_cat = 'DriveType'
			 LEFT JOIN na_vehicle_single nvs ON l1.ops_dataid = nvs.vss_auto_id AND nvs.vss_type = 'DriveType'
			 WHERE oml_base_id = %d",$baseId);
		$driveOmlList = $this->db->query($sql3)->fetchAll();

		$driveToOml = [];
		foreach($driveOmlList as $item){
			if(!$driveToOml[$item['drive_id']]){
				$driveToOml[$item['drive_id']] = [];
			}
			if(!in_array($item['oml_id'],$driveToOml[$item['drive_id']])){
				$driveToOml[$item['drive_id']][] = $item['oml_id'];
			}
		}
		foreach($allDriveList as &$item){
			if(count($allSelectedDriveList) > 0){
				// 先添加全选驱动的项
				$item['oml_id'] = $allSelectedDriveList;
			}
			if($driveToOml[$item['drive_id']]){
				$item['oml_id'] = array_merge($item['oml_id']??[],$driveToOml[$item['drive_id']]);
			}
		}
		// 过滤掉oml_id为空的项,返回最终的筛选列表
		$driveOptions = array_values(array_filter($allDriveList, function($item){
			return isset($item['oml_id']);
		}));
		return $driveOptions;
	}
	
	/**
	 * @method 获取所选北美车型对应的详细属性的id列表
	 * @author hongzhang
	 * @copyright 2026-04-30
	 */
	public function generateEBay($type,$id){

		$mlList = [];
		$vehicleList = [];
		$KeysNameLink = [
			'oe'=>[
				'table' => 'oe_model_na',
				'link_table' => 'oe_model_na_link',
				'id'=> 'oml_oe_id',
				'base_name' => 'oml_base_id',
				'ml_name' => 'oml_id',
				'ml_dataid_name' => 'ops_dataid',
				'ml_ml_name' => 'ops_oml_id',
				'ml_type_name' => 'ops_type',
				'ml_cat_name' => 'ops_cat'
			],
			'pro'=>[
				'table' => 'pro_model_na',
				'link_table' => 'pro_model_na_link',
				'id'=> 'pml_p_id',
				'base_name' => 'pml_base_id',
				'ml_name' => 'pml_id',
				'ml_dataid_name' => 'pps_dataid',
				'ml_ml_name' => 'pps_pml_id',
				'ml_type_name' => 'pps_type',
				'ml_cat_name' => 'pps_cat'
			]
		];
		$Keys = [];
		if($type == 1){
			$Keys = $KeysNameLink['oe'];
		}else{
			$Keys = $KeysNameLink['pro'];
		}

		$mlList = $this->db->select($Keys['table'],[$Keys['ml_name']],[$Keys['id']=>$id]);
		$mlList = array_column($mlList,$Keys['ml_name']);
		// 循环处理每个传入的北美车型的信息
		foreach($mlList as $ml_id){
			$baseId = $this->db->get($Keys['table'],$Keys['base_name'],[$Keys['ml_name']=>$ml_id]);
			// 获取子车型id列表
			// 先看看link表中是否有记录
			$subModelIds = [];
			$linkSubModelList = $this->db->select($Keys['link_table'],[$Keys['ml_dataid_name']],[$Keys['ml_ml_name']=>$ml_id,$Keys['ml_type_name']=>'single',$Keys['ml_cat_name']=>'SubModel']);
			if(count($linkSubModelList) > 0){
				// 存在子车型记录，则说明这款车型的子车型id即为link表中的数据
				$subModelList = $this->db->select('na_vehicle',['veh_submodel_id'],['veh_id'=>array_column($linkSubModelList,$Keys['ml_dataid_name']),'GROUP'=>'veh_submodel_id']);
				$subModelIds = array_column($subModelList,'veh_submodel_id');
			}else{
				// 不存在link，即默认全选子车型
				$subModelList = $this->db->select('na_vehicle',['veh_submodel_id'],['veh_base_id'=>$baseId,'GROUP'=>'veh_submodel_id']);
				$subModelIds = array_column($subModelList,'veh_submodel_id');
			}
			/** 获取了子车型后，
			 * 相同子车型还有细分地区Region
			 * baseid+子车型id+地区id,才能定位veh_id
			 * 先取出所有相关的车型信息，再根据link表过滤*/
			$table = 'na_vehicle';
			$join = [
				// 车门车身信息
				'[><]na_vehicle_bodystyle(vbs)' => ['veh_id'=>'vbs_mod_id'],
				'[><]na_entity_bodystyle' => ['vbs.vbs_body_id'=>'body_id'],
				// 驱动信息
				'[><]na_vehicle_single(vss1)' => ['veh_id'=>'vss_mod_id'],
				// WheelBase信息
				'[><]na_vehicle_single(vss2)' => ['veh_id'=>'vss_mod_id'],
				// 发动机相关
				'[><]na_vehicle_engine(vee)' => ['veh_id'=>'vee_mod_id'],
				'[><]na_entity_engine' => ['vee.vee_eng_id' => 'eng_id']
			];
			$fields = ['veh_base_id','veh_submodel_id','veh_region_id','body_doors_id','body_type_id','vss1.vss_dataid(drive_type_id)','vss2.vss_dataid(wheelbase_id)','eng_base_id','eng_aspiration_id','eng_cylinder_head_id','eng_fuel_id'];
			$where = ['veh_base_id'=>$baseId,'veh_submodel_id'=>$subModelIds,'vss1.vss_type'=>'DriveType','vss2.vss_type'=>'WheelBase'];
			// 根据link表获取过滤信息
			$filterList = $this->db->select($Keys['link_table'],[$Keys['ml_type_name'],$Keys['ml_cat_name'],$Keys['ml_dataid_name']],[$Keys['ml_ml_name']=>$ml_id]);
			$bodyIds = [];
			$driveIds = [];
			$wheelbaseIds = [];
			$engIds = [];
			foreach($filterList as $item){
				// 车身信息
				if($item[$Keys['ml_type_name']] == 'bodystyle'){
					$bodyIds[] = $item[$Keys['ml_dataid_name']];
				}
				// 驱动信息
				if($item[$Keys['ml_cat_name']] == 'DriveType'){
					$driveIds[] = $item[$Keys['ml_dataid_name']];
				}
				// WheelBase信息
				if($item[$Keys['ml_cat_name']] == 'WheelBase'){
					$wheelbaseIds[] = $item[$Keys['ml_dataid_name']];
				}
				// 发动机相关
				if($item[$Keys['ml_type_name']] == 'engine'){
					$engIds[] = $item[$Keys['ml_dataid_name']];
				}
			}
			if(count($bodyIds) > 0){
				$where['body_id'] = $bodyIds;
			}
			if(count($driveIds) > 0){
				$where['vss1.vss_auto_id'] = $driveIds;
			}
			if(count($wheelbaseIds) > 0){
				$where['vss2.vss_auto_id'] = $wheelbaseIds;
			} 
			if(count($engIds) > 0){
				$where['eng_id'] = $engIds;
			}
			$rows = $this->db->select($table,$join,$fields,$where);
			if(!empty($rows)){
				$vehicleList = array_merge($vehicleList, $rows);
			}
			
		}
		return $vehicleList;
	}

	/**
	 * @method 转换取数据的字段名
	 * @author hongzhang
	 * @copyright 2026-05-12
	 */
	public function getSmallfileds($showFileds)
	{
		$smallReGetfiled = [
			'pcat_name'=>'pcat.'.LG_NAME.'(pcat_name)',
			'cat_name'=>'cat.'.LG_NAME.'(cat_name)',
		];

		$fileds = [];
		foreach($showFileds as $fv)
		{
			if(isset($smallReGetfiled[$fv])){
				$fileds[] = $smallReGetfiled[$fv];
			}else{
				$fileds[] = $fv;
			}
		}
		return $fileds;
	}
}