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 App\DistrictRoi7;
use App\DistrictRoi30;
use App\DistrictRoi15;
use App\DistrictRoi45;
use App\DistrictRoi60;
use App\SalerTargets;
use App\TemplatesLog;
use App\OrderGoodsSkus;
use App\Goods;
use App\GoodsSkus;
use App\RedisModel as Redis;
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_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 != '售后管理员' && $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 != '售后管理员' && $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_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="dangrishuj.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>              

            </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, 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){
                    if($team_id>0) $query->where('team_id', $team_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;

                $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['goods_cost']."</td>";
                $data_str .= "<td>".$v['freight_cost']."</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>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","复购率")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","综合ROI")."</th>              
                <th>".iconv("UTF-8", "GB2312//IGNORE","新粉ROI")."</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 .'%' : '';

                //加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 .'%' : '';   

                $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 .= "<td>".$v['fugou_rate']."</td>";
                $data_str .= "<td>".$v['total_roi']."</td>";
                $data_str .= "<td>".$v['new_roi']."</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');
        }

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

        $count = $_data['count'];

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

        $end_time = date('Y-m-d');
        foreach($_data['data'] as $k=>&$v){

            $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){
                if($team_id>0) $query->where('team_id', $team_id);
            })->where('is_del',0)->where('dtime', $v['date'])->first();
            $v['total_cost'] = isset($result->total_cost) ? $result->total_cost : '';
            $v['total_fan_add'] = isset($result->total_fan_add) ? $result->total_fan_add : '';
            $v['dtime'] = $v['date'];

            #进粉成本
            $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;

             //当日加粉
            $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('is_del', 0)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->where('createTime','<',$end_time)->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;
            // 1.当日粉累计成单数
            $v['order_count'] = $order->order_count;
            // 2.当日粉累计成交额 
            $v['order_amount'] = $order->order_amount;
            #当日新粉roi=当日新增粉丝成交销售额/当日投放消耗额*100%
            $v['new_roi'] = $v['total_cost']>0?  round($v['new_order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            # 综合ROI=当日加粉累计成交额/当日投放消耗额*100%
            $v['roi'] = $v['total_cost']>0?  round($v['order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            # 导粉率=当日个人微信号粉丝数/当日公众号粉丝数*100%
            $v['fan_rate'] = $v['total_fan_add']>0?  round($v['wx_fan_add'] / $v['total_fan_add'], 4) * 100 .'%' : '';

        }      

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

        return view('statistics/throwDay', ['result' =>$_data['data'],
            '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');
        }

        $_data = $this->throwCtimePage($stime, $etime);

        $end_time = date('Y-m-d');
        foreach($_data as $k=>&$v){
            $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){
                if($team_id>0) $query->where('team_id', $team_id);
            })->where('is_del',0)->where('dtime', $v['date'])->first();
            $v['total_cost'] = isset($result->total_cost) ? $result->total_cost : '';
            $v['total_fan_add'] = isset($result->total_fan_add) ? $result->total_fan_add : '';
            $v['dtime'] = $v['date'];

            #进粉成本
            $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;

             //当日加粉
            $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('is_del', 0)->where(function($query) use($team_id){
                if($team_id>0) $query->where('team_id', $team_id);
            })->where('createTime','<',$end_time)->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;
            // 1.当日粉累计成单数
            $v['order_count'] = $order->order_count;
            // 2.当日粉累计成交额 
            $v['order_amount'] = $order->order_amount;
            #当日新粉roi=当日新增粉丝成交销售额/当日投放消耗额*100%
            $v['new_roi'] = $v['total_cost']>0?  round($v['new_order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            # 综合ROI=当日加粉累计成交额/当日投放消耗额*100%
            $v['roi'] = $v['total_cost']>0?  round($v['order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
            # 导粉率=当日个人微信号粉丝数/当日公众号粉丝数*100%
            $v['fan_rate'] = $v['total_fan_add']>0?  round($v['wx_fan_add'] / $v['total_fan_add'], 4) * 100 .'%' : '';

        }     

        $indexKey = ['dtime','total_cost','total_fan_add','cost_fan','wx_fan_add','cost_wx_fan','new_roi','roi','fan_rate'];
        $title = ['日期', '当日投放金额', '当日公众号进粉', '当日进粉成本', '当日微信粉','当日微信粉成本', '当日新粉ROI', '综合ROI', '导粉率'];
        
        $filename = 'toufang_'.date('Y-m-d_H').'.xlsx';
        return Order::export_excel($_data, $filename, $indexKey, $title);
    
    }
                     
    /**
     * 销售可看当日数据表
     *
     */
    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/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'))->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;

            #发货
            $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'] = $v['receivedAmount'] - $v['wx_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['profit'] = $v['wx_true_amount'] + $v['zfm_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'))->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;

            #发货
            $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'] = $v['receivedAmount'] - $v['wx_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['profit'] = $v['wx_true_amount'] + $v['zfm_true_amount'] - $v['cost'] - $v['freight_cost'];

        }

        $indexKey = ['team_name','ctime','order_count','receivedAmount','wx_money','zfm_money','aftersale_count','aftersale_fee','wx_refund_count','wx_refund_price','wx_true_amount','zfm_refund_count','zfm_refund_price','zfm_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); 
    }
                                                                                
}