You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
337 lines
27 KiB
337 lines
27 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_InsertRewardPoints] Script Date: 4/4/2024 4:50:45 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
ALTER PROC [dbo].[proc_InsertRewardPoints] (
|
|
@Flag VARCHAR(20)
|
|
,@TranId BIGINT = NULL
|
|
,@CustomerId BIGINT = NULL
|
|
,@ReferralId BIGINT = NULL
|
|
,@rewardPoints INT = NULL
|
|
,@rewardValue MONEY = NULL
|
|
,@type VARCHAR(200) = NULL
|
|
,@pageSize VARCHAR(50) = NULL
|
|
,@pageNumber VARCHAR(50) = NULL
|
|
,@sortBy VARCHAR(50) = NULL
|
|
,@sortOrder VARCHAR(50) = NULL
|
|
,@newPoints INT = NULL
|
|
,@oldPoints INT = NULL
|
|
,@promotionType VARCHAR(50) = NULL
|
|
,@user VARCHAR(50) = NULL
|
|
,@isActive BIT = NULL
|
|
,@rowId INT = NULL
|
|
)
|
|
AS
|
|
;
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
DECLARE @table VARCHAR(MAX)
|
|
,@select_field_list VARCHAR(MAX)
|
|
,@extra_field_list VARCHAR(MAX)
|
|
,@sql_filter VARCHAR(MAX)
|
|
,@ACC_NUM VARCHAR(30)
|
|
|
|
-- #1526 - Post production fixes for Redeem & Earn
|
|
BEGIN
|
|
DECLARE @sourceCustomerId INT, @destinationCustomerId INT, @code NVARCHAR(50), @runningBalance MONEY, @totalDrAmount MONEY
|
|
, @totalCrAmount MONEY , @transactionPoints INT, @registrationPoints INT, @EquivalentPointsForOne INT, @AvailableBalance MONEY
|
|
|
|
SELECT @registrationPoints = Points
|
|
FROM TBL_PROMOTION_SETUP (NOLOCK)
|
|
WHERE PromotionType = 'REGISTRATION'
|
|
AND IsActive = 1
|
|
|
|
SELECT @transactionPoints = Points
|
|
FROM TBL_PROMOTION_SETUP (NOLOCK)
|
|
WHERE PromotionType = 'TRANSACTION'
|
|
AND IsActive = 1
|
|
|
|
SELECT @EquivalentPointsForOne = Points
|
|
FROM TBL_PROMOTION_MASTER_SETUP (NOLOCK)
|
|
WHERE IsActive = 1
|
|
|
|
DECLARE @orgReferral INT, @orgCustomerId INT
|
|
|
|
IF @Flag = 'REGISTER'
|
|
BEGIN
|
|
--@CustomerId = Customer who is newly registering in the system
|
|
--@ReferralId = Customer who is already in the system/ who have referred @CustomerId
|
|
--EXEC proc_InsertRewardPoints @Flag = 'REGISTER', @CustomerId = 1, @ReferralId = 2
|
|
IF EXISTS(SELECT 'X' FROM customerMaster WHERE customerId = @CustomerId)
|
|
BEGIN
|
|
INSERT INTO Customer_Promotion(sourceCustomerId, destinationCustomerId, code, codeType, referenceId, rewardType, amount, createdDate, status, approvedDate, tranType, runningBalance, EquivalentPoints)
|
|
SELECT @ReferralId,@CustomerId, membershipId, 'REGISTRATION', @ReferralId, 'REFER_EARN', @registrationPoints/@EquivalentPointsForOne, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@CustomerId) + (@registrationPoints/@EquivalentPointsForOne), @registrationPoints
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @CustomerId
|
|
|
|
UNION
|
|
|
|
SELECT @CustomerId,@ReferralId, membershipId, 'REGISTRATION', @ReferralId, 'REFER_EARN', @registrationPoints/@EquivalentPointsForOne, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@ReferralId) + (@registrationPoints/@EquivalentPointsForOne), @registrationPoints
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @ReferralId
|
|
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @registrationPoints WHERE customerId = @CustomerId
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @registrationPoints WHERE customerId = @ReferralId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
IF EXISTS(SELECT 'X' FROM REFERRAL_AGENT_WISE WHERE ROW_ID = @CustomerId)
|
|
BEGIN
|
|
SET @orgCustomerId = @ReferralId
|
|
SET @orgReferral = @CustomerId
|
|
|
|
INSERT INTO Customer_Promotion(sourceCustomerId, destinationCustomerId, code, codeType, referenceId, rewardType, amount, createdDate, status, approvedDate, tranType, runningBalance, EquivalentPoints)
|
|
SELECT @orgCustomerId,@orgReferral, membershipId, 'REGISTRATION', @ReferralId, 'REFER_EARN', @registrationPoints/@EquivalentPointsForOne, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@orgCustomerId) + (@registrationPoints/@EquivalentPointsForOne), @registrationPoints
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @orgCustomerId
|
|
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @registrationPoints WHERE customerId = @orgCustomerId
|
|
END
|
|
END
|
|
|
|
END
|
|
ELSE IF @Flag = 'TRANSACTION'
|
|
BEGIN
|
|
--@CustomerId = Customer who is doing transaction
|
|
--EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION', @CustomerId = 1, @TranId = 1234567890
|
|
IF EXISTS(SELECT 'X' FROM REMITTRAN(NOLOCK) WHERE ID = @TranID)
|
|
BEGIN
|
|
|
|
print 'A';
|
|
IF NOT EXISTS(SELECT 'X' FROM Customer_Promotion (NOLOCK) WHERE destinationCustomerId = @CustomerId and codeTYPE = 'REGISTRATION' and rewardType='REFER_EARN')
|
|
BEGIN
|
|
SELECT @sourceCustomerId = sourceCustomerId, @destinationCustomerId = ISNULL(destinationCustomerId,0)
|
|
FROM Customer_Promotion (NOLOCK)
|
|
WHERE destinationCustomerId = @CustomerId
|
|
and codeTYPE = 'REGISTRATION' and rewardType='REFER_EARN';
|
|
|
|
IF ISNULL(@destinationCustomerId, 0) <> 0
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 'X' FROM Customer_Promotion(NOLOCK) WHERE codeTYPE = 'FIRST_TXN' AND destinationCustomerId = @CustomerId)
|
|
BEGIN
|
|
INSERT INTO Customer_Promotion(sourceCustomerId, destinationCustomerId, code, codeType, referenceId, rewardType, amount, createdDate, status, approvedDate, tranType, runningBalance, EquivalentPoints)
|
|
SELECT @sourceCustomerId, @destinationCustomerId, membershipId, 'FIRST_TXN', @TranId, 'REFER_EARN', @transactionPoints/@EquivalentPointsForOne, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@destinationCustomerId) + (@transactionPoints/@EquivalentPointsForOne), @transactionPoints
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @destinationCustomerId
|
|
|
|
UNION
|
|
|
|
SELECT @destinationCustomerId, @sourceCustomerId, membershipId, 'FIRST_TXN', @TranId, 'REFER_EARN', @transactionPoints/@EquivalentPointsForOne, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@sourceCustomerId) + (@transactionPoints/@EquivalentPointsForOne), @transactionPoints
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @sourceCustomerId
|
|
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @transactionPoints WHERE customerId = @sourceCustomerId
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @transactionPoints WHERE customerId = @destinationCustomerId
|
|
END
|
|
END
|
|
|
|
ELSE
|
|
BEGIN
|
|
SELECT @sourceCustomerId = sourceCustomerId, @destinationCustomerId = ISNULL(destinationCustomerId,0)
|
|
FROM Customer_Promotion (NOLOCK)
|
|
WHERE sourceCustomerId = @CustomerId
|
|
and codeTYPE = 'REGISTRATION' and rewardType='REFER_EARN';
|
|
|
|
IF ISNULL(@sourceCustomerId, 0) <> 0 AND EXISTS(SELECT 'X' FROM REFERRAL_AGENT_WISE WHERE ROW_ID = @destinationCustomerId)
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 'X' FROM Customer_Promotion(NOLOCK) WHERE codeTYPE = 'FIRST_TXN' AND sourceCustomerId = @CustomerId)
|
|
BEGIN
|
|
INSERT INTO Customer_Promotion(sourceCustomerId, destinationCustomerId, code, codeType, referenceId, rewardType, amount, createdDate, status, approvedDate, tranType, runningBalance, EquivalentPoints)
|
|
SELECT @sourceCustomerId, @destinationCustomerId, membershipId, 'FIRST_TXN', @TranId, 'REFER_EARN', @transactionPoints/@EquivalentPointsForOne, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@sourceCustomerId) + (@transactionPoints/@EquivalentPointsForOne), @transactionPoints
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @sourceCustomerId
|
|
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @transactionPoints WHERE customerId = @sourceCustomerId
|
|
END
|
|
END
|
|
END
|
|
END
|
|
|
|
END
|
|
END
|
|
ELSE IF @Flag = 'DEBIT'
|
|
BEGIN
|
|
IF ISNULL(@rewardPoints, 0) > 0
|
|
BEGIN
|
|
--@CustomerId = Customer who is doing transaction
|
|
--EXEC proc_InsertRewardPoints @Flag = 'DEBIT', @CustomerId = 1, @rewardPoints = 100, @TranId = 1234567890
|
|
|
|
SELECT @AvailableBalance = DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@CustomerId)
|
|
|
|
IF ISNULL(@rewardPoints, 0) > ISNULL(@AvailableBalance, 0)
|
|
BEGIN
|
|
SELECT 1 Code, 'Insufficient Balance For Redeem!' msg, NULL id
|
|
RETURN;
|
|
END
|
|
|
|
INSERT INTO Customer_Promotion(sourceCustomerId, destinationCustomerId, code, codeType, referenceId, rewardType, amount, createdDate, status, approvedDate, tranType, runningBalance, EquivalentPoints)
|
|
SELECT @CustomerId, 0, membershipId, 'REDEEM', @TranId, 'REFER_EARN', @rewardPoints, GETDATE(), 1, GETDATE(), 'DR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@CustomerId) - @rewardPoints, @rewardPoints * @EquivalentPointsForOne
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @CustomerId
|
|
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) - @rewardPoints WHERE customerId = @CustomerId
|
|
END
|
|
END
|
|
ELSE IF @Flag = 'CREDIT'
|
|
BEGIN
|
|
IF ISNULL(@rewardPoints, 0) > 0
|
|
BEGIN
|
|
--@CustomerId = Customer who is doing transaction
|
|
--EXEC proc_InsertRewardPoints @Flag = 'DEBIT', @CustomerId = 1, @rewardPoints = 100, @TranId = 1234567890
|
|
|
|
SELECT @AvailableBalance = DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@CustomerId)
|
|
|
|
IF @type = 'CANCEL'
|
|
BEGIN
|
|
SELECT @rewardPoints = rewardPoints FROM remitTran WHERE id = @TranId
|
|
|
|
INSERT INTO Customer_Promotion(sourceCustomerId, destinationCustomerId, code, codeType, referenceId, rewardType, amount, createdDate, status, approvedDate, tranType, runningBalance, EquivalentPoints)
|
|
SELECT @CustomerId, 0, membershipId, 'REDEEM', @TranId, 'CANCEL_REFUND', @rewardPoints, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@CustomerId) - @rewardPoints, @rewardPoints * @EquivalentPointsForOne
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @CustomerId
|
|
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @rewardPoints WHERE customerId = @CustomerId
|
|
END
|
|
|
|
ELSE IF @type = 'REJECT'
|
|
BEGIN
|
|
SELECT @rewardPoints = rewardPoints FROM remitTranTemp WHERE id = @TranId
|
|
|
|
INSERT INTO Customer_Promotion(sourceCustomerId, destinationCustomerId, code, codeType, referenceId, rewardType, amount, createdDate, status, approvedDate, tranType, runningBalance, EquivalentPoints)
|
|
SELECT @CustomerId, 0, membershipId, 'REDEEM', @TranId, 'REJECT_REFUND', @rewardPoints, GETDATE(), 1, GETDATE(), 'CR'
|
|
, DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@CustomerId) - @rewardPoints, @rewardPoints * @EquivalentPointsForOne
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @CustomerId
|
|
|
|
UPDATE customerMaster SET bonusPoint = ISNULL(bonusPoint, 0) + @rewardPoints WHERE customerId = @CustomerId
|
|
END
|
|
END
|
|
END
|
|
ELSE IF @flag = 'GET'
|
|
BEGIN
|
|
IF @sortBy IS NULL
|
|
SET @sortBy = 'RowId'
|
|
IF @sortOrder IS NULL
|
|
SET @sortOrder = 'ASC'
|
|
SET @table = '(SELECT RowId, Points, PromotionType, IsActive FROM TBL_PROMOTION_SETUP WHERE isActive = 1)x'
|
|
|
|
SET @sql_filter = ''
|
|
|
|
SET @select_field_list ='
|
|
RowId
|
|
,Points
|
|
,PromotionType
|
|
,isActive
|
|
'
|
|
EXEC dbo.proc_paging
|
|
@table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber
|
|
END
|
|
ELSE IF @flag = 'editById'
|
|
BEGIN
|
|
SELECT * FROM TBL_PROMOTION_SETUP WHERE PromotionType = @promotionType AND isActive = 1
|
|
END
|
|
ELSE IF @flag = 'GET-MASTER'
|
|
BEGIN
|
|
IF @sortBy IS NULL
|
|
SET @sortBy = 'RowId'
|
|
IF @sortOrder IS NULL
|
|
SET @sortOrder = 'ASC'
|
|
|
|
SET @table = '(SELECT * FROM TBL_PROMOTION_MASTER_SETUP WHERE isActive = 1)x'
|
|
|
|
SET @sql_filter = ''
|
|
|
|
SET @select_field_list ='
|
|
RowId
|
|
,Points
|
|
,isActive
|
|
'
|
|
EXEC dbo.proc_paging
|
|
@table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber
|
|
END
|
|
ELSE IF @flag = 'UPDATE-POINT'
|
|
BEGIN
|
|
SELECT @oldPoints = Points FROM TBL_PROMOTION_SETUP WHERE PromotionType = @promotionType
|
|
IF EXISTS (SELECT 'X' FROM TBL_PROMOTION_SETUP WHERE PromotionType = @promotionType)
|
|
BEGIN
|
|
UPDATE TBL_PROMOTION_SETUP SET
|
|
Points = @newPoints
|
|
,UpdatedBy = @user
|
|
,UpdatedDate = GETDATE()
|
|
,IsActive = '1'
|
|
WHERE PromotionType = @promotionType
|
|
|
|
SELECT 0 error_code, 'Reward Amount updated successfully.' mes, @newPoints id
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO TBL_PROMOTION_SETUP VALUES (@newPoints, @promotionType, '1', @user, GETDATE())
|
|
|
|
SELECT 0 error_code, 'Reward type added successfully.' mes, @newPoints id
|
|
END
|
|
|
|
EXEC proc_applicationLogs 'i', NULL, 'update', 'TBL_PROMOTION_SETUP', @promotionType, @user, @oldPoints, @newPoints
|
|
|
|
END
|
|
|
|
ELSE IF @flag = 'UPDATE-MASTER'
|
|
BEGIN
|
|
--SELECT @oldPoints = points FROM TBL_PROMOTION_SETUP WHERE RowId = @rowId
|
|
|
|
UPDATE TBL_PROMOTION_MASTER_SETUP SET
|
|
Points = @newPoints
|
|
,UpdatedBy = @user
|
|
,UpdatedDate = GETDATE()
|
|
WHERE RowId = @rowId
|
|
|
|
SELECT 0 error_code, 'Reward Amount updated successfully.' mes, @newPoints id
|
|
|
|
EXEC proc_applicationLogs 'i', NULL, 'update', 'TBL_PROMOTION_MASTER_SETUP', NULL, @user, @oldPoints, @newPoints
|
|
|
|
END
|
|
|
|
ELSE IF @flag = 'DELETE'
|
|
BEGIN
|
|
BEGIN TRANSACTION
|
|
UPDATE TBL_PROMOTION_SETUP SET
|
|
IsActive = 0
|
|
,UpdatedBy = @user
|
|
,UpdatedDate=GETDATE()
|
|
WHERE PromotionType = @promotionType
|
|
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION
|
|
SELECT 0 error_code, 'Record deleted successfully.' mes, @promotionType id
|
|
|
|
EXEC proc_applicationLogs 'i', NULL, 'delete', 'TBL_PROMOTION_SETUP', @promotionType, @user, 'ACTIVE', 'INACTIVE/DELETE'
|
|
END
|
|
END
|
|
|
|
|