<?php 
namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;

class DayGrandRoiHistory extends Command {

    protected $signature = 'DayGrandRoiHistory';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每日汇总投放，成本，毛利等数据';


    public function handle()
    {
        $this->DayGrandRoiHistory();
    }

    
    public function DayGrandRoiHistory(){
        $result = DB::table('day_grand_total')->select('id','idate')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>$v){
            //7 - 60 roi汇总
            $s7 = $v['idate']. ' -7 day';
            $date = date('Y-m-d', strtotime($s7));
            $data['cost7'] = DB::table('roi_total')->where('type', 1)->where('ad_time', '<=', $date)->sum('cost');
            $data['order_count7'] = DB::table('roi_total')->where('type', 1)->where('ad_time', '<=', $date)->sum('order_count');
            $data['order_amount7'] = DB::table('roi_total')->where('type', 1)->where('ad_time', '<=', $date)->sum('order_amount');

            $s15 = $v['idate']. ' -15 day';
            $date = date('Y-m-d', strtotime($s15));
            $data['cost15'] = DB::table('roi_total')->where('type', 2)->where('ad_time', '<=', $date)->sum('cost');
            $data['order_count15'] = DB::table('roi_total')->where('type', 2)->where('ad_time', '<=', $date)->sum('order_count');
            $data['order_amount15'] = DB::table('roi_total')->where('type', 2)->where('ad_time', '<=', $date)->sum('order_amount');

            $s30 = $v['idate']. ' -30 day';
            $date = date('Y-m-d', strtotime($s30));
            $data['cost30'] = DB::table('roi_total')->where('type', 3)->where('ad_time', '<=', $date)->sum('cost');
            $data['order_count30'] = DB::table('roi_total')->where('type', 3)->where('ad_time', '<=', $date)->sum('order_count');
            $data['order_amount30'] = DB::table('roi_total')->where('type', 3)->where('ad_time', '<=', $date)->sum('order_amount');

            $s45 = $v['idate']. ' -45 day';
            $date = date('Y-m-d', strtotime($s45));
            $data['cost45'] = DB::table('roi_total')->where('type', 4)->where('ad_time', '<=', $date)->sum('cost');
            $data['order_count45'] = DB::table('roi_total')->where('type', 4)->where('ad_time', '<=', $date)->sum('order_count');
            $data['order_amount45'] = DB::table('roi_total')->where('type', 4)->where('ad_time', '<=', $date)->sum('order_amount');

            $s60 = $v['idate']. ' -60 day';
            $date = date('Y-m-d', strtotime($s60));
            $data['cost60'] = DB::table('roi_total')->where('type', 5)->where('ad_time', '<=', $date)->sum('cost');
            $data['order_count60'] = DB::table('roi_total')->where('type', 5)->where('ad_time', '<=', $date)->sum('order_count');
            $data['order_amount60'] = DB::table('roi_total')->where('type', 5)->where('ad_time', '<=', $date)->sum('order_amount');

            $res = DB::table('day_grand_total')->where('id', $v['id'])->update($data);
            echo "\n日期：".$v['idate']." 更新结果：".$res;
        }
        
    }

}