· 6 years ago · May 02, 2019, 08:44 AM
1USE [UTNetSystem_3_2_Letshego]
2GO
3/****** Object: StoredProcedure [utnetAgentManagement].[GetTransactionsLog] Script Date: 5/2/2019 9:34:34 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10
11ALTER PROCEDURE [utnetAgentManagement].[GetTransactionsLog]
12 @ImplementationID nvarchar(50),
13 @AgentID bigint = null,
14 @OperationTypeID bigint = null,
15 @CustomerNumber nvarchar(200) = null,
16 @ReferenceNumber nvarchar(200) = null,
17 @DateFrom nvarchar(10),
18 @DateTo nvarchar(10),
19 @CurrencyID nchar(3),
20 @ReceiptPrinted bit = null,
21 @supervisor nvarchar(50) = NULL,
22 @Status varchar(10),
23 @pageSize INT = 50, -- how many rows will be returned per page
24 @pageNumber INT = 1, -- which page number to display
25 @sortBy VARCHAR(50) = 'transferDate', -- on which column results to be sorted
26 @sortOrder VARCHAR(4) = 'DESC', -- what kind of sort to be used ascending or descending
27 @TransactionsDataXML xml OUTPUT,
28 @Result bigint OUTPUT,
29 @ResultMessage nvarchar(max) OUTPUT
30 AS
31 BEGIN
32
33
34 IF @AgentID = ''
35 SET @AgentID = NULL;
36 IF @supervisor = ''
37 SET @supervisor = NULL;
38 IF @Status = ''
39 SET @Status = NULL;
40 IF @DateFrom = ''
41 SET @DateFrom = NULL;
42 IF @DateTo = ''
43 SET @DateTo = NULL;
44 IF @CustomerNumber = ''
45 SET @CustomerNumber = NULL;
46 IF @ReferenceNumber = ''
47 SET @ReferenceNumber = NULL;
48 IF @CurrencyID = ''
49 SET @CurrencyID = NULL;
50 IF @OperationTypeID = 0
51 SET @OperationTypeID = NULL;
52
53 DECLARE @startRow INT = ( @pageNumber - 1) * @pageSize + 1
54 DECLARE @endRow INT = @startRow + @pageSize - 1
55
56 IF OBJECT_ID('tempdb..#transaction') IS NOT NULL
57 DROP TABLE #transaction
58
59 IF OBJECT_ID('tempdb..#resultHR') IS NOT NULL
60 DROP TABLE #resultHR
61
62
63 CREATE TABLE #transaction(tranid bigint)
64 IF NOT EXISTS(
65 select ag.AgentID
66 from [utnetAgentManagement].[tAgents] ag
67 LEFT JOIN [utnetAgentManagement].[tAgentMembers] am on ag.AgentID=am.AgentID
68 JOIN [utUserManagement].[tUserProfiles] UP on am.UserProfileID=up.UserProfileID AND up.IsSupervisor=0
69 where ag.IsDeleted = 0
70 AND ag.IsSuperAgent=0
71 AND AgentDetails.value( '(/AgentDetails/Supervisor/text())[1]', 'nvarchar(max)' ) is null
72 AND ( am.UserProfileID = @supervisor OR @supervisor is null)
73 AND (@AgentID is null or ag.AgentID = @AgentID)
74 )
75 BEGIN
76 IF(@supervisor=-1)
77 BEGIN
78 insert into #transaction
79 SELECT distinct TransactionReferenceID
80 FROM utnetTeller.tTransactionReferences tr
81 JOIN utUserManagement.tUserProfiles up on tr.LevelAgentSupervisor=up.UserProfileID
82 LEFT JOIN utnetAgentManagement.tAgentMembers am on tr.LevelAgentSupervisor=am.UserProfileID
83
84 WHERE up.IsDeleted = 1 AND am.UserProfileID is null
85 END
86 ELSE IF(@AgentID=-1)
87 BEGIN
88 insert into #transaction
89 SELECT distinct TransactionReferenceID
90 FROM utnetTeller.tTransactionReferences tr
91 JOIN utUserManagement.tUserProfiles up on tr.CreatedBy=up.UserProfileID
92 JOIN utnetAgentManagement.tAgentMembers am on tr.CreatedBy=am.UserProfileID
93
94 WHERE up.IsDeleted = 1
95 AND (LevelAgentSupervisor = @supervisor or @supervisor IS NULL)
96 END
97 ELSE
98 BEGIN
99 insert into #transaction
100 SELECT distinct TransactionReferenceID
101 FROM utnetTeller.tTransactionReferences tr
102 JOIN utUserManagement.tUserProfiles up on tr.LevelAgentSupervisor=up.UserProfileID OR tr.AgentSupervisor=up.UserProfileID
103 OR tr.CreatedBy=up.UserProfileID
104 WHERE (LevelAgentSupervisor = @supervisor OR AgentSupervisor = @supervisor OR @supervisor IS NULL)
105 AND (tr.AgentID = @AgentID OR @AgentID IS NULL)
106 --AND up.IsDeleted = 0
107 --AND up.IsLocked = 0
108 --AND up.IsAuthorized = 1
109 END
110 END
111 ELSE
112 insert into #transaction
113 select tr.TransactionReferenceID
114 from utnetTeller.tTransactionReferences tr
115 WHERE (LevelAgentSupervisor = @supervisor or AgentSupervisor = @supervisor or @supervisor IS NULL)
116 AND (tr.AgentID = @AgentID OR @AgentID IS NULL)
117
118-- select * from #transaction
119
120
121
122 SELECT @TransactionsDataXML = (
123 SELECT
124 tr.AgentID,
125 tr.AgentAccount as floatAccountNumber,
126 CASE WHEN ISNULL(ptr.OperationTypeID,tr.OperationTypeID) = 1
127 THEN tra.ToAccountNumber
128 ELSE tra.FromAccountNumber
129 END AS CustomerAccountNumber,
130 CASE WHEN ISNULL(ptr.OperationTypeID,tr.OperationTypeID) = 3
131 THEN tra.ToAccountNumber
132 ELSE ''
133 END AS ReceiverAccountNumber,
134 CASE WHEN tr.TransactionDetails IS NULL
135 THEN ''
136 ELSE COALESCE(tr.TransactionDetails.value('(/TransactionDetails//RecipientName/node())[1]', 'nvarchar(max)'),'')
137 END AS Receiver,
138 tr.TransactionReferenceID,
139 ISNULL(tr.TransactionUniqueRef,'') AS TransactionUniqueRef,
140 coalesce(pf.Amount,0) AS FeeAmount,
141 coalesce(pc.Amount,0) AS CommissionAmount,
142 coalesce(pc.SuperAgentAmount,0) AS SuperAgentCommissionAmount,
143 tra.FromAccountNumber,
144 tra.ToAccountNumber,
145 CASE WHEN ptr.OperationTypeID = 9 THEN 0 ELSE tra.Amount END AS Amount,
146 tra.CurrencyID,
147 CASE WHEN ptr.OperationTypeID = 3
148 THEN tra.CurrencyID
149 ELSE ''
150 END AS ReceiverCurrencyID,
151 tr.TransactionStatusID,
152 tr.CreatedBy,
153 am.IsOwner,
154 tr.AgentFirstName,
155 tr.AgentLastName,
156 --case when am.IsOwner = '0' then tr.AgentFirstName + ' ' + tr.AgentLastName + ' ('+ isnull(amc.Username, 'No Username')+')' END AS AgentMember,
157 ISNULL(ag.AgentDetails.value( '(/AgentDetails/AgentName/text())[1]', 'nvarchar(max)' ), tr.AgentFirstName + ' ' + tr.AgentLastName) + ' ('+ isnull(aguc.Username, 'No Username')+')' as Agent,
158 tr.AgentBranch Branch,
159 case
160 when up.IsDeleted=1 and tr.CreatedOn>= up.DeletedOn then ''
161 else
162 up.FirstName + ' ' + up.LastName + ' ('+ isnull(sc.Username, 'No Username')+')'
163 end as Supervisor,
164 CONVERT(NVARCHAR(10), tr.CreatedOn, 103) AS CreatedOn,
165 CONVERT(NVARCHAR(5), tr.CreatedOn, 108) AS CreatedOnHour,
166 tr.CreatedOn AS CreatedOnFullDate,
167 ISNULL(ptr.OperationTypeID,tr.OperationTypeID) as OperationTypeID,
168 ot.Name AS OperationType,
169 c.CustomerUniqueRef AS CustomerNumber,
170 c.FirstName + ' ' + c.LastName AS Customer,
171 CASE WHEN pc.CommissionDetails IS NULL
172 THEN ''
173 ELSE pc.CommissionDetails.value('(/CommissionDetails//CommissionTransactionRef/node())[1]', 'nvarchar(max)')
174 END AS CommissionTransactionRef,
175 CASE WHEN pc.CommissionDetails IS NULL
176 THEN ''
177 ELSE pc.CommissionDetails.value('(/CommissionDetails//CommissionPostedDate/node())[1]', 'nvarchar(max)')
178 END AS CommissionPostedDate,
179 ISNULL(pc.CommissionDetails.value('(/CommissionDetails/CommissionApproval/text())[1]', 'nvarchar(100)'),'') as CreatedByName,
180 tr.TransactionDetails as transactionsdetailsxml,
181 tr.TransactionDetails.value('(/TransactionDetails//Narrative/text())[1]', 'nvarchar(max)') as narrative
182 --,ROW_NUMBER() OVER(ORDER BY
183 -- CASE WHEN @sortOrder = 'ASC' THEN
184 -- CASE
185 -- WHEN @sortBy = 'transferDate' THEN tr.CreatedOn --cast(FORMAT ( t.transferDateTime, 'd', 'en-gb') as nvarchar(50))
186
187 -- END
188 -- END,
189 -- CASE WHEN @sortOrder = 'DESC' THEN
190 -- CASE
191 -- WHEN @sortBy = 'transferDate' THEN tr.CreatedOn --cast(FORMAT ( t.transferDateTime, 'd', 'en-gb') as nvarchar(50))
192 -- END
193 -- END DESC) AS rowNum
194 --,COUNT(*) OVER(PARTITION BY 1) recordsTotal
195
196 --INTO #resultHR
197 FROM
198 utnetTeller.tTransactionReferences tr
199 JOIN
200 utnetTeller.tTransactionAmounts tra ON tr.TransactionReferenceID = tra.TransactionReferenceID
201 LEFT JOIN
202 utnetAgentManagement.tProcessedTransactions ptr ON tr.TransactionReferenceID = ptr.TransactionReferenceID
203 LEFT JOIN
204 utnetAgentManagement.tProcessedFees pf ON ptr.ProcessedFeeID=pf.ProcessedFeeID
205 LEFT JOIN
206 utnetAgentManagement.tProcessedCommissions pc ON ptr.ProcessedCommissionID=pc.ProcessedCommissionID
207 LEFT JOIN
208 utnetCustomer.tCustomers c ON tr.CustomerID = c.CustomerID
209 LEFT JOIN
210 utnetAgentManagement.tOperationTypes ot ON ISNULL(tr.OperationTypeID,ptr.OperationTypeID) = ot.OperationTypeID
211 LEFT JOIN
212 utnetAgentManagement.tAgentMembers am on am.UserProfileID = tr.CreatedBy
213 LEFT JOIN
214 utnetAgentManagement.tAgentMembers am2 on am2.AgentID = am.AgentID AND am2.IsOwner='true'
215 LEFT JOIN
216 utnetAgentManagement.tAgents ag on ag.AgentID = am.AgentID
217 LEFT JOIN
218 utUserManagement.tUserProfiles agup on agup.UserProfileID = am2.UserProfileID
219 LEFT JOIN
220 utUserManagement.[tUserProfileCredentials] agupc on agupc.UserProfileID = agup.UserProfileID
221 LEFT JOIN
222 utUserManagement.[tUserCredentials] aguc on aguc.UserCredentialsID = agupc.UserCredentialsID AND aguc.IsSystemCredential = 1
223 LEFT JOIN
224 utUserManagement.tUserProfiles up on up.UserProfileID = tr.OneLevelUpSupervisor --and up.IsDeleted=0--ag.AgentDetails.value( '(/AgentDetails/Supervisor/text())[1]', 'nvarchar(max)' )--tr.AgentSupervisor
225
226 LEFT JOIN (
227 [utUserManagement].[tUserProfileCredentials] spc
228 INNER JOIN [utUserManagement].[tUserCredentials] sc on (sc.UserCredentialsID = spc.UserCredentialsID AND sc.IsSystemCredential = 1)
229 ) on spc.UserProfileID = tr.OneLevelUpSupervisor
230 left join utUserManagement.tUserProfiles up2 on up2.UserProfileID = spc.UserProfileID --and up.IsDeleted=0--ag.AgentDetails.value('(/AgentDetails//Supervisor/node())[1]','nvarchar(100)')--up.UserProfileID
231 LEFT JOIN [utUserManagement].[tUserProfileCredentials] ampc on ampc.UserProfileID = tr.CreatedBy
232 INNER JOIN [utUserManagement].[tUserCredentials] amc on (amc.UserCredentialsID = ampc.UserCredentialsID AND amc.IsSystemCredential = 1)
233
234 WHERE --( tr.AgentID = @AgentID OR @AgentID IS NULL )
235 ( tr.TransactionUniqueRef = @ReferenceNumber OR @ReferenceNumber IS NULL )
236 AND ( tr.OperationTypeID = @OperationTypeID OR @OperationTypeID IS NULL )
237 AND (tr.TransactionStatusID = @Status or @Status IS NULL)
238 AND ( tra.CurrencyID = @CurrencyID OR @CurrencyID IS NULL )
239 AND ( CAST(tr.CreatedOn AS DATE) >= CAST(@DateFrom AS DATE) OR @DateFrom IS NULL )
240 AND ( CAST(tr.CreatedOn AS DATE) <= CAST(@DateTo AS DATE) OR @DateTo IS NULL )
241 and tr.TransactionReferenceID in (select tranid from #transaction)
242 --and (tr.AgentSupervisor = @supervisor OR tr.AgentID = @SuperAgentID OR tr.AgentID in (select id from #agentnet))
243 order by CONVERT(NVARCHAR(10), tr.CreatedOn, 103)
244 OFFSET (@pageNumber-1)*@pageSize ROWS
245 FETCH NEXT @pageSize ROWS ONLY
246 FOR XML PATH('Transaction'), type );
247
248 -- SELECT @TransactionsDataXML = (
249 -- select * ,case when IsOwner = '0' then AgentFirstName + ' ' + AgentLastName + ' ('+ isnull(amc.Username, 'No Username')+')' END AS AgentMember
250 -- ,(recordsTotal - 1) / @pageSize + 1 AS pagesTotal
251 -- from #resultHR
252 -- LEFT JOIN [utUserManagement].[tUserProfileCredentials] ampc on ampc.UserProfileID = CreatedBy
253 --INNER JOIN [utUserManagement].[tUserCredentials] amc on (amc.UserCredentialsID = ampc.UserCredentialsID AND amc.IsSystemCredential = 1)
254
255 -- WHERE rowNum BETWEEN @startRow AND @endRow
256 --ORDER BY rowNum
257 --FOR XML PATH('Transaction'), type );
258
259 SELECT @TransactionsDataXML = (
260 SELECT @TransactionsDataXML
261 FOR XML PATH('Transactions'), type );
262
263 SET @Result = 0
264 SET @ResultMessage = 'utnet_core_sql.GetOperatorTransactions.success'
265END