<?php
namespace model;
/**
 * Class user
 * @package model
 */
class sys extends \model
{
	public function __construct()
    {
		parent::__construct();
	}
	
	public function test(){
	    
	}
	
	public function pro_model_cpdate($ma_id){
	    //$ma_id = 24;
		ini_set('memory_limit', \mvc::$cfg['export_memory_limit']);
		set_time_limit(\mvc::$cfg['export_time_limit']);
		$startTime = time();
		//$rows = $this->db->select('m_area',['ma_id'],['ma_ziku'=>1]);
		$where =[
	        'OR'=>['ma_ziku'=>1,'ma_mlzy'=>1]
	    ];
	    if(!empty($ma_id)){
	        $where['ma_id'] = $ma_id;
	    }
	    $rows = $this->db->select('m_area',
		    [
		       '[>]crm_country' => ['ma_code'=>'cc_code'], 
		    ],
		    ['ma_id','cc_id','ma_ziku','ma_mlzy'],
		    $where
		 );
		foreach($rows as $row){
    		//$ma_id =24;  //俄罗斯
    		//gsp 823021
    		//innum BM-8-962A  modelRand 325i
    		if($row['cc_id'] || $row['ma_mlzy']){
        		echo "1. ".$row['ma_id']." Begin\r\n";
    		    $this->pro_model_cpdate_by_ma_id($row['ma_id'],$row['cc_id'],$row['ma_ziku'],$row['ma_mlzy']);
    		}
		}
		echo "\r\nTime: ";
		echo time()-$startTime;
		echo "\r\n";
	}
	
	public function pro_model_cpdate_by_ma_id($ma_id,$cc_id,$ma_ziku,$ma_mlzy)
    {
		$maRow = $this->db->get('m_area','*',['ma_id'=>$ma_id]);
		if(empty($maRow) || empty($maRow['ma_fileds'])){
		    return false;
		}
		$maFileds = json_decode($maRow['ma_fileds'],true);
		//dd($maFileds);
		$selectFiledArr = [];
		$groupFiledArr = [];
		$orderFiledArr = [];
		$defineFileds = [
		    'mk_name'=>['mm_mk_id'],
		    'ms_name'=>['mm_ms_id'],
		    'mm_name'=>['mm_id'],
		    'mm_serie'=>['mm_serie'],
		    //'year_from'=>['mm_year_from','mm_month_from'],
		    //'year_to'=>['mm_year_to','mm_month_to'],
		    'mm_fuel'=>['mm_fuel'],
		    'mm_disp'=>['mm_disp'],
		    'mm_chassis'=>['mm_chassis'],
		    'mm_engine'=>['mm_engine'],
		    'mm_vehicletype'=>['mm_vehicletype'],
		    'mm_turbo'=>['mm_turbo'],
		    'mm_kw'=>['mm_kw'],
		    'mm_hp'=>['mm_hp'],
		    'mm_cylinders'=>['mm_cylinders'],
		    'mm_values'=>['mm_values'],
		    'mm_drive_type'=>['mm_drive_type'],
		    'mm_body'=>['mm_body'],
		    'mm_doors'=>['mm_doors'],
		    'mm_platform'=>['mm_platform'],
		    'mm_others1'=>['mm_others1'],
		    'mm_others2'=>['mm_others2'],
		    'p_outnum'=>['pml_p_id'],
		    'pml_pos0'=>['pml_pos0'],
		    'pml_pos1'=>['pml_pos1'],
		    'pml_pos2'=>['pml_pos2'],
		    'pml_pos3'=>['pml_pos3'],
		    'pml_pos4'=>['pml_pos4'],
		    'pml_pos5'=>['pml_pos5'],
		    'pml_pos6'=>['pml_pos6'],
		    'pma_catalog_notes'=>['pma_catalog_notes'],
		];
		/*
        if(isset($maFileds['p_outnum'])){
            foreach([
    		    'mk_name'=>['mm_mk_id'],
    		    'ms_name'=>['mm_ms_id'],
    		    'mm_name'=>['mm_id'],
    		    'mm_serie'=>['mm_serie'],
    		    'year_from'=>['mm_year_from','mm_month_from'],
    		    'year_to'=>['mm_year_to','mm_month_to'],
    		    'mm_fuel'=>['mm_fuel'],
    		    'mm_disp'=>['mm_disp'],
    		    'mm_chassis'=>['mm_chassis'],
    		    'mm_engine'=>['mm_engine'],
    		    'mm_vehicletype'=>['mm_vehicletype'],
    		    'mm_turbo'=>['mm_turbo'],
    		    'mm_kw'=>['mm_kw'],
    		    'mm_hp'=>['mm_hp'],
    		    'mm_cylinders'=>['mm_cylinders'],
    		    'mm_values'=>['mm_values'],
    		    'mm_drive_type'=>['mm_drive_type'],
    		    'mm_body'=>['mm_body'],
    		    'mm_doors'=>['mm_doors'],
    		    'mm_platform'=>['mm_platform'],
    		    'mm_others1'=>['mm_others1'],
    		    'mm_others2'=>['mm_others2'],
                ] as $k=>$v){
                unset($defineFileds[$k]);
            }
        }*/  
		
		foreach($defineFileds as $k=>$fields){
    		if(isset($maFileds[$k])){
    		    foreach($fields as $v)  $groupFiledArr[] = $v;
    		    /*
    		    if(in_array($k,['year_from','year_to'])===false){
    		        foreach($fields as $v)  $orderFiledArr[] = $v;
    		    }*/
    		}
		}
		
		if(in_array('mm_mk_id',$groupFiledArr)){
		    $mkRows = $this->db->select('m_make',['mk_id'=>['name(name)']]);
		}
		
		if(in_array('mm_ms_id',$groupFiledArr)){
		    $msRows = $this->db->select('m_series',['ms_id'=>['name(name)']]);
		}
		/*
		if(in_array('mm_id',$groupFiledArr)){
		    $mmRows = $this->db->select('m_model',['mm_id'=>['name(name)']]);
		}*/
		$selectFiledArr = array_merge($groupFiledArr,[
		    'pml_id',
		    'date_from',
		    'date_to',
		]);
		$sql = "SELECT 
                CAST(CONCAT(mm_year_from,LPAD(mm_month_from,2,'0')) AS SIGNED) date_from, 
                CAST(CONCAT(IF(mm_year_to,mm_year_to,YEAR(NOW())),LPAD(IF(mm_month_to,mm_month_to,MONTH(NOW())),2,'0')) AS SIGNED) date_to,
                model.*,link.*{:FIELD_pma_catalog_notes} FROM pro_model_link link
                JOIN m_model model ON mm_id=pml_mm_id
                {:JOIN_pro_model_area_link}
                {:JOIN_crm_country_link}
                {:group}
                {:order}";
                
				//where mm_id in (122821,122822,122823,122824,122825,122826)
                //WHERE mm_ms_id= 1522
				//where pml_p_id = 36443 and mm_disp='1.5'
        $JOIN_pro_model_area_link = ''; 
        $JOIN_crm_country_link = ''; 
        $FIELD_pma_catalog_notes = ''; 
        if($ma_mlzy){ //专用库
            $FIELD_pma_catalog_notes = ',note.pma_catalog_notes'; 
            $JOIN_pro_model_area_link = 'JOIN pro_model_area_link note ON pml_id=pma_pml_id AND pma_ma_id ='.$ma_id;
        }else{
            if(in_array('pma_catalog_notes',$groupFiledArr)){
                $FIELD_pma_catalog_notes = ',note.pma_catalog_notes'; 
                $JOIN_pro_model_area_link = 'LEFT OUTER JOIN pro_model_area_link note ON pml_id=pma_pml_id AND pma_ma_id ='.$ma_id;
            }
        }
        if(empty($ma_mlzy) && in_array('pml_p_id',$groupFiledArr)){ //子库
            $JOIN_crm_country_link = "JOIN crm_country_link cclnk ON pml_mm_id=cl_value_id AND cl_cc_id=".$cc_id." AND cl_type = 'model'";
        }
        
        $sql = qgprintf($sql,[
            'FIELD_pma_catalog_notes'=> $FIELD_pma_catalog_notes,
            'JOIN_pro_model_area_link'=> $JOIN_pro_model_area_link,
            'JOIN_crm_country_link'=> $JOIN_crm_country_link,
            //'group'=> 'GROUP BY '.implode(',',$groupFiledArr),
            'group'=>'', 
            //'order'=> 'ORDER BY '.implode(',',$orderFiledArr).',date_from,date_to',
            'order'=>'', //不排序，后面使用数组排序
        ]);	
        //dd($groupFiledArr,$sql);
        $data = [];
        $this->db->useBuff(false);
        $res = $this->db->query($sql);
        echo "2. "." Fetching\r\n";
        while($row = $res->fetch(\PDO::FETCH_ASSOC)){
            $key = '';
            $item = [];
            foreach ($selectFiledArr as $field){
                if(isset($row[$field])){
                    switch ($field) {
                        case 'mm_mk_id':
                            $v = $mkRows[$row[$field]]['name'];
                            break;
                        case 'mm_ms_id':
                            $v = $msRows[$row[$field]]['name'];
                            break;
                        case 'mm_id':
                            $v = $row['name'];
                            break;
                        default:
                        $v = $row[$field];
                    }
                    $item[$field] = $v;
                    if(in_array($field,$groupFiledArr)===true && in_array($field,['mm_year_from','mm_month_from','mm_year_to','mm_month_to',])===false){
                        $key .= $field.':'.$v.'<@>';
                    }
                }
            }
            $key = md4($key);
            //$item['guid'] = $key;
            $data[$key][] = $item;
        }
        $this->db->useBuff(true);
        echo "3. "."Loaded into memory\r\n";
        //dd($data);
        $rows = [];
        foreach($data as $key=>$items){
            if(count($items)>1){
                $tmpArr = $this->modelMerge($items);
                if($tmpArr) $rows[] = $tmpArr;
                //foreach($tmpArr as $v){
                //    $rows[] = $v;
                //}
            }else{
                //$rows[] = $items[0]['pml_id'];
            }
            unset($data[$key]);
        }
        //dd($rows);
        
        echo "4. "."Preparing to write to the database\r\n";
        $this->db->begin();
        $this->db->query("DELETE FROM pro_model_cpdate  WHERE pmcpd_ma_id=".$ma_id);
        $regI = 0;
        $insertData = [];
        foreach($rows as $i=> $items){
            foreach($items as $j=>$item){
                $tag = $i * 100 + $j;
                foreach($item as $pml_id){
                    $insertData[] = [
                        'pmcpd_pml_id'=>$pml_id,
                        'pmcpd_ma_id'=>$ma_id,
                        'pmcpd_tag'=>$tag,
                    ];
                    $regI++;
                    if($regI % 1000 == 0){
                        $this->db->insert('pro_model_cpdate',$insertData);
                        $insertData = [];
                    }
                }
            }
        }
        //dd($insertData);
        if(!empty($insertData)){
            $this->db->insert('pro_model_cpdate',$insertData);
        }
        $this->db->end();
		//dd($insertData);
        unset($insertData);
        unset($rows);
        unset($data);
        echo "5. ".$ma_id. " End.\r\n\r\n";
	}
	public function modelMerge($items){
	    //年月排序
	    uasort($items,function($a,$b){
            if ($a['date_from'] == $b['date_from']) {
                if ($a['date_to'] == $b['date_to']) {
                    return 0;
                }
                return ($a['date_to'] < $b['date_to']) ? -1 : 1;	        
            }
            return ($a['date_from'] < $b['date_from']) ? -1 : 1;	        
	    });
	    //dd($items);
	    $yearArr = [];
	    foreach($items as $item){
	        $year_from = 0;
	        $year_to = 0;
	        if($item['date_from']){
	            $year_from = substr($item['date_from'],0,4) * 1;
	        }
	        if($item['date_from']){
	            $year_to = substr($item['date_to'],0,4) * 1;
	        }
	        if($year_from && $year_to){
	            for($year=$year_from;$year<=$year_to;$year++){
	                $yearArr[$year][] = $item['pml_id'];
	            }
	        }
	    }
	    ksort($yearArr);
	    $groupArr = [];
	    $regI=0;
	    $preYear = -1;
	    foreach($yearArr as $year=>$items){
	        if($preYear==-1){
	            $preYear = $year;
				$groupArr[$regI][] = $year;
	            continue;
	        }
	        if($year - 1 != $preYear){
    	        $regI++;
				$groupArr[$regI][] = $year;
	        }else{
				$groupArr[$regI][] = $year;
			}
	        $preYear = $year;
	    }
		//dd($groupArr);
	    $data = [];
	    foreach($groupArr as $k=>$values){
	        if(!isset($data[$k])) $data[$k] = [];
	        foreach($values as $v){
	            $data[$k] = array_merge($data[$k],$yearArr[$v]);
	            $data[$k] = array_unique($data[$k]);
	        }
	    }
		//dd($data);
	    return $data;
	}
	public function cmpModelDate($pre_date_to,$date_from){
		$pre_date_to *= 1;
		$date_from *= 1;
		return ($pre_date_to + 1 == $date_from) 
				|| ($pre_date_to == $date_from-89)
				|| (substr($date_from,4,2)=='00' && substr($pre_date_to,4,2)==date('m') 
					&& substr($pre_date_to,0,4)*1+1 == substr($date_from,0,4)*1 )
		;
	}
	
	public function modelMerge2($items){
	    //年月排序
	    uasort($items,function($a,$b){
            if ($a['date_from'] == $b['date_from']) {
                if ($a['date_to'] == $b['date_to']) {
                    return 0;
                }
                return ($a['date_to'] < $b['date_to']) ? -1 : 1;	        
            }
            return ($a['date_from'] < $b['date_from']) ? -1 : 1;	        
	    });
	    //
	    $pre_date_from = null;
	    $pre_date_to = null;
	    $g=[];
	    $i=0;
	    $allMin = 0; $allMax = 0;
	    foreach($items as $k=>$item){
	        $date_from = $item['date_from']*1;
	        $date_to = $item['date_to']*1;
	        if($allMin==0 || $date_from < $allMin) $allMin = $date_from;
	        if($date_to>$allMax) $allMax = $date_to;
	        if(is_null($pre_date_from)){
	            //第一条
	            $pre_date_from = $date_from;
	            $pre_date_to = $date_to;
	            //$g[$i][] = $item['pml_id'];
	            continue;
	        }
	        if($this->cmpModelDate($pre_date_to,$date_from)){
	            $g[$i][] = $item['pml_id'];
	            $g[$i][] = $items[$k-1]['pml_id'];
	        }else{
	            $i++;
				if(!isset($items[$k+1]) || !$this->cmpModelDate($date_to,$items[$k+1]['date_from'])){
					$g[$i][] = $item['pml_id'];
				}
	        }
            $pre_date_from = $date_from;
            $pre_date_to = $date_to;
	    }
	    //29ba323ad9492e95
	    //dd($g,$items);
	    foreach($g as $k=>$v){
			$g[$k] = array_unique($g[$k]);
	    }
	    return  $g;
	}

	public function cmpModelDate2($pre_date_to,$date_from){
		$pre_date_to *= 1;
		$date_from *= 1;
		return ($pre_date_to + 1 == $date_from) 
				|| ($pre_date_to == $date_from-89)
				|| (substr($date_from,4,2)=='00' && substr($pre_date_to,4,2)==date('m') 
					&& substr($pre_date_to,0,4)*1+1 == substr($date_from,0,4)*1 )
		;
	}

	public function mysqlInfo(){;
	    if(isset(\mvc::$req['killid']) && is_numeric(\mvc::$req['killid'])){
	        $this->db->query('KILL '.\mvc::$req['killid']);
            echo 'KILL '.\mvc::$req['killid']."<br>";
	    }
        $data=[];
        $st = $this->db->query('SHOW PROCESSLIST;');
        $rows = $st->fetchAll();
        foreach($rows as $k=>$row){
            $data[$k] = $row;
        }
        
        $st = $this->db->query('SHOW GLOBAL VARIABLES');
        $rows = $st->fetchAll();
        foreach($rows as $row){
            $data[$row['Variable_name']] = $row['Value'];
        }
        return $data;
	}
	
    //保存Grid布局
    public function saveGridConfig($uuid,$jsonStr)
    {
		return $this->db->replace('grid_cfg',['gc_config'=>$jsonStr,'gc_uuid'=>$uuid,'gc_user'=>getOpuser()],['gc_uuid','gc_user']);
    }
	
    //获取Grid布局
    public function getGridConfig($uuid)
    {
		$jsonStr = $this->db->get('grid_cfg','gc_config',['gc_uuid'=>$uuid,'gc_user'=>getOpuser()]);
		if(empty($jsonStr)){
    		$jsonStr = $this->db->get('grid_cfg','gc_config',['gc_uuid'=>$uuid,'gc_user'=>'grid']);
		}
		return $jsonStr;
    }
	
    //重置Grid布局
    public function resetGridConfig($uuid)
    {
		return $this->db->delete('grid_cfg',['gc_uuid'=>$uuid,'gc_user'=>getOpuser()]);
    }
    
    //检测是否有PNG图
	public function checkPNG(){
        $dir = \mvc::$cfg['dir']['files'];
	    $this->db->useBuff(false);
        $st = $this->db->query("SELECT * FROM files WHERE filetype='image' AND  fileextname<>'png'");
        $ids = [];
        while($row = $st->fetch()){
            $pngfile = $dir.'/'.ltrim($row['filename'],'/');
            $pngfile = str_replace('.jpg','.png',$pngfile);
            if(file_exists($pngfile)){
                $ids[] = $row['fileid'];
            }
        }
	    $this->db->useBuff(true);
	    foreach($ids as $id){
            $this->db->update('files',['fileextname'=>'png'],['fileid'=>$id]);
	    }
        dd('OK');
	}
	
    //http://127.0.0.1/qgmvc/gsp/admin/sys/changeOutNum?innum=G-01-CH-0003&outnum=S010047
    //http://192.168.0.63/gsp/admin/sys/changeOutNum?innum=&outnum=
    public function changeOutNum($innum,$outnum)
    {
		$innum = trim($innum);
		$outnum = trim($outnum);
		$outnumFormat = formatNum($outnum);
		if(empty($innum) || empty($outnumFormat)){
		    dd('失败: 参数有误');
		}
		$pid = $this->db->get('p_code','pc_p_id',['pc_innum'=>$innum]);
		if(!$pid){
		    dd('失败: INNUM 不存在');
		}
		if(
		    $this->db->has('p_code',['pc_outnum'=>$outnumFormat])
		    ||
		    $this->db->has('p_code_number',['pcn_pf_id'=>2,'pcn_format'=>$outnumFormat])
		 ){
		    dd('失败: OUTNUM 已存在');
		}
		$this->db->begin();
		$this->db->update('p_code',['pc_outnum'=>$outnum],['pc_p_id'=>$pid]);
		$this->db->update('p_code_number',['pcn_number'=>$outnum,'pcn_format'=>$outnumFormat],['pcn_pc_id'=>$pid,'pcn_pf_id'=>2]);
		$this->db->update('pro',['p_outnum'=>$outnum,'p_outnum_format'=>$outnumFormat],['p_id'=>$pid]);
		$this->db->update('pro_numidx',['pn_number'=>$outnum,'pn_formart'=>$outnumFormat],['pn_p_id'=>$pid,'pn_pf_id'=>2]);
		$this->db->update('pro_numidx_temp',['pnt_outnum'=>$outnum],['pnt_p_id'=>$pid]);
		$this->db->update('pro_pic_task',['ppt_outnum'=>$outnum],['ppt_innum'=>$innum]);
		$this->db->update('pro_pic',['ppic_outnum'=>$outnum],['ppic_p_id'=>$pid]);
		$this->db->update('sample',['s_outnum'=>$outnum],['s_innum'=>$innum]);
		$this->db->end();
		
	    dd('外编码 '.$outnum.' 修改成功');
    }

	
    //恢复文件
    public function recoveryFiles()
    {
        function getDbFiles($db,$fp,$objFile,$objImage,$file_category,$file_item,$fileremark,$number,$extname,$src){
           if($row = $db->get('files','*',['file_category'=>$file_category,'file_item'=>$file_item,'fileremark'=>$fileremark]))
           {
               $fileid = $row['fileid'];
               $dist = \mvc::$cfg['dir']['files'].'/'.$row['filename'];
               if(true || !file_exists($dist)){
                   mkdirs(dirname($dist));
                   copy($src,$dist);
                   $objFile->buildAllImage($file_category,$file_item,$dist);
                   if($file_category=='proimage' && $file_item=='beautify'){
                        if($number==$fileremark){
                            if($pid = $db->get('pro','p_id',['p_innum'=>$number])){
                                $objImage->setMain($pid,$fileid);
                            }
                        }
                   }
               }
           }else{
               fputcsv($fp, [$file_category,$file_item,$fileremark,'不存在']);
           }
        }
        
        $dir = \mvc::$cfg['dir']['files'].'/importServer/recovery';
		$arr = scandir($dir);
	    $objFile = makeOne(\model\files::class);
	    $objImage = makeOne(\model\pro\image::class);
        $logpath = 'import/'.date('Ym');
        $logfilename = 'log'.getOpuser().'RecoveryFiles.csv';
        $logfile = \mvc::$cfg['dir']['files'].'/'.$logpath.'/'.$logfilename;
        mkdirs(\mvc::$cfg['dir']['files'].'/'.$logpath);
        $fp = fopen($logfile, 'w'); //记录日志
	    
		foreach($arr as $path)
		{
		    list($file_category,$file_item) = explode('_',$path);
		    if($path=='.' || $path=='..' || empty($file_item))
		        continue;
		   
		   $subarr = scandir($dir.'/'.$path);
		   foreach($subarr as $filename){
    		    if($filename=='.' || $filename=='..')
    		        continue;
    		    $info = explode('.',strtolower(basename($filename)));
                $extname = end($info);
                $fileremark = strtolower($info[0]);
                $number = preg_split('/[_]/',$fileremark)[0];
                getDbFiles($this->db,$fp,$objFile,$objImage,$file_category,$file_item,$fileremark,$number,$extname,$dir.'/'.$path.'/'.$filename);
		   }
		}
		fclose($fp);
        //消息记录与在线提醒
        $url = \mvc::$cfg['path']['files'].'/'.$logpath.'/'.$logfilename;
        $msg = [
        	'amsg_title'=> '图片恢复导入完成',
        	'amsg_body'=> '图片恢复导入完成',
        	'amsg_url'=> $url,
        	'amsg_urltitle'=> basename($url),
        ];
        makeOne(\model\message::class)->send([getOpuser()],$msg);
        socketSendMsg([getOpuser()],$msg);
    }

	//http://192.168.0.63/gsp/admin/sys/delProduct?outnum=xxx
	public function delProduct($outnum)
	{
		$outnum = trim($outnum);
		$outnumFormat = formatNum($outnum);
		$proArr = $this->db->get('pro', ['p_id', 'p_pic', 'p_state_main'],['p_outnum_format'=>$outnumFormat]);
		if(empty($proArr)) 
			$proArr = $this->db->get('p_code', ['pc_id(p_id)', 'pc_state_main(p_state_main)'],['pc_outnum'=>$outnumFormat]);
		if(empty($proArr))  dd('失败: OUTNUM 不存在');
		if($proArr['p_state_main'] == 'Y6')
		{
			$pid = $proArr['p_id'];
			
			$fileids = [];
			$rows = $this->db->select('pro_pic', 'ppic_fileid', ['ppic_p_id'=>$pid]);
			if(!empty($rows))
				$fileids = array_merge($fileids, $rows);
			$this->db->begin();
			
			//优先执行
			$this->db->delete('pro_model_area_link_temp', ['RAW'=>raw(sprintf(" pmat_pmlt_id in (select pmlt_p_id from pro_model_link_temp where pmlt_p_id=%d)", $pid))]);
			$this->db->delete('tags_link', ['RAW'=>raw(sprintf(" tl_type='_promodel' and tl_value_id in (select pmlt_id from pro_model_link_temp where pmlt_p_id=%d)", $pid))]);

			$this->db->delete('pro_model_area_link', ['RAW'=>raw(sprintf(" pma_pml_id in (select pml_id from pro_model_link where pml_p_id=%d)", $pid))]);
			$this->db->delete('tags_link', ['RAW'=>raw(sprintf(" tl_type='promodel' and tl_value_id in (select pml_id from pro_model_link where pml_p_id=%d)", $pid))]);
		
			$this->db->delete('tags_link', ['RAW'=>raw(sprintf(" tl_type='number' and tl_value_id in (select pn_id from pro_numidx where pn_p_id=%d)", $pid))]);
			$this->db->delete('tags_link', ['RAW'=>raw(sprintf(" tl_type='_number' and tl_value_id in (select pnt_id from pro_numidx_temp where pnt_p_id=%d)", $pid))]);
			$this->db->delete('tags_link', ['tl_type'=>'pro', 'tl_value_id'=>$pid]);
			
			$delTableArr = [
				['pro','p_id'],
				['p_code','pc_id'],
				['p_code_number','pcn_pc_id'],
				['gsp_pro_param_val','ppv_p_id'],
				['pro_attaqty','pa_p_id'],
				['pro_exchange','pe_p_id1','pe_p_id2'],
				['pro_fatherson','pf_father_pid','pf_son_pid'],
				['pro_merge','pch_p_id_from','pch_p_id_to'],
				['pro_model_change','pml_p_id'],
				['pro_model_cn_link','pml_p_id'],
				['pro_model_link','pml_p_id'],
				['pro_model_link_temp','pmlt_p_id'],
				['pro_numidx','pn_p_id'],
				['pro_numidx_change','pn_p_id'],
				['pro_numidx_temp','pnt_p_id'],
				['pro_pair','pp_p_id1', 'pp_p_id2'],
				['pro_pic','ppic_p_id'],
				['pro_pic_task','ppt_p_id'],
				['pro_state_date','pst_p_id'],
			];
			foreach($delTableArr as $tv)
			{
				$where = [$tv[1]=>$pid];
				if(count($tv) > 2)
				{
					$orArr = [];
					for($i = 1; $i < count($tv); $i++)
					{
						$orArr[$tv[$i]] = $pid;
					}
					$where = ['OR'=>$orArr];
				}
				$this->db->delete($tv[0], $where);
			}

			$this->db->update('sample', ['s_p_id'=>0, 's_outnum'=>''], ['s_p_id'=>$pid]);
			$this->db->end();
			
			if(!empty($fileids))
			{
				$files = new \model\files();
				$files->delete($fileids, true);
			}
			if(!empty($proArr['p_pic']))
			{
				$mainf = ['catalog_gsp','catalog_g','tecdoc'];
				foreach($mainf as $mf)
				{
					foreach(['small.jpg', 'mid.jpg', 'big.jpg'] as $sf)
					{	
						//pro\ xxx \ pid % 100  \ pidsmall.jpg 
						$delfile = \mvc::$cfg['dir']['files'].'/pro/'.$mf.'/'.($pid%100).'/'.$pid.$sf;
						@unlink();
					}
				}
			}
			dd('外编码 '.$outnum.' 删除成功');
		}
		else
		{
			dd('失败: 只能删除Y6状态的产品');
		}
	}
	
    //批量删除样品
	public function deleteSample()
    {
		if(!\p::isAdmin()){
		    dd('非法操作');
		    exit();
		}
		$sample = make(\model\sample\sample::class);
		$filename = \mvc::$cfg['dir']['files'].'/temp/sampleDelete.csv';
		$arr = file($filename);
        $this->db->begin();
		foreach($arr as $plmseq){
		    $plmseq = trim($plmseq);
		    $row = $sample->getRowByFieldValue('s_plmseq',$plmseq);
		    $ids = $row['s_id'];
		    if($ids){
                $this->db->delete('history',['h_mod'=>'sample','h_mod_id'=>$ids]);
                $this->db->delete('sample_supplier',['ss_s_id'=>$ids]);
                $this->db->delete('sample_process',['sp_s_id'=>$ids]);
                $this->db->delete('sample_numidx',['sn_s_id'=>$ids]);
                $this->db->delete('sample_devechange',['sb_id'=>$ids]);
                $this->db->delete('sample_develop_change',['sdc_s_id'=>$ids]);
                $this->db->delete('sample_model_link',['sml_s_id'=>$ids]);
                $this->db->delete('sample_model_txt',['smt_s_id'=>$ids]);
                $this->db->delete('sample_tech',['st_s_id'=>$ids]);
                $this->db->delete('sample',['s_id'=>$ids]);
		    }
		}
        $this->db->end();
        exit('操作完成');
    }
    //批量清除样品车型样品
	public function clearModelSample()
    {
		if(!\p::isAdmin()){
		    dd('非法操作');
		    exit();
		}
		$sample = make(\model\sample\sample::class);
		$filename = \mvc::$cfg['dir']['files'].'/temp/sampleClearModel.csv';
		$arr = file($filename);
        $this->db->begin();
		foreach($arr as $plmseq){
		    $plmseq = trim($plmseq);
		    $row = $sample->getRowByFieldValue('s_plmseq',$plmseq);
		    $ids = $row['s_id'];
		    if($ids){
                $this->db->delete('sample_model_link',['sml_s_id'=>$ids]);
                $this->db->delete('sample_model_txt',['smt_s_id'=>$ids]);
		    }
		}
        $this->db->end();
    }
    
	public function get_event_month_sample_sql()
    {
		$sql = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = 'month_sample' AND NOT COLUMN_COMMENT like 'ADD%'";
		$rows = $this->db->query($sql)->fetchAll();
		$fields = [];
		$fields[] = "DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y') `year`";
		$fields[] = "DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%m') `month`";
		$fields[] = "DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m') `yearmonth`";
		$fields[] = "cate.parent_id `bigcate`";
		$fields[] = "s_cate `cate`";
		$fields[] = "s_state_main `state`";
		$fields[] = "IFNULL(DATEDIFF(s_stop_time,s_send_plm_time),-1)+1 `cycle`";
		foreach($rows as $row){
		    $fields[]= '`'.$row['COLUMN_NAME'].'`';
		}
		$select = "SELECT ". implode(',',$fields) . " FROM gspcatalog.sample";
		$select.= " LEFT OUTER JOIN gspcatalog.pro_cate cate ON s_cate = cate.id";
		$insert = "INSERT IGNORE INTO `month_sample` ".$select.";\r\n";
		echo $insert;
	}
	
    /**更换图片水印
    * @param $type 类型,只能是 g,gsp
    */
	public function watermark($type)
    {
        if(!in_array($type,['g','gsp'])){
            exit('type 只能是 g 或者 gsp');
        }
        $waterFile = './static/images/watermark/'.$type.'.png';
        if(!file_exists($waterFile)){
            exit('水印文件不存在:'.$waterFile);
        }
        echo "开始重新生成水印...\r\n";
        $image = make(\lib\image::class,100);
        $logfile = \mvc::$cfg['dir']['log'].'/watermark_change.csv';
        $fp = fopen($logfile, 'w'); //记录日志
        $hasError = false;
        $rows  = $this->db->select('pro',['[>]files'=>['p_pic'=>'fileid']],['p_id','p_outnum','filename'],['p_pic[>]'=>0]);
        $count = count($rows);
        $i=0;
		$cfg = \mvc::$cfg['upload']['category']['proimage']['items']['catalog_'.$type];
        foreach($rows as $row){
            $i++;
            if($row['filename']){
                $pid = $row['p_id'];
                $initFilename = \mvc::$cfg['dir']['files'].'/'.ltrim($row['filename'],'/');
                if(file_exists($initFilename)){
                    foreach(['big','mid','small'] as $key){
                        $filename = \mvc::$cfg['dir']['files'].'/pro/catalog_'.$type . '/' . ($pid % 100) . '/' . $pid. $key .'.jpg';
						//$res = $image->Watermark($initFilename,$filename,$waterFile);
						//原图尺寸过大 2023/2/10
						$size = $cfg['size'][$key];
						$res = $image->ResizeWatermark($initFilename,$filename,$size[0],$size[1],$waterFile);
                        if($res !== true){
                            $hasError = true;
                            fputcsv($fp, [$filename,'导入失败,请检测是否是一个有效的图片']);
                        }else{
							@chmod($filename, 0777);
                        }
                    }
                }
                echo $row['p_outnum']. "  ". $i .'/'. $count."\r\n";
            }
        }
        fclose($fp);
        echo '日志文件位置:'.$logfile."\r\n";
        echo "生成水印完成。\r\n";
	}


	 //g,gsp小图加水印
    public function resetSmallPicWatermark()
	{
        echo "开始重新生成水印...\r\n";
		$image = make(\lib\image::class,100);
        $logfile = \mvc::$cfg['dir']['log'].'/resetSmallPicWatermark.csv';
        $fp = fopen($logfile, 'w'); //记录日志
        $hasError = false;
        $rows  = $this->db->select('pro',['[>]files'=>['p_pic'=>'fileid']],['p_id','p_outnum','filename'],['p_pic[>]'=>0]);
        $count = count($rows);
        $i=0;
        foreach($rows as $row)
		{
			$i++;
            if(empty($row['filename'])) continue;
			$pid = $row['p_id'];
			$initFilename = \mvc::$cfg['dir']['files'].'/'.ltrim($row['filename'],'/');
			if(file_exists($initFilename)){
				foreach(['catalog_g','catalog_gsp'] as $item){	
					$watermark = \mvc::$cfg['dir']['static'].'/images/watermark/pro/'.$item.'.png';
					$cfg = \mvc::$cfg['upload']['category']['proimage']['items'][$item];
					$key = 'small';
					$size = $cfg['size'][$key];
					$filename = \mvc::$cfg['dir']['files'].'/pro/'.$item . '/' . ($pid % 100) . '/' . $pid. $key .'.jpg';
					$res = $image->ResizeWatermark($initFilename,$filename,$size[0],$size[1],$watermark);
					if($res !== true){
						$hasError = true;
						fputcsv($fp, [$filename,'导入失败,请检测是否是一个有效的图片']);
					}
				}
			}
			else{
				fputcsv($fp, [$row['p_outnum'], $initFilename,'不存在']);
			}
			echo $row['p_outnum']. "  ". $i .'/'. $count."\r\n";
           
        }
        fclose($fp);
        echo '日志文件位置:'.$logfile."\r\n";
        echo "生成水印完成。\r\n";
	}

    /**批量修改Innum	*/
	public function changeInnum(){
		$tables = [
			//'api_fromplm_task*',
			//'api_task*',
			//'files*',
			//'gsp_new_saledataall_bi--',
			//'gsp_new_saledataall_bi_bak_copy1--',
			//'gsp_new_saledataall_bi_product*',
			#'p_code',
			#'p_code_number',
			#'pack',
			#'pro',
			//'pro_exchange*',
			#'pro_merge',
			//'pro_model_link* :pml_quote_number,pml_merge_remark',
			#'pro_model_link_temp',
			'pro_model_change',
			#'pro_numidx',
			#'pro_numidx_change',
			#'pro_numidx_temp',
			//'pro_numidx_temp_copy--',
			#'pro_pic',
			#'pro_pic_task',
			#'sample',
			//'sample_copy1--',
			//'sample_copy2--',
			//'sample_develop_change* :sdc_custrequ_change',
			//'sample_numidx',
			//'sample_project_notice*:spn_change_content,spn_change_reason',
			#'sample_tech',
			//'temp1--',
		];
		$change_table = "sys_change_innum";
		//$change_table = "1temp";
		$br = "\r\n";
		print_r($br.$br."========================================".$br);
		print_r("Begin:".$br);
		$this->db->begin();

		if(in_array('p_code',$tables)){
		    echo "p_code".$br;
			$sql = qgprintf("UPDATE p_code
								JOIN sys_change_innum ON pc_innum = old_innum
								SET pc_innum = new_innum");
			$this->db->query($sql);
		}

		if(in_array('pack',$tables)){
		    echo "pack".$br;
			$sql = qgprintf("UPDATE pack
								JOIN sys_change_innum ON gspno = old_innum
								SET gspno = new_innum");
			$this->db->query($sql);
		}

		if(in_array('pro_model_change',$tables)){
		    echo "pro_model_change".$br;
			$sql = qgprintf("UPDATE pro_model_change
								JOIN sys_change_innum ON p_innum = old_innum
								SET p_innum = new_innum");
			$this->db->query($sql);
		}

		if(in_array('pro_model_link_temp',$tables)){
		    echo "pro_model_link_temp".$br;
			$sql = qgprintf("UPDATE pro_model_link_temp
								JOIN sys_change_innum ON pmlt_innum = old_innum
								SET pmlt_innum = new_innum");
			$this->db->query($sql);
		}

		if(in_array('pro_numidx_change',$tables)){
		    echo "pro_numidx_change".$br;
			$sql = qgprintf("UPDATE pro_numidx_change
								JOIN sys_change_innum ON p_innum = old_innum
								SET p_innum = new_innum");
			$this->db->query($sql);
		}

		if(in_array('pro_numidx_temp',$tables)){
		    echo "pro_numidx_temp".$br;
			$sql = qgprintf("UPDATE pro_numidx_temp
								JOIN sys_change_innum ON pnt_innum = old_innum
								SET pnt_innum = new_innum");
			$this->db->query($sql);
		}

		if(in_array('pro_pic',$tables)){
		    echo "pro_pic".$br;
			$sql = qgprintf("UPDATE pro_pic
								JOIN sys_change_innum ON ppic_innum = old_innum
								SET ppic_innum = new_innum,ppic_filebase=REPLACE(ppic_filebase,old_innum,new_innum) ");
			$this->db->query($sql);
		}

		if(in_array('pro_pic_task',$tables)){
		    echo "pro_pic_task".$br;
			$sql = qgprintf("UPDATE pro_pic_task
								JOIN sys_change_innum ON ppt_innum = old_innum
								SET ppt_innum = new_innum");
			$this->db->query($sql);
		}

		if(in_array('sample',$tables)){
		    echo "sample".$br;
			$sql = qgprintf("UPDATE sample
								JOIN sys_change_innum ON s_innum = old_innum
								SET s_innum = new_innum");
			$this->db->query($sql);
		}

		if(in_array('sample_tech',$tables)){
		    echo "sample_tech".$br;
			$sql = qgprintf("UPDATE sample_tech
								JOIN sys_change_innum ON SUSER03 = old_innum
								SET innum = new_innum,SUSER03 = new_innum");
			$this->db->query($sql);
		}


		$rows = $this->db->select($change_table,'*');
		$regI = 1;
		foreach($rows as $row){
			print_r($regI.$br);
			$row['old_formart'] = formatNum($row['old_innum']);
			$row['new_formart'] = formatNum($row['new_innum']);

			if(in_array('p_code_number',$tables)){
				$sql = qgprintf("UPDATE p_code_number
									JOIN sys_change_innum ON pcn_pf_id=3 AND pcn_format = '{:old_formart}'
									SET pcn_number = '{:new_innum}',pcn_format='{:new_formart}'",$row);
				$this->db->query($sql);
			}			

			if(in_array('pro',$tables)){
				$sql = qgprintf("UPDATE pro
									JOIN sys_change_innum ON p_innum = '{:old_innum}'
									SET p_innum = '{:new_innum}',p_innum_format='{:new_formart}'",$row);
				$this->db->query($sql);
			}
			
			if(in_array('pro_merge',$tables)){
				$sql = qgprintf("UPDATE pro_merge SET 
						pch_from=
							REPLACE(
								REPLACE(
									REPLACE(pch_from,'\"p_innum\":\"{:old_innum}\"','\"p_innum\":\"{:new_innum}\"')
									,'\"p_innum_format\":\"{:old_formart}\"','\"p_innum_format\":\"{:new_formart}\"'
								),'\"pc_innum\":\"{:old_innum}\"','\"pc_innum\":\"{:new_innum}\"'
							)
						WHERE pch_from LIKE '%\"p_innum\":\"{:old_innum}\"%'",$row);
				$this->db->query($sql);
			}

			if(in_array('pro_numidx',$tables)){
				$sql = qgprintf("UPDATE pro_numidx
									JOIN sys_change_innum ON pn_pf_id=3 AND pn_formart = '{:old_formart}'
									SET pn_number = '{:new_innum}',pn_formart='{:new_formart}'",$row);
				$this->db->query($sql);
			}			

			$regI++;
		}
		$this->db->end();
		print_r("End.".$br);
	}
}