Laravel 命令行工具实例

大数据导入

<?php
 
namespace eBay\Console\Commands;
 
use Illuminate\Console\Command;
use DB;
use Schema;
 
class AmazonImport extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'amazon:import';
 
    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Import Amazon Data';
 
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }
 
    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //
        $import = DB::table('amazon_imports')->where("has_import",'<',1)->orderBy('date')->first();
        if(!isset($import->id)) { return; }
         
        // 表名
        $tableName = 'amazon_import_'.$import->date;
        // 删表,如果存在
        Schema::dropIfExists($tableName);
        // 建表
        $this->builderTable($tableName);
         
        if (Schema::hasTable($tableName)){
            $s = microtime(true);
             
            // 插入数据
            $file = 'D:/11-201.txt';
            $row = 0; $dd = [];
            if (($handle = fopen($file, "r")) !== FALSE) {
                while (($data = fgetcsv($handle, 0, "\t")) !== FALSE) {
                    $row++;
                    if($row < 2) { continue; }
                    $d = [ 
                        'marketplace_id' => $data [0],
                        'gl_product_group' => $data [1],
                        'gl_product_group_desc' => $data [2],
                        'category_desc' => $data [3],
                        'subcategory_desc' => $data [4],
                        'asin' => $data [5],
                        'item_name' => $data [6],
                        'brand_name' => $data [7],
                        'ordered_gms_usd_tim' => $data [8],
                        'ordered_units_tim' => $data [9],
                        'fba_ordered_gms_usd_tim' => $data [10],
                        
                        'fba_ordered_units_tim' => $data [11],
                        'asp' => round($data [12],15),
                        
                        'gms_rank' => $data [13],
                        'gms_view_count_tim' => $data [14],
                        'units_conversion_rate' => round($data [15],15)
                    ];
                    
                    //if($row > 5000) { break; }
                    //continue;
                    
                    $dd [] = $d;
                     
                    // 合理调整,MySQL对于批量数据大小有限制
                    if (count ( $dd ) > 1999) {
                        DB::table ( $tableName )->insert ( $dd );
                        $dd = [ ];
                    }
                }
                if(!empty($dd)) {
                    DB::table ( $tableName )->insert ( $dd );
                    $dd = [ ];
                }
                $e = microtime(true);
                echo 'Total:'.(float)($e - $s)."\n";
                 
                fclose($handle);
                 
                // 
                DB::table('amazon_imports')->where('id',$import->id)->update(['has_import'=>1]);
            }
            
            // 插表完成后,更新类目
            DB::table('amazon_categories')->truncate();            
            $prefix = DB::connection()->getTablePrefix();  
            $query = "INSERT INTO ".$prefix."amazon_categories(product_group, product_group_desc, category_desc,subcategory_desc)
                SELECT gl_product_group, gl_product_group_desc, category_desc, subcategory_desc FROM `".$prefix.$tableName."` GROUP BY gl_product_group_desc,category_desc,subcategory_desc";  
            DB::statement($query);
             
            // 插表完成后,产生比对数据
            $compares = DB::table('amazon_imports')
                ->where("has_import",'>',0)
                ->where('id','!=',$import->id)
                ->orderBy('date','desc')->take(4)->get();
             
            $this->call('amazon:caculate', [
                'from' => $import->date
            ]);
        } else {
            return;
        }
    }
     
    public function builderTable($tableName)
    {
        Schema::create($tableName, function($table)
        {
            $table->increments('id');
             
            //
            $table->tinyInteger('marketplace_id')->unsigned();
            $table->smallInteger('gl_product_group')->unsigned();
            $table->string('gl_product_group_desc',32);
            $table->string('category_desc',64);
            $table->string('subcategory_desc',64);
            $table->string('asin',16);
            $table->string('item_name',128);
            $table->string('brand_name',64);
            $table->decimal('ordered_gms_usd_tim',10,2);
            $table->decimal('fba_ordered_gms_usd_tim',10,2);
            $table->smallInteger('ordered_units_tim');
            $table->smallInteger('fba_ordered_units_tim');
            $table->double('asp',20,15);
            $table->smallInteger('gms_rank');
            $table->mediumInteger('gms_view_count_tim');
            $table->double('units_conversion_rate',20,15);
            
            $table->index('asin');
        });
    }
}

执行批量计算

<?php
 
namespace eBay\Console\Commands;
 
use Illuminate\Console\Command;
use DB;
use Schema;
 
class AmazonCaculate extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'amazon:caculate {from}';
 
    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Caculate Amazon Data';
 
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }
 
    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        // 取回参数
        $date = $this->argument('from');
        if(!empty($date)) {
            $has = DB::table("amazon_imports")->where('date',$date)->where('has_import','>',0)->first();
            if(!isset($has->id)) {
                echo 'Import Date:'.$date." Do Not Import.\n";
                return;
            }
        }
        
        // 取回需要比较的日期,当前记录的前4个
        $compares = DB::table('amazon_imports')
            ->where("has_import",'>',0)
            ->where('id','<', $has->id)
            ->orderBy('date','desc')->take(4)->get();
        $nowTable = "amazon_import_".$has->date;
        
        // 原表不存在,就不要比较了
        if (!Schema::hasTable($nowTable)){
            echo "Table ".$nowTable." DO NOT Existes\n";
            return;
        }

        foreach($compares as $compare) {
            $beforeTable = "amazon_import_".$compare->date;
            // 被比较的表不存在,就到此结束,后面的咔嚓
            if (!Schema::hasTable($beforeTable)){
                break;
            }
            
            $tableName = 'amazon_'.$has->date."_".$compare->date;
            // 如果结果表已经存在,不需要重复
            if (!Schema::hasTable($tableName)){
                // 删表,如果存在
                // Schema::dropIfExists($tableName);
                // 建表
                $this->builderTable($tableName);
            
                // 批量插入
                $prefix = DB::connection()->getTablePrefix();
                $query = "INSERT INTO `".$prefix.$tableName."`
                    (marketplace_id,
                    gl_product_group,
                    gl_product_group_desc,
                    category_desc,
                    subcategory_desc,
                    asin,
                    item_name,
                    brand_name,
                    
                    ordered_gms_usd_tim,
                    ordered_gms_usd_tim_diff,
                    
                    fba_ordered_gms_usd_tim,
                    fba_ordered_gms_usd_tim_diff,
                    
                    ordered_units_tim,
                    ordered_units_tim_diff,
                    
                    fba_ordered_units_tim,
                    fba_ordered_units_tim_diff,
                    
                    asp,
                    asp_diff,
                    
                    gms_rank,
                    gms_rank_diff,
                    
                    gms_view_count_tim,
                    gms_view_count_tim_diff,
                    
                    units_conversion_rate,
                    
                    is_new)
                SELECT 
                    n.marketplace_id, 
                    n.gl_product_group, 
                    n.gl_product_group_desc, 
                    n.category_desc,
                    n.subcategory_desc,
                    n.asin,
                    n.item_name,
                    n.brand_name,
                    n.ordered_gms_usd_tim,
                    (n.ordered_gms_usd_tim - b.ordered_gms_usd_tim) AS ordered_gms_usd_tim_diff,
                    
                    n.fba_ordered_gms_usd_tim,
                    (n.fba_ordered_gms_usd_tim - b.fba_ordered_gms_usd_tim) AS fba_ordered_gms_usd_tim_diff,
                    
                    n.ordered_units_tim,
                    (n.ordered_units_tim - b.ordered_units_tim) AS ordered_units_tim_diff,
                    
                    n.fba_ordered_units_tim,
                    (n.fba_ordered_units_tim - b.fba_ordered_units_tim) AS fba_ordered_units_tim_diff,
                    
                    n.asp,
                    (n.asp - b.asp) AS asp_diff,
                    
                    n.gms_rank,
                    (n.gms_rank - b.gms_rank) AS gms_rank_diff,
                    
                    n.gms_view_count_tim,
                    (n.gms_view_count_tim - b.gms_view_count_tim) AS gms_view_count_tim_diff,
                    
                    n.units_conversion_rate,
                    
                    IF(b.id IS NULL,1,0)
                FROM `".$prefix.$nowTable."` n LEFT JOIN `".$prefix.$beforeTable."` b ON n.asin = b.asin";
                
                DB::statement($query);
            } 
        }
    }
     
    public function builderTable($tableName)
    {
        Schema::create($tableName, function($table)
        {
            $table->increments('id');
             
            //
            $table->tinyInteger('marketplace_id')->unsigned();
            $table->smallInteger('gl_product_group')->unsigned();
            $table->string('gl_product_group_desc',32);
            $table->string('category_desc',64);
            $table->string('subcategory_desc',64);
            $table->string('asin',16);
            $table->string('item_name',128);
            $table->string('brand_name',64);
            $table->decimal('ordered_gms_usd_tim',10,2);
            $table->decimal('ordered_gms_usd_tim_diff',10,2);
            
            $table->decimal('fba_ordered_gms_usd_tim',10,2);
            $table->decimal('fba_ordered_gms_usd_tim_diff',10,2);
            
            $table->integer('ordered_units_tim');
            $table->integer('ordered_units_tim_diff');
            
            $table->integer('fba_ordered_units_tim');
            $table->integer('fba_ordered_units_tim_diff');
            
            $table->double('asp',20,15);
            $table->double('asp_diff',20,15);
            
            $table->integer('gms_rank')->unsigned();
            //$table->smallInteger('gms_rank_before')->unsigned();
            $table->integer('gms_rank_diff');
            
            $table->integer('gms_view_count_tim');
            $table->integer('gms_view_count_tim_diff');
            
            $table->double('units_conversion_rate',20,15);
            $table->tinyInteger('is_new')->default(0);
            
            $table->index('asin','asin_idx');
            
            //$table->index('gl_product_group_desc');
            //$table->index('category_desc');
            //$table->index('subcategory_desc');
            $table->index(['gl_product_group_desc','category_desc','subcategory_desc'],'category_idx');
        });
    }
}

命令行工具是非常便利的,数据库的操作相比之下也省了不少功夫。