Untitled

import tkinter as tk
from tkinter import ttk, messagebox, filedialog, PhotoImage
from datetime import datetime
import sqlite3 

def initialize_database():
    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS recipients (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        phone_number TEXT NOT NULL UNIQUE,
        category TEXT,
        strand TEXT,
        section TEXT                      
    );
    """)
    
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS message (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        content TEXT NOT NULL,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS sent_messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        recipient TEXT NOT NULL,
        content TEXT NOT NULL,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """)

    conn.commit()
    conn.close()

initialize_database()

def send_sms():
    selected_indices = numbers_list.curselection()
    selected_recipients = [numbers_list.get(i) for i in selected_indices]

    message = message_text.get("1.0", tk.END).strip()

    if not recipients:
        messagebox.showerror("Error", "Recipient list is empty.")
        return
    if not message:
        messagebox.showerror("Error", "Message cannot be empty.")
        return
    
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    footer = f"\n\nThis text was sent on {current_time}"
    message += footer
    
    for recipient in selected_recipients:
        print(f"Sending SMS to {recipient}")
        save_sent_message_to_db(recipient, message)
    
    messagebox.showinfo("Success", "Messages sent successfully!")

def select_all_recipients():
    numbers_list.select_set(0, tk.END)
    update_send_sms_button_state()

def save_sent_message_to_db(recipient, content):
    conn =  sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("INSERT INTO sent_messages (recipient, content) VALUES (?, ?)", (recipient, content))
    conn.commit()
    conn.close()

def show_sent_items():
    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("SELECT recipient, content, timestamp FROM sent_messages")
    sent_messages = cursor.fetchall()
    conn.close()

    if not sent_messages:
        messagebox.showinfo("Sent Items", "No messages have been sent yet.")
        return

    sent_items_window = tk.Toplevel(root)
    sent_items_window.title("Sent Items")
    sent_items_window.geometry("400x300")

    sent_items_text = tk.Text(sent_items_window, wrap=tk.WORD)
    sent_items_text.pack(expand=True, fill=tk.BOTH)

    for recipient, content, timestamp in sent_messages:
        sent_items_text.insert(tk.END, f"To: {recipient}\nMessage: {content}\nSent on: {timestamp}\n\n")


def load_contacts():
    file_path = filedialog.askopenfilename(filetypes=[["Text Files", "*.txt"]])
    if file_path:
        with open(file_path, "r") as file:
            for line in file:
                numbers_list.insert(tk.END, line.strip())


def refresh_recipient_list():
    try:
        conn = sqlite3.connect("sms_system.db")
        cursor = conn.cursor()

        cursor.execute("SELECT phone_number, category, strand, section FROM recipients")
        numbers_list.delete(0, tk.END)
        for row in cursor.fetchall():
            display_text = f"{row[0]} - Category: {row[1]} - Strand: {row[2]}, - Section: {row[3]}"
            numbers_list.insert(tk.END, display_text)


        conn.close()
    except sqlite3.Error as e:
        messagebox.showerror("Database error", f"Error: {e}")    

def add_recipients_to_db(phone_number, category, strand, section):
        conn = sqlite3.connect("sms_system.db")
        cursor = conn.cursor()

        cursor.execute("""
            INSERT INTO recipients (phone_number, category, strand, section)
            VALUES (?, ?, ?, ?)
            """,        
            (phone_number, category, strand, section)
        )
        conn.commit()
        refresh_recipient_list()
        update_total_recipients_count()
        messagebox.showinfo("Success", "Recipient added successfully!")

        recipient_entry.delete(0, tk.END)
        strand_entry.delete(0, tk.END)
        section_entry.delete(0, tk.END)
        category_var.set('')

        conn.close()

def save_message_to_db(content):
    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("INSERT INTO message (content) VALUES (?)", (content,))
    conn.commit()
    conn.close()

def load_recipients():
    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("SELECT phone_number, category, strand, section from recipients")
    rows = cursor.fetchall()
    conn.close()
    return [row[0] for row in rows]

def load_templates():
    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("SELECT content FROM messages")
    rows = cursor.fetchall()
    conn.close()
    return[row[0] for row in rows]

def reset_recipients():

    if not messagebox.askyesno("Confirmation", "Are you sure you want to delete all recipients?"):
        return
    
    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("DELETE FROM recipients")
    cursor.execute("DELETE FROM sent_messages")
    conn.commit()
    conn.close()

    numbers_list.delete(0, tk.END)
    messagebox.showinfo("Reset Successful", "All recipients have been deleted.")

def update_total_recipients_count():
    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM recipients")
    total_count = cursor.fetchone()[0]
    conn.close()

    total_label.config(text=f"Total Recipients: {total_count}")

def update_send_sms_button_state(event=None):
    if numbers_list.curselection():
        send_button.config(state=tk.NORMAL)
    else:
        send_button.config(state=tk.DISABLED)

def filter_recipients():
    category_filter = category_filter_var.get()
    strand_filter = strand_filter_var.get().lower()
    section_filter = section_filter_var.get().lower()

    numbers_list.delete(0, tk.END)

    conn = sqlite3.connect("sms_system.db")
    cursor = conn.cursor()
    cursor.execute("SELECT phone_number, category, strand, section FROM recipients")
    recipients = cursor.fetchall()
    conn.close()

    for row in recipients:
        phone_number, category, strand, section = row

        matches_category = (not category_filter or category == category_filter)
        matches_strand = (not strand_filter or strand_filter in strand.lower())
        matches_section = (not section_filter or section_filter in section.lower())

        if matches_category and matches_strand and matches_section:
            display_text = f"{phone_number} - Category: {category} - Strand: {strand}, - Section: {section}"
            numbers_list.insert(tk.END, display_text)

def deselect_all_recipients():
    numbers_list.select_clear(0, tk.END)
    update_send_sms_button_state()

def open_template_window():
    template_window = tk.Toplevel()
    template_window.title("Message Template")
    template_window.geometry("400x300")

    label = tk.Label(template_window, text="Enter your message template:", font=("Arial", 12))
    label.pack(pady=10)

    template_text = tk.Text(template_window, height=10, width=50)
    template_text.pack(pady=10)

    def save_template():
        template = template_text.get("1.0", tk.END).strip()
        if not template:
            messagebox.showerror("Error", "Template cannot be empty.")
            return
        
        with open("templates.txt", "a") as file:
            file.write(template + "\n\n")
        messagebox.showinfo("Success", "Template saved successfully!")
        template_text.delete("1.0", tk.END)

    save_button = tk.Button(template_window, text="Save Template", command=save_template)
    save_button.pack(pady=10)

def open_template_viewer():
    template_viewer_window = tk.Toplevel()
    template_viewer_window.title("Saved Message Templates")
    template_viewer_window.geometry("400x300")

    templates_text = tk.Text(template_viewer_window, height=15, width=50)
    templates_text.pack(pady=10)

    try:
        with open("templates.txt", "r") as file:
            templates = file.read()
            templates_text.insert(tk.END, templates)
    except FileNotFoundError:
        templates_text.insert(tk.END, "No templates found.")

root = tk.Tk()
root.title("PLMar SHS - SMS Announcement System")
root.geometry("610x620")
root.iconbitmap(r"e:\plmars.ico")


canvas = tk.Canvas(root)
scrollbar = tk.Scrollbar(root, orient="vertical", command=canvas.yview)
scrollable_frame = tk.Frame(canvas)

scrollable_frame.bind(
    "<Configure>",
    lambda e: canvas.configure(scrollregion=canvas.bbox("all"))
)

canvas.create_window((0, 0), window=scrollable_frame, anchor="n")
canvas.configure(yscrollcommand=scrollbar.set)

scrollbar.pack(side="right", fill="y")
canvas.pack(side="left", fill="both", expand=True)

header_frame = tk.Frame(scrollable_frame, bg="#4CAAD8", height=85)
header_frame.pack(fill=tk.X, pady=10)

try:
    logo = PhotoImage(file="e:\plmarsnobg.png")
    resized_logo = logo.subsample(3,3)
    logo_label = tk.Label(header_frame, image=resized_logo, bg="#4CAAD8")
    logo_label.image = resized_logo
    logo_label.pack(side=tk.LEFT, padx=10)
except Exception as e:
        print("Error loading logo", e)

header_label = tk.Label(header_frame, text="PAMANTASAN NG LUNGSOD NG MARIKINA\nSENIOR HIGH SCHOOL\nSMS ANNOUNCEMENT SYSTEM", bg="#4CAAD8", fg="white", font=("Arial", 14, "bold"))
header_label.pack(side="top", fill="x", padx=10, pady=10, expand=True)

details_frame = tk.Frame(scrollable_frame, padx=5, pady=5)
details_frame.pack(fill=tk.X, expand=True)

recipient_label = tk.Label(details_frame, text="Enter Recipient Number:", font=("Arial", 10))
recipient_label.grid(row=0, column=0, sticky="e", padx=5, pady=5)
recipient_entry = tk.Entry(details_frame, width=25)
recipient_entry.grid(row=0, column=1, sticky="w", padx=5, pady=5)

category_label = tk.Label(details_frame, text="Category:", font=("Arial", 10))
category_label.grid(row=1, column=0, sticky="e", padx=5, pady=5)
category_var = tk.StringVar()
category_menu = ttk.Combobox(details_frame, textvariable=category_var, state="readonly", values=["Student", "Teacher"], width=22)
category_menu.grid(row=1, column=1, sticky="w", padx=5, pady=5)

strand_label = tk.Label(details_frame, text="Strand:", font=("Arial", 10))
strand_label.grid(row=2, column=0, sticky="e", padx=5, pady=5)
strand_entry = tk.Entry(details_frame, width=25)
strand_entry.grid(row=2, column=1, sticky="w", padx=5, pady=5)

section_label = tk.Label(details_frame, text="Section:", font=("Arial", 10))
section_label.grid(row=3, column=0, sticky="e", padx=5, pady=5)
section_entry = tk.Entry(details_frame, width=25)
section_entry.grid(row=3, column=1, sticky="w", padx=5, pady=5)

category_filter_label = tk.Label(details_frame, text="Filter by Category:", font=("Arial", 10))
category_filter_label.grid(row=4, column=0, sticky="w", padx=2, pady=2)

category_filter_var = tk.StringVar()
category_filter_menu = ttk.Combobox(details_frame, textvariable=category_filter_var, state="readonly", values=["", "Student", "Teacher"], width=7)
category_filter_menu.grid(row=5, column=0, sticky="w", padx=2, pady=2)

strand_filter_label = tk.Label(details_frame, text="Filter by Strand:", font=("Arial", 10))
strand_filter_label.grid(row=4, column=1, sticky="w", padx=2, pady=2)

strand_filter_var = tk.StringVar()
strand_filter_entry = tk.Entry(details_frame, textvariable=strand_filter_var, width=10)
strand_filter_entry.grid(row=5, column=1, sticky="w", padx=2, pady=2)

section_filter_label = tk.Label(details_frame, text="Filter by Section:", font=("Arial", 10))
section_filter_label.grid(row=4, column=2, sticky="w", padx=2, pady=2)

section_filter_var = tk.StringVar()
section_filter_entry = tk.Entry(details_frame, textvariable=section_filter_var, width=10)
section_filter_entry.grid(row=5, column=2, sticky="w", padx=2, pady=2)

input_frame = tk.Frame(scrollable_frame, padx=10, pady=10)
input_frame.pack(fill=tk.X, expand=True)

add_button = tk.Button(
    details_frame,
    text="Add",
    width=10,
    command=lambda: add_recipients_to_db(
        recipient_entry.get(),
        category_var.get(),
        strand_entry.get(),
        section_entry.get(),
    ),
)
add_button.grid(row=0, column=2, sticky="w", padx=5, pady=5)

load_button = tk.Button(details_frame, text="Load Contacts From File", width=20, command=load_contacts)
load_button.grid(row=0, column=3, sticky="w", padx=5, pady=5)

total_label = tk.Label(input_frame, text="Total Numbers:", font=("Arial", 10, "bold"))
total_label.grid(row=1, column=0, sticky="w", pady=10)

numbers_list = tk.Listbox(input_frame, width=95, height=8, selectmode=tk.MULTIPLE)
numbers_list.grid(row=2, column=0, columnspan=4, pady=1, sticky="nsew")

recipients = load_recipients()
for recipient in recipients:
    numbers_list.insert(tk.END, recipient)

message_frame = tk.Frame(scrollable_frame, padx=10, pady=10)
message_frame.pack(fill=tk.X, expand=True)

message_label = tk.Label(message_frame, text="Message:", font=("Arial", 10))
message_label.pack(anchor="w")
message_text = tk.Text(message_frame, height=10, width=50)
message_text.pack(fill=tk.BOTH, pady=1)

example_message = """PLMARS:Announcement!"""
message_text.insert(tk.END, example_message)

options_frame = tk.Frame(scrollable_frame, padx=10, pady=10)
options_frame.pack(fill=tk.X, expand=True)

one_time_radio = tk.Radiobutton(options_frame, text="One-Time Execution", value=1)
one_time_radio.grid(row=2, column=0, sticky="w", pady=5)
one_by_one_radio = tk.Radiobutton(options_frame, text="One-by-One Execution Mode", value=2)
one_by_one_radio.grid(row=3, column=0, sticky="w")
delay_checkbox = tk.Checkbutton(options_frame, text="Delayed Delivery:")
delay_checkbox.grid(row=4, column=0, sticky="w")

delay_frame = tk.Frame(options_frame)
delay_frame.grid(row=4, column=1, sticky="w")

delay_sms_spinbox = tk.Spinbox(delay_frame, from_=1, to=10, width=5)
delay_sms_spinbox.pack(side=tk.LEFT)
delay_sms_label = tk.Label(delay_frame, text="SMS")
delay_sms_label.pack(side=tk.LEFT, padx=5)
delay_time_spinbox = tk.Spinbox(delay_frame, from_=1, to=10, width=5)
delay_time_spinbox.pack(side=tk.LEFT)
delay_time_label = tk.Label(delay_frame, text="Sec.")
delay_time_label.pack(side=tk.LEFT, padx=5)


button_frame = tk.Frame(scrollable_frame, padx=10, pady=10)
button_frame.pack(fill=tk.X, expand=True)

sent_items_button = tk.Button(button_frame, text="SENT ITEMS", width=10, bg="green", fg="white", command=show_sent_items)
sent_items_button.pack(side=tk.LEFT, padx=5)

send_button = tk.Button(button_frame, text="SEND", width=10, bg="blue", fg="white", command=lambda: [
    send_sms(),
    save_message_to_db(message_text.get("1.0", tk.END).strip())
])
send_button.pack(side=tk.LEFT, padx=5)
send_button.config(state=tk.DISABLED)

template_frame = tk.Frame(scrollable_frame, padx=10, pady=10)
template_frame.pack(fill=tk.X, expand=True)

template_button = tk.Button(template_frame, text="ADD TEMPLATE", width=15, command=open_template_window)
template_button.pack(side=tk.LEFT, padx=5)

view_templates_button = tk.Button(template_frame, text="VIEW TEMPLATES", width=15, command=open_template_viewer)
view_templates_button.pack(side=tk.LEFT, padx=5)

select_all_button = tk.Button(button_frame, text="SELECT ALL", width=10, command=select_all_recipients)
select_all_button.pack(side=tk.LEFT, padx=5)

deselect_all_button = tk.Button(button_frame, text="DESELECT ALL", width=10, command=deselect_all_recipients)
deselect_all_button.pack(side=tk.LEFT, padx=5)

numbers_list.bind('<<ListboxSelect>>', update_send_sms_button_state)
category_filter_menu.bind("<<ComboboxSelected>>", lambda event: filter_recipients())
strand_filter_entry.bind("<KeyRelease>", lambda event: filter_recipients())
section_filter_entry.bind("<KeyRelease>", lambda event: filter_recipients())

reset_button = tk.Button(
    button_frame, 
    text="RESET", 
    width=10, 
    bg="red", 
    fg="white", 
    command=lambda: [
        reset_recipients(), 
        message_text.delete("1.0", tk.END),
        update_total_recipients_count(),
        ],
)
reset_button.pack(side=tk.LEFT, padx=5)

footer_frame = tk.Frame(scrollable_frame, padx=10, pady=10)
footer_frame.pack(fill=tk.X, expand=True)

about_button = tk.Button(footer_frame, text="ABOUT", width=10, command=lambda: messagebox.showinfo("About", "PLMar SHS SMS Announcement System\nVersion 1.0"))
about_button.pack(side=tk.LEFT, padx=5)

help_button = tk.Button(footer_frame, text="HELP", width=10, command=lambda: messagebox.showinfo("Help", "For assistance, contact support@plmar.edu.ph"))
help_button.pack(side=tk.LEFT, padx=5)

root.mainloop()