public ActionResult ConfigurationPartial(ConfigurationParamViewModel paramViewModel)
{
int roleGroupId = paramViewModel.Id;
PeriodeType pType = string.IsNullOrEmpty(paramViewModel.PeriodeType)
? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), paramViewModel.PeriodeType);
var request = new GetKpiTargetsConfigurationRequest();
request.PeriodeType = pType.ToString();
request.RoleGroupId = roleGroupId;
request.Year = paramViewModel.Year;
request.Month = paramViewModel.Month;
var response = _kpiTargetService.GetKpiTargetsConfiguration(request);
if (response.IsSuccess)
{
var viewModel = response.MapTo<ConfigurationKpiTargetsViewModel>();
viewModel.Year = request.Year;
viewModel.Month = request.Month;
viewModel.Years = _dropdownService.GetYears().MapTo<SelectListItem>();
viewModel.Months = _dropdownService.GetMonths().MapTo<SelectListItem>();
viewModel.PeriodeType = pType.ToString();
viewModel.FileName = this._ExportToExcel(viewModel);
return PartialView("Configuration/_" + pType.ToString(), viewModel);
}
return base.ErrorPage(response.Message);
}
public GetKpiTargetsConfigurationResponse GetKpiTargetsConfiguration(GetKpiTargetsConfigurationRequest request)
{
var response = new GetKpiTargetsConfigurationResponse();
try
{
var periodeType = string.IsNullOrEmpty(request.PeriodeType)
? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), request.PeriodeType);
var kpis = DataContext.Kpis
.Include(x => x.RoleGroup)
.Include(x => x.Measurement).ToList();
if(request.RoleGroupId>0){
kpis = kpis.Where(x => x.RoleGroup.Id == request.RoleGroupId).ToList();
var roleGroup = DataContext.RoleGroups.Single(x => x.Id == request.RoleGroupId);
response.RoleGroupName = roleGroup.Name;
response.RoleGroupId = roleGroup.Id;
response.IsSuccess = true;
}
switch (periodeType)
{
case PeriodeType.Yearly:
var kpiTargetsYearly = DataContext.KpiTargets
.Include(x => x.Kpi)
.Where(x => x.PeriodeType == periodeType).ToList();
foreach (var kpi in kpis)
{
var kpiDto = kpi.MapTo<GetKpiTargetsConfigurationResponse.Kpi>();
foreach (var number in YearlyNumbers)
{
var achievement = kpiTargetsYearly.SingleOrDefault(x => x.Kpi.Id == kpi.Id && x.Periode.Year == number);
if (achievement != null)
{
var targetDto =
achievement.MapTo<GetKpiTargetsConfigurationResponse.KpiTarget>();
kpiDto.KpiTargets.Add(targetDto);
}
else
{
var targetDto = new GetKpiTargetsConfigurationResponse.KpiTarget();
targetDto.Periode = new DateTime(number, 1, 1);
kpiDto.KpiTargets.Add(targetDto);
}
}
response.Kpis.Add(kpiDto);
}
break;
case PeriodeType.Monthly:
var kpiTargetsMonthly = DataContext.KpiTargets
.Include(x => x.Kpi)
.Where(x => x.PeriodeType == periodeType && x.Periode.Year == request.Year).ToList();
foreach (var kpi in kpis)
{
var kpiDto = kpi.MapTo<GetKpiTargetsConfigurationResponse.Kpi>();
var targets = kpiTargetsMonthly.Where(x => x.Kpi.Id == kpi.Id).ToList();
for (int i = 1; i <= 12; i++)
{
var target = targets.FirstOrDefault(x => x.Periode.Month == i);
if (target != null)
{
var achievementDto =
target.MapTo<GetKpiTargetsConfigurationResponse.KpiTarget>();
kpiDto.KpiTargets.Add(achievementDto);
}
else
{
var achievementDto = new GetKpiTargetsConfigurationResponse.KpiTarget();
achievementDto.Periode = new DateTime(request.Year, i, 1);
kpiDto.KpiTargets.Add(achievementDto);
}
}
response.Kpis.Add(kpiDto);
}
break;
case PeriodeType.Daily:
var kpiTargetsDaily = DataContext.KpiTargets
.Include(x => x.Kpi)
.Where(x => x.PeriodeType == periodeType && x.Periode.Year == request.Year
&& x.Periode.Month == request.Month).ToList();
foreach (var kpi in kpis)
{
var kpiDto = kpi.MapTo<GetKpiTargetsConfigurationResponse.Kpi>();
var targets = kpiTargetsDaily.Where(x => x.Kpi.Id == kpi.Id).ToList();
for (int i = 1; i <= DateTime.DaysInMonth(request.Year, request.Month); i++)
{
var target = targets.FirstOrDefault(x => x.Periode.Day == i);
if (target != null)
{
var targetDto =
target.MapTo<GetKpiTargetsConfigurationResponse.KpiTarget>();
kpiDto.KpiTargets.Add(targetDto);
}
else
{
var targetDto = new GetKpiTargetsConfigurationResponse.KpiTarget();
targetDto.Periode = new DateTime(request.Year, request.Month, i);
kpiDto.KpiTargets.Add(targetDto);
}
}
response.Kpis.Add(kpiDto);
}
break;
}
}
catch (InvalidOperationException invalidOperationException)
{
response.Message = invalidOperationException.Message;
}
catch (ArgumentNullException argumentNullException)
{
response.Message = argumentNullException.Message;
}
return response;
}
public FileResult DownloadTemplate(DownloadTemplateViewModel vModel)
{
ConfigType config = string.IsNullOrEmpty(vModel.ConfigType) ? ConfigType.KpiTarget
: (ConfigType)Enum.Parse(typeof(ConfigType), vModel.ConfigType);
#region Get Data
PeriodeType pType = string.IsNullOrEmpty(vModel.PeriodeType) ? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), vModel.PeriodeType);
var viewModel = new ConfigurationViewModel();
switch (config)
{
case ConfigType.KpiTarget:
{
var request = new GetKpiTargetsConfigurationRequest()
{
PeriodeType = vModel.PeriodeType,
Year = vModel.Year,
Month = vModel.Month,
RoleGroupId = vModel.RoleGroupId
};
var target = _kpiTargetService.GetKpiTargetsConfiguration(request);
viewModel = target.MapTo<ConfigurationViewModel>();
break;
}
case ConfigType.KpiAchievement:
{
var request = new GetKpiAchievementsConfigurationRequest()
{
PeriodeType = vModel.PeriodeType,
Year = vModel.Year,
Month = vModel.Month,
RoleGroupId = vModel.RoleGroupId
};
var achievement = _kpiAchievementService.GetKpiAchievementsConfiguration(request);
viewModel = achievement.MapTo<ConfigurationViewModel>();
break;
}
case ConfigType.OperationData:
{
var request = vModel.MapTo<GetOperationDataConfigurationRequest>();
request.PeriodeType = pType;
request.IsPartial = false;
var operationData = _operationDataService.GetOperationDataConfiguration(request);
viewModel = operationData.MapTo<ConfigurationViewModel>();
//return new FileContentResult(null, "application/octet-stream") { FileDownloadName = "as" };
break;
}
default:
break;
}
#endregion
/*
* Find and Create Directory
*/
var resultPath = Server.MapPath(string.Format("{0}{1}/", TemplateDirectory, vModel.ConfigType));
if (!Directory.Exists(resultPath))
{
Directory.CreateDirectory(resultPath);
}
#region parsing data to excel
string dateFormat = string.Empty;
string workSheetName = new StringBuilder(vModel.PeriodeType).ToString();
switch (vModel.PeriodeType)
{
case "Yearly":
dateFormat = "yyyy";
break;
case "Monthly":
dateFormat = "mmm-yy";
workSheetName = string.Format("{0}_{1}", workSheetName, vModel.Year);
break;
default:
dateFormat = "dd-mmm-yy";
workSheetName = string.Format("{0}_{1}-{2}", workSheetName, vModel.Year, vModel.Month.ToString().PadLeft(2, '0'));
break;
}
string fileName = string.Format(@"{0}_{1}_{2}.xlsx", vModel.ConfigType, vModel.PeriodeType, DateTime.Now.ToString("yyyymmddMMss"));
IWorkbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = workSheetName;
workbook.Worksheets.ActiveWorksheet = worksheet;
RowCollection rows = workbook.Worksheets[0].Rows;
ColumnCollection columns = workbook.Worksheets[0].Columns;
Row headerRow = rows[0];
headerRow.FillColor = Color.DarkGray;
headerRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
headerRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Column kpiIdColumn = columns[0];
Column kpiNameColumn = columns[1];
kpiIdColumn.Visible = false;
headerRow.Worksheet.Cells[headerRow.Index, kpiIdColumn.Index].Value = "KPI ID";
headerRow.Worksheet.Cells[headerRow.Index, kpiNameColumn.Index].Value = "KPI Name";
int i = 1; //i for row
#region inserting from models
foreach (var kpi in viewModel.Kpis)
{
worksheet.Cells[i, kpiIdColumn.Index].Value = kpi.Id;
worksheet.Cells[i, kpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
int j = 2; // for column
var items = new List<ConfigurationViewModel.Item>();
switch (vModel.ConfigType)
{
case "KpiTarget":
{
foreach (var target in kpi.KpiTargets)
{
var item = new ConfigurationViewModel.Item
{
Id = target.Id,
KpiId = kpi.Id,
Periode = target.Periode,
Remark = target.Remark,
Value = target.Value.HasValue ? target.Value.ToString() : string.Empty,
PeriodeType = pType
};
items.Add(item);
}
break;
}
case "KpiAchievement":
{
foreach (var achievement in kpi.KpiAchievements)
{
var item = new ConfigurationViewModel.Item()
{
Id = achievement.Id,
KpiId = kpi.Id,
Periode = achievement.Periode,
Remark = achievement.Remark,
Value = achievement.Value.HasValue ? achievement.Value.ToString() : string.Empty,
PeriodeType = pType
};
items.Add(item);
}
break;
}
case "OperationData":
{
//items = kpi.OperationData.MapTo<ConfigurationViewModel.Item>();
foreach (var operationData in kpi.OperationData)
{
var item = new ConfigurationViewModel.Item()
{
Id = operationData.Id,
KpiId = kpi.Id,
Periode = operationData.Periode,
Remark = operationData.Remark,
Value = operationData.Value.HasValue ? operationData.Value.ToString() : string.Empty,
PeriodeType = pType
};
items.Add(item);
}
break;
}
}
foreach (var item in items)
{
worksheet.Cells[headerRow.Index, j].Value = item.Periode;
worksheet.Cells[headerRow.Index, j].NumberFormat = dateFormat;
worksheet.Cells[headerRow.Index, j].AutoFitColumns();
worksheet.Cells[i, j].Value = item.RealValue;
worksheet.Cells[i, j].NumberFormat = "#,0.#0";
worksheet.Columns[j].AutoFitColumns();
j++;
}
Column totalValueColumn = worksheet.Columns[j];
if (i == headerRow.Index + 1)
{
worksheet.Cells[headerRow.Index, totalValueColumn.Index].Value = "Average";
worksheet.Cells[headerRow.Index, totalValueColumn.Index + 1].Value = "SUM";
Range r1 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
}
else
{
// add formula
Range r2 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
}
i++;
}
#endregion
kpiNameColumn.AutoFitColumns();
worksheet.FreezePanes(headerRow.Index, kpiNameColumn.Index);
string resultFilePath = string.Format("{0},{1}", resultPath, fileName);
//System.Web.HttpContext.Current.Request.MapPath(resultPath + fileName);
//System.Web.HttpContext.Current.Response.Clear();
//System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//System.Web.HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}", fileName));
using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
stream.Close();
}
//System.Web.HttpContext.Current.Response.End();
//workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
//workbook.Dispose();
#endregion
string namafile = Path.GetFileName(resultFilePath);
byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
var response = new FileContentResult(fileBytes, "application/octet-stream") { FileDownloadName = fileName };
return response;
}
public AllKpiTargetsResponse GetAllKpiTargetByRole(GetKpiTargetsConfigurationRequest request)
{
var response = new AllKpiTargetsResponse();
try
{
var kpiTargets = DataContext.Kpis
.Include(x => x.Measurement)
.Include(x => x.Type)
.Include(x => x.RoleGroup)
.AsEnumerable()
.OrderBy(x => x.Order)
.Where(x=>x.RoleGroup.Id == request.RoleGroupId)
.GroupBy(x => x.RoleGroup).ToDictionary(x => x.Key);
foreach (var item in kpiTargets)
{
var kpis = new List<AllKpiTargetsResponse.Kpi>();
foreach (var val in item.Value)
{
kpis.Add(val.MapTo<AllKpiTargetsResponse.Kpi>());
}
response.RoleGroups.Add(new AllKpiTargetsResponse.RoleGroup
{
Id = item.Key.Id,
Name = item.Key.Name,
Kpis = kpis
});
}
response.IsSuccess = true;
}
catch (ArgumentNullException argumentNullException)
{
response.Message = argumentNullException.Message;
}
catch (InvalidOperationException invalidOperationException)
{
response.Message = invalidOperationException.Message;
}
return response;
}
public FileResult DownloadTemplate(string configType, string periodeType, int year, int month)
{
ConfigType config = string.IsNullOrEmpty(configType)
? ConfigType.KpiTarget
: (ConfigType)Enum.Parse(typeof(ConfigType), configType);
#region Get Data
PeriodeType pType = string.IsNullOrEmpty(periodeType)
? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), periodeType);
var viewModel = new ConfigurationViewModel();
switch (config)
{
case ConfigType.KpiTarget:
//todo get KpiTarget Data
var targetRequest = new GetKpiTargetsConfigurationRequest() { PeriodeType = periodeType, Year = year, Month = month };
var target = _kpiTargetService.GetKpiTargetsConfiguration(targetRequest);
viewModel = target.MapTo<ConfigurationViewModel>();
break;
case ConfigType.KpiAchievement:
//todo get KpiAchievement Data
var request = new GetKpiAchievementsConfigurationRequest() { PeriodeType = periodeType, Year = year, Month = month };
var achievement = _kpiAchievementService.GetKpiAchievementsConfiguration(request);
viewModel = achievement.MapTo<ConfigurationViewModel>();
break;
case ConfigType.Economic:
break;
default:
break;
}
#endregion
/*
* Find and Create Directory
*/
var resultPath = Server.MapPath(string.Format("{0}{1}/", TemplateDirectory, configType));
if (!System.IO.Directory.Exists(resultPath))
{
System.IO.Directory.CreateDirectory(resultPath);
}
#region parsing data to excel
string dateFormat = string.Empty;
string workSheetName = new StringBuilder(periodeType).ToString();
switch (periodeType)
{
case "Yearly":
dateFormat = "yyyy";
break;
case "Monthly":
dateFormat = "mmm-yy";
workSheetName = string.Format("{0}_{1}", workSheetName, year);
break;
default:
dateFormat = "dd-mmm-yy";
workSheetName = string.Format("{0}_{1}-{2}", workSheetName, year, month.ToString().PadLeft(2, '0'));
break;
}
string guid = Guid.NewGuid().ToString();
string fileName = new StringBuilder(guid).Append(".xlsx").ToString();
//using (FileStream stream = new FileStream(fileName,FileMode.Create,FileAccess.ReadWrite)
//{
//}
IWorkbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = workSheetName;
workbook.Worksheets.ActiveWorksheet = worksheet;
RowCollection rows = workbook.Worksheets[0].Rows;
ColumnCollection columns = workbook.Worksheets[0].Columns;
Row HeaderRow = rows[0];
HeaderRow.FillColor = Color.DarkGray;
HeaderRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
HeaderRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Column KpiIdColumn = columns[0];
Column KpiNameColumn = columns[1];
KpiIdColumn.Visible = false;
HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiIdColumn.Index].Value = "KPI ID";
HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiNameColumn.Index].Value = "KPI Name";
int i = 1; //i for row
foreach (var kpi in viewModel.Kpis)
{
worksheet.Cells[i, KpiIdColumn.Index].Value = kpi.Id;
worksheet.Cells[i, KpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
int j = 2; // for column
var items = new List<ConfigurationViewModel.Item>();
switch (configType)
{
case "KpiTarget":
foreach (var target in kpi.KpiTargets)
{
var item = new ConfigurationViewModel.Item();
item.Id = target.Id;
item.KpiId = kpi.Id;
item.Periode = target.Periode;
item.Remark = target.Remark;
item.Value = target.Value;
item.PeriodeType = pType;
items.Add(item);
}
break;
case"KpiAchievement":
foreach (var achieve in kpi.KpiAchievements)
{
var item = new ConfigurationViewModel.Item() { Id = achieve.Id, KpiId = achieve.Id, Periode = achieve.Periode, Remark = achieve.Remark, Value = achieve.Value, PeriodeType = pType };
items.Add(item);
}
break;
case "Economic":
items = kpi.Economics.MapTo<ConfigurationViewModel.Item>();
break;
default:
break;
}
foreach (var achievement in items)
{
worksheet.Cells[HeaderRow.Index, j].Value = achievement.Periode;
worksheet.Cells[HeaderRow.Index, j].NumberFormat = dateFormat;
worksheet.Cells[HeaderRow.Index, j].AutoFitColumns();
worksheet.Cells[i, j].Value = achievement.Value;
worksheet.Cells[i, j].NumberFormat = "#,0.#0";
worksheet.Columns[j].AutoFitColumns();
j++;
}
//Column TotalValueColumn = worksheet.Columns[j];
//if (i == HeaderRow.Index + 1)
//{
// worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index].Value = "Average";
// worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index + 1].Value = "SUM";
// Range r1 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
// worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
// worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
//}
//else
//{
// // add formula
// Range r2 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
// worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
// worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
//}
i++;
}
KpiNameColumn.AutoFitColumns();
worksheet.FreezePanes(HeaderRow.Index, KpiNameColumn.Index);
string resultFilePath = string.Format("{0},{1}",resultPath,fileName);// System.Web.HttpContext.Current.Request.MapPath(resultPath + fileName);
//System.Web.HttpContext.Current.Response.Clear();
//System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//System.Web.HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}", fileName));
using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
{
workbook.SaveDocument(stream, DocumentFormat.Xlsx);
stream.Close();
}
//System.Web.HttpContext.Current.Response.End();
//workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
//workbook.Dispose();
#endregion
string namafile = Path.GetFileName(resultFilePath);
byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
var response = new FileContentResult(fileBytes, "application/octet-stream") { FileDownloadName = namafile };
return response;
}