<?php
require_once "./Controller/Database.php";
require_once "./Controller/Setting.php";

class Dashboard extends BD {

    private $PARM;
    function __construct() {
        $this->PARM = new Setting();
    }

    public function GET() {
        $this->Permission();
        $query = $this->consult("SELECT fecha FROM vorganizaciones ORDER BY fecha LIMIT 1");
        $query->execute();
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT COALESCE(COUNT(*),0) AS total FROM reporte_instancias");
        $query->execute();
        $organizaciones = $query->fetchAll(PDO::FETCH_ASSOC)[0]['total'];
        $query = $this->consult("SELECT COALESCE(COUNT(*),0) AS total FROM reporte_unidades");
        $query->execute();
        $unidades = $query->fetchAll(PDO::FETCH_ASSOC)[0]['total'];
        $query = $this->consult("SELECT COALESCE(COUNT(*),0) AS total FROM reporte_personal");
        $query->execute();
        $personal = $query->fetchAll(PDO::FETCH_ASSOC)[0]['total'];
        $query = $this->consult("SELECT estado, COUNT(*) FROM organizaciones GROUP BY estado ORDER BY estado");
        $query->execute();
        $mapa = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT sexo, COUNT(*) FROM reporte_personal GROUP BY sexo");
        $query->execute();
        $sexo = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT sexo,etario, COUNT(*) FROM reporte_personal  GROUP BY sexo,etario");
        $query->execute();
        $etario = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT instancia FROM reporte_instancias");
        $query->execute();
        $instancias = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT nombre FROM reporte_unidades");
        $query->execute();
        $unidadeslist = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT id_nivel_estudio, COUNT(*) FROM reporte_personal  GROUP BY id_nivel_estudio");
        $query->execute();
        $nivelacademico = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT sector_id, COUNT(*) FROM reporte_personal  GROUP BY sector_id");
        $query->execute();
        $sector = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT id_area, COUNT(*) FROM reporte_personal  GROUP BY id_area");
        $query->execute();
        $area = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT sector_id, COUNT(*) FROM reporte_instancias  GROUP BY sector_id");
        $query->execute();
        $sectorinst = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT area, COUNT(*) FROM reporte_unidades  GROUP BY area");
        $query->execute();
        $ocde = $query->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            $fecha = $result[0]['fecha'];
        } else {
            $fecha = date("Y-m-d");
        }
        $this->data = array(
            'fecha_ini' => $fecha,    
            'fecha_fin' => date("Y-m-d"),
            'organizaciones' => $organizaciones,    
            'unidades' => $unidades,
            'personal' => $personal,        
            'mapa' => $mapa,     
            'sexo' => $sexo,     
            'etario' => $etario,     
            'instancias' => $instancias,     
            'unidadeslist' => $unidadeslist,    
            'nivelacademico' => $nivelacademico,    
            'sector' => $sector,  
            'area' => $area, 
            'sectorinst' => $sectorinst, 
            'ocde' => $ocde, 
        );
        $this->httpHeaders = 200;
    }

    public function Buscar() {
        $this->Permission();
        $query = $this->consult("SELECT fecha FROM vorganizaciones ORDER BY fecha LIMIT 1");
        $query->execute();
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT COALESCE(COUNT(*),0) AS total FROM organizaciones");
        $query->execute();
        $organizaciones = $query->fetchAll(PDO::FETCH_ASSOC)[0]['total'];
        $query = $this->consult("SELECT COALESCE(COUNT(*),0) AS total FROM reporte_unidades");
        $query->execute();
        $unidades = $query->fetchAll(PDO::FETCH_ASSOC)[0]['total'];
        $query = $this->consult("SELECT COALESCE(COUNT(*),0) AS total FROM personal");
        $query->execute();
        $personal = $query->fetchAll(PDO::FETCH_ASSOC)[0]['total'];
        $query = $this->consult("SELECT instancia FROM reporte_instancias");
        $query->execute();
        $instancias = $query->fetchAll(PDO::FETCH_ASSOC);
        $query = $this->consult("SELECT nombre FROM reporte_unidades");
        $query->execute();
        $unidadeslist = $query->fetchAll(PDO::FETCH_ASSOC);
        if($result){
            $fecha = $result[0]['fecha'];
        } else {
            $fecha = date("Y-m-d");
        }
        $this->data = array(
            'fecha_ini' => $fecha,    
            'fecha_fin' => date("Y-m-d"),
            'organizaciones' => $organizaciones,    
            'unidades' => $unidades,
            'personal' => $personal,      
            'instancias' => $instancias,     
            'unidadeslist' => $unidadeslist,       
        );
        $this->httpHeaders = 200;
    }


    public function Descarga() {
        $this->Permission();
        $input = $this->input;
        $this->data = $input;
        $tabla = $input->caso;
        $query = $this->consult("SELECT * FROM $tabla WHERE fecha>=? AND fecha<=?");
        $query->execute([$input->fecha_ini,$input->fecha_fin]);
        $this->data = $query->fetchAll(PDO::FETCH_ASSOC);
        $this->httpHeaders = 200;
    }
}