public int InsertOrderItem(OrderItemDto dto)
{
//string sql = @" INSERT INTO [dbo].[OrderItem]( [OrderId], [ProductId], [FeatureName],[FeatureValue], [Num], [Name], [TotalFee], [Price], [Sort], [Status], [Creator], [CreateTime], [IsDeleted] ) Select @OrderId, s.ProductId, f.Name , v.Value,@Num,p.Name,@TotalFee,@Price, 0, 1, NULL, @date, 0 from dbo.[Product] p join dbo.[ProductSku] s on p.id = s.Productid JOIN dbo.[FeatureValue] v on s.FeatureValueId = v.Id JOIN dbo.[Feature] f ON f.id = v.FeatureId Where p.Status =1 and Isdeleted = 0 ";
string sql = @" INSERT INTO [dbo].[OrderItem]( [OrderId], [ProductId], [FeatureName],[FeatureValue], [Num], [Name], [TotalFee], [ImgUrl], [Price], [Sort], [Status], [Creator], [CreateTime], [IsDeleted] ) select @OrderId, @ProductId, @FeatureName , @FeatureValue,@Num, @Name,@TotalFee, p.CoverUrl, @Price, 0, 1, NULL, @date, 0 from dbo.[Product] p where p.Id=@ProductId;";
return(DbManage.Execute(sql, new { OrderId = dto.OrderId, ProductId = dto.ProductId, FeatureName = dto.FeatureName, FeatureValue = dto.FeatureValue, Num = dto.Num, Name = dto.Name, TotalFee = dto.TotalFee, Price = dto.Price, date = DateTime.Now }));
}
public bool SetDefault(int userId, int id)
{
var sql = @"UPDATE UserAddress SET IsDefault=0 WHERE UserId=@userId AND IsDefault=1;
UPDATE UserAddress SET IsDefault=1 WHERE UserId=@userId AND Id=@id;";
return(DbManage.Execute(sql, new { userId, id }) > 0);
}
public int InsertOrderShipping(OrderShipping model)
{
string sql = @" INSERT INTO [dbo].[OrderShipping](OrderId, Name, Mobile, Province, City, District, Address,
PostCode, Sort, Status, Creator, CreateTime, IsDeleted) values( @OrderId, @Receiver, @Mobile,
@Province, @City, @District, @Address, @PostCode, 0, 1, 'admin', GETDATE(), 0) " ;
return(DbManage.Execute(sql, new { OrderId = model.OrderId, Receiver = model.Name, Mobile = model.Mobile, Province = model.Province, City = model.City, District = model.District, Address = model.Address, PostCode = model.PostCode }));
}
public bool AddToCart(int userId, int productId, int featureValueId, int num)
{
var sql = @"DECLARE @@skuId int
SELECT top 1 @@skuId = id from ProductSku where ProductId =@productId and FeatureValueId = @featureValueId
INSERT INTO ShoppingCart(ProductSkuId, num, Status, CreateTime, UpdateTime, IsDeleted, UserId)
values(@@skuId, @num, 1, GETDATE(), GETDATE(), 0, @userId);";
return(DbManage.Execute(sql, new { userId, productId, featureValueId, num }) > 0);
}
public int CheckParamsValid(GenerateOrderDto dto)
{
var procParams = new DynamicParameters();
procParams.Add("@UserId", dto.UserId);
procParams.Add("@TotalFee", dto.TotalFee);
procParams.Add("@Payment", dto.Payment);
procParams.Add("@UserCouponId", dto.UserCouponId);
procParams.Add("@PostFee", dto.PostFee);
procParams.Add("@ROut", dbType: DbType.Int32, direction: ParameterDirection.Output);
DbManage.Execute("[dbo].[Order_ParamsCheck]", procParams, CommandType.StoredProcedure);
return(procParams.Get <int>("@ROut"));
}
public bool UpdateToken(string token, string newToken, DateTime expireTime)
{
var sql = @"update dbo.[User] set token=@newToken, tokenExpireTime=@expireTime where token=@token;";
return(DbManage.Execute(sql, new { token, newToken, expireTime }) > 0);
}
public bool UpdateNum(int userId, int cartId, int num)
{
var sql = @"UPDATE ShoppingCart SET Num=@num WHERE Id=@cartId AND UserId=@userId;";
return(DbManage.Execute(sql, new { userId, cartId, num }) > 0);
}
public bool DeleteBatch(int userId, int[] ids)
{
var sql = @"UPDATE ShoppingCart SET IsDeleted = 1 WHERE Id in @ids AND UserId=@userId;";
return(DbManage.Execute(sql, new { userId, ids }) > 0);
}
public void DeleteProductSku(int productId)
{
string sql = @"Delete From dbo.ProductSku WHERE ProductId = @productId ";
DbManage.Execute(sql, new { productId });
}
public bool AddAmount(int productId, int num)
{
var sql = @"Update Product Set StoreAmount=StoreAmount+@num where Id=@productId;";
return(DbManage.Execute(sql, new { productId, num }) > 0);
}
public void DeleteFilesOfProduct(int productId)
{
string sql = @" DELETE FROM dbo.[file] where productId =@productId ";
DbManage.Execute(sql, new { productId });
}
public bool Cancel(long caseId)
{
var sql = @"delete from dbo.Recommend where caseId = @caseId ";
return(DbManage.Execute(sql, new { caseId }) > 0);
}
public bool DeleteShoppingCart(int[] cartIdArr)
{
string sql = " UPDATE dbo.[ShoppingCart] SET IsDeleted = 1 WHERE Id in @cartIdArr";
return(DbManage.Execute(sql, new { cartIdArr }) > 0);
}
public bool UpdateUserCoupon(int userCouponId)
{
string sql = " UPDATE dbo.[UserCoupon] SET IsUsed = 1 WHERE Id = @userCouponId AND IsUsed = 0";
return(DbManage.Execute(sql, new { userCouponId }) == 1);
}
public bool UpdateOrderStatus(string tradeNo, int status)
{
string sql = " UPDATE dbo.[Order] SET Status = @status WHERE TradeNo = @tradeNo";
return(DbManage.Execute(sql, new { tradeNo, status }) == 1);
}
public bool AddUserCoupon(int id, int userId)
{
var sql = @"insert into [dbo].[UserCoupon] (userid,couponid, status, createtime, updatetime, isdeleted, isUsed) values(@userId, @id, 1, getdate(), getdate(), 0, 0);";
return(DbManage.Execute(sql, new { id, userId }) > 0);
}