public IList <BulletinEntity> GetBulletinEntities(int top = 0)
{
string sql;
if (top == 0)
{
sql = @"SELECT [Id]
,[Title]
,[Content]
,[CreateTime]
,[LinkUrl]
,[Sort]
,[Status]
FROM [Bulletin] WHERE Status=1 order by Sort";
}
else
{
sql = @"SELECT TOP ({0}) [Id] ,[Title]
,[Content]
,[CreateTime]
,[LinkUrl]
,[Sort]
,[Status]
FROM [Bulletin] WHERE Status=1 order by Sort";
sql = string.Format(sql, top);
}
return(DbSFO2OMain.ExecuteSqlList <BulletinEntity>(sql));
}
///
/// /// <summary>
/// 我的酒豆详情
/// </summary>
public List <MyHL> HLDetail(int userId, int typeName, int PageSize, int PageIndex)
{
string type = null;
int startIndex = (PageIndex - 1) * PageSize + 1;
int endIndex = PageIndex * PageSize;
if (typeName == 0)
{
type = " and hll.Direction in (1,2)";
}
else if (typeName == 1)
{
type = " and hll.Direction = 1";
}
else if (typeName == 2)
{
type = " and hll.Direction = 2";
}
string sql = @"with sputb
AS
(SELECT hll.UserId,hll.TradeCode,hll.[Description],hll.Direction,hll.ChangedHuoLi,hll.CurrentHuoLi,CONVERT(VARCHAR(23),hll.CreateTime,120) as CreateTime,ROW_NUMBER() OVER (ORDER BY hll.Id DESC ) as rindex
FROM HuoLiLog AS hll WHERE hll.UserId=@userId " + type + ") select *,(select count(1) from sputb) as TotalRecord from sputb where rindex between @StartIndex and @EndIndex";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@userId", userId);
parameters.Append("@StartIndex", startIndex);
parameters.Append("@EndIndex", endIndex);
var list = DbSFO2OMain.ExecuteSqlList <MyHL>(sql, parameters);
return(list.ToList());
}
public List <PromotionSkuListModel> GetPromotionSkuInfo(List <string> skus)
{
var sql = @"SELECT s.spu,s.Sku,p.Name,s.MainValue,s.SubValue,s.Price
,pImg.ImagePath
FROM ProductInfo(NOLOCK) p
INNER JOIN SkuInfo(NOLOCK) s
ON p.Id=s.SpuId AND p.LanguageVersion=2 AND s.Status=3
LEFT JOIN ProductImage(NOLOCK) pImg
ON pImg.Spu = p.Spu AND pImg.SortValue=1
WHERE s.sku IN ({0})";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
StringBuilder sb = new StringBuilder(1000);
int index = 0;
foreach (var sku in skus)
{
sb.Append("@sku" + index + ",");
parameters.Append("@sku" + index, sku);
index++;
}
return(DbSFO2OMain.ExecuteSqlList <PromotionSkuListModel>(String.Format(sql, sb.Remove(sb.Length - 1, 1).ToString()), parameters).ToList());
}
/// <summary>
/// 获取申诉的订单
/// </summary>
/// <param name="userId"></param>
/// <param name="country"></param>
/// <param name="language"></param>
/// <param name="orderCode"></param>
/// <returns></returns>
public IList <RefundInfoModel> GetMyOrderInfoAndRefund(string orderCode)
{
string sql =
@"SELECT
RefundCode,
OrderCode,
UserId,
RefundType,
RefundReason,
RefundDescription,
RefundStatus,
TotalAmount,
RMBTotalAmount,
DutyAmount,
RmbDutyAmount,
IsReturnDuty,
IsQualityProblem,
Commission,
CollectionCode,
CreateTime,
AuditTime,
TobePickUpTime,
PickupTime,
CreateBy,
Auditor,
Pickupper,
ExpressCompany,
ExpressList,
NoPassReason,
CancelReason,
ImagePath,
ExchangeRate,
SupplierId,
RegionCode,
ProductStatus,
CompletionTime FROM
RefundOrderInfo WHERE OrderCode=@orderCode ";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@orderCode", orderCode);
return(DbSFO2OMain.ExecuteSqlList <RefundInfoModel>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(new List <RefundInfoModel>());
}
}
public IList <MyOrderSkuInfoEntity> GetMyOrderInfo(int userId, int country, int language, string orderCode)
{
string sql =
@"SELECT ti.StartTime as TeamStartTime,ti.EndTime as TeamEndTime,ti.TeamStatus,ti.UserID AS TeamUserId,oi.TeamCode,oi.HuoLi,oi.[OrderCode],oi.[UserId],[OrderStatus],[TotalAmount],[Freight],[ProductTotalAmount],[CustomsDuties]
,oi.[PaidAmount],[ExchangeRate],opay.PayPlatform as PayType,[Receiver],[Phone],[PassPortType],[PassPortNum],[ReceiptAddress]
,[ReceiptPostalCode],[TaxType]
,ar.AreaName AS [ReceiptRegion]
,ct.CityName AS [ReceiptCity]
,pv.ProvinceName AS [ReceiptProvince]
,[ReceiptCountry]
,[ShippingMethod]
,oi.[CreateTime],[PayTime],[DeliveryTime],[ArrivalTime],[OrderCompletionTime],p.IsReturn
,RowsCount=1
,op.RefundQuantity,op.Spu, op.Sku,p.Name,s.MainDicValue, s.MainValue,s.SubDicValue,s.SubValue, op.Quantity, op.UnitPrice,op.PayUnitPrice,pim.ImagePath
,opay.PayStatus,op.TaxRate,p.SupplierId,op.IsBearDuty,op.Commission,p.NetWeightUnit,p.NetContentUnit,oi.Huoli,gc.CardSum,ISNULL(oi.Coupon,0) AS Coupon,ISNULL(gc.Id,0) AS CouponId,oe.ExpressList ExpressList,oe.ExpressCompany ExpressCompany
FROM [OrderInfo] oi
LEFT JOIN OrderProducts AS op ON op.OrderCode=oi.OrderCode
INNER JOIN ProductInfo AS p ON op.Spu=p.Spu AND p.LanguageVersion=@LanguageVersion
INNER JOIN SkuInfo AS s ON s.Sku=op.Sku AND s.SpuId=p.Id
INNER JOIN ProductImage AS pim ON pim.Spu=op.Spu AND pim.SortValue=1
LEFT JOIN OrderPayment AS opay ON oi.[OrderCode]=opay.OrderCode AND opay.PayStatus=2
LEFT JOIN City AS ct ON ct.CityId=oi.ReceiptCity AND ct.LanguageVersion=@LanguageVersion
LEFT JOIN Province AS pv ON pv.ProvinceId=oi.ReceiptProvince AND pv.LanguageVersion=@LanguageVersion
LEFT JOIN Area AS ar ON ar.AreaId=oi.ReceiptRegion AND ar.LanguageVersion=@LanguageVersion
LEFT JOIN TeamInfo AS ti ON ti.TeamCode=oi.TeamCode
LEFT JOIN GiftCard AS gc ON gc.OrderCode= CASE WHEN ISNULL(oi.ParentOrderCode,'')='' THEN oi.OrderCode ELSE oi.ParentOrderCode END
LEFT JOIN OrderExpress oe ON oe.OrderCode=oi.OrderCode
WHERE oi.UserId=@UserId AND oi.OrderCode=@OrderCode ";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", userId);
parameters.Append("@OrderCode", orderCode);
//parameters.Append("@ReceiptCountry", country);
parameters.Append("@LanguageVersion", language);
return(DbSFO2OMain.ExecuteSqlList <MyOrderSkuInfoEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(new List <MyOrderSkuInfoEntity>());
}
}
/// <summary>
/// 获取订单详情信息
/// </summary>
/// <param name="userId"></param>
/// <param name="country"></param>
/// <param name="language"></param>
/// <param name="orderCode"></param>
/// <returns></returns>
public IList <MyOrderSkuInfoEntity> GetMyOrderInfoByParentOrderCode(string ParentOrderCode)
{
string sql =
@"SELECT oi.OrderCode as OrderCode FROM OrderInfo AS oi WHERE oi.ParentOrderCode=@ParentOrderCode ";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@ParentOrderCode", ParentOrderCode);
return(DbSFO2OMain.ExecuteSqlList <MyOrderSkuInfoEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(new List <MyOrderSkuInfoEntity>());
}
}
/// <summary>
/// 获取订单SKU详细信息
/// </summary>
/// <param name="userId"></param>
/// <param name="language"></param>
/// <param name="orderCode"></param>
/// <returns></returns>
public IList <OrderSkuEntity> GetOrderSkuEntities(int userId, int language, string orderCode)
{
string sql =
@"SELECT oi.[OrderCode],oi.[UserId],[OrderStatus],[TotalAmount],[Freight],[ProductTotalAmount],[CustomsDuties]
,oi.[PaidAmount],[ExchangeRate],oi.[PayType]
,oi.[CreateTime],[PayTime],[DeliveryTime],[ArrivalTime],[OrderCompletionTime]
,op.Spu, op.Sku, op.Quantity, op.UnitPrice, op.PayUnitPrice
,op.TaxRate, op.Commission, op.PayAmount, op.TaxAmount
,op.IsBearDuty,op.RefundQuantity
,opay.PayStatus,
p.SupplierId,
op2.PromotionId
,op2.PromotionPrice
,op2.OriginalPrice
,op2.OriginalRMBPrice
,p2.PromotionCost
FROM [OrderInfo] oi
LEFT JOIN OrderProducts AS op ON op.OrderCode=oi.OrderCode
INNER JOIN ProductInfo AS p ON op.Spu=p.Spu AND p.LanguageVersion=1
INNER JOIN SkuInfo AS s ON s.Sku=op.Sku AND s.SpuId=p.Id
LEFT JOIN OrderPayment AS opay ON oi.[OrderCode]=opay.OrderCode AND opay.PayStatus=2
LEFT JOIN OrderPromotions AS op2 ON oi.OrderCode=op2.OrderCode AND op.Sku = op2.Sku
LEFT JOIN Promotions AS p2 ON op2.PromotionId=p2.Id
WHERE oi.UserId=@UserId AND oi.OrderCode=@OrderCode";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", userId);
parameters.Append("@OrderCode", orderCode);
parameters.Append("@LanguageVersion", language);
return(DbSFO2OMain.ExecuteSqlList <OrderSkuEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(new List <OrderSkuEntity>());
}
}
/// <summary>
/// 根据状态获取订单列表
/// </summary>
/// <param name="userId"></param>
/// <param name="country"></param>
/// <param name="language"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="status"></param>
/// <returns></returns>
public IList <MyOrderSkuInfoEntity> GetMyOrderProductInfos(int userId, int country, int language, int pageIndex, int pageSize, Model.Enum.OrderStatusEnum status)
{
string sql = @"
--DECLARE @PageSize INT
--DECLARE @PageIndex INT
--SET @PageSize=100
--SET @PageIndex=1
;WITH tbAll([OrderCode],[UserId],[OrderStatus],[TotalAmount],[Freight],[ProductTotalAmount],[CustomsDuties],[CreateTime],[TeamCode],[Huoli],RowNum,Coupon) AS
(
SELECT [OrderCode]
,[UserId]
,[OrderStatus]
,[TotalAmount]
,[Freight]
,[ProductTotalAmount]
,[CustomsDuties]
,[CreateTime]
,[TeamCode]
,[Huoli]
,ROW_NUMBER() OVER(ORDER BY [CreateTime] DESC) AS RowNum
,IsNull(Coupon,0) as Coupon
FROM OrderInfo AS oi
WHERE oi.UserId=@UserId
AND oi.ReceiptCountry=@ReceiptCountry
{0}
)
SELECT ta.[OrderCode],
ta.[UserId],
[OrderStatus],
[TotalAmount],
[Freight],
[ProductTotalAmount],
[CustomsDuties],
ta.[CreateTime],
rowsCount= (select max(rownum) from tbAll),
op.RefundQuantity,
op.Spu,
op.Sku,
p.Name,
s.MainDicValue,
s.MainValue,
s.SubDicValue,
s.SubValue,
op.Quantity,
op.PayUnitPrice,
ta.TeamCode as TeamCode,ta.Huoli as HuoLi,ta.Coupon as Coupon,
pim.ImagePath,p.NetWeightUnit,p.NetContentUnit,
ti.UserID AS TeamUserId,
ti.StartTime AS TeamStartTime,
ti.EndTime AS TeamEndTime,
ti.TeamStatus AS TeamStatus,
oe.ExpressList AS ExpressList,
oe.ExpressCompany AS ExpressCompany
from tbAll as ta
INNER JOIN OrderProducts AS op ON op.OrderCode=ta.OrderCode
INNER JOIN ProductInfo AS p ON op.Spu=p.Spu AND p.LanguageVersion=@LanguageVersion
INNER JOIN SkuInfo AS s ON s.Sku=op.Sku AND s.SpuId=p.Id
INNER JOIN ProductImage AS pim ON pim.Spu=op.Spu AND pim.SortValue=1
LEFT JOIN TeamInfo AS ti ON ti.TeamCode=ta.TeamCode
LEFT JOIN OrderExpress as oe ON oe.OrderCode=ta.OrderCode
where rownum>(@PageSize*(@PageIndex-1)) and rownum <=(@PageSize*@PageIndex) ORDER BY ta.[CreateTime] DESC
";
try
{
var parameters = DbSFO2OMain.CreateParameterCollection();
sql = string.Format(sql, status != OrderStatusEnum.Default ? " and oi.OrderStatus=@OrderStatus " : " AND 1=1 ");
parameters.Append("@OrderStatus", status.ToInt());
parameters.Append("@UserId", userId);
parameters.Append("@ReceiptCountry", country);
parameters.Append("@LanguageVersion", language);
parameters.Append("@PageSize", pageSize);
parameters.Append("@PageIndex", pageIndex);
return(DbSFO2OMain.ExecuteSqlList <MyOrderSkuInfoEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(new List <MyOrderSkuInfoEntity>());
}
}
