';
        //print_r($new_data);print_r($columns);exit;
        return view('statistics/orderRateList', ['result' =>$new_data, 'columns'=>$columns, 'stime'=>$stime, 'etime'=>$etime, 'team_id'=>$team_id, 'teamlist'=>$teamList]);

    }

    /**
     * 团队成单率梯形图
     */
    public function orderTeamRateList($stime, $etime, $team_id){       
        $_day = 30;
        if($stime == '' && $etime == ''){
            $stime = date("Y-m-d", strtotime('-32 day'));
            $etime = date("Y-m-d", strtotime('-2 day'));
        }else{
            if($stime){
                $_day = floor( (time() - strtotime($stime)) / 86400) - 2; //距昨天天数
            }
        }
        #统计天数
        $month = date('d')<=2 ? date('Y-m', strtotime('-1 month')) : date('Y-m'); //当前月
        $day_count = DB::table('team_cust_day_remain')->where('team_id', $team_id)->where('month_time', $month)->where(function($query) use($stime, $etime){
            if($stime) $query->where('idate','>=', $stime);
            if($etime) $query->where('idate','<=', $etime);
        })->count();

        $result = DB::table('team_cust_day_remain')->where('team_id', $team_id)->where(function($query) use($stime, $etime){
            if($stime) $query->where('idate','>=', $stime);
            if($etime) $query->where('idate','<=', $etime);
        })->orderBy('idate', 'asc')->orderBy('month_time', 'asc')->get();

        if($stime)
        #获取列
        $columns = array();
        $i_max = 31;
        for($i=0; $i<$i_max; $i++){
            $columns[] = $i;
        }

        if($_day >= 45){
            $columns[] = 45;
        }
        if($_day >= 60){
            $columns[] = 60;
        }
        if($_day >= 75){
            $columns[] = 75;
        }
        if($_day >= 90){
            $columns[] = 90;
        }


        #数据整合
        $data = array();
        foreach($result as $k=>$v){
            $key = $v->idate;
            $rate_data = json_decode($v->order_rate_data, true);
            $data[$key] = isset($data[$key]) ? array_merge($data[$key], $rate_data) : $rate_data;
        }

        #团队成员：
        $saler_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');

        #数据格式化
        $new_data = array();
        foreach($data as $k=>$v){
            //当日加粉数
            $fan_add = DB::table('cust_day_detail')->whereIn('admin_id', $saler_ids)->where('dtime', $k)->where('is_del',0)->sum('fan_add');
            $new_data[$k]['fan_add'] = $fan_add;
            foreach($columns as $val){
                $n = $val;
                $new_data[$k]['rate'][] = isset($v[$n]['rate']) ? $v[$n]['rate'].'%' : '';
            }
        }
        //echo '
';
        //print_r($new_data);print_r($columns);exit;
        $self_role = session('role_name');
        $teamList = DB::table('teams')->select('id', 'name')->where(function($query) use($self_role, $team_id){
            if($self_role != '超级管理员' && $self_role != '售后管理员') $query->where('id', $team_id);
        })->get();
        $teamList = json_decode(json_encode($teamList), true);
        return ['result' =>$new_data, 'columns'=>$columns, 'stime'=>$stime, 'etime'=>$etime, 'team_id'=>$team_id, 'teamlist'=>$teamList];

    }

    /**
     * orderSaleRank 实时销售数据展榜
     */
    public function orderSaleRank(Request $request){
        header('Location:https://datav.aliyuncs.com/share/c936e72d66c7b5900238cf51cbd31884');
        exit;
    }

    /**
     * orderDistrict 实时订单地域分布
     */
    public function orderDistrict(Request $request){
        header('Location:https://datav.aliyuncs.com/share/423b408234ea48d49a2a4ee7f33fa4e4');
        exit;
    }

    /**
     * 地区roi
     */
    public function districtRoi(Request $request){
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }       

        $ret = AdCost::select('id')->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('ad_time')->groupBy('city')->get();
        $ret = json_decode(json_encode($ret), true);

        $count = count($ret);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = AdCost::select(DB::raw('sum(cost) as total_cost, sum(conversion_times) as conversion_times, city, ad_time'))->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('ad_time')->groupBy('city')->orderBy('ad_time', 'desc')->offset($offset)->limit($pageSize)->get();

        $result = json_decode(json_encode($result), true);

        foreach($result as $k=>&$v){
            //新粉收入
            //当日加粉
            $city_name = str_replace('市', '', $v['city']);
            $phones = DB::table('customers')->where('fanTime', $v['ad_time'])->where('receiverCity','like', '%'.$city_name.'%')->lists('phone');
            #当日加粉订单总计：
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->first();
            #当日新粉成单：            
            $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['ad_time'])->where('createTime','<=', $v['ad_time'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->first();
            // 1.当日新粉成单数
            $v['new_order_count'] = $new_order->order_count;
            // 2.当日新粉成交额 
            $v['new_order_amount'] = $new_order->order_amount;
            // 3.当日粉丝总成交额
            $v['order_amount'] = $order->order_amount;
            // 总单数
            $v['order_count'] = $order->order_count;
            //新粉roi
            $v['new_roi'] = $v['total_cost']>0 ? round($v['new_order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['total_cost']>0 ? round($v['order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            $v['new_fan_count'] = count($phones);

        }

        //总计
        $total_data = [
            'cost' => 0,
            'fan_count' => 0,
            'order_count' => 0,
            'order_amount' => 0,
            'roi' => 0,
            'conversion_times' => 0,
            'new_order_count' => 0,
            'new_order_amount' => 0,
            'new_roi' => 0,
        ];

        //投入
        $adcost = AdCost::select(DB::raw('sum(cost) as total_cost, sum(conversion_times) as conversion_times'))->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->first();
        $total_data['cost'] = $adcost->total_cost;
        $total_data['conversion_times'] = $adcost->conversion_times;
        //总新粉
        $phones = DB::table('customers')->where('fanTime','>=', '2019-09-01')->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('fanTime', '>=', $stime);
            if($etime) $query->where('fanTime', '<=', $etime);
            if($city) $query->where('receiverCity', 'like', '%'.$city.'%');
        })->lists('phone');
        $total_data['fan_count'] = !empty($phones) ? count($phones) : 0;
        //总收入
        $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->first();
        $total_data['order_count'] = $order->order_count;
        $total_data['order_amount'] = $order->order_amount;
        $total_data['roi'] = $total_data['cost']>0 ? round($total_data['order_amount'] / $total_data['cost'], 4) * 100 .'%' : '';
        #总新粉成单：            
        $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, 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)->whereIn('receiverMobile', $phones)->first();
        $total_data['new_order_count'] = $new_order->order_count;
        $total_data['new_order_amount'] = $new_order->order_amount;
        $total_data['new_roi'] = $total_data['cost']>0 ? round($total_data['new_order_amount'] / $total_data['cost'], 4) * 100 .'%' : '';

        return view('statistics/districtRoi', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                          
            'stime'             =>$stime,
            'etime'             =>$etime,          
            'city'             =>$city,          
            'total_data'             =>$total_data,          
            ]);
    }

    /**
     * 导出地域roi报表
     */
    public function districtRoi_export(Request $request){
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }       

        $result = AdCost::select(DB::raw('sum(cost) as total_cost, sum(conversion_times) as conversion_times, city, ad_time'))->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('ad_time')->groupBy('city')->orderBy('ad_time', 'desc')->get();

        $result = json_decode(json_encode($result), true);

        foreach($result as $k=>&$v){
            //新粉收入
            //当日加粉
            $city_name = str_replace('市', '', $v['city']);
            $phones = DB::table('customers')->where('fanTime', $v['ad_time'])->where('receiverCity','like', '%'.$city_name.'%')->lists('phone');
            #当日加粉订单总计：
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->first();
            #当日新粉成单：            
            $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['ad_time'])->where('createTime','<=', $v['ad_time'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->first();
            // 1.当日新粉成单数
            $v['new_order_count'] = $new_order->order_count;
            // 2.当日新粉成交额 
            $v['new_order_amount'] = $new_order->order_amount;
            // 3.当日粉丝总成交额
            $v['order_amount'] = $order->order_amount;
            // 总单数
            $v['order_count'] = $order->order_count;
            //新粉roi
            $v['new_roi'] = $v['total_cost']>0 ? round($v['new_order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['total_cost']>0 ? round($v['order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            $v['new_fan_count'] = count($phones);

        }

        //总计
        $total_data = [
            'cost' => 0,
            'fan_count' => 0,
            'order_count' => 0,
            'order_amount' => 0,
            'roi' => 0,
            'conversion_times' => 0,
            'new_order_count' => 0,
            'new_order_amount' => 0,
            'new_roi' => 0,
        ];

        //投入
        $adcost = AdCost::select(DB::raw('sum(cost) as total_cost, sum(conversion_times) as conversion_times'))->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->first();
        $total_data['cost'] = $adcost->total_cost;
        $total_data['conversion_times'] = $adcost->conversion_times;
        //总新粉
        $phones = DB::table('customers')->where('fanTime','>=', '2019-09-01')->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('fanTime', '>=', $stime);
            if($etime) $query->where('fanTime', '<=', $etime);
            if($city) $query->where('receiverCity', 'like', '%'.$city.'%');
        })->lists('phone');
        $total_data['fan_count'] = !empty($phones) ? count($phones) : 0;
        //总收入
        $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->first();
        $total_data['order_count'] = $order->order_count;
        $total_data['order_amount'] = $order->order_amount;
        $total_data['roi'] = $total_data['cost']>0 ? round($total_data['order_amount'] / $total_data['cost'], 4) * 100 .'%' : '';

        #总新粉成单：            
        $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, 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)->whereIn('receiverMobile', $phones)->first();
        $total_data['new_order_count'] = $new_order->order_count;
        $total_data['new_order_amount'] = $new_order->order_amount;
        $total_data['new_roi'] = $total_data['cost']>0 ? round($total_data['new_order_amount'] / $total_data['cost'], 4) * 100 .'%' : '';

        $indexKey = ['ad_time','city','total_cost','conversion_times','new_fan_count','new_order_count','new_order_amount','new_roi','order_count','order_amount','total_roi'];
        $title = ['日期', '城市', '投放成本', '加粉数', '新粉数量', '新粉成单数', '新粉收入', '新粉ROI', '累计成单数', '累计收入', '累计ROI'];
        $filename = 'district_roi_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 团队订单财务明细
     */
    public function teamFinanceList(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $aftersale = (int)$request->input('aftersale'); //售后

        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;

        $count = Order::where(function($query) use($team_id, $stime, $etime, $aftersale){
            if($team_id) $query->where('team_id', $team_id);
            if($stime && $aftersale == 0) $query->where('createTime', '>=', $stime);
            if($etime && $aftersale == 0) $query->where('createTime', '<=', $etime. ' 23:59:59');
            if($stime && $aftersale == 1) $query->where('aftersale_time', '>=', $stime);
            if($etime && $aftersale == 1) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');
            if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
        })->where('is_del', 0)->count();
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $teams = DB::table('teams')->lists('name', 'id');
        $resultQuery = Order::select('team_id', 'createTime', 'id', 'receivedAmount', 'aftersale_fee', 'refund_price', 'cost', 'freight_cost', 'payment_type', 'aftersale_time')->where(function($query) use($team_id, $stime, $etime, $aftersale){
            if($team_id) $query->where('team_id', $team_id);
            if($stime && $aftersale == 0) $query->where('createTime', '>=', $stime);
            if($etime && $aftersale == 0) $query->where('createTime', '<=', $etime. ' 23:59:59');
            if($stime && $aftersale == 1) $query->where('aftersale_time', '>=', $stime);
            if($etime && $aftersale == 1) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');
            if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
        })->where('is_del', 0)->orderBy('team_id', 'asc');
        if($aftersale == 1){
            $result = $resultQuery->orderBy('aftersale_time','desc')->offset($offset)->limit($pageSize)->get();
        }else{
            $result = $resultQuery->orderBy('createTime','desc')->offset($offset)->limit($pageSize)->get();
        }
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付'];
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';;
            $v['true_amount'] = $v['receivedAmount'] - $v['refund_price'] - $v['aftersale_fee'];
            $v['payment_type'] = $payArr[$v['payment_type']];
            $v['ctime'] = $aftersale == 1 ? $v['aftersale_time'] : $v['createTime'];
        }

        $teamList = DB::table('teams')->select('id', 'name')->get();
        $teamList = json_decode(json_encode($teamList), true);

        return view('/statistics/teamFinanceList',[
            'result'   =>   $result,
            'team_id'   =>   $team_id,
            'stime'   =>   $stime,
            'etime'   =>   $etime,
            'page'   =>   $page,
            'count'   =>   $count,
            'pages'   =>   $pages,
            'teamlist'   =>   $teamList,
            'aftersale'   =>   $aftersale,
        ]);
    } 

    /**
     * 团队订单财务汇总列表
     */
    public function teamFinanceTotal(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $aftersale = (int)$request->input('aftersale');

        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }
        $offset = ($page-1) * $pageSize;

        if($aftersale == 1){
            $countQuery = Order::select(DB::raw('left(aftersale_time, 10) as ctime, team_id'));
        }else{
            $countQuery = Order::select(DB::raw('left(createTime, 10) as ctime, team_id'));
        }
        $count = $countQuery->where(function($query) use($team_id, $stime, $etime, $aftersale){
            if($team_id) $query->where('team_id', $team_id);
            if($stime && $aftersale == 0) $query->where('createTime', '>=', $stime);
            if($etime && $aftersale == 0) $query->where('createTime', '<=', $etime. ' 23:59:59');
            if($stime && $aftersale == 1) $query->where('aftersale_time', '>=', $stime);
            if($etime && $aftersale == 1) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');
            if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
        })->where('is_del', 0)->groupBy('team_id')->groupBy('ctime')->get();
        $count = count($count);

        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $teams = DB::table('teams')->lists('name', 'id');
        if($aftersale == 1){
            $result = Order::select(DB::raw('left(aftersale_time, 10) as ctime, team_id, count(1) as order_count, sum(receivedAmount) as receivedAmount, sum(aftersale_fee) as aftersale_fee, sum(refund_price) as refund_price, sum(cost) as cost, sum(freight_cost) as freight_cost, sum(if(payment_type=1,receivedAmount,null)) as wx_money'))->where(function($query) use($team_id, $stime, $etime, $aftersale){
                if($team_id) $query->where('team_id', $team_id);
                if($stime) $query->where('aftersale_time', '>=', $stime);
                if($etime) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');
                if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
            })->where('is_del', 0)->groupBy('team_id')->groupBy('ctime')->orderBy('team_id', 'asc')->orderBy('aftersale_time','desc')->offset($offset)->limit($pageSize)->get();
        }else{
            $result = Order::select(DB::raw('left(createTime, 10) as ctime, team_id, count(1) as order_count, sum(receivedAmount) as receivedAmount, sum(aftersale_fee) as aftersale_fee, sum(refund_price) as refund_price, sum(cost) as cost, sum(freight_cost) as freight_cost, sum(if(payment_type=1,receivedAmount,null)) as wx_money'))->where(function($query) use($team_id, $stime, $etime, $aftersale){
                if($team_id) $query->where('team_id', $team_id);
                if($stime) $query->where('createTime', '>=', $stime);
                if($etime) $query->where('createTime', '<=', $etime.' 23:59:59');
                if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
            })->where('is_del', 0)->groupBy('team_id')->groupBy('ctime')->orderBy('team_id', 'asc')->orderBy('createTime','desc')->offset($offset)->limit($pageSize)->get();
        }
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';
            $v['true_amount'] = $v['receivedAmount'] - $v['refund_price'] - $v['aftersale_fee'];
            $v['zfm_money'] = $v['receivedAmount'] - $v['wx_money'];
        }

        $teamList = DB::table('teams')->select('id', 'name')->get();
        $teamList = json_decode(json_encode($teamList), true);
        return view('/statistics/teamFinanceTotal',[
            'result'   =>   $result,
            'team_id'   =>   $team_id,
            'stime'   =>   $stime,
            'etime'   =>   $etime,
            'page'   =>   $page,
            'count'   =>   $count,
            'pages'   =>   $pages,
            'teamlist'   =>   $teamList,
            'aftersale'   =>   $aftersale,
        ]);

    }

    /**
     * 团队财务明细导出
     */
    public function teamFinanceList_export(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $aftersale = $request->input('aftersale');
       
        $teams = DB::table('teams')->lists('name', 'id');
        $resultQuery = Order::select('team_id', 'createTime', 'id', 'receivedAmount', 'aftersale_fee', 'refund_price', 'cost', 'freight_cost', 'payment_type', 'aftersale_time')->where(function($query) use($team_id, $stime, $etime, $aftersale){
            if($team_id) $query->where('team_id', $team_id);
            if($stime && $aftersale == 0) $query->where('createTime', '>=', $stime);
            if($etime && $aftersale == 0) $query->where('createTime', '<=', $etime. ' 23:59:59');
            if($stime && $aftersale == 1) $query->where('aftersale_time', '>=', $stime);
            if($etime && $aftersale == 1) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');
            if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
        })->where('is_del', 0)->orderBy('team_id', 'asc');
        if($aftersale == 1){
            $result = $resultQuery->orderBy('aftersale_time','desc')->get();
        }else{
            $result = $resultQuery->orderBy('createTime','desc')->get();
        }
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付'];
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';;
            $v['true_amount'] = $v['receivedAmount'] - $v['refund_price'] - $v['aftersale_fee'];
            $v['payment_type'] = $payArr[$v['payment_type']];
        }

        if($aftersale == 0) $indexKey = ['team_name','createTime','id','receivedAmount','aftersale_fee','refund_price','true_amount','cost','freight_cost', 'payment_type'];
        if($aftersale == 1) $indexKey = ['team_name','aftersale_time','id','receivedAmount','aftersale_fee','refund_price','true_amount','cost','freight_cost', 'payment_type'];
        $title = ['团队', '时间', '订单编号', '成交金额', '售后', '退补差价', '实际金额', '供应商成本', '物流成本', '支付方式'];
        $filename = $aftersale==0 ? 'dingdanmingxi'.date('Y-m-d_H').'.xlsx' : 'shouhoumingxi'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 团队财务汇总导出
     */
    public function teamFinanceTotal_export(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $aftersale = $request->input('aftersale');
       
        $teams = DB::table('teams')->lists('name', 'id');
        if($aftersale == 1){
            $result = Order::select(DB::raw('left(aftersale_time, 10) as ctime, team_id, count(1) as order_count, sum(receivedAmount) as receivedAmount, sum(aftersale_fee) as aftersale_fee, sum(refund_price) as refund_price, sum(cost) as cost, sum(freight_cost) as freight_cost, sum(if(payment_type=1,receivedAmount,null)) as wx_money'))->where(function($query) use($team_id, $stime, $etime, $aftersale){
                if($team_id) $query->where('team_id', $team_id);
                if($stime) $query->where('aftersale_time', '>=', $stime);
                if($etime) $query->where('aftersale_time', '<=', $etime.' 23:59:59');
                if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
            })->where('is_del', 0)->groupBy('team_id')->groupBy('ctime')->orderBy('team_id', 'asc')->orderBy('aftersale_time','desc')->get();
        }else{
            $result = Order::select(DB::raw('left(createTime, 10) as ctime, team_id, count(1) as order_count, sum(receivedAmount) as receivedAmount, sum(aftersale_fee) as aftersale_fee, sum(refund_price) as refund_price, sum(cost) as cost, sum(freight_cost) as freight_cost, sum(if(payment_type=1,receivedAmount,null)) as wx_money'))->where(function($query) use($team_id, $stime, $etime, $aftersale){
                if($team_id) $query->where('team_id', $team_id);
                if($stime) $query->where('createTime', '>=', $stime);
                if($etime) $query->where('createTime', '<=', $etime.' 23:59:59');
                if($aftersale == 1) $query->where('aftersale_fee', '>', 0);
            })->where('is_del', 0)->groupBy('team_id')->groupBy('ctime')->orderBy('team_id', 'asc')->orderBy('createTime','desc')->get();
        }
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';
            $v['true_amount'] = $v['receivedAmount'] - $v['refund_price'] - $v['aftersale_fee'];
            $v['zfm_money'] = $v['receivedAmount'] - $v['wx_money'];
        }

        if($aftersale==0){
            $indexKey = ['team_name','ctime','order_count','receivedAmount','wx_money','zfm_money','aftersale_fee','refund_price','true_amount','cost','freight_cost'];
            $title = ['团队', '时间', '总单数', '成交金额', '微信支付金额','付款码支付金额', '售后', '退补差价', '实际金额', '供应商成本', '物流成本'];
        }else{
            $indexKey = ['team_name','ctime','order_count','receivedAmount','aftersale_fee','refund_price','true_amount','cost','freight_cost'];
            $title = ['团队', '时间', '总单数', '成交金额', '售后', '退补差价', '实际金额', '供应商成本', '物流成本'];
        }
        $filename = $aftersale==0 ? 'dingdanhuizong_'.date('Y-m-d_H').'.xlsx' : 'shouhouhuizong_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);

    }

    /**
     * 地区汇总roi
     */
    public function districtTotalRoi(Request $request){
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }       

        $ret = AdCost::select('id')->where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->get();
        $ret = json_decode(json_encode($ret), true);
        $count = count($ret);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = AdCost::select(DB::raw('sum(cost) as total_cost, sum(conversion_times) as conversion_times, city'))->where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->offset($offset)->limit($pageSize)->get();

        $result = json_decode(json_encode($result), true);

        foreach($result as $k=>&$v){
            //新粉收入
            //加粉
            $city_name = str_replace('市', '', $v['city']);
            $phones = DB::table('customers')->where('receiverCity','like', '%'.$city_name.'%')->lists('phone');
            #订单总计：
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->first();
            
            #汇总新粉成单：            
            $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, 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)->whereIn('receiverMobile', $phones)->first();
            // 1.新粉成单数
            $v['new_order_count'] = $new_order->order_count;
            // 2.新粉成交额 
            $v['new_order_amount'] = $new_order->order_amount;
            // 3.粉丝总成交额
            $v['order_amount'] = $order->order_amount;
            // 总单数
            $v['order_count'] = $order->order_count;
            //新粉roi
            $v['new_roi'] = $v['total_cost']>0 ? round($v['new_order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['total_cost']>0 ? round($v['order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            $v['new_fan_count'] = count($phones);

        }

        return view('statistics/districtTotalRoi', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                    
            ]);

    }

    /**
     * 地域roi7日
     */
    public function districtRoi7day(Request $request){
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $count = DistrictRoi7::where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->count();

        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = DistrictRoi7::where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->orderBy('id', 'desc')->offset($offset)->limit($pageSize)->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['order_amount'] / $v['cost'], 4) * 100 .'%' : '';
        }

        return view('statistics/districtRoi7day', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                    
            'stime'             =>$stime,                    
            'etime'             =>$etime,                    
            ]);

    }

    /**
     * 地域roi7日
     */
    public function districtRoi7day_export(Request $request){

        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $result = DistrictRoi7::where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->orderBy('id', 'desc')->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['order_amount'] / $v['cost'], 4) * 100 .'%' : '';
        }

        $indexKey = ['ad_time','city','cost','gzh_count','fan_count','new_fan_order_count','new_fan_order_amount','new_roi','order_count','order_amount','total_roi'];
        $title = ['日期', '城市', '投放成本', '加粉数', '新粉数量', '新粉成单数', '新粉收入', '新粉ROI', '7日累计成单数', '7日累计收入', '7日累计ROI'];
        $filename = 'districtRoi7day_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 地域roi7日
     */
    public function districtRoi7dayTotal(Request $request){
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $count = DistrictRoi7::where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->get();

        $count = count($count);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = DistrictRoi7::select(DB::raw('city,sum(cost) as cost,sum(gzh_count) as gzh_count,sum(fan_count) as fan_count,sum(new_fan_order_count) as new_fan_order_count,sum(new_fan_order_amount) as new_fan_order_amount,sum(order_count) as order_count,sum(order_amount) as sum_order_amount, sum(order_amount)/sum(cost) as roi'))->where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->orderBy('roi', 'desc')->offset($offset)->limit($pageSize)->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['sum_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //数据格式化
            $v['order_amount'] = round($v['sum_order_amount'],2);
        }

        return view('statistics/districtRoi7dayTotal', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                                        
            ]);

    }

    /**
     * 地域roi7日
     */
    public function districtRoi7dayTotal_export(Request $request){

        $city = $request->input('city');
        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $result = DistrictRoi7::select(DB::raw('city,sum(cost) as cost,sum(gzh_count) as gzh_count,sum(fan_count) as fan_count,sum(new_fan_order_count) as new_fan_order_count,sum(new_fan_order_amount) as new_fan_order_amount,sum(order_count) as order_count,sum(order_amount) as sum_order_amount,sum(order_amount)/sum(cost) as roi'))->where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->orderBy('roi', 'desc')->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['sum_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //数据格式化
            $v['order_amount'] = round($v['sum_order_amount'],2);
        }

        $indexKey = ['city','cost','gzh_count','fan_count','new_fan_order_count','new_fan_order_amount','new_roi','order_count','order_amount','total_roi'];
        $title = ['城市', '投放成本', '加粉数', '新粉数量', '新粉成单数', '新粉收入', '新粉ROI', '7日累计成单数', '7日累计收入', '7日累计ROI'];
        $filename = 'districtRoi7day_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 地域roi30日
     */
    public function districtRoi30day(Request $request){
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $count = districtRoi30::where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->count();

        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = districtRoi30::where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->orderBy('id', 'desc')->offset($offset)->limit($pageSize)->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['order_amount'] / $v['cost'], 4) * 100 .'%' : '';
        }

        return view('statistics/districtRoi30day', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                    
            'stime'             =>$stime,                    
            'etime'             =>$etime,                    
            ]);

    }

    /**
     * 地域roi30日
     */
    public function districtRoi30day_export(Request $request){

        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $result = districtRoi30::where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->orderBy('id', 'desc')->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['order_amount'] / $v['cost'], 4) * 100 .'%' : '';
        }

        $indexKey = ['ad_time','city','cost','gzh_count','fan_count','new_fan_order_count','new_fan_order_amount','new_roi','order_count','order_amount','total_roi'];
        $title = ['日期', '城市', '投放成本', '加粉数', '新粉数量', '新粉成单数', '新粉收入', '新粉ROI', '累计成单数', '累计收入', '累计ROI'];
        $filename = 'districtRoi30day_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 地域roi30日汇总
     */
    public function districtRoi30dayTotal(Request $request){
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

        $offset = ($page-1) * $pageSize;
        $city = $request->input('city');

        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $count = districtRoi30::where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->get();

        $count = count($count);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = districtRoi30::select(DB::raw('city,sum(cost) as cost,sum(gzh_count) as gzh_count,sum(fan_count) as fan_count,sum(new_fan_order_count) as new_fan_order_count,sum(new_fan_order_amount) as new_fan_order_amount,sum(order_count) as order_count,sum(order_amount) as sum_order_amount, sum(order_amount)/sum(cost) as roi'))->where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->orderBy('roi', 'desc')->offset($offset)->limit($pageSize)->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['sum_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //数据格式化
            $v['order_amount'] = round($v['sum_order_amount'],2);
        }

        return view('statistics/districtRoi30dayTotal', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                                        
            ]);

    }

    /**
     * 地域roi30日
     */
    public function districtRoi30dayTotal_export(Request $request){

        $city = $request->input('city');
        if($city !== null){
            $city = str_replace('市', '', $city);
        }

        $result = districtRoi30::select(DB::raw('city,sum(cost) as cost,sum(gzh_count) as gzh_count,sum(fan_count) as fan_count,sum(new_fan_order_count) as new_fan_order_count,sum(new_fan_order_amount) as new_fan_order_amount,sum(order_count) as order_count,sum(order_amount) as sum_order_amount,sum(order_amount)/sum(cost) as roi'))->where(function($query) use($city){    
            if($city) $query->where('city', 'like', '%'.$city.'%');
        })->groupBy('city')->orderBy('roi', 'desc')->get();

        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
             //新粉roi
            $v['new_roi'] = $v['cost']>0 ? round($v['new_fan_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //累计roi
            $v['total_roi'] = $v['cost']>0 ? round($v['sum_order_amount'] / $v['cost'], 4) * 100 .'%' : '';
            //数据格式化
            $v['order_amount'] = round($v['sum_order_amount'],2);
        }

        $indexKey = ['city','cost','gzh_count','fan_count','new_fan_order_count','new_fan_order_amount','new_roi','order_count','order_amount','total_roi'];
        $title = ['城市', '投放成本', '加粉数', '新粉数量', '新粉成单数', '新粉收入', '新粉ROI', '累计成单数', '累计收入', '累计ROI'];
        $filename = 'districtRoi30day_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 销售月度业绩报表
     */
    public function salerBonus(Request $request){
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');
        $mstime = $request->input('stime');
        $metime = $request->input('etime');

        if($mstime){
            $stime = date('Y-m-01', strtotime($mstime));
        }else{
            $stime = null;
        }
        if($metime){
            $etime = date('Y-m-01', strtotime($metime. ' 1 month')); 
        }else{
            $etime = null;
        }

        //假如有团队筛选，检索销售队员
        $sale_ids = null;
        if($team_id>0 && !$admin_id){
            $sale_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }

        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }
        $offset = ($page-1) * $pageSize;

        $ret = Order::select(DB::raw('left(createTime, 7) as month, admin_id'))->where('is_del', 0)->where('status', '>=', 2)->where(function($query) use($admin_id, $sale_ids, $stime, $etime){
            if($admin_id) $query->where('admin_id', $admin_id);
            if(!empty($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if(!empty($stime)) $query->where('createTime', '>=', $stime);
            if(!empty($etime)) $query->where('createTime', '<', $etime);
        })->groupBy('month')->groupBy('admin_id')->get();
        $count = count($ret);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = Order::select(DB::raw('left(createTime, 7) as month, admin_id, count(1) as order_count, sum(receivedAmount) as order_amount'))->where('is_del', 0)->where('status', '>=', 2)->where(function($query) use($admin_id, $sale_ids, $stime, $etime){
            if($admin_id) $query->where('admin_id', $admin_id);
            if(!empty($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if(!empty($stime)) $query->where('createTime', '>=', $stime);
            if(!empty($etime)) $query->where('createTime', '<', $etime);
        })->groupBy('month')->groupBy('admin_id')->orderBy('month', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $m_stime = date('Y-m-01', strtotime($v['month']));
            $m_etime = date('Y-m-01', strtotime($m_stime. ' 1 month'));
            //加粉数
            $fan_count = DB::table('cust_day_detail')->select(DB::raw('sum(fan_add) as fan_count, admin_name'))->where('dtime', '>=', $m_stime)->where('dtime', '<', $m_etime)->where('admin_id', $v['admin_id'])->where('is_del', 0)->first();
            $v['fan_count'] = $fan_count->fan_count;
            $v['admin_name'] = $fan_count->admin_name;
            if(empty($v['admin_name'])){
                $v['admin_name'] = DB::table('admin')->where('id', $v['admin_id'])->pluck('name');
            }
            #计算提成
            $v['bonus'] = $this->getSalerBonus($v['order_amount']);
        }

        $teamList = DB::table('teams')->select('id', 'name')->get();
        $teamList = json_decode(json_encode($teamList), true);
        $adminList = DB::table('admin')->select('id', 'realname', 'username')->where('id','>', 1)->get();
        $adminList = json_decode(json_encode($adminList), true);
        return view('statistics/salerBonus', ['result'=>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,  
            'teamlist'             =>$teamList,  
            'adminlist'             =>$adminList,  
            'team_id'             =>$team_id,  
            'admin_id'             =>$admin_id,  
            'stime'             =>$mstime,  
            'etime'             =>$metime,  
        ]);
    }

    /**
     * 计算提成
     */
    public function getSalerBonus($amount){
        $result = [
            0 => 0, //总的
            1 => 0, 
            2 => 0,
            3 => 0
        ];
        if($amount>150000){
            $result[1] = round(100000 * 0.03);
            $result[2] = round(50000 * 0.04);
            $result[3] = round( ($amount - 150000) * 0.05 , 2);
            $result[0] = $result[1] + $result[2] + $result[3];
        }elseif($amount>100000){
            $result[1] = round(100000 * 0.03);
            $result[2] = round(($amount - 100000) * 0.04, 2);
            $result[0] = $result[1] + $result[2];
        }else{
            $result[1] = round($amount * 0.03, 2);
            $result[0] = $result[1];
        }

        return $result;
    }

    /**
     * 销售月度业绩报表
     */
    public function salerBonus_export(Request $request){ 
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');

        if($stime){
            $stime = date('Y-m-01', strtotime($stime));
        }
        if($etime){
            $etime = date('Y-m-01', strtotime($etime. ' 1 month')); 
        }

        //假如有团队筛选，检索销售队员
        $sale_ids = null;
        if($team_id>0 && !$admin_id){
            $sale_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }

        $result = Order::select(DB::raw('left(createTime, 7) as month, admin_id, count(1) as order_count, sum(receivedAmount) as order_amount'))->where('is_del', 0)->where('status', '>=', 2)->where(function($query) use($admin_id, $sale_ids, $stime, $etime){
            if($admin_id) $query->where('admin_id', $admin_id);
            if(!empty($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if(!empty($stime)) $query->where('createTime', '>=', $stime);
            if(!empty($etime)) $query->where('createTime', '<', $etime);
        })->groupBy('month')->groupBy('admin_id')->orderBy('month', 'desc')->get();
        $result = json_decode(json_encode($result), true);

        foreach($result as $k=>&$v){
            $m_stime = date('Y-m-01', strtotime($v['month']));
            $m_etime = date('Y-m-01', strtotime($m_stime. ' 1 month'));
            //加粉数
            $fan_count = DB::table('cust_day_detail')->select(DB::raw('sum(fan_add) as fan_count, admin_name'))->where('dtime', '>=', $m_stime)->where('dtime', '<', $m_etime)->where('admin_id', $v['admin_id'])->where('is_del', 0)->first();
            $v['fan_count'] = $fan_count->fan_count;
            $v['admin_name'] = $fan_count->admin_name;
            if(empty($v['admin_name'])){
                $v['admin_name'] = DB::table('admin')->where('id', $v['admin_id'])->pluck('name');
            }
            #计算提成
            $bonus = $this->getSalerBonus($v['order_amount']);
            $v['bonus0'] = $bonus[0];
            $v['bonus1'] = $bonus[1];
            $v['bonus2'] = $bonus[2];
            $v['bonus3'] = $bonus[3];
        }

        $indexKey = ['month','admin_name','order_count','order_amount','fan_count','bonus0','bonus1','bonus2','bonus3'];
        $title = ['月份', '销售名', '总单量', '总成交额', '总加粉数', '总提成', '一阶提成', '二阶提成', '三阶提成'];
        $filename = 'xiaoshouyj_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }
}