/// <summary>
/// Method to get all Posts by title
/// </summary>
/// <param name="title"></param>
/// <returns></returns>
public List <Result> getFavourites(string email)
{
LD = LinkDatabase.getInstance();
string query = @"SELECT P.ID, P.TITLE FROM " + LD.databaseName + @".POST AS P, " + LD.databaseName +
@".FAVOURITES AS F WHERE F.EMAIL = '" + email + @"' AND F.ID = P.ID;";
//string query = @"SELECT ID, TITLE FROM " + LD.databaseName + @".POST AS P NATURAL JOIN " + LD.databaseName +
//@".FAVOURITES AS F WHERE F.EMAIL = '" + email + @"' AND F.ID = P.ID;";
List <Result> Favs = new List <Result>();
try
{
Result temp;
MySqlDataReader reader = LD.executeGenericSQL(query);
while (reader.Read())
{
temp = new Result();
temp.ID = reader.GetInt32("ID").ToString();
temp.title = reader.GetString("Title");
Favs.Add(temp);
}
} catch (Exception e)
{
sw.Write(e.Message);
} finally
{
LD.doClose();
}
return(Favs);
}
/// <summary>
/// This retrieves an admin by its email address
/// </summary>
/// <param name="Phone_Num"></param>
/// <returns></returns>
public List <Admin> getAdminByEmail(string Email)
{
LD = LinkDatabase.getInstance();
List <Admin> users = new List <Admin>();
Admin temp;
string customQuerry = @"SELECT * FROM " + LD.databaseName + @".ADMIN WHERE Email = '" + Email + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(customQuerry);
if (reader != null)
{
while (reader.Read())
{
temp = new Admin();
temp.email = reader.GetString("Email");
temp.role = reader.GetString("Role");
users.Add(temp);
}
}
}
catch (Exception e)
{
sw.Write("Failure in getUsers: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
LD.doClose();
return(users);
}
public bool checkAuthor(string author, string title)
{
bool result = false;
LD = LinkDatabase.getInstance();
string query = @"SELECT NAME, TITLE FROM " + LD.databaseName + @".AUTHOR WHERE NAME = '" +
author + "' AND TITLE = '" + title + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
result = true;
}
}
catch (Exception e)
{
sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
finally
{
LD.doClose();
}
return(result);
}
public List <User> getUserListByPhone(string Phone_Num)
{
LD = LinkDatabase.getInstance();
List <User> users = new List <User>();
User temp;
string customQuerry = @"SELECT * FROM " + LD.databaseName + @".USER WHERE Phone_Num = '" + Phone_Num + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(customQuerry);
if (reader != null)
{
while (reader.Read())
{
temp = new User();
temp.phone = reader.GetString("Phone_Num");
temp.email = reader.GetString("Email");
temp.Uni = reader.GetString("UNI_NAME");
temp.fname = reader.GetString("Fname");
temp.lname = reader.GetString("Lname");
users.Add(temp);
}
}
}
catch (Exception e)
{
sw.Write("Failure in getUsers: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
LD.doClose();
return(users);
}
public bool checkUsedFor(string title, string course, string department)
{
bool result = false;
LD = LinkDatabase.getInstance();
string query = @"SELECT TITLE, COURSENUM, DEPARTMENT FROM " + LD.databaseName + @".USED_FOR WHERE TITLE = '" +
title + @"' AND COURSENUM = '" + course + @"' AND DEPARTMENT = '" + department + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
result = true;
}
} catch (Exception e)
{
sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
} finally
{
LD.doClose();
}
return(result);
}
public bool checkTextbook(string textbook, int edition)
{
bool result = false;
LD = LinkDatabase.getInstance();
string query = @"SELECT TITLE, EDITION FROM " + LD.databaseName + @".TEXTBOOK WHERE TITLE = '" +
textbook + @"' AND EDITION = '" + edition + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
result = true;
}
} catch (Exception e)
{
sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
} finally
{
LD.doClose();
}
return(result);
}
public List <Result> getUserPosts(string username)
{
LD = LinkDatabase.getInstance();
string query = @"SELECT TITLE, ID FROM " + LD.databaseName + @".POST WHERE EMAIL = '" + username +
@"';";
List <Result> search = new List <Result>();
try
{
Result temp;
MySqlDataReader reader = LD.executeGenericSQL(query);
while (reader.Read())
{
temp = new Result();
temp.ID = reader.GetInt32("ID").ToString();
temp.title = reader.GetString("Title");
search.Add(temp);
}
}
catch (MySqlException d)
{
sw.Write("Sql Error:" + d.Message);
}
catch (Exception e)
{
sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
finally
{
LD.doClose();
}
return(search);
}
public List <Result> getSearchTitles(string title, string department, string code, string university)
{
LD = LinkDatabase.getInstance();
//string query = @"SELECT TITLE FROM POST WHERE TITLE = '" + title + @"';";
string query = @"SELECT P.TITLE, P.ID FROM " + LD.databaseName + @".POST AS P, " + LD.databaseName + @".USED_FOR AS U" +
@" WHERE P.Title = U.Title AND P.Title LIKE '%" + title + @"%' AND U.Department = '" + department + @"' AND U.CourseNum = '"
+ code + @"' AND P.UNI_Name = '" + university + @"';";
List <Result> search = new List <Result>();
try
{
Result temp;
MySqlDataReader reader = LD.executeGenericSQL(query);
while (reader.Read())
{
temp = new Result();
temp.ID = reader.GetInt32("ID").ToString();
temp.title = reader.GetString("Title");
search.Add(temp);
}
} catch (MySqlException d)
{
sw.Write("Sql Error:" + d.Message);
}
catch (Exception e)
{
sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
LD.doClose();
return(search);
}
public UserInfo getUserInfo(string username)
{
LD = LinkDatabase.getInstance();
string query = @"SELECT UNI_NAME, PHONE_NUM FROM " + LD.databaseName + @".USER WHERE EMAIL = '" +
username + @"';";
UserInfo info = new UserInfo();
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
info.phone = reader.GetString("PHONE_NUM");
info.university = reader.GetString("UNI_NAME");
}
}
catch (Exception e)
{
sw.Write("Failure in getPost: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
LD.doClose();
return(info);
}
/// <summary>
/// Method to get all the universites in a list
/// </summary>
/// <returns></returns>
public List <UniData> getUniversitiesByName(string UNI_NAME)
{
LD = LinkDatabase.getInstance();
string query = @"SELECT * FROM " + LD.databaseName + @".UNIVERSITY WHERE UNI_NAME = '" + UNI_NAME + @"';";
List <UniData> output = new List <UniData>();
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
UniData temp;
while (reader.Read())
{
temp = new UniData();
temp.name = reader.GetString("UNI_NAME");
temp.city = reader.GetString("City");
temp.prov_state = reader.GetString("Prov_State");
temp.country = reader.GetString("Country");
output.Add(temp);
}
}
catch (Exception e)
{
sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
} finally {
LD.doClose();
}
return(output);
}
/// <summary>
///
/// </summary>
/// <param name="Email"></param>
/// <returns></returns>
public List <ReviewResults> getReviewByEmail(string Email)
{
LD = LinkDatabase.getInstance();
List <ReviewResults> reviews = new List <ReviewResults>();
ReviewResults temp;
string customQuerry = @"SELECT * FROM " + LD.databaseName + @".REVIEW WHERE Email = '" + Email + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(customQuerry);
if (reader != null)
{
while (reader.Read())
{
temp = new ReviewResults();
temp.Reviewer_Email = reader.GetString("Reviewer_Email");
temp.Description = reader.GetString("Description");
temp.Rating = reader.GetInt32("Rating");
reviews.Add(temp);
}
}
}
catch (Exception e)
{
sw.Write("Failure in getUsers: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
LD.doClose();
return(reviews);
}
public string getUserPhone(string email)
{
LD = LinkDatabase.getInstance();
string query = @"SELECT PHONE_NUM FROM " + LD.databaseName + @".USER WHERE EMAIL = '" + email + @"';";
string result = "";
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
result = reader.GetString("Phone_num");
}
} catch (Exception e)
{
sw.Write("Failure in getPhone: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
} finally
{
LD.doClose();
}
return(result);
}
/// <summary>
/// Main method used to get posts from front page search
/// </summary>
/// <param name="courseName"></param>
/// <param name="title"></param>
/// <param name="courseCode"></param>
/// <param name="UniName"></param>
//public List<Post> getPost(string department, string title, string courseCode, string UniName)
public Post getPost(string id)
{
LD = LinkDatabase.getInstance();
string query = @"SELECT * FROM " + LD.databaseName + @".POST NATURAL JOIN " + LD.databaseName +
@".USED_FOR WHERE " + LD.databaseName + @".POST.ID = '" + id + @"';";
Post temp = new Post();
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
temp.ID = reader.GetInt32("ID");
temp.Phone = reader.GetString("Phone_Num");
temp.email = reader.GetString("Email");
temp.Uni = reader.GetString("UNI_NAME");
temp.date = reader.GetString("Date");
// temp.bookType = reader.GetString("BookType");
temp.condition = reader.GetString("Book_Condition");
temp.price = reader.GetDouble("Price");
temp.description = reader.GetString("Description");
temp.Title = reader.GetString("Title");
temp.department = reader.GetString("Department");
temp.code = reader.GetString("CourseNum");
// outPost.Add(temp);
}
}
catch (Exception e)
{
sw.Write("Failure in getPost: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
LD.doClose();
return(temp);
}
public bool checkCourse(string code, string department, string university)
{
bool result = false;
LD = LinkDatabase.getInstance();
string query = @"SELECT COURSENUM, DEPARTMENT, UNI_NAME FROM " + LD.databaseName + @".COURSE WHERE COURSENUM = '" +
code + @"' AND DEPARTMENT = '" + department + @"' AND UNI_NAME = '" + university + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
result = true;
}
} catch (Exception e)
{
sw.Write("Failure in insertPost textbook " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
} finally
{
LD.doClose();
}
return(result);
}
public bool favouriteExists(string phone, string email, string id)
{
bool result = false;
LD = LinkDatabase.getInstance();
string query = @"SELECT * FROM " + LD.databaseName + @".FAVOURITES WHERE PHONE_NUM = '" + phone +
@"' AND EMAIL = '" + email + @"' AND ID = '" + id + @"';";
try
{
MySqlDataReader reader = LD.executeGenericSQL(query);
if (reader.Read())
{
result = true;
}
} catch (Exception e)
{
sw.Write(e.Message);
} finally
{
LD.doClose();
}
return(result);
}
