public void NapChiTietSach(int madausach)
{
SachCollection sachColl = new SachCollection();
sachColl = sachBUS.TimDSSach(madausach);
//nạp vào ChiTietDaugridview
ChiTietDauSachGridView.DataSource = sachColl;
ChiTietDauSachGridView.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
SachCollection sachColl = new SachCollection();
sachColl = sachBUS.TimDSSach("");
int soluong=16;
if(sachColl.Count<=16)
soluong=sachColl.Count;
SachCollection _sachColl = new SachCollection();
for(int i=0;i<soluong;i++)
{
_sachColl.Add(sachColl.Index(i));
}
SachListView.DataSource =_sachColl ;
SachListView.DataBind();
}
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 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();
}
}
}
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;
}
//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 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();
}
}
}
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();
}
}
}
public int TaoMaDauSach()
{
SachCollection sachColl = new SachCollection();
SqlConnection cnn = new SqlConnection(cnnstr);
string query = "select MAX(madausach) from sach";
SqlCommand cmd = new SqlCommand(query, cnn);
cnn.Open();
object res= cmd.ExecuteScalar();
cnn.Close();
if (res.ToString()=="")
return 1;
return Convert.ToInt32(res)+1;
}
protected void InToanBoButton_Click(object sender, EventArgs e)
{
int madausach = Convert.ToInt32(ViewState["madausach"].ToString());
SachCollection sachColl = new SachCollection();
sachColl = sachBUS.TimDSSach(madausach);
StringBuilder str = new StringBuilder();
for (int i = 0; i < sachColl.Count; i++)
{
str.Append(sachColl.Index(i).MaSach);
str.Append("\r\n");
}
Xuatfile(str);
}
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();
}
}
