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 } year_time = "#{@year}-01-01 00:00:00" year_over_time = "#{@year}-12-31 23:59:59" x = Common.find_by(extras: EducodeSales::Common::XTYPE)&.id o = Common.find_by(extras: EducodeSales::Common::OTYPE)&.id @sale_trend = SaleTrend.find_or_create_by(year: @year) @business_amount = Business.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id != ?", x).sum(:total_amount).round(2) stage_ids = Common.where(clazz: '商机阶段', name: ['已中标','已签单','已验收','回款中', '服务中','已结束']).pluck(:id) @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(:total_amount).round(2) @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) 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) count_type = params[:count_type] || "actual_amount" common = Common.find_by(clazz: 'staff_type', name: '销售') names = Staff.includes(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id] } x_business_ids = [] default_dates = (30.day.ago.to_date..Date.today).map { |d| d.strftime("%Y-%W")}.uniq #默认时间范围 Business.all.each do |d| if d.last_follow_up&.clazz&.id == x x_business_ids << d.id end end colors = ['#44D7B6','#4CACFF','#F7B500','#FF0000', '#FF7F00', '#FFFF00', '#00FF00', '#00FFFF', '#0000FF', '#8B00FF', 'rgba(255, 99, 132, 1)', 'rgba(54, 162, 235, 1)', 'rgba(255, 206, 86, 1)', 'rgba(75, 192, 192, 1)', 'rgba(153, 102, 255, 1)', 'rgba(255, 159, 64, 1)' ,'rgba(255, 0, 250, 1)'] @follow_count_range = params[:follow_count_range] || "week" @goal_count_range = params[:goal_count_range] || "month" # 商机跟进数图表 @follow_count_data = week_follow_count_chart(default_dates,names,x_business_ids,colors) if params[:follow_count_range].present? case params[:follow_count_range] when "week" #按周 if params[:date_week].present? date = params[:date_week].split(" - ") dates = (date[0].to_date..date[1].to_date).map { |d| d.strftime("%Y-%W")}.uniq @follow_count_data = week_follow_count_chart(dates,names,x_business_ids,colors) end when "month" #按月 if params[:date_month].present? # (Date.new(2020,12,1)..Date.new(2021,12,1)).map { |d| d.strftime("%Y-%m")}.uniq 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 = month_follow_count_chart(dates,names,x_business_ids,colors) end when "year" #按年 if params[:date_year].present? date = params[:date_year].split(" - ") dates = (date[0]..date[1]).to_a @follow_count_data = year_follow_count_chart(dates,names,x_business_ids,colors) end else #按天 if params[:date].present? date = params[:date].split(" - ") dates = (date[0].to_date..date[1].to_date).to_a @follow_count_data = day_follow_count_chart(dates,names,x_business_ids,colors) end end end #商机区域分布图表 provinces = EducodeSales::Common.where(clazz: 'area').pluck(:name) if params[:business_count_type] == 'money' || params[:business_count_type].blank? @business_data = { labels: provinces, datasets:[ { label: "商机总额", 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 != ?", x).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 = ?", params[:business_type]).sum(:total_amount).round(2) end }, backgroundColor: colors[0], borderColor: colors[0], borderWidth: 1 } ] } else @business_data = { labels: provinces, datasets:[ { label: "商机数量", 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 != ?", x).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 = ?", params[:business_type]).count end }, backgroundColor: colors[0], borderColor: colors[0], borderWidth: 1 } ] } end # 已中标图表 goal_default_dates = ("#{Time.now.year}-01".."#{Time.now.year}-#{Time.now.month}").to_a sale_names = ['已中标','已签单','已回款'] @goal_count_data = month_sale_chart(goal_default_dates,sale_names,colors,x,stage_ids,s_stage_ids,count_type) 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(" - ") 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,colors,x,stage_ids,s_stage_ids,count_type) 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,colors,x,stage_ids,s_stage_ids,count_type) end end 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 private def sale_trend_params params.permit(:chance_money, :ballot_money, :sign_money, :returned_money) end def day_follow_count_chart(dates,names,x_business_ids,colors) { labels: dates, datasets:names.map.with_index do |name,i| { # hidden: i < 3 ? false : true, label: name[0], data: dates.map{ |d| @follow_up = FollowUp.where(staff_id: name[1], created_at: d.beginning_of_day..d.end_of_day).where.not(business: x_business_ids).count }, backgroundColor: "#fff", pointBorderColor: colors[i%15+1], borderColor: colors[i%15+1], pointBackgroundColor: colors[i%15+1], borderWidth: 2 } end } end def week_follow_count_chart(dates,names,x_business_ids,colors) { labels: 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], datasets:names.map.with_index do |name,i| { # hidden: i < 3 ? false : true, label: name[0], data: dates.map{ |d| d = d.split("-") year = d[0].to_i week = d[1].to_i if week == 0 @follow_up = 999999 else @follow_up = FollowUp.where(staff_id: name[1]).where("created_at >= ? AND created_at <= ?", Date.commercial(year,week).strftime("%Y-%m-%d"), Date.commercial(year,week,7).strftime("%Y-%m-%d")).where.not(business: x_business_ids).count end } - [999999], backgroundColor: "#fff", pointBorderColor: colors[i%15+1], borderColor: colors[i%15+1], pointBackgroundColor: colors[i%15+1], borderWidth: 2 } end } end def month_follow_count_chart(dates,names,x_business_ids,colors) { labels: dates, datasets:names.map.with_index do |name,i| { # hidden: i < 3 ? false : true, label: name[0], data: dates.map { |d| @follow_up = FollowUp.where( staff_id: name[1]).where("created_at >= ? AND created_at <= ?", "#{d}-01", "#{d}-31").where.not(business: x_business_ids).count }, backgroundColor: "#fff", pointBorderColor: colors[i%15+1], borderColor: colors[i%15+1], pointBackgroundColor: colors[i%15+1], borderWidth: 2, } end } end def year_follow_count_chart(dates,names,x_business_ids,colors) { labels: dates, datasets:names.map.with_index do |name,i| { # hidden: i < 3 ? false : true, label: name[0], data: dates.map { |d| @follow_up = FollowUp.where( staff_id: name[1]).where("created_at >= ? AND created_at <= ?", "#{d}-01-01", "#{d}-12-31").where.not(business: x_business_ids).count }, backgroundColor: "#fff", pointBorderColor: colors[i%15+1], borderColor: colors[i%15+1], pointBackgroundColor: colors[i%15+1], borderWidth: 2 } end } end def month_sale_chart(dates,names,colors,x,stage_ids,s_stage_ids,count_type) { labels: dates, datasets: names.map.with_index do |name,i|{ label: name, data: dates.map{ |d| case i when 0 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 (?)", "#{d}-01", "#{d}-31", stage_ids).sum(count_type).round(2) when 1 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 (?)", "#{d}-01", "#{d}-31", s_stage_ids).sum(count_type).round(2) else 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 <= ? ", "#{d}-01", "#{d}-31").sum(:amount).round(2) 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) { labels: dates, datasets:names.map.with_index do |name,i| { label: name, data: dates.map{ |d| case i when 0 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 (?)", "#{d}-01-01", "#{d}-12-31", stage_ids).sum(count_type).round(2) when 1 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 (?)", "#{d}-01-01", "#{d}-12-31", s_stage_ids).sum(count_type).round(2) else 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 <= ? ", "#{d}-01-01", "#{d}-12-31").sum(:amount).round(2) end }, backgroundColor: colors[i], borderColor: colors[i], borderWidth: 1 } end } end end end