public bool BrechaSearch(string nombre) { var mysql = new DBConnection.ConexionMysql(); query = String.Format("SELECT * " + "FROM admin_brecha " + "WHERE nombre='{0}';", nombre); mysql.AbrirConexion(); MySqlCommand consulta = new MySqlCommand(query, mysql.conectar); MySqlDataReader buscar = consulta.ExecuteReader(); using (buscar) { while (buscar.Read()) { if (!string.IsNullOrEmpty(buscar.GetString("nombre"))) { return(true); } } } mysql.CerrarConexion(); return(false); }
public bool ExisteRol(string rol) { var mysql = new DBConnection.ConexionMysql(); query = String.Format("SELECT r.nombre As Rol " + "FROM seg_rol r " + "WHERE r.nombre = '{0}'", rol); mysql.AbrirConexion(); MySqlCommand consulta = new MySqlCommand(query, mysql.conectar); MySqlDataReader buscar = consulta.ExecuteReader(); using (buscar) { while (buscar.Read()) { if (!string.IsNullOrEmpty(buscar.GetString("Rol"))) { return(true); } } } mysql.CerrarConexion(); return(false); }
public DataTable PotenciaRead(string fadn, string ano) { DataTable dt = new DataTable(); var mysql = new DBConnection.ConexionMysql(); if (fadn == "Confederación Deportiva Autónoma de Guatemala") { add = ";"; } else { add = " WHERE fadn = '{0}' AND ano = '{1}';"; } query = String.Format("SELECT p.idpotencia_ag AS numero, n.nombre AS nivel, p.primera_potencia AS primera, " + "p.segunda_potencia AS segunda, p.tercera_potencia AS tercera, p.posicion_guatemala AS posicion " + "FROM pat_potencia_ag p " + "INNER JOIN admin_nivel n ON n.idnivel = p.fknivel" + add, fadn, ano); mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); }
public DataTable DirigenciaCreate(ModeloDirigencia objCrear) { var mysql = new DBConnection.ConexionMysql(); DataTable dt = new DataTable(); if (objCrear.fk_cargo == 0) { objCrear.fk_cargo = 3; query = String.Format("INSERT INTO pat_dirigencia_deportiva_fadn " + "(primer_nombre, segundo_nombre, primer_apellido, segundo_apellido, " + "fktipo_personal_fadn, fkcargo, fkestado, fadn, ano, fkdepartamento) " + "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', " + "'{7}', '{8}', '{9}'); ", objCrear.nombre1, objCrear.nombre2, objCrear.apellido1, objCrear.apellido2, objCrear.fk_persona, objCrear.fk_cargo, objCrear.fk_estado, objCrear.fadn, objCrear.anio, objCrear.fk_departamento); } else { objCrear.fk_departamento = 1; query = String.Format("INSERT INTO pat_dirigencia_deportiva_fadn " + "(primer_nombre, segundo_nombre, primer_apellido, segundo_apellido, " + "fktipo_personal_fadn, fkcargo, fkestado, fadn, ano, fkdepartamento) " + "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', " + "'{7}', '{8}', '{9}'); ", objCrear.nombre1, objCrear.nombre2, objCrear.apellido1, objCrear.apellido2, objCrear.fk_persona, objCrear.fk_cargo, objCrear.fk_estado, objCrear.fadn, objCrear.anio, objCrear.fk_departamento); } mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); }
public DataTable Part3Read(string fadn, string anio) { DataTable dt = new DataTable(); var mysql = new DBConnection.ConexionMysql(); query = String.Format("SELECT p3.idp3 AS numero, pe1.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_pe1 pe1 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = pe1.codigo " + "WHERE pe1.fadn = '{0}' AND pe1.ano = '{1}' " + "GROUP BY(pe1.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, pe2.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_pe2 pe2 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = pe2.codigo " + "WHERE pe2.fadn = '{0}' AND pe2.ano = '{1}' " + "GROUP BY(pe2.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c1_1.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c1_1 c1_1 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c1_1.codigo " + "WHERE c1_1.fadn = '{0}' AND c1_1.ano = '{1}' " + "GROUP BY(c1_1.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c2_1.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c2_1 c2_1 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c2_1.codigo " + "WHERE c2_1.fadn = '{0}' AND c2_1.ano = '{1}' " + "GROUP BY(c2_1.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c2_2.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c2_2 c2_2 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c2_2.codigo " + "WHERE c2_2.fadn = '{0}' AND c2_2.ano = '{1}' " + "GROUP BY(c2_2.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c2_3.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c2_3 c2_3 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c2_3.codigo " + "WHERE c2_3.fadn = '{0}' AND c2_3.ano = '{1}' " + "GROUP BY(c2_3.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c3_1.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c3_1 c3_1 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c3_1.codigo " + "WHERE c3_1.fadn = '{0}' AND c3_1.ano = '{1}' " + "GROUP BY(c3_1.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c3_2.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c3_2 c3_2 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c3_2.codigo " + "WHERE c3_2.fadn = '{0}' AND c3_2.ano = '{1}' " + "GROUP BY(c3_2.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c4_1.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c4_1 c4_1 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c4_1.codigo " + "WHERE c4_1.fadn = '{0}' AND c4_1.ano = '{1}' " + "GROUP BY(c4_1.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c4_2.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c4_2 c4_2 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c4_2.codigo " + "WHERE c4_2.fadn = '{0}' AND c4_2.ano = '{1}' " + "GROUP BY(c4_2.codigo) " + "UNION " + "SELECT p3.idp3 AS numero, c4_3.codigo AS codigo, p3.promocion AS promocion, " + "p3.programa AS programa, p3.actividad AS actividad, p3.subtotal AS subtotal, " + "p3.otra_fuente AS otra_fuente, p3.total AS total " + "FROM pat_c4_3 c4_3 " + "LEFT JOIN pat_p3 p3 ON p3.codigo = c4_3.codigo " + "WHERE c4_3.fadn = '{0}' AND c4_3.ano = '{1}' " + "GROUP BY(c4_3.codigo)", fadn, anio); mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); }
public DataTable observacionMostrarFADN(int id, int posicion) { var mysql = new DBConnection.ConexionMysql(); DataTable dt = new DataTable(); switch (posicion) { case 1: where = "oe.fkinformacion"; break; case 2: where = "oe.fkanalisis_brecha"; break; case 3: where = "oe.fkanalisis_puesto"; break; case 4: where = "oe.fkc1"; break; case 5: where = "oe.fkc1_1"; break; case 6: where = "oe.fkc2"; break; case 7: where = "oe.fkc2_1"; break; case 8: where = "oe.fkc2_2"; break; case 9: where = "oe.fkc2_3"; break; case 10: where = "oe.fkc3"; break; case 11: where = "oe.fkc3_1"; break; case 12: where = "oe.fkc3_2"; break; case 13: where = "oe.fkc4"; break; case 14: where = "oe.fkc4_1"; break; case 15: where = "oe.fkc4_2"; break; case 16: where = "oe.fkc4_3"; break; case 17: where = "oe.fkcpe"; break; case 18: where = "oe.fkdirigencia_deportiva_fadn"; break; case 19: where = "oe.fkfoda_baestrategica"; break; case 20: where = "oe.fkorganigrama"; break; case 21: where = "oe.fkp1"; break; case 22: where = "oe.fkp2"; break; case 23: where = "oe.fkp3"; break; case 24: where = "oe.fkpe1"; break; case 25: where = "oe.fkpe2"; break; case 26: where = "oe.fkpotencia_ag"; break; case 27: where = "oe.fkresultado_dih"; break; case 28: where = "oe.fkentrenador"; break; case 29: where = "oe.fkarbitro"; break; } query = String.Format("SELECT oe.idobservacion_fadn AS numero, oe.observacion AS observacion, oe.fecha AS fecha, " + "CONCAT(u.primer_nombre, ' ', u.segundo_nombre, ' ', u.primer_apellido, ' ', segundo_apellido) " + "AS usuario FROM seg_observacion_fadn oe " + "INNER JOIN seg_usuario u ON u.idusuario = oe.fkusuario " + "WHERE " + where + " = '{0}'", id); mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); }
public bool ObservacionEvaluadorExiste(int observacion, int posicion) { var mysql = new DBConnection.ConexionMysql(); switch (posicion) { case 1: where = "oe.fkinformacion"; break; case 2: where = "oe.fkanalisis_brecha"; break; case 3: where = "oe.fkanalisis_puesto"; break; case 4: where = "oe.fkc1"; break; case 5: where = "oe.fkc1_1"; break; case 6: where = "oe.fkc2"; break; case 7: where = "oe.fkc2_1"; break; case 8: where = "oe.fkc2_2"; break; case 9: where = "oe.fkc2_3"; break; case 10: where = "oe.fkc3"; break; case 11: where = "oe.fkc3_1"; break; case 12: where = "oe.fkc3_2"; break; case 13: where = "oe.fkc4"; break; case 14: where = "oe.fkc4_1"; break; case 15: where = "oe.fkc4_2"; break; case 16: where = "oe.fkc4_3"; break; case 17: where = "oe.fkcpe"; break; case 18: where = "oe.fkdirigencia_deportiva_fadn"; break; case 19: where = "oe.fkfoda_baestrategica"; break; case 20: where = "oe.fkorganigrama"; break; case 21: where = "oe.fkp1"; break; case 22: where = "oe.fkp2"; break; case 23: where = "oe.fkp3"; break; case 24: where = "oe.fkpe1"; break; case 25: where = "oe.fkpe2"; break; case 26: where = "oe.fkpotencia_ag"; break; case 27: where = "oe.fkresultado_dih"; break; case 28: where = "oe.fkentrenador"; break; case 29: where = "oe.fkarbitro"; break; } query = String.Format("SELECT oe.observacion AS observacion " + "FROM seg_observacion_evaluador oe " + "WHERE " + where + " = '{0}'", observacion); mysql.AbrirConexion(); MySqlCommand consulta = new MySqlCommand(query, mysql.conectar); MySqlDataReader buscar = consulta.ExecuteReader(); using (buscar) { while (buscar.Read()) { if (!string.IsNullOrEmpty(buscar.GetString("observacion"))) { return(true); } } } mysql.CerrarConexion(); return(false); }
public DataTable graficaAprobadoRechazadoComite(string fadn, string anio) { var mysql = new DBConnection.ConexionMysql(); DataTable dt = new DataTable(); query = String.Format("SELECT (SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_informacion pat ON pat.idinformacion = s.fkinformacion " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion = '' GROUP BY(s.fkinformacion)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_informacion pat ON pat.idinformacion = s.fkinformacion " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion != '' GROUP BY(s.fkinformacion)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO) " + "UNION ALL " + "SELECT(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_organigrama pat ON pat.idorganigrama = s.fkorganigrama " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion = '' GROUP BY(s.fkorganigrama)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_organigrama pat ON pat.idorganigrama = s.fkorganigrama " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion != '' GROUP BY(s.fkorganigrama)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO) " + "UNION ALL " + "SELECT(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_dirigencia_deportiva_fadn pat ON pat.idasamblea_personal_fadn = s.fkdirigencia_deportiva_fadn " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion = '' GROUP BY(s.fkdirigencia_deportiva_fadn)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_dirigencia_deportiva_fadn pat ON pat.idasamblea_personal_fadn = s.fkdirigencia_deportiva_fadn " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion != '' GROUP BY(s.fkdirigencia_deportiva_fadn)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO) " + "UNION ALL " + "SELECT(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_analisis_puesto pat ON pat.idanalisis_puesto = s.fkanalisis_puesto " + "WHERE pat.fadn = '{0}' AND s.observacion = '' GROUP BY(s.fkanalisis_puesto)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_analisis_puesto pat ON pat.idanalisis_puesto = s.fkanalisis_puesto " + "WHERE pat.fadn = '{0}' AND s.observacion != '' GROUP BY(s.fkanalisis_puesto)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO) " + "UNION ALL " + "SELECT(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_analisis_brecha pat ON pat.idanalisis_brecha = s.fkanalisis_brecha " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion = '' GROUP BY(s.fkanalisis_brecha)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_analisis_brecha pat ON pat.idanalisis_brecha = s.fkanalisis_brecha " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion != '' GROUP BY(s.fkanalisis_brecha)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO) " + "UNION ALL " + "SELECT(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_resultado_dih pat ON pat.idresultado_dih = s.fkresultado_dih " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion = '' GROUP BY(s.fkresultado_dih)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_resultado_dih pat ON pat.idresultado_dih = s.fkresultado_dih " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion != '' GROUP BY(s.fkresultado_dih)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO) " + "UNION ALL " + "SELECT(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_potencia_ag pat ON pat.idpotencia_ag = s.fkpotencia_ag " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion = '' GROUP BY(s.fkpotencia_ag)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_potencia_ag pat ON pat.idpotencia_ag = s.fkpotencia_ag " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion != '' GROUP BY(s.fkpotencia_ag)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO) " + "UNION ALL " + "SELECT(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_foda_baestrategica pat ON pat.idfoda_bestrategica = s.fkfoda_baestrategica " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion = '' GROUP BY(s.fkfoda_baestrategica)) AS RECHAZADO, " + "(SELECT COUNT(s.idobservacion_fadn) AS aprobado " + "FROM seg_observacion_fadn s " + "INNER JOIN pat_foda_baestrategica pat ON pat.idfoda_bestrategica = s.fkfoda_baestrategica " + "WHERE pat.fadn = '{0}' AND pat.ano = '{1}' AND s.observacion != '' GROUP BY(s.fkfoda_baestrategica)) AS APROBADO " + "FROM seg_observacion_fadn GROUP BY(APROBADO)", fadn, anio); mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); }
public DataTable graficaPorcentajeEstado(string fadn, string anio) { var mysql = new DBConnection.ConexionMysql(); DataTable dt = new DataTable(); query = String.Format("SELECT " + "(SELECT COUNT(pat.idinformacion) " + "FROM pat_informacion pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idinformacion) " + "FROM pat_informacion pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_informacion GROUP BY(LISTO) " + "UNION ALL " + "SELECT " + "(SELECT COUNT(pat.idorganigrama) " + "FROM pat_organigrama pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idorganigrama) " + "FROM pat_organigrama pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_organigrama GROUP BY(LISTO) " + "UNION ALL " + "SELECT " + "(SELECT COUNT(pat.idasamblea_personal_fadn) " + "FROM pat_dirigencia_deportiva_fadn pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idasamblea_personal_fadn) " + "FROM pat_dirigencia_deportiva_fadn pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_dirigencia_deportiva_fadn GROUP BY(LISTO) " + "UNION ALL " + "SELECT " + "(SELECT COUNT(pat.idanalisis_puesto) " + "FROM pat_analisis_puesto pat " + "WHERE pat.fadn = '{0}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idanalisis_puesto) " + "FROM pat_analisis_puesto pat " + "WHERE pat.fadn = '{0}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_analisis_puesto GROUP BY(LISTO) " + "UNION ALL " + "SELECT " + "(SELECT COUNT(pat.idanalisis_brecha) " + "FROM pat_analisis_brecha pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idanalisis_brecha) " + "FROM pat_analisis_brecha pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_analisis_brecha GROUP BY(LISTO) " + "UNION ALL " + "SELECT " + "(SELECT COUNT(pat.idresultado_dih) " + "FROM pat_resultado_dih pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idresultado_dih) " + "FROM pat_resultado_dih pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_resultado_dih GROUP BY(LISTO) " + "UNION ALL " + "SELECT " + "(SELECT COUNT(pat.idpotencia_ag) " + "FROM pat_potencia_ag pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idpotencia_ag) " + "FROM pat_potencia_ag pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_potencia_ag GROUP BY(LISTO) " + "UNION ALL " + "SELECT " + "(SELECT COUNT(pat.idfoda_bestrategica) " + "FROM pat_foda_baestrategica pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado = 13) " + "/ " + "(SELECT COUNT(pat.idfoda_bestrategica) " + "FROM pat_foda_baestrategica pat " + "WHERE pat.fadn = '{0}' " + "AND pat.ano = '{1}' AND pat.fkestado != 12) AS LISTO " + "FROM pat_foda_baestrategica GROUP BY(LISTO)", fadn, anio); mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); }
public DataTable P2Parte1Read(string fadn, string anio, int estado) { query = ""; DataTable dt = new DataTable(); var mysql = new DBConnection.ConexionMysql(); for (int i = 4; i < 9; i++) { if (estado > 1) { query = String.Format(query + " SELECT p.idp2 AS idnumero2, pac.renglon AS renglon, " + "pac.proyeccion_egresos AS nombre, p.col_uno AS monto1, p.col_dos AS monto2, " + "p.col_tres AS monto3, p.col_cuatro AS monto4, p.col_cinco AS finanza " + "FROM pat_p2 p RIGHT JOIN admin_programa_ac pac " + "ON pac.idprograma_ac = p.fkprograma_ac WHERE p.fkestado = '{0}' AND idpadre = '11' AND subpadre = '{1}' OR idprograma_ac = '{1}' GROUP BY(pac.idprograma_ac) " + "UNION ALL " + "SELECT null AS idnumero2, null, CONCAT('SUB TOTAL Q') AS nombre, " + "SUM(p.col_uno) AS monto1, SUM(p.col_dos) AS monto2," + "SUM(p.col_tres) AS monto3, SUM(p.col_cuatro) AS monto4, SUM(p.col_cinco) AS finanza " + "FROM pat_p2 p INNER JOIN admin_programa_ac pac " + "ON pac.idprograma_ac = p.fkprograma_ac " + "WHERE p.fkestado = '{0}' AND idpadre = '1' AND subpadre = '{1}' AND fadn = '{2}' AND anio = '{3}' AND idprograma_ac != '1' ", estado, i, fadn, anio); if (i == 8) { mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); } else { query = String.Format(query + " UNION ALL"); } } else { query = String.Format(query + " SELECT p.idp2 AS idnumero2, pac.renglon AS renglon, " + "pac.proyeccion_egresos AS nombre, p.col_uno AS monto1, p.col_dos AS monto2, " + "p.col_tres AS monto3, p.col_cuatro AS monto4, p.col_cinco AS finanza " + "FROM pat_p2 p RIGHT JOIN admin_programa_ac pac " + "ON pac.idprograma_ac = p.fkprograma_ac WHERE p.fkestado IN (1,2) AND idpadre = '1' AND subpadre = '{1}' OR idprograma_ac = '{1}' GROUP BY(pac.idprograma_ac) " + "UNION ALL " + "SELECT null AS idnumero2, null, CONCAT('SUB TOTAL Q') AS nombre, " + "SUM(p.col_uno) AS monto1, SUM(p.col_dos) AS monto2," + "SUM(p.col_tres) AS monto3, SUM(p.col_cuatro) AS monto4, SUM(p.col_cinco) AS finanza " + "FROM pat_p2 p INNER JOIN admin_programa_ac pac " + "ON pac.idprograma_ac = p.fkprograma_ac " + "WHERE p.fkestado IN (1,2) AND idpadre = '1' AND subpadre = '{1}' AND fadn = '{2}' AND anio = '{3}' AND idprograma_ac != '1'", estado, i, fadn, anio); if (i == 8) { mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); } else { query = String.Format(query + " UNION ALL"); } } } return(dt); }
public DataTable Part11Read(string fadn, string anio, int estado) { DataTable dt = new DataTable(); var mysql = new DBConnection.ConexionMysql(); for (int i = 1; i < 9; i++) { add = ""; if (estado > 1) { if (i == 1) { add = "WHERE fkestado = '{3}' AND idpadre = '1' AND subpadre = '{0}' AND ic.fadn = '{1}' AND p.ano = '{2}' OR ic.fadn = 'Confederacion Deportiva Autonoma de Guatemala' OR idingreso_corriente = '{0}' OR idingreso_corriente = '1' GROUP BY (ic.codigo)"; } if (i == 3 || i == 4 || i == 5 || i == 6 || i == 7) { add = "WHERE fkestado = '{3}' AND idpadre = '1' AND subpadre = '{0}' AND ic.fadn = '{1}' AND p.ano = '{2}' OR ic.fadn = 'Confederacion Deportiva Autonoma de Guatemala' OR idingreso_corriente = '{0}' GROUP BY (ic.codigo)"; } if (i == 8) { add = "WHERE fkestado = '{3}' AND idpadre = '2' AND subpadre = '{0}' AND ic.fadn = '{1}' AND p.ano = '{2}' OR ic.fadn = 'Confederacion Deportiva Autonoma de Guatemala' OR idingreso_corriente = '{0}' OR idingreso_corriente = '2' GROUP BY (ic.codigo)"; } } else { if (i == 1) { add = "WHERE fkestado IN (1,2) AND idpadre = '1' AND subpadre = '{0}' AND ic.fadn = '{1}' AND p.ano = '{2}' OR ic.fadn = 'Confederacion Deportiva Autonoma de Guatemala' OR idingreso_corriente = '{0}' OR idingreso_corriente = '1' GROUP BY (ic.codigo)"; } if (i == 3 || i == 4 || i == 5 || i == 6 || i == 7) { add = "WHERE fkestado IN (1,2) AND idpadre = '1' AND subpadre = '{0}' AND ic.fadn = '{1}' AND p.ano = '{2}' OR ic.fadn = 'Confederacion Deportiva Autonoma de Guatemala' OR idingreso_corriente = '{0}' GROUP BY (ic.codigo)"; } if (i == 8) { add = "WHERE fkestado IN (1,2) AND idpadre = '2' AND subpadre = '{0}' AND ic.fadn = '{1}' AND p.ano = '{2}' OR ic.fadn = 'Confederacion Deportiva Autonoma de Guatemala' OR idingreso_corriente = '{0}' OR idingreso_corriente = '2' GROUP BY (ic.codigo)"; } } if (add != "") { query = query + String.Format("SELECT p.idp1 AS idnumero2, ic.codigo AS codigo, " + "ic.nombre AS nombre, p.col_uno AS monto1, p.col_dos AS monto2, p.col_tres AS monto3 " + "FROM pat_p1 p RIGHT JOIN admin_ingreso_corriente ic " + "ON ic.idingreso_corriente = p.fkingreso_corriente " + add, i, fadn, anio, estado); } if (i == 8) { mysql.AbrirConexion(); MySqlDataAdapter consulta = new MySqlDataAdapter(query, mysql.conectar); consulta.Fill(dt); mysql.CerrarConexion(); return(dt); } if (i == 2 || i == 3 || i == 4 || i == 5 || i == 6 || i == 7) { query = String.Format(query + " UNION ALL "); } } return(dt); }