# Example Templates Documentation

**Last Updated:** 2025-11-20

## Overview

This document provides comprehensive documentation for the example templates included in the Ordio template generator system. These templates serve as reference implementations showcasing best practices, advanced features, and production-quality patterns.

## Available Example Templates

### 1. Schichtplan Premium Vorlage (shift-planning-advanced.json)

**Category:** Shift Planning  
**Version:** 2.0.0  
**Complexity:** Advanced

**Description:**

A premium shift planning template with comprehensive features including analytics, charts, compliance checks, and advanced calculations. This template demonstrates production-quality patterns for complex workforce management scenarios.

**Key Features:**

- **5 Sheets:** Instructions, Employee Data, Shift Plan, Calculations, Compliance, Analytics
- **Data Validation:** Minimum wage checks, working hours limits, department dropdowns
- **Conditional Formatting:** Status indicators, visual feedback
- **Charts:** Bar chart for weekly costs, pie chart for cost distribution
- **Compliance Checks:** ArbZG compliance (max hours, breaks), minimum wage validation
- **Named Ranges:** For better formula readability
- **Error Handling:** All formulas wrapped in WENNFEHLER
- **German Locale:** All text and formulas in German

**Usage Instructions:**

1. **Setup:**

   - Open the template in Excel
   - Navigate to "Mitarbeiter" sheet
   - Enter employee data (name, hourly rate, weekly hours, department)

2. **Planning:**

   - Go to "Schichtplan" sheet
   - Enter shift times for each employee and day
   - Format: "HH:MM-HH:MM" (e.g., "08:00-17:00")

3. **Review:**

   - Check "Compliance" sheet for automatic compliance checks
   - Review "Analytics" sheet for cost analysis and charts
   - Verify all compliance checks show "OK"

4. **Best Practices:**
   - Always verify compliance before finalizing schedule
   - Review analytics for cost optimization opportunities
   - Use data validation to prevent errors

**Advanced Features Demonstrated:**

- Multi-sheet organization
- Cross-sheet references
- Complex formulas with error handling
- Data validation with German error messages
- Conditional formatting for status indicators
- Charts for visualization (requires Python enhancement)
- Named ranges for maintainability
- Compliance automation

**Generation Command:**

```bash
php v2/systems/excel-template-generator/scripts/template-cli.php generate v2/systems/excel-template-generator/data/template-definitions/examples/shift-planning-advanced.json output.xlsx
```

**Enhancement Command (for charts):**

```bash
python3 v2/scripts/enhance-template.py output.xlsx enhancements.json enhanced.xlsx
```

### 2. Basic Templates

The following basic templates are also available as simpler examples:

- **shift-planning-basic.json** - Basic shift planning without advanced features
- **time-tracking-basic.json** - Simple time tracking template
- **payroll-basic.json** - Basic payroll calculations
- **employee-onboarding.json** - Employee onboarding checklist
- **performance-review.json** - Performance review template
- **vacation-planning.json** - Vacation planning with German compliance
- **training-schedule.json** - Training schedule template
- **compliance-audit.json** - Compliance audit checklist

## Template Features Reference

### Sheet Types

**Instructions Sheet:**

- Clear title
- Step-by-step instructions
- Examples
- Troubleshooting tips

**Data Entry Sheet:**

- Clear labels
- Input cells with validation
- Grouped fields
- Visual separation

**Calculation Sheet:**

- Separated from data entry
- Documented formulas
- Protected cells
- Named ranges

**Report Sheet:**

- Summary metrics
- Charts (via Python enhancement)
- Tables
- Analysis

**Compliance Sheet:**

- Automatic compliance checks
- Status indicators
- Conditional formatting
- German HR compliance

**Analytics Sheet:**

- Key metrics
- Charts
- Visualizations
- Insights

### Advanced Features

**Charts:**

Charts are defined in the template JSON but require Python enhancement to be added to the Excel file:

```json
{
  "charts": [
    {
      "type": "bar",
      "title": "Chart Title",
      "data_range": "Sheet1!A1:B10",
      "category_labels": "Sheet1!A1:A10",
      "position": {
        "cell": "D3",
        "width": 400,
        "height": 250
      },
      "color_palette": "professional"
    }
  ]
}
```

**Supported Chart Types:**

- `bar` - Bar chart
- `line` - Line chart
- `pie` - Pie chart
- `scatter` - Scatter plot
- `area` - Area chart
- `column` - Column chart

**Data Validation:**

```json
{
  "data_validation": [
    {
      "range": "A2:A10",
      "type": "decimal",
      "operator": "greaterThanOrEqual",
      "formula1": "12.82",
      "error_message": "Stundenlohn muss mindestens €12,82 betragen",
      "input_message": "Geben Sie einen Stundenlohn ein"
    }
  ]
}
```

**Conditional Formatting:**

```json
{
  "conditional_formatting": [
    {
      "range": "B2:B10",
      "type": "cellValue",
      "operator": "equal",
      "formula": "\"OK\"",
      "style": {
        "fill": { "color": "#ECFDF5" },
        "font": { "color": "#10B981", "bold": true }
      }
    }
  ]
}
```

**Named Ranges:**

```json
{
  "named_ranges": [
    {
      "name": "Mindestlohn",
      "range": "A1",
      "scope": "Sheet1",
      "description": "Aktueller Mindestlohn"
    }
  ]
}
```

## Creating Your Own Templates

### Step 1: Start with Metadata

```json
{
  "metadata": {
    "name": "Your Template Name",
    "category": "shift_planning",
    "version": "1.0.0",
    "description": "Template description",
    "author": "Ordio GmbH",
    "created_date": "2025-11-20",
    "use_cases": ["Use case 1", "Use case 2"],
    "tags": ["tag1", "tag2"]
  }
}
```

### Step 2: Define Sheets

Follow the standard sheet order:

1. Instructions
2. Overview/Dashboard
3. Data Entry
4. Calculations
5. Reports
6. Reference (optional)

### Step 3: Add Features

- Data validation for input cells
- Conditional formatting for status indicators
- Named ranges for complex formulas
- Error handling (WENNFEHLER) for all formulas
- Compliance checks where applicable

### Step 4: Generate and Enhance

```bash
# Generate base template
php v2/systems/excel-template-generator/scripts/template-cli.php generate template.json output.xlsx

# Enhance with charts (if needed)
python3 v2/scripts/enhance-template.py output.xlsx enhancements.json enhanced.xlsx

# Validate
php v2/systems/excel-template-generator/scripts/template-cli.php validate template.json enhanced.xlsx

# Score quality
php v2/systems/excel-template-generator/scripts/template-cli.php score template.json --benchmark
```

## Quality Standards

All example templates meet the following quality standards:

- ✅ Quality score ≥ 80%
- ✅ All formulas have error handling
- ✅ Data validation rules present
- ✅ German locale used
- ✅ Compliance checks included
- ✅ Instructions sheet provided
- ✅ Ordio branding applied
- ✅ Named ranges used for complex formulas
- ✅ Conditional formatting for visual feedback

## Testing Example Templates

To test all example templates:

```bash
php v2/scripts/test-template-quality.php --all --verbose
```

To test a specific template:

```bash
php v2/scripts/test-template-quality.php --template=v2/systems/excel-template-generator/data/template-definitions/examples/shift-planning-advanced.json --verbose
```

## Best Practices Demonstrated

1. **Separation of Concerns:** Data entry separate from calculations
2. **Error Handling:** All formulas wrapped in WENNFEHLER
3. **Data Validation:** Input validation with German error messages
4. **Visual Feedback:** Conditional formatting for status indicators
5. **Documentation:** Clear instructions and formula documentation
6. **Compliance:** Automatic compliance checks
7. **Maintainability:** Named ranges for complex references
8. **User Experience:** Clear labels, organized layout, helpful messages

## Next Steps

1. Review example templates to understand patterns
2. Use as reference when creating new templates
3. Follow the same structure and quality standards
4. Enhance with Python for advanced features (charts, pivot tables)
5. Test thoroughly before publishing

## Support

For questions about example templates:

1. Review this documentation
2. Check the AI workflow guide: `docs/systems/excel-generator/AI_AGENT_WORKFLOW.md`
3. Review best practices: `docs/systems/excel-generator/BEST_PRACTICES_GUIDE.md`
4. Check quick reference: `docs/systems/excel-generator/QUICK_REFERENCE.md`