class Admins::MultipleSchoolReportsController < Admins::BaseController
  before_action :must_admin!

  def index
    @params_page = params[:page] || 1
    @title = params[:search_type]

    time_range = []
    created_field = params[:search_type] == '教师上传资源数' ? 'ua.created_on' : 'ua.created_at'
    if params[:begin_at].present?
      time_range <<  "#{created_field} >= '#{params[:begin_at]} 00:00:00'"
    end
    if params[:end_at].present?
      time_range << "#{created_field} <= '#{params[:end_at]} 23:59:59'"
    end
    where_sql = " ue.school_id = #{params[:school]} #{time_range.present? ? ' AND ' + time_range.join(" AND ") : '' }"

    if params[:school].present?
      case @title
      when "用户登录次数"
        @data = UserAction.select("*").from("
          (
          SELECT DATE_FORMAT(ua.created_at,'%Y-%m-%d') AS date, COUNT(*) count
          FROM user_actions ua JOIN user_extensions ue ON ue.user_id = ua.user_id 
          WHERE ua.action_type = 'Login' AND #{where_sql}
          GROUP BY date
          ) AS data")
      when "教师上传资源数"
        @data = Attachment.select("*").from("
          (
            SELECT DATE_FORMAT(ua.created_on, '%Y-%m-%d') AS date, COUNT(*) count
            FROM attachments ua JOIN user_extensions ue ON ue.user_id = ua.author_id AND ue.identity=0
            WHERE #{where_sql}
            GROUP BY date
          ) AS data
        ")
      when "实训作业数"
        @data = HomeworkCommon.select("*").from("
          (
            SELECT DATE_FORMAT(ua.created_at, '%Y-%m-%d') AS date, COUNT(*) count 
            FROM homework_commons ua JOIN user_extensions ue on ue.user_id = ua.user_id
            WHERE ua.homework_type = 4 AND #{where_sql}
            GROUP BY date
          ) AS data
        ")
      when "发布实训作业数"
        @data = HomeworkCommon.select("*").from("
          (
            SELECT DATE_FORMAT(ua.created_at, '%Y-%m-%d') AS date, COUNT(*) count 
            FROM homework_commons ua JOIN user_extensions ue on ue.user_id = ua.user_id
            WHERE ua.homework_type = 4 AND publish_time IS NOT NULL and publish_time < current_timestamp AND #{where_sql}
            GROUP BY date
          ) AS data
        ")
      when "评测次数"
        @data = EvaluateRecord.unscoped.select("*").from("
          (
            SELECT DATE_FORMAT(ua.created_at,'%Y-%m-%d') AS date, COUNT(*) count
            FROM evaluate_records ua JOIN user_extensions ue ON ue.user_id = ua.user_id
            WHERE #{where_sql}
            GROUP BY date
          ) AS data
        ")
      end

      @data = @data.order("#{params[:sort_by]} #{params[:sort_direction]}")

      respond_to do |format|
        format.html
        format.js {
          @data = paginate @data
        }
        format.xlsx {
          school = School.find(params[:school])
          filename = "#{school.name}#{@title}统计.xlsx"
          render xlsx: 'index', filename: filename
        }
      end
     
    end
  end

  def school
    @schools = School.where("name like ?", "%#{params[:q]}%")
    render json: { items: @schools.as_json(only: ["id", "name"]), total_count: @schools.count }
  end
end
