5.2.0 · #324 · User guide: Report Packs - the Starters & leavers blocks · Module: Starters and leavers · Engine: Report Packs Developer Guide

Four block handlers in includes/report_packs/blocks_starters.php read jml_cases and the case's steps (the subtasks of its task) the same way the Starters register does. A case is in the period by its date (effective_date), never by when it was opened.

Nothing new in the database. A case and its steps:

jml_cases  (id, tenant_id, kind 'starter|mover|leaver', person_name, job_title,
            effective_date DATE,          -- first day, move date or last day
            status 'open|done|cancelled', template_id, parent_task_id, owner_analyst_id)
tasks      (id, parent_task_id, status_id, due_date DATE, completed_datetime DATETIME,
            assigned_analyst_id, assigned_team_id)   -- a step = a subtask of the case's task
task_statuses (id, is_closed)                         -- a step is done when its status is closed

Progress is never stored on the case. It is counted from the steps every time, exactly as StartersService::listCases() counts it:

function rpStartersProgressCols(): string
{
    return "(SELECT COUNT(*) FROM tasks s WHERE s.parent_task_id = c.parent_task_id) AS steps_total,
            (SELECT COUNT(*) FROM tasks s JOIN task_statuses ss ON ss.id = s.status_id
              WHERE s.parent_task_id = c.parent_task_id AND ss.is_closed = 1) AS steps_done";
}

One helper builds the WHERE clause for every period block:

function rpStartersWhere(PDO $conn, int $analystId, array $range, $tenant,
                         string $kind = '', bool $withCancelled = false): array
{
    [$tSql, $tArgs] = rpTenantClause($conn, $analystId, $tenant, 'c.tenant_id');  // NULL = Default company
    $sql  = ' WHERE c.effective_date BETWEEN ? AND ?';                              // the person's date
    $args = [$range['from_date'], $range['to_date']];
    if (!$withCancelled) $sql .= " AND c.status <> 'cancelled'";
    if ($kind !== '' && $kind !== 'all' && JmlKind::tryFrom($kind)) { $sql .= ' AND c.kind = ?'; $args[] = $kind; }
    return [$sql . $tSql, array_merge($args, $tArgs)];
}

• 🔑 The date, not the opening. "Starters this month" in a pack means the people starting this month. A case opened in January for a March start is a March figure.
• Cancelled cases are left out of everything except the chart grouped by status, which is the one place they belong ($withCancelled = true there only).
• The company comes from rpTenantClause(), like every other block, so a pack set to one company never shows another's cases.
• Access: each handler declares 'module' => 'starters'. The engine refuses the block for a viewer without Starters & leavers (analystCanAccessModule() in rpBlockData()), and a shared pack shows that viewer a placeholder instead of the figures.

Of the period's cases whose date has come (effective_date <= today) and that have steps, how many had every step closed by the end of their date. It is worked out in one query as "no step that is still open, has no completion time, or was completed on or after the day after the date":

SELECT COUNT(*) AS total,
       SUM(CASE WHEN NOT EXISTS (
               SELECT 1 FROM tasks s LEFT JOIN task_statuses ss ON ss.id = s.status_id
                WHERE s.parent_task_id = c.parent_task_id
                  AND (COALESCE(ss.is_closed, 0) = 0 OR s.completed_datetime IS NULL
                       OR s.completed_datetime >= DATE_ADD(c.effective_date, INTERVAL 1 DAY))
           ) THEN 1 ELSE 0 END) AS ready
  FROM jml_cases c
 WHERE ...period... AND c.effective_date <= ?            -- today
   AND EXISTS (SELECT 1 FROM tasks s2 WHERE s2.parent_task_id = c.parent_task_id)

• A case with no steps is counted neither way - it would otherwise always be "ready".
• A case dated in the future is not counted yet - it has not had its chance.
• ⚠️ completed_datetime is UTC and effective_date is the organisation's calendar day, so a step closed in the last hour or two of the day can land either side. That is accepted for a monthly figure; do not "fix" it by converting one row at a time in PHP.

Open steps (COALESCE(ss.is_closed, 0) = 0) of open cases with due_date < today. This is a snapshot of now, so the block sets 'snapshot' => true and the pack says so under its title. The summary's Steps overdue tile uses the same rule, so the tile and the table always agree. Days late are worked out in PHP from the two dates. The pill is amber up to a week late and red after that.

To add a fifth block, follow Report Packs Internals 3 - adding a block: a data function here, a handler in rpHandlers(), a toolbox item in rpToolboxItems() with 'area' => 'starters', and packs.tool.<key>.title/desc in lang/en/reporting.php. tests/report-packs-blocks.php then runs it with every option automatically.

• TRAP: filter by effective_date, never created_datetime. Every figure here is about when people join or leave. A case opened early would otherwise count in the wrong month.
• TRAP: never store progress on the case. Count it from the subtasks, as the register does. A stored count would drift the first time someone closes a step from the Tasks module.
• TRAP: the summary's overdue tile and the overdue table must use the same rule (open step, open case, due before today). If one changes, change both.

• tests/report-packs-starters.php (17, prtest only: FREEITSM_TEST_DATABASE=freeitsm_prtest). It seeds six cases dated in 2001, so no real case falls in its period, inside one transaction that is always rolled back. It checks:
  • the kind counts, with a cancelled case left out;
  • Ready on the day as 1 of 3: one step closed on the day counts as ready, a step closed the next day does not, and a case with no steps is not counted;
  • overdue rising by exactly 2: the cancelled case's step and a step not yet due are not counted;
  • the chart by kind, by status (with the cancelled case) and by template ("No template");
  • the list: its order, progress 50% (1/2) and -, Open only, the kind filter and the row limit;
  • the overdue table: 10 days late, the kind filter, and nothing left behind.
• tests/report-packs-blocks.php (45, read-only) runs every handler, including these four, with every option value, and checks every label resolves.

See also: Report Packs Developer Guide · Starters and leavers