require_dependency "educode_sales/application_controller" module EducodeSales class SubjectTrendsController < ApplicationController # authorize_resource class: false include SaleTrendsHelper include SaleTrendsCountHelper def trends @year = params[:year] ? params[:year] : Time.now.year @month = params[:month] ? params[:month] : Time.now.month @months = ['全部','1','2','3','4','5','6','7','8','9','10','11','12'] @years = ['全部'] + (1..(Time.now.year - 2014)).reverse_each.map { |d| 2014 + d } end def trendss # 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(:budget_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 = EducodeSales::MoneyPlanRecord.from("( SELECT distinct(educode_sales_money_plan_records.id), educode_sales_money_plan_records.amount FROM educode_sales_money_plan_records JOIN educode_sales_money_plan_claims ON educode_sales_money_plan_claims.money_plan_record_id = educode_sales_money_plan_records.id GROUP BY educode_sales_money_plan_records.id ) AS educode_sales_money_plan_records ").sum("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(:budget_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(:budget_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(:budget_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) @return_amount = EducodeSales::MoneyPlanRecord.from("( SELECT distinct(educode_sales_money_plan_records.id), educode_sales_money_plan_records.amount, date_at FROM educode_sales_money_plan_records JOIN educode_sales_money_plan_claims ON educode_sales_money_plan_claims.money_plan_record_id = educode_sales_money_plan_records.id GROUP BY educode_sales_money_plan_records.id ) AS educode_sales_money_plan_records ").where("educode_sales_money_plan_records.date_at >= ? AND educode_sales_money_plan_records.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(:budget_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(:budget_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 = ['已中标', '已签单', '已回款'] common = Common.find_by(clazz: 'staff_type', name: '销售') staffs = Staff.joins(:user).where(job_type: common.id).where.not(role_id: 11) @staffs = staffs.map { |d| [d.user.real_name, d.id]} 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(:budget_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(:budget_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 def goal_forecast respond_to do |format| format.html do end format.js do if params[:forecast_type].blank? || params[:forecast_type] == "money" p "按金额" goal_forecast_money else p "按数量" goal_forecast_count end end end end def user_stat respond_to do |format| format.html do end format.js do user_stat = EducodeSales::UserStatService.new gon.table_1 = user_stat.table_1 gon.table_2 = user_stat.table_2(1) gon.table_3 = user_stat.table_3 end end end #拜访分析 def visit_analysis sort_by = params[:sort_by].present? ? params[:sort_by] : "desc" user_name = params[:user_name] visit_type = params[:visit_type].present? ? params[:visit_type].split(",") : "" title_names = %w[本年拜访数 本季拜访数 本月拜访数 本周拜访数] respond_to do |format| format.js do @user_names = EducodeSales::Attendance.pluck(:name).uniq.map{|d| {name: d, value: d}} @visit_types = SaleTrend::VISIT_TYPE.map {|d| {name: d[0], value: d[1]}} sql = "SELECT name, count(*) as name_count FROM `educode_sales_attendances` WHERE (attendance_date >= '#{Time.now.beginning_of_year}' and attendance_date <= '#{Time.now.end_of_year}') group by name order by name_count desc limit 30" visit_count = EducodeSales::Attendance.find_by_sql(sql).map{|d| [d.name, d.name_count]} #年拜访量前30的用户名 top_thirty_username = visit_count.map{ |d| d[0]} @visit_count_data = visit_user_type_name(top_thirty_username, sort_by, visit_type, title_names) end format.json do if params[:user_type].present? && params[:user_type] == "name" if user_name.present? sql_by_name = "SELECT name, count(*) as name_count FROM `educode_sales_attendances` WHERE (attendance_date >= '#{Time.now.beginning_of_year}' and attendance_date <= '#{Time.now.end_of_year}') and name like '%#{user_name}%' group by name" else sql_by_name = "SELECT name, count(*) as name_count FROM `educode_sales_attendances` WHERE (attendance_date >= '#{Time.now.beginning_of_year}' and attendance_date <= '#{Time.now.end_of_year}') group by name order by name_count desc limit 30" end visit_count = EducodeSales::Attendance.find_by_sql(sql_by_name).map{|d| [d.name, d.name_count]} #年拜访量前30的用户名 top_thirty_username = visit_count.map{ |d| d[0]} @visit_count_data = visit_user_type_name(top_thirty_username, sort_by, visit_type, title_names) else if user_name.present? sql_by_customer = "SELECT name, customer, count(*) as customer_count FROM `educode_sales_attendances` WHERE (attendance_date >= '#{Time.now.beginning_of_year}' and attendance_date <= '#{Time.now.end_of_year}') and name like '%#{user_name}%' group by customer order by customer_count desc limit 30" else sql_by_customer = "SELECT name, customer, count(*) as customer_count FROM `educode_sales_attendances` WHERE (attendance_date >= '#{Time.now.beginning_of_year}' and attendance_date <= '#{Time.now.end_of_year}') group by customer order by customer_count desc limit 30" end visit_count = EducodeSales::Attendance.find_by_sql(sql_by_customer).map{|d| [d.customer, d.customer_count]} #年拜访量前30的用户名 top_thirty_customer = visit_count.map{ |d| d[0]} @visit_count_data = visit_user_type_customer(top_thirty_customer, sort_by, visit_type, title_names) end render json: {data: @visit_count_data } 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] business = EducodeSales::Business.joins(:last_follow_up) if params[:sales_staff_id].present? # 销售经理 business = business.joins("LEFT JOIN educode_sales_assign_follow_ups ON educode_sales_assign_follow_ups.follow_up_id = educode_sales_businesses.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 = ?)", params[:sales_staff_id], params[:sales_staff_id]) end if params[:property].present? # 客户类型 business = business.joins(department: [school: :school_tags]).where("school_tags.id = ?", params[:property]) end data_1 = business. 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 = business. 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 = 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 def goal_forecast_money property = params[:property] || nil staff_id = params[:staff_id] || nil @forecast_count_range = params[:forecast_count_range] || "month" forecast_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 = %w[计划投标额 计划投标额累计 实际中标额 实际中标额累计] case @forecast_count_range when "week" # 年初第一天周数是0会导致周数重复计算 if params[:forecast_date_day].present? date = params[:forecast_date_day].split(" - ") dates = (date[0].to_date..date[1].to_date).map { |d| d.strftime("%Y-%W") }.uniq.select { |d| d.split("-")[1] != '00' } @forecast_count_data = goal_forecast_week(dates, sale_names, staff_id, property) else forecast_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y-%W") }.uniq.select { |d| d.split("-")[1] != '00' } @forecast_count_data = goal_forecast_week(forecast_default_dates, sale_names, staff_id, property) end when "quarter" # 按年 if params[:forecast_date_year].present? date = params[:forecast_date_year].split(" - ") dates = (date[0]..date[1]).to_a dates = dates.map { |d| [d.to_s + "-" + "1", d.to_s + "-" + "2", d.to_s + "-" + "3", d.to_s + "-" + "4"] }.flatten @forecast_count_data = goal_forecast_quarter(dates, sale_names, staff_id, property) else forecast_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y") }.uniq forecast_default_dates = forecast_default_dates.map { |d| [d.to_s + "-" + "1", d.to_s + "-" + "2", d.to_s + "-" + "3", d.to_s + "-" + "4"] }.flatten @forecast_count_data = goal_forecast_quarter(forecast_default_dates, sale_names, staff_id, property) end when "month" # 按月 if params[:forecast_date_month].present? date = params[:forecast_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 @forecast_count_data = goal_forecast_month(dates, sale_names, staff_id, property) else @forecast_count_data = goal_forecast_month(forecast_default_dates, sale_names, staff_id, property) end else # 按年 if params[:forecast_date_year].present? date = params[:forecast_date_year].split(" - ") dates = (date[0]..date[1]).to_a @forecast_count_data = goal_forecast_year(dates, sale_names, staff_id, property) else forecast_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y") }.uniq @forecast_count_data = goal_forecast_year(forecast_default_dates, sale_names, staff_id, property) end end @forecast_count_data_0 = @forecast_count_data[0] @forecast_count_data_1 = @forecast_count_data[1] end def goal_forecast_count property = params[:property] || nil staff_id = params[:staff_id] || nil @forecast_count_range = params[:forecast_count_range] || "month" forecast_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 = %w[计划投标额 计划投标额累计 实际中标额 实际中标额累计] case @forecast_count_range when "week" # 年初第一天周数是0会导致周数重复计算 if params[:forecast_date_day].present? date = params[:forecast_date_day].split(" - ") dates = (date[0].to_date..date[1].to_date).map { |d| d.strftime("%Y-%W") }.uniq.select { |d| d.split("-")[1] != '00' } @forecast_count_data = goal_forecast_week_count(dates, sale_names, staff_id, property) else forecast_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y-%W") }.uniq.select { |d| d.split("-")[1] != '00' } @forecast_count_data = goal_forecast_week_count(forecast_default_dates, sale_names, staff_id, property) end when "quarter" # 按年 if params[:forecast_date_year].present? date = params[:forecast_date_year].split(" - ") dates = (date[0]..date[1]).to_a dates = dates.map { |d| [d.to_s + "-" + "1", d.to_s + "-" + "2", d.to_s + "-" + "3", d.to_s + "-" + "4"] }.flatten @forecast_count_data = goal_forecast_quarter_count(dates, sale_names, staff_id, property) else forecast_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y") }.uniq forecast_default_dates = forecast_default_dates.map { |d| [d.to_s + "-" + "1", d.to_s + "-" + "2", d.to_s + "-" + "3", d.to_s + "-" + "4"] }.flatten @forecast_count_data = goal_forecast_quarter_count(forecast_default_dates, sale_names, staff_id, property) end when "month" # 按月 if params[:forecast_date_month].present? date = params[:forecast_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 @forecast_count_data = goal_forecast_month_count(dates, sale_names, staff_id, property) else @forecast_count_data = goal_forecast_month_count(forecast_default_dates, sale_names, staff_id, property) end else # 按年 if params[:forecast_date_year].present? date = params[:forecast_date_year].split(" - ") dates = (date[0]..date[1]).to_a @forecast_count_data = goal_forecast_year_count(dates, sale_names, staff_id, property) else forecast_default_dates = ("#{Time.now.year}-01-01".to_date.."#{Time.now.year}-#{Time.now.month}-01".to_date).map { |d| d.strftime("%Y") }.uniq @forecast_count_data = goal_forecast_year_count(forecast_default_dates, sale_names, staff_id, property) end end @forecast_count_data_0 = @forecast_count_data[0] @forecast_count_data_1 = @forecast_count_data[1] end def visit_user_type_name(top_thirty_username, sort_by, visit_type, title_names) #本年拜访数 visit_count_year = EducodeSales::Attendance.where("name in (?)", top_thirty_username) .where(" attendance_date >= '#{Time.now.beginning_of_year}' and attendance_date <= '#{Time.now.end_of_year}' ") .group(:name).select("name, count(*) name_count").order("name_count #{sort_by}")&.map{ |d| [d.name, d.name_count]} #本季拜访数 current_year = Time.now.year current_month = Time.now.month start_month = (current_month - 1) / 3 * 3 + 1 end_month = start_month + 2 start_date = Date.new(current_year, start_month, 1).strftime("%Y-%m-%d") + " 00:00:00" end_date = Date.new(current_year, end_month, -1).strftime("%Y-%m-%d") + " 23:59:59" visit_count_season = EducodeSales::Attendance.where("name in (?)", top_thirty_username) .where("attendance_date >= '#{start_date}' and attendance_date <= '#{end_date}'") .group(:name).select("name, count(*) name_count").order("name_count #{sort_by}")&.map{ |d| [d.name, d.name_count]} #本月拜访数 visit_count_month = EducodeSales::Attendance.where("name in (?)", top_thirty_username) .where("attendance_date >= '#{Time.now.beginning_of_month}' and attendance_date <= '#{Time.now.end_of_month}'") .group(:name).select("name, count(*) name_count").order("name_count #{sort_by}")&.map{ |d| [d.name, d.name_count]} #本周拜访数 visit_count_week = EducodeSales::Attendance.where("name in (?)", top_thirty_username) .where("attendance_date >= '#{Time.now.beginning_of_week}' and attendance_date <= '#{Time.now.end_of_week}'") .group(:name).select("name, count(*) name_count").order("name_count #{sort_by}")&.map{ |d| [d.name, d.name_count]} visit_analysis_charts(visit_count_year, visit_count_season, visit_count_month, visit_count_week, visit_type, title_names) end def visit_user_type_customer(top_thirty_customer, sort_by, visit_type, title_names) #本年拜访数 visit_count_year = EducodeSales::Attendance.where("customer in (?)", top_thirty_customer) .where(" attendance_date >= '#{Time.now.beginning_of_year}' and attendance_date <= '#{Time.now.end_of_year}' ") .group(:customer).select("customer, count(*) customer_count").order("customer_count #{sort_by}")&.map{ |d| [d.customer, d.customer_count]} #本季拜访数 current_year = Time.now.year current_month = Time.now.month start_month = (current_month - 1) / 3 * 3 + 1 end_month = start_month + 2 start_date = Date.new(current_year, start_month, 1).strftime("%Y-%m-%d") + " 00:00:00" end_date = Date.new(current_year, end_month, -1).strftime("%Y-%m-%d") + " 23:59:59" visit_count_season = EducodeSales::Attendance.where("customer in (?)", top_thirty_customer) .where("attendance_date >= '#{start_date}' and attendance_date <= '#{end_date}'") .group(:customer).select("customer, count(*) customer_count").order("customer_count #{sort_by}")&.map{ |d| [d.customer, d.customer_count]} #本月拜访数 visit_count_month = EducodeSales::Attendance.where("customer in (?)", top_thirty_customer) .where("attendance_date >= '#{Time.now.beginning_of_month}' and attendance_date <= '#{Time.now.end_of_month}'") .group(:customer).select("customer, count(*) customer_count").order("customer_count #{sort_by}")&.map{ |d| [d.customer, d.customer_count]} #本周拜访数 visit_count_week = EducodeSales::Attendance.where("customer in (?)", top_thirty_customer) .where("attendance_date >= '#{Time.now.beginning_of_week}' and attendance_date <= '#{Time.now.end_of_week}'") .group(:customer).select("customer, count(*) customer_count").order("customer_count #{sort_by}")&.map{ |d| [d.customer, d.customer_count]} visit_analysis_charts(visit_count_year, visit_count_season, visit_count_month, visit_count_week, visit_type, title_names) end end end