require_dependency "educode_sales/application_controller" module EducodeSales class SubjectTrendsController < ApplicationController # authorize_resource class: false include SaleTrendsHelper include SaleTrendsCountHelper before_action :subject_url def trends authorize! :status, BusinessDeliverSubject @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 } search_year = Time.now.change(year: @year) # 统计时间 start_time = @month == "全部" ? search_year.change(month: 1).beginning_of_month : search_year.change(month: @month).beginning_of_month end_time = @month == "全部" ? search_year.change(month: 12).end_of_month : search_year.change(month: @month).end_of_month # 商机交付课程(已交付项目) b_d_s = BusinessDeliverSubject.joins(business: :last_follow_up).where("educode_sales_follow_ups.reception_at BETWEEN '#{start_time}' AND '#{end_time}' ") @completed_b_count = b_d_s.delivered.count @uncompleted_b_count = b_d_s.undelivered.count # 实践课程 b_subjects = b_d_s.joins(:subjects) un_b_subjects = BusinessSubject.joins(:subject) .where.not(subject_id: b_subjects.pluck("subjects.id")) .where("educode_sales_business_subjects.deliver_date BETWEEN '#{start_time}' AND '#{end_time}' ") @completed_s_count = un_b_subjects.where("subjects.status = 2 or public = 2").count + b_subjects.where("subjects.status = 2 or public = 2").count @uncompleted_s_count = un_b_subjects.where.not("subjects.status = 2 or public = 2").count + b_subjects.where.not("subjects.status = 2 or public = 2").count # 实践项目 un_b_shixun_ids = b_d_s.joins(business_subjects: :business_subject_shixuns).pluck("educode_sales_business_subject_shixuns.id") un_s_shixun_ids = un_b_subjects.joins(:business_subject_shixuns).pluck("educode_sales_business_subject_shixuns.id") un_shixun_ids = BusinessSubjectShixun.left_joins(:business_subject) .where("educode_sales_business_subjects.id is null and educode_sales_business_subject_shixuns.deliver_date BETWEEN '#{start_time}' AND '#{end_time}' ") .pluck("educode_sales_business_subject_shixuns.id") shixuns = BusinessSubjectShixun.where(id: un_b_shixun_ids + un_s_shixun_ids + un_shixun_ids).left_joins(shixun_producer: [staff: :user]) # todo 待优化 shixuns = shixuns.distinct("DISTINCT educode_sales_business_subject_shixuns.id") all_shixun = shixuns.where("educode_sales_business_subject_shixuns.shixun_type = 1") un_shixun = shixuns.where("educode_sales_business_subject_shixuns.shixun_type = 2") @money = shixuns.pluralist.completed.sum(:deliver_money) # all_shixun_users = all_shixun.left_joins(shixun_producer: [staff: :user]) # un_shixun_users = un_shixun.left_joins(shixun_producer: [staff: :user]) # 管培 @training_completed_shixuns = all_shixun.training.completed.count @training_un_completed_shixuns = all_shixun.training.un_completed.count @training_completed_un_shixuns = un_shixun.training.completed.count @training_un_completed_un_shixuns = un_shixun.training.un_completed.count @training_shixuns = all_shixun.training.pluck("DISTINCT users.id ").count == 0 ? 0 : all_shixun.training.count / all_shixun.training.pluck("DISTINCT users.id ").count @training_un_shixuns = un_shixun.training.pluck("DISTINCT users.id ").count == 0 ? 0 : un_shixun.training.count / un_shixun.training.pluck("DISTINCT users.id ").count # 全职 @worker_completed_shixuns = all_shixun.worker.completed.count @worker_un_completed_shixuns = all_shixun.worker.un_completed.count @worker_completed_un_shixuns = un_shixun.worker.completed.count @worker_un_completed_un_shixuns = un_shixun.worker.un_completed.count @worker_shixuns = all_shixun.worker.pluck("DISTINCT users.id ").count == 0 ? 0 : all_shixun.worker.count / all_shixun.worker.pluck("DISTINCT users.id ").count @worker_un_shixuns = un_shixun.worker.pluck("DISTINCT users.id ").count == 0 ? 0 : un_shixun.worker.count / un_shixun.worker.pluck("DISTINCT users.id ").count # 专职(兼职) @pluralist_completed_shixuns = all_shixun.pluralist.completed.count @pluralist_un_completed_shixuns = all_shixun.pluralist.un_completed.count @pluralist_completed_shixun_money = all_shixun.pluralist.completed.sum(:deliver_money) @pluralist_completed_un_shixuns = un_shixun.pluralist.completed.count @pluralist_un_completed_un_shixuns = un_shixun.pluralist.un_completed.count @pluralist_completed_un_shixun_money = un_shixun.pluralist.completed.sum(:deliver_money) @pluralist_shixuns = all_shixun.pluralist.pluck("DISTINCT users.id ").count == 0 ? 0 : all_shixun.pluralist.count / all_shixun.pluralist.pluck("DISTINCT users.id ").count @pluralist_un_shixuns = un_shixun.pluralist.pluck("DISTINCT users.id ").count == 0 ? 0 : un_shixun.pluralist.count / un_shixun.pluralist.pluck("DISTINCT users.id ").count # 排名 producer = BusinessSubjectStaff.joins(staff: :user).shixun_producer.group("users.id").select("COUNT(users.id) num, CONCAT(users.lastname, users.firstname) user_name") @max_users = producer.order("num desc").limit(3).map{|d| d.user_name }.join("、") @min_users = producer.order("num asc").limit(3).map{|d| d.user_name }.join("、") end def distinct_count(item) item.count("DISTINCT educode_sales_business_subject_shixuns.id") end def demo # 管培 @training_completed_shixuns = distinct_count(all_shixun.training.completed) p @training_completed_shixuns @training_un_completed_shixuns = distinct_count all_shixun.training.un_completed @training_completed_un_shixuns = distinct_count un_shixun.training.completed @training_un_completed_un_shixuns = distinct_count un_shixun.training.un_completed # @training_shixuns = all_shixun.training.pluck("DISTINCT users.id ").count == 0 ? 0 : distinct_count(all_shixun.training) / all_shixun.training.pluck("DISTINCT users.id ").count @training_un_shixuns = un_shixun.training.pluck("DISTINCT users.id ").count == 0 ? 0 : distinct_count(un_shixun.training) / un_shixun.training.pluck("DISTINCT users.id ").count # # # 全职 # @worker_completed_shixuns = distinct_count all_shixun.worker.completed # @worker_un_completed_shixuns = distinct_count all_shixun.worker.un_completed # @worker_completed_un_shixuns = distinct_count un_shixun.worker.completed # @worker_un_completed_un_shixuns = distinct_count un_shixun.worker.un_completed # # @worker_shixuns = all_shixun.worker.pluck("DISTINCT users.id ").count == 0 ? 0 : distinct_count all_shixun.worker / all_shixun.worker.pluck("DISTINCT users.id ").count # @worker_un_shixuns = un_shixun.worker.pluck("DISTINCT users.id ").count == 0 ? 0 : distinct_count un_shixun.worker / un_shixun.worker.pluck("DISTINCT users.id ").count # # # # 专职(兼职) # @pluralist_completed_shixuns = distinct_count all_shixun.pluralist.completed # @pluralist_un_completed_shixuns = distinct_count all_shixun.pluralist.un_completed # # @pluralist_completed_shixun_money = all_shixun.pluralist.completed.distinct("DISTINCT educode_sales_business_subject_shixuns.id").sum(:deliver_money) # @pluralist_completed_un_shixuns = distinct_count un_shixun.pluralist.completed # @pluralist_un_completed_un_shixuns = distinct_count un_shixun.pluralist.un_completed # @pluralist_completed_un_shixun_money = un_shixun.pluralist.completed.distinct("DISTINCT educode_sales_business_subject_shixuns.id").sum(:deliver_money) # # @pluralist_shixuns = all_shixun.pluralist.pluck("DISTINCT users.id ").count == 0 ? 0 : distinct_count all_shixun.pluralist.count / all_shixun.pluralist.pluck("DISTINCT users.id ").count # @pluralist_un_shixuns = un_shixun.pluralist.pluck("DISTINCT users.id ").count == 0 ? 0 : distinct_count un_shixun.pluralist.count / un_shixun.pluralist.pluck("DISTINCT users.id ").count end end end