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

<?php 
namespace App\Console\Commands;

use Illuminate\Console\Command;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Input\InputArgument;
use TopClient;
use DB;
class OrderRateByDay extends Command {

    protected $signature = 'OrderRateByDay';

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


    public function handle()
    {
        $this->OrderRateByDay();
    }
    public function OrderRateByDay(){
        #昨天日期：
        $date = date('Y-m-d', strtotime('-1 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;
    }

}