public GetAchievementsResponse GetAchievements(GetKpiAchievementsConfigurationRequest request) { PeriodeType periodeType = (PeriodeType)Enum.Parse(typeof(PeriodeType), request.PeriodeType); var response = new GetAchievementsResponse(); try { var kpiAchievement = DataContext.KpiAchievements.Include(x => x.Kpi).Where(x => x.PeriodeType == periodeType && x.Periode.Year == request.Year && x.Periode.Month == request.Month).ToList(); if (kpiAchievement.Count > 0) { foreach (var item in kpiAchievement) { response.KpiAchievements.Add(item.MapTo <GetKpiAchievementResponse>()); } } response.IsSuccess = true; } catch (InvalidOperationException invalidOperationException) { response.IsSuccess = false; response.Message = invalidOperationException.Message; } catch (ArgumentNullException argumentNullException) { response.IsSuccess = false; response.Message = argumentNullException.Message; } return(response); }
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 GetKpiAchievementsConfigurationRequest(); request.PeriodeType = pType.ToString(); request.RoleGroupId = roleGroupId; request.Year = paramViewModel.Year; request.Month = paramViewModel.Month; var response = _kpiAchievementService.GetKpiAchievementsConfiguration(request); if (response.IsSuccess) { var viewModel = response.MapTo <ConfigurationKpiAchievementsViewModel>(); 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 AllKpiAchievementsResponse GetKpiAchievementsByRole(GetKpiAchievementsConfigurationRequest request) { var response = new AllKpiAchievementsResponse(); try { var kpiAchievements = DataContext.Kpis .Include(x => x.Measurement) .Include(x => x.Type) .Include(x => x.RoleGroup) .Where(x => x.RoleGroup.Id == request.RoleGroupId) .AsEnumerable() .OrderBy(x => x.Order) .GroupBy(x => x.RoleGroup).ToDictionary(x => x.Key); foreach (var item in kpiAchievements) { var kpis = new List <AllKpiAchievementsResponse.Kpi>(); foreach (var val in item.Value) { kpis.Add(val.MapTo <AllKpiAchievementsResponse.Kpi>()); } response.RoleGroups.Add(new AllKpiAchievementsResponse.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 GetKpiAchievementsConfigurationResponse GetKpiAchievementsConfiguration(GetKpiAchievementsConfigurationRequest request) { var response = new GetKpiAchievementsConfigurationResponse(); 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; } //var kpis = DataContext.Kpis // .Include(x => x.RoleGroup) // .Include(x => x.Measurement) // .Where(x => x.RoleGroup.Id == request.RoleGroupId).ToList(); switch (periodeType) { case PeriodeType.Yearly: var kpiAchievementsYearly = DataContext.KpiAchievements .Include(x => x.Kpi) .Where(x => x.PeriodeType == periodeType).ToList(); foreach (var kpi in kpis) { var kpiDto = kpi.MapTo <GetKpiAchievementsConfigurationResponse.Kpi>(); foreach (var number in YearlyNumbers) { var achievement = kpiAchievementsYearly.SingleOrDefault(x => x.Kpi.Id == kpi.Id && x.Periode.Year == number); if (achievement != null) { var achievementDto = achievement.MapTo <GetKpiAchievementsConfigurationResponse.KpiAchievement>(); kpiDto.KpiAchievements.Add(achievementDto); } else { var achievementDto = new GetKpiAchievementsConfigurationResponse.KpiAchievement(); achievementDto.Periode = new DateTime(number, 1, 1); kpiDto.KpiAchievements.Add(achievementDto); } } response.Kpis.Add(kpiDto); } break; case PeriodeType.Monthly: var kpiAchievementsMonthly = DataContext.KpiAchievements .Include(x => x.Kpi) .Where(x => x.PeriodeType == periodeType && x.Periode.Year == request.Year).ToList(); foreach (var kpi in kpis) { var kpiDto = kpi.MapTo <GetKpiAchievementsConfigurationResponse.Kpi>(); var achievements = kpiAchievementsMonthly.Where(x => x.Kpi.Id == kpi.Id).ToList(); for (int i = 1; i <= 12; i++) { var achievement = achievements.FirstOrDefault(x => x.Periode.Month == i); if (achievement != null) { var achievementDto = achievement.MapTo <GetKpiAchievementsConfigurationResponse.KpiAchievement>(); kpiDto.KpiAchievements.Add(achievementDto); } else { var achievementDto = new GetKpiAchievementsConfigurationResponse.KpiAchievement(); achievementDto.Periode = new DateTime(request.Year, i, 1); kpiDto.KpiAchievements.Add(achievementDto); } } response.Kpis.Add(kpiDto); } break; case PeriodeType.Daily: var kpiAchievementsDaily = DataContext.KpiAchievements .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 <GetKpiAchievementsConfigurationResponse.Kpi>(); var achievements = kpiAchievementsDaily.Where(x => x.Kpi.Id == kpi.Id).ToList(); for (int i = 1; i <= DateTime.DaysInMonth(request.Year, request.Month); i++) { var achievement = achievements.FirstOrDefault(x => x.Periode.Day == i); if (achievement != null) { var achievementDto = achievement.MapTo <GetKpiAchievementsConfigurationResponse.KpiAchievement>(); kpiDto.KpiAchievements.Add(achievementDto); } else { var achievementDto = new GetKpiAchievementsConfigurationResponse.KpiAchievement(); achievementDto.Periode = new DateTime(request.Year, request.Month, i); kpiDto.KpiAchievements.Add(achievementDto); } } 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); }