app-admin-module/Modules/Finance/Http/Controllers/StatisticalController.php

<?php

namespace App\Http\Controllers;

use App\Models\Activity;
use App\Models\GoodsSpec;
use App\Models\Ordertest;
use App\Models\WdStore;
use Carbon\Carbon;
use Illuminate\Http\Request;
use App\Models\GoodsSku;
use App\Models\User;
use App\Models\OrderGoods;
use App\Models\System;
use App\Models\Store;
use App\Models\Rewards;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use App\Exports\StatisticalExport;
use App\Http\Requests;
use Excel;


class StatisticalController extends Controller
{
    public function export(Request $request){
        return Excel::download(new StatisticalExport, 'users.csv');
    }
    //销售额统计(扇形图)
    public function  SalesStatisticsFan(){
        $starttime='2000-01-01 00:00:00';
        $endtime='2050-01-01 00:00:00';
        $list=$this->getStatisticalList($starttime,$endtime);
        return $this->success($list);
    }
    //销售额统计（柱形图）
    public function  SalesStatisticsCyl(Request $request){
        $time=date("Y-01-01 00:00:00",strtotime($request->get('year').'-01'));
        for($i=0;$i<12;$i++){
            $start=$time;
            $time= date("Y-m-d H:i:s",strtotime("$time +1 month"));
            $end=$time;
            $list[$i+1]=$this->getStatisticalList($start,$end);
        }
        $lists=[];
        foreach($list as $key=>$val){
            foreach($val as $k=>$v){
                $lists[$v['goods_id']]['name']=$v['goods']['name'];
                $lists[$v['goods_id']]['account'][$key]=$v['account'];
            }
        }
        return $this->success($lists);
    }
    //销售额统计（折线图）
    public function  SalesStatisticsLine(Request $request){
        $t=date('t', strtotime($request->get('month')));
        $time=date("Y-m-01 00:00:00",strtotime($request->get('month')));
        for($i=0;$i<$t;$i++){
            $start=$time;
            $time= date("Y-m-d H:i:s",strtotime("$time +1 days"));
            $end=$time;
            $list[$i+1]=$this->getStatisticalList($start,$end);
        }
        $lists=[];
        foreach($list as $key=>$val){
            foreach($val as $k=>$v){
                $lists[$v['goods_id']]['name']=$v['goods']['name'];
                $lists[$v['goods_id']]['account'][$key]=$v['account'];
            }
        }
        return $this->success_list($lists,$msg="SUCCESS",$t);
    }

    public function getStatisticalList($starttime,$endtime){
        $time=[$starttime,$endtime];
        $list=OrderGoods::
        select('goods_id','order_id',DB::raw('SUM(totalprice) as account'))//,DB::raw('SUM(totalprice) as account'),'order_id','sku_id','totalprice'
        ->with(['goods:id,name'])
            ->whereHas('order', function (Builder $query) use ($time) {
                $query->whereBetween('created_at',$time)//'2020-01-01 00:00:00'
                    ->whereIn('status',[2,3])
                    ->where('agent_id',1);
                })
            ->groupBy('goods_id')
            ->get();
        return $list;
    }

    //销售量统计(扇形图)
    public function  SalesStatisticsNumFan(){
        $starttime='2000-01-01 00:00:00';
        $endtime='2050-01-01 00:00:00';
        $list=$this->getStatisticalNumList($starttime,$endtime);
        return $this->success($list);
    }
    //销售量统计（柱形图）
    public function  SalesStatisticsNumCyl(Request $request){
        $time=date("Y-01-01 00:00:00",strtotime($request->get('year').'-01'));
        for($i=0;$i<12;$i++){
            $start=$time;
            $time= date("Y-m-d H:i:s",strtotime("$time +1 month"));
            $end=$time;
            $list[$i+1]=$this->getStatisticalNumList($start,$end);
        }
        $lists=[];
        foreach($list as $key=>$val){
            foreach($val as $k=>$v){
                $lists[$v['goods_id']]['name']=$v['goods']['name'];
                $lists[$v['goods_id']]['total'][$key]=$v['total'];
            }
        }
        return $this->success($lists);
    }
    //销售量统计（折线图）
    public function  SalesStatisticsNumLine(Request $request){
        $t=date('t', strtotime($request->get('month')));
        $time=date("Y-m-01 00:00:00",strtotime($request->get('month')));
        for($i=0;$i<$t;$i++){
            $start=$time;
            $time= date("Y-m-d H:i:s",strtotime("$time +1 days"));
            $end=$time;
            $list[$i+1]=$this->getStatisticalNumList($start,$end);
        }
        $lists=[];
        foreach($list as $key=>$val){
            foreach($val as $k=>$v){
                $lists[$v['goods_id']]['name']=$v['goods']['name'];
                $lists[$v['goods_id']]['total'][$key]=$v['total'];
            }
        }
        return $this->success_list($lists,$msg="SUCCESS",$t);
    }

    public function getStatisticalNumList($starttime,$endtime){
        $time=[$starttime,$endtime];
        $list=OrderGoods::
        select('goods_id','order_id',DB::raw('SUM(num) as total'))//,DB::raw('SUM(totalprice) as account'),'order_id','sku_id','totalprice'
        ->with(['goods:id,name'])
            ->whereHas('order', function (Builder $query) use ($time) {
                $query
                    ->whereBetween('created_at',$time)//'2020-01-01 00:00:00'
                    ->whereIn('status',[2,3])
                    ->where('agent_id',1)
                ;
            })
            ->groupBy('goods_id')
            ->get();
        return $list;
    }
//新增代理统计
    public function agentAddNum(Request $request)
    {
        $input=$request->all();
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        //新增代理统计
        $count['all'] = User::whereBetween('created_at', $time)->count();
        //新增皇冠代理统计
        $count['crow'] = User::whereBetween('level_at', $time)->where('level', 3)->count();
        //新增顶级代理统计
        $count['top'] = User::whereBetween('level_at', $time)->where('level', 2)->count();
        //新增特级代理统计
        $count['super'] = User::whereBetween('created_at', $time)->orwhereBetween('level_at',$time)->where('level', 1)->count();
        return $this->success($count);
    }
    public function agentAddNumLine(Request $request){
        $input=$request->all();
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        //新增代理统计
        $count['all'] = User::whereBetween('created_at', $time)
            ->selectRaw('DATE(created_at) as date,COUNT(*) as value')
            ->groupBy('date')
            ->get();
        //新增皇冠代理统计
        $count['crow'] = User::whereBetween('created_at', $time)
            ->where('level', 3)
            ->selectRaw('DATE(created_at) as date,COUNT(*) as value')
            ->groupBy('date')
            ->get();
        //新增顶级代理统计
        $count['top'] = User::whereBetween('created_at', $time)
            ->where('level', 2)
            ->selectRaw('DATE(created_at) as date,COUNT(*) as value')
            ->groupBy('date')
            ->get();
        //新增特级代理统计
        $count['super'] = User::whereBetween('created_at', $time)
            ->where('level', 1)
            ->selectRaw('DATE(created_at) as date,COUNT(*) as value')
            ->groupBy('date')
            ->get();
        return $this->success($count);
    }

    //代理业绩排行榜
    public function agentSalesResults(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        $search_name=$input['search_name'];
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count=DB::table('users')
            ->select('users.nickname','users.level','warea.name as warea','uu.nickname as agent',DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('order','order.user_id','=','users.id')
            ->leftJoin('order_goods','order.id','=','order_goods.order_id')
            ->leftJoin('warea','warea.id','=','users.warea_id')
            ->leftJoin('users as uu','uu.id','=','users.agent_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->where('users.nickname', 'like', '%' . $search_name . '%')
            ->groupBy('users.id')
            ->get()->count();

        // $list=User::
        // select('goods_id','order_id',DB::raw('SUM(num) as total'))//,DB::raw('SUM(totalprice) as account'),'order_id','sku_id','totalprice'
        // ->with(['agent:id,nickname','warea:id,name'])
        //     ->whereHas('order', function (Builder $query) use ($time) {
        //         $query
        //             ->whereBetween('created_at',$time)//'2020-01-01 00:00:00'
        //             ->whereIn('status',[2,3])
        //             ->whereHas('user', function (Builder $query1) {
        //                 $query1->where('level',3);
        //             })
        //         ;
        //     })
        //     ->groupBy('goods_id')
        //     ->get();

        if($count==0){
            $this->error('400001','没有数据');
        }
        $list=DB::table('users')
            ->select('users.nickname','users.level','warea.name as warea','uu.nickname as agent',DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('order','order.user_id','=','users.id')
            ->leftJoin('order_goods','order.id','=','order_goods.order_id')
            ->leftJoin('warea','warea.id','=','users.warea_id')
            ->leftJoin('users as uu','uu.id','=','users.agent_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->where('users.nickname', 'like', '%' . $search_name . '%')
            ->groupBy('users.id')
            ->orderBy('account','desc')
            ->skip($num)->take($page_size)
            ->get();
        if($list){
            return $this->success_list($list,'SUCCESS',$count);
        }else{
            return $this->error();
        }
    }
    //战区销售额排行
    public function wareaSalesResults(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count =DB::table('warea')
            ->select('warea.name as warea', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users', function ($join) {
                $join->on('users.warea_id', '=', 'warea.id')
                    ->where('users.level', '=', 3);
            })
            ->leftJoin('order', 'order.user_id', '=', 'users.id')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('warea.id')
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list = DB::table('warea')
            ->select('warea.name as warea', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users', function ($join) {
                $join->on('users.warea_id', '=', 'warea.id')
                    ->where('users.level', '=', 3);//只计算战区中皇冠的进货总金额
            })
            ->leftJoin('order', 'order.user_id', '=', 'users.id')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('warea.id')
            ->orderBy('account','desc')
            ->skip($num)->take($page_size)
            ->get();
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }
    //皇冠代理团队业绩排行
    public function crownTeamSalesResults(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count = DB::table('users')
            ->select('users.name as crowteam', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users as uu', 'users.id', '=', 'uu.agent_id')
            ->leftJoin('order', 'order.user_id', '=', 'uu.id')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->where('users.level', '=', 3)
            ->groupBy('users.id')
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list = DB::table('users')
            ->select('users.nickname as crowteam', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users as uu', 'users.id', '=', 'uu.agent_id')
            ->leftJoin('order', 'order.user_id', '=', 'uu.id')          //皇冠下级进货总量
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->where('users.level', '=', 3)
            ->groupBy('users.id')
            ->orderBy('account','desc')
            ->skip($num)->take($page_size)
            ->get();
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }
    //皇冠团队新增代理人数
    public function crownAddAgentNum(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count = DB::table('users')
            ->select('users.nickname as teamName','users.id',DB::raw('COUNT(uu.id) as count'))
            ->where('users.level', '=', 3)
            ->leftJoin('users as uu', 'users.id', '=', 'uu.crown_id')
            ->whereBetween('uu.created_at',$time)
            ->groupBy('users.id')
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list = DB::table('users')
            ->select('users.nickname as teamName','users.id',DB::raw('COUNT(uu.id) as count'))
            ->where('users.level', '=', 3)
            ->leftJoin('users as uu', 'users.id', '=', 'uu.crown_id')
            ->whereBetween('uu.created_at',$time)
            ->groupBy('users.id')
            ->orderBy('count','desc')
            ->skip($num)->take($page_size)
            ->get();
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }
    //店铺业绩排行榜
    public function storeSalesResults(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count = DB::table('store')
            ->select('store.name as storename', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users', function ($join) {
                $join->on('users.store_id', '=', 'store.id');
            })
            ->leftJoin('order', 'order.user_id', '=', 'users.id')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('store.id')
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list = DB::table('store')
            ->select('store.name as storename', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users', function ($join) {
                $join->on('users.store_id', '=', 'store.id');
            })
            ->leftJoin('order', 'order.user_id', '=', 'users.id')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('store.id')
            ->orderBy('account','desc')
            ->skip($num)->take($page_size)
            ->get();
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }
    //流水账单
    public function salesWaterBills(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count=DB::table('order')
            ->select('order.id','order.ordersn', DB::raw('SUM(order_goods.totalprice) as account'),DB::raw('SUM(order_goods.num) as total'),'order.created_at')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('order.id')
            ->orderBy('account','desc')
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list=DB::table('order')
            ->select('order.id','order.ordersn', DB::raw('SUM(order_goods.totalprice) as account'),DB::raw('SUM(order_goods.num) as total'),'order.created_at')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('order.id')
            ->orderBy('account','desc')
            ->skip($num)->take($page_size)
            ->get();
        foreach($list as $key =>$val){
            $goodsinfo=DB::table('order_goods')->select('goods.name','order_goods.sku_id','order_goods.price','order_goods.num','order_goods.totalprice')
                ->where('order_id',$val->id)
                ->leftJoin('goods','goods.id','order_goods.goods_id')
                ->get();
            foreach($goodsinfo as $k =>$v){
                $goodsinfo[$k]->sku=$this->getSku($v->sku_id);
            }

            $list[$key]->goods=$goodsinfo;
        }
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }
    public function getSku($id){
        $goodsSku=GoodsSku::withTrashed()->find($id);
        $path=$goodsSku->sku_path;
        $arr=explode('/',$path);
        $goodsSku=[];
        foreach($arr as $k=>$v){
            $upSpec=GoodsSpec::withTrashed()->where('id',$v)->first();
            $upSpec['uptitle']=GoodsSpec::withTrashed()->where('id',$upSpec->pid)->value('title');
            $goodsSku[$upSpec['uptitle']]=$upSpec->title;
        }
        return $goodsSku;
    }
    //进货奖励
    public function rewardSpending(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch($input['type']){
            case '0':
                $starttime=date("Y-m-d 00:00:00",strtotime($input['time'][0]));
                $endtime= date("Y-m-d 00:00:00",strtotime($input['time'][1] ."+1 days"));
                break;
            case '1':
                $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));
                break;
            case '2':
                $starttime=date("Y-01-01 00:00:00",strtotime($input['time']));
                $endtime= date("Y-01-01 00:00:00",strtotime($input['time'] ."+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count=DB::table('rewards')
            ->select('rewards.id','uu.nickname as agent_name','users.nickname as recom_name','rewards.bonus','rewards.created_at')
            // DB::raw('SUM(rewards.bonus) as account')
            ->leftJoin('users','users.id','=','rewards.recom_id')
            ->leftJoin('users as uu','uu.id','=','rewards.agent_id')
            // ->where('rewards.agent_id',0)
            ->where('rewards.type', 1)
            ->whereBetween('rewards.created_at',$time)
            // ->groupBy('rewards.recom_id')
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list=DB::table('rewards')
            ->select('rewards.id','uu.nickname as agent_name','users.nickname as recom_name','rewards.bonus','rewards.created_at')
            ->leftJoin('users','users.id','=','rewards.recom_id')
            ->leftJoin('users as uu','uu.id','=','rewards.agent_id')
            // ->where('rewards.agent_id',0)
            ->where('rewards.type',1)
            ->whereBetween('rewards.created_at',$time)
            // ->groupBy('rewards.recom_id')
            ->orderBy('rewards.id','desc')
            ->skip($num)->take($page_size)
            ->get();
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }
    //业绩奖励
    public function performanceReward(Request $request)
    {
        $input = $request->all();
        $page_size = $input['page_size'];
        $page_index = $input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch ($input['type']) {
            case '0':
                $starttime = date("Y-m-d 00:00:00", strtotime($input['time'][0]));
                $endtime = date("Y-m-d 00:00:00", strtotime($input['time'][1] . "+1 days"));
                break;
            case '1':
                $starttime = date("Y-m-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-m-01 00:00:00", strtotime($input['time'] . "+1 months"));
                break;
            case '2':
                $starttime = date("Y-01-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-01-01 00:00:00", strtotime($input['time'] . "+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count = DB::table('rewards')
            ->select('rewards.id','users.nickname', 'rewards.bonus','rewards.month','rewards.created_at')
            ->leftJoin('users', 'users.id', '=', 'rewards.recom_id')
            // ->where('rewards.agent_id', 0)
            ->where('rewards.type', 2)
            ->whereBetween('rewards.created_at', $time)
            // ->groupBy('rewards.recom_id')
            ->get()->count();
        if ($count == 0) {
            $this->error('400001', '没有数据');
        }
        $list = DB::table('rewards')
            ->select('rewards.id','users.nickname', 'rewards.bonus','rewards.month','rewards.created_at')
            ->leftJoin('users', 'users.id', '=', 'rewards.recom_id')
            // ->where('rewards.agent_id', 0)
            ->where('rewards.type', 2)
            ->whereBetween('rewards.created_at', $time)
            // ->groupBy('rewards.recom_id')
            ->orderBy('rewards.id', 'desc')
            ->skip($num)->take($page_size)
            ->get();
        if ($list) {
            return $this->success_list($list, 'SUCCESS', $count);
        } else {
            return $this->error();
        }
    }
    //店铺奖励
    public function storeReward(Request $request)
    {
        $input = $request->all();
        $page_size = $input['page_size'];
        $page_index = $input['page_index'];
        $num = ($page_index - 1) * $page_size;
        switch ($input['type']) {
            case '0':
                $starttime = date("Y-m-d 00:00:00", strtotime($input['time'][0]));
                $endtime = date("Y-m-d 00:00:00", strtotime($input['time'][1] . "+1 days"));
                break;
            case '1':
                $starttime = date("Y-m-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-m-01 00:00:00", strtotime($input['time'] . "+1 months"));
                break;
            case '2':
                $starttime = date("Y-01-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-01-01 00:00:00", strtotime($input['time'] . "+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $count = DB::table('rewards')
            ->select('store.id', 'store.name','goods.name','rewards.*')
            ->leftJoin('goods', 'goods.id', '=', 'rewards.goods_id')
            ->leftJoin('store', 'store.id', '=', 'rewards.store_id')
            // ->where('rewards.agent_id', 0)
            ->where('rewards.type', 3)
            ->whereBetween('rewards.created_at', $time)
            // ->groupBy('store.id')
            ->get()->count();
        if ($count == 0) {
            $this->error('400001', '没有数据');
        }
        $list = DB::table('rewards')
            ->select('store.id', 'store.name','goods.name as goodsname','rewards.*')
            ->leftJoin('goods', 'goods.id', '=', 'rewards.goods_id')
            ->leftJoin('store', 'store.id', '=', 'rewards.store_id')
            // ->where('rewards.agent_id', 0)
            ->where('rewards.type', 3)
            ->whereBetween('rewards.created_at', $time)
            // ->groupBy('store.id')
            ->orderBy('rewards.id', 'desc')
            ->skip($num)->take($page_size)
            ->get();
        if ($list) {
            return $this->success_list($list, 'SUCCESS', $count);
        } else {
            return $this->error();
        }
    }
    //店铺奖励详情
    public function getStoreRewardDetail(Request $request){
        $input = $request->all();
        switch ($input['type']) {
            case '0':
                $starttime = date("Y-m-d 00:00:00", strtotime($input['time'][0]));
                $endtime = date("Y-m-d 00:00:00", strtotime($input['time'][1] . "+1 days"));
                break;
            case '1':
                $starttime = date("Y-m-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-m-01 00:00:00", strtotime($input['time'] . "+1 months"));
                break;
            case '2':
                $starttime = date("Y-01-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-01-01 00:00:00", strtotime($input['time'] . "+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $list = DB::table('users')
            ->select('users.id', 'users.nickname', DB::raw('SUM(rewards.bonus) as account'))
            ->leftJoin('users', 'users.store_id', '=', 'store.id')
            ->leftJoin('rewards', 'users.id', '=', 'rewards.recom_id')
            ->where('users.store_id',$input['id'])
            ->where('rewards.agent_id', 0)
            ->where('rewards.type', 2)
            ->whereBetween('rewards.created_at', $time)
            ->groupBy('users.id')
            ->get();
        if ($list) {
            return $this->success($list);
        } else {
            return $this->error();
        }
    }
    //获取代理业绩奖励结算信息
    public function getAgentSalesResult(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        $search_name=$input['search_name'];

        $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
        $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));

        $time = [$starttime, $endtime];
        $sales_area_reward_str=System::where('keys','sales_area_reward')->value('values');
        $sales_area_reward_arr=explode('/',$sales_area_reward_str);
        foreach($sales_area_reward_arr as $key =>$val){
            $sales_area_reward[$key]=explode('*',$val);
        }
        $result = array();
        foreach($sales_area_reward as $k=>$v){
            $result[$k] = $v[0];
        }
        $minValue=min($result);

        $count=DB::table('users')
            ->select('users.id','users.nickname','users.level','warea.name as warea','uu.nickname as agent',DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('order','order.user_id','=','users.id')
            ->leftJoin('order_goods','order.id','=','order_goods.order_id')
            ->leftJoin('warea','warea.id','=','users.warea_id')
            ->leftJoin('users as uu','uu.id','=','users.agent_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->where('users.nickname', 'like', '%' . $search_name . '%')
            ->groupBy('users.id')
            ->having('account','>=',$minValue)
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list=DB::table('users')
            ->select('users.id','users.nickname','users.level','warea.name as warea','uu.nickname as agent',DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('order','order.user_id','=','users.id')
            ->leftJoin('order_goods','order.id','=','order_goods.order_id')
            ->leftJoin('warea','warea.id','=','users.warea_id')
            ->leftJoin('users as uu','uu.id','=','users.agent_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->where('users.nickname', 'like', '%' . $search_name . '%')
            ->groupBy('users.id')
            ->having('account','>=',$minValue)
            ->orderBy('account','desc')
            ->skip($num)->take($page_size)
            ->get();
            foreach ($list as $key=>$val){
                $rewards=Rewards::where('recom_id',$val->id)->where('type',2)->where('month',$input['time'])->first();
                if($rewards){
                    $list[$key]->finished=1;
                }else{
                    $list[$key]->finished=0;
                }
                foreach($sales_area_reward as $k=>$v){
                    if($val->account>=$v[0] && $val->account<$v[1]){
                        $list[$key]->rewards=round($val->account*$v[2]/100,2);
                    }
                }
                $list[$key]->minValue=$minValue;
            }
        if($list){
            return $this->success_list($list,'SUCCESS',$count);
        }else{
            return $this->error();
        }
    }

    //获取门店业绩奖励货物结算信息
    public function getStoreSalesResultGoods(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        $type=$input['type'];
        $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
        $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));

        $time = [$starttime, $endtime];
        if($type==1){
           $rate=System::where('keys','store_reward_goods_job')->value('values');
        }elseif($type==2){
            $rate=System::where('keys','store_reward_goods_enjoy')->value('values');
        }

        $count = DB::table('store')
            ->select('store.name as storename', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users', function ($join) {
                $join->on('users.store_id', '=', 'store.id');
            })
            ->leftJoin('order', 'order.user_id', '=', 'users.id')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->leftJoin('users as uu','uu.id','=','store.man_id')
            ->where('store.type',$type)
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('store.id')
            ->get()->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list = DB::table('store')
            ->select('store.id','store.name as storename','store.man_id','uu.nickname', DB::raw('SUM(order_goods.totalprice) as account'))
            ->leftJoin('users', function ($join) {
                $join->on('users.store_id', '=', 'store.id');
            })
            ->leftJoin('order', 'order.user_id', '=', 'users.id')
            ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
            ->leftJoin('users as uu','uu.id','=','store.man_id')
            ->where('store.type',$type)
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at',$time)
            ->groupBy('store.id')
            ->orderBy('account','desc')
            ->skip($num)->take($page_size)
            ->get();
            foreach($list as $key =>$val){
                $rewards=Rewards::where('recom_id',$val->man_id)->where('type',3)->where('month',$input['time'])->first();
                if($rewards){
                    $list[$key]->finished=1;
                }else{
                    $list[$key]->finished=0;
                }
                $list[$key]->style=2;
                $list[$key]->rewards=round($val->account*$rate/100,2);
            }
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }
     //获取门店业绩奖励钱结算信息
    public function getStoreSalesResultMoney(Request $request){
        $input=$request->all();
        $page_size=$input['page_size'];
        $page_index=$input['page_index'];
        $num = ($page_index - 1) * $page_size;
        $type=$input['type'];
        $starttime=date("Y-m-01 00:00:00",strtotime($input['time']));
        $endtime= date("Y-m-01 00:00:00",strtotime($input['time'] ."+1 months"));

        $time = [$starttime, $endtime];
        if($type==2){
           $rate=System::where('keys','store_reward_money_enjoy')->value('values');
        }elseif($type==3){
            $rate=System::where('keys','store_reward_money_experience')->value('values');
        }
        $rates=explode('/',$rate);
        $count=Store::where('type',$type)->with(['user:id,nickname'])->count();
        if($count==0){
            $this->error('400001','没有数据');
        }
        $list=Store::where('type',$type)
            ->with(['user:id,nickname'])
            ->orderBy('id','desc')->skip($num)->take($page_size)->get();
        foreach($list as $key=>$val){
            $rewards=Rewards::where('recom_id',$val->man_id)->where('type',3)->where('month',$input['time'])->first();
                if($rewards){
                    $list[$key]->finished=1;
                }else{
                    $list[$key]->finished=0;
                }
            $list[$key]->style=1;
            $list[$key]->acount=0;
            $info=DB::table('store')
                ->select('goods.type', DB::raw('SUM(order_goods.num) as total'))
                ->leftJoin('users', function ($join) {
                    $join->on('users.store_id', '=', 'store.id');
                })
                ->leftJoin('order', 'order.user_id', '=', 'users.id')
                ->leftJoin('order_goods', 'order.id', '=', 'order_goods.order_id')
                ->leftJoin('goods', 'goods.id', '=', 'order_goods.goods_id')
                ->where('store.id',$val['id'])
                ->where('store.type',$type)
                ->whereIn('order.status',[2,3])
                ->whereBetween('order.created_at',$time)
                ->groupBy('store.id','goods.type')
                ->get();
                foreach($info as $k =>$v){
                    if($v->type==1){
                        $info[$k]->price=$rates[0];
                        $info[$k]->totalprice=$v->total*$rates[0];
                        $list[$key]->acount+=$v->total*$rates[0];
                    }
                    if($v->type==2){
                        $info[$k]->price=$rates[1];
                        $info[$k]->totalprice=$v->total*$rates[1];
                        $list[$key]->acount+=$v->total*$rates[1];
                    }
                    if($v->type==3){
                        $info[$k]->price=$rates[2];
                        $info[$k]->totalprice=$v->total*$rates[2];
                        $list[$key]->acount+=$v->total*$rates[2];
                    }
                }
            $list[$key]->info=$info;
        }
        if ($list) {
            return $this->success_list($list,'SUCCESS',$count);
        } else {
            return $this->error();
        }
    }

    //订单统计
    public function getOrderGoodsNumList(Request $request){
        $input=$request->all();
        switch ($input['type']) {
            case '0':
                $starttime = date("Y-m-d 00:00:00", strtotime($input['time'][0]));
                $endtime = date("Y-m-d 00:00:00", strtotime($input['time'][1] . "+1 days"));
                break;
            case '1':
                $starttime = date("Y-m-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-m-01 00:00:00", strtotime($input['time'] . "+1 months"));
                break;
            case '2':
                $starttime = date("Y-01-01 00:00:00", strtotime($input['time']));
                $endtime = date("Y-01-01 00:00:00", strtotime($input['time'] . "+1 year"));
                break;
        }
        $time = [$starttime, $endtime];
        $list=DB::table('goods')
            ->select('goods.id','goods.name', DB::raw('COUNT(distinct order.id) as account'))
            ->leftJoin('order_goods','order_goods.goods_id','=','goods.id')
            ->leftJoin('order','order.id','=','order_goods.order_id')
            ->whereIn('order.status',[2,3])
            ->whereBetween('order.created_at', $time)
            ->groupBy('goods.id')
            ->get();
        if ($list) {
            return $this->success($list);
        } else {
            return $this->error();
        }
    }

    /*获取所有的代理公司**/
    public function GetCrown(){
        $user_ids=Ordertest::where('is_deleted',0)->groupBy('user_id')->pluck('user_id')->toArray();
        $crown_ids=User::where('level',3)->whereNull('deleted_at')->pluck('id')->toArray();
        $crown_ids=array_unique(array_merge($crown_ids,$user_ids));
        $user=User::whereIn('id',$crown_ids)->select('id','remark_name','nickname','mobile','realname')->get();
        return $this->success($user);
    }

    /**每个代理公司每次活动统计*/
    public function GetCrownMoney(Request $request){
        $id=$request->input('id');
        $activity_list=Activity::where('disabled',0)->whereNull('deleted_at')->orderBy('id')->get();

        $data=[];
        foreach ($activity_list as $k=>$v){
            $money=Ordertest::where('user_id',$id)->where('is_deleted',0)->whereBetween('created_at',[$v->start_time,$v->end_time])->sum('money');
            $data[$k]['money']=$money;
            $data[$k]['title']=$v->title;
            $data[$k]['start_time']=$v->start_time;
            $data[$k]['end_time']=$v->end_time;
        }
        return $this->success($data);
    }

    /**今年活动金额*/
    public function NewYearMoney(){
        $time_start=Carbon::now()->startOfYear();
        $time_end=Carbon::now()->endOfYear();
        $moneys=Ordertest::where('is_deleted',0)->whereBetween('created_at',[$time_start,$time_end])->sum('money');
        return $this->success($moneys);
    }

    public function ActivityMoney(){
        $activity_list=Activity::where('disabled',0)->whereNull('deleted_at')->orderBy('id')->get();

        $data=[];
        foreach ($activity_list as $k=>$v){
            $money=Ordertest::where('is_deleted',0)->whereBetween('created_at',[$v->start_time,$v->end_time])->sum('money');
            $data[$k]['money']=$money;
            $data[$k]['title']=$v->title;
            $data[$k]['start_time']=$v->start_time;
            $data[$k]['end_time']=$v->end_time;
        }
        return $this->success($data);
    }

    public function GetCrownInfo(Request $request){
       $data=User::whereNull('deleted_at')->where('level',3)->get();
       $re=[];
       foreach ($data as $k=>$v){
           $re[$k]['备注昵称']=$v->remark_name;
           $re[$k]['真实姓名']=$v->realname;
           $re[$k]['手机号']=$v->mobile;
           $re[$k]['身份证号']=WdStore::where('user_id',$v->id)->value('idCard');
       }
       return $this->success_list($re);
    }

    public function GetTeamCount(){
        $data=User::whereNull('deleted_at')->where('level',3)->get();
        $re=[];
        foreach ($data as $k=>$v){
            $ids=User::where('agent_id',$v->id)->whereIn('cert_status',[3,6])->pluck('id')->toArray();
            $idss=User::whereIn('agent_id',$ids)->whereIn('cert_status',[3,6])->pluck('id')->toArray();
            $id_all=array_merge($ids,$idss);
            $count=count($id_all);
            $count1=User::whereIn('id',$id_all)->where('level',1)->count();
            $count2=User::whereIn('id',$id_all)->where('level',2)->count();
            $re[$k]['count']=$count;
            $re[$k]['count_jl']=$count2;
            $re[$k]['count_zg']=$count1;
            $re[$k]['nickname']=$v->remark_name=null?$v->nickname:$v->remark_name;
        }
        $last_ages = array_column($re,'count');
        array_multisort($last_ages ,SORT_DESC,$re);
       return $this->success_list($re);
    }

    public function GetNewTeamCount(){
        $data=User::whereNull('deleted_at')->where('level',3)->get();
        $re=[];
        $new_ids=User::whereIn('cert_status',[3,6])->whereBetween('created_at',[Carbon::now()->startOfDay(),Carbon::now()->endOfDay()])->pluck('id')->toArray();
        $i=0;
        foreach ($data as $k=>$v){
            $ids=User::where('agent_id',$v->id)->whereIn('cert_status',[3,6])->pluck('id')->toArray();
            $idss=User::whereIn('agent_id',$ids)->whereIn('cert_status',[3,6])->pluck('id')->toArray();
            $id_all=array_merge($ids,$idss);
            $arr=array_intersect($new_ids,$id_all);
            if (count($arr)){
                $re[]=[
                    'count'=>count($arr),
                    'nickname'=>$v->remark_name=null?$v->nickname:$v->remark_name
                ];

            }
        }
        $last_ages = array_column($re,'count');
        array_multisort($last_ages ,SORT_DESC,$re);
        return $this->success_list($re);
    }

    public function GetAreaCount(Request $request){
        $province=$request->input('province','');
        $city=$request->input('city','');
        $area=$request->input('area','');
        $user=User::whereNull('deleted_at')->whereIn('cert_status',[3,6]);

        if ($province){
            $user->where('province',$province);
        }
        if ($city){
            $user->where('city',$city);
        }
        if ($area){
            $user->where('country',$area);
        }
        $user=$user->where('id','>',1)->select(DB::raw('count(*) as total'),'level')->groupBy('level')->get();
        return $this->success($user);
    }
}