Untitled

import time
import pandas as pd
import smtplib
import tkinter as tk
import os
import threading
import re
import subprocess
import queue
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from docx import Document
from tkinter import filedialog
from tkinter import messagebox
from dotenv import load_dotenv
from email.mime.base import MIMEBase
from email import encoders

def convert_docx_to_html(docx_file_path):
    html_file_path = docx_file_path.replace(".docx", ".html")
    try:
        # Run the `mammoth` command to convert .docx to .html
        subprocess.run(["mammoth", docx_file_path, html_file_path], check=True)
        with open(html_file_path, 'r', encoding='utf-8') as file:
            html_content = file.read()
        return html_content
    except subprocess.CalledProcessError as e:
        raise Exception(f"Error converting DOCX to HTML: {e}")

def extract_html_source_code(html_file_path):
    with open(html_file_path, 'r', encoding='utf-8') as file:
        html_content = file.read()
    return html_content

def email_to_env_var(email):
    return email.replace("@", "_").replace(".", "_")

def get_sender_credentials(sender_email):
    sender_env_var = email_to_env_var(sender_email)
    sender_email = os.getenv(f"{sender_env_var}_EMAIL")
    sender_password = os.getenv(f"{sender_env_var}_PASSWORD")
    return sender_email, sender_password

def dataframe_to_styled_html(df, merge_first_row=False):
    table_style = (
        "border-collapse: collapse; width: 65%; font-family: Arial, sans-serif; "
        "font-size: 14px; text-align: center;"
    )
    header_style = (
        "background-color: #6A0DAD; color: white; padding: 10px; border: 1px solid black;"
    )
    cell_style = "padding: 10px; border: 1px solid black;"
    row_alt_style = "background-color: #f2f2f2;"

    html_table = f'<table style="{table_style}">'

    if merge_first_row:
        html_table += f'<tr><td colspan="{len(df.columns)}" style="{header_style}">{df.columns[0]}</td></tr>'
    else:
        html_table += "<tr>"
        for col in df.columns:
            html_table += f'<th style="{header_style}">{col}</th>'
        html_table += "</tr>"

    for i, row in df.iterrows():
        row_style = row_alt_style if i % 2 == 1 else ""
        html_table += f'<tr style="{row_style}">'
        for cell in row:
            if pd.isna(cell):
                cell_content = ""
            else:
                if isinstance(cell, float):
                    cell_content = f"{cell:.2%}" if 0 <= cell <= 1 else cell
                else:
                    cell_content = str(cell)  # Ensure cell content is always a string
            html_table += f'<td style="{cell_style}">{cell_content}</td>'
        html_table += "</tr>"

    html_table += "</table>"
    return html_table

def send_bulk_emails_with_text_and_tables(
    smtp_server,
    smtp_port,
    email_list_file,
    table1_file,
    table2_file,
    table3_file,
    table4_file,
    table5_file,
    log_queue,
    log_file
):
    log_queue.put("The email-sending process has started. Please wait...")
    log_serial_number = 1

    with open(log_file, 'a') as log_file_obj:
        try:
            email_data = pd.read_excel(email_list_file)
            if "Email" not in email_data.columns or "Salutations" not in email_data.columns or "CC" not in email_data.columns or "From" not in email_data.columns or "File Name" not in email_data.columns or "Sender Name" not in email_data.columns or "Signature File" not in email_data.columns:
                log_queue.put("The email list file must contain columns: 'Email', 'Salutations', 'CC', 'From', 'File Name', 'Sender Name', and 'Signature File'.")
                return

            email_details = email_data[["Email", "Salutations", "CC", "From", "File Name", "Sender Name", "Attachments", "Signature File"]]

            tables = [
                (table1_file, "{{ TABLE_1 }}"),
                (table2_file, "{{ TABLE_2 }}"),
                (table3_file, "{{ TABLE_3 }}"),
                (table4_file, "{{ TABLE_4 }}"),
                (table5_file, "{{ TABLE_5 }}")
            ]
            
            # Read all tables as dataframes if they are valid files
            tables_html = {}
            for table_file, placeholder in tables:
                if table_file and os.path.isfile(table_file):
                    try:
                        table_df = pd.read_excel(table_file)
                        tables_html[placeholder] = dataframe_to_styled_html(table_df)
                    except Exception as e:
                        log_entry = f"Error reading table from {table_file}: {e}"
                        log_queue.put(log_entry)
                        log_file_obj.write(log_entry + "\n")
            
            for _, row in email_details.iterrows():
                try:
                    recipients = row["Email"].split(",")
                    salutation = row["Salutations"]
                    cc_list = row["CC"].split(",") if pd.notna(row["CC"]) else []
                    from_email = row["From"]
                    text_file = row["File Name"]
                    sender_name = row["Sender Name"]

                    signature_file = row["Signature File"]
                    signature_html = ""
                    if pd.notna(signature_file) and os.path.isfile(signature_file):
                        signature_html = convert_docx_to_html(signature_file)

                    attachment_file = row.get("Attachments", None)

                    if not os.path.isfile(text_file):
                        log_entry = f"{log_serial_number}. The file {text_file} does not exist for {from_email}."
                        log_queue.put(log_entry)
                        log_file_obj.write(log_entry + "\n")
                        continue

                    # html_file_path = convert_docx_to_html(text_file)
                    path = "Reports/mfReportsBody.html"
                    email_text = extract_html_source_code(path)

                    # Replace placeholders for tables dynamically
                    for placeholder, table_html in tables_html.items():
                        if placeholder in email_text:
                            email_text = email_text.replace(placeholder, table_html)
                        else:
                            log_queue.put(f"Placeholder {placeholder} not found in the text.")

                    # Replace the {{ Sender_Name }} and {{ Signature }} placeholders
                    email_text = email_text.replace("{{ Sender_Name }}", sender_name)
                    email_text = email_text.replace("{{ Signature }}", signature_html)

                    email_body_html = f"""
                    <html>
                        <body>
                            {email_text}
                        </body>
                    </html>
                    """

                    # Fetch sender email and password based on the 'From' column
                    sender_email, sender_password = get_sender_credentials(from_email)
                    if not sender_email or not sender_password:
                        log_entry = f"{log_serial_number}. Missing credentials for sender email {from_email}."
                        log_queue.put(log_entry)
                        log_file_obj.write(log_entry + "\n")
                        continue

                    with smtplib.SMTP(smtp_server, smtp_port) as server:
                        server.starttls()
                        server.login(sender_email, sender_password)

                        personalized_text = email_body_html.replace("{{ Salutations }}", salutation)

                        msg = MIMEMultipart()
                        msg["From"] = sender_email
                        msg["To"] = ", ".join(recipients)
                        msg["Cc"] = ", ".join(cc_list)
                        msg["Subject"] = "Mutual Fund Investment Snapshot - Dated as of Dec 31, 2024"

                        msg.attach(MIMEText(personalized_text, "html"))

                        # Handle attachments (if provided)
                        if attachment_file and os.path.isfile(attachment_file):
                            with open(attachment_file, "rb") as attachment:
                                part = MIMEBase("application", "octet-stream")
                                part.set_payload(attachment.read())
                                encoders.encode_base64(part)
                                part.add_header(
                                    "Content-Disposition",
                                    f"attachment; filename={os.path.basename(attachment_file)}"
                                )
                                msg.attach(part)
                                log_entry = f"Attachment {os.path.basename(attachment_file)} added."
                                log_queue.put(log_entry)
                                log_file_obj.write(log_entry + "\n")

                        all_recipients = recipients + cc_list
                        server.sendmail(sender_email, all_recipients, msg.as_string())

                        log_entry = f"{log_serial_number}. Email sent successfully from {sender_email} to {', '.join(recipients)}, CC: {', '.join(cc_list)}"
                        log_queue.put(log_entry)
                        log_file_obj.write(log_entry + "\n")

                except Exception as e:
                    log_entry = f"{log_serial_number}. Failed to send email from {from_email} to {', '.join(recipients)}: {e}"
                    log_queue.put(log_entry)
                    log_file_obj.write(log_entry + "\n")

                log_serial_number += 1

        except Exception as e:
            log_entry = f"Failed to connect to the SMTP server: {e}"
            log_queue.put(log_entry)
            log_file_obj.write(log_entry + "\n")

    log_queue.put("The email-sending process has completed successfully.")



# to show logs in the ui box
def update_log_ui(log_queue, log_text_widget):
    try:
        while not log_queue.empty():
            log_entry = log_queue.get_nowait()
            log_text_widget.insert(tk.END, log_entry + "\n")
            log_text_widget.yview(tk.END)
    except queue.Empty:
        pass
    finally:
        # Schedule the next update
        root.after(100, update_log_ui, log_queue, log_text_widget)

# threading is used to send emails slowly so it doesnt go into spam
def send_email_threaded():
    smtp_server = "smtp.gmail.com"
    smtp_port = 587
    email_list_file = email_list_entry.get()
    table_files = [
        table1_entry.get(),
        table2_entry.get(),
        table3_entry.get(),
        table4_entry.get(),
        table5_entry.get()
    ]
    log_file = "email_log.txt"

    if not email_list_file:
        messagebox.showerror("Missing Information", "Please provide the email list file.")
    else:
        log_queue = queue.Queue()
        email_thread = threading.Thread(
            target=send_bulk_emails_with_text_and_tables,
            args=(
                smtp_server,
                smtp_port,
                email_list_file,
                *table_files,  # Pass the table files dynamically
                log_queue,
                log_file,
            ),
        )
        email_thread.start()
        update_log_ui(log_queue, log_text_widget)


#upload file from ui
def browse_file(entry):
    file_path = filedialog.askopenfilename(
        title="Select a file", 
        filetypes=[("Excel files", ".xlsx")]
    )
    if file_path:
        entry.delete(0, tk.END)
        entry.insert(0, file_path)

load_dotenv()

# UI Setup
# UI Setup
root = tk.Tk()
root.title("Pygmalion Wealth")

tk.Label(root, text="Sender Email:").grid(row=0, column=0, padx=10, pady=5)
sender_email_entry = tk.Entry(root, width=40)
sender_email_entry.grid(row=0, column=1, padx=10, pady=5)

tk.Label(root, text="Email List File:").grid(row=2, column=0, padx=10, pady=5)
email_list_entry = tk.Entry(root, width=40)
email_list_entry.grid(row=2, column=1, padx=10, pady=5)
tk.Button(root, text="Browse", command=lambda: browse_file(email_list_entry)).grid(row=2, column=2, padx=10, pady=5)

tk.Label(root, text="Table 1 File (Optional):").grid(row=3, column=0, padx=10, pady=5)
table1_entry = tk.Entry(root, width=40)
table1_entry.grid(row=3, column=1, padx=10, pady=5)
tk.Button(root, text="Browse", command=lambda: browse_file(table1_entry)).grid(row=3, column=2, padx=10, pady=5)

tk.Label(root, text="Table 2 File (Optional):").grid(row=4, column=0, padx=10, pady=5)
table2_entry = tk.Entry(root, width=40)
table2_entry.grid(row=4, column=1, padx=10, pady=5)
tk.Button(root, text="Browse", command=lambda: browse_file(table2_entry)).grid(row=4, column=2, padx=10, pady=5)

tk.Label(root, text="Table 3 File (Optional):").grid(row=5, column=0, padx=10, pady=5)
table3_entry = tk.Entry(root, width=40)
table3_entry.grid(row=5, column=1, padx=10, pady=5)
tk.Button(root, text="Browse", command=lambda: browse_file(table3_entry)).grid(row=5, column=2, padx=10, pady=5)

tk.Label(root, text="Table 4 File (Optional):").grid(row=6, column=0, padx=10, pady=5)
table4_entry = tk.Entry(root, width=40)
table4_entry.grid(row=6, column=1, padx=10, pady=5)
tk.Button(root, text="Browse", command=lambda: browse_file(table4_entry)).grid(row=6, column=2, padx=10, pady=5)

tk.Label(root, text="Table 5 File (Optional):").grid(row=7, column=0, padx=10, pady=5)
table5_entry = tk.Entry(root, width=40)
table5_entry.grid(row=7, column=1, padx=10, pady=5)
tk.Button(root, text="Browse", command=lambda: browse_file(table5_entry)).grid(row=7, column=2, padx=10, pady=5)

log_text_widget = tk.Text(root, height=15, width=80)
log_text_widget.grid(row=8, column=0, columnspan=3, padx=10, pady=10)

tk.Button(root, text="Send Emails", command=send_email_threaded).grid(row=9, column=1, padx=10, pady=5)

root.mainloop()