How to use Python, PySide6 and Google Sheets to make a Database App (5): Add Transactions

This part is about building the Transaction part.

First, handles user interactions with the menu in the application:

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client)
    # ....


    def handle_secondary_action(self, top_level_item, second_level_item):
        # Handle the action based on the top-level and second-level item
        if top_level_item == "Investors" and second_level_item == "Add Investor":
            self.display_add_investor_form()
        elif top_level_item == "Accounts" and second_level_item == "Add Account":
            self.display_add_account_form()        
        elif top_level_item == "Transactions" and second_level_item == "Add Transaction":
            self.display_transaction_form()

Display Transaction Form

Since the transaction is based on an account, the next thing after the user clicks the menu is to pop up an account list.

    def display_transaction_form(self):
        # Clear existing content
        self.clear_content()

        dialog = SelectAccountDialog(self.google_sheets_client)
        result = dialog.exec()

        if result == QDialog.Accepted:
            selected_id = dialog.getSelectedAccountID()
            if selected_id:
                account_data = self.fetch_account_data(selected_id)
                if account_data:
                    self.showTransactionForm(account_data)

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client)
    # ....


    def fetch_account_data(self, account_id):
        # Connect to Google Sheets and select the appropriate worksheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("02_Accounts")

        # Get the header row (first row) to map column names to their indices
        header_row = worksheet.row_values(1)
        column_index_map = {name: index for index, name in enumerate(header_row)}

        # Ensure all required columns are present
        required_columns = ["ID_Account", "ID_Investor", "Type", "Last Name", "First Name", "Company Name", "Bank Name", "Branch Number", "Account Number"]
        for column in required_columns:
            if column not in column_index_map:
                print(f"Column '{column}' not found in the worksheet.")
                return None
        
        # Get all values in the 'Account ID' column
        account_id_column = worksheet.col_values(column_index_map["ID_Account"] + 1)  # +1 for 1-based index

        # Find the row index for the matching account_id
        try:
            row_index = account_id_column.index(account_id) + 1  # +1 because spreadsheet indices start at 1
        except ValueError:
            # Account ID not found
            return None
        
        # Fetch only the row with the matching Account ID
        row_data = worksheet.row_values(row_index)

        # Construct account data ensuring all keys exist in column_index_map and row_data
        account_data = {}
        for key in required_columns:
            if key in column_index_map and column_index_map[key] < len(row_data):
                account_data[key] = row_data[column_index_map[key]]
            else:
                account_data[key] = "N/A"  # or any default value you prefer

        return account_data

    def showTransactionForm(self, account_data):
        # Create the TransactionForm with the fetched account data
        transaction_form = TransactionForm(self.google_sheets_client, account_data)
        self.content.layout().addWidget(transaction_form)

TransactionForm Class

Purpose: This class creates a form for adding a transaction.

• Initialization (__init__):
• Parameters:
• google_sheets_client: An object to interact with Google Sheets.
• account_data: A dictionary containing data related to a specific account.

class TransactionForm(QWidget):
    def __init__(self, google_sheets_client, account_data):
        super().__init__()

        self.google_sheets_client = google_sheets_client
        self.worksheet_name = "03_Transactions"

        # Extract investor and account data
        self.display_investor_data = {key: value for key, value in account_data.items() if key in ["ID_Investor", "Type", "Last Name", "First Name", "Company Name"]}
        self.display_account_data = {key: value for key, value in account_data.items() if key not in self.display_investor_data}

        self.investor_labels = list(self.display_investor_data.keys())
        self.investor_fields = list(self.display_investor_data.values())

        self.account_labels = list(self.display_account_data.keys())
        self.account_fields = list(self.display_account_data.values())



        self.transaction_labels = ["Money In:", "Money Out:", "Transaction Date:"]
        self.transaction_fields = ["Money In", "Money Out", "Transaction Date"]

        # Initialize input fields
        self.input_fields = {field: QLineEdit() for field in self.transaction_fields}

        # Initialize UI
        self.initUI()

Functionality:

• Extracts relevant investor and account information from account_data.
• Initializes labels and fields for displaying investor and account information, and for inputting transaction details.
• Calls initUI to set up the user interface.

initUI Method:

• Sets up the layout and user interface elements of the form.
• Creates a scrollable area with group boxes for displaying investor and account information, and a separate group box for transaction details.
• Adds a submit button for submitting the transaction data.

class TransactionForm(QWidget):
    def __init__(self, google_sheets_client, account_data):
        # ...

    def initUI(self):
        # Main layout within a scrollable area
        scroll = QScrollArea(self)
        scroll.setWidgetResizable(True)
        content_widget = QWidget()
        scroll.setWidget(content_widget)
        layout = QVBoxLayout(content_widget)

        # Add group boxes without additional stretching between them
        layout.addWidget(self.create_group_box("Investor Information", self.investor_labels, self.investor_fields, is_editable=False))
        layout.addSpacing(20)
        layout.addWidget(self.create_group_box("Account Information", self.account_labels, self.account_fields, is_editable=False))
        layout.addSpacing(20)
        layout.addWidget(self.create_group_box("Transaction Details", self.transaction_labels, self.transaction_fields, is_editable=True))


        # Main layout
        main_layout = QVBoxLayout(self)
        main_layout.addWidget(scroll)

        # Submit Button outside the scroll area, centered at the bottom
        self.submit_button = QPushButton("Submit")
        self.submit_button.clicked.connect(self.submit_transaction)
        button_layout = QHBoxLayout()
        button_layout.addStretch(1)
        button_layout.addWidget(self.submit_button)
        button_layout.addStretch(1)

        main_layout.addLayout(button_layout)
        self.setLayout(main_layout)

create_group_box Method:

• Creates and returns a QGroupBox with a specified title.
• Depending on the is_editable flag, it adds either read-only line edits with existing data or empty line edits for new input.
• Arranges the labels and line edits in a grid layout within the group box.

class TransactionForm(QWidget):
    def __init__(self, google_sheets_client, account_data):
        # ...

    def create_group_box(self, title, labels, fields, is_editable=True):
        group_box = QGroupBox(title)

        group_box.setAlignment(Qt.AlignCenter)  # Center the title
        title_font = QFont()
        title_font.setPointSize(QLabel().font().pointSize())  # Set the font size to match label size
        group_box.setFont(title_font)

        layout = QGridLayout(group_box)

        for i, field_name in enumerate(fields):
            row, col = divmod(i, 2)

            label = QLabel(labels[i])
            label.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            label.setWordWrap(True)
            layout.addWidget(label, row, col * 2)

            # Determine the widget based on the editability
            if is_editable:
                widget = self.input_fields.get(field_name, QLineEdit())
            else:
                widget = QLineEdit(str(field_name))
                widget.setReadOnly(True)
                widget.setStyleSheet("background-color: lightgrey;")
            
            layout.addWidget(widget, row, col * 2 + 1)

        layout.setColumnStretch(0, 1)
        layout.setColumnStretch(1, 1)
        layout.setColumnStretch(2, 1)
        layout.setColumnStretch(3, 1)

        group_box.setLayout(layout)
        return group_box

submit_transaction Method:

• Gathers the data entered in the transaction fields.
• Updates this data with the investor and account information.
• Attempts to submit this combined data to a specific worksheet in Google Sheets.
• Clears the input fields after submission or shows an error message if the submission fails.

class TransactionForm(QWidget):
    def __init__(self, google_sheets_client, account_data):
        # ...

    def submit_transaction(self):
        # Collect data from the transaction fields
        transaction_data = {key: field.text() for key, field in self.input_fields.items()}


        transaction_data.update(self.display_investor_data)
        transaction_data.update(self.display_account_data)

        # TODO: Validate transaction data before submission

        # Submit the transaction data to Google Sheets
        try:
            worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet(self.worksheet_name)

            # Get column headers from the first row of the worksheet
            column_headers = worksheet.row_values(1)

            # Prepare row_data according to the order of column headers
            row_data = [transaction_data.get(header, "") for header in column_headers]

            # Update the cells starting from the first column of the next row
            worksheet.append_row(row_data)
            
            QMessageBox.information(self, "Success", "Transaction data submitted successfully.")
        except Exception as e:
            QMessageBox.critical(self, "Error", f"Failed to submit transaction data: {e}")

        self.clear_input_fields()

    def clear_input_fields(self):
        # Clear all input fields
        for widget in self.input_fields.values():
            widget.clear()    

SelectAccountDialog Class

Purpose: Creates a dialog to select an account from a list.

• Initialization (__init__):

Parameters:

• google_sheets_client: An object to interact with Google Sheets.

Functionality:

• Sets up the window title, size, and layout.
• Initializes a table to display account information.
• Adds “OK” and “Cancel” buttons for user actions.

class SelectAccountDialog(QDialog):
    def __init__(self, google_sheets_client):
        super().__init__()
        
        self.google_sheets_client = google_sheets_client
        self.selected_account_id = None

        self.setWindowTitle("Select Account")
        self.setGeometry(100, 100, 600, 400)

        self.desired_columns = ["ID_Account", "ID_Investor", "Type", "Last Name", "First Name", "Company Name", "Bank Name", "Branch Number", "Account Number"]

        # Center the window on the screen
        self.center_window()

        self.initUI()


    def center_window(self):
        # Get the primary screen
        screen = QApplication.primaryScreen()

        # Get the size of the screen
        screen_geometry = screen.geometry()

        # Calculate the position to center the window
        x = (screen_geometry.width() - self.width()) // 2
        y = (screen_geometry.height() - self.height()) // 2

        # Set the position
        self.move(x, y)

initUI Method:

• Configures the user interface of the dialog.
• Sets up a table to list accounts, fetching data from a Google Sheet.
• Configures the table to be non-editable and to allow selection of rows.
• Defines the actions for double-clicking a row and clicking the “OK” or “Cancel” buttons.

class SelectAccountDialog(QDialog):
    def __init__(self, google_sheets_client):
        #...


    def initUI(self):
        layout = QVBoxLayout(self)

        # Table to display accounts
        self.table = QTableWidget()
        self.table.setSelectionBehavior(QTableWidget.SelectRows)  # Select entire rows
        self.table.setSelectionMode(QTableWidget.SingleSelection)  # Allow only single row selection
        self.table.setEditTriggers(QTableWidget.NoEditTriggers)  # Make table content non-editable
        self.table.itemDoubleClicked.connect(self.onRowDoubleClicked)

        self.table.setColumnCount(len(self.desired_columns))  
        self.table.setHorizontalHeaderLabels(self.desired_columns)
        self.loadAccountsData()

        # Buttons
        btn_layout = QHBoxLayout()
        ok_btn = QPushButton("OK")
        ok_btn.setFixedWidth(100)
        ok_btn.clicked.connect(self.accept)

        cancel_btn = QPushButton("Cancel")
        cancel_btn.setFixedWidth(100)
        cancel_btn.clicked.connect(self.reject)

        btn_layout.addWidget(ok_btn)
        btn_layout.addWidget(cancel_btn)

        layout.addWidget(self.table)
        layout.addLayout(btn_layout)

    def onRowDoubleClicked(self, item):
        # This method gets triggered on double-clicking a table row
        # Perform the same action as clicking the "OK" button
        self.accept()

loadAccountsData Method:

• Fetches and displays account data from the Google Sheets in the table.
• Maps column names to indices and checks if all desired columns are present.
• Inserts the fetched data into the table.

class SelectAccountDialog(QDialog):
    def __init__(self, google_sheets_client):
        #...
    def loadAccountsData(self):
        # Fetch data from Google Sheets and identify column indices
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet('02_Accounts')
        all_data = worksheet.get_all_values()
        header_row = all_data[0]  # The first row is assumed to be the header

        # Create a dictionary to map column names to their indices
        column_indices = {name: index for index, name in enumerate(header_row)}

        # Define the desired column names
        desired_columns = self.desired_columns

        # Check if all desired columns are present in the sheet
        for col in desired_columns:
            if col not in column_indices:
                print(f"Column '{col}' not found in the spreadsheet.")
                return
        
        # Populate the table with data
        for row_data in all_data[1:]:
            row = self.table.rowCount()
            self.table.insertRow(row)
            for col_name in desired_columns:
                col_index = column_indices[col_name]
                self.table.setItem(row, desired_columns.index(col_name), QTableWidgetItem(row_data[col_index]))

    def getSelectedAccountID(self):
        # Return the selected account's ID
        selected_row = self.table.currentRow()
        if selected_row != -1:
            return self.table.item(selected_row, 0).text()
        return None