require_dependency "educode_sales/application_controller"

module EducodeSales
  class PlansController < ApplicationController
    protect_from_forgery
    def index
      # authorize! :read, SalePlan
      respond_to do |format|
        format.html do
          common = Common.find_by(clazz: 'staff_type', name: '销售')
          @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }
          @years = (1..(Time.now.year - 2020)).map { |d| 2020 + d }
          @months = (1..12).map { |d| d }
        end
        format.json do
          if @current_admin.is_admin?
            @sale_plans = SalePlan
          else
            level = @current_admin.role.role_areas.find_by(clazz: '销售计划').level
            case level
            when '自己'
              @sale_plans = SalePlan.where(staff_id: @current_admin.id)
            when '区域'
              staff_ids = Staff.joins(user: [user_extension: [department: :school]]).where("schools.province in (?)", @current_admin.areas.pluck(:name)).pluck(:id)

              school_ids = StaffSchool.where(staff_id: @current_admin.id).pluck(:school_id)
              if @current_admin.staff_school_tags.present?
                # 如果设置专项客户类型，则视为专项经理，根据专项客户查看对应商机
                school_ids += School.joins(:school_tags).where("school_tags.id in (?)", @current_admin.staff_school_tags.pluck(:school_tag_id)).pluck(:id)
              end

              business_ids = Business.where(school_id: school_ids.uniq).pluck(:id)
              @sale_plans = SalePlan.where("staff_id in (?) OR educode_sales_sale_plans.staff_id = ? OR educode_sales_sale_plans.business_id in (?)", staff_ids, @current_admin.id,business_ids)
            else
              @sale_plans = SalePlan
            end
          end
          if params[:clazz] == 'week'
            @sale_plans = @sale_plans.where.not(weekly: nil).where.not(month: nil)
          elsif params[:clazz] == 'month'
            @sale_plans = @sale_plans.where(weekly: nil).where.not(month: nil)
          end
          if params[:q].present? && params[:q][:staff_id].present?
            @sale_plans = @sale_plans.where(staff_id: params[:q][:staff_id])
          end
          # p "#{params[:q][:year]}-01-01 00:00:00".to_date, "#{params[:q][:year]}-12-31 23:59:00".to_date
          if params[:q].present? && params[:q][:year].present?
            @sale_plans = @sale_plans.where("month >= ? and month <= ?", "#{params[:q][:year]}-01-01 00:00:00".to_date, "#{params[:q][:year]}-12-31 23:59:00".to_date)
          end
          if params[:q].present? && params[:q][:month].present?
            @sale_plans = @sale_plans.where("month = ?", "#{params[:q][:month]}-01 00:00:00".to_date)
          end
          if params[:sort].present? && params[:sort][:field]
            @sale_plans = @sale_plans.order("#{params[:sort][:field]} #{params[:sort][:order]}")
          else
            @sale_plans = @sale_plans.order("created_at desc")
          end

          @sale_plans = @sale_plans.page(params[:page]).per(params[:limit])
        end
      end
    end

    def new
      render layout: false
    end

    def create
      if params["year"].present?
        business = Business.find_by(id: params[:business_id])
        plan = @current_admin.sale_plans.build(year: params[:year], business_id: business.id, clazz: params[:clazz], common_id: params[:common_id] )
        if params[:assign_id].present?
          plan.staff_id = params[:assign_id]
          plan.assign_id = @current_admin.id
        end
        if plan.save
          render_success
        else
          render_failure plan
        end
      else
        plan = @current_admin.sale_plans.build(plan_params)
        plan.month = params["month"] + "-1"
        plan.business = Business.find_by(id: params[:business_id])

        if params[:assign_id].present?
          plan.staff_id = params[:assign_id]
          plan.assign_id = @current_admin.id
        end
        if plan.save
          render_success
        else
          render_failure plan
        end
      end

    end

    def create_business_info
      business = Business.find_by(id: params[:business_id])
      info = @current_admin.business_infos.find_or_initialize_by(year: params[:year], business_id: business.id, clazz: params[:clazz])
      if info.save
        render_success
      else
        render_failure info
      end
    end


    def new_week
      @business = Business.find_by(id: params[:business_id])
      common = Common.find_by(clazz: 'staff_type', name: '销售')
      @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }


      @week = Time.now.strftime('%W').to_i
      week = Time.now.strftime('%W').to_i
      if week > 3
        @weeks = [[week+2,week+2], [week+1,week+1], [week.to_s + '  本周',week], [week - 1,week - 1], [week - 2,week - 2]]
      elsif week == 2
        @weeks = [[week.to_s + '  本周',week], [week - 1,week - 1]]
      else
        @weeks = [[week.to_s + '  本周',week]]
      end
      @commons = Common.where(clazz: '计划类型').pluck(:name, :id)
      business_ids = EducodeSales::AssignFollowUp.joins(:follow_up).where(staff_id: @current_admin.id).pluck :business_id
      @businesses = Business.includes(:department, :last_follow_up).where("educode_sales_businesses.id in (?) or educode_sales_businesses.staff_id = ?", business_ids, @current_admin.id).order(created_at: :desc)
      @businesses = @businesses.map { |b| [b.name + "(#{b.department&.school&.name} #{b.department&.name})" + "---" + "最新跟进:" + (b.last_follow_up.present? ? b.last_follow_up.created_at.to_s : b.created_at.to_s), b.id] }
      render layout: false
    end

    def new_weekly
      d = @current_admin
      area_ids = EducodeSales::Common.where(clazz: 'area').ids.sort.to_s
      if d.areas.present?
        if d.areas.ids.sort.to_s == area_ids
          @area = '全国'
        else
          @area =  d.areas.pluck(:name).join("、")
        end
      else
        @area =  ''
      end
      @name = @current_admin.user.real_name
      sale_plans = SalePlan.where(month: Time.now.strftime("%Y-%m") + "-01 00:00:00", weekly: Time.now.strftime('%W').to_i, staff_id: @current_admin.id)
      @rate = sale_plans.present? ? (sale_plans.sum("finish_rate") / sale_plans.size) : 0
      @departments_count = sale_plans.joins(:business).count("distinct(department_id)")
      @total_amount = sale_plans.joins(business: :last_follow_up).distinct.sum("total_amount")
      render layout: false
    end

    def new_month
      @commons = Common.where(clazz: '计划类型').pluck(:name, :id)
      common = Common.find_by(clazz: 'staff_type', name: '销售')
      @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }

      @business = Business.find_by(id: params[:business_id])
      business_ids = EducodeSales::AssignFollowUp.joins(:follow_up).where(staff_id: @current_admin.id).pluck :business_id
      @businesses =Business.includes(:department, :last_follow_up).where("educode_sales_businesses.id in (?) or educode_sales_businesses.staff_id = ?", business_ids, @current_admin.id).order(created_at: :desc)
      @businesses = @businesses.map { |b| [b.name + "(#{b.department&.school&.name} #{b.department&.name})" + "---" + "最新跟进:" + (b.last_follow_up.present? ? b.last_follow_up.created_at.to_s : b.created_at.to_s), b.id] }
      render layout: false
    end

    def new_year
      @business = Business.find_by(id: params[:business_id])
      business_ids = EducodeSales::AssignFollowUp.joins(:follow_up).where(staff_id: @current_admin.id).pluck :business_id
      @businesses =Business.includes(:department, :last_follow_up).where("educode_sales_businesses.id in (?) or educode_sales_businesses.staff_id = ?", business_ids, @current_admin.id).order(created_at: :desc)
      @businesses = @businesses.map { |b| [ b.name + "(#{b.department&.school&.name} #{b.department&.name})" + "---" + "最新跟进:" + (b.last_follow_up.present? ? b.last_follow_up.created_at.to_s : b.created_at.to_s), b.id] }
      @clazz = EducodeSales::SalePlan::CLAZZ_NAME
      render layout: false
    end

    # 暂时不用
    def staff_businesses
      if params[:q].present?
        business_ids = EducodeSales::AssignFollowUp.joins(:follow_up).where(staff_id: @current_admin.id).pluck :business_id
        @businesses = Business.includes(:department, :last_follow_up).where("educode_sales_businesses.id in (?) or educode_sales_businesses.staff_id = ?", business_ids, @current_admin.id)
        if params[:q].present?
          @businesses =  @businesses.where("educode_sales_businesses.name like ?", "%#{params[:q]}%")
        else
          @businesses =  @businesses.limit(10)
        end
        @businesses =  @businesses.order(created_at: :desc)

        @businesses = @businesses.map { |b| {name: b.name + "(#{b.department&.school&.name} #{b.department&.name})" + "---" + "最新跟进:" + (b.last_follow_up.present? ? b.last_follow_up.created_at.to_s : b.created_at.to_s), value: b.id} }
        render json: { data: @businesses, code: 0, msg: "success" }
      end
    end

    def new_monthly
      d = @current_admin
      area_ids = EducodeSales::Common.where(clazz: 'area').ids.sort.to_s
      if d.areas.present?
        if d.areas.ids.sort.to_s == area_ids
          @area = '全国'
        else
          @area =  d.areas.pluck(:name).join("、")
        end
      else
        @area =  ''
      end
      @name = @current_admin.user.real_name
      sale_plans = SalePlan.where(month: Time.now.strftime("%Y-%m") + "-01 00:00:00", staff_id: @current_admin.id, weekly: nil)
      @rate = sale_plans.present? ? (sale_plans.sum("finish_rate") / sale_plans.size) : 0
      @departments_count = sale_plans.joins(:business).count("distinct(department_id)")
      @total_amount = sale_plans.joins(business: :last_follow_up).distinct.sum("total_amount")
      render layout: false
    end

    def show_week
      @sale_plan = SalePlan.find(params[:id])
      render layout: false
    end

    def show_weekly
      render layout: false
    end

    def show_month
      @sale_plan = SalePlan.find(params[:id])
      render layout: false
    end

    def show_monthly
      render layout: false
    end

    def edit_week
      @commons = Common.where(clazz: '计划类型').pluck(:name, :id)
      business_ids = EducodeSales::AssignFollowUp.joins(:follow_up).where(staff_id: @current_admin.id).pluck :business_id
      @sale_plan = SalePlan.find(params[:id])
      # 加入指派的商机id
      business_ids << @sale_plan.business_id
      @businesses = Business.includes(:department, :last_follow_up).where("educode_sales_businesses.id in (?) or educode_sales_businesses.staff_id = ?", business_ids, @current_admin.id).order(created_at: :desc)
      @businesses = @businesses.map { |b| [b.name + "(" + b.department&.school&.name.to_s + ")" + "---" + "最新跟进:" + (b.last_follow_up.present? ? b.last_follow_up.created_at.to_s : b.created_at.to_s), b.id] }

      if @sale_plan.weekly.present?
        week = Time.now.strftime('%W').to_i
        if week > 3
          @weeks = [week+2, week+1, week, week - 1, week - 2]
        elsif week == 2
          @weeks = [week, week - 1]
        else
          @weeks = [week]
        end
        @weeks << @sale_plan.weekly
        @weeks = @weeks.uniq.sort
        @weeks = @weeks.map do |d|
          if d == week
            ["#{d}  本周", d]
          else
            [d, d]
          end
        end
      end
      @months = [Time.now.strftime("%Y-%m"), Time.now.months_ago(1).strftime("%Y-%m")]
      @months << @sale_plan.month.strftime("%Y-%m")
      @months = @months.uniq.sort
      @finish_rates = []
      11.times { |d| @finish_rates << ["#{d * 10}%", d * 10] }
      render layout: false
    end

    def update
      sale_plan = SalePlan.find(params[:id])
      params["month"] = params["month"] + "-1"
      if sale_plan.update(plan_params)
        render_success
      else
        render_failure sale_plan
      end
    end

    def destroy
      if params[:year].present?
        # 删除年度计划
        sale_plans = SalePlan.where(year: params[:year], clazz: EducodeSales::SalePlan::CLAZZ_NAME[params[:clazz]])
        sale_plans.each do |d|
          EducodeSales::Recycle.create(source: d, deleter_id: @current_admin.id)
        end
        sale_plans.update_all(deleted_at: Time.now)
        sale_plans.business_infos.delete_all
        render_success
      else
        sale_plan = SalePlan.find(params[:id])
        if sale_plan.soft_destroy(@current_admin.id)
          render_success
        else
          render_failure sale_plan
        end
      end
    end

    def edit_weekly
      render layout: false
    end

    def edit_month
      @commons = Common.where(clazz: '计划类型').pluck(:name, :id)
      business_ids = EducodeSales::AssignFollowUp.joins(:follow_up).where(staff_id: @current_admin.id).pluck :business_id
      @businesses = Business.includes(:department, :last_follow_up).where("educode_sales_businesses.id in (?) or educode_sales_businesses.staff_id = ?", business_ids, @current_admin.id).order(created_at: :desc)
      @businesses = @businesses.map { |b| [b.name + "(" + b.department&.school&.name.to_s + ")" + "---" + "最新跟进:" + (b.last_follow_up.present? ? b.last_follow_up.created_at.to_s : b.created_at.to_s), b.id] }
      @sale_plan = SalePlan.find(params[:id])
      @finish_rates = []
      11.times { |d| @finish_rates << ["#{d*10}%", d * 10]}
      render layout: false
    end

    def edit_monthly
      render layout: false
    end

    def my_week
      @sale_plans = SalePlan.where(staff_id: @current_admin.id, month: Time.now.strftime("%Y-%m") + "-01 00:00:00", weekly: Time.now.strftime('%W').to_i)
      if  params[:sort].present? && params[:sort][:field]
        @sale_plans = @sale_plans.order("#{params[:sort][:field]} #{params[:sort][:order]}")
      else
        @sale_plans = @sale_plans.order("updated_at desc")
      end
      @sale_plans =  @sale_plans.page(params[:page]).per(params[:limit])
    end

    def my_month
      @sale_plans = SalePlan.where(staff_id: @current_admin.id, month: Time.now.strftime("%Y-%m") + "-01 00:00:00", weekly: nil)
      if  params[:sort].present? && params[:sort][:field]
        @sale_plans = @sale_plans.order("#{params[:sort][:field]} #{params[:sort][:order]}")
      else
        @sale_plans = @sale_plans.order("updated_at desc")
      end
      @sale_plans =  @sale_plans.page(params[:page]).per(params[:limit])
    end

    def years_plan
      @sale_plans = SalePlan.from("(
                                    SELECT SUM(last_follow.budget_amount) AS budget_amounts, educode_sales_sale_plans.*, ss.annual,
                                           SUM(IF(educode_sales_sale_plans.clazz = 1 AND educode_sales_business_infos.plan_sign_on IS NOT NULL, last_follow.budget_amount, 0)) AS clazz_1,
                                           SUM(IF(educode_sales_sale_plans.clazz = 18 and plan_bid_on IS NOT NULL, last_follow.budget_amount, 0)) AS clazz_18,

                                           (SUM(IF(educode_sales_sale_plans.clazz = 2 AND prepayment_plan_on IS NOT NULL AND check_fee_plan_on IS NOT NULL AND qa_plan_on IS NOT NULL, prepayment_amount, 0)) +
                                           SUM(IF(educode_sales_sale_plans.clazz = 2 AND prepayment_plan_on IS NOT NULL AND check_fee_plan_on IS NOT NULL AND qa_plan_on IS NOT NULL, check_fee, 0)) +
                                           SUM(IF(educode_sales_sale_plans.clazz = 2 AND prepayment_plan_on IS NOT NULL AND check_fee_plan_on IS NOT NULL AND qa_plan_on IS NOT NULL, qa_amount, 0)) ) AS clazz_2,
                                           SUM(IF(educode_sales_sale_plans.clazz = 7, last_follow.budget_amount, 0)) AS clazz_7,

                                           SUM(IF(last_follow.bidded_date IS NOT NULL, last_follow.actual_amount, 0)) AS finish_bid,
                                           SUM(IF(last_follow.signed_date IS NOT NULL, last_follow.actual_amount, 0)) AS finish_sign,
                                           SUM(educode_sales_businesses.return_money) AS finish_return_money

                                    FROM educode_sales_sale_plans
                                    JOIN educode_sales_businesses ON educode_sales_businesses.id = educode_sales_sale_plans.business_id
                                    LEFT JOIN educode_sales_business_infos ON educode_sales_business_infos.sale_plan_id = educode_sales_sale_plans.id
                                    LEFT JOIN educode_sales_follow_ups AS last_follow ON educode_sales_businesses.last_follow_up_id = last_follow.id AND last_follow.deleted_at IS NULL
                                    LEFT JOIN educode_sales_assessments_settings AS ss ON ss.staff_id = educode_sales_sale_plans.staff_id
                                         AND YEAR(ss.assessment_year) = educode_sales_sale_plans.year AND ss.assessment = educode_sales_sale_plans.clazz
                                    WHERE educode_sales_sale_plans.deleted_at IS NULL
                                    GROUP BY educode_sales_sale_plans.staff_id, educode_sales_sale_plans.clazz, educode_sales_sale_plans.year
                                  ) AS educode_sales_sale_plans
      ")
      if @current_admin.is_admin?
        # @sale_plans = SalePlan
      else
        level = @current_admin.role.role_areas.find_by(clazz: '销售计划').level
        case level
        when '自己'
          @sale_plans = @sale_plans.where(staff_id: @current_admin.id)
        when '区域'
          staff_ids = Staff.joins(user: [user_extension: [department: :school]]).where("schools.province in (?)", @current_admin.areas.pluck(:name)).pluck(:id)
          business_ids = Business.where(school_id: StaffSchool.where(staff_id: @current_admin.id).pluck(:school_id)).pluck(:id)
          @sale_plans = @sale_plans.where("educode_sales_sale_plans.staff_id in (?) OR educode_sales_sale_plans.staff_id = ? OR educode_sales_sale_plans.business_id in (?)", staff_ids, @current_admin.id,business_ids)
        else
          # @sale_plans = SalePlan
        end
      end
      @sale_plans = @sale_plans.where.not(year: nil)

      @sale_plans = @sale_plans.where(deleted_at: nil)

      if params[:q].present? && params[:q][:year].present?
        @sale_plans = @sale_plans.where(year: params[:q][:year])
      end
      if params[:q].present? && params[:q][:staff_id].present?
        @sale_plans = @sale_plans.where(staff_id: params[:q][:staff_id])
      end
      if params[:sort].present? && params[:sort][:field]
        @sale_plans = @sale_plans.order("#{params[:sort][:field]} #{params[:sort][:order]}")
      else
        @sale_plans = @sale_plans.order("created_at desc")
      end

      @sale_plans = @sale_plans.page(params[:page]).per(params[:limit])
    end

    def years_all_target
      #目标类别
      target_clazz = params[:target_clazz]
      #年份
      year = params[:year].present? ? params[:year].to_i : Date.today.year.to_i
      type = params[:type] || 'staff'
      #员工id
      staff_id = params[:staff_id].present? ? params[:staff_id] : nil
      #客户类型
      school_tag_id = params[:school_tag_id]

      staffs = get_sales_staffs_by_id(staff_id, params[:page], params[:limit])
      if staffs.nil?
        return render json: {success: false, msg: '无此数据或权限不足'}
      end
      if type == 'all'
        @data = get_sales_all_plan(staffs, type, year, school_tag_id)
        unless @data.empty?
          @data[0]['id'] = 0
        end
        @total_count = 1
      else
        @total_count = staffs.count
        @data = []
        staffs.each do |staff|
          @data += get_sales_all_plan([staff], type, year, school_tag_id)
        end
      end
      unless @data.empty?
        @data = @data.reject { |item| item['target_clazz'].nil? || (target_clazz.present? && !target_clazz.include?(item['target_clazz']))}
        current_months = Time.now.month
        @data.each do |item|
          item['year_plan'] = 0
          (13 - current_months).times do |i|
            column = 'plan_' + (i + current_months).to_s
            item['year_plan'] += item[column].present? ? item[column] : 0
          end
        end

        if type != 'all'
          tmp_id = 1
          last_id = 0
          @data.each do |item|
            if last_id > 0 && item['id'] != last_id
              tmp_id += 1
            end
            last_id = item['id']
            item['staff_id'] = last_id
            item['id'] = tmp_id + (params[:page].to_i-1)*params[:limit].to_i
          end
        end
      end
    end

    def my_years_plan
      @sale_plans = SalePlan.where(staff_id: @current_admin.id)
    end

    def business_infos
      @data = @current_admin.business_infos.where(sale_plan_id: nil, year: params[:year])

      if params[:sort].present? && params[:sort][:field]
        @data = @data.order("#{params[:sort][:field]} #{params[:sort][:order]}")
      else
        @data = @data.order("created_at desc")
      end
      @data = @data.page(params[:page]).per(params[:limit])
    end

    def edit_bussiness_info
      @business_info = @current_admin.business_infos.find(params[:id])
      render layout: false
    end

    def edit_bussiness_info_extra
      @business_info = EducodeSales::BusinessInfo.find(params[:id])
      render layout: false
    end

    def plan_business_infos
      if params[:q] && params[:q][:year_search].present?
        @data = EducodeSales::BusinessInfo.where(year: params[:q][:year_search], staff_id: params[:staff_id])
      else
        @data = EducodeSales::BusinessInfo.where(year: params[:year], staff_id: params[:staff_id])
      end

      @data = @data.where.not(sale_plan_id: nil)

      if params[:q] && params[:q][:clazz].present?
        @data = @data.where(clazz: params[:q][:clazz])
      end

      if params[:q] && params[:q][:department].present?
        @data = @data.joins(business: [department: :school]).where("schools.name like ?", "%#{params[:q][:department]}%")
      end

      if params[:sort].present? && params[:sort][:field]
        @data = @data.order("#{params[:sort][:field]} #{params[:sort][:order]}")
      else
        @data = @data.order("created_at desc")
      end
      @data = @data.page(params[:page]).per(params[:limit])
    end

    def update_business_info
      business_info = @current_admin.business_infos.find(params[:id])
      if business_info.update(business_info_params)
        render_success
      else
        render_failure business_info
      end
    end

    def update_business_info_extra
      business_info = EducodeSales::BusinessInfo.find(params[:id])
      if business_info.update(update_business_info_extra_params)
        render_success
      else
        render_failure business_info
      end
    end

    def create_sales_plan
      @current_admin.business_infos.where(sale_plan_id: nil).each do |d|
        plan = @current_admin.sale_plans.create(year: d.year, business_id: d.business_id, clazz: d.clazz)
        d.update(sale_plan_id: plan.id)
      end
      render_success
    end

    def delete_business_info
      @current_admin.business_infos.where(id: params[:id]).destroy_all
      render_success
    end

    def edit_year_plan
      @clazz = EducodeSales::SalePlan::CLAZZ_NAME
      render layout: false
    end

    def week_plan
      common = Common.find_by(clazz: 'staff_type', name: '销售')
      @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }
      @years = (1..(Time.now.year - 2020)).map { |d| 2020 + d }
      @months = (1..12).map { |d| d }
    end

    def weekly
      common = Common.find_by(clazz: 'staff_type', name: '销售')
      @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }
      @years = (1..(Time.now.year - 2020)).map { |d| 2020 + d }
      @months = (1..12).map { |d| d }
    end

    def monthly
      common = Common.find_by(clazz: 'staff_type', name: '销售')
      @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }
      @years = (1..(Time.now.year - 2020)).map { |d| 2020 + d }
      @months = (1..12).map { |d| d }
    end

    def month_plan
      common = Common.find_by(clazz: 'staff_type', name: '销售')
      @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }
      @years = (1..(Time.now.year - 2020)).map { |d| 2020 + d }
      @months = (1..12).map { |d| d }
    end

    def year_plan
      common = Common.find_by(clazz: 'staff_type', name: '销售')
      @staffs = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] }
      @years = (1..(Time.now.year - 2020)).map { |d| 2020 + d }
      @months = (1..12).map { |d| d }
    end


    def year_execute
      respond_to do |format|
        format.html do
          @staffs = get_sales_staffs_by_id().map { |d| [d.user.real_name, d.id] }
          gon.school_tags = SchoolTag.where(for_missions: true).map { |d| {value: d.id, name: d.name } }
        end
        format.js do
          @staffs = get_sales_staffs_by_id().map { |d| [d.user.real_name, d.id] }
          gon.school_tags = SchoolTag.where(for_missions: true).map { |d| {value: d.id, name: d.name } }
          gon.school_tags.unshift({value: 0, name: '全部类型'})
        end
      end
    end

    def year_target
      respond_to do |format|
        format.html do
        end
        format.js do
          @staffs = get_sales_staffs_by_id().map { |d| [d.user.real_name, d.id] }
          gon.school_tags = SchoolTag.where(for_missions: true).map { |d| {value: d.id, name: d.name } }
          gon.school_tags.unshift({value: 0, name: '全部类型'})
          gon.month = Time.now.month
        end
        format.json do
          if @current_admin.is_admin?
            @data = EducodeSales::SalesTargetHistory.all
          else
            level = @current_admin.role.role_areas.find_by(clazz: '销售计划').level
            case level
            when '自己'
              @data = EducodeSales::SalesTargetHistory.where(staff_id: @current_admin.id)
            when '区域'
              @data = EducodeSales::SalesTargetHistory.all
              staff_ids = Staff.joins(user: [user_extension: [department: :school]]).where("schools.province in (?)", @current_admin.areas.pluck(:name)).pluck(:id)
              @data = @data.where(staff_id: staff_ids)
            else
              @data = EducodeSales::SalesTargetHistory.all
            end
          end
          @data = @data.order("staff_id asc,num desc, is_use desc,field(state, 1,0,2), id desc")
          if params[:year].present?
            @data = @data.where(year: params[:year])
          end
          if params[:school_tag_id].present?
            @data = @data.where(school_tag_id: params[:school_tag_id])
          end
          if params[:staff_id].present?
            @data = @data.where(staff_id: params[:staff_id])
          end
          if params[:year_target].present?
            @data = @data.where(year_target: params[:year_target])
          end
          if params[:target_clazz].present?
            @data = @data.where(target_clazz: params[:target_clazz])
          end
          @data = @data.page(params[:page]).per(params[:limit])
        end
      end
    end

    def cal_finish_amount
      start_at = params[:year] + "-01-01"
      end_at = params[:year] + "-12-31"
      amount = 0
      case  params[:target_clazz]
      when "回款"
        # 对应销售经理下回款时间的回款金额
        amount = EducodeSales::MoneyPlanRecord.joins("
            LEFT JOIN educode_sales_follow_ups AS last_follow_up ON educode_sales_businesses.last_follow_up_id = last_follow_up.id AND last_follow_up.deleted_at IS NULL
            LEFT JOIN educode_sales_assign_follow_ups ON educode_sales_assign_follow_ups.follow_up_id = last_follow_up.id").
          joins(business: [department: [school: :school_tags]]).where("school_tags.id = ?", params[:school_tag_id]).
          where("(educode_sales_assign_follow_ups.id IS NOT NULL AND educode_sales_assign_follow_ups.staff_id = ?) OR (educode_sales_assign_follow_ups.id IS NULL AND educode_sales_businesses.staff_id = ?)", params[:staff_id], params[:staff_id]).
          where("date_at >= ? AND date_at <= ?", start_at, end_at).sum("amount")

      when '商机'
        a_clazz = EducodeSales::Common.find_by(extras: 'a_class')
        start_at = "#{params[:year]}-01-01 00:00:01"
        end_at = "#{params[:year]}-12-31 23:59:59"
        staff_id =  params[:staff_id]
        # 对应销售经理下A类商机预算金额
        amount = EducodeSales::Business.from("(
          SELECT distinct educode_sales_businesses.id, last_follow_up.budget_amount, educode_sales_businesses.deleted_at, educode_sales_businesses.department_id
          FROM educode_sales_businesses
          JOIN educode_sales_follow_ups ON educode_sales_follow_ups.business_id = educode_sales_businesses.id AND educode_sales_businesses.clazz_id = #{a_clazz.id}
               AND educode_sales_follow_ups.created_at > '#{start_at}' AND educode_sales_follow_ups.created_at < '#{end_at}'
          JOIN educode_sales_follow_ups AS last_follow_up ON educode_sales_businesses.last_follow_up_id = last_follow_up.id AND last_follow_up.deleted_at IS NULL AND last_follow_up.clazz_id = #{a_clazz.id}
          LEFT JOIN educode_sales_assign_follow_ups ON educode_sales_assign_follow_ups.follow_up_id = last_follow_up.id
          WHERE (educode_sales_assign_follow_ups.id IS NOT NULL AND educode_sales_assign_follow_ups.staff_id = '#{staff_id}') OR (educode_sales_assign_follow_ups.id IS NULL AND educode_sales_businesses.staff_id = '#{staff_id}')
          ) AS educode_sales_businesses
        ").joins(department: [school: :school_tags]).where("school_tags.id = ?", params[:school_tag_id]).sum(:budget_amount)
      when '中标'
        # 对应销售经理下合同列表的中标时间下合同金额
        contract_ids = EducodeSales::Common.where(clazz: '商机阶段', name: ['已中标', '已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id)
        businesses = EducodeSales::Business.joins("
          JOIN educode_sales_follow_ups ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id
        ").joins("LEFT JOIN educode_sales_assign_follow_ups ON educode_sales_assign_follow_ups.follow_up_id = educode_sales_businesses.last_follow_up_id").
          joins(department: [school: :school_tags]).where("school_tags.id = ?", params[:school_tag_id]).
          where("(educode_sales_assign_follow_ups.id IS NOT NULL AND educode_sales_assign_follow_ups.staff_id = ?) OR (educode_sales_assign_follow_ups.id IS NULL AND educode_sales_businesses.staff_id = ?)", params[:staff_id], params[:staff_id]).
          where("educode_sales_follow_ups.stage_id in (?)", contract_ids).
          where("educode_sales_follow_ups.bidded_date >= ? AND educode_sales_follow_ups.bidded_date <= ?", start_at, end_at).sum(:actual_amount)
      end
      render json: {amount: amount}
    end

    def add_year_target
      history = EducodeSales::SalesTargetHistory.find_by(staff_id: params[:staff_id], year: params[:year], school_tag_id: params[:school_tag_id], target_clazz: params[:target_clazz])
      # history.update_attributes(params.permit(:m_target_1, :m_target_2, :m_target_3, :m_target_4, :m_target_5, :m_target_6, :m_target_7, :m_target_8, :m_target_9, :m_target_10, :m_target_11, :m_target_12)) if history
      # 不能重复添加
      #if EducodeSales::SalesTargetHistory.exists?(staff_id: params[:staff_id], year: params[:year], school_tag_id: params[:school_tag_id], target_clazz: params[:target_clazz])
      #  return render_failure "不能重复添加"
      #end
      target = EducodeSales::SalesTargetHistory.new(year_target_param)
      if history
        use_old_attributes = %w[m_target_1 m_target_2 m_target_3 m_target_4 m_target_5 m_target_6 m_target_7 m_target_8 m_target_9 m_target_10 m_target_11 m_target_12] - params.keys
        use_old_attributes.each do |attr|
          target[attr] = history[attr]
        end
      end
      if history
        target.num = history.num
      else
        target.num = EducodeSales::SalesTargetHistory.maximum(:id).to_i + 1
      end

      target.creator_id = @current_admin.id
      target.state = '待审核'
      if target.save
        render json: { id: target.id, state: target.state, updated_at: target.updated_at.to_s(:date) }
      else
        render_failure target
      end
    end

    def staff_year_target
      respond_to do |format|
        format.html do
        end
        format.js do
          @staffs = get_sales_staffs_by_id().map { |d| [d.user.real_name, d.id] }
          gon.school_tags = SchoolTag.where(for_missions: true).map { |d| {value: d.id, name: d.name } }
          gon.month = Time.now.month
        end
        format.json do
          if @current_admin.is_admin?
            @data = EducodeSales::SalesTarget.all
          else
            level = @current_admin.role.role_areas.find_by(clazz: '销售计划').level
            case level
            when '自己'
              @data = EducodeSales::SalesTarget.where(staff_id: @current_admin.id)
            when '区域'
              @data = EducodeSales::SalesTarget.all
              staff_ids = Staff.joins(user: [user_extension: [department: :school]]).where("schools.province in (?)", @current_admin.areas.pluck(:name)).pluck(:id)
              @data = @data.where(staff_id: staff_ids)
            else
              @data = EducodeSales::SalesTarget.all
            end
          end
          @data = @data.order("staff_id asc,field(state, 1,2,0), id desc")
          if params[:year].present?
            @data = @data.where(year: params[:year])
          end
          if params[:school_tag_id].present?
            @data = @data.where(school_tag_id: params[:school_tag_id])
          end
          if params[:staff_id].present?
            @data = @data.where(staff_id: params[:staff_id])
          end
          if params[:target_clazz].present?
            @data = @data.where(target_clazz: params[:target_clazz])
          end
          @data = @data.page(params[:page]).per(params[:limit])
        end
      end
    end

    def save_staff_year_target
      unless params[:id].present?
        history = EducodeSales::SalesTarget.find_by(staff_id: params[:staff_id], year: params[:year], school_tag_id: params[:school_tag_id], target_clazz: params[:target_clazz])
        # 不能重复添加
        unless history.nil?
          return render_failure "同类型数据已存在，无法添加"
        end
        target = EducodeSales::SalesTarget.new(year_target_param)
      else
        target = EducodeSales::SalesTarget.find(params[:id])
        if target.nil?
          return render_failure "数据不存在，无法编辑"
        end
        target.update_attributes(year_target_param)
      end

      target.creator_id = @current_admin.id
      target.state = '待审核'
      if target.save
        create_target_operation_log(target.attributes.dup)
        render json: { id: target.id, state: target.state, updated_at: target.updated_at.to_s(:date) }
      else
        render_failure target
      end
    end

    def audit_staff_year_target
      target = EducodeSales::SalesTarget.find(params[:id])
      if target.state == '待审核'
        target.state = params[:state]
        target.comment = params[:comment]
        target.reviewer_id = @current_admin.id
        if target.save
          create_target_operation_log(target.attributes.dup)
          p
          render json: {id: target.id, comment: target.comment, reviewer: @current_admin.user&.real_name, state: target.state, updated_at: target.updated_at.to_s(:date)}
        else
          render_failure target
        end
      else
        render json: {success: false, msg: '该状态不能编辑'}
      end
    end

    def staff_year_target_log
      @data = EducodeSales::SalesTargetLog.where(target_id: params[:target_id]).order('id desc').page(params[:page]).per(params[:limit])
      respond_to do |format|
        format.html do
          render layout: false
        end
        format.json do
        end
      end
    end

    def staff_year_business
      respond_to do |format|
        format.html do
          @staff_id = (params[:staff_id].blank? || params[:staff_id] == 'null') ? nil : params[:staff_id]
          @year = params[:year].present? ? params[:year].to_i : Date.today.year.to_i
          @target_clazz = params[:target_clazz].present? ? params[:target_clazz] : '中标'
          @type = params[:type]
          @school_tag_id = params[:school_tag_id]
          render layout: false
        end
        format.json do
          staff_id = (params[:staff_id].blank? || params[:staff_id] == 'null') ? nil : params[:staff_id]
          staffs = get_sales_staffs_by_id(staff_id)
          if staffs.nil?
            return render json: {success: false, msg: '无此数据或权限不足'}
          end
          @year = params[:year].present? ? params[:year].to_i : Date.today.year.to_i
          month = params[:month].present? ? params[:month].to_i : nil
          @target_clazz = params[:target_clazz].present? ? params[:target_clazz] : '中标'
          school_tag_id = params[:school_tag_id].present? && params[:school_tag_id]!='0' ? params[:school_tag_id] : nil
          type = params[:type].present? ? params[:type] : 'plan'

          if month.present? && month != 0
            first_day = Date.new(@year, month, 1)
            last_day = Date.new(@year, month, -1)
          else
            first_day = Date.new(@year, 1, 1)
            last_day = Date.new(@year, 12, -1)
          end
          
          #staffs = get_sales_staffs_by_id(staff_id)
          staff_ids = staffs.pluck(:id).compact.join(',')
          follow_up_ids = get_follow_up_id(staff_ids)
          if follow_up_ids.empty?
            return
          end
          school_where = ''
          unless follow_up_ids.empty?
            ##查出哪些学校既属于本科又属于军事院校
            school_ids = EducodeSales::MoneyPlanRecord.from(
              "(SELECT TM1.school_id
          FROM school_tag_middles AS TM1
          LEFT JOIN school_tag_middles AS TM2
          ON TM1.school_id = TM2.school_id AND TM1.school_tag_id=1 AND TM2.school_tag_id=10
          WHERE TM1.school_tag_id IS NOT NULL AND TM2.school_tag_id IS NOT NULL AND TM1.school_id IN (
            SELECT school_id FROM educode_sales_businesses WHERE last_follow_up_id in (#{follow_up_ids})
          )) AS school"
            ).pluck(:school_id).join(',')
            unless school_ids.empty?
              school_where = " AND (TM.school_id in (#{school_ids}) AND TM.school_tag_id = 10 OR TM.school_id not in (#{school_ids}))"
            end
          end
          follow_up_ids_where = follow_up_ids.present? ? " AND B.last_follow_up_id in (#{follow_up_ids})" : " AND B.id in(0)"
          if type == 'plan'
            case @target_clazz
            when '中标'
              @data = get_staff_bidden_plan(follow_up_ids_where, school_where, first_day, last_day, school_tag_id)
            when '回款'
              @data = get_staff_money_plan(follow_up_ids_where, school_where, first_day, last_day, school_tag_id)
            when '商机'
              @data = get_staff_business_plan(follow_up_ids_where, school_where, first_day, last_day, school_tag_id)
            else
              # type code here
            end
          else
            case @target_clazz
            when '中标'
              @data = get_staff_bidden_finish(follow_up_ids_where, school_where, first_day, last_day, school_tag_id)
            when '回款'
              @data = get_staff_money_finish(follow_up_ids_where, school_where, first_day, last_day, school_tag_id)
            when '商机'
              @data = get_staff_business_finish(follow_up_ids_where, school_where, first_day, last_day, school_tag_id)
            else
            end
          end
          @data = @data.page(params[:page]).per(params[:limit])
        end
      end
    end

    private

    def plan_params
      params.permit(:month, :weekly, :content, :finish_rate, :business_id, :common_id, :assign_id)
    end

    def business_info_params
      params.permit(:plan_bid_on, :plan_sign_on, :prepayment_plan_on, :prepayment_amount)
    end

    def update_business_info_extra_params
      params.permit(:plan_bid_on, :actual_bidded_on, :bidded_amount, :plan_sign_on, :actual_sign_on, :sign_amount, :plan_deploy_on, :actual_deploy_on, :plan_check_on,
                    :actual_check_on, :prepayment_plan_on, :prepayment_actual_on, :prepayment_amount, :check_fee_plan_on, :check_fee_actual_on, :check_fee, :qa_plan_on, :qa_actual_on, :qa_amount, :account_receivable)
    end

    def year_target_param
      params.permit(:staff_id, :year, :target_clazz, :school_tag_id, :year_finish, :year_target, :year_diff, :target_1, :target_2, :target_3, :target_4, :target_5, :target_6, :target_7, :target_8, :target_9, :target_10, :target_11, :target_12)
    end

    def make_target_data(sales_all_plan, data, column, type)
      data.each do |item|
        _column = "#{column}_#{item.m.to_i.to_s}"
        if sales_all_plan[type][item.school_tag_id].present?
          if sales_all_plan[type][item.school_tag_id][_column].present?
            sales_all_plan[type][item.school_tag_id][_column] += item.amount.to_i
          else
            sales_all_plan[type][item.school_tag_id][_column] = item.amount.to_i
          end
        else
          sales_all_plan[type][item.school_tag_id] = {"school_tag_id" => item.school_tag_id, "school_tag_name" => item.school_tag_name}
          sales_all_plan[type][item.school_tag_id][_column] = item.amount.to_i
        end
        sales_all_plan[type][item.school_tag_id]['type'] = type
      end
      sales_all_plan
    end


    def get_sales_all_plan(staffs, type, year, school_tag_id)
      if staffs.empty?
        return []
      end
      sales_all_plan = {:bidder => {}, :business => {}, :money => {}}
      staff_ids = staffs.pluck(:id).join(',')
      select_school_tag_id = school_tag_id.blank? ? '0 as school_tag_id' : 'school_tag_id'
      and_where_school_tag_id = school_tag_id.present? && school_tag_id !='0' ? " AND school_tag_id in (#{school_tag_id})" : ""
        # 中标&商机&回款 - 目标按月
      sales_target_histories = EducodeSales::SalesTarget.from("
         (SELECT H.id,target_clazz,#{select_school_tag_id},T.name as school_tag_name,sum(target_1) as target_1, sum(target_2) target_2, sum(target_3) target_3, sum(target_4) target_4, sum(target_5) target_5, sum(target_6) target_6, sum(target_7) target_7,sum(target_8) target_8,sum(target_9) target_9, sum(target_10) target_10, sum(target_11) target_11, sum(target_12) target_12
         FROM educode_sales_sales_targets as H
         LEFT JOIN school_tags AS T ON H.school_tag_id = T.id
         WHERE state=1 AND year = #{year} AND T.for_missions = 1
         AND staff_id in( #{staff_ids})
         #{and_where_school_tag_id}
         GROUP BY target_clazz #{school_tag_id.present? ? ",school_tag_id" : ""}
         ) as educode_sales_sales_targets
      ").order('target_clazz asc, school_tag_id asc,id desc')

      sales_target_histories.each do |history|
        case history.target_clazz
        when '中标'
          sales_all_plan[:bidder][history.school_tag_id] = history.attributes.except('id')
        when '商机'
          sales_all_plan[:business][history.school_tag_id] = history.attributes.except('id')
        when '回款'
          sales_all_plan[:money][history.school_tag_id] = history.attributes.except('id')
        else
        end
      end

      ##follow_up_ids = EducodeSales::AssignFollowUp.where(staff_id: staffs.pluck(:id)).pluck(:follow_up_id).compact.join(',')
      follow_up_ids = EducodeSales::Business
                     .left_joins(last_follow_up: :assign_follow_ups)
                     .where("educode_sales_businesses.last_follow_up_id IS NOT NULL AND (educode_sales_businesses.staff_id in (#{staff_ids}) AND educode_sales_assign_follow_ups.id is null) OR educode_sales_assign_follow_ups.staff_id in (#{staff_ids})")
                     .pluck(:last_follow_up_id)
                     .compact
                     .join(',')

      school_where = ''
      unless follow_up_ids.empty?
        ##查出哪些学校既属于本科又属于军事院校
        school_ids = EducodeSales::MoneyPlanRecord.from(
          "(SELECT TM1.school_id
          FROM school_tag_middles AS TM1
          LEFT JOIN school_tag_middles AS TM2
          ON TM1.school_id = TM2.school_id AND TM1.school_tag_id=1 AND TM2.school_tag_id=10
          WHERE TM1.school_tag_id IS NOT NULL AND TM2.school_tag_id IS NOT NULL AND TM1.school_id IN (
            SELECT school_id FROM educode_sales_businesses WHERE last_follow_up_id in (#{follow_up_ids})
          )) AS school"
        ).pluck(:school_id).join(',')

        unless school_ids.empty?
          school_where = "AND ((TM.school_id in (#{school_ids}) AND TM.school_tag_id = 10) OR TM.school_id not in (#{school_ids}))"
        end
      end
      start_at = Date.new(year, 1, 1)
      end_at = Date.new(year, 12, -1)
      follow_up_ids_where = follow_up_ids.present? ? " AND B.last_follow_up_id in (#{follow_up_ids})" : " AND B.id in(0)"
      select_columns = 'amount,m,' + select_school_tag_id + ',school_tag_name';
      group_by = school_tag_id == '0' ? 'm' : 'school_tag_id, m'
      # 中标 - 完成 - 合同额(actual_amount) 中标时间 bidded_date
      bidder_finish = EducodeSales::FollowUp.select(select_columns).from(
        "(SELECT sum(F.actual_amount) as amount, date_format(F.bidded_date, '%m') as m,T.name as school_tag_name,
          TM.school_tag_id as school_tag_id,F.deleted_at
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND F.bidded_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{and_where_school_tag_id}
          GROUP BY #{group_by}
          ) AS educode_sales_follow_ups"
      ).order('school_tag_id ASC, m asc')

      # 中标 - 计划 金额invitation_money 时间invitation_at
      bidder_plan = EducodeSales::FollowUp.select(select_columns).from(
        "(SELECT sum(invitation_money) as amount, date_format(invitation_at, '%m') as m,T.name as school_tag_name,
          TM.school_tag_id as school_tag_id,F.deleted_at
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND F.invitation_at BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{and_where_school_tag_id}
          GROUP BY #{group_by}
          ORDER BY school_tag_id ASC
          ) AS educode_sales_follow_ups"
      ).order('school_tag_id ASC, m asc')
      # 回款 - 完成
      money_finish = EducodeSales::MoneyPlanRecord.select(select_columns).from(
        "(SELECT sum(amount) as amount, date_format(R.date_at, '%m') as m,T.name as school_tag_name,
          TM.school_tag_id as school_tag_id
          FROM educode_sales_money_plan_records as R
          LEFT JOIN educode_sales_businesses AS B ON R.business_id=B.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND T.for_missions = 1 AND R.date_at BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{and_where_school_tag_id}
          GROUP BY #{group_by}
          ) AS educode_sales_money_plan_records"
      ).order('school_tag_id ASC, m asc')
      # 回款 - 计划                                      .group_by { |d| d.m }
      money_plan = EducodeSales::FollowUp.select(select_columns).from(
        "(SELECT sum(plan_return_money) as amount, date_format(plan_return_date, '%m') as m,T.name as school_tag_name,
          F.deleted_at,TM.school_tag_id as school_tag_id
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND plan_return_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{and_where_school_tag_id}
          GROUP BY #{group_by}
          ORDER BY school_tag_id ASC
          ) AS educode_sales_follow_ups"
      ).order('school_tag_id ASC, m asc')
      # 商机-完成                                      .where(:bidded_date => start_datetime..end_datetime)
      business_finish = EducodeSales::FollowUp.select(select_columns).from(
        "(SELECT sum(budget_amount) as amount, date_format(bidded_date, '%m') as m,T.name as school_tag_name,
          F.deleted_at,TM.school_tag_id as school_tag_id
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND bidded_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{and_where_school_tag_id}
          GROUP BY #{group_by}
          ) AS educode_sales_follow_ups
          "
      ).order('school_tag_id ASC, m asc')
      # 商机-计划
      business_plan = EducodeSales::FollowUp.select(select_columns).from(
        "(SELECT sum(plan_a_money) as amount, date_format(plan_a_date, '%m') as m,T.name as school_tag_name,
          F.deleted_at,TM.school_tag_id as school_tag_id
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND plan_a_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{and_where_school_tag_id}
          GROUP BY #{group_by}
          ORDER BY school_tag_id ASC
          ) AS educode_sales_follow_ups"
      ).order('school_tag_id ASC, m asc')
      #中标完成
      sales_all_plan = make_target_data(sales_all_plan, bidder_finish, 'finish', :bidder)
      #中标计划
      sales_all_plan = make_target_data(sales_all_plan, bidder_plan, 'plan', :bidder)
      #回款完成
      sales_all_plan = make_target_data(sales_all_plan, money_finish, 'finish', :money)
      #回款计划
      sales_all_plan = make_target_data(sales_all_plan, money_plan, 'plan', :money)
      #商机完成
      sales_all_plan = make_target_data(sales_all_plan, business_finish, 'finish', :business)
      #商机计划
      sales_all_plan = make_target_data(sales_all_plan, business_plan, 'plan', :business)

      data = sales_all_plan[:bidder].values + sales_all_plan[:business].values + sales_all_plan[:money].values

      if data.empty?
        return []
      else
        p school_tag_id
        data.each do |item|
          item['id'] = type=='staff' ? staffs[0].id : 0
          item['name'] = type=='staff' ? staffs[0].name : ''
          item['target_clazz'] = item['target_clazz'].present? ? item['target_clazz'] : SalesTargetHistory::TARGET_CLAZZ_NAME[item['type']]
          item['school_tag_id'] = school_tag_id.present? ? item['school_tag_id'] : 0
          item['school_tag_name'] = school_tag_id.present? ? item['school_tag_name'] : '全部'
        end
      end
    end

    def get_sales_staffs_by_id(staff_id=nil, page=nil, limit=nil)
      role_ids = EducodeSales::Role.where(name: ['销售总监','销售区域总监', '销售经理']).pluck(:id)
      staffs = Staff.where(role_id: role_ids)
      unless staff_id.nil?
        staffs = staffs.where(id: staff_id)
      end
      unless staffs.nil?
        if @current_admin.is_admin?
        else
          level = @current_admin.role.role_areas.find_by(clazz: '销售计划').level
          case level
          when '自己'
            staffs = staffs.where(id: @current_admin.id)
          when '区域'
            staffs = staffs.joins(user: [user_extension: [department: :school]]).where("schools.province in (?)", @current_admin.areas.pluck(:name))
          else
          end
        end
      end
      if !page.nil? && !limit.nil?
        staffs.page(params[:page]).per(params[:limit])
      end
      staffs
    end

    def create_target_operation_log(target)
      id = target['id']
      target.delete('id')
      target.delete('created_at')
      target.delete('updated_at')
      log_model = EducodeSales::SalesTargetLog.new(target)
      log_model.target_id = id
      log_model.save
    end

    def get_staff_bidden_plan(follow_up_ids_where, school_where, start_at, end_at, school_tag_id)
      EducodeSales::Business.from(
        "(SELECT B.id,B.name,B.school_id,B.department_id,B.clazz_id,B.last_follow_up_id,B.staff_id,F.invitation_money as money, F.invitation_at as time,
          F.created_at as created_at,T.name as school_tag_name,TM.school_tag_id as school_tag_id,F.deleted_at
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND F.invitation_at BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{school_tag_id.present? ? " AND school_tag_id in (#{school_tag_id})" : ""}
          ORDER BY F.invitation_at ASC
          ) AS educode_sales_businesses"
      )
    end

    def get_staff_money_plan(follow_up_ids_where, school_where, start_at, end_at, school_tag_id)
      EducodeSales::Business.from(
        "(SELECT B.id,B.name,B.school_id,B.department_id,B.clazz_id,B.last_follow_up_id,B.staff_id,F.plan_return_money as money, F.plan_return_date as time,
          F.created_at as created_at,T.name as school_tag_name,F.deleted_at,TM.school_tag_id as school_tag_id
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND plan_return_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{school_tag_id.present? ? " AND school_tag_id in (#{school_tag_id})" : ""}
          ORDER BY plan_return_date ASC
          ) AS educode_sales_businesses"
      )
    end

    def get_staff_business_plan(follow_up_ids_where, school_where, start_at, end_at, school_tag_id)
      EducodeSales::Business.from(
        "(SELECT B.id,B.name,B.school_id,B.department_id,B.clazz_id,B.last_follow_up_id,B.staff_id,F.plan_a_money as money, F.plan_a_date as time,
          F.created_at as created_at,T.name as school_tag_name,F.deleted_at,TM.school_tag_id as school_tag_id
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND plan_a_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{school_tag_id.present? ? " AND school_tag_id in (#{school_tag_id})" : ""}
          ORDER BY plan_a_date ASC
          ) AS educode_sales_businesses"
      )
    end

    def get_staff_bidden_finish(follow_up_ids_where, school_where, start_at, end_at, school_tag_id)
      EducodeSales::Business.from(
        "(SELECT B.id,B.name,B.school_id,B.department_id,B.clazz_id,B.last_follow_up_id,B.staff_id,F.actual_amount as money, F.bidded_date as time,
          F.created_at as created_at,T.name as school_tag_name,F.deleted_at,TM.school_tag_id as school_tag_id
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND F.bidded_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{school_tag_id.present? ? " AND school_tag_id in (#{school_tag_id})" : ""}
          ORDER BY bidded_date ASC
          ) AS educode_sales_businesses"
      )
    end

    def get_staff_money_finish(follow_up_ids_where, school_where, start_at, end_at, school_tag_id)
      EducodeSales::Business.from(
        "(SELECT B.id,B.name,B.school_id,B.department_id,B.clazz_id,B.deleted_at,B.last_follow_up_id,B.staff_id,R.amount as money, R.date_at as time,
          R.created_at as created_at,T.name as school_tag_name,TM.school_tag_id as school_tag_id
          FROM educode_sales_money_plan_records as R
          LEFT JOIN educode_sales_businesses AS B ON R.business_id=B.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND T.for_missions = 1 AND R.date_at BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{school_tag_id.present? ? " AND school_tag_id in (#{school_tag_id})" : ""}
          ORDER BY R.date_at ASC
          ) AS educode_sales_businesses"
      )
    end

    def get_staff_business_finish(follow_up_ids_where, school_where, start_at, end_at, school_tag_id)
      EducodeSales::Business.from(
        "(SELECT B.id,B.name,B.school_id,B.department_id,B.clazz_id,B.last_follow_up_id,B.staff_id,F.budget_amount as money, F.bidded_date as time,
          F.created_at as created_at,T.name as school_tag_name,F.deleted_at,TM.school_tag_id as school_tag_id
          FROM educode_sales_businesses AS B
          LEFT JOIN educode_sales_follow_ups as F ON B.last_follow_up_id=F.id
          LEFT JOIN school_tag_middles AS TM ON TM.school_id=B.school_id
          LEFT JOIN school_tags as T ON TM.school_tag_id=T.id AND T.for_missions = 1
          WHERE B.deleted_at IS NULL AND F.deleted_at IS NULL AND T.for_missions = 1 AND bidded_date BETWEEN '#{start_at}' AND '#{end_at}'
          #{follow_up_ids_where.present? ? follow_up_ids_where : ''}
          #{school_where.present? ? school_where : ''}
          #{school_tag_id.present? ? " AND school_tag_id in (#{school_tag_id})" : ""}
          ORDER BY F.bidded_date ASC
          ) AS educode_sales_businesses"
      )
    end

    def get_follow_up_id(staff_ids)
      EducodeSales::Business
        .left_joins(last_follow_up: :assign_follow_ups)
        .where("educode_sales_businesses.last_follow_up_id IS NOT NULL AND (educode_sales_businesses.staff_id in (#{staff_ids}) AND educode_sales_assign_follow_ups.id is null) OR educode_sales_assign_follow_ups.staff_id in (#{staff_ids})")
        .pluck(:last_follow_up_id)
        .compact
        .join(',')
    end
  end
end