<?php

include_once("lib/general/cGeneral.php");
include_once("Model/UserAuth.php");
include_once("Controllers/CentralizadoController.php");
include_once("Controllers/GeorefController.php");
ini_set('display_errors', 1); 
class SaludController extends cGeneral{

    public static function get__client_data($idClient, $idPlataform){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        $sql_header_dsu = "SELECT 
                cc.urlLogo,
                ccp.title,
                ccp.primaryColor,
                ccp.secondaryColor
            FROM modulo_carga_ges.catCliente AS cc
            LEFT JOIN modulo_carga_ges.catClientePlataforma AS ccp ON ccp.idCliente = cc.idCliente AND idPlataforma = ".$idPlataform."
            WHERE cc.idCliente =". $idClient .";"; 

        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row = $fila;
                }
            }

            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }

        return $response;
    }


    public static function get_name_services_salud(){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        $sql_header_dsu = "SELECT idCatSalud, nombre FROM modulo_carga_ges.catSalud;"; 
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[$fila["idCatSalud"]] = $fila["nombre"];
                }
            }

            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }

        return $response;
    }

    public static function get_municipalities_per_client( $idClient ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        $sql_header_dsu = "
            SELECT 
                cm.idMunicipio
            FROM modulo_carga_ges.catCliente AS cc
            LEFT JOIN modulo_carga_ges.catClienteRelEstado AS ccre ON ccre.idCliente = cc.idCliente
            LEFT JOIN modulo_carga_ges.catMunicipio AS cm ON cm.idEntidad = ccre.idEstado
            WHERE cc.idCliente = ". $idClient ." ORDER BY cm.idMunicipio ASC
        "; 

        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[] = $fila["idMunicipio"];
                }
            }

            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }

        return $response;
    }

    public static function get_total_per_week( $start_date, $end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_total_per_week_salud(
            '". $start_date ."', 
            '". $end_date ."', 
            ".  $idCliente .", 
            '". $services ."', 
            '". $municipalities . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);     

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[] =$fila;
                }
            }
            $response["total_demandas"] = $get_row[0]["total_demandas"];  
            $response["vs_semana"] = $get_row[0]["total_semana_anterior"];
            $response["promedio"] = $get_row[0]["total_demandas"];                     
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_per_service_salud( $start_date, $end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_total_per_service_salud(
            '". $start_date ."', 
            '". $end_date ."', 
            ". $idCliente .", 
            '". $services ."', 
            '". $municipalities . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                        

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[] =$fila;
                }
            }
            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_per_urban_service_salud( $start_date, $end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_urban_service_salud(
            '". $start_date ."', 
            '". $end_date ."', 
            ". $idCliente .", 
            '". intval($services) ."', 
            '". $municipalities . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                        

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[0]["clave"] = "Categoría";
                    $get_row[0]["nombre"] = $fila["category"];
                    $get_row[0]["total"] = $fila["totalCategory"];

                    $get_row[1]["clave"] = "Institutión";
                    $get_row[1]["nombre"] = $fila["institution"];
                    $get_row[1]["total"] = $fila["totalInstitution"];

                    $get_row[2]["clave"] = "Institutión";
                    $get_row[2]["nombre"] = $fila["establishment"];
                    $get_row[2]["total"] = $fila["totalEstablishment"];
                }
            }
            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_by_municipalitie( $start_date, $end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_total_per_municipalitie_salud(
            '". $start_date ."', 
            '". $end_date ."', 
            ". $idCliente .", 
            '". $services ."', 
            '". $municipalities . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                        

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[$fila["municipalitieName"]][$fila["servicio"]] = $fila["total"];
                }
            }
            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_per_month($end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);
        $days = 29;
        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_total_per_month_salud(
            '". $end_date ."', 
            ". $idCliente .", 
            '". $services ."', 
            '". $municipalities . "',
            '". $days . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                        

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $servicio = $fila["servicio"];
                    unset($fila["servicio"]);
                    $get_row[$servicio] = $fila;

                }
            }
            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_per_weeks($end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);
        $weeks = 11;
        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_total_per_weeks_all_salud(
            '". $end_date ."', 
            ". $idCliente .", 
            '". $services ."', 
            '". $municipalities . "',
            '". $weeks . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                        

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $servicio = $fila["servicio"];
                    unset($fila["servicio"]);
                    $get_row[$servicio] = $fila;

                }
            }
            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_all_months_salud($end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_total_all_months_salud(
            '". $end_date ."', 
            ". $idCliente .", 
            '". $services ."', 
            '". $municipalities . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                        

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $servicio = $fila["servicio"];
                    unset($fila["servicio"]);
                    $get_row[$servicio] = $fila;
                }
            }
            $response = $get_row;                        
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_per_institutions( $start_date, $end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_total_per_level_salud(
            '". $start_date ."', 
            '". $end_date ."', 
            ".  $idCliente .", 
            '". $services ."', 
            '". $municipalities . "'
        )
        ";

                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);     

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[] =$fila;
                }
            }
            $response = $get_row;                 
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_total_per_categories( $start_date, $end_date, $idCliente, $services, $municipalities ){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);

        #Consulto la data de header de reporte
        $sql_header_dsu = "
        CALL modulo_carga_ges.get_table_by_categories_salud(
            '". $start_date ."', 
            '". $end_date ."', 
            ".  $idCliente .", 
            '". $services ."', 
            '". $municipalities . "'
        )
        ";
                
        $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);     

        try{
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            }
            else{
                $get_row = array();
                while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                    $get_row[] =$fila;
                }
            }
            $response = $get_row;                 
        }
        catch(Exception $e){
            echo $e->getMessage();
            die();
        }
        return $response;
    }

    public static function get_final_tables($start_date, $end_date, $idCliente, $services, $municipalities){
        global $General;
        $ConfigMySQLDSU = $General->getConexionGES(MOD_AMBIENT);
        $ConfigMySQLDSU2 = $General->getConexionGES(MOD_AMBIENT);
    
        # Consulto la data de header de reporte
        $sql_header_dsu = "
            CALL modulo_carga_ges.get_total_data_by_category_salud(
                '" . $start_date . "',
                '" . $end_date . "',
                " . $idCliente . ",
                '" . $services . "',
                '" . $municipalities . "'
            )
        ";
    
        $sql_total_data = "CALL modulo_carga_ges.get_total_data_by_categoryExplain_salud(
            '" . $start_date . "',
            '" . $end_date . "',
            " . $idCliente . ",
            '" . $services . "',
            '" . $municipalities . "'
        )";
    
        $resultado = mysqli_query($ConfigMySQLDSU, $sql_header_dsu);
        $resultado_total = mysqli_query($ConfigMySQLDSU2, $sql_total_data);
    
        try {
            if (!$resultado) {
                echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                $response["code"] = 301;
                $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_header_dsu";
            } else {
                $get_row = array();
                while ($fila = mysqli_fetch_array($resultado, MYSQLI_ASSOC)) {
                    $array = [];
                    $array["establecimiento"] = $fila["Nombre_inst"];
                    $array["municipio"] = $fila["Municipio"];
                    $array["total"] = $fila["totals"];
    
                    $get_row[$fila["Institucion_sec"]]["table_totals"][] = $array;
                }
    
                while ($fila_total = mysqli_fetch_array($resultado_total, MYSQLI_ASSOC)) {
                    $array2 = [];
                    $array2["establecimiento"] = $fila_total["Nombre_inst"];
                    $category = $fila_total["nombre_cat"];
                    $totals = $fila_total["totalByCat"];
    
                    // Agrupar los totales por establecimiento y category
                    if (isset($get_row[$fila_total["Institucion_sec"]]["totals"][$array2["establecimiento"]][$category])) {
                        $get_row[$fila_total["Institucion_sec"]]["totals"][$array2["establecimiento"]][$category]["totals"] += $totals;
                    } else {
                        $get_row[$fila_total["Institucion_sec"]]["totals"][$array2["establecimiento"]][$category] = [
                            "category" => $category,
                            "totals" => $totals,
                        ];
                    }
                }
    
                // Convertir el formato de totals a un array
                foreach ($get_row as &$item) {
                    foreach ($item["totals"] as &$establecimiento) {
                        $establecimiento = array_values($establecimiento);
                    }
                }
    
                $response[] = $get_row;
            }
        } catch (Exception $e) {
            echo $e->getMessage();
            die();
        }
    
        return $response;
    }
}