# Crystal Reports Scheduler - Web Application

Enterprise-grade web-based Crystal Reports scheduling solution for IFS ERP environments.

## Features

### Core Functionality
- **Web-based Report Management**: Upload, configure, and schedule Crystal Reports (.rpt files)
- **Oracle Cloud Database Integration**: Connect to Oracle databases hosted in cloud environments
- **Automated Parameter Detection**: Automatically read and expose Crystal Reports parameters
- **Flexible Scheduling**: Cron-based scheduling with multiple frequency options
- **Multi-format Output**: PDF, Excel, Word, CSV export capabilities
- **Dual Delivery Options**: Email delivery and network/local drive storage

### Advanced Features
- **Parameter Management**: Dynamic parameter configuration with data type validation
- **Email Templates**: Customizable email templates with report attachments
- **Audit Trail**: Complete logging of report executions and deliveries
- **Role-based Access**: User authentication and permission management
- **Dashboard Analytics**: Report execution statistics and performance monitoring

## Architecture

### Technology Stack
- **Frontend**: React.js with Material-UI/Ant Design
- **Backend**: Node.js with Express.js
- **Database**: PostgreSQL (metadata) + Oracle (reporting data)
- **Report Engine**: Crystal Reports .NET SDK / SAP Crystal Reports Server
- **Authentication**: JWT-based auth with role management
- **Scheduling**: node-cron with persistent job storage
- **File Storage**: Local filesystem or network drives
- **Email**: Nodemailer with SMTP support

### System Components
```
┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   Web Frontend  │    │  API Gateway    │    │ Report Engine   │
│   (React.js)    │◄──►│  (Express.js)   │◄──►│ (Crystal SDK)   │
└─────────────────┘    └─────────────────┘    └─────────────────┘
                               │                       │
                               ▼                       ▼
                    ┌─────────────────┐    ┌─────────────────┐
                    │   Metadata DB   │    │   Oracle Cloud  │
                    │  (PostgreSQL)   │    │   (IFS Database) │
                    └─────────────────┘    └─────────────────┘
                               │
                               ▼
                    ┌─────────────────┐    ┌─────────────────┐
                    │  Job Scheduler  │    │  File Storage   │
                    │  (node-cron)    │◄──►│ (Local/Network) │
                    └─────────────────┘    └─────────────────┘
                               │
                               ▼
                    ┌─────────────────┐
                    │  Email Service  │
                    │  (Nodemailer)   │
                    └─────────────────┘
```

## Implementation Plan

### Phase 1: Core Infrastructure (Week 1-2)
1. **Project Setup**
   - Initialize React frontend with TypeScript
   - Setup Express.js API server
   - Configure PostgreSQL metadata database
   - Basic authentication system

2. **Database Schema Design**
   - Reports table (metadata, file paths, parameters)
   - Schedules table (cron expressions, delivery options)
   - Users and permissions tables
   - Execution history and audit logs

### Phase 2: Report Management (Week 3-4)
1. **Crystal Reports Integration**
   - .NET Core wrapper for Crystal Reports SDK
   - Report file upload and validation
   - Parameter extraction and metadata parsing
   - Test report rendering pipeline

2. **Oracle Database Connectivity**
   - Connection pool management
   - Dynamic connection string configuration
   - Database authentication and security
   - Connection testing utilities

### Phase 3: Scheduling Engine (Week 5-6)
1. **Job Scheduler Implementation**
   - Persistent cron job storage
   - Dynamic job creation/modification
   - Error handling and retry logic
   - Performance monitoring

2. **Parameter Management System**
   - Dynamic parameter form generation
   - Data type validation (string, number, date, boolean)
   - Default value management
   - Parameter dependency handling

### Phase 4: Delivery Systems (Week 7-8)
1. **Email Delivery**
   - SMTP configuration management
   - Template system for email bodies
   - Attachment handling (PDF generation)
   - Delivery confirmation tracking

2. **File System Delivery**
   - Local and network drive support
   - Directory structure management
   - File naming conventions
   - Access permission validation

### Phase 5: User Interface (Week 9-10)
1. **Report Management UI**
   - Report upload interface
   - Parameter configuration forms
   - Schedule creation wizard
   - Execution history viewer

2. **Dashboard and Monitoring**
   - Real-time job status display
   - Performance analytics
   - Error reporting and alerts
   - System health monitoring

## Technical Specifications

### Crystal Reports Integration
```csharp
// .NET Core API wrapper for Crystal Reports
public class CrystalReportService
{
    public async Task<ReportMetadata> ExtractParametersAsync(string reportPath)
    public async Task<byte[]> GenerateReportAsync(ReportRequest request)
    public async Task<bool> ValidateReportAsync(string reportPath)
}
```

### Database Schema
```sql
-- Reports table
CREATE TABLE reports (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    parameters JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Schedules table
CREATE TABLE schedules (
    id SERIAL PRIMARY KEY,
    report_id INTEGER REFERENCES reports(id),
    name VARCHAR(255) NOT NULL,
    cron_expression VARCHAR(100) NOT NULL,
    parameters JSONB,
    delivery_config JSONB,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);
```

### API Endpoints
```typescript
// Report Management
POST   /api/reports                 // Upload new report
GET    /api/reports                 // List all reports
GET    /api/reports/:id/parameters  // Get report parameters
DELETE /api/reports/:id             // Delete report

// Schedule Management
POST   /api/schedules               // Create new schedule
GET    /api/schedules               // List all schedules
PUT    /api/schedules/:id           // Update schedule
DELETE /api/schedules/:id           // Delete schedule

// Execution Management
GET    /api/executions              // List execution history
POST   /api/executions/run/:id      // Manual execution trigger
GET    /api/executions/:id/logs     // Get execution logs
```

## Deployment Architecture

### Production Environment
- **Application Server**: Linux-based (Ubuntu/CentOS)
- **Web Server**: Nginx reverse proxy
- **Process Manager**: PM2 for Node.js applications
- **Database**: PostgreSQL 13+ with connection pooling
- **File Storage**: Network-attached storage (NAS) or cloud storage
- **Monitoring**: Prometheus + Grafana for metrics
- **Logging**: ELK stack (Elasticsearch, Logstash, Kibana)

### Security Considerations
- **Authentication**: JWT tokens with refresh mechanism
- **Authorization**: Role-based access control (RBAC)
- **Database Security**: Encrypted connections, least-privilege access
- **File Security**: Sandboxed report execution environment
- **Network Security**: VPN access for Oracle cloud connections
- **Audit Logging**: Complete audit trail for compliance

## IFS Integration Benefits

### Manufacturing Reporting
- **Production Reports**: Scheduled shop floor reports with real-time data
- **Inventory Analysis**: Automated inventory level reports to management
- **Quality Metrics**: Daily/weekly quality control report distribution
- **Financial Reporting**: Month-end financial reports to stakeholders

### Operational Efficiency
- **Reduced Manual Work**: Eliminate manual report generation and distribution
- **Consistent Delivery**: Ensure reports reach recipients on schedule
- **Parameter Automation**: Pre-configure common parameter combinations
- **Error Reduction**: Automated validation and error handling

This solution positions xcey IFS Consulting as a provider of enterprise-grade reporting infrastructure for IFS clients, addressing a common pain point in manufacturing environments where timely, accurate reporting is critical for operations.