Class: Report::TechProductivity::TechProductivity

Inherits:
Object
  • Object
show all
Defined in:
app/services/report/tech_productivity/tech_productivity.rb

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.get_crm_data(start_time, end_time, departments_sql, employee_ids_sql) ⇒ Object 



59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 
# File 'app/services/report/tech_productivity/tech_productivity.rb', line 59

def self.get_crm_data(start_time,end_time,departments_sql,employee_ids_sql)
  sql = <<-SQL
    select
        employee_id,employee_name,department,sum(business_day) as business_day,(sum(business_day) - sum(day_off)) as days_worked,
        sum(act_cmp_created) as act_cmp_created,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(act_cmp_created) / (sum(business_day) - sum(day_off)) end as act_cmp_created_avg,
        sum(accs_created) as accs_created,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(accs_created) / (sum(business_day) - sum(day_off)) end as accs_created_avg,
        sum(opp_created) as opp_created,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(opp_created) / (sum(business_day) - sum(day_off)) end as opp_created_avg,
        sum(ord_created) as ord_created,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(ord_created) / (sum(business_day) - sum(day_off)) end as ord_created_avg,
        sum(rmas_created) as rmas_created,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(rmas_created) / (sum(business_day) - sum(day_off)) end as rmas_created_avg,
        sum(sc_created) as sc_created,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(sc_created) / (sum(business_day) - sum(day_off)) end as sc_created_avg,
        sum(comm_sent) as comm_sent,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(comm_sent) / (sum(business_day) - sum(day_off)) end as comm_sent_avg,
        sum(sms) as sms,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(sms) / (sum(business_day) - sum(day_off)) end as sms_avg
    from view_tech_productivities
    where date between '#{start_time}' and '#{end_time}'
    #{departments_sql}
    #{employee_ids_sql}
    group by employee_id,employee_name,department
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.get_phone_data(start_time, end_time, departments_sql, employee_ids_sql) ⇒ Object 



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
# File 'app/services/report/tech_productivity/tech_productivity.rb', line 35

def self.get_phone_data(start_time,end_time,departments_sql,employee_ids_sql)
  sql = <<-SQL
    select
        employee_id,employee_name,department,sum(business_day) as business_day,(sum(business_day) - sum(day_off)) as days_worked,
        sum(time_avbl) as time_avbl,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(time_avbl) / (sum(business_day) - sum(day_off)) end as time_avbl_avg,
        sum(inbounds) as inbounds,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(inbounds) / (sum(business_day) - sum(day_off)) end as inbounds_avg,
        sum(outbounds) as outbounds,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(outbounds) / (sum(business_day) - sum(day_off)) end as outbounds_avg,
        sum(missed) as missed,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(missed) / (sum(business_day) - sum(day_off)) end as missed_avg,
        sum(talk_time) as talk_time,
        case when (sum(business_day) - sum(day_off)) <= 0 then 0 else sum(talk_time) / (sum(business_day) - sum(day_off)) end as talk_time_avg
    from view_kpis_time_on_tasks
    where date between '#{start_time}' and '#{end_time}'
    #{departments_sql}
    #{employee_ids_sql}
    group by employee_id,employee_name,department
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.result_report(options = {}) ⇒ Object 



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'app/services/report/tech_productivity/tech_productivity.rb', line 8

def self.result_report(options = {})
  cur_monthly_track = []
  start_date = options[:period1_gteq]
  end_date = options[:period1_lteq] > (Date.current.to_date - 1) ? (Date.current.to_date - 1) : options[:period1_lteq]
  departments = options[:departments].map(&:presence).compact
  employee_ids = options[:employee_ids].map(&:presence).compact

  if departments.present?
    departments = options[:departments].map{|d| "'#{d}'"}.join(',')
    departments_sql = " and department in (#{departments})"
  else
    departments_sql = " and department is not null"
  end

  if employee_ids.present?
    employee_ids = employee_ids.join(',')
    employee_ids_sql = " and employee_id in (#{employee_ids})"
  else
    employee_ids_sql = " and employee_id not in (116,6791405,5620978,69,155,150282,6791406,6362291,85)"
  end

  phone_data = get_phone_data(start_date,end_date,departments_sql,employee_ids_sql)
  crm_data = get_crm_data(start_date,end_date,departments_sql,employee_ids_sql)

  Result.new(success: true, phone_data: phone_data, crm_data: crm_data, start_date: start_date, end_date: end_date)
end