How to Build an Online Database APP by Python, PySide6 and Google Sheets (5): Add Account

Previously

How to Build an Online Database APP by Python, PySide6 and Google Sheets (4): Edit Investor

This article is about how to “add account” in the App:

Previously

How to Build an Online Database APP by Python, PySide6 and Google Sheets (4): Edit Investor

Call Function in the Main Window

1. display_add_account_form (in MainWindow Class):

• Clears current content.
• Opens a dialog (SelectInvestorDialog) to choose an investor.
• If an investor is selected, fetches their data and displays the form for adding a bank account using showAddAccountForm.

class MainWindow(QMainWindow):
    def display_add_account_form(self):
        # Clear existing content
        self.clear_content()

        # Open the dialog to select an investor
        dialog = SelectInvestorDialog(self.google_sheets_client)
        result = dialog.exec()

        if result == QDialog.Accepted:
            selected_id = dialog.getSelectedInvestorID()
            if selected_id:
                investor_data = self.fetch_investor_data(selected_id)
                if investor_data:
                    self.showAddAccountForm(investor_data)

    def fetch_investor_data(self, investor_id):
        # Connect to Google Sheets and select the appropriate worksheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("01_Investors")

        # 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_Investor", "Type", "Last Name", "First Name", "Company Name"]
        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 'ID_Investor' column
        id_investor_column = worksheet.col_values(column_index_map["ID_Investor"] + 1)  # +1 for 1-based index

        # Find the row index for the matching investor_id
        try:
            row_index = id_investor_column.index(investor_id) + 1  # +1 because spreadsheet indices start at 1
        except ValueError:
            # ID_Investor not found
            return None

        # Fetch only the row with the matching ID_Investor
        row_data = worksheet.row_values(row_index)

        # Use the column names to access the data
        return {
            "ID_Investor": row_data[column_index_map["ID_Investor"]],
            "Type": row_data[column_index_map["Type"]],
            "Last Name": row_data[column_index_map["Last Name"]],
            "First Name": row_data[column_index_map["First Name"]],
            "Company Name": row_data[column_index_map["Company Name"]],
            "Residency Status in Canada": row_data[column_index_map["Residency Status in Canada"]]
        }

2. showAddAccountForm (in MainWindow Class):

• Creates an instance of AddAccountForm with the fetched investor data.
• Adds the form widget to the layout.

    def showAddAccountForm(self, investor_data):
        # Create the AddAccountForm with the fetched investor data
        add_account_form = AddAccountForm(self.google_sheets_client, investor_data)
        self.content.layout().addWidget(add_account_form)

Create Add Account Class

• __init__: Initializes the form with fields for bank account details and account-specific data, using investor data passed as a parameter.

class AddAccountForm(QWidget):
    def __init__(self, google_sheets_client, investor_data):
        super().__init__()

        self.google_sheets_client = google_sheets_client
        self.investor_data = investor_data
        self.worksheet_name = "02_Accounts"

        # Define the fields and labels for the investor form
        self.investor_labels = list(self.investor_data.keys())
        self.investor_fields = list(self.investor_data.values())

        # Define the fields and labels for the account form
        self.account_labels = [
            "Bank Name (deposits):", 
            "Bank Name (withdrawals):", 
            "Institution No. (deposits):", 
            "Institution No. (withdrawals):", 
            "Branch No. (deposits):",
            "Branch No. (withdrawals):",
            "Account No. (deposits):", 
            "Account No. (withdrawals):"
        ]

        self.account_fields = [
            "Bank Name (deposits)", 
            "Bank Name (withdrawals)", 
            "Institution Number (deposits)", 
            "Institution Number (withdrawals)", 
            "Branch Number (deposits)",
            "Branch Number (withdrawals)",
            "Account Number (deposits)", 
            "Account Number (withdrawals)"
        ]

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

        self.account_id_labels = ["Account ID:", "Reinvest/Cash Dividend:"]
        self.account_id_fields = ["Account ID", "Reinvest/Cash Dividend"]

        self.account_id_fields = {
            "Account ID": QLineEdit(),
            "Reinvest/Cash Dividend": QComboBox()  # QComboBox for the dropdown
        }
        self.account_id_fields["Reinvest/Cash Dividend"].addItems(["Reinvest", "Cash Dividend"])

        self.account_id_fields["Account ID"].setText(self.generate_new_account_id())
        self.account_id_fields["Account ID"].setReadOnly(True)  # Make Account ID uneditable
        self.account_id_fields["Account ID"].setStyleSheet("background-color: lightgrey;")  # Set background color

        # Initialize UI
        self.initUI()

• initUI: Sets up the UI layout, including scrollable areas and group boxes for different sections of the form.

    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 ID", self.account_id_labels, self.account_id_fields, is_editable=True))
        layout.addSpacing(20) 
        layout.addWidget(self.create_group_box("Bank Account Details", self.account_labels, self.account_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("Add Account")
        self.submit_button.clicked.connect(self.submit_form)
        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: Creates a group box for different sections of the form, with appropriate labels and input fields.

    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:
                if field_name in self.account_id_fields:
                    widget = self.account_id_fields[field_name]
                else:
                    widget = self.input_fields.get(field_name, QLineEdit()) # Fetch from input_fields or create a new QLineEdit
            else:
                widget = QLineEdit(str(field_name))
                widget.setReadOnly(True)
                widget.setStyleSheet("background-color: lightgrey;")
            
            if isinstance(widget, QLineEdit):
                widget.setAlignment(Qt.AlignCenter)

            layout.addWidget(widget, row, col * 2 + 1)

        # Adjust column stretch to make each column equally wide
        layout.setColumnStretch(0, 1)
        layout.setColumnStretch(1, 1)
        layout.setColumnStretch(2, 1)
        layout.setColumnStretch(3, 1)

        group_box.setLayout(layout)
        return group_box

• generate_new_account_id: Generates a new account ID based on existing account data in the Google Sheets.

    def generate_new_account_id(self):
        id_investor = self.investor_data.get("ID_Investor", "")
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("02_Accounts")
        all_data = worksheet.get_all_values()
        id_investor_column = all_data[0].index("ID_Investor")
        id_account_column = all_data[0].index("ID_Account")

        max_number = 0
        for row in all_data[1:]:
            if row[id_investor_column] == id_investor:
                account_number = row[id_account_column].split('_')[-1]
                if account_number.isdigit():
                    max_number = max(max_number, int(account_number))

        new_account_number = max_number + 1
        return f"A_{datetime.now().year}_{id_investor.split('_')[-1]}_{new_account_number:02d}"

• update_account_id_field: Updates the account ID field with a newly generated ID.

    def update_account_id_field(self):
        # Generate and set the new account ID
        new_account_id = self.generate_new_account_id()
        self.account_id_fields["Account ID"].setText(new_account_id)

• submit_form: Collects data from the form fields, updates the Google Sheets database, and handles success or error messages.

    def submit_form(self):
        try:
            # Collect data from input fields, including account_id_fields
            account_data = {
                key: widget.currentText() if isinstance(widget, QComboBox) else widget.text()
                for key, widget in self.input_fields.items()
            }

            account_id_data = {
                key: widget.currentText() if isinstance(widget, QComboBox) else widget.text()
                for key, widget in self.account_id_fields.items()
            }

            update_id =  {'ID_Account': account_id_data['Account ID']}
            account_id_data.update(update_id)

            # Add investor data and account_id_data to account_data
            account_data.update(self.investor_data)
            account_data.update(account_id_data)

            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 = [account_data.get(header, "") for header in column_headers]

            worksheet.append_row(row_data)
            QMessageBox.information(self, "Success", "Account data submitted successfully.")
        except Exception as e:
            QMessageBox.critical(self, "Error", f"Failed to submit account data: {e}")

        self.clear_input_fields()
        self.update_account_id_field()  # Update the ID_Account field after clearing the form

• clear_input_fields: Clears all input fields in the form after submission.

    def clear_input_fields(self):
        # Clear all input fields
        for widget in self.input_fields.values():
            if isinstance(widget, QLineEdit):
                widget.clear()
            elif isinstance(widget, QComboBox):
                widget.setCurrentIndex(0)  # Reset combobox to default value

Next Step

How to Build an Online Database APP by Python, PySide6 and Google Sheets (6): Edit Account