L'Oréal OMG Assistant Global

A comprehensive PHP web application for L'Oréal campaign asset management with two main tools:

1. Master Global Asset Submission - Submit Box assets with metadata extraction
2. Global to Local - Transform global campaign CSVs into regional market CSVs

Features

Master Global Asset Submission

• Box API Integration: Real-time validation and preview of Box folders
• Auto-population: Master Campaign Number automatically retrieved from Box folder hierarchy
• Nested Folder Display: Recursive 3-level deep folder structure with expand/collapse
• Folder Validation: Ensures folder is named "SUPPLIED_ASSETS"
• Webhook Integration: Submit data to Make.com with response handling

Global to Local CSV Transformation

• Multi-Stage Processing: Visual progress tracker through 6 processing stages
• 16 Regional Markets: Automatically creates CSV files for 16 ISO codes (en-GB, es-ES, pt-PT, etc.)
• OMG Integration: Looks up business unit from OMG API (optional for testing)
• Date Transformation: Parses dates, adds 1 month, formats DD/MM/YYYY
• Preview All Files: Dropdown selector to preview each of 16 CSVs individually
• Download Options: Download individual files or all 16 as ZIP
• Error Reporting: Detailed error messages at each stage with actionable guidance
• Email Notifications: Mailgun integration for process notifications

Activity Logs & Reporting

• Comprehensive Logging: All actions logged to JSON file with full audit trail
• Activity Viewer: Web-based log viewer with statistics dashboard
• Filterable Reports: Filter by action type, user, or time period
• Export to CSV: Download logs for external analysis
• Statistics: Total actions, success rates, error tracking, unique users
• Detailed Data: Every log entry includes timestamp, user, status, IP, user agent, and action-specific data

Shared Features

• SSO Authentication: Microsoft Azure AD SSO with local development mode
• User Tracking: Email tracking for all submissions
• Email Notifications: SMTP via Mailgun for process notifications (start, complete, errors)
• Responsive Design: Modern UI with Montserrat font and L'Oréal brand colors (Black #000000, Yellow #FFC407)
• Comprehensive Error Handling: Clear error messages with actions at every step
• Audit Trail: Complete activity logging for compliance and reporting

Requirements

• PHP 7.4 or higher
• Composer
• Box JWT credentials
• Web server (Apache/Nginx) or MAMP for local development

Installation

1. Install Dependencies

composer install

2. Configuration

The application uses config.php for all configuration:

Local Development Mode (Default)

'sso' => [
    'enabled' => false,  // Local mode
    'local_user' => [
        'name' => 'Dave Porter',
        'email' => 'daveporter@oliver.agency'
    ]
]

Production Mode with SSO

'sso' => [
    'enabled' => true,  // Enable SSO
    'tenant_id' => 'your-azure-tenant-id',
    'client_id' => 'your-azure-client-id'
]

3. Box JWT Configuration

The Box JWT configuration file (43984435_77m2ujl3_config.json) must be present in the root directory. This file is already configured and should not be committed to version control (it's in .gitignore).

4. Webhook Configuration

The webhook is pre-configured in config.php:

'webhook' => [
    'url' => 'https://hook.us1.make.celonis.com/ddxrhuykysnbxqvb25uxsg0pjngqytiv',
    'api_key' => 'E4P9923eBaUTKrEr.iqvHtVHcZ6L!WH'
]

5. Global to Local Configuration

Configure regional transformation settings in config.php:

'global_to_local' => [
    'output_box_folder_id' => 'XXXXXXXXX',  // Set Box output folder ID
    'max_file_size' => 5242880,  // 5MB max
    'iso_codes' => [  // 16 target markets (editable)
        'en-GB', 'es-ES', 'pt-PT', 'en-IE', 'fr-CH', 'de-AT',
        'de-DE', 'cs-CZ', 'hu-HU', 'sk-SK', 'da-DK', 'fi-FI',
        'nb-NO', 'sv-SE', 'en-NN', 'de-CH'
    ],
    'business_unit_map' => [  // 19 business unit mappings
        'VICHY' => 'VICHY',
        'LA ROCHE' => 'LA ROCHE',
        // ... see config.php for complete list
    ]
]

6. OMG API Configuration

For automatic business unit lookup from campaign data:

'omg_api' => [
    'enabled' => false,  // Set to true when API key has permissions
    'base_url' => 'https://api2.omg.oliver.solutions/loreal/v1',
    'api_key' => 'YOUR_OMG_API_KEY',  // API key configured
    'timeout' => 60,
    'fallback_business_unit' => 'TESTING'  // Used when disabled
]

Current Status: OMG API configured but disabled due to 403 permission error. Using fallback "TESTING" business unit.

To Enable:

1. Request API key permissions for /loreal/v1/getProject endpoint
2. Set 'enabled' => true in config.php
3. OMG API will automatically lookup business unit from campaign number

7. Email Notifications

SMTP configured via Mailgun for email notifications:

'email' => [
    'enabled' => true,
    'service' => 'smtp',  // Using SMTP
    'from' => 'twist@mail.dev.oliver.solutions',
    'smtp_host' => 'smtp.mailgun.org',
    'smtp_port' => 587,
    'smtp_username' => 'twist@mail.dev.oliver.solutions',
    'smtp_password' => 'CONFIGURED'  // Already set
]

Notifications Sent To: Logged-in user's email (from SSO or local config)

Email Types:

• Process Started: When CSV upload begins
• Process Completed: When all files uploaded to Box (includes file count)
• Errors: When processing fails (includes error details)

Project Structure

Loreal-master-Entry/
├── config.php                      # Application configuration
├── header.php                      # Shared navigation header
├── .htaccess                       # Security configuration
│
# Authentication & Services
├── AuthMiddleware.php              # SSO authentication handler
├── BoxService.php                  # Box API integration
├── OMGService.php                  # OMG API integration
├── EmailService.php                # Email notifications (SMTP/Mailgun)
├── ApplicationLogger.php           # Activity logging system
├── CSVTransformer.php              # CSV transformation logic
│
# Master Global Asset Submission (Tab 1)
├── index.php                       # Main asset submission page
├── validate-box.php                # AJAX: Box ID validation
├── submit.php                      # Form submission + webhook
│
# Global to Local (Tab 2)
├── global-to-local.php             # CSV upload page
├── process-csv.php                 # CSV processing endpoint
├── upload-to-box.php               # Upload CSVs to Box
├── get-csv-preview.php             # Preview specific CSV file
├── download-csv.php                # Download single CSV
├── download-all-csv.php            # Download all CSVs as ZIP
│
# Activity Logs (Tab 3)
├── logs-viewer.php                 # Log viewer and reporting
│
# Assets
├── css/
│   └── styles.css                  # Application styles
├── js/
│   ├── app.js                      # Asset submission frontend
│   └── global-to-local.js          # Global to local frontend
│
# Data & Logs
├── logs/                           # Application logs (not in git)
│   ├── .gitkeep                    # Preserve directory
│   └── application.log             # Activity log (JSON)
├── vendor/                         # Composer dependencies
└── 43984435_77m2ujl3_config.json   # Box JWT config (not in git)

Usage

Local Development (MAMP)

1. Place the project in your MAMP htdocs directory
2. Ensure config.php has 'enabled' => false for SSO
3. Access via http://localhost:8888/Loreal-master-Entry/

Production Deployment

1. Upload to Apache server
2. Update config.php:
   • Set 'enabled' => true for SSO
   • Add Azure tenant and client IDs
   • Set 'debug' => false
3. Ensure .htaccess is configured if needed
4. Verify Box JWT config file is present

Workflow Overview

This application streamlines the process of submitting L'Oréal campaign assets stored in Box by automatically extracting metadata and sending it to downstream workflows via Make.com webhook.

Purpose

When campaign assets are uploaded to Box, this tool:

1. Validates the folder structure
2. Extracts campaign metadata from Box folder hierarchy
3. Catalogs all submitted assets (files and folders)
4. Sends structured data to Make.com for further processing
5. Tracks submission dates and user information

Box Folder Structure Requirements

The application expects a specific Box folder hierarchy:

📁 [Campaign Number] (e.g., "123456")          ← Master Campaign Number (extracted)
   └─ 📁 CAMPAIGN_ASSETS
      └─ 📁 SUPPLIED_ASSETS                    ← User submits this folder's Box ID
         ├─ 📁 Images/
         ├─ 📁 Videos/
         └─ 📄 files...

Critical Validation: The submitted folder MUST be named "SUPPLIED_ASSETS" or the workflow will fail.

Data Extraction

The application extracts and processes the following data:

From Box API:

• Master Campaign Number: Folder name two levels up (the campaign number folder)
• Master Campaign ID: Box folder ID two levels up
• Folder Structure: Complete recursive listing of all files and subfolders (up to 3 levels deep)
• Asset Count: Total number of files and folders

From User Input:

• Box ID: The Box folder ID for SUPPLIED_ASSETS
• Supply Date: When assets were supplied (formatted as DD/MM/YYYY 00:00)
• Live Date: Campaign go-live date (formatted as DD/MM/YYYY 00:00)
• End Date: Campaign end date (formatted as DD/MM/YYYY 00:00)

From System:

• User Email: Email of the person submitting (from SSO or local config)
• User Name: Full name of submitter
• Submission Timestamp: When the form was submitted

Complete Workflow Steps

Step 1: User Authentication

• Local Mode: Automatically authenticated as daveporter@oliver.agency
• Production Mode: User logs in with Microsoft Azure AD SSO

Step 2: Box ID Entry

1. User navigates to the SUPPLIED_ASSETS folder in Box
2. User copies the Box folder ID from the URL
3. User enters the Box ID into the form
4. User clicks "Lookup" button

Step 3: Box Validation (validate-box.php)

1. Authenticate with Box: Generate JWT token using Box app credentials
2. Retrieve Folder Info: GET /folders/{boxId} from Box API
3. Validate Folder Name: Check if folder name is "SUPPLIED_ASSETS" (case-insensitive)
   • ❌ If not: Stop immediately with error message
   • ✅ If valid: Continue to next step
4. Get Parent Folder: Retrieve the parent of SUPPLIED_ASSETS (CAMPAIGN_ASSETS)
5. Get Grandparent Folder: Retrieve the parent of CAMPAIGN_ASSETS (Campaign Number)
6. Extract Campaign Data:
   • Master Campaign Number = Grandparent folder name
   • Master Campaign ID = Grandparent folder ID
7. Recursively Fetch Contents: Get all files/folders inside SUPPLIED_ASSETS (3 levels deep)
8. Return Preview Data: Send back to frontend for display

Step 4: Preview Display

Frontend displays in right column:

• Master Campaign Number
• Master Campaign ID
• Folder Name (SUPPLIED_ASSETS)
• Total item count
• Expandable/collapsible folder tree showing all nested contents

Step 5: Date Entry

User fills in three required dates:

• Supply Date (when assets were provided)
• Live Date (campaign launch)
• End Date (campaign finish)

Step 6: Form Submission (submit.php)

1. Validate Form Data: Ensure all fields are present
2. Format Dates: Convert to DD/MM/YYYY 00:00 format
3. Prepare Webhook Payload:

{
  "userEmail": "user@example.com",
  "userName": "John Doe",
  "boxId": "312657997260",
  "masterCampaignNumber": "123456",
  "masterCampaignId": "311743237672",
  "supplyDate": "24/03/2025 00:00",
  "liveDate": "31/03/2025 00:00",
  "endDate": "07/04/2025 00:00",
  "boxContents": {
    "folderName": "SUPPLIED_ASSETS",
    "totalItems": 45,
    "folders": [
      {
        "id": "123",
        "name": "Images",
        "type": "folder",
        "contents": {
          "folders": [...],
          "files": [...]
        }
      }
    ],
    "files": [
      {
        "id": "456",
        "name": "readme.txt",
        "type": "file"
      }
    ]
  },
  "submittedAt": "2025-11-17 19:58:45"
}

Step 7: Webhook Integration (Make.com)

1. Send to Make.com: POST to webhook URL with x-make-apikey header
2. Receive Response: Get status and any processing results
3. Display to User:
   • ✅ Success: "Submission successful" + webhook response
   • ❌ Error: Show error message with details

Data Flow Diagram

User (Browser)
    ↓
[1] Enter Box ID → Click "Lookup"
    ↓
validate-box.php
    ↓
Box API (JWT Auth)
    ↓ (Folder Info + Contents)
validate-box.php
    ↓ (JSON Response)
Browser Preview Panel
    ↓
[2] User Fills Dates → Click "Submit"
    ↓
submit.php
    ↓
Make.com Webhook
    ↓ (Response)
submit.php
    ↓ (Success/Error)
User Sees Result

What Happens After Submission

The webhook sends the structured data to Make.com, which can then:

1. Trigger downstream automation workflows
2. Update campaign management systems
3. Notify relevant team members
4. Archive submission records
5. Integrate with other L'Oréal systems

Error Handling

The application includes comprehensive validation at each step:

Box Validation Errors:

• Invalid Box ID (404)
• Folder not named "SUPPLIED_ASSETS"
• Box authentication failure
• Network/API errors

Form Validation Errors:

• Missing required fields
• Invalid date formats
• Box ID not validated before submission

Webhook Errors:

• Webhook unreachable
• Authentication failure (API key)
• Timeout (>30 seconds)
• Non-200 response codes

All errors are logged and displayed to the user with actionable messages.

Global to Local Workflow Details

Purpose

Transform a single global campaign CSV (with English language) into 16 separate regional CSVs, one for each target market.

Input CSV Requirements

• Format: Comma-delimited with headers
• Excel Compatibility: Supports Sep=, prefix (automatically removed)
• Required Columns: Number, Title, Category, Media, Supply date, Live date, End date, Language, Country
• Filename Pattern: Project_{CampaignNumber}_*.csv or *_{CampaignNumber}_*.csv

Transformation Process

Stage 1: Upload & Validate

• File type validation (.csv required)
• File size check (max 5MB)
• CSV structure validation

Stage 2: Parse CSV

• Remove Excel separator hint
• Parse headers and data rows
• Validate required columns exist

Stage 3: Extract Campaign Number

• Parse from filename (e.g., "Project_1601654_mediaBookings.csv" → "1601654")
• Validate numeric format

Stage 4: OMG API Lookup (Optional - Currently Skipped for Testing)

• Call OMG API: GET /loreal/v1/getProject?project_number={campaign}
• Extract business_area from response
• For testing: Uses "TESTING" as business unit

Stage 5: Map Business Unit

• Convert business area to short code (VICHY, LA ROCHE, LPP, etc.)
• 19 business unit mappings configured
• Falls back to "ERROR" if unrecognized

Stage 6: Transform Data

• Create 16 separate CSV files (one per ISO code)
• For each ISO code:
  • Copy all input rows
  • Replace Language field with ISO code (e.g., "en-GB")
  • Replace Country field with country code (e.g., "GB")
  • Transform dates: Parse → Add 1 month → Format DD/MM/YYYY
  • Keep all other fields intact

ISO Codes (16 Markets)

Configured in config.php:

en-GB, es-ES, pt-PT, en-IE, fr-CH, de-AT, de-DE, cs-CZ,
hu-HU, sk-SK, da-DK, fi-FI, nb-NO, sv-SE, en-NN, de-CH

Output Files

• File Count: 16 (one per ISO code)
• Naming Pattern: OMG{campaign}_GlobalACIngest_{BU}-{Country}_{timestamp}.csv
• Example: OMG1601654_GlobalACIngest_TESTING-GB_1700253845.csv
• Destination: Box folder configured in config.php (output_box_folder_id)

Preview & Download Options

Preview Features:

• Summary dashboard (input rows, output files, campaign info)
• Dropdown selector to view any of the 16 CSV files
• Shows ALL rows in scrollable table
• Real-time switching between files

Download Options:

• Download Current File: Download the currently previewed CSV
• Download All Files (ZIP): Get all 16 CSVs in a single ZIP archive
• Files available before committing to Box upload

User Approval Workflow

1. Upload CSV file
2. Watch progress through 6 automated stages
3. Review summary and any warnings
4. Preview each of the 16 output files
5. Download to verify (optional)
6. Click "Approve & Upload to OMG" to finalize
7. All 16 files uploaded to Box output folder

Error Handling Examples

Upload Error:

❌ File Validation Failed
Invalid file type. Expected .csv, got .xlsx
Action Required: → Please upload a valid CSV file (max 5MB)

Parse Error:

❌ CSV Parsing Failed
Missing required columns
Missing: Supply date, Live date, End date
Action Required: → Verify you uploaded the correct CSV file format

Campaign Error:

❌ Campaign Number Not Found in Filename
Expected pattern: *_CAMPAIGN#_*.csv, got: GlobalFile.csv
Action Required: → Rename file to include campaign number (e.g., Global_123456_Assets.csv)

Date Error:

❌ Data Transformation Failed
Row 5: Invalid Live date format - Unable to parse '32/13/2025'
Action Required: → Use format DD/MM/YYYY or DD MMM YYYY

All errors are logged and displayed to the user with actionable messages.

Activity Logging & Reporting

Logging System

All application activities are automatically logged to logs/application.log in JSON format.

What Gets Logged:

• Master Asset Submissions: Box ID, campaign number, dates, webhook status
• Global to Local Transforms: Campaign number, business unit, input/output counts
• Box Uploads: File count, folder ID, upload status
• OMG API Lookups: Campaign number, business area, business unit

Log Entry Structure:

{
  "timestamp": "2025-11-17 22:30:15",
  "action": "global_to_local_transform",
  "user_email": "daveporter@oliver.agency",
  "user_name": "Dave Porter",
  "status": "success",
  "data": {
    "campaign_number": "1601654",
    "business_unit": "VICHY",
    "input_rows": 13,
    "output_file_count": 16,
    "total_output_rows": 208
  },
  "ip_address": "192.168.1.100",
  "user_agent": "Mozilla/5.0..."
}

Activity Logs Viewer

Access the logs viewer at /logs-viewer.php (Tab 3: "Activity Logs")

Features:

• Statistics Dashboard:

  • Total actions performed
  • Success vs error counts
  • Unique user count
  • Error list with timestamps

• Filterable Table:

  • Filter by action type (Asset Submission, Global to Local, Box Upload, OMG API)
  • Filter by user email
  • Show last 50, 100, 500, or 1000 entries
  • Expandable details for each log entry (full JSON data)

• Export Functionality:

  • Export filtered logs to CSV
  • Download for external analysis in Excel/Google Sheets
  • Includes all log fields

Use Cases:

• Monitor system usage and adoption
• Track which campaigns have been processed
• Identify error patterns and troubleshoot issues
• Audit user activity for compliance
• Generate usage reports for stakeholders
• Analyze peak usage times

Log File Location: logs/application.log (excluded from git)

Email Notifications

The system sends email notifications for Global to Local workflow:

Configured SMTP:

• Server: smtp.mailgun.org:587
• From: twist@mail.dev.oliver.solutions
• Recipient: Logged-in user's email

Notification Types:

1. Process Started: "Document: {filename} has been picked up for processing"
2. Process Completed: "Generated {count} regional CSV files. Files should appear in OMG within 5 minutes"
3. Process Failed: Error details and troubleshooting guidance

Configuration: Set 'enabled' => false in config.php to disable emails

All errors are logged and displayed to the user with actionable messages.

API Endpoints

Global to Local Endpoints

process-csv.php

• Method: POST (multipart/form-data)
• Input: CSV file upload
• Output: Processed data with progress tracking
• Returns: 16 CSV files info + preview

get-csv-preview.php

• Method: GET
• Parameters: fileIndex (0-15)
• Output: Full CSV preview for selected file

download-csv.php

• Method: GET
• Parameters: file (file index)
• Output: Single CSV file download

download-all-csv.php

• Method: POST
• Output: ZIP file containing all 16 CSVs

upload-to-box.php

• Method: POST
• Action: Uploads all 16 CSVs to Box output folder
• Returns: Upload confirmation + folder URL
• Logs: Records box_upload action with file count

Activity Logs Endpoints

logs-viewer.php

• Method: GET
• Parameters:
  • action (optional): Filter by action type
  • user (optional): Filter by user email
  • limit (optional): Number of entries (default: 100)
  • export=csv: Export filtered logs as CSV download
• Output: HTML page with logs table and statistics
• Returns: Filterable, exportable activity logs

Master Global Asset Submission Endpoints

validate-box.php

Method: POST Content-Type: application/json

Request:

{
  "boxId": "123456789"
}

Response (Success):

{
  "success": true,
  "data": {
    "boxId": "123456789",
    "folderName": "Asset Folder",
    "masterCampaignNumber": "Campaign Name",
    "masterCampaignId": "987654321",
    "contents": {
      "total": 15,
      "folders": [...],
      "files": [...]
    }
  }
}

submit.php

Method: POST Content-Type: application/json

Request:

{
  "boxId": "123456789",
  "supplyDate": "24/03/2025 00:00",
  "liveDate": "31/03/2025 00:00",
  "endDate": "07/04/2025 00:00",
  "boxData": {
    "masterCampaignNumber": "Campaign Name",
    "masterCampaignId": "987654321",
    "contents": {...}
  }
}

Response:

{
  "success": true,
  "message": "Submission successful",
  "webhookResponse": {...},
  "webhookStatus": 200
}

Security Features

• httpOnly Cookies: Authentication tokens stored securely
• JWT Validation: Box API uses signed JWT for authentication
• Input Sanitization: All user inputs are validated and escaped
• CSRF Protection: SameSite cookie attribute
• XSS Prevention: HTML escaping in frontend
• Sensitive Data: JWT config excluded from version control

Troubleshooting

Box Authentication Fails

• Verify 43984435_77m2ujl3_config.json is present
• Check private key passphrase is correct
• Ensure Box app has proper permissions

SSO Not Working

• Verify Azure tenant and client IDs
• Check redirect URI matches your domain
• Ensure HTTPS is enabled in production

Webhook Fails

• Check webhook URL is accessible
• Verify API key is correct
• Check webhook timeout setting (default 30s)

Development

Testing Locally

Set SSO to disabled in config.php for instant local access without Azure AD setup.

Debugging

Enable debug mode in config.php:

'app' => [
    'debug' => true  // Shows detailed error messages
]

Note: Disable in production!

Author

Dave Porter (daveporter@oliver.agency)

Repository

https://bitbucket.org/zlalani/loreal-global-kickoff

License

Proprietary - L'Oréal/Oliver Agency