seafood/app/Http/Controllers/Admin/StatisticsCo...

<?php
/**
 * Created by Sublime.
 * User: hao
 * Date: 19/08/30
 * Time: 上午11:20
 */
namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Logs;
use App\CustTotal;
use App\CustDetail;
use App\AdCost;
use App\Order;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class StatisticsController extends Controller
{
    /**
     * 当日数据统计
     */
	public function fanDay(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');
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');

        $self_role = session('role_name');
        $team_id = $request->input('team_id');
        #只能看自己团队的
        if($self_role != '超级管理员' && $self_role != '售后管理员'){
            $self_id = session('admin_id');
            $team_id = DB::table('admin')->where('id', $self_id)->pluck('team_id');        
        }

        if($admin_id>0 && !$team_id){
            $team_id = DB::table('admin')->where('id', $admin_id)->pluck('team_id');
        }

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

        $count = CustTotal::select(DB::raw('count(distinct dtime) as total'))->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
        })->where('is_del',0)->first();
        $count = $count->total;
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = CustTotal::select(DB::raw('sum(total_cost) as total_cost, sum(total_fan_add) as total_fan_add, dtime'))->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);
        foreach($result as $k=>&$v){           
            #进粉成本
            $v['cost_fan'] = $v['total_fan_add']>0? round($v['total_cost'] / $v['total_fan_add'], 2) : '';
            #当日微信粉
            $custDetail = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult'))->where('dtime', $v['dtime'])->where('is_del', 0)->where(function($query) use ($team_id, $sale_ids, $admin_id){
                if($team_id>0 && isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();
            $v['wx_fan_add'] = $custDetail->wx_fan_add; //当日微信粉数
            $v['total_new_reply'] = $custDetail->total_new_reply; //当日微信新粉回复
            $v['total_old_consult'] = $custDetail->total_old_consult; //当日微信老粉询价
            $v['new_reply_rate'] = $v['wx_fan_add']>0? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率

            #当日微信粉成本
            $v['cost_wx_fan'] = $v['wx_fan_add']>0?  round($v['total_cost'] / $v['wx_fan_add'], 2) : '';
            #当日订单数、销售额
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count, sum(freight_cost) as freight_cost, sum(cost) as goods_cost'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->where(function($query) use($team_id, $admin_id){
                if($team_id>0) $query->where('team_id', $team_id);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();
            $v['order_count'] = $order->order_count;
            $v['order_amount'] = $order->order_amount;

            #物流成本
            $v['freight_cost'] = $order->freight_cost;
            #货品成本
            $v['goods_cost'] = $order->goods_cost;
            #新加逻辑 -- 销售筛选
            if($admin_id>0){
                //预估当日投放 当日公众进粉
                # 1. 当前团队销售加粉占比
                $teamFanAdd = CustDetail::select(DB::raw('sum(fan_add) as team_fan_add'))->whereIn('admin_id', $sale_ids)->where('dtime', $v['dtime'])->where('is_del', 0)->first();
                $salerfanRate = $teamFanAdd->team_fan_add>0 ? round($v['wx_fan_add'] / $teamFanAdd->team_fan_add, 3) * 1000 : 0;
                #预估该销售投放金额
                $v['total_cost'] = round( $salerfanRate * $v['total_cost'] / 1000, 2);
                #预估进粉
                $v['total_fan_add'] = round( $salerfanRate * $v['total_fan_add'] / 1000);
                #当日微信粉成本
                $v['cost_wx_fan'] = $v['wx_fan_add']>0?  round($v['total_cost'] / $v['wx_fan_add'], 2) : '';
            }
            
        }

        $teamList = DB::table('teams')->select('id', 'name')->where(function($query) use($self_role, $team_id){
            if($team_id>0 && $self_role != '超级管理员' && $self_role != '售后管理员') $query->where('id', $team_id);
        })->get();
        $teamList = json_decode(json_encode($teamList), true);
        $adminList = DB::table('admin')->select('id', 'realname', 'username')->where('id','>', 1)->where(function($query) use($self_role, $team_id){
            if($team_id>0 && $self_role != '超级管理员' && $self_role != '售后管理员') $query->where('team_id', $team_id);
        })->get();
        $adminList = json_decode(json_encode($adminList), true);

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

    public function fanDay_export(Request $request){
        
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $team_id = (int)$request->input('team_id');

        $self_role = session('role_name');
        #只能看自己团队的
        if($self_role != '超级管理员' && $self_role != '售后管理员'){
            $self_id = session('admin_id');
            $team_id = DB::table('admin')->where('id', $self_id)->pluck('team_id');        
        }

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

        $result = CustTotal::select(DB::raw('sum(total_cost) as total_cost, sum(total_fan_add) as total_fan_add, dtime'))->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->get();
        $result = json_decode(json_encode($result),true);

        $filename="当日数据统计.xls";
        header("Content-type:application/vnd.ms-excel");
        Header("Accept-Ranges:bytes");
        Header("Content-Disposition:attachment;filename=".$filename); //$filename导出的文件名
        header("Pragma: no-cache");
        header("Expires: 0");
        $data_str = '<html xmlns:o="urn:schemas-microsoft-com:office:office"
            xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns="http://www.w3.org/TR/REC-html40">
            <head>
            <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
            <meta http-equiv=Content-Type content="text/html; charset=gb2312">
            <!--[if gte mso 9]><xml>
            <x:ExcelWorkbook>
            <x:ExcelWorksheets>
            <x:ExcelWorksheet>
            <x:Name></x:Name>
            <x:WorksheetOptions>
            <x:DisplayGridlines/>
            </x:WorksheetOptions>
            </x:ExcelWorksheet>
            </x:ExcelWorksheets>
            </x:ExcelWorkbook>
            </xml><![endif]-->
            </head>';
        $data_str .= "
            <table>
            <tr>
                <th>".iconv("UTF-8", "GB2312//IGNORE","日期")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日投放金额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日公众号进粉")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日进粉成本")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日微信粉")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日微信粉成本")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日订单数")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日销售额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉回复量")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉回复率")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日老粉主动咨询量")."</th>              

            </tr>";
            foreach ($result as $k => $v)
            {
                #进粉成本
                $v['cost_fan'] = $v['total_fan_add']>0 ? round($v['total_cost'] / $v['total_fan_add'], 2) : '';
                #当日微信粉
                $custDetail = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult'))->where('dtime', $v['dtime'])->where('is_del', 0)->where(function($query) use ($team_id, $sale_ids){
                if($team_id>0 && isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            })->first();
                $v['wx_fan_add'] = $custDetail->wx_fan_add; //当日微信粉数
                $v['total_new_reply'] = $custDetail->total_new_reply; //当日微信新粉回复
                $v['total_old_consult'] = $custDetail->total_old_consult; //当日微信老粉询价
                $v['new_reply_rate'] = $v['wx_fan_add']>0 ? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率

                #当日微信粉成本
                $v['cost_wx_fan'] = $v['wx_fan_add']>0 ? round($v['total_cost'] / $v['wx_fan_add'], 2) : '';
                #当日订单数、销售额
                $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->first();
                $v['order_count'] = $order->order_count;
                $v['order_amount'] = $order->order_amount;

                $data_str .= "<tr>";
                $data_str .= "<td>".$v['dtime']."</td>";
                $data_str .= "<td>".$v['total_cost']."</td>";
                $data_str .= "<td>".$v['total_fan_add']."</td>";
                $data_str .= "<td>".$v['cost_fan']."</td>";
                $data_str .= "<td>".$v['wx_fan_add']."</td>";
                $data_str .= "<td>".$v['cost_wx_fan']."</td>";
                $data_str .= "<td>".$v['order_count']."</td>";
                $data_str .= "<td>".$v['order_amount']."</td>";
                $data_str .= "<td>".$v['total_new_reply']."</td>";
                $data_str .= "<td>".$v['new_reply_rate']."</td>";
                $data_str .= "<td>".$v['total_old_consult']."</td>";

                $data_str .= "</tr>";
            }

        $data_str .= "</table>";
        echo $data_str;
        exit;       
    }

    /**
     * 当日数据统计
     */
    public function orderDay(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');
        $team_id = (int)$request->input('team_id');

        $self_role = session('role_name');
        #只能看自己团队的
        if($self_role != '超级管理员' && $self_role != '售后管理员'){
            $self_id = session('admin_id');
            $team_id = DB::table('admin')->where('id', $self_id)->pluck('team_id');        
        }

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

        //规定只统计前天及以前的数据
        $end_time = date('Y-m-d');

        $count = CustTotal::select(DB::raw('count(distinct dtime) as total'))->where(function($query) use($team_id, $stime, $etime, $end_time){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            $query->where('dtime','<', $end_time);
        })->where('is_del',0)->first();
        $count = $count->total;
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = CustTotal::select(DB::raw('sum(total_cost) as total_cost, sum(total_fan_add) as total_fan_add, dtime'))->where(function($query) use($team_id, $stime, $etime, $end_time){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            $query->where('dtime','<', $end_time);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);
        foreach($result as $k=>&$v){
            #当日微信好友数量
            $custDetail = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add'))->where('dtime', $v['dtime'])->where('is_del', 0)->where(function($query) use ($team_id, $sale_ids){
                if($team_id>0 && isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            })->first();
            $v['wx_fan_add'] = $custDetail->wx_fan_add; //当日微信粉数
            //当日加粉
            $phones = DB::table('customers')->where('fanTime', $v['dtime'])->lists('phone');
            #当日加粉订单总计：
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count, sum(cost) as order_cost, sum(freight_cost) as freight_cost'))->whereIn('receiverMobile', $phones)->where('createTime','<',$end_time)->where('is_del', 0)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->first();
            #当日新粉成单：            
            $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->first();
            // 1.当日新粉成单数
            $v['new_order_count'] = $new_order->order_count;
            // 2.当日新粉成交额 
            $v['new_order_amount'] = $new_order->order_amount;

            $old_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>', $v['dtime'].' 23:59:59')->where('createTime','<', $end_time)->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->first();
            // 3.老粉成单数
            $v['old_order_count'] = $old_order->order_count;
            // 4.老粉成交额
            $v['old_order_amount'] = $old_order->order_amount;
            // 5.总成交额
            $v['order_amount'] = $order->order_amount;

            #复购单数、复购成交额
            $fugou = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->where('is_fugou', 1)->where('createTime','<', $end_time)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->first();
            $v['fugou_order_count'] = $fugou->order_count;
            $v['fugou_order_amount'] = $fugou->order_amount;
            #货品成本
            $v['order_cost'] = $order->order_cost;
            //加物流成本
            $v['freight_cost'] = $order->freight_cost;
            #毛利
            $v['profit'] = $v['order_amount'] - $v['order_cost'] - $v['total_cost'] - $v['freight_cost'];
            //综合成单率 综合成单率=订单数/微信好友数
            $v['order_rate'] = $custDetail->wx_fan_add>0 ? round($order->order_count/$custDetail->wx_fan_add, 4) * 100 .'%' : '';
            //新粉成单率
            $v['new_order_rate'] = $custDetail->wx_fan_add>0 ? round($new_order->order_count/$custDetail->wx_fan_add, 4) * 100 .'%' : '';
            //复购率
            $fugou_order_count_no = $order->order_count - $fugou->order_count;
            $v['fugou_rate'] = $fugou_order_count_no>0 ? round($fugou->order_count / $fugou_order_count_no, 4) * 100 .'%' : '';

            //加roi
            $v['total_roi'] = $v['total_cost']>0 ? round($v['order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            $v['new_roi'] = $v['total_cost']>0 ? round($v['new_order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';            
            
        }

        $teamList = DB::table('teams')->select('id', 'name')->where(function($query) use($self_role, $team_id){
            if($team_id>0 && $self_role != '超级管理员' && $self_role != '售后管理员') $query->where('id', $team_id);
        })->get();
        $teamList = json_decode(json_encode($teamList), true);

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

    public function orderDay_export(Request $request){
        
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $team_id = (int)$request->input('team_id');

        $self_role = session('role_name');
        #只能看自己团队的
        if($self_role != '超级管理员' && $self_role != '售后管理员'){
            $self_id = session('admin_id');
            $team_id = DB::table('admin')->where('id', $self_id)->pluck('team_id');        
        }

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

        //规定只统计前天及以前的数据
        $end_time = date('Y-m-d');

        $result = CustTotal::select(DB::raw('sum(total_cost) as total_cost, sum(total_fan_add) as total_fan_add, dtime'))->where(function($query) use($team_id, $stime, $etime, $end_time){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            $query->where('dtime','<', $end_time);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->get();
        $result = json_decode(json_encode($result),true);

        $filename="分片数据统计.xls";
        header("Content-type:application/vnd.ms-excel");
        Header("Accept-Ranges:bytes");
        Header("Content-Disposition:attachment;filename=".$filename); //$filename导出的文件名
        header("Pragma: no-cache");
        header("Expires: 0");
        $data_str = '<html xmlns:o="urn:schemas-microsoft-com:office:office"
            xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns="http://www.w3.org/TR/REC-html40">
            <head>
            <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
            <meta http-equiv=Content-Type content="text/html; charset=gb2312">
            <!--[if gte mso 9]><xml>
            <x:ExcelWorkbook>
            <x:ExcelWorksheets>
            <x:ExcelWorksheet>
            <x:Name></x:Name>
            <x:WorksheetOptions>
            <x:DisplayGridlines/>
            </x:WorksheetOptions>
            </x:ExcelWorksheet>
            </x:ExcelWorksheets>
            </x:ExcelWorkbook>
            </xml><![endif]-->
            </head>';
        $data_str .= "
            <table>
            <tr>
                <th>".iconv("UTF-8", "GB2312//IGNORE","日期")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日进粉数量")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日微信好友数量")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉成单数")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉成单数")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","复购单数")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","复购成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","总成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","总投放成本")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","货品成本")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","毛利")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉成单率")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","综合成单率")."</th>              

            </tr>";

            foreach ($result as $k => $v)
            {
                #当日微信好友数量
                $custDetail = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add'))->where('dtime', $v['dtime'])->where('is_del', 0)->where(function($query) use ($team_id, $sale_ids){
                    if($team_id>0 && isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
                })->first();
                $v['wx_fan_add'] = $custDetail->wx_fan_add; //当日微信粉数
                //当日加粉
                $phones = DB::table('customers')->where('fanTime', $v['dtime'])->lists('phone');
                #当日加粉订单总计：
                $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count, sum(cost) as order_cost'))->whereIn('receiverMobile', $phones)->where('createTime', '<', $end_time)->where('is_del', 0)->where(function($query) use($team_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                })->first();
                #当日新粉成单：            
                $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                })->first();
                // 1.当日新粉成单数
                $v['new_order_count'] = $new_order->order_count;
                // 2.当日新粉成交额 
                $v['new_order_amount'] = $new_order->order_amount;

                $old_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>', $v['dtime'].' 23:59:59')->where('createTime','<', $end_time)->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                })->first();
                // 3.老粉成单数
                $v['old_order_count'] = $old_order->order_count;
                // 4.老粉成交额
                $v['old_order_amount'] = $old_order->order_amount;
                // 5.总成交额
                $v['order_amount'] = $order->order_amount;

                #复购单数、复购成交额
                $fugou = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->where('is_fugou', 1)->where('createTime','<', $end_time)->where(function($query) use($team_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                })->first();
                $v['fugou_order_count'] = $fugou->order_count;
                $v['fugou_order_amount'] = $fugou->order_amount;
                #货品成本
                $v['order_cost'] = $order->order_cost;
                #毛利
                $v['profit'] = $v['order_amount'] - $v['order_cost'] - $v['total_cost'];
                //综合成单率 综合成单率=订单数/微信好友数
                $v['order_rate'] = $custDetail->wx_fan_add>0 ? round($order->order_count/$custDetail->wx_fan_add, 4) * 100 .'%' : '';
                //新粉成单率
                $v['new_order_rate'] = $custDetail->wx_fan_add>0 ? round($new_order->order_count/$custDetail->wx_fan_add, 4) * 100 .'%' : '';
                //复购率
                $fugou_order_count_no = $order->order_count - $fugou->order_count;
                $v['fugou_rate'] = $fugou_order_count_no>0 ? round($fugou->order_count / $fugou_order_count_no, 4) * 100 .'%' : '';

                $data_str .= "<tr>";
                $data_str .= "<td>".$v['dtime']."</td>";
                $data_str .= "<td>".$v['total_fan_add']."</td>";
                $data_str .= "<td>".$v['wx_fan_add']."</td>";
                $data_str .= "<td>".$v['new_order_count']."</td>";
                $data_str .= "<td>".$v['new_order_amount']."</td>";
                $data_str .= "<td>".$v['old_order_count']."</td>";
                $data_str .= "<td>".$v['old_order_amount']."</td>";
                $data_str .= "<td>".$v['fugou_order_count']."</td>";
                $data_str .= "<td>".$v['fugou_order_amount']."</td>";
                $data_str .= "<td>".$v['order_amount']."</td>";
                $data_str .= "<td>".$v['total_cost']."</td>";
                $data_str .= "<td>".$v['order_cost']."</td>";
                $data_str .= "<td>".$v['profit']."</td>";
                $data_str .= "<td>".$v['new_order_rate']."</td>";
                $data_str .= "<td>".$v['order_rate']."</td>";

                $data_str .= "</tr>";
            }

        $data_str .= "</table>";
        echo $data_str;
        exit;       
    }

    /**
     * 当日数据统计（投放）
     */
    public function throwDay(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');
        $team_id = (int)$request->input('team_id');

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

        $count = CustTotal::select(DB::raw('count(distinct dtime) as total'))->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
        })->where('is_del',0)->first();
        $count = $count->total;
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = CustTotal::select(DB::raw('sum(total_cost) as total_cost, sum(total_fan_add) as total_fan_add, dtime'))->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);
        foreach($result as $k=>&$v){
            #进粉成本
            $v['cost_fan'] = $v['total_fan_add']>0? round($v['total_cost'] / $v['total_fan_add'], 2) : '';
            #当日微信粉
            $custDetail = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult'))->where('dtime', $v['dtime'])->where('is_del', 0)->where(function($query) use ($team_id, $sale_ids){
                if($team_id>0 && isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            })->first();
            $v['wx_fan_add'] = $custDetail->wx_fan_add; //当日微信粉数
            $v['total_new_reply'] = $custDetail->total_new_reply; //当日微信新粉回复
            $v['total_old_consult'] = $custDetail->total_old_consult; //当日微信老粉询价
            $v['new_reply_rate'] = $v['wx_fan_add']>0? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率

            #当日微信粉成本
            $v['cost_wx_fan'] = $v['wx_fan_add']>0?  round($v['total_cost'] / $v['wx_fan_add'], 2) : '';
            #当日订单数、销售额
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->first();
            $v['order_count'] = $order->order_count;
            $v['order_amount'] = $order->order_amount;
            
        }

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

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

    public function throwDay_export(Request $request){
        
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $team_id = (int)$request->input('team_id');

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

        $result = CustTotal::select(DB::raw('sum(total_cost) as total_cost, sum(total_fan_add) as total_fan_add, dtime'))->where(function($query) use($team_id, $stime, $etime){
            if($team_id) $query->where('team_id', '=', $team_id);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->get();
        $result = json_decode(json_encode($result),true);

        $filename="当日数据统计.xls";
        header("Content-type:application/vnd.ms-excel");
        Header("Accept-Ranges:bytes");
        Header("Content-Disposition:attachment;filename=".$filename); //$filename导出的文件名
        header("Pragma: no-cache");
        header("Expires: 0");
        $data_str = '<html xmlns:o="urn:schemas-microsoft-com:office:office"
            xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns="http://www.w3.org/TR/REC-html40">
            <head>
            <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
            <meta http-equiv=Content-Type content="text/html; charset=gb2312">
            <!--[if gte mso 9]><xml>
            <x:ExcelWorkbook>
            <x:ExcelWorksheets>
            <x:ExcelWorksheet>
            <x:Name></x:Name>
            <x:WorksheetOptions>
            <x:DisplayGridlines/>
            </x:WorksheetOptions>
            </x:ExcelWorksheet>
            </x:ExcelWorksheets>
            </x:ExcelWorkbook>
            </xml><![endif]-->
            </head>';
        $data_str .= "
            <table>
            <tr>
                <th>".iconv("UTF-8", "GB2312//IGNORE","日期")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日投放金额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日公众号进粉")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日进粉成本")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日微信粉")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日微信粉成本")."</th>          

            </tr>";
            foreach ($result as $k => $v)
            {
                #进粉成本
                $v['cost_fan'] = $v['total_fan_add']>0 ? round($v['total_cost'] / $v['total_fan_add'], 2) : '';
                #当日微信粉
                $custDetail = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult'))->where('dtime', $v['dtime'])->where('is_del', 0)->where(function($query) use ($team_id, $sale_ids){
                if($team_id>0 && isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            })->first();
                $v['wx_fan_add'] = $custDetail->wx_fan_add; //当日微信粉数
                $v['total_new_reply'] = $custDetail->total_new_reply; //当日微信新粉回复
                $v['total_old_consult'] = $custDetail->total_old_consult; //当日微信老粉询价
                $v['new_reply_rate'] = $v['wx_fan_add']>0 ? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率

                #当日微信粉成本
                $v['cost_wx_fan'] = $v['wx_fan_add']>0 ? round($v['total_cost'] / $v['wx_fan_add'], 2) : '';
                #当日订单数、销售额
                $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->first();
                $v['order_count'] = $order->order_count;
                $v['order_amount'] = $order->order_amount;

                $data_str .= "<tr>";
                $data_str .= "<td>".$v['dtime']."</td>";
                $data_str .= "<td>".$v['total_cost']."</td>";
                $data_str .= "<td>".$v['total_fan_add']."</td>";
                $data_str .= "<td>".$v['cost_fan']."</td>";
                $data_str .= "<td>".$v['wx_fan_add']."</td>";
                $data_str .= "<td>".$v['cost_wx_fan']."</td>";
                $data_str .= "</tr>";
            }

        $data_str .= "</table>";
        echo $data_str;
        exit;       
    }

    /**
     * 销售可看当日数据表
     *
     */
    public function fanDaySaler(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');
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');
        $is_self_team = (int)$request->input('is_self_team');

        //规定只统计前天及以前的数据
        $end_time = date('Y-m-d');
        
        $self_role = session('role_name');
        if($self_role == '销售'){
            $admin_id = session('admin_id');
            #假如查看团队的
            if($is_self_team == 1){
                $team_id = DB::table('admin')->where('id', $admin_id)->pluck('team_id');
                $admin_id = 0;
            }else{
                $team_id = null;
            }            
        }

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

        $count = CustDetail::select(DB::raw('count(distinct dtime) as total'))->where(function($query) use($sale_ids, $stime, $etime, $admin_id, $end_time){
            if($admin_id) $query->where('admin_id', '=', $admin_id);
            if(isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            if($end_time) $query->where('dtime', '<', $end_time);
        })->where('is_del',0)->first();
        $count = $count->total;
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult, sum(new_consult) as total_new_consult, dtime'))->where(function($query) use($sale_ids, $stime, $etime, $admin_id, $end_time){
            if($admin_id) $query->where('admin_id', '=', $admin_id);
            if(isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            if($end_time) $query->where('dtime', '<', $end_time);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);
        foreach($result as $k=>&$v){
            $v['new_reply_rate'] = $v['wx_fan_add']>0? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率
            $v['new_consult_rate'] = $v['total_new_reply']>0? round($v['total_new_consult'] / $v['total_new_reply'], 4) * 100 .'%' : ''; //当日新粉询价率=

            //当日加粉
            $phones = DB::table('customers')->where('fanTime', $v['dtime'])->lists('phone');
            #当日新粉成单：            
            $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id, $admin_id){
                if($team_id>0) $query->where('team_id', $team_id);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();
            // 1.当日新粉成单数
            $v['new_order_count'] = $new_order->order_count;
            // 2.当日新粉成交额 
            $v['new_order_amount'] = $new_order->order_amount;
            // 3.新粉成交转化率
            $v['new_order_rate'] = $v['total_new_consult']>0 ? round($v['new_order_count'] / $v['total_new_consult'], 4) * 100 .'%' : '';
            //累计老粉量
            $dtime = $v['dtime'];
            $v['wx_old_fan'] = CustDetail::where(function($query) use($sale_ids, $dtime, $admin_id){
                if($admin_id) $query->where('admin_id', '=', $admin_id);
                if($sale_ids) $query->whereIn('admin_id', $sale_ids);
                if($dtime) $query->where('dtime', '<', $dtime);
            })->where('is_del',0)->sum('fan_add');
            $v['old_consult_rate'] = $v['wx_old_fan']>0? round($v['total_old_consult'] / $v['wx_old_fan'], 4) * 100 .'%' : ''; //当日老粉询价率

            #当日订单数、销售额
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->where(function($query) use($team_id, $admin_id){
                if($team_id>0) $query->where('team_id', $team_id);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();
            $v['order_count'] = $order->order_count;
            $v['order_amount'] = $order->order_amount;

            // 3.老粉成单数
            $v['old_order_count'] = $order->order_count - $v['new_order_count'];
            // 4.老粉成交额
            $v['old_order_amount'] = $order->order_amount - $v['new_order_amount'];
            // 5.老粉成交转化率
            $v['old_order_rate'] = $v['total_old_consult']>0 ? round($v['old_order_count'] / $v['total_old_consult'], 4) * 100 .'%' : '';
            
        }

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

    public function fanDaySaler_export(Request $request){
        
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');
        $is_self_team = (int)$request->input('is_self_team');

        //规定只统计前天及以前的数据
        $end_time = date('Y-m-d');
        
        $self_role = session('role_name');
        if($self_role == '销售'){
            $admin_id = session('admin_id');
            #假如查看团队的
            if($is_self_team == 1){
                $team_id = DB::table('admin')->where('id', $admin_id)->pluck('team_id');
                $admin_id = 0;
            }else{
                $team_id = null;
            }            
        }

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

        $result = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult, sum(new_consult) as total_new_consult, dtime'))->where(function($query) use($sale_ids, $stime, $etime, $admin_id, $end_time){
            if($admin_id) $query->where('admin_id', '=', $admin_id);
            if(isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            if($end_time) $query->where('dtime', '<', $end_time);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->get();
        $result = json_decode(json_encode($result),true);

        $filename="当日数据统计(销售).xls";
        header("Content-type:application/vnd.ms-excel");
        Header("Accept-Ranges:bytes");
        Header("Content-Disposition:attachment;filename=".$filename); //$filename导出的文件名
        header("Pragma: no-cache");
        header("Expires: 0");
        $data_str = '<html xmlns:o="urn:schemas-microsoft-com:office:office"
            xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns="http://www.w3.org/TR/REC-html40">
            <head>
            <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
            <meta http-equiv=Content-Type content="text/html; charset=gb2312">
            <!--[if gte mso 9]><xml>
            <x:ExcelWorkbook>
            <x:ExcelWorksheets>
            <x:ExcelWorksheet>
            <x:Name></x:Name>
            <x:WorksheetOptions>
            <x:DisplayGridlines/>
            </x:WorksheetOptions>
            </x:ExcelWorksheet>
            </x:ExcelWorksheets>
            </x:ExcelWorkbook>
            </xml><![endif]-->
            </head>';                                               
                
        $data_str .= "
            <table>
            <tr>
                <th>".iconv("UTF-8", "GB2312//IGNORE","日期")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉增加量")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉回复")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉回复率")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉询价量")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉询价率")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉成交单量")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉成交额")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉成交转化率")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","累计老粉量")."</th>                           
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉询价量")."</th>                           
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉询价率")."</th>                           
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉成交单量")."</th>                           
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉成交额")."</th>                           
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉成交转化率")."</th>                           
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日单量")."</th>                                                     
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日销售额")."</th>                           

            </tr>";
            foreach ($result as $k => $v)
            {
                $v['new_reply_rate'] = $v['wx_fan_add']>0? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率
                $v['new_consult_rate'] = $v['total_new_reply']>0? round($v['total_new_consult'] / $v['total_new_reply'], 4) * 100 .'%' : ''; //当日新粉询价率=

                //当日加粉
                $phones = DB::table('customers')->where('fanTime', $v['dtime'])->lists('phone');
                #当日新粉成单：            
                $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id, $admin_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                    if($admin_id>0) $query->where('admin_id', $admin_id);
                })->first();
                // 1.当日新粉成单数
                $v['new_order_count'] = $new_order->order_count;
                // 2.当日新粉成交额 
                $v['new_order_amount'] = $new_order->order_amount;
                // 3.新粉成交转化率
                $v['new_order_rate'] = $v['total_new_consult']>0 ? round($v['new_order_count'] / $v['total_new_consult'], 4) * 100 .'%' : '';
                //累计老粉量
                $dtime = $v['dtime'];
                $v['wx_old_fan'] = CustDetail::where(function($query) use($sale_ids, $dtime, $admin_id){
                    if($admin_id) $query->where('admin_id', '=', $admin_id);
                    if($sale_ids) $query->whereIn('admin_id', $sale_ids);
                    if($dtime) $query->where('dtime', '<', $dtime);
                })->where('is_del',0)->sum('fan_add');
                $v['old_consult_rate'] = $v['wx_old_fan']>0? round($v['total_old_consult'] / $v['wx_old_fan'], 4) * 100 .'%' : ''; //当日老粉询价率

                #当日订单数、销售额
                $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->where(function($query) use($team_id, $admin_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                    if($admin_id>0) $query->where('admin_id', $admin_id);
                })->first();
                $v['order_count'] = $order->order_count;
                $v['order_amount'] = $order->order_amount;

                // 3.老粉成单数
                $v['old_order_count'] = $order->order_count - $v['new_order_count'];
                // 4.老粉成交额
                $v['old_order_amount'] = $order->order_amount - $v['new_order_amount'];
                // 5.老粉成交转化率
                $v['old_order_rate'] = $v['total_old_consult']>0 ? round($v['old_order_count'] / $v['total_old_consult'], 4) * 100 .'%' : '';

                $data_str .= "<tr>";
                $data_str .= "<td>".$v['dtime']."</td>";
                $data_str .= "<td>".$v['wx_fan_add']."</td>";
                $data_str .= "<td>".$v['total_new_reply']."</td>";
                $data_str .= "<td>".$v['new_reply_rate']."</td>";
                $data_str .= "<td>".$v['total_new_consult']."</td>";
                $data_str .= "<td>".$v['new_consult_rate']."</td>";
                $data_str .= "<td>".$v['new_order_count']."</td>";
                $data_str .= "<td>".$v['new_order_amount']."</td>";
                $data_str .= "<td>".$v['new_order_rate']."</td>";
                $data_str .= "<td>".$v['wx_old_fan']."</td>";
                $data_str .= "<td>".$v['total_old_consult']."</td>";
                $data_str .= "<td>".$v['old_consult_rate']."</td>";
                $data_str .= "<td>".$v['old_order_count']."</td>";
                $data_str .= "<td>".$v['old_order_amount']."</td>";
                $data_str .= "<td>".$v['old_order_rate']."</td>";
                $data_str .= "<td>".$v['order_count']."</td>";
                $data_str .= "<td>".$v['order_amount']."</td>";

                $data_str .= "</tr>";
            }

        $data_str .= "</table>";
        echo $data_str;
        exit;       
    }

    /**
     * 销售可看当日分片数据
     *
     */
    public function orderDaySaler(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');
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');
        $is_self_team = (int)$request->input('is_self_team');

        //规定只统计前天及以前的数据
        $end_time = date('Y-m-d');
        
        $self_role = session('role_name');
        if($self_role == '销售'){
            $admin_id = session('admin_id');
            #假如查看团队的
            if($is_self_team == 1){
                $team_id = DB::table('admin')->where('id', $admin_id)->pluck('team_id');
                $admin_id = 0;
            }else{
                $team_id = null;
            }            
        }

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

        $count = CustDetail::select(DB::raw('count(distinct dtime) as total'))->where(function($query) use($sale_ids, $stime, $etime, $admin_id, $end_time){
            if($admin_id) $query->where('admin_id', '=', $admin_id);
            if(isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            $query->where('dtime', '<', $end_time);
        })->where('is_del',0)->first();
        $count = $count->total;
        if ($count > 1) {
            // 总页数
            $pages = ceil($count/$pageSize);
        }else{
            // 总页数
            $pages = 1;
        }

        $result = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult, sum(new_consult) as total_new_consult, dtime'))->where(function($query) use($sale_ids, $stime, $etime, $admin_id, $end_time){
            if($admin_id) $query->where('admin_id', '=', $admin_id);
            if(isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            $query->where('dtime', '<', $end_time);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->offset($offset)->limit($pageSize)->get();
        $result = json_decode(json_encode($result),true);
        foreach($result as $k=>&$v){
            $v['new_reply_rate'] = $v['wx_fan_add']>0? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率
            $v['new_consult_rate'] = $v['total_new_reply']>0? round($v['total_new_consult'] / $v['total_new_reply'], 4) * 100 .'%' : ''; //当日新粉询价率=

            //当日加粉
            $phones = DB::table('customers')->where('fanTime', $v['dtime'])->lists('phone');
            #当日新粉成单：            
            $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id, $admin_id){
                if($team_id>0) $query->where('team_id', $team_id);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();
            // 1.当日新粉成单数
            $v['new_order_count'] = $new_order->order_count;
            // 2.当日新粉成交额 
            $v['new_order_amount'] = $new_order->order_amount;

            #当日老粉成单
            $old_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>', $v['dtime'].' 23:59:59')->where('createTime','<', $end_time)->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id, $admin_id){
                if($team_id>0) $query->where('team_id', $team_id);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();
            // 1.当日老粉成单数
            $v['old_order_count'] = $old_order->order_count;
            // 2.当日老粉成交额 
            $v['old_order_amount'] = $old_order->order_amount;

            #复购单数、复购成交额
            $fugou = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->where('is_fugou', 1)->where('createTime','<', $end_time)->where(function($query) use($team_id, $admin_id){
                if($team_id>0) $query->where('team_id', $team_id);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();
            $v['fugou_order_count'] = $fugou->order_count;
            $v['fugou_order_amount'] = $fugou->order_amount;

            #当日加粉订单总计：
            $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('createTime','<',$end_time)->where('is_del', 0)->where(function($query) use($team_id, $admin_id){
                if($team_id>0) $query->where('team_id', $team_id);
                if($admin_id>0) $query->where('admin_id', $admin_id);
            })->first();

            //总成交额
            $v['order_amount'] = $order->order_amount;

            //综合成单率 综合成单率=订单数/微信好友数
            $v['order_rate'] = $v['wx_fan_add']>0 ? round($order->order_count / $v['wx_fan_add'], 4) * 100 .'%' : '';
            //新粉成单率
            $v['new_order_rate'] = $v['wx_fan_add']>0 ? round($v['new_order_count'] / $v['wx_fan_add'], 4) * 100 .'%' : '';
            //复购率
            $fugou_order_count_no = $order->order_count - $fugou->order_count;
            $v['fugou_rate'] = $fugou_order_count_no>0 ? round($fugou->order_count / $fugou_order_count_no, 4) * 100 .'%' : '';
            
        }

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

    public function orderDaySaler_export(Request $request){
        
        $stime = $request->input('stime');
        $etime = $request->input('etime');
        $team_id = (int)$request->input('team_id');
        $admin_id = (int)$request->input('admin_id');
        $is_self_team = (int)$request->input('is_self_team');

        //规定只统计前天及以前的数据
        $end_time = date('Y-m-d');
        
        $self_role = session('role_name');
        if($self_role == '销售'){
            $admin_id = session('admin_id');
            #假如查看团队的
            if($is_self_team == 1){
                $team_id = DB::table('admin')->where('id', $admin_id)->pluck('team_id');
                $admin_id = 0;
            }else{
                $team_id = null;
            }            
        }

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

        $result = CustDetail::select(DB::raw('sum(fan_add) as wx_fan_add, sum(new_reply) as total_new_reply, sum(old_consult) as total_old_consult, sum(new_consult) as total_new_consult, dtime'))->where(function($query) use($sale_ids, $stime, $etime, $admin_id, $end_time){
            if($admin_id) $query->where('admin_id', '=', $admin_id);
            if(isset($sale_ids)) $query->whereIn('admin_id', $sale_ids);
            if($stime) $query->where('dtime', '>=', $stime);
            if($etime) $query->where('dtime', '<=', $etime);
            $query->where('dtime', '<', $end_time);
        })->where('is_del',0)->groupBy('dtime')->orderBy('dtime', 'desc')->get();
        $result = json_decode(json_encode($result),true);

        $filename="当日数据统计(销售).xls";
        header("Content-type:application/vnd.ms-excel");
        Header("Accept-Ranges:bytes");
        Header("Content-Disposition:attachment;filename=".$filename); //$filename导出的文件名
        header("Pragma: no-cache");
        header("Expires: 0");
        $data_str = '<html xmlns:o="urn:schemas-microsoft-com:office:office"
            xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns="http://www.w3.org/TR/REC-html40">
            <head>
            <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
            <meta http-equiv=Content-Type content="text/html; charset=gb2312">
            <!--[if gte mso 9]><xml>
            <x:ExcelWorkbook>
            <x:ExcelWorksheets>
            <x:ExcelWorksheet>
            <x:Name></x:Name>
            <x:WorksheetOptions>
            <x:DisplayGridlines/>
            </x:WorksheetOptions>
            </x:ExcelWorksheet>
            </x:ExcelWorksheets>
            </x:ExcelWorkbook>
            </xml><![endif]-->
            </head>';                                               
       
        $data_str .= "
            <table>
            <tr>
                <th>".iconv("UTF-8", "GB2312//IGNORE","日期")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日微信好友数量")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉成单数")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉成单数")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","老粉成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","复购单数")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","复购成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","总成交额")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","当日新粉成单率")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","复购率")."</th>
                <th>".iconv("UTF-8", "GB2312//IGNORE","综合成单率")."</th>
                           

            </tr>";
            foreach ($result as $k => $v)
            {
                $v['new_reply_rate'] = $v['wx_fan_add']>0? round($v['total_new_reply'] / $v['wx_fan_add'], 4) * 100 .'%' : ''; //当日新粉回复率
                $v['new_consult_rate'] = $v['total_new_reply']>0? round($v['total_new_consult'] / $v['total_new_reply'], 4) * 100 .'%' : ''; //当日新粉询价率=

                //当日加粉
                $phones = DB::table('customers')->where('fanTime', $v['dtime'])->lists('phone');
                #当日新粉成单：            
                $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $v['dtime'])->where('createTime','<', $v['dtime'].' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id, $admin_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                    if($admin_id>0) $query->where('admin_id', $admin_id);
                })->first();
                // 1.当日新粉成单数
                $v['new_order_count'] = $new_order->order_count;
                // 2.当日新粉成交额 
                $v['new_order_amount'] = $new_order->order_amount;

                #当日老粉成单
                $old_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>', $v['dtime'].' 23:59:59')->where('createTime','<', $end_time)->where('is_del', 0)->whereIn('receiverMobile', $phones)->where(function($query) use($team_id, $admin_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                    if($admin_id>0) $query->where('admin_id', $admin_id);
                })->first();
                // 1.当日老粉成单数
                $v['old_order_count'] = $old_order->order_count;
                // 2.当日老粉成交额 
                $v['old_order_amount'] = $old_order->order_amount;

                #复购单数、复购成交额
                $fugou = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('is_del', 0)->where('is_fugou', 1)->where('createTime','<', $end_time)->where(function($query) use($team_id, $admin_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                    if($admin_id>0) $query->where('admin_id', $admin_id);
                })->first();
                $v['fugou_order_count'] = $fugou->order_count;
                $v['fugou_order_amount'] = $fugou->order_amount;

                #当日加粉订单总计：
                $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('createTime','<',$end_time)->where('is_del', 0)->where(function($query) use($team_id, $admin_id){
                    if($team_id>0) $query->where('team_id', $team_id);
                    if($admin_id>0) $query->where('admin_id', $admin_id);
                })->first();

                //总成交额
                $v['order_amount'] = $order->order_amount;

                //综合成单率 综合成单率=订单数/微信好友数
                $v['order_rate'] = $v['wx_fan_add']>0 ? round($order->order_count / $v['wx_fan_add'], 4) * 100 .'%' : '';
                //新粉成单率
                $v['new_order_rate'] = $v['wx_fan_add']>0 ? round($v['new_order_count'] / $v['wx_fan_add'], 4) * 100 .'%' : '';
                //复购率
                $fugou_order_count_no = $order->order_count - $fugou->order_count;
                $v['fugou_rate'] = $fugou_order_count_no>0 ? round($fugou->order_count / $fugou_order_count_no, 4) * 100 .'%' : '';

                $data_str .= "<tr>";
                $data_str .= "<td>".$v['dtime']."</td>";
                $data_str .= "<td>".$v['wx_fan_add']."</td>";
                $data_str .= "<td>".$v['new_order_count']."</td>";
                $data_str .= "<td>".$v['new_order_amount']."</td>";
                $data_str .= "<td>".$v['old_order_count']."</td>";
                $data_str .= "<td>".$v['old_order_amount']."</td>";
                $data_str .= "<td>".$v['fugou_order_count']."</td>";
                $data_str .= "<td>".$v['fugou_order_amount']."</td>";
                $data_str .= "<td>".$v['order_amount']."</td>";
                $data_str .= "<td>".$v['new_order_rate']."</td>";
                $data_str .= "<td>".$v['fugou_rate']."</td>";
                $data_str .= "<td>".$v['order_rate']."</td>";

                $data_str .= "</tr>";
            }

        $data_str .= "</table>";
        echo $data_str;
        exit;       
    }

    /**
     * 成单率梯形图
     */
    public function orderRateList(Request $request){
        $stime = $request->input('stime');
        $etime = $request->input('etime');

        $self_role = session('role_name');
        $team_id = $request->input('team_id');
        #只能看自己团队的
        if($self_role != '超级管理员' && $self_role != '售后管理员'){
            $self_id = session('admin_id');
            $team_id = DB::table('admin')->where('id', $self_id)->pluck('team_id');        
        }
        if($team_id>0){
            $result = $this->orderTeamRateList($stime, $etime, $team_id);
            return view('statistics/orderRateList', $result);
        }

        $_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('cust_day_remain')->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('cust_day_remain')->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;
        }

        #数据格式化
        $new_data = array();
        foreach($data as $k=>$v){
            //当日加粉数
            $fan_add = DB::table('cust_day_detail')->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'].'%' : '';
            }
        }

        $teamList = DB::table('teams')->select('id', 'name')->where(function($query) use($self_role, $team_id){
            if($team_id>0 && $self_role != '超级管理员' && $self_role != '售后管理员') $query->where('id', $team_id);
        })->get();
        $teamList = json_decode(json_encode($teamList), true);
        //echo '<pre>';
        //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 '<pre>';
        //print_r($new_data);print_r($columns);exit;
        $self_role = session('role_name');
        $teamList = DB::table('teams')->select('id', 'name')->where(function($query) use($self_role, $team_id){
            if($self_role != '超级管理员' && $self_role != '售后管理员') $query->where('id', $team_id);
        })->get();
        $teamList = json_decode(json_encode($teamList), true);
        return ['result' =>$new_data, 'columns'=>$columns, 'stime'=>$stime, 'etime'=>$etime, 'team_id'=>$team_id, 'teamlist'=>$teamList];

    }

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

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

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

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

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

        $ret = AdCost::select('id')->where(function($query) use($stime, $etime, $city){    
            if($stime) $query->where('ad_time', '>=', $stime);
            if($etime) $query->where('ad_time', '<=', $etime);
            if($city) $query->where('city', '=', $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,
        ];

        //投入
        $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 .'%' : '';

        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,
        ];

        //投入
        $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 .'%' : '';

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