private void button2_Click(object sender, EventArgs e)
{
Logger log = new Logger();
log.W("Iniciando log");
string path = "test.xml";
XDocument doc = XmlFormatter.OpenFile(path);
//XmlFormatter.PrintHeaderElementsBoletaDeVenta(doc, path, "cadenaDeParametros");
//XmlFormatter.PrintDetailElementsBoletaDeVenta(doc, path, "cadenaDeParametros");
//XmlFormatter.PrintPaymentElementsBoletaDeVenta(doc, path, "cadenaDeParametros");
//XmlFormatter.PrintValueElementsBoletaDeVenta(doc, path, "cadenaDeParametros");
log.W("Finalizando log");
}
static private void GenerarXML(MicrosCheck check, int index)
{
var filePath = $"{"TRX"}" +
$"{_configuration.CodigoTerminal}" +
$"{_configuration.CodigoBUPLA}" +
$"{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Year.ToString().Substring(2, 2)}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month}" : DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day}" : DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour}" : DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute}" : DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute.ToString())}" +
$"{check.Encabezado.IdDoc.Znumd}" +
$"{".xml"}";
try
{
var doc = XmlFormatter.OpenFile(filePath);
XmlFormatter.ImprimirDocumento(filePath, doc, index);
XmlFormatter.ImprimirElementosEncabezado(doc, filePath, check.Encabezado, index);
if (check.Detalle != null)
{
var j = 1;
foreach (var detalle in check.Detalle)
{
XmlFormatter.ImprimirDetalle(doc, filePath, index, j);
XmlFormatter.ImprimirCdgItem(doc, filePath, index, j);
XmlFormatter.ImprimirElementosDetalle(doc, filePath, detalle, index, j);
j++;
}
}
XmlFormatter.ImprimirTotales(doc, filePath, index);
XmlFormatter.ImprimirElementosTotales(doc, filePath, check.Totales, index);
XmlFormatter.ImprimirDscrcgGlobal(doc, filePath, index);
XmlFormatter.ImprimirElementosDscrcgGlobar(doc, filePath, check.DscrgGlobal, index);
XmlFormatter.ImprimirParametros(doc, filePath, index);
XmlFormatter.ImprimirElementosParametros(doc, filePath, check.Parametros, index, _configuration.EnviaMontoEscrito == "0");
XmlFormatter.RenameXmlNodes(doc, filePath);
}
catch (Exception ex)
{
throw;
}
}
private void button1_Click(object sender, EventArgs e)
{
using (OracleConnection connection = TestDb.GetDBConnection())
{
Cursor.Current = Cursors.WaitCursor;
Logger log = new Logger();
try
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleCommand subCommand = connection.CreateCommand();
string sql = "SELECT 'ENCABEZADO' TIPO, to_char(FID.MICROSBSNZDATE, 'dd/MM/yyyy') FECHA," +
"to_char(FID.MICROSBSNZDATE, 'hh:mm:ss') HORA, FID.FCRINVNUMBER INVNUM, to_char(FID.MICROSBSNZDATE, 'yyyyMM') PERIODO," +
"CAST(FID.SUBTOTAL1 AS INT) SUBTOTAL, CAST(FID.SUBTOTAL2 AS INT) DESCUENTO, CAST(FID.SUBTOTAL3 AS INT) TIPS," +
"CAST(FID.TAXTTL1 AS INT) IVA, CAST(FID.SUBTOTAL8 AS INT) TOTAL, FID.MICROSCHKNUM CHECKNUM, FID.PCWSID TERMINAL" +
"FROM FCR_INVOICE_DATA FID" +
"WHERE to_char(MICROSBSNZDATE, 'yyyyMMdd') = '20151128'" +
"ORDER BY FID.MICROSBSNZDATE DESC";
command.CommandText = sql;
OracleDataReader reader = command.ExecuteReader();
string path = "20151128_BoletaDeVenta.xml";
var xDoc = XmlFormatter.OpenFile(path);
int i = 0;
while (reader.Read())
{
i++;
var documento = new BoletaDeVenta();
var bvh = new BoletaDeVentaHeader()
{
Correlativo = i,
InvNumber = Convert.ToInt32(reader["INVNUM"]),
//CheckNumber = Convert.ToInt32(reader["CHECKNUM"]),
Fecha = reader["FECHA"].ToString(),
Periodo = reader["PERIODO"].ToString(),
Neto = Convert.ToInt32(reader["SUBTOTAL"]),
Total = Convert.ToInt32(reader["TOTAL"]),
Usuario = reader["TERMINAL"].ToString()
};
//XmlFormatter.PrintDocument(path, xDoc, i);
//XmlFormatter.PrintHeaderElementsBoletVentaEL(xDoc, path, bvh, i);
subSql = ""; //PRINT DETALLES
//PRINT PAGOS
//Print VALORES(DESCUENTO)
//PRINT VALORES(EXENTO)
//PRINT VALORES(IVA)
//PRINT VALORES(NBASE)
//PRINT VALORES(NETO)
//PRINT VALORES(PROPINA)
//PRINT VALORES(SPA)
//PRINT VALORES(TOTAL)
//PRINT VALORES(TRANSBANK)
//PRINT VALORES(TRANSIVA)
//PRINT VALORES(TRANSNETO)
}
XmlFormatter.RenameXmlNodes(xDoc, path);
}
catch (Exception ex)
{
log.W(ex.Message);
MessageBox.Show(ex.Message);
}
}
Cursor.Current = Cursors.Default;
}
private void GenerarXML(MicrosCheck check, int index)
{
var filePath = $"{@"D:\archivos-xml\"}" +
$"{"TRX"}" +
$"{_configuration.CodigoTerminal}" +
$"{_configuration.CodigoBUPLA}" +
$"{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Year.ToString().Substring(2, 2)}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month}" : DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day}" : DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour}" : DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute}" : DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute.ToString())}" +
$"{check.Encabezado.IdDoc.Znumd}" +
$"{".xml"}";
var copyPath = $"{@"D:\Netgroup\Ruby\xml\"}" +
$"{"TRX"}" +
$"{_configuration.CodigoTerminal}" +
$"{_configuration.CodigoBUPLA}" +
$"{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Year.ToString().Substring(2, 2)}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month}" : DateTime.Parse(check.Encabezado.IdDoc.Bldat).Month.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day}" : DateTime.Parse(check.Encabezado.IdDoc.Bldat).Day.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour}" : DateTime.Parse(check.Encabezado.IdDoc.Zhora).Hour.ToString())}" +
$"{(DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute.ToString().Length == 1 ? $"0{DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute}" : DateTime.Parse(check.Encabezado.IdDoc.Zhora).Minute.ToString())}" +
$"{check.Encabezado.IdDoc.Znumd}" +
$"{".xml"}";
try
{
//Logger.WriteLog($"Grabo xml en {filePath}", _logFilePath);
var doc = XmlFormatter.OpenFile(filePath);
XmlFormatter.ImprimirDocumento(filePath, doc, index);
XmlFormatter.ImprimirElementosEncabezado(doc, filePath, check.Encabezado, index);
//Logger.WriteLog($"Detalles qty: {check.Encabezado.IdDoc.Znumd} - {check.Detalle.Count}", _logFilePath);
if (check.Detalle.Count != 0)
{
var j = 1;
foreach (var detalle in check.Detalle)
{
XmlFormatter.ImprimirDetalle(doc, filePath, index, j);
XmlFormatter.ImprimirCdgItem(doc, filePath, index, j);
XmlFormatter.ImprimirElementosDetalle(doc, filePath, detalle, index, j);
j++;
}
}
else
{
Logger.WriteLog("El documento aún no posee detalles grabados en base de datos.", _logFilePath);
}
XmlFormatter.ImprimirTotales(doc, filePath, index);
XmlFormatter.ImprimirElementosTotales(doc, filePath, check.Totales, index);
XmlFormatter.ImprimirDscrcgGlobal(doc, filePath, index);
XmlFormatter.ImprimirElementosDscrcgGlobar(doc, filePath, check.DscrgGlobal, index);
XmlFormatter.ImprimirParametros(doc, filePath, index);
XmlFormatter.ImprimirElementosParametros(doc, filePath, check.Parametros, index, _configuration.EnviaMontoEscrito == "0");
XmlFormatter.RenameXmlNodes(doc, filePath);
try
{
File.Copy(filePath, copyPath, true);
}
catch (Exception ex)
{
Logger.WriteLog($"Error al copiar xml: {ex.Message}", _logFilePath);
throw;
}
Logger.WriteLog($"Se generó XML para fcrInvNumber: {check.Encabezado.IdDoc.Znumd} en la ruta {filePath}", _logFilePath);
}
catch (Exception ex)
{
Logger.WriteLog($"Error al generar XML: {ex.Message}", _logFilePath);
throw;
}
}
private void GenerarArchivo(OracleConnection connection)
{
var tipoDocumento = "BOLETA DE VENTA";
log.W("Inicia procesamiento " + tipoDocumento + " - " + dtpDesde.Value.ToString("dd/MM/yyyy"));
if (connection != null)
{
dtpDesde.Enabled = false;
btnGenerar.Enabled = false;
btnSalir.Enabled = false;
Cursor.Current = Cursors.WaitCursor;
try
{
connection.Open();
var command = connection.CreateCommand();
var subCommand = connection.CreateCommand();
var query = "SELECT TO_CHAR(FCRBSNZDATE, 'dd/MM/yyyy') FECHA, TO_CHAR(FCRBSNZDATE, 'yyyyMMdd') FECHA2, TO_CHAR(FCRBSNZDATE, 'yyyyMM') PERIODO, " +
"TO_CHAR(FCRBSNZDATE, 'dd/MM/yyyy') HORA, FCRINVNUMBER FCRINVNUMBER, FCRINVNUMBER INVNUM, " +
"MICROSCHKNUM CHECKNUM, ROUND(SUBTOTAL8/1.19) NETO, SUBTOTAL2 DESCUENTOS, SUBTOTAL3 SERVICIOS, SUBTOTAL5 EXENTO, " +
"SUBTOTAL8 TOTAL, SUBTOTAL9 PROPINA, ROUND(SUBTOTAL8/1.19 * 0.19) IVA, PCWSID TERMINAL " +
"FROM FCR_INVOICE_DATA WHERE TO_CHAR(FCRBSNZDATE, 'yyyyMMdd') = '" + dtpDesde.Value.ToString("yyyyMMdd") + "'" +
"ORDER BY FCRBSNZDATE DESC";
log.W($"query: {query}");
command.CommandText = query;
var reader = command.ExecuteReader();
var path = dtpDesde.Value.ToString("yyyyMMdd") + "_BoletaDeVenta.xml";
var xDoc = XmlFormatter.OpenFile(path);
var i = 0;
while (reader.Read())
{
i++;
var bveh = new BoletaVentaHeader()
{
Correlativo = i,
InvNumber = Convert.ToInt32(reader["INVNUM"]),
Fecha = reader["FECHA"].ToString(),
Hora = reader["HORA"].ToString(),
Usuario = reader["TERMINAL"].ToString(),
Periodo = reader["PERIODO"].ToString(),
Neto = decimal.Round(Convert.ToDecimal(reader["NETO"])),
Subtotal = Convert.ToDecimal(reader["NETO"]),
Total = Convert.ToDecimal(reader["TOTAL"])
};
XmlFormatter.PrintDocument(path, xDoc, i);
XmlFormatter.PrintHeaderElements(xDoc, path, tipoDocumento, bveh, i);
//DETALLE ---------------------------------------------------------------------
var sqlChecks = "SELECT CHECKID, CHECKNUMBER, EMPLOYEEID, CASHIERID, WORKSTATIONID, CHECKOPEN, " +
"TABLEOPEN, CHECKCLOSE, SPLITFROMCHECKNUM, SUBTOTAL, TAX, OTHER, PAYMENT, DUE FROM CHECKS " +
"WHERE TO_CHAR(CHECKPOSTINGTIME, 'YYYYMMDD') = '" + reader["FECHA2"] + "' and " +
"CHECKNUMBER = " + reader["CHECKNUM"];
command = connection.CreateCommand();
command.CommandText = sqlChecks;
var subReaderChecks = command.ExecuteReader();
while (subReaderChecks.Read())
{
//ITEMS ---------------------------------------------------------------------
var sqlCheckDetail = "SELECT CHECK_DETAIL.CHECKDETAILID, CHECK_DETAIL.CHECKID, CHECK_DETAIL.DETAILINDEX, " +
"CHECK_DETAIL.DETAILTYPE, CHECK_DETAIL.TOTAL, CHECK_DETAIL.SALESCOUNT, STRING_TABLE.STRINGTEXT, MENU_ITEM_DEFINITION.MENUITEMDEFID MENUITEMDEFID, " +
"MENU_ITEM_MASTER.OBJECTNUMBER OBJECTNUMBER FROM CHECK_DETAIL INNER JOIN MENU_ITEM_DETAIL ON CHECK_DETAIL.CHECKDETAILID = " +
"MENU_ITEM_DETAIL.CHECKDETAILID INNER JOIN MENU_ITEM_DEFINITION ON MENU_ITEM_DEFINITION.MENUITEMDEFID = " +
"MENU_ITEM_DETAIL.MENUITEMDEFID INNER JOIN STRING_TABLE ON STRING_TABLE.STRINGNUMBERID = " +
"MENU_ITEM_DEFINITION.NAME1ID INNER JOIN MENU_ITEM_MASTER ON MENU_ITEM_MASTER.MENUITEMMASTERID = MENU_ITEM_DEFINITION.MENUITEMMASTERID " +
"WHERE CHECKID = " + subReaderChecks["CHECKID"] + " AND TOTAL <> 0 AND MENU_ITEM_DEFINITION.MENUITEMCLASSID <> 1341 " +
"ORDER BY CHECK_DETAIL.DETAILINDEX";
//log.W(sqlCheckDetail);
command = connection.CreateCommand();
command.CommandText = sqlCheckDetail;
var subReaderDetail = command.ExecuteReader();
log.W("Procesado sqlCheckDetail");
var k = 0;
string articulo;
while (subReaderDetail.Read())
{
try
{
k++;
var subtotal = decimal.Round(Convert.ToDecimal(subReaderDetail["TOTAL"]) / 1.19m);
articulo = subReaderDetail["OBJECTNUMBER"].ToString();
var bved = new BoletaVentaDetalle()
{
Correlativo = i,
CorrelativoOrigen = Convert.ToInt32(subReaderChecks["CHECKNUMBER"]),
Secuencia = k,
Articulo = articulo,
Cantidad = Convert.ToInt32(subReaderDetail["SALESCOUNT"]),
Precio = Convert.ToInt32(subReaderDetail["TOTAL"]) / Convert.ToInt32(subReaderDetail["SALESCOUNT"]),
Subtotal = subtotal,
Iva = decimal.Round(subtotal * 0.19m),
Fecha = reader["FECHA"].ToString(),
Total = Convert.ToInt32(subReaderDetail["TOTAL"])
};
XmlFormatter.PrintDetail(xDoc, path, i, k);
XmlFormatter.PrintDetailElements(xDoc, path, tipoDocumento, bved, i, k);
}
catch (Exception ex)
{
log.W($"sqlCheckDetail: {ex.Message}");
throw;
}
}
//PAGOS ---------------------------------------------------------------------
var sqlCheckPayment = "SELECT CHECK_DETAIL.CHECKDETAILID, CHECK_DETAIL.CHECKID, CHECK_DETAIL.DETAILINDEX, " +
"CHECK_DETAIL.DETAILTYPE, CHECK_DETAIL.TOTAL, CHECK_DETAIL.SALESCOUNT, TENDER_MEDIA_DETAIL.TENDMEDID " +
"FROM CHECK_DETAIL INNER JOIN TENDER_MEDIA_DETAIL ON CHECK_DETAIL.CHECKDETAILID = TENDER_MEDIA_DETAIL.CHECKDETAILID " +
"WHERE CHECKID = " + subReaderChecks["CHECKID"] + " AND TOTAL > 0 ORDER BY CHECK_DETAIL.DETAILINDEX";
decimal transbank = 0;
command = connection.CreateCommand();
command.CommandText = sqlCheckPayment;
var subReaderPayment = command.ExecuteReader();
var l = 0;
//log.W($"sqlCheckPayment: {sqlCheckPayment}");
while (subReaderPayment.Read())
{
try
{
l++;
if (subReaderPayment["TENDMEDID"].ToString() == "21")
{
transbank += Convert.ToDecimal(subReaderPayment["TOTAL"]);
}
articulo = subReaderPayment["TENDMEDID"].ToString();
var bvep = new BoletaVentaPago()
{
Correlativo = i,
BillNo = Convert.ToInt32(reader["INVNUM"]),
Linea = l,
Monto = Convert.ToDecimal(subReaderPayment["TOTAL"]),
TrxCode = articulo,
Fecha = reader["FECHA"].ToString()
};
XmlFormatter.PrintPayment(xDoc, path, i, l);
XmlFormatter.PrintPaymentElements(xDoc, path, tipoDocumento, bvep, i, l, false);
}
catch (Exception ex)
{
log.W($"sqlCheckPayment: {ex.Message}");
throw;
}
}
//VALORES ---------------------------------------------------------------------
//DESCUENTO -------------------------------------------------------------------
var sqlCheckDiscount = "SELECT ABS(CHECK_DETAIL.TOTAL) TOTAL " +
"FROM CHECK_DETAIL WHERE CHECKID = " + subReaderChecks["CHECKID"] +
"AND DETAILTYPE = 2 ORDER BY CHECK_DETAIL.DETAILINDEX";
command = connection.CreateCommand();
command.CommandText = sqlCheckDiscount;
var subReaderDiscount = command.ExecuteReader();
var descQty = 0;
while (subReaderDiscount.Read())
{
descQty++;
var bvev = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "DESCUENTO",
Factor = -1,
Orden = 2,
Monto = Convert.ToDecimal(subReaderDiscount["TOTAL"]),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 1);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 1);
}
//Si no hubo descuentos no agrego el nodo
if (descQty == 0)
{
var bvev = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "DESCUENTO",
Factor = -1,
Orden = 2,
Monto = 0,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 1);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 1);
}
//VALORES ------------------------------------------------------------------
//EXENTO -------------------------------------------------------------------
var bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "EXENTO",
Factor = 1,
Orden = 3,
Monto = 0,
Porcentaje = 0
};
//Por ahora no agrego el nodo correspondiente a EXENTO ya que no se manejan montos exentos
//XmlFormatter.PrintValues(xDoc, path, i, 2);
//XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 2);
//VALORES ---------------------------------------------------------------
//IVA -------------------------------------------------------------------
var iva = "0";
if (reader["IVA"].ToString() != string.Empty)
{
iva = reader["IVA"].ToString();
}
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "IVA",
Factor = 1,
Orden = 3,
Monto = decimal.Round(Convert.ToDecimal(iva)),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 3);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 3);
//VALORES ---------------------------------------------------------------
//NBASE -----------------------------------------------------------------
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "NBASE",
Factor = 0,
Orden = 6,
Monto = Convert.ToDecimal(reader["NETO"]),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 4);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 4);
//VALORES ---------------------------------------------------------------
//NETO ------------------------------------------------------------------
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "NETO",
Factor = 0,
Orden = 6,
Monto = Convert.ToDecimal(reader["NETO"]),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 5);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 5);
//VALORES ---------------------------------------------------------------
//PROPINA ---------------------------------------------------------------
var propina = "0";
if (reader["PROPINA"].ToString() != string.Empty)
{
propina = reader["PROPINA"].ToString();
}
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "PROPINA",
Factor = 1,
Orden = 1,
Monto = Convert.ToDecimal(propina),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 6);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 6);
//VALORES ---------------------------------------------------------------
//SPA -------------------------------------------------------------------
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "SPA",
Factor = 1,
Orden = 7,
Monto = 0,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 7);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 7);
//VALORES ---------------------------------------------------------------
//TOTAL -----------------------------------------------------------------
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "TOTAL",
Factor = 0,
Orden = 6,
Monto = Convert.ToDecimal(reader["TOTAL"]),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 8);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 8);
//VALORES ---------------------------------------------------------------
//TRANSBANK -------------------------------------------------------------
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "TRANSBANK",
Factor = 0,
Orden = 6,
Monto = transbank,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 9);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 9);
//VALORES ---------------------------------------------------------------
//TRANSIVA --------------------------------------------------------------
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "TRANSIVA",
Factor = 0,
Orden = 6,
Monto = transbank - decimal.Round(transbank / 1.19m),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 10);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 10);
//VALORES ---------------------------------------------------------------
//TRANSNETO -------------------------------------------------------------
bvev2 = new BoletaVentaValores()
{
Correlativo = i,
Nombre = "TRANSNETO",
Factor = 0,
Orden = 6,
Monto = decimal.Round(transbank / 1.19m),
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 11);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev2, i, 11);
}
}
XmlFormatter.RenameXmlNodes(xDoc, path);
log.W("Finaliza procesamiento " + tipoDocumento + " - " + dtpDesde.Value.ToString("dd/MM/yyyy"));
}
catch (Exception ex)
{
log.W(ex.Message + "|" + ex.InnerException.Message);
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Archivo de configuración no encontrado, contacte a su proveedor.");
log.W("Archivo de configuración no encontrado");
}
}
private void btnGenerar_Click(object sender, EventArgs e)
{
string tipoDocumento = "BOLETA VENTA (EL)";
log.W("Inicia procesamiento " + tipoDocumento + " - " + dtpDesde.Value.ToString("dd/MM/yyyy"));
dtpDesde.Enabled = false;
btnGenerar.Enabled = false;
btnSalir.Enabled = false;
Cursor.Current = Cursors.WaitCursor;
using (OracleConnection connection = ConnDb.GetDBConnection(configuration))
{
Logger log = new Logger();
if (connection != null)
{
Cursor.Current = Cursors.WaitCursor;
try
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleCommand subCommand = connection.CreateCommand();
string sql, sqlTrx, sqlPagos, sqlTransbank, sqlIva, sqlPaidouts, sqlPropina, sqlDescuentos, sqlSpa;
sql = "SELECT BILL_NO, TO_CHAR(BUSINESS_DATE, 'dd/MM/yyyy') BUSINESSDATE, TO_CHAR(BUSINESS_DATE, 'yyyyMM') PERIODO, RESV_NAME_ID, ROOM, TOT_REV_TAXABLE, TOT_NONREV_TAXABLE, TOT_REV_NONTAXABLE, TOT_NONREV_NONTAXABLE, TOTAL_NET, TOTAL_GROSS, TAX1_AMT, NET1_AMT, FOLIO_TYPE, FOLIO_NO, CASHIER_ID FROM FOLIO$_TAX " +
"WHERE TO_CHAR(BUSINESS_DATE, 'YYYYmmDD') = '" + dtpDesde.Value.ToString("yyyyMMdd") + "' AND TOTAL_NET <> 0";
command.CommandText = sql;
OracleDataReader reader = command.ExecuteReader();
string path = dtpDesde.Value.ToString("yyyyMMdd") + "_BoletaDeVentaEL.xml";
var xDoc = XmlFormatter.OpenFile(path);
int i = 0;
while (reader.Read())
{
i++;
var bveh = new BoletaVentaHeader()
{
Correlativo = i,
InvNumber = Convert.ToInt32(reader["BILL_NO"]),
Fecha = reader["BUSINESSDATE"].ToString(),
Usuario = reader["CASHIER_ID"].ToString(),
Periodo = reader["PERIODO"].ToString(),
Neto = Convert.ToDecimal(reader["TOTAL_NET"]),
Subtotal = Convert.ToDecimal(reader["TOTAL_NET"]),
Total = Convert.ToDecimal(reader["TOTAL_GROSS"])
};
XmlFormatter.PrintDocument(path, xDoc, i);
XmlFormatter.PrintHeaderElements(xDoc, path, tipoDocumento, bveh, i);
//Transacciones
sqlTrx = "SELECT FT.TAX_INCLUSIVE_YN, FT.NET_AMOUNT, FT.GROSS_AMOUNT, FT.CHEQUE_NUMBER," +
"FT.TRX_NO, TC.DESCRIPTION, FT.TC_GROUP, FT.TRX_CODE, FT.QUANTITY, TO_CHAR(FT.TRX_DATE, 'dd/MM/yyyy') TRX_DATE, FT.ROOM, FT.RESV_NAME_ID," +
"FT.PRICE_PER_UNIT, FT.TRX_AMOUNT, FT.POSTED_AMOUNT, FT.REVENUE_AMT, FT.BILL_NO " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TC_GROUP not in (70, 80, 90) AND NET_AMOUNT > 0 and BILL_NO = " + reader["BILL_NO"].ToString();
sqlIva = "SELECT NVL(SUM(FT.TRX_AMOUNT), 0) AMOUNT " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TC_GROUP = 80 AND NET_AMOUNT <> 0 and BILL_NO = " + reader["BILL_NO"].ToString();
sqlPagos = "SELECT TO_CHAR(FT.BUSINESS_DATE, 'dd/MM/yyyy') BUSINESS_DATE, FT.TAX_INCLUSIVE_YN, FT.NET_AMOUNT, FT.GROSS_AMOUNT, FT.CHEQUE_NUMBER," +
"FT.TRX_NO, TC.DESCRIPTION, FT.TC_GROUP, FT.TRX_CODE, FT.QUANTITY, TO_CHAR(FT.TRX_DATE, 'dd/MM/yyyy') TRX_DATE, FT.ROOM, FT.RESV_NAME_ID," +
"FT.PRICE_PER_UNIT, FT.TRX_AMOUNT, FT.POSTED_AMOUNT, FT.REVENUE_AMT, FT.BILL_NO " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TC_GROUP = 90 AND BILL_NO = " + reader["BILL_NO"].ToString();
sqlTransbank = "SELECT NVL(SUM(FT.TRX_AMOUNT), 0) AMOUNT " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TRX_CODE = 9050 AND BILL_NO = " + reader["BILL_NO"].ToString();
sqlPropina = "SELECT NVL(SUM(FT.TRX_AMOUNT), 0) AMOUNT " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TC_GROUP = 70 AND NET_AMOUNT <> 0 and BILL_NO = " + reader["BILL_NO"].ToString();
sqlPaidouts = "SELECT NVL(SUM(FT.TRX_AMOUNT), 0) AMOUNT " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TC_GROUP = 75 AND NET_AMOUNT <> 0 and BILL_NO = " + reader["BILL_NO"].ToString();
sqlSpa = "SELECT NVL(SUM(FT.TRX_AMOUNT), 0) AMOUNT " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TC_GROUP = 40 AND NET_AMOUNT <> 0 and BILL_NO = " + reader["BILL_NO"].ToString();
sqlDescuentos = "SELECT NVL(SUM(FT.TRX_AMOUNT), 0) AMOUNT " +
"FROM FINANCIAL_TRANSACTIONS FT INNER JOIN TRX$_CODES TC ON TC.TRX_CODE = FT.TRX_CODE " +
"WHERE RESV_NAME_ID = " + reader["RESV_NAME_ID"] + " and FT.TC_GROUP NOT IN (70, 80, 90) AND NET_AMOUNT < 0 and BILL_NO = " + reader["BILL_NO"].ToString();
command = connection.CreateCommand();
command.CommandText = sqlTrx;
OracleDataReader subReader = command.ExecuteReader();
int j = 0;
string articulo;
while (subReader.Read())
{
j++;
try
{
articulo = mapeoItems[subReader["TRX_CODE"].ToString()];
}
catch
{
articulo = subReader["TRX_CODE"].ToString();
}
var bved = new BoletaVentaDetalle()
{
Correlativo = i,
Secuencia = j,
Articulo = articulo,
Cantidad = Convert.ToInt32(subReader["QUANTITY"]),
Precio = decimal.Round(Convert.ToDecimal(subReader["PRICE_PER_UNIT"]), 2),
Subtotal = decimal.Round(Convert.ToDecimal(subReader["NET_AMOUNT"]) / 1.19m, 2),
Iva = decimal.Round(Convert.ToDecimal(subReader["NET_AMOUNT"]) * 0.19m, 2),
Fecha = subReader["TRX_DATE"].ToString(),
Total = decimal.Round(Convert.ToDecimal(subReader["GROSS_AMOUNT"]), 2)
};
XmlFormatter.PrintDetail(xDoc, path, i, j);
XmlFormatter.PrintDetailElements(xDoc, path, tipoDocumento, bved, i, j);
}
//PRINT PAGOS
command = connection.CreateCommand();
command.CommandText = sqlPagos;
subReader = command.ExecuteReader();
j = 0;
while (subReader.Read())
{
j++;
try
{
articulo = mapeoItems[subReader["TRX_CODE"].ToString()];
}
catch
{
articulo = subReader["TRX_CODE"].ToString();
}
var bvep = new BoletaVentaPago()
{
Correlativo = i,
Linea = j,
TrxCode = articulo,
BillNo = Convert.ToInt32(subReader["BILL_NO"]),
Fecha = subReader["BUSINESS_DATE"].ToString(),
Monto = Convert.ToDecimal(subReader["TRX_AMOUNT"])
};
XmlFormatter.PrintPayment(xDoc, path, i, j);
XmlFormatter.PrintPaymentElements(xDoc, path, tipoDocumento, bvep, i, j, true);
}
//Print VALORES(DESCUENTO)
command = connection.CreateCommand();
command.CommandText = sqlDescuentos;
subReader = command.ExecuteReader();
subReader.Read();
var bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = -1,
Monto = Convert.ToDecimal(subReader["AMOUNT"]),
Nombre = "DESCUENTO",
Orden = 2,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 1);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 1);
//PRINT VALORES(EXENTO - PAIDOUTS)
command = connection.CreateCommand();
command.CommandText = sqlPaidouts;
subReader = command.ExecuteReader();
subReader.Read();
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 1,
Monto = Convert.ToDecimal(subReader["AMOUNT"]),
Nombre = "EXENTO",
Orden = 3,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 2);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 2);
//PRINT VALORES(IVA)
command.Dispose();
command.CommandText = sqlIva;
subReader = command.ExecuteReader();
subReader.Read();
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 0,
Monto = Convert.ToDecimal(subReader["AMOUNT"]),
Nombre = "IVA",
Orden = 5,
Porcentaje = 19m
};
XmlFormatter.PrintValues(xDoc, path, i, 3);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 3);
//PRINT VALORES(NBASE)
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 0,
Monto = Convert.ToDecimal(reader["TOTAL_GROSS"]),
Nombre = "NBASE",
Orden = 6,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 4);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 4);
//PRINT VALORES(NETO)
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 0,
Monto = Convert.ToDecimal(reader["TOTAL_NET"]),
Nombre = "NETO",
Orden = 4,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 5);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 5);
//PRINT VALORES(PROPINA)
command.Dispose();
command.CommandText = sqlPropina;
subReader = command.ExecuteReader();
subReader.Read();
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 1,
Monto = Convert.ToDecimal(subReader["AMOUNT"]),
Nombre = "PROPINA",
Orden = 1,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 6);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 6);
//PRINT VALORES(SPA)
command.Dispose();
command.CommandText = sqlSpa;
subReader = command.ExecuteReader();
subReader.Read();
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 1,
Monto = Convert.ToDecimal(subReader["AMOUNT"]),
Nombre = "SPA",
Orden = 7,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 7);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 7);
//PRINT VALORES(TOTAL)
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 0,
Monto = Convert.ToDecimal(reader["TOTAL_NET"]),
Nombre = "TOTAL",
Orden = 8,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 8);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 8);
//PRINT VALORES(TRANSBANK)
command.Dispose();
command.CommandText = sqlTransbank;
subReader = command.ExecuteReader();
subReader.Read();
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 0,
Monto = Convert.ToDecimal(subReader["AMOUNT"]),
Nombre = "TRANSBANK",
Orden = 9,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 9);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 9);
//PRINT VALORES(TRANSIVA)
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 0,
Monto = 0,
Nombre = "TRANSIVA",
Orden = 11,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 10);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 10);
//PRINT VALORES(TRANSNETO)
bvev = new BoletaVentaValores()
{
Correlativo = i,
Factor = 0,
Monto = 0,
Nombre = "TRANSNETO",
Orden = 10,
Porcentaje = 0
};
XmlFormatter.PrintValues(xDoc, path, i, 11);
XmlFormatter.PrintValueElements(xDoc, path, tipoDocumento, bvev, i, 11);
}
XmlFormatter.RenameXmlNodes(xDoc, path);
log.W("Finaliza procesamiento " + tipoDocumento + " - " + dtpDesde.Value.ToString("dd/MM/yyyy"));
dtpDesde.Enabled = true;
btnGenerar.Enabled = true;
btnSalir.Enabled = true;
Cursor.Current = Cursors.Default;
}
catch (Exception ex)
{
log.W(ex.Message + "|" + ex.InnerException.Message);
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("Archivo de configuración no encontrado, contacte a su proveedor.");
log.W("Archivo de configuración no encontrado");
}
}
Cursor.Current = Cursors.Default;
}
