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.
335 lines
14 KiB
Markdown
335 lines
14 KiB
Markdown
# 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
|