vending_data_intelligence_report/CLAUDE.md

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

Data Intelligence Report Generator for ERP ROA (Oracle Database). Generates Excel and PDF business intelligence reports with sales analytics, margin analysis, stock tracking, financial indicators, and alerts.

Commands

# Virtual Environment setup
python -m venv .venv
source .venv/bin/activate  # Linux/WSL
pip install -r requirements.txt

# Run report (default: last 12 months)
python main.py

# Custom period
python main.py --months 6

# Docker alternative
docker-compose run --rm report-generator

Oracle Connection

Environment	ORACLE_HOST value
Windows native	127.0.0.1
WSL	Windows IP (cat /etc/resolv.conf | grep nameserver)
Docker	host.docker.internal

Architecture

main.py                    # Entry point, orchestrates everything
├── config.py              # .env loader, thresholds (RECOMMENDATION_THRESHOLDS)
├── queries.py             # SQL queries in QUERIES dict with metadata
├── recommendations.py     # RecommendationsEngine - auto-generates alerts
└── report_generator.py    # Excel/PDF generators

Data flow:

1. main.py executes queries via OracleConnection context manager
2. Results stored in results dict (query_name → DataFrame)
3. Consolidation logic merges related DataFrames (e.g., KPIs + YoY)
4. ExcelReportGenerator creates consolidated sheets + detail sheets
5. PDFReportGenerator creates consolidated pages + charts

Report structure (after consolidation):

• Excel: 4 consolidated sheets (Vedere Ansamblu, Indicatori Venituri, Clienti si Risc, Tablou Financiar) + detail sheets
• PDF: Consolidated pages with multiple sections + charts + detail tables

Key Code Locations

What	Where
SQL queries	queries.py - constants like SUMAR_EXECUTIV, CONCENTRARE_RISC_YOY
Query registry	queries.py:QUERIES dict
Sheet order	main.py:sheet_order list (~line 242)
Consolidated sheets	main.py after "GENERARE SHEET-URI CONSOLIDATE" (~line 567)
Legends	main.py:legends dict (~line 303)
Alert thresholds	config.py:RECOMMENDATION_THRESHOLDS
Consolidated sheet method	report_generator.py:ExcelReportGenerator.add_consolidated_sheet()
Consolidated page method	report_generator.py:PDFReportGenerator.add_consolidated_page()

Adding New Reports

1. Add SQL constant in queries.py (e.g., NEW_QUERY = """SELECT...""")
2. Add to QUERIES dict: 'new_query': {'sql': NEW_QUERY, 'params': {'months': 12}, 'title': '...', 'description': '...'}
3. Add 'new_query' to sheet_order in main.py
4. Add legend in legends dict if needed
5. For PDF: add rendering in PDF section of generate_reports()

Adding Consolidated Views

To add data to consolidated sheets, modify the sections list in add_consolidated_sheet() calls:

excel_gen.add_consolidated_sheet(
    name='Sheet Name',
    sections=[
        {'title': 'Section', 'df': results.get('query_name'), 'legend': legends.get('query_name')}
    ]
)

Oracle Schema Conventions

• sters = 0 excludes deleted records
• tip NOT IN (7, 8, 9, 24) excludes returns/credit notes
• Account 341, 345 = own production; 301 = raw materials
• Required views: fact_vfacturi2, fact_vfacturi_detalii, vnom_articole, vnom_parteneri, vstoc, vrul

YoY Query Pattern

When creating Year-over-Year comparison queries:

1. Use CTEs for current period (ADD_MONTHS(TRUNC(SYSDATE), -12) to SYSDATE)
2. Use CTEs for previous period (ADD_MONTHS(TRUNC(SYSDATE), -24) to ADD_MONTHS(TRUNC(SYSDATE), -12))
3. Handle empty previous data with NVL() fallback to 0
4. Add TREND column with values like 'CRESTERE', 'SCADERE', 'STABIL', 'FARA DATE YOY'

Conditional Formatting Colors

Status	Excel Fill	Meaning
OK/Good	#4ECDC4 (teal)	CRESTERE, IMBUNATATIRE, DIVERSIFICARE
Warning	#FFE66D (yellow)	ATENTIE
Alert	#FF6B6B (red)	ALERTA, SCADERE, DETERIORARE, CONCENTRARE