Generate-Monthly-Statement-for-all-users¶

Generating Account Statements for all users in a Specific Month¶

This guide provides a comprehensive step-by-step process for generating an account statement for all users within a specified month. It covers the workflow from querying the data in BigQuery, preparing the data for insertion into an Azure SQL database, and finally triggering the generation of the monthly statement through a C# application.

Prerequisites¶

BigQuery Studio access in the environment "dw-prod.
Admin permissions in the Azure SQL production database.
Access to Azure DevOps for temporary permissions to production databases.
Cloning access for the bank-backend repository from Azure DevOps.
SQL database tool to handle big load of data (DBeaver is an option)

1. Query Execution in BigQuery¶

Connect to BigQuery using DBeaver (or similar tool).
Copy and paste the following SQL query into the worksheet, adjusting the variables (_includedStartDate, endDate) as necessary for the specific month:

with galileo_accounts as (
select
        c.prn
        , c.galileo_account_id
        , c.last_transaction_date
        , c.current_balance
        , c.available_balance
    from s3.galileo_customer_master_file_extended c
    where
        c.last_transaction_date < '2025-04-01' //update non inclusive end date
        and c.last_transaction_date >= '2025-03-01' //update inclusive start date
)

, galileo_cards as (
    select
        prn
        , galileo_account_id
        , row_number() over (partition by galileo_account_id order by card_status_update_date desc) rnk
    from `s3.galileo_account_card_extended`
    qualify rnk = 1
)

select
 DISTINCT (c.last_transaction_date) as date,
    coalesce(mc.user_id, ml.account_holder_id, mc2.user_id, ml2.account_holder_id) as user_id,
    c.prn as customer_prn
    , c.galileo_account_id as customer_galileo_account_id

    , c.current_balance as customer_current_balance
    , c.available_balance as customer_available_balance
    , coalesce(ml.ledger_id, ml2.ledger_id) as ledger_id
from galileo_accounts c
left join  galileo_cards ac on c.galileo_account_id = ac.galileo_account_id
left join dbt.fact_card mc on coalesce(mc.external_account_id, mc.external_reference_id) = ac.prn
left join dbt.fact_card mc2 on coalesce(mc2.external_account_id, mc2.external_reference_id) = c.prn
left join dbt.dim_ledger ml on ml.is_current and coalesce(mc.ledger_account_id, mc2.ledger_account_id) = ml.ledger_id
left join dbt.dim_ledger ml2 on ml2.is_current and c.prn = ml2.external_reference_id

order by date desc

Update the variables for the month you want the account statements.
Execute the query to get the latest balances for the user for the specified month.
Export the data in CSV format (Make sure to set Extract Type as Single Query, and Disable "Open new connections" option since because it causes issues with transactions

2. Azure SQL Database Preparation¶

Obtain admin permission in the Azure SQL production database via Azure DevOps > Pipelines > Temporary access to prod databases.
Authorize yourself to the AccountStatement production database.

3. Data Import and Script Execution¶

Create a table named "LatestBalancesCSV" by importing the flat file (your CSV file).
Update columns using the following conversions:
Balances -> money (convert balance types to money type)
Int -> nvarchar (convert int types to nvarchar)
Allow nulls for each column
Wait for the import to finish.

Before Inserting the data into the Account Balance, make sure that all the balances data is correct.¶

There are cases when we get balances, but for some PRN we don't have either userId or ledgerId, and we need to make sure that this is not because of missing infomration

4. Run the below query to check whether there is missing userId or LedgerId in "LatestBalancesCSV" table¶

SELECT * FROM LatestBalancesCSV WHERE UserId IS NULL OR LedgerId IS NULL; 
--or in case UserId and LedgerId are varchar 
SELECT * FROM LatestBalancesCSV WHERE UserId IS NULL OR LedgerId IS NULL OR (LEN(UserId) != 36 or LEN(LedgerId) != 36)

If we see some PRN that have no UserId or LedgerId we need to make sure we have the data in wallet database.
Run the following queries to check whether this PRN is related to an account and user:

select C.CardId, A.AccountHolderId, C.AccountId from [Card] C JOIN Account A on C.AccountId = A.AccountId 
where ExternalAccountId = '{PRN}'
OR
select L.Id, L.AccountHolderId, L.AccountId from [LedgerAccount] L  
where L.ExternalReferenceId= '{PRN}'

If the data is found, try to manually update the records in the "LatestBalancesCsv" table with correct infomration, and also
Bring up the issue with the team

6. Execute the following SQL script to add data to the account statements database from the "LatestBalancesCSV" table¶

INSERT INTO [dbo].[AccountBalance]
(Id, UserId, LedgerAccountId, Date, BalanceTimestamp, AvailableBalance, LedgerBalance)
SELECT NEWID() AS Id, * FROM (
    SELECT DISTINCT 
    UserId, 
    LedgerId, 
    CAST([Date] AS DATE) AS [DATE],
    DATEADD(second, 86399, CAST(T.[Date] AS DATETIME)) AS BalanceTimestamp,
    ISNULL(CustomerAvailableBalance, 0) AS [AvailableBalance],
    ISNULL(CustomerCurrentBalance, 0) AS [LedgerBalance]
    FROM [dbo].[LatestBalancesCSV] AS LB
    WHERE (UserId IS NOT NULL) AND (LedgerId IS NOT NULL) AND 
    NOT EXISTS (
        SELECT 1 FROM AccountBalance AB WHERE AB.UserId = LB.UserId AND AB.LedgerAccountId = LB.LedgerId AND CAST(LB.[Date] AS DATE) = AB.[Date]
    )
) AS T;

7. Generating Monthly Statement¶

Clone the bank-backend repository from Azure DevOps.
Open the Minority/Minority.Tools/SendEventsToServiceBus solution.
In the Program class, ensure all method calls are commented out except for the new method GenerateMonthlyStatementForUser:

        private static async Task PublishMonthlyStatementPartial(string yearMonth)
        {
            const bool dryRun = false;

            var monthlyStatementEvent = new PartialPublishMonthlyStatementsEvent
            {
                Month = yearMonth, 
                AfterAccountId = null,
                Step = 500
            };

            var publisher = new CustomEventSender<PartialPublishMonthlyStatementsEvent>(dryRun, _connectionString, new LogHelper(new SimpleLogger()));

            await publisher.PublishEvents(new List<PartialPublishMonthlyStatementsEvent> { monthlyStatementEvent }, null, TimeSpan.FromDays(1));
        }

Call the above method in the Process method with the correct month:

await PublishMonthlyStatementPartial(yearMonth: "2023-03");

Run the project with the production connection string to publish the event for generating the monthly statement for the specific user.

8. Verification¶

Check Kibana for the events "PartialPublishMonthlyStatementsEvent" "CreateMonthlyStatementEvent" and ensure there are no issues and the response is successful.