public ActionResult Index() { SinglePageViewModel singlevm = new SinglePageViewModel(); try { GroupWiseDetails objGrpWise = new GroupWiseDetails(); singlevm.lstGroupWiseDetails = SPR.GetGroupWiseData(); foreach (var item in singlevm.lstGroupWiseDetails) { objGrpWise.CustCount = objGrpWise.CustCount + item.CustCount; objGrpWise.BulkUploadCount = objGrpWise.BulkUploadCount + item.BulkUploadCount; objGrpWise.Total = objGrpWise.Total + item.Total; } objGrpWise.CustName = "Total"; singlevm.lstGroupWiseDetails.Add(objGrpWise); singlevm.lstCommunication = SPR.GetCommunicationWhatsAppExpiryData(); var userDetails = (CustomerLoginDetail)Session["UserSession"]; userDetails.CustomerName = CR.GetCustomerName(userDetails.GroupId); Session["UserSession"] = userDetails; singlevm.lstCSMembers = CR.GetRMAssigned(); singlevm.lstsummarytable = SPR.GetSinglePageSummaryTable(); singlevm.lstnontransactingGrp = SPR.GetSinglePageNonTransactingGroups(); singlevm.lstnontransactingOutlet = SPR.GetNonTransactingOutlet(""); singlevm.lstlowtransactingOutlet = SPR.GetLowTransactingOutlet(""); singlevm.lstCitywiseData = SPR.GetCityWiseData(); if (singlevm.lstCitywiseData != null) { var categories = singlevm.lstCitywiseData.GroupBy(x => x.CategoryName).Select(y => y.First()).ToList(); singlevm.lstCategories = categories; var cities = singlevm.lstCitywiseData.GroupBy(x => x.CityName).Select(y => y.First()).ToList(); List <CitywiseReport> objData = new List <CitywiseReport>(); foreach (var item in cities) { CitywiseReport objItem = new CitywiseReport(); long cityCount = singlevm.lstCitywiseData.Where(x => x.CityName == item.CityName).Sum(y => y.MemberBase); objItem.CityName = item.CityName; objItem.CategoryName = item.CategoryName; objItem.MemberBase = cityCount; objData.Add(objItem); } objData = objData.OrderByDescending(x => x.MemberBase).ToList(); cities = objData.GroupBy(x => x.CityName).Select(y => y.First()).ToList(); List <CitywiseReport> objCategoryData = new List <CitywiseReport>(); foreach (var category in categories) { CitywiseReport objItem = new CitywiseReport(); long categoryCount = singlevm.lstCitywiseData.Where(x => x.CategoryName == category.CategoryName).Sum(y => y.MemberBase); objItem.CategoryName = category.CategoryName; objItem.MemberBase = categoryCount; objCategoryData.Add(objItem); } singlevm.lstCategoriesTotal = objCategoryData; singlevm.GrandTotal = objCategoryData.Sum(x => x.MemberBase); singlevm.lstCities = cities; } } catch (Exception ex) { newexception.AddException(ex, "Single Page"); } return(View(singlevm)); }
public List <CitywiseReport> GetCityWiseData() { List <CityWiseData> objData = new List <CityWiseData>(); List <CitywiseReport> lstCitywiseReport = new List <CitywiseReport>(); List <CitywiseReport> lstCitywiseReportNew = new List <CitywiseReport>(); List <CitywiseReport> lstFinalData = new List <CitywiseReport>(); try { using (var context = new CommonDBContext()) { var groups = context.tblGroupDetails.Where(x => x.IsActive == true && x.IsLive == true).ToList(); foreach (var item in groups) { var groupId = Convert.ToInt32(item.GroupId); CityWiseData objItem = new CityWiseData(); objItem.GroupId = Convert.ToString(item.GroupId); objItem.GroupName = item.GroupName; var category = (from c in context.tblGroupDetails join ct in context.tblCategories on c.RetailCategory equals ct.CategoryId where c.GroupId == groupId select new { ct.CategoryName }).FirstOrDefault(); objItem.CategoryName = category.CategoryName; var city = (from c in context.tblGroupDetails join ct in context.tblCities on c.City equals ct.CityId where c.GroupId == groupId select new { ct.CityName }).FirstOrDefault(); objItem.CityName = city.CityName; string connStr = CR.GetCustomerConnString(Convert.ToString(item.GroupId)); using (var contextdb = new BOTSDBContext(connStr)) { //objItem.MemberBase = contextdb.CustomerDetails.Count(); objItem.MemberBase = contextdb.CustomerDetails.Count(); var sqlQ = $"SELECT COUNT(*) as Count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'BulkUploadCustList'"; var exist = contextdb.Database.SqlQuery <int>(sqlQ).FirstOrDefault(); if (exist > 0) { objItem.MemberBulkUpload = contextdb.BulkUploadCustLists.Count(); objItem.TotalMemberBase = objItem.MemberBase + contextdb.BulkUploadCustLists.Count(); } else { objItem.MemberBulkUpload = 0; objItem.TotalMemberBase = objItem.MemberBase; } } objData.Add(objItem); } var uniqueCities = objData.GroupBy(x => x.CityName).Select(y => y.First()).ToList(); var uniqueCategories = objData.GroupBy(x => x.CategoryName).Select(y => y.First()).ToList(); var Cities = context.tblCities.ToList(); var Categories = context.tblCategories.ToList(); foreach (var city in Cities) { foreach (var category in Categories) { CitywiseReport objReport = new CitywiseReport(); objReport.CityName = city.CityName; objReport.CategoryName = category.CategoryName; var lst = objData.Where(x => x.CityName == city.CityName && x.CategoryName == category.CategoryName).ToList(); var total = lst.Sum(x => x.TotalMemberBase); objReport.MemberBase = total; lstCitywiseReport.Add(objReport); } } foreach (var city in Cities) { var count = lstCitywiseReport.Where(x => x.CityName == city.CityName && x.MemberBase != 0).Count(); if (count > 0) { var filterList = lstCitywiseReport.Where(x => x.CityName == city.CityName).ToList(); lstCitywiseReportNew.AddRange(filterList); } } foreach (var category in Categories) { var count = lstCitywiseReportNew.Where(x => x.CategoryName == category.CategoryName && x.MemberBase != 0).Count(); if (count > 0) { var filterList = lstCitywiseReportNew.Where(x => x.CategoryName == category.CategoryName).ToList(); lstFinalData.AddRange(filterList); } } } } catch (Exception ex) { newexception.AddException(ex, "GetCityWiseData"); } return(lstFinalData); }