seafood/app/Console/Commands/RoiSalerTota...

<?php 
namespace App\Console\Commands;

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

    protected $signature = 'RoiSalerTotalHistory';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Roi销售汇总历史';


    public function handle()
    {
        $this->RoiSalerTotalHistory();
    }
    public function RoiSalerTotalHistory(){
        $timeArr = array(
            1 => '7',
            2 => '15',
            3 => '30',
            4 => '45',
            5 => '60',
        );
        //获取团队
        $team_ids = $this->getTeams();
        //获取各个团队销售id
        $salerIds = $this->getTeamSalers($team_ids);
        for($i=1;$i<6;$i++){
            $j_min = $timeArr[$i];
            for($j=72; $j>=$j_min; $j--){
                $stime = date('Y-m-d', strtotime('-'.$j.' day'));
                $e = $j-$j_min; 
                $etime = $date = date('Y-m-d', strtotime('-'.$e.' day'));

               //计算date i天内的roi    
                $custTotal = CustTotal::select('total_cost', 'total_fan_add', 'team_id')->whereIn('team_id', $team_ids)->where('is_del',0)->where('dtime', $stime)->get();
                $custTotal = json_decode(json_encode($custTotal), true);  

                foreach($custTotal as $k=>$v){
                    $saler_ids = $salerIds[$v['team_id']];
                    //团队当日销售总加粉
                    $wx_fan_total = CustDetail::whereIn('admin_id', $saler_ids)->where('is_del', 0)->where('dtime', $stime)->sum('fan_add');
                    //团队销售上报加粉
                    $result = CustDetail::select('fan_add', 'admin_id')->whereIn('admin_id', $saler_ids)->where('is_del', 0)->where('dtime', $stime)->get();
                    $result = json_decode(json_encode($result), true);
                    foreach($result as $key=>$detail){
                        $data = array();
                        $data['cost'] = round($detail['fan_add']/$wx_fan_total * $v['total_cost'], 2);
                        $data['gzh_count'] = round($detail['fan_add']/$wx_fan_total * $v['total_fan_add']);

                        $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)->where('admin_id', $detail['admin_id'])->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)->where('admin_id', $detail['admin_id'])->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'] = $detail['fan_add'];
                        $data['admin_id'] = $detail['admin_id'];
                        //插入数据
                        $res = $this->insertData($data);
                    }
                }
                
            }
        }
    }

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

    public function getTeamSalers($team_ids){
        $data = array();
        foreach($team_ids as $team_id){
            $data[$team_id] = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }
        return $data;
    }

    public function getTeams(){
        $data = DB::table('teams')->where('type', 1)->lists('id');
        return $data;
    }

}