public ActionResult DsmExcelDashBoard(int csmId, int dealerId)
{
var now = DateTime.Now;
var year = now.Year.ToString();
var productDashBoardMmodel = new ProductDashBoardMmodel();
productDashBoardMmodel.Name = _masterService.FindDealers(x => x.Id == dealerId).First().Name;
var dseList =
_manpowerService.FindDealerManpowers(dealerId: dealerId, csmId: csmId).Where(x => x.Type.Equals("DSM"));
var productDseList = dseList.GroupBy(x => x.ProductId);
var products = _masterService.FindProducts(x => productDseList.Any(y => y.Key == x.Id));
var productvarients = _masterService.FindProductVarient(x => products.Any(y => y.Id == x.ProductId));
var profiles = _profileService.FindProfiles(x => dseList.Any(y => y.Profile.Id == x.Id));
var months = _masterService.FindMonths(year: now.Year);
var trainingProfiles =
_trainingProfileMapService.FindTrainingProfileMaps(x => dseList.Any(y => y.Id == x.DealerManpowerId));
var trainings = _masterService.FindTraining(x => trainingProfiles.Any(y => y.TrainingId == x.Id));
var compentencies =
_competencyProfileMapService.FindCompetencyProfileMaps(x => dseList.Any(y => y.Id == x.DealerManpowerId));
var targets =
_targetService.FindTargets(
x => months.Any(y => y.Id == x.MonthId) && dseList.Any(y => y.Id == x.DealerManpowerId));
productDashBoardMmodel.ProductDashBoards = new Dictionary<string, IEnumerable<DseDashBoradModel>>();
foreach (var productDse in productDseList)
{
var dseproductVaiantsIds = productvarients.Where(x => x.ProductId == productDse.Key).Select(x => x.Id);
var dashBoradModels = new List<DseDashBoradModel>();
foreach (var dealerManpower in productDse.OrderBy(x => x.Name))
{
var profile = profiles.SingleOrDefault(x => x.DealerManpower.Id == dealerManpower.Id);
var dashBoradModel = new DseDashBoradModel();
dashBoradModel.Name = dealerManpower.Name;
if (profile.TotalWorkExperience != null)
{
dashBoradModel.Exp = (int)profile.TotalWorkExperience.Value;
}
if (profile.ExperienceWithVE != null)
{
dashBoradModel.VeExp = (decimal)profile.ExperienceWithVE;
}
dashBoradModel.Level = dealerManpower.Profile.TrainingLevel;
var dseTargets = targets.Where(x => dseproductVaiantsIds.Contains(x.ProductVarientId) && x.DealerManpowerId == dealerManpower.Id);
var ytd =
dealerManpower.YearTargets.SingleOrDefault(
x => x.Year == year && x.ObjectInfo.DeletedDate == null);
dashBoradModel.YTDAct = dseTargets.Sum(x => x.Actual);
dashBoradModel.YTDPlan1 = ytd == null ? 0 : ytd.Target1;
dashBoradModel.YTDPlan2 = ytd == null ? 0 : ytd.Target2;
dashBoradModel.TivRepresenting = profile.TIVRepresenting;
var dsecompentencies = compentencies.Where(x => x.DealerManpowerId == dealerManpower.Id);
if (dsecompentencies.Any())
dashBoradModel.Competency =
(decimal)(compentencies.Where(x => x.DealerManpowerId == dealerManpower.Id).Average(x => x.Score) * 20);
else
{
dashBoradModel.Competency = 0;
}
var map = trainingProfiles.Where(x => x.DealerManpowerId == dealerManpower.Id).LastOrDefault();
if (map != null)
{
dashBoradModel.Type = trainings.SingleOrDefault(x => x.Id == map.TrainingId).Name;
if (map.LastTrainingDate != null)
{
dashBoradModel.Date = map.LastTrainingDate;
}
}
dashBoradModels.Add(dashBoradModel);
}
var product = products.SingleOrDefault(x => x.Id == productDse.Key);
productDashBoardMmodel.ProductDashBoards.Add(product.Name, dashBoradModels);
}
productDashBoardMmodel.CsmId = csmId;
productDashBoardMmodel.Id = dealerId;
var tempPath = FileService.GetDownloadFilePath("xlsx");
var excelFilePath = FileService.ConvertToAbsolute(tempPath);
var converter = new DashBoardConverter();
converter.GenerateExcel(excelFilePath, productDashBoardMmodel);
var fileContent = FileService.ReadBytesFromAbsolutePath(excelFilePath);
FileService.DeleteFile(excelFilePath);
return File(fileContent, "application/octet-stream", "Dashboard.xlsx");
}
public ActionResult DseExcelDashBoard(int csmId, int dealerId)
{
var productDashBoardMmodel = new ProductDashBoardMmodel();
var now = DateTime.Now;
var currentYear = new DateTime(now.Year, 1, 1);
productDashBoardMmodel.Name = _masterService.FindDealers(x => x.Id == dealerId).First().Name;
var dseList =
_manpowerService.FindAllDealerManpowers(new List<int> { dealerId }, new List<int> { csmId }).ToList();
var productDseList = dseList.GroupBy(x => x.ProductId);
var products = _masterService.FindProducts(x => productDseList.Any(y => y.Key == x.Id)).ToList();
var productvarients = _masterService.FindProductVarient(x => products.Any(y => y.Id == x.ProductId)).ToList();
var profiles = _profileService.FindProfiles(x => dseList.Any(y => y.Profile.Id == x.Id)).ToList();
var year = now.Year.ToString();
var trainingProfiles =
_trainingProfileMapService.FindTrainingProfileMaps(x => dseList.Any(y => y.Id == x.DealerManpowerId));
var trainings = _masterService.FindTraining(x => trainingProfiles.Any(y => y.TrainingId == x.Id)).ToList();
var compentencies = _competencyProfileMapService.FindCompetencyProfileMaps(x => dseList.Any(y => y.Id == x.DealerManpowerId)).ToList();
var targets = _targetService.FindTargets(x => dseList.Any(y => y.Id == x.DealerManpowerId)).ToList();
productDashBoardMmodel.TotalProductivity = new Dictionary<string, double>();
productDashBoardMmodel.ProductDashBoards = new Dictionary<string, IEnumerable<DseDashBoradModel>>();
productDashBoardMmodel.TotalCompetency = new Dictionary<string, double>();
foreach (var productDse in productDseList)
{
var dseproductVaiantsIds = productvarients.Where(x => x.ProductId == productDse.Key).Select(x => x.Id);
int totalActual = 0, totalEmployee = 0;
var dashBoradModels = new List<DseDashBoradModel>();
List<CompetencyProfileMap> competencyProfileMaps = new List<CompetencyProfileMap>();
foreach (var dealerManpower in productDse.OrderBy(x => x.Name))
{
var profile = profiles.SingleOrDefault(x => x.DealerManpower.Id == dealerManpower.Id);
var dashBoradModel = new DseDashBoradModel();
dashBoradModel.Name = dealerManpower.Name;
if (profile != null && profile.TotalWorkExperience != null)
{
dashBoradModel.Exp = (int)profile.TotalWorkExperience.Value;
}
if (profile != null && profile.ExperienceWithVE != null)
{
dashBoradModel.VeExp = (decimal)profile.ExperienceWithVE;
}
dashBoradModel.Level = dealerManpower.Profile.TrainingLevel;
var dseTargets = targets.Where(x => dseproductVaiantsIds.Contains(x.ProductVarientId) && x.DealerManpowerId == dealerManpower.Id).ToList();
dashBoradModel.YTDAct = dseTargets.Sum(x => x.Actual);
totalActual = totalActual + dashBoradModel.YTDAct;
if (dealerManpower.ObjectInfo.CreatedDate.Ticks < currentYear.Ticks && dealerManpower.Profile.DateOfLeaving == null)
{
totalEmployee = totalEmployee + now.Month - 1;
}
else if (dealerManpower.ObjectInfo.CreatedDate.Ticks < currentYear.Ticks && dealerManpower.Profile.DateOfLeaving != null && dealerManpower.Profile.DateOfLeaving.Value.Ticks > currentYear.Ticks)
{
if (dealerManpower.Profile.DateOfLeaving.Value.Month == now.Month)
{
totalEmployee = totalEmployee + dealerManpower.Profile.DateOfLeaving.Value.Month - 1;
}
else
{
totalEmployee = totalEmployee + dealerManpower.Profile.DateOfLeaving.Value.Month;
}
}
else if (dealerManpower.ObjectInfo.CreatedDate.Ticks > currentYear.Ticks && dealerManpower.Profile.DateOfLeaving == null)
{
totalEmployee = totalEmployee + now.Month - dealerManpower.ObjectInfo.CreatedDate.Month;
}
else if (dealerManpower.ObjectInfo.CreatedDate.Ticks > currentYear.Ticks && dealerManpower.Profile.DateOfLeaving != null)
{
if (dealerManpower.Profile.DateOfLeaving.Value.Month == now.Month)
totalEmployee = totalEmployee + dealerManpower.Profile.DateOfLeaving.Value.Month -
dealerManpower.ObjectInfo.CreatedDate.Month;
else
{
totalEmployee = totalEmployee + dealerManpower.Profile.DateOfLeaving.Value.Month -
dealerManpower.ObjectInfo.CreatedDate.Month + 1;
}
}
dashBoradModel.TotlalTarget1 = dseTargets.Sum(x => x.Target1);
dashBoradModel.TotalTarget2 = dseTargets.Sum(x => x.Target2);
var yearTarget = dealerManpower.YearTargets.SingleOrDefault(x => x.Year == year && x.ObjectInfo.DeletedDate == null);
dashBoradModel.YTDPlan1 = yearTarget == null ? 0 : yearTarget.Target1;
dashBoradModel.YTDPlan2 = yearTarget == null ? 0 : yearTarget.Target2;
dashBoradModel.Productivity = Math.Round(dseTargets.Any() ? dseTargets.Average(x => x.Actual) : 0, 2);
if (profile != null) dashBoradModel.TivRepresenting = profile.TIVRepresenting;
var dsecompentencies = compentencies.Where(x => x.DealerManpowerId == dealerManpower.Id).ToList();
competencyProfileMaps.AddRange(dsecompentencies);
if (dsecompentencies.Any())
{
dashBoradModel.Competency =
(decimal)
(compentencies.Where(x => x.DealerManpowerId == dealerManpower.Id).Average(x => x.Score) * 20);
}
else
{
dashBoradModel.Competency = 0;
}
var map = trainingProfiles.Where(x => x.DealerManpowerId == dealerManpower.Id).LastOrDefault();
if (map != null)
{
dashBoradModel.Type = trainings.SingleOrDefault(x => x.Id == map.TrainingId).Name;
if (map.LastTrainingDate != null)
{
dashBoradModel.Date = map.LastTrainingDate;
}
}
dashBoradModels.Add(dashBoradModel);
}
ViewBag.List = Session["BreadcrumbList"];
var product = products.SingleOrDefault(x => x.Id == productDse.Key);
productDashBoardMmodel.ProductDashBoards.Add(product.Name, dashBoradModels);
productDashBoardMmodel.TotalProductivity.Add(product.Name, totalEmployee != 0 ? Math.Round(totalActual / (double)totalEmployee, 2) : 0);
double totalproductCompetency = 0;
if (competencyProfileMaps.Any())
{
var groupedCompetency = competencyProfileMaps.GroupBy(x => x.DealerManpowerId).ToList();
totalproductCompetency = groupedCompetency.Sum(x => competencyProfileMaps.Where(y => y.DealerManpowerId == x.Key).Average(y => y.Score)) / groupedCompetency.Count() * 20;
}
productDashBoardMmodel.TotalCompetency.Add(product.Name, Math.Round(totalproductCompetency, 1));
}
productDashBoardMmodel.CsmId = csmId;
productDashBoardMmodel.Id = dealerId;
var tempPath = FileService.GetDownloadFilePath("xlsx");
var excelFilePath = FileService.ConvertToAbsolute(tempPath);
var converter = new DashBoardConverter();
converter.GenerateExcel(excelFilePath, productDashBoardMmodel);
var fileContent = FileService.ReadBytesFromAbsolutePath(excelFilePath);
FileService.DeleteFile(excelFilePath);
return File(fileContent, "application/octet-stream", "Dashboard.xlsx");
}