public void PhanLoai(SachBO sachBO)
{
//cập nhật lại sách
SqlConnection cnn = new SqlConnection(cnnstr);
string query ="Update Sach Set maloai=@maloai, mactphanloai=@mactphanloai";
query += " ,khuvuc=@khuvuc, ke=@ke,ngan=@ngan, muondemve=@muondemve";
query += " WHERE madausach=@madausach";
SqlCommand cmd = new SqlCommand(query, cnn);
if(sachBO.MaLoai!="")
cmd.Parameters.AddWithValue("@maloai", sachBO.MaLoai);
else
cmd.Parameters.AddWithValue("@maloai", DBNull.Value);
if(sachBO.MaCTPhanLoai!="")
cmd.Parameters.AddWithValue("@mactphanloai", sachBO.MaCTPhanLoai);
else
cmd.Parameters.AddWithValue("@mactphanloai", DBNull.Value);
if(sachBO.khuvuc!="")
cmd.Parameters.AddWithValue("@khuvuc", sachBO.khuvuc);
else
cmd.Parameters.AddWithValue("@khuvuc", DBNull.Value);
cmd.Parameters.AddWithValue("@ke", sachBO.ke);
cmd.Parameters.AddWithValue("@ngan", sachBO.ngan);
cmd.Parameters.AddWithValue("@muondemve", sachBO.muondemve);
cmd.Parameters.AddWithValue("@madausach", sachBO.Madausach);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
public void SuaSach(SachBO sachBO, bool hasimage)
{
//cập nhật lại sách
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "";
query = "Update Sach Set MaNXB=@MaNXB,TenSach=@TenSach,NamXuatBan=@NamXuatBan";
query += ",LanXuatBan=@LanXuatBan,TriGia=@TriGia,NgayNhap=@NgayNhap";
if (hasimage == true)
query += ",hinhanh=@HinhAnh";
query += " WHERE Madausach=@madausach";
SqlCommand cmd = new SqlCommand(query, cnn);
if(sachBO.MaNXB!="")
cmd.Parameters.AddWithValue("@MaNXB", sachBO.MaNXB);
else
cmd.Parameters.AddWithValue("@MaNXB", DBNull.Value);
cmd.Parameters.AddWithValue("@TenSach", sachBO.TenSach);
cmd.Parameters.AddWithValue("@NamXuatBan", sachBO.namxuatban);
cmd.Parameters.AddWithValue("@LanXuatBan", sachBO.lanxuatban);
cmd.Parameters.AddWithValue("@TriGia", sachBO.trigia);
if(hasimage==true)
cmd.Parameters.AddWithValue("@HinhAnh", sachBO.hinhanh);
cmd.Parameters.AddWithValue("@NgayNhap", sachBO.ngaynhap);
cmd.Parameters.AddWithValue("@madausach",sachBO.Madausach);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
//cập nhật lại ds tác giả của sách
//dell all
query = "DELETE FROM Sach_TacGia WHERE masach in (SELECT masach FROM sach WHERE madausach=@masachdel)";
SqlCommand cmdDel = new SqlCommand(query, cnn);
cmdDel.Parameters.AddWithValue("@masachdel", sachBO.Madausach);
cnn.Open();
cmdDel.ExecuteNonQuery();
cnn.Close();
//insert
SachCollection sachColl = new SachCollection();
sachColl = TimDSSach(sachBO.Madausach);
for (int i = 0; i < sachColl.Count; i++)
{
query = " INSERT into Sach_TacGia(masach,matg) VALUES(@masach,@matg)";
for (int j = 0; j < sachBO.tacgiaColl.Count; j++)
{
SqlCommand cmdTG = new SqlCommand(query, cnn);
cmdTG.Parameters.AddWithValue("@masach", sachColl.Index(i).MaSach);
cmdTG.Parameters.AddWithValue("@matg", sachBO.tacgiaColl.Index(j).MaTG);
cnn.Open();
cmdTG.ExecuteNonQuery();
cnn.Close();
}
}
}
protected void SachGridview_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && DataBinder.Eval(e.Row.DataItem, "masach") != null)
{
//Mã sách
string masach = DataBinder.Eval(e.Row.DataItem, "masach").ToString();
if (masach != "")
{
Image hinhanh = (Image)e.Row.FindControl("HinhAnhImage");
Label tensach = (Label)e.Row.FindControl("TenSachLabel");
Label nhaxuatban = (Label)e.Row.FindControl("NhaxuatbanLabel");
Label namxuatban = (Label)e.Row.FindControl("NamxuatbanLabel");
Label lanxuatban = (Label)e.Row.FindControl("LanxuatbanLabel");
ListBox tacgia = (ListBox)e.Row.FindControl("TacGiaListbox");
Label trigia = (Label)e.Row.FindControl("TriGiaLabel");
Label ngaymuon = (Label)e.Row.FindControl("NgaymuonLabel");
Label ngayhethan = (Label)e.Row.FindControl("HethanLabel");
Label ngaygiahan = (Label)e.Row.FindControl("GiahanLabel");
Label ngaytrehan = (Label)e.Row.FindControl("NgaytrehanLabel");
//Gán dữ liệu
SachBO sachBO = new SachBO();
sachBO = sachBUS.Tim1Sach(masach);
hinhanh.ImageUrl = sachBO.hinhanh;
tensach.Text = sachBO.TenSach;
nhaxuatban.Text = nxbBUS.Tim1NXB(sachBO.MaNXB).TenNXB;
namxuatban.Text = sachBO.namxuatban.ToString();
lanxuatban.Text = sachBO.lanxuatban.ToString();
tacgia.DataSource = sachBO.tacgiaColl;
tacgia.DataTextField = "TenTG";
trigia.Text = sachBO.trigia.ToString();
string maphieumuon = DataBinder.Eval(e.Row.DataItem, "maphieumuon").ToString();
PhieuMuonBO phieumuonBO = new PhieuMuonBO();
phieumuonBO = phieumuonBUS.Tim1PhieuMuon(maphieumuon);
ngaymuon.Text = phieumuonBO.NgayMuon;
ngayhethan.Text = phieumuonBO.NgayHetHan;
DateTime _ngayhethan = Convert.ToDateTime(nhanvienBUS.ChuyenNgayThang(phieumuonBO.NgayHetHan));
TimeSpan dt = DateTime.Now - _ngayhethan;
DateTime _giahan = new DateTime();
if (DataBinder.Eval(e.Row.DataItem, "giahan")!=null&&DataBinder.Eval(e.Row.DataItem, "giahan").ToString() != "")
{
_giahan = Convert.ToDateTime(nhanvienBUS.ChuyenNgayThang(DataBinder.Eval(e.Row.DataItem, "giahan").ToString()));
ngaygiahan.Text = _giahan.ToString();
dt = DateTime.Now - _giahan;
}
int _ngaytrehan = Convert.ToInt32(dt.Days);
if (_ngaytrehan > 0)
ngaytrehan.Text = _ngaytrehan.ToString();
}
}
}
protected void SachGridview_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && DataBinder.Eval(e.Row.DataItem, "masach") != null)
{
//Lấy các con trol
Label MaSachLabel = (Label)e.Row.FindControl("MaSachLabel");
Label TenSachLabel = (Label)e.Row.FindControl("TenSachLabel");
Label MaPhieuMuonLabel = (Label)e.Row.FindControl("MaPhieuMuonLabel");
Label NgayMuonLabel = (Label)e.Row.FindControl("NgayMuonLabel");
Label NgayHetHanLabel = (Label)e.Row.FindControl("NgayHetHanLabel");
Label TenNhanVienLabel = (Label)e.Row.FindControl("TenNhanVienLabel");
Label MaNhanVienLabel = (Label)e.Row.FindControl("MaNhanVienLabel");
Label DocGiaLabel = (Label)e.Row.FindControl("DocGiaLabel");
Label MaDocGiaLabel = (Label)e.Row.FindControl("MaDocGiaLabel");
Label GiaHanLabel = (Label)e.Row.FindControl("GiaHanLabel");
Button GiaHanButton= (Button)e.Row.FindControl("GiaHanButton");
string masach = DataBinder.Eval(e.Row.DataItem, "masach").ToString();
//nạp thông tin sách
SachBO sachBO = new SachBO();
sachBO = sachBUS.Tim1Sach(masach);
MaSachLabel.Text = sachBO.MaSach;
TenSachLabel.Text = sachBO.TenSach;
//nạp thông tin phiếu mượn
string maphieumuon = DataBinder.Eval(e.Row.DataItem, "maphieumuon").ToString();
PhieuMuonBO phieumuonBO = new PhieuMuonBO();
phieumuonBO = phieumuonBUS.Tim1PhieuMuon(maphieumuon);
MaPhieuMuonLabel.Text = phieumuonBO.MaPhieuMuon;
NgayMuonLabel.Text = phieumuonBO.NgayMuon;
NgayHetHanLabel.Text = phieumuonBO.NgayHetHan;
GiaHanLabel.Text = phieumuonBUS.TimGiaHan(maphieumuon, masach);
if (GiaHanLabel.Text != "")
GiaHanButton.Visible = false;
//nạp thông tin nhân viên
NhanVienBO nhanvienBO = new NhanVienBO();
nhanvienBO = nhanvienBUS.Tim1Nhanvien(phieumuonBO.MaNV);
TenNhanVienLabel.Text = nhanvienBO.TenNV;
MaNhanVienLabel.Text = nhanvienBO.MaNV;
//nạp thông tin độc giả
string madg = doctaichoBUS.Tim1DocGia_Luot(phieumuonBO.MaLuot);
DocGiaBO docgiaBO = new DocGiaBO();
docgiaBO = docgiaBUS.Tim1DocGia(madg);
DocGiaLabel.Text = docgiaBO.TenDocGia;
MaDocGiaLabel.Text = docgiaBO.MaDocGia;
}
}
public bool ThemSach(string manxb,string tensach, int namxuatban,int lanxuatban,decimal trigia,string hinhanh, TacGiaCollection tacgiaColl,string ngaynhap, int soluong)
{
try
{
SachBO sachBO = new SachBO();
sachBO.MaNXB = manxb;
sachBO.TenSach = tensach;
sachBO.namxuatban = namxuatban;
sachBO.lanxuatban = lanxuatban;
sachBO.trigia = trigia;
sachBO.hinhanh = hinhanh;
sachBO.ngaynhap = ngaynhap;
sachBO.tacgiaColl = tacgiaColl;
sachDAO.ThemSach(sachBO,soluong);
return true;
}
catch
{
return false;
}
}
public bool SuaSach(int madausach, string manxb, string tensach, int namxuatban, int lanxuatban, decimal trigia, string hinhanh, TacGiaCollection tacgiaColl, string ngaynhap,bool hasimage)
{
try
{
SachBO sachBO= new SachBO();
sachBO.Madausach = madausach;
sachBO.MaNXB = manxb;
sachBO.TenSach = tensach;
sachBO.namxuatban = namxuatban;
sachBO.lanxuatban = lanxuatban;
sachBO.trigia = trigia;
sachBO.hinhanh = hinhanh;
sachBO.tacgiaColl = tacgiaColl;
sachBO.ngaynhap = ngaynhap;
sachDAO.SuaSach(sachBO,hasimage);
return true;
}
catch
{
return false;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["sachid"] != null)
{
try
{
string madausach = Request.QueryString["sachid"].ToString();
SachBO sachBO = new SachBO();
sachBO = sachBUS.Tim1Sach(Convert.ToInt32(madausach));
HinhAnhImage.ImageUrl = sachBO.hinhanh;
TenSachLabel.Text = sachBO.TenSach;
NhaXuatBanLabel.Text = nhaxuatbanBUS.Tim1NXB(sachBO.MaNXB).TenNXB;
NamXuatBanLabel.Text = sachBO.namxuatban.ToString();
LanXuatBanLabel.Text = sachBO.lanxuatban.ToString();
TacGiaListBox.DataSource = sachBO.tacgiaColl;
TacGiaListBox.DataTextField = "TenTG";
TacGiaListBox.DataBind();
if(sachBO.MaLoai!="")
LoaiSachLabel.Text = loaisachBUS.Tim1LoaiSach(sachBO.MaLoai).TenLoai;
if (sachBO.MaCTPhanLoai != "")
{
TheLoaiLabel.Text = phanloaiBUS.Tim1CTPhanLoai(sachBO.MaCTPhanLoai).TenCTPhanLoai;
ChuDeLabel.Text = phanloaiBUS.Tim1PhanLoai_CT(sachBO.MaCTPhanLoai).TenPhanLoai;
}
if(sachBO.khuvuc!="")
KhuVucLabel.Text =khuvucBUS.Tim1KhuVuc(sachBO.khuvuc).TenKhuVuc;
KeLabel.Text = sachBO.ke;
NganLabel.Text = sachBO.ngan;
TriGiaLabel.Text = sachBO.trigia.ToString();
}
catch
{
}
}
}
//Xử lý tìm sáhc
public SachCollection TimDSSachDocNhieu()
{
SachCollection sachColl = new SachCollection();
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "SELECT distinct Top 30 MaLoai,MaCTPhanLoai,MaNXB,TenSach,NamXuatBan,LanXuatBan,TriGia,TrangThai,MuonDemVe,HinhAnh,KhuVuc,Ke,Ngan";
query += ",convert(nvarchar(10),NgayNhap,103) ngaynhap,MaPhieuThanhLy, cast(LydoThanhLy as nvarchar(max)) lydothanhly,Madausach";
query += " FROM Sach WHERE";
query += " trangthai=1 AND masach in (select top 30 masach from ChiTietPhieuMuon_Tra group by masach order by COUNT(*) desc)";
SqlCommand cmd = new SqlCommand(query, cnn);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SachBO sachBO = new SachBO();
// sachBO.MaSach=dr["masach"].ToString();
sachBO.MaNXB = dr["manxb"].ToString();
sachBO.MaLoai = dr["maloai"].ToString();
sachBO.MaCTPhanLoai = dr["mactphanloai"].ToString();
sachBO.TenSach = dr["tensach"].ToString();
sachBO.namxuatban = Convert.ToInt32(dr["namxuatban"].ToString());
sachBO.lanxuatban = Convert.ToInt32(dr["lanxuatban"].ToString());
sachBO.trigia = Convert.ToDecimal(dr["trigia"].ToString());
sachBO.hinhanh = dr["hinhanh"].ToString();
sachBO.muondemve = Convert.ToBoolean(dr["muondemve"].ToString());
sachBO.ngaynhap = dr["ngaynhap"].ToString();
sachBO.khuvuc = dr["khuvuc"].ToString();
sachBO.ke = dr["ke"].ToString();
sachBO.ngan = dr["ngan"].ToString();
sachBO.maphieuthanhly = dr["maphieuthanhly"].ToString();
sachBO.lydothanhly = dr["lydothanhly"].ToString();
sachBO.Madausach = Convert.ToInt32(dr["madausach"].ToString());
string masach = TimDSSach(sachBO.Madausach).Index(0).MaSach;
sachBO.tacgiaColl = tgDAO.TimDSTacGia_Sach(masach);// tất cả các tác giả
sachColl.Add(sachBO);
}
cnn.Close();
return sachColl;
}
public SachCollection TimSach(string tensach,string maloaisach,string maphanloai,string mactphanloai)
{
SachCollection sachColl = new SachCollection();
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "SELECT distinct MaLoai,MaCTPhanLoai,MaNXB,TenSach,NamXuatBan,LanXuatBan,TriGia,TrangThai,MuonDemVe,HinhAnh,KhuVuc,Ke,Ngan";
query += ",convert(nvarchar(10),NgayNhap,103) ngaynhap,MaPhieuThanhLy, cast(LydoThanhLy as nvarchar(max)) lydothanhly,Madausach";
query += " FROM Sach WHERE";
query += " tensach like N'%"+tensach+"%'";
if (maloaisach != "")
query += " AND maloai=@maloaisach";
if(mactphanloai!="")
query += " AND mactphanloai=@mactphanloai";
if (maphanloai != "")
query += " AND MaCTphanloai in (SELECT maCTphanloai FrOM chitietphanloai WHERE maphanloai=@maphanloai)";
SqlCommand cmd = new SqlCommand(query, cnn);
if (maloaisach != "")
cmd.Parameters.AddWithValue("@maloaisach", maloaisach );
if (mactphanloai != "")
cmd.Parameters.AddWithValue("@mactphanloai",mactphanloai);
if(maphanloai!="")
cmd.Parameters.AddWithValue("@maphanloai", maphanloai);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SachBO sachBO = new SachBO();
// sachBO.MaSach=dr["masach"].ToString();
sachBO.MaNXB = dr["manxb"].ToString();
sachBO.MaLoai = dr["maloai"].ToString();
sachBO.MaCTPhanLoai = dr["mactphanloai"].ToString();
sachBO.TenSach = dr["tensach"].ToString();
sachBO.namxuatban = Convert.ToInt32(dr["namxuatban"].ToString());
sachBO.lanxuatban = Convert.ToInt32(dr["lanxuatban"].ToString());
sachBO.trigia = Convert.ToDecimal(dr["trigia"].ToString());
sachBO.hinhanh = dr["hinhanh"].ToString();
sachBO.muondemve = Convert.ToBoolean(dr["muondemve"].ToString());
sachBO.ngaynhap = dr["ngaynhap"].ToString();
sachBO.khuvuc = dr["khuvuc"].ToString();
sachBO.ke = dr["ke"].ToString();
sachBO.ngan = dr["ngan"].ToString();
sachBO.maphieuthanhly = dr["maphieuthanhly"].ToString();
sachBO.lydothanhly = dr["lydothanhly"].ToString();
sachBO.Madausach = Convert.ToInt32(dr["madausach"].ToString());
string masach = TimDSSach(sachBO.Madausach).Index(0).MaSach;
sachBO.tacgiaColl = tgDAO.TimDSTacGia_Sach(masach);// tất cả các tác giả
sachColl.Add(sachBO);
}
cnn.Close();
return sachColl;
}
protected void LuuButton_Click(object sender, EventArgs e)
{
bool res=KiemTraHopLe(NamXuatBanSuaTextBox.Text, LanXuatBanSuaTextBox.Text, TriGiaSuaTextBox.Text);
if (res == false)
{
SuaPopup.Show();
return;
}
SachBO sachBO = new SachBO();
//lấy thông tim mới để sửa lại sách
int madausach=Convert.ToInt32( ViewState["madausach"].ToString());
sachBO = sachBUS.Tim1Sach(madausach);
string manxb=NhaXuatBanSuaDropDown.SelectedValue;
string tensach=TenSachSuaTextBox.Text;
int namxuatban=Convert.ToInt32(NamXuatBanSuaTextBox.Text);
int lanxuatban=Convert.ToInt32(LanXuatBanSuaTextBox.Text);
decimal trigia=Convert.ToDecimal(TriGiaSuaTextBox.Text);
string hinhanh="";
TacGiaCollection tacgiaColl= new TacGiaCollection();
for (int i = 0; i < TacGiaSuaListBox.Items.Count; i++)
{
if (TacGiaSuaListBox.Items[i].Selected == true)
{
TacGiaBO tgBO = new TacGiaBO();
tgBO.MaTG = TacGiaSuaListBox.Items[i].Value;
tacgiaColl.Add(tgBO);
}
}
string ngaynhap = nhanvienBUS.ChuyenNgayThang(NgayNhapSuaTextBox.Text);
//Lấy thông tin hình cần upload và đánh dấu có hình hay không
bool hasimage = true;//biến cho biết là có hình mới đựơc upload không
if (HinhAnhSuaUpload.PostedFile != null && HinhAnhSuaUpload.PostedFile.FileName != "")
hinhanh = "~/images/sach/" + HinhAnhSuaUpload.PostedFile.FileName;
else
{
hinhanh = "";
hasimage = false;
}
//Thực hiện lưu vào CSDL
bool kq = sachBUS.SuaSach(madausach, manxb, tensach, namxuatban, lanxuatban, trigia, hinhanh, tacgiaColl, ngaynhap, hasimage);
if (kq == true)
{
//Lưu hình vào thư mục ../images/sach
if (hasimage == true)
{
if (System.IO.File.Exists(hinhanh))//nếu file tồn tại
System.IO.File.Delete(hinhanh);//thì xóa file đi
HinhAnhSuaUpload.SaveAs(Server.MapPath("..\\images\\sach\\") + System.IO.Path.GetFileName(hinhanh));
}
}
NapDuLieu();
}
public SachBO Tim1Sach(int madausach)
{
SachBO sachBO = new SachBO();
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "SELECT top 1 MaSach, MaLoai,MaCTPhanLoai,MaNXB,TenSach,NamXuatBan,LanXuatBan,TriGia,TrangThai,MuonDemVe,HinhAnh,KhuVuc,Ke,Ngan";
query += ",convert(nvarchar(10),NgayNhap,103) ngaynhap,MaPhieuThanhLy, cast(LydoThanhLy as nvarchar(max)) lydothanhly,Madausach";
query+=" FROM sach WHERE trangthai=1 AND madausach=@madausach";
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Parameters.AddWithValue("@madausach", madausach);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
sachBO.MaSach = dr["masach"].ToString();
sachBO.MaNXB = dr["manxb"].ToString();
sachBO.MaLoai = dr["maloai"].ToString();
sachBO.MaCTPhanLoai = dr["mactphanloai"].ToString();
sachBO.TenSach = dr["tensach"].ToString();
sachBO.namxuatban = Convert.ToInt32(dr["namxuatban"].ToString());
sachBO.lanxuatban = Convert.ToInt32(dr["lanxuatban"].ToString());
sachBO.trigia = Convert.ToDecimal(dr["trigia"].ToString());
sachBO.hinhanh = dr["hinhanh"].ToString();
sachBO.muondemve = Convert.ToBoolean(dr["muondemve"].ToString());
sachBO.ngaynhap = dr["ngaynhap"].ToString();
sachBO.khuvuc = dr["khuvuc"].ToString();
sachBO.ke = dr["ke"].ToString();
sachBO.ngan = dr["ngan"].ToString();
sachBO.maphieuthanhly = dr["maphieuthanhly"].ToString();
sachBO.lydothanhly = dr["lydothanhly"].ToString();
sachBO.Madausach = Convert.ToInt32(dr["madausach"].ToString());
sachBO.tacgiaColl = tgDAO.TimDSTacGia_Sach(sachBO.MaSach);// tất cả các tác giả
}
cnn.Close();
return sachBO;
}
public void ThemSach(int madausach)
{
SachBO sachBO = new SachBO();
sachBO = Tim1Sach(madausach);
//Thêm sách
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "";
query = "insert into Sach(MaNXB,TenSach,NamXuatBan,LanXuatBan,TriGia,HinhAnh,NgayNhap,TrangThai,Madausach)";
query += " values(@MaNXB,@TenSach,@NamXuatBan,@LanXuatBan,@TriGia,@HinhAnh,Getdate(),1,@madausach) ";
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Parameters.AddWithValue("@MaNXB", sachBO.MaNXB);
cmd.Parameters.AddWithValue("@TenSach", sachBO.TenSach);
cmd.Parameters.AddWithValue("@NamXuatBan", sachBO.namxuatban);
cmd.Parameters.AddWithValue("@LanXuatBan", sachBO.lanxuatban);
cmd.Parameters.AddWithValue("@TriGia", sachBO.trigia);
cmd.Parameters.AddWithValue("@HinhAnh", sachBO.hinhanh);
cmd.Parameters.AddWithValue("@madausach", madausach);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
//thêm ds tác giả của sách
SachCollection sachColl = new SachCollection();
sachColl = TimDSSach(madausach);
query =" IF @check not in (SELECT masach FROM Sach_TacGia)";
query += " INSERT INTO Sach_TacGia(masach,matg) VALUES(@masach,@matg)";
for (int i = 0; i < sachColl.Count; i++)
{
for (int j = 0; j < sachBO.tacgiaColl.Count; j++)
{
SqlCommand cmdTG = new SqlCommand(query, cnn);
cmdTG.Parameters.AddWithValue("@check", sachColl.Index(i).MaSach);
cmdTG.Parameters.AddWithValue("@masach", sachColl.Index(i).MaSach);
cmdTG.Parameters.AddWithValue("@matg", sachBO.tacgiaColl.Index(j).MaTG);
cnn.Open();
cmdTG.ExecuteNonQuery();
cnn.Close();
}
}
}
//XỬ LÝ SÁCH
public SachBO Tim1Sach(string masach)
{
SachBO sachBO = new SachBO();
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "SELECT top 1 * from sach where masach=@masach AND trangthai=1";
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Parameters.AddWithValue("@masach", masach);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
sachBO.MaSach = dr["masach"].ToString();
sachBO.MaNXB = dr["manxb"].ToString();
sachBO.MaLoai = dr["maloai"].ToString();
sachBO.MaCTPhanLoai = dr["mactphanloai"].ToString();
sachBO.TenSach = dr["tensach"].ToString();
sachBO.namxuatban = Convert.ToInt32(dr["namxuatban"].ToString());
sachBO.lanxuatban = Convert.ToInt32(dr["lanxuatban"].ToString());
sachBO.trigia = Convert.ToDecimal(dr["trigia"].ToString());
sachBO.hinhanh = dr["hinhanh"].ToString();
sachBO.muondemve = Convert.ToBoolean(dr["muondemve"].ToString());
sachBO.ngaynhap = dr["ngaynhap"].ToString();
sachBO.khuvuc = dr["khuvuc"].ToString();
sachBO.ke = dr["ke"].ToString();
sachBO.ngan = dr["ngan"].ToString();
sachBO.maphieuthanhly = dr["maphieuthanhly"].ToString();
sachBO.lydothanhly = dr["lydothanhly"].ToString();
sachBO.Madausach = Convert.ToInt32(dr["madausach"].ToString());
sachBO.tacgiaColl = tgDAO.TimDSTacGia_Sach(masach);// tất cả các tác giả
}
cnn.Close();
return sachBO;
}
protected void KiemTraButton_Click(object sender, EventArgs e)
{
string masach = MaSachKiemTraTextbox.Text;
//Kiểm tra sách đã bị báo là mất chưa
bool kt1 = phieumuonBUS.KiemTraSach_DaMat(masach);
if (kt1 == true)
{
ThongbaoSachLabel.Text = "Mã sách sai hoặc đã là mất! Xin kiểm tra lại";
AnhienSach(false);
ResetSach();
return;
}
//Kiểm tra sách có thuộc phiếu mượn (chưa đựơc trả) nào không
string maphieumuon = phieumuonBUS.KiemTraSach_DangMuon(masach);
if(maphieumuon=="")
{
ThongbaoSachLabel.Text = "Sách này chưa đựơc mượn";
AnhienSach(false);
ResetSach();
return;
}
AnhienSach(true);
//nạp thông tin sách
SachBO sachBO = new SachBO();
sachBO = sachBUS.Tim1Sach(masach);
MaSachLabel.Text = sachBO.MaSach;
TenSachLabel.Text = sachBO.TenSach;
//nạp thông tin phiếu mượn
PhieuMuonBO phieumuonBO= new PhieuMuonBO();
phieumuonBO=phieumuonBUS.Tim1PhieuMuon(maphieumuon);
MaPhieuMuonLabel.Text = phieumuonBO.MaPhieuMuon;
NgayMuonLabel.Text = phieumuonBO.NgayMuon;
NgayHetHanLabel.Text = phieumuonBO.NgayHetHan;
// GiaHanLabel.Text = phieumuonBO.GiaHan;
//nạp thông tin nhân viên
NhanVienBO nhanvienBO = new NhanVienBO();
nhanvienBO = nhanvienBUS.Tim1Nhanvien(phieumuonBO.MaNV);
TenNhanVienLabel.Text=nhanvienBO.TenNV;
MaNhanVienLabel.Text=nhanvienBO.MaNV;
//nạp thông tin độc giả
string madg = doctaichoBUS.Tim1DocGia_Luot(phieumuonBO.MaLuot);
DocGiaBO docgiaBO = new DocGiaBO();
docgiaBO = docgiaBUS.Tim1DocGia(madg);
DocGiaLabel.Text = docgiaBO.TenDocGia;
MaDocGiaLabel.Text = docgiaBO.MaDocGia;
//Kiểm tra sách đã hết hạn hay chưa
DateTime dt = new DateTime();
dt = DateTime.Now;
int hethan=0;
if (NgayHetHanLabel.Text != "")
{
DateTime ngayhethan=Convert.ToDateTime(nhanvienBUS.ChuyenNgayThang(NgayHetHanLabel.Text));
hethan =Convert.ToInt32((dt-ngayhethan).Days);
}
if (GiaHanLabel.Text != "")
{
DateTime ngaygiahan=Convert.ToDateTime(nhanvienBUS.ChuyenNgayThang(GiaHanLabel.Text));
hethan = Convert.ToInt32((dt-ngaygiahan).Days);
}
if(hethan>0)
ThongbaoSachLabel.Text="Sách đã hết hạn "+hethan+" ngày!";
}
public void ThemSach(SachBO sachBO, int soluong)
{
//Thêm sách
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "";
int madausach = TaoMaDauSach();
for (int i = 0; i < soluong; i++)
{
query = "insert into Sach(MaNXB,TenSach,NamXuatBan,LanXuatBan,TriGia,HinhAnh,NgayNhap,TrangThai,Madausach)";
query += " values(@MaNXB,@TenSach,@NamXuatBan,@LanXuatBan,@TriGia,@HinhAnh,@NgayNhap,1,@madausach) ";
SqlCommand cmd = new SqlCommand(query, cnn);
if (sachBO.MaNXB != "")
cmd.Parameters.AddWithValue("@MaNXB", sachBO.MaNXB);
else
cmd.Parameters.AddWithValue("@MaNXB", DBNull.Value);
cmd.Parameters.AddWithValue("@TenSach", sachBO.TenSach);
cmd.Parameters.AddWithValue("@NamXuatBan", sachBO.namxuatban);
cmd.Parameters.AddWithValue("@LanXuatBan", sachBO.lanxuatban);
cmd.Parameters.AddWithValue("@TriGia", sachBO.trigia);
cmd.Parameters.AddWithValue("@HinhAnh", sachBO.hinhanh);
cmd.Parameters.AddWithValue("@NgayNhap",sachBO.ngaynhap);
cmd.Parameters.AddWithValue("@madausach", madausach);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
//thêm ds tác giả của sách
SachCollection sachColl = new SachCollection();
sachColl=TimDSSach(madausach);
query = "INSERT into Sach_TacGia(masach,matg) VALUES(@masach,@matg)";
for (int i = 0; i < sachColl.Count; i++)
{
for (int j = 0; j < sachBO.tacgiaColl.Count; j++)
{
SqlCommand cmdTG = new SqlCommand(query, cnn);
cmdTG.Parameters.AddWithValue("@masach", sachColl.Index(i).MaSach);
cmdTG.Parameters.AddWithValue("@matg", sachBO.tacgiaColl.Index(j).MaTG);
cnn.Open();
cmdTG.ExecuteNonQuery();
cnn.Close();
}
}
}
protected void SachGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
ViewState["madausach"] = "";
if (e.CommandName == "chitiet")
{
ThongbaoLabel.Text = "";
ViewState["madausach"] = e.CommandArgument.ToString();
ChiTietPopup.Show();
//lấy tất cả sách có mã đầu sách là e.CommandArgument
int madausach = Convert.ToInt32(e.CommandArgument);
SachCollection sachColl = new SachCollection();
sachColl = sachBUS.TimDSSach(madausach);
//nạp vào ChiTietDaugridview
ChiTietDauSachGridView.DataSource = sachColl;
ChiTietDauSachGridView.DataBind();
//gán dữ liệu đầu sách điểh hình vào
HinhanhdausachImage.ImageUrl = sachColl.Index(0).hinhanh ;
TenDauSachLabel.Text = sachColl.Index(0).TenSach;
ChiTietDauSachLanXuatBanLabel.Text = sachColl.Index(0).lanxuatban.ToString();
ChiTietDauSachNgayNhapLabel.Text = sachColl.Index(0).ngaynhap.ToString();
ChiTietDauSachTriGiaLabel.Text = sachColl.Index(0).trigia.ToString();
NapDuLieu();
}
else if(e.CommandName=="xoa")
{
ThongbaoLabel.Text = "";
bool kq=sachBUS.XoaSach(Convert.ToInt32(e.CommandArgument.ToString()));
if(kq==false)
ThongbaoLabel.Text = "Không thể xóa đầu sách này được";
NapDuLieu();
}
else if (e.CommandName == "sua")
{
ThongbaoLabel.Text = "";
NapDSNXB();
NapDSTacGia();
ViewState["madausach"] = e.CommandArgument.ToString();
//nạp dữ liệu lên form sửa
SachBO sachBO = new SachBO();
sachBO = sachBUS.Tim1Sach(Convert.ToInt32(e.CommandArgument));
TenSachSuaTextBox.Text = sachBO.TenSach;
TacGiaCollection tacgiaColl = new TacGiaCollection();
tacgiaColl = sachBO.tacgiaColl;
if (tacgiaColl != null && tacgiaColl.Count != 0)
{
for (int i = 0; i < tacgiaColl.Count; i++)
{
for (int j = 0; j < TacGiaSuaListBox.Items.Count; j++)
{
if (tacgiaColl.Index(i).MaTG == TacGiaSuaListBox.Items[j].Value)
{
TacGiaSuaListBox.Items[j].Selected = true;
break;
}
}
}
}
NhaXuatBanSuaDropDown.SelectedValue = sachBO.MaNXB;
NamXuatBanSuaTextBox.Text = sachBO.namxuatban.ToString() ;
LanXuatBanSuaTextBox.Text = sachBO.lanxuatban.ToString();
TriGiaSuaTextBox.Text = sachBO.trigia.ToString();
NgayNhapSuaTextBox.Text = sachBO.ngaynhap.ToString();
SuaPopup.Show();
NapDuLieu();
}
}
protected void ChiTietGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "xoa")
{
int index = Convert.ToInt32(e.CommandArgument.ToString());
ChiTietPhieuMuon_TraCollection chitietphieuColl = new ChiTietPhieuMuon_TraCollection();
chitietphieuColl = Session["SachMuon"] as ChiTietPhieuMuon_TraCollection;
chitietphieuColl.Remove(index);
NapDuLieuVaoGrid();
}
else if(e.CommandName == "xem")
{
SachBO sachBO = new SachBO();
string masach=e.CommandArgument.ToString();
sachBO = sachBUS.Tim1Sach(masach);
if (sachBO != null && sachBO.TenSach != null)
{
MaSachCTLabel.Text = masach;
TenSachCTLabel.Text = sachBO.TenSach;
HinhAnhSachCTImage.ImageUrl = sachBO.hinhanh;
NhaXuatBanCTLabel.Text = nhaxuatbanBUS.Tim1NXB(sachBO.MaNXB).TenNXB;
LanXuatBanCTLabel.Text = sachBO.lanxuatban.ToString();
NamXuatBanCTLabel.Text = sachBO.namxuatban.ToString();
TacGiaCTListBox.DataSource = sachBO.tacgiaColl;
TacGiaCTListBox.DataTextField = "TenTG";
TacGiaCTListBox.DataValueField = "MaTG";
TacGiaCTListBox.DataBind();
TriGiaCTLabel.Text = sachBO.trigia.ToString() + " vnđ";
}
SachPopup.Show();
}
}
protected void MasachTextBox_TextChanged(object sender, EventArgs e)
{
SachBO sachBO = new SachBO();
sachBO = sachBUS.Tim1Sach(MasachTextBox.Text);
if (sachBO != null && sachBO.TenSach != null)
{
ThongbaoSachLabel.Text = "";
MasachmuonLabel.Text = MasachTextBox.Text;
TenSachLabel.Text = sachBO.TenSach;
HinhAnhSachImage.ImageUrl = sachBO.hinhanh;
NhaxuatbanLabel.Text = nhaxuatbanBUS.Tim1NXB(sachBO.MaNXB).TenNXB;
LanxuatbanLabel.Text = sachBO.lanxuatban.ToString();
NamXuatBanLabel.Text = sachBO.namxuatban.ToString();
TacGiaListBox.DataSource = sachBO.tacgiaColl;
TacGiaListBox.DataTextField = "TenTG";
TacGiaListBox.DataValueField = "MaTG";
TacGiaListBox.DataBind();
TriGiaLabel.Text = sachBO.trigia.ToString() + " vnđ";
TienthechanTextbox.Text = sachBO.trigia.ToString();
AnHienSach(true);
LuuButton.Focus();
}
else
{
ThongbaoSachLabel.Text = "Mã sách không hợp lệ";
ResetSach();
AnHienSach(false);
MasachTextBox.Focus();
}
}
protected void SachGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
ViewState["madausach"] = "";
if (e.CommandName == "phanloai")
{
ViewState["madausach"] = e.CommandArgument.ToString();
ChiTietPopup.Show();
//Nạp chitiết phân lọai
string maphanloai = PhanLoaiDropDown.SelectedValue;
CTPhanLoaiDropdown.DataSource = phanloaiBUS.TimDSCTPhanLoai(maphanloai);
CTPhanLoaiDropdown.DataValueField = "MaCTPhanLoai";
CTPhanLoaiDropdown.DataTextField = "TenCTPhanLoai";
CTPhanLoaiDropdown.DataBind();
//lấy tất cả sách có mã đầu sách là e.CommandArgument
int madausach = Convert.ToInt32(e.CommandArgument);
SachBO sachBO = new SachBO();
sachBO = sachBUS.Tim1Sach(madausach);
//gán dữ liệu đầu sách điểh hình vào
HinhanhdausachImage.ImageUrl = sachBO.hinhanh;
TenDauSachLabel.Text = sachBO.TenSach;
ChiTietDauSachLanXuatBanLabel.Text = sachBO.lanxuatban.ToString();
ChiTietDauSachNgayNhapLabel.Text = sachBO.ngaynhap.ToString();
ChiTietDauSachTriGiaLabel.Text = sachBO.trigia.ToString();
//gán dữ liệu phân lọai
if (sachBO.MaLoai != null && sachBO.MaLoai.ToString() != "")
LoaiSachLuuDropdown.SelectedValue = sachBO.MaLoai;
else
LoaiSachLuuDropdown.SelectedValue = "";
if (sachBO.MaCTPhanLoai != null&&sachBO.MaCTPhanLoai.ToString()!="")
{
PhanLoaiDropDown.SelectedValue = phanloaiBUS.Tim1PhanLoai_CT(sachBO.MaCTPhanLoai).MaPhanLoai;
string _maphanloai = PhanLoaiDropDown.SelectedValue;
NapCTPhanLoai(_maphanloai);
CTPhanLoaiDropdown.SelectedValue = sachBO.MaCTPhanLoai;
}
else
{
NapCTPhanLoai("");
PhanLoaiDropDown.SelectedValue = "";
CTPhanLoaiDropdown.SelectedValue = "";
}
if (sachBO.khuvuc != null && sachBO.khuvuc.ToString()!="")
KhuvucDropdown.SelectedValue = sachBO.khuvuc;
else
KhuvucDropdown.SelectedValue = "";
// if (sachBO.ke.ToString() != "")
KeTextbox.Text = sachBO.ke;
// if (sachBO.ngan.ToString() != "")
NganTextbox.Text = sachBO.ngan;
if (sachBO.muondemve == true)
MuondemveCheckbox.Checked = true;
else
MuondemveCheckbox.Checked = false;
NapDuLieu();
}
}
public bool PhanLoai(int madausach,string maloai,string mactphanloai,string khuvuc,string ke,string ngan,bool muondemve)
{
try
{
SachBO sachBO = new SachBO();
sachBO.Madausach = madausach;
sachBO.MaLoai = maloai;
sachBO.MaCTPhanLoai = mactphanloai;
sachBO.khuvuc = khuvuc;
sachBO.ke = ke;
sachBO.ngan = ngan;
sachBO.muondemve = muondemve;
sachDAO.PhanLoai(sachBO);
return true;
}
catch
{
return false;
}
}
public SachCollection TimSachDaMat(string thongtintim, int cachtim)
{
SachCollection sachColl = new SachCollection();
if (cachtim == 0)
{
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "SELECT masach,MaLoai,MaCTPhanLoai,MaNXB,TenSach,NamXuatBan,LanXuatBan,TriGia,TrangThai,MuonDemVe,HinhAnh,KhuVuc,Ke,Ngan";
query += ",convert(nvarchar(10),NgayNhap,103) ngaynhap,MaPhieuThanhLy, cast(LydoThanhLy as nvarchar(max)) lydothanhly,Madausach";
query += " FROM Sach WHERE";
query += " trangthai=0 AND tensach like N'%" + thongtintim + "%' order by ngaynhap desc";
SqlCommand cmd = new SqlCommand(query, cnn);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SachBO sachBO = new SachBO();
sachBO.MaSach=dr["masach"].ToString();
sachBO.MaNXB = dr["manxb"].ToString();
sachBO.MaLoai = dr["maloai"].ToString();
sachBO.MaCTPhanLoai = dr["mactphanloai"].ToString();
sachBO.TenSach = dr["tensach"].ToString();
sachBO.namxuatban = Convert.ToInt32(dr["namxuatban"].ToString());
sachBO.lanxuatban = Convert.ToInt32(dr["lanxuatban"].ToString());
sachBO.trigia = Convert.ToDecimal(dr["trigia"].ToString());
sachBO.hinhanh = dr["hinhanh"].ToString();
sachBO.muondemve = Convert.ToBoolean(dr["muondemve"].ToString());
sachBO.ngaynhap = dr["ngaynhap"].ToString();
sachBO.khuvuc = dr["khuvuc"].ToString();
sachBO.ke = dr["ke"].ToString();
sachBO.ngan = dr["ngan"].ToString();
sachBO.maphieuthanhly = dr["maphieuthanhly"].ToString();
sachBO.lydothanhly = dr["lydothanhly"].ToString();
sachBO.Madausach = Convert.ToInt32(dr["madausach"].ToString());
sachBO.tacgiaColl = tgDAO.TimDSTacGia_Sach(thongtintim);// tất cả các tác giả
sachColl.Add(sachBO);
}
cnn.Close();
}
else
{
SachBO sachBO = new SachBO();
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "SELECT top 1 convert(nvarchar(10),NgayNhap, * from sach where masach=@masach AND trangthai=0";
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Parameters.AddWithValue("@masach", thongtintim);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
sachBO.MaSach = dr["masach"].ToString();
sachBO.MaNXB = dr["manxb"].ToString();
sachBO.MaLoai = dr["maloai"].ToString();
sachBO.MaCTPhanLoai = dr["mactphanloai"].ToString();
sachBO.TenSach = dr["tensach"].ToString();
sachBO.namxuatban = Convert.ToInt32(dr["namxuatban"].ToString());
sachBO.lanxuatban = Convert.ToInt32(dr["lanxuatban"].ToString());
sachBO.trigia = Convert.ToDecimal(dr["trigia"].ToString());
sachBO.hinhanh = dr["hinhanh"].ToString();
sachBO.muondemve = Convert.ToBoolean(dr["muondemve"].ToString());
sachBO.ngaynhap = dr["ngaynhap"].ToString();
sachBO.khuvuc = dr["khuvuc"].ToString();
sachBO.ke = dr["ke"].ToString();
sachBO.ngan = dr["ngan"].ToString();
sachBO.maphieuthanhly = dr["maphieuthanhly"].ToString();
sachBO.lydothanhly = dr["lydothanhly"].ToString();
sachBO.Madausach = Convert.ToInt32(dr["madausach"].ToString());
sachBO.tacgiaColl = tgDAO.TimDSTacGia_Sach(thongtintim);// tất cả các tác giả
}
cnn.Close();
sachColl.Add(sachBO);
}
return sachColl;
}
public void Add(SachBO sachBO)
{
List.Add(sachBO);
}