/**<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++; } } } }