· 7 years ago · Oct 04, 2018, 09:34 PM
1(139.9ms) SET NAMES utf8 COLLATE utf8_unicode_ci, @@SESSION.sql_mode = 'NO_ENGINE_SUBSTITUTION', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2 AutopopulateStepStatus Load (137.5ms) SELECT `autopopulate_step_statuses`.* FROM `autopopulate_step_statuses` WHERE `autopopulate_step_statuses`.`id` = 11686 LIMIT 1
3 Client Load (290.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
4 CalendarSchedule Load (149.0ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`id` = 1381 LIMIT 1
5 Block Load (145.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`id` = 29261 LIMIT 1
6 AutopopulateStep Load (140.1ms) SELECT `autopopulate_steps`.* FROM `autopopulate_steps` WHERE `autopopulate_steps`.`calendar_schedule_id` = 1381 AND `autopopulate_steps`.`step` = 3 ORDER BY autopopulate_steps.step ASC, autopopulate_steps.display_order ASC
7 PrepopulateRule Exists (140.6ms) SELECT 1 AS one FROM `prepopulate_rules` WHERE `prepopulate_rules`.`autopopulate_step_id` = 1729 LIMIT 1
8 BlockSchedule Load (137.3ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
9ClientTools::SchedulesPublisher.set_options({:timeframe=>1})
10 CallType Load (140.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 319 AND `call_types`.`calendar_schedule_id` = 1381 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
11 TargetGroupCall Load (138.4ms) SELECT `target_group_calls`.* FROM `target_group_calls` WHERE `target_group_calls`.`call_type_id` IN (12692, 12693, 12694)
12 (151.4ms) select all_cts.call_type_id,
13 cts_masks.assignment_type,
14 cts_masks.assignment_id,
15 cts_masks.own_n_sc_dow_mask
16 from (
17 select
18 if(tgc.target_group_id is null, 'CallType', 'TargetGroup') AS assignment_type,
19 ifnull(tgc.target_group_id, ct.id) AS assignment_id,
20 ct.id as call_type_id
21 from call_types as ct
22 left join target_group_calls as tgc
23 on tgc.call_type_id = ct.id
24 where ct.calendar_schedule_id = 1381
25 and ct.active = 1
26 ) as all_cts
27 left join (
28 select
29 if(tgc.target_group_id is null, 'CallType', 'TargetGroup') AS assignment_type,
30 ifnull(tgc.target_group_id, ct.call_type_id) AS assignment_id,
31 BIT_OR(ct.own_n_sc_dow_mask) AS own_n_sc_dow_mask
32 from (
33 SELECT
34 ct1.id AS call_type_id,
35 (BIT_OR(1 << (sc.dayofweek_on - 1)) | ct1.days_of_week_mask) AS own_n_sc_dow_mask
36 FROM call_types AS ct1
37 LEFT JOIN schedules AS sc USE INDEX (ix_schedules_call_date)
38 ON sc.call_type_id = ct1.id
39 AND sc.schedule_on BETWEEN '2019-01-01' AND '2019-01-31'
40 WHERE ct1.calendar_schedule_id = 1381
41 AND ct1.active = 1
42 GROUP BY ct1.id
43 ) as ct
44 left join target_group_calls as tgc
45 on tgc.call_type_id = ct.call_type_id
46 group by assignment_type, assignment_id
47 ) as cts_masks
48 on cts_masks.assignment_type = all_cts.assignment_type
49 and cts_masks.assignment_id = all_cts.assignment_id
50
51 CallType Load (144.9ms) /* UNPUBLISHED CALL TYPES */
52 SELECT DISTINCT ct.*,
53 s_ct.own_n_sc_dow_mask AS own_n_sc_dow_mask
54 FROM call_types AS ct
55 INNER JOIN (
56 SELECT
57 12692 AS call_type_id,
58 12692 AS assignment_id,
59 'CallType' AS assignment_type,
60 127 AS own_n_sc_dow_mask
61 UNION SELECT
62 12693 AS call_type_id,
63 12693 AS assignment_id,
64 'CallType' AS assignment_type,
65 127 AS own_n_sc_dow_mask
66 UNION SELECT
67 12694 AS call_type_id,
68 12694 AS assignment_id,
69 'CallType' AS assignment_type,
70 127 AS own_n_sc_dow_mask
71
72 ) s_ct ON s_ct.call_type_id = ct.id
73 LEFT JOIN published_blocks AS pb
74 ON pb.client_id = ct.client_id
75 AND pb.calendar_schedule_id = ct.calendar_schedule_id
76 AND pb.block_id = 29261
77 LEFT JOIN published_block_call_types AS pbct
78 ON pbct.client_id = ct.client_id
79 AND pbct.published_block_id = pb.id
80 AND pbct.assignment_type = s_ct.assignment_type
81 AND pbct.assignment_id = s_ct.assignment_id
82 WHERE ct.client_id = 319
83 AND ct.calendar_schedule_id = 1381
84 AND ct.active = 1
85 AND s_ct.own_n_sc_dow_mask > 0
86 AND (
87 pb.id IS NULL
88 OR (
89 (pb.assignment_ids IS NOT NULL OR pb.days_of_week_mask != 127)
90 AND (
91 pbct.id IS NULL
92 OR (s_ct.own_n_sc_dow_mask & pbct.days_of_week_mask) != s_ct.own_n_sc_dow_mask
93 )
94 )
95 )
96 ORDER BY display_order ASC, abbreviation ASC
97
98 CallType Load (424.2ms) SELECT `call_types`.* FROM `call_types` INNER JOIN `autopopulate_step_call_types` ON `call_types`.`id` = `autopopulate_step_call_types`.`call_type_id` WHERE `autopopulate_step_call_types`.`autopopulate_step_id` = 1729
99 CalendarSchedule Load (143.0ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
100 Block Load (137.7ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND `blocks`.`id` = 29261 ORDER BY start_date asc, end_date asc LIMIT 1
101 BlockSchedule Load (139.1ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
102 PopulationTimeframe Load (139.5ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 319 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
103 RuleSet Load (136.4ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1461 LIMIT 1
104 Block Load (138.6ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND (blocks.start_date >= '2019-01-01' AND blocks.end_date <= '2019-01-31') ORDER BY start_date asc, end_date asc
105INFO : (10-04 13:41:26) phase: Scheduler::Base
106INFO : (10-04 13:41:26) from: 2019-01-01 to: 2019-01-31
107 Rule Load (163.0ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1461 ORDER BY applied desc, weight desc
108INFO : (10-04 13:41:26) rule configurations: Default
109 -
110INFO : (10-04 13:41:26) started: Scheduler::Cache::Collector.collect_all
111INFO : (10-04 13:41:26) collect_staffs
112 Staff Load (143.4ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `assign_staff_types` ON `assign_staff_types`.`staff_id` = `staffs`.`id` INNER JOIN `staff_types` ON `staff_types`.`id` = `assign_staff_types`.`staff_type_id` WHERE `staffs`.`client_id` = 319 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
113 Client Load (276.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319
114 StaffSpecialization Load (137.2ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (20705, 20703, 20720, 20704, 20700, 20695, 20722, 20701, 20697, 20699, 20698, 20728, 20706, 20696)
115 Specialization Load (141.3ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` IN (711, 712) ORDER BY `specializations`.`priority` ASC
116 StaffCallType Load (147.4ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (20705, 20703, 20720, 20704, 20700, 20695, 20722, 20701, 20697, 20699, 20698, 20728, 20706, 20696)
117INFO : (10-04 13:41:29) initial eligibles: [20705,20703,20720,20704,20700,20695,20722,20701,20697,20699,20698,20728,20706,20696]
118INFO : (10-04 13:41:29) collect_call_types
119 CallType Load (139.0ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 319 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
120INFO : (10-04 13:41:29) initial call types: [12692,12693,12694,12708,12706,12707,12711]
121INFO : (10-04 13:41:29) collect_chain_links
122 ChainLink Load (138.5ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 319
123INFO : (10-04 13:41:30) collect_linkages
124INFO : (10-04 13:41:30) collect_schedules
125 Schedule Load (139.3ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 319 AND (`schedules`.`schedule_on` BETWEEN '2019-01-01' AND '2019-01-31')
126INFO : (10-04 13:41:30) collect_slots
127INFO : (10-04 13:41:30) collect_approved_call_requests
128 Request Load (140.0ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 319 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2019-01-01' AND '2019-01-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
129INFO : (10-04 13:41:30) collect_approved_no_call_requests
130 Request Load (139.9ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 319 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2019-01-01' AND '2019-01-31') ORDER BY requests.schedule_on asc, requests.created_at asc
131INFO : (10-04 13:41:31) finished: Scheduler::Cache::Collector.collect_all
132INFO : (10-04 13:41:31) == begin to process requests
133 CallType Load (142.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 1381 AND `call_types`.`active` = 1
134 (166.4ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 1381 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
135 Request Load (141.6ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 319 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (12692, 12693, 12694) AND (requests.schedule_on > '2018-12-31')
136 Schedule Load (141.2ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`schedule_on` = '2019-06-19' AND `schedules`.`staff_id` = 20721
137 RequestCallType Load (145.1ms) SELECT `request_call_types`.* FROM `request_call_types` WHERE `request_call_types`.`request_id` = 1966700 AND (request_call_types.second_call_type_id IS NULL)
138 SQL (157.8ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 20695 WHERE `schedules`.`client_id` = 319 AND 1=0
139INFO : (10-04 13:41:32) == end of requests processing
140INFO : (10-04 13:41:32) == begin prepopulate process
141 Block Load (142.7ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND (blocks.start_date <= '2019-01-31' AND blocks.end_date >= '2019-01-31') ORDER BY start_date asc, end_date asc LIMIT 1
142 Client Load (279.7ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
143INFO : (10-04 13:41:33) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
144INFO : (10-04 13:41:33) <-- clear: 1 - Scheduler::AutoPopulate::Queries::Query001_PR_01
145INFO : (10-04 13:41:33) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query001_PR_02
146INFO : (10-04 13:41:33) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query001_PR_03
147INFO : (10-04 13:41:33) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query001_PR_04
148INFO : (10-04 13:41:33) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query001_PR_05
149INFO : (10-04 13:41:33) <-- clear: 6 - Scheduler::AutoPopulate::Queries::Query005
150INFO : (10-04 13:41:33) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query01
151INFO : (10-04 13:41:33) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query012
152INFO : (10-04 13:41:33) <-- clear: 9 - Scheduler::AutoPopulate::Queries::QueryTargets
153INFO : (10-04 13:41:33) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query013
154INFO : (10-04 13:41:33) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query014
155INFO : (10-04 13:41:33) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query015_PR
156INFO : (10-04 13:41:33) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query016
157INFO : (10-04 13:41:33) <-- clear: 14 - Scheduler::AutoPopulate::Queries::Query02
158INFO : (10-04 13:41:33) <-- clear: 15 - Scheduler::AutoPopulate::Queries::Query03
159INFO : (10-04 13:41:33) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query04
160INFO : (10-04 13:41:33) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query045
161INFO : (10-04 13:41:33) <-- clear: 18 - Scheduler::AutoPopulate::Queries::QueryA
162INFO : (10-04 13:41:33) <-- clear: 19 - Scheduler::AutoPopulate::Queries::QueryB
163INFO : (10-04 13:41:33) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query05
164INFO : (10-04 13:41:33) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query06
165INFO : (10-04 13:41:33) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query07
166INFO : (10-04 13:41:33) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0702
167INFO : (10-04 13:41:33) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0705
168INFO : (10-04 13:41:33) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query0706
169INFO : (10-04 13:41:33) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query0707
170INFO : (10-04 13:41:33) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query0708
171INFO : (10-04 13:41:33) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query0709
172INFO : (10-04 13:41:33) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query08
173INFO : (10-04 13:41:33) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query085
174INFO : (10-04 13:41:33) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query09_PR
175INFO : (10-04 13:41:33) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query092
176INFO : (10-04 13:41:33) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query093
177INFO : (10-04 13:41:33) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query095
178INFO : (10-04 13:41:33) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query097
179INFO : (10-04 13:41:33) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query098
180INFO : (10-04 13:41:33) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL
181INFO : (10-04 13:41:33) <-- clear: 38 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
182INFO : (10-04 13:41:33) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
183INFO : (10-04 13:41:33) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
184INFO : (10-04 13:41:33) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
185INFO : (10-04 13:41:33) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
186INFO : (10-04 13:41:33) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
187INFO : (10-04 13:41:33) <-- clear: 44 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
188INFO : (10-04 13:41:33) <-- clear: 45 - Scheduler::AutoPopulate::Queries::Query09_12_Points
189INFO : (10-04 13:41:33) <-- clear: 46 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
190INFO : (10-04 13:41:33) <-- clear: 47 - Scheduler::AutoPopulate::Queries::Query10
191INFO : (10-04 13:41:33) <-- clear: 48 - Scheduler::AutoPopulate::Queries::QueryScore02
192INFO : (10-04 13:41:33) <-- clear: 49 - Scheduler::AutoPopulate::Queries::QueryScore03
193INFO : (10-04 13:41:33) <-- clear: 50 - Scheduler::AutoPopulate::Queries::QueryScore04
194INFO : (10-04 13:41:33) <-- clear: 51 - Scheduler::AutoPopulate::Queries::Query11
195INFO : (10-04 13:41:33) <-- clear: 52 - Scheduler::AutoPopulate::Queries::DropTables
196 (139.6ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_329765480
197 (137.8ms) DROP TABLE IF EXISTS buffer_annual_point_targets_329765480
198 (139.7ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_329765480
199 (137.4ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_329765480
200 (137.0ms) DROP TABLE IF EXISTS buffer_call_days_targets_329765480
201 (140.5ms) DROP TABLE IF EXISTS buffer_call_type_targets_329765480
202 (146.1ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_329765480
203 (141.5ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_329765480
204 (139.5ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_329765480
205 (147.9ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_329765480
206 (497.5ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_329765480
207 (141.8ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_329765480
208 (160.6ms) DROP TABLE IF EXISTS buffer_current_cd_targets_329765480
209 (691.8ms) DROP TABLE IF EXISTS buffer_current_ct_targets_329765480
210 (150.1ms) DROP TABLE IF EXISTS buffer_current_dow_targets_329765480
211 (138.4ms) DROP TABLE IF EXISTS buffer_current_hour_targets_329765480
212 (147.5ms) DROP TABLE IF EXISTS buffer_current_oa_targets_329765480
213 (150.1ms) DROP TABLE IF EXISTS buffer_current_point_targets_329765480
214 (144.1ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_329765480
215 (139.3ms) DROP TABLE IF EXISTS buffer_hour_targets_329765480
216 (194.8ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_329765480
217 (138.0ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_329765480
218 (159.8ms) DROP TABLE IF EXISTS buffer_linkage_targets_329765480
219 (152.7ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_329765480
220 (154.2ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_329765480
221 (152.5ms) DROP TABLE IF EXISTS buffer_location_privileges_329765480
222 (141.3ms) DROP TABLE IF EXISTS buffer_periods_329765480
223 (139.6ms) DROP TABLE IF EXISTS buffer_point_targets_329765480
224 (153.4ms) DROP TABLE IF EXISTS buffer_schedules_329765480
225 (148.8ms) DROP TABLE IF EXISTS buffer_target_call_types_329765480
226 (159.0ms) DROP TABLE IF EXISTS buffer_target_daily_points_329765480
227 (137.5ms) DROP TABLE IF EXISTS buffer_target_dates_list_329765480
228 (153.3ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_329765480
229 (139.0ms) DROP TABLE IF EXISTS buffer_targets_329765480
230 (164.8ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_329765480
231 (138.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_329765480
232 (151.9ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_329765480
233 (142.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_329765480
234 (179.8ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_329765480
235 (140.8ms) DROP TABLE IF EXISTS buffer_prepopulate_steps_329765480
236 (140.9ms) DROP TABLE IF EXISTS buffer_prepopulate_dates_329765480
237 (145.3ms) DROP TABLE IF EXISTS buffer_prepopulate_periods_329765480
238 (144.5ms) DROP TABLE IF EXISTS buffer_prepopulate_assignments_329765480
239 (142.2ms) DROP TABLE IF EXISTS buffer_prepopulate_staff_schedules_329765480
240 (174.0ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_329765480
241 (140.0ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_329765480
242 (155.5ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_329765480
243 (495.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_329765480
244 (152.9ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_329765480
245 (141.8ms) DROP TABLE IF EXISTS buffer_call_type_assignments_329765480
246 (141.8ms) DROP TABLE IF EXISTS buffer_combined_availabilities_329765480
247 (140.9ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_329765480
248 (145.0ms) DROP TABLE IF EXISTS buffer_counter_increments_329765480
249 (139.3ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_329765480
250 (140.6ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_329765480
251 (142.0ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_329765480
252 (142.5ms) DROP TABLE IF EXISTS buffer_linkage_patterns_329765480
253 (146.5ms) DROP TABLE IF EXISTS buffer_linkages_329765480
254 (166.6ms) DROP TABLE IF EXISTS buffer_location_privileges_329765480
255 (150.4ms) DROP TABLE IF EXISTS buffer_min_distances_329765480
256 (148.2ms) DROP TABLE IF EXISTS buffer_negative_relations_329765480
257 (138.4ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_329765480
258 (155.4ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_329765480
259 (138.4ms) DROP TABLE IF EXISTS buffer_rule_scores_329765480
260 (163.6ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_329765480
261 (138.5ms) DROP TABLE IF EXISTS buffer_schedule_linkages_329765480
262 (148.6ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_329765480
263 (139.7ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_329765480
264 (162.2ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_329765480
265 (149.2ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_329765480
266 (140.5ms) DROP TABLE IF EXISTS buffer_staff_specializations_329765480
267 (138.1ms) DROP TABLE IF EXISTS buffer_static_availabilities_329765480
268 (175.9ms) DROP TABLE IF EXISTS buffer_target_assignments_329765480
269 (145.1ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_329765480
270 (149.1ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_329765480
271 (141.2ms) DROP TABLE IF EXISTS buffer_assignment_limits_329765480
272 (159.0ms) delete from buffer_target_dates
273 where session_id = 329765480
274
275INFO : (10-04 13:41:46) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
276 (141.0ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_329765480 LIKE buffer_assignment_limit_call_types
277 (163.3ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_329765480 LIKE buffer_assignment_limit_periods
278 (140.3ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_329765480 LIKE buffer_assignment_limit_staff_types
279 (152.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_329765480 LIKE buffer_assignment_limit_staffs
280 (144.1ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_329765480 LIKE buffer_auto_populate_slots
281 (167.9ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_329765480 LIKE buffer_call_type_assignments
282 (140.7ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_329765480 LIKE buffer_combined_availabilities
283 (152.5ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_329765480 LIKE buffer_combined_availability_linkages
284 (141.2ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_329765480 LIKE buffer_counter_increments
285 (153.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_329765480 LIKE buffer_linkage_assigned_days
286 (144.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_329765480 LIKE buffer_linkage_call_type_assignments
287 (162.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_329765480 LIKE buffer_linkage_dow_targets
288 (142.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_329765480 LIKE buffer_linkage_patterns
289 (170.5ms) CREATE TABLE IF NOT EXISTS buffer_linkages_329765480 LIKE buffer_linkages
290 (154.4ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_329765480 LIKE buffer_location_privileges
291 (141.4ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_329765480 LIKE buffer_min_distances
292 (145.0ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_329765480 LIKE buffer_negative_relations
293 (141.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_329765480 LIKE buffer_rule_calc_filters
294 (143.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_329765480 LIKE buffer_rule_calc_schedules
295 (156.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_329765480 LIKE buffer_rule_scores
296 (141.7ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_329765480 LIKE buffer_rule_scores_with_corrections
297 (154.9ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_329765480 LIKE buffer_schedule_linkages
298 (140.6ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_329765480 LIKE buffer_schedules_linkage_patterns
299 (174.6ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_329765480 LIKE buffer_specialization_daily_counts
300 (146.3ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_329765480 LIKE buffer_staff_assigned_days
301 (140.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_329765480 LIKE buffer_staff_daily_counts
302 (143.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_329765480 LIKE buffer_staff_specializations
303 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_329765480 LIKE buffer_static_availabilities
304 (155.3ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_329765480 LIKE buffer_target_assignments
305 (168.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_329765480 LIKE buffer_linkage_assignment_limits
306 (142.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_329765480 LIKE buffer_staff_assignment_limits
307 (140.4ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_329765480 LIKE buffer_assignment_limits
308 (143.7ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_steps_329765480 LIKE buffer_prepopulate_steps
309 (163.0ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_dates_329765480 LIKE buffer_prepopulate_dates
310 (141.1ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_periods_329765480 LIKE buffer_prepopulate_periods
311 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_assignments_329765480 LIKE buffer_prepopulate_assignments
312 (160.3ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_staff_schedules_329765480 LIKE buffer_prepopulate_staff_schedules
313 (155.3ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_329765480 LIKE buffer_annual_hour_targets
314 (512.7ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_329765480 LIKE buffer_annual_point_targets
315 (290.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_329765480 LIKE buffer_annual_targets_staff_totals
316 (139.3ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_329765480 LIKE buffer_auto_populate_slots
317 (142.1ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_329765480 LIKE buffer_call_days_targets
318 (143.5ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_329765480 LIKE buffer_call_type_targets
319 (144.6ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_329765480 LIKE buffer_cumulative_cd_targets
320 (146.7ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_329765480 LIKE buffer_cumulative_ct_targets
321 (143.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_329765480 LIKE buffer_cumulative_dow_targets
322 (154.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_329765480 LIKE buffer_cumulative_hour_targets
323 (493.6ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_329765480 LIKE buffer_cumulative_oa_targets
324 (153.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_329765480 LIKE buffer_cumulative_point_targets
325 (155.0ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_329765480 LIKE buffer_current_cd_targets
326 (142.5ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_329765480 LIKE buffer_current_ct_targets
327 (147.0ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_329765480 LIKE buffer_current_dow_targets
328 (146.9ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_329765480 LIKE buffer_current_hour_targets
329 (153.8ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_329765480 LIKE buffer_current_oa_targets
330 (146.0ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_329765480 LIKE buffer_current_point_targets
331 (142.7ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_329765480 LIKE buffer_day_of_week_targets
332 (145.7ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_329765480 LIKE buffer_hour_targets
333 (139.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_329765480 LIKE buffer_linkage_call_days_targets
334 (141.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_329765480 LIKE buffer_linkage_call_type_targets
335 (141.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_329765480 LIKE buffer_linkage_targets
336 (149.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_329765480 LIKE buffer_linkage_hour_targets
337 (153.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_329765480 LIKE buffer_linkage_point_targets
338 (150.6ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_329765480 LIKE buffer_location_privileges
339 (142.6ms) CREATE TABLE IF NOT EXISTS buffer_periods_329765480 LIKE buffer_periods
340 (157.9ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_329765480 LIKE buffer_point_targets
341 (438.4ms) CREATE TABLE IF NOT EXISTS buffer_schedules_329765480 LIKE buffer_schedules
342 (145.1ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_329765480 LIKE buffer_target_call_types
343 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_329765480 LIKE buffer_target_daily_points
344 (143.6ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_329765480 LIKE buffer_target_dates_list
345 (144.1ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_329765480 LIKE buffer_target_primary_availabilities
346 (141.3ms) CREATE TABLE IF NOT EXISTS buffer_targets_329765480 LIKE buffer_targets
347 (147.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_329765480 LIKE buffer_tmp_targets_availabilities
348 (150.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_329765480 LIKE buffer_tmp_targets_availabilities_exclusions
349 (146.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_329765480 LIKE buffer_tmp_targets_client_totals
350 (146.8ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_329765480 LIKE buffer_tmp_targets_scheduled
351 (143.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_329765480 LIKE buffer_tmp_targets_staff_totals
352INFO : (10-04 13:41:58) --> fill: 1 - Scheduler::AutoPopulate::Queries::Query001_PR_01
353 (160.8ms) insert into buffer_prepopulate_steps_329765480 (
354 rule_id,
355 rule_type,
356 rule_subtype,
357 staff_id,
358 x,
359 days_of_week_mask,
360 start_date,
361 start_date_first_dow,
362 required_assignments
363 )
364 select
365 pr.id as rule_id,
366 pr.rule_type,
367 coalesce(rrt.recurring_type, mrt.timeframe) as rule_subtype,
368 pr.staff_id,
369 rrt.frequency as x,
370 case
371 when (rrt.days_of_week_mask = 0) then 127
372 else coalesce(rrt.days_of_week_mask, mrt.days_of_week_mask)
373 end as days_of_week_mask,
374 rrt.start_date,
375 case
376 when (cl.start_of_week = 0) then
377 date_add(rrt.start_date, interval -(dayofweek(rrt.start_date) - 1) day)
378 else
379 date_add(rrt.start_date, interval -weekday(rrt.start_date) day)
380 end as start_date_first_dow,
381 case
382 when (rrt.id is not null) then 1
383 else mrt.number_to_assign
384 end as required_assignments
385 from
386 clients as cl
387 inner join prepopulate_rules as pr
388 on pr.autopopulate_step_id = 1729
389 left join recurring_rule_types as rrt
390 on pr.id = rrt.prepopulate_rule_id
391 left join minimum_rule_types as mrt
392 on pr.id = mrt.prepopulate_rule_id
393 where cl.id = 319
394 order by
395 pr.display_order,
396 coalesce(rrt.display_order, mrt.display_order)
397
398INFO : (10-04 13:41:58) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query001_PR_02
399 (143.8ms) insert into buffer_prepopulate_dates_329765480 (
400 day,
401 schedule_on,
402 day_of_week_mask,
403 week,
404 first_dow,
405 last_dow,
406 month,
407 first_dom,
408 last_dom,
409 block,
410 first_dob,
411 last_dob
412 )
413 select
414 dl.day,
415 dl.schedule_on,
416 (1 << (dayofweek(dl.schedule_on) - 1)) as day_of_week_mask,
417 yearweek(dl.schedule_on, dl.week_mode) as week,
418 case
419 when (dl.start_of_week = 0) then
420 date_add(dl.schedule_on, interval -(dayofweek(dl.schedule_on) - 1) day)
421 else
422 date_add(dl.schedule_on, interval -weekday(dl.schedule_on) day)
423 end as first_dow,
424 case
425 when (dl.start_of_week = 0) then
426 date_add(dl.schedule_on, interval (7 - dayofweek(dl.schedule_on)) day)
427 else
428 date_add(dl.schedule_on, interval (6 - weekday(dl.schedule_on)) day)
429 end as last_dow,
430 (year(dl.schedule_on) * 100 + month(dl.schedule_on)) as month,
431 date_add(dl.schedule_on, interval (1 - dayofmonth(dl.schedule_on)) day) as first_dom,
432 last_day(dl.schedule_on) as last_dom,
433 dl.block_id as block,
434 dl.start_date as first_dob,
435 dl.end_date as last_dob
436 from (
437 select
438 (ns.item_number + 1) as day,
439 date_add(b.start_date, interval ns.item_number day) as schedule_on,
440 b.id as block_id,
441 b.start_date,
442 b.end_date,
443 cl.start_of_week,
444 case
445 when (cl.start_of_week = 0) then 2
446 else 7
447 end as week_mode
448 from
449 clients as cl
450 inner join blocks as b
451 on b.id in (29261)
452 inner join numeric_sequence as ns
453 on ns.item_number <= datediff(b.end_date, b.start_date)
454 where cl.id = 319
455 order by null
456 ) as dl
457 order by null
458
459INFO : (10-04 13:41:58) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query001_PR_03
460 (153.4ms) insert into buffer_prepopulate_periods_329765480 (
461 step_id,
462 start_date,
463 end_date
464 )
465 select
466 ps.id as step_id,
467 min(
468 case
469 when (ps.rule_type = 'recurring') then d.schedule_on
470 when (ps.rule_subtype = 'per_week') then d.first_dow
471 when (ps.rule_subtype = 'per_month') then d.first_dom
472 when (ps.rule_subtype = 'per_block') then d.first_dob
473 end
474 ) as _start_date,
475 max(
476 case
477 when (ps.rule_type = 'recurring') then d.schedule_on
478 when (ps.rule_subtype = 'per_week') then d.last_dow
479 when (ps.rule_subtype = 'per_month') then d.last_dom
480 when (ps.rule_subtype = 'per_block') then d.last_dob
481 end
482 ) as _end_date
483 from
484 buffer_prepopulate_steps_329765480 as ps
485 inner join buffer_prepopulate_dates_329765480 as d
486 on (
487 ps.rule_type = 'minimum'
488 or ps.start_date <= d.schedule_on
489 )
490 and (ps.days_of_week_mask & d.day_of_week_mask) > 0
491 where
492 ps.rule_type = 'minimum'
493 or (
494 ps.rule_subtype = 'every_x_days'
495 and (datediff(d.schedule_on, ps.start_date) mod ps.x) = 0
496 )
497 or (
498 ps.rule_subtype = 'every_x_weeks'
499 and (datediff(d.first_dow, ps.start_date_first_dow) mod ps.x) = 0
500 )
501 or (
502 ps.rule_subtype = 'x_day_of_week_month'
503 and ((datediff(d.schedule_on, d.first_dom) div 7 + 1) / ps.x) = 1
504 )
505 or (
506 ps.rule_subtype = 'x_day_of_week_block'
507 and ((datediff(d.schedule_on, d.first_dob) div 7 + 1) / ps.x) = 1
508 )
509 group by
510 ps.id,
511 case
512 when (ps.rule_type = 'recurring') then d.day
513 when (ps.rule_subtype = 'per_week') then d.week
514 when (ps.rule_subtype = 'per_month') then d.month
515 when (ps.rule_subtype = 'per_block') then d.block
516 end
517 order by
518 ps.id,
519 _start_date
520
521INFO : (10-04 13:41:59) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query001_PR_04
522 (158.0ms) insert into buffer_prepopulate_assignments_329765480 (
523 period_id,
524 required_assignments,
525 scheduled_assignments
526 )
527 select
528 pp.id as period_id,
529 ps.required_assignments,
530 count(sc.id) as scheduled_assignments
531 from (
532 select
533 sc.id,
534 sc.schedule_on,
535 (1 << (sc.dayofweek_on - 1)) as day_of_week_mask,
536 sc.call_type_id,
537 sc.staff_id
538 from
539 schedules as sc
540 inner join (
541 select
542 min(start_date) as start_date,
543 max(end_date) as end_date
544 from buffer_prepopulate_periods_329765480
545 ) as pp
546 on sc.schedule_on between pp.start_date and pp.end_date
547 where sc.client_id = 319
548 order by null
549 ) as sc
550 inner join prepopulate_rule_call_types as prct
551 on sc.call_type_id = prct.call_type_id
552 inner join buffer_prepopulate_steps_329765480 as ps
553 on prct.prepopulate_rule_id = ps.rule_id
554 and sc.staff_id = ps.staff_id
555 and (sc.day_of_week_mask & ps.days_of_week_mask) > 0
556 inner join buffer_prepopulate_periods_329765480 as pp
557 on ps.id = pp.step_id
558 and sc.schedule_on between pp.start_date and pp.end_date
559 group by
560 pp.id,
561 ps.required_assignments
562 order by null
563
564 (158.5ms) insert into buffer_prepopulate_assignments_329765480 (
565 period_id,
566 required_assignments,
567 scheduled_assignments
568 )
569 select
570 pp.id as period_id,
571 ps.required_assignments,
572 0 as scheduled_assignments
573 from
574 buffer_prepopulate_periods_329765480 as pp
575 left join buffer_prepopulate_assignments_329765480 as pa
576 on pp.id = pa.period_id
577 inner join buffer_prepopulate_steps_329765480 as ps
578 on pp.step_id = ps.id
579 where pa.id is null
580 order by null
581
582INFO : (10-04 13:41:59) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query001_PR_05
583 (143.8ms) insert into buffer_auto_populate_slots_329765480 (
584 schedule_on,
585 day_of_week_mask,
586 call_type_id,
587 display_order,
588 should_be_populated
589 )
590 select distinct
591 pd.schedule_on,
592 pd.day_of_week_mask,
593 ct.id as call_type_id,
594 ct.display_order,
595 1 as should_be_populated
596 from
597 buffer_prepopulate_steps_329765480 as ps
598 inner join buffer_prepopulate_periods_329765480 as pp
599 on ps.id = pp.step_id
600 inner join buffer_prepopulate_dates_329765480 as pd
601 on pd.schedule_on between pp.start_date and pp.end_date
602 and (ps.days_of_week_mask & pd.day_of_week_mask) > 0
603 inner join prepopulate_rule_call_types as prct
604 on ps.rule_id = prct.prepopulate_rule_id
605 inner join call_types as ct
606 on prct.call_type_id = ct.id
607 and ct.active = 1
608 and (pd.day_of_week_mask & ct.days_of_week_mask) > 0
609 order by null
610
611INFO : (10-04 13:41:59) --> fill: 6 - Scheduler::AutoPopulate::Queries::Query005
612 (140.4ms) insert into buffer_auto_populate_slots_329765480 (
613 schedule_on,
614 day_of_week_mask,
615 call_type_id,
616 display_order,
617 should_be_populated
618 )
619 select distinct
620 date_add(
621 auto_populate_slots.schedule_on,
622 interval linkage_relations.distance_in_days day
623 ) as schedule_on,
624 (1 << (
625 dayofweek(
626 date_add(
627 auto_populate_slots.schedule_on,
628 interval linkage_relations.distance_in_days day
629 )
630 ) - 1
631 )
632 ) as day_of_week_mask,
633 linkage_relations.linked_call_type_id as call_type_id,
634 call_types.display_order,
635 0 as should_be_populated
636 from
637 buffer_auto_populate_slots_329765480 as auto_populate_slots
638 inner join linkage_relations
639 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
640 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
641 left join buffer_auto_populate_slots_329765480 as auto_populate_slots_2
642 on date_add(
643 auto_populate_slots.schedule_on,
644 interval linkage_relations.distance_in_days day
645 ) = auto_populate_slots_2.schedule_on
646 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
647 inner join call_types
648 on linkage_relations.linked_call_type_id = call_types.id
649 where auto_populate_slots_2.id is null
650 order by null
651
652INFO : (10-04 13:41:59) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query01
653 (139.5ms) insert into linkage_relations (
654 client_id,
655 call_type_id,
656 day_of_week_mask,
657 linked_call_type_id,
658 distance_in_days,
659 linkage_template_id
660 )
661 select distinct
662 319 /* client_id */ as client_id,
663 aps.call_type_id,
664 aps.day_of_week_mask,
665 aps.call_type_id as linked_call_type_id,
666 0 as distance_in_days,
667 coalesce(
668 lr.linkage_template_id,
669 @linkage_template_max := @linkage_template_max + 1
670 ) as linkage_template_id
671 from
672 buffer_auto_populate_slots_329765480/* client_id */ as aps
673 inner join (
674 select @linkage_template_max := count(distinct linkage_template_id) - 1
675 from linkage_relations
676 where client_id = 319 /* client_id */
677 order by null
678 ) as lt
679 on 1 = 1
680 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
681 on aps.call_type_id = lr.call_type_id
682 and aps.day_of_week_mask = lr.day_of_week_mask
683 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
684 on aps.call_type_id = lr_2.call_type_id
685 and aps.day_of_week_mask = lr_2.day_of_week_mask
686 and aps.call_type_id = lr_2.linked_call_type_id
687 and lr_2.distance_in_days = 0
688 where lr_2.id is null
689 order by null
690
691 (278.4ms) insert into buffer_schedules_329765480 (
692 call_type_id,
693 staff_id,
694 schedule_on,
695 day_of_week_mask,
696 is_weekend,
697 display_order,
698 week_number,
699 should_be_evaluated,
700 should_be_populated,
701 linkage_instance_id
702 )
703 select
704 call_type_id,
705 staff_id,
706 schedule_on,
707 day_of_week_mask,
708 is_weekend,
709 display_order,
710 week_number,
711 max(should_be_evaluated) as should_be_evaluated,
712 max(should_be_populated) as should_be_populated,
713 min(linkage_instance_id) as linkage_instance_id
714 from (
715 select
716 auto_populate_slots_2.call_type_id,
717 schedules.staff_id,
718 auto_populate_slots_2.schedule_on,
719 auto_populate_slots_2.day_of_week_mask,
720 case
721 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
722 else 1
723 end as is_weekend,
724 auto_populate_slots_2.display_order,
725 yearweek(
726 auto_populate_slots_2.schedule_on,
727 case
728 when (clients.start_of_week = 0) then 2
729 else 7
730 end
731 ) as week_number,
732 case
733 when (schedules.staff_id is null) then 1
734 else 0
735 end as should_be_evaluated,
736 (
737 case
738 when (
739 auto_populate_slots_2.should_be_populated = 1
740 and schedules.staff_id is null
741 ) then 1
742 else 0
743 end
744 ) as should_be_populated,
745 (
746 linkage_relations.linkage_template_id * 1000000 +
747 yearweek(
748 auto_populate_slots.schedule_on,
749 case
750 when (clients.start_of_week = 0) then 2
751 else 7
752 end
753 )
754 ) as linkage_instance_id
755 from
756 buffer_auto_populate_slots_329765480 as auto_populate_slots
757 inner join clients
758 on clients.id = 319 /* client_id */
759 inner join linkage_relations
760 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
761 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
762 inner join buffer_auto_populate_slots_329765480 as auto_populate_slots_2
763 on date_add(
764 auto_populate_slots.schedule_on,
765 interval linkage_relations.distance_in_days day
766 ) = auto_populate_slots_2.schedule_on
767 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
768 left join schedules
769 on auto_populate_slots_2.call_type_id = schedules.call_type_id
770 and auto_populate_slots_2.schedule_on = schedules.schedule_on
771 -- we should exclude do-not-populate slots
772 left join autopopulate_exclusions
773 on auto_populate_slots_2.call_type_id = autopopulate_exclusions.call_type_id
774 and auto_populate_slots_2.schedule_on = autopopulate_exclusions.schedule_on
775 where
776 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
777 -- once we added someone to that slot then it should be considered as a regular one
778 schedules.id is not null
779 or autopopulate_exclusions.id is null
780 order by null
781 ) as t1
782 group by
783 call_type_id,
784 staff_id,
785 schedule_on,
786 day_of_week_mask,
787 display_order,
788 week_number
789 order by null
790
791INFO : (10-04 13:42:00) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query012
792 (163.4ms) update
793 buffer_schedules_329765480 as sc
794 inner join call_types as ct
795 on sc.call_type_id = ct.id
796 inner join calendar_schedules as cs
797 on ct.calendar_schedule_id = cs.id
798 inner join blocks as b
799 on cs.block_schedule_id = b.block_schedule_id
800 and sc.schedule_on between b.start_date and b.end_date
801 set
802 sc.calendar_schedule_id = cs.id,
803 sc.block_id = b.id
804
805 BlockSchedule Load (142.0ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
806 (1193.3ms) insert into buffer_periods_329765480 (
807 calendar_schedule_id,
808 block_schedule_id,
809 period_offset,
810 start_block_display_order,
811 end_block_display_order,
812 start_date,
813 end_date,
814 should_be_populated,
815 should_be_evaluated
816 )
817 select
818 t1.calendar_schedule_id,
819 t1.block_schedule_id,
820 t1.period_offset,
821 t2.start_block_display_order,
822 t2.end_block_display_order,
823 t2.start_date,
824 t2.end_date,
825 t1.should_be_populated,
826 t1.should_be_evaluated
827 from (
828 select
829 sc.calendar_schedule_id,
830 b.block_schedule_id,
831 case
832 when ((month(b.start_date) - pt.begin_period) >= 0)
833 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
834 else
835 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
836 end as period_offset,
837 max(sc.should_be_populated) as should_be_populated,
838 max(sc.should_be_evaluated) as should_be_evaluated
839 from (
840 select
841 sc.block_id,
842 sc.calendar_schedule_id,
843 max(ps.should_be_populated) as should_be_populated,
844 1 as should_be_evaluated
845 from
846 buffer_schedules_329765480 as sc
847 inner join buffer_auto_populate_slots_329765480 as ps
848 on sc.schedule_on = ps.schedule_on
849 and sc.call_type_id = ps.call_type_id
850 group by
851 sc.block_id,
852 sc.calendar_schedule_id
853 order by null
854 ) as sc
855 inner join blocks as b
856 on sc.block_id = b.id
857 inner join population_timeframes as pt
858 on pt.client_id = 319
859 and pt.timeframe_type = 1
860 group by
861 sc.calendar_schedule_id,
862 b.block_schedule_id,
863 period_offset
864 order by null
865 ) as t1
866 inner join (
867 select
868 b.block_schedule_id,
869 case
870 when ((month(b.start_date) - pt.begin_period) >= 0)
871 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
872 else
873 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
874 end as period_offset,
875 min(b.display_order) as start_block_display_order,
876 max(b.display_order) as end_block_display_order,
877 min(b.start_date) as start_date,
878 max(b.end_date) as end_date
879 from
880 blocks as b
881 inner join population_timeframes as pt
882 on pt.client_id = 319
883 and pt.timeframe_type = 1
884 where
885 b.client_id = 319
886 group by
887 b.block_schedule_id,
888 period_offset
889 order by null
890 ) as t2
891 on t1.block_schedule_id = t2.block_schedule_id
892 and t1.period_offset = t2.period_offset
893 order by null
894
895 (163.4ms) update
896 buffer_schedules_329765480 as sc
897 inner join buffer_periods_329765480 as p
898 on sc.calendar_schedule_id = p.calendar_schedule_id
899 and sc.schedule_on between p.start_date and p.end_date
900 set sc.period_offset = p.period_offset
901
902INFO : (10-04 13:42:01) --> fill: 9 - Scheduler::AutoPopulate::Queries::QueryTargets
903INFO : (10-04 13:42:01) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query013
904 (578.4ms) insert into buffer_counter_increments_329765480 (
905 schedule_id,
906 calls,
907 points,
908 hours,
909 affects_cumulative_targets
910 )
911 select
912 sc.id as schedule_id,
913 case
914 when (ct.included_in_targets > 0) then 1
915 else 0
916 end as calls,
917 coalesce(
918 case
919 when (hl.observed_date is null or dp.point is not null) then dp.point
920 else dp_2.point
921 end,
922 0.0
923 ) as points,
924 ct.hours,
925 case
926 when (b.display_order between (t1.timeframe_ending - (2 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
927 else 0
928 end as affects_cumulative_targets
929 from
930 buffer_schedules_329765480 as sc
931 inner join (
932 select
933 id,
934 included_in_targets,
935 coalesce(
936 timestampdiff(
937 minute,
938 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
939 concat(
940 '1900-01-0',
941 case
942 when ((
943 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
944 )
945 and (
946 timediff(
947 maketime(end_time_hour, end_time_minute, 0),
948 maketime(start_time_hour, start_time_minute, 0)
949 ) <= 0
950 )
951 ) then '2'
952 else '1'
953 end,
954 ' ',
955 maketime(end_time_hour, end_time_minute, 0)
956 )
957 ) / 60,
958 0.0
959 ) as hours
960 from call_types
961 where
962 client_id = 319
963 and active = 1
964 order by null
965 ) as ct
966 on sc.call_type_id = ct.id
967 inner join blocks as b
968 on sc.block_id = b.id
969 inner join (
970 select
971 calendar_schedule_id,
972 max(
973 case
974 when (should_be_populated = 1) then end_block_display_order
975 else null
976 end
977 ) as timeframe_ending
978 from buffer_periods_329765480
979 group by calendar_schedule_id
980 order by null
981 ) as t1
982 on sc.calendar_schedule_id = t1.calendar_schedule_id
983 left join (
984 select
985 hd.observed_date,
986 case
987 when (hd.observed_date > hd.extended_date) then hd.extended_date
988 else hd.observed_date
989 end as date_from,
990 case
991 when (hd.observed_date < hd.extended_date) then hd.extended_date
992 else hd.observed_date
993 end as date_to
994 from
995 holidays as h
996 inner join holiday_dates as hd
997 on h.id = hd.holiday_id
998 inner join (
999 select
1000 year(min(schedule_on)) as year_from,
1001 year(max(schedule_on)) as year_to
1002 from buffer_schedules_329765480
1003 where should_be_evaluated = 1
1004 ) as y
1005 on hd.year between y.year_from and y.year_to
1006 where
1007 h.client_id = 319
1008 and (
1009 h.observed = 1
1010 or h.extended = 1
1011 )
1012 order by null
1013 ) as hl
1014 on sc.schedule_on between hl.date_from and hl.date_to
1015 left join assignment_annotations as aa
1016 on ct.id = aa.assignmentable_id
1017 and aa.assignmentable_type = 'CallType'
1018 and aa.annotation_id = 0 /* annotation = "None" */
1019 and aa.applicable = 1
1020 left join assignment_annotation_daily_points as dp
1021 on aa.id = dp.assignment_annotation_id
1022 and case
1023 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
1024 when (sc.schedule_on = hl.observed_date) then 7
1025 else 8
1026 end = dp.day
1027 left join assignment_annotation_daily_points as dp_2
1028 on aa.id = dp_2.assignment_annotation_id
1029 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
1030 where sc.should_be_evaluated = 1
1031 order by null
1032
1033INFO : (10-04 13:42:02) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query014
1034 (143.0ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
1035 insert into buffer_linkage_patterns_329765480 (
1036 period_offset,
1037 calendar_schedule_id,
1038 block_id,
1039 block_start_date,
1040 block_start_dow,
1041 block_length,
1042 linkage_pattern_id,
1043 pattern_start_dow,
1044 days_before_start,
1045 pattern_length,
1046 start_offset,
1047 iterations_count
1048 )
1049 select
1050 b1.period_offset,
1051 b1.calendar_schedule_id,
1052 b1.block_id,
1053 b2.start_date as block_start_date,
1054 dayofweek(b2.start_date) as block_start_dow,
1055 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
1056 lp.id as linkage_pattern_id,
1057 (lp.start_on + 1) as pattern_start_dow,
1058 lp.days_before_start,
1059 (lp.length * 7) as pattern_length,
1060 null as start_offset,
1061 null as iterations_count
1062 from (
1063 select
1064 calendar_schedule_id,
1065 block_id,
1066 period_offset
1067 from buffer_schedules_329765480
1068 group by
1069 calendar_schedule_id,
1070 block_id,
1071 period_offset
1072 order by null
1073 ) as b1
1074 inner join blocks as b2
1075 on b1.block_id = b2.id
1076 inner join linkage_patterns as lp
1077 on lp.client_id = 319
1078 and b1.calendar_schedule_id = lp.calendar_schedule_id
1079 order by null
1080
1081 (143.9ms) -- Calculate the basic offset ("+" or "-") that will be applied to a linkage pattern if a block and a pattern are starting on different day of week
1082 update buffer_linkage_patterns_329765480
1083 set start_offset = if(
1084 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
1085 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
1086 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
1087 )
1088
1089 (143.2ms) -- In some cases (when a block and a pattern are starting on different day of week or when a pattern is shorter than a block)
1090 -- a pattern should be repeated couple of times (in order to cover the entire block)
1091 update buffer_linkage_patterns_329765480
1092 set iterations_count =
1093 ceiling(
1094 (
1095 block_length +
1096 case
1097 when (start_offset < 0) then abs(start_offset)
1098 else 0
1099 end
1100 ) * 1.0 / pattern_length
1101 )
1102
1103 (175.1ms) -- Generate an assignments sequence for every linkage pattern
1104 insert into buffer_schedules_linkage_patterns_329765480 (
1105 call_type_id,
1106 staff_id,
1107 schedule_on,
1108 period_offset,
1109 day_of_week_mask,
1110 display_order,
1111 week_number,
1112 should_be_evaluated,
1113 should_be_populated,
1114 linkage_instance_id,
1115 block_id,
1116 calendar_schedule_id
1117 )
1118 select
1119 lps.assignment_id as call_type_id,
1120 null as staff_id,
1121 case
1122 when (lp.start_offset < 0)
1123 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
1124 else
1125 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
1126 end as schedule_on,
1127 lp.period_offset,
1128 null as day_of_week_mask,
1129 null as display_order,
1130 null as week_number,
1131 0 as should_be_evaluated,
1132 0 as should_be_populated,
1133 lp.linkage_pattern_id as linkage_instance_id,
1134 lp.block_id,
1135 lp.calendar_schedule_id
1136 from
1137 numeric_sequence as ns
1138 inner join buffer_linkage_patterns_329765480 as lp
1139 on ns.item_number <= (lp.iterations_count - 1)
1140 inner join linkage_pattern_slots as lps
1141 on lp.linkage_pattern_id = lps.linkage_pattern_id
1142 and lps.assignment_type = 'CallType'
1143 where
1144 case
1145 when (lp.start_offset < 0)
1146 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
1147 else
1148 (ns.item_number * lp.pattern_length + lps.offset)
1149 end between 0 and (lp.block_length - 1)
1150 order by null
1151
1152 (142.5ms) -- Fill the columns that we left blank in the previous "INSERT"
1153 update
1154 buffer_schedules_linkage_patterns_329765480 as slp
1155 inner join clients as cl
1156 on cl.id = 319
1157 inner join call_types as ct
1158 on slp.call_type_id = ct.id
1159 left join buffer_schedules_329765480 as bs
1160 on slp.schedule_on = bs.schedule_on
1161 and slp.call_type_id = bs.call_type_id
1162 set
1163 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
1164 slp.display_order = ct.display_order,
1165 slp.week_number =
1166 yearweek(
1167 slp.schedule_on,
1168 case
1169 when (cl.start_of_week = 0) then 2
1170 else 7
1171 end
1172 ),
1173 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
1174 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
1175
1176 (163.6ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
1177 delete slp_1
1178 from
1179 buffer_schedules_linkage_patterns_329765480 as slp_1
1180 inner join (
1181 select linkage_instance_id
1182 from buffer_schedules_linkage_patterns_329765480
1183 group by linkage_instance_id
1184 having
1185 max(should_be_evaluated) = 0
1186 and max(should_be_populated) = 0
1187 order by null
1188 ) as slp_2
1189 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
1190
1191 (137.2ms) -- Delete do-not-populate slots
1192 delete slp
1193 from
1194 buffer_schedules_linkage_patterns_329765480 as slp
1195 inner join autopopulate_exclusions as ae
1196 on slp.call_type_id = ae.call_type_id
1197 and slp.schedule_on = ae.schedule_on
1198 left join schedules as sc
1199 on slp.call_type_id = sc.call_type_id
1200 and slp.schedule_on = sc.schedule_on
1201 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
1202 -- once we added someone to that slot then it should be considered as a regular one
1203 where sc.id is null
1204
1205 (158.4ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
1206 update
1207 buffer_schedules_linkage_patterns_329765480 as slp
1208 inner join buffer_schedules_329765480 as bs_1
1209 on slp.schedule_on = bs_1.schedule_on
1210 and slp.call_type_id = bs_1.call_type_id
1211 inner join buffer_schedules_329765480 as bs_2
1212 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
1213 set bs_2.linkage_instance_id = slp.linkage_instance_id
1214
1215 (140.0ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
1216 -- should be copied over to the buffer schedules table (used during the auto-populate process)
1217 insert into buffer_schedules_329765480 (
1218 call_type_id,
1219 staff_id,
1220 schedule_on,
1221 period_offset,
1222 day_of_week_mask,
1223 is_weekend,
1224 display_order,
1225 week_number,
1226 should_be_evaluated,
1227 should_be_populated,
1228 linkage_instance_id,
1229 block_id,
1230 calendar_schedule_id
1231 )
1232 select
1233 call_type_id,
1234 staff_id,
1235 schedule_on,
1236 period_offset,
1237 day_of_week_mask,
1238 case
1239 when (day_of_week_mask & 62 > 0) then 0
1240 else 1
1241 end as is_weekend,
1242 display_order,
1243 week_number,
1244 should_be_evaluated,
1245 should_be_populated,
1246 linkage_instance_id,
1247 block_id,
1248 calendar_schedule_id
1249 from buffer_schedules_linkage_patterns_329765480
1250 where
1251 should_be_evaluated = 0
1252 and should_be_populated = 0
1253
1254INFO : (10-04 13:42:03) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query015_PR
1255 (140.1ms) insert into buffer_prepopulate_staff_schedules_329765480 (
1256 step_id,
1257 period_id,
1258 schedule_id,
1259 linkage_instance_id,
1260 staff_id
1261 )
1262 select
1263 ps.id as step_id,
1264 pp.id as period_id,
1265 bs.id as schedule_id,
1266 bs.linkage_instance_id,
1267 ps.staff_id
1268 from
1269 buffer_prepopulate_steps_329765480 as ps
1270 inner join buffer_prepopulate_periods_329765480 as pp
1271 on ps.id = pp.step_id
1272 inner join buffer_prepopulate_dates_329765480 as pd
1273 on pd.schedule_on between pp.start_date and pp.end_date
1274 and (ps.days_of_week_mask & pd.day_of_week_mask) > 0
1275 inner join prepopulate_rule_call_types as prct
1276 on ps.rule_id = prct.prepopulate_rule_id
1277 inner join buffer_schedules_329765480 as bs
1278 on pd.schedule_on = bs.schedule_on
1279 and prct.call_type_id = bs.call_type_id
1280 order by pp.id
1281
1282 (136.3ms) select 1
1283 from buffer_prepopulate_staff_schedules_329765480
1284 limit 1
1285
1286INFO : (10-04 13:42:03) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query016
1287**Airbrake: closed