public UserAddressDto GetDetail(int userId, int id) { var sql = @"SELECT ua.Id,ua.UserId, ua.Receiver, ua.Mobile, ua.Province, ua.City, ua.District, ua.Address, ua.PostCode, ua.IsDefault FROM UserAddress ua WHERE ua.Id=@id AND ua.UserId=@userId"; return(DbManage.Query <UserAddressDto>(sql, new { userId, id }).FirstOrDefault()); }
public IEnumerable <OrderItemDto> GetProductsByTradeNo(string tradeNo) { var sql = @"SELECT oi.ProductId as ProductId, oi.Num FROM OrderItem oi INNER JOIN [Order] o on oi.orderId=o.id WHERE o.tradeNo = @tradeNo;"; return(DbManage.Query <OrderItemDto>(sql, new { tradeNo })); }
public IEnumerable <UserAddressDto> List(int userId) { var sql = @"SELECT ua.Id,ua.UserId, ua.Receiver, ua.Mobile, ua.Province, ua.City, ua.District, ua.Address, ua.PostCode, ua.IsDefault FROM UserAddress ua WHERE ua.IsDeleted=0 AND ua.Status = 1 AND ua.UserId=@userId;"; return(DbManage.Query <UserAddressDto>(sql, new { userId })); }
public OrderStatViewDto GetStatCountInfo(int userId) { string sql = @"SELECT (SELECT COUNT(1) FROM [Order] WHERE UserId=2 AND IsDeleted=0 AND Status=0) AS NotPayCount, (SELECT COUNT(1) FROM [Order] WHERE UserId=@userId AND IsDeleted=0 AND Status=1) AS NotDeliverCount, (SELECT COUNT(1) FROM [Order] WHERE UserId=@userId AND IsDeleted=0 AND Status=2) AS DeliveredCount, (SELECT COUNT(1) FROM [Order] WHERE UserId=@userId AND IsDeleted=0 AND Status=3) AS FinishedCount" ; return(DbManage.Query <OrderStatViewDto>(sql, new { userId }).FirstOrDefault()); }
public UserAddressDto GetDefault(int userId) { var sql = @"SELECT TOP 1 ua.Id,ua.UserId, ua.Receiver, ua.Mobile, ua.Province, ua.City, ua.District, ua.Address, ua.PostCode, ua.IsDefault FROM UserAddress ua WHERE ua.IsDefault=1 AND ua.UserId=@userId ORDER BY UpdateTime DESC;"; return(DbManage.Query <UserAddressDto>(sql, new { userId }).FirstOrDefault()); }
public PayOrderDto GetPayOrderInfoByTradeNo(string tradeNo) { string sql = @"SELECT o.TradeNo, o.Payment as TotalFee, u.OpenId from [Order] o LEFT JOIN [dbo].[User] u on u.Id=o.UserId WHERE o.TradeNo=@tradeNo;"; return(DbManage.Query <PayOrderDto>(sql, new { tradeNo }).FirstOrDefault()); }
public CouponDto GetDetail(int id, int userId) { var sql = @"SELECT TOP 1 c.Id, c.Title, c.StartTime, c.EndTime, c.MinFee, c.RebateFee, c.MaxOwnLimit, c.Status, CASE WHEN DATEDIFF(d, GETDATE(), c.EndTime) >=0 THEN 1 ELSE 0 END as CanUse, (SELECT count(1) FROM UserCoupon uc WHERE uc.CouponId=c.Id AND uc.Status=1 AND IsDeleted=0 AND IsUsed=0 AND uc.UserId=@userId ) as OwnCount FROM Coupon c WHERE c.Id =@id;"; return(DbManage.Query <CouponDto>(sql, new { id, userId }).FirstOrDefault()); }
public IEnumerable <ProductSkuDto> GetProductSku(int productId) { string sql = @"SELECT f.Id as FeatureId,v.Id as ValueId,f.Name as FeatureName,v.[Value] as FeatureValue, sku.Num , ISNULL(sku.Price, p.Price) AS Price from dbo.ProductSku sku JOIN dbo.Product p ON sku.ProductId = p.Id JOIN dbo.FeatureValue v ON sku.FeatureValueId = v.Id JOIN dbo.Feature f ON v.FeatureId = f.Id WHERE sku.ProductId = @productId AND sku.IsDeleted=0 AND sku.Status =1 AND v.IsDeleted=0 AND v.Status=1 AND f.IsDeleted=0 AND f.Status =1 AND p.Status=1 AND p.IsDeleted=0 " ; return(DbManage.Query <ProductSkuDto>(sql, new { productId })); }
public IEnumerable <ProductFileDto> GetProductFiles(int productId, int fileType) { string sql = "select Url,Type from dbo.[File] where IsDeleted = 0 and Status = 1 and ProductId = @productId "; if (fileType > 0) { sql += "and Type = @fileType"; } return(DbManage.Query <ProductFileDto>(sql, new { productId, fileType })); }
public IEnumerable <FeatureWithValueDto> GetProductFeatureWithValues(int productId) { string sql = @"SELECT p.Id as ProductId, f.Id as FeatureId, f.Name, f.DisplayName as DisplayName, fv.Id as ValueId, fv.[Value] as [Value] FROM FeatureValue fv INNER JOIN Feature f on f.Id = fv.FeatureId INNER JOIN ProductSku sku on sku.FeatureValueId = fv.Id INNER JOIN Product p on p.Id = sku.ProductId WHERE p.Id = @productId and p.Status = 1"; return(DbManage.Query <FeatureWithValueDto>(sql, new { productId })); }
public IEnumerable <ShoppingCartDetailDto> GetItemsByCartIds(int[] cartIdArr) { string sql = @" SELECT c.id as cartid, p.Id as ProductId, p.CoverUrl, p.Name,c.Num, ISNULL(s.Price, p.Price) AS Price, f.[Name] as FeatureName,s.FeatureValueId,v.[value] as FeatureValue FROM dbo.ShoppingCart c JOIN dbo.ProductSku s on c.ProductSkuId = s.Id JOIN dbo.Product p ON s.ProductId = p.Id JOIN dbo.FeatureValue v ON s.FeatureValueId = v.Id JOIN dbo.Feature f ON v.FeatureId = f.Id WHERE c.IsDeleted=0 AND c.id in @cartIdArr ORDER BY c.Sort,c.CreateTime DESC" ; return(DbManage.Query <ShoppingCartDetailDto>(sql, new { cartIdArr })); }
public UserCouponDto GetDefault(int userId, int totalFee) { var sql = @"SELECT TOP 1 uc.Id, u.Id as UserId, c.Id as CouponId, c.Title, c.StartTime, c.EndTime, c.MinFee, c.RebateFee, c.MaxOwnLimit, c.Status FROM UserCoupon uc INNER JOIN Coupon c on c.Id=uc.CouponId INNER JOIN [User] u on u.Id=uc.UserId WHERE uc.isUsed=0 AND uc.IsDeleted=0 and u.Id = @userId AND c.MinFee<= @totalFee AND DATEDIFF(d, GETDATE(), c.EndTime) >=0 ORDER BY c.RebateFee DESC, uc.CreateTime ASC;"; return(DbManage.Query <UserCouponDto>(sql, new { userId, totalFee }).FirstOrDefault()); }
public OrderDetailViewDto GetDetail(int userId, int orderId) { string sql = @"SELECT o.Id, o.TradeNo, o.TotalFee, o.PostFee, o.Payment, o.Status, o.UserMessage, o.UserCouponId, c.Title as CouponTitle, c.RebateFee as CouponFee, os.Name as Receiver, os.Mobile, os.Province, os.City, os.District, os.Address, os.PostCode from [Order] o LEFT JOIN OrderShipping os on os.OrderId=o.Id LEFT JOIN UserCoupon uc on uc.Id=o.UserCouponId LEFT JOIN Coupon c on c.Id=uc.CouponId WHERE o.UserId=@userId AND o.Id = @orderId;"; return(DbManage.Query <OrderDetailViewDto>(sql, new { userId, orderId }).FirstOrDefault()); }
public IEnumerable <UserCouponDto> GetNoUseList(int userId, int totalFee) { var sql = @" SELECT uc.Id, u.Id as UserId, c.Id as CouponId, c.Title, c.StartTime, c.EndTime, c.MinFee, c.RebateFee, c.MaxOwnLimit, '未使用' as status, (case WHEN c.MinFee <= @totalFee THEN 1 ELSE 0 END) as CanUse FROM UserCoupon uc INNER JOIN Coupon c on c.Id=uc.CouponId AND uc.Status=1 INNER JOIN [User] u on u.Id=uc.UserId WHERE uc.Status=1 AND uc.IsDeleted=0 AND u.Id = @userId and uc.isUsed = 0 and DATEDIFF(d, GETDATE(), c.EndTime) >=0 ORDER BY uc.CreateTime ASC;"; return(DbManage.Query <UserCouponDto>(sql, new { userId, totalFee })); }
public IEnumerable <OrderItemDto> GetOrderItems(int orderId) { string sql = @" SELECT Name,FeatureName,FeatureValue,Num,Price,TotalFee,ImgUrl from dbo.OrderItem WHERE orderid = @orderId "; return(DbManage.Query <OrderItemDto>(sql, new { orderId })); }
public IEnumerable <OrderItemViewDto> GetAllByOrderId(int orderId) { string sql = @"SELECT * FROM [OrderItem] WHERE OrderId=@orderId ORDER BY Id DESC"; return(DbManage.Query <OrderItemViewDto>(sql, new { orderId })); }
public UserDto GetUserByToken(string token) { var sql = @"select id,nickname,avatarurl,token from dbo.[User] where token=@token;"; return(DbManage.Query <UserDto>(sql, new { token }).FirstOrDefault()); }
public UserDto GetUserByOpenId(string openId) { var sql = @"select id,nickname,avatarurl,token from dbo.[User] where openid=@openId"; return(DbManage.Query <UserDto>(sql, new { openId }).FirstOrDefault()); }
public IEnumerable <FeatureWithValueDto> GetFeatureWithValues() { string sql = @" SELECT f.Id as FeatureId,f.Name,v.Id AS ValueId,v.[value] FROM dbo.feature f JOIN dbo.FeatureValue v ON f.Id = v.FeatureId WHERE f.status =1 AND f.isdeleted=0 AND v.status =1 AND v.isdeleted=0"; return(DbManage.Query <FeatureWithValueDto>(sql)); }
public IEnumerable <int> GetFeatureValueIds(int productId) { string sql = @"SELECT FeatureValueId from dbo.ProductSku s WHERE s.ProductId = @productId AND Status = 1 AND IsDeleted = 0 "; return(DbManage.Query <int>(sql, new { productId })); }
public Admin GetByUserName(string username) { var sql = @"select top 1 * from [dbo].[Admin] where LogName=@userName"; return(DbManage.Query <Admin>(sql, new { username = username }).FirstOrDefault()); }
public OrderShippingDto GetOrderShipping(int orderId) { string sql = @" SELECT top 1 * FROM dbo.OrderShipping WHERE OrderId = @orderId "; return(DbManage.Query <OrderShippingDto>(sql, new { orderId }).FirstOrDefault()); }
public OrderItemViewDto GetDefaultProduct(int orderId) { string sql = @" SELECT TOP 1 * FROM [OrderItem] oi WHERE oi.OrderId =@orderId ORDER BY oi.Id DESC;"; return(DbManage.Query <OrderItemViewDto>(sql, new { orderId }).FirstOrDefault()); }