Class: Report::TechProductivity::TechProductivity

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

Overview

Service object: tech productivity.

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

Get crm data.

Parameters:

  • start_time (Time)
  • end_time (Time)
  • departments_sql (Object)
  • employee_ids_sql (Object)

Returns:

  • (Object) 


86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'app/services/report/tech_productivity/tech_productivity.rb', line 86

def self.get_crm_data(start_time, end_time, departments_sql, employee_ids_sql)
  sql = <<-SQL.squish
    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

  ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&:symbolize_keys)
end

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

Get phone data.

Parameters:

  • start_time (Time)
  • end_time (Time)
  • departments_sql (Object)
  • employee_ids_sql (Object)

Returns:

  • (Object) 


56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'app/services/report/tech_productivity/tech_productivity.rb', line 56

def self.get_phone_data(start_time, end_time, departments_sql, employee_ids_sql)
  sql = <<-SQL.squish
    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

  ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&:symbolize_keys)
end

.result_report(options = {}) ⇒ Result

Returns phone and CRM productivity data.

Parameters:

  • options (Hash) (defaults to: {}) —

    report filter options

Options Hash (options):

  • period1_gteq (Date) —

    start of the reporting period

  • period1_lteq (Date) —

    end of the reporting period (capped at yesterday)

  • departments (Array<String>) —

    departments to include (blank entries ignored)

  • employee_ids (Array<Integer>) —

    employees to include (blank entries ignored)

Returns:

  • (Result) —

    phone and CRM productivity data



24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
# File 'app/services/report/tech_productivity/tech_productivity.rb', line 24

def self.result_report(options = {})
  start_date = options[:period1_gteq]
  end_date = [options[:period1_lteq], (Date.current.to_date - 1)].min
  departments = options[:departments].filter_map(&:presence)
  employee_ids = options[:employee_ids].filter_map(&:presence)

  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