seafood/app/Console/Commands/OrderRateByD...

<?php 
namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;
class OrderRateByDayHistory extends Command {

    protected $signature = 'OrderRateByDayHistory';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '综合成单率日志脚本';


    public function handle()
    {
        $this->OrderRateByDayHistory();
    }
    public function OrderRateByDayHistory(){
        for($i=70; $i>1; $i--){
            #昨天日期：
            $date = date('Y-m-d', strtotime('-'.$i.' day'));
            //第一步： 统计昨日数据
            #昨日加粉 and 昨日成单 
            $phones = DB::table('customers')->where('fanTime', $date)->lists('phone');
            $order_count = DB::table('order')->whereIn('receiverMobile', $phones)->where('createTime','>=',$date)->where('createTime','<=',$date.' 23:59:59')->where('is_del', 0)->count();
            #昨日加粉
            $fan_add = DB::table('cust_day_detail')->where('is_del', 0)->where('dtime', $date)->sum('fan_add');
            #计算昨日成单率
            $rate = $fan_add>0 ? round($order_count / $fan_add, 4) * 100  : 0;
            //存入数据表
            $result = $this->updateData($date, $rate, $date);
            echo "\nDate:".$date." fan：".$fan_add." order_count:".$order_count." rate:".$rate." 处理结果：".$result;
            
            //第二布：统计往期数据
            #查日期列表
            $dates = DB::table('cust_day_remain')->select('idate')->where('idate','<', $date)->groupBy('idate')->orderBy('idate', 'desc')->get();
            if(!empty($dates)){
                $dates = json_decode(json_encode($dates), true);
                foreach($dates as $k=>$v){
                    #当前日期加粉 and 昨日成单 
                    $phones = DB::table('customers')->where('fanTime', $v['idate'])->lists('phone');
                    $order_count = DB::table('order')->whereIn('receiverMobile', $phones)->where('createTime','>=',$date)->where('createTime','<=',$date.' 23:59:59')->where('is_del', 0)->count();
                    #当前日期加粉
                    $fan_add = DB::table('cust_day_detail')->where('is_del', 0)->where('dtime', $v['idate'])->sum('fan_add');
                    #计算当前日期加粉在昨日成单率
                    $rate = $fan_add>0 ? round($order_count / $fan_add, 4) * 100  : 0;
                    //存入数据表
                    $result = $this->updateData($v['idate'], $rate, $date);
                    echo "\nDate:".$v['idate']." fan：".$fan_add." order_count:".$order_count." rate:".$rate." 处理结果：".$result;
                }
            }
        }
    }

    /**
     * @param idate:统计日期 rate：成单率 date:昨日
     *
     */
    public function updateData($idate, $rate, $date){
        #当前月
        $month = date('Y-m', strtotime($date));
        #当月1号
        $month_first = date('Y-m-01', strtotime($date));
        $data = array();
        #如果是昨天的数据（或昨天是当月一号）直接插入
        if($idate == $date || $date == $month_first){
            $data['idate'] = $idate;
            $data['month_time'] = $month;
            $data['order_rate_data'] = json_encode([0=>['date'=>$date, 'rate'=>$rate]]);
            $res = DB::table('cust_day_remain')->insert($data);
        }else{
            //往期成单
            $odata = DB::table('cust_day_remain')->where('idate', $idate)->where('month_time', $month)->first();
            $order_rate_data = json_decode($odata->order_rate_data, true);
            $order_rate_data[] = ['date'=>$date, 'rate'=>$rate];
            $data['order_rate_data'] = json_encode($order_rate_data);
            $res = DB::table('cust_day_remain')->where('idate', $idate)->where('month_time', $month)->update($data);
        }

        return $res;
    }

}