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);
}