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