private void btnPrev_Click(object sender, EventArgs e)
{
try
{
string tipMes = cbMes.SelectedValue.ToString() == "00" ? "%" : cbMes.SelectedValue.ToString();
string tipAnn = nudAnn.Value.ToString();
DateTime dtIni = dpIni.Value;
DateTime dtFin = dpFin.Value;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySqlDataAdapter datos;
command = conn.CreateCommand();
//SELECT act.idAct, act.desAct, ofi.nomOfi, cat.desCat, his.fecDep, his.monDep, his.mesDep, his.desDep, par.desPar FROM historialdepreciacion his, activo act, parametrodepreciacion par, oficina ofi, categoria cat WHERE his.activo_idAct=act.idAct AND his.numCom=par.idPar AND act.oficina_idOfi=ofi.idOfi AND act.categoria_idCat=cat.idCat ORDER BY his.fecDep
string commSql = "SELECT act.idAct, act.desAct, ofi.nomOfi, cat.desCat, his.fecDep, his.monDep, his.mesDep, his.desDep, "
+ "par.desPar FROM historialdepreciacion his, activo act, parametrodepreciacion par, oficina ofi, categoria cat "
+ "WHERE his.activo_idAct=act.idAct AND his.numCom=par.idPar AND act.oficina_idOfi=ofi.idOfi AND act.categoria_idCat=cat.idCat "
+ "AND his.desDep LIKE @desDep ";
if (rb2.Checked)
{
command.Parameters.Add("@desDep", MySqlDbType.String).Value = "1-%";
commSql += "AND his.fecDep >= @fecIni AND his.fecDep <= @fecFin ";
command.Parameters.Add("@fecIni", MySqlDbType.DateTime).Value = dtIni;
command.Parameters.Add("@fecFin", MySqlDbType.DateTime).Value = dtFin;
}
if (rb1.Checked)
{
command.Parameters.Add("@desDep", MySqlDbType.String).Value = tipAnn + "-" + tipMes;
}
commSql += "ORDER BY his.fecDep";
command.CommandText = commSql;
datos = new MySqlDataAdapter(command);
DataTable dtAct = new DataTable();
datos.Fill(dtAct);
conn.Close();
if (dtAct.Rows.Count > 0)
{
libGen lgen = new libGen();
rptFrm rp = new rptFrm();
rp.nomRep = "rptDepreciaciones.rdlc"; //nombre del reporte a utilizar
rp.dtParam = new DataTable(); // coleccion de parametros que tiene el reporte
rp.dtParam.Columns.Add("nom");
rp.dtParam.Columns.Add("val");
if (rb2.Checked)
{
rp.dtParam.Rows.Add("nomRep", "REPORTE DE SALIDAS DE ACTIVOS");
rp.dtParam.Rows.Add("fecIni", dtIni.ToShortDateString());
rp.dtParam.Rows.Add("fecFin", dtFin.ToShortDateString());
rp.dtParam.Rows.Add("tipMes", "");
}
else
{
rp.dtParam.Rows.Add("nomRep", "REPORTE DE DEPRECIACIONES POR MES");
libGen _libGen = new libGen();
rp.dtParam.Rows.Add("fecIni", "");
rp.dtParam.Rows.Add("fecFin", "");
rp.dtParam.Rows.Add("tipMes", _libGen.annMes(tipAnn + "-" + tipMes));
}
rp.ds = "dsRep"; //nombre del dataset
rp.dtSet = dtAct; //contenido del dataset
rp.Show(); //mostrar formulario de reporte
}
else
{
MessageBox.Show("Error:" + Environment.NewLine + "No Existen Datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Error:" + Environment.NewLine + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnPrev_Click(object sender, EventArgs e)
{
try
{
string tipOfi = cbOfi.SelectedValue.ToString() == "0" ? "%" : cbOfi.SelectedValue.ToString();
string tipCat = cbCat.SelectedValue.ToString() == "0" ? "%" : cbCat.SelectedValue.ToString();
string tipPar = cbPar.SelectedValue.ToString() == "0" ? "%" : cbPar.SelectedValue.ToString();
string tipAdq = cbAdq.SelectedValue.ToString() == "0" ? "%" : cbAdq.SelectedValue.ToString();
string tipEst = cbEst.SelectedValue.ToString() == "0" ? "%" : cbEst.SelectedValue.ToString();
DateTime dtIni = dpIni.Value;
DateTime dtFin = dpFin.Value;
MySqlConnection conn = new MySqlConnection(connString);
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySqlDataAdapter datos;
command = conn.CreateCommand();
string commSql = "SELECT idAct, act.desAct, act.fecCom, act.numCom, act.ultDep,act.depMen, valcom, depacu, valact, "
+ "valres, par.mesVid, his.mssDep mesDep, par.desPar, ofi.desOfi, cat.desCat, adq.nomAdq, est.nomEst FROM activo act "
+ "LEFT JOIN (SELECT SUM(rial.mesDep)mssDep, rial.activo_idAct FROM historialdepreciacion rial, activo WHERE rial.activo_idAct=activo.idAct "
+ "GROUP BY activo_idAct) AS his ON (act.idAct=his.activo_idAct), parametrodepreciacion par, oficina ofi, categoria cat, "
+ "tipoaquisicion adq, estadoactivo est WHERE act.parametroDepreciacion_idPar=PAR.idPar AND act.oficina_idOfi=ofi.idOfi "
+ "AND act.categoria_idCat=cat.idCat AND act.tipoAquisicion_idAdq=adq.idAdq AND act.estadoActivo_idEst=est.idEst "
+ "AND par.idPar LIKE @tipPar AND ofi.idOfi LIKE @tipOfi AND cat.IdCat LIKE @tipCat AND adq.idAdq LIKE @tipAdq AND est.idEst LIKE @tipEst ";
if (chbFechas.Checked)
{
commSql += "AND act.fecCom >= @fecIni AND act.fecCom <= @fecFin ";
command.Parameters.Add("@fecIni", MySqlDbType.DateTime).Value = dtIni;
command.Parameters.Add("@fecFin", MySqlDbType.DateTime).Value = dtFin;
}
commSql += " ORDER BY ofi.idOfi, cat.idCat, act.idAct";
command.CommandText = commSql;
command.Parameters.Add("@tipPar", MySqlDbType.String).Value = tipPar;
command.Parameters.Add("@tipOfi", MySqlDbType.String).Value = tipOfi;
command.Parameters.Add("@tipCat", MySqlDbType.String).Value = tipCat;
command.Parameters.Add("@tipAdq", MySqlDbType.String).Value = tipAdq;
command.Parameters.Add("@tipEst", MySqlDbType.String).Value = tipEst;
datos = new MySqlDataAdapter(command);
DataTable dtAct = new DataTable();
datos.Fill(dtAct);
conn.Close();
if (dtAct.Rows.Count > 0)
{
libGen lgen = new libGen();
rptFrm rp = new rptFrm();
rp.nomRep = "rptActivos.rdlc"; //nombre del reporte a utilizar
rp.dtParam = new DataTable(); // coleccion de parametros que tiene el reporte
rp.dtParam.Columns.Add("nom");
rp.dtParam.Columns.Add("val");
rp.dtParam.Rows.Add("nomRep", "REPORTE DE ACTIVOS");
rp.dtParam.Rows.Add("tipPar", (cbPar.SelectedItem as DataRowView)["desPar"].ToString());
rp.dtParam.Rows.Add("tipOfi", (cbOfi.SelectedItem as DataRowView)["desPar"].ToString());
rp.dtParam.Rows.Add("tipCat", (cbCat.SelectedItem as DataRowView)["desPar"].ToString());
rp.dtParam.Rows.Add("tipAdq", (cbAdq.SelectedItem as DataRowView)["desPar"].ToString());
rp.dtParam.Rows.Add("tipEst", (cbEst.SelectedItem as DataRowView)["desPar"].ToString());
if (chbFechas.Checked)
{
rp.dtParam.Rows.Add("fecIni", dtIni.ToShortDateString());
rp.dtParam.Rows.Add("fecFin", dtFin.ToShortDateString());
}
else
{
rp.dtParam.Rows.Add("fecIni", "");
rp.dtParam.Rows.Add("fecFin", "");
}
rp.ds = "dsRep"; //nombre del dataset
rp.dtSet = dtAct; //contenido del dataset
rp.Show(); //mostrar formulario de reporte
}
else
{
MessageBox.Show("Error:" + Environment.NewLine + "No Existen Datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Error:" + Environment.NewLine + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}