Class: Report::TechCallsReport::TechCallsReport

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

Overview

Service object: tech calls report.

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.data_detail(start_date, end_date) ⇒ Object

Data detail.

Parameters:

  • start_date (Date)
  • end_date (Date)

Returns:

  • (Object) 


109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'app/services/report/tech_calls_report/tech_calls_report.rb', line 109

def self.data_detail(start_date, end_date)
  sql = <<-SQL.squish
    select qcl.id,timezone('America/Chicago', timestamptz(start_time)) as start_time,origination,qcl.caller_party_id,p.type,p.customer_id,coalesce(full_name,'Redirected from BPO America')::varchar as full_name,caller_id_number,
    queue_name,queue_extension,outcome,member_extension,member_name,talk_time,unique_id
    from queue_call_logs qcl
    left outer join parties p on qcl.caller_party_id = p.id
    where timezone('America/Chicago', timestamptz(start_time))::date between '#{start_date}' and '#{end_date}'
    and queue_extension = 600
    and member_extension is not null
    union all
    select cl.id,timezone('America/Chicago', timestamptz(cl.start_time)) as start_time,origination,from_party_id,p2.type,p2.customer_id,
    case when (coalesce((case when split_part(split_part(display,'(',2),')',1) = '18475503954' then 'Redirected from BPO America' else full_name end),"from") = 'Anatoliy Isayenko' or
              coalesce((case when split_part(split_part(display,'(',2),')',1) = '18475503954' then 'Redirected from BPO America' else full_name end),"from") = 'Scott Rosenbaum' or
              coalesce((case when split_part(split_part(display,'(',2),')',1) = '18475503954' then 'Redirected from BPO America' else full_name end),"from") = 'Christopher Sphar' or
              coalesce((case when split_part(split_part(display,'(',2),')',1) = '18475503954' then 'Redirected from BPO America' else full_name end),"from") = 'Jeff Burnett') then 'Redirected from BPO America'
         else coalesce((case when split_part(split_part(display,'(',2),')',1) = '18475503954' then 'Redirected from BPO America' else full_name end),"from") end as "from",
    from_number,
    case when to_account_id = 1160 then 'Anatoliy Isayenko'
        when to_account_id = 1193 then 'Scott Rosenbaum'
        when to_account_id = 1266 then 'Jeff Burnett'
        when to_account_id = 1156 then 'Christopher Sphar'
        when to_account_id = 1216 then 'BPO American' else '' end as  "to",
    602 as to_number,null as outcome,null as member_extension,null as member_name,talk_duration,cl.cdr_call_id
    from call_logs cl
    inner join call_log_events ce on cl.cdr_call_id = ce.cdr_call_id
    left outer join parties p2 on cl.from_party_id = p2.id
    where leg_type = 'INCOMING'
    and timezone('America/Chicago', timestamptz(cl.start_time))::date between '#{start_date}' and '#{end_date}'
    and talk_duration > 0
    and to_account_id = 1216
  SQL

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

.data_summary(start_date, end_date) ⇒ Object

Data summary.

Parameters:

  • start_date (Date)
  • end_date (Date)

Returns:

  • (Object) 


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
 
# File 'app/services/report/tech_calls_report/tech_calls_report.rb', line 64

def self.data_summary(start_date, end_date)
  sql = <<-SQL.squish
    select member_account_id,member_name,coalesce((sum(abandoned) + sum(completed) + sum(missed)),0) as total,coalesce(sum(abandoned),0) as abandoned,coalesce(sum(completed),0) as completed,coalesce(sum(missed),0) as missed
    from (
      select member_account_id,member_name,
      case when outcome = 'abandoned' then 1 else 0 end as abandoned,
      case when outcome = 'completed' then 1 else 0 end as completed,
      case when outcome = 'missed' then 1 else 0 end as missed
      from queue_call_logs
      where timezone('America/Chicago', timestamptz(start_time))::date between '#{start_date}' and '#{end_date}'
      and queue_extension = 600
      and member_extension is not null
      union all
      select 1160 as member_account_id,'Anatoliy Isayenko' as member_name,0,0,0
      union all
      select 1266 as member_account_id,'Jeff Burnett' as member_name,0,0,0
      union all
      select 1156 as member_account_id,'Christopher Sphar' as member_name,0,0,0
      union all
      select 1193 as member_account_id,'Scott Rosenbaum' as member_name,0,0,0
      union all
      select to_account_id,
      case when to_account_id = 1160 then 'Anatoliy Isayenko'
           when to_account_id = 1193 then 'Scott Rosenbaum'
           when to_account_id = 1266 then 'Jeff Burnett'
           when to_account_id = 1156 then 'Christopher Sphar'
           when to_account_id = 1216 then 'BPO American' else '' end as member_name,
      0 as abandoned,1 as completed,0 as missed
      from call_logs cl
      inner join call_log_events ce on cl.cdr_call_id = ce.cdr_call_id
      where leg_type = 'INCOMING'
      and timezone('America/Chicago', timestamptz(cl.start_time))::date between '#{start_date}' and '#{end_date}'
      and talk_duration > 0
      and to_account_id = 1216
    )a group by member_account_id,member_name
    order by 1;
  SQL

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

.total_report(options = {}) ⇒ Result

Returns tech call summary and detail 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

Returns:

  • (Result) —

    tech call summary and detail data



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
 
# File 'app/services/report/tech_calls_report/tech_calls_report.rb', line 28

def self.total_report(options = {})
  summary = data_summary(options[:period1_gteq], options[:period1_lteq])
  summary = (summary + [{ member_account_id: '1216', member_name: 'BPO American', total: '0', abandoned: '0', completed: '0', missed: '0' }]) if summary.count == 4
  detail = data_detail(options[:period1_gteq], options[:period1_lteq])
  cs_total_calls = []
  sr_total_calls = []
  ai_total_calls = []
  jb_total_calls = []
  detail.each do |bpo|
    cs_total_calls << bpo if bpo[:member_name] == 'Christopher Sphar' || bpo[:queue_name] == 'Christopher Sphar'
    sr_total_calls << bpo if bpo[:member_name] == 'Scott Rosenbaum' || bpo[:queue_name] == 'Scott Rosenbaum'
    ai_total_calls << bpo if bpo[:member_name] == 'Anatoliy Isayenko' || bpo[:queue_name] == 'Anatoliy Isayenko'
    jb_total_calls << bpo if bpo[:member_name] == 'Jeff Burnett' || bpo[:queue_name] == 'Jeff Burnett'
  end
  bpo_not_redirected = []
  bpo_redirected = []
  detail.each do |bpo|
    bpo_not_redirected << bpo if bpo[:queue_name] == 'BPO American'
    bpo_redirected << bpo if bpo[:queue_name] != 'BPO American'
  end

  Result.new(success: true,
             summary: summary,
             detail: detail,
             cs_total_calls: cs_total_calls,
             sr_total_calls: sr_total_calls,
             ai_total_calls: ai_total_calls,
             jb_total_calls: jb_total_calls,
             bpo_not_redirected: bpo_not_redirected,
             bpo_redirected: bpo_redirected)
end