public List<eComprobanteVC> GetSelectTotalComprobante(eComprobanteVC o, String orderBy)
{
dSqlComprobanteVC dc = new dSqlComprobanteVC();
var dataList = dc.GetSelectComprobante(o, 0, 1000, orderBy, out total, out moneda,"FORM");
List<eComprobanteVC> list = new List<eComprobanteVC>();
eComprobanteVC ec = new eComprobanteVC();
Decimal _Otr_Cta_Xcob_Rea_Ced = 0.00m;
Decimal _Pri_Xpag_Rea_Ced = 0.00m;
Decimal _Pri_Xcob_Rea_Ace = 0.00m;
Decimal _Sin_Xcob_Rea_Ced = 0.00m;
Decimal _Sin_Xpag_Rea_Ace = 0.00m;
Decimal _Otr_Cta_Xpag_Rea_Ace = 0.00m;
Decimal _Dscto_Comis_Rea = 0.00m;
Decimal _Saldo_Deudor = 0.00m;
Decimal _Saldo_Acreedor = 0.00m;
for (int i = 0; i < dataList.Count; i++) {
_Otr_Cta_Xcob_Rea_Ced += Convert.ToDecimal(dataList[i]._Otr_Cta_Xcob_Rea_Ced);
_Pri_Xpag_Rea_Ced += Convert.ToDecimal(dataList[i]._Pri_Xpag_Rea_Ced);
_Pri_Xcob_Rea_Ace += Convert.ToDecimal(dataList[i]._Pri_Xcob_Rea_Ace);
_Sin_Xcob_Rea_Ced += Convert.ToDecimal(dataList[i]._Sin_Xcob_Rea_Ced);
_Sin_Xpag_Rea_Ace += Convert.ToDecimal(dataList[i]._Sin_Xpag_Rea_Ace);
_Otr_Cta_Xpag_Rea_Ace += Convert.ToDecimal(dataList[i]._Otr_Cta_Xpag_Rea_Ace);
_Dscto_Comis_Rea += Convert.ToDecimal(dataList[i]._Dscto_Comis_Rea);
_Saldo_Deudor += Convert.ToDecimal(dataList[i]._Saldo_Deudor);
_Saldo_Acreedor += Convert.ToDecimal(dataList[i]._Saldo_Acreedor);
}
ec._Otr_Cta_Xcob_Rea_Ced = String.Format(moneda, _Otr_Cta_Xcob_Rea_Ced).Substring(3);
ec._Pri_Xpag_Rea_Ced = String.Format(moneda,_Pri_Xpag_Rea_Ced).Substring(3);
ec._Pri_Xcob_Rea_Ace = String.Format(moneda,_Pri_Xcob_Rea_Ace).Substring(3);
ec._Sin_Xcob_Rea_Ced = String.Format(moneda,_Sin_Xcob_Rea_Ced).Substring(3);
ec._Sin_Xpag_Rea_Ace = String.Format(moneda,_Sin_Xpag_Rea_Ace).Substring(3);
ec._Otr_Cta_Xpag_Rea_Ace = String.Format(moneda,_Otr_Cta_Xpag_Rea_Ace).Substring(3);
ec._Dscto_Comis_Rea = String.Format(moneda,_Dscto_Comis_Rea).Substring(3);
ec._Saldo_Deudor = String.Format(moneda,_Saldo_Deudor).Substring(3);
ec._Saldo_Acreedor = String.Format(moneda,_Saldo_Acreedor).Substring(3);
list.Add(ec);
return list;
}
protected void btn_exportar_Click(object sender, ImageClickEventArgs e)
{
eComprobanteVC eo = new eComprobanteVC();
if (txt_operacion_c.Text != "")
eo._Nro_Comprobante = Int32.Parse(txt_operacion_c.Text);
else
eo._Nro_Comprobante = 0;
if (ddl_tipcom_c.SelectedItem.Value != "0")
eo._Tip_Comprobante = ddl_tipcom_c.SelectedItem.Value;
else
eo._Tip_Comprobante = "0";
if (txt_fec_ini.Text != "" && txt_fec_hasta.Text != "")
{
eo._fecha_fin = Convert.ToDateTime(txt_fec_hasta.Text);
eo._fecha_ini = Convert.ToDateTime(txt_fec_ini.Text);
eo._token_fecha = "SI";
}
else
{
eo._fecha_fin = System.DateTime.Today;
eo._fecha_ini = System.DateTime.Today;
eo._token_fecha = "NO";
}
if(ddl_ramo_c.SelectedItem.Value != "0")
eo._Cod_Ramo = ddl_ramo_c.SelectedItem.Value;
else
eo._Cod_Ramo = "0";
if(ddl_contrato_c.SelectedItem.Value != "")
eo._Ide_Contrato = ddl_contrato_c.SelectedItem.Value;
else
eo._Ide_Contrato = "0";
eo._Formato_Moneda = formato_moneda;
exportarDataExecl(eo);
}
public DataTable GetSelectComprobanteExport(eComprobanteVC o, int start, int size, String orderBy, out int total)
{
dSqlComprobanteVC dc = new dSqlComprobanteVC();
return dc.GetSelectComprobanteExport(o,start,size,orderBy,out total);
}
public List<eComprobanteVC> GetSelectComprobante(eComprobanteVC o, int start, int size, String orderBy, out int total,String excel)
{
dSqlComprobanteVC dc = new dSqlComprobanteVC();
return dc.GetSelectComprobante(o, start, size, orderBy, out total, out moneda,excel);
}
public DataTable GetSelectComprobanteExport(eComprobanteVC o, int start, int size, String orderBy, out int total)
{
String[] cabecera = { "N° Comprobante", "Tipo \n Comprobante", "Reasegurador", "Fecha de \n Comprobante", "Año", "Mes", "Asegurado", "N° Contrato", "Ramo","Moneda", "Descripción", "Primas por pagar \n Reaseguros \n Cedidos",
"Primas por Cobrar \n Reaseguros \n Aceptados","Siniestros por \n Cobrar \n Reaseguros \n Cedidos","Siniestros por \n Pagar Reaseguros \n Aceptados","Otras Cuentas por \n Cobrar \n Reaseguros \n Cedidos",
"Otras Cuentas por \n Pagar Reaseguros \n Aceptados","Descuento y \n Comisiones de \n Reaseguros","Saldo Deudor","Saldo Acreedor","Saldo Deudor \n (COMPENSADOS)","Saldo Acreedor \n (COMPENSADOS)","Estado","Fecha Registro","Usuario \n Registro"};
DataTable dt = new DataTable();
for (int c = 0; c < cabecera.Length; c++)
{
dt.Columns.Add(cabecera[c]);
}
int DBtotRow = 0;
try
{
conexion.Open();
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = conexion;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = _db.sSelectComprobante;
sqlcmd.Parameters.Add("@NRO_CONTRATO", SqlDbType.Char).Value = o._Ide_Contrato;
sqlcmd.Parameters.Add("@TIPO_COMPROBANTE", SqlDbType.Char).Value = o._Tip_Comprobante;
sqlcmd.Parameters.Add("@COD_RAMO", SqlDbType.Char).Value = o._Cod_Ramo;
sqlcmd.Parameters.Add("@NRO_COMPROBANTE", SqlDbType.Int).Value = o._Nro_Comprobante;
sqlcmd.Parameters.Add("@FECHA_INI", SqlDbType.Date).Value = o._fecha_ini;
sqlcmd.Parameters.Add("@FECHA_HASTA", SqlDbType.Date).Value = o._fecha_fin;
sqlcmd.Parameters.Add("@TOKEN_DATE", SqlDbType.Char).Value = o._token_fecha;
sqlcmd.Parameters.Add("@ORDERBY", SqlDbType.VarChar).Value = orderBy;
sqlcmd.Parameters.Add("@START", SqlDbType.Int).Value = start;
sqlcmd.Parameters.Add("@FIN", SqlDbType.Int).Value = size;
sqlcmd.Parameters.Add("@TOTALROW", SqlDbType.Int).Direction = ParameterDirection.Output;
SqlDataReader dr = sqlcmd.ExecuteReader();
while (dr.Read())
{
Object[] obj = new Object[cabecera.Length];
obj[0] = dr.GetInt32(1);
obj[1] = dr.GetString(2);
obj[2] = dr.GetString(3);
obj[3] = dr.GetDateTime(4);
obj[4] = dr.GetInt32(5);
obj[5] = meses[dr.GetInt32(6)];
obj[6] = dr.GetString(7);
obj[7] = dr.GetString(8);
obj[8] = dr.GetString(9);
obj[9] = dr.GetString(10);
obj[10] = dr.GetString(11);
obj[11] = String.Format(o._Formato_Moneda, dr.GetDecimal(12)).Substring(3);
obj[12] = String.Format(o._Formato_Moneda, dr.GetDecimal(13)).Substring(3);
obj[13] = String.Format(o._Formato_Moneda, dr.GetDecimal(14)).Substring(3);
obj[14] = String.Format(o._Formato_Moneda, dr.GetDecimal(15)).Substring(3);
obj[15] = String.Format(o._Formato_Moneda, dr.GetDecimal(16)).Substring(3);
obj[16] = String.Format(o._Formato_Moneda, dr.GetDecimal(17)).Substring(3);
obj[17] = String.Format(o._Formato_Moneda, dr.GetDecimal(18)).Substring(3);
obj[18] = String.Format(o._Formato_Moneda, dr.GetDecimal(19)).Substring(3);
obj[19] = String.Format(o._Formato_Moneda, dr.GetDecimal(20)).Substring(3);
obj[20] = String.Format(o._Formato_Moneda, dr.GetDecimal(21)).Substring(3);
obj[21] = String.Format(o._Formato_Moneda, dr.GetDecimal(22)).Substring(3);
obj[22] = dr.GetString(23);
obj[23] = dr.GetDateTime(24);
obj[24] = dr.GetString(25);
dt.Rows.Add(obj);
}
dr.Close();
DBtotRow = (int)sqlcmd.Parameters["@TOTALROW"].Value;
}
catch (Exception ex)
{
}
finally
{
conexion.Close();
}
total = DBtotRow;
return dt;
}
public List<eComprobanteVC> GetSelectComprobante(eComprobanteVC o, int start, int size, String orderBy, out int total,out String monedaS,String excel)
{
List<eComprobanteVC> list = new List<eComprobanteVC>();
int DBtotRow = 0;
try
{
conexion.Open();
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Connection = conexion;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = _db.sSelectComprobante;
sqlcmd.Parameters.Add("@NRO_CONTRATO", SqlDbType.Char).Value = o._Ide_Contrato;
sqlcmd.Parameters.Add("@TIPO_COMPROBANTE", SqlDbType.Char).Value = o._Tip_Comprobante;
sqlcmd.Parameters.Add("@COD_RAMO", SqlDbType.Char).Value = o._Cod_Ramo;
sqlcmd.Parameters.Add("@NRO_COMPROBANTE", SqlDbType.Int).Value = o._Nro_Comprobante;
sqlcmd.Parameters.Add("@FECHA_INI", SqlDbType.Date).Value = o._fecha_ini;
sqlcmd.Parameters.Add("@FECHA_HASTA", SqlDbType.Date).Value = o._fecha_fin;
sqlcmd.Parameters.Add("@TOKEN_DATE", SqlDbType.Char).Value = o._token_fecha;
sqlcmd.Parameters.Add("@ORDERBY", SqlDbType.VarChar).Value = orderBy;
sqlcmd.Parameters.Add("@START", SqlDbType.Int).Value = start;
sqlcmd.Parameters.Add("@FIN", SqlDbType.Int).Value = size;
sqlcmd.Parameters.Add("@TOTALROW", SqlDbType.Int).Direction = ParameterDirection.Output;
SqlDataReader dr = sqlcmd.ExecuteReader();
while (dr.Read())
{
var ec = new eComprobanteVC();
ec._Nro_Comprobante = dr.GetInt32(1);
ec._Tip_Comprobante = dr.GetString(2);
ec._Cod_Reasegurador = dr.GetString(3);
ec._Fec_Comprobante = dr.GetDateTime(4);
ec._Ano_Comprobante = dr.GetInt32(5);
ec._Mes_Comprobante = meses[dr.GetInt32(6)];
ec._Cod_Asegurado = dr.GetString(7);
ec._Ide_Contrato = dr.GetString(8);
ec._Cod_Ramo = dr.GetString(9);
ec._Cod_Moneda = dr.GetString(10);
ec._Des_Reg_Trimestre = dr.GetString(11);
ec._Pri_Xpag_Rea_Ced = String.Format(o._Formato_Moneda, dr.GetDecimal(12)).Substring(3);
ec._Pri_Xcob_Rea_Ace = String.Format(o._Formato_Moneda, dr.GetDecimal(13)).Substring(3);
ec._Sin_Xcob_Rea_Ced = String.Format(o._Formato_Moneda, dr.GetDecimal(14)).Substring(3);
ec._Sin_Xpag_Rea_Ace = String.Format(o._Formato_Moneda, dr.GetDecimal(15)).Substring(3);
ec._Otr_Cta_Xcob_Rea_Ced = String.Format(o._Formato_Moneda, dr.GetDecimal(16)).Substring(3);
ec._Otr_Cta_Xpag_Rea_Ace = String.Format(o._Formato_Moneda, dr.GetDecimal(17)).Substring(3);
ec._Dscto_Comis_Rea = String.Format(o._Formato_Moneda, dr.GetDecimal(18)).Substring(3);
ec._Saldo_Deudor = String.Format(o._Formato_Moneda, dr.GetDecimal(19)).Substring(3);
ec._Saldo_Acreedor = String.Format(o._Formato_Moneda, dr.GetDecimal(20)).Substring(3);
ec._Saldo_Deudor_Comp = String.Format(o._Formato_Moneda, dr.GetDecimal(21)).Substring(3);
ec._Saldo_Acreedor_Comp = String.Format(o._Formato_Moneda, dr.GetDecimal(22)).Substring(3);
ec._Estado = dr.GetString(23);
ec._Fec_Reg = dr.GetDateTime(24);
ec._Usu_Reg = dr.GetString(25);
list.Add(ec);
}
dr.Close();
DBtotRow = (int)sqlcmd.Parameters["@TOTALROW"].Value;
}
catch (Exception ex)
{
}
finally
{
conexion.Close();
}
total = DBtotRow;
monedaS = o._Formato_Moneda;
return list;
}
private void exportarDataExecl(eComprobanteVC o)
{
int total;
bComprobanteVC bc = new bComprobanteVC();
DataTable dt = bc.GetSelectComprobanteExport(o,0, 1000000, "NRO_COMPROBANTE ASC", out total);
String filename = "COMPROBANTE " + ddl_tipcom_c.SelectedItem.Text + " " + String.Format("{0:dd/MM/yyyy}", System.DateTime.Today) + ".xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
var output = new MemoryStream();
var writer = new StreamWriter(output);
HSSFWorkbook xssfworkbook = new HSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("COMPROBANTE");
IFont titleFont = xssfworkbook.CreateFont();
titleFont.FontName = "Calibri";
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleFont.Color = (IndexedColors.Black.Index);
titleFont.FontHeightInPoints = 11;
ICellStyle styleCabecera = xssfworkbook.CreateCellStyle();
styleCabecera.Alignment = HorizontalAlignment.Center;
styleCabecera.VerticalAlignment = VerticalAlignment.Center;
styleCabecera.SetFont(titleFont);
styleCabecera.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleCabecera.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleCabecera.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleCabecera.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
for (int cl1 = 0; cl1 < 11; cl1++)
{
sheet.SetColumnWidth(cl1, 150 * 25);
}
for (int cl = 11; cl <= 21; cl++)
{
sheet.SetColumnWidth(cl, 300 * 25);
}
IRow dataHeader = sheet.CreateRow(0);
ICell dataCellH;
for (int h = 0; h < dt.Columns.Count; h++) {
dataCellH = dataHeader.CreateCell(h);
dataCellH.SetCellValue(dt.Columns[h].ToString());
dataCellH.CellStyle = styleCabecera;
dataCellH.CellStyle.WrapText = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
IRow dataBody = sheet.CreateRow(1 + r);
for (int c = 0; c < dt.Columns.Count; c++)
{
dataBody.CreateCell(c, CellType.String).SetCellValue(dt.Rows[r][c].ToString());
}
}
xssfworkbook.Write(output);
writer.Flush();
dt.Rows.Clear();
dt.Columns.Clear();
dt.Clear();
Response.BinaryWrite(output.GetBuffer());
Response.End();
}
private static eComprobanteVC SetParametros( String[] dataFrm)
{
Int32 nro_comprob = 0;
String fecha_token = "NO";
if (dataFrm[3] != "") { nro_comprob = Int32.Parse(dataFrm[3]); }
if (dataFrm[4] != "") { fecha_token = "SI"; }
eComprobanteVC ec = new eComprobanteVC();
ec._Ide_Contrato = dataFrm[0];
ec._Tip_Comprobante = dataFrm[1];
ec._Cod_Ramo = dataFrm[2];
ec._Nro_Comprobante = nro_comprob;
ec._Formato_Moneda = formato_moneda;
if (dataFrm[4] != "" || dataFrm[5] != "")
{
ec._token_fecha = fecha_token;
ec._fecha_ini = Convert.ToDateTime(dataFrm[4]);
ec._fecha_fin = Convert.ToDateTime(dataFrm[5]);
}
else
{
ec._token_fecha = fecha_token;
ec._fecha_ini = System.DateTime.Today;
ec._fecha_fin = System.DateTime.Today;
}
return ec;
}
