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");
}
private ProductDashBoardMmodel StateWiseRm_Dashboard(int id)
{
var rm = _userService.GetUser(id);
var regionmaps = rm.UserRegionMaps.ToList();
var regions = regionmaps.Select(x => x.Region);
var states = regions.SelectMany(x => x.States).ToList();
var rsmsIds = _userService.FindChilds(new List<int> { id }).Select(x => x.Id).ToList();
var csmIds = _userService.FindChilds(rsmsIds).Select(x => x.Id).ToList();
var rmManPower = _dealerManpowerService.FindAllUserManpowers(csmIds, string.Empty).ToList();
var now = DateTime.Now;
var janMonth = _masterService.FindAndCreateMonth("January", now.Year);
var month = _masterService.FindAndCreateMonth(now.ToString("MMMM"), now.Year);
var products = _masterService.GetAllProducts();
var productDashBoardMmodel = new ProductDashBoardMmodel();
productDashBoardMmodel.ProductDashBoards = new Dictionary<string, IEnumerable<DseDashBoradModel>>();
productDashBoardMmodel.TotalProductivity = new Dictionary<string, double>();
productDashBoardMmodel.TotalCompetency = new Dictionary<string, double>();
var currentmonth = DateTime.Parse(string.Format("1 {0} {1}", janMonth.Name, month.Year));
var nextMonth = currentmonth.AddMonths(1);
foreach (var product in products)
{
var dashBoradModels = new List<DseDashBoradModel>();
IEnumerable<Target> targets;
int totalActual = 0, totalEmployee = 0;
List<CompetencyProfileMap> competencyProfileMaps = new List<CompetencyProfileMap>();
foreach (var state in states)
{
var totalmanpowers = rmManPower.Where(x => x.ProductId == product.Id && x.Dealer.StateId == state.Id).ToList();
var manpowerTargets =
_manpowerTargetService.FindUserManpowerMonthTarget(csmIds, new List<int> { month.Id }).ToList();
var dashBoradModel = new DseDashBoradModel();
if ((!totalmanpowers.Any()) && (!manpowerTargets.Any()))
continue;
dashBoradModel.ManPowerTarget = manpowerTargets.Sum(x => x.Planned);
var manpowers = totalmanpowers.Where(x => x.Type == "DSE");
var manPowerIds = manpowers.Select(x => x.Id).ToList().ToList();
targets = _targetService.FindProductTarget(manPowerIds).ToList();
IEnumerable<Target> janTargets = targets.Where(x => x.MonthId == janMonth.Id).ToList();
var productEmployee = totalmanpowers.Count(x => x.ObjectInfo.CreatedDate.Ticks < nextMonth.Ticks &&
(x.Profile.DateOfLeaving == null || x.Profile.DateOfLeaving.Value.Ticks >= currentmonth.Ticks));
//foreach (var manpower in totalmanpowers)
//{
// if (manpower.ObjectInfo.CreatedDate.Ticks < currentYear.Ticks && manpower.Profile.DateOfLeaving == null)
// {
// productEmployee = productEmployee + now.Month - 1;
// }
// else if (manpower.ObjectInfo.CreatedDate.Ticks < currentYear.Ticks && manpower.Profile.DateOfLeaving != null && manpower.Profile.DateOfLeaving.Value.Ticks > currentYear.Ticks)
// {
// if (manpower.Profile.DateOfLeaving.Value.Month == now.Month)
// {
// productEmployee = productEmployee + manpower.Profile.DateOfLeaving.Value.Month - 1;
// }
// else
// {
// productEmployee = productEmployee + manpower.Profile.DateOfLeaving.Value.Month;
// }
// }
// else if (manpower.ObjectInfo.CreatedDate.Ticks > currentYear.Ticks && manpower.Profile.DateOfLeaving == null)
// {
// productEmployee = productEmployee + now.Month - manpower.ObjectInfo.CreatedDate.Month;
// }
// else if (manpower.ObjectInfo.CreatedDate.Ticks > currentYear.Ticks && manpower.Profile.DateOfLeaving != null)
// {
// if (manpower.Profile.DateOfLeaving.Value.Month == now.Month)
// productEmployee = productEmployee + manpower.Profile.DateOfLeaving.Value.Month -
// manpower.ObjectInfo.CreatedDate.Month;
// else
// {
// productEmployee = productEmployee + manpower.Profile.DateOfLeaving.Value.Month -
// manpower.ObjectInfo.CreatedDate.Month + 1;
// }
// }
//}
totalEmployee = productEmployee + totalEmployee;
var janTargetTotal = janTargets.Sum(x => x.Actual);
dashBoradModel.Productivity = productEmployee != 0 ? Math.Round(janTargetTotal / (double)productEmployee, 2) : 0;
totalmanpowers.RemoveAll(
x => x.ObjectInfo.DeletedDate != null && x.ObjectInfo.DeletedDate.Value.Month != now.Month);
dashBoradModel.ActualManPower = totalmanpowers.Count();
dashBoradModel.Levels = new Dictionary<string, int>();
dashBoradModel.Name = state.Name;
var profiles = _profileService.FindProfiles(manPowerIds).ToList();
if (profiles.Any())
{
dashBoradModel.Exp = (int)profiles.Average(x => x.TotalWorkExperience != null ? x.TotalWorkExperience.Value : 0);
dashBoradModel.VeExp = (decimal)profiles.Average(x => x.ExperienceWithVE != null ? x.ExperienceWithVE.Value : 0);
dashBoradModel.Levels.Add("A", profiles.Count(x => x.TrainingLevel == "A"));
dashBoradModel.Levels.Add("B", profiles.Count(x => x.TrainingLevel == "B"));
dashBoradModel.Levels.Add("C", profiles.Count(x => x.TrainingLevel == "C"));
}
else
{
dashBoradModel.Exp = 0;
dashBoradModel.VeExp = 0;
dashBoradModel.Levels.Add("A", 0);
dashBoradModel.Levels.Add("B", 0);
dashBoradModel.Levels.Add("C", 0);
}
dashBoradModel.YTDAct = targets.Sum(x => x.Actual);
totalActual = totalActual + janTargetTotal;
dashBoradModel.YTDPlan1 = targets.Sum(x => x.Target1);
dashBoradModel.YTDPlan2 = targets.Sum(x => x.Target2);
var dsecompentenciesIds = _competencyProfileMapService.FindCompetencyProfileMaps(totalmanpowers.Where(x => x.ObjectInfo.DeletedDate == null).Select(x => x.Id)).ToList();
competencyProfileMaps.AddRange(dsecompentenciesIds);
if (dsecompentenciesIds.Any())
{
var groupedCompetency = dsecompentenciesIds.GroupBy(x => x.DealerManpowerId).ToList();
dashBoradModel.Competency = (decimal)groupedCompetency.Sum(x => dsecompentenciesIds.Where(y => y.DealerManpowerId == x.Key).Average(y => y.Score)) / groupedCompetency.Count() * 20;
}
else
{
dashBoradModel.Competency = 0;
}
dashBoradModels.Add(dashBoradModel);
}
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, 2));
}
productDashBoardMmodel.Name = rm.Name;
productDashBoardMmodel.Id = rm.Id;
productDashBoardMmodel.Role = "RM";
return productDashBoardMmodel;
}
public ActionResult DsmDashBoard(int csmId, int dealerId)
{
Session["BreadcrumbList"] = Utils.HtmlExtensions.SetBreadcrumbs((List<BreadcrumbModel>)Session["BreadcrumbList"], string.Format("/Report/DsmDashBoard?dealerId={0}&id={1}", dealerId, csmId), "DSM Dashboard");
ViewBag.List = Session["BreadcrumbList"];
var productDashBoardMmodel = new ProductDashBoardMmodel();
var now = DateTime.Now;
var year = now.Year.ToString();
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);
dashBoradModel.YTDAct = dseTargets.Sum(x => x.Actual);
var ytd =
dealerManpower.YearTargets.SingleOrDefault(
x => x.Year == year && x.ObjectInfo.DeletedDate == null);
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;
return View("DseDashBoard", productDashBoardMmodel);
}
public ActionResult DsePdfDashBoard(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 viewString = RenderViewToString("DsePdfDashBoard", productDashBoardMmodel);
var absolutePath = WkhtmlWrapper.CreatePdf(viewString, Server.MapPath("/tmp"),
FileService.CreateTempFilePath("pdf"), "Portrait", WkhtmlWrapper.PaperSize.A4);
var fileContents = FileService.ReadBytesFromAbsolutePath(absolutePath);
FileService.DeleteFile(absolutePath);
return File(fileContents, "application/pdf", "Dashboard.pdf");
}