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; }
public JsonResult ProcessFile(ProcessFileViewModel viewModel) { var file = string.Format("{0}{1}/{2}", UploadDirectory, viewModel.ConfigType, viewModel.Filename); viewModel.Filename = file; var response = ReadExcelFile(viewModel); return Json(new { isSuccess = response.IsSuccess, Message = response.Message }); }