· 8 years ago · Aug 02, 2018, 10:46 AM
1(125.4ms) 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 (251.9ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
3 CalendarSchedule Load (126.4ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 6 AND `calendar_schedules`.`id` = 6 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
4 CallType Load (162.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (46, 47, 48, 49, 51)
5 CalendarSchedule Load (125.9ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 6 AND `calendar_schedules`.`id` = 6 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
6 Block Load (126.8ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND `blocks`.`id` = 17779 ORDER BY start_date asc, end_date asc LIMIT 1
7 BlockSchedule Load (125.1ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
8 PopulationTimeframe Load (125.1ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 6 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
9 RuleSet Load (124.7ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1468 LIMIT 1
10 Block Load (125.9ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date >= '2017-12-01' AND blocks.end_date <= '2017-12-31') ORDER BY start_date asc, end_date asc
11INFO : (08-02 10:32:20) phase: Scheduler::Base
12INFO : (08-02 10:32:20) from: 2017-12-01 to: 2017-12-31
13 Rule Load (126.2ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1468 ORDER BY applied desc, weight desc
14INFO : (08-02 10:32:21) rule configurations: Default
15 - 'Cumulative Assignment Targets' not applied
16 - 'Cumulative Combined Assignment Targets' not applied
17 - 'Cumulative Daily Assignment Targets' not applied
18 - 'Cumulative Day of Week Targets' not applied
19 - 'Cumulative Hour Targets' not applied
20 - 'Cumulative Point Targets' not applied
21 - 'Cumulative Weekend / Weekday Assignment Targets' not applied
22 - 'Cumulative Weekend / Weekday Combined Assignment Targets' not applied
23 - 'Cumulative Weekend / Weekday Hour Targets' not applied
24 - 'Cumulative Weekend / Weekday Point Targets' not applied
25 - 'Current Assignment Targets' not applied
26 - 'Current Combined Assignment Targets' not applied
27 - 'Current Daily Assignment Targets' not applied
28 - 'Current Day of Week Targets' not applied
29 - 'Current Hour Targets' not applied
30 - 'Current Point Targets' not applied
31 - 'Current Weekend / Weekday Assignment Targets' not applied
32 - 'Current Weekend / Weekday Combined Assignment Targets' not applied
33 - 'Current Weekend / Weekday Hour Targets' not applied
34 - 'Current Weekend / Weekday Point Targets' not applied
35 - 'Pending Call Requests' not applied
36 - 'Pending No-call Requests' not applied
37 - 'Assignments Per Day' not applied
38 - 'Days Between Assignments' not applied
39 - 'Days Between Same Assignment' not applied
40 - 'Unassigned Days' not applied
41 - 'Weeks Between Same Assignment On Same Day Of Week' not applied
42 - 'Weeks Between Same Day Of Week' not applied
43INFO : (08-02 10:32:21) started: Scheduler::Cache::Collector.collect_all
44INFO : (08-02 10:32:21) collect_staffs
45 Staff Load (128.0ms) 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` = 6 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
46 Client Load (249.0ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6
47 StaffSpecialization Load (125.5ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 18127, 78, 8510)
48 Specialization Load (127.0ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` = 7 ORDER BY `specializations`.`priority` ASC
49 StaffCallType Load (262.0ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 18127, 78, 8510)
50INFO : (08-02 10:32:24) initial eligibles: [66,77,68,74,72,3351,79,80,6913,81,65,18125,70,6960,71,73,20694,75,18127,78,8510]
51INFO : (08-02 10:32:24) collect_call_types
52 CallType Load (141.3ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 6 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
53INFO : (08-02 10:32:24) initial call types: [46,47,48,49,51,2716,397,1343,1344]
54INFO : (08-02 10:32:24) collect_chain_links
55 ChainLink Load (137.2ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 6
56 CallType Load (128.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (46, 48, 47)
57 CallType Load (126.6ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (48, 46, 47)
58INFO : (08-02 10:32:24) collect_linkages
59INFO : (08-02 10:32:24) collect_schedules
60 Schedule Load (128.7ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 6 AND (`schedules`.`schedule_on` BETWEEN '2017-12-01' AND '2017-12-31')
61INFO : (08-02 10:32:25) collect_slots
62INFO : (08-02 10:32:25) collect_approved_call_requests
63 Request Load (129.3ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2017-12-01' AND '2017-12-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
64INFO : (08-02 10:32:25) collect_approved_no_call_requests
65 Request Load (125.8ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2017-12-01' AND '2017-12-31') ORDER BY requests.schedule_on asc, requests.created_at asc
66INFO : (08-02 10:32:25) finished: Scheduler::Cache::Collector.collect_all
67INFO : (08-02 10:32:25) begin to process requests
68 CallType Load (143.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 6 AND `call_types`.`active` = 1
69 (142.2ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 6 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
70 Request Load (139.6ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 6 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (46, 47, 48, 49, 51, 397, 1343, 1344, 2716) AND (requests.schedule_on > '2017-09-30')
71 SQL (144.0ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 6 AND 1=0
72INFO : (08-02 10:32:26) end of requests processing
73 Block Load (125.4ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date <= '2017-12-31' AND blocks.end_date >= '2017-12-31') ORDER BY start_date asc, end_date asc LIMIT 1
74 Client Load (267.4ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
75 (126.8ms) SELECT `autopopulate_exclusions`.`schedule_on`, `autopopulate_exclusions`.`call_type_id` FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 6 AND `autopopulate_exclusions`.`schedule_on` IN ('2017-12-01', '2017-12-02', '2017-12-03', '2017-12-04', '2017-12-05', '2017-12-06', '2017-12-07', '2017-12-08', '2017-12-09', '2017-12-10', '2017-12-11', '2017-12-12', '2017-12-13', '2017-12-14', '2017-12-15', '2017-12-16', '2017-12-17', '2017-12-18', '2017-12-19', '2017-12-20', '2017-12-21', '2017-12-22', '2017-12-23', '2017-12-24', '2017-12-25', '2017-12-26', '2017-12-27', '2017-12-28', '2017-12-29', '2017-12-30', '2017-12-31')
76 (126.2ms) select
77 b.start_date,
78 b.end_date,
79 (pb.assignment_ids IS NULL) as fully_published,
80 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
81 pbct.days_of_week_mask
82 from blocks as b
83 inner join published_blocks as pb
84 on pb.block_id = b.id
85 inner join published_block_call_types as pbct
86 on pbct.published_block_id = pb.id
87 where b.client_id = 6
88 and pb.calendar_schedule_id = 6
89 and b.end_date BETWEEN '2017-12-01' AND '2017-12-31'
90
91INFO : (08-02 10:32:27) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
92INFO : (08-02 10:32:27) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
93INFO : (08-02 10:32:27) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
94INFO : (08-02 10:32:27) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
95INFO : (08-02 10:32:27) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
96INFO : (08-02 10:32:27) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
97INFO : (08-02 10:32:27) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
98INFO : (08-02 10:32:27) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
99INFO : (08-02 10:32:27) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
100INFO : (08-02 10:32:27) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query015
101INFO : (08-02 10:32:27) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
102INFO : (08-02 10:32:27) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
103INFO : (08-02 10:32:27) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
104INFO : (08-02 10:32:27) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
105INFO : (08-02 10:32:27) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
106INFO : (08-02 10:32:27) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
107INFO : (08-02 10:32:27) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
108INFO : (08-02 10:32:27) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
109INFO : (08-02 10:32:27) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
110INFO : (08-02 10:32:27) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
111INFO : (08-02 10:32:27) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0705
112INFO : (08-02 10:32:27) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0706
113INFO : (08-02 10:32:27) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0707
114INFO : (08-02 10:32:27) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0708
115INFO : (08-02 10:32:27) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0709
116INFO : (08-02 10:32:27) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query08
117INFO : (08-02 10:32:27) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query085
118INFO : (08-02 10:32:27) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query09
119INFO : (08-02 10:32:27) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query092
120INFO : (08-02 10:32:27) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query093
121INFO : (08-02 10:32:27) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query095
122INFO : (08-02 10:32:27) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query097
123INFO : (08-02 10:32:27) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query098
124INFO : (08-02 10:32:27) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query09_09_AL
125INFO : (08-02 10:32:27) <-- clear: 34 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
126INFO : (08-02 10:32:27) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
127INFO : (08-02 10:32:27) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
128INFO : (08-02 10:32:27) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
129INFO : (08-02 10:32:27) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
130INFO : (08-02 10:32:27) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
131INFO : (08-02 10:32:27) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
132INFO : (08-02 10:32:27) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Points
133INFO : (08-02 10:32:27) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
134INFO : (08-02 10:32:27) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query10
135INFO : (08-02 10:32:27) <-- clear: 44 - Scheduler::AutoPopulate::Queries::QueryScore02
136INFO : (08-02 10:32:27) <-- clear: 45 - Scheduler::AutoPopulate::Queries::QueryScore03
137INFO : (08-02 10:32:27) <-- clear: 46 - Scheduler::AutoPopulate::Queries::QueryScore04
138INFO : (08-02 10:32:27) <-- clear: 47 - Scheduler::AutoPopulate::Queries::Query11
139INFO : (08-02 10:32:27) <-- clear: 48 - Scheduler::AutoPopulate::Queries::DropTables
140 (127.5ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_126128084
141 (126.7ms) DROP TABLE IF EXISTS buffer_annual_point_targets_126128084
142 (126.7ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_126128084
143 (126.6ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_126128084
144 (127.4ms) DROP TABLE IF EXISTS buffer_call_days_targets_126128084
145 (133.0ms) DROP TABLE IF EXISTS buffer_call_type_targets_126128084
146 (126.9ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_126128084
147 (126.1ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_126128084
148 (126.5ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_126128084
149 (126.8ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_126128084
150 (127.2ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_126128084
151 (131.9ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_126128084
152 (132.9ms) DROP TABLE IF EXISTS buffer_current_cd_targets_126128084
153 (126.7ms) DROP TABLE IF EXISTS buffer_current_ct_targets_126128084
154 (126.4ms) DROP TABLE IF EXISTS buffer_current_dow_targets_126128084
155 (126.4ms) DROP TABLE IF EXISTS buffer_current_hour_targets_126128084
156 (126.8ms) DROP TABLE IF EXISTS buffer_current_oa_targets_126128084
157 (127.6ms) DROP TABLE IF EXISTS buffer_current_point_targets_126128084
158 (126.7ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_126128084
159 (137.3ms) DROP TABLE IF EXISTS buffer_hour_targets_126128084
160 (127.6ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_126128084
161 (126.8ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_126128084
162 (126.6ms) DROP TABLE IF EXISTS buffer_linkage_targets_126128084
163 (127.3ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_126128084
164 (130.7ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_126128084
165 (127.4ms) DROP TABLE IF EXISTS buffer_location_privileges_126128084
166 (131.1ms) DROP TABLE IF EXISTS buffer_periods_126128084
167 (127.6ms) DROP TABLE IF EXISTS buffer_point_targets_126128084
168 (126.6ms) DROP TABLE IF EXISTS buffer_schedules_126128084
169 (127.2ms) DROP TABLE IF EXISTS buffer_target_call_types_126128084
170 (127.8ms) DROP TABLE IF EXISTS buffer_target_daily_points_126128084
171 (126.4ms) DROP TABLE IF EXISTS buffer_target_dates_list_126128084
172 (126.6ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_126128084
173 (135.2ms) DROP TABLE IF EXISTS buffer_targets_126128084
174 (127.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_126128084
175 (126.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_126128084
176 (126.5ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_126128084
177 (126.3ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_126128084
178 (127.8ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_126128084
179 (126.6ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_126128084
180 (145.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_126128084
181 (126.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_126128084
182 (127.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_126128084
183 (126.2ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_126128084
184 (126.4ms) DROP TABLE IF EXISTS buffer_call_type_assignments_126128084
185 (130.2ms) DROP TABLE IF EXISTS buffer_combined_availabilities_126128084
186 (126.2ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_126128084
187 (126.7ms) DROP TABLE IF EXISTS buffer_counter_increments_126128084
188 (126.3ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_126128084
189 (126.4ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_126128084
190 (126.7ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_126128084
191 (126.6ms) DROP TABLE IF EXISTS buffer_linkage_patterns_126128084
192 (127.7ms) DROP TABLE IF EXISTS buffer_linkages_126128084
193 (126.5ms) DROP TABLE IF EXISTS buffer_location_privileges_126128084
194 (138.1ms) DROP TABLE IF EXISTS buffer_min_distances_126128084
195 (127.4ms) DROP TABLE IF EXISTS buffer_negative_relations_126128084
196 (126.4ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_126128084
197 (126.4ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_126128084
198 (127.2ms) DROP TABLE IF EXISTS buffer_rule_scores_126128084
199 (132.4ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_126128084
200 (126.8ms) DROP TABLE IF EXISTS buffer_schedule_linkages_126128084
201 (130.8ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_126128084
202 (126.3ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_126128084
203 (126.4ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_126128084
204 (129.0ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_126128084
205 (127.1ms) DROP TABLE IF EXISTS buffer_staff_specializations_126128084
206 (126.3ms) DROP TABLE IF EXISTS buffer_static_availabilities_126128084
207 (126.0ms) DROP TABLE IF EXISTS buffer_target_assignments_126128084
208 (137.1ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_126128084
209 (128.4ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_126128084
210 (126.4ms) DROP TABLE IF EXISTS buffer_assignment_limits_126128084
211 (124.8ms) delete from buffer_target_dates
212 where session_id = 126128084
213
214INFO : (08-02 10:32:36) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
215 (129.6ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_126128084 LIKE buffer_assignment_limit_call_types
216 (130.0ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_126128084 LIKE buffer_assignment_limit_periods
217 (129.4ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_126128084 LIKE buffer_assignment_limit_staff_types
218 (131.1ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_126128084 LIKE buffer_assignment_limit_staffs
219 (129.0ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_126128084 LIKE buffer_auto_populate_slots
220 (129.8ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_126128084 LIKE buffer_call_type_assignments
221 (129.0ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_126128084 LIKE buffer_combined_availabilities
222 (130.8ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_126128084 LIKE buffer_combined_availability_linkages
223 (129.1ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_126128084 LIKE buffer_counter_increments
224 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_126128084 LIKE buffer_linkage_assigned_days
225 (134.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_126128084 LIKE buffer_linkage_call_type_assignments
226 (130.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_126128084 LIKE buffer_linkage_dow_targets
227 (129.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_126128084 LIKE buffer_linkage_patterns
228 (129.0ms) CREATE TABLE IF NOT EXISTS buffer_linkages_126128084 LIKE buffer_linkages
229 (129.1ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_126128084 LIKE buffer_location_privileges
230 (129.0ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_126128084 LIKE buffer_min_distances
231 (131.5ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_126128084 LIKE buffer_negative_relations
232 (136.1ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_126128084 LIKE buffer_rule_calc_filters
233 (130.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_126128084 LIKE buffer_rule_calc_schedules
234 (140.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_126128084 LIKE buffer_rule_scores
235 (130.1ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_126128084 LIKE buffer_rule_scores_with_corrections
236 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_126128084 LIKE buffer_schedule_linkages
237 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_126128084 LIKE buffer_schedules_linkage_patterns
238 (129.5ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_126128084 LIKE buffer_specialization_daily_counts
239 (129.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_126128084 LIKE buffer_staff_assigned_days
240 (130.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_126128084 LIKE buffer_staff_daily_counts
241 (144.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_126128084 LIKE buffer_staff_specializations
242 (129.3ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_126128084 LIKE buffer_static_availabilities
243 (175.0ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_126128084 LIKE buffer_target_assignments
244 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_126128084 LIKE buffer_linkage_assignment_limits
245 (139.4ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_126128084 LIKE buffer_staff_assignment_limits
246 (129.3ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_126128084 LIKE buffer_assignment_limits
247 (129.4ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_126128084 LIKE buffer_annual_hour_targets
248 (129.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_126128084 LIKE buffer_annual_point_targets
249 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_126128084 LIKE buffer_annual_targets_staff_totals
250 (130.3ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_126128084 LIKE buffer_auto_populate_slots
251 (131.0ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_126128084 LIKE buffer_call_days_targets
252 (141.2ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_126128084 LIKE buffer_call_type_targets
253 (462.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_126128084 LIKE buffer_cumulative_cd_targets
254 (135.3ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_126128084 LIKE buffer_cumulative_ct_targets
255 (129.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_126128084 LIKE buffer_cumulative_dow_targets
256 (138.3ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_126128084 LIKE buffer_cumulative_hour_targets
257 (135.3ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_126128084 LIKE buffer_cumulative_oa_targets
258 (134.7ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_126128084 LIKE buffer_cumulative_point_targets
259 (130.0ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_126128084 LIKE buffer_current_cd_targets
260 (129.5ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_126128084 LIKE buffer_current_ct_targets
261 (129.3ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_126128084 LIKE buffer_current_dow_targets
262 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_126128084 LIKE buffer_current_hour_targets
263 (143.4ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_126128084 LIKE buffer_current_oa_targets
264 (129.3ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_126128084 LIKE buffer_current_point_targets
265 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_126128084 LIKE buffer_day_of_week_targets
266 (129.8ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_126128084 LIKE buffer_hour_targets
267 (129.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_126128084 LIKE buffer_linkage_call_days_targets
268 (129.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_126128084 LIKE buffer_linkage_call_type_targets
269 (128.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_126128084 LIKE buffer_linkage_targets
270 (128.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_126128084 LIKE buffer_linkage_hour_targets
271 (129.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_126128084 LIKE buffer_linkage_point_targets
272 (128.7ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_126128084 LIKE buffer_location_privileges
273 (130.1ms) CREATE TABLE IF NOT EXISTS buffer_periods_126128084 LIKE buffer_periods
274 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_126128084 LIKE buffer_point_targets
275 (130.8ms) CREATE TABLE IF NOT EXISTS buffer_schedules_126128084 LIKE buffer_schedules
276 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_126128084 LIKE buffer_target_call_types
277 (136.9ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_126128084 LIKE buffer_target_daily_points
278 (129.1ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_126128084 LIKE buffer_target_dates_list
279 (128.6ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_126128084 LIKE buffer_target_primary_availabilities
280 (128.9ms) CREATE TABLE IF NOT EXISTS buffer_targets_126128084 LIKE buffer_targets
281 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_126128084 LIKE buffer_tmp_targets_availabilities
282 (129.6ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_126128084 LIKE buffer_tmp_targets_availabilities_exclusions
283 (129.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_126128084 LIKE buffer_tmp_targets_client_totals
284 (129.1ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_126128084 LIKE buffer_tmp_targets_scheduled
285 (130.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_126128084 LIKE buffer_tmp_targets_staff_totals
286INFO : (08-02 10:32:46) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
287 (128.2ms) select
288 b.start_date,
289 b.end_date,
290 (pb.assignment_ids IS NULL) as fully_published,
291 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
292 pbct.days_of_week_mask
293 from blocks as b
294 inner join published_blocks as pb
295 on pb.block_id = b.id
296 inner join published_block_call_types as pbct
297 on pbct.published_block_id = pb.id
298 where b.client_id = 6
299 and pb.calendar_schedule_id = 6
300 and b.end_date BETWEEN '2017-12-01' AND '2017-12-31'
301
302 AutopopulateExclusion Load (134.7ms) SELECT `autopopulate_exclusions`.* FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 6 AND `autopopulate_exclusions`.`schedule_on` IN ('2017-12-01', '2017-12-02', '2017-12-03', '2017-12-04', '2017-12-05', '2017-12-06', '2017-12-07', '2017-12-08', '2017-12-09', '2017-12-10', '2017-12-11', '2017-12-12', '2017-12-13', '2017-12-14', '2017-12-15', '2017-12-16', '2017-12-17', '2017-12-18', '2017-12-19', '2017-12-20', '2017-12-21', '2017-12-22', '2017-12-23', '2017-12-24', '2017-12-25', '2017-12-26', '2017-12-27', '2017-12-28', '2017-12-29', '2017-12-30', '2017-12-31')
303 (159.8ms) INSERT INTO buffer_auto_populate_slots_126128084
304 (schedule_on, day_of_week_mask, call_type_id, display_order, should_be_populated)
305 VALUES ('2017-12-01', 32, 46, 0, 1), ('2017-12-01', 32, 47, 2, 1), ('2017-12-01', 32, 48, 3, 1), ('2017-12-01', 32, 49, 4, 1), ('2017-12-01', 32, 51, 7, 1), ('2017-12-02', 64, 46, 0, 1), ('2017-12-02', 64, 48, 3, 1), ('2017-12-03', 1, 46, 0, 1), ('2017-12-03', 1, 48, 3, 1), ('2017-12-04', 2, 46, 0, 1), ('2017-12-04', 2, 47, 2, 1), ('2017-12-04', 2, 48, 3, 1), ('2017-12-04', 2, 49, 4, 1), ('2017-12-04', 2, 51, 7, 1), ('2017-12-05', 4, 46, 0, 1), ('2017-12-05', 4, 47, 2, 1), ('2017-12-05', 4, 48, 3, 1), ('2017-12-05', 4, 49, 4, 1), ('2017-12-05', 4, 51, 7, 1), ('2017-12-06', 8, 46, 0, 1), ('2017-12-06', 8, 47, 2, 1), ('2017-12-06', 8, 48, 3, 1), ('2017-12-06', 8, 49, 4, 1), ('2017-12-06', 8, 51, 7, 1), ('2017-12-07', 16, 46, 0, 1), ('2017-12-07', 16, 47, 2, 1), ('2017-12-07', 16, 48, 3, 1), ('2017-12-07', 16, 49, 4, 1), ('2017-12-07', 16, 51, 7, 1), ('2017-12-08', 32, 46, 0, 1), ('2017-12-08', 32, 47, 2, 1), ('2017-12-08', 32, 48, 3, 1), ('2017-12-08', 32, 49, 4, 1), ('2017-12-08', 32, 51, 7, 1), ('2017-12-09', 64, 46, 0, 1), ('2017-12-09', 64, 48, 3, 1), ('2017-12-10', 1, 46, 0, 1), ('2017-12-10', 1, 48, 3, 1), ('2017-12-11', 2, 46, 0, 1), ('2017-12-11', 2, 47, 2, 1), ('2017-12-11', 2, 48, 3, 1), ('2017-12-11', 2, 49, 4, 1), ('2017-12-11', 2, 51, 7, 1), ('2017-12-12', 4, 46, 0, 1), ('2017-12-12', 4, 47, 2, 1), ('2017-12-12', 4, 48, 3, 1), ('2017-12-12', 4, 49, 4, 1), ('2017-12-12', 4, 51, 7, 1), ('2017-12-13', 8, 46, 0, 1), ('2017-12-13', 8, 47, 2, 1), ('2017-12-13', 8, 48, 3, 1), ('2017-12-13', 8, 49, 4, 1), ('2017-12-13', 8, 51, 7, 1), ('2017-12-14', 16, 46, 0, 1), ('2017-12-14', 16, 47, 2, 1), ('2017-12-14', 16, 48, 3, 1), ('2017-12-14', 16, 49, 4, 1), ('2017-12-14', 16, 51, 7, 1), ('2017-12-15', 32, 46, 0, 1), ('2017-12-15', 32, 47, 2, 1), ('2017-12-15', 32, 48, 3, 1), ('2017-12-15', 32, 49, 4, 1), ('2017-12-15', 32, 51, 7, 1), ('2017-12-16', 64, 46, 0, 1), ('2017-12-16', 64, 48, 3, 1), ('2017-12-17', 1, 46, 0, 1), ('2017-12-17', 1, 48, 3, 1), ('2017-12-18', 2, 46, 0, 1), ('2017-12-18', 2, 47, 2, 1), ('2017-12-18', 2, 48, 3, 1), ('2017-12-18', 2, 49, 4, 1), ('2017-12-18', 2, 51, 7, 1), ('2017-12-19', 4, 46, 0, 1), ('2017-12-19', 4, 47, 2, 1), ('2017-12-19', 4, 48, 3, 1), ('2017-12-19', 4, 49, 4, 1), ('2017-12-19', 4, 51, 7, 1), ('2017-12-20', 8, 46, 0, 1), ('2017-12-20', 8, 47, 2, 1), ('2017-12-20', 8, 48, 3, 1), ('2017-12-20', 8, 49, 4, 1), ('2017-12-20', 8, 51, 7, 1), ('2017-12-21', 16, 46, 0, 1), ('2017-12-21', 16, 47, 2, 1), ('2017-12-21', 16, 48, 3, 1), ('2017-12-21', 16, 49, 4, 1), ('2017-12-21', 16, 51, 7, 1), ('2017-12-22', 32, 46, 0, 1), ('2017-12-22', 32, 47, 2, 1), ('2017-12-22', 32, 48, 3, 1), ('2017-12-22', 32, 49, 4, 1), ('2017-12-22', 32, 51, 7, 1), ('2017-12-23', 64, 46, 0, 1), ('2017-12-23', 64, 48, 3, 1), ('2017-12-24', 1, 46, 0, 1), ('2017-12-24', 1, 48, 3, 1), ('2017-12-25', 2, 46, 0, 1), ('2017-12-25', 2, 47, 2, 1), ('2017-12-25', 2, 48, 3, 1), ('2017-12-25', 2, 49, 4, 1), ('2017-12-25', 2, 51, 7, 1), ('2017-12-26', 4, 46, 0, 1), ('2017-12-26', 4, 47, 2, 1), ('2017-12-26', 4, 48, 3, 1), ('2017-12-26', 4, 49, 4, 1), ('2017-12-26', 4, 51, 7, 1), ('2017-12-27', 8, 46, 0, 1), ('2017-12-27', 8, 47, 2, 1), ('2017-12-27', 8, 48, 3, 1), ('2017-12-27', 8, 49, 4, 1), ('2017-12-27', 8, 51, 7, 1), ('2017-12-28', 16, 46, 0, 1), ('2017-12-28', 16, 47, 2, 1), ('2017-12-28', 16, 48, 3, 1), ('2017-12-28', 16, 49, 4, 1), ('2017-12-28', 16, 51, 7, 1), ('2017-12-29', 32, 46, 0, 1), ('2017-12-29', 32, 47, 2, 1), ('2017-12-29', 32, 48, 3, 1), ('2017-12-29', 32, 49, 4, 1), ('2017-12-29', 32, 51, 7, 1), ('2017-12-30', 64, 46, 0, 1), ('2017-12-30', 64, 48, 3, 1), ('2017-12-31', 1, 46, 0, 1), ('2017-12-31', 1, 48, 3, 1)
306
307INFO : (08-02 10:32:46) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
308 (127.3ms) insert into buffer_auto_populate_slots_126128084/* client_id */ (
309 schedule_on,
310 day_of_week_mask,
311 call_type_id,
312 display_order,
313 should_be_populated
314 )
315 select distinct
316 date_add(
317 auto_populate_slots.schedule_on,
318 interval soft_linkages.offset day
319 ) as schedule_on,
320 (1 << (
321 dayofweek(
322 date_add(
323 auto_populate_slots.schedule_on,
324 interval soft_linkages.offset day
325 )
326 ) - 1
327 )
328 ) as day_of_week_mask,
329 soft_linkages.linked_call_type_id as call_type_id,
330 call_types.display_order,
331 0 as should_be_populated
332 from
333 buffer_auto_populate_slots_126128084/* client_id */ as auto_populate_slots
334 inner join soft_linkages
335 on auto_populate_slots.call_type_id = soft_linkages.call_type_id
336 and auto_populate_slots.day_of_week_mask = soft_linkages.day_of_week_mask
337 left join buffer_auto_populate_slots_126128084/* client_id */ as auto_populate_slots_2
338 on date_add(
339 auto_populate_slots.schedule_on,
340 interval soft_linkages.offset day
341 ) = auto_populate_slots_2.schedule_on
342 and soft_linkages.linked_call_type_id = auto_populate_slots_2.call_type_id
343 inner join call_types
344 on soft_linkages.linked_call_type_id = call_types.id
345 where auto_populate_slots_2.id is null
346 order by null
347
348INFO : (08-02 10:32:47) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
349 (133.0ms) insert into buffer_auto_populate_slots_126128084 (
350 schedule_on,
351 day_of_week_mask,
352 call_type_id,
353 display_order,
354 should_be_populated
355 )
356 select distinct
357 date_add(
358 auto_populate_slots.schedule_on,
359 interval linkage_relations.distance_in_days day
360 ) as schedule_on,
361 (1 << (
362 dayofweek(
363 date_add(
364 auto_populate_slots.schedule_on,
365 interval linkage_relations.distance_in_days day
366 )
367 ) - 1
368 )
369 ) as day_of_week_mask,
370 linkage_relations.linked_call_type_id as call_type_id,
371 call_types.display_order,
372 0 as should_be_populated
373 from
374 buffer_auto_populate_slots_126128084 as auto_populate_slots
375 inner join linkage_relations
376 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
377 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
378 left join buffer_auto_populate_slots_126128084 as auto_populate_slots_2
379 on date_add(
380 auto_populate_slots.schedule_on,
381 interval linkage_relations.distance_in_days day
382 ) = auto_populate_slots_2.schedule_on
383 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
384 inner join call_types
385 on linkage_relations.linked_call_type_id = call_types.id
386 where auto_populate_slots_2.id is null
387 order by null
388
389INFO : (08-02 10:32:47) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
390 (129.5ms) insert into linkage_relations (
391 client_id,
392 call_type_id,
393 day_of_week_mask,
394 linked_call_type_id,
395 distance_in_days,
396 linkage_template_id
397 )
398 select distinct
399 6 /* client_id */ as client_id,
400 aps.call_type_id,
401 aps.day_of_week_mask,
402 aps.call_type_id as linked_call_type_id,
403 0 as distance_in_days,
404 coalesce(
405 lr.linkage_template_id,
406 @linkage_template_max := @linkage_template_max + 1
407 ) as linkage_template_id
408 from
409 buffer_auto_populate_slots_126128084/* client_id */ as aps
410 inner join (
411 select @linkage_template_max := count(distinct linkage_template_id) - 1
412 from linkage_relations
413 where client_id = 6 /* client_id */
414 order by null
415 ) as lt
416 on 1 = 1
417 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
418 on aps.call_type_id = lr.call_type_id
419 and aps.day_of_week_mask = lr.day_of_week_mask
420 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
421 on aps.call_type_id = lr_2.call_type_id
422 and aps.day_of_week_mask = lr_2.day_of_week_mask
423 and aps.call_type_id = lr_2.linked_call_type_id
424 and lr_2.distance_in_days = 0
425 where lr_2.id is null
426 order by null
427
428 (139.5ms) insert into buffer_schedules_126128084 (
429 call_type_id,
430 staff_id,
431 schedule_on,
432 day_of_week_mask,
433 is_weekend,
434 display_order,
435 week_number,
436 should_be_evaluated,
437 should_be_populated,
438 linkage_instance_id
439 )
440 select
441 call_type_id,
442 staff_id,
443 schedule_on,
444 day_of_week_mask,
445 is_weekend,
446 display_order,
447 week_number,
448 max(should_be_evaluated) as should_be_evaluated,
449 max(should_be_populated) as should_be_populated,
450 min(linkage_instance_id) as linkage_instance_id
451 from (
452 select
453 auto_populate_slots_2.call_type_id,
454 schedules.staff_id,
455 auto_populate_slots_2.schedule_on,
456 auto_populate_slots_2.day_of_week_mask,
457 case
458 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
459 else 1
460 end as is_weekend,
461 auto_populate_slots_2.display_order,
462 yearweek(
463 auto_populate_slots_2.schedule_on,
464 case
465 when (clients.start_of_week = 0) then 2
466 else 7
467 end
468 ) as week_number,
469 case
470 when (schedules.staff_id is null) then 1
471 else 0
472 end as should_be_evaluated,
473 (
474 case
475 when (
476 auto_populate_slots_2.should_be_populated = 1
477 and schedules.staff_id is null
478 ) then 1
479 else 0
480 end
481 ) as should_be_populated,
482 (
483 linkage_relations.linkage_template_id * 1000000 +
484 yearweek(
485 auto_populate_slots.schedule_on,
486 case
487 when (clients.start_of_week = 0) then 2
488 else 7
489 end
490 )
491 ) as linkage_instance_id
492 from
493 buffer_auto_populate_slots_126128084 as auto_populate_slots
494 inner join clients
495 on clients.id = 6 /* client_id */
496 inner join linkage_relations
497 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
498 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
499 inner join buffer_auto_populate_slots_126128084 as auto_populate_slots_2
500 on date_add(
501 auto_populate_slots.schedule_on,
502 interval linkage_relations.distance_in_days day
503 ) = auto_populate_slots_2.schedule_on
504 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
505 left join schedules
506 on auto_populate_slots_2.call_type_id = schedules.call_type_id
507 and auto_populate_slots_2.schedule_on = schedules.schedule_on
508 order by null
509 ) as t1
510 group by
511 call_type_id,
512 staff_id,
513 schedule_on,
514 day_of_week_mask,
515 display_order,
516 week_number
517 order by null
518
519INFO : (08-02 10:32:47) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
520 (147.4ms) update
521 buffer_schedules_126128084 as sc
522 inner join call_types as ct
523 on sc.call_type_id = ct.id
524 inner join calendar_schedules as cs
525 on ct.calendar_schedule_id = cs.id
526 inner join blocks as b
527 on cs.block_schedule_id = b.block_schedule_id
528 and sc.schedule_on between b.start_date and b.end_date
529 set
530 sc.calendar_schedule_id = cs.id,
531 sc.block_id = b.id
532
533 BlockSchedule Load (126.0ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
534 (130.6ms) insert into buffer_periods_126128084 (
535 calendar_schedule_id,
536 block_schedule_id,
537 period_offset,
538 start_block_display_order,
539 end_block_display_order,
540 start_date,
541 end_date,
542 should_be_populated,
543 should_be_evaluated
544 )
545 select
546 t1.calendar_schedule_id,
547 t1.block_schedule_id,
548 t1.period_offset,
549 t2.start_block_display_order,
550 t2.end_block_display_order,
551 t2.start_date,
552 t2.end_date,
553 t1.should_be_populated,
554 t1.should_be_evaluated
555 from (
556 select
557 sc.calendar_schedule_id,
558 b.block_schedule_id,
559 case
560 when ((month(b.start_date) - pt.begin_period) >= 0)
561 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
562 else
563 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
564 end as period_offset,
565 max(sc.should_be_populated) as should_be_populated,
566 max(sc.should_be_evaluated) as should_be_evaluated
567 from (
568 select
569 sc.block_id,
570 sc.calendar_schedule_id,
571 max(ps.should_be_populated) as should_be_populated,
572 1 as should_be_evaluated
573 from
574 buffer_schedules_126128084 as sc
575 inner join buffer_auto_populate_slots_126128084 as ps
576 on sc.schedule_on = ps.schedule_on
577 and sc.call_type_id = ps.call_type_id
578 group by
579 sc.block_id,
580 sc.calendar_schedule_id
581 order by null
582 ) as sc
583 inner join blocks as b
584 on sc.block_id = b.id
585 inner join population_timeframes as pt
586 on pt.client_id = 6
587 and pt.timeframe_type = 1
588 group by
589 sc.calendar_schedule_id,
590 b.block_schedule_id,
591 period_offset
592 order by null
593 ) as t1
594 inner join (
595 select
596 b.block_schedule_id,
597 case
598 when ((month(b.start_date) - pt.begin_period) >= 0)
599 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
600 else
601 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
602 end as period_offset,
603 min(b.display_order) as start_block_display_order,
604 max(b.display_order) as end_block_display_order,
605 min(b.start_date) as start_date,
606 max(b.end_date) as end_date
607 from
608 blocks as b
609 inner join population_timeframes as pt
610 on pt.client_id = 6
611 and pt.timeframe_type = 1
612 where
613 b.client_id = 6
614 group by
615 b.block_schedule_id,
616 period_offset
617 order by null
618 ) as t2
619 on t1.block_schedule_id = t2.block_schedule_id
620 and t1.period_offset = t2.period_offset
621 order by null
622
623 (127.4ms) update
624 buffer_schedules_126128084 as sc
625 inner join buffer_periods_126128084 as p
626 on sc.calendar_schedule_id = p.calendar_schedule_id
627 and sc.schedule_on between p.start_date and p.end_date
628 set sc.period_offset = p.period_offset
629
630INFO : (08-02 10:32:48) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
631INFO : (08-02 10:32:48) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
632 (176.6ms) insert into buffer_counter_increments_126128084 (
633 schedule_id,
634 calls,
635 points,
636 hours,
637 affects_cumulative_targets
638 )
639 select
640 sc.id as schedule_id,
641 case
642 when (ct.included_in_targets > 0) then 1
643 else 0
644 end as calls,
645 coalesce(
646 case
647 when (hl.observed_date is null or dp.point is not null) then dp.point
648 else dp_2.point
649 end,
650 0.0
651 ) as points,
652 ct.hours,
653 case
654 when (b.display_order between (t1.timeframe_ending - (2 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
655 else 0
656 end as affects_cumulative_targets
657 from
658 buffer_schedules_126128084 as sc
659 inner join (
660 select
661 id,
662 included_in_targets,
663 coalesce(
664 timestampdiff(
665 minute,
666 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
667 concat(
668 '1900-01-0',
669 case
670 when ((
671 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
672 )
673 and (
674 timediff(
675 maketime(end_time_hour, end_time_minute, 0),
676 maketime(start_time_hour, start_time_minute, 0)
677 ) <= 0
678 )
679 ) then '2'
680 else '1'
681 end,
682 ' ',
683 maketime(end_time_hour, end_time_minute, 0)
684 )
685 ) / 60,
686 0.0
687 ) as hours
688 from call_types
689 where
690 client_id = 6
691 and active = 1
692 order by null
693 ) as ct
694 on sc.call_type_id = ct.id
695 inner join blocks as b
696 on sc.block_id = b.id
697 inner join (
698 select
699 calendar_schedule_id,
700 max(
701 case
702 when (should_be_populated = 1) then end_block_display_order
703 else null
704 end
705 ) as timeframe_ending
706 from buffer_periods_126128084
707 group by calendar_schedule_id
708 order by null
709 ) as t1
710 on sc.calendar_schedule_id = t1.calendar_schedule_id
711 left join (
712 select
713 hd.observed_date,
714 case
715 when (hd.observed_date > hd.extended_date) then hd.extended_date
716 else hd.observed_date
717 end as date_from,
718 case
719 when (hd.observed_date < hd.extended_date) then hd.extended_date
720 else hd.observed_date
721 end as date_to
722 from
723 holidays as h
724 inner join holiday_dates as hd
725 on h.id = hd.holiday_id
726 inner join (
727 select
728 year(min(schedule_on)) as year_from,
729 year(max(schedule_on)) as year_to
730 from buffer_schedules_126128084
731 where should_be_evaluated = 1
732 ) as y
733 on hd.year between y.year_from and y.year_to
734 where
735 h.client_id = 6
736 and (
737 h.observed = 1
738 or h.extended = 1
739 )
740 order by null
741 ) as hl
742 on sc.schedule_on between hl.date_from and hl.date_to
743 left join assignment_annotations as aa
744 on ct.id = aa.assignmentable_id
745 and aa.assignmentable_type = 'CallType'
746 and aa.annotation_id = 0 /* annotation = "None" */
747 and aa.applicable = 1
748 left join assignment_annotation_daily_points as dp
749 on aa.id = dp.assignment_annotation_id
750 and case
751 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
752 when (sc.schedule_on = hl.observed_date) then 7
753 else 8
754 end = dp.day
755 left join assignment_annotation_daily_points as dp_2
756 on aa.id = dp_2.assignment_annotation_id
757 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
758 where sc.should_be_evaluated = 1
759 order by null
760
761INFO : (08-02 10:32:48) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
762 (251.5ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
763 insert into buffer_linkage_patterns_126128084 (
764 period_offset,
765 calendar_schedule_id,
766 block_id,
767 block_start_date,
768 block_start_dow,
769 block_length,
770 linkage_pattern_id,
771 pattern_start_dow,
772 days_before_start,
773 pattern_length,
774 start_offset,
775 iterations_count
776 )
777 select
778 b1.period_offset,
779 b1.calendar_schedule_id,
780 b1.block_id,
781 b2.start_date as block_start_date,
782 dayofweek(b2.start_date) as block_start_dow,
783 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
784 lp.id as linkage_pattern_id,
785 (lp.start_on + 1) as pattern_start_dow,
786 lp.days_before_start,
787 (lp.length * 7) as pattern_length,
788 null as start_offset,
789 null as iterations_count
790 from (
791 select
792 calendar_schedule_id,
793 block_id,
794 period_offset
795 from buffer_schedules_126128084
796 group by
797 calendar_schedule_id,
798 block_id,
799 period_offset
800 order by null
801 ) as b1
802 inner join blocks as b2
803 on b1.block_id = b2.id
804 inner join linkage_patterns as lp
805 on lp.client_id = 6
806 and b1.calendar_schedule_id = lp.calendar_schedule_id
807 order by null
808
809 (126.9ms) -- Calculate the basic offset ("+" or "-") that will be applied to a linkage pattern if a block and a pattern are starting on different day of week
810 update buffer_linkage_patterns_126128084
811 set start_offset = if(
812 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
813 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
814 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
815 )
816
817 (147.4ms) -- In some cases (when a block and a pattern are starting on different day of week or when a pattern is shorter than a block)
818 -- a pattern should be repeated couple of times (in order to cover the entire block)
819 update buffer_linkage_patterns_126128084
820 set iterations_count =
821 ceiling(
822 (
823 block_length +
824 case
825 when (start_offset < 0) then abs(start_offset)
826 else 0
827 end
828 ) * 1.0 / pattern_length
829 )
830
831 (126.9ms) -- Generate an assignments sequence for every linkage pattern
832 insert into buffer_schedules_linkage_patterns_126128084 (
833 call_type_id,
834 staff_id,
835 schedule_on,
836 period_offset,
837 day_of_week_mask,
838 display_order,
839 week_number,
840 should_be_evaluated,
841 should_be_populated,
842 linkage_instance_id,
843 block_id,
844 calendar_schedule_id
845 )
846 select
847 lps.assignment_id as call_type_id,
848 null as staff_id,
849 case
850 when (lp.start_offset < 0)
851 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
852 else
853 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
854 end as schedule_on,
855 lp.period_offset,
856 null as day_of_week_mask,
857 null as display_order,
858 null as week_number,
859 0 as should_be_evaluated,
860 0 as should_be_populated,
861 lp.linkage_pattern_id as linkage_instance_id,
862 lp.block_id,
863 lp.calendar_schedule_id
864 from
865 numeric_sequence as ns
866 inner join buffer_linkage_patterns_126128084 as lp
867 on ns.item_number <= (lp.iterations_count - 1)
868 inner join linkage_pattern_slots as lps
869 on lp.linkage_pattern_id = lps.linkage_pattern_id
870 and lps.assignment_type = 'CallType'
871 where
872 case
873 when (lp.start_offset < 0)
874 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
875 else
876 (ns.item_number * lp.pattern_length + lps.offset)
877 end between 0 and (lp.block_length - 1)
878 order by null
879
880 (126.0ms) -- Fill the columns that we left blank in the previous "INSERT"
881 update
882 buffer_schedules_linkage_patterns_126128084 as slp
883 inner join clients as cl
884 on cl.id = 6
885 inner join call_types as ct
886 on slp.call_type_id = ct.id
887 left join buffer_schedules_126128084 as bs
888 on slp.schedule_on = bs.schedule_on
889 and slp.call_type_id = bs.call_type_id
890 set
891 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
892 slp.display_order = ct.display_order,
893 slp.week_number =
894 yearweek(
895 slp.schedule_on,
896 case
897 when (cl.start_of_week = 0) then 2
898 else 7
899 end
900 ),
901 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
902 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
903
904 (124.9ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
905 delete slp_1
906 from
907 buffer_schedules_linkage_patterns_126128084 as slp_1
908 inner join (
909 select linkage_instance_id
910 from buffer_schedules_linkage_patterns_126128084
911 group by linkage_instance_id
912 having
913 max(should_be_evaluated) = 0
914 and max(should_be_populated) = 0
915 order by null
916 ) as slp_2
917 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
918
919 (124.9ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
920 update
921 buffer_schedules_linkage_patterns_126128084 as slp
922 inner join buffer_schedules_126128084 as bs_1
923 on slp.schedule_on = bs_1.schedule_on
924 and slp.call_type_id = bs_1.call_type_id
925 inner join buffer_schedules_126128084 as bs_2
926 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
927 set bs_2.linkage_instance_id = slp.linkage_instance_id
928
929 (131.2ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
930 -- should be copied over to the buffer schedules table (used during the auto-populate process)
931 insert into buffer_schedules_126128084 (
932 call_type_id,
933 staff_id,
934 schedule_on,
935 period_offset,
936 day_of_week_mask,
937 is_weekend,
938 display_order,
939 week_number,
940 should_be_evaluated,
941 should_be_populated,
942 linkage_instance_id,
943 block_id,
944 calendar_schedule_id
945 )
946 select
947 call_type_id,
948 staff_id,
949 schedule_on,
950 period_offset,
951 day_of_week_mask,
952 case
953 when (day_of_week_mask & 62 > 0) then 0
954 else 1
955 end as is_weekend,
956 display_order,
957 week_number,
958 should_be_evaluated,
959 should_be_populated,
960 linkage_instance_id,
961 block_id,
962 calendar_schedule_id
963 from buffer_schedules_linkage_patterns_126128084
964 where
965 should_be_evaluated = 0
966 and should_be_populated = 0
967
968INFO : (08-02 10:32:49) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query015
969 (133.9ms) insert into buffer_schedules_126128084 (
970 call_type_id,
971 staff_id,
972 schedule_on,
973 day_of_week_mask,
974 is_weekend,
975 display_order,
976 should_be_evaluated,
977 should_be_populated,
978 linkage_instance_id
979 )
980 select distinct
981 schedules_call_overlap.call_type_id,
982 schedules_call_overlap.staff_id,
983 schedules_call_overlap.schedule_on,
984 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
985 case
986 when (dayofweek(schedules_call_overlap.schedule_on) between 2 and 6) then 0
987 else 1
988 end as is_weekend,
989 call_types.display_order,
990 0 as should_be_evaluated,
991 0 as should_be_populated,
992 -1 as linkage_instance_id
993 from (
994 select schedule_on
995 from buffer_schedules_126128084
996 where should_be_evaluated = 1
997 group by schedule_on
998 order by null
999 ) as buffer_schedules_1
1000 inner join schedules as schedules_call_overlap
1001 on schedules_call_overlap.client_id = 6
1002 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1003 left join buffer_schedules_126128084 as buffer_schedules_2
1004 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1005 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1006 inner join call_types
1007 on schedules_call_overlap.call_type_id = call_types.id
1008 where buffer_schedules_2.id is null
1009 order by null
1010
1011INFO : (08-02 10:32:49) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1012INFO : (08-02 10:32:49) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1013INFO : (08-02 10:32:49) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1014INFO : (08-02 10:32:49) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1015INFO : (08-02 10:32:49) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1016 (161.0ms) insert into buffer_schedules_126128084 (
1017 call_type_id,
1018 staff_id,
1019 schedule_on,
1020 day_of_week_mask,
1021 is_weekend,
1022 display_order,
1023 should_be_evaluated,
1024 should_be_populated,
1025 linkage_instance_id
1026 )
1027 select distinct straight_join
1028 schedules.call_type_id,
1029 schedules.staff_id,
1030 schedules.schedule_on,
1031 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1032 case
1033 when (dayofweek(schedules.schedule_on) between 2 and 6) then 0
1034 else 1
1035 end as is_weekend,
1036 call_types.display_order,
1037 0 as should_be_evaluated,
1038 0 as should_be_populated,
1039 -1 as linkage_instance_id
1040 from
1041 buffer_schedules_126128084 as buffer_schedules_1
1042 inner join negative_links_influences use index (ix_unique_links)
1043 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1044 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1045 and negative_links_influences.source_type = 'CallType'
1046 and negative_links_influences.target_type = 'CallType'
1047 inner join schedules use index (ix_schedules_call_date)
1048 on date_add(
1049 buffer_schedules_1.schedule_on,
1050 interval negative_links_influences.offset day
1051 ) = schedules.schedule_on
1052 and negative_links_influences.target_id = schedules.call_type_id
1053 left join buffer_schedules_126128084 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1054 on schedules.schedule_on = buffer_schedules_2.schedule_on
1055 and schedules.call_type_id = buffer_schedules_2.call_type_id
1056 inner join call_types
1057 on schedules.call_type_id = call_types.id
1058 where
1059 buffer_schedules_1.linkage_instance_id >= 0
1060 and buffer_schedules_1.should_be_evaluated = 1
1061 and buffer_schedules_2.id is null
1062 order by null
1063
1064INFO : (08-02 10:32:49) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1065 (131.0ms) insert into buffer_schedules_126128084 (
1066 call_type_id,
1067 staff_id,
1068 schedule_on,
1069 day_of_week_mask,
1070 is_weekend,
1071 display_order,
1072 should_be_evaluated,
1073 should_be_populated,
1074 linkage_instance_id
1075 )
1076 select distinct straight_join
1077 (-1 * negative_links_influences.target_id) as call_type_id,
1078 vacations.staff_id,
1079 vacations.schedule_on,
1080 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1081 vacations.weekend_on as is_weekend,
1082 -1 as display_order,
1083 0 as should_be_evaluated,
1084 0 as should_be_populated,
1085 -1 as linkage_instance_id
1086 from
1087 buffer_schedules_126128084 as buffer_schedules_1
1088 inner join negative_links_influences use index (ix_unique_links)
1089 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1090 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1091 and negative_links_influences.source_type = 'CallType'
1092 and negative_links_influences.target_id > 0
1093 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1094 inner join vacations use index (ix_vacations_on_schedule_and_client)
1095 on vacations.client_id = 6 /* client_id */
1096 and date_add(
1097 buffer_schedules_1.schedule_on,
1098 interval negative_links_influences.offset day
1099 ) = vacations.schedule_on
1100 and negative_links_influences.target_id = vacations.non_clinical_type_id
1101 and vacations.status in (3) /* based on the "non-working requests" settings */
1102 left join buffer_schedules_126128084 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1103 on vacations.staff_id = buffer_schedules_2.staff_id
1104 and vacations.schedule_on = buffer_schedules_2.schedule_on
1105 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1106 where
1107 buffer_schedules_1.linkage_instance_id >= 0
1108 and buffer_schedules_1.should_be_evaluated = 1
1109 and buffer_schedules_2.id is null
1110 order by null
1111
1112INFO : (08-02 10:32:49) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1113 (129.6ms) insert into buffer_linkages_126128084 (
1114 linkage_instance_id,
1115 slots_count_total,
1116 has_conflicts
1117 )
1118 select straight_join
1119 schedules.linkage_instance_id,
1120 count(1) as slots_count_total,
1121 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1122 from
1123 buffer_schedules_126128084 as schedules
1124 left join (
1125 select straight_join
1126 linkage_relations.linkage_template_id
1127 from linkage_relations
1128 inner join negative_links_influences use index (ix_unique_links)
1129 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1130 and linkage_relations.call_type_id = negative_links_influences.source_id
1131 and negative_links_influences.source_type = 'CallType'
1132 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1133 and negative_links_influences.target_type = 'CallType'
1134 and linkage_relations.distance_in_days = negative_links_influences.offset
1135 where linkage_relations.client_id = 6 /* client_id */
1136 and (
1137 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1138 or linkage_relations.distance_in_days != 0
1139 )
1140 group by linkage_relations.linkage_template_id
1141 order by null
1142 ) as linkage_conflicts
1143 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1144 where schedules.linkage_instance_id >= 0
1145 group by
1146 schedules.linkage_instance_id,
1147 has_conflicts
1148 order by null
1149
1150INFO : (08-02 10:32:49) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1151 (129.2ms) insert into buffer_target_assignments_126128084 (
1152 call_type_id,
1153 target_group_id,
1154 assignment_id,
1155 assignment_type
1156 )
1157 select
1158 t1.call_type_id,
1159 ifnull(tg.id, 0) as target_group_id,
1160 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1161 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1162 from (
1163 select ct.id as call_type_id
1164 from
1165 buffer_schedules_126128084 as sc
1166 inner join call_types as ct
1167 on sc.call_type_id = ct.id
1168 and ct.active = 1
1169 and ct.included_in_targets > 0
1170 where linkage_instance_id >= 0
1171 group by ct.id
1172 order by null
1173 ) as t1
1174 left join target_group_calls as tgc
1175 on t1.call_type_id = tgc.call_type_id
1176 left join target_groups as tg
1177 on tgc.target_group_id = tg.id
1178 and tg.active = 1
1179 order by null
1180
1181INFO : (08-02 10:32:50) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1182INFO : (08-02 10:32:50) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1183INFO : (08-02 10:32:50) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0705
1184 (126.2ms) insert into buffer_linkage_call_type_assignments_126128084 (
1185 linkage_instance_id,
1186 call_type_id,
1187 is_weekend,
1188 period_offset,
1189 calendar_schedule_id,
1190 max_assignments,
1191 assignments_count
1192 )
1193 select
1194 s.linkage_instance_id,
1195 s.call_type_id,
1196 s.is_weekend,
1197 s.period_offset,
1198 s.calendar_schedule_id,
1199 case
1200 when (s.is_weekend = 0) then ct.max_weekday_assignments
1201 else ct.max_weekend_assignments
1202 end as _max_assignments,
1203 sum(1) as assignments_count
1204 from buffer_schedules_126128084 as s
1205 inner join call_types as ct
1206 on s.call_type_id = ct.id
1207 and ct.active = 1
1208 and (
1209 ct.max_weekday_assignments is not null
1210 or ct.max_weekend_assignments is not null
1211 )
1212 where s.linkage_instance_id >= 0
1213 and s.staff_id is null
1214 group by
1215 s.linkage_instance_id,
1216 s.call_type_id,
1217 s.is_weekend,
1218 s.period_offset,
1219 s.calendar_schedule_id,
1220 _max_assignments
1221 having _max_assignments >= 0
1222 order by null
1223
1224INFO : (08-02 10:32:50) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0706
1225 (125.5ms) insert into buffer_call_type_assignments_126128084 (
1226 staff_id,
1227 call_type_id,
1228 is_weekend,
1229 period_offset,
1230 calendar_schedule_id,
1231 assignments_count
1232 )
1233 select
1234 sc.staff_id,
1235 sc.call_type_id,
1236 sc.weekend_on as is_weekend,
1237 p.period_offset,
1238 p.calendar_schedule_id,
1239 sum(1) as assignments_count
1240 from buffer_periods_126128084 as p
1241 inner join call_types as ct
1242 on p.calendar_schedule_id = ct.calendar_schedule_id
1243 and ct.active = 1
1244 and (
1245 ct.max_weekday_assignments is not null
1246 or ct.max_weekend_assignments is not null
1247 )
1248 inner join schedules as sc
1249 on ct.id = sc.call_type_id
1250 and sc.schedule_on between p.start_date and p.end_date
1251 and sc.staff_id is not null
1252 where p.should_be_evaluated = 1
1253 group by
1254 sc.staff_id,
1255 sc.call_type_id,
1256 sc.weekend_on,
1257 p.period_offset,
1258 p.calendar_schedule_id
1259 order by null
1260
1261INFO : (08-02 10:32:50) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0707
1262INFO : (08-02 10:32:50) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0708
1263 (128.4ms) insert into buffer_linkage_assigned_days_126128084 (
1264 linkage_instance_id,
1265 period_offset,
1266 week_number,
1267 schedule_on,
1268 is_weekend
1269 )
1270 select distinct
1271 sc.linkage_instance_id,
1272 sc.period_offset,
1273 sc.week_number,
1274 sc.schedule_on,
1275 sc.is_weekend
1276 from
1277 buffer_schedules_126128084 as sc
1278 inner join call_types as ct
1279 on sc.call_type_id = ct.id
1280 and ct.unassigned_day = 0
1281 where
1282 sc.linkage_instance_id >= 0
1283 and sc.staff_id is null
1284 order by null
1285
1286INFO : (08-02 10:32:50) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0709
1287INFO : (08-02 10:32:50) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query08
1288 (127.7ms) insert into buffer_staff_daily_counts_126128084 (
1289 staff_id, schedule_on, linkage_instance_id, slots_count
1290 )
1291 select staff_id, schedule_on, null as linkage_instance_id,
1292 count(1) as slots_count
1293 from buffer_schedules_126128084
1294 where staff_id is not null
1295 and call_type_id > 0 /* VC */
1296 group by staff_id, schedule_on
1297 order by null
1298
1299 (125.0ms) insert into buffer_staff_daily_counts_126128084 (
1300 staff_id, schedule_on, linkage_instance_id, slots_count
1301 )
1302 select staff_id, schedule_on, linkage_instance_id,
1303 count(1) as slots_count
1304 from buffer_schedules_126128084
1305 where staff_id is not null
1306 and call_type_id > 0 /* VC */
1307 group by staff_id, schedule_on, linkage_instance_id
1308 order by null
1309
1310INFO : (08-02 10:32:50) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query085
1311 (262.7ms) insert into buffer_staff_specializations_126128084 (
1312 staff_id, specialization_id
1313 )
1314 select staff_id, specialization_id
1315 from view_staff_specializations
1316 where client_id = 6
1317 order by null
1318
1319 (126.6ms) insert into buffer_specialization_daily_counts_126128084 (
1320 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1321 )
1322 select sp.id as specialization_id,
1323 null as staff_id,
1324 bs_1.schedule_on,
1325 count(distinct st_sp.staff_id) as staffs_scheduled,
1326 sp.max_scheduled,
1327 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
1328 from (
1329 select schedule_on, day_of_week_mask
1330 from buffer_schedules_126128084
1331 where should_be_evaluated = 1
1332 group by schedule_on, day_of_week_mask
1333 order by null
1334 ) as bs_1
1335 inner join specializations as sp
1336 on sp.client_id = 6
1337 and sp.active = 1
1338 inner join buffer_schedules_126128084 as bs_2
1339 on bs_1.schedule_on = bs_2.schedule_on
1340 and bs_2.call_type_id > 0 /* is not VC */
1341 inner join max_applicable_call_types as mact
1342 on sp.id = mact.specialization_id
1343 and bs_2.call_type_id = mact.call_type_id
1344 left join buffer_staff_specializations_126128084 as st_sp
1345 on bs_2.staff_id = st_sp.staff_id
1346 and sp.id = st_sp.specialization_id
1347 group by sp.id, bs_1.schedule_on, sp.max_scheduled
1348 order by null
1349
1350 (126.9ms) insert into buffer_specialization_daily_counts_126128084 (
1351 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1352 )
1353 select sp.id as specialization_id,
1354 st_sp.staff_id,
1355 bs_1.schedule_on,
1356 1 as staffs_scheduled,
1357 sp.max_scheduled,
1358 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
1359 from (
1360 select schedule_on, day_of_week_mask
1361 from buffer_schedules_126128084
1362 where should_be_evaluated = 1
1363 group by schedule_on, day_of_week_mask
1364 order by null
1365 ) as bs_1
1366 inner join specializations as sp
1367 on sp.client_id = 6
1368 and sp.active = 1
1369 inner join buffer_schedules_126128084 as bs_2
1370 on bs_1.schedule_on = bs_2.schedule_on
1371 and bs_2.call_type_id > 0 /* is not VC */
1372 inner join max_applicable_call_types as mact
1373 on sp.id = mact.specialization_id
1374 and bs_2.call_type_id = mact.call_type_id
1375 inner join buffer_staff_specializations_126128084 as st_sp
1376 on bs_2.staff_id = st_sp.staff_id
1377 and sp.id = st_sp.specialization_id
1378 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
1379 order by null
1380
1381INFO : (08-02 10:32:51) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query09
1382 (126.5ms) truncate table buffer_location_privileges_126128084
1383
1384 (131.2ms) insert into buffer_location_privileges_126128084 (
1385 staff_id,
1386 location_id,
1387 begin_date,
1388 end_date
1389 )
1390 select
1391 s.id as staff_id,
1392 l.location_id,
1393 '0000-00-00' as begin_date,
1394 '9999-12-31' as end_date
1395 from (
1396 select l.id as location_id
1397 from
1398 buffer_schedules_126128084 as sc
1399 inner join call_types as ct
1400 on sc.call_type_id = ct.id
1401 and ct.active = 1
1402 inner join locations as l
1403 on ct.location_id = l.id
1404 and l.active = 1
1405 where sc.linkage_instance_id >= 0
1406 and sc.should_be_evaluated = 1
1407 group by l.id
1408 order by null
1409 ) as l
1410 inner join staffs as s
1411 on s.client_id = 6
1412 and s.active = 1
1413 and s.take_call = 1
1414 and s.auto_populatable = 1
1415 left join location_privileges as lp
1416 on s.id = lp.staff_id
1417 where lp.id is null
1418 union
1419 select
1420 lp.staff_id,
1421 lp.location_id,
1422 lp.begin_date,
1423 coalesce(lp.end_date, '9999-12-31') as end_date
1424 from (
1425 select l.id as location_id
1426 from
1427 buffer_schedules_126128084 as sc
1428 inner join call_types as ct
1429 on sc.call_type_id = ct.id
1430 and ct.active = 1
1431 inner join locations as l
1432 on ct.location_id = l.id
1433 and l.active = 1
1434 where sc.linkage_instance_id >= 0
1435 and sc.should_be_evaluated = 1
1436 group by l.id
1437 order by null
1438 ) as l
1439 inner join location_privileges as lp
1440 on l.location_id = lp.location_id
1441 order by null;
1442
1443 (3736.1ms) insert into buffer_static_availabilities_126128084 (
1444 schedule_id,
1445 staff_id,
1446 staff_type_id,
1447 is_primary
1448 )
1449 select
1450 schedules.id as schedule_id,
1451 staffs.id as staff_id,
1452 assign_staff_types.staff_type_id,
1453 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
1454 from
1455 buffer_schedules_126128084 as schedules
1456 /* AvailabilityRule */
1457 inner join call_types
1458 on schedules.call_type_id = call_types.id
1459 and call_types.active = 1
1460 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
1461 inner join staff_call_types
1462 on call_types.id = staff_call_types.call_type_id
1463 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
1464 inner join staffs
1465 on staff_call_types.staff_id = staffs.id
1466 and staffs.active = 1
1467 and staffs.take_call = 1
1468 and staffs.auto_populatable = 1
1469 inner join buffer_location_privileges_126128084 as location_privileges
1470 on staffs.id = location_privileges.staff_id
1471 and call_types.location_id = location_privileges.location_id
1472 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
1473 inner join assign_staff_types
1474 on staffs.id = assign_staff_types.staff_id
1475 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
1476 left join eligible_staff_types
1477 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
1478 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
1479 left join assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1480 on staffs.id = assign_rotation_schedules.staff_id
1481 and schedules.schedule_on between assign_rotation_schedules.begin_date and assign_rotation_schedules.end_date
1482 left join staff_rotations force index (ix_staff_rotations_composite_1)
1483 on staffs.id = staff_rotations.staff_id
1484 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
1485 and assign_rotation_schedules.rotation_schedule_id = staff_rotations.rotation_schedule_id
1486 left join daily_rotations
1487 on staffs.id = daily_rotations.staff_id
1488 and schedules.schedule_on = daily_rotations.schedule_on
1489 and assign_rotation_schedules.rotation_schedule_id = daily_rotations.rotation_schedule_id
1490 and staff_rotations.id is not null
1491 left join rotations
1492 on coalesce(daily_rotations.rotation_id, staff_rotations.rotation_id) = rotations.id
1493 and rotations.active = 1
1494 left join rotation_call_types
1495 on rotations.id = rotation_call_types.rotation_id
1496 and staff_call_types.call_type_id = rotation_call_types.call_type_id
1497 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
1498 /* RequiredRotationsRule */
1499 left join required_rotations
1500 on staff_call_types.call_type_id = required_rotations.call_type_id
1501 left join staff_rotations as rr_staff_rotations force index (ix_staff_rotations_composite_3)
1502 on staffs.id = rr_staff_rotations.staff_id
1503 and required_rotations.rotation_id = rr_staff_rotations.rotation_id
1504 left join assign_rotation_schedules as rr_assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1505 on staffs.id = rr_assign_rotation_schedules.staff_id
1506 and rr_staff_rotations.start_date <= rr_assign_rotation_schedules.end_date
1507 and rr_staff_rotations.end_date >= rr_assign_rotation_schedules.begin_date
1508 and rr_staff_rotations.rotation_schedule_id = rr_assign_rotation_schedules.rotation_schedule_id
1509 /* NoCallRequestsHiddenHardRule */
1510 left join request_settings
1511 on assign_staff_types.staff_type_id = request_settings.staff_type_id
1512 left join requests
1513 on schedules.schedule_on = requests.schedule_on
1514 and staffs.id = requests.staff_id
1515 and requests.request_type = 0 /* type = no_call */
1516 and requests.status in (1, 3) /* status = approved or fulfilled */
1517 and call_types.is_no_call_requestable = 1
1518 left join request_call_types
1519 on requests.id = request_call_types.request_id
1520 and schedules.call_type_id = request_call_types.call_type_id
1521 where
1522 schedules.linkage_instance_id >= 0
1523 and schedules.should_be_evaluated = 1
1524 and (
1525 call_types.all_staff_types = 1
1526 or eligible_staff_types.id is not null
1527 )
1528 and (
1529 rotations.id is null
1530 or rotation_call_types.id is not null
1531 )
1532 and (
1533 requests.id is null
1534 or (
1535 requests.all_call_types = 1
1536 and request_settings.call_only_all_call_switch = 1 /* calls only */
1537 and call_types.assignment_type = 1 /* clinical */
1538 )
1539 or (
1540 requests.all_call_types = 0
1541 and request_call_types.id is null
1542 )
1543 )
1544 group by
1545 schedules.id,
1546 staffs.id,
1547 assign_staff_types.staff_type_id,
1548 is_primary
1549 having
1550 sum(
1551 case
1552 when (
1553 rr_staff_rotations.id is not null
1554 and rr_assign_rotation_schedules.id is not null
1555 and schedules.schedule_on >= greatest(rr_staff_rotations.start_date, rr_assign_rotation_schedules.begin_date)
1556 )
1557 then 1
1558 else 0
1559 end
1560 ) >= count(distinct required_rotations.rotation_id) /* RequiredRotationsRule */
1561 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
1562 order by null
1563
1564INFO : (08-02 10:32:55) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query092
1565 (130.8ms) /* RotationPostCallRule */
1566 /*
1567 Exclude cases when people have a rotation starting on the next day after a call we're trying to make,
1568 if such rotation is configured appropriately ("Post-call on first day" = "Not permitted").
1569 */
1570 delete sa
1571 from buffer_static_availabilities_126128084 as sa
1572 inner join buffer_schedules_126128084 as sc
1573 on sa.schedule_id = sc.id
1574 inner join call_types as ct
1575 on sc.call_type_id = ct.id
1576 and ct.assignment_type = 0 /* call */
1577 inner join staff_rotations as sr
1578 on sa.staff_id = sr.staff_id
1579 and date_add(sc.schedule_on, interval 1 day) between sr.start_date and sr.end_date
1580 inner join assign_rotation_schedules as ars
1581 on sa.staff_id = ars.staff_id
1582 and date_add(sc.schedule_on, interval 1 day) between ars.begin_date and ars.end_date
1583 and sr.rotation_schedule_id = ars.rotation_schedule_id
1584 inner join rotations as r
1585 on sr.rotation_id = r.id
1586 and r.post_call_allowed = 0
1587 left join assign_rotation_schedules as ars_2
1588 on sa.staff_id = ars_2.staff_id
1589 and sr.start_date <= ars_2.end_date
1590 and sr.end_date >= ars_2.begin_date
1591 and sr.rotation_schedule_id = ars_2.rotation_schedule_id
1592 -- Earlier intersection
1593 and ars.begin_date > ars_2.begin_date
1594 where
1595 date_add(sc.schedule_on, interval 1 day) = greatest(sr.start_date, ars.begin_date)
1596 and ars_2.id is null;
1597
1598INFO : (08-02 10:32:55) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query093
1599INFO : (08-02 10:32:55) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query095
1600 (133.4ms) insert into buffer_negative_relations_126128084 (
1601 schedule_id,
1602 affected_by_schedule_id,
1603 staff_id
1604 )
1605 select
1606 b_schedules.id as schedule_id,
1607 b_schedules_negative_link.id as affected_by_schedule_id,
1608 b_schedules_negative_link.staff_id
1609 from
1610 buffer_schedules_126128084 as b_schedules
1611 /* NegativeLinkRule */
1612 inner join negative_links_influences
1613 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1614 and b_schedules.call_type_id = negative_links_influences.source_id
1615 and negative_links_influences.source_type = 'CallType'
1616 and negative_links_influences.target_type = 'CallType'
1617 inner join buffer_schedules_126128084 as b_schedules_negative_link
1618 on date_add(
1619 b_schedules.schedule_on,
1620 interval negative_links_influences.offset day
1621 ) = b_schedules_negative_link.schedule_on
1622 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
1623 and b_schedules.id != b_schedules_negative_link.id
1624 where
1625 b_schedules.linkage_instance_id >= 0
1626 and b_schedules.should_be_evaluated = 1
1627 order by null
1628
1629 (132.7ms) insert into buffer_negative_relations_126128084 (
1630 schedule_id,
1631 affected_by_schedule_id,
1632 staff_id
1633 )
1634 select
1635 b_schedules.id as schedule_id,
1636 b_schedules_negative_link.id as affected_by_schedule_id,
1637 b_schedules_negative_link.staff_id
1638 from
1639 buffer_schedules_126128084 as b_schedules
1640 /* NegativeLinkRule */
1641 inner join negative_links_influences
1642 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1643 and b_schedules.call_type_id = negative_links_influences.source_id
1644 and negative_links_influences.source_type = 'CallType'
1645 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1646 inner join buffer_schedules_126128084 as b_schedules_negative_link
1647 on date_add(
1648 b_schedules.schedule_on,
1649 interval negative_links_influences.offset day
1650 ) = b_schedules_negative_link.schedule_on
1651 and (-1 * negative_links_influences.target_id) = b_schedules_negative_link.call_type_id
1652 and b_schedules.id != b_schedules_negative_link.id
1653 where
1654 b_schedules.linkage_instance_id >= 0
1655 and b_schedules.should_be_evaluated = 1
1656 order by null
1657
1658 (126.3ms) delete from buffer_schedules_126128084
1659 where call_type_id < 0 /* vacations and non-clinicals */
1660
1661INFO : (08-02 10:32:55) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query097
1662 (701.1ms) insert into buffer_staff_assigned_days_126128084 (
1663 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
1664 )
1665 select staffs.staff_id,
1666 if(days.within_period = 1, days.period_offset, null) as period_offset,
1667 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
1668 days.schedule_on,
1669 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
1670 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
1671 from (
1672 select distinct staff_id
1673 from buffer_static_availabilities_126128084
1674 order by null
1675 ) as staffs
1676 inner join (
1677 select p.period_offset,
1678 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,
1679 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
1680 from buffer_periods_126128084 as p
1681 inner join (
1682 select 0 as digit union select 1 union select 2 union select 3 union select 4
1683 union select 5 union select 6 union select 7 union select 8 union select 9
1684 ) as digits_1
1685 inner join (
1686 select 0 as digit union select 1 union select 2 union select 3 union select 4
1687 union select 5 union select 6 union select 7 union select 8 union select 9
1688 ) as digits_2
1689 inner join (
1690 select 0 as digit union select 1 union select 2 union select 3 union select 4
1691 union select 5 union select 6 union select 7 union select 8 union select 9
1692 ) as digits_3
1693 where p.calendar_schedule_id = 6
1694 and p.should_be_evaluated = 1
1695 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
1696 order by null
1697 ) as days
1698 inner join (
1699 select min(start_date) as start_date, max(end_date) as end_date
1700 from buffer_periods_126128084
1701 where calendar_schedule_id = 6
1702 and should_be_evaluated = 1
1703 order by null
1704 ) as limits
1705 inner join clients
1706 on clients.id = 6
1707 left join (
1708 select sc.staff_id, sc.schedule_on
1709 from buffer_periods_126128084 as p
1710 inner join schedules as sc
1711 on sc.client_id = 6
1712 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
1713 inner join call_types as ct
1714 on sc.call_type_id = ct.id
1715 and ct.unassigned_day = 0
1716 where p.calendar_schedule_id = 6
1717 and p.should_be_evaluated = 1
1718 group by sc.staff_id, sc.schedule_on
1719 order by null
1720 ) as schedules
1721 on staffs.staff_id = schedules.staff_id
1722 and days.schedule_on = schedules.schedule_on
1723 left join (
1724 select v.staff_id, v.schedule_on
1725 from buffer_periods_126128084 as p
1726 inner join vacations as v
1727 on v.client_id = 6
1728 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
1729 and v.status IN (3)
1730 inner join non_clinical_types as nct
1731 on v.non_clinical_type_id = nct.id
1732 and nct.active = 1
1733 and nct.unassigned_day = 0
1734 where p.calendar_schedule_id = 6
1735 and p.should_be_evaluated = 1
1736 group by v.staff_id, v.schedule_on
1737 order by null
1738 ) as vacations
1739 on staffs.staff_id = vacations.staff_id
1740 and days.schedule_on = vacations.schedule_on
1741 where days.within_period = 1
1742 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
1743 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
1744 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
1745 )
1746 order by null
1747
1748INFO : (08-02 10:32:56) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query098
1749 (135.4ms) insert into buffer_combined_availabilities_126128084 (
1750 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
1751 )
1752 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
1753 sc.linkage_instance_id, 0 as has_violations
1754 from buffer_schedules_126128084 as sc
1755 /* Static availability */
1756 inner join buffer_static_availabilities_126128084 as sa
1757 on sc.id = sa.schedule_id
1758 inner join buffer_linkages_126128084 as l
1759 on sc.linkage_instance_id = l.linkage_instance_id
1760 where sc.should_be_evaluated = 1
1761 and l.has_conflicts = 0
1762
1763 (125.0ms) insert into buffer_schedule_linkages_126128084 (
1764 staff_id, linkage_instance_id, slots_count
1765 )
1766 select staff_id, linkage_instance_id, count(1) as slots_count
1767 from buffer_schedules_126128084 use index (ix_buffer_schedules_composite_2)
1768 where staff_id is not null
1769 and linkage_instance_id >= 0
1770 group by staff_id, linkage_instance_id
1771 order by null
1772
1773INFO : (08-02 10:32:56) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query09_09_AL
1774 (157.7ms) insert into buffer_assignment_limits_126128084 (
1775 id,
1776 name,
1777 calendar_schedule_id,
1778 considered_days_of_week_mask,
1779 limit_type,
1780 limit_subtypes_mask,
1781 timeframe_type,
1782 max_per_period_limit,
1783 max_weekly_limit,
1784 max_rolling_period,
1785 max_rolling_limit,
1786 max_consecutive_limit
1787 )
1788 select
1789 id,
1790 name,
1791 calendar_schedule_id,
1792 considered_days_of_week_mask,
1793 limit_type,
1794 (
1795 (coalesce(max_per_period_limit, -1) >= 0) * 1 +
1796 (coalesce(max_weekly_limit, -1) >= 0) * 2 +
1797 (
1798 coalesce(max_rolling_period, -1) > 0
1799 and coalesce(max_rolling_limit, -1) >= 0
1800 ) * 4 +
1801 (coalesce(max_consecutive_limit, -1) > 0) * 8
1802 ) as limit_subtypes_mask,
1803 timeframe_type,
1804 max_per_period_limit,
1805 max_weekly_limit,
1806 max_rolling_period,
1807 max_rolling_limit,
1808 max_consecutive_limit
1809 from assignment_limits
1810 where
1811 client_id = 6
1812 and active = 1
1813 and (
1814 max_per_period_limit >= 0
1815 or max_weekly_limit >= 0
1816 or (
1817 max_rolling_period > 0
1818 and max_rolling_limit >= 0
1819 )
1820 or max_consecutive_limit > 0
1821 )
1822 order by null
1823
1824 (125.7ms) select id from buffer_assignment_limits_126128084 limit 1
1825 (146.8ms) insert into buffer_assignment_limit_call_types_126128084 (
1826 assignment_limit_id,
1827 call_type_id
1828 )
1829 select
1830 alct.assignment_limit_id,
1831 alct.call_type_id
1832 from
1833 buffer_assignment_limits_126128084 as al
1834 inner join assignment_limit_call_types as alct
1835 on al.id = alct.assignment_limit_id
1836 order by null
1837
1838 (128.8ms) insert into buffer_assignment_limit_staffs_126128084 (
1839 assignment_limit_id,
1840 staff_id
1841 )
1842 select
1843 als.assignment_limit_id,
1844 als.staff_id
1845 from
1846 buffer_assignment_limits_126128084 as al
1847 inner join assignment_limit_staffs as als
1848 on al.id = als.assignment_limit_id
1849 inner join (
1850 select staff_id
1851 from buffer_combined_availabilities_126128084
1852 group by staff_id
1853 order by null
1854 ) as s
1855 on als.staff_id = s.staff_id
1856 union
1857 select
1858 al.id as assignment_limit_id,
1859 s.staff_id
1860 from
1861 (
1862 select al.id
1863 from
1864 buffer_assignment_limits_126128084 as al
1865 left join assignment_limit_staffs as als
1866 on al.id = als.assignment_limit_id
1867 where als.id is null
1868 group by al.id
1869 order by null
1870 ) as al
1871 inner join (
1872 select staff_id
1873 from buffer_combined_availabilities_126128084
1874 group by staff_id
1875 order by null
1876 ) as s
1877 on 1 = 1
1878 order by null
1879
1880 (128.5ms) insert into buffer_assignment_limit_staff_types_126128084 (
1881 assignment_limit_id,
1882 staff_type_id
1883 )
1884 select
1885 alst.assignment_limit_id,
1886 alst.staff_type_id
1887 from
1888 buffer_assignment_limits_126128084 as al
1889 inner join assignment_limit_staff_types as alst
1890 on al.id = alst.assignment_limit_id
1891 order by null
1892
1893 (126.0ms) delete al
1894 from
1895 buffer_assignment_limits_126128084 as al
1896 inner join (
1897 select t1.assignment_limit_id
1898 from (
1899 select assignment_limit_id
1900 from buffer_assignment_limit_call_types_126128084
1901 group by assignment_limit_id
1902 union all
1903 select assignment_limit_id
1904 from buffer_assignment_limit_staffs_126128084
1905 group by assignment_limit_id
1906 union all
1907 select assignment_limit_id
1908 from buffer_assignment_limit_staff_types_126128084
1909 group by assignment_limit_id
1910 order by null
1911 ) as t1
1912 group by t1.assignment_limit_id
1913 having count(1) < 3
1914 order by null
1915 ) as t1
1916 on al.id = t1.assignment_limit_id
1917
1918 (125.5ms) delete alct
1919 from
1920 buffer_assignment_limit_call_types_126128084 as alct
1921 left join buffer_assignment_limits_126128084 as al
1922 on alct.assignment_limit_id = al.id
1923 where al.id is null
1924
1925 (246.2ms) delete als
1926 from
1927 buffer_assignment_limit_staffs_126128084 as als
1928 left join buffer_assignment_limits_126128084 as al
1929 on als.assignment_limit_id = al.id
1930 where al.id is null
1931
1932 (159.2ms) delete alst
1933 from
1934 buffer_assignment_limit_staff_types_126128084 as alst
1935 left join buffer_assignment_limits_126128084 as al
1936 on alst.assignment_limit_id = al.id
1937 where al.id is null
1938
1939INFO : (08-02 10:32:58) --> fill: 34 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
1940 (125.4ms) select
1941 coalesce(bit_or(limit_subtypes_mask), 0)
1942 from buffer_assignment_limits_126128084
1943
1944INFO : (08-02 10:32:58) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
1945INFO : (08-02 10:32:58) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
1946 (128.3ms) insert into buffer_assignment_limit_periods_126128084 (
1947 assignment_limit_id,
1948 limit_type,
1949 limit_subtype_mask,
1950 limit_value,
1951 start_date,
1952 end_date
1953 )
1954 select
1955 al.id as assignment_limit_id,
1956 al.limit_type,
1957 2 /* per week */ as limit_subtype_mask,
1958 al.max_weekly_limit as limit_value,
1959 case
1960 when (cl.start_of_week = 0) then
1961 date_add(sc.schedule_on, interval (1 - dayofweek(sc.schedule_on)) day)
1962 else
1963 date_add(sc.schedule_on, interval (0 - weekday(sc.schedule_on)) day)
1964 end as _start_date,
1965 case
1966 when (cl.start_of_week = 0) then
1967 date_add(sc.schedule_on, interval (7 - dayofweek(sc.schedule_on)) day)
1968 else
1969 date_add(sc.schedule_on, interval (6 - weekday(sc.schedule_on)) day)
1970 end as _end_date
1971 from
1972 buffer_schedules_126128084 as sc
1973 inner join buffer_assignment_limit_call_types_126128084 as alct
1974 on sc.call_type_id = alct.call_type_id
1975 inner join buffer_assignment_limits_126128084 as al
1976 on alct.assignment_limit_id = al.id
1977 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1978 and (al.limit_subtypes_mask & 2 /* per week */) > 0
1979 inner join clients as cl
1980 on cl.id = 6
1981 where sc.should_be_evaluated = 1
1982 group by
1983 al.id,
1984 al.limit_type,
1985 al.max_weekly_limit,
1986 _start_date,
1987 _end_date
1988 order by null
1989
1990INFO : (08-02 10:32:58) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
1991INFO : (08-02 10:32:58) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
1992INFO : (08-02 10:32:58) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
1993 (139.0ms) insert into buffer_linkage_assignment_limits_126128084 (
1994 staff_id,
1995 linkage_instance_id,
1996 assignment_limit_id,
1997 period_id,
1998 consecutive_days_mask,
1999 linkage_value
2000 )
2001 select
2002 ca.staff_id,
2003 ca.linkage_instance_id,
2004 al.id as assignment_limit_id,
2005 alp.id as period_id,
2006 case
2007 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2008 bit_or(1 << datediff(sc.schedule_on, alp.start_date))
2009 else 0
2010 end as consecutive_days_mask,
2011 case
2012 when (al.limit_type = 0 /* assignments */) then count(sc.id)
2013 when (al.limit_type = 1 /* points */) then sum(ci.points)
2014 when (al.limit_type = 2 /* hours */) then sum(ci.hours)
2015 else 0
2016 end as linkage_value
2017 from
2018 buffer_combined_availabilities_126128084 as ca
2019 inner join buffer_schedules_126128084 as sc
2020 on ca.schedule_id = sc.id
2021 inner join buffer_assignment_limit_call_types_126128084 as alct
2022 on sc.call_type_id = alct.call_type_id
2023 inner join buffer_assignment_limits_126128084 as al
2024 on alct.assignment_limit_id = al.id
2025 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2026 inner join buffer_assignment_limit_staffs_126128084 as als
2027 on al.id = als.assignment_limit_id
2028 and ca.staff_id = als.staff_id
2029 inner join buffer_assignment_limit_staff_types_126128084 as alst
2030 on al.id = alst.assignment_limit_id
2031 and ca.staff_type_id = alst.staff_type_id
2032 inner join buffer_assignment_limit_periods_126128084 as alp
2033 on al.id = alp.assignment_limit_id
2034 and sc.schedule_on between alp.start_date and alp.end_date
2035 inner join buffer_counter_increments_126128084 as ci
2036 on sc.id = ci.schedule_id
2037 group by
2038 ca.staff_id,
2039 ca.linkage_instance_id,
2040 al.id,
2041 alp.id
2042 having linkage_value != 0
2043 order by null
2044
2045INFO : (08-02 10:32:58) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
2046 (148.8ms) insert into buffer_staff_assignment_limits_126128084 (
2047 staff_id,
2048 assignment_limit_id,
2049 period_id,
2050 consecutive_days_mask,
2051 current_value
2052 )
2053 select
2054 lal.staff_id,
2055 al.id as assignment_limit_id,
2056 alp.id as period_id,
2057 case
2058 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2059 bit_or(1 << datediff(sc.schedule_on, alp.start_date))
2060 else 0
2061 end as consecutive_days_mask,
2062 case
2063 when (al.limit_type = 0 /* assignments */) then count(sc.id)
2064 when (al.limit_type = 1 /* points */) then sum(coalesce(dp.point, dp_2.point, 0.0))
2065 when (al.limit_type = 2 /* hours */) then sum(coalesce(ct_h.hours, 0.0))
2066 else 0
2067 end as current_value
2068 from
2069 (
2070 select
2071 staff_id,
2072 assignment_limit_id,
2073 period_id
2074 from buffer_linkage_assignment_limits_126128084
2075 group by
2076 staff_id,
2077 assignment_limit_id,
2078 period_id
2079 order by null
2080 ) as lal
2081 inner join buffer_assignment_limit_staffs_126128084 as als
2082 on lal.assignment_limit_id = als.assignment_limit_id
2083 and lal.staff_id = als.staff_id
2084 inner join buffer_assignment_limit_periods_126128084 as alp
2085 on lal.period_id = alp.id
2086 inner join buffer_assignment_limits_126128084 as al
2087 on lal.assignment_limit_id = al.id
2088 inner join (
2089 select
2090 sc.id,
2091 sc.schedule_on,
2092 (1 << (sc.dayofweek_on - 1)) as day_of_week_mask,
2093 (sc.dayofweek_on - 1) as wday,
2094 sc.call_type_id,
2095 sc.staff_id
2096 from
2097 schedules as sc
2098 inner join (
2099 select
2100 min(start_date) as start_date,
2101 max(end_date) as end_date
2102 from buffer_assignment_limit_periods_126128084
2103 ) as alp
2104 on sc.schedule_on between alp.start_date and alp.end_date
2105 where sc.client_id = 6
2106 order by null
2107 ) as sc
2108 on lal.staff_id = sc.staff_id
2109 and sc.schedule_on between alp.start_date and alp.end_date
2110 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2111 inner join buffer_assignment_limit_call_types_126128084 as alct
2112 on lal.assignment_limit_id = alct.assignment_limit_id
2113 and sc.call_type_id = alct.call_type_id
2114 inner join assign_staff_types as ast
2115 on lal.staff_id = ast.staff_id
2116 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2117 inner join buffer_assignment_limit_staff_types_126128084 as alst
2118 on lal.assignment_limit_id = alst.assignment_limit_id
2119 and ast.staff_type_id = alst.staff_type_id
2120 left join (
2121 select
2122 hd.observed_date,
2123 case
2124 when (hd.observed_date > hd.extended_date) then hd.extended_date
2125 else hd.observed_date
2126 end as date_from,
2127 case
2128 when (hd.observed_date < hd.extended_date) then hd.extended_date
2129 else hd.observed_date
2130 end as date_to
2131 from
2132 holidays as h
2133 inner join holiday_dates as hd
2134 on h.id = hd.holiday_id
2135 inner join (
2136 select
2137 year(min(start_date)) as year_from,
2138 year(max(end_date)) as year_to
2139 from buffer_assignment_limit_periods_126128084
2140 ) as y
2141 on hd.year between y.year_from and y.year_to
2142 where
2143 h.client_id = 6
2144 and (
2145 h.observed = 1
2146 or h.extended = 1
2147 )
2148 order by null
2149 ) as hl
2150 on sc.schedule_on between hl.date_from and hl.date_to
2151 and al.limit_type = 1 /* points */
2152 left join assignment_annotations as aa
2153 on sc.call_type_id = aa.assignmentable_id
2154 and aa.assignmentable_type = 'CallType'
2155 and aa.annotation_id = 0 /* annotation = "None" */
2156 and aa.applicable = 1
2157 and al.limit_type = 1 /* points */
2158 left join assignment_annotation_daily_points as dp
2159 on aa.id = dp.assignment_annotation_id
2160 and hl.observed_date is not null
2161 and case
2162 when (sc.schedule_on = hl.observed_date) then 7
2163 else 8
2164 end = dp.day
2165 left join assignment_annotation_daily_points as dp_2
2166 on aa.id = dp_2.assignment_annotation_id
2167 and sc.wday = dp_2.day
2168 left join (
2169 select
2170 id,
2171 coalesce(
2172 timestampdiff(
2173 minute,
2174 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
2175 concat(
2176 '1900-01-0',
2177 case
2178 when
2179 (
2180 (
2181 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
2182 )
2183 and (
2184 timediff(
2185 maketime(end_time_hour, end_time_minute, 0),
2186 maketime(start_time_hour, start_time_minute, 0)
2187 ) <= 0
2188 )
2189 ) then '2'
2190 else '1'
2191 end,
2192 ' ',
2193 maketime(end_time_hour, end_time_minute, 0)
2194 )
2195 ) / 60,
2196 0.0
2197 ) as hours
2198 from call_types
2199 where
2200 client_id = 6
2201 and active = 1
2202 having hours > 0
2203 order by null
2204 ) as ct_h
2205 on sc.call_type_id = ct_h.id
2206 and al.limit_type = 2 /* hours */
2207 group by
2208 lal.staff_id,
2209 al.id,
2210 alp.id
2211 order by null
2212
2213INFO : (08-02 10:32:58) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Points
2214INFO : (08-02 10:32:58) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
2215INFO : (08-02 10:32:58) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query10
2216 (126.3ms) select
2217 coalesce(max(
2218 case
2219 when (ct.max_weekday_assignments > 0) then 1
2220 else 0
2221 end
2222 ), 0) as weekday_flag,
2223 coalesce(max(
2224 case
2225 when (ct.max_weekend_assignments > 0) then 1
2226 else 0
2227 end
2228 ), 0) as weekend_flag
2229 from
2230 buffer_auto_populate_slots_126128084 as aps
2231 inner join call_types as ct
2232 on aps.call_type_id = ct.id
2233
2234 (141.3ms) /* AssignmentLimitsPerPeriod */
2235 /* AssignmentLimitsPerWeek */
2236 /* AssignmentLimitsRolling */
2237 /* AssignmentLimitsConsecutive */
2238
2239 delete ca
2240 from
2241 buffer_combined_availabilities_126128084 as ca
2242 inner join buffer_linkage_assignment_limits_126128084 as lal
2243 on ca.staff_id = lal.staff_id
2244 and ca.linkage_instance_id = lal.linkage_instance_id
2245 left join buffer_staff_assignment_limits_126128084 as sal
2246 on ca.staff_id = sal.staff_id
2247 and lal.period_id = sal.period_id
2248 inner join buffer_assignment_limit_periods_126128084 as alp
2249 on lal.period_id = alp.id
2250 where
2251 case
2252 when alp.limit_subtype_mask in (1, 2, 4) /* per period, per week, rolling */ then
2253 (lal.linkage_value + coalesce(sal.current_value, 0))
2254 else /* consecutive */
2255 bit_count(lal.consecutive_days_mask | coalesce(sal.consecutive_days_mask, 0))
2256 end > alp.limit_value
2257
2258 (127.8ms) /* NegativeLinkRule */
2259 delete ca
2260 from buffer_combined_availabilities_126128084 as ca use index (ix_buffer_combined_availabilities_composite_1)
2261 inner join buffer_negative_relations_126128084 as nr use index (ix_buffer_negative_relations_composite_1)
2262 on ca.schedule_id = nr.schedule_id
2263 and ca.staff_id = nr.staff_id
2264
2265 (126.9ms) /* SpecializationOverlapRule */
2266 delete ca
2267 from buffer_combined_availabilities_126128084 as ca use index (ix_buffer_combined_availabilities_composite_1)
2268 inner join buffer_schedules_126128084 as sc
2269 on ca.schedule_id = sc.id
2270 inner join buffer_staff_specializations_126128084 as ssp
2271 on ca.staff_id = ssp.staff_id
2272 inner join max_applicable_call_types as mact
2273 on ssp.specialization_id = mact.specialization_id
2274 and sc.call_type_id = mact.call_type_id
2275 inner join buffer_specialization_daily_counts_126128084 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2276 on ssp.specialization_id = sdc.specialization_id
2277 and sc.schedule_on = sdc.schedule_on
2278 and sdc.staff_id is null
2279 left join buffer_specialization_daily_counts_126128084 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2280 on ssp.specialization_id = sdc_2.specialization_id
2281 and sc.schedule_on = sdc_2.schedule_on
2282 and ca.staff_id = sdc_2.staff_id
2283 where sdc.should_be_evaluated = 1
2284 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2285
2286 (128.2ms) /* BrokenLinkageRule */
2287 truncate table buffer_combined_availability_linkages_126128084
2288
2289 (130.2ms) insert into buffer_combined_availability_linkages_126128084 (
2290 staff_id,
2291 linkage_instance_id,
2292 slots_count,
2293 is_primary,
2294 has_violations
2295 )
2296 select
2297 staff_id,
2298 linkage_instance_id,
2299 count(1) as slots_count,
2300 min(is_primary) as is_primary,
2301 max(has_violations) as has_violations
2302 from buffer_combined_availabilities_126128084 use index (ix_buffer_combined_availabilities_composite_2)
2303 group by
2304 staff_id,
2305 linkage_instance_id
2306 order by null
2307
2308 (128.1ms) delete ca
2309 from
2310 buffer_combined_availabilities_126128084 as ca use index (ix_buffer_combined_availabilities_composite_1)
2311 inner join buffer_schedules_126128084 as sc
2312 on ca.schedule_id = sc.id
2313 inner join buffer_linkages_126128084 as l
2314 on sc.linkage_instance_id = l.linkage_instance_id
2315 inner join buffer_combined_availability_linkages_126128084 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2316 on ca.staff_id = ls_1.staff_id
2317 and sc.linkage_instance_id = ls_1.linkage_instance_id
2318 left join buffer_schedule_linkages_126128084 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2319 on ca.staff_id = ls_2.staff_id
2320 and sc.linkage_instance_id = ls_2.linkage_instance_id
2321 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2322
2323INFO : (08-02 10:32:59) --> fill: 44 - Scheduler::AutoPopulate::Queries::QueryScore02
2324INFO : (08-02 10:32:59) --> fill: 45 - Scheduler::AutoPopulate::Queries::QueryScore03
2325INFO : (08-02 10:32:59) --> fill: 46 - Scheduler::AutoPopulate::Queries::QueryScore04
2326INFO : (08-02 10:32:59) --> fill: 47 - Scheduler::AutoPopulate::Queries::Query11
2327 (127.7ms) update buffer_schedules_126128084 as sc
2328 left join buffer_combined_availabilities_126128084 as ca
2329 on sc.id = ca.schedule_id
2330 set sc.should_be_evaluated = 0,
2331 sc.should_be_populated = 0
2332 where sc.should_be_evaluated = 1
2333 and ca.id is null
2334
2335**Airbrake: closed