require_dependency "educode_sales/application_controller"

module EducodeSales
  class SaleTrendsController < ApplicationController
    # authorize_resource class: false

    def trends
      # authorize! :trends,  EducodeSales::SaleTrend
      @year = params[:year] ? params[:year] : Time.now.year
      @years = ['全部'] + (1..(Time.now.year - 2014)).reverse_each.map { |d| 2014 + d }
      x = Common.find_by(extras: EducodeSales::Common::XTYPE)&.id
      o = Common.find_by(extras: EducodeSales::Common::OTYPE)&.id
      if (@year == '全部')
        @business_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ? AND educode_sales_follow_ups.clazz_id != ?", x, o).sum(:total_amount).round(2)
        stage_ids = Common.where(clazz: '商机阶段', name: ['已中标', '已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id)
        # 中标总金额， 已回款总金额
        total_return = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.stage_id IN (?)", stage_ids).select("sum(total_amount) AS total, sum(educode_sales_businesses.return_money) AS return_money")
        @goal_amount = total_return[0]['total']&.round(2) || 0

        @actual_goal_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.stage_id IN (?)", stage_ids).sum(:actual_amount).round(2)
        s_stage_ids = Common.where(clazz: '商机阶段', name: ['已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id)
        @service_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.stage_id IN (?)", s_stage_ids).sum(:total_amount).round(2)
        @actual_service_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.stage_id IN (?)", s_stage_ids).sum(:actual_amount).round(2)

        @return_amount = Business.joins(last_follow_up: :money_plans).where("educode_sales_follow_ups.clazz_id != ?", x).where.not("educode_sales_money_plans.clazz!= ?", 1).sum(:amount).round(2)
        # # @return_amount = total_return[0]['return_money']&.round(2) || 0
        # @receivable_amount = @goal_amount - @return_amount

        @receivable_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.stage_id IN (?)", stage_ids).select("(SUM(educode_sales_follow_ups.actual_amount) - SUM(educode_sales_businesses.return_money)) AS num")
        @receivable_amount = @receivable_amount.present? && @receivable_amount[0]['num'] ? @receivable_amount[0]['num'].round(2) : 0

        a = Common.where(clazz: '商机类型', name: Common.find_by(extras: EducodeSales::Common::ATYPE)&.name).pluck(:id)
        @a_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id IN (?)", a).sum(:total_amount).round(2)
        b = Common.where(clazz: '商机类型', name: Common.find_by(extras: EducodeSales::Common::BTYPE)&.name).pluck(:id)
        @b_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id IN (?)", b).sum(:total_amount).round(2)
      else
        year_time = "#{@year}-01-01 00:00:00"
        year_over_time = "#{@year}-12-31 23:59:59"

        @sale_trend = SaleTrend.find_or_create_by(year: @year)
        @business_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ? AND educode_sales_follow_ups.clazz_id != ?", x, o).where("educode_sales_follow_ups.year <= ?", @year).sum(:total_amount).round(2)
        stage_ids = Common.where(clazz: '商机阶段', name: ['已中标', '已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id)

        # 中标总金额， 已回款总金额
        total_return = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.bidded_date >= ? AND educode_sales_follow_ups.bidded_date <= ? AND educode_sales_follow_ups.stage_id IN (?)", "#{@year}-01-01", "#{@year}-12-31", stage_ids).select("sum(total_amount) AS total, sum(educode_sales_businesses.return_money) AS return_money")
        @goal_amount = total_return[0]['total']&.round(2) || 0

        @actual_goal_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.bidded_date >= ? AND educode_sales_follow_ups.bidded_date <= ? AND educode_sales_follow_ups.stage_id IN (?)", "#{@year}-01-01", "#{@year}-12-31", stage_ids).sum(:actual_amount).round(2)
        s_stage_ids = Common.where(clazz: '商机阶段', name: ['已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id)
        @service_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.signed_date >= ? AND educode_sales_follow_ups.signed_date <= ?  AND educode_sales_follow_ups.stage_id IN (?)", "#{@year}-01-01", "#{@year}-12-31", s_stage_ids).sum(:total_amount).round(2)
        @actual_service_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.signed_date >= ? AND educode_sales_follow_ups.signed_date <= ?  AND educode_sales_follow_ups.stage_id IN (?)", "#{@year}-01-01", "#{@year}-12-31", s_stage_ids).sum(:actual_amount).round(2)
        @return_amount = Business.joins(last_follow_up: :money_plans).where("educode_sales_follow_ups.clazz_id != ?", x).where.not("educode_sales_money_plans.clazz!= ?", 1).where("educode_sales_money_plans.date_at >= ?  AND educode_sales_money_plans.date_at <= ? ", year_time, year_over_time).sum(:amount).round(2)

        # 往年累计已中标合同
        # @former_goal_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.bidded_date <= ? AND educode_sales_follow_ups.stage_id IN (?)", "#{@year}-12-31", stage_ids).sum(:actual_amount).round(2)
        # # 往年累计已回款
        # @former_return_amount = Business.joins(last_follow_up: :money_plans).where("educode_sales_follow_ups.clazz_id != ?", x).where.not("educode_sales_money_plans.clazz!= ?", 1).where("educode_sales_money_plans.date_at <= ? ", year_over_time).sum(:amount).round(2)
        # @receivable_amount = @former_goal_amount - @former_return_amount

        @receivable_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).where("educode_sales_follow_ups.bidded_date <= ? AND educode_sales_follow_ups.stage_id IN (?)", "#{@year}-12-31", stage_ids).select("(SUM(educode_sales_follow_ups.actual_amount) - SUM(educode_sales_businesses.return_money)) AS num")
        @receivable_amount = @receivable_amount.present? && @receivable_amount[0]['num'] ? @receivable_amount[0]['num'].round(2) : 0

        a = Common.where(clazz: '商机类型', name: Common.find_by(extras: EducodeSales::Common::ATYPE)&.name).pluck(:id)
        @a_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id IN (?)", a).where("educode_sales_follow_ups.year <= ?", @year).sum(:total_amount).round(2)
        b = Common.where(clazz: '商机类型', name: Common.find_by(extras: EducodeSales::Common::BTYPE)&.name).pluck(:id)
        @b_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id IN (?)", b).where("educode_sales_follow_ups.year <= ?", @year).sum(:total_amount).round(2)
      end

    end

    def sale_trends
      sale_trend = SaleTrend.find_by(year: params[:year])
      if sale_trend.update(sale_trend_params)
        render_success
      else
        render_failure sale_trend
      end
    end

    def operations

    end

    # 商机跟进数
    def business_followup_analysis
      common = EducodeSales::Common.find_by(clazz: 'staff_type', name: '销售')
      names = EducodeSales::Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id, d.role_id] }
      x = EducodeSales::Common.find_by(extras: EducodeSales::Common::XTYPE)&.id
      x_business_ids = EducodeSales::Business.joins(last_follow_up: :clazz).where("educode_sales_commons.id = ?",x).pluck :id

      @follow_count_range = params[:follow_count_range] || "week"
      # 商机跟进数图表
      respond_to do |format|
        format.html do

        end
        format.js do
          default_dates = (30.day.ago.to_date..Date.today).map { |d| d.strftime("%Y-%W") }.uniq #默认时间范围
          @follow_count_data =  business_followup_charts(default_dates, names, x_business_ids, SaleTrend::COLORS, [30.day.ago.to_date.beginning_of_week, Date.today], "%Y%u") do |default_dates|
            default_dates.map { |date|
              d = date.split("-")
              year = d[0].to_i
              week = d[1].to_i
              if week == 0
                0
              else
                month = Date.commercial(year, week).strftime("%m月%d日")
                day = Date.commercial(year, week, 7).strftime("%m月%d日")
                date.to_s + "(#{month}-#{day})"
              end
            } - [0]
          end
        end
        format.json do
          if params[:follow_count_range].present?
            case params[:follow_count_range]
            when "week" #按周
              if params[:date_week].present?
                date = params[:date_week].split(" - ")
                # 年初第一天周数是0会导致周数重复计算
                dates = (date[0].to_date..date[1].to_date).map { |d|  d.strftime("%Y-%W") }.uniq.select { |d| d.split("-")[1] != '00' }
                @follow_count_data =  business_followup_charts(dates, names, x_business_ids, SaleTrend::COLORS, date, "%Y%u") do |dates|
                  dates.map { |date|
                    d = date.split("-")
                    year = d[0].to_i
                    week = d[1].to_i
                    if week == 0
                      0
                    else
                      month = Date.commercial(year, week).strftime("%m月%d日")
                      day = Date.commercial(year, week, 7).strftime("%m月%d日")
                      date.to_s + "(#{month}-#{day})"
                    end
                  } - [0]
                end
              else
                return render json: {msg: '请选择时间范围', success: false }
              end
            when "month" #按月
              if params[:date_month].present?
                date = params[:date_month].split(" - ")
                dates = ((date[0] + "-01").to_date..(date[1] + "-01").to_date).map { |d| d.strftime("%Y-%m") }.uniq
                @follow_count_data = business_followup_charts(dates, names, x_business_ids, SaleTrend::COLORS, [(date[0] + "-01").to_date, (date[1] + "-01").to_date.end_of_month], '%Y%m')
              else
                return render json: {msg: '请选择时间范围', success: false }
              end
            when "year" #按年
              if params[:date_year].present?
                date = params[:date_year].split(" - ")
                dates = (date[0]..date[1]).to_a.map { |d| d.to_s}
                @follow_count_data = business_followup_charts(dates, names, x_business_ids, SaleTrend::COLORS, ["#{date[0]}-01-01".to_date.beginning_of_year, "#{date[1]}-01-01".to_date.end_of_year], '%Y')
              else
                return render json: {msg: '请选择时间范围', success: false }
              end
            else
              #按天
              if params[:date].present?
                date = params[:date].split(" - ")
                dates = (date[0].to_date..date[1].to_date).to_a.map { |d| d.to_s}
                @follow_count_data = business_followup_charts(dates, names, x_business_ids, SaleTrend::COLORS, [date[0].to_date, date[1].to_date], '%Y%m%d')
              else
                return render json: {msg: '请选择时间范围', success: false }
              end
            end
          end
          render json: {data: @follow_count_data }
        end
      end

    end

    # 销售额分析
    def sales_analysis
      respond_to do |format|
        format.html do

        end
        format.js do
          x = EducodeSales::Common.find_by(extras: EducodeSales::Common::XTYPE)&.id
          count_type = params[:count_type] || "actual_amount"
          stage_ids = Common.where(clazz: '商机阶段', name: ['已中标', '已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id)
          s_stage_ids = Common.where(clazz: '商机阶段', name: ['已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id)
          @goal_count_range = params[:goal_count_range] || "month"

          goal_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y-%m") }.uniq
          sale_names = ['已中标', '已签单', '已回款']

          if params[:goal_count_range].present?
            case params[:goal_count_range]
            when "week"
            when "month" #按月
              if params[:goal_date_month].present?
                date = params[:goal_date_month].split(" - ")
                date[0] = (date[0] + "-01").to_date.to_s
                date[1] = (date[1] + "-01").to_date.end_of_month.to_s

                dates = ((date[0] + "-01").to_date..(date[1] + "-01").to_date).map { |d| d.strftime("%Y-%m") }.uniq
                @goal_count_data = month_sale_chart(dates, sale_names, SaleTrend::COLORS, x, stage_ids, s_stage_ids, count_type, date)
              else
                @goal_count_data = month_sale_chart(goal_default_dates, sale_names, SaleTrend::COLORS, x, stage_ids, s_stage_ids, count_type, ["#{Time.now.year}-01", "#{Time.now.year}-#{Time.now.month}"])
              end
            else
              #按年
              if params[:goal_date_year].present?
                date = params[:goal_date_year].split(" - ")
                dates = (date[0]..date[1]).to_a
                @goal_count_data = year_sale_chart(dates, sale_names, SaleTrend::COLORS, x, stage_ids, s_stage_ids, count_type, date)
              else
                goal_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y") }.uniq
                @goal_count_data = year_sale_chart(goal_default_dates, sale_names, SaleTrend::COLORS, x, stage_ids, s_stage_ids, count_type, ["#{Time.now.year}", "#{Time.now.year}"])
              end
            end
          else
            @goal_count_data = month_sale_chart(goal_default_dates, sale_names, SaleTrend::COLORS, x, stage_ids, s_stage_ids, count_type, ["#{Time.now.year}-01", "#{Time.now.year}-#{Time.now.month}"])
          end
        end
      end
    end

    # 商机区域分布
    def business_area
      respond_to do |format|
        format.html do
        end
        format.js do
          @business_types = EducodeSales::Common.where(clazz: 'business_type').where.not(extras: "x_class").map { |d| {name: d.name, value: d.id } }
          @select_business_type = params[:business_type].present? ?  params[:business_type].split(",") : []
          x = EducodeSales::Common.find_by(extras: EducodeSales::Common::XTYPE)&.id
          o = Common.find_by(extras: EducodeSales::Common::OTYPE)&.id
          provinces = EducodeSales::Common.where(clazz: 'area').pluck(:name)
          if params[:business_count_type] == 'money' || params[:business_count_type].blank?
            data = provinces.map { |province|
              if params[:business_type].blank?
                Business.joins(:last_follow_up).where("educode_sales_businesses.department_id in (?)", School.joins(:departments).where(province: province).pluck("departments.id")).where("educode_sales_follow_ups.clazz_id != ? AND educode_sales_follow_ups.clazz_id != ?", x, o).sum(:total_amount).round(2)
              else
                Business.joins(:last_follow_up).where("educode_sales_businesses.department_id in (?)", School.joins(:departments).where(province: province).pluck("departments.id")).where("educode_sales_follow_ups.clazz_id in (?)", params[:business_type].split(",")).sum(:total_amount).round(2)
              end
            }
            if params[:sort_by].present?
              data_hash = []
              data.each_with_index do |d, i|
                data_hash << {id: i, num: d}
              end

              data_hash.sort! { |a, b| params[:sort_by] == 'asc' ?  a[:num] <=> b[:num]  : b[:num] <=> a[:num] }
              data = []
              provinces_sort = []
              data_hash.each_with_index do |d|
                data << d[:num]
                provinces_sort << provinces[d[:id]]
              end
              provinces = provinces_sort
            end
            @business_data = {
              labels: provinces,
              datasets: [
                {
                  label: "商机总额",
                  data: data,
                  backgroundColor: SaleTrend::COLORS[0],
                  borderColor: SaleTrend::COLORS[0],
                  borderWidth: 1
                }
              ]
            }
          else
            data = provinces.map { |province|
              if params[:business_type].blank?
                Business.joins(:last_follow_up).where("educode_sales_businesses.department_id in (?)", School.joins(:departments).where(province: province).pluck("departments.id")).where("educode_sales_follow_ups.clazz_id != ? AND educode_sales_follow_ups.clazz_id != ?", x, o).count
              else
                Business.joins(:last_follow_up).where("educode_sales_businesses.department_id in (?)", School.joins(:departments).where(province: province).pluck("departments.id")).where("educode_sales_follow_ups.clazz_id in (?)", params[:business_type].split(",")).count
              end
            }
            if params[:sort_by].present?
              data_hash = []
              data.each_Wit_index do |d, i|
                data_hash << {id: i, num: d}
              end

              data_hash.sort! { |a, b| params[:sort_by] == 'asc' ?  a[:num] <=> b[:num]  : b[:num] <=> a[:num] }
              data = []
              provinces_sort = []
              data_hash.each_with_index do |d|
                data << d[:num]
                provinces_sort << provinces[d[:id]]
              end
              provinces = provinces_sort
            end
            @business_data = {
              labels: provinces,
              datasets: [
                {
                  label: "商机数量",
                  data: data,
                  backgroundColor: SaleTrend::COLORS[0],
                  borderColor: SaleTrend::COLORS[0],
                  borderWidth: 1
                }
              ]
            }
          end
        end
      end
    end

    # 销售人员跟进分析
    def sales_followup_analysis
      respond_to do |format|
        format.html do

        end
        format.js do
          #  销售人员跟进分析
          @goal_count_range = params[:goal_count_range] || "month"
          begin_time = Time.now.at_beginning_of_year.to_s
          end_time = Time.now.at_end_of_year.to_s
          customer_time_range = params[:customer_time_range]
          case customer_time_range
          when 'last_week'
            begin_time = (Time.now - 1.week).at_beginning_of_week.to_s
            end_time = (Time.now - 1.week).at_end_of_week.to_s
          when 'this_week'
            begin_time = Time.now.at_beginning_of_week.to_s
            end_time = Time.now.at_end_of_week.to_s
          when 'last_month'
            begin_time = (Time.now - 1.month).at_beginning_of_month.to_s
            end_time = (Time.now - 1.month).at_end_of_month.to_s
          when 'this_month'
            begin_time = Time.now.at_beginning_of_month.to_s
            end_time = Time.now.at_end_of_month.to_s
          when 'last_year'
            begin_time = (Time.now - 1.year).at_beginning_of_year.to_s
            end_time = (Time.now - 1.year).at_end_of_year.to_s
          when 'this_year'
            begin_time = Time.now.at_beginning_of_year.to_s
            end_time = Time.now.at_end_of_year.to_s
          when 'all'
            begin_time = (Time.now - 5.year).at_beginning_of_year.to_s
            end_time = (Time.now + 5.year).at_end_of_year.to_s
          when 'diy'
            if params[:customer_date].present?
              date = params[:customer_date].split(" - ")
              begin_time = date[0] + " 00:00:00"
              end_time = date[1] + " 23:59:59"
            end
          end
          common = Common.find_by(clazz: 'staff_type', name: '销售')
          staff_names = Staff.joins(:user).where(job_type: common.id).map { |d| d.user.real_name }
          staff_ids = Staff.joins(:user).where(job_type: common.id).pluck(:id)
          if params[:customer_count_type] == 'money' || params[:customer_count_type].blank?
            @customer_data = {
              labels: staff_names,
              datasets: [
                {
                  label: "跟进客户数",
                  data: staff_ids.map { |staff_id|
                    (EducodeSales::CustomerFollow.where(staff_id: staff_id).where("created_at >= ? AND created_at <= ?", begin_time, end_time).pluck(:school_id) + EducodeSales::Business.where(id: EducodeSales::FollowUp.where(staff_id: staff_id).where("created_at >= ? AND created_at <= ?", begin_time, end_time).pluck(:business_id).uniq).pluck(:school_id)).uniq.size
                  },
                  backgroundColor: SaleTrend::COLORS[0],
                  borderColor: SaleTrend::COLORS[0],
                  borderWidth: 1
                }
              ]
            }
          else
            @customer_data = {
              labels: staff_names,
              datasets: [
                {
                  label: "跟进客户次数",
                  data: staff_ids.map { |staff_id|
                    EducodeSales::CustomerFollow.where(staff_id: staff_id).where("created_at >= ? AND created_at <= ?", begin_time, end_time).size + EducodeSales::FollowUp.where(staff_id: staff_id).where("created_at >= ? AND created_at <= ?", begin_time, end_time).size
                  },
                  backgroundColor: SaleTrend::COLORS[0],
                  borderColor: SaleTrend::COLORS[0],
                  borderWidth: 1
                }
              ]
            }
          end
        end
      end
    end

    private

    def sale_trend_params
      params.permit(:chance_money, :ballot_money, :sign_money, :returned_money)
    end

    def business_followup_charts(dates, names, x_business_ids, colors, date, clazz)
      not_business = x_business_ids.present? ? x_business_ids.join(',') : ''
      staff_ids = names.present? ? names.map { |d| d[1]}.join(",") : ''

      sql = "SELECT DATE_FORMAT(created_at, '#{clazz}') as week, count(*) as counts, staff_id
             FROM educode_sales_follow_ups
             WHERE date(created_at) >= '#{date[0]}' and date(created_at) <= '#{date[1]}'
             AND business_id NOT IN (#{not_business}) and educode_sales_follow_ups.deleted_at IS NULL
             AND staff_id IN (#{staff_ids})
             GROUP BY DATE_FORMAT(created_at, '#{clazz}'), staff_id"
      data = EducodeSales::FollowUp.find_by_sql(sql).group_by { |d| "#{d['staff_id']}-#{d['week']}" }
      {
        labels: block_given? ? yield(dates) : dates,
        datasets: names.map.with_index do |name, i|
          {
            label: name[0],
            hidden: name[2] == 11, #role_id:11 离职角色
            data: dates.map { |date| data["#{name[1]}-#{ date.gsub("-", '')}"]&.[](0)&.[]('counts') || 0 },
            backgroundColor: "#fff",
            pointBorderColor: colors[i % 50 + 1],
            borderColor: colors[i % 50 + 1],
            pointBackgroundColor: colors[i % 50 + 1],
            borderWidth: 2
          }
        end
      }
    end


    def month_sale_chart(dates, names, colors, x, stage_ids, s_stage_ids, count_type, range)
      begin_at = range[0]
      end_at = range[1]

      data_1 = EducodeSales::Business.joins(:last_follow_up).
        where("educode_sales_follow_ups.clazz_id != ?", x).
        where("educode_sales_follow_ups.stage_id IN (?)", stage_ids).
        where("educode_sales_follow_ups.bidded_date >= ? AND educode_sales_follow_ups.bidded_date <= ?", begin_at, end_at).
        select("SUM(#{count_type}) AS count_type, DATE_FORMAT(bidded_date, '%Y-%m') AS month").
        group("DATE_FORMAT(bidded_date, '%Y-%m')").
        order("DATE_FORMAT(bidded_date, '%Y-%m')")
      data_1_hash = {}
      data_1_total_hash = {}
      data_1.each do |d|
        data_1_hash[d.month] = d.count_type.round(2)
      end

      last_month = 0
      last_sum = 0
      dates.each do |month|
        # 只累计当前年份
        if last_month != month.split("-")[0]
          last_sum = 0
        end
        data_1_total_hash[month] = ((data_1_hash[month] || 0) + last_sum).round(2)
        last_sum += data_1_hash[month] || 0
        last_month = month.split("-")[0]
      end

      data_2 = EducodeSales::Business.joins(:last_follow_up).
        where("educode_sales_follow_ups.clazz_id != ?", x).
        where("educode_sales_follow_ups.stage_id IN (?)", s_stage_ids).
        where("educode_sales_follow_ups.signed_date >= ? AND educode_sales_follow_ups.signed_date <= ?", begin_at, end_at).
        select("SUM(#{count_type}) AS count_type, DATE_FORMAT(signed_date, '%Y-%m') AS month").
        group("DATE_FORMAT(signed_date, '%Y-%m')").
        order("DATE_FORMAT(signed_date, '%Y-%m')")
      data_2_hash = {}
      data_2_total_hash = {}
      data_2.each do |d|
        data_2_hash[d.month] = d.count_type.round(2)
      end

      last_month = 0
      last_sum = 0
      dates.each do |month|
        # 只累计当前年份
        if last_month != month.split("-")[0]
          last_sum = 0
        end
        data_2_total_hash[month] = ((data_2_hash[month] || 0) + last_sum).round(2)
        last_sum += data_2_hash[month] || 0
        last_month = month.split("-")[0]
      end

      data_3 = EducodeSales::Business.joins(last_follow_up: :money_plans).
        where("educode_sales_follow_ups.clazz_id != ?", x).
        where.not("educode_sales_money_plans.clazz!= ?", 1).
        where("educode_sales_money_plans.date_at >= ? AND educode_sales_money_plans.date_at <= ?", begin_at, end_at).
        select("SUM(amount) AS count_type, DATE_FORMAT(date_at, '%Y-%m') AS month").
        group("DATE_FORMAT(date_at, '%Y-%m')").
        order("DATE_FORMAT(date_at, '%Y-%m')")
      data_3_hash = {}
      data_3.each do |d|
        data_3_hash[d.month] = d.count_type.round(2)
      end

      data_3_total_hash = {}
      last_month = 0
      last_sum = 0
      dates.each do |month|
        # 只累计当前年份
        if last_month != month.split("-")[0]
          last_sum = 0
        end
        data_3_total_hash[month] = ((data_3_hash[month] || 0) + last_sum).round(2)
        last_sum += data_3_hash[month] || 0
        last_month = month.split("-")[0]
      end


      {
        labels: dates,
        datasets: names.map.with_index do |name, i|
          {
            label: name,
            data: dates.map { |d|
              case i
              when 0
                data_1_hash[d] || 0
              when 1
                data_2_hash[d] || 0
              else
                data_3_hash[d] || 0
              end
            },
            data1: dates.map { |d|
              case i
              when 0
                data_1_total_hash[d] || 0
              when 1
                data_2_total_hash[d] || 0
              else
                data_3_total_hash[d] || 0
              end
            },
            backgroundColor: colors[i],
            borderColor: colors[i],
            borderWidth: 1
          }
        end
      }
    end

    def year_sale_chart(dates, names, colors, x, stage_ids, s_stage_ids, count_type, range)
      begin_at = range[0].split("-")
      end_at = range[1].split("-")
      begin_at = DateTime.new(begin_at[0].to_i).beginning_of_year.to_date.to_s
      end_at = DateTime.new(end_at[0].to_i).end_of_year.to_date.to_s

      data_1 = EducodeSales::Business.joins(:last_follow_up).
        where("educode_sales_follow_ups.clazz_id != ?", x).
        where("educode_sales_follow_ups.stage_id IN (?)", stage_ids).
        where("educode_sales_follow_ups.bidded_date >= ? AND educode_sales_follow_ups.bidded_date <= ?", begin_at, end_at).
        select("SUM(#{count_type}) AS count_type, DATE_FORMAT(bidded_date, '%Y') AS year").
        group("DATE_FORMAT(bidded_date, '%Y')").
        order("DATE_FORMAT(bidded_date, '%Y')")
      data_1_hash = {}
      data_1.each do |d|
        data_1_hash[d.year] = d.count_type.round(2)
      end

      data_1_total_hash = {}
      last_month = 0
      last_sum = 0
      dates.each do |month|
        # 只累计当前年份
        if last_month != month.split("-")[0]
          last_sum = 0
        end
        data_1_total_hash[month] = ((data_1_hash[month] || 0) + last_sum).round(2)
        last_sum += data_1_hash[month] || 0
        last_month = month.split("-")[0]
      end

      data_2 = EducodeSales::Business.joins(:last_follow_up).
        where("educode_sales_follow_ups.clazz_id != ?", x).
        where("educode_sales_follow_ups.stage_id IN (?)", s_stage_ids).
        where("educode_sales_follow_ups.signed_date >= ? AND educode_sales_follow_ups.signed_date <= ?", begin_at, end_at).
        select("SUM(#{count_type}) AS count_type, DATE_FORMAT(signed_date, '%Y') AS year").
        group("DATE_FORMAT(signed_date, '%Y')").
        order("DATE_FORMAT(signed_date, '%Y')")
      data_2_hash = {}
      data_2.each do |d|
        data_2_hash[d.year] = d.count_type.round(2)
      end

      data_2_total_hash = {}
      last_month = 0
      last_sum = 0
      dates.each do |month|
        # 只累计当前年份
        if last_month != month.split("-")[0]
          last_sum = 0
        end
        data_2_total_hash[month] = ((data_2_hash[month] || 0) + last_sum).round(2)
        last_sum += data_2_hash[month] || 0
        last_month = month.split("-")[0]
      end

      data_3 = EducodeSales::Business.joins(last_follow_up: :money_plans).
        where("educode_sales_follow_ups.clazz_id != ?", x).
        where.not("educode_sales_money_plans.clazz!= ?", 1).
        where("educode_sales_money_plans.date_at >= ? AND educode_sales_money_plans.date_at <= ?", begin_at, end_at).
        select("SUM(amount) AS count_type, DATE_FORMAT(date_at, '%Y') AS year").
        group("DATE_FORMAT(date_at, '%Y')").
        order("DATE_FORMAT(date_at, '%Y')")
      data_3_hash = {}
      data_3.each do |d|
        data_3_hash[d.year] = d.count_type.round(2)
      end

      data_3_total_hash = {}
      last_month = 0
      last_sum = 0
      dates.each do |month|
        # 只累计当前年份
        if last_month != month.split("-")[0]
          last_sum = 0
        end

        data_3_total_hash[month] = ((data_3_hash[month] || 0) + last_sum).round(2)
        last_sum += data_3_hash[month] || 0
        last_month = month.split("-")[0]
      end



      {
        labels: dates,
        datasets: names.map.with_index do |name, i|
          {
            label: name,
            data: dates.map { |d|
              case i
              when 0
                data_1_hash[d] || 0
              when 1
                data_2_hash[d] || 0
              else
                data_3_hash[d]|| 0
              end },
            data1: dates.map { |d|
              case i
              when 0
                data_1_total_hash[d] || 0
              when 1
                data_2_total_hash[d] || 0
              else
                data_3_total_hash[d] || 0
              end
            },
            backgroundColor: colors[i],
            borderColor: colors[i],
            borderWidth: 1
          }
        end
      }
    end

  end
end