· 8 years ago · Mar 09, 2018, 04:42 AM
1CalendarSchedule Load (144.6ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
2 CallType Load (146.6ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (12692, 12693, 12694)
3 CalendarSchedule Load (147.0ms) 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 Block Load (145.0ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND `blocks`.`id` = 29251 ORDER BY start_date asc, end_date asc LIMIT 1
5 BlockSchedule Load (145.7ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
6 PopulationTimeframe Load (150.6ms) 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
7 RuleSet Load (146.9ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1527 LIMIT 1
8 Block Load (145.3ms) 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
9INFO : (02-27 08:16:50) phase: Scheduler::Base
10INFO : (02-27 08:16:50) from: 2018-03-01 to: 2018-03-31
11 Rule Load (150.3ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1527 ORDER BY applied desc, weight desc
12INFO : (02-27 08:16:51) rule configurations: CAT
13 - 'Cumulative Assignment Targets' applied {"opt1"=>{"applied"=>"1"}, "round_targets"=>"false"} weight:5
14 - 'Cumulative Combined Assignment Targets' not applied
15 - 'Cumulative Daily Assignment Targets' not applied
16 - 'Cumulative Day of Week Targets' not applied
17 - 'Cumulative Hour Targets' not applied
18 - 'Cumulative Point Targets' not applied
19 - 'Cumulative Weekend / Weekday Assignment Targets' not applied
20 - 'Cumulative Weekend / Weekday Combined Assignment Targets' not applied
21 - 'Cumulative Weekend / Weekday Hour Targets' not applied
22 - 'Cumulative Weekend / Weekday Point Targets' not applied
23 - 'Current Assignment Targets' not applied
24 - 'Current Combined Assignment Targets' not applied
25 - 'Current Daily Assignment Targets' not applied
26 - 'Current Day of Week Targets' not applied
27 - 'Current Hour Targets' not applied
28 - 'Current Point Targets' not applied
29 - 'Current Weekend / Weekday Assignment Targets' not applied
30 - 'Current Weekend / Weekday Combined Assignment Targets' not applied
31 - 'Current Weekend / Weekday Hour Targets' not applied
32 - 'Current Weekend / Weekday Point Targets' not applied
33 - 'Pending Call Requests' not applied
34 - 'Pending No-call Requests' not applied
35 - 'Assignments Per Day' not applied
36 - 'Days Between Assignments' not applied
37 - 'Days Between Same Assignment' not applied
38 - 'Unassigned Days' not applied
39 - 'Weeks Between Same Assignment On Same Day Of Week' not applied
40 - 'Weeks Between Same Day Of Week' not applied
41INFO : (02-27 08:16:51) started: Scheduler::Cache::Collector.collect_all
42INFO : (02-27 08:16:51) collect_staffs
43 Staff Load (151.8ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `assign_staff_types` ON `assign_staff_types`.`staff_id` = `staffs`.`id` INNER JOIN `staff_types` ON `staff_types`.`id` = `assign_staff_types`.`staff_type_id` WHERE `staffs`.`client_id` = 319 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
44 Client Load (148.2ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319
45 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)
46 Specialization Load (145.8ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` IN (711, 712) ORDER BY `specializations`.`priority` ASC
47 StaffCallType Load (186.1ms) 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)
48INFO : (02-27 08:16:54) initial eligibles: [20702,20705,20704,20695,20701,20697,20699,20698,20706,20696]
49INFO : (02-27 08:16:54) collect_call_types
50 CallType Load (151.3ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 319 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
51INFO : (02-27 08:16:54) initial call types: [12692,12693,12694,12706]
52INFO : (02-27 08:16:54) collect_chain_links
53 ChainLink Load (146.0ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 319
54INFO : (02-27 08:16:54) collect_linkages
55INFO : (02-27 08:16:54) collect_schedules
56 Schedule Load (157.5ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 319 AND (`schedules`.`schedule_on` BETWEEN '2018-03-01' AND '2018-03-31')
57INFO : (02-27 08:16:54) collect_slots
58INFO : (02-27 08:16:54) collect_approved_call_requests
59 Request Load (168.8ms) 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
60INFO : (02-27 08:16:55) collect_approved_no_call_requests
61 Request Load (189.4ms) 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
62INFO : (02-27 08:16:55) finished: Scheduler::Cache::Collector.collect_all
63INFO : (02-27 08:16:55) begin to process requests
64 CallType Load (147.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 1381 AND `call_types`.`active` = 1
65 (144.1ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 1381 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
66 Request Load (146.4ms) 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')
67 SQL (181.8ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 319 AND 1=0
68INFO : (02-27 08:16:56) end of requests processing
69 Block Load (144.5ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND (blocks.start_date <= '2018-03-31' AND blocks.end_date >= '2018-03-31') ORDER BY start_date asc, end_date asc LIMIT 1
70 Client Load (148.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
71 NegativeLink Load (145.2ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
72INFO : (02-27 08:16:57) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
73INFO : (02-27 08:16:57) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
74INFO : (02-27 08:16:57) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
75INFO : (02-27 08:16:57) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
76INFO : (02-27 08:16:57) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
77INFO : (02-27 08:16:57) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
78INFO : (02-27 08:16:57) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
79INFO : (02-27 08:16:57) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
80INFO : (02-27 08:16:57) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
81INFO : (02-27 08:16:57) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query015
82INFO : (02-27 08:16:57) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
83INFO : (02-27 08:16:57) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
84INFO : (02-27 08:16:57) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
85INFO : (02-27 08:16:57) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
86INFO : (02-27 08:16:57) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
87INFO : (02-27 08:16:57) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
88INFO : (02-27 08:16:57) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
89INFO : (02-27 08:16:57) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
90INFO : (02-27 08:16:57) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
91INFO : (02-27 08:16:57) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
92INFO : (02-27 08:16:57) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0703
93INFO : (02-27 08:16:57) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0704
94INFO : (02-27 08:16:57) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0705
95INFO : (02-27 08:16:57) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0706
96INFO : (02-27 08:16:57) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0707
97INFO : (02-27 08:16:57) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query0708
98INFO : (02-27 08:16:57) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query0709
99INFO : (02-27 08:16:57) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL
100INFO : (02-27 08:16:57) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
101INFO : (02-27 08:16:57) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
102INFO : (02-27 08:16:57) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
103INFO : (02-27 08:16:57) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
104INFO : (02-27 08:16:57) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query08
105INFO : (02-27 08:16:57) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query085
106INFO : (02-27 08:16:57) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query09
107INFO : (02-27 08:16:57) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query092
108INFO : (02-27 08:16:57) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query093
109INFO : (02-27 08:16:57) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query095
110INFO : (02-27 08:16:57) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query097
111INFO : (02-27 08:16:57) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query098
112INFO : (02-27 08:16:57) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
113INFO : (02-27 08:16:57) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
114INFO : (02-27 08:16:57) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
115INFO : (02-27 08:16:57) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
116INFO : (02-27 08:16:57) <-- clear: 44 - Scheduler::AutoPopulate::Queries::Query09_09_RP
117INFO : (02-27 08:16:57) <-- clear: 45 - Scheduler::AutoPopulate::Queries::Query10
118INFO : (02-27 08:16:57) <-- clear: 46 - Scheduler::AutoPopulate::Queries::QueryScore02
119INFO : (02-27 08:16:57) <-- clear: 47 - Scheduler::AutoPopulate::Queries::QueryScore03
120INFO : (02-27 08:16:57) <-- clear: 48 - Scheduler::AutoPopulate::Queries::QueryScore04
121INFO : (02-27 08:16:57) <-- clear: 49 - Scheduler::AutoPopulate::Queries::Query11
122INFO : (02-27 08:16:57) <-- clear: 50 - Scheduler::AutoPopulate::Queries::DropTables
123 (146.9ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_793447700
124 (202.0ms) DROP TABLE IF EXISTS buffer_annual_point_targets_793447700
125 (146.3ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_793447700
126 (145.7ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_793447700
127 (145.9ms) DROP TABLE IF EXISTS buffer_call_days_targets_793447700
128 (147.7ms) DROP TABLE IF EXISTS buffer_call_type_targets_793447700
129 (147.1ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_793447700
130 (148.5ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_793447700
131 (145.2ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_793447700
132 (147.6ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_793447700
133 (147.0ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_793447700
134 (158.9ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_793447700
135 (147.6ms) DROP TABLE IF EXISTS buffer_current_cd_targets_793447700
136 (154.3ms) DROP TABLE IF EXISTS buffer_current_ct_targets_793447700
137 (146.4ms) DROP TABLE IF EXISTS buffer_current_dow_targets_793447700
138 (148.2ms) DROP TABLE IF EXISTS buffer_current_hour_targets_793447700
139 (145.6ms) DROP TABLE IF EXISTS buffer_current_oa_targets_793447700
140 (151.3ms) DROP TABLE IF EXISTS buffer_current_point_targets_793447700
141 (145.9ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_793447700
142 (157.6ms) DROP TABLE IF EXISTS buffer_hour_targets_793447700
143 (146.5ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_793447700
144 (156.2ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_793447700
145 (147.3ms) DROP TABLE IF EXISTS buffer_linkage_targets_793447700
146 (153.0ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_793447700
147 (146.7ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_793447700
148 (148.6ms) DROP TABLE IF EXISTS buffer_location_privileges_793447700
149 (147.6ms) DROP TABLE IF EXISTS buffer_periods_793447700
150 (154.4ms) DROP TABLE IF EXISTS buffer_point_targets_793447700
151 (150.0ms) DROP TABLE IF EXISTS buffer_schedules_793447700
152 (152.8ms) DROP TABLE IF EXISTS buffer_staff_rotation_points_793447700
153 (146.6ms) DROP TABLE IF EXISTS buffer_target_call_types_793447700
154 (154.4ms) DROP TABLE IF EXISTS buffer_target_daily_points_793447700
155 (146.2ms) DROP TABLE IF EXISTS buffer_target_dates_list_793447700
156 (148.4ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_793447700
157 (153.7ms) DROP TABLE IF EXISTS buffer_targets_793447700
158 (145.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_793447700
159 (148.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_793447700
160 (153.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_793447700
161 (145.9ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_793447700
162 (149.2ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_793447700
163 (146.8ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_793447700
164 (151.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_793447700
165 (145.8ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_793447700
166 (156.4ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_793447700
167 (147.0ms) DROP TABLE IF EXISTS buffer_call_type_assignments_793447700
168 (146.0ms) DROP TABLE IF EXISTS buffer_combined_availabilities_793447700
169 (145.9ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_793447700
170 (145.6ms) DROP TABLE IF EXISTS buffer_counter_increments_793447700
171 (145.9ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_793447700
172 (181.2ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_consecutive_793447700
173 (145.9ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_periods_793447700
174 (146.9ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_rolling_793447700
175 (147.5ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_weeks_793447700
176 (153.5ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_793447700
177 (146.0ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_793447700
178 (147.9ms) DROP TABLE IF EXISTS buffer_linkage_patterns_793447700
179 (145.9ms) DROP TABLE IF EXISTS buffer_linkages_793447700
180 (145.9ms) DROP TABLE IF EXISTS buffer_location_privileges_793447700
181 (145.1ms) DROP TABLE IF EXISTS buffer_min_distances_793447700
182 (155.2ms) DROP TABLE IF EXISTS buffer_negative_relations_793447700
183 (146.4ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_793447700
184 (152.2ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_793447700
185 (150.6ms) DROP TABLE IF EXISTS buffer_rule_scores_793447700
186 (151.6ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_793447700
187 (145.9ms) DROP TABLE IF EXISTS buffer_schedule_linkages_793447700
188 (146.0ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_793447700
189 (147.1ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_793447700
190 (154.9ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_793447700
191 (146.1ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_periods_793447700
192 (146.4ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_rolling_793447700
193 (147.2ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_weeks_793447700
194 (164.1ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_consecutive_793447700
195 (147.1ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_793447700
196 (146.9ms) DROP TABLE IF EXISTS buffer_staff_specializations_793447700
197 (146.3ms) DROP TABLE IF EXISTS buffer_static_availabilities_793447700
198 (151.8ms) DROP TABLE IF EXISTS buffer_target_assignments_793447700
199 (146.6ms) DROP VIEW IF EXISTS buffer_weekly_call_type_targets_793447700
200
201 (157.6ms) DROP VIEW IF EXISTS buffer_current_weekly_ct_targets_793447700
202
203 (149.0ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_ct_targets_793447700
204
205 (154.0ms) DROP VIEW IF EXISTS buffer_weekly_targets_793447700
206
207 (147.1ms) DROP VIEW IF EXISTS buffer_current_weekly_oa_targets_793447700
208
209 (145.5ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_oa_targets_793447700
210
211 (146.6ms) DROP VIEW IF EXISTS buffer_weekly_point_targets_793447700
212
213 (158.7ms) DROP VIEW IF EXISTS buffer_current_weekly_point_targets_793447700
214
215 (151.4ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_point_targets_793447700
216
217 (149.8ms) DROP VIEW IF EXISTS buffer_weekly_hour_targets_793447700
218
219 (147.2ms) DROP VIEW IF EXISTS buffer_current_weekly_hour_targets_793447700
220
221 (178.8ms) DROP VIEW IF EXISTS buffer_cumulative_weekly_hour_targets_793447700
222
223 (144.9ms) delete from buffer_target_dates
224 where session_id = 793447700
225 and client_id = 319
226
227INFO : (02-27 08:17:10) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
228 (148.6ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_793447700 LIKE buffer_assignment_limit_call_types
229 (148.6ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_793447700 LIKE buffer_assignment_limit_periods
230 (160.2ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_793447700 LIKE buffer_assignment_limit_staff_types
231 (154.1ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_793447700 LIKE buffer_auto_populate_slots
232 (152.4ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_793447700 LIKE buffer_call_type_assignments
233 (148.8ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_793447700 LIKE buffer_combined_availabilities
234 (153.1ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_793447700 LIKE buffer_combined_availability_linkages
235 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_793447700 LIKE buffer_counter_increments
236 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_793447700 LIKE buffer_linkage_assigned_days
237 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_consecutive_793447700 LIKE buffer_linkage_assignments_count_consecutive
238 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_periods_793447700 LIKE buffer_linkage_assignments_count_periods
239 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_rolling_793447700 LIKE buffer_linkage_assignments_count_rolling
240 (150.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_weeks_793447700 LIKE buffer_linkage_assignments_count_weeks
241 (148.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_793447700 LIKE buffer_linkage_call_type_assignments
242 (149.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_793447700 LIKE buffer_linkage_dow_targets
243 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_793447700 LIKE buffer_linkage_patterns
244 (148.4ms) CREATE TABLE IF NOT EXISTS buffer_linkages_793447700 LIKE buffer_linkages
245 (148.2ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_793447700 LIKE buffer_location_privileges
246 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_793447700 LIKE buffer_min_distances
247 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_793447700 LIKE buffer_negative_relations
248 (148.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_793447700 LIKE buffer_rule_calc_filters
249 (148.9ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_793447700 LIKE buffer_rule_calc_schedules
250 (154.9ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_793447700 LIKE buffer_rule_scores
251 (150.8ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_793447700 LIKE buffer_rule_scores_with_corrections
252 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_793447700 LIKE buffer_schedule_linkages
253 (147.9ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_793447700 LIKE buffer_schedules_linkage_patterns
254 (152.8ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_793447700 LIKE buffer_specialization_daily_counts
255 (150.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_793447700 LIKE buffer_staff_assigned_days
256 (149.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_periods_793447700 LIKE buffer_staff_assignments_count_periods
257 (153.5ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_rolling_793447700 LIKE buffer_staff_assignments_count_rolling
258 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_weeks_793447700 LIKE buffer_staff_assignments_count_weeks
259 (149.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_consecutive_793447700 LIKE buffer_staff_assignments_count_consecutive
260 (151.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_793447700 LIKE buffer_staff_daily_counts
261 (149.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_793447700 LIKE buffer_staff_specializations
262 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_793447700 LIKE buffer_static_availabilities
263 (161.2ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_793447700 LIKE buffer_target_assignments
264 (148.9ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_793447700 LIKE buffer_annual_hour_targets
265 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_793447700 LIKE buffer_annual_point_targets
266 (149.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_793447700 LIKE buffer_annual_targets_staff_totals
267 (146.6ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_793447700 LIKE buffer_auto_populate_slots
268 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_793447700 LIKE buffer_call_days_targets
269 (148.5ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_793447700 LIKE buffer_call_type_targets
270 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_793447700 LIKE buffer_cumulative_cd_targets
271 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_793447700 LIKE buffer_cumulative_ct_targets
272 (149.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_793447700 LIKE buffer_cumulative_dow_targets
273 (148.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_793447700 LIKE buffer_cumulative_hour_targets
274 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_793447700 LIKE buffer_cumulative_oa_targets
275 (148.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_793447700 LIKE buffer_cumulative_point_targets
276 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_793447700 LIKE buffer_current_cd_targets
277 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_793447700 LIKE buffer_current_ct_targets
278 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_793447700 LIKE buffer_current_dow_targets
279 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_793447700 LIKE buffer_current_hour_targets
280 (151.0ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_793447700 LIKE buffer_current_oa_targets
281 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_793447700 LIKE buffer_current_point_targets
282 (153.5ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_793447700 LIKE buffer_day_of_week_targets
283 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_793447700 LIKE buffer_hour_targets
284 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_793447700 LIKE buffer_linkage_call_days_targets
285 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_793447700 LIKE buffer_linkage_call_type_targets
286 (148.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_793447700 LIKE buffer_linkage_targets
287 (150.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_793447700 LIKE buffer_linkage_hour_targets
288 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_793447700 LIKE buffer_linkage_point_targets
289 (165.7ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_793447700 LIKE buffer_location_privileges
290 (170.9ms) CREATE TABLE IF NOT EXISTS buffer_periods_793447700 LIKE buffer_periods
291 (209.9ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_793447700 LIKE buffer_point_targets
292 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_schedules_793447700 LIKE buffer_schedules
293 (153.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_rotation_points_793447700 LIKE buffer_staff_rotation_points
294 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_793447700 LIKE buffer_target_call_types
295 (148.3ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_793447700 LIKE buffer_target_daily_points
296 (148.7ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_793447700 LIKE buffer_target_dates_list
297 (155.0ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_793447700 LIKE buffer_target_primary_availabilities
298 (150.7ms) CREATE TABLE IF NOT EXISTS buffer_targets_793447700 LIKE buffer_targets
299 (151.0ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_793447700 LIKE buffer_tmp_targets_availabilities
300 (148.4ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_793447700 LIKE buffer_tmp_targets_availabilities_exclusions
301 (150.4ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_793447700 LIKE buffer_tmp_targets_client_totals
302 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_793447700 LIKE buffer_tmp_targets_scheduled
303 (148.8ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_793447700 LIKE buffer_tmp_targets_staff_totals
304INFO : (02-27 08:17:22) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
305 (149.8ms) select
306 b.start_date,
307 b.end_date,
308 (pb.assignment_ids IS NULL) as fully_published,
309 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
310 pbct.days_of_week_mask
311 from blocks as b
312 inner join published_blocks as pb
313 on pb.block_id = b.id
314 inner join published_block_call_types as pbct
315 on pbct.published_block_id = pb.id
316
317 where b.client_id = 319
318 and pb.calendar_schedule_id = 1381
319 and b.end_date BETWEEN '2018-03-01' AND '2018-03-31'
320
321 AutopopulateExclusion Load (145.7ms) 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')
322 (153.7ms) INSERT INTO buffer_auto_populate_slots_793447700
323 (schedule_on, day_of_week_mask, call_type_id, display_order, should_be_populated)
324 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)
325
326INFO : (02-27 08:17:23) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
327 (146.9ms) insert into buffer_auto_populate_slots_793447700/* client_id */ (
328 schedule_on,
329 day_of_week_mask,
330 call_type_id,
331 display_order,
332 should_be_populated
333 )
334 select distinct straight_join
335 date_add(
336 auto_populate_slots.schedule_on,
337 interval soft_linkages.offset day
338 ) as schedule_on,
339 (1 << (
340 dayofweek(
341 date_add(
342 auto_populate_slots.schedule_on,
343 interval soft_linkages.offset day
344 )
345 ) - 1
346 )
347 ) as day_of_week_mask,
348 soft_linkages.linked_call_type_id as call_type_id,
349 call_types.display_order,
350 0 as should_be_populated
351 from
352 buffer_auto_populate_slots_793447700/* client_id */ as auto_populate_slots
353 inner join soft_linkages
354 on auto_populate_slots.call_type_id = soft_linkages.call_type_id
355 and auto_populate_slots.day_of_week_mask = soft_linkages.day_of_week_mask
356 left join buffer_auto_populate_slots_793447700/* client_id */ as auto_populate_slots_2
357 on date_add(
358 auto_populate_slots.schedule_on,
359 interval soft_linkages.offset day
360 ) = auto_populate_slots_2.schedule_on
361 and soft_linkages.linked_call_type_id = auto_populate_slots_2.call_type_id
362 inner join call_types
363 on soft_linkages.linked_call_type_id = call_types.id
364 where auto_populate_slots_2.id is null
365 order by null
366
367INFO : (02-27 08:17:23) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
368 (145.4ms) insert into buffer_auto_populate_slots_793447700 (
369 schedule_on,
370 day_of_week_mask,
371 call_type_id,
372 display_order,
373 should_be_populated
374 )
375 select distinct straight_join
376 date_add(
377 auto_populate_slots.schedule_on,
378 interval linkage_relations.distance_in_days day
379 ) as schedule_on,
380 (1 << (
381 dayofweek(
382 date_add(
383 auto_populate_slots.schedule_on,
384 interval linkage_relations.distance_in_days day
385 )
386 ) - 1
387 )
388 ) as day_of_week_mask,
389 linkage_relations.linked_call_type_id as call_type_id,
390 call_types.display_order,
391 0 as should_be_populated
392 from
393 buffer_auto_populate_slots_793447700 as auto_populate_slots
394 inner join linkage_relations use index (ix_linkage_relations_composite1)
395 on auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
396 and auto_populate_slots.call_type_id = linkage_relations.call_type_id
397 left join buffer_auto_populate_slots_793447700 as auto_populate_slots_2 use index (ix_buffer_auto_populate_slots_on_date_and_call)
398 on date_add(
399 auto_populate_slots.schedule_on,
400 interval linkage_relations.distance_in_days day
401 ) = auto_populate_slots_2.schedule_on
402 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
403 inner join call_types
404 on linkage_relations.linked_call_type_id = call_types.id
405 where auto_populate_slots_2.id is null
406 order by null
407
408INFO : (02-27 08:17:23) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
409 (145.1ms) insert into linkage_relations (
410 client_id,
411 day_of_week_mask,
412 call_type_id,
413 linked_call_type_id,
414 distance_in_days,
415 linkage_template_id
416 )
417 select distinct straight_join
418 319 /* client_id */ as client_id,
419 aps.day_of_week_mask,
420 aps.call_type_id,
421 aps.call_type_id as linked_call_type_id,
422 0 as distance_in_days,
423 ifnull(
424 lr.linkage_template_id,
425 @linkage_template_max := @linkage_template_max + 1
426 ) as linkage_template_id
427 from
428 buffer_auto_populate_slots_793447700/* client_id */ as aps
429 inner join (
430 select @linkage_template_max := count(distinct linkage_template_id) - 1
431 from linkage_relations
432 where client_id = 319 /* client_id */
433 order by null
434 ) as lt
435 on 1 = 1
436 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
437 on aps.day_of_week_mask = lr.day_of_week_mask
438 and aps.call_type_id = lr.call_type_id
439 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
440 on aps.day_of_week_mask = lr_2.day_of_week_mask
441 and aps.call_type_id = lr_2.call_type_id
442 and aps.call_type_id = lr_2.linked_call_type_id
443 and lr_2.distance_in_days = 0
444 where lr_2.id is null
445 order by null
446
447 (156.7ms) insert into buffer_schedules_793447700 (
448 call_type_id,
449 staff_id,
450 schedule_on,
451 day_of_week_mask,
452 display_order,
453 week_number,
454 should_be_evaluated,
455 should_be_populated,
456 linkage_instance_id
457 )
458 select
459 call_type_id,
460 staff_id,
461 schedule_on,
462 day_of_week_mask,
463 display_order,
464 week_number,
465 max(should_be_evaluated) as should_be_evaluated,
466 max(should_be_populated) as should_be_populated,
467 min(linkage_instance_id) as linkage_instance_id
468 from (
469 select straight_join
470 auto_populate_slots_2.call_type_id,
471 schedules.staff_id,
472 auto_populate_slots_2.schedule_on,
473 auto_populate_slots_2.day_of_week_mask,
474 auto_populate_slots_2.display_order,
475 yearweek(
476 auto_populate_slots_2.schedule_on,
477 if(clients.start_of_week = 0, 2, 7)
478 ) as week_number,
479 if(schedules.staff_id is null, 1, 0) as should_be_evaluated,
480 (
481 case
482 when (auto_populate_slots_2.should_be_populated = 1 and schedules.staff_id is null) then 1
483 else 0
484 end
485 ) as should_be_populated,
486 (
487 linkage_relations.linkage_template_id * 1000000 +
488 yearweek(
489 auto_populate_slots.schedule_on,
490 if(clients.start_of_week = 0, 2, 7)
491 )
492 ) as linkage_instance_id
493 from
494 buffer_auto_populate_slots_793447700 as auto_populate_slots
495 inner join clients
496 on clients.id = 319 /* client_id */
497 inner join linkage_relations use index (ix_linkage_relations_composite1)
498 on auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
499 and auto_populate_slots.call_type_id = linkage_relations.call_type_id
500 inner join buffer_auto_populate_slots_793447700 as auto_populate_slots_2 use index (ix_buffer_auto_populate_slots_on_date_and_call)
501 on date_add(
502 auto_populate_slots.schedule_on,
503 interval linkage_relations.distance_in_days day
504 ) = auto_populate_slots_2.schedule_on
505 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
506 left join schedules use index (ix_schedules_call_date)
507 on auto_populate_slots_2.call_type_id = schedules.call_type_id
508 and auto_populate_slots_2.schedule_on = schedules.schedule_on
509 ) as t1
510 group by
511 call_type_id,
512 staff_id,
513 schedule_on,
514 day_of_week_mask,
515 display_order,
516 week_number
517 order by null
518
519INFO : (02-27 08:17:23) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
520 (152.8ms) update buffer_schedules_793447700 as sc
521 inner join call_types as ct
522 on sc.call_type_id = ct.id
523 inner join calendar_schedules as cs
524 on ct.calendar_schedule_id = cs.id
525 inner join blocks as b
526 on cs.block_schedule_id = b.block_schedule_id
527 and sc.schedule_on between b.start_date and b.end_date
528 set sc.calendar_schedule_id = cs.id,
529 sc.block_id = b.id
530 where sc.linkage_instance_id >= 0
531
532 BlockSchedule Load (146.9ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
533 (149.2ms) insert into buffer_periods_793447700 (
534 calendar_schedule_id, block_schedule_id, period_offset, start_block_display_order, end_block_display_order,
535 start_date, end_date, should_be_populated, should_be_evaluated
536 )
537 select t1.calendar_schedule_id,
538 t1.block_schedule_id,
539 t1.period_offset,
540 t2.start_block_display_order,
541 t2.end_block_display_order,
542 t2.start_date,
543 t2.end_date,
544 t1.should_be_populated,
545 t1.should_be_evaluated
546 from (
547 select sc.calendar_schedule_id,
548 b.block_schedule_id,
549 if (
550 (month(b.start_date) - pt.begin_period) >= 0,
551 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type,
552 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type
553 ) as period_offset,
554 max(sc.should_be_populated) as should_be_populated,
555 max(sc.should_be_evaluated) as should_be_evaluated
556 from (
557 select sc.calendar_schedule_id, sc.block_id,
558 max(ps.should_be_populated) as should_be_populated,
559 max(1) as should_be_evaluated
560 from buffer_schedules_793447700 as sc
561 inner join buffer_auto_populate_slots_793447700 as ps
562 on sc.schedule_on = ps.schedule_on
563 and sc.call_type_id = ps.call_type_id
564 group by sc.calendar_schedule_id, sc.block_id
565 order by null
566 ) as sc
567 inner join blocks as b
568 on sc.block_id = b.id
569 inner join population_timeframes as pt
570 on pt.client_id = 319
571 and pt.timeframe_type = 1
572 group by sc.calendar_schedule_id, b.block_schedule_id, period_offset
573 order by null
574 ) as t1
575 inner join (
576 select b.block_schedule_id,
577 if (
578 (month(b.start_date) - pt.begin_period) >= 0,
579 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type,
580 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type
581 ) as period_offset,
582 min(b.display_order) as start_block_display_order,
583 max(b.display_order) as end_block_display_order,
584 min(b.start_date) as start_date,
585 max(b.end_date) as end_date
586 from blocks as b
587 inner join population_timeframes as pt
588 on pt.client_id = 319
589 and pt.timeframe_type = 1
590 where b.client_id = 319
591 group by b.block_schedule_id, period_offset
592 order by null
593 ) as t2
594 on t1.block_schedule_id = t2.block_schedule_id
595 and t1.period_offset = t2.period_offset
596 order by null
597
598 (148.7ms) update buffer_schedules_793447700 as sc
599 inner join buffer_periods_793447700 as p
600 on sc.calendar_schedule_id = p.calendar_schedule_id
601 and sc.schedule_on between p.start_date and p.end_date
602 set sc.period_offset = p.period_offset
603 where sc.linkage_instance_id >= 0
604
605INFO : (02-27 08:17:24) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
606 (147.4ms) delete from buffer_target_dates
607 where session_id = 793447700
608
609 (145.7ms) select distinct calendar_schedule_id
610 from buffer_periods_793447700
611
612 (162.5ms) insert into buffer_target_dates (
613 session_id,
614 client_id,
615 calendar_schedule_id,
616 block_id,
617 start_date,
618 end_date,
619 block_display_order,
620 period_offset,
621 build_oa_targets,
622 oa_targets_source,
623 copy_oa_targets,
624 build_ct_targets,
625 ct_targets_source,
626 copy_ct_targets,
627 build_dow_targets,
628 dow_targets_source,
629 copy_dow_targets,
630 build_point_targets,
631 point_targets_source,
632 copy_point_targets,
633 build_hour_targets,
634 hour_targets_source,
635 copy_hour_targets,
636 build_cd_targets,
637 cd_targets_source,
638 copy_cd_targets,
639 is_published,
640 is_partially_published,
641 affected_by_populate,
642 within_cumulative_period
643 )
644 select
645 793447700 as session_id,
646 319 as client_id,
647 t1.calendar_schedule_id,
648 t1.block_id,
649 t1.start_date,
650 t1.end_date,
651 t1.block_display_order,
652 t1.period_offset,
653 case
654 when (
655 (
656 FALSE
657 )
658 and t1.is_published = 0
659 and t2_1.block_id is null
660 and t2_2.block_id is null
661 ) then 1
662 else 0
663 end as build_oa_targets,
664 case
665 when (t2_1.block_id is not null) then 'published'
666 when (t2_2.block_id is not null) then 'unpublished'
667 else ''
668 end as oa_targets_source,
669 0 as copy_oa_targets,
670 case
671 when (
672 (
673 t1.within_cumulative_period = 1 /* AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
674 )
675 and t1.is_published = 0
676 ) then (
677 case
678 when (pb.id is null and t3_2.block_id is null) then 1
679 when (pb.has_published = 1 and pb.has_unpublished = 0 and t3_1.block_id is null) then 1
680 when (pb.has_published = 0 and pb.has_unpublished = 1 and t3_2.block_id is null) then 1
681 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
682 else 0
683 end
684 )
685 else 0
686 end as build_ct_targets,
687 case
688 when (pb.id is null and t3_1.block_id is not null) then 'published'
689 when (pb.id is null and t3_2.block_id is not null) then 'unpublished'
690 when (pb.has_published = 1 and pb.has_unpublished = 0 and t3_1.block_id is not null) then 'published'
691 when (pb.has_published = 0 and pb.has_unpublished = 1 and t3_2.block_id is not null) then 'unpublished'
692 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'
693 else ''
694 end as ct_targets_source,
695 0 as copy_ct_targets,
696 case
697 when (
698 (
699 FALSE
700 )
701 and t1.is_published = 0
702 and t4_1.block_id is null
703 and t4_2.block_id is null
704 ) then 1
705 else 0
706 end as build_dow_targets,
707 case
708 when (t4_1.block_id is not null) then 'published'
709 when (t4_2.block_id is not null) then 'unpublished'
710 else ''
711 end as dow_targets_source,
712 0 as copy_dow_targets,
713 case
714 when (
715 (
716 FALSE
717 )
718 and t1.is_published = 0
719 and t5_1.block_id is null
720 and t5_2.block_id is null
721 ) then 1
722 else 0
723 end as build_point_targets,
724 case
725 when (t5_1.block_id is not null) then 'published'
726 when (t5_2.block_id is not null) then 'unpublished'
727 else ''
728 end as point_targets_source,
729 0 as copy_point_targets,
730 case
731 when (
732 (
733 FALSE
734 )
735 and t1.is_published = 0
736 and t6_1.block_id is null
737 and t6_2.block_id is null
738 ) then 1
739 else 0
740 end as build_hour_targets,
741 case
742 when (t6_1.block_id is not null) then 'published'
743 when (t6_2.block_id is not null) then 'unpublished'
744 else ''
745 end as hour_targets_source,
746 0 as copy_hour_targets,
747 case
748 when (
749 (
750 FALSE
751 )
752 and t1.is_published = 0
753 ) then (
754 case
755 when (pb.id is null and t7_2.block_id is null) then 1
756 when (pb.has_published = 1 and pb.has_unpublished = 0 and t7_1.block_id is null) then 1
757 when (pb.has_published = 0 and pb.has_unpublished = 1 and t7_2.block_id is null) then 1
758 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
759 else 0
760 end
761 )
762 else 0
763 end as build_cd_targets,
764 case
765 when (pb.id is null and t7_1.block_id is not null) then 'published'
766 when (pb.id is null and t7_2.block_id is not null) then 'unpublished'
767 when (pb.has_published = 1 and pb.has_unpublished = 0 and t7_1.block_id is not null) then 'published'
768 when (pb.has_published = 0 and pb.has_unpublished = 1 and t7_2.block_id is not null) then 'unpublished'
769 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'
770 else ''
771 end as cd_targets_source,
772 0 as copy_cd_targets,
773 t1.is_published,
774 case
775 when (pb.id is not null) then 1
776 else 0
777 end as is_partially_published,
778 t1.affected_by_populate,
779 t1.within_cumulative_period
780 from (
781 select
782 t1.calendar_schedule_id,
783 b.id as block_id,
784 b.start_date,
785 b.end_date,
786 t1.block_display_order,
787 p.period_offset,
788 case
789 when (t1.block_display_order between t1.start_block_display_order and t1.end_block_display_order) then 1
790 else 0
791 end as affected_by_populate,
792 case
793 when (t1.block_display_order between (t1.timeframe_ending - (2 - 1)) and t1.timeframe_ending) then 1
794 else 0
795 end as within_cumulative_period,
796 case
797 when (pb.id is not null) then 1
798 else 0
799 end as is_published
800 from (
801 select
802 t1.calendar_schedule_id,
803 t1.block_schedule_id,
804 (t1.end_block_display_order - ns.item_number) as block_display_order,
805 t1.start_block_display_order,
806 t1.end_block_display_order,
807 t1.timeframe_ending
808 from (
809 select
810 calendar_schedule_id,
811 block_schedule_id,
812 min(start_block_display_order) as start_block_display_order,
813 max(end_block_display_order) as end_block_display_order,
814 max(
815 case
816 when (should_be_populated = 1) then end_block_display_order
817 else null
818 end
819 ) as timeframe_ending
820 from
821 buffer_periods_793447700
822 group by
823 calendar_schedule_id,
824 block_schedule_id
825 order by null
826 ) as t1
827 inner join numeric_sequence as ns
828 on ns.item_number < 100
829 and t1.end_block_display_order >= ns.item_number
830 order by null
831 ) as t1
832 inner join blocks as b
833 on t1.block_schedule_id = b.block_schedule_id
834 and t1.block_display_order = b.display_order
835 left join buffer_periods_793447700 as p
836 on t1.calendar_schedule_id = p.calendar_schedule_id
837 and t1.block_display_order between p.start_block_display_order and p.end_block_display_order
838 left join published_blocks as pb
839 on t1.calendar_schedule_id = pb.calendar_schedule_id
840 and b.id = pb.block_id
841 and pb.assignment_ids is null
842 and pb.days_of_week_mask = 127
843 where
844 t1.block_display_order between t1.start_block_display_order and t1.end_block_display_order
845 or t1.block_display_order between (t1.timeframe_ending - (2 - 1)) and t1.timeframe_ending
846 order by null
847 ) as t1
848 left join (
849 select
850 calendar_schedule_id,
851 block_id
852 from
853 targets
854 where
855 calendar_schedule_id in (1381) /* list of calendar schedules */
856 group by
857 calendar_schedule_id,
858 block_id
859 order by null
860 ) as t2_1
861 on t1.calendar_schedule_id = t2_1.calendar_schedule_id
862 and t1.block_id = t2_1.block_id
863 left join (
864 select
865 calendar_schedule_id,
866 block_id
867 from
868 unpublished_targets
869 where
870 calendar_schedule_id in (1381) /* list of calendar schedules */
871 group by
872 calendar_schedule_id,
873 block_id
874 order by null
875 ) as t2_2
876 on t1.calendar_schedule_id = t2_2.calendar_schedule_id
877 and t1.block_id = t2_2.block_id
878 left join (
879 select
880 calendar_schedule_id,
881 block_id
882 from
883 call_type_targets
884 where
885 calendar_schedule_id in (1381) /* list of calendar schedules */
886 group by
887 calendar_schedule_id,
888 block_id
889 order by null
890 ) as t3_1
891 on t1.calendar_schedule_id = t3_1.calendar_schedule_id
892 and t1.block_id = t3_1.block_id
893 left join (
894 select
895 calendar_schedule_id,
896 block_id
897 from
898 unpublished_call_type_targets
899 where
900 calendar_schedule_id in (1381) /* list of calendar schedules */
901 group by
902 calendar_schedule_id,
903 block_id
904 order by null
905 ) as t3_2
906 on t1.calendar_schedule_id = t3_2.calendar_schedule_id
907 and t1.block_id = t3_2.block_id
908 left join (
909 select
910 calendar_schedule_id,
911 block_id
912 from
913 day_of_week_targets
914 where
915 calendar_schedule_id in (1381) /* list of calendar schedules */
916 group by
917 calendar_schedule_id,
918 block_id
919 order by null
920 ) as t4_1
921 on t1.calendar_schedule_id = t4_1.calendar_schedule_id
922 and t1.block_id = t4_1.block_id
923 left join (
924 select
925 calendar_schedule_id,
926 block_id
927 from
928 unpublished_day_of_week_targets
929 where
930 calendar_schedule_id in (1381) /* list of calendar schedules */
931 group by
932 calendar_schedule_id,
933 block_id
934 order by null
935 ) as t4_2
936 on t1.calendar_schedule_id = t4_2.calendar_schedule_id
937 and t1.block_id = t4_2.block_id
938 left join (
939 select
940 calendar_schedule_id,
941 block_id
942 from
943 point_targets
944 where
945 calendar_schedule_id in (1381) /* list of calendar schedules */
946 group by
947 calendar_schedule_id,
948 block_id
949 order by null
950 ) as t5_1
951 on t1.calendar_schedule_id = t5_1.calendar_schedule_id
952 and t1.block_id = t5_1.block_id
953 left join (
954 select
955 calendar_schedule_id,
956 block_id
957 from
958 unpublished_point_targets
959 where
960 calendar_schedule_id in (1381) /* list of calendar schedules */
961 group by
962 calendar_schedule_id,
963 block_id
964 order by null
965 ) as t5_2
966 on t1.calendar_schedule_id = t5_2.calendar_schedule_id
967 and t1.block_id = t5_2.block_id
968 left join (
969 select
970 calendar_schedule_id,
971 block_id
972 from
973 hour_targets
974 where
975 calendar_schedule_id in (1381) /* list of calendar schedules */
976 group by
977 calendar_schedule_id,
978 block_id
979 order by null
980 ) as t6_1
981 on t1.calendar_schedule_id = t6_1.calendar_schedule_id
982 and t1.block_id = t6_1.block_id
983 left join (
984 select
985 calendar_schedule_id,
986 block_id
987 from
988 unpublished_hour_targets
989 where
990 calendar_schedule_id in (1381) /* list of calendar schedules */
991 group by
992 calendar_schedule_id,
993 block_id
994 order by null
995 ) as t6_2
996 on t1.calendar_schedule_id = t6_2.calendar_schedule_id
997 and t1.block_id = t6_2.block_id
998 left join (
999 select
1000 calendar_schedule_id,
1001 block_id
1002 from
1003 call_days_targets
1004 where
1005 calendar_schedule_id in (1381) /* list of calendar schedules */
1006 group by
1007 calendar_schedule_id,
1008 block_id
1009 order by null
1010 ) as t7_1
1011 on t1.calendar_schedule_id = t7_1.calendar_schedule_id
1012 and t1.block_id = t7_1.block_id
1013 left join (
1014 select
1015 calendar_schedule_id,
1016 block_id
1017 from
1018 unpublished_call_days_targets
1019 where
1020 calendar_schedule_id in (1381) /* list of calendar schedules */
1021 group by
1022 calendar_schedule_id,
1023 block_id
1024 order by null
1025 ) as t7_2
1026 on t1.calendar_schedule_id = t7_2.calendar_schedule_id
1027 and t1.block_id = t7_2.block_id
1028 left join (
1029 select
1030 pb.id,
1031 pb.calendar_schedule_id,
1032 pb.block_id,
1033 max(
1034 case
1035 when (coalesce(pbct.days_of_week_mask, 0) > 0) then 1
1036 else 0
1037 end
1038 ) as has_published,
1039 max(
1040 case
1041 when (coalesce(pbct.days_of_week_mask, 0) < 127) then 1
1042 else 0
1043 end
1044 ) as has_unpublished
1045 from (
1046 select
1047 case
1048 when (tg.id is null) then ct.id
1049 else tg.id
1050 end as assignment_id,
1051 case
1052 when (tg.id is null) then 'CallType'
1053 else 'TargetGroup'
1054 end as assignment_type,
1055 ct.calendar_schedule_id
1056 from
1057 call_types as ct
1058 left join target_group_calls as tgc
1059 on ct.id = tgc.call_type_id
1060 left join target_groups as tg
1061 on tgc.target_group_id = tg.id
1062 and tg.active = 1
1063 where
1064 ct.calendar_schedule_id in (1381)
1065 and ct.active = 1
1066 and ct.included_in_targets > 0
1067 and ct.days_of_week_mask > 0
1068 group by
1069 assignment_id,
1070 assignment_type,
1071 ct.calendar_schedule_id
1072 order by null
1073 ) as ct
1074 inner join published_blocks as pb
1075 on ct.calendar_schedule_id = pb.calendar_schedule_id
1076 and (
1077 pb.assignment_ids is not null
1078 or pb.days_of_week_mask < 127
1079 )
1080 left join published_block_call_types as pbct
1081 on pb.id = pbct.published_block_id
1082 and ct.assignment_id = pbct.assignment_id
1083 and ct.assignment_type = pbct.assignment_type
1084 group by
1085 pb.id,
1086 pb.calendar_schedule_id,
1087 pb.block_id
1088 order by null
1089 ) as pb
1090 on t1.calendar_schedule_id = pb.calendar_schedule_id
1091 and t1.block_id = pb.block_id
1092 order by null
1093
1094Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0101 - 457.34200000000004ms
1095 (148.2ms) /*
1096 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1097 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1098 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1099 */
1100 update buffer_target_dates
1101 set build_cd_targets = 1
1102 where
1103 session_id = 793447700
1104 and build_cd_targets = 0
1105 and cd_targets_source = ''
1106 and (
1107 build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1108 )
1109
1110 (146.2ms) /*
1111 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1112 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1113 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1114 */
1115 update buffer_target_dates
1116 set
1117 build_ct_targets = 1, ct_targets_source = "" /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1118 where
1119 session_id = 793447700
1120 and build_cd_targets = 1
1121
1122Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0102 - 295.213ms
1123 (144.7ms) /*
1124 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1125 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1126 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1127 */
1128 insert into buffer_call_days_targets_793447700 (
1129 calendar_schedule_id,
1130 block_id,
1131 staff_id,
1132 assignment_id,
1133 assignment_type,
1134 day_of_week_mask,
1135 is_weekend,
1136 raw_target,
1137 target,
1138 initial_target,
1139 scheduled,
1140 available_days
1141 )
1142 select straight_join
1143 cdt.calendar_schedule_id,
1144 cdt.block_id,
1145 cdt.staff_id,
1146 cdt.assignment_id,
1147 cdt.assignment_type,
1148 cdt.day_of_week_mask,
1149 cdt.is_weekend,
1150 cdt.raw_target,
1151 cdt.target,
1152 cdt.initial_target,
1153 cdt.scheduled,
1154 cdt.available_days
1155 from
1156 buffer_target_dates as td
1157 inner join call_days_targets as cdt
1158 on td.calendar_schedule_id = cdt.calendar_schedule_id
1159 and td.block_id = cdt.block_id
1160 where
1161 td.session_id = 793447700
1162 and td.build_cd_targets = 0
1163 and td.cd_targets_source in ('published', 'both')
1164 and (
1165 td.build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1166 )
1167 order by null
1168
1169 (150.7ms) /*
1170 MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
1171 StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
1172 MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
1173 */
1174 insert into buffer_call_days_targets_793447700 (
1175 calendar_schedule_id,
1176 block_id,
1177 staff_id,
1178 assignment_id,
1179 assignment_type,
1180 day_of_week_mask,
1181 is_weekend,
1182 raw_target,
1183 target,
1184 initial_target,
1185 scheduled,
1186 available_days
1187 )
1188 select straight_join
1189 ucdt.calendar_schedule_id,
1190 ucdt.block_id,
1191 ucdt.staff_id,
1192 ucdt.assignment_id,
1193 ucdt.assignment_type,
1194 ucdt.day_of_week_mask,
1195 ucdt.is_weekend,
1196 ucdt.raw_target,
1197 ucdt.target,
1198 ucdt.initial_target,
1199 ucdt.scheduled,
1200 ucdt.available_days
1201 from
1202 buffer_target_dates as td
1203 inner join unpublished_call_days_targets as ucdt
1204 on td.calendar_schedule_id = ucdt.calendar_schedule_id
1205 and td.block_id = ucdt.block_id
1206 where
1207 td.session_id = 793447700
1208 and td.build_cd_targets = 0
1209 and td.cd_targets_source in ('unpublished', 'both')
1210 and (
1211 td.build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1212 )
1213 order by null
1214
1215Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0103 - 296.331ms
1216 (146.4ms) select max(build_cd_targets) as build_cd_targets,
1217 max(build_ct_targets) as build_ct_targets,
1218 max(build_oa_targets) as build_oa_targets,
1219 max(build_dow_targets) as build_dow_targets,
1220 max(build_point_targets) as build_point_targets,
1221 max(build_hour_targets) as build_hour_targets
1222 from buffer_target_dates
1223 where session_id = 793447700
1224 order by null
1225
1226Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets02 - 146.781ms
1227 (150.6ms) /* AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
1228 insert into buffer_cumulative_ct_targets_793447700 (
1229 staff_id,
1230 assignment_id,
1231 assignment_type,
1232 is_weekend,
1233 calendar_schedule_id,
1234 raw_target,
1235 adjusted_target,
1236 scheduled,
1237 available_days
1238 )
1239 select
1240 t.staff_id,
1241 t.assignment_id,
1242 t.assignment_type,
1243 t.is_weekend,
1244 t.calendar_schedule_id,
1245 sum(t.raw_target) as raw_target,
1246 sum(t.adjusted_target) as adjusted_target,
1247 sum(t.scheduled) as scheduled,
1248 sum(t.available_days) as available_days
1249 from (
1250 select
1251 t.calendar_schedule_id,
1252 t.block_id,
1253 t.staff_id,
1254 t.assignment_id,
1255 t.assignment_type,
1256 t.is_weekend,
1257 t.raw_target,
1258 t.target as adjusted_target,
1259 t.scheduled,
1260 t.available_days
1261 from buffer_target_dates as td
1262 inner join buffer_call_type_targets_793447700 as t
1263 on td.calendar_schedule_id = t.calendar_schedule_id
1264 and td.block_id = t.block_id
1265 where
1266 td.session_id = 793447700
1267 and td.build_ct_targets = 1
1268 and td.within_cumulative_period = 1
1269 union
1270 select
1271 ctt.calendar_schedule_id,
1272 ctt.block_id,
1273 ctt.staff_id,
1274 ctt.assignment_id,
1275 ctt.assignment_type,
1276 ctt.is_weekend,
1277 ctt.raw_target,
1278 ctt.target,
1279 ctt.scheduled,
1280 ctt.available_days
1281 from buffer_target_dates as td
1282 inner join call_type_targets as ctt
1283 on td.calendar_schedule_id = ctt.calendar_schedule_id
1284 and td.block_id = ctt.block_id
1285 where
1286 td.session_id = 793447700
1287 and td.build_ct_targets = 0
1288 and td.ct_targets_source in ('published', 'both')
1289 and td.within_cumulative_period = 1
1290 union
1291 select
1292 uctt.calendar_schedule_id,
1293 uctt.block_id,
1294 uctt.staff_id,
1295 uctt.assignment_id,
1296 uctt.assignment_type,
1297 uctt.is_weekend,
1298 uctt.raw_target,
1299 uctt.target,
1300 uctt.scheduled,
1301 uctt.available_days
1302 from
1303 buffer_target_dates as td
1304 inner join unpublished_call_type_targets as uctt
1305 on td.calendar_schedule_id = uctt.calendar_schedule_id
1306 and td.block_id = uctt.block_id
1307 where
1308 td.session_id = 793447700
1309 and td.build_ct_targets = 0
1310 and td.ct_targets_source in ('unpublished', 'both')
1311 and td.within_cumulative_period = 1
1312 union all
1313 select
1314 tad.calendar_schedule_id,
1315 tad.block_id,
1316 tad.staff_id,
1317 tad.assignment_id,
1318 tad.assignment_type,
1319 tad.is_weekend,
1320 0 as raw_target,
1321 0 as target,
1322 tad.adjustment_value as scheduled,
1323 0 as available_days
1324 from
1325 buffer_target_dates as td
1326 inner join target_adjustment_details as tad
1327 on td.calendar_schedule_id = tad.calendar_schedule_id
1328 and td.block_id = tad.block_id
1329 and tad.staff_id > 0
1330 where
1331 td.session_id = 793447700
1332 and td.within_cumulative_period = 1
1333 order by null
1334 ) as t
1335 group by
1336 t.staff_id,
1337 t.assignment_id,
1338 t.assignment_type,
1339 t.is_weekend,
1340 t.calendar_schedule_id
1341 order by null
1342
1343 (151.2ms) /* CumulativeWeeklyCallTypeTargetsRule */
1344 create or replace view buffer_cumulative_weekly_ct_targets_793447700 (
1345 staff_id,
1346 assignment_id,
1347 assignment_type,
1348 calendar_schedule_id,
1349 raw_target,
1350 adjusted_target,
1351 scheduled,
1352 available_days
1353 ) as
1354 select
1355 staff_id,
1356 assignment_id,
1357 assignment_type,
1358 calendar_schedule_id,
1359 sum(raw_target) as raw_target,
1360 sum(adjusted_target) as adjusted_target,
1361 sum(scheduled) as scheduled,
1362 sum(available_days) as available_days
1363 from buffer_cumulative_ct_targets_793447700
1364 group by
1365 staff_id,
1366 assignment_id,
1367 assignment_type,
1368 calendar_schedule_id
1369 order by null
1370
1371Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0702 - 302.933ms
1372Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0703 - 0.041ms
1373INFO : (02-27 08:17:25) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
1374 (166.6ms) insert into buffer_counter_increments_793447700 (
1375 schedule_id,
1376 calls,
1377 points,
1378 hours,
1379 affects_cumulative_targets,
1380 affects_rotations
1381 )
1382 select
1383 sc.id as schedule_id,
1384 if(ct.included_in_targets > 0, 1, 0) as calls,
1385 ifnull(if(h.observed_date is null or dp.point is not null, dp.point, dp_2.point), 0.0) as points,
1386 ct.hours,
1387 if(b.display_order between (t1.timeframe_ending - (2 /* cumulative targets period */ - 1)) and t1.timeframe_ending, 1, 0) as affects_cumulative_targets,
1388 if(ct.display_concurrent_rotations = 1, 0, 1) as affects_rotations
1389 from buffer_schedules_793447700 as sc
1390 inner join (
1391 select
1392 id as call_type_id,
1393 included_in_targets,
1394 display_concurrent_rotations,
1395 ifnull(
1396 timestampdiff(
1397 minute,
1398 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
1399 concat(
1400 '1900-01-0',
1401 if(
1402 (start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0)
1403 and (timediff(maketime(end_time_hour, end_time_minute, 0), maketime(start_time_hour, start_time_minute, 0)) <= 0),
1404 '2', '1'
1405 ),
1406 ' ',
1407 maketime(end_time_hour, end_time_minute, 0)
1408 )
1409 ) / 60,
1410 0.0
1411 ) as hours
1412 from call_types
1413 where client_id = 319
1414 and active = 1
1415 order by null
1416 ) as ct
1417 on sc.call_type_id = ct.call_type_id
1418 left join (
1419 select
1420 i_hd.observed_date,
1421 if(i_hd.observed_date > i_hd.extended_date, i_hd.extended_date, i_hd.observed_date) as date_from,
1422 if(i_hd.observed_date < i_hd.extended_date, i_hd.extended_date, i_hd.observed_date) as date_to
1423 from holidays as i_h
1424 inner join holiday_dates as i_hd
1425 on i_h.id = i_hd.holiday_id
1426 where
1427 i_h.client_id = 319
1428 and (
1429 i_h.observed = 1
1430 or i_h.extended = 1
1431 )
1432 order by null
1433 ) as h
1434 on sc.schedule_on between h.date_from and h.date_to
1435 left join assignment_annotations as aa
1436 on ct.call_type_id = aa.assignmentable_id
1437 and aa.assignmentable_type = 'CallType'
1438 and aa.annotation_id = 0 /* annotation = "None" */
1439 and aa.applicable = 1
1440 left join assignment_annotation_daily_points as dp
1441 on aa.id = dp.assignment_annotation_id
1442 and if(
1443 h.observed_date is null,
1444 dayofweek(sc.schedule_on) - 1,
1445 if(sc.schedule_on = h.observed_date, 7, 8)
1446 ) = dp.day
1447 left join assignment_annotation_daily_points as dp_2
1448 on aa.id = dp_2.assignment_annotation_id
1449 and dayofweek(sc.schedule_on) - 1 = dp_2.day
1450 left join blocks as b
1451 on sc.block_id = b.id
1452 left join (
1453 select calendar_schedule_id,
1454 max(if(should_be_populated = 1, end_block_display_order, null)) as timeframe_ending
1455 from buffer_periods_793447700
1456 group by calendar_schedule_id
1457 order by null
1458 ) as t1
1459 on sc.calendar_schedule_id = t1.calendar_schedule_id
1460 where sc.should_be_evaluated = 1
1461 order by null
1462
1463INFO : (02-27 08:17:25) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
1464 (147.5ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
1465 insert into buffer_linkage_patterns_793447700 (
1466 period_offset, calendar_schedule_id, block_id, block_start_date, block_start_dow, block_length,
1467 linkage_pattern_id, pattern_start_dow, days_before_start, pattern_length, start_offset, iterations_count
1468 )
1469 select
1470 b1.period_offset,
1471 b1.calendar_schedule_id,
1472 b1.block_id,
1473 b2.start_date as block_start_date,
1474 dayofweek(b2.start_date) as block_start_dow,
1475 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
1476 lp.id as linkage_pattern_id,
1477 (lp.start_on + 1) as pattern_start_dow,
1478 lp.days_before_start,
1479 (lp.length * 7) as pattern_length,
1480 null as start_offset,
1481 null as iterations_count
1482 from (
1483 select calendar_schedule_id, block_id, period_offset
1484 from buffer_schedules_793447700
1485 group by calendar_schedule_id, block_id, period_offset
1486 order by null
1487 ) as b1
1488 inner join blocks as b2
1489 on b1.block_id = b2.id
1490 inner join linkage_patterns as lp
1491 on lp.client_id = 319
1492 and b1.calendar_schedule_id = lp.calendar_schedule_id
1493 order by null
1494
1495 (146.1ms) -- 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
1496 update buffer_linkage_patterns_793447700
1497 set start_offset = if(
1498 (pattern_start_dow + if(pattern_start_dow < block_start_dow, 7, 0) - block_start_dow) <= days_before_start,
1499 (pattern_start_dow + if(pattern_start_dow < block_start_dow, 7, 0) - block_start_dow),
1500 (block_start_dow + if(block_start_dow < pattern_start_dow, 7, 0) - pattern_start_dow) * (-1)
1501 )
1502
1503 (145.6ms) -- In some cases (when a block and a pattern are starting on different day of week or when a pattern is shorter than a block)
1504 -- a pattern should be repeated couple of times (in order to cover the entire block)
1505 update buffer_linkage_patterns_793447700
1506 set iterations_count = ceiling((block_length + if(start_offset < 0, abs(start_offset), 0)) * 1.0 / pattern_length)
1507
1508 (146.3ms) -- Generate an assignments sequence for every linkage pattern
1509 insert into buffer_schedules_linkage_patterns_793447700 (
1510 call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1511 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1512 )
1513 select
1514 lps.assignment_id as call_type_id,
1515 null as staff_id,
1516 if(
1517 lp.start_offset < 0,
1518 date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day),
1519 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
1520 ) as schedule_on,
1521 lp.period_offset,
1522 null as day_of_week_mask,
1523 null as display_order,
1524 null as week_number,
1525 0 as should_be_evaluated,
1526 0 as should_be_populated,
1527 lp.linkage_pattern_id as linkage_instance_id,
1528 lp.block_id,
1529 lp.calendar_schedule_id
1530 from numeric_sequence as ns
1531 inner join buffer_linkage_patterns_793447700 as lp
1532 on ns.item_number <= (lp.iterations_count - 1)
1533 inner join linkage_pattern_slots as lps
1534 on lp.linkage_pattern_id = lps.linkage_pattern_id
1535 and lps.assignment_type = 'CallType'
1536 where
1537 if(
1538 lp.start_offset < 0,
1539 (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset),
1540 (ns.item_number * lp.pattern_length + lps.offset)
1541 ) between 0 and (lp.block_length - 1)
1542 order by null
1543
1544 (177.9ms) -- Fill the columns that we left blank in the previous "INSERT"
1545 update buffer_schedules_linkage_patterns_793447700 as slp
1546 inner join clients as cl
1547 on cl.id = 319
1548 inner join call_types as ct
1549 on slp.call_type_id = ct.id
1550 left join buffer_schedules_793447700 as bs
1551 on slp.schedule_on = bs.schedule_on
1552 and slp.call_type_id = bs.call_type_id
1553 set slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
1554 slp.display_order = ct.display_order,
1555 slp.week_number = yearweek(slp.schedule_on, if(cl.start_of_week = 0, 2, 7)),
1556 slp.should_be_evaluated = ifnull(bs.should_be_evaluated, 0),
1557 slp.should_be_populated = ifnull(bs.should_be_populated, 0)
1558
1559 (144.2ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
1560 delete slp_1
1561 from buffer_schedules_linkage_patterns_793447700 as slp_1
1562 inner join (
1563 select linkage_instance_id
1564 from buffer_schedules_linkage_patterns_793447700
1565 group by linkage_instance_id
1566 having max(should_be_evaluated) = 0
1567 and max(should_be_populated) = 0
1568 order by null
1569 ) as slp_2
1570 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
1571
1572 (144.1ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
1573 update buffer_schedules_linkage_patterns_793447700 as slp
1574 inner join buffer_schedules_793447700 as bs_1
1575 on slp.schedule_on = bs_1.schedule_on
1576 and slp.call_type_id = bs_1.call_type_id
1577 inner join buffer_schedules_793447700 as bs_2
1578 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
1579 set bs_2.linkage_instance_id = slp.linkage_instance_id
1580
1581 (145.9ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
1582 -- should be copied over to the buffer schedules table (used during the auto-populate process)
1583 insert into buffer_schedules_793447700 (
1584 call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1585 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1586 )
1587 select call_type_id, staff_id, schedule_on, period_offset, day_of_week_mask, display_order, week_number,
1588 should_be_evaluated, should_be_populated, linkage_instance_id, block_id, calendar_schedule_id
1589 from buffer_schedules_linkage_patterns_793447700
1590 where should_be_evaluated = 0
1591 and should_be_populated = 0
1592
1593INFO : (02-27 08:17:27) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query015
1594 (155.5ms) insert into buffer_schedules_793447700 (
1595 call_type_id, staff_id, schedule_on, day_of_week_mask, display_order,
1596 should_be_evaluated, should_be_populated, linkage_instance_id
1597 )
1598 select distinct
1599 schedules_call_overlap.call_type_id,
1600 schedules_call_overlap.staff_id,
1601 schedules_call_overlap.schedule_on,
1602 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
1603 call_types.display_order,
1604 0 as should_be_evaluated,
1605 0 as should_be_populated,
1606 -1 as linkage_instance_id
1607 from (
1608 select schedule_on
1609 from buffer_schedules_793447700
1610 where should_be_evaluated = 1
1611 group by schedule_on
1612 order by null
1613 ) as buffer_schedules_1
1614 inner join schedules as schedules_call_overlap
1615 on schedules_call_overlap.client_id = 319
1616 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1617 left join buffer_schedules_793447700 as buffer_schedules_2
1618 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1619 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1620 inner join call_types
1621 on schedules_call_overlap.call_type_id = call_types.id
1622 where buffer_schedules_2.id is null
1623 order by null
1624
1625INFO : (02-27 08:17:27) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1626INFO : (02-27 08:17:27) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1627INFO : (02-27 08:17:27) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1628INFO : (02-27 08:17:27) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1629INFO : (02-27 08:17:27) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1630 (149.3ms) insert into buffer_schedules_793447700 (
1631 call_type_id,
1632 staff_id,
1633 schedule_on,
1634 day_of_week_mask,
1635 display_order,
1636 should_be_evaluated,
1637 should_be_populated,
1638 linkage_instance_id
1639 )
1640 select distinct straight_join
1641 schedules.call_type_id,
1642 schedules.staff_id,
1643 schedules.schedule_on,
1644 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1645 call_types.display_order,
1646 0 as should_be_evaluated,
1647 0 as should_be_populated,
1648 -1 as linkage_instance_id
1649 from
1650 buffer_schedules_793447700 as buffer_schedules_1
1651 inner join negative_links_influences use index (ix_unique_links)
1652 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1653 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1654 and negative_links_influences.source_type = 'CallType'
1655 and negative_links_influences.target_type = 'CallType'
1656 inner join schedules use index (ix_schedules_call_date)
1657 on date_add(
1658 buffer_schedules_1.schedule_on,
1659 interval negative_links_influences.offset day
1660 ) = schedules.schedule_on
1661 and negative_links_influences.target_id = schedules.call_type_id
1662 left join buffer_schedules_793447700 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1663 on schedules.schedule_on = buffer_schedules_2.schedule_on
1664 and schedules.call_type_id = buffer_schedules_2.call_type_id
1665 inner join call_types
1666 on schedules.call_type_id = call_types.id
1667 where
1668 buffer_schedules_1.linkage_instance_id >= 0
1669 and buffer_schedules_1.should_be_evaluated = 1
1670 and buffer_schedules_2.id is null
1671 order by null
1672
1673INFO : (02-27 08:17:27) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1674 (152.7ms) insert into buffer_schedules_793447700 (
1675 call_type_id,
1676 staff_id,
1677 schedule_on,
1678 day_of_week_mask,
1679 display_order,
1680 should_be_evaluated,
1681 should_be_populated,
1682 linkage_instance_id
1683 )
1684 select distinct straight_join
1685 0 as call_type_id,
1686 vacations.staff_id,
1687 vacations.schedule_on,
1688 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1689 -1 as display_order,
1690 0 as should_be_evaluated,
1691 0 as should_be_populated,
1692 -1 as linkage_instance_id
1693 from
1694 buffer_schedules_793447700 as buffer_schedules_1
1695 inner join negative_links_influences use index (ix_unique_links)
1696 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1697 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1698 and negative_links_influences.source_type = 'CallType'
1699 and negative_links_influences.target_id is null
1700 and negative_links_influences.target_type = 'Vacation'
1701 inner join vacations use index (ix_vacations_on_schedule_and_client)
1702 on vacations.client_id = 319 /* client_id */
1703 and date_add(
1704 buffer_schedules_1.schedule_on,
1705 interval negative_links_influences.offset day
1706 ) = vacations.schedule_on
1707 and vacations.vacation_type = 0 /* vacation */
1708 and vacations.status in (3) /* based on the "non-working requests" settings */
1709 left join buffer_schedules_793447700 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1710 on vacations.staff_id = buffer_schedules_2.staff_id
1711 and vacations.schedule_on = buffer_schedules_2.schedule_on
1712 and buffer_schedules_2.call_type_id = 0
1713 where
1714 buffer_schedules_1.linkage_instance_id >= 0
1715 and buffer_schedules_1.should_be_evaluated = 1
1716 and buffer_schedules_2.id is null
1717 order by null
1718
1719 (145.2ms) insert into buffer_schedules_793447700 (
1720 call_type_id,
1721 staff_id,
1722 schedule_on,
1723 day_of_week_mask,
1724 display_order,
1725 should_be_evaluated,
1726 should_be_populated,
1727 linkage_instance_id
1728 )
1729 select distinct straight_join
1730 (-1 * negative_links_influences.target_id) as call_type_id,
1731 vacations.staff_id,
1732 vacations.schedule_on,
1733 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1734 -1 as display_order,
1735 0 as should_be_evaluated,
1736 0 as should_be_populated,
1737 -1 as linkage_instance_id
1738 from
1739 buffer_schedules_793447700 as buffer_schedules_1
1740 inner join negative_links_influences use index (ix_unique_links)
1741 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1742 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1743 and negative_links_influences.source_type = 'CallType'
1744 and negative_links_influences.target_id > 0
1745 and negative_links_influences.target_type = 'NonClinicalType'
1746 inner join vacations use index (ix_vacations_on_schedule_and_client)
1747 on vacations.client_id = 319 /* client_id */
1748 and date_add(
1749 buffer_schedules_1.schedule_on,
1750 interval negative_links_influences.offset day
1751 ) = vacations.schedule_on
1752 and vacations.vacation_type = 1 /* non-clinical */
1753 and negative_links_influences.target_id = vacations.non_clinical_type_id
1754 and vacations.status in (3) /* based on the "non-working requests" settings */
1755 left join buffer_schedules_793447700 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1756 on vacations.staff_id = buffer_schedules_2.staff_id
1757 and vacations.schedule_on = buffer_schedules_2.schedule_on
1758 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1759 where
1760 buffer_schedules_1.linkage_instance_id >= 0
1761 and buffer_schedules_1.should_be_evaluated = 1
1762 and buffer_schedules_2.id is null
1763 order by null;
1764
1765INFO : (02-27 08:17:27) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1766 (285.5ms) insert into buffer_linkages_793447700 (
1767 linkage_instance_id,
1768 slots_count_total,
1769 has_conflicts
1770 )
1771 select straight_join
1772 schedules.linkage_instance_id,
1773 count(1) as slots_count_total,
1774 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1775 from
1776 buffer_schedules_793447700 as schedules
1777 left join (
1778 select straight_join
1779 linkage_relations.linkage_template_id
1780 from linkage_relations
1781 inner join negative_links_influences use index (ix_unique_links)
1782 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1783 and linkage_relations.call_type_id = negative_links_influences.source_id
1784 and negative_links_influences.source_type = 'CallType'
1785 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1786 and negative_links_influences.target_type = 'CallType'
1787 and linkage_relations.distance_in_days = negative_links_influences.offset
1788 where linkage_relations.client_id = 319 /* client_id */
1789 and (
1790 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1791 or linkage_relations.distance_in_days != 0
1792 )
1793 group by linkage_relations.linkage_template_id
1794 order by null
1795 ) as linkage_conflicts
1796 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1797 where schedules.linkage_instance_id >= 0
1798 group by
1799 schedules.linkage_instance_id,
1800 has_conflicts
1801 order by null
1802
1803INFO : (02-27 08:17:28) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1804 (151.7ms) insert into buffer_target_assignments_793447700 (
1805 call_type_id,
1806 target_group_id,
1807 assignment_id,
1808 assignment_type
1809 )
1810 select
1811 t1.call_type_id,
1812 ifnull(tg.id, 0) as target_group_id,
1813 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1814 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1815 from (
1816 select ct.id as call_type_id
1817 from buffer_schedules_793447700 as sc
1818 inner join call_types as ct
1819 on sc.call_type_id = ct.id
1820 and ct.active = 1
1821 and ct.included_in_targets > 0
1822 where linkage_instance_id >= 0
1823 group by ct.id
1824 order by null
1825 ) as t1
1826 left join target_group_calls as tgc
1827 on t1.call_type_id = tgc.call_type_id
1828 left join target_groups as tg
1829 on tgc.target_group_id = tg.id
1830 and tg.active = 1
1831 order by null;
1832
1833
1834INFO : (02-27 08:17:28) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1835INFO : (02-27 08:17:28) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1836 (150.1ms) insert into buffer_linkage_call_type_targets_793447700 (
1837 linkage_instance_id,
1838 calendar_schedule_id,
1839 period_offset,
1840 assignment_id,
1841 assignment_type,
1842 weekday_targets,
1843 weekend_targets
1844 )
1845 select s.linkage_instance_id,
1846 s.calendar_schedule_id,
1847 -1 as period_offset,
1848 ta.assignment_id,
1849 ta.assignment_type,
1850 sum(if(s.day_of_week_mask & 62 > 0, ci.calls, 0)) as weekday_targets,
1851 sum(if(s.day_of_week_mask & 65 > 0, ci.calls, 0)) as weekend_targets
1852 from buffer_schedules_793447700 as s
1853 inner join buffer_target_assignments_793447700 as ta
1854 on s.call_type_id = ta.call_type_id
1855 inner join buffer_counter_increments_793447700 as ci
1856 on s.id = ci.schedule_id
1857 and ci.affects_cumulative_targets = 1
1858 where
1859 s.linkage_instance_id >= 0
1860 and s.staff_id is null
1861 group by
1862 s.linkage_instance_id,
1863 s.calendar_schedule_id,
1864 ta.assignment_id,
1865 ta.assignment_type
1866 order by null
1867
1868INFO : (02-27 08:17:28) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0703
1869INFO : (02-27 08:17:28) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0704
1870INFO : (02-27 08:17:28) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0705
1871 (147.4ms) insert into buffer_linkage_call_type_assignments_793447700 (
1872 linkage_instance_id,
1873 calendar_schedule_id,
1874 period_offset,
1875 call_type_id,
1876 weekday_assignments,
1877 max_weekday_assignments,
1878 weekend_assignments,
1879 max_weekend_assignments
1880 )
1881 select
1882 s.linkage_instance_id,
1883 s.calendar_schedule_id,
1884 s.period_offset,
1885 s.call_type_id,
1886 sum(if(s.day_of_week_mask & 62 > 0, 1, 0)) as weekday_assignments,
1887 ct.max_weekday_assignments,
1888 sum(if(s.day_of_week_mask & 65 > 0, 1, 0)) as weekend_assignments,
1889 ct.max_weekend_assignments
1890 from buffer_schedules_793447700 as s
1891 inner join call_types as ct
1892 on s.call_type_id = ct.id
1893 and ct.active = 1
1894 where s.linkage_instance_id >= 0
1895 and s.staff_id is null
1896 group by
1897 s.linkage_instance_id,
1898 s.calendar_schedule_id,
1899 s.period_offset,
1900 s.call_type_id
1901 order by null
1902
1903INFO : (02-27 08:17:28) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0706
1904 (286.8ms) insert into buffer_call_type_assignments_793447700 (
1905 calendar_schedule_id,
1906 period_offset,
1907 staff_id,
1908 call_type_id,
1909 weekday_assignments,
1910 weekend_assignments
1911 )
1912 select straight_join
1913 p.calendar_schedule_id,
1914 p.period_offset,
1915 sc.staff_id,
1916 sc.call_type_id,
1917 sum(if((dayofweek(sc.schedule_on) - 1) not in (0, 6), 1, 0)) as weekday_assignments,
1918 sum(if((dayofweek(sc.schedule_on) - 1) in (0, 6), 1, 0)) as weekend_assignments
1919 from buffer_periods_793447700 as p
1920 inner join schedules as sc
1921 on sc.client_id = 319
1922 and sc.schedule_on between p.start_date and p.end_date
1923 and sc.staff_id is not null
1924 inner join call_types as ct
1925 on sc.call_type_id = ct.id
1926 and p.calendar_schedule_id = ct.calendar_schedule_id
1927 and ct.active = 1
1928 where p.should_be_evaluated = 1
1929 group by
1930 p.calendar_schedule_id,
1931 p.period_offset,
1932 sc.staff_id,
1933 sc.call_type_id
1934 order by null
1935
1936INFO : (02-27 08:17:28) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0707
1937INFO : (02-27 08:17:28) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query0708
1938 (151.2ms) insert into buffer_linkage_assigned_days_793447700 (
1939 linkage_instance_id,
1940 period_offset,
1941 week_number,
1942 schedule_on,
1943 is_weekend
1944 )
1945 select
1946 sc.linkage_instance_id,
1947 sc.period_offset,
1948 sc.week_number,
1949 sc.schedule_on,
1950 if(dayofweek(sc.schedule_on) not in (7, 1), 0, 1) as is_weekend
1951 from
1952 buffer_schedules_793447700 as sc
1953 inner join call_types as ct
1954 on sc.call_type_id = ct.id
1955 and ct.unassigned_day = 0
1956 where
1957 sc.linkage_instance_id >= 0
1958 and sc.staff_id is null
1959 group by
1960 sc.linkage_instance_id,
1961 sc.period_offset,
1962 sc.week_number,
1963 sc.schedule_on,
1964 is_weekend
1965 order by null
1966
1967INFO : (02-27 08:17:28) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query0709
1968INFO : (02-27 08:17:28) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL
1969 (146.6ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
1970 insert into buffer_assignment_limit_call_types_793447700 (
1971 assignment_limit_id, call_type_id
1972 )
1973 select al.id as assignment_limit_id, ct.id as call_type_id
1974 from assignment_limits as al
1975 inner join assignment_limit_call_types as alct
1976 on al.id = alct.assignment_limit_id
1977 inner join call_types as ct
1978 on alct.call_type_id = ct.id
1979 and ct.active = 1
1980 where al.client_id = 319
1981 and al.active = 1
1982 union
1983 select al.id as assignment_limit_id, ct.id as call_type_id
1984 from assignment_limits as al
1985 inner join call_types as ct
1986 on al.client_id = ct.client_id
1987 and al.calendar_schedule_id = ct.calendar_schedule_id
1988 and ct.active = 1
1989 where al.client_id = 319
1990 and al.active = 1
1991 and al.all_current_schedule_assignments = 1
1992 union
1993 select al.id as assignment_limit_id, ct.id as call_type_id
1994 from assignment_limits as al
1995 inner join call_types as ct
1996 on al.client_id = ct.client_id
1997 and al.calendar_schedule_id != ct.calendar_schedule_id
1998 and ct.active = 1
1999 where al.client_id = 319
2000 and al.active = 1
2001 and al.all_other_schedule_assignments = 1
2002 order by null
2003
2004 (145.8ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
2005 insert into buffer_assignment_limit_staff_types_793447700 (
2006 assignment_limit_id, staff_type_id
2007 )
2008 select al.id as assignment_limit_id, st.id as staff_type_id
2009 from assignment_limits as al
2010 inner join assignment_limit_staff_types as alst
2011 on al.id = alst.assignment_limit_id
2012 inner join staff_types as st
2013 on alst.staff_type_id = st.id
2014 and st.active = 1
2015 where al.client_id = 319
2016 and al.active = 1
2017 union
2018 select al.id as assignment_limit_id, st.id as staff_type_id
2019 from assignment_limits as al
2020 inner join staff_types as st
2021 on al.client_id = st.client_id
2022 and st.active = 1
2023 where al.client_id = 319
2024 and al.active = 1
2025 and al.all_staff_types = 1
2026 order by null
2027
2028INFO : (02-27 08:17:29) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
2029 (147.8ms) /* AssignmentLimitsPerPeriod */
2030 insert into buffer_assignment_limit_periods_793447700 (
2031 assignment_limit_id, period_id, start_date, end_date
2032 )
2033 select
2034 al.id as assignment_limit_id,
2035 if(
2036 bs.blocks_type = 0,
2037 if(
2038 (month(b.start_date) - pt.begin_period) >= 0,
2039 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type + 1,
2040 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type + 1
2041 ),
2042 b.display_order
2043 ) as period_id,
2044 min(b.start_date) as start_date,
2045 max(b.end_date) as end_date
2046 from assignment_limits as al
2047 inner join calendar_schedules as cs
2048 on al.calendar_schedule_id = cs.id
2049 inner join block_schedules as bs
2050 on cs.block_schedule_id = bs.id
2051 inner join blocks as b
2052 on bs.id = b.block_schedule_id
2053 and b.end_date >= date_add(now(), interval -24 month)
2054 inner join population_timeframes as pt
2055 on pt.client_id = 319
2056 and al.timeframe_type = pt.timeframe_type
2057 where al.client_id = 319
2058 and al.active = 1
2059 and al.max_per_period_limit is not null
2060 group by al.id, period_id
2061 order by null
2062
2063 (144.4ms) /* AssignmentLimitsPerPeriod */
2064 insert into buffer_linkage_assignments_count_periods_793447700 (
2065 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2066 )
2067 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date,
2068 count(distinct sc.id) as assignments_count
2069 from buffer_schedules_793447700 as sc
2070 inner join buffer_assignment_limit_call_types_793447700 as alct
2071 on sc.call_type_id = alct.call_type_id
2072 inner join assignment_limits as al
2073 on alct.assignment_limit_id = al.id
2074 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2075 and al.max_per_period_limit is not null
2076 inner join buffer_assignment_limit_periods_793447700 as alp
2077 on alct.assignment_limit_id = alp.assignment_limit_id
2078 and sc.schedule_on between alp.start_date and alp.end_date
2079 where sc.linkage_instance_id >= 0
2080 and sc.staff_id is null
2081 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date
2082 order by null
2083
2084INFO : (02-27 08:17:29) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
2085 (145.0ms) /* AssignmentLimitsPerWeek */
2086 insert into buffer_linkage_assignments_count_weeks_793447700 (
2087 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2088 )
2089 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2090 if(
2091 cl.start_of_week = 0,
2092 date_add(sc.schedule_on, interval (1 - dayofweek(sc.schedule_on)) day),
2093 date_add(sc.schedule_on, interval (0 - weekday(sc.schedule_on)) day)
2094 ) as start_date,
2095 if(
2096 cl.start_of_week = 0,
2097 date_add(sc.schedule_on, interval (7 - dayofweek(sc.schedule_on)) day),
2098 date_add(sc.schedule_on, interval (6 - weekday(sc.schedule_on)) day)
2099 ) as end_date,
2100 count(distinct sc.id) as assignments_count
2101 from buffer_schedules_793447700 as sc
2102 inner join buffer_assignment_limit_call_types_793447700 as alct
2103 on sc.call_type_id = alct.call_type_id
2104 inner join assignment_limits as al
2105 on alct.assignment_limit_id = al.id
2106 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2107 and al.max_weekly_limit is not null
2108 inner join clients as cl
2109 on cl.id = 319
2110 where sc.linkage_instance_id >= 0
2111 and sc.staff_id is null
2112 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2113 order by null
2114
2115INFO : (02-27 08:17:29) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
2116 (144.5ms) /* AssignmentLimitsRolling */
2117 insert into buffer_linkage_assignments_count_rolling_793447700 (
2118 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2119 )
2120 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2121 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as start_date,
2122 date_add(sc.schedule_on, interval (al.max_rolling_period - ns.item_number) day) as end_date,
2123 count(distinct sc.id) as assignments_count
2124 from buffer_schedules_793447700 as sc
2125 inner join buffer_assignment_limit_call_types_793447700 as alct
2126 on sc.call_type_id = alct.call_type_id
2127 inner join assignment_limits as al
2128 on alct.assignment_limit_id = al.id
2129 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2130 and al.max_rolling_period is not null
2131 and al.max_rolling_limit is not null
2132 inner join numeric_sequence as ns
2133 on ns.item_number between 1 and al.max_rolling_period
2134 where sc.linkage_instance_id >= 0
2135 and sc.staff_id is null
2136 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2137 order by null
2138
2139INFO : (02-27 08:17:29) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
2140 (144.2ms) /* AssignmentLimitsConsecutive */
2141 insert into buffer_linkage_assignments_count_consecutive_793447700 (
2142 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date
2143 )
2144 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2145 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as start_date,
2146 date_add(sc.schedule_on, interval ((al.max_consecutive_limit + 1) - ns.item_number) day) as end_date
2147 from buffer_schedules_793447700 as sc
2148 inner join buffer_assignment_limit_call_types_793447700 as alct
2149 on sc.call_type_id = alct.call_type_id
2150 inner join assignment_limits as al
2151 on alct.assignment_limit_id = al.id
2152 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2153 and al.max_consecutive_limit is not null
2154 inner join numeric_sequence as ns
2155 on ns.item_number between 1 and (al.max_consecutive_limit + 1)
2156 where sc.linkage_instance_id >= 0
2157 and sc.staff_id is null
2158 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2159 order by null
2160
2161INFO : (02-27 08:17:29) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query08
2162 (144.0ms) insert into buffer_staff_daily_counts_793447700 (
2163 staff_id, schedule_on, linkage_instance_id, slots_count
2164 )
2165 select staff_id, schedule_on, null as linkage_instance_id,
2166 count(1) as slots_count
2167 from buffer_schedules_793447700
2168 where staff_id is not null
2169 and call_type_id > 0 /* VC */
2170 group by staff_id, schedule_on
2171 order by null
2172
2173 (144.9ms) insert into buffer_staff_daily_counts_793447700 (
2174 staff_id, schedule_on, linkage_instance_id, slots_count
2175 )
2176 select staff_id, schedule_on, linkage_instance_id,
2177 count(1) as slots_count
2178 from buffer_schedules_793447700
2179 where staff_id is not null
2180 and call_type_id > 0 /* VC */
2181 group by staff_id, schedule_on, linkage_instance_id
2182 order by null
2183
2184INFO : (02-27 08:17:30) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query085
2185 (265.1ms) insert into buffer_staff_specializations_793447700 (
2186 staff_id, specialization_id
2187 )
2188 select staff_id, specialization_id
2189 from view_staff_specializations
2190 where client_id = 319
2191 order by null
2192
2193 (148.4ms) insert into buffer_specialization_daily_counts_793447700 (
2194 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2195 )
2196 select sp.id as specialization_id,
2197 null as staff_id,
2198 bs_1.schedule_on,
2199 count(distinct st_sp.staff_id) as staffs_scheduled,
2200 sp.max_scheduled,
2201 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
2202 from (
2203 select schedule_on, day_of_week_mask
2204 from buffer_schedules_793447700
2205 where should_be_evaluated = 1
2206 group by schedule_on, day_of_week_mask
2207 order by null
2208 ) as bs_1
2209 inner join specializations as sp
2210 on sp.client_id = 319
2211 and sp.active = 1
2212 inner join buffer_schedules_793447700 as bs_2
2213 on bs_1.schedule_on = bs_2.schedule_on
2214 and bs_2.call_type_id > 0 /* is not VC */
2215 inner join max_applicable_call_types as mact
2216 on sp.id = mact.specialization_id
2217 and bs_2.call_type_id = mact.call_type_id
2218 left join buffer_staff_specializations_793447700 as st_sp
2219 on bs_2.staff_id = st_sp.staff_id
2220 and sp.id = st_sp.specialization_id
2221 group by sp.id, bs_1.schedule_on, sp.max_scheduled
2222 order by null
2223
2224 (159.3ms) insert into buffer_specialization_daily_counts_793447700 (
2225 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2226 )
2227 select sp.id as specialization_id,
2228 st_sp.staff_id,
2229 bs_1.schedule_on,
2230 1 as staffs_scheduled,
2231 sp.max_scheduled,
2232 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
2233 from (
2234 select schedule_on, day_of_week_mask
2235 from buffer_schedules_793447700
2236 where should_be_evaluated = 1
2237 group by schedule_on, day_of_week_mask
2238 order by null
2239 ) as bs_1
2240 inner join specializations as sp
2241 on sp.client_id = 319
2242 and sp.active = 1
2243 inner join buffer_schedules_793447700 as bs_2
2244 on bs_1.schedule_on = bs_2.schedule_on
2245 and bs_2.call_type_id > 0 /* is not VC */
2246 inner join max_applicable_call_types as mact
2247 on sp.id = mact.specialization_id
2248 and bs_2.call_type_id = mact.call_type_id
2249 inner join buffer_staff_specializations_793447700 as st_sp
2250 on bs_2.staff_id = st_sp.staff_id
2251 and sp.id = st_sp.specialization_id
2252 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
2253 order by null
2254
2255INFO : (02-27 08:17:30) --> fill: 34 - Scheduler::AutoPopulate::Queries::Query09
2256 (145.8ms) truncate table buffer_location_privileges_793447700
2257
2258 (174.2ms) insert into buffer_location_privileges_793447700 (
2259 staff_id,
2260 location_id,
2261 begin_date,
2262 end_date
2263 )
2264 select
2265 s.id as staff_id,
2266 l.location_id,
2267 '0000-00-00' as begin_date,
2268 '9999-12-31' as end_date
2269 from (
2270 select l.id as location_id
2271 from
2272 buffer_schedules_793447700 as sc
2273 inner join call_types as ct
2274 on sc.call_type_id = ct.id
2275 and ct.active = 1
2276 inner join locations as l
2277 on ct.location_id = l.id
2278 and l.active = 1
2279 where sc.linkage_instance_id >= 0
2280 and sc.should_be_evaluated = 1
2281 group by l.id
2282 order by null
2283 ) as l
2284 inner join staffs as s
2285 on s.client_id = 319
2286 and s.active = 1
2287 and s.take_call = 1
2288 left join location_privileges as lp
2289 on s.id = lp.staff_id
2290 where lp.id is null
2291
2292 union
2293
2294 select
2295 lp.staff_id,
2296 lp.location_id,
2297 lp.begin_date,
2298 lp.end_date
2299 from (
2300 select l.id as location_id
2301 from
2302 buffer_schedules_793447700 as sc
2303 inner join call_types as ct
2304 on sc.call_type_id = ct.id
2305 and ct.active = 1
2306 inner join locations as l
2307 on ct.location_id = l.id
2308 and l.active = 1
2309 where sc.linkage_instance_id >= 0
2310 and sc.should_be_evaluated = 1
2311 group by l.id
2312 order by null
2313 ) as l
2314 inner join location_privileges as lp
2315 on l.location_id = lp.location_id
2316 order by null;
2317
2318 (269.1ms) insert into buffer_static_availabilities_793447700 (
2319 schedule_id,
2320 staff_id,
2321 staff_type_id,
2322 is_primary
2323 )
2324 select
2325 schedules.id as schedule_id,
2326 staffs.id as staff_id,
2327 assign_staff_types.staff_type_id,
2328 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
2329 from
2330 buffer_schedules_793447700 as schedules
2331 /* AvailabilityRule */
2332 inner join call_types
2333 on schedules.call_type_id = call_types.id
2334 and call_types.active = 1
2335 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
2336 inner join staff_call_types
2337 on call_types.id = staff_call_types.call_type_id
2338 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
2339 inner join staffs
2340 on staff_call_types.staff_id = staffs.id
2341 and staffs.active = 1
2342 and staffs.take_call = 1
2343 and staffs.auto_populatable = 1
2344 inner join buffer_location_privileges_793447700 as location_privileges
2345 on staffs.id = location_privileges.staff_id
2346 and call_types.location_id = location_privileges.location_id
2347 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
2348 inner join assign_staff_types
2349 on staffs.id = assign_staff_types.staff_id
2350 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
2351 left join eligible_staff_types
2352 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
2353 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
2354 left join staff_rotations
2355 on staffs.id = staff_rotations.staff_id
2356 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
2357 left join daily_rotations
2358 on staffs.id = daily_rotations.staff_id
2359 and schedules.schedule_on = daily_rotations.schedule_on
2360 left join rotations
2361 on rotations.id = ifnull(daily_rotations.rotation_id, staff_rotations.rotation_id)
2362 and rotations.active = 1
2363 left join rotation_call_types
2364 on rotations.id = rotation_call_types.rotation_id
2365 and staff_call_types.call_type_id = rotation_call_types.call_type_id
2366 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
2367 /* RequiredRotationsRule */
2368 left join required_rotations
2369 on staff_call_types.call_type_id = required_rotations.call_type_id
2370 left join staff_rotations as staff_rotations2
2371 on staffs.id = staff_rotations2.staff_id
2372 and required_rotations.rotation_id = staff_rotations2.rotation_id
2373 and schedules.schedule_on >= staff_rotations2.start_date
2374 /* NoCallRequestsHiddenHardRule */
2375 left join request_settings
2376 on assign_staff_types.staff_type_id = request_settings.staff_type_id
2377 left join requests
2378 on schedules.schedule_on = requests.schedule_on
2379 and staffs.id = requests.staff_id
2380 and requests.request_type = 0 /* type = no_call */
2381 and requests.status in (1, 3) /* status = approved or fulfilled */
2382 and call_types.is_no_call_requestable = 1
2383 left join request_call_types
2384 on requests.id = request_call_types.request_id
2385 and schedules.call_type_id = request_call_types.call_type_id
2386 where
2387 schedules.linkage_instance_id >= 0
2388 and schedules.should_be_evaluated = 1
2389 and (
2390 call_types.all_staff_types = 1
2391 or eligible_staff_types.id is not null
2392 )
2393 and (
2394 rotations.id is null
2395 or rotation_call_types.id is not null
2396 )
2397 and (
2398 requests.id is null
2399 or (
2400 requests.all_call_types = 1
2401 and request_settings.call_only_all_call_switch = 1 /* calls only */
2402 and call_types.assignment_type = 1 /* clinical */
2403 )
2404 or (
2405 requests.all_call_types != 1
2406 and request_call_types.id is null
2407 )
2408 )
2409 group by
2410 schedules.id,
2411 staffs.id,
2412 assign_staff_types.staff_type_id,
2413 is_primary
2414 having
2415 sum(if(required_rotations.id is not null and staff_rotations2.id is null, 1, 0)) = 0 /* RequiredRotationsRule */
2416 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
2417 order by null
2418
2419INFO : (02-27 08:17:31) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query092
2420 (148.0ms) /* RotationPostCallRule */
2421 /* Exclude people and slots when a post-call is not allowed on the first day of a rotation */
2422 delete sa
2423 from buffer_static_availabilities_793447700 as sa
2424 inner join buffer_schedules_793447700 as sc
2425 on sa.schedule_id = sc.id
2426 inner join call_types as ct
2427 on sc.call_type_id = ct.id
2428 and ct.assignment_type = 0 /* call */
2429 inner join staff_rotations as sr
2430 on sa.staff_id = sr.staff_id
2431 and date_add(sc.schedule_on, interval 1 day) = sr.start_date
2432 inner join rotations as r
2433 on sr.rotation_id = r.id
2434 and r.post_call_allowed = 0
2435
2436INFO : (02-27 08:17:31) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query093
2437INFO : (02-27 08:17:31) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query095
2438 (160.5ms) insert into buffer_negative_relations_793447700 (
2439 schedule_id,
2440 affected_by_schedule_id,
2441 staff_id
2442 )
2443 select
2444 b_schedules.id as schedule_id,
2445 b_schedules_negative_link.id as affected_by_schedule_id,
2446 b_schedules_negative_link.staff_id
2447 from
2448 buffer_schedules_793447700 as b_schedules
2449 /* NegativeLinkRule */
2450 inner join negative_links_influences
2451 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2452 and b_schedules.call_type_id = negative_links_influences.source_id
2453 and negative_links_influences.source_type = 'CallType'
2454 and negative_links_influences.target_type = 'CallType'
2455 inner join buffer_schedules_793447700 as b_schedules_negative_link
2456 on date_add(
2457 b_schedules.schedule_on,
2458 interval negative_links_influences.offset day
2459 ) = b_schedules_negative_link.schedule_on
2460 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
2461 and b_schedules.id != b_schedules_negative_link.id
2462 where
2463 b_schedules.linkage_instance_id >= 0
2464 and b_schedules.should_be_evaluated = 1
2465 order by null
2466
2467 (148.8ms) insert into buffer_negative_relations_793447700 (
2468 schedule_id,
2469 affected_by_schedule_id,
2470 staff_id
2471 )
2472 select
2473 b_schedules.id as schedule_id,
2474 b_schedules_negative_link.id as affected_by_schedule_id,
2475 b_schedules_negative_link.staff_id
2476 from
2477 buffer_schedules_793447700 as b_schedules
2478 /* NegativeLinkRule */
2479 inner join negative_links_influences
2480 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2481 and b_schedules.call_type_id = negative_links_influences.source_id
2482 and negative_links_influences.source_type = 'CallType'
2483 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
2484 inner join buffer_schedules_793447700 as b_schedules_negative_link
2485 on date_add(
2486 b_schedules.schedule_on,
2487 interval negative_links_influences.offset day
2488 ) = b_schedules_negative_link.schedule_on
2489 and (-1 * ifnull(negative_links_influences.target_id, 0)) = b_schedules_negative_link.call_type_id
2490 and b_schedules.id != b_schedules_negative_link.id
2491 where
2492 b_schedules.linkage_instance_id >= 0
2493 and b_schedules.should_be_evaluated = 1
2494 order by null
2495
2496 (153.0ms) delete from buffer_schedules_793447700
2497 where call_type_id <= 0 /* vacations and non-clinicals */
2498
2499INFO : (02-27 08:17:32) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query097
2500 (164.1ms) insert into buffer_staff_assigned_days_793447700 (
2501 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
2502 )
2503 select staffs.staff_id,
2504 if(days.within_period = 1, days.period_offset, null) as period_offset,
2505 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
2506 days.schedule_on,
2507 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
2508 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
2509 from (
2510 select distinct staff_id
2511 from buffer_static_availabilities_793447700
2512 order by null
2513 ) as staffs
2514 inner join (
2515 select p.period_offset,
2516 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,
2517 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
2518 from buffer_periods_793447700 as p
2519 inner join (
2520 select 0 as digit union select 1 union select 2 union select 3 union select 4
2521 union select 5 union select 6 union select 7 union select 8 union select 9
2522 ) as digits_1
2523 inner join (
2524 select 0 as digit union select 1 union select 2 union select 3 union select 4
2525 union select 5 union select 6 union select 7 union select 8 union select 9
2526 ) as digits_2
2527 inner join (
2528 select 0 as digit union select 1 union select 2 union select 3 union select 4
2529 union select 5 union select 6 union select 7 union select 8 union select 9
2530 ) as digits_3
2531 where p.calendar_schedule_id = 1381
2532 and p.should_be_evaluated = 1
2533 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
2534 order by null
2535 ) as days
2536 inner join (
2537 select min(start_date) as start_date, max(end_date) as end_date
2538 from buffer_periods_793447700
2539 where calendar_schedule_id = 1381
2540 and should_be_evaluated = 1
2541 order by null
2542 ) as limits
2543 inner join clients
2544 on clients.id = 319
2545 left join (
2546 select sc.staff_id, sc.schedule_on
2547 from buffer_periods_793447700 as p
2548 inner join schedules as sc
2549 on sc.client_id = 319
2550 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2551 inner join call_types as ct
2552 on sc.call_type_id = ct.id
2553 and ct.unassigned_day = 0
2554 where p.calendar_schedule_id = 1381
2555 and p.should_be_evaluated = 1
2556 group by sc.staff_id, sc.schedule_on
2557 order by null
2558 ) as schedules
2559 on staffs.staff_id = schedules.staff_id
2560 and days.schedule_on = schedules.schedule_on
2561 left join (
2562 select v.staff_id, v.schedule_on
2563 from buffer_periods_793447700 as p
2564 inner join vacations as v
2565 on v.client_id = 319
2566 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2567 and v.status IN (3)
2568 left join non_clinical_types as nct
2569 on v.non_clinical_type_id = nct.id
2570 and nct.active = 1
2571 and nct.unassigned_day = 0
2572 where p.calendar_schedule_id = 1381
2573 and p.should_be_evaluated = 1
2574 and (
2575 v.vacation_type = 0
2576 or nct.id is not null
2577 )
2578 group by v.staff_id, v.schedule_on
2579 order by null
2580 ) as vacations
2581 on staffs.staff_id = vacations.staff_id
2582 and days.schedule_on = vacations.schedule_on
2583 where days.within_period = 1
2584 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
2585 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
2586 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
2587 )
2588 order by null
2589
2590INFO : (02-27 08:17:32) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query098
2591 (150.9ms) insert into buffer_combined_availabilities_793447700 (
2592 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
2593 )
2594 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
2595 sc.linkage_instance_id, 0 as has_violations
2596 from buffer_schedules_793447700 as sc
2597 /* Static availability */
2598 inner join buffer_static_availabilities_793447700 as sa
2599 on sc.id = sa.schedule_id
2600 inner join buffer_linkages_793447700 as l
2601 on sc.linkage_instance_id = l.linkage_instance_id
2602 where sc.should_be_evaluated = 1
2603 and l.has_conflicts = 0
2604
2605 (144.3ms) insert into buffer_schedule_linkages_793447700 (
2606 staff_id, linkage_instance_id, slots_count
2607 )
2608 select staff_id, linkage_instance_id, count(1) as slots_count
2609 from buffer_schedules_793447700 use index (ix_buffer_schedules_composite_2)
2610 where staff_id is not null
2611 and linkage_instance_id >= 0
2612 group by staff_id, linkage_instance_id
2613 order by null
2614
2615INFO : (02-27 08:17:32) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
2616 (146.3ms) /* AssignmentLimitsPerPeriod */
2617 insert into buffer_staff_assignments_count_periods_793447700 (
2618 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2619 )
2620 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2621 count(distinct sc.id) as assignments_count
2622 from (
2623 select linkage_instance_id, assignment_limit_id, start_date, end_date
2624 from buffer_linkage_assignments_count_periods_793447700
2625 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2626 order by null
2627 ) as lac
2628 inner join (
2629 select linkage_instance_id, staff_id
2630 from buffer_combined_availabilities_793447700
2631 group by linkage_instance_id, staff_id
2632 order by null
2633 ) as ca
2634 on lac.linkage_instance_id = ca.linkage_instance_id
2635 inner join assignment_limits as al
2636 on lac.assignment_limit_id = al.id
2637 inner join schedules as sc
2638 on ca.staff_id = sc.staff_id
2639 and sc.schedule_on between lac.start_date and lac.end_date
2640 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2641 inner join buffer_assignment_limit_call_types_793447700 as alct
2642 on lac.assignment_limit_id = alct.assignment_limit_id
2643 and sc.call_type_id = alct.call_type_id
2644 inner join assign_staff_types as ast
2645 on sc.staff_id = ast.staff_id
2646 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2647 inner join buffer_assignment_limit_staff_types_793447700 as alst
2648 on lac.assignment_limit_id = alst.assignment_limit_id
2649 and ast.staff_type_id = alst.staff_type_id
2650 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2651 order by null
2652
2653INFO : (02-27 08:17:32) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
2654 (145.0ms) /* AssignmentLimitsPerWeek */
2655 insert into buffer_staff_assignments_count_weeks_793447700 (
2656 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2657 )
2658 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2659 count(distinct sc.id) as assignments_count
2660 from (
2661 select linkage_instance_id, assignment_limit_id, start_date, end_date
2662 from buffer_linkage_assignments_count_weeks_793447700
2663 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2664 order by null
2665 ) as lac
2666 inner join (
2667 select linkage_instance_id, staff_id
2668 from buffer_combined_availabilities_793447700
2669 group by linkage_instance_id, staff_id
2670 order by null
2671 ) as ca
2672 on lac.linkage_instance_id = ca.linkage_instance_id
2673 inner join assignment_limits as al
2674 on lac.assignment_limit_id = al.id
2675 inner join schedules as sc
2676 on ca.staff_id = sc.staff_id
2677 and sc.schedule_on between lac.start_date and lac.end_date
2678 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2679 inner join buffer_assignment_limit_call_types_793447700 as alct
2680 on lac.assignment_limit_id = alct.assignment_limit_id
2681 and sc.call_type_id = alct.call_type_id
2682 inner join assign_staff_types as ast
2683 on sc.staff_id = ast.staff_id
2684 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2685 inner join buffer_assignment_limit_staff_types_793447700 as alst
2686 on lac.assignment_limit_id = alst.assignment_limit_id
2687 and ast.staff_type_id = alst.staff_type_id
2688 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2689 order by null
2690
2691INFO : (02-27 08:17:32) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
2692 (144.5ms) /* AssignmentLimitsRolling */
2693 insert into buffer_staff_assignments_count_rolling_793447700 (
2694 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2695 )
2696 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2697 count(distinct sc.id) as assignments_count
2698 from (
2699 select linkage_instance_id, assignment_limit_id, start_date, end_date
2700 from buffer_linkage_assignments_count_rolling_793447700
2701 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2702 order by null
2703 ) as lac
2704 inner join (
2705 select linkage_instance_id, staff_id
2706 from buffer_combined_availabilities_793447700
2707 group by linkage_instance_id, staff_id
2708 order by null
2709 ) as ca
2710 on lac.linkage_instance_id = ca.linkage_instance_id
2711 inner join assignment_limits as al
2712 on lac.assignment_limit_id = al.id
2713 inner join schedules as sc
2714 on ca.staff_id = sc.staff_id
2715 and sc.schedule_on between lac.start_date and lac.end_date
2716 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2717 inner join buffer_assignment_limit_call_types_793447700 as alct
2718 on lac.assignment_limit_id = alct.assignment_limit_id
2719 and sc.call_type_id = alct.call_type_id
2720 inner join assign_staff_types as ast
2721 on sc.staff_id = ast.staff_id
2722 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2723 inner join buffer_assignment_limit_staff_types_793447700 as alst
2724 on lac.assignment_limit_id = alst.assignment_limit_id
2725 and ast.staff_type_id = alst.staff_type_id
2726 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2727 order by null
2728
2729INFO : (02-27 08:17:32) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
2730 (145.1ms) /* AssignmentLimitsConsecutive */
2731 insert into buffer_staff_assignments_count_consecutive_793447700 (
2732 assignment_limit_id, staff_id, schedule_on, start_date, end_date
2733 )
2734 select straight_join lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2735 from (
2736 select linkage_instance_id, assignment_limit_id, start_date, end_date
2737 from buffer_linkage_assignments_count_consecutive_793447700
2738 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2739 order by null
2740 ) as lac
2741 inner join (
2742 select linkage_instance_id, staff_id
2743 from buffer_combined_availabilities_793447700
2744 group by linkage_instance_id, staff_id
2745 order by null
2746 ) as ca
2747 on lac.linkage_instance_id = ca.linkage_instance_id
2748 inner join assignment_limits as al
2749 on lac.assignment_limit_id = al.id
2750 inner join schedules as sc
2751 on ca.staff_id = sc.staff_id
2752 and sc.schedule_on between lac.start_date and lac.end_date
2753 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2754 inner join buffer_assignment_limit_call_types_793447700 as alct
2755 on lac.assignment_limit_id = alct.assignment_limit_id
2756 and sc.call_type_id = alct.call_type_id
2757 inner join assign_staff_types as ast
2758 on sc.staff_id = ast.staff_id
2759 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2760 inner join buffer_assignment_limit_staff_types_793447700 as alst
2761 on lac.assignment_limit_id = alst.assignment_limit_id
2762 and ast.staff_type_id = alst.staff_type_id
2763 group by lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2764 order by null
2765
2766INFO : (02-27 08:17:33) --> fill: 44 - Scheduler::AutoPopulate::Queries::Query09_09_RP
2767INFO : (02-27 08:17:33) --> fill: 45 - Scheduler::AutoPopulate::Queries::Query10
2768 RuleSet Load (143.5ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1527 LIMIT 1
2769 Client Load (146.8ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
2770 (146.0ms) /* SpecializationOverlapRule */
2771 delete ca
2772 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
2773 inner join buffer_schedules_793447700 as sc
2774 on ca.schedule_id = sc.id
2775 inner join buffer_staff_specializations_793447700 as ssp
2776 on ca.staff_id = ssp.staff_id
2777 inner join max_applicable_call_types as mact
2778 on ssp.specialization_id = mact.specialization_id
2779 and sc.call_type_id = mact.call_type_id
2780 inner join buffer_specialization_daily_counts_793447700 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2781 on ssp.specialization_id = sdc.specialization_id
2782 and sc.schedule_on = sdc.schedule_on
2783 and sdc.staff_id is null
2784 left join buffer_specialization_daily_counts_793447700 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2785 on ssp.specialization_id = sdc_2.specialization_id
2786 and sc.schedule_on = sdc_2.schedule_on
2787 and ca.staff_id = sdc_2.staff_id
2788 where sdc.should_be_evaluated = 1
2789 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2790
2791 (144.5ms) delete ca
2792 from buffer_combined_availabilities_793447700 as ca
2793 inner join (
2794 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2795 sum(lac.assignments_count) as assignments_count
2796 from (
2797 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2798 from buffer_combined_availabilities_793447700 as ca
2799 inner join buffer_schedules_793447700 as sc
2800 on ca.schedule_id = sc.id
2801 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2802 order by null
2803 ) as ca_sc
2804 inner join buffer_linkage_assignments_count_weeks_793447700 as lac
2805 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2806 and ca_sc.schedule_on = lac.schedule_on
2807 inner join buffer_assignment_limit_staff_types_793447700 as alst
2808 on lac.assignment_limit_id = alst.assignment_limit_id
2809 and ca_sc.staff_type_id = alst.staff_type_id
2810 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2811 order by null
2812 ) as lac
2813 on ca.linkage_instance_id = lac.linkage_instance_id
2814 and ca.staff_id = lac.staff_id
2815 inner join assignment_limits as al
2816 on lac.assignment_limit_id = al.id
2817 left join buffer_staff_assignments_count_weeks_793447700 as sac
2818 on lac.assignment_limit_id = sac.assignment_limit_id
2819 and lac.staff_id = sac.staff_id
2820 and lac.start_date = sac.start_date
2821 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
2822
2823 (159.2ms) delete ca
2824 from buffer_combined_availabilities_793447700 as ca
2825 inner join (
2826 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2827 sum(lac.assignments_count) as assignments_count
2828 from (
2829 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2830 from buffer_combined_availabilities_793447700 as ca
2831 inner join buffer_schedules_793447700 as sc
2832 on ca.schedule_id = sc.id
2833 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2834 order by null
2835 ) as ca_sc
2836 inner join buffer_linkage_assignments_count_periods_793447700 as lac
2837 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2838 and ca_sc.schedule_on = lac.schedule_on
2839 inner join buffer_assignment_limit_staff_types_793447700 as alst
2840 on lac.assignment_limit_id = alst.assignment_limit_id
2841 and ca_sc.staff_type_id = alst.staff_type_id
2842 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2843 order by null
2844 ) as lac
2845 on ca.linkage_instance_id = lac.linkage_instance_id
2846 and ca.staff_id = lac.staff_id
2847 inner join assignment_limits as al
2848 on lac.assignment_limit_id = al.id
2849 left join buffer_staff_assignments_count_periods_793447700 as sac
2850 on lac.assignment_limit_id = sac.assignment_limit_id
2851 and lac.staff_id = sac.staff_id
2852 and lac.start_date = sac.start_date
2853 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
2854
2855 (144.6ms) delete ca
2856 from buffer_combined_availabilities_793447700 as ca
2857 inner join (
2858 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2859 sum(lac.assignments_count) as assignments_count
2860 from (
2861 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2862 from buffer_combined_availabilities_793447700 as ca
2863 inner join buffer_schedules_793447700 as sc
2864 on ca.schedule_id = sc.id
2865 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2866 order by null
2867 ) as ca_sc
2868 inner join buffer_linkage_assignments_count_rolling_793447700 as lac
2869 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2870 and ca_sc.schedule_on = lac.schedule_on
2871 inner join buffer_assignment_limit_staff_types_793447700 as alst
2872 on lac.assignment_limit_id = alst.assignment_limit_id
2873 and ca_sc.staff_type_id = alst.staff_type_id
2874 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2875 order by null
2876 ) as lac
2877 on ca.linkage_instance_id = lac.linkage_instance_id
2878 and ca.staff_id = lac.staff_id
2879 inner join assignment_limits as al
2880 on lac.assignment_limit_id = al.id
2881 left join buffer_staff_assignments_count_rolling_793447700 as sac
2882 on lac.assignment_limit_id = sac.assignment_limit_id
2883 and lac.staff_id = sac.staff_id
2884 and lac.start_date = sac.start_date
2885 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
2886
2887 (150.9ms) delete ca
2888 from buffer_combined_availabilities_793447700 as ca
2889 inner join (
2890 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2891 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
2892 from (
2893 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2894 from buffer_combined_availabilities_793447700 as ca
2895 inner join buffer_schedules_793447700 as sc
2896 on ca.schedule_id = sc.id
2897 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2898 order by null
2899 ) as ca_sc
2900 inner join buffer_linkage_assignments_count_consecutive_793447700 as lac
2901 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2902 and ca_sc.schedule_on = lac.schedule_on
2903 inner join buffer_assignment_limit_staff_types_793447700 as alst
2904 on lac.assignment_limit_id = alst.assignment_limit_id
2905 and ca_sc.staff_type_id = alst.staff_type_id
2906 left join buffer_staff_assignments_count_consecutive_793447700 as sac
2907 on lac.assignment_limit_id = sac.assignment_limit_id
2908 and ca_sc.staff_id = sac.staff_id
2909 and lac.schedule_on = sac.schedule_on
2910 and lac.start_date = sac.start_date
2911 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2912 order by null
2913 ) as lac
2914 on ca.linkage_instance_id = lac.linkage_instance_id
2915 and ca.staff_id = lac.staff_id
2916 inner join assignment_limits as al
2917 on lac.assignment_limit_id = al.id
2918 left join (
2919 select assignment_limit_id, staff_id, start_date,
2920 count(schedule_on) as assignments_count
2921 from buffer_staff_assignments_count_consecutive_793447700
2922 group by assignment_limit_id, staff_id, start_date
2923 ) as sac
2924 on lac.assignment_limit_id = sac.assignment_limit_id
2925 and lac.staff_id = sac.staff_id
2926 and lac.start_date = sac.start_date
2927 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
2928
2929 (149.5ms) /* CumulativeWeeklyCallTypeTargetsRule */
2930 delete ca
2931 from buffer_combined_availabilities_793447700 as ca
2932 inner join buffer_schedules_793447700 as sc
2933 on ca.schedule_id = sc.id
2934 inner join buffer_target_assignments_793447700 as ta
2935 on sc.call_type_id = ta.call_type_id
2936 inner join buffer_linkage_call_type_targets_793447700 as lctt
2937 on sc.linkage_instance_id = lctt.linkage_instance_id
2938 and sc.calendar_schedule_id = lctt.calendar_schedule_id
2939 and lctt.period_offset = -1
2940 and ta.assignment_id = lctt.assignment_id
2941 and ta.assignment_type = lctt.assignment_type
2942 inner join buffer_cumulative_weekly_ct_targets_793447700 as ctt
2943 on ca.staff_id = ctt.staff_id
2944 and sc.calendar_schedule_id = ctt.calendar_schedule_id
2945 and ta.assignment_id = ctt.assignment_id
2946 and ta.assignment_type = ctt.assignment_type
2947 where ((round(ctt.raw_target, 2)) - ctt.scheduled - (lctt.weekday_targets + lctt.weekend_targets)) < 0
2948
2949 (147.4ms) /* MaxAssignmentsRule */
2950 delete ca
2951 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
2952 inner join buffer_schedules_793447700 as sc
2953 on ca.schedule_id = sc.id
2954 inner join buffer_linkage_call_type_assignments_793447700 as lcta use index (ix_buffer_linkage_call_type_assignments_composite_1)
2955 on sc.linkage_instance_id = lcta.linkage_instance_id
2956 and sc.calendar_schedule_id = lcta.calendar_schedule_id
2957 and sc.period_offset = lcta.period_offset
2958 and sc.call_type_id = lcta.call_type_id
2959 left join buffer_call_type_assignments_793447700 as cta use index (ix_buffer_call_type_assignments_composite_1)
2960 on ca.staff_id = cta.staff_id
2961 and sc.calendar_schedule_id = cta.calendar_schedule_id
2962 and sc.period_offset = cta.period_offset
2963 and sc.call_type_id = cta.call_type_id
2964 where
2965 /* weekday checkbox */
2966 (
2967 lcta.max_weekday_assignments is not null
2968 and (ifnull(cta.weekday_assignments, 0) + lcta.weekday_assignments) > lcta.max_weekday_assignments
2969 )
2970 /* weekend checkbox */
2971 or (
2972 lcta.max_weekend_assignments is not null
2973 and (ifnull(cta.weekend_assignments, 0) + lcta.weekend_assignments) > lcta.max_weekend_assignments
2974 )
2975
2976 (145.7ms) /* NegativeLinkRule */
2977 delete ca
2978 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
2979 inner join buffer_negative_relations_793447700 as nr use index (ix_buffer_negative_relations_composite_1)
2980 on ca.schedule_id = nr.schedule_id
2981 and ca.staff_id = nr.staff_id
2982
2983 (157.5ms) /* BrokenLinkageRule */
2984 truncate table buffer_combined_availability_linkages_793447700
2985
2986 (148.8ms) insert into buffer_combined_availability_linkages_793447700 (
2987 staff_id, linkage_instance_id, slots_count, is_primary, has_violations
2988 )
2989 select staff_id, linkage_instance_id,
2990 count(1) as slots_count,
2991 min(is_primary) as is_primary,
2992 max(has_violations) as has_violations
2993 from buffer_combined_availabilities_793447700 use index (ix_buffer_combined_availabilities_composite_2)
2994 group by staff_id, linkage_instance_id
2995 order by null
2996
2997 (144.5ms) delete ca
2998 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
2999 inner join buffer_schedules_793447700 as sc
3000 on ca.schedule_id = sc.id
3001 inner join buffer_linkages_793447700 as l
3002 on sc.linkage_instance_id = l.linkage_instance_id
3003 inner join buffer_combined_availability_linkages_793447700 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3004 on ca.staff_id = ls_1.staff_id
3005 and sc.linkage_instance_id = ls_1.linkage_instance_id
3006 left join buffer_schedule_linkages_793447700 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3007 on ca.staff_id = ls_2.staff_id
3008 and sc.linkage_instance_id = ls_2.linkage_instance_id
3009 where (ls_1.slots_count + ifnull(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */
3010
3011INFO : (02-27 08:17:34) --> fill: 46 - Scheduler::AutoPopulate::Queries::QueryScore02
3012INFO : (02-27 08:17:35) --> fill: 47 - Scheduler::AutoPopulate::Queries::QueryScore03
3013INFO : (02-27 08:17:35) --> fill: 48 - Scheduler::AutoPopulate::Queries::QueryScore04
3014INFO : (02-27 08:17:35) --> fill: 49 - Scheduler::AutoPopulate::Queries::Query11
3015 (144.2ms) update buffer_schedules_793447700 as sc
3016 left join buffer_combined_availabilities_793447700 as ca
3017 on sc.id = ca.schedule_id
3018 set sc.should_be_evaluated = 0,
3019 sc.should_be_populated = 0
3020 where sc.should_be_evaluated = 1
3021 and ca.id is null
3022
3023INFO : (02-27 08:17:35) --> fill: 50 - Scheduler::AutoPopulate::Queries::DropTables
3024INFO : (02-27 08:17:35) Setting up lists...
3025 (151.2ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
3026 from buffer_schedules_793447700 as b_schedules
3027 left join schedules
3028 on schedules.client_id = 319
3029 and schedules.call_type_id = b_schedules.call_type_id
3030 and schedules.schedule_on = b_schedules.schedule_on
3031 where b_schedules.linkage_instance_id != -1
3032 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
3033
3034INFO : (02-27 08:17:35) Begin to populate. Pre-set: 0. Unscheduled: 93.
3035INFO : (02-27 08:17:35) plan unit: [id:201809] [2018-03-04 - OB]
3036INFO : (02-27 08:17:35) plan unit: [id:201810] [2018-03-11 - OB]
3037INFO : (02-27 08:17:35) plan unit: [id:201811] [2018-03-18 - OB]
3038INFO : (02-27 08:17:35) plan unit: [id:201812] [2018-03-25 - OB]
3039INFO : (02-27 08:17:35) plan unit: [id:1201809] [2018-03-04 - OR]
3040INFO : (02-27 08:17:35) plan unit: [id:1201810] [2018-03-11 - OR]
3041INFO : (02-27 08:17:35) plan unit: [id:1201811] [2018-03-18 - OR]
3042INFO : (02-27 08:17:35) plan unit: [id:1201812] [2018-03-25 - OR]
3043INFO : (02-27 08:17:35) plan unit: [id:2201809] [2018-03-04 - BU]
3044INFO : (02-27 08:17:35) plan unit: [id:2201810] [2018-03-11 - BU]
3045INFO : (02-27 08:17:35) plan unit: [id:2201811] [2018-03-18 - BU]
3046INFO : (02-27 08:17:35) plan unit: [id:2201812] [2018-03-25 - BU]
3047INFO : (02-27 08:17:35) plan unit: [id:4201809] [2018-03-05 - OB]
3048INFO : (02-27 08:17:35) plan unit: [id:4201810] [2018-03-12 - OB]
3049INFO : (02-27 08:17:35) plan unit: [id:4201811] [2018-03-19 - OB]
3050INFO : (02-27 08:17:35) plan unit: [id:4201812] [2018-03-26 - OB]
3051INFO : (02-27 08:17:35) plan unit: [id:5201809] [2018-03-05 - OR]
3052INFO : (02-27 08:17:35) plan unit: [id:5201810] [2018-03-12 - OR]
3053INFO : (02-27 08:17:35) plan unit: [id:5201811] [2018-03-19 - OR]
3054INFO : (02-27 08:17:35) plan unit: [id:5201812] [2018-03-26 - OR]
3055INFO : (02-27 08:17:35) plan unit: [id:6201809] [2018-03-05 - BU]
3056INFO : (02-27 08:17:35) plan unit: [id:6201810] [2018-03-12 - BU]
3057INFO : (02-27 08:17:35) plan unit: [id:6201811] [2018-03-19 - BU]
3058INFO : (02-27 08:17:35) plan unit: [id:6201812] [2018-03-26 - BU]
3059INFO : (02-27 08:17:35) plan unit: [id:8201809] [2018-03-06 - OB]
3060INFO : (02-27 08:17:35) plan unit: [id:8201810] [2018-03-13 - OB]
3061INFO : (02-27 08:17:35) plan unit: [id:8201811] [2018-03-20 - OB]
3062INFO : (02-27 08:17:35) plan unit: [id:8201812] [2018-03-27 - OB]
3063INFO : (02-27 08:17:35) plan unit: [id:9201809] [2018-03-06 - OR]
3064INFO : (02-27 08:17:35) plan unit: [id:9201810] [2018-03-13 - OR]
3065INFO : (02-27 08:17:35) plan unit: [id:9201811] [2018-03-20 - OR]
3066INFO : (02-27 08:17:35) plan unit: [id:9201812] [2018-03-27 - OR]
3067INFO : (02-27 08:17:35) plan unit: [id:10201809] [2018-03-06 - BU]
3068INFO : (02-27 08:17:35) plan unit: [id:10201810] [2018-03-13 - BU]
3069INFO : (02-27 08:17:35) plan unit: [id:10201811] [2018-03-20 - BU]
3070INFO : (02-27 08:17:35) plan unit: [id:10201812] [2018-03-27 - BU]
3071INFO : (02-27 08:17:35) plan unit: [id:12201809] [2018-03-07 - OB]
3072INFO : (02-27 08:17:35) plan unit: [id:12201810] [2018-03-14 - OB]
3073INFO : (02-27 08:17:35) plan unit: [id:12201811] [2018-03-21 - OB]
3074INFO : (02-27 08:17:35) plan unit: [id:12201812] [2018-03-28 - OB]
3075INFO : (02-27 08:17:35) plan unit: [id:13201809] [2018-03-07 - OR]
3076INFO : (02-27 08:17:35) plan unit: [id:13201810] [2018-03-14 - OR]
3077INFO : (02-27 08:17:35) plan unit: [id:13201811] [2018-03-21 - OR]
3078INFO : (02-27 08:17:35) plan unit: [id:13201812] [2018-03-28 - OR]
3079INFO : (02-27 08:17:35) plan unit: [id:14201809] [2018-03-07 - BU]
3080INFO : (02-27 08:17:35) plan unit: [id:14201810] [2018-03-14 - BU]
3081INFO : (02-27 08:17:35) plan unit: [id:14201811] [2018-03-21 - BU]
3082INFO : (02-27 08:17:35) plan unit: [id:14201812] [2018-03-28 - BU]
3083INFO : (02-27 08:17:35) plan unit: [id:16201808] [2018-03-01 - OB]
3084INFO : (02-27 08:17:35) plan unit: [id:16201809] [2018-03-08 - OB]
3085INFO : (02-27 08:17:35) plan unit: [id:16201810] [2018-03-15 - OB]
3086INFO : (02-27 08:17:35) plan unit: [id:16201811] [2018-03-22 - OB]
3087INFO : (02-27 08:17:35) plan unit: [id:16201812] [2018-03-29 - OB]
3088INFO : (02-27 08:17:35) plan unit: [id:17201808] [2018-03-01 - OR]
3089INFO : (02-27 08:17:35) plan unit: [id:17201809] [2018-03-08 - OR]
3090INFO : (02-27 08:17:35) plan unit: [id:17201810] [2018-03-15 - OR]
3091INFO : (02-27 08:17:35) plan unit: [id:17201811] [2018-03-22 - OR]
3092INFO : (02-27 08:17:35) plan unit: [id:17201812] [2018-03-29 - OR]
3093INFO : (02-27 08:17:35) plan unit: [id:18201808] [2018-03-01 - BU]
3094INFO : (02-27 08:17:35) plan unit: [id:18201809] [2018-03-08 - BU]
3095INFO : (02-27 08:17:35) plan unit: [id:18201810] [2018-03-15 - BU]
3096INFO : (02-27 08:17:35) plan unit: [id:18201811] [2018-03-22 - BU]
3097INFO : (02-27 08:17:35) plan unit: [id:18201812] [2018-03-29 - BU]
3098INFO : (02-27 08:17:35) plan unit: [id:20201808] [2018-03-02 - OB]
3099INFO : (02-27 08:17:35) plan unit: [id:20201809] [2018-03-09 - OB]
3100INFO : (02-27 08:17:35) plan unit: [id:20201810] [2018-03-16 - OB]
3101INFO : (02-27 08:17:35) plan unit: [id:20201811] [2018-03-23 - OB]
3102INFO : (02-27 08:17:35) plan unit: [id:20201812] [2018-03-30 - OB]
3103INFO : (02-27 08:17:35) plan unit: [id:21201808] [2018-03-02 - OR]
3104INFO : (02-27 08:17:35) plan unit: [id:21201809] [2018-03-09 - OR]
3105INFO : (02-27 08:17:35) plan unit: [id:21201810] [2018-03-16 - OR]
3106INFO : (02-27 08:17:35) plan unit: [id:21201811] [2018-03-23 - OR]
3107INFO : (02-27 08:17:35) plan unit: [id:21201812] [2018-03-30 - OR]
3108INFO : (02-27 08:17:35) plan unit: [id:22201808] [2018-03-02 - BU]
3109INFO : (02-27 08:17:35) plan unit: [id:22201809] [2018-03-09 - BU]
3110INFO : (02-27 08:17:35) plan unit: [id:22201810] [2018-03-16 - BU]
3111INFO : (02-27 08:17:35) plan unit: [id:22201811] [2018-03-23 - BU]
3112INFO : (02-27 08:17:35) plan unit: [id:22201812] [2018-03-30 - BU]
3113INFO : (02-27 08:17:35) plan unit: [id:24201808] [2018-03-03 - OB]
3114INFO : (02-27 08:17:35) plan unit: [id:24201809] [2018-03-10 - OB]
3115INFO : (02-27 08:17:35) plan unit: [id:24201810] [2018-03-17 - OB]
3116INFO : (02-27 08:17:35) plan unit: [id:24201811] [2018-03-24 - OB]
3117INFO : (02-27 08:17:35) plan unit: [id:24201812] [2018-03-31 - OB]
3118INFO : (02-27 08:17:35) plan unit: [id:25201808] [2018-03-03 - OR]
3119INFO : (02-27 08:17:35) plan unit: [id:25201809] [2018-03-10 - OR]
3120INFO : (02-27 08:17:35) plan unit: [id:25201810] [2018-03-17 - OR]
3121INFO : (02-27 08:17:35) plan unit: [id:25201811] [2018-03-24 - OR]
3122INFO : (02-27 08:17:35) plan unit: [id:25201812] [2018-03-31 - OR]
3123INFO : (02-27 08:17:35) plan unit: [id:26201808] [2018-03-03 - BU]
3124INFO : (02-27 08:17:35) plan unit: [id:26201809] [2018-03-10 - BU]
3125INFO : (02-27 08:17:35) plan unit: [id:26201810] [2018-03-17 - BU]
3126INFO : (02-27 08:17:35) plan unit: [id:26201811] [2018-03-24 - BU]
3127INFO : (02-27 08:17:35) plan unit: [id:26201812] [2018-03-31 - BU]
3128 NegativeLink Load (145.6ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
3129 (144.8ms) /* SpecializationOverlapRule */
3130 delete ca
3131 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
3132 inner join buffer_schedules_793447700 as sc
3133 on ca.schedule_id = sc.id
3134 inner join buffer_staff_specializations_793447700 as ssp
3135 on ca.staff_id = ssp.staff_id
3136 inner join max_applicable_call_types as mact
3137 on ssp.specialization_id = mact.specialization_id
3138 and sc.call_type_id = mact.call_type_id
3139 inner join buffer_specialization_daily_counts_793447700 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3140 on ssp.specialization_id = sdc.specialization_id
3141 and sc.schedule_on = sdc.schedule_on
3142 and sdc.staff_id is null
3143 left join buffer_specialization_daily_counts_793447700 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3144 on ssp.specialization_id = sdc_2.specialization_id
3145 and sc.schedule_on = sdc_2.schedule_on
3146 and ca.staff_id = sdc_2.staff_id
3147 where sdc.should_be_evaluated = 1
3148 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3149
3150 (144.3ms) delete ca
3151 from buffer_combined_availabilities_793447700 as ca
3152 inner join (
3153 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3154 sum(lac.assignments_count) as assignments_count
3155 from (
3156 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3157 from buffer_combined_availabilities_793447700 as ca
3158 inner join buffer_schedules_793447700 as sc
3159 on ca.schedule_id = sc.id
3160 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3161 order by null
3162 ) as ca_sc
3163 inner join buffer_linkage_assignments_count_weeks_793447700 as lac
3164 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3165 and ca_sc.schedule_on = lac.schedule_on
3166 inner join buffer_assignment_limit_staff_types_793447700 as alst
3167 on lac.assignment_limit_id = alst.assignment_limit_id
3168 and ca_sc.staff_type_id = alst.staff_type_id
3169 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3170 order by null
3171 ) as lac
3172 on ca.linkage_instance_id = lac.linkage_instance_id
3173 and ca.staff_id = lac.staff_id
3174 inner join assignment_limits as al
3175 on lac.assignment_limit_id = al.id
3176 left join buffer_staff_assignments_count_weeks_793447700 as sac
3177 on lac.assignment_limit_id = sac.assignment_limit_id
3178 and lac.staff_id = sac.staff_id
3179 and lac.start_date = sac.start_date
3180 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
3181
3182 (146.8ms) delete ca
3183 from buffer_combined_availabilities_793447700 as ca
3184 inner join (
3185 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3186 sum(lac.assignments_count) as assignments_count
3187 from (
3188 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3189 from buffer_combined_availabilities_793447700 as ca
3190 inner join buffer_schedules_793447700 as sc
3191 on ca.schedule_id = sc.id
3192 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3193 order by null
3194 ) as ca_sc
3195 inner join buffer_linkage_assignments_count_periods_793447700 as lac
3196 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3197 and ca_sc.schedule_on = lac.schedule_on
3198 inner join buffer_assignment_limit_staff_types_793447700 as alst
3199 on lac.assignment_limit_id = alst.assignment_limit_id
3200 and ca_sc.staff_type_id = alst.staff_type_id
3201 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3202 order by null
3203 ) as lac
3204 on ca.linkage_instance_id = lac.linkage_instance_id
3205 and ca.staff_id = lac.staff_id
3206 inner join assignment_limits as al
3207 on lac.assignment_limit_id = al.id
3208 left join buffer_staff_assignments_count_periods_793447700 as sac
3209 on lac.assignment_limit_id = sac.assignment_limit_id
3210 and lac.staff_id = sac.staff_id
3211 and lac.start_date = sac.start_date
3212 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
3213
3214 (146.1ms) delete ca
3215 from buffer_combined_availabilities_793447700 as ca
3216 inner join (
3217 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3218 sum(lac.assignments_count) as assignments_count
3219 from (
3220 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3221 from buffer_combined_availabilities_793447700 as ca
3222 inner join buffer_schedules_793447700 as sc
3223 on ca.schedule_id = sc.id
3224 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3225 order by null
3226 ) as ca_sc
3227 inner join buffer_linkage_assignments_count_rolling_793447700 as lac
3228 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3229 and ca_sc.schedule_on = lac.schedule_on
3230 inner join buffer_assignment_limit_staff_types_793447700 as alst
3231 on lac.assignment_limit_id = alst.assignment_limit_id
3232 and ca_sc.staff_type_id = alst.staff_type_id
3233 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3234 order by null
3235 ) as lac
3236 on ca.linkage_instance_id = lac.linkage_instance_id
3237 and ca.staff_id = lac.staff_id
3238 inner join assignment_limits as al
3239 on lac.assignment_limit_id = al.id
3240 left join buffer_staff_assignments_count_rolling_793447700 as sac
3241 on lac.assignment_limit_id = sac.assignment_limit_id
3242 and lac.staff_id = sac.staff_id
3243 and lac.start_date = sac.start_date
3244 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
3245
3246 (150.5ms) delete ca
3247 from buffer_combined_availabilities_793447700 as ca
3248 inner join (
3249 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
3250 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
3251 from (
3252 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3253 from buffer_combined_availabilities_793447700 as ca
3254 inner join buffer_schedules_793447700 as sc
3255 on ca.schedule_id = sc.id
3256 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
3257 order by null
3258 ) as ca_sc
3259 inner join buffer_linkage_assignments_count_consecutive_793447700 as lac
3260 on ca_sc.linkage_instance_id = lac.linkage_instance_id
3261 and ca_sc.schedule_on = lac.schedule_on
3262 inner join buffer_assignment_limit_staff_types_793447700 as alst
3263 on lac.assignment_limit_id = alst.assignment_limit_id
3264 and ca_sc.staff_type_id = alst.staff_type_id
3265 left join buffer_staff_assignments_count_consecutive_793447700 as sac
3266 on lac.assignment_limit_id = sac.assignment_limit_id
3267 and ca_sc.staff_id = sac.staff_id
3268 and lac.schedule_on = sac.schedule_on
3269 and lac.start_date = sac.start_date
3270 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
3271 order by null
3272 ) as lac
3273 on ca.linkage_instance_id = lac.linkage_instance_id
3274 and ca.staff_id = lac.staff_id
3275 inner join assignment_limits as al
3276 on lac.assignment_limit_id = al.id
3277 left join (
3278 select assignment_limit_id, staff_id, start_date,
3279 count(schedule_on) as assignments_count
3280 from buffer_staff_assignments_count_consecutive_793447700
3281 group by assignment_limit_id, staff_id, start_date
3282 ) as sac
3283 on lac.assignment_limit_id = sac.assignment_limit_id
3284 and lac.staff_id = sac.staff_id
3285 and lac.start_date = sac.start_date
3286 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
3287
3288 (148.1ms) /* CumulativeWeeklyCallTypeTargetsRule */
3289 delete ca
3290 from buffer_combined_availabilities_793447700 as ca
3291 inner join buffer_schedules_793447700 as sc
3292 on ca.schedule_id = sc.id
3293 inner join buffer_target_assignments_793447700 as ta
3294 on sc.call_type_id = ta.call_type_id
3295 inner join buffer_linkage_call_type_targets_793447700 as lctt
3296 on sc.linkage_instance_id = lctt.linkage_instance_id
3297 and sc.calendar_schedule_id = lctt.calendar_schedule_id
3298 and lctt.period_offset = -1
3299 and ta.assignment_id = lctt.assignment_id
3300 and ta.assignment_type = lctt.assignment_type
3301 inner join buffer_cumulative_weekly_ct_targets_793447700 as ctt
3302 on ca.staff_id = ctt.staff_id
3303 and sc.calendar_schedule_id = ctt.calendar_schedule_id
3304 and ta.assignment_id = ctt.assignment_id
3305 and ta.assignment_type = ctt.assignment_type
3306 where ((round(ctt.raw_target, 2)) - ctt.scheduled - (lctt.weekday_targets + lctt.weekend_targets)) < 0
3307
3308 (148.3ms) /* MaxAssignmentsRule */
3309 delete ca
3310 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
3311 inner join buffer_schedules_793447700 as sc
3312 on ca.schedule_id = sc.id
3313 inner join buffer_linkage_call_type_assignments_793447700 as lcta use index (ix_buffer_linkage_call_type_assignments_composite_1)
3314 on sc.linkage_instance_id = lcta.linkage_instance_id
3315 and sc.calendar_schedule_id = lcta.calendar_schedule_id
3316 and sc.period_offset = lcta.period_offset
3317 and sc.call_type_id = lcta.call_type_id
3318 left join buffer_call_type_assignments_793447700 as cta use index (ix_buffer_call_type_assignments_composite_1)
3319 on ca.staff_id = cta.staff_id
3320 and sc.calendar_schedule_id = cta.calendar_schedule_id
3321 and sc.period_offset = cta.period_offset
3322 and sc.call_type_id = cta.call_type_id
3323 where
3324 /* weekday checkbox */
3325 (
3326 lcta.max_weekday_assignments is not null
3327 and (ifnull(cta.weekday_assignments, 0) + lcta.weekday_assignments) > lcta.max_weekday_assignments
3328 )
3329 /* weekend checkbox */
3330 or (
3331 lcta.max_weekend_assignments is not null
3332 and (ifnull(cta.weekend_assignments, 0) + lcta.weekend_assignments) > lcta.max_weekend_assignments
3333 )
3334
3335 (162.4ms) /* NegativeLinkRule */
3336 delete ca
3337 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
3338 inner join buffer_negative_relations_793447700 as nr use index (ix_buffer_negative_relations_composite_1)
3339 on ca.schedule_id = nr.schedule_id
3340 and ca.staff_id = nr.staff_id
3341
3342 (147.0ms) /* BrokenLinkageRule */
3343 truncate table buffer_combined_availability_linkages_793447700
3344
3345 (149.9ms) insert into buffer_combined_availability_linkages_793447700 (
3346 staff_id, linkage_instance_id, slots_count, is_primary, has_violations
3347 )
3348 select staff_id, linkage_instance_id,
3349 count(1) as slots_count,
3350 min(is_primary) as is_primary,
3351 max(has_violations) as has_violations
3352 from buffer_combined_availabilities_793447700 use index (ix_buffer_combined_availabilities_composite_2)
3353 group by staff_id, linkage_instance_id
3354 order by null
3355
3356 (146.9ms) delete ca
3357 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
3358 inner join buffer_schedules_793447700 as sc
3359 on ca.schedule_id = sc.id
3360 inner join buffer_linkages_793447700 as l
3361 on sc.linkage_instance_id = l.linkage_instance_id
3362 inner join buffer_combined_availability_linkages_793447700 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3363 on ca.staff_id = ls_1.staff_id
3364 and sc.linkage_instance_id = ls_1.linkage_instance_id
3365 left join buffer_schedule_linkages_793447700 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3366 on ca.staff_id = ls_2.staff_id
3367 and sc.linkage_instance_id = ls_2.linkage_instance_id
3368 where (ls_1.slots_count + ifnull(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */
3369
3370 (151.9ms) select straight_join
3371 sc.schedule_on,
3372 sc.call_type_id,
3373 count(distinct ca.staff_id) as available_members_count,
3374 group_concat(
3375 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3376 order by ls_1.is_primary desc, ca.staff_id
3377 separator ', '
3378 ) as available_members_ids,
3379 sc.linkage_instance_id,
3380 l.slots_count_total as linkage_slots_count
3381 from buffer_combined_availabilities_793447700 as ca
3382 inner join buffer_schedules_793447700 as sc
3383 on ca.schedule_id = sc.id
3384 inner join buffer_linkages_793447700 as l
3385 on sc.linkage_instance_id = l.linkage_instance_id
3386 inner join buffer_combined_availability_linkages_793447700 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3387 on ca.staff_id = ls_1.staff_id
3388 and sc.linkage_instance_id = ls_1.linkage_instance_id
3389 and ls_1.has_violations = 0
3390 where sc.should_be_evaluated = 1
3391 and sc.should_be_populated = 1
3392 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3393 order by available_members_count, sc.schedule_on, sc.display_order
3394 limit 1
3395
3396 (145.4ms) truncate table buffer_rule_scores_793447700
3397
3398 (145.4ms) truncate table buffer_rule_calc_filters_793447700
3399
3400 (145.8ms) insert into buffer_rule_calc_filters_793447700 (
3401 linkage_instance_id, staff_id
3402 ) values (16201808, 20695),(16201808, 20696),(16201808, 20697),(16201808, 20698),(16201808, 20699),(16201808, 20701),(16201808, 20704),(16201808, 20706)
3403
3404 (147.1ms) /* CumulativeWeeklyCallTypeTargetsRule */
3405 insert into buffer_rule_scores_793447700 (
3406 linkage_instance_id, schedule_id, staff_id, rule_name, value
3407 )
3408 select straight_join
3409 b_schedules.linkage_instance_id, b_schedules.id as schedule_id, availabilities.staff_id, 'CumulativeWeeklyCallTypeTargetsRule' as rule_name,
3410 (
3411 (b_targets.raw_target /* depends on a cumulative targets period */ - b_targets.scheduled) /
3412 b_targets.raw_target /* depends on a cumulative targets period */
3413 ) as value
3414 from buffer_rule_calc_filters_793447700 as filters
3415 inner join buffer_schedules_793447700 as b_schedules
3416 on filters.linkage_instance_id = b_schedules.linkage_instance_id
3417 inner join buffer_combined_availabilities_793447700 as availabilities use index (ix_buffer_combined_availabilities_composite_1)
3418 on b_schedules.id = availabilities.schedule_id
3419 and filters.staff_id = availabilities.staff_id
3420 inner join buffer_target_assignments_793447700 as target_assignments
3421 on b_schedules.call_type_id = target_assignments.call_type_id
3422 inner join buffer_cumulative_weekly_ct_targets_793447700 as b_targets
3423 on availabilities.staff_id = b_targets.staff_id
3424 and b_schedules.calendar_schedule_id = b_targets.calendar_schedule_id
3425 and target_assignments.assignment_id = b_targets.assignment_id
3426 and target_assignments.assignment_type = b_targets.assignment_type
3427 order by null
3428
3429 (150.9ms) truncate table buffer_rule_scores_with_corrections_793447700
3430
3431 (148.5ms) insert into buffer_rule_scores_with_corrections_793447700 (
3432 linkage_instance_id, schedule_id, staff_id, rule_name, value
3433 )
3434 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3435 ifnull(rule_scores.value, max_values.max_value + 1) as value
3436 from buffer_rule_scores_793447700 as rule_scores
3437 inner join (
3438 select rule_name, max(value) as max_value
3439 from buffer_rule_scores_793447700
3440 group by rule_name
3441 order by null
3442 ) as max_values
3443 on rule_scores.rule_name = max_values.rule_name
3444 order by null
3445
3446 (147.4ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3447 from (
3448 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3449 sum(
3450 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3451 rules.weight *
3452 total_values.slots_count
3453 ) as slot_scores
3454 from buffer_rule_scores_with_corrections_793447700 as rule_scores
3455 inner join (
3456 select rule_name, sum(abs(value)) as total_value,
3457 count(distinct schedule_id) as slots_count
3458 from buffer_rule_scores_with_corrections_793447700
3459 group by rule_name
3460 order by null
3461 ) as total_values
3462 on rule_scores.rule_name = total_values.rule_name
3463 inner join rules
3464 on rule_scores.rule_name = rules.type
3465 where rules.rule_set_id = 1527
3466 and rules.applied = 1
3467 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3468 order by null
3469 ) as staff_scores
3470 group by linkage_instance_id, staff_id
3471 order by round(avg(slot_scores), 5) desc, rand()
3472 limit 1
3473
3474 PublishedBlock Load (148.4ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 319 AND ('2018-03-01' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1381 ORDER BY `published_blocks`.`id` ASC LIMIT 1
3475 (143.6ms) BEGIN
3476 (147.1ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
3477 FROM call_types AS ct
3478 INNER JOIN calendar_schedules AS cs
3479 ON ct.calendar_schedule_id = cs.id
3480 INNER JOIN blocks AS b
3481 ON b.client_id = ct.client_id
3482 AND b.block_schedule_id = cs.block_schedule_id
3483 AND '2018-03-01' BETWEEN b.start_date AND b.end_date
3484 INNER JOIN published_blocks AS pb
3485 ON pb.client_id = ct.client_id
3486 AND pb.block_id = b.id
3487 AND pb.calendar_schedule_id = cs.id
3488 LEFT JOIN published_block_call_types AS pbct
3489 ON pbct.client_id = ct.client_id
3490 AND pbct.assignment_id = ct.id
3491 AND pbct.assignment_type = 'CallType'
3492 AND pbct.block_id = b.id
3493 AND (
3494 (1 << (DAYOFWEEK('2018-03-01') - 1) & pbct.days_of_week_mask) > 0
3495 )
3496 WHERE ct.id = 12692
3497 AND (
3498 (
3499 pb.assignment_ids IS NULL
3500 AND (1 << (DAYOFWEEK('2018-03-01') - 1) & pb.days_of_week_mask) > 0
3501 )
3502 OR pbct.id IS NOT NULL
3503 )
3504
3505 CallType Load (146.5ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
3506 Client Load (150.3ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3507 Staff Load (640.3ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20698 LIMIT 1
3508 Schedule Exists (144.1ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12692 AND `schedules`.`client_id` = 319 AND `schedules`.`schedule_on` = '2018-03-01' LIMIT 1
3509 SQL (169.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, 12692, 20698, '2018-03-01', '2018-02-27 08:17:40', '2018-02-27 08:17:40', 0, 5, 'auto_populate_step', 166)
3510 VisibleAssignment Load (144.6ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 319 AND `visible_assignments`.`date` = '2018-03-01' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12692 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
3511 (146.6ms) COMMIT
3512FLUSHED: nil:nil
3513 (151.7ms) UPDATE buffer_schedules_793447700
3514 SET staff_id = 20698,
3515 should_be_populated = 0,
3516 should_be_evaluated = 0
3517 WHERE
3518 call_type_id = 12692
3519 AND schedule_on = '2018-03-01'
3520
3521 (148.3ms) delete from buffer_combined_availabilities_793447700
3522 where linkage_instance_id = 16201808
3523
3524 (143.8ms) UPDATE buffer_rule_calc_schedules_793447700
3525 SET staff_id = 20698
3526 WHERE call_type_id = 12692
3527 AND schedule_on = '2018-03-01'
3528
3529 (146.1ms) INSERT INTO buffer_rule_calc_schedules_793447700
3530 SET staff_id = 20698,
3531 call_type_id = 12692,
3532 schedule_on = '2018-03-01',
3533 day_of_week_mask = 16
3534
3535 (143.5ms) delete from buffer_linkage_targets_793447700
3536 where linkage_instance_id = 16201808
3537
3538 (146.1ms) delete from buffer_linkage_call_type_targets_793447700
3539 where linkage_instance_id = 16201808
3540
3541 (143.6ms) delete from buffer_linkage_point_targets_793447700
3542 where linkage_instance_id = 16201808
3543
3544 (159.3ms) delete from buffer_linkage_hour_targets_793447700
3545 where linkage_instance_id = 16201808
3546
3547 (146.0ms) delete from buffer_linkage_call_type_assignments_793447700
3548 where linkage_instance_id = 16201808
3549
3550 (143.8ms) delete from buffer_linkage_call_days_targets_793447700
3551 where linkage_instance_id = 16201808
3552
3553 (146.6ms) delete from buffer_linkage_assigned_days_793447700
3554 where linkage_instance_id = 16201808
3555
3556 (150.6ms) delete from buffer_linkage_dow_targets_793447700
3557 where linkage_instance_id = 16201808
3558
3559 (144.8ms) update buffer_schedules_793447700 as sc
3560 inner join call_types as ct
3561 on sc.call_type_id = ct.id
3562 and ct.active = 1
3563 inner join buffer_call_type_assignments_793447700 as cta
3564 on sc.staff_id = cta.staff_id
3565 and sc.calendar_schedule_id = cta.calendar_schedule_id
3566 and sc.period_offset = cta.period_offset
3567 and sc.call_type_id = cta.call_type_id
3568 set cta.weekday_assignments = cta.weekday_assignments + if((sc.day_of_week_mask & 62 > 0), 1, 0),
3569 cta.weekend_assignments = cta.weekend_assignments + if((sc.day_of_week_mask & 65 > 0), 1, 0)
3570 where sc.schedule_on = '2018-03-01'
3571 and sc.call_type_id = 12692
3572 and sc.staff_id = 20698
3573
3574 (147.2ms) INSERT INTO buffer_call_type_assignments_793447700 (
3575 staff_id, calendar_schedule_id, period_offset, call_type_id, weekday_assignments, weekend_assignments
3576 )
3577 SELECT sc.staff_id,
3578 sc.calendar_schedule_id,
3579 sc.period_offset,
3580 sc.call_type_id,
3581 if((sc.day_of_week_mask & 62 > 0), 1, 0) AS weekday_assignments,
3582 if((sc.day_of_week_mask & 65 > 0), 1, 0) AS weekend_assignments
3583 FROM
3584 buffer_schedules_793447700 as sc
3585 WHERE
3586 sc.staff_id = 20698
3587 AND sc.schedule_on = '2018-03-01'
3588 AND sc.call_type_id = 12692
3589
3590 (144.7ms) UPDATE buffer_staff_daily_counts_793447700
3591 SET slots_count = slots_count + 1
3592 WHERE
3593 staff_id = 20698
3594 AND schedule_on = '2018-03-01'
3595 AND linkage_instance_id is null
3596
3597 (153.3ms) INSERT INTO buffer_staff_daily_counts_793447700
3598 SET slots_count = 1,
3599 staff_id = 20698,
3600 schedule_on = '2018-03-01',
3601 linkage_instance_id = null
3602
3603 (161.1ms) UPDATE buffer_staff_daily_counts_793447700
3604 SET slots_count = slots_count + 1
3605 WHERE
3606 staff_id = 20698
3607 AND schedule_on = '2018-03-01'
3608 AND linkage_instance_id = 16201808
3609
3610 (173.9ms) INSERT INTO buffer_staff_daily_counts_793447700
3611 SET slots_count = 1,
3612 staff_id = 20698,
3613 schedule_on = '2018-03-01',
3614 linkage_instance_id = 16201808
3615
3616 (169.7ms) update buffer_staff_specializations_793447700 as st_sp
3617 inner join max_applicable_call_types as mact
3618 on st_sp.specialization_id = mact.specialization_id
3619 and mact.call_type_id = 12692
3620 inner join buffer_specialization_daily_counts_793447700 as sp_dc
3621 on st_sp.specialization_id = sp_dc.specialization_id
3622 and sp_dc.staff_id is null
3623 and sp_dc.schedule_on = '2018-03-01'
3624 left join buffer_specialization_daily_counts_793447700 as sp_dc_2
3625 on st_sp.specialization_id = sp_dc_2.specialization_id
3626 and st_sp.staff_id = sp_dc_2.staff_id
3627 and sp_dc_2.schedule_on = '2018-03-01'
3628 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
3629 where st_sp.staff_id = 20698
3630 and sp_dc_2.id is null
3631
3632 (147.4ms) insert into buffer_specialization_daily_counts_793447700 (
3633 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
3634 )
3635 select sp.id as specialization_id,
3636 20698 as staff_id,
3637 '2018-03-01' as schedule_on,
3638 1 as staffs_scheduled,
3639 sp.max_scheduled,
3640 sp_dc.should_be_evaluated
3641 from buffer_staff_specializations_793447700 as st_sp
3642 inner join specializations as sp
3643 on st_sp.specialization_id = sp.id
3644 inner join max_applicable_call_types as mact
3645 on st_sp.specialization_id = mact.specialization_id
3646 and mact.call_type_id = 12692
3647 inner join buffer_specialization_daily_counts_793447700 as sp_dc
3648 on st_sp.specialization_id = sp_dc.specialization_id
3649 and sp_dc.staff_id is null
3650 and sp_dc.schedule_on = '2018-03-01'
3651 left join buffer_specialization_daily_counts_793447700 as sp_dc_2
3652 on st_sp.specialization_id = sp_dc_2.specialization_id
3653 and st_sp.staff_id = sp_dc_2.staff_id
3654 and sp_dc_2.schedule_on = '2018-03-01'
3655 where st_sp.staff_id = 20698
3656 and sp_dc_2.id is null
3657
3658 (145.2ms) UPDATE buffer_negative_relations_793447700
3659 SET staff_id = 20698
3660 WHERE affected_by_schedule_id = (
3661 SELECT id
3662 FROM buffer_schedules_793447700
3663 WHERE schedule_on = '2018-03-01'
3664 AND call_type_id = 12692
3665 )
3666
3667 (146.5ms) UPDATE buffer_staff_assigned_days_793447700 as sad
3668 inner join call_types as ct
3669 on ct.id = 12692
3670 and ct.unassigned_day = 0
3671 SET sad.is_assigned = 1
3672 WHERE sad.staff_id = 20698
3673 AND sad.schedule_on = '2018-03-01'
3674
3675 (145.1ms) -- current_oa_targets
3676 update buffer_schedules_793447700 as sc
3677 inner join buffer_counter_increments_793447700 as ci
3678 on sc.id = ci.schedule_id
3679 inner join buffer_current_oa_targets_793447700 as t
3680 on sc.staff_id = t.staff_id
3681 and sc.calendar_schedule_id = t.calendar_schedule_id
3682 and sc.period_offset = t.period_offset
3683 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3684 set t.scheduled = t.scheduled + ci.calls
3685 where sc.schedule_on = '2018-03-01'
3686 and sc.call_type_id = 12692
3687 and sc.staff_id = 20698
3688
3689 (144.3ms) -- cumulative_oa_targets
3690 update buffer_schedules_793447700 as sc
3691 inner join buffer_counter_increments_793447700 as ci
3692 on sc.id = ci.schedule_id
3693 and ci.affects_cumulative_targets = 1
3694 inner join buffer_cumulative_oa_targets_793447700 as t
3695 on sc.calendar_schedule_id = t.calendar_schedule_id
3696 and sc.staff_id = t.staff_id
3697 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3698 set t.scheduled = t.scheduled + ci.calls
3699 where sc.schedule_on = '2018-03-01'
3700 and sc.call_type_id = 12692
3701 and sc.staff_id = 20698
3702
3703 (145.4ms) -- current_cd_targets
3704 update buffer_schedules_793447700 as sc
3705 inner join buffer_counter_increments_793447700 as ci
3706 on sc.id = ci.schedule_id
3707 inner join buffer_target_assignments_793447700 as ta
3708 on sc.call_type_id = ta.call_type_id
3709 inner join buffer_current_cd_targets_793447700 as t
3710 on sc.staff_id = t.staff_id
3711 and sc.calendar_schedule_id = t.calendar_schedule_id
3712 and sc.period_offset = t.period_offset
3713 and ta.assignment_id = t.assignment_id
3714 and ta.assignment_type = t.assignment_type
3715 and sc.day_of_week_mask = t.day_of_week_mask
3716 set t.scheduled = t.scheduled + ci.calls
3717 where sc.schedule_on = '2018-03-01'
3718 and sc.call_type_id = 12692
3719 and sc.staff_id = 20698
3720
3721 (144.0ms) -- cumulative_cd_targets
3722 update buffer_schedules_793447700 as sc
3723 inner join buffer_counter_increments_793447700 as ci
3724 on sc.id = ci.schedule_id
3725 and ci.affects_cumulative_targets = 1
3726 inner join buffer_target_assignments_793447700 as ta
3727 on sc.call_type_id = ta.call_type_id
3728 inner join buffer_cumulative_cd_targets_793447700 as t
3729 on sc.calendar_schedule_id = t.calendar_schedule_id
3730 and sc.staff_id = t.staff_id
3731 and ta.assignment_id = t.assignment_id
3732 and ta.assignment_type = t.assignment_type
3733 and sc.day_of_week_mask = t.day_of_week_mask
3734 set t.scheduled = t.scheduled + ci.calls
3735 where sc.schedule_on = '2018-03-01'
3736 and sc.call_type_id = 12692
3737 and sc.staff_id = 20698
3738
3739 (151.2ms) -- current_ct_targets
3740 update buffer_schedules_793447700 as sc
3741 inner join buffer_counter_increments_793447700 as ci
3742 on sc.id = ci.schedule_id
3743 inner join buffer_target_assignments_793447700 as ta
3744 on sc.call_type_id = ta.call_type_id
3745 inner join buffer_current_ct_targets_793447700 as t
3746 on sc.staff_id = t.staff_id
3747 and sc.calendar_schedule_id = t.calendar_schedule_id
3748 and sc.period_offset = t.period_offset
3749 and ta.assignment_id = t.assignment_id
3750 and ta.assignment_type = t.assignment_type
3751 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3752 set t.scheduled = t.scheduled + ci.calls
3753 where sc.schedule_on = '2018-03-01'
3754 and sc.call_type_id = 12692
3755 and sc.staff_id = 20698
3756
3757 (147.0ms) -- cumulative_ct_targets
3758 update buffer_schedules_793447700 as sc
3759 inner join buffer_counter_increments_793447700 as ci
3760 on sc.id = ci.schedule_id
3761 and ci.affects_cumulative_targets = 1
3762 inner join buffer_target_assignments_793447700 as ta
3763 on sc.call_type_id = ta.call_type_id
3764 inner join buffer_cumulative_ct_targets_793447700 as t
3765 on sc.calendar_schedule_id = t.calendar_schedule_id
3766 and sc.staff_id = t.staff_id
3767 and ta.assignment_id = t.assignment_id
3768 and ta.assignment_type = t.assignment_type
3769 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3770 set t.scheduled = t.scheduled + ci.calls
3771 where sc.schedule_on = '2018-03-01'
3772 and sc.call_type_id = 12692
3773 and sc.staff_id = 20698
3774
3775 (145.7ms) -- current_dow_targets
3776 update buffer_schedules_793447700 as sc
3777 inner join buffer_counter_increments_793447700 as ci
3778 on sc.id = ci.schedule_id
3779 inner join buffer_current_dow_targets_793447700 as t
3780 on sc.staff_id = t.staff_id
3781 and sc.calendar_schedule_id = t.calendar_schedule_id
3782 and sc.period_offset = t.period_offset
3783 and dayofweek(sc.schedule_on) - 1 = t.wday
3784 set t.scheduled = t.scheduled + ci.calls
3785 where sc.schedule_on = '2018-03-01'
3786 and sc.call_type_id = 12692
3787 and sc.staff_id = 20698
3788
3789 (148.3ms) -- cumulative_dow_targets
3790 update buffer_schedules_793447700 as sc
3791 inner join buffer_counter_increments_793447700 as ci
3792 on sc.id = ci.schedule_id
3793 and ci.affects_cumulative_targets = 1
3794 inner join buffer_cumulative_dow_targets_793447700 as t
3795 on sc.calendar_schedule_id = t.calendar_schedule_id
3796 and sc.staff_id = t.staff_id
3797 and dayofweek(sc.schedule_on) - 1 = t.wday
3798 set t.scheduled = t.scheduled + ci.calls
3799 where sc.schedule_on = '2018-03-01'
3800 and sc.call_type_id = 12692
3801 and sc.staff_id = 20698
3802
3803 (145.0ms) -- current and cumulative point targets
3804
3805 update buffer_schedules_793447700 as sc
3806 inner join buffer_counter_increments_793447700 as ci
3807 on sc.id = ci.schedule_id
3808 inner join buffer_point_targets_793447700 as t
3809 on sc.staff_id = t.staff_id
3810 and sc.calendar_schedule_id = t.calendar_schedule_id
3811 and sc.block_id = t.block_id
3812 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3813 left join buffer_staff_rotation_points_793447700 as srp
3814 on sc.schedule_on = srp.schedule_on
3815 and sc.staff_id = srp.staff_id
3816 and sc.calendar_schedule_id = srp.calendar_schedule_id
3817 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3818 where sc.schedule_on = '2018-03-01'
3819 and sc.call_type_id = 12692
3820 and sc.staff_id = 20698
3821
3822 (146.9ms) update buffer_schedules_793447700 as sc
3823 inner join buffer_counter_increments_793447700 as ci
3824 on sc.id = ci.schedule_id
3825 inner join buffer_target_dates as td
3826 on td.session_id = 793447700
3827 and td.client_id = 319
3828 and sc.calendar_schedule_id != td.calendar_schedule_id
3829 and sc.schedule_on between td.start_date and td.end_date
3830 inner join buffer_point_targets_793447700 as t
3831 on sc.staff_id = t.staff_id
3832 and td.calendar_schedule_id = t.calendar_schedule_id
3833 and td.block_id = t.block_id
3834 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3835 left join buffer_staff_rotation_points_793447700 as srp
3836 on sc.schedule_on = srp.schedule_on
3837 and sc.staff_id = srp.staff_id
3838 and td.calendar_schedule_id = srp.calendar_schedule_id
3839 set t.scheduled = t.scheduled - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0),
3840 t.other_scheduled = t.other_scheduled + ci.points
3841 where sc.schedule_on = '2018-03-01'
3842 and sc.call_type_id = 12692
3843 and sc.staff_id = 20698
3844
3845 (283.9ms) -- current_point_targets
3846 update buffer_schedules_793447700 as sc
3847 inner join buffer_counter_increments_793447700 as ci
3848 on sc.id = ci.schedule_id
3849 inner join buffer_current_point_targets_793447700 as t
3850 on sc.staff_id = t.staff_id
3851 and sc.calendar_schedule_id = t.calendar_schedule_id
3852 and sc.period_offset = t.period_offset
3853 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3854 left join buffer_staff_rotation_points_793447700 as srp
3855 on sc.schedule_on = srp.schedule_on
3856 and sc.staff_id = srp.staff_id
3857 and sc.calendar_schedule_id = srp.calendar_schedule_id
3858 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3859 where sc.schedule_on = '2018-03-01'
3860 and sc.call_type_id = 12692
3861 and sc.staff_id = 20698
3862
3863 (147.3ms) -- cumulative_point_targets
3864 update buffer_schedules_793447700 as sc
3865 inner join buffer_counter_increments_793447700 as ci
3866 on sc.id = ci.schedule_id
3867 and ci.affects_cumulative_targets = 1
3868 inner join buffer_cumulative_point_targets_793447700 as t
3869 on sc.calendar_schedule_id = t.calendar_schedule_id
3870 and sc.staff_id = t.staff_id
3871 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3872 left join buffer_staff_rotation_points_793447700 as srp
3873 on sc.schedule_on = srp.schedule_on
3874 and sc.staff_id = srp.staff_id
3875 and sc.calendar_schedule_id = srp.calendar_schedule_id
3876 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
3877 where sc.schedule_on = '2018-03-01'
3878 and sc.call_type_id = 12692
3879 and sc.staff_id = 20698
3880
3881 (147.9ms) update buffer_schedules_793447700 as sc
3882 inner join buffer_counter_increments_793447700 as ci
3883 on sc.id = ci.schedule_id
3884 inner join buffer_staff_rotation_points_793447700 as srp
3885 on sc.schedule_on = srp.schedule_on
3886 and sc.staff_id = srp.staff_id
3887 set srp.points = if(ci.affects_rotations = 1, 0.0, srp.points)
3888 where sc.schedule_on = '2018-03-01'
3889 and sc.call_type_id = 12692
3890 and sc.staff_id = 20698
3891
3892 (144.6ms) -- current and cumulative hour targets
3893
3894 update buffer_schedules_793447700 as sc
3895 inner join buffer_counter_increments_793447700 as ci
3896 on sc.id = ci.schedule_id
3897 inner join buffer_hour_targets_793447700 as t
3898 on sc.staff_id = t.staff_id
3899 and sc.calendar_schedule_id = t.calendar_schedule_id
3900 and sc.block_id = t.block_id
3901 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3902 set t.scheduled = t.scheduled + ci.hours
3903 where sc.schedule_on = '2018-03-01'
3904 and sc.call_type_id = 12692
3905 and sc.staff_id = 20698
3906
3907 (146.9ms) update buffer_schedules_793447700 as sc
3908 inner join buffer_counter_increments_793447700 as ci
3909 on sc.id = ci.schedule_id
3910 inner join buffer_target_dates as td
3911 on td.session_id = 793447700
3912 and td.client_id = 319
3913 and sc.calendar_schedule_id != td.calendar_schedule_id
3914 and sc.schedule_on between td.start_date and td.end_date
3915 inner join buffer_hour_targets_793447700 as t
3916 on sc.staff_id = t.staff_id
3917 and td.calendar_schedule_id = t.calendar_schedule_id
3918 and td.block_id = t.block_id
3919 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3920 set t.other_scheduled = t.other_scheduled + ci.hours
3921 where sc.schedule_on = '2018-03-01'
3922 and sc.call_type_id = 12692
3923 and sc.staff_id = 20698
3924
3925 (144.6ms) -- current_hour_targets
3926 update buffer_schedules_793447700 as sc
3927 inner join buffer_counter_increments_793447700 as ci
3928 on sc.id = ci.schedule_id
3929 inner join buffer_current_hour_targets_793447700 as t
3930 on sc.staff_id = t.staff_id
3931 and sc.calendar_schedule_id = t.calendar_schedule_id
3932 and sc.period_offset = t.period_offset
3933 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3934 set t.scheduled = t.scheduled + ci.hours
3935 where sc.schedule_on = '2018-03-01'
3936 and sc.call_type_id = 12692
3937 and sc.staff_id = 20698
3938
3939 (144.2ms) -- cumulative_hour_targets
3940 update buffer_schedules_793447700 as sc
3941 inner join buffer_counter_increments_793447700 as ci
3942 on sc.id = ci.schedule_id
3943 and ci.affects_cumulative_targets = 1
3944 inner join buffer_cumulative_hour_targets_793447700 as t
3945 on sc.calendar_schedule_id = t.calendar_schedule_id
3946 and sc.staff_id = t.staff_id
3947 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
3948 set t.scheduled = t.scheduled + ci.hours
3949 where sc.schedule_on = '2018-03-01'
3950 and sc.call_type_id = 12692
3951 and sc.staff_id = 20698
3952
3953 (151.3ms) /* AssignmentLimitsPerPeriod */
3954 /* Unique Key: assignment_limit_id, staff_id, start_date */
3955 insert into buffer_staff_assignments_count_periods_793447700 (
3956 assignment_limit_id, staff_id, start_date, end_date, assignments_count
3957 )
3958 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
3959 1 as assignments_count
3960 from buffer_schedules_793447700 as sc
3961 inner join buffer_assignment_limit_call_types_793447700 as alct
3962 on sc.call_type_id = alct.call_type_id
3963 inner join assignment_limits as al
3964 on alct.assignment_limit_id = al.id
3965 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3966 and al.max_per_period_limit is not null
3967 inner join assign_staff_types as ast
3968 on sc.staff_id = ast.staff_id
3969 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
3970 inner join buffer_assignment_limit_staff_types_793447700 as alst
3971 on alct.assignment_limit_id = alst.assignment_limit_id
3972 and ast.staff_type_id = alst.staff_type_id
3973 inner join (
3974 select assignment_limit_id, start_date, end_date
3975 from buffer_linkage_assignments_count_periods_793447700
3976 group by assignment_limit_id, start_date, end_date
3977 order by null
3978 ) as lac
3979 on alct.assignment_limit_id = lac.assignment_limit_id
3980 and sc.schedule_on between lac.start_date and lac.end_date
3981 where sc.schedule_on = '2018-03-01'
3982 and sc.call_type_id = 12692
3983 and sc.staff_id = 20698
3984 on duplicate key update
3985 assignments_count = assignments_count + 1
3986
3987 (304.0ms) /* AssignmentLimitsPerWeek */
3988 /* Unique Key: assignment_limit_id, staff_id, start_date */
3989 insert into buffer_staff_assignments_count_weeks_793447700 (
3990 assignment_limit_id, staff_id, start_date, end_date, assignments_count
3991 )
3992 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
3993 1 as assignments_count
3994 from buffer_schedules_793447700 as sc
3995 inner join buffer_assignment_limit_call_types_793447700 as alct
3996 on sc.call_type_id = alct.call_type_id
3997 inner join assignment_limits as al
3998 on alct.assignment_limit_id = al.id
3999 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4000 and al.max_weekly_limit is not null
4001 inner join assign_staff_types as ast
4002 on sc.staff_id = ast.staff_id
4003 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4004 inner join buffer_assignment_limit_staff_types_793447700 as alst
4005 on alct.assignment_limit_id = alst.assignment_limit_id
4006 and ast.staff_type_id = alst.staff_type_id
4007 inner join (
4008 select assignment_limit_id, start_date, end_date
4009 from buffer_linkage_assignments_count_weeks_793447700
4010 group by assignment_limit_id, start_date, end_date
4011 order by null
4012 ) as lac
4013 on alct.assignment_limit_id = lac.assignment_limit_id
4014 and sc.schedule_on between lac.start_date and lac.end_date
4015 where sc.schedule_on = '2018-03-01'
4016 and sc.call_type_id = 12692
4017 and sc.staff_id = 20698
4018 on duplicate key update
4019 assignments_count = assignments_count + 1
4020
4021 (150.6ms) /* AssignmentLimitsRolling */
4022 /* Unique Key: assignment_limit_id, staff_id, start_date */
4023 insert into buffer_staff_assignments_count_rolling_793447700 (
4024 assignment_limit_id, staff_id, start_date, end_date, assignments_count
4025 )
4026 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
4027 1 as assignments_count
4028 from buffer_schedules_793447700 as sc
4029 inner join buffer_assignment_limit_call_types_793447700 as alct
4030 on sc.call_type_id = alct.call_type_id
4031 inner join assignment_limits as al
4032 on alct.assignment_limit_id = al.id
4033 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4034 and al.max_rolling_period is not null
4035 and al.max_rolling_limit is not null
4036 inner join assign_staff_types as ast
4037 on sc.staff_id = ast.staff_id
4038 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4039 inner join buffer_assignment_limit_staff_types_793447700 as alst
4040 on alct.assignment_limit_id = alst.assignment_limit_id
4041 and ast.staff_type_id = alst.staff_type_id
4042 inner join (
4043 select assignment_limit_id, start_date, end_date
4044 from buffer_linkage_assignments_count_rolling_793447700
4045 group by assignment_limit_id, start_date, end_date
4046 order by null
4047 ) as lac
4048 on alct.assignment_limit_id = lac.assignment_limit_id
4049 and sc.schedule_on between lac.start_date and lac.end_date
4050 where sc.schedule_on = '2018-03-01'
4051 and sc.call_type_id = 12692
4052 and sc.staff_id = 20698
4053 on duplicate key update
4054 assignments_count = assignments_count + 1
4055
4056 (146.6ms) /* AssignmentLimitsConsecutive */
4057 /* Unique Key: assignment_limit_id, staff_id, schedule_on, start_date */
4058 insert ignore into buffer_staff_assignments_count_consecutive_793447700 (
4059 assignment_limit_id, staff_id, schedule_on, start_date, end_date
4060 )
4061 select lac.assignment_limit_id, sc.staff_id, sc.schedule_on, lac.start_date, lac.end_date
4062 from buffer_schedules_793447700 as sc
4063 inner join buffer_assignment_limit_call_types_793447700 as alct
4064 on sc.call_type_id = alct.call_type_id
4065 inner join assignment_limits as al
4066 on alct.assignment_limit_id = al.id
4067 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4068 and al.max_consecutive_limit is not null
4069 inner join assign_staff_types as ast
4070 on sc.staff_id = ast.staff_id
4071 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4072 inner join buffer_assignment_limit_staff_types_793447700 as alst
4073 on alct.assignment_limit_id = alst.assignment_limit_id
4074 and ast.staff_type_id = alst.staff_type_id
4075 inner join (
4076 select assignment_limit_id, start_date, end_date
4077 from buffer_linkage_assignments_count_consecutive_793447700
4078 group by assignment_limit_id, start_date, end_date
4079 order by null
4080 ) as lac
4081 on alct.assignment_limit_id = lac.assignment_limit_id
4082 and sc.schedule_on between lac.start_date and lac.end_date
4083 where sc.schedule_on = '2018-03-01'
4084 and sc.call_type_id = 12692
4085 and sc.staff_id = 20698
4086
4087INFO : (02-27 08:17:47) scheduled for: plan unit: [id:16201808] [2018-03-01 - OB], Izhack Rabbe. DEPTH(1)
4088 NegativeLink Load (146.5ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
4089 (146.0ms) /* SpecializationOverlapRule */
4090 delete ca
4091 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
4092 inner join buffer_schedules_793447700 as sc
4093 on ca.schedule_id = sc.id
4094 inner join buffer_staff_specializations_793447700 as ssp
4095 on ca.staff_id = ssp.staff_id
4096 inner join max_applicable_call_types as mact
4097 on ssp.specialization_id = mact.specialization_id
4098 and sc.call_type_id = mact.call_type_id
4099 inner join buffer_specialization_daily_counts_793447700 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
4100 on ssp.specialization_id = sdc.specialization_id
4101 and sc.schedule_on = sdc.schedule_on
4102 and sdc.staff_id is null
4103 left join buffer_specialization_daily_counts_793447700 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
4104 on ssp.specialization_id = sdc_2.specialization_id
4105 and sc.schedule_on = sdc_2.schedule_on
4106 and ca.staff_id = sdc_2.staff_id
4107 where sdc.should_be_evaluated = 1
4108 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
4109
4110 (146.6ms) delete ca
4111 from buffer_combined_availabilities_793447700 as ca
4112 inner join (
4113 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
4114 sum(lac.assignments_count) as assignments_count
4115 from (
4116 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4117 from buffer_combined_availabilities_793447700 as ca
4118 inner join buffer_schedules_793447700 as sc
4119 on ca.schedule_id = sc.id
4120 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4121 order by null
4122 ) as ca_sc
4123 inner join buffer_linkage_assignments_count_weeks_793447700 as lac
4124 on ca_sc.linkage_instance_id = lac.linkage_instance_id
4125 and ca_sc.schedule_on = lac.schedule_on
4126 inner join buffer_assignment_limit_staff_types_793447700 as alst
4127 on lac.assignment_limit_id = alst.assignment_limit_id
4128 and ca_sc.staff_type_id = alst.staff_type_id
4129 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
4130 order by null
4131 ) as lac
4132 on ca.linkage_instance_id = lac.linkage_instance_id
4133 and ca.staff_id = lac.staff_id
4134 inner join assignment_limits as al
4135 on lac.assignment_limit_id = al.id
4136 left join buffer_staff_assignments_count_weeks_793447700 as sac
4137 on lac.assignment_limit_id = sac.assignment_limit_id
4138 and lac.staff_id = sac.staff_id
4139 and lac.start_date = sac.start_date
4140 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
4141
4142 (146.1ms) delete ca
4143 from buffer_combined_availabilities_793447700 as ca
4144 inner join (
4145 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
4146 sum(lac.assignments_count) as assignments_count
4147 from (
4148 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4149 from buffer_combined_availabilities_793447700 as ca
4150 inner join buffer_schedules_793447700 as sc
4151 on ca.schedule_id = sc.id
4152 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4153 order by null
4154 ) as ca_sc
4155 inner join buffer_linkage_assignments_count_periods_793447700 as lac
4156 on ca_sc.linkage_instance_id = lac.linkage_instance_id
4157 and ca_sc.schedule_on = lac.schedule_on
4158 inner join buffer_assignment_limit_staff_types_793447700 as alst
4159 on lac.assignment_limit_id = alst.assignment_limit_id
4160 and ca_sc.staff_type_id = alst.staff_type_id
4161 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
4162 order by null
4163 ) as lac
4164 on ca.linkage_instance_id = lac.linkage_instance_id
4165 and ca.staff_id = lac.staff_id
4166 inner join assignment_limits as al
4167 on lac.assignment_limit_id = al.id
4168 left join buffer_staff_assignments_count_periods_793447700 as sac
4169 on lac.assignment_limit_id = sac.assignment_limit_id
4170 and lac.staff_id = sac.staff_id
4171 and lac.start_date = sac.start_date
4172 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
4173
4174 (146.4ms) delete ca
4175 from buffer_combined_availabilities_793447700 as ca
4176 inner join (
4177 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
4178 sum(lac.assignments_count) as assignments_count
4179 from (
4180 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4181 from buffer_combined_availabilities_793447700 as ca
4182 inner join buffer_schedules_793447700 as sc
4183 on ca.schedule_id = sc.id
4184 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4185 order by null
4186 ) as ca_sc
4187 inner join buffer_linkage_assignments_count_rolling_793447700 as lac
4188 on ca_sc.linkage_instance_id = lac.linkage_instance_id
4189 and ca_sc.schedule_on = lac.schedule_on
4190 inner join buffer_assignment_limit_staff_types_793447700 as alst
4191 on lac.assignment_limit_id = alst.assignment_limit_id
4192 and ca_sc.staff_type_id = alst.staff_type_id
4193 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
4194 order by null
4195 ) as lac
4196 on ca.linkage_instance_id = lac.linkage_instance_id
4197 and ca.staff_id = lac.staff_id
4198 inner join assignment_limits as al
4199 on lac.assignment_limit_id = al.id
4200 left join buffer_staff_assignments_count_rolling_793447700 as sac
4201 on lac.assignment_limit_id = sac.assignment_limit_id
4202 and lac.staff_id = sac.staff_id
4203 and lac.start_date = sac.start_date
4204 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
4205
4206 (167.4ms) delete ca
4207 from buffer_combined_availabilities_793447700 as ca
4208 inner join (
4209 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
4210 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
4211 from (
4212 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4213 from buffer_combined_availabilities_793447700 as ca
4214 inner join buffer_schedules_793447700 as sc
4215 on ca.schedule_id = sc.id
4216 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
4217 order by null
4218 ) as ca_sc
4219 inner join buffer_linkage_assignments_count_consecutive_793447700 as lac
4220 on ca_sc.linkage_instance_id = lac.linkage_instance_id
4221 and ca_sc.schedule_on = lac.schedule_on
4222 inner join buffer_assignment_limit_staff_types_793447700 as alst
4223 on lac.assignment_limit_id = alst.assignment_limit_id
4224 and ca_sc.staff_type_id = alst.staff_type_id
4225 left join buffer_staff_assignments_count_consecutive_793447700 as sac
4226 on lac.assignment_limit_id = sac.assignment_limit_id
4227 and ca_sc.staff_id = sac.staff_id
4228 and lac.schedule_on = sac.schedule_on
4229 and lac.start_date = sac.start_date
4230 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
4231 order by null
4232 ) as lac
4233 on ca.linkage_instance_id = lac.linkage_instance_id
4234 and ca.staff_id = lac.staff_id
4235 inner join assignment_limits as al
4236 on lac.assignment_limit_id = al.id
4237 left join (
4238 select assignment_limit_id, staff_id, start_date,
4239 count(schedule_on) as assignments_count
4240 from buffer_staff_assignments_count_consecutive_793447700
4241 group by assignment_limit_id, staff_id, start_date
4242 ) as sac
4243 on lac.assignment_limit_id = sac.assignment_limit_id
4244 and lac.staff_id = sac.staff_id
4245 and lac.start_date = sac.start_date
4246 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
4247
4248 (149.9ms) /* CumulativeWeeklyCallTypeTargetsRule */
4249 delete ca
4250 from buffer_combined_availabilities_793447700 as ca
4251 inner join buffer_schedules_793447700 as sc
4252 on ca.schedule_id = sc.id
4253 inner join buffer_target_assignments_793447700 as ta
4254 on sc.call_type_id = ta.call_type_id
4255 inner join buffer_linkage_call_type_targets_793447700 as lctt
4256 on sc.linkage_instance_id = lctt.linkage_instance_id
4257 and sc.calendar_schedule_id = lctt.calendar_schedule_id
4258 and lctt.period_offset = -1
4259 and ta.assignment_id = lctt.assignment_id
4260 and ta.assignment_type = lctt.assignment_type
4261 inner join buffer_cumulative_weekly_ct_targets_793447700 as ctt
4262 on ca.staff_id = ctt.staff_id
4263 and sc.calendar_schedule_id = ctt.calendar_schedule_id
4264 and ta.assignment_id = ctt.assignment_id
4265 and ta.assignment_type = ctt.assignment_type
4266 where ((round(ctt.raw_target, 2)) - ctt.scheduled - (lctt.weekday_targets + lctt.weekend_targets)) < 0
4267
4268 (150.2ms) /* MaxAssignmentsRule */
4269 delete ca
4270 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
4271 inner join buffer_schedules_793447700 as sc
4272 on ca.schedule_id = sc.id
4273 inner join buffer_linkage_call_type_assignments_793447700 as lcta use index (ix_buffer_linkage_call_type_assignments_composite_1)
4274 on sc.linkage_instance_id = lcta.linkage_instance_id
4275 and sc.calendar_schedule_id = lcta.calendar_schedule_id
4276 and sc.period_offset = lcta.period_offset
4277 and sc.call_type_id = lcta.call_type_id
4278 left join buffer_call_type_assignments_793447700 as cta use index (ix_buffer_call_type_assignments_composite_1)
4279 on ca.staff_id = cta.staff_id
4280 and sc.calendar_schedule_id = cta.calendar_schedule_id
4281 and sc.period_offset = cta.period_offset
4282 and sc.call_type_id = cta.call_type_id
4283 where
4284 /* weekday checkbox */
4285 (
4286 lcta.max_weekday_assignments is not null
4287 and (ifnull(cta.weekday_assignments, 0) + lcta.weekday_assignments) > lcta.max_weekday_assignments
4288 )
4289 /* weekend checkbox */
4290 or (
4291 lcta.max_weekend_assignments is not null
4292 and (ifnull(cta.weekend_assignments, 0) + lcta.weekend_assignments) > lcta.max_weekend_assignments
4293 )
4294
4295 (146.3ms) /* NegativeLinkRule */
4296 delete ca
4297 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
4298 inner join buffer_negative_relations_793447700 as nr use index (ix_buffer_negative_relations_composite_1)
4299 on ca.schedule_id = nr.schedule_id
4300 and ca.staff_id = nr.staff_id
4301
4302 (146.2ms) /* BrokenLinkageRule */
4303 truncate table buffer_combined_availability_linkages_793447700
4304
4305 (149.3ms) insert into buffer_combined_availability_linkages_793447700 (
4306 staff_id, linkage_instance_id, slots_count, is_primary, has_violations
4307 )
4308 select staff_id, linkage_instance_id,
4309 count(1) as slots_count,
4310 min(is_primary) as is_primary,
4311 max(has_violations) as has_violations
4312 from buffer_combined_availabilities_793447700 use index (ix_buffer_combined_availabilities_composite_2)
4313 group by staff_id, linkage_instance_id
4314 order by null
4315
4316 (147.9ms) delete ca
4317 from buffer_combined_availabilities_793447700 as ca use index (ix_buffer_combined_availabilities_composite_1)
4318 inner join buffer_schedules_793447700 as sc
4319 on ca.schedule_id = sc.id
4320 inner join buffer_linkages_793447700 as l
4321 on sc.linkage_instance_id = l.linkage_instance_id
4322 inner join buffer_combined_availability_linkages_793447700 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
4323 on ca.staff_id = ls_1.staff_id
4324 and sc.linkage_instance_id = ls_1.linkage_instance_id
4325 left join buffer_schedule_linkages_793447700 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
4326 on ca.staff_id = ls_2.staff_id
4327 and sc.linkage_instance_id = ls_2.linkage_instance_id
4328 where (ls_1.slots_count + ifnull(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */
4329
4330 (162.9ms) select straight_join
4331 sc.schedule_on,
4332 sc.call_type_id,
4333 count(distinct ca.staff_id) as available_members_count,
4334 group_concat(
4335 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
4336 order by ls_1.is_primary desc, ca.staff_id
4337 separator ', '
4338 ) as available_members_ids,
4339 sc.linkage_instance_id,
4340 l.slots_count_total as linkage_slots_count
4341 from buffer_combined_availabilities_793447700 as ca
4342 inner join buffer_schedules_793447700 as sc
4343 on ca.schedule_id = sc.id
4344 inner join buffer_linkages_793447700 as l
4345 on sc.linkage_instance_id = l.linkage_instance_id
4346 inner join buffer_combined_availability_linkages_793447700 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
4347 on ca.staff_id = ls_1.staff_id
4348 and sc.linkage_instance_id = ls_1.linkage_instance_id
4349 and ls_1.has_violations = 0
4350 where sc.should_be_evaluated = 1
4351 and sc.should_be_populated = 1
4352 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
4353 order by available_members_count, sc.schedule_on, sc.display_order
4354 limit 1
4355
4356 (145.7ms) truncate table buffer_rule_scores_793447700
4357
4358 (146.2ms) truncate table buffer_rule_calc_filters_793447700
4359
4360 (150.2ms) insert into buffer_rule_calc_filters_793447700 (
4361 linkage_instance_id, staff_id
4362 ) values (18201808, 20695),(18201808, 20696),(18201808, 20697),(18201808, 20699),(18201808, 20701),(18201808, 20704),(18201808, 20705),(18201808, 20706)
4363
4364 (152.0ms) /* CumulativeWeeklyCallTypeTargetsRule */
4365 insert into buffer_rule_scores_793447700 (
4366 linkage_instance_id, schedule_id, staff_id, rule_name, value
4367 )
4368 select straight_join
4369 b_schedules.linkage_instance_id, b_schedules.id as schedule_id, availabilities.staff_id, 'CumulativeWeeklyCallTypeTargetsRule' as rule_name,
4370 (
4371 (b_targets.raw_target /* depends on a cumulative targets period */ - b_targets.scheduled) /
4372 b_targets.raw_target /* depends on a cumulative targets period */
4373 ) as value
4374 from buffer_rule_calc_filters_793447700 as filters
4375 inner join buffer_schedules_793447700 as b_schedules
4376 on filters.linkage_instance_id = b_schedules.linkage_instance_id
4377 inner join buffer_combined_availabilities_793447700 as availabilities use index (ix_buffer_combined_availabilities_composite_1)
4378 on b_schedules.id = availabilities.schedule_id
4379 and filters.staff_id = availabilities.staff_id
4380 inner join buffer_target_assignments_793447700 as target_assignments
4381 on b_schedules.call_type_id = target_assignments.call_type_id
4382 inner join buffer_cumulative_weekly_ct_targets_793447700 as b_targets
4383 on availabilities.staff_id = b_targets.staff_id
4384 and b_schedules.calendar_schedule_id = b_targets.calendar_schedule_id
4385 and target_assignments.assignment_id = b_targets.assignment_id
4386 and target_assignments.assignment_type = b_targets.assignment_type
4387 order by null
4388
4389 (145.5ms) truncate table buffer_rule_scores_with_corrections_793447700
4390
4391 (146.9ms) insert into buffer_rule_scores_with_corrections_793447700 (
4392 linkage_instance_id, schedule_id, staff_id, rule_name, value
4393 )
4394 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
4395 ifnull(rule_scores.value, max_values.max_value + 1) as value
4396 from buffer_rule_scores_793447700 as rule_scores
4397 inner join (
4398 select rule_name, max(value) as max_value
4399 from buffer_rule_scores_793447700
4400 group by rule_name
4401 order by null
4402 ) as max_values
4403 on rule_scores.rule_name = max_values.rule_name
4404 order by null
4405
4406 (146.4ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
4407 from (
4408 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
4409 sum(
4410 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
4411 rules.weight *
4412 total_values.slots_count
4413 ) as slot_scores
4414 from buffer_rule_scores_with_corrections_793447700 as rule_scores
4415 inner join (
4416 select rule_name, sum(abs(value)) as total_value,
4417 count(distinct schedule_id) as slots_count
4418 from buffer_rule_scores_with_corrections_793447700
4419 group by rule_name
4420 order by null
4421 ) as total_values
4422 on rule_scores.rule_name = total_values.rule_name
4423 inner join rules
4424 on rule_scores.rule_name = rules.type
4425 where rules.rule_set_id = 1527
4426 and rules.applied = 1
4427 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
4428 order by null
4429 ) as staff_scores
4430 group by linkage_instance_id, staff_id
4431 order by round(avg(slot_scores), 5) desc, rand()
4432 limit 1
4433
4434 PublishedBlock Load (144.7ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 319 AND ('2018-03-01' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1381 ORDER BY `published_blocks`.`id` ASC LIMIT 1
4435 (144.1ms) BEGIN
4436 (146.8ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
4437 FROM call_types AS ct
4438 INNER JOIN calendar_schedules AS cs
4439 ON ct.calendar_schedule_id = cs.id
4440 INNER JOIN blocks AS b
4441 ON b.client_id = ct.client_id
4442 AND b.block_schedule_id = cs.block_schedule_id
4443 AND '2018-03-01' BETWEEN b.start_date AND b.end_date
4444 INNER JOIN published_blocks AS pb
4445 ON pb.client_id = ct.client_id
4446 AND pb.block_id = b.id
4447 AND pb.calendar_schedule_id = cs.id
4448 LEFT JOIN published_block_call_types AS pbct
4449 ON pbct.client_id = ct.client_id
4450 AND pbct.assignment_id = ct.id
4451 AND pbct.assignment_type = 'CallType'
4452 AND pbct.block_id = b.id
4453 AND (
4454 (1 << (DAYOFWEEK('2018-03-01') - 1) & pbct.days_of_week_mask) > 0
4455 )
4456 WHERE ct.id = 12694
4457 AND (
4458 (
4459 pb.assignment_ids IS NULL
4460 AND (1 << (DAYOFWEEK('2018-03-01') - 1) & pb.days_of_week_mask) > 0
4461 )
4462 OR pbct.id IS NOT NULL
4463 )
4464
4465 CallType Load (147.0ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12694 LIMIT 1
4466 Client Load (150.2ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
4467 Staff Load (146.0ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20706 LIMIT 1
4468 Schedule Exists (150.3ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12694 AND `schedules`.`client_id` = 319 AND `schedules`.`schedule_on` = '2018-03-01' LIMIT 1
4469 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-01', '2018-02-27 08:18:04', '2018-02-27 08:18:04', 0, 5, 'auto_populate_step', 166)
4470 VisibleAssignment Load (145.3ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 319 AND `visible_assignments`.`date` = '2018-03-01' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12694 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
4471 (148.5ms) COMMIT
4472FLUSHED: nil:nil
4473 (146.2ms) UPDATE buffer_schedules_793447700
4474 SET staff_id = 20706,
4475 should_be_populated = 0,
4476 should_be_evaluated = 0
4477 WHERE
4478 call_type_id = 12694
4479 AND schedule_on = '2018-03-01'
4480
4481 (146.4ms) delete from buffer_combined_availabilities_793447700
4482 where linkage_instance_id = 18201808
4483
4484 (146.0ms) UPDATE buffer_rule_calc_schedules_793447700
4485 SET staff_id = 20706
4486 WHERE call_type_id = 12694
4487 AND schedule_on = '2018-03-01'
4488
4489 (148.2ms) INSERT INTO buffer_rule_calc_schedules_793447700
4490 SET staff_id = 20706,
4491 call_type_id = 12694,
4492 schedule_on = '2018-03-01',
4493 day_of_week_mask = 16
4494
4495 (144.9ms) delete from buffer_linkage_targets_793447700
4496 where linkage_instance_id = 18201808
4497
4498 (156.5ms) delete from buffer_linkage_call_type_targets_793447700
4499 where linkage_instance_id = 18201808
4500
4501 (165.6ms) delete from buffer_linkage_point_targets_793447700
4502 where linkage_instance_id = 18201808
4503
4504 (164.1ms) delete from buffer_linkage_hour_targets_793447700
4505 where linkage_instance_id = 18201808
4506
4507 (168.3ms) delete from buffer_linkage_call_type_assignments_793447700
4508 where linkage_instance_id = 18201808
4509
4510 (169.3ms) delete from buffer_linkage_call_days_targets_793447700
4511 where linkage_instance_id = 18201808
4512
4513 (150.2ms) delete from buffer_linkage_assigned_days_793447700
4514 where linkage_instance_id = 18201808
4515
4516 (153.8ms) delete from buffer_linkage_dow_targets_793447700
4517 where linkage_instance_id = 18201808
4518
4519 (144.7ms) update buffer_schedules_793447700 as sc
4520 inner join call_types as ct
4521 on sc.call_type_id = ct.id
4522 and ct.active = 1
4523 inner join buffer_call_type_assignments_793447700 as cta
4524 on sc.staff_id = cta.staff_id
4525 and sc.calendar_schedule_id = cta.calendar_schedule_id
4526 and sc.period_offset = cta.period_offset
4527 and sc.call_type_id = cta.call_type_id
4528 set cta.weekday_assignments = cta.weekday_assignments + if((sc.day_of_week_mask & 62 > 0), 1, 0),
4529 cta.weekend_assignments = cta.weekend_assignments + if((sc.day_of_week_mask & 65 > 0), 1, 0)
4530 where sc.schedule_on = '2018-03-01'
4531 and sc.call_type_id = 12694
4532 and sc.staff_id = 20706
4533
4534 (157.6ms) INSERT INTO buffer_call_type_assignments_793447700 (
4535 staff_id, calendar_schedule_id, period_offset, call_type_id, weekday_assignments, weekend_assignments
4536 )
4537 SELECT sc.staff_id,
4538 sc.calendar_schedule_id,
4539 sc.period_offset,
4540 sc.call_type_id,
4541 if((sc.day_of_week_mask & 62 > 0), 1, 0) AS weekday_assignments,
4542 if((sc.day_of_week_mask & 65 > 0), 1, 0) AS weekend_assignments
4543 FROM
4544 buffer_schedules_793447700 as sc
4545 WHERE
4546 sc.staff_id = 20706
4547 AND sc.schedule_on = '2018-03-01'
4548 AND sc.call_type_id = 12694
4549
4550 (143.9ms) UPDATE buffer_staff_daily_counts_793447700
4551 SET slots_count = slots_count + 1
4552 WHERE
4553 staff_id = 20706
4554 AND schedule_on = '2018-03-01'
4555 AND linkage_instance_id is null
4556
4557 (147.2ms) INSERT INTO buffer_staff_daily_counts_793447700
4558 SET slots_count = 1,
4559 staff_id = 20706,
4560 schedule_on = '2018-03-01',
4561 linkage_instance_id = null
4562
4563 (143.7ms) UPDATE buffer_staff_daily_counts_793447700
4564 SET slots_count = slots_count + 1
4565 WHERE
4566 staff_id = 20706
4567 AND schedule_on = '2018-03-01'
4568 AND linkage_instance_id = 18201808
4569
4570 (161.3ms) INSERT INTO buffer_staff_daily_counts_793447700
4571 SET slots_count = 1,
4572 staff_id = 20706,
4573 schedule_on = '2018-03-01',
4574 linkage_instance_id = 18201808
4575
4576 (144.7ms) update buffer_staff_specializations_793447700 as st_sp
4577 inner join max_applicable_call_types as mact
4578 on st_sp.specialization_id = mact.specialization_id
4579 and mact.call_type_id = 12694
4580 inner join buffer_specialization_daily_counts_793447700 as sp_dc
4581 on st_sp.specialization_id = sp_dc.specialization_id
4582 and sp_dc.staff_id is null
4583 and sp_dc.schedule_on = '2018-03-01'
4584 left join buffer_specialization_daily_counts_793447700 as sp_dc_2
4585 on st_sp.specialization_id = sp_dc_2.specialization_id
4586 and st_sp.staff_id = sp_dc_2.staff_id
4587 and sp_dc_2.schedule_on = '2018-03-01'
4588 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
4589 where st_sp.staff_id = 20706
4590 and sp_dc_2.id is null
4591
4592 (145.0ms) insert into buffer_specialization_daily_counts_793447700 (
4593 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
4594 )
4595 select sp.id as specialization_id,
4596 20706 as staff_id,
4597 '2018-03-01' as schedule_on,
4598 1 as staffs_scheduled,
4599 sp.max_scheduled,
4600 sp_dc.should_be_evaluated
4601 from buffer_staff_specializations_793447700 as st_sp
4602 inner join specializations as sp
4603 on st_sp.specialization_id = sp.id
4604 inner join max_applicable_call_types as mact
4605 on st_sp.specialization_id = mact.specialization_id
4606 and mact.call_type_id = 12694
4607 inner join buffer_specialization_daily_counts_793447700 as sp_dc
4608 on st_sp.specialization_id = sp_dc.specialization_id
4609 and sp_dc.staff_id is null
4610 and sp_dc.schedule_on = '2018-03-01'
4611 left join buffer_specialization_daily_counts_793447700 as sp_dc_2
4612 on st_sp.specialization_id = sp_dc_2.specialization_id
4613 and st_sp.staff_id = sp_dc_2.staff_id
4614 and sp_dc_2.schedule_on = '2018-03-01'
4615 where st_sp.staff_id = 20706
4616 and sp_dc_2.id is null
4617
4618 (143.9ms) UPDATE buffer_negative_relations_793447700
4619 SET staff_id = 20706
4620 WHERE affected_by_schedule_id = (
4621 SELECT id
4622 FROM buffer_schedules_793447700
4623 WHERE schedule_on = '2018-03-01'
4624 AND call_type_id = 12694
4625 )
4626
4627 (153.4ms) UPDATE buffer_staff_assigned_days_793447700 as sad
4628 inner join call_types as ct
4629 on ct.id = 12694
4630 and ct.unassigned_day = 0
4631 SET sad.is_assigned = 1
4632 WHERE sad.staff_id = 20706
4633 AND sad.schedule_on = '2018-03-01'
4634
4635 (144.5ms) -- current_oa_targets
4636 update buffer_schedules_793447700 as sc
4637 inner join buffer_counter_increments_793447700 as ci
4638 on sc.id = ci.schedule_id
4639 inner join buffer_current_oa_targets_793447700 as t
4640 on sc.staff_id = t.staff_id
4641 and sc.calendar_schedule_id = t.calendar_schedule_id
4642 and sc.period_offset = t.period_offset
4643 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4644 set t.scheduled = t.scheduled + ci.calls
4645 where sc.schedule_on = '2018-03-01'
4646 and sc.call_type_id = 12694
4647 and sc.staff_id = 20706
4648
4649 (157.1ms) -- cumulative_oa_targets
4650 update buffer_schedules_793447700 as sc
4651 inner join buffer_counter_increments_793447700 as ci
4652 on sc.id = ci.schedule_id
4653 and ci.affects_cumulative_targets = 1
4654 inner join buffer_cumulative_oa_targets_793447700 as t
4655 on sc.calendar_schedule_id = t.calendar_schedule_id
4656 and sc.staff_id = t.staff_id
4657 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4658 set t.scheduled = t.scheduled + ci.calls
4659 where sc.schedule_on = '2018-03-01'
4660 and sc.call_type_id = 12694
4661 and sc.staff_id = 20706
4662
4663 (147.1ms) -- current_cd_targets
4664 update buffer_schedules_793447700 as sc
4665 inner join buffer_counter_increments_793447700 as ci
4666 on sc.id = ci.schedule_id
4667 inner join buffer_target_assignments_793447700 as ta
4668 on sc.call_type_id = ta.call_type_id
4669 inner join buffer_current_cd_targets_793447700 as t
4670 on sc.staff_id = t.staff_id
4671 and sc.calendar_schedule_id = t.calendar_schedule_id
4672 and sc.period_offset = t.period_offset
4673 and ta.assignment_id = t.assignment_id
4674 and ta.assignment_type = t.assignment_type
4675 and sc.day_of_week_mask = t.day_of_week_mask
4676 set t.scheduled = t.scheduled + ci.calls
4677 where sc.schedule_on = '2018-03-01'
4678 and sc.call_type_id = 12694
4679 and sc.staff_id = 20706
4680
4681 (153.1ms) -- cumulative_cd_targets
4682 update buffer_schedules_793447700 as sc
4683 inner join buffer_counter_increments_793447700 as ci
4684 on sc.id = ci.schedule_id
4685 and ci.affects_cumulative_targets = 1
4686 inner join buffer_target_assignments_793447700 as ta
4687 on sc.call_type_id = ta.call_type_id
4688 inner join buffer_cumulative_cd_targets_793447700 as t
4689 on sc.calendar_schedule_id = t.calendar_schedule_id
4690 and sc.staff_id = t.staff_id
4691 and ta.assignment_id = t.assignment_id
4692 and ta.assignment_type = t.assignment_type
4693 and sc.day_of_week_mask = t.day_of_week_mask
4694 set t.scheduled = t.scheduled + ci.calls
4695 where sc.schedule_on = '2018-03-01'
4696 and sc.call_type_id = 12694
4697 and sc.staff_id = 20706
4698
4699 (144.9ms) -- current_ct_targets
4700 update buffer_schedules_793447700 as sc
4701 inner join buffer_counter_increments_793447700 as ci
4702 on sc.id = ci.schedule_id
4703 inner join buffer_target_assignments_793447700 as ta
4704 on sc.call_type_id = ta.call_type_id
4705 inner join buffer_current_ct_targets_793447700 as t
4706 on sc.staff_id = t.staff_id
4707 and sc.calendar_schedule_id = t.calendar_schedule_id
4708 and sc.period_offset = t.period_offset
4709 and ta.assignment_id = t.assignment_id
4710 and ta.assignment_type = t.assignment_type
4711 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4712 set t.scheduled = t.scheduled + ci.calls
4713 where sc.schedule_on = '2018-03-01'
4714 and sc.call_type_id = 12694
4715 and sc.staff_id = 20706
4716
4717 (152.2ms) -- cumulative_ct_targets
4718 update buffer_schedules_793447700 as sc
4719 inner join buffer_counter_increments_793447700 as ci
4720 on sc.id = ci.schedule_id
4721 and ci.affects_cumulative_targets = 1
4722 inner join buffer_target_assignments_793447700 as ta
4723 on sc.call_type_id = ta.call_type_id
4724 inner join buffer_cumulative_ct_targets_793447700 as t
4725 on sc.calendar_schedule_id = t.calendar_schedule_id
4726 and sc.staff_id = t.staff_id
4727 and ta.assignment_id = t.assignment_id
4728 and ta.assignment_type = t.assignment_type
4729 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4730 set t.scheduled = t.scheduled + ci.calls
4731 where sc.schedule_on = '2018-03-01'
4732 and sc.call_type_id = 12694
4733 and sc.staff_id = 20706
4734
4735 (147.1ms) -- current_dow_targets
4736 update buffer_schedules_793447700 as sc
4737 inner join buffer_counter_increments_793447700 as ci
4738 on sc.id = ci.schedule_id
4739 inner join buffer_current_dow_targets_793447700 as t
4740 on sc.staff_id = t.staff_id
4741 and sc.calendar_schedule_id = t.calendar_schedule_id
4742 and sc.period_offset = t.period_offset
4743 and dayofweek(sc.schedule_on) - 1 = t.wday
4744 set t.scheduled = t.scheduled + ci.calls
4745 where sc.schedule_on = '2018-03-01'
4746 and sc.call_type_id = 12694
4747 and sc.staff_id = 20706
4748
4749 (150.3ms) -- cumulative_dow_targets
4750 update buffer_schedules_793447700 as sc
4751 inner join buffer_counter_increments_793447700 as ci
4752 on sc.id = ci.schedule_id
4753 and ci.affects_cumulative_targets = 1
4754 inner join buffer_cumulative_dow_targets_793447700 as t
4755 on sc.calendar_schedule_id = t.calendar_schedule_id
4756 and sc.staff_id = t.staff_id
4757 and dayofweek(sc.schedule_on) - 1 = t.wday
4758 set t.scheduled = t.scheduled + ci.calls
4759 where sc.schedule_on = '2018-03-01'
4760 and sc.call_type_id = 12694
4761 and sc.staff_id = 20706
4762
4763 (144.5ms) -- current and cumulative point targets
4764
4765 update buffer_schedules_793447700 as sc
4766 inner join buffer_counter_increments_793447700 as ci
4767 on sc.id = ci.schedule_id
4768 inner join buffer_point_targets_793447700 as t
4769 on sc.staff_id = t.staff_id
4770 and sc.calendar_schedule_id = t.calendar_schedule_id
4771 and sc.block_id = t.block_id
4772 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4773 left join buffer_staff_rotation_points_793447700 as srp
4774 on sc.schedule_on = srp.schedule_on
4775 and sc.staff_id = srp.staff_id
4776 and sc.calendar_schedule_id = srp.calendar_schedule_id
4777 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
4778 where sc.schedule_on = '2018-03-01'
4779 and sc.call_type_id = 12694
4780 and sc.staff_id = 20706
4781
4782 (155.8ms) update buffer_schedules_793447700 as sc
4783 inner join buffer_counter_increments_793447700 as ci
4784 on sc.id = ci.schedule_id
4785 inner join buffer_target_dates as td
4786 on td.session_id = 793447700
4787 and td.client_id = 319
4788 and sc.calendar_schedule_id != td.calendar_schedule_id
4789 and sc.schedule_on between td.start_date and td.end_date
4790 inner join buffer_point_targets_793447700 as t
4791 on sc.staff_id = t.staff_id
4792 and td.calendar_schedule_id = t.calendar_schedule_id
4793 and td.block_id = t.block_id
4794 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4795 left join buffer_staff_rotation_points_793447700 as srp
4796 on sc.schedule_on = srp.schedule_on
4797 and sc.staff_id = srp.staff_id
4798 and td.calendar_schedule_id = srp.calendar_schedule_id
4799 set t.scheduled = t.scheduled - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0),
4800 t.other_scheduled = t.other_scheduled + ci.points
4801 where sc.schedule_on = '2018-03-01'
4802 and sc.call_type_id = 12694
4803 and sc.staff_id = 20706
4804
4805 (144.6ms) -- current_point_targets
4806 update buffer_schedules_793447700 as sc
4807 inner join buffer_counter_increments_793447700 as ci
4808 on sc.id = ci.schedule_id
4809 inner join buffer_current_point_targets_793447700 as t
4810 on sc.staff_id = t.staff_id
4811 and sc.calendar_schedule_id = t.calendar_schedule_id
4812 and sc.period_offset = t.period_offset
4813 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4814 left join buffer_staff_rotation_points_793447700 as srp
4815 on sc.schedule_on = srp.schedule_on
4816 and sc.staff_id = srp.staff_id
4817 and sc.calendar_schedule_id = srp.calendar_schedule_id
4818 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
4819 where sc.schedule_on = '2018-03-01'
4820 and sc.call_type_id = 12694
4821 and sc.staff_id = 20706
4822
4823 (150.9ms) -- cumulative_point_targets
4824 update buffer_schedules_793447700 as sc
4825 inner join buffer_counter_increments_793447700 as ci
4826 on sc.id = ci.schedule_id
4827 and ci.affects_cumulative_targets = 1
4828 inner join buffer_cumulative_point_targets_793447700 as t
4829 on sc.calendar_schedule_id = t.calendar_schedule_id
4830 and sc.staff_id = t.staff_id
4831 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4832 left join buffer_staff_rotation_points_793447700 as srp
4833 on sc.schedule_on = srp.schedule_on
4834 and sc.staff_id = srp.staff_id
4835 and sc.calendar_schedule_id = srp.calendar_schedule_id
4836 set t.scheduled = t.scheduled + ci.points - if(ci.affects_rotations = 1, ifnull(srp.points, 0.0), 0.0)
4837 where sc.schedule_on = '2018-03-01'
4838 and sc.call_type_id = 12694
4839 and sc.staff_id = 20706
4840
4841 (145.5ms) update buffer_schedules_793447700 as sc
4842 inner join buffer_counter_increments_793447700 as ci
4843 on sc.id = ci.schedule_id
4844 inner join buffer_staff_rotation_points_793447700 as srp
4845 on sc.schedule_on = srp.schedule_on
4846 and sc.staff_id = srp.staff_id
4847 set srp.points = if(ci.affects_rotations = 1, 0.0, srp.points)
4848 where sc.schedule_on = '2018-03-01'
4849 and sc.call_type_id = 12694
4850 and sc.staff_id = 20706
4851
4852 (164.2ms) -- current and cumulative hour targets
4853
4854 update buffer_schedules_793447700 as sc
4855 inner join buffer_counter_increments_793447700 as ci
4856 on sc.id = ci.schedule_id
4857 inner join buffer_hour_targets_793447700 as t
4858 on sc.staff_id = t.staff_id
4859 and sc.calendar_schedule_id = t.calendar_schedule_id
4860 and sc.block_id = t.block_id
4861 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4862 set t.scheduled = t.scheduled + ci.hours
4863 where sc.schedule_on = '2018-03-01'
4864 and sc.call_type_id = 12694
4865 and sc.staff_id = 20706
4866
4867 (144.8ms) update buffer_schedules_793447700 as sc
4868 inner join buffer_counter_increments_793447700 as ci
4869 on sc.id = ci.schedule_id
4870 inner join buffer_target_dates as td
4871 on td.session_id = 793447700
4872 and td.client_id = 319
4873 and sc.calendar_schedule_id != td.calendar_schedule_id
4874 and sc.schedule_on between td.start_date and td.end_date
4875 inner join buffer_hour_targets_793447700 as t
4876 on sc.staff_id = t.staff_id
4877 and td.calendar_schedule_id = t.calendar_schedule_id
4878 and td.block_id = t.block_id
4879 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4880 set t.other_scheduled = t.other_scheduled + ci.hours
4881 where sc.schedule_on = '2018-03-01'
4882 and sc.call_type_id = 12694
4883 and sc.staff_id = 20706
4884
4885 (145.5ms) -- current_hour_targets
4886 update buffer_schedules_793447700 as sc
4887 inner join buffer_counter_increments_793447700 as ci
4888 on sc.id = ci.schedule_id
4889 inner join buffer_current_hour_targets_793447700 as t
4890 on sc.staff_id = t.staff_id
4891 and sc.calendar_schedule_id = t.calendar_schedule_id
4892 and sc.period_offset = t.period_offset
4893 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4894 set t.scheduled = t.scheduled + ci.hours
4895 where sc.schedule_on = '2018-03-01'
4896 and sc.call_type_id = 12694
4897 and sc.staff_id = 20706
4898
4899 (145.4ms) -- cumulative_hour_targets
4900 update buffer_schedules_793447700 as sc
4901 inner join buffer_counter_increments_793447700 as ci
4902 on sc.id = ci.schedule_id
4903 and ci.affects_cumulative_targets = 1
4904 inner join buffer_cumulative_hour_targets_793447700 as t
4905 on sc.calendar_schedule_id = t.calendar_schedule_id
4906 and sc.staff_id = t.staff_id
4907 and if((sc.day_of_week_mask & 62 > 0), 0, 1) = t.is_weekend
4908 set t.scheduled = t.scheduled + ci.hours
4909 where sc.schedule_on = '2018-03-01'
4910 and sc.call_type_id = 12694
4911 and sc.staff_id = 20706
4912
4913 (307.6ms) /* AssignmentLimitsPerPeriod */
4914 /* Unique Key: assignment_limit_id, staff_id, start_date */
4915 insert into buffer_staff_assignments_count_periods_793447700 (
4916 assignment_limit_id, staff_id, start_date, end_date, assignments_count
4917 )
4918 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
4919 1 as assignments_count
4920 from buffer_schedules_793447700 as sc
4921 inner join buffer_assignment_limit_call_types_793447700 as alct
4922 on sc.call_type_id = alct.call_type_id
4923 inner join assignment_limits as al
4924 on alct.assignment_limit_id = al.id
4925 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4926 and al.max_per_period_limit is not null
4927 inner join assign_staff_types as ast
4928 on sc.staff_id = ast.staff_id
4929 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4930 inner join buffer_assignment_limit_staff_types_793447700 as alst
4931 on alct.assignment_limit_id = alst.assignment_limit_id
4932 and ast.staff_type_id = alst.staff_type_id
4933 inner join (
4934 select assignment_limit_id, start_date, end_date
4935 from buffer_linkage_assignments_count_periods_793447700
4936 group by assignment_limit_id, start_date, end_date
4937 order by null
4938 ) as lac
4939 on alct.assignment_limit_id = lac.assignment_limit_id
4940 and sc.schedule_on between lac.start_date and lac.end_date
4941 where sc.schedule_on = '2018-03-01'
4942 and sc.call_type_id = 12694
4943 and sc.staff_id = 20706
4944 on duplicate key update
4945 assignments_count = assignments_count + 1
4946
4947 (163.3ms) /* AssignmentLimitsPerWeek */
4948 /* Unique Key: assignment_limit_id, staff_id, start_date */
4949 insert into buffer_staff_assignments_count_weeks_793447700 (
4950 assignment_limit_id, staff_id, start_date, end_date, assignments_count
4951 )
4952 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
4953 1 as assignments_count
4954 from buffer_schedules_793447700 as sc
4955 inner join buffer_assignment_limit_call_types_793447700 as alct
4956 on sc.call_type_id = alct.call_type_id
4957 inner join assignment_limits as al
4958 on alct.assignment_limit_id = al.id
4959 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4960 and al.max_weekly_limit is not null
4961 inner join assign_staff_types as ast
4962 on sc.staff_id = ast.staff_id
4963 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4964 inner join buffer_assignment_limit_staff_types_793447700 as alst
4965 on alct.assignment_limit_id = alst.assignment_limit_id
4966 and ast.staff_type_id = alst.staff_type_id
4967 inner join (
4968 select assignment_limit_id, start_date, end_date
4969 from buffer_linkage_assignments_count_weeks_793447700
4970 group by assignment_limit_id, start_date, end_date
4971 order by null
4972 ) as lac
4973 on alct.assignment_limit_id = lac.assignment_limit_id
4974 and sc.schedule_on between lac.start_date and lac.end_date
4975 where sc.schedule_on = '2018-03-01'
4976 and sc.call_type_id = 12694
4977 and sc.staff_id = 20706
4978 on duplicate key update
4979 assignments_count = assignments_count + 1
4980
4981 (146.3ms) /* AssignmentLimitsRolling */
4982 /* Unique Key: assignment_limit_id, staff_id, start_date */
4983 insert into buffer_staff_assignments_count_rolling_793447700 (
4984 assignment_limit_id, staff_id, start_date, end_date, assignments_count
4985 )
4986 select lac.assignment_limit_id, sc.staff_id, lac.start_date, lac.end_date,
4987 1 as assignments_count
4988 from buffer_schedules_793447700 as sc
4989 inner join buffer_assignment_limit_call_types_793447700 as alct
4990 on sc.call_type_id = alct.call_type_id
4991 inner join assignment_limits as al
4992 on alct.assignment_limit_id = al.id
4993 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4994 and al.max_rolling_period is not null
4995 and al.max_rolling_limit is not null
4996 inner join assign_staff_types as ast
4997 on sc.staff_id = ast.staff_id
4998 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
4999 inner join buffer_assignment_limit_staff_types_793447700 as alst
5000 on alct.assignment_limit_id = alst.assignment_limit_id
5001 and ast.staff_type_id = alst.staff_type_id
5002 inner join (
5003 select assignment_limit_id, start_date, end_date
5004 from buffer_linkage_assignments_count_rolling_793447700
5005 group by assignment_limit_id, start_date, end_date
5006 order by null
5007 ) as lac
5008 on alct.assignment_limit_id = lac.assignment_limit_id
5009 and sc.schedule_on between lac.start_date and lac.end_date
5010 where sc.schedule_on = '2018-03-01'
5011 and sc.call_type_id = 12694
5012 and sc.staff_id = 20706
5013 on duplicate key update
5014 assignments_count = assignments_count + 1
5015
5016 (146.3ms) /* AssignmentLimitsConsecutive */
5017 /* Unique Key: assignment_limit_id, staff_id, schedule_on, start_date */
5018 insert ignore into buffer_staff_assignments_count_consecutive_793447700 (
5019 assignment_limit_id, staff_id, schedule_on, start_date, end_date
5020 )
5021 select lac.assignment_limit_id, sc.staff_id, sc.schedule_on, lac.start_date, lac.end_date
5022 from buffer_schedules_793447700 as sc
5023 inner join buffer_assignment_limit_call_types_793447700 as alct
5024 on sc.call_type_id = alct.call_type_id
5025 inner join assignment_limits as al
5026 on alct.assignment_limit_id = al.id
5027 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
5028 and al.max_consecutive_limit is not null
5029 inner join assign_staff_types as ast
5030 on sc.staff_id = ast.staff_id
5031 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
5032 inner join buffer_assignment_limit_staff_types_793447700 as alst
5033 on alct.assignment_limit_id = alst.assignment_limit_id
5034 and ast.staff_type_id = alst.staff_type_id
5035 inner join (
5036 select assignment_limit_id, start_date, end_date
5037 from buffer_linkage_assignments_count_consecutive_793447700
5038 group by assignment_limit_id, start_date, end_date
5039 order by null
5040 ) as lac
5041 on alct.assignment_limit_id = lac.assignment_limit_id
5042 and sc.schedule_on between lac.start_date and lac.end_date
5043 where sc.schedule_on = '2018-03-01'
5044 and sc.call_type_id = 12694
5045 and sc.staff_id = 20706
5046
5047INFO : (02-27 08:18:11) scheduled for: plan unit: [id:18201808] [2018-03-01 - BU], Illajah Stern. DEPTH(2)
5048**Airbrake: closed