/// <summary> /// 获取购物车列表 /// </summary> /// <param name="userId"></param> /// <param name="salesTerritory"></param> /// <param name="language"></param> /// <returns></returns> public int GetMiniShoppingCart(int userId, int salesTerritory, int language) { string sql = @" SELECT count(1) from ShoppingCart sc (NOLOCK) INNER JOIN skuinfo s ON s.Sku=sc.Sku AND s.[Status] = 3 INNER JOIN productInfo p (NOLOCK) ON p.id=s.SpuId AND p.SalesTerritory=sc.CountryId WHERE p.LanguageVersion=@LanguageVersion AND sc.UserId=@UserId AND (sc.CountryId=@CountryId or sc.CountryId=3 )"; try { var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@UserId", userId); parameters.Append("@CountryId", salesTerritory); parameters.Append("@LanguageVersion", language); return(DbSFO2ORead.ExecuteSqlScalar <int>(sql, parameters)); } catch (Exception ex) { LogHelper.Error(ex); return(0); } }
/// <summary> /// 获取汇率信息 /// </summary> /// <param name="pattern"></param> /// <returns></returns> public decimal GetExchangeRateByPattern(int pattern = 1) { string sql = @"SELECT TOP 1 [Rate] FROM [ExchangeRates] WHERE [Status]=1 AND [Pattern]=@Pattern ORDER BY [UpdateTime],[CreateTime] DESC "; try { var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@Pattern", pattern); return(DbSFO2ORead.ExecuteSqlScalar <decimal>(sql, parameters)); } catch (Exception ex) { LogHelper.Error(ex); throw ex; } }
/// <summary> /// sku是否已存在购物车中 /// </summary> /// <param name="userId"></param> /// <param name="sku"></param> /// <param name="salesTerritory"></param> /// <returns></returns> public int IsExistSku(int userId, string sku, int salesTerritory) { try { string sql = " SELECT Quantity FROM ShoppingCart AS sc WHERE sc.UserId=@UserId AND (sc.CountryId=@CountryId or sc.CountryId=3) and sku=@sku"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@UserId", userId); parameters.Append("@CountryId", salesTerritory); parameters.Append("@sku", sku); return(DbSFO2ORead.ExecuteSqlScalar <int>(sql, parameters)); } catch (Exception ex) { LogHelper.Error(ex); throw ex; } }
public int GetTeamUserId(string OrderCode, string TeamCode) { try { string sql = @" SELECT oi.UserId FROM OrderInfo AS oi INNER JOIN TeamInfo AS ti ON ti.TeamCode = oi.TeamCode WHERE oi.TeamCode=@TeamCode AND oi.OrderCode=@OrderCode"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@TeamCode", TeamCode); parameters.Append("@OrderCode", OrderCode); return(DbSFO2ORead.ExecuteSqlScalar <int>(sql, parameters)); } catch (Exception ex) { LogHelper.Error(ex); throw ex; } }
/// <summary> /// 取品牌的在售SPU数量 /// </summary> /// <param name="brandId">品牌id</param> /// <param name="language">语言版本</param> /// <param name="deliveryRegion">销售区域</param> /// <returns></returns> public int GetBrandSaleSpuCount(int brandId, int language, int deliveryRegion) { string sql = @";with spucnt(spu,spuCount) As ( select p.spu,sum(s.ForOrderQty) from ProductInfo p inner join Stock s on s.Spu=p.Spu inner join SkuInfo sk on sk.SpuId=p.Id and sk.Sku=s.Sku where p.LanguageVersion=@Language and sk.[Status] = 3 and (p.SalesTerritory =@Region or p.SalesTerritory=3) group by p.Spu ) select count(p.spu) from ProductInfo p inner join spucnt st on st.spu=p.spu where p.LanguageVersion=@Language and (p.SalesTerritory =@Region or p.SalesTerritory=3) and st.spuCount-p.MinForOrder>0 and BrandId=@BrandId;"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@BrandId", brandId); parameters.Append("@Language", language); parameters.Append("@Region", deliveryRegion); return(DbSFO2ORead.ExecuteSqlScalar <int>(sql, parameters)); }