public static bool cancelEvent(int eventID, int userID)
{
var db = new SqlConnectionDataContext();
var result =
(from uevent in db.Users_Events
where uevent.EventID == eventID && uevent.UserID == userID
select uevent).FirstOrDefault();
if (result == null)
{
return(false);
}
db.Users_Events.DeleteOnSubmit(result);
try
{
db.SubmitChanges();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
// Provide for exceptions.
}
}
public static bool deleteEvent(int eventID)
{
var db = new SqlConnectionDataContext();
var result =
(from esport in db.SportEvents
where esport.EventID == eventID
select esport).FirstOrDefault();
if (result == null)
{
return(false);
}
db.SportEvents.DeleteOnSubmit(result);
foreach (Users_Event uevent in db.Users_Events.Where(p => p.EventID == eventID))
{
db.Users_Events.DeleteOnSubmit(uevent);
}
try
{
db.SubmitChanges();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
// Provide for exceptions.
}
}
public static string Join(int eventid)
{
var dataContext = new SqlConnectionDataContext();
UserAccount loggedAccount = ManageCookie.user;
if (loggedAccount == null)
{
return("User is not logged in!");
}
int userID = loggedAccount.UserID;
//check if owner
int ownerID = getOwnerID(dataContext, eventid);
if (ownerID == userID)
{
//user is the owner of event
return("User is the owner");
}
else
{
//check if user is among the attendies of the event
int user_event_id = checkUserFromUsers_Event(dataContext, eventid, userID);
if (user_event_id == 0)
{
//user is not among the ettendies, he can join
addUserToEvent(dataContext, eventid, userID);
return("Success");
}
else
{
return("User is among the attendies");
}
}
}
public static List <UserAccount> filterUsers(FormCollection form)
{
//retrieving filter parameters
string name = form[0];
string address = form[1];
string email = form[2];
var db = new SqlConnectionDataContext();
List <UserAccount> usersList;
var query = (from users in db.UserAccounts
select users).ToList();
//Filter by display name name
if (!String.IsNullOrWhiteSpace(name))
{
query = query.Where(p => p.DisplayName.Contains(name)).ToList();
}
//Filter by address name
if (!String.IsNullOrWhiteSpace(address))
{
query = query.Where(p => p.Address.Contains(address)).ToList();
}
//Filter by email
if (!String.IsNullOrWhiteSpace(email))
{
query = query.Where(p => p.Email.Contains(email)).ToList();
}
usersList = query.ToList();
return(usersList);
}
public static List <Place> filterPlaces(FormCollection form)
{
//retrieving filter parameters
string name = form[0];
string address = form[1];
var db = new SqlConnectionDataContext();
List <Place> placesList;
var query = (from place in db.Places
select place).ToList();
//Filter by display name name
if (!String.IsNullOrWhiteSpace(name))
{
query = query.Where(p => p.Name.Contains(name)).ToList();
}
//Filter by address name
if (!String.IsNullOrWhiteSpace(address))
{
query = query.Where(p => p.Address.Contains(address)).ToList();
}
placesList = query.ToList();
return(placesList);
}
public static List <CategoryStatistics> getTopTenPlacesStatistics()
{
var dataContext = new SqlConnectionDataContext();
List <CategoryStatistics> seStatistics = getPlacesStatistics(dataContext);
return(seStatistics);
}
public static List <CategoryStatistics> getCategoriesStatistics()
{
var dataContext = new SqlConnectionDataContext();
List <CategoryStatistics> seStatistics = getSportEventStatistics(dataContext);
return(seStatistics);
}
public static List <String> getUsersByEvent(SqlConnectionDataContext db, int eventID)
{
return((from ue in db.Users_Events
join ua in db.UserAccounts on ue.UserID equals ua.UserID
where ue.EventID == eventID
select ua.DisplayName).ToList());
}
//Building dataTable contains details for the model foreach user
private static Hashtable getAllUsersEvents()
{
Dictionary <int, DataTable> usersData = new Dictionary <int, DataTable>();
var db = new SqlConnectionDataContext();
List <UserAccount> users = db.UserAccounts.ToList();
foreach (UserAccount user in users)
{
var query = (from sevents in db.SportEvents
join uevents in db.Users_Events on sevents.EventID equals uevents.EventID into ueventGroup //using 'into' to retrieve group unique eventID and removing duplicates events.
select new
{
category = sevents.CategoryID,
difftime = sevents.PlaceID,
classification = (ueventGroup.Any(x => x.UserID.Equals(user.UserID))) ? 1 : 0
});
DataTable userData = CustomLINQtoDataSetMethods.CopyToDataTable(query); //using external class to convert into datatable structure for Accord ML usage
usersData.Add(user.UserID, userData);
}
Hashtable usersDataHash = new Hashtable(usersData);
return(usersDataHash);
}
private static int getOwnerID(SqlConnectionDataContext db, int eventID)
{
int ownerID = (from se in db.SportEvents
where se.EventID == eventID
select se.OwnerID).FirstOrDefault();
return(ownerID);
}
private static int checkUserFromUsers_Event(SqlConnectionDataContext db, int eventID, int userID)
{
int Event_User_ID = (from ue in db.Users_Events
where ue.EventID == eventID && ue.UserID == userID
select ue.Event_User_ID).FirstOrDefault();
return(Event_User_ID);
}
public static string getAccessTokenByUserID(int userID)
{
var db = new SqlConnectionDataContext();
string accessToken = (from u in db.UserAccounts
where u.UserID == userID
select u.accessToken).FirstOrDefault().ToString();
return(accessToken);
}
public static List <UserAccount> getUsersList()
{
var db = new SqlConnectionDataContext();
List <UserAccount> usersList = (from myRow in db.UserAccounts
select myRow).ToList();
return(usersList);
}
//get user by his facebook id
private static UserAccount getUser(SqlConnectionDataContext db, string userfID)
{
UserAccount usera =
(from u in db.UserAccounts
where u.FacebookID == userfID
select u).FirstOrDefault();
return(usera);
}
public static List <Place> getPlacesList()
{
var db = new SqlConnectionDataContext();
List <Place> placesList = (from myRow in db.Places
select myRow).ToList();
return(placesList);
}
public static String convertUserIDtoName(int userID)
{
var dataContext = new SqlConnectionDataContext();
string userName = (from u in dataContext.UserAccounts
where u.UserID == userID
select u.DisplayName).FirstOrDefault();
return(userName);
}
public static List <SportEvent> getEventsList()
{
var db = new SqlConnectionDataContext();
List <SportEvent> eventsList = (from myRow in db.SportEvents
select myRow).ToList();
return(eventsList);
}
private static UserAccount getUserByHash(SqlConnectionDataContext db, string SHA256Hash)
{
UserAccount usera =
(from u in db.UserAccounts
where u.Hash == SHA256Hash
select u).FirstOrDefault();
return(usera);
}
//update all facebook details
private static void updateUser(SqlConnectionDataContext db, UserAccount u, string displayName, string Email, string photoURL, string accessToken)
{
u.DisplayName = displayName;
u.Email = Email;
u.PhotoURL = photoURL;
u.LastLogon = DateTime.Now;
u.accessToken = accessToken;
db.SubmitChanges();
}
public static string[] GetLocationCordinatesByName(String name)
{
var dataContext = new SqlConnectionDataContext();
Table <Place> table_Places = dataContext.Places;
List <Place> list_Places = table_Places.ToList();
Place p = list_Places.Find(location => location.Name == name);
string[] toString = { p.lat.ToString(), p.lng.ToString() };
return(toString);
}
public static void removeUserByID(int userID)
{
var db = new SqlConnectionDataContext();
var user = db.UserAccounts.Where(u => u.UserID == userID).SingleOrDefault();
if (user != null)
{
db.UserAccounts.DeleteOnSubmit(user);
db.SubmitChanges();
}
}
public static List <SportEvent_Parsed> GetEvents(FilterModel filter)
{
//customize filter model for quering
filter = filterPrep(filter);
List <SportEvent_Parsed> sportEvents;
var dataContext = new SqlConnectionDataContext();
sportEvents = getFilterEvents(dataContext, filter.category, filter.place, filter.maxAttendies);
return(sportEvents);
}
public static String getAllPlacesInfo()
{
var dataContext = new SqlConnectionDataContext();
Table <Place> table_Places = dataContext.Places;
List <Place> list_Places = table_Places.ToList();
String result = JsonConvert.SerializeObject(list_Places, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
return(result);
}
public static bool isPlaceExists(int placeID)
{
var db = new SqlConnectionDataContext();
var place = (from p in db.Places
where p.Id == placeID
select p.Id).FirstOrDefault();
if (place != 0)
{
return(true);
}
return(false);
}
public static bool isUserExists(int userID)
{
var db = new SqlConnectionDataContext();
var user = (from ua in db.UserAccounts
where ua.UserID == userID
select ua.UserID).FirstOrDefault();
if (user != 0)
{
return(true);
}
return(false);
}
public static bool isEventExists(int eventID)
{
var db = new SqlConnectionDataContext();
var sevent = (from se in db.SportEvents
where se.EventID == eventID
select se.EventID).FirstOrDefault();
if (sevent != 0)
{
return(true);
}
return(false);
}
public static string[] getPlacesName()
{
var dataContext = new SqlConnectionDataContext();
Table <Place> table_Places = dataContext.Places;
List <Place> list_Places = table_Places.ToList();
string[] toString = new string[list_Places.Count];
for (int i = 0; i < list_Places.Count; i++)
{
toString[i] = list_Places[i].Name;
}
return(toString);
}
public static string[] getCategoriesName()
{
var dataContext = new SqlConnectionDataContext();
Table <Category> table_Categories = dataContext.Categories;
//IEnumerator<SportEvent> enu_sportEvents = table_sportEvents.GetEnumerator();
List <Category> list_Categories = table_Categories.ToList();
string[] toString = new string[list_Categories.Count];
for (int i = 0; i < list_Categories.Count; i++)
{
toString[i] = list_Categories[i].Name;
}
return(toString);
}
private static List <CategoryStatistics> getPlacesStatistics(SqlConnectionDataContext db)
{
int count_all = db.SportEvents.Count();
List <CategoryStatistics> seStatistics = (from se in db.SportEvents
join places in db.Places on se.PlaceID equals places.Id
group places.Address by places.Address into g
select new CategoryStatistics
{
label = g.Key,
value = 100.0 * g.Count() / count_all
}).Take(10).ToList(); // Limit in SQL = Take in Linq to SQL.
return(seStatistics);
}
public static UserAccount CheckCookieExists()
{
HttpCookie cookie = HttpContext.Current.Request.Cookies["FacebookCookie"];
if (cookie == null)
{
return(null);
}
String SHA256Hash = cookie.Value.ToString();
var dataContext = new SqlConnectionDataContext();
user = getUserByHash(dataContext, SHA256Hash);
return(user);
}
