seafood/app/Console/Commands/RoiTotal.php

<?php 
namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;
use App\CustTotal;
use App\CustDetail;
class RoiTotal extends Command {

    protected $signature = 'RoiTotal';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '汇总roi';


    public function handle()
    {
        $this->RoiTotal();
    }
    public function RoiTotal(){
        $timeArr = array(
            1 => '-7 day',
            2 => '-15 day',
            3 => '-30 day',
            4 => '-45 day',
            5 => '-60 day',
        );
        for($i=1;$i<6;$i++){
            $stime = date('Y-m-d', strtotime($timeArr[$i]));   
            $etime = $date = date('Y-m-d');
            //计算date i天内的roi    
            $result = CustTotal::select(DB::raw('sum(total_cost) as total_cost, sum(total_fan_add) as total_fan_add'))->where('is_del',0)->where('dtime', $stime)->first();

            $result = json_decode(json_encode($result), true);           
            
            $data = array();
            $data['ad_time'] = $stime;  
            $data['type'] = $i;            
            //新粉收入
            //当日加粉
            $phones = DB::table('customers')->where('fanTime', $stime)->lists('phone');
            #当日加粉15日订单总计：
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count, count(distinct(receiverMobile)) as cust_count'))->whereIn('receiverMobile', $phones)->where('createTime', '>=', $stime)->where('createTime', '<', $etime)->where('is_del', 0)->first();
            #当日新粉成单：            
            $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $stime)->where('createTime','<=', $stime.' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->first();
           
            // 1.当日新粉成单数
            $data['new_fan_order_count'] = $new_order->order_count;
            // 2.当日新粉成交额 
            $data['new_fan_order_amount'] = $new_order->order_amount;
            // 3.当日粉丝总成交额
            $data['order_amount'] = $order->order_amount;
            // 15日总单数
            $data['order_count'] = $order->order_count;

            $data['cust_count'] = $order->cust_count;
            $data['fan_count'] = CustDetail::where('dtime', $stime)->where('is_del', 0)->sum('fan_add');
            $data['gzh_count'] = $result['total_fan_add'];
            $data['cost'] = $result['total_cost'];
            //插入数据
            $res = $this->insertData($data);
            
        }
  
    }

    /**
     * @param idate:统计日期 rate：成单率 date:昨日
     *
     */
    public function insertData($data){        
        return DB::table('roi_total')->insert($data);
    }

}