<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Magasin;
use App\Admin;
use App\Ville;
use App\Win;
use Excel;
use DB;
class SuperController extends Controller
{

    public function __construct()
    {
        $this->middleware(['auth','superOnly']);
    }
    
    function addAdminForm(){
    	return view('admin.addAdmin');
    }

    function addAdmin(Request $data){

    	$this->validate($data,[
			'name' => 'required|string|max:255',
            'email' => 'required|string|email|max:255|unique:admins',
			'tel' => 'required|numeric',
            'password' => 'required|string|min:6|confirmed',
    		]);

    	$admin = new Admin();

        $admin->name = $data['name'];
        $admin->email = $data['email'];
        $admin->tel = $data['tel'];
        $admin->password = bcrypt($data['password']);

        $admin->save();
        return redirect('/super/admins');
    }

    function adminsList(){

    	return view('admin.adminsList');

    }
    function getAdminsList(){

    	return ['admins' => Admin::all()->filter(function ($user, $key) {return !$user->isSuper();}), 'magasins' => Magasin::all()];

    }

    function putAdmin(Request $data){

    	$this->validate($data,[
    		'*.id' => 'required|numeric',
			'*.name' => 'required|string|max:255',
			'*.tel' => 'required|numeric',
            '*.email' => 'required|string|email|max:255|unique:admins,email,'.$data->admin['id'],
    		]);

    	$admin = Admin::find($data->admin['id']);
    	$admin->name = $data->admin['name'];
    	$admin->tel = $data->admin['tel'];
    	$admin->email = $data->admin['email'];
    	$admin->magasin_id = $data->admin['magasin_id'];
    	$admin->save();

    	return $data->admin;
    }

    function deleteAdmin(Request $data){

    	$admin = Admin::find($data->admin['id']);
    	$admin->delete();

    	return ['admins' => Admin::all()->filter(function ($user, $key) {return !$user->isSuper();}), 'magasins' => Magasin::all()];

    }

    function WinsStats(Request $data){

    			$wins = array();
    			$wins['villes'] = Ville::all();
    			$wins['byVille'] = $this->getWinsByVille($data->ville);
    			
    			return response($wins,200);

    }

    function WinsChart(Request $data){

    			$wins = array();
    			$wins['byDay'] = $this->getWinsByDay();
    			$wins['Rewards'] = $this->countRewards();
    			$wins['byDayByVille'] = $this->getWinsByDayByVille();
    			$wins['villes'] = Ville::all();

    			return response($wins,200);

    }

    function stats(){
    	return view('admin.stats');
    }

    function getWinsByVille($ville = null){

    	if ($ville) {
    		
    		$wins = Win::join('users', 'users.id', '=', 'wins.user_id')
    							  ->join('villes', 'villes.id', '=', 'users.ville_id')
            		->where('villes.id',$ville)
            		->groupBy('villes.id')
            		->select(array(
                                'villes.intitule as ville',
                                DB::raw('count(wins.id) as countWins')
                            ))

                    ->get();


    		$winsGiven = Win::join('users', 'users.id', '=', 'wins.user_id')
    							  ->join('villes', 'villes.id', '=', 'users.ville_id')
            		->where('villes.id',$ville)
            		->groupBy('wins.isGiven')
            		->groupBy('villes.id')
            		->select(array(
                                'wins.isGiven',
                                'villes.intitule as ville',
                                DB::raw('count(wins.id) as countWins')
                            ))

                    ->get();


    	}else{

    		$wins = Win::join('users', 'users.id', '=', 'wins.user_id')
    							  ->join('villes', 'villes.id', '=', 'users.ville_id')
            		->groupBy('villes.id')
            		->select(array(
                                'villes.intitule as ville',
                                DB::raw('count(wins.id) as countWins')
                            ))

                    ->get();


    		$winsGiven = Win::join('users', 'users.id', '=', 'wins.user_id')
    							  ->join('villes', 'villes.id', '=', 'users.ville_id')
            		->groupBy('wins.isGiven')
            		->groupBy('villes.id')
            		->select(array(
                                'wins.isGiven',
                                'villes.intitule as ville',
                                DB::raw('count(wins.id) as countWins')
                            ))

                    ->get();
		}


            $remis = $winsGiven->filter(function($win){
            	return $win->isGiven;
            });

            $nonRemis = $winsGiven->filter(function($win){
            	return !$win->isGiven;
            });
            return ['wins'=>$wins, 'remis'=>$remis, 'nonRemis'=>$nonRemis];
    }

    function getWinsByDay(){


    	return Win::groupBy(DB::raw('DAY(updated_at)'))
        		->groupBy('wins.isGiven')
        		->select(array('wins.isGiven',DB::raw('count(wins.id) as countWins'),DB::raw('DAY(updated_at) as day'),))
                ->get();



    }

    function getWinsByDayByVille(){


    	return 	Win::join('users', 'users.id', '=', 'wins.user_id')
				->join('villes', 'villes.id', '=', 'users.ville_id')
        		->groupBy('users.ville_id')
        		->groupBy('wins.isGiven')
    			->groupBy(DB::raw('DAY(wins.updated_at)'))
        		->select(array('wins.isGiven',DB::raw('count(wins.id) as countWins'),DB::raw('DAY(wins.updated_at) as day'),'villes.intitule as ville'))
                ->get();



    }

    function countRewards(){

    	return Win::join('rewards', 'rewards.id', '=', 'wins.reward_id')
				            		->groupBy('wins.reward_id')
				            		->select(array(
				                                'rewards.resultText as reward',
				                                DB::raw('count(wins.reward_id) as countRewards')
				                            ))
				                    ->get();

    }

    function export(){

        $wins = Win::all();

            $wins->map(function ($win) {
                $win['reward'] = $win->reward;
                $win['user'] = $win->user;
                $win['ville'] = $win->user->ville;
                $win['admin'] = $win->admin;
                return $win;
            });

        // return view('admin.excelView',['wins'=>$wins]);
        Excel::create('cosmos Rewards', function($excel) use ($wins) {

                $excel->sheet('New sheet', function($sheet)  use ($wins) {

                    $sheet->loadView('admin.excelView',['wins'=>$wins]);

                });

        })->export('xls');;


    }
}
