Class: Report::TechCallsReport::TechCallsReport

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

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.data_detail(start_date, end_date) ⇒ Object 



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

def self.data_detail(start_date,end_date)
  sql = <<-SQL
    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

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

.data_summary(start_date, end_date) ⇒ Object 



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

def self.data_summary(start_date,end_date)
  sql = <<-SQL
    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

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

.total_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
34
35
36
37
38
 
# File 'app/services/report/tech_calls_report/tech_calls_report.rb', line 8

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