Editing Tupaia Report (section)

= Tupaia Report Documentation =

== Overview ==
The Tupaia Report provides insights into client support activities (e.g., Oral Care, Mental Health) and financials (costs, referrals) for Tupaia cases. It includes:  
* '''Client Stages (Aggregated)''': Summary counts of client progress  
* '''Client Stages (Detail)''': Individual client status tracking  
* '''Report Details''': All Tupaia activity details

Data is consolidated from Kotahi CMS into three materialized views for efficient Power BI reporting.

== Key Concepts ==

=== Definitions ===
{| class="wikitable"
! Term 
! Description
|-
| '''Assessment Done''' 
| Indicates Tupaia assessment completion status (<code>Yes</code>/<code>No</code>)
|-
| '''Intervention Stage''' 
| Client progress status (see [[#Stages Definition|Stages Definition]])
|-
| '''Cost''' 
| Approved payment amounts from JSON fields
|}

=== Stages Definition ===
{| class="wikitable"
! Stage 
! Criteria 
|-
| '''Need Identified'''
| Requirement detected, no activity exists
|-
| '''Intervention Booked'''
| Activity scheduled but not completed
|-
| '''Intervention Completed'''
| Service marked as done in JSON
|-
| '''Referral Received'''
| External referral logged with date
|-
| '''Declined'''
| Client actively declined support
|-
| '''Not Applicable'''
| No action needed or data missing
|}

== Configuration ==
* '''Access''': Currently limited to Moli and data team  
* '''Refresh''': cron jobs refresh materialized views

== Technical Design ==

=== Data Sources ===
{| class="wikitable"
! Table 
! Purpose
|-
| <code>kotahi.clients</code>
| Client demographics and case names
|-
| <code>kotahi.activities</code>
| Activity records and JSON metadata
|-
| <code>kotahi.clientrelationships</code>
| Family/household calculations
|}

=== Materialized Views ===

==== kotahi.mvw_tupaia_report ====
'''Purpose''': Powers '''Report Details''' page with all activity details relating to Tupaia service

'''Key Columns''':
{| class="wikitable"
! Column 
! Description
|-
| Whānau Ora Case ID
| Case identifier from CustomFields
|-
| Cost($)
| Approved payment amount from JSON
|}

==== kotahi.mvw_tupaia_stages ====
'''Purpose''': Drives '''Client Stages (Detail)''' page with individual client statuses

'''Key Columns''':
{| class="wikitable"
! Column 
! Description
|-
| DentalStatus
| Current dental intervention state
|-
| EyeHearingStatus
| Vision/auditory care progress
|-
| MHStatus
| Mental health referral status
|}

'''Business Rules''':
# Excludes clients discharged (ActivityTypeId=33)
# Uses lateral joins for latest activity per type
# JSON field driven status calculations

==== kotahi.mvw_tupaia_stages_counts ====
'''Purpose''': Provides aggregated numbers for '''Client Stages (Aggregated)'''


'''Example Metrics''':
{| class="wikitable"
! Metric 
! Description
|-
| dental_need_identified
| Clients needing dental intervention
|-
| bowel_up_to_date
| Client bowel screening is up to date
|}

== Reports ==
=== Power BI Pages ===
{| class="wikitable"
! Page 
! Source MV 
! Features
|-
| Report Details
| mvw_tupaia_report
| Tupaia activities details
|-
| Client Stages (Detail)
| mvw_tupaia_stages
| Individual client journeys
|-
| Client Stages (Aggregated)
| mvw_tupaia_stages_counts
| Service utilization metrics
|}


[[Category:Apps]]  
[[Category:Reports]]  
[[Category:Kotahi]]