· 8 years ago · Feb 25, 2018, 08:40 AM
1(176.7ms) 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 (287.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3 CalendarSchedule Load (163.1ms) 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 (199.9ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (12692, 12693, 12694)
5 CalendarSchedule Load (175.9ms) 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 (184.4ms) 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 (172.3ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
8 PopulationTimeframe Load (133.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 (153.6ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1511 LIMIT 1
10 Block Load (176.0ms) 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 11:44:03) phase: Scheduler::Base
12INFO : (02-13 11:44:03) from: 2018-03-01 to: 2018-03-31
13 Rule Load (541.8ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1511 ORDER BY applied desc, weight desc
14INFO : (02-13 11:44:04) rule configurations: CDoWT
15 - 'Cumulative Day of Week Targets' applied {"opt1"=>{"applied"=>"1"}, "opt2"=>{"applied"=>"1"}, "opt3"=>{"applied"=>"1"}, "opt4"=>{"applied"=>"1"}, "opt5"=>{"applied"=>"1"}, "opt6"=>{"applied"=>"1"}, "opt7"=>{"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 Hour Targets' not applied
20 - 'Cumulative Point Targets' not applied
21 - 'Cumulative Weekend / Weekday Assignment 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 11:44:04) started: Scheduler::Cache::Collector.collect_all
44INFO : (02-13 11:44:04) collect_staffs
45 Staff Load (149.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 (303.8ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319
47 StaffSpecialization Load (205.6ms) 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 (170.7ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` IN (711, 712) ORDER BY `specializations`.`priority` ASC
49 StaffCallType Load (178.7ms) 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 11:44:07) initial eligibles: [20702,20705,20704,20695,20701,20697,20699,20698,20706,20696]
51INFO : (02-13 11:44:07) collect_call_types
52 CallType Load (187.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 11:44:07) initial call types: [12692,12693,12694,12706]
54INFO : (02-13 11:44:07) collect_chain_links
55 ChainLink Load (164.6ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 319
56INFO : (02-13 11:44:08) collect_linkages
57INFO : (02-13 11:44:08) collect_schedules
58 Schedule Load (181.3ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 319 AND (`schedules`.`schedule_on` BETWEEN '2018-03-01' AND '2018-03-31')
59INFO : (02-13 11:44:08) collect_slots
60INFO : (02-13 11:44:08) collect_approved_call_requests
61 Request Load (178.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 11:44:08) collect_approved_no_call_requests
63 Request Load (197.2ms) 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 11:44:09) finished: Scheduler::Cache::Collector.collect_all
65INFO : (02-13 11:44:09) begin to process requests
66 CallType Load (193.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 1381 AND `call_types`.`active` = 1
67 (151.5ms) 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 (181.7ms) 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 (200.4ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 319 AND 1=0
70INFO : (02-13 11:44:10) end of requests processing
71 Block Load (204.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 (228.9ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
73 NegativeLink Load (169.3ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
74INFO : (02-13 11:44:11) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
75INFO : (02-13 11:44:11) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
76INFO : (02-13 11:44:11) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
77INFO : (02-13 11:44:11) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
78INFO : (02-13 11:44:11) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
79INFO : (02-13 11:44:11) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
80INFO : (02-13 11:44:11) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
81INFO : (02-13 11:44:11) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
82INFO : (02-13 11:44:11) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
83INFO : (02-13 11:44:11) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query015
84INFO : (02-13 11:44:11) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
85INFO : (02-13 11:44:11) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
86INFO : (02-13 11:44:11) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
87INFO : (02-13 11:44:11) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
88INFO : (02-13 11:44:11) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
89INFO : (02-13 11:44:11) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
90INFO : (02-13 11:44:11) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
91INFO : (02-13 11:44:11) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
92INFO : (02-13 11:44:11) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
93INFO : (02-13 11:44:11) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
94INFO : (02-13 11:44:11) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0703
95INFO : (02-13 11:44:11) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0704
96INFO : (02-13 11:44:11) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0705
97INFO : (02-13 11:44:11) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0706
98INFO : (02-13 11:44:11) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0707
99INFO : (02-13 11:44:11) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query0708
100INFO : (02-13 11:44:11) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query0709
101INFO : (02-13 11:44:11) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL
102INFO : (02-13 11:44:11) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
103INFO : (02-13 11:44:11) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
104INFO : (02-13 11:44:11) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
105INFO : (02-13 11:44:11) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
106INFO : (02-13 11:44:11) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query08
107INFO : (02-13 11:44:11) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query085
108INFO : (02-13 11:44:11) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query09
109INFO : (02-13 11:44:11) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query092
110INFO : (02-13 11:44:11) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query093
111INFO : (02-13 11:44:11) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query095
112INFO : (02-13 11:44:11) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query097
113INFO : (02-13 11:44:11) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query098
114INFO : (02-13 11:44:11) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
115INFO : (02-13 11:44:11) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
116INFO : (02-13 11:44:11) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
117INFO : (02-13 11:44:11) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
118INFO : (02-13 11:44:11) <-- clear: 44 - Scheduler::AutoPopulate::Queries::Query09_09_RP
119INFO : (02-13 11:44:11) <-- clear: 45 - Scheduler::AutoPopulate::Queries::Query10
120INFO : (02-13 11:44:11) <-- clear: 46 - Scheduler::AutoPopulate::Queries::QueryScore02
121INFO : (02-13 11:44:11) <-- clear: 47 - Scheduler::AutoPopulate::Queries::QueryScore03
122INFO : (02-13 11:44:11) <-- clear: 48 - Scheduler::AutoPopulate::Queries::QueryScore04
123INFO : (02-13 11:44:11) <-- clear: 49 - Scheduler::AutoPopulate::Queries::Query11
124INFO : (02-13 11:44:11) <-- clear: 50 - Scheduler::AutoPopulate::Queries::DropTables
125 (179.6ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_1164884184
126 (192.5ms) DROP TABLE IF EXISTS buffer_annual_point_targets_1164884184
127 (184.6ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_1164884184
128 (148.0ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1164884184
129 (170.6ms) DROP TABLE IF EXISTS buffer_call_days_targets_1164884184
130 (173.6ms) DROP TABLE IF EXISTS buffer_call_type_targets_1164884184
131 (179.5ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_1164884184
132 (180.2ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_1164884184
133 (179.2ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_1164884184
134 (179.8ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_1164884184
135 (181.0ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_1164884184
136 (186.3ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_1164884184
137 (185.7ms) DROP TABLE IF EXISTS buffer_current_cd_targets_1164884184
138 (164.9ms) DROP TABLE IF EXISTS buffer_current_ct_targets_1164884184
139 (175.7ms) DROP TABLE IF EXISTS buffer_current_dow_targets_1164884184
140 (182.9ms) DROP TABLE IF EXISTS buffer_current_hour_targets_1164884184
141 (178.7ms) DROP TABLE IF EXISTS buffer_current_oa_targets_1164884184
142 (183.7ms) DROP TABLE IF EXISTS buffer_current_point_targets_1164884184
143 (191.5ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_1164884184
144 (185.1ms) DROP TABLE IF EXISTS buffer_hour_targets_1164884184
145 (180.6ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_1164884184
146 (199.7ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_1164884184
147 (177.3ms) DROP TABLE IF EXISTS buffer_linkage_targets_1164884184
148 (210.4ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_1164884184
149 (168.1ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_1164884184
150 (136.9ms) DROP TABLE IF EXISTS buffer_location_privileges_1164884184
151 (202.3ms) DROP TABLE IF EXISTS buffer_periods_1164884184
152 (175.2ms) DROP TABLE IF EXISTS buffer_point_targets_1164884184
153 (177.7ms) DROP TABLE IF EXISTS buffer_schedules_1164884184
154 (177.8ms) DROP TABLE IF EXISTS buffer_staff_rotation_points_1164884184
155 (163.8ms) DROP TABLE IF EXISTS buffer_target_call_types_1164884184
156 (179.4ms) DROP TABLE IF EXISTS buffer_target_daily_points_1164884184
157 (172.6ms) DROP TABLE IF EXISTS buffer_target_dates_list_1164884184
158 (175.9ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_1164884184
159 (175.6ms) DROP TABLE IF EXISTS buffer_targets_1164884184
160 (165.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_1164884184
161 (170.0ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_1164884184
162 (171.2ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_1164884184
163 (185.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_1164884184
164 (172.8ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_1164884184
165 (175.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_1164884184
166 (178.1ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_1164884184
167 (179.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_1164884184
168 (618.6ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1164884184
169 (168.5ms) DROP TABLE IF EXISTS buffer_call_type_assignments_1164884184
170 (157.2ms) DROP TABLE IF EXISTS buffer_combined_availabilities_1164884184
171 (194.7ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_1164884184
172 (185.5ms) DROP TABLE IF EXISTS buffer_counter_increments_1164884184
173 (141.1ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_1164884184
174 (213.4ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_consecutive_1164884184
175 (140.4ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_periods_1164884184
176 (144.6ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_rolling_1164884184
177 (135.6ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_weeks_1164884184
178 (179.0ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_1164884184
179 (140.2ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_1164884184
180 (158.8ms) DROP TABLE IF EXISTS buffer_linkage_patterns_1164884184
181 (138.2ms) DROP TABLE IF EXISTS buffer_linkages_1164884184
182 (152.3ms) DROP TABLE IF EXISTS buffer_location_privileges_1164884184
183 (136.3ms) DROP TABLE IF EXISTS buffer_min_distances_1164884184
184 (179.1ms) DROP TABLE IF EXISTS buffer_negative_relations_1164884184
185 (137.4ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_1164884184
186 (146.1ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_1164884184
187 (136.4ms) DROP TABLE IF EXISTS buffer_rule_scores_1164884184
188 (157.2ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_1164884184
189 (138.2ms) DROP TABLE IF EXISTS buffer_schedule_linkages_1164884184
190 (500.7ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_1164884184
191 (183.9ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_1164884184
192 (174.4ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_1164884184
193 (172.0ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_periods_1164884184
194 (179.3ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_rolling_1164884184
195 (208.3ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_weeks_1164884184
196 (170.7ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_consecutive_1164884184
197 (175.6ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_1164884184
198 (172.6ms) DROP TABLE IF EXISTS buffer_staff_specializations_1164884184
199 (172.0ms) DROP TABLE IF EXISTS buffer_static_availabilities_1164884184
200 (173.1ms) DROP TABLE IF EXISTS buffer_target_assignments_1164884184
201 (164.0ms) DROP VIEW IF EXISTS buffer_weekly_call_type_targets_1164884184
202
203 (172.1ms) DROP VIEW IF EXISTS buffer_current_weekly_ct_targets_1164884184
204
205 (171.5ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_ct_targets_1164884184
206
207 (179.2ms) DROP VIEW IF EXISTS buffer_weekly_targets_1164884184
208
209 (178.3ms) DROP VIEW IF EXISTS buffer_current_weekly_oa_targets_1164884184
210
211 (169.8ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_oa_targets_1164884184
212
213 (179.5ms) DROP VIEW IF EXISTS buffer_weekly_point_targets_1164884184
214
215 (180.4ms) DROP VIEW IF EXISTS buffer_current_weekly_point_targets_1164884184
216
217 (181.9ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_point_targets_1164884184
218
219 (193.7ms) DROP VIEW IF EXISTS buffer_weekly_hour_targets_1164884184
220
221 (186.5ms) DROP VIEW IF EXISTS buffer_current_weekly_hour_targets_1164884184
222
223 (180.4ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_hour_targets_1164884184
224
225 (174.7ms) delete from buffer_target_dates
226 where session_id = 1164884184
227 and client_id = 319
228
229INFO : (02-13 11:44:27) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
230 (182.5ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_1164884184 LIKE buffer_assignment_limit_call_types
231 (197.6ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_1164884184 LIKE buffer_assignment_limit_periods
232 (186.1ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_1164884184 LIKE buffer_assignment_limit_staff_types
233 (187.4ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1164884184 LIKE buffer_auto_populate_slots
234 (193.1ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_1164884184 LIKE buffer_call_type_assignments
235 (156.2ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_1164884184 LIKE buffer_combined_availabilities
236 (189.8ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_1164884184 LIKE buffer_combined_availability_linkages
237 (182.0ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_1164884184 LIKE buffer_counter_increments
238 (183.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_1164884184 LIKE buffer_linkage_assigned_days
239 (191.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_consecutive_1164884184 LIKE buffer_linkage_assignments_count_consecutive
240 (181.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_periods_1164884184 LIKE buffer_linkage_assignments_count_periods
241 (191.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_rolling_1164884184 LIKE buffer_linkage_assignments_count_rolling
242 (164.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_weeks_1164884184 LIKE buffer_linkage_assignments_count_weeks
243 (176.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_1164884184 LIKE buffer_linkage_call_type_assignments
244 (180.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_1164884184 LIKE buffer_linkage_dow_targets
245 (178.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_1164884184 LIKE buffer_linkage_patterns
246 (184.0ms) CREATE TABLE IF NOT EXISTS buffer_linkages_1164884184 LIKE buffer_linkages
247 (187.7ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1164884184 LIKE buffer_location_privileges
248 (191.2ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_1164884184 LIKE buffer_min_distances
249 (217.9ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_1164884184 LIKE buffer_negative_relations
250 (602.2ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_1164884184 LIKE buffer_rule_calc_filters
251 (165.1ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_1164884184 LIKE buffer_rule_calc_schedules
252 (165.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_1164884184 LIKE buffer_rule_scores
253 (155.0ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_1164884184 LIKE buffer_rule_scores_with_corrections
254 (161.9ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_1164884184 LIKE buffer_schedule_linkages
255 (162.1ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_1164884184 LIKE buffer_schedules_linkage_patterns
256 (677.9ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_1164884184 LIKE buffer_specialization_daily_counts
257 (155.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_1164884184 LIKE buffer_staff_assigned_days
258 (165.5ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_periods_1164884184 LIKE buffer_staff_assignments_count_periods
259 (142.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_rolling_1164884184 LIKE buffer_staff_assignments_count_rolling
260 (165.2ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_weeks_1164884184 LIKE buffer_staff_assignments_count_weeks
261 (155.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_consecutive_1164884184 LIKE buffer_staff_assignments_count_consecutive
262 (182.4ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_1164884184 LIKE buffer_staff_daily_counts
263 (202.4ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_1164884184 LIKE buffer_staff_specializations
264 (147.5ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_1164884184 LIKE buffer_static_availabilities
265 (531.1ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_1164884184 LIKE buffer_target_assignments
266 (336.9ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_1164884184 LIKE buffer_annual_hour_targets
267 (233.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_1164884184 LIKE buffer_annual_point_targets
268 (162.2ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_1164884184 LIKE buffer_annual_targets_staff_totals
269 (181.9ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1164884184 LIKE buffer_auto_populate_slots
270 (187.4ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_1164884184 LIKE buffer_call_days_targets
271 (163.4ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_1164884184 LIKE buffer_call_type_targets
272 (147.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_1164884184 LIKE buffer_cumulative_cd_targets
273 (141.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_1164884184 LIKE buffer_cumulative_ct_targets
274 (188.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_1164884184 LIKE buffer_cumulative_dow_targets
275 (140.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_1164884184 LIKE buffer_cumulative_hour_targets
276 (171.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_1164884184 LIKE buffer_cumulative_oa_targets
277 (171.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_1164884184 LIKE buffer_cumulative_point_targets
278 (158.6ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_1164884184 LIKE buffer_current_cd_targets
279 (154.9ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_1164884184 LIKE buffer_current_ct_targets
280 (143.4ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_1164884184 LIKE buffer_current_dow_targets
281 (151.6ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_1164884184 LIKE buffer_current_hour_targets
282 (169.4ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_1164884184 LIKE buffer_current_oa_targets
283 (148.8ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_1164884184 LIKE buffer_current_point_targets
284 (162.2ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_1164884184 LIKE buffer_day_of_week_targets
285 (200.3ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_1164884184 LIKE buffer_hour_targets
286 (166.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_1164884184 LIKE buffer_linkage_call_days_targets
287 (158.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_1164884184 LIKE buffer_linkage_call_type_targets
288 (172.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_1164884184 LIKE buffer_linkage_targets
289 (174.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_1164884184 LIKE buffer_linkage_hour_targets
290 (177.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_1164884184 LIKE buffer_linkage_point_targets
291 (164.8ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1164884184 LIKE buffer_location_privileges
292 (179.9ms) CREATE TABLE IF NOT EXISTS buffer_periods_1164884184 LIKE buffer_periods
293 (158.3ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_1164884184 LIKE buffer_point_targets
294 (146.4ms) CREATE TABLE IF NOT EXISTS buffer_schedules_1164884184 LIKE buffer_schedules
295 (207.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_rotation_points_1164884184 LIKE buffer_staff_rotation_points
296 (532.8ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_1164884184 LIKE buffer_target_call_types
297 (203.2ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_1164884184 LIKE buffer_target_daily_points
298 (159.7ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_1164884184 LIKE buffer_target_dates_list
299 (203.4ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_1164884184 LIKE buffer_target_primary_availabilities
300 (279.7ms) CREATE TABLE IF NOT EXISTS buffer_targets_1164884184 LIKE buffer_targets
301 (161.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_1164884184 LIKE buffer_tmp_targets_availabilities
302 (156.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_1164884184 LIKE buffer_tmp_targets_availabilities_exclusions
303 (231.4ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_1164884184 LIKE buffer_tmp_targets_client_totals
304 (189.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_1164884184 LIKE buffer_tmp_targets_scheduled
305 (237.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_1164884184 LIKE buffer_tmp_targets_staff_totals
306INFO : (02-13 11:44:42) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
307 (188.2ms) 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 (181.0ms) 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 (262.9ms) INSERT INTO buffer_auto_populate_slots_1164884184
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 11:44:43) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
329 (180.9ms) insert into buffer_auto_populate_slots_1164884184/* 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_1164884184/* 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_1164884184/* 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 11:44:43) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
370 (143.2ms) insert into buffer_auto_populate_slots_1164884184 (
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_1164884184 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_1164884184 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 11:44:44) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
411 (234.8ms) 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_1164884184/* 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 (157.7ms) insert into buffer_schedules_1164884184 (
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_1164884184 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_1164884184 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 11:44:44) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
522 (192.6ms) update buffer_schedules_1164884184 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 (217.2ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
535 (168.1ms) insert into buffer_periods_1164884184 (
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_1164884184 as sc
563 inner join buffer_auto_populate_slots_1164884184 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 (198.6ms) update buffer_schedules_1164884184 as sc
601 inner join buffer_periods_1164884184 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 11:44:45) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
608 (167.5ms) delete from buffer_target_dates
609 where session_id = 1164884184
610
611 (172.6ms) select distinct calendar_schedule_id
612 from buffer_periods_1164884184
613
614 (245.9ms) 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 1164884184 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 FALSE
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 t1.within_cumulative_period = 1 /* AnnualDayOfWeekMixRule */
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 - (3 - 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_1164884184
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_1164884184 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 - (3 - 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 - 588.1840000000001ms
1097 (133.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 = 1164884184
1106 and build_cd_targets = 0
1107 and cd_targets_source = ''
1108 and (
1109 build_dow_targets = 1 /* MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule */
1110 )
1111
1112 (172.2ms) /*
1113 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1114 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1115 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1116 */
1117 update buffer_target_dates
1118 set
1119 build_dow_targets = 1, dow_targets_source = "" /* MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule */
1120 where
1121 session_id = 1164884184
1122 and build_cd_targets = 1
1123
1124Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0102 - 306.656ms
1125 (166.4ms) /*
1126 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1127 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1128 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1129 */
1130 insert into buffer_call_days_targets_1164884184 (
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 = 1164884184
1164 and td.build_cd_targets = 0
1165 and td.cd_targets_source in ('published', 'both')
1166 and (
1167 td.build_dow_targets = 1 /* MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule */
1168 )
1169 order by null
1170
1171 (567.1ms) /*
1172 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1173 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1174 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1175 */
1176 insert into buffer_call_days_targets_1164884184 (
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 = 1164884184
1210 and td.build_cd_targets = 0
1211 and td.cd_targets_source in ('unpublished', 'both')
1212 and (
1213 td.build_dow_targets = 1 /* MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule */
1214 )
1215 order by null
1216
1217Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0103 - 735.4490000000001ms
1218 (141.3ms) 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 = 1164884184
1226 order by null
1227
1228Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets02 - 141.857ms
1229 (163.9ms) /* AnnualDayOfWeekMixRule */
1230 insert into buffer_cumulative_dow_targets_1164884184 (
1231 staff_id,
1232 wday,
1233 calendar_schedule_id,
1234 raw_target,
1235 adjusted_target,
1236 scheduled,
1237 available_days
1238 )
1239 select
1240 t.staff_id,
1241 t.wday,
1242 t.calendar_schedule_id,
1243 sum(t.raw_target) as raw_target,
1244 sum(t.adjusted_target) as adjusted_target,
1245 sum(t.scheduled) as scheduled,
1246 sum(t.available_days) as available_days
1247 from (
1248 select
1249 t.calendar_schedule_id,
1250 t.block_id,
1251 t.staff_id,
1252 t.wday,
1253 t.raw_target,
1254 t.target as adjusted_target,
1255 t.scheduled,
1256 t.available_days
1257 from buffer_target_dates as td
1258 inner join buffer_day_of_week_targets_1164884184 as t
1259 on td.calendar_schedule_id = t.calendar_schedule_id
1260 and td.block_id = t.block_id
1261 where td.session_id = 1164884184
1262 and td.build_dow_targets = 1
1263 and td.within_cumulative_period = 1
1264 union
1265 select
1266 dowt.calendar_schedule_id,
1267 dowt.block_id,
1268 dowt.staff_id,
1269 dowt.wday,
1270 dowt.raw_target,
1271 dowt.target,
1272 dowt.scheduled,
1273 dowt.available_days
1274 from buffer_target_dates as td
1275 inner join day_of_week_targets as dowt
1276 on td.calendar_schedule_id = dowt.calendar_schedule_id
1277 and td.block_id = dowt.block_id
1278 where td.session_id = 1164884184
1279 and td.build_dow_targets = 0
1280 and td.dow_targets_source = 'published'
1281 and td.within_cumulative_period = 1
1282 union
1283 select
1284 udowt.calendar_schedule_id,
1285 udowt.block_id,
1286 udowt.staff_id,
1287 udowt.wday,
1288 udowt.raw_target,
1289 udowt.target,
1290 udowt.scheduled,
1291 udowt.available_days
1292 from buffer_target_dates as td
1293 inner join unpublished_day_of_week_targets as udowt
1294 on td.calendar_schedule_id = udowt.calendar_schedule_id
1295 and td.block_id = udowt.block_id
1296 where td.session_id = 1164884184
1297 and td.build_dow_targets = 0
1298 and td.dow_targets_source = 'unpublished'
1299 and td.within_cumulative_period = 1
1300 union all
1301 select
1302 tad.calendar_schedule_id,
1303 tad.block_id,
1304 tad.staff_id,
1305 log2(tad.day_of_week_mask) as wday,
1306 0 as raw_target,
1307 0 as target,
1308 tad.adjustment_value as scheduled,
1309 0 as available_days
1310 from buffer_target_dates as td
1311 inner join target_adjustment_details as tad
1312 on td.calendar_schedule_id = tad.calendar_schedule_id
1313 and td.block_id = tad.block_id
1314 and tad.staff_id > 0
1315 where td.session_id = 1164884184
1316 and td.within_cumulative_period = 1
1317 order by null
1318 ) as t
1319 group by
1320 t.staff_id,
1321 t.wday,
1322 t.calendar_schedule_id
1323 order by null
1324
1325Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0702 - 164.688ms
1326Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0703 - 0.064ms
1327INFO : (02-13 11:44:47) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
1328 (277.7ms) insert into buffer_counter_increments_1164884184 (
1329 schedule_id,
1330 calls,
1331 points,
1332 hours,
1333 affects_cumulative_targets,
1334 affects_rotations
1335 )
1336 select
1337 sc.id as schedule_id,
1338 if(ct.included_in_targets > 0, 1, 0) as calls,
1339 ifnull(if(h.observed_date is null or dp.point is not null, dp.point, dp_2.point), 0.0) as points,
1340 ct.hours,
1341 if(b.display_order between (t1.timeframe_ending - (3 /* cumulative targets period */ - 1)) and t1.timeframe_ending, 1, 0) as affects_cumulative_targets,
1342 if(ct.display_concurrent_rotations = 1, 0, 1) as affects_rotations
1343 from buffer_schedules_1164884184 as sc
1344 inner join (
1345 select
1346 id as call_type_id,
1347 included_in_targets,
1348 display_concurrent_rotations,
1349 ifnull(
1350 timestampdiff(
1351 minute,
1352 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
1353 concat(
1354 '1900-01-0',
1355 if(
1356 (start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0)
1357 and (timediff(maketime(end_time_hour, end_time_minute, 0), maketime(start_time_hour, start_time_minute, 0)) <= 0),
1358 '2', '1'
1359 ),
1360 ' ',
1361 maketime(end_time_hour, end_time_minute, 0)
1362 )
1363 ) / 60,
1364 0.0
1365 ) as hours
1366 from call_types
1367 where client_id = 319
1368 and active = 1
1369 order by null
1370 ) as ct
1371 on sc.call_type_id = ct.call_type_id
1372 left join (
1373 select
1374 i_hd.observed_date,
1375 if(i_hd.observed_date > i_hd.extended_date, i_hd.extended_date, i_hd.observed_date) as date_from,
1376 if(i_hd.observed_date < i_hd.extended_date, i_hd.extended_date, i_hd.observed_date) as date_to
1377 from holidays as i_h
1378 inner join holiday_dates as i_hd
1379 on i_h.id = i_hd.holiday_id
1380 where
1381 i_h.client_id = 319
1382 and (
1383 i_h.observed = 1
1384 or i_h.extended = 1
1385 )
1386 order by null
1387 ) as h
1388 on sc.schedule_on between h.date_from and h.date_to
1389 left join assignment_annotations as aa
1390 on ct.call_type_id = aa.assignmentable_id
1391 and aa.assignmentable_type = 'CallType'
1392 and aa.annotation_id = 0 /* annotation = "None" */
1393 and aa.applicable = 1
1394 left join assignment_annotation_daily_points as dp
1395 on aa.id = dp.assignment_annotation_id
1396 and if(
1397 h.observed_date is null,
1398 dayofweek(sc.schedule_on) - 1,
1399 if(sc.schedule_on = h.observed_date, 7, 8)
1400 ) = dp.day
1401 left join assignment_annotation_daily_points as dp_2
1402 on aa.id = dp_2.assignment_annotation_id
1403 and dayofweek(sc.schedule_on) - 1 = dp_2.day
1404 left join blocks as b
1405 on sc.block_id = b.id
1406 left join (
1407 select calendar_schedule_id,
1408 max(if(should_be_populated = 1, end_block_display_order, null)) as timeframe_ending
1409 from buffer_periods_1164884184
1410 group by calendar_schedule_id
1411 order by null
1412 ) as t1
1413 on sc.calendar_schedule_id = t1.calendar_schedule_id
1414 where sc.should_be_evaluated = 1
1415 order by null
1416
1417INFO : (02-13 11:44:47) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
1418 (151.3ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
1419 insert into buffer_linkage_patterns_1164884184 (
1420 period_offset, calendar_schedule_id, block_id, block_start_date, block_start_dow, block_length,
1421 linkage_pattern_id, pattern_start_dow, days_before_start, pattern_length, start_offset, iterations_count
1422 )
1423 select
1424 b1.period_offset,
1425 b1.calendar_schedule_id,
1426 b1.block_id,
1427 b2.start_date as block_start_date,
1428 dayofweek(b2.start_date) as block_start_dow,
1429 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
1430 lp.id as linkage_pattern_id,
1431 (lp.start_on + 1) as pattern_start_dow,
1432 lp.days_before_start,
1433 (lp.length * 7) as pattern_length,
1434 null as start_offset,
1435 null as iterations_count
1436 from (
1437 select calendar_schedule_id, block_id, period_offset
1438 from buffer_schedules_1164884184
1439 group by calendar_schedule_id, block_id, period_offset
1440 order by null
1441 ) as b1
1442 inner join blocks as b2
1443 on b1.block_id = b2.id
1444 inner join linkage_patterns as lp
1445 on lp.client_id = 319
1446 and b1.calendar_schedule_id = lp.calendar_schedule_id
1447 order by null
1448
1449 (142.7ms) -- 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
1450 update buffer_linkage_patterns_1164884184
1451 set start_offset = if(
1452 (pattern_start_dow + if(pattern_start_dow < block_start_dow, 7, 0) - block_start_dow) <= days_before_start,
1453 (pattern_start_dow + if(pattern_start_dow < block_start_dow, 7, 0) - block_start_dow),
1454 (block_start_dow + if(block_start_dow < pattern_start_dow, 7, 0) - pattern_start_dow) * (-1)
1455 )
1456
1457 (135.6ms) -- In some cases (when a block and a pattern are starting on different day of week or when a pattern is shorter than a block)
1458 -- a pattern should be repeated couple of times (in order to cover the entire block)
1459 update buffer_linkage_patterns_1164884184
1460 set iterations_count = ceiling((block_length + if(start_offset < 0, abs(start_offset), 0)) * 1.0 / pattern_length)
1461
1462 (168.7ms) -- Generate an assignments sequence for every linkage pattern
1463 insert into buffer_schedules_linkage_patterns_1164884184 (
1464 call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1465 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1466 )
1467 select
1468 lps.assignment_id as call_type_id,
1469 null as staff_id,
1470 if(
1471 lp.start_offset < 0,
1472 date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day),
1473 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
1474 ) as schedule_on,
1475 lp.period_offset,
1476 null as day_of_week_mask,
1477 null as display_order,
1478 null as week_number,
1479 0 as should_be_evaluated,
1480 0 as should_be_populated,
1481 lp.linkage_pattern_id as linkage_instance_id,
1482 lp.block_id,
1483 lp.calendar_schedule_id
1484 from numeric_sequence as ns
1485 inner join buffer_linkage_patterns_1164884184 as lp
1486 on ns.item_number <= (lp.iterations_count - 1)
1487 inner join linkage_pattern_slots as lps
1488 on lp.linkage_pattern_id = lps.linkage_pattern_id
1489 and lps.assignment_type = 'CallType'
1490 where
1491 if(
1492 lp.start_offset < 0,
1493 (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset),
1494 (ns.item_number * lp.pattern_length + lps.offset)
1495 ) between 0 and (lp.block_length - 1)
1496 order by null
1497
1498 (156.3ms) -- Fill the columns that we left blank in the previous "INSERT"
1499 update buffer_schedules_linkage_patterns_1164884184 as slp
1500 inner join clients as cl
1501 on cl.id = 319
1502 inner join call_types as ct
1503 on slp.call_type_id = ct.id
1504 left join buffer_schedules_1164884184 as bs
1505 on slp.schedule_on = bs.schedule_on
1506 and slp.call_type_id = bs.call_type_id
1507 set slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
1508 slp.display_order = ct.display_order,
1509 slp.week_number = yearweek(slp.schedule_on, if(cl.start_of_week = 0, 2, 7)),
1510 slp.should_be_evaluated = ifnull(bs.should_be_evaluated, 0),
1511 slp.should_be_populated = ifnull(bs.should_be_populated, 0)
1512
1513 (176.9ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
1514 delete slp_1
1515 from buffer_schedules_linkage_patterns_1164884184 as slp_1
1516 inner join (
1517 select linkage_instance_id
1518 from buffer_schedules_linkage_patterns_1164884184
1519 group by linkage_instance_id
1520 having max(should_be_evaluated) = 0
1521 and max(should_be_populated) = 0
1522 order by null
1523 ) as slp_2
1524 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
1525
1526 (154.8ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
1527 update buffer_schedules_linkage_patterns_1164884184 as slp
1528 inner join buffer_schedules_1164884184 as bs_1
1529 on slp.schedule_on = bs_1.schedule_on
1530 and slp.call_type_id = bs_1.call_type_id
1531 inner join buffer_schedules_1164884184 as bs_2
1532 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
1533 set bs_2.linkage_instance_id = slp.linkage_instance_id
1534
1535 (214.3ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
1536 -- should be copied over to the buffer schedules table (used during the auto-populate process)
1537 insert into buffer_schedules_1164884184 (
1538 call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1539 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1540 )
1541 select call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1542 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1543 from buffer_schedules_linkage_patterns_1164884184
1544 where should_be_evaluated = 0
1545 and should_be_populated = 0
1546
1547INFO : (02-13 11:44:48) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query015
1548 (178.2ms) insert into buffer_schedules_1164884184 (
1549 call_type_id, staff_id, schedule_on, day_of_week_mask, display_order,
1550 should_be_evaluated, should_be_populated, linkage_instance_id
1551 )
1552 select distinct
1553 schedules_call_overlap.call_type_id,
1554 schedules_call_overlap.staff_id,
1555 schedules_call_overlap.schedule_on,
1556 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
1557 call_types.display_order,
1558 0 as should_be_evaluated,
1559 0 as should_be_populated,
1560 -1 as linkage_instance_id
1561 from (
1562 select schedule_on
1563 from buffer_schedules_1164884184
1564 where should_be_evaluated = 1
1565 group by schedule_on
1566 order by null
1567 ) as buffer_schedules_1
1568 inner join schedules as schedules_call_overlap
1569 on schedules_call_overlap.client_id = 319
1570 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1571 left join buffer_schedules_1164884184 as buffer_schedules_2
1572 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1573 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1574 inner join call_types
1575 on schedules_call_overlap.call_type_id = call_types.id
1576 where buffer_schedules_2.id is null
1577 order by null
1578
1579INFO : (02-13 11:44:48) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1580INFO : (02-13 11:44:48) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1581INFO : (02-13 11:44:48) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1582INFO : (02-13 11:44:48) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1583INFO : (02-13 11:44:48) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1584 (153.1ms) insert into buffer_schedules_1164884184 (
1585 call_type_id,
1586 staff_id,
1587 schedule_on,
1588 day_of_week_mask,
1589 display_order,
1590 should_be_evaluated,
1591 should_be_populated,
1592 linkage_instance_id
1593 )
1594 select distinct straight_join
1595 schedules.call_type_id,
1596 schedules.staff_id,
1597 schedules.schedule_on,
1598 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1599 call_types.display_order,
1600 0 as should_be_evaluated,
1601 0 as should_be_populated,
1602 -1 as linkage_instance_id
1603 from
1604 buffer_schedules_1164884184 as buffer_schedules_1
1605 inner join negative_links_influences use index (ix_unique_links)
1606 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1607 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1608 and negative_links_influences.source_type = 'CallType'
1609 and negative_links_influences.target_type = 'CallType'
1610 inner join schedules use index (ix_schedules_call_date)
1611 on date_add(
1612 buffer_schedules_1.schedule_on,
1613 interval negative_links_influences.offset day
1614 ) = schedules.schedule_on
1615 and negative_links_influences.target_id = schedules.call_type_id
1616 left join buffer_schedules_1164884184 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1617 on schedules.schedule_on = buffer_schedules_2.schedule_on
1618 and schedules.call_type_id = buffer_schedules_2.call_type_id
1619 inner join call_types
1620 on schedules.call_type_id = call_types.id
1621 where
1622 buffer_schedules_1.linkage_instance_id >= 0
1623 and buffer_schedules_1.should_be_evaluated = 1
1624 and buffer_schedules_2.id is null
1625 order by null
1626
1627INFO : (02-13 11:44:49) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1628 (139.1ms) insert into buffer_schedules_1164884184 (
1629 call_type_id,
1630 staff_id,
1631 schedule_on,
1632 day_of_week_mask,
1633 display_order,
1634 should_be_evaluated,
1635 should_be_populated,
1636 linkage_instance_id
1637 )
1638 select distinct straight_join
1639 0 as call_type_id,
1640 vacations.staff_id,
1641 vacations.schedule_on,
1642 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1643 -1 as display_order,
1644 0 as should_be_evaluated,
1645 0 as should_be_populated,
1646 -1 as linkage_instance_id
1647 from
1648 buffer_schedules_1164884184 as buffer_schedules_1
1649 inner join negative_links_influences use index (ix_unique_links)
1650 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1651 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1652 and negative_links_influences.source_type = 'CallType'
1653 and negative_links_influences.target_id is null
1654 and negative_links_influences.target_type = 'Vacation'
1655 inner join vacations use index (ix_vacations_on_schedule_and_client)
1656 on vacations.client_id = 319 /* client_id */
1657 and date_add(
1658 buffer_schedules_1.schedule_on,
1659 interval negative_links_influences.offset day
1660 ) = vacations.schedule_on
1661 and vacations.vacation_type = 0 /* vacation */
1662 and vacations.status in (3) /* based on the "non-working requests" settings */
1663 left join buffer_schedules_1164884184 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1664 on vacations.staff_id = buffer_schedules_2.staff_id
1665 and vacations.schedule_on = buffer_schedules_2.schedule_on
1666 and buffer_schedules_2.call_type_id = 0
1667 where
1668 buffer_schedules_1.linkage_instance_id >= 0
1669 and buffer_schedules_1.should_be_evaluated = 1
1670 and buffer_schedules_2.id is null
1671 order by null
1672
1673 (138.9ms) insert into buffer_schedules_1164884184 (
1674 call_type_id,
1675 staff_id,
1676 schedule_on,
1677 day_of_week_mask,
1678 display_order,
1679 should_be_evaluated,
1680 should_be_populated,
1681 linkage_instance_id
1682 )
1683 select distinct straight_join
1684 (-1 * negative_links_influences.target_id) as call_type_id,
1685 vacations.staff_id,
1686 vacations.schedule_on,
1687 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1688 -1 as display_order,
1689 0 as should_be_evaluated,
1690 0 as should_be_populated,
1691 -1 as linkage_instance_id
1692 from
1693 buffer_schedules_1164884184 as buffer_schedules_1
1694 inner join negative_links_influences use index (ix_unique_links)
1695 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1696 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1697 and negative_links_influences.source_type = 'CallType'
1698 and negative_links_influences.target_id > 0
1699 and negative_links_influences.target_type = 'NonClinicalType'
1700 inner join vacations use index (ix_vacations_on_schedule_and_client)
1701 on vacations.client_id = 319 /* client_id */
1702 and date_add(
1703 buffer_schedules_1.schedule_on,
1704 interval negative_links_influences.offset day
1705 ) = vacations.schedule_on
1706 and vacations.vacation_type = 1 /* non-clinical */
1707 and negative_links_influences.target_id = vacations.non_clinical_type_id
1708 and vacations.status in (3) /* based on the "non-working requests" settings */
1709 left join buffer_schedules_1164884184 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1710 on vacations.staff_id = buffer_schedules_2.staff_id
1711 and vacations.schedule_on = buffer_schedules_2.schedule_on
1712 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1713 where
1714 buffer_schedules_1.linkage_instance_id >= 0
1715 and buffer_schedules_1.should_be_evaluated = 1
1716 and buffer_schedules_2.id is null
1717 order by null;
1718
1719INFO : (02-13 11:44:49) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1720 (137.8ms) insert into buffer_linkages_1164884184 (
1721 linkage_instance_id,
1722 slots_count_total,
1723 has_conflicts
1724 )
1725 select straight_join
1726 schedules.linkage_instance_id,
1727 count(1) as slots_count_total,
1728 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1729 from
1730 buffer_schedules_1164884184 as schedules
1731 left join (
1732 select straight_join
1733 linkage_relations.linkage_template_id
1734 from linkage_relations
1735 inner join negative_links_influences use index (ix_unique_links)
1736 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1737 and linkage_relations.call_type_id = negative_links_influences.source_id
1738 and negative_links_influences.source_type = 'CallType'
1739 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1740 and negative_links_influences.target_type = 'CallType'
1741 and linkage_relations.distance_in_days = negative_links_influences.offset
1742 where linkage_relations.client_id = 319 /* client_id */
1743 and (
1744 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1745 or linkage_relations.distance_in_days != 0
1746 )
1747 group by linkage_relations.linkage_template_id
1748 order by null
1749 ) as linkage_conflicts
1750 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1751 where schedules.linkage_instance_id >= 0
1752 group by
1753 schedules.linkage_instance_id,
1754 has_conflicts
1755 order by null
1756
1757INFO : (02-13 11:44:49) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1758 (175.7ms) insert into buffer_target_assignments_1164884184 (
1759 call_type_id,
1760 target_group_id,
1761 assignment_id,
1762 assignment_type
1763 )
1764 select
1765 t1.call_type_id,
1766 ifnull(tg.id, 0) as target_group_id,
1767 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1768 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1769 from (
1770 select ct.id as call_type_id
1771 from buffer_schedules_1164884184 as sc
1772 inner join call_types as ct
1773 on sc.call_type_id = ct.id
1774 and ct.active = 1
1775 and ct.included_in_targets > 0
1776 where linkage_instance_id >= 0
1777 group by ct.id
1778 order by null
1779 ) as t1
1780 left join target_group_calls as tgc
1781 on t1.call_type_id = tgc.call_type_id
1782 left join target_groups as tg
1783 on tgc.target_group_id = tg.id
1784 and tg.active = 1
1785 order by null;
1786
1787
1788INFO : (02-13 11:44:49) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1789INFO : (02-13 11:44:49) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1790INFO : (02-13 11:44:49) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0703
1791INFO : (02-13 11:44:49) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0704
1792INFO : (02-13 11:44:49) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0705
1793 (136.4ms) insert into buffer_linkage_call_type_assignments_1164884184 (
1794 linkage_instance_id,
1795 calendar_schedule_id,
1796 period_offset,
1797 call_type_id,
1798 weekday_assignments,
1799 max_weekday_assignments,
1800 weekend_assignments,
1801 max_weekend_assignments
1802 )
1803 select
1804 s.linkage_instance_id,
1805 s.calendar_schedule_id,
1806 s.period_offset,
1807 s.call_type_id,
1808 sum(if(s.day_of_week_mask & 62 > 0, 1, 0)) as weekday_assignments,
1809 ct.max_weekday_assignments,
1810 sum(if(s.day_of_week_mask & 65 > 0, 1, 0)) as weekend_assignments,
1811 ct.max_weekend_assignments
1812 from buffer_schedules_1164884184 as s
1813 inner join call_types as ct
1814 on s.call_type_id = ct.id
1815 and ct.active = 1
1816 where s.linkage_instance_id >= 0
1817 and s.staff_id is null
1818 group by
1819 s.linkage_instance_id,
1820 s.calendar_schedule_id,
1821 s.period_offset,
1822 s.call_type_id
1823 order by null
1824
1825INFO : (02-13 11:44:49) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0706
1826 (138.5ms) insert into buffer_call_type_assignments_1164884184 (
1827 calendar_schedule_id,
1828 period_offset,
1829 staff_id,
1830 call_type_id,
1831 weekday_assignments,
1832 weekend_assignments
1833 )
1834 select straight_join
1835 p.calendar_schedule_id,
1836 p.period_offset,
1837 sc.staff_id,
1838 sc.call_type_id,
1839 sum(if((dayofweek(sc.schedule_on) - 1) not in (0, 6), 1, 0)) as weekday_assignments,
1840 sum(if((dayofweek(sc.schedule_on) - 1) in (0, 6), 1, 0)) as weekend_assignments
1841 from buffer_periods_1164884184 as p
1842 inner join schedules as sc
1843 on sc.client_id = 319
1844 and sc.schedule_on between p.start_date and p.end_date
1845 and sc.staff_id is not null
1846 inner join call_types as ct
1847 on sc.call_type_id = ct.id
1848 and p.calendar_schedule_id = ct.calendar_schedule_id
1849 and ct.active = 1
1850 where p.should_be_evaluated = 1
1851 group by
1852 p.calendar_schedule_id,
1853 p.period_offset,
1854 sc.staff_id,
1855 sc.call_type_id
1856 order by null
1857
1858INFO : (02-13 11:44:50) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0707
1859INFO : (02-13 11:44:50) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query0708
1860 (269.9ms) insert into buffer_linkage_assigned_days_1164884184 (
1861 linkage_instance_id,
1862 period_offset,
1863 week_number,
1864 schedule_on,
1865 is_weekend
1866 )
1867 select
1868 sc.linkage_instance_id,
1869 sc.period_offset,
1870 sc.week_number,
1871 sc.schedule_on,
1872 if(dayofweek(sc.schedule_on) not in (7, 1), 0, 1) as is_weekend
1873 from
1874 buffer_schedules_1164884184 as sc
1875 inner join call_types as ct
1876 on sc.call_type_id = ct.id
1877 and ct.unassigned_day = 0
1878 where
1879 sc.linkage_instance_id >= 0
1880 and sc.staff_id is null
1881 group by
1882 sc.linkage_instance_id,
1883 sc.period_offset,
1884 sc.week_number,
1885 sc.schedule_on,
1886 is_weekend
1887 order by null
1888
1889INFO : (02-13 11:44:50) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query0709
1890 (302.2ms) /* AnnualDayOfWeekMixRule */
1891 insert into buffer_linkage_dow_targets_1164884184 (
1892 linkage_instance_id,
1893 calendar_schedule_id,
1894 period_offset,
1895 wday,
1896 targets
1897 )
1898 select
1899 s.linkage_instance_id,
1900 s.calendar_schedule_id,
1901 -1 as period_offset,
1902 (dayofweek(s.schedule_on) - 1) as wday,
1903 sum(ci.calls) as targets
1904 from
1905 buffer_schedules_1164884184 as s
1906 inner join buffer_counter_increments_1164884184 as ci
1907 on s.id = ci.schedule_id
1908 and ci.affects_cumulative_targets = 1
1909 where
1910 s.linkage_instance_id >= 0
1911 and s.staff_id is null
1912 group by
1913 s.linkage_instance_id,
1914 s.calendar_schedule_id,
1915 wday
1916 order by null
1917
1918INFO : (02-13 11:44:50) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL
1919 (145.2ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
1920 insert into buffer_assignment_limit_call_types_1164884184 (
1921 assignment_limit_id, call_type_id
1922 )
1923 select al.id as assignment_limit_id, ct.id as call_type_id
1924 from assignment_limits as al
1925 inner join assignment_limit_call_types as alct
1926 on al.id = alct.assignment_limit_id
1927 inner join call_types as ct
1928 on alct.call_type_id = ct.id
1929 and ct.active = 1
1930 where al.client_id = 319
1931 and al.active = 1
1932 union
1933 select al.id as assignment_limit_id, ct.id as call_type_id
1934 from assignment_limits as al
1935 inner join call_types as ct
1936 on al.client_id = ct.client_id
1937 and al.calendar_schedule_id = ct.calendar_schedule_id
1938 and ct.active = 1
1939 where al.client_id = 319
1940 and al.active = 1
1941 and al.all_current_schedule_assignments = 1
1942 union
1943 select al.id as assignment_limit_id, ct.id as call_type_id
1944 from assignment_limits as al
1945 inner join call_types as ct
1946 on al.client_id = ct.client_id
1947 and al.calendar_schedule_id != ct.calendar_schedule_id
1948 and ct.active = 1
1949 where al.client_id = 319
1950 and al.active = 1
1951 and al.all_other_schedule_assignments = 1
1952 order by null
1953
1954 (145.9ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
1955 insert into buffer_assignment_limit_staff_types_1164884184 (
1956 assignment_limit_id, staff_type_id
1957 )
1958 select al.id as assignment_limit_id, st.id as staff_type_id
1959 from assignment_limits as al
1960 inner join assignment_limit_staff_types as alst
1961 on al.id = alst.assignment_limit_id
1962 inner join staff_types as st
1963 on alst.staff_type_id = st.id
1964 and st.active = 1
1965 where al.client_id = 319
1966 and al.active = 1
1967 union
1968 select al.id as assignment_limit_id, st.id as staff_type_id
1969 from assignment_limits as al
1970 inner join staff_types as st
1971 on al.client_id = st.client_id
1972 and st.active = 1
1973 where al.client_id = 319
1974 and al.active = 1
1975 and al.all_staff_types = 1
1976 order by null
1977
1978INFO : (02-13 11:44:50) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
1979 (149.6ms) /* AssignmentLimitsPerPeriod */
1980 insert into buffer_assignment_limit_periods_1164884184 (
1981 assignment_limit_id, period_id, start_date, end_date
1982 )
1983 select
1984 al.id as assignment_limit_id,
1985 if(
1986 bs.blocks_type = 0,
1987 if(
1988 (month(b.start_date) - pt.begin_period) >= 0,
1989 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type + 1,
1990 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type + 1
1991 ),
1992 b.display_order
1993 ) as period_id,
1994 min(b.start_date) as start_date,
1995 max(b.end_date) as end_date
1996 from assignment_limits as al
1997 inner join calendar_schedules as cs
1998 on al.calendar_schedule_id = cs.id
1999 inner join block_schedules as bs
2000 on cs.block_schedule_id = bs.id
2001 inner join blocks as b
2002 on bs.id = b.block_schedule_id
2003 and b.end_date >= date_add(now(), interval -24 month)
2004 inner join population_timeframes as pt
2005 on pt.client_id = 319
2006 and al.timeframe_type = pt.timeframe_type
2007 where al.client_id = 319
2008 and al.active = 1
2009 and al.max_per_period_limit is not null
2010 group by al.id, period_id
2011 order by null
2012
2013 (151.9ms) /* AssignmentLimitsPerPeriod */
2014 insert into buffer_linkage_assignments_count_periods_1164884184 (
2015 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2016 )
2017 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date,
2018 count(distinct sc.id) as assignments_count
2019 from buffer_schedules_1164884184 as sc
2020 inner join buffer_assignment_limit_call_types_1164884184 as alct
2021 on sc.call_type_id = alct.call_type_id
2022 inner join assignment_limits as al
2023 on alct.assignment_limit_id = al.id
2024 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2025 and al.max_per_period_limit is not null
2026 inner join buffer_assignment_limit_periods_1164884184 as alp
2027 on alct.assignment_limit_id = alp.assignment_limit_id
2028 and sc.schedule_on between alp.start_date and alp.end_date
2029 where sc.linkage_instance_id >= 0
2030 and sc.staff_id is null
2031 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date
2032 order by null
2033
2034INFO : (02-13 11:44:51) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
2035 (159.3ms) /* AssignmentLimitsPerWeek */
2036 insert into buffer_linkage_assignments_count_weeks_1164884184 (
2037 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2038 )
2039 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2040 if(
2041 cl.start_of_week = 0,
2042 date_add(sc.schedule_on, interval (1 - dayofweek(sc.schedule_on)) day),
2043 date_add(sc.schedule_on, interval (0 - weekday(sc.schedule_on)) day)
2044 ) as start_date,
2045 if(
2046 cl.start_of_week = 0,
2047 date_add(sc.schedule_on, interval (7 - dayofweek(sc.schedule_on)) day),
2048 date_add(sc.schedule_on, interval (6 - weekday(sc.schedule_on)) day)
2049 ) as end_date,
2050 count(distinct sc.id) as assignments_count
2051 from buffer_schedules_1164884184 as sc
2052 inner join buffer_assignment_limit_call_types_1164884184 as alct
2053 on sc.call_type_id = alct.call_type_id
2054 inner join assignment_limits as al
2055 on alct.assignment_limit_id = al.id
2056 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2057 and al.max_weekly_limit is not null
2058 inner join clients as cl
2059 on cl.id = 319
2060 where sc.linkage_instance_id >= 0
2061 and sc.staff_id is null
2062 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2063 order by null
2064
2065INFO : (02-13 11:44:51) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
2066 (137.8ms) /* AssignmentLimitsRolling */
2067 insert into buffer_linkage_assignments_count_rolling_1164884184 (
2068 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2069 )
2070 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2071 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as start_date,
2072 date_add(sc.schedule_on, interval (al.max_rolling_period - ns.item_number) day) as end_date,
2073 count(distinct sc.id) as assignments_count
2074 from buffer_schedules_1164884184 as sc
2075 inner join buffer_assignment_limit_call_types_1164884184 as alct
2076 on sc.call_type_id = alct.call_type_id
2077 inner join assignment_limits as al
2078 on alct.assignment_limit_id = al.id
2079 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2080 and al.max_rolling_period is not null
2081 and al.max_rolling_limit is not null
2082 inner join numeric_sequence as ns
2083 on ns.item_number between 1 and al.max_rolling_period
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 11:44:51) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
2090 (136.4ms) /* AssignmentLimitsConsecutive */
2091 insert into buffer_linkage_assignments_count_consecutive_1164884184 (
2092 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date
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_consecutive_limit + 1) - ns.item_number) day) as end_date
2097 from buffer_schedules_1164884184 as sc
2098 inner join buffer_assignment_limit_call_types_1164884184 as alct
2099 on sc.call_type_id = alct.call_type_id
2100 inner join assignment_limits as al
2101 on alct.assignment_limit_id = al.id
2102 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2103 and al.max_consecutive_limit is not null
2104 inner join numeric_sequence as ns
2105 on ns.item_number between 1 and (al.max_consecutive_limit + 1)
2106 where sc.linkage_instance_id >= 0
2107 and sc.staff_id is null
2108 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2109 order by null
2110
2111INFO : (02-13 11:44:51) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query08
2112 (148.8ms) insert into buffer_staff_daily_counts_1164884184 (
2113 staff_id, schedule_on, linkage_instance_id, slots_count
2114 )
2115 select staff_id, schedule_on, null as linkage_instance_id,
2116 count(1) as slots_count
2117 from buffer_schedules_1164884184
2118 where staff_id is not null
2119 and call_type_id > 0 /* VC */
2120 group by staff_id, schedule_on
2121 order by null
2122
2123 (133.5ms) insert into buffer_staff_daily_counts_1164884184 (
2124 staff_id, schedule_on, linkage_instance_id, slots_count
2125 )
2126 select staff_id, schedule_on, linkage_instance_id,
2127 count(1) as slots_count
2128 from buffer_schedules_1164884184
2129 where staff_id is not null
2130 and call_type_id > 0 /* VC */
2131 group by staff_id, schedule_on, linkage_instance_id
2132 order by null
2133
2134INFO : (02-13 11:44:51) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query085
2135 (308.7ms) insert into buffer_staff_specializations_1164884184 (
2136 staff_id, specialization_id
2137 )
2138 select staff_id, specialization_id
2139 from view_staff_specializations
2140 where client_id = 319
2141 order by null
2142
2143 (169.4ms) insert into buffer_specialization_daily_counts_1164884184 (
2144 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2145 )
2146 select sp.id as specialization_id,
2147 null as staff_id,
2148 bs_1.schedule_on,
2149 count(distinct st_sp.staff_id) as staffs_scheduled,
2150 sp.max_scheduled,
2151 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
2152 from (
2153 select schedule_on, day_of_week_mask
2154 from buffer_schedules_1164884184
2155 where should_be_evaluated = 1
2156 group by schedule_on, day_of_week_mask
2157 order by null
2158 ) as bs_1
2159 inner join specializations as sp
2160 on sp.client_id = 319
2161 and sp.active = 1
2162 inner join buffer_schedules_1164884184 as bs_2
2163 on bs_1.schedule_on = bs_2.schedule_on
2164 and bs_2.call_type_id > 0 /* is not VC */
2165 inner join max_applicable_call_types as mact
2166 on sp.id = mact.specialization_id
2167 and bs_2.call_type_id = mact.call_type_id
2168 left join buffer_staff_specializations_1164884184 as st_sp
2169 on bs_2.staff_id = st_sp.staff_id
2170 and sp.id = st_sp.specialization_id
2171 group by sp.id, bs_1.schedule_on, sp.max_scheduled
2172 order by null
2173
2174 (156.7ms) insert into buffer_specialization_daily_counts_1164884184 (
2175 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2176 )
2177 select sp.id as specialization_id,
2178 st_sp.staff_id,
2179 bs_1.schedule_on,
2180 1 as staffs_scheduled,
2181 sp.max_scheduled,
2182 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
2183 from (
2184 select schedule_on, day_of_week_mask
2185 from buffer_schedules_1164884184
2186 where should_be_evaluated = 1
2187 group by schedule_on, day_of_week_mask
2188 order by null
2189 ) as bs_1
2190 inner join specializations as sp
2191 on sp.client_id = 319
2192 and sp.active = 1
2193 inner join buffer_schedules_1164884184 as bs_2
2194 on bs_1.schedule_on = bs_2.schedule_on
2195 and bs_2.call_type_id > 0 /* is not VC */
2196 inner join max_applicable_call_types as mact
2197 on sp.id = mact.specialization_id
2198 and bs_2.call_type_id = mact.call_type_id
2199 inner join buffer_staff_specializations_1164884184 as st_sp
2200 on bs_2.staff_id = st_sp.staff_id
2201 and sp.id = st_sp.specialization_id
2202 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
2203 order by null
2204
2205INFO : (02-13 11:44:52) --> fill: 34 - Scheduler::AutoPopulate::Queries::Query09
2206 (140.1ms) truncate table buffer_location_privileges_1164884184
2207
2208 (174.4ms) insert into buffer_location_privileges_1164884184 (
2209 staff_id,
2210 location_id,
2211 begin_date,
2212 end_date
2213 )
2214 select
2215 s.id as staff_id,
2216 l.location_id,
2217 '0000-00-00' as begin_date,
2218 '9999-12-31' as end_date
2219 from (
2220 select l.id as location_id
2221 from
2222 buffer_schedules_1164884184 as sc
2223 inner join call_types as ct
2224 on sc.call_type_id = ct.id
2225 and ct.active = 1
2226 inner join locations as l
2227 on ct.location_id = l.id
2228 and l.active = 1
2229 where sc.linkage_instance_id >= 0
2230 and sc.should_be_evaluated = 1
2231 group by l.id
2232 order by null
2233 ) as l
2234 inner join staffs as s
2235 on s.client_id = 319
2236 and s.active = 1
2237 and s.take_call = 1
2238 left join location_privileges as lp
2239 on s.id = lp.staff_id
2240 where lp.id is null
2241
2242 union
2243
2244 select
2245 lp.staff_id,
2246 lp.location_id,
2247 lp.begin_date,
2248 lp.end_date
2249 from (
2250 select l.id as location_id
2251 from
2252 buffer_schedules_1164884184 as sc
2253 inner join call_types as ct
2254 on sc.call_type_id = ct.id
2255 and ct.active = 1
2256 inner join locations as l
2257 on ct.location_id = l.id
2258 and l.active = 1
2259 where sc.linkage_instance_id >= 0
2260 and sc.should_be_evaluated = 1
2261 group by l.id
2262 order by null
2263 ) as l
2264 inner join location_privileges as lp
2265 on l.location_id = lp.location_id
2266 order by null;
2267
2268 (260.0ms) insert into buffer_static_availabilities_1164884184 (
2269 schedule_id,
2270 staff_id,
2271 staff_type_id,
2272 is_primary
2273 )
2274 select
2275 schedules.id as schedule_id,
2276 staffs.id as staff_id,
2277 assign_staff_types.staff_type_id,
2278 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
2279 from
2280 buffer_schedules_1164884184 as schedules
2281 /* AvailabilityRule */
2282 inner join call_types
2283 on schedules.call_type_id = call_types.id
2284 and call_types.active = 1
2285 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
2286 inner join staff_call_types
2287 on call_types.id = staff_call_types.call_type_id
2288 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
2289 inner join staffs
2290 on staff_call_types.staff_id = staffs.id
2291 and staffs.active = 1
2292 and staffs.take_call = 1
2293 and staffs.auto_populatable = 1
2294 inner join buffer_location_privileges_1164884184 as location_privileges
2295 on staffs.id = location_privileges.staff_id
2296 and call_types.location_id = location_privileges.location_id
2297 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
2298 inner join assign_staff_types
2299 on staffs.id = assign_staff_types.staff_id
2300 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
2301 left join eligible_staff_types
2302 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
2303 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
2304 left join staff_rotations
2305 on staffs.id = staff_rotations.staff_id
2306 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
2307 left join daily_rotations
2308 on staffs.id = daily_rotations.staff_id
2309 and schedules.schedule_on = daily_rotations.schedule_on
2310 left join rotations
2311 on rotations.id = ifnull(daily_rotations.rotation_id, staff_rotations.rotation_id)
2312 and rotations.active = 1
2313 left join rotation_call_types
2314 on rotations.id = rotation_call_types.rotation_id
2315 and staff_call_types.call_type_id = rotation_call_types.call_type_id
2316 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
2317 /* RequiredRotationsRule */
2318 left join required_rotations
2319 on staff_call_types.call_type_id = required_rotations.call_type_id
2320 left join staff_rotations as staff_rotations2
2321 on staffs.id = staff_rotations2.staff_id
2322 and required_rotations.rotation_id = staff_rotations2.rotation_id
2323 and schedules.schedule_on >= staff_rotations2.start_date
2324 /* NoCallRequestsHiddenHardRule */
2325 left join request_settings
2326 on assign_staff_types.staff_type_id = request_settings.staff_type_id
2327 left join requests
2328 on schedules.schedule_on = requests.schedule_on
2329 and staffs.id = requests.staff_id
2330 and requests.request_type = 0 /* type = no_call */
2331 and requests.status in (1, 3) /* status = approved or fulfilled */
2332 and call_types.is_no_call_requestable = 1
2333 left join request_call_types
2334 on requests.id = request_call_types.request_id
2335 and schedules.call_type_id = request_call_types.call_type_id
2336 where
2337 schedules.linkage_instance_id >= 0
2338 and schedules.should_be_evaluated = 1
2339 and (
2340 call_types.all_staff_types = 1
2341 or eligible_staff_types.id is not null
2342 )
2343 and (
2344 rotations.id is null
2345 or rotation_call_types.id is not null
2346 )
2347 and (
2348 requests.id is null
2349 or (
2350 requests.all_call_types = 1
2351 and request_settings.call_only_all_call_switch = 1 /* calls only */
2352 and call_types.assignment_type = 1 /* clinical */
2353 )
2354 or (
2355 requests.all_call_types != 1
2356 and request_call_types.id is null
2357 )
2358 )
2359 group by
2360 schedules.id,
2361 staffs.id,
2362 assign_staff_types.staff_type_id,
2363 is_primary
2364 having
2365 sum(if(required_rotations.id is not null and staff_rotations2.id is null, 1, 0)) = 0 /* RequiredRotationsRule */
2366 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
2367 order by null
2368
2369INFO : (02-13 11:44:53) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query092
2370 (135.4ms) /* RotationPostCallRule */
2371 /* Exclude people and slots when a post-call is not allowed on the first day of a rotation */
2372 delete sa
2373 from buffer_static_availabilities_1164884184 as sa
2374 inner join buffer_schedules_1164884184 as sc
2375 on sa.schedule_id = sc.id
2376 inner join call_types as ct
2377 on sc.call_type_id = ct.id
2378 and ct.assignment_type = 0 /* call */
2379 inner join staff_rotations as sr
2380 on sa.staff_id = sr.staff_id
2381 and date_add(sc.schedule_on, interval 1 day) = sr.start_date
2382 inner join rotations as r
2383 on sr.rotation_id = r.id
2384 and r.post_call_allowed = 0
2385
2386INFO : (02-13 11:44:53) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query093
2387INFO : (02-13 11:44:53) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query095
2388 (157.6ms) insert into buffer_negative_relations_1164884184 (
2389 schedule_id,
2390 affected_by_schedule_id,
2391 staff_id
2392 )
2393 select
2394 b_schedules.id as schedule_id,
2395 b_schedules_negative_link.id as affected_by_schedule_id,
2396 b_schedules_negative_link.staff_id
2397 from
2398 buffer_schedules_1164884184 as b_schedules
2399 /* NegativeLinkRule */
2400 inner join negative_links_influences
2401 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2402 and b_schedules.call_type_id = negative_links_influences.source_id
2403 and negative_links_influences.source_type = 'CallType'
2404 and negative_links_influences.target_type = 'CallType'
2405 inner join buffer_schedules_1164884184 as b_schedules_negative_link
2406 on date_add(
2407 b_schedules.schedule_on,
2408 interval negative_links_influences.offset day
2409 ) = b_schedules_negative_link.schedule_on
2410 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
2411 and b_schedules.id != b_schedules_negative_link.id
2412 where
2413 b_schedules.linkage_instance_id >= 0
2414 and b_schedules.should_be_evaluated = 1
2415 order by null
2416
2417 (202.2ms) insert into buffer_negative_relations_1164884184 (
2418 schedule_id,
2419 affected_by_schedule_id,
2420 staff_id
2421 )
2422 select
2423 b_schedules.id as schedule_id,
2424 b_schedules_negative_link.id as affected_by_schedule_id,
2425 b_schedules_negative_link.staff_id
2426 from
2427 buffer_schedules_1164884184 as b_schedules
2428 /* NegativeLinkRule */
2429 inner join negative_links_influences
2430 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2431 and b_schedules.call_type_id = negative_links_influences.source_id
2432 and negative_links_influences.source_type = 'CallType'
2433 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
2434 inner join buffer_schedules_1164884184 as b_schedules_negative_link
2435 on date_add(
2436 b_schedules.schedule_on,
2437 interval negative_links_influences.offset day
2438 ) = b_schedules_negative_link.schedule_on
2439 and (-1 * ifnull(negative_links_influences.target_id, 0)) = b_schedules_negative_link.call_type_id
2440 and b_schedules.id != b_schedules_negative_link.id
2441 where
2442 b_schedules.linkage_instance_id >= 0
2443 and b_schedules.should_be_evaluated = 1
2444 order by null
2445
2446 (160.5ms) delete from buffer_schedules_1164884184
2447 where call_type_id <= 0 /* vacations and non-clinicals */
2448
2449INFO : (02-13 11:44:53) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query097
2450 (165.3ms) insert into buffer_staff_assigned_days_1164884184 (
2451 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
2452 )
2453 select staffs.staff_id,
2454 if(days.within_period = 1, days.period_offset, null) as period_offset,
2455 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
2456 days.schedule_on,
2457 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
2458 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
2459 from (
2460 select distinct staff_id
2461 from buffer_static_availabilities_1164884184
2462 order by null
2463 ) as staffs
2464 inner join (
2465 select p.period_offset,
2466 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,
2467 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
2468 from buffer_periods_1164884184 as p
2469 inner join (
2470 select 0 as digit union select 1 union select 2 union select 3 union select 4
2471 union select 5 union select 6 union select 7 union select 8 union select 9
2472 ) as digits_1
2473 inner join (
2474 select 0 as digit union select 1 union select 2 union select 3 union select 4
2475 union select 5 union select 6 union select 7 union select 8 union select 9
2476 ) as digits_2
2477 inner join (
2478 select 0 as digit union select 1 union select 2 union select 3 union select 4
2479 union select 5 union select 6 union select 7 union select 8 union select 9
2480 ) as digits_3
2481 where p.calendar_schedule_id = 1381
2482 and p.should_be_evaluated = 1
2483 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
2484 order by null
2485 ) as days
2486 inner join (
2487 select min(start_date) as start_date, max(end_date) as end_date
2488 from buffer_periods_1164884184
2489 where calendar_schedule_id = 1381
2490 and should_be_evaluated = 1
2491 order by null
2492 ) as limits
2493 inner join clients
2494 on clients.id = 319
2495 left join (
2496 select sc.staff_id, sc.schedule_on
2497 from buffer_periods_1164884184 as p
2498 inner join schedules as sc
2499 on sc.client_id = 319
2500 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2501 inner join call_types as ct
2502 on sc.call_type_id = ct.id
2503 and ct.unassigned_day = 0
2504 where p.calendar_schedule_id = 1381
2505 and p.should_be_evaluated = 1
2506 group by sc.staff_id, sc.schedule_on
2507 order by null
2508 ) as schedules
2509 on staffs.staff_id = schedules.staff_id
2510 and days.schedule_on = schedules.schedule_on
2511 left join (
2512 select v.staff_id, v.schedule_on
2513 from buffer_periods_1164884184 as p
2514 inner join vacations as v
2515 on v.client_id = 319
2516 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2517 and v.status IN (3)
2518 left join non_clinical_types as nct
2519 on v.non_clinical_type_id = nct.id
2520 and nct.active = 1
2521 and nct.unassigned_day = 0
2522 where p.calendar_schedule_id = 1381
2523 and p.should_be_evaluated = 1
2524 and (
2525 v.vacation_type = 0
2526 or nct.id is not null
2527 )
2528 group by v.staff_id, v.schedule_on
2529 order by null
2530 ) as vacations
2531 on staffs.staff_id = vacations.staff_id
2532 and days.schedule_on = vacations.schedule_on
2533 where days.within_period = 1
2534 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
2535 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
2536 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
2537 )
2538 order by null
2539
2540INFO : (02-13 11:44:53) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query098
2541 (142.7ms) insert into buffer_combined_availabilities_1164884184 (
2542 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
2543 )
2544 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
2545 sc.linkage_instance_id, 0 as has_violations
2546 from buffer_schedules_1164884184 as sc
2547 /* Static availability */
2548 inner join buffer_static_availabilities_1164884184 as sa
2549 on sc.id = sa.schedule_id
2550 inner join buffer_linkages_1164884184 as l
2551 on sc.linkage_instance_id = l.linkage_instance_id
2552 where sc.should_be_evaluated = 1
2553 and l.has_conflicts = 0
2554
2555 (185.2ms) insert into buffer_schedule_linkages_1164884184 (
2556 staff_id, linkage_instance_id, slots_count
2557 )
2558 select staff_id, linkage_instance_id, count(1) as slots_count
2559 from buffer_schedules_1164884184 use index (ix_buffer_schedules_composite_2)
2560 where staff_id is not null
2561 and linkage_instance_id >= 0
2562 group by staff_id, linkage_instance_id
2563 order by null
2564
2565INFO : (02-13 11:44:54) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
2566 (142.0ms) /* AssignmentLimitsPerPeriod */
2567 insert into buffer_staff_assignments_count_periods_1164884184 (
2568 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2569 )
2570 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2571 count(distinct sc.id) as assignments_count
2572 from (
2573 select linkage_instance_id, assignment_limit_id, start_date, end_date
2574 from buffer_linkage_assignments_count_periods_1164884184
2575 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2576 order by null
2577 ) as lac
2578 inner join (
2579 select linkage_instance_id, staff_id
2580 from buffer_combined_availabilities_1164884184
2581 group by linkage_instance_id, staff_id
2582 order by null
2583 ) as ca
2584 on lac.linkage_instance_id = ca.linkage_instance_id
2585 inner join assignment_limits as al
2586 on lac.assignment_limit_id = al.id
2587 inner join schedules as sc
2588 on ca.staff_id = sc.staff_id
2589 and sc.schedule_on between lac.start_date and lac.end_date
2590 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2591 inner join buffer_assignment_limit_call_types_1164884184 as alct
2592 on lac.assignment_limit_id = alct.assignment_limit_id
2593 and sc.call_type_id = alct.call_type_id
2594 inner join assign_staff_types as ast
2595 on sc.staff_id = ast.staff_id
2596 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2597 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2598 on lac.assignment_limit_id = alst.assignment_limit_id
2599 and ast.staff_type_id = alst.staff_type_id
2600 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2601 order by null
2602
2603INFO : (02-13 11:44:54) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
2604 (139.2ms) /* AssignmentLimitsPerWeek */
2605 insert into buffer_staff_assignments_count_weeks_1164884184 (
2606 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2607 )
2608 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2609 count(distinct sc.id) as assignments_count
2610 from (
2611 select linkage_instance_id, assignment_limit_id, start_date, end_date
2612 from buffer_linkage_assignments_count_weeks_1164884184
2613 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2614 order by null
2615 ) as lac
2616 inner join (
2617 select linkage_instance_id, staff_id
2618 from buffer_combined_availabilities_1164884184
2619 group by linkage_instance_id, staff_id
2620 order by null
2621 ) as ca
2622 on lac.linkage_instance_id = ca.linkage_instance_id
2623 inner join assignment_limits as al
2624 on lac.assignment_limit_id = al.id
2625 inner join schedules as sc
2626 on ca.staff_id = sc.staff_id
2627 and sc.schedule_on between lac.start_date and lac.end_date
2628 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2629 inner join buffer_assignment_limit_call_types_1164884184 as alct
2630 on lac.assignment_limit_id = alct.assignment_limit_id
2631 and sc.call_type_id = alct.call_type_id
2632 inner join assign_staff_types as ast
2633 on sc.staff_id = ast.staff_id
2634 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2635 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2636 on lac.assignment_limit_id = alst.assignment_limit_id
2637 and ast.staff_type_id = alst.staff_type_id
2638 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2639 order by null
2640
2641INFO : (02-13 11:44:54) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
2642 (141.8ms) /* AssignmentLimitsRolling */
2643 insert into buffer_staff_assignments_count_rolling_1164884184 (
2644 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2645 )
2646 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2647 count(distinct sc.id) as assignments_count
2648 from (
2649 select linkage_instance_id, assignment_limit_id, start_date, end_date
2650 from buffer_linkage_assignments_count_rolling_1164884184
2651 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2652 order by null
2653 ) as lac
2654 inner join (
2655 select linkage_instance_id, staff_id
2656 from buffer_combined_availabilities_1164884184
2657 group by linkage_instance_id, staff_id
2658 order by null
2659 ) as ca
2660 on lac.linkage_instance_id = ca.linkage_instance_id
2661 inner join assignment_limits as al
2662 on lac.assignment_limit_id = al.id
2663 inner join schedules as sc
2664 on ca.staff_id = sc.staff_id
2665 and sc.schedule_on between lac.start_date and lac.end_date
2666 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2667 inner join buffer_assignment_limit_call_types_1164884184 as alct
2668 on lac.assignment_limit_id = alct.assignment_limit_id
2669 and sc.call_type_id = alct.call_type_id
2670 inner join assign_staff_types as ast
2671 on sc.staff_id = ast.staff_id
2672 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2673 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2674 on lac.assignment_limit_id = alst.assignment_limit_id
2675 and ast.staff_type_id = alst.staff_type_id
2676 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2677 order by null
2678
2679INFO : (02-13 11:44:54) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
2680 (140.9ms) /* AssignmentLimitsConsecutive */
2681 insert into buffer_staff_assignments_count_consecutive_1164884184 (
2682 assignment_limit_id, staff_id, schedule_on, start_date, end_date
2683 )
2684 select straight_join lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2685 from (
2686 select linkage_instance_id, assignment_limit_id, start_date, end_date
2687 from buffer_linkage_assignments_count_consecutive_1164884184
2688 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2689 order by null
2690 ) as lac
2691 inner join (
2692 select linkage_instance_id, staff_id
2693 from buffer_combined_availabilities_1164884184
2694 group by linkage_instance_id, staff_id
2695 order by null
2696 ) as ca
2697 on lac.linkage_instance_id = ca.linkage_instance_id
2698 inner join assignment_limits as al
2699 on lac.assignment_limit_id = al.id
2700 inner join schedules as sc
2701 on ca.staff_id = sc.staff_id
2702 and sc.schedule_on between lac.start_date and lac.end_date
2703 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2704 inner join buffer_assignment_limit_call_types_1164884184 as alct
2705 on lac.assignment_limit_id = alct.assignment_limit_id
2706 and sc.call_type_id = alct.call_type_id
2707 inner join assign_staff_types as ast
2708 on sc.staff_id = ast.staff_id
2709 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2710 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2711 on lac.assignment_limit_id = alst.assignment_limit_id
2712 and ast.staff_type_id = alst.staff_type_id
2713 group by lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2714 order by null
2715
2716INFO : (02-13 11:44:54) --> fill: 44 - Scheduler::AutoPopulate::Queries::Query09_09_RP
2717INFO : (02-13 11:44:54) --> fill: 45 - Scheduler::AutoPopulate::Queries::Query10
2718 RuleSet Load (133.5ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1511 LIMIT 1
2719 Client Load (305.8ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
2720 (166.0ms) /* SpecializationOverlapRule */
2721 delete ca
2722 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
2723 inner join buffer_schedules_1164884184 as sc
2724 on ca.schedule_id = sc.id
2725 inner join buffer_staff_specializations_1164884184 as ssp
2726 on ca.staff_id = ssp.staff_id
2727 inner join max_applicable_call_types as mact
2728 on ssp.specialization_id = mact.specialization_id
2729 and sc.call_type_id = mact.call_type_id
2730 inner join buffer_specialization_daily_counts_1164884184 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2731 on ssp.specialization_id = sdc.specialization_id
2732 and sc.schedule_on = sdc.schedule_on
2733 and sdc.staff_id is null
2734 left join buffer_specialization_daily_counts_1164884184 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2735 on ssp.specialization_id = sdc_2.specialization_id
2736 and sc.schedule_on = sdc_2.schedule_on
2737 and ca.staff_id = sdc_2.staff_id
2738 where sdc.should_be_evaluated = 1
2739 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2740
2741 (143.7ms) delete ca
2742 from buffer_combined_availabilities_1164884184 as ca
2743 inner join (
2744 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2745 sum(lac.assignments_count) as assignments_count
2746 from (
2747 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2748 from buffer_combined_availabilities_1164884184 as ca
2749 inner join buffer_schedules_1164884184 as sc
2750 on ca.schedule_id = sc.id
2751 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2752 order by null
2753 ) as ca_sc
2754 inner join buffer_linkage_assignments_count_weeks_1164884184 as lac
2755 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2756 and ca_sc.schedule_on = lac.schedule_on
2757 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2758 on lac.assignment_limit_id = alst.assignment_limit_id
2759 and ca_sc.staff_type_id = alst.staff_type_id
2760 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2761 order by null
2762 ) as lac
2763 on ca.linkage_instance_id = lac.linkage_instance_id
2764 and ca.staff_id = lac.staff_id
2765 inner join assignment_limits as al
2766 on lac.assignment_limit_id = al.id
2767 left join buffer_staff_assignments_count_weeks_1164884184 as sac
2768 on lac.assignment_limit_id = sac.assignment_limit_id
2769 and lac.staff_id = sac.staff_id
2770 and lac.start_date = sac.start_date
2771 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
2772
2773 (163.7ms) delete ca
2774 from buffer_combined_availabilities_1164884184 as ca
2775 inner join (
2776 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2777 sum(lac.assignments_count) as assignments_count
2778 from (
2779 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2780 from buffer_combined_availabilities_1164884184 as ca
2781 inner join buffer_schedules_1164884184 as sc
2782 on ca.schedule_id = sc.id
2783 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2784 order by null
2785 ) as ca_sc
2786 inner join buffer_linkage_assignments_count_periods_1164884184 as lac
2787 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2788 and ca_sc.schedule_on = lac.schedule_on
2789 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2790 on lac.assignment_limit_id = alst.assignment_limit_id
2791 and ca_sc.staff_type_id = alst.staff_type_id
2792 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2793 order by null
2794 ) as lac
2795 on ca.linkage_instance_id = lac.linkage_instance_id
2796 and ca.staff_id = lac.staff_id
2797 inner join assignment_limits as al
2798 on lac.assignment_limit_id = al.id
2799 left join buffer_staff_assignments_count_periods_1164884184 as sac
2800 on lac.assignment_limit_id = sac.assignment_limit_id
2801 and lac.staff_id = sac.staff_id
2802 and lac.start_date = sac.start_date
2803 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
2804
2805 (151.5ms) delete ca
2806 from buffer_combined_availabilities_1164884184 as ca
2807 inner join (
2808 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2809 sum(lac.assignments_count) as assignments_count
2810 from (
2811 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2812 from buffer_combined_availabilities_1164884184 as ca
2813 inner join buffer_schedules_1164884184 as sc
2814 on ca.schedule_id = sc.id
2815 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2816 order by null
2817 ) as ca_sc
2818 inner join buffer_linkage_assignments_count_rolling_1164884184 as lac
2819 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2820 and ca_sc.schedule_on = lac.schedule_on
2821 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2822 on lac.assignment_limit_id = alst.assignment_limit_id
2823 and ca_sc.staff_type_id = alst.staff_type_id
2824 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2825 order by null
2826 ) as lac
2827 on ca.linkage_instance_id = lac.linkage_instance_id
2828 and ca.staff_id = lac.staff_id
2829 inner join assignment_limits as al
2830 on lac.assignment_limit_id = al.id
2831 left join buffer_staff_assignments_count_rolling_1164884184 as sac
2832 on lac.assignment_limit_id = sac.assignment_limit_id
2833 and lac.staff_id = sac.staff_id
2834 and lac.start_date = sac.start_date
2835 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
2836
2837 (799.1ms) delete ca
2838 from buffer_combined_availabilities_1164884184 as ca
2839 inner join (
2840 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2841 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
2842 from (
2843 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2844 from buffer_combined_availabilities_1164884184 as ca
2845 inner join buffer_schedules_1164884184 as sc
2846 on ca.schedule_id = sc.id
2847 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2848 order by null
2849 ) as ca_sc
2850 inner join buffer_linkage_assignments_count_consecutive_1164884184 as lac
2851 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2852 and ca_sc.schedule_on = lac.schedule_on
2853 inner join buffer_assignment_limit_staff_types_1164884184 as alst
2854 on lac.assignment_limit_id = alst.assignment_limit_id
2855 and ca_sc.staff_type_id = alst.staff_type_id
2856 left join buffer_staff_assignments_count_consecutive_1164884184 as sac
2857 on lac.assignment_limit_id = sac.assignment_limit_id
2858 and ca_sc.staff_id = sac.staff_id
2859 and lac.schedule_on = sac.schedule_on
2860 and lac.start_date = sac.start_date
2861 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2862 order by null
2863 ) as lac
2864 on ca.linkage_instance_id = lac.linkage_instance_id
2865 and ca.staff_id = lac.staff_id
2866 inner join assignment_limits as al
2867 on lac.assignment_limit_id = al.id
2868 left join (
2869 select assignment_limit_id, staff_id, start_date,
2870 count(schedule_on) as assignments_count
2871 from buffer_staff_assignments_count_consecutive_1164884184
2872 group by assignment_limit_id, staff_id, start_date
2873 ) as sac
2874 on lac.assignment_limit_id = sac.assignment_limit_id
2875 and lac.staff_id = sac.staff_id
2876 and lac.start_date = sac.start_date
2877 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
2878
2879 (151.3ms) /* AnnualDayOfWeekMixRule */
2880 delete ca
2881 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
2882 inner join buffer_schedules_1164884184 as sc
2883 on ca.schedule_id = sc.id
2884 inner join buffer_linkage_dow_targets_1164884184 as ldowt use index (ix_buffer_linkage_dow_targets_composite_1)
2885 on sc.linkage_instance_id = ldowt.linkage_instance_id
2886 and sc.calendar_schedule_id = ldowt.calendar_schedule_id
2887 and ldowt.period_offset = -1
2888 and (dayofweek(sc.schedule_on) - 1) = ldowt.wday
2889 inner join buffer_cumulative_dow_targets_1164884184 as dowt use index (ix_buffer_cumulative_dow_targets_composite_1)
2890 on ca.staff_id = dowt.staff_id
2891 and sc.calendar_schedule_id = dowt.calendar_schedule_id
2892 and (dayofweek(sc.schedule_on) - 1) = dowt.wday
2893 where (sc.day_of_week_mask & 127 /* AnnualDayOfWeekMixRule "days of week selection" */) > 0
2894 and ((round(dowt.adjusted_target, 2)) - dowt.scheduled - ldowt.targets) < 0
2895
2896 (150.3ms) /* MaxAssignmentsRule */
2897 delete ca
2898 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
2899 inner join buffer_schedules_1164884184 as sc
2900 on ca.schedule_id = sc.id
2901 inner join buffer_linkage_call_type_assignments_1164884184 as lcta use index (ix_buffer_linkage_call_type_assignments_composite_1)
2902 on sc.linkage_instance_id = lcta.linkage_instance_id
2903 and sc.calendar_schedule_id = lcta.calendar_schedule_id
2904 and sc.period_offset = lcta.period_offset
2905 and sc.call_type_id = lcta.call_type_id
2906 left join buffer_call_type_assignments_1164884184 as cta use index (ix_buffer_call_type_assignments_composite_1)
2907 on ca.staff_id = cta.staff_id
2908 and sc.calendar_schedule_id = cta.calendar_schedule_id
2909 and sc.period_offset = cta.period_offset
2910 and sc.call_type_id = cta.call_type_id
2911 where
2912 /* weekday checkbox */
2913 (
2914 lcta.max_weekday_assignments is not null
2915 and (ifnull(cta.weekday_assignments, 0) + lcta.weekday_assignments) > lcta.max_weekday_assignments
2916 )
2917 /* weekend checkbox */
2918 or (
2919 lcta.max_weekend_assignments is not null
2920 and (ifnull(cta.weekend_assignments, 0) + lcta.weekend_assignments) > lcta.max_weekend_assignments
2921 )
2922
2923 (144.7ms) /* NegativeLinkRule */
2924 delete ca
2925 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
2926 inner join buffer_negative_relations_1164884184 as nr use index (ix_buffer_negative_relations_composite_1)
2927 on ca.schedule_id = nr.schedule_id
2928 and ca.staff_id = nr.staff_id
2929
2930 (136.2ms) /* BrokenLinkageRule */
2931 truncate table buffer_combined_availability_linkages_1164884184
2932
2933 (198.3ms) insert into buffer_combined_availability_linkages_1164884184 (
2934 staff_id, linkage_instance_id, slots_count, is_primary, has_violations
2935 )
2936 select staff_id, linkage_instance_id,
2937 count(1) as slots_count,
2938 min(is_primary) as is_primary,
2939 max(has_violations) as has_violations
2940 from buffer_combined_availabilities_1164884184 use index (ix_buffer_combined_availabilities_composite_2)
2941 group by staff_id, linkage_instance_id
2942 order by null
2943
2944 (156.6ms) delete ca
2945 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
2946 inner join buffer_schedules_1164884184 as sc
2947 on ca.schedule_id = sc.id
2948 inner join buffer_linkages_1164884184 as l
2949 on sc.linkage_instance_id = l.linkage_instance_id
2950 inner join buffer_combined_availability_linkages_1164884184 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2951 on ca.staff_id = ls_1.staff_id
2952 and sc.linkage_instance_id = ls_1.linkage_instance_id
2953 left join buffer_schedule_linkages_1164884184 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2954 on ca.staff_id = ls_2.staff_id
2955 and sc.linkage_instance_id = ls_2.linkage_instance_id
2956 where (ls_1.slots_count + ifnull(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */
2957
2958INFO : (02-13 11:44:57) --> fill: 46 - Scheduler::AutoPopulate::Queries::QueryScore02
2959INFO : (02-13 11:44:57) --> fill: 47 - Scheduler::AutoPopulate::Queries::QueryScore03
2960INFO : (02-13 11:44:57) --> fill: 48 - Scheduler::AutoPopulate::Queries::QueryScore04
2961INFO : (02-13 11:44:57) --> fill: 49 - Scheduler::AutoPopulate::Queries::Query11
2962 (168.6ms) update buffer_schedules_1164884184 as sc
2963 left join buffer_combined_availabilities_1164884184 as ca
2964 on sc.id = ca.schedule_id
2965 set sc.should_be_evaluated = 0,
2966 sc.should_be_populated = 0
2967 where sc.should_be_evaluated = 1
2968 and ca.id is null
2969
2970INFO : (02-13 11:44:57) --> fill: 50 - Scheduler::AutoPopulate::Queries::DropTables
2971INFO : (02-13 11:44:57) Setting up lists...
2972 (165.7ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
2973 from buffer_schedules_1164884184 as b_schedules
2974 left join schedules
2975 on schedules.client_id = 319
2976 and schedules.call_type_id = b_schedules.call_type_id
2977 and schedules.schedule_on = b_schedules.schedule_on
2978 where b_schedules.linkage_instance_id != -1
2979 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
2980
2981INFO : (02-13 11:44:58) Begin to populate. Pre-set: 0. Unscheduled: 93.
2982INFO : (02-13 11:44:58) plan unit: [id:201809] [2018-03-04 - OB]
2983INFO : (02-13 11:44:58) plan unit: [id:201810] [2018-03-11 - OB]
2984INFO : (02-13 11:44:58) plan unit: [id:201811] [2018-03-18 - OB]
2985INFO : (02-13 11:44:58) plan unit: [id:201812] [2018-03-25 - OB]
2986INFO : (02-13 11:44:58) plan unit: [id:1201809] [2018-03-04 - OR]
2987INFO : (02-13 11:44:58) plan unit: [id:1201810] [2018-03-11 - OR]
2988INFO : (02-13 11:44:58) plan unit: [id:1201811] [2018-03-18 - OR]
2989INFO : (02-13 11:44:58) plan unit: [id:1201812] [2018-03-25 - OR]
2990INFO : (02-13 11:44:58) plan unit: [id:2201809] [2018-03-04 - BU]
2991INFO : (02-13 11:44:58) plan unit: [id:2201810] [2018-03-11 - BU]
2992INFO : (02-13 11:44:58) plan unit: [id:2201811] [2018-03-18 - BU]
2993INFO : (02-13 11:44:58) plan unit: [id:2201812] [2018-03-25 - BU]
2994INFO : (02-13 11:44:58) plan unit: [id:6201809] [2018-03-05 - OB]
2995INFO : (02-13 11:44:58) plan unit: [id:6201810] [2018-03-12 - OB]
2996INFO : (02-13 11:44:58) plan unit: [id:6201811] [2018-03-19 - OB]
2997INFO : (02-13 11:44:58) plan unit: [id:6201812] [2018-03-26 - OB]
2998INFO : (02-13 11:44:58) plan unit: [id:7201809] [2018-03-05 - OR]
2999INFO : (02-13 11:44:58) plan unit: [id:7201810] [2018-03-12 - OR]
3000INFO : (02-13 11:44:58) plan unit: [id:7201811] [2018-03-19 - OR]
3001INFO : (02-13 11:44:58) plan unit: [id:7201812] [2018-03-26 - OR]
3002INFO : (02-13 11:44:58) plan unit: [id:8201809] [2018-03-05 - BU]
3003INFO : (02-13 11:44:58) plan unit: [id:8201810] [2018-03-12 - BU]
3004INFO : (02-13 11:44:58) plan unit: [id:8201811] [2018-03-19 - BU]
3005INFO : (02-13 11:44:58) plan unit: [id:8201812] [2018-03-26 - BU]
3006INFO : (02-13 11:44:58) plan unit: [id:12201809] [2018-03-06 - OB]
3007INFO : (02-13 11:44:58) plan unit: [id:12201810] [2018-03-13 - OB]
3008INFO : (02-13 11:44:58) plan unit: [id:12201811] [2018-03-20 - OB]
3009INFO : (02-13 11:44:58) plan unit: [id:12201812] [2018-03-27 - OB]
3010INFO : (02-13 11:44:58) plan unit: [id:13201809] [2018-03-06 - OR]
3011INFO : (02-13 11:44:58) plan unit: [id:13201810] [2018-03-13 - OR]
3012INFO : (02-13 11:44:58) plan unit: [id:13201811] [2018-03-20 - OR]
3013INFO : (02-13 11:44:58) plan unit: [id:13201812] [2018-03-27 - OR]
3014INFO : (02-13 11:44:58) plan unit: [id:14201809] [2018-03-06 - BU]
3015INFO : (02-13 11:44:58) plan unit: [id:14201810] [2018-03-13 - BU]
3016INFO : (02-13 11:44:58) plan unit: [id:14201811] [2018-03-20 - BU]
3017INFO : (02-13 11:44:58) plan unit: [id:14201812] [2018-03-27 - BU]
3018INFO : (02-13 11:44:58) plan unit: [id:18201809] [2018-03-07 - OB]
3019INFO : (02-13 11:44:58) plan unit: [id:18201810] [2018-03-14 - OB]
3020INFO : (02-13 11:44:58) plan unit: [id:18201811] [2018-03-21 - OB]
3021INFO : (02-13 11:44:58) plan unit: [id:18201812] [2018-03-28 - OB]
3022INFO : (02-13 11:44:58) plan unit: [id:19201809] [2018-03-07 - OR]
3023INFO : (02-13 11:44:58) plan unit: [id:19201810] [2018-03-14 - OR]
3024INFO : (02-13 11:44:58) plan unit: [id:19201811] [2018-03-21 - OR]
3025INFO : (02-13 11:44:58) plan unit: [id:19201812] [2018-03-28 - OR]
3026INFO : (02-13 11:44:58) plan unit: [id:20201809] [2018-03-07 - BU]
3027INFO : (02-13 11:44:58) plan unit: [id:20201810] [2018-03-14 - BU]
3028INFO : (02-13 11:44:58) plan unit: [id:20201811] [2018-03-21 - BU]
3029INFO : (02-13 11:44:58) plan unit: [id:20201812] [2018-03-28 - BU]
3030INFO : (02-13 11:44:58) plan unit: [id:24201808] [2018-03-01 - OB]
3031INFO : (02-13 11:44:58) plan unit: [id:24201809] [2018-03-08 - OB]
3032INFO : (02-13 11:44:58) plan unit: [id:24201810] [2018-03-15 - OB]
3033INFO : (02-13 11:44:58) plan unit: [id:24201811] [2018-03-22 - OB]
3034INFO : (02-13 11:44:58) plan unit: [id:24201812] [2018-03-29 - OB]
3035INFO : (02-13 11:44:58) plan unit: [id:25201808] [2018-03-01 - OR]
3036INFO : (02-13 11:44:58) plan unit: [id:25201809] [2018-03-08 - OR]
3037INFO : (02-13 11:44:58) plan unit: [id:25201810] [2018-03-15 - OR]
3038INFO : (02-13 11:44:58) plan unit: [id:25201811] [2018-03-22 - OR]
3039INFO : (02-13 11:44:58) plan unit: [id:25201812] [2018-03-29 - OR]
3040INFO : (02-13 11:44:58) plan unit: [id:26201808] [2018-03-01 - BU]
3041INFO : (02-13 11:44:58) plan unit: [id:26201809] [2018-03-08 - BU]
3042INFO : (02-13 11:44:58) plan unit: [id:26201810] [2018-03-15 - BU]
3043INFO : (02-13 11:44:58) plan unit: [id:26201811] [2018-03-22 - BU]
3044INFO : (02-13 11:44:58) plan unit: [id:26201812] [2018-03-29 - BU]
3045INFO : (02-13 11:44:58) plan unit: [id:30201808] [2018-03-02 - OB]
3046INFO : (02-13 11:44:58) plan unit: [id:30201809] [2018-03-09 - OB]
3047INFO : (02-13 11:44:58) plan unit: [id:30201810] [2018-03-16 - OB]
3048INFO : (02-13 11:44:58) plan unit: [id:30201811] [2018-03-23 - OB]
3049INFO : (02-13 11:44:58) plan unit: [id:30201812] [2018-03-30 - OB]
3050INFO : (02-13 11:44:58) plan unit: [id:31201808] [2018-03-02 - OR]
3051INFO : (02-13 11:44:58) plan unit: [id:31201809] [2018-03-09 - OR]
3052INFO : (02-13 11:44:58) plan unit: [id:31201810] [2018-03-16 - OR]
3053INFO : (02-13 11:44:58) plan unit: [id:31201811] [2018-03-23 - OR]
3054INFO : (02-13 11:44:58) plan unit: [id:31201812] [2018-03-30 - OR]
3055INFO : (02-13 11:44:58) plan unit: [id:32201808] [2018-03-02 - BU]
3056INFO : (02-13 11:44:58) plan unit: [id:32201809] [2018-03-09 - BU]
3057INFO : (02-13 11:44:58) plan unit: [id:32201810] [2018-03-16 - BU]
3058INFO : (02-13 11:44:58) plan unit: [id:32201811] [2018-03-23 - BU]
3059INFO : (02-13 11:44:58) plan unit: [id:32201812] [2018-03-30 - BU]
3060INFO : (02-13 11:44:58) plan unit: [id:36201808] [2018-03-03 - OB]
3061INFO : (02-13 11:44:58) plan unit: [id:36201809] [2018-03-10 - OB]
3062INFO : (02-13 11:44:58) plan unit: [id:36201810] [2018-03-17 - OB]
3063INFO : (02-13 11:44:58) plan unit: [id:36201811] [2018-03-24 - OB]
3064INFO : (02-13 11:44:58) plan unit: [id:36201812] [2018-03-31 - OB]
3065INFO : (02-13 11:44:58) plan unit: [id:37201808] [2018-03-03 - OR]
3066INFO : (02-13 11:44:58) plan unit: [id:37201809] [2018-03-10 - OR]
3067INFO : (02-13 11:44:58) plan unit: [id:37201810] [2018-03-17 - OR]
3068INFO : (02-13 11:44:58) plan unit: [id:37201811] [2018-03-24 - OR]
3069INFO : (02-13 11:44:58) plan unit: [id:37201812] [2018-03-31 - OR]
3070INFO : (02-13 11:44:58) plan unit: [id:38201808] [2018-03-03 - BU]
3071INFO : (02-13 11:44:58) plan unit: [id:38201809] [2018-03-10 - BU]
3072INFO : (02-13 11:44:58) plan unit: [id:38201810] [2018-03-17 - BU]
3073INFO : (02-13 11:44:58) plan unit: [id:38201811] [2018-03-24 - BU]
3074INFO : (02-13 11:44:58) plan unit: [id:38201812] [2018-03-31 - BU]
3075 NegativeLink Load (137.8ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
3076 (167.1ms) /* SpecializationOverlapRule */
3077 delete ca
3078 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
3079 inner join buffer_schedules_1164884184 as sc
3080 on ca.schedule_id = sc.id
3081 inner join buffer_staff_specializations_1164884184 as ssp
3082 on ca.staff_id = ssp.staff_id
3083 inner join max_applicable_call_types as mact
3084 on ssp.specialization_id = mact.specialization_id
3085 and sc.call_type_id = mact.call_type_id
3086 inner join buffer_specialization_daily_counts_1164884184 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3087 on ssp.specialization_id = sdc.specialization_id
3088 and sc.schedule_on = sdc.schedule_on
3089 and sdc.staff_id is null
3090 left join buffer_specialization_daily_counts_1164884184 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3091 on ssp.specialization_id = sdc_2.specialization_id
3092 and sc.schedule_on = sdc_2.schedule_on
3093 and ca.staff_id = sdc_2.staff_id
3094 where sdc.should_be_evaluated = 1
3095 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3096
3097 (147.8ms) delete ca
3098 from buffer_combined_availabilities_1164884184 as ca
3099 inner join (
3100 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3101 sum(lac.assignments_count) as assignments_count
3102 from (
3103 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3104 from buffer_combined_availabilities_1164884184 as ca
3105 inner join buffer_schedules_1164884184 as sc
3106 on ca.schedule_id = sc.id
3107 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3108 order by null
3109 ) as ca_sc
3110 inner join buffer_linkage_assignments_count_weeks_1164884184 as lac
3111 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3112 and ca_sc.schedule_on = lac.schedule_on
3113 inner join buffer_assignment_limit_staff_types_1164884184 as alst
3114 on lac.assignment_limit_id = alst.assignment_limit_id
3115 and ca_sc.staff_type_id = alst.staff_type_id
3116 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3117 order by null
3118 ) as lac
3119 on ca.linkage_instance_id = lac.linkage_instance_id
3120 and ca.staff_id = lac.staff_id
3121 inner join assignment_limits as al
3122 on lac.assignment_limit_id = al.id
3123 left join buffer_staff_assignments_count_weeks_1164884184 as sac
3124 on lac.assignment_limit_id = sac.assignment_limit_id
3125 and lac.staff_id = sac.staff_id
3126 and lac.start_date = sac.start_date
3127 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
3128
3129 (4787.8ms) delete ca
3130 from buffer_combined_availabilities_1164884184 as ca
3131 inner join (
3132 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3133 sum(lac.assignments_count) as assignments_count
3134 from (
3135 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3136 from buffer_combined_availabilities_1164884184 as ca
3137 inner join buffer_schedules_1164884184 as sc
3138 on ca.schedule_id = sc.id
3139 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3140 order by null
3141 ) as ca_sc
3142 inner join buffer_linkage_assignments_count_periods_1164884184 as lac
3143 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3144 and ca_sc.schedule_on = lac.schedule_on
3145 inner join buffer_assignment_limit_staff_types_1164884184 as alst
3146 on lac.assignment_limit_id = alst.assignment_limit_id
3147 and ca_sc.staff_type_id = alst.staff_type_id
3148 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3149 order by null
3150 ) as lac
3151 on ca.linkage_instance_id = lac.linkage_instance_id
3152 and ca.staff_id = lac.staff_id
3153 inner join assignment_limits as al
3154 on lac.assignment_limit_id = al.id
3155 left join buffer_staff_assignments_count_periods_1164884184 as sac
3156 on lac.assignment_limit_id = sac.assignment_limit_id
3157 and lac.staff_id = sac.staff_id
3158 and lac.start_date = sac.start_date
3159 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
3160
3161 (13539.9ms) delete ca
3162 from buffer_combined_availabilities_1164884184 as ca
3163 inner join (
3164 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3165 sum(lac.assignments_count) as assignments_count
3166 from (
3167 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3168 from buffer_combined_availabilities_1164884184 as ca
3169 inner join buffer_schedules_1164884184 as sc
3170 on ca.schedule_id = sc.id
3171 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3172 order by null
3173 ) as ca_sc
3174 inner join buffer_linkage_assignments_count_rolling_1164884184 as lac
3175 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3176 and ca_sc.schedule_on = lac.schedule_on
3177 inner join buffer_assignment_limit_staff_types_1164884184 as alst
3178 on lac.assignment_limit_id = alst.assignment_limit_id
3179 and ca_sc.staff_type_id = alst.staff_type_id
3180 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3181 order by null
3182 ) as lac
3183 on ca.linkage_instance_id = lac.linkage_instance_id
3184 and ca.staff_id = lac.staff_id
3185 inner join assignment_limits as al
3186 on lac.assignment_limit_id = al.id
3187 left join buffer_staff_assignments_count_rolling_1164884184 as sac
3188 on lac.assignment_limit_id = sac.assignment_limit_id
3189 and lac.staff_id = sac.staff_id
3190 and lac.start_date = sac.start_date
3191 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
3192
3193 (14671.7ms) delete ca
3194 from buffer_combined_availabilities_1164884184 as ca
3195 inner join (
3196 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3197 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
3198 from (
3199 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3200 from buffer_combined_availabilities_1164884184 as ca
3201 inner join buffer_schedules_1164884184 as sc
3202 on ca.schedule_id = sc.id
3203 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3204 order by null
3205 ) as ca_sc
3206 inner join buffer_linkage_assignments_count_consecutive_1164884184 as lac
3207 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3208 and ca_sc.schedule_on = lac.schedule_on
3209 inner join buffer_assignment_limit_staff_types_1164884184 as alst
3210 on lac.assignment_limit_id = alst.assignment_limit_id
3211 and ca_sc.staff_type_id = alst.staff_type_id
3212 left join buffer_staff_assignments_count_consecutive_1164884184 as sac
3213 on lac.assignment_limit_id = sac.assignment_limit_id
3214 and ca_sc.staff_id = sac.staff_id
3215 and lac.schedule_on = sac.schedule_on
3216 and lac.start_date = sac.start_date
3217 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3218 order by null
3219 ) as lac
3220 on ca.linkage_instance_id = lac.linkage_instance_id
3221 and ca.staff_id = lac.staff_id
3222 inner join assignment_limits as al
3223 on lac.assignment_limit_id = al.id
3224 left join (
3225 select assignment_limit_id, staff_id, start_date,
3226 count(schedule_on) as assignments_count
3227 from buffer_staff_assignments_count_consecutive_1164884184
3228 group by assignment_limit_id, staff_id, start_date
3229 ) as sac
3230 on lac.assignment_limit_id = sac.assignment_limit_id
3231 and lac.staff_id = sac.staff_id
3232 and lac.start_date = sac.start_date
3233 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
3234
3235 (210.6ms) /* AnnualDayOfWeekMixRule */
3236 delete ca
3237 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
3238 inner join buffer_schedules_1164884184 as sc
3239 on ca.schedule_id = sc.id
3240 inner join buffer_linkage_dow_targets_1164884184 as ldowt use index (ix_buffer_linkage_dow_targets_composite_1)
3241 on sc.linkage_instance_id = ldowt.linkage_instance_id
3242 and sc.calendar_schedule_id = ldowt.calendar_schedule_id
3243 and ldowt.period_offset = -1
3244 and (dayofweek(sc.schedule_on) - 1) = ldowt.wday
3245 inner join buffer_cumulative_dow_targets_1164884184 as dowt use index (ix_buffer_cumulative_dow_targets_composite_1)
3246 on ca.staff_id = dowt.staff_id
3247 and sc.calendar_schedule_id = dowt.calendar_schedule_id
3248 and (dayofweek(sc.schedule_on) - 1) = dowt.wday
3249 where (sc.day_of_week_mask & 127 /* AnnualDayOfWeekMixRule "days of week selection" */) > 0
3250 and ((round(dowt.adjusted_target, 2)) - dowt.scheduled - ldowt.targets) < 0
3251
3252 (166.4ms) /* MaxAssignmentsRule */
3253 delete ca
3254 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
3255 inner join buffer_schedules_1164884184 as sc
3256 on ca.schedule_id = sc.id
3257 inner join buffer_linkage_call_type_assignments_1164884184 as lcta use index (ix_buffer_linkage_call_type_assignments_composite_1)
3258 on sc.linkage_instance_id = lcta.linkage_instance_id
3259 and sc.calendar_schedule_id = lcta.calendar_schedule_id
3260 and sc.period_offset = lcta.period_offset
3261 and sc.call_type_id = lcta.call_type_id
3262 left join buffer_call_type_assignments_1164884184 as cta use index (ix_buffer_call_type_assignments_composite_1)
3263 on ca.staff_id = cta.staff_id
3264 and sc.calendar_schedule_id = cta.calendar_schedule_id
3265 and sc.period_offset = cta.period_offset
3266 and sc.call_type_id = cta.call_type_id
3267 where
3268 /* weekday checkbox */
3269 (
3270 lcta.max_weekday_assignments is not null
3271 and (ifnull(cta.weekday_assignments, 0) + lcta.weekday_assignments) > lcta.max_weekday_assignments
3272 )
3273 /* weekend checkbox */
3274 or (
3275 lcta.max_weekend_assignments is not null
3276 and (ifnull(cta.weekend_assignments, 0) + lcta.weekend_assignments) > lcta.max_weekend_assignments
3277 )
3278
3279 (157.5ms) /* NegativeLinkRule */
3280 delete ca
3281 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
3282 inner join buffer_negative_relations_1164884184 as nr use index (ix_buffer_negative_relations_composite_1)
3283 on ca.schedule_id = nr.schedule_id
3284 and ca.staff_id = nr.staff_id
3285
3286 (176.0ms) /* BrokenLinkageRule */
3287 truncate table buffer_combined_availability_linkages_1164884184
3288
3289 (149.0ms) insert into buffer_combined_availability_linkages_1164884184 (
3290 staff_id, linkage_instance_id, slots_count, is_primary, has_violations
3291 )
3292 select staff_id, linkage_instance_id,
3293 count(1) as slots_count,
3294 min(is_primary) as is_primary,
3295 max(has_violations) as has_violations
3296 from buffer_combined_availabilities_1164884184 use index (ix_buffer_combined_availabilities_composite_2)
3297 group by staff_id, linkage_instance_id
3298 order by null
3299
3300 (173.8ms) delete ca
3301 from buffer_combined_availabilities_1164884184 as ca use index (ix_buffer_combined_availabilities_composite_1)
3302 inner join buffer_schedules_1164884184 as sc
3303 on ca.schedule_id = sc.id
3304 inner join buffer_linkages_1164884184 as l
3305 on sc.linkage_instance_id = l.linkage_instance_id
3306 inner join buffer_combined_availability_linkages_1164884184 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3307 on ca.staff_id = ls_1.staff_id
3308 and sc.linkage_instance_id = ls_1.linkage_instance_id
3309 left join buffer_schedule_linkages_1164884184 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3310 on ca.staff_id = ls_2.staff_id
3311 and sc.linkage_instance_id = ls_2.linkage_instance_id
3312 where (ls_1.slots_count + ifnull(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */
3313
3314 (197.6ms) select straight_join
3315 sc.schedule_on,
3316 sc.call_type_id,
3317 count(distinct ca.staff_id) as available_members_count,
3318 group_concat(
3319 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3320 order by ls_1.is_primary desc, ca.staff_id
3321 separator ', '
3322 ) as available_members_ids,
3323 sc.linkage_instance_id,
3324 l.slots_count_total as linkage_slots_count
3325 from buffer_combined_availabilities_1164884184 as ca
3326 inner join buffer_schedules_1164884184 as sc
3327 on ca.schedule_id = sc.id
3328 inner join buffer_linkages_1164884184 as l
3329 on sc.linkage_instance_id = l.linkage_instance_id
3330 inner join buffer_combined_availability_linkages_1164884184 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3331 on ca.staff_id = ls_1.staff_id
3332 and sc.linkage_instance_id = ls_1.linkage_instance_id
3333 and ls_1.has_violations = 0
3334 where sc.should_be_evaluated = 1
3335 and sc.should_be_populated = 1
3336 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3337 order by available_members_count, sc.schedule_on, sc.display_order
3338 limit 1
3339
3340 (169.1ms) truncate table buffer_rule_scores_1164884184
3341
3342 (143.7ms) truncate table buffer_rule_calc_filters_1164884184
3343
3344 (157.9ms) insert into buffer_rule_calc_filters_1164884184 (
3345 linkage_instance_id, staff_id
3346 ) values (8201809, 20695),(8201809, 20696),(8201809, 20699),(8201809, 20701),(8201809, 20705),(8201809, 20706)
3347
3348 (154.2ms) /* AnnualDayOfWeekMixRule */
3349 insert into buffer_rule_scores_1164884184 (
3350 linkage_instance_id, schedule_id, staff_id, rule_name, value
3351 )
3352 select straight_join
3353 b_schedules.linkage_instance_id, b_schedules.id as schedule_id, availabilities.staff_id, 'AnnualDayOfWeekMixRule' as rule_name,
3354 (
3355 (b_targets.adjusted_target /* depends on a cumulative targets period */ - b_targets.scheduled) /
3356 b_targets.adjusted_target /* depends on a cumulative targets period */
3357 ) as value
3358 from buffer_rule_calc_filters_1164884184 as filters
3359 inner join buffer_schedules_1164884184 as b_schedules
3360 on filters.linkage_instance_id = b_schedules.linkage_instance_id
3361 inner join buffer_combined_availabilities_1164884184 as availabilities use index (ix_buffer_combined_availabilities_composite_1)
3362 on b_schedules.id = availabilities.schedule_id
3363 and filters.staff_id = availabilities.staff_id
3364 inner join call_types
3365 on b_schedules.call_type_id = call_types.id
3366 and call_types.included_in_targets > 0
3367 inner join buffer_cumulative_dow_targets_1164884184 as b_targets use index (ix_buffer_cumulative_dow_targets_composite_1)
3368 on availabilities.staff_id = b_targets.staff_id
3369 and b_schedules.calendar_schedule_id = b_targets.calendar_schedule_id
3370 and (dayofweek(b_schedules.schedule_on) - 1) = b_targets.wday
3371 order by null
3372
3373 (172.0ms) truncate table buffer_rule_scores_with_corrections_1164884184
3374
3375 (164.6ms) insert into buffer_rule_scores_with_corrections_1164884184 (
3376 linkage_instance_id, schedule_id, staff_id, rule_name, value
3377 )
3378 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3379 ifnull(rule_scores.value, max_values.max_value + 1) as value
3380 from buffer_rule_scores_1164884184 as rule_scores
3381 inner join (
3382 select rule_name, max(value) as max_value
3383 from buffer_rule_scores_1164884184
3384 group by rule_name
3385 order by null
3386 ) as max_values
3387 on rule_scores.rule_name = max_values.rule_name
3388 order by null
3389
3390 (166.6ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3391 from (
3392 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3393 sum(
3394 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3395 rules.weight *
3396 total_values.slots_count
3397 ) as slot_scores
3398 from buffer_rule_scores_with_corrections_1164884184 as rule_scores
3399 inner join (
3400 select rule_name, sum(abs(value)) as total_value,
3401 count(distinct schedule_id) as slots_count
3402 from buffer_rule_scores_with_corrections_1164884184
3403 group by rule_name
3404 order by null
3405 ) as total_values
3406 on rule_scores.rule_name = total_values.rule_name
3407 inner join rules
3408 on rule_scores.rule_name = rules.type
3409 where rules.rule_set_id = 1511
3410 and rules.applied = 1
3411 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3412 order by null
3413 ) as staff_scores
3414 group by linkage_instance_id, staff_id
3415 order by round(avg(slot_scores), 5) desc, rand()
3416 limit 1
3417
3418 PublishedBlock Load (167.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-05' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1381 ORDER BY `published_blocks`.`id` ASC LIMIT 1
3419 (143.0ms) BEGIN
3420 (160.4ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
3421 FROM call_types AS ct
3422 INNER JOIN calendar_schedules AS cs
3423 ON ct.calendar_schedule_id = cs.id
3424 INNER JOIN blocks AS b
3425 ON b.client_id = ct.client_id
3426 AND b.block_schedule_id = cs.block_schedule_id
3427 AND '2018-03-05' BETWEEN b.start_date AND b.end_date
3428 INNER JOIN published_blocks AS pb
3429 ON pb.client_id = ct.client_id
3430 AND pb.block_id = b.id
3431 AND pb.calendar_schedule_id = cs.id
3432 LEFT JOIN published_block_call_types AS pbct
3433 ON pbct.client_id = ct.client_id
3434 AND pbct.assignment_id = ct.id
3435 AND pbct.assignment_type = 'CallType'
3436 AND pbct.block_id = b.id
3437 AND (
3438 (1 << (DAYOFWEEK('2018-03-05') - 1) & pbct.days_of_week_mask) > 0
3439 )
3440 WHERE ct.id = 12694
3441 AND (
3442 (
3443 pb.assignment_ids IS NULL
3444 AND (1 << (DAYOFWEEK('2018-03-05') - 1) & pb.days_of_week_mask) > 0
3445 )
3446 OR pbct.id IS NOT NULL
3447 )
3448
3449 CallType Load (231.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12694 LIMIT 1
3450 Client Load (325.0ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3451 Staff Load (174.3ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20706 LIMIT 1
3452 Schedule Exists (192.7ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12694 AND `schedules`.`client_id` = 319 AND `schedules`.`schedule_on` = '2018-03-05' LIMIT 1
3453 SQL (235.8ms) 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, 12694, 20706, '2018-03-05', '2018-02-13 11:45:35', '2018-02-13 11:45:35', 0, 2, 'auto_populate_step', 166)
3454 VisibleAssignment Load (172.3ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 319 AND `visible_assignments`.`date` = '2018-03-05' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12694 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
3455 (156.1ms) COMMIT
3456FLUSHED: nil:nil
3457 (211.8ms) UPDATE buffer_schedules_1164884184
3458 SET staff_id = 20706,
3459 should_be_populated = 0,
3460 should_be_evaluated = 0
3461 WHERE
3462 call_type_id = 12694
3463 AND schedule_on = '2018-03-05'
3464
3465 (151.8ms) delete from buffer_combined_availabilities_1164884184
3466 where linkage_instance_id = 8201809
3467
3468 (162.0ms) UPDATE buffer_rule_calc_schedules_1164884184
3469 SET staff_id = 20706
3470 WHERE call_type_id = 12694
3471 AND schedule_on = '2018-03-05'
3472
3473 (171.6ms) INSERT INTO buffer_rule_calc_schedules_1164884184
3474 SET staff_id = 20706,
3475 call_type_id = 12694,
3476 schedule_on = '2018-03-05',
3477 day_of_week_mask = 2
3478
3479 (188.8ms) delete from buffer_linkage_targets_1164884184
3480 where linkage_instance_id = 8201809
3481
3482 (184.5ms) delete from buffer_linkage_call_type_targets_1164884184
3483 where linkage_instance_id = 8201809
3484
3485 (167.0ms) delete from buffer_linkage_point_targets_1164884184
3486 where linkage_instance_id = 8201809
3487
3488 (182.8ms) delete from buffer_linkage_hour_targets_1164884184
3489 where linkage_instance_id = 8201809
3490
3491 (168.4ms) delete from buffer_linkage_call_type_assignments_1164884184
3492 where linkage_instance_id = 8201809
3493
3494 (138.1ms) delete from buffer_linkage_call_days_targets_1164884184
3495 where linkage_instance_id = 8201809
3496
3497 (138.3ms) delete from buffer_linkage_assigned_days_1164884184
3498 where linkage_instance_id = 8201809
3499
3500 (140.2ms) delete from buffer_linkage_dow_targets_1164884184
3501 where linkage_instance_id = 8201809
3502
3503 (140.3ms) update buffer_schedules_1164884184 as sc
3504 inner join call_types as ct
3505 on sc.call_type_id = ct.id
3506 and ct.active = 1
3507 inner join buffer_call_type_assignments_1164884184 as cta
3508 on sc.staff_id = cta.staff_id
3509 and sc.calendar_schedule_id = cta.calendar_schedule_id
3510 and sc.period_offset = cta.period_offset
3511 and sc.call_type_id = cta.call_type_id
3512 set cta.weekday_assignments = cta.weekday_assignments + if((sc.day_of_week_mask & 62 > 0), 1, 0),
3513 cta.weekend_assignments = cta.weekend_assignments + if((sc.day_of_week_mask & 65 > 0), 1, 0)
3514 where sc.schedule_on = '2018-03-05'
3515 and sc.call_type_id = 12694
3516 and sc.staff_id = 20706
3517
3518 (145.5ms) INSERT INTO buffer_call_type_assignments_1164884184 (
3519 staff_id, calendar_schedule_id, period_offset, call_type_id, weekday_assignments, weekend_assignments
3520 )
3521 SELECT sc.staff_id,
3522 sc.calendar_schedule_id,
3523 sc.period_offset,
3524 sc.call_type_id,
3525 if((sc.day_of_week_mask & 62 > 0), 1, 0) AS weekday_assignments,
3526 if((sc.day_of_week_mask & 65 > 0), 1, 0) AS weekend_assignments
3527 FROM
3528 buffer_schedules_1164884184 as sc
3529 WHERE
3530 sc.staff_id = 20706
3531 AND sc.schedule_on = '2018-03-05'
3532 AND sc.call_type_id = 12694
3533
3534 (145.6ms) UPDATE buffer_staff_daily_counts_1164884184
3535 SET slots_count = slots_count + 1
3536 WHERE
3537 staff_id = 20706
3538 AND schedule_on = '2018-03-05'
3539 AND linkage_instance_id is null
3540
3541 (148.8ms) INSERT INTO buffer_staff_daily_counts_1164884184
3542 SET slots_count = 1,
3543 staff_id = 20706,
3544 schedule_on = '2018-03-05',
3545 linkage_instance_id = null
3546
3547 (136.9ms) UPDATE buffer_staff_daily_counts_1164884184
3548 SET slots_count = slots_count + 1
3549 WHERE
3550 staff_id = 20706
3551 AND schedule_on = '2018-03-05'
3552 AND linkage_instance_id = 8201809
3553
3554 (135.3ms) INSERT INTO buffer_staff_daily_counts_1164884184
3555 SET slots_count = 1,
3556 staff_id = 20706,
3557 schedule_on = '2018-03-05',
3558 linkage_instance_id = 8201809
3559
3560 (134.7ms) update buffer_staff_specializations_1164884184 as st_sp
3561 inner join max_applicable_call_types as mact
3562 on st_sp.specialization_id = mact.specialization_id
3563 and mact.call_type_id = 12694
3564 inner join buffer_specialization_daily_counts_1164884184 as sp_dc
3565 on st_sp.specialization_id = sp_dc.specialization_id
3566 and sp_dc.staff_id is null
3567 and sp_dc.schedule_on = '2018-03-05'
3568 left join buffer_specialization_daily_counts_1164884184 as sp_dc_2
3569 on st_sp.specialization_id = sp_dc_2.specialization_id
3570 and st_sp.staff_id = sp_dc_2.staff_id
3571 and sp_dc_2.schedule_on = '2018-03-05'
3572 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
3573 where st_sp.staff_id = 20706
3574 and sp_dc_2.id is null
3575
3576 (159.0ms) insert into buffer_specialization_daily_counts_1164884184 (
3577 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
3578 )
3579 select sp.id as specialization_id,
3580 20706 as staff_id,
3581 '2018-03-05' as schedule_on,
3582 1 as staffs_scheduled,
3583 sp.max_scheduled,
3584 sp_dc.should_be_evaluated
3585 from buffer_staff_specializations_1164884184 as st_sp
3586 inner join specializations as sp
3587 on st_sp.specialization_id = sp.id
3588 inner join max_applicable_call_types as mact
3589 on st_sp.specialization_id = mact.specialization_id
3590 and mact.call_type_id = 12694
3591 inner join buffer_specialization_daily_counts_1164884184 as sp_dc
3592 on st_sp.specialization_id = sp_dc.specialization_id
3593 and sp_dc.staff_id is null
3594 and sp_dc.schedule_on = '2018-03-05'
3595 left join buffer_specialization_daily_counts_1164884184 as sp_dc_2
3596 on st_sp.specialization_id = sp_dc_2.specialization_id
3597 and st_sp.staff_id = sp_dc_2.staff_id
3598 and sp_dc_2.schedule_on = '2018-03-05'
3599 where st_sp.staff_id = 20706
3600 and sp_dc_2.id is null
3601
3602 (133.8ms) UPDATE buffer_negative_relations_1164884184
3603 SET staff_id = 20706
3604 WHERE affected_by_schedule_id = (
3605 SELECT id
3606 FROM buffer_schedules_1164884184
3607 WHERE schedule_on = '2018-03-05'
3608 AND call_type_id = 12694
3609 )
3610
3611 (140.4ms) UPDATE buffer_staff_assigned_days_1164884184 as sad
3612 inner join call_types as ct
3613 on ct.id = 12694
3614 and ct.unassigned_day = 0
3615 SET sad.is_assigned = 1
3616 WHERE sad.staff_id = 20706
3617 AND sad.schedule_on = '2018-03-05'
3618
3619 (142.8ms) -- current_oa_targets
3620 update buffer_schedules_1164884184 as sc
3621 inner join buffer_counter_increments_1164884184 as ci
3622 on sc.id = ci.schedule_id
3623 inner join buffer_current_oa_targets_1164884184 as t
3624 on sc.staff_id = t.staff_id
3625 and sc.calendar_schedule_id = t.calendar_schedule_id
3626 and sc.period_offset = t.period_offset
3627 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3628 set t.scheduled = t.scheduled + ci.calls
3629 where sc.schedule_on = '2018-03-05'
3630 and sc.call_type_id = 12694
3631 and sc.staff_id = 20706
3632
3633 (168.1ms) -- cumulative_oa_targets
3634 update buffer_schedules_1164884184 as sc
3635 inner join buffer_counter_increments_1164884184 as ci
3636 on sc.id = ci.schedule_id
3637 and ci.affects_cumulative_targets = 1
3638 inner join buffer_cumulative_oa_targets_1164884184 as t
3639 on sc.calendar_schedule_id = t.calendar_schedule_id
3640 and sc.staff_id = t.staff_id
3641 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3642 set t.scheduled = t.scheduled + ci.calls
3643 where sc.schedule_on = '2018-03-05'
3644 and sc.call_type_id = 12694
3645 and sc.staff_id = 20706
3646
3647 (145.7ms) -- current_cd_targets
3648 update buffer_schedules_1164884184 as sc
3649 inner join buffer_counter_increments_1164884184 as ci
3650 on sc.id = ci.schedule_id
3651 inner join buffer_target_assignments_1164884184 as ta
3652 on sc.call_type_id = ta.call_type_id
3653 inner join buffer_current_cd_targets_1164884184 as t
3654 on sc.staff_id = t.staff_id
3655 and sc.calendar_schedule_id = t.calendar_schedule_id
3656 and sc.period_offset = t.period_offset
3657 and ta.assignment_id = t.assignment_id
3658 and ta.assignment_type = t.assignment_type
3659 and sc.day_of_week_mask = t.day_of_week_mask
3660 set t.scheduled = t.scheduled + ci.calls
3661 where sc.schedule_on = '2018-03-05'
3662 and sc.call_type_id = 12694
3663 and sc.staff_id = 20706
3664
3665 (141.9ms) -- cumulative_cd_targets
3666 update buffer_schedules_1164884184 as sc
3667 inner join buffer_counter_increments_1164884184 as ci
3668 on sc.id = ci.schedule_id
3669 and ci.affects_cumulative_targets = 1
3670 inner join buffer_target_assignments_1164884184 as ta
3671 on sc.call_type_id = ta.call_type_id
3672 inner join buffer_cumulative_cd_targets_1164884184 as t
3673 on sc.calendar_schedule_id = t.calendar_schedule_id
3674 and sc.staff_id = t.staff_id
3675 and ta.assignment_id = t.assignment_id
3676 and ta.assignment_type = t.assignment_type
3677 and sc.day_of_week_mask = t.day_of_week_mask
3678 set t.scheduled = t.scheduled + ci.calls
3679 where sc.schedule_on = '2018-03-05'
3680 and sc.call_type_id = 12694
3681 and sc.staff_id = 20706
3682
3683 (142.8ms) -- current_ct_targets
3684 update buffer_schedules_1164884184 as sc
3685 inner join buffer_counter_increments_1164884184 as ci
3686 on sc.id = ci.schedule_id
3687 inner join buffer_target_assignments_1164884184 as ta
3688 on sc.call_type_id = ta.call_type_id
3689 inner join buffer_current_ct_targets_1164884184 as t
3690 on sc.staff_id = t.staff_id
3691 and sc.calendar_schedule_id = t.calendar_schedule_id
3692 and sc.period_offset = t.period_offset
3693 and ta.assignment_id = t.assignment_id
3694 and ta.assignment_type = t.assignment_type
3695 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3696 set t.scheduled = t.scheduled + ci.calls
3697 where sc.schedule_on = '2018-03-05'
3698 and sc.call_type_id = 12694
3699 and sc.staff_id = 20706
3700
3701 (152.8ms) -- cumulative_ct_targets
3702 update buffer_schedules_1164884184 as sc
3703 inner join buffer_counter_increments_1164884184 as ci
3704 on sc.id = ci.schedule_id
3705 and ci.affects_cumulative_targets = 1
3706 inner join buffer_target_assignments_1164884184 as ta
3707 on sc.call_type_id = ta.call_type_id
3708 inner join buffer_cumulative_ct_targets_1164884184 as t
3709 on sc.calendar_schedule_id = t.calendar_schedule_id
3710 and sc.staff_id = t.staff_id
3711 and ta.assignment_id = t.assignment_id
3712 and ta.assignment_type = t.assignment_type
3713 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3714 set t.scheduled = t.scheduled + ci.calls
3715 where sc.schedule_on = '2018-03-05'
3716 and sc.call_type_id = 12694
3717 and sc.staff_id = 20706
3718
3719 (137.4ms) -- current_dow_targets
3720 update buffer_schedules_1164884184 as sc
3721 inner join buffer_counter_increments_1164884184 as ci
3722 on sc.id = ci.schedule_id
3723 inner join buffer_current_dow_targets_1164884184 as t
3724 on sc.staff_id = t.staff_id
3725 and sc.calendar_schedule_id = t.calendar_schedule_id
3726 and sc.period_offset = t.period_offset
3727 and dayofweek(sc.schedule_on) - 1 = t.wday
3728 set t.scheduled = t.scheduled + ci.calls
3729 where sc.schedule_on = '2018-03-05'
3730 and sc.call_type_id = 12694
3731 and sc.staff_id = 20706
3732
3733 (178.3ms) -- cumulative_dow_targets
3734 update buffer_schedules_1164884184 as sc
3735 inner join buffer_counter_increments_1164884184 as ci
3736 on sc.id = ci.schedule_id
3737 and ci.affects_cumulative_targets = 1
3738 inner join buffer_cumulative_dow_targets_1164884184 as t
3739 on sc.calendar_schedule_id = t.calendar_schedule_id
3740 and sc.staff_id = t.staff_id
3741 and dayofweek(sc.schedule_on) - 1 = t.wday
3742 set t.scheduled = t.scheduled + ci.calls
3743 where sc.schedule_on = '2018-03-05'
3744 and sc.call_type_id = 12694
3745 and sc.staff_id = 20706
3746
3747 (155.1ms) -- current and cumulative point targets
3748
3749 update buffer_schedules_1164884184 as sc
3750 inner join buffer_counter_increments_1164884184 as ci
3751 on sc.id = ci.schedule_id
3752 inner join buffer_point_targets_1164884184 as t
3753 on sc.staff_id = t.staff_id
3754 and sc.calendar_schedule_id = t.calendar_schedule_id
3755 and sc.block_id = t.block_id
3756 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3757 left join buffer_staff_rotation_points_1164884184 as srp
3758 on sc.schedule_on = srp.schedule_on
3759 and sc.staff_id = srp.staff_id
3760 and sc.calendar_schedule_id = srp.calendar_schedule_id
3761 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3762 where sc.schedule_on = '2018-03-05'
3763 and sc.call_type_id = 12694
3764 and sc.staff_id = 20706
3765
3766 (169.8ms) update buffer_schedules_1164884184 as sc
3767 inner join buffer_counter_increments_1164884184 as ci
3768 on sc.id = ci.schedule_id
3769 inner join buffer_target_dates as td
3770 on td.session_id = 1164884184
3771 and td.client_id = 319
3772 and sc.calendar_schedule_id != td.calendar_schedule_id
3773 and sc.schedule_on between td.start_date and td.end_date
3774 inner join buffer_point_targets_1164884184 as t
3775 on sc.staff_id = t.staff_id
3776 and td.calendar_schedule_id = t.calendar_schedule_id
3777 and td.block_id = t.block_id
3778 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3779 left join buffer_staff_rotation_points_1164884184 as srp
3780 on sc.schedule_on = srp.schedule_on
3781 and sc.staff_id = srp.staff_id
3782 and td.calendar_schedule_id = srp.calendar_schedule_id
3783 set t.scheduled = t.scheduled - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0),
3784 t.other_scheduled = t.other_scheduled + ci.points
3785 where sc.schedule_on = '2018-03-05'
3786 and sc.call_type_id = 12694
3787 and sc.staff_id = 20706
3788
3789 (137.1ms) -- current_point_targets
3790 update buffer_schedules_1164884184 as sc
3791 inner join buffer_counter_increments_1164884184 as ci
3792 on sc.id = ci.schedule_id
3793 inner join buffer_current_point_targets_1164884184 as t
3794 on sc.staff_id = t.staff_id
3795 and sc.calendar_schedule_id = t.calendar_schedule_id
3796 and sc.period_offset = t.period_offset
3797 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3798 left join buffer_staff_rotation_points_1164884184 as srp
3799 on sc.schedule_on = srp.schedule_on
3800 and sc.staff_id = srp.staff_id
3801 and sc.calendar_schedule_id = srp.calendar_schedule_id
3802 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3803 where sc.schedule_on = '2018-03-05'
3804 and sc.call_type_id = 12694
3805 and sc.staff_id = 20706
3806
3807 (167.4ms) -- cumulative_point_targets
3808 update buffer_schedules_1164884184 as sc
3809 inner join buffer_counter_increments_1164884184 as ci
3810 on sc.id = ci.schedule_id
3811 and ci.affects_cumulative_targets = 1
3812 inner join buffer_cumulative_point_targets_1164884184 as t
3813 on sc.calendar_schedule_id = t.calendar_schedule_id
3814 and sc.staff_id = t.staff_id
3815 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3816 left join buffer_staff_rotation_points_1164884184 as srp
3817 on sc.schedule_on = srp.schedule_on
3818 and sc.staff_id = srp.staff_id
3819 and sc.calendar_schedule_id = srp.calendar_schedule_id
3820 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3821 where sc.schedule_on = '2018-03-05'
3822 and sc.call_type_id = 12694
3823 and sc.staff_id = 20706
3824
3825 (135.3ms) update buffer_schedules_1164884184 as sc
3826 inner join buffer_counter_increments_1164884184 as ci
3827 on sc.id = ci.schedule_id
3828 inner join buffer_staff_rotation_points_1164884184 as srp
3829 on sc.schedule_on = srp.schedule_on
3830 and sc.staff_id = srp.staff_id
3831 set srp.points = if(ci.affects_rotations = 1, 0.0, srp.points)
3832 where sc.schedule_on = '2018-03-05'
3833 and sc.call_type_id = 12694
3834 and sc.staff_id = 20706
3835
3836 (135.6ms) -- current and cumulative hour targets
3837
3838 update buffer_schedules_1164884184 as sc
3839 inner join buffer_counter_increments_1164884184 as ci
3840 on sc.id = ci.schedule_id
3841 inner join buffer_hour_targets_1164884184 as t
3842 on sc.staff_id = t.staff_id
3843 and sc.calendar_schedule_id = t.calendar_schedule_id
3844 and sc.block_id = t.block_id
3845 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3846 set t.scheduled = t.scheduled + ci.hours
3847 where sc.schedule_on = '2018-03-05'
3848 and sc.call_type_id = 12694
3849 and sc.staff_id = 20706
3850
3851 (136.0ms) update buffer_schedules_1164884184 as sc
3852 inner join buffer_counter_increments_1164884184 as ci
3853 on sc.id = ci.schedule_id
3854 inner join buffer_target_dates as td
3855 on td.session_id = 1164884184
3856 and td.client_id = 319
3857 and sc.calendar_schedule_id != td.calendar_schedule_id
3858 and sc.schedule_on between td.start_date and td.end_date
3859 inner join buffer_hour_targets_1164884184 as t
3860 on sc.staff_id = t.staff_id
3861 and td.calendar_schedule_id = t.calendar_schedule_id
3862 and td.block_id = t.block_id
3863 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3864 set t.other_scheduled = t.other_scheduled + ci.hours
3865 where sc.schedule_on = '2018-03-05'
3866 and sc.call_type_id = 12694
3867 and sc.staff_id = 20706
3868
3869 (278.0ms) -- current_hour_targets
3870 update buffer_schedules_1164884184 as sc
3871 inner join buffer_counter_increments_1164884184 as ci
3872 on sc.id = ci.schedule_id
3873 inner join buffer_current_hour_targets_1164884184 as t
3874 on sc.staff_id = t.staff_id
3875 and sc.calendar_schedule_id = t.calendar_schedule_id
3876 and sc.period_offset = t.period_offset
3877 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3878 set t.scheduled = t.scheduled + ci.hours
3879 where sc.schedule_on = '2018-03-05'
3880 and sc.call_type_id = 12694
3881 and sc.staff_id = 20706
3882
3883 (163.9ms) -- cumulative_hour_targets
3884 update buffer_schedules_1164884184 as sc
3885 inner join buffer_counter_increments_1164884184 as ci
3886 on sc.id = ci.schedule_id
3887 and ci.affects_cumulative_targets = 1
3888 inner join buffer_cumulative_hour_targets_1164884184 as t
3889 on sc.calendar_schedule_id = t.calendar_schedule_id
3890 and sc.staff_id = t.staff_id
3891 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3892 set t.scheduled = t.scheduled + ci.hours
3893 where sc.schedule_on = '2018-03-05'
3894 and sc.call_type_id = 12694
3895 and sc.staff_id = 20706
3896
3897 (141.1ms) /* AssignmentLimitsPerPeriod */
3898 /* Unique Key: assignment_limit_id, staff_id, start_date */
3899 insert into buffer_staff_assignments_count_periods_1164884184 (
3900 assignment_limit_id, staff_id, start_date, end_date, assignments_count
3901 )
3902 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
3903 1 as assignments_count
3904 from buffer_schedules_1164884184 as sc
3905 inner join buffer_assignment_limit_call_types_1164884184 as alct
3906 on sc.call_type_id = alct.call_type_id
3907 inner join assignment_limits as al
3908 on alct.assignment_limit_id = al.id
3909 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3910 and al.max_per_period_limit is not null
3911 inner join assign_staff_types as ast
3912 on sc.staff_id = ast.staff_id
3913 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
3914 inner join buffer_assignment_limit_staff_types_1164884184 as alst
3915 on alct.assignment_limit_id = alst.assignment_limit_id
3916 and ast.staff_type_id = alst.staff_type_id
3917 inner join (
3918 select assignment_limit_id, start_date, end_date
3919 from buffer_linkage_assignments_count_periods_1164884184
3920 group by assignment_limit_id, start_date, end_date
3921 order by null
3922 ) as lac
3923 on alct.assignment_limit_id = lac.assignment_limit_id
3924 and sc.schedule_on between lac.start_date and lac.end_date
3925 where sc.schedule_on = '2018-03-05'
3926 and sc.call_type_id = 12694
3927 and sc.staff_id = 20706
3928 on duplicate key update
3929 assignments_count = assignments_count + 1
3930
3931 (191.9ms) /* AssignmentLimitsPerWeek */
3932 /* Unique Key: assignment_limit_id, staff_id, start_date */
3933 insert into buffer_staff_assignments_count_weeks_1164884184 (
3934 assignment_limit_id, staff_id, start_date, end_date, assignments_count
3935 )
3936 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
3937 1 as assignments_count
3938 from buffer_schedules_1164884184 as sc
3939 inner join buffer_assignment_limit_call_types_1164884184 as alct
3940 on sc.call_type_id = alct.call_type_id
3941 inner join assignment_limits as al
3942 on alct.assignment_limit_id = al.id
3943 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3944 and al.max_weekly_limit is not null
3945 inner join assign_staff_types as ast
3946 on sc.staff_id = ast.staff_id
3947 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
3948 inner join buffer_assignment_limit_staff_types_1164884184 as alst
3949 on alct.assignment_limit_id = alst.assignment_limit_id
3950 and ast.staff_type_id = alst.staff_type_id
3951 inner join (
3952 select assignment_limit_id, start_date, end_date
3953 from buffer_linkage_assignments_count_weeks_1164884184
3954 group by assignment_limit_id, start_date, end_date
3955 order by null
3956 ) as lac
3957 on alct.assignment_limit_id = lac.assignment_limit_id
3958 and sc.schedule_on between lac.start_date and lac.end_date
3959 where sc.schedule_on = '2018-03-05'
3960 and sc.call_type_id = 12694
3961 and sc.staff_id = 20706
3962 on duplicate key update
3963 assignments_count = assignments_count + 1
3964
3965 (312.6ms) /* AssignmentLimitsRolling */
3966 /* Unique Key: assignment_limit_id, staff_id, start_date */
3967 insert into buffer_staff_assignments_count_rolling_1164884184 (
3968 assignment_limit_id, staff_id, start_date, end_date, assignments_count
3969 )
3970 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
3971 1 as assignments_count
3972 from buffer_schedules_1164884184 as sc
3973 inner join buffer_assignment_limit_call_types_1164884184 as alct
3974 on sc.call_type_id = alct.call_type_id
3975 inner join assignment_limits as al
3976 on alct.assignment_limit_id = al.id
3977 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3978 and al.max_rolling_period is not null
3979 and al.max_rolling_limit is not null
3980 inner join assign_staff_types as ast
3981 on sc.staff_id = ast.staff_id
3982 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
3983 inner join buffer_assignment_limit_staff_types_1164884184 as alst
3984 on alct.assignment_limit_id = alst.assignment_limit_id
3985 and ast.staff_type_id = alst.staff_type_id
3986 inner join (
3987 select assignment_limit_id, start_date, end_date
3988 from buffer_linkage_assignments_count_rolling_1164884184
3989 group by assignment_limit_id, start_date, end_date
3990 order by null
3991 ) as lac
3992 on alct.assignment_limit_id = lac.assignment_limit_id
3993 and sc.schedule_on between lac.start_date and lac.end_date
3994 where sc.schedule_on = '2018-03-05'
3995 and sc.call_type_id = 12694
3996 and sc.staff_id = 20706
3997 on duplicate key update
3998 assignments_count = assignments_count + 1
3999
4000 (145.2ms) /* AssignmentLimitsConsecutive */
4001 /* Unique Key: assignment_limit_id, staff_id, schedule_on, start_date */
4002 insert ignore into buffer_staff_assignments_count_consecutive_1164884184 (
4003 assignment_limit_id, staff_id, schedule_on, start_date, end_date
4004 )
4005 select lac.assignment_limit_id, sc.staff_id, sc.schedule_on, lac.start_date, lac.end_date
4006 from buffer_schedules_1164884184 as sc
4007 inner join buffer_assignment_limit_call_types_1164884184 as alct
4008 on sc.call_type_id = alct.call_type_id
4009 inner join assignment_limits as al
4010 on alct.assignment_limit_id = al.id
4011 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4012 and al.max_consecutive_limit is not null
4013 inner join assign_staff_types as ast
4014 on sc.staff_id = ast.staff_id
4015 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4016 inner join buffer_assignment_limit_staff_types_1164884184 as alst
4017 on alct.assignment_limit_id = alst.assignment_limit_id
4018 and ast.staff_type_id = alst.staff_type_id
4019 inner join (
4020 select assignment_limit_id, start_date, end_date
4021 from buffer_linkage_assignments_count_consecutive_1164884184
4022 group by assignment_limit_id, start_date, end_date
4023 order by null
4024 ) as lac
4025 on alct.assignment_limit_id = lac.assignment_limit_id
4026 and sc.schedule_on between lac.start_date and lac.end_date
4027 where sc.schedule_on = '2018-03-05'
4028 and sc.call_type_id = 12694
4029 and sc.staff_id = 20706
4030
4031INFO : (02-13 11:45:43) scheduled for: plan unit: [id:8201809] [2018-03-05 - BU], Illajah Stern. DEPTH(1)
4032**Airbrake: closed