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