public static IEnumerable <sanpham> ListSPLQ(int id)
{
using (var db = new BabyShopConnectionDB())
{
return(db.Query <sanpham>("SELECT top 3 * FROM sanpham WHERE MaSanPham <> @0 AND MaLoaiSanPham = (Select MaLoaiSanPham from sanpham where MaSanPham = @0) ORDER BY SoLuongBan DESC ", id));
}
}
public static PetaPoco.Page <sanpham> TimKiem(string key, string order, int pgNumb = 1, int itemsPerPg = 20)
{
var db = new BabyShopConnectionDB();
var sql = string.Format("select * from sanpham where khoa=0 {0} order by ngaycapnhat desc {1}", string.IsNullOrEmpty(key) ? "" : " and " + key, string.IsNullOrEmpty(order) ? "" : ", " + order);
return(db.Page <sanpham>(pgNumb, itemsPerPg, sql));
}
public static AspNetUser layThongTin(string id)
{
using (var db = new BabyShopConnectionDB())
{
return(db.SingleOrDefault <AspNetUser>("Select FullName , NgaySinh , GenDer From AspNetUsers Where Id = @0", id));
}
}
public static IEnumerable <BinhLuan> DanhSachBL(int MaSanPham)
{
using (var db = new BabyShopConnectionDB())
{
return(db.Query <BinhLuan>("select * from BinhLuan where MaSanPham = @0 order by Ngay desc", MaSanPham));
}
}
public static nhasanxuat ChiTietNSX(int id)
{
using (var db = new BabyShopConnectionDB())
{
return(db.Query <nhasanxuat>("SELECT * FROM nhasanxuat WHERE MaNhaSanXuat = @0", id).FirstOrDefault());
}
}
public static IEnumerable <v_GioHang> DanhSach(string maTaiKhoan)
{
using (var db = new BabyShopConnectionDB())
{
return(db.Query <v_GioHang>("select * from v_GioHang where MaTaiKhoan = @0", maTaiKhoan));
}
}
public static IEnumerable <sanpham> GetTop10()
{
using (var db = new BabyShopConnectionDB())
{
return(db.Query <sanpham>("Select * from SanPham where MaSanPham in (select top 10 MaSanPham from chitietdondathang Group by MaSanPham ORDER BY SUM(SoLuong) DESC)"));
}
}
public static IEnumerable <BabyShopConnection.sanpham> TimKiem(string q)
{
var db = new BabyShopConnectionDB();
var a = db.Query <BabyShopConnection.sanpham>("select * from sanpham where TenSanPham like '%" + q + "%'");
return(a);
}
public static IEnumerable <loaisanpham> HienThiLoaiSanPham()
{
using (var db = new BabyShopConnectionDB())
{
return(db.Query <loaisanpham>("SELECT * FROM loaisanpham WHERE BiXoa = 0"));
}
}
public static void CapNhat(int id, int soLuong)
{
using (var db = new BabyShopConnectionDB())
{
db.Execute("update GioHang set [SoLuong] = @0 where id = @1", soLuong, id);
}
}
public static void Them(int MaSanPham, string TenTaiKhoan, string MaTaiKhoan, string NoiDung)
{
using (var db = new BabyShopConnectionDB())
{
var ds = "INSERT INTO [dbo].[BinhLuan]([MaSanPham],[TenTaiKhoan],[MaTaiKhoan],[NoiDung])VALUES(@0,@1,@2,@3)";
db.Execute(ds, MaSanPham, TenTaiKhoan, MaTaiKhoan, NoiDung);
}
}
public static Page <sanpham> HienThiDanhSachSanPham(int pageNumber, int itemPerpage)
{
var db = new BabyShopConnectionDB();
return(db.Page <sanpham>(pageNumber, itemPerpage, "select sp.*, lsp.TenLoaiSanPham as TenLoai, nsx.TenNhaSanXuat as TenNSX "
+ "from sanpham sp join loaisanpham lsp on sp.MaLoaiSanPham = lsp.MaLoaiSanPham join nhasanxuat nsx on sp.MaNhaSanXuat = nsx.MaNhaSanXuat"
+ "WHERE sp.BiXoa <> 1 ORDER BY sp.SoLuongBan DESC"));
}
public static sanpham ChiTietSanPham(int id)
{
var db = new BabyShopConnectionDB();
return(db.SingleOrDefault <sanpham>(" SELECT s.MaSanPham, s.TenSanPham, s.GiaBan, s.SoLuongTon, s.SoLuongXem, "
+ "s.HinhAnh, s.MoTa, n.TenNhaSanXuat,l.TenLoaiSanPham, s.MaLoaiSanPham, s.XuatXu "
+ "FROM SanPham s, NhaSanXuat n, LoaiSanPham l "
+ "WHERE s.BiXoa = 0 AND s.MaNhaSanXuat = n.MaNhaSanXuat "
+ "AND s.MaLoaiSanPham = l.MaLoaiSanPham AND s.MaSanPham = @0", id));
}
public static void Them(int maSanPham, String maTaiKhoan)
{
using (var db = new BabyShopConnectionDB())
{
GioHang gioHang = new GioHang()
{
MaSanPham = maSanPham,
MaTaiKhoan = maTaiKhoan,
SoLuong = 1
};
db.Insert(gioHang);
}
}
public static IEnumerable <nhasanxuat> HienThiDanhSachNSX()
{
var db = new BabyShopConnectionDB();
return(db.Query <nhasanxuat>("SELECT * FROM nhasanxuat WHERE BiXoa = 0"));
}
public static void DeleteSanPham(int id, sanpham sp)
{
var db = new BabyShopConnectionDB();
db.Delete <sanpham>("WHERE MaSanPham = @0", id);
}
public static void ThemNhaSanXuat(nhasanxuat nsx)
{
var db = new BabyShopConnectionDB();
db.Insert(nsx);
}
public static void ThemSanPham(sanpham sp)
{
var db = new BabyShopConnectionDB();
db.Insert(sp);
}
public static void EditSanPham(int id, sanpham sp)
{
var db = new BabyShopConnectionDB();
db.Update <sanpham>("SET TenSanPham=@0, MoTa=@1, XuatXu=@2, MaNhaSanXuat=@3, GiaBan=@4, SoLuongBan=@5, SoLuongTon=@6, MaLoaiSanPham=@7, HinhAnh=@8 where MaSanPham=@9", sp.TenSanPham, sp.MoTa, sp.XuatXu, sp.MaNhaSanXuat, sp.GiaBan, sp.SoLuongBan, sp.SoLuongTon, sp.MaLoaiSanPham, sp.HinhAnh, id);
}
public static IEnumerable <sanpham> DanhSachSanPham()
{
var db = new BabyShopConnectionDB();
return(db.Query <sanpham>("SELECT * FROM SanPham WHERE BiXoa <> 1 ORDER BY MaSanPham DESC"));
}
public static void ThemLoaiSanPham(loaisanpham lsp)
{
var db = new BabyShopConnectionDB();
db.Insert(lsp);
}
public static nhasanxuat ChiTietNhaSanXuat(int id)
{
var db = new BabyShopConnectionDB();
return(db.SingleOrDefault <nhasanxuat>("SELECT * FROM nhasanxuat WHERE MaNhaSanXuat = @0", id));
}
public static void EditNhaSanXuat(int id, nhasanxuat nsx)
{
var db = new BabyShopConnectionDB();
db.Update <nhasanxuat>("SET TenNhaSanXuat=@0, LoGoURL=@1, BiXoa=@2 where MaNhaSanXuat=@3", nsx.TenNhaSanXuat, nsx.LoGoURL, nsx.BiXoa, id);
}
public static Page <nhasanxuat> HienThiDanhNhaSanXuat(int pageNumber, int itemPerpage)
{
var db = new BabyShopConnectionDB();
return(db.Page <nhasanxuat>(pageNumber, itemPerpage, "SELECT * FROM NhaSanXuat WHERE BiXoa <> 1 "));
}
public static Page <loaisanpham> HienThiDanhSachLoaiSanPham(int pageNumber, int itemPerpage)
{
var db = new BabyShopConnectionDB();
return(db.Page <loaisanpham>(pageNumber, itemPerpage, "SELECT * FROM LoaiSanPham WHERE BiXoa <> 1 "));
}
public static IEnumerable <loaisanpham> DanhSach()
{
var dsLoaiSanPham = new BabyShopConnectionDB();
return(dsLoaiSanPham.Query <loaisanpham>("select * from loaisanpham where BiXoa=0 "));
}
public static IEnumerable <nhasanxuat> DanhSach()
{
var dsLoaiSanPham = new BabyShopConnectionDB();
return(dsLoaiSanPham.Query <nhasanxuat>("select * from NhaSanXuat where BiXoa=0 "));
}
public static sanpham ChiTietSanPham(int id)
{
var db = new BabyShopConnectionDB();
return(db.SingleOrDefault <sanpham>("SELECT * FROM SanPham WHERE MaSanPham = @0", id));
}
public static void EditLoaiSanPham(int id, loaisanpham lsp)
{
var db = new BabyShopConnectionDB();
db.Update <loaisanpham>("SET TenLoaiSanPham=@0, BiXoa = @1 where MaLoaiSanPham=@2", lsp.TenLoaiSanPham, lsp.BiXoa, id);
}
public static IEnumerable <sanpham> ChiTiet(int id)
{
var db = new BabyShopConnectionDB();
return(db.Query <sanpham>("select * from sanpham where MaLoaiSanPham = @0", id));
}
