/// <summary> /// 根据Id获取用户 /// </summary> /// <param name="Id"></param> /// <returns></returns> public UserInfo GetUserInfoById(int Id) { string sql = $"select * from UserInfo where UserInfoId = {Id}"; var list = DapperHelper.GetList <UserInfo>(sql); return(list[0]); }
/// <summary> /// 添加订单返回订单主键 /// </summary> /// <param name="order"></param> /// <returns></returns> public int AddOrderInfo(OrderInfo order) { string sql = $"insert into OrderInfo values({order.GoodsLookIdOut},{order.UserInfoIdOut},'{order.OrderInfoSumPrice}',{order.OrderInfoNumber},1);select @@identity as NewId"; var list = DapperHelper.GetList <OrderInfo>(sql); return(list[0].NewId); }
/// <summary> /// 获取随机优惠券 /// </summary> /// <returns></returns> public DisCountInfo GetDisCountInfos(int ss) { string sql = $"select * from DisCountInfo"; var list = DapperHelper.GetList <DisCountInfo>(sql); Random random = new Random(); if (ss == 1) { var s = random.Next(1, 5); return(list[s]); } if (ss == 2) { var s = random.Next(6, 10); return(list[s]); } if (ss == 3) { var s = random.Next(10, 15); return(list[s]); } if (ss == 9999) { return(list[15]); } return(list[0]); }
/// <summary> /// 获取买家的余额 /// </summary> /// <param name="UserId"></param> /// <returns></returns> public int GetUserMoney(int UserId) { string sql = $"select * from UserInfo where UserInfoId={UserId}"; var list = DapperHelper.GetList <UserInfo>(sql); return(list[0].UserInfoMoney); }
/// <summary> /// 获取该用户的所有优惠券 /// </summary> /// <param name="UserId"></param> /// <returns></returns> public List <DisCountInfo> GetDisCountInfosById(int UserId) { var d = DateTime.Now; string sql = $"select c.DisCountInfoPrice,a.StartTime,a.EndTime from DisCountAndUser a join UserInfo b on a.UserIdOut = b.UserInfoId join DisCountInfo c on a.DisCountId = c.DisCountInfoId where b.UserInfoId ={UserId} and a.EndTime >='{d}'"; return(DapperHelper.GetList <DisCountInfo>(sql)); }
/// <summary> /// 根据具体商品获取商家 /// </summary> /// <returns></returns> public int GetShopUserId(int ShopUserId) { string sql = $"select d.UserInfoId from GoodsLook a join GoodsInfo b on a.GoodsIdOut = b.GoodsInfoId join ShopInfo c on c.ShopInfoId = b.ShopIdOut join UserInfo d on c.UserInfoIdOut = d.UserInfoId where a.GoodsLookId = {ShopUserId}"; var l = DapperHelper.GetList <UserInfo>(sql); return(l[0].UserInfoId); }
/// <summary> /// 获取所有品牌 /// </summary> /// <returns></returns> public List <ClothesBrand> GetClothesBrands(int Id) { string sql = "select * from ClothesBrand where 1=1"; if (Id != 0) { sql += $" and ClothesBrandId='{Id}'"; } return(DapperHelper.GetList <ClothesBrand>(sql)); }
/// <summary> /// 获取选中的总价格 /// </summary> /// <param name="ids"></param> /// <param name="UserId"></param> /// <returns></returns> public TempCar GetGoodsCarPrice(string ids, int UserId) { TempCar t = new TempCar(); string sql = $"select a.GoodsNumber*d.GoodsInfoPrice as SumPrice,a.GoodsNumber from GoodsCarInfo a join GoodsLook b on a.GoodsLookIdOut = b.GoodsLookId join UserInfo c on a.UserInfoIdOut = c.UserInfoId join GoodsInfo d on b.GoodsIdOut = d.GoodsInfoId where c.UserInfoId = {UserId} and a.GoodsCarInfoId in({ids})"; var list = DapperHelper.GetList <GoodsCarInfo>(sql); foreach (var item in list) { t.SumPrice += item.SumPrice; t.SumCount += item.GoodsNumber; } return(t); }
//调用存储过程 public static int ExecTran_Pay(int UserId, int ShopUserId, int Price) { using (SqlConnection conn = new SqlConnection(StrConn)) { SqlTransaction tran; //定义事务 conn.Open(); tran = conn.BeginTransaction(); //事务开始 var red = DapperHelper.GetList <UserInfo>($"select * from UserInfo where UserInfoId={UserId}"); var UserMoney = red[0].UserInfoMoney; SqlCommand comm2 = new SqlCommand($"update UserInfo set UserInfoMoney = UserInfoMoney-{Price} where UserInfoId ={UserId}"); comm2.Connection = conn; comm2.Transaction = tran; comm2.ExecuteNonQuery(); SqlCommand comm = new SqlCommand($"update UserInfo set UserInfoMoney = UserInfoMoney+{Price} where UserInfoId ={ShopUserId}"); comm.Connection = conn; comm.Transaction = tran; comm.ExecuteNonQuery(); tran.Commit(); //事务提交 return(1); } }
/// <summary> /// 登录 /// </summary> /// <param name="name"></param> /// <param name="pass"></param> /// <returns></returns> public List <UserInfo> GetUserInfos(string name, string pass) { string sql = $"select * from UserInfo where UserInfoName = '{name}' and UserInfoPass='******'"; return(DapperHelper.GetList <UserInfo>(sql)); }
/// <summary> /// 联动复选框查询类型 /// </summary> /// <param name="pid"></param> /// <returns></returns> public List <ClothesType> GetClothesType(int pid) { string sql = $"select * from ClothesType where ClothesTypePid={pid}"; return(DapperHelper.GetList <ClothesType>(sql)); }
/// <summary> /// 根据Id查询出订单支付 /// </summary> /// <param name="OrderId"></param> /// <returns></returns> public List <OrderInfo> GetPayMoneyOrderInfo(int OrderId) { string sql = $"select c.*,f.AddressInfoName,a.OrderInfoId,b.GoodsImg,d.GoodsInfoDesc,b.GoodsColor,b.GoodsSize,d.GoodsInfoPrice,a.OrderInfoNumber,a.OrderInfoSumPrice,e.UserInfoIdOut as shangjia,a.UserInfoIdOut from OrderInfo a join GoodsLook b on a.GoodsLookIdOut = b.GoodsLookId join UserInfo c on a.UserInfoIdOut = c.UserInfoId join GoodsInfo d on b.GoodsIdOut = d.GoodsInfoId join ShopInfo e on d.ShopIdOut = e.ShopInfoId join AddressInfo f on c.UserInfoId = f.UserInfoIdOut where a.OrderInfoId = {OrderId}"; return(DapperHelper.GetList <OrderInfo>(sql)); }
/// <summary> /// 查询所有订单 /// </summary> /// <param name="UserId"></param> /// <returns></returns> public List <OrderInfo> GetOrderInfos(int UserId, int State) { string sql = $"select e.*,b.GoodsLookId,c.GoodsInfoId,b.GoodsImg,b.GoodsLookId,c.GoodsInfoDesc,b.GoodsColor,b.GoodsSize,a.OrderInfoSumPrice,a.OrderInfoState,a.OrderInfoId from OrderInfo a join GoodsLook b on a.GoodsLookIdOut = b.GoodsLookId join GoodsInfo c on b.GoodsIdOut = c.GoodsInfoId join UserInfo e on a.UserInfoIdOut = e.UserInfoId where e.UserInfoId ={UserId} and a.OrderInfoState = {State} "; return(DapperHelper.GetList <OrderInfo>(sql)); }
/// <summary> /// 商品详情尺码 /// </summary> /// <param name="id"></param> /// <returns></returns> public List <GoodsLook> GetGoodsLooksSize(int id) { string sql = $"select distinct(GoodsSize) from GoodsLook where GoodsIdOut={id}"; return(DapperHelper.GetList <GoodsLook>(sql)); }
/// <summary> /// 根据主键查询地址 /// </summary> /// <param name="AddressId"></param> /// <returns></returns> public List <AddressInfo> GetAddressInfosByAddressId(int AddressId) { string sql = $"select * from AddressInfo where AddressInfoId = {AddressId}"; return(DapperHelper.GetList <AddressInfo>(sql)); }
/// <summary> /// 获取收货地址 /// </summary> /// <returns></returns> public List <AddressInfo> GetAddressInfos(int userInfoId) { string sql = $"select * from AddressInfo where UserInfoIdOut = {userInfoId}"; return(DapperHelper.GetList <AddressInfo>(sql)); }
public List <UserInfo> GetUserNameInfos(string name) { string sql = $"select * from UserInfo where UserInfoName = '{name}'"; return(DapperHelper.GetList <UserInfo>(sql)); }
/// <summary> /// 查询所有购物车 /// </summary> /// <returns></returns> public List <GoodsCarInfo> GetGoodsCarInfos() { string sql = $"select * from GoodsInfo"; return(DapperHelper.GetList <GoodsCarInfo>(sql)); }
/// <summary> /// 商品详情库存 /// </summary> /// <param name="id"></param> /// <returns></returns> public List <GoodsLook> GetGoodsLooksKC(int id, string color, string size) { string sql = $"select * from GoodsLook where GoodsIdOut={id} and GoodsColor='{color}' and GoodsSize='{size}'"; return(DapperHelper.GetList <GoodsLook>(sql)); }
/// <summary> /// 查询所有活动 /// </summary> /// <returns></returns> public List <ActibityInfo> GetActibityInfos() { string sql = $"select * from ActibityInfo where ActibityInfoState=1"; return(DapperHelper.GetList <ActibityInfo>(sql)); }
/// <summary> /// 获取所有收藏 /// </summary> /// <param name="UserId"></param> /// <returns></returns> public List <GoodsAddInfoAndUserInfoAndGoodsInfo> GetGoodsAddInfos(int UserId) { string sql = $"select * from GoodsAddInfo a join UserInfo b on a.UserInfoIdOut = b.UserInfoId join GoodsInfo c on a.GoodsInfoIdOut = c.GoodsInfoId where b.UserInfoId ={UserId}"; return(DapperHelper.GetList <GoodsAddInfoAndUserInfoAndGoodsInfo>(sql)); }
/// <summary> /// 商品详情图片 /// </summary> /// <param name="id"></param> /// <returns></returns> public List <GoodsDescImg> GetGoodsImgDesc(int GoodsId) { string sql = $"select * from GoodsDescImg a join GoodsInfo b on a.GoodsIdOut = b.GoodsInfoId where b.GoodsInfoId = {GoodsId}"; return(DapperHelper.GetList <GoodsDescImg>(sql)); }
/// <summary> /// 联查所有购物车 /// </summary> /// <returns></returns> public List <GoodsCarInfo> GetShopCarAndGoods(int UserId) { string sql = $" select d.GoodsInfoId,a.GoodsCarInfoId,c.GoodsSize,c.GoodsColor,d.GoodsInfoName,c.GoodsImg,d.GoodsInfoDesc,d.GoodsInfoPrice,a.GoodsNumber,b.UserInfoId,b.UserInfoName,c.GoodsLookId from GoodsCarInfo a join UserInfo b on a.UserInfoIdOut = b.UserInfoId join GoodsLook c on a.GoodsLookIdOut = c.GoodsLookId join GoodsInfo d on c.GoodsIdOut = d.GoodsInfoId where b.UserInfoId = {UserId}"; return(DapperHelper.GetList <GoodsCarInfo>(sql)); }
/// <summary> /// 查询销售量最高的商品 /// </summary> /// <returns></returns> public List <GoodsInfo> GetGoodsInfoTop() { string sql = $"select top 4 * from GoodsInfo order by GoodsInfoCount desc"; return(DapperHelper.GetList <GoodsInfo>(sql)); }
/// <summary> /// 获取所有尺码 /// </summary> /// <returns></returns> public List <SizeInfo> GetSizeInfos() { string sql = $"select * from SizeInfo"; return(DapperHelper.GetList <SizeInfo>(sql)); }
/// <summary> /// 查询商品 /// </summary> /// <param name="id"></param> /// <returns></returns> public List <GoodsInfo> GetGoodsInfos(int id) { string sql = $"select * from GoodsInfo where GoodsInfoId={id}"; return(DapperHelper.GetList <GoodsInfo>(sql)); }
/// <summary> /// 获取所有秒杀 /// </summary> /// <returns></returns> public List <KillInfo> GetKillInfos() { string sql = "select * from KillInfo k join GoodsLook g on k.GoodsInfoIdOut = g.GoodsLookId join GoodsInfo c on g.GoodsIdOut = c.GoodsInfoId"; return(DapperHelper.GetList <KillInfo>(sql)); }
/// <summary> /// 获取地址name /// </summary> /// <param name="pid"></param> /// <returns></returns> public List <PosstionInfo> GetPosstionInfosName(int pid) { string sql = $"select * from PosstionInfoId where Pid={pid}"; return(DapperHelper.GetList <PosstionInfo>(sql)); }
/// <summary> /// 获取物流信息 /// </summary> /// <param name="OrderId"></param> /// <returns></returns> public List <CompanyInfo> GetCompanyInfos(int OrderId) { string sql = $"select * from CompanyInfo where OrderInfoIdOut = {OrderId}"; return(DapperHelper.GetList <CompanyInfo>(sql)); }
/// <summary> /// 模糊查询类型 /// </summary> /// <returns></returns> public List <ClothesType> GetClothesTypesByMasterId(int Id) { string sql = $"select * from ClothesType where ClothesTypeId ={Id}"; return(DapperHelper.GetList <ClothesType>(sql)); }