<%-- 
    Document   : xml_get_grafica
    Created on : 31/01/2012, 12:47:38 PM
    Author     : psoto
--%>

<%@page contentType="text/xml" pageEncoding="UTF-8"%>
<%@page
    import = "conexion.*"
    import = "java.sql.*"
    import = "java.text.NumberFormat"
    import = "java.text.DecimalFormat"
    import = "general.Config"
    import = "general.Permisos"
    import = "java.io.File"
    import = "java.io.InputStream"
    import = "org.w3c.dom.Document"
    import = "org.w3c.dom.*"
    import = "javax.xml.parsers.DocumentBuilderFactory"
    import = "javax.xml.parsers.DocumentBuilder"
    import = "org.xml.sax.SAXException"
    import = "org.xml.sax.SAXParseException"
    import = "org.xml.sax.InputSource"
    import = "java.net.URL"
    import = "java.net.URLConnection"
    import = "java.net.HttpURLConnection"
    import = "java.util.HashMap"
    import = "java.util.Enumeration"
    import = "java.util.Date"
    import = "java.text.SimpleDateFormat" 
    %>

    <%
        ConexionEquipos c = null;
        Connection lcnt_con = null;
        ResultSet lrs_res = null;
        Statement lst_stm = null;
        String ls_query = new String("");
        ResultSet lrs_res2 = null;
        Statement lst_stm2 = null;
        String ls_query2 = new String("");
        String emergent_error = new String("");
        boolean lb_connection = false;
        String debug = new String("si");
        String fecha_inicio = new String("si");
        String fecha_termino = new String("");
        String tipo_ticket = new String("");
        String tipo_campo = new String("fecha_cierre");
        int total_categorias = 0;
        int total_areas = 0;
        String ids_categorias = new String("");
        String ids_areas = new String("");
        String xml_completo = "";

        // Conexión a la base de datos
        try {
            if (!Config.getParametro("debug").equals("si")) {
                debug = "no";
            }
            c = new ConexionEquipos();
            lcnt_con = c.getConnection();
            lst_stm = lcnt_con.createStatement();
            lst_stm2 = lcnt_con.createStatement();
            lb_connection = true;
        } catch (Exception e) {
            emergent_error = "Error al conectarse a la base de datos ";
            if (debug.equals("si")) {
                emergent_error += e;
            }
            lb_connection = false;
        }

        if (lb_connection) {
            try {
                 Date hoy = new Date();
                SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String fecha_hoy = formato.format(hoy);
            
                fecha_inicio = request.getParameter("fecha_inicio");
                fecha_termino = request.getParameter("fecha_inicio");
                tipo_ticket = request.getParameter("fecha_inicio");

                Enumeration names = request.getParameterNames();
                String nextID = new String("");
                while (names.hasMoreElements()) {
                    nextID = names.nextElement().toString();
                    if (nextID.startsWith("id_cat_categorias_")) {
                        ids_categorias += nextID.substring(18) + ",";
                        total_categorias++;
                    }
                }

                ids_categorias = ids_categorias.substring(0, ids_categorias.length() - 1);
                nextID = new String("");
                names = request.getParameterNames();
                while (names.hasMoreElements()) {
                    nextID = names.nextElement().toString();
                    if (nextID.startsWith("id_area_")) {
                        ids_areas += nextID.substring(8) + ",";
                        total_areas++;
                    }
                }
                ids_areas = ids_areas.substring(0, ids_areas.length() - 1);

                if (request.getParameter("tipo_ticket") != null) {
                    tipo_ticket = request.getParameter("tipo_ticket");
                    if (request.getParameter("tipo_ticket").equals("1")) {
                        tipo_campo = " fecha_creacion ";
                    } else if (request.getParameter("tipo_ticket").equals("2")) {
                        tipo_campo = " fecha_cierre ";
                    } else if (request.getParameter("tipo_ticket").equals("3")) {
                        tipo_campo = " fecha_creacion ";
                    }
                }
                
                xml_completo += "<data>";
                ls_query = "SELECT id_cat_categorias, nombre_categoria  FROM cat_categorias WHERE status=true AND id_cat_categorias in(" + ids_categorias + ") ORDER BY nombre_categoria ";
                lrs_res = lst_stm.executeQuery(ls_query);                
                    xml_completo += "<row_0>";
                    xml_completo += "<area></area>";                    
                while (lrs_res.next()) {                    
                    xml_completo += "<valor>"+lrs_res.getString("nombre_categoria")+"</valor>";                                        
                }
                xml_completo += "</row_0>";
                
                int tmp_area =1;
                String[] ids_array = ids_categorias.split(",");
                ls_query = "SELECT "
                                    + "     ca.id_area, "
                                    + "     ca.nombre,  "
                                    + "     ca.clave_area  "
                                    + "FROM cat_areas ca "
                                    + "WHERE status= true  "
                                    +"   AND id_area in("+ids_areas+")"
                                    + "ORDER BY ca.nombre";
                            lrs_res = lst_stm.executeQuery(ls_query);
                            while (lrs_res.next()) {
                                xml_completo += "<row_"+tmp_area+">";
                                 xml_completo +=" <area>"+lrs_res.getString("clave_area")+"</area>";                                
                                for (int x = 0; x < ids_array.length; x++) {
                                    ls_query = "SELECT "
                                            + "    	id_area, "
                                            + "    	nombre_categoria, "
                                            + "	    id_cat_categorias, "
                                            + "    	COUNT(id_tickets) total_ticket "
                                            + "    FROM usuarios_cargos uc  "
                                            + "LEFT JOIN tickets tk USING(id_usuarios_cargos) "
                                            + "LEFT JOIN cat_categorias ct USING(id_cat_categorias) "
                                            + "WHERE   "
                                            + "		id_area= " + lrs_res.getString("id_area")
                                            + "    AND	id_cat_categorias= " + ids_array[x]
                                            + "    AND	tk.status= TRUE "
                                            + "     AND " + tipo_campo + " >= '" + (request.getParameter("fecha_inicio") == null ? fecha_hoy : request.getParameter("fecha_inicio")) + "' "
                                            + "     AND " + tipo_campo + "  <= ADDDATE('" + (request.getParameter("fecha_termino") == null ? fecha_hoy : request.getParameter("fecha_termino")) + "',INTERVAL 1 DAY ) ";
                                    if (tipo_ticket.equals("3")) {
                                        ls_query += " AND cerrado= false ";
                                    } 
                                    ls_query += " GROUP BY id_area, id_cat_categorias";
                                    lrs_res2 = lst_stm2.executeQuery(ls_query);
                                    if (lrs_res2.next()) {
                                        xml_completo += "<valor>" + lrs_res2.getString("total_ticket") + "</valor>";
                                    } else {
                                         xml_completo += "<valor>0</valor>";
                                    }                                    
                                }   
                                    xml_completo += "</row_"+tmp_area+">";
                                    tmp_area++;                                                                    
                            }                    

                xml_completo += "</data>";
            out.println(xml_completo);
            } catch (Exception e) {
                emergent_error += "Error: " + e.toString() + " Ultimo query: " + ls_query;
                out.println("<response<error>Error en el proceso. Error: " + emergent_error + "</error></response>");
            } finally {
                lst_stm.close();
            }
        } else {
            out.println("<response<error>Error en la conexion.</error></response>");
        }
    %>