module EducodeSales module ApplicationHelper def current?(css, path) if path.is_a?(Array) ? (current_page?(path.first) || request.path == path.last) : current_page?(path) css else ' ' end end def handled_data(item) item.blank? ? "--" : item end def handled_time_data(item) item.blank? ? "--" : item.strftime("%Y-%m-%d") end def handled_time_data_accurate(item) item.blank? ? "--" : item.strftime("%Y-%m-%d %H:%M:%S") end def url_to_avatar(source) return "" if source.blank? if File.exist?(disk_filename(source&.class, source&.id)) && File.file?(disk_filename(source&.class, source&.id)) ctime = File.ctime(disk_filename(source&.class, source&.id)).to_i if source.class.to_s == 'User' File.join(relative_path, ["#{source&.class}", "#{source&.id}"]) + "?t=#{ctime}" else File.join("images/avatars", ["#{source&.class}", "#{source&.id}"]) + "?t=#{ctime}" end elsif source.class.to_s == 'User' str = source.user_extension.try(:gender).to_i == 0 ? "b" : "g" File.join(relative_path, "#{source.class}", str) elsif source.class.to_s == 'Subject' File.join("images","educoder", "index", "subject", "subject#{rand(19)}.jpg") elsif source.class.to_s == 'Shixun' File.join("images","educoder", "index", "shixun", "shixun#{rand(23)}.jpg") end end def disk_filename(source_type,source_id,image_file=nil) File.join(storage_path, "#{source_type}", "#{source_id}") end def storage_path File.join(Rails.root, "public", "images", relative_path) end def relative_path "avatars" end def base_url url = Rails.application.config_for(:configuration)['wechat_pay']['callback_url'] if url.include?("https://data.educoder") "https://www.educoder.net" elsif url.include?("'https://pre-data.educoder") "https://pre.educoder.net" else "https://test.educoder.net" end end # 完成率completion_rate def completion_rate(setting,progress) if setting.to_i == 0 '100%' else (progress.to_f/setting.to_f*100).round(2).to_s + "%" end end # 签单金额 得分规则 def signed_amount_score(setting,progress) progress.to_f >= setting.to_f || setting.to_i == 0 ? 40:(progress.to_f/setting.to_f*40).round(2) end # 回款金额 得分规则 def collection_amount_score(setting,progress) (progress.to_i >= setting.to_i) || (setting.to_i == 0) ? 20:(progress.to_f/setting.to_f*20).round(2) end # 拜访量 得分规则 def visits_score(d) d.to_i > 30 ? 20:0 end #新增商机数 得分规则 def add_businesses_score(staff_id,start_time,end_time) if @current_admin.is_admin? @businesses = Business.all else level = @current_admin.role.role_areas.find_by(clazz: '商机管理').level case level when '自己' # Business.joins(Business字段: :表的名称)----> Business表(belongs_to) Business字段关联的表(has_many) Business字段关联的表在关联的表(belongs_to) business_ids = Business.joins(last_follow_up: :assign_follow_ups).where("educode_sales_assign_follow_ups.staff_id = ?", @current_admin.id).pluck(:id) @businesses = Business.where("educode_sales_businesses.staff_id = ? OR educode_sales_businesses.id in (?)", @current_admin.id, business_ids) when '区域' school_ids = School.where(province: @current_admin.areas.pluck(:name)).pluck(:id) + StaffSchool.where(staff_id: @current_admin.id).pluck(:school_id) business_ids = Business.joins(last_follow_up: :assign_follow_ups).where("educode_sales_assign_follow_ups.staff_id = ?", @current_admin.id).pluck(:id) @businesses = Business.joins("JOIN departments ON educode_sales_businesses.department_id = departments.id").where("departments.school_id in (?) OR educode_sales_businesses.staff_id = #{@current_admin.id} OR educode_sales_businesses.id in (?)", school_ids, business_ids) else @businesses = Business.all end end ids_a_b = Common.where(extras: %w[a_class b_class ]).pluck(:id) ids_c_d = Common.where(extras: %w[c_class d_class]).pluck(:id) @businesses_a_b_count = @businesses.joins("JOIN educode_sales_follow_ups ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id").where("educode_sales_follow_ups.clazz_id in (?)", ids_a_b) .where("educode_sales_businesses.staff_id = ?", staff_id) .where("educode_sales_businesses.created_at >= ? and educode_sales_businesses.created_at <= ?", "#{@assessment_year}-#{start_time} 00:00:00".to_date, "#{@assessment_year}-#{end_time} 23:59:00".to_date) @businesses_c_d_count = @businesses.joins("JOIN educode_sales_follow_ups ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id").where("educode_sales_follow_ups.clazz_id in (?)", ids_c_d) .where("educode_sales_businesses.staff_id = ?", staff_id) .where("educode_sales_businesses.created_at >= ? and educode_sales_businesses.created_at <= ?", "#{@assessment_year}-#{start_time} 00:00:00".to_date, "#{@assessment_year}-#{end_time} 23:59:00".to_date) @businesses_a_b_count.count.to_i * 10 + @businesses_c_d_count.count.to_i * 5 end def get_businesses_chart(year, month) contract_ids = EducodeSales::Common.where(clazz: '商机阶段', name: ['已中标', '已签单', '已验收', '回款中', '服务中', '已结束']).pluck(:id) @businesses = EducodeSales::Business.joins(" JOIN educode_sales_follow_ups ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id ").where("educode_sales_follow_ups.stage_id in (?)", contract_ids) # 逾期id late_ids = EducodeSales::Business.joins("JOIN educode_sales_follow_ups ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id JOIN educode_sales_commons on educode_sales_commons.id = educode_sales_follow_ups.stage_id") .where("educode_sales_follow_ups.bidded_date is not null") .where("NOW() > DATE_ADD(educode_sales_follow_ups.bidded_date, INTERVAL 1 MONTH)") .where("educode_sales_businesses.p_stage is null") .where("educode_sales_commons.name = '已中标'") .or(EducodeSales::Business.joins("JOIN educode_sales_follow_ups ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id JOIN educode_sales_commons on educode_sales_commons.id = educode_sales_follow_ups.stage_id") .where("NOW() > educode_sales_businesses.p_pre_money_time") .where("educode_sales_businesses.p_pre_money_time is not null") .where("educode_sales_businesses.p_actual_money_time is null") ).or(EducodeSales::Business.joins("JOIN educode_sales_follow_ups ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id JOIN educode_sales_commons on educode_sales_commons.id = educode_sales_follow_ups.stage_id") .where("NOW() > educode_sales_businesses.p_pre_accept_time") .where("educode_sales_businesses.p_pre_accept_time is not null") .where("educode_sales_businesses.p_accept_time is null")).ids # 待交付项目 data_4 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") # 实际交付 data_5 = @businesses.where("educode_sales_businesses.p_deploy_time > ? AND educode_sales_businesses.p_deploy_time < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") # 待准备项目 data_6 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") # 完成准备 data_7 = @businesses.where("educode_sales_businesses.p_status = 1") .where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") # 待验收平台包 data_8 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") # 实际验收平台包 data_9 = @businesses.where("educode_sales_businesses.p_platform_time is not null") .where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") # 待验收课程包 data_10 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") # 实际验收课程包 data_11 = @businesses.where("educode_sales_businesses.p_course_time is not null") .where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .count("distinct educode_sales_businesses.id") data_2 = data_4 + data_6 + data_8 + data_10 # 本月总任务 data_3 = data_5 + data_7 + data_9 + data_11 # 本月完成任务 # 本月任务完成度 data_1 = data_2.zero? ? 0 : (data_3.to_f / data_2.to_f).round(2) * 100 # 历史遗漏项目 data_12 = @businesses.where("educode_sales_follow_ups.reception_at < ?", year + "-01-01") .where("educode_sales_businesses.p_course_time IS NULL OR educode_sales_businesses.p_platform_time IS NULL OR educode_sales_businesses.p_deploy_time IS NULL OR educode_sales_businesses.p_actual_money_time IS NULL OR educode_sales_businesses.p_accept_time IS NULL ").count("distinct educode_sales_businesses.id") # 年度待交付项目 data_13 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-01-01", year + "-12-31") .count("distinct educode_sales_businesses.id") # 年度已交付项目 data_14 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-01-01", year + "-12-31") .where("educode_sales_businesses.p_course_time IS NOT NULL AND educode_sales_businesses.p_platform_time IS NOT NULL AND educode_sales_businesses.p_deploy_time IS NOT NULL AND educode_sales_businesses.p_actual_money_time IS NOT NULL AND educode_sales_businesses.p_accept_time IS NOT NULL ").count("distinct educode_sales_businesses.id") # 年度延期项目 data_15 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-01-01", year + "-12-31") .where(id: late_ids) .count("distinct educode_sales_businesses.id") year_data_all_1 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-01-01", year + "-12-31") .group(:p_staff_id).sum("case when educode_sales_businesses.p_course_time is not null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is not null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is not null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is not null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is not null then 1 else 0 end") year_data_all_2 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-01-01", year + "-12-31") .group(:p_sale_staff_id).sum("case when educode_sales_businesses.p_course_time is not null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is not null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is not null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is not null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is not null then 1 else 0 end") month_data_all_1 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .group(:p_staff_id).sum("case when educode_sales_businesses.p_course_time is not null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is not null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is not null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is not null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is not null then 1 else 0 end") month_data_all_2 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .group(:p_sale_staff_id).sum("case when educode_sales_businesses.p_course_time is not null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is not null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is not null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is not null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is not null then 1 else 0 end") year_merged_hash = month_data_all_1.merge(month_data_all_2) { |key, v1, v2| v1 + v2 }.sort_by { |k, v| v }.reverse.to_h month_merged_hash = year_data_all_1.merge(year_data_all_2) { |key, v1, v2| v1 + v2 }.sort_by { |k, v| v }.reverse.to_h not_finish_year_data_all_1 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-01-01", year + "-12-31") .group(:p_staff_id).sum("case when educode_sales_businesses.p_course_time is null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is null then 1 else 0 end") not_finish_year_data_all_2 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-01-01", year + "-12-31") .group(:p_sale_staff_id).sum("case when educode_sales_businesses.p_course_time is null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is null then 1 else 0 end") not_finish_month_data_all_1 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .group(:p_staff_id).sum("case when educode_sales_businesses.p_course_time is null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is null then 1 else 0 end") not_finish_month_data_all_2 = @businesses.where("educode_sales_follow_ups.reception_at > ? AND educode_sales_follow_ups.reception_at < ?", year + "-" + month + "-01", year + "-" + month + "-31") .group(:p_sale_staff_id).sum("case when educode_sales_businesses.p_course_time is null then 1 else 0 end + case when educode_sales_businesses.p_platform_time is null then 1 else 0 end + case when educode_sales_businesses.p_deploy_time is null then 1 else 0 end + case when educode_sales_businesses.p_actual_money_time is null then 1 else 0 end + case when educode_sales_businesses.p_accept_time is null then 1 else 0 end") not_finish_year_merged_hash = not_finish_year_data_all_1.merge(not_finish_year_data_all_2) { |key, v1, v2| v1 + v2 }.sort_by { |k, v| v }.reverse.to_h not_finish_month_merged_hash = not_finish_month_data_all_1.merge(not_finish_month_data_all_2) { |key, v1, v2| v1 + v2 }.sort_by { |k, v| v }.reverse.to_h all_year_merged_hash = year_merged_hash.merge(not_finish_year_merged_hash) { |key, v1, v2| v1 + v2 }.sort_by { |k, v| v }.reverse.to_h all_month_merged_hash = month_merged_hash.merge(not_finish_month_merged_hash) { |key, v1, v2| v1 + v2 }.sort_by { |k, v| v }.reverse.to_h staffs = EducodeSales::Staff.all.includes(:user) colors = SaleTrend::COLORS chart1 = { labels: staffs.map { |d| d.user.real_name }, datasets: %w[已完成 未完成].map.with_index do |d, i| { label: d, data: staffs.map { |s| case d when "已完成" month_merged_hash[s.id].to_i when "未完成" not_finish_month_merged_hash[s.id].to_i end }, backgroundColor: colors[i], borderColor: colors[i], borderWidth: 1 } end } chart2 = { labels: staffs.map { |d| d.user.real_name }, datasets: %w[已完成 未完成].map.with_index do |d, i| { label: d, data: staffs.map { |s| case d when "已完成" year_merged_hash[s.id].to_i when "未完成" not_finish_year_merged_hash[s.id].to_i end }, backgroundColor: colors[i], borderColor: colors[i], borderWidth: 1 } end } staff_data = EducodeSales::Staff.where(id: year_merged_hash.keys).limit(3).map { |d| [d.user.real_name, all_year_merged_hash[d.id], year_merged_hash[d.id]] } [data_1, data_2, data_3, data_4, data_5, data_6, data_7, data_8, data_9, data_10, data_11, data_12, data_13, data_14, data_15, staff_data, chart1, chart2] end end end