<?php

namespace App\Repositories;

use App\Exercise;
use App\TrainingSession;
use App\TrainingWorkout;
use App\TrainingWorkoutLine;
use App\TrainingWorkoutSection;
use App\UserBenchmark;
use Carbon\Carbon;
use http\Client\Curl\User;
use Illuminate\Support\Facades\DB;
use mysql_xdevapi\Exception;

class BusinessIntelligenceRepository implements RepositoryInterface{
    public static function all() {
        return true;
    }

    public static function create(array $data){
        return true;
    }

    public static function update(array $data, $id){
        return true;
    }

    public static function delete($id){
        return true;
    }

    public static function find($id)
    {
        return true;
    }

    public static function getCategories() {
        $cat = ["Todos"];
        foreach(TrainingWorkout::select('category')->groupBy('category')->get() as $c) {
            if($c->category!=null) array_push($cat,$c->category);
        }
        return $cat;
    }

    public static function getWorkoutsUser($id_user, $params) {
        $user = UserRepository::find($id_user);
        $box_id = $params['box_id'];
        $data_ini = $params['data_ini'];
        $data_fi = $params['data_fi'];
        $sort_dir = $params['sort_dir'];
        $sort_col = $params['sort_col'];
        $name_filter = $params['name'];

        $ub = UserBenchmark::where('user_id',$id_user)
            ->get();

        $ub = collect($ub)->sortBy('training_session.date_start');

        $w = TrainingWorkout::where(function($n) use ($name_filter) {
            $n->where('name','LIKE','%'.$name_filter.'%')
                ->orWhere('name','LIKE',$name_filter.'%')
                ->orWhere('name','LIKE','%'.$name_filter);
        })->get();

        $cat = [];
        foreach(TrainingWorkout::select('category')->groupBy('category')->get() as $c) {
            if($c->category!=null) array_push($cat,$c->category);
        }

        foreach ($w as $wr) {
            $sesiones = [];
            foreach ($ub as $b) {
                foreach ($b->user_benchmark_sections as $section) {
                    foreach ($section->user_benchmark_lines as $line) {
                        if (TrainingWorkoutLine::find($line->training_workout_line_id)->exercise_id == $wr->id) {
                            array_push($sesiones, $b);
                        }
                    }
                }
            }
            $wr['benchmarks'] = $sesiones;
            $wr['categories'] = $cat;
        }

        return $w;
    }

    public static function getExercisesUser($id_user, $params) {
        $user = UserRepository::find($id_user);
        $box_id = $params['box_id'];
        $data_ini = $params['data_ini'];
        $data_fi = $params['data_fi'];
        $sort_dir = $params['sort_dir'];
        $sort_col = $params['sort_col'];
        $name_filter = $params['name'];
        $lvl = $params['lvl'];

        $ub = UserBenchmark::where('user_id',$id_user)
            ->get();

        $ub = collect($ub)->sortBy('training_session.date_start');

        if($lvl) {
            $e = Exercise::where(function($n) use ($name_filter) {
                $n->where('name','LIKE','%'.$name_filter.'%')
                    ->orWhere('name','LIKE',$name_filter.'%')
                    ->orWhere('name','LIKE','%'.$name_filter);
            })->where('level','=',$lvl)->orderBy($sort_col,$sort_dir)->get();
        } else {
            $e = Exercise::where(function($n) use ($name_filter) {
                $n->where('name','LIKE','%'.$name_filter.'%')
                    ->orWhere('name','LIKE',$name_filter.'%')
                    ->orWhere('name','LIKE','%'.$name_filter);
            })->orderBy($sort_col,$sort_dir)->get();
        }
        foreach ($e as $ex) {
            $sesiones = [];
            $workouts = [];
            $grafic = [];
            foreach ($ub as $b) {
                foreach ($b->user_benchmark_sections as $section) {
                    foreach ($section->user_benchmark_lines as $line) {
                        if (TrainingWorkoutLine::find($line->training_workout_line_id)->exercise_id == $ex->id) {
                            array_push($sesiones, $b);
                            array_push($workouts, TrainingWorkout::find($b->training_workout_id));
                            if ($line->val!=null) {
                                $valor = $line->units=='lb' ? ($line->val*2.20462) : ($line->units=='mile' ? $line->val*0.000621371 : $line->val);
                                array_push($grafic,
                                    [
                                        "name" => Carbon::createFromFormat('Y-m-d H:i:s',TrainingSession::find($b->training_session_id)->day)->format('d/m/Y'),
                                        "value" => (int)number_format($valor,2,',','')
                                    ]
                                );
                            }

                        }
                    }
                }
            }
            $ex['benchmarks'] = $sesiones;
            $ex['graph'] = $grafic;
            $ex['workouts'] = $workouts;
        }

        return $e;
    }

    public static function getTrainingSessionsUser($id_user, $params) {
        $user = UserRepository::find($id_user);
        $box_id = isset($params['box_id']) ? $params['box_id'] : null;
        $data_ini = isset($params['data_ini']) ? $params['data_ini'] : null;
        $data_fi = isset($params['data_fi']) ? $params['data_fi'] : null;
        $sort_dir = isset($params['sort_dir']) ? $params['sort_dir'] : null;
        $sort_col = isset($params['sort_col']) ? $params['sort_col'] : null;

        $ts = TrainingSession::select('training_sessions.*')
            ->where('training_sessions.box_id','=',$box_id)
            ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
            ->leftJoin('user_training_sessions','user_training_sessions.training_session_id','=','training_sessions.id')
            ->where('user_training_sessions.user_id','=',$id_user)
            ->orderBy('training_sessions.start_time')
            ->get();

        $availableToJoin = $user->availableWorkoutSessionsId();
        $availableToLeave = $user->availableWorkoutSessionsToLeaveId();

        foreach ($ts as $t) {
            TrainingSessionRepository::transformWithStatus($user,$t,$availableToJoin,$availableToLeave);
        }

        if ($sort_col) {
            $ts = collect($ts)->sortBy($sort_col)->toArray();
        }
        else {
            $ts = collect($ts)->sortBy("date_start")->toArray();
        }

        if ($sort_dir == "desc") $ts = array_reverse($ts);

        return array_merge([],$ts);
    }

    public static function getTrainingSessions($id_user, $params) {
        $user = UserRepository::find($id_user);
        $box_id = $params['box_id'];
        $data = $params['data'];

        $ts = TrainingSession::select('training_sessions.*')
            ->where('training_sessions.box_id','=',$box_id)
            ->where('training_sessions.day','=',$data)
            ->orderBy('training_sessions.start_time')
            ->get();

        $availableToJoin = $user->availableWorkoutSessionsId();
        $availableToLeave = $user->availableWorkoutSessionsToLeaveId();
        foreach ($ts as $t) {
            TrainingSessionRepository::transformWithStatus($user,$t,$availableToJoin,$availableToLeave);
        }

        return $ts;
    }

    public static function getWorkout($id_user,$params) {
        $user = UserRepository::find($id_user);
        $company_id = $params['company_id'];
        $box_id = ($params['box_id']) ? $params['box_id'] : null;
        $boxes_id = ($box_id) ? [$box_id] : [];
        $workout_id = $params['workout_id'];

        if ($user->isCompanyAdmin($company_id) || $user->isSuperAdmin()) {
            if (!$box_id) {
                $boxes = CompanyRepository::find($company_id)->boxes;
                $boxes_id = [];
                foreach ($boxes as $b) {
                    array_push($boxes_id, $b->id);
                }
            }
        }

        $workout = TrainingWorkout::find($workout_id);

        $usos = DB::table('training_workouts')
            ->select(DB::raw('MONTH(training_sessions.day) month, YEAR(training_sessions.day) year, TRUNCATE(COUNT(user_training_sessions.id),2) as value'))
            ->where('training_workouts.id','=',$workout_id)
            ->leftJoin('training_sessions','training_sessions.training_workout_id','=','training_workouts.id')
            ->leftJoin('user_training_sessions','user_training_sessions.training_session_id','=','training_sessions.id')
            ->orderBy('year')
            ->orderBy('month')
            ->groupBy('year','month')
            ->get();

        if (count($usos)==1) {
            if($usos[0]->month==null || $usos[0]->year==null) $usos = null;
            else {
                $usos = DB::table('training_workouts')
                    ->select(DB::raw('training_sessions.day as day, TRUNCATE(COUNT(user_training_sessions.id),2) as value'))
                    ->where('training_workouts.id', '=', $workout_id)
                    ->leftJoin('training_sessions', 'training_sessions.training_workout_id', '=', 'training_workouts.id')
                    ->leftJoin('user_training_sessions', 'user_training_sessions.training_session_id', '=', 'training_sessions.id')
                    ->orderBy('day')
                    ->groupBy('day')
                    ->get();

                $usos = self::normalizeMonthLineal($usos->toArray());
            }
        } else if (count($usos)>1) {
            $usos = self::transformToDateGraph($usos,Carbon::create($usos[0]->year,$usos[0]->month,'01')->format('Y-m-d'),Carbon::create($usos[count($usos)-1]->year,$usos[count($usos)-1]->month,'01')->format('Y-m-d'));
        }

        $benchmarks = UserBenchmark::select('user_benchmarks.*')
                        ->where('training_workout_id','=',$workout_id)
                        ->with('user')
                        ->get()
                        ->sortBy('user_benchmarks.score')
                        ->take(10);

        $t = TrainingWorkoutSection::select('type')->groupBy('type')->get();


        $types = [];
        foreach ($t as $typ) {
            array_push($types, $typ['type']);
        }

        return [
            "workout" => $workout,
            "chart" => $usos ? array(["name" => "Usos", "series" => $usos]) : null,
            "benchmarks" => $benchmarks,
            "types" => $types
        ];
    }

    public static function getWorkouts($id_user,$params) {
        $user = UserRepository::find($id_user);
        $company_id = $params['company_id'];
        $box_id = ($params['box_id']) ? $params['box_id'] : null;
        $boxes_id = ($box_id) ? [$box_id] : [];
        $sort_dir = $params['sort_dir'];
        $sort_col = $params['sort_col'];
        $name_filter = $params['name_filter'];
        $category_filter = $params['category_filter'];
        $box_filter = $params['box_filter'];

        if ($user->isCompanyAdmin($company_id) || $user->isSuperAdmin()) {
            if (!$box_id) {
                $boxes = CompanyRepository::find($company_id)->boxes;
                $boxes_id = [];
                foreach ($boxes as $b) {
                    array_push($boxes_id, $b->id);
                }
            }
        }


        $workouts = TrainingWorkout::select('training_workouts.*',DB::raw('COALESCE(count(training_sessions.id),0) uses'))
            ->where(function($q) use ($boxes_id,$box_filter) {
                if ($box_filter) {
                    $q->whereIn('training_workouts.box_id', $boxes_id);
                } else {
                    $q->whereIn('training_workouts.box_id', $boxes_id)
                        ->orWhereNull('training_workouts.box_id');
                }
        })->leftJoin('training_sessions','training_sessions.training_workout_id','=','training_workouts.id')
            ->where(function($n) use ($name_filter) {
                $n->where('training_workouts.name','LIKE','%'.$name_filter.'%')
                ->orWhere('training_workouts.name','LIKE',$name_filter.'%')
                ->orWhere('training_workouts.name','LIKE','%'.$name_filter);
            })
            ->where(function($c) use ($category_filter) {
                $c->where('training_workouts.category','LIKE','%'.$category_filter.'%')
                ->orWhere('training_workouts.category','LIKE',$category_filter.'%')
                ->orWhere('training_workouts.category','LIKE','%'.$category_filter);
            })
            ->groupBy('training_workouts.id')
            ->get();


        if ($sort_col) $workouts = collect($workouts)->sortBy($sort_col)->toArray();
        if ($sort_dir == "desc") $workouts = array_reverse($workouts);

        return array_merge($workouts,[]);
    }

    public static function getAssists($id_user,$params) {
        $user = UserRepository::find($id_user);
        $company_id = $params['company_id'];
        $box_id = ($params['box_id']) ? $params['box_id'] : null;
        $boxes_id = ($box_id) ? [$box_id] : [];
        $data_ini = $params['data_ini'];
        $data_fi = $params['data_fi'];

        if ($user->isCompanyAdmin($company_id) || $user->isSuperAdmin()) {
            if (!$box_id) {
                $boxes = CompanyRepository::find($company_id)->boxes;
                $boxes_id = [];
                foreach ($boxes as $b) {
                    array_push($boxes_id, $b->id);
                }
            }
        }

        $assists = DB::table('training_sessions')
            ->select(DB::raw('MONTH(training_sessions.day) month, YEAR(training_sessions.day) year, COUNT(training_sessions.id) as value'))
            ->whereIn('training_sessions.box_id',$boxes_id)
            ->leftJoin('boxes','boxes.id','=','training_sessions.box_id')
            ->leftJoin('user_training_sessions','user_training_sessions.training_session_id','=','training_sessions.id')
            ->where('user_training_sessions.user_id','=',$params['user_id'])
            ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
            ->orderBy('year')
            ->orderBy('month')
            ->groupBy('year','month')
            ->get();

        return (count($assists)>0) ? [ "chart" => array(["name" => "Clases impartidas", "series" => self::transformToDateGraph($assists, $data_ini, $data_fi)])] : null;
    }

    public static function getBillingInfo($id_user,$params) {
        $user = UserRepository::find($id_user);
        $company_id = $params['company_id'];
        $box_id = ($params['box_id']) ? $params['box_id'] : null;
        $boxes_id = ($box_id) ? [$box_id] : [];
        $data_ini = $params['data_ini'];
        $data_fi = $params['data_fi'];

        if ($user->isCompanyAdmin($company_id) || $user->isSuperAdmin()) {
            if (!$box_id) {
                $boxes = CompanyRepository::find($company_id)->boxes;
                $boxes_id = [];
                foreach ($boxes as $b) {
                    array_push($boxes_id, $b->id);
                }
            }
        }

        $ingresos = self::getIngresos($data_ini, $data_fi, $boxes_id);
        $toolTips = [
            "ingresos" => "Conoce tus ingresos"
        ];

        return [
            "ingresos" => $ingresos,
            "tool_tips" => $toolTips
        ];
    }

    public static function getBoxOrdersInfo($id_user,$params) {
        $user = UserRepository::find($id_user);
        $company_id = $params['company_id'];
        $box_id = ($params['box_id']) ? $params['box_id'] : null;
        $boxes_id = ($box_id) ? [$box_id] : [];
        $data_ini = $params['data_ini'];
        $data_fi = $params['data_fi'];
        $coach = ($box_id) ? (!$user->isBoxAdmin($box_id) && $user->isCoach($box_id)) : false;
        $sort_dir = $params['sort_dir'];
        $sort_col = $params['sort_col'];
        $paid_filter = $params['paid_filter'];

        if ($user->isCompanyAdmin($company_id) || $user->isSuperAdmin()) {
            if (!$box_id) {
                $boxes = CompanyRepository::find($company_id)->boxes;
                $boxes_id = [];
                foreach ($boxes as $b) {
                    array_push($boxes_id, $b->id);
                }
            }
        }

        $box_orders = ($coach) ? null : self::getBoxOrders($data_ini, $data_fi, $boxes_id, $paid_filter, $sort_dir, $sort_col);

        return $box_orders;
    }

    public static function getBoxOrders($data_ini, $data_fi, $boxes_id, $paid_filter, $sort_dir = null, $sort_col = null) {
        $o = [];
        $dataIni = Carbon::createFromDate($data_ini);
        $dataFi = Carbon::createFromDate($data_fi);
        foreach ($boxes_id as $bid) {
            foreach (BoxRepository::find($bid)->boxOrders as $box_order) {
                if(($box_order->created_at)->between($dataIni,$dataFi)) {
                    if ($paid_filter==1 && $box_order->invoice_id==null) {
                        array_push($o,$box_order);
                    } else if ($paid_filter==2 && $box_order->invoice_id!=null) {
                        array_push($o,$box_order);
                    } else if (!$paid_filter) array_push($o,$box_order);
                }
            }
        }
        if ($sort_col) $o = collect($o)->sortBy($sort_col)->toArray();
        if ($sort_dir == "desc") $o = array_reverse($o);

        $o = array_merge($o,[]);

        return $o;
    }

    public static function getDashboardInfo($id_user,$params) {
        $user = UserRepository::find($id_user);
        $company_id = $params['company_id'];
        $box_id = ($params['box_id']) ? $params['box_id'] : null;
        $boxes_id = ($box_id) ? [$box_id] : [];
        $data_ini = $params['data_ini'];
        $data_fi = $params['data_fi'];
        $coach = ($box_id) ? (!$user->isBoxAdmin($box_id) && $user->isCoach($box_id)) : false;
        $subscriptions_ending = $box_id ? BoxRepository::userSubscriptions($box_id, ["subscription_status" => 3, "role_group" => 1, "renew" => 0]) : CompanyRepository::userSubscriptions($company_id, ["subscription_status" => 3, "role_group" => 1, "renew" => 0]);
        $subscriptions_prorroga = $box_id ? BoxRepository::userSubscriptions($box_id, ["subscription_status" => 4, "role_group" => 1]) : CompanyRepository::userSubscriptions($company_id, ["subscription_status" => 4, "role_group" => 1]);

        if ($user->isCompanyAdmin($company_id) || $user->isSuperAdmin()) {
            if (!$box_id) {
                $boxes = CompanyRepository::find($company_id)->boxes;
                $boxes_id = [];
                foreach ($boxes as $b) {
                    array_push($boxes_id, $b->id);
                }
            }
        }

        $ingresos = ($coach) ? null : self::getIngresos($data_ini, $data_fi, $boxes_id);
        $actividad_usuarios = self::getActividadUsuarios($data_ini, $data_fi, $boxes_id);
        $actividad_box = self::getActividadBox($data_ini, $data_fi, $boxes_id);
        $consumo_creditos = ($coach) ? null : self::getConsumoCreditos($data_ini, $data_fi, $boxes_id);
        $maxMinUsers = ($coach) ? null : self::getMaxMinUsers($data_ini, $data_fi, $boxes_id);
        $clases_impartidas = self::getClasesImpartidas($data_ini, $data_fi, $boxes_id);
        $subscripcions_tarifa = ($coach) ? null : self::getSubscripcionesTarifa($data_ini, $data_fi, $boxes_id);
        $ocupacio_box = self::getOcupacioBox($data_ini, $data_fi, $boxes_id);
        $toolTips = [
            "ingresos" => "Conoce tus ingresos",
            "actividad_box" => "Aquí podarás consultar el numero de usuarios que han visitado el box",
            "consumo_creditos" => "De los creditos disponibles en cada tarifa, consulta que porcentage se está usando realmente",
            "clases_impartidas" => "Conoce las clases impartidas",
            "subscripciones_tarifa" => "Cuantos usuarios hay en cada tarifa",
            "ocupacio_box" => "El aforo es un problema? Consulta cuanto se llenan las clases",
            "subscriptions_ending" => "Atletas a los que les queda menos de 7 días para que les caduque la suscripción (no automática)",
            "subscriptions_prorroga" => "Atletas que tienen la suscripción en período de gracia y urge que la renueven"
        ];

        return [
            "ingresos" => $ingresos,
            "actividad_usuarios" => $actividad_usuarios,
            "actividad_box" => $actividad_box,
            "consumo_creditos" => $consumo_creditos,
            "max_min_users" => $maxMinUsers,
            "clases_impartidas" => $clases_impartidas,
            "subscripciones_tarifa" => $subscripcions_tarifa,
            "ocupacio_box" => $ocupacio_box,
            "tool_tips" => $toolTips,
            "subscriptions_ending" => $subscriptions_ending,
            "subscriptions_prorroga" => $subscriptions_prorroga
        ];
    }

    public static function getIngresos($data_ini, $data_fi, $boxes_id) {
        try {
            $res = DB::table('box_orders')
                ->select(DB::raw('MONTH(invoices.emitted_date) month, YEAR(invoices.emitted_date) year, TRUNCATE(SUM(invoices.total),2) as value'))
                ->whereIn('box_orders.box_id',$boxes_id)
                ->whereNotNull('invoice_id')
                ->leftJoin('invoices','invoices.id','=','box_orders.invoice_id')
                ->whereBetween('invoices.emitted_date', [date($data_ini), date($data_fi)])
                ->orderBy('year')
                ->orderBy('month')
                ->groupBy('year','month')
                ->get();

            if (count($res)==1) {
                $r = DB::table('box_orders')
                    ->select(DB::raw('invoices.emitted_date day, TRUNCATE(SUM(invoices.total),2) as value'))
                    ->whereIn('box_orders.box_id',$boxes_id)
                    ->whereNotNull('invoice_id')
                    ->leftJoin('invoices','invoices.id','=','box_orders.invoice_id')
                    ->whereBetween('invoices.emitted_date', [date($data_ini), date($data_fi)])
                    ->orderBy('day')
                    ->groupBy('day')
                    ->get();
                $res = self::normalizeMonthLineal($r->toArray());
            } else $res = self::transformToDateGraph($res,$data_ini,$data_fi);

            $total = DB::table('box_orders')
                ->select(DB::raw('COALESCE(TRUNCATE(SUM(invoices.total),2),0) as total, COALESCE(TRUNCATE(SUM(box_orders.subtotal),2),0) as subtotal, COALESCE(TRUNCATE(SUM(box_orders.vat),2),0) as vat, \'€\' as units'))
                ->whereIn('box_orders.box_id',$boxes_id)
                ->whereNotNull('invoice_id')
                ->leftJoin('invoices','invoices.id','=','box_orders.invoice_id')
                ->whereBetween('invoices.emitted_date', [date($data_ini), date($data_fi)])
                ->first();

            [$data_ini_2,$data_fi_2] = self::getLastPeriod($data_ini,$data_fi);

            $total_anterior = DB::table('box_orders')
                ->select(DB::raw('COALESCE(SUM(invoices.total),0) as total, COALESCE(TRUNCATE(SUM(box_orders.subtotal),2),0) as subtotal, COALESCE(TRUNCATE(SUM(box_orders.vat),2),0) as vat, \'€\' as units'))
                ->whereIn('box_orders.box_id',$boxes_id)
                ->whereNotNull('invoice_id')
                ->leftJoin('invoices','invoices.id','=','box_orders.invoice_id')
                ->whereBetween('invoices.emitted_date', [date($data_ini_2), date($data_fi_2)])
                ->first();

            $evolution = self::getEvolution($total->total, $total_anterior->total);
            $evolution_subtotal = self::getEvolution($total->subtotal, $total_anterior->subtotal);
            $evolution_vat = self::getEvolution($total->vat, $total_anterior->vat);

            return array("chart" => array(["name" => "Ingresos", "series" => $res]),
                "total" => $total,
                "total_anterior" => $total_anterior,
                "evolution" => $evolution,
                "evolution_subtotal" => $evolution_subtotal,
                "evolution_vat" => $evolution_vat
            );
        } catch (\Exception $e) {
            return $e->getMessage();
        }

    }

    public static function getActividadUsuarios($data_ini, $data_fi, $boxes_id) {
        try {
            $subscriptions = self::getSubscriptions($boxes_id);
            $new = 0;
            $old = 0;

            $dataIni = Carbon::createFromDate($data_ini);
            $dataFi = Carbon::createFromDate($data_fi);
            foreach ($subscriptions as $s) {
                if (Carbon::createFromDate($s->start)->between($dataIni,$dataFi) &&
                    (($s->end=='INFINITE') || Carbon::createFromDate($s->end)<($dataFi))) {
                    $new += 1;
                } // Empieza dentro del periodo y acaba despues (o no acaba)
                else if ((($s->end!='INFINITE') && Carbon::createFromDate($s->end)->between($dataIni,$dataFi)) &&
                    Carbon::createFromDate($s->start)<($dataIni)) {
                    $old += 1;
                } // Acaba en el periodo y ha empezado antes
            }

            return array(
                "new" => array("number" => $new, "evolution" => number_format((float)(($new==0) ? 0 : ($new/count($subscriptions))*100), 2, '.', '')),
                "old" => array("number" => $old, "evolution" => number_format((float)(($old==0) ? 0 : ($old/count($subscriptions))*100), 2, '.', ''))
            );
        } catch (\Exception $e) {
            return null;
        }
    }

    public static function getActividadBox($data_ini, $data_fi, $boxes_id) {
        try {
            return DB::table('training_sessions')
                ->select('boxes.name as name',DB::raw('COUNT(user_training_sessions.user_id) as value'))
                ->whereIn('training_sessions.box_id',$boxes_id)
                ->rightJoin('user_training_sessions', 'user_training_sessions.training_session_id','=','training_sessions.id')
                ->leftJoin('boxes','boxes.id','=','training_sessions.box_id')
                ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
                ->groupBy('boxes.id')
                ->get();
        } catch (\Exception $e) {
            return null;
        }
    }

    public static function getConsumoCreditos($data_ini, $data_fi, $boxes_id) {
        try {
            $subscriptions_in_week = DB::table('user_subscriptions')
                ->select('user_subscriptions.user_id','user_subscriptions.pricing_id','pricings.name as pricing_name','boxes.name as box_name',DB::raw('COALESCE(WEEK(user_subscriptions.start_date),0) week_start,COALESCE(WEEK(user_subscriptions.end_date),0) week_end, (pricings.wod_counter + pricings.open_counter + pricings.total_counter) as total'))
                ->leftJoin('pricings','pricings.id','=','user_subscriptions.pricing_id')
                ->leftJoin('pricing_box','pricing_box.pricing_id','=','pricings.id')
                ->leftJoin('boxes','boxes.id','=','pricing_box.box_id')
                ->whereIn('pricing_box.box_id',$boxes_id)
                ->get();

            $credits_consumits = DB::table('training_sessions')
                ->select('user_training_sessions.user_id', DB::raw('COUNT(user_training_sessions.user_id) credits, WEEK(training_sessions.day) week'))
                ->whereIn('training_sessions.box_id',$boxes_id)
                ->rightJoin('user_training_sessions','user_training_sessions.training_session_id','=','training_sessions.id')
                ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
                ->groupBy('training_sessions.id','user_training_sessions.user_id','week')
                ->orderBy('week')
                ->get();

            $p = [];
            foreach ($subscriptions_in_week as $pr) {
                $p[$pr->pricing_id] = array("counter" => 0, "total" => $pr->total, "pricing_name" => $pr->pricing_name, "pricing_id" => $pr->pricing_id);
            }


            $size = count($subscriptions_in_week);
            foreach ($credits_consumits as $cc) {
                $trobat = false;
                $i = 0;
                while( !$trobat && $i<$size) {
                    if ($cc->user_id==$subscriptions_in_week[$i]->user_id &&
                        (($cc->week>$subscriptions_in_week[$i]->week_start && $cc->week<$subscriptions_in_week[$i]->week_end) ||
                            $cc->week==$subscriptions_in_week[$i]->week_start || $cc->week==$subscriptions_in_week[$i]->week_end)) {
                        $trobat = true;
                        $p[$subscriptions_in_week[$i]->pricing_id]['counter'] += 1;
                    }
                    $i++;
                }
            }

            $res = [];
            foreach ($p as $pr) {
                if($pr['total'] && !is_null($pr['counter'])){
                    $value = ($pr['counter']/$pr['total'])*100;
                    array_push($res, array("name" => $pr['pricing_name'], "value" => (int)$value));
                }
            }

            return $res;
        } catch (\Exception $e) {
            return null;
        }
    }

    public static function getMaxMinUsers($data_ini, $data_fi, $boxes_id) {
         try {
             $users_on_session = DB::table('users')
                ->select('user_training_sessions.user_id','users.url_profile_photo as photo',DB::raw('CONCAT(users.name, \' \', users.surnames) as user,COUNT(user_training_sessions.id) as training_sessions'))
                ->leftJoin('user_training_sessions','user_training_sessions.user_id','=','users.id')
                ->leftJoin('training_sessions','training_sessions.id','=','user_training_sessions.training_session_id')
                ->whereIn('training_sessions.box_id',$boxes_id)
                ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
                ->groupBy('user_training_sessions.user_id')
                ->orderBy('training_sessions','DESC')
                ->limit(3)
                ->get();

             $us = [];
             $us_id = [];
             foreach ($users_on_session as $u) {
                 array_push($us,$u);
                 array_push($us_id,$u->user_id);
             }
             if (count($us)<6) {
                 $others = DB::table('user_subscriptions')
                     ->select('users.id','users.url_profile_photo as photo',DB::raw('CONCAT(users.name, \' \', users.surnames) as user'))
                     ->leftJoin('users','users.id','=','user_subscriptions.user_id')
                     ->leftJoin('pricings','pricings.id','=','user_subscriptions.pricing_id')
                     ->leftJoin('pricing_box','pricing_box.pricing_id','=','user_subscriptions.pricing_id')
                     ->whereIn('pricing_box.box_id',$boxes_id)
                     ->whereNotIn('users.id',$us_id)
                     ->groupBy('user_subscriptions.user_id')
                     ->inRandomOrder()
                     ->limit(6-count($us))
                     ->get();

                 foreach ($others as $u) {
                     array_push($us,$u);
                 }
             }
             return $us;
         } catch (\Exception $e) {
             return null;
         }
    }

    public static function getClasesImpartidas($data_ini, $data_fi, $boxes_id) {
        try {
            $clases = DB::table('training_sessions')
                ->select(DB::raw('MONTH(training_sessions.day) month, YEAR(training_sessions.day) year, COUNT(training_sessions.id) as value'))
                ->whereIn('training_sessions.box_id',$boxes_id)
                ->leftJoin('boxes','boxes.id','=','training_sessions.box_id')
                ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
                ->orderBy('year')
                ->orderBy('month')
                ->groupBy('year','month')
                ->get();
            if (count($clases)==1) {
                $clases = DB::table('training_sessions')
                    ->select(DB::raw('training_sessions.day day, COUNT(training_sessions.id) as value'))
                    ->whereIn('training_sessions.box_id',$boxes_id)
                    ->leftJoin('boxes','boxes.id','=','training_sessions.box_id')
                    ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
                    ->orderBy('day')
                    ->groupBy('day')
                    ->get();

                $c = self::normalizeMonthLineal($clases->toArray());
            } else $c = self::transformToDateGraph($clases, $data_ini, $data_fi);
            return (count($c)>0) ? array(["name" => "Clases impartidas", "series" => $c]) : null;
        } catch (\Exception $e) {
            return $e->getMessage();
        }
    }

    public static function getSubscripcionesTarifa($data_ini, $data_fi, $boxes_id) {
        try {
            return DB::table('user_subscriptions')
                ->select(DB::raw('pricings.name as name, COUNT(user_subscriptions.user_id) as value'))
                ->leftJoin('pricings','pricings.id','=','user_subscriptions.pricing_id')
                ->leftJoin('pricing_box','pricing_box.pricing_id','=','pricings.id')
                ->leftJoin('boxes','boxes.id','=','pricing_box.box_id')
                ->whereIn('pricing_box.box_id',$boxes_id)
                ->where(function ($query) use ($data_ini,$data_fi) {
                    $query->whereBetween('user_subscriptions.start_date', [date($data_ini), date($data_fi)])
                        ->orWhereBetween('user_subscriptions.end_date', [date($data_ini), date($data_fi)])
                        ->orWhereNull('user_subscriptions.end_date');
                })
                ->groupBy('pricings.id')
                ->get();
        } catch (\Exception $e) {
            return null;
        }
    }

    public static function getOcupacioBox($data_ini, $data_fi, $boxes_id) {
        try {
            return DB::table('user_training_sessions')
                ->select('boxes.name as name',DB::raw('ROUND((COUNT(user_training_sessions.user_id)/SUM(training_sessions.capacity))*100,2) as value'))
                ->leftJoin('training_sessions','training_sessions.id','=','user_training_sessions.training_session_id')
                ->leftJoin('boxes','boxes.id','=','training_sessions.box_id')
                ->whereIn('training_sessions.box_id',$boxes_id)
                ->whereBetween('training_sessions.day', [date($data_ini), date($data_fi)])
                ->groupBy('boxes.id')
                ->get();
        } catch (\Exception $e) {
            return null;
        }
    }

    public static function transformToDateGraph($queryResult, $data_ini, $data_fi){

        $start_date = Carbon::createFromFormat('Y-m-d',$data_ini)->toDateString();
        $end_date = Carbon::createFromFormat('Y-m-d',$data_fi)->subMonth()->toDateString();

        $period = \Carbon\CarbonPeriod::create($start_date, '1 month', $end_date);
        $dates = $period->toArray();

        $res = [];
        $months = ["Ene", "Feb", "Mar", "Abr", "May", "Jun", "Jul", "Ago", "Sep", "Oct", "Nov", "Dic"];

        foreach ($dates as $d) {
            $month = $d->format('m');
            $year = $d->format('Y');

            $i = 0;
            $val = false;
            while($i<count($queryResult) && !$val) {
                if ($month == $queryResult[$i]->month && $year == $queryResult[$i]->year) {
                    $val = $queryResult[$i]->value;
                }
                $i++;
            }
            array_push($res,array(
                "name" => ($months[$month-1] . " " . $year),
                "value" => $val ? $val : 0
            ));
        }

        return $res;
    }

    public static function getSubscriptions($boxes_id) {
        return DB::table('user_subscriptions')
            ->select('user_subscriptions.user_id',DB::raw('MIN(user_subscriptions.start_date) as start, MAX(COALESCE(user_subscriptions.end_date,\'INFINITE\')) as end'))
            ->leftJoin('pricings','pricings.id','=','user_subscriptions.pricing_id')
            ->leftJoin('pricing_box','pricing_box.pricing_id','=','pricings.id')
            ->leftJoin('boxes','boxes.id','=','pricing_box.box_id')
            ->whereIn('pricing_box.box_id',$boxes_id)
            ->groupBy('user_subscriptions.user_id')
            ->get();
    }

    public static function getLastPeriod($data_ini, $data_fi) {
        $dataIni = Carbon::createFromFormat('Y-m-d', $data_ini);
        $dataFi = Carbon::createFromFormat('Y-m-d', $data_fi);

        $diff = $dataIni->diffInDays($dataFi);

        $data_fi = $data_ini;
        $data_ini = $dataIni->subDays($diff)->toDateString();

        return [$data_ini,$data_fi];
    }

    public static function normalizeMonthLineal($n) {
        $start_date = Carbon::createFromFormat('Y-m-d',$n[0]->day)->firstOfMonth()->toDateString();
        $end_date = Carbon::createFromFormat('Y-m-d',$n[count($n)-1]->day)->endOfMonth()->toDateString();

        $period = \Carbon\CarbonPeriod::create($start_date,$end_date);
        $dates = $period->toArray();

        $res = [];
        foreach ($dates as $d) {
            $date = $d->format('Y-m-d');
            $i = 0;
            $key = false;
            while($i<count($n) && !$key) {
                if ($date == Carbon::createFromFormat('Y-m-d',$n[$i]->day)->format('Y-m-d')) {
                    $key = $i;
                }
                $i++;
            }
            array_push($res,array("name" => $date, "value" => ($key ? $n[$key]->value : 0)));
        }

        return $res;
    }

    public static function getEvolution($now,$old) {
        $evolution = 0;
        if ($old==0.0 && $now!=0.0) {
            $evolution = 100;
        } else if ($old!=0.0 && $now==0.0) {
            $evolution = -100;
        } else if ($old==0.0 && $now==0.0){
            $evolution = 0;
        }else $evolution = (int)number_format(($now/$old)*100,2,',','');

        return $evolution;
    }

    public static function getTotal($data) {
        $total = 0;
        foreach ($data['series'] as $item) {
            $total += $item['value'];
        }

        return $total . ' €';
    }

}
