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.
313 lines
14 KiB
313 lines
14 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_paidTranReport] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[proc_paidTranReport]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_paidTranReport] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[proc_paidTranReport]
|
|
@flag VARCHAR(20),
|
|
@fromDate VARCHAR(20) = NULL,
|
|
@toDate VARCHAR(30) = NULL,
|
|
@sCountry VARCHAR(50) = NULL,
|
|
@sZone VARCHAR(50) = NULL,
|
|
@sDistrict VARCHAR(50) = NULL,
|
|
@sLocation VARCHAR(50) = NULL,
|
|
@sAgent VARCHAR(50) = NULL,
|
|
@sBranch VARCHAR(50) = NULL,
|
|
@rCountry VARCHAR(50) = NULL,
|
|
@rZone VARCHAR(50) = NULL,
|
|
@rDistrict VARCHAR(50) = NULL,
|
|
@rLocation VARCHAR(50) = NULL,
|
|
@rAgent VARCHAR(50) = NULL,
|
|
@rBranch VARCHAR(50) = NULL,
|
|
@tranType VARCHAR(50) = NULL,
|
|
@user VARCHAR(50) = NULL
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET ANSI_NULLS ON;
|
|
|
|
IF @rZone ='All'
|
|
set @rZone = null
|
|
if @sZone ='All'
|
|
set @sZone= null
|
|
|
|
IF @rDistrict ='All'
|
|
set @rDistrict = null
|
|
if @sDistrict ='All'
|
|
set @sDistrict= null
|
|
|
|
IF @rLocation ='All'
|
|
set @rLocation = null
|
|
if @sLocation ='All'
|
|
set @sLocation= null
|
|
|
|
SET @TODATE = @TODATE + ' 23:59:59'
|
|
|
|
IF @FLAG='A'
|
|
BEGIN
|
|
|
|
IF OBJECT_ID('tempdb..#TEMP_TABLE') IS NOT NULL
|
|
DROP TABLE #TEMP_TABLE
|
|
|
|
select dbo.GetAgentNameFromId(pAgent) [Payout Agent]
|
|
,ltrim(rtrim(dbo.GetAgentNameFromId(sBranch)))+' : '+'<a href = "#" onclick=OpenInNewWindow("'+dbo.FNAGetURL()+'Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId='+CAST(A.id AS VARCHAR)+'") >' + dbo.FNADecryptString(controlNo) +'</a>'+'</br>'+isnull(sen.firstName,'')+' '+isnull(sen.middleName,'')+' '+isnull(sen.lastName1,'')+' '+isnull(sen.lastName2,'') [Sendng Details]
|
|
,isnull(rec.firstName,'')+' '+isnull(rec.middleName,'')+' '+isnull(rec.lastName1,'')+' '+isnull(rec.lastName2,'') [Receiver Name]
|
|
,paidBy+'<br/>'+ convert(varchar,paidDate,107) [Paid Date]
|
|
,cast(dbo.ShowDecimal(cAmt) as varchar)+' '+collCurr [Send Amount]
|
|
,cast(dbo.ShowDecimal(pAmt) as varchar) [Receive Amount]
|
|
INTO #TEMP_TABLE
|
|
from remitTran A with(nolock)
|
|
LEFT JOIN tranReceivers rec WITH(NOLOCK) ON a.id = rec.tranId
|
|
LEFT JOIN tranSenders sen WITH(NOLOCK) ON a.id=sen.tranId
|
|
LEFT JOIN agentMaster B with(nolock) ON a.sBranch=b.agentId
|
|
LEFT JOIN agentMaster C with(nolock) ON a.pBranch=C.agentId
|
|
LEFT JOIN api_districtList D WITH(NOLOCK) ON D.ROWID=B.agentLocation
|
|
LEFT JOIN api_districtList E WITH(NOLOCK) ON E.ROWID=C.agentLocation
|
|
WHERE tranStatus='Paid'
|
|
AND paidDate BETWEEN @fromDate AND @toDate
|
|
AND isnull(B.agentState,'') =ISNULL(@sZone,isnull(B.agentState,''))
|
|
AND isnull(B.agentDistrict,'') =ISNULL(@sDistrict,isnull(B.agentDistrict,''))
|
|
AND isnull(B.agentLocation,'') =ISNULL(@sLocation,isnull(B.agentLocation,''))
|
|
AND isnull(sAgent,'') = ISNULL(@sAgent,isnull(sAgent,''))
|
|
AND isnull(sBranch,'') = ISNULL(@sBranch,isnull(sBranch,''))
|
|
AND isnull(C.agentState,'') =ISNULL(@rZone,isnull(C.agentState,''))
|
|
AND isnull(C.agentDistrict,'') =ISNULL(@rDistrict,isnull(C.agentDistrict,''))
|
|
AND isnull(C.agentLocation,'') =ISNULL(@rLocation,isnull(C.agentLocation,''))
|
|
AND isnull(pAgent,'') = ISNULL(@rAgent,isnull(pAgent,''))
|
|
AND isnull(pBranch,'') = ISNULL(@rBranch,isnull(pBranch,''))
|
|
AND a.tranType = 'D'
|
|
order by pBranch
|
|
|
|
SELECT DISTINCT [Payout Agent] HEAD FROM #TEMP_TABLE
|
|
|
|
SELECT [Payout Agent] HEAD,[Sendng Details],[Receiver Name],[Paid Date],[Send Amount],[Receive Amount] FROM #TEMP_TABLE
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
|
|
SELECT 'From Date ' head, CONVERT(VARCHAR, @fromDate, 101) value
|
|
UNION ALL
|
|
SELECT 'To Date ' head, CONVERT(VARCHAR, @toDate, 101) value
|
|
UNION ALL
|
|
SELECT 'Sending Country ' head,ISNULL(@sCountry,'All')
|
|
UNION ALL
|
|
SELECT 'Sending Zone ' head,ISNULL(@sZone,'All')
|
|
UNION ALL
|
|
SELECT 'Sending District ' head,ISNULL(@sDistrict,'All')
|
|
UNION ALL
|
|
SELECT 'Sending Location ' head,ISNULL((select districtName from api_districtList where districtCode=@sLocation),'All')
|
|
UNION ALL
|
|
SELECT 'Sending Agent ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @sAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Sending Branch ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @rAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Country ' head,ISNULL(@rCountry,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Zone ' head,ISNULL(@rZone,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving District ' head,ISNULL(@rDistrict,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Location ' head,ISNULL((select districtName from api_districtList where districtCode=@rLocation),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Agent ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @sAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Branch ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @rAgent),'All')
|
|
|
|
SELECT 'PAID TRANSACTION REPORT DETAIL (DOMESTIC)' title
|
|
|
|
|
|
END
|
|
|
|
IF @flag='B'
|
|
BEGIN
|
|
select dbo.GetAgentNameFromId(pAgent) [Payout Agent]
|
|
,count(*) [Total Count]
|
|
,dbo.ShowDecimal(sum(pAmt)) [Payout Amount]
|
|
from remitTran a with(nolock)
|
|
LEFT JOIN tranReceivers rec WITH(NOLOCK) ON a.id = rec.tranId
|
|
LEFT JOIN tranSenders sen WITH(NOLOCK) ON a.id=sen.tranId
|
|
LEFT JOIN agentMaster B with(nolock) ON a.sBranch=b.agentId
|
|
LEFT JOIN agentMaster C with(nolock) ON a.pBranch=C.agentId
|
|
LEFT JOIN api_districtList D WITH(NOLOCK) ON D.ROWID=B.agentLocation
|
|
LEFT JOIN api_districtList E WITH(NOLOCK) ON E.ROWID=C.agentLocation
|
|
where tranStatus='Paid'
|
|
AND paidDate BETWEEN @fromDate AND @toDate
|
|
AND isnull(B.agentState,'') =ISNULL(@sZone,isnull(B.agentState,''))
|
|
AND isnull(B.agentDistrict,'') =ISNULL(@sDistrict,isnull(B.agentDistrict,''))
|
|
AND isnull(B.agentLocation,'') =ISNULL(@sLocation,isnull(B.agentLocation,''))
|
|
AND isnull(sAgent,'') = ISNULL(@sAgent,isnull(sAgent,''))
|
|
AND isnull(sBranch,'') = ISNULL(@sBranch,isnull(sBranch,''))
|
|
AND isnull(C.agentState,'') =ISNULL(@rZone,isnull(C.agentState,''))
|
|
AND isnull(C.agentDistrict,'') =ISNULL(@rDistrict,isnull(C.agentDistrict,''))
|
|
AND isnull(C.agentLocation,'') =ISNULL(@rLocation,isnull(C.agentLocation,''))
|
|
AND isnull(pAgent,'') = ISNULL(@rAgent,isnull(pAgent,''))
|
|
AND isnull(pBranch,'') = ISNULL(@rBranch,isnull(pBranch,''))
|
|
AND a.tranType = 'D'
|
|
group by pAgent
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
|
|
SELECT 'From Date ' head, CONVERT(VARCHAR, @fromDate, 101) value
|
|
UNION ALL
|
|
SELECT 'To Date ' head, CONVERT(VARCHAR, @toDate, 101) value
|
|
UNION ALL
|
|
SELECT 'Sending Country ' head,ISNULL(@sCountry,'All')
|
|
UNION ALL
|
|
SELECT 'Sending Zone ' head,ISNULL(@sZone,'All')
|
|
UNION ALL
|
|
SELECT 'Sending District ' head,ISNULL(@sDistrict,'All')
|
|
UNION ALL
|
|
SELECT 'Sending Location ' head,ISNULL((select districtName from api_districtList where districtCode=@sLocation),'All')
|
|
UNION ALL
|
|
SELECT 'Sending Agent ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @sAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Sending Branch ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @rAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Country ' head,ISNULL(@rCountry,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Zone ' head,ISNULL(@rZone,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving District ' head,ISNULL(@rDistrict,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Location ' head,ISNULL((select districtName from api_districtList where districtCode=@rLocation),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Agent ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @sAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Branch ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @rAgent),'All')
|
|
|
|
SELECT 'PAID TRANSACTION REPORT SUMMARY (DOMESTIC)' title
|
|
END
|
|
|
|
IF @flag='C'
|
|
BEGIN
|
|
IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
|
|
DROP TABLE #temptable
|
|
CREATE TABLE #temptable
|
|
(
|
|
agentId int null,
|
|
sendCount int null,
|
|
sendAmount money null,
|
|
sendHOmargin money null,
|
|
sendServiceCharge money null,
|
|
sendAgentComm money null,
|
|
sendSuperAgentComm money null,
|
|
paidCount int null,
|
|
paidAmount money null,
|
|
paidHOmargin money null,
|
|
paidServiceCharge money null,
|
|
paidAgentComm money null,
|
|
paidSuperAgentComm money null
|
|
)
|
|
--insert into #temptable
|
|
--select sBranch
|
|
-- ,sendCount = count(*)
|
|
-- ,sendAmount = SUM(cAmt)
|
|
-- ,sendHOMargin = sum(isnull(serviceCharge,0)-isnull(sAgentComm,0)-isnull(sSuperAgentComm,0)-isnull(pAgentComm,0)-isnull(pSuperAgentComm,0))
|
|
-- ,sendServiceCharge = sum(isnull(serviceCharge,0))
|
|
-- ,sendAgentComm = sum(isnull(sAgentComm,0))
|
|
-- ,sendSuperAgentComm = sum(isnull(sSuperAgentComm,0))
|
|
-- ,0,0,0,0,0,0
|
|
--from remitTran a with(nolock)
|
|
--LEFT JOIN agentMaster B with(nolock) ON a.sBranch=b.agentId
|
|
--LEFT JOIN agentMaster C with(nolock) ON a.pBranch=C.agentId
|
|
--LEFT JOIN api_districtList D WITH(NOLOCK) ON D.ROWID=B.agentLocation
|
|
--LEFT JOIN api_districtList E WITH(NOLOCK) ON E.ROWID=C.agentLocation
|
|
--where a.approvedDate between @fromDate AND @toDate
|
|
-- AND isnull(convert(varchar,a.cancelApprovedDate,101),'') <> convert(varchar,a.approvedDate,101)
|
|
-- AND isnull(B.agentState,'') =ISNULL(@sZone,isnull(B.agentState,''))
|
|
-- AND isnull(B.agentDistrict,'') =ISNULL(@sDistrict,isnull(B.agentDistrict,''))
|
|
-- AND isnull(B.agentLocation,'') =ISNULL(@sLocation,isnull(B.agentLocation,''))
|
|
-- AND isnull(sAgent,'') = ISNULL(@sAgent,isnull(sAgent,''))
|
|
-- AND isnull(sBranch,'') = ISNULL(@sBranch,isnull(sBranch,''))
|
|
-- AND isnull(C.agentState,'') =ISNULL(@rZone,isnull(C.agentState,''))
|
|
-- AND isnull(C.agentDistrict,'') =ISNULL(@rDistrict,isnull(C.agentDistrict,''))
|
|
-- AND isnull(C.agentLocation,'') =ISNULL(@rLocation,isnull(C.agentLocation,''))
|
|
-- AND isnull(pAgent,'') = ISNULL(@rAgent,isnull(pAgent,''))
|
|
-- AND isnull(pBranch,'') = ISNULL(@rBranch,isnull(pBranch,''))
|
|
-- AND a.tranType = 'D'
|
|
--group by sBranch
|
|
|
|
insert into #temptable
|
|
select
|
|
pAgent
|
|
,0,0,0,0,0,0
|
|
,paidCount = count(*)
|
|
,paidAmount = SUM(pAmt)
|
|
,paidHOMargin = sum(isnull(serviceCharge,0)-isnull(sAgentComm,0)-isnull(sSuperAgentComm,0)-isnull(pAgentComm,0)-isnull(pSuperAgentComm,0))
|
|
,paidServiceCharge = sum(isnull(serviceCharge,0))
|
|
,paidAgentComm = sum(isnull(pAgentComm,0))
|
|
,paidSuperAgentComm = sum(isnull(pSuperAgentComm,0))
|
|
from remitTran a with(nolock)
|
|
LEFT JOIN agentMaster B with(nolock) ON a.sBranch=b.agentId
|
|
LEFT JOIN agentMaster C with(nolock) ON a.pBranch=C.agentId
|
|
LEFT JOIN api_districtList D WITH(NOLOCK) ON D.ROWID=B.agentLocation
|
|
LEFT JOIN api_districtList E WITH(NOLOCK) ON E.ROWID=C.agentLocation
|
|
where tranStatus='Paid'
|
|
AND a.paidDate between @fromDate AND @toDate
|
|
AND isnull(B.agentState,'') =ISNULL(@sZone,isnull(B.agentState,''))
|
|
AND isnull(B.agentDistrict,'') =ISNULL(@sDistrict,isnull(B.agentDistrict,''))
|
|
AND isnull(B.agentLocation,'') =ISNULL(@sLocation,isnull(B.agentLocation,''))
|
|
AND isnull(sAgent,'') = ISNULL(@sAgent,isnull(sAgent,''))
|
|
AND isnull(sBranch,'') = ISNULL(@sBranch,isnull(sBranch,''))
|
|
AND isnull(C.agentState,'') =ISNULL(@rZone,isnull(C.agentState,''))
|
|
AND isnull(C.agentDistrict,'') =ISNULL(@rDistrict,isnull(C.agentDistrict,''))
|
|
AND isnull(C.agentLocation,'') =ISNULL(@rLocation,isnull(C.agentLocation,''))
|
|
AND isnull(pAgent,'') = ISNULL(@rAgent,isnull(pAgent,''))
|
|
AND isnull(pBranch,'') = ISNULL(@rBranch,isnull(pBranch,''))
|
|
AND a.tranType = 'D'
|
|
group by pAgent
|
|
|
|
select
|
|
[Agent Name] = am.agentName,
|
|
[#Send Count] = sum(isnull(sendCount,0)),
|
|
[#Paid Count] = sum(isnull(paidCount,0)),
|
|
[Send Amount] = sum(isnull(sendAmount,0)),
|
|
[Paid Amount] = sum(isnull(paidAmount,0)),
|
|
[Net Sattlement Amount] = sum(isnull(sendAmount,0)) - sum(ISNULL(paidAmount,0)),
|
|
[SC] = sum(isnull(sendAgentComm,0)),
|
|
[RC] = sum(isnull(paidAgentComm,0)),
|
|
[Total Margin] = sum(isnull(sendAgentComm,0)) - sum(isnull(paidAgentComm,0)),
|
|
[HO Margin] = sum(isnull(sendHOmargin,0)) + sum(isnull(paidHOmargin,0))
|
|
from #temptable temp with(nolock) inner join agentMaster am with(nolock) on temp.agentId = am.agentId
|
|
group by am.agentName
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
|
|
SELECT 'From Date ' head, CONVERT(VARCHAR, @fromDate, 101) value
|
|
UNION ALL
|
|
SELECT 'To Date ' head, CONVERT(VARCHAR, @toDate, 101) value
|
|
UNION ALL
|
|
SELECT 'Sending Country ' head,ISNULL(@sCountry,'All')
|
|
UNION ALL
|
|
SELECT 'Sending Zone ' head,ISNULL(@sZone,'All')
|
|
UNION ALL
|
|
SELECT 'Sending District ' head,ISNULL(@sDistrict,'All')
|
|
UNION ALL
|
|
SELECT 'Sending Location ' head,ISNULL((select districtName from api_districtList where districtCode=@sLocation),'All')
|
|
UNION ALL
|
|
SELECT 'Sending Agent ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @sAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Sending Branch ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @rAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Country ' head,ISNULL(@rCountry,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Zone ' head,ISNULL(@rZone,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving District ' head,ISNULL(@rDistrict,'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Location ' head,ISNULL((select districtName from api_districtList where districtCode=@rLocation),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Agent ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @sAgent),'All')
|
|
UNION ALL
|
|
SELECT 'Receiving Branch ' head,ISNULL((SELECT agentName FROM agentMaster with(nolock) WHERE agentId = @rAgent),'All')
|
|
|
|
SELECT 'PAID TRANSACTION REPORT SUMMARY WITH COMMISSION (DOMESTIC)' title
|
|
END
|
|
|
|
|
|
|
|
GO
|