Reporter Query

complaint_query.rb

class CustomQueries::ComplaintQuery < CustomQueries::ReporterQuery
  @@select = {
      'Record ID'           => 'complaints.id',
      'Transmittal Date'    => "complaints.transmittal_date",
      'Student ID'          => "IFNULL(students.student_id, 'Student Not Identified')",
      'Student Last Name'   => 'IFNULL(students.last_name, IFNULL(complaints.student_last_name_entry_text, mandates.student_last_name_entry_text))',
      'Student First Name' => 'IFNULL(students.first_name, IFNULL(complaints.student_first_name_entry_text, mandates.student_first_name_entry_text))',
      'Student DOB'              => "IFNULL(students.dob, complaints.student_born_on_entry_text)",
      'Current Attending School' => 'IFNULL(current_school.name, "Not Enrolled")',
      'Attorney'                 => "CONCAT(attorney.first_name, ' ', attorney.last_name)",
      'Investigator'             => "CONCAT(investigator.first_name, ' ', investigator.last_name)",
      'Case Manager'             => "CONCAT(case_manager.first_name, ' ', case_manager.last_name)",
      'Res Sess Scheduled Date'  => "resolution_sessions.when"
  }

  @@calculated_fields = []

  @@where = {
    'case_manager_id' => "case_manager.id in (%s)",
    'attorney_id' => "attorney.id in (%s)",
    'investigator_id' => "investigator.id in (%s)",
    'aasm_state_equals_all' => "complaints.aasm_state in ('%s')",
    'aasm_state_does_not_equal_all' => "complaints.aasm_state not in ('%s')",
    'transmittal_date_gte' => "complaints.transmittal_date >= '%s'",
    'transmittal_date_lte' => "complaints.transmittal_date <= '%s'",
    'current_school_id' => 'current_school.id = %d',
    'allegation_school_id' => 'allegation_school.id = %d',
    'first_name' => "((NOT(complaints.student_id_is_not_required <=> 1) AND students.first_name LIKE '%%%s%%') OR (complaints.student_id_is_not_required = 1 AND complaints.student_first_name_entry_text LIKE '%%%s%%'))",
    'last_name' => "((NOT(complaints.student_id_is_not_required <=> 1) AND students.last_name LIKE '%%%s%%') OR (complaints.student_id_is_not_required = 1 AND complaints.student_last_name_entry_text LIKE '%%%s%%'))",
    'stars_id' => 'students.student_id = %d',
    'present_attending_school_id' => 'complaints.present_attending_school_id = %d',
    'sho_case_number' => "complaints.sho_case_number = '%s'",
    'id' => 'complaints.id in (%s)'
  }

  @@default_selects = [
    'Transmittal Date',
    'Student ID',
    'Student Last Name',
    'Student First Name',
    'Student DOB',
    'Current Attending School',
    'Attorney',
    'Investigator',
    'Res Sess Scheduled Date'
  ]

  def custom_sql
    sql = <<-eos
      SELECT 
      complaints.id as 'complaint_id',
      #{selects.join(",\n")}
      FROM complaints
      LEFT JOIN allegations ON allegations.complaint_id = complaints.id
      LEFT JOIN meetings resolution_sessions ON resolution_sessions.workitem_type = 'Complaint' AND resolution_sessions.workitem_id = complaints.id AND resolution_sessions.meeting_type_id = 1
      LEFT JOIN allegation_schools ON allegations.id = allegation_schools.allegation_id
      LEFT JOIN schools allegation_school ON allegation_schools.school_id = allegation_school.id
      LEFT JOIN students ON complaints.student_id = students.id
      LEFT JOIN schools current_school ON students.school_id = current_school.id
      LEFT JOIN assignments atty_assignments ON (atty_assignments.workitem_type = 'Complaint' AND atty_assignments.workitem_id = complaints.id AND atty_assignments.role_id = 2 AND atty_assignments.user_id != -9000)
      LEFT JOIN users attorney ON atty_assignments.user_id = attorney.id
      LEFT JOIN assignments inv_assignments ON (inv_assignments.workitem_type = 'Complaint' AND inv_assignments.workitem_id = complaints.id AND inv_assignments.role_id = 13 AND inv_assignments.user_id != -9000)
      LEFT JOIN users investigator ON inv_assignments.user_id = investigator.id
      LEFT JOIN assignments cm_assignments ON (cm_assignments.workitem_type = 'Complaint' AND cm_assignments.workitem_id = complaints.id AND cm_assignments.role_id = 3 AND cm_assignments.user_id != -9000)
      LEFT JOIN users case_manager ON cm_assignments.user_id = case_manager.id
      LEFT JOIN mandates ON complaints.id = mandates.complaint_id AND complaints.placeholder = 1
      WHERE
        NOT(complaints.placeholder <=> 1)
      AND
        complaints.aasm_state NOT IN ('data_entry', 'data_validation')
    eos

    sql << " AND #{wheres.join(' AND ')}" if wheres.present?

    sql << scoping_sql

    sql << " GROUP BY complaints.id "
    sql << " LIMIT 1000"

    sql
  end

  def scoping_sql
    return "" if User.current.has_role_group?(RoleGroup::AllStudentAccess)

    id_sql = <<-id_sql
        SELECT complaints.id
        FROM complaints
        JOIN school_student_permissions ssp ON complaints.student_id = ssp.student_id AND ssp.school_id IN (#{scoping_school_ids.join(',')})
        UNION
        SELECT complaints.id
        FROM complaints
        WHERE present_attending_school_id IN (#{scoping_school_ids.join(',')})
    id_sql

    complaint_ids = []
    ActiveRecord::Base.connection.execute(id_sql).each do |row|
      complaint_ids << row[0]
    end

    sql = <<-sql
      AND complaints.id IN
      (#{complaint_ids.join(',')})
    sql

    sql
  end

  def matching_records
    @complaint_ids = []
    records = []
    ActiveRecord::Base.connection.execute(custom_sql).each do |row|
      @complaint_ids << row[0]
      row[0] = complaint_path(row[0])
      records << row
    end
    records
  end
end