mirror of
https://github.com/mblanke/Gov_Travel_App.git
synced 2026-03-01 14:10:22 -05:00
mblanke
15094ac94b
- Implemented Python scraper using BeautifulSoup and pandas to automatically collect travel rates from official NJC website - Added currency extraction from table titles (supports EUR, USD, AUD, CAD, ARS, etc.) - Added country extraction from table titles for international rates - Flatten pandas MultiIndex columns for cleaner data structure - Default to CAD for domestic Canadian sources (accommodations and domestic tables) - Created SQLite database schema (raw_tables, rate_entries, exchange_rates, accommodations) - Successfully scraped 92 tables with 17,205 rate entries covering 25 international cities - Added migration script to convert scraped data to Node.js database format - Updated .gitignore for Python files (.venv/, __pycache__, *.pyc, *.sqlite3) - Fixed city validation and currency conversion in main app - Added comprehensive debug and verification scripts This replaces manual JSON maintenance with automated data collection from official government source.
14 KiB
14 KiB
Database Visual Overview
📊 Database Architecture
┌─────────────────────────────────────────────────────┐
│ Government Travel Cost Estimator │
│ (Web Application) │
└──────────────────┬──────────────────────────────────┘
│
┌──────────┴──────────┐
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐
│ perDiem │ │accommodation │
│ Rates.json │ │ Rates.json │
└──────────────┘ └──────────────┘
🗂️ Per Diem Database Structure
perDiemRates.json
│
├── metadata
│ ├── effectiveDate: "2025-10-01"
│ ├── version: "1.0"
│ ├── lastUpdated: "2025-10-30"
│ └── source: "NJC Travel Directive"
│
├── regions
│ │
│ ├── canada
│ │ ├── meals
│ │ │ ├── breakfast (rate100: 29.05, rate75: 21.80, rate50: 14.55)
│ │ │ ├── lunch (rate100: 29.60, rate75: 22.20, rate50: 14.80)
│ │ │ └── dinner (rate100: 60.75, rate75: 45.55, rate50: 30.40)
│ │ ├── incidentals (rate100: 17.30, rate75: 13.00)
│ │ └── privateAccommodation (50.00/night)
│ │
│ ├── yukon (similar structure)
│ ├── nwt (similar structure)
│ ├── nunavut (similar structure)
│ ├── usa (similar structure)
│ ├── alaska (similar structure)
│ └── international (similar structure)
│
└── rateRules
├── day1to30: "rate100"
├── day31to120: "rate75"
└── day121onward: "rate50"
🏨 Accommodation Database Structure
accommodationRates.json
│
├── metadata
│ ├── effectiveDate: "2025-10-30"
│ ├── version: "1.0"
│ └── source: "PWGSC Accommodation Directory"
│
├── cities (Canadian & US)
│ ├── ottawa (standard: 165, max: 200, CAD)
│ ├── toronto (standard: 180, max: 220, CAD)
│ ├── vancouver (standard: 190, max: 240, CAD)
│ ├── newyork (standard: 250, max: 350, USD)
│ └── ... (30+ cities)
│
├── internationalCities
│ ├── london (standard: 280, max: 380, CAD)
│ ├── paris (standard: 260, max: 350, CAD)
│ ├── tokyo (standard: 240, max: 340, CAD)
│ └── ... (8 cities)
│
└── defaults (by region)
├── canada (150/185 CAD)
├── usa (150/200 USD)
├── yukon (185/230 CAD)
└── ... (7 regions)
🔄 Data Flow Diagram
┌────────────────┐
│ User Inputs │
│ - Departure │
│ - Destination │
│ - Dates │
└───────┬────────┘
│
▼
┌────────────────────┐
│ JavaScript │
│ - loadDatabases() │
└───────┬────────────┘
│
├─────────────────────┐
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐
│Load perDiem │ │Load accom. │
│Database │ │Database │
└───────┬──────┘ └───────┬──────┘
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐
│Get Region │ │Find City │
│Rates │ │Rates │
└───────┬──────┘ └───────┬──────┘
│ │
└─────────┬───────────┘
│
▼
┌──────────────────┐
│Calculate Costs │
│- Meals │
│- Incidentals │
│- Accommodation │
│- Flight │
└────────┬─────────┘
│
▼
┌──────────────────┐
│Display Results │
│- Total Cost │
│- Breakdown │
│- Policy Links │
└──────────────────┘
📋 Rate Lookup Logic
Per Diem Lookup
User selects destination type: "canada"
↓
Look up: perDiemRatesDB.regions["canada"]
↓
Extract:
├── Breakfast: $29.05
├── Lunch: $29.60
├── Dinner: $60.75
└── Incidental: $17.30
↓
Calculate: Daily Total = $136.70
Accommodation Lookup
User enters city: "Toronto"
↓
Normalize: "toronto" (lowercase)
↓
Check: accommodationRatesDB.cities["toronto"]
↓
Found! Return:
├── Standard Rate: $180
├── Max Rate: $220
└── Suggestion: "Toronto, ON: $180-$220 CAD"
↓
If NOT found → Use regional default
🎯 Rate Tiers Visual
┌────────────────────────────────────────────────┐
│ Extended Stay Rate Tiers │
├────────────────────────────────────────────────┤
│ │
│ Days 1-30 ████████████ 100% (rate100) │
│ Full meal allowance │
│ │
│ Days 31-120 ████████ 75% (rate75) │
│ Reduced meal allowance │
│ │
│ Days 121+ █████ 50% (rate50) │
│ Further reduced meals │
│ (Incidentals stay at 75%) │
│ │
└────────────────────────────────────────────────┘
💰 Cost Calculation Formula
Total Travel Cost = Flight + Accommodation + Meals + Incidentals
Where:
├── Flight = Base Cost × Business Class Multiplier (if ≥9 hours)
├── Accommodation = Per Night Rate × Number of Nights
├── Meals = (Breakfast + Lunch + Dinner) × Number of Days
└── Incidentals = Daily Rate × Number of Days
Example:
├── Flight: $650 × 2.5 = $1,625 (10-hour flight → business class)
├── Accommodation: $180 × 3 nights = $540
├── Meals: $119.40 × 4 days = $477.60
└── Incidentals: $17.30 × 4 days = $69.20
─────────────────────────────────────────────────
Total: $2,711.80 CAD
🗺️ Region Coverage Map
┌──────────────────────────────────────────┐
│ CANADA │
│ ┌────────────────────────────────────┐ │
│ │ Yukon NWT Nunavut │ │
│ │ $155.70 $159.05 $194.40 │ │
│ └────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────┐ │
│ │ Provinces (Canada) │ │
│ │ Daily Total: $136.70 CAD │ │
│ └────────────────────────────────────┘ │
└──────────────────────────────────────────┘
┌──────────────────────────────────────────┐
│ UNITED STATES │
│ ┌────────────────────────────────────┐ │
│ │ Alaska │ │
│ │ $155.70 USD │ │
│ └────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────┐ │
│ │ Continental USA │ │
│ │ Daily Total: $136.70 USD │ │
│ └────────────────────────────────────┘ │
└──────────────────────────────────────────┘
┌──────────────────────────────────────────┐
│ INTERNATIONAL │
│ Daily Total: $180.00 CAD (average) │
│ (Varies by country - see Appendix D) │
└──────────────────────────────────────────┘
📊 Database Size Comparison
File Size Visual:
┌────────────────────────────┐
│ perDiemRates.json │
│ ████ ~4KB │
└────────────────────────────┘
┌────────────────────────────┐
│ accommodationRates.json │
│ ██████ ~6KB │
└────────────────────────────┘
┌────────────────────────────┐
│ script.js │
│ █████████ ~8.5KB │
└────────────────────────────┘
┌────────────────────────────┐
│ styles.css │
│ ███████ ~6.7KB │
└────────────────────────────┘
Total Project: ~30KB (very lightweight!)
🔄 Update Frequency Timeline
┌─────────────────────────────────────────────────┐
│ Annual Cycle │
├─────────────────────────────────────────────────┤
│ │
│ Jan Feb Mar Apr May Jun │
│ │ │ │ │ │ │ │
│ └─────┴─────┴─────┴─────┴─────┘ │
│ Monitor for updates │
│ │
│ Jul Aug Sep [OCT] Nov Dec │
│ │ │ │ ╔═══╗ │ │ │
│ │ │ │ ║NEW║ │ │ │
│ │ │ │ ║RATES │
│ │ │ │ ╚═══╝ │ │ │
│ └─────┴─────┴────────┴──┴─────┘ │
│ Update DB! │
│ │
│ Typical effective date: October 1st │
│ Update databases immediately after release │
│ │
└─────────────────────────────────────────────────┘
🎨 JSON Structure Colors (Conceptual)
{
"metadata": { ... } ← 🔵 Blue (Info)
"regions": { ← 🟢 Green (Data)
"canada": { ← 🟡 Yellow (Region)
"meals": { ... } ← 🟠 Orange (Category)
"incidentals": { ... } ← 🟠 Orange (Category)
}
},
"rateRules": { ... } ← 🔴 Red (Rules)
}
📈 Data Hierarchy
Level 0: Database File
│
Level 1: ├── metadata
├── regions / cities
└── defaults / rules
│
Level 2: └── canada / toronto
│
Level 3: ├── meals / rates
└── incidentals / notes
│
Level 4: └── breakfast / standardRate
│
Level 5: └── rate100 / value: 29.05
Visual Guide Version: 1.0
Created: October 30, 2025
Purpose: Quick reference for database structure and flow