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