public static List <string> GetChapterAddresses(Mile mile, WMMCRC db)
{
List <string> fromToAddressList = new List <string>();
var FromChapterAddress = (from a in db.ChapterAddresses
where a.ChapterId == mile.FromId
select string.Concat(a.StreetAddress1, " ", a.StreetAddress2, " ", a.City, " ",
(from b in db.States
where b.Id == a.StateId
select b.Name).FirstOrDefault(), " ", a.Zip
)).FirstOrDefault();
fromToAddressList.Add(FromChapterAddress);
var ToChapterAddress = (from c in db.ChapterAddresses
where c.ChapterId == mile.ToId
select string.Concat(c.StreetAddress1, " ", c.StreetAddress2, " ", c.City, " ",
(from d in db.States
where d.Id == c.StateId
select d.Name).FirstOrDefault(), " ", c.Zip
)).FirstOrDefault();
fromToAddressList.Add(ToChapterAddress);
return(fromToAddressList);
}
private static Dictionary <int, string> GetChaptersList(WMMCRC db)
{
Dictionary <int, string> chapterList = new Dictionary <int, string>();
chapterList = db.Chapters
.ToDictionary(a => a.ChapterId, a => a.ChapterName);
return(chapterList);
}
public static void GetChapterNames(Mile mile, WMMCRC database)
{
mile.ToName = (from s in database.Chapters
where s.ChapterId == mile.ToId
select s.ChapterName).FirstOrDefault();
mile.FromName = (from f in database.Chapters
where f.ChapterId == mile.FromId
select f.ChapterName).FirstOrDefault();
}
public static string GetRole(WMMCRC db, string Id)
{
string role = string.Empty;
role = (from a in db.AspNetRoles
where a.Id == Id
select a.Name).FirstOrDefault();
return(role);
}
public static string GetUserName(WMMCRC db, string Id)
{
string userName = string.Empty;
userName = (from a in db.AspNetUsers
where a.Id == Id
select a.UserName).FirstOrDefault();
return(userName);
}
public static string GetChapterName(WMMCRC db, int chapterId)
{
string chapterName = string.Empty;
chapterName = (from a in db.Chapters
where a.ChapterId == chapterId
select a.ChapterName).FirstOrDefault();
return(chapterName);
}
public static string GetMemberName(WMMCRC db, int memberId)
{
string memberName = string.Empty;
memberName = (from a in db.Members
where a.MemberId == memberId
select a.FullName).FirstOrDefault();
return(memberName);
}
private static Dictionary <int, string> GetMembersList(WMMCRC db)
{
Dictionary <int, string> memberList = new Dictionary <int, string>();
memberList = db.Members
.Where(a => a.Active == true)
.ToDictionary(a => a.MemberId,
a => a.FullName);
return(memberList);
}
private static void PopulateReportTable(WMMCRC db, Dictionary <int, string> memberList, Dictionary <int, string> chapterList, DataTable reportDt, string year)
{
int memberId = 0;
int chapterId = 0;
string memberName = string.Empty;
string chapterName = string.Empty;
int trips = 0;
foreach (DataRow row in reportDt.Rows)
{
chapterName = row[0].ToString();
chapterId = chapterList.FirstOrDefault(x => x.Value == chapterName).Key;
if (chapterId == 1)
{
continue;
}
foreach (DataColumn column in reportDt.Columns)
{
memberName = column.ColumnName;
memberId = memberList.FirstOrDefault(x => x.Value == memberName).Key;
//SAVE MAY BE ABLE TO USE FOR MILES TOTALS
//List<string> trips = (from a in db.Rides
// where a.MemberId == memberId && a.RideTo == chapterId
// select a.Miles).ToList();
DateTime fromDate;
DateTime toDate;
GetDateParameter(year, out fromDate, out toDate);
trips = (from a in db.Rides
where a.MemberId == memberId && a.RideTo == chapterId &&
(a.RideDate >= fromDate && a.RideDate <= toDate)
select a.Miles).Count();
//if(trips.Count>0)
//{
// //SAVE MAY BE ABLE TO USE FOR MILES TOTALS
// // List<int> tripsMilesNum = trips.Select(int.Parse).ToList();
// // int numOfVisits = tripsMilesNum.Sum();
//}
if (trips > 0)
{
row[column] = trips;
reportDt.AcceptChanges();
row.SetModified();
}
}
SetTotals(reportDt, row);
}
}
public static string GetChapterName(int?eventId, WMMCRC database)
{
int chapterId = (from b in database.Events
where b.EventId == eventId
select b.ChapterId).FirstOrDefault();
string chapterName = (from a in database.Chapters
where a.ChapterId == chapterId
select a.ChapterName).FirstOrDefault();
return(chapterName);
}
public static DataTable CreateReport(WMMCRC db, string year)
{
Dictionary <int, string> memberList = GetMembersList(db);
Dictionary <int, string> chapterList = GetChaptersList(db);
DataTable reportDt = CreateReportDt(memberList, chapterList);
PopulateReportTable(db, memberList, chapterList, reportDt, year);
return(reportDt);
}
public static IOrderedEnumerable <Event> LookupChapterNames(WMMCRC database, IOrderedEnumerable <Event> noChapterEvents)
{
Dictionary <int, string> dictChapterName = new Dictionary <int, string>();
dictChapterName = database.Chapters.ToDictionary(y => y.ChapterId, y => y.ChapterName);
foreach (Event item in noChapterEvents)
{
//item.ChapterName = (from a in database.Chapters
// where a.ChapterId == item.ChapterId
// select a.ChapterName).FirstOrDefault();
item.ChapterName = dictChapterName[item.ChapterId];
}
return(noChapterEvents);
}
public static IOrderedEnumerable <Event> GetSearchDateView(WMMCRC database, string year)
{
IOrderedEnumerable <Event> eventList;
IEnumerable <Event> eventListobj = new List <Event>();
int thisYear = DateTime.Now.Year;
List <Event> eventsList = database.Events.ToList();
eventListobj = eventsList.Where(s => s.EventDate.Value.Year == Convert.ToInt32(year));
eventList = eventListobj.OrderByDescending(s => s.EventDate);
LookupChapterNames(database, eventList);
return(eventList);
}
private static int GetClubhouseIdFromName(string searchString, WMMCRC database)
{
var chapterId = 0;
if (searchString.Length == 1)
{
chapterId = (from a in database.Chapters
where a.ChapterName.Substring(0, 1) == searchString
select a.ChapterId).FirstOrDefault();
}
else
{
chapterId = (from a in database.Chapters
where a.ChapterName.Contains(searchString)
select a.ChapterId).FirstOrDefault();
}
return(chapterId);
}
public static int GetMilesTotal(int MemberId, string year, WMMCRC db)
{
int MilesTotal = 0;
DateTime fromDate;
DateTime toDate;
GetDateParameter(year, out fromDate, out toDate);
var milesList = (from a in db.Rides
where a.MemberId == MemberId && a.Miles != null &&
(a.RideDate >= fromDate && a.RideDate <= toDate)
select a.Miles);
foreach (string mile in milesList)
{
MilesTotal = MilesTotal + Int32.Parse(mile);
}
return(MilesTotal);
}
public static IOrderedEnumerable <Event> LookupChapterNames(WMMCRC database)
{
// This Method returns only this years events
List <Event> eventsList = database.Events.ToList();
Dictionary <int, string> dictChapterName = new Dictionary <int, string>();
dictChapterName = database.Chapters.ToDictionary(y => y.ChapterId, y => y.ChapterName);
List <Event> thisYearEvent = GetThisYearEvents(eventsList);
foreach (Event item in thisYearEvent)
{
//item.ChapterName = (from a in database.Chapters
// where a.ChapterId == item.ChapterId
// select a.ChapterName).FirstOrDefault();
item.ChapterName = dictChapterName[item.ChapterId];
}
return(thisYearEvent.OrderByDescending(s => s.EventDate));
}
public static List <Mile> GetClubhouseName(string searchString, WMMCRC database)
{
// List<Mile> milesList = new List<Mile>();
int toId = 0;
int fromId = 0;
//Taking too long to load all so start with just checo when page loads
if (string.IsNullOrWhiteSpace(searchString))
{
//Checo Id
fromId = 1;
}
else
{
fromId = GetClubhouseIdFromName(searchString, database);
}
//milesList = database.Miles.ToList();
var milesListObj = (from a in database.Miles
where a.FromId == fromId
// select new Mile (a.MilesId,a.FromId,a.ToId,a.Miles,a.Active,a.DateModified,a.DateCreated)).ToList();
select new { a.MilesId, a.FromId, a.ToId, a.Miles, a.Active, a.DateModified, a.DateCreated }).ToList();
List <Mile> milesList = new List <Mile>();
foreach (var item in milesListObj)
{
milesList.Add(new Mile(item.MilesId, item.FromId, item.ToId, item.Miles, item.Active, item.DateModified, item.DateCreated));
}
foreach (var mile in milesList)
{
toId = mile.ToId;
fromId = mile.FromId;
GetChapterNames(mile, database);
}
return(milesList);
}
public static List <Ride> GetLisMemberNames(WMMCRC db, string yearSearchString)
{
List <Ride> allRidesList = new List <Ride>();
List <Ride> returnRideList = new List <Ride>();
allRidesList = db.Rides.ToList();
int searchYear = 0;
if (string.IsNullOrWhiteSpace(yearSearchString))
{
returnRideList = GetThisYearRides(allRidesList);
}
else
{
searchYear = Convert.ToInt32(yearSearchString);
returnRideList = GetThisYearRides(allRidesList, searchYear);
}
Dictionary <int, string> dictMemberName = new Dictionary <int, string>();
dictMemberName = db.Members.ToDictionary(x => x.MemberId, x => x.FullName);
Dictionary <int, string> dictChapterName = new Dictionary <int, string>();
dictChapterName = db.Chapters.ToDictionary(y => y.ChapterId, y => y.ChapterName);
foreach (Ride ride in returnRideList)
{
ride.MemberName = dictMemberName[ride.MemberId];
ride.RideFromName = dictChapterName[ride.RideFrom];
ride.RideToName = dictChapterName[ride.RideTo];
}
return(returnRideList);
}
