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

Constants included from Models::Schedulable

Models::Schedulable::SIMPLE_FORM_OPTIONS

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::Schedulable

config

Methods included from Models::AfterCommittable

#after_commit

Methods included from Models::EventPublishable

#publish_event

Class Method Details

.call_block_times_employees ⇒ Array<Hash>

Returns:

  • (Array<Hash>) 


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

def self.call_block_times_employees
  sql = <<-SQL.squish
    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

  ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&: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) ⇒ Array<Hash>

Aggregate call/activity totals for one employee over a date range.

Parameters:

  • employee_id (Integer)
  • start_date (Date, String)
  • end_date (Date, String)
  • block (String) —

    block period label

Returns:

  • (Array<Hash>) 


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
148
149
150
151
152
153
154
155
 
# File 'app/models/call_block.rb', line 91

def self.getting_data(employee_id, start_date, end_date, block)
  sql = <<-SQL.squish
    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

  ActiveRecord::Base.lease_connection.execute(sql).to_a.map(&: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 ⇒ void

This method returns an undefined value.



187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'app/models/call_block.rb', line 187

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]} #{e[:st1]}", "#{d[0]} #{e[:et1]}", e[:b1]) if d[1] == wd
      getting_data(e[:employee_id], "#{d[0]} #{e[:st2]}", "#{d[0]} #{e[:et2]}", e[:b2]) if d[1] == wd
    end
  end
end

.import_new_data ⇒ void

This method returns an undefined value.



169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'app/models/call_block.rb', line 169

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]} #{e[:st1]}", "#{d[0]} #{e[:et1]}", e[:b1])
        getting_data(e[:employee_id], "#{d[0]} #{e[:st2]}", "#{d[0]} #{e[:et2]}", e[:b2])
      end
    end
  end
end

.list_of_dates ⇒ Array<Array(Date, Integer)>

Returns:

  • (Array<Array(Date, Integer)>) 


158
159
160
161
162
163
164
165
166
 
# File 'app/models/call_block.rb', line 158

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
  dates
end