/**<summary>Metodo que realiza la busqueda por nombre de una rama</summary> */
/**<return> No devuelve nada </return>*/
/**<param name="sender">Objeto</param>*/
/**<param name="e">Evento</param>*/
private void btnSearch_Click(object sender, EventArgs e)
{
String parametro = this.cmbSearchBy.Value.ToString();
try
{
if (parametro == "Nombre")
{
String nameSearch = this.txtSearchName.Text;
this.cmbStated.Visible = false;
sqlString = "SELECT [id] as '#', [name] as 'NOMBRE DE RAMA', [shortName] as 'ALIAS DE RAMA',[isEnable] as 'Estado',[created] as 'FECHA DE CREACION'" +
"FROM [SACMEDB].[dbo].[branch] where name like '%" + nameSearch + "%'";
SqlManager.loadDataGridView(dataGridView_Branch, sqlString, conn);
}
else
{
this.cmbStated.Visible = true;
this.txtSearchName.Visible = false;
}
}
catch (Exception ex)
{
string ex1 = ex.ToString();
MessageBox.Show("No existen datos que se ajusten\na sus parametros de busqueda");
}
}
public void actualizarDataGrids()
{
sqlString = "select name as'ORGANIZADOR',institution as 'INSTITUCION' from SACMEDB.dbo.OrganizersTmp";
SqlManager.loadDataGridView(dataGridOrganizadores, sqlString, conn);
//sqlString = "select (select name+' '+lastname from users where id= userId)as'NOMBRES',rol as 'ROL', registeredDate as'FECHA DE REGISTRO' from SACMEDB.dbo.eventParticipant where eventId = '" + idEvento + "'";
sqlString = "select name + ' ' + lastname as 'NOMBRES',id as 'CEDULA', registrationNumber as 'MATRICULA', emailPersonal as 'CORREO' from usersTmp";
SqlManager.loadDataGridView(dataGridParticipantes, sqlString, conn);
}
private void ultraButton_courses_Click(object sender, EventArgs e)
{
String q = "", val;
int b = 0;
if (radioButton_ced.Checked)
{
val = ultraTextEditor_search.Value.ToString();
q = "SELECT c.academicTerm as 'Término',(u.name+' '+u.lastname) as 'Nombres y Apellidos',ca.name AS 'Carrera',u.registrationNumber AS 'Matrícula', c.name AS 'Curso',c.institution AS 'Institución'," +
" c.gainedPoints AS 'Puntos obtenidos', c.duration AS 'Carga horaria', c.id FROM dbo.course c, dbo.users u, dbo.career ca WHERE u.id=c.userId AND u.id='" + val + "'" +
" AND ca.id=u.careerId ORDER BY c.academicTerm DESC;";
}
else if (radioButton_mat.Checked)
{
val = ultraTextEditor_search.Value.ToString();
q = "SELECT c.academicTerm as 'Término',(u.name+' '+u.lastname) as 'Nombres y Apellidos',ca.name AS 'Carrera',u.registrationNumber AS 'Matrícula', c.name AS 'Curso',c.institution AS 'Institución'," +
" c.gainedPoints AS 'Puntos obtenidos', c.duration AS 'Carga horaria', c.id FROM dbo.course c, dbo.users u, dbo.career ca WHERE u.id=c.userId AND u.registrationNumber='" + val + "'" +
" AND ca.id=u.careerId ORDER BY c.academicTerm DESC;";
}
else if (radioButton_name.Checked)
{
val = ultraTextEditor_search.Value.ToString();
q = "SELECT c.academicTerm as 'Término',(u.name+' '+u.lastname) as 'Nombres y Apellidos',ca.name AS 'Carrera',u.registrationNumber AS 'Matrícula', c.name AS 'Curso',c.institution AS 'Institución'," +
" c.gainedPoints AS 'Puntos obtenidos', c.duration AS 'Carga horaria', c.id FROM dbo.course c, dbo.users u, dbo.career ca WHERE (u.name+' '+u.lastname) like '%" + val +
"%' AND u.id=c.userId AND ca.id=u.careerId ORDER BY c.academicTerm DESC;";
}
else
{
b = 1;
}
if (b == 0)
{
SqlManager.loadDataGridView(dataGridView_AC, q, conn);
dataGridView_AC.Enabled = true;
dataGridView_AC.Columns[8].Visible = true;
dataGridView_AC.Columns[9].Visible = false;
rowCount = 0;
while (rowCount < dataGridView_AC.Rows.Count)
{
dataGridView_AC.Rows[rowCount].Cells[0].Value = "course";
rowCount++;
}
}
else
{
MessageBox.Show("Debe elegir un parámetro de búsqueda.");
}
}
private void ultraButton_espol_Click(object sender, EventArgs e)
{
String val, q = "";
int b = 0;
if (radioButton_ced.Checked)
{
val = ultraTextEditor_search.Value.ToString();
q = "SELECT ap.academicTerm as Término,(u.name+' '+u.lastname) as 'Nombres y Apellidos', ca.name AS Carrera ,u.registrationNumber AS Matrícula, ap.gainedPoints as 'Puntos obtenidos',ap.totalPoints as 'Puntos totales'," +
"ap.espolAverage as 'Promedio ESPOL', ap.observation AS 'Observación',ap.id FROM dbo.academicPerformance ap, dbo.users u, dbo.career ca WHERE ap.userId='" + val
+ "' AND u.id=ap.userId AND ca.id=u.careerId ORDER BY ap.academicTerm DESC;";
}
else if (radioButton_mat.Checked)
{
val = ultraTextEditor_search.Value.ToString();
q = "SELECT ap.academicTerm as Término,(u.name+' '+u.lastname) as 'Nombres y Apellidos', ca.name AS Carrera ,u.registrationNumber AS Matrícula, ap.gainedPoints as 'Puntos obtenidos',ap.totalPoints as 'Puntos totales'," +
"ap.espolAverage as 'Promedio ESPOL', ap.observation AS 'Observación',ap.id FROM dbo.academicPerformance ap, dbo.users u, dbo.career ca WHERE u.registrationNumber='" + val + "' AND u.id=ap.userId AND ca.id=u.careerId ORDER BY ap.academicTerm DESC;";
}
else if (radioButton_name.Checked)
{
val = ultraTextEditor_search.Value.ToString();
q = "SELECT ap.academicTerm as Término,(u.name+' '+u.lastname) as 'Nombres y Apellidos', ca.name AS Carrera ,u.registrationNumber AS Matrícula, ap.gainedPoints as 'Puntos obtenidos',ap.totalPoints as 'Puntos totales'," +
"ap.espolAverage as 'Promedio ESPOL', ap.observation AS 'Observación',ap.id FROM dbo.academicPerformance ap, dbo.users u,dbo.career ca WHERE (u.name+' '+u.lastname) like '%" + val
+ "%' AND u.id=ap.userId AND ca.id=u.careerId ORDER BY ap.academicTerm DESC;";
}
else
{
b = 1;
}
if (b == 0)
{
SqlManager.loadDataGridView(dataGridView_AC, q, conn);
dataGridView_AC.Enabled = true;
dataGridView_AC.Columns[8].Visible = true;
dataGridView_AC.Columns[9].Visible = false;
rowCount = 0;
while (rowCount < dataGridView_AC.Rows.Count)
{
dataGridView_AC.Rows[rowCount].Cells[0].Value = "espol";
rowCount++;
}
}
else
{
MessageBox.Show("Debe elegir un parámetro de búsqueda.");
}
}
/*************************************************************************************************************************************************************/
public void btnBuscarPorTipoC_Click(object sender, EventArgs e)
{
sqlString = "SELECT[name] as 'NOMBRE DE EVENTO',[description] as 'DESCRIPCION',(SELECT SACMEDB.dbo.users.name + ' ' + SACMEDB.dbo.users.lastname from SACMEDB.dbo.users where id = responsible) as 'RESPONSABLE',[duration] as 'DURACION',[start] as 'FECHA DE INICIO',(select name from [SACMEDB].[dbo].[eventType] where id=eventTypeId) as 'TIPO DE EVENTO',(select name from [SACMEDB].[dbo].[branch] where id=branchId) as 'RAMA ENCARGADA',[created] as 'FECHA DE CREACION',(SELECT SACMEDB.dbo.users.name + ' ' + SACMEDB.dbo.users.lastname from SACMEDB.dbo.users where id = createdBy) as 'CREADO POR' FROM [SACMEDB].[dbo].[event]";
if (this.cmbBuscarPor.Text == "TIPO")
{
if (this.cmbTipoC.Text != "")
{
if (this.txtBuscar.Text == "" || this.txtBuscar.Text == "\r\n")
{
//select * from SACMEDB.dbo.event where eventTypeId = 1 and name like '%GIT%'
sqlString = sqlString + " where eventTypeId = (select id from SACMEDB.dbo.eventType where name = '" + this.cmbTipoC.Text + "')";
}
else
{
sqlString = sqlString + " where eventTypeId = (select id from SACMEDB.dbo.eventType where name = '" + this.cmbTipoC.Text + "') and name like '%" + this.txtBuscar.Text + "%'";
}
}
else
{
MessageBox.Show("Selecciona un Tipo de Proyecto", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else if (this.cmbBuscarPor.Text == "NOMBRE")
{
if (this.txtBuscar.Text != "" && this.txtBuscar.Text != "\r\n")
{
sqlString = sqlString + " where name like '%" + this.txtBuscar.Text + "%'";
}
else
{
MessageBox.Show("Ingresa algun nombre de evento", "Nombre de evento invalido", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
conn.getConnection();
SqlManager.loadDataGridView(dataGridView_Eventos, sqlString, conn);
this.txtBuscar.Text = null;
this.btnBuscarPorTipoC.Focus();
}
/**<summary>Metodo que maneja la accion al cambiar el combo relacionado con el estado.
* Segun el estado se muestra las ramas.</summary> */
/**<return> No devuelve nada </return>*/
/**<param name="sender">Objeto</param>*/
/**<param name="e">Evento</param>*/
private void cmbStated_ValueChanged(object sender, EventArgs e)
{
try {
String estado = this.cmbStated.Value.ToString();
int estadoEntero;
if (estado == "Activado")
{
estadoEntero = 1;
}
else
{
estadoEntero = 0;
}
sqlString = "SELECT [id] as '#', [name] as 'NOMBRE DE RAMA', [shortName] as 'ALIAS DE RAMA', [isEnable] as 'Estado',[created] as 'FECHA DE CREACION'" +
"FROM [SACMEDB].[dbo].[branch] where isEnable=" + estadoEntero + "";
SqlManager.loadDataGridView(dataGridView_Branch, sqlString, conn);
}
catch (Exception ex)
{
MessageBox.Show("Se produjo un error" + ex);
}
}
public void fillDatagridRemover()
{
SqlManager.loadDataGridView(dataGridRemover, sqlStringRemove, conn);
}
public void fillDatagridAgregar()
{
SqlManager.loadDataGridView(dataGridAgregar, sqlStringAdd, conn);
}
private void ultraButton_Buscar_Click(object sender, EventArgs e)
{
int rowCount;
String query = "SELECT Cargo= " +
"(SELECT dd.name " +
"FROM directorship dd, directorshipHistorial ddhh " +
"WHERE ddhh.usersId=u.id AND ddhh.directorshipId=dd.id AND ddhh.chargeTakenDate=(" +
"SELECT MAX(chargeTakenDate) " +
"FROM dbo.directorshipHistorial " +
"WHERE usersId=u.id ))," +
"Estado=" +
"(SELECT us.id " +
"FROM userState us " +
"WHERE us.id=u.userStateId), " +
"Miembro=" +
"(SELECT isMember " +
"FROM userTreatment ut " +
"WHERE ut.id=u.userTreatmentId AND ut.isMember=1), " +
"u.name as Nombres,u.lastname as Apellidos, u.id as Cédula, u.birthDate as 'Fecha de Nacimiento'," +
"u.address as Dirección, u.phone as Teléfono, u.cellphone as Celular," +
"u.registrationNumber as Matrícula,c.name as Carrera, u.emailESPOL as 'Correo de ESPOL'," +
"u.username as Usuario,u.emailPersonal as 'Correo Alterno' " +
"FROM users u,career c " +
"WHERE (c.id=u.careerId AND ";
String cont = " ;";
String q = "";
String search = ultraTextEditor_Buscar.Text;
int flag = 0;
if (search == "")
{
MessageBox.Show("Escriba algún dato en la caja de texto");
}
else
{
if (radioButton_Cedula.Checked)
{
search = ultraTextEditor_Buscar.Text;
q = query + "u.id='" + search + "')" + cont;
flag = 1;
}
else if (radioButton_Matricula.Checked)
{
search = ultraTextEditor_Buscar.Text;
q = query + "u.registrationNumber='" + search + "')" + cont;
flag = 1;
}
else if (radioButton_Nombre.Checked)
{
search = ultraTextEditor_Buscar.Text;
q = query + "u.name like '%" + search + "%')" + cont;
flag = 1;
}
else if (radioButton_Apellido.Checked)
{
search = ultraTextEditor_Buscar.Text;
q = query + "u.lastname like '%" + search + "%')" + cont;
flag = 1;
}
else
{
MessageBox.Show("Seleccione una opción para continuar");
}
if (flag == 1)
{
SqlManager.loadDataGridView(dataGridView_Users, q, conn);
dataGridView_Users.Columns[1].Visible = false;
dataGridView_Users.Columns[0].Width = 60;
dataGridView_Users.Columns[0].Visible = true;
dataGridView_Users.Columns[0].DisplayIndex = 14;
if (dataGridView_Users.Columns.Count == 16)
{
dataGridView_Users.Columns.Remove("ColumnEstado");
}
DataGridViewColumn newCol = new DataGridViewColumn(); // add a column to the grid
DataGridViewCell cell = new DataGridViewTextBoxCell(); //Specify which type of cell in this column
newCol.CellTemplate = cell;
newCol.HeaderText = "Estado";
newCol.Name = "ColumnEstado";
newCol.Visible = true;
newCol.Width = 50;
dataGridView_Users.Columns.Add(newCol);
dataGridView_Users.Columns[0].Visible = true;
dataGridView_Users.Columns[0].Width = 100;
rowCount = 0;
String a;
while (rowCount < dataGridView_Users.Rows.Count - 1)
{
a = dataGridView_Users.Rows[rowCount].Cells[1].Value.ToString();
String qry = "SELECT us.isActive,us.isInactive,us.isPassive,us.isRemoved,us.isTesting FROM dbo.userState us WHERE us.id=" + a + ";";
SqlDataReader mr = SqlManager.getQuery(qry, conn);
if (mr.HasRows)
{
mr.Read();
if (mr[0].ToString() == "True")
{
dataGridView_Users.Rows[rowCount].Cells[15].Value = "Activo";
}
else if (mr[1].ToString() == "True")
{
dataGridView_Users.Rows[rowCount].Cells[15].Value = "Inactivo";
}
else if (mr[2].ToString() == "True")
{
dataGridView_Users.Rows[rowCount].Cells[15].Value = "Pasivo";
}
else if (mr[3].ToString() == "True")
{
dataGridView_Users.Rows[rowCount].Cells[15].Value = "Removido";
}
else if (mr[4].ToString() == "True")
{
dataGridView_Users.Rows[rowCount].Cells[15].Value = "A prueba";
}
else
{
dataGridView_Users.Rows[rowCount].Cells[15].Value = "Sin información";
}
}
mr.Close();
rowCount++;
}
}
}
}
