public void ExportCfmIdle(CfmIdleReportModel model) { string pathFile = ""; pathFile = CreateXlsCfmIdle(model); var newFile = new FileInfo(pathFile); var fileName = Path.GetFileName(pathFile); string attachment = string.Format("attachment; filename={0}", fileName); Response.Clear(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.WriteFile(newFile.FullName); Response.Flush(); newFile.Delete(); Response.End(); }
public ActionResult Index() { var model = new CfmIdleReportModel(); model.MainMenu = _mainMenu; model.CurrentLogin = CurrentUser; model.SearchView.FromDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); model.SearchView.ToDate = DateTime.Today; var filter = new CfmIdleGetByParamInput(); filter.FromDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); filter.ToDate = DateTime.Today; var data = _cfmIdleReportBLL.GetCfmIdle(filter); var ListData = Mapper.Map <List <CfmIdleVehicle> >(data); model.ListCfmIdle = ListData; foreach (var item in model.ListCfmIdle) { var CfmIdleVehicle = new CfmIdleVehicle(); var today = DateTime.Today; var StartIdle = (decimal)(today - new DateTime(1900, 1, 1)).TotalDays; var EndIdle = (decimal)(today - new DateTime(1900, 1, 1)).TotalDays; if (item.StartIdle.HasValue) { StartIdle = (decimal)(item.StartIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays; } if (item.EndIdle.HasValue) { EndIdle = (decimal)(item.EndIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays; } item.IdleDuration = Math.Round((decimal)(EndIdle - StartIdle) / 30, 2); item.TotalMonthly = Math.Round((decimal)(item.IdleDuration * (item.MonthlyInstallment.HasValue ? item.MonthlyInstallment : 0)), 2); var Ctf = _ctfBLL.GetCtf().Where(x => (x.PoliceNumber == null ? "" : x.PoliceNumber.ToUpper()) == (item.PoliceNumber == null ? "" : item.PoliceNumber.ToUpper())).FirstOrDefault(); if (Ctf != null) { item.GroupLevel = Ctf.GroupLevel; } } var GrandTotal = new CfmIdleVehicle { PoliceNumber = "", Note = "GrandTotal", CreatedDate = DateTime.Today, IdleDuration = model.ListCfmIdle.Sum(x => x.IdleDuration), TotalMonthly = model.ListCfmIdle.Sum(x => x.TotalMonthly) }; if (model.ListCfmIdle != null && model.ListCfmIdle.Count > 0) { var result = model.ListCfmIdle.GroupBy(x => x.PoliceNumber).Select(grouping => new CfmIdleVehicle { PoliceNumber = grouping.First().PoliceNumber, Note = "SubTotal", CreatedDate = DateTime.Today, IdleDuration = grouping.Sum(x => x.IdleDuration), TotalMonthly = grouping.Sum(x => x.TotalMonthly) }).ToList(); foreach (var itemResult in result) { model.ListCfmIdle.Add(itemResult); } } model.ListCfmIdle = model.ListCfmIdle.OrderBy(x => x.PoliceNumber).ToList(); if (model.ListCfmIdle != null && model.ListCfmIdle.Count > 0) { model.ListCfmIdle.Add(GrandTotal); } return(View(model)); }
private string CreateXlsCfmIdle(CfmIdleReportModel model) { //get data var data = GetVehicleData(model.SearchView); foreach (var item in data) { var CfmIdleVehicle = new CfmIdleVehicle(); var StartIdle = (decimal)(item.StartIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays - 1; var today = DateTime.Today; var EndIdle = (decimal)(today - new DateTime(1900, 1, 1)).TotalDays + 1; if (item.EndIdle.HasValue) { EndIdle = (decimal)(item.EndIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays + 1; } item.IdleDuration = Math.Round((decimal)(EndIdle - StartIdle) / 30, 2); item.TotalMonthly = Math.Round((decimal)(item.IdleDuration * item.MonthlyInstallment), 2); var Ctf = _ctfBLL.GetCtf().Where(x => (x.PoliceNumber == null ? "" : x.PoliceNumber.ToUpper()) == (item.PoliceNumber == null ? "" : item.PoliceNumber.ToUpper())).FirstOrDefault(); if (Ctf != null) { item.GroupLevel = Ctf.GroupLevel; } } var GrandTotal = new CfmIdleVehicle { PoliceNumber = "", Note = "GrandTotal", CreatedDate = DateTime.Today, IdleDuration = data.Sum(x => x.IdleDuration), TotalMonthly = data.Sum(x => x.TotalMonthly) }; if (data.Count > 0 && data != null) { var result = data.GroupBy(x => x.PoliceNumber).Select(grouping => new CfmIdleVehicle { PoliceNumber = grouping.First().PoliceNumber, Note = "SubTotal", CreatedDate = DateTime.Today, IdleDuration = grouping.Sum(x => x.IdleDuration), TotalMonthly = grouping.Sum(x => x.TotalMonthly) }).ToList(); foreach (var itemResult in result) { data.Add(itemResult); } } data = data.OrderBy(x => x.PoliceNumber).ToList(); if (data.Count > 0 && data != null) { data.Add(GrandTotal); } var slDocument = new SLDocument(); //title slDocument.SetCellValue(1, 1, "CFM IDLE Report"); slDocument.MergeWorksheetCells(1, 1, 1, 16); //create style SLStyle valueStyle = slDocument.CreateStyle(); valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center); valueStyle.Font.Bold = true; valueStyle.Font.FontSize = 18; slDocument.SetCellStyle(1, 1, valueStyle); //create header slDocument = CreateHeaderCfmIdle(slDocument); //create data slDocument = CreateDataExcelCfmIdle(slDocument, data); var fileName = "Cfm_Idle_Report" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx"; var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName); slDocument.SaveAs(path); return(path); }
public PartialViewResult ListCfmIdleVehicle(CfmIdleReportModel model) { model.ListCfmIdle = new List <CfmIdleVehicle>(); model.ListCfmIdle = GetVehicleData(model.SearchView); foreach (var item in model.ListCfmIdle) { var CfmIdleVehicle = new CfmIdleVehicle(); var today = DateTime.Today; var StartIdle = (decimal)(today - new DateTime(1900, 1, 1)).TotalDays; var EndIdle = (decimal)(today - new DateTime(1900, 1, 1)).TotalDays; if (item.StartIdle.HasValue) { StartIdle = (decimal)(item.StartIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays; } if (item.EndIdle.HasValue) { EndIdle = (decimal)(item.EndIdle.Value.Date - new DateTime(1900, 1, 1)).TotalDays; } item.IdleDuration = Math.Round((decimal)(EndIdle - StartIdle) / 30, 2); item.TotalMonthly = Math.Round((decimal)(item.IdleDuration * (item.MonthlyInstallment.HasValue ? item.MonthlyInstallment : 0)), 2); var Ctf = _ctfBLL.GetCtf().Where(x => (x.PoliceNumber == null ? "" : x.PoliceNumber.ToUpper()) == (item.PoliceNumber == null ? "" : item.PoliceNumber.ToUpper())).FirstOrDefault(); if (Ctf != null) { item.GroupLevel = Ctf.GroupLevel; } } var GrandTotal = new CfmIdleVehicle { PoliceNumber = "", Note = "GrandTotal", CreatedDate = DateTime.Today, IdleDuration = model.ListCfmIdle.Sum(x => x.IdleDuration), TotalMonthly = model.ListCfmIdle.Sum(x => x.TotalMonthly) }; if (model.ListCfmIdle != null && model.ListCfmIdle.Count > 0) { var result = model.ListCfmIdle.GroupBy(x => x.PoliceNumber).Select(grouping => new CfmIdleVehicle { PoliceNumber = grouping.First().PoliceNumber, Note = "SubTotal", CreatedDate = DateTime.Today, IdleDuration = grouping.Sum(x => x.IdleDuration), TotalMonthly = grouping.Sum(x => x.TotalMonthly) }).ToList(); foreach (var itemResult in result) { model.ListCfmIdle.Add(itemResult); } } model.ListCfmIdle = model.ListCfmIdle.OrderBy(x => x.PoliceNumber).ToList(); if (model.ListCfmIdle != null && model.ListCfmIdle.Count > 0) { model.ListCfmIdle.Add(GrandTotal); } return(PartialView("_ListCfmIdleVehicle", model)); }