Class: Analytic::PhoneRecordFact

Inherits:
Object
  • Object
show all
Includes:
Utility
Defined in:
app/reports/analytic/phone_record_fact.rb

Defined Under Namespace

Classes: QueueRepResult, Result

Class Method Summary collapse

Class Method Details

.get_call_results(start_date, end_date) ⇒ 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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'app/reports/analytic/phone_record_fact.rb', line 8

def self.get_call_results(start_date,end_date)

	 sql = <<-SQL
	    with outbound as (select
      e.full_name as rep,
      start_time::date as date,
      count(*) as out,
      sum(talk_duration) as out_talk
      from call_logs
      inner join parties e on e.id = call_logs.from_party_id and e.type = 'Employee' and e.inactive = 'f'
      where start_time >= '#{start_date}'
      and origination = 'outgoing'
      and talk_duration > 0
      and e.id in (55,490271,85,531953,8,155,46,6,70,1437798,531955,166896,2692081,49,93,69,2787310)
      group by date,e.full_name
      order by e.full_name,date)

      ,inbound as (select
      e.full_name as rep,
      start_time::date as date,
      count(*) as inb,
      sum(talk_duration) as inb_talk
      from call_logs
      inner join parties e on e.id = call_logs.to_party_id and e.type = 'Employee' and e.inactive = 'f'
      where start_time >= '#{start_date}'
      and origination = 'incoming'
      and talk_duration > 0
      and e.id in (55,490271,85,531953,8,155,46,6,70,1437798,531955,166896,2692081,49,93,69,2787310)
      and (from_party_id not in (select id from parties where parties.type = 'Employee') or from_party_id is null)
      group by date,e.full_name
      order by e.full_name,date)

      select
      distinct(d.date),d.weekday_name,r.rep,
      (select o.inb from inbound o where o.date = d.date and o.rep = r.rep) as inbound,
      (select o.out from outbound o where o.date = d.date and o.rep = r.rep) as outbound,
      (select TO_CHAR((o.inb_talk || ' second')::interval, 'HH24:MI:SS') from inbound o where o.date = d.date and o.rep = r.rep) as in_talk,
      (select TO_CHAR((o.out_talk || ' second')::interval, 'HH24:MI:SS') from outbound o where o.date = d.date and o.rep = r.rep) as out_talk
      from analytic_date_time_dimensions d,outbound r
      where d.date between '#{start_date}' and '#{end_date}'
      and d.weekend = 'Weekday'
      order by r.rep,d.date
    SQL
	results = ActiveRecord::Base.connection.execute(sql)

end

.get_queue_results(options = {}) ⇒ Object

start_date,end_date,queue,agent,grouping)



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
 
# File 'app/reports/analytic/phone_record_fact.rb', line 55

def self.get_queue_results(options = {}) #start_date,end_date,queue,agent,grouping)
  date_start = options[:period1_gteq]
  date_end = options[:period1_lteq]
  queue = options[:queue]
  agent = options[:agent]
  grouping = options[:grouping]



  sql1 = "WITH q as (
          select 1 as calls,
          case when outcome = 'redirected' then 1 else 0 end as r,
          case when (outcome = 'abandoned' OR (outcome = 'missed' and result_type = 'ABANDON') or (talk_time = 0 and member_extension is null)) then 1 else 0 end as a,
          case when outcome = 'completed' or (talk_time > 0 and member_extension is not null) then 1 else 0 end as c,
          case when outcome = 'missed' and result_type <> 'ABANDON' and enter_position is not null then 1 else 0 end as m,
          queue_name,dt.quarter,dt.year,dt.month,dt.day,dt.weekday_name,dt.week,dt.week_start_date,qcl.member_name,qcl.member_party_id,
          date_part('hour',timezone('America/Chicago', timestamptz(start_time))) as hour
          from queue_call_logs qcl
          inner join analytic_date_time_dimensions dt on dt.date = timezone('America/Chicago', timestamptz(qcl.start_time))::date "

  sql2 = "and dt.date between '#{date_start}' and '#{date_end}'"

  sql3 = "(sum(r) + sum(a) + sum(c) + sum(m)) as ttl_calls,sum(r) as redirects,sum(a) as abandoned,sum(c) as completed,sum(m) as missed
          from q "

  if queue.blank?
    sql_queue1 = ""
    sql_queue2 = ") select 'All' as queue_name,(select member_party_id from queue_call_logs where q.member_name = queue_call_logs.member_name limit 1) as party_id,member_name,#{grouping} as grouping,"
    sql_queue3 = "group by grouping,member_name
                  order by member_name,grouping"
  else
    sql_queue1 = "and queue_name = '#{queue}'"
    sql_queue2 = ") select queue_name,(select member_party_id from queue_call_logs where q.member_name = queue_call_logs.member_name limit 1) as party_id,member_name,#{grouping} as grouping,"
    sql_queue3 = "group by queue_name,grouping,member_name
                  order by queue_name,member_name,grouping"
  end

  if agent.blank?
    sql_agent1 = ""
  else
    sql_agent1 = "where member_name = '#{agent}'"
  end

  sql_final = sql1 + sql_queue1 + sql2 + sql_queue2 + sql3 + sql_agent1 + sql_queue3

	results = ActiveRecord::Base.connection.execute(sql_final)

  individuals = []
  summary = QueueRepResult.new
  # Iterate through the results. Create our individual record and a summary
  results.to_a.each do |r|
  csr = QueueRepResult.new(r)
  individuals << csr
  summary.grouping += csr.grouping || 0
  summary.ttl_calls += csr.ttl_calls || 0
  summary.redirects += csr.redirects || 0
  summary.abandoned += csr.abandoned || 0
  summary.completed += csr.completed || 0
  summary.missed += csr.missed || 0
end

Result.new(individuals: individuals, summary: summary, date_start: date_start, date_end: date_end)

end

.out_inb_stats(date) ⇒ Object 



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
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
 
# File 'app/reports/analytic/phone_record_fact.rb', line 120

def self.out_inb_stats(date)
  if date.wday == 0
    business_date = date.days_ago(2)
  elsif date.wday == 1
    business_date = date.days_ago(3)
  else
    business_date = date.days_ago(1)
  end
  date_start = business_date
  date_end = business_date
  start_time = business_date.beginning_of_day.to_fs(:db)
  end_time = business_date.end_of_day.to_fs(:db)

	 sql = <<-SQL
      WITH reps as (
        select party_id as id,full_name as member_name
        from employee_records e
        inner join parties p on p.id = e.party_id
        where p.type = 'Employee' and p.inactive = 'f'
        and e.department in ('Tech Support','Sales','Customer Service','Business Development')
        union all
        select party_id as id,full_name as member_name
        from employee_records e
        inner join parties p on p.id = e.party_id
        where p.type = 'Employee' and p.inactive = false
        and party_id = 4175246
      ), r as (
        select queue_name,member_name,member_party_id,1 as calls_presented,CASE WHEN outcome = 'completed' THEN 1 ELSE 0 END as cmp,
        CASE WHEN outcome = 'missed' and enter_position is not null and ring_time > 1 THEN 1 ELSE 0 END as miss,(talk_time) as talk_time,dt.date
        from queue_call_logs qcl
        inner join analytic_date_time_dimensions dt on dt.date = timezone('America/Chicago', timestamptz(qcl.start_time))::date
        where dt.date BETWEEN '#{date_start}' AND '#{date_end}'
        order by queue_name,member_name,dt.date
      ), o as (
        select e.id as rep,
        timezone('America/Chicago', timestamptz(start_time))::date as date,
        case when talk_duration >= 0 then 1 else 0 end as out,
        case when (from_party_id = to_party_id and talk_duration > 0 ) or (to_party_id is not null and to_party_id not in (select id from parties where type = 'Employee') and talk_duration > 0) then 1 else 0 end as cust_conn,
        case when (from_party_id = to_party_id and talk_duration = 0) or (to_party_id is not null and to_party_id not in (select id from parties where type = 'Employee') and talk_duration = 0) then 1 else 0 end as cust_non_conn,
        case when (to_party_id is null ) and talk_duration > 0  then 1 else 0 end as not_cust_conn,
        case when (to_party_id is null ) and talk_duration = 0  then 1 else 0 end as not_cust_non_conn,
        case when to_party_id in (select id from parties where type = 'Employee') and talk_duration > 0 then 1 else 0 end as ext_conn,
        case when to_party_id in (select id from parties where type = 'Employee') and talk_duration = 0 then 1 else 0 end as ext_non_conn,
        case when talk_duration = 0 then (case when total_duration > 10 then total_duration - 10 else 0 end) else talk_duration end as out_talk
        from call_logs
        inner join parties e on e.id = call_logs.from_party_id and e.type = 'Employee' and e.inactive = 'f'
        where origination = 'outgoing'
        --and talk_duration > 0
        and call_logs.start_time::date BETWEEN '#{start_time}' AND '#{end_time}'
        and e.id in (select id from reps)
        union all
        select e.id as rep,
        timezone('America/Chicago', timestamptz(start_time))::date as date,
        case when talk_duration >= 0 then 1 else 0 end as out,
        case when (from_party_id = to_party_id and talk_duration > 0 ) or (to_party_id is not null and to_party_id not in (select id from parties where type = 'Employee') and talk_duration > 0) then 1 else 0 end as cust_conn,
        case when (from_party_id = to_party_id and talk_duration = 0) or (to_party_id is not null and to_party_id not in (select id from parties where type = 'Employee') and talk_duration = 0) then 1 else 0 end as cust_non_conn,
        case when (to_party_id is null ) and talk_duration > 0  then 1 else 0 end as not_cust_conn,
        case when (to_party_id is null ) and talk_duration = 0  then 1 else 0 end as not_cust_non_conn,
        case when to_party_id in (select id from parties where type = 'Employee') and talk_duration > 0 then 1 else 0 end as ext_conn,
        case when to_party_id in (select id from parties where type = 'Employee') and talk_duration = 0 then 1 else 0 end as ext_non_conn,
        case when talk_duration = 0 then (case when total_duration > 10 then total_duration - 10 else 0 end) else talk_duration end as out_talk
        from call_logs
        inner join parties e on e.id = call_logs.from_party_id and e.type = 'Employee' and e.inactive = 'f'
        where origination = 'incoming'
        and "from" like 'WarmlyYours - %'
        and call_logs.start_time::date BETWEEN '#{start_time}' AND '#{end_time}'
        and e.id in (select id from reps)
        --order by e.full_name,date
      ), out as (
        select rep,date,sum(out) as ttl_out,sum(cust_conn) as customers_conn,sum(cust_non_conn) as customers_non_conn,sum(not_cust_conn) as not_cust_conn,
        sum(not_cust_non_conn) as not_cust_non_conn,sum(ext_conn) as ext_conn,sum(ext_non_conn) as ext_non_conn,TO_CHAR((sum(out_talk) || ' second')::interval, 'HH24:MI:SS') as talk
        from o group by rep,date order by rep,date
      ), inbound as (
        select e.full_name as rep,e.id,
        timezone('America/Chicago', timestamptz(start_time))::date as date,
        1 as inb,
        CASE WHEN call_logs.from_party_id is null or call_logs.from_party_id in (select id from parties where type = 'Employee') THEN 1 else 0 end as non_cust,
        case when talk_duration = 0 then (case when total_duration > 10 then total_duration - 10 else 0 end) else talk_duration end as inb_talk
        from call_logs
        inner join parties e on e.id = call_logs.to_party_id and e.type = 'Employee' and e.inactive = 'f'
        where start_time::date BETWEEN '#{start_time}' AND '#{end_time}'
        and origination = 'incoming'
        and talk_duration > 0
        and e.id in (select id from reps)
        and (from_party_id not in (select id from parties where parties.type = 'Employee') or from_party_id is null)
        and call_logs.from_number not in ((select caller_id_number from
        queue_call_logs where member_party_id = to_party_id and queue_call_logs.caller_id_number = call_logs.from_number and call_logs.start_time::date = queue_call_logs.start_time::date))
        order by e.full_name,date
      )
      select d.date,reps.member_name,
      (select ttl_out from out where rep = reps.id and out.date = d.date) as ttl_outbounds,
      (select customers_conn from out where rep = reps.id and out.date = d.date) as outbounds_to_customers_conn,
      (select customers_non_conn from out where rep = reps.id and out.date = d.date) as outbounds_to_customers_non_conn,
      (select not_cust_conn from out where rep = reps.id and out.date = d.date) as outbounds_to_non_customers_conn,
      (select not_cust_non_conn from out where rep = reps.id and out.date = d.date) as outbounds_to_non_customers_non_conn,
      (select ext_conn from out where rep = reps.id and out.date = d.date) as outbounds_to_extension_conn,
      (select ext_non_conn from out where rep = reps.id and out.date = d.date) as outbounds_to_extension_non_conn,
      (select TO_CHAR((talk || ' second')::interval, 'HH24:MI:SS') from out where rep = reps.id and out.date = d.date) as out_talk,
      coalesce((select sum(inb) from inbound i where i.id = reps.id and i.date = d.date),0)+coalesce((select sum(calls_presented) from r where r.member_party_id = reps.id and r.date = d.date), 0) as inbounds,
      (select sum(cmp) from r where r.member_party_id = reps.id and r.date = d.date) as queue_answered,
      coalesce((select sum(inb) from inbound i where i.id = reps.id and i.date = d.date),0) as direct_inbound_answered,
      (select sum(calls_presented) from r where r.member_party_id = reps.id and r.date = d.date) as queue_calls_presented,
      (select sum(miss) from r where r.member_party_id = reps.id and r.date = d.date) as queue_missed,
      coalesce((select sum(non_cust) from inbound i where i.id = reps.id and i.date = d.date),0) as direct_inbound_non_customer,
      TO_CHAR(((select sum(talk_time) from r where r.member_party_id = reps.id and r.date = d.date) || ' second')::interval, 'HH24:MI:SS') as queue_in_talk_time,
      TO_CHAR(((select sum(inb_talk) from inbound i where i.id = reps.id and i.date = d.date) || ' second')::interval, 'HH24:MI:SS') as direct_in_talk_time
      from reps,analytic_date_time_dimensions d
      where reps.member_name is not null
      and d.date BETWEEN '#{date_start}' AND '#{date_end}'
      order by reps.member_name,d.date
   SQL
	results = ActiveRecord::Base.connection.execute(sql)

end