public ActionResult ExcelDseCompetence(int csmId, int dealerId) { var csmCompetenceModel = new CsmCompetenceModel(); var dlr = _userDealerMapServiceService.FindDealerByID(dealerId).FirstOrDefault(); var dealerManpowers = dlr.DealerManpowers.Where(x => x.CompetencyProfileMaps != null && x.DealerId == dlr.Id && x.UserId == csmId); var products = _masterService.GetAllProducts().ToList(); var cSmHeads = _masterService.GetAllCompetenceHeader().ToList(); var delaerCsmCompetenceModels = new List<DealerCsmcompetenceModel>(); decimal totalCompetence = 0, totalManpower = 0; foreach (var dealerManpower in dealerManpowers) { var delaerCsmCompetenceModel = new DealerCsmcompetenceModel(); delaerCsmCompetenceModel.ProductCompetency = new Dictionary<int, decimal>(); var competenceCountCsmModels = new List<CompetenceCountCsmModel>(); decimal totalProductCompetence = 0, totalProductManpower = 0; IDictionary<int, List<CompetencyProfileMap>> dealerHeadCompetency = new Dictionary<int, List<CompetencyProfileMap>>(); foreach (var cSmHead in cSmHeads) { dealerHeadCompetency.Add(cSmHead.Id, new List<CompetencyProfileMap>()); } foreach (var product in products) { List<CompetencyProfileMap> productCompetencyProfileMaps = new List<CompetencyProfileMap>(); foreach (var csmHead in cSmHeads) { var manpowerIds = dealerManpower.Id; if (dealerManpower.ProductId == product.Id) { var compmaps = _competencyProfileMapService.FindManpowerHeadComap(manpowerIds, csmHead.Id).ToList(); dealerHeadCompetency[csmHead.Id].AddRange(compmaps); productCompetencyProfileMaps.AddRange(compmaps); var groupedCompetency = compmaps.GroupBy(x => x.DealerManpowerId).ToList(); competenceCountCsmModels.Add(new CompetenceCountCsmModel { CompetenceId = csmHead.Id, ProductId = product.Id, NoOfCompetence = compmaps.Any() ? ((decimal)groupedCompetency.Sum(x => compmaps.Where(y => y.DealerManpowerId == x.Key).Average(y => y.Score)) / groupedCompetency.Count() * 20) : 0, }); } else { competenceCountCsmModels.Add(new CompetenceCountCsmModel { CompetenceId = csmHead.Id, ProductId = product.Id, NoOfCompetence = 0, }); } } var groupedProductCompetency = productCompetencyProfileMaps.GroupBy(x => x.DealerManpowerId).ToList(); delaerCsmCompetenceModel.ProductCompetency.Add(product.Id, groupedProductCompetency.Any() ? (decimal)(groupedProductCompetency.Sum(x => productCompetencyProfileMaps.Where(y => y.DealerManpowerId == x.Key).Average(y => y.Score)) / groupedProductCompetency.Count() * 20) : 0); totalProductManpower = totalProductManpower + groupedProductCompetency.Count(); totalProductCompetence = totalProductCompetence + (groupedProductCompetency.Any() ? (decimal)(groupedProductCompetency.Sum(x => productCompetencyProfileMaps.Where( y => y.DealerManpowerId == x.Key).Average(y => y.Score)) * 20) : 0); } delaerCsmCompetenceModel.HeadCompetency = new Dictionary<int, decimal>(); foreach (var temp in dealerHeadCompetency) { var groupedCompetency = temp.Value.GroupBy(x => x.DealerManpowerId).ToList(); var comp = temp.Value != null && temp.Value.Any() ? ((decimal)groupedCompetency.Sum(x => temp.Value.Where(y => y.DealerManpowerId == x.Key).Average(y => y.Score)) / groupedCompetency.Count() * 20) : 0; delaerCsmCompetenceModel.HeadCompetency.Add(temp.Key, comp); } totalCompetence = totalCompetence + totalProductCompetence; totalManpower = totalManpower + totalProductManpower; delaerCsmCompetenceModel.DealerId = dealerManpower.Id; delaerCsmCompetenceModel.DealerName = dealerManpower.Name; delaerCsmCompetenceModel.CompetenceCountCsmModels = competenceCountCsmModels; delaerCsmCompetenceModels.Add(delaerCsmCompetenceModel); } csmCompetenceModel.Id = csmId; csmCompetenceModel.DlId = dealerId; csmCompetenceModel.Name = dlr.Name; csmCompetenceModel.TotalCompetency = totalManpower != 0 ? totalCompetence / totalManpower : 0; csmCompetenceModel.DelaerCsmCompetenceModels = delaerCsmCompetenceModels.OrderBy(x => x.DealerName); csmCompetenceModel.competence = cSmHeads.ToDictionary(x => x.Id, x => x.Category); csmCompetenceModel.Products = products.ToDictionary(x => x.Id, x => x.Name); var tempPath = FileService.GetDownloadFilePath("xlsx"); var excelFilePath = FileService.ConvertToAbsolute(tempPath); var converter = new CsmCompetenceConverter(); converter.GenerateExcel(excelFilePath, csmCompetenceModel); var fileContent = FileService.ReadBytesFromAbsolutePath(excelFilePath); FileService.DeleteFile(excelFilePath); return File(fileContent, "application/octet-stream", "Dse_Competence-Analysis.xlsx"); }
public ActionResult ExcelCsmCompentencyAnalysis(int csmId) { var csmcompetenceModel = CsmCompetence(csmId); var tempPath = FileService.GetDownloadFilePath("xlsx"); var excelFilePath = FileService.ConvertToAbsolute(tempPath); var converter = new CsmCompetenceConverter(); converter.GenerateExcel(excelFilePath, csmcompetenceModel); var fileContent = FileService.ReadBytesFromAbsolutePath(excelFilePath); FileService.DeleteFile(excelFilePath); return File(fileContent, "application/octet-stream", "Competency-Analysis.xlsx"); }