phpstock/app/command/makeGood.php

<?php
declare (strict_types = 1);

namespace app\command;

use app\admin\model\Unit;use PhpOffice\PhpSpreadsheet\Cell\Coordinate;use PhpOffice\PhpSpreadsheet\IOFactory;use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;use think\facade\Db;

class makeGood extends Command
{
	protected $db;
    protected function configure()
    {
        // 指令配置
        $this->setName('makegood')
            ->setDescription('the makegood command');
    }

    protected function execute(Input $input, Output $output)
    {
        // 指令输出
        $output->writeln('makegood');
        $file ="./public/exece.xlsx";
        $spreadsheet =IOFactory::load($file);
        // 选择第一个工作表
        ini_set("memory_limit","-1");
        $this->db=Db::connect("mysql_sys");
//        $this->getNake();return;
		$worksheet = $spreadsheet->getActiveSheet();
		$height = $worksheet->getHighestRow();

		// 获取最高的列号
		$highestColumn = $worksheet->getHighestColumn();
		$number = Coordinate::columnIndexFromString($highestColumn);
        $header=[
        '',
        'spuCode',
        'good_name',
        'cat_id',
        'brand_id',
        'good_unit',
        'good_type',
        'companyNo',
        'moq',
        'customized',
        'tax',
        'supplierNo',
        'is_auth',
        'is_stock',
        'after_sales',
        'craft_desc',
        'good_remark',
        'good_size',
        'weight',
        'packing_way',
        'packing_size',
        'packing_spec',
        'packing_list',
        'packing_weight',
        'good_bar',
        'supply_area',
        'delivery_place',
        'origin_place',
        'delivery_day',
        'lead_time',
        'sample_day',
        'sample_fee',
        'cert_fee',
        'packing_fee',
        'cost_fee',
        'mark_fee',
        'demo_fee',
        'open_fee',
        'noble_metal',
        'noble_weight',
        'is_gold_price',
        'cgd_gold_price',
        'market_price',
        'nake_price',
        'is_step',
        'supplier_origin_price',
        'cgd_supplier_code',
        'cgd_apply_id',
        'cgd_apply_name',
        'is_exclusive',
        'is_diff',
        'config',
        'other_config',
        'stock_moq',
        'is_support_barter',
        'is_combind',
        'plat_code'];
        $data=[];
        for ($i=2;$i<=$height;$i++){
        	$temp=[];
            for ($j=1;$j<=$number;$j++){
            	if(isset($header[$j])){
            	 $temp[$header[$j]] = $worksheet->getCellByColumnAndRow($j,$i)->getValue()??'';
            	}else{
            		break;
            	}
            }
            $temp['spuCode'] = substr(makeNo('SKU'),0,-4).str_pad(strval($i),4,"0",STR_PAD_LEFT);
            $temp['companyName'] = $this->getCompanyName($temp['companyNo']);
            $temp['supplierName'] = $this->getCompanyName($temp['supplierNo']);
            $temp['cgd_supplier_name'] = $this->getCompanyName($temp['cgd_supplier_code']);
            $temp['delivery_place']=  $this->getAddress($temp['delivery_place']);
            $temp['origin_place']=  $this->getAddress($temp['origin_place']);
            $temp['good_unit']=  $this->getUnit($temp['good_unit']);
            $data[]=$temp;
            if($i%1000==0){
            	 Db::name('good_temp')->insertAll($data);
            	 $data=[];
            }
        }
        if(!empty($data)) Db::name('good_temp')->insertAll($data);
        $this->getOnline();
        $this->getNake();
    }
    
    public function getCompanyName($code){
        return $this->db->name("headquarters")->where("code",$code)->value("name",'');
    }
    public function getUnit($unit_name){
      $unit=Unit::where("unit",$unit_name)->findOrEmpty();
      if($unit->isEmpty()){
      	$info =Unit::create(["unit"=>$unit_name]);
      	return $info->id;
      }
      return $unit->id;
    }
    
    public function getAddress($addr){
        $p_c_a =get_address($addr);
        $addeRss=[];
                $province = Db::name('province')
                    ->field('province_code,name')
                    ->where('name', $p_c_a['province'])
                    ->findOrEmpty();
			$addeRss[]=$province['province_code'];
                $city = Db::name('city')
                    ->field('city_code,name')
                    ->where(['name' => $p_c_a['city']?:$p_c_a['province'], 'province_code' => $province['province_code'] ?: ''])
                    ->findOrEmpty();
                $addeRss[]=$city['city_code']?? $province['province_code'];
                $area = Db::name('area')
                    ->field('area_code,name')
                    ->where(['name' => $p_c_a['district'], 'city_code' => $city['city_code'] ?:''])
                    ->findOrEmpty();
               
              $addeRss[]= $area['area_code']?? '';
             
              return implode(",",$addeRss);
    }
    
    public function getOnline(){
    	$file ='./public/add.xlsx';
        $spreadsheet =IOFactory::load($file);
        $worksheet = $spreadsheet->getActiveSheet();
		$height = $worksheet->getHighestRow();

		// 获取最高的列号
		$highestColumn = $worksheet->getHighestColumn();
		$number = Coordinate::columnIndexFromString($highestColumn);
		$data=[];
		$header=["",'spuCode','skuCode','platform_code','is_compliance','compliance_remark','plat_code','is_fixed'];
        for ($i=2;$i<=$height;$i++){
        	$temp=[];
            for ($j=1;$j<=$number;$j++){
            	if(isset($header[$j])){
            	 $temp[$header[$j]] = $worksheet->getCellByColumnAndRow($j,$i)->getValue()??'';
            	}else{
            		break;
            	}
            }
            $temp['spuCode'] = Db::name("good_temp")->where(["plat_code"=>$temp['plat_code']])->value("spuCode",'');
            $temp['skuCode'] = substr(makeNo('SN'),0,-4).str_pad(strval($i),4,'0',STR_PAD_LEFT);
            $data[]=$temp;
             if($i%1000==0){
            	 Db::name('good_platform_temp')->insertAll($data);
            	 $data=[];
            }
        }
       if(!empty($data)) Db::name('good_platform_temp')->insertAll($data);
        $this->getLadder();
    }
    
    
    public function getLadder(){
    	        $file ='./public/ladder.xlsx';
    	        $spreadsheet =IOFactory::load($file);
    	        $worksheet = $spreadsheet->getActiveSheet();
    			$height = $worksheet->getHighestRow();
    	
    			// 获取最高的列号
    			$highestColumn = $worksheet->getHighestColumn();
    			$number = Coordinate::columnIndexFromString($highestColumn);
    			$data=[];
    			$header=['','skuCode','min_num','sale_price','origin_price','market_price','market_platform','origin_rate','cost_fee','plat_code'];
    	        for ($i=2;$i<=$height;$i++){
    	        	$temp=[];
    	            for ($j=1;$j<=$number;$j++){
    	            	if(isset($header[$j])){
    	            	 $temp[$header[$j]] = $worksheet->getCellByColumnAndRow($j,$i)->getValue()??'';
    	            	}else{
    	            		break;
    	            	}
    	            }
    	            $temp['skuCode'] = Db::name('good_platform_temp')->where(['plat_code'=>$temp['plat_code']])->value('skuCode','');
    	            $data[]=$temp;
    	            if($i%1000==0){
		                 Db::name('good_ladder_temp')->insertAll($data);
		                 $data=[];
                    }
        }
       if(!empty($data))Db::name('good_ladder_temp')->insertAll($data);
    }
    
   public function getNake(){
    	        $file ='./public/nake.xlsx';
    	        $spreadsheet =IOFactory::load($file);
    	        $worksheet = $spreadsheet->getActiveSheet();
    			$height = $worksheet->getHighestRow();
    	
    			// 获取最高的列号
    			$highestColumn = $worksheet->getHighestColumn();
    			$number = Coordinate::columnIndexFromString($highestColumn);
    			$data=[];
    			$header=['','spuCode','min_num','nake_fee','cost_fee','delivery_fee','cert_fee','mark_fee','package_fee','other_fee','nake_total','plat_code'];
    	        for ($i=2;$i<=$height;$i++){
    	        	$temp=[];
    	            for ($j=1;$j<=$number;$j++){
    	            	if(isset($header[$j])){
    	            	 $temp[$header[$j]] = $worksheet->getCellByColumnAndRow($j,$i)->getValue()??'';
    	            	}else{
    	            		break;
    	            	}
    	            }
    	            $temp['spuCode'] = Db::name('good_temp')->where(['plat_code'=>$temp['plat_code']])->value('spuCode','');
    	            $data[]=$temp;
    	            if($i%1000==0){
		                 Db::name('good_nake_temp')->insertAll($data);
		                 $data=[];
                    }
        }
       if(!empty($data))Db::name('good_nake_temp')->insertAll($data);
    	  
    }
}