require_dependency "educode_sales/application_controller" module EducodeSales class AssessmentsController < ApplicationController before_action :progress_or_get_export_data, only: [:progress, :get_export_data] def index respond_to do |format| format.html do common = Common.find_by(clazz: 'staff_type', name: '销售') @staffs = Staff.joins(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id]} # [[], [], ............] # 导出 gon.export_menus = [] gon.export_menus << { title: '导出到Csv文件', event: 'export_csv' } gon.export_menus << { title: '导出到Excel文件', event: 'export_excel' } # index页面new弹出层下使用 gon.staff_ids = Staff.joins(:user).where(job_type: common.id).map do |d| {value: d.id, name: d.user.real_name } end filter = Filter.find_or_create_by(staff_id: @current_admin.id, clazz: "assessments_setup") if filter.extras.present? gon.filter = filter.extras else gon.filter = {} end end format.json do @year = params[:q].present? && params[:q][:assessment_year].present? ? params[:q][:assessment_year]:'2022' @assessments_id = params[:q].present? && params[:q][:assessment_id].present? ? params[:q][:assessment_id]: '1' @assessments = AssessmentsSetting.where("assessment_year >= ? and assessment_year <= ?", "#{@year}-01-01 00:00:00".to_date, "#{@year}-12-31 23:59:00".to_date) @assessments = @assessments.where(assessment: @assessments_id).order("updated_at desc") if params[:q].present? && params[:q][:staff_id].present? @assessments = @assessments.where(staff_id: params[:q][:staff_id]).order("updated_at desc") end end end end # 新增 def create begin assessment = AssessmentsSetting.new(params_create) assessment.user_id = current_user.user_id assessment.assessment_year = "#{params[:assessment_year]}-01-01" AssessmentsSetting.transaction do params[:staffs_ids].split(",").each do |d| staff = assessment.dup staff.staff_id = d.to_i staff.save end end render_success rescue => e render_failure '增加数据错误' end end def new render layout: false end #编辑 def edit @assessment = AssessmentsSetting.find_by(id: params[:id]) @username = @assessment.staff.user.real_name @year = @assessment.assessment_year.to_s[0..3] case @assessment.assessment when 1 @assessment_name = "签单金额" when 2 @assessment_name = "回款金额" when 3 @assessment_name = "拜访量" when 4 @assessment_name = "新增商机数" end render layout: false end # 根新 def update @assessment = AssessmentsSetting.find_by(id: params[:id]) @assessment.update_attributes(params_update) render_success end # 删除接口 def destroy begin assessment = AssessmentsSetting.find(params[:id]) assessment.destroy render_success rescue => e render_failure '暂不能删除' end end # 导出数据 def get_export_data p 'get_export_data' end # 考核完成情况 def progress p 'progress' end def progress_or_get_export_data # common = Common.find_by(clazz: 'staff_type', name: '销售') # @staff_ids = Staff.joins(:user).where(job_type: common.id).pluck(:id) @assessment_id = params[:q].present? && params[:q][:assessment_id].present? ? params[:q][:assessment_id]:'1' @assessment_year = params[:q].present? && params[:q][:assessment_year].present? ? params[:q][:assessment_year] : Time.now.year @assessments =AssessmentsSetting.where(assessment: @assessment_id) .where("assessment_year >= ? and assessment_year <= ?", "#{@assessment_year}-01-01 00:00:00".to_date, "#{@assessment_year}-12-31 23:59:00".to_date) .order("created_at desc") if @assessments.present? # 小优化 @staff_ids = params[:q].present? && params[:q][:staff_id].present? ? [params[:q][:staff_id].to_i]:@assessments.distinct.pluck(:staff_id) progress_main # 注意一下 @assessments = @assessments.where(staff_id: @staff_ids) end end # todo 主方法 统计用户的数据 def progress_main case @assessment_id when '1' # 签单金额 busniness_jurisdiction signed_count when '2' # 回款金额 collection_count when '3' # 拜访量 visits customer_and_busniness_visits when '4' # 新增商机数 busniness_jurisdiction busniness_add end end # 签单金额 def signed_count # 过滤x类商机 ids = Common.where.not(extras: %w[x_class]).pluck(:id) @businesses = @businesses.joins(:last_follow_up).where("educode_sales_follow_ups.clazz_id in (?) ",ids) .where("educode_sales_follow_ups.signed_date >= ? and educode_sales_follow_ups.signed_date <= ?", "#{@assessment_year}-01-01 00:00:00".to_date, "#{@assessment_year}-12-31 23:59:59".to_date) # 得到最新阶段为已签单商机 s_stage_ids = Common.where(clazz: '商机阶段', name: ['已签单','已验收','回款中', '服务中','已结束']).pluck(:id) @businesses = @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.stage_id IN (?)",s_stage_ids) @staff_ids.each do |d| # 注意 businesses = @businesses.where(staff_id: d) data_count=[] data_list = FollowUp.joins("JOIN educode_sales_businesses ON educode_sales_businesses.last_follow_up_id = educode_sales_follow_ups.id") .where("educode_sales_businesses.id in (?)",businesses.pluck(:id)).map do |m| {value: m.actual_amount.to_i, date:m.signed_date.to_s[5..6].to_i} end count_data(data_list,data_count) # 统计数据 update_assessments_progress(data_count,d) # 批量更新assessment数据 事物 一个commit end end # 回款金额 权限 def collection_count follow_up_ids = Business.pluck(:last_follow_up_id) if @current_admin.is_admin? @money_plans = MoneyPlan.joins(:follow_up).where(follow_up_id: follow_up_ids) else level = @current_admin.role.role_areas.find_by(clazz: '回款管理').level case level when '自己' @money_plans = MoneyPlan.joins(:follow_up).where(follow_up_id: follow_up_ids).where(staff_id: @current_admin.id) when '区域' a_ids = MoneyPlan.where(follow_up_id: follow_up_ids).where(staff_id: @current_admin.id).pluck(:follow_up_id) school_ids = School.where(province: @current_admin.areas.pluck(:name)).pluck(:id) + StaffSchool.where(staff_id: @current_admin.id).pluck(:school_id) b_ids = Business.where(school_id: school_ids).pluck(:last_follow_up_id) ids = a_ids + b_ids @money_plans = MoneyPlan.joins(:follow_up).where(follow_up_id: ids) else @money_plans = MoneyPlan.joins(:follow_up).where(follow_up_id: follow_up_ids) end end # todo 1 :实际回款 0:计划回款 where(clazz: 1 OR 实际回款 ) @money_plans = @money_plans.where(clazz: 1).where.not(date_at: nil) @staff_ids.each do |d| data_count = [] data_list = @money_plans.where(staff_id: d).map do |m| {date:m.date_at.to_s[5..6].to_i, value:m.amount.to_i} end count_data(data_list,data_count) update_assessments_progress(data_count, d) end end # 权限获取拜访量 def visits if @current_admin.is_admin? @customers = School.all else level = @current_admin.role.role_areas.find_by(clazz: '客户管理').level case level when '自己' school_ids = CustomerExtension.where(customer_staff_id: @current_admin.id).pluck(:school_id) @customers = School.where(id: school_ids) when '区域' a_school_ids = School.where(province: @current_admin.areas.pluck(:name)).ids b_school_ids = CustomerExtension.where(customer_staff_id: @current_admin.id).pluck(:school_id) school_ids = a_school_ids + b_school_ids + StaffSchool.where(staff_id: @current_admin.id).pluck(:school_id) @customers = School.where(id: school_ids) else @customers = School.all end end part_a_ids = CustomerFollow.all.pluck(:school_id) part_b_ids = Business.where(id: EducodeSales::FollowUp.pluck(:business_id)).pluck(:school_id) ids = part_a_ids + part_b_ids + CustomerAdd.all.pluck(:school_id) @customers = @customers.where(id: ids) end # 拜访量 def customer_and_busniness_visits @staff_ids.each do |m| customers = @customers.where(id: EducodeSales::CustomerExtension.where(customer_staff_id: m).pluck(:school_id)) data_list = [] data_count = [] CustomerFollow.joins(:school).where(school_id: customers.pluck(:id)) .where("Educode_Sales_customer_follows.created_at >= ? and Educode_Sales_customer_follows.created_at <= ?","#{@assessment_year}-01-01 ","#{@assessment_year}-12-31 23:59:59" ) .each do |d| data_list << {value: 1, date: d.created_at.to_s[5..6].to_i} end FollowUp.joins(:business).where("Educode_Sales_businesses.id in (?)",EducodeSales::Business.where(school_id: customers.pluck(:id)).pluck(:id)) .where("Educode_Sales_follow_ups.created_at >= ? and Educode_Sales_follow_ups.created_at <= ?","#{@assessment_year}-01-01 00:00:00", "#{@assessment_year}-12-31 23:59:59").each do |d| data_list << {date: d.created_at.to_s[5..6].to_i, value: 1} end count_data(data_list,data_count) assessment = @assessments.where(staff_id: m) update_assessments_progress(data_count, assessment) end end # 新增商机数 def busniness_add @businesses = @businesses.where("educode_sales_businesses.created_at >= ? and educode_sales_businesses.created_at <= ?", "#{@assessment_year}-01-01 00:00:00","#{@assessment_year}-12-31 23:59:59") ids = Common.where(extras: %w[a_class b_class c_class d_class]).pluck(:id) @businesses = @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) @staff_ids.each do |d| businesses = @businesses.where(staff_id: d) data_count = [] data_list = businesses.map do |m| {date: m.created_at.to_s[5..6].to_i, value: 1} end count_data(data_list,data_count) # 统计数据 update_assessments_progress(data_count, d) # 批量更新assessment数据 事物 一个commit end end # busniness 权限 def busniness_jurisdiction # busniness 权限 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 end # 更新assessments_settings表中数据 def update_assessments_progress(data_count,staff_id) data_count[12] = data_count[0].to_i + data_count[1].to_i + data_count[2].to_i data_count[13] = data_count[3].to_i + data_count[4].to_i + data_count[5].to_i data_count[14] = data_count[6].to_i + data_count[7].to_i + data_count[8].to_i data_count[15] = data_count[9].to_i + data_count[10].to_i + data_count[11].to_i data_count[16] = data_count[12].to_i + data_count[13].to_i + data_count[14].to_i + data_count[15].to_i # 开启事物 处理 批量修改数据 assessments = @assessments.where(staff_id: staff_id) assessments.update(annual_progress: data_count[16], first_quarter_progress: data_count[12], second_quarter_progress: data_count[13], third_quarter_progress: data_count[14], fourth_quarter_progress: data_count[15], january_progress: data_count[0], february_progress: data_count[1], march_progress: data_count[2], april_progress: data_count[3], may_progress: data_count[4], june_progress: data_count[5], july_progress: data_count[6], august_progress: data_count[7], september_progress: data_count[8], october_progress: data_count[9], november_progress: data_count[10], december_progress: data_count[11]) end # 将统计到的数据转入a中 def count_data(data_list,data_count) data_list.each do |d| if data_count[d[:date]-1].present? data_count[d[:date]-1] = data_count[d[:date]-1] + d[:value] else data_count[d[:date]-1] = d[:value] end end end private def params_create params.permit( :annual, :assessment,:assessment_year, :first_quarter, :second_quarter, :third_quarter, :fourth_quarter, :january, :february, :march, :april, :may, :june, :july, :august, :september, :october, :november, :december) end def params_update params.permit( :annual, :first_quarter, :second_quarter, :third_quarter, :fourth_quarter, :january, :february, :march, :april, :may, :june, :july, :august, :september, :october, :november, :december) end end end