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

Previously

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

This article is about how to “Add Transaction” in the app:

Call function in the Main Window

1. display_transaction_form (in MainWindow Class):

• Clears existing content.
• Opens SelectAccountDialog for the user to select an account.
• Fetches account data for the selected account and displays a transaction form using showTransactionForm.

class MainWindow(QMainWindow):
    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)

2. fetch_account_data:

• Connects to the Google Sheets worksheet named “02_Accounts”.
• Retrieves and returns account data based on the selected account ID.

    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", 'Reinvest/Cash Dividend']
        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

3. showTransactionForm:

• Creates an instance of TransactionForm with the fetched account data.
• Adds the transaction form widget to the main content layout.

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

Create Add Transaction Class

• __init__: Initializes the form, extracts and segregates investor and account data, and sets up input fields for transaction details.

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 = ["Transaction Date:", "Dividend Period:", "Money In (+):", "Money Out (-):"]
        self.transaction_fields = ["Transaction Date", "Dividend Period", "Money In", "Money Out"]

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

        # self.input_fields["Transaction Date"].setPlaceholderText("YYYY-MM-DD")  # Set the placeholder

        # Set the transaction date to the current date, and make it editable
        current_date = datetime.now().strftime("%Y-%m-%d")
        self.input_fields["Transaction Date"].setText(current_date)
        self.input_fields["Transaction Date"].textChanged.connect(self.updateDividendPeriod)

        # Initialize and set the Dividend Period field as uneditable
        self.updateDividendPeriod(current_date)
        self.input_fields["Dividend Period"].setReadOnly(True)
        self.input_fields["Dividend Period"].setStyleSheet("background-color: lightgrey;")

        self.input_fields["Money In"].setPlaceholderText("$")
        self.input_fields["Money Out"].setPlaceholderText("$")    

        # Initialize UI
        self.initUI()

• initUI: Sets up the UI layout with scrollable areas and group boxes for investor information, account information, and transaction details.

    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: Creates a QGroupBox for different sections of the form with 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:
                widget = self.input_fields.get(field_name, QLineEdit())
            else:
                widget = QLineEdit(str(field_name))
                widget.setReadOnly(True)
                widget.setStyleSheet("background-color: lightgrey;")
            
            # Align text in the center for all fields
            widget.setAlignment(Qt.AlignCenter)

            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

• updateDividendPeriod: Calculates and updates the dividend period based on the transaction date.

    def updateDividendPeriod(self, date_str):
        try:
            date = datetime.strptime(date_str, "%Y-%m-%d")
            quarter = (date.month - 1) // 3 + 1
            dividend_period = f"{date.year}Q{quarter}"
            self.input_fields["Dividend Period"].setText(dividend_period)
        except ValueError:
            # Handle invalid date format
            self.input_fields["Dividend Period"].clear()

• is_valid_date and is_valid_money_input: Validate the transaction date and money inputs.

    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 2023 <= date_obj.year and 1 <= date_obj.month <= 12 and 1 <= date_obj.day <= 31:
                return True
        except ValueError:
            pass

        return False

    def is_valid_money_input(self, money_in, money_out):
        # Check if both are empty or both are filled
        if bool(money_in) == bool(money_out):
            return False, "Please enter a value in either 'Money In' or 'Money Out', but not both."

        def is_valid_number(value):
            try:
                # Convert to float and check for positive value
                number = float(value)
                if number <= 0:
                    return False

                # Check for no more than two decimal places
                if '.' in value:
                    decimal_part = value.split('.')[-1]
                    if len(decimal_part) > 2:
                        return False

                return True
            except ValueError:
                return False

        # Validate 'Money In' input
        if money_in and not is_valid_number(money_in):
            return False, "'Money In' should be a positive number with up to two decimal places."

        # Validate 'Money Out' input
        if money_out and not is_valid_number(money_out):
            return False, "'Money Out' should be a positive number with up to two decimal places."

        return True, ""

• submit_transaction: Collects data from the form, validates it, shows a preview dialog, and processes the transaction if confirmed.

    def submit_transaction(self):

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

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

        money_in = self.input_fields["Money In"].text()
        money_out = self.input_fields["Money Out"].text()

        is_valid, message = self.is_valid_money_input(money_in, money_out)

        if not is_valid:
            QMessageBox.warning(self, "Invalid Input", message)
            return  # Stop further processing


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

        transaction_data = self.convert_transaction_data(transaction_input_data)

        transaction_data.update(self.display_investor_data)
        transaction_data.update(self.display_account_data)
        
        # Show the preview dialog
        if self.show_preview_dialog(transaction_data):
            # User clicked "OK" in the preview dialog
            self.process_transaction(transaction_data)
        else:
            # User clicked "Cancel" in the preview dialog
            # Optional: Any action to be taken on cancelation
            pass

• process_transaction: Submits the transaction data to Google Sheets and handles success or error messages.

    def process_transaction(self, transaction_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()

• clear_input_fields: Clears all input fields after transaction submission.

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

• show_preview_dialog: Shows a preview dialog with the transaction details for user confirmation.

    def show_preview_dialog(self, transaction_data):
        dialog = QDialog(self)
        dialog.setWindowTitle("Preview")

        layout = QVBoxLayout(dialog)

        keys_to_keep = ["Transaction Amount", "Transaction Date", "Money In/Out", "Last Name", "First Name", "Company Name", "Reinvest/Cash Dividend"]

        # Create a new dictionary with only the selected keys
        transaction_data_preview = {key: transaction_data[key] for key in keys_to_keep}

        # Display transaction data
        for key, value in transaction_data_preview.items():
            label = QLabel(f"{key}: {value}")
            label.setAlignment(Qt.AlignCenter)  # Align the label text to the center
            layout.addWidget(label)

        # Buttons
        button_layout = QHBoxLayout()
        ok_button = QPushButton("OK")
        cancel_button = QPushButton("Cancel")
        button_layout.addWidget(ok_button)
        button_layout.addWidget(cancel_button)
        layout.addLayout(button_layout)

        ok_button.clicked.connect(dialog.accept)
        cancel_button.clicked.connect(dialog.reject)

        return dialog.exec() == QDialog.Accepted  

• convert_transaction_data: Converts the collected transaction data into the format required for submission.

    def convert_transaction_data(self, original_data):
        result_data = {
            "Money In/Out": None,
            "Amount": None,
            "Transaction Amount": None,
            "Transaction Date": None
        }

        money_in = original_data.get("Money In")
        money_out = original_data.get("Money Out")
        transaction_date = original_data.get("Transaction Date")

        # Determine "Money In/Out" and "Amount" based on the rules
        if money_in:
            result_data["Money In/Out"] = "Money In"
            result_data["Amount"] = money_in
            result_data["Transaction Amount"] = float(money_in)
        elif money_out:
            result_data["Money In/Out"] = "Money Out"
            result_data["Amount"] = money_out
            result_data["Transaction Amount"] = -float(money_out)

        # Copy "Transaction Date"
        result_data["Transaction Date"] = transaction_date

        return result_data

Next Step

How to Build an Online Database APP by Python, PySide6 and Google Sheets (8): Delete Transaction