# Standards of Conduct - Acknowledgment Records Implementation

## Overview
Minimal, efficient implementation for tracking and verifying signed acknowledgments from employees, contractors, and vendor employees on standards of conduct.

## Database Tables

### 1. **tbl_coc_population_groups**
Tracks acknowledgment completion by population group (Employee, Contractor, Vendor)
- Automatic completion percentage calculation
- Separate tracking per group type
- Links to control code (CC1.1) and entity

### 2. **tbl_coc_acknowledgment_records**
Individual acknowledgment records for each person
- Status: acknowledged, pending, overdue
- Stores: individual_id, name, email, acknowledgment_date, reference
- Bulk upsert support for CSV imports

### 3. **tbl_coc_gaps_and_findings**
Tracks gaps and findings with management responses
- Severity levels: low, medium, high, critical
- Status tracking: open, in_progress, closed
- Management response workflow

### 4. **tbl_coc_bulk_uploads**
Audit trail for bulk imports
- Records upload source (Workday, BambooHR, etc.)
- Success/partial/failed status
- Number of records processed and matched

## Key Features

✅ **Bulk Upload Support**
- CSV import from Workday, BambooHR, or custom systems
- Auto-detection of acknowledged vs. pending
- Upsert logic (create or update)

✅ **Population Group Tracking**
- Separate completion tracking for Employees, Contractors, Vendors
- Real-time percentage calculations
- Per-group status dashboard

✅ **Gap Management**
- Flag incomplete acknowledgment areas
- Severity-based prioritization
- Management response workflow

✅ **Audit Trail**
- All uploads logged with source system
- Creation and update timestamps
- User attribution

## File Structure

```
app/
├── Controllers/
│   └── ConductAcknowledgmentsController.php
├── Models/
│   ├── PopulationGroupModel.php
│   ├── AcknowledgmentRecordModel.php
│   ├── GapAndFindingModel.php
│   └── BulkUploadModel.php
├── Database/
│   ├── Migrations/
│   │   └── 2026-06-23-140000_CreateStandardsOfConductAcknowledgments.php
│   └── tbl_conduct_acknowledgments.sql
└── Views/
    └── conduct_acknowledgments/
        ├── dashboard.php
        ├── group_details.php
        ├── upload.php
        ├── add_gap.php
        └── add_response.php
```

## Routes to Add

Add these to `app/Config/Routes.php`:

```php
// Standards of Conduct Acknowledgments
$routes->get('conduct-acknowledgments/dashboard/(:any)', 'ConductAcknowledgmentsController::dashboard/$1');
$routes->get('conduct-acknowledgments/group/(:num)', 'ConductAcknowledgmentsController::viewGroup/$1');
$routes->get('conduct-acknowledgments/upload/(:num)', 'ConductAcknowledgmentsController::uploadRecords/$1');
$routes->post('conduct-acknowledgments/upload/(:num)', 'ConductAcknowledgmentsController::uploadRecords/$1');
$routes->get('conduct-acknowledgments/add-gap/(:num)', 'ConductAcknowledgmentsController::addGap/$1');
$routes->post('conduct-acknowledgments/add-gap/(:num)', 'ConductAcknowledgmentsController::addGap/$1');
$routes->get('conduct-acknowledgments/add-response/(:num)', 'ConductAcknowledgmentsController::addResponse/$1');
$routes->post('conduct-acknowledgments/add-response/(:num)', 'ConductAcknowledgmentsController::addResponse/$1');
$routes->get('conduct-acknowledgments/close-finding/(:num)', 'ConductAcknowledgmentsController::closeFinding/$1');
```

## CSV Import Format

Expected CSV format for bulk uploads:

```
individual_id,individual_name,email,acknowledged_date,acknowledgment_reference
EMP001,John Doe,john@company.com,2026-06-20,WDAY-123
CNT002,Jane Smith,jane@contractor.com,2026-06-19,BHR-456
VND003,Bob Wilson,bob@vendor.com,,MANUAL-789
```

- **individual_id**: Unique ID (e.g., employee ID, contractor code)
- **individual_name**: Full name
- **email**: Email address (optional)
- **acknowledged_date**: Date of acknowledgment (optional - if blank, status = pending)
- **acknowledgment_reference**: Reference from source system (e.g., Workday ID)

## Usage Flow

1. **Dashboard**: `/conduct-acknowledgments/dashboard/{entity_name}`
   - View completion status by population group
   - See progress bars per group

2. **View Group**: `/conduct-acknowledgments/group/{group_id}`
   - See all acknowledgment records
   - View gaps and findings
   - Check upload history

3. **Upload Records**: `/conduct-acknowledgments/upload/{group_id}`
   - Upload CSV file from HR system
   - Select file source (Workday, BambooHR, etc.)
   - System auto-updates completion percentage

4. **Add Gap**: `/conduct-acknowledgments/add-gap/{group_id}`
   - Flag missing or incomplete acknowledgments
   - Set severity level
   - Triggers audit requirement

5. **Add Response**: `/conduct-acknowledgments/add-response/{gap_id}`
   - Management provides response to gap
   - Mark as "in_progress"
   - Close finding when complete

## Key Methods

### PopulationGroupModel
- `getGroupsByEntity()` - Get all groups for an entity
- `updateCompletion()` - Recalculate completion % (automatic on upload)

### AcknowledgmentRecordModel
- `getByPopulationGroup()` - Get records for a group
- `getStatsByGroup()` - Count by status (acknowledged/pending/overdue)
- `bulkUpsert()` - Import from CSV

### GapAndFindingModel
- `getByGroup()` - Get findings for a group
- `getOpenFindings()` - Get non-closed findings
- `addResponse()` - Add management response

## Next Steps (Optional Enhancements)

- Email reminders for pending acknowledgments
- Auto-mark overdue after X days
- Export findings to audit report
- Role-based access control
- Audit report generation

## Data Relationships

```
PopulationGroup (1) ──→ (∞) AcknowledgmentRecords
                   └──→ (∞) GapsAndFindings
                   └──→ (∞) BulkUploads
```

## Notes

- **No over-engineering**: Minimal fields, simple validation
- **Easy to understand**: Clear naming, straightforward logic
- **Audit-ready**: Full audit trail with timestamps
- **Flexible imports**: Supports any HR system with CSV export