';
        //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/52065f53a48a366a7097f07d086ad085');
        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');

        $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){
            if($team_id) $query->where('team_id', $team_id);
            if($stime) $query->where('createTime', '>=', $stime);
            if($etime) $query->where('createTime', '<=', $etime. ' 23:59:59');

        })->where('is_del', 0)->where('status','>=',2)->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){
            if($team_id) $query->where('team_id', $team_id);
            if($stime ) $query->where('createTime', '>=', $stime);
            if($etime ) $query->where('createTime', '<=', $etime. ' 23:59:59');
        })->where('is_del', 0)->where('status','>=',2)->orderBy('team_id', 'asc');

        $result = $resultQuery->orderBy('createTime','desc')->offset($offset)->limit($pageSize)->get();
        
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        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'] = $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,
        ]);
    } 

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

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

        $_data = $this->teamCtimePage($stime, $etime, $team_id, $offset, $pageSize);

        $count = $_data['count'];

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

        $teams = DB::table('teams')->lists('name', 'id');

        foreach($_data['data'] as $k=>&$v){
            $order_result = Order::select(DB::raw('team_id, count(1) as order_count, sum(receivedAmount) as receivedAmount, sum(if(payment_type=1,receivedAmount,null)) as wx_money, sum(if(payment_type=2,receivedAmount,null)) as zfm_money, sum(if(payment_type=3,receivedAmount,null)) as gth_money, sum(if(payment_type=4,receivedAmount,null)) as vip_money'))->where('is_del', 0)->where('status','>=',2)->where('createTime','>=', $v['date'])->where('createTime','<=', $v['date'].' 23:59:59')->where('team_id', $v['team_id'])->first();

            $v['order_count'] = isset($order_result->order_count) ? $order_result->order_count : '';
            $v['receivedAmount'] = isset($order_result->receivedAmount) ? $order_result->receivedAmount : '';
            $v['wx_money'] = isset($order_result->wx_money) ? $order_result->wx_money : '';
          
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';
            #当天售后和退补
            $ctime = $v['ctime'] = $v['date'];
            $team = $v['team_id'];
            $aftersale_info = Order::select(DB::raw('count(1) as aftersale_count, sum(aftersale_fee) as aftersale_fee'))->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('aftersale_time', '>=', $ctime);
                $query->where('aftersale_time', '<=', $ctime.' 23:59:59');
            })->where('is_del', 0)->where('status','>=',2)->where('aftersale_fee', '>', 0)->first();
            $v['aftersale_count'] = $aftersale_info->aftersale_count;
            $v['aftersale_fee'] = $aftersale_info->aftersale_fee;
            #退补
            $refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['refund_count'] = $refund_info->refund_count;
            $v['refund_price'] = $refund_info->refund_price;
            //wx退补
            $wx_refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where('refund_type', 1)->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['wx_refund_count'] = $wx_refund_info->refund_count;
            $v['wx_refund_price'] = $wx_refund_info->refund_price;
            //付款码退补
            $zfm_refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where('refund_type', 2)->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['zfm_refund_count'] = $zfm_refund_info->refund_count;
            $v['zfm_refund_price'] = $zfm_refund_info->refund_price;
             //个体户退补
            $gth_refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where('refund_type', 3)->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['gth_refund_count'] = $gth_refund_info->refund_count;
            $v['gth_refund_price'] = $gth_refund_info->refund_price;

            #发货
            $send_info = Order::select(DB::raw('count(1) as send_count, sum(cost) as cost, sum(freight_cost) as freight_cost'))->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('send_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('logistics_id', '>', '')->first();
            $v['send_count'] = $send_info->send_count;
            $v['cost'] = $send_info->cost;
            $v['freight_cost'] = $send_info->freight_cost;

            $v['zfm_money'] = isset($order_result->zfm_money) ? $order_result->zfm_money : '';           
            $v['gth_money'] = isset($order_result->gth_money) ? $order_result->gth_money : '';           
            $v['vip_money'] = isset($order_result->vip_money) ? $order_result->vip_money : '';           
            $v['wx_true_amount'] = $v['wx_money'] + $v['wx_refund_price'] - $v['aftersale_fee'];
            $v['zfm_true_amount'] = $v['zfm_money'] + $v['zfm_refund_price'];
            $v['gth_true_amount'] = $v['gth_money'] + $v['gth_refund_price'];
            $v['vip_true_amount'] = $v['vip_money'];

            #毛利
            $v['profit'] = $v['wx_true_amount'] + $v['zfm_true_amount'] + $v['gth_true_amount'] + $v['vip_true_amount'] - $v['cost'] - $v['freight_cost'];

        }

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

    }

    /**
     * 团队财务明细导出
     */
    public function teamFinanceList_export(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
       
        $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){
            if($team_id) $query->where('team_id', $team_id);
            if($stime ) $query->where('createTime', '>=', $stime);
            if($etime ) $query->where('createTime', '<=', $etime. ' 23:59:59');
        })->where('is_del', 0)->where('status','>=',2)->orderBy('team_id', 'asc');

        $result = $resultQuery->orderBy('createTime','desc')->get();
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        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']];
        }

        $indexKey = ['team_name','createTime','id','receivedAmount','cost','freight_cost', 'payment_type'];      
        $title = ['团队', '时间', '订单编号', '成交金额','供应商成本', '物流成本', '支付方式'];
        $filename = 'dingdanmingxi_'.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');
       
        $teams = DB::table('teams')->lists('name', 'id');
        $result = $this->teamCtimePage_export($stime, $etime, $team_id);

        foreach($result as $k=>&$v){
            $order_result = Order::select(DB::raw('team_id, count(1) as order_count, sum(receivedAmount) as receivedAmount, sum(if(payment_type=1,receivedAmount,null)) as wx_money, sum(if(payment_type=2,receivedAmount,null)) as zfm_money, sum(if(payment_type=3,receivedAmount,null)) as gth_money, sum(if(payment_type=4,receivedAmount,null)) as vip_money'))->where('is_del', 0)->where('status','>=',2)->where('createTime','>=', $v['date'])->where('createTime','<=', $v['date'].' 23:59:59')->where('team_id', $v['team_id'])->first();

            $v['order_count'] = isset($order_result->order_count) ? $order_result->order_count : '';
            $v['receivedAmount'] = isset($order_result->receivedAmount) ? $order_result->receivedAmount : '';
            $v['wx_money'] = isset($order_result->wx_money) ? $order_result->wx_money : '';
          
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';
            #当天售后和退补
            $ctime = $v['ctime'] = $v['date'];
            $team = $v['team_id'];
            $aftersale_info = Order::select(DB::raw('count(1) as aftersale_count, sum(aftersale_fee) as aftersale_fee'))->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('aftersale_time', '>=', $ctime);
                $query->where('aftersale_time', '<=', $ctime.' 23:59:59');
            })->where('is_del', 0)->where('status','>=',2)->where('aftersale_fee', '>', 0)->first();
            $v['aftersale_count'] = $aftersale_info->aftersale_count;
            $v['aftersale_fee'] = $aftersale_info->aftersale_fee;
            #退补
            $refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['refund_count'] = $refund_info->refund_count;
            $v['refund_price'] = $refund_info->refund_price;
            //wx退补
            $wx_refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where('refund_type', 1)->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['wx_refund_count'] = $wx_refund_info->refund_count;
            $v['wx_refund_price'] = $wx_refund_info->refund_price;
            //付款码退补
            $zfm_refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where('refund_type', 2)->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['zfm_refund_count'] = $zfm_refund_info->refund_count;
            $v['zfm_refund_price'] = $zfm_refund_info->refund_price;
             //个体户退补
            $gth_refund_info = Order::select(DB::raw('count(1) as refund_count, sum(refund_price) as refund_price'))->where('refund_type', 3)->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('refund_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->first();
            $v['gth_refund_count'] = $gth_refund_info->refund_count;
            $v['gth_refund_price'] = $gth_refund_info->refund_price;

            #发货
            $send_info = Order::select(DB::raw('count(1) as send_count, sum(cost) as cost, sum(freight_cost) as freight_cost'))->where(function($query) use($team, $ctime){
                $query->where('team_id', $team);
                $query->where('send_time', '=', $ctime);
            })->where('is_del', 0)->where('status','>=',2)->where('logistics_id', '>', '')->first();
            $v['send_count'] = $send_info->send_count;
            $v['cost'] = $send_info->cost;
            $v['freight_cost'] = $send_info->freight_cost;

            $v['zfm_money'] = isset($order_result->zfm_money) ? $order_result->zfm_money : '';           
            $v['gth_money'] = isset($order_result->gth_money) ? $order_result->gth_money : '';           
            $v['vip_money'] = isset($order_result->vip_money) ? $order_result->vip_money : '';           
            $v['wx_true_amount'] = $v['wx_money'] + $v['wx_refund_price'] - $v['aftersale_fee'];
            $v['zfm_true_amount'] = $v['zfm_money'] + $v['zfm_refund_price'];
            $v['gth_true_amount'] = $v['gth_money'] + $v['gth_refund_price'];
            $v['vip_true_amount'] = $v['vip_money'];

            #毛利
            $v['profit'] = $v['wx_true_amount'] + $v['zfm_true_amount'] + $v['gth_true_amount'] + $v['vip_true_amount'] - $v['cost'] - $v['freight_cost'];

        }

        $indexKey = ['team_name','ctime','order_count','receivedAmount','wx_money','zfm_money','gth_money', 'vip_money', 'aftersale_count','aftersale_fee','wx_refund_count','wx_refund_price','wx_true_amount','zfm_refund_count','zfm_refund_price','zfm_true_amount','gth_refund_count','gth_refund_price','gth_true_amount', 'send_count', 'cost', 'freight_cost', 'profit'];
        $title = ['团队', '时间', '总单数', '成交金额', '微信支付金额','付款码支付金额', '个体户支付', '充值卡支付金额', '售后单数', '售后金额', '微信退补单数', '微信退补差价', '微信实际金额', '付款码退补单数', '付款码退补差价', '付款码实际金额', '个体户退补单数', '个体户退补差价', '个体户实际金额', '发货单数', '货品成本', '物流成本', '毛利'];
        
        $filename = 'dingdanhuizong_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);

    }


    /**
     * 售后财务明细
     */
    public function aftersaleFinanceList(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $warehouse = (int)$request->input('warehouse');

        $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, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime) $query->where('aftersale_time', '>=', $stime);
            if($etime) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');

        })->where('is_del', 0)->where('status','>=',2)->where('aftersale_fee', '>', 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', 'warehouse')->where(function($query) use($team_id, $stime, $etime, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime ) $query->where('aftersale_time', '>=', $stime);
            if($etime ) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');
        })->where('is_del', 0)->where('status','>=',2)->where('aftersale_fee', '>', 0);

        $result = $resultQuery->orderBy('aftersale_time','desc')->offset($offset)->limit($pageSize)->get();
        
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        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'] = $v['aftersale_time'];
            $v['warehouse'] = $wareArr[$v['warehouse']];
        }

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

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

     /**
     * 退补财务明细
     */
    public function refundFinanceList(Request $request){
        $team_id = (int)$request->input('team_id');
        $warehouse = (int)$request->input('warehouse');
        $stime = $request->input('stime');
        $etime = $request->input('etime');

        $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, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime) $query->where('refund_time', '>=', $stime);
            if($etime) $query->where('refund_time', '<=', $etime);

        })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1)->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', 'refund_price', 'payment_type', 'refund_time', 'refund_note', 'refund_type', 'warehouse')->where(function($query) use($team_id, $stime, $etime, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime ) $query->where('refund_time', '>=', $stime);
            if($etime ) $query->where('refund_time', '<=', $etime);
        })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1);

        $result = $resultQuery->orderBy('refund_time','desc')->offset($offset)->limit($pageSize)->get();
        
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';;            
            $v['payment_type'] = $payArr[$v['payment_type']];
            $v['ctime'] = $v['refund_time'];
            //退补支付方式
            if($v['refund_type'] == 1){
                $v['refund_type'] = '微信支付';
            }elseif($v['refund_type'] == 2){
                $v['refund_type'] = '付款码支付';
            }elseif($v['refund_type'] == 3){
                $v['refund_type'] = '个体户支付';
            }else{
                $v['refund_type'] = '';
            }   
            $v['warehouse'] = $wareArr[$v['warehouse']];
        }

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

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

     /**
     * 发货财务明细
     */
    public function sendFinanceList(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $warehouse = (int)$request->input('warehouse');

        $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, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime) $query->where('send_time', '>=', $stime);
            if($etime) $query->where('send_time', '<=', $etime);

        })->where('is_del', 0)->where('status','>=',2)->where('logistics_id', '>', '')->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', 'cost', 'freight_cost', 'payment_type', 'send_time', 'warehouse', 'receiverName', 'logistics_id')->where(function($query) use($team_id, $stime, $etime, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime ) $query->where('send_time', '>=', $stime);
            if($etime ) $query->where('send_time', '<=', $etime);
        })->where('is_del', 0)->where('status','>=',2)->where('logistics_id', '>', '');

        $result = $resultQuery->orderBy('send_time','desc')->offset($offset)->limit($pageSize)->get();
        
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';;            
            $v['payment_type'] = $payArr[$v['payment_type']];
            $v['ctime'] = $v['send_time'];
            $v['warehouse'] = $wareArr[$v['warehouse']];
        }

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

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

    /**
     * 售后财务明细
     */
    public function aftersaleFinanceList_export(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $warehouse = (int)$request->input('warehouse');

        $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', 'warehouse')->where(function($query) use($team_id, $stime, $etime, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime ) $query->where('aftersale_time', '>=', $stime);
            if($etime ) $query->where('aftersale_time', '<=', $etime. ' 23:59:59');
        })->where('is_del', 0)->where('status','>=',2)->where('aftersale_fee', '>', 0);

        $result = $resultQuery->orderBy('aftersale_time','desc')->get();
        
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        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'] = $v['aftersale_time'];
            $v['warehouse'] = $wareArr[$v['warehouse']];
        }

        $indexKey = ['team_name','ctime','id','receivedAmount','aftersale_fee', 'payment_type', 'warehouse'];      
        $title = ['团队', '售后时间', '订单编号', '成交金额','售后金额', '支付方式', '发货仓库'];
        $filename = 'shouhoumingxi_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    } 

     /**
     * 退补财务明细
     */
    public function refundFinanceList_export(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $warehouse = (int)$request->input('warehouse');

        $teams = DB::table('teams')->lists('name', 'id');
        $resultQuery = Order::select('team_id', 'createTime', 'id', 'receivedAmount', 'refund_price', 'payment_type', 'refund_time', 'refund_note', 'refund_type', 'warehouse')->where(function($query) use($team_id, $stime, $etime, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime ) $query->where('refund_time', '>=', $stime);
            if($etime ) $query->where('refund_time', '<=', $etime);
        })->where('is_del', 0)->where('status','>=',2)->where('refund_time', '>', '')->where('is_refund', 1);

        $result = $resultQuery->orderBy('refund_time','desc')->get();
        
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';;            
            $v['payment_type'] = $payArr[$v['payment_type']];
            $v['ctime'] = $v['refund_time'];
            //退补支付方式
            if($v['refund_type'] == 1){
                $v['refund_type'] = '微信支付';
            }elseif($v['refund_type'] == 2){
                $v['refund_type'] = '付款码支付';
            }elseif($v['refund_type'] == 3){
                $v['refund_type'] = '个体户支付';
            }else{
                $v['refund_type'] = '';
            } 
            $v['warehouse'] = $wareArr[$v['warehouse']];
        }

        $indexKey = ['team_name','ctime','id','receivedAmount', 'payment_type','refund_price', 'refund_type', 'warehouse'];      
        $title = ['团队', '退补时间', '订单编号', '成交金额', '支付方式', '退补差价', '退补支付方式', '发货仓库'];
        $filename = 'tuibumingxi_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    } 

     /**
     * 发货财务明细
     */
    public function sendFinanceList_export(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $warehouse = (int)$request->input('warehouse');
        
        $teams = DB::table('teams')->lists('name', 'id');
        $resultQuery = Order::select('team_id', 'createTime', 'id', 'receivedAmount', 'cost', 'freight_cost', 'payment_type', 'send_time', 'warehouse', 'receiverName', 'logistics_id')->where(function($query) use($team_id, $stime, $etime, $warehouse){
            if($team_id) $query->where('team_id', $team_id);
            if($warehouse) $query->where('warehouse', $warehouse);
            if($stime ) $query->where('send_time', '>=', $stime);
            if($etime ) $query->where('send_time', '<=', $etime);
        })->where('is_del', 0)->where('status','>=',2)->where('logistics_id', '>', '');

        $result = $resultQuery->orderBy('send_time','desc')->get();
        
        $result = json_decode(json_encode($result), true);
        $payArr = [1=>'微信支付', 2=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        foreach($result as $k=>&$v){
            $v['team_name'] = $v['team_id']>0 ? $teams[$v['team_id']] : '';;            
            $v['payment_type'] = $payArr[$v['payment_type']];
            $v['ctime'] = $v['send_time'];
            $v['warehouse'] = $wareArr[$v['warehouse']];
        }

        $indexKey = ['team_name','ctime','id','receivedAmount', 'cost', 'freight_cost', 'payment_type', 'warehouse', 'receiverName', 'logistics_id'];      
        $title = ['团队', '发货时间', '订单编号', '成交金额','货品成本', '物流成本', '支付方式', '发货仓库', '收货人', '物流单号'];
        $filename = 'fahuomingxi_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    } 


    /**
     * 团队订单财务汇总列表
     */
    public function teamFinanceTotal2(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)->where('status','>=',2)->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)->where('status','>=',2)->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)->where('status','>=',2)->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_export2(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)->where('status','>=',2)->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=>'付款码支付', 3=>'个体户支付', 4=>'充值卡支付'];
        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_export2(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)->where('status','>=',2)->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)->where('status','>=',2)->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);
    }

    /**
     * 地域roi15日
     */
    public function districtRoi15day(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 = districtRoi15::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 = districtRoi15::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/districtRoi15day', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                    
            'stime'             =>$stime,                    
            'etime'             =>$etime,                    
            ]);

    }

    /**
     * 地域roi15日
     */
    public function districtRoi15day_export(Request $request){

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

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

        $result = districtRoi15::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 = 'districtRoi15day_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 地域roi15日汇总
     */
    public function districtRoi15dayTotal(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 = districtRoi15::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 = districtRoi15::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/districtRoi15dayTotal', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                                        
            ]);

    }

    /**
     * 地域roi15日
     */
    public function districtRoi15dayTotal_export(Request $request){

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

        $result = districtRoi15::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 = 'districtRoi15day_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 地域roi45日
     */
    public function districtRoi45day(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 = districtRoi45::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 = districtRoi45::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/districtRoi45day', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                    
            'stime'             =>$stime,                    
            'etime'             =>$etime,                    
            ]);

    }

    /**
     * 地域roi45日
     */
    public function districtRoi45day_export(Request $request){

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

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

        $result = districtRoi45::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 = 'districtRoi45day_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 地域roi45日汇总
     */
    public function districtRoi45dayTotal(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 = districtRoi45::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 = districtRoi45::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/districtRoi45dayTotal', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'             =>$city,                                        
            ]);

    }

    /**
     * 地域roi45日
     */
    public function districtRoi45dayTotal_export(Request $request){

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

        $result = districtRoi45::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 = 'districtRoi45day_'.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('realname');
            }
            #计算提成
            $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('realname');
            }
            #计算提成
            $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);
    }

    /**
     * 团队财务汇总时间格式处理  
     */
    public function teamCtimePage($stime, $etime, $team_id, $offset, $pageSize){
        $result = array();
        if( empty($sdate) ) $sdate = '2019-09-04';
        $edate = date('Y-m-d');
        if($stime && $stime>$sdate){
            $sdate = $stime;
        }
        if($etime && $etime<$edate){
            $edate = $etime;
        }
        $teams = [1,3,5];
        if($team_id>0){
            $teams = [$team_id];
        }
        $d = (int)(strtotime($edate) - strtotime($sdate)) / 86400 + 1;
        for($i=$d-1; $i>=0; $i--){
            $dtime = $sdate. '+'.$i.' day';
            $date = date('Y-m-d', strtotime($dtime));
            foreach($teams as $team_id){
                $result[] = ['date'=>$date, 'team_id'=>$team_id];
            }
        }

        $count = count($result);
        $ret['data'] = array_slice($result, $offset, $pageSize);
        $ret['count'] = $count;
        return $ret;
    }

    /**
     * 团队财务汇总时间格式处理
     */
    public function teamCtimePage_export($stime, $etime, $team_id){
        $result = array();
        if( empty($sdate) ) $sdate = '2019-09-04';
        $edate = date('Y-m-d');
        if($stime && $stime>$sdate){
            $sdate = $stime;
        }
        if($etime && $etime<$edate){
            $edate = $etime;
        }
        $teams = [1,3,5];
        if($team_id>0){
            $teams = [$team_id];
        }
        $d = (int)(strtotime($edate) - strtotime($sdate)) / 86400 + 1;
        for($i=$d-1; $i>=0; $i--){
            $dtime = $sdate. '+'.$i.' day';
            $date = date('Y-m-d', strtotime($dtime));
            foreach($teams as $team_id){
                $result[] = ['date'=>$date, 'team_id'=>$team_id];
            }
        }

        return $result;
    }

    /**
     * 投放数据时间格式 分页  
     */
    public function throwCtimePage($stime, $etime, $offset='', $pageSize=''){
        $result = array();
        if( empty($sdate) ) $sdate = '2019-07-03';
        $edate = date('Y-m-d', strtotime('-1 day'));
        if($stime && $stime>$sdate){
            $sdate = $stime;
        }
        if($etime && $etime<$edate){
            $edate = $etime;
        }

        $d = (int)(strtotime($edate) - strtotime($sdate)) / 86400 + 1;
        for($i=$d-1; $i>=0; $i--){
            $dtime = $sdate. '+'.$i.' day';
            $date = date('Y-m-d', strtotime($dtime));            
            $result[] = ['date'=>$date];           
        }
        
        if( $pageSize>0 ){
            $count = count($result);
            $ret['data'] = array_slice($result, $offset, $pageSize);
            $ret['count'] = $count;
        }else{
            $ret = $result;
        }
        return $ret;
    }

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

        //假如有团队筛选，检索销售队员
        $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;

        $count = DB::table('saler_targets')->where(function($query) use($admin_id, $sale_ids, $mstime, $metime){
            if($admin_id) $query->where('admin_id', $admin_id);
            if(!empty($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if(!empty($mstime)) $query->where('month', '>=', $mstime);
            if(!empty($metime)) $query->where('month', '<=', $metime);
        })->count();
        
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = DB::table('saler_targets')->where(function($query) use($admin_id, $sale_ids, $mstime, $metime){
            if($admin_id) $query->where('admin_id', $admin_id);
            if(!empty($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if(!empty($mstime)) $query->where('month', '>=', $mstime);
            if(!empty($metime)) $query->where('month', '<=', $metime);
        })->orderBy('month', 'desc')->offset($offset)->limit($pageSize)->get();

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

        //月份天数数组：
        $m_day = [1=>31,2=>28,3=>31,4=>30,5=>31,6=>30,7=>31,8=>31,9=>30,10=>31,11=>30,12=>31];
        if(date('Y') % 4 == 0){
            $m_day[2] = 29;
        }
        $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'));

            //总单数
            $order = Order::select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('admin_id', $v['admin_id'])->where('is_del', 0)->where('createTime','>=',$m_stime)->where('createTime', '<', $m_etime)->first();
            $v['order_count'] = $order->order_count;
            $v['order_amount'] = $order->order_amount;
            //销售额完成率
            $v['target_rate'] = $v['amount']>0 ? round( $order->order_amount / $v['amount'] , 4) : '';
            //时间进度
            //当前月
            $tmonth = date('Y-m');
            $m = date('m');
            $d = date('d');
            $days = $m_day[$m];
            if( $v['month'] == $tmonth ){               
                $v['time_rate'] = round($d / $days, 4);
                //剩余每天任务
                if( $v['target_rate'] >= 1){
                    $v['day_remain'] = '';
                }else{
                    $v['day_remain'] = round( ($v['amount'] - $order->order_amount) / $days, 4 );
                }
            }elseif( $v['month'] < $tmonth ){
                $v['time_rate'] = 1;
                $v['day_remain'] = '';
            }elseif( $v['month'] > $tmonth ){
                $v['time_rate'] = 0;
                $v['day_remain'] = round( $v['amount'] / $days, 4 );
            }

            //粉数数据
            $fan_count = DB::table('cust_day_detail')->select(DB::raw('sum(fan_add) as fan_count, sum(new_reply) as new_reply_count, sum(new_consult) as new_consult_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['new_reply_count'] = $fan_count->new_reply_count;
            $v['new_consult_count'] = $fan_count->new_consult_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('username');
            }

            //新粉成单            
            $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)->where('cu.fanTime','>=',$m_stime)->where('cu.fanTime', '<', $m_etime)->where('order.admin_id', $v['admin_id'])->first();
            $v['new_order_count'] = $new_order->order_count;
            $v['new_order_amount'] = $new_order->order_amount;
            //老粉成单
            $v['old_order_count'] = $order->order_count - $new_order->order_count;
            $v['old_order_amount'] = $order->order_amount - $new_order->order_amount;
            //复购单数
            //总单数
            $fugou_order = Order::select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('admin_id', $v['admin_id'])->where('is_del', 0)->where('createTime','>=',$m_stime)->where('createTime', '<', $m_etime)->where('is_fugou', 1)->first();
            $v['fugou_order_count'] = $fugou_order->order_count;
            $v['fugou_order_amount'] = $fugou_order->order_amount;

            //新粉转化
            $phones = DB::table('customers')->select(DB::raw('distinct(customers.phone)'))->leftJoin('order', 'customers.phone', '=', 'order.receiverMobile')->where('customers.fanTime','>=',$m_stime)->where('customers.fanTime','<',$m_etime)->where('order.admin_id', $v['admin_id'])->get();

            $new_orderfan_count = count($phones);

            $v['new_fan_rate'] = $fan_count->fan_count>0 ? round( $new_orderfan_count / $fan_count->fan_count, 4 ) : '';
            //客单价
            $v['order_amount_avg'] = $v['order_count'] > 0 ? round( $v['order_amount'] / $v['order_count'], 4 ) : '';

            $v['new_orderfan_count'] = $new_orderfan_count;
        }

        $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/salerTargetProgress', ['result'=>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,  
            'teamlist'          =>$teamList,  
            'adminlist'         =>$adminList,  
            'team_id'           =>$team_id,  
            'admin_id'          =>$admin_id,  
            'stime'             =>$mstime,  
            'etime'             =>$metime,  
        ]);
    }

    /**
     * @return \Illuminate\View\View
     */
    public function salerTargetCreate()
    {
        $adminList = DB::table('admin')->select('id', 'realname', 'username')->where('id','>', 1)->get();
        $adminList = json_decode(json_encode($adminList), true);
        $teamList = DB::table('teams')->select('id', 'name')->get();
        $teamList = json_decode(json_encode($teamList), true);
        return view('statistics/salerTargetCreate',['adminlist'=>$adminList, 'teamlist'=>$teamList]);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function salerTargetStore(Request $request)
    {
        $admin_id = intval($request->input('admin_id'));
        $this->validate($request, [
            'admin_id' => 'required|integer|min:1',           
            'month' => 'required|unique:saler_targets,month,null,month,admin_id,'.$admin_id,           
            'amount' => 'required',           
        ], [
            'admin_id.required' => '销售不能为空',
            'admin_id.min' => '销售不能为空',           
            'month.required' => '日期不能为空',
            'month.unique' => '已经添加过',
            'amount.required' => '销售额不能为空',
            
        ]);
        $salerTargets = new SalerTargets();
        $salerTargets->admin_id = intval($request->input('admin_id'));
        $salerTargets->month = trim($request->input('month'));
        $salerTargets->amount = trim($request->input('amount'));

        $salerTargets->save();
        return redirect('/admin/statistics/salerTargetProgress')->with('info', '添加成功');
    }

    /**
     * @param $id
     * @return \Illuminate\View\View
     */
    public function salerTargetEdit($id)
    {
        $salerTargets = salerTargetss::findOrFail($id);
        return view('salerTargets/edit', ['salerTargets' => $salerTargets, 'id'=>$id]);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function salerTargetUpdate(Request $request)
    {
        $id = (int)$request->input('id');
        $this->validate($request, [
            'url' => 'required|unique:salerTargetss,url,'.$id.',id',           
        ], [
            'url.required' => '模板链接不能为空',
            'url.unique' => '该模板链接已存在',
            
        ]);

        $salerTargets = salerTargetss::findOrFail($id);
        $salerTargets->url = trim($request->input('url'));
        $salerTargets->note = trim($request->input('note'));
    
        //图片上传 阿里云oss       
        if ($request->hasFile('img') && $request->file('img')->isValid()) {
            $file = $request->file('img');
            $ossClient=new oss();
            // 上传阿里云
            $file = $ossClient->upload($file->getClientOriginalExtension(), $file->getRealPath(), 'upload/seafoodPic'.date("Y-m-d",time()).'/'.date('His'));
            $img=$file['oss-request-url'];
            $salerTargets->img=str_replace("kx-youhuiquan.oss-cn-beijing.aliyuncs.com","imgs.726p.com",$img);
        }

        $salerTargets->save();
        return redirect('/admin/salerTargets/index')->with('info', '修改模板成功');
    }

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

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

        $result = DB::table('saler_targets')->where(function($query) use($admin_id, $sale_ids, $mstime, $metime){
            if($admin_id) $query->where('admin_id', $admin_id);
            if(!empty($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if(!empty($mstime)) $query->where('month', '>=', $mstime);
            if(!empty($metime)) $query->where('month', '<=', $metime);
        })->orderBy('month', 'desc')->get();

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

        //月份天数数组：
        $m_day = [1=>31,2=>28,3=>31,4=>30,5=>31,6=>30,7=>31,8=>31,9=>30,10=>31,11=>30,12=>31];
        if(date('Y') % 4 == 0){
            $m_day[2] = 29;
        }
        $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'));

            //总单数
            $order = Order::select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('admin_id', $v['admin_id'])->where('is_del', 0)->where('createTime','>=',$m_stime)->where('createTime', '<', $m_etime)->first();
            $v['order_count'] = $order->order_count;
            $v['order_amount'] = $order->order_amount;
            //销售额完成率
            $v['target_rate'] = $v['amount']>0 ? round( $order->order_amount / $v['amount'] , 4) : '';
            //时间进度
            //当前月
            $tmonth = date('Y-m');
            $m = date('m');
            $d = date('d');
            $days = $m_day[$m];
            if( $v['month'] == $tmonth ){               
                $v['time_rate'] = round($d / $days, 4);
                //剩余每天任务
                if( $v['target_rate'] >= 1){
                    $v['day_remain'] = '';
                }else{
                    $v['day_remain'] = round( ($v['amount'] - $order->order_amount) / $days, 4 );
                }
            }elseif( $v['month'] < $tmonth ){
                $v['time_rate'] = 1;
                $v['day_remain'] = '';
            }elseif( $v['month'] > $tmonth ){
                $v['time_rate'] = 0;
                $v['day_remain'] = round( $v['amount'] / $days, 4 );
            }

            //粉数数据
            $fan_count = DB::table('cust_day_detail')->select(DB::raw('sum(fan_add) as fan_count, sum(new_reply) as new_reply_count, sum(new_consult) as new_consult_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['new_reply_count'] = $fan_count->new_reply_count;
            $v['new_consult_count'] = $fan_count->new_consult_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('username');
            }

            //新粉成单            
            $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)->where('cu.fanTime','>=',$m_stime)->where('cu.fanTime', '<', $m_etime)->where('order.admin_id', $v['admin_id'])->first();
            $v['new_order_count'] = $new_order->order_count;
            $v['new_order_amount'] = $new_order->order_amount;
            //老粉成单
            $v['old_order_count'] = $order->order_count - $new_order->order_count;
            $v['old_order_amount'] = $order->order_amount - $new_order->order_amount;
            //复购单数
            //总单数
            $fugou_order = Order::select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('admin_id', $v['admin_id'])->where('is_del', 0)->where('createTime','>=',$m_stime)->where('createTime', '<', $m_etime)->where('is_fugou', 1)->first();
            $v['fugou_order_count'] = $fugou_order->order_count;
            $v['fugou_order_amount'] = $fugou_order->order_amount;

            //新粉转化
            $phones = DB::table('customers')->select(DB::raw('distinct(customers.phone)'))->leftJoin('order', 'customers.phone', '=', 'order.receiverMobile')->where('customers.fanTime','>=',$m_stime)->where('customers.fanTime','<',$m_etime)->where('order.admin_id', $v['admin_id'])->get();

            $new_orderfan_count = count($phones);

            $v['new_fan_rate'] = $fan_count->fan_count>0 ? round( $new_orderfan_count / $fan_count->fan_count, 4 ) : '';
            //客单价
            $v['order_amount_avg'] = $v['order_count'] > 0 ? round( $v['order_amount'] / $v['order_count'], 4 ) : '';

            $v['new_orderfan_count'] = $new_orderfan_count;
        }

        $indexKey = ['admin_name','month','order_count','order_amount','amount','target_rate','time_rate','day_remain', 'fan_count', 'new_reply_count', 'new_consult_count', 'new_order_count', 'new_order_amount', 'old_order_count', 'old_order_amount', 'fugou_order_count', 'fugou_order_amount', 'order_count', 'order_amount', 'new_orderfan_count', 'new_fan_rate', 'order_amount_avg'];
        $title = ['销售', '月份', '总订单', '总销售额', '目标销售额','销售额完成率', '时间进度', '剩余每天销售任务', '粉丝数', '初步响应', '单品咨询', '新粉当日成单', '新粉流水', '老粉成单', '老粉流水', '复购成单', '复购流水', '总订单', '总流水', '新粉下单人数', '新粉丝转化', '客单价'];
        
        $filename = 'xiaoshouyuebao_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 仓库发货财务报表
     */
    public function warehouseFinanceList(Request $request){
        $warehouse = (int)$request->input('warehouse');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }
        $offset = ($page-1) * $pageSize;

        $count =Order::select('warehouse')->where(function($query) use($stime, $etime, $warehouse){
            if($stime) $query->where('send_time', '>=', $stime);
            if($etime) $query->where('send_time', '<=', $etime);
            if($warehouse>0) $query->where('warehouse', '=', $warehouse);
        })->whereNotNull('send_time')->where('warehouse','>',0)->where('logistics_id','>','')->where('is_del', 0)->where('status','>=',2)->groupBy('warehouse')->groupBy('send_time')->get();      
        $count = count($count);
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }


        $result = Order::select(DB::raw('send_time,warehouse,sum(cost) as goods_cost, sum(freight_cost) as freight_cost, count(1) as order_count'))->where(function($query) use($stime, $etime, $warehouse){
            if($stime) $query->where('send_time', '>=', $stime);
            if($etime) $query->where('send_time', '<=', $etime);
            if($warehouse>0) $query->where('warehouse', '=', $warehouse);
        })->whereNotNull('send_time')->where('warehouse','>',0)->where('logistics_id','>','')->where('is_del', 0)->where('status','>=',2)->groupBy('warehouse')->groupBy('send_time')->orderBy('send_time', 'desc')->offset($offset)->limit($pageSize)->get();

        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        foreach($result as $k=>&$item){
            $item->warehouse = $wareArr[$item->warehouse];
        }

        //汇总
        $total = Order::select(DB::raw('sum(cost) as goods_cost, sum(freight_cost) as freight_cost, count(1) as order_count'))->where(function($query) use($stime, $etime, $warehouse){
            if($stime) $query->where('send_time', '>=', $stime);
            if($etime) $query->where('send_time', '<=', $etime);
            if($warehouse>0) $query->where('warehouse', '=', $warehouse);
        })->whereNotNull('send_time')->where('warehouse','>',0)->where('logistics_id','>','')->where('is_del', 0)->where('status','>=',2)->first();


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

    }   
    /**
     * 仓库发货财务报表导出
     */
    public function warehouseFinanceList_export(Request $request){
        $warehouse = (int)$request->input('warehouse');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
       
        $result = Order::select(DB::raw('send_time,warehouse,sum(cost) as goods_cost, sum(freight_cost) as freight_cost, count(1) as order_count'))->where(function($query) use($stime, $etime, $warehouse){
            if($stime) $query->where('send_time', '>=', $stime);
            if($etime) $query->where('send_time', '<=', $etime);
            if($warehouse>0) $query->where('warehouse', '=', $warehouse);
        })->whereNotNull('send_time')->where('warehouse','>',0)->where('logistics_id','>','')->where('is_del', 0)->where('status','>=',2)->groupBy('warehouse')->groupBy('send_time')->orderBy('send_time', 'desc')->get();

        $wareArr = array(
            0 => '其他',
            1 => 'A仓',
            2 => 'B仓',
            3 => 'C仓',
        );
        foreach($result as $k=>&$item){
            $item->warehouse = $wareArr[$item->warehouse];
        }

        $indexKey = ['send_time','warehouse','order_count','goods_cost','freight_cost'];
        $title = ['日期', '仓库', '单数', '货品成本', '物流成本'];
        
        $filename = 'cangkufahuo_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    } 

    /**
     * 地域roi7 15,30,45,60日汇总
     */
    public function districtRoiAllTotal(Request $request){
        $only_roi = (int)$request->input('only_roi');
        $sort = (int)$request->input('sort'); //排序 0：7日降序 1：15日降序 2：30日降序 3：45日降序 4：60日降序
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }

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

        if($city){
            $city = str_replace('市', '', $city); //单独搜城市不走redis 不用排序
        }else{
            //从redis取数据
            $redis_key = 'seafood.districtRoiAllTotal';
            $redisData = Redis::get($redis_key);
            if(!empty($redisData)){
                $result = json_decode($redisData, true);
                switch ($sort) {
                    case '0':
                        $sort_c = 'total_roi';
                        break;
                    case '1':
                        $sort_c = 'total_roi15_sort';
                        break;
                    case '2':
                        $sort_c = 'total_roi30_sort';
                        break;
                    case '3':
                        $sort_c = 'total_roi45_sort';
                        break;
                    case '4':
                        $sort_c = 'total_roi60_sort';
                        break;
                    default:
                        $sort_c = 'total_roi';
                        break;
                }
                if($sort>0){
                    $sortArr=array_column($result, $sort_c);
                    array_multisort($sortArr, SORT_DESC,  $result);
                }
                $count = count($result);
                $result = array_slice($result, $offset, $pageSize);
                if ($count > 1) {
                    // 总页数
                    $pages = ceil($count/$pageSize);
                }else{
                    // 总页数
                    $pages = 1;
                }
                return view('statistics/districtRoiAllTotal', ['result' =>$result,
                    'page'              =>$page,
                    'count'             =>$count,
                    'pages'             =>$pages,                                    
                    'city'              =>$city,                                        
                    'only_roi'          =>$only_roi,                                        
                    'sort'          =>$sort,                                        
                ]);

            }                       
        }

        $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['total_roi_sort'] = rtrim($v['total_roi'], '%') * 100;
            //数据格式化
            $v['order_amount'] = round($v['sum_order_amount'],2);

            //15日
            $data15 = DistrictRoi15::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data15->cost)){
                $v['cost15'] = $data15->cost;
                $v['order_count15'] = $data15->order_count;
                $v['order_amount15'] = round($data15->sum_order_amount,2);
                $v['total_roi15'] = $v['cost15']>0 ? round($data15->sum_order_amount / $v['cost15'], 4) * 100 .'%' : '';
                $v['total_roi15_sort'] = rtrim($v['total_roi15'], '%') * 100;
            }else{
                $v['cost15'] = '';
                $v['order_count15'] = '';
                $v['order_amount15'] = '';
                $v['total_roi15'] = '';
                $v['total_roi15_sort'] = 0;
            }

            //30日
            $data30 = DistrictRoi30::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data30->cost)){
                $v['cost30'] = $data30->cost;
                $v['order_count30'] = $data30->order_count;
                $v['order_amount30'] = round($data30->sum_order_amount,2);
                $v['total_roi30'] = $v['cost30']>0 ? round($data30->sum_order_amount / $v['cost30'], 4) * 100 .'%' : '';
                $v['total_roi30_sort'] = rtrim($v['total_roi30'], '%') * 100;
            }else{
                $v['cost30'] = '';
                $v['order_count30'] = '';
                $v['order_amount30'] = '';
                $v['total_roi30'] = '';
                $v['total_roi30_sort'] = 0;
            }

            //45日
            $data45 = DistrictRoi45::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data45->cost)){
                $v['cost45'] = $data45->cost;
                $v['order_count45'] = $data45->order_count;
                $v['order_amount45'] = round($data45->sum_order_amount,2);
                $v['total_roi45'] = $v['cost45']>0 ? round($data45->sum_order_amount / $v['cost45'], 4) * 100 .'%' : '';
                $v['total_roi45_sort'] = rtrim($v['total_roi45'], '%') * 100;
            }else{
                $v['cost45'] = '';
                $v['order_count45'] = '';
                $v['order_amount45'] = '';
                $v['total_roi45'] = '';
                $v['total_roi45_sort'] = 0;
            }

            //60日
            $data60 = DistrictRoi60::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data60->cost)){
                $v['cost60'] = $data60->cost;
                $v['order_count60'] = $data60->order_count;
                $v['order_amount60'] = round($data60->sum_order_amount,2);
                $v['total_roi60'] = $v['cost60']>0 ? round($data60->sum_order_amount / $v['cost60'], 4) * 100 .'%' : '';
                $v['total_roi60_sort'] = rtrim($v['total_roi60'], '%') * 100;
            }else{
                $v['cost60'] = '';
                $v['order_count60'] = '';
                $v['order_amount60'] = '';
                $v['total_roi60'] = '';
                $v['total_roi60_sort'] = 0;
            }

        }

        $count = count($result);
        if(!$city){
            Redis::set($redis_key, json_encode($result));
            //计算今日剩余时间
            $_time = strtotime(date('Y-m-d'). '23:59:59') - time();
            Redis::expire($redis_key, $_time);
            switch ($sort) {
                case '0':
                    $sort_c = 'total_roi';
                    break;
                case '1':
                    $sort_c = 'total_roi15_sort';
                    break;
                case '2':
                    $sort_c = 'total_roi30_sort';
                    break;
                case '3':
                    $sort_c = 'total_roi45_sort';
                    break;
                case '4':
                    $sort_c = 'total_roi60_sort';
                    break;
                default:
                    $sort_c = 'total_roi';
                    break;
            }
            if($sort>0){
                $sortArr=array_column($result, $sort_c);
                array_multisort($sortArr, SORT_DESC,  $result);  
            }          
            $result = array_slice($result, $offset, $pageSize);
        }

        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }
        return view('statistics/districtRoiAllTotal', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'city'              =>$city,                                        
            'only_roi'          =>$only_roi,                                        
            'sort'          =>$sort,                                        
            ]);

    }

    /**
     * 地域roi汇总导出
     */
    public function districtRoiAllTotal_export(Request $request){
        $only_roi = (int)$request->input('only_roi');
        $sort = (int)$request->input('sort'); //排序 0：7日降序 1：15日降序 2：30日降序 3：45日降序 4：60日降序
        $city = $request->input('city');
    
        if($city){
            $city = str_replace('市', '', $city); //单独搜城市不走redis 不用排序
        }else{
            //从redis取数据
            $redis_key = 'seafood.districtRoiAllTotal';
            $redisData = Redis::get($redis_key);
            if(!empty($redisData)){
                $result = json_decode($redisData, true);
                switch ($sort) {
                    case '0':
                        $sort_c = 'total_roi';
                        break;
                    case '1':
                        $sort_c = 'total_roi15_sort';
                        break;
                    case '2':
                        $sort_c = 'total_roi30_sort';
                        break;
                    case '3':
                        $sort_c = 'total_roi45_sort';
                        break;
                    case '4':
                        $sort_c = 'total_roi60_sort';
                        break;
                    default:
                        $sort_c = 'total_roi';
                        break;
                }
                if($sort>0){
                    $sortArr=array_column($result, $sort_c);
                    array_multisort($sortArr, SORT_DESC,  $result);
                }
            
                if($only_roi == 1){
                    $indexKey = ['city','total_roi','total_roi15','total_roi30','total_roi45','total_roi60'];
                    $title = ['城市', '7日累计ROI', '15日累计ROI', '30日累计ROI', '45日累计ROI', '60日累计ROI'];
                }
                else{
                    $indexKey = ['city','cost','order_count','order_amount','total_roi','cost15','order_count15','order_amount15','total_roi15','cost30','order_count30','order_amount30','total_roi30','cost45','order_count45','order_amount45','total_roi45','cost60','order_count60','order_amount60','total_roi60'];
                    $title = ['城市', '7日累计投放成本', '7日累计成单数', '7日累计收入', '7日累计ROI', '15日累计投放成本', '15日累计成单数', '15日累计收入', '15日累计ROI', '30日累计投放成本', '30日累计成单数', '30日累计收入', '30日累计ROI', '45日累计投放成本', '45日累计成单数', '45日累计收入', '45日累计ROI', '60日累计投放成本', '60日累计成单数', '60日累计收入', '60日累计ROI'];
                }
                $filename = 'districtRoiAll_'.date('Y-m-d_H').'.xlsx';
                return Order::export_excel($result, $filename, $indexKey, $title);
            }                       
        }

        $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['total_roi_sort'] = rtrim($v['total_roi'], '%') * 100;
            //数据格式化
            $v['order_amount'] = round($v['sum_order_amount'],2);

            //15日
            $data15 = DistrictRoi15::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data15->cost)){
                $v['cost15'] = $data15->cost;
                $v['order_count15'] = $data15->order_count;
                $v['order_amount15'] = round($data15->sum_order_amount,2);
                $v['total_roi15'] = $v['cost15']>0 ? round($data15->sum_order_amount / $v['cost15'], 4) * 100 .'%' : '';
                $v['total_roi15_sort'] = rtrim($v['total_roi15'], '%') * 100;
            }else{
                $v['cost15'] = '';
                $v['order_count15'] = '';
                $v['order_amount15'] = '';
                $v['total_roi15'] = '';
                $v['total_roi15_sort'] = 0;
            }

            //30日
            $data30 = DistrictRoi30::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data30->cost)){
                $v['cost30'] = $data30->cost;
                $v['order_count30'] = $data30->order_count;
                $v['order_amount30'] = round($data30->sum_order_amount,2);
                $v['total_roi30'] = $v['cost30']>0 ? round($data30->sum_order_amount / $v['cost30'], 4) * 100 .'%' : '';
                $v['total_roi30_sort'] = rtrim($v['total_roi30'], '%') * 100;
            }else{
                $v['cost30'] = '';
                $v['order_count30'] = '';
                $v['order_amount30'] = '';
                $v['total_roi30'] = '';
                $v['total_roi30_sort'] = 0;
            }

            //45日
            $data45 = DistrictRoi45::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data45->cost)){
                $v['cost45'] = $data45->cost;
                $v['order_count45'] = $data45->order_count;
                $v['order_amount45'] = round($data45->sum_order_amount,2);
                $v['total_roi45'] = $v['cost45']>0 ? round($data45->sum_order_amount / $v['cost45'], 4) * 100 .'%' : '';
                $v['total_roi45_sort'] = rtrim($v['total_roi45'], '%') * 100;
            }else{
                $v['cost45'] = '';
                $v['order_count45'] = '';
                $v['order_amount45'] = '';
                $v['total_roi45'] = '';
                $v['total_roi45_sort'] = 0;
            }

            //60日
            $data60 = DistrictRoi60::select(DB::raw('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('city', $v['city'])->first();
            if(isset($data60->cost)){
                $v['cost60'] = $data60->cost;
                $v['order_count60'] = $data60->order_count;
                $v['order_amount60'] = round($data60->sum_order_amount,2);
                $v['total_roi60'] = $v['cost60']>0 ? round($data60->sum_order_amount / $v['cost60'], 4) * 100 .'%' : '';
                $v['total_roi60_sort'] = rtrim($v['total_roi60'], '%') * 100;
            }else{
                $v['cost60'] = '';
                $v['order_count60'] = '';
                $v['order_amount60'] = '';
                $v['total_roi60'] = '';
                $v['total_roi60_sort'] = 0;
            }

        }

        if(!$city){
            Redis::set($redis_key, json_encode($result));
            //计算今日剩余时间
            $_time = strtotime(date('Y-m-d'). '23:59:59') - time();
            Redis::expire($redis_key, $_time);
            switch ($sort) {
                case '0':
                    $sort_c = 'total_roi';
                    break;
                case '1':
                    $sort_c = 'total_roi15_sort';
                    break;
                case '2':
                    $sort_c = 'total_roi30_sort';
                    break;
                case '3':
                    $sort_c = 'total_roi45_sort';
                    break;
                case '4':
                    $sort_c = 'total_roi60_sort';
                    break;
                default:
                    $sort_c = 'total_roi';
                    break;
            }
            if($sort>0){
                $sortArr=array_column($result, $sort_c);
                array_multisort($sortArr, SORT_DESC,  $result);  
            }          
        }

        if($only_roi == 1){
            $indexKey = ['city','total_roi','total_roi15','total_roi30','total_roi45','total_roi60'];
            $title = ['城市', '7日累计ROI', '15日累计ROI', '30日累计ROI', '45日累计ROI', '60日累计ROI'];
        }
        else{
            $indexKey = ['city','cost','order_count','order_amount','total_roi','cost15','order_count15','order_amount15','total_roi15','cost30','order_count30','order_amount30','total_roi30','cost45','order_count45','order_amount45','total_roi45','cost60','order_count60','order_amount60','total_roi60'];
            $title = ['城市', '7日累计投放成本', '7日累计成单数', '7日累计收入', '7日累计ROI', '15日累计投放成本', '15日累计成单数', '15日累计收入', '15日累计ROI', '30日累计投放成本', '30日累计成单数', '30日累计收入', '30日累计ROI', '45日累计投放成本', '45日累计成单数', '45日累计收入', '45日累计ROI', '60日累计投放成本', '60日累计成单数', '60日累计收入', '60日累计ROI'];
        }
        $filename = 'districtRoiAll_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 销售粉丝复购率
     */
    public function salerOrderFugou(Request $request){
        //获取销售
        $saler_id = (int)$request->input('admin_id');
        $team_id = (int)$request->input('team_id');
        $sort = (int)$request->input('sort');
        switch ($sort) {
            case '1':
                $_sort_name = 'cust_fugou_rate_sort';
                break;
            case '2':
                $_sort_name = 'cust_order_rate';
                break;           
            default:
                $_sort_name = 'fugou_rate_sort';
                break;
        }

        //假如有团队筛选，检索销售队员
        $saler_ids = null;
        if($saler_id>0){
            $saler_ids = [$saler_id];
        }elseif($team_id>0){
            $saler_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }else{
            $saler_ids = DB::table('admin')->whereIn('team_id', [1,3,5])->lists('id');
        }

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

        $offset = ($page-1) * $pageSize;
        $count = count($saler_ids);
    
        //销售名字
        $name_arr = DB::table('admin')->whereIn('id', $saler_ids)->lists('realname', 'id');
        $result = array();
        foreach($saler_ids as $k=>$admin_id){
            $result[$k]['admin_id'] = $admin_id;
            //pv
            $temp = TemplatesLog::select(DB::raw('count(1) as tcount, count(if(type=1,true,null)) as pv_count'))->where('admin_id',$admin_id)->first();
            $result[$k]['tcount'] = $temp->tcount;
            $result[$k]['pv_count'] = $temp->pv_count;
            //加粉
            $custDetail = CustDetail::select(DB::raw('sum(fan_add) as fan_add, admin_name'))->where('admin_id', $admin_id)->where('is_del', 0)->first();
            if(!empty($custDetail)){
                $result[$k]['fan_add'] = $custDetail->fan_add;             
            }else{
                $result[$k]['fan_add'] = '';             
            }
            $result[$k]['admin_name'] = $name_arr[$admin_id];
            $result[$k]['long_count'] = $result[$k]['tcount'] - $result[$k]['pv_count'];

            //点击率
            $result[$k]['click_rate'] = $result[$k]['pv_count']>0 && $result[$k]['long_count']>0 ? round( $result[$k]['long_count'] / $result[$k]['pv_count'], 4 ) * 100 . '%' : '';
            $result[$k]['change_rate'] = $result[$k]['long_count']>0 && $result[$k]['fan_add']>0 ? round( $result[$k]['fan_add'] / $result[$k]['long_count'], 4 ) * 100 . '%' : '';
            //下单数据统计
            $order = Order::select(DB::raw('count(1) as order_count,sum(receivedAmount) as order_amount,count(distinct(receiverMobile)) as cust_count'))->where('admin_id', $admin_id)->where('is_able', 1)->where('is_del', 0)->first();
            $result[$k]['order_count'] = $order->order_count;
            $result[$k]['order_amount'] = $order->order_amount;
            //下单用户数
            $customer_count = $order->cust_count;
            //复购用户数 = 下单超过一单的用户数
            $order_cust_fugou = Order::select(DB::raw('count(1) as counts,receiverMobile'))->where('admin_id', $admin_id)->where('is_able', 1)->where('is_del', 0)->groupBy('receiverMobile')->having('counts', '>', 1)->get();
            $customer_fugou_count = count($order_cust_fugou);

            //复购单= 总单-用户数
            $fugou_count = $order->order_count - $order->cust_count;
            $no_fugou_count = $order->order_count - $fugou_count;
            //复购率
            $fugou_rate = $no_fugou_count>0 ? round( $fugou_count / $no_fugou_count, 4 ) * 100 . '%' : '';
            $cust_fugou_rate = $customer_count>0 ? round( $customer_fugou_count / $customer_count, 4 ) * 100 . '%' : '';

            $result[$k]['customer_count'] = $customer_count;
            $result[$k]['customer_fugou_count'] = $customer_fugou_count;
            $result[$k]['fugou_rate'] = $fugou_rate;
            $result[$k]['fugou_count'] = $fugou_count;
            $result[$k]['cust_fugou_rate'] = $cust_fugou_rate;
            $result[$k]['fugou_rate_sort'] = $no_fugou_count>0 ? round( $fugou_count / $no_fugou_count, 4 ) * 10000 : 0;
            $result[$k]['cust_fugou_rate_sort'] = $customer_count>0 ? round( $customer_fugou_count / $customer_count, 4 ) * 10000 : 0;
            //用户转化率
            $result[$k]['cust_order_rate'] = $result[$k]['fan_add']>0 ? round( $customer_count / $result[$k]['fan_add'], 4 ) * 100 . '%' : '';

        }

        //按复购率排序
        $fugou_rate_arr = array_column($result, $_sort_name);
        array_multisort($fugou_rate_arr, SORT_DESC, $result);
        $result = array_slice($result, $offset, $pageSize);

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

        $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/salerOrderFugou', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                    
            'admin_id'          =>$saler_id,                                        
            'team_id'           =>$team_id,                                                                              
            'teamlist'           =>$teamList,                                                                              
            'adminlist'           =>$adminList,                                                                              
            'sort'           =>$sort,                                                                              
            ]);

    }

    /**
     * 销售粉丝复购率导出
     */
    public function salerOrderFugou_export(Request $request){
        //获取销售
        $saler_id = (int)$request->input('admin_id');
        $team_id = (int)$request->input('team_id');
        $sort = (int)$request->input('sort');
        switch ($sort) {
            case '1':
                $_sort_name = 'cust_fugou_rate_sort';
                break;
            case '2':
                $_sort_name = 'cust_order_rate';
                break;           
            default:
                $_sort_name = 'fugou_rate_sort';
                break;
        }

        //假如有团队筛选，检索销售队员
        $saler_ids = null;
        if($saler_id>0){
            $saler_ids = [$saler_id];
        }elseif($team_id>0){
            $saler_ids = DB::table('admin')->where('team_id', $team_id)->lists('id');
        }else{
            $saler_ids = DB::table('admin')->whereIn('team_id', [1,3,5])->lists('id');
        }

        //销售名字
        $name_arr = DB::table('admin')->whereIn('id', $saler_ids)->lists('realname', 'id');
        $result = array();
        foreach($saler_ids as $k=>$admin_id){
            $result[$k]['admin_id'] = $admin_id;
            //pv
            $temp = TemplatesLog::select(DB::raw('count(1) as tcount, count(if(type=1,true,null)) as pv_count'))->where('admin_id',$admin_id)->first();
            $result[$k]['tcount'] = $temp->tcount;
            $result[$k]['pv_count'] = $temp->pv_count;
            //加粉
            $custDetail = CustDetail::select(DB::raw('sum(fan_add) as fan_add, admin_name'))->where('admin_id', $admin_id)->where('is_del', 0)->first();
            if(!empty($custDetail)){
                $result[$k]['fan_add'] = $custDetail->fan_add;             
            }else{
                $result[$k]['fan_add'] = '';             
            }
            $result[$k]['admin_name'] = $name_arr[$admin_id];
            $result[$k]['long_count'] = $result[$k]['tcount'] - $result[$k]['pv_count'];

            //点击率
            $result[$k]['click_rate'] = $result[$k]['pv_count']>0 && $result[$k]['long_count']>0 ? round( $result[$k]['long_count'] / $result[$k]['pv_count'], 4 ) * 100 . '%' : '';
            $result[$k]['change_rate'] = $result[$k]['long_count']>0 && $result[$k]['fan_add']>0 ? round( $result[$k]['fan_add'] / $result[$k]['long_count'], 4 ) * 100 . '%' : '';
            //下单数据统计
            $order = Order::select(DB::raw('count(1) as order_count,sum(receivedAmount) as order_amount,count(distinct(receiverMobile)) as cust_count'))->where('admin_id', $admin_id)->where('is_able', 1)->where('is_del', 0)->first();
            $result[$k]['order_count'] = $order->order_count;
            $result[$k]['order_amount'] = $order->order_amount;
            //下单用户数
            $customer_count = $order->cust_count;
            //复购用户数 = 下单超过一单的用户数
            $order_cust_fugou = Order::select(DB::raw('count(1) as counts,receiverMobile'))->where('admin_id', $admin_id)->where('is_able', 1)->where('is_del', 0)->groupBy('receiverMobile')->having('counts', '>', 1)->get();
            $customer_fugou_count = count($order_cust_fugou);

            //复购单= 总单-用户数
            $fugou_count = $order->order_count - $order->cust_count;
            $no_fugou_count = $order->order_count - $fugou_count;
            //复购率
            $fugou_rate = $no_fugou_count>0 ? round( $fugou_count / $no_fugou_count, 4 ) * 100 . '%' : '';
            $cust_fugou_rate = $customer_count>0 ? round( $customer_fugou_count / $customer_count, 4 ) * 100 . '%' : '';

            $result[$k]['customer_count'] = $customer_count;
            $result[$k]['customer_fugou_count'] = $customer_fugou_count;
            $result[$k]['fugou_rate'] = $fugou_rate;
            $result[$k]['fugou_count'] = $fugou_count;
            $result[$k]['cust_fugou_rate'] = $cust_fugou_rate;
            $result[$k]['fugou_rate_sort'] = $no_fugou_count>0 ? round( $fugou_count / $no_fugou_count, 4 ) * 10000 : 0;
            $result[$k]['cust_fugou_rate_sort'] = $customer_count>0 ? round( $customer_fugou_count / $customer_count, 4 ) * 10000 : 0;
            //用户转化率
            $result[$k]['cust_order_rate'] = $result[$k]['fan_add']>0 ? round( $customer_count / $result[$k]['fan_add'], 4 ) * 100 . '%' : '';
        }

        //按复购率排序
        $fugou_rate_arr = array_column($result, $_sort_name);
        array_multisort($fugou_rate_arr, SORT_DESC, $result);

        //$indexKey = ['admin_name','pv_count','long_count','fan_add','change_rate','customer_count','customer_fugou_count','order_count','order_amount','fugou_rate'];
        $indexKey = ['admin_name','fan_add','customer_count','cust_order_rate','order_count','order_amount', 'fugou_count', 'fugou_rate', 'customer_fugou_count', 'cust_fugou_rate'];
        //$title = ['销售', 'PV量', '累计预估加粉数', '累计加粉数', '加粉转化率', '下单用户数', '复购用户数', '累计下单数', '累计销售额', '复购率'];
        $title = ['销售', '累计加粉数', '下单用户数', '用户转化率', '累计下单数', '累计销售额', '复购订单数', '订单复购率', '复购用户数', '用户复购率'];
        $filename = 'xiaoshoufugou_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);  
    }

    /**
     * 每日累计汇总数据
     */
    public function dayGrandTotal(Request $request){
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }
        $_start = '2019-09-04';
        $data = array();
        if($page == 1){
            $pageSize = $pageSize - 1;
            $offset = ($page-1) * $pageSize;
            //今日数据实时计算
            $idate = date('Y-m-d');
            $data['idate'] = $idate;
            //总投入
            $data['throw_cost'] = CustTotal::where('is_del',0)->where('dtime','>=',$_start)->sum('total_cost');
            //订单信息
            $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('createTime','>=',$_start)->where('is_del',0)->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['fan_count'] = CustDetail::where('is_del',0)->where('dtime','>=',$_start)->sum('fan_add');    
            //毛利 = 总销售额-总商品成本-总运费-总售后
            $data['profit'] = $order->order_amount - $order->goods_cost - $order->freight_cost - $order->aftersale_cost - $data['throw_cost'] + $order->refund_fee;
            $data['roi'] = $data['throw_cost']>0?  round($order->order_amount / $data['throw_cost'], 4) * 100 .'%' : '';
            //总新粉单数            
            $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.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'];
            //总复购率
            $data['fugou_rate'] = $data['cust_count']>0 ? round($data['fugou_order_count'] / $data['cust_count'], 4) : '';

            //7 - 60 roi汇总
            $data['cost7'] = DB::table('roi_total')->where('type', 1)->sum('cost');
            $data['order_count7'] = DB::table('roi_total')->where('type', 1)->sum('order_count');
            $data['order_amount7'] = DB::table('roi_total')->where('type', 1)->sum('order_amount');
            $data['roi7'] = $data['cost7']>0?  round($data['order_amount7'] / $data['cost7'], 4) * 100 .'%' : '';

            $data['cost15'] = DB::table('roi_total')->where('type', 2)->sum('cost');
            $data['order_count15'] = DB::table('roi_total')->where('type', 2)->sum('order_count');
            $data['order_amount15'] = DB::table('roi_total')->where('type', 2)->sum('order_amount');
            $data['roi15'] = $data['cost15']>0?  round($data['order_amount15'] / $data['cost15'], 4) * 100 .'%' : '';

            $data['cost30'] = DB::table('roi_total')->where('type', 3)->sum('cost');
            $data['order_count30'] = DB::table('roi_total')->where('type', 3)->sum('order_count');
            $data['order_amount30'] = DB::table('roi_total')->where('type', 3)->sum('order_amount');
            $data['roi30'] = $data['cost30']>0?  round($data['order_amount30'] / $data['cost30'], 4) * 100 .'%' : '';

            $data['cost45'] = DB::table('roi_total')->where('type', 4)->sum('cost');
            $data['order_count45'] = DB::table('roi_total')->where('type', 4)->sum('order_count');
            $data['order_amount45'] = DB::table('roi_total')->where('type', 4)->sum('order_amount');
            $data['roi45'] = $data['cost45']>0?  round($data['order_amount45'] / $data['cost45'], 4) * 100 .'%' : '';

            $data['cost60'] = DB::table('roi_total')->where('type', 5)->sum('cost');
            $data['order_count60'] = DB::table('roi_total')->where('type', 5)->sum('order_count');
            $data['order_amount60'] = DB::table('roi_total')->where('type', 5)->sum('order_amount');
            $data['roi60'] = $data['cost60']>0?  round($data['order_amount60'] / $data['cost60'], 4) * 100 .'%' : '';
        }else{
            $offset = ($page-1) * $pageSize - 1;
        }
        //查询历史数据
        $count = DB::table('day_grand_total')->count();
        $count = $count + 1;
        $result = DB::table('day_grand_total')->orderBy('idate', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $v['roi'] = $v['throw_cost']>0 ? round($v['order_amount'] / $v['throw_cost'], 4) * 100 .'%' : '';
            $v['fugou_rate'] = $v['cust_count']>0 ? round($v['fugou_order_count'] / $v['cust_count'], 4) : '';
            $v['roi7'] = $v['cost7']>0?  round($v['order_amount7'] / $v['cost7'], 4) * 100 .'%' : '';
            $v['roi15'] = $v['cost15']>0?  round($v['order_amount15'] / $v['cost15'], 4) * 100 .'%' : '';
            $v['roi30'] = $v['cost30']>0?  round($v['order_amount30'] / $v['cost30'], 4) * 100 .'%' : '';
            $v['roi45'] = $v['cost45']>0?  round($v['order_amount45'] / $v['cost45'], 4) * 100 .'%' : '';
            $v['roi60'] = $v['cost60']>0?  round($v['order_amount60'] / $v['cost60'], 4) * 100 .'%' : '';
        }
        if($page == 1){
            $result  = array_merge([$data], $result);
        }

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

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

    /**
     * 每日累计汇总数据导出
     */
    public function dayGrandTotal_export(Request $request){
        $_start = '2019-09-04';
        $data = array();
        //今日数据实时计算
        $idate = date('Y-m-d');
        $data['idate'] = $idate;
        //总投入
        $data['throw_cost'] = CustTotal::where('is_del',0)->where('dtime','>=',$_start)->sum('total_cost');
        //订单信息
        $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('createTime','>=',$_start)->where('is_del',0)->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['fan_count'] = CustDetail::where('is_del',0)->where('dtime','>=',$_start)->sum('fan_add');    
        //毛利 = 总销售额-总商品成本-总运费-总售后
        $data['profit'] = $order->order_amount - $order->goods_cost - $order->freight_cost - $order->aftersale_cost - $data['throw_cost'] + $order->refund_fee;
        $data['roi'] = $data['throw_cost']>0?  round($order->order_amount / $data['throw_cost'], 4) * 100 .'%' : '';
        //总新粉单数            
        $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.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'];
        //总复购率
        $data['fugou_rate'] = $data['cust_count']>0 ? round($data['fugou_order_count'] / $data['cust_count'], 4) : '';

        //7 - 60 roi汇总
        $data['cost7'] = DB::table('roi_total')->where('type', 1)->sum('cost');
        $data['order_count7'] = DB::table('roi_total')->where('type', 1)->sum('order_count');
        $data['order_amount7'] = DB::table('roi_total')->where('type', 1)->sum('order_amount');
        $data['roi7'] = $data['cost7']>0?  round($data['order_amount7'] / $data['cost7'], 4) * 100 .'%' : '';

        $data['cost15'] = DB::table('roi_total')->where('type', 2)->sum('cost');
        $data['order_count15'] = DB::table('roi_total')->where('type', 2)->sum('order_count');
        $data['order_amount15'] = DB::table('roi_total')->where('type', 2)->sum('order_amount');
        $data['roi15'] = $data['cost15']>0?  round($data['order_amount15'] / $data['cost15'], 4) * 100 .'%' : '';

        $data['cost30'] = DB::table('roi_total')->where('type', 3)->sum('cost');
        $data['order_count30'] = DB::table('roi_total')->where('type', 3)->sum('order_count');
        $data['order_amount30'] = DB::table('roi_total')->where('type', 3)->sum('order_amount');
        $data['roi30'] = $data['cost30']>0?  round($data['order_amount30'] / $data['cost30'], 4) * 100 .'%' : '';

        $data['cost45'] = DB::table('roi_total')->where('type', 4)->sum('cost');
        $data['order_count45'] = DB::table('roi_total')->where('type', 4)->sum('order_count');
        $data['order_amount45'] = DB::table('roi_total')->where('type', 4)->sum('order_amount');
        $data['roi45'] = $data['cost45']>0?  round($data['order_amount45'] / $data['cost45'], 4) * 100 .'%' : '';

        $data['cost60'] = DB::table('roi_total')->where('type', 5)->sum('cost');
        $data['order_count60'] = DB::table('roi_total')->where('type', 5)->sum('order_count');
        $data['order_amount60'] = DB::table('roi_total')->where('type', 5)->sum('order_amount');
        $data['roi60'] = $data['cost60']>0?  round($data['order_amount60'] / $data['cost60'], 4) * 100 .'%' : '';

        $result = DB::table('day_grand_total')->orderBy('idate', 'desc')->get();
        $result = json_decode(json_encode($result), true); 
        foreach($result as $k=>&$v){
            $v['roi'] = $v['throw_cost']>0 ? round($v['order_amount'] / $v['throw_cost'], 4) * 100 .'%' : '';
            $v['fugou_rate'] = $v['cust_count']>0 ? round($v['fugou_order_count'] / $v['cust_count'], 4) : '';
            $v['roi7'] = $v['cost7']>0?  round($v['order_amount7'] / $v['cost7'], 4) * 100 .'%' : '';
            $v['roi15'] = $v['cost15']>0?  round($v['order_amount15'] / $v['cost15'], 4) * 100 .'%' : '';
            $v['roi30'] = $v['cost30']>0?  round($v['order_amount30'] / $v['cost30'], 4) * 100 .'%' : '';
            $v['roi45'] = $v['cost45']>0?  round($v['order_amount45'] / $v['cost45'], 4) * 100 .'%' : '';
            $v['roi60'] = $v['cost60']>0?  round($v['order_amount60'] / $v['cost60'], 4) * 100 .'%' : '';
        }  
        $result  = array_merge([$data], $result);

        $indexKey = ['idate','throw_cost','cust_count','order_count','order_amount','goods_cost', 'freight_cost', 'aftersale_cost', 'refund_fee', 'profit', 'roi', 'fan_count', 'new_order_count', 'old_order_count', 'fugou_order_count', 'fugou_rate', 'roi7', 'roi15', 'roi30', 'roi45', 'roi60'];
        $title = ['汇总日期', '总投放', '总客户数', '总下单数', '总销售额', '总货品成本', '总运费成本', '总售后', '总退补', '总毛利', '累计ROI', '总加粉数', '总新粉单数', '总老粉单数', '总复购单数', '总复购率', '累计7日roi', '累计15日roi', '累计30日roi', '累计45日roi', '累计60日roi'];
        $filename = 'huizongshuj_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title); 
    }

    /**
     * 近30天销量排行
     */
    public function volumeRank(Request $request){
        $stime = date('Y-m-d', strtotime('-30 day'));
        $result = OrderGoodsSkus::select(DB::raw('goods_id, sku_id, sum(send_num) as volume, count(1) as counts'))->where('create_time', '>=', $stime)->where('send_num','>',0)->where('is_del', 0)->groupBy('sku_id')->orderBy('volume', 'desc')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $goods = Goods::where('id', $v['goods_id'])->first();
            $skus = GoodsSkus::select('is_weigh', 'propsName', 'price')->where('id', $v['sku_id'])->first();
            $v['name'] = $goods->name;
            $v['propsName'] = $skus->propsName;      
            $v['propsName'] .= !empty($skus) && $skus->is_weigh == 1 ? '【称重商品】' : '';  
            $v['price'] = $skus->price;
            if($skus->is_weigh == 0) $v['volume'] = (int)$v['volume'];
            $v['amount'] = round($v['price'] * $v['volume'], 2);

        }
        return view('statistics/volumeRank', ['result' =>$result,                                                                                                  
            ]);
    }

    public function volumeRank_export(Request $request){
        $stime = date('Y-m-d', strtotime('-30 day'));
        $result = OrderGoodsSkus::select(DB::raw('goods_id, sku_id, sum(send_num) as volume, count(1) as counts'))->where('create_time', '>=', $stime)->where('send_num','>',0)->where('is_del', 0)->groupBy('sku_id')->orderBy('volume', 'desc')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $goods = Goods::where('id', $v['goods_id'])->first();
            $skus = GoodsSkus::select('is_weigh', 'propsName', 'price')->where('id', $v['sku_id'])->first();
            $v['name'] = $goods->name;
            $v['propsName'] = $skus->propsName;      
            $v['propsName'] .= !empty($skus) && $skus->is_weigh == 1 ? '【称重商品】' : '';  
            $v['price'] = $skus->price;
            if($skus->is_weigh == 0) $v['volume'] = (int)$v['volume'];
            $v['amount'] = round($v['price'] * $v['volume'], 2);
            $v['rank_id'] = $k+1;
        }

        $indexKey = ['rank_id','name','propsName','price','counts','volume','amount'];
        $title = ['排名', '商品标题', '商品规格', '价格', '最近30日成单量', '最近30日销量', '近30日销售额'];
        $filename = 'shangpingpaihang_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title);
    }

    /**
     * 分团队每日roi汇总数据
     */
    public function dayGrandTeamTotal(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }
        
        $offset = ($page-1) * $pageSize;
        
        //查询历史数据
        $count = DB::table('day_grand_team_total')->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', $team_id);
            if($stime) $query->where('idate', '>=', $stime);
            if($etime) $query->where('idate', '<=', $etime);
        })->count();
        $teamArr = array(
            1 => '北京销售团队',
            3 => '济南销售团队',
            5 => '杭州雷霆团队',
        );
        $result = DB::table('day_grand_team_total')->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', $team_id);
            if($stime) $query->where('idate', '>=', $stime);
            if($etime) $query->where('idate', '<=', $etime);
        })->orderBy('idate', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $v['roi'] = $v['throw_cost']>0 ? round($v['order_amount'] / $v['throw_cost'], 4) * 100 .'%' : '';
            $v['fugou_rate'] = $v['cust_count']>0 ? round($v['fugou_order_count'] / $v['cust_count'], 4) : '';
            $v['roi7'] = $v['cost7']>0?  round($v['order_amount7'] / $v['cost7'], 4) * 100 .'%' : '';
            $v['roi15'] = $v['cost15']>0?  round($v['order_amount15'] / $v['cost15'], 4) * 100 .'%' : '';
            $v['roi30'] = $v['cost30']>0?  round($v['order_amount30'] / $v['cost30'], 4) * 100 .'%' : '';
            $v['roi45'] = $v['cost45']>0?  round($v['order_amount45'] / $v['cost45'], 4) * 100 .'%' : '';
            $v['roi60'] = $v['cost60']>0?  round($v['order_amount60'] / $v['cost60'], 4) * 100 .'%' : '';
            $v['team_name'] = $teamArr[$v['team_id']];
        }

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

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

    /**
     * 每日累计汇总数据导出
     */
    public function dayGrandTeamTotal_export(Request $request){
        $team_id = (int)$request->input('team_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
       
        $teamArr = array(
            1 => '北京销售团队',
            3 => '济南销售团队',
            5 => '杭州雷霆团队',
        );
        $result = DB::table('day_grand_team_total')->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', $team_id);
            if($stime) $query->where('idate', '>=', $stime);
            if($etime) $query->where('idate', '<=', $etime);
        })->orderBy('idate', 'desc')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $v['roi'] = $v['throw_cost']>0 ? round($v['order_amount'] / $v['throw_cost'], 4) * 100 .'%' : '';
            $v['fugou_rate'] = $v['cust_count']>0 ? round($v['fugou_order_count'] / $v['cust_count'], 4) : '';
            $v['roi7'] = $v['cost7']>0?  round($v['order_amount7'] / $v['cost7'], 4) * 100 .'%' : '';
            $v['roi15'] = $v['cost15']>0?  round($v['order_amount15'] / $v['cost15'], 4) * 100 .'%' : '';
            $v['roi30'] = $v['cost30']>0?  round($v['order_amount30'] / $v['cost30'], 4) * 100 .'%' : '';
            $v['roi45'] = $v['cost45']>0?  round($v['order_amount45'] / $v['cost45'], 4) * 100 .'%' : '';
            $v['roi60'] = $v['cost60']>0?  round($v['order_amount60'] / $v['cost60'], 4) * 100 .'%' : '';
            $v['team_name'] = $teamArr[$v['team_id']];
        }

        $indexKey = ['idate','team_name','throw_cost','cust_count','order_count','order_amount','goods_cost', 'freight_cost', 'aftersale_cost', 'refund_fee', 'profit', 'roi', 'fan_count', 'new_order_count', 'old_order_count', 'fugou_order_count', 'fugou_rate', 'roi7', 'roi15', 'roi30', 'roi45', 'roi60'];
        $title = ['汇总日期', '团队', '总投放', '总客户数', '总下单数', '总销售额', '总货品成本', '总运费成本', '总售后', '总退补', '总毛利', '累计ROI', '总加粉数', '总新粉单数', '总老粉单数', '总复购单数', '总复购率', '累计7日roi', '累计15日roi', '累计30日roi', '累计45日roi', '累计60日roi'];
        $filename = 'teamRoi_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title); 
    }

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

        $offset = ($page-1) * $pageSize;
        $count = CustomerOrderDay::count();
        $data = CustomerOrderDay::offset($offset)->limit($pageSize)->orderBy('grossProfit','desc')->get();

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

        $result = json_decode(json_encode($data),true);
        $result = array_map(function($value){
            $item = $value;
            $phone = $value['phone'];
            $item['phone'] = is_numeric($phone) ? substr_replace($phone, '****', 3, 4) : $phone;
            return $item;
        },$result);
        return view('statistics/customerOrder', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,
        ]);
    }

    function findNum($str=''){
        $str=trim($str);
        if(empty($str)){return '';}
        $reg='/(\d{3}(\.\d+)?)/is';//匹配数字的正则表达式
        preg_match_all($reg,$str,$result);
        if(is_array($result)&&!empty($result)&&!empty($result[1])&&!empty($result[1][0])){
            return $result[1][0];
        }
        return '';
    }

    /**
     * 分团队每日roi汇总数据
     */
    public function dayGrandSalerTotal(Request $request){
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $page = (int)$request->input('page');
        $pageSize = 20;
        if($page<=0){
            $page = 1;
        }
        
        $offset = ($page-1) * $pageSize;
        
        //查询历史数据
        $count = DB::table('day_grand_saler_total')->where(function($query) use($team_id, $stime, $etime, $admin_id){
            if($team_id) $query->where('team_id', $team_id);
            if($admin_id) $query->where('admin_id', $admin_id);
            if($stime) $query->where('idate', '>=', $stime);
            if($etime) $query->where('idate', '<=', $etime);
        })->count();
        $teamArr = DB::table('teams')->lists('name', 'id');
        $adminArr = DB::table('admin')->lists('realname', 'id');
        $result = DB::table('day_grand_saler_total')->where(function($query) use($team_id, $stime, $etime, $admin_id){
            if($team_id) $query->where('team_id', $team_id);
            if($admin_id) $query->where('admin_id', $admin_id);
            if($stime) $query->where('idate', '>=', $stime);
            if($etime) $query->where('idate', '<=', $etime);
        })->orderBy('idate', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $v['roi'] = $v['throw_cost']>0 ? round($v['order_amount'] / $v['throw_cost'], 4) * 100 .'%' : '';
            $v['fugou_rate'] = $v['cust_count']>0 ? round($v['fugou_order_count'] / $v['cust_count'], 4) : '';
            $v['roi7'] = $v['cost7']>0?  round($v['order_amount7'] / $v['cost7'], 4) * 100 .'%' : '';
            $v['roi15'] = $v['cost15']>0?  round($v['order_amount15'] / $v['cost15'], 4) * 100 .'%' : '';
            $v['roi30'] = $v['cost30']>0?  round($v['order_amount30'] / $v['cost30'], 4) * 100 .'%' : '';
            $v['roi45'] = $v['cost45']>0?  round($v['order_amount45'] / $v['cost45'], 4) * 100 .'%' : '';
            $v['roi60'] = $v['cost60']>0?  round($v['order_amount60'] / $v['cost60'], 4) * 100 .'%' : '';
            $v['team_name'] = $teamArr[$v['team_id']];
            $v['admin_name'] = $adminArr[$v['admin_id']];
        }

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

        $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/dayGrandSalerTotal', ['result' =>$result,
            'page'              =>$page,
            'count'             =>$count,
            'pages'             =>$pages,                                                                                                   
            'teamlist'          =>$teamList,                                                                                                   
            'adminlist'          =>$adminList,                                                                                                   
            'team_id'          =>$team_id,                                                                                                   
            'admin_id'          =>$admin_id,                                                                                                   
            'stime'          =>$stime,                                                                                                   
            'etime'          =>$etime,                                                                                                   
            ]);
    }

    /**
     * 每日累计汇总数据导出
     */
    public function dayGrandSalerTotal_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');
       
        $teamArr = DB::table('teams')->lists('name', 'id');
        $adminArr = DB::table('admin')->lists('realname', 'id');
        $result = DB::table('day_grand_saler_total')->where(function($query) use($team_id, $stime, $etime, $admin_id){
            if($team_id) $query->where('team_id', $team_id);
            if($admin_id) $query->where('admin_id', $admin_id);
            if($stime) $query->where('idate', '>=', $stime);
            if($etime) $query->where('idate', '<=', $etime);
        })->orderBy('idate', 'desc')->get();
        $result = json_decode(json_encode($result), true);
        foreach($result as $k=>&$v){
            $v['roi'] = $v['throw_cost']>0 ? round($v['order_amount'] / $v['throw_cost'], 4) * 100 .'%' : '';
            $v['fugou_rate'] = $v['cust_count']>0 ? round($v['fugou_order_count'] / $v['cust_count'], 4) : '';
            $v['roi7'] = $v['cost7']>0?  round($v['order_amount7'] / $v['cost7'], 4) * 100 .'%' : '';
            $v['roi15'] = $v['cost15']>0?  round($v['order_amount15'] / $v['cost15'], 4) * 100 .'%' : '';
            $v['roi30'] = $v['cost30']>0?  round($v['order_amount30'] / $v['cost30'], 4) * 100 .'%' : '';
            $v['roi45'] = $v['cost45']>0?  round($v['order_amount45'] / $v['cost45'], 4) * 100 .'%' : '';
            $v['roi60'] = $v['cost60']>0?  round($v['order_amount60'] / $v['cost60'], 4) * 100 .'%' : '';
            $v['team_name'] = $teamArr[$v['team_id']];
            $v['admin_name'] = $adminArr[$v['admin_id']];
        }

        $indexKey = ['idate','admin_name','team_name','throw_cost','cust_count','order_count','order_amount','goods_cost', 'freight_cost', 'aftersale_cost', 'refund_fee', 'profit', 'roi', 'fan_count', 'new_order_count', 'old_order_count', 'fugou_order_count', 'fugou_rate', 'roi7', 'roi15', 'roi30', 'roi45', 'roi60'];
        $title = ['汇总日期','销售', '所属团队', '总投放', '总客户数', '总下单数', '总销售额', '总货品成本', '总运费成本', '总售后', '总退补', '总毛利', '累计ROI', '总加粉数', '总新粉单数', '总老粉单数', '总复购单数', '总复购率', '累计7日roi', '累计15日roi', '累计30日roi', '累计45日roi', '累计60日roi'];
        $filename = 'salerRoi_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($result, $filename, $indexKey, $title); 
    }
                                                                                
}