public bool add(bookDTO book)
{
string query = string.Empty;
query += "INSERT INTO SACH (MaSach, MaDauSach, NgayNhap, NXB, NamXuatBan, Gia) ";
query += "VALUES (@MaSach, @MaDauSach, @NgayNhap, @NXB, @NamXuatBan, @Gia)";
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;")) //Init connection to host
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@MaSach", book.MaSach);
cmd.Parameters.AddWithValue("@MaDauSach", book.MaDauSach);
cmd.Parameters.AddWithValue("@NgayNhap", book.NgayNhap);
cmd.Parameters.AddWithValue("@NXB", book.NhaXuatBan);
cmd.Parameters.AddWithValue("@NamXuatBan", book.NamXuatBan);
cmd.Parameters.AddWithValue("@Gia", book.TriGia);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
private void btnBorrow_Click(object sender, EventArgs e)
{
QLTVDTO.borrowbook borrowDTO = new QLTVDTO.borrowbook();
bookDTO book = new bookDTO();
ReaderDTO reader = new ReaderDTO();
book.MaSach = tbBookCode.Text;
reader.IdReader = int.Parse(tbReaderCode.Text);
string temp1 = tbReaderCode.Text;
borrowDTO.NgayTraSachThuc = dtpReturnBookDate.Value;
borrowDTO.Idborrowbook = tbReturnBook.Text;
//add into db
bool result = returnBUS.Tra(borrowDTO, book, reader);
if (result == true)
{
MessageBox.Show("Trả thành công");
}
else
{
MessageBox.Show("Trả sách thất bại");
}
}
public bool del(bookDTO book)
{
string query = string.Empty;
query += "DELETE FROM SACH WHERE [MaSach] = @MaSach";
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;")) //Init connection to host
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@MaSach", book.MaSach);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
public bool Return(borrowbook SachMuon, bookDTO book, ReaderDTO reader)
{
string query2 = string.Empty;
query2 += "IF NOT EXISTS(SELECT TOP 1 MaThe FROM THEDOCGIA WHERE MaThe= @MaThe) " + Environment.NewLine;
query2 += "INSERT INTO MUONSACH (MaThe, MaSach) " + Environment.NewLine;
query2 += "VALUES (@MaThe, @MaSach) ";
query2 += "else " + Environment.NewLine;
query2 += "begin " + Environment.NewLine;
query2 += "DECLARE @dk INT " + Environment.NewLine;
query2 += "DECLARE cursorProduct CURSOR FOR " + Environment.NewLine;
query2 += "select DaMuon from SACH where MaSach = @MaSach " + Environment.NewLine;
query2 += "Open cursorProduct FETCH NEXT FROM cursorProduct INTO @dk " + Environment.NewLine;
query2 += "if @dk = 1 " + Environment.NewLine;
query2 += "begin" + Environment.NewLine;
query2 += "update SACH set DaMuon = 0 where MaSach = @MaSach " + Environment.NewLine;
query2 += "UPDATE MUONSACH SET ";
query2 += "NgayTraThucTe = @NgayTraThucTe ";
query2 += "WHERE MaThe=@MaThe and MaSach = @MaSach " + Environment.NewLine;
query2 += "end " + Environment.NewLine;
query2 += "else " + Environment.NewLine;
query2 += "INSERT INTO MUONSACH (MaThe, MaSach, NgayTraThucTe,MaMuonSach) " + Environment.NewLine;
query2 += "VALUES (@MaThe, @MaSach, @NgayTraThucTe,@MaMuonSach) ";
query2 += "end " + Environment.NewLine;
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;")) //Init connection to host
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query2;
cmd.Parameters.AddWithValue("@MaSach", book.MaSach);
cmd.Parameters.AddWithValue("@MaThe", reader.IdReader);
cmd.Parameters.AddWithValue("@MaMuonSach", SachMuon.Idborrowbook);
cmd.Parameters.AddWithValue("@NgayTraThucTe", SachMuon.NgayTraSachThuc);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(false);
}
}
}
return(true);
}
//to select authores from db
public List <bookDTO> selectedBook()
{
string query = string.Empty;
query += "SELECT * ";
query += "FROM SACH";
List <bookDTO> lsBook = new List <bookDTO>();
using (SqlConnection con = new SqlConnection(@"server=" + Dns.GetHostName() + ";Trusted_Connection=yes;database=LIBMANAGEMENT;"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
bookDTO book = new bookDTO();
book.MaSach = reader["MaSach"].ToString();
book.MaDauSach = reader["MaDauSach"].ToString();
book.NgayNhap = reader.GetFieldValue <DateTime>(reader.GetOrdinal("NgayNhap"));
book.NhaXuatBan = reader["NXB"].ToString();
book.NamXuatBan = reader.GetFieldValue <DateTime>(reader.GetOrdinal("NamXuatBan"));
book.TriGia = Decimal.Parse(reader["Gia"].ToString());
book.TinhTrang = reader["DaMuon"].ToString();
lsBook.Add(book);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
}
return(lsBook);
}
private void btnDel_Click(object sender, EventArgs e)
{
DialogResult warning = new DialogResult();
warning = MessageBox.Show("Bạn có chắc chắn muốn xóa sách này?", "Cảnh báo!", MessageBoxButtons.YesNo);
if (warning == DialogResult.Yes)
{
int rowIndex = dgvBooksManage.CurrentCell.RowIndex;
bookDTO = new bookDTO();
bookDTO.MaSach = dgvBooksManage.Rows[rowIndex].Cells[0].Value.ToString();
bookBUS.del(bookDTO);
}
else
{
return;
}
LoadDataInto_DataGridViewOfBook();
}
private void btnBorrow_Click(object sender, EventArgs e)
{
QLTVDTO.borrowbook borrowDTO = new QLTVDTO.borrowbook();
bookDTO book = new bookDTO();
ReaderDTO reader = new ReaderDTO();
DateTimePicker myPicker = new DateTimePicker();
myPicker.Value = DateTime.Now;
int k = myPicker.Value.Month - reader.DateCreateReader.Month;
int SoSachMuon = ReaderBUS.SoSachMuon(reader);
int SoSachMax = ReaderBUS.getMaxofBorrowBook();
reader.IdReader = int.Parse(tbReaderCode.Text);
string temp = tbReaderCode.Text;
if ((k > ReaderBUS.getTimeofReader()) && (SoSachMuon > SoSachMax))
{
MessageBox.Show("Đã Hết hạn mượn Thẻ ");
}
else
{
book.MaSach = tbBookCode.Text;
borrowDTO.NgayMuonSach = DateBorrowdtp.Value;
borrowDTO.Idborrowbook = tbIdBorrowBook.Text;
borrowDTO.NgayTraSach = DateBorrowdtp.Value.AddDays(borrowBUS.SoNgayMuonToiDa());
//add into db
bool result = borrowBUS.add(borrowDTO, book, reader);
if (result == true)
{
MessageBox.Show("Mượn thành công");
}
else
{
MessageBox.Show("Mượn thất bại");
}
}
}
/*End placeholders*/
private void btnAdd_Click(object sender, EventArgs e)
{
//map data from gui
bookDTO addBookDTO = new bookDTO();
addBookBUS = new bookBUS();
DateTimePicker myPicker = new DateTimePicker();
myPicker.Value = DateTime.Now;
int k = myPicker.Value.Year - dtpPublishYear.Value.Year;
if (k > addBookBUS.getMaxOfYearExp())
{
MessageBox.Show("Năm xuất bản không phù hợp");
}
else
{
addBookDTO.MaSach = tbBookCode.Text;
addBookDTO.MaDauSach = cbTitleName.SelectedValue.ToString();
addBookDTO.NgayNhap = dtpDateIn.Value;
addBookDTO.NamXuatBan = dtpPublishYear.Value;
addBookDTO.NhaXuatBan = tbPublisher.Text;
addBookDTO.TriGia = nudValue.Value;
addBookDTO.TinhTrang = "0";
//add into db
bool result = addBookBUS.add(addBookDTO);
if (result == true)
{
MessageBox.Show("Thêm sách thành công");
}
else
{
MessageBox.Show("Thêm sách thất bại");
}
}
}
private void btnMod_Click(object sender, EventArgs e)
{
DialogResult warning = new DialogResult();
warning = MessageBox.Show("Bạn có chắc chắn muốn sửa sách này?", "Cảnh báo!", MessageBoxButtons.YesNo);
if (warning == DialogResult.Yes)
{
int rowIndex = dgvBooksManage.CurrentCell.RowIndex;
bookDTO = new bookDTO();
bookDTO.MaSach = dgvBooksManage.Rows[rowIndex].Cells[0].Value.ToString();
bookDTO.MaDauSach = dgvBooksManage.Rows[rowIndex].Cells[1].Value.ToString();
bookDTO.NgayNhap = Convert.ToDateTime(dgvBooksManage.Rows[rowIndex].Cells[2].Value);
bookDTO.NhaXuatBan = dgvBooksManage.Rows[rowIndex].Cells[3].Value.ToString();
bookDTO.NamXuatBan = Convert.ToDateTime(dgvBooksManage.Rows[rowIndex].Cells[4].Value);
bookDTO.TriGia = Decimal.Parse(dgvBooksManage.Rows[rowIndex].Cells[5].Value.ToString());
bookDTO.TinhTrang = dgvBooksManage.Rows[rowIndex].Cells[6].Value.ToString();
// to display form to modify author
frmModBook frmModBook = new frmModBook();
LoadTitlesInto_ComboBox(frmModBook);
frmModBook.tbBookCode.ReadOnly = true;
frmModBook.tbBookCode.Text = bookDTO.MaSach;
frmModBook.cbTitleName.SelectedValue = bookDTO.MaDauSach;
frmModBook.dtpDateIn.Value = bookDTO.NgayNhap;
frmModBook.dtpPublishYear.Value = bookDTO.NamXuatBan;
frmModBook.tbPublisher.Text = bookDTO.NhaXuatBan;
frmModBook.nudValue.Value = bookDTO.TriGia;
frmModBook.ShowDialog();
}
else
{
return;
}
LoadDataInto_DataGridViewOfBook();
}
/*End placeholders*/
private void btnAdd_Click(object sender, EventArgs e)
{
//map data from gui
bookDTO addBookDTO = new bookDTO();
addBookDTO.MaSach = tbBookCode.Text;
addBookDTO.MaDauSach = cbTitleName.SelectedValue.ToString();
addBookDTO.NgayNhap = dtpDateIn.Value;
addBookDTO.NamXuatBan = dtpPublishYear.Value;
addBookDTO.NhaXuatBan = tbPublisher.Text;
addBookDTO.TriGia = nudValue.Value;
//add into db
bool result = addBookBUS.add(addBookDTO);
if (result == true)
{
MessageBox.Show("Thêm sách thành công");
}
else
{
MessageBox.Show("Thêm sách thất bại");
}
}
public bool Tra(borrowbook SachMuon, bookDTO book, ReaderDTO reader)
{
bool re = br.Return(SachMuon, book, reader);
return(re);
}
public bool add(borrowbook SachMuon, bookDTO book, ReaderDTO reader)
{
bool re = br.Add(SachMuon, book, reader);
return(re);
}
public bool mod(bookDTO bookDTO)
{
bool re = bookDAL.mod(bookDTO);
return(re);
}
public bool del(bookDTO bookDTO)
{
bool re = bookDAL.del(bookDTO);
return(re);
}
public bool add(bookDTO bookDTO)
{
bool isAdd = bookDAL.add(bookDTO);
return(isAdd);
}
private void frmModBook_Load(object sender, EventArgs e)
{
bookDTO = new bookDTO();
}
