<?php

namespace App\Http\Controllers\Reporte;

use App\Model\Encuesta;
use Illuminate\Database\QueryException;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use App\Model\myObjectGraph;

class EstadisticosController extends Controller
{

    //asistencia
    public function indexAsistencia()
    {
        DB::statement("SET lc_time_names = 'es_EC'");
        $year = date('Y');
        $meses = DB::select(DB::raw("
            SELECT DISTINCT MONTH(ap.fecha) as id,  MONTHNAME(ap.fecha) as nombre
            FROM asistencia_personal ap
            WHERE YEAR(ap.fecha) = $year
            ORDER BY id DESC 
        "));
        return view('pages.reportes.estadisticas.indexasistencia', compact('meses'));
    }

    public function asistenciagraph($mes)
    {
        DB::statement("SET lc_time_names = 'es_EC'");
        $anio = date('Y');

        if ($mes != null) {
            $faltas = DB::select(DB::raw("
            SELECT ap.estado as name, COUNT(ap.id) as count
            FROM asistencia_personal ap
            WHERE ap.estado = 'Falta' AND MONTH(ap.fecha) = $mes AND  YEAR(ap.fecha) = $anio
            GROUP BY ap.estado
        "));

            $permisos = DB::select(DB::raw("
            SELECT ap.estado as name, COUNT(ap.id) as count
            FROM asistencia_personal ap
            WHERE ap.estado = 'Permiso' AND MONTH(ap.fecha) = $mes AND  YEAR(ap.fecha) = $anio
            GROUP BY ap.estado
        "));

            $tardanzas = DB::select(DB::raw("
            SELECT ap.estado as name, COUNT(ap.id) as count
            FROM asistencia_personal ap
            WHERE ap.estado = 'Tardanza' AND MONTH(ap.fecha) = $mes AND  YEAR(ap.fecha) = $anio
            GROUP BY ap.estado
        "));

            $asistencias = DB::select(DB::raw("
            SELECT ap.estado as name, COUNT(ap.id) as count
            FROM asistencia_personal ap
            WHERE ap.estado = 'Asistio' AND MONTH(ap.fecha) = $mes AND  YEAR(ap.fecha) = $anio
            GROUP BY ap.estado
        "));

            if (count($faltas) > 0) {
                $countfaltas = $faltas[0]->count;
            } else {
                $countfaltas = 0;
            }
            if (count($permisos) > 0) {
                $countpermisos = $permisos[0]->count;
            } else {
                $countpermisos = 0;
            }
            if (count($tardanzas) > 0) {
                $counttardanzas = $tardanzas[0]->count;
            } else {
                $counttardanzas = 0;
            }
            if (count($asistencias) > 0) {
                $countasistencias = $asistencias[0]->count;
            } else {
                $countasistencias = 0;
            }

            $datagrap = [
                ['label' => "Asistencia", 'value' => $countasistencias],
                ['label' => "Tardanza", 'value' => $counttardanzas],
                ['label' => "Permiso", 'value' => $countpermisos],
                ['label' => "Faltas", 'value' => $countfaltas],
            ];
        } else {
            $datagrap = [
                ['label' => "Asistencia", 'value' => 0],
                ['label' => "Tardanza", 'value' => 0],
                ['label' => "Permiso", 'value' => 0],
                ['label' => "Faltas", 'value' => 0],
            ];
        }


        return response()->json($datagrap);
    }

    public function tablatardanza($mes)
    {
        $anio = date('Y');

        $faltas = DB::select(DB::raw("
            SELECT DISTINCT  u.nombres, u.apellido_paterno, u.apellido_materno, COUNT(ap.estado) as count
            FROM asistencia_personal ap
            JOIN usuario u on ap.id_user = u.id
            WHERE ap.estado = 'Tardanza' AND MONTH(ap.fecha) = $mes and YEAR(ap.fecha) =$anio
            GROUP BY  u.nombres, u.apellido_paterno, u.apellido_materno, ap.estado
            ORDER BY count DESC
            LIMIT 10
        "));

        return view('pages.reportes.estadisticas.tablas.tardanzas', compact('faltas'));

    }

    public function tablafalta($mes)
    {
        $anio = date('Y');

        if ($mes != null) {
            $faltas = DB::select(DB::raw("
            SELECT DISTINCT  u.nombres, u.apellido_paterno, u.apellido_materno, COUNT(ap.estado) as count
            FROM asistencia_personal ap
            JOIN usuario u on ap.id_user = u.id
            WHERE ap.estado = 'Falta' AND MONTH(ap.fecha) = $mes and YEAR(ap.fecha) =$anio
            GROUP BY  u.nombres, u.apellido_paterno, u.apellido_materno, ap.estado
            ORDER BY count DESC
            LIMIT 10
        "));
        } else {
            $faltas = [];
        }

        return view('pages.reportes.estadisticas.tablas.faltas', compact('faltas'));

    }

    public function tablapermiso($mes)
    {
        $anio = date('Y');

        if ($mes != null) {
            $faltas = DB::select(DB::raw("
            SELECT DISTINCT  u.nombres, u.apellido_paterno, u.apellido_materno, COUNT(ap.estado) as count
            FROM asistencia_personal ap
            JOIN usuario u on ap.id_user = u.id
            WHERE ap.estado = 'Permiso' AND MONTH(ap.fecha) = $mes and YEAR(ap.fecha) =$anio
            GROUP BY  u.nombres, u.apellido_paterno, u.apellido_materno, ap.estado
            ORDER BY count DESC
            LIMIT 10
        "));
        } else {
            $faltas = [];
        }

        return view('pages.reportes.estadisticas.tablas.permisos', compact('faltas'));

    }

    public function especialidadasistenciagraph($mes)
    {

        $anio = date('Y');

        $array = DB::select(DB::raw("

            SELECT e.nombre as y, IF(falta.cantfalta is not null ,falta.cantfalta,0) as a, IF(tardanza.canttardanza is not null ,tardanza.canttardanza,0) as b
            FROM especialidad e
            LEFT JOIN (SELECT DISTINCT  e.id as EspecialidadID, COUNT(aa.id) as cantfalta
                        FROM asistencia_alumno aa 
                        JOIN alumno_seccion sa on aa.id_alumno_seccion = sa.id
                        JOIN alumno a on sa.id_alumno = a.id
                        JOIN especialidad e on a.id_especialidad = e.id
                        WHERE YEAR(aa.fecha) = $anio and MONTH(aa.fecha)= $mes AND aa.estado= 'Falta'
                        GROUP BY e.nombre
                         ORDER BY e.nombre DESC) as falta on falta.EspecialidadID = e.id
            LEFT JOIN (SELECT DISTINCT  e.id as EspecialidadID, COUNT(aa.id) as canttardanza
                        FROM asistencia_alumno aa 
                        JOIN alumno_seccion sa on aa.id_alumno_seccion = sa.id
                        JOIN alumno a on sa.id_alumno = a.id
                        JOIN especialidad e on a.id_especialidad = e.id
                        WHERE YEAR(aa.fecha) = $anio and MONTH(aa.fecha)= $mes AND aa.estado= 'Tardanza'
                        GROUP BY e.nombre
                        ORDER BY e.nombre DESC)as tardanza on tardanza.EspecialidadID = e.id

        "));

        return response()->json($array);

    }

    public function tablatardanzaAlumnos($mes)
    {

        $anio = date('Y');

        $tardanzaalumnos = DB::select(DB::raw("
            SELECT DISTINCT u.nombres,u.apellido_paterno, u.apellido_materno,  e.nombre as Especialidad, COUNT(aa.id) as count
            FROM asistencia_alumno aa 
            JOIN alumno_seccion sa on aa.id_alumno_seccion = sa.id
            JOIN alumno a on sa.id_alumno = a.id
            JOIN especialidad e on a.id_especialidad = e.id
            join usuario u on a.id_usuario = u.id
            WHERE YEAR(aa.fecha) = $anio and MONTH(aa.fecha)= $mes AND aa.estado= 'Tardanza'
            GROUP BY u.nombres,u.apellido_paterno, u.apellido_materno,  e.nombre
             ORDER BY e.nombre DESC
        "));

        return view('pages.reportes.estadisticas.tablas.tardanzasAlumnos', compact('tardanzaalumnos'));

    }

    public function tablafaltaAlumnos($mes)
    {

        $anio = date('Y');

        $faltaalumnos = DB::select(DB::raw("
            SELECT u.nombres,u.apellido_paterno, u.apellido_materno, faltas.Especialidad, faltas.count
            FROM usuario u
            INNER JOIN (SELECT DISTINCT u.id, u.nombres,u.apellido_paterno, u.apellido_materno,  e.nombre as Especialidad, COUNT(aa.id) as count
                        FROM asistencia_alumno aa 
                        JOIN alumno_seccion sa on aa.id_alumno_seccion = sa.id
                        JOIN alumno a on sa.id_alumno = a.id
                        JOIN especialidad e on a.id_especialidad = e.id
                        join usuario u on a.id_usuario = u.id
                        WHERE YEAR(aa.fecha) = $anio and MONTH(aa.fecha)= $mes AND aa.estado= 'Falta'
                        GROUP BY u.nombres,u.apellido_paterno, u.apellido_materno,  e.nombre
                        ORDER BY e.nombre DESC) as faltas on u.id = faltas.id
            WHERE faltas.count > 15
        "));

        return view('pages.reportes.estadisticas.tablas.faltasAlumnos', compact('faltaalumnos'));

    }

    //fin asistencia

    //situacion academica
    public function indexSituacionAcademica()
    {

        $periodos = DB::select(DB::raw("
            SELECT p.id,p.nombre
            FROM periodo p
            WHERE p.estado = 1 OR p.estado = 2
            ORDER  BY p.estado ASC 
        "));

        return view('pages.reportes.estadisticas.indexsituacionacademica', compact('periodos'));
    }

    public function estudiantesvsgraduados()
    {

        $cantegresados = DB::select(DB::raw("
            SELECT COUNT(a.id) as cant
            FROM alumno a
            INNER  JOIN usuario u on a.id_usuario = u.id
            WHERE a.egresado = 1 AND u.genero = 'Masculino'
        "));

        $cantalumnos = DB::select(DB::raw("
            SELECT COUNT(a.id) as cant
            FROM alumno a
            INNER  JOIN usuario u on a.id_usuario = u.id
            WHERE a.egresado = 1 AND u.genero = 'Masculino'
        "));

        $datagrap = [
            ['label' => 'Egresados', 'value' => $cantegresados[0]->cant],
            ['label' => 'Estudiantes', 'value' => $cantalumnos[0]->cant],

        ];

        return response()->json($datagrap);
    }

    public function estudiantesvsgraduadosF()
    {

        $cantegresados = DB::select(DB::raw("
            SELECT COUNT(a.id) as cant
            FROM alumno a
            INNER  JOIN usuario u on a.id_usuario = u.id
            WHERE a.egresado = 1 AND u.genero = 'Femenino'
        "));

        $cantalumnos = DB::select(DB::raw("
            SELECT COUNT(a.id) as cant
            FROM alumno a
            INNER  JOIN usuario u on a.id_usuario = u.id
            WHERE a.egresado = 1 AND u.genero = 'Femenino'

        "));

        $datagrap = [
            ['label' => 'Egresadas', 'value' => $cantegresados[0]->cant],
            ['label' => 'Estudiantes', 'value' => $cantalumnos[0]->cant],

        ];

        return response()->json($datagrap);
    }

    public function alumnosaprobadosvsdesaprobados($periodoID)
    {

        $alumnosaprobados = DB::select(DB::raw("
        
            SELECT e.nombre as Especialidad, count(pf.alumnoid) as cantaprobados
            FROM especialidad e
            RIGHT join (
                SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final
                FROM alumno_seccion sa
                JOIN notas n on n.id_alumno_seccion = sa.id
                LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso
                LEFT JOIN curso c on c.id = sc.id_curso
                LEFT JOIN alumno a on sa.id_alumno = a.id
                LEFT JOIN especialidad e on a.id_especialidad = e.id
                LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad
                WHERE ep.id_periodo = $periodoID
                GROUP BY  a.id, e.id 
                ORDER BY `especialidadid` ASC
            ) pf on e.id = pf.especialidadid
            where pf.promedio_final > 11
            GROUP BY e.nombre
        
        "));

        $alumnosdesaprobados = DB::select(DB::raw("
        
            SELECT e.nombre as Especialidad, count(pf.alumnoid) as cantdesaprobados
            FROM especialidad e
            RIGHT join (
                SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final
                FROM alumno_seccion sa
                JOIN notas n on n.id_alumno_seccion = sa.id
                LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso
                LEFT JOIN curso c on c.id = sc.id_curso
                LEFT JOIN alumno a on sa.id_alumno = a.id
                LEFT JOIN especialidad e on a.id_especialidad = e.id
                LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad
                WHERE ep.id_periodo = $periodoID
                GROUP BY  a.id, e.id
                ORDER BY `especialidadid` ASC
            ) pf on e.id = pf.especialidadid
            where pf.promedio_final < 11
            GROUP BY e.nombre
        
        "));

        $alumnosarray = array();

        foreach ($alumnosaprobados as $item) {
            foreach ($alumnosdesaprobados as $obj) {
                if ($item->Especialidad == $obj->Especialidad) {

                    $myobj = new myObjectGraph();

                    //a -> aprobados   ------    b-> desaprobados
                    $myobj->y = $item->Especialidad;
                    $myobj->a = $item->cantaprobados;
                    $myobj->b = $obj->cantdesaprobados;

                    $alumnosarray[] = $myobj;
                }
            }
        }

        return response()->json($alumnosarray);

    }

    public function alumnasaprobadasvsdesaprobadas($periodoID)
    {

        $alumnosaprobados = DB::select(DB::raw("
        
            SELECT e.nombre as Especialidad, count(pf.alumnoid) as cantaprobados
            FROM especialidad e
            RIGHT join (
                SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final
                FROM alumno_seccion sa
                JOIN notas n on n.id_alumno_seccion = sa.id
                LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso
                LEFT JOIN curso c on c.id = sc.id_curso
                LEFT JOIN alumno a on sa.id_alumno = a.id
                INNER JOIN usuario u on a.id_usuario = u.id
                LEFT JOIN especialidad e on a.id_especialidad = e.id
                LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad
                WHERE ep.id_periodo = $periodoID AND u.genero = 'Femenino'
                GROUP BY  a.id, e.id 
                ORDER BY `especialidadid` ASC
            ) pf on e.id = pf.especialidadid
            where pf.promedio_final > 11
            GROUP BY e.nombre
        
        "));

        $alumnosdesaprobados = DB::select(DB::raw("
        
            SELECT e.nombre as Especialidad, count(pf.alumnoid) as cantdesaprobados
            FROM especialidad e
            RIGHT join (
                SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final
                FROM alumno_seccion sa
                JOIN notas n on n.id_alumno_seccion = sa.id
                LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso
                LEFT JOIN curso c on c.id = sc.id_curso
                LEFT JOIN alumno a on sa.id_alumno = a.id
                INNER JOIN usuario u on a.id_usuario = u.id
                LEFT JOIN especialidad e on a.id_especialidad = e.id
                LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad
                WHERE ep.id_periodo = $periodoID AND u.genero = 'Femenino'
                GROUP BY  a.id, e.id
                ORDER BY `especialidadid` ASC
            ) pf on e.id = pf.especialidadid
            where pf.promedio_final < 11
            GROUP BY e.nombre
        
        "));

        $alumnosarray = array();

        foreach ($alumnosaprobados as $item) {
            foreach ($alumnosdesaprobados as $obj) {
                if ($item->Especialidad == $obj->Especialidad) {

                    $myobj = new myObjectGraph();

                    //a -> aprobados   ------    b-> desaprobados
                    $myobj->y = $item->Especialidad;
                    $myobj->a = $item->cantaprobados;
                    $myobj->b = $obj->cantdesaprobados;

                    $alumnosarray[] = $myobj;
                }
            }
        }

        return response()->json($alumnosarray);

    }

    public function rendimientoacademico($periodoID)
    {

        $cantaprobados = DB::select(DB::raw("
        
           SELECT count(pf.alumnoid) as cantaprobados 
           FROM especialidad e RIGHT join ( 
              SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final 
              FROM alumno_seccion sa JOIN notas n on n.id_alumno_seccion = sa.id 
              LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso 
              LEFT JOIN curso c on c.id = sc.id_curso 
              LEFT JOIN alumno a on sa.id_alumno = a.id 
              LEFT JOIN especialidad e on a.id_especialidad = e.id 
              LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad 
              WHERE ep.id_periodo = $periodoID 
              GROUP BY a.id, e.id 
              ORDER BY `especialidadid` ASC 
              ) pf on e.id = pf.especialidadid 
           where pf.promedio_final > 11
        
        "));

        $cantdesaprobados = DB::select(DB::raw("
        
           SELECT count(pf.alumnoid) as cantdesaprobados 
           FROM especialidad e RIGHT join ( 
              SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final 
              FROM alumno_seccion sa JOIN notas n on n.id_alumno_seccion = sa.id 
              LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso 
              LEFT JOIN curso c on c.id = sc.id_curso 
              LEFT JOIN alumno a on sa.id_alumno = a.id 
              LEFT JOIN especialidad e on a.id_especialidad = e.id 
              LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad 
              WHERE ep.id_periodo = $periodoID 
              GROUP BY a.id, e.id 
              ORDER BY `especialidadid` ASC 
              ) pf on e.id = pf.especialidadid 
           where pf.promedio_final < 11
        
        "));

        $datagrap = [
            ['label' => 'Aprobados', 'value' => $cantaprobados[0]->cantaprobados],
            ['label' => 'Desaprobados', 'value' => $cantdesaprobados[0]->cantdesaprobados],

        ];

        return response()->json($datagrap);

    }

    public function rendimientoacademicoA($periodoID)
    {

        $cantaprobados = DB::select(DB::raw("
        
           SELECT count(pf.alumnoid) as cantaprobados 
           FROM especialidad e RIGHT join ( 
              SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final 
              FROM alumno_seccion sa JOIN notas n on n.id_alumno_seccion = sa.id 
              LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso 
              LEFT JOIN curso c on c.id = sc.id_curso 
              LEFT JOIN alumno a on sa.id_alumno = a.id 
              INNER JOIN usuario u on a.id_usuario = u.id
              LEFT JOIN especialidad e on a.id_especialidad = e.id 
              LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad 
              WHERE ep.id_periodo = $periodoID AND u.genero = 'Femenino'
              GROUP BY a.id, e.id 
              ORDER BY `especialidadid` ASC 
              ) pf on e.id = pf.especialidadid 
           where pf.promedio_final > 11
        
        "));

        $cantdesaprobados = DB::select(DB::raw("
        
           SELECT count(pf.alumnoid) as cantdesaprobados 
           FROM especialidad e RIGHT join ( 
              SELECT DISTINCT a.id as alumnoid ,e.id as especialidadid,AVG(n.nota) as promedio_final 
              FROM alumno_seccion sa JOIN notas n on n.id_alumno_seccion = sa.id 
              LEFT JOIN seccion_curso sc on sc.id = sa.id_seccion_curso 
              LEFT JOIN curso c on c.id = sc.id_curso 
              LEFT JOIN alumno a on sa.id_alumno = a.id 
              INNER JOIN usuario u on a.id_usuario = u.id
              LEFT JOIN especialidad e on a.id_especialidad = e.id 
              LEFT JOIN especialidad_periodo ep on e.id = ep.id_especialidad 
              WHERE ep.id_periodo = $periodoID AND u.genero = 'Femenino'
              GROUP BY a.id, e.id 
              ORDER BY `especialidadid` ASC 
              ) pf on e.id = pf.especialidadid 
           where pf.promedio_final < 11
        
        "));

        $datagrap = [
            ['label' => 'Aprobadas', 'value' => $cantaprobados[0]->cantaprobados],
            ['label' => 'Desaprobadas', 'value' => $cantdesaprobados[0]->cantdesaprobados],

        ];

        return response()->json($datagrap);

    }
    //fin situacion academica

    //tramites
    public function indextramites()
    {
        return view('pages.reportes.estadisticas.indextramites');
    }

    public function tupasfinalizadosvsiniciados()
    {
        DB::statement("SET lc_time_names = 'es_EC'");

        $tramitesiniciados = DB::select(DB::raw("
        
            SELECT  Monthname(tp.created_at) as mes, count(tp.id) as count
            FROM tramite_procedimiento tp
            INNER JOIN tramite_usuario tu on tp.id = tu.id_tramite_procedimiento
            where tp.id_tramite_tipo = " . config('constants.TRAMITE_TIPO.TUPA') . " AND tu.id_tramite_usuario_estado = " . config('constants.TRAMITE_USUARIO_ESTADO_ID.INICIADO') . " AND Year(tp.created_at) = YEAR(CURDATE())
            GROUP BY tp.created_at
            ORDER BY MONTH(tp.created_at) ASC
        
        "));

        $tramitesfinalizados = DB::select(DB::raw("
            SELECT  Monthname(tp.created_at) as mes, count(tp.id) as count
            FROM tramite_procedimiento tp
            INNER JOIN tramite_usuario tu on tp.id = tu.id_tramite_procedimiento
            where tp.id_tramite_tipo = " . config('constants.TRAMITE_TIPO.TUPA') . " AND tu.id_tramite_usuario_estado = " . config('constants.TRAMITE_USUARIO_ESTADO_ID.FINALIZADO') . " AND Year(tp.created_at) = YEAR(CURDATE())
            GROUP BY tp.created_at
            ORDER BY MONTH(tp.created_at) ASC
        "));

        $tramitesarray = array();

        foreach ($tramitesiniciados as $item) {

            if (count($tramitesfinalizados) > 0) {

                foreach ($tramitesfinalizados as $obj) {

                    $myobj = new myObjectGraph();

                    //a -> iniciados   ------    b-> finalizados
                    $myobj->y = $item->mes;
                    $myobj->a = $item->count;
                    if ($obj->count == null) {
                        $myobj->b = 0;
                    } else {
                        $myobj->b = $obj->count;
                    }

                    $tramitesarray[] = $myobj;
                }
            } else {
                $myobj = new myObjectGraph();

                //a -> iniciados   ------    b-> finalizados
                $myobj->y = $item->mes;
                $myobj->a = $item->count;
                $myobj->b = 0;

                $tramitesarray[] = $myobj;
            }


        }


        return response()->json($tramitesarray);

    }
    //fin tramites

    //Encuestas
    public function indexreporteencuesta()
    {

        $encuestas = Encuesta::all();

        return view('pages.reportes.estadisticas.indexencuestas', compact('encuestas'));

    }

    public function encuestasrespondidasvsnorespondidas()
    {


        $encuestas = DB::select(DB::raw("
        
            SELECT e.nombre, respondidas.cantRespondidas, norespondidas.cantNoRespondidas
            FROM encuesta e
            LEFT JOIN (SELECT e.id, COUNT(eu.id) as cantRespondidas
                       FROM encuesta e
                       LEFT JOIN encuesta_usuario eu on e.id = eu.id_encuesta AND eu.respondida = 1
                       GROUP BY e.id) as respondidas on e.id = respondidas.id
            LEFT JOIN (SELECT e.id, COUNT(eu.id) as cantNoRespondidas
                       FROM encuesta e
                       LEFT JOIN encuesta_usuario eu on e.id = eu.id_encuesta AND eu.respondida = 0
                       GROUP BY e.id) as norespondidas on e.id = norespondidas.id
        
        "));

        $encuestasarray = array();


        foreach ($encuestas as $item) {
            $myobj = new myObjectGraph();

            $myobj->y = $item->nombre;
            $myobj->a = $item->cantRespondidas;
            $myobj->b = $item->cantNoRespondidas;

            $encuestasarray[] = $myobj;

        }

        return response()->json($encuestasarray);

    }

    public function detalleencuesta($encuestaID)
    {

        $encuesta = Encuesta::find($encuestaID);

        $cantRespondidas = DB::select(DB::raw("
                SELECT e.id, COUNT(eu.id) as cantRespondidas
                FROM encuesta e
                LEFT JOIN encuesta_usuario eu on e.id = eu.id_encuesta AND eu.respondida = 1
                WHERE  e.id = $encuestaID
                GROUP BY e.id
            "));

        $usuariosencuestados = DB::select(DB::raw("
                SELECT u.nombres, u.apellido_paterno, u.apellido_materno, eu.id, eu.updated_at, u.id as userid
                FROM encuesta e
                INNER JOIN encuesta_usuario eu on e.id = eu.id_encuesta
                INNER JOIN usuario u on eu.id_usuario = u.id
                WHERE eu.respondida = 1 AND e.id = $encuestaID
         "));

        $usuariosporresponder = DB::select(DB::raw("
                SELECT u.nombres, u.apellido_paterno, u.apellido_materno, eu.id,  u.email
                FROM encuesta e
                INNER JOIN encuesta_usuario eu on e.id = eu.id_encuesta
                INNER JOIN usuario u on eu.id_usuario = u.id
                WHERE eu.respondida != 1 AND  e.id = $encuestaID
         "));

        $preguntas = DB::select(DB::raw("
        
            SELECT p.descripcion as pregunta, r.descripcion as respuesta, COUNT(rue.id) as cantrespuestas
            FROM encuesta e
            INNER JOIN pregunta p on e.id = p.id_encuesta
            INNER JOIN respuesta r on p.id = r.id_pregunta
            LEFT JOIN respuesta_usuario_encuesta rue on r.id = rue.id_respuesta
            WHERE e.id = $encuestaID AND rue.respuesta_texto is null
            GROUP BY  p.descripcion, r.descripcion
        
        "));

        return view('pages.reportes.encuesta.detalleencuesta', compact('encuesta', 'cantRespondidas', 'usuariosencuestados', 'preguntas', 'usuariosporresponder'));

    }

    //fin encuestas

    //Alumno

    public function indexAlumnos()
    {

        $alumnos = DB::select(DB::raw("
            SELECT DISTINCT u.apellido_paterno, u.apellido_materno, u.nombres, dep.nombre as departamento
            FROM alumno a
            INNER JOIN usuario u ON a.id_usuario = u.id
            INNER JOIN ficha_postulante fp ON u.id = fp.id_usuario
            INNER JOIN distrito d ON fp.id_distrito_direccion = d.id
            INNER JOIN provincia p on d.id_provincia = p.id
            INNER JOIN  departamento dep on p.id_departamento = dep.id
            ORDER BY dep.nombre ASC 
        "));

        $procedencia = DB::select(DB::raw("
            SELECT DISTINCT u.apellido_paterno, u.apellido_materno, u.nombres, fp.tipo_educacion as tipocolegio
            FROM alumno a
            INNER JOIN usuario u ON a.id_usuario = u.id
            INNER JOIN ficha_postulante fp ON u.id = fp.id_usuario
        "));

        $periodo = DB::table('periodo')->get();

        $alumnostipoIngreso = DB::select(DB::raw("
            SELECT a.codigo ,u.apellido_paterno ,u.apellido_materno,u.nombres, tp.nombre
            FROM usuario u
            INNER JOIN alumno a on u.id = a.id_usuario
            INNER JOIN ficha_postulante fp on u.id = fp.id_usuario
            INNER JOIN tipo_postulacion tp on fp.id_tipo_postulacion = tp.id
            WHERE tp.id != 1 AND  tp.id != 5 AND  tp.id != 6
        "));

        return view('pages.reportes.estadisticas.indexalumno', compact('alumnos', 'alumnostipoIngreso', 'procedencia', 'periodo'));
    }

    public function cantalumnosxespecialdad($periodoID)
    {

        $especialidadxalumnos = DB::select(DB::raw("
            SELECT e.nombre as y, count(a.id) as a
            FROM  alumno a
            INNER  JOIN  especialidad e on a.id_especialidad = e.id
            INNER  JOIN especialidad_periodo ep on e.id = ep.id_especialidad
            WHERE  ep.id_periodo = $periodoID
            GROUP BY e.nombre
        "));

        return response()->json($especialidadxalumnos);
    }

    public function cantalumnosxgenero()
    {
        $cantgeneros = DB::select(DB::raw("
            SELECT u.genero as label, count(a.id) as value
            FROM alumno a
            INNER  JOIN usuario u on a.id_usuario = u.id
            GROUP BY  u.genero
        "));

        return response()->json($cantgeneros);
    }

    public function cantproyeccionxespecialidad($periodoID)
    {

        $canrproxesp = DB::select(DB::raw("
            SELECT e.nombre as y, count(ps.id) as a
            FROM proyeccion_social ps
            INNER JOIN proyeccion_social_usuario psu on ps.id = psu.id_proyeccion_social
            INNER  JOIN usuario u on psu.id_usuario = u.id
            INNER JOIN  alumno a on u.id = a.id_usuario
            INNER JOIN  especialidad e on a.id_especialidad = e.id
            INNER  JOIN especialidad_periodo ep on e.id = ep.id_especialidad
            WHERE ep.id_periodo = $periodoID 
            GROUP BY e.nombre;
        "));

        return response()->json($canrproxesp);
    }

    public function cantalumnospordepartamento()
    {

        $alumnosxdepartamentos = DB::select(DB::raw("
            SELECT DISTINCT  dep.nombre as y, COUNT(a.id) as a
            FROM alumno a
            INNER JOIN usuario u ON a.id_usuario = u.id
            INNER JOIN ficha_postulante fp ON u.id = fp.id_usuario
            INNER JOIN distrito d ON fp.id_distrito_direccion = d.id
            INNER JOIN provincia p on d.id_provincia = p.id
            INNER JOIN  departamento dep on p.id_departamento = dep.id
            GROUP BY  dep.nombre
            ORDER BY dep.nombre ASC
        "));

        return response()->json($alumnosxdepartamentos);
    }

    public function cantalumnosporcolegioprocedencia()
    {

        $alumnosxcolegioprocedencia = DB::select(DB::raw("
            SELECT DISTINCT  fp.tipo_educacion as y, COUNT(a.id) as a
            FROM alumno a
            INNER JOIN usuario u ON a.id_usuario = u.id
            INNER JOIN ficha_postulante fp ON u.id = fp.id_usuario
            GROUP BY fp.tipo_educacion
            ORDER BY fp.tipo_educacion ASC
        "));

        return response()->json($alumnosxcolegioprocedencia);
    }

    public function cantalumnosportipoingreso()
    {

        $alumnosxtipoingreso = DB::select(DB::raw("
            SELECT tp.nombre as y, COUNT(a.id) as a
            FROM usuario u
            INNER JOIN alumno a on u.id = a.id_usuario
            INNER JOIN ficha_postulante fp on u.id = fp.id_usuario
            INNER JOIN tipo_postulacion tp on fp.id_tipo_postulacion = tp.id
            GROUP BY tp.nombre
            ORDER BY tp.nombre ASC
        "));

        return response()->json($alumnosxtipoingreso);
    }

    //Fin Alumno

    //Titulados
    public function indextitulados()
    {

        $periodo = DB::table('periodo')->get();

        return view('pages.reportes.estadisticas.indextitulados', compact('periodo'));
    }

    public function tituladosporperiodo()
    {

        $titulados = DB::select(DB::raw("
            SELECT p.nombre as y, COUNT(a.id)  as a
            FROM usuario u
            INNER JOIN alumno a on u.id = a.id_usuario
            INNER JOIN periodo p on a.id_periodo_titulado = p.id
            WHERE a.titulado = 1
            GROUP BY p.nombre
        "));

        return response()->json($titulados);

    }

    public function tituladosporpromocion()
    {

        $titulados = DB::select(DB::raw("
            SELECT g.nombre as y, COUNT(a.id)  as a
            FROM usuario u
            INNER JOIN alumno a on u.id = a.id_usuario
            INNER JOIN periodo p on a.id_periodo_titulado = p.id
            INNER JOIN grupo g on a.id_grupo = g.id
            WHERE a.titulado =1 
            GROUP BY g.nombre
        "));

        return response()->json($titulados);

    }
    //Fin Titulados

    //Eventos
    public function indexeventos()
    {
        return view('pages.reportes.estadisticas.indexeventos');
    }

    public function eventosmasasistidos()
    {

        $eventosconmasasistentes = DB::select(DB::raw("
            SELECT e.nombre as y, COUNT(p.id) as a
            FROM evento e 
            INNER JOIN participante p ON e.id = p.id_evento
            WHERE  Year(e.fecha_fin) = YEAR(CURDATE())  
            GROUP BY e.nombre
            ORDER BY e.nombre
            LIMIT 15
        "));

        return response()->json($eventosconmasasistentes);

    }

    //Fin Eventos
}
