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(); } }