# LOA Task 3 — HR Communications Implementation Plan
> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
**Goal:** Add status-change email notifications and an ad-hoc HR email compose panel with audit log to the LOA portal.
**Architecture:** Status-change notifications hook into the existing `handle_update()` in `loa_save.php` — fetch current status before UPDATE, fire `send_loa_notification()` on change. Ad-hoc emails go through a new `loa_send_email.php` endpoint that calls SendGrid directly (for status tracking), logs to a new `loa_email_log` table, and is exposed via the existing `public/hr/loa.php` router.
**Tech Stack:** PHP 8, MySQLi/PDO, SendGrid PHP SDK, jQuery (already loaded), vanilla JS fetch
---
## File Map
| File | Action | Purpose |
|------|--------|---------|
| `app/HR/LOA/loa_notifications.php` | Modify | Add 6 status cases to `send_loa_notification()` switch |
| `app/HR/LOA/loa_save.php` | Modify | Detect status change in `handle_update()`, fire notification |
| `app/HR/LOA/loa_send_email.php` | Create | Ad-hoc send endpoint — permission check, SendGrid call, DB log |
| `app/HR/LOA/loa_edit.php` | Modify | Load email log, add Section 7 HTML + JS send handler |
| `public/hr/loa.php` | Modify | Add `loa_send_email` to `$allowed` whitelist |
| DB | Create table | `loa_email_log` |
---
## Task 1: Create loa_email_log table
**Files:**
- DB: run against `whitewater` MySQL database
- [ ] **Step 1: Run CREATE TABLE**
Connect to MySQL (`whitewater` db) and run:
```sql
CREATE TABLE loa_email_log (
id INT AUTO_INCREMENT PRIMARY KEY,
loa_form_id INT NOT NULL,
to_email VARCHAR(255) NOT NULL,
subject VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
sent_by VARCHAR(100) NOT NULL,
sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status ENUM('sent','failed') NOT NULL DEFAULT 'sent',
error_message TEXT NULL,
INDEX idx_loa_form_id (loa_form_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
- [ ] **Step 2: Verify**
```sql
DESCRIBE loa_email_log;
```
Expected: 9 rows showing all columns (id, loa_form_id, to_email, subject, body, sent_by, sent_at, status, error_message).
---
## Task 2: Add status cases to loa_notifications.php
**Files:**
- Modify: `app/HR/LOA/loa_notifications.php`
The existing `send_loa_notification()` switch has one case (`New`) and a `default: return;`. Add 6 cases before the `default` line.
- [ ] **Step 1: Open `app/HR/LOA/loa_notifications.php` and locate the switch**
Find line with `default:` at the end of the switch (currently the last case before the closing `}`).
- [ ] **Step 2: Insert the 6 new cases**
Replace the `default:` block with the following (the new cases + the default):
```php
case 'approved':
$employee_subject = 'Your Leave of Absence Has Been Approved';
$employee_body = 'Hi ' . $esc($name) . ',
'
. 'Your Leave of Absence request has been approved. Here is a summary of your approved leave:
'
. ''
. '| Leave Type: | ' . $esc($category) . ' |
'
. '| Start Date: | ' . $esc($start) . ' |
'
. '| Expected End Date: | ' . $esc($end) . ' |
'
. '
'
. 'A member of the HR team will be in touch with you regarding next steps. If you have questions, please contact hr@whitewatercw.com.
'
. 'Best regards,
Human Resources
';
$detail_url = 'https://intranet.whitewatercw.com/hr/loa.php?page=loa_edit&id=' . $loa_id;
$hr_subject = 'LOA Approved — ' . $esc($name);
$hr_body = 'LOA case #' . $loa_id . ' has been marked Approved.
'
. ''
. '| Employee: | ' . $esc($name) . ' |
'
. '| Job Title: | ' . $esc($title) . ' |
'
. '| Location: | ' . $esc($location) . ' |
'
. '| Leave Type: | ' . $esc($category) . ' |
'
. '| Start Date: | ' . $esc($start) . ' |
'
. '| Expected End Date: | ' . $esc($end) . ' |
'
. '
'
. 'View Case in LOA Portal
';
if (!empty($email)) {
send_loa_email($sg_key, $employee_subject, $email, $name, $employee_body);
} else {
error_log('LOA notify: no employee email for case #' . $loa_id . ' — skipping employee notification');
}
send_loa_email($sg_key, $hr_subject, LOA_HR_EMAIL, 'Human Resources', $hr_body);
return;
case 'denied':
$employee_subject = 'Your Leave of Absence Request Has Been Denied';
$employee_body = 'Hi ' . $esc($name) . ',
'
. 'Unfortunately, your Leave of Absence request has been denied.
'
. ''
. '| Leave Type: | ' . $esc($category) . ' |
'
. '| Start Date: | ' . $esc($start) . ' |
'
. '
'
. 'If you have questions or would like to discuss this further, please contact hr@whitewatercw.com.
'
. 'Best regards,
Human Resources
';
$detail_url = 'https://intranet.whitewatercw.com/hr/loa.php?page=loa_edit&id=' . $loa_id;
$hr_subject = 'LOA Denied — ' . $esc($name);
$hr_body = 'LOA case #' . $loa_id . ' has been marked Denied.
'
. ''
. '| Employee: | ' . $esc($name) . ' |
'
. '| Location: | ' . $esc($location) . ' |
'
. '| Leave Type: | ' . $esc($category) . ' |
'
. '
'
. 'View Case in LOA Portal
';
if (!empty($email)) {
send_loa_email($sg_key, $employee_subject, $email, $name, $employee_body);
} else {
error_log('LOA notify: no employee email for case #' . $loa_id . ' — skipping employee notification');
}
send_loa_email($sg_key, $hr_subject, LOA_HR_EMAIL, 'Human Resources', $hr_body);
return;
case 'active':
$employee_subject = 'Your Leave of Absence Is Now Active';
$employee_body = 'Hi ' . $esc($name) . ',
'
. 'Your Leave of Absence is now active. Here is a summary:
'
. ''
. '| Leave Type: | ' . $esc($category) . ' |
'
. '| Start Date: | ' . $esc($start) . ' |
'
. '| Expected End Date: | ' . $esc($end) . ' |
'
. '
'
. 'We hope you have a smooth and restful leave. If you have any questions, please contact hr@whitewatercw.com.
'
. 'Best regards,
Human Resources
';
$detail_url = 'https://intranet.whitewatercw.com/hr/loa.php?page=loa_edit&id=' . $loa_id;
$hr_subject = 'LOA Active — ' . $esc($name);
$hr_body = 'LOA case #' . $loa_id . ' is now Active.
'
. ''
. '| Employee: | ' . $esc($name) . ' |
'
. '| Location: | ' . $esc($location) . ' |
'
. '| Leave Type: | ' . $esc($category) . ' |
'
. '| Start Date: | ' . $esc($start) . ' |
'
. '| Expected End Date: | ' . $esc($end) . ' |
'
. '
'
. 'View Case in LOA Portal
';
if (!empty($email)) {
send_loa_email($sg_key, $employee_subject, $email, $name, $employee_body);
} else {
error_log('LOA notify: no employee email for case #' . $loa_id . ' — skipping employee notification');
}
send_loa_email($sg_key, $hr_subject, LOA_HR_EMAIL, 'Human Resources', $hr_body);
return;
case 'returned':
$employee_subject = 'Your Return to Work Has Been Recorded';
$employee_body = 'Hi ' . $esc($name) . ',
'
. 'Your return to work has been recorded in our system. Welcome back!
'
. 'If anything seems incorrect or you have questions, please contact hr@whitewatercw.com.
'
. 'Best regards,
Human Resources
';
$detail_url = 'https://intranet.whitewatercw.com/hr/loa.php?page=loa_edit&id=' . $loa_id;
$hr_subject = 'LOA Returned — ' . $esc($name);
$hr_body = 'LOA case #' . $loa_id . ' has been marked Returned.
'
. ''
. '| Employee: | ' . $esc($name) . ' |
'
. '| Location: | ' . $esc($location) . ' |
'
. '| Leave Type: | ' . $esc($category) . ' |
'
. '
'
. 'View Case in LOA Portal
';
if (!empty($email)) {
send_loa_email($sg_key, $employee_subject, $email, $name, $employee_body);
} else {
error_log('LOA notify: no employee email for case #' . $loa_id . ' — skipping employee notification');
}
send_loa_email($sg_key, $hr_subject, LOA_HR_EMAIL, 'Human Resources', $hr_body);
return;
case 'extended':
$employee_subject = 'Your Leave of Absence Has Been Extended';
$employee_body = 'Hi ' . $esc($name) . ',
'
. 'Your Leave of Absence has been extended. Here is a summary of your updated leave:
'
. ''
. '| Leave Type: | ' . $esc($category) . ' |
'
. '| Start Date: | ' . $esc($start) . ' |
'
. '| Expected End Date: | ' . $esc($end) . ' |
'
. '
'
. 'If you have questions, please contact hr@whitewatercw.com.
'
. 'Best regards,
Human Resources
';
$detail_url = 'https://intranet.whitewatercw.com/hr/loa.php?page=loa_edit&id=' . $loa_id;
$hr_subject = 'LOA Extended — ' . $esc($name);
$hr_body = 'LOA case #' . $loa_id . ' has been marked Extended.
'
. ''
. '| Employee: | ' . $esc($name) . ' |
'
. '| Location: | ' . $esc($location) . ' |
'
. '| Leave Type: | ' . $esc($category) . ' |
'
. '| Expected End Date: | ' . $esc($end) . ' |
'
. '
'
. 'View Case in LOA Portal
';
if (!empty($email)) {
send_loa_email($sg_key, $employee_subject, $email, $name, $employee_body);
} else {
error_log('LOA notify: no employee email for case #' . $loa_id . ' — skipping employee notification');
}
send_loa_email($sg_key, $hr_subject, LOA_HR_EMAIL, 'Human Resources', $hr_body);
return;
case 'cancelled':
$employee_subject = 'Your Leave of Absence Has Been Cancelled';
$employee_body = 'Hi ' . $esc($name) . ',
'
. 'Your Leave of Absence has been cancelled.
'
. 'If you have questions or believe this was done in error, please contact hr@whitewatercw.com.
'
. 'Best regards,
Human Resources
';
$detail_url = 'https://intranet.whitewatercw.com/hr/loa.php?page=loa_edit&id=' . $loa_id;
$hr_subject = 'LOA Cancelled — ' . $esc($name);
$hr_body = 'LOA case #' . $loa_id . ' has been marked Cancelled.
'
. ''
. '| Employee: | ' . $esc($name) . ' |
'
. '| Location: | ' . $esc($location) . ' |
'
. '| Leave Type: | ' . $esc($category) . ' |
'
. '
'
. 'View Case in LOA Portal
';
if (!empty($email)) {
send_loa_email($sg_key, $employee_subject, $email, $name, $employee_body);
} else {
error_log('LOA notify: no employee email for case #' . $loa_id . ' — skipping employee notification');
}
send_loa_email($sg_key, $hr_subject, LOA_HR_EMAIL, 'Human Resources', $hr_body);
return;
default:
return;
```
- [ ] **Step 3: Verify file syntax**
```bash
php -l app/HR/LOA/loa_notifications.php
```
Expected: `No syntax errors detected`
---
## Task 3: Wire status-change detection into loa_save.php
**Files:**
- Modify: `app/HR/LOA/loa_save.php` — `handle_update()` function (lines ~183–273)
Two insertions: one before the SET clause loop, one inside the execute success branch.
- [ ] **Step 1: Add status capture + pre-fetch after the `$id` validation block**
In `handle_update()`, after:
```php
if ($id <= 0) {
http_response_code(400);
echo json_encode(['error' => 'Invalid LOA ID.']);
exit;
}
```
Insert:
```php
// Capture new status before building SET clause (for post-save notification)
$new_status_post = isset($_POST['leave_status']) ? trim($_POST['leave_status']) : null;
// Fetch current status only if leave_status is being updated
$prev_status = null;
if ($new_status_post !== null) {
$prev_stmt = $conn->prepare("SELECT leave_status FROM loa_form WHERE id = ? LIMIT 1");
$prev_stmt->bind_param('i', $id);
$prev_stmt->execute();
$prev_row = $prev_stmt->get_result()->fetch_assoc();
$prev_stmt->close();
$prev_status = $prev_row['leave_status'] ?? null;
}
```
- [ ] **Step 2: Add notification call inside the execute success branch**
Find the existing success branch:
```php
if ($stmt->execute()) {
echo json_encode(['success' => true]);
} else {
```
Replace with:
```php
if ($stmt->execute()) {
// Fire status-change notification if applicable
if ($new_status_post !== null && $new_status_post !== $prev_status) {
$notif_statuses = ['approved', 'denied', 'active', 'returned', 'extended', 'cancelled'];
if (in_array($new_status_post, $notif_statuses, true)) {
$case_stmt = $conn->prepare("
SELECT full_name, email, job_title, location_name,
leave_category, start_date, expected_end_date
FROM loa_form WHERE id = ? LIMIT 1
");
$case_stmt->bind_param('i', $id);
$case_stmt->execute();
$case_row = $case_stmt->get_result()->fetch_assoc();
$case_stmt->close();
if ($case_row) {
send_loa_notification($new_status_post, array_merge($case_row, ['loa_id' => $id]));
}
}
}
echo json_encode(['success' => true]);
} else {
```
- [ ] **Step 3: Verify file syntax**
```bash
php -l app/HR/LOA/loa_save.php
```
Expected: `No syntax errors detected`
---
## Task 4: Create loa_send_email.php and register in router
**Files:**
- Create: `app/HR/LOA/loa_send_email.php`
- Modify: `public/hr/loa.php`
- [ ] **Step 1: Create `app/HR/LOA/loa_send_email.php`**
```php
'Method not allowed.']);
exit;
}
$servername = envv('new_servername');
$username = envv('new_username');
$password = envv('new_password');
$dbname = envv('new_dbname');
$port = envv('DB_PORT');
if (!$servername || !$username || $password === null || !$dbname) {
http_response_code(500);
echo json_encode(['error' => 'Server configuration error.']);
exit;
}
// Permission check — Edit only
$pdo = new \PDO(
"mysql:host=$servername;port=$port;dbname=$dbname;charset=utf8mb4",
$username, $password,
[\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC]
);
$emp_id = $_SESSION['user_id'] ?? 0;
$perm_stmt = $pdo->prepare("SELECT label FROM V_employee_permissions WHERE form_name = 'LOA' AND id = ?");
$perm_stmt->execute([$emp_id]);
$perm = $perm_stmt->fetch();
if (!$perm || ($perm['label'] ?? '') !== 'Edit') {
http_response_code(403);
echo json_encode(['error' => 'Permission denied.']);
exit;
}
$loa_id = isset($_POST['loa_id']) ? (int)$_POST['loa_id'] : 0;
$to_email = trim($_POST['to_email'] ?? '');
$subject = trim($_POST['subject'] ?? '');
$body = trim($_POST['body'] ?? '');
$sent_by = trim($_SESSION['full_name'] ?? $_SESSION['user'] ?? 'HR');
if ($loa_id <= 0 || empty($to_email) || empty($subject) || empty($body)) {
http_response_code(400);
echo json_encode(['error' => 'All fields are required.']);
exit;
}
if (!filter_var($to_email, FILTER_VALIDATE_EMAIL)) {
http_response_code(400);
echo json_encode(['error' => 'Invalid email address.']);
exit;
}
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset('utf8mb4');
if ($conn->connect_error) {
http_response_code(500);
echo json_encode(['error' => 'DB connection error.']);
exit;
}
$sg_key = envv('SENDGRID_API_KEY');
$send_status = 'sent';
$error_message = null;
try {
$sendgrid = new \SendGrid($sg_key);
$mail = new \SendGrid\Mail\Mail();
$mail->setFrom(LOA_FROM_EMAIL, LOA_FROM_NAME);
$mail->setSubject($subject);
$mail->addTo($to_email);
$mail->addContent(
'text/html',
loa_email_wrap(
$subject,
'' . nl2br(htmlspecialchars($body, ENT_QUOTES | ENT_HTML5, 'UTF-8')) . '
'
)
);
$response = $sendgrid->send($mail);
if ($response->statusCode() >= 400) {
$send_status = 'failed';
$error_message = 'SendGrid ' . $response->statusCode() . ': ' . substr($response->body(), 0, 500);
}
} catch (\Exception $e) {
$send_status = 'failed';
$error_message = $e->getMessage();
}
$log_stmt = $conn->prepare("
INSERT INTO loa_email_log (loa_form_id, to_email, subject, body, sent_by, sent_at, status, error_message)
VALUES (?, ?, ?, ?, ?, NOW(), ?, ?)
");
$log_stmt->bind_param('issssss', $loa_id, $to_email, $subject, $body, $sent_by, $send_status, $error_message);
$log_stmt->execute();
$log_stmt->close();
$conn->close();
if ($send_status === 'failed') {
http_response_code(500);
echo json_encode(['error' => 'Email failed to send. ' . ($error_message ?? '')]);
exit;
}
echo json_encode(['success' => true, 'sent_at' => date('Y-m-d H:i:s'), 'sent_by' => $sent_by]);
```
- [ ] **Step 2: Register `loa_send_email` in the router**
In `public/hr/loa.php`, find:
```php
$allowed = ['loa', 'loa_new', 'loa_edit', 'loa_save', 'loa_docs_save', 'loa_employee_search'];
```
Replace with:
```php
$allowed = ['loa', 'loa_new', 'loa_edit', 'loa_save', 'loa_docs_save', 'loa_employee_search', 'loa_send_email'];
```
- [ ] **Step 3: Verify both files**
```bash
php -l app/HR/LOA/loa_send_email.php && php -l public/hr/loa.php
```
Expected: `No syntax errors detected` for both.
---
## Task 5: Add HR Communications section to loa_edit.php
**Files:**
- Modify: `app/HR/LOA/loa_edit.php`
Three changes: (A) load email log in PHP block, (B) add Section 7 HTML, (C) add send button JS.
- [ ] **Step 1: Add email log query in the PHP data block**
Find the doc close + conn close block (around line 87–88):
```php
$doc_stmt->close();
$conn->close();
```
Replace with:
```php
$doc_stmt->close();
// Load email log for Section 7
$email_log_stmt = $conn->prepare("
SELECT to_email, subject, sent_by, sent_at
FROM loa_email_log
WHERE loa_form_id = ?
ORDER BY sent_at DESC
");
$email_log_stmt->bind_param('i', $loa_id);
$email_log_stmt->execute();
$email_log_result = $email_log_stmt->get_result();
$email_log = [];
while ($log_row = $email_log_result->fetch_assoc()) {
$email_log[] = $log_row;
}
$email_log_stmt->close();
$conn->close();
```
- [ ] **Step 2: Add Section 7 HTML**
Find the closing `` at the very bottom of the HTML (before the `` script block):
```php
```
Insert Section 7 between those two divs and the script block:
```php
HR Communications
| Sent At |
To |
Subject |
Sent By |
|
|
|
|
| No emails sent. |
```
- [ ] **Step 3: Add send button JS inside the canEdit script block**
In the `` script block, after the `showPageMessage` function definition, add:
```javascript
// ---- HR Communications — ad-hoc email send ----
document.getElementById('send-email-btn').addEventListener('click', function () {
const to = document.getElementById('email-to').value.trim();
const subject = document.getElementById('email-subject').value.trim();
const body = document.getElementById('email-body').value.trim();
const btn = this;
const status = document.getElementById('send-email-status');
if (!to || !subject || !body) {
status.textContent = 'All fields are required.';
status.style.color = '#dc3545';
return;
}
const formData = new FormData();
formData.append('loa_id', LOA_ID);
formData.append('to_email', to);
formData.append('subject', subject);
formData.append('body', body);
btn.disabled = true;
status.textContent = 'Sending...';
status.style.color = '#0066cc';
fetch('loa.php?page=loa_send_email', { method: 'POST', body: formData })
.then(r => r.json())
.then(data => {
btn.disabled = false;
if (data.success) {
status.textContent = '✓ Sent';
status.style.color = '#28a745';
setTimeout(() => { status.textContent = ''; }, 4000);
document.getElementById('email-subject').value = '';
document.getElementById('email-body').value = '';
const no_row = document.getElementById('no-emails-row');
if (no_row) no_row.remove();
const tr = document.createElement('tr');
[data.sent_at, to, subject, data.sent_by].forEach(text => {
const td = document.createElement('td');
td.textContent = text;
tr.appendChild(td);
});
document.getElementById('email-log-tbody').prepend(tr);
showPageMessage('Email sent successfully.', 'success');
} else {
status.textContent = '✗ Failed';
status.style.color = '#dc3545';
showPageMessage(data.error || 'Send failed.', 'error');
}
})
.catch(err => {
btn.disabled = false;
status.textContent = '✗ Error';
status.style.color = '#dc3545';
showPageMessage('Network error: ' + err.message, 'error');
});
});
```
- [ ] **Step 4: Verify file syntax**
```bash
php -l app/HR/LOA/loa_edit.php
```
Expected: `No syntax errors detected`
---
## Task 6: Smoke test
No formal test framework in this project — manual verification against local WAMP stack.
- [ ] **Step 1: Test status-change notification (approved)**
Open a test LOA case in `loa_edit.php`. Change `Leave Status` to `Approved` and click **Save Status**.
Expected:
- Section save returns `✓ Saved`
- `loa_email_log` has NO row (status-change emails go through `send_loa_notification`, not the log table — that's for ad-hoc only)
- Check server `error_log` — no SendGrid errors (or SendGrid 202 Accepted if SENDGRID_API_KEY is set in dev)
- [ ] **Step 2: Verify notification does not fire on same-status save**
Save the same case with `Leave Status` still set to `approved` (no change).
Expected: no new email attempt in error_log.
- [ ] **Step 3: Test ad-hoc compose — success path**
In `loa_edit.php` Section 7, fill in To (valid email), Subject, Message. Click **Send Email**.
Expected:
- Button shows `Sending...` → `✓ Sent`
- New row appears in the log table without page reload
- `SELECT * FROM loa_email_log ORDER BY id DESC LIMIT 1;` shows the row with `status = 'sent'`
- [ ] **Step 4: Test ad-hoc compose — validation**
Submit with empty Subject.
Expected: `All fields are required.` shown in red next to button. No fetch fired.
- [ ] **Step 5: Test ad-hoc compose — invalid email**
Submit with `To: notanemail`.
Expected: server returns `{"error":"Invalid email address."}`, shown as page error message. Row logged as `failed` in `loa_email_log`.
- [ ] **Step 6: Test read-only view**
Log in as a user with ReadOnly LOA permission. Open a case in `loa_edit.php`.
Expected: compose panel not rendered, log table is visible (read-only).