月度归档:2015年12月

jQuery UI – Datepicker

单日期选择:

$(function() {
	var dayNamesMin =['日','一', '二', '三', '四', '五', '六'];
	var monthNamesShort = ['一','二','三','四','五','六','七','八','九','十','十一','十二'];
	$('#datepicker').datepicker({
		changeMonth: true,
		changeYear: true,
		dayNamesMin:dayNamesMin,
		monthNamesShort:monthNamesShort,
		dateFormat: 'yy-mm-dd'
	});
});

jquery-ui-datepicker-only

$(function(){
    var dayNamesMin =['日','一', '二', '三', '四', '五', '六'];
    var monthNamesShort = ['一','二','三','四','五','六','七','八','九','十','十一','十二'];
    $("#datefrom").datepicker({
	defaultDate: "+1w",
        changeMonth: true,
        dayNamesMin:dayNamesMin,
	monthNamesShort:monthNamesShort,
	dateFormat: 'yy-mm-dd',
        numberOfMonths: 3,
        onClose: function( selectedDate ) {
        	$("#dateto").datepicker( "option", "minDate", selectedDate );
        }
    });
    
    $("#dateto").datepicker({
        defaultDate: "+1w",
        changeMonth: true,
        dayNamesMin:dayNamesMin,
	monthNamesShort:monthNamesShort,
	dateFormat: 'yy-mm-dd',
        numberOfMonths: 3,
        onClose: function( selectedDate ) {
          	$("#datefrom").datepicker( "option", "maxDate", selectedDate );
        }
    });
});

jquery-ui-datepicker

jQuery UI中提供的是日历选择,无法选择到具体的时间,如果需要选择时间,可以引入一个扩展:

<link href="/css/jquery-ui-timepicker-addon.min.css" rel="stylesheet">
<script src="/js/jquery-ui-timepicker-addon.min.js"></script>
<script type="text/javascript">
$(function(){
	var dayNamesMin =['日','一', '二', '三', '四', '五', '六'];
	var monthNamesShort = ['一','二','三','四','五','六','七','八','九','十','十一','十二'];
	$("#datepicker").datetimepicker({
		changeMonth: true,
		changeYear: true,
		dayNamesMin:dayNamesMin,
		monthNamesShort:monthNamesShort,
		dateFormat: 'yy-mm-dd',
		showSecond: true,
		timeFormat: 'HH:mm:ss',
		stepHour: 1,
		stepMinute: 1,
		stepSecond: 1
	});
});
</script>

(http://trentrichardson.com/examples/timepicker)
jquery-ui-datetime

这个时间选择器的JS文件体积有点大。

Laravel 邮件

Laravel中的发送邮件部分是swiftmailer的封装。它封装的意义在于使用Laravel并且更加容易的使用。目前存在的邮件相关的程序包很多,没有必要自己实现一个。swiftmailer大概是一个不错的邮件包。

关于邮件发送的内容,本身涉及到的内容是非常多的,但是我们可能要使用的仅仅是发送邮件,大部分情况下,我们需要的仅仅一个模子程序:

$mes = '你好';
        
$image = Image::make(storage_path('app/files/tt.jpg'));
$imgBase64String = $image->encode('data-url');
        
\Mail::send("emails.test", [
    'mes' => $mes,
    // 二进制数据流
    'image1'=>Storage::get('app/files/tt.jpg'),
    'image2'=>$imgBase64String         
], function($message) {
    $message->to('ifeeline@qq.com')->subject('test');
            
    //在邮件中上传附件,附加的名称乱码处理
    $attachment = storage_path('app/files/test.doc');
    $message->attach($attachment,['as'=>"=?UTF-8?B?".base64_encode('队列')."?=.doc"]);
});

## 模板
<span>Hello {{$mes}}</span>
<img src="{{$message->embedData($image1,'tt.jpg')}}">
<img src="{!!$image2!!}">

要在邮件主体中嵌入图片,最简便的方法就是直接生成data-url字符串。另外,附件中文名称的处理也算奇葩了。

Laravel 中使用 Redis

Laravel中对Redis的访问是通过predis/predis实现的,这个包是纯PHP实现(注意:PHP中有一个redis扩展实现,如果追求高效,可以使用它)。

安装predis/predis

php composer.phar require predis/predis

Laravel本身提供了一个叫Redis的Facade:

Redis      redis       Illuminate\Redis\Database

Illuminate\Redis\Database是一层简单的封装,内部使用Predis\Client(predis/predis包提供)。大概看看serviceProvider:

<?php

namespace Illuminate\Redis;

use Illuminate\Support\ServiceProvider;

class RedisServiceProvider extends ServiceProvider
{
    /**
     * Indicates if loading of the provider is deferred.
     *
     * @var bool
     */
    protected $defer = true;

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->app->singleton('redis', function ($app) {
            return new Database($app['config']['database.redis']);
        });
    }

    /**
     * Get the services provided by the provider.
     *
     * @return array
     */
    public function provides()
    {
        return ['redis'];
    }
}

首先,这是一个延时服务(defer为true)。说明实际使用到时才实例化。redis对应一个Illuminate\Redis\Database实例。可以看到,它单独放入了Redis目录,可见这个东西确实是一个比较特殊的东西,不太好跟其它的东西并列。

针对Redis的配置,在config/database.php中用redis键配置:

    'redis' => [

        'cluster' => false,

        'default' => [
            'host'     => '127.0.0.1',
            'port'     => 6379,
            'database' => 0,
        ],
        'cache' => [
            'host'     => '127.0.0.1',
            'port'     => 6380,
            'database' => 0,
        ],
        'session' => [
            'host'     => '127.0.0.1',
            'port'     => 6381,
            'database' => 0,
        ],
    ]

很明显,可以设置多个配置。如果需要使用Redis作为缓存,需要修改config/cache.php:

'default' => env('CACHE_DRIVER', 'redis'),

'stores' => [
        'redis' => [
            'driver' => 'redis',
            'connection' => 'default',
        ],
],

其中的connection对应的default,就是database.php中redis的default,可以为cache专门指定一个链接(Redis实例)。

如果要把Session数据存储到Redis中,可以修改session.php文件:

'driver' => env('SESSION_DRIVER', 'redis'),
'connection' => 'session', // 对应Redis的配置

使用上,很简单,通过Redis进行:

Redis::get($key);
Redis::keys("*");
Redis::set($key,$value);
Redis::exists($key);

基本上,取决于你对Redis的熟悉程度了。

另外,PHP中的redis扩展会在全局空间中产生一个叫redis的类,这个名称和这里的Redis冲突,所以,如果安装redis扩展,你需要到app.php中将Redis别名改为其它的。

Laravel 配置详解

框架初始化流程:
实例化$app容器(Illuminate\Foundation\Application),然后调用$app->make(Illuminate\Contracts\Http\Kernel::class)产生一个$kernel,捕获请求信息生成$request对象($request = Illuminate\Http\Request::capture()),调用$kernel的handle()方法并把$request注入这个方法中。handle()方法在Illuminate\Foundation\Http\Kernel中,它的核心代码是$response = $this->sendRequestThroughRouter($request),sendRequestThroughRouter方法是进入点:

    protected function sendRequestThroughRouter($request)
    {
        $this->app->instance('request', $request);

        Facade::clearResolvedInstance('request');

        $this->bootstrap();

        return (new Pipeline($this->app))
                    ->send($request)
                    ->through($this->app->shouldSkipMiddleware() ? [] : $this->middleware)
                    ->then($this->dispatchToRouter());
    }

首先把请求对象注入容器,接下来就是bootstrap(),最后是请求经过中间件,分发到路由器。这里关注点在bootstrap()方法:

    public function bootstrap()
    {
        if (! $this->app->hasBeenBootstrapped()) {
            $this->app->bootstrapWith($this->bootstrappers());
        }
    }

这里的$this->bootstrappers()返回的是$kernel对象的$bootstrappers数组(可以在最终类覆盖),然后调用容器的bootstrapWith()方法,类文件是Illuminate\Foundation\Application:

    public function bootstrapWith(array $bootstrappers)
    {
        $this->hasBeenBootstrapped = true;

        foreach ($bootstrappers as $bootstrapper) {
            $this['events']->fire('bootstrapping: '.$bootstrapper, [$this]);

            $this->make($bootstrapper)->bootstrap($this);

            $this['events']->fire('bootstrapped: '.$bootstrapper, [$this]);
        }
    }

可见是根据传递进来的$bootstrappers,然后循环实例化,调用实例的bootstrap()方法而已,所以我们知道,$kernel的$bootstrappers数组的每个元素是一个字符串类名,这些类都必须有bootstrap()方法:

    protected $bootstrappers = [
        'Illuminate\Foundation\Bootstrap\DetectEnvironment',
        'Illuminate\Foundation\Bootstrap\LoadConfiguration',
        'Illuminate\Foundation\Bootstrap\ConfigureLogging',
        'Illuminate\Foundation\Bootstrap\HandleExceptions',
        'Illuminate\Foundation\Bootstrap\RegisterFacades',
        'Illuminate\Foundation\Bootstrap\RegisterProviders',
        'Illuminate\Foundation\Bootstrap\BootProviders',
    ];

在bootstrap前,容器已经初步初始化,经过bootstrap后的容器,才是真正初始化后的容器。 这个启动流程仅为热身。

##Illuminate\Foundation\Bootstrap\LoadConfiguration
<?php
namespace Illuminate\Foundation\Bootstrap;

class LoadConfiguration
{
    public function bootstrap(Application $app)
    {
        $items = [];

        if (file_exists($cached = $app->getCachedConfigPath())) {
            $items = require $cached;

            $loadedFromCache = true;
        }

        $app->instance('config', $config = new Repository($items));

        if (! isset($loadedFromCache)) {
            $this->loadConfigurationFiles($app, $config);
        }

        date_default_timezone_set($config['app.timezone']);

        mb_internal_encoding('UTF-8');
    }
    // ...
}

首先判断配置文件的缓存是否存在,如果存在就直接使用这个缓存。 $app->getCachedConfigPath():

public function getCachedConfigPath()
    {
        return $this->basePath().'/bootstrap/cache/config.php';
    }

就是一个文件路径,想必必定是所有配置文件的合并。如果这个文件不存在的,就遍历conf下的所有php结尾的文件,然后把配置文件require进$config对象,它是一个Illuminate\Config\Illuminate\Config实例(这个过程由loadConfigurationFiles实现),这个对象简单来说就是组装成一个如下的关联数组:

$config[
    'app' => [
        'timezone' => 'UTC',
    ],
    'auth' => [],
    'cache' => [],
]

如果要获取值,可以$config->get(‘app.timezone’),可以看到,可以很便利地使用点语法获取配置。以上提到,配置文件可以合并缓存,缓存起来的实际就是一个类似如上组织的大文件。这个文件可以使用如下命令生成:

php artisan config:cache

这个命令将会生成bootstrap/cache/config.php文件,它是所有文件的合并。一旦这个文件存在,那么框架在载入配置时就使用这个文件,同时也意味者如果修改了配置,需要重新生成这个文件。

所以,如果需要添加自己的配置,完全可以往config目录里面简单放入一个php文件,只要return一个关联数组即可。

获取配置值:

$app = config('app');
$timezone = $app->get('timezone');

$timezone = config('app.timezone');

$config = app('config');
$timezone = $config->get('app.timezone');

虽然可以把所有文件合并为一个大的文件缓存起来,但是每次请求都会解析一次配置,然后把配置装入一个对象,而配置几乎是不变的,并不需要每次请求都重复一次这个过程,可以使用apcu扩展来保存这个配置对象。apcu是一个共享内存方案,保存在其中的变量在多个进程中共享。

配置文件的加载是由Illuminate\Foundation\Bootstrap\LoadConfiguration来完成的,这个类是Illuminate\Foundation\Http\Kernel的中的$bootstrapper数组指定的:

    protected $bootstrappers = [
        'Illuminate\Foundation\Bootstrap\DetectEnvironment',
        'Illuminate\Foundation\Bootstrap\LoadConfiguration',
        'Illuminate\Foundation\Bootstrap\ConfigureLogging',
        'Illuminate\Foundation\Bootstrap\HandleExceptions',
        'Illuminate\Foundation\Bootstrap\RegisterFacades',
        'Illuminate\Foundation\Bootstrap\RegisterProviders',
        'Illuminate\Foundation\Bootstrap\BootProviders',
    ];

而我们可以配置的App\Http\Kernel类继承了Illuminate\Foundation\Http\Kernel类,所以只需要在App\Http\Kernel中覆盖这个变量就可以用自定义的类来载入配置:

    protected $bootstrappers = [
        'Illuminate\Foundation\Bootstrap\DetectEnvironment',
        'App\Http\Bootstrap\LoadConfiguration',  // 换掉
        'Illuminate\Foundation\Bootstrap\ConfigureLogging',
        'Illuminate\Foundation\Bootstrap\HandleExceptions',
        'Illuminate\Foundation\Bootstrap\RegisterFacades',
        'Illuminate\Foundation\Bootstrap\RegisterProviders',
        'Illuminate\Foundation\Bootstrap\BootProviders',
    ];

自定义的类继承Illuminate\Foundation\Bootstrap\LoadConfiguration,覆盖bootstrap()方法即可:

<?php

namespace App\Http\Bootstrap;

use Illuminate\Config\Repository;
use Illuminate\Contracts\Foundation\Application;
use Illuminate\Foundation\Bootstrap\LoadConfiguration as SystemLoadConfiguration;

class LoadConfiguration extends SystemLoadConfiguration
{
    public function bootstrap(Application $app)
    {
        $configKey = 'nal_config';
        $cacheConfig = env('CACHE_CONFIG', false);
        if ($cacheConfig) {
            if (extension_loaded('apcu')) {
                if (apcu_exists($configKey)) {
                    $this->load($app, apcu_fetch($configKey));
                } else {
                    apcu_store($configKey, $this->load($app, null, true));
                }
            } else {
                $this->load($app);
            }
        } else {
            if (extension_loaded('apcu') && apcu_exists($configKey)) {
                apcu_delete($configKey);
            }
            $this->load($app);
        }
    }

    public function load($app, $config = null)
    {
        if (!empty($config)) {
            $app->instance('config', $config);
        } else {
            $items = [];

            // First we will see if we have a cache configuration file. If we do, we'll load
            // the configuration items from that file so that it is very quick. Otherwise
            // we will need to spin through every configuration file and load them all.
            if (file_exists($cached = $app->getCachedConfigPath())) {
                $items = require $cached;

                $loadedFromCache = true;
            }

            $app->instance('config', $config = new Repository($items));

            // Next we will spin through all of the configuration files in the configuration
            // directory and load each one into the repository. This will make all of the
            // options available to the developer for use in various parts of this app.
            if (!isset($loadedFromCache)) {
                $this->loadConfigurationFiles($app, $config);
            }
        }

        date_default_timezone_set($config['app.timezone']);

        mb_internal_encoding('UTF-8');

        return $config;
    }
}

这样就可以实现配置共享了。一旦这么干,如果修改了配置,就需要重启或清空缓存才能让配置生效。

Laravel – Redis实例

<?php
 
namespace eBay\Console\Commands;
 
use Illuminate\Console\Command;
use DB;
use Schema;
 
class TitleRepeat extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'title:repeat {--list=}';
 
    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '';
 
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }
 
    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $config = app('config');
        
        // Redis扩展不存在
        if (!extension_loaded('redis')) {
            echo "Redis do not exists";
            return;
        }
        
        // 链接Redis
        $redis = new \Redis();
        $redisServer = $config->get('app.redisServer');
        $redisPort = $config->get('app.redisPort');
        
        if(!$redis->connect($redisServer, $redisPort)) {
            echo 'Connect Redis Host:'.$redisServer." Port:".$redisPort." Fail";
            return;
        }
        
        // 单个  或  全量
        $pids = $this->option('list');
        if(!empty($pids)) {
            $pids = explode(',', preg_replace('/\s+/', '', $pids));
            $mainSkus = DB::table("ebay_item_master")->whereIn('id',$pids)->get();
        } else {
            $mainSkus = DB::table('ebay_item_master')->where('if_primary','>',0)
                ->select(['sku','title1','title2','title3','title4','title5','title6'])->get();
        }
        if(count($mainSkus) < 1){
            echo 'No Products...';
            return;
        }
        
        foreach($mainSkus as $mainSku){
            $sku = trim($mainSku->sku);
             
            for($i = 1; $i <= 6; $i++) {
                $title = 'title'.$i;
                // 不理会空的
                if(empty($mainSku->$title)){ continue; }
                 
                // 计算哈希
                $titleValueHash = $this->getStringHash($mainSku->$title);
                $hashHold = $sku.'_'.$title;
                
                $key = strtolower($sku."_".$title);
                if($redis->exists($key)) {
                    $keyValue = $redis->get($key);

                    // 不相等 换了标题
                    if($keyValue !== $titleValueHash) {
                        $oldKey = 'sku_hash_'.$keyValue;
                        $newKey = 'sku_hash_'.$titleValueHash;
                         
                        // 需要更新 或 删除
                        if($redis->exists($oldKey)) {
                            $oldKeyValue = $redis->get($oldKey);
                            // 只包含一个值
                            if(empty($oldKeyValue) || ($oldKeyValue === $hashHold)){
                                $redis->delete($oldKey);
                            } else {
                                $nowNewValue = str_replace([$hashHold.',',','.$hashHold],'',$oldKeyValue);
                                if(!empty($nowNewValue)) {
                                    $redis->set($oldKey,$nowNewValue);
                                } else {
                                    $redis->delete($oldKey);
                                }
                            }
                        }
                         
                        // 需要更新 或 添加
                        if($redis->exists($newKey)) {
                            $newKeyValue = $redis->get($newKey);
                            // 只包含一个值
                            if(empty($newKeyValue)){
                                $redis->set($newKey,$hashHold);
                            } else {
                                if($newKeyValue !== $hashHold){
                                    $newHashHold = str_replace([$hashHold.',', ','.$hashHold],'',$newKeyValue);
                                    if(!empty($newHashHold)) {
                                        $redis->set($newKey,$newHashHold.','.$hashHold);
                                    } else {
                                        $redis->set($newKey,$hashHold);
                                    }
                                }
                            }
                        } else {
                            $redis->set($newKey,$hashHold);
                        }
                        // 更新
                        $redis->set($key,$titleValueHash);
                    } else {
                        // 新旧Key对应的值相等,判断值对应的Key是否存在
                        $noExistKey = 'sku_hash_'.$keyValue;
                        if(!$redis->exists($noExistKey)){
                            $redis->set($noExistKey,$hashHold);
                        }
                    }
                } else {
                    $newKey = 'sku_hash_'.$titleValueHash;
                    if($redis->exists($newKey)) {
                        $newKeyValue = $redis->get($newKey);
                        // 只包含一个值
                        if(empty($newKeyValue)){
                            $redis->set($newKey,$hashHold);
                        } else {
                            if($newKeyValue !== $hashHold){
                                $newHashHold = str_replace([$hashHold.',', ','.$hashHold],'',$newKeyValue);
                                if(!empty($newHashHold)) {
                                    $redis->set($newKey,$newHashHold.','.$hashHold);
                                } else {
                                    $redis->set($newKey,$hashHold);
                                }
                            }
                        }
                    } else {
                        $redis->set($newKey,$hashHold);
                    }
                    
                    $redis->set($key,$titleValueHash);
                }
            } 
        }
         
    }
     
    public function getStringHash($str) {
        $titleValue = preg_split('/\s+/',trim($str));
        sort($titleValue);
        $titleValueHash = md5(implode('',$titleValue));
         
        return $titleValueHash;
    }
    
}

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');
        });
    }
}

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