/// <summary>
/// 生成支付记录、支付信息关联表新增Sql语句
/// </summary>
/// <param name="view">支付记录、支付信息关联实体</param>
/// <returns>sql语句</returns>
internal string GenerateInsertPaymentOrder_OrdersSql(Domain.WebModel.Models.Orders.PaymentOrder_OrdersView view)
{
StringBuilder sqlBuilder = new StringBuilder(" INSERT INTO PaymentOrder_Orders ( RelateID, PaymentOrderID, OrderID )");
sqlBuilder.AppendFormat(" VALUES ( {0},'{1}','{2}')", view.RelateID, SqlFilterUtil.ReplaceSqlChar(view.PaymentOrderID), SqlFilterUtil.ReplaceSqlChar(view.OrderID));
return(sqlBuilder.ToString());
}
/// <summary>
/// 更新支付单、订单支付通道
/// </summary>
/// <param name="view">订单支付信息(PaymentOrderID,PayChannel)</param>
/// <returns>Sql语句</returns>
public ResultModel UpdatePayChannel(Domain.WebModel.Models.Orders.PaymentOrderView view)
{
ResultModel result = new ResultModel();
List <string> sqls = new List <string>();
sqls.Add(string.Format(" UPDATE PaymentOrder SET PayChannel={0},OrderNO='{1}',RechargeAmount={2} WHERE PaymentOrderID='{3}'",
view.PayChannel,
view.OrderNO,
view.RechargeAmount,
SqlFilterUtil.ReplaceSqlChar(view.PaymentOrderID)));
int payType = view.PayChannel == (int)OrderEnums.PayChannel.Balance
? (int)OrderEnums.PayType.BalancePay
: (int)OrderEnums.PayType.ThirdPay;
string orderSql = string.Format(@" UPDATE dbo.[Order] SET PayChannel='{0}', PayType='{1}' WHERE OrderID IN (SELECT b.OrderID FROM PaymentOrder
AS a INNER JOIN dbo.PaymentOrder_Orders AS b ON a.PaymentOrderID=b.PaymentOrderID AND a.PaymentOrderID='{2}')"
, view.PayChannel, payType, view.PaymentOrderID);
sqls.Add(orderSql);
string sql = SqlTransactionUtil.GenerateTransSql(sqls);
//执行sql
var queryResult = _database.RunSqlQuery(x => x.ToResultSets(sql));
dynamic source = queryResult[0][0];
result.IsValid = source.Count > 0;
return(result);
}
/// <summary>
/// 生成订单跟踪日志插入sql语句
/// </summary>
/// <param name="view">日志实体</param>
/// <returns>sql语句</returns>
internal string GenerateInsertSql(Domain.WebModel.Models.Orders.OrderTrackingLogView view)
{
string sql =
string.Format(" INSERT INTO dbo.OrderTrackingLog (OrderID,OrderStatus,TrackingContent,CreateTime ,CreateBy) VALUES ('{0}',{1},'{2}','{3}','{4}')",
SqlFilterUtil.ReplaceSqlChar(view.OrderID),
view.OrderStatus,
SqlFilterUtil.ReplaceSqlChar(view.TrackingContent),
view.CreateTime.DateTimeToString(),
SqlFilterUtil.ReplaceSqlChar(view.CreateBy)
);
return(sql);
}
/// <summary>
/// 判断充值单类型
/// </summary>
/// <param name="paymentOrderId">支付单号</param>
/// <param name="prefix">充值单前缀</param>
/// <returns>判断充值单类型</returns>
public ResultModel IsCurrentRechargeOrder(string paymentOrderId, ERechargeOrderPrefix prefix)
{
ResultModel resultModel = new ResultModel()
{
IsValid = false
};
string sql = string.Format("SELECT COUNT(*) AS [Count] FROM dbo.PaymentOrder_Orders WHERE PaymentOrderID='{0}' AND OrderID LIKE '{1}%'"
, SqlFilterUtil.ReplaceSqlChar(paymentOrderId), EnumDescription.GetFieldText(prefix));
//执行sql
var queryResult = _database.RunSqlQuery(x => x.ToResultSets(sql));
dynamic source = queryResult[0][0];
resultModel.IsValid = source.Count > 0;
return(resultModel);
}
/// <summary>
/// 根据商品Id,sku码Id获取商品信息
/// </summary>
/// <param name="lstGoodsId">商品Id集合</param>
/// <param name="lstSkuProductIds">sku码Id集合</param>
/// <param name="languageId">语言代码</param>
/// <returns></returns>
public ResultModel GetGoodsInfo(List <String> lstGoodsId, List <String> lstSkuProductIds, int languageId)
{
ResultModel result = new ResultModel {
IsValid = false
};
if (lstGoodsId == null || lstGoodsId.Count == 0)
{
result.Messages.Add("参数异常,请稍候再试");
}
else
{
var goodsidattr = string.Join(",", lstGoodsId.Select(m => "'" + SqlFilterUtil.ReplaceSqlChar(m) + "'").ToArray());
var skuattr = string.Join(",", lstSkuProductIds.Select(m => "'" + SqlFilterUtil.ReplaceSqlChar(m) + "'").ToArray());
StringBuilder sb = new StringBuilder();
string sql = @"Select p.FareTemplateID,p.Volume,sp.PurchasePrice,sp.Stock AS 'StockQuantity',sp.MarketPrice,sp.SkuName As 'ValueStr',sp.SKU_ProducId As 'SkuNumber',p.RebateDays,p.RebateRatio
,p.PostagePrice,p.SupplierId, p.ProductID AS 'GoodsId',p.FreeShipping,p.Weight,p.MerchantID AS 'ComId',p.Status,p.MerchantID,
(SELECT reverse(stuff(reverse((
SELECT DISTINCT BBB.AttributeName+',' FROM dbo.SKU_AttributeValues AAA JOIN dbo.SKU_Attributes BBB ON AAA.AttributeId=BBB.AttributeId
WHERE CHARINDEX(convert(varchar,AAA.ValueId)+'_',sp.SKUStr)>0
OR CHARINDEX('_'+convert(varchar,AAA.ValueId),sp.SKUStr)>0
OR sp.SKUStr=convert(varchar,AAA.ValueId) FOR XML PATH(''))),1,1,''))) As 'AttributeName'
,pp.PicUrl As 'Pic',pl.ProductName AS 'GoodsName',
Case When (pr.StarDate<=GETDATE() and pr.EndDate>=GETDATE() and pr.SalesRuleId=2 and pr.Discount>0) Then sp.HKPrice*pr.Discount
Else sp.HKPrice End As 'GoodsUnits'
,m.ShopName AS 'ComName',m.IsProvideInvoices
,0 As 'Count',0 As 'IsChecked' From Product p
LEFT JOIN ProductPic pp on p.ProductID = pp.ProductId LEFT JOIN Product_Lang pl ON p.ProductID = pl.ProductId
LEFT JOIN YH_MerchantInfo m ON p.MerchantID=m.MerchantID LEFT JOIN SKU_Product sp on p.ProductId = sp.ProductId
LEFT JOIN ProductRule pr ON p.ProductID = pr.ProductId
Where pp.Flag = 1 And pl.LanguageID ={0} AND sp.SKU_ProducId IN ({1}) AND p.ProductId IN ({2});";
sb.AppendFormat(sql, languageId, skuattr, goodsidattr);
var data = _database.RunSqlQuery(x => x.ToResultSets(sb.ToString()));
List <dynamic> sources = data[0];
result.Data = sources.ToEntity <GoodsInfoModel>();
foreach (var prodcut in result.Data)
{
prodcut.Pic = GetConfig.FullPath() + prodcut.Pic;
}
result.IsValid = true;
}
return(result);
}
/// <summary>
/// 生成订单支付信息新增Sql语句
/// </summary>
/// <param name="view">订单支付信息</param>
/// <returns>Sql语句</returns>
internal string GenerateInsertSql(Domain.WebModel.Models.Orders.PaymentOrderView view)
{
StringBuilder sqlBuilder = new StringBuilder(" INSERT INTO [PaymentOrder]([PaymentOrderID],[UserID],[ProductAmount],[RealAmount],[Flag],[CreateDT],[PayType],[PayChannel],[outOrderId],[OrderNO],[RechargeAmount])");
sqlBuilder.AppendFormat(" VALUES ( '{0}',{1},{2},{3},{4},'{5}',{6},{7} ,'{8}','{9}',{10}) ",
SqlFilterUtil.ReplaceSqlChar(view.PaymentOrderID),
view.UserID,
view.ProductAmount,
view.RealAmount,
view.Flag,
view.CreateDT.DateTimeToString(),
view.PayType,
view.PayChannel,
SqlFilterUtil.ReplaceSqlChar(view.outOrderId),
view.OrderNO,
view.RechargeAmount
);
return(sqlBuilder.ToString());
}
/// <summary>
/// 获取单个订单的商品评论
/// </summary>
/// <param name="model">查询模型</param>
/// <returns>单个订单的商品评论</returns>
public ResultModel GetOrderProductComments(SearchOrderProductCommentView model)
{
var result = new ResultModel()
{
IsValid = false
};
string sql = string.Format(@"SELECT a.SKU_ProducId,a.Iscomment,a.OrderID,a.ProductId,a.SkuName,c.PicUrl,d.ProductName,e.CommentContent,e.CommentDT,e.CommentLevel,e.ProductCommentId,
stuff((select ','+convert(varchar(10),Labels) from SP_ProductComment_Labels where ProductCommentId = e.ProductCommentId for xml path('')),1,1,'') as LablesStr
FROM OrderDetails AS a
INNER JOIN dbo.[Order] AS f ON a.OrderID=f.OrderID
INNER JOIN dbo.Product AS b ON a.ProductId =b.ProductId
LEFT JOIN(SELECT * FROM dbo.ProductPic WHERE Flag=1) AS c
ON b.ProductId=c.ProductID
LEFT JOIN(SELECT * FROM dbo.Product_Lang WHERE LanguageID={0}) AS d
ON b.ProductId=d.ProductId
LEFT JOIN dbo.SP_ProductComment AS e ON a.SKU_ProducId=e.SKU_ProducId AND a.OrderID=e.OrderId
WHERE a.OrderID='{1}' AND f.UserID={2}", model.LanguageID.Value, SqlFilterUtil.ReplaceSqlChar(model.OrderID), model.UserID);
if (model.Iscomment.HasValue)
{
sql += string.Format(" AND a.Iscomment={0}", model.Iscomment.Value);
}
List <dynamic> sources = _database.RunSqlQuery(x => x.ToResultSets(sql))[0];
result.Data = sources.ToEntity <OrderProductCommentView>();
result.IsValid = true;
return(result);
}
/// <summary>
/// 获取支付单数据
/// </summary>
/// <param name="view">支付单查找条件(需要提供 PaymentOrderID,UserID)</param>
/// <param name="languageID">Id</param>
/// <returns></returns>
public ResultModel GetPaymentActionData(PaymentOrderView view, int languageID)
{
ResultModel resultModel = new ResultModel()
{
IsValid = false
};
PaymentActionPageView paymentAction = null;
view.Flag = (int)OrderEnums.PaymentFlag.NonPaid;
string sql = string.Format(@"SELECT TOP 1 a.PaymentOrderID, a.PayChannel, a.Flag,a.UserID, a.ProductAmount,
c.OrderID,c.OrderAddressId,c.PostalCode,c.Mobile,c.THAreaID,c.Email,c.DetailsAddress,c.Receiver
FROM PaymentOrder AS a
INNER JOIN PaymentOrder_Orders AS b
ON a.PaymentOrderID=b.PaymentOrderID
INNER JOIN OrderAddress AS c
ON b.OrderID=c.OrderID
WHERE a.PaymentOrderID='{0}' AND a.Flag={1} AND a.UserID={2}
", SqlFilterUtil.ReplaceSqlChar(view.PaymentOrderID), view.Flag, view.UserID);
//执行sql
var queryResult = _database.RunSqlQuery(x => x.ToResultSets(sql));
if (queryResult[0].Count > 0)
{
resultModel.IsValid = true;
dynamic paymentData = queryResult[0][0];
paymentAction = new PaymentActionPageView();
paymentAction.PaymentOrderView =
new PaymentOrderView()
{
PaymentOrderID = paymentData.PaymentOrderID,
ProductAmount = paymentData.ProductAmount,
PayChannel = paymentData.PayChannel,
Flag = paymentData.Flag,
UserID = paymentData.UserID
};
paymentAction.OrderAddressView = new OrderAddressView()
{
OrderID = paymentData.OrderID,
OrderAddressId = paymentData.OrderAddressId,
PostalCode = paymentData.PostalCode,
Mobile = paymentData.Mobile,
THAreaID = paymentData.THAreaID,
Email = paymentData.Email,
DetailsAddress = paymentData.DetailsAddress,
Receiver = paymentData.Receiver
};
//获取 省、市、区
Dictionary <string, string> userAreas = thAreaService.GetSingleTierAreaNames(new SearchUserAddressModel()
{
THAreaID = paymentAction.OrderAddressView.THAreaID
}, languageID).Data;
paymentAction.OrderAddressView.DetailsAddress = AddressHelper.ShowUserAddress(userAreas["Country"], userAreas["Sheng"], userAreas["Shi"], userAreas["Qu"],
paymentAction.OrderAddressView.DetailsAddress, languageID);
paymentAction.OrderDetailsForPayResultView = orderService.GetOrderDetailsByPaymentOrderId(paymentAction.PaymentOrderView.PaymentOrderID).Data;
}
resultModel.Data = paymentAction;
return(resultModel);
}
/// <summary>
/// 生成支付单 支付成功Sql
/// </summary>
/// <param name="view">订单支付信息</param>
/// <returns>语句</returns>
internal string GenerateUpdatePaymentSuccessSql(PaymentOrderView view)
{
string sql = string.Format(" UPDATE [PaymentOrder] SET [RealAmount] = {0},[Flag] = {1},[PaymentDate] ='{2}',[outOrderId] = '{3}' WHERE [PaymentOrderID] ='{4}'", view.RealAmount, view.Flag, view.PaymentDate.Value.DateTimeToString(), SqlFilterUtil.ReplaceSqlChar(view.outOrderId), SqlFilterUtil.ReplaceSqlChar(view.PaymentOrderID));
return(sql);
}
/// <summary>
/// 生成支付单状态更新Sql语句
/// </summary>
/// <param name="view">订单支付信息(PaymentOrderID,Flag)</param>
/// <returns>Sql语句</returns>
internal string GenerateUpdateStatusSql(Domain.WebModel.Models.Orders.PaymentOrderView view)
{
string sql = string.Format(" UPDATE PaymentOrder SET Flag={0} WHERE PaymentOrderID='{1}'", view.Flag, SqlFilterUtil.ReplaceSqlChar(view.PaymentOrderID));
return(sql);
}
