seafood/app/Console/Commands/DayGrandSale...

<?php 
namespace App\Console\Commands;

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

    protected $signature = 'DayGrandSalerTotal';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '分销售每日汇总投放，成本，毛利等数据';


    public function handle()
    {
        $this->DayGrandSalerTotal();
    }

    
    public function DayGrandSalerTotal(){
        $_start = '2019-09-04';
        $_end = date('Y-m-d');
        
        $idate = date('Y-m-d', strtotime('-1 day'));
        //查看是否已有数据
        $if_e = DB::table('day_grand_saler_total')->where('idate', $idate)->first();
        if(!empty($if_e)){
            echo "\n日期：".$idate." 已存在";
            return false;
        }
        $team_ids = $this->getTeams();
        //获取各个团队销售id
        $salerIds = $this->getTeamSalers($team_ids);
        foreach($salerIds as $team_id=>$saler_ids){
            //团队总投入
            $team_throw_cost = CustTotal::where('is_del',0)->where('team_id',$team_id)->where('dtime','<',$_end)->where('dtime','>=',$_start)->sum('total_cost');
            //团队销售历史总加粉
            $wx_fan_total = CustDetail::whereIn('admin_id', $saler_ids)->where('is_del', 0)->where('dtime','<',$_end)->where('dtime','>=',$_start)->sum('fan_add');
            foreach($saler_ids as $admin_id){
                $data = array();
                $data['idate'] = $idate;
                $data['admin_id'] = $admin_id;
                $data['team_id'] = $team_id;
                //总加粉
                $data['fan_count'] = CustDetail::where('is_del',0)->where('admin_id', $admin_id)->where('dtime','<',$_end)->where('dtime','>=',$_start)->sum('fan_add');
                //预估总投入                   
                $data['throw_cost'] = $wx_fan_total>0 ? round($data['fan_count']/$wx_fan_total * $team_throw_cost, 2) : 0; 

                //订单信息
                $order = Order::select(DB::raw('sum(cost) as goods_cost, sum(freight_cost) as freight_cost, sum(aftersale_fee) as aftersale_cost, count(1) as order_count, sum(receivedAmount) as order_amount, count(distinct(receiverMobile)) as cust_count, sum(refund_price) as refund_fee'))->where('is_del',0)->where('admin_id', $admin_id)->where('createTime','<',$_end)->where('createTime','>=',$_start)->first();
                $data['goods_cost'] = $order->goods_cost;
                $data['freight_cost'] = $order->freight_cost;
                $data['aftersale_cost'] = $order->aftersale_cost;
                $data['refund_fee'] = $order->refund_fee;
                $data['order_count'] = $order->order_count;
                $data['order_amount'] = $order->order_amount;
                $data['cust_count'] = $order->cust_count;
            
                //毛利 = 总销售额-总商品成本-总运费-总售后
                $data['profit'] = $order->order_amount - $order->goods_cost - $order->freight_cost - $order->aftersale_cost - $data['throw_cost'] + $order->refund_fee;
                //总新粉单数            
                $new_order = Order::select(DB::raw('count(1) as order_count'))->leftJoin('customers as cu','cu.phone', '=', 'order.receiverMobile')->whereRaw('left(order.createTime, 10) = cu.fanTime')->where('order.is_del', 0)->where('order.admin_id', $admin_id)->where('order.createTime','<',$_end)->where('order.createTime','>=',$_start)->first();
                $data['new_order_count'] = $new_order->order_count;
                //总老粉单数
                $data['old_order_count'] = $data['order_count'] - $data['new_order_count'];
                //总复购订单
                $data['fugou_order_count'] = $data['order_count'] - $data['cust_count'];

                //7 - 60 roi汇总
                $date = date('Y-m-d', strtotime('-7 day'));
                $data['cost7'] = DB::table('roi_saler_total')->where('type', 1)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('cost');
                $data['order_count7'] = DB::table('roi_saler_total')->where('type', 1)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_count');
                $data['order_amount7'] = DB::table('roi_saler_total')->where('type', 1)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_amount');

                $date = date('Y-m-d', strtotime('-15 day'));
                $data['cost15'] = DB::table('roi_saler_total')->where('type', 2)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('cost');
                $data['order_count15'] = DB::table('roi_saler_total')->where('type', 2)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_count');
                $data['order_amount15'] = DB::table('roi_saler_total')->where('type', 2)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_amount');

                $date = date('Y-m-d', strtotime('-30 day'));
                $data['cost30'] = DB::table('roi_saler_total')->where('type', 3)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('cost');
                $data['order_count30'] = DB::table('roi_saler_total')->where('type', 3)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_count');
                $data['order_amount30'] = DB::table('roi_saler_total')->where('type', 3)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_amount');

                $date = date('Y-m-d', strtotime('-45 day'));
                $data['cost45'] = DB::table('roi_saler_total')->where('type', 4)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('cost');
                $data['order_count45'] = DB::table('roi_saler_total')->where('type', 4)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_count');
                $data['order_amount45'] = DB::table('roi_saler_total')->where('type', 4)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_amount');

                $date = date('Y-m-d', strtotime('-60 day'));
                $data['cost60'] = DB::table('roi_saler_total')->where('type', 5)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('cost');
                $data['order_count60'] = DB::table('roi_saler_total')->where('type', 5)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_count');
                $data['order_amount60'] = DB::table('roi_saler_total')->where('type', 5)->where('admin_id', $admin_id)->where('ad_time', '<', $date)->sum('order_amount');
                $res = DB::table('day_grand_saler_total')->insert($data);
                echo "\n日期：".$idate." 团队：".$team_id." 销售：".$admin_id." 插入结果：".$res;
            }

        }
    }

    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;
    }

}