public sachDTO thongtinSach(int masach)
{
sachDTO sDTO = new sachDTO();
string query = string.Format("select * from [sach] where masach=@masach");
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@masach", SqlDbType.Int);
param[0].Value = masach;
DataTable dtb = new DataTable();
dtb = conn.excuteNonQuery(query, param);
if (dtb.Rows.Count > 0)
{
DataRow dr = dtb.Rows[0];
sDTO.Masach = int.Parse(dr["masach"].ToString());
sDTO.Tensach = dr["tensach"].ToString();
sDTO.Theloai = dr["Theloai"].ToString();
sDTO.Tacgia = dr["tacgia"].ToString();
sDTO.Nxb = dr["nxb"].ToString();
sDTO.Ngaynhap = DateTime.Parse(dr["ngaynhap"].ToString());
sDTO.Ngayxb = DateTime.Parse(dr["ngayxb"].ToString());
sDTO.Giatri = int.Parse(dr["giatri"].ToString());
sDTO.Trangthai = int.Parse(dr["trangthai"].ToString());
}
return(sDTO);
}
// them sach
public bool themSach(sachDTO sDTO)
{
string query = string.Format("insert into [sach] values (@masach,@tensach,@theloai,@tacgia,@nxb,@ngaynhap,@ngayxb,@giatri,@trangthai)");
SqlParameter[] param = new SqlParameter[9];
param[0] = new SqlParameter("@masach", SqlDbType.Int);
param[0].Value = Convert.ToString(sDTO.Masach);
param[1] = new SqlParameter("@tensach", SqlDbType.NVarChar);
param[1].Value = Convert.ToString(sDTO.Tensach);
param[2] = new SqlParameter("@theloai", SqlDbType.NVarChar);
param[2].Value = Convert.ToString(sDTO.Theloai);
param[3] = new SqlParameter("@tacgia", SqlDbType.NVarChar);
param[3].Value = Convert.ToString(sDTO.Tacgia);
param[4] = new SqlParameter("@nxb", SqlDbType.NVarChar);
param[4].Value = Convert.ToString(sDTO.Nxb);
param[5] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
param[5].Value = Convert.ToString(sDTO.Ngaynhap);
param[6] = new SqlParameter("@ngayxb", SqlDbType.DateTime);
param[6].Value = Convert.ToString(sDTO.Ngayxb);
param[7] = new SqlParameter("@giatri", SqlDbType.Int);
param[7].Value = Convert.ToString(sDTO.Giatri);
param[8] = new SqlParameter("@trangthai", SqlDbType.Int);
param[8].Value = Convert.ToString(sDTO.Trangthai);
conn.excuteNonQuery2(query, param);
return(true);
}
// update sach
public bool suaSach(sachDTO sDTO, int masach)
{
string query = string.Format(" update [sach] set tensach=@tensach,theloai=@theloai,tacgia=@tacgia, nxb=@nxb,ngaynhap=@ngaynhap,ngayxb=@ngayxb,giatri=@giatri,trangthai=@trangthai where masach=@masach ");
SqlParameter[] param = new SqlParameter[9];
param[0] = new SqlParameter("@masach", SqlDbType.Int);
param[0].Value = Convert.ToString(sDTO.Masach);
param[1] = new SqlParameter("@tensach", SqlDbType.NVarChar);
param[1].Value = Convert.ToString(sDTO.Tensach);
param[2] = new SqlParameter("@theloai", SqlDbType.NVarChar);
param[2].Value = Convert.ToString(sDTO.Theloai);
param[3] = new SqlParameter("@tacgia", SqlDbType.NVarChar);
param[3].Value = Convert.ToString(sDTO.Tacgia);
param[4] = new SqlParameter("@nxb", SqlDbType.NVarChar);
param[4].Value = Convert.ToString(sDTO.Nxb);
param[5] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
param[5].Value = Convert.ToString(sDTO.Ngaynhap);
param[6] = new SqlParameter("@ngayxb", SqlDbType.DateTime);
param[6].Value = Convert.ToString(sDTO.Ngayxb);
param[7] = new SqlParameter("@giatri", SqlDbType.Int);
param[7].Value = Convert.ToString(sDTO.Giatri);
param[8] = new SqlParameter("@trangthai", SqlDbType.Int);
param[8].Value = Convert.ToString(sDTO.Trangthai);
conn.excuteNonQuery2(query, param);
return(true);
}
private void btnThemSach_Click(object sender, EventArgs e)
{
if (txbTenSach.Text != "" && txbMaSach.Text != "" && cbTheLoaiSach.Text != "" && txbTacGiaSach.Text != "" && txbNhaXuatBanSach.Text != "" && txbNamXuatBanSach.Text != "" && txbGiaTriSach.Text != "")
{
QuanLiSachBUS qlsBUSS = new QuanLiSachBUS();
DateTime dtXuatban = new DateTime(int.Parse(txbNamXuatBanSach.Text), 1, 1);
sachDTO sach = new sachDTO(int.Parse(txbMaSach.Text), txbTenSach.Text, cbTheLoaiSach.Text, txbTacGiaSach.Text, txbNhaXuatBanSach.Text, DateTime.Now, dtXuatban, int.Parse(txbGiaTriSach.Text), 1);
if (qlsBUSS.ThemSach(sach))
{
MessageBox.Show("Thêm sách thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
refresh();
if (!listLoaiSach.Contains(new loaisachDTO(cbTheLoaiSach.Text)))
{
new QuanLiTheLoaiSachBUS().ThemTheLoaisach(new loaisachDTO(cbTheLoaiSach.Text));
}
}
else
{
MessageBox.Show("Thêm sách thất bại. " + BUS_notification.mess, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Vui lòng điền đầy đủ thông tin", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
frmChinh.loadDanhSachSach();
}
private void btnSuaThongTinSach_Click(object sender, EventArgs e)
{
refreshError();
if (listSach.Count > 0)
{
if (txbTenSach.Text != "" && cbTheLoaiSach.Text != "" && txbTacGiaSach.Text != "" && txbNhaXuatBanSach.Text != "" && txbNamXuatBanSach.Text != "" && txbGiaTriSach.Text != "")
{
DateTime datebyYear = new DateTime(int.Parse(txbNamXuatBanSach.Text), 1, 1);
int trangThaiSach = 0;
for (int i = 0; i < QuanLiSachBUS.DanhSachTrangThaiSach.Length; i++)
{
if (cbTinhTrangSach.Text == QuanLiSachBUS.DanhSachTrangThaiSach[i])
{
trangThaiSach = i;
}
}
sachDTO sach = new sachDTO(int.Parse(txbMaSach.Text), txbTenSach.Text, cbTheLoaiSach.Text, txbTacGiaSach.Text, txbNhaXuatBanSach.Text, dtpNgayNhapSach.Value, datebyYear, int.Parse(txbGiaTriSach.Text), trangThaiSach);
bool isExistTheLoaiSach = false;
foreach (loaisachDTO loaisach in listLoaiSach)
{
if (cbTheLoaiSach.Text == loaisach.Theloaisach)
{
isExistTheLoaiSach = true;
}
}
if (isExistTheLoaiSach == false)
{
quanLiTheLoaiSachBUS.ThemTheLoaisach(new loaisachDTO(cbTheLoaiSach.Text));
listLoaiSach.Clear();
listLoaiSach = quanLiTheLoaiSachBUS.LayDanhSachCacTheLoai();
cbTheLoaiSach.Items.Clear();
cbTimSachTheoTheLoai.Items.Clear();
foreach (loaisachDTO loaisach in listLoaiSach)
{
cbTheLoaiSach.Items.Add(loaisach.Theloaisach);
cbTimSachTheoTheLoai.Items.Add(loaisach.Theloaisach);
}
}
if (quanLiSachBUS.SuaSach(sach))
{
sttErrorLabel.Text = "Cập nhật thông tin thành công";
loadDanhSachSach(indexSach);
}
else
{
sttErrorLabel.Text = "Cập nhật thông tin thất bại. " + BUS_notification.mess;
}
}
else
{
sttErrorLabel.Text = "Vui lòng điền đầy đủ thông tin";
}
}
}
public dongThongTinSach(sachDTO sach, DateTime date)
{
InitializeComponent();
lbMaSach.Text = sach.Masach.ToString();
lbTenSach.Text = sach.Tensach;
dtpNgayMuonSach.Value = date;
cbTinhTrangSach.Text = QuanLiSachBUS.DanhSachTrangThaiSach[sach.Trangthai];
}
public void loadDanhSachSach()
{
listSach = quanLiSachBUS.DanhSachSach();
if (listSach.Count > 0)
{
sach = listSach[0];
anhXaThongTinSach(0);
}
}
private void initDongThongTinSach(sachDTO sach)
{
dongThongTinSach dongThongTin = new dongThongTinSach(sach);
dongThongTin.changeEnable_CbTinhTrangSach(false);
dongThongTin.chkChonSach.CheckState = CheckState.Checked;
listDongThongTinSach.Add(dongThongTin);
dongThongTin.Location = new Point(3, 3 + dongThongTin.Height * (listSach.Count() - 1));
pnDanhSachSachMuon.Controls.Add(dongThongTin);
}
/// <summary>
/// Sửa thông tin sách
/// </summary>
/// <param name="sach"></param>
/// <returns></returns>
public bool SuaSach(sachDTO sach)
{
sachDAL saxDAL = new sachDAL();
if (saxDAL.isSach(sach.Masach))
{
return(saxDAL.suaSach(sach, sach.Masach));
}
return(false);
}
private void dgvThongTinSach_CellClick(object sender, DataGridViewCellEventArgs e)
{
DataGridView data = (DataGridView)sender;
if (data.RowCount > 0)
{
indexSach = data.SelectedRows[0].Index;
sach = listSach[indexSach];
anhXaThongTinSach(indexSach);
}
}
public void loadDanhSachSach(int index)
{
listSach = quanLiSachBUS.DanhSachSach();
dgvThongTinSach.DataSource = listSach.Select(o => new { Column1 = o.Masach, Column2 = o.Tensach, Column3 = o.Tacgia, Column4 = o.Nxb }).ToList();
dgvThongTinSach.Rows[0].Selected = false;
dgvThongTinSach.Rows[index].Selected = true;
dgvThongTinSach.CurrentCell = dgvThongTinSach.Rows[index].Cells[0];
anhXaThongTinSach(index);
indexSach = index;
sach = listSach[indexSach];
}
/// <summary>
/// Cập nhật thông tin cho sách đã mượn
/// </summary>
/// <param name="sDTO"></param>
/// <returns></returns>
internal bool MuonSach(sachDTO sDTO)
{
sachDAL saxDAL = new sachDAL();
if (saxDAL.isSach(sDTO.Masach))
{
sDTO.Trangthai = (int)TrangThaiSach.DaChoMuon;
return(SuaSach(sDTO));
}
return(false);
}
private void initThongTinSach(sachDTO sach)
{
txbMaSach.Text = sach.Masach.ToString();
txbTenSach.Text = sach.Tensach;
cbTheLoaiSach.Text = sach.Theloai;
txbTacGiaSach.Text = sach.Tacgia;
txbNhaXuatBanSach.Text = sach.Nxb;
dtpNgayNhapSach.Value = sach.Ngaynhap;
txbNamXuatBanSach.Text = sach.Ngayxb.Year.ToString();
txbGiaTriSach.Text = sach.Giatri.ToString();
cbTinhTrangSach.Text = QuanLiSachBUS.DanhSachTrangThaiSach[sach.Trangthai];
}
/// <summary>
/// Cập nhật thông tin cho sách đã trả về kho
/// </summary>
/// <param name="sDTO"></param>
/// <returns></returns>
internal bool Travekho(sachDTO sDTO)
{
sachDAL saxDAL = new sachDAL();
if (saxDAL.isSach(sDTO.Masach))
{
sDTO.Trangthai = (int)TrangThaiSach.CoSan;
return(SuaSach(sDTO));
}
return(false);
}
private void btnTaoPhieuMuon_Click(object sender, EventArgs e)
{
lbError.Text = "";
if (cbTimSachTheoMa.Text != "")
{
if (cbTimSachTheoMa.SelectedIndex >= 0)
{
sachDTO sach = quanLiSach.Timsachtheoma(listSachCoSan[cbTimSachTheoMa.SelectedIndex].Masach);
listSach.Add(sach);
listSachCoSan.RemoveAt(cbTimSachTheoMa.SelectedIndex);
initDongThongTinSach(sach);
cbTimSachTheoMa.Items.Clear();
initComboBoxListItems();
cbTimSachTheoMa.DroppedDown = false;
}
else
{
lbError.Text = "Mã sách không tồn tại";
}
cbTimSachTheoMa.Text = "";
ScrollToBottom(panel3);
}
else
{
List <sachDTO> listSachMuon = new List <sachDTO>();
for (int i = 0; i < listDongThongTinSach.Count; i++)
{
if (listDongThongTinSach[i].chkChonSach.Checked)
{
listSachMuon.Add(listSach[i]);
}
}
if (listSachMuon.Count != 0)
{
if (quanLiMuonTraMat.MuonSach(docgia, listSachMuon))
{
MessageBox.Show("Tạo phiếu mượn thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show(BUS_notification.mess, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
lbError.Text = "Chưa nhập mã sách";
}
}
}
private void initDongThongTinSach(sachDTO sach, DateTime ngaymuon)
{
dongThongTinSach dongThongTin = new dongThongTinSach(sach, ngaymuon);
listDongThongTinSach.Add(dongThongTin);
dongThongTin.cbTinhTrangSach.DataSource = new List <String> {
QuanLiSachBUS.DanhSachTrangThaiSach[0], QuanLiSachBUS.DanhSachTrangThaiSach[2]
};
dongThongTin.Location = new Point(3, 3 + dongThongTin.Height * (listDongThongTinSach.Count() - 1));
dongThongTin.chkChonSach.CheckState = CheckState.Unchecked;
dongThongTin.Click += DongThongTin_Click;
pnDanhSachSachDangMuon.Controls.Add(dongThongTin);
}
// tim kiem doc gia theo ten
public bool timkiemsach(string field, string Searchstr, List <sachDTO> listSach)
{
//****** ưu tiên chuỗi đầu tien hơn
// tìm vs chuỗi vào có dấu
// thêm vào list
// tạo một list tạm
// chuyển tất cả các tên trong list thành không dấu vào list tạm
// chuyển searchstr thành ko dấu
// tim trong list ko dấu vs str ko dau
// add
//string searchStr2=ConvertToUnSign(Searchstr);
string query = string.Format("select * from [sach] where " + field + " like @" + field);
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@" + field, SqlDbType.NVarChar);
param[0].Value = Convert.ToString("%" + Searchstr + "%");
DataTable dtb = new DataTable();
dtb = conn.excuteNonQuery(query, param);
foreach (DataRow dr in dtb.Rows)
{
sachDTO sDTO = new sachDTO();
sDTO.Masach = int.Parse(dr["masach"].ToString());
sDTO.Tensach = dr["tensach"].ToString();
sDTO.Theloai = dr["Theloai"].ToString();
sDTO.Tacgia = dr["tacgia"].ToString();
sDTO.Nxb = dr["nxb"].ToString();
sDTO.Ngaynhap = DateTime.Parse(dr["ngaynhap"].ToString());
sDTO.Ngayxb = DateTime.Parse(dr["ngayxb"].ToString());
sDTO.Giatri = int.Parse(dr["giatri"].ToString());
sDTO.Trangthai = int.Parse(dr["trangthai"].ToString());
listSach.Add(sDTO);
}
if (dtb.Rows.Count > 0)
{
return(true);
}
return(false);
}
//hien thi danh sach
public bool tatcaSach(List <sachDTO> lsachDTO)
{
string query = string.Format("select* from [sach] ");
SqlParameter[] parm = new SqlParameter[1];
// ko can thiet nhung phai co
docgiaDTO dg = new docgiaDTO();
parm[0] = new SqlParameter("@masach", SqlDbType.Int);
parm[0].Value = dg.MaThe;
DataTable datatable = new DataTable();
datatable = conn.excuteNonQuery(query, parm);
//gan value trong datatable vao DTO
foreach (DataRow dr in datatable.Rows)
{
sachDTO sDTO = new sachDTO();
sDTO.Masach = int.Parse(dr["masach"].ToString());
sDTO.Tensach = dr["tensach"].ToString();
sDTO.Theloai = dr["Theloai"].ToString();
sDTO.Tacgia = dr["tacgia"].ToString();
sDTO.Nxb = dr["nxb"].ToString();
sDTO.Ngaynhap = DateTime.Parse(dr["ngaynhap"].ToString());
sDTO.Ngayxb = DateTime.Parse(dr["ngayxb"].ToString());
sDTO.Giatri = int.Parse(dr["giatri"].ToString());
sDTO.Trangthai = int.Parse(dr["trangthai"].ToString());
lsachDTO.Add(sDTO);
}
return(true);
}
/// <summary>
/// Event Tab Sach
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnTimSach_Click(object sender, EventArgs e)
{
refreshError();
if (txbTimSachTheoMa.Text == "" && txbTimSachTheoTacGia.Text == "" && txbTimSachTheoTen.Text == "" && cbTimSachTheoTheLoai.Text == "")
{
listSach = quanLiSachBUS.DanhSachSach();
}
else
{
try
{
sachDTO sDTO = new sachDTO(txbTimSachTheoMa.Text == "" ? -1 : int.Parse(txbTimSachTheoMa.Text), txbTimSachTheoTen.Text, cbTimSachTheoTheLoai.Text, txbTimSachTheoTacGia.Text, "", DateTime.Now, DateTime.Now, 0, 0);
listSach = quanLiSachBUS.TimSach(sDTO);
}
catch (FormatException error)
{
sttErrorLabel.Text = "Lỗi định dạng. Vui lòng nhập lại";
Console.WriteLine(error.ToString());
}
}
initDataGridViewSach();
}
/// <summary>
/// Thêm sách mới
/// </summary>
/// <param name="sDTO"></param>
/// <returns></returns>
public bool ThemSach(sachDTO sDTO)
{
sachDAL saxDal = new sachDAL();
//kiểm tra các điều kiện
if (saxDal.isSach(sDTO.Masach))
{
return(false);
}
quydinhDAL quydinh = new quydinhDAL();
quydinhDTO listquydinh = new quydinhDTO();
if (!quydinh.listquydinh(listquydinh))
{
return(false);
}
System.DateTime date = DateTime.Now;
int curyear = date.Year;
if (curyear - sDTO.Ngayxb.Year > listquydinh.Kcnamxuatban)
{
return(false);
}
//tạo
List <sachDTO> list = new List <sachDTO>();
if (!saxDal.tatcaSach(list))
{
return(false);
}
// sDTO.Masach = list.Count + 1;
sDTO.Trangthai = 1;
return(saxDal.themSach(sDTO));
}
public frmThongTinSach(sachDTO sach)
{
InitializeComponent();
initThongTinSach(sach);
}
/// <summary>
/// M thêm code vô đây
/// </summary>
/// <param name="sDTO"></param>
/// <returns></returns>
public bool XoaSach(sachDTO sDTO)
{
return(false);
}
/// <summary>
/// Tìm sách có sặn trong thư viện
/// </summary>
/// <param name="sDTO"></param>
/// <returns></returns>
public List <sachDTO> TimSach(sachDTO sDTO)
{
List <sachDTO> list = new List <sachDTO>();
List <sachDTO> result = new List <sachDTO>();
string tensach = sDTO.Tensach;
string theloai = sDTO.Theloai;
string tacgia = sDTO.Tacgia;
string nhaxuatban = sDTO.Nxb;
string masach;
if (sDTO.Masach == -1)
{
masach = "";
}
else
{
masach = sDTO.Masach.ToString();
}
if (tensach == null)
{
tensach = "";
}
if (theloai == null)
{
theloai = "";
}
if (tacgia == null)
{
tacgia = "";
}
if (nhaxuatban == null)
{
nhaxuatban = "";
}
int max_masach_distance = masach.ToString().Length / 2;
int max_name_distance = tensach.Length / 2;
int max_author_distance = tacgia.Length / 2;
int max_publishcompany_distance = nhaxuatban.Length / 2;
sachDAL saxDAL = new sachDAL();
sachDTO temp = new sachDTO();
list = DanhSachSach();
//if(masach != "")
//if (saxDAL.isSach(sDTO.Masach,ref temp))
//{
// result.Add(temp);
//}
if (masach != "")
{
if (saxDAL.isSach(sDTO.Masach))
{
foreach (sachDTO sax in list)
{
if (sax.Masach == int.Parse(masach))
{
result.Add(sax);
return(result);
}
}
}
}
if (tensach != "")
{
List <ResultItem> item = new List <ResultItem>();
foreach (sachDTO sa in list)
{
int check = TimKiemBUS.CheckisAvaiable(tensach, sa.Tensach);
if (check != -1)
{
ResultItem rItem;
rItem.mark = check;
rItem.sach = sa;
item.Add(rItem);
}
}
item.Sort((s1, s2) => s1.mark.CompareTo(s2.mark));
foreach (ResultItem i in item)
{
result.Add(i.sach);
}
}
if (theloai != "")
{
foreach (sachDTO sach in list)
{
if (sach.Theloai == theloai)
{
if (result.IndexOf(sach) == -1)
{
result.Add(sach);
}
}
}
}
if (tacgia != "")
{
foreach (sachDTO sach in list)
{
if (TimKiemBUS.CheckisAvaiable(tacgia, sach.Tacgia) != -1)
{
if (result.IndexOf(sach) == -1)
{
result.Add(sach);
}
}
}
}
if (nhaxuatban != "")
{
foreach (sachDTO sach in list)
{
if (Levenshtein_Distance.Distance(sach.Nxb, nhaxuatban) <= sach.Nxb.Length)
{
if (result.IndexOf(sach) == -1)
{
result.Add(sach);
}
}
}
}
return(result);
}
