public ActionResult ExportGroupWise()
{
System.Data.DataTable table = new System.Data.DataTable();
try
{
List <GroupWiseDetails> lstGroupWiseDetails = new List <GroupWiseDetails>();
lstGroupWiseDetails = SPR.GetGroupWiseData();
GroupWiseDetails objGrpWise = new GroupWiseDetails();
lstGroupWiseDetails = SPR.GetGroupWiseData();
foreach (var item in lstGroupWiseDetails)
{
objGrpWise.CustCount = objGrpWise.CustCount + item.CustCount;
objGrpWise.BulkUploadCount = objGrpWise.BulkUploadCount + item.BulkUploadCount;
objGrpWise.Total = objGrpWise.Total + item.Total;
}
objGrpWise.CustName = "Total";
lstGroupWiseDetails.Add(objGrpWise);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(GroupWiseDetails));
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (GroupWiseDetails item in lstGroupWiseDetails)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
var ReportName = "GroupWiseData";
string fileName = "BOTS_" + ReportName + ".xlsx";
using (XLWorkbook wb = new XLWorkbook())
{
table.TableName = ReportName;
wb.Worksheets.Add(table);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
}
}
catch (Exception ex)
{
return(null);
}
}
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 <GroupWiseDetails> GetGroupWiseData()
{
List <GroupWiseDetails> ObjData = new List <GroupWiseDetails>();
try
{
using (var context = new CommonDBContext())
{
var groups = context.tblGroupDetails.Where(x => x.IsActive == true && x.IsLive == true && x.GroupId != 1051).ToList();
foreach (var item in groups)
{
GroupWiseDetails objItem = new GroupWiseDetails();
var groupId = Convert.ToInt32(item.GroupId);
objItem.CustId = Convert.ToString(item.GroupId);
objItem.CustName = item.GroupName;
objItem.BusinessCategory = item.CustomerType;
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.CustCategory = 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.Location = city.CityName;
var CSName = (from c in context.tblGroupDetails
join ct in context.tblRMAssigneds on c.RMAssigned equals ct.RMAssignedId
where c.GroupId == groupId
select new
{
ct.RMAssignedName
}).FirstOrDefault();
objItem.CSName = CSName.RMAssignedName;
string connStr = CR.GetCustomerConnString(Convert.ToString(item.GroupId));
using (var contextdb = new BOTSDBContext(connStr))
{
objItem.CustCount = 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.BulkUploadCount = contextdb.BulkUploadCustLists.Count();
objItem.Total = objItem.CustCount + contextdb.BulkUploadCustLists.Count();
}
else
{
objItem.BulkUploadCount = 0;
objItem.Total = objItem.CustCount;
}
}
ObjData.Add(objItem);
}
}
}
catch (Exception ex)
{
newexception.AddException(ex, "GetGroupWiseData");
}
ObjData = ObjData.OrderByDescending(x => x.Total).ToList();
return(ObjData);
}
