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