# Collections Process Status Enhancement — Implementation Plan > **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task. **Goal:** Add a collections process tracking workflow to `admin/collections.cgi` so dmoss can update invoice status (no contact, pending review, payment arrangements, paid, delete request, send for review) with automated email notifications, plus filter invoices by process status. **Architecture:** Single new ENUM column `collections_process_status` on `career_center.invoices`. New AJAX action `update_process_status` in `collections.cgi` handles all 6 statuses. Three statuses trigger email notifications via `html_notify()`. Frontend gets a status dropdown in the detail panel and a filter dropdown in the filter row. **Tech Stack:** Perl CGI, MySQL, JavaScript (vanilla), Tailwind CSS, `html_notify()` for email --- ## Context for Implementer - **File:** `/var/www/vhosts/hbcuconnect.com/httpdocs/admin/collections.cgi` (~1075 lines) - **Pattern:** Single-page AJAX app. Perl subroutines return JSON via `json_ok()` / `json_err()`. Frontend uses `fetch()` and `renderDetail()` to build HTML. - **DB handle:** `$dbh` connects to `career_center` database - **Auth:** `$USER` = `$ENV{'REMOTE_USER'}`, `$USER_INFO` = sales_contacts row - **Email:** `html_notify($to, $from, $subject, $text, $from_name, $cc, $html)` from `niceMail.pl` - **Logging:** `&logEvent($employer_id, $contact, $summary, $event, $dbh, $priority)` from `commonFuncs.pm` - **CRITICAL:** After editing files, run `chown hbcuconnect:psacln` on every modified file - **CRITICAL:** In `qq~` strings, escape `@` as `\@`, `$` in JS regex as `\$` --- ### Task 1: Add Database Column **Step 1: Run ALTER TABLE** ```bash mysql -h newdb.hbcuconnect.com -u $(perl -e 'require "lib/config.pl"; my $c = getConfiguration(); print $c->{CC_DB_USER}') -p$(perl -e 'require "lib/config.pl"; my $c = getConfiguration(); print $c->{CC_DB_PASS}') career_center -e " ALTER TABLE invoices ADD COLUMN collections_process_status ENUM('no_contact','pending_review','payment_arrangements','paid','delete_requested','sent_for_review') DEFAULT NULL AFTER collections_protected_by; " ``` Run from `/var/www/vhosts/hbcuconnect.com/httpdocs/admin/`. **Step 2: Verify column exists** ```bash mysql -h newdb.hbcuconnect.com -u [user] -p[pass] career_center -e "DESCRIBE invoices collections_process_status;" ``` Expected: One row showing the ENUM column with NULL default. --- ### Task 2: Add Action Dispatch + `updateProcessStatus` Subroutine **Files:** - Modify: `admin/collections.cgi:30-40` (action dispatch) - Modify: `admin/collections.cgi` (add new subroutine after `markPaid`, before `printMain`) **Step 1: Add dispatch entry** In the action dispatch block (line 30-40), add a new `elsif` before the `else`: ```perl elsif ($action eq 'update_process_status') { updateProcessStatus(); } ``` Add it right before the `else { printMain(); }` line. **Step 2: Write the `updateProcessStatus` subroutine** Insert after the `markPaid` subroutine (after line 578) and before `printMain`: ```perl ###################################################################### #### AJAX: update_process_status ###################################################################### sub updateProcessStatus { my $invoice_id = int($q->param('invoice_id') || 0); my $new_status = $q->param('process_status') || ''; my $justification = $q->param('justification') || ''; json_err('Missing invoice_id') unless $invoice_id; my %valid = map { $_ => 1 } qw(no_contact pending_review payment_arrangements paid delete_requested sent_for_review); json_err('Invalid status') unless $valid{$new_status}; # Load invoice + employer data my $sth = $dbh->prepare(" SELECT a.*, b.company_name, b.email AS contact_email, b.first_name, b.last_name, b.employer_id, DATE_FORMAT(a.due_date, '%m/%d/%Y') AS due_formatted, DATEDIFF(CURDATE(), a.due_date) AS days_overdue FROM invoices a JOIN employer_data b ON a.employer_id = b.employer_id WHERE a.invoice_id = ? "); $sth->execute($invoice_id); my $inv = $sth->fetchrow_hashref; json_err('Invoice not found') unless $inv; my $client_name = $inv->{actual_client} || $inv->{company_name} || 'Unknown'; my $total_formatted = sprintf('%.2f', $inv->{total_price}); my $original_sales = $inv->{split_commission} || $inv->{sales_person} || ''; # Map status to display label my %labels = ( no_contact => 'No Contact Made', pending_review => 'Pending Client Review', payment_arrangements => 'Payment Arrangements Made', paid => 'Paid', delete_requested => 'Delete Requested', sent_for_review => 'Sent for Review', ); my $label = $labels{$new_status}; # Handle each status if ($new_status eq 'paid') { # Mark as paid $sth = $dbh->prepare("UPDATE invoices SET paid_date = CURDATE(), collections_process_status = 'paid' WHERE invoice_id = ?"); $sth->execute($invoice_id); &logEvent($inv->{employer_id}, $USER, "Payment Received", "Invoice #$invoice_id marked as paid (\$$total_formatted) via Collections Manager", $dbh); # Email full team: wrmoss, dmoss, original sales rep _notifyPaid($inv, $total_formatted, $client_name, $original_sales); json_ok({ message => "Invoice #$invoice_id marked as PAID. Team notified." }); } elsif ($new_status eq 'delete_requested') { # Update status, email wrmoss for approval $sth = $dbh->prepare("UPDATE invoices SET collections_process_status = 'delete_requested' WHERE invoice_id = ?"); $sth->execute($invoice_id); &logEvent($inv->{employer_id}, $USER, "Delete Requested", "Invoice #$invoice_id - deletion requested via Collections Manager. Justification: $justification", $dbh); _notifyDeleteRequest($inv, $total_formatted, $client_name, $justification); json_ok({ message => "Delete request sent to wrmoss for Invoice #$invoice_id." }); } elsif ($new_status eq 'sent_for_review') { json_err('Justification is required for review requests') unless $justification; $sth = $dbh->prepare("UPDATE invoices SET collections_process_status = 'sent_for_review' WHERE invoice_id = ?"); $sth->execute($invoice_id); &logEvent($inv->{employer_id}, $USER, "Sent for Review", "Invoice #$invoice_id sent for review. Justification: $justification", $dbh); _notifySentForReview($inv, $total_formatted, $client_name, $original_sales, $justification); json_ok({ message => "Invoice #$invoice_id sent for review. wrmoss and sales rep notified." }); } else { # Simple status update (no_contact, pending_review, payment_arrangements) $sth = $dbh->prepare("UPDATE invoices SET collections_process_status = ? WHERE invoice_id = ?"); $sth->execute($new_status, $invoice_id); &logEvent($inv->{employer_id}, $USER, "Collections Status Update", "Invoice #$invoice_id status changed to: $label", $dbh); json_ok({ message => "Invoice #$invoice_id status updated to: $label" }); } } ``` **Step 3: Syntax check** ```bash cd /var/www/vhosts/hbcuconnect.com/httpdocs/admin && perl -c collections.cgi ``` Expected: `collections.cgi syntax OK` --- ### Task 3: Add Email Notification Helper Subroutines **Files:** - Modify: `admin/collections.cgi` (add 3 helper subs right after `updateProcessStatus`) Insert these three subroutines immediately after `updateProcessStatus`: **Step 1: `_notifyPaid` — full team notification** ```perl sub _notifyPaid { my ($inv, $total_formatted, $client_name, $original_sales) = @_; my $invoice_id = $inv->{invoice_id}; my $days = $inv->{days_overdue} || 0; my $html = qq~
HBCU Connect

Invoice Collected!

Invoice #$invoice_id — $client_name

\$$total_formatted
Amount Collected
$days
Days Overdue at Collection
Payment received via Collections
Marked as paid by $USER_INFO->{name} in the Collections Manager. Commission split with: $original_sales.
View Invoice

Automated notification • HBCU Connect Collections System

~; my $subject = "COLLECTED: Invoice #$invoice_id - $client_name (\$$total_formatted)"; # Send to wrmoss html_notify('wrmoss\@hbcuconnect.com', 'collections\@hbcuconnect.com', $subject, $html, 'HBCU Collections'); # Send to dmoss html_notify('dmoss\@hbcuconnect.com', 'collections\@hbcuconnect.com', $subject, $html, 'HBCU Collections'); # Send to original sales rep if ($original_sales && $original_sales ne 'dmoss' && $original_sales ne 'wrmoss') { my $sth = $dbh->prepare('SELECT email FROM sales_contacts WHERE login_id = ?'); $sth->execute($original_sales); my ($rep_email) = $sth->fetchrow_array; if ($rep_email) { html_notify($rep_email, 'collections\@hbcuconnect.com', $subject, $html, 'HBCU Collections'); } } } ``` **Step 2: `_notifyDeleteRequest` — wrmoss approval request** ```perl sub _notifyDeleteRequest { my ($inv, $total_formatted, $client_name, $justification) = @_; my $invoice_id = $inv->{invoice_id}; $justification ||= 'No justification provided'; my $html = qq~
HBCU Connect

Invoice Deletion Request

Invoice #$invoice_id — $client_name

\$$total_formatted
Invoice Amount
$inv->{days_overdue}
Days Overdue
Justification from $USER_INFO->{name}:
$justification
Review Invoice

Action required • HBCU Connect Collections System

~; html_notify('wrmoss\@hbcuconnect.com', 'collections\@hbcuconnect.com', "DELETE REQUEST: Invoice #$invoice_id - $client_name (\$$total_formatted)", $html, 'HBCU Collections'); } ``` **Step 3: `_notifySentForReview` — wrmoss + original sales rep** ```perl sub _notifySentForReview { my ($inv, $total_formatted, $client_name, $original_sales, $justification) = @_; my $invoice_id = $inv->{invoice_id}; my $html = qq~
HBCU Connect

Invoice Sent for Review

Invoice #$invoice_id — $client_name

\$$total_formatted
Invoice Amount
$inv->{days_overdue}
Days Overdue
Review notes from $USER_INFO->{name}:
$justification
Review Invoice   View Client

Review requested • HBCU Connect Collections System

~; my $subject = "REVIEW: Invoice #$invoice_id - $client_name (\$$total_formatted)"; # Send to wrmoss html_notify('wrmoss\@hbcuconnect.com', 'collections\@hbcuconnect.com', $subject, $html, 'HBCU Collections'); # Send to original sales rep if ($original_sales && $original_sales ne 'wrmoss') { my $sth = $dbh->prepare('SELECT email FROM sales_contacts WHERE login_id = ?'); $sth->execute($original_sales); my ($rep_email) = $sth->fetchrow_array; if ($rep_email) { html_notify($rep_email, 'collections\@hbcuconnect.com', $subject, $html, 'HBCU Collections'); } } } ``` **Step 4: Syntax check** ```bash cd /var/www/vhosts/hbcuconnect.com/httpdocs/admin && perl -c collections.cgi ``` --- ### Task 4: Update Backend Queries to Include + Filter by Process Status **Files:** - Modify: `admin/collections.cgi` — `getQueue()`, `getClaimable()`, `getInvoiceDetail()` **Step 1: Update `getQueue()` (line 140)** Add `collections_process_status` filter param parsing after the `$amt_max` line (after line 145): ```perl my $process_status = $q->param('process_status') || ''; ``` Add filter condition after the `$amt_max` block (after line 178): ```perl if ($process_status) { push @where, "a.collections_process_status = ?"; push @bind, $process_status; } ``` Add `a.collections_process_status` to the SELECT column list (line 184, after `a.last_reminder,`): ``` a.collections_process_status, ``` Add `collections_process_status` to the hash pushed into `@invoices` (after the `campaign_brief` line, line 215): ```perl collections_process_status => $row->{collections_process_status} || '', ``` **Step 2: Update `getClaimable()` (line 225)** Same changes — add `process_status` param, filter, SELECT column, and hash field. Mirror the exact same pattern as `getQueue`. Add after line 230: ```perl my $process_status = $q->param('process_status') || ''; ``` Add after line 264 (the `$amt_max` block): ```perl if ($process_status) { push @where, "a.collections_process_status = ?"; push @bind, $process_status; } ``` Add `a.collections_process_status` to the SELECT (line 271, after `a.collections_status,`): ``` a.collections_process_status, ``` Add to the hash (after `collections_status` line 298): ```perl collections_process_status => $row->{collections_process_status} || '', ``` **Step 3: Update `getInvoiceDetail()` (line 308)** The SELECT already uses `a.*` so the column is included. Just add it to the JSON response hash. In the `json_ok` invoice hash (around line 363, after the `collections_status` line): ```perl collections_process_status => $inv->{collections_process_status} || '', ``` **Step 4: Syntax check** ```bash cd /var/www/vhosts/hbcuconnect.com/httpdocs/admin && perl -c collections.cgi ``` --- ### Task 5: Update Frontend — Process Status Filter Dropdown **Files:** - Modify: `admin/collections.cgi` — the filter row HTML (around line 648-677) and `getFilterParams()` JS function **Step 1: Add process status dropdown to filter row** In the filter row div (around line 649-677), add a new ` ``` **Step 2: Update `getFilterParams()` JS function (line 763)** Add after the sort param (before the `return`): ```javascript const processStatus = document.getElementById('f-process-status').value; if (processStatus) params.set('process_status', processStatus); ``` **Step 3: Update `clearFilters()` JS function (line 786)** Add before `loadCurrentTab()`: ```javascript document.getElementById('f-process-status').value = ''; ``` --- ### Task 6: Update Frontend — Process Status Dropdown in Detail Panel **Files:** - Modify: `admin/collections.cgi` — the `renderDetail()` JS function (line 882) **Step 1: Add process status section to `renderDetail()`** Insert AFTER the header block (after the closing `` for the gradient header, around line 898) and BEFORE the Quick Actions bar (before the `// Quick Actions` comment around line 901): ```javascript // Process Status Section const statusOptions = [ { value: 'no_contact', label: 'No Contact Made', color: 'gray' }, { value: 'pending_review', label: 'Pending Client Review', color: 'blue' }, { value: 'payment_arrangements', label: 'Payment Arrangements Made', color: 'amber' }, { value: 'paid', label: 'Paid', color: 'green' }, { value: 'delete_requested', label: 'Delete Invoice', color: 'red' }, { value: 'sent_for_review', label: 'Send for Review', color: 'purple' }, ]; const currentStatus = inv.collections_process_status || ''; const currentLabel = statusOptions.find(s => s.value === currentStatus); html += '
'; html += '
'; html += '

Collections Process Status

'; if (currentLabel) { html += '' + currentLabel.label + ''; } else { html += 'Not Set'; } html += '
'; html += '
'; html += ''; html += ''; html += '
'; // Justification textarea (hidden, shown for review/delete) html += ''; html += '
'; ``` **Step 2: Add JS to show/hide justification field** Add an onchange handler to the select. Replace the select line with: ```javascript html += '