Class: CallBlock

Inherits:
ApplicationRecord show all
Includes:
Models::Auditable
Defined in:
app/models/call_block.rb

Overview

== Schema Information

Table name: call_blocks
Database name: primary

id :integer not null, primary key
block_period :string
call_block_date :date not null
ttl_activities :integer
ttl_outbounds :integer
employee_id :integer not null

Indexes

index_call_blocks_on_block_period (block_period)
index_call_blocks_on_call_block_date (call_block_date)
index_call_blocks_on_employee_id (employee_id)

Constant Summary

Constants included from Models::Auditable

Models::Auditable::ALWAYS_IGNORED

Class Method Summary collapse

Methods included from Models::Auditable

#all_skipped_columns, #audit_reference_data, #creator, #should_not_save_version, #stamp_record, #updater

Methods inherited from ApplicationRecord

ransackable_associations, ransackable_attributes, ransackable_scopes, ransortable_attributes, #to_relation

Methods included from Models::EventPublishable

#publish_event

Class Method Details

.call_block_times_employees ⇒ Object 



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
 
# File 'app/models/call_block.rb', line 22

def self.call_block_times_employees
  sql = <<-SQL
    WITH reps as (
        select party_id as id,full_name as member_name,department,company_id
        from employee_records e
        inner join parties p on p.id = e.party_id
        where p.type = 'Employee'
        and p.inactive = false
        and p.full_name <> 'www.warmlyyours.com'
        and e.department in ('Customer Service','Sales')
        and p.id not in (116,6791405,5620978,69,155,150282,6791406,6362291,85,92)

    ), work_hours as (
        select employee_id,1::int as week_day,hours -> 'mon' ->> 'work' as schedule,
        case when length(hours -> 'mon' ->> 'work') = 18 then ((DATE_PART('hour',(substr((hours -> 'mon' ->> 'work')::varchar,12,5)::time - substr((hours -> 'mon' ->> 'work')::varchar,3,5)::time)) * 3600) + (DATE_PART('min',(substr((hours -> 'mon' ->> 'work')::varchar,12,5)::time - substr((hours -> 'mon' ->> 'work')::varchar,3,5)::time)) * 60))::numeric
          when length(hours -> 'mon' ->> 'work') > 18 then (((DATE_PART('hour',(substr((hours -> 'mon' ->> 'work')::varchar,12,5)::time - substr((hours -> 'mon' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'mon' ->> 'work')::varchar,30,5)::time - substr((hours -> 'mon' ->> 'work')::varchar,21,5)::time))) * 3600) +  ((DATE_PART('min',(substr((hours -> 'mon' ->> 'work')::varchar,12,5)::time - substr((hours -> 'mon' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'mon' ->> 'work')::varchar,30,5)::time - substr((hours -> 'mon' ->> 'work')::varchar,21,5)::time))) * 60))::numeric else 0 end as work_secs
        from employee_work_schedules ew
        inner join reps re on ew.employee_id = re.id
        union all
        select employee_id,2::int as week_day,hours -> 'tue' ->> 'work' as schedule,
        case when length(hours -> 'tue' ->> 'work') = 18 then ((DATE_PART('hour',(substr((hours -> 'tue' ->> 'work')::varchar,12,5)::time - substr((hours -> 'tue' ->> 'work')::varchar,3,5)::time)) * 3600) + (DATE_PART('min',(substr((hours -> 'tue' ->> 'work')::varchar,12,5)::time - substr((hours -> 'tue' ->> 'work')::varchar,3,5)::time)) * 60))::numeric
          when length(hours -> 'tue' ->> 'work') > 18 then (((DATE_PART('hour',(substr((hours -> 'tue' ->> 'work')::varchar,12,5)::time - substr((hours -> 'tue' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'tue' ->> 'work')::varchar,30,5)::time - substr((hours -> 'tue' ->> 'work')::varchar,21,5)::time))) * 3600) +  ((DATE_PART('min',(substr((hours -> 'tue' ->> 'work')::varchar,12,5)::time - substr((hours -> 'tue' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'tue' ->> 'work')::varchar,30,5)::time - substr((hours -> 'tue' ->> 'work')::varchar,21,5)::time))) * 60))::numeric else 0 end as work_secs
        from employee_work_schedules ew
        inner join reps re on ew.employee_id = re.id
        union all
        select employee_id,3::int as week_day,hours -> 'wed' ->> 'work' as schedule,
        case when length(hours -> 'wed' ->> 'work') = 18 then ((DATE_PART('hour',(substr((hours -> 'wed' ->> 'work')::varchar,12,5)::time - substr((hours -> 'wed' ->> 'work')::varchar,3,5)::time)) * 3600) + (DATE_PART('min',(substr((hours -> 'wed' ->> 'work')::varchar,12,5)::time - substr((hours -> 'wed' ->> 'work')::varchar,3,5)::time)) * 60))::numeric
          when length(hours -> 'wed' ->> 'work') > 18 then (((DATE_PART('hour',(substr((hours -> 'wed' ->> 'work')::varchar,12,5)::time - substr((hours -> 'wed' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'wed' ->> 'work')::varchar,30,5)::time - substr((hours -> 'wed' ->> 'work')::varchar,21,5)::time))) * 3600) +  ((DATE_PART('min',(substr((hours -> 'wed' ->> 'work')::varchar,12,5)::time - substr((hours -> 'wed' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'wed' ->> 'work')::varchar,30,5)::time - substr((hours -> 'wed' ->> 'work')::varchar,21,5)::time))) * 60))::numeric else 0 end as work_secs
        from employee_work_schedules ew
        inner join reps re on ew.employee_id = re.id
        union all
        select employee_id,4::int as week_day,hours -> 'thu' ->> 'work' as schedule,
        case when length(hours -> 'thu' ->> 'work') = 18 then ((DATE_PART('hour',(substr((hours -> 'thu' ->> 'work')::varchar,12,5)::time - substr((hours -> 'thu' ->> 'work')::varchar,3,5)::time)) * 3600) + (DATE_PART('min',(substr((hours -> 'thu' ->> 'work')::varchar,12,5)::time - substr((hours -> 'thu' ->> 'work')::varchar,3,5)::time)) * 60))::numeric
          when length(hours -> 'thu' ->> 'work') > 18 then (((DATE_PART('hour',(substr((hours -> 'thu' ->> 'work')::varchar,12,5)::time - substr((hours -> 'thu' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'thu' ->> 'work')::varchar,30,5)::time - substr((hours -> 'thu' ->> 'work')::varchar,21,5)::time))) * 3600) +  ((DATE_PART('min',(substr((hours -> 'thu' ->> 'work')::varchar,12,5)::time - substr((hours -> 'thu' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'thu' ->> 'work')::varchar,30,5)::time - substr((hours -> 'thu' ->> 'work')::varchar,21,5)::time))) * 60))::numeric else 0 end as work_secs
        from employee_work_schedules ew
        inner join reps re on ew.employee_id = re.id
        union all
        select employee_id,5::int as week_day,hours -> 'fri' ->> 'work' as schedule,
        case when length(hours -> 'fri' ->> 'work') = 18 then ((DATE_PART('hour',(substr((hours -> 'fri' ->> 'work')::varchar,12,5)::time - substr((hours -> 'fri' ->> 'work')::varchar,3,5)::time)) * 3600) + (DATE_PART('min',(substr((hours -> 'fri' ->> 'work')::varchar,12,5)::time - substr((hours -> 'fri' ->> 'work')::varchar,3,5)::time)) * 60))::numeric
          when length(hours -> 'fri' ->> 'work') > 18 then (((DATE_PART('hour',(substr((hours -> 'fri' ->> 'work')::varchar,12,5)::time - substr((hours -> 'fri' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'fri' ->> 'work')::varchar,30,5)::time - substr((hours -> 'fri' ->> 'work')::varchar,21,5)::time))) * 3600) +  ((DATE_PART('min',(substr((hours -> 'fri' ->> 'work')::varchar,12,5)::time - substr((hours -> 'fri' ->> 'work')::varchar,3,5)::time) + (substr((hours -> 'fri' ->> 'work')::varchar,30,5)::time - substr((hours -> 'fri' ->> 'work')::varchar,21,5)::time))) * 60))::numeric else 0 end as work_secs
        from employee_work_schedules ew
        inner join reps re on ew.employee_id = re.id

    )
    select employee_id,week_day,
    case when length(schedule) = 18 then substr(schedule,3,5)
      when length(schedule) > 18 then substr(schedule,3,5) else '0' end as st1,
    case when length(schedule) = 18 then substr((substr(schedule,3,5)::time + (((work_secs / 2) / 3600)::varchar || ' hours')::interval)::varchar,1,5)
      when length(schedule) > 18 then substr(schedule,12,5) else '0' end as et1,'morning' as b1,
    case when length(schedule) = 18 then substr((substr(schedule,3,5)::time + (((work_secs / 2) / 3600)::varchar || ' hours')::interval + '1 min'::interval)::varchar,1,5)
      when length(schedule) > 18 then substr(schedule,21,5) else '0' end as st2,
    case when length(schedule) = 18 then substr(schedule,12,5)
      when length(schedule) > 18 then substr(schedule,30,5) else '0' end as et2,'afternoon' as b2
    from work_hours
    where length(schedule) >= 18;
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }.map{ |r| {employee_id: r[:employee_id].to_i, week_day: r[:week_day].to_i, st1: r[:st1], et1: r[:et1], b1: r[:b1], st2: r[:st2], et2: r[:et2], b2: r[:b2]} }
end

.getting_data(employee_id, start_date, end_date, block) ⇒ Object 



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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'app/models/call_block.rb', line 82

def self.getting_data(employee_id,start_date,end_date,block)
  new_data = []
  sql = <<-SQL
    WITH reps as (
      select party_id as id,full_name as member_name,department,company_id
      from employee_records e
      inner join parties p on p.id = e.party_id
      where p.id = #{employee_id}
    ), 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,
      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'
      inner join reps on e.id = reps.id
      where timezone('America/Chicago', timestamptz(start_time))::date between '#{start_date}' and '#{end_date}'
      and origination = 'outgoing'
      order by e.full_name,date

    ), out as (
      select rep,date,
      sum(out) as ttl_out,
      sum(out_talk) as talk
      from o
      group by rep,date
      order by rep,date

    ), reps_dates as (
      select reps.id as employee_id,reps.member_name,reps.department,date,company_id
      from reps, analytic_date_time_dimensions
      where date between '#{start_date}'::date and '#{end_date}'::date

    ), activities as (
      select closed_by_id,count(id) as activities
      from activities
      where timezone('America/Chicago', timestamptz(completion_datetime))::date between '#{start_date}' and '#{end_date}'
      and activity_result_type_id > 1
      group by closed_by_id

    )
    select
    '#{start_date}'::date as call_block_date,
    employee_id,
    sum(coalesce(ttl_outbounds,0)) as ttl_outbounds,
    coalesce(act.activities,0) as ttl_activities,
    '#{block}' as block_period
    from (
      select
      rd.employee_id,
      rd.member_name,
      rd.department,
      rd.company_id,
      sum(ttl_out)::int as ttl_outbounds,
      sum(talk)::int as outbound_talk_time_in_seconds
      from reps_dates rd
      left join out on rd.employee_id = out.rep and rd.date = out.date
      where rd.member_name is not null
      group by rd.employee_id,rd.member_name,rd.department,rd.company_id
    )a
    left join activities act on a.employee_id = act.closed_by_id
    group by employee_id,act.activities;
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }.map{ |r| {call_block_date: r[:call_block_date].to_date,employee_id: r[:employee_id].to_i, ttl_outbounds: r[:ttl_outbounds].to_i, ttl_activities: r[:ttl_activities].to_i, block_period: r[:block_period]} }
end

.import_daily_data ⇒ Object 



175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'app/models/call_block.rb', line 175

def self.import_daily_data
  emp_call_block_times = call_block_times_employees
  d = [Date.current.days_ago(1),Date.current.days_ago(1).wday]
  (1..5).each do |wd|
    cbt = []
    emp_call_block_times.map{ |r| cbt << r if r[:week_day] == wd}
    cbt.each do |e|
      getting_data(e[:employee_id],(d[0].to_s + ' ' + e[:st1]),(d[0].to_s + ' ' + e[:et1]),e[:b1]) if d[1] == wd
      getting_data(e[:employee_id],(d[0].to_s + ' ' + e[:st2]),(d[0].to_s + ' ' + e[:et2]),e[:b2]) if d[1] == wd
    end
  end
end

.import_new_data ⇒ Object 



159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'app/models/call_block.rb', line 159

def self.import_new_data
  emp_call_block_times = call_block_times_employees
  dates = list_of_dates
  (1..5).each do |wd|
    cbt = []; dt = []
    emp_call_block_times.map{ |r| cbt << r if r[:week_day] == wd}
    dates.map{ |r| dt << r if r[1] == wd}
    dt.each do |d|
      cbt.each do |e|
        getting_data(e[:employee_id],(d[0].to_s + ' ' + e[:st1]),(d[0].to_s + ' ' + e[:et1]),e[:b1])
        getting_data(e[:employee_id],(d[0].to_s + ' ' + e[:st2]),(d[0].to_s + ' ' + e[:et2]),e[:b2])
      end
    end
  end
end

.list_of_dates ⇒ Object 



149
150
151
152
153
154
155
156
157
 
# File 'app/models/call_block.rb', line 149

def self.list_of_dates
  start_date = Date.current.beginning_of_year.beginning_of_week.days_ago(1)
  end_date = Date.current.days_ago(1)
  dates = []
  (start_date..end_date).each do |d|
    dates << [d,d.wday] if (d.wday.positive? && d.wday < 6)
  end
  return dates
end