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);
}
private BaseResponse ReadExcelFile(ProcessFileViewModel viewModel)
{
var response = new BaseResponse();
try
{
int year = DateTime.Now.Year;
int month = DateTime.Now.Month;
var listData = new List <ConfigurationViewModel.Item>();
if (viewModel.Filename != Path.GetFullPath(viewModel.Filename))
{
viewModel.Filename = Server.MapPath(viewModel.Filename);
}
if (!System.IO.File.Exists(viewModel.Filename))
{
response.IsSuccess = false;
response.Message = "File Not Found";
return(response);
}
Workbook workbook = new Workbook();
using (FileStream stream = new FileStream(viewModel.Filename, FileMode.Open))
{
workbook.LoadDocument(stream, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
#region foreach
foreach (var worksheet in workbook.Worksheets)
{
string[] name = worksheet.Name.Split('_');
PeriodeType pType;
if (name[0] == "Daily" || name[0] == "Monthly" || name[0] == "Yearly")
{
string periodeType = name[0];
pType = string.IsNullOrEmpty(periodeType) ? PeriodeType.Yearly : (PeriodeType)Enum.Parse(typeof(PeriodeType), periodeType);
string period = name[name.Count() - 1];
string[] periodes = null;
if (periodeType != period && !string.IsNullOrEmpty(period))
{
switch (periodeType)
{
case "Yearly":
break;
case "Monthly":
year = int.Parse(period);
break;
case "Daily":
periodes = period.Split('-');
year = int.Parse(periodes[0]);
month = int.Parse(periodes[periodes.Count() - 1]);
break;
}
}
workbook.Worksheets.ActiveWorksheet = worksheet;
Range range = worksheet.GetUsedRange();
int rows = range.RowCount;
int column = range.ColumnCount - 2;
int kpiId = 0;
var kpiListWithOperationConfigId = new List <int>();
var operationIds = new GetOperationsInResponse();
#region get kpi list with key operation config
if (viewModel.ConfigType.ToLowerInvariant().Equals("operationdata"))
{
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < column; j++)
{
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
kpiId = int.Parse(worksheet.Cells[i, j].Value.ToString());
kpiListWithOperationConfigId.Add(kpiId);
}
}
}
}
operationIds = _operationConfigService.GetOperationIn(new GetOperationsInRequest {
KpiIds = kpiListWithOperationConfigId
});
}
#endregion
for (int i = 1; i < rows; i++)
{
bool isAuthorizedKPI = false;
for (int j = 0; j < column; j++)
{
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
kpiId = int.Parse(worksheet.Cells[i, j].Value.ToString());
//this will validate authorized KPI based on Role
isAuthorizedKPI = ValidateAuthorizeKPI(kpiId);
if (!isAuthorizedKPI)
{
break;
}
}
}
else if (j > 1)
{
if (worksheet.Cells[0, j].Value.Type == CellValueType.DateTime)
{
DateTime periodData = DateTime.Parse(worksheet.Cells[0, j].Value.ToString());
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric ||
worksheet.Cells[i, j].Value.Type == CellValueType.Text)
{
string value = worksheet.Cells[i, j].Value.ToString();
int operationId = 0;
if (viewModel.ConfigType.ToLowerInvariant().Equals(ConfigType.OperationData.ToString().ToLowerInvariant()))
{
var operation = operationIds.KeyOperations.FirstOrDefault(x => x.KpiId == kpiId);
if (operation != null)
{
operationId = operation.Id;
}
}
if (!string.IsNullOrEmpty(value))
{
var data = new ConfigurationViewModel.Item
{
Value = value,
KpiId = kpiId,
Periode = periodData,
PeriodeType = pType,
OperationId = operationId,
ScenarioId = viewModel.ScenarioId
};
listData.Add(data);
}
}
}
}
}
if (!isAuthorizedKPI)
{
continue;
}
}
}
else
{
response.IsSuccess = false;
response.Message = "File Not Valid";
break;
}
switch (viewModel.ConfigType)
{
case "KpiTarget":
response = UpdateKpiTarget(listData);
break;
case "KpiAchievement":
response = UpdateKpiAchievement(listData);
break;
case "OperationData":
response = UpdateOperationData(listData);
break;
default:
response.IsSuccess = false;
response.Message = string.Format(@"config type for {0} is not existed",
viewModel.ConfigType);
break;
}
}
#endregion
}
}
catch (Exception exception)
{
response.IsSuccess = false;
response.Message = exception.Message;
}
return(response);
}
