module EducodeSales
  module SaleTrendsHelper
    NAMES = %w[计划投标额 计划投标额累计 实际中标额 实际中标额累计]
    RETURN_NAMES = %w[计划回款额 计划回款额-全部 实际回款额 实际回款额-累计 计划回款额-O类]

    def goal_forecast_quarter(labels, selects, staff_id = nil, property)
      plan_get = plan_get(staff_id, labels, "quarter", property)
      actual_get = actual_get(staff_id, labels, "quarter", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == NAMES[0]
                 labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_get[[year, quarter]]&.pluck(:budget_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == NAMES[1]
                 arr = labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_get[[year, quarter]]&.pluck(:budget_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == NAMES[2]
                 labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_get[[year, quarter]]&.pluck(:actual_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == NAMES[3]
                 arr = labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_get[[year, quarter]]&.pluck(:actual_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end
        type = if select == NAMES[3] || select == NAMES[1]
                 'line'
               else
                 "bar"
               end
        backgroundColor = if select == NAMES[3] || select == NAMES[1]
                            'rgba(54, 162, 235, 0.2)'
                          else
                            SaleTrend::COLORS[i]
                          end
        {
          type: type,
          label: select,
          data: data.map { |d| d.round(2)},
          backgroundColor:
            backgroundColor,
          borderColor:
            SaleTrend::COLORS[i],
          borderWidth: 1
        }
      end
      hash_a = {
        labels: labels,
        datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
        labels: labels,
        datasets: [datasets[1], datasets[3]]
      }
      [hash_a, hash_b]
    end

    def goal_forecast_week(labels, selects, staff_id = nil, property)

      plan_get = plan_get(staff_id, labels, "week", property)
      actual_get = actual_get(staff_id, labels, "week", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == NAMES[0]
                 labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   plan_get[yearweek].to_f.round(3)
                 end
               elsif select == NAMES[1]
                 arr = labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   plan_get[yearweek].to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == NAMES[2]
                 labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   actual_get[yearweek].to_f.round(3)
                 end
               elsif select == NAMES[3]
                 arr = labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   actual_get[yearweek].to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end
        type = if select == NAMES[0] || select == NAMES[2]
                 'bar'
               else
                 "line"
               end
        backgroundColor = if select == NAMES[0] || select == NAMES[2]
                            SaleTrend::COLORS[i]
                          else
                            'rgba(54, 162, 235, 0.2)'
                          end
        {
          type: type,
          label: select,
          data: data.map { |d| d.round(2)},
          backgroundColor:
            backgroundColor,
          borderColor:
            SaleTrend::COLORS[i],
          borderWidth: 1
        }
      end

      hash_a = {
        labels: labels,
        datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
        labels: labels,
        datasets: [datasets[1], datasets[3]]
      }
      [hash_a, hash_b]
    end

    def goal_forecast_month(labels, selects, staff_id = nil, property)
      plan_get = plan_get(staff_id, labels, "month", property)
      actual_get = actual_get(staff_id, labels, "month", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == NAMES[0]
                 labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_get[[year, month]]&.pluck(:budget_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == NAMES[1]
                 arr = labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_get[[year, month]]&.pluck(:budget_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == NAMES[2]
                 labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_get[[year, month]]&.pluck(:actual_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == NAMES[3]
                 arr = labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_get[[year, month]]&.pluck(:actual_amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end

        type = if select == NAMES[3] || select == NAMES[1]
                 'line'
               else
                 "bar"
               end
        backgroundColor = if select == NAMES[3] || select == NAMES[1]
                            'rgba(54, 162, 235, 0.2)'
                          else
                            SaleTrend::COLORS[i]
                          end
        {
          type: type,
          label: select,
          data: data.map { |d| d.round(2)},
          backgroundColor:
            backgroundColor,
          borderColor:
            SaleTrend::COLORS[i],
          borderWidth: 1
        }
      end

      hash_a = {
        labels: labels,
        datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
        labels: labels,
        datasets: [datasets[1], datasets[3]]
      }
      [hash_a, hash_b]
    end

    def goal_forecast_year(labels, selects, staff_id = nil, property)
      plan_get = plan_get(staff_id, labels, "year", property)
      actual_get = actual_get(staff_id, labels, "year", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == NAMES[0]
                 labels.map { |d| plan_get[d.to_i] || 0 }
               elsif select == NAMES[1]
                 arr = labels.map { |d| plan_get[d.to_i] || 0 }
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == NAMES[2]
                 labels.map { |d| actual_get[d.to_i] || 0 }
               elsif select == NAMES[3]
                 arr = labels.map { |d| actual_get[d.to_i] || 0 }
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end

        type = if select == NAMES[3] || select == NAMES[1]
                 'line'
               else
                 "bar"
               end
        backgroundColor = if select == NAMES[3] || select == NAMES[1]
                            'rgba(54, 162, 235, 0.2)'
                          else
                            SaleTrend::COLORS[i]
                          end
        {
          type: type,
          label: select,
          data: data.map { |d| d.round(2)},
          backgroundColor:
            backgroundColor,
          borderColor:
            SaleTrend::COLORS[i],
          borderWidth: 1
        }
      end
      hash_a = {
        labels: labels,
        datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
        labels: labels,
        datasets: [datasets[1], datasets[3]]
      }
      [hash_a, hash_b]
    end

    def visit_analysis_charts(visit_count_year, visit_count_season, visit_count_month, visit_count_week, visit_type, title_names)
      if visit_type.present? && visit_type.include?("1")
        names =  visit_count_year.map{|d| d[0]}
      elsif visit_type.present? && visit_type.include?("2")
        names =  visit_count_season.map{|d| d[0]}
      elsif visit_type.present? && visit_type.include?("3")
        names =  visit_count_month.map{|d| d[0]}
      elsif visit_type.present? && visit_type.include?("4")
        names =  visit_count_week.map{|d| d[0]}
      else
        names =  visit_count_year.map{|d| d[0]}
      end

      visit_count_season_data = []
      names.each do |name|
        data = visit_count_season.find { |d| d[0] == name}
        if data
          visit_count_season_data << data
        else
          visit_count_season_data << [name, 0]
        end
      end

      visit_count_month_data = []
      names.each do |name|
        data = visit_count_month.find { |d| d[0] == name}
        if data
          visit_count_month_data << data
        else
          visit_count_month_data << [name, 0]
        end
      end

      visit_count_week_data = []
      names.each do |name|
        data = visit_count_week.find { |d| d[0] == name}
        if data
          visit_count_week_data << data
        else
          visit_count_week_data << [name, 0]
        end
      end

      year = {
          label: title_names[0],
          data: visit_count_year.map {|d| d[1].to_i},
          backgroundColor: 'rgba(255, 99, 132, 1)'}

      season = {
          label: title_names[1],
          data: visit_count_season_data.map {|d| d[1].to_i},
          backgroundColor: 'rgba(255, 159, 64, 1)'}

      month = {
          label: title_names[2],
          data:visit_count_month_data.map {|d| d[1].to_i},
          backgroundColor: 'rgba(54, 162, 235, 1)'}

      week = {
          label: title_names[3],
          data: visit_count_week_data.map {|d| d[1].to_i},
          backgroundColor: 'rgba(153, 102, 255, 1)'}

      final_data = []
      if visit_type.include?("1")
        final_data << year
      end
      if visit_type.include?("2")
        final_data << season
      end
      if visit_type.include?("3")
        final_data << month
      end
      if visit_type.include?("4")
        final_data << week
      end

      if visit_type.present?
        {
            labels: names,
            datasets: final_data
        }
      else
        {
            labels: names,
            datasets: [year, season, month, week]
        }
      end
    end


    def return_money_forecast_week(labels, selects, staff_id = nil, property)
      plan_return = plan_return(staff_id, labels, "week", property)
      actual_return = actual_return(staff_id, labels, "week", property)
      plan_return_by_o = plan_return_by_o(staff_id, labels, "week", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == RETURN_NAMES[0]
                 labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   plan_return[yearweek].to_f.round(3)
                 end
               elsif select == RETURN_NAMES[1]
                 arr = labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   plan_return[yearweek].to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[2]
                 labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   actual_return[yearweek].to_f.round(3)
                 end
               elsif select == RETURN_NAMES[3]
                 arr = labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   actual_return[yearweek].to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[4]
                 arr = labels.map do |d|
                   week = d.split("-").last
                   year = d.split("-").first
                   yearweek = "#{year}#{week}".to_i
                   plan_return_by_o[yearweek].to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end
        type = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                 'bar'
               else
                 "line"
               end
        backgroundColor = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                            SaleTrend::COLORS[i]
                          else
                            'rgba(54, 162, 235, 0.2)'
                          end
        {
            type: type,
            label: select,
            data: data&.map { |d| d.round(2) },
            backgroundColor: backgroundColor,
            borderColor: SaleTrend::COLORS[i],
            borderWidth: 1
        }
      end

      hash_a = {
          labels: labels,
          datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
          labels: labels,
          datasets: [datasets[1], datasets[4], datasets[3]]
      }
      [hash_a, hash_b]
    end

    def return_money_forecast_quarter(labels, selects, staff_id = nil, property)
      plan_return = plan_return(staff_id, labels, "quarter", property)
      actual_return = actual_return(staff_id, labels, "quarter", property)
      plan_return_by_o = plan_return_by_o(staff_id, labels, "quarter", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == RETURN_NAMES[0]
                 labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_return[[year, quarter]]&.pluck(:plan_return_money)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == RETURN_NAMES[1]
                 arr = labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_return[[year, quarter]]&.pluck(:plan_return_money)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[2]
                 labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_return[[year, quarter]]&.pluck(:amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == RETURN_NAMES[3]
                 arr = labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_return[[year, quarter]]&.pluck(:amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[4]
                 arr = labels.map do |d|
                   quarter = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_return_by_o[[year, quarter]]&.pluck(:plan_return_money)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end
        type = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                 'bar'
               else
                 "line"
               end
        backgroundColor = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                            SaleTrend::COLORS[i]
                          else
                            'rgba(54, 162, 235, 0.2)'
                          end
        {
            type: type,
            label: select,
            data: data.map { |d| d.round(2) },
            backgroundColor: backgroundColor,
            borderColor: SaleTrend::COLORS[i],
            borderWidth: 1
        }
      end

      hash_a = {
          labels: labels,
          datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
          labels: labels,
          datasets: [datasets[1], datasets[4], datasets[3]]
      }
      [hash_a, hash_b]
    end


    def return_money_forecast_month(labels, selects, staff_id = nil, property)
      plan_return = plan_return(staff_id, labels, "month", property)
      actual_return = actual_return(staff_id, labels, "month", property)
      plan_return_by_o = plan_return_by_o(staff_id, labels, "month", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == RETURN_NAMES[0]
                 labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_return[[year, month]]&.pluck(:plan_return_money)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == RETURN_NAMES[1]
                 arr = labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_return[[year, month]]&.pluck(:plan_return_money)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[2]
                 labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_return[[year, month]]&.pluck(:amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
               elsif select == RETURN_NAMES[3]
                 arr = labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   actual_return[[year, month]]&.pluck(:amount)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[4]
                 arr = labels.map do |d|
                   month = d.split("-").last.to_i
                   year = d.split("-").first.to_i
                   plan_return_by_o[[year, month]]&.pluck(:plan_return_money)&.reject(&:blank?)&.sum.to_f.round(3)
                 end
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end
        type = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                 'bar'
               else
                 "line"
               end
        backgroundColor = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                            SaleTrend::COLORS[i]
                          else
                            'rgba(54, 162, 235, 0.2)'
                          end
        {
            type: type,
            label: select,
            data: data.map { |d| d.round(2) },
            backgroundColor: backgroundColor,
            borderColor: SaleTrend::COLORS[i],
            borderWidth: 1
        }
      end

      hash_a = {
          labels: labels,
          datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
          labels: labels,
          datasets: [datasets[1], datasets[4], datasets[3]]
      }
      [hash_a, hash_b]
    end

    def return_money_forecast_year(labels, selects, staff_id = nil, property)
      plan_return = plan_return(staff_id, labels, "year", property)
      actual_return = actual_return(staff_id, labels, "year", property)
      plan_return_by_o = plan_return_by_o(staff_id, labels, "year", property)
      datasets = selects.map.with_index do |select, i|
        data = if select == RETURN_NAMES[0]
                 labels.map { |d| plan_return[d.to_i] || 0 }
               elsif select == RETURN_NAMES[1]
                 arr = labels.map { |d| plan_return[d.to_i] || 0 }
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[2]
                 labels.map { |d| actual_return[d.to_i] || 0 }
               elsif select == RETURN_NAMES[3]
                 arr = labels.map { |d| actual_return[d.to_i] || 0 }
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               elsif select == RETURN_NAMES[4]
                 arr = labels.map { |d| plan_return_by_o[d.to_i] || 0 }
                 arr.map.with_index(1) { |num, i| arr.first(i).inject(:+) }
               end

        type = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                 'bar'
               else
                 "line"
               end
        backgroundColor = if select == RETURN_NAMES[0] || select == RETURN_NAMES[2]
                            SaleTrend::COLORS[i]
                          else
                            'rgba(54, 162, 235, 0.2)'
                          end
        {
            type: type,
            label: select,
            data: data.map { |d| d.round(2) },
            backgroundColor: backgroundColor,
            borderColor: SaleTrend::COLORS[i],
            borderWidth: 1
        }
      end

      hash_a = {
          labels: labels,
          datasets: [datasets[0], datasets[2]]
      }
      hash_b = {
          labels: labels,
          datasets: [datasets[1], datasets[4], datasets[3]]
      }
      [hash_a, hash_b]
    end

    private

    def plan_get(staff_id, time_range, type, property)
      # budget_amount 预算额
      start_time = case type
                   when "month"
                     time_range.first + "-01"
                   when "year"
                     time_range.first + "-01-01"
                   when "week"
                     time_range.first.split("-").first + "-01-01"
                   when "quarter"
                     time_range.first.split("-").first + "-01-01"
                   end
      end_time = case type
                 when "month"
                   time_range.last + "-31"
                 when "year"
                   time_range.last + "-12-31"
                 when "week"
                   time_range.last.split("-").first + "-12-31"
                 when "quarter"
                   time_range.last.split("-").first + "-12-31"
                 end
      staff_id = staff_id.present? ? Array(staff_id) : nil
      data = Business.joins(:last_follow_up)
                     .where("educode_sales_follow_ups.invitation_at >= ? and educode_sales_follow_ups.invitation_at <= ?", start_time, end_time)
      if staff_id.present?
        data = data.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").
          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 = ?)", staff_id, staff_id)
      end
      if property.present?
        data = data.joins(department: [school: :school_tags]).where("school_tags.id = ?", property)
      end
      case type
      when "week"
        data.select("educode_sales_follow_ups.*, yearweek(educode_sales_follow_ups.invitation_at) as week").group("yearweek(educode_sales_follow_ups.invitation_at)").sum("educode_sales_follow_ups.budget_amount")
      when "quarter"
        data = data.select("educode_sales_follow_ups.*, quarter(educode_sales_follow_ups.invitation_at) as quarter, year(educode_sales_follow_ups.invitation_at) as year")
        data.group_by { |d| [d.year, d.quarter] }
      when "month"
        data = data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.invitation_at) as year, month(educode_sales_follow_ups.invitation_at) as month")
        data.group_by { |d| [d.year, d.month] }
      when "year"
        data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.invitation_at) as year").group("year(educode_sales_follow_ups.invitation_at)").sum("educode_sales_follow_ups.budget_amount")
      end
    end

    def actual_get(staff_id, time_range, type, property)
      start_time = case type
                   when "month"
                     time_range.first + "-01"
                   when "year"
                     time_range.first + "-01-01"
                   when "week"
                     time_range.first.split("-").first + "-01-01"
                   when "quarter"
                     time_range.first.split("-").first + "-01-01"
                   end
      end_time = case type
                 when "month"
                   time_range.last + "-31"
                 when "year"
                   time_range.last + "-12-31"
                 when "week"
                   time_range.last.split("-").first + "-12-31"
                 when "quarter"
                   time_range.last.split("-").first + "-12-31"
                 end
      # actual_amount 合同额
      staff_id = staff_id.present? ? Array(staff_id) : nil
      data = Business.joins(:last_follow_up)
                     .where("educode_sales_follow_ups.bidded_date >= ? and educode_sales_follow_ups.bidded_date <= ? ", start_time, end_time)
      if staff_id.present?
        data = data.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").
          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 = ?)", staff_id, staff_id)
      end
      if property.present?
        data = data.joins(department: [school: :school_tags]).where("school_tags.id = ?", property)
      end
      case type
      when "week"
        data.select("educode_sales_follow_ups.*, yearweek(educode_sales_follow_ups.bidded_date) as week").group("yearweek(educode_sales_follow_ups.bidded_date)").sum("educode_sales_follow_ups.actual_amount")
      when "quarter"
        data = data.select("educode_sales_follow_ups.*, quarter(educode_sales_follow_ups.bidded_date) as quarter, year(educode_sales_follow_ups.bidded_date) as year")
        data.group_by { |d| [d.year, d.quarter] }
      when "month"
        data = data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.bidded_date) as year, month(educode_sales_follow_ups.bidded_date) as month")
        data.group_by { |d| [d.year, d.month] }
      when "year"
        data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.bidded_date) as year").group("year(educode_sales_follow_ups.bidded_date)").sum("educode_sales_follow_ups.actual_amount")
      end
    end

    def plan_return(staff_id, time_range, type, property)
      # plan_return_money 计划回款额
      start_time = case type
                   when "month"
                     time_range.first + "-01"
                   when "year"
                     time_range.first + "-01-01"
                   when "week"
                     time_range.first.split("-").first + "-01-01"
                   when "quarter"
                     time_range.first.split("-").first + "-01-01"
                   end
      end_time = case type
                 when "month"
                   time_range.last + "-31"
                 when "year"
                   time_range.last + "-12-31"
                 when "week"
                   time_range.last.split("-").first + "-12-31"
                 when "quarter"
                   time_range.last.split("-").first + "-12-31"
                 end
      staff_id = staff_id.present? ? Array(staff_id) : nil
      data = Business.joins(:last_follow_up).where("educode_sales_businesses.deleted_at is null")
                 .where("educode_sales_follow_ups.plan_return_date >= ? and educode_sales_follow_ups.plan_return_date <= ?", start_time, end_time)
      if staff_id.present?
        data = data.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").
            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 = ?)", staff_id, staff_id)
      end
      if property.present?
        data = data.joins(department: [school: :school_tags]).where("school_tags.id = ?", property)
      end
      case type
      when "week"
        data.select("educode_sales_follow_ups.*, yearweek(educode_sales_follow_ups.plan_return_date) as week").group("yearweek(educode_sales_follow_ups.plan_return_date)").sum("educode_sales_follow_ups.plan_return_money")
      when "quarter"
        data = data.select("educode_sales_follow_ups.*, quarter(educode_sales_follow_ups.plan_return_date) as quarter, year(educode_sales_follow_ups.invitation_at) as year")
        data.group_by { |d| [d.year, d.quarter] }
      when "month"
        data = data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.plan_return_date) as year, month(educode_sales_follow_ups.plan_return_date) as month")
        data.group_by { |d| [d.year, d.month] }
      when "year"
        data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.plan_return_date) as year").group("year(educode_sales_follow_ups.plan_return_date)").sum("educode_sales_follow_ups.plan_return_money")
      end
    end


    def actual_return(staff_id, time_range, type, property)
      # money_plan_records表中 amount: 回款金额
      start_time = case type
                   when "month"
                     time_range.first + "-01"
                   when "year"
                     time_range.first + "-01-01"
                   when "week"
                     time_range.first.split("-").first + "-01-01"
                   when "quarter"
                     time_range.first.split("-").first + "-01-01"
                   end
      end_time = case type
                 when "month"
                   time_range.last + "-31"
                 when "year"
                   time_range.last + "-12-31"
                 when "week"
                   time_range.last.split("-").first + "-12-31"
                 when "quarter"
                   time_range.last.split("-").first + "-12-31"
                 end
      staff_id = staff_id.present? ? Array(staff_id) : nil
      data = MoneyPlanRecord.where("educode_sales_money_plan_records.date_at >= ? and educode_sales_money_plan_records.date_at <= ?", start_time, end_time)
      if staff_id.present?
        data = data.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").
            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 = ?)", staff_id, staff_id)
      end
      if property.present?
        data = data.joins(business: [department: [school: :school_tags]]).where("school_tags.id = ?", property)
      end
      case type
      when "week"
        data.select("educode_sales_money_plan_records.*, yearweek(educode_sales_money_plan_records.date_at) as week").group("yearweek(educode_sales_money_plan_records.date_at)").sum("educode_sales_money_plan_records.amount")
      when "quarter"
        data = data.select("educode_sales_money_plan_records.*, quarter(educode_sales_money_plan_records.date_at) as quarter, year(educode_sales_money_plan_records.date_at) as year")
        data.group_by { |d| [d.year, d.quarter] }
      when "month"
        data = data.select("educode_sales_money_plan_records.*, year(educode_sales_money_plan_records.date_at) as year, month(educode_sales_money_plan_records.date_at) as month")
        data.group_by { |d| [d.year, d.month] }
      when "year"
        data.select("educode_sales_money_plan_records.*, year(educode_sales_money_plan_records.date_at) as year").group("year(educode_sales_money_plan_records.date_at)").sum("educode_sales_money_plan_records.amount")
      end
    end

    def plan_return_by_o(staff_id, time_range, type, property)
      # plan_return_money 计划回款额-o类
      clazz_id =  Common.find_by(name: "O类-中标商机", clazz: "business_type", extras: "o_class").id
      start_time = case type
                   when "month"
                     time_range.first + "-01"
                   when "year"
                     time_range.first + "-01-01"
                   when "week"
                     time_range.first.split("-").first + "-01-01"
                   when "quarter"
                     time_range.first.split("-").first + "-01-01"
                   end
      end_time = case type
                 when "month"
                   time_range.last + "-31"
                 when "year"
                   time_range.last + "-12-31"
                 when "week"
                   time_range.last.split("-").first + "-12-31"
                 when "quarter"
                   time_range.last.split("-").first + "-12-31"
                 end
      staff_id = staff_id.present? ? Array(staff_id) : nil
      data = Business.joins(:last_follow_up).where("educode_sales_businesses.deleted_at is null").where("educode_sales_businesses.clazz_id = ?", clazz_id)
                 .where("educode_sales_follow_ups.plan_return_date >= ? and educode_sales_follow_ups.plan_return_date <= ?", start_time, end_time)
      if staff_id.present?
        data = data.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").
            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 = ?)", staff_id, staff_id)
      end
      if property.present?
        data = data.joins(department: [school: :school_tags]).where("school_tags.id = ?", property)
      end
      case type
      when "week"
        data.select("educode_sales_follow_ups.*, yearweek(educode_sales_follow_ups.plan_return_date) as week").group("yearweek(educode_sales_follow_ups.plan_return_date)").sum("educode_sales_follow_ups.plan_return_money")
      when "quarter"
        data = data.select("educode_sales_follow_ups.*, quarter(educode_sales_follow_ups.plan_return_date) as quarter, year(educode_sales_follow_ups.invitation_at) as year")
        data.group_by { |d| [d.year, d.quarter] }
      when "month"
        data = data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.plan_return_date) as year, month(educode_sales_follow_ups.plan_return_date) as month")
        data.group_by { |d| [d.year, d.month] }
      when "year"
        data.select("educode_sales_follow_ups.*, year(educode_sales_follow_ups.plan_return_date) as year").group("year(educode_sales_follow_ups.plan_return_date)").sum("educode_sales_follow_ups.plan_return_money")
      end
    end
  end
end