private void BuildAnual(Hoja hoja, DateTime iniDate) { var sMonthTitle = new Styles() { BackColor = Color.Gray, FontColor = Color.White, FontBold = true }.Update(s => s.Borders.SetGrid()); var sCredito = new Styles() { BackColor = Color.FromArgb(200, 255, 200) }.Update(s => s.Borders.SetGrid()); var sCreditoTotal = new Styles() { BackColor = Color.FromArgb(100, 255, 100), FontBold = true }.Update(s => s.Borders.SetGrid()); var sDebito = new Styles() { BackColor = Color.FromArgb(255, 230, 230) }.Update(s => s.Borders.SetGrid()); var sDebitoTotal = new Styles() { BackColor = Color.FromArgb(255, 200, 200), FontBold = true }.Update(s => s.Borders.SetGrid()); var movimientos = DAL.Session.Movimientos.Find(p => p.Fecha >= iniDate); var listaIdConceptosUsados = movimientos.Select(p => p.IdConcepto).Distinct(); var conceptos = DAL.Session.Conceptos.Find(p => listaIdConceptosUsados.Contains(p.Id)).OrderBy(p => p.Name); var conceptosCredito = conceptos.Where(p => p.EsCredito).Select(p => p.Id).ToArray(); var conceptosDebito = conceptos.Where(p => !p.EsCredito).Select(p => p.Id).ToArray(); var data = new DataInfo(); // HEADER var rowNum = 1; data.AddRow(new Row(rowNum, sMonthTitle)); var i = 0; var currentDate = iniDate; do { var colNum = 3 + i; currentDate = iniDate.AddMonths(i); var range = Ranges.GetRange(colNum, rowNum); data.AddColumn(new Column(colNum).Update(a => { a.Width = 11; a.Style.DataNumberFormat = "$ #,##0.00"; })); data.AddCell(new Cell(range, new Styles() { DataNumberFormat = "mmm-yyyy" }, $"'{currentDate:MMM-yyyy}")); i++; } while (currentDate.Month != DateTime.Now.Month || currentDate.Year != DateTime.Now.Year); rowNum++; // Separador data.AddRow(new Row(rowNum).Update(a => a.Height = 3)); rowNum++; // Lista Conceptos var conceptoRow = new Dictionary <Guid, int>(); data.AddColumn(new Column(1) { Width = 30 }); data.AddColumn(new Column(2) { Width = 0.3 }); foreach (var idConcepto in conceptosCredito) { var coord = $"A{rowNum}"; var oConcepto = conceptos.Single(p => p.Id == idConcepto); data.AddRow(new Row(rowNum, sCredito)); data.AddCell(new Cell(coord, oConcepto.Name)); conceptoRow.Add(oConcepto.Id, rowNum); rowNum++; } data.AddRow(new Row(rowNum, sCreditoTotal)); var subTotalCreditoRow = rowNum; rowNum++; // subtotales // Separador data.AddRow(new Row(rowNum).Update(a => a.Height = 3)); rowNum++; foreach (var idConcepto in conceptosDebito) { var coord = $"A{rowNum}"; var oConcepto = conceptos.Single(p => p.Id == idConcepto); data.AddRow(new Row(rowNum, sDebito)); data.AddCell(new Cell(coord, oConcepto.Name)); conceptoRow.Add(oConcepto.Id, rowNum); rowNum++; } data.AddRow(new Row(rowNum, sDebitoTotal)); var subTotalDebitoRow = rowNum; rowNum++; // Separador data.AddRow(new Row(rowNum).Update(a => a.Height = 3)); // Fill DATA i = 0; currentDate = iniDate; do { currentDate = iniDate.AddMonths(i); var c = 3 + i; decimal totalCredito = 0; decimal totalDebito = 0; foreach (var concepto in conceptoRow) { var monto = movimientos.Where(p => p.IdConcepto == concepto.Key && p.Fecha.Month == currentDate.Month && p.Fecha.Year == currentDate.Year).Sum(p => p.Monto); if (monto != 0) { var coords = Ranges.GetRange(c, concepto.Value); data.AddCell(new Cell(coords, Convert.ToDouble(monto))); } if (conceptos.Single(p => p.Id == concepto.Key).EsCredito) { totalCredito += monto; } else { totalDebito += monto; } } data.AddCell(new Cell(Ranges.GetRange(c, subTotalCreditoRow), Convert.ToDouble(totalCredito))); data.AddCell(new Cell(Ranges.GetRange(c, subTotalDebitoRow), Convert.ToDouble(totalDebito))); i++; } while (currentDate.Month != DateTime.Now.Month || currentDate.Year != DateTime.Now.Year); hoja.SetInfo(data); }
private void BuildMensual(Hoja hoja, DateTime desde, DateTime?hasta = null) { hasta = hasta ?? desde.AddMonths(1); var dbMovimientos = DAL.Session.Movimientos.Find(p => p.Fecha >= desde && p.Fecha < hasta).OrderBy(p => p.Fecha); var dbConceptos = DAL.Session.Conceptos.Find().ToArray(); // Estilos a usar var colCurrency = new Styles() { DataNumberFormat = "$ #,##0.00;[Red]$ #,##0.00" }; var rowHeaderStyle = new Styles() { FontBold = true, BackColor = Color.Gray }; var cellStyle = new Styles() { }.Update(a => a.Borders.SetRectangle()); // Datos var data = new DataInfo(); data.AddColumn(new Column(1) { Width = 10 }) .AddColumn(new Column(2) { Width = 45 }) .AddColumn(new Column(3, colCurrency) { Width = 13 }) .AddColumn(new Column(4, colCurrency) { Width = 13 }) .AddColumn(new Column(5, colCurrency) { Width = 13 }) .AddColumn(new Column(6) { Width = 85 }) .AddRow(new Row(1, rowHeaderStyle)) .AddCell(new Cell("A1", cellStyle, "Fecha")) .AddCell(new Cell("B1", cellStyle, "Concepto")) .AddCell(new Cell("C1", cellStyle, "Crédito")) .AddCell(new Cell("D1", cellStyle, "Débito")) .AddCell(new Cell("E1", cellStyle, "Saldo")) .AddCell(new Cell("F1", cellStyle, "Observaciones")); var row = 1; decimal saldo = 0; foreach (var dbMovimiento in dbMovimientos) { row++; var dbConcepto = dbConceptos.Single(p => p.Id == dbMovimiento.IdConcepto); saldo += (dbConcepto.EsCredito ? 1 : -1) * dbMovimiento.Monto; data.AddCell(new Cell($"A{row}", new Styles() { BackColor = Color.LightCyan }.Update(a => a.Borders.SetRectangle()), $"'{dbMovimiento.Fecha:dd/MM/yyyy}")) .AddCell(new Cell($"B{row}", new Styles() { BackColor = Color.LightCyan }.Update(a => a.Borders.SetRectangle()), dbConcepto.Name)) .AddCell(new Cell($"C{row}", new Styles() { BackColor = Color.LightGreen }.Update(a => a.Borders.SetRectangle()), dbConcepto.EsCredito ? Convert.ToDouble(dbMovimiento.Monto) : (object)"")) .AddCell(new Cell($"D{row}", new Styles() { BackColor = Color.LightSalmon }.Update(a => a.Borders.SetRectangle()), !dbConcepto.EsCredito ? Convert.ToDouble(dbMovimiento.Monto) : (object)"")) .AddCell(new Cell($"E{row}", new Styles() { BackColor = Color.LightSkyBlue }.Update(a => a.Borders.SetRectangle()), saldo)) .AddCell(new Cell($"F{row}", new Styles() { BackColor = Color.LightCyan }.Update(a => a.Borders.SetRectangle()), dbMovimiento.Observaciones)); } hoja.SetInfo(data); }