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