# RateLoader v3.0

A unified Windows application for hotel rate management that combines three legacy utilities into a single cohesive system with GUI interface and background processing capabilities.

## Overview

RateLoader v3.0 integrates the functionality of:

- **ExportMailer** - CSV export and email functionality
- **PriceExport** - JSON price export to API
- **RateLoader** - HTTP server for rate loading

The application provides both a GUI interface for manual operations and automated background processing with configurable scheduling.

## Features

### Core Functionality

- **CSV Export**: Downloads SQL files, executes queries, generates CSV files with exact legacy format
- **Email Delivery**: Sends CSV attachments with configurable SMTP settings
- **API Integration**: Exports price data to external APIs with proper authentication
- **HTTP Server**: Receives rate data via HTTP endpoints and processes to Oracle database
- **Task Scheduling**: Automated execution at configurable times (default: 08:00, 12:00, 15:00, 18:00, 20:00)
- **Sequential Processing**: Single-worker queue ensures no parallel execution conflicts
- **Comprehensive Logging**: Unified log file with component prefixes for easy filtering

### GUI Interface

- **Settings Tab**: Configure all application settings
- **Export Mailer Tab**: Manual CSV export and email operations
- **Price Export Tab**: Manual price export to API
- **Rate Loader Tab**: HTTP server control and monitoring
- **Logs Tab**: Real-time log viewing with auto-refresh

### Error Handling & Resilience

- Graceful error handling with comprehensive logging
- Application stays alive through all error conditions
- Automatic retry mechanisms for network operations
- Resource cleanup and memory management

## Installation

### Option 1: Standalone Executable (Recommended)

#### Prerequisites

- Windows operating system (Windows 7 or later)
- Oracle Client 32-bit (for database connectivity)
- 2GB RAM minimum
- 200MB disk space

#### Setup Instructions

1. **Download** the `rateloader-gui.exe` file
2. **Extract** to your desired directory (e.g., `C:\RateLoader\`)
3. **Copy** `settings.ini` to the same directory as the .exe (included in distribution)
4. **Run the application**:
   ```bash
   rateloader-gui.exe
   ```

The executable includes all Python dependencies and requires no separate Python installation.

#### First Run

On first run, the application will automatically:

- Create `log/` directory next to the executable
- Initialize all required components
- Load configuration from `settings.ini`

### Option 2: Python Source Installation

#### Prerequisites

- Python 3.8 (32-bit) for Oracle compatibility
- cx_Oracle 7.3 for Oracle database connectivity
- Windows operating system

#### Setup Instructions

1. **Extract the application** to your desired directory
2. **Install Python dependencies**:
   ```bash
   pip install cx_Oracle==7.3 requests python-dateutil
   ```
3. **Run the application**:
   ```bash
   python main.py
   ```

## Configuration

### Settings.ini Structure

The application uses `settings.ini` for configuration. All required keys are auto-created with defaults if missing.

```ini
[settings]
# Oracle Configuration
oracleconnectstring = v8live/live@v8
nls_lang = AMERICAN_AMERICA.CL8MSWIN1251
file_encoding = cp1251

# ExportMailer Configuration
hotel_key = HOTEL123
url = http://fidelio.pro/select.sql
block-url = http://fidelio.pro/selectBlocks.sql
sendfrom = folkote@yandex.ru
sendto = metraplex@gmail.com
mailserver = smtp.yandex.ru
port = 465
pwd = hvlmwcrnbafsavjy
ssl = 1

# PriceExport Configuration
api_url = https://app.hotellab.io/privat/api/integration/1c/set-pms-prices/5
token = 1111
months = 12

# RateLoader Configuration
server_port = 8000

# Scheduler Configuration
schedule_hours = 8,12,15,18,20
```

### Configuration Details

| Setting               | Description                               | Default                                                              |
| --------------------- | ----------------------------------------- | -------------------------------------------------------------------- |
| `hotel_key`           | Hotel identifier for CSV filenames        | `HOTEL123`                                                           |
| `oracleconnectstring` | Oracle database connection string         | `v8live/live@v8`                                                     |
| `nls_lang`            | Oracle NLS language setting               | `AMERICAN_AMERICA.CL8MSWIN1251`                                      |
| `file_encoding`       | CSV file encoding                         | `cp1251`                                                             |
| `url`                 | Main SQL file URL                         | `http://fidelio.pro/select.sql`                                      |
| `block-url`           | Blocks SQL file URL                       | `http://fidelio.pro/selectBlocks.sql`                                |
| `sendfrom`            | Email sender address                      | `folkote@yandex.ru`                                                  |
| `sendto`              | Email recipient address                   | `metraplex@gmail.com`                                                |
| `mailserver`          | SMTP server address                       | `smtp.yandex.ru`                                                     |
| `port`                | SMTP port                                 | `465`                                                                |
| `pwd`                 | Email password                            | `hvlmwcrnbafsavjy`                                                   |
| `ssl`                 | Use SSL/TLS for email (0/1)               | `1`                                                                  |
| `api_url`             | Price export API URL                      | `https://app.hotellab.io/privat/api/integration/1c/set-pms-prices/5` |
| `token`               | API authentication token                  | `1111`                                                               |
| `months`              | Number of months to export                | `12`                                                                 |
| `server_port`         | HTTP server port                          | `8000`                                                               |
| `schedule_hours`      | Comma-separated hours for scheduled tasks | `8,12,15,18,20`                                                      |

## Usage

### Standalone Executable Usage

#### GUI Mode

Launch the GUI interface:

```bash
rateloader-gui.exe
```

The GUI provides tabs for:

- **Rate Loader**: Start/stop HTTP server and monitor status
- **Export Mailer**: Run CSV export and email operations manually
- **Price Export**: Run price export to API manually
- **Settings**: Configure all application parameters
- **Logs**: View real-time logs from all components

#### Directory Structure

When using the standalone executable, ensure this directory structure:

```
RateLoader/
├── rateloader-gui.exe    # Main executable
├── settings.ini          # Configuration file (required)
└── log/                  # Log directory (created automatically)
    └── rateloader.log     # Unified log file with component prefixes
```

### Python Source Usage

#### GUI Mode

Launch the GUI interface:

```bash
python gui/app.py
```

#### Background Mode

Run in background mode (no GUI):

```bash
python main.py
```

This starts:

- Task queue with single worker
- Scheduler for automated execution
- HTTP server for rate loading
- All logging systems

### HTTP API Endpoints

When the HTTP server is running, these endpoints are available:

#### GET /ping

Health check endpoint

```json
{ "status": "ok" }
```

#### GET /status

System status endpoint

```json
{
  "server": "running",
  "timestamp": 1234567890,
  "task_queue": {...},
  "scheduler": {...},
  "oracle_connected": true
}
```

#### POST /load

Rate data submission endpoint

```json
{
	"prices": [
		{
			"date": "2023-01-01",
			"category": "STD",
			"ratecode": "RACK",
			"price": 100.0,
			"guests": 2,
			"children_rates": [
				{
					"price": 50.0,
					"age_category": "child"
				}
			]
		}
	]
}
```

## File Formats

### CSV Export Format

- **Main Export**: `<HOTEL_KEY>.csv`
- **Blocks Export**: `BLOCKS-<HOTEL_KEY>.csv`
- **Content**: One column per line from query's first column
- **Encoding**: Configurable (default: cp1251)

### Hotel Key Configuration

**Important Change**: The hotel key is now configured in `settings.ini` instead of being retrieved from the database.

- **Setting**: `hotel_key` in `[settings]` section
- **Default**: `HOTEL123` (placeholder - should be updated to your actual hotel key)
- **Purpose**: Used for CSV filenames (`<HOTEL_KEY>.csv`, `BLOCKS-<HOTEL_KEY>.csv`) and email subjects

**Migration Note**: Previously, the hotel key was retrieved from the database using the query:

```sql
select wuss_value from wuss where upper(wuss_name) = upper('UDEF_Hotellabkey')
```

This has been replaced with a configuration setting for better reliability and easier management. Please update the `hotel_key` value in your `settings.ini` to match your actual hotel identifier.

- **Overwrite**: Files are overwritten on each run (no timestamps)

### JSON Rate Data Format

```json
{
	"prices": [
		{
			"date": "YYYY-MM-DD",
			"category": "CATEGORY_CODE",
			"ratecode": "RATE_CODE",
			"price": 100.0,
			"guests": 2,
			"children_rates": [
				{
					"price": 50.0,
					"age_category": "child"
				}
			]
		}
	]
}
```

## Scheduling

### Default Schedule

The application runs scheduled tasks at:

- 08:00
- 12:00
- 15:00
- 18:00
- 20:00

### Task Order

At each scheduled time:

1. **ExportMailer** (CSV export and email)
2. **PriceExport** (API export)

### Custom Schedule

Modify `schedule_hours` in settings.ini:

```ini
schedule_hours = 9,15,21  # Custom hours
```

## Logging

### Unified Log File

A single unified log file is created in the `log/` directory:

- **`rateloader.log`** - All application operations with component prefixes

### Component Prefixes

The unified log uses component prefixes for easy filtering:

- **`[EXPORT]`** - CSV/email export operations
- **`[PRICE]`** - API export operations
- **`[RATELOADER]`** - Core application operations
- **`[GUI]`** - GUI interface operations
- **`[ORACLE]`** - Database operations
- **`[SCHEDULER]`** - Task scheduling operations
- **`[HTTP]`** - HTTP server operations
- **`[QUEUE]`** - Task queue operations

### Log Rotation

- Files rotate when reaching 5MB
- 5 backup files are kept
- Encoding: UTF-8

### Log Format

```
YYYY-MM-DD HH:MM:SS - rateloader - LEVEL - [COMPONENT] Message
```

### Example Log Output

```
2023-10-23 14:30:15 - rateloader - INFO - [EXPORT] CSV export process started
2023-10-23 14:30:16 - rateloader - INFO - [ORACLE] Connected to database
2023-10-23 14:30:17 - rateloader - INFO - [EXPORT] Main export completed successfully
2023-10-23 14:30:18 - rateloader - INFO - [PRICE] API export process started
2023-10-23 14:30:19 - rateloader - ERROR - [PRICE] API request failed: Connection timeout
```

## Error Handling

### Resilience Features

- All exceptions are caught and logged
- Application continues running after errors
- Failed tasks are logged but don't stop the queue
- Network operations have timeout and retry logic
- Database connections are properly managed

### Common Error Scenarios

- **Network failures**: Logged and retried where appropriate
- **Database errors**: Logged, transactions rolled back
- **File I/O errors**: Logged, partial files handled safely
- **Invalid data**: Logged, processing continues with next item

## Testing

### Running Tests

The application includes comprehensive integration tests:

```bash
# Run all integration tests
python test_full_integration.py

# Run GUI tests
python test_gui_integration.py

# Run scheduler tests
python test_scheduler_integration.py

# Run error handling tests
python test_error_handling.py
```

### Test Coverage

- PRD acceptance criteria validation
- End-to-end workflow testing
- GUI functionality testing
- Error handling validation
- Performance and stability testing

## Troubleshooting

### Standalone Executable Issues

#### Application Won't Start

- Ensure `settings.ini` is in the same directory as `rateloader-gui.exe`
- Check Windows Event Viewer for application errors
- Verify you have write permissions for the log directory
- Run as Administrator if permission issues occur

#### Oracle Connection Problems

- Verify Oracle client is installed (32-bit)
- Check connection string format: `user/pass@dsn`
- Ensure NLS_LANG is set correctly in settings.ini
- Verify database is accessible from your network
- Check Oracle client PATH environment variable

#### Common Issues

#### Email Sending Issues

- Check SMTP server settings in settings.ini
- Verify authentication credentials
- Test SSL/TLS settings (ssl=0 or 1)
- Check firewall/antivirus blocking

#### HTTP Server Issues

- Verify port is not in use (check with `netstat -an | findstr :8000`)
- Check Windows firewall settings
- Ensure no other application using the port

#### Performance Issues

- Check log file sizes in log/ directory (rotation should handle this)
- Monitor memory usage with Task Manager
- Verify Oracle connection pooling

#### Executable-Specific Issues

- **"DLL not found" errors**: Install Oracle Client 32-bit
- **"Access denied" errors**: Run as Administrator or check folder permissions
- **"Settings not found" errors**: Ensure settings.ini is in same directory as .exe
- **"Cannot create log directory"**: Check write permissions

### Debug Mode

Enable debug logging by checking log files in the `log/` directory for detailed error information. The executable creates comprehensive logs for all components.

## Development

### Project Structure

```
rateloader/
├── core/                   # Core components
│   ├── config.py          # Configuration management
│   ├── logger.py          # Logging system
│   ├── oracle.py          # Oracle database wrapper
│   ├── task_queue.py      # Task queue system
│   ├── scheduler.py       # Task scheduler
│   ├── http_server.py     # HTTP server
│   ├── mailer.py          # Email functionality
│   └── tasks.py           # Task definitions
├── features/              # Feature implementations
│   ├── export_mailer.py   # CSV export and email
│   ├── price_export.py    # API price export
│   └── rate_loader_logic.py # Rate processing logic
├── gui/                   # GUI interface
│   └── app.py            # Main GUI application
├── log/                   # Log files directory
├── docs/                  # Documentation
├── test_*.py             # Test files
├── main.py               # Main entry point
└── settings.ini          # Configuration file
```

### Adding New Features

1. Create feature module in `features/`
2. Create task definition in `core/tasks.py`
3. Add GUI controls if needed
4. Write comprehensive tests
5. Update documentation

## System Requirements

### Minimum Requirements

- Windows 7 or later
- Python 3.8 (32-bit)
- 512MB RAM
- 50MB disk space

### Recommended Requirements

- Windows 10 or later
- Python 3.8 (32-bit)
- 2GB RAM
- 200MB disk space
- Oracle Client 32-bit

### Dependencies

- cx_Oracle 7.3
- requests
- python-dateutil
- tkinter (included with Python)

## License

This software is proprietary and confidential. All rights reserved.

## Support

For technical support:

1. Check the troubleshooting section
2. Review log files for error details
3. Run integration tests to diagnose issues
4. Contact support team with detailed error information

## Version History

### v3.0.0 (Current)

- Unified three legacy utilities into single application
- Added GUI interface with tabs
- Implemented task queue and scheduler
- Added comprehensive error handling
- Created integration test suite
- Full PRD compliance

### Legacy Versions

- hotellabexport.py - CSV export utility
- main_priceexport.py - Price export utility
- main.py - Rate loader HTTP server

---

**RateLoader v3.0** - Complete hotel rate management solution
