import os import json import pandas as pd from datetime import datetime, timedelta from pathlib import Path import gradio as gr import sqlite3 from typing import Dict, List, Optional, Any import zipfile import io import csv # Configuration DATA_DIR = Path("data") DATA_DIR.mkdir(exist_ok=True) VENDORS_DB = DATA_DIR / "vendors.db" JSON_BACKUP = DATA_DIR / "vendors_backup.json" # Initialize database def init_database(): """Initialize SQLite database for persistent storage""" conn = sqlite3.connect(str(VENDORS_DB)) cursor = conn.cursor() # Create vendors table cursor.execute(''' CREATE TABLE IF NOT EXISTS vendors ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, contact TEXT, email TEXT NOT NULL, last_purchase DATE, status TEXT DEFAULT 'Not Contacted', initial_request DATE, last_followup DATE, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') # Create email log table cursor.execute(''' CREATE TABLE IF NOT EXISTS email_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, vendor_id INTEGER, subject TEXT, sent_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status TEXT, FOREIGN KEY (vendor_id) REFERENCES vendors (id) ) ''') # Create data files table cursor.execute(''' CREATE TABLE IF NOT EXISTS data_files ( id INTEGER PRIMARY KEY AUTOINCREMENT, vendor_id INTEGER, filename TEXT, filepath TEXT, upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status TEXT, FOREIGN KEY (vendor_id) REFERENCES vendors (id) ) ''') conn.commit() conn.close() # Database operations def get_db_connection(): conn = sqlite3.connect(str(VENDORS_DB)) conn.row_factory = sqlite3.Row return conn # Sample data for initial setup SAMPLE_VENDORS = [ { "name": "Tech Supplies Inc.", "contact": "John Smith", "email": "john@techsupplies.com", "last_purchase": "2024-11-15", "status": "Received", "initial_request": "2024-11-01", "last_followup": "2024-11-10", "notes": "Data received, needs validation" }, { "name": "Global Manufacturing", "contact": "Sarah Johnson", "email": "s.johnson@globalmfg.com", "last_purchase": "2024-10-22", "status": "Request Sent", "initial_request": "2024-11-05", "last_followup": "", "notes": "Awaiting response" }, { "name": "Quality Parts Co.", "contact": "Robert Chen", "email": "r.chen@qualityparts.com", "last_purchase": "2024-09-30", "status": "Follow-up Needed", "initial_request": "2024-10-28", "last_followup": "2024-11-12", "notes": "Promised by Nov 20" } ] def setup_initial_data(): """Load initial sample data if database is empty""" conn = get_db_connection() cursor = conn.cursor() cursor.execute("SELECT COUNT(*) as count FROM vendors") count = cursor.fetchone()["count"] if count == 0: for vendor in SAMPLE_VENDORS: cursor.execute(''' INSERT INTO vendors (name, contact, email, last_purchase, status, initial_request, last_followup, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ''', ( vendor["name"], vendor["contact"], vendor["email"], vendor["last_purchase"], vendor["status"], vendor["initial_request"], vendor["last_followup"], vendor["notes"] )) conn.commit() conn.close() # Vendor CRUD operations def get_all_vendors(): """Retrieve all vendors from database""" conn = get_db_connection() cursor = conn.cursor() cursor.execute("SELECT * FROM vendors ORDER BY name") vendors = [dict(row) for row in cursor.fetchall()] conn.close() return vendors def add_vendor(vendor_data: Dict): """Add new vendor to database""" conn = get_db_connection() cursor = conn.cursor() cursor.execute(''' INSERT INTO vendors (name, contact, email, last_purchase, status, initial_request, last_followup, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ''', ( vendor_data["name"], vendor_data.get("contact", ""), vendor_data["email"], vendor_data.get("last_purchase", ""), vendor_data.get("status", "Not Contacted"), vendor_data.get("initial_request", ""), vendor_data.get("last_followup", ""), vendor_data.get("notes", "") )) vendor_id = cursor.lastrowid conn.commit() conn.close() return vendor_id def update_vendor(vendor_id: int, updates: Dict): """Update vendor information""" conn = get_db_connection() cursor = conn.cursor() set_clause = ", ".join([f"{k} = ?" for k in updates.keys()]) set_clause += ", updated_at = CURRENT_TIMESTAMP" values = list(updates.values()) + [vendor_id] cursor.execute(f''' UPDATE vendors SET {set_clause} WHERE id = ? ''', values) conn.commit() conn.close() def delete_vendor(vendor_id: int): """Delete vendor from database""" conn = get_db_connection() cursor = conn.cursor() cursor.execute("DELETE FROM vendors WHERE id = ?", (vendor_id,)) conn.commit() conn.close() # Email template management def get_email_template(): """Retrieve the current email template""" template_file = DATA_DIR / "email_template.txt" if template_file.exists(): return template_file.read_text() # Default template default_template = """Subject: Action Required: Product Data Request for {company_name} - Fiscal Year 2025 Reconciliation Dear {contact_name}, I hope this email finds you well. This is {your_name} from the Accounting Department at {company_name}. As part of our ongoing system data enhancement and financial reconciliation project, we are requesting your assistance in providing a detailed list of all products supplied to us during the Fiscal Year 2025 (Date Range: {start_date} to {end_date}). This data is essential for ensuring the accuracy of our inventory records, pricing files, and general ledger, and will help streamline our future transactions. Please provide the data in a CSV or Excel file with the following columns: DATE | SKU | DESCRIPTION | PO Number | PRICE | QUANTITY | AMOUNT Data Scope: All invoices/fulfillments dated within FY2025. To facilitate organization, please name your file: {vendor_name}_FY2025_ProductList.csv Requested Deadline: {deadline} If you did not have any activity with us in FY2025, please simply reply "No Activity" to this email so we can update our records. We sincerely appreciate your partnership and support in this important matter. Please let me know if you have any questions or require a list of your PO numbers from our side to compile this data. Best regards, {your_name} Accountant {company_name} [Your Phone Number] [Your Email]""" return default_template def save_email_template(template: str): """Save email template to file""" template_file = DATA_DIR / "email_template.txt" template_file.write_text(template) # Data export functions def export_vendors_csv(): """Export vendors to CSV format""" vendors = get_all_vendors() if not vendors: return None output = io.StringIO() fieldnames = ["id", "name", "contact", "email", "last_purchase", "status", "initial_request", "last_followup", "notes", "created_at", "updated_at"] writer = csv.DictWriter(output, fieldnames=fieldnames) writer.writeheader() writer.writerows(vendors) return output.getvalue() def generate_zip_file(): """Generate ZIP file with all vendor data""" zip_buffer = io.BytesIO() with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file: # Add vendors CSV vendors_csv = export_vendors_csv() if vendors_csv: zip_file.writestr("vendors_checklist.csv", vendors_csv) # Add email template template = get_email_template() zip_file.writestr("email_template.txt", template) # Add data format specification spec = """DATA FORMAT SPECIFICATION ======================= File Format: CSV or Excel (.xlsx) Date Range: Fiscal Year 2025 (January 1, 2024 - December 31, 2024) Required Columns: 1. DATE (MM/DD/YYYY): Invoice or shipment date 2. SKU (Text): Vendor's product code 3. DESCRIPTION (Text): Product description 4. PO_NUMBER (Text): Purchase order number 5. PRICE (Number, 2 decimals): Unit price 6. QUANTITY (Number): Quantity shipped 7. AMOUNT (Number, 2 decimals): Line total (optional) Sample: DATE,SKU,DESCRIPTION,PO_NUMBER,PRICE,QUANTITY,AMOUNT 07/15/2024,PROD-12345,Widget A,PO-2024-00123,24.99,100,2499.00""" zip_file.writestr("data_format_specification.txt", spec) # Add project overview overview = f"""PROJECT OVERVIEW ================ Project: Vendor Data Collection FY2025 Created: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')} Total Vendors: {len(get_all_vendors())} Status Summary: {get_status_summary()}""" zip_file.writestr("project_overview.txt", overview) zip_buffer.seek(0) return zip_buffer.getvalue() def get_status_summary(): """Get summary of vendor statuses""" vendors = get_all_vendors() status_counts = {} for vendor in vendors: status = vendor["status"] status_counts[status] = status_counts.get(status, 0) + 1 return ", ".join([f"{k}: {v}" for k, v in status_counts.items()]) # Gradio Interface Components def create_vendor_table(): """Create and display vendor table""" vendors = get_all_vendors() if not vendors: return gr.DataFrame(value=pd.DataFrame(columns=[ "ID", "Name", "Contact", "Email", "Last Purchase", "Status", "Initial Request", "Last Follow-up", "Notes" ])) df = pd.DataFrame(vendors) df = df[["id", "name", "contact", "email", "last_purchase", "status", "initial_request", "last_followup", "notes"]] return gr.DataFrame( value=df, headers=["ID", "Name", "Contact", "Email", "Last Purchase", "Status", "Initial Request", "Last Follow-up", "Notes"], datatype=["number", "str", "str", "str", "str", "str", "str", "str", "str"], row_count=len(vendors), col_count=(9, "fixed"), interactive=True ) def add_vendor_ui(): """UI for adding new vendor""" with gr.Row(): with gr.Column(scale=1): name = gr.Textbox(label="Vendor Name", placeholder="Enter vendor name") contact = gr.Textbox(label="Contact Person", placeholder="Contact name") email = gr.Textbox(label="Email", placeholder="vendor@company.com") with gr.Column(scale=1): last_purchase = gr.Textbox(label="Last Purchase Date", placeholder="YYYY-MM-DD") status = gr.Dropdown( label="Status", choices=["Not Contacted", "Request Sent", "Received", "Follow-up Needed", "Declined", "No FY2025 Activity"], value="Not Contacted" ) notes = gr.Textbox(label="Notes", placeholder="Additional notes") return [name, contact, email, last_purchase, status, notes] def stats_dashboard(): """Create statistics dashboard""" vendors = get_all_vendors() total = len(vendors) if total == 0: stats = {"Total": 0, "Received": 0, "Pending": 0, "Not Contacted": 0} progress = 0 else: received = len([v for v in vendors if v["status"] == "Received"]) pending = len([v for v in vendors if v["status"] in ["Request Sent", "Follow-up Needed"]]) not_contacted = len([v for v in vendors if v["status"] == "Not Contacted"]) stats = { "Total Vendors": total, "Data Received": received, "Pending Response": pending, "Not Contacted": not_contacted } progress = (received / total) * 100 if total > 0 else 0 return gr.JSON(value=stats), gr.Number(value=progress, label="Completion %") def email_composer(): """Email composition interface""" vendors = get_all_vendors() vendor_choices = [f"{v['id']}: {v['name']} - {v['contact']}" for v in vendors] with gr.Row(): with gr.Column(scale=2): template = gr.Textbox( value=get_email_template(), label="Email Template", lines=20, interactive=True ) with gr.Column(scale=1): gr.Markdown("### Variables") gr.Markdown(""" Available placeholders: - `{company_name}`: Your company name - `{your_name}`: Your name - `{contact_name}`: Vendor contact - `{vendor_name}`: Vendor name - `{start_date}`: FY start - `{end_date}`: FY end - `{deadline}`: Response deadline """) selected_vendors = gr.CheckboxGroup( choices=vendor_choices, label="Select Vendors", interactive=True ) preview_btn = gr.Button("Preview Email") send_btn = gr.Button("Send to Selected", variant="primary") return template, selected_vendors, preview_btn, send_btn # Callback functions def handle_add_vendor(name, contact, email, last_purchase, status, notes): """Handle adding new vendor""" if not name or not email: return gr.Error("Name and email are required"), None vendor_data = { "name": name, "contact": contact, "email": email, "last_purchase": last_purchase, "status": status, "initial_request": datetime.now().strftime("%Y-%m-%d") if status != "Not Contacted" else "", "notes": notes } try: vendor_id = add_vendor(vendor_data) return gr.Info(f"Vendor '{name}' added successfully!"), create_vendor_table() except Exception as e: return gr.Error(f"Error adding vendor: {str(e)}"), None def handle_export_data(): """Handle data export""" try: zip_data = generate_zip_file() timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") filename = f"vendor_data_fy2025_{timestamp}.zip" # Save to temporary file temp_file = DATA_DIR / filename temp_file.write_bytes(zip_data) return gr.File(value=str(temp_file), label="Download ZIP") except Exception as e: return gr.Error(f"Export failed: {str(e)}") def handle_send_email(template, selected_vendors): """Handle sending emails to selected vendors""" if not selected_vendors: return gr.Warning("Please select at least one vendor") # In a real implementation, this would connect to your email service # For now, we'll just log the action conn = get_db_connection() cursor = conn.cursor() for vendor_str in selected_vendors: vendor_id = int(vendor_str.split(":")[0]) cursor.execute(''' INSERT INTO email_log (vendor_id, subject, status) VALUES (?, ?, ?) ''', (vendor_id, "Product Data Request", "Queued")) # Update vendor status cursor.execute(''' UPDATE vendors SET status = 'Request Sent', initial_request = CASE WHEN initial_request IS NULL OR initial_request = '' THEN ? ELSE initial_request END, last_followup = ? WHERE id = ? ''', (datetime.now().strftime("%Y-%m-%d"), datetime.now().strftime("%Y-%m-%d"), vendor_id)) conn.commit() conn.close() return gr.Info(f"Email queued for {len(selected_vendors)} vendor(s)") # Main Gradio Interface def create_dashboard(): """Create the main dashboard interface""" with gr.Blocks(title="Vendor Data Collection Dashboard", theme=gr.themes.Soft()) as app: gr.Markdown("# 📊 Vendor Data Collection Dashboard") gr.Markdown("### Fiscal Year 2025 Product Data Enhancement Project") with gr.Tabs(): # Tab 1: Vendor Checklist with gr.Tab("📋 Vendor Checklist"): with gr.Row(): with gr.Column(scale=3): vendor_table = gr.DataFrame( label="Vendor List", interactive=True ) refresh_btn = gr.Button("Refresh Table", variant="secondary") export_csv_btn = gr.Button("Export to CSV") with gr.Column(scale=1): gr.Markdown("### Add New Vendor") add_form = add_vendor_ui() add_btn = gr.Button("Add Vendor", variant="primary") # Tab 2: Email Templates with gr.Tab("✉️ Email Templates"): template, selected_vendors, preview_btn, send_btn = email_composer() # Email preview preview = gr.Textbox( label="Email Preview", lines=10, interactive=False ) # Tab 3: Progress Dashboard with gr.Tab("📈 Progress Dashboard"): stats_json, progress = stats_dashboard() gr.Markdown("### Project Statistics") gr.JSON(label="Current Stats", value=stats_json) gr.Markdown("### Completion Progress") gr.ProgressBar(label="Overall Progress", value=progress) refresh_stats_btn = gr.Button("Refresh Statistics") # Tab 4: Export Tools with gr.Tab("📦 Export Tools"): gr.Markdown("### Generate Master ZIP File") gr.Markdown(""" This will create a ZIP file containing: - Vendor checklist (CSV) - Email template - Data format specifications - Project overview """) zip_btn = gr.Button("Generate ZIP File", variant="primary") download_file = gr.File(label="Download") gr.Markdown("### Individual Exports") with gr.Row(): gr.Button("Export Vendor List") gr.Button("Export Email Log") gr.Button("Export Statistics") # Tab 5: Data Format with gr.Tab("📄 Data Format"): gr.Markdown("## Data Format Requirements") gr.Markdown(""" ### Required File Format - **Format**: CSV or Excel (.xlsx) - **Date Range**: Fiscal Year 2025 (Jan 1, 2024 - Dec 31, 2024) - **File Naming**: `[VendorName]_FY2025_ProductList.csv` ### Required Columns | Column | Type | Format | Required | Description | |--------|------|--------|----------|-------------| | DATE | Date | MM/DD/YYYY | Yes | Invoice/shipment date | | SKU | Text | Vendor code | Yes | Product identifier | | DESCRIPTION | Text | Clear description | Yes | Product details | | PO_NUMBER | Text | Your PO number | Yes | Purchase order | | PRICE | Number | 2 decimals | Yes | Unit price | | QUANTITY | Number | Integer | Yes | Quantity shipped | | AMOUNT | Number | 2 decimals | Optional | Line total | ### Sample CSV ```csv DATE,SKU,DESCRIPTION,PO_NUMBER,PRICE,QUANTITY,AMOUNT 07/15/2024,PROD-12345,Widget A,PO-2024-00123,24.99,100,2499.00 08/22/2024,PROD-67890,Gadget B,PO-2024-00245,15.50,250,3875.00 ``` """) # Event handlers refresh_btn.click( fn=create_vendor_table, outputs=[vendor_table] ) add_btn.click( fn=handle_add_vendor, inputs=add_form, outputs=[gr.Textbox(visible=False), vendor_table] ) zip_btn.click( fn=handle_export_data, outputs=[download_file] ) send_btn.click( fn=handle_send_email, inputs=[template, selected_vendors], outputs=[gr.Textbox(visible=False)] ) refresh_stats_btn.click( fn=stats_dashboard, outputs=[stats_json, progress] ) # Initialize on load app.load( fn=create_vendor_table, outputs=[vendor_table] ).then( fn=stats_dashboard, outputs=[stats_json, progress] ) return app # Initialize database and setup def main(): """Main entry point""" print("Initializing database...") init_database() setup_initial_data() print(f"Database ready: {VENDORS_DB}") # Create and launch Gradio app app = create_dashboard() return app if __name__ == "__main__": # For Hugging Face Spaces deployment app = main() app.launch( server_name="0.0.0.0", server_port=7860, share=False )