/// <summary>
/// This method removes a university by the given name
/// </summary>
/// <param name="Uni_Name"></param>
public void removeUniversityByName(String Uni_Name)
{
LD = LinkDatabase.getInstance();
string query = @"DELETE FROM " + LD.databaseName + @".UNIVERSITY WHERE UNI_NAME = '" + Uni_Name + @"';";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
public void modifyUserUniversity(string newUni, string oldUni, string userEmail)
{
LD = LinkDatabase.getInstance();
string query = @"UPDATE " + LD.databaseName + @".USER SET UNI_NAME = '" + newUni + @"' WHERE UNI_NAME = '" + oldUni + @"'AND Email = '" + userEmail + @"'; ";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
/// <summary>
///
/// </summary>
/// <param name="phone"></param>
/// <param name="email"></param>
/// <param name="relatedID"></param>
public void insertFavourite(string phone, string email, int relatedID)
{
LD = LinkDatabase.getInstance();
string query = @"INSERT INTO " + LD.databaseName + ".FAVOURITES(Phone_Num, Email, ID)" + @"VALUES('" + phone + "','" + email + "','" + relatedID + "');";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
/// <summary>
/// Method to add a new university to the db
/// </summary>
/// <param name="name"></param>
/// <param name="city"></param>
/// <param name="prov"></param>
/// <param name="Country"></param>
public void insertUniversity(string name, string city, string prov, string Country)
{
LD = LinkDatabase.getInstance();
string query = @"INSERT INTO " + LD.databaseName + ".UNIVERSITY(UNI_NAME, City, Prov_State, Country)" + @"VALUES('" + name + "','" + city + "','" + prov + "','" + Country + "');";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
/// <summary>
/// This function removes an Admin given its email.
/// </summary>
/// <param name="email"></param>
public void removeAdminByEmail(String email)
{
LD = LinkDatabase.getInstance();
string query = @"DELETE FROM " + LD.databaseName + @".ADMIN WHERE Email = '" + email + @"';";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
/// <summary>
///
/// </summary>
/// <param name="phone"></param>
/// <param name="email"></param>
/// <param name="uni"></param>
/// <param name="fname"></param>
/// <param name="lname"></param>
/// <param name="PW"></param>
public void insertUser(string phone, string email, string uni, string fname, string lname, string PW)
{
LD = LinkDatabase.getInstance();
string query = @"INSERT INTO " + LD.databaseName + ".USER(Phone_Num, Email, UNI_NAME, Fname, Lname, Password)" +
@"VALUES('" + phone + "','" + email + "','" + uni + "','" + fname + "','" + lname + "','" + PW + "');";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
/// <summary>
///
/// </summary>
/// <param name="email"></param>
/// <param name="role"></param>
public void insertReview(string Phone_Num, string Email, string Reviewer_Email, string Description, int Rating)
{
LD = LinkDatabase.getInstance();
string query = @"INSERT INTO " + LD.databaseName + ".REVIEW(Phone_Num, Email, Reviewer_Email, Description, Rating)" +
@"VALUES('" + Phone_Num + "','" + Email + "','" + Reviewer_Email + "','" + Description + "','" + Rating + "');";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
/// <summary>
/// This function inserts an admin into the database
/// </summary>
/// <param name="phone"></param>
/// <param name="email"></param>
/// <param name="uni"></param>
/// <param name="fname"></param>
/// <param name="lname"></param>
/// <param name="PW"></param>
public void insertAdmin(string email, string role)
{
LD = LinkDatabase.getInstance();
string query = @"INSERT INTO " + LD.databaseName + ".ADMIN(Email, Role)" +
@"VALUES('" + email + "','" + role + "');";
try
{
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in insertFavourite: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
/// <summary>
///
/// </summary>
public bool deletePost(string id)
{
LD = LinkDatabase.getInstance();
string query = @"DELETE FROM " + LD.databaseName + @".POST WHERE ID = '" + id + @"';";
try
{
LD.executeNonQueryGeneric(query);
return(true);
}
catch (Exception e)
{
sw.Write("Failure in deletePost: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
return(false);
}
public void saveFavourite(string phone, string email, string id)
{
if (!favouriteExists(phone, email, id))
{
string query = @"INSERT INTO " + LD.databaseName + @".FAVOURITES(phone_num, email, id)VALUES('" + phone +
@"', '" + email + @"', '" + id + @"');";
try
{
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query);
}
catch (Exception e)
{
sw.Write("Failure in getUniversities: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
}
/// <summary>
/// Method to insert a new post into the db
/// </summary>
/// <param name="post"></param>
public string insertPost(Post post)
{
string query1 = @"INSERT INTO " + LD.databaseName + @".POST(Phone_Num, Email, UNI_NAME, Date, Book_Condition, Price, Description, Title)" +
@"VALUES('" + post.Phone + @"','" + post.email + @"','" + post.Uni + @"','" + post.date + @"','" + post.condition + @"','" + post.price + @"','" + post.description +
@"','" + post.Title + @"');";
if (checkTextbook(post.Title, post.edition))
{
if (checkAuthor(post.author, post.Title))
{
//Title and Author both in data base, just insert post (Best Case)
try
{
if (!checkCourse(post.code, post.department, post.Uni))
{
string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" +
post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');";
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query5); //Insert course
}
if (!checkUsedFor(post.Title, post.code, post.department))
{
string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" +
post.Title + @"', '" + post.code + @"', '" + post.department + @"');";
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query4); //Insert used_for
}
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query1); //Insert Post
}
catch (Exception e)
{
}
}
else
{
//Textbook exists but not author, insert post and author (highly unlikely)
string query2 = @"INSERT INTO " + LD.databaseName + @".AUTHOR(name, title)VALUES('" +
post.author + @"', '" + post.Title + @"');";
try
{
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query2); //Insert author
if (!checkCourse(post.code, post.department, post.Uni))
{
string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" +
post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');";
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query5); //Insert course
}
if (!checkUsedFor(post.Title, post.code, post.department))
{
string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" +
post.Title + @"', '" + post.code + @"', '" + post.department + @"');";
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query4); //Insert used_for
}
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query1); //Insert post
}
catch (Exception e)
{
}
}
}
else if (checkAuthor(post.author, post.Title))
{
string query3 = @"INSERT INTO " + LD.databaseName + @".TEXTBOOK(title, edition)VALUES('" +
post.Title + @"', '" + post.edition + @"');";
//Textbook does not exist but author does(highly unlikely)
try
{
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query3); //Insert textbook
if (!checkCourse(post.code, post.department, post.Uni))
{
string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" +
post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');";
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query5); //Insert course
}
if (!checkUsedFor(post.Title, post.code, post.department))
{
string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" +
post.Title + @"', '" + post.code + @"', '" + post.department + @"');";
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query4); //Insert used_for
}
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query1); //Insert post
}
catch (Exception e)
{
sw.Write("Failure in insertPost textbook and post: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
else
{
string query2 = @"INSERT INTO " + LD.databaseName + @".AUTHOR(name, title)VALUES('" +
post.author + @"', '" + post.Title + @"');";
string query3 = @"INSERT INTO " + LD.databaseName + @".TEXTBOOK(title, edition)VALUES('" +
post.Title + @"', '" + post.edition + @"');";
try
{
//Both text book and author do not , insert all
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query3); //Insert textbook
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query2); //Insert author
if (!checkCourse(post.code, post.department, post.Uni))
{
LD = LinkDatabase.getInstance();
string query5 = @"INSERT INTO " + LD.databaseName + @".COURSE(course_title, coursenum, department, uni_name)VALUES('" +
post.coursename + @"', '" + post.code + @"', '" + post.department + @"', '" + post.Uni + @"');";
LD.executeNonQueryGeneric(query5); //Insert course
}
if (!checkUsedFor(post.Title, post.code, post.department))
{
LD = LinkDatabase.getInstance();
string query4 = @"INSERT INTO " + LD.databaseName + @".USED_FOR(title, coursenum, department)VALUES('" +
post.Title + @"', '" + post.code + @"', '" + post.department + @"');";
LD.executeNonQueryGeneric(query4); //Insert used_for
}
LD = LinkDatabase.getInstance();
LD.executeNonQueryGeneric(query1); //Insert post
}
catch (Exception e)
{
sw.Write("Failure in insertPost, post: " + e.Message + " " + DateTime.Now.ToString("MM/dd/yyyy h:mm tt"));
}
}
return("You have successfully created a post");
}
