require_dependency "educode_sales/application_controller" module EducodeSales class AssessmentsController < ApplicationController 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' } end format.json do p '--------------------------admin' p @current_admin p '----------------admin' @assessments = AssessmentsSetting.all @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 = @assessments.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("id desc") if params[:q].present? && params[:q][:staff_id].present? @assessments = @assessments.where(staff_id: params[:q][:staff_id]).order("id desc") end # 对数据进行分页 默认为page=1 limit=10 # @assessments = @assessments.order("id desc").page(params[:page]).per(params[:limit]) end end end # 新增 def create ActiveRecord::Base.transaction do demo = AssessmentsSetting.new(params_create) demo.user_id = current_user.user_id demo.assessment_year = "#{params[:assessment_year]}-01-01" demo.save p demo.assessment_year,'ok' end render_success end def new 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]} # [[], [], ............] # do something # @staffs = Staff.joins(:user).where(job_type: common.id).map { |d| [d.user.real_name, d.id]} 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 ActiveRecord::DeleteRestrictionError => e render_failure '渠道下已有关联数据产生，暂不能删除' end end # 导出数据 def 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[:assessment_id].present? ? params[:assessment_id]:'1' @assessment_year = params[:assessment_year] ? params[:assessment_year] : '2022' @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) if params[:staff_id].present? @assessment = @assessments.where(staff_id: params[:staff_id]).order("id desc").first progress_main else # @assessments = @assessments.where(staff_id: @staff_ids) 注意 这样写有问题 @staff_ids.each do |s| @assessment = @assessments.where(staff_id: s).order("id desc").first progress_main end end # 在从新获取一下 统计后的所有assessment_setting数据 @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) @assessments = params[:staff_id].present? ? @assessments.where(staff_id: params[:staff_id]).order("id desc"): @assessments.order("id desc") end # 增加导出记录 def add_export_records end # todo 可以增加一个字段(用户判断assessment_setting表中的某一数据是否在一定条件下为最新数据 # todo 在统计数据时 可以通过该字段 获取最新的数据 并统计 当比之前写的大大降低的代码的重复量 ，并且提高了数据库的性能)----注意 如果增加该字段后 在对某条数据进行操作时，要实施更新该条数据的该字段 # 考核完成情况 def progress 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] ? params[:q][:assessment_year] : '2022' @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) if params[:q].present? && params[:q][:staff_id].present? @assessment = @assessments.where(staff_id: params[:q][:staff_id]).order("id desc").first progress_main else # @assessments = @assessments.where(staff_id: @staff_ids) 注意 这样写有问题 @staff_ids.each do |s| @assessment = @assessments.where(staff_id: s).order("id desc").first progress_main end end # 在从新获取一下 统计后的所有assessment_setting数据 @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) @assessments = params[:q].present? && params[:q][:staff_id].present? ? @assessments.where(staff_id: params[:q][:staff_id]).order("id desc"): @assessments.order("id desc") end # todo 主方法 统计用户的数据 def progress_main if @assessment.present? a = [] case @assessment_id when '1' # 签单金额 busniness_jurisdiction signed_amount(a) when '2' # 回款金额 collection_amount(a) when '3' # 拜访量 visits school_ids = EducodeSales::CustomerExtension.where(customer_staff_id: @staff_id).pluck(:school_id) @customers = @customers.where(id: school_ids) busniness_visits(a) # 商机拜访量 customer_visits(a) # 客户拜访量 when '4' # 新增商机数 busniness_jurisdiction busniness_add(a) end update_assessments_progress(a) else # render layout:false end end # 签单金额 def signed_amount(a) # 过滤x类商机 ids = Common.where(extras: %w[a_class b_class c_class d_claass e_class o_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) # 得到最新阶段为已签单商机 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的商机------------先暂时指定 @businesses = @businesses.where(staff_id: @staff_id) # 对businesses each操作并 获取最新根新记录 @businesses.each { |d| d @follow_ups = d.follow_ups.where.not(signed_date: nil).order("signed_date desc") if @follow_ups.first.present? signed_date = @follow_ups.first.signed_date.to_time || '1000-01-01 00:00:00'.to_time case signed_date.to_s[0..6] when "#{@assessment_year}-01" a[0] = a[0].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-02" a[1] = a[1].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-03" a[2] = a[2].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-04" a[3] = a[3].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-05" a[4] = a[4].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-06" a[5] = a[5].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-07" a[6] = a[6].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-08" a[7] = a[7].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-09" a[8] = a[8].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-10" a[9] = a[9].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-11" a[10] = a[10].to_i + @follow_ups.first.actual_amount.to_i when "#{@assessment_year}-12" a[11] = a[11].to_i + @follow_ups.first.actual_amount.to_i end end } end # 回款金额 def collection_amount(a) 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 实际回款 ) 先把staff写死 @money_plans = @money_plans.where(clazz: 1).where.not(date_at: nil ).where(staff_id: @staff_id) @money_plans.each do |d| case d.date_at.to_s[0..6] when "#{@assessment_year}-01" a[0] = a[0].to_i + d.amount.to_i when "#{@assessment_year}-02" a[1] = a[1].to_i + d.amount.to_i when "#{@assessment_year}-03" a[2] = a[2].to_i + d.amount.to_i when "#{@assessment_year}-04" a[3] = a[3].to_i + d.amount.to_i when "#{@assessment_year}-05" a[4] = a[4].to_i + d.amount.to_i when "#{@assessment_year}-06" a[5] = a[5].to_i + d.amount.to_i when "#{@assessment_year}-07" a[6] = a[6].to_i + d.amount.to_i when "#{@assessment_year}-08" a[7] = a[7].to_i + d.amount.to_i when "#{@assessment_year}-09" a[8] = a[8].to_i + d.amount.to_i when "#{@assessment_year}-10" a[9] = a[9].to_i + d.amount.to_i when "#{@assessment_year}-11" a[10] = a[10].to_i + d.amount.to_i when "#{@assessment_year}-12" a[11] = a[11].to_i + d.amount.to_i end 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_visits(a) @customers.each do |d| customer_follows = EducodeSales::CustomerFollow.where(school_id: d.id) customer_follows.each do |f| case f.created_at.to_time.to_s[0..6] when "#{@assessment_year}-01" a[0] = a[0].to_i + 1 when "#{@assessment_year}-02" a[1] = a[1].to_i + 1 when "#{@assessment_year}-03" a[2] = a[2].to_i + 1 when "#{@assessment_year}-04" a[3] = a[3].to_i + 1 when "#{@assessment_year}-05" a[4] = a[4].to_i + 1 when "#{@assessment_year}-06" a[5] = a[5].to_i + 1 when "#{@assessment_year}-07" a[6] = a[6].to_i + 1 when "#{@assessment_year}-08" a[7] = a[7].to_i + 1 when "#{@assessment_year}-09" a[8] = a[8].to_i + 1 when "#{@assessment_year}-10" a[9] = a[9].to_i + 1 when "#{@assessment_year}-11" a[10] = a[10].to_i + 1 when "#{@assessment_year}-12" a[11] = a[11].to_i + 1 end end end end # 商机拜访量 def busniness_visits(a) @customers.each do |d| business_ids = EducodeSales::Business.where(school_id: d.id).ids follow_ups = EducodeSales::FollowUp.where(business_id: business_ids) follow_ups.each do |f| case f.created_at.to_time.to_s[0..6] when "#{@assessment_year}-01" a[0] = a[0].to_i + 1 when "#{@assessment_year}-02" a[1] = a[1].to_i + 1 when "#{@assessment_year}-03" a[2] = a[2].to_i + 1 when "#{@assessment_year}-04" a[3] = a[3].to_i + 1 when "#{@assessment_year}-05" a[4] = a[4].to_i + 1 when "#{@assessment_year}-06" a[5] = a[5].to_i + 1 when "#{@assessment_year}-07" a[6] = a[6].to_i + 1 when "#{@assessment_year}-08" a[7] = a[7].to_i + 1 when "#{@assessment_year}-09" a[8] = a[8].to_i + 1 when "#{@assessment_year}-10" a[9] = a[9].to_i + 1 when "#{@assessment_year}-11" a[10] = a[10].to_i + 1 when "#{@assessment_year}-12" a[11] = a[11].to_i + 1 end end end end # busniness_jurisdiction # ids = Common.where(extras: %w[a_class b_class c_class d_claass e_class o_class x_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) # @businesses = @businesses.where("educode_sales_businesses.follow_ups_count > ?",'0').where(staff_id: @staff_id) # 新增商机数 def busniness_add(a) # 1.新增商机数统计在哪一个时间段？以商机的创建时间为准；先默认 过滤2022之前的 businesses -created_et deleted_at follow_ups_count @businesses = @businesses.where("follow_ups_count > ?", '0').where("educode_sales_businesses.created_at >= ? and educode_sales_businesses.created_at <= ?", "#{@assessment_year}-01-01 00:00:00".to_date,"#{@assessment_year}-12-31 23:59:00".to_date) # 2.销售人员新增的商机数量，过滤E、O、X类商机，包含：ABCD类商机； 上面有连接follow_up表 ids = Common.where(extras: %w[a_class b_class c_class d_claass]).pluck(:id) # 数组[1,5,2,15] @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写死 @businesses = @businesses.where(staff_id: @staff_id) @businesses.each do |d| case d.created_at.to_time.to_s[0..6] when "#{@assessment_year}-01" a[0] = a[0].to_i + 1 when "#{@assessment_year}-02" a[1] = a[1].to_i + 1 when "#{@assessment_year}-03" a[2] = a[2].to_i + 1 when "#{@assessment_year}-04" a[3] = a[3].to_i + 1 when "#{@assessment_year}-05" a[4] = a[4].to_i + 1 when "#{@assessment_year}-06" a[5] = a[5].to_i + 1 when "#{@assessment_year}-07" a[6] = a[6].to_i + 1 when "#{@assessment_year}-08" a[7] = a[7].to_i + 1 when "#{@assessment_year}-09" a[8] = a[8].to_i + 1 when "#{@assessment_year}-10" a[9] = a[9].to_i + 1 when "#{@assessment_year}-11" a[10] = a[10].to_i + 1 when "#{@assessment_year}-12" a[11] = a[11].to_i + 1 end end end # busniness 权限 def busniness_jurisdiction # busniness 权限 if @current_admin.is_admin? @businesses = Business 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 end end end # 更新assessments_settings表中数据 def update_assessments_progress(a) a[12] = a[0].to_i + a[1].to_i + a[2].to_i a[13] = a[3].to_i + a[4].to_i + a[5].to_i a[14] = a[6].to_i + a[7].to_i + a[8].to_i a[15] = a[9].to_i + a[10].to_i + a[11].to_i a[16] = a[12].to_i + a[13].to_i + a[14].to_i + a[15].to_i @assessment.update(annual_progress: a[16], first_quarter_progress: a[12], second_quarter_progress: a[13], third_quarter_progress: a[14], fourth_quarter_progress: a[15], january_progress: a[0], february_progress: a[1], march_progress: a[2], april_progress: a[3], may_progress: a[4], june_progress: a[5], july_progress: a[6], august_progress: a[7], september_progress: a[8], october_progress: a[9], november_progress: a[10], december_progress: a[11]) end private def params_create params.permit(:staff_id, :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