· 8 years ago · Feb 25, 2018, 01:32 PM
1(134.5ms) SET NAMES utf8, @@SESSION.sql_mode = 'NO_ENGINE_SUBSTITUTION', @@SESSION.collation_connection = 'utf8_unicode_ci', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2 Client Load (299.3ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3 CalendarSchedule Load (137.6ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
4 CallType Load (131.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (12692, 12693, 12694)
5 CalendarSchedule Load (132.5ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
6 Block Load (138.0ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND `blocks`.`id` = 29251 ORDER BY start_date asc, end_date asc LIMIT 1
7 BlockSchedule Load (129.5ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
8 PopulationTimeframe Load (254.1ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 319 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
9 RuleSet Load (129.3ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1503 LIMIT 1
10 Block Load (134.7ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND (blocks.start_date >= '2018-03-01' AND blocks.end_date <= '2018-03-31') ORDER BY start_date asc, end_date asc
11INFO : (02-13 16:16:57) phase: Scheduler::Base
12INFO : (02-13 16:16:57) from: 2018-03-01 to: 2018-03-31
13 Rule Load (130.1ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1503 ORDER BY applied desc, weight desc
14INFO : (02-13 16:16:58) rule configurations: 4. CUMULATIVE WEEKEND / WEEKDAY ASSIGNMENT TARGETS
15 - 'Cumulative Weekend / Weekday Assignment Targets' applied {"opt1"=>{"applied"=>"1"}, "opt2"=>{"applied"=>"1"}, "round_targets"=>"false"} 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 Combined Assignment Targets' not applied
23 - 'Cumulative Weekend / Weekday Hour Targets' not applied
24 - 'Cumulative Weekend / Weekday Point Targets' not applied
25 - 'Current Assignment Targets' not applied
26 - 'Current Combined Assignment Targets' not applied
27 - 'Current Daily Assignment Targets' not applied
28 - 'Current Day of Week Targets' not applied
29 - 'Current Hour Targets' not applied
30 - 'Current Point Targets' not applied
31 - 'Current Weekend / Weekday Assignment Targets' not applied
32 - 'Current Weekend / Weekday Combined Assignment Targets' not applied
33 - 'Current Weekend / Weekday Hour Targets' not applied
34 - 'Current Weekend / Weekday Point Targets' not applied
35 - 'Pending Call Requests' not applied
36 - 'Pending No-call Requests' not applied
37 - 'Assignments Per Day' 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 : (02-13 16:16:58) started: Scheduler::Cache::Collector.collect_all
44INFO : (02-13 16:16:58) collect_staffs
45 Staff Load (131.6ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `assign_staff_types` ON `assign_staff_types`.`staff_id` = `staffs`.`id` INNER JOIN `staff_types` ON `staff_types`.`id` = `assign_staff_types`.`staff_type_id` WHERE `staffs`.`client_id` = 319 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
46 Client Load (267.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319
47 StaffSpecialization Load (131.1ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (20702, 20705, 20704, 20695, 20701, 20697, 20699, 20698, 20706, 20696)
48 Specialization Load (132.8ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` IN (711, 712) ORDER BY `specializations`.`priority` ASC
49 StaffCallType Load (130.8ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (20702, 20705, 20704, 20695, 20701, 20697, 20699, 20698, 20706, 20696)
50INFO : (02-13 16:17:00) initial eligibles: [20702,20705,20704,20695,20701,20697,20699,20698,20706,20696]
51INFO : (02-13 16:17:00) collect_call_types
52 CallType Load (131.5ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 319 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
53INFO : (02-13 16:17:00) initial call types: [12692,12693,12694,12706]
54INFO : (02-13 16:17:00) collect_chain_links
55 ChainLink Load (129.7ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 319
56INFO : (02-13 16:17:01) collect_linkages
57INFO : (02-13 16:17:01) collect_schedules
58 Schedule Load (137.1ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 319 AND (`schedules`.`schedule_on` BETWEEN '2018-03-01' AND '2018-03-31')
59INFO : (02-13 16:17:01) collect_slots
60INFO : (02-13 16:17:01) collect_approved_call_requests
61 Request Load (132.6ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 319 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-03-01' AND '2018-03-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
62INFO : (02-13 16:17:01) collect_approved_no_call_requests
63 Request Load (132.5ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 319 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-03-01' AND '2018-03-31') ORDER BY requests.schedule_on asc, requests.created_at asc
64INFO : (02-13 16:17:01) finished: Scheduler::Cache::Collector.collect_all
65INFO : (02-13 16:17:01) begin to process requests
66 CallType Load (130.6ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 1381 AND `call_types`.`active` = 1
67 (132.0ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 1381 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
68 Request Load (130.6ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 319 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (12692, 12693, 12694) AND (requests.schedule_on > '2018-02-28')
69 SQL (152.2ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 319 AND 1=0
70INFO : (02-13 16:17:03) end of requests processing
71 Block Load (133.1ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND (blocks.start_date <= '2018-03-31' AND blocks.end_date >= '2018-03-31') ORDER BY start_date asc, end_date asc LIMIT 1
72 Client Load (137.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
73 NegativeLink Load (130.3ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
74INFO : (02-13 16:17:03) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
75INFO : (02-13 16:17:03) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
76INFO : (02-13 16:17:03) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
77INFO : (02-13 16:17:03) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
78INFO : (02-13 16:17:03) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
79INFO : (02-13 16:17:03) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
80INFO : (02-13 16:17:03) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
81INFO : (02-13 16:17:03) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
82INFO : (02-13 16:17:03) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
83INFO : (02-13 16:17:03) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query015
84INFO : (02-13 16:17:03) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
85INFO : (02-13 16:17:03) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
86INFO : (02-13 16:17:03) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
87INFO : (02-13 16:17:03) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
88INFO : (02-13 16:17:03) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
89INFO : (02-13 16:17:03) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
90INFO : (02-13 16:17:03) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
91INFO : (02-13 16:17:03) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
92INFO : (02-13 16:17:03) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
93INFO : (02-13 16:17:03) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
94INFO : (02-13 16:17:03) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0703
95INFO : (02-13 16:17:03) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0704
96INFO : (02-13 16:17:03) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0705
97INFO : (02-13 16:17:03) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0706
98INFO : (02-13 16:17:03) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0707
99INFO : (02-13 16:17:03) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query0708
100INFO : (02-13 16:17:03) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query0709
101INFO : (02-13 16:17:03) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL
102INFO : (02-13 16:17:03) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
103INFO : (02-13 16:17:03) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
104INFO : (02-13 16:17:03) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
105INFO : (02-13 16:17:03) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
106INFO : (02-13 16:17:03) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query08
107INFO : (02-13 16:17:03) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query085
108INFO : (02-13 16:17:03) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query09
109INFO : (02-13 16:17:03) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query092
110INFO : (02-13 16:17:03) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query093
111INFO : (02-13 16:17:03) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query095
112INFO : (02-13 16:17:03) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query097
113INFO : (02-13 16:17:03) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query098
114INFO : (02-13 16:17:03) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
115INFO : (02-13 16:17:03) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
116INFO : (02-13 16:17:03) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
117INFO : (02-13 16:17:03) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
118INFO : (02-13 16:17:03) <-- clear: 44 - Scheduler::AutoPopulate::Queries::Query09_09_RP
119INFO : (02-13 16:17:03) <-- clear: 45 - Scheduler::AutoPopulate::Queries::Query10
120INFO : (02-13 16:17:03) <-- clear: 46 - Scheduler::AutoPopulate::Queries::QueryScore02
121INFO : (02-13 16:17:03) <-- clear: 47 - Scheduler::AutoPopulate::Queries::QueryScore03
122INFO : (02-13 16:17:03) <-- clear: 48 - Scheduler::AutoPopulate::Queries::QueryScore04
123INFO : (02-13 16:17:03) <-- clear: 49 - Scheduler::AutoPopulate::Queries::Query11
124INFO : (02-13 16:17:03) <-- clear: 50 - Scheduler::AutoPopulate::Queries::DropTables
125 (131.8ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_72687288
126 (131.9ms) DROP TABLE IF EXISTS buffer_annual_point_targets_72687288
127 (151.8ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_72687288
128 (133.3ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_72687288
129 (131.8ms) DROP TABLE IF EXISTS buffer_call_days_targets_72687288
130 (134.0ms) DROP TABLE IF EXISTS buffer_call_type_targets_72687288
131 (136.2ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_72687288
132 (131.4ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_72687288
133 (152.0ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_72687288
134 (131.3ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_72687288
135 (132.0ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_72687288
136 (141.1ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_72687288
137 (131.2ms) DROP TABLE IF EXISTS buffer_current_cd_targets_72687288
138 (135.9ms) DROP TABLE IF EXISTS buffer_current_ct_targets_72687288
139 (149.9ms) DROP TABLE IF EXISTS buffer_current_dow_targets_72687288
140 (131.0ms) DROP TABLE IF EXISTS buffer_current_hour_targets_72687288
141 (131.0ms) DROP TABLE IF EXISTS buffer_current_oa_targets_72687288
142 (132.1ms) DROP TABLE IF EXISTS buffer_current_point_targets_72687288
143 (131.9ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_72687288
144 (131.6ms) DROP TABLE IF EXISTS buffer_hour_targets_72687288
145 (131.2ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_72687288
146 (131.1ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_72687288
147 (133.3ms) DROP TABLE IF EXISTS buffer_linkage_targets_72687288
148 (132.3ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_72687288
149 (132.9ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_72687288
150 (132.4ms) DROP TABLE IF EXISTS buffer_location_privileges_72687288
151 (135.4ms) DROP TABLE IF EXISTS buffer_periods_72687288
152 (134.9ms) DROP TABLE IF EXISTS buffer_point_targets_72687288
153 (131.1ms) DROP TABLE IF EXISTS buffer_schedules_72687288
154 (131.4ms) DROP TABLE IF EXISTS buffer_staff_rotation_points_72687288
155 (131.8ms) DROP TABLE IF EXISTS buffer_target_call_types_72687288
156 (135.6ms) DROP TABLE IF EXISTS buffer_target_daily_points_72687288
157 (142.5ms) DROP TABLE IF EXISTS buffer_target_dates_list_72687288
158 (146.5ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_72687288
159 (130.7ms) DROP TABLE IF EXISTS buffer_targets_72687288
160 (134.0ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_72687288
161 (131.0ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_72687288
162 (133.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_72687288
163 (133.1ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_72687288
164 (132.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_72687288
165 (131.6ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_72687288
166 (142.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_72687288
167 (131.8ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_72687288
168 (131.3ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_72687288
169 (134.3ms) DROP TABLE IF EXISTS buffer_call_type_assignments_72687288
170 (131.5ms) DROP TABLE IF EXISTS buffer_combined_availabilities_72687288
171 (136.6ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_72687288
172 (167.0ms) DROP TABLE IF EXISTS buffer_counter_increments_72687288
173 (146.8ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_72687288
174 (132.2ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_consecutive_72687288
175 (131.2ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_periods_72687288
176 (135.8ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_rolling_72687288
177 (131.2ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_weeks_72687288
178 (135.9ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_72687288
179 (131.0ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_72687288
180 (135.6ms) DROP TABLE IF EXISTS buffer_linkage_patterns_72687288
181 (131.2ms) DROP TABLE IF EXISTS buffer_linkages_72687288
182 (131.4ms) DROP TABLE IF EXISTS buffer_location_privileges_72687288
183 (132.3ms) DROP TABLE IF EXISTS buffer_min_distances_72687288
184 (130.9ms) DROP TABLE IF EXISTS buffer_negative_relations_72687288
185 (130.9ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_72687288
186 (132.0ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_72687288
187 (131.3ms) DROP TABLE IF EXISTS buffer_rule_scores_72687288
188 (131.9ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_72687288
189 (131.9ms) DROP TABLE IF EXISTS buffer_schedule_linkages_72687288
190 (253.1ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_72687288
191 (253.9ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_72687288
192 (253.6ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_72687288
193 (258.0ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_periods_72687288
194 (253.3ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_rolling_72687288
195 (253.1ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_weeks_72687288
196 (255.1ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_consecutive_72687288
197 (253.3ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_72687288
198 (254.5ms) DROP TABLE IF EXISTS buffer_staff_specializations_72687288
199 (266.7ms) DROP TABLE IF EXISTS buffer_static_availabilities_72687288
200 (253.3ms) DROP TABLE IF EXISTS buffer_target_assignments_72687288
201 (131.5ms) DROP VIEW IF EXISTS buffer_weekly_call_type_targets_72687288
202
203 (131.6ms) DROP VIEW IF EXISTS buffer_current_weekly_ct_targets_72687288
204
205 (131.0ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_ct_targets_72687288
206
207 (132.1ms) DROP VIEW IF EXISTS buffer_weekly_targets_72687288
208
209 (131.1ms) DROP VIEW IF EXISTS buffer_current_weekly_oa_targets_72687288
210
211 (132.0ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_oa_targets_72687288
212
213 (132.0ms) DROP VIEW IF EXISTS buffer_weekly_point_targets_72687288
214
215 (132.1ms) DROP VIEW IF EXISTS buffer_current_weekly_point_targets_72687288
216
217 (131.4ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_point_targets_72687288
218
219 (131.2ms) DROP VIEW IF EXISTS buffer_weekly_hour_targets_72687288
220
221 (131.3ms) DROP VIEW IF EXISTS buffer_current_weekly_hour_targets_72687288
222
223 (135.0ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_hour_targets_72687288
224
225 (134.3ms) delete from buffer_target_dates
226 where session_id = 72687288
227 and client_id = 319
228
229INFO : (02-13 16:17:17) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
230 (139.3ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_72687288 LIKE buffer_assignment_limit_call_types
231 (145.0ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_72687288 LIKE buffer_assignment_limit_periods
232 (144.7ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_72687288 LIKE buffer_assignment_limit_staff_types
233 (134.6ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_72687288 LIKE buffer_auto_populate_slots
234 (138.7ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_72687288 LIKE buffer_call_type_assignments
235 (134.0ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_72687288 LIKE buffer_combined_availabilities
236 (141.7ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_72687288 LIKE buffer_combined_availability_linkages
237 (146.5ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_72687288 LIKE buffer_counter_increments
238 (135.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_72687288 LIKE buffer_linkage_assigned_days
239 (137.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_consecutive_72687288 LIKE buffer_linkage_assignments_count_consecutive
240 (137.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_periods_72687288 LIKE buffer_linkage_assignments_count_periods
241 (134.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_rolling_72687288 LIKE buffer_linkage_assignments_count_rolling
242 (138.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_weeks_72687288 LIKE buffer_linkage_assignments_count_weeks
243 (139.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_72687288 LIKE buffer_linkage_call_type_assignments
244 (134.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_72687288 LIKE buffer_linkage_dow_targets
245 (134.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_72687288 LIKE buffer_linkage_patterns
246 (136.2ms) CREATE TABLE IF NOT EXISTS buffer_linkages_72687288 LIKE buffer_linkages
247 (134.5ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_72687288 LIKE buffer_location_privileges
248 (134.7ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_72687288 LIKE buffer_min_distances
249 (134.1ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_72687288 LIKE buffer_negative_relations
250 (134.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_72687288 LIKE buffer_rule_calc_filters
251 (135.1ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_72687288 LIKE buffer_rule_calc_schedules
252 (256.5ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_72687288 LIKE buffer_rule_scores
253 (255.2ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_72687288 LIKE buffer_rule_scores_with_corrections
254 (255.4ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_72687288 LIKE buffer_schedule_linkages
255 (144.1ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_72687288 LIKE buffer_schedules_linkage_patterns
256 (135.4ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_72687288 LIKE buffer_specialization_daily_counts
257 (254.2ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_72687288 LIKE buffer_staff_assigned_days
258 (255.3ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_periods_72687288 LIKE buffer_staff_assignments_count_periods
259 (254.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_rolling_72687288 LIKE buffer_staff_assignments_count_rolling
260 (256.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_weeks_72687288 LIKE buffer_staff_assignments_count_weeks
261 (255.7ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_consecutive_72687288 LIKE buffer_staff_assignments_count_consecutive
262 (256.3ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_72687288 LIKE buffer_staff_daily_counts
263 (253.7ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_72687288 LIKE buffer_staff_specializations
264 (262.2ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_72687288 LIKE buffer_static_availabilities
265 (254.0ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_72687288 LIKE buffer_target_assignments
266 (254.2ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_72687288 LIKE buffer_annual_hour_targets
267 (256.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_72687288 LIKE buffer_annual_point_targets
268 (254.6ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_72687288 LIKE buffer_annual_targets_staff_totals
269 (255.8ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_72687288 LIKE buffer_auto_populate_slots
270 (255.6ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_72687288 LIKE buffer_call_days_targets
271 (258.0ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_72687288 LIKE buffer_call_type_targets
272 (384.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_72687288 LIKE buffer_cumulative_cd_targets
273 (253.6ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_72687288 LIKE buffer_cumulative_ct_targets
274 (257.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_72687288 LIKE buffer_cumulative_dow_targets
275 (253.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_72687288 LIKE buffer_cumulative_hour_targets
276 (258.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_72687288 LIKE buffer_cumulative_oa_targets
277 (253.5ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_72687288 LIKE buffer_cumulative_point_targets
278 (259.7ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_72687288 LIKE buffer_current_cd_targets
279 (254.0ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_72687288 LIKE buffer_current_ct_targets
280 (258.7ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_72687288 LIKE buffer_current_dow_targets
281 (258.8ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_72687288 LIKE buffer_current_hour_targets
282 (257.3ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_72687288 LIKE buffer_current_oa_targets
283 (258.0ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_72687288 LIKE buffer_current_point_targets
284 (253.6ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_72687288 LIKE buffer_day_of_week_targets
285 (253.1ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_72687288 LIKE buffer_hour_targets
286 (253.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_72687288 LIKE buffer_linkage_call_days_targets
287 (262.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_72687288 LIKE buffer_linkage_call_type_targets
288 (254.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_72687288 LIKE buffer_linkage_targets
289 (253.5ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_72687288 LIKE buffer_linkage_hour_targets
290 (254.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_72687288 LIKE buffer_linkage_point_targets
291 (254.7ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_72687288 LIKE buffer_location_privileges
292 (255.3ms) CREATE TABLE IF NOT EXISTS buffer_periods_72687288 LIKE buffer_periods
293 (253.5ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_72687288 LIKE buffer_point_targets
294 (254.7ms) CREATE TABLE IF NOT EXISTS buffer_schedules_72687288 LIKE buffer_schedules
295 (254.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_rotation_points_72687288 LIKE buffer_staff_rotation_points
296 (254.0ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_72687288 LIKE buffer_target_call_types
297 (253.3ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_72687288 LIKE buffer_target_daily_points
298 (254.3ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_72687288 LIKE buffer_target_dates_list
299 (254.0ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_72687288 LIKE buffer_target_primary_availabilities
300 (267.1ms) CREATE TABLE IF NOT EXISTS buffer_targets_72687288 LIKE buffer_targets
301 (256.2ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_72687288 LIKE buffer_tmp_targets_availabilities
302 (253.1ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_72687288 LIKE buffer_tmp_targets_availabilities_exclusions
303 (254.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_72687288 LIKE buffer_tmp_targets_client_totals
304 (253.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_72687288 LIKE buffer_tmp_targets_scheduled
305 (263.2ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_72687288 LIKE buffer_tmp_targets_staff_totals
306INFO : (02-13 16:17:33) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
307 (261.4ms) select
308 b.start_date,
309 b.end_date,
310 (pb.assignment_ids IS NULL) as fully_published,
311 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
312 pbct.days_of_week_mask
313 from blocks as b
314 inner join published_blocks as pb
315 on pb.block_id = b.id
316 inner join published_block_call_types as pbct
317 on pbct.published_block_id = pb.id
318
319 where b.client_id = 319
320 and pb.calendar_schedule_id = 1381
321 and b.end_date BETWEEN '2018-03-01' AND '2018-03-31'
322
323 AutopopulateExclusion Load (252.5ms) SELECT `autopopulate_exclusions`.* FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 319 AND `autopopulate_exclusions`.`schedule_on` IN ('2018-03-01', '2018-03-02', '2018-03-03', '2018-03-04', '2018-03-05', '2018-03-06', '2018-03-07', '2018-03-08', '2018-03-09', '2018-03-10', '2018-03-11', '2018-03-12', '2018-03-13', '2018-03-14', '2018-03-15', '2018-03-16', '2018-03-17', '2018-03-18', '2018-03-19', '2018-03-20', '2018-03-21', '2018-03-22', '2018-03-23', '2018-03-24', '2018-03-25', '2018-03-26', '2018-03-27', '2018-03-28', '2018-03-29', '2018-03-30', '2018-03-31')
324 (277.0ms) INSERT INTO buffer_auto_populate_slots_72687288
325 (schedule_on, day_of_week_mask, call_type_id, display_order, should_be_populated)
326 VALUES ('2018-03-01', '16', '12692', '0', 1), ('2018-03-01', '16', '12693', '1', 1), ('2018-03-01', '16', '12694', '3', 1), ('2018-03-02', '32', '12692', '0', 1), ('2018-03-02', '32', '12693', '1', 1), ('2018-03-02', '32', '12694', '3', 1), ('2018-03-03', '64', '12692', '0', 1), ('2018-03-03', '64', '12693', '1', 1), ('2018-03-03', '64', '12694', '3', 1), ('2018-03-04', '1', '12692', '0', 1), ('2018-03-04', '1', '12693', '1', 1), ('2018-03-04', '1', '12694', '3', 1), ('2018-03-05', '2', '12692', '0', 1), ('2018-03-05', '2', '12693', '1', 1), ('2018-03-05', '2', '12694', '3', 1), ('2018-03-06', '4', '12692', '0', 1), ('2018-03-06', '4', '12693', '1', 1), ('2018-03-06', '4', '12694', '3', 1), ('2018-03-07', '8', '12692', '0', 1), ('2018-03-07', '8', '12693', '1', 1), ('2018-03-07', '8', '12694', '3', 1), ('2018-03-08', '16', '12692', '0', 1), ('2018-03-08', '16', '12693', '1', 1), ('2018-03-08', '16', '12694', '3', 1), ('2018-03-09', '32', '12692', '0', 1), ('2018-03-09', '32', '12693', '1', 1), ('2018-03-09', '32', '12694', '3', 1), ('2018-03-10', '64', '12692', '0', 1), ('2018-03-10', '64', '12693', '1', 1), ('2018-03-10', '64', '12694', '3', 1), ('2018-03-11', '1', '12692', '0', 1), ('2018-03-11', '1', '12693', '1', 1), ('2018-03-11', '1', '12694', '3', 1), ('2018-03-12', '2', '12692', '0', 1), ('2018-03-12', '2', '12693', '1', 1), ('2018-03-12', '2', '12694', '3', 1), ('2018-03-13', '4', '12692', '0', 1), ('2018-03-13', '4', '12693', '1', 1), ('2018-03-13', '4', '12694', '3', 1), ('2018-03-14', '8', '12692', '0', 1), ('2018-03-14', '8', '12693', '1', 1), ('2018-03-14', '8', '12694', '3', 1), ('2018-03-15', '16', '12692', '0', 1), ('2018-03-15', '16', '12693', '1', 1), ('2018-03-15', '16', '12694', '3', 1), ('2018-03-16', '32', '12692', '0', 1), ('2018-03-16', '32', '12693', '1', 1), ('2018-03-16', '32', '12694', '3', 1), ('2018-03-17', '64', '12692', '0', 1), ('2018-03-17', '64', '12693', '1', 1), ('2018-03-17', '64', '12694', '3', 1), ('2018-03-18', '1', '12692', '0', 1), ('2018-03-18', '1', '12693', '1', 1), ('2018-03-18', '1', '12694', '3', 1), ('2018-03-19', '2', '12692', '0', 1), ('2018-03-19', '2', '12693', '1', 1), ('2018-03-19', '2', '12694', '3', 1), ('2018-03-20', '4', '12692', '0', 1), ('2018-03-20', '4', '12693', '1', 1), ('2018-03-20', '4', '12694', '3', 1), ('2018-03-21', '8', '12692', '0', 1), ('2018-03-21', '8', '12693', '1', 1), ('2018-03-21', '8', '12694', '3', 1), ('2018-03-22', '16', '12692', '0', 1), ('2018-03-22', '16', '12693', '1', 1), ('2018-03-22', '16', '12694', '3', 1), ('2018-03-23', '32', '12692', '0', 1), ('2018-03-23', '32', '12693', '1', 1), ('2018-03-23', '32', '12694', '3', 1), ('2018-03-24', '64', '12692', '0', 1), ('2018-03-24', '64', '12693', '1', 1), ('2018-03-24', '64', '12694', '3', 1), ('2018-03-25', '1', '12692', '0', 1), ('2018-03-25', '1', '12693', '1', 1), ('2018-03-25', '1', '12694', '3', 1), ('2018-03-26', '2', '12692', '0', 1), ('2018-03-26', '2', '12693', '1', 1), ('2018-03-26', '2', '12694', '3', 1), ('2018-03-27', '4', '12692', '0', 1), ('2018-03-27', '4', '12693', '1', 1), ('2018-03-27', '4', '12694', '3', 1), ('2018-03-28', '8', '12692', '0', 1), ('2018-03-28', '8', '12693', '1', 1), ('2018-03-28', '8', '12694', '3', 1), ('2018-03-29', '16', '12692', '0', 1), ('2018-03-29', '16', '12693', '1', 1), ('2018-03-29', '16', '12694', '3', 1), ('2018-03-30', '32', '12692', '0', 1), ('2018-03-30', '32', '12693', '1', 1), ('2018-03-30', '32', '12694', '3', 1), ('2018-03-31', '64', '12692', '0', 1), ('2018-03-31', '64', '12693', '1', 1), ('2018-03-31', '64', '12694', '3', 1)
327
328INFO : (02-13 16:17:34) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
329 (261.5ms) insert into buffer_auto_populate_slots_72687288/* client_id */ (
330 schedule_on,
331 day_of_week_mask,
332 call_type_id,
333 display_order,
334 should_be_populated
335 )
336 select distinct straight_join
337 date_add(
338 auto_populate_slots.schedule_on,
339 interval soft_linkages.offset day
340 ) as schedule_on,
341 (1 << (
342 dayofweek(
343 date_add(
344 auto_populate_slots.schedule_on,
345 interval soft_linkages.offset day
346 )
347 ) - 1
348 )
349 ) as day_of_week_mask,
350 soft_linkages.linked_call_type_id as call_type_id,
351 call_types.display_order,
352 0 as should_be_populated
353 from
354 buffer_auto_populate_slots_72687288/* client_id */ as auto_populate_slots
355 inner join soft_linkages
356 on auto_populate_slots.call_type_id = soft_linkages.call_type_id
357 and auto_populate_slots.day_of_week_mask = soft_linkages.day_of_week_mask
358 left join buffer_auto_populate_slots_72687288/* client_id */ as auto_populate_slots_2
359 on date_add(
360 auto_populate_slots.schedule_on,
361 interval soft_linkages.offset day
362 ) = auto_populate_slots_2.schedule_on
363 and soft_linkages.linked_call_type_id = auto_populate_slots_2.call_type_id
364 inner join call_types
365 on soft_linkages.linked_call_type_id = call_types.id
366 where auto_populate_slots_2.id is null
367 order by null
368
369INFO : (02-13 16:17:35) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
370 (274.6ms) insert into buffer_auto_populate_slots_72687288 (
371 schedule_on,
372 day_of_week_mask,
373 call_type_id,
374 display_order,
375 should_be_populated
376 )
377 select distinct straight_join
378 date_add(
379 auto_populate_slots.schedule_on,
380 interval linkage_relations.distance_in_days day
381 ) as schedule_on,
382 (1 << (
383 dayofweek(
384 date_add(
385 auto_populate_slots.schedule_on,
386 interval linkage_relations.distance_in_days day
387 )
388 ) - 1
389 )
390 ) as day_of_week_mask,
391 linkage_relations.linked_call_type_id as call_type_id,
392 call_types.display_order,
393 0 as should_be_populated
394 from
395 buffer_auto_populate_slots_72687288 as auto_populate_slots
396 inner join linkage_relations use index (ix_linkage_relations_composite1)
397 on auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
398 and auto_populate_slots.call_type_id = linkage_relations.call_type_id
399 left join buffer_auto_populate_slots_72687288 as auto_populate_slots_2 use index (ix_buffer_auto_populate_slots_on_date_and_call)
400 on date_add(
401 auto_populate_slots.schedule_on,
402 interval linkage_relations.distance_in_days day
403 ) = auto_populate_slots_2.schedule_on
404 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
405 inner join call_types
406 on linkage_relations.linked_call_type_id = call_types.id
407 where auto_populate_slots_2.id is null
408 order by null
409
410INFO : (02-13 16:17:35) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
411 (253.9ms) insert into linkage_relations (
412 client_id,
413 day_of_week_mask,
414 call_type_id,
415 linked_call_type_id,
416 distance_in_days,
417 linkage_template_id
418 )
419 select distinct straight_join
420 319 /* client_id */ as client_id,
421 aps.day_of_week_mask,
422 aps.call_type_id,
423 aps.call_type_id as linked_call_type_id,
424 0 as distance_in_days,
425 ifnull(
426 lr.linkage_template_id,
427 @linkage_template_max := @linkage_template_max + 1
428 ) as linkage_template_id
429 from
430 buffer_auto_populate_slots_72687288/* client_id */ as aps
431 inner join (
432 select @linkage_template_max := count(distinct linkage_template_id) - 1
433 from linkage_relations
434 where client_id = 319 /* client_id */
435 order by null
436 ) as lt
437 on 1 = 1
438 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
439 on aps.day_of_week_mask = lr.day_of_week_mask
440 and aps.call_type_id = lr.call_type_id
441 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
442 on aps.day_of_week_mask = lr_2.day_of_week_mask
443 and aps.call_type_id = lr_2.call_type_id
444 and aps.call_type_id = lr_2.linked_call_type_id
445 and lr_2.distance_in_days = 0
446 where lr_2.id is null
447 order by null
448
449 (253.5ms) insert into buffer_schedules_72687288 (
450 call_type_id,
451 staff_id,
452 schedule_on,
453 day_of_week_mask,
454 display_order,
455 week_number,
456 should_be_evaluated,
457 should_be_populated,
458 linkage_instance_id
459 )
460 select
461 call_type_id,
462 staff_id,
463 schedule_on,
464 day_of_week_mask,
465 display_order,
466 week_number,
467 max(should_be_evaluated) as should_be_evaluated,
468 max(should_be_populated) as should_be_populated,
469 min(linkage_instance_id) as linkage_instance_id
470 from (
471 select straight_join
472 auto_populate_slots_2.call_type_id,
473 schedules.staff_id,
474 auto_populate_slots_2.schedule_on,
475 auto_populate_slots_2.day_of_week_mask,
476 auto_populate_slots_2.display_order,
477 yearweek(
478 auto_populate_slots_2.schedule_on,
479 if(clients.start_of_week = 0, 2, 7)
480 ) as week_number,
481 if(schedules.staff_id is null, 1, 0) as should_be_evaluated,
482 (
483 case
484 when (auto_populate_slots_2.should_be_populated = 1 and schedules.staff_id is null) then 1
485 else 0
486 end
487 ) as should_be_populated,
488 (
489 linkage_relations.linkage_template_id * 1000000 +
490 yearweek(
491 auto_populate_slots.schedule_on,
492 if(clients.start_of_week = 0, 2, 7)
493 )
494 ) as linkage_instance_id
495 from
496 buffer_auto_populate_slots_72687288 as auto_populate_slots
497 inner join clients
498 on clients.id = 319 /* client_id */
499 inner join linkage_relations use index (ix_linkage_relations_composite1)
500 on auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
501 and auto_populate_slots.call_type_id = linkage_relations.call_type_id
502 inner join buffer_auto_populate_slots_72687288 as auto_populate_slots_2 use index (ix_buffer_auto_populate_slots_on_date_and_call)
503 on date_add(
504 auto_populate_slots.schedule_on,
505 interval linkage_relations.distance_in_days day
506 ) = auto_populate_slots_2.schedule_on
507 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
508 left join schedules use index (ix_schedules_call_date)
509 on auto_populate_slots_2.call_type_id = schedules.call_type_id
510 and auto_populate_slots_2.schedule_on = schedules.schedule_on
511 ) as t1
512 group by
513 call_type_id,
514 staff_id,
515 schedule_on,
516 day_of_week_mask,
517 display_order,
518 week_number
519 order by null
520
521INFO : (02-13 16:17:35) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
522 (267.4ms) update buffer_schedules_72687288 as sc
523 inner join call_types as ct
524 on sc.call_type_id = ct.id
525 inner join calendar_schedules as cs
526 on ct.calendar_schedule_id = cs.id
527 inner join blocks as b
528 on cs.block_schedule_id = b.block_schedule_id
529 and sc.schedule_on between b.start_date and b.end_date
530 set sc.calendar_schedule_id = cs.id,
531 sc.block_id = b.id
532 where sc.linkage_instance_id >= 0
533
534 BlockSchedule Load (265.4ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
535 (252.1ms) insert into buffer_periods_72687288 (
536 calendar_schedule_id, block_schedule_id, period_offset, start_block_display_order, end_block_display_order,
537 start_date, end_date, should_be_populated, should_be_evaluated
538 )
539 select t1.calendar_schedule_id,
540 t1.block_schedule_id,
541 t1.period_offset,
542 t2.start_block_display_order,
543 t2.end_block_display_order,
544 t2.start_date,
545 t2.end_date,
546 t1.should_be_populated,
547 t1.should_be_evaluated
548 from (
549 select sc.calendar_schedule_id,
550 b.block_schedule_id,
551 if (
552 (month(b.start_date) - pt.begin_period) >= 0,
553 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type,
554 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type
555 ) as period_offset,
556 max(sc.should_be_populated) as should_be_populated,
557 max(sc.should_be_evaluated) as should_be_evaluated
558 from (
559 select sc.calendar_schedule_id, sc.block_id,
560 max(ps.should_be_populated) as should_be_populated,
561 max(1) as should_be_evaluated
562 from buffer_schedules_72687288 as sc
563 inner join buffer_auto_populate_slots_72687288 as ps
564 on sc.schedule_on = ps.schedule_on
565 and sc.call_type_id = ps.call_type_id
566 group by sc.calendar_schedule_id, sc.block_id
567 order by null
568 ) as sc
569 inner join blocks as b
570 on sc.block_id = b.id
571 inner join population_timeframes as pt
572 on pt.client_id = 319
573 and pt.timeframe_type = 1
574 group by sc.calendar_schedule_id, b.block_schedule_id, period_offset
575 order by null
576 ) as t1
577 inner join (
578 select b.block_schedule_id,
579 if (
580 (month(b.start_date) - pt.begin_period) >= 0,
581 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type,
582 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type
583 ) as period_offset,
584 min(b.display_order) as start_block_display_order,
585 max(b.display_order) as end_block_display_order,
586 min(b.start_date) as start_date,
587 max(b.end_date) as end_date
588 from blocks as b
589 inner join population_timeframes as pt
590 on pt.client_id = 319
591 and pt.timeframe_type = 1
592 where b.client_id = 319
593 group by b.block_schedule_id, period_offset
594 order by null
595 ) as t2
596 on t1.block_schedule_id = t2.block_schedule_id
597 and t1.period_offset = t2.period_offset
598 order by null
599
600 (265.1ms) update buffer_schedules_72687288 as sc
601 inner join buffer_periods_72687288 as p
602 on sc.calendar_schedule_id = p.calendar_schedule_id
603 and sc.schedule_on between p.start_date and p.end_date
604 set sc.period_offset = p.period_offset
605 where sc.linkage_instance_id >= 0
606
607INFO : (02-13 16:17:37) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
608 (261.6ms) delete from buffer_target_dates
609 where session_id = 72687288
610
611 (264.6ms) select distinct calendar_schedule_id
612 from buffer_periods_72687288
613
614 (255.0ms) insert into buffer_target_dates (
615 session_id,
616 client_id,
617 calendar_schedule_id,
618 block_id,
619 start_date,
620 end_date,
621 block_display_order,
622 period_offset,
623 build_oa_targets,
624 oa_targets_source,
625 copy_oa_targets,
626 build_ct_targets,
627 ct_targets_source,
628 copy_ct_targets,
629 build_dow_targets,
630 dow_targets_source,
631 copy_dow_targets,
632 build_point_targets,
633 point_targets_source,
634 copy_point_targets,
635 build_hour_targets,
636 hour_targets_source,
637 copy_hour_targets,
638 build_cd_targets,
639 cd_targets_source,
640 copy_cd_targets,
641 is_published,
642 is_partially_published,
643 affected_by_populate,
644 within_cumulative_period
645 )
646 select
647 72687288 as session_id,
648 319 as client_id,
649 t1.calendar_schedule_id,
650 t1.block_id,
651 t1.start_date,
652 t1.end_date,
653 t1.block_display_order,
654 t1.period_offset,
655 case
656 when (
657 (
658 FALSE
659 )
660 and t1.is_published = 0
661 and t2_1.block_id is null
662 and t2_2.block_id is null
663 ) then 1
664 else 0
665 end as build_oa_targets,
666 case
667 when (t2_1.block_id is not null) then 'published'
668 when (t2_2.block_id is not null) then 'unpublished'
669 else ''
670 end as oa_targets_source,
671 0 as copy_oa_targets,
672 case
673 when (
674 (
675 t1.within_cumulative_period = 1 /* AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
676 )
677 and t1.is_published = 0
678 ) then (
679 case
680 when (pb.id is null and t3_2.block_id is null) then 1
681 when (pb.has_published = 1 and pb.has_unpublished = 0 and t3_1.block_id is null) then 1
682 when (pb.has_published = 0 and pb.has_unpublished = 1 and t3_2.block_id is null) then 1
683 when (pb.has_published = 1 and pb.has_unpublished = 1 and (t3_1.block_id is null or t3_2.block_id is null)) then 1
684 else 0
685 end
686 )
687 else 0
688 end as build_ct_targets,
689 case
690 when (pb.id is null and t3_1.block_id is not null) then 'published'
691 when (pb.id is null and t3_2.block_id is not null) then 'unpublished'
692 when (pb.has_published = 1 and pb.has_unpublished = 0 and t3_1.block_id is not null) then 'published'
693 when (pb.has_published = 0 and pb.has_unpublished = 1 and t3_2.block_id is not null) then 'unpublished'
694 when (pb.has_published = 1 and pb.has_unpublished = 1 and t3_1.block_id is not null and t3_2.block_id is not null) then 'both'
695 else ''
696 end as ct_targets_source,
697 0 as copy_ct_targets,
698 case
699 when (
700 (
701 FALSE
702 )
703 and t1.is_published = 0
704 and t4_1.block_id is null
705 and t4_2.block_id is null
706 ) then 1
707 else 0
708 end as build_dow_targets,
709 case
710 when (t4_1.block_id is not null) then 'published'
711 when (t4_2.block_id is not null) then 'unpublished'
712 else ''
713 end as dow_targets_source,
714 0 as copy_dow_targets,
715 case
716 when (
717 (
718 FALSE
719 )
720 and t1.is_published = 0
721 and t5_1.block_id is null
722 and t5_2.block_id is null
723 ) then 1
724 else 0
725 end as build_point_targets,
726 case
727 when (t5_1.block_id is not null) then 'published'
728 when (t5_2.block_id is not null) then 'unpublished'
729 else ''
730 end as point_targets_source,
731 0 as copy_point_targets,
732 case
733 when (
734 (
735 FALSE
736 )
737 and t1.is_published = 0
738 and t6_1.block_id is null
739 and t6_2.block_id is null
740 ) then 1
741 else 0
742 end as build_hour_targets,
743 case
744 when (t6_1.block_id is not null) then 'published'
745 when (t6_2.block_id is not null) then 'unpublished'
746 else ''
747 end as hour_targets_source,
748 0 as copy_hour_targets,
749 case
750 when (
751 (
752 FALSE
753 )
754 and t1.is_published = 0
755 ) then (
756 case
757 when (pb.id is null and t7_2.block_id is null) then 1
758 when (pb.has_published = 1 and pb.has_unpublished = 0 and t7_1.block_id is null) then 1
759 when (pb.has_published = 0 and pb.has_unpublished = 1 and t7_2.block_id is null) then 1
760 when (pb.has_published = 1 and pb.has_unpublished = 1 and (t7_1.block_id is null or t7_2.block_id is null)) then 1
761 else 0
762 end
763 )
764 else 0
765 end as build_cd_targets,
766 case
767 when (pb.id is null and t7_1.block_id is not null) then 'published'
768 when (pb.id is null and t7_2.block_id is not null) then 'unpublished'
769 when (pb.has_published = 1 and pb.has_unpublished = 0 and t7_1.block_id is not null) then 'published'
770 when (pb.has_published = 0 and pb.has_unpublished = 1 and t7_2.block_id is not null) then 'unpublished'
771 when (pb.has_published = 1 and pb.has_unpublished = 1 and t7_1.block_id is not null and t7_2.block_id is not null) then 'both'
772 else ''
773 end as cd_targets_source,
774 0 as copy_cd_targets,
775 t1.is_published,
776 case
777 when (pb.id is not null) then 1
778 else 0
779 end as is_partially_published,
780 t1.affected_by_populate,
781 t1.within_cumulative_period
782 from (
783 select
784 t1.calendar_schedule_id,
785 b.id as block_id,
786 b.start_date,
787 b.end_date,
788 t1.block_display_order,
789 p.period_offset,
790 case
791 when (t1.block_display_order between t1.start_block_display_order and t1.end_block_display_order) then 1
792 else 0
793 end as affected_by_populate,
794 case
795 when (t1.block_display_order between (t1.timeframe_ending - (2 - 1)) and t1.timeframe_ending) then 1
796 else 0
797 end as within_cumulative_period,
798 case
799 when (pb.id is not null) then 1
800 else 0
801 end as is_published
802 from (
803 select
804 t1.calendar_schedule_id,
805 t1.block_schedule_id,
806 (t1.end_block_display_order - ns.item_number) as block_display_order,
807 t1.start_block_display_order,
808 t1.end_block_display_order,
809 t1.timeframe_ending
810 from (
811 select
812 calendar_schedule_id,
813 block_schedule_id,
814 min(start_block_display_order) as start_block_display_order,
815 max(end_block_display_order) as end_block_display_order,
816 max(
817 case
818 when (should_be_populated = 1) then end_block_display_order
819 else null
820 end
821 ) as timeframe_ending
822 from
823 buffer_periods_72687288
824 group by
825 calendar_schedule_id,
826 block_schedule_id
827 order by null
828 ) as t1
829 inner join numeric_sequence as ns
830 on ns.item_number < 100
831 and t1.end_block_display_order >= ns.item_number
832 order by null
833 ) as t1
834 inner join blocks as b
835 on t1.block_schedule_id = b.block_schedule_id
836 and t1.block_display_order = b.display_order
837 left join buffer_periods_72687288 as p
838 on t1.calendar_schedule_id = p.calendar_schedule_id
839 and t1.block_display_order between p.start_block_display_order and p.end_block_display_order
840 left join published_blocks as pb
841 on t1.calendar_schedule_id = pb.calendar_schedule_id
842 and b.id = pb.block_id
843 and pb.assignment_ids is null
844 and pb.days_of_week_mask = 127
845 where
846 t1.block_display_order between t1.start_block_display_order and t1.end_block_display_order
847 or t1.block_display_order between (t1.timeframe_ending - (2 - 1)) and t1.timeframe_ending
848 order by null
849 ) as t1
850 left join (
851 select
852 calendar_schedule_id,
853 block_id
854 from
855 targets
856 where
857 calendar_schedule_id in (1381) /* list of calendar schedules */
858 group by
859 calendar_schedule_id,
860 block_id
861 order by null
862 ) as t2_1
863 on t1.calendar_schedule_id = t2_1.calendar_schedule_id
864 and t1.block_id = t2_1.block_id
865 left join (
866 select
867 calendar_schedule_id,
868 block_id
869 from
870 unpublished_targets
871 where
872 calendar_schedule_id in (1381) /* list of calendar schedules */
873 group by
874 calendar_schedule_id,
875 block_id
876 order by null
877 ) as t2_2
878 on t1.calendar_schedule_id = t2_2.calendar_schedule_id
879 and t1.block_id = t2_2.block_id
880 left join (
881 select
882 calendar_schedule_id,
883 block_id
884 from
885 call_type_targets
886 where
887 calendar_schedule_id in (1381) /* list of calendar schedules */
888 group by
889 calendar_schedule_id,
890 block_id
891 order by null
892 ) as t3_1
893 on t1.calendar_schedule_id = t3_1.calendar_schedule_id
894 and t1.block_id = t3_1.block_id
895 left join (
896 select
897 calendar_schedule_id,
898 block_id
899 from
900 unpublished_call_type_targets
901 where
902 calendar_schedule_id in (1381) /* list of calendar schedules */
903 group by
904 calendar_schedule_id,
905 block_id
906 order by null
907 ) as t3_2
908 on t1.calendar_schedule_id = t3_2.calendar_schedule_id
909 and t1.block_id = t3_2.block_id
910 left join (
911 select
912 calendar_schedule_id,
913 block_id
914 from
915 day_of_week_targets
916 where
917 calendar_schedule_id in (1381) /* list of calendar schedules */
918 group by
919 calendar_schedule_id,
920 block_id
921 order by null
922 ) as t4_1
923 on t1.calendar_schedule_id = t4_1.calendar_schedule_id
924 and t1.block_id = t4_1.block_id
925 left join (
926 select
927 calendar_schedule_id,
928 block_id
929 from
930 unpublished_day_of_week_targets
931 where
932 calendar_schedule_id in (1381) /* list of calendar schedules */
933 group by
934 calendar_schedule_id,
935 block_id
936 order by null
937 ) as t4_2
938 on t1.calendar_schedule_id = t4_2.calendar_schedule_id
939 and t1.block_id = t4_2.block_id
940 left join (
941 select
942 calendar_schedule_id,
943 block_id
944 from
945 point_targets
946 where
947 calendar_schedule_id in (1381) /* list of calendar schedules */
948 group by
949 calendar_schedule_id,
950 block_id
951 order by null
952 ) as t5_1
953 on t1.calendar_schedule_id = t5_1.calendar_schedule_id
954 and t1.block_id = t5_1.block_id
955 left join (
956 select
957 calendar_schedule_id,
958 block_id
959 from
960 unpublished_point_targets
961 where
962 calendar_schedule_id in (1381) /* list of calendar schedules */
963 group by
964 calendar_schedule_id,
965 block_id
966 order by null
967 ) as t5_2
968 on t1.calendar_schedule_id = t5_2.calendar_schedule_id
969 and t1.block_id = t5_2.block_id
970 left join (
971 select
972 calendar_schedule_id,
973 block_id
974 from
975 hour_targets
976 where
977 calendar_schedule_id in (1381) /* list of calendar schedules */
978 group by
979 calendar_schedule_id,
980 block_id
981 order by null
982 ) as t6_1
983 on t1.calendar_schedule_id = t6_1.calendar_schedule_id
984 and t1.block_id = t6_1.block_id
985 left join (
986 select
987 calendar_schedule_id,
988 block_id
989 from
990 unpublished_hour_targets
991 where
992 calendar_schedule_id in (1381) /* list of calendar schedules */
993 group by
994 calendar_schedule_id,
995 block_id
996 order by null
997 ) as t6_2
998 on t1.calendar_schedule_id = t6_2.calendar_schedule_id
999 and t1.block_id = t6_2.block_id
1000 left join (
1001 select
1002 calendar_schedule_id,
1003 block_id
1004 from
1005 call_days_targets
1006 where
1007 calendar_schedule_id in (1381) /* list of calendar schedules */
1008 group by
1009 calendar_schedule_id,
1010 block_id
1011 order by null
1012 ) as t7_1
1013 on t1.calendar_schedule_id = t7_1.calendar_schedule_id
1014 and t1.block_id = t7_1.block_id
1015 left join (
1016 select
1017 calendar_schedule_id,
1018 block_id
1019 from
1020 unpublished_call_days_targets
1021 where
1022 calendar_schedule_id in (1381) /* list of calendar schedules */
1023 group by
1024 calendar_schedule_id,
1025 block_id
1026 order by null
1027 ) as t7_2
1028 on t1.calendar_schedule_id = t7_2.calendar_schedule_id
1029 and t1.block_id = t7_2.block_id
1030 left join (
1031 select
1032 pb.id,
1033 pb.calendar_schedule_id,
1034 pb.block_id,
1035 max(
1036 case
1037 when (coalesce(pbct.days_of_week_mask, 0) > 0) then 1
1038 else 0
1039 end
1040 ) as has_published,
1041 max(
1042 case
1043 when (coalesce(pbct.days_of_week_mask, 0) < 127) then 1
1044 else 0
1045 end
1046 ) as has_unpublished
1047 from (
1048 select
1049 case
1050 when (tg.id is null) then ct.id
1051 else tg.id
1052 end as assignment_id,
1053 case
1054 when (tg.id is null) then 'CallType'
1055 else 'TargetGroup'
1056 end as assignment_type,
1057 ct.calendar_schedule_id
1058 from
1059 call_types as ct
1060 left join target_group_calls as tgc
1061 on ct.id = tgc.call_type_id
1062 left join target_groups as tg
1063 on tgc.target_group_id = tg.id
1064 and tg.active = 1
1065 where
1066 ct.calendar_schedule_id in (1381)
1067 and ct.active = 1
1068 and ct.included_in_targets > 0
1069 and ct.days_of_week_mask > 0
1070 group by
1071 assignment_id,
1072 assignment_type,
1073 ct.calendar_schedule_id
1074 order by null
1075 ) as ct
1076 inner join published_blocks as pb
1077 on ct.calendar_schedule_id = pb.calendar_schedule_id
1078 and (
1079 pb.assignment_ids is not null
1080 or pb.days_of_week_mask < 127
1081 )
1082 left join published_block_call_types as pbct
1083 on pb.id = pbct.published_block_id
1084 and ct.assignment_id = pbct.assignment_id
1085 and ct.assignment_type = pbct.assignment_type
1086 group by
1087 pb.id,
1088 pb.calendar_schedule_id,
1089 pb.block_id
1090 order by null
1091 ) as pb
1092 on t1.calendar_schedule_id = pb.calendar_schedule_id
1093 and t1.block_id = pb.block_id
1094 order by null
1095
1096Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0101 - 783.856ms
1097 (258.5ms) /*
1098 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1099 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1100 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1101 */
1102 update buffer_target_dates
1103 set build_cd_targets = 1
1104 where
1105 session_id = 72687288
1106 and build_cd_targets = 0
1107 and cd_targets_source = ''
1108 and (
1109 build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1110 )
1111
1112 (268.0ms) /*
1113 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1114 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1115 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1116 */
1117 update buffer_target_dates
1118 set
1119 build_ct_targets = 1, ct_targets_source = "" /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1120 where
1121 session_id = 72687288
1122 and build_cd_targets = 1
1123
1124Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0102 - 527.4839999999999ms
1125 (261.0ms) /*
1126 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1127 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1128 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1129 */
1130 insert into buffer_call_days_targets_72687288 (
1131 calendar_schedule_id,
1132 block_id,
1133 staff_id,
1134 assignment_id,
1135 assignment_type,
1136 day_of_week_mask,
1137 is_weekend,
1138 raw_target,
1139 target,
1140 initial_target,
1141 scheduled,
1142 available_days
1143 )
1144 select straight_join
1145 cdt.calendar_schedule_id,
1146 cdt.block_id,
1147 cdt.staff_id,
1148 cdt.assignment_id,
1149 cdt.assignment_type,
1150 cdt.day_of_week_mask,
1151 cdt.is_weekend,
1152 cdt.raw_target,
1153 cdt.target,
1154 cdt.initial_target,
1155 cdt.scheduled,
1156 cdt.available_days
1157 from
1158 buffer_target_dates as td
1159 inner join call_days_targets as cdt
1160 on td.calendar_schedule_id = cdt.calendar_schedule_id
1161 and td.block_id = cdt.block_id
1162 where
1163 td.session_id = 72687288
1164 and td.build_cd_targets = 0
1165 and td.cd_targets_source in ('published', 'both')
1166 and (
1167 td.build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1168 )
1169 order by null
1170
1171 (253.0ms) /*
1172 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1173 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1174 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1175 */
1176 insert into buffer_call_days_targets_72687288 (
1177 calendar_schedule_id,
1178 block_id,
1179 staff_id,
1180 assignment_id,
1181 assignment_type,
1182 day_of_week_mask,
1183 is_weekend,
1184 raw_target,
1185 target,
1186 initial_target,
1187 scheduled,
1188 available_days
1189 )
1190 select straight_join
1191 ucdt.calendar_schedule_id,
1192 ucdt.block_id,
1193 ucdt.staff_id,
1194 ucdt.assignment_id,
1195 ucdt.assignment_type,
1196 ucdt.day_of_week_mask,
1197 ucdt.is_weekend,
1198 ucdt.raw_target,
1199 ucdt.target,
1200 ucdt.initial_target,
1201 ucdt.scheduled,
1202 ucdt.available_days
1203 from
1204 buffer_target_dates as td
1205 inner join unpublished_call_days_targets as ucdt
1206 on td.calendar_schedule_id = ucdt.calendar_schedule_id
1207 and td.block_id = ucdt.block_id
1208 where
1209 td.session_id = 72687288
1210 and td.build_cd_targets = 0
1211 and td.cd_targets_source in ('unpublished', 'both')
1212 and (
1213 td.build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1214 )
1215 order by null
1216
1217Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0103 - 515.089ms
1218 (254.9ms) select max(build_cd_targets) as build_cd_targets,
1219 max(build_ct_targets) as build_ct_targets,
1220 max(build_oa_targets) as build_oa_targets,
1221 max(build_dow_targets) as build_dow_targets,
1222 max(build_point_targets) as build_point_targets,
1223 max(build_hour_targets) as build_hour_targets
1224 from buffer_target_dates
1225 where session_id = 72687288
1226 order by null
1227
1228Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets02 - 255.289ms
1229 (260.0ms) /* AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1230 insert into buffer_cumulative_ct_targets_72687288 (
1231 staff_id,
1232 assignment_id,
1233 assignment_type,
1234 is_weekend,
1235 calendar_schedule_id,
1236 raw_target,
1237 adjusted_target,
1238 scheduled,
1239 available_days
1240 )
1241 select
1242 t.staff_id,
1243 t.assignment_id,
1244 t.assignment_type,
1245 t.is_weekend,
1246 t.calendar_schedule_id,
1247 sum(t.raw_target) as raw_target,
1248 sum(t.adjusted_target) as adjusted_target,
1249 sum(t.scheduled) as scheduled,
1250 sum(t.available_days) as available_days
1251 from (
1252 select
1253 t.calendar_schedule_id,
1254 t.block_id,
1255 t.staff_id,
1256 t.assignment_id,
1257 t.assignment_type,
1258 t.is_weekend,
1259 t.raw_target,
1260 t.target as adjusted_target,
1261 t.scheduled,
1262 t.available_days
1263 from buffer_target_dates as td
1264 inner join buffer_call_type_targets_72687288 as t
1265 on td.calendar_schedule_id = t.calendar_schedule_id
1266 and td.block_id = t.block_id
1267 where
1268 td.session_id = 72687288
1269 and td.build_ct_targets = 1
1270 and td.within_cumulative_period = 1
1271 union
1272 select
1273 ctt.calendar_schedule_id,
1274 ctt.block_id,
1275 ctt.staff_id,
1276 ctt.assignment_id,
1277 ctt.assignment_type,
1278 ctt.is_weekend,
1279 ctt.raw_target,
1280 ctt.target,
1281 ctt.scheduled,
1282 ctt.available_days
1283 from buffer_target_dates as td
1284 inner join call_type_targets as ctt
1285 on td.calendar_schedule_id = ctt.calendar_schedule_id
1286 and td.block_id = ctt.block_id
1287 where
1288 td.session_id = 72687288
1289 and td.build_ct_targets = 0
1290 and td.ct_targets_source in ('published', 'both')
1291 and td.within_cumulative_period = 1
1292 union
1293 select
1294 uctt.calendar_schedule_id,
1295 uctt.block_id,
1296 uctt.staff_id,
1297 uctt.assignment_id,
1298 uctt.assignment_type,
1299 uctt.is_weekend,
1300 uctt.raw_target,
1301 uctt.target,
1302 uctt.scheduled,
1303 uctt.available_days
1304 from
1305 buffer_target_dates as td
1306 inner join unpublished_call_type_targets as uctt
1307 on td.calendar_schedule_id = uctt.calendar_schedule_id
1308 and td.block_id = uctt.block_id
1309 where
1310 td.session_id = 72687288
1311 and td.build_ct_targets = 0
1312 and td.ct_targets_source in ('unpublished', 'both')
1313 and td.within_cumulative_period = 1
1314 union all
1315 select
1316 tad.calendar_schedule_id,
1317 tad.block_id,
1318 tad.staff_id,
1319 tad.assignment_id,
1320 tad.assignment_type,
1321 tad.is_weekend,
1322 0 as raw_target,
1323 0 as target,
1324 tad.adjustment_value as scheduled,
1325 0 as available_days
1326 from
1327 buffer_target_dates as td
1328 inner join target_adjustment_details as tad
1329 on td.calendar_schedule_id = tad.calendar_schedule_id
1330 and td.block_id = tad.block_id
1331 and tad.staff_id > 0
1332 where
1333 td.session_id = 72687288
1334 and td.within_cumulative_period = 1
1335 order by null
1336 ) as t
1337 group by
1338 t.staff_id,
1339 t.assignment_id,
1340 t.assignment_type,
1341 t.is_weekend,
1342 t.calendar_schedule_id
1343 order by null
1344
1345Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0702 - 260.81100000000004ms
1346Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0703 - 0.078ms
1347INFO : (02-13 16:17:39) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
1348 (252.9ms) insert into buffer_counter_increments_72687288 (
1349 schedule_id,
1350 calls,
1351 points,
1352 hours,
1353 affects_cumulative_targets,
1354 affects_rotations
1355 )
1356 select
1357 sc.id as schedule_id,
1358 if(ct.included_in_targets > 0, 1, 0) as calls,
1359 ifnull(if(h.observed_date is null or dp.point is not null, dp.point, dp_2.point), 0.0) as points,
1360 ct.hours,
1361 if(b.display_order between (t1.timeframe_ending - (2 /* cumulative targets period */ - 1)) and t1.timeframe_ending, 1, 0) as affects_cumulative_targets,
1362 if(ct.display_concurrent_rotations = 1, 0, 1) as affects_rotations
1363 from buffer_schedules_72687288 as sc
1364 inner join (
1365 select
1366 id as call_type_id,
1367 included_in_targets,
1368 display_concurrent_rotations,
1369 ifnull(
1370 timestampdiff(
1371 minute,
1372 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
1373 concat(
1374 '1900-01-0',
1375 if(
1376 (start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0)
1377 and (timediff(maketime(end_time_hour, end_time_minute, 0), maketime(start_time_hour, start_time_minute, 0)) <= 0),
1378 '2', '1'
1379 ),
1380 ' ',
1381 maketime(end_time_hour, end_time_minute, 0)
1382 )
1383 ) / 60,
1384 0.0
1385 ) as hours
1386 from call_types
1387 where client_id = 319
1388 and active = 1
1389 order by null
1390 ) as ct
1391 on sc.call_type_id = ct.call_type_id
1392 left join (
1393 select
1394 i_hd.observed_date,
1395 if(i_hd.observed_date > i_hd.extended_date, i_hd.extended_date, i_hd.observed_date) as date_from,
1396 if(i_hd.observed_date < i_hd.extended_date, i_hd.extended_date, i_hd.observed_date) as date_to
1397 from holidays as i_h
1398 inner join holiday_dates as i_hd
1399 on i_h.id = i_hd.holiday_id
1400 where
1401 i_h.client_id = 319
1402 and (
1403 i_h.observed = 1
1404 or i_h.extended = 1
1405 )
1406 order by null
1407 ) as h
1408 on sc.schedule_on between h.date_from and h.date_to
1409 left join assignment_annotations as aa
1410 on ct.call_type_id = aa.assignmentable_id
1411 and aa.assignmentable_type = 'CallType'
1412 and aa.annotation_id = 0 /* annotation = "None" */
1413 and aa.applicable = 1
1414 left join assignment_annotation_daily_points as dp
1415 on aa.id = dp.assignment_annotation_id
1416 and if(
1417 h.observed_date is null,
1418 dayofweek(sc.schedule_on) - 1,
1419 if(sc.schedule_on = h.observed_date, 7, 8)
1420 ) = dp.day
1421 left join assignment_annotation_daily_points as dp_2
1422 on aa.id = dp_2.assignment_annotation_id
1423 and dayofweek(sc.schedule_on) - 1 = dp_2.day
1424 left join blocks as b
1425 on sc.block_id = b.id
1426 left join (
1427 select calendar_schedule_id,
1428 max(if(should_be_populated = 1, end_block_display_order, null)) as timeframe_ending
1429 from buffer_periods_72687288
1430 group by calendar_schedule_id
1431 order by null
1432 ) as t1
1433 on sc.calendar_schedule_id = t1.calendar_schedule_id
1434 where sc.should_be_evaluated = 1
1435 order by null
1436
1437INFO : (02-13 16:17:39) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
1438 (253.4ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
1439 insert into buffer_linkage_patterns_72687288 (
1440 period_offset, calendar_schedule_id, block_id, block_start_date, block_start_dow, block_length,
1441 linkage_pattern_id, pattern_start_dow, days_before_start, pattern_length, start_offset, iterations_count
1442 )
1443 select
1444 b1.period_offset,
1445 b1.calendar_schedule_id,
1446 b1.block_id,
1447 b2.start_date as block_start_date,
1448 dayofweek(b2.start_date) as block_start_dow,
1449 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
1450 lp.id as linkage_pattern_id,
1451 (lp.start_on + 1) as pattern_start_dow,
1452 lp.days_before_start,
1453 (lp.length * 7) as pattern_length,
1454 null as start_offset,
1455 null as iterations_count
1456 from (
1457 select calendar_schedule_id, block_id, period_offset
1458 from buffer_schedules_72687288
1459 group by calendar_schedule_id, block_id, period_offset
1460 order by null
1461 ) as b1
1462 inner join blocks as b2
1463 on b1.block_id = b2.id
1464 inner join linkage_patterns as lp
1465 on lp.client_id = 319
1466 and b1.calendar_schedule_id = lp.calendar_schedule_id
1467 order by null
1468
1469 (253.2ms) -- 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
1470 update buffer_linkage_patterns_72687288
1471 set start_offset = if(
1472 (pattern_start_dow + if(pattern_start_dow < block_start_dow, 7, 0) - block_start_dow) <= days_before_start,
1473 (pattern_start_dow + if(pattern_start_dow < block_start_dow, 7, 0) - block_start_dow),
1474 (block_start_dow + if(block_start_dow < pattern_start_dow, 7, 0) - pattern_start_dow) * (-1)
1475 )
1476
1477 (258.9ms) -- 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)
1478 -- a pattern should be repeated couple of times (in order to cover the entire block)
1479 update buffer_linkage_patterns_72687288
1480 set iterations_count = ceiling((block_length + if(start_offset < 0, abs(start_offset), 0)) * 1.0 / pattern_length)
1481
1482 (255.3ms) -- Generate an assignments sequence for every linkage pattern
1483 insert into buffer_schedules_linkage_patterns_72687288 (
1484 call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1485 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1486 )
1487 select
1488 lps.assignment_id as call_type_id,
1489 null as staff_id,
1490 if(
1491 lp.start_offset < 0,
1492 date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day),
1493 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
1494 ) as schedule_on,
1495 lp.period_offset,
1496 null as day_of_week_mask,
1497 null as display_order,
1498 null as week_number,
1499 0 as should_be_evaluated,
1500 0 as should_be_populated,
1501 lp.linkage_pattern_id as linkage_instance_id,
1502 lp.block_id,
1503 lp.calendar_schedule_id
1504 from numeric_sequence as ns
1505 inner join buffer_linkage_patterns_72687288 as lp
1506 on ns.item_number <= (lp.iterations_count - 1)
1507 inner join linkage_pattern_slots as lps
1508 on lp.linkage_pattern_id = lps.linkage_pattern_id
1509 and lps.assignment_type = 'CallType'
1510 where
1511 if(
1512 lp.start_offset < 0,
1513 (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset),
1514 (ns.item_number * lp.pattern_length + lps.offset)
1515 ) between 0 and (lp.block_length - 1)
1516 order by null
1517
1518 (254.1ms) -- Fill the columns that we left blank in the previous "INSERT"
1519 update buffer_schedules_linkage_patterns_72687288 as slp
1520 inner join clients as cl
1521 on cl.id = 319
1522 inner join call_types as ct
1523 on slp.call_type_id = ct.id
1524 left join buffer_schedules_72687288 as bs
1525 on slp.schedule_on = bs.schedule_on
1526 and slp.call_type_id = bs.call_type_id
1527 set slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
1528 slp.display_order = ct.display_order,
1529 slp.week_number = yearweek(slp.schedule_on, if(cl.start_of_week = 0, 2, 7)),
1530 slp.should_be_evaluated = ifnull(bs.should_be_evaluated, 0),
1531 slp.should_be_populated = ifnull(bs.should_be_populated, 0)
1532
1533 (257.0ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
1534 delete slp_1
1535 from buffer_schedules_linkage_patterns_72687288 as slp_1
1536 inner join (
1537 select linkage_instance_id
1538 from buffer_schedules_linkage_patterns_72687288
1539 group by linkage_instance_id
1540 having max(should_be_evaluated) = 0
1541 and max(should_be_populated) = 0
1542 order by null
1543 ) as slp_2
1544 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
1545
1546 (268.6ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
1547 update buffer_schedules_linkage_patterns_72687288 as slp
1548 inner join buffer_schedules_72687288 as bs_1
1549 on slp.schedule_on = bs_1.schedule_on
1550 and slp.call_type_id = bs_1.call_type_id
1551 inner join buffer_schedules_72687288 as bs_2
1552 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
1553 set bs_2.linkage_instance_id = slp.linkage_instance_id
1554
1555 (266.9ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
1556 -- should be copied over to the buffer schedules table (used during the auto-populate process)
1557 insert into buffer_schedules_72687288 (
1558 call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1559 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1560 )
1561 select call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1562 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1563 from buffer_schedules_linkage_patterns_72687288
1564 where should_be_evaluated = 0
1565 and should_be_populated = 0
1566
1567INFO : (02-13 16:17:41) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query015
1568 (288.3ms) insert into buffer_schedules_72687288 (
1569 call_type_id, staff_id, schedule_on, day_of_week_mask, display_order,
1570 should_be_evaluated, should_be_populated, linkage_instance_id
1571 )
1572 select distinct
1573 schedules_call_overlap.call_type_id,
1574 schedules_call_overlap.staff_id,
1575 schedules_call_overlap.schedule_on,
1576 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
1577 call_types.display_order,
1578 0 as should_be_evaluated,
1579 0 as should_be_populated,
1580 -1 as linkage_instance_id
1581 from (
1582 select schedule_on
1583 from buffer_schedules_72687288
1584 where should_be_evaluated = 1
1585 group by schedule_on
1586 order by null
1587 ) as buffer_schedules_1
1588 inner join schedules as schedules_call_overlap
1589 on schedules_call_overlap.client_id = 319
1590 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1591 left join buffer_schedules_72687288 as buffer_schedules_2
1592 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1593 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1594 inner join call_types
1595 on schedules_call_overlap.call_type_id = call_types.id
1596 where buffer_schedules_2.id is null
1597 order by null
1598
1599INFO : (02-13 16:17:41) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1600INFO : (02-13 16:17:41) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1601INFO : (02-13 16:17:41) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1602INFO : (02-13 16:17:41) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1603INFO : (02-13 16:17:41) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1604 (250.3ms) insert into buffer_schedules_72687288 (
1605 call_type_id,
1606 staff_id,
1607 schedule_on,
1608 day_of_week_mask,
1609 display_order,
1610 should_be_evaluated,
1611 should_be_populated,
1612 linkage_instance_id
1613 )
1614 select distinct straight_join
1615 schedules.call_type_id,
1616 schedules.staff_id,
1617 schedules.schedule_on,
1618 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1619 call_types.display_order,
1620 0 as should_be_evaluated,
1621 0 as should_be_populated,
1622 -1 as linkage_instance_id
1623 from
1624 buffer_schedules_72687288 as buffer_schedules_1
1625 inner join negative_links_influences use index (ix_unique_links)
1626 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1627 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1628 and negative_links_influences.source_type = 'CallType'
1629 and negative_links_influences.target_type = 'CallType'
1630 inner join schedules use index (ix_schedules_call_date)
1631 on date_add(
1632 buffer_schedules_1.schedule_on,
1633 interval negative_links_influences.offset day
1634 ) = schedules.schedule_on
1635 and negative_links_influences.target_id = schedules.call_type_id
1636 left join buffer_schedules_72687288 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1637 on schedules.schedule_on = buffer_schedules_2.schedule_on
1638 and schedules.call_type_id = buffer_schedules_2.call_type_id
1639 inner join call_types
1640 on schedules.call_type_id = call_types.id
1641 where
1642 buffer_schedules_1.linkage_instance_id >= 0
1643 and buffer_schedules_1.should_be_evaluated = 1
1644 and buffer_schedules_2.id is null
1645 order by null
1646
1647INFO : (02-13 16:17:42) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1648 (253.3ms) insert into buffer_schedules_72687288 (
1649 call_type_id,
1650 staff_id,
1651 schedule_on,
1652 day_of_week_mask,
1653 display_order,
1654 should_be_evaluated,
1655 should_be_populated,
1656 linkage_instance_id
1657 )
1658 select distinct straight_join
1659 0 as call_type_id,
1660 vacations.staff_id,
1661 vacations.schedule_on,
1662 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1663 -1 as display_order,
1664 0 as should_be_evaluated,
1665 0 as should_be_populated,
1666 -1 as linkage_instance_id
1667 from
1668 buffer_schedules_72687288 as buffer_schedules_1
1669 inner join negative_links_influences use index (ix_unique_links)
1670 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1671 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1672 and negative_links_influences.source_type = 'CallType'
1673 and negative_links_influences.target_id is null
1674 and negative_links_influences.target_type = 'Vacation'
1675 inner join vacations use index (ix_vacations_on_schedule_and_client)
1676 on vacations.client_id = 319 /* client_id */
1677 and date_add(
1678 buffer_schedules_1.schedule_on,
1679 interval negative_links_influences.offset day
1680 ) = vacations.schedule_on
1681 and vacations.vacation_type = 0 /* vacation */
1682 and vacations.status in (3) /* based on the "non-working requests" settings */
1683 left join buffer_schedules_72687288 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1684 on vacations.staff_id = buffer_schedules_2.staff_id
1685 and vacations.schedule_on = buffer_schedules_2.schedule_on
1686 and buffer_schedules_2.call_type_id = 0
1687 where
1688 buffer_schedules_1.linkage_instance_id >= 0
1689 and buffer_schedules_1.should_be_evaluated = 1
1690 and buffer_schedules_2.id is null
1691 order by null
1692
1693 (253.3ms) insert into buffer_schedules_72687288 (
1694 call_type_id,
1695 staff_id,
1696 schedule_on,
1697 day_of_week_mask,
1698 display_order,
1699 should_be_evaluated,
1700 should_be_populated,
1701 linkage_instance_id
1702 )
1703 select distinct straight_join
1704 (-1 * negative_links_influences.target_id) as call_type_id,
1705 vacations.staff_id,
1706 vacations.schedule_on,
1707 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1708 -1 as display_order,
1709 0 as should_be_evaluated,
1710 0 as should_be_populated,
1711 -1 as linkage_instance_id
1712 from
1713 buffer_schedules_72687288 as buffer_schedules_1
1714 inner join negative_links_influences use index (ix_unique_links)
1715 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1716 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1717 and negative_links_influences.source_type = 'CallType'
1718 and negative_links_influences.target_id > 0
1719 and negative_links_influences.target_type = 'NonClinicalType'
1720 inner join vacations use index (ix_vacations_on_schedule_and_client)
1721 on vacations.client_id = 319 /* client_id */
1722 and date_add(
1723 buffer_schedules_1.schedule_on,
1724 interval negative_links_influences.offset day
1725 ) = vacations.schedule_on
1726 and vacations.vacation_type = 1 /* non-clinical */
1727 and negative_links_influences.target_id = vacations.non_clinical_type_id
1728 and vacations.status in (3) /* based on the "non-working requests" settings */
1729 left join buffer_schedules_72687288 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1730 on vacations.staff_id = buffer_schedules_2.staff_id
1731 and vacations.schedule_on = buffer_schedules_2.schedule_on
1732 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1733 where
1734 buffer_schedules_1.linkage_instance_id >= 0
1735 and buffer_schedules_1.should_be_evaluated = 1
1736 and buffer_schedules_2.id is null
1737 order by null;
1738
1739INFO : (02-13 16:17:42) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1740 (254.4ms) insert into buffer_linkages_72687288 (
1741 linkage_instance_id,
1742 slots_count_total,
1743 has_conflicts
1744 )
1745 select straight_join
1746 schedules.linkage_instance_id,
1747 count(1) as slots_count_total,
1748 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1749 from
1750 buffer_schedules_72687288 as schedules
1751 left join (
1752 select straight_join
1753 linkage_relations.linkage_template_id
1754 from linkage_relations
1755 inner join negative_links_influences use index (ix_unique_links)
1756 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1757 and linkage_relations.call_type_id = negative_links_influences.source_id
1758 and negative_links_influences.source_type = 'CallType'
1759 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1760 and negative_links_influences.target_type = 'CallType'
1761 and linkage_relations.distance_in_days = negative_links_influences.offset
1762 where linkage_relations.client_id = 319 /* client_id */
1763 and (
1764 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1765 or linkage_relations.distance_in_days != 0
1766 )
1767 group by linkage_relations.linkage_template_id
1768 order by null
1769 ) as linkage_conflicts
1770 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1771 where schedules.linkage_instance_id >= 0
1772 group by
1773 schedules.linkage_instance_id,
1774 has_conflicts
1775 order by null
1776
1777INFO : (02-13 16:17:42) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1778 (254.2ms) insert into buffer_target_assignments_72687288 (
1779 call_type_id,
1780 target_group_id,
1781 assignment_id,
1782 assignment_type
1783 )
1784 select
1785 t1.call_type_id,
1786 ifnull(tg.id, 0) as target_group_id,
1787 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1788 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1789 from (
1790 select ct.id as call_type_id
1791 from buffer_schedules_72687288 as sc
1792 inner join call_types as ct
1793 on sc.call_type_id = ct.id
1794 and ct.active = 1
1795 and ct.included_in_targets > 0
1796 where linkage_instance_id >= 0
1797 group by ct.id
1798 order by null
1799 ) as t1
1800 left join target_group_calls as tgc
1801 on t1.call_type_id = tgc.call_type_id
1802 left join target_groups as tg
1803 on tgc.target_group_id = tg.id
1804 and tg.active = 1
1805 order by null;
1806
1807
1808INFO : (02-13 16:17:43) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1809INFO : (02-13 16:17:43) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1810 (256.0ms) insert into buffer_linkage_call_type_targets_72687288 (
1811 linkage_instance_id,
1812 calendar_schedule_id,
1813 period_offset,
1814 assignment_id,
1815 assignment_type,
1816 weekday_targets,
1817 weekend_targets
1818 )
1819 select s.linkage_instance_id,
1820 s.calendar_schedule_id,
1821 -1 as period_offset,
1822 ta.assignment_id,
1823 ta.assignment_type,
1824 sum(if(s.day_of_week_mask & 62 > 0, ci.calls, 0)) as weekday_targets,
1825 sum(if(s.day_of_week_mask & 65 > 0, ci.calls, 0)) as weekend_targets
1826 from buffer_schedules_72687288 as s
1827 inner join buffer_target_assignments_72687288 as ta
1828 on s.call_type_id = ta.call_type_id
1829 inner join buffer_counter_increments_72687288 as ci
1830 on s.id = ci.schedule_id
1831 and ci.affects_cumulative_targets = 1
1832 where
1833 s.linkage_instance_id >= 0
1834 and s.staff_id is null
1835 group by
1836 s.linkage_instance_id,
1837 s.calendar_schedule_id,
1838 ta.assignment_id,
1839 ta.assignment_type
1840 order by null
1841
1842INFO : (02-13 16:17:43) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0703
1843INFO : (02-13 16:17:43) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0704
1844INFO : (02-13 16:17:43) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0705
1845 (252.4ms) insert into buffer_linkage_call_type_assignments_72687288 (
1846 linkage_instance_id,
1847 calendar_schedule_id,
1848 period_offset,
1849 call_type_id,
1850 weekday_assignments,
1851 max_weekday_assignments,
1852 weekend_assignments,
1853 max_weekend_assignments
1854 )
1855 select
1856 s.linkage_instance_id,
1857 s.calendar_schedule_id,
1858 s.period_offset,
1859 s.call_type_id,
1860 sum(if(s.day_of_week_mask & 62 > 0, 1, 0)) as weekday_assignments,
1861 ct.max_weekday_assignments,
1862 sum(if(s.day_of_week_mask & 65 > 0, 1, 0)) as weekend_assignments,
1863 ct.max_weekend_assignments
1864 from buffer_schedules_72687288 as s
1865 inner join call_types as ct
1866 on s.call_type_id = ct.id
1867 and ct.active = 1
1868 where s.linkage_instance_id >= 0
1869 and s.staff_id is null
1870 group by
1871 s.linkage_instance_id,
1872 s.calendar_schedule_id,
1873 s.period_offset,
1874 s.call_type_id
1875 order by null
1876
1877INFO : (02-13 16:17:43) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0706
1878 (256.3ms) insert into buffer_call_type_assignments_72687288 (
1879 calendar_schedule_id,
1880 period_offset,
1881 staff_id,
1882 call_type_id,
1883 weekday_assignments,
1884 weekend_assignments
1885 )
1886 select straight_join
1887 p.calendar_schedule_id,
1888 p.period_offset,
1889 sc.staff_id,
1890 sc.call_type_id,
1891 sum(if((dayofweek(sc.schedule_on) - 1) not in (0, 6), 1, 0)) as weekday_assignments,
1892 sum(if((dayofweek(sc.schedule_on) - 1) in (0, 6), 1, 0)) as weekend_assignments
1893 from buffer_periods_72687288 as p
1894 inner join schedules as sc
1895 on sc.client_id = 319
1896 and sc.schedule_on between p.start_date and p.end_date
1897 and sc.staff_id is not null
1898 inner join call_types as ct
1899 on sc.call_type_id = ct.id
1900 and p.calendar_schedule_id = ct.calendar_schedule_id
1901 and ct.active = 1
1902 where p.should_be_evaluated = 1
1903 group by
1904 p.calendar_schedule_id,
1905 p.period_offset,
1906 sc.staff_id,
1907 sc.call_type_id
1908 order by null
1909
1910INFO : (02-13 16:17:44) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0707
1911INFO : (02-13 16:17:44) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query0708
1912 (252.8ms) insert into buffer_linkage_assigned_days_72687288 (
1913 linkage_instance_id,
1914 period_offset,
1915 week_number,
1916 schedule_on,
1917 is_weekend
1918 )
1919 select
1920 sc.linkage_instance_id,
1921 sc.period_offset,
1922 sc.week_number,
1923 sc.schedule_on,
1924 if(dayofweek(sc.schedule_on) not in (7, 1), 0, 1) as is_weekend
1925 from
1926 buffer_schedules_72687288 as sc
1927 inner join call_types as ct
1928 on sc.call_type_id = ct.id
1929 and ct.unassigned_day = 0
1930 where
1931 sc.linkage_instance_id >= 0
1932 and sc.staff_id is null
1933 group by
1934 sc.linkage_instance_id,
1935 sc.period_offset,
1936 sc.week_number,
1937 sc.schedule_on,
1938 is_weekend
1939 order by null
1940
1941INFO : (02-13 16:17:44) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query0709
1942INFO : (02-13 16:17:44) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL
1943 (266.7ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
1944 insert into buffer_assignment_limit_call_types_72687288 (
1945 assignment_limit_id, call_type_id
1946 )
1947 select al.id as assignment_limit_id, ct.id as call_type_id
1948 from assignment_limits as al
1949 inner join assignment_limit_call_types as alct
1950 on al.id = alct.assignment_limit_id
1951 inner join call_types as ct
1952 on alct.call_type_id = ct.id
1953 and ct.active = 1
1954 where al.client_id = 319
1955 and al.active = 1
1956 union
1957 select al.id as assignment_limit_id, ct.id as call_type_id
1958 from assignment_limits as al
1959 inner join call_types as ct
1960 on al.client_id = ct.client_id
1961 and al.calendar_schedule_id = ct.calendar_schedule_id
1962 and ct.active = 1
1963 where al.client_id = 319
1964 and al.active = 1
1965 and al.all_current_schedule_assignments = 1
1966 union
1967 select al.id as assignment_limit_id, ct.id as call_type_id
1968 from assignment_limits as al
1969 inner join call_types as ct
1970 on al.client_id = ct.client_id
1971 and al.calendar_schedule_id != ct.calendar_schedule_id
1972 and ct.active = 1
1973 where al.client_id = 319
1974 and al.active = 1
1975 and al.all_other_schedule_assignments = 1
1976 order by null
1977
1978 (266.2ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
1979 insert into buffer_assignment_limit_staff_types_72687288 (
1980 assignment_limit_id, staff_type_id
1981 )
1982 select al.id as assignment_limit_id, st.id as staff_type_id
1983 from assignment_limits as al
1984 inner join assignment_limit_staff_types as alst
1985 on al.id = alst.assignment_limit_id
1986 inner join staff_types as st
1987 on alst.staff_type_id = st.id
1988 and st.active = 1
1989 where al.client_id = 319
1990 and al.active = 1
1991 union
1992 select al.id as assignment_limit_id, st.id as staff_type_id
1993 from assignment_limits as al
1994 inner join staff_types as st
1995 on al.client_id = st.client_id
1996 and st.active = 1
1997 where al.client_id = 319
1998 and al.active = 1
1999 and al.all_staff_types = 1
2000 order by null
2001
2002INFO : (02-13 16:17:44) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
2003 (255.7ms) /* AssignmentLimitsPerPeriod */
2004 insert into buffer_assignment_limit_periods_72687288 (
2005 assignment_limit_id, period_id, start_date, end_date
2006 )
2007 select
2008 al.id as assignment_limit_id,
2009 if(
2010 bs.blocks_type = 0,
2011 if(
2012 (month(b.start_date) - pt.begin_period) >= 0,
2013 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type + 1,
2014 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type + 1
2015 ),
2016 b.display_order
2017 ) as period_id,
2018 min(b.start_date) as start_date,
2019 max(b.end_date) as end_date
2020 from assignment_limits as al
2021 inner join calendar_schedules as cs
2022 on al.calendar_schedule_id = cs.id
2023 inner join block_schedules as bs
2024 on cs.block_schedule_id = bs.id
2025 inner join blocks as b
2026 on bs.id = b.block_schedule_id
2027 and b.end_date >= date_add(now(), interval -24 month)
2028 inner join population_timeframes as pt
2029 on pt.client_id = 319
2030 and al.timeframe_type = pt.timeframe_type
2031 where al.client_id = 319
2032 and al.active = 1
2033 and al.max_per_period_limit is not null
2034 group by al.id, period_id
2035 order by null
2036
2037 (266.7ms) /* AssignmentLimitsPerPeriod */
2038 insert into buffer_linkage_assignments_count_periods_72687288 (
2039 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2040 )
2041 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date,
2042 count(distinct sc.id) as assignments_count
2043 from buffer_schedules_72687288 as sc
2044 inner join buffer_assignment_limit_call_types_72687288 as alct
2045 on sc.call_type_id = alct.call_type_id
2046 inner join assignment_limits as al
2047 on alct.assignment_limit_id = al.id
2048 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2049 and al.max_per_period_limit is not null
2050 inner join buffer_assignment_limit_periods_72687288 as alp
2051 on alct.assignment_limit_id = alp.assignment_limit_id
2052 and sc.schedule_on between alp.start_date and alp.end_date
2053 where sc.linkage_instance_id >= 0
2054 and sc.staff_id is null
2055 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date
2056 order by null
2057
2058INFO : (02-13 16:17:45) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
2059 (253.9ms) /* AssignmentLimitsPerWeek */
2060 insert into buffer_linkage_assignments_count_weeks_72687288 (
2061 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2062 )
2063 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2064 if(
2065 cl.start_of_week = 0,
2066 date_add(sc.schedule_on, interval (1 - dayofweek(sc.schedule_on)) day),
2067 date_add(sc.schedule_on, interval (0 - weekday(sc.schedule_on)) day)
2068 ) as start_date,
2069 if(
2070 cl.start_of_week = 0,
2071 date_add(sc.schedule_on, interval (7 - dayofweek(sc.schedule_on)) day),
2072 date_add(sc.schedule_on, interval (6 - weekday(sc.schedule_on)) day)
2073 ) as end_date,
2074 count(distinct sc.id) as assignments_count
2075 from buffer_schedules_72687288 as sc
2076 inner join buffer_assignment_limit_call_types_72687288 as alct
2077 on sc.call_type_id = alct.call_type_id
2078 inner join assignment_limits as al
2079 on alct.assignment_limit_id = al.id
2080 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2081 and al.max_weekly_limit is not null
2082 inner join clients as cl
2083 on cl.id = 319
2084 where sc.linkage_instance_id >= 0
2085 and sc.staff_id is null
2086 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2087 order by null
2088
2089INFO : (02-13 16:17:45) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
2090 (261.6ms) /* AssignmentLimitsRolling */
2091 insert into buffer_linkage_assignments_count_rolling_72687288 (
2092 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2093 )
2094 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2095 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as start_date,
2096 date_add(sc.schedule_on, interval (al.max_rolling_period - ns.item_number) day) as end_date,
2097 count(distinct sc.id) as assignments_count
2098 from buffer_schedules_72687288 as sc
2099 inner join buffer_assignment_limit_call_types_72687288 as alct
2100 on sc.call_type_id = alct.call_type_id
2101 inner join assignment_limits as al
2102 on alct.assignment_limit_id = al.id
2103 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2104 and al.max_rolling_period is not null
2105 and al.max_rolling_limit is not null
2106 inner join numeric_sequence as ns
2107 on ns.item_number between 1 and al.max_rolling_period
2108 where sc.linkage_instance_id >= 0
2109 and sc.staff_id is null
2110 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2111 order by null
2112
2113INFO : (02-13 16:17:45) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
2114 (253.3ms) /* AssignmentLimitsConsecutive */
2115 insert into buffer_linkage_assignments_count_consecutive_72687288 (
2116 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date
2117 )
2118 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2119 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as start_date,
2120 date_add(sc.schedule_on, interval ((al.max_consecutive_limit + 1) - ns.item_number) day) as end_date
2121 from buffer_schedules_72687288 as sc
2122 inner join buffer_assignment_limit_call_types_72687288 as alct
2123 on sc.call_type_id = alct.call_type_id
2124 inner join assignment_limits as al
2125 on alct.assignment_limit_id = al.id
2126 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2127 and al.max_consecutive_limit is not null
2128 inner join numeric_sequence as ns
2129 on ns.item_number between 1 and (al.max_consecutive_limit + 1)
2130 where sc.linkage_instance_id >= 0
2131 and sc.staff_id is null
2132 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2133 order by null
2134
2135INFO : (02-13 16:17:46) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query08
2136 (254.8ms) insert into buffer_staff_daily_counts_72687288 (
2137 staff_id, schedule_on, linkage_instance_id, slots_count
2138 )
2139 select staff_id, schedule_on, null as linkage_instance_id,
2140 count(1) as slots_count
2141 from buffer_schedules_72687288
2142 where staff_id is not null
2143 and call_type_id > 0 /* VC */
2144 group by staff_id, schedule_on
2145 order by null
2146
2147 (253.7ms) insert into buffer_staff_daily_counts_72687288 (
2148 staff_id, schedule_on, linkage_instance_id, slots_count
2149 )
2150 select staff_id, schedule_on, linkage_instance_id,
2151 count(1) as slots_count
2152 from buffer_schedules_72687288
2153 where staff_id is not null
2154 and call_type_id > 0 /* VC */
2155 group by staff_id, schedule_on, linkage_instance_id
2156 order by null
2157
2158INFO : (02-13 16:17:46) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query085
2159 (262.4ms) insert into buffer_staff_specializations_72687288 (
2160 staff_id, specialization_id
2161 )
2162 select staff_id, specialization_id
2163 from view_staff_specializations
2164 where client_id = 319
2165 order by null
2166
2167 (269.7ms) insert into buffer_specialization_daily_counts_72687288 (
2168 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2169 )
2170 select sp.id as specialization_id,
2171 null as staff_id,
2172 bs_1.schedule_on,
2173 count(distinct st_sp.staff_id) as staffs_scheduled,
2174 sp.max_scheduled,
2175 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
2176 from (
2177 select schedule_on, day_of_week_mask
2178 from buffer_schedules_72687288
2179 where should_be_evaluated = 1
2180 group by schedule_on, day_of_week_mask
2181 order by null
2182 ) as bs_1
2183 inner join specializations as sp
2184 on sp.client_id = 319
2185 and sp.active = 1
2186 inner join buffer_schedules_72687288 as bs_2
2187 on bs_1.schedule_on = bs_2.schedule_on
2188 and bs_2.call_type_id > 0 /* is not VC */
2189 inner join max_applicable_call_types as mact
2190 on sp.id = mact.specialization_id
2191 and bs_2.call_type_id = mact.call_type_id
2192 left join buffer_staff_specializations_72687288 as st_sp
2193 on bs_2.staff_id = st_sp.staff_id
2194 and sp.id = st_sp.specialization_id
2195 group by sp.id, bs_1.schedule_on, sp.max_scheduled
2196 order by null
2197
2198 (350.7ms) insert into buffer_specialization_daily_counts_72687288 (
2199 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2200 )
2201 select sp.id as specialization_id,
2202 st_sp.staff_id,
2203 bs_1.schedule_on,
2204 1 as staffs_scheduled,
2205 sp.max_scheduled,
2206 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
2207 from (
2208 select schedule_on, day_of_week_mask
2209 from buffer_schedules_72687288
2210 where should_be_evaluated = 1
2211 group by schedule_on, day_of_week_mask
2212 order by null
2213 ) as bs_1
2214 inner join specializations as sp
2215 on sp.client_id = 319
2216 and sp.active = 1
2217 inner join buffer_schedules_72687288 as bs_2
2218 on bs_1.schedule_on = bs_2.schedule_on
2219 and bs_2.call_type_id > 0 /* is not VC */
2220 inner join max_applicable_call_types as mact
2221 on sp.id = mact.specialization_id
2222 and bs_2.call_type_id = mact.call_type_id
2223 inner join buffer_staff_specializations_72687288 as st_sp
2224 on bs_2.staff_id = st_sp.staff_id
2225 and sp.id = st_sp.specialization_id
2226 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
2227 order by null
2228
2229INFO : (02-13 16:17:47) --> fill: 34 - Scheduler::AutoPopulate::Queries::Query09
2230 (252.6ms) truncate table buffer_location_privileges_72687288
2231
2232 (267.9ms) insert into buffer_location_privileges_72687288 (
2233 staff_id,
2234 location_id,
2235 begin_date,
2236 end_date
2237 )
2238 select
2239 s.id as staff_id,
2240 l.location_id,
2241 '0000-00-00' as begin_date,
2242 '9999-12-31' as end_date
2243 from (
2244 select l.id as location_id
2245 from
2246 buffer_schedules_72687288 as sc
2247 inner join call_types as ct
2248 on sc.call_type_id = ct.id
2249 and ct.active = 1
2250 inner join locations as l
2251 on ct.location_id = l.id
2252 and l.active = 1
2253 where sc.linkage_instance_id >= 0
2254 and sc.should_be_evaluated = 1
2255 group by l.id
2256 order by null
2257 ) as l
2258 inner join staffs as s
2259 on s.client_id = 319
2260 and s.active = 1
2261 and s.take_call = 1
2262 left join location_privileges as lp
2263 on s.id = lp.staff_id
2264 where lp.id is null
2265
2266 union
2267
2268 select
2269 lp.staff_id,
2270 lp.location_id,
2271 lp.begin_date,
2272 lp.end_date
2273 from (
2274 select l.id as location_id
2275 from
2276 buffer_schedules_72687288 as sc
2277 inner join call_types as ct
2278 on sc.call_type_id = ct.id
2279 and ct.active = 1
2280 inner join locations as l
2281 on ct.location_id = l.id
2282 and l.active = 1
2283 where sc.linkage_instance_id >= 0
2284 and sc.should_be_evaluated = 1
2285 group by l.id
2286 order by null
2287 ) as l
2288 inner join location_privileges as lp
2289 on l.location_id = lp.location_id
2290 order by null;
2291
2292 (253.3ms) insert into buffer_static_availabilities_72687288 (
2293 schedule_id,
2294 staff_id,
2295 staff_type_id,
2296 is_primary
2297 )
2298 select
2299 schedules.id as schedule_id,
2300 staffs.id as staff_id,
2301 assign_staff_types.staff_type_id,
2302 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
2303 from
2304 buffer_schedules_72687288 as schedules
2305 /* AvailabilityRule */
2306 inner join call_types
2307 on schedules.call_type_id = call_types.id
2308 and call_types.active = 1
2309 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
2310 inner join staff_call_types
2311 on call_types.id = staff_call_types.call_type_id
2312 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
2313 inner join staffs
2314 on staff_call_types.staff_id = staffs.id
2315 and staffs.active = 1
2316 and staffs.take_call = 1
2317 and staffs.auto_populatable = 1
2318 inner join buffer_location_privileges_72687288 as location_privileges
2319 on staffs.id = location_privileges.staff_id
2320 and call_types.location_id = location_privileges.location_id
2321 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
2322 inner join assign_staff_types
2323 on staffs.id = assign_staff_types.staff_id
2324 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
2325 left join eligible_staff_types
2326 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
2327 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
2328 left join staff_rotations
2329 on staffs.id = staff_rotations.staff_id
2330 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
2331 left join daily_rotations
2332 on staffs.id = daily_rotations.staff_id
2333 and schedules.schedule_on = daily_rotations.schedule_on
2334 left join rotations
2335 on rotations.id = ifnull(daily_rotations.rotation_id, staff_rotations.rotation_id)
2336 and rotations.active = 1
2337 left join rotation_call_types
2338 on rotations.id = rotation_call_types.rotation_id
2339 and staff_call_types.call_type_id = rotation_call_types.call_type_id
2340 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
2341 /* RequiredRotationsRule */
2342 left join required_rotations
2343 on staff_call_types.call_type_id = required_rotations.call_type_id
2344 left join staff_rotations as staff_rotations2
2345 on staffs.id = staff_rotations2.staff_id
2346 and required_rotations.rotation_id = staff_rotations2.rotation_id
2347 and schedules.schedule_on >= staff_rotations2.start_date
2348 /* NoCallRequestsHiddenHardRule */
2349 left join request_settings
2350 on assign_staff_types.staff_type_id = request_settings.staff_type_id
2351 left join requests
2352 on schedules.schedule_on = requests.schedule_on
2353 and staffs.id = requests.staff_id
2354 and requests.request_type = 0 /* type = no_call */
2355 and requests.status in (1, 3) /* status = approved or fulfilled */
2356 and call_types.is_no_call_requestable = 1
2357 left join request_call_types
2358 on requests.id = request_call_types.request_id
2359 and schedules.call_type_id = request_call_types.call_type_id
2360 where
2361 schedules.linkage_instance_id >= 0
2362 and schedules.should_be_evaluated = 1
2363 and (
2364 call_types.all_staff_types = 1
2365 or eligible_staff_types.id is not null
2366 )
2367 and (
2368 rotations.id is null
2369 or rotation_call_types.id is not null
2370 )
2371 and (
2372 requests.id is null
2373 or (
2374 requests.all_call_types = 1
2375 and request_settings.call_only_all_call_switch = 1 /* calls only */
2376 and call_types.assignment_type = 1 /* clinical */
2377 )
2378 or (
2379 requests.all_call_types != 1
2380 and request_call_types.id is null
2381 )
2382 )
2383 group by
2384 schedules.id,
2385 staffs.id,
2386 assign_staff_types.staff_type_id,
2387 is_primary
2388 having
2389 sum(if(required_rotations.id is not null and staff_rotations2.id is null, 1, 0)) = 0 /* RequiredRotationsRule */
2390 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
2391 order by null
2392
2393INFO : (02-13 16:17:48) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query092
2394 (256.8ms) /* RotationPostCallRule */
2395 /* Exclude people and slots when a post-call is not allowed on the first day of a rotation */
2396 delete sa
2397 from buffer_static_availabilities_72687288 as sa
2398 inner join buffer_schedules_72687288 as sc
2399 on sa.schedule_id = sc.id
2400 inner join call_types as ct
2401 on sc.call_type_id = ct.id
2402 and ct.assignment_type = 0 /* call */
2403 inner join staff_rotations as sr
2404 on sa.staff_id = sr.staff_id
2405 and date_add(sc.schedule_on, interval 1 day) = sr.start_date
2406 inner join rotations as r
2407 on sr.rotation_id = r.id
2408 and r.post_call_allowed = 0
2409
2410INFO : (02-13 16:17:48) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query093
2411INFO : (02-13 16:17:48) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query095
2412 (254.2ms) insert into buffer_negative_relations_72687288 (
2413 schedule_id,
2414 affected_by_schedule_id,
2415 staff_id
2416 )
2417 select
2418 b_schedules.id as schedule_id,
2419 b_schedules_negative_link.id as affected_by_schedule_id,
2420 b_schedules_negative_link.staff_id
2421 from
2422 buffer_schedules_72687288 as b_schedules
2423 /* NegativeLinkRule */
2424 inner join negative_links_influences
2425 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2426 and b_schedules.call_type_id = negative_links_influences.source_id
2427 and negative_links_influences.source_type = 'CallType'
2428 and negative_links_influences.target_type = 'CallType'
2429 inner join buffer_schedules_72687288 as b_schedules_negative_link
2430 on date_add(
2431 b_schedules.schedule_on,
2432 interval negative_links_influences.offset day
2433 ) = b_schedules_negative_link.schedule_on
2434 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
2435 and b_schedules.id != b_schedules_negative_link.id
2436 where
2437 b_schedules.linkage_instance_id >= 0
2438 and b_schedules.should_be_evaluated = 1
2439 order by null
2440
2441 (262.2ms) insert into buffer_negative_relations_72687288 (
2442 schedule_id,
2443 affected_by_schedule_id,
2444 staff_id
2445 )
2446 select
2447 b_schedules.id as schedule_id,
2448 b_schedules_negative_link.id as affected_by_schedule_id,
2449 b_schedules_negative_link.staff_id
2450 from
2451 buffer_schedules_72687288 as b_schedules
2452 /* NegativeLinkRule */
2453 inner join negative_links_influences
2454 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2455 and b_schedules.call_type_id = negative_links_influences.source_id
2456 and negative_links_influences.source_type = 'CallType'
2457 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
2458 inner join buffer_schedules_72687288 as b_schedules_negative_link
2459 on date_add(
2460 b_schedules.schedule_on,
2461 interval negative_links_influences.offset day
2462 ) = b_schedules_negative_link.schedule_on
2463 and (-1 * ifnull(negative_links_influences.target_id, 0)) = b_schedules_negative_link.call_type_id
2464 and b_schedules.id != b_schedules_negative_link.id
2465 where
2466 b_schedules.linkage_instance_id >= 0
2467 and b_schedules.should_be_evaluated = 1
2468 order by null
2469
2470 (425.9ms) delete from buffer_schedules_72687288
2471 where call_type_id <= 0 /* vacations and non-clinicals */
2472
2473INFO : (02-13 16:17:49) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query097
2474 (253.0ms) insert into buffer_staff_assigned_days_72687288 (
2475 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
2476 )
2477 select staffs.staff_id,
2478 if(days.within_period = 1, days.period_offset, null) as period_offset,
2479 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
2480 days.schedule_on,
2481 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
2482 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
2483 from (
2484 select distinct staff_id
2485 from buffer_static_availabilities_72687288
2486 order by null
2487 ) as staffs
2488 inner join (
2489 select p.period_offset,
2490 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,
2491 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
2492 from buffer_periods_72687288 as p
2493 inner join (
2494 select 0 as digit union select 1 union select 2 union select 3 union select 4
2495 union select 5 union select 6 union select 7 union select 8 union select 9
2496 ) as digits_1
2497 inner join (
2498 select 0 as digit union select 1 union select 2 union select 3 union select 4
2499 union select 5 union select 6 union select 7 union select 8 union select 9
2500 ) as digits_2
2501 inner join (
2502 select 0 as digit union select 1 union select 2 union select 3 union select 4
2503 union select 5 union select 6 union select 7 union select 8 union select 9
2504 ) as digits_3
2505 where p.calendar_schedule_id = 1381
2506 and p.should_be_evaluated = 1
2507 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
2508 order by null
2509 ) as days
2510 inner join (
2511 select min(start_date) as start_date, max(end_date) as end_date
2512 from buffer_periods_72687288
2513 where calendar_schedule_id = 1381
2514 and should_be_evaluated = 1
2515 order by null
2516 ) as limits
2517 inner join clients
2518 on clients.id = 319
2519 left join (
2520 select sc.staff_id, sc.schedule_on
2521 from buffer_periods_72687288 as p
2522 inner join schedules as sc
2523 on sc.client_id = 319
2524 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2525 inner join call_types as ct
2526 on sc.call_type_id = ct.id
2527 and ct.unassigned_day = 0
2528 where p.calendar_schedule_id = 1381
2529 and p.should_be_evaluated = 1
2530 group by sc.staff_id, sc.schedule_on
2531 order by null
2532 ) as schedules
2533 on staffs.staff_id = schedules.staff_id
2534 and days.schedule_on = schedules.schedule_on
2535 left join (
2536 select v.staff_id, v.schedule_on
2537 from buffer_periods_72687288 as p
2538 inner join vacations as v
2539 on v.client_id = 319
2540 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2541 and v.status IN (3)
2542 left join non_clinical_types as nct
2543 on v.non_clinical_type_id = nct.id
2544 and nct.active = 1
2545 and nct.unassigned_day = 0
2546 where p.calendar_schedule_id = 1381
2547 and p.should_be_evaluated = 1
2548 and (
2549 v.vacation_type = 0
2550 or nct.id is not null
2551 )
2552 group by v.staff_id, v.schedule_on
2553 order by null
2554 ) as vacations
2555 on staffs.staff_id = vacations.staff_id
2556 and days.schedule_on = vacations.schedule_on
2557 where days.within_period = 1
2558 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
2559 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
2560 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
2561 )
2562 order by null
2563
2564INFO : (02-13 16:17:49) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query098
2565 (256.0ms) insert into buffer_combined_availabilities_72687288 (
2566 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
2567 )
2568 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
2569 sc.linkage_instance_id, 0 as has_violations
2570 from buffer_schedules_72687288 as sc
2571 /* Static availability */
2572 inner join buffer_static_availabilities_72687288 as sa
2573 on sc.id = sa.schedule_id
2574 inner join buffer_linkages_72687288 as l
2575 on sc.linkage_instance_id = l.linkage_instance_id
2576 where sc.should_be_evaluated = 1
2577 and l.has_conflicts = 0
2578
2579 (256.5ms) insert into buffer_schedule_linkages_72687288 (
2580 staff_id, linkage_instance_id, slots_count
2581 )
2582 select staff_id, linkage_instance_id, count(1) as slots_count
2583 from buffer_schedules_72687288 use index (ix_buffer_schedules_composite_2)
2584 where staff_id is not null
2585 and linkage_instance_id >= 0
2586 group by staff_id, linkage_instance_id
2587 order by null
2588
2589INFO : (02-13 16:17:50) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
2590 (726.3ms) /* AssignmentLimitsPerPeriod */
2591 insert into buffer_staff_assignments_count_periods_72687288 (
2592 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2593 )
2594 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2595 count(distinct sc.id) as assignments_count
2596 from (
2597 select linkage_instance_id, assignment_limit_id, start_date, end_date
2598 from buffer_linkage_assignments_count_periods_72687288
2599 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2600 order by null
2601 ) as lac
2602 inner join (
2603 select linkage_instance_id, staff_id
2604 from buffer_combined_availabilities_72687288
2605 group by linkage_instance_id, staff_id
2606 order by null
2607 ) as ca
2608 on lac.linkage_instance_id = ca.linkage_instance_id
2609 inner join assignment_limits as al
2610 on lac.assignment_limit_id = al.id
2611 inner join schedules as sc
2612 on ca.staff_id = sc.staff_id
2613 and sc.schedule_on between lac.start_date and lac.end_date
2614 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2615 inner join buffer_assignment_limit_call_types_72687288 as alct
2616 on lac.assignment_limit_id = alct.assignment_limit_id
2617 and sc.call_type_id = alct.call_type_id
2618 inner join assign_staff_types as ast
2619 on sc.staff_id = ast.staff_id
2620 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2621 inner join buffer_assignment_limit_staff_types_72687288 as alst
2622 on lac.assignment_limit_id = alst.assignment_limit_id
2623 and ast.staff_type_id = alst.staff_type_id
2624 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2625 order by null
2626
2627INFO : (02-13 16:17:50) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
2628 (320.4ms) /* AssignmentLimitsPerWeek */
2629 insert into buffer_staff_assignments_count_weeks_72687288 (
2630 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2631 )
2632 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2633 count(distinct sc.id) as assignments_count
2634 from (
2635 select linkage_instance_id, assignment_limit_id, start_date, end_date
2636 from buffer_linkage_assignments_count_weeks_72687288
2637 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2638 order by null
2639 ) as lac
2640 inner join (
2641 select linkage_instance_id, staff_id
2642 from buffer_combined_availabilities_72687288
2643 group by linkage_instance_id, staff_id
2644 order by null
2645 ) as ca
2646 on lac.linkage_instance_id = ca.linkage_instance_id
2647 inner join assignment_limits as al
2648 on lac.assignment_limit_id = al.id
2649 inner join schedules as sc
2650 on ca.staff_id = sc.staff_id
2651 and sc.schedule_on between lac.start_date and lac.end_date
2652 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2653 inner join buffer_assignment_limit_call_types_72687288 as alct
2654 on lac.assignment_limit_id = alct.assignment_limit_id
2655 and sc.call_type_id = alct.call_type_id
2656 inner join assign_staff_types as ast
2657 on sc.staff_id = ast.staff_id
2658 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2659 inner join buffer_assignment_limit_staff_types_72687288 as alst
2660 on lac.assignment_limit_id = alst.assignment_limit_id
2661 and ast.staff_type_id = alst.staff_type_id
2662 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2663 order by null
2664
2665INFO : (02-13 16:17:51) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
2666 (681.4ms) /* AssignmentLimitsRolling */
2667 insert into buffer_staff_assignments_count_rolling_72687288 (
2668 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2669 )
2670 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2671 count(distinct sc.id) as assignments_count
2672 from (
2673 select linkage_instance_id, assignment_limit_id, start_date, end_date
2674 from buffer_linkage_assignments_count_rolling_72687288
2675 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2676 order by null
2677 ) as lac
2678 inner join (
2679 select linkage_instance_id, staff_id
2680 from buffer_combined_availabilities_72687288
2681 group by linkage_instance_id, staff_id
2682 order by null
2683 ) as ca
2684 on lac.linkage_instance_id = ca.linkage_instance_id
2685 inner join assignment_limits as al
2686 on lac.assignment_limit_id = al.id
2687 inner join schedules as sc
2688 on ca.staff_id = sc.staff_id
2689 and sc.schedule_on between lac.start_date and lac.end_date
2690 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2691 inner join buffer_assignment_limit_call_types_72687288 as alct
2692 on lac.assignment_limit_id = alct.assignment_limit_id
2693 and sc.call_type_id = alct.call_type_id
2694 inner join assign_staff_types as ast
2695 on sc.staff_id = ast.staff_id
2696 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2697 inner join buffer_assignment_limit_staff_types_72687288 as alst
2698 on lac.assignment_limit_id = alst.assignment_limit_id
2699 and ast.staff_type_id = alst.staff_type_id
2700 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2701 order by null
2702
2703INFO : (02-13 16:17:51) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
2704 (306.0ms) /* AssignmentLimitsConsecutive */
2705 insert into buffer_staff_assignments_count_consecutive_72687288 (
2706 assignment_limit_id, staff_id, schedule_on, start_date, end_date
2707 )
2708 select straight_join lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2709 from (
2710 select linkage_instance_id, assignment_limit_id, start_date, end_date
2711 from buffer_linkage_assignments_count_consecutive_72687288
2712 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2713 order by null
2714 ) as lac
2715 inner join (
2716 select linkage_instance_id, staff_id
2717 from buffer_combined_availabilities_72687288
2718 group by linkage_instance_id, staff_id
2719 order by null
2720 ) as ca
2721 on lac.linkage_instance_id = ca.linkage_instance_id
2722 inner join assignment_limits as al
2723 on lac.assignment_limit_id = al.id
2724 inner join schedules as sc
2725 on ca.staff_id = sc.staff_id
2726 and sc.schedule_on between lac.start_date and lac.end_date
2727 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2728 inner join buffer_assignment_limit_call_types_72687288 as alct
2729 on lac.assignment_limit_id = alct.assignment_limit_id
2730 and sc.call_type_id = alct.call_type_id
2731 inner join assign_staff_types as ast
2732 on sc.staff_id = ast.staff_id
2733 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2734 inner join buffer_assignment_limit_staff_types_72687288 as alst
2735 on lac.assignment_limit_id = alst.assignment_limit_id
2736 and ast.staff_type_id = alst.staff_type_id
2737 group by lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2738 order by null
2739
2740INFO : (02-13 16:17:52) --> fill: 44 - Scheduler::AutoPopulate::Queries::Query09_09_RP
2741INFO : (02-13 16:17:52) --> fill: 45 - Scheduler::AutoPopulate::Queries::Query10
2742 RuleSet Load (662.2ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1503 LIMIT 1
2743 Client Load (301.8ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
2744 (666.2ms) /* SpecializationOverlapRule */
2745 delete ca
2746 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
2747 inner join buffer_schedules_72687288 as sc
2748 on ca.schedule_id = sc.id
2749 inner join buffer_staff_specializations_72687288 as ssp
2750 on ca.staff_id = ssp.staff_id
2751 inner join max_applicable_call_types as mact
2752 on ssp.specialization_id = mact.specialization_id
2753 and sc.call_type_id = mact.call_type_id
2754 inner join buffer_specialization_daily_counts_72687288 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2755 on ssp.specialization_id = sdc.specialization_id
2756 and sc.schedule_on = sdc.schedule_on
2757 and sdc.staff_id is null
2758 left join buffer_specialization_daily_counts_72687288 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2759 on ssp.specialization_id = sdc_2.specialization_id
2760 and sc.schedule_on = sdc_2.schedule_on
2761 and ca.staff_id = sdc_2.staff_id
2762 where sdc.should_be_evaluated = 1
2763 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2764
2765 (256.3ms) delete ca
2766 from buffer_combined_availabilities_72687288 as ca
2767 inner join (
2768 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2769 sum(lac.assignments_count) as assignments_count
2770 from (
2771 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2772 from buffer_combined_availabilities_72687288 as ca
2773 inner join buffer_schedules_72687288 as sc
2774 on ca.schedule_id = sc.id
2775 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2776 order by null
2777 ) as ca_sc
2778 inner join buffer_linkage_assignments_count_weeks_72687288 as lac
2779 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2780 and ca_sc.schedule_on = lac.schedule_on
2781 inner join buffer_assignment_limit_staff_types_72687288 as alst
2782 on lac.assignment_limit_id = alst.assignment_limit_id
2783 and ca_sc.staff_type_id = alst.staff_type_id
2784 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2785 order by null
2786 ) as lac
2787 on ca.linkage_instance_id = lac.linkage_instance_id
2788 and ca.staff_id = lac.staff_id
2789 inner join assignment_limits as al
2790 on lac.assignment_limit_id = al.id
2791 left join buffer_staff_assignments_count_weeks_72687288 as sac
2792 on lac.assignment_limit_id = sac.assignment_limit_id
2793 and lac.staff_id = sac.staff_id
2794 and lac.start_date = sac.start_date
2795 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
2796
2797 (254.1ms) delete ca
2798 from buffer_combined_availabilities_72687288 as ca
2799 inner join (
2800 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2801 sum(lac.assignments_count) as assignments_count
2802 from (
2803 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2804 from buffer_combined_availabilities_72687288 as ca
2805 inner join buffer_schedules_72687288 as sc
2806 on ca.schedule_id = sc.id
2807 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2808 order by null
2809 ) as ca_sc
2810 inner join buffer_linkage_assignments_count_periods_72687288 as lac
2811 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2812 and ca_sc.schedule_on = lac.schedule_on
2813 inner join buffer_assignment_limit_staff_types_72687288 as alst
2814 on lac.assignment_limit_id = alst.assignment_limit_id
2815 and ca_sc.staff_type_id = alst.staff_type_id
2816 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2817 order by null
2818 ) as lac
2819 on ca.linkage_instance_id = lac.linkage_instance_id
2820 and ca.staff_id = lac.staff_id
2821 inner join assignment_limits as al
2822 on lac.assignment_limit_id = al.id
2823 left join buffer_staff_assignments_count_periods_72687288 as sac
2824 on lac.assignment_limit_id = sac.assignment_limit_id
2825 and lac.staff_id = sac.staff_id
2826 and lac.start_date = sac.start_date
2827 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
2828
2829 (256.0ms) delete ca
2830 from buffer_combined_availabilities_72687288 as ca
2831 inner join (
2832 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2833 sum(lac.assignments_count) as assignments_count
2834 from (
2835 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2836 from buffer_combined_availabilities_72687288 as ca
2837 inner join buffer_schedules_72687288 as sc
2838 on ca.schedule_id = sc.id
2839 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2840 order by null
2841 ) as ca_sc
2842 inner join buffer_linkage_assignments_count_rolling_72687288 as lac
2843 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2844 and ca_sc.schedule_on = lac.schedule_on
2845 inner join buffer_assignment_limit_staff_types_72687288 as alst
2846 on lac.assignment_limit_id = alst.assignment_limit_id
2847 and ca_sc.staff_type_id = alst.staff_type_id
2848 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2849 order by null
2850 ) as lac
2851 on ca.linkage_instance_id = lac.linkage_instance_id
2852 and ca.staff_id = lac.staff_id
2853 inner join assignment_limits as al
2854 on lac.assignment_limit_id = al.id
2855 left join buffer_staff_assignments_count_rolling_72687288 as sac
2856 on lac.assignment_limit_id = sac.assignment_limit_id
2857 and lac.staff_id = sac.staff_id
2858 and lac.start_date = sac.start_date
2859 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
2860
2861 (253.7ms) delete ca
2862 from buffer_combined_availabilities_72687288 as ca
2863 inner join (
2864 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2865 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
2866 from (
2867 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2868 from buffer_combined_availabilities_72687288 as ca
2869 inner join buffer_schedules_72687288 as sc
2870 on ca.schedule_id = sc.id
2871 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2872 order by null
2873 ) as ca_sc
2874 inner join buffer_linkage_assignments_count_consecutive_72687288 as lac
2875 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2876 and ca_sc.schedule_on = lac.schedule_on
2877 inner join buffer_assignment_limit_staff_types_72687288 as alst
2878 on lac.assignment_limit_id = alst.assignment_limit_id
2879 and ca_sc.staff_type_id = alst.staff_type_id
2880 left join buffer_staff_assignments_count_consecutive_72687288 as sac
2881 on lac.assignment_limit_id = sac.assignment_limit_id
2882 and ca_sc.staff_id = sac.staff_id
2883 and lac.schedule_on = sac.schedule_on
2884 and lac.start_date = sac.start_date
2885 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2886 order by null
2887 ) as lac
2888 on ca.linkage_instance_id = lac.linkage_instance_id
2889 and ca.staff_id = lac.staff_id
2890 inner join assignment_limits as al
2891 on lac.assignment_limit_id = al.id
2892 left join (
2893 select assignment_limit_id, staff_id, start_date,
2894 count(schedule_on) as assignments_count
2895 from buffer_staff_assignments_count_consecutive_72687288
2896 group by assignment_limit_id, staff_id, start_date
2897 ) as sac
2898 on lac.assignment_limit_id = sac.assignment_limit_id
2899 and lac.staff_id = sac.staff_id
2900 and lac.start_date = sac.start_date
2901 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
2902
2903 (253.5ms) delete ca
2904 from buffer_combined_availabilities_72687288 as ca
2905 inner join buffer_schedules_72687288 as sc
2906 on ca.schedule_id = sc.id
2907 inner join buffer_target_assignments_72687288 as ta
2908 on sc.call_type_id = ta.call_type_id
2909 inner join buffer_linkage_call_type_targets_72687288 as lctt
2910 on sc.linkage_instance_id = lctt.linkage_instance_id
2911 and sc.calendar_schedule_id = lctt.calendar_schedule_id
2912 and lctt.period_offset = -1
2913 and ta.assignment_id = lctt.assignment_id
2914 and ta.assignment_type = lctt.assignment_type
2915 inner join buffer_cumulative_ct_targets_72687288 as ctt
2916 on ca.staff_id = ctt.staff_id
2917 and sc.calendar_schedule_id = ctt.calendar_schedule_id
2918 and ta.assignment_id = ctt.assignment_id
2919 and ta.assignment_type = ctt.assignment_type
2920 where
2921 (ctt.is_weekend = 1 and ((round(ctt.raw_target, 2)) - ctt.scheduled - lctt.weekend_targets) < 0) /* weekend checkbox */
2922
2923 or
2924 (ctt.is_weekend = 0 and ((round(ctt.raw_target, 2)) - ctt.scheduled - lctt.weekday_targets) < 0) /* weekday checkbox */
2925
2926
2927 (258.2ms) /* MaxAssignmentsRule */
2928 delete ca
2929 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
2930 inner join buffer_schedules_72687288 as sc
2931 on ca.schedule_id = sc.id
2932 inner join buffer_linkage_call_type_assignments_72687288 as lcta use index (ix_buffer_linkage_call_type_assignments_composite_1)
2933 on sc.linkage_instance_id = lcta.linkage_instance_id
2934 and sc.calendar_schedule_id = lcta.calendar_schedule_id
2935 and sc.period_offset = lcta.period_offset
2936 and sc.call_type_id = lcta.call_type_id
2937 left join buffer_call_type_assignments_72687288 as cta use index (ix_buffer_call_type_assignments_composite_1)
2938 on ca.staff_id = cta.staff_id
2939 and sc.calendar_schedule_id = cta.calendar_schedule_id
2940 and sc.period_offset = cta.period_offset
2941 and sc.call_type_id = cta.call_type_id
2942 where
2943 /* weekday checkbox */
2944 (
2945 lcta.max_weekday_assignments is not null
2946 and (ifnull(cta.weekday_assignments, 0) + lcta.weekday_assignments) > lcta.max_weekday_assignments
2947 )
2948 /* weekend checkbox */
2949 or (
2950 lcta.max_weekend_assignments is not null
2951 and (ifnull(cta.weekend_assignments, 0) + lcta.weekend_assignments) > lcta.max_weekend_assignments
2952 )
2953
2954 (256.9ms) /* NegativeLinkRule */
2955 delete ca
2956 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
2957 inner join buffer_negative_relations_72687288 as nr use index (ix_buffer_negative_relations_composite_1)
2958 on ca.schedule_id = nr.schedule_id
2959 and ca.staff_id = nr.staff_id
2960
2961 (257.4ms) /* BrokenLinkageRule */
2962 truncate table buffer_combined_availability_linkages_72687288
2963
2964 (253.3ms) insert into buffer_combined_availability_linkages_72687288 (
2965 staff_id, linkage_instance_id, slots_count, is_primary, has_violations
2966 )
2967 select staff_id, linkage_instance_id,
2968 count(1) as slots_count,
2969 min(is_primary) as is_primary,
2970 max(has_violations) as has_violations
2971 from buffer_combined_availabilities_72687288 use index (ix_buffer_combined_availabilities_composite_2)
2972 group by staff_id, linkage_instance_id
2973 order by null
2974
2975 (254.3ms) delete ca
2976 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
2977 inner join buffer_schedules_72687288 as sc
2978 on ca.schedule_id = sc.id
2979 inner join buffer_linkages_72687288 as l
2980 on sc.linkage_instance_id = l.linkage_instance_id
2981 inner join buffer_combined_availability_linkages_72687288 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2982 on ca.staff_id = ls_1.staff_id
2983 and sc.linkage_instance_id = ls_1.linkage_instance_id
2984 left join buffer_schedule_linkages_72687288 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2985 on ca.staff_id = ls_2.staff_id
2986 and sc.linkage_instance_id = ls_2.linkage_instance_id
2987 where (ls_1.slots_count + ifnull(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */
2988
2989INFO : (02-13 16:17:56) --> fill: 46 - Scheduler::AutoPopulate::Queries::QueryScore02
2990INFO : (02-13 16:17:56) --> fill: 47 - Scheduler::AutoPopulate::Queries::QueryScore03
2991INFO : (02-13 16:17:56) --> fill: 48 - Scheduler::AutoPopulate::Queries::QueryScore04
2992INFO : (02-13 16:17:56) --> fill: 49 - Scheduler::AutoPopulate::Queries::Query11
2993 (251.5ms) update buffer_schedules_72687288 as sc
2994 left join buffer_combined_availabilities_72687288 as ca
2995 on sc.id = ca.schedule_id
2996 set sc.should_be_evaluated = 0,
2997 sc.should_be_populated = 0
2998 where sc.should_be_evaluated = 1
2999 and ca.id is null
3000
3001INFO : (02-13 16:17:56) --> fill: 50 - Scheduler::AutoPopulate::Queries::DropTables
3002INFO : (02-13 16:17:56) Setting up lists...
3003 (260.5ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
3004 from buffer_schedules_72687288 as b_schedules
3005 left join schedules
3006 on schedules.client_id = 319
3007 and schedules.call_type_id = b_schedules.call_type_id
3008 and schedules.schedule_on = b_schedules.schedule_on
3009 where b_schedules.linkage_instance_id != -1
3010 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
3011
3012INFO : (02-13 16:17:56) Begin to populate. Pre-set: 0. Unscheduled: 93.
3013INFO : (02-13 16:17:56) plan unit: [id:201809] [2018-03-04 - OB]
3014INFO : (02-13 16:17:56) plan unit: [id:201810] [2018-03-11 - OB]
3015INFO : (02-13 16:17:56) plan unit: [id:201811] [2018-03-18 - OB]
3016INFO : (02-13 16:17:56) plan unit: [id:201812] [2018-03-25 - OB]
3017INFO : (02-13 16:17:56) plan unit: [id:1201809] [2018-03-04 - OR]
3018INFO : (02-13 16:17:56) plan unit: [id:1201810] [2018-03-11 - OR]
3019INFO : (02-13 16:17:56) plan unit: [id:1201811] [2018-03-18 - OR]
3020INFO : (02-13 16:17:56) plan unit: [id:1201812] [2018-03-25 - OR]
3021INFO : (02-13 16:17:56) plan unit: [id:2201809] [2018-03-04 - BU]
3022INFO : (02-13 16:17:56) plan unit: [id:2201810] [2018-03-11 - BU]
3023INFO : (02-13 16:17:56) plan unit: [id:2201811] [2018-03-18 - BU]
3024INFO : (02-13 16:17:56) plan unit: [id:2201812] [2018-03-25 - BU]
3025INFO : (02-13 16:17:56) plan unit: [id:6201809] [2018-03-05 - OB]
3026INFO : (02-13 16:17:56) plan unit: [id:6201810] [2018-03-12 - OB]
3027INFO : (02-13 16:17:56) plan unit: [id:6201811] [2018-03-19 - OB]
3028INFO : (02-13 16:17:56) plan unit: [id:6201812] [2018-03-26 - OB]
3029INFO : (02-13 16:17:56) plan unit: [id:7201809] [2018-03-05 - OR]
3030INFO : (02-13 16:17:56) plan unit: [id:7201810] [2018-03-12 - OR]
3031INFO : (02-13 16:17:56) plan unit: [id:7201811] [2018-03-19 - OR]
3032INFO : (02-13 16:17:56) plan unit: [id:7201812] [2018-03-26 - OR]
3033INFO : (02-13 16:17:56) plan unit: [id:8201809] [2018-03-05 - BU]
3034INFO : (02-13 16:17:56) plan unit: [id:8201810] [2018-03-12 - BU]
3035INFO : (02-13 16:17:56) plan unit: [id:8201811] [2018-03-19 - BU]
3036INFO : (02-13 16:17:56) plan unit: [id:8201812] [2018-03-26 - BU]
3037INFO : (02-13 16:17:56) plan unit: [id:12201809] [2018-03-06 - OB]
3038INFO : (02-13 16:17:56) plan unit: [id:12201810] [2018-03-13 - OB]
3039INFO : (02-13 16:17:56) plan unit: [id:12201811] [2018-03-20 - OB]
3040INFO : (02-13 16:17:56) plan unit: [id:12201812] [2018-03-27 - OB]
3041INFO : (02-13 16:17:56) plan unit: [id:13201809] [2018-03-06 - OR]
3042INFO : (02-13 16:17:56) plan unit: [id:13201810] [2018-03-13 - OR]
3043INFO : (02-13 16:17:56) plan unit: [id:13201811] [2018-03-20 - OR]
3044INFO : (02-13 16:17:56) plan unit: [id:13201812] [2018-03-27 - OR]
3045INFO : (02-13 16:17:56) plan unit: [id:14201809] [2018-03-06 - BU]
3046INFO : (02-13 16:17:56) plan unit: [id:14201810] [2018-03-13 - BU]
3047INFO : (02-13 16:17:56) plan unit: [id:14201811] [2018-03-20 - BU]
3048INFO : (02-13 16:17:56) plan unit: [id:14201812] [2018-03-27 - BU]
3049INFO : (02-13 16:17:56) plan unit: [id:18201809] [2018-03-07 - OB]
3050INFO : (02-13 16:17:56) plan unit: [id:18201810] [2018-03-14 - OB]
3051INFO : (02-13 16:17:57) plan unit: [id:18201811] [2018-03-21 - OB]
3052INFO : (02-13 16:17:57) plan unit: [id:18201812] [2018-03-28 - OB]
3053INFO : (02-13 16:17:57) plan unit: [id:19201809] [2018-03-07 - OR]
3054INFO : (02-13 16:17:57) plan unit: [id:19201810] [2018-03-14 - OR]
3055INFO : (02-13 16:17:57) plan unit: [id:19201811] [2018-03-21 - OR]
3056INFO : (02-13 16:17:57) plan unit: [id:19201812] [2018-03-28 - OR]
3057INFO : (02-13 16:17:57) plan unit: [id:20201809] [2018-03-07 - BU]
3058INFO : (02-13 16:17:57) plan unit: [id:20201810] [2018-03-14 - BU]
3059INFO : (02-13 16:17:57) plan unit: [id:20201811] [2018-03-21 - BU]
3060INFO : (02-13 16:17:57) plan unit: [id:20201812] [2018-03-28 - BU]
3061INFO : (02-13 16:17:57) plan unit: [id:24201808] [2018-03-01 - OB]
3062INFO : (02-13 16:17:57) plan unit: [id:24201809] [2018-03-08 - OB]
3063INFO : (02-13 16:17:57) plan unit: [id:24201810] [2018-03-15 - OB]
3064INFO : (02-13 16:17:57) plan unit: [id:24201811] [2018-03-22 - OB]
3065INFO : (02-13 16:17:57) plan unit: [id:24201812] [2018-03-29 - OB]
3066INFO : (02-13 16:17:57) plan unit: [id:25201808] [2018-03-01 - OR]
3067INFO : (02-13 16:17:57) plan unit: [id:25201809] [2018-03-08 - OR]
3068INFO : (02-13 16:17:57) plan unit: [id:25201810] [2018-03-15 - OR]
3069INFO : (02-13 16:17:57) plan unit: [id:25201811] [2018-03-22 - OR]
3070INFO : (02-13 16:17:57) plan unit: [id:25201812] [2018-03-29 - OR]
3071INFO : (02-13 16:17:57) plan unit: [id:26201808] [2018-03-01 - BU]
3072INFO : (02-13 16:17:57) plan unit: [id:26201809] [2018-03-08 - BU]
3073INFO : (02-13 16:17:57) plan unit: [id:26201810] [2018-03-15 - BU]
3074INFO : (02-13 16:17:57) plan unit: [id:26201811] [2018-03-22 - BU]
3075INFO : (02-13 16:17:57) plan unit: [id:26201812] [2018-03-29 - BU]
3076INFO : (02-13 16:17:57) plan unit: [id:30201808] [2018-03-02 - OB]
3077INFO : (02-13 16:17:57) plan unit: [id:30201809] [2018-03-09 - OB]
3078INFO : (02-13 16:17:57) plan unit: [id:30201810] [2018-03-16 - OB]
3079INFO : (02-13 16:17:57) plan unit: [id:30201811] [2018-03-23 - OB]
3080INFO : (02-13 16:17:57) plan unit: [id:30201812] [2018-03-30 - OB]
3081INFO : (02-13 16:17:57) plan unit: [id:31201808] [2018-03-02 - OR]
3082INFO : (02-13 16:17:57) plan unit: [id:31201809] [2018-03-09 - OR]
3083INFO : (02-13 16:17:57) plan unit: [id:31201810] [2018-03-16 - OR]
3084INFO : (02-13 16:17:57) plan unit: [id:31201811] [2018-03-23 - OR]
3085INFO : (02-13 16:17:57) plan unit: [id:31201812] [2018-03-30 - OR]
3086INFO : (02-13 16:17:57) plan unit: [id:32201808] [2018-03-02 - BU]
3087INFO : (02-13 16:17:57) plan unit: [id:32201809] [2018-03-09 - BU]
3088INFO : (02-13 16:17:57) plan unit: [id:32201810] [2018-03-16 - BU]
3089INFO : (02-13 16:17:57) plan unit: [id:32201811] [2018-03-23 - BU]
3090INFO : (02-13 16:17:57) plan unit: [id:32201812] [2018-03-30 - BU]
3091INFO : (02-13 16:17:57) plan unit: [id:36201808] [2018-03-03 - OB]
3092INFO : (02-13 16:17:57) plan unit: [id:36201809] [2018-03-10 - OB]
3093INFO : (02-13 16:17:57) plan unit: [id:36201810] [2018-03-17 - OB]
3094INFO : (02-13 16:17:57) plan unit: [id:36201811] [2018-03-24 - OB]
3095INFO : (02-13 16:17:57) plan unit: [id:36201812] [2018-03-31 - OB]
3096INFO : (02-13 16:17:57) plan unit: [id:37201808] [2018-03-03 - OR]
3097INFO : (02-13 16:17:57) plan unit: [id:37201809] [2018-03-10 - OR]
3098INFO : (02-13 16:17:57) plan unit: [id:37201810] [2018-03-17 - OR]
3099INFO : (02-13 16:17:57) plan unit: [id:37201811] [2018-03-24 - OR]
3100INFO : (02-13 16:17:57) plan unit: [id:37201812] [2018-03-31 - OR]
3101INFO : (02-13 16:17:57) plan unit: [id:38201808] [2018-03-03 - BU]
3102INFO : (02-13 16:17:57) plan unit: [id:38201809] [2018-03-10 - BU]
3103INFO : (02-13 16:17:57) plan unit: [id:38201810] [2018-03-17 - BU]
3104INFO : (02-13 16:17:57) plan unit: [id:38201811] [2018-03-24 - BU]
3105INFO : (02-13 16:17:57) plan unit: [id:38201812] [2018-03-31 - BU]
3106 NegativeLink Load (217.1ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
3107 (256.4ms) /* SpecializationOverlapRule */
3108 delete ca
3109 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
3110 inner join buffer_schedules_72687288 as sc
3111 on ca.schedule_id = sc.id
3112 inner join buffer_staff_specializations_72687288 as ssp
3113 on ca.staff_id = ssp.staff_id
3114 inner join max_applicable_call_types as mact
3115 on ssp.specialization_id = mact.specialization_id
3116 and sc.call_type_id = mact.call_type_id
3117 inner join buffer_specialization_daily_counts_72687288 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3118 on ssp.specialization_id = sdc.specialization_id
3119 and sc.schedule_on = sdc.schedule_on
3120 and sdc.staff_id is null
3121 left join buffer_specialization_daily_counts_72687288 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3122 on ssp.specialization_id = sdc_2.specialization_id
3123 and sc.schedule_on = sdc_2.schedule_on
3124 and ca.staff_id = sdc_2.staff_id
3125 where sdc.should_be_evaluated = 1
3126 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3127
3128 (253.2ms) delete ca
3129 from buffer_combined_availabilities_72687288 as ca
3130 inner join (
3131 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3132 sum(lac.assignments_count) as assignments_count
3133 from (
3134 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3135 from buffer_combined_availabilities_72687288 as ca
3136 inner join buffer_schedules_72687288 as sc
3137 on ca.schedule_id = sc.id
3138 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3139 order by null
3140 ) as ca_sc
3141 inner join buffer_linkage_assignments_count_weeks_72687288 as lac
3142 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3143 and ca_sc.schedule_on = lac.schedule_on
3144 inner join buffer_assignment_limit_staff_types_72687288 as alst
3145 on lac.assignment_limit_id = alst.assignment_limit_id
3146 and ca_sc.staff_type_id = alst.staff_type_id
3147 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3148 order by null
3149 ) as lac
3150 on ca.linkage_instance_id = lac.linkage_instance_id
3151 and ca.staff_id = lac.staff_id
3152 inner join assignment_limits as al
3153 on lac.assignment_limit_id = al.id
3154 left join buffer_staff_assignments_count_weeks_72687288 as sac
3155 on lac.assignment_limit_id = sac.assignment_limit_id
3156 and lac.staff_id = sac.staff_id
3157 and lac.start_date = sac.start_date
3158 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
3159
3160 (252.9ms) delete ca
3161 from buffer_combined_availabilities_72687288 as ca
3162 inner join (
3163 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3164 sum(lac.assignments_count) as assignments_count
3165 from (
3166 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3167 from buffer_combined_availabilities_72687288 as ca
3168 inner join buffer_schedules_72687288 as sc
3169 on ca.schedule_id = sc.id
3170 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3171 order by null
3172 ) as ca_sc
3173 inner join buffer_linkage_assignments_count_periods_72687288 as lac
3174 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3175 and ca_sc.schedule_on = lac.schedule_on
3176 inner join buffer_assignment_limit_staff_types_72687288 as alst
3177 on lac.assignment_limit_id = alst.assignment_limit_id
3178 and ca_sc.staff_type_id = alst.staff_type_id
3179 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3180 order by null
3181 ) as lac
3182 on ca.linkage_instance_id = lac.linkage_instance_id
3183 and ca.staff_id = lac.staff_id
3184 inner join assignment_limits as al
3185 on lac.assignment_limit_id = al.id
3186 left join buffer_staff_assignments_count_periods_72687288 as sac
3187 on lac.assignment_limit_id = sac.assignment_limit_id
3188 and lac.staff_id = sac.staff_id
3189 and lac.start_date = sac.start_date
3190 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
3191
3192 (253.1ms) delete ca
3193 from buffer_combined_availabilities_72687288 as ca
3194 inner join (
3195 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3196 sum(lac.assignments_count) as assignments_count
3197 from (
3198 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3199 from buffer_combined_availabilities_72687288 as ca
3200 inner join buffer_schedules_72687288 as sc
3201 on ca.schedule_id = sc.id
3202 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3203 order by null
3204 ) as ca_sc
3205 inner join buffer_linkage_assignments_count_rolling_72687288 as lac
3206 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3207 and ca_sc.schedule_on = lac.schedule_on
3208 inner join buffer_assignment_limit_staff_types_72687288 as alst
3209 on lac.assignment_limit_id = alst.assignment_limit_id
3210 and ca_sc.staff_type_id = alst.staff_type_id
3211 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3212 order by null
3213 ) as lac
3214 on ca.linkage_instance_id = lac.linkage_instance_id
3215 and ca.staff_id = lac.staff_id
3216 inner join assignment_limits as al
3217 on lac.assignment_limit_id = al.id
3218 left join buffer_staff_assignments_count_rolling_72687288 as sac
3219 on lac.assignment_limit_id = sac.assignment_limit_id
3220 and lac.staff_id = sac.staff_id
3221 and lac.start_date = sac.start_date
3222 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
3223
3224 (258.5ms) delete ca
3225 from buffer_combined_availabilities_72687288 as ca
3226 inner join (
3227 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3228 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
3229 from (
3230 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3231 from buffer_combined_availabilities_72687288 as ca
3232 inner join buffer_schedules_72687288 as sc
3233 on ca.schedule_id = sc.id
3234 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3235 order by null
3236 ) as ca_sc
3237 inner join buffer_linkage_assignments_count_consecutive_72687288 as lac
3238 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3239 and ca_sc.schedule_on = lac.schedule_on
3240 inner join buffer_assignment_limit_staff_types_72687288 as alst
3241 on lac.assignment_limit_id = alst.assignment_limit_id
3242 and ca_sc.staff_type_id = alst.staff_type_id
3243 left join buffer_staff_assignments_count_consecutive_72687288 as sac
3244 on lac.assignment_limit_id = sac.assignment_limit_id
3245 and ca_sc.staff_id = sac.staff_id
3246 and lac.schedule_on = sac.schedule_on
3247 and lac.start_date = sac.start_date
3248 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3249 order by null
3250 ) as lac
3251 on ca.linkage_instance_id = lac.linkage_instance_id
3252 and ca.staff_id = lac.staff_id
3253 inner join assignment_limits as al
3254 on lac.assignment_limit_id = al.id
3255 left join (
3256 select assignment_limit_id, staff_id, start_date,
3257 count(schedule_on) as assignments_count
3258 from buffer_staff_assignments_count_consecutive_72687288
3259 group by assignment_limit_id, staff_id, start_date
3260 ) as sac
3261 on lac.assignment_limit_id = sac.assignment_limit_id
3262 and lac.staff_id = sac.staff_id
3263 and lac.start_date = sac.start_date
3264 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
3265
3266 (261.0ms) delete ca
3267 from buffer_combined_availabilities_72687288 as ca
3268 inner join buffer_schedules_72687288 as sc
3269 on ca.schedule_id = sc.id
3270 inner join buffer_target_assignments_72687288 as ta
3271 on sc.call_type_id = ta.call_type_id
3272 inner join buffer_linkage_call_type_targets_72687288 as lctt
3273 on sc.linkage_instance_id = lctt.linkage_instance_id
3274 and sc.calendar_schedule_id = lctt.calendar_schedule_id
3275 and lctt.period_offset = -1
3276 and ta.assignment_id = lctt.assignment_id
3277 and ta.assignment_type = lctt.assignment_type
3278 inner join buffer_cumulative_ct_targets_72687288 as ctt
3279 on ca.staff_id = ctt.staff_id
3280 and sc.calendar_schedule_id = ctt.calendar_schedule_id
3281 and ta.assignment_id = ctt.assignment_id
3282 and ta.assignment_type = ctt.assignment_type
3283 where
3284 (ctt.is_weekend = 1 and ((round(ctt.raw_target, 2)) - ctt.scheduled - lctt.weekend_targets) < 0) /* weekend checkbox */
3285
3286 or
3287 (ctt.is_weekend = 0 and ((round(ctt.raw_target, 2)) - ctt.scheduled - lctt.weekday_targets) < 0) /* weekday checkbox */
3288
3289
3290 (264.6ms) /* MaxAssignmentsRule */
3291 delete ca
3292 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
3293 inner join buffer_schedules_72687288 as sc
3294 on ca.schedule_id = sc.id
3295 inner join buffer_linkage_call_type_assignments_72687288 as lcta use index (ix_buffer_linkage_call_type_assignments_composite_1)
3296 on sc.linkage_instance_id = lcta.linkage_instance_id
3297 and sc.calendar_schedule_id = lcta.calendar_schedule_id
3298 and sc.period_offset = lcta.period_offset
3299 and sc.call_type_id = lcta.call_type_id
3300 left join buffer_call_type_assignments_72687288 as cta use index (ix_buffer_call_type_assignments_composite_1)
3301 on ca.staff_id = cta.staff_id
3302 and sc.calendar_schedule_id = cta.calendar_schedule_id
3303 and sc.period_offset = cta.period_offset
3304 and sc.call_type_id = cta.call_type_id
3305 where
3306 /* weekday checkbox */
3307 (
3308 lcta.max_weekday_assignments is not null
3309 and (ifnull(cta.weekday_assignments, 0) + lcta.weekday_assignments) > lcta.max_weekday_assignments
3310 )
3311 /* weekend checkbox */
3312 or (
3313 lcta.max_weekend_assignments is not null
3314 and (ifnull(cta.weekend_assignments, 0) + lcta.weekend_assignments) > lcta.max_weekend_assignments
3315 )
3316
3317 (258.5ms) /* NegativeLinkRule */
3318 delete ca
3319 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
3320 inner join buffer_negative_relations_72687288 as nr use index (ix_buffer_negative_relations_composite_1)
3321 on ca.schedule_id = nr.schedule_id
3322 and ca.staff_id = nr.staff_id
3323
3324 (254.8ms) /* BrokenLinkageRule */
3325 truncate table buffer_combined_availability_linkages_72687288
3326
3327 (254.8ms) insert into buffer_combined_availability_linkages_72687288 (
3328 staff_id, linkage_instance_id, slots_count, is_primary, has_violations
3329 )
3330 select staff_id, linkage_instance_id,
3331 count(1) as slots_count,
3332 min(is_primary) as is_primary,
3333 max(has_violations) as has_violations
3334 from buffer_combined_availabilities_72687288 use index (ix_buffer_combined_availabilities_composite_2)
3335 group by staff_id, linkage_instance_id
3336 order by null
3337
3338 (256.0ms) delete ca
3339 from buffer_combined_availabilities_72687288 as ca use index (ix_buffer_combined_availabilities_composite_1)
3340 inner join buffer_schedules_72687288 as sc
3341 on ca.schedule_id = sc.id
3342 inner join buffer_linkages_72687288 as l
3343 on sc.linkage_instance_id = l.linkage_instance_id
3344 inner join buffer_combined_availability_linkages_72687288 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3345 on ca.staff_id = ls_1.staff_id
3346 and sc.linkage_instance_id = ls_1.linkage_instance_id
3347 left join buffer_schedule_linkages_72687288 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3348 on ca.staff_id = ls_2.staff_id
3349 and sc.linkage_instance_id = ls_2.linkage_instance_id
3350 where (ls_1.slots_count + ifnull(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */
3351
3352 (257.5ms) select straight_join
3353 sc.schedule_on,
3354 sc.call_type_id,
3355 count(distinct ca.staff_id) as available_members_count,
3356 group_concat(
3357 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3358 order by ls_1.is_primary desc, ca.staff_id
3359 separator ', '
3360 ) as available_members_ids,
3361 sc.linkage_instance_id,
3362 l.slots_count_total as linkage_slots_count
3363 from buffer_combined_availabilities_72687288 as ca
3364 inner join buffer_schedules_72687288 as sc
3365 on ca.schedule_id = sc.id
3366 inner join buffer_linkages_72687288 as l
3367 on sc.linkage_instance_id = l.linkage_instance_id
3368 inner join buffer_combined_availability_linkages_72687288 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3369 on ca.staff_id = ls_1.staff_id
3370 and sc.linkage_instance_id = ls_1.linkage_instance_id
3371 and ls_1.has_violations = 0
3372 where sc.should_be_evaluated = 1
3373 and sc.should_be_populated = 1
3374 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3375 order by available_members_count, sc.schedule_on, sc.display_order
3376 limit 1
3377
3378 (252.5ms) truncate table buffer_rule_scores_72687288
3379
3380 (253.2ms) truncate table buffer_rule_calc_filters_72687288
3381
3382 (254.8ms) insert into buffer_rule_calc_filters_72687288 (
3383 linkage_instance_id, staff_id
3384 ) values (36201808, 20698),(36201808, 20702)
3385
3386 (253.8ms) /* AnnualCallMixRule */
3387 insert into buffer_rule_scores_72687288 (
3388 linkage_instance_id, schedule_id, staff_id, rule_name, value
3389 )
3390 select straight_join
3391 b_schedules.linkage_instance_id, b_schedules.id as schedule_id, availabilities.staff_id, 'AnnualCallMixRule' as rule_name,
3392 (
3393 (b_targets.raw_target /* depends on a cumulative targets period */ - b_targets.scheduled) /
3394 b_targets.raw_target /* depends on a cumulative targets period */
3395 ) as value
3396 from buffer_rule_calc_filters_72687288 as filters
3397 inner join buffer_schedules_72687288 as b_schedules
3398 on filters.linkage_instance_id = b_schedules.linkage_instance_id
3399 inner join buffer_combined_availabilities_72687288 as availabilities use index (ix_buffer_combined_availabilities_composite_1)
3400 on b_schedules.id = availabilities.schedule_id
3401 and filters.staff_id = availabilities.staff_id
3402 inner join buffer_target_assignments_72687288 as target_assignments
3403 on b_schedules.call_type_id = target_assignments.call_type_id
3404 inner join buffer_cumulative_ct_targets_72687288 as b_targets use index (ix_buffer_cumulative_ct_targets_composite_1)
3405 on availabilities.staff_id = b_targets.staff_id
3406 and b_schedules.calendar_schedule_id = b_targets.calendar_schedule_id
3407 and target_assignments.assignment_id = b_targets.assignment_id
3408 and target_assignments.assignment_type = b_targets.assignment_type
3409 and if(b_schedules.day_of_week_mask & 62 > 0, 0, 1) = b_targets.is_weekend
3410 order by null
3411
3412 (252.6ms) truncate table buffer_rule_scores_with_corrections_72687288
3413
3414 (265.3ms) insert into buffer_rule_scores_with_corrections_72687288 (
3415 linkage_instance_id, schedule_id, staff_id, rule_name, value
3416 )
3417 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3418 ifnull(rule_scores.value, max_values.max_value + 1) as value
3419 from buffer_rule_scores_72687288 as rule_scores
3420 inner join (
3421 select rule_name, max(value) as max_value
3422 from buffer_rule_scores_72687288
3423 group by rule_name
3424 order by null
3425 ) as max_values
3426 on rule_scores.rule_name = max_values.rule_name
3427 order by null
3428
3429 (253.0ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3430 from (
3431 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3432 sum(
3433 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3434 rules.weight *
3435 total_values.slots_count
3436 ) as slot_scores
3437 from buffer_rule_scores_with_corrections_72687288 as rule_scores
3438 inner join (
3439 select rule_name, sum(abs(value)) as total_value,
3440 count(distinct schedule_id) as slots_count
3441 from buffer_rule_scores_with_corrections_72687288
3442 group by rule_name
3443 order by null
3444 ) as total_values
3445 on rule_scores.rule_name = total_values.rule_name
3446 inner join rules
3447 on rule_scores.rule_name = rules.type
3448 where rules.rule_set_id = 1503
3449 and rules.applied = 1
3450 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3451 order by null
3452 ) as staff_scores
3453 group by linkage_instance_id, staff_id
3454 order by round(avg(slot_scores), 5) desc, rand()
3455 limit 1
3456
3457 PublishedBlock Load (253.6ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 319 AND ('2018-03-03' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1381 ORDER BY `published_blocks`.`id` ASC LIMIT 1
3458 (239.6ms) BEGIN
3459 (252.6ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
3460 FROM call_types AS ct
3461 INNER JOIN calendar_schedules AS cs
3462 ON ct.calendar_schedule_id = cs.id
3463 INNER JOIN blocks AS b
3464 ON b.client_id = ct.client_id
3465 AND b.block_schedule_id = cs.block_schedule_id
3466 AND '2018-03-03' BETWEEN b.start_date AND b.end_date
3467 INNER JOIN published_blocks AS pb
3468 ON pb.client_id = ct.client_id
3469 AND pb.block_id = b.id
3470 AND pb.calendar_schedule_id = cs.id
3471 LEFT JOIN published_block_call_types AS pbct
3472 ON pbct.client_id = ct.client_id
3473 AND pbct.assignment_id = ct.id
3474 AND pbct.assignment_type = 'CallType'
3475 AND pbct.block_id = b.id
3476 AND (
3477 (1 << (DAYOFWEEK('2018-03-03') - 1) & pbct.days_of_week_mask) > 0
3478 )
3479 WHERE ct.id = 12692
3480 AND (
3481 (
3482 pb.assignment_ids IS NULL
3483 AND (1 << (DAYOFWEEK('2018-03-03') - 1) & pb.days_of_week_mask) > 0
3484 )
3485 OR pbct.id IS NOT NULL
3486 )
3487
3488 CallType Load (250.6ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
3489 Client Load (249.4ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3490 Staff Load (249.0ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20698 LIMIT 1
3491 Schedule Exists (241.0ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12692 AND `schedules`.`client_id` = 319 AND `schedules`.`schedule_on` = '2018-03-03' LIMIT 1
3492 SQL (251.4ms) 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 (319, 12692, 20698, '2018-03-03', '2018-02-13 16:18:04', '2018-02-13 16:18:04', 1, 7, 'auto_populate_step', 166)
3493 VisibleAssignment Load (255.2ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 319 AND `visible_assignments`.`date` = '2018-03-03' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12692 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
3494 (252.4ms) COMMIT
3495FLUSHED: nil:nil
3496 (259.2ms) UPDATE buffer_schedules_72687288
3497 SET staff_id = 20698,
3498 should_be_populated = 0,
3499 should_be_evaluated = 0
3500 WHERE
3501 call_type_id = 12692
3502 AND schedule_on = '2018-03-03'
3503
3504 (253.7ms) delete from buffer_combined_availabilities_72687288
3505 where linkage_instance_id = 36201808
3506
3507 (253.2ms) UPDATE buffer_rule_calc_schedules_72687288
3508 SET staff_id = 20698
3509 WHERE call_type_id = 12692
3510 AND schedule_on = '2018-03-03'
3511
3512 (255.6ms) INSERT INTO buffer_rule_calc_schedules_72687288
3513 SET staff_id = 20698,
3514 call_type_id = 12692,
3515 schedule_on = '2018-03-03',
3516 day_of_week_mask = 64
3517
3518 (253.5ms) delete from buffer_linkage_targets_72687288
3519 where linkage_instance_id = 36201808
3520
3521 (253.4ms) delete from buffer_linkage_call_type_targets_72687288
3522 where linkage_instance_id = 36201808
3523
3524 (253.8ms) delete from buffer_linkage_point_targets_72687288
3525 where linkage_instance_id = 36201808
3526
3527 (253.3ms) delete from buffer_linkage_hour_targets_72687288
3528 where linkage_instance_id = 36201808
3529
3530 (256.2ms) delete from buffer_linkage_call_type_assignments_72687288
3531 where linkage_instance_id = 36201808
3532
3533 (256.4ms) delete from buffer_linkage_call_days_targets_72687288
3534 where linkage_instance_id = 36201808
3535
3536 (254.8ms) delete from buffer_linkage_assigned_days_72687288
3537 where linkage_instance_id = 36201808
3538
3539 (255.4ms) delete from buffer_linkage_dow_targets_72687288
3540 where linkage_instance_id = 36201808
3541
3542 (258.8ms) update buffer_schedules_72687288 as sc
3543 inner join call_types as ct
3544 on sc.call_type_id = ct.id
3545 and ct.active = 1
3546 inner join buffer_call_type_assignments_72687288 as cta
3547 on sc.staff_id = cta.staff_id
3548 and sc.calendar_schedule_id = cta.calendar_schedule_id
3549 and sc.period_offset = cta.period_offset
3550 and sc.call_type_id = cta.call_type_id
3551 set cta.weekday_assignments = cta.weekday_assignments + if((sc.day_of_week_mask & 62 > 0), 1, 0),
3552 cta.weekend_assignments = cta.weekend_assignments + if((sc.day_of_week_mask & 65 > 0), 1, 0)
3553 where sc.schedule_on = '2018-03-03'
3554 and sc.call_type_id = 12692
3555 and sc.staff_id = 20698
3556
3557 (253.7ms) INSERT INTO buffer_call_type_assignments_72687288 (
3558 staff_id, calendar_schedule_id, period_offset, call_type_id, weekday_assignments, weekend_assignments
3559 )
3560 SELECT sc.staff_id,
3561 sc.calendar_schedule_id,
3562 sc.period_offset,
3563 sc.call_type_id,
3564 if((sc.day_of_week_mask & 62 > 0), 1, 0) AS weekday_assignments,
3565 if((sc.day_of_week_mask & 65 > 0), 1, 0) AS weekend_assignments
3566 FROM
3567 buffer_schedules_72687288 as sc
3568 WHERE
3569 sc.staff_id = 20698
3570 AND sc.schedule_on = '2018-03-03'
3571 AND sc.call_type_id = 12692
3572
3573 (254.7ms) UPDATE buffer_staff_daily_counts_72687288
3574 SET slots_count = slots_count + 1
3575 WHERE
3576 staff_id = 20698
3577 AND schedule_on = '2018-03-03'
3578 AND linkage_instance_id is null
3579
3580 (253.4ms) INSERT INTO buffer_staff_daily_counts_72687288
3581 SET slots_count = 1,
3582 staff_id = 20698,
3583 schedule_on = '2018-03-03',
3584 linkage_instance_id = null
3585
3586 (253.4ms) UPDATE buffer_staff_daily_counts_72687288
3587 SET slots_count = slots_count + 1
3588 WHERE
3589 staff_id = 20698
3590 AND schedule_on = '2018-03-03'
3591 AND linkage_instance_id = 36201808
3592
3593 (255.7ms) INSERT INTO buffer_staff_daily_counts_72687288
3594 SET slots_count = 1,
3595 staff_id = 20698,
3596 schedule_on = '2018-03-03',
3597 linkage_instance_id = 36201808
3598
3599 (253.8ms) update buffer_staff_specializations_72687288 as st_sp
3600 inner join max_applicable_call_types as mact
3601 on st_sp.specialization_id = mact.specialization_id
3602 and mact.call_type_id = 12692
3603 inner join buffer_specialization_daily_counts_72687288 as sp_dc
3604 on st_sp.specialization_id = sp_dc.specialization_id
3605 and sp_dc.staff_id is null
3606 and sp_dc.schedule_on = '2018-03-03'
3607 left join buffer_specialization_daily_counts_72687288 as sp_dc_2
3608 on st_sp.specialization_id = sp_dc_2.specialization_id
3609 and st_sp.staff_id = sp_dc_2.staff_id
3610 and sp_dc_2.schedule_on = '2018-03-03'
3611 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
3612 where st_sp.staff_id = 20698
3613 and sp_dc_2.id is null
3614
3615 (254.7ms) insert into buffer_specialization_daily_counts_72687288 (
3616 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
3617 )
3618 select sp.id as specialization_id,
3619 20698 as staff_id,
3620 '2018-03-03' as schedule_on,
3621 1 as staffs_scheduled,
3622 sp.max_scheduled,
3623 sp_dc.should_be_evaluated
3624 from buffer_staff_specializations_72687288 as st_sp
3625 inner join specializations as sp
3626 on st_sp.specialization_id = sp.id
3627 inner join max_applicable_call_types as mact
3628 on st_sp.specialization_id = mact.specialization_id
3629 and mact.call_type_id = 12692
3630 inner join buffer_specialization_daily_counts_72687288 as sp_dc
3631 on st_sp.specialization_id = sp_dc.specialization_id
3632 and sp_dc.staff_id is null
3633 and sp_dc.schedule_on = '2018-03-03'
3634 left join buffer_specialization_daily_counts_72687288 as sp_dc_2
3635 on st_sp.specialization_id = sp_dc_2.specialization_id
3636 and st_sp.staff_id = sp_dc_2.staff_id
3637 and sp_dc_2.schedule_on = '2018-03-03'
3638 where st_sp.staff_id = 20698
3639 and sp_dc_2.id is null
3640
3641 (270.4ms) UPDATE buffer_negative_relations_72687288
3642 SET staff_id = 20698
3643 WHERE affected_by_schedule_id = (
3644 SELECT id
3645 FROM buffer_schedules_72687288
3646 WHERE schedule_on = '2018-03-03'
3647 AND call_type_id = 12692
3648 )
3649
3650 (253.5ms) UPDATE buffer_staff_assigned_days_72687288 as sad
3651 inner join call_types as ct
3652 on ct.id = 12692
3653 and ct.unassigned_day = 0
3654 SET sad.is_assigned = 1
3655 WHERE sad.staff_id = 20698
3656 AND sad.schedule_on = '2018-03-03'
3657
3658 (253.6ms) -- current_oa_targets
3659 update buffer_schedules_72687288 as sc
3660 inner join buffer_counter_increments_72687288 as ci
3661 on sc.id = ci.schedule_id
3662 inner join buffer_current_oa_targets_72687288 as t
3663 on sc.staff_id = t.staff_id
3664 and sc.calendar_schedule_id = t.calendar_schedule_id
3665 and sc.period_offset = t.period_offset
3666 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3667 set t.scheduled = t.scheduled + ci.calls
3668 where sc.schedule_on = '2018-03-03'
3669 and sc.call_type_id = 12692
3670 and sc.staff_id = 20698
3671
3672 (255.8ms) -- cumulative_oa_targets
3673 update buffer_schedules_72687288 as sc
3674 inner join buffer_counter_increments_72687288 as ci
3675 on sc.id = ci.schedule_id
3676 and ci.affects_cumulative_targets = 1
3677 inner join buffer_cumulative_oa_targets_72687288 as t
3678 on sc.calendar_schedule_id = t.calendar_schedule_id
3679 and sc.staff_id = t.staff_id
3680 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3681 set t.scheduled = t.scheduled + ci.calls
3682 where sc.schedule_on = '2018-03-03'
3683 and sc.call_type_id = 12692
3684 and sc.staff_id = 20698
3685
3686 (253.3ms) -- current_cd_targets
3687 update buffer_schedules_72687288 as sc
3688 inner join buffer_counter_increments_72687288 as ci
3689 on sc.id = ci.schedule_id
3690 inner join buffer_target_assignments_72687288 as ta
3691 on sc.call_type_id = ta.call_type_id
3692 inner join buffer_current_cd_targets_72687288 as t
3693 on sc.staff_id = t.staff_id
3694 and sc.calendar_schedule_id = t.calendar_schedule_id
3695 and sc.period_offset = t.period_offset
3696 and ta.assignment_id = t.assignment_id
3697 and ta.assignment_type = t.assignment_type
3698 and sc.day_of_week_mask = t.day_of_week_mask
3699 set t.scheduled = t.scheduled + ci.calls
3700 where sc.schedule_on = '2018-03-03'
3701 and sc.call_type_id = 12692
3702 and sc.staff_id = 20698
3703
3704 (253.8ms) -- cumulative_cd_targets
3705 update buffer_schedules_72687288 as sc
3706 inner join buffer_counter_increments_72687288 as ci
3707 on sc.id = ci.schedule_id
3708 and ci.affects_cumulative_targets = 1
3709 inner join buffer_target_assignments_72687288 as ta
3710 on sc.call_type_id = ta.call_type_id
3711 inner join buffer_cumulative_cd_targets_72687288 as t
3712 on sc.calendar_schedule_id = t.calendar_schedule_id
3713 and sc.staff_id = t.staff_id
3714 and ta.assignment_id = t.assignment_id
3715 and ta.assignment_type = t.assignment_type
3716 and sc.day_of_week_mask = t.day_of_week_mask
3717 set t.scheduled = t.scheduled + ci.calls
3718 where sc.schedule_on = '2018-03-03'
3719 and sc.call_type_id = 12692
3720 and sc.staff_id = 20698
3721
3722 (257.3ms) -- current_ct_targets
3723 update buffer_schedules_72687288 as sc
3724 inner join buffer_counter_increments_72687288 as ci
3725 on sc.id = ci.schedule_id
3726 inner join buffer_target_assignments_72687288 as ta
3727 on sc.call_type_id = ta.call_type_id
3728 inner join buffer_current_ct_targets_72687288 as t
3729 on sc.staff_id = t.staff_id
3730 and sc.calendar_schedule_id = t.calendar_schedule_id
3731 and sc.period_offset = t.period_offset
3732 and ta.assignment_id = t.assignment_id
3733 and ta.assignment_type = t.assignment_type
3734 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3735 set t.scheduled = t.scheduled + ci.calls
3736 where sc.schedule_on = '2018-03-03'
3737 and sc.call_type_id = 12692
3738 and sc.staff_id = 20698
3739
3740 (253.0ms) -- cumulative_ct_targets
3741 update buffer_schedules_72687288 as sc
3742 inner join buffer_counter_increments_72687288 as ci
3743 on sc.id = ci.schedule_id
3744 and ci.affects_cumulative_targets = 1
3745 inner join buffer_target_assignments_72687288 as ta
3746 on sc.call_type_id = ta.call_type_id
3747 inner join buffer_cumulative_ct_targets_72687288 as t
3748 on sc.calendar_schedule_id = t.calendar_schedule_id
3749 and sc.staff_id = t.staff_id
3750 and ta.assignment_id = t.assignment_id
3751 and ta.assignment_type = t.assignment_type
3752 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3753 set t.scheduled = t.scheduled + ci.calls
3754 where sc.schedule_on = '2018-03-03'
3755 and sc.call_type_id = 12692
3756 and sc.staff_id = 20698
3757
3758 (257.3ms) -- current_dow_targets
3759 update buffer_schedules_72687288 as sc
3760 inner join buffer_counter_increments_72687288 as ci
3761 on sc.id = ci.schedule_id
3762 inner join buffer_current_dow_targets_72687288 as t
3763 on sc.staff_id = t.staff_id
3764 and sc.calendar_schedule_id = t.calendar_schedule_id
3765 and sc.period_offset = t.period_offset
3766 and dayofweek(sc.schedule_on) - 1 = t.wday
3767 set t.scheduled = t.scheduled + ci.calls
3768 where sc.schedule_on = '2018-03-03'
3769 and sc.call_type_id = 12692
3770 and sc.staff_id = 20698
3771
3772 (253.4ms) -- cumulative_dow_targets
3773 update buffer_schedules_72687288 as sc
3774 inner join buffer_counter_increments_72687288 as ci
3775 on sc.id = ci.schedule_id
3776 and ci.affects_cumulative_targets = 1
3777 inner join buffer_cumulative_dow_targets_72687288 as t
3778 on sc.calendar_schedule_id = t.calendar_schedule_id
3779 and sc.staff_id = t.staff_id
3780 and dayofweek(sc.schedule_on) - 1 = t.wday
3781 set t.scheduled = t.scheduled + ci.calls
3782 where sc.schedule_on = '2018-03-03'
3783 and sc.call_type_id = 12692
3784 and sc.staff_id = 20698
3785
3786 (257.5ms) -- current and cumulative point targets
3787
3788 update buffer_schedules_72687288 as sc
3789 inner join buffer_counter_increments_72687288 as ci
3790 on sc.id = ci.schedule_id
3791 inner join buffer_point_targets_72687288 as t
3792 on sc.staff_id = t.staff_id
3793 and sc.calendar_schedule_id = t.calendar_schedule_id
3794 and sc.block_id = t.block_id
3795 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3796 left join buffer_staff_rotation_points_72687288 as srp
3797 on sc.schedule_on = srp.schedule_on
3798 and sc.staff_id = srp.staff_id
3799 and sc.calendar_schedule_id = srp.calendar_schedule_id
3800 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3801 where sc.schedule_on = '2018-03-03'
3802 and sc.call_type_id = 12692
3803 and sc.staff_id = 20698
3804
3805 (253.8ms) update buffer_schedules_72687288 as sc
3806 inner join buffer_counter_increments_72687288 as ci
3807 on sc.id = ci.schedule_id
3808 inner join buffer_target_dates as td
3809 on td.session_id = 72687288
3810 and td.client_id = 319
3811 and sc.calendar_schedule_id != td.calendar_schedule_id
3812 and sc.schedule_on between td.start_date and td.end_date
3813 inner join buffer_point_targets_72687288 as t
3814 on sc.staff_id = t.staff_id
3815 and td.calendar_schedule_id = t.calendar_schedule_id
3816 and td.block_id = t.block_id
3817 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3818 left join buffer_staff_rotation_points_72687288 as srp
3819 on sc.schedule_on = srp.schedule_on
3820 and sc.staff_id = srp.staff_id
3821 and td.calendar_schedule_id = srp.calendar_schedule_id
3822 set t.scheduled = t.scheduled - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0),
3823 t.other_scheduled = t.other_scheduled + ci.points
3824 where sc.schedule_on = '2018-03-03'
3825 and sc.call_type_id = 12692
3826 and sc.staff_id = 20698
3827
3828 (258.5ms) -- current_point_targets
3829 update buffer_schedules_72687288 as sc
3830 inner join buffer_counter_increments_72687288 as ci
3831 on sc.id = ci.schedule_id
3832 inner join buffer_current_point_targets_72687288 as t
3833 on sc.staff_id = t.staff_id
3834 and sc.calendar_schedule_id = t.calendar_schedule_id
3835 and sc.period_offset = t.period_offset
3836 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3837 left join buffer_staff_rotation_points_72687288 as srp
3838 on sc.schedule_on = srp.schedule_on
3839 and sc.staff_id = srp.staff_id
3840 and sc.calendar_schedule_id = srp.calendar_schedule_id
3841 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3842 where sc.schedule_on = '2018-03-03'
3843 and sc.call_type_id = 12692
3844 and sc.staff_id = 20698
3845
3846 (253.5ms) -- cumulative_point_targets
3847 update buffer_schedules_72687288 as sc
3848 inner join buffer_counter_increments_72687288 as ci
3849 on sc.id = ci.schedule_id
3850 and ci.affects_cumulative_targets = 1
3851 inner join buffer_cumulative_point_targets_72687288 as t
3852 on sc.calendar_schedule_id = t.calendar_schedule_id
3853 and sc.staff_id = t.staff_id
3854 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3855 left join buffer_staff_rotation_points_72687288 as srp
3856 on sc.schedule_on = srp.schedule_on
3857 and sc.staff_id = srp.staff_id
3858 and sc.calendar_schedule_id = srp.calendar_schedule_id
3859 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3860 where sc.schedule_on = '2018-03-03'
3861 and sc.call_type_id = 12692
3862 and sc.staff_id = 20698
3863
3864 (257.7ms) update buffer_schedules_72687288 as sc
3865 inner join buffer_counter_increments_72687288 as ci
3866 on sc.id = ci.schedule_id
3867 inner join buffer_staff_rotation_points_72687288 as srp
3868 on sc.schedule_on = srp.schedule_on
3869 and sc.staff_id = srp.staff_id
3870 set srp.points = if(ci.affects_rotations = 1, 0.0, srp.points)
3871 where sc.schedule_on = '2018-03-03'
3872 and sc.call_type_id = 12692
3873 and sc.staff_id = 20698
3874
3875 (131.5ms) -- current and cumulative hour targets
3876
3877 update buffer_schedules_72687288 as sc
3878 inner join buffer_counter_increments_72687288 as ci
3879 on sc.id = ci.schedule_id
3880 inner join buffer_hour_targets_72687288 as t
3881 on sc.staff_id = t.staff_id
3882 and sc.calendar_schedule_id = t.calendar_schedule_id
3883 and sc.block_id = t.block_id
3884 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3885 set t.scheduled = t.scheduled + ci.hours
3886 where sc.schedule_on = '2018-03-03'
3887 and sc.call_type_id = 12692
3888 and sc.staff_id = 20698
3889
3890 (130.1ms) update buffer_schedules_72687288 as sc
3891 inner join buffer_counter_increments_72687288 as ci
3892 on sc.id = ci.schedule_id
3893 inner join buffer_target_dates as td
3894 on td.session_id = 72687288
3895 and td.client_id = 319
3896 and sc.calendar_schedule_id != td.calendar_schedule_id
3897 and sc.schedule_on between td.start_date and td.end_date
3898 inner join buffer_hour_targets_72687288 as t
3899 on sc.staff_id = t.staff_id
3900 and td.calendar_schedule_id = t.calendar_schedule_id
3901 and td.block_id = t.block_id
3902 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3903 set t.other_scheduled = t.other_scheduled + ci.hours
3904 where sc.schedule_on = '2018-03-03'
3905 and sc.call_type_id = 12692
3906 and sc.staff_id = 20698
3907
3908 (129.4ms) -- current_hour_targets
3909 update buffer_schedules_72687288 as sc
3910 inner join buffer_counter_increments_72687288 as ci
3911 on sc.id = ci.schedule_id
3912 inner join buffer_current_hour_targets_72687288 as t
3913 on sc.staff_id = t.staff_id
3914 and sc.calendar_schedule_id = t.calendar_schedule_id
3915 and sc.period_offset = t.period_offset
3916 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3917 set t.scheduled = t.scheduled + ci.hours
3918 where sc.schedule_on = '2018-03-03'
3919 and sc.call_type_id = 12692
3920 and sc.staff_id = 20698
3921
3922 (129.7ms) -- cumulative_hour_targets
3923 update buffer_schedules_72687288 as sc
3924 inner join buffer_counter_increments_72687288 as ci
3925 on sc.id = ci.schedule_id
3926 and ci.affects_cumulative_targets = 1
3927 inner join buffer_cumulative_hour_targets_72687288 as t
3928 on sc.calendar_schedule_id = t.calendar_schedule_id
3929 and sc.staff_id = t.staff_id
3930 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3931 set t.scheduled = t.scheduled + ci.hours
3932 where sc.schedule_on = '2018-03-03'
3933 and sc.call_type_id = 12692
3934 and sc.staff_id = 20698
3935
3936 (133.0ms) /* AssignmentLimitsPerPeriod */
3937 /* Unique Key: assignment_limit_id, staff_id, start_date */
3938 insert into buffer_staff_assignments_count_periods_72687288 (
3939 assignment_limit_id, staff_id, start_date, end_date, assignments_count
3940 )
3941 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
3942 1 as assignments_count
3943 from buffer_schedules_72687288 as sc
3944 inner join buffer_assignment_limit_call_types_72687288 as alct
3945 on sc.call_type_id = alct.call_type_id
3946 inner join assignment_limits as al
3947 on alct.assignment_limit_id = al.id
3948 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3949 and al.max_per_period_limit is not null
3950 inner join assign_staff_types as ast
3951 on sc.staff_id = ast.staff_id
3952 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
3953 inner join buffer_assignment_limit_staff_types_72687288 as alst
3954 on alct.assignment_limit_id = alst.assignment_limit_id
3955 and ast.staff_type_id = alst.staff_type_id
3956 inner join (
3957 select assignment_limit_id, start_date, end_date
3958 from buffer_linkage_assignments_count_periods_72687288
3959 group by assignment_limit_id, start_date, end_date
3960 order by null
3961 ) as lac
3962 on alct.assignment_limit_id = lac.assignment_limit_id
3963 and sc.schedule_on between lac.start_date and lac.end_date
3964 where sc.schedule_on = '2018-03-03'
3965 and sc.call_type_id = 12692
3966 and sc.staff_id = 20698
3967 on duplicate key update
3968 assignments_count = assignments_count + 1
3969
3970 (130.9ms) /* AssignmentLimitsPerWeek */
3971 /* Unique Key: assignment_limit_id, staff_id, start_date */
3972 insert into buffer_staff_assignments_count_weeks_72687288 (
3973 assignment_limit_id, staff_id, start_date, end_date, assignments_count
3974 )
3975 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
3976 1 as assignments_count
3977 from buffer_schedules_72687288 as sc
3978 inner join buffer_assignment_limit_call_types_72687288 as alct
3979 on sc.call_type_id = alct.call_type_id
3980 inner join assignment_limits as al
3981 on alct.assignment_limit_id = al.id
3982 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3983 and al.max_weekly_limit is not null
3984 inner join assign_staff_types as ast
3985 on sc.staff_id = ast.staff_id
3986 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
3987 inner join buffer_assignment_limit_staff_types_72687288 as alst
3988 on alct.assignment_limit_id = alst.assignment_limit_id
3989 and ast.staff_type_id = alst.staff_type_id
3990 inner join (
3991 select assignment_limit_id, start_date, end_date
3992 from buffer_linkage_assignments_count_weeks_72687288
3993 group by assignment_limit_id, start_date, end_date
3994 order by null
3995 ) as lac
3996 on alct.assignment_limit_id = lac.assignment_limit_id
3997 and sc.schedule_on between lac.start_date and lac.end_date
3998 where sc.schedule_on = '2018-03-03'
3999 and sc.call_type_id = 12692
4000 and sc.staff_id = 20698
4001 on duplicate key update
4002 assignments_count = assignments_count + 1
4003
4004 (259.2ms) /* AssignmentLimitsRolling */
4005 /* Unique Key: assignment_limit_id, staff_id, start_date */
4006 insert into buffer_staff_assignments_count_rolling_72687288 (
4007 assignment_limit_id, staff_id, start_date, end_date, assignments_count
4008 )
4009 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
4010 1 as assignments_count
4011 from buffer_schedules_72687288 as sc
4012 inner join buffer_assignment_limit_call_types_72687288 as alct
4013 on sc.call_type_id = alct.call_type_id
4014 inner join assignment_limits as al
4015 on alct.assignment_limit_id = al.id
4016 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4017 and al.max_rolling_period is not null
4018 and al.max_rolling_limit is not null
4019 inner join assign_staff_types as ast
4020 on sc.staff_id = ast.staff_id
4021 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4022 inner join buffer_assignment_limit_staff_types_72687288 as alst
4023 on alct.assignment_limit_id = alst.assignment_limit_id
4024 and ast.staff_type_id = alst.staff_type_id
4025 inner join (
4026 select assignment_limit_id, start_date, end_date
4027 from buffer_linkage_assignments_count_rolling_72687288
4028 group by assignment_limit_id, start_date, end_date
4029 order by null
4030 ) as lac
4031 on alct.assignment_limit_id = lac.assignment_limit_id
4032 and sc.schedule_on between lac.start_date and lac.end_date
4033 where sc.schedule_on = '2018-03-03'
4034 and sc.call_type_id = 12692
4035 and sc.staff_id = 20698
4036 on duplicate key update
4037 assignments_count = assignments_count + 1
4038
4039 (131.5ms) /* AssignmentLimitsConsecutive */
4040 /* Unique Key: assignment_limit_id, staff_id, schedule_on, start_date */
4041 insert ignore into buffer_staff_assignments_count_consecutive_72687288 (
4042 assignment_limit_id, staff_id, schedule_on, start_date, end_date
4043 )
4044 select lac.assignment_limit_id, sc.staff_id, sc.schedule_on, lac.start_date, lac.end_date
4045 from buffer_schedules_72687288 as sc
4046 inner join buffer_assignment_limit_call_types_72687288 as alct
4047 on sc.call_type_id = alct.call_type_id
4048 inner join assignment_limits as al
4049 on alct.assignment_limit_id = al.id
4050 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4051 and al.max_consecutive_limit is not null
4052 inner join assign_staff_types as ast
4053 on sc.staff_id = ast.staff_id
4054 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4055 inner join buffer_assignment_limit_staff_types_72687288 as alst
4056 on alct.assignment_limit_id = alst.assignment_limit_id
4057 and ast.staff_type_id = alst.staff_type_id
4058 inner join (
4059 select assignment_limit_id, start_date, end_date
4060 from buffer_linkage_assignments_count_consecutive_72687288
4061 group by assignment_limit_id, start_date, end_date
4062 order by null
4063 ) as lac
4064 on alct.assignment_limit_id = lac.assignment_limit_id
4065 and sc.schedule_on between lac.start_date and lac.end_date
4066 where sc.schedule_on = '2018-03-03'
4067 and sc.call_type_id = 12692
4068 and sc.staff_id = 20698
4069
4070INFO : (02-13 16:18:15) scheduled for: plan unit: [id:36201808] [2018-03-03 - OB], Izhack Rabbe. DEPTH(1)
4071**Airbrake: closed