public FileResult ReportedMovementsToExcel(int accountStatusReportID)
{
AccountStatusReport Reporte = db.AccountStatusReport.Find(accountStatusReportID);
List <TableToExportExcel> datatables = new List <TableToExportExcel>();
//Getting house data to exportr
VMHouse vmHouse = Reporte.house.getVM();
var houseList = new List <VMHouse>();
houseList.Add(vmHouse);
DataTable dtHOuse = ExcelTools.listToDatatable <VMHouse>(houseList);
//Exporting movements data
List <VMReportedMovementes> vmReporte = VMReportedMovementes.listToVMReportedMovements(Reporte.ReportedMovements.ToList());
DataTable dtReporte = ExcelTools.listToDatatable <VMReportedMovementes>(vmReporte);
//Generating excel
string heading = "Monthly Statement " + Reporte.dateMonth.ToString("MMMM-yyyy");
datatables.Add(new TableToExportExcel(dtHOuse, "House"));
datatables.Add(new TableToExportExcel(dtReporte, heading));
ExcelPackage package = ExcelTools.exportToExcel(datatables, heading);
byte[] bytesFile = package.GetAsByteArray();
//Preparing download file
string usrName = Reporte.house.ApplicationUser.UserName;
usrName = usrName.Substring(0, usrName.IndexOf('@'));
return(File(bytesFile, ExcelTools.EXCEL_MIME_TYPE, String.Format("{0}_{1}_{2}{3}", heading, usrName, Reporte.house.name, ExcelTools.EXCEL_FORMAT)));
}
public FileResult HouseMovementsToExcel(int houseID, DateTime?fecha)
{
House house = db.Houses.Find(houseID);
DateTime fechaConArgumentos = new DateTime();
if (fecha == null)
{
//Si no viene fecha, se establece por defecto el mes actual
fechaConArgumentos = DateTime.Now;
fechaConArgumentos = new DateTime(fechaConArgumentos.Year, fechaConArgumentos.Month, 1);
}
else
{
//Si dentro de la transaccion viene con fecha, se ignora la hora
fechaConArgumentos = fecha.Value;
}
//Getting data to export
List <Movement> movimientos = new List <Movement>();
if (User.IsInRole(ApplicationUser.RoleNames.ADMINISTRADOR))
{
movimientos = db.Movements.Where(mov => mov.houseID == houseID && mov.transactionDate.Month == fechaConArgumentos.Month &&
mov.transactionDate.Year == fechaConArgumentos.Year).OrderBy(move => move.transactionDate).ToList();
}
else
{
movimientos = db.Movements.Where(mov => mov.houseID == houseID && mov.state == true && mov.transactionDate.Year == fechaConArgumentos.Year &&
mov.transactionDate.Month == fechaConArgumentos.Month).OrderBy(move => move.transactionDate).ToList();
}
//Preparing data to introduce to excel
List <VMReportedMovementes> vmMovementes = ReportedMovements.VMReportedMovementes.listToVMReportedMovements(movimientos);
DataTable dtMovementes = ExcelTools.listToDatatable <VMReportedMovementes>(vmMovementes);
List <TableToExportExcel> datatables = new List <TableToExportExcel>();
//Getting house data to introduce in excel
VMHouse vmHouse = house.getVM();
var houseList = new List <VMHouse>();
houseList.Add(vmHouse);
DataTable dtHOuse = ExcelTools.listToDatatable <VMHouse>(houseList);
//Generating excel
string heading = "Monthly Statement " + fecha.Value.ToString("MMMM-yyyy");
datatables.Add(new TableToExportExcel(dtHOuse, "House"));
datatables.Add(new TableToExportExcel(dtMovementes, "Movements of House" + fecha.Value.ToString("MMMM-yyyy")));
ExcelPackage package = ExcelTools.exportToExcel(datatables, heading);
byte[] bytesFile = package.GetAsByteArray();
//Preparing download file
string usrName = house.ApplicationUser.UserName;
usrName = usrName.Substring(0, usrName.IndexOf('@'));
return(File(bytesFile, ExcelTools.EXCEL_MIME_TYPE, String.Format("{0}_{1}_{2}{3}", heading, usrName, house.name, ExcelTools.EXCEL_FORMAT)));
}
public FileResult ProfitAndLossToExcel(String id, DateTime?fecha1, DateTime?fecha2, bool onlyContributions = false, int houseID = 0)
{
House house = null;
ApplicationUser owner;
//Re-Create the report
if (houseID == 0) //If house is not specified, just
{
owner = db.Users.Find(id);
}
else //If house is specified, the owner is find from it
{
house = db.Houses.Find(houseID);
owner = house.ApplicationUser;
}
ApplicationUser.ProfitAndLossReport profitAndLossReport = owner.generateProfitAndLossReport(fecha1.Value, fecha2.Value, houseID);
//Pre-processed to be exported
List <VMProfitAndLossRow> vmProfitList = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.profitReport.ToList());
DataTable dtProfit = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmProfitList);
List <VMProfitAndLossRow> vmLossList = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.lossReport.ToList());
DataTable dtLoss = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmLossList);
List <VMProfitAndLossRow> vmContribs = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.contributionReport.ToList());
DataTable dtContribs = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmContribs);
List <TableToExportExcel> datatables = new List <TableToExportExcel>();
if (house != null) //If house was specified, house data is added to the Excel File
{
//Getting house data to exportr
VMHouse vmHouse = house.getVM();
var houseList = new List <VMHouse>();
houseList.Add(vmHouse);
DataTable dtHOuse = ExcelTools.listToDatatable <VMHouse>(houseList);
datatables.Add(new TableToExportExcel(dtHOuse, "House"));
}
if (onlyContributions)
{
datatables.Add(new TableToExportExcel(dtContribs, "Contributions"));
}
else
{
datatables.Add(new TableToExportExcel(dtProfit, "Income"));
datatables.Add(new TableToExportExcel(dtLoss, "Loss"));
}
//Exported
ExcelPackage package = ExcelTools.exportToExcel(datatables, onlyContributions? "Contributions" : "Profit and Loss");
var reportTotals = profitAndLossReport.totals;
ExcelWorksheet worksheet = package.Workbook.Worksheets.ElementAt(0);
int startRow = worksheet.Dimension.End.Row + 2;
worksheet.Cells["A" + (startRow)].Value = "Totals";
worksheet.Cells["A" + (startRow)].Style.Font.Size = 20;
startRow++;
int counter = startRow;
foreach (var item in reportTotals)
{
if (onlyContributions && item.concept == ProfitAndLossTotalConcepts.TotalContribution ||
!onlyContributions && (item.concept != ProfitAndLossTotalConcepts.TotalContribution))
{
worksheet.Cells["A" + counter].Value = item.conceptName;
worksheet.Cells["B" + counter].Value = item.total;
counter++;
}
}
// format cells - add borders
using (ExcelRange r = worksheet.Cells[startRow, 1, counter - 1, 2])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
worksheet.InsertRow(1, 2);
worksheet.Cells["A1"].Value = "From";
worksheet.Cells["B1"].Value = fecha1.Value.ToString("dd/MMM/yyyy");
worksheet.Cells["C1"].Value = "To";
worksheet.Cells["D1"].Value = fecha2.Value.ToString("dd/MMM/yyyy");
worksheet.Cells["A1"].Style.Font.Bold = true;
worksheet.Cells["C1"].Style.Font.Bold = true;
if (house == null) //House not specified, owner name is added in the header of the file
{
worksheet.Cells["E1"].Value = "Owner";
worksheet.Cells["E1"].Style.Font.Bold = true;
worksheet.Cells["F1"].Value = owner.OwnerName;
}
byte[] bytesFile = package.GetAsByteArray();
return(File(bytesFile, ExcelTools.EXCEL_MIME_TYPE, profitAndLossReport.ToString(onlyContributions) + ExcelTools.EXCEL_FORMAT));
}
