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