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);
}
