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.
789 lines
56 KiB
789 lines
56 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_REGISTRATION_REPORT] Script Date: 2/28/2024 10:56:44 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
--PROC_REGISTRATION_REPORT @flag ='rejectedReport',@user='admin1',@FROM_DATE='2022-03-01',@TO_DATE='2022-03-23',@agentId=null,@branchId=null,@withAgent=null,@tranType='i'
|
|
ALTER PROC [dbo].[PROC_REGISTRATION_REPORT] @FLAG VARCHAR(20)
|
|
,@user VARCHAR(30)
|
|
,@FROM_DATE VARCHAR(10) = NULL
|
|
,@TO_DATE VARCHAR(10) = NULL
|
|
,@SEARCH_BY VARCHAR(10) = NULL
|
|
,@GROUP_BY VARCHAR(10) = NULL
|
|
,@agentId BIGINT = NULL
|
|
,@branchId BIGINT = NULL
|
|
,@withAgent VARCHAR(20) = NULL
|
|
,@tranType VARCHAR(5) = NULL
|
|
,@isOnlineTxn VARCHAR(5) = NULL
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
----------------------------------
|
|
--JME-547 -> Add column approvedBy in Customer Registration Report
|
|
--#134 -> Allow edit option of address in Town Area
|
|
-- #712 added parameter @tranType
|
|
-- #718 -show mobile registered customers on new customer registration report , @flag = 'customer'
|
|
-- #1403 - add Branch/Agent , @FLAG = 's-customerAndTxn'
|
|
-- #1707 - Add summary dropdown , @flag = 's-customerAndTxn'
|
|
-- #11751 - @flag = details , add registration Type
|
|
-----------------------------------
|
|
BEGIN TRY
|
|
DECLARE @agentCode VARCHAR(10)
|
|
|
|
SELECT @agentCode = agentCode
|
|
FROM applicationusers
|
|
WHERE username = @user
|
|
|
|
IF @agentCode = '1001'
|
|
BEGIN
|
|
SET @USER = NULL
|
|
END
|
|
|
|
IF @FLAG = 'beneficiary'
|
|
BEGIN
|
|
SELECT *
|
|
INTO #temp1
|
|
FROM (
|
|
SELECT DISTINCT customerId
|
|
FROM receiverinformation
|
|
WHERE createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
) x
|
|
|
|
SELECT MembershipId
|
|
,CustomerName
|
|
,PostalCode
|
|
,mobile
|
|
,dob
|
|
,VisaStatus
|
|
,Occupation
|
|
,receiverFirstName
|
|
,receiverMiddle
|
|
,receiverLastName
|
|
,createddate
|
|
,country
|
|
,address
|
|
,receiverMobile
|
|
,PaymentMode
|
|
,BANK_NAME
|
|
,BRANCH_NAME
|
|
,receiverAccountNo
|
|
,relationship
|
|
,purposeOfRemit
|
|
,createdby
|
|
FROM (
|
|
SELECT ISNULL(cm.postalcode, CM.membershipId) MembershipId
|
|
,CM.FULLNAME [CustomerName]
|
|
,cm.zipcode [PostalCode]
|
|
,CM.mobile
|
|
,CM.dob
|
|
,CASE
|
|
WHEN cm.occupation = '11383'
|
|
THEN cm.occupationother
|
|
ELSE sdv2.DETAILTITLE
|
|
END [Occupation]
|
|
,ri.firstname receiverFirstName
|
|
,ri.middlename receiverMiddle
|
|
,ri.lastname1 receiverLastName
|
|
,ri.createddate
|
|
,ri.country
|
|
,ri.[address]
|
|
,ri.mobile [receiverMobile]
|
|
,stm.typeTitle PaymentMode
|
|
,AM.BANK_NAME
|
|
,ISNULL(ABBL.BRANCH_NAME, 'Any Branch') BRANCH_NAME
|
|
,ri.receiverAccountNo
|
|
,CASE
|
|
WHEN relationship = '11339'
|
|
THEN ri.relationOther
|
|
ELSE SDV3.detailTitle
|
|
END relationship
|
|
,SDV4.detailtitle purposeOfRemit
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY ri.customerid ORDER BY ri.receiverid
|
|
) ranknum
|
|
,ri.createdby
|
|
,RI.agentId
|
|
,VISA.detailTitle visaStatus
|
|
FROM receiverinformation ri(NOLOCK)
|
|
INNER JOIN #temp1 tmp ON tmp.customerid = ri.customerid
|
|
INNER JOIN customerMaster cm ON cm.customerid = ri.customerid
|
|
INNER JOIN applicationusers au(NOLOCK) ON au.username = ri.createdBy
|
|
LEFT JOIN STATICDATAVALUE SDV1 ON SDV1.VALUEID = CM.GENDER
|
|
LEFT JOIN STATICDATAVALUE SDV2 ON SDV2.VALUEID = CM.occupation
|
|
INNER JOIN serviceTypeMaster stm(NOLOCK) ON stm.serviceTypeId = ri.paymentMode
|
|
LEFT JOIN API_BANK_LIST_MASTER AM(NOLOCK) ON AM.MASTER_BANK_ID = RI.PAYOUTPARTNER
|
|
LEFT JOIN API_BANK_BRANCH_LIST ABBL(NOLOCK) ON cast(ABBL.BRANCH_ID AS VARCHAR) = ri.BANKLOCATION
|
|
LEFT JOIN STATICDATAVALUE SDV3(NOLOCK) ON SDV3.VALUEID = ri.RELATIONSHIP
|
|
LEFT JOIN STATICDATAVALUE SDV4(NOLOCK) ON SDV4.VALUEID = ri.PURPOSEOFREMIT
|
|
LEFT JOIN STATICDATAVALUE VISA(NOLOCK) ON VISA.VALUEID = CM.VISASTATUS
|
|
--AND ISNULL(RI.ISDELETED,'0') <> '1'
|
|
) x
|
|
WHERE ranknum <> 1
|
|
AND createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
AND agentid = ISNULL(@agentId, agentid)
|
|
--AND CREATEDBY = ISNULL(@USER,CREATEDBY)
|
|
ORDER BY createddate
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'From Date' head
|
|
,@FROM_DATE VALUE
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@TO_DATE VALUE
|
|
|
|
SELECT 'New Beneficiary Regisration Report(Sending Agent)' title
|
|
END
|
|
ELSE IF @FLAG = 'customer'
|
|
BEGIN
|
|
IF @withAgent = 'withAgent'
|
|
BEGIN
|
|
SELECT customerId
|
|
INTO #TEMP
|
|
FROM customerMaster(NOLOCK) CM
|
|
INNER JOIN AGENTMASTER am(NOLOCK) ON am.BRANCHCODE = SUBSTRING(CM.membershipid, 1, 3)
|
|
WHERE CM.createdDate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
AND am.AGENTID = isnull(@agentId, am.agentId)
|
|
|
|
SELECT RT.ID
|
|
,T.CUSTOMERID
|
|
,RT.PROMOTIONCODE
|
|
,RT.CREATEDDATE
|
|
INTO #TRAN
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
INNER JOIN TRANSENDERS TS(NOLOCK) ON TS.TRANID = RT.ID
|
|
INNER JOIN #TEMP T(NOLOCK) ON T.CUSTOMERID = TS.CUSTOMERID
|
|
WHERE RT.CREATEDDATE >= @FROM_DATE
|
|
|
|
SELECT *
|
|
INTO #MAIN
|
|
FROM (
|
|
SELECT ROW_NUMBER() OVER (
|
|
PARTITION BY CUSTOMERID ORDER BY CREATEDDATE
|
|
) SN
|
|
,*
|
|
FROM #TRAN
|
|
) X
|
|
WHERE SN = 1
|
|
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY CM.CUSTOMERID
|
|
) [SNo]
|
|
,ISNULL(CM.POSTALCODE, MEMBERSHIPID) ID
|
|
,CM.FULLNAME Name
|
|
,RegistrationType = CASE WHEN cm.RegistrationType IS NULL THEN 'Manual' ELSE cm.RegistrationType END
|
|
,Address = DBO.FNAGetCustomerAddress(CM.CUSTOMERID, '')
|
|
,cm.mobile MobileNo
|
|
,C.COUNTRYNAME Nationality
|
|
,convert(VARCHAR(10), dob, 121) DOB
|
|
,cm.createdby CreatedBy
|
|
,RA.REFERRAL_NAME Agent
|
|
,CM.createdDate CreatedDateTime
|
|
FROM #MAIN M(NOLOCK)
|
|
INNER JOIN REFERRAL_AGENT_WISE RA(NOLOCK) ON RA.REFERRAL_CODE = M.PROMOTIONCODE
|
|
INNER JOIN CUSTOMERMASTER CM(NOLOCK) ON CM.CUSTOMERID = M.CUSTOMERID
|
|
INNER JOIN AGENTMASTER am(NOLOCK) ON am.BRANCHCODE = SUBSTRING(CM.membershipid, 1, 3)
|
|
LEFT JOIN countryStateMaster csm(NOLOCK) ON cast(csm.stateId AS VARCHAR) = cm.STATE
|
|
LEFT JOIN countryMaster C(NOLOCK) ON C.COUNTRYID = CM.NATIVECOUNTRY
|
|
WHERE am.agentid = ISNULL(@agentId, am.agentId)
|
|
ORDER BY CM.createdDate
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT ROW_NUMBER() OVER (
|
|
ORDER BY (SELECT 1)
|
|
) SN
|
|
,membershipId
|
|
,CustomerName
|
|
,sAddress [Address]
|
|
,mobile
|
|
--,Gender
|
|
,[Verification ID Type]
|
|
,convert(VARCHAR(10), idIssueDate, 121) [ID Issue Date]
|
|
,convert(VARCHAR(10), idExpiryDate, 121) [ID Valid Date]
|
|
,nativeCountry [Native Country]
|
|
--,VisaStatus
|
|
,Occupation
|
|
,convert(VARCHAR(10), dob, 121) dob
|
|
,createdBy
|
|
,[VerifiedBy]
|
|
,createdDate
|
|
,approvedBy = approvedBy
|
|
--,receiverFirstName
|
|
--,receiverMiddle
|
|
--,receiverLastName
|
|
--,receiverName
|
|
-- ,country
|
|
--,address
|
|
--,receiverMobile
|
|
--,PaymentMode
|
|
--,BANK_NAME
|
|
--,BRANCH_NAME
|
|
--,receiverAccountNo
|
|
--,relationship
|
|
--,purposeOfRemit
|
|
--,RegistrationType
|
|
FROM (
|
|
SELECT CM.CUSTOMERID
|
|
,CM.FULLNAME [CustomerName]
|
|
,sAddress = ISNULL(cm.address,'') + ISNULL(cm.city,'') + ISNULL(cm.ADDITIONALADDRESS,'')
|
|
,CM.mobile
|
|
,ISNULL(CM.POSTALCODE, CM.membershipId) membershipId
|
|
--,sdv1.DETAILTITLE [Gender]
|
|
,CASE
|
|
WHEN CM.IDTYPE = '11402'
|
|
THEN CM.OTHERIDNUMBER
|
|
ELSE IDTYPE.DETAILTITLE
|
|
END [Verification ID Type]
|
|
,CM.idIssueDate
|
|
,CM.idExpiryDate
|
|
,CMM.COUNTRYNAME [nativeCountry]
|
|
,CASE
|
|
WHEN cm.occupation = '11383'
|
|
THEN cm.occupationother
|
|
ELSE sdv2.DETAILTITLE
|
|
END [Occupation]
|
|
,CM.dob
|
|
,CM.createdBy
|
|
,CM.createdDate
|
|
--,receiverName = ISNULL(ri.firstname,'') + ISNULL(ri.middlename,'') + ISNULL(ri.lastname1,'')
|
|
--,ri.firstname receiverFirstName
|
|
--,ri.middlename receiverMiddle
|
|
--,ri.lastname1 receiverLastName
|
|
--,ri.country
|
|
--,ri.[address]
|
|
,CM.verifiedBy [VerifiedBy]
|
|
,CM.approvedBy
|
|
--,ri.mobile [receiverMobile]
|
|
--,stm.typeTitle PaymentMode
|
|
--,ABL.BANK_NAME
|
|
--,ISNULL(ABBL.BRANCH_NAME, 'Any Where') BRANCH_NAME
|
|
-- ,ri.receiverAccountNo
|
|
--,CASE
|
|
-- WHEN relationship = '11339'
|
|
-- THEN ri.relationOther
|
|
-- ELSE SDV3.detailTitle
|
|
-- END relationship
|
|
--,CASE
|
|
-- WHEN ri.PURPOSEOFREMIT = '11347'
|
|
-- THEN RI.PURPOSEOTHER
|
|
-- ELSE SDV4.detailtitle
|
|
-- END purposeOfRemit
|
|
-- ,VISA.detailTitle visaStatus
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY cm.customerid
|
|
order by cm.customerid
|
|
) ranknum
|
|
,RegistrationType = CASE WHEN cm.RegistrationType IS NULL THEN 'Manual' WHEN cm.registrationtype='MKYC' THEN 'MANUAL' ELSE cm.RegistrationType END
|
|
FROM customerMaster cm
|
|
LEFT JOIN receiverInformation ri ON cm.customerid = ri.customerid
|
|
--INNER JOIN applicationusers au (nolock) on au.username = CM.createdBy
|
|
LEFT JOIN AGENTMASTER am(NOLOCK) ON am.BRANCHCODE = SUBSTRING(CM.membershipid, 1, 3)
|
|
LEFT JOIN STATICDATAVALUE SDV1 ON SDV1.VALUEID = CM.GENDER
|
|
LEFT JOIN STATICDATAVALUE SDV2 ON SDV2.VALUEID = CM.occupation
|
|
LEFT JOIN serviceTypeMaster stm(NOLOCK) ON stm.serviceTypeId = ri.paymentMode
|
|
LEFT JOIN API_BANK_LIST ABL(NOLOCK) ON ABL.BANK_ID = ri.PAYOUTPARTNER
|
|
LEFT JOIN API_BANK_BRANCH_LIST ABBL(NOLOCK) ON CAST(ABBL.BRANCH_ID as VARCHAR) = ri.BANKLOCATION
|
|
LEFT JOIN STATICDATAVALUE SDV3(NOLOCK) ON SDV3.VALUEID = ri.RELATIONSHIP
|
|
LEFT JOIN STATICDATAVALUE SDV4(NOLOCK) ON SDV4.VALUEID = ri.PURPOSEOFREMIT
|
|
LEFT JOIN STATICDATAVALUE VISA(NOLOCK) ON VISA.VALUEID = CM.VISASTATUS
|
|
LEFT JOIN STATICDATAVALUE IDTYPE(NOLOCK) ON IDTYPE.VALUEID = CM.IDTYPE
|
|
LEFT JOIN COUNTRYMASTER CMM(NOLOCK) ON CMM.COUNTRYID = CM.NATIVECOUNTRY
|
|
WHERE CM.createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
--AND ISNULL(RI.ISDELETED,'0') <> '1'
|
|
AND am.agentid = ISNULL(@agentId, am.agentId)
|
|
UNION ALL
|
|
SELECT CM.CUSTOMERID
|
|
,CM.FULLNAME [CustomerName]
|
|
,sAddress = ISNULL(cm.address,'') +' '+ ISNULL(cm.city,'') + ISNULL(cm.ADDITIONALADDRESS,'')
|
|
,CM.mobile
|
|
,ISNULL(CM.POSTALCODE, CM.membershipId) membershipId
|
|
-- ,sdv1.DETAILTITLE [Gender]
|
|
,CASE
|
|
WHEN CM.IDTYPE = '11402'
|
|
THEN CM.OTHERIDNUMBER
|
|
ELSE IDTYPE.DETAILTITLE
|
|
END [Verification ID Type]
|
|
,CM.idIssueDate
|
|
,CM.idExpiryDate
|
|
,CMM.COUNTRYNAME [nativeCountry]
|
|
,CASE
|
|
WHEN cm.occupation = '11383'
|
|
THEN cm.occupationother
|
|
ELSE sdv2.DETAILTITLE
|
|
END [Occupation]
|
|
,CM.dob
|
|
,CM.createdBy
|
|
,CM.createdDate
|
|
--,ri.firstname receiverFirstName
|
|
--,ri.middlename receiverMiddle
|
|
--,ri.lastname1 receiverLastName
|
|
-- ,receiverName = ISNULL(ri.firstname,'') + ISNULL(ri.middlename,'') + ISNULL(ri.lastname1,'')
|
|
-- ,ri.country
|
|
-- ,ri.[address]
|
|
,cm.mobileverifiedby AS [VerifiedBy]
|
|
,CM.approvedBy
|
|
--,ri.mobile [receiverMobile]
|
|
--,stm.typeTitle PaymentMode
|
|
--,ABL.BANK_NAME
|
|
--,ISNULL(ABBL.BRANCH_NAME, 'Any Where') BRANCH_NAME
|
|
--,ri.receiverAccountNo
|
|
--,CASE
|
|
-- WHEN relationship = '11339'
|
|
-- THEN ri.relationOther
|
|
-- ELSE SDV3.detailTitle
|
|
-- END relationship
|
|
--,CASE
|
|
-- WHEN ri.PURPOSEOFREMIT = '11347'
|
|
-- THEN RI.PURPOSEOTHER
|
|
-- ELSE SDV4.detailtitle
|
|
-- END purposeOfRemit
|
|
--,VISA.detailTitle visaStatus
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY cm.customerid
|
|
order by cm.customerid
|
|
) ranknum
|
|
,RegistrationType = CASE WHEN cm.RegistrationType IS NULL THEN 'Manual' ELSE cm.RegistrationType END
|
|
FROM customerMaster cm
|
|
LEFT JOIN receiverInformation ri ON cm.customerid = ri.customerid
|
|
-- INNER JOIN AGENTMASTER am(NOLOCK) ON am.BRANCHCODE LIKE 'ONL%'--SUBSTRING(CM.membershipid, 1, 3)
|
|
LEFT JOIN STATICDATAVALUE SDV1 ON SDV1.VALUEID = CM.GENDER
|
|
LEFT JOIN STATICDATAVALUE SDV2 ON SDV2.VALUEID = CM.occupation
|
|
LEFT JOIN serviceTypeMaster stm(NOLOCK) ON stm.serviceTypeId = ri.paymentMode
|
|
LEFT JOIN API_BANK_LIST ABL(NOLOCK) ON ABL.BANK_ID = ri.PAYOUTPARTNER
|
|
LEFT JOIN API_BANK_BRANCH_LIST ABBL(NOLOCK) ON CAST(ABBL.BRANCH_ID AS VARCHAR) = ri.BANKLOCATION
|
|
LEFT JOIN STATICDATAVALUE SDV3(NOLOCK) ON SDV3.VALUEID = ri.RELATIONSHIP
|
|
LEFT JOIN STATICDATAVALUE SDV4(NOLOCK) ON SDV4.VALUEID = ri.PURPOSEOFREMIT
|
|
LEFT JOIN STATICDATAVALUE VISA(NOLOCK) ON VISA.VALUEID = CM.VISASTATUS
|
|
LEFT JOIN STATICDATAVALUE IDTYPE(NOLOCK) ON IDTYPE.VALUEID = CM.IDTYPE
|
|
LEFT JOIN COUNTRYMASTER CMM(NOLOCK) ON CMM.COUNTRYID = CM.NATIVECOUNTRY
|
|
WHERE CM.createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
--AND ISNULL(RI.ISDELETED,'0') <> '1'
|
|
-- AND am.agentid = ISNULL(@agentId, '394395')
|
|
-- AND cm.agentid = @agentId
|
|
AND cm.createdFrom = 'M'
|
|
AND cm.mobileverifieddate is not null and cm.mobileApprovedDate is not null
|
|
) a
|
|
WHERE ranknum = 1
|
|
--and createdBy = ISNULL(@user,createdBy)
|
|
ORDER BY CREATEDDATE
|
|
END
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'From Date' head
|
|
,@FROM_DATE VALUE
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@TO_DATE VALUE
|
|
|
|
SELECT 'Customer Regisration Report(Sending Agent)' title
|
|
END
|
|
--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
|
|
ELSE
|
|
|
|
IF @FLAG = 'rejectedReport'
|
|
BEGIN
|
|
DECLARE @sql VARCHAR(max)
|
|
,@sql1 VARCHAR(max)
|
|
,@sql2 VARCHAR(max)
|
|
|
|
SET @sql1 = 'SELECT ROW_NUMBER() OVER (
|
|
ORDER BY CTH.ID
|
|
) [SN]
|
|
,dbo.fnadecryptstring(CTH.CONTROLNO) [ControlNo]
|
|
,cth.tranId [TranId]
|
|
,sh.fullname [FullName]
|
|
,sh.mobile [Mobile]
|
|
,ISNULL(cth.receiverName, RH.fullName) [ReceiverName]
|
|
,cth.transactionreference [Trust_Pay_ID]
|
|
,cth.depositType [DepositType]
|
|
,cth.paymentmethod [PaymentMethod]
|
|
,cth.camt [Amount]
|
|
,cth.trnStatusBeforeCnlReq [Status]
|
|
,cth.cancelRequestdate [Reject Date]
|
|
,cancelApprovedBy [Rejected By]
|
|
,CTH.cancelreason [Rejected Remarks]
|
|
FROM cancelTranHistory CTH(NOLOCK)
|
|
INNER JOIN cancelTranSendersHistory SH(NOLOCK) ON SH.TRANID = CTH.TRANID
|
|
INNER JOIN cancelTranReceiversHistory RH(NOLOCK) ON RH.TRANID = CTH.TRANID
|
|
WHERE 1=1'
|
|
SET @sql2 = 'SELECT ROW_NUMBER() OVER (
|
|
ORDER BY RTT.ID
|
|
) [SN]
|
|
,dbo.fnadecryptstring(RTT.CONTROLNO) [ControlNo]
|
|
,rtt.id [TranId]
|
|
,TST.fullname [FullName]
|
|
,TST.mobile [Mobile]
|
|
,ISNULL(RTT.receiverName, TRT.fullName) [ReceiverName]
|
|
,RTT.transactionreference [Trust_Pay_ID]
|
|
,RTT.depositType [DepositType]
|
|
,RTT.paymentmethod [PaymentMethod]
|
|
,RTT.camt [Amount]
|
|
,RTT.Paystatus [Status]
|
|
,RTT.cancelRequestdate [Reject Date]
|
|
,cancelApprovedBy [Rejected By]
|
|
,RTT.cancelreason [Rejected Remarks]
|
|
FROM remitTranTemp RTT(NOLOCK)
|
|
INNER JOIN tranSendersTemp TST(NOLOCK) ON TST.tranId = RTT.id
|
|
INNER JOIN tranReceiversTemp TRT(NOLOCK) ON TRT.tranId = RTT.id
|
|
WHERE
|
|
|
|
RTT.tranStatus = ''rejected''
|
|
OR RTT.payStatus = ''rejected'''
|
|
|
|
IF isnull(@FROM_DATE, '') <> ''
|
|
AND isnull(@TO_DATE, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and cth.createddate BETWEEN ''' + @FROM_DATE + ''' AND ''' + @TO_DATE + '''+'' 23:59:59'''
|
|
SET @sql2 = @sql2 + ' and rtt.createddate BETWEEN ''' + @FROM_DATE + ''' AND ''' + @TO_DATE + '''+'' 23:59:59'''
|
|
END
|
|
|
|
IF ISNULL(@agentId, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and cth.tranType = ''' + @agentId + ''''
|
|
SET @sql2 = @sql2 + ' and rtt.tranType = ''' + @agentId + ''''
|
|
END
|
|
|
|
IF isnull(@tranType, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and cth.tranType = ''' + @tranType + ''''
|
|
SET @sql2 = @sql2 + ' and rtt.tranType = ''' + @tranType + ''''
|
|
END
|
|
|
|
IF isnull(@isOnlineTxn, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and cth.isOnlineTxn = ''' + @isOnlineTxn + ''''
|
|
SET @sql2 = @sql2 + ' and rtt.isOnlineTxn = ''' + @isOnlineTxn + ''''
|
|
END
|
|
|
|
SET @SQL = 'SELECt
|
|
[SN]
|
|
,[ControlNo]
|
|
,[TranId]
|
|
,[FullName]
|
|
,[Mobile]
|
|
,[ReceiverName]
|
|
,[Trust_Pay_ID]
|
|
,[DepositType]
|
|
,[PaymentMethod]
|
|
,[Amount]
|
|
,[Status]
|
|
,[Reject Date]
|
|
,[Rejected By]
|
|
,[Rejected Remarks]
|
|
from (
|
|
' + @sql1 + ' union all ' + @sql2 + '
|
|
|
|
)x
|
|
'
|
|
SET @SQL = @SQL + 'order by x.[SN] desc'
|
|
|
|
PRINT (@SQL)
|
|
|
|
EXEC (@SQL)
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'From Date' head
|
|
,@FROM_DATE VALUE
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@TO_DATE VALUE
|
|
|
|
SELECT 'Rejected Transaction Report' title
|
|
END
|
|
|
|
|
|
ELSE IF @FLAG = 's-customerAndTxn'
|
|
BEGIN
|
|
IF @SEARCH_BY = 'DETAIL'
|
|
BEGIN
|
|
SELECT [Transaction Creation Date & Time]
|
|
,[Control Number]
|
|
,[Customer Name]
|
|
,[Customer Registration Date & Time]
|
|
,[KYC Complete Date & Time]
|
|
,[Branch/Agent]
|
|
,[Customer Approved Date & Time]
|
|
,[Customer ID No]
|
|
,[Date Of Birth]
|
|
,[Gender]
|
|
,[Address] = UPPER([Address])
|
|
,[VisaStatus]
|
|
,[Occupation]
|
|
,[Id Type]
|
|
,[Id No]
|
|
,[Id Issued Date]
|
|
,[Id Expiry Date]
|
|
,[Nationality]
|
|
,[Beneficiary Name]
|
|
,[Payment Type]
|
|
,[Receiver Country]
|
|
,[Transaction Approved Date & Time]
|
|
,[Payment Date]
|
|
,[Cancelled Date]
|
|
,[Corresponding]
|
|
,[Collect Amount]
|
|
,[Service Charge]
|
|
,[Send Amount]
|
|
,[Payount Amount]
|
|
,[Payout Currency]
|
|
,[CustRate]
|
|
,[Purpose]
|
|
,[Relationship with Sender]
|
|
,[Source Of Fund]
|
|
,[User]
|
|
,[TR Approved By]
|
|
FROM (
|
|
SELECT convert(VARCHAR(19), RT.createdDate, 121) [Transaction Creation Date & Time]
|
|
,dbo.fnadecryptstring(controlno) [Control Number]
|
|
,senderName [Customer Name]
|
|
,convert(VARCHAR(19), CM.CREATEDDATE, 121) [Customer Registration Date & Time]
|
|
,ISNULL(convert(VARCHAR(19), KYC_DATE, 121), convert(VARCHAR, CM.CREATEDDATE, 121)) [KYC Complete Date & Time]
|
|
,ROW_NUMBER() OVER (
|
|
PARTITION BY RT.controlno ORDER BY KYC.kyc_date DESC
|
|
) ranknum
|
|
,convert(VARCHAR(19), CM.APPROVEDDATE, 121) [Customer Approved Date & Time]
|
|
,CM.MembershipId [Customer ID No]
|
|
,convert(VARCHAR(10), CM.DOB, 121) [Date Of Birth]
|
|
,gender.detailTitle [Gender]
|
|
,[Address] = DBO.FNAGetCustomerAddress(CM.CUSTOMERID, '')
|
|
,visaStatus.detailTitle [VisaStatus]
|
|
,CASE
|
|
WHEN cm.occupation = '11383'
|
|
THEN cm.occupationother
|
|
ELSE occupation.detailTitle
|
|
END [Occupation]
|
|
,idType.detailTitle [Id Type]
|
|
,cm.idNumber [Id No]
|
|
,convert(VARCHAR(10), cm.idIssueDate, 121) [Id Issued Date]
|
|
,convert(VARCHAR(10), cm.idExpiryDate, 121) [Id Expiry Date]
|
|
,nativeCountry.countryName [Nationality]
|
|
,RT.receiverName [Beneficiary Name]
|
|
,RT.paymentMethod [Payment Type]
|
|
,RT.PCOUNTRY [Receiver Country]
|
|
,convert(VARCHAR(19), rt.approvedDate, 121) [Transaction Approved Date & Time]
|
|
,ISNULL(convert(VARCHAR(19), rt.paidDate, 121), '') [Payment Date]
|
|
,ISNULL(CONVERT(VARCHAR, rt.cancelApproveddate, 121), '') [Cancelled Date]
|
|
,rt.pSuperAgentName [Corresponding]
|
|
,rt.cAmt [Collect Amount]
|
|
,rt.serviceCharge [Service Charge]
|
|
,rt.tamt [Send Amount]
|
|
,rt.pAmt [Payount Amount]
|
|
,rt.payoutCurr [Payout Currency]
|
|
,rt.customerRate [CustRate]
|
|
,rt.purposeofremit [Purpose]
|
|
,CASE
|
|
WHEN RI.relationship = '11339'
|
|
THEN ri.relationOther
|
|
ELSE SDV3.detailTitle
|
|
END [Relationship with Sender]
|
|
,rt.sourceOfFund [Source Of Fund]
|
|
,rt.createdby [User]
|
|
,rt.approvedBy [TR Approved By]
|
|
,[Branch/Agent] = CASE WHEN staff.REFERRAL_CODE IS NOT NULL THEN staff.REFERRAL_NAME
|
|
WHEN AGENT.REFERRAL_CODE IS NOT NULL THEN AGENT.REFERRAL_NAME
|
|
-- WHEN branch.REFERRAL_CODE IS NOT NULL THEN BRANCH.REFERRAL_NAME
|
|
WHEN novisit.REFERRAL_CODE IS NOT NULL THEN novisit.AgentName
|
|
WHEN rt.tranType = 'M' AND promotionCode IS NULL THEN 'Jme Mobile'
|
|
END
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
INNER JOIN TRANSENDERS TS(NOLOCK) ON TS.TRANID = RT.ID
|
|
INNER JOIN TRANRECEIVERS TR(NOLOCK) ON TR.TRANID = RT.ID
|
|
INNER JOIN CUSTOMERMASTER CM(NOLOCK) ON CM.CUSTOMERID = TS.CUSTOMERID
|
|
INNER JOIN RECEIVERINFORMATION RI(NOLOCK) ON RI.RECEIVERID = TR.CUSTOMERID
|
|
LEFT JOIN TBL_CUSTOMER_KYC KYC(NOLOCK) ON KYC.CUSTOMERID = CM.CUSTOMERID
|
|
LEFT JOIN staticDataValue gender(NOLOCK) ON gender.valueId = cm.gender
|
|
LEFT JOIN staticDataValue visaStatus(NOLOCK) ON visaStatus.valueId = cm.visaStatus
|
|
LEFT JOIN staticDataValue occupation(NOLOCK) ON occupation.valueId = cm.occupation
|
|
LEFT JOIN countrymaster nativeCountry(NOLOCK) ON nativeCountry.countryId = cm.nativeCountry
|
|
LEFT JOIN staticDataValue idType(NOLOCK) ON idType.valueId = cm.idType
|
|
LEFT JOIN STATICDATAVALUE SDV3(NOLOCK) ON SDV3.VALUEID = ri.RELATIONSHIP
|
|
LEFT JOIN (SELECT REFERRAL_CODE, REFERRAL_NAME, REFERRAL_TYPE_CODE, STAFF_VISIT FROM REFERRAL_AGENT_WISE(NOLOCK) WHERE STAFF_VISIT = 1)staff ON staff.REFERRAL_CODE = RT.promotionCode
|
|
LEFT JOIN (SELECT REFERRAL_CODE, REFERRAL_NAME, REFERRAL_TYPE_CODE, AGENT_ID STAFF_VISIT FROM REFERRAL_AGENT_WISE(NOLOCK) WHERE REFERRAL_TYPE_CODE = 'RR' and AGENT_ID <> '0')agent ON agent.REFERRAL_CODE = RT.promotionCode
|
|
-- LEFT JOIN (SELECT REFERRAL_CODE, REFERRAL_NAME, REFERRAL_TYPE_CODE FROM REFERRAL_AGENT_WISE(NOLOCK) WHERE REFERRAL_TYPE_CODE= 'RB')branch ON branch.REFERRAL_CODE = RT.promotionCode
|
|
LEFT JOIN (SELECT REFERRAL_CODE, agentName FROM REFERRAL_AGENT_WISE(NOLOCK) ra
|
|
inner join agentMaster (NOLOCK) am on am.agentId = ra.branch_id
|
|
WHERE staff_Visit IS NULL OR staff_Visit = 0
|
|
)novisit ON novisit.REFERRAL_CODE = RT.promotionCode
|
|
WHERE rt.createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
) x
|
|
WHERE ranknum = 1
|
|
END
|
|
ELSE IF @SEARCH_BY = 'SUMMARY' AND @GROUP_BY = 'branch'
|
|
BEGIN
|
|
SELECT ISNULL([Branch/Agent] , 'UNKNOWN') AS [Branch / Agent] ,
|
|
ISNULL(COUNT(ID), 0) AS [TXN COUNT]
|
|
FROM (
|
|
SELECT
|
|
RT.id
|
|
,[Branch/Agent] = CASE WHEN staff.REFERRAL_CODE IS NOT NULL THEN staff.REFERRAL_NAME
|
|
WHEN AGENT.REFERRAL_CODE IS NOT NULL THEN AGENT.REFERRAL_NAME
|
|
-- WHEN branch.REFERRAL_CODE IS NOT NULL THEN BRANCH.REFERRAL_NAME
|
|
WHEN novisit.REFERRAL_CODE IS NOT NULL THEN novisit.AgentName
|
|
WHEN rt.tranType = 'M' AND promotionCode IS NULL THEN 'Jme Mobile'
|
|
END
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
LEFT JOIN (SELECT REFERRAL_CODE, REFERRAL_NAME, REFERRAL_TYPE_CODE, STAFF_VISIT FROM REFERRAL_AGENT_WISE(NOLOCK) WHERE STAFF_VISIT = 1)staff ON staff.REFERRAL_CODE = RT.promotionCode
|
|
LEFT JOIN (SELECT REFERRAL_CODE, REFERRAL_NAME, REFERRAL_TYPE_CODE, AGENT_ID STAFF_VISIT FROM REFERRAL_AGENT_WISE(NOLOCK) WHERE REFERRAL_TYPE_CODE = 'RR' and AGENT_ID <> '0')agent ON agent.REFERRAL_CODE = RT.promotionCode
|
|
-- LEFT JOIN (SELECT REFERRAL_CODE, REFERRAL_NAME, REFERRAL_TYPE_CODE FROM REFERRAL_AGENT_WISE(NOLOCK) WHERE REFERRAL_TYPE_CODE= 'RB')branch ON branch.REFERRAL_CODE = RT.promotionCode
|
|
LEFT JOIN (SELECT REFERRAL_CODE, agentName FROM REFERRAL_AGENT_WISE(NOLOCK) ra
|
|
inner join agentMaster (NOLOCK) am on am.agentId = ra.branch_id
|
|
WHERE staff_Visit IS NULL OR staff_Visit = 0
|
|
)novisit ON novisit.REFERRAL_CODE = RT.promotionCode
|
|
WHERE rt.createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
) x
|
|
GROUP BY [Branch/Agent]
|
|
ORDER BY [Branch/Agent] ASC
|
|
END
|
|
ELSE IF @SEARCH_BY = 'SUMMARY' AND @GROUP_BY = 'pType'
|
|
BEGIN
|
|
SELECT
|
|
rt.paymentMethod AS [Payment Type]
|
|
, ISNULL(COUNT(RT.id) , 0) AS [TXN COUNT]
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
WHERE rt.createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
GROUP BY paymentMethod
|
|
ORDER BY paymentMethod ASC
|
|
END
|
|
ELSE IF @SEARCH_BY = 'SUMMARY' AND @GROUP_BY = 'country'
|
|
BEGIN
|
|
SELECT
|
|
rt.pCountry AS [Receiver Country]
|
|
, ISNULL(COUNT(RT.id) , 0) AS [TXN COUNT]
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
WHERE rt.createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
GROUP BY pCountry
|
|
ORDER BY pCountry ASC
|
|
END
|
|
ELSE IF @SEARCH_BY = 'SUMMARY' AND @GROUP_BY = 'tDate'
|
|
BEGIN
|
|
SELECT
|
|
convert(VARCHAR(10), createdDate, 121) AS [Transaction Date]
|
|
-- CAST(rt.createdDate AS DATE) AS [Transaction Date]
|
|
, ISNULL(COUNT(RT.id) , 0) AS [TXN COUNT]
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
WHERE rt.createddate BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
GROUP BY CONVERT(VARCHAR(10), createdDate, 121)
|
|
ORDER BY CONVERT(VARCHAR(10), createdDate, 121)
|
|
-- ORDER BY CAST(createdDate AS DATE) ASC
|
|
END
|
|
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'From Date' head
|
|
,@FROM_DATE VALUE
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@TO_DATE VALUE
|
|
|
|
--UNION ALL
|
|
--SELECT '@grou'
|
|
|
|
SELECT 'CUSTOMER AND TRANSACTION REPORT' title
|
|
END
|
|
ELSE IF @FLAG = 'disabled'
|
|
BEGIN
|
|
DECLARE @USERTYPE CHAR(2)
|
|
|
|
SELECT @USERTYPE = USERTYPE
|
|
FROM APPLICATIONUSERS(NOLOCK)
|
|
WHERE USERNAME = @USER
|
|
|
|
SELECT membershipid
|
|
,postalcode [Old Customer Id]
|
|
,fullname [Customer Name]
|
|
,Mobile
|
|
,gender.detailTitle [Gender]
|
|
,Idnumber
|
|
,idType.detailTitle [Id Type]
|
|
,convert(VARCHAR(10), cm.idIssueDate, 121) [Id Issued Date]
|
|
,convert(VARCHAR(10), cm.idExpiryDate, 121) [Id Expiry Date]
|
|
,nativeCountry.countryName [Nationality]
|
|
,visaStatus.detailTitle [VisaStatus]
|
|
,CASE
|
|
WHEN cm.occupation = '11383'
|
|
THEN cm.occupationother
|
|
ELSE occupation.detailTitle
|
|
END [Occupation]
|
|
,link = CASE
|
|
WHEN S.CUSTOMERID IS NOT NULL
|
|
THEN CASE
|
|
WHEN ISNULL(@USERTYPE, 'HO') = 'HO'
|
|
THEN '<a href="/Remit/CustomerSOA/CustomerSoaReceipt.aspx?rptFor=disabled&customerId=' + CAST(CM.CUSTOMERID AS VARCHAR) + '">View SOA</a>'
|
|
ELSE '<a href="/AgentNew/CustomerSOA/CustomerSOA.aspx?rptFor=disabled&customerId=' + CAST(CM.CUSTOMERID AS VARCHAR) + '">View SOA</a>'
|
|
END
|
|
ELSE ''
|
|
END
|
|
FROM CUSTOMERMASTER cm(NOLOCK)
|
|
LEFT JOIN (
|
|
SELECT DISTINCT CUSTOMERID
|
|
FROM TRANSENDERS S(NOLOCK)
|
|
INNER JOIN REMITTRAN R(NOLOCK) ON R.ID = S.TRANID
|
|
WHERE R.TRANSTATUS <> 'CANCEL'
|
|
) S ON S.CUSTOMERID = CM.CUSTOMERID
|
|
LEFT JOIN countrymaster nativeCountry(NOLOCK) ON nativeCountry.countryId = cm.nativeCountry
|
|
LEFT JOIN staticDataValue visaStatus(NOLOCK) ON visaStatus.valueId = cm.visaStatus
|
|
LEFT JOIN staticDataValue occupation(NOLOCK) ON occupation.valueId = cm.occupation
|
|
LEFT JOIN staticDataValue gender(NOLOCK) ON gender.valueId = cm.gender
|
|
LEFT JOIN staticDataValue idType(NOLOCK) ON idType.valueId = cm.idType
|
|
WHERE ISNULL(cm.ISACTIVE, 'Y') = 'N'
|
|
AND ISNULL(cm.deletedDate, CM.CREATEDDATE) BETWEEN @FROM_DATE
|
|
AND @TO_DATE + ' 23:59:59'
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT 'From Date' head
|
|
,@FROM_DATE VALUE
|
|
|
|
UNION ALL
|
|
|
|
SELECT 'To Date' head
|
|
,@TO_DATE VALUE
|
|
|
|
SELECT 'Disabled Customers' title
|
|
END
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION
|
|
|
|
SELECT '1' ErrorCode
|
|
,ERROR_MESSAGE() Msg
|
|
,NULL id
|
|
END CATCH
|
|
|