Class: Report::CallStatistics::Query

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

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.report(options = {}) ⇒ Object 



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
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
 
# File 'app/services/report/call_statistics/query.rb', line 9

def self.report(options = {})
  return Result.new(success: false, message: "Missing start date") unless options[:period1_gteq].present?
  return Result.new(success: false, message: "Missing end date") unless options[:period1_lteq].present?
  
  date_start = options[:period1_gteq]
  date_end = options[:period1_lteq]
  start_datetime = date_start.to_time.strftime('%F %T')
  end_datetime = date_end.to_time.strftime('%F %T')
  # start_time = options[:date_start].beginning_of_day.to_fs(:db)
  # end_time = options[:date_end].end_of_day.to_fs(:db)
  date_start_lm = date_start.last_month.beginning_of_month
  date_end_lm = date_start_lm.end_of_month

  if options[:departments].present?
    departments = options[:departments].map{|d| "'#{d}'"}.join(',')
    departments_sql = " and department is not null and department in (#{departments})"
  end

  if options[:employee_ids].present?
    employee_ids = options[: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,150282,6791406,6362291,85)"
  end

  sql = <<-SQL
    select
    employee_id,
    member_name,
    department,
    sum(ttl_outbounds) as ttl_outbounds,
    sum(outbounds_to_customers) as outbounds_to_customers,
    sum(outbounds_to_customers_conn) as outbounds_to_customers_conn,
    sum(outbounds_to_customers_non_conn) as outbounds_to_customers_non_conn,
    sum(outbounds_to_non_customers) as outbounds_to_non_customers,
    sum(outbounds_to_non_customers_conn) as outbounds_to_non_customers_conn,
    sum(outbounds_to_non_customers_non_conn) as outbounds_to_non_customers_non_conn,
    sum(outbounds_to_extension_conn) as outbounds_to_extension_conn,
    sum(outbounds_to_extension_non_conn) as outbounds_to_extension_non_conn,
    sum(outbound_talk_time_in_seconds) as outbound_talk_time_in_seconds,
    sum(ext_conn_talk_time_in_seconds) as ext_conn_talk_time_in_seconds,
    sum(inbounds) as inbounds,
    sum(inbounds_from_queue_answered) as inbounds_from_queue_answered,
    sum(inbound_direct_answered) as inbound_direct_answered,
    sum(inbound_from_queue_presented) as inbound_from_queue_presented,
    sum(inbound_from_queue_missed) as inbound_from_queue_missed,
    sum(inbound_direct_from_non_customer) as inbound_direct_from_non_customer,
    sum(inbound_from_queue_talk_time_in_seconds) as inbound_from_queue_talk_time_in_seconds,
    sum(inbound_direct_talk_time_in_seconds) as inbound_direct_talk_time_in_seconds
    from view_call_statistics
    where call_date between '#{start_datetime}' AND '#{end_datetime}'
    #{departments_sql}
    #{employee_ids_sql}
    group by employee_id,member_name,department
    order by member_name;
  SQL

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

  individuals = []
  summary = CallStatResultSummary.new
  # Iterate through the results. Create our individual record and a summary
  results.to_a.each do |r|
    csr = CallStatResult.new(r)
    individuals << csr
    summary.ttl_outbounds += csr.ttl_outbounds || 0
    summary.outbounds_to_customers += csr.outbounds_to_customers || 0
    summary.outbounds_to_customers_conn += csr.outbounds_to_customers_conn || 0
    summary.outbounds_to_customers_non_conn += csr.outbounds_to_customers_non_conn || 0
    summary.outbounds_to_non_customers += csr.outbounds_to_non_customers || 0
    summary.outbounds_to_non_customers_conn += csr.outbounds_to_non_customers_conn || 0
    summary.outbounds_to_non_customers_non_conn += csr.outbounds_to_non_customers_non_conn || 0
    summary.outbounds_to_extension_conn += csr.outbounds_to_extension_conn || 0
    summary.outbounds_to_extension_non_conn += csr.outbounds_to_extension_non_conn || 0
    summary.inbounds += csr.inbounds || 0
    summary.inbounds_from_queue_answered += csr.inbounds_from_queue_answered || 0
    summary.inbound_direct_answered += csr.inbound_direct_answered || 0
    summary.inbound_from_queue_presented += csr.inbound_from_queue_presented || 0
    summary.inbound_from_queue_missed += csr.inbound_from_queue_missed || 0
    summary.inbound_direct_from_non_customer += csr.inbound_direct_from_non_customer || 0
    summary.add_outbound_talk_time       csr.outbound_talk_time_in_seconds || 0
    summary.add_ext_conn_talk_time       csr.ext_conn_talk_time_in_seconds || 0
    summary.add_direct_inbound_talk_time csr.inbound_from_queue_talk_time_in_seconds || 0
    summary.add_queue_inbound_talk_time  csr.inbound_direct_talk_time_in_seconds || 0
  end

  Result.new(success: true, individuals: individuals, summary: summary, date_start: date_start, date_end: date_end)
end