educoder/app/services/admins/school_base_statistic_service.rb

class Admins::SchoolBaseStatisticService < ApplicationService
  include CustomSortable

  attr_reader :params

  sort_columns :student_count, :teacher_count, :course_count, :course_group_count,
               :attachment_count, :video_count, :normal_work_count, :shixun_work_count, :shixun_evaluate_count,
               :student_work_count, :exercise_count, default_direction: :desc

  def initialize(params)
    @params = params
  end

  def call
    schools = School.group('schools.id')

    keyword = params[:keyword].try(:to_s).try(:strip)
    if keyword.present?
      schools = schools.where("schools.name LIKE :keyword OR schools.id LIKE :keyword", keyword: "%#{keyword}%")
    end

    count = schools.count.count
    # 根据排序字段进行查询
    schools = query_by_sort_column(schools, params[:sort_by])

    schools = custom_sort(schools, params[:sort_by], params[:sort_direction])
    schools = schools.limit(page_size).offset(offset)
    schools = package_other_data(schools)
    [count, schools]
  end

  def package_other_data(schools)
    ids = schools.map(&:id)

    student_count = UserExtension.where(school_id: ids, identity: :student).group(:school_id).count
    teachers = UserExtension.where(school_id: ids, identity: :teacher).group(:school_id)
    teacher_count = teachers.count
    courses = Course.where(is_delete: 0, school_id: ids).group(:school_id)
    course_count= courses.count
    course_group_count = courses.joins(:course_groups).count
    attachment_count = courses.joins(:attachments).count
    video_count = teachers.joins(user: :videos).where("videos.delete_state IS NULL").count

    homeworks = HomeworkCommon.joins("JOIN user_extensions ON homework_commons.user_id = user_extensions.user_id").where(user_extensions: {school_id: ids})
    shixun_work_count = homeworks.where(homework_type: 4).group(:school_id).count
    normal_work_count = homeworks.where(homework_type: 1).group(:school_id).count

    reports = SchoolReport.where(school_id: ids)
    evaluate_count = reports.each_with_object({}) { |report, obj| obj[report.school_id] = report.shixun_evaluate_count }
    student_work_count = reports.each_with_object({}) { |report, obj| obj[report.school_id] = report.student_work_count }

    exercise_count = Exercise.joins(:course).where(courses: { school_id: ids }).group(:school_id).count

    schools.map do |school|
      {
        id: school.id,
        name: school.name,
        teacher_count: teacher_count[school.id],
        student_count:  student_count[school.id],
        course_count: course_count[school.id],
        course_group_count: course_group_count[school.id],
        attachment_count: attachment_count[school.id],
        video_count: video_count[school.id],
        normal_work_count: normal_work_count[school.id],
        shixun_work_count: shixun_work_count[school.id],
        student_work_count: student_work_count[school.id],
        shixun_evaluate_count: evaluate_count[school.id],
        exercise_count: exercise_count[school.id]
      }
    end
  end

  private
  def query_by_sort_column(schools, sort_by_column)
    base_query_column = 'schools.id, schools.name'

    case sort_by_column.to_s
    when 'teacher_count' then
      schools.joins('LEFT JOIN user_extensions ue ON ue.school_id = schools.id AND ue.identity = 0')
        .select("#{base_query_column}, COUNT(ue.id) teacher_count")
    when 'student_count' then
      schools.joins('LEFT JOIN user_extensions ue ON ue.school_id = schools.id AND ue.identity = 1')
        .select("#{base_query_column}, COUNT(*) student_count")
    when 'course_count' then
      schools.joins('LEFT JOIN courses ON courses.school_id = schools.id AND courses.is_delete = 0')
        .select("#{base_query_column}, COUNT(courses.id) course_count")
    when 'course_group_count' then
      schools.joins("LEFT JOIN courses ON courses.school_id = schools.id AND courses.is_delete = 0
                     LEFT JOIN course_groups ON course_groups.course_id = courses.id")
        .select("#{base_query_column}, COUNT(course_groups.id) course_group_count")
    when 'attachment_count' then
      schools.joins("LEFT JOIN courses cs ON cs.school_id = schools.id AND cs.is_delete = 0
                     LEFT JOIN attachments ON attachments.container_type ='Course' AND attachments.container_id = cs.id")
        .select("#{base_query_column}, COUNT(*) attachment_count")
    when 'video_count' then
      schools.joins("LEFT JOIN user_extensions ue ON ue.school_id = schools.id AND ue.identity = 0
                     LEFT JOIN videos ON videos.user_id = ue.user_id AND videos.delete_state IS NOT NULL")
        .select("#{base_query_column}, COUNT(*) video_count")
    when 'normal_work_count' then
      schools.joins("LEFT JOIN user_extensions ON user_extensions.school_id = schools.id
                     LEFT JOIN homework_commons ON homework_commons.user_id = user_extensions.user_id AND homework_commons.homework_type = 0")
        .select("#{base_query_column},  COUNT(*) normal_work_count")
    when 'shixun_work_count' then
      schools.joins("LEFT JOIN user_extensions ON user_extensions.school_id = schools.id
                     LEFT JOIN homework_commons ON homework_commons.user_id = user_extensions.user_id AND homework_commons.homework_type = 4")
        .select("#{base_query_column}, COUNT(*) shixun_work_count")
    when 'student_work_count' then
      schools.joins("LEFT JOIN school_reports ON school_reports.school_id = schools.id")
        .select("#{base_query_column}, SUM(student_work_count) AS student_work_count")
    when 'shixun_evaluate_count' then
      schools.joins('LEFT JOIN school_reports ON school_reports.school_id = schools.id')
        .select("#{base_query_column}, SUM(shixun_evaluate_count) AS shixun_evaluate_count")
    when 'exercise_count' then
      schools.joins('LEFT JOIN courses cs ON cs.school_id = schools.id AND cs.is_delete = 0
                     LEFT JOIN exercises ON exercises.course_id = cs.id')
        .select("#{base_query_column}, COUNT(exercises.id) exercise_count")
    end
  end

  def page_size
    params[:per_page] || 20
  end

  def offset
    (params[:page].to_i.zero? ? 0 : params[:page].to_i - 1) * page_size
  end
end