public static Textbook getTextbook(int textbookID)
{
Textbook textbook = null;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("TextBookID = '{0}'", textbookID), "TextBooks");
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
string isbn = Convert.ToString(row["ISBN"]);
string title = Convert.ToString(row["BookTitle"]);
string author = Convert.ToString(row["Author"]);
string bookImageURL = Convert.ToString(row["BookImageURL"]);
int courseID = Convert.ToInt32(row["CourseID"]);
string courseName = CourseInfoHandler.getCourseName(courseID);
int storePrice = Convert.ToInt32(row["StorePrice"]);
textbook = new Textbook(isbn, title, author, courseName, bookImageURL, storePrice);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the textbook --- " + ex.Message);
}
return textbook;
}
public static IEnumerable<Textbook> getAllTextbooks()
{
List<Textbook> allTextbooks = new List<Textbook>();
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select("", "TextBooks");
foreach (DataRow row in dt.Rows)
{
string isbn = Convert.ToString(row["ISBN"]);
string title = Convert.ToString(row["BookTitle"]);
string author = Convert.ToString(row["Author"]);
string bookImageURL = Convert.ToString(row["BookImageURL"]);
int courseID = Convert.ToInt32(row["CourseID"]);
string courseName = CourseInfoHandler.getCourseName(courseID);
int storePrice = Convert.ToInt32(row["StorePrice"]);
Textbook textbook = new Textbook(isbn, title, author, courseName, bookImageURL, storePrice);
allTextbooks.Add(textbook);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving all textbooks --- " + ex.Message);
}
return allTextbooks;
}
public static int AddUser(UserProfile elm)
{
int id = -1;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("FacebookID = '{0}'", elm.FacebookID), "UserProfile");
if (dt == null ||dt.Rows.Count == 0)
{
Dictionary<string, string> Profile = new Dictionary<string, string>();
Profile.Add("FacebookID", elm.FacebookID);
Profile.Add("Name", elm.Name);
Profile.Add("Email", elm.Email);
Profile.Add("FacebookProfileLink", elm.FacebookProfileLink);
Profile.Add("Gender", elm.Gender);
Profile.Add("IsActive", "1");
Profile.Add("IsDeleted", "0");
Profile.Add("CreatedDate", Convert.ToString(DateTime.Now));
Profile.Add("ModifiedDate", Convert.ToString(DateTime.Now));
id = DAL.insert(Profile, "UserProfile");
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in adding a new user --- " + ex.Message);
}
return id;
}
public static int createBid(Bid bid)
{
int bidID = -1;
DataAccessLayer DAL = new DataAccessLayer();
DataTable bidDupPrices = DAL.select(String.Format("BidPrice = '{0}' AND PostID = '{1}'", bid.BidPrice, bid.PostID), "Bids");
if (bidDupPrices.Rows.Count > 0)
{
return bidID;
}
Dictionary<string, string> Bid = new Dictionary<string, string>();
Bid.Add("BidPrice", Convert.ToString(bid.BidPrice));
Bid.Add("PostID", Convert.ToString(bid.PostID));
Bid.Add("BidderID", Convert.ToString(bid.BidderID));
Bid.Add("viaEmail", Convert.ToString(bid.BidviaEmail));
Bid.Add("IsActive", Convert.ToString(true));
Bid.Add("IsDeleted", Convert.ToString(false));
Bid.Add("CreatedDate", Convert.ToString(DateTime.Now));
Bid.Add("ModifiedDate", Convert.ToString(DateTime.Now));
bidID = DAL.insert(Bid, "Bids");
return bidID;
}
public static UserProfile getUserProfile(int profileID)
{
UserProfile profile = null;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("ProfileID = '{0}'", profileID), "UserProfile");
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
string facebookID = Convert.ToString(row["FacebookID"]);
string name = Convert.ToString(row["Name"]);
string email = Convert.ToString(row["Email"]);
string facebookProfileLink = Convert.ToString(row["FacebookProfileLink"]);
string gender = Convert.ToString(row["Gender"]);
profile = new UserProfile(name, facebookProfileLink, facebookID, gender, email, String.Empty);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message);
}
return profile;
}
public static int insert(Post newPost)
{
int id = -1;
try
{
DataAccessLayer DAL = new DataAccessLayer();
// DataTable dt = DAL.select(String.Format("ProfileID = '{0}' AND TextBookID = '{1}' AND IsBuy = '{2}'", newPost.ProfileID, newPost.TextBookID, newPost.IsBuy), "Posts");
// if (dt == null || dt.Rows.Count == 0)
// {
Dictionary<string, string> post = new Dictionary<string, string>();
post.Add("ProfileID", Convert.ToString(newPost.ProfileID));
post.Add("TextBookID", Convert.ToString(newPost.TextBookID));
post.Add("IsBuy", Convert.ToString(newPost.IsBuy));
post.Add("Price", Convert.ToString(newPost.Price));
post.Add("viaEmail", Convert.ToString(newPost.viaEmail));
post.Add("BookCondition", newPost.Condition);
post.Add("ExpiryDate", Convert.ToString(newPost.ExpiryDate));
post.Add("IsNegotiable", Convert.ToString(newPost.IsNegotiable));
post.Add("IsActive", "1");
post.Add("IsDeleted", "0");
post.Add("CreatedDate", Convert.ToString(DateTime.Now));
post.Add("ModifiedDate", Convert.ToString(DateTime.Now));
id = DAL.insert(post, "Posts");
// }
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in adding a new post --- " + ex.Message);
}
return id;
}
public static List<Bid> getBids(int postID)
{
List<Bid> bids = new List<Bid>();
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("postID = '{0}'", postID), "Bids");
foreach (DataRow row in dt.Rows)
{
int bidderID = Convert.ToInt32(row["BidderID"]);
DataTable buyerDt = DAL.select(String.Format("profileID = '{0}'", bidderID), "UserProfile");
DataRow buyerRow = buyerDt.Rows[0];
int bidID = Convert.ToInt32(row["BidID"]);
string bidder = Convert.ToString(buyerRow["Name"]);
int bidPrice = Convert.ToInt32(row["BidPrice"]);
bool viaEmail = Convert.ToBoolean(row["viaEmail"]);
Bid bid = new Bid(postID, bidderID, bidder, bidPrice, viaEmail, bidID);
bids.Add(bid);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the bids --- " + ex.Message);
}
return bids;
}
public static IEnumerable<MarketPost> getAllMarketPosts(bool isBuyPost)
{
List<MarketPost> allMarketPosts = new List<MarketPost>();
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("IsBuy = '{0}'", isBuyPost), "Posts");
foreach (DataRow row in dt.Rows)
{
int textBookID = Convert.ToInt32(row["TextBookID"]);
int profileID = Convert.ToInt32(row["ProfileID"]);
int price = Convert.ToInt32(row["Price"]);
int postID = Convert.ToInt32(row["PostID"]);
bool viaEmail = Convert.ToBoolean(row["viaEmail"]);
bool isNegotiable = Convert.ToBoolean(row["IsNegotiable"]);
bool isBuy = Convert.ToBoolean(row["IsBuy"]);
string condition = Convert.ToString(row["BookCondition"]);
DateTime datePosted = Convert.ToDateTime(row["CreatedDate"]);
UserProfile UserProfile = AccountHandler.getUserProfile(profileID);
string postedBy = UserProfile.Name;
string email = UserProfile.Email;
Textbook textbook = TextbookHandler.getTextbook(textBookID);
string title = textbook.Title;
string course = textbook.CourseName;
string isbn = textbook.ISBN;
string author = textbook.Author;
string bookImageURL = textbook.BookImageURL;
List<Bid> bids = BidHandler.getBids(postID);
MarketPost marketPost = new MarketPost(
title, isBuy, course,
condition, postedBy, datePosted,
isbn, author, bookImageURL,
price, bids, isNegotiable, email,
viaEmail, profileID, postID);
allMarketPosts.Add(marketPost);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving all market posts --- " + ex.Message);
}
return allMarketPosts;
}
public static bool deletePost(int postID)
{
bool success = false;
DataAccessLayer DAL = new DataAccessLayer();
try
{
DAL.delete(String.Format("postID = '{0}'", postID), "Posts");
}
catch (Exception ex)
{
Console.Write("ERROR: could not delete post ---- " + ex.Message);
}
return success;
}
public static int[] getCourseIDs(string courseName)
{
List<int> courseIDs = new List<int>();
try
{
courseName = courseName.Replace(" ", String.Empty);
string pattern = @"([A-Za-z]+)";
string[] result = Regex.Split(courseName, pattern);
string coursePrefix = result[1];
StringBuilder courseNumber = new StringBuilder();
for (int i = 2; i < result.Length; i++)
{
courseNumber.Append(result[i]);
}
string whereClause = String.Empty;
if (courseNumber.Equals(String.Empty))
{
whereClause = String.Format("CourseName LIKE '%{0}%'", coursePrefix);
}
else
{
whereClause = String.Format("CourseName LIKE '%{0}%{1}%'", coursePrefix, courseNumber.ToString());
}
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(whereClause, "CourseInfo", new string[] { "CourseID" });
foreach (DataRow row in dt.Rows)
{
int courseID = Convert.ToInt32(row["CourseID"]);
courseIDs.Add(courseID);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving course IDs by course name --- " + ex.Message);
}
return courseIDs.ToArray();
}
public bool InsertStatus(DemoModel Status)
{
DataAccessLayer dal = new DataAccessLayer();
bool Success = true;
try
{
/* Testing Insert */
Dictionary<string, string> dict = new Dictionary<string,string>();
dict.Add("StatusName", Status.StatusName);
// no need to add statusID, as that column is an identity column
dal.insert(dict, "TransactionStatus");
/* Testing Select */
DataTable dt = new DataTable();
dt = dal.select("StatusName = 'a'", "TransactionStatus");
foreach (DataRow row in dt.Rows) // Loop over the rows.
{
foreach (var col in row.ItemArray)
{
Debug.Write(col);
}
Debug.Write('\n');
}
/* Testing Update */
Dictionary<string, string> d = new Dictionary<string, string>();
d.Add("StatusName", "'asma_rox'");
dal.update("TransactionStatus", "StatusName = 'asma_sux'", d);
/* Manually go into SQL Server to see if the row is correctly updated */
/* Testing Delete */
dal.delete("StatusName = 'johnny'", "TransactionStatus");
/* now the db should not contain an entry with StatusName = 'johnny' */
}
catch (Exception)
{
Success = false;
}
return Success;
}
public static int getCourseID(string courseName)
{
int courseID = -1;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("CourseName = '{0}'", courseName), "CourseInfo");
if (dt != null && dt.Rows.Count > 0)
{
courseID = Convert.ToInt32(dt.Rows[0]["CourseID"]);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the course ID --- " + ex.Message);
}
return courseID;
}
public static int getPostID(Post newPost)
{
int postID = -1;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("ProfileID = '{0}' AND TextBookID = '{1}' AND IsBuy = '{2}'", newPost.ProfileID, newPost.TextBookID, newPost.IsBuy), "Posts");
if (dt != null && dt.Rows.Count > 0)
{
postID = Convert.ToInt32(dt.Rows[0]["PostID"]);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the post ID --- " + ex.Message);
}
return postID;
}
public static int getProfileID_Facebook(string facebookID)
{
int profileID = -1;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("FacebookID = '{0}'", facebookID), "UserProfile");
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
profileID = Convert.ToInt32(row["ProfileID"]);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message);
}
return profileID;
}
public static IEnumerable<Textbook> getTextbooksByCourse(string courseName)
{
List<Textbook> textbooks = new List<Textbook>();
try
{
int[] courseIDs = CourseInfoHandler.getCourseIDs(courseName);
foreach (int courseID in courseIDs)
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("CourseID = '{0}'", courseID), "TextBooks");
foreach (DataRow row in dt.Rows)
{
string isbn = Convert.ToString(row["ISBN"]);
string title = Convert.ToString(row["BookTitle"]);
string author = Convert.ToString(row["Author"]);
string bookImageURL = Convert.ToString(row["BookImageURL"]);
string full_courseName = CourseInfoHandler.getCourseName(courseID);
int storePrice = Convert.ToInt32(row["StorePrice"]);
Textbook textbook = new Textbook(isbn, title, author, full_courseName, bookImageURL, storePrice);
textbooks.Add(textbook);
}
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving textbooks by course name --- " + ex.Message);
}
return textbooks;
}
public static int insert(CourseInfo newCourseInfo)
{
int id = -1;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("CourseName = '{0}'", newCourseInfo.CourseName), "CourseInfo");
if (dt == null || dt.Rows.Count == 0)
{
Dictionary<string, string> courseInfo = new Dictionary<string, string>();
courseInfo.Add("CourseName", newCourseInfo.CourseName);
courseInfo.Add("Description", newCourseInfo.Description);
courseInfo.Add("IsActive", "1");
courseInfo.Add("IsDeleted", "0");
courseInfo.Add("CreatedDate", Convert.ToString(DateTime.Now));
courseInfo.Add("ModifiedDate", Convert.ToString(DateTime.Now));
id = DAL.insert(courseInfo, "CourseInfo");
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in adding a new course --- " + ex.Message);
}
return id;
}
public static MarketPost getMarketPost(int postID)
{
MarketPost marketPost = null;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("PostID = '{0}'", postID), "Posts");
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
int profileID = Convert.ToInt32(row["ProfileID"]);
UserProfile UserProfile = AccountHandler.getUserProfile(profileID);
string postedBy = UserProfile.Name;
string email = UserProfile.Email;
int textbookID = Convert.ToInt32(row["TextBookID"]);
Textbook textbook = TextbookHandler.getTextbook(textbookID);
string isbn = textbook.ISBN;
string title = textbook.Title;
string author = textbook.Author;
string course = textbook.CourseName;
string bookImageURL = textbook.BookImageURL;
bool isBuy = Convert.ToBoolean(row["IsBuy"]);
bool isNegotiable = Convert.ToBoolean(row["IsNegotiable"]);
string condition = Convert.ToString(row["BookCondition"]);
int price = Convert.ToInt32(row["Price"]);
DateTime datePosted = Convert.ToDateTime(row["CreatedDate"]);
List<Bid> bids = BidHandler.getBids(postID);
bool viaEmail = Convert.ToBoolean(row["viaEmail"]);
marketPost = new MarketPost(
title, isBuy, course,
condition, postedBy, datePosted,
isbn, author, bookImageURL,
price, bids, isNegotiable, email,
viaEmail, profileID, postID);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the market post --- " + ex.Message);
}
return marketPost;
}
public static CourseInfo getCourseInfo(int courseID)
{
CourseInfo courseInfo = null;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("CourseID = '{0}'", courseID), "CourseInfo");
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
string courseName = Convert.ToString(row["CourseName"]);
string description = Convert.ToString(row["Description"]);
courseInfo = new CourseInfo(courseName, description);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the course information --- " + ex.Message);
}
return courseInfo;
}
public static int getTextbookID(string course, string title)
{
int textbookID = -1;
try
{
int courseID = CourseInfoHandler.getCourseID(course);
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("CourseID = '{0}' AND BookTitle = '{1}'", courseID, title), "TextBooks");
if (dt != null && dt.Rows.Count > 0)
{
textbookID = Convert.ToInt32(dt.Rows[0]["TextBookID"]);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the textbook ID --- " + ex.Message);
}
return textbookID;
}
public static int[] getTextbookIDsByCourse(string courseName)
{
List<int> textbookIDs = new List<int>();
try
{
int[] courseIDs = CourseInfoHandler.getCourseIDs(courseName);
foreach (int courseID in courseIDs)
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("CourseID = '{0}'", courseID), "TextBooks", new string[] { "TextBookID" });
foreach (DataRow row in dt.Rows)
{
int textbookID = Convert.ToInt32(row["TextBookID"]);
textbookIDs.Add(textbookID);
}
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving textbook IDs by course name --- " + ex.Message);
}
return textbookIDs.ToArray();
}
public static int[] getTextbookIDsByISBN(string isbn)
{
List<int> textbookIDs = new List<int>();
try
{
// ISBN parsing (may or may not contain hyphens)
isbn = isbn.Replace("-", String.Empty);
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("ISBN LIKE '%{0}%'", isbn), "TextBooks", new string[] { "TextBookID" });
foreach (DataRow row in dt.Rows)
{
int textbookID = Convert.ToInt32(row["TextBookID"]);
textbookIDs.Add(textbookID);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving textbook IDs by ISBN --- " + ex.Message);
}
return textbookIDs.ToArray();
}
public static bool updateUserProfile_Email(int ProfileID, string Email)
{
bool success = false;
try
{
Dictionary<string, string> Profile = new Dictionary<string,string>();
Profile.Add("Email", String.Format("'{0}'", Email));
DataAccessLayer DAL = new DataAccessLayer();
DAL.update("UserProfile", String.Format("ProfileID = '{0}'", ProfileID), Profile);
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in updating user profile with ethe given email --- " + ex.Message);
}
return success;
}
public static int[] getTextbookIDsByTitle(string title)
{
List<int> textbookIDs = new List<int>();
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("BookTitle LIKE '%{0}%'", title), "TextBooks", new string[] { "TextBookID" });
foreach (DataRow row in dt.Rows)
{
int textbookID = Convert.ToInt32(row["TextBookID"]);
textbookIDs.Add(textbookID);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving textbook IDs by book title --- " + ex.Message);
}
return textbookIDs.ToArray();
}
public static int insert(Textbook newBook)
{
int id = -1;
try
{
DataAccessLayer DAL = new DataAccessLayer();
DataTable dt = DAL.select(String.Format("ISBN = '{0}'", newBook.ISBN), "TextBooks");
if (dt == null || dt.Rows.Count == 0)
{
int courseID = CourseInfoHandler.getCourseID(newBook.CourseName);
Dictionary<string, string> textbook = new Dictionary<string, string>();
textbook.Add("ISBN", newBook.ISBN);
textbook.Add("BookTitle", newBook.Title);
textbook.Add("Author", newBook.Author);
textbook.Add("CourseID", courseID.ToString());
textbook.Add("BookImageURL", newBook.BookImageURL);
textbook.Add("StorePrice", Convert.ToString(newBook.StorePrice));
textbook.Add("IsActive", "1");
textbook.Add("IsDeleted", "0");
textbook.Add("CreatedDate", Convert.ToString(DateTime.Now));
textbook.Add("ModifiedDate", Convert.ToString(DateTime.Now));
id = DAL.insert(textbook, "TextBooks");
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in adding a new textbook --- " + ex.Message);
}
return id;
}