How to Build an Online Database APP by Python, PySide6 and Google Sheets (3): Add Investor
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_boxhandle_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 stylefind_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_idis_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 Falsesubmit_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
