public List <JoinBorrow> Find(string text)
{
string query = "select tblBorrow.ID, tblBook.Title, tblBook.Author, tblBook.Publisher, tblBook.PublishedDate, tblAccount.FullName, tblAccount.Address, tblAccount.PhoneNumber, tblAccount.Opened, tblAccount.Level, tblBorrow.Borrowed, tblBorrow.Returned, tblBorrow.Fine from ( tblBorrow inner join tblBook on tblBorrow.BookId = tblBook.ID) inner join tblAccount on tblBorrow.AccountId = tblAccount.ID where tblBorrow.ID like @inputQuery or tblBook.Title like @inputQuery or tblBook.Publisher like @inputQuery or tblBook.PublishedDate like @inputQuery or tblAccount.FullName like @inputQuery or tblAccount.Address like @inputQuery or tblAccount.PhoneNumber like @inputQuery or tblAccount.Opened like @inputQuery or tblAccount.Level like @inputQuery or tblBorrow.Borrowed like @inputQuery or tblBorrow.Returned like @inputQuery or tblBorrow.Fine like @inputQuery ;";
Queue <object> parameters = new Queue <object>();
parameters.Enqueue(new MySqlParameter("@Query", "%" + text + "%"));
DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters);
List <JoinBorrow> resultAccount = new List <JoinBorrow>();
foreach (DataRow dataRow in result.Tables[0].Rows)
{
int id = Convert.ToInt32(dataRow[0].ToString());
string title = dataRow[1].ToString();
string author = dataRow[2].ToString();
string publisher = dataRow[3].ToString();
DateTime published = DateTime.Parse(dataRow[4].ToString());
string fullname = dataRow[5].ToString();
string address = dataRow[6].ToString();
string phone = dataRow[7].ToString();
DateTime opened = DateTime.Parse(dataRow[8].ToString());
AccountLevelEnums level = (AccountLevelEnums)Convert.ToInt32(dataRow[9].ToString());
DateTime borrowed = DateTime.Parse(dataRow[10].ToString());
DateTime returned = string.IsNullOrEmpty(dataRow[12].ToString()) ? DateTime.MinValue : Convert.ToDateTime(dataRow[11].ToString());
decimal fine = string.IsNullOrEmpty(dataRow[12].ToString()) ? 0 : Convert.ToDecimal(dataRow[12].ToString());
resultAccount.Add(new JoinBorrow(
id, title, author, publisher, published, fullname, address, phone, opened, level, borrowed, returned, fine));
}
return(resultAccount);
}
public JoinBorrow Read(int id)
{
if (id <= 0)
{
throw new NotFiniteNumberException();
}
Queue <object> parameters = new Queue <object>();
parameters.Enqueue(new MySqlParameter("@ID", id));
string query = "select tblBorrow.ID, tblBook.Title, tblBook.Author, tblBook.Publisher, tblBook.PublishedDate, tblAccount.FullName, tblAccount.Address, tblAccount.PhoneNumber, tblAccount.Opened, tblAccount.Level, tblBorrow.Borrowed, tblBorrow.Returned, tblBorrow.Fine from ( tblBorrow inner join tblBook on tblBorrow.BookId = tblBook.ID) inner join tblAccount on tblBorrow.AccountId = tblAccount.ID where tblBorrow.ID = @ID;";
DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters);
foreach (DataRow dataRow in result.Tables[0].Rows)
{
int ida = Convert.ToInt32(dataRow[0].ToString());
string title = dataRow[1].ToString();
string author = dataRow[2].ToString();
string publisher = dataRow[3].ToString();
DateTime published = DateTime.Parse(dataRow[4].ToString());
string fullname = dataRow[5].ToString();
string address = dataRow[6].ToString();
string phone = dataRow[7].ToString();
DateTime opened = DateTime.Parse(dataRow[8].ToString());
AccountLevelEnums level = (AccountLevelEnums)Convert.ToInt32(dataRow[9].ToString());
DateTime borrowed = DateTime.Parse(dataRow[10].ToString());
DateTime returned = string.IsNullOrEmpty(dataRow[12].ToString()) ? DateTime.MinValue : Convert.ToDateTime(dataRow[11].ToString());
decimal fine = string.IsNullOrEmpty(dataRow[12].ToString()) ? 0 : Convert.ToDecimal(dataRow[12].ToString());
return(new JoinBorrow(
ida, title, author, publisher, published, fullname, address, phone, opened, level, borrowed, returned, fine));
}
return(new JoinBorrow());
}
public Account(int id, string fullName, string address, string phoneNumber, DateTime opened, AccountLevelEnums accountLevel, string username, string password, string salt)
{
ID = id;
FullName = fullName.Trim();
Address = address.Trim();
PhoneNumber = phoneNumber.Trim();
Opened = opened;
AccountLevel = accountLevel;
Username = username;
Password = password;
Salt = salt;
}
public bool SignIn(string fullname, string address, string phone, AccountLevelEnums level, string username, string password)
{
bool isValid = !IsStringEmpty(fullname, username, password) && password.Length >= 8 && level != 0 && (account.AccountLevel >= level || level == AccountLevelEnums.Patron);
if (!isValid)
{
return(false);
}
if (AccountMehod.CountUsername(username) != 0)
{
return(false);
}
AccountMehod.Add(new Account(fullname, address, phone, DateTime.Today, level, username, password));
return(true);
}
public JoinBorrow(int id, string title, string author, string publisher, DateTime publishedDate, string fullName, string address, string phone, DateTime opened, AccountLevelEnums level, DateTime borrowed, DateTime returned, decimal fine)
{
ID = id;
Title = title;
Author = author;
Publisher = publisher;
PublishedDate = publishedDate;
FullName = fullName;
Address = address;
PhoneNumber = phone;
Opened = opened;
AccountLevel = level;
Borrowed = borrowed;
Returned = returned;
Fine = fine;
}
private void btnSignIn_Click(object sender, EventArgs e)
{
var libraryApp = LibraryProxy.GetInstance((DBFactoryEnums)cboDbOption.SelectedIndex + 1);
string address = txtAddress.Text.Trim();
string phone = txtPhone.Text.Trim();
AccountLevelEnums level = (AccountLevelEnums)cboLevel.SelectedIndex + 1;
string fullname = txtFullname.Text.Trim();
string username = txtUsername.Text.Trim();
string password = txtPassword.Text;
if (libraryApp.SignIn(fullname, address, phone, level, username, password))
{
MessageBox.Show("Sign In Success");
txtAddress.Text = "";
txtFullname.Text = "";
txtPassword.Text = "";
txtPhone.Text = "";
txtUsername.Text = "";
}
else
{
MessageBox.Show("One or more field haven't been filled correctly");
}
}
public Account(int id, string fullName, string address, string phoneNumber, DateTime opened, AccountLevelEnums accountLevel) : this(id, fullName, address, phoneNumber, opened, accountLevel, "", "", "")
{
}
public Account(string fullName, string address, string phoneNumber, DateTime opened, AccountLevelEnums accountLevel, string username, string password) : this(0, fullName, address, phoneNumber, opened, accountLevel, username, password, "")
{
}
