public static ResumenFinal getResumenFinal(edificio e, DateTime fechaDesde, DateTime fechaHasta)
{
try
{
admEntities db = Datos.getDB();
ResumenFinal resumenFinal = new ResumenFinal();
fechaHasta = DateTime.Parse(fechaHasta.AddMonths(1).AddDays(-fechaHasta.Day).Day + "/" + fechaHasta.Month + "/" + fechaHasta.Year);
var sectoresDB = db.sector.ToList();
var facturas = db.factura.Where(x => x.dir_edificio == e.direccion && x.fecha >= fechaDesde && x.fecha <= fechaHasta).ToList();
//var facturasAgrupadas = facturas.GroupBy(x => x.fecha.ToString("MM yyyy")).Select(x=> new { Importe = x.Sum(y=> y.importe) });
DateTime tmpFecha = fechaDesde;
List<Periodo> periodos = new List<Periodo>();
bool seguir = true;
do
{
List<Sector> sectores = new List<Sector>();
var Conceptos = db.Database.SqlQuery<Conceptos>(@"select s.descripcion 'Sector', f.numero_factura 'NumeroFactura', p.razon_social 'Proveedor', concat(tg.descripcion, ' ', f.detalle) 'concepto', f.importe from factura f left join provedor p on p.razon_social = f.razon_provedor join sector s on id_sector = s.idsector join tipo_gasto tg on f.id_tipogasto = tg.idtipo_gasto where dir_edificio = '" + e.direccion + "' and month(f.fecha) = " + tmpFecha.Month + " and year(f.fecha) = " + tmpFecha.Year).ToList();
foreach(var c in Conceptos.OrderBy(x=> x.Sector))
{
var s = sectores.Where(x => x.Nombre == c.Sector).SingleOrDefault();
if (s == null)
{
Sector sect = new Sector();
sect.Nombre = c.Sector;
sect.Importe += c.Importe;
sectores.Add(sect);
}
else
s.Importe += c.Importe;
}
sectores = OrdenarSectores(sectores,sectoresDB);
Periodo p = new Periodo();
p.Sectores = sectores;
p.Total = sectores.Sum(x => x.Importe);
p.Nombre = tmpFecha.Month + "/" + tmpFecha.Year;
if (sectores.Count > 0)
periodos.Add(p);
if (tmpFecha <= fechaHasta)
seguir = true;
else
seguir = false;
tmpFecha = tmpFecha.AddMonths(1);
}
while (seguir);
resumenFinal.Periodos = periodos;
var deudaDelEdificio = CatalogoDeudores.getDeudoresFromEdificioRegenerarUnidad(e, fechaHasta).Sum(x=> x.Importe);
var totalExpensas = periodos.Sum(x => x.Total);
var entradasDeCaja = getMovimientosCaja(e, fechaDesde, fechaHasta).Sum(x => x.importe > 0 ? (Double)x.importe : 0);
var salidasDeCaja = getMovimientosCaja(e, fechaDesde, fechaHasta).Sum(x => x.importe < 0 ? (Double)x.importe : 0);
if (salidasDeCaja < 0)
salidasDeCaja *= -1;
resumenFinal.CobroDeExpensas = totalExpensas - deudaDelEdificio;
resumenFinal.CobroFondosEspeciales = entradasDeCaja;
resumenFinal.TotalIngresos = totalExpensas - deudaDelEdificio + entradasDeCaja;
resumenFinal.GastosComunes = totalExpensas;
resumenFinal.GastosEspeciales = salidasDeCaja;
resumenFinal.TotalEgresos = totalExpensas + salidasDeCaja;
resumenFinal.SaldoEnCaja = resumenFinal.TotalIngresos - resumenFinal.TotalEgresos;
return resumenFinal;
}
catch (Exception ex)
{
Logger.Log.write(ex.InnerException == null ? ex.Message : ex.InnerException.Message);
throw ex;
}
}
private static void addResumenAnual_ResumenFinal(Document doc, ResumenFinal resumenFinal, DateTime fechaHasta)
{
if (resumenFinal.Periodos.Count == 0)
return;
var sectores = resumenFinal.Periodos.First().Sectores;
PdfPTable t = new PdfPTable(sectores.Count + 2);
t.WidthPercentage = 65;
List<float> myWitdths = new List<float>();
myWitdths.Add(40f);
for (int i = 0; i < sectores.Count; i++)
myWitdths.Add(30f);
myWitdths.Add(18f);
float[] widths = myWitdths.ToArray();
t.SetWidths(widths);
Paragraph p;
PdfPCell c = new PdfPCell();
int leading = 11;
c.Border = 0;
c.BorderWidthBottom = 1;
c.BorderWidthLeft = 1;
c.BorderWidthTop = 1;
c.FixedHeight = 20f;
p = new Paragraph(leading, "Período", calibri8B);
p.Alignment = Element.ALIGN_CENTER;
c.AddElement(p);
t.AddCell(c);
foreach (var r in resumenFinal.Periodos.First().Sectores)
{
c = new PdfPCell();
c.Border = 0;
c.BorderWidthBottom = 1;
c.BorderWidthLeft = 1;
c.BorderWidthTop = 1;
p = new Paragraph(leading, r.Nombre, calibri8B);
p.Alignment = Element.ALIGN_CENTER;
c.AddElement(p);
t.AddCell(c);
}
c = new PdfPCell();
c.Border = 0;
c.BorderWidthBottom = 1;
c.BorderWidthLeft = 1;
c.BorderWidthRight = 1;
c.BorderWidthTop = 1;
p = new Paragraph(leading, "Total", calibri8B);
p.Alignment = Element.ALIGN_CENTER;
c.AddElement(p);
t.AddCell(c);
leading = -3;
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri11B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
foreach (var r in resumenFinal.Periodos.First().Sectores)
{
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri11B);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
}
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri11B);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
leading = 9;
foreach (var r in resumenFinal.Periodos)
{
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, r.Nombre, calibri8N);
p.Alignment = Element.ALIGN_CENTER;
c.AddElement(p);
t.AddCell(c);
foreach (var s in r.Sectores)
{
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, s.Importe.ToString("n2"), calibri8N);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
}
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, r.Sectores.Sum(x => x.Importe).ToString("n2"), calibri8N);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
}
doc.Add(t);
doc.Add(new Paragraph(30, " "));
t = new PdfPTable(4);
t.WidthPercentage = 65;
float[] widths2 = { 28f, 15f, 15f, 50f };
t.SetWidths(widths2);
leading = 11;
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Ingresos", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Cobro expensas", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, resumenFinal.CobroDeExpensas.ToString("n2"), calibri8N);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Cobro fondos especiales", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, resumenFinal.CobroFondosEspeciales.ToString("n2"), calibri8N);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Total Ingresos", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, resumenFinal.TotalIngresos.ToString("n2"), calibri8B);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
doc.Add(t);
//EGRESOS
doc.Add(new Paragraph(30, " "));
t = new PdfPTable(4);
t.WidthPercentage = 65;
float[] widths3 = { 28f, 15f, 15f, 50f };
t.SetWidths(widths3);
leading = 11;
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Egresos", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Gastos comunes", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, resumenFinal.GastosComunes.ToString("n2"), calibri8N);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Gastos especiales", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, resumenFinal.GastosEspeciales.ToString("n2"), calibri8N);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8N);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Total Egresos", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, resumenFinal.TotalEgresos.ToString("n2"), calibri8B);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
doc.Add(t);
doc.Add(new Paragraph(30, " "));
t = new PdfPTable(4);
t.WidthPercentage = 65;
float[] widths4 = { 42f, 1f, 15f, 50f };
t.SetWidths(widths4);
leading = 11;
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, "Disponibilidad al " + fechaHasta.ToShortDateString(), calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, resumenFinal.SaldoEnCaja.ToString("n2"), calibri8B);
p.Alignment = Element.ALIGN_RIGHT;
c.AddElement(p);
t.AddCell(c);
c = new PdfPCell();
c.Border = 0;
p = new Paragraph(leading, " ", calibri8B);
p.Alignment = Element.ALIGN_LEFT;
c.AddElement(p);
t.AddCell(c);
doc.Add(t);
}