/*************************************************************************************************************************************************************/
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 el evento de clic en el boton consultar/actualizar rama.
* Toma el id de la fila del GridLayout y segun eso realiza la busqueda de la rama que coincida con el id.
* Muestra los datos en una nueva ventana</summary> */
/**<return> No devuelve nada </return>*/
/**<param name="sender">Objeto</param>*/
/**<param name="e">Evento</param>*/
private void btnConsult_Click(object sender, EventArgs e)
{
Int32 selectedRowCount = dataGridView_Branch.Rows.GetRowCount(DataGridViewElementStates.Selected);
if (selectedRowCount > 0)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
int numFila;
try{
String fila = this.dataGridView_Branch.SelectedRows[selectedRowCount - 1].Index.ToString();
numFila = Int32.Parse(fila);
string idRama = this.dataGridView_Branch[0, numFila].Value.ToString();
int idBranch = Int32.Parse(idRama);
sqlString = "select name,shortName, isEnable, created from branch where id='" + idBranch + "';";
DR = SqlManager.getQuery(sqlString, conn);
Branch br = new Branch();
br.setId(idBranch);
if (DR != null)
{
while (DR.Read())
{
br.setName((String)(DR["name"]));
br.setShortName((String)(DR["shortName"]));
br.setIsEnable((bool)(DR["isEnable"]));
br.setCreated((DateTime)(DR["created"]));
}
}
DR.Close();
Form_uploadDataBranch consulta = new Form_uploadDataBranch(br, conn);
consulta.Show();
}
catch (Exception ex)
{
MessageBox.Show("Disculpe las molestias.Se produjo un error inesperado:\n " + ex);
}
}
else
{
MessageBox.Show("Para consultar debe elegir una rama de la lista");
}
}
/*************************************************************************************************************************************************************/
private void cmbEncargadoR_BeforeDropDown(object sender, CancelEventArgs e)
{
ArrayList encargados = new ArrayList();
if (this.cmbRamaEncargadaR.Text == "")
{
MessageBox.Show("Seleccione La Rama Encargada", "Rama Ecargada Inválida", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
this.cmbEncargadoR.Items.Clear();
//sqlString = "select name + ' ' + lastname as 'fullName' from SACMEDB.dbo.users where (SACMEDB.dbo.users.id = (select userId from userBranch where branchId = (select SACMEDB.dbo.branch.id from branch where name = '" + this.cmbRamaEncargadaR.Text + "')))";
sqlString = "select userId from userBranch where branchId = (select id from branch where name = '" + this.cmbRamaEncargadaR.Text + "')";
try
{
DR = SqlManager.getQuery(sqlString, conn);
if (DR != null)
{
while (DR.Read())
{
encargados.Add(DR["userId"].ToString());
idEncargado.Items.Add(DR["userId"].ToString());
}
}
DR.Close();
for (int i = 0; i < encargados.Count; i++)
{
DR = SqlManager.getQuery("select name +' '+ lastname as 'fullName' from users where id = '" + encargados[i] + "'", conn);
if (DR != null)
{
while (DR.Read())
{
this.cmbEncargadoR.Items.Add(DR["fullName"]);
}
}
DR.Close();
}
}
catch (DbException ex) { MessageBox.Show("No se pudo Obtener El Encargado:\n " + ex); }
}
}
public void addToDataGridRemover(int row, int column, bool b)
{
this.row = row;
if (row != -1)
{
string query;
if (b)
{
query = "delete from organizersTmp where name = '" + this.dataGridRemover.Rows[row].Cells[1].Value.ToString() + "'";
}
else
{
query = "delete from usersTmp where id = '" + this.dataGridRemover.Rows[row].Cells[2].Value.ToString() + "'";
}
SqlManager.executeQuery(query, conn);
fillDatagridAgregar();
fillDatagridRemover();
}
}
/*************************************************************************************************************************************************************/
public void fillComboBox(UltraComboEditor u, string sqlString, string campo)
{
u.Items.Clear();
try
{
conn.getConnection();
DR = SqlManager.getQuery(sqlString, conn);
if (DR != null)
{
while (DR.Read())
{
u.Items.Add(DR[campo]);
}
}
DR.Close();
}
catch (DbException ex)
{
MessageBox.Show("No se pudo Obtener las Ramas:\n " + ex);
}
}
private void addItemtoComboEditor(Infragistics.Win.UltraWinEditors.UltraComboEditor uce, String query)
{
String item;
SqlDataReader myreader = null;
myreader = SqlManager.getQuery(query, conn);
while (uce.Items.Count > 0)
{
uce.Items.RemoveAt(0);
}
if (myreader.HasRows)
{
while (myreader.Read())
{
item = myreader[0].ToString();
uce.Items.Add(item);
}
}
myreader.Close();
}
/**<summary>Metodo que maneja el evento click del boton Actualizar. Actualiza los datos
* de la rama en la base de datos</summary> */
/**<return> No devuelve nada </return>*/
/**<param name="sender">Objeto</param>*/
/**<param name="e">Evento</param>*/
private void btnSave_Click(object sender, EventArgs e)
{
try {
conn.getConnection();
Branch br1 = new Branch();
br1.setName(this.txtNameBranch_up.Text);
br1.setShortName(this.txtShorNamet_up.Text);
String state = this.cmbState_up.Value.ToString();
if (state == "Activado")
{
br1.setIsEnable(true);
}
else
{
br1.setIsEnable(false);
}
br1.setCreated((DateTime)this.dateCreateBranch_up.Value);
int idB = br.getId();
sqlString = "update branch set name='" + br1.getName() + "', shortName='" + br1.getShortName() + "', isEnable='" + br1.getIsEnable() + "'" +
"where id='" + idB + "'";
bool result = SqlManager.executeQuery(sqlString, conn);
if (result)
{
MessageBox.Show("Datos actualizados con exito ");
this.habilitarDeshabilitarCampos(false);
}
else
{
MessageBox.Show("No se pudo guardar los datos");
}
}
catch (Exception ex)
{
MessageBox.Show("No se pudo actualizar la rama:\n " + ex);
}
}
/**<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);
}
}
/*************************************************************************************************************************************************************/
private bool insertEvent(Event e)
{
try
{
conn.getConnection();
this.sqlString = "insert into event(name,description,responsible,duration,start,eventTypeId,branchId,createdBy)" +
"VALUES('" + e.getName() + "',"
+ "'" + e.getDescription() + "',"
+ "'" + e.getResponsible() + "',"
+ "'" + e.getDuration() + "',"
+ "'" + dateToSQL(e.getStart()) + "',"
+ "'" + e.getEventTypeId() + "',"
+ "'" + e.getBranchId() + "',"
+ "'" + e.getCreatedBy() + "')";
SqlManager.executeQuery(sqlString, conn);
}
catch (DbException ex)
{
MessageBox.Show("No se pudo Agregar evento:\n " + ex);
return(false);
}
return(true);
}
/*************************************************************************************************************************************************************/
private void btnModificar_Click(object sender, EventArgs e)
{
ArrayList evento = getSelectedItem(this.dataGridView_Eventos);
if (SelectedItems)
{
string idEvento = null;
this.formMain.Enabled = false;
if (evento.Count > 0)
{
sqlString = "select id from [SACMEDB].[dbo].[event] where name = '" + evento[0] + "' and [SACMEDB].[dbo].[event].[description] = '" + evento[1] + "'";
try
{
DR = SqlManager.getQuery(sqlString, conn);
if (DR != null)
{
while (DR.Read())
{
idEvento = DR["id"].ToString();
}
}
DR.Close();
}
catch (DbException ex)
{
MessageBox.Show("No se pudo Obtener el Id del Evento:\n " + ex);
}
formMain.Enabled = false;
Form_modifyEvent modify = new Form_modifyEvent(idEvento, formMain, this, DR, conn);
modify.Show();
}
else
{
MessageBox.Show("No se encontró el evento a buscar:\n ");
}
}
}
private void ultraButton_Guardar_Click(object sender, EventArgs e)
{
pass = ultraTextEditor_Contrasenia.Text;
confirPass = ultraTextEditor_ConfirmContrasenia.Text;
if (pass == "" || confirPass == "")
{
MessageBox.Show("Ingrese correctamente los valores correspondientes");
}
else if (pass != confirPass)
{
MessageBox.Show("Contraseñas no coinciden");
}
else
{
this.query = "UPDATE users SET password='******' WHERE username='******';";
if (SqlManager.executeQuery(query, conn))
{
Form_main formmain = new Form_main(ini, conn, user);
this.Hide();
formmain.Show();
}
}
}
private void btnGuardarM_Click(object sender, EventArgs e)
{
bool cambiosValidos = false;
int j = 0;
if (this.checkBoxFechaInicio.Checked)
{
if (Convert.ToDateTime(fechaInicioOriginal).CompareTo(DateTime.Today) < 0)
{
MessageBox.Show("No puede seleccionar una fecha del pasado", "Fecha Inválida", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (Convert.ToDateTime(fechaInicioOriginal).CompareTo(DateTime.Today) == 0)
{
MessageBox.Show("El evento es HOY, no puede cambiar la fecha", "Fecha Inválida", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
cambiosValidos = true;
}
}
if (cambiosValidos)
{
if (checkBoxFechaInicio.Checked)
{
sqlString = "update SACMEDB.dbo.event set start = '" + formEvent.dateToSQL(this.dateFechaInicioM.DateTime) + "' where id = '" + idEvento + "'";
SqlManager.executeQuery(sqlString, conn);
this.fechaInicioOriginal = formEvent.dateToSQL(dateFechaInicioM.DateTime);
this.dateFechaInicioM.DateTime = Convert.ToDateTime(fechaInicioOriginal);
}
}
if (this.checkBoxEncargado.Checked)
{
if (this.cmbEncargadoM.Text == "")
{
MessageBox.Show("No ha seleccionado a un encargado", "Encargado Sin especificar", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
cambiosValidos = true;
}
}
if (cambiosValidos)
{
if (checkBoxEncargado.Checked)
{
this.cmbEncargadosId.SelectedIndex = this.cmbEncargadoM.SelectedIndex;
sqlString = "update SACMEDB.dbo.event set responsible = '" + cmbEncargadosId.SelectedItem.ToString() + "' where id = '" + idEvento + "'";
SqlManager.executeQuery(sqlString, conn);
}
}
sqlString = "delete from eventParticipant where eventId = '" + idEvento + "'; delete from eventOrganizer where eventId = '" + idEvento + "'";
for (int i = 0; i < this.cmbOrganizadoresM.Items.Count; i++)
{
sqlString = "insert into [SACMEDB].[dbo].[eventOrganizer] (eventId,organizerId) values ((select id from [SACMEDB].[dbo].[event] where name = '" + this.txtNombreM.Text + "' and description = '" + this.txtDescripcionM.Text + "'),(select id from organizers where name = '" + this.cmbOrganizadoresM.Items[i].ToString() + "'))";
SqlManager.executeQuery(sqlString, conn);
j++;
}
for (int i = 0; i < this.cmbIdParticipantesM.Items.Count; i++)
{
sqlString = "insert into [SACMEDB].[dbo].[eventParticipant] (userId,eventId,rol) values ('" + this.cmbIdParticipantesM.Items[i].ToString() + "',(select id from [SACMEDB].[dbo].[event] where name = '" + this.txtNombreM.Text + "' and description = '" + this.txtDescripcionM.Text + "'),'Participante Evento " + this.txtNombreM.Text + "') ";
SqlManager.executeQuery(sqlString, conn);
j++;
}
if (cambiosValidos)
{
MessageBox.Show("Cambios Guardados");
}
if (j > 0)
{
MessageBox.Show("Organizadores o Participantes Actualizados");
}
}
public void fillDatagridAgregar()
{
SqlManager.loadDataGridView(dataGridAgregar, sqlStringAdd, conn);
}
/*************************************************************************************************************************************************************/
private void btnGuardarEvento_Click(object sender, EventArgs e)
{
int branchId = 0;
int eventTypeId = 0;
if (isValidRegistryForm())
{
try
{
conn.getConnection();
DR = SqlManager.getQuery("select id from SACMEDB.dbo.branch where name = '" + this.cmbRamaEncargadaR.Text + "'", conn);
if (DR != null)
{
while (DR.Read())
{
branchId = Convert.ToInt32(DR["id"].ToString());
}
}
DR.Close();
}
catch (DbException ex) { MessageBox.Show("No Se Pudo Obtener El Id De La Rama:\n " + ex); }
try
{
conn.getConnection();
DR = SqlManager.getQuery("select id from [SACMEDB].[dbo].[eventType] where name = '" + this.cmbTipoR.Text + "'", conn);
if (DR != null)
{
while (DR.Read())
{
eventTypeId = Convert.ToInt32(DR["id"].ToString());
}
}
DR.Close();
}
catch (DbException ex) { MessageBox.Show("No Se Pudo Obtener El Id De La Rama:\n " + ex); }
Event ev = new Event();
ev.setName(this.txtNombreR.Text);
ev.setDescription(this.txtDescripcionR.Text);
this.idEncargado.SelectedIndex = this.cmbEncargadoR.SelectedIndex;
ev.setResponsible(idEncargado.SelectedItem.ToString());
ev.setDuration(this.NumericEditorDuracionR.Value.ToString() + " " + this.cmbDuracion.Text);
ev.setStart((DateTime)this.dateFechaDeInicioR.Value);
ev.setEventTypeId(eventTypeId);
ev.setBranchId(branchId);
ev.setCreatedBy(eventCreator);
insertEvent(ev);
for (int i = 0; i < this.cmbOrganizadorR.Items.Count; i++)
{
sqlString = "insert into [SACMEDB].[dbo].[eventOrganizer] (eventId,organizerId) values ((select id from [SACMEDB].[dbo].[event] where name = '" + this.txtNombreR.Text + "' and description = '" + this.txtDescripcionR.Text + "'),(select id from organizers where name = '" + this.cmbOrganizadorR.Items[i].ToString() + "'))";
SqlManager.executeQuery(sqlString, conn);
}
for (int i = 0; i < this.cmbParticipantesR.Items.Count; i++)
{
sqlString = "insert into [SACMEDB].[dbo].[eventParticipant] (userId,eventId,rol) values ('" + this.idParticipants.Items[i].ToString() + "',(select id from [SACMEDB].[dbo].[event] where name = '" + this.txtNombreR.Text + "' and description = '" + this.txtDescripcionR.Text + "'),'Participante Evento " + this.txtNombreR.Text + "') ";
SqlManager.executeQuery(sqlString, conn);
}
clearRegistryForm();
MessageBox.Show("Evento Creado Satisfactoriamente");
}
}
public void fillDatagridRemover()
{
SqlManager.loadDataGridView(dataGridRemover, sqlStringRemove, conn);
}
private void ultraButton_Ingresar_Click(object sender, EventArgs e)
{
int flag = 0;
String query = "";
SqlDataReader user = null;
Form_usersPassword fm;
this.usuario = ultraTextEditor_Usuario.Text;
this.contrasenia = ultraTextEditor_Contrasenia.Text;
if (this.usuario == "" || this.contrasenia == "")
{
MessageBox.Show("Ingrese correctamente sus datos");
}
else
{
query = "SELECT id FROM users WHERE username='******';";
user = SqlManager.getQuery(query, sacmeDbConn);
if (!user.HasRows)
{
MessageBox.Show("Ud no pertenece al Sistema");
user.Close();
}
else
{
user.Close();
if (ultraCheckEditor_PrimeraVez.Checked)
{
DirectorioEspol.directorioEspolSoapClient wservice = new DirectorioEspol.directorioEspolSoapClient();
if (wservice.autenticacion(usuario, contrasenia))
{
flag = 1;
}
else
{
MessageBox.Show("Ud no pertenece a la ESPOL, no podrá ingresar a SACME");
}
}
else
{
query = "SELECT id FROM users WHERE username='******' AND password='******';";
user = SqlManager.getQuery(query, sacmeDbConn);
if (!user.HasRows)
{
MessageBox.Show("El usuario o contraseña es inválido. Intente nuevamente");
user.Close();
}
else
{
user.Close();
Form_main formmain = new Form_main(this, sacmeDbConn, this.usuario);
this.Hide();
formmain.Show();
}
user.Close();
}
if (flag == 1)
{
fm = new Form_usersPassword(ultraTextEditor_Usuario.Text, sacmeDbConn, this);
this.Hide();
fm.Show();
}
}
}
}
public Form_academicControlRegistry(Connector conn, User userSession, String id, String typeAC)
{
String sql;
this.conn = conn;
InitializeComponent();
this.userSession = userSession;
this.referenceId = id;
SqlDataReader DR;
if (typeAC == "course")
{
this.typeAC = typeAC;
ultraTabControl1.SelectedTab = ultraTabPageControl2.Tab;
ultraTabPageControl1.Enabled = false;
sql = "SELECT id,name,gainedPoints,academicTerm,institution,duration,userId FROM " +
"dbo.course WHERE id=" + id;
try
{
DR = SqlManager.getQuery(sql, conn);
if (DR != null)
{
DR.Read();
ultraTextEditor_nameofcourse.Value = DR["name"].ToString();
ultraTextEditor_institutioncourse.Value = DR["institution"].ToString();
ultraTextEditor_pointgetcourse.Value = DR["gainedPoints"].ToString();
String acTerm = DR["academicTerm"].ToString();
String duration = DR["duration"].ToString();
String[] academicT = acTerm.Split(' ', '-');
ultraTextEditor_anio1course.Value = academicT[1];
ultraTextEditor_anio2course.Value = academicT[2];
ultraComboEditor_termcurso.SelectedText = academicT[0];
String[] timeDuration = duration.Split(' ');
ultraComboEditor_typedurationcourse.Value = timeDuration[1];
numericUpDown_durationcountcourse.Value = Int32.Parse(timeDuration[0]);
ultraTextEditor_searchcourse.Value = DR["userId"].ToString();
radioButton_cedcourse.Checked = true;
}
DR.Close();
}
catch (DbException ex)
{
MessageBox.Show("No se pudo Obtener el Id del Evento:\n " + ex);
}
ultraButton_savecourse.Text = "ACTUALIZAR";
ultraButton_savecourse.Size = new Size(92, 24);
}
else if (typeAC == "espol")
{
this.typeAC = typeAC;
ultraTabControl1.SelectedTab = ultraTabPageControl1.Tab;
ultraTabPageControl2.Enabled = false;
sql = "SELECT id,userId,gainedPoints,totalPoints,academicTerm,espolAverage,observation " +
"FROM dbo.academicPerformance WHERE id=" + id;
try
{
DR = SqlManager.getQuery(sql, conn);
if (DR != null)
{
DR.Read();
radioButton_ced.Checked = true;
ultraTextEditor_search.Text = DR["userId"].ToString();
String acTerm = DR["academicTerm"].ToString();
String[] academicT = acTerm.Split(' ', '-');
ultraTextEditor_anio1.Value = academicT[1];
ultraTextEditor_anio2.Value = academicT[2];
ultraComboEditor_term.SelectedText = academicT[0];
ultraTextEditor_gainedPoints.Text = DR["gainedPoints"].ToString();
bool parse = Int32.TryParse(ultraTextEditor_gainedPoints.Text, out gainedPointsLast);
ultraTextEditor_espolAvg.Text = DR["espolAverage"].ToString();
ultraTextEditor_observation.Text = DR["observation"].ToString();
}
DR.Close();
}
catch (DbException ex)
{
MessageBox.Show("No se pudo Obtener el Id del Evento:\n " + ex);
}
ultraButton_saveEspol.Text = "ACTUALIZAR";
ultraButton_saveEspol.Size = new Size(92, 24);
}
}
private void ultraButton2_Click(object sender, EventArgs e)
{
String academicTerm = null, observation = null, espolAvg = null, ced = null;
int gainedPoints = 0, totalPoints = 0, totalPoints_update = 0;
float espolAvg_float;
academicTerm = ultraComboEditor_term.Text + " " + ultraTextEditor_anio1.Text + "-" + ultraTextEditor_anio2.Text;
ced = ultraTextEditor_search.Text;
observation = ultraTextEditor_observation.Text;
espolAvg = ultraTextEditor_espolAvg.Text;
bool parse = Int32.TryParse(ultraTextEditor_gainedPoints.Text, out gainedPoints);
if (false == parse)
{
MessageBox.Show("Formato puntos ganados incorrecto.\nDebe ser un entero.");
}
else
{
String qTotalP = "SELECT acper.totalPoints FROM dbo.academicPerformance acper WHERE acper.userId='" + ced + "'" +
"AND acper.inserted = (SELECT MAX(ap.inserted) FROM dbo.academicPerformance ap WHERE ap.userId='" + ced + "');";
SqlDataReader myReader = SqlManager.getQuery(qTotalP, conn);
if (myReader.HasRows)
{
myReader.Read();
totalPoints = Int32.Parse(myReader[0].ToString());
totalPoints_update = totalPoints - gainedPointsLast + gainedPoints;
totalPoints = totalPoints + gainedPoints;
}
else
{
totalPoints = gainedPoints;
}
myReader.Close();
}
parse = float.TryParse(espolAvg, NumberStyles.Any, CultureInfo.InvariantCulture, out espolAvg_float);
if (parse == true)
{
String insertedBy = null;
if (ultraButton_saveEspol.Text == "GUARDAR")
{
String burnAPquery = "INSERT INTO dbo.academicPerformance (userId,academicTerm,gainedPoints,totalPoints,espolAverage,observation,insertedBy)" +
"VALUES ('" + ced + "','" + academicTerm + "'," + gainedPoints + "," + totalPoints + "," + espolAvg_float + ",'" + observation + "','" + insertedBy + "');";
bool burn = SqlManager.executeQuery(burnAPquery, conn);
if (burn)
{
MessageBox.Show("Registro se ha guardado exitosamente.");
this.Close();
}
else
{
MessageBox.Show("Su registro no ha podido ser guardado.");
}
}
else if (ultraButton_saveEspol.Text == "ACTUALIZAR")
{
String updateAPquery = "UPDATE dbo.academicPerformance SET userId='" + ced + "',academicTerm='" + academicTerm + "',gainedPoints=" + gainedPoints + ",totalPoints=" + totalPoints_update + ",espolAverage=" + espolAvg_float + ",observation='" + observation + "' " +
"WHERE id=" + referenceId;
MessageBox.Show(updateAPquery);
bool update = SqlManager.executeQuery(updateAPquery, conn);
if (update)
{
MessageBox.Show("Registro ha sido actualizado exitosamente.");
this.Close();
}
else
{
MessageBox.Show("Su registro no ha podido ser actualizado.");
}
}
}
else
{
MessageBox.Show("El formato ingresado de promedio ESPOL es incorrecto.");
}
}
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++;
}
}
}
}