01 — Data Sources Inventory

Document: 01_DATA_SOURCES_INVENTORY.md Module: KPI Matrix (Employee Performance Management) Author: AI Research Agent Date: February 2026 Status: Implemented — data-source inventory retained as reference (consumed by Kpi_calculation_engine; see 02 Appendix A)

1. Overview

This document catalogs every existing data point in ERPat that can serve as input for KPI calculations. Each source is documented with its model, table, key columns, available aggregation methods, and KPI applicability.

Total Data Sources Identified: 15 existing models (no new data collection needed for core KPIs)

2. Attendance Data

2.1 Attendance_model (ci_attendance)

File: application/models/Attendance_model.php (719 lines)

Key Methods:

• get_details($options) — Lines 258-400. Full SQL JOIN with attendance_metrics, users, schedule, team. Supports filters: id, log_type, status, date range (start_date/end_date), user_id, department_id, active_only, allowed_members, access_type.
• get_summary_details($options) — Lines 401+. Simplified aggregate query for summaries.

2.2 Attendance_metrics_model (ci_attendance_metrics)

File: application/models/Attendance_metrics_model.php (53 lines)

18 Metric Columns (returned by get_metric_fields()):

KPI Formulas from Attendance:

Attendance Rate       = (days_worked / scheduled_days) × 100
Punctuality Score     = ((scheduled_days - days_late) / scheduled_days) × 100
Undertime Rate        = (SUM(under) / SUM(schedule)) × 100  [lower = better]
Schedule Compliance   = (SUM(worked) / SUM(schedule)) × 100
Overtime Ratio        = (SUM(all_ot) / SUM(worked)) × 100  [context-dependent]

3. Task & Project Data

3.1 Tasks_model (ci_tasks)

File: application/models/Tasks_model.php (629 lines)

Critical Method — get_task_statistics() (Lines 452-475):

SELECT COUNT(tasks.id) AS total, task_status.id AS status_id,
       task_status.key_name, task_status.title, task_status.color
FROM tasks
LEFT JOIN task_status ON task_status.id = tasks.status_id
WHERE tasks.deleted = 0 AND tasks.assigned_to = ?
GROUP BY tasks.status_id

Returns: {total, status_id, key_name, title, color} — Perfect for KPI dashboard pie charts and completion rates.

KPI Formulas from Tasks:

Task Completion Rate  = (completed_tasks / total_assigned_tasks) × 100
Points Completion     = (completed_points / total_assigned_points) × 100
On-Time Delivery      = (tasks_completed_before_deadline / total_completed) × 100
Task Velocity         = completed_tasks / period_days
Average Task Duration = SUM(DATEDIFF(completed_date, start_date)) / completed_count

3.2 Projects_model (ci_projects)

File: application/models/Projects_model.php (337 lines)

Critical Computed Fields (Lines 12-105 in get_details()):

SUM(tasks.points) AS total_points,
SUM(IF(tasks.status_id = 3, tasks.points, 0)) AS completed_points

Critical Method — count_project_status() (Lines 107-140):

Returns {open: N, completed: N} per user via project_members join.

KPI Formulas from Projects:

Project Progress %    = (completed_points / total_points) × 100
Project Delivery Rate = (completed_projects / total_projects) × 100
Project On-Time Rate  = (projects_completed_by_deadline / total_completed) × 100

4. Ticket/Support Data

4.1 Tickets_model (ci_tickets)

File: application/models/Tickets_model.php (379 lines)

Overdue Calculation Logic (from get_details()):

-- Overdue if: current time > due_date OR current time > (created_at + ticket_type_duration hours)
IF(tickets.due_date IS NOT NULL,
   IF(NOW() > tickets.due_date, 1, 0),
   IF(NOW() > DATE_ADD(tickets.created_at, INTERVAL ticket_types.duration HOUR), 1, 0)
) AS is_overdue

Available Aggregation Methods:

• count_new_tickets() — Total new tickets
• get_ticket_status_info() — Grouped by status with counts
• count_tickets() — Filtered count
• count_member_tickets() — Per-member count

KPI Formulas from Tickets:

Ticket Resolution Rate    = (resolved_tickets / assigned_tickets) × 100
Average Resolution Time   = SUM(resolution_time) / resolved_count
SLA Compliance           = (tickets_resolved_within_duration / total_resolved) × 100
Ticket Points Earned     = SUM(ticket_type_points for resolved tickets)
Overdue Ticket Rate      = (overdue_tickets / total_tickets) × 100  [lower = better]
First Response Time      = AVG(TIMESTAMPDIFF(first_response, created_at))

5. Timesheet Data

5.1 Timesheets_model (ci_project_time)

File: application/models/Timesheets_model.php (311 lines)

Critical Method — get_summary_details() (Lines 100-185):

SUM(TIMESTAMPDIFF(SECOND, start_time, end_time)) + SUM(ROUND(hours * 60) * 60) AS total_duration

Supports group_by parameter with values: "member", "task", "project"

Critical Method — get_timesheet_statistics() (Lines 241-280):

Daily hours aggregation with timezone conversion support. Returns per-day totals for chart rendering.

KPI Formulas from Timesheets:

Billable Hours Ratio   = (logged_hours / expected_hours) × 100
Utilization Rate       = (productive_hours / total_available_hours) × 100
Time per Task          = AVG(duration per task completion)
Time Logging Compliance = (days_with_timesheet / working_days) × 100

6. Disciplinary Data

6.1 Discipline_entries_model (ci_discipline_entries)

File: application/models/Discipline_entries_model.php (~155 lines)

Key Methods:

• get_yearly_entries_count($user_id, $start_date, $end_date) — Counts entries in date range with optional status filter
• count_open_cases_for_user($user_id) — Counts entries with status IN ('new', 'open') where FIND_IN_SET(user_id, user)

KPI Formulas from Disciplinary:

Disciplinary Score     = MAX_SCORE - (infraction_count × penalty_per_infraction)
Compliance Score       = 100 - ((open_cases / max_expected) × 100)
Infraction Frequency   = infraction_count / months_in_period

Note: This is a negative KPI — more infractions = lower score. The scoring should use an inverse scale.

7. Leave Data

7.1 Leave_applications_model (ci_leave_applications)

File: application/models/Leave_applications_model.php (308 lines)

Available Filters: status, date range overlap, target_year, applicant_id, department_id

7.2 Leave_credits_model (ci_leave_credits)

File: application/models/Leave_credits_model.php (233 lines)

Critical Method — get_balance():

SUM(IF(action='debit', counts, 0)) - SUM(IF(action='credit', counts, 0)) AS balance

Supports target_year filtering.

Critical Method — get_overall():

Per-user, per-leave-type, per-action summary grouped by user_id, action, leave_type_id.

KPI Formulas from Leave:

Leave Utilization Rate  = (leave_days_used / leave_credits_allocated) × 100
Unplanned Leave Rate    = (emergency_leaves / total_leaves) × 100  [lower = better]
Leave Balance Health    = leave_credit_balance / allocated_credits × 100
Absence Rate            = (total_leave_days / working_days_in_period) × 100

8. Schedule Data

8.1 Schedule_model (ci_schedule)

File: application/models/Schedule_model.php (~100 lines)

Key Methods:

• getUserSchedId($user_id) — Gets schedule assignment from team_member_job_info
• getUserSchedObj($user_id) — Returns full schedule object

KPI Application: Schedule data provides the baseline for calculating:

• Schedule compliance (actual vs expected hours)
• Late arrival detection (in_time vs schedule start)
• Early departure detection (out_time vs schedule end)

9. Professional Development Data

9.1 Certifications_model (ci_certifications)

File: application/models/Certifications_model.php (~60 lines)

Key Method — get_total_certifications_of_user($user_id):

Counts active/valid certifications where due_date_of_certification >= CURRENT_DATE.

9.2 Skillsets_workforce_model (ci_skillsets_workforce)

File: application/models/Skillsets_workforce_model.php (110 lines)

Bidirectional Matching: FIND_IN_SET enables skillset→job_function and job_function→skillset lookups.

9.3 Skillsets_model (ci_skillsets)

File: application/models/Skillsets_model.php (100 lines)

• get_skillsets_color($skillset_ids) — Color/title for given IDs
• get_skillset_and_job_function_color() — Parses prefixed IDs for both tables

KPI Formulas from Professional Development:

Certification Score     = (active_certifications / target_certifications) × 100
Skill Breadth Score    = (distinct_skills / department_avg_skills) × 100
Competency Match       = (matched_skills / required_skills_for_role) × 100
Development Growth     = (current_certs - previous_period_certs) / previous_period_certs × 100

10. Salary & Compensation Data

10.1 Salary_grades_model (ci_salary_grades)

File: application/models/Salary_grades_model.php (230 lines)

Critical Method — get_grade_matrix():

Returns all active grades with embedded employees array. Each employee's grade determined via latest approved salary_history record (MAX(effective_date) subquery).

Critical Method — compute_salary_for_step($grade_id, $step):

salary = min_salary + (step - 1) × step_increment   (capped at max_salary)

10.2 Salary_history_model (ci_salary_history)

File: application/models/Salary_history_model.php (643 lines)

Key Methods for KPI Integration:

• get_rate_for_date($user_id, $date) — Most recent approved rate as of a given date
• get_rates_in_range() — All rates within a period (for pro-rating)
• has_rate_change_in_range() — Boolean check for mid-period changes
• sync_to_job_info($user_id) — Syncs to team_member_job_info
• get_users_salary_summary() — All active staff with LATEST approved salary via MAX(effective_date) subquery + salary_grade joins
• get_timeline_data() — Full salary change timeline with prev_record comparison

KPI-Salary Integration Points:

Current Grade Position  = salary_step / total_steps  (0.0 to 1.0)
Grade Band Position     = (monthly_salary - min_salary) / (max_salary - min_salary)
Steps Remaining         = total_steps - salary_step
Next Step Salary        = min_salary + (salary_step) × step_increment
Increase on Advancement = step_increment / monthly_salary × 100  (% increase)

11. Payslip Aggregate Data

11.1 Payslips_model (ci_payslips)

File: application/models/Payslips_model.php

These methods aggregate from approved payslips joined with payrolls for date filtering. They provide year-to-date absence and undertime totals that are authoritative (tied to payroll approval).

KPI Application:

Payroll Absence Rate   = yearly_absent_total / (expected_hours_per_year) × 100
Payroll Undertime Rate = yearly_undertime_total / (expected_hours_per_year) × 100

12. Cross-Reference: Data Source to KPI Category Mapping

13. Proposed New Data Points

While the existing system covers ~80% of KPI needs, some new data points would enhance the scoring:

These are optional enhancements — the core KPI system can function entirely on existing data.

14. Data Freshness & Update Frequency

Recommendation: KPI scores should be calculated on-demand or scheduled nightly. Real-time calculation is not necessary — most source data changes within normal business cycles.