Generate-Monthly-Statement-for-User-Account¶

Generating an Account Statement for a Specific User in a Specific Month¶

This guide provides a comprehensive step-by-step process for generating an account statement for a specific user 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.

1. Query Execution in BigQuery¶

Login to BigQuery using the Majority Google account.
Create a new query in the dw-prod-gwwiiag resource.
Copy and paste the following SQL query into the worksheet, adjusting the variables (includedStartDate, endDate, userId) as necessary for the specific user and month:

SET (includedStartDate, endDate) = ('2024-02-01', '2024-03-01');  
SET userId = '<guid>';

with galileo_accounts as (
    select
        c.prn
        , c.galileo_account_id
        , c.work_of_date
        , c.current_balance
        , c.available_balance
    from s3.galileo_customer_master_file c
    where
        c.work_of_date < $endDate --end month for statement
        and c.work_of_date >= $includedStartDate --start month for statement
)

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

select
 DISTINCT (c.work_of_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
where coalesce(mc.user_id, ml.account_holder_id, mc2.user_id, ml2.account_holder_id)= $userId 
order by date desc

Update the variables for the specific user and month you want the account statement.
Execute the query in BigQuery to get the latest balances for the user for the specified month.
Export the data in CSV format or keep the query results open for manual creation of insert scripts.

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¶

If you exported the CSV files, follow these steps:
- 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.
- 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;

If manually creating insert scripts, create the following insert script for each data point from the Snowflake query result:

INSERT INTO [dbo].[AccountBalance] (Id, UserId, LedgerAccountId, Date, AvailableBalance, LedgerBalance)
VALUES (NEWID(), '<GUID>', '<GUID>', '<DATE>', '<MONEY>', '<MONEY>');

4. 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 GenerateMonthlyStatementForUser(Guid userId, Guid accountId,  DateTime fromDate, DateTime? toDate = null)
        {
            const bool dryRun = false;
            var publisher = new CustomEventSender<CreateMonthlyStatementEvent>(dryRun, _connectionString, new LogHelper(new SimpleLogger()));

            var events = new List<CreateMonthlyStatementEvent>
            {
                    new() {
                        UserId = userId,
                        AccountId = accountId,
                        FromDate = fromDate,
                        ToDate = toDate ?? fromDate.AddMonths(1).AddMilliseconds(-10),
                        DeleteExistingStatement = true,
                    }
            };

            await publisher.PublishEvents(events, null, TimeSpan.FromHours(1));
        }

Call the above method in the Process method with the correct GUIDs and date:

await GenerateMonthlyStatementForUser(
    userId: Guid.Parse("<GUID>"),
    accountId: Guid.Parse("<GUID>"),
    fromDate:  new DateTime(year: 2025, month: 01, day: 01, 0, 0, 0, 0, DateTimeKind.Utc),
    toDate:  new DateTime(year: 2025, month: 01, day: 01, 0, 0, 0, 0, DateTimeKind.Utc));

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

5. Verification¶

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