public static DonDatHang LayMaDh(string username)
{
var db = new SmartShopConnectionDB();
var madh = db.SingleOrDefault <DonDatHang>("SELECT TOP 1 * FROM DonDatHang WHERE Username=@0 ORDER BY MaDH DESC", username);
return(madh);
}
public static void Xoa(string id, Product sp)
{
var db = new SmartShopConnectionDB();
db.Execute("DELETE FROM Comment WHERE MaSP=@0", id);
db.Execute("DELETE FROM Product WHERE MaSP=@0", id);
}
public static Comment ChiTiet(string idUser)
{
var db = new SmartShopConnectionDB();
var cmt = db.SingleOrDefault <Comment>("SELECT TOP 1 * FROM [DAn_Web2].[dbo].[Comment] WHERE UserCmt=@0 ORDER BY IDCmt DESC", idUser);
return(cmt);
}
public static Loai ChiTiet(string id)
{
var db = new SmartShopConnectionDB();
var l = db.SingleOrDefault <Loai>("SELECT * FROM loai WHERE MaLoai=@0", id);
l.dsCL = ChungLoaiBus.DanhSach();
return(l);
}
public static void PhanQuyen(string us)
{
var db = new SmartShopConnectionDB();
var user = db.SingleOrDefault <viewUser>("SELECT * FROM viewUser where UserName=@0", us);
AspNetUserRole pq = new AspNetUserRole();
pq.RoleId = "12629e90-6c15-424b-abaa-164250682d15";
pq.UserId = user.UserId;
db.Insert("AspNetUserRoles", pq);
}
public static Product ChiTiet(string id)
{
var db = new SmartShopConnectionDB();
var sp = db.SingleOrDefault <Product>("SELECT * FROM Product WHERE MaSP=@0", id);
sp.dsLoai = LoaiBus.DanhSach();
sp.LuotXem = sp.LuotXem + 1;
db.Update("Product", "MaSP", sp);
return(sp);
}
public static void Xoa(string us)
{
var db = new SmartShopConnectionDB();
var user = db.SingleOrDefault <viewUser>("SELECT * FROM viewUser where UserName=@0", us);
string id = user.UserId;
db.Execute("DELETE FROM AspNetUserRoles WHERE UserId=@0", id);
db.Execute("DELETE FROM AspNetUsers WHERE UserName=@0", us);
db.Execute("DELETE FROM Accounts WHERE UserName=@0", us);
}
public static void Them(DonDatHang dh, List <ChiTietDH> ctdh, string username)
{
var db = new SmartShopConnectionDB();
db.Insert("DonDatHang", "MaDH", dh);
var madh = db.SingleOrDefault <DonDatHang>("SELECT TOP 1 * FROM DonDatHang WHERE Username=@0 ORDER BY MaDH DESC", username);
foreach (var item in ctdh)
{
item.MaDH = madh.MaDH;
db.Insert("ChiTietDH", "MaDH", item);
}
}
public static IEnumerable <DonDatHang> DanhSach()
{
var db = new SmartShopConnectionDB();
var dsDH = db.Query <DonDatHang>("select * from DonDatHang");
List <DonDatHang> model = new List <DonDatHang>();
/* foreach (var item in dsCL)
* {
* viewMenu clvm = new viewMenu();
* clvm.MaCL = item.MaCL;
* clvm.TenCL = item.TenCL;
* clvm.dsLoai = db.Query<Loai>("select * from Loai where MaCL=@0", item.MaCL);
* model.Add(clvm);
* }*/
return(db.Query <DonDatHang>("SELECT * FROM DonDatHang"));
}
//
// GET: /Cart/
public ActionResult Index()
{
var db = new SmartShopConnectionDB();
List <Cart> ds_SP = new List <Cart>();
List <string> sp = new List <string>();
sp = (List <string>)Session["sp_gh"];
if (sp != null)
{
foreach (var item in sp)
{
Cart sp_gh = new Cart();
sp_gh.dsSP = db.Query <Product>("select * from Product where MaSP=@0", item.ToString());
ds_SP.Add(sp_gh);
}
}
return(View(ds_SP));
}
public static IEnumerable <viewMenu> Menu()
{
var db = new SmartShopConnectionDB();
//return db.Query<viewMenu>("SELECT * FROM viewMenu");
var dsCL = db.Query <viewMenu>("select chungloai.MaCL,chungloai.TenCL from chungloai");
List <viewMenu> model = new List <viewMenu>();
foreach (var item in dsCL)
{
viewMenu clvm = new viewMenu();
clvm.MaCL = item.MaCL;
clvm.TenCL = item.TenCL;
clvm.dsLoai = db.Query <Loai>("select * from Loai where MaCL=@0", item.MaCL);
model.Add(clvm);
}
return(model);
}
public JsonResult AddToCart(string id)
{
var response = new { Code = 1, Msg = "Fail", sluong = @Session["sluong"], ttien = @Session["TongTien"] };
var db = new SmartShopConnectionDB();
Product sp = db.Single <Product>("select * from Product where MaSP = @0", id);
if (sp != null)
{
DHangModels objCart = (DHangModels)Session["Cart"];
if (objCart == null)
{
objCart = new DHangModels();
}
ShoppingCartItem item = new ShoppingCartItem()
{
MaSP = sp.MaSP,
TenSP = sp.TenSP,
Hinh = sp.Hinh,
Gia = Int32.Parse(sp.Gia),
SoLuong = 1,
TongCong = Int32.Parse(sp.Gia),
};
objCart.AddToCart(item);
Session["Cart"] = objCart;
ShoppingCartModels model = new ShoppingCartModels();
model.Cart = (DHangModels)Session["Cart"];
int ii = 0;
int tongtien = 0;
foreach (var item2 in model.Cart.ListItem)
{
tongtien = tongtien + (int)item2.TongCong;
ii++;
}
Session["sluong"] = ii;
Session["TongTien"] = tongtien.ToString("N0");
response = new { Code = 0, Msg = "Success", sluong = @Session["sluong"], ttien = @Session["TongTien"] };
}
return(Json(response));
}
public static IEnumerable <DonDatHang> DanhSach()
{
var db = new SmartShopConnectionDB();
var dsDH = db.Query <DonDatHang>("select * from DonDatHang");
List <DonDatHang> model = new List <DonDatHang>();
foreach (var item in dsDH)
{
DonDatHang dh = new DonDatHang();
dh.MaDH = item.MaDH;
dh.Username = item.Username;
dh.NgayDatHang = item.NgayDatHang;
dh.HoTen = item.HoTen;
dh.GhiChu = item.GhiChu;
dh.DiaChi = item.DiaChi;
dh.Email = item.Email;
dh.SDT = item.SDT;
dh.TinhTrang = item.TinhTrang;
dh.Tongtien = item.Tongtien;
dh.DsctDH = db.Query <View_CTDH>("select * from ChiTietDH where MaDH=@0", item.MaDH);
model.Add(dh);
}
return(model);
}
public static IEnumerable <DonDatHang> DsDHUs(string id)
{
var db = new SmartShopConnectionDB();
var dsDH = db.Query <DonDatHang>("select * from DonDatHang where Username=@0 ORDER BY NgayDatHang DESC", id);
List <DonDatHang> model = new List <DonDatHang>();
foreach (var item in dsDH)
{
DonDatHang dh = new DonDatHang();
dh.MaDH = item.MaDH;
dh.Username = item.Username;
dh.NgayDatHang = item.NgayDatHang;
dh.HoTen = item.HoTen;
dh.GhiChu = item.GhiChu;
dh.DiaChi = item.DiaChi;
dh.Email = item.Email;
dh.SDT = item.SDT;
dh.TinhTrang = item.TinhTrang;
dh.Tongtien = item.Tongtien;
dh.DsctDH = db.Query <View_CTDH>("select * from [DAn_Web2].[dbo].[View_CTDH] where MaDH=@0", item.MaDH);
model.Add(dh);
}
return(model);
}
public static void Sua(Comment cmt)
{
var db = new SmartShopConnectionDB();
db.Update("Comment", "IDCmt", cmt);
}
public static void Xoa(int id)
{
var db = new SmartShopConnectionDB();
db.Execute("DELETE FROM Comment WHERE IDCmt=@0", id);
}
public static void Them(Comment cmt)
{
var db = new SmartShopConnectionDB();
db.Insert(cmt);
}
public static void Sua(Loai l)
{
var db = new SmartShopConnectionDB();
db.Update("Loai", "MaLoai", l);
}
public static void Them(Loai l)
{
var db = new SmartShopConnectionDB();
db.Insert("Loai", "MaLoai", l);
}
public static Page <Product> TimKiem(int?page, string key)
{
var db = new SmartShopConnectionDB();
return(db.Page <Product>(page.Value, 9, "select * from Product where TenSP like '%" + key + "%' order by NgayDangSP DESC"));
}
public static Page <Product> SpTheoLoai(int?page, string maloai)
{
var db = new SmartShopConnectionDB();
return(db.Page <Product>(page.Value, 9, "SELECT * FROM Product WHERE LoaiSP=@0 ORDER BY NgayDangSP DESC", maloai));
}
public static Page <Product> SpTheoCL(int?page, int?macl)
{
var db = new SmartShopConnectionDB();
return(db.Page <Product>(page.Value, 9, "SELECT Product.*,ChungLoai.MaCL FROM Product,Loai,ChungLoai WHERE Product.LoaiSP=Loai.MaLoai AND Loai.MaCL=ChungLoai.MaCL AND ChungLoai.MaCL=@0 ORDER BY NgayDangSP DESC", macl));
}
public static Page <Product> DanhSachHOT(int?page)
{
var db = new SmartShopConnectionDB();
return(db.Page <Product>(page.Value, 9, "SELECT * FROM Product ORDER BY LuotXem DESC"));
}
public static Page <viewAdProduct> AdDanhSach(int?page)
{
var db = new SmartShopConnectionDB();
return(db.Page <viewAdProduct>(page.Value, 9, "SELECT * FROM viewAdProduct ORDER BY NgayDangSP DESC"));
}
public static IEnumerable <viewLoai> DanhSachView()
{
var db = new SmartShopConnectionDB();
return(db.Query <viewLoai>("SELECT * FROM viewLoai"));
}
public static void Sua(Product sp)
{
var db = new SmartShopConnectionDB();
db.Update("Product", "MaSP", sp);
}
public static void Xoa(string id, Loai l)
{
var db = new SmartShopConnectionDB();
db.Execute("DELETE FROM Loai WHERE MaLoai=@0", id);
}
public static Page <Comment> CmtSp(string masp, int?page)
{
var db = new SmartShopConnectionDB();
return(db.Page <Comment>(page.Value, 5, "SELECT * FROM Comment where MaSP=@0 ORDER BY IDCmt DESC", masp));
}
public static void Them(Product sp)
{
var db = new SmartShopConnectionDB();
db.Insert("Product", "MaSP", sp);
}
public static Account Admin(string us)
{
var db = new SmartShopConnectionDB();
return(db.SingleOrDefault <Account>("SELECT R.Name FROM Accounts A,AspNetUsers Us,AspNetUserRoles UR,AspNetRoles R WHERE A.UserName=@0 AND A.UserName=Us.UserName AND Us.Id=UR.UserId AND UR.RoleId=R.Id", us));
}
