Reports Logic

Based on current tables next data must be aggregated and analyzed:

• Number of patients per doctor
• Number of patients registered for the same phone number
• Number and percentage of patients per doctor that have set offline authorization method
• Number and percentage of patients per legal entity that have set offline authorization method

Using replicated tables from fraud data mart and BI tool the reports with outliers must be shown.

Until there is no enough statistic to use statistical methods to find outliers, the top least(5%, 25 rows) can be shown instead.

For now there are 3 reports are required - for autorization, doctors and phone_numbers.

Predefine views with aggregation

1. Doctor aggregation  

• number of patients per doctor (total_patients_doctor)

Conditions: employee, declarations

• employee.id=declarations.employee_id
• employee.employee_type='DOCTOR'
• employee.is_active=true
• employee.status='APPROVED'
• declarations.is_active=true
• declarations.status='active'

Fields:

do not take into account party_id with avg(qty_person_id_60)=0, exclude them from report.

• number and percentage of patients per doctor that have set offline authorization method (autorization_doctor)

Conditions:

• employee.id=declarations.employee_id
• declarations.person_id=presons.id
• employee.division_id=divisions.id
• employee.employee_type='DOCTOR'
• employee.is_active=true
• employee.status='APPROVED'
• declarations.is_active=true
• declarations.status='active'
• divisions.addresses.{type:"RESIDINCE"}

Fields:

count(authentication_methods.type='OFFLINE'.person_id)

2. Patient aggregation  

• number of patients with same phone number (patients_phonenumber)

The table recalculates on daily basis

Conditions:

• declarations.person_id=presons.id
• declarations.is_active=true
• declarations.status='active'

Fields:

3. Legal entity aggregation  

• number and percentage of patients per legal_entity that have set offline authorization method (autorization_legal_entity)

Conditions:

• employee.id=declarations.employee_id
• declarations.person_id=presons.id
• employee.division_id=divisions.id
• employee.employee_type='DOCTOR'
• employee.is_active=true
• employee.status='APPROVED'
• declarations.is_active=true
• declarations.status='active'
• divisions.addresses.{type:"RESIDINCE"}

Fields:

count(authentication_methods.type='OFFLINE'.person_id)

Authorizations_fraud report

Using 

• autorization_legal_entity table - legal entities with patients_qty<= 50 are not taken into account.

• autorization_doctor table - doctors with patients_qty<= 10 are not taken into account.

Required input filter parameters:

• settlement_type ['CITY', 'OTHER']
• type ['DOCTOR', 'LEGAL_ENTITY']   

Order by ratio_offline_patients_qty desc and show least(5%, 25 rows) with the highest value. 

Output fields:

• id (based on input type it can be party_id or legal_entity_id)
• settlement_type
• offline_patients_qty
• ratio_offline_patients_qty
• report_date

Phone_numbers_fraud report

Using 

• patients_phonenumber table - phone numbers with patients_qty<= 1 are not taken into account.

Order by ratio_offline_patients_qty desc and show least(5%, 25 rows) with the highest value.

Output fields:

• phone_number
• patients_qty
• report_date

Doctors_fraud report

Using

• total_patients_doctor table - doctors with patients_qty<= 10 are not taken into account.

Optional input query parameters (filter by):

• patients_qty
• patient_increase_30d

Output fields:

• party_id
• legal_entity_id
• one of [patients_qty, patient_increase_30d, patient_increase_30d_0]
• report_date