private void RecuperarInformacion(string id)
{
SqlDataSource1.SelectCommand = "SELECT D.idDesembolso, D.fecha, D.monto, TD.nombre FROM Desembolso D "
+ " INNER JOIN TipoDesembolso TD ON D.idTipoDesembolso = TD.idTipoDesembolso INNER JOIN Ciclo C ON D.idCiclo = C.idCiclo"
+ " WHERE C.idBecario = '" + id + "' ORDER BY fecha DESC ";
DGV.DataBind();
}
protected void RecuperarInformacion(string id)
{
//Consulta para obtener información del becario
string sql = "SELECT CONCAT(I.apellidos, ', ', I.nombres) AS[Becario], N.nombre AS[Nivel], C.nombre AS[Carrera],"
+ " U.nombre AS[Universidad], P.nombre AS[Programa],"
+ " B.fechaInicio AS[InicioBeca], B.fechaFin AS[FinBeca] FROM Becario B"
+ " INNER JOIN ProgramaBecas P ON B.idPrograma = P.idPrograma"
+ " INNER JOIN Universidad U ON B.idUniversidad = U.idUniversidad"
+ " INNER JOIN Carrera C ON B.idCarrera = C.idCarrera"
+ " INNER JOIN NivelEducativo N ON B.idNivelEducativo = N.idNivelEducativo"
+ " INNER JOIN InformacionPersonal I ON B.idInformacion = I.idInformacion"
+ " WHERE B.idBecario = '" + id + "'";
SqlDataReader reader = DBConnection.GetData(sql);
reader.Read();
nombreB.InnerHtml = "Nombre: " + reader["Becario"];
nivelB.InnerHtml = "Nivel Educativo: " + reader["Nivel"];
carreraB.InnerHtml = "Carrera: " + reader["Carrera"];
universidadB.InnerHtml = "Universidad: " + reader["Universidad"];
programaB.InnerHtml = "Programa de Beca: " + reader["Programa"];
fechaInicioB.InnerHtml = "Inicio de Beca: " + String.Format("{0:yyyy/MM/dd}", reader["InicioBeca"]);
fechaFinB.InnerHtml = "Fin de Beca: " + String.Format("{0:yyyy/MM/dd}", reader["FinBeca"]);
reader.Close();
//Obtenemos información y la asociamos al datagridview
SqlDataSource1.SelectCommand = "SELECT TI.nombre AS[TipoIncidente], TI.descripcion AS[descripcion], BI.idBitacora, BI.fechaAplicacion, BI.descripcion AS[detalles] FROM BitacoraIncidentes BI"
+ " INNER JOIN TipoIncidente TI ON BI.idTipoIncidente = TI.idTipoIncidente"
+ " WHERE BI.idBecario = '" + id + "'";
DGV.DataBind();
}
private void RecuperarInformacion(string id)
{
//Consulta para obtener información del becario
string sql = "SELECT CONCAT(I.apellidos, ', ', I.nombres) AS[Becario], N.nombre AS[Nivel], C.nombre AS[Carrera],"
+ " U.nombre AS[Universidad], P.nombre AS[Programa],"
+ " B.fechaInicio AS[InicioBeca], B.fechaFin AS[FinBeca] FROM Becario B"
+ " INNER JOIN ProgramaBecas P ON B.idPrograma = P.idPrograma"
+ " INNER JOIN Universidad U ON B.idUniversidad = U.idUniversidad"
+ " INNER JOIN Carrera C ON B.idCarrera = C.idCarrera"
+ " INNER JOIN NivelEducativo N ON B.idNivelEducativo = N.idNivelEducativo"
+ " INNER JOIN InformacionPersonal I ON B.idInformacion = I.idInformacion"
+ " WHERE B.idBecario = '" + id + "'";
SqlDataReader reader = DBConnection.GetData(sql);
reader.Read();
nombreB.InnerHtml = "Nombre: " + reader["Becario"];
nivelB.InnerHtml = "Nivel Educativo: " + reader["Nivel"];
carreraB.InnerHtml = "Carrera: " + reader["Carrera"];
universidadB.InnerHtml = "Universidad: " + reader["Universidad"];
programaB.InnerHtml = "Programa de Beca: " + reader["Programa"];
fechaInicioB.InnerHtml = "Inicio de Beca: " + String.Format("{0:yyyy/MM/dd}", reader["InicioBeca"]);
fechaFinB.InnerHtml = "Fin de Beca: " + String.Format("{0:yyyy/MM/dd}", reader["FinBeca"]);
reader.Close();
//Obtenemos información y la ponemos en datagridview
SqlDataSource1.SelectCommand = "SELECT anio AS[Anio], idCiclo AS [idCiclo], nCiclo AS [nCiclo], evidenciaNotas AS [Aprobado]"
+ " FROM Ciclo WHERE idBecario = '" + id + "'; ";
DGV.DataBind();
}
protected void btnBuscar_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.SelectCommand = "SELECT InformacionPersonal.nombres, InformacionPersonal.apellidos, ProgramaBecas.nombre, Becario.idBecario FROM Becario INNER JOIN InformacionPersonal ON Becario.idInformacion = InformacionPersonal.idInformacion INNER JOIN ProgramaBecas ON Becario.idPrograma = ProgramaBecas.idPrograma WHERE " + cmbBuscador.SelectedValue + " LIKE '%" + txtBuscar.Text + "%'";
DGV.DataBind();
}
catch (Exception err)
{
SqlDataSource1.SelectCommand = "SELECT InformacionPersonal.nombres, InformacionPersonal.apellidos, ProgramaBecas.nombre, Becario.idBecario FROM Becario INNER JOIN InformacionPersonal ON Becario.idInformacion = InformacionPersonal.idInformacion INNER JOIN ProgramaBecas ON Becario.idPrograma = ProgramaBecas.idPrograma";
DGV.DataBind();
}
}
protected void UploadButton_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile && Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
try
{
_DTFinal_Cashflow = new DataTable();
using (var excel = new ExcelPackage(FileUpload1.PostedFile.InputStream))
{
//var tbl = new DataTable();
var ws = excel.Workbook.Worksheets.First();
var hasHeader = true; // adjust accordingly
// add DataColumns to DataTable
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
_DTFinal_Cashflow.Columns.Add(hasHeader ? firstRowCell.Text
: String.Format("Column {0}", firstRowCell.Start.Column));
}
// add DataRows to DataTable
int startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = _DTFinal_Cashflow.NewRow();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
_DTFinal_Cashflow.Rows.Add(row);
}
_DTFinal_Cashflow.Columns[0].ColumnName = "CashflowItem";
_DTFinal_Cashflow.Columns[1].ColumnName = "CashflowValue";
ViewState["varDataTable"] = _DTFinal_Cashflow;
DGV.DataSource = _DTFinal_Cashflow;
DGV.DataBind();
RetouchGridView();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "err_msg", "bootbox.alert({message:'<strong>There is an error:</strong> " + ex.Message.Replace("'", "\\'") + "', title:'Oops!'});", true);
}
}
}
private void BindDataCashFlow(int batchid)
{
try
{
log4net.Config.XmlConfigurator.Configure();
log.DebugFormat("BindDataCashFlow() Called.. Parameter: batchid={0}", batchid);
_DTFinal_Cashflow = new DataTable();
_DTFinal_Cashflow = clsCashflow.GetDataCashflow(batchid);
DGV.DataSource = _DTFinal_Cashflow;
DGV.DataBind();
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "err_msg", "bootbox.alert({message:'<strong>There is an error:</strong> " + ex.Message.Replace("'", "\\'") + "', title:'Oops!'});", true);
}
}
protected void btnBuscar_Click(object sender, EventArgs e)
{//Proceso para buscar
string filtro = "";
try {
if (cmbBuscador.SelectedValue.Equals("nombre"))
{
filtro = "I.nombres";
}
else if (cmbBuscador.SelectedValue.Equals("apellido"))
{
filtro = "I.apellidos";
}
else if (cmbBuscador.SelectedValue.Equals("programa"))
{
filtro = "P.nombre";
}
else if (cmbBuscador.SelectedValue.Equals("codigo"))
{
filtro = "B.idBecario";
}
//if () {
SqlDataSource1.SelectCommand = "SELECT C.idCiclo, C.anio, nCiclo, CONCAT(I.nombres, ' ', I.apellidos) AS [NombreBecario], P.nombre AS [Programa],"
+ " B.idBecario AS[CodigoB] FROM Ciclo C"
+ " INNER JOIN Becario B ON C.idBecario = B.idBecario"
+ " INNER JOIN ProgramaBecas P ON B.idPrograma = P.idPrograma"
+ " INNER JOIN InformacionPersonal I ON B.idInformacion = I.idInformacion"
+ " WHERE C.evidenciaNotas = 0 AND " + filtro + " LIKE '%" + txtBuscar.Text + "%' "
+ " ORDER BY C.anio, C.nCiclo, [NombreBecario]";
DGV.DataBind();
}
catch (Exception err) {
SqlDataSource1.SelectCommand = "SELECT C.idCiclo, C.anio, nCiclo, CONCAT(I.nombres, ' ', I.apellidos) AS[NombreBecario],"
+ " P.nombre AS[Programa], B.idBecario AS[CodigoB] FROM Ciclo C"
+ " INNER JOIN Becario B ON C.idBecario = B.idBecario"
+ " INNER JOIN ProgramaBecas P ON B.idPrograma = P.idPrograma"
+ " INNER JOIN InformacionPersonal I ON B.idInformacion = I.idInformacion"
+ " WHERE C.evidenciaNotas = 0 ORDER BY C.anio, C.nCiclo, [NombreBecario]";
DGV.DataBind();
}
}
protected void btnBuscar_Click(object sender, EventArgs e)
{
string filtro = "";
try
{
if (int.Parse(cmbBuscador.SelectedValue) == 0)
{
filtro = "nombres";
}
else if (int.Parse(cmbBuscador.SelectedValue) == 1)
{
filtro = "apellidos";
}
else if (int.Parse(cmbBuscador.SelectedValue) == 2)
{
filtro = "PB.nombre";
}
else if (int.Parse(cmbBuscador.SelectedValue) == 3)
{
filtro = "idBecario";
}
//if () {
SqlDataSource1.SelectCommand = "SELECT C.idCiclo, C.anio, nCiclo, INF.nombres, INF.apellidos, PB.nombre AS [programa], B.idBecario FROM Becario B "
+ " INNER JOIN Ciclo C ON B.idBecario = C.idBecario"
+ " INNER JOIN InformacionPersonal INF ON B.idInformacion = INF.idInformacion"
+ " INNER JOIN ProgramaBecas PB ON B.idPrograma = PB.idPrograma WHERE C.evidenciaNotas = 1 AND C.bloqueado = 0 AND " + filtro + " LIKE '%" + txtBuscar.Text + "%' ORDER BY C.nCiclo DESC; ";
DGV.DataBind();
}
catch (Exception err)
{
SqlDataSource1.SelectCommand = "SELECT C.idCiclo, C.anio, nCiclo, INF.nombres, INF.apellidos, PB.nombre AS [programa], B.idBecario FROM Becario B"
+ " INNER JOIN Ciclo C ON B.idBecario = C.idBecario"
+ " INNER JOIN InformacionPersonal INF ON B.idInformacion = INF.idInformacion"
+ " INNER JOIN ProgramaBecas PB ON B.idPrograma = PB.idPrograma WHERE C.evidenciaNotas = 1 AND C.bloqueado = 0 ORDER BY C.nCiclo DESC; ";
DGV.DataBind();
}
}