<?php

namespace App\Http\Controllers;


use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\View;


class DownloadController extends Controller{

    public function excel_all(Request $request){
        $s=DB::table('season')->where('season',$request->season)->first();
        $start=$s->round_start_time;
        $end=$s->round_end_time;
        $season=DB::table('basic')->where('keys','=','season')->value('value');
        if ($season !==$request->season){
            $data=DB::select("select gl.integral,al.* from  (select f.*,e.war_names as war_name FROM (SELECT c.level_name,c.id,c.phone,c.up_phone,c.up_name,c.nickname,ifnull(b.total,0) as total, IFNULL(b.hard,0) as hard,IFNULL(b.simple,0) as simple, IFNULL(b.old,0) as old FROM `user` c LEFT JOIN (select sum(ifnull(a.type_one,0)) AS hard,sum(ifnull(a.type_two,0)) as simple,sum(ifnull(a.type_three,0)) as old,a.uid , (sum(ifnull(a.type_one,0))+sum(ifnull(a.type_two,0))+sum(ifnull(a.type_three,0))) as total from inte_review a WHERE a.time>=? AND a.time<? group By a.uid ) as b ON b.uid=c.id  JOIN enroll d WHERE d.uid=c.id and  d.status=0 and d.season=?) as f JOIN service e ON e.phone=f.up_phone) as al join glory gl on  al.id=gl.uid where gl.season=?;",[$start,$end,$request->season,$request->season]);
        }else{
            $data=DB::select("select f.*,e.war_names as war_name FROM (SELECT c.level_name,c.phone,c.up_phone,c.up_name,c.integral,c.nickname,ifnull(b.total,0) as total, IFNULL(b.hard,0) as hard,IFNULL(b.simple,0) as simple, IFNULL(b.old,0) as old FROM `user` c LEFT JOIN (select sum(ifnull(a.type_one,0)) AS hard,sum(ifnull(a.type_two,0)) as simple,sum(ifnull(a.type_three,0)) as old,a.uid , (sum(ifnull(a.type_one,0))+sum(ifnull(a.type_two,0))+sum(ifnull(a.type_three,0))) as total from inte_review a WHERE a.time>=? AND a.time<? group By a.uid ) as b ON b.uid=c.id  JOIN enroll d WHERE d.uid=c.id and  d.status=0 and d.season=?) as f JOIN service e ON e.phone=f.up_phone;",[$start,$end,$request->season]);
        }
//        $end=DB::table('basic')->where('keys','=','round_end_time')->value('value');
//        $data=DB::table('user')
//            ->join('service','service.phone','=','user.up_phone')
//            ->join('war_zone','war_zone.uid','=','service.uid')
//            ->join('enroll','enroll.uid','=','user.id')
//            ->where('enroll.status',0)
//            ->leftJoin('inte_review','user.id','=','inte_review.uid')
//            ->whereIn('user.level_name',['销售主管','销售经理'])
//            ->where(function ($query) use($start) {
//                $query->where('inte_review.created_at','>',date('Y-m-d H:i:s',$start))
//                    ->orWhereNull('inte_review.created_at');
//            })
//            ->groupBy('user.id')
//            ->select(DB::raw("SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old"),'user.id','user.integral','user.nickname','user.up_name','user.phone','user.up_phone','war_zone.war_name')
//            ->get()->toArray();

        return $this->success_list($data);
    }

    public function excel_every(Request $request){
        $start=$request->start;
        $end=$request->end;
        if ($request->season==19){
            $data=DB::select("SELECT al.*,w.war_names as war_name FROM (
SELECT
    u.nickname,
    u.phone,
    u.up_phone,
    u.up_name,
    u.level_name,
    IFNULL(DATA .hard, 0) AS hard,
    IFNULL(DATA .simple, 0) AS simple,
    IFNULL(DATA .old, 0) AS old,
    IFNULL(DATA .integral, 0) AS integral
FROM
    user u
LEFT JOIN (
    SELECT
        SUM(IFNULL(il.integral, 0)) AS integral,
        ins.*
    FROM
        inte_log il
    LEFT JOIN (
        SELECT
            sum(ifnull(a.type_one, 0)) AS hard,
            sum(ifnull(a.type_two, 0)) AS simple,
            sum(ifnull(a.type_three, 0)) AS old,
            a.uid AS auid
        FROM
            inte_review a
        WHERE
            a.time >= ?
        AND a.time <= ?
        GROUP BY
            a.uid
    ) AS ins ON il.uid = ins.auid
    WHERE
        il.created_at >= ?
    AND il.created_at <= ?
    GROUP BY
        il.uid
) AS DATA ON u.id = DATA .auid
JOIN enroll ON enroll.uid = u.id
WHERE
    enroll.`status` = 0 AND enroll.season=?)as al JOIN service w ON w.phone=al.up_phone
",[$start,$end-1,date('Y-m-d H:i:s',$start),date('Y-m-d H:i:s',$end),$request->season]);
        }else{
            $data=DB::select("SELECT al.*,w.war_names as war_name FROM (
SELECT
    u.nickname,
    u.phone,
    u.up_phone,
    u.up_name,
    u.level_name,
    IFNULL(DATA .hard, 0) AS hard,
    IFNULL(DATA .simple, 0) AS simple,
    IFNULL(DATA .old, 0) AS old,
    IFNULL(DATA .integral, 0) AS integral
FROM
    user u
LEFT JOIN (
    SELECT
        SUM(IFNULL(il.integral, 0)) AS integral,
        ins.*
    FROM
        inte_log il
    LEFT JOIN (
        SELECT
            sum(ifnull(a.type_one, 0)) AS hard,
            sum(ifnull(a.type_two, 0)) AS simple,
            sum(ifnull(a.type_three, 0)) AS old,
            a.uid AS auid
        FROM
            inte_review a
        WHERE
            a.time >= ?
        AND a.time <= ?
        GROUP BY
            a.uid
    ) AS ins ON il.uid = ins.auid
    WHERE
        il.time >= ?
    AND il.time <= ?
    GROUP BY
        il.uid
) AS DATA ON u.id = DATA .auid
JOIN enroll ON enroll.uid = u.id
WHERE
    enroll.`status` = 0 AND enroll.season=?)as al JOIN service w ON w.phone=al.up_phone
",[$start,$end-1,$start,$end-1,$request->season]);
        }

//        foreach ($data as $k=>$v){
//            $num=$v->hard=null?0:$v->hard+$v->old=null?0:$v->old+$v->simple=null?0:$v->simple;
////            $datas=$v;
//            if ($num>=3){
//                $datas[$k]['integral']=$num*20;
//            }else{
//                $datas[$k]['integral']=$num*10;
//            }
//            $datas[$k]['hard']=$v->hard==null?0:$v->hard;
//            $datas[$k]['simple']=$v->simple==null?0:$v->simple;
//            $datas[$k]['old']=$v->old==null?0:$v->old;
//            $datas[$k]['total']=$num;
//            $datas[$k]['nickname']=$v->nickname;
//            $datas[$k]['up_name']=$v->up_name;
//            $datas[$k]['phone']=$v->phone;
//            $datas[$k]['up_phone']=$v->phone;
//            $datas[$k]['war_name']=$v->war_name;
//        }
        return $this->success_list($data);
    }


    public function excel_grade(Request $request){
        $data=DB::table('inte_review')
            ->whereBetween('created_at',[date('Y-m-d H:i:s',$request->start),date('Y-m-d H:i:s',$request->end)])
            ->groupBy('uid')
            ->select(DB::raw("uid,SUM(type_one) as one,SUM(type_two) as two,SUM(type_three) as three"))
            ->get();
        $user=[];
        if ($data){

            foreach ($data as $k=>$v){
                $num=$v->one+$v->two+$v->three;
                $num=$num*10;
                if ($num>=30){
                   $user[$k]['id']=$v->uid;
                   $user[$k]['hard']=$v->one;
                   $user[$k]['simple']=$v->two;
                   $user[$k]['old']=$v->three;
                }
            }

            $uid=[];
            if ($user){
                foreach ($user as $k=>$v){
                    $uid[$k]=$v['id'];
                }
                $crown=DB::table('user')
                    ->join('service','service.phone','=','user.up_phone')
                    ->join('war_zone','war_zone.uid','=','service.uid')
                    ->whereIn('user.id',$uid)
                    ->select('user.id','user.nickname','user.up_name','user.phone','user.up_phone','war_zone.war_name as war_name')->get()->toArray();
//            foreach ($crown as $k=>$v){
//                $sid[$k]=$v->sid;
//            }
//            $war_zone=DB::table('war_zone')->whereIn('uid',$sid)->select('war_name','uid')->get();
            }else{
                return $this->success_list([]);
            }
            $crown=json_decode(json_encode($crown), true);
            $excel=[];
            foreach ($crown as $k=>$v){
                foreach ($user as $key=>$value){
                    if ($value['id']=$v['id']){
                        unset($value['id']);
                        $excel[$key]=array_merge($v,$value);
                    }
                }
            }
            return $this->response()->array([
                'code'=>200,
                'data'=>[
                    'list'=>$excel
                ]
            ]);
        }

        return $this->success_list([]);
    }

    public function repeat(){

        $data=DB::table('enroll')
            ->select('uid', DB::raw('count(*) as count'))
            ->where('status',0)
            ->groupBy('uid')
            ->havingRaw('count(*) > 1')
            ->get();
        foreach ($data as $k=>$v){
            $s[$k]=DB::table('user')->join('service','service.phone','=','user.up_phone')
                ->join('war_zone','war_zone.uid','=','service.uid')
                ->where('user.id',$v->uid)
                ->select('user.nickname','user.name','user.phone','user.up_phone','user.up_name','war_zone.war_name')
                ->first();
        }


        foreach ($s as $k=>$v){
            $name=DB::table('user')->join('war_zone','war_zone.uid','=','user.id')
                ->where('war_zone.war_name','=',$v->war_name)->value('user.nickname');
            $ss[$k][]=$v;
            $ss[$k]['service']=$name;
        }
       return $this->success_list($ss);
    }

    public function excel_rank(Request $request){

        $season=DB::table('basic')->where('keys','=','season')->value('value');
        if ($request->type==1){
            if ($request->season==$season){
                $data=DB::table('user')
                    ->join('enroll','enroll.uid','=','user.id')
                    ->join('service','service.phone','=','user.up_phone')
                    ->join('war_zone','war_zone.uid','=','service.uid')
                    ->where('enroll.status',0)
                    ->where('enroll.season',$request->season)
                    ->orderByDesc('user.integral')
                    ->select('user.nickname','user.id','user.phone','user.integral as score','user.up_name','user.level_name','service.uid as sid','war_zone.war_name','user.up_phone')
                    ->get();
                    return $this->success_list($data);
            }else{
                $data=DB::table('user')
                    ->join('glory','glory.uid','=','user.id')
                    ->join('service','service.phone','=','user.up_phone')
                    ->join('war_zone','war_zone.uid','=','service.uid')
                    ->where('glory.season',$request->season)
                    ->orderByDesc('glory.integral')
                    ->select('user.nickname','user.id','user.phone','glory.integral as score','user.up_name','user.level_name','service.uid as sid','war_zone.war_name','user.up_phone')
                    ->get();
                return $this->success_list($data);
            }

//            return $this->success_list([]);
        }

        if ($request->type==2){
            if ($request->season==$season){
                $data=DB::table('user')
                    ->join('service','service.phone','=','user.phone')
                    ->join('war_zone','war_zone.uid','=','service.uid')
                    ->where('user.level_name','=','代理公司')
                    ->orderByDesc('user.integral')
                    ->select('user.id','user.nickname','user.phone','user.integral as score','user.up_name','user.up_phone','service.uid as sid','war_zone.war_name')->get();
                return $this->success_list($data);
            }else{
                $data=DB::table('user')
                    ->join('service','service.phone','=','user.phone')
                    ->join('war_zone','war_zone.uid','=','service.uid')
                    ->join('glory','glory.uid','=','user.id')
                    ->where('glory.season','=',$request->season)
                    ->where('user.level_name','=','代理公司')
                    ->orderByDesc('glory.integral')
                    ->select('user.id','user.nickname','user.phone','glory.integral as score','user.up_name','user.up_phone','service.uid as sid','war_zone.war_name')->get();
                return $this->success_list($data);
            }

//            return $this->success_list([]);
        }
    }


    public function one_week(Request $request){
        $now=Carbon::now()->timestamp;
        $time=DB::table('season')->where('season',$request->season)->first();
        if ($request->week==1){
            $day=($now-$time->round_start_time)/86400;
            if ($day>=7){
                $data=$this->sql($time->round_start_time);
                return $this->success_list($data);
            }
            return $this->error(300);
        }
        if ($request->week==2){
            $day=($now-$time->round_start_time)/86400;
            if ($day>=14){
                $times=$time->round_start_time+86400*7;
                $data=$this->sql($times);
                return $this->success_list($data);
            }
            return $this->error(300);
        }
        if ($request->week==3){
            $day=($now-$time->round_start_time)/86400;
            if ($day>=21){
                $times=$time->round_start_time+86400*14;
                $data=$this->sql($times);
                return $this->success_list($data);
            }
            return $this->error(300);
        }

    }

    public function sql($time){
        $data=DB::select("SELECT p.*,m.war_names as war_name FROM service m JOIN 
(SELECT k.nickname,k.phone,k.up_name,k.up_phone,k.level_name FROM `user` k JOIN
(SELECT b.uid FROM (SELECT uid,SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old,(SUM(type_one)+sum(type_two)+SUM(type_three))as total FROM inte_review WHERE time>=? AND time<? GROUP BY uid) as b WHERE b.total>2
UNION ALL
SELECT c.uid FROM (SELECT uid,SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old,(SUM(type_one)+sum(type_two)+SUM(type_three))as total FROM inte_review WHERE time>=? AND time<? GROUP BY uid) as c WHERE c.total>2
UNION ALL
SELECT d.uid FROM (SELECT uid,SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old,(SUM(type_one)+sum(type_two)+SUM(type_three))as total FROM inte_review WHERE time>=? AND time<? GROUP BY uid) as d WHERE d.total>2
UNION ALL
SELECT e.uid FROM (SELECT uid,SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old,(SUM(type_one)+sum(type_two)+SUM(type_three))as total FROM inte_review WHERE time>=? AND time<? GROUP BY uid) as e WHERE e.total>2
UNION ALL
SELECT f.uid FROM (SELECT uid,SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old,(SUM(type_one)+sum(type_two)+SUM(type_three))as total FROM inte_review WHERE time>=? AND time<? GROUP BY uid) as f WHERE f.total>2
UNION ALL
SELECT g.uid FROM (SELECT uid,SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old,(SUM(type_one)+sum(type_two)+SUM(type_three))as total FROM inte_review WHERE time>=? AND time<? GROUP BY uid) as g WHERE g.total>2
UNION ALL
SELECT j.uid FROM (SELECT uid,SUM(type_one) as hard,SUM(type_two) as simple,SUM(type_three) as old,(SUM(type_one)+sum(type_two)+SUM(type_three))as total FROM inte_review WHERE time>=? AND time<? GROUP BY uid) as j WHERE j.total>2) L ON L.uid=k.id) as p ON m.phone=p.up_phone GROUP BY p.phone HAVING COUNT(p.phone)=7
",[$time,$time+86400,$time+86400,$time+86400*2,$time+86400*2,$time+86400*3,$time+86400*3,$time+86400*4,$time+86400*4,$time+86400*5,$time+86400*5,$time+86400*6,$time+86400*6,$time+86400*7]);
        return $data;
    }

    public function bonus(){
        $season=21;
        $data=DB::table('user')->where('level_name','<>','政委')->select('nickname','phone','up_name','level_name','up_phone','bonus')->orderByDesc('bonus')->get();
        $dt=[];
        foreach ($data as $k=>$v){
            $dt[$k]['昵称']=$v->nickname;
            $dt[$k]['手机号']=$v->phone;


            $dt[$k]['奖学金']=$v->bonus;
            $dt[$k]['等级']=$v->level_name;
            if ($v->level_name=='代理公司'){
                $dt[$k]['战区']=DB::table('service')->where('phone',$v->phone)->value('war_names');
                $dt[$k]['上级名称']=DB::table('user')->where('phone',$v->up_phone)->value('nickname');
            }else{
                $dt[$k]['战区']=DB::table('service')->where('phone',$v->up_phone)->value('war_names');
                $dt[$k]['上级名称']=$v->up_name;
            }
            $dt[$k]['上级手机号']=$v->up_phone;
        }
        return $this->success_list($dt);
    }
}