# Menu Badge Audit Report
**Generated:** 2025-12-26  
**Purpose:** Comprehensive analysis of menu item badge queries for alert/notification system

---

## Executive Summary

### Current State
- **Total Menu Items:** 313
- **Items WITH Badges:** 77 (24.6%)
- **Items WITHOUT Badges:** 231 (73.8%)
- **Header Items:** 5

### Key Findings
1. **Good Coverage in Core Areas:** Email, Files, Sales Documents, HR modules have good badge coverage
2. **Missing Coverage:** Many operational areas lack badge queries (Purchasing, Inventory, Projects)
3. **Opportunity:** 231 menu items could benefit from intelligent badge queries

---

## Step 1: Current Badge Query Audit

### ✅ Well-Implemented Badge Queries

#### Email System (Excellent Implementation)
- **Inbox:** Unread email count with urgent threshold
- **Drafts:** Draft count with warning threshold
- **Starred:** Starred items count
- **Spam:** Spam count
- All email folders have appropriate badge queries

#### Sales & Invoicing (Good Implementation)
- **Invoices:** Unpaid/overdue invoice count with urgent threshold
- **Orders:** Active order count with warning threshold
- **Quotes:** Active quote count
- **Credit Notes:** Unapplied credit notes

#### HR & Time Tracking (Good Implementation)
- **Time Off Requests:** Pending approval count
- **Timesheets:** Unsubmitted/pending approval count
- **Employees:** Active employee count
- **Badge Attendance:** Currently present employees

#### Manufacturing (Good Implementation)
- **Work Orders:** Active work order count
- **Quality Inspections:** Pending inspection count
- **Maintenance:** Pending maintenance work orders
- **Capacity Planning:** Over-capacity alerts

#### CRM (Good Implementation)
- **Support Tickets:** Open ticket count with urgent threshold
- **Opportunities:** Active opportunity count
- **Activities:** Today's activities for user

---

## Step 2: High-Priority Missing Badge Queries

### 🔴 CRITICAL - Immediate Attention Needed

#### 1. **Accounts Payable - Overdue Bills**
```sql
-- Menu: Bills/Invoices (under Purchases)
-- URL: purchases/bills
SELECT COUNT(*) 
FROM bills 
WHERE status = 'overdue' 
  AND deleted_at IS NULL
```
- **Badge Color:** `danger`
- **Urgent Threshold:** `3`
- **Urgent Color:** `danger`
- **Rationale:** Overdue bills can damage vendor relationships and credit rating

#### 2. **Purchase Orders - Pending Approval**
```sql
-- Menu: Purchase Orders (under Procurement Process)
-- Already has badge, but consider adding urgent threshold
SELECT COUNT(*) as count 
FROM purchase_orders 
WHERE status = 'pending' 
  AND deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Delayed PO approvals can halt production

#### 3. **Inventory - Out of Stock Items**
```sql
-- Menu: Stock Levels (under Inventory)
-- URL: /inventory/stock-levels
SELECT COUNT(DISTINCT p.id) 
FROM products p 
LEFT JOIN inventory_levels il ON p.id = il.product_id 
WHERE p.status = 'active' 
  AND COALESCE(il.quantity, 0) = 0 
  AND p.deleted_at IS NULL
```
- **Badge Color:** `danger`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Out of stock items can halt sales and production

#### 4. **Inventory - Low Stock Items**
```sql
-- Menu: Stock Levels (under Inventory)
-- URL: /inventory/stock-levels
SELECT COUNT(DISTINCT p.id) 
FROM products p 
LEFT JOIN inventory_levels il ON p.id = il.product_id 
WHERE p.status = 'active' 
  AND p.reorder_level > 0 
  AND COALESCE(il.quantity, 0) <= p.reorder_level 
  AND COALESCE(il.quantity, 0) > 0
  AND p.deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `10`
- **Urgent Color:** `danger`
- **Rationale:** Low stock items need reordering to prevent stockouts

#### 5. **Purchase Requisitions - Pending Approval**
```sql
-- Menu: Purchase Requisitions (under Purchases)
-- URL: purchases/requisitions
SELECT COUNT(*) 
FROM purchase_requisitions 
WHERE status = 'pending' 
  AND deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Delayed requisitions can delay procurement

#### 6. **Goods Receipt - Pending Inspection**
```sql
-- Menu: Goods Receipt (under Procurement Process)
-- Already has badge, consider refining
SELECT COUNT(*) 
FROM goods_receipts 
WHERE status = 'pending_inspection' 
  AND deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Pending inspections delay inventory availability

#### 7. **Customer Payments - Overdue Invoices**
```sql
-- Menu: Customer Payments (under Accounting)
-- URL: /accounting/customer-payments
SELECT COUNT(*) 
FROM invoices 
WHERE status = 'overdue' 
  AND deleted_at IS NULL
```
- **Badge Color:** `danger`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Overdue invoices impact cash flow

#### 8. **Vendor Payments - Due This Week**
```sql
-- Menu: Vendor Payments (under Accounting)
-- URL: /accounting/vendor-payments
SELECT COUNT(*) 
FROM bills 
WHERE status IN ('approved', 'pending') 
  AND due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
  AND deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `3`
- **Urgent Color:** `danger`
- **Rationale:** Upcoming payments need attention for cash flow planning

---

### 🟡 MEDIUM PRIORITY - Important for Monitoring

#### 9. **Projects - Overdue Projects**
```sql
-- Menu: Projects (under TOOLS)
-- URL: projects
SELECT COUNT(*) 
FROM projects 
WHERE status = 'active' 
  AND end_date < CURDATE() 
  AND deleted_at IS NULL
```
- **Badge Color:** `danger`
- **Urgent Threshold:** `3`
- **Urgent Color:** `danger`
- **Rationale:** Overdue projects need immediate attention

#### 10. **Projects - Active Projects**
```sql
-- Menu: My Projects (under Projects)
-- URL: projects/?my_projects=1
SELECT COUNT(*) 
FROM projects 
WHERE status = 'active' 
  AND deleted_at IS NULL
```
- **Badge Color:** `info`
- **Rationale:** Shows workload at a glance

#### 11. **Tasks - My Overdue Tasks**
```sql
-- Menu: My Tasks (under Projects)
-- URL: projects/my-tasks
SELECT COUNT(*) 
FROM project_tasks 
WHERE assigned_to = {user_id} 
  AND status NOT IN ('completed', 'cancelled') 
  AND due_date < CURDATE() 
  AND deleted_at IS NULL
```
- **Badge Color:** `danger`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Personal task management

#### 12. **Tasks - My Active Tasks**
```sql
-- Menu: My Tasks (under Projects)
-- URL: projects/my-tasks
SELECT COUNT(*) 
FROM project_tasks 
WHERE assigned_to = {user_id} 
  AND status NOT IN ('completed', 'cancelled') 
  AND deleted_at IS NULL
```
- **Badge Color:** `info`
- **Rationale:** Shows personal workload

#### 13. **Supplier Contracts - Expiring Soon**
```sql
-- Menu: Supplier Contracts (under Purchases)
-- URL: purchases/contracts
SELECT COUNT(*) 
FROM supplier_contracts 
WHERE status = 'active' 
  AND end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) 
  AND deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `3`
- **Urgent Color:** `danger`
- **Rationale:** Contract renewals need advance planning

#### 14. **Lot/Serial Numbers - Expiring Soon**
```sql
-- Menu: Expiration Dates (under Inventory > Lot/Serial Numbers)
-- URL: inventory/lots/expiration
SELECT COUNT(*) 
FROM lot_serial_numbers 
WHERE expiration_date IS NOT NULL 
  AND expiration_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) 
  AND status = 'active'
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Expiring inventory needs action

#### 15. **Lot/Serial Numbers - Expired**
```sql
-- Menu: Expiration Dates (under Inventory > Lot/Serial Numbers)
-- URL: inventory/lots/expiration
SELECT COUNT(*) 
FROM lot_serial_numbers 
WHERE expiration_date IS NOT NULL 
  AND expiration_date < CURDATE() 
  AND status = 'active'
```
- **Badge Color:** `danger`
- **Urgent Threshold:** `1`
- **Urgent Color:** `danger`
- **Rationale:** Expired inventory must be removed

#### 16. **Purchase Returns - Pending**
```sql
-- Menu: Purchase Returns (under Purchases)
-- URL: purchases/returns
SELECT COUNT(*) 
FROM purchase_returns 
WHERE status IN ('pending', 'approved') 
  AND deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Urgent Threshold:** `5`
- **Urgent Color:** `danger`
- **Rationale:** Returns need processing

#### 17. **RFQs - Pending Response**
```sql
-- Menu: RFQs (Quotes) (under Purchases)
-- URL: purchases/rfqs
SELECT COUNT(*) 
FROM purchase_rfqs 
WHERE status = 'sent' 
  AND response_due_date >= CURDATE() 
  AND deleted_at IS NULL
```
- **Badge Color:** `info`
- **Urgent Threshold:** `5`
- **Urgent Color:** `warning`
- **Rationale:** Track pending vendor quotes

#### 18. **Debit Notes - Unapplied**
```sql
-- Menu: Debit Notes (under Purchases)
-- URL: purchases/debit-notes
SELECT COUNT(*) 
FROM debit_notes 
WHERE status NOT IN ('applied', 'cancelled') 
  AND deleted_at IS NULL
```
- **Badge Color:** `warning`
- **Rationale:** Unapplied debit notes need reconciliation

---

## Step 3: Recommended SQL Queries by Category

### Accounting & Finance

#### Bank Reconciliation - Unreconciled Accounts
```sql
SELECT COUNT(*) 
FROM bank_accounts 
WHERE is_active = 1 
  AND (last_reconciliation_date IS NULL 
       OR last_reconciliation_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY))
```
- **Menu:** Bank Reconciliation
- **Badge Color:** `danger`
- **Urgent Threshold:** `1`

#### Journal Entries - Unposted Entries
```sql
SELECT COUNT(*) 
FROM journal_entries 
WHERE status = 'draft'
```
- **Menu:** Journal Entries
- **Badge Color:** `warning`

#### Expenses - Pending Approval
```sql
SELECT COUNT(*) 
FROM expenses 
WHERE status = 'pending_approval'
```
- **Menu:** Expenses
- **Badge Color:** `warning`
- **Urgent Threshold:** `10`

### FTZ Management

#### FTZ Alerts - Critical Alerts
```sql
SELECT COUNT(*) 
FROM ftz_alerts 
WHERE status IN ('ACTIVE', 'ACKNOWLEDGED') 
  AND severity = 'CRITICAL'
```
- **Menu:** FTZ Alerts
- **Badge Color:** `danger`
- **Urgent Threshold:** `1`

#### Zone Lots - Low Stock
```sql
SELECT COUNT(*) 
FROM ftz_zone_lots 
WHERE status = 'ACTIVE' 
  AND current_quantity > 0 
  AND current_quantity <= COALESCE(ftz_alert_threshold, initial_quantity * 0.2)
```
- **Menu:** All Zone Lots
- **Badge Color:** `warning`
- **Urgent Threshold:** `5`

### HR & Recruitment

#### Applications - Pending Review
```sql
SELECT COUNT(*) 
FROM applications 
WHERE status = 'pending_review'
```
- **Menu:** Applications (under Recruitment)
- **Badge Color:** `info`
- **Urgent Threshold:** `5`

#### Interviews - Scheduled Today
```sql
SELECT COUNT(*) 
FROM interviews 
WHERE status = 'scheduled' 
  AND interview_date = CURDATE()
```
- **Menu:** Interviews
- **Badge Color:** `warning`

#### Offers - Pending Acceptance
```sql
SELECT COUNT(*) 
FROM offers 
WHERE status = 'pending' 
  AND offer_date >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)
```
- **Menu:** Offers
- **Badge Color:** `success`

#### Employee Issues - Active
```sql
SELECT COUNT(*) 
FROM employee_issues 
WHERE status IN ('reported', 'investigating', 'in-progress')
```
- **Menu:** Issues & Challenges
- **Badge Color:** `warning`

#### Separations - Pending
```sql
SELECT COUNT(*) 
FROM separations 
WHERE status = 'pending'
```
- **Menu:** Separations
- **Badge Color:** `warning`

---

## Implementation Guide

### How to Add Badge Queries

1. **Navigate to Menu Management**
   - Go to http://localhost:8080/menus
   - Find the menu item you want to update

2. **Edit the Menu Item**
   - Click the "Edit" button for the menu item
   - Scroll to the "Badge Configuration" section

3. **Add the Badge Query**
   - Paste the SQL query into the "Badge Query" field
   - Set the "Badge Color" (primary, secondary, success, danger, warning, info)
   - Optionally set "Urgent Threshold" (number)
   - Optionally set "Urgent Color" (for when count exceeds threshold)

4. **Save and Test**
   - Save the menu item
   - Navigate to the main menu to see the badge appear
   - Verify the count is accurate

### Badge Color Guidelines

- **`danger` (Red):** Critical issues requiring immediate attention
- **`warning` (Yellow/Orange):** Important items needing attention soon
- **`info` (Light Blue):** Informational counts, active items
- **`success` (Green):** Positive indicators, completed items
- **`primary` (Blue):** General information, total counts
- **`secondary` (Gray):** Less important information

### Urgent Threshold Guidelines

- Set urgent threshold for items that become critical above a certain count
- When count exceeds threshold, badge color changes to urgent color
- Examples:
  - Overdue invoices: threshold = 5
  - Critical alerts: threshold = 1
  - Pending approvals: threshold = 10

---

## Next Steps

1. **Review this report** and prioritize which badge queries to implement
2. **Test SQL queries** in phpMyAdmin or database tool to verify they work
3. **Implement high-priority badges** first (marked with 🔴)
4. **Implement medium-priority badges** next (marked with 🟡)
5. **Monitor badge performance** and adjust thresholds as needed
6. **Gather user feedback** on which badges are most useful

---

## Maintenance

- **Review badge queries quarterly** to ensure they remain relevant
- **Update thresholds** based on business volume changes
- **Add new badges** as new features are added to the system
- **Remove badges** that are not providing value

---

**Report Generated By:** Menu Badge Audit Script  
**Script Location:** `scripts/audit_menu_badges.php`  
**Last Updated:** 2025-12-26