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