seafood/app/Console/Commands/TeamOrderRat...

<?php 
namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;
use App\Admin;
class TeamOrderRateByDayHistory extends Command {

    protected $signature = 'TeamOrderRateByDayHistory';

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


    public function handle()
    {
        $this->TeamOrderRateByDayHistory();
    }
    public function TeamOrderRateByDayHistory(){
        $team_ids = Admin::getTeams();
        foreach($team_ids as $team_id){
            #团队成员：
            $saler_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
            for($i=78; $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')->where('team_id', $team_id)->whereIn('receiverMobile', $phones)->where('createTime','>=',$date)->where('createTime','<=',$date.' 23:59:59')->where('is_del', 0)->count();
                #昨日加粉
                $fan_add = DB::table('cust_day_detail')->whereIn('admin_id', $saler_ids)->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, $team_id);
                echo "\nDate:".$date." fan：".$fan_add." order_count:".$order_count." rate:".$rate." 处理结果：".$result;
                
                //第二布：统计往期数据
                #查日期列表
                $dates = DB::table('team_cust_day_remain')->select('idate')->where('team_id', $team_id)->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')->where('team_id', $team_id)->whereIn('receiverMobile', $phones)->where('createTime','>=',$date)->where('createTime','<=',$date.' 23:59:59')->where('is_del', 0)->count();
                        #当前日期加粉
                        $fan_add = DB::table('cust_day_detail')->whereIn('admin_id', $saler_ids)->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, $team_id);
                        echo "\nDate:".$v['idate']." fan：".$fan_add." order_count:".$order_count." rate:".$rate." 处理结果：".$result;
                    }
                }
            }
        }
    }

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

        return $res;
    }

}