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.
251 lines
7.8 KiB
251 lines
7.8 KiB
USE FastMoneyPro_Remit
|
|
GO
|
|
|
|
ALTER PROC proc_SendTransactionOnBehalf (
|
|
@flag VARCHAR(20)
|
|
,@searchData VARCHAR(50) = NULL
|
|
,@searchValue VARCHAR(50) = NULL
|
|
,@countryId VARCHAR(10) = NULL
|
|
,@reciverID VARCHAR(20) = NULL
|
|
,@CustomerID VARCHAR(20) = NULL
|
|
,@locationId VARCHAR(10) = NULL
|
|
,@tranid BIGINT = NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON
|
|
IF @flag = 's'
|
|
BEGIN
|
|
|
|
DECLARE @availableBalance MONEY
|
|
|
|
IF @searchData NOT IN ('Email','Id Numbert')
|
|
BEGIN
|
|
SELECT errorCode = 1, msg = 'Enter search value'
|
|
RETURN
|
|
END
|
|
/*Check Easy remit customer block or not*/
|
|
|
|
DECLARE @custId VARCHAR(20)
|
|
SELECT TOP 1 @custId=customerId from customerMaster(nolock) where email=@searchValue and customerType='11068' ---change customer type in live
|
|
IF EXISTS (SELECT 'X' FROM CUSTOMER_BLOCK_LIST(nolock) where customerid=@custId and isBlock='Y')
|
|
BEGIN
|
|
EXEC proc_errorHandler 1,'You account is blocked.Please contact GME Support!' ,null
|
|
RETURN;
|
|
END
|
|
|
|
IF @searchData = 'email'
|
|
BEGIN
|
|
|
|
SELECT @availableBalance = ISNULL(cm.availablebalance,0)
|
|
FROM customermaster cm (NOLOCK)
|
|
WHERE cm.email = @searchValue AND CM.approvedDate IS NOT NULL AND isActive = 'Y'
|
|
|
|
IF @availableBalance IS NULL
|
|
BEGIN
|
|
SELECT errorCode=1 ,msg='Customer is not approved/exists in system.'
|
|
RETURN
|
|
END
|
|
|
|
IF @availableBalance = 0
|
|
BEGIN
|
|
SELECT errorCode=1 ,msg='You do not have sufficient balance'
|
|
RETURN
|
|
END
|
|
|
|
UPDATE customermaster SET sourceOfFund='Salary / Wages' WHERE sourceOfFund IS NULL AND email = @searchValue
|
|
|
|
SELECT
|
|
customerid,errorCode=0,
|
|
fullName = ISNULL(cm.firstname,'')
|
|
, valueId = idType
|
|
, idName = sd.detailTitle
|
|
, svalidDate = idExpiryDate
|
|
, smobile = cm.mobile
|
|
, senderAddress = cm.[address]
|
|
, nativecountryid = cm.nativecountry
|
|
, nativecountry = ctm.countryName
|
|
, semail = cm.email
|
|
, sCity = Cm.city
|
|
, IdIssuedDate = cm.idissuedate
|
|
, availablebalance = ISNULL(cm.availablebalance,0)
|
|
, sourceOfFund = cm.sourceOfFund
|
|
, sourceOfFundid = sof.valueId
|
|
, cm.walletAccountNo
|
|
, SenderIdNo = cm.idNumber
|
|
, SenderBirthDate = cm.dob
|
|
FROM customermaster cm (nolocK)
|
|
INNER JOIN staticDataValue sd (NOLOCK) ON sd.valueId = cm.idType AND sd.typeID=1300 AND ISNULL(sd.IS_DELETE ,'N')<>'Y'
|
|
INNER JOIN countrymaster ctm (nolocK) ON ctm.countryid=cm.nativecountry
|
|
LEFT JOIN dbo.staticDataValue sof (NOLOCK) ON sof.detailTitle = cm.sourceOfFund AND sof.typeID = 3900 AND ISNULL(sof.IS_DELETE ,'N')<>'Y'
|
|
WHERE cm.email = @searchValue AND cm.isActive = 'Y'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
SELECT @availableBalance = ISNULL(cm.availablebalance,0)
|
|
FROM customermaster cm (NOLOCK)
|
|
WHERE cm.idNumber = @searchValue AND CM.approvedDate IS NOT NULL AND isActive = 'Y'
|
|
|
|
IF @availableBalance IS NULL
|
|
BEGIN
|
|
SELECT errorCode=1 ,msg='Customer is not approved/exists in system.'
|
|
RETURN
|
|
END
|
|
|
|
IF @availableBalance = 0
|
|
BEGIN
|
|
SELECT errorCode=1 ,msg='You do not have sufficient balance'
|
|
RETURN
|
|
END
|
|
|
|
SELECT
|
|
customerid,errorCode=0,
|
|
fullName = ISNULL(cm.firstname,'')
|
|
, valueId = idType
|
|
, idName = sd.detailTitle
|
|
, svalidDate = idExpiryDate
|
|
, smobile = cm.mobile
|
|
, senderAddress = cm.[address]
|
|
, nativecountryid = cm.nativecountry
|
|
, nativecountry = ctm.countryName
|
|
, semail = cm.email
|
|
, sCity = Cm.city
|
|
, IdIssuedDate = cm.idissuedate
|
|
, availablebalance = ISNULL(cm.availablebalance,0)
|
|
, sourceOfFund = cm.sourceOfFund
|
|
, sourceOfFundid = sof.valueId
|
|
, cm.walletAccountNo
|
|
, SenderIdNo = cm.idNumber
|
|
, SenderBirthDate = cm.dob
|
|
FROM customermaster cm (nolocK)
|
|
INNER JOIN staticDataValue sd (NOLOCK) ON sd.valueId = cm.idType AND sd.typeID=1300 AND ISNULL(sd.IS_DELETE ,'N')<>'Y'
|
|
INNER JOIN countrymaster ctm (nolocK) ON ctm.countryid=cm.nativecountry
|
|
LEFT JOIN dbo.staticDataValue sof (NOLOCK) ON sof.detailTitle = cm.sourceOfFund AND sof.typeID = 3900 AND ISNULL(sof.IS_DELETE ,'N')<>'Y'
|
|
WHERE cm.idNumber = @searchValue AND cm.isActive = 'Y'
|
|
END
|
|
END
|
|
ELSE IF @flag = 'ddlReceiver'
|
|
BEGIN
|
|
SELECT [Key]=receiverId , Value= firstName + ' '+ ISNULL(middleName,'') + ' '+ ISNULL(lastName1,'') +''+ ISNULL(lastName2,' ')
|
|
FROM receiverInformation (nolocK)
|
|
WHERE customerId = @CustomerID AND isActive = 1
|
|
END
|
|
ELSE IF @flag = 'state'
|
|
BEGIN
|
|
IF @countryId = '151'
|
|
BEGIN
|
|
SELECT [Key] = Replace(stateName,char(9),'')
|
|
, [Value] = Replace(stateName,CHAR(9),'')
|
|
FROM dbo.countriesStates rcs WITH(NOLOCK)
|
|
INNER JOIN dbo.countryMaster cm WITH(NOLOCK) ON cm.countryCode = rcs.countryCode
|
|
WHERE countryId = @countryId
|
|
ORDER BY stateName ASC
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 'A' FROM tblServicewiseLocation (NOLOCK) WHERE countryId = @countryId)
|
|
BEGIN
|
|
SELECT [Value] = 'Any State', [Key] = '0'
|
|
RETURN
|
|
END
|
|
SELECT [Value] = location
|
|
,[Key] = rowId
|
|
FROM tblServicewiseLocation (NOLOCK)
|
|
WHERE countryId = @countryId
|
|
AND isActive = 1
|
|
END
|
|
END
|
|
ELSE IF @flag = 'substate'
|
|
BEGIN
|
|
IF @countryId = '151'
|
|
BEGIN
|
|
SELECT [Key] = REPLACE(stateName,char(9),'')
|
|
, [Value] = REPLACE(stateName,CHAR(9),'')
|
|
FROM dbo.countriesStates rcs WITH(NOLOCK)
|
|
INNER JOIN countryMaster CM (NOLOCK) ON CM.countryName = rcs.countryName
|
|
WHERE CM.countryId = @countryId
|
|
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
DECLARE @payoutPartner VARCHAR(20)
|
|
SELECT @payoutPartner = partnerId FROM tblServicewiseLocation (NOLOCK) WHERE ROWID = @locationId
|
|
--TRANGLO SDN. BHD. and country Indonesia have direct sub location defined
|
|
IF @payoutPartner = '224388' AND @countryId = '105'
|
|
BEGIN
|
|
SELECT [Key] = rowId, [Value] = subLocation
|
|
FROM tblSubLocation (NOLOCK)
|
|
WHERE locationId = 0
|
|
AND isActive = 1
|
|
AND partnerId = @payoutPartner
|
|
ORDER BY subLocation ASC
|
|
|
|
RETURN
|
|
END
|
|
IF @payoutPartner = '393901' AND @countryId = '36' -- For truemoney
|
|
BEGIN
|
|
SELECT [Key] = rowId, [Value] = subLocation
|
|
FROM tblSubLocation (NOLOCK)
|
|
WHERE locationId = 0
|
|
AND isActive = 1
|
|
AND partnerId = @payoutPartner
|
|
ORDER BY subLocation ASC
|
|
|
|
RETURN
|
|
END
|
|
IF NOT EXISTS(SELECT 'A' FROM tblSubLocation (NOLOCK) WHERE locationId = @locationId AND partnerId<>'392577')
|
|
BEGIN
|
|
SELECT [Value] = 'Any location',[Key] = '0'
|
|
RETURN
|
|
END
|
|
|
|
SELECT [Key] = rowId, [Value] = subLocation
|
|
FROM tblSubLocation (NOLOCK)
|
|
WHERE locationId = @locationId
|
|
AND isActive = 1 AND partnerId<>'392577'
|
|
ORDER BY subLocation ASC
|
|
RETURN
|
|
END
|
|
RETURN
|
|
END
|
|
ELSE IF @flag='r'
|
|
BEGIN
|
|
IF @reciverID IS NULL
|
|
BEGIN
|
|
SELECT errorCode=1, msg='Select Receiver Value'
|
|
RETURN
|
|
END
|
|
SELECT errorCode=0
|
|
, ri.firstName
|
|
, ri.middleName
|
|
, ISNULL(lastName1,' ') + ISNULL(lastName2,' ') AS lastName
|
|
, ri.city
|
|
, ri.[address]
|
|
, mobile
|
|
, cm.countryId
|
|
, cm.countryName
|
|
, receiverId
|
|
, relationship
|
|
, Relationid =sd.valueid
|
|
, [state]= ISNULL([state] ,'Any State')
|
|
, StateId = CASE WHEN RI.COUNTRY = 'NEPAL' THEN cs.rowId ELSE TL.rowId END
|
|
, locationid=partnersublocationid
|
|
, district
|
|
, purposeOfRemit
|
|
, purposeOfRemitid =por.valueId
|
|
FROM receiverInformation ri (nolocK)
|
|
LEFT JOIN countriesStates cs (NOLOCK) ON cs.stateName = ri.state
|
|
LEFT JOIN tblServicewiseLocation TL (NOLOCK) ON TL.location = ri.state
|
|
LEFT JOIN tblSubLocation TS(NOLOCK) ON TS.subLocation = ri.district
|
|
LEFT JOIN staticDataValue sd (NOLOCK) ON sd.detailTitle = ri.relationship AND sd.typeid= 2100 AND ISNULL(sd.IS_DELETE ,'N')<>'Y'
|
|
LEFT JOIN staticDataValue por (NOLOCK) ON por.detailTitle=ri.purposeOfRemit AND por.typeid= 3800 AND ISNULL(por.IS_DELETE ,'N')<>'Y'
|
|
lEFT JOIN countryMaster cm(NOLOCK) ON cm.countryName = ri.country
|
|
WHERE ri.receiverId=@reciverID
|
|
RETURN
|
|
END
|
|
ELSE IF @flag ='controlno'
|
|
BEGIN
|
|
SELECT dbo.FNADecryptString(controlNo) AS controlNo FROM vwRemitTran(NOLOCK) WHERE holdTranId = @tranid OR id = @tranid
|
|
RETURN
|
|
END
|