protected void Buscar_Click(object sender, EventArgs e) { panel1.Visible = true; botonExportar.Visible = true; ButtonExp.Visible = false; panel3.Visible = false; if ((lista_depto.SelectedValue.ToString() == "0")) { //MensajeError("Debes de Seleccionar el Departamento"); panel1.Visible = false; panel2.Visible = false; botonExportar.Visible = false; return; } //else //{ String idOrganizationEBS = lista_depto.SelectedValue.ToString(); TablaEmpleados empleadosEbs = new TablaEmpleados(); DataTable table = empleadosEbs.empleadoxdepto(idOrganizationEBS); if (table == null) { MensajeError("Null empleadoxdepto"); return; } gridBuscar.DataSource = table; gridBuscar.DataBind(); panel2.Visible = true; int contadorpos; //panel3.Visible = true; //panel2.Visible = true; //string idplantilla; //---BORRO la CONSULTA ANTERIOR usuario.Text = (string)(Session["Nombre"]); empcritico.Text = ""; empnec.Text = ""; empreconocer.Text = ""; empretener.Text = ""; using (SqlConnection cnxdel = new SqlConnection(connectionString)) { cnxdel.Open(); string query_del = " DELETE FROM EVA_C_GRAFICA " + " WHERE iduser= '******' "; SqlCommand cmdd = new SqlCommand(query_del, cnxdel); SqlDataReader drd = cmdd.ExecuteReader(); } using (SqlConnection cnxdel = new SqlConnection(connectionString)) { cnxdel.Open(); string query_del = " DELETE FROM EVA_D_GRAFICA " + " WHERE usuario= '" + usuario.Text + "' "; SqlCommand cmdd = new SqlCommand(query_del, cnxdel); SqlDataReader drd = cmdd.ExecuteReader(); } //saco la plantillas using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query = "SELECT id_plantilla FROM EVA_PLANTILLAS WHERE (vigencia_plant = '1')"; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { idplantilla.Text = dr["id_plantilla"].ToString(); } } else { idplantilla.Text = "No se encontro plantilla autorizada"; } } // cierre de la conn DataTable dt = new DataTable(); TablaEmpleados empleadosEbs2 = new TablaEmpleados(); DataTable table2 = empleadosEbs2.empleadoxdeptoID(idOrganizationEBS); if (table2 != null) { foreach (DataRow row in table2.Rows) // Loop over the rows. { foreach (var item in row.ItemArray) // Loop over the items. { using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query = " select count(c.id_eva) as total, c.id_eva " + " from EVA_C_EVALUACION as c, EVA_D_EVALUACION as d " + " where c.id_emp='" + item + "' and c.estatus='2' and c.id_eva=d.id_eva and d.id_plantilla='" + idplantilla.Text + "' " + " group by c.id_eva "; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { evaluacionid.Text = dr["id_eva"].ToString(); } } else { evaluacionid.Text = "0"; } } // cierre de la conn if (evaluacionid.Text != "0") { using (SqlConnection cnx = new SqlConnection(connectionString)) { // estatus=2 cuando la evaluacion ha sido liberada por jefe inmediato cnx.Open(); string query = " select {fn IFNULL(SUM(calificacion_val_jefe),0)} AS total_puntaje_valores " + " from EVA_C_EVALUACION as e, EVA_D_EVALUACION as d " + " where e.id_eva='" + evaluacionid.Text + "' and e.estatus='2' and d.id_plantilla='" + idplantilla.Text + "' and e.id_eva=d.id_eva "; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { total_valores.Text = dr["total_puntaje_valores"].ToString(); } } else { total_valores.Text = "0"; } } // cierre de la conn if (total_valores.Text != "0") { using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query = " select {fn IFNULL(SUM(ponderacion_ind_jefe),0)} AS total_puntaje_indicadores " + " from EVA_C_EVALUACION as e, EVA_D_INDICADORES as d " + " where e.id_eva='" + evaluacionid.Text + "' and e.estatus='2' and d.id_plantilla='" + idplantilla.Text + "' " + " and e.id_eva=d.id_eva "; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { total_valores_ind.Text = dr["total_puntaje_indicadores"].ToString(); } } else { total_valores_ind.Text = "No se encontro puntaje indicadores"; } } // cierre de la conn // para sacar la calificacion en Letra using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query2 = " SELECT ponderacion_kpi, calificacion_kpi, descripcion_kpi " + " FROM EVA_C_CALIFICACION_KPI " + " WHERE ponderacion_kpi <= '" + total_valores_ind.Text + "' order by 1 "; SqlCommand cmd2 = new SqlCommand(query2, cnx); SqlDataReader dr2 = cmd2.ExecuteReader(); if (dr2.HasRows) { while (dr2.Read()) { cal_indicador.Text = dr2["calificacion_kpi"].ToString(); } } else { //MensajeError("No se encontro Calificacion"); return; } } // cierre de la conn //--------------PARA GUARDAR LA POSICION EN LA GRAFICA Y QUE POSICION CORRESPONDE-------------------------------------- //string posicion; int contador_ab1, contador_cc1, contador_cd1, contador_ab2, contador_cc2, contador_cd2, contador_ab3, contador_cc3, contador_cd3; contador_ab1 = 0; contador_cc1 = 0; contador_cd1 = 0; contador_ab2 = 0; contador_cc2 = 0; contador_cd2 = 0; contador_ab3 = 0; contador_cc3 = 0; contador_cd3 = 0; if ((Convert.ToInt32(total_valores.Text) >= 1) && ((Convert.ToInt32(total_valores.Text.Trim())) <= 22)) { if (cal_indicador.Text.Trim() == "A" || cal_indicador.Text.Trim() == "B") { posicion.Text = "ab1"; contador_ab1 = contador_ab1 + 1; } if (cal_indicador.Text.Trim() == "C-" || cal_indicador.Text.Trim() == "C") { posicion.Text = "cc1"; contador_cc1 = contador_cc1 + 1; } if (cal_indicador.Text.Trim() == "C+" || cal_indicador.Text.Trim() == "D") { posicion.Text = "cd1"; contador_cd1 = contador_cd1 + 1; } //aqui guardo para tener los datos using (SqlConnection cnx2 = new SqlConnection(connectionString)) { cnx2.Open(); string query = " insert EVA_D_GRAFICA (id_eva, cal_valores, cal_indicadores, posicion, usuario, idempleado) " + " values ('" + evaluacionid.Text + "', '" + Convert.ToInt32(total_valores.Text) + "', '" + cal_indicador.Text + "', '" + posicion.Text + "', '" + usuario.Text + "' , '" + item + "' )"; SqlCommand cmd = new SqlCommand(query, cnx2); SqlDataReader dr = cmd.ExecuteReader(); } } else { if ((Convert.ToInt32(total_valores.Text) >= 23) && (Convert.ToInt32(total_valores.Text.Trim()) <= 50)) { if (cal_indicador.Text.Trim() == "A" || cal_indicador.Text.Trim() == "B") { posicion.Text = "ab2"; contador_ab2 = contador_ab2 + 1; } if (cal_indicador.Text.Trim() == "C-" || cal_indicador.Text.Trim() == "C") { posicion.Text = "cc2"; contador_cc2 = contador_cc2 + 1; } if (cal_indicador.Text.Trim() == "C+" || cal_indicador.Text.Trim() == "D") { posicion.Text = "cd2"; contador_cd2 = contador_cd2 + 1; } //aqui guardo para tener los datos using (SqlConnection cnx2 = new SqlConnection(connectionString)) { cnx2.Open(); string query = " insert EVA_D_GRAFICA (id_eva, cal_valores, cal_indicadores, posicion, usuario, idempleado) " + " values ('" + evaluacionid.Text + "', '" + Convert.ToInt32(total_valores.Text) + "', '" + cal_indicador.Text + "', '" + posicion.Text + "', '" + usuario.Text + "', '" + item + "' )"; SqlCommand cmd = new SqlCommand(query, cnx2); SqlDataReader dr = cmd.ExecuteReader(); } } else { if ((Convert.ToInt32(total_valores.Text) >= 51) && (Convert.ToInt32(total_valores.Text.Trim()) <= 54)) { if (cal_indicador.Text.Trim() == "A" || cal_indicador.Text.Trim() == "B") { posicion.Text = "ab3"; contador_ab3 = contador_ab3 + 1; } if (cal_indicador.Text.Trim() == "C-" || cal_indicador.Text.Trim() == "C") { posicion.Text = "cc3"; contador_cc3 = contador_cc3 + 1; } if (cal_indicador.Text.Trim() == "C+" || cal_indicador.Text.Trim() == "D") { posicion.Text = "cd3"; contador_cd3 = contador_cd3 + 1; } //aqui guardo para tener los datos using (SqlConnection cnx2 = new SqlConnection(connectionString)) { cnx2.Open(); string query = " insert EVA_D_GRAFICA (id_eva, cal_valores, cal_indicadores, posicion, usuario, idempleado) " + " values ('" + evaluacionid.Text + "', '" + Convert.ToInt32(total_valores.Text) + "', '" + cal_indicador.Text + "', '" + posicion.Text + "', '" + usuario.Text + "', '" + item + "') "; SqlCommand cmd = new SqlCommand(query, cnx2); SqlDataReader dr = cmd.ExecuteReader(); } } } } } //--------------------------------------------------------------------------------------------------------------------- } // termina el if de total_valores.Text else { // sino tiene total de valores en 0 //aqui guardo para tener los datos using (SqlConnection cnx2 = new SqlConnection(connectionString)) { cnx2.Open(); string query = " insert EVA_D_GRAFICA (id_eva, cal_valores, cal_indicadores, posicion, usuario) " + " values ('" + evaluacionid.Text + "', '0', '0', '-', '" + usuario.Text + "') "; SqlCommand cmd = new SqlCommand(query, cnx2); SqlDataReader dr = cmd.ExecuteReader(); } } } } } else { MensajeError("Null -empleadoxdeptoID"); return; } // AQUI GUARDO LOS RESULTADOS contadorpos = 0; while (contadorpos <= 8) { if (contadorpos == 0) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('ab1', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 1) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('ab2', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 2) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('ab3', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 3) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('cc1', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 4) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('cc2', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 5) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('cc3', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 6) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('cd1', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 7) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('cd2', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } if (contadorpos == 8) { using (SqlConnection cnx3 = new SqlConnection(connectionString)) { cnx3.Open(); string query3 = " insert EVA_C_GRAFICA (idposicion, total_depto, iduser) " + " values ('cd3', 0, '" + usuario.Text + "') "; SqlCommand cmd3 = new SqlCommand(query3, cnx3); SqlDataReader dr3 = cmd3.ExecuteReader(); } } contadorpos = contadorpos + 1; } using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query2 = " SELECT COUNT(posicion) AS total, posicion " + " FROM EVA_D_GRAFICA " + " WHERE (usuario = '" + usuario.Text + "') " + " GROUP BY posicion ORDER BY 2"; SqlCommand cmd2 = new SqlCommand(query2, cnx); SqlDataReader dr2 = cmd2.ExecuteReader(); if (dr2.HasRows) { while (dr2.Read()) { Txtposicion.Text = dr2["posicion"].ToString(); //total_pos.Text = dr2["total"].ToString(); contadorpos = contadorpos + 1; if (Txtposicion.Text.Trim() == "ab1") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "ab2") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "ab3") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "cc1") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "cc2") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "cc3") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "cd1") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "cd2") { total_pos.Text = dr2["total"].ToString(); } else { if (Txtposicion.Text.Trim() == "cd3") { total_pos.Text = dr2["total"].ToString(); } else { total_pos.Text = "0"; } } } } } } } } } //-- aqui antes de que acabe el while using (SqlConnection cnx2 = new SqlConnection(connectionString)) { cnx2.Open(); string query = " UPDATE EVA_C_GRAFICA set idposicion='" + Txtposicion.Text + "', total_depto='" + Convert.ToInt16(total_pos.Text) + "' , iduser='******' " + " WHERE iduser= '******' and idposicion='" + Txtposicion.Text + "' "; SqlCommand cmd = new SqlCommand(query, cnx2); SqlDataReader dr = cmd.ExecuteReader(); } } // del while } else { //MensajeError("No se encontro Calificacion"); return; } } // cierre de la conn string totalvalores = "0"; using (SqlConnection cnxt = new SqlConnection(connectionString)) { cnxt.Open(); string query2 = " SELECT SUM(cal_valores) AS totalval " + " FROM EVA_D_GRAFICA " + " WHERE (usuario = '" + usuario.Text + "') "; SqlCommand cmd2 = new SqlCommand(query2, cnxt); SqlDataReader drt = cmd2.ExecuteReader(); if (drt.HasRows) { while (drt.Read()) { totalvalores = drt["totalval"].ToString(); } } } if (Convert.ToInt32(totalvalores) != 0) { renderChart(); //para las graficas } else { panel2.Visible = false; } panel3.Visible = false; panel4.Visible = false; LinkBCGral.Visible = false; //} }
protected void Button1_Click(object sender, System.EventArgs e) { MensajeError(string.Empty); MensajeExito(string.Empty); //int graba = 1; String idEmpleadoJDE = lista_empleados.SelectedValue.ToString(); opcestatus.Visible = true; //para banda estrategica osea directores if (id_categoria.Text == "A1" || id_categoria.Text == "A2") { opcestatus.Visible = true; //================================================================================================================================== panel1.Visible = true; botonExportar.Visible = true; botonExportar.Visible = false; if ((lista_empleados.SelectedValue.ToString() == "0")) { //MensajeError("Debes de Seleccionar el Departamento"); panel1.Visible = false; //panel2.Visible = false; botonExportar.Visible = false; return; } //else //{ String idOrganizationEBS = lista_empleados.SelectedValue.ToString(); TablaEmpleados empleadosEbs = new TablaEmpleados(); DataTable table = empleadosEbs.empleadoxdepto(idOrganizationEBS); if (table == null) { MensajeError("Null empleadoxdepto"); return; } gridBuscar.DataSource = table; gridBuscar.DataBind(); //panel2.Visible = true; int contadorpos; //panel3.Visible = true; //panel2.Visible = true; //string idplantilla; //---BORRO la CONSULTA ANTERIOR usuario.Text = (string)(Session["Nombre"]); empcritico.Text = ""; empnec.Text = ""; empreconocer.Text = ""; empretener.Text = ""; //saco la plantillas using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query = "SELECT id_plantilla FROM EVA_PLANTILLAS WHERE (vigencia_plant = '1')"; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { idplantilla.Text = dr["id_plantilla"].ToString(); } } else { idplantilla.Text = "No se encontro plantilla autorizada"; } } // cierre de la conn DataTable dt = new DataTable(); TablaEmpleados empleadosEbs2 = new TablaEmpleados(); DataTable table2 = empleadosEbs2.empleadoxdeptoID(idOrganizationEBS); if (table2 != null) { foreach (DataRow row in table2.Rows) // Loop over the rows. { foreach (var item in row.ItemArray) // Loop over the items. { using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query = " select count(c.id_eva) as total, c.id_eva " + " from EVA_C_EVALUACION as c, EVA_D_EVALUACION as d " + " where c.id_emp='" + item + "' and c.estatus='2' and c.id_eva=d.id_eva and d.id_plantilla='" + idplantilla.Text + "' " + " group by c.id_eva "; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { evaluacionid.Text = dr["id_eva"].ToString(); } } else { evaluacionid.Text = "0"; } } // cierre de la conn if (evaluacionid.Text != "0") { using (SqlConnection cnx = new SqlConnection(connectionString)) { // estatus=2 cuando la evaluacion ha sido liberada por jefe inmediato cnx.Open(); string query = " select {fn IFNULL(SUM(calificacion_val_jefe),0)} AS total_puntaje_valores " + " from EVA_C_EVALUACION as e, EVA_D_EVALUACION as d " + " where e.id_eva='" + evaluacionid.Text + "' and e.estatus='2' and d.id_plantilla='" + idplantilla.Text + "' and e.id_eva=d.id_eva "; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { total_valores.Text = dr["total_puntaje_valores"].ToString(); } } else { total_valores.Text = "0"; } } // cierre de la conn if (total_valores.Text != "0") { using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query = " select {fn IFNULL(SUM(ponderacion_ind_jefe),0)} AS total_puntaje_indicadores " + " from EVA_C_EVALUACION as e, EVA_D_INDICADORES as d " + " where e.id_eva='" + evaluacionid.Text + "' and e.estatus='2' and d.id_plantilla='" + idplantilla.Text + "' " + " and e.id_eva=d.id_eva "; SqlCommand cmd = new SqlCommand(query, cnx); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { total_valores_ind.Text = dr["total_puntaje_indicadores"].ToString(); } } else { total_valores_ind.Text = "No se encontro puntaje indicadores"; } } // cierre de la conn // para sacar la calificacion en Letra using (SqlConnection cnx = new SqlConnection(connectionString)) { cnx.Open(); string query2 = " SELECT ponderacion_kpi, calificacion_kpi, descripcion_kpi " + " FROM EVA_C_CALIFICACION_KPI " + " WHERE ponderacion_kpi <= '" + total_valores_ind.Text + "' order by 1 "; SqlCommand cmd2 = new SqlCommand(query2, cnx); SqlDataReader dr2 = cmd2.ExecuteReader(); if (dr2.HasRows) { while (dr2.Read()) { cal_indicador.Text = dr2["calificacion_kpi"].ToString(); } } else { //MensajeError("No se encontro Calificacion"); return; } } // cierre de la conn } } } } } else { MensajeError("Null -empleadoxdeptoID"); return; } //===============================================================================================================HASTA AQUI } else { opcestatus.Visible = false; } }