<?php
namespace model;
use \model;
use \model\collocation;
use \model\ypApi;
use \model\excel;
/**
 * Class procate
 * @package model
 */
class carmodelcn extends model
{
	/**
	  * @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 soul
	  */
	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'])? 'brand_initial,brand_name,make_name,mod1_name,mod2_name,mod2_start_year,mod3_name,mod3_start_year': $params['orderby'];

		$fields = '*';
		$table = 'm_cn_model';
		$where = $this->filter2medoo($filters,function(&$field,&$op,&$data){
        });
        $where = $this->listWhere($where,$page,$pagesize,$sidx,$sord,$orderby);

		if(!empty($params['letter'])) $where['brand_initial[=~]'] = $params['letter'];
		if(!empty($params['brand_id'])) $where['brand_id'] = $params['brand_id'];
		if(!empty($params['keys'])) $where['mod3_id'] = is_array($params['keys'])? $params['keys']: explode(',', $params['keys']);

		if(!empty($params['groupFields']))
		{	
			$where['GROUP'] = explode(',', $params['groupFields']);
		}
		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'] = 'http://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'])? 'http://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'])? 'http://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;
	}

	public function getMod3IdByProId($proId)
	{
		$arr = $this->db->select('pro_model_cn_link', 'pml_mod3_id', ['pml_p_id'=>(int)$proId]);
		return $arr?:[];
	}
}