USE [SA_BETA_TRADEDB_0002]
GO

/****** Object: StoredProcedure [PaGamePublic].[uspAutoBuyBiddingFromWorldMarket] Script Date: 8/3/2025 11:00:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [PaGamePublic].[uspAutoBuyBiddingFromWorldMarket]
@nationCode INT,
@serverNo INT,
@userNo BIGINT, -- User making the purchase
@BiddingRate FLOAT = 1.0, -- Bidding range
@buyCount BIGINT = 1, -- Quantity to purchase
@weightPerOne BIGINT = 5, -- Weight of each item

@walletMoney BIGINT OUTPUT, -- Amount of money in wallet after purchase
@realBuyCount BIGINT OUTPUT, -- Actual quantity purchased
@totalMoneyCount BIGINT OUTPUT, -- Total purchase price
@sellNo BIGINT OUTPUT, -- Sales record number
@sellUserNo BIGINT OUTPUT, -- Seller user number
@sellUserId NVARCHAR(60) OUTPUT, -- Seller user ID
@sellLeftCount BIGINT OUTPUT, -- Amount remaining after sale
@raceCount BIGINT OUTPUT, -- Number of bidders
@stopType INT OUTPUT, -- Transaction interruption type, 0: Normal, 1: No bids, 2: Purchase failed

@symNo NVARCHAR(50) OUTPUT,
@rv INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET LOCK_TIMEOUT 3000;
SET XACT_ABORT ON;

DECLARE @buyKeyType INT;
DECLARE @buyMainKey INT;
DECLARE @buySubKey INT;
DECLARE @buyChooseKey INT;
DECLARE @metarialMainKey INT = 0;
DECLARE @buyerMetarialCount BIGINT = 0;
DECLARE @buyPrice BIGINT;
DECLARE @itemFound BIT = 0;
DECLARE @currentTry INT = 0;
DECLARE @maxTries INT = 10; -- Set a maximum retry limit to avoid infinite loops

BEGIN TRY
-- Attempts to purchase items
WHILE @itemFound = 0 AND @currentTry < @maxTries
BEGIN
SET @currentTry = @currentTry + 1;

-- Select the cheapest available item from regular users (excluding admin and the buyer user himself)
SELECT TOP 1
@buyKeyType = _keyType,
@buyMainKey = _mainKey,
@buySubKey = _subKey,
@buyChooseKey = _chooseSubKey,
@buyPrice = _pricePerOne,
@sellUserNo = _userNo
FROM SA_BETA_TRADEDB_0002.PaGamePrivate.TblWorldMarketBiddingSell
WHERE _userNo <> 1 AND _userNo <> @userNo AND _leftCount > 0 -- Delete items from both the administrator and the user themselves
ORDER BY _pricePerOne ASC;

-- Verificação se algum item foi selecionado
IF @buyKeyType IS NULL OR @buyMainKey IS NULL OR @buySubKey IS NULL OR @buyChooseKey IS NULL OR @buyPrice IS NULL
BEGIN
PRINT 'There are no items available for purchase!';
SET @rv = -17;
SET @symNo = N'eErrNoHaveSellBiddingInfoWorldMarket';
GOTO LABEL_END;
END

-- Checks if the item has already been sold and if the seller is not the buyer
IF EXISTS (
SELECT 1
FROM PaGamePrivate.TblWorldMarketBiddingSell
WHERE _keyType = @buyKeyType AND _mainKey = @buyMainKey AND _subKey = @buySubKey AND _chooseSubKey = @buyChooseKey AND _leftCount > 0 AND _userNo <> @userNo
)
BEGIN
-- Try to make the purchase
BEGIN TRANSACTION;

-- Resetting variables
SET @rv = 0;
SET @symNo = N'';
SET @realBuyCount = 0;
SET @totalMoneyCount = 0;
SET @sellNo = 0;
SET @stopType = 0;
SET @walletMoney = 0;
SET @raceCount = 0;

DECLARE @beforeCount1 BIGINT = 0;
DECLARE @afterCount1 BIGINT = 0;
DECLARE @beforeCount2 BIGINT = 0;
DECLARE @afterCount2 BIGINT = 0;
DECLARE @beforeCount3 BIGINT = 0;
DECLARE @afterCount3 BIGINT = 0;
DECLARE @beforeCount4 BIGINT = 0;
DECLARE @afterCount4 BIGINT = 0;

DECLARE @boughtCount BIGINT = 0;
DECLARE @leftCount BIGINT = 0;
DECLARE @registerMoneyCount BIGINT = 0;
DECLARE @retryBiddingNo BIGINT = 0;

DECLARE @pushWeight BIGINT = 0;
DECLARE @maxWeight BIGINT;
SELECT @maxWeight = _weight FROM PaGamePrivate.TblWorldMarketWalletInfo WHERE _nationCode = @nationCode AND _serverNo = @serverNo AND _userNo = @userNo;

DECLARE @buyBiddingTable TABLE
(
_sellNo BIGINT,
_leftCount BIGINT,
_pricePerOne BIGINT,
_chooseSubKey INT,
_needMaterialCount INT,
_nationCode INT,
_serverNo INT,
_userNo INT,
_rank BIGINT
);

DECLARE @isGameMaster BIT = 0;
EXEC PaGamePublic.uspIsGameMaster__XXX @nationCode, @serverNo, @userNo, @isGameMaster OUTPUT;
IF (0 = @isGameMaster)
BEGIN
EXEC @rv = PaGamePublic.uspIsMarketOpen__XXX @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 1;
GOTO LABEL_END;
END;
END;

EXEC @rv = PaGamePublic.uspIsBlockItem__XXX @buyKeyType, @buyMainKey, @buySubKey, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 2;
GOTO LABEL_END;
END;

EXEC @rv = PaGamePublic.uspIsBlockUser__XXX @nationCode, @serverNo, @userNo, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 3;
GOTO LABEL_END;
END;

EXEC @rv = PaGamePublic.uspCheckMyWallet_XXX @nationCode, @serverNo, @userNo, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 4;
GOTO LABEL_END;
END;

-- Check if there is any item to retry
IF (0 < @retryBiddingNo)
BEGIN
UPDATE PaGamePrivate.TblWorldMarketBiddingBuy
SET @boughtCount = _boughtCount, _boughtCount = 0
WHERE (_buyNo = @retryBiddingNo) AND (_keyType = @buyKeyType) AND (_mainKey = @buyMainKey) AND (_subKey = @buySubKey) AND (_chooseSubKey = @buyChooseKey) AND (_nationCode = @nationCode) AND (_serverNo = @serverNo) AND (_userNo = @userNo);
IF (@@ROWCOUNT <> 1)
BEGIN
SET @rv = -5;
SET @symNo = N'eErrNoNoCalculateItemWorldMarket';
GOTO LABEL_END;
END;

IF (0 < @boughtCount)
BEGIN
SET @pushWeight = @weightPerOne * @boughtCount;
EXEC @rv = PaGamePublic.uspPushWeightToMyWallet__XXX @nationCode, @serverNo, @userNo, @pushWeight, @maxWeight, 1, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 6;
GOTO LABEL_END;
END;

EXEC @rv = PaGamePublic.uspPushItemToMyWallet__XXX @nationCode, @serverNo, @userNo, @buyKeyType, @buyMainKey, @buyChooseKey, 0, @boughtCount, @beforeCount1 OUTPUT, @afterCount1 OUTPUT, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 7;
GOTO LABEL_END;
END;
END;

SELECT @registerMoneyCount = _registerMoneyCount, @leftCount = _leftCount
FROM PaGamePrivate.TblWorldMarketBiddingBuy
WHERE (_buyNo = @retryBiddingNo) AND (_keyType = @buyKeyType) AND (_mainKey = @buyMainKey) AND (_subKey = @buySubKey) AND (_chooseSubKey = @buyChooseKey) AND (_nationCode = @nationCode) AND (_serverNo = @serverNo) AND (_userNo = @userNo);
IF (@@ROWCOUNT <> 1)
BEGIN
SET @rv = -8;
SET @symNo = N'eErrNoWithdrawItemFail';
GOTO LABEL_END;
END;

DELETE PaGamePrivate.TblWorldMarketBiddingBuy
WHERE (_buyNo = @retryBiddingNo) AND (_boughtCount = 0);
IF (@@ROWCOUNT <> 1)
BEGIN
SET @rv = -9;
SET @symNo = N'eErrNoWithdrawItemFail';
GOTO LABEL_END;
END;

IF (0 < @registerMoneyCount)
BEGIN
EXEC @rv = PaGamePublic.uspPushItemToMyWallet__XXX @nationCode, @serverNo, @userNo, 0, 1, 0, 0, @registerMoneyCount, @beforeCount2 OUTPUT, @afterCount2 OUTPUT, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 10;
GOTO LABEL_END;
END;
END;
END;

IF (@buySubKey = @buyChooseKey AND 0 < @buyerMetarialCount)
BEGIN
SET @rv = -11;
SET @symNo = N'eErrNoWorldMarketBuybiddingFail';
GOTO LABEL_END;
END;

DECLARE @metarialPricePerOneStock BIGINT = 0;
EXEC @rv = PaGamePublic.uspGetMaterialPrice_XXX 0, @metarialMainKey, 0, @metarialPricePerOneStock OUTPUT, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 12;
GOTO LABEL_END;
END;
DECLARE @buyerMetarialPrice BIGINT = @buyerMetarialCount * @metarialPricePerOneStock;

DECLARE @originalCount BIGINT = @buyCount;
DECLARE @pricePerOneStock BIGINT = 0;
DECLARE @minPriceOneStock BIGINT = 0;
DECLARE @maxPriceOneStock BIGINT = 0;

SELECT @pricePerOneStock = _pricePerOne, @minPriceOneStock = _minPrice, @maxPriceOneStock = _maxPrice
FROM PaGamePrivate.TblWorldMarket
WHERE (@buyKeyType = _keyType) AND (@buyMainKey = _mainKey) AND (@buySubKey = _subKey) AND (1 = _isDisplay);
IF (1 <> @@ROWCOUNT)
BEGIN
SET @rv = -13;
SET @symNo = N'eErrNoWorldMarketBuybiddingFail';
GOTO LABEL_END;
END;

DECLARE @highBiddingPrice BIGINT = @pricePerOneStock * (1.0 + @BiddingRate);
IF (@maxPriceOneStock < @highBiddingPrice)
BEGIN
SET @highBiddingPrice = @maxPriceOneStock;
END;
IF (@highBiddingPrice < @buyPrice)
BEGIN
SET @rv = -14;
SET @symNo = N'eErrNoWorldMarketBuybiddingFail';
GOTO LABEL_END;
END;

DECLARE @lowBiddingPrice BIGINT = @pricePerOneStock * (1.0 - @BiddingRate);
IF (@lowBiddingPrice < @minPriceOneStock)
BEGIN
SET @lowBiddingPrice = @minPriceOneStock;
END;
IF (@buyPrice < @lowBiddingPrice)
BEGIN
SET @rv = -15;
SET @symNo = N'eErrNoWorldMarketBuybiddingFail';
GOTO LABEL_END;
END;

DECLARE @checkPrice BIGINT = 0;
SET @checkPrice = @buyPrice + @buyerMetarialPrice;

IF NOT EXISTS (
SELECT *
FROM PaGamePrivate.TblWorldMarketWallet
WHERE (_nationCode = @nationCode) AND (_serverNo = @serverNo) AND (_userNo = @userNo) AND (_keyType = 0) AND (_mainKey = 1) AND (_subKey = 0) AND ((@checkPrice * @buyCount) <= _count)
)
BEGIN
SET @rv = -16;
SET @symNo = N'eErrNoBusinessNotEnoughMoney';
GOTO LABEL_END;
END;

DECLARE @count BIGINT = 0;
DECLARE @sellBiddingMoneyCount BIGINT = 0;

DECLARE @chooseSubKey INT = 0;
DECLARE @sellerMaterialCount BIGINT = 0;
DECLARE @sellNationCode INT = 0;
DECLARE @sellServerNo INT = 0;

INSERT INTO @buyBiddingTable (_sellNo, _leftCount, _pricePerOne, _chooseSubKey, _needMaterialCount, _nationCode, _serverNo, _userNo, _rank)
SELECT _sellNo, _leftCount, _pricePerOne, _chooseSubKey, _needMaterialCount, _nationCode, _serverNo, _userNo, RANK() OVER (ORDER BY _pricePerOne ASC) AS _rank
FROM PaGamePrivate.TblWorldMarketBiddingSell
WHERE (@buyKeyType = _keyType) AND (@buyMainKey = _mainKey) AND (@buySubKey = @buySubKey) AND (0 < _leftCount) AND (_pricePerOne <= @buyPrice) AND (@lowBiddingPrice <= _pricePerOne) AND (_userNo <> 1) AND (_userNo <> @userNo);

SELECT TOP 1 @sellNo = _sellNo, @count = _leftCount, @sellBiddingMoneyCount = _pricePerOne, @chooseSubKey = _chooseSubKey, @sellerMaterialCount = _needMaterialCount, @sellNationCode = _nationCode, @sellServerNo = _serverNo, @sellUserNo = _userNo
FROM @buyBiddingTable
ORDER BY _pricePerOne ASC, _sellNo ASC;
IF (1 <> @@ROWCOUNT)
BEGIN
SET @rv = 0;
SET @stopType = 1;
PRINT 'There are no items available for purchase!';
GOTO LABEL_END;
END;

SELECT @raceCount = COUNT(*)
FROM @buyBiddingTable
WHERE _rank = 1;

DECLARE @sellerMetarialPrice BIGINT = @sellerMaterialCount * @metarialPricePerOneStock;

IF (@count <= @buyCount)
BEGIN
SET @realBuyCount = @count;
END;
ELSE
BEGIN
SET @realBuyCount = @buyCount;
END;

SET @sellLeftCount = @count - @realBuyCount;

UPDATE PaGamePrivate.TblWorldMarketBiddingSell
SET _leftCount = _leftCount - @realBuyCount, _soldCount = _soldCount + @realBuyCount, _accumulateMoneyCount = _accumulateMoneyCount + (@realBuyCount * (_pricePerOne + @sellerMetarialPrice))
WHERE (@sellNo = _sellno) AND (@realBuyCount <= _leftCount);
IF (1 <> @@ROWCOUNT)
BEGIN
SET @rv = 0;
SET @stopType = 2;
GOTO LABEL_END;
END;

SET @totalMoneyCount = (@sellBiddingMoneyCount + @buyerMetarialPrice) * @realBuyCount;

DECLARE @tradeMoneyCount BIGINT = @sellBiddingMoneyCount * @realBuyCount;
EXEC @rv = PaGamePublic.uspVariedTradeCount_XXX @buyKeyType, @buyMainKey, @buySubKey, @realBuyCount, @tradeMoneyCount, 1, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 19;
GOTO LABEL_END;
END;

EXEC @rv = PaGamePublic.uspPopItemFromMyWallet__XXX @nationCode, @serverNo, @userNo, 0, 1, 0, 0, @totalMoneyCount, @beforeCount3 OUTPUT, @afterCount3 OUTPUT, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 20;
GOTO LABEL_END;
END;

SET @pushWeight = @weightPerOne * @realBuyCount;
EXEC @rv = PaGamePublic.uspPushWeightToMyWallet__XXX @nationCode, @serverNo, @userNo, @pushWeight, @maxWeight, 1, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 21;
GOTO LABEL_END;
END;

EXEC @rv = PaGamePublic.uspPushItemToMyWallet__XXX @nationCode, @serverNo, @userNo, @buyKeyType, @buyMainKey, @buyChooseKey, 0, @realBuyCount, @beforeCount4 OUTPUT, @afterCount4 OUTPUT, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 22;
GOTO LABEL_END;
END;

EXEC @rv = PaGamePublic.uspChangeItemWalletParam__XXX @nationCode, @serverNo, @userNo, @sellNationCode, @sellServerNo, @sellUserNo, @buyKeyType, @buyMainKey, @buyChooseKey, @realBuyCount, @symNo OUTPUT;
IF (0 <> @rv)
BEGIN
SET @rv = @rv * 100 - 23;
GOTO LABEL_END;
END;

SELECT @sellUserId = _userId
FROM PaGamePrivate.TblWorldMarketWalletInfo
WHERE (_nationCode = @sellNationCode) AND (_serverNo = @sellServerNo) AND (_userNo = @sellUserNo);

PRINT 'Purchase completed successfully';
SET @itemFound = 1;
END
ELSE
BEGIN
PRINT 'Item already sold, trying next item...';
END
END

LABEL_END:
IF (0 = @rv)
BEGIN
COMMIT TRANSACTION;
END
ELSE
BEGIN
ROLLBACK TRANSACTION;
END

-- After making the purchase, remove the items from the admin wallet, except the item with _mainKey = 1
IF @userNo = 1
BEGIN
DELETE FROM PaGamePrivate.TblWorldMarketWallet
WHERE _userNo = 1 AND _mainKey != 1;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
--PRINT 'Error making purchase in CATCH block';
SELECT ERROR_MESSAGE() AS CatchErrorMessage, ERROR_LINE() AS ErrorLine, ERROR_NUMBER() AS ErrorNumber, ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH
END;
GO
Последнее редактирование:Сегодня в 3:41 утра
В настоящее время специализируюсь на администрировании баз данных Oracle и SQL Server, а в свободное время проявляю большой интерес к программированию.
Нравиться ОтвечатьОтчет
Сегодня в 19:03
Новый
Добавить закладку
#5
ZHH950219
ZHH950219
Новичок-заклинатель
Присоединился17 февраля 2024 г.
Сообщения46
Оценка реакции20
Поддерживает ли он Android-клиенты?
Нравиться ОтвечатьОтчет



Напишите свой ответ...

Ответить на сообщение
Прикрепить файлы
Пользователи, просматривающие эту ветку
орохимару2al9zko
Всего: 2 (участников: 2, гостей: 0)
Спонсор RaGEZONE
Гиперфильтр
О RaGEZONE
RaGEZONE® — ведущий форум для разработчиков MMO, MMORPG и мобильных игр. Мы предоставляем исполняемые файлы, исходные коды и обучающие материалы для ваших любимых игр, делая его незаменимым ресурсом для разработчиков и фанатов. Вы также можете найти и поделиться приватными серверами, формируя активное игровое сообщество.
Ста