📊 Excel Team Progress Tracker

Arabic-first dashboard for weekly department Excel reports: financial requests, task progress, validation alerts, and cross-week completion deltas — without a traditional business database.

🎯 Project overview

Organizations submit one Excel file per department per week, stored under folders named yyyy-mm-dd. This system:

Parses .xlsx files with Python and xlwings (Windows + Microsoft Excel)
Normalizes Arabic text and Eastern Arabic numerals (٠–٩ → 0–9)
Flags data issues (missing/duplicate task codes, unapproved financial rows)
Caches a canonical JSON payload in Laravel
Displays an RTL Filament admin UI in Arabic

Key capabilities

Area	What you get
💰 Financials	Weekly/department/grand totals; unapproved rows visible but excluded from sums
✅ Tasks	Full SRS column mapping; completion % deltas across weeks
⚠️ Alerts	System-wide warnings for bad source data
🏢 Views	Global financial & projects; department hub (financials, projects, tasks); integrity matrix

Tech stack

Layer	Technology
Extractor	Python 3.11+, xlwings, pandas, PyYAML, openpyxl
Backend	Laravel 11, Filament 3.3
Storage	Filesystem + JSON (`latest.json`); SQLite only for auth/cache
UI	Filament, custom Vite theme (dark mode), Cairo font, RTL, brand `#FF2D20`

🏗️ Project architecture

flowchart LR
  subgraph sources [Filesystem]
    WeekDirs["yyyy-mm-dd folders"]
    Xlsx["Department.xlsx"]
    WeekDirs --> Xlsx
  end
  subgraph extract [Python]
    Discover[discovery.py]
    Parse[parsers + normalize]
    Validate[validate.py]
    JSON[latest.json]
    Discover --> Parse --> Validate --> JSON
  end
  subgraph laravel [Laravel]
    Cmd["progress:parse"]
    Cache[ProgressCacheService]
    Metrics[ProgressMetricsService]
    UI[Filament RTL admin]
    Cmd --> Parse
    JSON --> Cache --> Metrics --> UI
  end
  Config[config/progress.yaml] --> Discover
  Config --> Cmd
  Xlsx --> Discover

Repository layout

excel_summary/
├── letterhead.txt.example     # Template → copy to letterhead.txt (gitignored, per site)
├── config/progress.yaml       # Shared YAML: paths, ranges, Arabic headers
├── extractor/                 # Python venv + run.py CLI
│   └── scripts/generate_samples.py
├── backend/                   # Laravel + Filament (/admin)
├── samples/                   # Dummy weekly Excel (dev)
├── scripts/                   # PowerShell: setup, dev-start, parse, tests, …
├── docs/                      # Project documentation (see docs/README.md)
│   ├── user/                  # USER_GUIDE.md
│   ├── development/           # DEVELOPMENT.md
│   ├── technical/             # SYSTEM_DOCUMENTATION.md, UX_Logic_SRS.md
│   ├── release/               # PACKAGING.md
│   └── design/                # DESIGN, SYRIAN_BRAND_TOKENS
└── README.md                  # This file (entry point)

📦 Dependencies

System requirements

Requirement	Notes
Windows	Recommended; xlwings uses Excel COM
Microsoft Excel	Required for parsing (`run.py`); not for generating samples
PHP 8.2+	Laravel backend
Composer	PHP dependencies
Node.js (LTS)	Build Filament admin theme (`npm ci` / `npm run build` in `backend/`)
Python 3.11+	Extractor venv

PHP (`backend/composer.json`)

Package	Purpose
`laravel/framework` ^11	Web app, cache, scheduler
`filament/filament` 3.3	Arabic RTL admin UI
`laravel/tinker`	REPL (dev)

Python (`extractor/requirements.txt`)

Package	Purpose
`xlwings`	Read Excel via installed Excel
`pandas`	Data manipulation
`PyYAML`	Load `config/progress.yaml`
`openpyxl`	Generate dummy `.xlsx` without Excel
`pytest`	Extractor tests

🛠️ Installation

Shortcut (Windows): from repo root run .\scripts\setup-first-time.ps1 (venv, Composer, npm theme build, .env, letterhead.txt, DB, samples, initial parse) — see scripts/README.md.

1️⃣ Python extractor

cd extractor
python -m venv .venv
.\.venv\Scripts\pip install -r requirements.txt

2️⃣ Letterhead (institution names)

# From repo root — skip if you already ran setup-first-time.ps1
copy letterhead.txt.example letterhead.txt
# Edit letterhead.txt (UTF-8 key=value lines)

3️⃣ Laravel backend

cd backend
composer install
npm ci
npm run build
copy .env.example .env
php artisan key:generate
php artisan migrate
php artisan db:seed

4️⃣ Dummy data (first run)

cd extractor
.\.venv\Scripts\python scripts\generate_samples.py --clean

cd ..\backend
php artisan progress:parse --force

Daily commands: see DEVELOPMENT.md for what to run every session vs once.

Using the dashboard

For managers and admins using /admin (not developers), see USER_GUIDE.md — signing in, refreshing data, date filters, home matrix, global and department views, and department display aliases.

▶️ How to run

PowerShell scripts (Windows)

From the repo root, use the helpers in scripts/. Full reference: scripts/README.md.

When	Script
First clone / new PC	`.\scripts\setup-first-time.ps1`
Daily — start admin	`.\scripts\dev-start.ps1`
Daily — Excel changed	`.\scripts\dev-parse.ps1`
Dummy / test data	`.\scripts\dev-generate-samples.ps1` (`-Clean` to wipe `samples/`)
After `git pull`	`.\scripts\dev-update-deps.ps1`
DB schema changed	`.\scripts\dev-migrate.ps1`
After `.env` / config edit	`.\scripts\dev-clear-cache.ps1`
Run tests	`.\scripts\dev-test.ps1`
Debug Python only	`.\scripts\dev-parse-python-only.ps1` then `dev-parse.ps1`

Start the dashboard

.\scripts\dev-start.ps1

Or manually:

cd backend
composer run serve-dev

Open http://127.0.0.1:8000/admin

Field	Value
Email	`admin@progress.local`
Password	`password`

On some Windows + PHP 8.4 setups, php artisan serve fails — use composer run serve-dev (PHP built-in server).

Refresh data after Excel changes

.\scripts\dev-parse.ps1

Or from backend\: php artisan progress:parse --force. Or click «تحديث البيانات» in the admin header.

Generate dummy data (GUI or CLI)

From the home page (الرئيسية): «توليد بيانات تجريبية» — recreates samples/ and re-parses (only in local env or when EXCEL_ROOT points at samples/).

From terminal:

.\scripts\dev-generate-samples.ps1 -Clean

Or: php artisan progress:generate-samples --clean then php artisan progress:parse --force in backend\.

⚙️ Configuration

Configuration has two layers: shared YAML for Excel layout, and optional .env overrides.

`config/progress.yaml`

Key	Description
`paths.excel_root`	Root folder of weekly directories (default: `samples`)
`paths.output_json`	Parsed JSON output path
`excel.week_folder_pattern`	Regex for week folders (`yyyy-mm-dd`)
`excel.financial.amount_range`	e.g. `E8:E25`
`excel.financial.description_range`	e.g. `H8:H25`
`excel.tasks.header_row`	Task header row (default: `28`)
`excel.tasks.data_start_row`	First data row (default: `29`)
`excel.tasks.columns`	Arabic header → internal field map
`validation.*`	Flags for missing/duplicate task codes, unapproved totals
`python.venv_python`	Path to venv Python for Laravel bridge

`letterhead.txt`

Official institution lines shown in the admin UI letterhead (Arabic). Edited as plain text at the repository root — not via Filament settings.

First-time: .\scripts\setup-first-time.ps1 copies letterhead.txt.example → letterhead.txt if missing. letterhead.txt is gitignored (each deployment keeps its own copy).

Key	Role
`aria`	Accessible label for the letterhead region
`republic`	Republic line
`ministry`	Ministry
`holding`	Holding company
`company`	Company
`directorate`	Directorate

Format: key=value per line; # starts a comment; file must be UTF-8. Reload the browser after changes.

Loader: backend/app/Support/LetterheadResources.php — path is hard-coded as ../../letterhead.txt relative to backend/ via base_path() (change RESOURCE_FILE in that class to point elsewhere).

Placement: top on الرئيسية (/admin/home); footer on other data pages. See USER_GUIDE.md §10 and DEVELOPMENT.md.

`backend/.env` overrides

Variable	Purpose
`EXCEL_ROOT`	Fallback weekly reports directory if no path saved in admin (الإعدادات → مسار ملفات Excel)
`PROGRESS_CONFIG_PATH`	Override YAML path
`PYTHON_EXECUTABLE`	Override Python binary
`PYTHON_SCRIPT`	Override `extractor/run.py` path

⚠️ Do not set env vars to empty strings (EXCEL_ROOT=). Leave them commented out so defaults apply.

Preferred: set the reports root in the admin UI — الإعدادات → مسار ملفات Excel (/admin/excel-root). The path is stored in backend/storage/app/excel_root.json and overrides EXCEL_ROOT when present.

Optional .env fallback (used only before the first GUI save):

EXCEL_ROOT=Z:/Reports/WeeklyProgress

Production deployment (security)

Before going live, set these in backend/.env (never commit .env):

Variable	Value
`APP_ENV`	`production`
`APP_DEBUG`	`false`
`APP_KEY`	Run `php artisan key:generate` once per environment
`ADMIN_PASSWORD`	Strong unique password (required before `php artisan db:seed`)
`ADMIN_EMAIL`	Optional; defaults to `admin@progress.local`

cd backend
php artisan key:generate
php artisan migrate
# Set ADMIN_PASSWORD in .env first, then:
php artisan db:seed

Restrict /admin (HTTPS, VPN, or firewall). Do not use the local dev password password in production.

See DEVELOPMENT.md for secret scanning, git safety rules, and confidential data handling.

🖥️ Dashboard views (Arabic RTL, V3)

View	Description
🏠 الرئيسية (`/admin/home`, page title الصفحة الرئيسية)	Files matrix (scoped by global date range) and department entry cards
💰 الاحتياج المالي (`/admin/global-financial`)	All departments by week tab + weekly grand total + range rollup
📁 المشاريع (`/admin/global-projects`)	Projects across all departments with dept badges
🏢 مركز المديرية (`/admin/departments/{dept}`)	Hub: per-dept financials, projects, tasks (hub card المالية → page المركز المالي; global nav الاحتياج المالي — see `UX_Logic_SRS.md` §5.1)
📂 مسار ملفات Excel (`/admin/excel-root`, nav الإعدادات)	Persistent weekly reports root; changing path re-parses all Excel data
🏷️ أسماء المديريات (`/admin/department-aliases`, nav الإعدادات)	Optional display aliases (Excel filenames unchanged)
Global date bar	On progress list pages (hidden on hub and settings) — filters weeks for views above

Weekly Excel folders may use yyyy-mm-dd or yyyy_mm_dd (normalized to hyphens in the app).

See UX_Logic_SRS.md and SYSTEM_DOCUMENTATION.md §7 for the full IA.

Browser tab icon and letterhead

Custom favicon and sidebar logo: backend/public/images/favicon.svg, registered in Filament AdminPanelProvider. Brand SVG wordmarks: docs/design/SYRIAN_BRAND_TOKENS.md.

Official letterhead text: letterhead.txt — see Configuration § letterhead.txt.

📋 Artisan commands

Command	Description
`php artisan progress:parse`	Parse if Excel files changed
`php artisan progress:parse --force`	Force re-parse
`php artisan progress:generate-samples`	Generate dummy Excel under `samples/`
`php artisan progress:generate-samples --clean`	Delete `samples/` first, then generate
`php artisan progress:ensure-admin`	Create or reset the Filament admin user

Hourly auto-parse: backend/routes/console.php.

Portable distribution (Windows)

To ship a zip-ready folder (bundled PHP + embeddable Python + pre-built admin UI) for users who do not have PHP/Python/Node installed, see PACKAGING.md and packaging/README-python.md.

Build from the repo root (requires extracted PHP NTS and Python embeddable zips):

.\scripts\build-portable.ps1 `
  -PhpRoot "C:\path\to\php-nts-folder" `
  -PythonRoot "C:\path\to\python-embed-amd64" `
  -Zip

End users unzip to a writable folder and run Start-Dashboard.bat. Microsoft Excel is still required for parsing reports.

📚 Related documents

Document	Audience
docs/README.md	Documentation index (all guides below)
docs/release/PACKAGING.md	IT / release — portable Windows build
docs/user/USER_GUIDE.md	Dashboard operators (Arabic UI workflows)
docs/development/DEVELOPMENT.md	Developers — everyday commands
docs/technical/SYSTEM_DOCUMENTATION.md	Technical reference — architecture, services, GUI
docs/technical/UX_Logic_SRS.md	Information architecture and UX rules
scripts/README.md	PowerShell scripts — full reference (setup, daily dev, tests)
backend/README.md	Backend-only notes (theme build)

📄 License

See LICENSE.

Name		Name	Last commit message	Last commit date
Latest commit History 61 Commits
.github/workflows		.github/workflows
backend		backend
config		config
docs		docs
extractor		extractor
packaging		packaging
scripts		scripts
.gitignore		.gitignore
.gitleaks.toml		.gitleaks.toml
.pre-commit-config.yaml		.pre-commit-config.yaml
.secrets.baseline		.secrets.baseline
LICENSE		LICENSE
README.md		README.md
Start-Dashboard.bat		Start-Dashboard.bat
Stop-Dashboard.bat		Stop-Dashboard.bat
letterhead.txt.example		letterhead.txt.example

Folders and files

Latest commit

History

Repository files navigation

📊 Excel Team Progress Tracker

Table of contents

🎯 Project overview

Key capabilities

Tech stack

🏗️ Project architecture

Repository layout

📦 Dependencies

System requirements

PHP (backend/composer.json)

Python (extractor/requirements.txt)

🛠️ Installation

1️⃣ Python extractor

2️⃣ Letterhead (institution names)

3️⃣ Laravel backend

4️⃣ Dummy data (first run)

Using the dashboard

▶️ How to run

PowerShell scripts (Windows)

Start the dashboard

Refresh data after Excel changes

Generate dummy data (GUI or CLI)

⚙️ Configuration

config/progress.yaml

letterhead.txt

backend/.env overrides

Production deployment (security)

🖥️ Dashboard views (Arabic RTL, V3)

Browser tab icon and letterhead

📋 Artisan commands

Portable distribution (Windows)

📚 Related documents

📄 License

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

PHP (`backend/composer.json`)

Python (`extractor/requirements.txt`)

`config/progress.yaml`

`letterhead.txt`

`backend/.env` overrides

Packages