发信人: yahao(亚豪)
整理人: i_am_trueman(2004-01-14 09:24:34), 站内信件
|
合并订单的存贮过程代码片断：
=======================================================
---- 【操作】合并订单，分为：合并商品和货款（17）订单补欠款即只转货款（18） ----
IF @ProcessID = 17 Or @ProcessID = 18
BEGIN
---- 保存当前订单受理记录 ----
INSERT INTO OrderProcessInfo
(
OrderID,
ProcessID,
AdminMsgID,
CreateOn,
CreateBy,
Status
)
VALUES
(
@OrderID,
@ProcessID,
@intAdminMsgID,
getdate(),
@OpUser,
0
)
SET @intErrCode = @intErrCode + @@ERROR
---- 保存目标订单受理记录 ----
EXEC usp_AddOrderProcessMsg @ObjectID,@UserSID,@intTargetOrderID,@ProcessID,@strTargetAdminMsg,@OpUser,@RemoteAddr,@intRetValue OUTPUT
SET @intErrCode = @intErrCode + @intRetValue
---- 具体操作 ----
IF @ProcessID = 17
BEGIN
---- 合并订单（货款和订购商品） ----
SELECT
@intOrderProductSpecies = ProductSpecies,
@intOrderProductAmount = ProductAmount,
@intProductAmountFee = ProductAmountFee,
@intAdditionalFee = AdditionalFee,
@intPostageFee = PostageFee,
@intTotalFee = TotalFee,
@decRealIncomeFee = RealIncomeFee,
@intBuyerScore = BuyerScore,
@intIntroducerScore = IntroducerScore,
@decPostageCostFee = PostageCostFee,
@decAdditionalCostFee = AdditionalCostFee
FROM OrderInfo
WHERE OrderID = @OrderID
SET @strAllAdminMsg = '合并商品 ' + CAST(@intOrderProductSpecies As varchar(5)) + '种/' + CAST(@intOrderProductAmount As varchar(5)) + '件；商品费用 ' + CAST(@intProductAmountFee As varchar(10)) + '元，邮费 ' + CAST(@intPostageFee As varchar(6)) + ' 元，附加费 ' + CAST(@intAdditionalFee As varchar(10)) + '元，总计 ' + CAST(@intTotalFee As varchar(10)) + ' 元；实收款：' + CAST(@decRealIncomeFee As varchar(10)) + '元；结转实付邮费：' + CAST(@decPostageCostFee As varchar(10)) + '元、实付汇费/ePay手续费：' + CAST(@decAdditionalCostFee As varchar(10)) + '元；积分 ' + CAST(@intIntroducerScore As varchar(6)) + '/' + CAST(@intBuyerScore As varchar(6))
---- 更新受理日志 ----
IF LEN(@strAllAdminMsg) > 0
BEGIN
WHILE (@strAllAdminMsg <> '' And @strAllAdminMsg <> '(续)')
BEGIN
SET @strAdminMsg = dbo.fun_mySubString(@strAllAdminMsg,1,120)
---- 更新当前订单受理日志 ----
EXEC usp_AddOrderProcessMsg @ObjectID,@UserSID,@OrderID,30,@strAdminMsg,'system',@RemoteAddr,@intRetValue OUTPUT
---- 更新目标订单受理日志 ----
EXEC usp_AddOrderProcessMsg @ObjectID,@UserSID,@intTargetOrderID,30,@strAdminMsg,'system',@RemoteAddr,@intRetValue OUTPUT
SET @strAllAdminMsg = REPLACE(@strAllAdminMsg,@strAdminMsg,'')
SET @strAllAdminMsg = '(续)' + @strAllAdminMsg
SET @strAdminMsg = ''
END
END
---- 处理具体的订购商品，并生成合并商品内容清单 ----
SET @strAllAdminMsg = '商品列表：'
SET @TableData = CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
ProductID,
ProductName,
ActualPrice,
ProductQuantity,
ProductGift,
StyleID,
RefSource,
IntroducerScore,
BuyerScore
FROM OrderProductInfo
WHERE OrderID = @OrderID
ORDER BY ActualPrice * ProductQuantity DESC
---- 打开游标 ----
OPEN @TableData
FETCH NEXT FROM @TableData INTO @intProductID,@strProductName,@intProductPrice,@intProductQuantity,@strProductGift,@intStyleID,@intRefSource,@intIntroducerScore,@intBuyerScore
---- 处理商品数据，并生成信息字符串 ----
WHILE @@FETCH_STATUS = 0
BEGIN
IF Exists (
SELECT ProductID
FROM OrderProductInfo
WHERE
OrderID = @intTargetOrderID
And
ProductID = @intProductID
And
StyleID = @intStyleID
And
RefSource = @intRefSource
And
ProductGift = @strProductGift
And
IntroducerScore = @intIntroducerScore
And
BuyerScore = @intBuyerScore
)
---- 如果有相同的订购商品，则只累加数量和积分即可 ----
UPDATE OrderProductInfo
SET
ProductQuantity = ProductQuantity + @intProductQuantity,
IntroducerScore = IntroducerScore + @intIntroducerScore,
BuyerScore = BuyerScore + @intBuyerScore
WHERE
OrderID = @intTargetOrderID
And
ProductID = @intProductID
And
StyleID = @intStyleID
And
RefSource = @intRefSource
And
ProductGift = @strProductGift
And
IntroducerScore = @intIntroducerScore
And
BuyerScore = @intBuyerScore
ELSE
INSERT INTO OrderProductInfo
SELECT
@intTargetOrderID,
ProductID,
ProductName,
ProductPrice,
ActualPrice,
ProductQuantity,
ProductGift,
StyleID,
RefSource,
RefProductID,
BuyerScore,
IntroducerScore,
getdate(),
null,
'',
0
FROM OrderProductInfo
WHERE
OrderID = @OrderID
And
ProductID = @intProductID
And
StyleID = @intStyleID
And
RefSource = @intRefSource
SET @intErrCode = @intErrCode + @@ERROR
SET @strAllAdminMsg = @strAllAdminMsg + @strProductName + '[' + CAST(@intProductID As varchar(10)) + '/' + CAST(@intProductPrice As varchar(10)) + '/' + CAST(@intProductQuantity As varchar(5)) + ']'
FETCH NEXT FROM @TableData INTO @intProductID,@strProductName,@intProductPrice,@intProductQuantity,@strProductGift,@intStyleID,@intRefSource,@intIntroducerScore,@intBuyerScore
END
---- 关闭游标并释放资源 ----
CLOSE @TableData
DEALLOCATE @TableData
---- 订购商品数量、种类和商品总金额 ----
SELECT
@intOrderProductSpecies = COUNT(RowID),
@intOrderProductAmount = SUM(ProductQuantity)
FROM OrderProductInfo
WHERE OrderID = @intTargetOrderID
---- 主商品（价格最高的商品） ----
SET @strProductName = (SELECT Top 1 ProductName FROM OrderProductInfo WHERE OrderID = @intTargetOrderID ORDER BY ActualPrice DESC)
UPDATE OrderInfo
SET
MainProductName = @strProductName,
ProductSpecies = @intOrderProductSpecies,
ProductAmount = @intOrderProductAmount,
ProductAmountFee = ProductAmountFee + @intProductAmountFee,
PostageFee = PostageFee + @intPostageFee,
AdditionalFee = AdditionalFee + @intAdditionalFee,
TotalFee = TotalFee + @intTotalFee,
RealIncomeFee = RealIncomeFee + @decRealIncomeFee,
BuyerScore = BuyerScore + @intBuyerScore,
IntroducerScore = IntroducerScore + @intIntroducerScore,
PostageCostFee = PostageCostFee + @decPostageCostFee,
AdditionalCostFee = AdditionalCostFee + @decAdditionalCostFee,
UpdateOn = getdate()
WHERE OrderID = @intTargetOrderID
SET @intErrCode = @intErrCode + @@ERROR
---- 重新生成订购商品详细（发货清单）列表并计算订单成本和毛利数据 ----
EXEC usp_GenOrderProductExtraList @ObjectID,@UserSID,@intTargetOrderID,2,1,@OpUser,@intRetValue OUTPUT
SET @strAllAdminMsg = @strAllAdminMsg + '；RCCP'
---- 更新受理日志 ----
IF LEN(@strAllAdminMsg) > 0
BEGIN
WHILE (@strAllAdminMsg <> '' And @strAllAdminMsg <> '(续)')
BEGIN
SET @strAdminMsg = dbo.fun_mySubString(@strAllAdminMsg,1,120)
---- 更新当前订单受理日志 ----
EXEC usp_AddOrderProcessMsg @ObjectID,@UserSID,@OrderID,30,@strAdminMsg,'system',@RemoteAddr,@intRetValue OUTPUT
---- 更新目标订单受理日志 ----
EXEC usp_AddOrderProcessMsg @ObjectID,@UserSID,@intTargetOrderID,30,@strAdminMsg,'system',@RemoteAddr,@intRetValue OUTPUT
SET @strAllAdminMsg = REPLACE(@strAllAdminMsg,@strAdminMsg,'')
SET @strAllAdminMsg = '(续)' + @strAllAdminMsg
SET @strAdminMsg = ''
END
END
END
ELSE
BEGIN
---- 取得订单资料（实付邮费和实付汇费/ePay手续费） ----
SELECT
@decPostageCostFee = PostageCostFee,
@decAdditionalCostFee = AdditionalCostFee
FROM OrderInfo
WHERE
OrderID = @OrderID
And
Status & 1 = 0
---- 订单补欠款 ----
IF @decRealIncomeFee > 0 And (@decRealIncomeFee <> @intTotalFee)
BEGIN
SET @strAdminMsg = '订单补欠款，总计：' + CAST(@decRealIncomeFee As varchar(10)) + ' 元（实收款）；结转实付邮费：' + CAST(@decPostageCostFee As varchar(10)) + '元、实付汇费/ePay手续费：' + CAST(@decAdditionalCostFee As varchar(10)) + '元'
---- 更新目标订单收款金额 ----
UPDATE OrderInfo
SET
RealIncomeFee = RealIncomeFee + @decRealIncomeFee,
PostageCostFee = PostageCostFee + @decPostageCostFee,
AdditionalCostFee = AdditionalCostFee + @decAdditionalCostFee
WHERE OrderID = @intTargetOrderID
END
ELSE
BEGIN
SET @strAdminMsg = '订单补欠款，总计：' + CAST(@intTotalFee As varchar(10)) + ' 元（全款）；结转实付邮费：' + CAST(@decPostageCostFee As varchar(10)) + '元、实付汇费/ePay手续费：' + CAST(@decAdditionalCostFee As varchar(10)) + '元'
---- 更新目标订单收款金额 ----
UPDATE OrderInfo
SET
RealIncomeFee = RealIncomeFee + @intTotalFee,
PostageCostFee = PostageCostFee + @decPostageCostFee,
AdditionalCostFee = AdditionalCostFee + @decAdditionalCostFee
WHERE OrderID = @intTargetOrderID
END
SET @intErrCode = @intErrCode + @@ERROR
---- 更新当前订单受理日志 ----
EXEC usp_AddOrderProcessMsg @ObjectID,@UserSID,@OrderID,30,@strAdminMsg,'system',@RemoteAddr,@intRetValue OUTPUT
---- 更新目标订单受理日志 ----
EXEC usp_AddOrderProcessMsg @ObjectID,@UserSID,@intTargetOrderID,30,@strAdminMsg,'system',@RemoteAddr,@intRetValue OUTPUT
END
---- 更新当前订单受理状态为已合并（4） ----
UPDATE OrderInfo
SET
ProcessStatus = 4,
UpdateOn = getdate()
WHERE
ProcessStatus & 4 = 0
And
OrderID = @OrderID
SET @intErrCode = @intErrCode + @@ERROR
END
----
绿色签名 - 欢迎光临网易广州虚拟社区ASP版 |
|