· 7 years ago · Dec 21, 2018, 09:02 PM
1(146.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 (153.9ms) SELECT `autopopulate_step_statuses`.* FROM `autopopulate_step_statuses` WHERE `autopopulate_step_statuses`.`id` = 11727 LIMIT 1
3 Client Load (150.0ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 26 LIMIT 1
4 CalendarSchedule Load (147.7ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`id` = 26 LIMIT 1
5 Block Load (146.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`id` = 18678 LIMIT 1
6 AutopopulateStep Load (150.9ms) SELECT `autopopulate_steps`.* FROM `autopopulate_steps` WHERE `autopopulate_steps`.`calendar_schedule_id` = 26 AND `autopopulate_steps`.`step` = 4 ORDER BY autopopulate_steps.step ASC, autopopulate_steps.display_order ASC
7 PrepopulateRule Exists (147.3ms) SELECT 1 AS one FROM `prepopulate_rules` WHERE `prepopulate_rules`.`autopopulate_step_id` = 1735 LIMIT 1
8 BlockSchedule Load (146.9ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 26 LIMIT 1
9ClientTools::SchedulesPublisher.set_options({:timeframe=>1})
10 CallType Load (152.7ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 26 AND `call_types`.`calendar_schedule_id` = 26 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
11 TargetGroupCall Load (148.2ms) SELECT `target_group_calls`.* FROM `target_group_calls` WHERE `target_group_calls`.`call_type_id` IN (11456, 11457, 308, 303, 304, 5502, 305, 306)
12 (158.7ms) 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 = 26
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 '2018-02-01' AND '2018-02-28'
40 WHERE ct1.calendar_schedule_id = 26
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 (443.0ms) /* 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 303 AS call_type_id,
58 303 AS assignment_id,
59 'CallType' AS assignment_type,
60 62 AS own_n_sc_dow_mask
61 UNION SELECT
62 304 AS call_type_id,
63 304 AS assignment_id,
64 'CallType' AS assignment_type,
65 62 AS own_n_sc_dow_mask
66 UNION SELECT
67 305 AS call_type_id,
68 305 AS assignment_id,
69 'CallType' AS assignment_type,
70 62 AS own_n_sc_dow_mask
71 UNION SELECT
72 306 AS call_type_id,
73 306 AS assignment_id,
74 'CallType' AS assignment_type,
75 62 AS own_n_sc_dow_mask
76 UNION SELECT
77 308 AS call_type_id,
78 308 AS assignment_id,
79 'CallType' AS assignment_type,
80 65 AS own_n_sc_dow_mask
81 UNION SELECT
82 5502 AS call_type_id,
83 5502 AS assignment_id,
84 'CallType' AS assignment_type,
85 0 AS own_n_sc_dow_mask
86 UNION SELECT
87 11456 AS call_type_id,
88 11456 AS assignment_id,
89 'CallType' AS assignment_type,
90 127 AS own_n_sc_dow_mask
91 UNION SELECT
92 11457 AS call_type_id,
93 11457 AS assignment_id,
94 'CallType' AS assignment_type,
95 127 AS own_n_sc_dow_mask
96
97 ) s_ct ON s_ct.call_type_id = ct.id
98 LEFT JOIN published_blocks AS pb
99 ON pb.client_id = ct.client_id
100 AND pb.calendar_schedule_id = ct.calendar_schedule_id
101 AND pb.block_id = 18678
102 LEFT JOIN published_block_call_types AS pbct
103 ON pbct.client_id = ct.client_id
104 AND pbct.published_block_id = pb.id
105 AND pbct.assignment_type = s_ct.assignment_type
106 AND pbct.assignment_id = s_ct.assignment_id
107 WHERE ct.client_id = 26
108 AND ct.calendar_schedule_id = 26
109 AND ct.active = 1
110 AND s_ct.own_n_sc_dow_mask > 0
111 AND (
112 pb.id IS NULL
113 OR (
114 (pb.assignment_ids IS NOT NULL OR pb.days_of_week_mask != 127)
115 AND (
116 pbct.id IS NULL
117 OR (s_ct.own_n_sc_dow_mask & pbct.days_of_week_mask) != s_ct.own_n_sc_dow_mask
118 )
119 )
120 )
121 ORDER BY display_order ASC, abbreviation ASC
122
123 CallType Load (150.1ms) 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` = 1735
124 CalendarSchedule Load (147.2ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 26 AND `calendar_schedules`.`id` = 26 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
125 Block Load (146.9ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 26 AND `blocks`.`id` = 18678 ORDER BY start_date asc, end_date asc LIMIT 1
126 BlockSchedule Load (146.2ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 26 LIMIT 1
127 PopulationTimeframe Load (147.4ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 26 AND `population_timeframes`.`timeframe_type` = 3 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
128 RuleSet Load (148.8ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 701 LIMIT 1
129 Block Load (147.2ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 26 AND (blocks.start_date >= '2018-01-01' AND blocks.end_date <= '2018-03-31') ORDER BY start_date asc, end_date asc
130INFO : (12-21 18:51:23) phase: Scheduler::Base
131INFO : (12-21 18:51:23) from: 2018-01-01 to: 2018-03-31
132 Rule Load (157.5ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 701 ORDER BY applied desc, weight desc
133 RuleSet Load (149.7ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 701 LIMIT 1
134 Client Load (147.9ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 26 LIMIT 1
135 RuleSet Load (147.3ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 701 LIMIT 1
136 Client Load (148.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 26 LIMIT 1
137INFO : (12-21 18:51:25) rule configurations: Weekends
138 - 'Days Between Same Assignment' applied {} weight:10
139 - 'Cumulative Assignment Targets' not applied
140 - 'Cumulative Combined Assignment Targets' not applied
141 - 'Cumulative Daily Assignment Targets' not applied
142 - 'Cumulative Day of Week Targets' not applied
143 - 'Cumulative Hour Targets' not applied
144 - 'Cumulative Point Targets' not applied
145 - 'Cumulative Weekend / Weekday Assignment Targets' not applied
146 - 'Cumulative Weekend / Weekday Combined Assignment Targets' not applied
147 - 'Cumulative Weekend / Weekday Hour Targets' not applied
148 - 'Cumulative Weekend / Weekday Point Targets' not applied
149 - 'Current Assignment Targets' not applied
150 - 'Current Combined Assignment Targets' not applied
151 - 'Current Daily Assignment Targets' not applied
152 - 'Current Day of Week Targets' not applied
153 - 'Current Hour Targets' not applied
154 - 'Current Point Targets' not applied
155 - 'Current Weekend / Weekday Assignment Targets' not applied
156 - 'Current Weekend / Weekday Combined Assignment Targets' not applied
157 - 'Current Weekend / Weekday Hour Targets' not applied
158 - 'Current Weekend / Weekday Point Targets' not applied
159 - 'Pending Call Requests' not applied
160 - 'Pending No-call Requests' not applied
161 - 'Assignments Per Day' not applied
162 - 'Days Between Assignments' not applied
163 - 'Unassigned Days' not applied
164 - 'Weeks Between Same Assignment On Same Day Of Week' not applied
165 - 'Weeks Between Same Day Of Week' not applied
166INFO : (12-21 18:51:25) started: Scheduler::Cache::Collector.collect_all
167INFO : (12-21 18:51:25) collect_staffs
168 Staff Load (148.4ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `staff_entities` `staff_entities_staffs` ON `staff_entities_staffs`.`staff_id` = `staffs`.`id` 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` INNER JOIN `staff_entities` ON `staffs`.`id` = `staff_entities`.`staff_id` WHERE `staff_entities`.`entity_id` = 26 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY staff_entities.entity_id, staff_entities.display_order, last_name, first_name
169 Client Load (150.2ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 26
170 StaffSpecialization Load (146.3ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (828, 829, 831, 832, 833, 834, 12736, 9364, 835)
171 StaffCallType Load (152.8ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (828, 829, 831, 832, 833, 834, 12736, 9364, 835)
172INFO : (12-21 18:51:27) initial eligibles: [828,829,831,832,833,834,12736,9364,835]
173INFO : (12-21 18:51:27) collect_call_types
174 CallType Load (147.7ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 26 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
175INFO : (12-21 18:51:27) initial call types: [11456,11457,308,303,304,5502,305,306]
176INFO : (12-21 18:51:27) collect_chain_links
177 ChainLink Load (146.8ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 26
178 CallType Load (147.3ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (11457, 308, 11456, 306)
179 CallType Load (149.6ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (308, 11457, 306, 11456)
180INFO : (12-21 18:51:27) collect_linkages
181INFO : (12-21 18:51:27) collect_schedules
182 Schedule Load (148.5ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 26 AND (`schedules`.`schedule_on` BETWEEN '2018-01-01' AND '2018-03-31')
183INFO : (12-21 18:51:28) collect_slots
184INFO : (12-21 18:51:28) collect_approved_call_requests
185 Request Load (148.3ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 26 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-01-01' AND '2018-03-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
186INFO : (12-21 18:51:28) collect_approved_no_call_requests
187 Request Load (147.1ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 26 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-01-01' AND '2018-03-31') ORDER BY requests.schedule_on asc, requests.created_at asc
188INFO : (12-21 18:51:28) finished: Scheduler::Cache::Collector.collect_all
189INFO : (12-21 18:51:28) == begin to process requests
190 CallType Load (147.7ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 26 AND `call_types`.`active` = 1
191 (150.6ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 26 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
192 Request Load (161.9ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 26 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (303, 304, 305, 306, 308, 5502, 11456, 11457) AND (requests.schedule_on > '2017-03-31')
193 SQL (151.9ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 834 WHERE `schedules`.`client_id` = 26 AND 1=0
194INFO : (12-21 18:51:29) == end of requests processing
195INFO : (12-21 18:51:29) == begin prepopulate process
196 Block Load (148.9ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 26 AND (blocks.start_date <= '2018-03-31' AND blocks.end_date >= '2018-03-31') ORDER BY start_date asc, end_date asc LIMIT 1
197 Block Load (152.4ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 26 AND `blocks`.`block_schedule_id` = 26 AND (blocks.end_date < '2018-03-01') AND (blocks.id != 19128) ORDER BY start_date desc
198 Client Load (148.7ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 26 LIMIT 1
199INFO : (12-21 18:51:30) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
200INFO : (12-21 18:51:30) <-- clear: 1 - Scheduler::AutoPopulate::Queries::Query001_PR_01
201INFO : (12-21 18:51:30) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query001_PR_02
202INFO : (12-21 18:51:30) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query001_PR_03
203INFO : (12-21 18:51:30) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query001_PR_04
204INFO : (12-21 18:51:30) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query001_PR_05
205INFO : (12-21 18:51:30) <-- clear: 6 - Scheduler::AutoPopulate::Queries::Query005
206INFO : (12-21 18:51:30) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query01
207INFO : (12-21 18:51:30) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query012
208INFO : (12-21 18:51:30) <-- clear: 9 - Scheduler::AutoPopulate::Queries::QueryTargets
209INFO : (12-21 18:51:30) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query013
210INFO : (12-21 18:51:30) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query014
211INFO : (12-21 18:51:30) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query015_PR
212INFO : (12-21 18:51:30) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query016
213INFO : (12-21 18:51:30) <-- clear: 14 - Scheduler::AutoPopulate::Queries::Query02
214INFO : (12-21 18:51:30) <-- clear: 15 - Scheduler::AutoPopulate::Queries::Query03
215INFO : (12-21 18:51:30) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query04
216INFO : (12-21 18:51:30) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query045
217INFO : (12-21 18:51:30) <-- clear: 18 - Scheduler::AutoPopulate::Queries::QueryA
218INFO : (12-21 18:51:30) <-- clear: 19 - Scheduler::AutoPopulate::Queries::QueryB
219INFO : (12-21 18:51:30) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query05
220INFO : (12-21 18:51:30) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query06
221INFO : (12-21 18:51:30) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query07
222INFO : (12-21 18:51:30) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0702
223INFO : (12-21 18:51:30) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0705
224INFO : (12-21 18:51:30) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query0706
225INFO : (12-21 18:51:30) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query0707
226INFO : (12-21 18:51:30) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query0708
227INFO : (12-21 18:51:30) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query0709
228INFO : (12-21 18:51:30) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query08
229INFO : (12-21 18:51:30) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query085
230INFO : (12-21 18:51:30) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query09_PR
231INFO : (12-21 18:51:30) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query092
232INFO : (12-21 18:51:30) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query093
233INFO : (12-21 18:51:30) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query095
234INFO : (12-21 18:51:30) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query097
235INFO : (12-21 18:51:30) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query098
236INFO : (12-21 18:51:30) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL
237INFO : (12-21 18:51:30) <-- clear: 38 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
238INFO : (12-21 18:51:30) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
239INFO : (12-21 18:51:30) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
240INFO : (12-21 18:51:30) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
241INFO : (12-21 18:51:30) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
242INFO : (12-21 18:51:30) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
243INFO : (12-21 18:51:30) <-- clear: 44 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
244INFO : (12-21 18:51:30) <-- clear: 45 - Scheduler::AutoPopulate::Queries::Query09_12_Points
245INFO : (12-21 18:51:30) <-- clear: 46 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
246INFO : (12-21 18:51:30) <-- clear: 47 - Scheduler::AutoPopulate::Queries::Query10
247INFO : (12-21 18:51:30) <-- clear: 48 - Scheduler::AutoPopulate::Queries::QueryScore02
248INFO : (12-21 18:51:30) <-- clear: 49 - Scheduler::AutoPopulate::Queries::QueryScore03
249INFO : (12-21 18:51:30) <-- clear: 50 - Scheduler::AutoPopulate::Queries::QueryScore04
250INFO : (12-21 18:51:30) <-- clear: 51 - Scheduler::AutoPopulate::Queries::Query11
251INFO : (12-21 18:51:30) <-- clear: 52 - Scheduler::AutoPopulate::Queries::DropTables
252 (155.5ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_965635012
253 (150.4ms) DROP TABLE IF EXISTS buffer_annual_point_targets_965635012
254 (149.0ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_965635012
255 (148.8ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_965635012
256 (151.5ms) DROP TABLE IF EXISTS buffer_call_days_targets_965635012
257 (147.9ms) DROP TABLE IF EXISTS buffer_call_type_targets_965635012
258 (147.7ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_965635012
259 (148.5ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_965635012
260 (155.4ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_965635012
261 (156.0ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_965635012
262 (154.5ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_965635012
263 (149.2ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_965635012
264 (147.9ms) DROP TABLE IF EXISTS buffer_current_cd_targets_965635012
265 (150.1ms) DROP TABLE IF EXISTS buffer_current_ct_targets_965635012
266 (148.5ms) DROP TABLE IF EXISTS buffer_current_dow_targets_965635012
267 (148.2ms) DROP TABLE IF EXISTS buffer_current_hour_targets_965635012
268 (154.8ms) DROP TABLE IF EXISTS buffer_current_oa_targets_965635012
269 (148.6ms) DROP TABLE IF EXISTS buffer_current_point_targets_965635012
270 (147.8ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_965635012
271 (147.6ms) DROP TABLE IF EXISTS buffer_hour_targets_965635012
272 (147.8ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_965635012
273 (148.2ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_965635012
274 (148.2ms) DROP TABLE IF EXISTS buffer_linkage_targets_965635012
275 (198.1ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_965635012
276 (158.4ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_965635012
277 (160.6ms) DROP TABLE IF EXISTS buffer_location_privileges_965635012
278 (149.6ms) DROP TABLE IF EXISTS buffer_periods_965635012
279 (156.7ms) DROP TABLE IF EXISTS buffer_point_targets_965635012
280 (148.4ms) DROP TABLE IF EXISTS buffer_schedules_965635012
281 (149.9ms) DROP TABLE IF EXISTS buffer_target_call_types_965635012
282 (148.0ms) DROP TABLE IF EXISTS buffer_target_daily_points_965635012
283 (149.7ms) DROP TABLE IF EXISTS buffer_target_dates_list_965635012
284 (165.3ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_965635012
285 (175.1ms) DROP TABLE IF EXISTS buffer_targets_965635012
286 (147.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_965635012
287 (147.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_965635012
288 (147.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_965635012
289 (158.1ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_965635012
290 (147.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_965635012
291 (147.8ms) DROP TABLE IF EXISTS buffer_prepopulate_steps_965635012
292 (148.6ms) DROP TABLE IF EXISTS buffer_prepopulate_dates_965635012
293 (158.8ms) DROP TABLE IF EXISTS buffer_prepopulate_periods_965635012
294 (149.1ms) DROP TABLE IF EXISTS buffer_prepopulate_assignments_965635012
295 (148.2ms) DROP TABLE IF EXISTS buffer_prepopulate_staff_schedules_965635012
296 (148.0ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_965635012
297 (149.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_965635012
298 (147.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_965635012
299 (148.6ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_965635012
300 (152.4ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_965635012
301 (148.7ms) DROP TABLE IF EXISTS buffer_call_type_assignments_965635012
302 (148.3ms) DROP TABLE IF EXISTS buffer_combined_availabilities_965635012
303 (147.9ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_965635012
304 (150.3ms) DROP TABLE IF EXISTS buffer_counter_increments_965635012
305 (147.2ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_965635012
306 (147.4ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_965635012
307 (149.1ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_965635012
308 (147.9ms) DROP TABLE IF EXISTS buffer_linkage_patterns_965635012
309 (147.5ms) DROP TABLE IF EXISTS buffer_linkages_965635012
310 (149.4ms) DROP TABLE IF EXISTS buffer_location_privileges_965635012
311 (147.7ms) DROP TABLE IF EXISTS buffer_min_distances_965635012
312 (148.8ms) DROP TABLE IF EXISTS buffer_negative_relations_965635012
313 (147.8ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_965635012
314 (149.4ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_965635012
315 (147.6ms) DROP TABLE IF EXISTS buffer_rule_scores_965635012
316 (150.0ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_965635012
317 (157.0ms) DROP TABLE IF EXISTS buffer_schedule_linkages_965635012
318 (147.8ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_965635012
319 (148.4ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_965635012
320 (162.1ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_965635012
321 (148.2ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_965635012
322 (148.3ms) DROP TABLE IF EXISTS buffer_staff_specializations_965635012
323 (156.0ms) DROP TABLE IF EXISTS buffer_static_availabilities_965635012
324 (154.7ms) DROP TABLE IF EXISTS buffer_target_assignments_965635012
325 (159.6ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_965635012
326 (148.0ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_965635012
327 (151.8ms) DROP TABLE IF EXISTS buffer_assignment_limits_965635012
328 (147.3ms) delete from buffer_target_dates
329 where session_id = 965635012
330
331INFO : (12-21 18:51:42) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
332 (153.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_965635012 LIKE buffer_assignment_limit_call_types
333 (150.9ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_965635012 LIKE buffer_assignment_limit_periods
334 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_965635012 LIKE buffer_assignment_limit_staff_types
335 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_965635012 LIKE buffer_assignment_limit_staffs
336 (151.0ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_965635012 LIKE buffer_auto_populate_slots
337 (151.7ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_965635012 LIKE buffer_call_type_assignments
338 (154.1ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_965635012 LIKE buffer_combined_availabilities
339 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_965635012 LIKE buffer_combined_availability_linkages
340 (154.4ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_965635012 LIKE buffer_counter_increments
341 (154.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_965635012 LIKE buffer_linkage_assigned_days
342 (159.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_965635012 LIKE buffer_linkage_call_type_assignments
343 (151.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_965635012 LIKE buffer_linkage_dow_targets
344 (150.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_965635012 LIKE buffer_linkage_patterns
345 (196.4ms) CREATE TABLE IF NOT EXISTS buffer_linkages_965635012 LIKE buffer_linkages
346 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_965635012 LIKE buffer_location_privileges
347 (152.0ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_965635012 LIKE buffer_min_distances
348 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_965635012 LIKE buffer_negative_relations
349 (153.2ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_965635012 LIKE buffer_rule_calc_filters
350 (158.9ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_965635012 LIKE buffer_rule_calc_schedules
351 (154.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_965635012 LIKE buffer_rule_scores
352 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_965635012 LIKE buffer_rule_scores_with_corrections
353 (154.2ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_965635012 LIKE buffer_schedule_linkages
354 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_965635012 LIKE buffer_schedules_linkage_patterns
355 (153.5ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_965635012 LIKE buffer_specialization_daily_counts
356 (152.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_965635012 LIKE buffer_staff_assigned_days
357 (153.3ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_965635012 LIKE buffer_staff_daily_counts
358 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_965635012 LIKE buffer_staff_specializations
359 (152.2ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_965635012 LIKE buffer_static_availabilities
360 (160.7ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_965635012 LIKE buffer_target_assignments
361 (149.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_965635012 LIKE buffer_linkage_assignment_limits
362 (155.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_965635012 LIKE buffer_staff_assignment_limits
363 (151.1ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_965635012 LIKE buffer_assignment_limits
364 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_steps_965635012 LIKE buffer_prepopulate_steps
365 (152.6ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_dates_965635012 LIKE buffer_prepopulate_dates
366 (151.1ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_periods_965635012 LIKE buffer_prepopulate_periods
367 (149.9ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_assignments_965635012 LIKE buffer_prepopulate_assignments
368 (153.5ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_staff_schedules_965635012 LIKE buffer_prepopulate_staff_schedules
369 (152.5ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_965635012 LIKE buffer_annual_hour_targets
370 (154.3ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_965635012 LIKE buffer_annual_point_targets
371 (151.7ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_965635012 LIKE buffer_annual_targets_staff_totals
372 (147.5ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_965635012 LIKE buffer_auto_populate_slots
373 (151.4ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_965635012 LIKE buffer_call_days_targets
374 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_965635012 LIKE buffer_call_type_targets
375 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_965635012 LIKE buffer_cumulative_cd_targets
376 (158.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_965635012 LIKE buffer_cumulative_ct_targets
377 (151.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_965635012 LIKE buffer_cumulative_dow_targets
378 (151.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_965635012 LIKE buffer_cumulative_hour_targets
379 (157.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_965635012 LIKE buffer_cumulative_oa_targets
380 (149.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_965635012 LIKE buffer_cumulative_point_targets
381 (155.8ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_965635012 LIKE buffer_current_cd_targets
382 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_965635012 LIKE buffer_current_ct_targets
383 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_965635012 LIKE buffer_current_dow_targets
384 (153.2ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_965635012 LIKE buffer_current_hour_targets
385 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_965635012 LIKE buffer_current_oa_targets
386 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_965635012 LIKE buffer_current_point_targets
387 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_965635012 LIKE buffer_day_of_week_targets
388 (153.2ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_965635012 LIKE buffer_hour_targets
389 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_965635012 LIKE buffer_linkage_call_days_targets
390 (150.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_965635012 LIKE buffer_linkage_call_type_targets
391 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_965635012 LIKE buffer_linkage_targets
392 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_965635012 LIKE buffer_linkage_hour_targets
393 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_965635012 LIKE buffer_linkage_point_targets
394 (151.0ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_965635012 LIKE buffer_location_privileges
395 (156.4ms) CREATE TABLE IF NOT EXISTS buffer_periods_965635012 LIKE buffer_periods
396 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_965635012 LIKE buffer_point_targets
397 (154.5ms) CREATE TABLE IF NOT EXISTS buffer_schedules_965635012 LIKE buffer_schedules
398 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_965635012 LIKE buffer_target_call_types
399 (152.6ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_965635012 LIKE buffer_target_daily_points
400 (152.4ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_965635012 LIKE buffer_target_dates_list
401 (153.2ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_965635012 LIKE buffer_target_primary_availabilities
402 (150.7ms) CREATE TABLE IF NOT EXISTS buffer_targets_965635012 LIKE buffer_targets
403 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_965635012 LIKE buffer_tmp_targets_availabilities
404 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_965635012 LIKE buffer_tmp_targets_availabilities_exclusions
405 (149.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_965635012 LIKE buffer_tmp_targets_client_totals
406 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_965635012 LIKE buffer_tmp_targets_scheduled
407 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_965635012 LIKE buffer_tmp_targets_staff_totals
408INFO : (12-21 18:51:53) --> fill: 1 - Scheduler::AutoPopulate::Queries::Query001_PR_01
409 (149.3ms) insert into buffer_prepopulate_steps_965635012 (
410 rule_id,
411 rule_type,
412 rule_subtype,
413 staff_id,
414 x,
415 days_of_week_mask,
416 start_date,
417 start_date_first_dow,
418 required_assignments
419 )
420 select
421 pr.id as rule_id,
422 pr.rule_type,
423 coalesce(rrt.recurring_type, mrt.timeframe) as rule_subtype,
424 pr.staff_id,
425 rrt.frequency as x,
426 case
427 when (rrt.days_of_week_mask = 0) then 127
428 else coalesce(rrt.days_of_week_mask, mrt.days_of_week_mask)
429 end as days_of_week_mask,
430 rrt.start_date,
431 case
432 when (cl.start_of_week = 0) then
433 date_add(rrt.start_date, interval -(dayofweek(rrt.start_date) - 1) day)
434 else
435 date_add(rrt.start_date, interval -weekday(rrt.start_date) day)
436 end as start_date_first_dow,
437 case
438 when (rrt.id is not null) then 1
439 else mrt.number_to_assign
440 end as required_assignments
441 from
442 clients as cl
443 inner join prepopulate_rules as pr
444 on pr.autopopulate_step_id = 1735
445 left join recurring_rule_types as rrt
446 on pr.id = rrt.prepopulate_rule_id
447 left join minimum_rule_types as mrt
448 on pr.id = mrt.prepopulate_rule_id
449 where cl.id = 26
450 order by
451 pr.display_order,
452 coalesce(rrt.display_order, mrt.display_order)
453
454INFO : (12-21 18:51:54) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query001_PR_02
455 (151.9ms) insert into buffer_prepopulate_dates_965635012 (
456 day,
457 schedule_on,
458 day_of_week_mask,
459 week,
460 first_dow,
461 last_dow,
462 month,
463 first_dom,
464 last_dom,
465 block,
466 first_dob,
467 last_dob
468 )
469 select
470 dl.day,
471 dl.schedule_on,
472 (1 << (dayofweek(dl.schedule_on) - 1)) as day_of_week_mask,
473 yearweek(dl.schedule_on, dl.week_mode) as week,
474 case
475 when (dl.start_of_week = 0) then
476 date_add(dl.schedule_on, interval -(dayofweek(dl.schedule_on) - 1) day)
477 else
478 date_add(dl.schedule_on, interval -weekday(dl.schedule_on) day)
479 end as first_dow,
480 case
481 when (dl.start_of_week = 0) then
482 date_add(dl.schedule_on, interval (7 - dayofweek(dl.schedule_on)) day)
483 else
484 date_add(dl.schedule_on, interval (6 - weekday(dl.schedule_on)) day)
485 end as last_dow,
486 (year(dl.schedule_on) * 100 + month(dl.schedule_on)) as month,
487 date_add(dl.schedule_on, interval (1 - dayofmonth(dl.schedule_on)) day) as first_dom,
488 last_day(dl.schedule_on) as last_dom,
489 dl.block_id as block,
490 dl.start_date as first_dob,
491 dl.end_date as last_dob
492 from (
493 select
494 (datediff(b.start_date, b_min.start_date) + (ns.item_number + 1)) as day,
495 date_add(b.start_date, interval ns.item_number day) as schedule_on,
496 b.id as block_id,
497 b.start_date,
498 b.end_date,
499 cl.start_of_week,
500 case
501 when (cl.start_of_week = 0) then 2
502 else 7
503 end as week_mode
504 from
505 clients as cl
506 inner join blocks as b
507 on b.id in (18222,18678,19128)
508 inner join (
509 select min(start_date) as start_date
510 from blocks
511 where id in (18222,18678,19128)
512 ) as b_min
513 on 1 = 1
514 inner join numeric_sequence as ns
515 on ns.item_number <= datediff(b.end_date, b.start_date)
516 where cl.id = 26
517 order by null
518 ) as dl
519 order by null
520
521INFO : (12-21 18:51:54) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query001_PR_03
522 (150.8ms) insert into buffer_prepopulate_periods_965635012 (
523 step_id,
524 start_date,
525 end_date
526 )
527 select
528 ps.id as step_id,
529 min(
530 case
531 when (ps.rule_type = 'recurring') then d.schedule_on
532 when (ps.rule_subtype = 'per_week') then d.first_dow
533 when (ps.rule_subtype = 'per_month') then d.first_dom
534 when (ps.rule_subtype = 'per_block') then d.first_dob
535 end
536 ) as _start_date,
537 max(
538 case
539 when (ps.rule_type = 'recurring') then d.schedule_on
540 when (ps.rule_subtype = 'per_week') then d.last_dow
541 when (ps.rule_subtype = 'per_month') then d.last_dom
542 when (ps.rule_subtype = 'per_block') then d.last_dob
543 end
544 ) as _end_date
545 from
546 buffer_prepopulate_steps_965635012 as ps
547 inner join buffer_prepopulate_dates_965635012 as d
548 on (
549 ps.rule_type = 'minimum'
550 or ps.start_date <= d.schedule_on
551 )
552 and (ps.days_of_week_mask & d.day_of_week_mask) > 0
553 where
554 ps.rule_type = 'minimum'
555 or (
556 ps.rule_subtype = 'every_x_days'
557 and (datediff(d.schedule_on, ps.start_date) mod ps.x) = 0
558 )
559 or (
560 ps.rule_subtype = 'every_x_weeks'
561 and (datediff(d.first_dow, ps.start_date_first_dow) mod ps.x) = 0
562 )
563 or (
564 ps.rule_subtype = 'x_day_of_week_month'
565 and ((datediff(d.schedule_on, d.first_dom) div 7 + 1) / ps.x) = 1
566 )
567 or (
568 ps.rule_subtype = 'x_day_of_week_block'
569 and ((datediff(d.schedule_on, d.first_dob) div 7 + 1) / ps.x) = 1
570 )
571 group by
572 ps.id,
573 case
574 when (ps.rule_type = 'recurring') then d.day
575 when (ps.rule_subtype = 'per_week') then d.week
576 when (ps.rule_subtype = 'per_month') then d.month
577 when (ps.rule_subtype = 'per_block') then d.block
578 end
579 order by
580 ps.id,
581 _start_date
582
583INFO : (12-21 18:51:54) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query001_PR_04
584 (150.3ms) insert into buffer_prepopulate_assignments_965635012 (
585 period_id,
586 required_assignments,
587 scheduled_assignments
588 )
589 select
590 pp.id as period_id,
591 ps.required_assignments,
592 count(sc.id) as scheduled_assignments
593 from (
594 select
595 sc.id,
596 sc.schedule_on,
597 (1 << (sc.dayofweek_on - 1)) as day_of_week_mask,
598 sc.call_type_id,
599 sc.staff_id
600 from
601 schedules as sc
602 inner join (
603 select
604 min(start_date) as start_date,
605 max(end_date) as end_date
606 from buffer_prepopulate_periods_965635012
607 ) as pp
608 on sc.schedule_on between pp.start_date and pp.end_date
609 where sc.client_id = 26
610 order by null
611 ) as sc
612 inner join prepopulate_rule_call_types as prct
613 on sc.call_type_id = prct.call_type_id
614 inner join buffer_prepopulate_steps_965635012 as ps
615 on prct.prepopulate_rule_id = ps.rule_id
616 and sc.staff_id = ps.staff_id
617 and (sc.day_of_week_mask & ps.days_of_week_mask) > 0
618 inner join buffer_prepopulate_periods_965635012 as pp
619 on ps.id = pp.step_id
620 and sc.schedule_on between pp.start_date and pp.end_date
621 group by
622 pp.id,
623 ps.required_assignments
624 order by null
625
626 (201.3ms) insert into buffer_prepopulate_assignments_965635012 (
627 period_id,
628 required_assignments,
629 scheduled_assignments
630 )
631 select
632 pp.id as period_id,
633 ps.required_assignments,
634 0 as scheduled_assignments
635 from
636 buffer_prepopulate_periods_965635012 as pp
637 left join buffer_prepopulate_assignments_965635012 as pa
638 on pp.id = pa.period_id
639 inner join buffer_prepopulate_steps_965635012 as ps
640 on pp.step_id = ps.id
641 where pa.id is null
642 order by null
643
644INFO : (12-21 18:51:54) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query001_PR_05
645 (149.7ms) insert into buffer_auto_populate_slots_965635012 (
646 schedule_on,
647 day_of_week_mask,
648 call_type_id,
649 display_order,
650 should_be_populated
651 )
652 select distinct
653 pd.schedule_on,
654 pd.day_of_week_mask,
655 ct.id as call_type_id,
656 ct.display_order,
657 1 as should_be_populated
658 from
659 buffer_prepopulate_steps_965635012 as ps
660 inner join buffer_prepopulate_periods_965635012 as pp
661 on ps.id = pp.step_id
662 inner join buffer_prepopulate_dates_965635012 as pd
663 on pd.schedule_on between pp.start_date and pp.end_date
664 and (ps.days_of_week_mask & pd.day_of_week_mask) > 0
665 inner join prepopulate_rule_call_types as prct
666 on ps.rule_id = prct.prepopulate_rule_id
667 inner join call_types as ct
668 on prct.call_type_id = ct.id
669 and ct.active = 1
670 and (pd.day_of_week_mask & ct.days_of_week_mask) > 0
671 order by null
672
673INFO : (12-21 18:51:54) --> fill: 6 - Scheduler::AutoPopulate::Queries::Query005
674 (148.7ms) insert into buffer_auto_populate_slots_965635012 (
675 schedule_on,
676 day_of_week_mask,
677 call_type_id,
678 display_order,
679 should_be_populated
680 )
681 select distinct
682 date_add(
683 auto_populate_slots.schedule_on,
684 interval linkage_relations.distance_in_days day
685 ) as schedule_on,
686 (1 << (
687 dayofweek(
688 date_add(
689 auto_populate_slots.schedule_on,
690 interval linkage_relations.distance_in_days day
691 )
692 ) - 1
693 )
694 ) as day_of_week_mask,
695 linkage_relations.linked_call_type_id as call_type_id,
696 call_types.display_order,
697 0 as should_be_populated
698 from
699 buffer_auto_populate_slots_965635012 as auto_populate_slots
700 inner join linkage_relations
701 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
702 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
703 left join buffer_auto_populate_slots_965635012 as auto_populate_slots_2
704 on date_add(
705 auto_populate_slots.schedule_on,
706 interval linkage_relations.distance_in_days day
707 ) = auto_populate_slots_2.schedule_on
708 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
709 inner join call_types
710 on linkage_relations.linked_call_type_id = call_types.id
711 where auto_populate_slots_2.id is null
712 order by null
713
714INFO : (12-21 18:51:55) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query01
715 (155.1ms) insert into linkage_relations (
716 client_id,
717 call_type_id,
718 day_of_week_mask,
719 linked_call_type_id,
720 distance_in_days,
721 linkage_template_id
722 )
723 select distinct
724 26 /* client_id */ as client_id,
725 aps.call_type_id,
726 aps.day_of_week_mask,
727 aps.call_type_id as linked_call_type_id,
728 0 as distance_in_days,
729 coalesce(
730 lr.linkage_template_id,
731 @linkage_template_max := @linkage_template_max + 1
732 ) as linkage_template_id
733 from
734 buffer_auto_populate_slots_965635012/* client_id */ as aps
735 inner join (
736 select @linkage_template_max := count(distinct linkage_template_id) - 1
737 from linkage_relations
738 where client_id = 26 /* client_id */
739 order by null
740 ) as lt
741 on 1 = 1
742 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
743 on aps.call_type_id = lr.call_type_id
744 and aps.day_of_week_mask = lr.day_of_week_mask
745 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
746 on aps.call_type_id = lr_2.call_type_id
747 and aps.day_of_week_mask = lr_2.day_of_week_mask
748 and aps.call_type_id = lr_2.linked_call_type_id
749 and lr_2.distance_in_days = 0
750 where lr_2.id is null
751 order by null
752
753 (152.3ms) insert into buffer_schedules_965635012 (
754 call_type_id,
755 staff_id,
756 schedule_on,
757 day_of_week_mask,
758 is_weekend,
759 display_order,
760 week_number,
761 should_be_evaluated,
762 should_be_populated,
763 linkage_instance_id
764 )
765 select
766 call_type_id,
767 staff_id,
768 schedule_on,
769 day_of_week_mask,
770 is_weekend,
771 display_order,
772 week_number,
773 max(should_be_evaluated) as should_be_evaluated,
774 max(should_be_populated) as should_be_populated,
775 min(linkage_instance_id) as linkage_instance_id
776 from (
777 select
778 auto_populate_slots_2.call_type_id,
779 schedules.staff_id,
780 auto_populate_slots_2.schedule_on,
781 auto_populate_slots_2.day_of_week_mask,
782 case
783 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
784 else 1
785 end as is_weekend,
786 auto_populate_slots_2.display_order,
787 yearweek(
788 auto_populate_slots_2.schedule_on,
789 case
790 when (clients.start_of_week = 0) then 2
791 else 7
792 end
793 ) as week_number,
794 case
795 when (schedules.staff_id is null) then 1
796 else 0
797 end as should_be_evaluated,
798 (
799 case
800 when (
801 auto_populate_slots_2.should_be_populated = 1
802 and schedules.staff_id is null
803 ) then 1
804 else 0
805 end
806 ) as should_be_populated,
807 (
808 linkage_relations.linkage_template_id * 1000000 +
809 yearweek(
810 auto_populate_slots.schedule_on,
811 case
812 when (clients.start_of_week = 0) then 2
813 else 7
814 end
815 )
816 ) as linkage_instance_id
817 from
818 buffer_auto_populate_slots_965635012 as auto_populate_slots
819 inner join clients
820 on clients.id = 26 /* client_id */
821 inner join linkage_relations
822 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
823 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
824 inner join buffer_auto_populate_slots_965635012 as auto_populate_slots_2
825 on date_add(
826 auto_populate_slots.schedule_on,
827 interval linkage_relations.distance_in_days day
828 ) = auto_populate_slots_2.schedule_on
829 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
830 left join schedules
831 on auto_populate_slots_2.call_type_id = schedules.call_type_id
832 and auto_populate_slots_2.schedule_on = schedules.schedule_on
833 -- we should exclude do-not-populate slots
834 left join autopopulate_exclusions
835 on auto_populate_slots_2.call_type_id = autopopulate_exclusions.call_type_id
836 and auto_populate_slots_2.schedule_on = autopopulate_exclusions.schedule_on
837 where
838 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
839 -- once we added someone to that slot then it should be considered as a regular one
840 schedules.id is not null
841 or autopopulate_exclusions.id is null
842 order by null
843 ) as t1
844 group by
845 call_type_id,
846 staff_id,
847 schedule_on,
848 day_of_week_mask,
849 display_order,
850 week_number
851 order by null
852
853INFO : (12-21 18:51:55) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query012
854 (158.0ms) update
855 buffer_schedules_965635012 as sc
856 inner join call_types as ct
857 on sc.call_type_id = ct.id
858 inner join calendar_schedules as cs
859 on ct.calendar_schedule_id = cs.id
860 inner join blocks as b
861 on cs.block_schedule_id = b.block_schedule_id
862 and sc.schedule_on between b.start_date and b.end_date
863 set
864 sc.calendar_schedule_id = cs.id,
865 sc.block_id = b.id
866
867 BlockSchedule Load (146.1ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 26 LIMIT 1
868 (156.6ms) insert into buffer_periods_965635012 (
869 calendar_schedule_id,
870 block_schedule_id,
871 period_offset,
872 start_block_display_order,
873 end_block_display_order,
874 start_date,
875 end_date,
876 should_be_populated,
877 should_be_evaluated
878 )
879 select
880 t1.calendar_schedule_id,
881 t1.block_schedule_id,
882 t1.period_offset,
883 t2.start_block_display_order,
884 t2.end_block_display_order,
885 t2.start_date,
886 t2.end_date,
887 t1.should_be_populated,
888 t1.should_be_evaluated
889 from (
890 select
891 sc.calendar_schedule_id,
892 b.block_schedule_id,
893 case
894 when ((month(b.start_date) - pt.begin_period) >= 0)
895 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
896 else
897 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
898 end as period_offset,
899 max(sc.should_be_populated) as should_be_populated,
900 max(sc.should_be_evaluated) as should_be_evaluated
901 from (
902 select
903 sc.block_id,
904 sc.calendar_schedule_id,
905 max(ps.should_be_populated) as should_be_populated,
906 1 as should_be_evaluated
907 from
908 buffer_schedules_965635012 as sc
909 inner join buffer_auto_populate_slots_965635012 as ps
910 on sc.schedule_on = ps.schedule_on
911 and sc.call_type_id = ps.call_type_id
912 group by
913 sc.block_id,
914 sc.calendar_schedule_id
915 order by null
916 ) as sc
917 inner join blocks as b
918 on sc.block_id = b.id
919 inner join population_timeframes as pt
920 on pt.client_id = 26
921 and pt.timeframe_type = 3
922 group by
923 sc.calendar_schedule_id,
924 b.block_schedule_id,
925 period_offset
926 order by null
927 ) as t1
928 inner join (
929 select
930 b.block_schedule_id,
931 case
932 when ((month(b.start_date) - pt.begin_period) >= 0)
933 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
934 else
935 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
936 end as period_offset,
937 min(b.display_order) as start_block_display_order,
938 max(b.display_order) as end_block_display_order,
939 min(b.start_date) as start_date,
940 max(b.end_date) as end_date
941 from
942 blocks as b
943 inner join population_timeframes as pt
944 on pt.client_id = 26
945 and pt.timeframe_type = 3
946 where
947 b.client_id = 26
948 group by
949 b.block_schedule_id,
950 period_offset
951 order by null
952 ) as t2
953 on t1.block_schedule_id = t2.block_schedule_id
954 and t1.period_offset = t2.period_offset
955 order by null
956
957 (151.7ms) update
958 buffer_schedules_965635012 as sc
959 inner join buffer_periods_965635012 as p
960 on sc.calendar_schedule_id = p.calendar_schedule_id
961 and sc.schedule_on between p.start_date and p.end_date
962 set sc.period_offset = p.period_offset
963
964INFO : (12-21 18:51:55) --> fill: 9 - Scheduler::AutoPopulate::Queries::QueryTargets
965INFO : (12-21 18:51:55) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query013
966 (160.9ms) insert into buffer_counter_increments_965635012 (
967 schedule_id,
968 calls,
969 points,
970 hours,
971 affects_cumulative_targets
972 )
973 select
974 sc.id as schedule_id,
975 case
976 when (ct.included_in_targets > 0) then 1
977 else 0
978 end as calls,
979 coalesce(
980 case
981 when (hl.observed_date is null or dp.point is not null) then dp.point
982 else dp_2.point
983 end,
984 0.0
985 ) as points,
986 ct.hours,
987 case
988 when (b.display_order between (t1.timeframe_ending - (15 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
989 else 0
990 end as affects_cumulative_targets
991 from
992 buffer_schedules_965635012 as sc
993 inner join (
994 select
995 id,
996 included_in_targets,
997 coalesce(
998 timestampdiff(
999 minute,
1000 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
1001 concat(
1002 '1900-01-0',
1003 case
1004 when ((
1005 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
1006 )
1007 and (
1008 timediff(
1009 maketime(end_time_hour, end_time_minute, 0),
1010 maketime(start_time_hour, start_time_minute, 0)
1011 ) <= 0
1012 )
1013 ) then '2'
1014 else '1'
1015 end,
1016 ' ',
1017 maketime(end_time_hour, end_time_minute, 0)
1018 )
1019 ) / 60,
1020 0.0
1021 ) as hours
1022 from call_types
1023 where
1024 client_id = 26
1025 and active = 1
1026 order by null
1027 ) as ct
1028 on sc.call_type_id = ct.id
1029 inner join blocks as b
1030 on sc.block_id = b.id
1031 inner join (
1032 select
1033 calendar_schedule_id,
1034 max(
1035 case
1036 when (should_be_populated = 1) then end_block_display_order
1037 else null
1038 end
1039 ) as timeframe_ending
1040 from buffer_periods_965635012
1041 group by calendar_schedule_id
1042 order by null
1043 ) as t1
1044 on sc.calendar_schedule_id = t1.calendar_schedule_id
1045 left join (
1046 select
1047 hd.observed_date,
1048 case
1049 when (hd.observed_date > hd.extended_date) then hd.extended_date
1050 else hd.observed_date
1051 end as date_from,
1052 case
1053 when (hd.observed_date < hd.extended_date) then hd.extended_date
1054 else hd.observed_date
1055 end as date_to
1056 from
1057 holidays as h
1058 inner join holiday_dates as hd
1059 on h.id = hd.holiday_id
1060 inner join (
1061 select
1062 year(min(schedule_on)) as year_from,
1063 year(max(schedule_on)) as year_to
1064 from buffer_schedules_965635012
1065 where should_be_evaluated = 1
1066 ) as y
1067 on hd.year between y.year_from and y.year_to
1068 where
1069 h.client_id = 26
1070 and (
1071 h.observed = 1
1072 or h.extended = 1
1073 )
1074 order by null
1075 ) as hl
1076 on sc.schedule_on between hl.date_from and hl.date_to
1077 left join assignment_annotations as aa
1078 on ct.id = aa.assignmentable_id
1079 and aa.assignmentable_type = 'CallType'
1080 and aa.annotation_id = 0 /* annotation = "None" */
1081 and aa.applicable = 1
1082 left join assignment_annotation_daily_points as dp
1083 on aa.id = dp.assignment_annotation_id
1084 and case
1085 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
1086 when (sc.schedule_on = hl.observed_date) then 7
1087 else 8
1088 end = dp.day
1089 left join assignment_annotation_daily_points as dp_2
1090 on aa.id = dp_2.assignment_annotation_id
1091 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
1092 where sc.should_be_evaluated = 1
1093 order by null
1094
1095INFO : (12-21 18:51:56) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query014
1096 (147.0ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
1097 insert into buffer_linkage_patterns_965635012 (
1098 period_offset,
1099 calendar_schedule_id,
1100 block_id,
1101 block_start_date,
1102 block_start_dow,
1103 block_length,
1104 linkage_pattern_id,
1105 pattern_start_dow,
1106 days_before_start,
1107 pattern_length,
1108 start_offset,
1109 iterations_count
1110 )
1111 select
1112 b1.period_offset,
1113 b1.calendar_schedule_id,
1114 b1.block_id,
1115 b2.start_date as block_start_date,
1116 dayofweek(b2.start_date) as block_start_dow,
1117 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
1118 lp.id as linkage_pattern_id,
1119 (lp.start_on + 1) as pattern_start_dow,
1120 lp.days_before_start,
1121 (lp.length * 7) as pattern_length,
1122 null as start_offset,
1123 null as iterations_count
1124 from (
1125 select
1126 calendar_schedule_id,
1127 block_id,
1128 period_offset
1129 from buffer_schedules_965635012
1130 group by
1131 calendar_schedule_id,
1132 block_id,
1133 period_offset
1134 order by null
1135 ) as b1
1136 inner join blocks as b2
1137 on b1.block_id = b2.id
1138 inner join linkage_patterns as lp
1139 on lp.client_id = 26
1140 and b1.calendar_schedule_id = lp.calendar_schedule_id
1141 order by null
1142
1143 (148.1ms) -- 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
1144 update buffer_linkage_patterns_965635012
1145 set start_offset = if(
1146 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
1147 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
1148 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
1149 )
1150
1151 (149.6ms) -- 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)
1152 -- a pattern should be repeated couple of times (in order to cover the entire block)
1153 update buffer_linkage_patterns_965635012
1154 set iterations_count =
1155 ceiling(
1156 (
1157 block_length +
1158 case
1159 when (start_offset < 0) then abs(start_offset)
1160 else 0
1161 end
1162 ) * 1.0 / pattern_length
1163 )
1164
1165 (149.8ms) -- Generate an assignments sequence for every linkage pattern
1166 insert into buffer_schedules_linkage_patterns_965635012 (
1167 call_type_id,
1168 staff_id,
1169 schedule_on,
1170 period_offset,
1171 day_of_week_mask,
1172 display_order,
1173 week_number,
1174 should_be_evaluated,
1175 should_be_populated,
1176 linkage_instance_id,
1177 block_id,
1178 calendar_schedule_id
1179 )
1180 select
1181 lps.assignment_id as call_type_id,
1182 null as staff_id,
1183 case
1184 when (lp.start_offset < 0)
1185 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
1186 else
1187 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
1188 end as schedule_on,
1189 lp.period_offset,
1190 null as day_of_week_mask,
1191 null as display_order,
1192 null as week_number,
1193 0 as should_be_evaluated,
1194 0 as should_be_populated,
1195 lp.linkage_pattern_id as linkage_instance_id,
1196 lp.block_id,
1197 lp.calendar_schedule_id
1198 from
1199 numeric_sequence as ns
1200 inner join buffer_linkage_patterns_965635012 as lp
1201 on ns.item_number <= (lp.iterations_count - 1)
1202 inner join linkage_pattern_slots as lps
1203 on lp.linkage_pattern_id = lps.linkage_pattern_id
1204 and lps.assignment_type = 'CallType'
1205 where
1206 case
1207 when (lp.start_offset < 0)
1208 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
1209 else
1210 (ns.item_number * lp.pattern_length + lps.offset)
1211 end between 0 and (lp.block_length - 1)
1212 order by null
1213
1214 (147.5ms) -- Fill the columns that we left blank in the previous "INSERT"
1215 update
1216 buffer_schedules_linkage_patterns_965635012 as slp
1217 inner join clients as cl
1218 on cl.id = 26
1219 inner join call_types as ct
1220 on slp.call_type_id = ct.id
1221 left join buffer_schedules_965635012 as bs
1222 on slp.schedule_on = bs.schedule_on
1223 and slp.call_type_id = bs.call_type_id
1224 set
1225 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
1226 slp.display_order = ct.display_order,
1227 slp.week_number =
1228 yearweek(
1229 slp.schedule_on,
1230 case
1231 when (cl.start_of_week = 0) then 2
1232 else 7
1233 end
1234 ),
1235 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
1236 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
1237
1238 (149.3ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
1239 delete slp_1
1240 from
1241 buffer_schedules_linkage_patterns_965635012 as slp_1
1242 inner join (
1243 select linkage_instance_id
1244 from buffer_schedules_linkage_patterns_965635012
1245 group by linkage_instance_id
1246 having
1247 max(should_be_evaluated) = 0
1248 and max(should_be_populated) = 0
1249 order by null
1250 ) as slp_2
1251 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
1252
1253 (146.5ms) -- Delete do-not-populate slots
1254 delete slp
1255 from
1256 buffer_schedules_linkage_patterns_965635012 as slp
1257 inner join autopopulate_exclusions as ae
1258 on slp.call_type_id = ae.call_type_id
1259 and slp.schedule_on = ae.schedule_on
1260 left join schedules as sc
1261 on slp.call_type_id = sc.call_type_id
1262 and slp.schedule_on = sc.schedule_on
1263 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
1264 -- once we added someone to that slot then it should be considered as a regular one
1265 where sc.id is null
1266
1267 (146.2ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
1268 update
1269 buffer_schedules_linkage_patterns_965635012 as slp
1270 inner join buffer_schedules_965635012 as bs_1
1271 on slp.schedule_on = bs_1.schedule_on
1272 and slp.call_type_id = bs_1.call_type_id
1273 inner join buffer_schedules_965635012 as bs_2
1274 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
1275 set bs_2.linkage_instance_id = slp.linkage_instance_id
1276
1277 (148.7ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
1278 -- should be copied over to the buffer schedules table (used during the auto-populate process)
1279 insert into buffer_schedules_965635012 (
1280 call_type_id,
1281 staff_id,
1282 schedule_on,
1283 period_offset,
1284 day_of_week_mask,
1285 is_weekend,
1286 display_order,
1287 week_number,
1288 should_be_evaluated,
1289 should_be_populated,
1290 linkage_instance_id,
1291 block_id,
1292 calendar_schedule_id
1293 )
1294 select
1295 call_type_id,
1296 staff_id,
1297 schedule_on,
1298 period_offset,
1299 day_of_week_mask,
1300 case
1301 when (day_of_week_mask & 62 > 0) then 0
1302 else 1
1303 end as is_weekend,
1304 display_order,
1305 week_number,
1306 should_be_evaluated,
1307 should_be_populated,
1308 linkage_instance_id,
1309 block_id,
1310 calendar_schedule_id
1311 from buffer_schedules_linkage_patterns_965635012
1312 where
1313 should_be_evaluated = 0
1314 and should_be_populated = 0
1315
1316INFO : (12-21 18:51:57) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query015_PR
1317 (149.0ms) insert into buffer_prepopulate_staff_schedules_965635012 (
1318 step_id,
1319 period_id,
1320 schedule_id,
1321 linkage_instance_id,
1322 staff_id
1323 )
1324 select
1325 ps.id as step_id,
1326 pp.id as period_id,
1327 bs.id as schedule_id,
1328 bs.linkage_instance_id,
1329 ps.staff_id
1330 from
1331 buffer_prepopulate_steps_965635012 as ps
1332 inner join buffer_prepopulate_periods_965635012 as pp
1333 on ps.id = pp.step_id
1334 inner join buffer_prepopulate_dates_965635012 as pd
1335 on pd.schedule_on between pp.start_date and pp.end_date
1336 and (ps.days_of_week_mask & pd.day_of_week_mask) > 0
1337 inner join prepopulate_rule_call_types as prct
1338 on ps.rule_id = prct.prepopulate_rule_id
1339 inner join buffer_schedules_965635012 as bs
1340 on pd.schedule_on = bs.schedule_on
1341 and prct.call_type_id = bs.call_type_id
1342 order by pp.id
1343
1344 (145.8ms) select 1
1345 from buffer_prepopulate_staff_schedules_965635012
1346 limit 1
1347
1348INFO : (12-21 18:51:57) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query016
1349 (149.9ms) insert into buffer_schedules_965635012 (
1350 call_type_id,
1351 staff_id,
1352 schedule_on,
1353 day_of_week_mask,
1354 is_weekend,
1355 display_order,
1356 should_be_evaluated,
1357 should_be_populated,
1358 linkage_instance_id
1359 )
1360 select distinct
1361 schedules_call_overlap.call_type_id,
1362 schedules_call_overlap.staff_id,
1363 schedules_call_overlap.schedule_on,
1364 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
1365 case
1366 when (dayofweek(schedules_call_overlap.schedule_on) between 2 and 6) then 0
1367 else 1
1368 end as is_weekend,
1369 call_types.display_order,
1370 0 as should_be_evaluated,
1371 0 as should_be_populated,
1372 -1 as linkage_instance_id
1373 from (
1374 select schedule_on
1375 from buffer_schedules_965635012
1376 where should_be_evaluated = 1
1377 group by schedule_on
1378 order by null
1379 ) as buffer_schedules_1
1380 inner join schedules as schedules_call_overlap
1381 on schedules_call_overlap.client_id = 26
1382 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1383 left join buffer_schedules_965635012 as buffer_schedules_2
1384 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1385 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1386 inner join call_types
1387 on schedules_call_overlap.call_type_id = call_types.id
1388 where buffer_schedules_2.id is null
1389 order by null
1390
1391INFO : (12-21 18:51:57) --> fill: 14 - Scheduler::AutoPopulate::Queries::Query02
1392INFO : (12-21 18:51:57) --> fill: 15 - Scheduler::AutoPopulate::Queries::Query03
1393INFO : (12-21 18:51:57) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query04
1394INFO : (12-21 18:51:57) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query045
1395INFO : (12-21 18:51:57) --> fill: 18 - Scheduler::AutoPopulate::Queries::QueryA
1396 (153.7ms) insert into buffer_schedules_965635012 (
1397 call_type_id,
1398 staff_id,
1399 schedule_on,
1400 day_of_week_mask,
1401 is_weekend,
1402 display_order,
1403 should_be_evaluated,
1404 should_be_populated,
1405 linkage_instance_id
1406 )
1407 select distinct straight_join
1408 schedules.call_type_id,
1409 schedules.staff_id,
1410 schedules.schedule_on,
1411 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1412 case
1413 when (dayofweek(schedules.schedule_on) between 2 and 6) then 0
1414 else 1
1415 end as is_weekend,
1416 call_types.display_order,
1417 0 as should_be_evaluated,
1418 0 as should_be_populated,
1419 -1 as linkage_instance_id
1420 from
1421 buffer_schedules_965635012 as buffer_schedules_1
1422 inner join negative_links_influences use index (ix_unique_links)
1423 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1424 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1425 and negative_links_influences.source_type = 'CallType'
1426 and negative_links_influences.target_type = 'CallType'
1427 inner join schedules use index (ix_schedules_call_date)
1428 on date_add(
1429 buffer_schedules_1.schedule_on,
1430 interval negative_links_influences.offset day
1431 ) = schedules.schedule_on
1432 and negative_links_influences.target_id = schedules.call_type_id
1433 left join buffer_schedules_965635012 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1434 on schedules.schedule_on = buffer_schedules_2.schedule_on
1435 and schedules.call_type_id = buffer_schedules_2.call_type_id
1436 inner join call_types
1437 on schedules.call_type_id = call_types.id
1438 where
1439 buffer_schedules_1.linkage_instance_id >= 0
1440 and buffer_schedules_1.should_be_evaluated = 1
1441 and buffer_schedules_2.id is null
1442 order by null
1443
1444INFO : (12-21 18:51:58) --> fill: 19 - Scheduler::AutoPopulate::Queries::QueryB
1445 (151.4ms) insert into buffer_schedules_965635012 (
1446 call_type_id,
1447 staff_id,
1448 schedule_on,
1449 day_of_week_mask,
1450 is_weekend,
1451 display_order,
1452 should_be_evaluated,
1453 should_be_populated,
1454 linkage_instance_id
1455 )
1456 select distinct straight_join
1457 (-1 * negative_links_influences.target_id) as call_type_id,
1458 vacations.staff_id,
1459 vacations.schedule_on,
1460 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1461 vacations.weekend_on as is_weekend,
1462 -1 as display_order,
1463 0 as should_be_evaluated,
1464 0 as should_be_populated,
1465 -1 as linkage_instance_id
1466 from
1467 buffer_schedules_965635012 as buffer_schedules_1
1468 inner join negative_links_influences use index (ix_unique_links)
1469 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1470 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1471 and negative_links_influences.source_type = 'CallType'
1472 and negative_links_influences.target_id > 0
1473 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1474 inner join vacations use index (ix_vacations_on_schedule_and_client)
1475 on vacations.client_id = 26 /* client_id */
1476 and date_add(
1477 buffer_schedules_1.schedule_on,
1478 interval negative_links_influences.offset day
1479 ) = vacations.schedule_on
1480 and negative_links_influences.target_id = vacations.non_clinical_type_id
1481 and vacations.status in (3) /* based on the "non-working requests" settings */
1482 left join buffer_schedules_965635012 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1483 on vacations.staff_id = buffer_schedules_2.staff_id
1484 and vacations.schedule_on = buffer_schedules_2.schedule_on
1485 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1486 where
1487 buffer_schedules_1.linkage_instance_id >= 0
1488 and buffer_schedules_1.should_be_evaluated = 1
1489 and buffer_schedules_2.id is null
1490 order by null
1491
1492INFO : (12-21 18:51:58) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query05
1493 (149.2ms) insert into buffer_linkages_965635012 (
1494 linkage_instance_id,
1495 slots_count_total,
1496 has_conflicts
1497 )
1498 select straight_join
1499 schedules.linkage_instance_id,
1500 count(1) as slots_count_total,
1501 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1502 from
1503 buffer_schedules_965635012 as schedules
1504 left join (
1505 select straight_join
1506 linkage_relations.linkage_template_id
1507 from linkage_relations
1508 inner join negative_links_influences use index (ix_unique_links)
1509 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1510 and linkage_relations.call_type_id = negative_links_influences.source_id
1511 and negative_links_influences.source_type = 'CallType'
1512 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1513 and negative_links_influences.target_type = 'CallType'
1514 and linkage_relations.distance_in_days = negative_links_influences.offset
1515 where linkage_relations.client_id = 26 /* client_id */
1516 and (
1517 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1518 or linkage_relations.distance_in_days != 0
1519 )
1520 group by linkage_relations.linkage_template_id
1521 order by null
1522 ) as linkage_conflicts
1523 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1524 where schedules.linkage_instance_id >= 0
1525 group by
1526 schedules.linkage_instance_id,
1527 has_conflicts
1528 order by null
1529
1530INFO : (12-21 18:51:58) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query06
1531 (151.2ms) insert into buffer_target_assignments_965635012 (
1532 call_type_id,
1533 target_group_id,
1534 assignment_id,
1535 assignment_type
1536 )
1537 select
1538 t1.call_type_id,
1539 ifnull(tg.id, 0) as target_group_id,
1540 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1541 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1542 from (
1543 select ct.id as call_type_id
1544 from
1545 buffer_schedules_965635012 as sc
1546 inner join call_types as ct
1547 on sc.call_type_id = ct.id
1548 and ct.active = 1
1549 and ct.included_in_targets > 0
1550 where linkage_instance_id >= 0
1551 group by ct.id
1552 order by null
1553 ) as t1
1554 left join target_group_calls as tgc
1555 on t1.call_type_id = tgc.call_type_id
1556 left join target_groups as tg
1557 on tgc.target_group_id = tg.id
1558 and tg.active = 1
1559 order by null
1560
1561INFO : (12-21 18:51:58) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query07
1562INFO : (12-21 18:51:58) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0702
1563INFO : (12-21 18:51:58) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0705
1564 (150.1ms) insert into buffer_linkage_call_type_assignments_965635012 (
1565 linkage_instance_id,
1566 call_type_id,
1567 is_weekend,
1568 period_offset,
1569 calendar_schedule_id,
1570 max_assignments,
1571 assignments_count
1572 )
1573 select
1574 s.linkage_instance_id,
1575 s.call_type_id,
1576 s.is_weekend,
1577 s.period_offset,
1578 s.calendar_schedule_id,
1579 case
1580 when (s.is_weekend = 0) then ct.max_weekday_assignments
1581 else ct.max_weekend_assignments
1582 end as _max_assignments,
1583 sum(1) as assignments_count
1584 from buffer_schedules_965635012 as s
1585 inner join call_types as ct
1586 on s.call_type_id = ct.id
1587 and ct.active = 1
1588 and (
1589 ct.max_weekday_assignments is not null
1590 or ct.max_weekend_assignments is not null
1591 )
1592 where s.linkage_instance_id >= 0
1593 and s.staff_id is null
1594 group by
1595 s.linkage_instance_id,
1596 s.call_type_id,
1597 s.is_weekend,
1598 s.period_offset,
1599 s.calendar_schedule_id,
1600 _max_assignments
1601 having _max_assignments >= 0
1602 order by null
1603
1604INFO : (12-21 18:51:58) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query0706
1605 (295.1ms) insert into buffer_call_type_assignments_965635012 (
1606 staff_id,
1607 call_type_id,
1608 is_weekend,
1609 period_offset,
1610 calendar_schedule_id,
1611 assignments_count
1612 )
1613 select
1614 sc.staff_id,
1615 sc.call_type_id,
1616 sc.weekend_on as is_weekend,
1617 p.period_offset,
1618 p.calendar_schedule_id,
1619 sum(1) as assignments_count
1620 from buffer_periods_965635012 as p
1621 inner join call_types as ct
1622 on p.calendar_schedule_id = ct.calendar_schedule_id
1623 and ct.active = 1
1624 and (
1625 ct.max_weekday_assignments is not null
1626 or ct.max_weekend_assignments is not null
1627 )
1628 inner join schedules as sc
1629 on ct.id = sc.call_type_id
1630 and sc.schedule_on between p.start_date and p.end_date
1631 and sc.staff_id is not null
1632 where p.should_be_evaluated = 1
1633 group by
1634 sc.staff_id,
1635 sc.call_type_id,
1636 sc.weekend_on,
1637 p.period_offset,
1638 p.calendar_schedule_id
1639 order by null
1640
1641INFO : (12-21 18:51:59) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query0707
1642INFO : (12-21 18:51:59) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query0708
1643 (151.1ms) insert into buffer_linkage_assigned_days_965635012 (
1644 linkage_instance_id,
1645 period_offset,
1646 week_number,
1647 schedule_on,
1648 is_weekend
1649 )
1650 select distinct
1651 sc.linkage_instance_id,
1652 sc.period_offset,
1653 sc.week_number,
1654 sc.schedule_on,
1655 sc.is_weekend
1656 from
1657 buffer_schedules_965635012 as sc
1658 inner join call_types as ct
1659 on sc.call_type_id = ct.id
1660 and ct.unassigned_day = 0
1661 where
1662 sc.linkage_instance_id >= 0
1663 and sc.staff_id is null
1664 order by null
1665
1666INFO : (12-21 18:51:59) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query0709
1667INFO : (12-21 18:51:59) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query08
1668 (168.3ms) insert into buffer_staff_daily_counts_965635012 (
1669 staff_id, schedule_on, linkage_instance_id, slots_count
1670 )
1671 select staff_id, schedule_on, null as linkage_instance_id,
1672 count(1) as slots_count
1673 from buffer_schedules_965635012
1674 where staff_id is not null
1675 and call_type_id > 0 /* VC */
1676 group by staff_id, schedule_on
1677 order by null
1678
1679 (150.3ms) insert into buffer_staff_daily_counts_965635012 (
1680 staff_id, schedule_on, linkage_instance_id, slots_count
1681 )
1682 select staff_id, schedule_on, linkage_instance_id,
1683 count(1) as slots_count
1684 from buffer_schedules_965635012
1685 where staff_id is not null
1686 and call_type_id > 0 /* VC */
1687 group by staff_id, schedule_on, linkage_instance_id
1688 order by null
1689
1690INFO : (12-21 18:51:59) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query085
1691 (252.1ms) insert into buffer_staff_specializations_965635012 (
1692 staff_id, specialization_id
1693 )
1694 select staff_id, specialization_id
1695 from view_staff_specializations
1696 where client_id = 26
1697 order by null
1698
1699 (147.9ms) insert into buffer_specialization_daily_counts_965635012 (
1700 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1701 )
1702 select sp.id as specialization_id,
1703 null as staff_id,
1704 bs_1.schedule_on,
1705 count(distinct st_sp.staff_id) as staffs_scheduled,
1706 sp.max_scheduled,
1707 if(bs_1.day_of_week_mask & sp.days_of_week_mask > 0 or sp.days_of_week_mask = 0, 1, 0) as should_be_evaluated
1708 from (
1709 select schedule_on, day_of_week_mask
1710 from buffer_schedules_965635012
1711 where should_be_evaluated = 1
1712 group by schedule_on, day_of_week_mask
1713 order by null
1714 ) as bs_1
1715 inner join specializations as sp
1716 on sp.client_id = 26
1717 and sp.active = 1
1718 inner join buffer_schedules_965635012 as bs_2
1719 on bs_1.schedule_on = bs_2.schedule_on
1720 and bs_2.call_type_id > 0 /* is not VC */
1721 inner join max_applicable_call_types as mact
1722 on sp.id = mact.specialization_id
1723 and bs_2.call_type_id = mact.call_type_id
1724 left join buffer_staff_specializations_965635012 as st_sp
1725 on bs_2.staff_id = st_sp.staff_id
1726 and sp.id = st_sp.specialization_id
1727 group by sp.id, bs_1.schedule_on, sp.max_scheduled
1728 order by null
1729
1730 (146.8ms) insert into buffer_specialization_daily_counts_965635012 (
1731 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1732 )
1733 select sp.id as specialization_id,
1734 st_sp.staff_id,
1735 bs_1.schedule_on,
1736 1 as staffs_scheduled,
1737 sp.max_scheduled,
1738 if(bs_1.day_of_week_mask & sp.days_of_week_mask > 0 or sp.days_of_week_mask = 0, 1, 0) as should_be_evaluated
1739 from (
1740 select schedule_on, day_of_week_mask
1741 from buffer_schedules_965635012
1742 where should_be_evaluated = 1
1743 group by schedule_on, day_of_week_mask
1744 order by null
1745 ) as bs_1
1746 inner join specializations as sp
1747 on sp.client_id = 26
1748 and sp.active = 1
1749 inner join buffer_schedules_965635012 as bs_2
1750 on bs_1.schedule_on = bs_2.schedule_on
1751 and bs_2.call_type_id > 0 /* is not VC */
1752 inner join max_applicable_call_types as mact
1753 on sp.id = mact.specialization_id
1754 and bs_2.call_type_id = mact.call_type_id
1755 inner join buffer_staff_specializations_965635012 as st_sp
1756 on bs_2.staff_id = st_sp.staff_id
1757 and sp.id = st_sp.specialization_id
1758 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
1759 order by null
1760
1761INFO : (12-21 18:52:00) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query09_PR
1762 (154.3ms) truncate table buffer_location_privileges
1763
1764 (150.1ms) insert into buffer_location_privileges_965635012 (
1765 staff_id,
1766 location_id,
1767 begin_date,
1768 end_date
1769 )
1770 select
1771 s.id as staff_id,
1772 l.location_id,
1773 '0000-00-00' as begin_date,
1774 '9999-12-31' as end_date
1775 from (
1776 select l.id as location_id
1777 from
1778 buffer_schedules_965635012 as sc
1779 inner join call_types as ct
1780 on sc.call_type_id = ct.id
1781 and ct.active = 1
1782 inner join locations as l
1783 on ct.location_id = l.id
1784 and l.active = 1
1785 where sc.linkage_instance_id >= 0
1786 and sc.should_be_evaluated = 1
1787 group by l.id
1788 order by null
1789 ) as l
1790 inner join staffs as s
1791 on s.client_id = 26
1792 and s.active = 1
1793 and s.take_call = 1
1794 and s.auto_populatable = 1
1795 left join location_privileges as lp
1796 on s.id = lp.staff_id
1797 where lp.id is null
1798 union
1799 select
1800 lp.staff_id,
1801 lp.location_id,
1802 lp.begin_date,
1803 coalesce(lp.end_date, '9999-12-31') as end_date
1804 from (
1805 select l.id as location_id
1806 from
1807 buffer_schedules_965635012 as sc
1808 inner join call_types as ct
1809 on sc.call_type_id = ct.id
1810 and ct.active = 1
1811 inner join locations as l
1812 on ct.location_id = l.id
1813 and l.active = 1
1814 where sc.linkage_instance_id >= 0
1815 and sc.should_be_evaluated = 1
1816 group by l.id
1817 order by null
1818 ) as l
1819 inner join location_privileges as lp
1820 on l.location_id = lp.location_id
1821 order by null
1822
1823 (292.1ms) insert into buffer_static_availabilities_965635012 (
1824 schedule_id,
1825 staff_id,
1826 staff_type_id,
1827 is_primary
1828 )
1829 select straight_join
1830 schedules.id as schedule_id,
1831 staffs.id as staff_id,
1832 assign_staff_types.staff_type_id,
1833 if(schedules.day_of_week_mask & staff_call_types.days_of_week_mask & ifnull(rotation_call_types.days_of_week_mask, 127) > 0, 1, 0) as is_primary
1834 from
1835 (
1836 select
1837 linkage_instance_id,
1838 staff_id
1839 from buffer_prepopulate_staff_schedules_965635012
1840 group by
1841 linkage_instance_id,
1842 staff_id
1843 order by null
1844 ) as staff_schedules
1845 inner join buffer_schedules_965635012 as schedules
1846 on staff_schedules.linkage_instance_id = schedules.linkage_instance_id
1847 and schedules.should_be_evaluated = 1
1848 /* AvailabilityRule */
1849 inner join call_types
1850 on schedules.call_type_id = call_types.id
1851 and call_types.active = 1
1852 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
1853 inner join staff_call_types
1854 on staff_schedules.staff_id = staff_call_types.staff_id
1855 and call_types.id = staff_call_types.call_type_id
1856 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
1857 inner join staffs
1858 on staff_call_types.staff_id = staffs.id
1859 and staffs.active = 1
1860 and staffs.take_call = 1
1861 and staffs.auto_populatable = 1
1862 inner join buffer_location_privileges_965635012 as location_privileges
1863 on staffs.id = location_privileges.staff_id
1864 and call_types.location_id = location_privileges.location_id
1865 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
1866 inner join assign_staff_types
1867 on staffs.id = assign_staff_types.staff_id
1868 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
1869 left join eligible_staff_types
1870 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
1871 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
1872 left join assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1873 on staffs.id = assign_rotation_schedules.staff_id
1874 and schedules.schedule_on between assign_rotation_schedules.begin_date and assign_rotation_schedules.end_date
1875 left join staff_rotations force index (ix_staff_rotations_composite_1)
1876 on staffs.id = staff_rotations.staff_id
1877 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
1878 and assign_rotation_schedules.rotation_schedule_id = staff_rotations.rotation_schedule_id
1879 left join daily_rotations
1880 on staffs.id = daily_rotations.staff_id
1881 and schedules.schedule_on = daily_rotations.schedule_on
1882 and assign_rotation_schedules.rotation_schedule_id = daily_rotations.rotation_schedule_id
1883 and staff_rotations.id is not null
1884 left join rotations
1885 on coalesce(daily_rotations.rotation_id, staff_rotations.rotation_id) = rotations.id
1886 and rotations.active = 1
1887 left join rotation_call_types
1888 on rotations.id = rotation_call_types.rotation_id
1889 and staff_call_types.call_type_id = rotation_call_types.call_type_id
1890 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
1891 /* RequiredRotationsRule */
1892 left join required_rotations
1893 on staff_call_types.call_type_id = required_rotations.call_type_id
1894 left join staff_rotations as rr_staff_rotations force index (ix_staff_rotations_composite_3)
1895 on staffs.id = rr_staff_rotations.staff_id
1896 and required_rotations.rotation_id = rr_staff_rotations.rotation_id
1897 left join assign_rotation_schedules as rr_assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1898 on staffs.id = rr_assign_rotation_schedules.staff_id
1899 and rr_staff_rotations.start_date <= rr_assign_rotation_schedules.end_date
1900 and rr_staff_rotations.end_date >= rr_assign_rotation_schedules.begin_date
1901 and rr_staff_rotations.rotation_schedule_id = rr_assign_rotation_schedules.rotation_schedule_id
1902 /* NoCallRequestsHiddenHardRule */
1903 left join request_settings
1904 on assign_staff_types.staff_type_id = request_settings.staff_type_id
1905 left join requests
1906 on schedules.schedule_on = requests.schedule_on
1907 and staffs.id = requests.staff_id
1908 and requests.request_type = 0 /* type = no_call */
1909 and requests.status in (1, 3) /* status = approved or fulfilled */
1910 and call_types.is_no_call_requestable = 1
1911 left join request_call_types
1912 on requests.id = request_call_types.request_id
1913 and schedules.call_type_id = request_call_types.call_type_id
1914 where
1915 (
1916 call_types.all_staff_types = 1
1917 or eligible_staff_types.id is not null
1918 )
1919 and (
1920 rotations.id is null
1921 or rotation_call_types.id is not null
1922 )
1923 and (
1924 requests.id is null
1925 or (
1926 requests.all_call_types = 1
1927 and request_settings.call_only_all_call_switch = 1 /* calls only */
1928 and call_types.assignment_type = 1 /* clinical */
1929 )
1930 or (
1931 requests.all_call_types = 0
1932 and request_call_types.id is null
1933 )
1934 )
1935 group by
1936 schedules.id,
1937 staffs.id,
1938 assign_staff_types.staff_type_id,
1939 is_primary
1940 having
1941 count(distinct
1942 case
1943 when (
1944 rr_staff_rotations.id is not null
1945 and rr_assign_rotation_schedules.id is not null
1946 and schedules.schedule_on >= greatest(rr_staff_rotations.start_date, rr_assign_rotation_schedules.begin_date)
1947 )
1948 then rr_staff_rotations.rotation_id
1949 else null
1950 end
1951 ) >= count(distinct required_rotations.rotation_id) /* RequiredRotationsRule */
1952 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
1953 order by null
1954
1955INFO : (12-21 18:52:00) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query092
1956 (183.1ms) /* RotationPostCallRule */
1957 /*
1958 Exclude cases when people have a rotation starting on the next day after a call we're trying to make,
1959 if such rotation is configured appropriately ("Post-call on first day" = "Not permitted").
1960 */
1961 delete sa
1962 from buffer_static_availabilities_965635012 as sa
1963 inner join buffer_schedules_965635012 as sc
1964 on sa.schedule_id = sc.id
1965 inner join call_types as ct
1966 on sc.call_type_id = ct.id
1967 and ct.assignment_type = 0 /* call */
1968 inner join staff_rotations as sr
1969 on sa.staff_id = sr.staff_id
1970 and date_add(sc.schedule_on, interval 1 day) between sr.start_date and sr.end_date
1971 inner join assign_rotation_schedules as ars
1972 on sa.staff_id = ars.staff_id
1973 and date_add(sc.schedule_on, interval 1 day) between ars.begin_date and ars.end_date
1974 and sr.rotation_schedule_id = ars.rotation_schedule_id
1975 inner join rotations as r
1976 on sr.rotation_id = r.id
1977 and r.post_call_allowed = 0
1978 left join assign_rotation_schedules as ars_2
1979 on sa.staff_id = ars_2.staff_id
1980 and sr.start_date <= ars_2.end_date
1981 and sr.end_date >= ars_2.begin_date
1982 and sr.rotation_schedule_id = ars_2.rotation_schedule_id
1983 -- Earlier intersection
1984 and ars.begin_date > ars_2.begin_date
1985 where
1986 date_add(sc.schedule_on, interval 1 day) = greatest(sr.start_date, ars.begin_date)
1987 and ars_2.id is null;
1988
1989INFO : (12-21 18:52:00) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query093
1990INFO : (12-21 18:52:00) --> fill: 34 - Scheduler::AutoPopulate::Queries::Query095
1991 (151.8ms) insert into buffer_negative_relations_965635012 (
1992 schedule_id,
1993 affected_by_schedule_id,
1994 staff_id
1995 )
1996 select
1997 b_schedules.id as schedule_id,
1998 b_schedules_negative_link.id as affected_by_schedule_id,
1999 b_schedules_negative_link.staff_id
2000 from
2001 buffer_schedules_965635012 as b_schedules
2002 /* NegativeLinkRule */
2003 inner join negative_links_influences
2004 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2005 and b_schedules.call_type_id = negative_links_influences.source_id
2006 and negative_links_influences.source_type = 'CallType'
2007 and negative_links_influences.target_type = 'CallType'
2008 inner join buffer_schedules_965635012 as b_schedules_negative_link
2009 on date_add(
2010 b_schedules.schedule_on,
2011 interval negative_links_influences.offset day
2012 ) = b_schedules_negative_link.schedule_on
2013 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
2014 and b_schedules.id != b_schedules_negative_link.id
2015 where
2016 b_schedules.linkage_instance_id >= 0
2017 and b_schedules.should_be_evaluated = 1
2018 order by null
2019
2020 (153.6ms) insert into buffer_negative_relations_965635012 (
2021 schedule_id,
2022 affected_by_schedule_id,
2023 staff_id
2024 )
2025 select
2026 b_schedules.id as schedule_id,
2027 b_schedules_negative_link.id as affected_by_schedule_id,
2028 b_schedules_negative_link.staff_id
2029 from
2030 buffer_schedules_965635012 as b_schedules
2031 /* NegativeLinkRule */
2032 inner join negative_links_influences
2033 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2034 and b_schedules.call_type_id = negative_links_influences.source_id
2035 and negative_links_influences.source_type = 'CallType'
2036 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
2037 inner join buffer_schedules_965635012 as b_schedules_negative_link
2038 on date_add(
2039 b_schedules.schedule_on,
2040 interval negative_links_influences.offset day
2041 ) = b_schedules_negative_link.schedule_on
2042 and (-1 * negative_links_influences.target_id) = b_schedules_negative_link.call_type_id
2043 and b_schedules.id != b_schedules_negative_link.id
2044 where
2045 b_schedules.linkage_instance_id >= 0
2046 and b_schedules.should_be_evaluated = 1
2047 order by null
2048
2049 (148.1ms) delete from buffer_schedules_965635012
2050 where call_type_id < 0 /* vacations and non-clinicals */
2051
2052INFO : (12-21 18:52:01) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query097
2053 (166.3ms) insert into buffer_staff_assigned_days_965635012 (
2054 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
2055 )
2056 select staffs.staff_id,
2057 if(days.within_period = 1, days.period_offset, null) as period_offset,
2058 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
2059 days.schedule_on,
2060 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
2061 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
2062 from (
2063 select distinct staff_id
2064 from buffer_static_availabilities_965635012
2065 order by null
2066 ) as staffs
2067 inner join (
2068 select p.period_offset,
2069 date_add(date_add(p.start_date, interval -6 day), interval (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) day) as schedule_on,
2070 if((digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) - 6 between 0 and datediff(p.end_date, p.start_date), 1, 0) as within_period
2071 from buffer_periods_965635012 as p
2072 inner join (
2073 select 0 as digit union select 1 union select 2 union select 3 union select 4
2074 union select 5 union select 6 union select 7 union select 8 union select 9
2075 ) as digits_1
2076 inner join (
2077 select 0 as digit union select 1 union select 2 union select 3 union select 4
2078 union select 5 union select 6 union select 7 union select 8 union select 9
2079 ) as digits_2
2080 inner join (
2081 select 0 as digit union select 1 union select 2 union select 3 union select 4
2082 union select 5 union select 6 union select 7 union select 8 union select 9
2083 ) as digits_3
2084 where p.calendar_schedule_id = 26
2085 and p.should_be_evaluated = 1
2086 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
2087 order by null
2088 ) as days
2089 inner join (
2090 select min(start_date) as start_date, max(end_date) as end_date
2091 from buffer_periods_965635012
2092 where calendar_schedule_id = 26
2093 and should_be_evaluated = 1
2094 order by null
2095 ) as limits
2096 inner join clients
2097 on clients.id = 26
2098 left join (
2099 select sc.staff_id, sc.schedule_on
2100 from buffer_periods_965635012 as p
2101 inner join schedules as sc
2102 on sc.client_id = 26
2103 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2104 inner join call_types as ct
2105 on sc.call_type_id = ct.id
2106 and ct.unassigned_day = 0
2107 where p.calendar_schedule_id = 26
2108 and p.should_be_evaluated = 1
2109 group by sc.staff_id, sc.schedule_on
2110 order by null
2111 ) as schedules
2112 on staffs.staff_id = schedules.staff_id
2113 and days.schedule_on = schedules.schedule_on
2114 left join (
2115 select v.staff_id, v.schedule_on
2116 from buffer_periods_965635012 as p
2117 inner join vacations as v
2118 on v.client_id = 26
2119 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2120 and v.status IN (3)
2121 inner join non_clinical_types as nct
2122 on v.non_clinical_type_id = nct.id
2123 and nct.active = 1
2124 and nct.unassigned_day = 0
2125 where p.calendar_schedule_id = 26
2126 and p.should_be_evaluated = 1
2127 group by v.staff_id, v.schedule_on
2128 order by null
2129 ) as vacations
2130 on staffs.staff_id = vacations.staff_id
2131 and days.schedule_on = vacations.schedule_on
2132 where days.within_period = 1
2133 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
2134 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
2135 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
2136 )
2137 order by null
2138
2139INFO : (12-21 18:52:01) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query098
2140 (148.8ms) insert into buffer_combined_availabilities_965635012 (
2141 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
2142 )
2143 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
2144 sc.linkage_instance_id, 0 as has_violations
2145 from buffer_schedules_965635012 as sc
2146 /* Static availability */
2147 inner join buffer_static_availabilities_965635012 as sa
2148 on sc.id = sa.schedule_id
2149 inner join buffer_linkages_965635012 as l
2150 on sc.linkage_instance_id = l.linkage_instance_id
2151 where sc.should_be_evaluated = 1
2152 and l.has_conflicts = 0
2153
2154 (145.9ms) insert into buffer_schedule_linkages_965635012 (
2155 staff_id, linkage_instance_id, slots_count
2156 )
2157 select staff_id, linkage_instance_id, count(1) as slots_count
2158 from buffer_schedules_965635012 use index (ix_buffer_schedules_composite_2)
2159 where staff_id is not null
2160 and linkage_instance_id >= 0
2161 group by staff_id, linkage_instance_id
2162 order by null
2163
2164INFO : (12-21 18:52:01) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL
2165 (146.7ms) insert into buffer_assignment_limits_965635012 (
2166 id,
2167 name,
2168 calendar_schedule_id,
2169 considered_days_of_week_mask,
2170 limit_type,
2171 limit_subtypes_mask,
2172 timeframe_type,
2173 max_per_period_limit,
2174 max_weekly_limit,
2175 max_rolling_period,
2176 max_rolling_limit,
2177 max_consecutive_limit
2178 )
2179 select
2180 id,
2181 name,
2182 calendar_schedule_id,
2183 considered_days_of_week_mask,
2184 limit_type,
2185 (
2186 (coalesce(max_per_period_limit, -1) >= 0) * 1 +
2187 (coalesce(max_weekly_limit, -1) >= 0) * 2 +
2188 (
2189 coalesce(max_rolling_period, -1) > 0
2190 and coalesce(max_rolling_limit, -1) >= 0
2191 ) * 4 +
2192 (coalesce(max_consecutive_limit, -1) > 0) * 8
2193 ) as limit_subtypes_mask,
2194 timeframe_type,
2195 max_per_period_limit,
2196 max_weekly_limit,
2197 max_rolling_period,
2198 max_rolling_limit,
2199 max_consecutive_limit
2200 from assignment_limits
2201 where
2202 client_id = 26
2203 and active = 1
2204 and (
2205 max_per_period_limit >= 0
2206 or max_weekly_limit >= 0
2207 or (
2208 max_rolling_period > 0
2209 and max_rolling_limit >= 0
2210 )
2211 or max_consecutive_limit > 0
2212 )
2213 order by null
2214
2215 (170.2ms) select id from buffer_assignment_limits_965635012 limit 1
2216INFO : (12-21 18:52:02) --> fill: 38 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
2217 (147.8ms) select
2218 coalesce(bit_or(limit_subtypes_mask), 0)
2219 from buffer_assignment_limits_965635012
2220
2221INFO : (12-21 18:52:02) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
2222INFO : (12-21 18:52:02) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
2223INFO : (12-21 18:52:02) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
2224INFO : (12-21 18:52:02) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
2225INFO : (12-21 18:52:02) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
2226INFO : (12-21 18:52:02) --> fill: 44 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
2227INFO : (12-21 18:52:02) --> fill: 45 - Scheduler::AutoPopulate::Queries::Query09_12_Points
2228INFO : (12-21 18:52:02) --> fill: 46 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
2229INFO : (12-21 18:52:02) --> fill: 47 - Scheduler::AutoPopulate::Queries::Query10
2230 RuleSet Load (146.6ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 701 LIMIT 1
2231 Client Load (147.9ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 26 LIMIT 1
2232 (146.4ms) select
2233 coalesce(max(
2234 case
2235 when (ct.max_weekday_assignments > 0) then 1
2236 else 0
2237 end
2238 ), 0) as weekday_flag,
2239 coalesce(max(
2240 case
2241 when (ct.max_weekend_assignments > 0) then 1
2242 else 0
2243 end
2244 ), 0) as weekend_flag
2245 from
2246 buffer_auto_populate_slots_965635012 as aps
2247 inner join call_types as ct
2248 on aps.call_type_id = ct.id
2249
2250 (146.0ms) /* NegativeLinkRule */
2251 delete ca
2252 from buffer_combined_availabilities_965635012 as ca use index (ix_buffer_combined_availabilities_composite_1)
2253 inner join buffer_negative_relations_965635012 as nr use index (ix_buffer_negative_relations_composite_1)
2254 on ca.schedule_id = nr.schedule_id
2255 and ca.staff_id = nr.staff_id
2256
2257 (146.9ms) /* SpecializationOverlapRule */
2258 delete ca
2259 from buffer_combined_availabilities_965635012 as ca use index (ix_buffer_combined_availabilities_composite_1)
2260 inner join buffer_schedules_965635012 as sc
2261 on ca.schedule_id = sc.id
2262 inner join buffer_staff_specializations_965635012 as ssp
2263 on ca.staff_id = ssp.staff_id
2264 inner join max_applicable_call_types as mact
2265 on ssp.specialization_id = mact.specialization_id
2266 and sc.call_type_id = mact.call_type_id
2267 inner join buffer_specialization_daily_counts_965635012 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2268 on ssp.specialization_id = sdc.specialization_id
2269 and sc.schedule_on = sdc.schedule_on
2270 and sdc.staff_id is null
2271 left join buffer_specialization_daily_counts_965635012 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2272 on ssp.specialization_id = sdc_2.specialization_id
2273 and sc.schedule_on = sdc_2.schedule_on
2274 and ca.staff_id = sdc_2.staff_id
2275 where sdc.should_be_evaluated = 1
2276 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2277
2278 (148.1ms) /* BrokenLinkageRule */
2279 truncate table buffer_combined_availability_linkages_965635012
2280
2281 (149.8ms) insert into buffer_combined_availability_linkages_965635012 (
2282 staff_id,
2283 linkage_instance_id,
2284 slots_count,
2285 is_primary,
2286 has_violations
2287 )
2288 select
2289 staff_id,
2290 linkage_instance_id,
2291 count(1) as slots_count,
2292 min(is_primary) as is_primary,
2293 max(has_violations) as has_violations
2294 from buffer_combined_availabilities_965635012 use index (ix_buffer_combined_availabilities_composite_2)
2295 group by
2296 staff_id,
2297 linkage_instance_id
2298 order by null
2299
2300 (148.4ms) delete ca
2301 from
2302 buffer_combined_availabilities_965635012 as ca use index (ix_buffer_combined_availabilities_composite_1)
2303 inner join buffer_schedules_965635012 as sc
2304 on ca.schedule_id = sc.id
2305 inner join buffer_linkages_965635012 as l
2306 on sc.linkage_instance_id = l.linkage_instance_id
2307 inner join buffer_combined_availability_linkages_965635012 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2308 on ca.staff_id = ls_1.staff_id
2309 and sc.linkage_instance_id = ls_1.linkage_instance_id
2310 left join buffer_schedule_linkages_965635012 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2311 on ca.staff_id = ls_2.staff_id
2312 and sc.linkage_instance_id = ls_2.linkage_instance_id
2313 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2314
2315INFO : (12-21 18:52:03) --> fill: 48 - Scheduler::AutoPopulate::Queries::QueryScore02
2316 (157.6ms) insert into buffer_min_distances_965635012 (
2317 schedule_id, staff_id, rule_name, min_distance_in_days
2318 )
2319 select b_schedules.id as schedule_id, availabilities.staff_id, 'CallMixRule' as rule_name,
2320 min(abs(datediff(b_schedules.schedule_on, schedules.schedule_on))) as min_distance_in_days
2321 from buffer_schedules_965635012 as b_schedules
2322 inner join buffer_combined_availabilities_965635012 as availabilities
2323 on b_schedules.id = availabilities.schedule_id
2324 inner join schedules
2325 on availabilities.staff_id = schedules.staff_id
2326 and b_schedules.call_type_id = schedules.call_type_id
2327 left join buffer_schedules_965635012 as b_schedules_2
2328 on schedules.schedule_on = b_schedules_2.schedule_on
2329 and schedules.call_type_id = b_schedules_2.call_type_id
2330 and b_schedules.linkage_instance_id = b_schedules_2.linkage_instance_id
2331 where b_schedules_2.id is null
2332 group by b_schedules.id, availabilities.staff_id, rule_name
2333 order by null
2334
2335INFO : (12-21 18:52:03) --> fill: 49 - Scheduler::AutoPopulate::Queries::QueryScore03
2336 (146.5ms) insert into buffer_rule_calc_schedules_965635012 (
2337 call_type_id, staff_id, schedule_on, day_of_week_mask
2338 )
2339 select schedules.call_type_id, schedules.staff_id,
2340 schedules.schedule_on, (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask
2341 from buffer_min_distances_965635012 as min_distances
2342 inner join buffer_schedules_965635012 as b_schedules
2343 on min_distances.schedule_id = b_schedules.id
2344 inner join schedules
2345 on min_distances.staff_id = schedules.staff_id
2346 and b_schedules.call_type_id = schedules.call_type_id
2347 and date_add(b_schedules.schedule_on, interval min_distances.min_distance_in_days day) = schedules.schedule_on
2348 where min_distances.rule_name in ('CallMixRule')
2349 group by schedules.call_type_id, schedules.staff_id,
2350 schedules.schedule_on, (1 << (dayofweek(schedules.schedule_on) - 1))
2351 order by null
2352
2353 (149.3ms) insert into buffer_rule_calc_schedules_965635012 (
2354 call_type_id, staff_id, schedule_on, day_of_week_mask
2355 )
2356 select schedules.call_type_id, schedules.staff_id,
2357 schedules.schedule_on, (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask
2358 from buffer_min_distances_965635012 as min_distances
2359 inner join buffer_schedules_965635012 as b_schedules
2360 on min_distances.schedule_id = b_schedules.id
2361 inner join schedules
2362 on min_distances.staff_id = schedules.staff_id
2363 and b_schedules.call_type_id = schedules.call_type_id
2364 and date_add(b_schedules.schedule_on, interval -min_distances.min_distance_in_days day) = schedules.schedule_on
2365 where min_distances.rule_name in ('CallMixRule')
2366 group by schedules.call_type_id, schedules.staff_id,
2367 schedules.schedule_on, (1 << (dayofweek(schedules.schedule_on) - 1))
2368 order by null
2369
2370INFO : (12-21 18:52:03) --> fill: 50 - Scheduler::AutoPopulate::Queries::QueryScore04
2371INFO : (12-21 18:52:03) --> fill: 51 - Scheduler::AutoPopulate::Queries::Query11
2372 (145.9ms) update buffer_schedules_965635012 as sc
2373 left join buffer_combined_availabilities_965635012 as ca
2374 on sc.id = ca.schedule_id
2375 set sc.should_be_evaluated = 0,
2376 sc.should_be_populated = 0
2377 where sc.should_be_evaluated = 1
2378 and ca.id is null
2379
2380INFO : (12-21 18:52:04) --> fill: 52 - Scheduler::AutoPopulate::Queries::DropTables
2381INFO : (12-21 18:52:04) Setting up lists...
2382 (147.4ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
2383 from buffer_schedules_965635012 as b_schedules
2384 left join schedules
2385 on schedules.client_id = 26
2386 and schedules.call_type_id = b_schedules.call_type_id
2387 and schedules.schedule_on = b_schedules.schedule_on
2388 where b_schedules.linkage_instance_id != -1
2389 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
2390
2391INFO : (12-21 18:52:04) Begin to populate. Pre-set: 0. Unscheduled: 65.
2392INFO : (12-21 18:52:04) plan unit: [id:3201753] [2018-01-01 - 2nd Call]
2393INFO : (12-21 18:52:04) plan unit: [id:3201801] [2018-01-08 - 2nd Call]
2394INFO : (12-21 18:52:04) plan unit: [id:3201802] [2018-01-15 - 2nd Call]
2395INFO : (12-21 18:52:04) plan unit: [id:3201803] [2018-01-22 - 2nd Call]
2396INFO : (12-21 18:52:04) plan unit: [id:3201804] [2018-01-29 - 2nd Call]
2397INFO : (12-21 18:52:04) plan unit: [id:3201805] [2018-02-05 - 2nd Call]
2398INFO : (12-21 18:52:04) plan unit: [id:3201806] [2018-02-12 - 2nd Call]
2399INFO : (12-21 18:52:04) plan unit: [id:3201807] [2018-02-19 - 2nd Call]
2400INFO : (12-21 18:52:04) plan unit: [id:3201808] [2018-02-26 - 2nd Call]
2401INFO : (12-21 18:52:04) plan unit: [id:3201809] [2018-03-05 - 2nd Call]
2402INFO : (12-21 18:52:04) plan unit: [id:3201810] [2018-03-12 - 2nd Call]
2403INFO : (12-21 18:52:04) plan unit: [id:3201811] [2018-03-19 - 2nd Call]
2404INFO : (12-21 18:52:04) plan unit: [id:3201812] [2018-03-26 - 2nd Call]
2405INFO : (12-21 18:52:04) plan unit: [id:9201753] [2018-01-02 - 2nd Call]
2406INFO : (12-21 18:52:04) plan unit: [id:9201801] [2018-01-09 - 2nd Call]
2407INFO : (12-21 18:52:04) plan unit: [id:9201802] [2018-01-16 - 2nd Call]
2408INFO : (12-21 18:52:04) plan unit: [id:9201803] [2018-01-23 - 2nd Call]
2409INFO : (12-21 18:52:04) plan unit: [id:9201804] [2018-01-30 - 2nd Call]
2410INFO : (12-21 18:52:04) plan unit: [id:9201805] [2018-02-06 - 2nd Call]
2411INFO : (12-21 18:52:04) plan unit: [id:9201806] [2018-02-13 - 2nd Call]
2412INFO : (12-21 18:52:04) plan unit: [id:9201807] [2018-02-20 - 2nd Call]
2413INFO : (12-21 18:52:04) plan unit: [id:9201808] [2018-02-27 - 2nd Call]
2414INFO : (12-21 18:52:04) plan unit: [id:9201809] [2018-03-06 - 2nd Call]
2415INFO : (12-21 18:52:04) plan unit: [id:9201810] [2018-03-13 - 2nd Call]
2416INFO : (12-21 18:52:04) plan unit: [id:9201811] [2018-03-20 - 2nd Call]
2417INFO : (12-21 18:52:04) plan unit: [id:9201812] [2018-03-27 - 2nd Call]
2418INFO : (12-21 18:52:04) plan unit: [id:13201753] [2018-01-03 - 2nd Call]
2419INFO : (12-21 18:52:04) plan unit: [id:13201801] [2018-01-10 - 2nd Call]
2420INFO : (12-21 18:52:04) plan unit: [id:13201802] [2018-01-17 - 2nd Call]
2421INFO : (12-21 18:52:04) plan unit: [id:13201803] [2018-01-24 - 2nd Call]
2422INFO : (12-21 18:52:04) plan unit: [id:13201804] [2018-01-31 - 2nd Call]
2423INFO : (12-21 18:52:04) plan unit: [id:13201805] [2018-02-07 - 2nd Call]
2424INFO : (12-21 18:52:04) plan unit: [id:13201806] [2018-02-14 - 2nd Call]
2425INFO : (12-21 18:52:04) plan unit: [id:13201807] [2018-02-21 - 2nd Call]
2426INFO : (12-21 18:52:04) plan unit: [id:13201808] [2018-02-28 - 2nd Call]
2427INFO : (12-21 18:52:04) plan unit: [id:13201809] [2018-03-07 - 2nd Call]
2428INFO : (12-21 18:52:04) plan unit: [id:13201810] [2018-03-14 - 2nd Call]
2429INFO : (12-21 18:52:04) plan unit: [id:13201811] [2018-03-21 - 2nd Call]
2430INFO : (12-21 18:52:04) plan unit: [id:13201812] [2018-03-28 - 2nd Call]
2431INFO : (12-21 18:52:04) plan unit: [id:19201753] [2018-01-04 - 2nd Call]
2432INFO : (12-21 18:52:04) plan unit: [id:19201801] [2018-01-11 - 2nd Call]
2433INFO : (12-21 18:52:04) plan unit: [id:19201802] [2018-01-18 - 2nd Call]
2434INFO : (12-21 18:52:04) plan unit: [id:19201803] [2018-01-25 - 2nd Call]
2435INFO : (12-21 18:52:04) plan unit: [id:19201804] [2018-02-01 - 2nd Call]
2436INFO : (12-21 18:52:04) plan unit: [id:19201805] [2018-02-08 - 2nd Call]
2437INFO : (12-21 18:52:04) plan unit: [id:19201806] [2018-02-15 - 2nd Call]
2438INFO : (12-21 18:52:04) plan unit: [id:19201807] [2018-02-22 - 2nd Call]
2439INFO : (12-21 18:52:04) plan unit: [id:19201808] [2018-03-01 - 2nd Call]
2440INFO : (12-21 18:52:04) plan unit: [id:19201809] [2018-03-08 - 2nd Call]
2441INFO : (12-21 18:52:04) plan unit: [id:19201810] [2018-03-15 - 2nd Call]
2442INFO : (12-21 18:52:04) plan unit: [id:19201811] [2018-03-22 - 2nd Call]
2443INFO : (12-21 18:52:04) plan unit: [id:19201812] [2018-03-29 - 2nd Call]
2444INFO : (12-21 18:52:04) plan unit: [id:24201753] [2018-01-05 - 2nd Call]
2445INFO : (12-21 18:52:04) plan unit: [id:24201801] [2018-01-12 - 2nd Call]
2446INFO : (12-21 18:52:04) plan unit: [id:24201802] [2018-01-19 - 2nd Call]
2447INFO : (12-21 18:52:04) plan unit: [id:24201803] [2018-01-26 - 2nd Call]
2448INFO : (12-21 18:52:04) plan unit: [id:24201804] [2018-02-02 - 2nd Call]
2449INFO : (12-21 18:52:04) plan unit: [id:24201805] [2018-02-09 - 2nd Call]
2450INFO : (12-21 18:52:04) plan unit: [id:24201806] [2018-02-16 - 2nd Call]
2451INFO : (12-21 18:52:04) plan unit: [id:24201807] [2018-02-23 - 2nd Call]
2452INFO : (12-21 18:52:04) plan unit: [id:24201808] [2018-03-02 - 2nd Call]
2453INFO : (12-21 18:52:04) plan unit: [id:24201809] [2018-03-09 - 2nd Call]
2454INFO : (12-21 18:52:04) plan unit: [id:24201810] [2018-03-16 - 2nd Call]
2455INFO : (12-21 18:52:04) plan unit: [id:24201811] [2018-03-23 - 2nd Call]
2456INFO : (12-21 18:52:04) plan unit: [id:24201812] [2018-03-30 - 2nd Call]
2457 (147.5ms) select
2458 coalesce(max(
2459 case
2460 when (ct.max_weekday_assignments > 0) then 1
2461 else 0
2462 end
2463 ), 0) as weekday_flag,
2464 coalesce(max(
2465 case
2466 when (ct.max_weekend_assignments > 0) then 1
2467 else 0
2468 end
2469 ), 0) as weekend_flag
2470 from
2471 buffer_auto_populate_slots_965635012 as aps
2472 inner join call_types as ct
2473 on aps.call_type_id = ct.id
2474
2475 (146.2ms) /* NegativeLinkRule */
2476 delete ca
2477 from buffer_combined_availabilities_965635012 as ca use index (ix_buffer_combined_availabilities_composite_1)
2478 inner join buffer_negative_relations_965635012 as nr use index (ix_buffer_negative_relations_composite_1)
2479 on ca.schedule_id = nr.schedule_id
2480 and ca.staff_id = nr.staff_id
2481
2482 (148.0ms) /* SpecializationOverlapRule */
2483 delete ca
2484 from buffer_combined_availabilities_965635012 as ca use index (ix_buffer_combined_availabilities_composite_1)
2485 inner join buffer_schedules_965635012 as sc
2486 on ca.schedule_id = sc.id
2487 inner join buffer_staff_specializations_965635012 as ssp
2488 on ca.staff_id = ssp.staff_id
2489 inner join max_applicable_call_types as mact
2490 on ssp.specialization_id = mact.specialization_id
2491 and sc.call_type_id = mact.call_type_id
2492 inner join buffer_specialization_daily_counts_965635012 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2493 on ssp.specialization_id = sdc.specialization_id
2494 and sc.schedule_on = sdc.schedule_on
2495 and sdc.staff_id is null
2496 left join buffer_specialization_daily_counts_965635012 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2497 on ssp.specialization_id = sdc_2.specialization_id
2498 and sc.schedule_on = sdc_2.schedule_on
2499 and ca.staff_id = sdc_2.staff_id
2500 where sdc.should_be_evaluated = 1
2501 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2502
2503 (148.0ms) /* BrokenLinkageRule */
2504 truncate table buffer_combined_availability_linkages_965635012
2505
2506 (148.3ms) insert into buffer_combined_availability_linkages_965635012 (
2507 staff_id,
2508 linkage_instance_id,
2509 slots_count,
2510 is_primary,
2511 has_violations
2512 )
2513 select
2514 staff_id,
2515 linkage_instance_id,
2516 count(1) as slots_count,
2517 min(is_primary) as is_primary,
2518 max(has_violations) as has_violations
2519 from buffer_combined_availabilities_965635012 use index (ix_buffer_combined_availabilities_composite_2)
2520 group by
2521 staff_id,
2522 linkage_instance_id
2523 order by null
2524
2525 (148.5ms) delete ca
2526 from
2527 buffer_combined_availabilities_965635012 as ca use index (ix_buffer_combined_availabilities_composite_1)
2528 inner join buffer_schedules_965635012 as sc
2529 on ca.schedule_id = sc.id
2530 inner join buffer_linkages_965635012 as l
2531 on sc.linkage_instance_id = l.linkage_instance_id
2532 inner join buffer_combined_availability_linkages_965635012 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2533 on ca.staff_id = ls_1.staff_id
2534 and sc.linkage_instance_id = ls_1.linkage_instance_id
2535 left join buffer_schedule_linkages_965635012 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2536 on ca.staff_id = ls_2.staff_id
2537 and sc.linkage_instance_id = ls_2.linkage_instance_id
2538 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2539
2540 (147.3ms) select
2541 pp.id as period_id,
2542 ca.staff_id,
2543 group_concat(
2544 distinct ca.linkage_instance_id
2545 order by ca.linkage_instance_id
2546 separator ','
2547 ) as linkage_instance_ids
2548 from
2549 buffer_prepopulate_periods_965635012 as pp
2550 inner join buffer_prepopulate_assignments_965635012 as pa
2551 on pp.id = pa.period_id
2552 and pa.required_assignments > pa.scheduled_assignments
2553 inner join buffer_prepopulate_staff_schedules_965635012 as pss
2554 on pp.id = pss.period_id
2555 inner join buffer_combined_availabilities_965635012 as ca
2556 on pss.schedule_id = ca.schedule_id
2557 and pss.staff_id = ca.staff_id
2558 group by
2559 pp.id,
2560 ca.staff_id
2561 order by pp.id
2562 limit 1
2563
2564 (147.6ms) truncate table buffer_rule_scores_965635012
2565
2566 (147.5ms) truncate table buffer_rule_calc_filters_965635012
2567
2568 (147.7ms) insert into buffer_rule_calc_filters_965635012 (
2569 linkage_instance_id,
2570 staff_id
2571 ) values (3201753, 12736)
2572
2573 (148.7ms) insert into buffer_rule_calc_filters_965635012 (
2574 linkage_instance_id,
2575 staff_id
2576 ) values (9201753, 12736)
2577
2578 (150.6ms) insert into buffer_rule_calc_filters_965635012 (
2579 linkage_instance_id,
2580 staff_id
2581 ) values (13201753, 12736)
2582
2583 (147.7ms) insert into buffer_rule_calc_filters_965635012 (
2584 linkage_instance_id,
2585 staff_id
2586 ) values (19201753, 12736)
2587
2588 (147.7ms) insert into buffer_rule_calc_filters_965635012 (
2589 linkage_instance_id,
2590 staff_id
2591 ) values (24201753, 12736)
2592
2593 (148.6ms) /* CallMixRule */
2594 insert into buffer_rule_scores_965635012 (
2595 linkage_instance_id,
2596 schedule_id,
2597 staff_id,
2598 rule_name,
2599 value
2600 )
2601 select
2602 b_schedules.linkage_instance_id,
2603 b_schedules.id as schedule_id,
2604 availabilities.staff_id,
2605 'CallMixRule' as _rule_name,
2606 min(abs(datediff(b_schedules.schedule_on, rc_schedules.schedule_on))) as value
2607 from
2608 buffer_rule_calc_filters_965635012 as filters
2609 inner join buffer_schedules_965635012 as b_schedules
2610 on filters.linkage_instance_id = b_schedules.linkage_instance_id
2611 inner join buffer_combined_availabilities_965635012 as availabilities
2612 on b_schedules.id = availabilities.schedule_id
2613 and filters.staff_id = availabilities.staff_id
2614 left join buffer_rule_calc_schedules_965635012 as rc_schedules
2615 on availabilities.staff_id = rc_schedules.staff_id
2616 and b_schedules.call_type_id = rc_schedules.call_type_id
2617 group by
2618 b_schedules.linkage_instance_id,
2619 b_schedules.id,
2620 availabilities.staff_id,
2621 _rule_name
2622 order by null
2623
2624 (147.7ms) truncate table buffer_rule_scores_with_corrections_965635012
2625
2626 (148.6ms) insert into buffer_rule_scores_with_corrections_965635012 (
2627 linkage_instance_id, schedule_id, staff_id, rule_name, value
2628 )
2629 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2630 ifnull(rule_scores.value, max_values.max_value + 1) as value
2631 from buffer_rule_scores_965635012 as rule_scores
2632 inner join (
2633 select rule_name, max(value) as max_value
2634 from buffer_rule_scores_965635012
2635 group by rule_name
2636 order by null
2637 ) as max_values
2638 on rule_scores.rule_name = max_values.rule_name
2639 order by null
2640
2641 (147.5ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2642 from (
2643 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2644 sum(
2645 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2646 rules.weight *
2647 total_values.slots_count
2648 ) as slot_scores
2649 from buffer_rule_scores_with_corrections_965635012 as rule_scores
2650 inner join (
2651 select rule_name, sum(abs(value)) as total_value,
2652 count(distinct schedule_id) as slots_count
2653 from buffer_rule_scores_with_corrections_965635012
2654 group by rule_name
2655 order by null
2656 ) as total_values
2657 on rule_scores.rule_name = total_values.rule_name
2658 inner join rules
2659 on rule_scores.rule_name = rules.type
2660 where rules.rule_set_id = 701
2661 and rules.applied = 1
2662 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2663 order by null
2664 ) as staff_scores
2665 group by linkage_instance_id, staff_id
2666 order by round(avg(slot_scores), 5) desc, rand()
2667 limit 1