Class: Report::KpiCall::KpiCall

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

Overview

Service object: kpi call.

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

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

Get 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
79
80
81
82
83
84
85
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
 
# File 'app/services/report/kpi_call/kpi_call.rb', line 56

def self.get_data(start_time, end_time, departments_sql, employee_ids_sql)
  sql = <<-SQL.squish
    with kpis_limits as (
        select
        sum(case when kpi_code = 2 then tier2 else 0 end) as inbounds_min,sum(case when kpi_code = 2 then tier1 else 0 end) as inbounds_pts,
        sum(case when kpi_code = 3 then tier2 else 0 end) as outbounds_min,sum(case when kpi_code = 3 then tier1 else 0 end) as outbounds_pts,
        sum(case when kpi_code = 4 then tier2 else 0 end) as activities_min,sum(case when kpi_code = 4 then tier1 else 0 end) as activities_pts,
        sum(case when kpi_code = 5 then tier2 else 0 end) as sms_min,sum(case when kpi_code = 5 then tier1 else 0 end) as sms_pts,
        sum(case when kpi_code = 6 then tier2 else 0 end) as looms_min,sum(case when kpi_code = 6 then tier1 else 0 end) as looms_pts,
        sum(case when kpi_code = 7 then tier2 else 0 end) as call_blocks_min,sum(case when kpi_code = 7 then tier1 else 0 end) as call_blocks_pts,
        sum(case when kpi_code = 8 then tier2 else 0 end) as emails_min,sum(case when kpi_code = 8 then tier1 else 0 end) as emails_pts,
        sum(case when kpi_code = 9 then tier2 else 0 end) as missed_calls_threshold,sum(case when kpi_code = 9 then tier1 else 0 end) as missed_calls_pts
        from kpis k
        inner join (
          select id,max(kpi_date) as kpi_date
          from kpis
          group by id
        ) mkc on k.id = mkc.id and k.kpi_date = mkc.kpi_date

    ), kpi_data as (
        select
        employee_id,employee_name,department,sum(business_day) as business_day,(sum(business_day) - sum(day_off)) as days_worked,round(sum(credits_value)::numeric,2) as credits,
        sum(time_avbl) as time_avbl,
        case when sum(business_day) = 0 then 0 else sum(time_avbl) / sum(business_day) end as time_avbl_avg,
        sum(inbounds) as inbounds,
        sum(outbounds) as outbounds,
        sum(missed) as missed,
        sum(activities) as activities,
        sum(sms) as sms,
        sum(looms) as looms,
        sum(call_block) as call_blocks,
        sum(emails) as emails
        from view_kpis_time_on_tasks e
        where date between '#{start_time}' and '#{end_time}'
        #{departments_sql}
        #{employee_ids_sql}
        group by employee_id, employee_name, department

    )
    select
    employee_id,employee_name,department,business_day,days_worked,credits,time_avbl,time_avbl_avg,
    inbounds,(case when (inbounds_min * (days_worked - credits)) = 0 then 0 else (inbounds / (inbounds_min * (days_worked - credits))) end * inbounds_pts) as inbounds_wpi,inbounds_pts,
    outbounds,(case when (outbounds_min * (days_worked - credits)) = 0 then 0 else (outbounds / (outbounds_min * (days_worked - credits))) end * outbounds_pts) as outbounds_wpi,outbounds_pts,
    activities,(case when (activities_min * (days_worked - credits)) = 0 then 0 else (activities / (activities_min * (days_worked - credits))) end * activities_pts) as activities_wpi,activities_pts,
    sms,(case when (sms_min * (days_worked - credits)) = 0 then 0 else (sms / (sms_min * (days_worked - credits))) end * sms_pts) as sms_wpi,sms_pts,
    looms,(case when (looms_min * (days_worked - credits)) = 0 then 0 else (looms / (looms_min * (days_worked - credits))) end * looms_pts) as looms_wpi,looms_pts,
    call_blocks,(case when (call_blocks_min * (days_worked - credits)) = 0 then 0 else (call_blocks / (call_blocks_min * (days_worked - credits))) end * call_blocks_pts) as call_blocks_wpi,call_blocks_pts,
    emails,(case when (emails_min * (days_worked - credits)) = 0 then 0 else (emails / (emails_min * (days_worked - credits))) end * emails_pts) as emails_wpi,emails_pts,
    missed,
    round(case when inbounds = 0 then 0 else (missed::numeric / inbounds::numeric) * 100 end, 1) as missed_calls_pct,
    case when (days_worked - credits) <= 0 then 0 when time_avbl = 0 then 0 when inbounds = 0 then missed_calls_pts when (missed::numeric / inbounds::numeric) * 100 <= missed_calls_threshold then missed_calls_pts else 0 end as missed_calls_wpi,
    missed_calls_pts
    from kpi_data kd, kpis_limits kl
    order by department, employee_name;
  SQL

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

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

Get data csv.

Parameters:

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

Returns:

  • (Object) 


121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'app/services/report/kpi_call/kpi_call.rb', line 121

def self.get_data_csv(start_time, end_time, departments_sql, employee_ids_sql)
  sql = <<-SQL.squish
    select
    employee_id,employee_name,department,(sum(business_day) - sum(day_off)) as days_worked,round(sum(credits_value)::numeric,2) as credits,
    sum(time_avbl) as time_avbl,
    sum(inbounds) as inbounds,
    sum(outbounds) as outbounds,
    sum(missed) as missed,
    round(case when sum(inbounds) = 0 then 0 else (sum(missed)::numeric / sum(inbounds)::numeric) * 100 end, 2) as missed_calls_pct,
    sum(activities) as activities,
    sum(sms) as sms,
    sum(looms) as looms,
    sum(call_block) as call_blocks,
    sum(emails) as emails
    from view_kpis_time_on_tasks e
    where date between '#{start_time}' and '#{end_time}'
    #{departments_sql}
    #{employee_ids_sql}
    group by employee_id, employee_name, department
    order by department, employee_name;
  SQL

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

.result_report(options = {}) ⇒ Result

Returns call KPI 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:



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/kpi_call/kpi_call.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 e.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

  data = get_data(start_date, end_date, departments_sql, employee_ids_sql)
  data_csv = get_data_csv(start_date, end_date, departments_sql, employee_ids_sql)

  Result.new(success: true, data: data, data_csv: data_csv, start_date: start_date, end_date: end_date)
end