Spaces:
Runtime error
Runtime error
Download app.py from jonels1988/VSD: direct link, hf CLI and curl.
- Browser
- Download file 22.1 kB
-
https://huggingface.co/spaces/jonels1988/VSD/resolve/main/app.py
- Command line
-
hf download hf://spaces/jonels1988/VSD/app.py
-
curl -L -o app.py https://huggingface.co/spaces/jonels1988/VSD/resolve/main/app.py
22.1 kB
| 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 | |
| ) |