<?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 DSUController extends cGeneral{

        public static function get_header_dsu($id_edo,$date_start,$periodo,$filter_services,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);
            $periodomas1 = $periodo + 1;
            $label = " DAY ";            
            $periodoant = ($periodo * 2) + 1;


            #Consulto la data de header de reporte
            $sql_header_dsu = "/*CONSULTA DE CABECERA DEL REPORTE*/
                                   /* SET lc_time_names = 'es_ES';*/

                                    SELECT 
                                    cant_demandas,
                                    cant_demandas_ant,
                                    vs_sem_ant,
                                    ROUND(
                                        vs_sem_ant_ant * 100 / cant_demandas - 100,
                                        0
                                    ) * - 1 AS vs_sem_ant_porc,
                                    WEEK('".$date_start."') AS num_semana,
                                    CONCAT(
                                        'Del ',
                                        DATE_FORMAT(
                                        DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label."),
                                        '%d de %M del %Y'
                                        ),
                                        '\\\\newline al ',
                                        DATE_FORMAT('".$date_start."', '%d de %M del %Y')
                                    ) AS fechas_texto 
                                    FROM
                                    (SELECT 
                                        SUM(
                                        CASE
                                            WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") 
                                            AND '".$date_start."' 
                                            THEN 1 
                                            ELSE 0 
                                        END
                                        ) AS cant_demandas,
                                        SUM(
                                        CASE
                                            WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodoant.$label.") 
                                            AND DATE_SUB('".$date_start."', INTERVAL ".($periodo+1).$label.") 
                                            THEN 1 
                                            ELSE 0 
                                        END
                                        ) AS cant_demandas_ant,
                                        SUM(
                                        CASE
                                            WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") 
                                            AND '".$date_start."' 
                                            THEN 1 
                                            ELSE 0 
                                        END
                                        ) - SUM(
                                        CASE
                                            WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodoant.$label.") 
                                            AND DATE_SUB('".$date_start."', INTERVAL ".($periodo+1).$label.") 
                                            THEN 1 
                                            ELSE 0 
                                        END
                                        ) AS vs_sem_ant,
                                        SUM(
                                        CASE
                                            WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodoant.$label.") 
                                            AND DATE_SUB('".$date_start."', INTERVAL ".($periodo+1).$label.") 
                                            THEN 1 
                                            ELSE 0 
                                        END
                                        ) AS vs_sem_ant_ant 
                                    FROM
                                        post_commentCategory c 
                                        INNER JOIN post p 
                                        ON c.idPost = p.id 
                                        INNER JOIN vistaDSU cc 
                                        ON c.idCommentCategory = cc.id5 
                                    WHERE CAST(c.idCommentCategory AS DECIMAL) >= 251 
                                        AND c.active = 1 
                                        AND p.idProject = ".$id_edo." 

                                        /*BLOQUE DE SERVICIOS*/
                                        ".$filter_services." 
                                        /*BLOQUE DE MUNICIPIO*/
                                        ".$filter_mun."  
                                        /*BLOQUE DE COLONIAS*/
                                        ".$filter_colonias."

                                    ) AS subquery ;
                                    
                                    "; 
                    
            $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                            

            try{
                if (!$resultado) {
                    #("Error de BD, no se pudo consultar la base de datos get_header_dsu" );
                    echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                    
                    #$General->closeConexcionDB($conexion);
                    $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[0];                        
            }
            catch(Exception $e){
                echo $e->getMessage();
                die();
            }

            return $response;

        }


        public static function get_count_xservicios($id_edo,$date_start,$periodo,$filter_services,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);
            $periodomas1 = $periodo + 1;
            $label = " DAY ";            
            $periodoant = ($periodo * 2) + 1;


            #Consulto la data de header de reporte
           $sql_header_dsu = "SELECT
                                    cc.category2 AS servicio,
                                    SUM(
                                        CASE
                                            WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") AND '".$date_start."'  THEN 1
                                            ELSE 0
                                        END
                                    ) AS sem_actual,
                                    SUM(
                                        CASE
                                            WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodoant.$label.") AND DATE_SUB('".$date_start."', INTERVAL ".($periodo+1).$label.")  THEN 1
                                            ELSE 0
                                        END
                                    ) AS sem_ant,
                                    (
                                        SUM(
                                            CASE
                                                WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") AND '".$date_start."'  THEN 1
                                                ELSE 0
                                            END
                                        ) - SUM(
                                            CASE
                                                WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodoant.$label.") AND DATE_SUB('".$date_start."', INTERVAL ".($periodo+1).$label.")  THEN 1
                                                ELSE 0
                                            END
                                        )
                                    ) AS vs_sem_anta,
                                    IFNULL(
                                        ROUND(
                                            (
                                                SUM(
                                                    CASE
                                                        WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodoant.$label.") AND DATE_SUB('".$date_start."', INTERVAL ".($periodo+1).$label.")  THEN 1
                                                        ELSE 0
                                                    END
                                                )
                                            ) * 100 / NULLIF(
                                                SUM(
                                                    CASE
                                                        WHEN p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") AND '".$date_start."'  THEN 1
                                                        ELSE 0
                                                    END
                                                ), 0
                                            ) - 100, 0
                                        ), 0
                                    ) AS vs_sem_ant_porc
                                FROM
                                    post_commentCategory c
                                    INNER JOIN post p ON c.idPost = p.id
                                    INNER JOIN vistaDSU cc ON c.idCommentCategory = cc.id5
                                WHERE
                                    CAST(
                                        c.idCommentCategory AS DECIMAL
                                    ) >= 251
                                    AND c.active = 1
                                    AND p.idproject = ".$id_edo."

                                    /*BLOQUE DE SERVICIOS*/
                                    ".$filter_services." 
                                    /*BLOQUE DE MUNICIPIO*/
                                    ".$filter_mun."  
                                    /*BLOQUE DE COLONIAS*/
                                    ".$filter_colonias."
                                    
                                GROUP BY
                                    cc.category2
                                ORDER BY FIELD(cc.category2, 'AGUA', 'VIALIDADES', 'ALUMBRADO Y ELECTRICIDAD', 'BASURA', 'ESPACIOS PÚBLICOS') ASC, sem_actual DESC, sem_ant DESC;"; 
                    
            $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                            

            try{
                if (!$resultado) {
                    #("Error de BD, no se pudo consultar la base de datos get_header_dsu" );
                    echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                    
                    #$General->closeConexcionDB($conexion);
                    $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;
                    }

                    /*
                    $___servicios        = array("AGUA" => "Agua", "ALUMBRADO Y ELECTRICIDAD" => "Alumbrado y electricidad", "BASURA" => "Basura", 
                                                 "VIALIDADES" => "Vialidades", "ESPACIOS PÚBLICOS" => "Espacios públicos");
                    for($aux=0;count($get_row)>$aux;$aux++){
                        $get_row[$aux]["servicio"] = $___servicios[$get_row[$aux]["servicio"]] ;
                    }
                    
                    */


                }

                $response = $get_row;                        
            }
            catch(Exception $e){
                echo $e->getMessage();
                die();
            }

            return $response;

        }

        public static function get_top_serv_urbanos($id_edo,$date_start,$periodo,$filter_services,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);
            $periodomas1 = $periodo + 1;
            $label = " DAY ";            
            $periodoant = ($periodo * 2) + 1;

            #-- WHERE servicio IN ("AGUA") #Filtrado por tipo de servicio
            $__filter_services = "";
            if(isset($filter_services) && !empty($filter_services)){
                $json_serv = json_decode($filter_services, true);
                #print_r($json_category); 
                $__filter_services =  "WHERE servicio IN (";

                for($x=0;count($json_serv) > $x; $x++){    
                    $__filter_services .= "'".$json_serv[$x]."',";
                }
                $__filter_services = substr($__filter_services, 0, -1);
                $__filter_services .=  ") ";
            }

            #Consulto la data de header de reporte
            $sql_header_dsu = " SELECT t1.servicio, municipio, t_mun, categoria, t_cat, colonia, t_col FROM (SELECT servicio, municipio, MAX(t_mun) as t_mun FROM (SELECT 
                                    cc.category2 AS servicio,
                                    p.idMunicipio AS municipio,
                                    COUNT(p.idMunicipio) AS t_mun
                                FROM
                                    post_commentCategory c
                                        INNER JOIN
                                    post p ON c.idPost = p.id
                                        INNER JOIN
                                    vistaDSU cc ON c.idCommentCategory = cc.id5
                                WHERE
                                    p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") AND '".$date_start."'
                                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                        AND c.active = 1
                                        AND p.idProject = ".$id_edo."
                                        /*BLOQUE DE MUNICIPIO*/
                                        ".$filter_mun."  
                                GROUP BY cc.category2, p.idMunicipio) AS t1 GROUP BY servicio ORDER BY t_mun DESC) as t1
                                INNER JOIN
                                (SELECT servicio, categoria, MAX(t_cat) as t_cat FROM (SELECT 
                                    cc.category2 AS servicio,
                                    cc.category5 AS categoria,
                                    COUNT(cc.category2) AS t_cat
                                FROM
                                    post_commentCategory c
                                        INNER JOIN
                                    post p ON c.idPost = p.id
                                        INNER JOIN
                                    vistaDSU cc ON c.idCommentCategory = cc.id5
                                WHERE
                                    p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") AND '".$date_start."'
                                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                        AND c.active = 1
                                        AND p.idProject = ".$id_edo."
                                GROUP BY cc.category2, cc.category5) AS t1 GROUP BY servicio ORDER BY t_cat DESC) as t2 ON t1.servicio = t2.servicio
                                INNER JOIN
                                (SELECT servicio, colonia, MAX(t_col) as t_col FROM (SELECT 
                                    cc.category2 AS servicio,
                                    p.idColonia AS colonia,
                                    COUNT(p.idColonia) AS t_col
                                FROM
                                    post_commentCategory c
                                        INNER JOIN
                                    post p ON c.idPost = p.id
                                        INNER JOIN
                                    vistaDSU cc ON c.idCommentCategory = cc.id5
                                WHERE
                                    p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") AND '".$date_start."'
                                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                        AND c.active = 1
                                        AND p.idProject = ".$id_edo."
                                        /*BLOQUE DE COLONIAS*/
                                        ".$filter_colonias."
                                GROUP BY cc.category2, p.idColonia) AS t1 
                                        /*BLOQUE DE SERVICIOS*/
                                        ".$__filter_services."  
                                        GROUP BY servicio ORDER BY t_col DESC) as t3 ON t1.servicio = t3.servicio
                                ORDER BY FIELD(t1.servicio, 'AGUA', 'VIALIDADES', 'ALUMBRADO Y ELECTRICIDAD', 'BASURA', 'ESPACIOS PÚBLICOS');"; 
            
            $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu); 

            try{
                if (!$resultado) {
                    #("Error de BD, no se pudo consultar la base de datos get_header_dsu" );
                    echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                    
                    #$General->closeConexcionDB($conexion);
                    $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;
                    }

                    for($aux=0;count($get_row)>$aux;$aux++){
                        
                        if(isset($get_row[$aux]["colonia"]) && !empty($get_row[$aux]["colonia"])){
                            $get_info_col = CentralizadoController::get_info_col_mun($get_row[$aux]["colonia"]);
                            if(isset($get_info_col[0]["name_col"])){
                                $get_row[$aux]["id_colonia"] = $get_row[$aux]["colonia"];
                                $get_row[$aux]["colonia"] = utf8_encode($get_info_col[0]["name_col"]);                            
                            }
                        }

                        if(isset($get_row[$aux]["municipio"]) && !empty($get_row[$aux]["municipio"])){
                            $get_info_mun = CentralizadoController::get_info_mun($get_row[$aux]["municipio"]);
                            if(isset($get_info_mun[0]["name_mun"])){
                                $get_row[$aux]["id_municipio"] = $get_row[$aux]["municipio"];
                                $get_row[$aux]["municipio"] = utf8_encode($get_info_mun[0]["name_mun"]);                            
                            }
                        }
                    }
                    


                }

                $response = $get_row;                        
            }
            catch(Exception $e){
                echo $e->getMessage();
                die();
            }

            return $response;

        }

        public static function grafica_semanas($id_edo,$date_start,$periodo,$filter_services,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);
            $periodomas1 = $periodo + 1;
            $label = " DAY ";            
            $periodoant = ($periodo * 2) + 1;

            #Consulto la data de header de reporte
            $sql_header_dsu = "SET @project_id = ".$id_edo.";
                                SET @number_of_weeks = 11;
                                SET @date_conditions = '';
                                SET @actual_date = '".$date_start."';
                                CALL DSAnalytics.loadDatesByWeek(@number_of_weeks,  @actual_date, @date_conditions);
                                SELECT @date_conditions AS result INTO @column_sql;
                                SET @final_sql = CONCAT(
                                    \"SELECT cc.category2, \", @column_sql,
                                    \" FROM DSAnalytics.vistaDSU AS cc
                                    LEFT JOIN DSAnalytics.post_commentCategory AS pcc ON pcc.idCommentCategory = cc.id5
                                    LEFT JOIN DSAnalytics.post AS p ON pcc.idPost = p.id
                                    WHERE 
                                        p.idProject = \", @project_id , \"

                                        /*BLOQUE DE SERVICIOS*/
                                        ".$filter_services." 
                                        /*BLOQUE DE MUNICIPIO*/
                                        ".$filter_mun."  
                                        /*BLOQUE DE COLONIAS*/
                                        ".$filter_colonias."

                                        AND p.postDate BETWEEN \", QUOTE(DATE_SUB(@actual_date, INTERVAL 3 MONTH)), \" AND \", QUOTE(@actual_date), \"
                                    GROUP BY cc.id2
                                    ORDER BY FIELD(cc.category2, 'AGUA', 'VIALIDADES', 'ALUMBRADO Y ELECTRICIDAD', 'BASURA', 'ESPACIOS PÚBLICOS') ASC;\"
                                );
                                PREPARE stmt FROM @final_sql;
                                EXECUTE stmt;";  
            #$resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu); 
            $resultado = mysqli_multi_query($ConfigMySQLDSU, $sql_header_dsu);
            if($resultado){
                do{
                    if($result = mysqli_use_result($ConfigMySQLDSU)){
                        $aux = 0;$_response = array();
                        while($row = mysqli_fetch_row($result)){
                            $_response[$aux] =  $row;
                            $aux++;
                        }
                        mysqli_free_result($result);
                    }
                    if(mysqli_more_results($ConfigMySQLDSU)){
                        print("\n");
                    }
                }while(mysqli_next_result($ConfigMySQLDSU));
            }
              
           
                $response = $_response;   

            return $response;

        }

        public static function get_grafica_mun($id_edo,$date_start,$periodo,$filter_services,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);
            $periodomas1 = $periodo + 1;
            $label = " DAY ";            
            $periodoant = ($periodo * 2) + 1;


            #Consulto la data de header de reporte
            $sql_header_dsu = "/*GRAFICA DE TOTALES POR SERVICIO Y MUNICIPIO*/
                SELECT t2.servicio, t2.municipio, COALESCE(t1.total, 0) as total FROM (
                    SELECT 
                        cc.category2 AS servicio,
                        p.idMunicipio AS municipio,
                        COUNT(cc.category2) AS total
                    FROM
                        post_commentCategory c
                            INNER JOIN
                        post p ON c.idPost = p.id
                            INNER JOIN
                        vistaDSU cc ON c.idCommentCategory = cc.id5
                    WHERE
                        p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo." ".$label.") AND '".$date_start."'
                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                        AND c.active = 1
                        AND p.idProject = ".$id_edo."

                        /*BLOQUE DE SERVICIOS*/
                        ".$filter_services." 
                        /*BLOQUE DE MUNICIPIO*/
                        ".$filter_mun."  
                        /*BLOQUE DE COLONIAS*/
                        ".$filter_colonias."

                    GROUP BY cc.category2, p.idMunicipio
                ) as t1 RIGHT JOIN (
                    SELECT 
                        cc.category2 AS servicio, 
                        p.idMunicipio AS municipio
                    FROM
                        (
                            SELECT DISTINCT
                                p.idMunicipio AS idMunicipio
                            FROM
                                post_commentCategory c
                                    INNER JOIN
                                post p ON c.idPost = p.id
                                    INNER JOIN
                                vistaDSU cc ON c.idCommentCategory = cc.id5
                            WHERE
                                p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo." ".$label.") AND '".$date_start."'
                                AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                AND c.active = 1
                                AND p.idProject = ".$id_edo."

                                /*BLOQUE DE SERVICIOS*/
                                ".$filter_services." 
                                /*BLOQUE DE MUNICIPIO*/
                                ".$filter_mun."  
                                /*BLOQUE DE COLONIAS*/
                                ".$filter_colonias."
                        ) AS p
                    CROSS JOIN vistaDSU cc
                    LEFT JOIN post_commentCategory c 
                        ON c.idPost = p.idMunicipio 
                        AND c.idCommentCategory = cc.id5
                    WHERE
                        1
                        /*BLOQUE DE SERVICIOS*/
                        ".$filter_services." 
                        /*BLOQUE DE MUNICIPIO*/
                        ".$filter_mun."  
                        /*BLOQUE DE COLONIAS*/
                        ".$filter_colonias."
                    GROUP BY cc.category2 , p.idMunicipio
                ) as t2 ON t1.servicio = t2.servicio AND t1.municipio = t2.municipio 
                ORDER BY FIELD(t2.servicio, 'AGUA', 'VIALIDADES', 'ALUMBRADO Y ELECTRICIDAD', 'BASURA', 'ESPACIOS PÚBLICOS') ASC;";
                

                                
            $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
            // SELECT 
            //     cc.category2 AS servicio,
            //     p.idMunicipio AS municipio
            // FROM
            //     post_commentCategory c
            // INNER JOIN post p ON c.idPost = p.id
            // INNER JOIN vistaDSU cc ON c.idCommentCategory = cc.id5
            // WHERE
            //     p.idProject = ".$id_edo."
            
            //     /*BLOQUE DE SERVICIOS*/
            //     ".$filter_services." 
            //     /*BLOQUE DE MUNICIPIO*/
            //     ".$filter_mun."  
            //     /*BLOQUE DE COLONIAS*/
            //     ".$filter_colonias."
            
            // GROUP BY cc.category2, p.idMunicipio

            try{
                if (!$resultado) {
                    #("Error de BD, no se pudo consultar la base de datos get_header_dsu" );
                    echo mysqli_errno($ConfigMySQLDSU) . ": " . mysqli_error($ConfigMySQLDSU);
                    
                    #$General->closeConexcionDB($conexion);
                    $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;
                    }

                    for($x=0;count($get_row)>$x;$x++){
                        $get_info_mun = CentralizadoController::get_info_mun($get_row[$x]["municipio"]);
                        if(isset($get_info_mun[0]["name_mun"]))
                        $get_row[$x]["municipio"] = utf8_encode($get_info_mun[0]["name_mun"]);
                    }


                }

                $response = $get_row;                        
            }
            catch(Exception $e){
                echo $e->getMessage();
                die();
            }

            return $response;

        }


         ## TOP 10 DE COLONIAS
         public static function get_top_colonias($id_edo,$date_start,$periodo,$filter_services,$filter_mun,$filter_colonias){
            $periodomas1 = $periodo + 1;
            $label = " DAY ";            
            $periodoant = ($periodo * 2) + 1;
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT) ;

            #Consulto la data de header de reporte
            $sql_header_dsu = "SELECT
                                    p.idColonia AS colonia,
                                    p.idMunicipio as municipio,
                                    COUNT(p.idColonia) AS total
                                FROM
                                    post_commentCategory c
                                        INNER JOIN
                                    post p ON c.idPost = p.id
                                        INNER JOIN
                                    vistaDSU cc ON c.idCommentCategory = cc.id5
                                WHERE
                                    p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL '".$periodo."' ".$label.") AND '".$date_start."'
                                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                        AND c.active = 1
                                        AND p.idProject = ".$id_edo."

                                        /*BLOQUE DE SERVICIOS*/
                                        ".$filter_services." 
                                        /*BLOQUE DE MUNICIPIO*/
                                        ".$filter_mun."  
                                        /*BLOQUE DE COLONIAS*/
                                        ".$filter_colonias."

                                GROUP BY p.idColonia ORDER BY total DESC LIMIT 10;";
            $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                            

            try{
                if (!$resultado) {
                    #("Error de BD, no se pudo consultar la base de datos get_top_colonias" );
                    $General->closeConexcionDB($conexion);
                    $response = "";
                }
                else{
                    $get_row = array();
                    while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                        $get_row[] =$fila;
                    }

                    for($aux=0;count($get_row)>$aux;$aux++){
                        if(!empty($get_row[$aux]["municipio"]) && isset($get_row[$aux]["municipio"])){
                            $get_info_mun = CentralizadoController::get_info_mun($get_row[$aux]["municipio"]);
                            if(isset($get_info_mun[0]["name_mun"])){
                                $get_row[$aux]["id_municipio"] = $get_row[$aux]["municipio"];
                                $get_row[$aux]["municipio"] = utf8_encode($get_info_mun[0]["name_mun"]);                            
                            }
                        }
                        if(!empty($get_row[$aux]["colonia"]) && isset($get_row[$aux]["colonia"])){
                            $get_info_col = CentralizadoController::get_info_col_mun($get_row[$aux]["colonia"]);
                            if(isset($get_info_col[0]["name_col"])){
                                $get_row[$aux]["id_colonia"] = $get_row[$aux]["colonia"];
                                $get_row[$aux]["colonia"] = utf8_encode($get_info_col[0]["name_col"]);
                            }
                        }
                    }
    
    
                    $response = $get_row;

                }

                
                                    
            }
            catch(Exception $e){
                echo $e->getMessage();
                die();
            }

            return $response;

        }

        public static function grafica_30_days($id_edo,$date_start,$filter_services,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);

            #Consulto la data de header de reporte
            $sql_header_dsu = "SET @project_id = ".$id_edo.";
                                SET @number_of_days = 29;
                                SET @date_conditions = '';
                                SET @actual_date = '".$date_start."';
                                CALL DSAnalytics.loadDates(@number_of_days,  @actual_date, @date_conditions);
                                SELECT @date_conditions AS result INTO @column_sql;
                                SET @final_sql = CONCAT(
                                    \"SELECT cc.category2, \", @column_sql,
                                    \" FROM DSAnalytics.vistaDSU AS cc
                                    LEFT JOIN DSAnalytics.post_commentCategory AS pcc ON pcc.idCommentCategory = cc.id5
                                    LEFT JOIN DSAnalytics.post AS p ON pcc.idPost = p.id
                                    WHERE 
                                        p.idProject = \", @project_id , \"
                                        /*BLOQUE DE SERVICIOS*/
                                        ".$filter_services." 
                                        /*BLOQUE DE MUNICIPIO*/
                                        ".$filter_mun."  
                                        /*BLOQUE DE COLONIAS*/
                                        ".$filter_colonias."
                                        AND p.postDate BETWEEN \", QUOTE(DATE_SUB(@actual_date, INTERVAL 1 MONTH)), \" AND \", QUOTE(@actual_date), \"
                                    GROUP BY cc.id2
                                    ORDER BY FIELD(cc.category2, 'AGUA', 'VIALIDADES', 'ALUMBRADO Y ELECTRICIDAD', 'BASURA', 'ESPACIOS PÚBLICOS') ASC;\"
                                );
                                PREPARE stmt FROM @final_sql;
                                EXECUTE stmt;";
            #$resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu); 
            $resultado = mysqli_multi_query($ConfigMySQLDSU, $sql_header_dsu);
            if($resultado){
                do{
                    if($result = mysqli_use_result($ConfigMySQLDSU)){
                        $aux = 0;$_response = array();
                        while($row = mysqli_fetch_row($result)){
                            $_response[$aux] =  $row;
                            $aux++;
                        }
                        mysqli_free_result($result);
                    }
                    if(mysqli_more_results($ConfigMySQLDSU)){
                        print("\n");
                    }
                }while(mysqli_next_result($ConfigMySQLDSU));
            }
              
           
                $response = $_response;   

            return $response;

        }

        public static function grafica_mes($id_edo,$date_start,$filter_services,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);

            #Consulto la data de header de reporte
            $sql_header_dsu = "SET @project_id = ".$id_edo.";
                                SET @number_of_weeks = 11;
                                SET @date_conditions = '';
                                SET @actual_date = '".$date_start."';
                                CALL DSAnalytics.loadDatesByMonth(@actual_date, @date_conditions);
                                SELECT @date_conditions AS result INTO @column_sql;
                                SET @final_sql = CONCAT(
                                    \"SELECT cc.category2, \", @column_sql,
                                    \" FROM DSAnalytics.vistaDSU AS cc
                                    LEFT JOIN DSAnalytics.post_commentCategory AS pcc ON pcc.idCommentCategory = cc.id5
                                    LEFT JOIN DSAnalytics.post AS p ON pcc.idPost = p.id
                                    WHERE 
                                        p.idProject = \", @project_id , \"
                                        /*BLOQUE DE SERVICIOS*/
                                        ".$filter_services." 
                                        /*BLOQUE DE MUNICIPIO*/
                                        ".$filter_mun."  
                                        /*BLOQUE DE COLONIAS*/
                                        ".$filter_colonias."
                                        AND p.postDate BETWEEN \", QUOTE(MAKEDATE(YEAR(@actual_date), 1)), \" AND \", QUOTE(@actual_date), \"
                                    GROUP BY cc.id2
                                    ORDER BY FIELD(cc.category2, 'AGUA', 'VIALIDADES', 'ALUMBRADO Y ELECTRICIDAD', 'BASURA', 'ESPACIOS PÚBLICOS') ASC;\"
                                );
                                PREPARE stmt FROM @final_sql;
                                EXECUTE stmt;";
            
            #$resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu); 
            $resultado = mysqli_multi_query($ConfigMySQLDSU, $sql_header_dsu);
            if($resultado){
                do{
                    if($result = mysqli_use_result($ConfigMySQLDSU)){
                        $aux = 0;$_response = array();
                        while($row = mysqli_fetch_row($result)){
                            $_response[$aux] =  $row;
                            $aux++;
                        }
                        mysqli_free_result($result);
                    }
                    if(mysqli_more_results($ConfigMySQLDSU)){
                        print("\n");
                    }
                }while(mysqli_next_result($ConfigMySQLDSU));
            }
              
           
                $response = $_response;   

            return $response;

        }

        public static function reporte_x_servicio($id_edo,$date_start,$periodo,$id_services,$array_categories,$filter_mun,$filter_colonias){
            $periodomas1 = $periodo + 1;
            $label = " DAY ";            
            $periodoant = ($periodo * 2) + 1;
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);
            mysqli_set_charset( $ConfigMySQLDSU , "utf8");

            $georefController = GeorefController::getInstance();
            $municipalities = $georefController->getMunicipalities();

            /**
                * @Posibles valores:
                * SERVICIO: AGUA
                * SET @service = 255;
                * id_services : 255
                * SET @categories = '363, 364, 365, 366, 367, 368, 459, 369, 370, 371, 432, 436, 460, 373,
                *                    374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 461, 384, 385, 386,
                *                    387, 388, 389, 390, 441, 451, 462, 463';
                * 
                * SERVICIO: VIALIDADES
                * SET @service = 256;
                * SET @categories =  '391, 392, 393, 394, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405,
                *                     407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 
                *                     421, 422, 423, 424, 433, 440, 445, 446, 447, 449, 453, 474, 475, 476, 
                *                     477';
                * 
                * SERVICIO: ALUMBRADO Y ELECTRICIDAD
                * SET @service = 254;
                * SET @categories = '465, 464, 452, 442, 437, 431, 430, 362, 361, 360, 359, 358, 357, 356, 
                *                    355, 354, 353, 352, 351, 350, 348, 347, 346, 345, 344, 343, 342, 341';
                * 
                * SERVICIO: BASURA
                * SET @service = 252;
                * SET @categories = '468, 467, 466, 448, 443, 438, 312, 311, 310, 309, 308, 307, 306, 305, 
                *                    304, 303, 302, 300, 299, 298, 297, 296, 295, 294, 293, 292, 291, 290, 
                *                    289, 288, 287';
                *
                * 
                * SERVICIO: ESPACIOS PÚBLICOS
                * SET @service = 253;
                * SET @categories = '473, 472, 471, 470, 469, 458, 457, 454, 444, 439, 435, 434, 429, 428, 
                *                    427, 426, 425, 340, 339, 338, 337, 336, 335, 334, 333, 332, 331, 330, 
                *                    329, 328, 327, 326, 325, 324, 322, 321, 320, 319, 317, 316, 315, 314, 
                *                    313';
             */

            #Consulto la data de header de reporte
            $sql_header_dsu = "SET @project_id = ".$id_edo.";
                                SET @service = ".$id_services.";
                                SET @categories = '".$array_categories."';
                                SET group_concat_max_len = 100000000;
                                SELECT GROUP_CONCAT(Resultado ORDER BY idMunicipio SEPARATOR \", \") AS ResultadoFinal
                                FROM (
                                    SELECT
                                        p.idMunicipio,
                                        CONCAT(\"SUM(CASE WHEN p.idMunicipio = \", p.idMunicipio, \" THEN 1 ELSE 0 END) AS Municipio_\", p.idMunicipio) AS Resultado
                                    FROM DSAnalytics.post as p
                                    WHERE 
                                        p.idProject = @project_id
                                        
                                        /*BLOQUE DE MUNICIPIO*/
                                        ".$filter_mun."  
                                        /*BLOQUE DE COLONIAS*/
                                        ".$filter_colonias."

                                    GROUP BY p.idMunicipio
                                ) AS Subquery INTO @column_sql;
                                SET @final_sql = CONCAT(
                                    \"SELECT cc.category5, \", @column_sql,
                                    \" FROM DSAnalytics.vistaDSU as cc
                                    LEFT JOIN DSAnalytics.post_commentCategory AS pcc ON pcc.idCommentCategory = cc.id5
                                    LEFT JOIN DSAnalytics.post AS p ON pcc.idPost = p.id
                                    WHERE 
                                        p.idProject =\", @project_id , \"
                                        AND p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL ".$periodo.$label.") AND '".$date_start."'
                                        AND cc.id2 =\", @service , \"
                                        AND cc.id5 IN (\", @categories , \")
                                    GROUP BY cc.category5;\"
                                );
                                PREPARE stmt FROM @final_sql;
                                EXECUTE stmt;";

            $resultado = mysqli_multi_query($ConfigMySQLDSU, $sql_header_dsu);
            if ($resultado) {
                do {
                    if ($result = mysqli_use_result($ConfigMySQLDSU)) {
                        $aux = 0;
                        $_response = array();
            
                        $infoColumnas = array();
                        $numColumnas = mysqli_num_fields($result);
            
                        for ($i = 0; $i < $numColumnas; $i++) {
                            $columna = mysqli_fetch_field_direct($result, $i);
                            $infoColumnas[] = $columna->name;
                        }
            
                        $_response["columnas"] = $infoColumnas;
            
                        while ($row = mysqli_fetch_row($result)) {
                            $_response["datos"][] = $row;
                            $aux++;
                        }
            
                        $resultados["table_municipalitie"] = [];
                        if (mysqli_num_rows($result) > 0) {
                            foreach ($_response['columnas'] as $index => $columna) {
                                $number_municipalitie = str_replace('Municipio_', '', $columna);

                                $name_municipio = isset($municipalities[intval($number_municipalitie)]) ? $municipalities[intval($number_municipalitie)] : "";
                                $real_name = $name_municipio;
    
                                if (strpos($columna, 'Municipio_') === 0) {
                                    $municipio = $real_name;
                                    $resultados["table_municipalitie"][$municipio] = [];
                                    $totalMunicipio = 0;
                
                                    foreach ($_response['datos'] as $dato) {
                                        $servicio = $dato[0];
                                        $cantidad = $dato[$index];
                
                                        $resultados["table_municipalitie"][$municipio][$servicio] = $cantidad;
                                        $totalMunicipio += $cantidad;
                                    }
                
                                    $resultados["table_municipalitie"][$municipio]['TOTAL DE REPORTES'] = $totalMunicipio;
                                }
                            }
                
                            $totales = array();
                            $total_final = 0;
                            foreach ($_response['datos'] as $dato) {
                                $servicio = $dato[0];
                                foreach ($resultados["table_municipalitie"] as $municipio => $datosMunicipio) {
                                    if (!isset($totales[$servicio])) {
                                        $totales[$servicio] = 0;
                                    }
                                    
                                    $totales[$servicio] += $datosMunicipio[$servicio];
                                    $total_final  += $datosMunicipio[$servicio];
                                }
                            }
                
                            $resultados["table_municipalitie"]['Total'] = $totales;
                            $resultados["table_municipalitie"]['Total']["TOTAL DE REPORTES"] =  $total_final;
                        }
                        mysqli_free_result($result);
                    }
            
                    if (mysqli_more_results($ConfigMySQLDSU)) {
                        print("\n");
                    }
                } while (mysqli_next_result($ConfigMySQLDSU));
            }

            if(!isset($resultados))
                $resultados= array();
            return $resultados;
        }

        public static function reporte_x_colonia($id_edo, $date_start, $id_service, $array_categories,$filter_mun,$filter_colonias){
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT);

            $georefController = GeorefController::getInstance();
            $municipalities = $georefController->getMunicipalities();
            $colonies = $georefController->getColonies();
            $categories_posts =  $georefController->getCategoriesPost();


            $resultados = array(); 

            

            foreach($array_categories as $category){
                $name_category = $categories_posts[$category];
                $prev_results =[];
                $sql_header_dsu = "SELECT 
                    idColonia AS colonia,
                    idMunicipio AS municipio,
                    lastWeek4,
                    lastWeek3,
                    lastWeek2,
                    lastWeek,
                    currentWeek,
                    lastWeek4 + lastWeek3 + lastWeek2 + lastWeek + currentWeek AS totalWeeks
                FROM (
                    SELECT 
                        p.idColonia, 
                        p.idMunicipio, 
                        COUNT(CASE WHEN p.postDate BETWEEN '" . $date_start ."' - INTERVAL 5 WEEK + INTERVAL 1 DAY AND '" . $date_start ."' - INTERVAL 4 WEEK THEN p.id END) AS lastWeek4, 
                        COUNT(CASE WHEN p.postDate BETWEEN '" . $date_start ."' - INTERVAL 4 WEEK + INTERVAL 1 DAY AND '" . $date_start ."' - INTERVAL 3 WEEK THEN p.id END) AS lastWeek3, 
                        COUNT(CASE WHEN p.postDate BETWEEN '" . $date_start ."' - INTERVAL 3 WEEK + INTERVAL 1 DAY AND '" . $date_start ."' - INTERVAL 2 WEEK THEN p.id END) AS lastWeek2, 
                        COUNT(CASE WHEN p.postDate BETWEEN '" . $date_start ."' - INTERVAL 2 WEEK + INTERVAL 1 DAY AND '" . $date_start ."' - INTERVAL 1 WEEK THEN p.id END) AS lastWeek, 
                        COUNT(CASE WHEN p.postDate BETWEEN '" . $date_start ."' - INTERVAL 1 WEEK + INTERVAL 1 DAY AND '" . $date_start ."' THEN p.id END) AS currentWeek
                    FROM 
                        DSAnalytics.post AS p 
                        LEFT JOIN DSAnalytics.post_commentCategory AS pcc ON pcc.idPost = p.id 
                        LEFT JOIN DSAnalytics.vistaDSU AS vd ON pcc.idCommentCategory = vd.id5 
                    WHERE 
                        /*p.idColonia IS NOT NULL 
                        AND */ p.idProject = " . $id_edo . "
                        AND vd.id2 = " . $id_service . "
                        AND vd.id5 = " . $category . "
                        AND p.postDate BETWEEN '" . $date_start ."' - INTERVAL 5 WEEK + INTERVAL 1 DAY AND '" . $date_start . "'
                        /*BLOQUE DE MUNICIPIO*/
                        ".$filter_mun."  
                        /*BLOQUE DE COLONIAS*/
                        ".$filter_colonias."
                    GROUP BY 
                        p.idColonia
                ) AS subquery
                HAVING totalWeeks > 0 AND currentWeek > 0
                ORDER BY totalWeeks DESC;";


                $resultado = mysqli_query($ConfigMySQLDSU, $sql_header_dsu);
                if ($resultado) {
                    while ($row = mysqli_fetch_assoc($resultado)) {
                        $row['municipio'] = isset($municipalities[$row['municipio']]) ? $municipalities[$row['municipio']] : "";
                        $row['colonia'] = isset($colonies[$row['colonia']]) ? $colonies[$row['colonia']] : "Sin colonia asignada";
                        $prev_results[] = $row;
                    }
                    mysqli_free_result($resultado);
                }
                $resultados[$name_category] = $prev_results;
            }

            return $resultados;
        }







        ## TABLA DE CANTIDADES DE SERVICIO POR TIPO, MUNICIPIO Y COLONIA
        public static function get_serv_tipo_mun_col($id_edo,$date_start,$periodo){
            $periodomas1 = $periodo + 1;
            $label = "";
            if($periodo == ".$periodo."){ 
                $label = " ".$label." ";
            }
            else{ 
                $label = " MONTH ";
            }
            $peridoant = ($periodo * 2) + 1;
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT) ;

            #Consulto la data de header de reporte
            $sql_header_dsu = "SELECT 
                                    servicio,
                                    MAX(categoria) AS categoria,
                                    SUM(cant_cat) AS cant_cat,
                                    MAX(municipio) AS municipio,
                                    SUM(cant_mun) AS cant_mun,
                                    MAX(colonia) AS colonia,
                                    SUM(cant_col) AS cant_col
                                FROM (
                                    SELECT 
                                        cc.category2 AS servicio,
                                        cc.category5 AS categoria,
                                        COUNT(cc.category5) AS cant_cat,
                                        NULL AS municipio,
                                        NULL AS cant_mun,
                                        NULL AS colonia,
                                        NULL AS cant_col
                                    FROM
                                        post_commentCategory c
                                    INNER JOIN
                                        post p ON c.idPost = p.id
                                    INNER JOIN
                                        vistaDSU cc ON c.idCommentCategory = cc.id5
                                    WHERE
                                        p.postDate BETWEEN DATE_SUB('".$data_start."', INTERVAL ".$periodo." ".$label.") AND '".$data_start."'
                                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                        AND c.active = 1
                                        AND p.idProject = ".$id_edo."
                                    GROUP BY cc.category2, cc.category5
                                
                                    UNION ALL
                                
                                    SELECT 
                                        cc.category2 AS servicio,
                                        NULL AS categoria,
                                        NULL AS cant_cat,
                                        p.idMunicipio AS municipio,
                                        COUNT(p.idMunicipio) AS cant_mun,
                                        NULL AS colonia,
                                        NULL AS cant_col
                                    FROM
                                        post_commentCategory c
                                    INNER JOIN
                                        post p ON c.idPost = p.id
                                    INNER JOIN
                                        vistaDSU cc ON c.idCommentCategory = cc.id5
                                    WHERE
                                        p.postDate BETWEEN DATE_SUB('".$data_start."', INTERVAL ".$periodo." ".$label.") AND '".$data_start."'
                                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                        AND c.active = 1
                                        AND p.idProject = ".$id_edo."
                                    GROUP BY cc.category2, p.idMunicipio
                                
                                    UNION ALL
                                
                                    SELECT 
                                        cc.category2 AS servicio,
                                        NULL AS categoria,
                                        NULL AS cant_cat,
                                        NULL AS municipio,
                                        NULL AS cant_mun,
                                        p.idColonia AS colonia,
                                        COUNT(p.idColonia) AS cant_col
                                    FROM
                                        post_commentCategory c
                                    INNER JOIN
                                        post p ON c.idPost = p.id
                                    INNER JOIN
                                        vistaDSU cc ON c.idCommentCategory = cc.id5
                                    WHERE
                                        p.postDate BETWEEN DATE_SUB('".$data_start."', INTERVAL ".$periodo." ".$label.") AND '".$data_start."'
                                        AND CAST(c.idCommentCategory AS DECIMAL) >= 251
                                        AND c.active = 1
                                        AND p.idProject = ".$id_edo."
                                    GROUP BY cc.category2, p.idColonia
                                ) AS subconsulta
                                GROUP BY servicio;";
                    
            $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                            

            try{
                if (!$resultado) {
                    #("Error de BD, no se pudo consultar la base de datos get_serv_tipo_mun_col" );
                    $General->closeConexcionDB($conexion);
                    $response["code"] = 301;
                    $response["msj"] = "Error de BD, no se pudo consultar la base de datos get_serv_tipo_mun_col";
                }
                else{
                    $get_row = array();
                    while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                        $get_row[] =$fila;
                    }


                }

                for($aux=0;count($get_row)>$aux;$aux++){
                    $get_row[$aux]["servicio"] = utf8_encode($get_row[$aux]["servicio"]);
                    $get_row[$aux]["categoria"] = utf8_encode($get_row[$aux]["categoria"]);
                    if(!empty($get_row[$aux]["colonia"]) && isset($get_row[$aux]["colonia"])){
                        $get_info_col = CentralizadoController::get_info_col_mun($get_row[$aux]["colonia"]);
                        $get_row[$aux]["colonia"] = utf8_encode($get_info_col[0]["name_col"]);
                        $get_row[$aux]["municpio"] = utf8_encode($get_info_col[0]["name_mun"]);
                    }
                }


                $response = $get_row;
                                    
            }
            catch(Exception $e){
                echo $e->getMessage();
                die();
            }

            return $response;

        }

       

         ## GRAFICA DE TOTALES POR SERVICIO Y MUNICIPIO
         public static function get_total_serv_mun($id_edo,$date_start,$periodo){
            $periodomas1 = $periodo + 1;
            if($periodo == ".$periodo."){ 
                $label = " ".$label." ";
            }
            else{ 
                $label = " MONTH ";
            }
            $peridoant = ($periodo * 2) + 1;
            global $General;
            $ConfigMySQLDSU = $General->getConexionDSU(MOD_AMBIENT) ;

            #Consulto la data de header de reporte
            $sql_header_dsu = "SELECT 
                                    t2.servicio,
                                    t2.municipio,
                                    t1.total 
                                FROM
                                    (SELECT 
                                    cc.category2 AS servicio,
                                    p.idMunicipio AS municipio,
                                    COUNT(cc.category2) AS total 
                                    FROM
                                    post_commentCategory c 
                                    INNER JOIN post p 
                                        ON c.idPost = p.id 
                                    INNER JOIN vistaDSU cc 
                                        ON c.idCommentCategory = cc.id5 
                                    WHERE p.postDate BETWEEN DATE_SUB('".$date_start."', INTERVAL '".$periodo."' ".$label.") 
                                    AND '".$date_start."' 
                                    AND CAST(c.idCommentCategory AS DECIMAL) >= 251 
                                    AND c.active = 1 
                                    AND p.idProject = ".$id_edo."  
                                    GROUP BY cc.category2,
                                    p.idMunicipio) AS t1 
                                    RIGHT JOIN 
                                    (SELECT 
                                        cc.category2 AS servicio,
                                        p.idMunicipio AS municipio 
                                    FROM
                                        post_commentCategory c 
                                        INNER JOIN post p 
                                        ON c.idPost = p.id 
                                        INNER JOIN vistaDSU cc 
                                        ON c.idCommentCategory = cc.id5 
                                    WHERE p.idProject = ".$id_edo." 
                                    GROUP BY cc.category2,
                                        p.idMunicipio) AS t2 
                                    ON t1.servicio = t2.servicio 
                                    AND t1.municipio = t2.municipio 
                                ORDER BY t2.servicio ";
                    
            $resultado = mysqli_query($ConfigMySQLDSU,$sql_header_dsu);
                            

            try{
                if (!$resultado) {
                    #("Error de BD, no se pudo consultar la base de datos get_top_colonias" );
                    $General->closeConexcionDB($conexion);
                    $response = "";
                }
                else{
                    $get_row = array();
                    while ($fila =mysqli_fetch_array($resultado, MYSQLI_ASSOC)){
                        $get_row[] =$fila;
                    }

                    for($aux=0;count($get_row)>$aux;$aux++){
                        $get_row[$aux]["servicio"] = utf8_encode($get_row[$aux]["servicio"]);
                        $get_info_mun = CentralizadoController::get_info_mun($get_row[$aux]["municipio"]);
                        if(isset($get_info_mun[0]["name_mun"]))
                        $get_row[$aux]["municipio"] = utf8_encode($get_info_mun[0]["name_mun"]);
                    }
    
    
                    $response = $get_row;

                }

                
                                    
            }
            catch(Exception $e){
                echo $e->getMessage();
                die();
            }

            return $response;

        }



}
?>