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);
}
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 static object GetSelectComprobante(int jtStartIndex, int jtPageSize, string jtSorting, String[] dataFrm)
{
int indexPage;
int totalRow;
if (jtStartIndex != 0)
{
indexPage = jtStartIndex / jtPageSize;
}
else
{
indexPage = jtStartIndex;
}
eComprobanteVC ec = frmConsultaComprobante.SetParametros(dataFrm);
bComprobanteVC bc = new bComprobanteVC();
List <eComprobanteVC> list = bc.GetSelectComprobante(ec, indexPage, jtPageSize, jtSorting.Substring(1), out totalRow, "FORM");
return(new { Result = "OK", Records = list, TotalRecordCount = totalRow });
}
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);
ec._Pri_Xpag_Rea_Ced = String.Format(moneda, _Pri_Xpag_Rea_Ced);
ec._Pri_Xcob_Rea_Ace = String.Format(moneda, _Pri_Xcob_Rea_Ace);
ec._Sin_Xcob_Rea_Ced = String.Format(moneda, _Sin_Xcob_Rea_Ced);
ec._Sin_Xpag_Rea_Ace = String.Format(moneda, _Sin_Xpag_Rea_Ace);
ec._Otr_Cta_Xpag_Rea_Ace = String.Format(moneda, _Otr_Cta_Xpag_Rea_Ace);
ec._Dscto_Comis_Rea = String.Format(moneda, _Dscto_Comis_Rea);
ec._Saldo_Deudor = String.Format(moneda, _Saldo_Deudor);
ec._Saldo_Acreedor = String.Format(moneda, _Saldo_Acreedor);
list.Add(ec);
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();
}
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));
obj[12] = String.Format(o._Formato_Moneda, dr.GetDecimal(13));
obj[13] = String.Format(o._Formato_Moneda, dr.GetDecimal(14));
obj[14] = String.Format(o._Formato_Moneda, dr.GetDecimal(15));
obj[15] = String.Format(o._Formato_Moneda, dr.GetDecimal(16));
obj[16] = String.Format(o._Formato_Moneda, dr.GetDecimal(17));
obj[17] = String.Format(o._Formato_Moneda, dr.GetDecimal(18));
obj[18] = String.Format(o._Formato_Moneda, dr.GetDecimal(19));
obj[19] = String.Format(o._Formato_Moneda, dr.GetDecimal(20));
obj[20] = String.Format(o._Formato_Moneda, dr.GetDecimal(21));
obj[21] = String.Format(o._Formato_Moneda, dr.GetDecimal(22));
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));
ec._Pri_Xcob_Rea_Ace = String.Format(o._Formato_Moneda, dr.GetDecimal(13));
ec._Sin_Xcob_Rea_Ced = String.Format(o._Formato_Moneda, dr.GetDecimal(14));
ec._Sin_Xpag_Rea_Ace = String.Format(o._Formato_Moneda, dr.GetDecimal(15));
ec._Otr_Cta_Xcob_Rea_Ced = String.Format(o._Formato_Moneda, dr.GetDecimal(16));
ec._Otr_Cta_Xpag_Rea_Ace = String.Format(o._Formato_Moneda, dr.GetDecimal(17));
ec._Dscto_Comis_Rea = String.Format(o._Formato_Moneda, dr.GetDecimal(18));
ec._Saldo_Deudor = String.Format(o._Formato_Moneda, dr.GetDecimal(19));
ec._Saldo_Acreedor = String.Format(o._Formato_Moneda, dr.GetDecimal(20));
ec._Saldo_Deudor_Comp = String.Format(o._Formato_Moneda, dr.GetDecimal(21));
ec._Saldo_Acreedor_Comp = String.Format(o._Formato_Moneda, dr.GetDecimal(22));
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);
}
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));
}
