public static int createTextBook(Textbook book)
{
int BookId = -1;
try
{
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("ISBN = '{0}'", book.ISBN), "TextBooks", NumRows : 1);
if (dt == null || dt.Rows.Count == 0)
{
Dictionary<string, object> textbook = new Dictionary<string, object>();
textbook.Add("ISBN", book.ISBN);
textbook.Add("BookTitle", book.BookTitle);
textbook.Add("Author", book.Author);
textbook.Add("CourseId", book.CourseId);
textbook.Add("BookImageURL", book.BookImageUrl);
textbook.Add("StorePrice", book.StorePrice);
textbook.Add("IsActive", 1);
textbook.Add("IsDeleted", 0);
textbook.Add("CreatedDate", DateTime.Now);
textbook.Add("ModifiedDate", DateTime.Now);
BookId = da.insert(textbook, "TextBooks");
}
}
catch (Exception ex) { Console.Write(ex.Message + " " + ex.StackTrace); }
return BookId;
}
public static int CreateProfile(string Name, string Email)
{
int id = -1;
try
{
DataAccess DAL = new DataAccess();
DataTable dt = DAL.select(String.Format("UserName = '******'", Email), "UserProfile", NumRows : 1);
if (dt == null || dt.Rows.Count == 0)
{
Dictionary<string, object> profile = new Dictionary<string, object>();
profile.Add("Name", Name);
profile.Add("Email", Email);
profile.Add("IsActive", 1);
profile.Add("IsDeleted", 0);
profile.Add("CreatedDate", DateTime.Now);
profile.Add("ModifiedDate", DateTime.Now);
id = DAL.insert(profile, "UserProfile");
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in adding a new course --- " + ex.Message);
}
return id;
}
public static int CreateCourse(string courseName)
{
int id = -1;
try
{
DataAccess DAL = new DataAccess();
DataTable dt = DAL.select(String.Format("CourseName = '{0}'", courseName), "CourseInfo", NumRows : 1);
if (dt == null || dt.Rows.Count == 0)
{
Dictionary<string, object> courseInfo = new Dictionary<string, object>();
courseInfo.Add("CourseName", courseName);
courseInfo.Add("Description", courseName);
courseInfo.Add("IsActive", 1);
courseInfo.Add("IsDeleted", 0);
courseInfo.Add("CreatedDate", DateTime.Now);
courseInfo.Add("ModifiedDate", 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 IEnumerable<Textbook> getAllTextbooks()
{
List<Textbook> textbooks = new List<Textbook>();
try
{
DataAccess DAL = new DataAccess();
DataTable dt = DAL.select("", "TextBooks");
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
int textbookId = Convert.ToInt32(row["TextBookId"]);
string bookTitle = Convert.ToString(row["BookTitle"]);
string isbn = Convert.ToString(row["ISBN"]);
string author = Convert.ToString(row["Author"]);
string bookImageUrl = row["BookImageURL"] is DBNull ? null : Convert.ToString(row["BookImageURL"]);
int courseId = Convert.ToInt32(row["CourseId"]);
string courseName = CourseHandler.getCourseName(courseId);
decimal? storePrice = row["StorePrice"] is DBNull ? (decimal?) null : Convert.ToDecimal(row["StorePrice"]);
int isActive = Convert.ToInt32(row["IsActive"]);
int isDeleted = Convert.ToInt32(row["IsDeleted"]);
DateTime createdDate = Convert.ToDateTime(row["CreatedDate"]);
DateTime modifiedDate = Convert.ToDateTime(row["ModifiedDate"]);
Textbook textbook = new Textbook(
textbookId,
bookTitle,
isbn,
author,
courseId,
courseName,
bookImageUrl,
storePrice,
isActive,
isDeleted,
createdDate,
modifiedDate
);
textbooks.Add(textbook);
}
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving all textbooks --- " + ex.Message);
}
return textbooks;
}
//Confirm Validation -> Checks to see if the user has confirmed using the token
//If a user that hasn't confirmed tries to log in we will send them an incorrect username or password error
public static bool ConfirmValidation(string Email)
{
try
{
var DAL = new DataAccess();
var dt = DAL.select(String.Format("UserName = '******' AND IsValid = '1' ", Email), "UserProfile", NumRows: 1);
return dt.Rows.Count > 0;
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message);
}
return false; //uh oh ..
}
public static IEnumerable<Comment> getComments(int TransactionId)
{
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("TransactionId = '{0}' AND IsActive = 1 AND IsDeleted = 0", TransactionId), "TransactionComments");
IEnumerable<Comment> comments = dt.AsEnumerable().Select(
x => new Comment(
Convert.ToInt32(x["CommentId"]),
Convert.ToString(x["Comment"]),
Convert.ToInt32(x["UserId"]),
(ActionBy)Convert.ToInt32(x["ActionBy"]),
Convert.ToInt32(x["TransactionId"]),
Convert.ToInt32(x["IsActive"]),
Convert.ToInt32(x["IsDeleted"]),
Convert.ToDateTime(x["CreatedDate"]),
Convert.ToDateTime(x["ModifiedDate"]))).OrderBy(x => x.CreatedDate).ToList();
return comments;
}
//in B.S. UserName == Email
public static string GetFacebookId(int userId)
{
string facebookId = null;
try
{
DataAccess DAL = new DataAccess();
DataTable dt = DAL.select(String.Format("UserId = '{0}'", userId), "UserProfile", NumRows: 1, ColumnNames: new string[] { "FacebookId" });
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
facebookId = row["FacebookId"] is DBNull ? null : Convert.ToString(row["FacebookId"]);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the facebook id for user --- " + ex.Message);
}
return facebookId;
}
public static Course getCourse(int courseId)
{
Course course = null;
try
{
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("CourseId = '{0}' AND IsActive = 1 AND IsDeleted = 0", courseId), "CourseInfo", NumRows : 1);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
string CourseName = Convert.ToString(row["CourseName"]);
string Description = Convert.ToString(row["Description"]);
int IsActive = Convert.ToInt32(row["IsActive"]);
int IsDeleted = Convert.ToInt32(row["IsDeleted"]);
DateTime CreatedDate = Convert.ToDateTime(row["CreatedDate"]);
DateTime ModifiedDate = Convert.ToDateTime(row["ModifiedDate"]);
course = new Course(
courseId,
CourseName,
Description,
IsActive,
IsDeleted,
CreatedDate,
ModifiedDate
);
}
}
catch (Exception ex)
{
Console.Write(ex.Message + " " + ex.StackTrace);
}
return course;
}
public static Post getPost(int postId)
{
Post post = null;
try
{
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("PostId = '{0}' AND IsDeleted = 0", postId), "Posts", NumRows : 1);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
int profileId = Convert.ToInt32(row["UserId"]);
int textbookId = Convert.ToInt32(row["TextBookId"]);
ActionBy actionBy = (ActionBy)Convert.ToInt32(row["ActionBy"]);
int price = Convert.ToInt32(row["Price"]);
BookCondition bookCondition = (BookCondition)Convert.ToInt32(row["BookCondition"]);
int isTransacting = Convert.ToInt32(row["IsTransacting"]);
int isActive = Convert.ToInt32(row["IsActive"]);
int isDeleted = Convert.ToInt32(row["IsDeleted"]);
DateTime createdDate = (DateTime)row["CreatedDate"];
DateTime modifiedDate = (DateTime)row["ModifiedDate"];
post = new Post(
postId,
profileId,
textbookId,
actionBy,
price,
bookCondition,
isTransacting,
isActive,
isDeleted,
createdDate,
modifiedDate
);
}
}
catch (Exception ex)
{
Console.Write(ex.Message + " " + ex.StackTrace);
}
return post;
}
public static string getCourseName(int courseId)
{
string courseName = null;
try
{
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("CourseId = '{0}' AND IsActive = 1 AND IsDeleted = 0", courseId), "CourseInfo", NumRows: 1, ColumnNames: new string[] {"CourseName"});
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
courseName = Convert.ToString(row["CourseName"]);
}
}
catch (Exception ex)
{
Console.Write(ex.Message + " " + ex.StackTrace);
}
return courseName;
}
public static Post findMatchingPost(Post post)
{
Post matchingPost = null;
try
{
var counterparty = post.ActionBy == ActionBy.Buyer ? ActionBy.Seller : ActionBy.Buyer;
string query = String.Format("UserId <> {0} AND TextBookId = {1} AND ActionBy = {2} " +
"AND IsTransacting = 0 AND IsActive = 1 AND IsDeleted = 0",
post.UserId,
post.TextBookId,
(int)counterparty
);
List<SortColumn> sortColumns = new List<SortColumn>();
if (post.ActionBy == ActionBy.Buyer) //buyer
{
query += String.Format("AND Price <= {0} AND BookCondition >= {1}",
post.Price,
(int)post.BookCondition
);
sortColumns.Add(new SortColumn("Price", "ASC"));
}
else //seller
{
query += String.Format("AND Price >= {0} AND BookCondition <= {1}",
post.Price,
(int)post.BookCondition
);
sortColumns.Add(new SortColumn("Price", "DESC"));
}
DataAccess da = new DataAccess();
DataTable dt = da.select(query, "Posts", NumRows : 1, SortColumns : sortColumns);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
int postId = Convert.ToInt32(row["PostId"]);
int profileId = Convert.ToInt32(row["UserId"]);
int textBookId = Convert.ToInt32(row["TextBookId"]);
ActionBy actionBy = (ActionBy)Convert.ToInt32(row["ActionBy"]);
int price = Convert.ToInt32(row["Price"]);
BookCondition bookCondition = (BookCondition)Convert.ToInt32(row["BookCondition"]);
int isTransacting = Convert.ToInt32(row["IsTransacting"]);
int isActive = Convert.ToInt32(row["IsActive"]);
int isDeleted = Convert.ToInt32(row["IsDeleted"]);
DateTime createdDate = (DateTime)row["CreatedDate"];
DateTime modifiedDate = (DateTime)row["ModifiedDate"];
matchingPost = new Post(
postId,
profileId,
textBookId,
actionBy,
price,
bookCondition,
isTransacting,
isActive,
isDeleted,
createdDate,
modifiedDate
);
}
}
catch (Exception ex)
{
Console.Write(ex.Message + " " + ex.StackTrace);
}
return matchingPost;
}
//User clicked on link
//We shall update the database to validate his/her account using the provided token
public static Profile ValidateProfile(Guid Token)
{
Profile profile = null;
try
{
var DAL = new DataAccess();
var dt = DAL.select(String.Format("ValidationToken = '{0}'", Token), "UserProfile", NumRows: 1);
if (dt != null && dt.Rows.Count > 0)
{
var updateDictionary = new Dictionary<string, object>();
updateDictionary.Add("IsValid", true);
DAL.update("UserProfile", "ValidationToken = '{0}'", updateDictionary);
var row = dt.Rows[0];
var UserName = Convert.ToString(row["UserName"]);
var profileId = Convert.ToInt32(row["UserId"]);
var displayName = Convert.ToString(row["DisplayName"]);
var facebookId = row["FacebookId"] is DBNull ? null : Convert.ToString(row["FacebookId"]);
var facebookLink = row["FacebookLink"] is DBNull ? null : Convert.ToString(row["FacebookLink"]);
profile = new Profile(profileId, displayName, UserName, facebookId, facebookLink);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message);
}
return profile;
}
//in B.S. UserName == Email
public static int GetProfileId(string Email)
{
int profileId = -1;
try
{
DataAccess DAL = new DataAccess();
DataTable dt = DAL.select(String.Format("UserName = '******'", Email), "UserProfile", NumRows : 1);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
profileId = Convert.ToInt32(row["UserId"]);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message);
}
return profileId;
}
public static Textbook getTextbook(int textbookId)
{
Textbook book = null;
try
{
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("TextBookId = '{0}' AND IsActive = 1 AND IsDeleted = 0", textbookId), "TextBooks", NumRows : 1);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
string bookTitle = Convert.ToString(row["BookTitle"]);
string isbn = Convert.ToString(row["ISBN"]);
string author = Convert.ToString(row["Author"]);
string bookImageUrl = row["BookImageURL"] is DBNull ? null : Convert.ToString(row["BookImageURL"]);
int courseId = Convert.ToInt32(row["CourseId"]);
string courseName = CourseHandler.getCourseName(courseId);
decimal? storePrice = row["StorePrice"] is DBNull ? (decimal?)null : Convert.ToDecimal(row["StorePrice"]);
int isActive = Convert.ToInt32(row["IsActive"]);
int isDeleted = Convert.ToInt32(row["IsDeleted"]);
DateTime createdDate = Convert.ToDateTime(row["CreatedDate"]);
DateTime modifiedDate = Convert.ToDateTime(row["ModifiedDate"]);
book = new Textbook(
textbookId,
bookTitle,
isbn,
author,
courseId,
courseName,
bookImageUrl,
storePrice,
isActive,
isDeleted,
createdDate,
modifiedDate
);
}
}
catch (Exception ex) { Console.Write(ex.Message + " " + ex.StackTrace); }
return book;
}
public static IEnumerable<Transaction> getUserTransactionHistory(int UserId)
{
DataAccess da = new DataAccess();
List<int> transactionIds = da.select(String.Format("UserId = '{0}'", UserId), "TransactionHistory").AsEnumerable().Select(x => (int)x["TransactionId"]).ToList();
string Ids = string.Join(",", transactionIds);
DataTable dt = da.select(String.Format("TransactionId IN ({0})", Ids), "Transactions");
IEnumerable<Transaction> transactions = dt.AsEnumerable().Select(
x => new Transaction(
Convert.ToInt32(x["TransactionId"]),
Convert.ToInt32(x["TextbookId"]),
Convert.ToInt32(x["SellerId"]),
Convert.ToInt32(x["BuyerId"]),
Convert.ToInt32(x["SellerPostId"]),
Convert.ToInt32(x["BuyerPostId"]),
x["FinalPrice"] is DBNull ? (int?) null : Convert.ToInt32(x["FinalPrice"]),
x["ConfirmPrice"] is DBNull ? (int?)null : Convert.ToInt32(x["ConfirmPrice"]),
Convert.ToInt32(x["InitialPrice"]),
(Confirmed) Convert.ToInt32(x["Confirmed"]),
Convert.ToInt32(x["IsActive"]),
Convert.ToInt32(x["IsDeleted"]),
Convert.ToDateTime(x["CreatedDate"]),
Convert.ToDateTime(x["ModifiedDate"]))).ToList();
return transactions;
}
public static Transaction getTransaction(int transactionId)
{
Transaction transaction = null;
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("TransactionId = '{0}'", transactionId), "Transactions", NumRows : 1);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
int TransactionId = Convert.ToInt32(row["TransactionId"]);
int TextbookId = Convert.ToInt32(row["TextbookId"]);
int SellerId = Convert.ToInt32(row["SellerId"]);
int BuyerId = Convert.ToInt32(row["BuyerId"]);
int SellerPostId = Convert.ToInt32(row["SellerPostId"]);
int BuyerPostId = Convert.ToInt32(row["BuyerPostId"]);
int? FinalPrice = row["FinalPrice"] is DBNull ? (int?)null : Convert.ToInt32(row["FinalPrice"]);
int? ConfirmPrice = row["ConfirmPrice"] is DBNull ? (int?)null : Convert.ToInt32(row["ConfirmPrice"]);
int InitialPrice = Convert.ToInt32(row["InitialPrice"]);
Confirmed Confirmed = (Confirmed) Convert.ToInt32(row["Confirmed"]);
int IsActive = Convert.ToInt32(row["IsActive"]);
int IsDeleted = Convert.ToInt32(row["IsDeleted"]);
DateTime CreatedDate = Convert.ToDateTime(row["CreatedDate"]);
DateTime ModifiedDate = Convert.ToDateTime(row["ModifiedDate"]);
transaction = new Transaction(
TransactionId,
TextbookId,
SellerId,
BuyerId,
SellerPostId,
BuyerPostId,
FinalPrice,
ConfirmPrice,
InitialPrice,
Confirmed,
IsActive,
IsDeleted,
CreatedDate,
ModifiedDate
);
}
return transaction;
}
public static bool isPostAvailable(int postId)
{
bool availableForMatching = false;
try
{
DataAccess da = new DataAccess();
DataTable dt = da.select(String.Format("PostId = '{0}' AND IsDeleted = 0", postId), "Posts", NumRows: 1);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
int isTransacting = Convert.ToInt32(row["IsTransacting"]);
int isActive = Convert.ToInt32(row["IsActive"]);
availableForMatching = isTransacting == 0 && isActive == 1;
}
}
catch (Exception e)
{
Console.Write("ERROR: Could not check post state with the given post id --- " + e.Message);
}
return availableForMatching;
}
public static Profile GetProfile(int userId)
{
Profile profile = null;
try
{
DataAccess DAL = new DataAccess();
DataTable dt = DAL.select(String.Format("UserId = '{0}'", userId), "UserProfile", NumRows : 1);
if (dt != null && dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
string name = Convert.ToString(row["DisplayName"]);
string email = Convert.ToString(row["UserName"]);
string facebookId = row["FacebookId"] is DBNull ? null : Convert.ToString(row["FacebookId"]);
string facebookLink = row["FacebookLink"] is DBNull ? null : Convert.ToString(row["FacebookLink"]);
profile = new Profile(userId, name, email, facebookId, facebookLink);
}
}
catch (Exception ex)
{
Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message);
}
return profile;
}
