177 lines
3.9 KiB
Transact-SQL
177 lines
3.9 KiB
Transact-SQL
|
|
-- CREATE PROCEDURE [dbo].[TRN_FreeTokensCredit]
|
|
|
|
|
|
-- @UserId bigint
|
|
-- ,@AccountId bigint
|
|
-- ,@Amount decimal(22,8)
|
|
-- ,@AmountUSD decimal(18,2)
|
|
-- ,@ExternalReferenceId varchar(64) = NULL
|
|
-- ,@OperatorId int
|
|
-- ,@Comment nvarchar(1000)
|
|
-- ,@WalletAddress varchar(100)
|
|
-- ,@PendingId bigint
|
|
-- ,@TransId bigint OUTPUT
|
|
|
|
|
|
|
|
--AS
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
----- Credit User with Free Tokens Bonus after registration
|
|
-----
|
|
----- Yoav Pinkas (c) 2018-02-12
|
|
------------------------------------------------------------------
|
|
|
|
--/*
|
|
|
|
------ Example:
|
|
|
|
--declare @UserId bigint
|
|
--select top 1 @UserId = UserId FROM Users where UserId > 4 and UserTypeId = 0
|
|
|
|
--declare @AccountId bigint
|
|
--select top 1 @AccountId = AccountId FROM Accounts where UserId = @UserId
|
|
--And [Symbol] = 'KNOKS'
|
|
|
|
--select @UserId UserId, @AccountId AccountId
|
|
|
|
|
|
|
|
--DECLARE @RC int
|
|
--DECLARE @Amount decimal(22,8) = 5200
|
|
--DECLARE @AmountUSD decimal(18,2) = 150
|
|
--DECLARE @ExternalReferenceId varchar(64)
|
|
--DECLARE @OperatorId int
|
|
--DECLARE @Comment nvarchar(1000) = 'Free Tokens Bonus - 1st time after registration'
|
|
--DECLARE @WalletAddress varchar(100)
|
|
--DECLARE @PendingId bigint
|
|
--DECLARE @TransId bigint
|
|
|
|
--SET ExternalReferenceId = 'BONUS_' + Cast(AccountId as varchar(20))
|
|
|
|
|
|
--EXECUTE @RC = [dbo].[TRN_FreeTokensCredit]
|
|
-- @UserId
|
|
-- ,@AccountId
|
|
-- ,@Amount
|
|
-- ,@AmountUSD
|
|
-- ,@ExternalReferenceId
|
|
-- ,@OperatorId
|
|
-- ,@Comment
|
|
-- ,@WalletAddress
|
|
-- ,@PendingId
|
|
-- ,@TransId OUTPUT
|
|
|
|
|
|
-- select * from [dbo].[AccountTransactions] where accountId = @AccountId order by 1 desc
|
|
-- select * from Accounts where accountId = @AccountId
|
|
|
|
|
|
|
|
|
|
|
|
--*/
|
|
|
|
|
|
--SET NOCOUNT ON
|
|
|
|
|
|
--DECLARE @CurrentBalance Decimal(22,8),
|
|
-- @CurrentBonusBalance Decimal(22,8),
|
|
-- @TranStarted bit,
|
|
-- @RC int,
|
|
-- @ErrorMessage nvarchar(1000),
|
|
-- @AccountTransactionTypeId smallint
|
|
|
|
|
|
--BEGIN TRY
|
|
-- SET @RC = 0
|
|
-- SET @TranStarted = 0
|
|
|
|
-- SET @AccountTransactionTypeId = 1 ---FreeTokensCredit
|
|
|
|
|
|
|
|
-- IF not exists(SELECT 1 FROM Accounts WITH (NOLOCK) WHERE [UserId] = @UserId and AccountId=@AccountId)
|
|
-- BEGIN
|
|
-- SET @RC = 2
|
|
-- SET @ErrorMessage = 'User not found for this Account (UserId is NULL value). @AccountId = ' + Cast(@AccountId AS Varchar(20))
|
|
-- RAISERROR (@ErrorMessage,16,1);
|
|
-- END
|
|
|
|
-- IF @ExternalReferenceId Is Null
|
|
-- BEGIN
|
|
-- SET @RC = 3
|
|
-- SET @ErrorMessage = 'External Reference cannot be null'
|
|
-- RAISERROR (@ErrorMessage,16,1);
|
|
-- END
|
|
|
|
-- If EXISTS(SELECT 1 FROM AccountTransactions WHERE [ExternalReferenceId] = @ExternalReferenceId)
|
|
-- BEGIN
|
|
-- SET @RC = 4
|
|
-- SET @ErrorMessage = 'External Reference already registered'
|
|
-- RAISERROR (@ErrorMessage,16,1);
|
|
-- END
|
|
|
|
-- BEGIN TRANSACTION
|
|
-- SET @TranStarted = 1
|
|
|
|
|
|
-- SELECT @CurrentBalance = IsNull(CurrentBalance,0) ,
|
|
-- @CurrentBonusBalance = IsNull(CurrentBonusBalance,0 )
|
|
-- FROM Accounts WITH (ROWLOCK)
|
|
-- WHERE AccountId = @AccountId
|
|
|
|
|
|
|
|
-- SET @CurrentBalance = @CurrentBalance + @Amount
|
|
-- set @CurrentBonusBalance =@CurrentBonusBalance +@Amount
|
|
|
|
|
|
|
|
|
|
|
|
|
|
---- TODO: Set parameter values here.
|
|
|
|
-- EXECUTE @RC = [dbo].[TRN_AccountTransactions_Add]
|
|
-- @UserId
|
|
-- ,@AccountId
|
|
-- ,@AccountTransactionTypeId
|
|
-- ,@Amount
|
|
-- ,@AmountUSD
|
|
-- ,@CurrentBalance
|
|
-- ,@CurrentBonusBalance
|
|
-- ,@ExternalReferenceId
|
|
-- ,@OperatorId
|
|
-- ,@Comment
|
|
-- ,@WalletAddress
|
|
-- ,@PendingId
|
|
-- ,@TransId OUTPUT
|
|
|
|
|
|
|
|
|
|
-- SELECT @TransId AS TransId,
|
|
-- @CurrentBalance AS CurrentBalance,
|
|
-- @CurrentBonusBalance AS CurrentBonusBalance
|
|
|
|
-- IF( @TranStarted = 1 )
|
|
-- COMMIT TRANSACTION
|
|
|
|
-- RETURN 0
|
|
|
|
--END TRY
|
|
--BEGIN CATCH
|
|
-- IF( @TranStarted = 1 )
|
|
-- ROLLBACK TRANSACTION
|
|
|
|
-- EXEC [USP_LogError]
|
|
-- EXEC [USP_RethrowError]
|
|
|
|
-- IF @RC = 0
|
|
-- SET @RC = 1
|
|
-- RETURN @RC
|
|
--END CATCH |