Editing Kotahi PBI

== Background ==

Kotahi is a Power BI app designed to provide insights into the [[Kotahi]] CMS system. It offers an overview of client demographics, staff performance, and service utilisation.

The app currently includes three pages:
* '''Clients overview''' – Summarises key client demographics and details.
* '''Clients distribution(map)''' – Visualises client localities through a geographical graph.
* '''Services Overview Page''' – Highlights service utilisation and staff performance metrics.
* '''Contacts summary''' – Summarises contacts made 
* '''Contacts detail''' – Contacts made in detail
* '''Activities detail''' – details all activities completed.
* '''Appointments''' – Appointments booked
* '''data completeness''' – pin points areas where client data is missing
* '''group activities''' – Visualises group activities and clients attendance.

== Definitions ==

* '''Active/Inactive''' - If the last activity date is inside 90 days, then client is labelled as active.

== Configuration ==

The app is currently accessible by PBI Aiga group.

== Technical Information ==

=== Data sources ===

* [[Kotahi data supply]]

=== Materialized Views ===

==== kotahi.mvw_client_services ====
'''Purpose:''' Aggregates client service registrations and discharge information

'''Source Tables:'''
* <code>kotahi.clientservicestatus</code> (service enrollment status)
* <code>kotahi.clients</code> (client demographics)
* <code>kotahi.services</code> (service definitions)
* <code>kotahi.activities</code> (activity records)
* <code>kotahi.users</code> (staff information)

'''Key Columns:'''
{| class="wikitable"
! Column Name !! Description !! Data Type
|-
| service_registered_name || Name of enrolled service || text
|-
| registereddate || Service enrollment date (YYYY-MM-DD) || date
|-
| dischargedate || Discharge date from exit activities || date
|-
| exitby || Staff who discharged client (''Firstname Lastname'') || text
|}

'''Business Rules:'''
# Discharges are identified by activity types 5 or 33 (configured in <code>activitytypes</code>)
# Uses '''90-day activity window''' for active/inactive status
# Discharge dates pulled from activity <code>EnteredDate</code>
# Maintains historical records through <code>LEFT JOIN</code> to preserve inactive enrollments


==== kotahi.mvw_client_summary ====
'''Purpose:''' Provides demographic overview and activity status for clients.

'''Source Tables:'''
* <code>kotahi.clients</code> (base client data)
* <code>kotahi.ethnicities</code> (prioritized ethnicity details)
* <code>kotahi.activities</code> (activity history)

'''Key Columns:'''
{| class="wikitable"
! Column Name !! Description !! Data Type
|-
| prioritisedethnicity || Client's primary ethnicity || text
|-
| lastactivitydate || Most recent activity timestamp || timestamp
|-
| agegroup || Age category bracket || text
|-
| clientstatus || Active/Inactive status || text
|-
| ResidentialAddress_Suburb || Client's residential suburb || text
|}

'''Business Rules:'''
# '''Age Group Calculation:'''
#* Uses <code>DOB</code> to calculate age brackets:
#** 0-4, 5-14, 15-24, 25-34, 35-44, 45-54, 55-64, 65+
#* Returns 'Unknown' if <code>DOB</code> is missing
# '''Activity Status:'''
#* Active = Last activity within 90 days
#* Inactive = No activity in past 90 days or never active
# '''Data Aggregation:'''
#* Groups by client attributes to ensure unique client records
#* Uses <code>LEFT JOIN</code> to preserve clients without activities

==== kotahi.mvw_activities_summary ====

'''Purpose:''' Aggregates activity details with participant information and custom field data for reporting.

'''Source Tables:'''
* <code>kotahi.activities</code> (base activity records)
* <code>kotahi.activitytypes</code> (activity categorization)
* <code>kotahi.services</code> (service associations)
* <code>kotahi.users</code> (staff information)
* <code>kotahi.clients</code> (client details)
* <code>kotahi.activityadditionalstaff</code> (multi-staff activities)
* <code>kotahi.activityadditionalclient</code> (multi-client activities)

'''Key Columns:'''
{| class="wikitable"
! Column Name !! Description !! Data Type
|-
| additionalstaffnames || Comma-separated list of non-primary staff || text
|-
| additionalclientnames || Comma-separated list of secondary clients || text
|-
| contactmethod || Phone/Email/In-person from JSON || text
|-
| contacttype || Classification from JSON (e.g., Follow-up) || text
|-
| duration_unit || Minutes/Hours from JSON || text
|}

'''Business Rules:'''
# '''Participant Aggregation:'''
#* Excludes primary activity completer from additional staff list
#* Uses '''array_agg''' with '''DISTINCT''' to prevent duplicates
# '''JSON Field Handling:'''
#* Extracts 6 key fields from <code>CustomFieldDataJson</code>:
#** client_present, contact_location, notes, etc.
# '''Activity Context:'''
#* Links to service through <code>activitytypes</code> → <code>services</code> hierarchy

'''Data Relationships:'''
<mermaid>
graph LR
    A[activities] --> B[activitytypes]
    A --> C[users]:::completedby
    A --> D[clients]:::mainclient
    A --> E[activityadditionalstaff]
    E --> F[users]:::additionalstaff
    A --> G[activityadditionalclient]
    G --> H[clients]:::additionalclient
    B --> I[services]:::service 
    
    classDef completedby fill:#e6f3ff,stroke:#0066cc;
    classDef mainclient fill:#e6ffe6,stroke:#009900;
    classDef additionalstaff fill:#fff0e6,stroke:#cc5200;
    classDef additionalclient fill:#ffe6ff,stroke:#990099;
    classDef service fill:#C0C0C0,stroke:#991199; 

</mermaid>

'''Refresh Logic:'''
* Materialized view refreshed daily via scheduled cronjob

==== kotahi.mv_calendar_appointments ====

'''Purpose:''' Provides appointment-level data for Power BI calendar and scheduling visuals.

'''Source Tables:'''
* <code>kotahi.calendarevents</code> – main event data
* <code>kotahi.calendarschedules</code> – schedule names (e.g. Whānau Ora, Psychologist)
* <code>kotahi.calendareventclient</code> – client associations
* <code>kotahi.clients</code> – client name and ID
* <code>kotahi.users</code> – staff information (CreatedByUser)

'''Key Columns:'''
{| class="wikitable"
! Column Name !! Description !! Data Type
|-
| EventId || Unique appointment/event ID || integer
|-
| EventName || Title or type of appointment || text
|-
| ScheduleName || Calendar or service schedule label (e.g. Whānau Ora, Psychologist) || text
|-
| DateStart || Start time of appointment || timestamp
|-
| DateEnd || End time of appointment || timestamp
|-
| StaffName || Full name of staff who created the appointment || text
|-
| ClientId || Unique ID of the client || integer
|-
| ClientName || Full name of the client || text
|-
| IsConfirmed || Whether the appointment is confirmed || boolean
|-
| IsCancelled || Whether the appointment was cancelled || boolean
|-
| EventNotes || Notes or comments added to the appointment || text
|}

'''Business Rules:'''
# Only includes appointments where <code>IsDeleted = false</code>
# Excludes events created by staff named "Alex Poor"
# Joins calendar events to schedules, clients, and users
# Supports filtering in Power BI by schedule name, date, staff, and client

'''Use Cases:'''
* Power BI visuals for daily/weekly appointment volume
* Staff-specific calendars
* Client service engagement timelines
* Monitoring cancellations and no-shows

'''Refresh Logic:'''
* Materialized view is refreshed nightly via cronjob to ensure up-to-date appointment data.


== TODO ==
* Implement Hauora assessment page
* Implement group activities page
[[Category:Apps]]