Class: Report::CallBreakdown::Command

Inherits:
BaseCommand
  • Object
show all
Defined in:
app/services/report/call_breakdown/command.rb

Overview

Service object: command.

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.pretty_hour(hour) ⇒ Object

Pretty hour.

Parameters:

  • hour (Object)

Returns:

  • (Object) 


162
163
164
 
# File 'app/services/report/call_breakdown/command.rb', line 162

def self.pretty_hour(hour)
  Time.zone.local(2016, 1, 1, hour, 0, 0).strftime("%l %P").strip
end

Instance Method Details

#department_select_options ⇒ Object

Department select options.

Returns:

  • (Object) 


168
169
170
 
# File 'app/services/report/call_breakdown/command.rb', line 168

def department_select_options
  EmployeeRecord.department_select_options.insert(-1, 'Tech 24x7')
end

#departments=(new_departments) ⇒ Object

Set the departments.

Parameters:

  • new_departments (Object)

Returns:

  • (Object) 


181
182
183
 
# File 'app/services/report/call_breakdown/command.rb', line 181

def departments=(new_departments)
  super((new_departments || []).filter_map(&:presence).uniq)
end

#detail_departments ⇒ Object

Detail departments.

Returns:

  • (Object) 


187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'app/services/report/call_breakdown/command.rb', line 187

def detail_departments
  department = []
  department = departments if departments.present?
  department = EmployeeRecord.department_select_options.insert(-1, 'Tech 24x7') if (departments.present? == false) && (employee_ids.present? == false)
  if employee_ids.present?
    employee_id = employee_ids
    where_employee = ' and id in (' + employee_id.map { |emp| emp }.join(",") + ')'
    sql = <<-SQL.squish
    with employee(employee_id,full_name) as (
      select id as employee_id,full_name
      from parties
      where type = 'Employee'
      and inactive = false
      #{where_employee}
    ), departments (party_id,department) as (
      select party_id,department
      from employee_records
      where department is not null
    ), queue_members (member_account_id,member_name) as (
      select distinct member_account_id,member_name
      from queue_call_logs
      where queue_name = 'Tech 24x7'
      and member_account_id is not null
    ), phone_employee(employee_id,switchvox_account_id) as (
      select distinct employee_id,switchvox_account_id
      from employee_phone_statuses
    ), call_logs(call_direction,member_account_id,by_hour,num_records) as (
      select cast('inbound' as varchar) as call_direction,to_account_id as member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time))) as by_hour,count(*) as num_records
      from call_logs
      where cast(start_time as date) between '#{period1_gteq}' and '#{period1_lteq}'
      group by to_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time)))
      union all
      select cast('outbound' as varchar) as call_direction,from_account_id as member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time))) as by_hour,count(*) as num_records
      from call_logs
      where cast(start_time as date) between '#{period1_gteq}' and '#{period1_lteq}'
      group by from_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time)))
    ), queue_calls(member_account_id,by_hour,num_records) as (
      select member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time))) as by_hour,count(*) as num_records
      from queue_call_logs
      where queue_name = 'Tech 24x7'
      and cast(start_time as date) between '#{period1_gteq}' and '#{period1_lteq}'
      group by member_account_id,date_part('hour',  timezone('America/Chicago', timestamptz(start_time)))
    )
    select distinct department
    from (
      select department,call_direction,by_hour,sum(num_records) as num_records
      from employee em
      inner join departments dep on employee_id = party_id
      inner join phone_employee pe on em.employee_id = pe.employee_id
      inner join call_logs cl on pe.switchvox_account_id = cl.member_account_id
      group by department,call_direction,by_hour
      union all
      select cast('Tech 24x7' as varchar) as department,cast('inbound' as varchar) as call_direction,by_hour,sum(num_records) as num_records
      from employee em
      inner join queue_members qm on full_name = member_name
      inner join phone_employee pem on em.employee_id = pem.employee_id
      inner join queue_calls qc on qm.member_account_id = qc.member_account_id
      group by by_hour
    )a;
    SQL

    result = ActiveRecord::Base.lease_connection.execute(sql)
    summary_data = result.map(&:symbolize_keys!)
    department = summary_data.pluck(:department).uniq.compact_blank.sort
  end
  department
end

#employee_ids=(new_employee_ids) ⇒ Object

Set the employee ids.

Parameters:

  • new_employee_ids (Array<Integer>)

Returns:

  • (Object) 


258
259
260
 
# File 'app/services/report/call_breakdown/command.rb', line 258

def employee_ids=(new_employee_ids)
  super((new_employee_ids || []).filter_map(&:presence).uniq)
end

#employee_select_options ⇒ Object

Employee select options.

Returns:

  • (Object) 


174
175
176
 
# File 'app/services/report/call_breakdown/command.rb', line 174

def employee_select_options
  Employee.phone_employees_select_options
end

#employees_array ⇒ Object

Employees array.

Returns:

  • (Object) 


264
265
266
267
268
269
 
# File 'app/services/report/call_breakdown/command.rb', line 264

def employees_array
  employees = Employee.joins(:employee_record).order(:full_name)
  employees = employees.where(id: employee_ids) if employee_ids.present?
  employees = employees.where(employee_records: { department: departments }) if departments.present?
  EmployeePhoneStatus.joins(:employee).merge(employees).pluck(:full_name, :employee_id, :switchvox_account_id)
end

#execute ⇒ Object

Run the report with the given filters and store the result.

Returns:

  • (Object) 


17
18
19
20
21
22
23
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
49
50
51
52
53
54
55
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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'app/services/report/call_breakdown/command.rb', line 17

def execute
  return unless valid?

  inbound_queue_call_results = {}
  _departments = departments
  _employee_ids = employee_ids
  _employees_array = employees_array

  sql_employees = "with employee(employee_id,full_name) as (
                    select id as employee_id,full_name
                    from parties
                    where type = 'Employee'
                    and inactive = false
                    and id <> 154142
                  ), queue_members (member_account_id,member_name) as (
                    select distinct member_account_id,member_name
                    from queue_call_logs
                    where queue_name = 'Tech 24x7'
                    and member_account_id is not null
                  ), phone_employee(employee_id,switchvox_account_id) as (
                    select distinct employee_id,switchvox_account_id
                    from employee_phone_statuses
                  )
                  select full_name,em.employee_id,switchvox_account_id
                  from employee em
                  inner join queue_members on full_name = member_name
                  inner join phone_employee pem on em.employee_id = pem.employee_id"

  sql_calls = "with employee(employee_id,full_name) as (
                select id as employee_id,full_name
                from parties
                where type = 'Employee'
                and inactive = false
                and id <> 154142
              ), queue_members (member_account_id,member_name) as (
                select distinct member_account_id,member_name
                from queue_call_logs
                where queue_name = 'Tech 24x7'
                and member_account_id is not null
              ), phone_employee(employee_id,switchvox_account_id) as (
                select distinct employee_id,switchvox_account_id
                from employee_phone_statuses
              ), queue_calls(member_account_id,by_hour,num_records) as (
                select member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time))) as by_hour,count(*) as num_records
                from queue_call_logs
                where queue_name = 'Tech 24x7'
                and cast(start_time as date) between '#{period1_gteq}' and '#{period1_lteq}'
                group by member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time)))
              )
              select switchvox_account_id,by_hour,num_records
              from employee em
              inner join queue_members qm on full_name = member_name
              inner join phone_employee pem on em.employee_id = pem.employee_id
              inner join queue_calls qc on qm.member_account_id = qc.member_account_id
              order by switchvox_account_id,by_hour"

  flag_tech_24 = 1 if _departments.map { |e| e }.join(",") == "Tech 24x7"
  flag_dep = 0
  _departments.each do |i|
    flag_dep = if i == "Tech 24x7"
                 1
               else
                 0
               end
  end

  flag_dep = 2 if _departments.present? == false

  if flag_dep >= 1
    result_query_employees = ActiveRecord::Base.lease_connection.execute(sql_employees)
    result_query_employees.each { |r| _employees_array << [r['full_name'], r['employee_id'].to_i, r['switchvox_account_id'].to_i] }
    employees_selected = []

    if _employee_ids.present? == true
      _employee_ids.each do |id|
        _employees_array.each { |h| employees_selected.insert(-1, h) if h[1] == id }
      end
      _employees_array = employees_selected.uniq
    end
    _employees_array = _employees_array.uniq.sort_by { |a| a[0] }

    result_query_calls = ActiveRecord::Base.lease_connection.execute(sql_calls)
    result_query_calls.each { |x| inbound_queue_call_results = inbound_queue_call_results.merge({ [x['switchvox_account_id'].to_i, x['by_hour'].to_i] => x['num_records'].to_i }) }
  end

   = _employees_array.pluck(2) # pluck all switchvox_account_id for our query
  inbound_results = CallLog.where(to_account_id: ).where(start_time: period1_range).group(:to_account_id).group_by_hour_of_day(:start_time).count
  outbound_results = CallLog.where(from_account_id: ).where(start_time: period1_range).group(:from_account_id).group_by_hour_of_day(:start_time).count

  if flag_dep >= 1
    if flag_tech_24 == 1
      inbound_previous_results = inbound_results
      inbound_results = inbound_results.merge(inbound_queue_call_results) { |_k, old_v, new_v| old_v + new_v }
      inbound_results = inbound_results.merge(inbound_previous_results) { |_k, old_v, new_v| old_v - new_v }

      outbound_results = outbound_results.merge(outbound_results) { |_k, old_v, new_v| old_v - new_v }
    else
      inbound_results = inbound_results.merge(inbound_queue_call_results) { |_k, old_v, new_v| old_v + new_v }
    end
  end

  _employees_array.each do |employee_name, employee_id, |
    result_object = Report::CallBreakdown::Result.new(employee_id: employee_id, employee_name: employee_name, account_id: )
    (0..23).each do |hour|
      result_key = [, hour]
      result_object.inbound_breakdown << (inbound_results[result_key] || 0)
      result_object.outbound_breakdown << (outbound_results[result_key] || 0)
    end
    results << result_object
  end
  results
end

#period1_humanized ⇒ Object

Period1 humanized.

Returns:

  • (Object) 


346
347
348
 
# File 'app/services/report/call_breakdown/command.rb', line 346

def period1_humanized
  "From #{period1_range.first} until #{period1_range.last}"
end

#period1_range ⇒ Object

Period1 range.

Returns:

  • (Object) 


340
341
342
 
# File 'app/services/report/call_breakdown/command.rb', line 340

def period1_range
  (period1_gteq.beginning_of_day && period1_lteq.end_of_day && period1_gteq.beginning_of_day)..period1_lteq.end_of_day
end

#result_hours ⇒ Object

Result hours.

Returns:

  • (Object) 


146
147
148
149
150
151
 
# File 'app/services/report/call_breakdown/command.rb', line 146

def result_hours
  # Parse the results and return only the hour with value in them
  return [] if results.blank?

  (7..18).to_a # .select{|h| results.any?{|r| r.inbound_breakdown[h].positive? || r.outbound_breakdown[h].positive? }}
end

#result_hours_pretty ⇒ Object

Result hours pretty.

Returns:

  • (Object) 


155
156
157
 
# File 'app/services/report/call_breakdown/command.rb', line 155

def result_hours_pretty
  result_hours.map { |hour| self.class.pretty_hour(hour) }.insert(-1, 'AFTER HOURS').insert(-1, 'TOTALS')
end

#summary_table_and_graph ⇒ Object

Summary table and graph.

Returns:

  • (Object) 


273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'app/services/report/call_breakdown/command.rb', line 273

def summary_table_and_graph
  department = detail_departments
  employee_id = employee_ids
  where_employee = ' and id <> 154142'
  where_employee = ' and id in (' + employee_id.map { |emp| emp }.join(",") + ')' if employee_ids.present?
  where_department = ' where department in (' + department.map { |dep| "'#{dep}'" }.join(",") + ')'

  sql = <<-SQL.squish
  with employee(employee_id,full_name) as (
    select id as employee_id,full_name
    from parties
    where type = 'Employee'
    and inactive = false
    #{where_employee}
  ), departments (party_id,department) as (
    select party_id,department
    from employee_records
    where department is not null
  ), queue_members (member_account_id,member_name) as (
    select distinct member_account_id,member_name
    from queue_call_logs
    where queue_name = 'Tech 24x7'
    and member_account_id is not null
  ), phone_employee(employee_id,switchvox_account_id) as (
    select distinct employee_id,switchvox_account_id
    from employee_phone_statuses
  ), call_logs(call_direction,member_account_id,by_hour,num_records) as (
    select cast('inbound' as varchar) as call_direction,to_account_id as member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time))) as by_hour,count(*) as num_records
    from call_logs
    where cast(start_time as date) between '#{period1_gteq}' and '#{period1_lteq}'
    group by to_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time)))
    union all
    select cast('outbound' as varchar) as call_direction,from_account_id as member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time))) as by_hour,count(*) as num_records
    from call_logs
    where cast(start_time as date) between '#{period1_gteq}' and '#{period1_lteq}'
    group by from_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time)))
  ), queue_calls(member_account_id,by_hour,num_records) as (
    select member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time))) as by_hour,count(*) as num_records
    from queue_call_logs
    where queue_name = 'Tech 24x7'
    and cast(start_time as date) between '#{period1_gteq}' and '#{period1_lteq}'
    group by member_account_id,date_part('hour', timezone('America/Chicago', timestamptz(start_time)))
  )
  select department,call_direction,by_hour,num_records
  from (
    select department,call_direction,(by_hour - 1) as by_hour,sum(num_records) as num_records
    from employee em
    inner join departments dep on employee_id = party_id
    inner join phone_employee pe on em.employee_id = pe.employee_id
    inner join call_logs cl on pe.switchvox_account_id = cl.member_account_id
    group by department,call_direction,by_hour
    union all
    select cast('Tech 24x7' as varchar) as department,cast('inbound' as varchar) as call_direction,by_hour,sum(num_records) as num_records
    from employee em
    inner join queue_members qm on full_name = member_name
    inner join phone_employee pem on em.employee_id = pem.employee_id
    inner join queue_calls qc on qm.member_account_id = qc.member_account_id
    group by by_hour
  )a #{where_department};
  SQL

  result = ActiveRecord::Base.lease_connection.execute(sql)
  result.map(&:symbolize_keys!)
end

#to_csv ⇒ String

Render the report rows as CSV.

Returns:

  • (String) 


132
133
134
135
136
137
138
139
140
141
142
 
# File 'app/services/report/call_breakdown/command.rb', line 132

def to_csv
  return unless valid?

  execute
  hours_in_out = result_hours_pretty.map { |hour_pretty| ["#{hour_pretty} IN", "#{hour_pretty} OUT"] }.flatten
  attributes = hours_in_out.insert(0, "Employee")

  CSV.generate(headers: true) do |csv|
    csv << attributes
  end
end