Parametrem na wejściu dla wszystkich sprawdzeń jest <external_user_id>

Pobranie listy sesji dla wskazanego usera z bazy SQL po <external_user_id>:

1 2SELECT apis.requestdate, apis.externaluserid, 3CASE 4 WHEN apis.status = 1 THEN 'Initiated' 5 WHEN apis.status = 2 THEN 'AuthenticationRequired' 6 WHEN apis.status = 3 THEN 'InProgress' 7 WHEN apis.status = 4 THEN 'Finished' 8 WHEN apis.status = 5 THEN 'AuthorizationFailed' 9 WHEN apis.status = 6 THEN 'Failed' 10 WHEN apis.status = 7 THEN 'DeletingConsent' 11 ELSE 'other' 12END AS SessionStatus, 13CASE 14 WHEN [stage] = 1 THEN 'Authentication' 15 WHEN [stage] = 2 THEN 'Accounts' 16 WHEN [stage] = 3 THEN 'Transactions' 17 WHEN [stage] = 4 THEN 'Payment' 18 WHEN [stage] = 5 THEN 'TransactionDetails' 19 WHEN [stage] = 6 THEN 'DeleteConsent' 20 WHEN [stage] = 7 THEN 'Expired' 21 ELSE ' other' 22END AS Stage, 23CASE 24 WHEN [sessionsource] = 1 THEN 'Authorize' 25 WHEN [sessionsource] = 2 THEN 'RefreshActiveAccounts' 26 WHEN [sessionsource] = 3 THEN 'Batch' 27 WHEN [sessionsource] = 4 THEN 'DeleteConsent' 28 ELSE ' other' 29END AS SessionSource, 30[failuremessageid], 31[accountsfetchedcount], 32[accountsprocessedcount], 33(SELECT NAME FROM [dbo].bank b WHERE b.id = apis.bankid) AS BankName 34FROM [dbo].apisession apis 35WHERE requestdate > Dateadd(day, -10, (getdate())) and apis.externaluserid='<external_user_id>' 36ORDER BY RequestDate DESC

Pobranie trace z logów Mongo dla kolekcji dziennej odpowiadającej RequestDate i failuremessageid (obie wartości pobierana z listy sesji dla usera, jak powyżej)

.find( {'executionData.logId':'failuremessageid'} ).sort({date:-1})

W wyniku otrzymujemy <executionid> które należy wykorzystać w zapytaniu o trace:

.find({'executionData.id':' *<executionid>*'}).sort({date:-1})

Pobranie id dla wskazanych userów z bazy SQL:

select id from aspnetusers where externalid in ('<external_user_id>')

w wyniku powinniśmy uzyskać x identyfikatorów <userid>

Da każdego z nich należy wykonać 2 kroki (Mongo) i przesłać wyniki:

Krok 1: zapytanie 1 do Mongo
​db.getCollection('Consent').find({'userId':UUID('<userid>')})

Krok 2: zapytanie 2 do Mongo

//przekleić wartość consent id , czyli _id z wyniku powyższego zapytania
​db.getCollection('ConsentHistoryItem').find({'additionalInformation.consentId':UUID('<consentid>')})

Dla każdego <userid> powtórzyć powyższe 2 kroki.

DLa każdego <userid> prośba o przesłanie wyników obu zapytań (najlepiej nazwanych userid_1 i user_id2

select bai.Value,com.ExternalId, bac.[Id],bac.[CreateDate],bac.[BankId],bac.[IsActive],bac.[SyncFromDate],bac.[SyncLastDate],bac.[SyncStatus],bac.[AccountTypeName],bac.[BankAccountType],
​(select max(date) from BankAccountBalance bab where bab.BankAccountId=bac.id and bab.BalanceType=1) as ClosingAvailable,

(select max(date) from BankAccountBalance bab where bab.BankAccountId=bac.id and bab.BalanceType=2) as ClosingBooked,

(select count(*) from [transaction] t where t.BankAccountId=bac.Id) as count_trx,bas.*
​from BankAccount bac join Company com on bac.CompanyId=com.Id join BankAccountIdentifier bai on bac.Id=bai.BankAccountId join BankAccountSync bas on bas.BankAccountId=bac.Id
​where com.ExternalId in ('<external_user_id>')

Zapytanie do API które zwróci informację o rachunkach wskazanego usera

curl --location --request GET 'https://<HOST>/api/v2/bankaccount?userId=<external_user_id>'
​--header 'api-clientid: <api-clientid>'
​--header 'Accept-Language: en-gb'
​--header 'Content-Type: application/json'
​--header 'IP: 121.1.0.1'
​--header 'api-userId: <external_user_id>''

SELECT IIF(bab.[amount]>0, '> 0','<= 0') as amount
,bab.[BalanceType]
,bab.[BankAccountId]
,(select bai.Value from bankaccount ba, bankaccountidentifier bai where bai.BankAccountId=ba.id and ba.id=bab.BankAccountId and bai.SchemeId=1) as IBAN
,(select c.ExternalId from bankaccount ba, company c where ba.companyId=c.id and ba.id=bab.BankAccountId) as externaluserid
,[IsUpToDate]
FROM [BankAccountBalance] bab where BankAccountid in (select ba.Id from bankaccount ba join company c on c.id=ba.CompanyId where c.ExternalId in ('<external_user_id>'))

Słownik wartości BalanceType

Gotowy URL do API po UserId albo ExternalUserId - można zwrócić sobie dane dotyczące statement

1select distinct concat ('/api/user/id/',uc.UserId,'/account/id/',ba.Id,'/statements') as API_URL 2from [transaction].[StatementTransaction] as tst 3join BankAccount as ba on ba.Guid = tst.AccountId 4join UserCompany as uc on uc.CompanyId = ba.CompanyId 5join UserApiClient as uac on uac.UserId = uc.UserId 6where 7--uac.UserId = '' 8uac.UserExternalId = ''

1declare @from Date 2declare @to Date 3 4set @from = cast('2021-01-01' as Date) 5--set @to = cast('2021-10-13' as Date) 6 7select distinct concat ('/api/user/id/',uc.UserId,'/account/id/',ba.Id,'/statements?dateFrom=',@from) as API_URL 8from [transaction].[StatementTransaction] as tst 9join [transaction].[StatementBatch] as tsb on tst.AccountId = tsb.AccountId 10join BankAccount as ba on ba.Guid = tst.AccountId 11join UserCompany as uc on uc.CompanyId = ba.CompanyId 12join UserApiClient as uac on uac.UserId = uc.UserId 13where 14uac.UserId = '35b52d1c-8ac1-4752-8d52-5ab959bcb1f6' and 15--uac.UserExternalId = '35b52d1c-8ac1-4752-8d52-5ab959bcb1f6' and 16tsb.DateFrom > @from --and 17--tsb.DateTo = '2021-10-12'