<?php
namespace model\pro;
!defined('XLSBR') && define('XLSBR'," \r\n"); //Excel换行符
class export2 extends \model\pro\pro
{
    public $sdb = null;
    public $username = '';
    public $message = null;
    public $captions = [];
    public $cates = [];
    public $langs = [];
    public $factorys = null;
    public $mm_drive_typeList = [];
    public $mm_fuelList = [];
    public $mm_turboList = [];
    public $mm_cylindersList = [];
    public function lg($str){
        if($this->langs && isset($this->langs[$str])){
            $str = $this->langs[$str];
        }
        return $str;
    }
    public function getCollocation(){
        $this->mm_drive_typeList = $this->db->select('object',['o_id'=>['cn_name','en_name']],['o_parent_id'=>16]);
        $this->mm_fuelList = $this->db->select('object',['o_id'=>['cn_name','en_name']],['o_parent_id'=>17]);
        $this->mm_turboList = $this->db->select('object',['o_id'=>['cn_name','en_name']],['o_parent_id'=>15]);
        $this->mm_cylindersList = $this->db->select('object',['o_id'=>['cn_name','en_name']],['o_parent_id'=>18]);
    }
    
    public function createCaptions(){
		$this->captions = [
            //列头，列宽
		    'state'=>       [$this->lg('状态'),10],
		    'outnum'=>      [$this->lg('FREAY型号'),20],
		    'innum'=>       [$this->lg('内部编码'),20],
		    'bigcate'=>     [$this->lg('产品线'),20],
		    'cate'=>        [$this->lg('小类'),20],
		    'sales'=>       [$this->lg('销量'),20],
		    'ps_qty'=>       [$this->lg('库存'),20],
		    'stock'=>       [$this->lg('库存'),20],
		    'vio'=>         [$this->lg('保有量'),20],
		    //'pic'=>         [$this->lg('图片'),20],
		    'tags'=>        [$this->lg('版本'),20],
		    
		    'g'=>           [$this->lg('Y品牌'),20],
		    'ncv'=>         [$this->lg('NCV'),20],
		    'area'=>         [$this->lg('区域码'),30],
		    'enertek'=>     ['ENERTEK',20],
		    'barcode'=>     ['PRO'.$this->lg('条码'),20],
		    'barcode_g'=>   [$this->lg('Y品牌').$this->lg('条码'),20],
		    'barcode_enertek'=>   ['ENERTEK'.$this->lg('条码'),20],

		    'allfacnum'=>     [$this->lg('交叉索引'),30,true],
		    'mergeto'=>     [$this->lg('合并后型号'),30],
		    
		    'oe'=>            [$this->lg('OE'),20],

		    'pf_name'=>       [$this->lg('搜索号品牌'),20],
		    'pn_number'=>     [$this->lg('搜索号码'),20],
		    'ckresult2'=>     [$this->lg('复核结论'),20],
		    
		    'models'=>    [$this->lg('车型'),100,true],
		    'mm_id'=>     ['FREAY ID',20],
		    'year'=>      [$this->lg('年份'),20],
		    'pml_pos0'=>  [$this->lg('变速箱'),20],
		    'pml_pos1'=>  [$this->lg('前/后轴'),20],
		    'pml_pos6'=>  [$this->lg('前/后'),20],
		    'pml_pos2'=>  [$this->lg('左/右'),20],
		    'pml_pos3'=>  [$this->lg('外/中/内'),20],
		    'pml_pos4'=>  [$this->lg('上/中/下'),20],
		    'pml_pos5'=>  [$this->lg('Complex'),20],
		    'pml_node1'=> [$this->lg('其他车型备注'),20],
		    /*
		    'pack'=>            [$this->lg('包装方式'),20],
		    'miniqty_sales'=>   [$this->lg('最小起订量'),20],
		    'miniqty_packing'=> [$this->lg('最小装箱只数'),20],
		    'miniqty_trays'=>   [$this->lg('最小托盘只数'),20],
		    'nilongsize'=>      [$this->lg('尼龙袋尺寸'),20],
		    'zhihesize'=>       [$this->lg('纸盒尺寸'),20],
		    'waixiangsize'=>    [$this->lg('外箱尺寸'),20],
		    'zhixiangsize'=>    [$this->lg('托盘尺寸'),20],
		    'danzhong'=>        [$this->lg('单重'),20],
		    */
            'packingWay'             =>        [$this->lg('包装方式'),20],            
            'netWeight'              =>        [$this->lg('产品净重'),20],            
            'zhSpecs'                =>        [$this->lg('纸盒规格'),20],            
            'zxSpecs'                =>        [$this->lg('外箱规格'),20],            
            'nylon1Specs'            =>        [$this->lg('尼龙袋1规格'),20],        
            'nylon2Specs'            =>        [$this->lg('尼龙袋2规格'),20],        
            'tpSpecs'                =>        [$this->lg('托盘规格'),20],            
            'perCartonGrossWeight'   =>        [$this->lg('每盒毛重'),20],            
            'perBoxGrossWeight'      =>        [$this->lg('每箱毛重'),20],            
            'perTorrGrossWeight'     =>        [$this->lg('每托毛重'),20],            
            'nylon1ProductNum'       =>        [$this->lg('尼龙袋1产品数量'),20],  
            'nylon2ProductNum'       =>        [$this->lg('尼龙袋2产品数量'),20],  
            'perCartonNum'           =>        [$this->lg('每盒数量'),20],            
            'perBoxCartonNum'        =>        [$this->lg('每箱盒数'),20],            
            'perBoxNum'              =>        [$this->lg('每箱袋数'),20],            
            'perBagNum'              =>        [$this->lg('每袋袋数'),20],            
            'perFloorBoxNum'         =>        [$this->lg('每层箱数'),20],            
            'perTorrBoxNum'          =>        [$this->lg('每托箱数'),20],            
            'perTorrNum'             =>        [$this->lg('每托层数'),20],            
            'perTorrProductNum'      =>        [$this->lg('每托数量'),20],            
            'perTorrVolume'          =>        [$this->lg('每托体积'),20],		    
		];
		$this->gspBrandName = [
		    'oe'=>          1,
		    'gsp'=>         2,
		    'innum'=>       3,
		    'g'=>           4,
		    'ncv'=>         5,
		    'enertek'=>     6
		];
		$this->columnStyle = [];
		$this->paramType = [
			'int'=>'int',
			'double'=>'double',
			'string'=>'string',
			'smalltxt'=>'smalltxt',
			'bigtxt'=>'smalltxt',
			'list'=>'int',
			'radio'=>'int',
			'checkbox'=>'string'
		];
	}
    public function count($sql){
        $coutsql = 'select count(*) from ('.$sql.')  as a';
        $stCount = $this->db->query($coutsql);
		$count = $stCount->fetchColumn();
		$count = is_numeric($count) ? $count + 0 : 0;
		return $count;
    }
    
    public function createCates(){
        $sql = "SELECT cate.id,cate.cn_name,cate.en_name,parent.cn_name cn_parent_name, parent.en_name en_parent_name FROM pro_cate cate JOIN pro_cate parent ON cate.parent_id = parent.id";
        $st = $this->db->query($sql);
        while($row = $st->fetch()){
            $this->cates[$row['id']] = [
                'cn'=>[
                    'parent_name'=>$row['cn_parent_name'],
                    'name'=>$row['cn_name'],
                ],
                'en'=>[
                    'parent_name'=>$row['en_parent_name'],
                    'name'=>$row['en_name'],
                ],
            ];
        }
    }
    
    public function createFactory(){
        $rows = $this->db->select('pro_factory',['pf_id'=>['pf_name(name)',$this->lgtag.'_name(as)']]);
        foreach($rows as $k=>$v){
            if(trim($v['as'])){
                $this->factorys[$k] = $v['as'];
            }else{
                $this->factorys[$k] = $v['name'];
            }
        }
    }
    /*
    public function getPack(&$data){
        $sql = "SELECT 
        pack,
        miniqty_sales,
        miniqty_packing,
        miniqty_trays,
        nilongsize,
        zhihesize,
        waixiangsize,
        zhixiangsize,
        danzhong
        FROM pro_pack WHERE pp_id=".$data['pid']." LIMIT 1";
        $st = $this->db->query($sql);
        if($row = $st->fetch()){
            foreach($row as $k=>$v){
                $data[$k]=$v;
            }
        }
    }*/
    
    public function getPack(&$data,$area){
        $sql = "SELECT 
        cn,en,
        packingWay,          
        netWeight,
        zhSpecs,
        zxSpecs,
        nylon1Specs,         
        nylon2Specs,         
        tpSpecs,             
        perCartonGrossWeight,
        perBoxGrossWeight,   
        perTorrGrossWeight,  
        nylon1ProductNum,    
        nylon2ProductNum,    
        perCartonNum,        
        perBoxCartonNum,     
        perBoxNum,           
        perBagNum,           
        perFloorBoxNum,      
        perTorrBoxNum,       
        perTorrNum,          
        perTorrProductNum,   
        perTorrVolume       
        FROM pro_pack_area
        LEFT OUTER JOIN lang_kv on type='pack' AND (cn = packingWay or en = packingWay)
        WHERE pp_p_id=".$data['pid']." AND area='".$area."' LIMIT 1";
        $st = $this->db->query($sql);
        if($row = $st->fetch()){
            if(!empty($row[LG]))  $row['packingWay'] = $row[LG];
            unset($row['cn']);
            unset($row['en']);
            foreach($row as $k=>$v){
                $data[$k]=$v;
            }
        }
    }
    
    public function brand($bandid,$field,$backfield,$rows,&$data){
        $data[$field] = '';
        foreach($rows as $v){
            $value = trim($v[$backfield]);
            if($value!='' && $v['pn_pf_id']== $bandid){
                if(isset($data[$field]) && !empty($data[$field])){
                    $data[$field] .= XLSBR;
                }else{
                    $data[$field] = '';
                }
                $data[$field] .= trim($value);
                if($v[$this->lgtag.'_area']){
                    $data[$field] .= " (".$v[$this->lgtag.'_area'].")";
                }
            }
        }
    }
    
    public function getAreaNum(&$data,$options){
        $sql = "SELECT 
        p_code_number.*,pf_name,m_area.cn_name cn_area,m_area.en_name en_area 
        FROM p_code_number
        JOIN pro_factory ON pf_id = pcn_pf_id 
        JOIN m_area ON ma_id = pcn_ma_id 
        WHERE pcn_check2=1 AND pcn_ma_id>0 AND pcn_pc_id=".$data['pid']."
        ";
        $st = $this->db->query($sql);
        $rows = $st->fetchAll();
        if(!$rows){
            return;
        }
        foreach($rows as $v){
            if(isset($data['area'])){
                $data['area'] .= XLSBR;
            }else{
                $data['area'] = '';
            }
            $areaLine = $v['pf_name'].': '.$v['pcn_number'].' ('.$v[$this->lgtag.'_area'].')';
            $data['area'] .= $areaLine;
        }
    }

    public function getNumIdx(&$data,$options,$find_exchange_facnum=true){
            $sql = "SELECT 
            pro_numidx.*,m_area.cn_name cn_area,m_area.en_name en_area 
            FROM pro_numidx
            LEFT OUTER JOIN m_area ON ma_id = pn_ma_id 
            WHERE (pn_is_gsp=1 OR ckresult2=".NUMIDX_CKRESULT2_OK.") AND pn_type='none' AND pn_p_id=".$data['pid']."
            GROUP BY pn_p_id,pn_pf_id,pn_number,pn_ma_id
        ";
        $st = $this->db->query($sql);
        $rows = $st->fetchAll();
        if(!$rows){
            return;
        }
        
        if($options['allfacnum']){
            foreach($rows as $v){
                if(isset($data['allfacnum'])){
                    $data['allfacnum'] .= XLSBR;
                }else{
                    $data['allfacnum'] = '';
                }
                $data['allfacnum'] .= $v['pn_number'];
                if($v[$this->lgtag.'_area']){
                    $data['allfacnum'] .= " (".$v[$this->lgtag.'_area'].")";
                }
            }
        }
        if($options['barcode']){
            $this->brand($this->gspBrandName['gsp'],'barcode','pn_barcode',$rows,$data);
        }
        if($options['barcode_g']){
            $this->brand($this->gspBrandName['g'],'barcode_g','pn_barcode',$rows,$data);
        }
        if($options['barcode_enertek']){
            $this->brand($this->gspBrandName['enertek'],'barcode_enertek','pn_barcode',$rows,$data);
        }
        foreach($options['gspnum'] as $v){
            $this->brand($this->gspBrandName[$v],$v,'pn_number',$rows,$data);
        }
        if($options['oe']['qty']>'0'){
            $this->brand($this->gspBrandName['oe'],'oe','pn_number',$rows,$data);
            $arr = explode(XLSBR,trim($data['oe'],XLSBR));
            if($options['oe']['iscols']){
                unset($data['oe']);
                for($i=0;$i<$options['oe']['qty'];$i++){
                    $oeKey = 'oe'.($i+1);
                    $this->captions[$oeKey] =     [strtoupper($oeKey),30];
                    if(!empty($arr[$i])){
                        $data[$oeKey] = $arr[$i];
                    }else{
                        $data[$oeKey] = '';
                    }
                }
            }else{
                $data['oe'] = [];
                for($i=0;$i<$options['oe']['qty'];$i++){
                    $data['oe'][] = $arr[$i];
                }
                $data['oe'] = implode(XLSBR,$data['oe']);
            }
        }
        if($options['fac']){
            if(is_null($this->factorys)){
                $this->createFactory();
            } 
            foreach($options['fac'] as $v){
                $facName = $this->factorys[$v];
                $this->captions[$facName] = [strtoupper($facName),30];
                $this->brand($v,$facName,'pn_number',$rows,$data);
            }
            //dd($rows,$this->brand);
        }
        //引入映射表号码
	    //没有OE时，使用通用产品的OE
	    if($options['ep_exchange_facnum'] 
	        && !$this->hasOE($data) 
	        && !empty($data['pid']) 
	        && empty($options['allfacnum'])
	        && empty($data['is_find_exchange_facnum'])
	        && $find_exchange_facnum==true)
	   {
	        $data['is_find_exchange_facnum'] = true;//是否找过映射表
	        $init_pid = $data['pid'] * 1;
	        $sql = "SELECT pe_p_id1,pe_p_id2 FROM `pro_exchange` 
            WHERE ( (pe_p_id1 = {:pid} AND (pe_frometo='' OR pe_frometo='1>2')) OR (pe_p_id2= {:pid} AND (pe_frometo='' OR pe_frometo='2>1')) )
            AND EXISTS(SELECT 1 FROM pro_numidx WHERE (pn_p_id=pe_p_id1 OR pn_p_id=pe_p_id2) AND pn_pf_id=1 LIMIT 1)
            LIMIT 1";
	        $sql = qgprintf($sql,['pid'=>$init_pid]);
	        $st = $this->db->query($sql);
	        if($row = $st->fetch()){
	            $data['pid'] = ($row['pe_p_id1'] == $init_pid) ? $row['pe_p_id2'] : $row['pe_p_id1'];
    	        $this->getNumIdx($data,$options,false);
	        }
	        //如果还没有OE，使用父产品的OE
	        if(!$this->hasOE($data) ){
    	        $sql = "SELECT pf_father_pid FROM pro_fatherson WHERE pf_son_pid={:pid}
    	        AND EXISTS(SELECT 1 FROM pro_numidx WHERE pn_p_id=pf_father_pid AND pn_pf_id=1 LIMIT 1)
    	        LIMIT 1";
    	        $sql = qgprintf($sql,['pid'=>$init_pid]);
    	        $st = $this->db->query($sql);
    	        if($row = $st->fetch()){
    	            $data['pid'] = $row['pf_father_pid'];
        	        $this->getNumIdx($data,$options,false);
    	        }
	        }
	        //如果还没有OE，使用手工维护的通用产品的OE
	        if(!$this->hasOE($data) ){
    	        $sql = "SELECT pemn_p_id1,pemn_p_id2 FROM `pro_exchange_model_numidx` 
                WHERE ( (pemn_p_id1 = {:pid} AND (pemn_frometo='' OR pemn_frometo='1>2')) OR (pemn_p_id2= {:pid} AND (pemn_frometo='' OR pemn_frometo='2>1')) )
                AND EXISTS(SELECT 1 FROM pro_numidx WHERE (pn_p_id=pemn_p_id1 OR pn_p_id=pemn_p_id2) AND pn_pf_id=1 LIMIT 1)
                LIMIT 1";
    	        $sql = qgprintf($sql,['pid'=>$init_pid]);
    	        $st = $this->db->query($sql);
    	        if($row = $st->fetch()){
    	            $data['pid'] = ($row['pemn_p_id1'] == $init_pid) ? $row['pemn_p_id2'] : $row['pemn_p_id1'];
        	        $this->getNumIdx($data,$options,false);
    	        }
	        }
	    }
   }
    
    //是否有OE
    function hasOE($data){
        if(empty($data)) return false;
        if(!empty($data['oe'])) return true;
        if(isset($data['oe1']) && !empty($data['oe1']))  return true;
        return false;
    }
    
    
    public function getMergeto(&$data){
        $sql = "select mpro.`p_outnum` mergeto from p_code JOIN pro mpro ON pc_merge_p_id = mpro.`p_id` where pc_p_id = ".$data['pid']." limit 1";
        $st = $this->db->query($sql);
        $row = $st->fetch();
        $data['mergeto'] = $row['mergeto'] ?: '';
    }
    
    public function formartModelRow($row,$rules){
        if(empty($this->modelFields)){
            $this->modelFields = [];
            $arr = explode(',',$rules['modelDisplayFields']);
            $arr = array_unique($arr);
    	    foreach($arr as $k=>$v){
    	        if($v=='year'){
    	           $this->modelFields[] = 'year_from';
    	           $this->modelFields[] = 'year_to';
    	        }else{
        	        $this->modelFields[] = $v;
    	        }
    	    }
        }
        $item = [];
        $item['mm_id'] = $row['mm_id'];
        foreach($this->modelFields as $v){
            if(isset($row[$v])){
                $value = $row[$v];
                if($v=='mm_drive_type'){
                    $value = $this->mm_drive_typeList[$value][LG_NAME];
                }elseif($v=='mm_fuel'){
                    $value = $this->mm_fuelList[$value][LG_NAME];
                }elseif($v=='mm_turbo'){
                    $value = $this->mm_turboList[$value][LG_NAME];
                }elseif($v=='mm_cylinders'){
                    $value = $this->mm_cylindersList[$value][LG_NAME];
                }
                $item[$v] = $value;
                if($v=='year_from'){
                    if($row['year_from']=='0.00') $row['year_from'] = '';
                    if($row['year_to']=='0.00') $row['year_to'] = '';
                    $item['year'] =  $row['year_from'] .'-'. $row['year_to'];
                    unset($row['year_from']);
                    unset($row['year_to']);
                    unset($item['year_from']);
                    unset($item['year_to']);
                }
            }else{
                $item[$v] = '';
            }
            if(!isset($item['year'])) $item['year'] = '';
        }
        if($rules['modelPos']){
            $item['pml_pos0'] = $row['pml_pos0'];
            $item['pml_pos1'] = $row['pml_pos1'];
            $item['pml_pos6'] = $row['pml_pos6'];
            $item['pml_pos2'] = $row['pml_pos2'];
            $item['pml_pos3'] = $row['pml_pos3'];
            $item['pml_pos4'] = $row['pml_pos4'];
            $item['pml_pos5'] = $row['pml_pos5'];
            $item['pml_node1'] = $row['pml_node1'];
        }
        return $item;
    }
    
    public function getFeildsByModelDisplayFields($modelDisplayFields,$rules){
        $modelDisplayFields = explode(',',$modelDisplayFields);
        $fields = [];
        foreach ($modelDisplayFields as $v){
            switch ($v) {
                case 'mk_name':
                    array_push($fields,'mk.name');
                    break;
                case 'ms_name':
                    array_push($fields,'ms.name');
                    break;
                case 'mm_name':
                    array_push($fields,'mm.name');
                    break;
                case 'year':
                    array_push($fields,'mm_year_from');
                    array_push($fields,'mm_month_from');
                    array_push($fields,'mm_year_to');
                    array_push($fields,'mm_month_to');
                    break;
                default:
                    array_push($fields,$v);
                    break;
            }
        }
        if($rules['modelPos']){
            array_push($fields,'pml_pos0');
            array_push($fields,'pml_pos1');
            array_push($fields,'pml_pos6');
            array_push($fields,'pml_pos2');
            array_push($fields,'pml_pos3');
            array_push($fields,'pml_pos4');
            array_push($fields,'pml_pos5');
            array_push($fields,'pml_node1');
        }
        return $fields;
    }
    
    public function getModel(&$data,$options,$rules){
        if(!empty($rules['models'])){
            if(empty($rules['modelFields'])){
                $modelFilterFields = $this->getFeildsByModelDisplayFields($rules['modelDisplayFields'],$rules);
                $GROUP = implode(',',$modelFilterFields);
                $sql = "
                    SELECT 
                    *
                    , mk.name mk_name, ms.name ms_name, mm.name mm_name
                    , CONCAT(mm_year_from,'.',LPAD(mm_month_from,2,'0')) year_from
                    , IF(mm_year_to=0,0,CONCAT(mm_year_to,'.',LPAD(mm_month_to,2,'0'))) year_to
                    FROM pro_model_link
                    JOIN m_model mm ON mm_id = pml_mm_id
                    JOIN m_series ms ON ms_id = mm_ms_id
                    JOIN m_make mk ON mk_id = mm_mk_id
                    WHERE pml_p_id = {:pid} AND pml_mm_id IN({:models})
                    GROUP BY $GROUP
                    ORDER BY $GROUP
                ";
                $sql = qgprintf($sql,['pid'=>$data['pid'],'models'=>$rules['models']]);
            }else{
                //自由模式
                if(empty($this->modelSubWhere)){
                    $modelFields = explode(',',$rules['modelFields']);
    			    $modelFilterFields = array_merge([],$modelFields);
    			    foreach($modelFilterFields as $k=>$v){
    			        if($v=='mk_name') array_push($modelFilterFields,'mm_mk_id');
    			        if($v=='ms_name') array_push($modelFilterFields,'mm_ms_id');
    			        if($v=='mm_name') array_push($modelFilterFields,'name');
    			        if(in_array($v,['mm_id','mk_name','ms_name','mm_name'])){
    			            unset($modelFilterFields[$k]);
    			        }
    			    }
            	    $frows = $this->db->select('m_model','*',['mm_id'=>explode(',',$rules['models'])]);
            	    $modelWhere=[];
            	    foreach($frows as $fi => $frow){
            	        $modelSubWhere = [];
            	        foreach($frow as $fkey => $fval){
            	            if(in_array($fkey,$modelFilterFields)){
            	                if($fkey=='name'){
            	                    $modelSubWhere[] = "mm.name='".$fval."'";
            	                }else{
            	                    $modelSubWhere[] = $fkey."='".$fval."'";
            	                }
            	            }
            	        }
            	        $modelWhere[] = implode(' AND ', $modelSubWhere);
            	    }
            	    unset($frows);
            	    $this->modelWhere = "(".implode(') OR (', $modelWhere).")";
                }
                $GROUP = implode(',',$modelFilterFields);
                $sql = "
                    SELECT 
                    *
                    , mk.name mk_name, ms.name ms_name, mm.name mm_name
                    , CONCAT(mm_year_from,'.',LPAD(mm_month_from,2,'0')) year_from
                    , IF(mm_year_to=0,0,CONCAT(mm_year_to,'.',LPAD(mm_month_to,2,'0'))) year_to
                    FROM pro_model_link
                    JOIN m_model mm ON mm_id = pml_mm_id
                    JOIN m_series ms ON ms_id = mm_ms_id
                    JOIN m_make mk ON mk_id = mm_mk_id
                    WHERE pml_p_id = {:pid} AND {:where}
                    GROUP BY $GROUP
                    ORDER BY mk_name,ms_name,mm_name
                ";
                $sql = qgprintf($sql,['pid'=>$data['pid'],'where'=>$this->modelWhere]);
            }
        }else{
            $modelFilterFields = $this->getFeildsByModelDisplayFields($rules['modelDisplayFields'],$rules);
            $GROUP = implode(',',$modelFilterFields);
            $sql = "
                SELECT 
                *
                , mk.name mk_name, ms.name ms_name, mm.name mm_name
                , CONCAT(mm_year_from,'.',LPAD(mm_month_from,2,'0')) year_from
                , IF(mm_year_to=0,0,CONCAT(mm_year_to,'.',LPAD(mm_month_to,2,'0'))) year_to
                FROM pro_model_link
                JOIN m_model mm ON mm_id = pml_mm_id
                JOIN m_series ms ON ms_id = mm_ms_id
                JOIN m_make mk ON mk_id = mm_mk_id
                WHERE pml_p_id = {:pid}
                GROUP BY $GROUP
                ORDER BY $GROUP
            ";
            $sql = qgprintf($sql,['pid'=>$data['pid']]);
        }
	    if($options['model']['modelCel']=='rows'){
            $result = [];
            $st = $this->db->query($sql);
            while($row = $st->fetch()){
                $item = $this->formartModelRow($row,$rules);
                $item = array_merge($data,$item);
                array_push($result,$item);
            }
            if(!empty($result)){
                $data = $result;
            }else{
                $data = array_merge($data,$this->formartModelRow($data,$rules));
            }
	    }else{
	        if($options['model']['modelCel']=='one'){
	            $sql .= " LIMIT 1";
	        }
            $models = '';
            $st = $this->db->query($sql);
            while($row = $st->fetch()){
                if($models!='') $models.=XLSBR;
                $item = $this->formartModelRow($row,$rules);
                foreach($item as $k=>$v){
                    if($k!='mm_id'){
                        $models.= $v." ";
                    }
                }
            }
            $data['models'] = $models;
	    }
	    //没有车型时
	    if($options['ep_exchange_model'] && !$this->hasModel($data) && !empty($data['pid'])){
	        $init_pid = $data['pid'];
	        //使用通用产品的车型
	        $sql = "SELECT pe_p_id1,pe_p_id2 FROM `pro_exchange` 
            WHERE ( (pe_p_id1 = {:pid} AND (pe_frometo='' OR pe_frometo='1>2')) OR (pe_p_id2= {:pid} AND (pe_frometo='' OR pe_frometo='2>1')) )
            AND EXISTS(SELECT 1 FROM pro_model_link WHERE pml_p_id=pe_p_id1 OR pml_p_id=pe_p_id2 LIMIT 1)
            LIMIT 1";
	        $sql = qgprintf($sql,['pid'=>$init_pid]);
	        $st = $this->db->query($sql);
	        if($row = $st->fetch()){
	            $data['pid'] = ($row['pe_p_id1'] == $init_pid) ? $row['pe_p_id2'] : $row['pe_p_id1'];
    	        $this->getModel($data,$options,$rules);
	        }
	        //如果还没有车型，使用父产品的车型
	        if(!$this->hasModel($data)){
    	        $sql = "SELECT pf_father_pid FROM pro_fatherson WHERE pf_son_pid={:pid}
    	        AND EXISTS(SELECT 1 FROM pro_model_link WHERE pml_p_id=pf_father_pid LIMIT 1)
    	        LIMIT 1";
    	        $sql = qgprintf($sql,['pid'=>$init_pid]);
    	        $st = $this->db->query($sql);
    	        if($row = $st->fetch()){
    	            $data['pid'] = $row['pf_father_pid'];
        	        $this->getModel($data,$options,$rules);
    	        }
	        }
	        //如果还没有车型，使用手工维护的通用产品的车型
	        if(!$this->hasModel($data)){
    	        $sql = "SELECT pemn_p_id1,pemn_p_id2 FROM `pro_exchange_model_numidx` 
                WHERE ( (pemn_p_id1 = {:pid} AND (pemn_frometo='' OR pemn_frometo='1>2')) OR (pemn_p_id2= {:pid} AND (pemn_frometo='' OR pemn_frometo='2>1')) )
                AND EXISTS(SELECT 1 FROM pro_model_link WHERE pml_p_id=pemn_p_id1 OR pml_p_id=pemn_p_id2 LIMIT 1)
                LIMIT 1";
    	        $sql = qgprintf($sql,['pid'=>$init_pid]);
    	        $st = $this->db->query($sql);
    	        if($row = $st->fetch()){
    	            $data['pid'] = ($row['pemn_p_id1'] == $init_pid) ? $row['pemn_p_id2'] : $row['pemn_p_id1'];
        	        $this->getModel($data,$options,$rules);
    	        }
	        }
	    }
    }
    
    //是否有车型
    function hasModel($data){
        if(empty($data)) return false;
        if(!empty($data['models'])) return true;
        if(isset($data[0]) && !empty($data[0]['mm_id']))  return true;
        return false;
    }
    
    function createCateHeaders($cateIds){
        $rows = $this->db->select('gsp_part_param',
            ['ptpa_id'=>['ptpa_id','ptpa_cate_id',$this->lgtag.'_name(name)','ptpa_type','ptpa_decimal']],
            ['ptpa_cate_id'=>$cateIds,'ORDER'=>['ptpa_col_order','ptpa_id']]
        );
        $sets = [];
        foreach($cateIds as $cid){
            $sets[$cid] = [];
            foreach($rows as $row){
                if($row['ptpa_cate_id']==$cid){
                    $item = [
                        'name'=> htmlentities($row['name']),
                        'field'=>'ppv_val_'.$this->paramType[$row['ptpa_type']],
                        'decimal'=>$row['ptpa_decimal']
                    ];
                    $sets[$cid][$row['ptpa_id']] = $item;
                }
            }
        }
        $this->paramSet = $sets;
    }
	function formatParamHeaderRow($row){
        $data = [];
        $this->headerFields = [];
        $data['outnum'] = $this->captions['outnum'][0];
        $data['innum'] = $this->captions['innum'][0];
        $data['bigcate'] = $this->captions['bigcate'][0];
        $data['cate'] = $this->captions['cate'][0];
        $this->headerFields[]  = 'outnum';
        $this->headerFields[]  = 'innum';
        $this->headerFields[]  = 'bigcate';
        $this->headerFields[]  = 'cate';
        foreach($this->paramSet[$row['cid']] as $ptpaid=>$item){
            $field = 'params.'.$ptpaid;
            $data[$field] = $item['name'];
            $this->headerFields[]  = $field;
        }
        return $data;
	}
	function formatParamDataRow($row){
	    $cid = $row['cid'];
        $data = [
            'outnum'=> $row['outnum'],
            'innum'=> $row['innum'],
            'bigcate'=> $this->cates[$row['cid']][$this->lgtag]['parent_name'],
            'cate'=> $this->cates[$row['cid']][$this->lgtag]['name'],
        ]; 
	    $paramRows = $this->db->select('gsp_pro_param_val',
    	    ['ppv_ptpa_id'=>['ppv_val_int','ppv_val_double','ppv_val_string']],
    	    ['ppv_p_id'=>$row['pid']]
	    );
	    foreach($this->paramSet[$cid] as $ptpaid=>$item){
	        if(isset($paramRows[$ptpaid])){
	            $value = $paramRows[$ptpaid][$item['field']];
                if($item['field']=='ppv_val_double'){
                    $value = round($value,$item['decimal']);
                }
                $data['params.'.$ptpaid] = $value;
	        }
	    }
	    $result = [];
	    foreach($this->headerFields as $field){
	        $result[$field] = (isset($data[$field])) ? $data[$field] : '';
	    }
        return $result;
	}
    
    public function formatProHeaderRow($row){
        $data = [];
        $this->headerFields = [];
        foreach(array_keys($row) as $field){
            if($this->captions[$field]){
                $data[$field] = $this->captions[$field][0];
                $this->headerFields[]  = $field;
            }
        }
		foreach($this->captions as $k=>$v){
    		$this->columnStyle[$k] = ['width'=>$v[1]];
    		if(isset($v[2]) && $v[2]){
        		$this->columnStyle[$k]['wrap'] = true;
    		}
		}
        return $data;
    }
    
    public function formatProDataRow($row,$options,$rules){
        $data = [
            'pic'=> $row['p_pic'],
            'pid'=> $row['p_id'],
            'cid'=> $row['p_cate'],
            'state'=> $row['p_state_main'],
            'outnum'=> $row['p_outnum'],
            'innum'=> $row['p_innum'],
            'bigcate'=> $this->cates[$row['p_cate']][$this->lgtag]['parent_name'],
            'cate'=> $this->cates[$row['p_cate']][$this->lgtag]['name'],
            'sales'=> $row['p_sales_qty'],
            'vio'=> $row['p_vio'],
        ]; 
        $this->pro[$row['p_id']] = [
            'pid'=> $row['p_id'],
            'cid'=> $row['p_cate'],
            'outnum'=> $row['p_outnum'],
            'innum'=> $row['p_innum'],
        ];
        if($options['param']){
            if(!isset($this->cateIds[$row['p_cate']])) $this->cateIds[$row['p_cate']] = 0;
            $this->cateIds[$row['p_cate']]++;
        }
        //dd($options);
        if(!in_array('innum',$options['gspnum'])){
            unset($data['innum']);
        }
        if(!$options['sales']){
            unset($data['sales']);
        }
        if(!$options['vio']){
            unset($data['vio']);
        }
        if($options['pack'] && $options['pack_area']){
            $this->getPack($data,$options['pack_area']);
        }
        if($options['stock']){
            $data['stock'] = $this->getStock($data['outnum']);
        }
        if(
            in_array('g',$options['gspnum']) ||
            in_array('nvc',$options['gspnum']) ||
            in_array('enertek',$options['gspnum']) ||
            $options['oe']['qty']>'0' ||
            $options['allfacnum'] ||
            $options['barcode'] ||
            $options['barcode_g'] ||
            $options['barcode_enertek'] ||
            $options['fac']
        ){
            $this->getNumIdx($data,$options);
        }
        if(in_array('area',$options['gspnum'])){
            $this->getAreaNum($data,$options);
        }
        if($options['hasmergeto']){
            $this->getMergeto($data);
        }
        //车型会有多行情况放在最后
        if($options['model']['hasmodel']){
            $this->getModel($data,$options,$rules);
        }
        return $data;
    }
    
    public function export($msgId,$count,$filename,$username,$lgtag,$type,$options,$rules,$sql,$pagesize)
    {
        if($type=="xlsxzip"){
            return $this->exportZip($msgId,$count,$filename,$username,$lgtag,$type,$options,$rules,$sql,$pagesize);
        }
	    //dd($data,$options,$rules);
        if($count>$pagesize) $count = $pagesize;
        $rules['modelPos'] = $options['model']['haspos'];
		$this->lgtag = ($lgtag=='en_name') ? 'en' : 'cn';
		if($this->lgtag!='cn'){
		    $langfile = \mvc::$cfg['dir']['locale'].'/project/language/'.$this->lgtag.'/field.php';
		    $this->langs = include($langfile);
		}
		$this->message = makeOne(\model\message::class);
        $this->username = $username;
        $this->getCollocation();
        $this->createCaptions();
        $this->createCates();

		//车型标题头
        foreach($options['model']['mcols'] as $k=>$v){
            $this->captions[$v[0]]=[$v[1],20];
        }
        
		$sql .= " ORDER BY p_cate,p_outnum";
        $dbcfg = array_merge([],\mvc::$cfg['db']['main']);
        $dbcfg['sdb'] = true;
        $this->sdb = new \lib\db($dbcfg);
        $this->sdb->useBuff(false);

        $subDir = date('Ym');
        $cacheDir = \mvc::$cfg['dir']['cache'].'/'. $subDir;
        mkdirs($cacheDir);
        $tempfile = $cacheDir.'/'.$username.$filename.'.xlsx';
        
        //$fp = fopen('php://output','w');
        //$fp = fopen($tempfile,'w');
        //$excel = new \lib\exportXlsx($fp,$cacheDir,'excel_'.$this->username);
        //$excel = new \lib\exportXlsx($fp);
        //$excel = new \lib\exportXml($fp);
        $this->pro = [];
        $this->cateIds = [];
        $excel = new \lib\exportXlsxDll($cacheDir,$tempfile);
        //$excel->header(basename($tempfile));
        $excel->bookBegin();
        $sheetName = $this->lg('基本资料');
            $st = $this->sdb->query($sql);
            $i=1;
            $modelReg=1;
            while($row = $st->fetch()){
                $data = $this->formatProDataRow($row,$options,$rules);
                if(!isset($data[0])) $data = [$data];
                if($i==1){
                    $headerRow = $this->formatProHeaderRow($data[0]);
                    $excel->sheetBegin($sheetName,$headerRow,$this->columnStyle);
                }
                foreach($data as $item){
                    foreach($item as $k=>$v){
                        if(!in_array($k,$this->headerFields)){
                            unset($item[$k]);
                        }
                    }
                    unset($item['pid']);
                    unset($item['cid']);
                    unset($item['pic']);
                    $excel->append($item);
                    
                    if($options['model']['hasmodel']){
                        if($modelReg%1000==0){
                            $body = $this->lg('车型').' '.$modelReg.' '.$this->lg('条').', '.$sheetName;
                            $this->sendMessage($msgId, $body,'','', $i,$count);
                        }
                        $modelReg++;
                    }
                }
                if($modelReg==1 && $i%1000==0){
                    $this->sendMessage($msgId, $sheetName,'','', $i,$count);
                }
                $i++;
                if($i>$pagesize) break;
            }
            $this->sendMessage($msgId, $sheetName,'','', $count,$count);
        $excel->sheetEnd();
        
        //参数
        if($options['param']){
            $cateIds = array_keys($this->cateIds);
            $this->createCateHeaders($cateIds);
            $sheetReg=1;
            $sheetCount=count($cateIds);
            foreach($cateIds as $cid){
                $sheetName = $this->cates[$cid][$this->lgtag]['name'];
                $parentName = $this->cates[$cid][$this->lgtag]['parent_name'];
                    $i=1;
                    $count = $this->cateIds[$cid];
                    foreach($this->pro as $row){
                        if($row['cid']==$cid){
                            if($i==1){
                                $headerRow = $this->formatParamHeaderRow($row);
                                $excel->sheetBegin($sheetName,$headerRow);
                            }
                            $data = $this->formatParamDataRow($row,$options,$rules);
                            $excel->append($data);
                            if($i%1000==0){
                                $body = $this->lg('小类').' '.$sheetReg.'/'.$sheetCount.', '.$parentName.'.'.$sheetName;
                                $this->sendMessage($msgId, $body,'','', $i,$count);
                            }
                            $i++;
                            if($i>$pagesize) break;
                        }
                    }
                    $body = $this->lg('小类').' '.$sheetReg.'/'.$sheetCount.', '.$parentName.'.'.$sheetName;
                    $this->sendMessage($msgId, $body,'','', $count,$count);
                $excel->sheetEnd();
                if($sheetReg>=252) break;
                $sheetReg++;
            }
            $body = $this->lg('小类').' '.$sheetCount.'/'.$sheetCount.', '.$parentName.'.'.$sheetName;
            $this->sendMessage($msgId, $body,'','', $count,$count);
        }
        //dd(1);
        $excel->bookEnd();
        //fclose($fp);
        $this->sendMessage($msgId, basename($tempfile), HOST.'/cache/'.$subDir.'/'.basename($tempfile),$tempfile, 0,-1);
        $this->sdb->close();
        $this->db->close();
        return true;
    }
    
    
    public function exportZip($msgId,$count,$filename,$username,$lgtag,$type,$options,$rules,$sql,$pagesize)
    {
        if($count>$pagesize) $count = $pagesize;
        $rules['modelPos'] = $options['model']['haspos'];
		$this->lgtag = ($lgtag=='en_name') ? 'en' : 'cn';
		if($this->lgtag!='cn'){
		    $langfile = \mvc::$cfg['dir']['locale'].'/project/language/'.$this->lgtag.'/field.php';
		    $this->langs = include($langfile);
		}
		$this->message = makeOne(\model\message::class);
        $this->username = $username;
        $this->createCaptions();
        $this->createCates();

		//车型标题头
        foreach($options['model']['mcols'] as $k=>$v){
            $this->captions[$v[0]]=[$v[1],20];
        }
        
		$sql .= " ORDER BY p_cate,p_outnum";
        $dbcfg = array_merge([],\mvc::$cfg['db']['main']);
        $dbcfg['sdb'] = true;
        $this->sdb = new \lib\db($dbcfg);
        $this->sdb->useBuff(false);

        $subDir = date('Ym');
        $cacheDir = \mvc::$cfg['dir']['cache'].'/'. $subDir;
        mkdirs($cacheDir);
        $tempfile = $cacheDir.'/'.$username.$filename.'.xlsx';
        $this->pro = [];
        $this->cateIds = [];
        $excel = new \lib\exportXlsxDll($cacheDir,$tempfile);
        $excel->bookBegin();
        $sheetName = $this->lg('基本资料');
        $fileList[] = $tempfile;
        $fileListName[] = $sheetName;
            $st = $this->sdb->query($sql);
            $i=1;
            $modelReg=1;
            while($row = $st->fetch()){
                $data = $this->formatProDataRow($row,$options,$rules);
                if(!isset($data[0])) $data = [$data];
                if($i==1){
                    $headerRow = $this->formatProHeaderRow($data[0]);
                    $excel->sheetBegin($sheetName,$headerRow,$this->columnStyle);
                }
                foreach($data as $item){
                    foreach($item as $k=>$v){
                        if(!in_array($k,$this->headerFields)){
                            unset($item[$k]);
                        }
                    }
                    unset($item['pid']);
                    unset($item['cid']);
                    $excel->append($item);
                    
                    if($options['model']['hasmodel']){
                        if($modelReg%1000==0){
                            $body = $this->lg('车型').' '.$modelReg.' '.$this->lg('条').', '.$sheetName;
                            $this->sendMessage($msgId, $body,'','', $i,$count);
                        }
                        $modelReg++;
                    }
                }
                if($modelReg==1 && $i%1000==0){
                    $this->sendMessage($msgId, $sheetName,'','', $i,$count);
                }
                $i++;
                if($i>$pagesize) break;
            }
            $this->sendMessage($msgId, $sheetName,'','', $count,$count);
        $excel->sheetEnd();
        $excel->bookEnd();
        
        //参数
        if($options['param']){
            $cateIds = array_keys($this->cateIds);
            $this->createCateHeaders($cateIds);
            $sheetReg=1;
            $sheetCount=count($cateIds);
            foreach($cateIds as $cid){
                $tempfile = $cacheDir.'/'.$username.$filename.'_'.$cid.'.xlsx';
                $excel = new \lib\exportXlsxDll($cacheDir,$tempfile);
                $excel->bookBegin();
                $sheetName = $this->cates[$cid][$this->lgtag]['name'];
                $parentName = $this->cates[$cid][$this->lgtag]['parent_name'];
                $fileList[] = $tempfile;
                $fileListName[] = $excel->formatFilename('cate_'.$parentName.'_'.$sheetName);
                    $i=1;
                    $count = $this->cateIds[$cid];
                    foreach($this->pro as $row){
                        if($row['cid']==$cid){
                            if($i==1){
                                $headerRow = $this->formatParamHeaderRow($row);
                                $excel->sheetBegin($sheetName,$headerRow);
                            }
                            $data = $this->formatParamDataRow($row,$options,$rules);
                            $excel->append($data);
                            if($i%1000==0){
                                $body = $this->lg('小类').' '.$sheetReg.'/'.$sheetCount.', '.$parentName.'.'.$sheetName;
                                $this->sendMessage($msgId, $body,'','', $i,$count);
                            }
                            $i++;
                            if($i>$pagesize) break;
                        }
                    }
                    $body = $this->lg('小类').' '.$sheetReg.'/'.$sheetCount.', '.$parentName.'.'.$sheetName;
                    $this->sendMessage($msgId, $body,'','', $count,$count);
                $excel->sheetEnd();
                $excel->bookEnd();
                $sheetReg++;
            }
            $body = $this->lg('小类').' '.$sheetCount.'/'.$sheetCount.', '.$parentName.'.'.$sheetName;
            $this->sendMessage($msgId, $body,'','', $count,$count);
        }
        
        $this->sdb->close();
        $this->db->close();
        
        $zip = new \ZipArchive();
        $zipFile = $cacheDir.'/'.$username.$filename.'.zip';
        if ($zip->open($zipFile,\ZipArchive::CREATE|\ZipArchive::OVERWRITE) === true) {
            foreach($fileList as $i=>$file){
                $zip->addFile($file,$fileListName[$i].'.xlsx');
            }
            $zip->close();
            foreach($fileList as $i=>$file){
                @unlink($file);
            }
            $this->sendMessage($msgId, basename($zipFile), HOST.'/cache/'.$subDir.'/'.basename($zipFile),$zipFile, 0,-1);
        }
        return true;
    }
    
    function sendMessage($msgId,$body,$url,$file,$pos,$count){
        if(!$msgId && $count>0){
            return false;
        }
        if($file==''){
            if($count==0) return false;
            $body = $body." ". floor($pos/$count*100) . "%, ".$pos . " / " . $count;
        }else{
            $filesize = filesize($file);
            if($filesize<1024*1024){
                $size = round($filesize/1024) . ' K';
            }elseif($filesize<1024*1024*1024){
                $size = round($filesize/1024/1024,2) . 'M';
            }elseif($filesize<1024*1024*1024*1024){
                $size = round($filesize/1024/1024/1024,2) . 'G';
            }
            $body = $body."  ( ". $size ." )";
        }
        if($msgId){
            $this->message->update($msgId,['amsg_body'=>$body,'amsg_url'=>$url,'amsg_file'=>$file]);
        }
    	$data = [
    		'cmd'=>'SEND',
    		'value'=>[
    			'to'=> [$this->username],
    			'data'=> [
    				"type"=> 'xlsx',
    				"data"=>[
        				"msgId"=> $msgId,
        				"body"=> $body,
        				"url"=> $url,
        				"pos"=> $pos,
        				"count"=> $count
    				]
    			]
    		]
    	];
    	if(is_null(\mvc::$websocketClient)){
    		\mvc::$websocketClient = new \WebSocket\Client(\mvc::$cfg['websocketLocalServer']);
    	}
    	\mvc::$websocketClient->text(json_encode($data));
    }
    
}