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.
87 lines
2.5 KiB
JavaScript
87 lines
2.5 KiB
JavaScript
const sqlite3 = require('sqlite3').verbose();
|
||
const path = require('path');
|
||
|
||
const dbPath = path.join(__dirname, '..', 'database', 'travel_rates.db');
|
||
|
||
const db = new sqlite3.Database(dbPath, (err) => {
|
||
if (err) {
|
||
console.error('❌ Database connection failed:', err);
|
||
process.exit(1);
|
||
}
|
||
});
|
||
|
||
console.log('\n🍽️ Checking Meal Rates Table...\n');
|
||
console.log('='.repeat(60));
|
||
|
||
// Check if meal_rates table exists
|
||
const checkTableQuery = `
|
||
SELECT name FROM sqlite_master
|
||
WHERE type='table' AND name='meal_rates'
|
||
`;
|
||
|
||
db.get(checkTableQuery, [], (err, row) => {
|
||
if (err) {
|
||
console.error('❌ Query failed:', err);
|
||
db.close();
|
||
process.exit(1);
|
||
}
|
||
|
||
if (!row) {
|
||
console.log('\n❌ meal_rates table does NOT exist in database\n');
|
||
console.log('The database migration only created accommodation_rates table.');
|
||
console.log('Meal rates need to be added separately.\n');
|
||
db.close();
|
||
return;
|
||
}
|
||
|
||
console.log('✅ meal_rates table EXISTS\n');
|
||
|
||
// Count records
|
||
const countQuery = 'SELECT COUNT(*) as count FROM meal_rates';
|
||
|
||
db.get(countQuery, [], (err, countRow) => {
|
||
if (err) {
|
||
console.error('❌ Count query failed:', err);
|
||
db.close();
|
||
process.exit(1);
|
||
}
|
||
|
||
console.log(`📊 Total meal rate records: ${countRow.count}\n`);
|
||
|
||
if (countRow.count === 0) {
|
||
console.log('⚠️ Table exists but is EMPTY - no meal rates imported\n');
|
||
db.close();
|
||
return;
|
||
}
|
||
|
||
// Show sample records
|
||
const sampleQuery = `
|
||
SELECT city_name, country, breakfast, lunch, dinner, incidentals, total_daily
|
||
FROM meal_rates
|
||
LIMIT 10
|
||
`;
|
||
|
||
db.all(sampleQuery, [], (err, rows) => {
|
||
if (err) {
|
||
console.error('❌ Sample query failed:', err);
|
||
db.close();
|
||
process.exit(1);
|
||
}
|
||
|
||
console.log('Sample meal rates:\n');
|
||
rows.forEach((row, index) => {
|
||
console.log(`${index + 1}. ${row.city_name}, ${row.country}`);
|
||
console.log(` Breakfast: $${row.breakfast}`);
|
||
console.log(` Lunch: $${row.lunch}`);
|
||
console.log(` Dinner: $${row.dinner}`);
|
||
console.log(` Incidentals: $${row.incidentals}`);
|
||
console.log(` Total Daily: $${row.total_daily}\n`);
|
||
});
|
||
|
||
db.close();
|
||
});
|
||
});
|
||
});
|
||
|
||
console.log('='.repeat(60) + '\n');
|