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");
}