<%-- Document : frm_datos_grafica Created on : 1/02/2012, 01:42:23 PM Author : psoto --%> <%@page contentType="text/html"%> <%@page pageEncoding="UTF-8"%> <%@page import = "conexion.*" import = "java.sql.*" import = "extras.formatFecha" import = "general.Config" import = "general.Permisos" import = "own.codec.*" import = "java.text.NumberFormat" import = "java.util.Date" import = "java.util.Calendar" import = "java.util.Hashtable" import = "java.util.ArrayList" import = "java.text.SimpleDateFormat" import = "java.util.Enumeration" %> Grafica <% /****** Despliega la lista de cines del sistema filtrados por el texto de búsqueda de buscar_cines.jsp (no obligatorio)***********/ /********************************************creado por: L.I. Salvador Díaz Parra*******************************************************/ //out.println(""); String nompag[] = request.getRequestURI().split("/"); Date hoy = new Date(); SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); String fecha_hoy = formato.format(hoy); %> <% 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"); boolean elementos_encontrados = false; int total_categorias = 0; int total_areas = 0; String ids_categorias = new String(""); String ids_areas = new String(""); String tipo_campo = new String(""); String tipo_ticket = new String(""); // 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 { 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 "; } } %> <% 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); while (lrs_res.next()) { out.println(""); } %> <% 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()) { out.println(""); 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()) { out.println(""); } else { out.println(""); } } out.println(""); } %>
Reporte APP
" + lrs_res.getString("nombre_categoria") + "
"+lrs_res.getString("clave_area")+"" + lrs_res2.getString("total_ticket") + "0
<% } catch (Exception e) { emergent_error += "Error: " + e.toString() + " Ultimo query: " + ls_query; %>
<%=emergent_error%>
<% } finally { lst_stm.close(); lst_stm2.close(); } } %>