public static List <Reservation> GetReservationByUsername(string username)
{
string sql = "select * from Reservation where username = '******'";
SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection());
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List <Reservation> reservations = new List <Reservation>();
Reservation r = null;
while (reader.Read())
{
// get the results of each column
int id = (int)reader["id"];
string isbn = (string)reader["isbn"];
DateTime create = (DateTime)reader["created_date"];
DateTime due = (DateTime)reader["due_date"];
double amount = (double)reader["amount"];
r = new Reservation(id, username, isbn, create, due, 0, amount);
reservations.Add(r);
}
return(reservations);
}
public static List<Review> GetReviewByUsername(string username)
{
string sql = "select * from Review where username = '******'";
SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection());
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<Review> reviews = new List<Review>();
Review r = null;
while (reader.Read())
{
// get the results of each column
int id = (int)reader["id"];
string title = (string)reader["title"];
string content = (string)reader["content"];
DateTime date = (DateTime)reader["date"];
string isbn = (string)reader["isbn"];
//int score = (int)reader["score"];
int score = 5;
r = new Review(id,title,content,date,isbn,username,score);
reviews.Add(r);
}
return reviews;
}
public static bool updateOrInsertReadingLog(string username, string isbn, float percent)
{
string sql = "select top 1 * from ReadingLog where username=@username and isbn=@isbn";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@isbn", isbn);
string nonQuery = "";
if (cmd.ExecuteReader().Read())
{
nonQuery =
"update ReadingLog set percentage = @percent where username=@username and isbn=@isbn";
}
else
{
nonQuery = "insert into ReadingLog(username, isbn, percentage) values (@username, @isbn, @percentage)";
}
var command = new SqlCommand(nonQuery, InitConnection.GetInstance().GetConnection());
command.Connection.Open();
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@isbn", isbn);
command.Parameters.AddWithValue("@percent", percent);
return(command.ExecuteNonQuery() == 1);
}
}
public static List<Review> GetReviewsByIsbn(string isbn)
{
List<Review> list = new List<Review>();
string sql = "select * from Review where isbn=@isbn";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@isbn", isbn);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
string title = read.GetString(1);
string content = read.GetString(2);
DateTime date = read.GetDateTime(3);
int score = read.GetInt16(6);
Review review = new Review();
review.Content = content;
review.Title = title;
review.Date = date;
review.Score = score;
list.Add(review);
}
}
return list;
}
public static bool LogInAsAdmin(string username, string pass)
{
string sql = "select * from Borrower where username=@username and password=@pass";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@pass", pass);
SqlDataReader reader = cmd.ExecuteReader();
return(reader.Read());
}
}
public static List <Book> GetBooksByCategoryf(string cName)
{
string sql =
"select * from Category c inner join Book_Category BC on c.category_name = BC.category_name inner join Book on Book.isbn = BC.isbn where c.category_name = @cName";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@cName", cName);
SqlDataReader read = cmd.ExecuteReader();
return(GetBookFromDataReader(read));
}
}
public static List <Book> GetBookIRent(string username)
{
string sql =
"select distinct Book.* from Book inner join Reservation R2 on Book.isbn = R2.isbn where R2.username = @username and due_date >= @date";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@date", DateTime.Now);
SqlDataReader reader = cmd.ExecuteReader();
return(GetBookFromDataReader(reader));
}
}
public static bool InsertReview(string title, string content, DateTime date, string isbn, string username,
int score)
{
string sql = "insert into Review values(@a, @b, @c, @d, @e, @f)";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@a", title);
cmd.Parameters.AddWithValue("@b", content);
cmd.Parameters.AddWithValue("@c", date);
cmd.Parameters.AddWithValue("@d", isbn);
cmd.Parameters.AddWithValue("@e", username);
cmd.Parameters.AddWithValue("@f", score);
return cmd.ExecuteNonQuery() == 1;
}
}
public static string GetAuthorName(string isbn)
{
string sql =
"select top 1 author_name from Author a inner join Book_Author ba on a.author_id = ba.author_id inner join Book b on ba.book_isbn = b.isbn where b.isbn = @isbn";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@isbn", isbn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
return((string)reader["author_name"]);
}
}
return("");
}
public static bool InsertReservation(string username, string isbn, DateTime reservedTime, DateTime expiredTime,
float amount)
{
Console.WriteLine("isbN fuckkk: " + isbn + " us: " + username);
string sql =
"insert into Reservation(username, isbn, created_date, due_date, amount) values (@username, @isbn, @reservedTime, @expiredTime, @amount)";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@isbn", isbn);
cmd.Parameters.AddWithValue("@reservedTime", reservedTime);
cmd.Parameters.AddWithValue("@expiredTime", expiredTime);
cmd.Parameters.AddWithValue("@amount", amount);
return(cmd.ExecuteNonQuery() == 1);
}
}
public static Borrower GetBorrowerByUsername(string username)
{
string sql = "select * from Borrower where username = '******'";
SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection());
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
Borrower b = null;
if (reader.Read())
{
// get the results of each column
string name = (string)reader["name"];
string email = (string)reader["email"];
double deposit = (double)reader["deposit"];
b = new Borrower(username, name, email, deposit);
}
return(b);
}
public static Book GetBookByBookCopyId(int copyId)
{
string sql =
"select top 1 * from Book inner join BookCopy BC on Book.isbn = BC.isbn where BC.book_id = @copyId";
using (var cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@copyId", copyId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (GetBookFromDataReader(reader).Count != 0)
{
return(GetBookFromDataReader(reader).ElementAt(0));
}
}
return(null);
}
}
public static bool CanReadBookNow(string username, string isbn)
{
string sql = "select due_date from Reservation where username=@username and isbn=@isbn";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@isbn", isbn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
DateTime dueDate = (DateTime)reader["due_date"];
if (dueDate.CompareTo(DateTime.Now) >= 0)
{
return(true);
}
}
}
return(false);
}
public static int GetTotalBookLength(int bookCopyId)
{
int length = 0;
Console.WriteLine("copy id: " + bookCopyId);
string sql = "select datalength(content) as [count] from BookCopy where book_id = @id";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@id", bookCopyId);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
length = (int)reader["count"];
}
reader.Dispose();
}
return(length);
}
public static void IncreaseBookRentedCount(string isbn)
{
string sql = "select count(*) as c from BookRentedCount where isbn=@isbn";
using (SqlCommand cmd = new SqlCommand(sql, InitConnection.GetInstance().GetConnection()))
{
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@isbn", isbn);
int count = 0;
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null && reader.Read())
{
count = reader.GetInt32(0);
}
string dml = "";
if (count != 0)
{
dml =
"update BookRentedCount set book_rented_count = @count, date=@date where isbn=@isbn";
}
else
{
dml = "insert into BookRentedCount(isbn, book_rented_count, date) values (@isbn, @count, @date)";
}
using (SqlCommand command = new SqlCommand(dml, InitConnection.GetInstance().GetConnection()))
{
command.Connection.Open();
command.Parameters.AddWithValue("@isbn", isbn);
command.Parameters.AddWithValue("@count", count + 1);
command.Parameters.AddWithValue("@date", DateTime.Now);
int rows = command.ExecuteNonQuery();
Console.WriteLine("rows: " + rows);
}
}
}
static BookDao()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
static ReviewDAO()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
static ReservationDAO()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
static BorrowerDAO()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
static PaymentDAO()
{
GenericConnection = InitConnection.GetInstance().GetConnection();
}
