------------------------------------------------------
--Copyright (c) 1.0
--Author :保密
--Date :2010-3-17 16:39:58
--Description:拍卖行的竞标
--游戏里的拍卖行竞拍存储过程，玩家反映一口价竞拍物品，点券扣了，但物品没有收到，目前拍卖行总交易10万多次，
--这种情况出现了11次。大概是什么问题呢？目前多次测试没能重现，有没有牛人能目测出问题在哪儿？==========================================================================================
--业务逻辑如下（所有的扣点加点，调用外部的存储过程，返回接口和调用方法也不一致，汗）：
--IF 点券交易
--BEGIN
-- IF 一口价
-- BEGIN
-- 1.发送给买家物品（发送到买家临时背包）
-- 2.如果这个东西之前有另外的玩家竞价过，需要退还上一个玩家的点券
-- 3.保存交易日志
-- 4.删除拍卖行的物品，删除这条拍卖纪录
-- 5.扣买家的点券
-- 6.给卖家点券
-- END
-- ELSE 不是一口价，竞价
-- BEGIN
-- 1.退回上一个竞价者的点券
-- 2.扣本次竞价者的点券
-- END
--END
--ELSE 游戏币交易
--BEGIN
-- 1.退回上一个竞价者的游戏币
-- 2.扣本次竞价者的游戏币
--END
--===========================================================================================
-- Return value:
-- 0:执行成功
-- 特别说明，由于代码生成器问题，返回值只接收SELECT值，不接收直接RETURN的值，呃~~~不能改变别人就改变自己吧
------------------------------------------------------
ALTER PROCEDURE [dbo].[P_HBAuctionHouse_BidUp]
@Idx BIGINT, -- 标识
@BuyerId varchar(50) ,-- 购买者Id，没有购买者的时候就是空的，有就是购买者的GUID
@BuyerName varchar(50),--购买者名字
@BuyPrice int -- 购买价
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRAN BIDUPTRAN
DECLARE @BuyerUid UNIQUEIDENTIFIER--用户Id，外部传的字符串类型，需要转换成UNIQUEIDENTIFIER
DECLARE @BidCount INT--竞标次数
DECLARE @SellerId UNIQUEIDENTIFIER--卖家id
DECLARE @SellerName VARCHAR(50)--卖家名称
DECLARE @BeforeBuyerId VARCHAR(50)--上次的竞价者id
DECLARE @BeforeBuyerName VARCHAR(50) --上次的竞价者NAME
DECLARE @M INT--上次的购买价
DECLARE @EndPrice INT--一口价
DECLARE @CurrencyType INT --点券还是游戏币
DECLARE @Result INT--点券增减返回值
DECLARE @SRCID VARCHAR(50)--交易号，负的交易ID
DECLARE @BeforePass9Name varchar(100)--之前买家的C通行证名
DECLARE @BuyerPass9Name varchar(100)--当前买家的C通行证名
DECLARE @TBLPAY TABLE(CODE VARCHAR(20))--交易表
DECLARE @NOW DATETIME--当前服务器时间
SET @NOW = GETDATE()
SET @BidCount = -1--竞标次数
IF @BuyerId <> ''
SET @BuyerUid = CAST(@BuyerId AS UNIQUEIDENTIFIER)
ELSE
SET @BuyerUid = '00000000-0000-0000-0000-000000000000'
SET @M = 0
BEGIN TRY
--有无该条记录
IF NOT EXISTS ( SELECT * FROM [HB_AuctionHouse] WITH(NOLOCK) WHERE Idx = @Idx )
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6400
RETURN;
END
--获取相关数据
SELECT @IDX = IDX, @SellerId=SellerId ,@SellerName=SellerName, @BeforeBuyerId=BuyerId,@BeforeBuyerName=BuyerName, @M=BuyPrice,@EndPrice=SellEndPrice, @BidCount =BidCount,@CurrencyType = CurrencyType
FROM [HB_AuctionHouse] WITH(XLOCK) WHERE Idx = @Idx
IF @M > @BuyPrice
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6407
RETURN;
END
IF @SellerId = @BuyerUid
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6401
RETURN;
END
--交易号
SET @SRCID = CAST( @Idx * -1 AS VARCHAR(18)) + '_' + CAST( @BidCount AS VARCHAR(5))
--允许用户竞标后，再一口价
IF @BeforeBuyerId = @BuyerId AND @EndPrice <> @BuyPrice
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6402
RETURN;
END
IF @CurrencyType = 2--点券
BEGIN
IF @EndPrice = @BuyPrice--如果竞标价等于一口价，那就直接拍定
BEGIN
INSERT INTO [HB_TempPackage]--加入临时背包
([ManagerId]
,[ItemId]
,[ItemStrengthLevel]
,[ItemCode]
,[ItemName]
,[Count]
,[ItemType]
,[ImageId]
,[Memo]
,[Status]
,[Rowtime]
,[Increment])
SELECT @BuyerId
,ItemId
,ItemStrengthLevel
,ItemCode
,ItemName
,[Count]
,[ItemType]
,[ImageId]
,'卖家:'+SellerName+',花费'+CAST(@BuyPrice AS VARCHAR(10))+'G'
,0
,GETDATE(),Increment FROM [HB_AuctionHouse] WHERE Idx = @Idx
--退回上一个竞价者的钱$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
IF @BidCount > 0
BEGIN
--获取上一个买家的Pass9Name
-- DECLARE @BeforePass9Name varchar(100)
SELECT @BeforePass9Name = Pass9Name FROM dbo.HB_User U
INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId
WHERE M.ManagerGuid = @BeforeBuyerId
DECLARE @B VARCHAR(50)
SET @B = @SRCID+'_B'
EXEC P_HbPayAccount_Pay @BeforePass9Name,-1,@B,0,0,@M,@Result
IF @Result <> 5200
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6402
RETURN;
END
END
--------------给上一个竞价者发一个邮件，通知竞标失败
--------------通知卖家已经拍卖成功
--保存交易日志
INSERT INTO [HB_AuctionHouse_LOG]
SELECT [Idx] , [ItemName] , [ItemStrengthLevel] ,
[ItemId] , [ItemCode] , [ItemLevel], [Count] , [ItemType] ,
[ImageId] , [SellerId] , [SellerName], @BuyerId , @BuyerName ,
[SellStartPrice] , [SellEndPrice], [BuyPrice] ,
[BidCount] ,[CurrencyType] ,[Increment] FROM [HB_AuctionHouse] WHERE Idx = @Idx
--删除拍卖行的物品，删除这条拍卖纪录
DELETE [HB_AuctionHouse] WHERE Idx = @Idx
--获取Pass9Name
SELECT @BuyerPass9Name = Pass9Name FROM dbo.HB_User U (NOLOCK)
INNER JOIN HB_MANAGER M (NOLOCK) ON U.UserGuid = M.UserId
WHERE M.ManagerGuid = @BuyerUid
--扣买家的点券
DECLARE @N VARCHAR(50)
SET @N = @SRCID+'_N'
INSERT @TBLPAY
EXEC P_HBPAYACCOUNT_CONSUMEPOINT @BuyerPass9Name,@N,@BuyPrice,@NOW
IF NOT EXISTS(SELECT 1 FROM @TBLPAY WHERE CODE='5200')
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6402
RETURN;
END
--给卖家点券$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--获取Pass9Name
DECLARE @SellerPass9Name varchar(100)
SELECT @SellerPass9Name = Pass9Name FROM dbo.HB_User U
INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId
WHERE M.ManagerGuid = @SellerId
DECLARE @S VARCHAR(50)
SET @S = @SRCID+'_S'
EXEC P_HbPayAccount_Pay @SellerPass9Name,-1,@S,0,0,@BuyPrice,@Result
--PRINT @Result
IF @Result <> 5200
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6402
RETURN;
END
END
ELSE---不是一口价
BEGIN
IF @BidCount = 0--是否是第一次竞拍
BEGIN
IF @BuyPrice = @M
BEGIN
--PRINT 2
UPDATE [dbo].[HB_AuctionHouse] SET
[BuyerId] = @BuyerId,
BuyerName = @BuyerName,
[BuyPrice] = @BuyPrice,
[LimitTime] = DATEADD(n,5,[LimitTime]) ,
BidCount = BidCount + 1
WHERE
[Idx] = @Idx
--PRINT 3
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6406
RETURN;
END
END
ELSE IF @BidCount > 0 --不是第一次竞拍
BEGIN
IF @BuyPrice > @M
BEGIN
--退回上一个竞价者的钱$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--获取上一个买家的Pass9Name
SELECT @BeforePass9Name = Pass9Name FROM dbo.HB_User U
INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId
WHERE M.ManagerGuid = @BeforeBuyerId
EXEC P_HbPayAccount_Pay @BeforePass9Name,-1,@SRCID,0,0,@M,@Result
IF @Result <> 5200
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6402
RETURN;
END
--------------------给上一个竞价者发一个邮件，通知竞标失败
--扣本次竞价者的钱放到后面
UPDATE [dbo].[HB_AuctionHouse] SET
[BuyerId] = @BuyerId,
BuyerName = @BuyerName,
[BuyPrice] = @BuyPrice,
[LimitTime] = DATEADD(n,5,[LimitTime]) ,
BidCount = BidCount + 1
WHERE
[Idx] = @Idx AND BuyPrice < @BuyPrice
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6407
RETURN;
END
END
--对玩家游戏币验证,扣买家的点券$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--
SELECT @BuyerPass9Name = Pass9Name FROM dbo.HB_User U
INNER JOIN HB_MANAGER M ON U.UserGuid = M.UserId
WHERE M.ManagerGuid = @BuyerUid
INSERT @TBLPAY
EXEC P_HBPAYACCOUNT_CONSUMEPOINT @BuyerPass9Name,@SRCID,@BuyPrice,@NOW
IF NOT EXISTS(SELECT 1 FROM @TBLPAY WHERE CODE='5200')
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6402
RETURN;
END
END
END
ELSE--游戏币
BEGIN
IF @BidCount = 0--是否是第一次竞拍
BEGIN
IF @BuyPrice = @M
BEGIN
--PRINT 2
UPDATE [dbo].[HB_AuctionHouse] SET
[BuyerId] = @BuyerId,
BuyerName = @BuyerName,
[BuyPrice] = @BuyPrice,
[LimitTime] = DATEADD(n,5,[LimitTime]) ,
BidCount = BidCount + 1
WHERE [Idx] = @Idx
--PRINT 3
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6406
RETURN;
END
END
ELSE IF @BidCount > 0 --不是第一次竞拍
BEGIN
IF @BuyPrice > @M
BEGIN
--退回上一个竞价者的钱
UPDATE HB_ManagerProperty SET
[Money] = [Money] + @M
WHERE ManagerId = CAST(@BeforeBuyerId AS UNIQUEIDENTIFIER)
--------------------给上一个竞价者发一个邮件，通知竞标失败
--扣本次竞价者的钱放到后面
UPDATE [dbo].[HB_AuctionHouse] SET
[BuyerId] = @BuyerId,
BuyerName = @BuyerName,
[BuyPrice] = @BuyPrice,
[LimitTime] = DATEADD(n,5,[LimitTime]) ,
BidCount = BidCount + 1
WHERE
[Idx] = @Idx AND BuyPrice < @BuyPrice
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6407
RETURN;
END
END
--对玩家游戏币验证
IF EXISTS (SELECT * FROM HB_ManagerProperty WHERE ManagerId = @BuyerUid AND [Money] >= @BuyPrice)
BEGIN
UPDATE HB_ManagerProperty
SET [Money] = [Money] - @BuyPrice
WHERE ManagerId = @BuyerUid
END
ELSE--没有足够的游戏币
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK
SELECT 6408
RETURN;
END
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRAN BIDUPTRAN
END
SELECT 0
RETURN 0
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
