Debt Model Database

[amolkittur]

Convert Excel File to Database, Implement Data Flow, and Integrate FastAPI Endpoints

Description

We need to convert our current Excel file to a database, implement a specific data flow in our PyQt application, and integrate FastAPI endpoints for communication between the PyQt app and the database.

Required Data Flow

1. Update Transactions

   • Check if the user is already present in the freedom blast/accounts if not then we have to do the manual entry else it will processed automatically
   • User inputs values through the PyQt interface
   • The values should be updated in the excel as well as the database using the endpoint.
   • These values should be appended to the "Transactions(Past)" sheet/table

2. Account Verification and Creation

   • Check if the AccID exists in the "Accounts(Present)" sheet/table
   • If not present, create a new account
   • If present, show the entry in the "5_Hand Loans" sheet/table

3. Balance Update in "5_Hand Loans"

   • Check if there's an entry with the same AccID in "5_Hand Loans"
   • If not present, enter the data without modifications
   • If present: a. Fetch the previous entry b. Get the value from the Balance column c. Add the new Amount to the previous Balance d. Write the updated Balance in a new row
   4. Update the same in the DB
      • Instead of converting the entire excel file we can convert only the necessary sheets to DB and keep on appending the values there

Tasks

1. [ ] Convert the Excel file to a database format
2. [ ] Modify the PyQt application to interact with the new database
3. [ ] Implement the data flow logic as described above
4. [ ] Implement FastAPI endpoints for database operations
5. [ ] Integrate FastAPI endpoints into the PyQt application
6. [ ] Test the new system thoroughly to ensure data integrity and correct flow
7. [ ] Document the new database structure, data flow, and API endpoints for future reference

FastAPI Integration

• [ ] Design and implement the following endpoints:
  • GET endpoint to retrieve account information
  • POST endpoint to create new transactions
  • PUT endpoint to update account balances
  • Any other necessary CRUD operations
• [ ] Ensure proper error handling and validation in the API
• [ ] Implement authentication and authorization for the API endpoints
• [ ] Update the PyQt application to use these endpoints for all database operations

Additional Notes

• Ensure data consistency between the old Excel format and the new database
• Consider implementing error handling and validation in the PyQt interface
• Think about potential performance optimizations for larger datasets
• Use asynchronous programming in both FastAPI and PyQt where appropriate to improve responsiveness
• Implement proper logging for both the API and the PyQt application

[amolkittur]

---

API Endpoint Development for Transaction Management

Overview

We need to develop several API endpoints to handle transactions imported from an Excel file and perform various operations such as fetching, modifying, deleting, and verifying transactions. Below is a detailed description of each endpoint, including methods, URLs, parameters, and functionalities.

---

Table of Contents

1. Import Transactions from Excel
2. Modify a Transaction
3. Delete a Transaction
4. Fetch Weekly Transactions
5. Additional Considerations
6. Summary of Endpoints

---

1. Import Transactions from Excel

Endpoint: transactions/import-transactions
Method: POST

Description:

• Fetch transactions from the TransactionsPast sheet in the Excel file.
• Filter transactions whose comments include the specified month (modifiable).
• Store these transactions into corresponding database files named after their AccID (e.g., Tr02_debit.db).

Parameters:

• Query Parameters:
  • month (optional): The name of the month to filter transactions. Defaults to the current month if not specified.

---

2. Modify a Transaction

Endpoint: /transactions/{trNo}/{transactionId} **Method:**PUT`

Description:

• Locate the database file corresponding to trNo from the designated folder.
• Search for the transaction with transactionId in the specified table.
• Update the transaction with the provided column names and values.

Parameters:

• Path Parameters:
  • trNo: The transaction number or account identifier (e.g., Tr02).
  • transactionId: The unique identifier of the transaction to be modified.

Request Body:

• An object containing the column names and their new values.

{
  "columnName1": "newValue1",
  "columnName2": "newValue2"
  // Add additional columns and values as needed
}

Need to decide on how to pass which fields to modify or how to send only which values needs to be changed

---

3. Delete a Transaction

Endpoint: /transactions/{trNo}/{transactionId}
Method: DELETE

Description:

• Access the database file for trNo from the folder where all the databases are located.
• Find the transaction with transactionId in the specified table.
• Delete the transaction row.

Parameters:

• Path Parameters:
  • trNo: The transaction number or account identifier.
  • transactionId: The unique identifier of the transaction to be deleted.

---

4. Fetch Weekly Transactions

Endpoint: /transactions/{trNo}/weekly
Method: GET

Description:

• Retrieve all transactions for a specific trNo that occurred during the specified week.
• This allows verification of transactions against your Excel file.

Parameters:

• Path Parameters:
  • trNo: The transaction number or account identifier.
• Query Parameters:
  • weekStart (optional): The start date of the week in YYYY-MM-DD format. Defaults to the current week if not provided.
  • weekEnd (optional): The end date of the week. Defaults to seven days after weekStart if not provided.

---

5. Summary of Endpoints

1. Import Transactions

   POST /transactions/import?month=September

2. Modify Transaction

   PUT /transactions/{trNo}/{transactionId}
   Body: { "columnName1": "newValue1", "columnName2": "newValue2" }

3. Delete Transaction

   DELETE /transactions/{trNo}/{transactionId}

4. Fetch Weekly Transactions

   GET /transactions/{trNo}/weekly?weekStart=YYYY-MM-DD&weekEnd=YYYY-MM-DD

---

Action Items

• [ ] Implement the Import Transactions endpoint to fetch and store transactions from the Excel file.
• [ ] Develop the Modify Transaction endpoint to update transaction details based on transactionId and trNo.
• [ ] Create the Delete Transaction endpoint to remove transactions from the database.
• [ ] Build the Fetch Weekly Transactions endpoint to retrieve transactions within a specified week.
• [ ] Ensure all endpoints include proper error handling and data validation.
• [ ] Consider adding authentication mechanisms if required.
• [ ] Document each endpoint with examples and response formats.

---

Note: Adjust the endpoints and parameters as necessary to fit the specific requirements of your application. Feel free to suggest improvements or additional features that could enhance the system.

---

Additional Notes

• Database Structure: Decide on the table names and structure within each *_debit.db file.
• Excel Integration: Determine how the Excel file will be accessed (e.g., file path, uploaded via API).
• Transaction Identification: Ensure that each transaction has a unique transactionId for accurate retrieval and modification.
• Testing: Write unit and integration tests for each endpoint to ensure reliability.

---

Let me know if there's anything else you'd like to add or modify!