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