educoder/app/controllers/courses_controller.rb

#encoding: utf-8
class CoursesController < ApplicationController
  # layout 'base_courses'
  include CoursesHelper
  include ActivitiesHelper
  helper :activities
  helper :members
  helper :words
  helper :attachments
  helper :files
  helper :activity_notifys
  include ApplicationHelper
  helper SyllabusesHelper

  before_filter :auth_login1, :only => [:show, :course_activity, :feedback]
  skip_before_filter :verify_authenticity_token, :only => [:update_position]
  before_filter :check_authentication, :except => [:update_position]
  menu_item :overview
  menu_item :feedback, :only => :feedback
  menu_item :homework, :only => :homework

  menu_item :new_homework, :only => :new_homework

  menu_item l(:label_sort_by_time), :only => :index
  menu_item l(:label_sort_by_active), :only => :index
  menu_item l(:label_sort_by_influence), :only => :index

  #before_filter :can_show_course, :except => []
  before_filter :logged_user_by_apptoken,:only => [:show,:feedback]
  before_filter :find_course, :except => [ :index, :search, :new, :join, :unjoin, :create, :new_join, :course, :join_private_courses, :join_course_multi_role, :unjoin_group, :search_course_list]
  before_filter :authorize_course, :only => [:show, :settings,  :update,  :course]
  before_filter :authorize_course_global, :only => [:new,:create]
  before_filter :toggleCourse, :only => [:finishcourse, :restartcourse]
  before_filter :is_deleted, :only => [:show, :settings]

  before_filter :require_login, :only => [:join, :unjoin]
  #before_filter :allow_join, :only => [:join]
  require 'bundler/setup'
  if RUBY_PLATFORM =~ /linux/
    require 'simple_xlsx_reader'
  end
  # params[:search] 搜索课程的名称
  # params[:select] "join" "create" "all" 参与的 创建的 所有的课程
  #
  def index
    @user = User.current
    @search  = params[:search].nil? ? '' : params[:search]
    @select  = params[:select].nil? ? '' : params[:select]
    @is_over = params[:is_over]                                 # 是否是新建课程页面跳转的查看归档
    @syllabuses_num  = Course.all.count
    #@syllabus_count = all_syllabuses.count
    # 我创建的课堂
    #create_syllabuses = Course.where(:tea_id => @user.id)
    # 我参与的课堂
    #join_syllabuses = @user.courses.where("is_delete = 0 and tea_id != #{@user.id}")
    # @join_syllabuses_count = @user.courses.not_deleted_not_end.order("updated_at desc").select{ |course| !@user.has_teacher_role(course)}.count
    # @create_syllabuses_count = @user.courses.not_deleted_not_end.order("updated_at desc").select{ |course| @user.has_teacher_role(course)}.count
    @my_courses_count = @user.courses.not_deleted_not_end.count

    # 页面筛选的课程
    if @select == "create"
      @syllabuses = @user.courses.not_deleted_not_end.where("name like '%#{@search}%'").order("updated_at desc").select{ |course| @user.has_teacher_role(course)}
    elsif @select == "join"
      @syllabuses = @user.courses.not_deleted_not_end.where("name like '%#{@search}%'").order("updated_at desc").select{ |course| !@user.has_teacher_role(course)}
    elsif @select == "end"
      # 归档的课堂
      end_syllabuses = Course.where("is_end = 1 and name like '%#{@search}%'")

      @syllabuses = end_syllabuses
    else
      # 全部课堂
      all_syllabuses  = Course.where("is_end = 0 and is_delete = 0 and name like '%#{@search}%'")
      @syllabuses = all_syllabuses
    end

    @syllabuses.each do |course|
      course[:infocount] = course.members.count
      #course[:infocount] = (User.current.admin? || User.current.allowed_to?(:as_teacher,course)) ? (course.homework_commons.count + visable_attachemnts_incourse(course).count + course.members.count) : (course.homework_commons.where("publish_time <= '#{Time.now}'").count + visable_attachemnts_incourse(course).count + course.members.count)
      if course[:infocount] < 0
        course[:infocount] = 0
      end
    end
    @syllabuses = @syllabuses.sort{|x,y| y[:infocount] <=> x[:infocount] }

    @syllabuses_count = @syllabuses.count
    @limit = 16
    @is_remote = true
    @syllabuses_pages = Paginator.new @syllabuses_count, @limit, params['page'] || 1
    @offset ||= @syllabuses_pages.offset
    @syllabuses = paginateHelper @syllabuses, @limit
    respond_to do |format|
      format.js
      format.html { render :layout => "base_edu" }
    end
  end

  def import_excel
    attachments = params[:attachments]
    attachments.each do |attachment|
      attachment = Attachment.find_by_token_only(attachment[1]['token'])
      attachment.container_id = @course.id
      attachment.container_type = "ImportStudent"
      attachment.save
    end
    @import_attachments = Attachment.where(:container_id => @course.id, :container_type => "ImportStudent")
  end

  def shixun_statistics
    @homeworks = @course.homework_commons.where("homework_type = 4 and publish_time < '#{Time.now}'").order("publish_time desc")
    filename="#{format_date(Time.now)}_#{@course.teacher.show_real_name.to_s}_#{@course.name}_实训数据报告";

    respond_to do |format|
      format.xls {
        send_data(shixun_xls(@homeworks,@course), :type => 'application/octet-stream', :filename => filename_for_content_disposition("#{filename}.xls"))
      }
    end
  end

  # REDO：性能需要优化，这块是管理员后台任务，不影响用户使用
  def sync_students
    school_id = User.find(@course.tea_id).try(:user_extensions).try(:school_id)
    @import_attachments = Attachment.where(:container_id => @course.id, :container_type => "ImportStudent")
    @had_import_count = 0
    @list_count = 0
    if @import_attachments.present?
      @import_attachments.each do |attachment|
        path = attachment.disk_directory
        name = attachment.disk_filename
        if name.split(".").last == "xls" || name.split(".").last == "xlsx"
          lists = readData("files/#{path}/#{name}")
          @list_count += lists.count
          lists.each do |list|
            logger.info("#{list[0]}---#{list[1]}")
            user = User.find_by_sql("SELECT u.* FROM `users` u, `user_extensions` ue where u.id = ue.user_id  and  CONCAT(u.lastname,u.firstname) ='#{list[1]}' and ue.school_id='#{school_id}' and ue.student_id='#{list[0]}'").first
            # 用户存在并且用户是非课程成员
            if user.present? && Member.where(:course_id => @course, :user_id => user.id).blank?
              members = []
              members << Member.new(:role_ids => [10], :user_id => user.id)
              @course.members << members
              StudentsForCourse.create(:student_id => user.id, :course_id => @course.id)
              @had_import_count += 1
            end
          end
        end
      end
    end
    @members = @course.members.joins("join user_extensions on members.user_id = user_extensions.user_id").order("student_id asc").select{|m| m.roles.to_s.include?("Student")}
    @member_count = @members.count
    @limit = 20
    @is_remote = true
    @page = (params['page'] || 1).to_i
    @member_pages = Paginator.new @member_count, @limit, params['page'] || 1
    @offset ||= @member_pages.offset
    @members = paginateHelper @members,@limit

    @teacher_count = TeacherAndAssistantCount @course
    @student_count = studentCount(@course)
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
  end

  def readData(xlsx)
    doc = SimpleXlsxReader.open(xlsx)
    sheet = doc.sheets.first
    sheet.rows.inject([]) { |l, row|
      if row.size >= 3 && row[1] && row[2]
        l << [row[1], row[2]]
      else
        l
      end
    }
  end

  # 邀请码停用/启用
  def set_invite_code_halt
    if User.current.allowed_to?(:as_teacher, @course) || User.current.admin?
      @course.update_attribute('invite_code_halt', @course.invite_code_halt == 0 ? 1 : 0)
    end
  end

  #查找组织
  def search_public_orgs_not_in_course
    condition = '%%'
    if !params[:name].nil?
      condition = "%#{params[:name].strip}%".gsub(" ","")
    end
    limit = 15
    course_org_ids = OrgCourse.find_by_sql("select distinct organization_id from org_courses where course_id = #{params[:id]}").map(&:organization_id)
    #@orgs_not_in_course = User.current.organizations.where("organizations.id not in (#{course_org_ids.join(',')}) and organizations.name like ?", condition).page(params[:page].to_i || 1).per(limit)
    #@org_count = User.current.organizations.where("organizations.id not in (#{course_org_ids.join(',')}) and organizations.name like ?", condition).count
     if course_org_ids.empty?
       @orgs_not_in_course = User.current.organizations.where("name like ?",condition).page((params[:page].to_i || 1)).per(limit)
       @org_count = @orgs_not_in_course.count
     else
       course_org_ids = "(" + course_org_ids.join(',') +  ")"
       @orgs_not_in_course =  User.current.organizations.where("organizations.id not in #{course_org_ids} and organizations.name like ?", condition).page((params[:page].to_i || 1)).per(limit)
       @org_count = @orgs_not_in_course.empty? ? 0 : @orgs_not_in_course.count
     end
     @course_count = Project.course_entities.visible.like(params[:name]).page(params[:page]).count
    @orgs_page = Paginator.new @org_count, limit,params[:page]
    @hint_flag = params[:hint_flag]
    #render :json => {:orgs => @orgs_not_in_course, :count => @org_count}.to_json
    respond_to do |format|
      format.js
    end
  end

  def join
    if User.current.logged?
      cs = CoursesService.new
      @user = User.current
      join = cs.join_course params,@user
      @state = join[:state]
      @course = join[:course]
      # else
      #   @course = Course.find_by_id params[:object_id]
      #   CourseMessage.create(:user_id => @course.tea_id, :course_id => @course.id, :viewed => false,:content=> params[:role],:course_message_id=>User.current.id,:course_message_type=>'JoinCourseRequest')
      #   @state = 6
      # end
    else
      @state = 5 #未登录
    end
    @object_id = @course.id if @course
    respond_to do |format|
      format.js #{ render :partial => 'set_join', :locals => {:user => @user, :course => @course, :object_id => params[:object_id]} }
    end

  end

  def join_course_multi_role
    if User.current.logged?
      cs = CoursesService.new
      @user = User.current
      join = cs.join_course_roles params,@user
      @state = join[:state]
      @course = join[:course]
      if @state == 6
        # 给课堂老师发留言
        if params[:role].include?("7") || params[:role].include?("9")
          notes = @user.show_real_name.to_s + " 申请以#{params[:role].include?('7') ? '教辅' : '教师'}的身份加入班级：<a target='_blank' href='#{members_course_path(@course)}'>#{@course.name}</a>"
          @course.course_teachers.each do |mem|
            user = mem.user
            user.journals_for_messages << JournalsForMessage.new(:user_id => @user.id, :notes => notes, :private => 1, :reply_id => 0)
          end
        end
      end
    else
      @state = 5 #未登录
    end
    @object_id = @course.id if @course
    respond_to do |format|
      format.js
    end
  end

  def unjoin
    if User.current.logged?
      cs = CoursesService.new
      cs.exit_course params,User.current
    end
    redirect_to user_path(User.current)
  end


  def join_private_courses
    respond_to do |format|
      format.js
    end
  end

  #更新课程信息
  def update
    cs = CoursesService.new
    c = cs.edit_course params,@course,User.current
    @course = c[:course]
    if @course.errors.full_messages.count <= 0
      respond_to do |format|
        format.js {
          # render :layout => 'base_courses'
          #flash[:notice] = l(:notice_successful_update)
          #redirect_to settings_course_url(@course)
        }
        format.api  { render_api_ok }
      end
    else
      respond_to do |format|
        format.js {
          # settings
          # redirect_to settings_course_url(@course)
        }
        format.api  { render_validation_errors(@course) }
      end
    end
  end

  def new_join
    @course = Course.find(params[:object_id])
    respond_to do |format|
      format.js
    end
  end

  # 课程搜索
  # add by nwb
  def search
    if params[:name].empty?
      courses = Course.visible
      @courses = paginateHelper courses,10
    else
      courses = Course.visible.where("LOWER(name) like '%#{params[:name].to_s.downcase}%'").order("time desc, created_at desc")
      @courses = paginateHelper courses,10
    end
    @name = params[:name]
    @type = 'courses'
    respond_to do |format|
      format.html {
        render :layout => 'course_base'
      }
      format.atom {
        courses = Course.visible.order('created_on DESC').limit(Setting.feeds_limit.to_i).all
        render_feed(courses, :title => "#{Setting.app_title}: #{l(:label_course_latest)}")
      }
    end

  rescue Exception => e
    if e.message == 'sumbit empty'
      (redirect_to courses_url, :notice => l(:label_sumbit_empty);return)
    end
  end

  def searchmembers
    @subPage_title = l :label_student_list
    @canShowCode = User.current.allowed_to?(:as_teacher,@course) && params[:role] != '1'
    # @is_remote = true
    @sort_type = 'score'
    @score_sort_by = "desc"
    @search_name = "#{params[:name].strip}"
    q = "#{params[:name].strip}"
    if params[:incourse]
      results = searchmember_by_name(student_homework_score(0,0,0,"desc"), q)
    elsif params[:ingroup]
      if params[:search_group_id] == "-1"
        @group = -1
        results = searchmember_by_name(student_homework_score(-1,0,0,"desc"), q)
      else
        @group = CourseGroup.find(params[:search_group_id])
        results = searchmember_by_name(student_homework_score(@group.id,0,0,"desc"), q)
      end
    end
    @is_remote = true
    #@result_count = results.count
    #@results = paginateHelper results, 10
    @results = results
    @search_name = q
    @limit = 50
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @members_count = @results.count
    @mem_pages = Paginator.new @members_count, @limit, @page
    @results = paginateHelper @results, @limit
  end

  def addgroups
    data = {:result=>1}
    @subPage_title = l :label_student_list
    if params[:group_name]
      if @course.course_groups.where(:name => params[:group_name]).count > 0
        data[:result] = 0
      else
        group = CourseGroup.new
        group.name = params[:group_name]
        group.course_id = @course.id
        group.save
      end
    end
    render :json => data
  end

  def deletegroup
    data = {:result=>1}
    group = CourseGroup.find params[:group_id]
    if group
      if group.members.count == 0
        CourseGroup.delete(params[:group_id])
      else
        group.members.update_all(:course_group_id => 0)
        CourseGroup.delete(params[:group_id])
      end
    end
    render :json => data
  end

  def updategroupname
    data = {:result=>1}
    @subPage_title = l :label_student_list
    if params[:group_name] && params[:group_id]
      group = CourseGroup.find(params[:group_id])
      if @course.course_groups.where("name = '#{params[:group_name]}' and id != #{params[:group_id]}").count > 0
        data[:result] = 0
      else
        group.name = params[:group_name]
        group.save
      end
    end
    render :json => data
  end

  def choose_group_allow
    @course.update_attributes(:choose_group_allow => !@course.choose_group_allow)
  end

  # 显示/隐藏课程"开源检索"的入口
  def open_or_close_os_interface
    os_status =  @course.os_allow == 0 ? 1 : 0
    @course.update_attribute(:os_allow, os_status)
    respond_to do |format|
      format.js
    end
  end

  def  valid_ajax
    req = Hash.new(false)
    req[:message] = ''

    valid_attr = params[:valid]
    valid_value = params[:value]
    group_id = params[:group_id]
    # faker = CourseGroup.new

    if valid_attr.eql?('name')
      course = Course.find params[:course_id]
      group_names = course.course_groups.map{|group| group.name unless group.id.to_s == group_id}.select{|group| !group.nil?}
      if group_names.include?(valid_value)
        req[:valid] = false
        req[:message] = l(:modal_valid_unpassing)
      else
        req[:valid] = true
        req[:message] = l(:modal_valid_passing)
      end
      # faker.name = valid_value
      # faker.course_id =  params[:course_id]
      # faker.valid?
      # req[:valid] = faker.errors[:name].blank?
      # req[:message] = faker.errors[:name]
    end
    # req[:message] = l(:modal_valid_passing) if req[:message].blank?
    render :json => req
  end

  def delete_course_students
    if params[:member_ids]
      params[:member_ids].split(",").each do |member_id|
        member = Member.find member_id
        if member
          if member.member_roles.count == 1
            joined = StudentsForCourse.where('student_id = ? and course_id = ?', member.user_id,@course.id).destroy_all
            member.destroy
          else
            member.member_roles.where("role_id = 10").destroy_all
            joined = StudentsForCourse.where('student_id = ? and course_id = ?', member.user_id,@course.id).destroy_all
            member.update_attributes(:course_group_id => 0)
            if params[:group_id] == '0'
              members = @course.members.select{|m| m.roles.to_s.include?("Student")}
            else
              members = @course.members.where("course_group_id = 0").select{|m| m.roles.to_s.include?("Student")}
            end
            member.member_roles.where("role_id = 7 or role_id = 9").first.update_attributes(:is_current => 1)
          end
        end
      end
    end
    @group = @course.course_groups.where(:id => params[:group_id].to_i).first if params[:group_id]
    @teacher_count = searchTeacherAndAssistant(@course).count
    @student_count = @course.student.count
    @no_group_count = @course.members.where("course_group_id = 0").select{|m| m.roles.to_s.include?("Student")}.count
    respond_to do |format|
      format.js
    end
  end

  def teacher_assign_group
    @group = @course.course_groups.where(:id => params[:course_group_id].to_i).first
    if params[:member_ids] && @group
      params[:member_ids].each do |member_id|
        member = Member.find member_id
        member.update_attributes(:course_group_id => params[:course_group_id].to_i) if member
      end
    end
    @teacher_count = searchTeacherAndAssistant(@course).count
    @student_count = @course.student.count
    @no_group_count = @course.members.where("course_group_id = 0").select{|m| m.roles.to_s.include?("Student")}.count
    respond_to do |format|
      format.js
    end
  end

  def join_group
    group = CourseGroup.find(params[:group_id])
    member = Member.where(:course_id => @course.id, :user_id => User.current.id).first
    member.course_group_id = group.id
    member.save
    @group = group

    #@course_groups = @course.course_groups
    #@no_group_count = @course.student.count - @course.members.where("course_group_id != 0").count
    redirect_to has_group_student_list_course_path(@course, :group_id => @group.id)
    #search_group_members group
  end

  def change_manager
    if params[:chooseteacher]
      man_member = Member.where(:course_id => @course.id, :user_id => @course.tea_id).first
      cha_member = Member.find params[:chooseteacher]
      if man_member && cha_member
        cha_member.member_roles.where("role_id = 9 || role_id = 7").first.update_attributes(:role_id => 3, :is_current => 1)
        if cha_member.member_roles.where("role_id = 10").count > 0
          cha_member.member_roles.where("role_id = 10").first.destroy
          StudentsForCourse.where(:course_id => @course.id, :student_id => cha_member.user_id).destroy_all
        end
        @course.update_attributes(:tea_id => cha_member.user_id)
        man_member.member_roles.first.update_attributes(:role_id => 9)
        course_act = CourseActivity.where(:course_id => @course.id, :course_act_id => @course.id, :course_act_type => 'Course').first
        course_act.update_column('user_id', cha_member.user_id)
        UserActivity.where(:act_id => @course.id, :container_type => 'Course', :act_type => 'Course', :container_id => @course.id).first.update_column('user_id', cha_member.user_id)
        ws = WechatService.new
        title = "您已成为班级管理员"
        ws.create_class_notice cha_member.user_id, "create_course_notice", @course.id,title, @course.name, man_member.user.show_name, @course.members.count, "点击查看班级详情。"
      end
    end
  end

  def all_student_list
    @order, @r_sort = params[:order] || "student_id", params[:sort] || "asc"
    @sort = @r_sort == "desc" ? "asc" : "desc"

    @search = params[:search]
    q = params[:search] ? "#{params[:search].strip}" : ""
    # if @order == "student_id"
    #   @members = @course.members.joins("join user_extensions on members.user_id = user_extensions.user_id").order("student_id #{@r_sort}").select{|m| m.roles.to_s.include?("Student")}
    # else
    #   @members = @course.members.joins("join users on members.user_id = users.id").order("CONVERT(lastname USING gbk) COLLATE gbk_chinese_ci #{@r_sort}, login #{@r_sort}").select{|m| m.roles.to_s.include?("Student")}
    # end
    @members = Member.find_by_sql("SELECT members.*,(
          SELECT SUM(student_works.work_score)
          FROM student_works,homework_commons
          WHERE student_works.homework_common_id = homework_commons.id
          AND homework_commons.homework_type in (1, 4)
          AND homework_commons.course_id = #{@course.id}
          AND student_works.user_id = members.user_id
        ) AS score FROM members
        JOIN students_for_courses
        ON students_for_courses.student_id = members.user_id AND students_for_courses.course_id = members.course_id
        WHERE members.course_id = #{@course.id} ORDER BY score desc")
    if q.nil? || q == ""
    else
      members = []
      @members.each do |m|
        username = m.user[:lastname].to_s.downcase + m.user[:firstname].to_s.downcase
        if m && m.user && (m.user.user_extensions[:student_id].to_s.downcase.include?(q) || username.include?(q))
          members << m
        end
      end
      @members = members
    end
    #分页
    @member_count = @members.count
    @all_member_ids = @members.map(&:id).join(",")
    @limit = 20
    @is_remote = true
    @page = (params['page'] || 1).to_i
    @member_pages = Paginator.new @member_count, @limit, params['page'] || 1
    @offset ||= @member_pages.offset
    @members = paginateHelper @members,@limit

    @teacher_count = TeacherAndAssistantCount @course
    @student_count = studentCount(@course)
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
    @import_attachments = Attachment.where(:container_id => @course.id, :container_type => "ImportStudent") # 统计上传的Excel数量
    @left_nav_type = 8
    respond_to do |format|
      format.js
      format.html{render :layout => 'base_courses'}
    end
  end

  def no_group_student_list
    @order, @r_sort = params[:order] || "student_id", params[:sort] || "asc"
    @sort = @r_sort == "desc" ? "asc" : "desc"

    @search = params[:search]
    q = params[:search] ? "#{params[:search].strip}" : ""
    @members = Member.find_by_sql("SELECT members.*,(
          SELECT SUM(student_works.work_score)
          FROM student_works,homework_commons
          WHERE student_works.homework_common_id = homework_commons.id
          AND homework_commons.homework_type in (1, 4)
          AND homework_commons.course_id = #{@course.id}
          AND student_works.user_id = members.user_id
        ) AS score FROM members
        JOIN students_for_courses
        ON students_for_courses.student_id = members.user_id AND students_for_courses.course_id = members.course_id
        WHERE members.course_id = #{@course.id} AND members.course_group_id = 0 ORDER BY score desc")
    # if @order == "student_id"
    #   @members = @course.members.where(:course_group_id => 0).joins("join user_extensions on members.user_id = user_extensions.user_id").order("student_id #{@sort}").select{|m| m.roles.to_s.include?("Student")}
    # else
    #   @members = @course.members.where(:course_group_id => 0).joins("join users on members.user_id = users.id").order("CONVERT(lastname USING gbk) COLLATE gbk_chinese_ci #{@sort}, login #{@b_sort}").select{|m| m.roles.to_s.include?("Student")}
    # end
    if q.nil? || q == ""
    else
      members = []
      @members.each do |m|
        username = m.user[:lastname].to_s.downcase + m.user[:firstname].to_s.downcase
        if m && m.user && (m.user.user_extensions[:student_id].to_s.downcase.include?(q) || username.include?(q))
          members << m
        end
      end
      @members = members
    end
    #分页
    @member_count = @members.count
    @all_member_ids = @members.map(&:id).join(",")
    @limit = 20
    @is_remote = true
    @page = (params['page'] || 1).to_i
    @member_pages = Paginator.new @member_count, @limit, params['page'] || 1
    @offset ||= @member_pages.offset
    @members = paginateHelper @members,@limit

    @teacher_count = TeacherAndAssistantCount @course
    @student_count = studentCount(@course)
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
    @left_nav_type = 9
    respond_to do |format|
      format.js
      format.html{render :layout => 'base_courses'}
    end
  end

  def has_group_student_list
    #@order, @r_sort = params[:order] || "student_id", params[:sort] || "asc"
    #@sort = @r_sort == "desc" ? "asc" : "desc"

    @search = params[:search]
    q = params[:search] ? "#{params[:search].strip}" : ""
    @group = @course.course_groups.find params[:group_id]
    @members = Member.find_by_sql("SELECT members.*,(
          SELECT SUM(student_works.work_score)
          FROM student_works,homework_commons
          WHERE student_works.homework_common_id = homework_commons.id
          AND homework_commons.homework_type in (1, 4)
          AND homework_commons.course_id = #{@course.id}
          AND student_works.user_id = members.user_id
        ) AS score FROM members
        JOIN students_for_courses
        ON students_for_courses.student_id = members.user_id AND students_for_courses.course_id = members.course_id
        WHERE members.course_id = #{@course.id} AND members.course_group_id = #{@group.id} ORDER BY score desc")
    # if @order == "student_id"
    #   @members = @course.members.where(:course_group_id => @group.id).joins("join user_extensions on members.user_id = user_extensions.user_id").select{|m| m.roles.to_s.include?("Student")}.reorder("student_id #{@sort}")
    # else
    #   @members = @course.members.where(:course_group_id => @group.id).joins("join users on members.user_id = users.id").select{|m| m.roles.to_s.include?("Student")}.reorder("CONVERT(lastname USING gbk) COLLATE gbk_chinese_ci #{@sort}, login #{@b_sort}")
    # end
    if q.nil? || q == ""
    else
      members = []
      @members.each do |m|
        username = m.user[:lastname].to_s.downcase + m.user[:firstname].to_s.downcase
        if m && m.user && (m.user.user_extensions[:student_id].to_s.downcase.include?(q) || username.include?(q))
          members << m
        end
      end
      @members = members
    end

    #分页
    @member_count = @members.count
    @all_member_ids = @members.map(&:id).join(",")
    @limit = 20
    @is_remote = true
    @page = (params['page'] || 1).to_i
    @member_pages = Paginator.new @member_count, @limit, params['page'] || 1
    @offset ||= @member_pages.offset
    @members = paginateHelper @members,@limit

    @teacher_count = TeacherAndAssistantCount @course
    @student_count = studentCount(@course)
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
    @left_nav_type = 10

    respond_to do |format|
      format.js
      format.html{render :layout => 'base_courses'}
    end
  end

  def choose_course_group
    @member_ids = params[:member_ids].split(",")
    @type = params[:type]
    @groups = @course.course_groups.order("name asc")
    respond_to do |format|
      format.js
    end
  end

  def search_course_teacher_list
    q = params[:search] ? "#{params[:search].strip}" : ""
    @members = searchTeacherAndAssistantNoManager(@course)
    if q.nil? || q == ""
    else
      members = []
      @members.each do |m|
        username = m.user[:lastname].to_s.downcase + m.user[:firstname].to_s.downcase
        if m && m.user && (m.user[:login].to_s.downcase.include?(q) ||  m.user[:mail].to_s.downcase.include?(q) || username.include?(q))
          members << m
        end
      end
      @members = members
    end
  end

  #查找未分班的学生
  def search_not_group_member
    @group = CourseGroup.find params[:group_id]
    @search = params[:search]
    q = params[:search] ? "#{params[:search].strip}" : ""
    @members = []
    @members = @course.members.where("course_group_id = 0").joins("join user_extensions on members.user_id = user_extensions.user_id").order("student_id asc").select{|m| m.roles.to_s.include?("Student")}
    if q.nil? || q == ""
    else
      members = []
      @members.each do |m|
        username = m.user[:lastname].to_s.downcase + m.user[:firstname].to_s.downcase
        if m && m.user && (m.user[:login].to_s.downcase.include?(q) ||  m.user.user_extensions[:student_id].to_s.downcase.include?(q) || username.include?(q))
          members << m
        end
      end
      @members = members
    end
    @is_search = params[:is_search] ? 1 : 0
    respond_to do |format|
        format.js
    end
  end

  def add_members
    @group = CourseGroup.find params[:group_id]
    if @group && params[:chooseStudent]
      params[:chooseStudent].each do |mem|
        member = Member.find mem
        if member && member.course == @course
          member.update_attribute('course_group_id', @group.id)
        end
      end
      redirect_to group_member_course_path(@course, :group_id => @group.id)
    end
  end

  def unjoin_group
    #@subPage_title = l :label_student_list
    group = CourseGroup.find(params[:group_id])
    member = Member.find params[:member]
    @user = member.user
    member.course_group_id = 0
    member.save
    @group = group
    @course = @group.course
    @teacher_count = searchTeacherAndAssistant(@course).count
    @student_count = @course.student.count
    @no_group_count = @course.members.where("course_group_id = 0").select{|m| m.roles.to_s.include?("Student")}.count
  end

  def searchgroupmembers
    @subPage_title = l :label_student_list
    @render_file = 'new_member_list'
    @canShowCode = isCourseTeacher(User.current.id,@course) && params[:role] != '1'
    @is_remote = true
    @sort_type = 'score'
    @score_sort_by = "desc"
    @search_name = ""
    if params[:group_id] && params[:group_id] != "0" && params[:group_id] != "-1"
      @group = CourseGroup.find(params[:group_id])
      @results = student_homework_score(@group.id,0, 0,"desc")
      # @results = paginateHelper @results, 10
    elsif params[:group_id] && params[:group_id] == "-1"
      @group = -1
      @results = student_homework_score(-1, 0, 10,"desc")
    else
      page_from = params[:page].nil? ? 0 : (params[:page].to_i - 1)
      @results = student_homework_score(0,page_from, 10,"desc")
    end
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
    @course_groups = @course.course_groups
    @limit = 50
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @members_count = @results.count
    @mem_pages = Paginator.new @members_count, @limit, @page
    @results = paginateHelper @results, @limit
  end

  def group_member
    @canShowCode = isCourseTeacher(User.current.id,@course)
    @sort_type = 'score'
    @score_sort_by = "desc"
    if params[:group_id] && params[:group_id] != "0" && params[:group_id] != "-1"
      @group = CourseGroup.find(params[:group_id])
      @results = student_homework_score(@group.id,0, 0,"desc")
    else
      page_from = params[:page].nil? ? 0 : (params[:page].to_i - 1)
      @results = student_homework_score(0,page_from, 10,"desc")
    end
    @limit = 50
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @members_count = @results.count
    @mem_pages = Paginator.new @members_count, @limit, @page
    @results = paginateHelper @results, @limit
    respond_to do |format|
      format.html {render :layout => 'base_courses'}
      format.js
    end
  end

  def member
    if User.current.member_of_course?(@course) || User.current.admin?
    # 当前用户查看班级消息时，设置消息为已读
    update_messsages_to_viewed("CourseMessage", @course)
    ## 有角色参数的才是课程，没有的就是项目
    @order, @r_sort = params[:order] || "student_id", params[:sort] || "asc"
    @sort = @r_sort == "desc" ? "asc" : "desc"

    @search = params[:search]
    q = params[:search] ? "#{params[:search].strip}" : ""
    if params[:role] == 't'
      @members = searchTeacherAndAssistant(@course)
    elsif params[:role] == 'as'
      if @order == "student_id"
        @members = @course.members.joins("join user_extensions on members.user_id = user_extensions.user_id").order("student_id #{@sort}").select{|m| m.roles.to_s.include?("Student")}
      else
        @members = @course.members.joins("join users on members.user_id = users.id").order("CONVERT(lastname USING gbk) COLLATE gbk_chinese_ci #{@sort}, login #{@b_sort}").select{|m| m.roles.to_s.include?("Student")}
      end
    elsif params[:role] == 'ns'
      if @order == "student_id"
        @members = @course.members.where(:course_group_id => 0).joins("join user_extensions on members.user_id = user_extensions.user_id").order("student_id #{@sort}").select{|m| m.roles.to_s.include?("Student")}
      else
        @members = @course.members.where(:course_group_id => 0).joins("join users on members.user_id = users.id").order("CONVERT(lastname USING gbk) COLLATE gbk_chinese_ci #{@sort}, login #{@b_sort}").select{|m| m.roles.to_s.include?("Student")}
      end
    else
      @group = @course.course_groups.find params[:role]
      if @order == "student_id"
        @members = @course.members.where(:course_group_id => @group.id).joins("join user_extensions on members.user_id = user_extensions.user_id").order("student_id #{@sort}").select{|m| m.roles.to_s.include?("Student")}
      else
        @members = @course.members.where(:course_group_id => @group.id).joins("join users on members.user_id = users.id").order("CONVERT(lastname USING gbk) COLLATE gbk_chinese_ci #{@sort}, login #{@b_sort}").select{|m| m.roles.to_s.include?("Student")}
      end
    end

    if q.nil? || q == ""
    else
      members = []
      @members.each do |m|
        username = m.user[:lastname].to_s.downcase + m.user[:firstname].to_s.downcase
        if m && m.user && (m.user.user_extensions[:student_id].to_s.downcase.include?(q) || username.include?(q))
          members << m
        end
      end
      @members = members
    end
    #分页
    @member_count = @members.count
    @limit = 20
    @is_remote = true
    @page = (params['page'] || 1).to_i
    @member_pages = Paginator.new @member_count, @limit, params['page'] || 1
    @offset ||= @member_pages.offset
    @members = paginateHelper @members,@limit

    @teacher_count = TeacherAndAssistantCount @course
    @student_count = studentCount(@course)
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count

    respond_to do |format|
      if @search
        format.js
      else
        format.html {render :layout => 'base_edu'}
        format.js
      end
    end
    else
      render_403
    end
  end

  def export_course_member_excel
    q = params[:name] ? "#{params[:name].strip}" : ""
    if params[:group_id] && params[:group_id] != "0" && params[:group_id] != "-1"
      group = CourseGroup.find params[:group_id]
      unless group.nil?
        @all_members = searchmember_by_name(student_homework_score(group.id,0,0,"desc"), q)
      end
    elsif params[:group_id] && params[:group_id] == "-1"
      @all_members = searchmember_by_name(student_homework_score(-1, 0, 10,"desc"), q)
    else
      @all_members = searchmember_by_name(student_homework_score(0, 0, 10,"desc"), q)
    end

    @homeworks = @course.homework_commons.where("publish_time <= '#{Time.now}'").order("created_at asc")
    filename="#{@course.teacher.lastname.to_s + @course.teacher.firstname.to_s }_#{@course.name}_#{l(:excel_member_list)}";

    respond_to do |format|
      format.xls {
        send_data(member_to_xls(@homeworks,@course,@all_members,@course.course_groups), :type => "text/excel;charset=utf-8; header=present",
                  :filename => filename_for_content_disposition("#{filename}.xls"))
      }
    end
  end

  def member_score_sort
    @canShowCode = isCourseTeacher(User.current.id,@course) && params[:role] != '1'
    @subPage_title = l :label_student_list
    @render_file = 'new_member_list'
    @is_remote = true
    @sort_type = params[:sort_type] if params[:sort_type]
    @score_sort_by = params[:sort_by] if params[:sort_by]
    @search_name = params[:search_name] if params[:search_name]
    group_id = params[:group_id]
    if !@search_name.nil?
      if group_id == '0'
        #page = params[:page].nil? ? 0 : (params['page'].to_i - 1)
        @results = searchmember_by_name(student_homework_score(0,0,0,@score_sort_by,@sort_type), @search_name)
        # @results = paginateHelper @results, 10
      elsif group_id == '-1'
        @group = -1
        @results = searchmember_by_name(student_homework_score(-1,0,0,@score_sort_by,@sort_type), @search_name)
      else
        @group = CourseGroup.find(group_id)
        @results = searchmember_by_name(student_homework_score(group_id, 0, 0,@score_sort_by,@sort_type),@search_name)
        # @results = paginateHelper @results, 10
      end
    else
      if group_id == '0'
        page = params[:page].nil? ? 0 : (params['page'].to_i - 1)
        @results = student_homework_score(0,page, 10,@score_sort_by,@sort_type)
      elsif group_id == '-1'
        @group = -1
        @results = student_homework_score(-1,0, 10,@score_sort_by,@sort_type)
      else
        @group = CourseGroup.find(group_id)
        @results = student_homework_score(group_id, 0, 0,@score_sort_by,@sort_type)
      end
    end
    @limit = 50
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @members_count = @results.count
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
    @mem_pages = Paginator.new @members_count, @limit, @page
    @results = paginateHelper @results, @limit
  end
  # 显示每个学生的作业评分详情
  def show_member_score
    @member_score = Member.find(params[:member_id]) if params[:member_id]
    respond_to do |format|
      format.html {render :layout => 'course_base'}
      format.js
    end
  end

  # 显示每个学生的作业评分详情
  def show_member_act_score
    @member = Member.find(params[:member_id]) if params[:member_id]
    respond_to do |format|
      format.html {render :layout => 'course_base'}
      format.js
    end
  end

  def delete_member
    member = @course.members.find params[:member_id]
    student_role = member.member_roles.where("role_id = 10").first
    teacher_role = member.member_roles.where("role_id = 7 || role_id = 9").first

    joined = StudentsForCourse.where('student_id = ? and course_id = ?', member.user_id,@course.id)
    joined.destroy_all
    if member && member.deletable? && student_role
      user_admin = CourseInfos.where("user_id = ? and course_id = ?", member.user_id, @course.id)
      if user_admin.size > 0
        user_admin.destroy_all
      end

      if member.member_roles.count > 1&& student_role && teacher_role
        student_role.destroy
        teacher_role.update_attribute("is_current", 1)
        member.update_attribute("course_group_id", 0)
      else
        member.destroy
      end
      #移出课程发送消息
      CourseMessage.create(:user_id => member.user_id, :course_id => @course.id, :course_message_type => "RemoveFromCourse", :viewed => false, :course_message_id => User.current.id)

      @course_groups = @course.course_groups
      @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
      @canShowCode = isCourseTeacher(User.current.id,@course)
      respond_to do |format|
        format.js
      end
    end
  end

  def handle_course courses, activities
    course_activity_count_array=activities.values()
    course_array=[]
    i=0;
    courses.each do |course|
      course_array[i]=course
      i=i+1
    end
    courses=desc_sort_course_by_avtivity(course_activity_count_array, course_array)
    return courses
  end

  def settings
    if User.current.allowed_to?(:as_teacher,@course)
      course_module = @course.course_modules
      if course_module.blank?
        type = ["news", "boards", "homework", "exercises", "poll", "statistics", "attachment"]
        for i in 0..6
          CourseModule.create(:course_id => @course.id, :type => type[i], :position => i + 1, :hidden => 0 )
        end
        course_module = @course.course_modules
      end
      @curr_page = '设置'
      respond_to do |format|
        format.html { render :layout => 'base_edu_course' }
        format.api { render_validation_errors(@course) }
      end
    else
      render_403
    end
  end

  def members
    #添加成员消息状态
    join_course_messages = CourseMessage.where("user_id =? and course_message_type =? and course_id =? and status = ? and viewed =?", User.current.id, 'JoinCourse', @course.id, 1, 0)
    join_course_messages.update_all(:viewed => true)

    @member ||= @course.members.new
    @role = params[:role] if params[:role]

    @teachers = searchTeacherAndAssistant(@course)
    @teacher_count = @teachers.count
    @student_count = studentCount(@course)
    @no_group_count = @course.members.where(:course_group_id => 0).select{|m| m.roles.to_s.include?("Student")}.count
    @apply_users = CourseMessage.where(:course_id => @course.id,:course_message_type => 'JoinCourseRequest', :status => 0)

    @curr_page = '成员管理'
    respond_to do |format|
      format.js
      format.html { render :layout => 'base_edu_course' }
      format.api { render_validation_errors(@course) }
    end
  end

  def update_position

    course_module = @course.course_modules

    for i in 0..6
      case params[:position][i]
        when "boards"
          course_module.where(:module_type => 'boards').first.update_attribute('position', i+1)
        when "news"
          course_module.where(:module_type => 'news').first.update_attribute('position', i+1)
        when "homework"
          course_module.where(:module_type => 'homework').first.update_attribute('position', i+1)
        when "exercises"
          course_module.where(:module_type => 'exercises').first.update_attribute('position', i+1)
        when "poll"
          course_module.where(:module_type => 'poll').first.update_attribute('position', i+1)
        when "statistics"
          course_module.where(:module_type => 'statistics').first.update_attribute('position', i+1)
        when "attachment"
          course_module.where(:module_type => 'attachment').first.update_attribute('position', i+1)
      end
    end

    # params[:message] 有值 就更行字段： 0为不隐藏，1为隐藏
    type = ["news", "boards", "attachment", "homework", "poll", "exercises", "statistics"]

    hidden = type - params[:message]
    course_module.where(:module_type => params[:message]).update_all(:hidden => 0)
    course_module.where(:module_type => hidden).update_all(:hidden => 1)


    @modules = @course.course_modules.order("position asc")
    @course_board = @course.boards.where("parent_id is NULL").first
    @board = @course_board.children.order("position asc")
    @board_count = @board.count

    respond_to do |format|
      format.js
    end
  end

  def private_or_public
    if @course.is_public == 0
      @course.update_attributes(:is_public => 1)
    else
      @course.update_attributes(:is_public => 0)
    end
    if @course.is_public == 0
      course_status = CourseStatus.find_by_course_id(@course.id)
      course_status.destroy if course_status
    elsif @course.is_public == 1
      course_status = CourseStatus.find_by_course_id(@course.id)
      course_status.destroy if course_status
      course_status = CourseStatus.create(:course_id => @course.id,  :grade => 0)
    end
    @syllabus = @course.syllabus
    respond_to do |format|
      format.js
    end
  end

  def search_member
    if User.current.allowed_to?(:as_teacher,@course) || User.current.admin
      q = "#{params[:name].strip}"
      @roles = Role.givable.all[3..5]
      if q.nil? || q == ""
        @members = @course.member_principals.includes(:roles, :principal).all.sort
      else
        @members = searchmember_by_name(@course.member_principals.includes(:roles, :principal).all.sort,q)
      end

    else
      render_403
    end
  end

  def create
    cs = CoursesService.new
    @course = cs.create_course(params,User.current)[:course]
    if params[:copy_course]
      copy_course = Course.find params[:copy_course].to_i
      @course.is_copy = params[:copy_course].to_i
      @course.open_student = copy_course.open_student
      @course.publish_resource = copy_course.publish_resource
      @course.save

      #copy avatar
      copy_avatar(@course, copy_course)

      if params[:course_content_type]
        params[:course_content_type].each do |type|
          case type
            when "0"
              homeworks = copy_course.homework_commons
              homeworks.each do |homework|
                new_homework = HomeworkCommon.new
                new_homework.name = homework.name
                new_homework.user_id = User.current.id
                new_homework.description = homework.description
                new_homework.publish_time = Time.now + 30.days
                new_homework.end_time = Time.now + 60
                new_homework.homework_type = homework.homework_type
                new_homework.late_penalty = homework.late_penalty
                new_homework.course_id = @course.id
                new_homework.teacher_priority = homework.teacher_priority
                new_homework.anonymous_comment = homework.anonymous_comment
                new_homework.quotes = 0
                new_homework.is_open = homework.is_open
                homework.attachments.each do |attachment|
                  att = attachment.copy
                  att.container_id = nil
                  att.container_type = nil
                  att.copy_from = attachment.id
                  att.save
                  new_homework.attachments << att
                end
                homework_detail_manual = homework.homework_detail_manual
                homework_detail_programing = homework.homework_detail_programing
                homework_detail_group = homework.homework_detail_group
                if homework_detail_manual
                  new_homework.homework_detail_manual = HomeworkDetailManual.new
                  new_homework_detail_manual =  new_homework.homework_detail_manual
                  new_homework_detail_manual.ta_proportion = homework_detail_manual.ta_proportion
                  new_homework_detail_manual.comment_status = 0
                  new_homework_detail_manual.evaluation_start = Date.today + 67
                  new_homework_detail_manual.evaluation_end = Date.today + 74
                  new_homework_detail_manual.evaluation_num = homework_detail_manual.evaluation_num
                  new_homework_detail_manual.absence_penalty = homework_detail_manual.absence_penalty
                end
                if homework_detail_programing
                  new_homework.homework_detail_programing = HomeworkDetailPrograming.new
                  new_homework.homework_detail_programing.ta_proportion = homework_detail_programing.ta_proportion
                  new_homework.homework_detail_programing.language = homework_detail_programing.language
                  homework.homework_tests.each_with_index do |homework_test|
                    new_homework.homework_tests << HomeworkTest.new(
                        input: homework_test.input,
                        output: homework_test.output
                    )
                  end
                end

                if homework_detail_group
                  new_homework.homework_detail_group = HomeworkDetailGroup.new
                  new_homework.homework_detail_group.min_num = homework_detail_group.min_num
                  new_homework.homework_detail_group.max_num = homework_detail_group.max_num
                  new_homework.homework_detail_group.base_on_project = homework_detail_group.base_on_project
                end
                if new_homework.save
                  new_homework_detail_manual.save if new_homework_detail_manual
                  new_homework.homework_detail_programing.save if new_homework.homework_detail_programing
                  new_homework.homework_detail_group.save if new_homework.homework_detail_group
                end
                homework.update_attribute(:quotes, homework.quotes+1)
              end
            when "1"
              attachments = copy_course.attachments
              attachments.each do |attachment|
                attach_copied_obj = attachment.copy
                attach_copied_obj.tag_list.add(attachment.tag_list) # tag关联
                attach_copied_obj.container = @course
                attach_copied_obj.created_on = Time.now
                attach_copied_obj.author_id = User.current.id
                attach_copied_obj.copy_from = attachment.copy_from.nil? ? attachment.id : attachment.copy_from
                attach_copied_obj.is_publish = 0
                attach_copied_obj.publish_time = Time.now + 30.days
                if attach_copied_obj.attachtype == nil
                  attach_copied_obj.attachtype = 4
                end
                attach_copied_obj.save
                update_quotes attach_copied_obj
              end
          end
        end
      end

=begin
      if params[:checkAll]
        attachments = copy_course.attachments
        attachments.each do |attachment|
          attach_copied_obj = attachment.copy
          attach_copied_obj.tag_list.add(attachment.tag_list) # tag关联
          attach_copied_obj.container = @course
          attach_copied_obj.created_on = Time.now
          attach_copied_obj.author_id = User.current.id
          attach_copied_obj.copy_from = attachment.copy_from.nil? ? attachment.id : attachment.copy_from
          if attach_copied_obj.attachtype == nil
            attach_copied_obj.attachtype = 4
          end
          attach_copied_obj.save
          update_quotes attach_copied_obj
        end
      elsif params[:course_attachment_type]
        copy_attachments = []
        params[:course_attachment_type].each do |type|
          case type
            when "1"
              tag_name = l(:label_courseware)
            when "2"
              tag_name = l(:label_software)
            when "3"
              tag_name = l(:label_media)
            when "4"
              tag_name = l(:label_code)
            when "6"
              tag_name = "论文"
            else
              tag_name = ""
          end
          if tag_name == ""
            tag_attachments = copy_course.attachments.select{|attachment|
              !attachment.tag_list.include?('课件') &&
                  !attachment.tag_list.include?('软件') &&
                  !attachment.tag_list.include?('媒体') &&
                  !attachment.tag_list.include?('代码') &&
                  !attachment.tag_list.include?('论文')  }
          else
            tag_attachments = copy_course.attachments.select{|attachment| attachment.tag_list.include?(tag_name)}
          end
          tag_attachments.each do |attach|
            next if copy_attachments.include?(attach)
            copy_attachments << attach
          end
        end
        unless copy_attachments.blank?
          copy_attachments.each do |c_attach|
            attach_copied_obj = c_attach.copy
            attach_copied_obj.tag_list.add(c_attach.tag_list) # tag关联
            attach_copied_obj.container = @course
            attach_copied_obj.created_on = Time.now
            attach_copied_obj.author_id = User.current.id
            attach_copied_obj.copy_from = c_attach.copy_from.nil? ? c_attach.id : c_attach.copy_from
            if attach_copied_obj.attachtype == nil
              attach_copied_obj.attachtype = 4
            end
            attach_copied_obj.save
            update_quotes attach_copied_obj
          end
        end
      end
=end
    end
    if @course
      #发送微信消息
      count = ShieldWechatMessage.where("container_type='User' and container_id=#{User.current.id} and shield_type='Course' and shield_id=#{@course.id}").count
      if count == 0
        ss  = SyllabusesService.new
        ss.send_wechat_create_class_notice User.current,@course
      end
      respond_to do |format|
        flash[:notice] = l(:notice_successful_create)
        format.html {redirect_to course_url(@course)}
        format.api { render :action => 'show', :status => :created, :location => url_for(:controller => 'courses', :action => 'show', :id => @course.id) }
      end
    else
      respond_to do |format|
        flash[:notice] = l(:notice_create_failed)
        # @course = Course.new
        format.html { redirect_to new_course_path } #Added by young
        format.api { render_validation_errors(@course) }
      end
    end
  end

  def course
    @school_id = params[:school_id]
    per_page_option = 10
    if @school_id == "0" or @school_id.nil?
      @courses_all = Course.active.visible.
          joins("LEFT JOIN #{CourseStatus.table_name} ON #{Course.table_name}.id = #{CourseStatus.table_name}.course_id")
    else
      @courses_all = Course.active.visible.
          joins("LEFT JOIN #{CourseStatus.table_name} ON #{Course.table_name}.id = #{CourseStatus.table_name}.course_id").
          joins(:course_extra).
          where("#{Course.table_name}.school_id = ?", @school_id)
    end

    @course_count = @courses_all.count
    @course_pages = Paginator.new @course_count, per_page_option, params['page']
    @course_activity_count=Hash.new
    @courses_all.each do |course|
      @course_activity_count[course.id]=0
    end
    case params[:course_sort_type]
      when '0'
        @courses =  @courses_all.order("created_on desc")
        @s_type = 0
        @courses =  @courses.offset(@course_pages.offset).limit(@course_pages.per_page)
        @course_activity_count=get_course_activity @courses,@course_activity_count
      when '1'
        @courses = @courses_all.order("course_ac_para desc")
        @s_type = 1
        @courses =  @courses.offset(@course_pages.offset).limit(@course_pages.per_page)
        @course_activity_count=get_course_activity @courses,@course_activity_count
      when '2'
        @courses = @courses_all.order("watchers_count desc")
        @s_type = 2
        @courses =  @courses.offset(@course_pages.offset).limit(@course_pages.per_page)
        @course_activity_count=get_course_activity @courses,@course_activity_count
      when '3'
        @course_activity_count=get_course_activity @courses_all,@course_activity_count
        @courses=handle_course @courses_all,@course_activity_count
        @s_type = 3
        @courses =  @courses[@course_pages.offset, @course_pages.per_page]
      else
        @s_type = 0
        @courses = @courses_all.order("created_on desc")
        @courses =  @courses.offset(@course_pages.offset).limit(@course_pages.per_page)
        @course_activity_count=get_course_activity @courses,@course_activity_count
    end

    respond_to do |format|
      format.html {
        render :layout => 'new_base'
      }
      format.api  {
      }
      format.atom {
        courses = Course.visible.order('created_on DESC').limit(Setting.feeds_limit.to_i).all
        render_feed(courses, :title => "#{Setting.app_title}: #{l(:label_course_latest)}")
      }
    end
  end

  def new
    if User.current.login?
      @course_type = params[:course_type] ||= params[:course]
      @issue_custom_fields = IssueCustomField.sorted.all
      @trackers = Tracker.sorted.all
      @course = Course.new
      @course.safe_attributes = params[:course]
      @syllabus = Syllabus.where("id = #{params[:syllabus_id].to_i}").first if params[:syllabus_id]
      # month = Time.now.month
      render :layout => 'base_edu'
    else
      redirect_to signin_url
    end
  end

  def search_course_list
    data = {courses: []}
    search = params[:search] ? "%#{params[:search].strip}%" : "%%"
    CourseList.where("name like '#{search}'").each do |course|
      option = []
      option << course.name.to_s
      option << course.id
      data[:courses] << option
    end
    render :json => data
  end

  def desc_sort_course_by_avtivity(activity_count, courses)
    return courses if activity_count.size<2
    (activity_count.size-2).downto(0) do |i|
      (0..i).each do |j|
        if activity_count[j]<activity_count[j+1]
          courses[j], courses[j+1]=courses[j+1], courses[j]
          activity_count[j], activity_count[j+1]=activity_count[j+1], activity_count[j]
        end
      end
    end
    return courses
  end


  def toggleCourse
    @course_prefs = Course.find_by_extra(@course.extra)
    unless (User.current.allowed_to?(:as_teacher,@course_prefs) || User.current.admin?)
      render_403
    end
  end

  def is_deleted
    if @course.is_delete == 1 and !User.current.admin?
      render_404
      return
    end
  end

  def get_courses
    @user = User.current
    membership = @user.coursememberships.all
    membership.sort! {|older, newer| newer.created_on <=> older.created_on }
    @memberships = []
    membership.collect { |e|
      @memberships.push(e)
    }
    @memberships_doing = []
    @memberships_done  = []
    now_time = Time.now.year
    @memberships.map { |e|
      end_time = e.course.get_time.year
      isDone = course_endTime_timeout?(e.course)
      if isDone
        @memberships_done.push e
      else
        @memberships_doing.push e
      end
    }
  end

  def finishcourse
    yesterday = Date.today.prev_day.to_time
    @course_prefs.endup_time = yesterday
    @save_flag = @course_prefs.save
    get_courses

    respond_to do |format|
      format.js
    end
  end


  def restartcourse
    day = Time.parse("3000-01-01")

    @course_prefs.endup_time = day
    @save_flag = @course_prefs.save
    get_courses

    respond_to do |format|
      format.js {
        render action:'finishcourse'
      }
    end
  end

  def course_activity
    redirect_to course_url(@course, type: params[:type], page: params[:page])
  end

  def show
    # 被删除的课程只有超级管理员才能看到，is_delete为1的时候，标记课程被删除
    # if @course.is_delete == 1 && !User.current.admin?
    #   render_403
    #   return
    # end
    #更新创建课程消息状态
    course_request_messages = CourseMessage.where(:user_id => User.current.id, :course_id => @course.id, :course_message_type => ["CourseRequestDealResult", "Course"], :viewed => false)
    course_request_messages.update_all(:viewed => true)

    # create_course_messages = @course.course_messages.where("user_id =? and course_message_type =? and course_id =? and viewed =?", User.current.id, 'Course', @course.id, 0)
    # create_course_messages.update_all(:viewed => true)

    #更新申请结果反馈消息的状态
    # course_request_messages = CourseMessage.where("user_id =? and course_id =? and course_message_type =? and viewed =?", User.current.id, @course.id,  'CourseRequestDealResult', false)
    # course_request_messages.update_all(:viewed => true)

    course_activities = @course.course_activities.where("course_act_type in ('HomeworkCommon', 'Attachment', 'Course', 'Message')")
    @canShowRealName = User.current.member_of_course? @course
    @page = params[:page] ? params[:page].to_i + 1 : 0
    if params[:type].present?
      case params[:type]
        when "common_homework"
          homework_ids = @course.homework_commons.where(:homework_type => 1)
          @course_activities = course_activities.where(:course_act_type => 'HomeworkCommon', :course_act_id => homework_ids.map(&:id)).order('updated_at desc')
        when "shixun_homework"
          homework_ids = @course.homework_commons.where(:homework_type => 4)
          @course_activities = course_activities.where(:course_act_type => 'HomeworkCommon', :course_act_id => homework_ids.map(&:id)).order('updated_at desc')
        when "news"
          @course_activities = course_activities.where("course_act_type = 'News'").order('updated_at desc')
        when "message"
          @course_activities = course_activities.where("course_act_type = 'Message'").order('updated_at desc')
        when "poll"
          @course_activities = course_activities.where("course_act_type = 'Poll'").order('updated_at desc')
        when "attachment"
          @course_activities = course_activities.where("course_act_type = 'Attachment'").order('updated_at desc')
        when "journalsForMessage"
          @course_activities = course_activities.where("course_act_type = 'JournalsForMessage'").order('updated_at desc')
        else
          @course_activities = course_activities.order('updated_at desc')
      end
    else
      @course_activities = course_activities.order('updated_at desc')
    end
    @course_activities_count = @course_activities.count
    @course_activities = @course_activities.limit(10).offset(@page * 10)
    @type = params[:type]

    @left_nav_type = 1
    @show_page = true
    respond_to do |format|
      format.js
      format.html{render :layout => 'base_courses'}
      format.api
    end
  end

  #判断指定用户是否为课程教师
  def isCourseTeacher(id,course)
    result = false
    user = User.find(id)
    if !user.nil? && user.allowed_to?(:as_teacher,course)
      result = true
    end
    result
  end

  def feedback
    CourseMessage.where("user_id = ? and course_id = ?", User.current, @course.id).update_all(:viewed => true)

    if (User.current.admin? || @course.is_public == 1 || (@course.is_public == 0 && User.current.member_of_course?(@course)))
      page = params[:page]
      # Find the page of the requested reply
      @jours = @course.journals_for_messages.where('m_parent_id IS NULL').order('created_on DESC')
      @jour_count = @jours.count
      @limit =  10
      if params[:r] && page.nil?
        offset = @jours.count(:conditions => ["#{JournalsForMessage.table_name}.id > ?", params[:r].to_i])
        page = 1 + offset / @limit
      end
      @jour = paginateHelper @jours,10
      @state = false
      @left_nav_type = 6
      respond_to do |format|
        format.html{render :layout => 'base_courses'}
        format.api
      end
    else
      render_403
    end
  end

  def search_homework_member homeworks,name
    if name == ""
      select_homework = homeworks
    else
      name = name.downcase
      select_homework = homeworks.select{ |homework|
        homework.user[:login].to_s.downcase.include?(name) ||  homework.user.user_extensions[:student_id].to_s.downcase.include?(name) || (homework.user[:lastname].to_s.downcase + homework.user[:firstname].to_s.downcase).include?(name)
      }
    end
    select_homework
  end

  # 作业查重
  def code_repeat
    #代码查重新加的
    @index = params[:index]
    @order,@b_sort,@name,@group = params[:order] || "score",params[:sort] || "desc",params[:name] || "",params[:group]

    @is_teacher = User.current.allowed_to?(:as_teacher,@course) || User.current.admin?

    if !@is_teacher
      render_403
      return
    end

    @homework = HomeworkCommon.find params[:homework]
                                                                                                                                                                                                        #order("#{@order} #{@b_sort}"
    @student_works = search_homework_member @homework.student_works.select("student_works.*,IF(final_score is null,null,IF(final_score = 0, 0, final_score - absence_penalty - late_penalty)) as score").order("simi_value desc").has_committed,@name

    @works_hash = {}

    @student_works.each do |tmpwork|
      @works_hash[tmpwork.id] = tmpwork
      puts tmpwork.id
    end

    respond_to do |format|
      format.html {render :layout => 'base_edu'}
    end
  end

  def show_comparecode
    @index = params[:index]
    src_id = params[:src_id]
    dst_id = params[:dst_id]

    @is_teacher = User.current.allowed_to?(:as_teacher,@course) || User.current.admin?

    if !@is_teacher
      render_403
      return
    end

    src_work = StudentWork.where("id =?", src_id).first

    @homework = HomeworkCommon.find params[:homework_id]

    @simi_value = src_work.simi_value.to_i

    @src_code = src_work.description
    src_user = User.where("id =?", src_work.user_id).first

    @src_username = src_user.try(:realname) != " " ? src_user.lastname + src_user.firstname : src_user.try(:login)

    #descriotion  user name

    dst_work = StudentWork.where("id =?", dst_id).first
    @dst_code = dst_work.description
    dst_user = User.where("id =?", dst_work.user_id).first

    @dst_username = dst_user.try(:realname) != " " ? dst_user.lastname + dst_user.firstname : dst_user.try(:login)

    respond_to do |format|
      format.html{render :layout => 'base_edu'}
    end
  end

  #根据已有课程复制课程
  #param id:已有课程ID
  def copy_course
    if @course
      @new_course = Course.new
      respond_to do |format|
        format.js
      end
=begin
      @new_course = Course.new @course.attributes
      @new_course.tea_id = User.current.id
      @new_course.created_at = DateTime.now
      @new_course.updated_at = DateTime.now
      @new_course.endup_time = nil
      if @new_course.save
        r = Role.givable.find_by_id(Setting.new_project_user_role_id.to_i) || Role.givable.first
        m = Member.new(:user => User.current, :roles => [r])
        m.project_id = -1
        course = CourseInfos.new(:user_id => User.current.id, :course_id => @new_course.id)
        #user_grades = UserGrade.create(:user_id => User.current.id, :course_id => @course.id)
        if @new_course.is_public == 1
          course_status = CourseStatus.create(:course_id => @new_course.id, :watchers_count => 0, :changesets_count => 0, :grade => 0, :course_type => 1)
        end
        @new_course.members << m
        @new_course.course_infos << course
        redirect_to settings_course_url @new_course
      end
=end
    else
      render_404
    end
  end

  #从课程创建的老师那里选择课程大纲
  def course_outline
    @teacher = User.find(@course.tea_id)
    @blog_articles = @teacher.blog.articles
    @is_in_show_outline_page = params[:is_in_show_outline_page]
    respond_to do |format|
      format.js
    end
  end

  #根据关键字搜索，查找方法一样的，但返回内容不一样
  def search_course_outline
    @article_title = params[:title]
    @teacher = User.find(@course.tea_id)
    @blog_articles = @teacher.blog.articles.like(@article_title)
    render :json=>@blog_articles.to_json
  end

  #设置或者更改课程的大纲
  def set_course_outline
    @course.outline = params[:outline_id]
    @course.save
    @is_in_show_outline_page = params[:is_in_show_outline_page]
    respond_to do |format|
      format.js
    end
  end

  #显示课程大纲
  def syllabus
    @article = BlogComment.find(@course.outline)
    respond_to do |format|
      format.html {render :layout => 'base_courses'}
    end
  end

  #删除课程
  #删除课程只是将课程的is_deleted状态改为false，is_deleted为false状态的课程只有管理员可以看到
  def destroy
    @course_id = @course.id
    syllabus = @course.syllabus
    @course.delete!
    respond_to do |format|
      format.js
      format.html{redirect_to courses_path()}
    end

    # redirect_to :back
  end

  #归档班级
  def archive_course
    if params[:source] == "1"
      @syllabus = @course.syllabus
    end
    if params[:type] == "0"
      @course.delete!
    else
      @course.update_attribute(:is_delete, false)
    end
    respond_to do |format|
      format.js
    end
  end

  # 恢复已删除的课程
  def renew
    if User.current.admin?
      @course.update_attributes(:is_delete => false)
      redirect_to course_path(@course)
    else
      return 404
    end
  end
  #搜索作业
  def homework_search
    @search = "%#{params[:search].strip.downcase}%"
    @is_teacher = User.current.logged? && (User.current.admin? || User.current.allowed_to?(:as_teacher,@course))
    @page = params[:page] ? params[:page].to_i + 1 : 0
    if @is_teacher
      @homeworks = @course.homework_commons.where("name like '%#{@search}%'").order("created_at desc").limit(10).offset(@page * 10)
    else
      @homeworks = @course.homework_commons.where("name like '%#{@search}%' and publish_time <= '#{Time.now}'").order("created_at desc").limit(10).offset(@page * 10)
    end
  end

  #统计
  def course_statistics
    @left_nav_type = 10
    @order = params[:order] ? params[:order] : 'desc'
    @tab = params[:tab] ? params[:tab].to_i : nil
    if !params[:tab] || params[:tab] == "1"
      @type = params[:type] ? params[:type] : 'act_score'
      sql = "SELECT (message_num*2 + message_reply_num*1 + news_reply_num*1 + news_num*1 + resource_num*5 + journal_num*1 + homework_journal_num*1) AS act_score,
            message_num, message_reply_num, news_reply_num, news_num, resource_num, journal_num, homework_journal_num, ccs.user_id FROM course_contributor_scores ccs
            JOIN students_for_courses ON students_for_courses.course_id = ccs.course_id AND students_for_courses.student_id = ccs.user_id
            WHERE ccs.course_id = #{@course.id} ORDER BY #{@type} #{@order}, act_score #{@order}"
      @members = CourseContributorScore.find_by_sql(sql)

    elsif params[:tab] == "2"
      @type = params[:type] ? params[:type] : 'total_score'
      @members = @course.course_homework_statisticss.reorder("#{@type} #{@order}, total_score #{@order}")

    elsif params[:tab] == "3"
      @homework_id = params[:homework_id] ? params[:homework_id].to_i : 0
      @type = params[:type] ? params[:type] : 'project_act_score'
      if @homework_id == 0
        sql = "SELECT (IFNULL(changeset_num, 0)*4 + issue_num*4 + issue_journal_num*1 + attach_num*5 + board_num*2 + board_message_num*1) AS project_act_score,
            changeset_num, issue_num, issue_journal_num, attach_num, board_num, board_message_num, pss.project_id FROM project_scores pss
            JOIN student_work_projects ON student_work_projects.project_id = pss.project_id AND student_work_projects.course_id = #{@course.id}
            GROUP BY pss.project_id ORDER BY #{@type} #{@order}, project_act_score #{@order}"
      else
        sql = "SELECT (IFNULL(changeset_num, 0)*4 + issue_num*4 + issue_journal_num*1 + attach_num*5 + board_num*2 + board_message_num*1) AS project_act_score,
            changeset_num, issue_num, issue_journal_num, attach_num, board_num, board_message_num, pss.project_id FROM project_scores pss
            JOIN student_work_projects ON student_work_projects.project_id = pss.project_id AND student_work_projects.course_id = #{@course.id} AND student_work_projects.homework_common_id = #{@homework_id}
            GROUP BY pss.project_id ORDER BY #{@type} #{@order}, project_act_score #{@order}"
      end
      @members = ProjectScore.find_by_sql(sql)
    end

    @limit = 20
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @members_count = @members.count
    @mem_pages = Paginator.new @members_count, @limit, @page
    @members = paginateHelper @members, @limit
    respond_to do |format|
      format.js
      format.html {render :layout => 'base_courses'}
    end
  end

  #切换身份
  def switch_role
    members = @course.members.where("user_id = #{params[:user_id]}")
    unless members.blank?
      #role = MemberRole.where("member_id = #{members.first.id} and role_id = #{params[:role]}").first
      curr_role = MemberRole.find_by_member_id_and_role_id(members.first.id, params[:curr_role])
      tar_role = MemberRole.find_by_member_id_and_role_id(members.first.id, params[:tar_role])
      unless (curr_role.nil? || tar_role.nil?)
        curr_role.update_column('is_current', 0)
        tar_role.update_column('is_current', 1)
      end
    end
    redirect_to course_path(@course)
  end

  def search_not_teachers
    members = searchTeacherAndAssistant @course
    member_ids = "(" + members.map(&:user_id).join(',') +  ")"
    condition = "%#{params[:search]}%".gsub(" ","")
    @school = params[:school] || @course.teacher.school_name
    if @school != ""
      school_s = "%#{@school}%".gsub(" ","")
      school_ids = School.where("name like '#{school_s}'").blank? ? "(-1)" : "(" + School.where("name like '#{school_s}'").map(&:id).join(",") + ")"
      @users = User.joins(:user_extensions).where("users.id not in #{member_ids} and status = 1 and LOWER(concat(lastname, firstname)) LIKE '#{condition}' and user_extensions.school_id in #{school_ids}")
    else
      @users = User.where("id not in #{member_ids} and status = 1 and LOWER(concat(lastname, firstname)) LIKE '#{condition}'").includes(:user_extensions)
    end

    @user_ids = @users.map(&:id).join(",")
    @limit = 8
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @users_count = @users.count
    @users_pages = Paginator.new @users_count, @limit, @page
    @users = paginateHelper @users, @limit
    respond_to do |format|
      format.js
    end
  end

  def search_not_students
    members = @course.members.select{|m| m.roles.to_s.include?("Student")}
    member_ids = members.empty? ? "(-1)" : "(" + members.map(&:user_id).join(',') +  ")"
    condition = "%#{params[:search]}%".gsub(" ","")
    @school = params[:school] || @course.teacher.school_name
    if @school != ""
      school_s = "%#{@school}%".gsub(" ","")
      school_ids = School.where("name like '#{school_s}'").blank? ? "(-1)" : "(" + School.where("name like '#{school_s}'").map(&:id).join(",") + ")"
      @users = User.joins(:user_extensions).where("users.id not in #{member_ids} and status = 1 and user_extensions.school_id in #{school_ids} and (LOWER(concat(lastname, firstname)) LIKE '#{condition}' or user_extensions.student_id LIKE '#{condition}')")
    else
      @users = User.joins(:user_extensions).where("users.id not in #{member_ids} and status = 1 and (LOWER(concat(lastname, firstname)) LIKE '#{condition}' or user_extensions.student_id LIKE '#{condition}')")
    end

    @user_ids = @users.map(&:id).join(",")
    @limit = 8
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @users_count = @users.count
    @users_pages = Paginator.new @users_count, @limit, @page
    @users = paginateHelper @users, @limit
    respond_to do |format|
      format.js
    end
  end

  private
  def update_quotes attachment
    if attachment.copy_from
      attachments = Attachment.find_by_sql("select * from attachments where copy_from = #{attachment.copy_from} or id =  #{attachment.copy_from}")
    else
      attachments = Attachment.find_by_sql("select * from attachments where copy_from = #{attachment.id} or id = #{attachment.copy_from}")
    end
    attachment.quotes = get_qute_number attachment
    attachment.save
    attachments.each do |att|
      att.quotes = attachment.quotes
      att.save
    end
  end

  def get_qute_number attachment
    if attachment.copy_from
      result = Attachment.find_by_sql("select count(*) as number from attachments where copy_from = #{attachment.copy_from}")
    else
      result = Attachment.find_by_sql("select count(*) as number from attachments where copy_from = #{attachment.id}")
    end
    if result.nil? || result.count <= 0
      return 0
    else
      return result[0].number
    end
  end

  def allow_join course
    if course_endTime_timeout? course
      respond_to do |format|
        format.js {
          @state = 2
          render :partial => 'set_join',
                 :locals => {:user => User.current,
                             :course => Course.find(params[:object_id]),
                             :object_id => params[:object_id]
                 }
        }
      end
    end
  end

  #验证是否显示课程
  def can_show_course
    @first_page = FirstPage.find_by_page_type('project')
    if @first_page.try(:show_course) == 2
      render_404
    end
  end

  def student_homework_score(groupid,start_from, nums, score_sort_by, sort_type = 'score')
    start_from = start_from * nums
    if groupid == 0
      sql_select = "SELECT members.*,(
          SELECT SUM(student_works.work_score)
          FROM student_works,homework_commons
          WHERE student_works.homework_common_id = homework_commons.id
          AND homework_commons.course_id = #{@course.id}
          AND student_works.user_id = members.user_id
        ) AS score,(SELECT max(message_num*2 + message_reply_num*1 + news_reply_num*1 + news_num*1 +
         resource_num*5 + journal_num*1 + homework_journal_num*1 ) FROM `course_contributor_scores` AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id
        ) AS act_score,(SELECT SUM(exercise_users.score) FROM exercise_users,exercises WHERE exercise_users.exercise_id = exercises.id
        AND exercises.course_id = #{@course.id} AND exercise_users.user_id = members.user_id) AS ex_score,
        (SELECT max(student_id) FROM user_extensions WHERE user_extensions.user_id = members.user_id) AS student_id,
        (SELECT max(message_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS message_num,
        (SELECT max(message_reply_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS message_reply_num,
        (SELECT max(news_reply_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS news_reply_num,
        (SELECT max(news_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS news_num,
        (SELECT max(resource_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS resource_num,
        (SELECT max(journal_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS journal_num,
        (SELECT max(homework_journal_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS homework_journal_num,
        (SELECT COUNT(ss.id) FROM student_works AS ss ,homework_commons AS hc WHERE ss.homework_common_id = hc.id AND hc.course_id = #{@course.id} AND ss.work_status != 0 AND ss.user_id = members.user_id) AS homework_num,
        (SELECT COUNT(eu.id) FROM exercise_users AS eu,exercises WHERE eu.exercise_id = exercises.id AND exercises.course_id = #{@course.id} AND exercises.end_time >= eu.created_at AND eu.user_id = members.user_id) AS exercise_num
        FROM members
        JOIN students_for_courses
        ON students_for_courses.student_id = members.user_id AND students_for_courses.course_id = members.course_id
        WHERE members.course_id = #{@course.id} ORDER BY #{sort_type} #{score_sort_by}"
    elsif groupid == -1
      sql_select = "SELECT members.*,(
          SELECT SUM(student_works.work_score)
          FROM student_works,homework_commons
          WHERE student_works.homework_common_id = homework_commons.id
          AND homework_commons.course_id = #{@course.id}
          AND student_works.user_id = members.user_id
        ) AS score,(SELECT max(message_num*2 + message_reply_num*1 + news_reply_num*1 + news_num*1 +
         resource_num*5 + journal_num*1 + homework_journal_num*1 ) FROM `course_contributor_scores` AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id
        ) AS act_score,(SELECT SUM(exercise_users.score) FROM exercise_users,exercises WHERE exercise_users.exercise_id = exercises.id
        AND exercises.course_id = #{@course.id} AND exercise_users.user_id = members.user_id) AS ex_score,
        (SELECT max(student_id) FROM user_extensions WHERE user_extensions.user_id = members.user_id) AS student_id,
        (SELECT max(message_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS message_num,
        (SELECT max(message_reply_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS message_reply_num,
        (SELECT max(news_reply_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS news_reply_num,
        (SELECT max(news_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS news_num,
        (SELECT max(resource_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS resource_num,
        (SELECT max(journal_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS journal_num,
        (SELECT max(homework_journal_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS homework_journal_num,
        (SELECT COUNT(ss.id) FROM student_works AS ss ,homework_commons AS hc WHERE ss.homework_common_id = hc.id AND hc.course_id = #{@course.id} AND ss.work_status != 0 AND ss.user_id = members.user_id) AS homework_num,
        (SELECT COUNT(eu.id) FROM exercise_users AS eu,exercises WHERE eu.exercise_id = exercises.id AND exercises.course_id = #{@course.id} AND exercises.end_time >= eu.created_at AND eu.user_id = members.user_id) AS exercise_num
        FROM members
        JOIN students_for_courses
        ON students_for_courses.student_id = members.user_id AND students_for_courses.course_id = members.course_id
        WHERE members.course_id = #{@course.id} AND members.course_group_id = 0 ORDER BY #{sort_type} #{score_sort_by}"
    else
      sql_select = "SELECT members.*,(
          SELECT SUM(student_works.work_score)
          FROM student_works,homework_commons
          WHERE student_works.homework_common_id = homework_commons.id
          AND homework_commons.course_id = #{@course.id}
          AND student_works.user_id = members.user_id
        ) AS score,(SELECT max(message_num*2 + message_reply_num*1 + news_reply_num*1 + news_num*1 +
         resource_num*5 + journal_num*1 + homework_journal_num*1 ) FROM `course_contributor_scores` AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id
        ) AS act_score,(SELECT SUM(exercise_users.score) FROM exercise_users,exercises WHERE exercise_users.exercise_id = exercises.id
        AND exercises.course_id = #{@course.id} AND exercise_users.user_id = members.user_id) AS ex_score,
        (SELECT max(student_id) FROM user_extensions WHERE user_extensions.user_id = members.user_id) AS student_id,
        (SELECT max(message_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS message_num,
        (SELECT max(message_reply_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS message_reply_num,
        (SELECT max(news_reply_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS news_reply_num,
        (SELECT max(news_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS news_num,
        (SELECT max(resource_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS resource_num,
        (SELECT max(journal_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS journal_num,
        (SELECT max(homework_journal_num) FROM course_contributor_scores AS ccs WHERE ccs.course_id = #{@course.id} AND ccs.user_id = members.user_id) AS homework_journal_num,
        (SELECT COUNT(ss.id) FROM student_works AS ss ,homework_commons AS hc WHERE ss.homework_common_id = hc.id AND hc.course_id = #{@course.id} AND ss.work_status != 0 AND ss.user_id = members.user_id) AS homework_num,
        (SELECT COUNT(eu.id) FROM exercise_users AS eu,exercises WHERE eu.exercise_id = exercises.id AND exercises.course_id = #{@course.id} AND exercises.end_time >= eu.created_at AND eu.user_id = members.user_id) AS exercise_num
        FROM members
        JOIN students_for_courses
        ON students_for_courses.student_id = members.user_id AND students_for_courses.course_id = members.course_id
        WHERE members.course_id = #{@course.id} AND members.course_group_id = #{groupid} ORDER BY #{sort_type} #{score_sort_by}"
    end
    sql = ActiveRecord::Base.connection()
    homework_scores = Member.find_by_sql(sql_select)
    sql.close()

    homework_scores
  end
  #获取课程的老师列表
  def find_course_teachers course
    searchTeacherAndAssistant(course).map{|teacher| teacher.user_id}.join(",")
  end

  #当加入，退出分班时查询分班的学生
  def search_group_members group
    @render_file = 'new_member_list'
    @canShowCode = isCourseTeacher(User.current.id,@course)
    @is_remote = true
    @score_sort_by = "desc"
    @results = student_homework_score(group.id,0,0, "desc")
    @limit = 50
    @page = params[:page].nil? ? 1 : params['page'].to_i
    @members_count = @results.count
    @mem_pages = Paginator.new @members_count, @limit, @page
    @results = paginateHelper @results, @limit
  end

  def shixun_xls homeworks, course
    xls_report = StringIO.new
    book = Spreadsheet::Workbook.new
    blue = Spreadsheet::Format.new :color => :blue, :weight => :bold, :size => 10
    black = Spreadsheet::Format.new :color => :black, :weight => :bold, :size => 10
    current_row = 0
    sheet = book.create_worksheet :name => "学员统计"
    sheet.row(0).default_format = blue
    sheet.row(0).concat(["学生id","学生姓名","作业编号","作业/实训名称","关卡","	经验值","金币","评测历史","评测时间","评测结果"])
    course.student.each_with_index do |course, i|
      # 总统计
      count_exp_score = 0
      count_gold_score = 0
      count_test = 0
      count_time = 0
      game_count = 0
      # end
      user = course.student
      current_row += 1
      sheet[current_row, 0] = user.login
      sheet[current_row, 1] = user.show_real_name
      homeworks.each_with_index do |homework, j|
        # 关卡统计
        count_game_exp_score = 0
        count_game_gold_score = 0
        count_game_test = 0
        count_game_time = 0
        # end
        sheet[current_row, 2] = "# #{j + 1}"
        sheet[current_row, 3] = homework.name
        shixun = homework.homework_commons_shixuns.shixun if homework.homework_commons_shixuns
        if shixun
          shixun.challenges.each_with_index do |challenge, k|
            sheet[current_row, 4] = "第#{k+1}关"
            games = Game.where(:challenge_id => challenge.id, :user_id => user.id)
            game_count = game_count + games.count
            if games.count > 0
              games.each do |game|
                sheet[current_row, 5] = game.final_score ? "+#{game.final_score.to_s}" : "--"
                sheet[current_row, 6] =  game.final_score ? (game.answer_open? ? "#{-challenge.score.to_i}" : "+#{game.final_score.to_i}") : "--"
                count_exp_score += (game.final_score ? game.final_score.to_i : 0)
                count_gold_score += (game.final_score ? (game.answer_open? ? -challenge.score.to_i : game.final_score.to_i) : 0)
                count_game_exp_score += (game.final_score ? game.final_score.to_i : 0)
                count_game_gold_score += (game.final_score ? (game.answer_open? ? -challenge.score.to_i : game.final_score.to_i) : 0)
                count = game.outputs.count
                if count > 0
                  outputs = game.outputs.where(:test_set_position => 1).reorder("created_at asc")
                  outputs.each_with_index do |output, k|
                    count_test += 1
                    count_game_test += 1
                    sheet[current_row, 7] = "第#{k+1}次评测"
                    sheet[current_row, 8] = k == 0 ? game_spend_time((output.created_at - game.created_at).to_i) : game_spend_time((output.created_at - outputs[k-1].created_at).to_i)
                    count_time = count_time + (k == 0 ? (output.created_at - game.created_at).to_i : (output.created_at - outputs[k-1].created_at).to_i)
                    count_game_time += (k == 0 ? (output.created_at - game.created_at).to_i : (output.created_at - outputs[k-1].created_at).to_i)
                    sheet[current_row, 9] = game.outputs.where(:query_index => output.query_index).select{|ou| ou.result == false}.count > 0 ? "失败" : "成功"
                    current_row += 1
                  end
                else
                  current_row += 1
                end
              end
            else
              current_row += 1
            end
          end
        end
        sheet[current_row,4] = "合计"
        sheet[current_row,5] = count_game_exp_score >= 0 ? "+#{count_game_exp_score}" : "-#{count_game_exp_score}"
        sheet[current_row,6] = count_game_gold_score >= 0 ? "+#{count_game_gold_score}" : "#{count_game_gold_score}"
        sheet[current_row,7] = "共#{count_game_test}次评测"
        sheet[current_row,8] = game_spend_time((count_game_time == 0 ? 0 : (count_game_time / count_test)).to_i)+"/次"
        sheet.row(current_row).default_format = black
        current_row += 1
      end
      sheet[current_row,2] = "合计"
      sheet[current_row,4] = "共#{game_count}"
      sheet[current_row,5] = count_exp_score >= 0 ? "+#{count_exp_score}" : "-#{count_exp_score}"
      sheet[current_row,6] = count_gold_score >= 0 ? "+#{count_gold_score}" : "#{count_gold_score}"
      sheet[current_row,7] = "共#{count_test}次评测"
      sheet[current_row,8] = game_spend_time((count_time == 0 ? 0 : (count_time / count_test)).to_i)+"/次"
      sheet.row(current_row).default_format = black
    end

    homeworks.each_with_index do |homework, i|
      sheet = book.create_worksheet :name => "第#{i+1}次作业"
      sheet.row(0).default_format = blue
      sheet[0,0] = "课堂编号"
      sheet[0,1] = "主讲老师"
      sheet[0,2] = "课堂名称"
      sheet[0,3] = "作业编号"
      sheet[0,4] = "作业/实训名称"
      sheet[0,5] = "关卡"
      sheet[0,6] = "学生id"
      sheet[0,7] = "学生姓名"
      sheet[0,8] = "经验值"
      sheet[0,9] = "金币"
      sheet[0,10] = "评测历史"
      sheet[0,11] = "评测时间"
      sheet[0,12] = "评测结果"
      sheet[1,0] = course.id
      sheet[1,1] = course.teacher.show_real_name
      sheet[1,2] = course.name
      sheet[1,3] = "# #{i + 1}"
      sheet[1,4] = homework.name
      current_row = 1
      shixun = homework.homework_commons_shixuns.shixun if homework.homework_commons_shixuns
      if shixun
        shixun.challenges.each_with_index do |challenge, j|
          sheet[current_row,5] = "第#{j+1}关"
          games = Game.where(:challenge_id => challenge.id, :user_id => homework.student_works.where("work_status != 0").map(&:user_id))
          count_exp_score = 0
          count_gold_score = 0
          count_test = 0
          count_time = 0
          if games.count > 0
            games.each do |game|
              user = game.user
              sheet[current_row,6] = user.login
              sheet[current_row,7] = user.show_real_name
              sheet[current_row,8] = game.final_score ? "+#{game.final_score.to_s}" : "--"
              count_exp_score = count_exp_score + (game.final_score ? game.final_score.to_i : 0)
              sheet[current_row,9] = game.final_score ? (game.answer_open? ? "#{-challenge.score.to_i}" : "+#{game.final_score.to_i}") : "--"
              count_gold_score = count_gold_score + (game.final_score ? (game.answer_open? ? -challenge.score.to_i : game.final_score.to_i) : 0)

              count = game.outputs.count
              if count > 0
                outputs = game.outputs.where(:test_set_position => 1).reorder("created_at asc")
                outputs.each_with_index do |output, k|
                  count_test = count_test + 1
                  sheet[current_row,10] = "第#{k+1}次评测"
                  sheet[current_row,11] = k == 0 ? game_spend_time((output.created_at - game.created_at).to_i) : game_spend_time((output.created_at - outputs[k-1].created_at).to_i)
                  count_time = count_time + (k == 0 ? (output.created_at - game.created_at).to_i : (output.created_at - outputs[k-1].created_at).to_i)
                  sheet[current_row,12] = game.outputs.where(:query_index => output.query_index).select{|ou| ou.result == false}.count > 0 ? "失败" : "成功"
                  current_row = current_row + 1
                end
              else
                current_row = current_row + 1
              end
            end
          else
            current_row = current_row + 1
          end

          sheet[current_row,6] = "合计"
          sheet[current_row,7] = games.count.to_s + "人"
          sheet[current_row,8] = count_exp_score >= 0 ? "+#{count_exp_score}" : "-#{count_exp_score}"
          sheet[current_row,9] = count_gold_score >= 0 ? "+#{count_gold_score}" : "#{count_gold_score}"
          sheet[current_row,10] = "共#{count_test}次评测"
          sheet[current_row,11] = game_spend_time((count_time == 0 ? 0 : (count_time / count_test)).to_i)+"/次"
          sheet.row(current_row).default_format = black
          current_row = current_row + 1
        end
      end
    end
    book.write xls_report
    xls_report.string
  end

  def member_to_xls homeworks, course, members,groups
    xls_report = StringIO.new
    book = Spreadsheet::Workbook.new
    sheet1 = book.create_worksheet :name => "总成绩"
    blue = Spreadsheet::Format.new :color => :blue, :weight => :bold, :size => 10
    #sheet1.row(0).default_format = blue
    #sheet1.row(0).concat([l(:excel_user_id),l(:excel_user_name),l(:excel_nickname),l(:excel_student_id),l(:excel_mail),l(:excel_class),l(:excel_f_score),l(:excel_commit_time)])
    sheet1[0,0] = "课程编号"
    sheet1[0,1] = course.course_list_id
    sheet1[1,0] = "课程名称"
    sheet1[1,1] = course.course_list_name
    sheet1[2,0] = "班级编号"
    sheet1[2,1] = course.id
    sheet1[3,0] = "班级名称"
    sheet1[3,1] = course.name
    sheet1[4,0] = "教师团队"
    sheet1[4,1] = (searchTeacherAndAssistant course).map{|member| member.user.show_name}.join('、')
    sheet1[5,0] = "主讲教师"
    sheet1[5,1] = course.teacher.show_name
    sheet1[6,0] = "排名"
    sheet1[6,1] = "学生姓名"
    sheet1[6,2] = "昵称"
    sheet1[6,3] = "学号"
    for i in 0 ... homeworks.count
      sheet1[6,i+4] = "第"+(i+1).to_s+"次"
    end
    sheet1[6,homeworks.count+4] = "作业得分"
    sheet1[6,homeworks.count+5] = "测评得分"
    sheet1[6,homeworks.count+6] = "社区得分"
    sheet1[6,homeworks.count+7] = "总得分"
    sheet1[6,homeworks.count+8] = "分班"
    count_row = 7
    members.each_with_index do |member, i|
      sheet1[count_row,0]= i+1
      sheet1[count_row,1] = member.user.lastname.to_s + member.user.firstname.to_s
      sheet1[count_row,2] = member.user.login
      sheet1[count_row,3] = member.user.user_extensions.student_id
      homeworks.each_with_index do |homework, j|
        student_works = homework.student_works.where("user_id = #{member.user.id}")
        if student_works.empty?
          sheet1[count_row,j+4] = 0
        else
          work_score = student_works.first.work_score.nil? ? 0 : student_works.first.work_score
          sheet1[count_row,j+4] = work_score <0 ? 0:work_score.round(2)
        end
      end
      hw_score = member.score.nil? ? 0 : member.score
      ex_score = member.ex_score.nil? ? 0 : member.ex_score
      act_score = member.act_score.nil? ? 0 : member.act_score
      sum = hw_score + ex_score + act_score
      sheet1[count_row,homeworks.count+4] = hw_score.round(2)
      sheet1[count_row,homeworks.count+5] = ex_score
      sheet1[count_row,homeworks.count+6] = act_score
      sheet1[count_row,homeworks.count+7] = sum.round(2)
      sheet1[count_row,homeworks.count+8] = member.course_group_id == 0 ? "暂无" : member.course_group.name
      count_row += 1
    end

    homeworks.each_with_index do |home, i|
      sheet = book.create_worksheet :name => "第#{i+1}次作业"
      sheet[0,0] = "课程编号"
      sheet[0,1] = course.course_list_id
      sheet[1,0] = "课程名称"
      sheet[1,1] = course.course_list_name
      sheet[2,0] = "班级编号"
      sheet[2,1] = course.id
      sheet[3,0] = "班级名称"
      sheet[3,1] = course.name
      sheet[4,0] = "教师团队"
      sheet[4,1] = (searchTeacherAndAssistant course).map{|member| member.user.show_name}.join('、')
      sheet[5,0] = "主讲教师"
      sheet[5,1] = course.teacher.show_name
      sheet[5,0] = "作业批次"
      sheet[5,1] = "第#{i+1}次作业"
      sheet[5,0] = "作业名称"
      sheet[5,1] = home.name
      if home.homework_type == 1 #普通作业
        if home.anonymous_comment ==0
          sheet.row(6).concat([l(:excel_rank),l(:excel_user_name),l(:excel_nickname),l(:excel_student_id),l(:excel_homework_name),l(:excel_homework_des),
                                l(:excel_t_score),l(:excel_ta_score), l(:excel_n_score),l(:excel_a_penalty),l(:excel_l_penalty),l(:excel_f_score),l(:excel_commit_time)])
        else
          sheet.row(6).concat([l(:excel_rank),l(:excel_user_name),l(:excel_nickname),l(:excel_student_id),l(:excel_homework_name),l(:excel_homework_des),
                                l(:excel_t_score),l(:excel_ta_score),l(:excel_l_penalty),l(:excel_f_score),l(:excel_commit_time)])
        end
        count_row = 7
        items = home.student_works.order("work_score desc")
        items.each_with_index do |stu, j|
          sheet[count_row,0]= j + 1
          sheet[count_row,1] = stu.user.show_name
          sheet[count_row,2] = stu.user.login
          sheet[count_row,3] = stu.user.user_extensions.student_id
          sheet[count_row,4] = stu.name
          sheet[count_row,5] = strip_html stu.description
          sheet[count_row,6] = stu.teacher_score.nil? ? l(:label_without_score) : stu.teacher_score.round(2)
          sheet[count_row,7] = stu.teaching_asistant_score.nil? ? l(:label_without_score) : stu.teaching_asistant_score.round(2)
          if home.anonymous_comment ==0
            sheet[count_row,8] = stu.student_score.nil? ? l(:label_without_score) : stu.student_score.round(2)
            sheet[count_row,9] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.absence_penalty
            sheet[count_row,10] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.late_penalty
            sheet[count_row,11] = stu.work_score.nil? ? l(:label_without_score) : stu.work_score.round(2)
            sheet[count_row,12] = format_time(stu.created_at)
          else
            sheet[count_row,8] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.late_penalty
            sheet[count_row,9] = stu.work_score.nil? ? l(:label_without_score) : stu.work_score.round(2)
            sheet[count_row,10] = format_time(stu.created_at)
          end
          count_row += 1
        end
      elsif home.homework_type == 2 #编程作业
        if home.anonymous_comment ==0
          sheet.row(6).concat([l(:excel_rank),l(:excel_user_name),l(:excel_nickname),l(:excel_student_id),l(:excel_homework_name),l(:excel_homework_des),
                                l(:excel_t_score),l(:excel_ta_score), l(:excel_s_score),l(:excel_n_score),l(:excel_a_penalty),l(:excel_l_penalty),l(:excel_f_score),l(:excel_commit_time)])
        else
          sheet.row(6).concat([l(:excel_rank),l(:excel_user_name),l(:excel_nickname),l(:excel_student_id),l(:excel_homework_name),l(:excel_homework_des),
                                l(:excel_t_score),l(:excel_ta_score), l(:excel_s_score),l(:excel_l_penalty),l(:excel_f_score),l(:excel_commit_time)])
        end
        count_row = 7
        items = home.student_works.order("work_score desc")
        items.each_with_index do |stu, j|
          sheet[count_row,0]= j + 1
          sheet[count_row,1] = stu.user.show_name
          sheet[count_row,2] = stu.user.login
          sheet[count_row,3] = stu.user.user_extensions.student_id
          sheet[count_row,4] = stu.name
          sheet[count_row,5] = stu.description
          sheet[count_row,6] = stu.teacher_score.nil? ? l(:label_without_score) : stu.teacher_score.round(2)
          sheet[count_row,7] = stu.teaching_asistant_score.nil? ? l(:label_without_score) : stu.teaching_asistant_score.round(2)
          sheet[count_row,8] = stu.system_score.nil? ? l(:label_without_score) : stu.system_score.round(2)
          if home.anonymous_comment ==0
            sheet[count_row,9] = stu.student_score.nil? ? l(:label_without_score) : stu.student_score.round(2)
            sheet[count_row,10] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.absence_penalty
            sheet[count_row,11] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.late_penalty
            sheet[count_row,12] = stu.work_score.nil? ? l(:label_without_score) : stu.work_score.round(2)
            sheet[count_row,13] = format_time(stu.created_at)
          else
            sheet[count_row,9] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.late_penalty
            sheet[count_row,10] = stu.work_score.nil? ? l(:label_without_score) : stu.work_score.round(2)
            sheet[count_row,11] = format_time(stu.created_at)
          end
          count_row += 1
        end
      elsif home.homework_type == 3 #分组作业
        if home.anonymous_comment ==0
          sheet.row(6).concat([l(:excel_rank),l(:excel_group_member),l(:excel_homework_name),l(:excel_homework_project),l(:excel_homework_des),
                                l(:excel_t_score),l(:excel_ta_score),l(:excel_n_score),l(:excel_a_penalty),l(:excel_l_penalty),l(:excel_f_score),l(:excel_commit_time)])
        else
          sheet.row(6).concat([l(:excel_rank),l(:excel_group_member),l(:excel_homework_name),l(:excel_homework_project),l(:excel_homework_des),
                                l(:excel_t_score),l(:excel_ta_score),l(:excel_l_penalty),l(:excel_f_score),l(:excel_commit_time)])
        end
        count_row = 7
        items = home.student_works.order("work_score desc")
        items.each_with_index do |stu, j|
          sheet[count_row,0] = j + 1
          sheet[count_row,1] = get_group_member_names stu
          sheet[count_row,2] = stu.name
          sheet[count_row,3] = (stu.project_id == 0 || stu.project_id.nil?) ? l(:excel_no_project) : stu.project.name
          sheet[count_row,4] = strip_html stu.description
          sheet[count_row,5] = stu.teacher_score.nil? ? l(:label_without_score) : stu.teacher_score.round(2)
          sheet[count_row,6] = stu.teaching_asistant_score.nil? ? l(:label_without_score) : stu.teaching_asistant_score.round(2)
          if home.anonymous_comment ==0
            sheet[count_row,7] = stu.student_score.nil? ? l(:label_without_score) : stu.student_score.round(2)
            sheet[count_row,8] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.absence_penalty
            sheet[count_row,9] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.late_penalty
            sheet[count_row,10] = stu.work_score.nil? ? l(:label_without_score) : stu.work_score.round(2)
            sheet[count_row,11] = format_time(stu.created_at)
          else
            sheet[count_row,7] = (home.teacher_priority == 1 && !stu.teacher_score.nil?) ? 0 : stu.late_penalty
            sheet[count_row,8] = stu.work_score.nil? ? l(:label_without_score) : stu.work_score.round(2)
            sheet[count_row,9] = format_time(stu.created_at)
          end
          count_row += 1
        end
      end
    end

=begin
    group0 = CourseGroup.new();
    group0.id = 0;
    group0.name = l(:excel_member_with_out_class)
    groups_dup = groups.dup
    groups_dup << group0
    groups_dup.each do |group|
      sheet1[count_row,0] = l(:excel_class)
      sheet1[count_row,1] = group.name
      count_row += 1
      members.each do |member|
        if member.course_group_id == group.id
          sheet1[count_row,0]= member.user.id
          sheet1[count_row,1] = member.user.lastname.to_s + member.user.firstname.to_s
          sheet1[count_row,2] = member.user.login
          sheet1[count_row,3] = member.user.user_extensions.student_id
          sheet1[count_row,4] = member.user.mail
          sheet1[count_row,5] = format("%0.2f",member.score.nil? ? 0:member.score.to_s)
          count_row += 1
        end
      end
    end
=end
    book.write xls_report
    xls_report.string
  end

end