private void btnDelete_Click(object sender, EventArgs e)
{
if (dgvReader.SelectedCells.Count > 0)
{
int selectedrowindex = dgvReader.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dgvReader.Rows[selectedrowindex];
string id = selectedRow.Cells["clmnReaderId"].Value.ToString();
DialogResult result = MessageBox.Show("Do you want to delete reader: " + selectedRow.Cells["clmnReaderName"].Value + "?", "Warning", MessageBoxButtons.OKCancel);
switch (result)
{
case DialogResult.Cancel:
break;
case DialogResult.OK:
ReaderBLL readerBLL = new ReaderBLL(selectedRow.Cells["clmnReaderName"].Value.ToString(), Convert.ToInt64(selectedRow.Cells["clmnReaderId"].Value.ToString()), selectedRow.Cells["clmnAddress"].Value.ToString(), selectedRow.Cells["clmnPhone"].Value.ToString(), selectedRow.Cells["clmnEmail"].Value.ToString(), Convert.ToDateTime(selectedRow.Cells["clmnDateallocated"].Value), Convert.ToDateTime(selectedRow.Cells["clmnDateend"].Value), Convert.ToDateTime(selectedRow.Cells["clmnDateallocated"].Value), Convert.ToBoolean(selectedRow.Cells["clmnStaff"].Value), Convert.ToInt32(selectedRow.Cells["clmnGraduation"].Value));
if (ReaderDAL.getReaderItem(readerBLL) == null)
{
MessageBox.Show("Can't delete! this Reader still not to give back of book");
break;
}
else
{
ReaderDAL.deleteReader(readerBLL);
MessageBox.Show("Delete complete!", "Success");
this.LoadDataToDataGridView();
break;
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (dgvReader.SelectedCells.Count > 0)
{
int selectedrowindex = dgvReader.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dgvReader.Rows[selectedrowindex];
ReaderBLL readerBLL = new ReaderBLL(this.txtDocgia.Text, Convert.ToInt64(this.txtMadocgia.Text), this.txtDc.Text, this.txtPhone.Text, this.txtEmail.Text, Convert.ToDateTime(this.txtNgaycap.Text), Convert.ToDateTime(this.txtNgahet.Text), Convert.ToDateTime(this.txtBirthday.Text), Convert.ToBoolean(this.cboStaff.Text), Convert.ToInt32(this.cboGranduationyear.Text));
//Int64 readerId = Int64.Parse(this.txtMadocgia.Text);
if (this.txtDocgia.Text == "" || this.txtPhone.Text == "" || this.txtEmail.Text == "" || this.txtDc.Text == "" || this.txtNgaycap.Text == "" || this.txtNgahet.Text == "" || this.txtBirthday.Text == "")
{
MessageBox.Show("You mustn't empty any fields", "Notice");
return;
}
if (!IsNumber(this.cboGranduationyear.Text))
{
MessageBox.Show("Both of \"Reader Code\" and \"Granduation year\" must be the number format!");
return;
}
if (!IsAllAlphabetic(this.txtDocgia.Text))
{
MessageBox.Show("The \"Reader Name\" must be the character format!");
return;
}
if (!IsNumber(this.txtPhone.Text))
{
MessageBox.Show("the \"Phone Number\" mustn't have the letters!");
return;
}
ReaderDAL.updateReader(readerBLL); //MessageBox.Show(readerBLL.Code.ToString());
MessageBox.Show("Update success!", "Success");
this.LoadDataToDataGridView();
}
}
public static void deleteReader(ReaderBLL readerBLL)
{
String sql = "SELECT * FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia";
DataTable dt = TypeOfBookDAL._condb.getDataTable(sql);
int dem = 0;
foreach (DataRow row in dt.Rows)
{
dem++;
if (Int64.Parse(row["madocgia"].ToString()) == readerBLL.Code)
{
/*String sql_01 = "DELETE FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia INNER JOIN [sachmuon] ON sachmuon.maphieumuon = phieumuon.maphieumuon INNER JOIN [phieutra] ON phieutra.maphieutra = sachmuon.maphieutra INNER JOIN [sachtra] ON sachtra.maphieutra = phieutra.maphieutra where docgia.madocgia = " + readerBLL.Code;
DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);*/
String sql_01 = "DELETE sachmuon FROM [sachmuon] INNER JOIN [phieumuon] ON phieumuon.maphieumuon = sachmuon.maphieumuon where phieumuon.madocgia =" + readerBLL.Code;
DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);
String sql_03 = "DELETE sachtra FROM [sachtra] INNER JOIN [phieutra] ON phieutra.maphieutra = sachtra.maphieutra where phieutra.madocgia =" + readerBLL.Code;
DataTable dt_03 = TypeOfBookDAL._condb.getDataTable(sql_03);
String sql_04 = "DELETE FROM [phieutra] where phieutra.madocgia =" + readerBLL.Code;
DataTable dt_04 = TypeOfBookDAL._condb.getDataTable(sql_04);
String sql_05 = "DELETE FROM [phieumuon] where phieumuon.madocgia =" + readerBLL.Code;
DataTable dt_05 = TypeOfBookDAL._condb.getDataTable(sql_05);
String sql_06 = "DELETE FROM [docgia] WHERE madocgia=" + readerBLL.Code;
DataTable dt_06 = TypeOfBookDAL._condb.getDataTable(sql_06);
//String sql_01 = "DELETE FROM [phieumuon] where phieumuon.madocgia =" + readerBLL.Code;
//DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);
//String sql_03 = "DELETE FROM [phieumuon] where phieumuon.madocgia =" + readerBLL.Code;
//DataTable dt_03 = TypeOfBookDAL._condb.getDataTable(sql_01);
}
else if (dem == dt.Rows.Count && Int64.Parse(row["madocgia"].ToString()) != readerBLL.Code)
{
String sql_02 = "DELETE FROM [docgia] WHERE madocgia=" + readerBLL.Code;
DataTable dt_02 = TypeOfBookDAL._condb.getDataTable(sql_02);
}
}
}
public bool checkBookBorrowMax(ReaderBLL readerBLL)
{
if (CertificateDAL.getCertificateByReaderId(readerBLL.Code) !=null && CertificateDAL.getCertificateByReaderId(readerBLL.Code).Count >= 3)
{
return false;
}
return true;
}
public int getBookQuantityCanBorrow(ReaderBLL readerBLL)
{
if (CertificateDAL.getCertificateByReaderId(readerBLL.Code) != null)
{
return (3 - CertificateDAL.getCertificateByReaderId(readerBLL.Code).Count);
}
return 3;
}
private void btnAdd_Click(object sender, EventArgs e)
{
ReaderBLL readerBLL = new ReaderBLL();
readerBLL.Name = this.txtDocgia.Text;
readerBLL.Code = Convert.ToInt64(this.txtMadocgia.Text);
readerBLL.Phone = this.txtPhone.Text;
readerBLL.Email = this.txtEmail.Text;
readerBLL.Address = this.txtDc.Text;
readerBLL.Allocateddate = Convert.ToDateTime(this.txtNgaycap.Text);
readerBLL.Enddate = Convert.ToDateTime(this.txtNgahet.Text);
readerBLL.Birthday = Convert.ToDateTime(this.txtBirthday.Text);
readerBLL.IsStaff = Convert.ToBoolean(this.cboStaff.Text);
readerBLL.Granduation = Convert.ToInt32(this.cboGranduationyear.Text);
if (readerBLL.Name == "" || readerBLL.Code == null || readerBLL.Phone == "" || readerBLL.Email == "" || readerBLL.Address == "" || readerBLL.Allocateddate == null || readerBLL.Enddate == null || readerBLL.Birthday == null)
{
MessageBox.Show("You mustn't empty any fields", "Notice");
return;
}
if (!IsNumber(readerBLL.Code.ToString()) || !IsNumber(readerBLL.Granduation.ToString()))
{
MessageBox.Show("Both of \"Reader Code\" and \"Granduation year\" must be the number format!");
return;
}
if (!IsAllAlphabetic(readerBLL.Name))
{
MessageBox.Show("The \"Reader Name\" must be the character format!");
return;
}
if (!IsNumber(readerBLL.Phone))
{
MessageBox.Show("the \"Phone Number\" mustn't have the letters!");
return;
}
/*if(ReaderDAL.getReaderItem(readerBLL) != null)
* {
* MessageBox.Show("The \"Reader Code\" Existed!");
* return;
* }*/
List <ReaderBLL> readerList = new List <ReaderBLL>();
readerList = ReaderDAL.getReaderList();
foreach (ReaderBLL row in readerList)
{
//MessageBox.Show(row.IsStaff.ToString());
if (readerBLL.Code == row.Code)
{
MessageBox.Show("The \"Reader Code\" Existed!");
return;
}
}
//isExist("123");
ReaderDAL.addReader(readerBLL);
MessageBox.Show("Add success!", "Success");
this.LoadDataToDataGridView();
}
protected void AddReader_Click(object sender, EventArgs e)
{
if (ProcessGUI.IsNumeric(this.txtReaderId.Text))
{
Int64 readerId = Int64.Parse(this.txtReaderId.Text);
ReaderBLL readerBLL = new ReaderBLL();
readerBLL = ReaderDAL.getReaderByReaderId(readerId);
this._readerBLL = readerBLL;
if (readerBLL != null)
{
this.lblReaderName.Text = readerBLL.Name;
this.lblReaderId.Text = readerBLL.Code.ToString();
this.lblEmail.Text = readerBLL.Email;
this.lblAddress.Text = readerBLL.Address;
this.lblDayOfBirth.Text = readerBLL.Birthday.ToShortDateString();
this.lblPhone.Text = readerBLL.Phone;
this.lblQuantity.Text = "0";
int result = (DateTime.Compare(DateTime.Now, readerBLL.Enddate));
if (result > 0)
{
string script = "alert(\"This reader has expired!\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
this.lblStatus.Text = "Expired";
}
else
{
BorrowBookBLL borrowBookBLL = new BorrowBookBLL();
if (borrowBookBLL.checkBookBorrowMax(readerBLL))
{
this.lblStatus.Text = "Good";
Session["readerName"]= readerBLL.Name;
Session["readerId"] = readerBLL.Code;
Session["quantity"] = borrowBookBLL.getBookQuantityCanBorrow(readerBLL);
this.lblQuantity.Text = borrowBookBLL.getBookQuantityCanBorrow(readerBLL).ToString();
}
else
{
string script = "alert(\"Reader has borrowed maximum books!\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
this.lblStatus.Text = "Maximum";
}
}
}
else
{
string script = "alert(\"Readers do not exist!\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
}
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
ReaderBLL readerBLL = new ReaderBLL();
readerBLL.Name = this.txtDocgia.Text;
readerBLL.Code = Convert.ToInt64(this.txtMadocgia.Text);
readerBLL.Phone = this.txtPhone.Text;
readerBLL.Email = this.txtEmail.Text;
readerBLL.Address = this.txtDc.Text;
readerBLL.Allocateddate = Convert.ToDateTime(this.txtNgaycap.Text);
readerBLL.Enddate = Convert.ToDateTime(this.txtNgahet.Text);
readerBLL.Birthday = Convert.ToDateTime(this.txtBirthday.Text);
readerBLL.IsStaff = Convert.ToBoolean(this.cboStaff.Text);
readerBLL.Granduation = Convert.ToInt32(this.cboGranduationyear.Text);
if (readerBLL.Name == ""||readerBLL.Code==null || readerBLL.Phone == "" || readerBLL.Email == "" || readerBLL.Address == "" || readerBLL.Allocateddate == null || readerBLL.Enddate == null || readerBLL.Birthday == null)
{
MessageBox.Show("You mustn't empty any fields", "Notice");
return;
}
if(!IsNumber(readerBLL.Code.ToString())|| !IsNumber(readerBLL.Granduation.ToString()))
{
MessageBox.Show("Both of \"Reader Code\" and \"Granduation year\" must be the number format!");
return;
}
if (!IsAllAlphabetic(readerBLL.Name))
{
MessageBox.Show("The \"Reader Name\" must be the character format!");
return;
}
if(!IsNumber(readerBLL.Phone))
{
MessageBox.Show("the \"Phone Number\" mustn't have the letters!");
return;
}
/*if(ReaderDAL.getReaderItem(readerBLL) != null)
{
MessageBox.Show("The \"Reader Code\" Existed!");
return;
}*/
List<ReaderBLL> readerList = new List<ReaderBLL>();
readerList = ReaderDAL.getReaderList();
foreach (ReaderBLL row in readerList)
{
//MessageBox.Show(row.IsStaff.ToString());
if(readerBLL.Code == row.Code)
{
MessageBox.Show("The \"Reader Code\" Existed!");
return;
}
}
//isExist("123");
ReaderDAL.addReader(readerBLL);
MessageBox.Show("Add success!", "Success");
this.LoadDataToDataGridView();
}
protected void AddReader_Click(object sender, EventArgs e)
{
if (ProcessGUI.IsNumeric(this.txtReaderId.Text))
{
Int64 readerId = Int64.Parse(this.txtReaderId.Text);
ReaderBLL readerBLL = new ReaderBLL();
readerBLL = ReaderDAL.getReaderByReaderId(readerId);
this._readerBLL = readerBLL;
if (readerBLL != null)
{
this.lblReaderName.Text = readerBLL.Name;
this.lblReaderId.Text = readerBLL.Code.ToString();
this.lblEmail.Text = readerBLL.Email;
this.lblAddress.Text = readerBLL.Address;
this.lblDayOfBirth.Text = readerBLL.Birthday.ToShortDateString();
this.lblPhone.Text = readerBLL.Phone;
this.lblQuantity.Text = "0";
int result = (DateTime.Compare(DateTime.Now, readerBLL.Enddate));
if (result > 0)
{
string script = "alert(\"This reader has expired!\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
this.lblStatus.Text = "Expired";
}
else
{
BorrowBookBLL borrowBookBLL = new BorrowBookBLL();
if (borrowBookBLL.checkBookBorrowMax(readerBLL))
{
this.lblStatus.Text = "Good";
Session["readerName"] = readerBLL.Name;
Session["readerId"] = readerBLL.Code;
Session["quantity"] = borrowBookBLL.getBookQuantityCanBorrow(readerBLL);
this.lblQuantity.Text = borrowBookBLL.getBookQuantityCanBorrow(readerBLL).ToString();
}
else
{
string script = "alert(\"Reader has borrowed maximum books!\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
this.lblStatus.Text = "Maximum";
}
}
}
else
{
string script = "alert(\"Readers do not exist!\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
}
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
string key = this.txtSearch.Text;
if (key == "".Trim())
{
MessageBox.Show("Please enter keyword!", "Notice");
return;
}
string catalog = "";
//MessageBox.Show(this.cboSearch.SelectedItem.ToString());
if (this.cboTimtheo.SelectedItem.ToString() == "Reader Name")
{
catalog += "docgia.tendocgia";
//MessageBox.Show(catalog);
}
else if (this.cboTimtheo.SelectedItem.ToString() == "Reader Id")
{
catalog += "docgia.madocgia";
//MessageBox.Show(catalog);
}
else if (this.cboTimtheo.SelectedItem.ToString() == "Phone Number")
{
catalog += "docgia.sdtdocgia";
//MessageBox.Show(catalog);
}
else if (this.cboTimtheo.SelectedItem.ToString() == "E-mail")
{
catalog += "docgia.emaildocgia";
//MessageBox.Show(catalog);
}
ReaderBLL readerBLL = new ReaderBLL();
List <ReaderBLL> readerArr = new List <ReaderBLL>();
readerArr = ReaderDAL.search(key, catalog);
this.dgvReader.Rows.Clear();
if (readerArr != null)
{
//MessageBox.Show("ok");
foreach (ReaderBLL row in readerArr)
{
this.dgvReader.Rows.Add(row.Code, row.Name, row.Address, row.Phone, row.Email, row.Allocateddate.ToShortDateString(), row.Enddate.ToShortDateString(), row.Birthday.ToShortDateString(), row.IsStaff, row.Granduation);
}
}
else
{
MessageBox.Show("Sorry! Can't find this reader");
return;
}
this.dgvReader.SelectionChanged += new EventHandler(dgvReader_SelectionChanged);
this.GetSelectedValue();
}
public static List <ReaderBLL> getReaderList()
{
string sql = "SELECT * FROM [docgia]";
DataTable dt = ReaderDAL._condb.getDataTable(sql);
if (dt.Rows.Count > 0)
{
List <ReaderBLL> readerList = new List <ReaderBLL>();
foreach (DataRow row in dt.Rows)
{
ReaderBLL readerBLL = new ReaderBLL(row["tendocgia"].ToString(), Convert.ToInt64(row["madocgia"]), row["diachidocgia"].ToString(), row["sdtdocgia"].ToString(), row["emaildocgia"].ToString(), DateTime.Parse(row["ngaycap"].ToString()), DateTime.Parse(row["ngayhethan"].ToString()), DateTime.Parse(row["ngaysinhdocgia"].ToString()), bool.Parse(row["lacbcnv"].ToString()), Int32.Parse(row["namtotnghiep"].ToString()));
readerList.Add(readerBLL);
}
return(readerList);
}
return(null);
}
public static ReaderBLL getReaderItem(ReaderBLL readerBLL)
{
String sql = "SELECT * FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia";
DataTable dt = TypeOfBookDAL._condb.getDataTable(sql);
int dem = 0;
foreach (DataRow row in dt.Rows)
{
dem++;
if (Int64.Parse(row["madocgia"].ToString()) == readerBLL.Code)
{
break;
}
else if (dem == dt.Rows.Count)
{
String sql_01 = "SELECT * FROM [docgia] where docgia.madocgia =" + readerBLL.Code;
DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);
if (dt_01.Rows.Count > 0)
{
DataRow row_01 = dt_01.Rows[0];
return(new ReaderBLL(row_01["tendocgia"].ToString(), Convert.ToInt64(row_01["madocgia"].ToString()), row_01["diachidocgia"].ToString(), row_01["sdtdocgia"].ToString(), row_01["emaildocgia"].ToString(), DateTime.Parse(row_01["ngaycap"].ToString()), DateTime.Parse(row_01["ngayhethan"].ToString()), DateTime.Parse(row_01["ngaysinhdocgia"].ToString()), bool.Parse(row_01["lacbcnv"].ToString()), Int32.Parse(row_01["namtotnghiep"].ToString())));
}
return(null);
}
}
dem = 0;
String sql_02 = "SELECT * FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia where docgia.madocgia =" + readerBLL.Code;
DataTable dt_02 = TypeOfBookDAL._condb.getDataTable(sql_02);
foreach (DataRow row_02 in dt_02.Rows)
{
dem++;
if (Int32.Parse(row_02["idtinhtrang"].ToString()) != 4)
{
return(null);
}
else if (Int32.Parse(row_02["idtinhtrang"].ToString()) == 4 && dem == dt_02.Rows.Count)
{
return(new ReaderBLL(row_02["tendocgia"].ToString(), Convert.ToInt64(row_02["madocgia"].ToString()), row_02["diachidocgia"].ToString(), row_02["sdtdocgia"].ToString(), row_02["emaildocgia"].ToString(), DateTime.Parse(row_02["ngaycap"].ToString()), DateTime.Parse(row_02["ngayhethan"].ToString()), DateTime.Parse(row_02["ngaysinhdocgia"].ToString()), bool.Parse(row_02["lacbcnv"].ToString()), Int32.Parse(row_02["namtotnghiep"].ToString())));
}
}
return(null);
}
public static List <ReaderBLL> search(string value, string catalog)
{
string sql = "SELECT * FROM [docgia] where " + catalog + " LIKE '%" + value + "%'";
DataTable dt = ReaderDAL._condb.getDataTable(sql);
List <ReaderBLL> readerBLLList = new List <ReaderBLL>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
ReaderBLL readerBLL = new ReaderBLL(row["tendocgia"].ToString(), Convert.ToInt64(row["madocgia"]), row["diachidocgia"].ToString(), row["sdtdocgia"].ToString(), row["emaildocgia"].ToString(), DateTime.Parse(row["ngaycap"].ToString()), DateTime.Parse(row["ngayhethan"].ToString()), DateTime.Parse(row["ngaysinhdocgia"].ToString()), bool.Parse(row["lacbcnv"].ToString()), Int32.Parse(row["namtotnghiep"].ToString()));
readerBLLList.Add(readerBLL);
}
return(readerBLLList);
}
else
{
return(null);
}
}
public static ReaderBLL getReaderItem(ReaderBLL readerBLL)
{
String sql = "SELECT * FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia";
DataTable dt = TypeOfBookDAL._condb.getDataTable(sql);
int dem = 0;
foreach (DataRow row in dt.Rows)
{
dem++;
if(Int64.Parse(row["madocgia"].ToString())==readerBLL.Code)
{
break;
}
else if(dem== dt.Rows.Count)
{
String sql_01 = "SELECT * FROM [docgia] where docgia.madocgia =" + readerBLL.Code;
DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);
if (dt_01.Rows.Count > 0)
{
DataRow row_01 = dt_01.Rows[0];
return new ReaderBLL(row_01["tendocgia"].ToString(), Convert.ToInt64(row_01["madocgia"].ToString()), row_01["diachidocgia"].ToString(), row_01["sdtdocgia"].ToString(), row_01["emaildocgia"].ToString(), DateTime.Parse(row_01["ngaycap"].ToString()), DateTime.Parse(row_01["ngayhethan"].ToString()), DateTime.Parse(row_01["ngaysinhdocgia"].ToString()), bool.Parse(row_01["lacbcnv"].ToString()), Int32.Parse(row_01["namtotnghiep"].ToString()));
}
return null;
}
}
dem = 0;
String sql_02 = "SELECT * FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia where docgia.madocgia =" + readerBLL.Code;
DataTable dt_02 = TypeOfBookDAL._condb.getDataTable(sql_02);
foreach (DataRow row_02 in dt_02.Rows)
{
dem++;
if (Int32.Parse(row_02["idtinhtrang"].ToString()) != 4)
{
return null;
}
else if(Int32.Parse(row_02["idtinhtrang"].ToString()) == 4 && dem == dt_02.Rows.Count)
{
return new ReaderBLL(row_02["tendocgia"].ToString(), Convert.ToInt64(row_02["madocgia"].ToString()), row_02["diachidocgia"].ToString(), row_02["sdtdocgia"].ToString(), row_02["emaildocgia"].ToString(), DateTime.Parse(row_02["ngaycap"].ToString()), DateTime.Parse(row_02["ngayhethan"].ToString()), DateTime.Parse(row_02["ngaysinhdocgia"].ToString()), bool.Parse(row_02["lacbcnv"].ToString()), Int32.Parse(row_02["namtotnghiep"].ToString()));
}
}
return null;
}
public static void deleteReader(ReaderBLL readerBLL)
{
String sql = "SELECT * FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia";
DataTable dt = TypeOfBookDAL._condb.getDataTable(sql);
int dem = 0;
foreach (DataRow row in dt.Rows)
{
dem++;
if (Int64.Parse(row["madocgia"].ToString()) == readerBLL.Code)
{
/*String sql_01 = "DELETE FROM [docgia] INNER JOIN [phieumuon] ON docgia.madocgia = phieumuon.madocgia INNER JOIN [sachmuon] ON sachmuon.maphieumuon = phieumuon.maphieumuon INNER JOIN [phieutra] ON phieutra.maphieutra = sachmuon.maphieutra INNER JOIN [sachtra] ON sachtra.maphieutra = phieutra.maphieutra where docgia.madocgia = " + readerBLL.Code;
* DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);*/
String sql_01 = "DELETE sachmuon FROM [sachmuon] INNER JOIN [phieumuon] ON phieumuon.maphieumuon = sachmuon.maphieumuon where phieumuon.madocgia =" + readerBLL.Code;
DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);
String sql_03 = "DELETE sachtra FROM [sachtra] INNER JOIN [phieutra] ON phieutra.maphieutra = sachtra.maphieutra where phieutra.madocgia =" + readerBLL.Code;
DataTable dt_03 = TypeOfBookDAL._condb.getDataTable(sql_03);
String sql_04 = "DELETE FROM [phieutra] where phieutra.madocgia =" + readerBLL.Code;
DataTable dt_04 = TypeOfBookDAL._condb.getDataTable(sql_04);
String sql_05 = "DELETE FROM [phieumuon] where phieumuon.madocgia =" + readerBLL.Code;
DataTable dt_05 = TypeOfBookDAL._condb.getDataTable(sql_05);
String sql_06 = "DELETE FROM [docgia] WHERE madocgia=" + readerBLL.Code;
DataTable dt_06 = TypeOfBookDAL._condb.getDataTable(sql_06);
//String sql_01 = "DELETE FROM [phieumuon] where phieumuon.madocgia =" + readerBLL.Code;
//DataTable dt_01 = TypeOfBookDAL._condb.getDataTable(sql_01);
//String sql_03 = "DELETE FROM [phieumuon] where phieumuon.madocgia =" + readerBLL.Code;
//DataTable dt_03 = TypeOfBookDAL._condb.getDataTable(sql_01);
}
else if (dem == dt.Rows.Count && Int64.Parse(row["madocgia"].ToString()) != readerBLL.Code)
{
String sql_02 = "DELETE FROM [docgia] WHERE madocgia=" + readerBLL.Code;
DataTable dt_02 = TypeOfBookDAL._condb.getDataTable(sql_02);
}
}
}
public static void addReader(ReaderBLL readerBLL)
{
String sql = "INSERT INTO [docgia] (tendocgia, madocgia, diachidocgia, sdtdocgia, emaildocgia, ngaycap, ngayhethan, ngaysinhdocgia, lacbcnv, namtotnghiep) VALUES ( N'" + readerBLL.Name + "', " + readerBLL.Code + ", N'" + readerBLL.Address + "', N'" + readerBLL.Phone + "', N'" + readerBLL.Email + "', '" + readerBLL.Allocateddate + "', '" + readerBLL.Enddate + "', '" + readerBLL.Birthday + "', " + ((readerBLL.IsStaff == true)?1:0) + ", '" + readerBLL.Granduation + "')";
ReaderDAL._condb.ExecuteNonQuery(sql);
}
private void btnSearch_Click(object sender, EventArgs e)
{
string key = this.txtSearch.Text;
if (key == "".Trim())
{
MessageBox.Show("Please enter keyword!", "Notice");
return;
}
string catalog = "";
//MessageBox.Show(this.cboSearch.SelectedItem.ToString());
if (this.cboTimtheo.SelectedItem.ToString() == "Reader Name")
{
catalog += "docgia.tendocgia";
//MessageBox.Show(catalog);
}
else if (this.cboTimtheo.SelectedItem.ToString() == "Reader Id")
{
catalog += "docgia.madocgia";
//MessageBox.Show(catalog);
}
else if (this.cboTimtheo.SelectedItem.ToString() == "Phone Number")
{
catalog += "docgia.sdtdocgia";
//MessageBox.Show(catalog);
}
else if (this.cboTimtheo.SelectedItem.ToString() == "E-mail")
{
catalog += "docgia.emaildocgia";
//MessageBox.Show(catalog);
}
ReaderBLL readerBLL = new ReaderBLL();
List<ReaderBLL> readerArr = new List<ReaderBLL>();
readerArr = ReaderDAL.search(key, catalog);
this.dgvReader.Rows.Clear();
if (readerArr != null)
{
//MessageBox.Show("ok");
foreach (ReaderBLL row in readerArr)
{
this.dgvReader.Rows.Add(row.Code, row.Name, row.Address, row.Phone, row.Email, row.Allocateddate.ToShortDateString(), row.Enddate.ToShortDateString(), row.Birthday.ToShortDateString(), row.IsStaff, row.Granduation);
}
}
else
{
MessageBox.Show("Sorry! Can't find this reader");
return;
}
this.dgvReader.SelectionChanged += new EventHandler(dgvReader_SelectionChanged);
this.GetSelectedValue();
}
private void btnSave_Click(object sender, EventArgs e)
{
if (dgvReader.SelectedCells.Count > 0)
{
int selectedrowindex = dgvReader.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dgvReader.Rows[selectedrowindex];
ReaderBLL readerBLL = new ReaderBLL(this.txtDocgia.Text, Convert.ToInt64(this.txtMadocgia.Text), this.txtDc.Text, this.txtPhone.Text, this.txtEmail.Text, Convert.ToDateTime(this.txtNgaycap.Text), Convert.ToDateTime(this.txtNgahet.Text), Convert.ToDateTime(this.txtBirthday.Text), Convert.ToBoolean(this.cboStaff.Text), Convert.ToInt32(this.cboGranduationyear.Text));
//Int64 readerId = Int64.Parse(this.txtMadocgia.Text);
if (this.txtDocgia.Text == "" || this.txtPhone.Text == "" || this.txtEmail.Text == "" || this.txtDc.Text == "" || this.txtNgaycap.Text == "" || this.txtNgahet.Text == "" || this.txtBirthday.Text == "")
{
MessageBox.Show("You mustn't empty any fields", "Notice");
return;
}
if (!IsNumber(this.cboGranduationyear.Text))
{
MessageBox.Show("Both of \"Reader Code\" and \"Granduation year\" must be the number format!");
return;
}
if (!IsAllAlphabetic(this.txtDocgia.Text))
{
MessageBox.Show("The \"Reader Name\" must be the character format!");
return;
}
if (!IsNumber(this.txtPhone.Text))
{
MessageBox.Show("the \"Phone Number\" mustn't have the letters!");
return;
}
ReaderDAL.updateReader(readerBLL); //MessageBox.Show(readerBLL.Code.ToString());
MessageBox.Show("Update success!", "Success");
this.LoadDataToDataGridView();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (dgvReader.SelectedCells.Count > 0)
{
int selectedrowindex = dgvReader.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dgvReader.Rows[selectedrowindex];
string id = selectedRow.Cells["clmnReaderId"].Value.ToString();
DialogResult result = MessageBox.Show("Do you want to delete reader: " + selectedRow.Cells["clmnReaderName"].Value + "?", "Warning", MessageBoxButtons.OKCancel);
switch (result)
{
case DialogResult.Cancel:
break;
case DialogResult.OK:
ReaderBLL readerBLL = new ReaderBLL(selectedRow.Cells["clmnReaderName"].Value.ToString(), Convert.ToInt64(selectedRow.Cells["clmnReaderId"].Value.ToString()), selectedRow.Cells["clmnAddress"].Value.ToString(), selectedRow.Cells["clmnPhone"].Value.ToString(), selectedRow.Cells["clmnEmail"].Value.ToString(), Convert.ToDateTime(selectedRow.Cells["clmnDateallocated"].Value), Convert.ToDateTime(selectedRow.Cells["clmnDateend"].Value), Convert.ToDateTime(selectedRow.Cells["clmnDateallocated"].Value), Convert.ToBoolean(selectedRow.Cells["clmnStaff"].Value), Convert.ToInt32(selectedRow.Cells["clmnGraduation"].Value));
if (ReaderDAL.getReaderItem(readerBLL) == null)
{
MessageBox.Show("Can't delete! this Reader still not to give back of book");
break;
}
else
{
ReaderDAL.deleteReader(readerBLL);
MessageBox.Show("Delete complete!", "Success");
this.LoadDataToDataGridView();
break;
}
}
}
}
public static void updateReader(ReaderBLL readerBLL)
{
String sql = "UPDATE [docgia] SET tendocgia=N'" + readerBLL.Name + "' , diachidocgia=N'" + readerBLL.Address + "', sdtdocgia=N'" + readerBLL.Phone + "', emaildocgia=N'" + readerBLL.Email + "', ngaycap='" + readerBLL.Allocateddate + "', ngayhethan='" + readerBLL.Enddate + "', ngaysinhdocgia='" + readerBLL.Birthday + "', lacbcnv=" + ((readerBLL.IsStaff == true)?1:0) + ", namtotnghiep='" + readerBLL.Granduation + "' WHERE madocgia=" + readerBLL.Code;
ReaderDAL._condb.ExecuteNonQuery(sql);
}
public static List<ReaderBLL> getReaderList()
{
string sql = "SELECT * FROM [docgia]";
DataTable dt = ReaderDAL._condb.getDataTable(sql);
if (dt.Rows.Count > 0)
{
List<ReaderBLL> readerList = new List<ReaderBLL>();
foreach (DataRow row in dt.Rows)
{
ReaderBLL readerBLL = new ReaderBLL(row["tendocgia"].ToString(), Convert.ToInt64(row["madocgia"]), row["diachidocgia"].ToString(), row["sdtdocgia"].ToString(), row["emaildocgia"].ToString(), DateTime.Parse(row["ngaycap"].ToString()), DateTime.Parse(row["ngayhethan"].ToString()), DateTime.Parse(row["ngaysinhdocgia"].ToString()), bool.Parse(row["lacbcnv"].ToString()), Int32.Parse(row["namtotnghiep"].ToString()));
readerList.Add(readerBLL);
}
return readerList;
}
return null;
}
public static void addReader(ReaderBLL readerBLL)
{
String sql = "INSERT INTO [docgia] (tendocgia, madocgia, diachidocgia, sdtdocgia, emaildocgia, ngaycap, ngayhethan, ngaysinhdocgia, lacbcnv, namtotnghiep) VALUES ( N'" + readerBLL.Name + "', " + readerBLL.Code + ", N'" + readerBLL.Address + "', N'" + readerBLL.Phone + "', N'" + readerBLL.Email + "', '" + readerBLL.Allocateddate + "', '" + readerBLL.Enddate + "', '" + readerBLL.Birthday + "', " + ((readerBLL.IsStaff==true)?1:0) + ", '" + readerBLL.Granduation + "')";
ReaderDAL._condb.ExecuteNonQuery(sql);
}
public static void updateReader(ReaderBLL readerBLL)
{
String sql = "UPDATE [docgia] SET tendocgia=N'" + readerBLL.Name + "' , diachidocgia=N'" + readerBLL.Address + "', sdtdocgia=N'" + readerBLL.Phone + "', emaildocgia=N'" + readerBLL.Email + "', ngaycap='" + readerBLL.Allocateddate + "', ngayhethan='" + readerBLL.Enddate + "', ngaysinhdocgia='" + readerBLL.Birthday + "', lacbcnv=" + ((readerBLL.IsStaff==true)?1:0) + ", namtotnghiep='" + readerBLL.Granduation + "' WHERE madocgia=" + readerBLL.Code;
ReaderDAL._condb.ExecuteNonQuery(sql);
}
public static List<ReaderBLL> search(string value, string catalog)
{
string sql = "SELECT * FROM [docgia] where " + catalog + " LIKE '%" + value + "%'";
DataTable dt = ReaderDAL._condb.getDataTable(sql);
List<ReaderBLL> readerBLLList = new List<ReaderBLL>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
ReaderBLL readerBLL = new ReaderBLL(row["tendocgia"].ToString(), Convert.ToInt64(row["madocgia"]), row["diachidocgia"].ToString(), row["sdtdocgia"].ToString(), row["emaildocgia"].ToString(), DateTime.Parse(row["ngaycap"].ToString()), DateTime.Parse(row["ngayhethan"].ToString()), DateTime.Parse(row["ngaysinhdocgia"].ToString()), bool.Parse(row["lacbcnv"].ToString()), Int32.Parse(row["namtotnghiep"].ToString()));
readerBLLList.Add(readerBLL);
}
return readerBLLList;
}
else
{
return null;
}
}
