# Sales Dashboard - Technical Development Summary ## Overview A **web-based Sales Dashboard** that reads from your Google Sheets and provides real-time data visualization with advanced filtering. βœ… **Focus**: Sales only βœ… **Status**: Fully operational βœ… **Dependency**: None (completely independent) --- ## What It Does ### Core Features - πŸ“Š Visualize sales data in real-time - πŸ” Filter by Cliente, Zona, Comercial, Familia, MΓͺs - πŸ“ˆ View summary statistics (revenue, quantity, clients) - πŸ” Secure Google OAuth authentication - πŸ“± Responsive on mobile/desktop ### What It Does NOT Do - ❌ No inventory management (see [INVENTORY_SETUP.md](INVENTORY_SETUP.md)) - ❌ No data editing (read-only) - ❌ No email reports - ❌ No forecasting --- ## Technical Stack ### Backend - **Flask** - Python web framework - **gspread** - Google Sheets API - **pandas** - Data filtering - **Flask-Login** - Session management - **google-auth-oauthlib** - OAuth 2.0 ### Frontend - HTML5 + Jinja2 - CSS3 (gradients, responsive) - Vanilla JavaScript (no libraries) ### Data - Google Sheets (read-only) - 5-minute caching system - In-memory filtering --- ## Your Data Structure (11 Columns) ``` Cliente | Zona | Comercial | Desconto | Prazo Pagamento Dias CΓ³digo | Referencia | Familia | MΓͺs | Quant FaturaΓ§ao ``` ### Real Example ``` ACME Corp | North | JosΓ© Amor | 5 | 30 | PROD-001 | SKU-2024 | Electronics January | 100 | 5000.00 ``` --- ## How It Works (User Flow) ### 1. First Login ``` Visit: https://your-domain/ ↓ Click "Login with Google" ↓ Google OAuth popup β†’ Authorize ↓ Redirected to Dashboard ↓ System asks for Google Sheet ID ↓ Paste Sheet ID from URL ↓ Data loads! ``` ### 2. Using the Dashboard ``` See all sales data + filters ↓ User clicks filter: "Comercial = JosΓ© Amor" ↓ JavaScript sends request with filter ↓ Flask receives + filters pandas DataFrame ↓ Returns filtered JSON ↓ Table updates instantly ``` ### 3. Performance ``` First request: 2-3 seconds (Google Sheets API) Cache hit (<5 min): <500ms Click Refresh button: Force reload ``` --- ## Architecture ### Data Flow ``` Google Sheets (Sales Data) ↓ [Get-Sheets-Data API] ↓ Pandas DataFrame ↓ Apply Filters ↓ Return JSON ↓ Frontend Table ``` ### Routes ``` GET /dashboard β†’ Main UI (requires login) GET /get-sheets-data β†’ API endpoint (returns JSON) POST /set-spreadsheet-id β†’ Save Sheet ID ``` --- ## Role-Based Access | Role | Sales Access | Notes | |------|--------------|-------| | admin | βœ… Full | See all sales | | comercial | βœ… Own only* | See only their sales | | viewer | βœ… Full | View-only (read-only) | | warehouse | ❌ NO | Has inventory access instead | *Comercials automatically filtered to show only their data --- ## API Response Example ```json GET /get-sheets-data?comercial=JosΓ©%20Amor { "data": [ { "Cliente": "ACME Corp", "Zona": "North", "Comercial": "JosΓ© Amor", "Desconto": 5, "Prazo Pagamento Dias": 30, "CΓ³digo": "PROD-001", "Referencia": "SKU-2024", "Familia": "Electronics", "MΓͺs": "January", "Quant": 100, "FaturaΓ§ao": 5000.00 } ], "summary": { "total_records": 1, "total_faturacao": 5000.00, "total_quantity": 100, "unique_clients": 1 } } ``` --- ## File Locations ### Code ``` app.py (lines ~1292-1450) β”œβ”€β”€ Route: /dashboard β”œβ”€β”€ Route: /get-sheets-data └── Route: /set-spreadsheet-id templates/ β”œβ”€β”€ base.html (header + nav) β”œβ”€β”€ index.html (login page) └── dashboard.html (sales UI) ``` ### Documentation ``` README.md β†’ Overall guide QUICKSTART.md β†’ 5-min setup DEVELOPMENT_SUMMARY.md β†’ This file (sales details) SYSTEM_OVERVIEW.md β†’ Both modules overview INVENTORY_SETUP.md β†’ Inventory (separate) ``` --- ## Configuration ### Google Sheet Setup 1. Create Google Sheet with 11 columns 2. Add your sales data 3. Share with Google OAuth account 4. Copy Sheet ID from URL ### In app.py ```python # Line ~35 DEFAULT_SPREADSHEET_ID = "YOUR-SHEET-ID-HERE" ``` ### User Roles (app.py) ```python # Add users to these sets: ADMIN_EMAILS = { "admin@globalerc.com" } COMMERCIAL_EMAILS = { "carlos@globalerc.com" } SALES_ACCESS_MAP = { "carlos@globalerc.com": ["Carlos Sales"] } ``` --- ## Filtering (Frontend) ### JavaScript Filters Available - **Cliente dropdown** β†’ Filter by customer - **Zona dropdown** β†’ Filter by region - **Comercial dropdown** β†’ Filter by salesperson - **Familia dropdown** β†’ Filter by product family - **MΓͺs dropdown** β†’ Filter by month ### Multiple Filters - Click multiple filters together - All filter conditions combined (AND logic) - Table updates in real-time - Refresh to reset --- ## Performance & Caching ### Cache System ``` Request arrives ↓ Check: Is data in cache AND <5 min old? ↓ YES β†’ Return cached (instant) ↓ NO β†’ Fetch from Google Sheets (2-3 sec) ↓ Store in cache ↓ Return to user ``` ### Cache Keys - Separate cache per user + spreadsheet - Format: `data:{spreadsheet_id}:{user_email}` - 5-minute TTL (time to live) ### Manual Refresh - Click "πŸ”„ Refresh" button - Clears cache for this user - Forces fresh data from Google Sheets --- ## Security βœ… **Read-Only** - Never modifies Google Sheets βœ… **OAuth 2.0** - Industry-standard authentication βœ… **Session Secure** - HttpOnly cookies βœ… **Role-Based** - Users see only permitted data βœ… **credentials.json** - In .gitignore (protected) --- ## Troubleshooting ### Problem: "Sheet not found" **Solution:** - Copy Sheet ID correctly from URL - Verify sheet is shared with OAuth account - Check for extra spaces in ID ### Problem: No data showing **Solution:** - Verify Google Sheet has data rows - Check column names match exactly - Click refresh πŸ”„ button - Check console for errors ### Problem: Filter not working **Solution:** - Verify data in that column exists - Check spelling (case-sensitive) - Try different filter - Restart app ### Problem: "Access Denied" **Solution:** - Check email in ADMIN_EMAILS or COMMERCIAL_EMAILS - Verify spelling (must be lowercase) - Restart Flask app --- ## Customization (Examples) ### Add Another Filter Column In `templates/dashboard.html`: ```html ``` In `app.py` (in get-sheets-data): ```python new_filter = request.args.get('newcolumn') if new_filter and new_filter != 'all': df = df[df['New Column'] == new_filter] ``` ### Modify Summary Stats In `templates/dashboard.html`, update JavaScript: ```javascript // Add custom calculations avg_invoice = sum(FaturaΓ§ao) / count(records) ``` ### Change Colors In `templates/base.html`: ```css header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); /* Change these hex colors */ } ``` --- ## Deployment ### Development ```bash python app.py # Runs on http://localhost:5000 ``` ### Production (Gunicorn) ```bash pip install gunicorn gunicorn -w 4 -b 0.0.0.0:5000 app:app ``` ### Production Checklist - [ ] Set `SESSION_COOKIE_SECURE = True` (needs HTTPS) - [ ] Change `SECRET_KEY` to random string - [ ] Set `DEBUG = False` - [ ] Use environment variables for configs - [ ] Enable HTTPS/SSL - [ ] Set up error logging --- ## Related Documentation | Document | Purpose | |----------|---------| | [SYSTEM_OVERVIEW.md](SYSTEM_OVERVIEW.md) | View both modules & architecture | | [INVENTORY_SETUP.md](INVENTORY_SETUP.md) | Inventory management (separate system) | | [README.md](README.md) | Overall project | | [QUICKSTART.md](QUICKSTART.md) | Quick setup | --- ## Quick Reference ### View Sales ``` https://your-domain/dashboard ``` ### API Call ``` GET https://your-domain/get-sheets-data?comercial=Jose%20Amor ``` ### Add Config ```python # app.py line ~35 DEFAULT_SPREADSHEET_ID = "your-id-here" ``` ### Restart ```bash # Kill Flask Ctrl+C # Restart python app.py ``` --- ## Notes - **Independent**: Sales module works without Inventory - **Read-Only**: Never changes your Google Sheets - **Fast**: 5-min cache keeps it responsive - **Secure**: OAuth authentication required - **Simple**: No database needed, just Google Sheets --- ## Status βœ… **PRODUCTION READY** Fully functional. Tested with real data. All security in place. --- **Version**: 1.0 **Updated**: February 2026 **Maintenance**: Low (just updates Google Sheet directly)