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 GenerarArchivo(SqlConnection connection)
{
lblStatus.Text = string.Empty;
dtpFechaDesde.Enabled = dtpFechaHasta.Enabled = btnVentasDetalle.Enabled = btnventasGenerales.Enabled = cbRango.Enabled = cbPuntoDeVenta.Enabled = false;
Cursor.Current = Cursors.WaitCursor;
const string pathCarpetaArchivosExportados = ".\\Exportados\\";
CrearDirectorioParaArchivosExportados(pathCarpetaArchivosExportados);
var path = cbRango.Checked ? string.Format(_imprimeDetalle ? "{0}{1}-{2}_{3}_Monticello_BoletasDeVentaDetalle.csv" : "{0}{1}-{2}_{3}_Monticello_BoletasDeVenta.csv",
pathCarpetaArchivosExportados,
dtpFechaDesde.Value.ToString("yyyyMMdd"),
dtpFechaHasta.Value.ToString("yyyyMMdd"),
cbPuntoDeVenta.Text) : string.Format(_imprimeDetalle ? "{0}{1}_{2}_Monticello_BoletasDeVentaDetalle.csv" : "{0}{1}_{2}_Monticello_BoletasDeVenta.csv",
pathCarpetaArchivosExportados,
dtpFechaDesde.Value.ToString("yyyyMMdd"),
cbPuntoDeVenta.Text.Replace(" ", ""));
EscribirTitulosColumnas(path);
//Calculo la cantidad de días a exportar
var cantDias = cbRango.Checked ? (dtpFechaHasta.Value - dtpFechaDesde.Value).TotalDays : 1;
var listaChecks = new List <MicrosCheck>();
for (var i = 0; i < cantDias; i++)
{
//Por cada día itero y extraigo la información sumarizada
try
{
var command = connection.CreateCommand();
var fecha = dtpFechaDesde.Value.AddDays(i).ToString("yyyyMMdd");
var fechaMasUnDia = dtpFechaDesde.Value.AddDays(i + 1).ToString("yyyyMMdd");
var query = "SELECT PCWSID, CONVERT(VARCHAR(10), FCRBSNZDATE, 103) FCRBSNZDATEDDMMYYYY, CONVERT(VARCHAR(10), FCRBSNZDATE, 112) FCRBSNZDATEYYYYMMDD, CONVERT(VARCHAR(5), " +
"FCRBSNZDATE, 108) HORAMIN, FCRINVNUMBER, MICROSCHKNUM, ROUND(SUBTOTAL2 / 1.19, 0) DISC, SUBTOTAL8 SUBTOTALCONIVA, TAXTTL1 IVA, EXTRAFIELD3 EDINUMBER " +
"FROM MICROSDB.FCR_INVOICE_DATA WHERE ((CONVERT(VARCHAR(10), FCRBSNZDATE, 112) = '" + fecha + "' AND CONVERT(VARCHAR(5), FCRBSNZDATE, 108) > '08:01') " +
"OR ((CONVERT(VARCHAR(10), FCRBSNZDATE, 112) = '" + fechaMasUnDia + "' AND CONVERT(VARCHAR(5), FCRBSNZDATE, 108) < '08:00'))) " + " AND EXTRAFIELD5 = " +
_puntoDeVenta + " ORDER BY 1, FCRBSNZDATE DESC";
//DEBUG
//Logger.Write(query);
command.CommandText = query;
var reader = command.ExecuteReader();
while (reader.Read())
{
//Itero por CABECERA CHECK uno por uno
//Totales para ventas generales
int totalAlimentosPorCheck = 0,
totalBebidasPorCheck = 0,
totalTabacosPorCheck = 0,
totalOtrosPorCheck = 0,
totalPropinasPorCheck = 0;
if (Convert.ToInt32(reader["FCRINVNUMBER"]) > _maxBol)
{
_maxBol = Convert.ToInt32(reader["FCRINVNUMBER"]);
}
if (Convert.ToInt32(reader["FCRINVNUMBER"]) < _minBol)
{
_minBol = Convert.ToInt32(reader["FCRINVNUMBER"]);
}
//Totales para ventas con detalle
//int totalVisa = 0, totalMaster = 0, totalAmerican = 0, totalDiners = 0,
// totalRedCompra = 0, totalComplimentary = 0, totalCargoHab = 0,
// totalCredEmpresa = 0, totalFact = 0, totalCheque = 0,
// totalTransfDeposito = 0, totalEfectivo = 0, totalCigarros = 0;
var microsCheck = new MicrosCheck()
{
Pcws = reader["PCWSID"].ToString(),
CheckNumber = reader["MICROSCHKNUM"].ToString(),
EdiNumber = reader["EDINUMBER"].ToString(),
Fecha = reader["FCRBSNZDATEDDMMYYYY"].ToString(),
Hora = reader["HORAMIN"].ToString(),
TotalNeto = Convert.ToInt32(reader["SUBTOTALCONIVA"]) - Convert.ToInt32(reader["IVA"]),
VentasTotales = Convert.ToInt32(reader["SUBTOTALCONIVA"]),
Iva = Convert.ToInt32(reader["IVA"])
};
//var totalIvaPorCheck = 0.0;
var totalDescPorCheck = reader["DISC"];
var hora = reader["HORAMIN"].ToString();
var horaMasUnMinuto = DateTime.Parse(hora).AddMinutes(1).ToString("HH:mm");
var horaMenosUnMinuto = DateTime.Parse(hora).AddMinutes(-1).ToString("HH:mm");
var chkNum = reader["MICROSCHKNUM"];
var queryChecks = "SELECT CHECKID FROM MICROSDB.CHECKS WHERE CONVERT(VARCHAR(10), CHECKPOSTINGTIME, 112) = '" + reader["FCRBSNZDATEYYYYMMDD"] +
"' AND CONVERT(VARCHAR(5), CHECKPOSTINGTIME, 108) IN ('" + hora + "', '" + horaMenosUnMinuto + "', '" + horaMasUnMinuto + "') and CHECKNUMBER = " + chkNum; //Query para checks
//DEBUG
//Logger.Write(queryChecks);
command = connection.CreateCommand();
command.CommandText = queryChecks;
var subreaderChecks = command.ExecuteReader();
while (subreaderChecks.Read())
{
//Itero los detalles del check para acumular totales segregados
var checkId = subreaderChecks["CHECKID"];
var queryCheckDetail = "SELECT CHECK_DETAIL.CHECKDETAILID, FAMILY_GROUP.OBJECTNUMBER FAMGRP,CHECK_DETAIL.CHECKID, CHECK_DETAIL.DETAILINDEX, " +
"CHECK_DETAIL.DETAILTYPE, ROUND(CHECK_DETAIL.TOTAL / 1.19, 0) TOTALSINIVA, CHECK_DETAIL.TOTAL TOTAL, CHECK_DETAIL.SALESCOUNT, " +
"STRING_TABLE.STRINGTEXT, MENU_ITEM_DEFINITION.MENUITEMDEFID MENUITEMDEFID, MENU_ITEM_MASTER.OBJECTNUMBER OBJECTNUMBER " +
"FROM MICROSDB.CHECK_DETAIL CHECK_DETAIL INNER JOIN MICROSDB.MENU_ITEM_DETAIL MENU_ITEM_DETAIL ON CHECK_DETAIL.CHECKDETAILID = " +
"MENU_ITEM_DETAIL.CHECKDETAILID INNER JOIN MICROSDB.MENU_ITEM_DEFINITION MENU_ITEM_DEFINITION ON MENU_ITEM_DEFINITION.MENUITEMDEFID = " +
"MENU_ITEM_DETAIL.MENUITEMDEFID INNER JOIN MICROSDB.STRING_TABLE STRING_TABLE ON STRING_TABLE.STRINGNUMBERID = MENU_ITEM_DEFINITION.NAME1ID INNER JOIN " +
"MICROSDB.MENU_ITEM_MASTER MENU_ITEM_MASTER ON MENU_ITEM_MASTER.MENUITEMMASTERID = MENU_ITEM_DEFINITION.MENUITEMMASTERID " +
"INNER JOIN MICROSDB.FAMILY_GROUP FAMILY_GROUP ON FAMILY_GROUP.FAMGRPID = MENU_ITEM_MASTER.FAMGRPID " +
"WHERE CHECKID = " + checkId + " AND TOTAL <> 0 " + "ORDER BY CHECK_DETAIL.DETAILINDEX";
//DEBUG
//Logger.Write(queryCheckDetail);
command = connection.CreateCommand();
command.CommandText = queryCheckDetail;
var subreaderDetail = command.ExecuteReader();
//CHECK DETAILS
var totalPorCheck = 0;
var totalSinIvaPorCheck = 0;
while (subreaderDetail.Read())
{
//DEBUG
//Logger.Write(microsCheck.EdiNumber + ";" + microsCheck.CheckNumber + ";" + subreaderDetail["FAMGRP"] + ";" + subreaderDetail["TOTALSINIVA"]);
var encontrado = false;
//PROCESO DETALLE POR CADA CHECK
var lFGAlimentos = new List <int>
{
3, 60, 62, 64, 66, 101, 102, 103, 104, 105, 107, 108, 109, 110,
111, 112, 114, 116, 117, 122, 123, 124, 125, 126, 132, 133, 160,
161, 162, 163, 164, 167, 179, 171, 224, 250, 254, 262, 263, 301
};
var lFGBebidas = new List <int>
{
1, 61, 120, 123, 125, 126, 128, 129, 165, 166, 168, 170, 200,
201, 203, 204, 205, 206, 207, 209, 210, 211, 212, 213, 214, 215,
216, 223, 225, 228, 230, 230, 256, 257, 258, 259, 260, 261, 263
};
var lFGTabacos = new List <int>
{
121
};
var lFGOtros = new List <int>
{
120, 125, 126, 130, 131, 302, 303
};
foreach (var fgAlimento in lFGAlimentos)
{
if (Convert.ToInt32(subreaderDetail["FAMGRP"]) == fgAlimento)
{
totalAlimentosPorCheck += Convert.ToInt32(subreaderDetail["TOTALSINIVA"]);
encontrado = true;
}
}
if (!encontrado)
{
foreach (var fgBebida in lFGBebidas)
{
if (Convert.ToInt32(subreaderDetail["FAMGRP"]) == fgBebida)
{
totalBebidasPorCheck += Convert.ToInt32(subreaderDetail["TOTALSINIVA"]);
encontrado = true;
}
}
}
if (!encontrado)
{
foreach (var fgTabaco in lFGTabacos)
{
if (Convert.ToInt32(subreaderDetail["FAMGRP"]) == fgTabaco)
{
totalTabacosPorCheck += Convert.ToInt32(subreaderDetail["TOTALSINIVA"]);
encontrado = true;
}
}
}
if (!encontrado)
{
foreach (var fgOtros in lFGOtros)
{
if (Convert.ToInt32(subreaderDetail["FAMGRP"]) == fgOtros)
{
totalOtrosPorCheck += Convert.ToInt32(subreaderDetail["TOTALSINIVA"]);
}
}
}
totalSinIvaPorCheck += Convert.ToInt32(subreaderDetail["TOTALSINIVA"]);
totalPorCheck += Convert.ToInt32(subreaderDetail["TOTAL"]);
}
if (_imprimeDetalle)
{
//PAGOS
var queryPayments = "SELECT CHECK_DETAIL.CHECKDETAILID, CHECK_DETAIL.CHECKID, CHECK_DETAIL.DETAILINDEX, " +
"CHECK_DETAIL.DETAILTYPE, CHECK_DETAIL.TOTAL TOTAL, CHECK_DETAIL.SALESCOUNT, TENDER_MEDIA_DETAIL.TENDMEDID TENDMEDID, TENDER_MEDIA.OBJECTNUMBER TENDERMEDIA " +
"FROM CHECK_DETAIL INNER JOIN TENDER_MEDIA_DETAIL ON CHECK_DETAIL.CHECKDETAILID = TENDER_MEDIA_DETAIL.CHECKDETAILID " +
"INNER JOIN TENDER_MEDIA ON TENDER_MEDIA_DETAIL.TENDMEDID = TENDER_MEDIA.TENDMEDID " +
"WHERE CHECKID = " + checkId + " ORDER BY CHECK_DETAIL.DETAILINDEX";
//DEBUG
//Logger.Write(queryPayments);
command = connection.CreateCommand();
command.CommandText = queryPayments;
var subreaderPayments = command.ExecuteReader();
microsCheck.Payments = new Dictionary <int, int>();
while (subreaderPayments.Read())
{
//DEBUG
//Logger.Write(checkId + ": " + Convert.ToInt32(subreaderPayments["TENDERMEDIA"]) + ", " + subreaderPayments["TOTAL"]);
if (!microsCheck.Payments.ContainsKey(Convert.ToInt32(subreaderPayments["TENDERMEDIA"])))
{
if (microsCheck.Payments.Count > 0)
//Porque si tengo por ejemplo VISA y luego resto efectivo, debería restar a VISA el efectivo (?)
{
var totalPayment = Convert.ToInt32(subreaderPayments["TOTAL"]);
var first = microsCheck.Payments.Keys.First();
microsCheck.Payments[first] += totalPayment;
if (totalPayment < 0)
{
totalPropinasPorCheck += Math.Abs(totalPayment);
}
}
else if (Convert.ToInt32(subreaderPayments["TOTAL"]) > 0)
{
microsCheck.Payments.Add(Convert.ToInt32(subreaderPayments["TENDERMEDIA"]), Convert.ToInt32(subreaderPayments["TOTAL"]));
}
}
else
{
microsCheck.Payments[Convert.ToInt32(subreaderPayments["TENDERMEDIA"])] += Convert.ToInt32(subreaderPayments["TOTAL"]);
}
}
}
}
microsCheck.Alimentos = Convert.ToInt32(totalAlimentosPorCheck /* / 1.19*/);
microsCheck.BebidasSAlcohol = Convert.ToInt32(totalBebidasPorCheck /* / 1.19*/);
microsCheck.Tabacos = Convert.ToInt32(totalTabacosPorCheck /* / 1.19*/);
microsCheck.Otros = Convert.ToInt32(totalOtrosPorCheck /* / 1.19*/);;
microsCheck.Descuentos = Math.Abs(Convert.ToInt32(reader["DISC"]));
microsCheck.Propinas = totalPropinasPorCheck;
//DEBUG
//Logger.Write(microsCheck.Fecha + " - " + Convert.ToDateTime(microsCheck.Fecha, new CultureInfo("es-AR")).ToString("yyyyMMdd") + " - " + microsCheck.EdiNumber);
//Logger.Write(microsCheck.EdiNumber + " - " + microsCheck.CheckNumber + " - " + microsCheck.Alimentos + " - " + microsCheck.BebidasSAlcohol);
listaChecks.Add(microsCheck);
}
}
catch (Exception ex)
{
lblStatus.ForeColor = Color.DarkRed;
lblStatus.Text = "Error al generar archivos";
Cursor.Current = Cursors.Default;
lblStatus.Visible = dtpFechaDesde.Enabled = btnVentasDetalle.Enabled = btnventasGenerales.Enabled =
cbRango.Enabled = lblStatus.Visible = cbPuntoDeVenta.Enabled = true;
dtpFechaHasta.Enabled = cbRango.Checked;
pbProcesando.Visible = false;
Logger.Write("ERROR: " + ex.Message);
}
}
for (var i = 0; i < cantDias; i++)
{
//Por cada día itero y extraigo la información sumarizada
var fecha = dtpFechaDesde.Value.AddDays(i).ToString("yyyyMMdd");
var fechaMasUnDia = dtpFechaDesde.Value.AddDays(i + 1).ToString("yyyyMMdd");
EscribirArchivoSumarizadoPorFecha(path, listaChecks, fecha, fechaMasUnDia);
lblStatus.ForeColor = Color.Green;
lblStatus.Text = "Archivos generados exitosamente";
Logger.Write("Archivos generados exitosamente");
}
dtpFechaDesde.Enabled = btnVentasDetalle.Enabled = btnventasGenerales.Enabled = cbRango.Enabled = lblStatus.Visible = cbPuntoDeVenta.Enabled = true;
dtpFechaHasta.Enabled = cbRango.Checked;
Cursor.Current = Cursors.Default;
pbProcesando.Visible = false;
}
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;
}
}
