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