private string CreateXlsRptFuel(RptFuelByParamInput input)
{
//get data
List <RptFuelDto> data = _rptFuelBLL.GetRptFuel(input);
var listData = Mapper.Map <List <RptFuelItem> >(data);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Fuel Report Data");
slDocument.MergeWorksheetCells(1, 1, 1, 19);
//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 = CreateHeaderExcelDashboard(slDocument);
//create data
slDocument = CreateDataExcelDashboard(slDocument, listData, input);
var fileName = "RptFuel" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
public List <FUEL_REPORT_DATA> GetRptFuel(RptFuelByParamInput filter)
{
Expression <Func <FUEL_REPORT_DATA, bool> > queryFilter = PredicateHelper.True <FUEL_REPORT_DATA>();
if (filter != null)
{
if (filter.MonthFrom > 0)
{
queryFilter = queryFilter.And(c => c.REPORT_MONTH >= filter.MonthFrom);
}
if (filter.YearFrom > 0)
{
queryFilter = queryFilter.And(c => c.REPORT_YEAR >= filter.YearFrom);
}
if (filter.MonthTo > 0)
{
queryFilter = queryFilter.And(c => c.REPORT_MONTH <= filter.MonthTo);
}
if (filter.YearTo > 0)
{
queryFilter = queryFilter.And(c => c.REPORT_YEAR <= filter.YearTo);
}
if (!string.IsNullOrEmpty(filter.VehicleType))
{
queryFilter = queryFilter.And(c => c.VEHICLE_TYPE.ToUpper() == filter.VehicleType.ToUpper());
}
if (!string.IsNullOrEmpty(filter.Function))
{
queryFilter = queryFilter.And(c => c.FUNCTION.ToUpper() == filter.Function.ToUpper());
}
if (!string.IsNullOrEmpty(filter.CostCenter))
{
queryFilter = queryFilter.And(c => c.COST_CENTER.ToUpper() == filter.CostCenter.ToUpper());
}
if (!string.IsNullOrEmpty(filter.PoliceNumber))
{
queryFilter = queryFilter.And(c => c.POLICE_NUMBER.ToUpper() == filter.PoliceNumber.ToUpper());
}
if (!string.IsNullOrEmpty(filter.Regional))
{
queryFilter = queryFilter.And(c => c.REGIONAL.ToUpper() == filter.Regional.ToUpper());
}
}
return(_rptFuelRepository.Get(queryFilter, null, "").ToList());
}
private SLDocument CreateDataExcelDashboard(SLDocument slDocument, List <RptFuelItem> listData, RptFuelByParamInput input)
{
int iRow = 3; //starting row data
var dataFuel = _rptFuelBLL.GetRptFuelData();
foreach (var data in listData)
{
if (input.MonthFrom == 1)
{
input.MonthFrom = 12;
input.YearFrom = input.YearFrom - 1;
}
else
{
input.MonthFrom = input.MonthFrom - 1;
}
if (data.Odometer != 0)
{
var data_temp = dataFuel.Where(x => x.PoliceNumber == data.PoliceNumber && x.ReportMonth == input.MonthFrom && x.ReportYear == input.YearFrom).Select(x => x.Odometer).FirstOrDefault();
if (data_temp == 0)
{
data.Usage = data.Odometer;
if (data.Liter != 0)
{
data.kmlt = Math.Round(data.Usage / data.Liter, 2);
}
}
else
{
data.Usage = data.Odometer - data_temp;
if (data.Liter != 0)
{
data.kmlt = Math.Round(data.Usage / data.Liter, 2);
}
}
}
slDocument.SetCellValue(iRow, 1, data.PoliceNumber);
slDocument.SetCellValue(iRow, 2, data.Liter);
slDocument.SetCellValue(iRow, 3, data.Odometer);
slDocument.SetCellValue(iRow, 4, data.Usage);
slDocument.SetCellValue(iRow, 5, data.kmlt);
slDocument.SetCellValue(iRow, 6, data.Cost);
slDocument.SetCellValue(iRow, 7, data.FuelType);
slDocument.SetCellValue(iRow, 8, data.CostCenter);
slDocument.SetCellValue(iRow, 9, data.Function);
slDocument.SetCellValue(iRow, 10, data.Manufacturer);
slDocument.SetCellValue(iRow, 11, data.Models);
slDocument.SetCellValue(iRow, 12, data.Series);
slDocument.SetCellValue(iRow, 13, data.BodyType);
slDocument.SetCellValue(iRow, 14, data.VehicleType);
slDocument.SetCellValue(iRow, 15, data.VehicleUsage);
slDocument.SetCellValue(iRow, 16, data.Location);
slDocument.SetCellValue(iRow, 17, data.Regional);
slDocument.SetCellValue(iRow, 18, data.Month);
slDocument.SetCellValue(iRow, 19, data.ReportYear);
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 19);
slDocument.SetCellStyle(3, 1, iRow - 1, 19, valueStyle);
return(slDocument);
}
public List <RptFuelDto> GetRptFuel(RptFuelByParamInput filter)
{
var data = _RptFuelService.GetRptFuel(filter);
return(Mapper.Map <List <RptFuelDto> >(data));
}
