<?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_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;
	}
}