· 8 years ago · Apr 24, 2018, 09:20 PM
1(135.3ms) 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 (150.4ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3 CalendarSchedule Load (137.8ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
4 CallType Load (604.9ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (12692, 12693, 12694)
5 CalendarSchedule Load (880.1ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
6 Block Load (153.5ms) 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 (157.8ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
8 PopulationTimeframe Load (136.0ms) 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 (137.3ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1502 LIMIT 1
10 Block Load (136.6ms) 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-17 11:37:13) phase: Scheduler::Base
12INFO : (04-17 11:37:13) from: 2018-05-01 to: 2018-05-31
13 Rule Load (139.2ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1502 ORDER BY applied desc, weight desc
14INFO : (04-17 11:37:14) rule configurations: 3. CUMULATIVE DAILY ASSIGNMENT TARGETS
15 - 'Cumulative Daily Assignment Targets' applied {"opt1"=>{"applied"=>"1"}, "opt2"=>{"applied"=>"1"}, "opt3"=>{"applied"=>"1"}, "opt4"=>{"applied"=>"1"}, "opt5"=>{"applied"=>"1"}, "opt6"=>{"applied"=>"1"}, "opt7"=>{"applied"=>"1"}, "round_targets"=>"false"} weight:5
16 - 'Cumulative Assignment Targets' not applied
17 - 'Cumulative Combined Assignment Targets' not applied
18 - 'Cumulative Day of Week Targets' not applied
19 - 'Cumulative Hour Targets' not applied
20 - 'Cumulative Point Targets' not applied
21 - 'Cumulative Weekend / Weekday Assignment Targets' not applied
22 - 'Cumulative Weekend / Weekday Combined Assignment Targets' not applied
23 - 'Cumulative Weekend / Weekday Hour Targets' not applied
24 - 'Cumulative Weekend / Weekday Point Targets' not applied
25 - 'Current Assignment Targets' not applied
26 - 'Current Combined Assignment Targets' not applied
27 - 'Current Daily Assignment Targets' not applied
28 - 'Current Day of Week Targets' not applied
29 - 'Current Hour Targets' not applied
30 - 'Current Point Targets' not applied
31 - 'Current Weekend / Weekday Assignment Targets' not applied
32 - 'Current Weekend / Weekday Combined Assignment Targets' not applied
33 - 'Current Weekend / Weekday Hour Targets' not applied
34 - 'Current Weekend / Weekday Point Targets' not applied
35 - 'Pending Call Requests' not applied
36 - 'Pending No-call Requests' not applied
37 - 'Assignments Per Day' not applied
38 - 'Days Between Assignments' not applied
39 - 'Days Between Same Assignment' not applied
40 - 'Unassigned Days' not applied
41 - 'Weeks Between Same Assignment On Same Day Of Week' not applied
42 - 'Weeks Between Same Day Of Week' not applied
43INFO : (04-17 11:37:14) started: Scheduler::Cache::Collector.collect_all
44INFO : (04-17 11:37:14) collect_staffs
45 Staff Load (136.8ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `assign_staff_types` ON `assign_staff_types`.`staff_id` = `staffs`.`id` INNER JOIN `staff_types` ON `staff_types`.`id` = `assign_staff_types`.`staff_type_id` WHERE `staffs`.`client_id` = 319 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
46 Client Load (139.8ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319
47 StaffSpecialization Load (140.3ms) 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 (135.6ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` IN (711, 712) ORDER BY `specializations`.`priority` ASC
49 StaffCallType Load (136.8ms) 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-17 11:37:17) initial eligibles: [20702,20705,20703,20704,20695,20701,20697,20699,20698,20706,20696]
51INFO : (04-17 11:37:17) collect_call_types
52 CallType Load (136.2ms) 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-17 11:37:17) initial call types: [12692,12693,12694,12706]
54INFO : (04-17 11:37:17) collect_chain_links
55 ChainLink Load (135.5ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 319
56INFO : (04-17 11:37:17) collect_linkages
57 CallType Load (136.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12693 LIMIT 1
58 CallType Load (140.7ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
59 CallType Load (135.9ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
60 CallType Load (138.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12693 LIMIT 1
61 CallType Load (135.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12693 LIMIT 1
62 CallType Load (139.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
63 CallType Load (135.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
64 CallType Load (145.3ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12693 LIMIT 1
65INFO : (04-17 11:37:18) collect_schedules
66 Schedule Load (157.4ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 319 AND (`schedules`.`schedule_on` BETWEEN '2018-05-01' AND '2018-05-31')
67INFO : (04-17 11:37:19) collect_slots
68INFO : (04-17 11:37:19) collect_approved_call_requests
69 Request Load (143.6ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 319 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-05-01' AND '2018-05-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
70INFO : (04-17 11:37:19) collect_approved_no_call_requests
71 Request Load (136.6ms) 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-17 11:37:19) finished: Scheduler::Cache::Collector.collect_all
73INFO : (04-17 11:37:19) begin to process requests
74 CallType Load (159.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 1381 AND `call_types`.`active` = 1
75 (489.5ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 1381 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
76 Request Load (135.9ms) 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 (153.4ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 319 AND 1=0
78INFO : (04-17 11:37:21) end of requests processing
79 Block Load (149.9ms) 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 (138.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
81 NegativeLink Load (136.9ms) SELECT `negative_links`.* FROM `negative_links` WHERE `negative_links`.`client_id` = 319
82INFO : (04-17 11:37:21) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
83INFO : (04-17 11:37:21) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
84INFO : (04-17 11:37:21) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
85INFO : (04-17 11:37:21) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
86INFO : (04-17 11:37:21) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
87INFO : (04-17 11:37:21) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
88INFO : (04-17 11:37:21) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
89INFO : (04-17 11:37:21) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
90INFO : (04-17 11:37:21) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
91INFO : (04-17 11:37:21) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query015
92INFO : (04-17 11:37:21) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
93INFO : (04-17 11:37:21) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
94INFO : (04-17 11:37:21) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
95INFO : (04-17 11:37:21) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
96INFO : (04-17 11:37:21) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
97INFO : (04-17 11:37:21) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
98INFO : (04-17 11:37:21) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
99INFO : (04-17 11:37:21) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
100INFO : (04-17 11:37:21) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
101INFO : (04-17 11:37:21) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
102INFO : (04-17 11:37:21) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0705
103INFO : (04-17 11:37:21) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0706
104INFO : (04-17 11:37:21) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0707
105INFO : (04-17 11:37:21) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0708
106INFO : (04-17 11:37:21) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0709
107INFO : (04-17 11:37:21) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query07_10_AL
108INFO : (04-17 11:37:21) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
109INFO : (04-17 11:37:21) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
110INFO : (04-17 11:37:21) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
111INFO : (04-17 11:37:21) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
112INFO : (04-17 11:37:21) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query08
113INFO : (04-17 11:37:21) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query085
114INFO : (04-17 11:37:21) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query09
115INFO : (04-17 11:37:21) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query092
116INFO : (04-17 11:37:21) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query093
117INFO : (04-17 11:37:21) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query095
118INFO : (04-17 11:37:21) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query097
119INFO : (04-17 11:37:21) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query098
120INFO : (04-17 11:37:21) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
121INFO : (04-17 11:37:21) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
122INFO : (04-17 11:37:21) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
123INFO : (04-17 11:37:21) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
124INFO : (04-17 11:37:21) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Points
125INFO : (04-17 11:37:21) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
126INFO : (04-17 11:37:21) <-- clear: 44 - Scheduler::AutoPopulate::Queries::Query10
127INFO : (04-17 11:37:21) <-- clear: 45 - Scheduler::AutoPopulate::Queries::QueryScore02
128INFO : (04-17 11:37:21) <-- clear: 46 - Scheduler::AutoPopulate::Queries::QueryScore03
129INFO : (04-17 11:37:21) <-- clear: 47 - Scheduler::AutoPopulate::Queries::QueryScore04
130INFO : (04-17 11:37:21) <-- clear: 48 - Scheduler::AutoPopulate::Queries::Query11
131INFO : (04-17 11:37:21) <-- clear: 49 - Scheduler::AutoPopulate::Queries::DropTables
132 (137.7ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_1744999692
133 (135.8ms) DROP TABLE IF EXISTS buffer_annual_point_targets_1744999692
134 (134.8ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_1744999692
135 (135.1ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1744999692
136 (138.5ms) DROP TABLE IF EXISTS buffer_call_days_targets_1744999692
137 (145.3ms) DROP TABLE IF EXISTS buffer_call_type_targets_1744999692
138 (135.2ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_1744999692
139 (134.8ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_1744999692
140 (139.7ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_1744999692
141 (134.9ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_1744999692
142 (168.0ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_1744999692
143 (134.8ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_1744999692
144 (149.6ms) DROP TABLE IF EXISTS buffer_current_cd_targets_1744999692
145 (135.1ms) DROP TABLE IF EXISTS buffer_current_ct_targets_1744999692
146 (169.3ms) DROP TABLE IF EXISTS buffer_current_dow_targets_1744999692
147 (135.8ms) DROP TABLE IF EXISTS buffer_current_hour_targets_1744999692
148 (146.3ms) DROP TABLE IF EXISTS buffer_current_oa_targets_1744999692
149 (137.1ms) DROP TABLE IF EXISTS buffer_current_point_targets_1744999692
150 (185.2ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_1744999692
151 (135.7ms) DROP TABLE IF EXISTS buffer_hour_targets_1744999692
152 (136.9ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_1744999692
153 (136.0ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_1744999692
154 (137.5ms) DROP TABLE IF EXISTS buffer_linkage_targets_1744999692
155 (135.2ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_1744999692
156 (134.9ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_1744999692
157 (134.8ms) DROP TABLE IF EXISTS buffer_location_privileges_1744999692
158 (135.5ms) DROP TABLE IF EXISTS buffer_periods_1744999692
159 (136.0ms) DROP TABLE IF EXISTS buffer_point_targets_1744999692
160 (270.5ms) DROP TABLE IF EXISTS buffer_schedules_1744999692
161 (138.2ms) DROP TABLE IF EXISTS buffer_target_call_types_1744999692
162 (136.1ms) DROP TABLE IF EXISTS buffer_target_daily_points_1744999692
163 (135.0ms) DROP TABLE IF EXISTS buffer_target_dates_list_1744999692
164 (137.6ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_1744999692
165 (134.7ms) DROP TABLE IF EXISTS buffer_targets_1744999692
166 (135.0ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_1744999692
167 (140.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_1744999692
168 (135.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_1744999692
169 (139.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_1744999692
170 (135.2ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_1744999692
171 (136.6ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_1744999692
172 (136.8ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_1744999692
173 (158.6ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_1744999692
174 (137.8ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1744999692
175 (150.6ms) DROP TABLE IF EXISTS buffer_call_type_assignments_1744999692
176 (134.5ms) DROP TABLE IF EXISTS buffer_combined_availabilities_1744999692
177 (176.5ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_1744999692
178 (154.8ms) DROP TABLE IF EXISTS buffer_counter_increments_1744999692
179 (182.2ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_1744999692
180 (135.0ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_consecutive_1744999692
181 (136.2ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_periods_1744999692
182 (136.4ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_rolling_1744999692
183 (147.8ms) DROP TABLE IF EXISTS buffer_linkage_assignments_count_weeks_1744999692
184 (135.0ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_1744999692
185 (169.6ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_1744999692
186 (137.1ms) DROP TABLE IF EXISTS buffer_linkage_patterns_1744999692
187 (163.0ms) DROP TABLE IF EXISTS buffer_linkages_1744999692
188 (134.6ms) DROP TABLE IF EXISTS buffer_location_privileges_1744999692
189 (301.1ms) DROP TABLE IF EXISTS buffer_min_distances_1744999692
190 (239.2ms) DROP TABLE IF EXISTS buffer_negative_relations_1744999692
191 (249.9ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_1744999692
192 (587.1ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_1744999692
193 (143.3ms) DROP TABLE IF EXISTS buffer_rule_scores_1744999692
194 (170.6ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_1744999692
195 (171.1ms) DROP TABLE IF EXISTS buffer_schedule_linkages_1744999692
196 (1300.3ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_1744999692
197 (137.5ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_1744999692
198 (161.9ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_1744999692
199 (136.1ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_periods_1744999692
200 (164.3ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_rolling_1744999692
201 (142.5ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_weeks_1744999692
202 (160.8ms) DROP TABLE IF EXISTS buffer_staff_assignments_count_consecutive_1744999692
203 (134.8ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_1744999692
204 (146.2ms) DROP TABLE IF EXISTS buffer_staff_specializations_1744999692
205 (135.3ms) DROP TABLE IF EXISTS buffer_static_availabilities_1744999692
206 (171.0ms) DROP TABLE IF EXISTS buffer_target_assignments_1744999692
207 (133.8ms) delete from buffer_target_dates
208 where session_id = 1744999692
209 and client_id = 319
210
211INFO : (04-17 11:37:34) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
212 (171.4ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_1744999692 LIKE buffer_assignment_limit_call_types
213 (137.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_1744999692 LIKE buffer_assignment_limit_periods
214 (176.5ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_1744999692 LIKE buffer_assignment_limit_staff_types
215 (138.0ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1744999692 LIKE buffer_auto_populate_slots
216 (142.1ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_1744999692 LIKE buffer_call_type_assignments
217 (139.4ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_1744999692 LIKE buffer_combined_availabilities
218 (140.6ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_1744999692 LIKE buffer_combined_availability_linkages
219 (140.5ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_1744999692 LIKE buffer_counter_increments
220 (153.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_1744999692 LIKE buffer_linkage_assigned_days
221 (138.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_consecutive_1744999692 LIKE buffer_linkage_assignments_count_consecutive
222 (138.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_periods_1744999692 LIKE buffer_linkage_assignments_count_periods
223 (138.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_rolling_1744999692 LIKE buffer_linkage_assignments_count_rolling
224 (140.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignments_count_weeks_1744999692 LIKE buffer_linkage_assignments_count_weeks
225 (138.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_1744999692 LIKE buffer_linkage_call_type_assignments
226 (139.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_1744999692 LIKE buffer_linkage_dow_targets
227 (141.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_1744999692 LIKE buffer_linkage_patterns
228 (139.1ms) CREATE TABLE IF NOT EXISTS buffer_linkages_1744999692 LIKE buffer_linkages
229 (138.2ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1744999692 LIKE buffer_location_privileges
230 (138.3ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_1744999692 LIKE buffer_min_distances
231 (142.0ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_1744999692 LIKE buffer_negative_relations
232 (138.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_1744999692 LIKE buffer_rule_calc_filters
233 (158.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_1744999692 LIKE buffer_rule_calc_schedules
234 (139.5ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_1744999692 LIKE buffer_rule_scores
235 (167.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_1744999692 LIKE buffer_rule_scores_with_corrections
236 (150.8ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_1744999692 LIKE buffer_schedule_linkages
237 (140.1ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_1744999692 LIKE buffer_schedules_linkage_patterns
238 (137.7ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_1744999692 LIKE buffer_specialization_daily_counts
239 (152.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_1744999692 LIKE buffer_staff_assigned_days
240 (138.7ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_periods_1744999692 LIKE buffer_staff_assignments_count_periods
241 (164.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_rolling_1744999692 LIKE buffer_staff_assignments_count_rolling
242 (138.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_weeks_1744999692 LIKE buffer_staff_assignments_count_weeks
243 (170.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignments_count_consecutive_1744999692 LIKE buffer_staff_assignments_count_consecutive
244 (137.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_1744999692 LIKE buffer_staff_daily_counts
245 (148.4ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_1744999692 LIKE buffer_staff_specializations
246 (140.2ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_1744999692 LIKE buffer_static_availabilities
247 (151.6ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_1744999692 LIKE buffer_target_assignments
248 (138.3ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_1744999692 LIKE buffer_annual_hour_targets
249 (142.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_1744999692 LIKE buffer_annual_point_targets
250 (139.5ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_1744999692 LIKE buffer_annual_targets_staff_totals
251 (134.9ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1744999692 LIKE buffer_auto_populate_slots
252 (427.6ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_1744999692 LIKE buffer_call_days_targets
253 (138.8ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_1744999692 LIKE buffer_call_type_targets
254 (141.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_1744999692 LIKE buffer_cumulative_cd_targets
255 (139.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_1744999692 LIKE buffer_cumulative_ct_targets
256 (147.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_1744999692 LIKE buffer_cumulative_dow_targets
257 (140.5ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_1744999692 LIKE buffer_cumulative_hour_targets
258 (922.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_1744999692 LIKE buffer_cumulative_oa_targets
259 (138.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_1744999692 LIKE buffer_cumulative_point_targets
260 (139.6ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_1744999692 LIKE buffer_current_cd_targets
261 (138.9ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_1744999692 LIKE buffer_current_ct_targets
262 (141.4ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_1744999692 LIKE buffer_current_dow_targets
263 (141.4ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_1744999692 LIKE buffer_current_hour_targets
264 (140.5ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_1744999692 LIKE buffer_current_oa_targets
265 (158.8ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_1744999692 LIKE buffer_current_point_targets
266 (140.0ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_1744999692 LIKE buffer_day_of_week_targets
267 (158.5ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_1744999692 LIKE buffer_hour_targets
268 (139.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_1744999692 LIKE buffer_linkage_call_days_targets
269 (167.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_1744999692 LIKE buffer_linkage_call_type_targets
270 (139.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_1744999692 LIKE buffer_linkage_targets
271 (147.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_1744999692 LIKE buffer_linkage_hour_targets
272 (139.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_1744999692 LIKE buffer_linkage_point_targets
273 (171.8ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1744999692 LIKE buffer_location_privileges
274 (142.7ms) CREATE TABLE IF NOT EXISTS buffer_periods_1744999692 LIKE buffer_periods
275 (152.6ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_1744999692 LIKE buffer_point_targets
276 (138.9ms) CREATE TABLE IF NOT EXISTS buffer_schedules_1744999692 LIKE buffer_schedules
277 (140.4ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_1744999692 LIKE buffer_target_call_types
278 (140.1ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_1744999692 LIKE buffer_target_daily_points
279 (174.3ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_1744999692 LIKE buffer_target_dates_list
280 (140.3ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_1744999692 LIKE buffer_target_primary_availabilities
281 (152.9ms) CREATE TABLE IF NOT EXISTS buffer_targets_1744999692 LIKE buffer_targets
282 (138.0ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_1744999692 LIKE buffer_tmp_targets_availabilities
283 (165.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_1744999692 LIKE buffer_tmp_targets_availabilities_exclusions
284 (138.2ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_1744999692 LIKE buffer_tmp_targets_client_totals
285 (169.0ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_1744999692 LIKE buffer_tmp_targets_scheduled
286 (139.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_1744999692 LIKE buffer_tmp_targets_staff_totals
287INFO : (04-17 11:37:46) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
288 (135.6ms) 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 (170.6ms) 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 (150.8ms) INSERT INTO buffer_auto_populate_slots_1744999692
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-17 11:37:47) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
310 (144.4ms) insert into buffer_auto_populate_slots_1744999692/* 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_1744999692/* 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_1744999692/* 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-17 11:37:47) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
351 (142.9ms) insert into buffer_auto_populate_slots_1744999692 (
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_1744999692 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_1744999692 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-17 11:37:47) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
392 (158.8ms) 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_1744999692/* 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 (149.6ms) insert into buffer_schedules_1744999692 (
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_1744999692 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_1744999692 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-17 11:37:48) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
522 (149.5ms) update
523 buffer_schedules_1744999692 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 (134.3ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
536 (169.9ms) insert into buffer_periods_1744999692 (
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_1744999692 as sc
577 inner join buffer_auto_populate_slots_1744999692 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 (138.7ms) update
626 buffer_schedules_1744999692 as sc
627 inner join buffer_periods_1744999692 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-17 11:37:48) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
633 (148.0ms) delete from buffer_target_dates
634 where session_id = 1744999692
635
636 (135.4ms) select distinct calendar_schedule_id
637 from buffer_periods_1744999692
638
639 (172.2ms) 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 1744999692 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 t1.within_cumulative_period = 1 /* CumulativeCallDaysTargetsRule */
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 FALSE
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_1744999692
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(1 /* current targets period */, 2 /* cumulative targets period */)
859 and t1.end_block_display_order >= ns.item_number
860 where
861 (t1.end_block_display_order - ns.item_number) /* block display order */
862 between t1.start_block_display_order and t1.end_block_display_order
863 or (t1.end_block_display_order - ns.item_number)
864 between (t1.timeframe_ending - (2 /* cumulative targets period */ - 1)) and t1.timeframe_ending
865 order by null
866 ) as t1
867 inner join blocks as b
868 on t1.block_schedule_id = b.block_schedule_id
869 and t1.block_display_order = b.display_order
870 left join buffer_periods_1744999692 as p
871 on t1.calendar_schedule_id = p.calendar_schedule_id
872 and t1.block_display_order between p.start_block_display_order and p.end_block_display_order
873 left join published_blocks as pb
874 on b.id = pb.block_id
875 and t1.calendar_schedule_id = pb.calendar_schedule_id
876 and pb.assignment_ids is null
877 and pb.days_of_week_mask = 127
878 order by null
879 ) as t1
880 left join (
881 select
882 calendar_schedule_id,
883 block_id
884 from
885 unpublished_call_days_targets
886 where
887 calendar_schedule_id in (1381) /* list of calendar schedules */
888 group by
889 calendar_schedule_id,
890 block_id
891 order by null
892 ) as cd_u
893 on t1.calendar_schedule_id = cd_u.calendar_schedule_id
894 and t1.block_id = cd_u.block_id
895 left join (
896 select
897 calendar_schedule_id,
898 block_id
899 from
900 unpublished_call_type_targets
901 where
902 calendar_schedule_id in (1381) /* list of calendar schedules */
903 group by
904 calendar_schedule_id,
905 block_id
906 order by null
907 ) as ct_u
908 on t1.calendar_schedule_id = ct_u.calendar_schedule_id
909 and t1.block_id = ct_u.block_id
910 left join (
911 select
912 calendar_schedule_id,
913 block_id
914 from
915 unpublished_targets
916 where
917 calendar_schedule_id in (1381) /* list of calendar schedules */
918 group by
919 calendar_schedule_id,
920 block_id
921 order by null
922 ) as oa_u
923 on t1.calendar_schedule_id = oa_u.calendar_schedule_id
924 and t1.block_id = oa_u.block_id
925 left join (
926 select
927 calendar_schedule_id,
928 block_id
929 from
930 unpublished_day_of_week_targets
931 where
932 calendar_schedule_id in (1381) /* list of calendar schedules */
933 group by
934 calendar_schedule_id,
935 block_id
936 order by null
937 ) as dow_u
938 on t1.calendar_schedule_id = dow_u.calendar_schedule_id
939 and t1.block_id = dow_u.block_id
940 left join (
941 select
942 calendar_schedule_id,
943 block_id
944 from
945 unpublished_point_targets
946 where
947 calendar_schedule_id in (1381) /* list of calendar schedules */
948 group by
949 calendar_schedule_id,
950 block_id
951 order by null
952 ) as p_u
953 on t1.calendar_schedule_id = p_u.calendar_schedule_id
954 and t1.block_id = p_u.block_id
955 left join (
956 select
957 calendar_schedule_id,
958 block_id
959 from
960 unpublished_hour_targets
961 where
962 calendar_schedule_id in (1381) /* list of calendar schedules */
963 group by
964 calendar_schedule_id,
965 block_id
966 order by null
967 ) as h_u
968 on t1.calendar_schedule_id = h_u.calendar_schedule_id
969 and t1.block_id = h_u.block_id
970 left join (
971 select
972 pb.calendar_schedule_id,
973 pb.block_id,
974 max(
975 case
976 when (coalesce(pbct.days_of_week_mask, 0) > 0) then 1
977 else 0
978 end
979 ) as has_published,
980 max(
981 case
982 when (coalesce(pbct.days_of_week_mask, 0) < 127) then 1
983 else 0
984 end
985 ) as has_unpublished
986 from (
987 select
988 case
989 when (tg.id is null) then ct.id
990 else tg.id
991 end as assignment_id,
992 case
993 when (tg.id is null) then 'CallType'
994 else 'TargetGroup'
995 end as assignment_type,
996 ct.calendar_schedule_id
997 from
998 call_types as ct
999 left join target_group_calls as tgc
1000 on ct.id = tgc.call_type_id
1001 left join target_groups as tg
1002 on tgc.target_group_id = tg.id
1003 and tg.active = 1
1004 where
1005 ct.calendar_schedule_id in (1381) /* list of calendar schedules */
1006 and ct.active = 1
1007 and ct.included_in_targets > 0
1008 and ct.days_of_week_mask > 0
1009 group by
1010 assignment_id,
1011 assignment_type,
1012 ct.calendar_schedule_id
1013 order by null
1014 ) as ct
1015 inner join published_blocks as pb
1016 on ct.calendar_schedule_id = pb.calendar_schedule_id
1017 and (
1018 pb.assignment_ids is not null
1019 or pb.days_of_week_mask < 127
1020 )
1021 left join published_block_call_types as pbct
1022 on pb.id = pbct.published_block_id
1023 and ct.assignment_id = pbct.assignment_id
1024 and ct.assignment_type = pbct.assignment_type
1025 group by
1026 pb.calendar_schedule_id,
1027 pb.block_id
1028 order by null
1029 ) as pb
1030 on t1.calendar_schedule_id = pb.calendar_schedule_id
1031 and t1.block_id = pb.block_id
1032 order by null
1033
1034Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0101 - 457.09ms
1035 (135.2ms) select
1036 max(build_cd_targets) as build_cd_targets,
1037 max(build_ct_targets) as build_ct_targets,
1038 max(build_oa_targets) as build_oa_targets,
1039 max(build_dow_targets) as build_dow_targets,
1040 max(build_point_targets) as build_point_targets,
1041 max(build_hour_targets) as build_hour_targets
1042 from buffer_target_dates
1043 where session_id = 1744999692
1044 order by null
1045
1046Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets02 - 135.67499999999998ms
1047 (963.2ms) /* CumulativeCallDaysTargetsRule */
1048 insert into buffer_cumulative_cd_targets_1744999692 (
1049 staff_id,
1050 assignment_id,
1051 assignment_type,
1052 day_of_week_mask,
1053 is_weekend,
1054 calendar_schedule_id,
1055 raw_target,
1056 adjusted_target,
1057 scheduled,
1058 available_days
1059 )
1060 select
1061 t.staff_id,
1062 t.assignment_id,
1063 t.assignment_type,
1064 t.day_of_week_mask,
1065 t.is_weekend,
1066 t.calendar_schedule_id,
1067 sum(t.raw_target) as raw_target,
1068 sum(t.adjusted_target) as adjusted_target,
1069 sum(t.scheduled) as scheduled,
1070 sum(t.available_days) as available_days
1071 from (
1072 select
1073 t.calendar_schedule_id,
1074 t.block_id,
1075 t.staff_id,
1076 t.assignment_id,
1077 t.assignment_type,
1078 t.day_of_week_mask,
1079 t.is_weekend,
1080 t.raw_target,
1081 t.target as adjusted_target,
1082 t.scheduled,
1083 t.available_days
1084 from
1085 buffer_target_dates as td
1086 inner join buffer_call_days_targets_1744999692 as t
1087 on td.calendar_schedule_id = t.calendar_schedule_id
1088 and td.block_id = t.block_id
1089 where
1090 td.session_id = 1744999692
1091 and td.build_cd_targets = 1
1092 and td.within_cumulative_period = 1
1093 union
1094 select
1095 cdt.calendar_schedule_id,
1096 cdt.block_id,
1097 cdt.staff_id,
1098 cdt.assignment_id,
1099 cdt.assignment_type,
1100 cdt.day_of_week_mask,
1101 cdt.is_weekend,
1102 cdt.raw_target,
1103 cdt.target,
1104 cdt.scheduled,
1105 cdt.available_days
1106 from
1107 buffer_target_dates as td
1108 inner join call_days_targets as cdt
1109 on td.calendar_schedule_id = cdt.calendar_schedule_id
1110 and td.block_id = cdt.block_id
1111 where
1112 td.session_id = 1744999692
1113 and td.build_cd_targets = 0
1114 and td.cd_targets_source in ('published', 'both')
1115 and td.within_cumulative_period = 1
1116 union
1117 select
1118 ucdt.calendar_schedule_id,
1119 ucdt.block_id,
1120 ucdt.staff_id,
1121 ucdt.assignment_id,
1122 ucdt.assignment_type,
1123 ucdt.day_of_week_mask,
1124 ucdt.is_weekend,
1125 ucdt.raw_target,
1126 ucdt.target,
1127 ucdt.scheduled,
1128 ucdt.available_days
1129 from
1130 buffer_target_dates as td
1131 inner join unpublished_call_days_targets as ucdt
1132 on td.calendar_schedule_id = ucdt.calendar_schedule_id
1133 and td.block_id = ucdt.block_id
1134 where
1135 td.session_id = 1744999692
1136 and td.build_cd_targets = 0
1137 and td.cd_targets_source in ('unpublished', 'both')
1138 and td.within_cumulative_period = 1
1139 union all
1140 select
1141 tad.calendar_schedule_id,
1142 tad.block_id,
1143 tad.staff_id,
1144 tad.assignment_id,
1145 tad.assignment_type,
1146 tad.day_of_week_mask,
1147 tad.is_weekend,
1148 0 as raw_target,
1149 0 as target,
1150 tad.adjustment_value as scheduled,
1151 0 as available_days
1152 from
1153 buffer_target_dates as td
1154 inner join target_adjustment_details as tad
1155 on td.calendar_schedule_id = tad.calendar_schedule_id
1156 and td.block_id = tad.block_id
1157 and tad.staff_id > 0
1158 where
1159 td.session_id = 1744999692
1160 and td.within_cumulative_period = 1
1161 order by null
1162 ) as t
1163 group by
1164 t.staff_id,
1165 t.assignment_id,
1166 t.assignment_type,
1167 t.day_of_week_mask,
1168 t.is_weekend,
1169 t.calendar_schedule_id
1170 order by null
1171
1172Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0702 - 964.3399999999999ms
1173Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0703 - 0.058ms
1174 (142.9ms) /* CurrentCallDaysTargetsRule, CumulativeCallDaysTargetsRule */
1175 insert into buffer_call_days_targets_1744999692 (
1176 calendar_schedule_id,
1177 block_id,
1178 staff_id,
1179 assignment_id,
1180 assignment_type,
1181 day_of_week_mask,
1182 is_weekend,
1183 raw_target,
1184 target,
1185 initial_target,
1186 scheduled,
1187 available_days
1188 )
1189 select
1190 cdt.calendar_schedule_id,
1191 cdt.block_id,
1192 cdt.staff_id,
1193 cdt.assignment_id,
1194 cdt.assignment_type,
1195 cdt.day_of_week_mask,
1196 cdt.is_weekend,
1197 cdt.raw_target,
1198 cdt.target,
1199 cdt.initial_target,
1200 cdt.scheduled,
1201 cdt.available_days
1202 from buffer_target_dates as td
1203 inner join call_days_targets as cdt
1204 on td.calendar_schedule_id = cdt.calendar_schedule_id
1205 and td.block_id = cdt.block_id
1206 where td.session_id = 1744999692
1207 and td.build_cd_targets = 0
1208 and td.build_ct_targets = 0
1209 and td.build_oa_targets = 0
1210 and td.build_dow_targets = 0
1211 and td.cd_targets_source in ('published', 'both')
1212 and td.affected_by_populate = 1
1213 union
1214 select
1215 ucdt.calendar_schedule_id,
1216 ucdt.block_id,
1217 ucdt.staff_id,
1218 ucdt.assignment_id,
1219 ucdt.assignment_type,
1220 ucdt.day_of_week_mask,
1221 ucdt.is_weekend,
1222 ucdt.raw_target,
1223 ucdt.target,
1224 ucdt.initial_target,
1225 ucdt.scheduled,
1226 ucdt.available_days
1227 from buffer_target_dates as td
1228 inner join unpublished_call_days_targets as ucdt
1229 on td.calendar_schedule_id = ucdt.calendar_schedule_id
1230 and td.block_id = ucdt.block_id
1231 where td.session_id = 1744999692
1232 and td.build_cd_targets = 0
1233 and td.build_ct_targets = 0
1234 and td.build_oa_targets = 0
1235 and td.build_dow_targets = 0
1236 and td.cd_targets_source in ('unpublished', 'both')
1237 and td.affected_by_populate = 1
1238 order by null
1239
1240 (134.8ms) /* CurrentCallDaysTargetsRule, CumulativeCallDaysTargetsRule */
1241 update buffer_call_days_targets_1744999692 as cdt
1242 inner join (
1243 select
1244 cdt.block_id,
1245 cdt.calendar_schedule_id,
1246 cdt.staff_id,
1247 cdt.assignment_id,
1248 cdt.assignment_type,
1249 cdt.day_of_week_mask,
1250 sum(tad.adjustment_value) as adjustment_value
1251 from buffer_call_days_targets_1744999692 as cdt
1252 inner join target_adjustment_details as tad
1253 on cdt.block_id = tad.block_id
1254 and cdt.calendar_schedule_id = tad.calendar_schedule_id
1255 and cdt.staff_id = tad.staff_id
1256 and cdt.assignment_id = tad.assignment_id
1257 and cdt.assignment_type = tad.assignment_type
1258 and cdt.day_of_week_mask = tad.day_of_week_mask
1259 group by
1260 cdt.block_id,
1261 cdt.calendar_schedule_id,
1262 cdt.staff_id,
1263 cdt.assignment_id,
1264 cdt.assignment_type,
1265 cdt.day_of_week_mask
1266 order by null
1267 ) as cdta
1268 on cdt.block_id = cdta.block_id
1269 and cdt.calendar_schedule_id = cdta.calendar_schedule_id
1270 and cdt.staff_id = cdta.staff_id
1271 and cdt.assignment_id = cdta.assignment_id
1272 and cdt.assignment_type = cdta.assignment_type
1273 and cdt.day_of_week_mask = cdta.day_of_week_mask
1274 set cdt.scheduled = cdt.scheduled + cdta.adjustment_value
1275
1276Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets07Reports - 278.68399999999997ms
1277INFO : (04-17 11:37:50) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
1278 (313.2ms) insert into buffer_counter_increments_1744999692 (
1279 schedule_id,
1280 calls,
1281 points,
1282 hours,
1283 affects_cumulative_targets
1284 )
1285 select
1286 sc.id as schedule_id,
1287 case
1288 when (ct.included_in_targets > 0) then 1
1289 else 0
1290 end as calls,
1291 coalesce(
1292 case
1293 when (hl.observed_date is null or dp.point is not null) then dp.point
1294 else dp_2.point
1295 end,
1296 0.0
1297 ) as points,
1298 ct.hours,
1299 case
1300 when (b.display_order between (t1.timeframe_ending - (2 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
1301 else 0
1302 end as affects_cumulative_targets
1303 from
1304 buffer_schedules_1744999692 as sc
1305 inner join (
1306 select
1307 id,
1308 included_in_targets,
1309 coalesce(
1310 timestampdiff(
1311 minute,
1312 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
1313 concat(
1314 '1900-01-0',
1315 case
1316 when ((
1317 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
1318 )
1319 and (
1320 timediff(
1321 maketime(end_time_hour, end_time_minute, 0),
1322 maketime(start_time_hour, start_time_minute, 0)
1323 ) <= 0
1324 )
1325 ) then '2'
1326 else '1'
1327 end,
1328 ' ',
1329 maketime(end_time_hour, end_time_minute, 0)
1330 )
1331 ) / 60,
1332 0.0
1333 ) as hours
1334 from call_types
1335 where
1336 client_id = 319
1337 and active = 1
1338 order by null
1339 ) as ct
1340 on sc.call_type_id = ct.id
1341 inner join blocks as b
1342 on sc.block_id = b.id
1343 inner join (
1344 select
1345 calendar_schedule_id,
1346 max(
1347 case
1348 when (should_be_populated = 1) then end_block_display_order
1349 else null
1350 end
1351 ) as timeframe_ending
1352 from buffer_periods_1744999692
1353 group by calendar_schedule_id
1354 order by null
1355 ) as t1
1356 on sc.calendar_schedule_id = t1.calendar_schedule_id
1357 left join (
1358 select
1359 hd.observed_date,
1360 case
1361 when (hd.observed_date > hd.extended_date) then hd.extended_date
1362 else hd.observed_date
1363 end as date_from,
1364 case
1365 when (hd.observed_date < hd.extended_date) then hd.extended_date
1366 else hd.observed_date
1367 end as date_to
1368 from
1369 holidays as h
1370 inner join holiday_dates as hd
1371 on h.id = hd.holiday_id
1372 inner join (
1373 select
1374 year(min(schedule_on)) as year_from,
1375 year(max(schedule_on)) as year_to
1376 from buffer_schedules_1744999692
1377 where should_be_evaluated = 1
1378 ) as y
1379 on hd.year between y.year_from and y.year_to
1380 where
1381 h.client_id = 319
1382 and (
1383 h.observed = 1
1384 or h.extended = 1
1385 )
1386 order by null
1387 ) as hl
1388 on sc.schedule_on between hl.date_from and hl.date_to
1389 left join assignment_annotations as aa
1390 on ct.id = aa.assignmentable_id
1391 and aa.assignmentable_type = 'CallType'
1392 and aa.annotation_id = 0 /* annotation = "None" */
1393 and aa.applicable = 1
1394 left join assignment_annotation_daily_points as dp
1395 on aa.id = dp.assignment_annotation_id
1396 and case
1397 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
1398 when (sc.schedule_on = hl.observed_date) then 7
1399 else 8
1400 end = dp.day
1401 left join assignment_annotation_daily_points as dp_2
1402 on aa.id = dp_2.assignment_annotation_id
1403 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
1404 where sc.should_be_evaluated = 1
1405 order by null
1406
1407INFO : (04-17 11:37:50) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
1408 (138.9ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
1409 insert into buffer_linkage_patterns_1744999692 (
1410 period_offset,
1411 calendar_schedule_id,
1412 block_id,
1413 block_start_date,
1414 block_start_dow,
1415 block_length,
1416 linkage_pattern_id,
1417 pattern_start_dow,
1418 days_before_start,
1419 pattern_length,
1420 start_offset,
1421 iterations_count
1422 )
1423 select
1424 b1.period_offset,
1425 b1.calendar_schedule_id,
1426 b1.block_id,
1427 b2.start_date as block_start_date,
1428 dayofweek(b2.start_date) as block_start_dow,
1429 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
1430 lp.id as linkage_pattern_id,
1431 (lp.start_on + 1) as pattern_start_dow,
1432 lp.days_before_start,
1433 (lp.length * 7) as pattern_length,
1434 null as start_offset,
1435 null as iterations_count
1436 from (
1437 select
1438 calendar_schedule_id,
1439 block_id,
1440 period_offset
1441 from buffer_schedules_1744999692
1442 group by
1443 calendar_schedule_id,
1444 block_id,
1445 period_offset
1446 order by null
1447 ) as b1
1448 inner join blocks as b2
1449 on b1.block_id = b2.id
1450 inner join linkage_patterns as lp
1451 on lp.client_id = 319
1452 and b1.calendar_schedule_id = lp.calendar_schedule_id
1453 order by null
1454
1455 (137.5ms) -- Calculate the basic offset ("+" or "-") that will be applied to a linkage pattern if a block and a pattern are starting on different day of week
1456 update buffer_linkage_patterns_1744999692
1457 set start_offset = if(
1458 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
1459 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
1460 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
1461 )
1462
1463 (142.7ms) -- 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)
1464 -- a pattern should be repeated couple of times (in order to cover the entire block)
1465 update buffer_linkage_patterns_1744999692
1466 set iterations_count =
1467 ceiling(
1468 (
1469 block_length +
1470 case
1471 when (start_offset < 0) then abs(start_offset)
1472 else 0
1473 end
1474 ) * 1.0 / pattern_length
1475 )
1476
1477 (136.6ms) -- Generate an assignments sequence for every linkage pattern
1478 insert into buffer_schedules_linkage_patterns_1744999692 (
1479 call_type_id,
1480 staff_id,
1481 schedule_on,
1482 period_offset,
1483 day_of_week_mask,
1484 display_order,
1485 week_number,
1486 should_be_evaluated,
1487 should_be_populated,
1488 linkage_instance_id,
1489 block_id,
1490 calendar_schedule_id
1491 )
1492 select
1493 lps.assignment_id as call_type_id,
1494 null as staff_id,
1495 case
1496 when (lp.start_offset < 0)
1497 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
1498 else
1499 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
1500 end as schedule_on,
1501 lp.period_offset,
1502 null as day_of_week_mask,
1503 null as display_order,
1504 null as week_number,
1505 0 as should_be_evaluated,
1506 0 as should_be_populated,
1507 lp.linkage_pattern_id as linkage_instance_id,
1508 lp.block_id,
1509 lp.calendar_schedule_id
1510 from
1511 numeric_sequence as ns
1512 inner join buffer_linkage_patterns_1744999692 as lp
1513 on ns.item_number <= (lp.iterations_count - 1)
1514 inner join linkage_pattern_slots as lps
1515 on lp.linkage_pattern_id = lps.linkage_pattern_id
1516 and lps.assignment_type = 'CallType'
1517 where
1518 case
1519 when (lp.start_offset < 0)
1520 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
1521 else
1522 (ns.item_number * lp.pattern_length + lps.offset)
1523 end between 0 and (lp.block_length - 1)
1524 order by null
1525
1526 (141.3ms) -- Fill the columns that we left blank in the previous "INSERT"
1527 update
1528 buffer_schedules_linkage_patterns_1744999692 as slp
1529 inner join clients as cl
1530 on cl.id = 319
1531 inner join call_types as ct
1532 on slp.call_type_id = ct.id
1533 left join buffer_schedules_1744999692 as bs
1534 on slp.schedule_on = bs.schedule_on
1535 and slp.call_type_id = bs.call_type_id
1536 set
1537 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
1538 slp.display_order = ct.display_order,
1539 slp.week_number =
1540 yearweek(
1541 slp.schedule_on,
1542 case
1543 when (cl.start_of_week = 0) then 2
1544 else 7
1545 end
1546 ),
1547 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
1548 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
1549
1550 (1433.5ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
1551 delete slp_1
1552 from
1553 buffer_schedules_linkage_patterns_1744999692 as slp_1
1554 inner join (
1555 select linkage_instance_id
1556 from buffer_schedules_linkage_patterns_1744999692
1557 group by linkage_instance_id
1558 having
1559 max(should_be_evaluated) = 0
1560 and max(should_be_populated) = 0
1561 order by null
1562 ) as slp_2
1563 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
1564
1565 (137.1ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
1566 update
1567 buffer_schedules_linkage_patterns_1744999692 as slp
1568 inner join buffer_schedules_1744999692 as bs_1
1569 on slp.schedule_on = bs_1.schedule_on
1570 and slp.call_type_id = bs_1.call_type_id
1571 inner join buffer_schedules_1744999692 as bs_2
1572 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
1573 set bs_2.linkage_instance_id = slp.linkage_instance_id
1574
1575 (135.5ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
1576 -- should be copied over to the buffer schedules table (used during the auto-populate process)
1577 insert into buffer_schedules_1744999692 (
1578 call_type_id,
1579 staff_id,
1580 schedule_on,
1581 period_offset,
1582 day_of_week_mask,
1583 is_weekend,
1584 display_order,
1585 week_number,
1586 should_be_evaluated,
1587 should_be_populated,
1588 linkage_instance_id,
1589 block_id,
1590 calendar_schedule_id
1591 )
1592 select
1593 call_type_id,
1594 staff_id,
1595 schedule_on,
1596 period_offset,
1597 day_of_week_mask,
1598 case
1599 when (day_of_week_mask & 62 > 0) then 0
1600 else 1
1601 end as is_weekend,
1602 display_order,
1603 week_number,
1604 should_be_evaluated,
1605 should_be_populated,
1606 linkage_instance_id,
1607 block_id,
1608 calendar_schedule_id
1609 from buffer_schedules_linkage_patterns_1744999692
1610 where
1611 should_be_evaluated = 0
1612 and should_be_populated = 0
1613
1614INFO : (04-17 11:37:53) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query015
1615 (135.6ms) insert into buffer_schedules_1744999692 (
1616 call_type_id,
1617 staff_id,
1618 schedule_on,
1619 day_of_week_mask,
1620 is_weekend,
1621 display_order,
1622 should_be_evaluated,
1623 should_be_populated,
1624 linkage_instance_id
1625 )
1626 select distinct
1627 schedules_call_overlap.call_type_id,
1628 schedules_call_overlap.staff_id,
1629 schedules_call_overlap.schedule_on,
1630 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
1631 case
1632 when (dayofweek(schedules_call_overlap.schedule_on) between 2 and 6) then 0
1633 else 1
1634 end as is_weekend,
1635 call_types.display_order,
1636 0 as should_be_evaluated,
1637 0 as should_be_populated,
1638 -1 as linkage_instance_id
1639 from (
1640 select schedule_on
1641 from buffer_schedules_1744999692
1642 where should_be_evaluated = 1
1643 group by schedule_on
1644 order by null
1645 ) as buffer_schedules_1
1646 inner join schedules as schedules_call_overlap
1647 on schedules_call_overlap.client_id = 319
1648 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1649 left join buffer_schedules_1744999692 as buffer_schedules_2
1650 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1651 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1652 inner join call_types
1653 on schedules_call_overlap.call_type_id = call_types.id
1654 where buffer_schedules_2.id is null
1655 order by null
1656
1657INFO : (04-17 11:37:53) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1658INFO : (04-17 11:37:53) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1659INFO : (04-17 11:37:53) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1660INFO : (04-17 11:37:53) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1661INFO : (04-17 11:37:53) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1662 (138.6ms) insert into buffer_schedules_1744999692 (
1663 call_type_id,
1664 staff_id,
1665 schedule_on,
1666 day_of_week_mask,
1667 is_weekend,
1668 display_order,
1669 should_be_evaluated,
1670 should_be_populated,
1671 linkage_instance_id
1672 )
1673 select distinct straight_join
1674 schedules.call_type_id,
1675 schedules.staff_id,
1676 schedules.schedule_on,
1677 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1678 case
1679 when (dayofweek(schedules.schedule_on) between 2 and 6) then 0
1680 else 1
1681 end as is_weekend,
1682 call_types.display_order,
1683 0 as should_be_evaluated,
1684 0 as should_be_populated,
1685 -1 as linkage_instance_id
1686 from
1687 buffer_schedules_1744999692 as buffer_schedules_1
1688 inner join negative_links_influences use index (ix_unique_links)
1689 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1690 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1691 and negative_links_influences.source_type = 'CallType'
1692 and negative_links_influences.target_type = 'CallType'
1693 inner join schedules use index (ix_schedules_call_date)
1694 on date_add(
1695 buffer_schedules_1.schedule_on,
1696 interval negative_links_influences.offset day
1697 ) = schedules.schedule_on
1698 and negative_links_influences.target_id = schedules.call_type_id
1699 left join buffer_schedules_1744999692 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1700 on schedules.schedule_on = buffer_schedules_2.schedule_on
1701 and schedules.call_type_id = buffer_schedules_2.call_type_id
1702 inner join call_types
1703 on schedules.call_type_id = call_types.id
1704 where
1705 buffer_schedules_1.linkage_instance_id >= 0
1706 and buffer_schedules_1.should_be_evaluated = 1
1707 and buffer_schedules_2.id is null
1708 order by null
1709
1710INFO : (04-17 11:37:53) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1711 (137.5ms) insert into buffer_schedules_1744999692 (
1712 call_type_id,
1713 staff_id,
1714 schedule_on,
1715 day_of_week_mask,
1716 is_weekend,
1717 display_order,
1718 should_be_evaluated,
1719 should_be_populated,
1720 linkage_instance_id
1721 )
1722 select distinct straight_join
1723 (-1 * negative_links_influences.target_id) as call_type_id,
1724 vacations.staff_id,
1725 vacations.schedule_on,
1726 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1727 vacations.weekend_on as is_weekend,
1728 -1 as display_order,
1729 0 as should_be_evaluated,
1730 0 as should_be_populated,
1731 -1 as linkage_instance_id
1732 from
1733 buffer_schedules_1744999692 as buffer_schedules_1
1734 inner join negative_links_influences use index (ix_unique_links)
1735 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1736 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1737 and negative_links_influences.source_type = 'CallType'
1738 and negative_links_influences.target_id > 0
1739 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1740 inner join vacations use index (ix_vacations_on_schedule_and_client)
1741 on vacations.client_id = 319 /* client_id */
1742 and date_add(
1743 buffer_schedules_1.schedule_on,
1744 interval negative_links_influences.offset day
1745 ) = vacations.schedule_on
1746 and negative_links_influences.target_id = vacations.non_clinical_type_id
1747 and vacations.status in (3) /* based on the "non-working requests" settings */
1748 left join buffer_schedules_1744999692 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1749 on vacations.staff_id = buffer_schedules_2.staff_id
1750 and vacations.schedule_on = buffer_schedules_2.schedule_on
1751 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1752 where
1753 buffer_schedules_1.linkage_instance_id >= 0
1754 and buffer_schedules_1.should_be_evaluated = 1
1755 and buffer_schedules_2.id is null
1756 order by null
1757
1758INFO : (04-17 11:37:53) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1759 (138.3ms) insert into buffer_linkages_1744999692 (
1760 linkage_instance_id,
1761 slots_count_total,
1762 has_conflicts
1763 )
1764 select straight_join
1765 schedules.linkage_instance_id,
1766 count(1) as slots_count_total,
1767 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1768 from
1769 buffer_schedules_1744999692 as schedules
1770 left join (
1771 select straight_join
1772 linkage_relations.linkage_template_id
1773 from linkage_relations
1774 inner join negative_links_influences use index (ix_unique_links)
1775 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1776 and linkage_relations.call_type_id = negative_links_influences.source_id
1777 and negative_links_influences.source_type = 'CallType'
1778 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1779 and negative_links_influences.target_type = 'CallType'
1780 and linkage_relations.distance_in_days = negative_links_influences.offset
1781 where linkage_relations.client_id = 319 /* client_id */
1782 and (
1783 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1784 or linkage_relations.distance_in_days != 0
1785 )
1786 group by linkage_relations.linkage_template_id
1787 order by null
1788 ) as linkage_conflicts
1789 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1790 where schedules.linkage_instance_id >= 0
1791 group by
1792 schedules.linkage_instance_id,
1793 has_conflicts
1794 order by null
1795
1796INFO : (04-17 11:37:53) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1797 (141.7ms) insert into buffer_target_assignments_1744999692 (
1798 call_type_id,
1799 target_group_id,
1800 assignment_id,
1801 assignment_type
1802 )
1803 select
1804 t1.call_type_id,
1805 ifnull(tg.id, 0) as target_group_id,
1806 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1807 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1808 from (
1809 select ct.id as call_type_id
1810 from
1811 buffer_schedules_1744999692 as sc
1812 inner join call_types as ct
1813 on sc.call_type_id = ct.id
1814 and ct.active = 1
1815 and ct.included_in_targets > 0
1816 where linkage_instance_id >= 0
1817 group by ct.id
1818 order by null
1819 ) as t1
1820 left join target_group_calls as tgc
1821 on t1.call_type_id = tgc.call_type_id
1822 left join target_groups as tg
1823 on tgc.target_group_id = tg.id
1824 and tg.active = 1
1825 order by null
1826
1827INFO : (04-17 11:37:54) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1828INFO : (04-17 11:37:54) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1829INFO : (04-17 11:37:54) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0705
1830 (135.5ms) insert into buffer_linkage_call_type_assignments_1744999692 (
1831 linkage_instance_id,
1832 call_type_id,
1833 is_weekend,
1834 period_offset,
1835 calendar_schedule_id,
1836 max_assignments,
1837 assignments_count
1838 )
1839 select
1840 s.linkage_instance_id,
1841 s.call_type_id,
1842 s.is_weekend,
1843 s.period_offset,
1844 s.calendar_schedule_id,
1845 case
1846 when (s.is_weekend = 0) then ct.max_weekday_assignments
1847 else ct.max_weekend_assignments
1848 end as _max_assignments,
1849 sum(1) as assignments_count
1850 from buffer_schedules_1744999692 as s
1851 inner join call_types as ct
1852 on s.call_type_id = ct.id
1853 and ct.active = 1
1854 and (
1855 ct.max_weekday_assignments is not null
1856 or ct.max_weekend_assignments is not null
1857 )
1858 where s.linkage_instance_id >= 0
1859 and s.staff_id is null
1860 group by
1861 s.linkage_instance_id,
1862 s.call_type_id,
1863 s.is_weekend,
1864 s.period_offset,
1865 s.calendar_schedule_id,
1866 _max_assignments
1867 having _max_assignments >= 0
1868 order by null
1869
1870INFO : (04-17 11:37:54) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0706
1871 (153.8ms) insert into buffer_call_type_assignments_1744999692 (
1872 staff_id,
1873 call_type_id,
1874 is_weekend,
1875 period_offset,
1876 calendar_schedule_id,
1877 assignments_count
1878 )
1879 select
1880 sc.staff_id,
1881 sc.call_type_id,
1882 sc.weekend_on as is_weekend,
1883 p.period_offset,
1884 p.calendar_schedule_id,
1885 sum(1) as assignments_count
1886 from buffer_periods_1744999692 as p
1887 inner join call_types as ct
1888 on p.calendar_schedule_id = ct.calendar_schedule_id
1889 and ct.active = 1
1890 and (
1891 ct.max_weekday_assignments is not null
1892 or ct.max_weekend_assignments is not null
1893 )
1894 inner join schedules as sc
1895 on ct.id = sc.call_type_id
1896 and sc.schedule_on between p.start_date and p.end_date
1897 and sc.staff_id is not null
1898 where p.should_be_evaluated = 1
1899 group by
1900 sc.staff_id,
1901 sc.call_type_id,
1902 sc.weekend_on,
1903 p.period_offset,
1904 p.calendar_schedule_id
1905 order by null
1906
1907INFO : (04-17 11:37:54) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0707
1908 (137.6ms) insert into buffer_linkage_call_days_targets_1744999692 (
1909 linkage_instance_id,
1910 assignment_id,
1911 assignment_type,
1912 day_of_week_mask,
1913 period_offset,
1914 calendar_schedule_id,
1915 scheduled
1916 )
1917 select
1918 s.linkage_instance_id,
1919 ta.assignment_id,
1920 ta.assignment_type,
1921 s.day_of_week_mask,
1922 -1 as period_offset,
1923 s.calendar_schedule_id,
1924 sum(ci.calls) as _scheduled
1925 from
1926 buffer_schedules_1744999692 as s
1927 inner join buffer_target_assignments_1744999692 as ta
1928 on s.call_type_id = ta.call_type_id
1929 inner join buffer_counter_increments_1744999692 as ci
1930 on s.id = ci.schedule_id
1931 and ci.affects_cumulative_targets = 1
1932 where
1933 s.linkage_instance_id >= 0
1934 and s.staff_id is null
1935 group by
1936 s.linkage_instance_id,
1937 ta.assignment_id,
1938 ta.assignment_type,
1939 s.day_of_week_mask,
1940 s.calendar_schedule_id
1941 having _scheduled > 0
1942 order by null
1943
1944INFO : (04-17 11:37:54) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0708
1945 (177.2ms) insert into buffer_linkage_assigned_days_1744999692 (
1946 linkage_instance_id,
1947 period_offset,
1948 week_number,
1949 schedule_on,
1950 is_weekend
1951 )
1952 select distinct
1953 sc.linkage_instance_id,
1954 sc.period_offset,
1955 sc.week_number,
1956 sc.schedule_on,
1957 sc.is_weekend
1958 from
1959 buffer_schedules_1744999692 as sc
1960 inner join call_types as ct
1961 on sc.call_type_id = ct.id
1962 and ct.unassigned_day = 0
1963 where
1964 sc.linkage_instance_id >= 0
1965 and sc.staff_id is null
1966 order by null
1967
1968INFO : (04-17 11:37:54) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0709
1969INFO : (04-17 11:37:54) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query07_10_AL
1970 (141.2ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
1971 insert into buffer_assignment_limit_call_types_1744999692 (
1972 assignment_limit_id, call_type_id
1973 )
1974 select al.id as assignment_limit_id, ct.id as call_type_id
1975 from assignment_limits as al
1976 inner join assignment_limit_call_types as alct
1977 on al.id = alct.assignment_limit_id
1978 inner join call_types as ct
1979 on alct.call_type_id = ct.id
1980 and ct.active = 1
1981 where al.client_id = 319
1982 and al.active = 1
1983 union
1984 select al.id as assignment_limit_id, ct.id as call_type_id
1985 from assignment_limits as al
1986 inner join call_types as ct
1987 on al.client_id = ct.client_id
1988 and al.calendar_schedule_id = ct.calendar_schedule_id
1989 and ct.active = 1
1990 where al.client_id = 319
1991 and al.active = 1
1992 and al.all_current_schedule_assignments = 1
1993 union
1994 select al.id as assignment_limit_id, ct.id as call_type_id
1995 from assignment_limits as al
1996 inner join call_types as ct
1997 on al.client_id = ct.client_id
1998 and al.calendar_schedule_id != ct.calendar_schedule_id
1999 and ct.active = 1
2000 where al.client_id = 319
2001 and al.active = 1
2002 and al.all_other_schedule_assignments = 1
2003 order by null
2004
2005 (273.9ms) /* AssignmentLimitsPerPeriod, AssignmentLimitsPerWeek, AssignmentLimitsRolling, AssignmentLimitsConsecutive */
2006 insert into buffer_assignment_limit_staff_types_1744999692 (
2007 assignment_limit_id, staff_type_id
2008 )
2009 select al.id as assignment_limit_id, st.id as staff_type_id
2010 from assignment_limits as al
2011 inner join assignment_limit_staff_types as alst
2012 on al.id = alst.assignment_limit_id
2013 inner join staff_types as st
2014 on alst.staff_type_id = st.id
2015 and st.active = 1
2016 where al.client_id = 319
2017 and al.active = 1
2018 union
2019 select al.id as assignment_limit_id, st.id as staff_type_id
2020 from assignment_limits as al
2021 inner join staff_types as st
2022 on al.client_id = st.client_id
2023 and st.active = 1
2024 where al.client_id = 319
2025 and al.active = 1
2026 and al.all_staff_types = 1
2027 order by null
2028
2029INFO : (04-17 11:37:55) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
2030 (137.5ms) /* AssignmentLimitsPerPeriod */
2031 insert into buffer_assignment_limit_periods_1744999692 (
2032 assignment_limit_id, period_id, start_date, end_date
2033 )
2034 select
2035 al.id as assignment_limit_id,
2036 if(
2037 bs.blocks_type = 0,
2038 if(
2039 (month(b.start_date) - pt.begin_period) >= 0,
2040 year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type + 1,
2041 (year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type + 1
2042 ),
2043 b.display_order
2044 ) as period_id,
2045 min(b.start_date) as start_date,
2046 max(b.end_date) as end_date
2047 from assignment_limits as al
2048 inner join calendar_schedules as cs
2049 on al.calendar_schedule_id = cs.id
2050 inner join block_schedules as bs
2051 on cs.block_schedule_id = bs.id
2052 inner join blocks as b
2053 on bs.id = b.block_schedule_id
2054 and b.end_date >= date_add(now(), interval -24 month)
2055 inner join population_timeframes as pt
2056 on pt.client_id = 319
2057 and al.timeframe_type = pt.timeframe_type
2058 where al.client_id = 319
2059 and al.active = 1
2060 and al.max_per_period_limit is not null
2061 group by al.id, period_id
2062 order by null
2063
2064 (135.5ms) /* AssignmentLimitsPerPeriod */
2065 insert into buffer_linkage_assignments_count_periods_1744999692 (
2066 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2067 )
2068 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date,
2069 count(distinct sc.id) as assignments_count
2070 from buffer_schedules_1744999692 as sc
2071 inner join buffer_assignment_limit_call_types_1744999692 as alct
2072 on sc.call_type_id = alct.call_type_id
2073 inner join assignment_limits as al
2074 on alct.assignment_limit_id = al.id
2075 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2076 and al.max_per_period_limit is not null
2077 inner join buffer_assignment_limit_periods_1744999692 as alp
2078 on alct.assignment_limit_id = alp.assignment_limit_id
2079 and sc.schedule_on between alp.start_date and alp.end_date
2080 where sc.linkage_instance_id >= 0
2081 and sc.staff_id is null
2082 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, alp.start_date, alp.end_date
2083 order by null
2084
2085INFO : (04-17 11:37:55) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
2086 (135.6ms) /* AssignmentLimitsPerWeek */
2087 insert into buffer_linkage_assignments_count_weeks_1744999692 (
2088 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2089 )
2090 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2091 if(
2092 cl.start_of_week = 0,
2093 date_add(sc.schedule_on, interval (1 - dayofweek(sc.schedule_on)) day),
2094 date_add(sc.schedule_on, interval (0 - weekday(sc.schedule_on)) day)
2095 ) as start_date,
2096 if(
2097 cl.start_of_week = 0,
2098 date_add(sc.schedule_on, interval (7 - dayofweek(sc.schedule_on)) day),
2099 date_add(sc.schedule_on, interval (6 - weekday(sc.schedule_on)) day)
2100 ) as end_date,
2101 count(distinct sc.id) as assignments_count
2102 from buffer_schedules_1744999692 as sc
2103 inner join buffer_assignment_limit_call_types_1744999692 as alct
2104 on sc.call_type_id = alct.call_type_id
2105 inner join assignment_limits as al
2106 on alct.assignment_limit_id = al.id
2107 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2108 and al.max_weekly_limit is not null
2109 inner join clients as cl
2110 on cl.id = 319
2111 where sc.linkage_instance_id >= 0
2112 and sc.staff_id is null
2113 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2114 order by null
2115
2116INFO : (04-17 11:37:55) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
2117 (135.7ms) /* AssignmentLimitsRolling */
2118 insert into buffer_linkage_assignments_count_rolling_1744999692 (
2119 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date, assignments_count
2120 )
2121 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2122 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as start_date,
2123 date_add(sc.schedule_on, interval (al.max_rolling_period - ns.item_number) day) as end_date,
2124 count(distinct sc.id) as assignments_count
2125 from buffer_schedules_1744999692 as sc
2126 inner join buffer_assignment_limit_call_types_1744999692 as alct
2127 on sc.call_type_id = alct.call_type_id
2128 inner join assignment_limits as al
2129 on alct.assignment_limit_id = al.id
2130 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2131 and al.max_rolling_period is not null
2132 and al.max_rolling_limit is not null
2133 inner join numeric_sequence as ns
2134 on ns.item_number between 1 and al.max_rolling_period
2135 where sc.linkage_instance_id >= 0
2136 and sc.staff_id is null
2137 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2138 order by null
2139
2140INFO : (04-17 11:37:55) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
2141 (134.9ms) /* AssignmentLimitsConsecutive */
2142 insert into buffer_linkage_assignments_count_consecutive_1744999692 (
2143 linkage_instance_id, assignment_limit_id, schedule_on, start_date, end_date
2144 )
2145 select sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on,
2146 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as start_date,
2147 date_add(sc.schedule_on, interval ((al.max_consecutive_limit + 1) - ns.item_number) day) as end_date
2148 from buffer_schedules_1744999692 as sc
2149 inner join buffer_assignment_limit_call_types_1744999692 as alct
2150 on sc.call_type_id = alct.call_type_id
2151 inner join assignment_limits as al
2152 on alct.assignment_limit_id = al.id
2153 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2154 and al.max_consecutive_limit is not null
2155 inner join numeric_sequence as ns
2156 on ns.item_number between 1 and (al.max_consecutive_limit + 1)
2157 where sc.linkage_instance_id >= 0
2158 and sc.staff_id is null
2159 group by sc.linkage_instance_id, alct.assignment_limit_id, sc.schedule_on, start_date, end_date
2160 order by null
2161
2162INFO : (04-17 11:37:55) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query08
2163 (143.8ms) insert into buffer_staff_daily_counts_1744999692 (
2164 staff_id, schedule_on, linkage_instance_id, slots_count
2165 )
2166 select staff_id, schedule_on, null as linkage_instance_id,
2167 count(1) as slots_count
2168 from buffer_schedules_1744999692
2169 where staff_id is not null
2170 and call_type_id > 0 /* VC */
2171 group by staff_id, schedule_on
2172 order by null
2173
2174 (136.1ms) insert into buffer_staff_daily_counts_1744999692 (
2175 staff_id, schedule_on, linkage_instance_id, slots_count
2176 )
2177 select staff_id, schedule_on, linkage_instance_id,
2178 count(1) as slots_count
2179 from buffer_schedules_1744999692
2180 where staff_id is not null
2181 and call_type_id > 0 /* VC */
2182 group by staff_id, schedule_on, linkage_instance_id
2183 order by null
2184
2185INFO : (04-17 11:37:56) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query085
2186 (260.7ms) insert into buffer_staff_specializations_1744999692 (
2187 staff_id, specialization_id
2188 )
2189 select staff_id, specialization_id
2190 from view_staff_specializations
2191 where client_id = 319
2192 order by null
2193
2194 (273.0ms) insert into buffer_specialization_daily_counts_1744999692 (
2195 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2196 )
2197 select sp.id as specialization_id,
2198 null as staff_id,
2199 bs_1.schedule_on,
2200 count(distinct st_sp.staff_id) as staffs_scheduled,
2201 sp.max_scheduled,
2202 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
2203 from (
2204 select schedule_on, day_of_week_mask
2205 from buffer_schedules_1744999692
2206 where should_be_evaluated = 1
2207 group by schedule_on, day_of_week_mask
2208 order by null
2209 ) as bs_1
2210 inner join specializations as sp
2211 on sp.client_id = 319
2212 and sp.active = 1
2213 inner join buffer_schedules_1744999692 as bs_2
2214 on bs_1.schedule_on = bs_2.schedule_on
2215 and bs_2.call_type_id > 0 /* is not VC */
2216 inner join max_applicable_call_types as mact
2217 on sp.id = mact.specialization_id
2218 and bs_2.call_type_id = mact.call_type_id
2219 left join buffer_staff_specializations_1744999692 as st_sp
2220 on bs_2.staff_id = st_sp.staff_id
2221 and sp.id = st_sp.specialization_id
2222 group by sp.id, bs_1.schedule_on, sp.max_scheduled
2223 order by null
2224
2225 (162.9ms) insert into buffer_specialization_daily_counts_1744999692 (
2226 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2227 )
2228 select sp.id as specialization_id,
2229 st_sp.staff_id,
2230 bs_1.schedule_on,
2231 1 as staffs_scheduled,
2232 sp.max_scheduled,
2233 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
2234 from (
2235 select schedule_on, day_of_week_mask
2236 from buffer_schedules_1744999692
2237 where should_be_evaluated = 1
2238 group by schedule_on, day_of_week_mask
2239 order by null
2240 ) as bs_1
2241 inner join specializations as sp
2242 on sp.client_id = 319
2243 and sp.active = 1
2244 inner join buffer_schedules_1744999692 as bs_2
2245 on bs_1.schedule_on = bs_2.schedule_on
2246 and bs_2.call_type_id > 0 /* is not VC */
2247 inner join max_applicable_call_types as mact
2248 on sp.id = mact.specialization_id
2249 and bs_2.call_type_id = mact.call_type_id
2250 inner join buffer_staff_specializations_1744999692 as st_sp
2251 on bs_2.staff_id = st_sp.staff_id
2252 and sp.id = st_sp.specialization_id
2253 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
2254 order by null
2255
2256INFO : (04-17 11:37:56) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query09
2257 (135.4ms) truncate table buffer_location_privileges_1744999692
2258
2259 (441.7ms) insert into buffer_location_privileges_1744999692 (
2260 staff_id,
2261 location_id,
2262 begin_date,
2263 end_date
2264 )
2265 select
2266 s.id as staff_id,
2267 l.location_id,
2268 '0000-00-00' as begin_date,
2269 '9999-12-31' as end_date
2270 from (
2271 select l.id as location_id
2272 from
2273 buffer_schedules_1744999692 as sc
2274 inner join call_types as ct
2275 on sc.call_type_id = ct.id
2276 and ct.active = 1
2277 inner join locations as l
2278 on ct.location_id = l.id
2279 and l.active = 1
2280 where sc.linkage_instance_id >= 0
2281 and sc.should_be_evaluated = 1
2282 group by l.id
2283 order by null
2284 ) as l
2285 inner join staffs as s
2286 on s.client_id = 319
2287 and s.active = 1
2288 and s.take_call = 1
2289 and s.auto_populatable = 1
2290 left join location_privileges as lp
2291 on s.id = lp.staff_id
2292 where lp.id is null
2293 union
2294 select
2295 lp.staff_id,
2296 lp.location_id,
2297 lp.begin_date,
2298 lp.end_date
2299 from (
2300 select l.id as location_id
2301 from
2302 buffer_schedules_1744999692 as sc
2303 inner join call_types as ct
2304 on sc.call_type_id = ct.id
2305 and ct.active = 1
2306 inner join locations as l
2307 on ct.location_id = l.id
2308 and l.active = 1
2309 where sc.linkage_instance_id >= 0
2310 and sc.should_be_evaluated = 1
2311 group by l.id
2312 order by null
2313 ) as l
2314 inner join location_privileges as lp
2315 on l.location_id = lp.location_id
2316 order by null;
2317
2318 (308.8ms) insert into buffer_static_availabilities_1744999692 (
2319 schedule_id,
2320 staff_id,
2321 staff_type_id,
2322 is_primary
2323 )
2324 select
2325 schedules.id as schedule_id,
2326 staffs.id as staff_id,
2327 assign_staff_types.staff_type_id,
2328 if(schedules.day_of_week_mask & staff_call_types.days_of_week_mask & ifnull(rotation_call_types.days_of_week_mask, 127) > 0, 1, 0) as is_primary
2329 from
2330 buffer_schedules_1744999692 as schedules
2331 /* AvailabilityRule */
2332 inner join call_types
2333 on schedules.call_type_id = call_types.id
2334 and call_types.active = 1
2335 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
2336 inner join staff_call_types
2337 on call_types.id = staff_call_types.call_type_id
2338 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
2339 inner join staffs
2340 on staff_call_types.staff_id = staffs.id
2341 and staffs.active = 1
2342 and staffs.take_call = 1
2343 and staffs.auto_populatable = 1
2344 inner join buffer_location_privileges_1744999692 as location_privileges
2345 on staffs.id = location_privileges.staff_id
2346 and call_types.location_id = location_privileges.location_id
2347 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
2348 inner join assign_staff_types
2349 on staffs.id = assign_staff_types.staff_id
2350 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
2351 left join eligible_staff_types
2352 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
2353 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
2354 left join staff_rotations
2355 on staffs.id = staff_rotations.staff_id
2356 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
2357 left join daily_rotations
2358 on staffs.id = daily_rotations.staff_id
2359 and schedules.schedule_on = daily_rotations.schedule_on
2360 left join rotations
2361 on rotations.id = ifnull(daily_rotations.rotation_id, staff_rotations.rotation_id)
2362 and rotations.active = 1
2363 left join rotation_call_types
2364 on rotations.id = rotation_call_types.rotation_id
2365 and staff_call_types.call_type_id = rotation_call_types.call_type_id
2366 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
2367 /* RequiredRotationsRule */
2368 left join required_rotations
2369 on staff_call_types.call_type_id = required_rotations.call_type_id
2370 left join staff_rotations as staff_rotations2
2371 on staffs.id = staff_rotations2.staff_id
2372 and required_rotations.rotation_id = staff_rotations2.rotation_id
2373 and schedules.schedule_on >= staff_rotations2.start_date
2374 /* NoCallRequestsHiddenHardRule */
2375 left join request_settings
2376 on assign_staff_types.staff_type_id = request_settings.staff_type_id
2377 left join requests
2378 on schedules.schedule_on = requests.schedule_on
2379 and staffs.id = requests.staff_id
2380 and requests.request_type = 0 /* type = no_call */
2381 and requests.status in (1, 3) /* status = approved or fulfilled */
2382 and call_types.is_no_call_requestable = 1
2383 left join request_call_types
2384 on requests.id = request_call_types.request_id
2385 and schedules.call_type_id = request_call_types.call_type_id
2386 where
2387 schedules.linkage_instance_id >= 0
2388 and schedules.should_be_evaluated = 1
2389 and (
2390 call_types.all_staff_types = 1
2391 or eligible_staff_types.id is not null
2392 )
2393 and (
2394 rotations.id is null
2395 or rotation_call_types.id is not null
2396 )
2397 and (
2398 requests.id is null
2399 or (
2400 requests.all_call_types = 1
2401 and request_settings.call_only_all_call_switch = 1 /* calls only */
2402 and call_types.assignment_type = 1 /* clinical */
2403 )
2404 or (
2405 requests.all_call_types = 0
2406 and request_call_types.id is null
2407 )
2408 )
2409 group by
2410 schedules.id,
2411 staffs.id,
2412 assign_staff_types.staff_type_id,
2413 is_primary
2414 having
2415 sum(if(required_rotations.id is not null and staff_rotations2.id is null, 1, 0)) = 0 /* RequiredRotationsRule */
2416 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
2417 order by null
2418
2419INFO : (04-17 11:37:57) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query092
2420 (138.8ms) /* RotationPostCallRule */
2421 /* Exclude people and slots when a post-call is not allowed on the first day of a rotation */
2422 delete sa
2423 from buffer_static_availabilities_1744999692 as sa
2424 inner join buffer_schedules_1744999692 as sc
2425 on sa.schedule_id = sc.id
2426 inner join call_types as ct
2427 on sc.call_type_id = ct.id
2428 and ct.assignment_type = 0 /* call */
2429 inner join staff_rotations as sr
2430 on sa.staff_id = sr.staff_id
2431 and date_add(sc.schedule_on, interval 1 day) = sr.start_date
2432 inner join rotations as r
2433 on sr.rotation_id = r.id
2434 and r.post_call_allowed = 0
2435
2436INFO : (04-17 11:37:57) --> fill: 34 - Scheduler::AutoPopulate::Queries::Query093
2437INFO : (04-17 11:37:57) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query095
2438 (445.9ms) insert into buffer_negative_relations_1744999692 (
2439 schedule_id,
2440 affected_by_schedule_id,
2441 staff_id
2442 )
2443 select
2444 b_schedules.id as schedule_id,
2445 b_schedules_negative_link.id as affected_by_schedule_id,
2446 b_schedules_negative_link.staff_id
2447 from
2448 buffer_schedules_1744999692 as b_schedules
2449 /* NegativeLinkRule */
2450 inner join negative_links_influences
2451 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2452 and b_schedules.call_type_id = negative_links_influences.source_id
2453 and negative_links_influences.source_type = 'CallType'
2454 and negative_links_influences.target_type = 'CallType'
2455 inner join buffer_schedules_1744999692 as b_schedules_negative_link
2456 on date_add(
2457 b_schedules.schedule_on,
2458 interval negative_links_influences.offset day
2459 ) = b_schedules_negative_link.schedule_on
2460 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
2461 and b_schedules.id != b_schedules_negative_link.id
2462 where
2463 b_schedules.linkage_instance_id >= 0
2464 and b_schedules.should_be_evaluated = 1
2465 order by null
2466
2467 (141.2ms) insert into buffer_negative_relations_1744999692 (
2468 schedule_id,
2469 affected_by_schedule_id,
2470 staff_id
2471 )
2472 select
2473 b_schedules.id as schedule_id,
2474 b_schedules_negative_link.id as affected_by_schedule_id,
2475 b_schedules_negative_link.staff_id
2476 from
2477 buffer_schedules_1744999692 as b_schedules
2478 /* NegativeLinkRule */
2479 inner join negative_links_influences
2480 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
2481 and b_schedules.call_type_id = negative_links_influences.source_id
2482 and negative_links_influences.source_type = 'CallType'
2483 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
2484 inner join buffer_schedules_1744999692 as b_schedules_negative_link
2485 on date_add(
2486 b_schedules.schedule_on,
2487 interval negative_links_influences.offset day
2488 ) = b_schedules_negative_link.schedule_on
2489 and (-1 * negative_links_influences.target_id) = b_schedules_negative_link.call_type_id
2490 and b_schedules.id != b_schedules_negative_link.id
2491 where
2492 b_schedules.linkage_instance_id >= 0
2493 and b_schedules.should_be_evaluated = 1
2494 order by null
2495
2496 (135.3ms) delete from buffer_schedules_1744999692
2497 where call_type_id < 0 /* vacations and non-clinicals */
2498
2499INFO : (04-17 11:37:58) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query097
2500 (295.1ms) insert into buffer_staff_assigned_days_1744999692 (
2501 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
2502 )
2503 select staffs.staff_id,
2504 if(days.within_period = 1, days.period_offset, null) as period_offset,
2505 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
2506 days.schedule_on,
2507 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
2508 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
2509 from (
2510 select distinct staff_id
2511 from buffer_static_availabilities_1744999692
2512 order by null
2513 ) as staffs
2514 inner join (
2515 select p.period_offset,
2516 date_add(date_add(p.start_date, interval -6 day), interval (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) day) as schedule_on,
2517 if((digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) - 6 between 0 and datediff(p.end_date, p.start_date), 1, 0) as within_period
2518 from buffer_periods_1744999692 as p
2519 inner join (
2520 select 0 as digit union select 1 union select 2 union select 3 union select 4
2521 union select 5 union select 6 union select 7 union select 8 union select 9
2522 ) as digits_1
2523 inner join (
2524 select 0 as digit union select 1 union select 2 union select 3 union select 4
2525 union select 5 union select 6 union select 7 union select 8 union select 9
2526 ) as digits_2
2527 inner join (
2528 select 0 as digit union select 1 union select 2 union select 3 union select 4
2529 union select 5 union select 6 union select 7 union select 8 union select 9
2530 ) as digits_3
2531 where p.calendar_schedule_id = 1381
2532 and p.should_be_evaluated = 1
2533 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
2534 order by null
2535 ) as days
2536 inner join (
2537 select min(start_date) as start_date, max(end_date) as end_date
2538 from buffer_periods_1744999692
2539 where calendar_schedule_id = 1381
2540 and should_be_evaluated = 1
2541 order by null
2542 ) as limits
2543 inner join clients
2544 on clients.id = 319
2545 left join (
2546 select sc.staff_id, sc.schedule_on
2547 from buffer_periods_1744999692 as p
2548 inner join schedules as sc
2549 on sc.client_id = 319
2550 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2551 inner join call_types as ct
2552 on sc.call_type_id = ct.id
2553 and ct.unassigned_day = 0
2554 where p.calendar_schedule_id = 1381
2555 and p.should_be_evaluated = 1
2556 group by sc.staff_id, sc.schedule_on
2557 order by null
2558 ) as schedules
2559 on staffs.staff_id = schedules.staff_id
2560 and days.schedule_on = schedules.schedule_on
2561 left join (
2562 select v.staff_id, v.schedule_on
2563 from buffer_periods_1744999692 as p
2564 inner join vacations as v
2565 on v.client_id = 319
2566 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2567 and v.status IN (3)
2568 inner join non_clinical_types as nct
2569 on v.non_clinical_type_id = nct.id
2570 and nct.active = 1
2571 and nct.unassigned_day = 0
2572 where p.calendar_schedule_id = 1381
2573 and p.should_be_evaluated = 1
2574 group by v.staff_id, v.schedule_on
2575 order by null
2576 ) as vacations
2577 on staffs.staff_id = vacations.staff_id
2578 and days.schedule_on = vacations.schedule_on
2579 where days.within_period = 1
2580 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
2581 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
2582 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
2583 )
2584 order by null
2585
2586INFO : (04-17 11:37:58) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query098
2587 (141.4ms) insert into buffer_combined_availabilities_1744999692 (
2588 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
2589 )
2590 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
2591 sc.linkage_instance_id, 0 as has_violations
2592 from buffer_schedules_1744999692 as sc
2593 /* Static availability */
2594 inner join buffer_static_availabilities_1744999692 as sa
2595 on sc.id = sa.schedule_id
2596 inner join buffer_linkages_1744999692 as l
2597 on sc.linkage_instance_id = l.linkage_instance_id
2598 where sc.should_be_evaluated = 1
2599 and l.has_conflicts = 0
2600
2601 (136.8ms) insert into buffer_schedule_linkages_1744999692 (
2602 staff_id, linkage_instance_id, slots_count
2603 )
2604 select staff_id, linkage_instance_id, count(1) as slots_count
2605 from buffer_schedules_1744999692 use index (ix_buffer_schedules_composite_2)
2606 where staff_id is not null
2607 and linkage_instance_id >= 0
2608 group by staff_id, linkage_instance_id
2609 order by null
2610
2611INFO : (04-17 11:37:59) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
2612 (136.0ms) /* AssignmentLimitsPerPeriod */
2613 insert into buffer_staff_assignments_count_periods_1744999692 (
2614 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2615 )
2616 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2617 count(distinct sc.id) as assignments_count
2618 from (
2619 select linkage_instance_id, assignment_limit_id, start_date, end_date
2620 from buffer_linkage_assignments_count_periods_1744999692
2621 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2622 order by null
2623 ) as lac
2624 inner join (
2625 select linkage_instance_id, staff_id
2626 from buffer_combined_availabilities_1744999692
2627 group by linkage_instance_id, staff_id
2628 order by null
2629 ) as ca
2630 on lac.linkage_instance_id = ca.linkage_instance_id
2631 inner join assignment_limits as al
2632 on lac.assignment_limit_id = al.id
2633 inner join schedules as sc
2634 on ca.staff_id = sc.staff_id
2635 and sc.schedule_on between lac.start_date and lac.end_date
2636 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2637 inner join buffer_assignment_limit_call_types_1744999692 as alct
2638 on lac.assignment_limit_id = alct.assignment_limit_id
2639 and sc.call_type_id = alct.call_type_id
2640 inner join assign_staff_types as ast
2641 on sc.staff_id = ast.staff_id
2642 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2643 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2644 on lac.assignment_limit_id = alst.assignment_limit_id
2645 and ast.staff_type_id = alst.staff_type_id
2646 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2647 order by null
2648
2649INFO : (04-17 11:37:59) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
2650 (135.7ms) /* AssignmentLimitsPerWeek */
2651 insert into buffer_staff_assignments_count_weeks_1744999692 (
2652 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2653 )
2654 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2655 count(distinct sc.id) as assignments_count
2656 from (
2657 select linkage_instance_id, assignment_limit_id, start_date, end_date
2658 from buffer_linkage_assignments_count_weeks_1744999692
2659 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2660 order by null
2661 ) as lac
2662 inner join (
2663 select linkage_instance_id, staff_id
2664 from buffer_combined_availabilities_1744999692
2665 group by linkage_instance_id, staff_id
2666 order by null
2667 ) as ca
2668 on lac.linkage_instance_id = ca.linkage_instance_id
2669 inner join assignment_limits as al
2670 on lac.assignment_limit_id = al.id
2671 inner join schedules as sc
2672 on ca.staff_id = sc.staff_id
2673 and sc.schedule_on between lac.start_date and lac.end_date
2674 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2675 inner join buffer_assignment_limit_call_types_1744999692 as alct
2676 on lac.assignment_limit_id = alct.assignment_limit_id
2677 and sc.call_type_id = alct.call_type_id
2678 inner join assign_staff_types as ast
2679 on sc.staff_id = ast.staff_id
2680 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2681 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2682 on lac.assignment_limit_id = alst.assignment_limit_id
2683 and ast.staff_type_id = alst.staff_type_id
2684 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2685 order by null
2686
2687INFO : (04-17 11:37:59) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
2688 (137.0ms) /* AssignmentLimitsRolling */
2689 insert into buffer_staff_assignments_count_rolling_1744999692 (
2690 assignment_limit_id, staff_id, start_date, end_date, assignments_count
2691 )
2692 select straight_join lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date,
2693 count(distinct sc.id) as assignments_count
2694 from (
2695 select linkage_instance_id, assignment_limit_id, start_date, end_date
2696 from buffer_linkage_assignments_count_rolling_1744999692
2697 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2698 order by null
2699 ) as lac
2700 inner join (
2701 select linkage_instance_id, staff_id
2702 from buffer_combined_availabilities_1744999692
2703 group by linkage_instance_id, staff_id
2704 order by null
2705 ) as ca
2706 on lac.linkage_instance_id = ca.linkage_instance_id
2707 inner join assignment_limits as al
2708 on lac.assignment_limit_id = al.id
2709 inner join schedules as sc
2710 on ca.staff_id = sc.staff_id
2711 and sc.schedule_on between lac.start_date and lac.end_date
2712 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2713 inner join buffer_assignment_limit_call_types_1744999692 as alct
2714 on lac.assignment_limit_id = alct.assignment_limit_id
2715 and sc.call_type_id = alct.call_type_id
2716 inner join assign_staff_types as ast
2717 on sc.staff_id = ast.staff_id
2718 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2719 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2720 on lac.assignment_limit_id = alst.assignment_limit_id
2721 and ast.staff_type_id = alst.staff_type_id
2722 group by lac.assignment_limit_id, ca.staff_id, lac.start_date, lac.end_date
2723 order by null
2724
2725INFO : (04-17 11:37:59) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
2726 (134.8ms) /* AssignmentLimitsConsecutive */
2727 insert into buffer_staff_assignments_count_consecutive_1744999692 (
2728 assignment_limit_id, staff_id, schedule_on, start_date, end_date
2729 )
2730 select straight_join lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2731 from (
2732 select linkage_instance_id, assignment_limit_id, start_date, end_date
2733 from buffer_linkage_assignments_count_consecutive_1744999692
2734 group by linkage_instance_id, assignment_limit_id, start_date, end_date
2735 order by null
2736 ) as lac
2737 inner join (
2738 select linkage_instance_id, staff_id
2739 from buffer_combined_availabilities_1744999692
2740 group by linkage_instance_id, staff_id
2741 order by null
2742 ) as ca
2743 on lac.linkage_instance_id = ca.linkage_instance_id
2744 inner join assignment_limits as al
2745 on lac.assignment_limit_id = al.id
2746 inner join schedules as sc
2747 on ca.staff_id = sc.staff_id
2748 and sc.schedule_on between lac.start_date and lac.end_date
2749 and ((1 << (dayofweek(sc.schedule_on) - 1)) & al.considered_days_of_week_mask) > 0
2750 inner join buffer_assignment_limit_call_types_1744999692 as alct
2751 on lac.assignment_limit_id = alct.assignment_limit_id
2752 and sc.call_type_id = alct.call_type_id
2753 inner join assign_staff_types as ast
2754 on sc.staff_id = ast.staff_id
2755 and sc.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
2756 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2757 on lac.assignment_limit_id = alst.assignment_limit_id
2758 and ast.staff_type_id = alst.staff_type_id
2759 group by lac.assignment_limit_id, ca.staff_id, sc.schedule_on, lac.start_date, lac.end_date
2760 order by null
2761
2762INFO : (04-17 11:37:59) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Points
2763INFO : (04-17 11:37:59) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
2764INFO : (04-17 11:37:59) --> fill: 44 - Scheduler::AutoPopulate::Queries::Query10
2765 RuleSet Load (156.4ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1502 LIMIT 1
2766 Client Load (272.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
2767 (135.9ms) /* SpecializationOverlapRule */
2768 delete ca
2769 from buffer_combined_availabilities_1744999692 as ca use index (ix_buffer_combined_availabilities_composite_1)
2770 inner join buffer_schedules_1744999692 as sc
2771 on ca.schedule_id = sc.id
2772 inner join buffer_staff_specializations_1744999692 as ssp
2773 on ca.staff_id = ssp.staff_id
2774 inner join max_applicable_call_types as mact
2775 on ssp.specialization_id = mact.specialization_id
2776 and sc.call_type_id = mact.call_type_id
2777 inner join buffer_specialization_daily_counts_1744999692 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2778 on ssp.specialization_id = sdc.specialization_id
2779 and sc.schedule_on = sdc.schedule_on
2780 and sdc.staff_id is null
2781 left join buffer_specialization_daily_counts_1744999692 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2782 on ssp.specialization_id = sdc_2.specialization_id
2783 and sc.schedule_on = sdc_2.schedule_on
2784 and ca.staff_id = sdc_2.staff_id
2785 where sdc.should_be_evaluated = 1
2786 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2787
2788 (137.3ms) delete ca
2789 from buffer_combined_availabilities_1744999692 as ca
2790 inner join (
2791 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2792 sum(lac.assignments_count) as assignments_count
2793 from (
2794 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2795 from buffer_combined_availabilities_1744999692 as ca
2796 inner join buffer_schedules_1744999692 as sc
2797 on ca.schedule_id = sc.id
2798 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2799 order by null
2800 ) as ca_sc
2801 inner join buffer_linkage_assignments_count_weeks_1744999692 as lac
2802 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2803 and ca_sc.schedule_on = lac.schedule_on
2804 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2805 on lac.assignment_limit_id = alst.assignment_limit_id
2806 and ca_sc.staff_type_id = alst.staff_type_id
2807 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2808 order by null
2809 ) as lac
2810 on ca.linkage_instance_id = lac.linkage_instance_id
2811 and ca.staff_id = lac.staff_id
2812 inner join assignment_limits as al
2813 on lac.assignment_limit_id = al.id
2814 left join buffer_staff_assignments_count_weeks_1744999692 as sac
2815 on lac.assignment_limit_id = sac.assignment_limit_id
2816 and lac.staff_id = sac.staff_id
2817 and lac.start_date = sac.start_date
2818 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_weekly_limit
2819
2820 (136.7ms) delete ca
2821 from buffer_combined_availabilities_1744999692 as ca
2822 inner join (
2823 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2824 sum(lac.assignments_count) as assignments_count
2825 from (
2826 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2827 from buffer_combined_availabilities_1744999692 as ca
2828 inner join buffer_schedules_1744999692 as sc
2829 on ca.schedule_id = sc.id
2830 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2831 order by null
2832 ) as ca_sc
2833 inner join buffer_linkage_assignments_count_periods_1744999692 as lac
2834 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2835 and ca_sc.schedule_on = lac.schedule_on
2836 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2837 on lac.assignment_limit_id = alst.assignment_limit_id
2838 and ca_sc.staff_type_id = alst.staff_type_id
2839 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2840 order by null
2841 ) as lac
2842 on ca.linkage_instance_id = lac.linkage_instance_id
2843 and ca.staff_id = lac.staff_id
2844 inner join assignment_limits as al
2845 on lac.assignment_limit_id = al.id
2846 left join buffer_staff_assignments_count_periods_1744999692 as sac
2847 on lac.assignment_limit_id = sac.assignment_limit_id
2848 and lac.staff_id = sac.staff_id
2849 and lac.start_date = sac.start_date
2850 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_per_period_limit
2851
2852 (176.5ms) delete ca
2853 from buffer_combined_availabilities_1744999692 as ca
2854 inner join (
2855 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2856 sum(lac.assignments_count) as assignments_count
2857 from (
2858 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2859 from buffer_combined_availabilities_1744999692 as ca
2860 inner join buffer_schedules_1744999692 as sc
2861 on ca.schedule_id = sc.id
2862 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2863 order by null
2864 ) as ca_sc
2865 inner join buffer_linkage_assignments_count_rolling_1744999692 as lac
2866 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2867 and ca_sc.schedule_on = lac.schedule_on
2868 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2869 on lac.assignment_limit_id = alst.assignment_limit_id
2870 and ca_sc.staff_type_id = alst.staff_type_id
2871 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2872 order by null
2873 ) as lac
2874 on ca.linkage_instance_id = lac.linkage_instance_id
2875 and ca.staff_id = lac.staff_id
2876 inner join assignment_limits as al
2877 on lac.assignment_limit_id = al.id
2878 left join buffer_staff_assignments_count_rolling_1744999692 as sac
2879 on lac.assignment_limit_id = sac.assignment_limit_id
2880 and lac.staff_id = sac.staff_id
2881 and lac.start_date = sac.start_date
2882 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_rolling_limit
2883
2884 (141.6ms) delete ca
2885 from buffer_combined_availabilities_1744999692 as ca
2886 inner join (
2887 select ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date,
2888 count(distinct if(sac.id is null, lac.schedule_on, null)) as assignments_count
2889 from (
2890 select ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2891 from buffer_combined_availabilities_1744999692 as ca
2892 inner join buffer_schedules_1744999692 as sc
2893 on ca.schedule_id = sc.id
2894 group by ca.staff_id, ca.staff_type_id, sc.linkage_instance_id, sc.schedule_on
2895 order by null
2896 ) as ca_sc
2897 inner join buffer_linkage_assignments_count_consecutive_1744999692 as lac
2898 on ca_sc.linkage_instance_id = lac.linkage_instance_id
2899 and ca_sc.schedule_on = lac.schedule_on
2900 inner join buffer_assignment_limit_staff_types_1744999692 as alst
2901 on lac.assignment_limit_id = alst.assignment_limit_id
2902 and ca_sc.staff_type_id = alst.staff_type_id
2903 left join buffer_staff_assignments_count_consecutive_1744999692 as sac
2904 on lac.assignment_limit_id = sac.assignment_limit_id
2905 and ca_sc.staff_id = sac.staff_id
2906 and lac.schedule_on = sac.schedule_on
2907 and lac.start_date = sac.start_date
2908 group by ca_sc.linkage_instance_id, lac.assignment_limit_id, ca_sc.staff_id, lac.start_date
2909 order by null
2910 ) as lac
2911 on ca.linkage_instance_id = lac.linkage_instance_id
2912 and ca.staff_id = lac.staff_id
2913 inner join assignment_limits as al
2914 on lac.assignment_limit_id = al.id
2915 left join (
2916 select assignment_limit_id, staff_id, start_date,
2917 count(schedule_on) as assignments_count
2918 from buffer_staff_assignments_count_consecutive_1744999692
2919 group by assignment_limit_id, staff_id, start_date
2920 ) as sac
2921 on lac.assignment_limit_id = sac.assignment_limit_id
2922 and lac.staff_id = sac.staff_id
2923 and lac.start_date = sac.start_date
2924 where (lac.assignments_count + ifnull(sac.assignments_count, 0)) > al.max_consecutive_limit
2925
2926 (164.6ms) /* CumulativeCallDaysTargetsRule */
2927 delete ca
2928 from
2929 buffer_combined_availabilities_1744999692 as ca
2930 inner join buffer_schedules_1744999692 as sc
2931 on ca.schedule_id = sc.id
2932 inner join buffer_target_assignments_1744999692 as ta
2933 on sc.call_type_id = ta.call_type_id
2934 inner join buffer_linkage_call_days_targets_1744999692 as lcdt
2935 on sc.linkage_instance_id = lcdt.linkage_instance_id
2936 and ta.assignment_id = lcdt.assignment_id
2937 and ta.assignment_type = lcdt.assignment_type
2938 and sc.day_of_week_mask = lcdt.day_of_week_mask
2939 and lcdt.period_offset = -1
2940 and sc.calendar_schedule_id = lcdt.calendar_schedule_id
2941 left join buffer_cumulative_cd_targets_1744999692 as cdt
2942 on ca.staff_id = cdt.staff_id
2943 and ta.assignment_id = cdt.assignment_id
2944 and ta.assignment_type = cdt.assignment_type
2945 and sc.day_of_week_mask = cdt.day_of_week_mask
2946 and sc.calendar_schedule_id = cdt.calendar_schedule_id
2947 where
2948 (sc.day_of_week_mask & 127 /* CumulativeCallDaysTargetsRule days_of_week_mask */) > 0
2949 and (coalesce(cdt.scheduled, 0) + lcdt.scheduled) > (round(coalesce(cdt.raw_target /* raw_target | adjusted_target */, 0), 2));
2950
2951 (135.8ms) select
2952 coalesce(max(
2953 case
2954 when (ct.max_weekday_assignments > 0) then 1
2955 else 0
2956 end
2957 ), 0) as weekday_flag,
2958 coalesce(max(
2959 case
2960 when (ct.max_weekend_assignments > 0) then 1
2961 else 0
2962 end
2963 ), 0) as weekend_flag
2964 from
2965 buffer_auto_populate_slots_1744999692 as aps
2966 inner join call_types as ct
2967 on aps.call_type_id = ct.id
2968
2969 (150.7ms) /* MaxAssignmentsRule */
2970 delete ca
2971 from buffer_combined_availabilities_1744999692 as ca
2972 inner join buffer_schedules_1744999692 as sc
2973 on ca.schedule_id = sc.id
2974 inner join buffer_linkage_call_type_assignments_1744999692 as lcta
2975 on sc.linkage_instance_id = lcta.linkage_instance_id
2976 and sc.call_type_id = lcta.call_type_id
2977 and sc.is_weekend = lcta.is_weekend
2978 and sc.period_offset = lcta.period_offset
2979 and sc.calendar_schedule_id = lcta.calendar_schedule_id
2980 left join buffer_call_type_assignments_1744999692 as cta
2981 on ca.staff_id = cta.staff_id
2982 and sc.call_type_id = cta.call_type_id
2983 and sc.is_weekend = cta.is_weekend
2984 and sc.period_offset = cta.period_offset
2985 and sc.calendar_schedule_id = cta.calendar_schedule_id
2986 where
2987 sc.is_weekend in (NULL) /* "weekday (0), weekend (1)" checkboxes */
2988 and (coalesce(cta.assignments_count, 0) + lcta.assignments_count) > lcta.max_assignments;
2989
2990 (133.5ms) /* NegativeLinkRule */
2991 delete ca
2992 from buffer_combined_availabilities_1744999692 as ca use index (ix_buffer_combined_availabilities_composite_1)
2993 inner join buffer_negative_relations_1744999692 as nr use index (ix_buffer_negative_relations_composite_1)
2994 on ca.schedule_id = nr.schedule_id
2995 and ca.staff_id = nr.staff_id
2996
2997 (138.0ms) /* BrokenLinkageRule */
2998 truncate table buffer_combined_availability_linkages_1744999692
2999
3000 (135.3ms) insert into buffer_combined_availability_linkages_1744999692 (
3001 staff_id,
3002 linkage_instance_id,
3003 slots_count,
3004 is_primary,
3005 has_violations
3006 )
3007 select
3008 staff_id,
3009 linkage_instance_id,
3010 count(1) as slots_count,
3011 min(is_primary) as is_primary,
3012 max(has_violations) as has_violations
3013 from buffer_combined_availabilities_1744999692 use index (ix_buffer_combined_availabilities_composite_2)
3014 group by
3015 staff_id,
3016 linkage_instance_id
3017 order by null
3018
3019 (199.2ms) delete ca
3020 from
3021 buffer_combined_availabilities_1744999692 as ca use index (ix_buffer_combined_availabilities_composite_1)
3022 inner join buffer_schedules_1744999692 as sc
3023 on ca.schedule_id = sc.id
3024 inner join buffer_linkages_1744999692 as l
3025 on sc.linkage_instance_id = l.linkage_instance_id
3026 inner join buffer_combined_availability_linkages_1744999692 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3027 on ca.staff_id = ls_1.staff_id
3028 and sc.linkage_instance_id = ls_1.linkage_instance_id
3029 left join buffer_schedule_linkages_1744999692 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3030 on ca.staff_id = ls_2.staff_id
3031 and sc.linkage_instance_id = ls_2.linkage_instance_id
3032 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
3033
3034**Airbrake: closed