Agency Management Reports

[STFF-104] Specification Details

Data Types Specifications 

The Data Types in the report that are outlined within this article are as follows: 

Each Data Type above has a section within the article and is linked appropriately. 

Notes:

  • If any Data Type was deleted and then re-activated, the client is not reported as deleted.
  • If any Data Type was deleted, then re-activated, and then deleted again, the first deletion will not be reported as deleted but will be reported for the DateTime of the last deletion.

Profile Created

Data Type(s): Profile Created
Deleted Option: all; not deleted

When a new client record is added, a corresponding row is added to the "common_logs" table.

For "Profile Created" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID
  • and common_logs.source_table = 12
  • and it is the profile creation record
  • and activity DateTime (common_logs.date) is within [report date range]
  • and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
  • and the client record is not deleted

Profile Edited

Data Type(s): Profile Edited
Deleted Option: all; not deleted

When an existing client record is updated, a corresponding row is added to the "common_logs" table.

For "Profile Edited" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID
  • and common_logs.source_table = 12
  • and it is the profile update record
  • and activity DateTime (clients_log.date) is within [report date range]
  • and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
  • and the client record is not deleted

Profile Deleted

Data Type(s): Profile Created, Profile Edited
Deleted Option: all; deleted only

When the client record is deleted, the "clients" table is updated with "clients.deleted = 1.

For "Profile Deleted" staff activity, all rows are selected from the "clients" table where:

  • clients.deleted = 1
  • and clients.last_updated is within [report date range]
  • and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

Note: If a client record is merged, the record is marked as deleted as reflected in the "clients" table. 

Program Enrollment

Data Type(s): Program Enrollment
Deleted Option: all; not deleted

When a  project enrollment is created or updated, a corresponding row is added to the "common_logs" table.

For "Program Enrollment" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 10
  • and common_logs.screen_type = 2

  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_programs.ref_agency = CURRENT_AGENCY

  • and client record is not deleted

  • and client project stay is not deleted

  • and agency project is not deleted

Program Enrollment Deleted

Data Type(s): Program Enrollment
Deleted Option: all; deleted only

When client project stay is deleted, "client_programs" table is updated with "client_programs.deleted = 1 AND client_programs.ref_agency_deleted = current_agency AND client_programs.ref_user_updated = staff_id".

For "Program Enrollment Deleted" staff activity, all rows are selected from the "client_programs" table where:

  • client_programs.ref_user_updated = STAFF_ID

  • and client_programs.deleted = 1

  • and client_programs.last_updated is within [report date range]

  • and clients.ref_agency_deleted = CURRENT_AGENCY

  • and client record is not deleted

  • and agency project is not deleted

Program Status Update

Data Type(s): Program Status Update 
Deleted Option: all; not deleted

When a status update is added or an existing status update is updated, a corresponding row is added to the "common_logs" table.

For "Program Status Update" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 10
  • and common_logs.screen_type = 3

  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_programs.ref_agency = CURRENT_AGENCY

  • and client record is not deleted

  • and client project stay is not deleted

  • and agency project is not deleted

Program Status Update Deleted

Data Type(s): Program Status Update 
Deleted Option: all; deleted only

When a status update screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Status Update Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

  • client_program_demographics.ref_user_updated = STAFF_ID

  • and client_program_demographics.screen_type = 3

  • and client_program_demographics.deleted = 1

  • and client_program_demographics.last_updated is within [report date range]

  • and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

  • and client project stay is not deleted

  • and client record is not deleted

  • and agency project is not deleted

Program Annual Update

Data Type(s): Program Annual Update 
Deleted Option: all; not deleted

When a status update is added or an existing status update is updated, a corresponding row is added to the "common_logs" table. 

For "Program Annual Update" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 10
  • and common_logs.screen_type = 6

  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_programs.ref_agency = CURRENT_AGENCY

  • and client record is not deleted

  • and the client's project stay is not deleted

  • and agency project is not deleted

Program Annual Update Deleted

Data Type(s): Program Annual Update 
Deleted Option: all; deleted only
 When client's project annual update screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Annual Update Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

  • client_program_demographics.ref_user_updated = STAFF_ID

  • and client_program_demographics.screen_type = 6

  • and client_program_demographics.deleted = 1

  • and client_program_demographics.last_updated is within [report date range]

  • and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

  • and client's project stay is not deleted

  • and client record is not deleted

  • and agency project is not deleted

Program Exit

Data Type(s): Program Exit
Deleted Option: all; not deleted
 When a project exit is added or an existing exit is updated, a corresponding row is added to the "common_logs" table.

For "Program Exit" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 10
  • and common_logs.screen_type = 4

  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_programs.ref_agency = CURRENT_AGENCY

  • and client record is not deleted

  • and client project stay is not deleted

  • and agency project is not deleted

Program Exit Deleted

Data Type(s): Program Exit
Deleted Option: all; deleted only

When an exit screen is deleted or deleted exit screen is updated, a corresponding row is added to the "common_logs" table.

For "Program Exit Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

  • client_program_demographics.ref_user_updated = STAFF_ID

  • and client_program_demographics.screen_type = 4

  • and client_program_demographics.deleted = 1

  • and client_program_demographics.last_updated is within [report date range]

  • and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

  • and client project stay is not deleted

  • and client record is not deleted

  • and agency project is not deleted

OR all rows are selected from the "client_programs_log" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 10
  • and common_logs.screen_type = 4

  • and activity DateTime (common_logs.date) is within [report date range]

  • and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) != "null"

  • and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) != ""

  • and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) IS NOT NULL

  • and ( JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) = "null"

    • or JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) = ""

    • or JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) IS NULL
      )

  • and client_programs.ref_agency = CURRENT_AGENCY

  • and client record is not deleted

  • and client project stay is not deleted

  • and agency project is not deleted

Program Follow-up

Data Type(s): Program Follow-Up
Deleted Option: all; not deleted

When a follow-up screen is added or an existing follow-up screen is updated, a corresponding row is added to the "common_logs" table.

For "Program Follow-up" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 10
  • and common_logs.screen_type = 5

  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_programs.ref_agency = CURRENT_AGENCY

  • and client record is not deleted

  • and client project stay is not deleted

  • and agency project is not deleted

Program Follow-up Deleted

Data Type(s): Program Exit
Deleted Option: all; deleted only

When client follow-up screen is deleted, the"client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Follow-up Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

  • client_program_demographics.ref_user_updated = STAFF_ID

  • and client_program_demographics.screen_type = 5

  • and client_program_demographics.deleted = 1

  • and client_program_demographics.last_updated is within [report date range]

  • and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

  • and client project stay is not deleted

  • and client record is not deleted

  • and agency project is not deleted

Client Note

Data Type(s): Client Note
Deleted Option: all; not deleted

When a new client note is added, a corresponding row is added to the "client_notes" table.

When an existing client note is updated, a corresponding row is added to the "client_notes_log" table. 

For "Client Note" staff activity, all rows are selected from the "client_notes" and the "client_notes_log" table where:

  • the client note is not deleted

  • and client_notes.ref_agency = CURRENT_AGENCY

  • and (client_notes.ref_user or client_notes_log.ref_user ) = STAFF_ID

  • and activity DateTime (client_notes.date or client_notes_log.date) is within [report date range]

  • and client note is not deleted

  • and client record is not deleted

Client Note Deleted

Data Type(s): Client Note
Deleted Option: all; deleted only

When Client Note is deleted, the "client_notes" table is updated with the "client_notes.deleted = 1 AND client_notes.ref_agency_deleted = current_agency AND client_notes.ref_user_updated = staff_id".

For "Client Note Deleted" staff activity, all rows are selected from the "client_notes" table where:

  • client_notes.ref_user_updated = STAFF_ID

  • and client_notes.ref_agency_deleted= CURRENT_AGENCY

  • and client_notes.deleted = 1

  • and activity DateTime (client_notes.last_updated) is within [report date range]

  • and client record is not deleted

Client File

Data Type(s): Client File
Deleted Option: all; not deleted

When a new client file is added a corresponding row is added to the "files" table and to the "client_files" table.

For "Client File" staff activity,  all rows are selected from the "files" table and the "client_files" table where:

  • files.ref_user = STAFF_ID

  • and activity DateTime (files.added_date) is within [report date range]

  • and client_files.ref_agency = CURRENT_AGENCY

  • and record from the "client_files" table is not deleted

  • and client record is not deleted

Client File Deleted

Data Type(s): Client File
Deleted Option: all; deleted only

When Client File is deleted, the "client_files" table is updated with "client_files.deleted = 1 AND client_files.ref_agency_deleted = current_agency AND client_files.ref_user_updated = staff_id".

For "Client File Deleted" staff activity, all rows are selected from the "client_files" table where:

  • client_files.ref_user_updated = STAFF_ID

  • and client_files.ref_agency_deleted= CURRENT_AGENCY

  • and client_files.deleted = 1

  • and activity DateTime (client_files.last_updated) is within [report date range]

  • and client record is not deleted

  • and file record is exists

Note: If Client File Deleted is selected, only client with deleted files during the report date range will be reported. 

Client Location

Data Type(s): Client Location
Deleted Option: all; not deleted

When a client location is added, a corresponding row is added to the "client_addresses" table.

For "Client Location" staff activity, all rows are selected from the "client_addresses" table where:

  • client_addresses.ref_user= STAFF_ID

  • and client_addresses.ref_agency = CURRENT_AGENCY

  • and activity DateTime (client_addresses.added_date) is within [report date range]

  • and client location is not deleted

  • and client record is not deleted

Client Location Deleted

Data Type(s): Client Location
Deleted Option: all; deleted only

When Client Location is deleted, the "client_addresses" table is updated with "client_addresses.deleted = 1 AND client_addresses.ref_agency_deleted = current_agency AND client_addresses.ref_user_updated = staff_id".

For "Client Location Deleted" staff activity, all rows are selected from the "client_addresses" table where:

  • client_addresses.ref_user_updated = STAFF_ID

  • and client_addresses.ref_agency_deleted= CURRENT_AGENCY

  • and client_addresses.deleted = 1

  • and activity DateTime (client_addresses.last_updated) is within [report date range]

  • and client record is not deleted

Assessment

Data Type(s): Assessment
Deleted Option: all; not deleted
 When an assessment is added or an existing assessment is updated, a corresponding row is added to the "common_logs" table.

For "Assessment" staff activity, all rows are selected from the "common_logs" table and "client_assessment_demographics" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 8
  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_assessment_demographics.ref_agency = CURRENT_AGENCY

  • and client_assessment_demographics record is not deleted

  • and client record is not deleted

Assessment Deleted

Data Type(s): Assessment
Deleted Option: all; deleted only
 When client's assessment is deleted, "client_assessment_demographics" table is updated with "client_assessment_demographics.deleted = 1 AND client_assessment_demographics.ref_agency_deleted = current_agency AND client_assessment_demographics.ref_user_updated = staff_id".

For "Assessment Deleted" staff activity, all rows are selected from the "client_assessment_demographics" table where:

  • client_assessment_demographics.ref_user_updated = STAFF_ID

  • and client_assessment_demographics.ref_agency_deleted = CURRENT_AGENCY

  • and client_assessment_demographics.last_updated is within [report date range]

  • and client_assessment_demographics.deleted = 1

Client Referral

Data Type(s): Client Referral
Deleted Option: all; not deleted

When a client referral is added, a corresponding row is added to the "referrals" table.

For "Client Referral" staff activity, all rows are selected from the "referrals" table where:

  • referrals.ref_user= STAFF_ID

  • and referrals.ref_agency = CURRENT_AGENCY

  • and activity DateTime (referrals.date) is within [report date range]

  • and referrals record is not deleted

  • and client record is not deleted

Client Referral Deleted

Data Type(s): Client Referral
Deleted Option: all; deleted only

When Client Referral is deleted, the "referrals" table is updated with "referrals.deleted = 1 AND referrals.ref_agency_deleted = current_agency AND referrals.ref_user_updated = staff_id".

For "Client Referrals Deleted" staff activity, all rows are selected from the "referrals" table where:

  • referrals.ref_user_updated = STAFF_ID

  • and referrals.ref_agency_deleted= CURRENT_AGENCY

  • and referrals.deleted = 1

  • and activity DateTime (referrals.last_updated) is within [report date range]

  • and client record is not deleted

Service Provided/Service Updated

Data Type(s): Service Provided, Service Updated
Deleted Option: all; not deleted

When a new service is added or updated, a corresponding row is added to the "common_logs" table.

For "Service Provided" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 11
  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_services record is not deleted

  • and client_services.ref_agency = CURRENT_AGENCY

  • and service item record is not deleted

  • and service record is not deleted

  • and client record is not deleted

In addition, if the conditions below are true the service is pulled as “Service Provided;” in other cases, the service is pulled as “Service Updated”

  • and ( IFNULL( JSON_LENGTH( common_logs.prev_record ), 0 ) = 0

    • or IF( common_logs.prev_record IN ( ““, [““], “null“ ), 0, 1 ) = 0 )

  • and JSON_EXTRACT( common_logs.curr_record, "$.id" ) IS NOT NULL

Service Deleted

Data Type(s): Service Provided, Service Updated
Deleted Option: all; deleted only
When a service is deleted, the "client_services" table is updated with "client_services.deleted = 1 and client_services.ref_agency_deleted = current_agency" and client_services.ref_user_updated= staff_id".

For "Service Deleted" staff activity, all rows are selected from the "client_services" table where:

  • client_services.ref_user_updated = STAFF_ID

  • and client_services.deleted = 1

  • and client_services.ref_agency_deleted= CURRENT_AGENCY

  • and client_services.last_updated is within [report date range]

  • and service item record is not deleted

  • and service record is not deleted

  • and client record is not deleted

Client ROI

Data Type(s): Client ROI
Deleted Option: all; not deleted
When an ROI is added, a corresponding row is added to the "release_of_information" table.

For "Client ROI" staff activity, all rows are selected from the "release_of_information" table where:

  • release_of_information.ref_user= STAFF_ID

  • and release_of_information.ref_agency = CURRENT_AGENCY

  • and activity DateTime (release_of_information.added_date) is within [report date range]

  • and client ROI is not deleted

  • and client record is not deleted

Client ROI Deleted

Data Type(s): Client ROI
Deleted Option: all; deleted only
When an ROI is deleted, the "release_of_information" table is updated with "release_of_information.deleted = 1 AND release_of_information.ref_agency_deleted = current_agency AND release_of_information.ref_user_updated = staff_id".

For "Client ROI Deleted" staff activity, all rows are selected from the "release_of_information" table where:

  • release_of_information.ref_user_updated = STAFF_ID

  • and release_of_information.ref_agency_deleted= CURRENT_AGENCY

  • and release_of_information.deleted = 1

  • and activity DateTime (release_of_information.last_updated) is within [report date range]

  • and client record is not deleted

Program Current Living Situation

Data Type(s): Program Current Living Situation
Deleted Option: all; not deleted

When a current living situation is added or an existing current living situation is updated, a corresponding row is added to the "common_logs" table. 

For "Program Current Living Situation" staff activity, all rows are selected from the "common_logs" table where:

  • common_logs.ref_user = STAFF_ID

  • and common_logs.source_table = 10

  • and common_logs.screen_type = 3

  • and common_logs.status_screen_type= 2

  • and activity DateTime (common_logs.date) is within [report date range]

  • and client_programs.ref_agency = CURRENT_AGENCY

  • and client record is not deleted

  • and the client's project stay is not deleted

  • and agency project is not deleted

Program Current Living Situation Deleted 

Data Type(s): Program Current Living Situation
Deleted Option: all; deleted only

When a client's project current living situation screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "current living situation Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

  • client_program_demographics.ref_user_updated = STAFF_ID

  • and client_program_demographics.screen_type = 3

  • and client_program_demographics.status_screen_type= 2

  • and client_program_demographics.deleted = 1

  • and client_program_demographics.last_updated is within [report date range]

  • and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY

  • and client's project stay is not deleted

  • and client record is not deleted

  • and agency project is not deleted

Client Contact Created

Data Type(s): Client Contact
Deleted Option: all; not deleted

When a client contact is added or an existing client contact is updated, a corresponding row is added to the "client_contacts" table.

For "Client Contact" staff activity, all rows are selected from the "client_contacts" table where:

  • client_contacts.ref_user= STAFF_ID

  • and activity DateTime (client_contacts.added_date) is within [report date range]

  • and Client Contact is not deleted

  • and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

  • and client record is not deleted

Client Contact Edited

Data Type(s): Client Contact
Deleted Option: all; not deleted

When an existing client contact is updated, a current row from the "client_contacts" table is updated.

For "Client Contact Created/Edited" staff activity, all rows are selected from the "release_of_information" table where:

  • client_contacts.ref_user= STAFF_ID

  • and client_contacts.added_date <> client_contacts.last_updated

  • and activity DateTime (client_contacts.added_date) is within [report date range]

  • and Client Contact is not deleted

  • and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

  • and client record is not deleted

Client Contact Deleted

Data Type(s): Client Contact
Deleted Option: all; deleted only

When a client contact is deleted, the "client_contacts" table is updated with "client_contacts.deleted = 1 AND client_contacts.ref_user_updated = staff_id".

For "Client Contact Deleted" staff activity, all rows are selected from the "client_contacts" table where:

  • client_contacts.ref_user_updated = STAFF_ID

  • and client_contacts.deleted = 1

  • and activity DateTime (client_contacts.last_updated) is within [report date range]

  • and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

  • and client record is not deleted



Updated: 04/17/2023