public List <sanphamDAL> getSanPhamTonKho()
{
List <sanphamDAL> list = new List <sanphamDAL>();
SqlConnection cn = cnn.connectToSql();
cn.Open();
string sql = "select * from san_pham order by soluong desc";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
sanphamDAL ds = new sanphamDAL();
ds.id_sanpham = rd.GetInt32(0);
ds.ten_sanpham = rd.GetString(1);
ds.soluong = rd.GetInt32(2);
ds.hinhanh = rd.GetString(3);
ds.id_danhmuc = rd.GetInt32(4);
ds.tt_chitiet = rd.GetString(5);
ds.ngaydang = rd.GetDateTime(6);
ds.gia = rd.GetDecimal(7);
ds.khuyenmai = rd.GetBoolean(8);
list.Add(ds);
}
return(list);
cmd.Dispose();
cn.Close();
}
// tim kiem
public List <sanphamDAL> timkiem(string column, string value)
{
List <sanphamDAL> list = new List <sanphamDAL>();
SqlConnection cn = cnn.connectToSql();
cn.Open();
string sql = "";
if (column == "Tất cả")
{
sql = "SELECT san_pham.id_sanpham, san_pham.ten_sanpham, san_pham.hinhanh, san_pham.gia, danhmuc.id_danhmuc AS iddanhmuc, san_pham.khuyenmai FROM danhmuc INNER JOIN san_pham ON danhmuc.id_danhmuc = san_pham.id_danhmuc WHERE (san_pham.ten_sanpham LIKE N'%" + value + "%')";
}
else
{
sql = "SELECT san_pham.id_sanpham, san_pham.ten_sanpham, san_pham.hinhanh, san_pham.gia, danhmuc.id_danhmuc AS iddanhmuc, san_pham.khuyenmai FROM danhmuc INNER JOIN san_pham ON danhmuc.id_danhmuc = san_pham.id_danhmuc WHERE (danhmuc.ten_danhmuc = N'" + column + "') AND (san_pham.ten_sanpham LIKE N'%" + value + "%')";
}
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
sanphamDAL ds = new sanphamDAL();
ds.id_sanpham = rd.GetInt32(0);
ds.ten_sanpham = rd.GetString(1);
ds.hinhanh = rd.GetString(2);
ds.gia = rd.GetDecimal(3);
ds.id_danhmuc = rd.GetInt32(4);
ds.khuyenmai = rd.GetBoolean(5);
list.Add(ds);
}
return(list);
cmd.Dispose();
cn.Close();
}
// lay san pham theo danh muc co phan trang
public List <sanphamDAL> getSanPhambyIdP(int id, int row, int hide)
{
List <sanphamDAL> list = new List <sanphamDAL>();
SqlConnection cn = cnn.connectToSql();
cn.Open();
string sql = "select top " + row + " * from san_pham where id_danhmuc=" + id + " AND id_sanpham not in (select top " + hide + " id_sanpham from san_pham where id_danhmuc=" + id + " order by id_sanpham DESC ) order by id_sanpham DESC";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
sanphamDAL ds = new sanphamDAL();
ds.id_sanpham = rd.GetInt32(0);
ds.ten_sanpham = rd.GetString(1);
ds.soluong = rd.GetInt32(2);
ds.hinhanh = rd.GetString(3);
ds.id_danhmuc = rd.GetInt32(4);
ds.tt_chitiet = rd.GetString(5);
ds.ngaydang = rd.GetDateTime(6);
ds.gia = rd.GetDecimal(7);
ds.khuyenmai = rd.GetBoolean(8);
list.Add(ds);
}
return(list);
cmd.Dispose();
cn.Close();
}
public List <sanphamDAL> getSanPhamP(int row, int hide, int danhmuc, string noidung, int view)
{
List <sanphamDAL> list = new List <sanphamDAL>();
SqlConnection cn = cnn.connectToSql();
cn.Open();
string sql = "";
if (view != -1)
{
sql = "select top " + row + "* from san_pham where id_danhmuc=" + view + " AND id_sanpham not in (select top " + hide + " id_sanpham from san_pham where id_danhmuc=" + view + " order by id_sanpham DESC ) order by id_sanpham DESC";
}
else if (danhmuc == 0 && noidung != "")
{
sql = "select top " + row + "* from san_pham where ten_sanpham like N'%" + noidung + "%' AND id_sanpham not in (select top " + hide + " id_sanpham from san_pham where ten_sanpham like N'%" + noidung + "%' order by id_sanpham DESC ) order by id_sanpham DESC";
}
else if (danhmuc != -1 && noidung != "")
{
sql = "select top " + row + "* from san_pham where id_danhmuc=" + danhmuc + " AND ten_sanpham like N'%" + noidung + "%' AND id_sanpham not in (select top " + hide + " id_sanpham from san_pham where id_danhmuc=" + danhmuc + " AND ten_sanpham like N'%" + noidung + "%' order by id_sanpham DESC ) order by id_sanpham DESC";
}
else
{
sql = "select top " + row + "* from san_pham where ten_sanpham like N'%" + noidung + "%' AND id_sanpham not in (select top " + hide + " id_sanpham from san_pham where ten_sanpham like N'%" + noidung + "%' order by id_sanpham DESC ) order by id_sanpham DESC";
}
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
sanphamDAL ds = new sanphamDAL();
ds.id_sanpham = rd.GetInt32(0);
ds.ten_sanpham = rd.GetString(1);
ds.soluong = rd.GetInt32(2);
ds.hinhanh = rd.GetString(3);
ds.id_danhmuc = rd.GetInt32(4);
ds.tt_chitiet = rd.GetString(5);
ds.ngaydang = rd.GetDateTime(6);
ds.gia = rd.GetDecimal(7);
ds.khuyenmai = rd.GetBoolean(8);
list.Add(ds);
}
return(list);
cmd.Dispose();
cn.Close();
}
// load sanpham khuyen mai:
public List <sanphamDAL> getSPkhuyenmai()
{
List <sanphamDAL> list = new List <sanphamDAL>();
SqlConnection cn = cnn.connectToSql();
cn.Open();
string sql = "SELECT id_sanpham, ten_sanpham, hinhanh FROM san_pham WHERE khuyenmai = 'true' order by id_sanpham desc";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
sanphamDAL ds = new sanphamDAL();
ds.id_sanpham = rd.GetInt32(0);
ds.ten_sanpham = rd.GetString(1);
ds.hinhanh = rd.GetString(2);
list.Add(ds);
}
return(list);
cmd.Dispose();
cn.Close();
}
