LCFS - Users can download transactions data

[justin-lepitzki]

Description:

• Enable the ability to download transactions information from the Transactions table into a spreadsheet
• Download as Excel button (see wireframe)
• For both BCeID and IDIR users, although downloaded information will be slightly different. For BCeID users, only their transactions are downloaded whereas IDIR users see all transactions.
• Download the following data from the transactions table: (in order from left to right): ID, Compliance period, Type, Compliance units from, Compliance units to, Number of units, Value per unit, Category, Status, Effective Date, Comments (external)
• For IDIR users, when the filtering function is in use (see card #192), only the filtered transactions should be downloaded. This functionality currently exists in TFRS for downloading the transactions table data.
• File type = .xls
• Workbook name format: BC_LCFS_transactions_YYYY-MM-DD.xls, where YYYY-MM-DD is the day the file was downloaded
• Worksheet name: Transactions
• Spreadsheet columns and values should be formatted in such a way that there is adequate spacing/width for the columns, number formatting uses comma's (43,343), and left or centre indent (see screenshot below)
• The button label switches to "Downloading..." after the user clicks on the button and the sheet is being downloaded (loading). The button then switches back to the regular label after it has downloaded. This functionality currently exists in TFRS and is useful to communicate to user that the system is working (while loading), especially for IDIR users where the downloaded data can take some time due to its larger size.

Note: This feature is near identical to the one that exists in TFRS

Wireframe:

• UXPin: https://preview.uxpin.com/59145260c7d8cc48969ac79401cc03d84843c3d9#/pages/165270334/simulate/no-panels

• Screenshot of example spreadsheet from TFRS:

• "Downloading..." button example from TFRS: Private Zenhub Image

Purpose and benefit to user: Users can easily and efficiently download a list of transactions.

Acceptance Criteria:

• [ ] Given I am a BCeID user, when I download transactions, then I should receive an Excel file containing only my transactions.
• [ ] Given I am an IDIR user, when I download transactions without applying filters, then I should receive an Excel file containing all transactions.
• [ ] Given I am an IDIR user, when I download transactions with filters applied, then the Excel file should only include the filtered transactions.
• [ ] Given I am a user, when I click the "Download as Excel" button, then the button should change to "Downloading..." until the download is complete.

Development Checklist:

• [ ] Use SpreadsheetBuilder from backend/lcfs/utils/spreadsheet_builder.py for generating the Excel file.
• [ ] Refer to the implementation in frontend/src/views/Organizations/Organizations.jsx. Specifically, examine DownloadButton and handleDownloadOrgs for guidance on integrating the download functionality.
• [ ] Write front-end tests to validate the download button behavior, including state changes and file generation. Ensure tests cover both BCeID and IDIR user scenarios.
• [ ] Create Pytest cases to test.
• [ ] Develop Cypress e2e tests to simulate the complete download process for both user types.

Notes:

[justin-lepitzki]

Hey team! Please add your planning poker estimate with Zenhub @AlexZorkin @hamed-valiollahi @kevin-hashimoto @prv-proton

[Grulin]

Found the following issues:

1) The transactions are downloading on the BCeID side, but not on the IDIR side for all organizations (Only works if you filter by organization). We want to be able to download all transactions from all organizations.

2) Compliance period, category and effective date columns are not showing any information in the Excel sheet. We should also include an 'Approval date' column if possible.

Please see screenshot of Excel sheet:

[hamed-valiollahi]

@Grulin , Thanks, Lindsy, for testing and reporting the issues.

• I fixed the routing issue on the IDIR side.
• For the "Compliance period", I couldn't find any stored information in the database, so I added the column, but the values for the records will be empty. Please let me know if this information is already stored in the database.
• Regarding "category," it already shows the transfer category, but there is no category stored for admin adjustment and initiative agreement in the database.
• The issue with the effective date is now resolved. If there is a transaction effective date stored for a transaction in the database, it will be shown.
• For the "Approval date," I couldn't find any information in the database. I added the column, but the values for the records will be empty. Could you please clarify what it means for different transactions (transfer, admin adjustment, and initiative agreement)?

Thank you

[Grulin]

Hi Hamed,

Thank you for the notes. Your first 4 points make sense. I only just realized that compliance period likely has not bee established yet since the fuel supply reporting sections are not complete. I think this will be added later.

For the final point, I think we should drop the column "Approval Date" and relabel the "Effective date" column to read "Recorded/Approved Date" if possible?

[hamed-valiollahi]

Hi Lindsy,

Thank you for reviewing my comments and providing your feedback.

... we should drop the column "Approval Date" and relabel the "Effective Date" column to "Recorded/Approved Date" if possible?

I'm sorry, I'm not sure if I understand this part correctly. Let me share my understanding so you can correct me if I'm wrong.

We have three types of transactions: Transfer, Admin Adjustment, and Initiative Agreement. Each type has its own "Effective Date", which we store as a date and time.

Also, each transaction type has several statuses, and we create a history record whenever the status changes with a date/time.

Transfer:

• Draft
• Deleted
• Sent
• Submitted
• Recommended
• Recorded
• Refused
• Declined
• Rescinded

Admin Adjustment:

• Draft
• Recommended
• Approved
• Deleted

Initiative Agreement:

• Draft
• Recommended
• Approved
• Deleted

So, should we drop the "Approval Date" and "Effective Date" columns, and add a "Recorded" column for transfers and an "Approved" column for admin adjustments and initiative agreements?

Thank you

[Grulin]

Hi Hamed,

Sorry for the confusion. Yes, your proposal here makes sense. Please drop the "Approval Date" column, but please keep the "Effective Date" column for admin adjustments and initiative agreements. This date can differ from the approved date for the admin adjustments and initiative agreements. It comes from the grey box in the transaction, rather than the status change:

Please add a "Recorded" column for transfers and an "Approved" column for admin adjustments and initiative agreements. Ultimately this is the date we track in our market reports for transfers, so it makes sense to have these displayed in this Excel sheet.

[hamed-valiollahi]

Thank you for the clarification, Lindsy. I appreciate it. I'll apply the requested changes.

[Grulin]

Hi @hamed-valiollahi This download looks great. The columns are now correct, but I was hoping we could only show "Approved" and "Recorded" status transactions in the "Status" column. Sorry I didn't make that clear before. It's currently showing all the different statuses (including draft, submitted and recommended). Thank you.