Previously

This article demonstrates how to realize the function of “Add Account.”

Previously

Call the Function in MainWindow Class

According to the last article, we already had code in the MainWindow class:

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

    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()
        # ...

handle_secondary_action: Handles actions based on the selected items from the UI. For example, if “Investors” and “Add Investor” are selected, it calls display_add_investor_form.

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

        # Set the main content label's text for Add Investor
        self.update_main_content("")

        # Display the AddInvestorForm
        add_investor_form = AddInvestorForm(google_sheets_client)
        self.content.layout().addWidget(add_investor_form)

Create Add Investor Class

• __init__: Constructor setting up the form to add a new investor. It includes initializing various input fields and comboboxes with predefined options.

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

        self.google_sheets_client = google_sheets_client
        self.worksheet_name = "01_Investors"

        # Grouped Fields
        self.group1_fields = ["ID_Investor", "Type", "Last Name", "First Name", "Company Name", "Business Number",
                              "Residency Status in Canada", "Date of Birth",  "SIN", "Passport", "Address", "Email", "Phone",
                              "DR", "Accredited Investor", "Excemption Used", "Related Person", "Relationship"]
        self.group2_fields = ["Subscription Agreement Received", "CCO Sign Off", "Trade Confirmation Sent"]
        self.group3_fields = ["Bank Name", "Institution Number", "Branch Number", "Account Number", "Initial Investment",
                              "Money Received On", "Share Issue Date", "Retraction Date", "Share Class",
                              "Share Issued Amount", "Share Certificate Number"]

        self.combobox_fields = ["Type", "Residency Status in Canada", "Accredited Investor", 
                                "Excemption Used", "Subscription Agreement Received", 
                                "Reinvest/Cash Dividen", "Trade Confirmation Sent", 
                                "CCO Sign Off", "DR"]

        # Define the options for each combobox field
        self.combobox_options = {
            "Type": ["Individual", "Entity"],
            "Residency Status in Canada": ["PR", "Citizen", "China(HK)", "China(Mainland)", "US", "Other"],
            "Accredited Investor": ["Yes", "No"],
            "Excemption Used": ["Family Members", "Close Personal Friend", "Close Business Associates"],
            "Subscription Agreement Received": ["Yes", "No"],
            #"Reinvest/Cash Dividend": ["Reinvest", "Cash Dividend"],
            "Trade Confirmation Sent": ["Yes", "No"],
            "CCO Sign Off": ["Yes", "No"],
            "DR": ["DR1", "DR2", "DR3", "DR4"]
        }

        # Labels (assuming they are the same as field names for simplicity)
        self.group1_labels = self.group1_fields
        self.group2_labels = self.group2_fields
        self.group3_labels = self.group3_fields

        # Initialize input_fields as a dictionary of QLineEdit and QComboBox
        self.input_fields = {}
        for field in (self.group1_fields + self.group2_fields + self.group3_fields):
            if field in self.combobox_fields:
                combobox = QComboBox()
                combobox.addItem("")  # Add empty default choice
                combobox.addItems(self.combobox_options[field])
                if field == "Type":
                    combobox.currentIndexChanged.connect(self.handle_type_change)
                self.input_fields[field] = combobox
            else:
                line_edit = QLineEdit()
                if field == "ID_Investor":
                    line_edit.setText(self.generate_new_investor_id())  # Set new ID  
                    line_edit.setReadOnly(True)
                    line_edit.setStyleSheet("background-color: lightgrey;") 
                if field == "Date of Birth":
                    line_edit.setPlaceholderText("YYYY-MM-DD")  # Set the placeholder"            
                self.input_fields[field] = line_edit

        # Now call initUI and pass the submit button text
        self.initUI()

• initUI: Sets up the UI layout for the form, including scrollable areas and button layouts.

    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 the groups with increased spacing between them
        layout.addWidget(self.create_group_box("Basic and Personal Information", self.group1_fields, self.group1_labels))
        layout.addSpacing(20)  # Double the space between groups
        layout.addWidget(self.create_group_box("Investment Preferences and Status", self.group2_fields, self.group2_labels))
        layout.addSpacing(20)  # Double the space between groups
        #layout.addWidget(self.create_group_box("Banking and Investment Details", self.group3_fields, self.group3_labels))

        # Set the layout for the main widget
        main_layout = QVBoxLayout(self)
        main_layout.addWidget(scroll)

        # "Add Investor" Button outside the group boxes, aligned at the bottom middle
        self.submit_button = QPushButton("Add Investor")
        self.submit_button.clicked.connect(self.submit_form)
        button_layout = QHBoxLayout()
        button_layout.addStretch(1)  # Add stretch to center the button
        button_layout.addWidget(self.submit_button)
        button_layout.addStretch(1)  # Add stretch to center the button
        
        main_layout.addLayout(button_layout)

        self.setLayout(main_layout)

• create_group_box: Creates and returns a QGroupBox widget with a grid layout containing labels and input fields.

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

        group_box.setAlignment(Qt.AlignCenter)  # Center the title
        # Set bold font for the group box title
        title_font = QFont()
        title_font.setPointSize(QLabel().font().pointSize())  # Set the font size to match label size
        #title_font.setBold(True)
        group_box.setFont(title_font)
        
        layout = QGridLayout()

        for i, field_name in enumerate(fields):
            row, col = divmod(i, 2)  # Calculate row and column index
            
            label = QLabel(labels[i])
            label.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            label.setWordWrap(True)
            layout.addWidget(label, row, col * 2)

            input_widget = self.input_fields[field_name]  # Reuse the created input widget
            
            if isinstance(input_widget, QLineEdit):
                input_widget.setAlignment(Qt.AlignCenter)

            layout.addWidget(input_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

• handle_type_change: Handles changes in the “Type” combobox, enabling or disabling fields based on the selected type.

def handle_type_change(self):
        # This method is triggered when "Type" combobox value is changed
        type_combobox = self.find_input_field("Type")
        is_individual = type_combobox.currentText() == "Individual"

        company_name_field = self.find_input_field("Company Name")
        business_number_field = self.find_input_field("Business Number")

        # Define the styles for disabled and enabled states
        disabled_style = "QLineEdit { background-color: lightgray; }"  # Example color for disabled state
        enabled_style = "QLineEdit { background-color: white; }"  # Example color for enabled state

        if is_individual:
            if company_name_field and isinstance(company_name_field, QLineEdit):
                company_name_field.clear()  # Clear the text
                company_name_field.setEnabled(False)  # Disable the field
                company_name_field.setStyleSheet(disabled_style)  # Apply disabled style

            if business_number_field and isinstance(business_number_field, QLineEdit):
                business_number_field.clear()  # Clear the text
                business_number_field.setEnabled(False)  # Disable the field
                business_number_field.setStyleSheet(disabled_style)  # Apply disabled style
        else:
            if company_name_field and isinstance(company_name_field, QLineEdit):
                company_name_field.setEnabled(True)  # Enable the field
                company_name_field.setStyleSheet(enabled_style)  # Apply enabled style

            if business_number_field and isinstance(business_number_field, QLineEdit):
                business_number_field.setEnabled(True)  # Enable the field
                business_number_field.setStyleSheet(enabled_style)  # Apply enabled style

• find_input_field: Returns the widget associated with a given field name.

    def find_input_field(self, field_name):
        # Return the widget associated with the given field name
        return self.input_fields.get(field_name, None)

• generate_new_investor_id: Generates a new unique investor ID based on existing IDs in the Google Sheets database.

    def generate_new_investor_id(self):
        # Fetch existing IDs from the sheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("01_Investors")
        all_data = worksheet.get_all_values()
        id_column_index = all_data[0].index("ID_Investor")
        existing_ids = [row[id_column_index] for row in all_data[1:] if row[id_column_index].startswith("I_")]

        # Extract year and number
        current_year = datetime.now().year
        prefix = f"I_{current_year}_"
        numbers = [int(id.split('_')[-1]) for id in existing_ids if id.startswith(prefix)]

        # Generate new ID
        new_number = max(numbers, default=0) + 1
        new_id = prefix + f"{new_number:04d}"  # Zero pad to 4 digits
        return new_id

• is_valid_date: Validates the format of a date string.

    def is_valid_date(self, date_str):
        try:
            # Parse the date string
            date_obj = datetime.strptime(date_str, "%Y-%m-%d")
            
            # Check the year range
            if 1900 <= date_obj.year <= 2020 and 1 <= date_obj.month <= 12 and 1 <= date_obj.day <= 31:
                return True
        except ValueError:
            pass

        return False

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

    def submit_form(self):

        # Get the date of birth input
        date_of_birth = self.input_fields["Date of Birth"].text()

        # Validate the date of birth
        if not self.is_valid_date(date_of_birth):
            QMessageBox.warning(self, "Invalid Date", "Please enter a valid date of birth, and in the format YYYY-MM-DD")
            return  # Stop further processing


        # Collect data from input fields
        form_data = {}
        for field_name, widget in self.input_fields.items():
            if isinstance(widget, QLineEdit):
                form_data[field_name] = widget.text()
            elif isinstance(widget, QComboBox):
                form_data[field_name] = widget.currentText()
            else:
                form_data[field_name] = None

        try:
            # Connect to the worksheet
            worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet(self.worksheet_name)

            # Find the next available row (first empty row)
            next_row = len(worksheet.get_all_values()) + 1

            # Construct the range string for the next row, starting at column 'A'
            range_string = f"A{next_row}"

            # Convert the form data dictionary to a list of values
            # Ensure that the order of fields matches the column order in the sheet
            header_row = worksheet.row_values(1)
            form_data_values = [form_data.get(field, '') for field in header_row]

            # Update the cells starting from the first column of the next row
            #worksheet.update(range_string, [form_data_values], value_input_option='USER_ENTERED')
            #worksheet.update(range_name=range_string, values=[form_data_values], value_input_option='USER_ENTERED')
            worksheet.update(values=[form_data_values], range_name=range_string, value_input_option='USER_ENTERED')

            QMessageBox.information(self, "Success", "Investor data submitted successfully.")
        except Exception as e:
            QMessageBox.critical(self, "Error", f"Failed to submit investor data: {e}")

        # Clear the input fields after submission
        self.clear_input_fields()

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

    def clear_input_fields(self):
        # Iterate over the widgets in self.input_fields and clear them
        for widget in self.input_fields.values():
            if isinstance(widget, QLineEdit):
                widget.clear()
            elif isinstance(widget, QComboBox):
                widget.setCurrentIndex(0)  # Reset combobox to the default (empty) selection

Next Step