· 8 years ago · May 27, 2018, 10:24 PM
1(142.7ms) SET NAMES utf8 COLLATE utf8_unicode_ci, @@SESSION.sql_mode = 'NO_ENGINE_SUBSTITUTION', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2 Client Load (149.4ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
3 CalendarSchedule Load (142.7ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 6 AND `calendar_schedules`.`id` = 6 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
4 CallType Load (146.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (46, 47, 48, 49, 51)
5 CalendarSchedule Load (146.8ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 6 AND `calendar_schedules`.`id` = 6 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
6 Block Load (157.2ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND `blocks`.`id` = 17087 ORDER BY start_date asc, end_date asc LIMIT 1
7 BlockSchedule Load (141.3ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
8 PopulationTimeframe Load (141.2ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 6 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
9 RuleSet Load (141.7ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1530 LIMIT 1
10 Block Load (141.8ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date >= '2017-10-01' AND blocks.end_date <= '2017-10-31') ORDER BY start_date asc, end_date asc
11INFO : (05-23 07:15:48) phase: Scheduler::Base
12INFO : (05-23 07:15:48) from: 2017-10-01 to: 2017-10-31
13 Rule Load (149.9ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1530 ORDER BY applied desc, weight desc
14INFO : (05-23 07:15:49) rule configurations: TARGET FIXES TESTING 2
15 - 'Assignments Per Day' applied {"opt1"=>{"applied"=>"1", "value"=>"1"}} weight:5
16 - 'Cumulative Assignment Targets' not applied
17 - 'Cumulative Combined Assignment Targets' not applied
18 - 'Cumulative Daily Assignment Targets' not applied
19 - 'Cumulative Day of Week Targets' not applied
20 - 'Cumulative Hour Targets' not applied
21 - 'Cumulative Point Targets' not applied
22 - 'Cumulative Weekend / Weekday Assignment Targets' not applied
23 - 'Cumulative Weekend / Weekday Combined Assignment Targets' not applied
24 - 'Cumulative Weekend / Weekday Hour Targets' not applied
25 - 'Cumulative Weekend / Weekday Point Targets' not applied
26 - 'Current Assignment Targets' not applied
27 - 'Current Combined Assignment Targets' not applied
28 - 'Current Daily Assignment Targets' not applied
29 - 'Current Day of Week Targets' not applied
30 - 'Current Hour Targets' not applied
31 - 'Current Point Targets' not applied
32 - 'Current Weekend / Weekday Assignment Targets' not applied
33 - 'Current Weekend / Weekday Combined Assignment Targets' not applied
34 - 'Current Weekend / Weekday Hour Targets' not applied
35 - 'Current Weekend / Weekday Point Targets' not applied
36 - 'Pending Call Requests' not applied
37 - 'Pending No-call Requests' not applied
38 - 'Days Between Assignments' not applied
39 - 'Days Between Same Assignment' not applied
40 - 'Unassigned Days' not applied
41 - 'Weeks Between Same Assignment On Same Day Of Week' not applied
42 - 'Weeks Between Same Day Of Week' not applied
43INFO : (05-23 07:15:49) started: Scheduler::Cache::Collector.collect_all
44INFO : (05-23 07:15:49) collect_staffs
45 Staff Load (148.8ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `assign_staff_types` ON `assign_staff_types`.`staff_id` = `staffs`.`id` INNER JOIN `staff_types` ON `staff_types`.`id` = `assign_staff_types`.`staff_type_id` WHERE `staffs`.`client_id` = 6 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
46 Client Load (170.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6
47 StaffSpecialization Load (142.3ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 18127, 78, 8510)
48 Specialization Load (144.8ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` = 7 ORDER BY `specializations`.`priority` ASC
49 StaffCallType Load (316.4ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 18127, 78, 8510)
50INFO : (05-23 07:15:52) initial eligibles: [66,77,68,74,72,3351,79,80,6913,81,65,18125,70,6960,71,73,20694,75,18127,78,8510]
51INFO : (05-23 07:15:52) collect_call_types
52 CallType Load (170.0ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 6 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
53INFO : (05-23 07:15:52) initial call types: [46,47,48,49,51,2716,397,1343,1344]
54INFO : (05-23 07:15:52) collect_chain_links
55 ChainLink Load (142.6ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 6
56INFO : (05-23 07:15:52) collect_linkages
57INFO : (05-23 07:15:52) collect_schedules
58 Schedule Load (156.5ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 6 AND (`schedules`.`schedule_on` BETWEEN '2017-10-01' AND '2017-10-31')
59INFO : (05-23 07:15:52) collect_slots
60INFO : (05-23 07:15:52) collect_approved_call_requests
61 Request Load (150.5ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2017-10-01' AND '2017-10-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
62INFO : (05-23 07:15:53) collect_approved_no_call_requests
63 Request Load (145.0ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2017-10-01' AND '2017-10-31') ORDER BY requests.schedule_on asc, requests.created_at asc
64INFO : (05-23 07:15:53) finished: Scheduler::Cache::Collector.collect_all
65INFO : (05-23 07:15:53) begin to process requests
66 CallType Load (168.0ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 6 AND `call_types`.`active` = 1
67 (154.1ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 6 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
68 Request Load (158.1ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 6 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (46, 47, 48, 49, 51, 397, 1343, 1344, 2716) AND (requests.schedule_on > '2017-09-30')
69 SQL (164.1ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 6 AND 1=0
70INFO : (05-23 07:15:54) end of requests processing
71 Block Load (141.9ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date <= '2017-10-31' AND blocks.end_date >= '2017-10-31') ORDER BY start_date asc, end_date asc LIMIT 1
72 Client Load (215.2ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
73 (144.7ms) SELECT `autopopulate_exclusions`.`schedule_on`, `autopopulate_exclusions`.`call_type_id` FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 6 AND `autopopulate_exclusions`.`schedule_on` IN ('2017-10-01', '2017-10-02', '2017-10-03', '2017-10-04', '2017-10-05', '2017-10-06', '2017-10-07', '2017-10-08', '2017-10-09', '2017-10-10', '2017-10-11', '2017-10-12', '2017-10-13', '2017-10-14', '2017-10-15', '2017-10-16', '2017-10-17', '2017-10-18', '2017-10-19', '2017-10-20', '2017-10-21', '2017-10-22', '2017-10-23', '2017-10-24', '2017-10-25', '2017-10-26', '2017-10-27', '2017-10-28', '2017-10-29', '2017-10-30', '2017-10-31')
74 (149.2ms) select
75 b.start_date,
76 b.end_date,
77 (pb.assignment_ids IS NULL) as fully_published,
78 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
79 pbct.days_of_week_mask
80 from blocks as b
81 inner join published_blocks as pb
82 on pb.block_id = b.id
83 inner join published_block_call_types as pbct
84 on pbct.published_block_id = pb.id
85 where b.client_id = 6
86 and pb.calendar_schedule_id = 6
87 and b.end_date BETWEEN '2017-10-01' AND '2017-10-31'
88
89INFO : (05-23 07:15:55) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
90INFO : (05-23 07:15:55) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
91INFO : (05-23 07:15:55) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
92INFO : (05-23 07:15:55) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
93INFO : (05-23 07:15:55) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
94INFO : (05-23 07:15:55) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
95INFO : (05-23 07:15:55) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
96INFO : (05-23 07:15:55) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
97INFO : (05-23 07:15:55) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
98INFO : (05-23 07:15:55) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query015
99INFO : (05-23 07:15:55) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
100INFO : (05-23 07:15:55) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
101INFO : (05-23 07:15:55) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
102INFO : (05-23 07:15:55) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
103INFO : (05-23 07:15:55) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
104INFO : (05-23 07:15:55) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
105INFO : (05-23 07:15:55) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
106INFO : (05-23 07:15:55) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
107INFO : (05-23 07:15:55) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
108INFO : (05-23 07:15:55) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
109INFO : (05-23 07:15:55) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0705
110INFO : (05-23 07:15:55) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0706
111INFO : (05-23 07:15:55) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0707
112INFO : (05-23 07:15:55) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0708
113INFO : (05-23 07:15:55) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0709
114INFO : (05-23 07:15:55) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query07_10_AL
115INFO : (05-23 07:15:55) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
116INFO : (05-23 07:15:55) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
117INFO : (05-23 07:15:55) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
118INFO : (05-23 07:15:55) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
119INFO : (05-23 07:15:55) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query08
120INFO : (05-23 07:15:55) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query085
121INFO : (05-23 07:15:55) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query09
122INFO : (05-23 07:15:55) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query092
123INFO : (05-23 07:15:55) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query093
124INFO : (05-23 07:15:55) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query095
125INFO : (05-23 07:15:55) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query097
126INFO : (05-23 07:15:55) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query098
127INFO : (05-23 07:15:55) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
128INFO : (05-23 07:15:55) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
129INFO : (05-23 07:15:55) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_12_Points
130INFO : (05-23 07:15:55) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
131INFO : (05-23 07:15:55) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query10
132INFO : (05-23 07:15:55) <-- clear: 43 - Scheduler::AutoPopulate::Queries::QueryScore02
133INFO : (05-23 07:15:55) <-- clear: 44 - Scheduler::AutoPopulate::Queries::QueryScore03
134INFO : (05-23 07:15:55) <-- clear: 45 - Scheduler::AutoPopulate::Queries::QueryScore04
135INFO : (05-23 07:15:55) <-- clear: 46 - Scheduler::AutoPopulate::Queries::Query11
136INFO : (05-23 07:15:55) <-- clear: 47 - Scheduler::AutoPopulate::Queries::DropTables
137 (142.7ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_926417780
138 (143.7ms) DROP TABLE IF EXISTS buffer_annual_point_targets_926417780
139 (143.1ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_926417780
140 (148.3ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_926417780
141 (142.3ms) DROP TABLE IF EXISTS buffer_call_days_targets_926417780
142 (143.1ms) DROP TABLE IF EXISTS buffer_call_type_targets_926417780
143 (145.0ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_926417780
144 (142.3ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_926417780
145 (142.8ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_926417780
146 (142.8ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_926417780
147 (143.4ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_926417780
148 (142.4ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_926417780
149 (148.6ms) DROP TABLE IF EXISTS buffer_current_cd_targets_926417780
150 (142.5ms) DROP TABLE IF EXISTS buffer_current_ct_targets_926417780
151 (143.0ms) DROP TABLE IF EXISTS buffer_current_dow_targets_926417780
152 (142.6ms) DROP TABLE IF EXISTS buffer_current_hour_targets_926417780
153 (148.3ms) DROP TABLE IF EXISTS buffer_current_oa_targets_926417780
154 (142.8ms) DROP TABLE IF EXISTS buffer_current_point_targets_926417780
155 (142.3ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_926417780
156 (142.6ms) DROP TABLE IF EXISTS buffer_hour_targets_926417780
157 (150.2ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_926417780
158 (142.5ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_926417780
159 (156.8ms) DROP TABLE IF EXISTS buffer_linkage_targets_926417780
160 (142.6ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_926417780
161 (162.1ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_926417780
162 (146.8ms) DROP TABLE IF EXISTS buffer_location_privileges_926417780
163 (146.1ms) DROP TABLE IF EXISTS buffer_periods_926417780
164 (142.1ms) DROP TABLE IF EXISTS buffer_point_targets_926417780
165 (161.9ms) DROP TABLE IF EXISTS buffer_schedules_926417780
166 (142.2ms) DROP TABLE IF EXISTS buffer_target_call_types_926417780
167 (150.0ms) DROP TABLE IF EXISTS buffer_target_daily_points_926417780
168 (143.0ms) DROP TABLE IF EXISTS buffer_target_dates_list_926417780
169 (160.7ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_926417780
170 (143.3ms) DROP TABLE IF EXISTS buffer_targets_926417780
171 (147.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_926417780
172 (142.5ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_926417780
173 (160.3ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_926417780
174 (142.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_926417780
175 (147.2ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_926417780
176 (142.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_926417780
177 (172.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_926417780
178 (147.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_926417780
179 (145.5ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_926417780
180 (143.5ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_926417780
181 (160.5ms) DROP TABLE IF EXISTS buffer_call_type_assignments_926417780
182 (143.0ms) DROP TABLE IF EXISTS buffer_combined_availabilities_926417780
183 (159.2ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_926417780
184 (142.3ms) DROP TABLE IF EXISTS buffer_counter_increments_926417780
185 (148.0ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_926417780
186 (142.2ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_926417780
187 (150.9ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_926417780
188 (142.4ms) DROP TABLE IF EXISTS buffer_linkage_patterns_926417780
189 (149.9ms) DROP TABLE IF EXISTS buffer_linkages_926417780
190 (142.9ms) DROP TABLE IF EXISTS buffer_location_privileges_926417780
191 (149.2ms) DROP TABLE IF EXISTS buffer_min_distances_926417780
192 (143.4ms) DROP TABLE IF EXISTS buffer_negative_relations_926417780
193 (158.7ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_926417780
194 (142.6ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_926417780
195 (160.5ms) DROP TABLE IF EXISTS buffer_rule_scores_926417780
196 (143.6ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_926417780
197 (179.5ms) DROP TABLE IF EXISTS buffer_schedule_linkages_926417780
198 (142.3ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_926417780
199 (150.5ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_926417780
200 (143.4ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_926417780
201 (160.1ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_926417780
202 (142.7ms) DROP TABLE IF EXISTS buffer_staff_specializations_926417780
203 (160.5ms) DROP TABLE IF EXISTS buffer_static_availabilities_926417780
204 (142.7ms) DROP TABLE IF EXISTS buffer_target_assignments_926417780
205 (158.4ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_926417780
206 (142.0ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_926417780
207 (151.1ms) DROP TABLE IF EXISTS buffer_assignment_limits_926417780
208 (142.4ms) delete from buffer_target_dates
209 where session_id = 926417780
210
211INFO : (05-23 07:16:06) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
212 (161.6ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_926417780 LIKE buffer_assignment_limit_call_types
213 (147.0ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_926417780 LIKE buffer_assignment_limit_periods
214 (154.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_926417780 LIKE buffer_assignment_limit_staff_types
215 (145.1ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_926417780 LIKE buffer_assignment_limit_staffs
216 (160.6ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_926417780 LIKE buffer_auto_populate_slots
217 (145.2ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_926417780 LIKE buffer_call_type_assignments
218 (152.0ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_926417780 LIKE buffer_combined_availabilities
219 (145.6ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_926417780 LIKE buffer_combined_availability_linkages
220 (146.5ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_926417780 LIKE buffer_counter_increments
221 (146.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_926417780 LIKE buffer_linkage_assigned_days
222 (150.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_926417780 LIKE buffer_linkage_call_type_assignments
223 (145.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_926417780 LIKE buffer_linkage_dow_targets
224 (159.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_926417780 LIKE buffer_linkage_patterns
225 (146.8ms) CREATE TABLE IF NOT EXISTS buffer_linkages_926417780 LIKE buffer_linkages
226 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_926417780 LIKE buffer_location_privileges
227 (144.8ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_926417780 LIKE buffer_min_distances
228 (155.1ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_926417780 LIKE buffer_negative_relations
229 (145.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_926417780 LIKE buffer_rule_calc_filters
230 (164.7ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_926417780 LIKE buffer_rule_calc_schedules
231 (169.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_926417780 LIKE buffer_rule_scores
232 (145.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_926417780 LIKE buffer_rule_scores_with_corrections
233 (147.1ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_926417780 LIKE buffer_schedule_linkages
234 (145.4ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_926417780 LIKE buffer_schedules_linkage_patterns
235 (145.7ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_926417780 LIKE buffer_specialization_daily_counts
236 (145.5ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_926417780 LIKE buffer_staff_assigned_days
237 (145.2ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_926417780 LIKE buffer_staff_daily_counts
238 (160.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_926417780 LIKE buffer_staff_specializations
239 (145.2ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_926417780 LIKE buffer_static_availabilities
240 (156.8ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_926417780 LIKE buffer_target_assignments
241 (145.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_926417780 LIKE buffer_linkage_assignment_limits
242 (157.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_926417780 LIKE buffer_staff_assignment_limits
243 (144.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_926417780 LIKE buffer_assignment_limits
244 (145.1ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_926417780 LIKE buffer_annual_hour_targets
245 (150.8ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_926417780 LIKE buffer_annual_point_targets
246 (153.4ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_926417780 LIKE buffer_annual_targets_staff_totals
247 (142.1ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_926417780 LIKE buffer_auto_populate_slots
248 (146.7ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_926417780 LIKE buffer_call_days_targets
249 (145.2ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_926417780 LIKE buffer_call_type_targets
250 (144.6ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_926417780 LIKE buffer_cumulative_cd_targets
251 (145.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_926417780 LIKE buffer_cumulative_ct_targets
252 (158.2ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_926417780 LIKE buffer_cumulative_dow_targets
253 (146.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_926417780 LIKE buffer_cumulative_hour_targets
254 (154.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_926417780 LIKE buffer_cumulative_oa_targets
255 (146.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_926417780 LIKE buffer_cumulative_point_targets
256 (147.0ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_926417780 LIKE buffer_current_cd_targets
257 (146.0ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_926417780 LIKE buffer_current_ct_targets
258 (151.9ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_926417780 LIKE buffer_current_dow_targets
259 (496.9ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_926417780 LIKE buffer_current_hour_targets
260 (146.7ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_926417780 LIKE buffer_current_oa_targets
261 (145.5ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_926417780 LIKE buffer_current_point_targets
262 (147.7ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_926417780 LIKE buffer_day_of_week_targets
263 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_926417780 LIKE buffer_hour_targets
264 (150.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_926417780 LIKE buffer_linkage_call_days_targets
265 (147.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_926417780 LIKE buffer_linkage_call_type_targets
266 (147.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_926417780 LIKE buffer_linkage_targets
267 (145.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_926417780 LIKE buffer_linkage_hour_targets
268 (147.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_926417780 LIKE buffer_linkage_point_targets
269 (143.6ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_926417780 LIKE buffer_location_privileges
270 (163.8ms) CREATE TABLE IF NOT EXISTS buffer_periods_926417780 LIKE buffer_periods
271 (145.2ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_926417780 LIKE buffer_point_targets
272 (145.0ms) CREATE TABLE IF NOT EXISTS buffer_schedules_926417780 LIKE buffer_schedules
273 (148.1ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_926417780 LIKE buffer_target_call_types
274 (156.9ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_926417780 LIKE buffer_target_daily_points
275 (144.9ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_926417780 LIKE buffer_target_dates_list
276 (146.0ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_926417780 LIKE buffer_target_primary_availabilities
277 (145.4ms) CREATE TABLE IF NOT EXISTS buffer_targets_926417780 LIKE buffer_targets
278 (147.1ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_926417780 LIKE buffer_tmp_targets_availabilities
279 (146.4ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_926417780 LIKE buffer_tmp_targets_availabilities_exclusions
280 (144.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_926417780 LIKE buffer_tmp_targets_client_totals
281 (145.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_926417780 LIKE buffer_tmp_targets_scheduled
282 (145.4ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_926417780 LIKE buffer_tmp_targets_staff_totals
283INFO : (05-23 07:16:17) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
284 (142.3ms) select
285 b.start_date,
286 b.end_date,
287 (pb.assignment_ids IS NULL) as fully_published,
288 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
289 pbct.days_of_week_mask
290 from blocks as b
291 inner join published_blocks as pb
292 on pb.block_id = b.id
293 inner join published_block_call_types as pbct
294 on pbct.published_block_id = pb.id
295 where b.client_id = 6
296 and pb.calendar_schedule_id = 6
297 and b.end_date BETWEEN '2017-10-01' AND '2017-10-31'
298
299 AutopopulateExclusion Load (144.0ms) SELECT `autopopulate_exclusions`.* FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 6 AND `autopopulate_exclusions`.`schedule_on` IN ('2017-10-01', '2017-10-02', '2017-10-03', '2017-10-04', '2017-10-05', '2017-10-06', '2017-10-07', '2017-10-08', '2017-10-09', '2017-10-10', '2017-10-11', '2017-10-12', '2017-10-13', '2017-10-14', '2017-10-15', '2017-10-16', '2017-10-17', '2017-10-18', '2017-10-19', '2017-10-20', '2017-10-21', '2017-10-22', '2017-10-23', '2017-10-24', '2017-10-25', '2017-10-26', '2017-10-27', '2017-10-28', '2017-10-29', '2017-10-30', '2017-10-31')
300 (148.6ms) INSERT INTO buffer_auto_populate_slots_926417780
301 (schedule_on, day_of_week_mask, call_type_id, display_order, should_be_populated)
302 VALUES ('2017-10-01', 1, 46, 0, 1), ('2017-10-01', 1, 48, 3, 1), ('2017-10-02', 2, 46, 0, 1), ('2017-10-02', 2, 47, 2, 1), ('2017-10-02', 2, 48, 3, 1), ('2017-10-02', 2, 49, 4, 1), ('2017-10-02', 2, 51, 7, 1), ('2017-10-03', 4, 46, 0, 1), ('2017-10-03', 4, 47, 2, 1), ('2017-10-03', 4, 48, 3, 1), ('2017-10-03', 4, 49, 4, 1), ('2017-10-03', 4, 51, 7, 1), ('2017-10-04', 8, 46, 0, 1), ('2017-10-04', 8, 47, 2, 1), ('2017-10-04', 8, 48, 3, 1), ('2017-10-04', 8, 49, 4, 1), ('2017-10-04', 8, 51, 7, 1), ('2017-10-05', 16, 46, 0, 1), ('2017-10-05', 16, 47, 2, 1), ('2017-10-05', 16, 48, 3, 1), ('2017-10-05', 16, 49, 4, 1), ('2017-10-05', 16, 51, 7, 1), ('2017-10-06', 32, 46, 0, 1), ('2017-10-06', 32, 47, 2, 1), ('2017-10-06', 32, 48, 3, 1), ('2017-10-06', 32, 49, 4, 1), ('2017-10-06', 32, 51, 7, 1), ('2017-10-07', 64, 46, 0, 1), ('2017-10-07', 64, 48, 3, 1), ('2017-10-08', 1, 46, 0, 1), ('2017-10-08', 1, 48, 3, 1), ('2017-10-09', 2, 46, 0, 1), ('2017-10-09', 2, 47, 2, 1), ('2017-10-09', 2, 48, 3, 1), ('2017-10-09', 2, 49, 4, 1), ('2017-10-09', 2, 51, 7, 1), ('2017-10-10', 4, 46, 0, 1), ('2017-10-10', 4, 47, 2, 1), ('2017-10-10', 4, 48, 3, 1), ('2017-10-10', 4, 49, 4, 1), ('2017-10-10', 4, 51, 7, 1), ('2017-10-11', 8, 46, 0, 1), ('2017-10-11', 8, 47, 2, 1), ('2017-10-11', 8, 48, 3, 1), ('2017-10-11', 8, 49, 4, 1), ('2017-10-11', 8, 51, 7, 1), ('2017-10-12', 16, 46, 0, 1), ('2017-10-12', 16, 47, 2, 1), ('2017-10-12', 16, 48, 3, 1), ('2017-10-12', 16, 49, 4, 1), ('2017-10-12', 16, 51, 7, 1), ('2017-10-13', 32, 46, 0, 1), ('2017-10-13', 32, 47, 2, 1), ('2017-10-13', 32, 48, 3, 1), ('2017-10-13', 32, 49, 4, 1), ('2017-10-13', 32, 51, 7, 1), ('2017-10-14', 64, 46, 0, 1), ('2017-10-14', 64, 48, 3, 1), ('2017-10-15', 1, 46, 0, 1), ('2017-10-15', 1, 48, 3, 1), ('2017-10-16', 2, 46, 0, 1), ('2017-10-16', 2, 47, 2, 1), ('2017-10-16', 2, 48, 3, 1), ('2017-10-16', 2, 49, 4, 1), ('2017-10-16', 2, 51, 7, 1), ('2017-10-17', 4, 46, 0, 1), ('2017-10-17', 4, 47, 2, 1), ('2017-10-17', 4, 48, 3, 1), ('2017-10-17', 4, 49, 4, 1), ('2017-10-17', 4, 51, 7, 1), ('2017-10-18', 8, 46, 0, 1), ('2017-10-18', 8, 47, 2, 1), ('2017-10-18', 8, 48, 3, 1), ('2017-10-18', 8, 49, 4, 1), ('2017-10-18', 8, 51, 7, 1), ('2017-10-19', 16, 46, 0, 1), ('2017-10-19', 16, 47, 2, 1), ('2017-10-19', 16, 48, 3, 1), ('2017-10-19', 16, 49, 4, 1), ('2017-10-19', 16, 51, 7, 1), ('2017-10-20', 32, 46, 0, 1), ('2017-10-20', 32, 47, 2, 1), ('2017-10-20', 32, 48, 3, 1), ('2017-10-20', 32, 49, 4, 1), ('2017-10-20', 32, 51, 7, 1), ('2017-10-21', 64, 46, 0, 1), ('2017-10-21', 64, 48, 3, 1), ('2017-10-22', 1, 46, 0, 1), ('2017-10-22', 1, 48, 3, 1), ('2017-10-23', 2, 46, 0, 1), ('2017-10-23', 2, 47, 2, 1), ('2017-10-23', 2, 48, 3, 1), ('2017-10-23', 2, 49, 4, 1), ('2017-10-23', 2, 51, 7, 1), ('2017-10-24', 4, 46, 0, 1), ('2017-10-24', 4, 47, 2, 1), ('2017-10-24', 4, 48, 3, 1), ('2017-10-24', 4, 49, 4, 1), ('2017-10-24', 4, 51, 7, 1), ('2017-10-25', 8, 46, 0, 1), ('2017-10-25', 8, 47, 2, 1), ('2017-10-25', 8, 48, 3, 1), ('2017-10-25', 8, 49, 4, 1), ('2017-10-25', 8, 51, 7, 1), ('2017-10-26', 16, 46, 0, 1), ('2017-10-26', 16, 47, 2, 1), ('2017-10-26', 16, 48, 3, 1), ('2017-10-26', 16, 49, 4, 1), ('2017-10-26', 16, 51, 7, 1), ('2017-10-27', 32, 46, 0, 1), ('2017-10-27', 32, 47, 2, 1), ('2017-10-27', 32, 48, 3, 1), ('2017-10-27', 32, 49, 4, 1), ('2017-10-27', 32, 51, 7, 1), ('2017-10-28', 64, 46, 0, 1), ('2017-10-28', 64, 48, 3, 1), ('2017-10-29', 1, 46, 0, 1), ('2017-10-29', 1, 48, 3, 1), ('2017-10-30', 2, 46, 0, 1), ('2017-10-30', 2, 47, 2, 1), ('2017-10-30', 2, 48, 3, 1), ('2017-10-30', 2, 49, 4, 1), ('2017-10-30', 2, 51, 7, 1), ('2017-10-31', 4, 46, 0, 1), ('2017-10-31', 4, 47, 2, 1), ('2017-10-31', 4, 48, 3, 1), ('2017-10-31', 4, 49, 4, 1), ('2017-10-31', 4, 51, 7, 1)
303
304INFO : (05-23 07:16:17) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
305 (156.1ms) insert into buffer_auto_populate_slots_926417780/* client_id */ (
306 schedule_on,
307 day_of_week_mask,
308 call_type_id,
309 display_order,
310 should_be_populated
311 )
312 select distinct
313 date_add(
314 auto_populate_slots.schedule_on,
315 interval soft_linkages.offset day
316 ) as schedule_on,
317 (1 << (
318 dayofweek(
319 date_add(
320 auto_populate_slots.schedule_on,
321 interval soft_linkages.offset day
322 )
323 ) - 1
324 )
325 ) as day_of_week_mask,
326 soft_linkages.linked_call_type_id as call_type_id,
327 call_types.display_order,
328 0 as should_be_populated
329 from
330 buffer_auto_populate_slots_926417780/* client_id */ as auto_populate_slots
331 inner join soft_linkages
332 on auto_populate_slots.call_type_id = soft_linkages.call_type_id
333 and auto_populate_slots.day_of_week_mask = soft_linkages.day_of_week_mask
334 left join buffer_auto_populate_slots_926417780/* client_id */ as auto_populate_slots_2
335 on date_add(
336 auto_populate_slots.schedule_on,
337 interval soft_linkages.offset day
338 ) = auto_populate_slots_2.schedule_on
339 and soft_linkages.linked_call_type_id = auto_populate_slots_2.call_type_id
340 inner join call_types
341 on soft_linkages.linked_call_type_id = call_types.id
342 where auto_populate_slots_2.id is null
343 order by null
344
345INFO : (05-23 07:16:17) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
346 (144.1ms) insert into buffer_auto_populate_slots_926417780 (
347 schedule_on,
348 day_of_week_mask,
349 call_type_id,
350 display_order,
351 should_be_populated
352 )
353 select distinct
354 date_add(
355 auto_populate_slots.schedule_on,
356 interval linkage_relations.distance_in_days day
357 ) as schedule_on,
358 (1 << (
359 dayofweek(
360 date_add(
361 auto_populate_slots.schedule_on,
362 interval linkage_relations.distance_in_days day
363 )
364 ) - 1
365 )
366 ) as day_of_week_mask,
367 linkage_relations.linked_call_type_id as call_type_id,
368 call_types.display_order,
369 0 as should_be_populated
370 from
371 buffer_auto_populate_slots_926417780 as auto_populate_slots
372 inner join linkage_relations
373 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
374 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
375 left join buffer_auto_populate_slots_926417780 as auto_populate_slots_2
376 on date_add(
377 auto_populate_slots.schedule_on,
378 interval linkage_relations.distance_in_days day
379 ) = auto_populate_slots_2.schedule_on
380 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
381 inner join call_types
382 on linkage_relations.linked_call_type_id = call_types.id
383 where auto_populate_slots_2.id is null
384 order by null
385
386INFO : (05-23 07:16:17) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
387 (145.8ms) insert into linkage_relations (
388 client_id,
389 call_type_id,
390 day_of_week_mask,
391 linked_call_type_id,
392 distance_in_days,
393 linkage_template_id
394 )
395 select distinct
396 6 /* client_id */ as client_id,
397 aps.call_type_id,
398 aps.day_of_week_mask,
399 aps.call_type_id as linked_call_type_id,
400 0 as distance_in_days,
401 coalesce(
402 lr.linkage_template_id,
403 @linkage_template_max := @linkage_template_max + 1
404 ) as linkage_template_id
405 from
406 buffer_auto_populate_slots_926417780/* client_id */ as aps
407 inner join (
408 select @linkage_template_max := count(distinct linkage_template_id) - 1
409 from linkage_relations
410 where client_id = 6 /* client_id */
411 order by null
412 ) as lt
413 on 1 = 1
414 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
415 on aps.call_type_id = lr.call_type_id
416 and aps.day_of_week_mask = lr.day_of_week_mask
417 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
418 on aps.call_type_id = lr_2.call_type_id
419 and aps.day_of_week_mask = lr_2.day_of_week_mask
420 and aps.call_type_id = lr_2.linked_call_type_id
421 and lr_2.distance_in_days = 0
422 where lr_2.id is null
423 order by null
424
425 (158.7ms) insert into buffer_schedules_926417780 (
426 call_type_id,
427 staff_id,
428 schedule_on,
429 day_of_week_mask,
430 is_weekend,
431 display_order,
432 week_number,
433 should_be_evaluated,
434 should_be_populated,
435 linkage_instance_id
436 )
437 select
438 call_type_id,
439 staff_id,
440 schedule_on,
441 day_of_week_mask,
442 is_weekend,
443 display_order,
444 week_number,
445 max(should_be_evaluated) as should_be_evaluated,
446 max(should_be_populated) as should_be_populated,
447 min(linkage_instance_id) as linkage_instance_id
448 from (
449 select
450 auto_populate_slots_2.call_type_id,
451 schedules.staff_id,
452 auto_populate_slots_2.schedule_on,
453 auto_populate_slots_2.day_of_week_mask,
454 case
455 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
456 else 1
457 end as is_weekend,
458 auto_populate_slots_2.display_order,
459 yearweek(
460 auto_populate_slots_2.schedule_on,
461 case
462 when (clients.start_of_week = 0) then 2
463 else 7
464 end
465 ) as week_number,
466 case
467 when (schedules.staff_id is null) then 1
468 else 0
469 end as should_be_evaluated,
470 (
471 case
472 when (
473 auto_populate_slots_2.should_be_populated = 1
474 and schedules.staff_id is null
475 ) then 1
476 else 0
477 end
478 ) as should_be_populated,
479 (
480 linkage_relations.linkage_template_id * 1000000 +
481 yearweek(
482 auto_populate_slots.schedule_on,
483 case
484 when (clients.start_of_week = 0) then 2
485 else 7
486 end
487 )
488 ) as linkage_instance_id
489 from
490 buffer_auto_populate_slots_926417780 as auto_populate_slots
491 inner join clients
492 on clients.id = 6 /* client_id */
493 inner join linkage_relations
494 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
495 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
496 inner join buffer_auto_populate_slots_926417780 as auto_populate_slots_2
497 on date_add(
498 auto_populate_slots.schedule_on,
499 interval linkage_relations.distance_in_days day
500 ) = auto_populate_slots_2.schedule_on
501 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
502 left join schedules
503 on auto_populate_slots_2.call_type_id = schedules.call_type_id
504 and auto_populate_slots_2.schedule_on = schedules.schedule_on
505 order by null
506 ) as t1
507 group by
508 call_type_id,
509 staff_id,
510 schedule_on,
511 day_of_week_mask,
512 display_order,
513 week_number
514 order by null
515
516INFO : (05-23 07:16:18) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
517 (162.4ms) update
518 buffer_schedules_926417780 as sc
519 inner join call_types as ct
520 on sc.call_type_id = ct.id
521 inner join calendar_schedules as cs
522 on ct.calendar_schedule_id = cs.id
523 inner join blocks as b
524 on cs.block_schedule_id = b.block_schedule_id
525 and sc.schedule_on between b.start_date and b.end_date
526 set
527 sc.calendar_schedule_id = cs.id,
528 sc.block_id = b.id
529
530 BlockSchedule Load (140.7ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
531 (148.8ms) insert into buffer_periods_926417780 (
532 calendar_schedule_id,
533 block_schedule_id,
534 period_offset,
535 start_block_display_order,
536 end_block_display_order,
537 start_date,
538 end_date,
539 should_be_populated,
540 should_be_evaluated
541 )
542 select
543 t1.calendar_schedule_id,
544 t1.block_schedule_id,
545 t1.period_offset,
546 t2.start_block_display_order,
547 t2.end_block_display_order,
548 t2.start_date,
549 t2.end_date,
550 t1.should_be_populated,
551 t1.should_be_evaluated
552 from (
553 select
554 sc.calendar_schedule_id,
555 b.block_schedule_id,
556 case
557 when ((month(b.start_date) - pt.begin_period) >= 0)
558 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
559 else
560 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
561 end as period_offset,
562 max(sc.should_be_populated) as should_be_populated,
563 max(sc.should_be_evaluated) as should_be_evaluated
564 from (
565 select
566 sc.block_id,
567 sc.calendar_schedule_id,
568 max(ps.should_be_populated) as should_be_populated,
569 1 as should_be_evaluated
570 from
571 buffer_schedules_926417780 as sc
572 inner join buffer_auto_populate_slots_926417780 as ps
573 on sc.schedule_on = ps.schedule_on
574 and sc.call_type_id = ps.call_type_id
575 group by
576 sc.block_id,
577 sc.calendar_schedule_id
578 order by null
579 ) as sc
580 inner join blocks as b
581 on sc.block_id = b.id
582 inner join population_timeframes as pt
583 on pt.client_id = 6
584 and pt.timeframe_type = 1
585 group by
586 sc.calendar_schedule_id,
587 b.block_schedule_id,
588 period_offset
589 order by null
590 ) as t1
591 inner join (
592 select
593 b.block_schedule_id,
594 case
595 when ((month(b.start_date) - pt.begin_period) >= 0)
596 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
597 else
598 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
599 end as period_offset,
600 min(b.display_order) as start_block_display_order,
601 max(b.display_order) as end_block_display_order,
602 min(b.start_date) as start_date,
603 max(b.end_date) as end_date
604 from
605 blocks as b
606 inner join population_timeframes as pt
607 on pt.client_id = 6
608 and pt.timeframe_type = 1
609 where
610 b.client_id = 6
611 group by
612 b.block_schedule_id,
613 period_offset
614 order by null
615 ) as t2
616 on t1.block_schedule_id = t2.block_schedule_id
617 and t1.period_offset = t2.period_offset
618 order by null
619
620 (143.3ms) update
621 buffer_schedules_926417780 as sc
622 inner join buffer_periods_926417780 as p
623 on sc.calendar_schedule_id = p.calendar_schedule_id
624 and sc.schedule_on between p.start_date and p.end_date
625 set sc.period_offset = p.period_offset
626
627INFO : (05-23 07:16:18) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
628INFO : (05-23 07:16:18) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
629 (178.4ms) insert into buffer_counter_increments_926417780 (
630 schedule_id,
631 calls,
632 points,
633 hours,
634 affects_cumulative_targets
635 )
636 select
637 sc.id as schedule_id,
638 case
639 when (ct.included_in_targets > 0) then 1
640 else 0
641 end as calls,
642 coalesce(
643 case
644 when (hl.observed_date is null or dp.point is not null) then dp.point
645 else dp_2.point
646 end,
647 0.0
648 ) as points,
649 ct.hours,
650 case
651 when (b.display_order between (t1.timeframe_ending - (2 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
652 else 0
653 end as affects_cumulative_targets
654 from
655 buffer_schedules_926417780 as sc
656 inner join (
657 select
658 id,
659 included_in_targets,
660 coalesce(
661 timestampdiff(
662 minute,
663 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
664 concat(
665 '1900-01-0',
666 case
667 when ((
668 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
669 )
670 and (
671 timediff(
672 maketime(end_time_hour, end_time_minute, 0),
673 maketime(start_time_hour, start_time_minute, 0)
674 ) <= 0
675 )
676 ) then '2'
677 else '1'
678 end,
679 ' ',
680 maketime(end_time_hour, end_time_minute, 0)
681 )
682 ) / 60,
683 0.0
684 ) as hours
685 from call_types
686 where
687 client_id = 6
688 and active = 1
689 order by null
690 ) as ct
691 on sc.call_type_id = ct.id
692 inner join blocks as b
693 on sc.block_id = b.id
694 inner join (
695 select
696 calendar_schedule_id,
697 max(
698 case
699 when (should_be_populated = 1) then end_block_display_order
700 else null
701 end
702 ) as timeframe_ending
703 from buffer_periods_926417780
704 group by calendar_schedule_id
705 order by null
706 ) as t1
707 on sc.calendar_schedule_id = t1.calendar_schedule_id
708 left join (
709 select
710 hd.observed_date,
711 case
712 when (hd.observed_date > hd.extended_date) then hd.extended_date
713 else hd.observed_date
714 end as date_from,
715 case
716 when (hd.observed_date < hd.extended_date) then hd.extended_date
717 else hd.observed_date
718 end as date_to
719 from
720 holidays as h
721 inner join holiday_dates as hd
722 on h.id = hd.holiday_id
723 inner join (
724 select
725 year(min(schedule_on)) as year_from,
726 year(max(schedule_on)) as year_to
727 from buffer_schedules_926417780
728 where should_be_evaluated = 1
729 ) as y
730 on hd.year between y.year_from and y.year_to
731 where
732 h.client_id = 6
733 and (
734 h.observed = 1
735 or h.extended = 1
736 )
737 order by null
738 ) as hl
739 on sc.schedule_on between hl.date_from and hl.date_to
740 left join assignment_annotations as aa
741 on ct.id = aa.assignmentable_id
742 and aa.assignmentable_type = 'CallType'
743 and aa.annotation_id = 0 /* annotation = "None" */
744 and aa.applicable = 1
745 left join assignment_annotation_daily_points as dp
746 on aa.id = dp.assignment_annotation_id
747 and case
748 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
749 when (sc.schedule_on = hl.observed_date) then 7
750 else 8
751 end = dp.day
752 left join assignment_annotation_daily_points as dp_2
753 on aa.id = dp_2.assignment_annotation_id
754 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
755 where sc.should_be_evaluated = 1
756 order by null
757
758INFO : (05-23 07:16:18) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
759 (146.5ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
760 insert into buffer_linkage_patterns_926417780 (
761 period_offset,
762 calendar_schedule_id,
763 block_id,
764 block_start_date,
765 block_start_dow,
766 block_length,
767 linkage_pattern_id,
768 pattern_start_dow,
769 days_before_start,
770 pattern_length,
771 start_offset,
772 iterations_count
773 )
774 select
775 b1.period_offset,
776 b1.calendar_schedule_id,
777 b1.block_id,
778 b2.start_date as block_start_date,
779 dayofweek(b2.start_date) as block_start_dow,
780 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
781 lp.id as linkage_pattern_id,
782 (lp.start_on + 1) as pattern_start_dow,
783 lp.days_before_start,
784 (lp.length * 7) as pattern_length,
785 null as start_offset,
786 null as iterations_count
787 from (
788 select
789 calendar_schedule_id,
790 block_id,
791 period_offset
792 from buffer_schedules_926417780
793 group by
794 calendar_schedule_id,
795 block_id,
796 period_offset
797 order by null
798 ) as b1
799 inner join blocks as b2
800 on b1.block_id = b2.id
801 inner join linkage_patterns as lp
802 on lp.client_id = 6
803 and b1.calendar_schedule_id = lp.calendar_schedule_id
804 order by null
805
806 (145.3ms) -- 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
807 update buffer_linkage_patterns_926417780
808 set start_offset = if(
809 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
810 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
811 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
812 )
813
814 (144.1ms) -- 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)
815 -- a pattern should be repeated couple of times (in order to cover the entire block)
816 update buffer_linkage_patterns_926417780
817 set iterations_count =
818 ceiling(
819 (
820 block_length +
821 case
822 when (start_offset < 0) then abs(start_offset)
823 else 0
824 end
825 ) * 1.0 / pattern_length
826 )
827
828 (142.5ms) -- Generate an assignments sequence for every linkage pattern
829 insert into buffer_schedules_linkage_patterns_926417780 (
830 call_type_id,
831 staff_id,
832 schedule_on,
833 period_offset,
834 day_of_week_mask,
835 display_order,
836 week_number,
837 should_be_evaluated,
838 should_be_populated,
839 linkage_instance_id,
840 block_id,
841 calendar_schedule_id
842 )
843 select
844 lps.assignment_id as call_type_id,
845 null as staff_id,
846 case
847 when (lp.start_offset < 0)
848 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
849 else
850 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
851 end as schedule_on,
852 lp.period_offset,
853 null as day_of_week_mask,
854 null as display_order,
855 null as week_number,
856 0 as should_be_evaluated,
857 0 as should_be_populated,
858 lp.linkage_pattern_id as linkage_instance_id,
859 lp.block_id,
860 lp.calendar_schedule_id
861 from
862 numeric_sequence as ns
863 inner join buffer_linkage_patterns_926417780 as lp
864 on ns.item_number <= (lp.iterations_count - 1)
865 inner join linkage_pattern_slots as lps
866 on lp.linkage_pattern_id = lps.linkage_pattern_id
867 and lps.assignment_type = 'CallType'
868 where
869 case
870 when (lp.start_offset < 0)
871 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
872 else
873 (ns.item_number * lp.pattern_length + lps.offset)
874 end between 0 and (lp.block_length - 1)
875 order by null
876
877 (152.3ms) -- Fill the columns that we left blank in the previous "INSERT"
878 update
879 buffer_schedules_linkage_patterns_926417780 as slp
880 inner join clients as cl
881 on cl.id = 6
882 inner join call_types as ct
883 on slp.call_type_id = ct.id
884 left join buffer_schedules_926417780 as bs
885 on slp.schedule_on = bs.schedule_on
886 and slp.call_type_id = bs.call_type_id
887 set
888 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
889 slp.display_order = ct.display_order,
890 slp.week_number =
891 yearweek(
892 slp.schedule_on,
893 case
894 when (cl.start_of_week = 0) then 2
895 else 7
896 end
897 ),
898 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
899 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
900
901 (144.5ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
902 delete slp_1
903 from
904 buffer_schedules_linkage_patterns_926417780 as slp_1
905 inner join (
906 select linkage_instance_id
907 from buffer_schedules_linkage_patterns_926417780
908 group by linkage_instance_id
909 having
910 max(should_be_evaluated) = 0
911 and max(should_be_populated) = 0
912 order by null
913 ) as slp_2
914 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
915
916 (143.0ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
917 update
918 buffer_schedules_linkage_patterns_926417780 as slp
919 inner join buffer_schedules_926417780 as bs_1
920 on slp.schedule_on = bs_1.schedule_on
921 and slp.call_type_id = bs_1.call_type_id
922 inner join buffer_schedules_926417780 as bs_2
923 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
924 set bs_2.linkage_instance_id = slp.linkage_instance_id
925
926 (141.6ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
927 -- should be copied over to the buffer schedules table (used during the auto-populate process)
928 insert into buffer_schedules_926417780 (
929 call_type_id,
930 staff_id,
931 schedule_on,
932 period_offset,
933 day_of_week_mask,
934 is_weekend,
935 display_order,
936 week_number,
937 should_be_evaluated,
938 should_be_populated,
939 linkage_instance_id,
940 block_id,
941 calendar_schedule_id
942 )
943 select
944 call_type_id,
945 staff_id,
946 schedule_on,
947 period_offset,
948 day_of_week_mask,
949 case
950 when (day_of_week_mask & 62 > 0) then 0
951 else 1
952 end as is_weekend,
953 display_order,
954 week_number,
955 should_be_evaluated,
956 should_be_populated,
957 linkage_instance_id,
958 block_id,
959 calendar_schedule_id
960 from buffer_schedules_linkage_patterns_926417780
961 where
962 should_be_evaluated = 0
963 and should_be_populated = 0
964
965INFO : (05-23 07:16:20) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query015
966 (141.5ms) insert into buffer_schedules_926417780 (
967 call_type_id,
968 staff_id,
969 schedule_on,
970 day_of_week_mask,
971 is_weekend,
972 display_order,
973 should_be_evaluated,
974 should_be_populated,
975 linkage_instance_id
976 )
977 select distinct
978 schedules_call_overlap.call_type_id,
979 schedules_call_overlap.staff_id,
980 schedules_call_overlap.schedule_on,
981 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
982 case
983 when (dayofweek(schedules_call_overlap.schedule_on) between 2 and 6) then 0
984 else 1
985 end as is_weekend,
986 call_types.display_order,
987 0 as should_be_evaluated,
988 0 as should_be_populated,
989 -1 as linkage_instance_id
990 from (
991 select schedule_on
992 from buffer_schedules_926417780
993 where should_be_evaluated = 1
994 group by schedule_on
995 order by null
996 ) as buffer_schedules_1
997 inner join schedules as schedules_call_overlap
998 on schedules_call_overlap.client_id = 6
999 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1000 left join buffer_schedules_926417780 as buffer_schedules_2
1001 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1002 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1003 inner join call_types
1004 on schedules_call_overlap.call_type_id = call_types.id
1005 where buffer_schedules_2.id is null
1006 order by null
1007
1008INFO : (05-23 07:16:20) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1009INFO : (05-23 07:16:20) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1010INFO : (05-23 07:16:20) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1011INFO : (05-23 07:16:20) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1012INFO : (05-23 07:16:20) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1013 (167.5ms) insert into buffer_schedules_926417780 (
1014 call_type_id,
1015 staff_id,
1016 schedule_on,
1017 day_of_week_mask,
1018 is_weekend,
1019 display_order,
1020 should_be_evaluated,
1021 should_be_populated,
1022 linkage_instance_id
1023 )
1024 select distinct straight_join
1025 schedules.call_type_id,
1026 schedules.staff_id,
1027 schedules.schedule_on,
1028 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1029 case
1030 when (dayofweek(schedules.schedule_on) between 2 and 6) then 0
1031 else 1
1032 end as is_weekend,
1033 call_types.display_order,
1034 0 as should_be_evaluated,
1035 0 as should_be_populated,
1036 -1 as linkage_instance_id
1037 from
1038 buffer_schedules_926417780 as buffer_schedules_1
1039 inner join negative_links_influences use index (ix_unique_links)
1040 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1041 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1042 and negative_links_influences.source_type = 'CallType'
1043 and negative_links_influences.target_type = 'CallType'
1044 inner join schedules use index (ix_schedules_call_date)
1045 on date_add(
1046 buffer_schedules_1.schedule_on,
1047 interval negative_links_influences.offset day
1048 ) = schedules.schedule_on
1049 and negative_links_influences.target_id = schedules.call_type_id
1050 left join buffer_schedules_926417780 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1051 on schedules.schedule_on = buffer_schedules_2.schedule_on
1052 and schedules.call_type_id = buffer_schedules_2.call_type_id
1053 inner join call_types
1054 on schedules.call_type_id = call_types.id
1055 where
1056 buffer_schedules_1.linkage_instance_id >= 0
1057 and buffer_schedules_1.should_be_evaluated = 1
1058 and buffer_schedules_2.id is null
1059 order by null
1060
1061INFO : (05-23 07:16:20) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1062 (150.7ms) insert into buffer_schedules_926417780 (
1063 call_type_id,
1064 staff_id,
1065 schedule_on,
1066 day_of_week_mask,
1067 is_weekend,
1068 display_order,
1069 should_be_evaluated,
1070 should_be_populated,
1071 linkage_instance_id
1072 )
1073 select distinct straight_join
1074 (-1 * negative_links_influences.target_id) as call_type_id,
1075 vacations.staff_id,
1076 vacations.schedule_on,
1077 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1078 vacations.weekend_on as is_weekend,
1079 -1 as display_order,
1080 0 as should_be_evaluated,
1081 0 as should_be_populated,
1082 -1 as linkage_instance_id
1083 from
1084 buffer_schedules_926417780 as buffer_schedules_1
1085 inner join negative_links_influences use index (ix_unique_links)
1086 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1087 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1088 and negative_links_influences.source_type = 'CallType'
1089 and negative_links_influences.target_id > 0
1090 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1091 inner join vacations use index (ix_vacations_on_schedule_and_client)
1092 on vacations.client_id = 6 /* client_id */
1093 and date_add(
1094 buffer_schedules_1.schedule_on,
1095 interval negative_links_influences.offset day
1096 ) = vacations.schedule_on
1097 and negative_links_influences.target_id = vacations.non_clinical_type_id
1098 and vacations.status in (3) /* based on the "non-working requests" settings */
1099 left join buffer_schedules_926417780 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1100 on vacations.staff_id = buffer_schedules_2.staff_id
1101 and vacations.schedule_on = buffer_schedules_2.schedule_on
1102 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1103 where
1104 buffer_schedules_1.linkage_instance_id >= 0
1105 and buffer_schedules_1.should_be_evaluated = 1
1106 and buffer_schedules_2.id is null
1107 order by null
1108
1109INFO : (05-23 07:16:20) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1110 (145.4ms) insert into buffer_linkages_926417780 (
1111 linkage_instance_id,
1112 slots_count_total,
1113 has_conflicts
1114 )
1115 select straight_join
1116 schedules.linkage_instance_id,
1117 count(1) as slots_count_total,
1118 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1119 from
1120 buffer_schedules_926417780 as schedules
1121 left join (
1122 select straight_join
1123 linkage_relations.linkage_template_id
1124 from linkage_relations
1125 inner join negative_links_influences use index (ix_unique_links)
1126 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1127 and linkage_relations.call_type_id = negative_links_influences.source_id
1128 and negative_links_influences.source_type = 'CallType'
1129 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1130 and negative_links_influences.target_type = 'CallType'
1131 and linkage_relations.distance_in_days = negative_links_influences.offset
1132 where linkage_relations.client_id = 6 /* client_id */
1133 and (
1134 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1135 or linkage_relations.distance_in_days != 0
1136 )
1137 group by linkage_relations.linkage_template_id
1138 order by null
1139 ) as linkage_conflicts
1140 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1141 where schedules.linkage_instance_id >= 0
1142 group by
1143 schedules.linkage_instance_id,
1144 has_conflicts
1145 order by null
1146
1147INFO : (05-23 07:16:20) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1148 (145.1ms) insert into buffer_target_assignments_926417780 (
1149 call_type_id,
1150 target_group_id,
1151 assignment_id,
1152 assignment_type
1153 )
1154 select
1155 t1.call_type_id,
1156 ifnull(tg.id, 0) as target_group_id,
1157 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1158 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1159 from (
1160 select ct.id as call_type_id
1161 from
1162 buffer_schedules_926417780 as sc
1163 inner join call_types as ct
1164 on sc.call_type_id = ct.id
1165 and ct.active = 1
1166 and ct.included_in_targets > 0
1167 where linkage_instance_id >= 0
1168 group by ct.id
1169 order by null
1170 ) as t1
1171 left join target_group_calls as tgc
1172 on t1.call_type_id = tgc.call_type_id
1173 left join target_groups as tg
1174 on tgc.target_group_id = tg.id
1175 and tg.active = 1
1176 order by null
1177
1178INFO : (05-23 07:16:20) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1179INFO : (05-23 07:16:20) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1180INFO : (05-23 07:16:20) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0705
1181 (142.4ms) insert into buffer_linkage_call_type_assignments_926417780 (
1182 linkage_instance_id,
1183 call_type_id,
1184 is_weekend,
1185 period_offset,
1186 calendar_schedule_id,
1187 max_assignments,
1188 assignments_count
1189 )
1190 select
1191 s.linkage_instance_id,
1192 s.call_type_id,
1193 s.is_weekend,
1194 s.period_offset,
1195 s.calendar_schedule_id,
1196 case
1197 when (s.is_weekend = 0) then ct.max_weekday_assignments
1198 else ct.max_weekend_assignments
1199 end as _max_assignments,
1200 sum(1) as assignments_count
1201 from buffer_schedules_926417780 as s
1202 inner join call_types as ct
1203 on s.call_type_id = ct.id
1204 and ct.active = 1
1205 and (
1206 ct.max_weekday_assignments is not null
1207 or ct.max_weekend_assignments is not null
1208 )
1209 where s.linkage_instance_id >= 0
1210 and s.staff_id is null
1211 group by
1212 s.linkage_instance_id,
1213 s.call_type_id,
1214 s.is_weekend,
1215 s.period_offset,
1216 s.calendar_schedule_id,
1217 _max_assignments
1218 having _max_assignments >= 0
1219 order by null
1220
1221INFO : (05-23 07:16:21) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0706
1222 (142.9ms) insert into buffer_call_type_assignments_926417780 (
1223 staff_id,
1224 call_type_id,
1225 is_weekend,
1226 period_offset,
1227 calendar_schedule_id,
1228 assignments_count
1229 )
1230 select
1231 sc.staff_id,
1232 sc.call_type_id,
1233 sc.weekend_on as is_weekend,
1234 p.period_offset,
1235 p.calendar_schedule_id,
1236 sum(1) as assignments_count
1237 from buffer_periods_926417780 as p
1238 inner join call_types as ct
1239 on p.calendar_schedule_id = ct.calendar_schedule_id
1240 and ct.active = 1
1241 and (
1242 ct.max_weekday_assignments is not null
1243 or ct.max_weekend_assignments is not null
1244 )
1245 inner join schedules as sc
1246 on ct.id = sc.call_type_id
1247 and sc.schedule_on between p.start_date and p.end_date
1248 and sc.staff_id is not null
1249 where p.should_be_evaluated = 1
1250 group by
1251 sc.staff_id,
1252 sc.call_type_id,
1253 sc.weekend_on,
1254 p.period_offset,
1255 p.calendar_schedule_id
1256 order by null
1257
1258INFO : (05-23 07:16:21) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0707
1259INFO : (05-23 07:16:21) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0708
1260 (144.8ms) insert into buffer_linkage_assigned_days_926417780 (
1261 linkage_instance_id,
1262 period_offset,
1263 week_number,
1264 schedule_on,
1265 is_weekend
1266 )
1267 select distinct
1268 sc.linkage_instance_id,
1269 sc.period_offset,
1270 sc.week_number,
1271 sc.schedule_on,
1272 sc.is_weekend
1273 from
1274 buffer_schedules_926417780 as sc
1275 inner join call_types as ct
1276 on sc.call_type_id = ct.id
1277 and ct.unassigned_day = 0
1278 where
1279 sc.linkage_instance_id >= 0
1280 and sc.staff_id is null
1281 order by null
1282
1283INFO : (05-23 07:16:21) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0709
1284INFO : (05-23 07:16:21) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query07_10_AL
1285 (143.8ms) insert into buffer_assignment_limits_926417780 (
1286 id,
1287 name,
1288 calendar_schedule_id,
1289 all_current_schedule_assignments,
1290 all_other_schedule_assignments,
1291 all_staff_types,
1292 considered_days_of_week_mask,
1293 limit_type,
1294 limit_subtypes_mask,
1295 timeframe_type,
1296 max_per_period_limit,
1297 max_weekly_limit,
1298 max_rolling_period,
1299 max_rolling_limit,
1300 max_consecutive_limit
1301 )
1302 select
1303 id,
1304 name,
1305 calendar_schedule_id,
1306 all_current_schedule_assignments,
1307 all_other_schedule_assignments,
1308 all_staff_types,
1309 considered_days_of_week_mask,
1310 0 /* assignments */ as limit_type,
1311 (
1312 (coalesce(max_per_period_limit, -1) >= 0) * 1 +
1313 (coalesce(max_weekly_limit, -1) >= 0) * 2 +
1314 (
1315 coalesce(max_rolling_period, -1) > 0
1316 and coalesce(max_rolling_limit, -1) >= 0
1317 ) * 4 +
1318 (coalesce(max_consecutive_limit, -1) >= 0) * 8
1319 ) as limit_subtypes_mask,
1320 timeframe_type,
1321 max_per_period_limit,
1322 max_weekly_limit,
1323 max_rolling_period,
1324 max_rolling_limit,
1325 max_consecutive_limit
1326 from assignment_limits
1327 where
1328 client_id = 6
1329 and active = 1
1330 and (
1331 max_per_period_limit >= 0
1332 or max_weekly_limit >= 0
1333 or (
1334 max_rolling_period > 0
1335 and max_rolling_limit >= 0
1336 )
1337 or max_consecutive_limit >= 0
1338 )
1339 order by null
1340
1341 (162.0ms) insert into buffer_assignment_limit_call_types_926417780 (
1342 assignment_limit_id,
1343 call_type_id
1344 )
1345 select
1346 al.id as assignment_limit_id,
1347 ct.id as call_type_id
1348 from
1349 buffer_assignment_limits_926417780 as al
1350 inner join assignment_limit_call_types as alct
1351 on al.id = alct.assignment_limit_id
1352 inner join call_types as ct
1353 on alct.call_type_id = ct.id
1354 and ct.active = 1
1355 union
1356 select
1357 al.id as assignment_limit_id,
1358 ct.id as call_type_id
1359 from
1360 buffer_assignment_limits_926417780 as al
1361 inner join call_types as ct
1362 on ct.client_id = 6
1363 and ct.active = 1
1364 and al.calendar_schedule_id = ct.calendar_schedule_id
1365 where al.all_current_schedule_assignments = 1
1366 union
1367 select
1368 al.id as assignment_limit_id,
1369 ct.id as call_type_id
1370 from
1371 buffer_assignment_limits_926417780 as al
1372 inner join call_types as ct
1373 on ct.client_id = 6
1374 and ct.active = 1
1375 and al.calendar_schedule_id != ct.calendar_schedule_id
1376 where al.all_other_schedule_assignments = 1
1377 order by null
1378
1379 (147.2ms) insert into buffer_assignment_limit_staffs_926417780 (
1380 assignment_limit_id,
1381 staff_id
1382 )
1383 select
1384 al.id as assignment_limit_id,
1385 s.id as staff_id
1386 from
1387 buffer_assignment_limits_926417780 as al
1388 inner join assignment_limit_staffs as als
1389 on al.id = als.assignment_limit_id
1390 inner join staffs as s
1391 on als.staff_id = s.id
1392 and s.active = 1
1393 and s.take_call = 1
1394 and s.auto_populatable = 1
1395 union
1396 select
1397 al.id as assignment_limit_id,
1398 s.id as staff_id
1399 from
1400 buffer_assignment_limits_926417780 as al
1401 inner join staffs as s
1402 on s.client_id = 6
1403 and s.active = 1
1404 and s.take_call = 1
1405 and s.auto_populatable = 1
1406 left join assignment_limit_staffs as als
1407 on al.id = als.assignment_limit_id
1408 where als.id is null
1409 order by null
1410
1411 (144.0ms) insert into buffer_assignment_limit_staff_types_926417780 (
1412 assignment_limit_id,
1413 staff_type_id
1414 )
1415 select
1416 al.id as assignment_limit_id,
1417 st.id as staff_type_id
1418 from
1419 buffer_assignment_limits_926417780 as al
1420 inner join assignment_limit_staff_types as alst
1421 on al.id = alst.assignment_limit_id
1422 inner join staff_types as st
1423 on alst.staff_type_id = st.id
1424 and st.active = 1
1425 union
1426 select
1427 al.id as assignment_limit_id,
1428 st.id as staff_type_id
1429 from
1430 buffer_assignment_limits_926417780 as al
1431 inner join staff_types as st
1432 on st.client_id = 6
1433 and st.active = 1
1434 where al.all_staff_types = 1
1435 order by null
1436
1437INFO : (05-23 07:16:21) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
1438 (146.1ms) insert into buffer_assignment_limit_periods_926417780 (
1439 assignment_limit_id,
1440 limit_type,
1441 limit_subtype_mask,
1442 limit_value,
1443 start_date,
1444 end_date
1445 )
1446 select
1447 t1.assignment_limit_id,
1448 t1.limit_type,
1449 t1.limit_subtype_mask,
1450 t1.limit_value,
1451 min(b.start_date) as start_date,
1452 max(b.end_date) as end_date
1453 from
1454 (
1455 select
1456 al.id as assignment_limit_id,
1457 al.limit_type,
1458 1 /* per period */ as limit_subtype_mask,
1459 al.max_per_period_limit as limit_value,
1460 al.timeframe_type as period_length,
1461 b.block_schedule_id,
1462 b.display_order as block_position,
1463 case
1464 /* calendar months */
1465 when (bs.blocks_type = 0) then
1466 (year(b.start_date) * 12 + month(b.start_date) - pt.begin_period) mod pt.timeframe_type
1467 /* custom blocks */
1468 else
1469 b.display_order
1470 end as position_inside_period
1471 from
1472 (
1473 select distinct
1474 call_type_id,
1475 day_of_week_mask,
1476 block_id
1477 from buffer_schedules_926417780
1478 where should_be_evaluated = 1
1479 order by null
1480 ) as sc
1481 inner join buffer_assignment_limit_call_types_926417780 as alct
1482 on sc.call_type_id = alct.call_type_id
1483 inner join buffer_assignment_limits_926417780 as al
1484 on alct.assignment_limit_id = al.id
1485 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1486 and (al.limit_subtypes_mask & 1 /* per period */) > 0
1487 inner join blocks as b
1488 on sc.block_id = b.id
1489 inner join block_schedules as bs
1490 on b.block_schedule_id = bs.id
1491 inner join population_timeframes as pt
1492 on pt.client_id = 6
1493 and al.timeframe_type = pt.timeframe_type
1494 group by
1495 al.id,
1496 al.limit_type,
1497 al.max_per_period_limit,
1498 al.timeframe_type,
1499 b.block_schedule_id,
1500 b.display_order,
1501 position_inside_period
1502 order by null
1503 ) as t1
1504 inner join blocks as b
1505 on t1.block_schedule_id = b.block_schedule_id
1506 and b.display_order /* block_position */
1507 between (t1.block_position - t1.position_inside_period)
1508 and ((t1.block_position - t1.position_inside_period) + (t1.period_length - 1))
1509 group by
1510 t1.assignment_limit_id,
1511 t1.limit_type,
1512 t1.limit_subtype_mask,
1513 t1.limit_value
1514 order by null
1515
1516INFO : (05-23 07:16:22) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
1517 (144.9ms) /* AssignmentLimitsPerWeek */
1518 insert into buffer_assignment_limit_periods_926417780 (
1519 assignment_limit_id,
1520 limit_type,
1521 limit_subtype_mask,
1522 limit_value,
1523 start_date,
1524 end_date
1525 )
1526 select
1527 al.id as assignment_limit_id,
1528 al.limit_type,
1529 2 /* per week */ as limit_subtype_mask,
1530 al.max_weekly_limit as limit_value,
1531 case
1532 when (cl.start_of_week = 0) then
1533 date_add(sc.schedule_on, interval (1 - dayofweek(sc.schedule_on)) day)
1534 else
1535 date_add(sc.schedule_on, interval (0 - weekday(sc.schedule_on)) day)
1536 end as _start_date,
1537 case
1538 when (cl.start_of_week = 0) then
1539 date_add(sc.schedule_on, interval (7 - dayofweek(sc.schedule_on)) day)
1540 else
1541 date_add(sc.schedule_on, interval (6 - weekday(sc.schedule_on)) day)
1542 end as _end_date
1543 from
1544 buffer_schedules_926417780 as sc
1545 inner join buffer_assignment_limit_call_types_926417780 as alct
1546 on sc.call_type_id = alct.call_type_id
1547 inner join buffer_assignment_limits_926417780 as al
1548 on alct.assignment_limit_id = al.id
1549 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1550 and (al.limit_subtypes_mask & 2 /* per week */) > 0
1551 inner join clients as cl
1552 on cl.id = 6
1553 where sc.should_be_evaluated = 1
1554 group by
1555 al.id,
1556 al.limit_type,
1557 al.max_weekly_limit,
1558 _start_date,
1559 _end_date
1560 order by null
1561
1562INFO : (05-23 07:16:22) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
1563 (150.1ms) /* AssignmentLimitsRolling */
1564 insert into buffer_assignment_limit_periods_926417780 (
1565 assignment_limit_id,
1566 limit_type,
1567 limit_subtype_mask,
1568 limit_value,
1569 start_date,
1570 end_date
1571 )
1572 select
1573 al.id as assignment_limit_id,
1574 al.limit_type,
1575 4 /* rolling */ as limit_subtype_mask,
1576 al.max_rolling_limit as limit_value,
1577 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as _start_date,
1578 date_add(sc.schedule_on, interval (al.max_rolling_period - ns.item_number) day) as _end_date
1579 from
1580 buffer_schedules_926417780 as sc
1581 inner join buffer_assignment_limit_call_types_926417780 as alct
1582 on sc.call_type_id = alct.call_type_id
1583 inner join buffer_assignment_limits_926417780 as al
1584 on alct.assignment_limit_id = al.id
1585 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1586 and (al.limit_subtypes_mask & 4 /* rolling */) > 0
1587 inner join numeric_sequence as ns
1588 on ns.item_number between 1 and al.max_rolling_period
1589 where sc.should_be_evaluated = 1
1590 group by
1591 al.id,
1592 al.limit_type,
1593 al.max_rolling_limit,
1594 _start_date,
1595 _end_date
1596 order by null
1597
1598INFO : (05-23 07:16:22) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
1599 (151.5ms) /* AssignmentLimitsConsecutive */
1600 insert into buffer_assignment_limit_periods_926417780 (
1601 assignment_limit_id,
1602 limit_type,
1603 limit_subtype_mask,
1604 limit_value,
1605 start_date,
1606 end_date
1607 )
1608 select
1609 al.id as assignment_limit_id,
1610 al.limit_type,
1611 8 /* consecutive */ as limit_subtype_mask,
1612 al.max_consecutive_limit as limit_value,
1613 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as _start_date,
1614 date_add(sc.schedule_on, interval ((al.max_consecutive_limit + 1) - ns.item_number) day) as _end_date
1615 from
1616 buffer_schedules_926417780 as sc
1617 inner join buffer_assignment_limit_call_types_926417780 as alct
1618 on sc.call_type_id = alct.call_type_id
1619 inner join buffer_assignment_limits_926417780 as al
1620 on alct.assignment_limit_id = al.id
1621 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1622 and (al.limit_subtypes_mask & 8 /* consecutive */) > 0
1623 inner join numeric_sequence as ns
1624 on ns.item_number between 1 and (al.max_consecutive_limit + 1)
1625 where sc.should_be_evaluated = 1
1626 group by
1627 al.id,
1628 al.limit_type,
1629 al.max_consecutive_limit,
1630 _start_date,
1631 _end_date
1632 order by null
1633
1634INFO : (05-23 07:16:22) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query08
1635 (141.6ms) insert into buffer_staff_daily_counts_926417780 (
1636 staff_id, schedule_on, linkage_instance_id, slots_count
1637 )
1638 select staff_id, schedule_on, null as linkage_instance_id,
1639 count(1) as slots_count
1640 from buffer_schedules_926417780
1641 where staff_id is not null
1642 and call_type_id > 0 /* VC */
1643 group by staff_id, schedule_on
1644 order by null
1645
1646 (145.7ms) insert into buffer_staff_daily_counts_926417780 (
1647 staff_id, schedule_on, linkage_instance_id, slots_count
1648 )
1649 select staff_id, schedule_on, linkage_instance_id,
1650 count(1) as slots_count
1651 from buffer_schedules_926417780
1652 where staff_id is not null
1653 and call_type_id > 0 /* VC */
1654 group by staff_id, schedule_on, linkage_instance_id
1655 order by null
1656
1657INFO : (05-23 07:16:22) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query085
1658 (296.6ms) insert into buffer_staff_specializations_926417780 (
1659 staff_id, specialization_id
1660 )
1661 select staff_id, specialization_id
1662 from view_staff_specializations
1663 where client_id = 6
1664 order by null
1665
1666 (142.9ms) insert into buffer_specialization_daily_counts_926417780 (
1667 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1668 )
1669 select sp.id as specialization_id,
1670 null as staff_id,
1671 bs_1.schedule_on,
1672 count(distinct st_sp.staff_id) as staffs_scheduled,
1673 sp.max_scheduled,
1674 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
1675 from (
1676 select schedule_on, day_of_week_mask
1677 from buffer_schedules_926417780
1678 where should_be_evaluated = 1
1679 group by schedule_on, day_of_week_mask
1680 order by null
1681 ) as bs_1
1682 inner join specializations as sp
1683 on sp.client_id = 6
1684 and sp.active = 1
1685 inner join buffer_schedules_926417780 as bs_2
1686 on bs_1.schedule_on = bs_2.schedule_on
1687 and bs_2.call_type_id > 0 /* is not VC */
1688 inner join max_applicable_call_types as mact
1689 on sp.id = mact.specialization_id
1690 and bs_2.call_type_id = mact.call_type_id
1691 left join buffer_staff_specializations_926417780 as st_sp
1692 on bs_2.staff_id = st_sp.staff_id
1693 and sp.id = st_sp.specialization_id
1694 group by sp.id, bs_1.schedule_on, sp.max_scheduled
1695 order by null
1696
1697 (310.0ms) insert into buffer_specialization_daily_counts_926417780 (
1698 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1699 )
1700 select sp.id as specialization_id,
1701 st_sp.staff_id,
1702 bs_1.schedule_on,
1703 1 as staffs_scheduled,
1704 sp.max_scheduled,
1705 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
1706 from (
1707 select schedule_on, day_of_week_mask
1708 from buffer_schedules_926417780
1709 where should_be_evaluated = 1
1710 group by schedule_on, day_of_week_mask
1711 order by null
1712 ) as bs_1
1713 inner join specializations as sp
1714 on sp.client_id = 6
1715 and sp.active = 1
1716 inner join buffer_schedules_926417780 as bs_2
1717 on bs_1.schedule_on = bs_2.schedule_on
1718 and bs_2.call_type_id > 0 /* is not VC */
1719 inner join max_applicable_call_types as mact
1720 on sp.id = mact.specialization_id
1721 and bs_2.call_type_id = mact.call_type_id
1722 inner join buffer_staff_specializations_926417780 as st_sp
1723 on bs_2.staff_id = st_sp.staff_id
1724 and sp.id = st_sp.specialization_id
1725 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
1726 order by null
1727
1728INFO : (05-23 07:16:23) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query09
1729 (150.1ms) truncate table buffer_location_privileges_926417780
1730
1731 (146.0ms) insert into buffer_location_privileges_926417780 (
1732 staff_id,
1733 location_id,
1734 begin_date,
1735 end_date
1736 )
1737 select
1738 s.id as staff_id,
1739 l.location_id,
1740 '0000-00-00' as begin_date,
1741 '9999-12-31' as end_date
1742 from (
1743 select l.id as location_id
1744 from
1745 buffer_schedules_926417780 as sc
1746 inner join call_types as ct
1747 on sc.call_type_id = ct.id
1748 and ct.active = 1
1749 inner join locations as l
1750 on ct.location_id = l.id
1751 and l.active = 1
1752 where sc.linkage_instance_id >= 0
1753 and sc.should_be_evaluated = 1
1754 group by l.id
1755 order by null
1756 ) as l
1757 inner join staffs as s
1758 on s.client_id = 6
1759 and s.active = 1
1760 and s.take_call = 1
1761 and s.auto_populatable = 1
1762 left join location_privileges as lp
1763 on s.id = lp.staff_id
1764 where lp.id is null
1765 union
1766 select
1767 lp.staff_id,
1768 lp.location_id,
1769 lp.begin_date,
1770 COALESCE(lp.end_date, '9999-12-31')
1771 from (
1772 select l.id as location_id
1773 from
1774 buffer_schedules_926417780 as sc
1775 inner join call_types as ct
1776 on sc.call_type_id = ct.id
1777 and ct.active = 1
1778 inner join locations as l
1779 on ct.location_id = l.id
1780 and l.active = 1
1781 where sc.linkage_instance_id >= 0
1782 and sc.should_be_evaluated = 1
1783 group by l.id
1784 order by null
1785 ) as l
1786 inner join location_privileges as lp
1787 on l.location_id = lp.location_id
1788 order by null;
1789
1790 (3808.0ms) insert into buffer_static_availabilities_926417780 (
1791 schedule_id,
1792 staff_id,
1793 staff_type_id,
1794 is_primary
1795 )
1796 select
1797 schedules.id as schedule_id,
1798 staffs.id as staff_id,
1799 assign_staff_types.staff_type_id,
1800 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
1801 from
1802 buffer_schedules_926417780 as schedules
1803 /* AvailabilityRule */
1804 inner join call_types
1805 on schedules.call_type_id = call_types.id
1806 and call_types.active = 1
1807 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
1808 inner join staff_call_types
1809 on call_types.id = staff_call_types.call_type_id
1810 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
1811 inner join staffs
1812 on staff_call_types.staff_id = staffs.id
1813 and staffs.active = 1
1814 and staffs.take_call = 1
1815 and staffs.auto_populatable = 1
1816 inner join buffer_location_privileges_926417780 as location_privileges
1817 on staffs.id = location_privileges.staff_id
1818 and call_types.location_id = location_privileges.location_id
1819 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
1820 inner join assign_staff_types
1821 on staffs.id = assign_staff_types.staff_id
1822 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
1823 left join eligible_staff_types
1824 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
1825 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
1826 left join staff_rotations
1827 on staffs.id = staff_rotations.staff_id
1828 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
1829 left join daily_rotations
1830 on staffs.id = daily_rotations.staff_id
1831 and schedules.schedule_on = daily_rotations.schedule_on
1832 left join rotations
1833 on rotations.id = ifnull(daily_rotations.rotation_id, staff_rotations.rotation_id)
1834 and rotations.active = 1
1835 left join rotation_call_types
1836 on rotations.id = rotation_call_types.rotation_id
1837 and staff_call_types.call_type_id = rotation_call_types.call_type_id
1838 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
1839 /* RequiredRotationsRule */
1840 left join required_rotations
1841 on staff_call_types.call_type_id = required_rotations.call_type_id
1842 left join staff_rotations as staff_rotations2
1843 on staffs.id = staff_rotations2.staff_id
1844 and required_rotations.rotation_id = staff_rotations2.rotation_id
1845 and schedules.schedule_on >= staff_rotations2.start_date
1846 /* NoCallRequestsHiddenHardRule */
1847 left join request_settings
1848 on assign_staff_types.staff_type_id = request_settings.staff_type_id
1849 left join requests
1850 on schedules.schedule_on = requests.schedule_on
1851 and staffs.id = requests.staff_id
1852 and requests.request_type = 0 /* type = no_call */
1853 and requests.status in (1, 3) /* status = approved or fulfilled */
1854 and call_types.is_no_call_requestable = 1
1855 left join request_call_types
1856 on requests.id = request_call_types.request_id
1857 and schedules.call_type_id = request_call_types.call_type_id
1858 where
1859 schedules.linkage_instance_id >= 0
1860 and schedules.should_be_evaluated = 1
1861 and (
1862 call_types.all_staff_types = 1
1863 or eligible_staff_types.id is not null
1864 )
1865 and (
1866 rotations.id is null
1867 or rotation_call_types.id is not null
1868 )
1869 and (
1870 requests.id is null
1871 or (
1872 requests.all_call_types = 1
1873 and request_settings.call_only_all_call_switch = 1 /* calls only */
1874 and call_types.assignment_type = 1 /* clinical */
1875 )
1876 or (
1877 requests.all_call_types = 0
1878 and request_call_types.id is null
1879 )
1880 )
1881 group by
1882 schedules.id,
1883 staffs.id,
1884 assign_staff_types.staff_type_id,
1885 is_primary
1886 having
1887 sum(if(required_rotations.id is not null and staff_rotations2.id is null, 1, 0)) = 0 /* RequiredRotationsRule */
1888 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
1889 order by null
1890
1891INFO : (05-23 07:16:27) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query092
1892 (507.2ms) /* RotationPostCallRule */
1893 /* Exclude people and slots when a post-call is not allowed on the first day of a rotation */
1894 delete sa
1895 from buffer_static_availabilities_926417780 as sa
1896 inner join buffer_schedules_926417780 as sc
1897 on sa.schedule_id = sc.id
1898 inner join call_types as ct
1899 on sc.call_type_id = ct.id
1900 and ct.assignment_type = 0 /* call */
1901 inner join staff_rotations as sr
1902 on sa.staff_id = sr.staff_id
1903 and date_add(sc.schedule_on, interval 1 day) = sr.start_date
1904 inner join rotations as r
1905 on sr.rotation_id = r.id
1906 and r.post_call_allowed = 0
1907
1908INFO : (05-23 07:16:28) --> fill: 34 - Scheduler::AutoPopulate::Queries::Query093
1909INFO : (05-23 07:16:28) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query095
1910 (153.2ms) insert into buffer_negative_relations_926417780 (
1911 schedule_id,
1912 affected_by_schedule_id,
1913 staff_id
1914 )
1915 select
1916 b_schedules.id as schedule_id,
1917 b_schedules_negative_link.id as affected_by_schedule_id,
1918 b_schedules_negative_link.staff_id
1919 from
1920 buffer_schedules_926417780 as b_schedules
1921 /* NegativeLinkRule */
1922 inner join negative_links_influences
1923 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1924 and b_schedules.call_type_id = negative_links_influences.source_id
1925 and negative_links_influences.source_type = 'CallType'
1926 and negative_links_influences.target_type = 'CallType'
1927 inner join buffer_schedules_926417780 as b_schedules_negative_link
1928 on date_add(
1929 b_schedules.schedule_on,
1930 interval negative_links_influences.offset day
1931 ) = b_schedules_negative_link.schedule_on
1932 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
1933 and b_schedules.id != b_schedules_negative_link.id
1934 where
1935 b_schedules.linkage_instance_id >= 0
1936 and b_schedules.should_be_evaluated = 1
1937 order by null
1938
1939 (198.0ms) insert into buffer_negative_relations_926417780 (
1940 schedule_id,
1941 affected_by_schedule_id,
1942 staff_id
1943 )
1944 select
1945 b_schedules.id as schedule_id,
1946 b_schedules_negative_link.id as affected_by_schedule_id,
1947 b_schedules_negative_link.staff_id
1948 from
1949 buffer_schedules_926417780 as b_schedules
1950 /* NegativeLinkRule */
1951 inner join negative_links_influences
1952 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1953 and b_schedules.call_type_id = negative_links_influences.source_id
1954 and negative_links_influences.source_type = 'CallType'
1955 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1956 inner join buffer_schedules_926417780 as b_schedules_negative_link
1957 on date_add(
1958 b_schedules.schedule_on,
1959 interval negative_links_influences.offset day
1960 ) = b_schedules_negative_link.schedule_on
1961 and (-1 * negative_links_influences.target_id) = b_schedules_negative_link.call_type_id
1962 and b_schedules.id != b_schedules_negative_link.id
1963 where
1964 b_schedules.linkage_instance_id >= 0
1965 and b_schedules.should_be_evaluated = 1
1966 order by null
1967
1968 (144.7ms) delete from buffer_schedules_926417780
1969 where call_type_id < 0 /* vacations and non-clinicals */
1970
1971INFO : (05-23 07:16:28) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query097
1972 (433.5ms) insert into buffer_staff_assigned_days_926417780 (
1973 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
1974 )
1975 select staffs.staff_id,
1976 if(days.within_period = 1, days.period_offset, null) as period_offset,
1977 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
1978 days.schedule_on,
1979 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
1980 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
1981 from (
1982 select distinct staff_id
1983 from buffer_static_availabilities_926417780
1984 order by null
1985 ) as staffs
1986 inner join (
1987 select p.period_offset,
1988 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,
1989 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
1990 from buffer_periods_926417780 as p
1991 inner join (
1992 select 0 as digit union select 1 union select 2 union select 3 union select 4
1993 union select 5 union select 6 union select 7 union select 8 union select 9
1994 ) as digits_1
1995 inner join (
1996 select 0 as digit union select 1 union select 2 union select 3 union select 4
1997 union select 5 union select 6 union select 7 union select 8 union select 9
1998 ) as digits_2
1999 inner join (
2000 select 0 as digit union select 1 union select 2 union select 3 union select 4
2001 union select 5 union select 6 union select 7 union select 8 union select 9
2002 ) as digits_3
2003 where p.calendar_schedule_id = 6
2004 and p.should_be_evaluated = 1
2005 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
2006 order by null
2007 ) as days
2008 inner join (
2009 select min(start_date) as start_date, max(end_date) as end_date
2010 from buffer_periods_926417780
2011 where calendar_schedule_id = 6
2012 and should_be_evaluated = 1
2013 order by null
2014 ) as limits
2015 inner join clients
2016 on clients.id = 6
2017 left join (
2018 select sc.staff_id, sc.schedule_on
2019 from buffer_periods_926417780 as p
2020 inner join schedules as sc
2021 on sc.client_id = 6
2022 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2023 inner join call_types as ct
2024 on sc.call_type_id = ct.id
2025 and ct.unassigned_day = 0
2026 where p.calendar_schedule_id = 6
2027 and p.should_be_evaluated = 1
2028 group by sc.staff_id, sc.schedule_on
2029 order by null
2030 ) as schedules
2031 on staffs.staff_id = schedules.staff_id
2032 and days.schedule_on = schedules.schedule_on
2033 left join (
2034 select v.staff_id, v.schedule_on
2035 from buffer_periods_926417780 as p
2036 inner join vacations as v
2037 on v.client_id = 6
2038 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2039 and v.status IN (3)
2040 inner join non_clinical_types as nct
2041 on v.non_clinical_type_id = nct.id
2042 and nct.active = 1
2043 and nct.unassigned_day = 0
2044 where p.calendar_schedule_id = 6
2045 and p.should_be_evaluated = 1
2046 group by v.staff_id, v.schedule_on
2047 order by null
2048 ) as vacations
2049 on staffs.staff_id = vacations.staff_id
2050 and days.schedule_on = vacations.schedule_on
2051 where days.within_period = 1
2052 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
2053 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
2054 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
2055 )
2056 order by null
2057
2058INFO : (05-23 07:16:29) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query098
2059 (152.1ms) insert into buffer_combined_availabilities_926417780 (
2060 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
2061 )
2062 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
2063 sc.linkage_instance_id, 0 as has_violations
2064 from buffer_schedules_926417780 as sc
2065 /* Static availability */
2066 inner join buffer_static_availabilities_926417780 as sa
2067 on sc.id = sa.schedule_id
2068 inner join buffer_linkages_926417780 as l
2069 on sc.linkage_instance_id = l.linkage_instance_id
2070 where sc.should_be_evaluated = 1
2071 and l.has_conflicts = 0
2072
2073 (159.8ms) insert into buffer_schedule_linkages_926417780 (
2074 staff_id, linkage_instance_id, slots_count
2075 )
2076 select staff_id, linkage_instance_id, count(1) as slots_count
2077 from buffer_schedules_926417780 use index (ix_buffer_schedules_composite_2)
2078 where staff_id is not null
2079 and linkage_instance_id >= 0
2080 group by staff_id, linkage_instance_id
2081 order by null
2082
2083INFO : (05-23 07:16:29) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
2084 (271.8ms) insert into buffer_linkage_assignment_limits_926417780 (
2085 staff_id,
2086 linkage_instance_id,
2087 assignment_limit_id,
2088 period_id,
2089 consecutive_days_mask,
2090 linkage_value
2091 )
2092 select
2093 ca.staff_id,
2094 ca.linkage_instance_id,
2095 al.id as assignment_limit_id,
2096 alp.id as period_id,
2097 case
2098 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2099 bit_or(1 << datediff(sc.schedule_on, alp.start_date))
2100 else 0
2101 end as consecutive_days_mask,
2102 count(sc.id) as linkage_value
2103 from
2104 buffer_combined_availabilities_926417780 as ca
2105 inner join buffer_schedules_926417780 as sc
2106 on ca.schedule_id = sc.id
2107 inner join buffer_assignment_limit_call_types_926417780 as alct
2108 on sc.call_type_id = alct.call_type_id
2109 inner join buffer_assignment_limits_926417780 as al
2110 on alct.assignment_limit_id = al.id
2111 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2112 inner join buffer_assignment_limit_staffs_926417780 as als
2113 on al.id = als.assignment_limit_id
2114 and ca.staff_id = als.staff_id
2115 inner join buffer_assignment_limit_staff_types_926417780 as alst
2116 on al.id = alst.assignment_limit_id
2117 and ca.staff_type_id = alst.staff_type_id
2118 inner join buffer_assignment_limit_periods_926417780 as alp
2119 on al.id = alp.assignment_limit_id
2120 and sc.schedule_on between alp.start_date and alp.end_date
2121 group by
2122 ca.staff_id,
2123 ca.linkage_instance_id,
2124 al.id,
2125 alp.id
2126 order by null
2127
2128INFO : (05-23 07:16:29) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
2129 (23163.7ms) insert into buffer_staff_assignment_limits_926417780 (
2130 staff_id,
2131 assignment_limit_id,
2132 period_id,
2133 consecutive_days_mask,
2134 current_value
2135 )
2136 select
2137 ca.staff_id,
2138 al.id as assignment_limit_id,
2139 alp.id as period_id,
2140 case
2141 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2142 bit_or(1 << datediff(sc.schedule_on, alp.start_date))
2143 else 0
2144 end as consecutive_days_mask,
2145 count(sc.id) as current_value
2146 from
2147 (
2148 select
2149 staff_id,
2150 linkage_instance_id
2151 from buffer_combined_availabilities_926417780
2152 group by
2153 staff_id,
2154 linkage_instance_id
2155 ) as ca
2156 inner join buffer_linkage_assignment_limits_926417780 as lal
2157 on ca.staff_id = lal.staff_id
2158 and ca.linkage_instance_id = lal.linkage_instance_id
2159 inner join buffer_assignment_limit_staffs_926417780 as als
2160 on lal.assignment_limit_id = als.assignment_limit_id
2161 and ca.staff_id = als.staff_id
2162 inner join buffer_assignment_limit_periods_926417780 as alp
2163 on lal.period_id = alp.id
2164 inner join buffer_assignment_limits_926417780 as al
2165 on lal.assignment_limit_id = al.id
2166 inner join schedules as sc
2167 on ca.staff_id = sc.staff_id
2168 and sc.schedule_on between alp.start_date and alp.end_date
2169 and ((1 << (sc.dayofweek_on - 1)) & al.considered_days_of_week_mask) > 0
2170 inner join buffer_assignment_limit_call_types_926417780 as alct
2171 on lal.assignment_limit_id = alct.assignment_limit_id
2172 and sc.call_type_id = alct.call_type_id
2173 inner join assign_staff_types as ast
2174 on ca.staff_id = ast.staff_id
2175 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2176 inner join buffer_assignment_limit_staff_types_926417780 as alst
2177 on lal.assignment_limit_id = alst.assignment_limit_id
2178 and ast.staff_type_id = alst.staff_type_id
2179 group by
2180 ca.staff_id,
2181 al.id,
2182 alp.id
2183 order by null
2184
2185INFO : (05-23 07:16:52) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_12_Points
2186INFO : (05-23 07:16:52) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
2187INFO : (05-23 07:16:52) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query10
2188 RuleSet Load (140.8ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1530 LIMIT 1
2189 Client Load (309.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
2190 (142.4ms) select
2191 coalesce(max(
2192 case
2193 when (ct.max_weekday_assignments > 0) then 1
2194 else 0
2195 end
2196 ), 0) as weekday_flag,
2197 coalesce(max(
2198 case
2199 when (ct.max_weekend_assignments > 0) then 1
2200 else 0
2201 end
2202 ), 0) as weekend_flag
2203 from
2204 buffer_auto_populate_slots_926417780 as aps
2205 inner join call_types as ct
2206 on aps.call_type_id = ct.id
2207
2208 (141.7ms) /* CallOverlapRule */
2209 delete ca
2210 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2211 inner join buffer_schedules_926417780 as sc
2212 on ca.schedule_id = sc.id
2213 left join buffer_staff_daily_counts_926417780 as sdc use index (ix_buffer_staff_daily_counts_composite_1)
2214 on ca.staff_id = sdc.staff_id
2215 and sc.schedule_on = sdc.schedule_on
2216 and sdc.linkage_instance_id is null
2217 left join buffer_staff_daily_counts_926417780 as sdc_2 use index (ix_buffer_staff_daily_counts_composite_1)
2218 on ca.staff_id = sdc_2.staff_id
2219 and sc.schedule_on = sdc_2.schedule_on
2220 and sc.linkage_instance_id = sdc_2.linkage_instance_id
2221 where (ifnull(sdc.slots_count, 0) - ifnull(sdc_2.slots_count, 0) + 1) > 1
2222
2223 (145.5ms) /* NegativeLinkRule */
2224 delete ca
2225 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2226 inner join buffer_negative_relations_926417780 as nr use index (ix_buffer_negative_relations_composite_1)
2227 on ca.schedule_id = nr.schedule_id
2228 and ca.staff_id = nr.staff_id
2229
2230 (144.1ms) /* SpecializationOverlapRule */
2231 delete ca
2232 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2233 inner join buffer_schedules_926417780 as sc
2234 on ca.schedule_id = sc.id
2235 inner join buffer_staff_specializations_926417780 as ssp
2236 on ca.staff_id = ssp.staff_id
2237 inner join max_applicable_call_types as mact
2238 on ssp.specialization_id = mact.specialization_id
2239 and sc.call_type_id = mact.call_type_id
2240 inner join buffer_specialization_daily_counts_926417780 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2241 on ssp.specialization_id = sdc.specialization_id
2242 and sc.schedule_on = sdc.schedule_on
2243 and sdc.staff_id is null
2244 left join buffer_specialization_daily_counts_926417780 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2245 on ssp.specialization_id = sdc_2.specialization_id
2246 and sc.schedule_on = sdc_2.schedule_on
2247 and ca.staff_id = sdc_2.staff_id
2248 where sdc.should_be_evaluated = 1
2249 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2250
2251 (147.4ms) /* BrokenLinkageRule */
2252 truncate table buffer_combined_availability_linkages_926417780
2253
2254 (149.0ms) insert into buffer_combined_availability_linkages_926417780 (
2255 staff_id,
2256 linkage_instance_id,
2257 slots_count,
2258 is_primary,
2259 has_violations
2260 )
2261 select
2262 staff_id,
2263 linkage_instance_id,
2264 count(1) as slots_count,
2265 min(is_primary) as is_primary,
2266 max(has_violations) as has_violations
2267 from buffer_combined_availabilities_926417780 use index (ix_buffer_combined_availabilities_composite_2)
2268 group by
2269 staff_id,
2270 linkage_instance_id
2271 order by null
2272
2273 (169.2ms) delete ca
2274 from
2275 buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2276 inner join buffer_schedules_926417780 as sc
2277 on ca.schedule_id = sc.id
2278 inner join buffer_linkages_926417780 as l
2279 on sc.linkage_instance_id = l.linkage_instance_id
2280 inner join buffer_combined_availability_linkages_926417780 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2281 on ca.staff_id = ls_1.staff_id
2282 and sc.linkage_instance_id = ls_1.linkage_instance_id
2283 left join buffer_schedule_linkages_926417780 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2284 on ca.staff_id = ls_2.staff_id
2285 and sc.linkage_instance_id = ls_2.linkage_instance_id
2286 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2287
2288INFO : (05-23 07:16:54) --> fill: 43 - Scheduler::AutoPopulate::Queries::QueryScore02
2289INFO : (05-23 07:16:54) --> fill: 44 - Scheduler::AutoPopulate::Queries::QueryScore03
2290INFO : (05-23 07:16:54) --> fill: 45 - Scheduler::AutoPopulate::Queries::QueryScore04
2291INFO : (05-23 07:16:54) --> fill: 46 - Scheduler::AutoPopulate::Queries::Query11
2292 (142.9ms) update buffer_schedules_926417780 as sc
2293 left join buffer_combined_availabilities_926417780 as ca
2294 on sc.id = ca.schedule_id
2295 set sc.should_be_evaluated = 0,
2296 sc.should_be_populated = 0
2297 where sc.should_be_evaluated = 1
2298 and ca.id is null
2299
2300INFO : (05-23 07:16:54) --> fill: 47 - Scheduler::AutoPopulate::Queries::DropTables
2301INFO : (05-23 07:16:54) Setting up lists...
2302 (156.2ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
2303 from buffer_schedules_926417780 as b_schedules
2304 left join schedules
2305 on schedules.client_id = 6
2306 and schedules.call_type_id = b_schedules.call_type_id
2307 and schedules.schedule_on = b_schedules.schedule_on
2308 where b_schedules.linkage_instance_id != -1
2309 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
2310
2311INFO : (05-23 07:16:54) Begin to populate. Pre-set: 0. Unscheduled: 128.
2312INFO : (05-23 07:16:54) plan unit: [id:201740] [2017-10-01 - OR]
2313INFO : (05-23 07:16:54) plan unit: [id:201741] [2017-10-08 - OR]
2314INFO : (05-23 07:16:54) plan unit: [id:201742] [2017-10-15 - OR]
2315INFO : (05-23 07:16:54) plan unit: [id:201743] [2017-10-22 - OR]
2316INFO : (05-23 07:16:54) plan unit: [id:201744] [2017-10-29 - OR]
2317INFO : (05-23 07:16:54) plan unit: [id:1201740] [2017-10-01 - OB]
2318INFO : (05-23 07:16:54) plan unit: [id:1201741] [2017-10-08 - OB]
2319INFO : (05-23 07:16:54) plan unit: [id:1201742] [2017-10-15 - OB]
2320INFO : (05-23 07:16:54) plan unit: [id:1201743] [2017-10-22 - OB]
2321INFO : (05-23 07:16:54) plan unit: [id:1201744] [2017-10-29 - OB]
2322INFO : (05-23 07:16:54) plan unit: [id:2201740] [2017-10-02 - OR]
2323INFO : (05-23 07:16:54) plan unit: [id:2201741] [2017-10-09 - OR]
2324INFO : (05-23 07:16:54) plan unit: [id:2201742] [2017-10-16 - OR]
2325INFO : (05-23 07:16:54) plan unit: [id:2201743] [2017-10-23 - OR]
2326INFO : (05-23 07:16:54) plan unit: [id:2201744] [2017-10-30 - OR]
2327INFO : (05-23 07:16:54) plan unit: [id:3201740] [2017-10-02 - BU]
2328INFO : (05-23 07:16:54) plan unit: [id:3201741] [2017-10-09 - BU]
2329INFO : (05-23 07:16:54) plan unit: [id:3201742] [2017-10-16 - BU]
2330INFO : (05-23 07:16:54) plan unit: [id:3201743] [2017-10-23 - BU]
2331INFO : (05-23 07:16:54) plan unit: [id:3201744] [2017-10-30 - BU]
2332INFO : (05-23 07:16:54) plan unit: [id:4201740] [2017-10-02 - OB]
2333INFO : (05-23 07:16:54) plan unit: [id:4201741] [2017-10-09 - OB]
2334INFO : (05-23 07:16:54) plan unit: [id:4201742] [2017-10-16 - OB]
2335INFO : (05-23 07:16:54) plan unit: [id:4201743] [2017-10-23 - OB]
2336INFO : (05-23 07:16:54) plan unit: [id:4201744] [2017-10-30 - OB]
2337INFO : (05-23 07:16:54) plan unit: [id:5201740] [2017-10-02 - GI1]
2338INFO : (05-23 07:16:54) plan unit: [id:5201741] [2017-10-09 - GI1]
2339INFO : (05-23 07:16:54) plan unit: [id:5201742] [2017-10-16 - GI1]
2340INFO : (05-23 07:16:54) plan unit: [id:5201743] [2017-10-23 - GI1]
2341INFO : (05-23 07:16:54) plan unit: [id:5201744] [2017-10-30 - GI1]
2342INFO : (05-23 07:16:54) plan unit: [id:6201740] [2017-10-02 - PR]
2343INFO : (05-23 07:16:54) plan unit: [id:6201741] [2017-10-09 - PR]
2344INFO : (05-23 07:16:54) plan unit: [id:6201742] [2017-10-16 - PR]
2345INFO : (05-23 07:16:54) plan unit: [id:6201743] [2017-10-23 - PR]
2346INFO : (05-23 07:16:54) plan unit: [id:6201744] [2017-10-30 - PR]
2347INFO : (05-23 07:16:54) plan unit: [id:11201740] [2017-10-03 - OR]
2348INFO : (05-23 07:16:54) plan unit: [id:11201741] [2017-10-10 - OR]
2349INFO : (05-23 07:16:54) plan unit: [id:11201742] [2017-10-17 - OR]
2350INFO : (05-23 07:16:54) plan unit: [id:11201743] [2017-10-24 - OR]
2351INFO : (05-23 07:16:54) plan unit: [id:11201744] [2017-10-31 - OR]
2352INFO : (05-23 07:16:54) plan unit: [id:12201740] [2017-10-03 - BU]
2353INFO : (05-23 07:16:54) plan unit: [id:12201741] [2017-10-10 - BU]
2354INFO : (05-23 07:16:54) plan unit: [id:12201742] [2017-10-17 - BU]
2355INFO : (05-23 07:16:54) plan unit: [id:12201743] [2017-10-24 - BU]
2356INFO : (05-23 07:16:54) plan unit: [id:12201744] [2017-10-31 - BU]
2357INFO : (05-23 07:16:54) plan unit: [id:13201740] [2017-10-03 - OB]
2358INFO : (05-23 07:16:54) plan unit: [id:13201741] [2017-10-10 - OB]
2359INFO : (05-23 07:16:54) plan unit: [id:13201742] [2017-10-17 - OB]
2360INFO : (05-23 07:16:54) plan unit: [id:13201743] [2017-10-24 - OB]
2361INFO : (05-23 07:16:54) plan unit: [id:13201744] [2017-10-31 - OB]
2362INFO : (05-23 07:16:54) plan unit: [id:14201740] [2017-10-03 - GI1]
2363INFO : (05-23 07:16:54) plan unit: [id:14201741] [2017-10-10 - GI1]
2364INFO : (05-23 07:16:54) plan unit: [id:14201742] [2017-10-17 - GI1]
2365INFO : (05-23 07:16:54) plan unit: [id:14201743] [2017-10-24 - GI1]
2366INFO : (05-23 07:16:54) plan unit: [id:14201744] [2017-10-31 - GI1]
2367INFO : (05-23 07:16:54) plan unit: [id:15201740] [2017-10-03 - PR]
2368INFO : (05-23 07:16:54) plan unit: [id:15201741] [2017-10-10 - PR]
2369INFO : (05-23 07:16:54) plan unit: [id:15201742] [2017-10-17 - PR]
2370INFO : (05-23 07:16:54) plan unit: [id:15201743] [2017-10-24 - PR]
2371INFO : (05-23 07:16:54) plan unit: [id:15201744] [2017-10-31 - PR]
2372INFO : (05-23 07:16:54) plan unit: [id:20201740] [2017-10-04 - OR]
2373INFO : (05-23 07:16:54) plan unit: [id:20201741] [2017-10-11 - OR]
2374INFO : (05-23 07:16:54) plan unit: [id:20201742] [2017-10-18 - OR]
2375INFO : (05-23 07:16:54) plan unit: [id:20201743] [2017-10-25 - OR]
2376INFO : (05-23 07:16:54) plan unit: [id:21201740] [2017-10-04 - BU]
2377INFO : (05-23 07:16:54) plan unit: [id:21201741] [2017-10-11 - BU]
2378INFO : (05-23 07:16:54) plan unit: [id:21201742] [2017-10-18 - BU]
2379INFO : (05-23 07:16:54) plan unit: [id:21201743] [2017-10-25 - BU]
2380INFO : (05-23 07:16:54) plan unit: [id:22201740] [2017-10-04 - OB]
2381INFO : (05-23 07:16:54) plan unit: [id:22201741] [2017-10-11 - OB]
2382INFO : (05-23 07:16:54) plan unit: [id:22201742] [2017-10-18 - OB]
2383INFO : (05-23 07:16:54) plan unit: [id:22201743] [2017-10-25 - OB]
2384INFO : (05-23 07:16:54) plan unit: [id:23201740] [2017-10-04 - GI1]
2385INFO : (05-23 07:16:54) plan unit: [id:23201741] [2017-10-11 - GI1]
2386INFO : (05-23 07:16:54) plan unit: [id:23201742] [2017-10-18 - GI1]
2387INFO : (05-23 07:16:54) plan unit: [id:23201743] [2017-10-25 - GI1]
2388INFO : (05-23 07:16:54) plan unit: [id:24201740] [2017-10-04 - PR]
2389INFO : (05-23 07:16:54) plan unit: [id:24201741] [2017-10-11 - PR]
2390INFO : (05-23 07:16:54) plan unit: [id:24201742] [2017-10-18 - PR]
2391INFO : (05-23 07:16:54) plan unit: [id:24201743] [2017-10-25 - PR]
2392INFO : (05-23 07:16:54) plan unit: [id:29201740] [2017-10-05 - OR]
2393INFO : (05-23 07:16:54) plan unit: [id:29201741] [2017-10-12 - OR]
2394INFO : (05-23 07:16:54) plan unit: [id:29201742] [2017-10-19 - OR]
2395INFO : (05-23 07:16:54) plan unit: [id:29201743] [2017-10-26 - OR]
2396INFO : (05-23 07:16:54) plan unit: [id:30201740] [2017-10-05 - BU]
2397INFO : (05-23 07:16:54) plan unit: [id:30201741] [2017-10-12 - BU]
2398INFO : (05-23 07:16:54) plan unit: [id:30201742] [2017-10-19 - BU]
2399INFO : (05-23 07:16:54) plan unit: [id:30201743] [2017-10-26 - BU]
2400INFO : (05-23 07:16:54) plan unit: [id:31201740] [2017-10-05 - OB]
2401INFO : (05-23 07:16:54) plan unit: [id:31201741] [2017-10-12 - OB]
2402INFO : (05-23 07:16:54) plan unit: [id:31201742] [2017-10-19 - OB]
2403INFO : (05-23 07:16:54) plan unit: [id:31201743] [2017-10-26 - OB]
2404INFO : (05-23 07:16:54) plan unit: [id:32201740] [2017-10-05 - GI1]
2405INFO : (05-23 07:16:54) plan unit: [id:32201741] [2017-10-12 - GI1]
2406INFO : (05-23 07:16:54) plan unit: [id:32201742] [2017-10-19 - GI1]
2407INFO : (05-23 07:16:54) plan unit: [id:32201743] [2017-10-26 - GI1]
2408INFO : (05-23 07:16:54) plan unit: [id:33201740] [2017-10-05 - PR]
2409INFO : (05-23 07:16:54) plan unit: [id:33201741] [2017-10-12 - PR]
2410INFO : (05-23 07:16:54) plan unit: [id:33201742] [2017-10-19 - PR]
2411INFO : (05-23 07:16:54) plan unit: [id:33201743] [2017-10-26 - PR]
2412INFO : (05-23 07:16:54) plan unit: [id:38201740] [2017-10-06 - OR]
2413INFO : (05-23 07:16:54) plan unit: [id:38201741] [2017-10-13 - OR]
2414INFO : (05-23 07:16:54) plan unit: [id:38201742] [2017-10-20 - OR]
2415INFO : (05-23 07:16:54) plan unit: [id:38201743] [2017-10-27 - OR]
2416INFO : (05-23 07:16:54) plan unit: [id:39201740] [2017-10-06 - BU]
2417INFO : (05-23 07:16:54) plan unit: [id:39201741] [2017-10-13 - BU]
2418INFO : (05-23 07:16:54) plan unit: [id:39201742] [2017-10-20 - BU]
2419INFO : (05-23 07:16:54) plan unit: [id:39201743] [2017-10-27 - BU]
2420INFO : (05-23 07:16:54) plan unit: [id:40201740] [2017-10-06 - OB]
2421INFO : (05-23 07:16:54) plan unit: [id:40201741] [2017-10-13 - OB]
2422INFO : (05-23 07:16:54) plan unit: [id:40201742] [2017-10-20 - OB]
2423INFO : (05-23 07:16:54) plan unit: [id:40201743] [2017-10-27 - OB]
2424INFO : (05-23 07:16:54) plan unit: [id:41201740] [2017-10-06 - GI1]
2425INFO : (05-23 07:16:54) plan unit: [id:41201741] [2017-10-13 - GI1]
2426INFO : (05-23 07:16:54) plan unit: [id:41201742] [2017-10-20 - GI1]
2427INFO : (05-23 07:16:54) plan unit: [id:41201743] [2017-10-27 - GI1]
2428INFO : (05-23 07:16:54) plan unit: [id:42201740] [2017-10-06 - PR]
2429INFO : (05-23 07:16:54) plan unit: [id:42201741] [2017-10-13 - PR]
2430INFO : (05-23 07:16:54) plan unit: [id:42201742] [2017-10-20 - PR]
2431INFO : (05-23 07:16:54) plan unit: [id:42201743] [2017-10-27 - PR]
2432INFO : (05-23 07:16:54) plan unit: [id:47201740] [2017-10-07 - OR]
2433INFO : (05-23 07:16:54) plan unit: [id:47201741] [2017-10-14 - OR]
2434INFO : (05-23 07:16:54) plan unit: [id:47201742] [2017-10-21 - OR]
2435INFO : (05-23 07:16:54) plan unit: [id:47201743] [2017-10-28 - OR]
2436INFO : (05-23 07:16:54) plan unit: [id:48201740] [2017-10-07 - OB]
2437INFO : (05-23 07:16:54) plan unit: [id:48201741] [2017-10-14 - OB]
2438INFO : (05-23 07:16:54) plan unit: [id:48201742] [2017-10-21 - OB]
2439INFO : (05-23 07:16:54) plan unit: [id:48201743] [2017-10-28 - OB]
2440 (150.9ms) select
2441 coalesce(max(
2442 case
2443 when (ct.max_weekday_assignments > 0) then 1
2444 else 0
2445 end
2446 ), 0) as weekday_flag,
2447 coalesce(max(
2448 case
2449 when (ct.max_weekend_assignments > 0) then 1
2450 else 0
2451 end
2452 ), 0) as weekend_flag
2453 from
2454 buffer_auto_populate_slots_926417780 as aps
2455 inner join call_types as ct
2456 on aps.call_type_id = ct.id
2457
2458 (141.9ms) /* CallOverlapRule */
2459 delete ca
2460 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2461 inner join buffer_schedules_926417780 as sc
2462 on ca.schedule_id = sc.id
2463 left join buffer_staff_daily_counts_926417780 as sdc use index (ix_buffer_staff_daily_counts_composite_1)
2464 on ca.staff_id = sdc.staff_id
2465 and sc.schedule_on = sdc.schedule_on
2466 and sdc.linkage_instance_id is null
2467 left join buffer_staff_daily_counts_926417780 as sdc_2 use index (ix_buffer_staff_daily_counts_composite_1)
2468 on ca.staff_id = sdc_2.staff_id
2469 and sc.schedule_on = sdc_2.schedule_on
2470 and sc.linkage_instance_id = sdc_2.linkage_instance_id
2471 where (ifnull(sdc.slots_count, 0) - ifnull(sdc_2.slots_count, 0) + 1) > 1
2472
2473 (155.0ms) /* NegativeLinkRule */
2474 delete ca
2475 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2476 inner join buffer_negative_relations_926417780 as nr use index (ix_buffer_negative_relations_composite_1)
2477 on ca.schedule_id = nr.schedule_id
2478 and ca.staff_id = nr.staff_id
2479
2480 (142.6ms) /* SpecializationOverlapRule */
2481 delete ca
2482 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2483 inner join buffer_schedules_926417780 as sc
2484 on ca.schedule_id = sc.id
2485 inner join buffer_staff_specializations_926417780 as ssp
2486 on ca.staff_id = ssp.staff_id
2487 inner join max_applicable_call_types as mact
2488 on ssp.specialization_id = mact.specialization_id
2489 and sc.call_type_id = mact.call_type_id
2490 inner join buffer_specialization_daily_counts_926417780 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2491 on ssp.specialization_id = sdc.specialization_id
2492 and sc.schedule_on = sdc.schedule_on
2493 and sdc.staff_id is null
2494 left join buffer_specialization_daily_counts_926417780 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2495 on ssp.specialization_id = sdc_2.specialization_id
2496 and sc.schedule_on = sdc_2.schedule_on
2497 and ca.staff_id = sdc_2.staff_id
2498 where sdc.should_be_evaluated = 1
2499 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2500
2501 (142.9ms) /* BrokenLinkageRule */
2502 truncate table buffer_combined_availability_linkages_926417780
2503
2504 (153.5ms) insert into buffer_combined_availability_linkages_926417780 (
2505 staff_id,
2506 linkage_instance_id,
2507 slots_count,
2508 is_primary,
2509 has_violations
2510 )
2511 select
2512 staff_id,
2513 linkage_instance_id,
2514 count(1) as slots_count,
2515 min(is_primary) as is_primary,
2516 max(has_violations) as has_violations
2517 from buffer_combined_availabilities_926417780 use index (ix_buffer_combined_availabilities_composite_2)
2518 group by
2519 staff_id,
2520 linkage_instance_id
2521 order by null
2522
2523 (183.0ms) delete ca
2524 from
2525 buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2526 inner join buffer_schedules_926417780 as sc
2527 on ca.schedule_id = sc.id
2528 inner join buffer_linkages_926417780 as l
2529 on sc.linkage_instance_id = l.linkage_instance_id
2530 inner join buffer_combined_availability_linkages_926417780 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2531 on ca.staff_id = ls_1.staff_id
2532 and sc.linkage_instance_id = ls_1.linkage_instance_id
2533 left join buffer_schedule_linkages_926417780 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2534 on ca.staff_id = ls_2.staff_id
2535 and sc.linkage_instance_id = ls_2.linkage_instance_id
2536 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2537
2538 (162.3ms) select straight_join
2539 sc.schedule_on,
2540 sc.call_type_id,
2541 count(distinct ca.staff_id) as available_members_count,
2542 group_concat(
2543 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
2544 order by ls_1.is_primary desc, ca.staff_id
2545 separator ', '
2546 ) as available_members_ids,
2547 sc.linkage_instance_id,
2548 l.slots_count_total as linkage_slots_count
2549 from buffer_combined_availabilities_926417780 as ca
2550 inner join buffer_schedules_926417780 as sc
2551 on ca.schedule_id = sc.id
2552 inner join buffer_linkages_926417780 as l
2553 on sc.linkage_instance_id = l.linkage_instance_id
2554 inner join buffer_combined_availability_linkages_926417780 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2555 on ca.staff_id = ls_1.staff_id
2556 and sc.linkage_instance_id = ls_1.linkage_instance_id
2557 and ls_1.has_violations = 0
2558 where sc.should_be_evaluated = 1
2559 and sc.should_be_populated = 1
2560 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
2561 order by available_members_count, sc.schedule_on, sc.display_order
2562 limit 1
2563
2564 (147.0ms) truncate table buffer_rule_scores_926417780
2565
2566 (144.6ms) truncate table buffer_rule_calc_filters_926417780
2567
2568 (153.1ms) insert into buffer_rule_calc_filters_926417780 (
2569 linkage_instance_id,
2570 staff_id
2571 ) values (14201741, 65),(14201741, 68),(14201741, 70),(14201741, 71),(14201741, 73),(14201741, 75),(14201741, 78),(14201741, 79),(14201741, 80),(14201741, 81),(14201741, 6960)
2572
2573 (147.1ms) /* CallOverlapRule */
2574 insert into buffer_rule_scores_926417780 (
2575 linkage_instance_id,
2576 schedule_id,
2577 staff_id,
2578 rule_name,
2579 value
2580 )
2581 select straight_join
2582 b_schedules.linkage_instance_id,
2583 b_schedules.id as schedule_id,
2584 availabilities.staff_id,
2585 'CallOverlapRule' as rule_name,
2586 (
2587 1 /* CallOverlap limit */ -
2588 case
2589 when (
2590 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0)) > 1 /* CallOverlap limit */
2591 and 1 /* CallOverlap limit */ != 0
2592 ) then 1 /* CallOverlap limit */
2593 else
2594 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0))
2595 end
2596 ) as value
2597 from
2598 buffer_rule_calc_filters_926417780 as filters
2599 inner join buffer_schedules_926417780 as b_schedules
2600 on filters.linkage_instance_id = b_schedules.linkage_instance_id
2601 inner join buffer_combined_availabilities_926417780 as availabilities
2602 on b_schedules.id = availabilities.schedule_id
2603 and filters.staff_id = availabilities.staff_id
2604 left join buffer_staff_daily_counts_926417780 as daily_counts
2605 on availabilities.staff_id = daily_counts.staff_id
2606 and b_schedules.schedule_on = daily_counts.schedule_on
2607 and daily_counts.linkage_instance_id is null
2608 left join buffer_staff_daily_counts_926417780 as daily_counts_2
2609 on availabilities.staff_id = daily_counts_2.staff_id
2610 and b_schedules.schedule_on = daily_counts_2.schedule_on
2611 and b_schedules.linkage_instance_id = daily_counts_2.linkage_instance_id
2612 order by null
2613
2614 (146.7ms) truncate table buffer_rule_scores_with_corrections_926417780
2615
2616 (143.8ms) insert into buffer_rule_scores_with_corrections_926417780 (
2617 linkage_instance_id, schedule_id, staff_id, rule_name, value
2618 )
2619 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2620 ifnull(rule_scores.value, max_values.max_value + 1) as value
2621 from buffer_rule_scores_926417780 as rule_scores
2622 inner join (
2623 select rule_name, max(value) as max_value
2624 from buffer_rule_scores_926417780
2625 group by rule_name
2626 order by null
2627 ) as max_values
2628 on rule_scores.rule_name = max_values.rule_name
2629 order by null
2630
2631 (154.4ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2632 from (
2633 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2634 sum(
2635 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2636 rules.weight *
2637 total_values.slots_count
2638 ) as slot_scores
2639 from buffer_rule_scores_with_corrections_926417780 as rule_scores
2640 inner join (
2641 select rule_name, sum(abs(value)) as total_value,
2642 count(distinct schedule_id) as slots_count
2643 from buffer_rule_scores_with_corrections_926417780
2644 group by rule_name
2645 order by null
2646 ) as total_values
2647 on rule_scores.rule_name = total_values.rule_name
2648 inner join rules
2649 on rule_scores.rule_name = rules.type
2650 where rules.rule_set_id = 1530
2651 and rules.applied = 1
2652 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2653 order by null
2654 ) as staff_scores
2655 group by linkage_instance_id, staff_id
2656 order by round(avg(slot_scores), 5) desc, rand()
2657 limit 1
2658
2659 PublishedBlock Load (142.6ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 6 AND ('2017-10-10' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 6 ORDER BY `published_blocks`.`id` ASC LIMIT 1
2660 (141.0ms) BEGIN
2661 (143.9ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
2662 FROM call_types AS ct
2663 INNER JOIN calendar_schedules AS cs
2664 ON ct.calendar_schedule_id = cs.id
2665 INNER JOIN blocks AS b
2666 ON b.client_id = ct.client_id
2667 AND b.block_schedule_id = cs.block_schedule_id
2668 AND '2017-10-10' BETWEEN b.start_date AND b.end_date
2669 INNER JOIN published_blocks AS pb
2670 ON pb.client_id = ct.client_id
2671 AND pb.block_id = b.id
2672 AND pb.calendar_schedule_id = cs.id
2673 LEFT JOIN published_block_call_types AS pbct
2674 ON pbct.client_id = ct.client_id
2675 AND pbct.assignment_id = ct.id
2676 AND pbct.assignment_type = 'CallType'
2677 AND pbct.block_id = b.id
2678 AND (
2679 (1 << (DAYOFWEEK('2017-10-10') - 1) & pbct.days_of_week_mask) > 0
2680 )
2681 WHERE ct.id = 49
2682 AND (
2683 (
2684 pb.assignment_ids IS NULL
2685 AND (1 << (DAYOFWEEK('2017-10-10') - 1) & pb.days_of_week_mask) > 0
2686 )
2687 OR pbct.id IS NOT NULL
2688 )
2689
2690 CallType Load (142.3ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 49 LIMIT 1
2691 Client Load (287.0ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
2692 Staff Load (145.0ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 68 LIMIT 1
2693 Schedule Exists (141.1ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 49 AND `schedules`.`client_id` = 6 AND `schedules`.`schedule_on` = '2017-10-10' LIMIT 1
2694 SQL (160.3ms) INSERT INTO `schedules` (`client_id`, `call_type_id`, `staff_id`, `schedule_on`, `created_at`, `updated_at`, `weekend_on`, `dayofweek_on`, `populate_method`, `creator_id`) VALUES (6, 49, 68, '2017-10-10', '2018-05-23 07:17:38', '2018-05-23 07:17:38', 0, 3, 'auto_populate_step', 166)
2695 VisibleAssignment Load (141.0ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 6 AND `visible_assignments`.`date` = '2017-10-10' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 49 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
2696 DailyListSlot Load (141.3ms) SELECT `daily_list_slots`.* FROM `daily_list_slots` WHERE `daily_list_slots`.`schedule_on` = '2017-10-10' AND `daily_list_slots`.`staff_id` = 68 AND `daily_list_slots`.`call_type_id` = 49 ORDER BY daily_list_slots.position asc LIMIT 1
2697 (145.6ms) COMMIT
2698FLUSHED: nil:nil
2699 (143.4ms) UPDATE buffer_schedules_926417780
2700 SET staff_id = 68,
2701 should_be_populated = 0,
2702 should_be_evaluated = 0
2703 WHERE
2704 call_type_id = 49
2705 AND schedule_on = '2017-10-10'
2706
2707 (145.4ms) delete from buffer_combined_availabilities_926417780
2708 where linkage_instance_id = 14201741
2709
2710 (141.2ms) UPDATE buffer_rule_calc_schedules_926417780
2711 SET staff_id = 68
2712 WHERE call_type_id = 49
2713 AND schedule_on = '2017-10-10'
2714
2715 (143.4ms) INSERT INTO buffer_rule_calc_schedules_926417780
2716 SET staff_id = 68,
2717 call_type_id = 49,
2718 schedule_on = '2017-10-10',
2719 day_of_week_mask = 4
2720
2721 (140.9ms) /* CallOverlapRule */
2722 UPDATE buffer_staff_daily_counts_926417780
2723 SET slots_count = slots_count + 1
2724 WHERE
2725 staff_id = 68
2726 AND schedule_on = '2017-10-10'
2727 AND linkage_instance_id is null
2728
2729 (146.7ms) INSERT INTO buffer_staff_daily_counts_926417780
2730 SET slots_count = 1,
2731 staff_id = 68,
2732 schedule_on = '2017-10-10',
2733 linkage_instance_id = null
2734
2735 (143.4ms) /* CallOverlapRule */
2736 UPDATE buffer_staff_daily_counts_926417780
2737 SET slots_count = slots_count + 1
2738 WHERE
2739 staff_id = 68
2740 AND schedule_on = '2017-10-10'
2741 AND linkage_instance_id = 14201741
2742
2743 (142.2ms) INSERT INTO buffer_staff_daily_counts_926417780
2744 SET slots_count = 1,
2745 staff_id = 68,
2746 schedule_on = '2017-10-10',
2747 linkage_instance_id = 14201741
2748
2749 (144.2ms) /* NegativeLinkRule */
2750 UPDATE buffer_negative_relations_926417780
2751 SET staff_id = 68
2752 WHERE affected_by_schedule_id = (
2753 SELECT id
2754 FROM buffer_schedules_926417780
2755 WHERE schedule_on = '2017-10-10'
2756 AND call_type_id = 49
2757 )
2758
2759 (153.3ms) /* SpecializationOverlapRule */
2760 update buffer_staff_specializations_926417780 as st_sp
2761 inner join max_applicable_call_types as mact
2762 on st_sp.specialization_id = mact.specialization_id
2763 and mact.call_type_id = 49
2764 inner join buffer_specialization_daily_counts_926417780 as sp_dc
2765 on st_sp.specialization_id = sp_dc.specialization_id
2766 and sp_dc.staff_id is null
2767 and sp_dc.schedule_on = '2017-10-10'
2768 left join buffer_specialization_daily_counts_926417780 as sp_dc_2
2769 on st_sp.specialization_id = sp_dc_2.specialization_id
2770 and st_sp.staff_id = sp_dc_2.staff_id
2771 and sp_dc_2.schedule_on = '2017-10-10'
2772 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
2773 where st_sp.staff_id = 68
2774 and sp_dc_2.id is null
2775
2776 (143.1ms) insert into buffer_specialization_daily_counts_926417780 (
2777 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2778 )
2779 select sp.id as specialization_id,
2780 68 as staff_id,
2781 '2017-10-10' as schedule_on,
2782 1 as staffs_scheduled,
2783 sp.max_scheduled,
2784 sp_dc.should_be_evaluated
2785 from buffer_staff_specializations_926417780 as st_sp
2786 inner join specializations as sp
2787 on st_sp.specialization_id = sp.id
2788 inner join max_applicable_call_types as mact
2789 on st_sp.specialization_id = mact.specialization_id
2790 and mact.call_type_id = 49
2791 inner join buffer_specialization_daily_counts_926417780 as sp_dc
2792 on st_sp.specialization_id = sp_dc.specialization_id
2793 and sp_dc.staff_id is null
2794 and sp_dc.schedule_on = '2017-10-10'
2795 left join buffer_specialization_daily_counts_926417780 as sp_dc_2
2796 on st_sp.specialization_id = sp_dc_2.specialization_id
2797 and st_sp.staff_id = sp_dc_2.staff_id
2798 and sp_dc_2.schedule_on = '2017-10-10'
2799 where st_sp.staff_id = 68
2800 and sp_dc_2.id is null
2801
2802 (143.9ms) /* MaxAssignmentsRule */
2803 delete from buffer_linkage_call_type_assignments_926417780
2804 where linkage_instance_id = 14201741;
2805
2806 (142.5ms) /* MaxAssignmentsRule */
2807 update
2808 buffer_schedules_926417780 as sc
2809 inner join call_types as ct
2810 on sc.call_type_id = ct.id
2811 and ct.active = 1
2812 inner join buffer_call_type_assignments_926417780 as cta
2813 on sc.staff_id = cta.staff_id
2814 and sc.call_type_id = cta.call_type_id
2815 and sc.is_weekend = cta.is_weekend
2816 and sc.period_offset = cta.period_offset
2817 and sc.calendar_schedule_id = cta.calendar_schedule_id
2818 set cta.assignments_count = cta.assignments_count + 1
2819 where
2820 sc.schedule_on = '2017-10-10'
2821 and sc.call_type_id = 49
2822 and sc.staff_id = 68
2823
2824 (159.1ms) insert into buffer_call_type_assignments_926417780 (
2825 staff_id,
2826 call_type_id,
2827 is_weekend,
2828 period_offset,
2829 calendar_schedule_id,
2830 assignments_count
2831 )
2832 select
2833 sc.staff_id,
2834 sc.call_type_id,
2835 sc.is_weekend,
2836 sc.period_offset,
2837 sc.calendar_schedule_id,
2838 1 as assignments_count
2839 from buffer_schedules_926417780 as sc
2840 where
2841 sc.staff_id = 68
2842 and sc.schedule_on = '2017-10-10'
2843 and sc.call_type_id = 49
2844
2845 (143.8ms) /* UnassignedDaysRule */
2846 delete from buffer_linkage_assigned_days_926417780
2847 where linkage_instance_id = 14201741
2848
2849 (160.7ms) /* UnassignedDaysRule */
2850 UPDATE buffer_staff_assigned_days_926417780 as sad
2851 inner join call_types as ct
2852 on ct.id = 49
2853 and ct.unassigned_day = 0
2854 SET sad.is_assigned = 1
2855 WHERE sad.staff_id = 68
2856 AND sad.schedule_on = '2017-10-10'
2857
2858 (143.4ms) delete
2859 from buffer_linkage_assignment_limits_926417780
2860 where
2861 staff_id = 68
2862 and linkage_instance_id = 14201741
2863
2864 (158.2ms) /* AssignmentLimitsPerPeriod */
2865 /* AssignmentLimitsPerWeek */
2866 /* AssignmentLimitsRolling */
2867 /* AssignmentLimitsConsecutive */
2868
2869 insert into buffer_staff_assignment_limits_926417780 (
2870 staff_id,
2871 assignment_limit_id,
2872 period_id,
2873 consecutive_days_mask,
2874 current_value
2875 )
2876 select
2877 sc.staff_id,
2878 al.id as assignment_limit_id,
2879 alp.id as period_id,
2880 case
2881 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2882 (1 << datediff(sc.schedule_on, alp.start_date))
2883 else 0
2884 end as consecutive_days_mask,
2885 1.0 as current_value
2886 from
2887 buffer_schedules_926417780 as sc
2888 inner join buffer_assignment_limit_call_types_926417780 as alct
2889 on sc.call_type_id = alct.call_type_id
2890 inner join buffer_assignment_limits_926417780 as al
2891 on alct.assignment_limit_id = al.id
2892 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2893 inner join buffer_assignment_limit_staffs_926417780 as als
2894 on al.id = als.assignment_limit_id
2895 and sc.staff_id = als.staff_id
2896 inner join assign_staff_types as ast
2897 on sc.staff_id = ast.staff_id
2898 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2899 inner join buffer_assignment_limit_staff_types_926417780 as alst
2900 on al.id = alst.assignment_limit_id
2901 and ast.staff_type_id = alst.staff_type_id
2902 inner join buffer_assignment_limit_periods_926417780 as alp
2903 on al.id = alp.assignment_limit_id
2904 and sc.schedule_on between alp.start_date and alp.end_date
2905 where
2906 sc.schedule_on = '2017-10-10'
2907 and sc.call_type_id = 49
2908 and sc.staff_id = 68
2909 on duplicate key update
2910 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
2911 current_value = current_value + values(current_value)
2912
2913INFO : (05-23 07:17:42) scheduled for: plan unit: [id:14201741] [2017-10-10 - GI1], Andrew Church. DEPTH(1)
2914 (142.4ms) select
2915 coalesce(max(
2916 case
2917 when (ct.max_weekday_assignments > 0) then 1
2918 else 0
2919 end
2920 ), 0) as weekday_flag,
2921 coalesce(max(
2922 case
2923 when (ct.max_weekend_assignments > 0) then 1
2924 else 0
2925 end
2926 ), 0) as weekend_flag
2927 from
2928 buffer_auto_populate_slots_926417780 as aps
2929 inner join call_types as ct
2930 on aps.call_type_id = ct.id
2931
2932 (150.3ms) /* CallOverlapRule */
2933 delete ca
2934 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2935 inner join buffer_schedules_926417780 as sc
2936 on ca.schedule_id = sc.id
2937 left join buffer_staff_daily_counts_926417780 as sdc use index (ix_buffer_staff_daily_counts_composite_1)
2938 on ca.staff_id = sdc.staff_id
2939 and sc.schedule_on = sdc.schedule_on
2940 and sdc.linkage_instance_id is null
2941 left join buffer_staff_daily_counts_926417780 as sdc_2 use index (ix_buffer_staff_daily_counts_composite_1)
2942 on ca.staff_id = sdc_2.staff_id
2943 and sc.schedule_on = sdc_2.schedule_on
2944 and sc.linkage_instance_id = sdc_2.linkage_instance_id
2945 where (ifnull(sdc.slots_count, 0) - ifnull(sdc_2.slots_count, 0) + 1) > 1
2946
2947 (142.9ms) /* NegativeLinkRule */
2948 delete ca
2949 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2950 inner join buffer_negative_relations_926417780 as nr use index (ix_buffer_negative_relations_composite_1)
2951 on ca.schedule_id = nr.schedule_id
2952 and ca.staff_id = nr.staff_id
2953
2954 (150.9ms) /* SpecializationOverlapRule */
2955 delete ca
2956 from buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
2957 inner join buffer_schedules_926417780 as sc
2958 on ca.schedule_id = sc.id
2959 inner join buffer_staff_specializations_926417780 as ssp
2960 on ca.staff_id = ssp.staff_id
2961 inner join max_applicable_call_types as mact
2962 on ssp.specialization_id = mact.specialization_id
2963 and sc.call_type_id = mact.call_type_id
2964 inner join buffer_specialization_daily_counts_926417780 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2965 on ssp.specialization_id = sdc.specialization_id
2966 and sc.schedule_on = sdc.schedule_on
2967 and sdc.staff_id is null
2968 left join buffer_specialization_daily_counts_926417780 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2969 on ssp.specialization_id = sdc_2.specialization_id
2970 and sc.schedule_on = sdc_2.schedule_on
2971 and ca.staff_id = sdc_2.staff_id
2972 where sdc.should_be_evaluated = 1
2973 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2974
2975 (143.4ms) /* BrokenLinkageRule */
2976 truncate table buffer_combined_availability_linkages_926417780
2977
2978 (150.1ms) insert into buffer_combined_availability_linkages_926417780 (
2979 staff_id,
2980 linkage_instance_id,
2981 slots_count,
2982 is_primary,
2983 has_violations
2984 )
2985 select
2986 staff_id,
2987 linkage_instance_id,
2988 count(1) as slots_count,
2989 min(is_primary) as is_primary,
2990 max(has_violations) as has_violations
2991 from buffer_combined_availabilities_926417780 use index (ix_buffer_combined_availabilities_composite_2)
2992 group by
2993 staff_id,
2994 linkage_instance_id
2995 order by null
2996
2997 (144.1ms) delete ca
2998 from
2999 buffer_combined_availabilities_926417780 as ca use index (ix_buffer_combined_availabilities_composite_1)
3000 inner join buffer_schedules_926417780 as sc
3001 on ca.schedule_id = sc.id
3002 inner join buffer_linkages_926417780 as l
3003 on sc.linkage_instance_id = l.linkage_instance_id
3004 inner join buffer_combined_availability_linkages_926417780 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3005 on ca.staff_id = ls_1.staff_id
3006 and sc.linkage_instance_id = ls_1.linkage_instance_id
3007 left join buffer_schedule_linkages_926417780 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3008 on ca.staff_id = ls_2.staff_id
3009 and sc.linkage_instance_id = ls_2.linkage_instance_id
3010 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
3011
3012 (180.9ms) select straight_join
3013 sc.schedule_on,
3014 sc.call_type_id,
3015 count(distinct ca.staff_id) as available_members_count,
3016 group_concat(
3017 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3018 order by ls_1.is_primary desc, ca.staff_id
3019 separator ', '
3020 ) as available_members_ids,
3021 sc.linkage_instance_id,
3022 l.slots_count_total as linkage_slots_count
3023 from buffer_combined_availabilities_926417780 as ca
3024 inner join buffer_schedules_926417780 as sc
3025 on ca.schedule_id = sc.id
3026 inner join buffer_linkages_926417780 as l
3027 on sc.linkage_instance_id = l.linkage_instance_id
3028 inner join buffer_combined_availability_linkages_926417780 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3029 on ca.staff_id = ls_1.staff_id
3030 and sc.linkage_instance_id = ls_1.linkage_instance_id
3031 and ls_1.has_violations = 0
3032 where sc.should_be_evaluated = 1
3033 and sc.should_be_populated = 1
3034 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3035 order by available_members_count, sc.schedule_on, sc.display_order
3036 limit 1
3037
3038 (142.9ms) truncate table buffer_rule_scores_926417780
3039
3040 (144.5ms) truncate table buffer_rule_calc_filters_926417780
3041
3042 (144.1ms) insert into buffer_rule_calc_filters_926417780 (
3043 linkage_instance_id,
3044 staff_id
3045 ) values (15201741, 65),(15201741, 70),(15201741, 71),(15201741, 73),(15201741, 75),(15201741, 78),(15201741, 79),(15201741, 80),(15201741, 81)
3046
3047 (149.3ms) /* CallOverlapRule */
3048 insert into buffer_rule_scores_926417780 (
3049 linkage_instance_id,
3050 schedule_id,
3051 staff_id,
3052 rule_name,
3053 value
3054 )
3055 select straight_join
3056 b_schedules.linkage_instance_id,
3057 b_schedules.id as schedule_id,
3058 availabilities.staff_id,
3059 'CallOverlapRule' as rule_name,
3060 (
3061 1 /* CallOverlap limit */ -
3062 case
3063 when (
3064 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0)) > 1 /* CallOverlap limit */
3065 and 1 /* CallOverlap limit */ != 0
3066 ) then 1 /* CallOverlap limit */
3067 else
3068 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0))
3069 end
3070 ) as value
3071 from
3072 buffer_rule_calc_filters_926417780 as filters
3073 inner join buffer_schedules_926417780 as b_schedules
3074 on filters.linkage_instance_id = b_schedules.linkage_instance_id
3075 inner join buffer_combined_availabilities_926417780 as availabilities
3076 on b_schedules.id = availabilities.schedule_id
3077 and filters.staff_id = availabilities.staff_id
3078 left join buffer_staff_daily_counts_926417780 as daily_counts
3079 on availabilities.staff_id = daily_counts.staff_id
3080 and b_schedules.schedule_on = daily_counts.schedule_on
3081 and daily_counts.linkage_instance_id is null
3082 left join buffer_staff_daily_counts_926417780 as daily_counts_2
3083 on availabilities.staff_id = daily_counts_2.staff_id
3084 and b_schedules.schedule_on = daily_counts_2.schedule_on
3085 and b_schedules.linkage_instance_id = daily_counts_2.linkage_instance_id
3086 order by null
3087
3088 (142.5ms) truncate table buffer_rule_scores_with_corrections_926417780
3089
3090 (149.4ms) insert into buffer_rule_scores_with_corrections_926417780 (
3091 linkage_instance_id, schedule_id, staff_id, rule_name, value
3092 )
3093 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3094 ifnull(rule_scores.value, max_values.max_value + 1) as value
3095 from buffer_rule_scores_926417780 as rule_scores
3096 inner join (
3097 select rule_name, max(value) as max_value
3098 from buffer_rule_scores_926417780
3099 group by rule_name
3100 order by null
3101 ) as max_values
3102 on rule_scores.rule_name = max_values.rule_name
3103 order by null
3104
3105 (144.7ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3106 from (
3107 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3108 sum(
3109 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3110 rules.weight *
3111 total_values.slots_count
3112 ) as slot_scores
3113 from buffer_rule_scores_with_corrections_926417780 as rule_scores
3114 inner join (
3115 select rule_name, sum(abs(value)) as total_value,
3116 count(distinct schedule_id) as slots_count
3117 from buffer_rule_scores_with_corrections_926417780
3118 group by rule_name
3119 order by null
3120 ) as total_values
3121 on rule_scores.rule_name = total_values.rule_name
3122 inner join rules
3123 on rule_scores.rule_name = rules.type
3124 where rules.rule_set_id = 1530
3125 and rules.applied = 1
3126 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3127 order by null
3128 ) as staff_scores
3129 group by linkage_instance_id, staff_id
3130 order by round(avg(slot_scores), 5) desc, rand()
3131 limit 1
3132
3133**Airbrake: closed