· 8 years ago · Jun 04, 2018, 04:52 PM
1(126.7ms) 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 (125.0ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3 CalendarSchedule Load (120.6ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
4 CallType Load (120.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (12692, 12693, 12694)
5 CalendarSchedule Load (120.5ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 319 AND `calendar_schedules`.`id` = 1381 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
6 Block Load (122.2ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND `blocks`.`id` = 29255 ORDER BY start_date asc, end_date asc LIMIT 1
7 BlockSchedule Load (120.3ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
8 PopulationTimeframe Load (120.8ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 319 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
9 RuleSet Load (123.9ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1461 LIMIT 1
10 Block Load (123.3ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND (blocks.start_date >= '2018-07-01' AND blocks.end_date <= '2018-07-31') ORDER BY start_date asc, end_date asc
11INFO : (05-31 19:11:11) phase: Scheduler::Base
12INFO : (05-31 19:11:11) from: 2018-07-01 to: 2018-07-31
13 Rule Load (120.3ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1461 ORDER BY applied desc, weight desc
14INFO : (05-31 19:11:11) rule configurations: Default
15 -
16INFO : (05-31 19:11:11) started: Scheduler::Cache::Collector.collect_all
17INFO : (05-31 19:11:11) collect_staffs
18 Staff Load (125.2ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `assign_staff_types` ON `assign_staff_types`.`staff_id` = `staffs`.`id` INNER JOIN `staff_types` ON `staff_types`.`id` = `assign_staff_types`.`staff_type_id` WHERE `staffs`.`client_id` = 319 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
19 Client Load (129.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319
20 StaffSpecialization Load (122.6ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (20702, 20705, 20703, 20704, 20695, 20701, 20697, 20699, 20698, 20706, 20696)
21 Specialization Load (128.2ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` IN (711, 712) ORDER BY `specializations`.`priority` ASC
22 StaffCallType Load (134.8ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (20702, 20705, 20703, 20704, 20695, 20701, 20697, 20699, 20698, 20706, 20696)
23INFO : (05-31 19:11:13) initial eligibles: [20702,20705,20703,20704,20695,20701,20697,20699,20698,20706,20696]
24INFO : (05-31 19:11:13) collect_call_types
25 CallType Load (121.9ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 319 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
26INFO : (05-31 19:11:14) initial call types: [12692,12693,12694,12706,12707]
27INFO : (05-31 19:11:14) collect_chain_links
28 ChainLink Load (126.4ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 319
29 CallType Load (131.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692
30 CallType Load (120.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692
31INFO : (05-31 19:11:15) collect_linkages
32INFO : (05-31 19:11:15) collect_schedules
33 Schedule Load (126.0ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 319 AND (`schedules`.`schedule_on` BETWEEN '2018-07-01' AND '2018-07-31')
34INFO : (05-31 19:11:15) collect_slots
35INFO : (05-31 19:11:15) collect_approved_call_requests
36 Request Load (130.8ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 319 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-07-01' AND '2018-07-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
37INFO : (05-31 19:11:15) collect_approved_no_call_requests
38 Request Load (122.8ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 319 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-07-01' AND '2018-07-31') ORDER BY requests.schedule_on asc, requests.created_at asc
39INFO : (05-31 19:11:15) finished: Scheduler::Cache::Collector.collect_all
40INFO : (05-31 19:11:15) begin to process requests
41 CallType Load (122.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 1381 AND `call_types`.`active` = 1
42 (120.5ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 1381 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
43 Request Load (120.5ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 319 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (12692, 12693, 12694) AND (requests.schedule_on > '2018-06-30')
44 SQL (142.9ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 319 AND 1=0
45INFO : (05-31 19:11:16) end of requests processing
46 Block Load (121.9ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 447 AND (blocks.start_date <= '2018-07-31' AND blocks.end_date >= '2018-07-31') ORDER BY start_date asc, end_date asc LIMIT 1
47 Block Load (122.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 319 AND `blocks`.`block_schedule_id` = 447 AND (blocks.end_date < '2018-07-01') AND (blocks.id != 29255) ORDER BY start_date desc
48 Client Load (127.7ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
49 (123.8ms) SELECT `autopopulate_exclusions`.`schedule_on`, `autopopulate_exclusions`.`call_type_id` FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 319 AND `autopopulate_exclusions`.`schedule_on` IN ('2018-07-01', '2018-07-02', '2018-07-03', '2018-07-04', '2018-07-05', '2018-07-06', '2018-07-07', '2018-07-08', '2018-07-09', '2018-07-10', '2018-07-11', '2018-07-12', '2018-07-13', '2018-07-14', '2018-07-15', '2018-07-16', '2018-07-17', '2018-07-18', '2018-07-19', '2018-07-20', '2018-07-21', '2018-07-22', '2018-07-23', '2018-07-24', '2018-07-25', '2018-07-26', '2018-07-27', '2018-07-28', '2018-07-29', '2018-07-30', '2018-07-31')
50 (124.8ms) select
51 b.start_date,
52 b.end_date,
53 (pb.assignment_ids IS NULL) as fully_published,
54 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
55 pbct.days_of_week_mask
56 from blocks as b
57 inner join published_blocks as pb
58 on pb.block_id = b.id
59 inner join published_block_call_types as pbct
60 on pbct.published_block_id = pb.id
61 where b.client_id = 319
62 and pb.calendar_schedule_id = 1381
63 and b.end_date BETWEEN '2018-07-01' AND '2018-07-31'
64
65INFO : (05-31 19:11:17) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
66INFO : (05-31 19:11:17) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
67INFO : (05-31 19:11:17) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
68INFO : (05-31 19:11:17) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
69INFO : (05-31 19:11:17) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
70INFO : (05-31 19:11:17) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
71INFO : (05-31 19:11:17) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
72INFO : (05-31 19:11:17) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
73INFO : (05-31 19:11:17) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
74INFO : (05-31 19:11:17) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query015
75INFO : (05-31 19:11:17) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
76INFO : (05-31 19:11:17) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
77INFO : (05-31 19:11:17) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
78INFO : (05-31 19:11:17) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
79INFO : (05-31 19:11:17) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
80INFO : (05-31 19:11:17) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
81INFO : (05-31 19:11:17) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
82INFO : (05-31 19:11:17) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
83INFO : (05-31 19:11:17) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
84INFO : (05-31 19:11:17) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
85INFO : (05-31 19:11:17) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0705
86INFO : (05-31 19:11:17) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0706
87INFO : (05-31 19:11:17) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0707
88INFO : (05-31 19:11:17) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0708
89INFO : (05-31 19:11:17) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0709
90INFO : (05-31 19:11:17) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query07_10_AL
91INFO : (05-31 19:11:17) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
92INFO : (05-31 19:11:17) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
93INFO : (05-31 19:11:17) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
94INFO : (05-31 19:11:17) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
95INFO : (05-31 19:11:17) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query08
96INFO : (05-31 19:11:17) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query085
97INFO : (05-31 19:11:17) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query09
98INFO : (05-31 19:11:17) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query092
99INFO : (05-31 19:11:17) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query093
100INFO : (05-31 19:11:17) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query095
101INFO : (05-31 19:11:17) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query097
102INFO : (05-31 19:11:17) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query098
103INFO : (05-31 19:11:17) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
104INFO : (05-31 19:11:17) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
105INFO : (05-31 19:11:17) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_12_Points
106INFO : (05-31 19:11:17) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
107INFO : (05-31 19:11:17) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query10
108INFO : (05-31 19:11:17) <-- clear: 43 - Scheduler::AutoPopulate::Queries::QueryScore02
109INFO : (05-31 19:11:17) <-- clear: 44 - Scheduler::AutoPopulate::Queries::QueryScore03
110INFO : (05-31 19:11:17) <-- clear: 45 - Scheduler::AutoPopulate::Queries::QueryScore04
111INFO : (05-31 19:11:17) <-- clear: 46 - Scheduler::AutoPopulate::Queries::Query11
112INFO : (05-31 19:11:17) <-- clear: 47 - Scheduler::AutoPopulate::Queries::DropTables
113 (144.6ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_1110845144
114 (118.0ms) DROP TABLE IF EXISTS buffer_annual_point_targets_1110845144
115 (119.8ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_1110845144
116 (123.2ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1110845144
117 (128.8ms) DROP TABLE IF EXISTS buffer_call_days_targets_1110845144
118 (125.6ms) DROP TABLE IF EXISTS buffer_call_type_targets_1110845144
119 (129.3ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_1110845144
120 (125.0ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_1110845144
121 (122.7ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_1110845144
122 (116.8ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_1110845144
123 (124.9ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_1110845144
124 (123.0ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_1110845144
125 (124.2ms) DROP TABLE IF EXISTS buffer_current_cd_targets_1110845144
126 (117.8ms) DROP TABLE IF EXISTS buffer_current_ct_targets_1110845144
127 (124.7ms) DROP TABLE IF EXISTS buffer_current_dow_targets_1110845144
128 (122.4ms) DROP TABLE IF EXISTS buffer_current_hour_targets_1110845144
129 (125.5ms) DROP TABLE IF EXISTS buffer_current_oa_targets_1110845144
130 (123.4ms) DROP TABLE IF EXISTS buffer_current_point_targets_1110845144
131 (121.1ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_1110845144
132 (122.7ms) DROP TABLE IF EXISTS buffer_hour_targets_1110845144
133 (122.8ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_1110845144
134 (117.3ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_1110845144
135 (122.1ms) DROP TABLE IF EXISTS buffer_linkage_targets_1110845144
136 (123.7ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_1110845144
137 (122.8ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_1110845144
138 (122.2ms) DROP TABLE IF EXISTS buffer_location_privileges_1110845144
139 (123.7ms) DROP TABLE IF EXISTS buffer_periods_1110845144
140 (122.0ms) DROP TABLE IF EXISTS buffer_point_targets_1110845144
141 (123.7ms) DROP TABLE IF EXISTS buffer_schedules_1110845144
142 (121.4ms) DROP TABLE IF EXISTS buffer_target_call_types_1110845144
143 (123.0ms) DROP TABLE IF EXISTS buffer_target_daily_points_1110845144
144 (122.7ms) DROP TABLE IF EXISTS buffer_target_dates_list_1110845144
145 (124.2ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_1110845144
146 (122.5ms) DROP TABLE IF EXISTS buffer_targets_1110845144
147 (121.9ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_1110845144
148 (122.8ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_1110845144
149 (123.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_1110845144
150 (122.3ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_1110845144
151 (125.2ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_1110845144
152 (126.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_1110845144
153 (125.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_1110845144
154 (135.7ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_1110845144
155 (120.8ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_1110845144
156 (125.3ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1110845144
157 (117.1ms) DROP TABLE IF EXISTS buffer_call_type_assignments_1110845144
158 (123.0ms) DROP TABLE IF EXISTS buffer_combined_availabilities_1110845144
159 (124.2ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_1110845144
160 (121.4ms) DROP TABLE IF EXISTS buffer_counter_increments_1110845144
161 (119.7ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_1110845144
162 (122.8ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_1110845144
163 (118.8ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_1110845144
164 (121.2ms) DROP TABLE IF EXISTS buffer_linkage_patterns_1110845144
165 (122.8ms) DROP TABLE IF EXISTS buffer_linkages_1110845144
166 (122.6ms) DROP TABLE IF EXISTS buffer_location_privileges_1110845144
167 (117.5ms) DROP TABLE IF EXISTS buffer_min_distances_1110845144
168 (122.5ms) DROP TABLE IF EXISTS buffer_negative_relations_1110845144
169 (123.8ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_1110845144
170 (126.7ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_1110845144
171 (118.6ms) DROP TABLE IF EXISTS buffer_rule_scores_1110845144
172 (122.6ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_1110845144
173 (120.5ms) DROP TABLE IF EXISTS buffer_schedule_linkages_1110845144
174 (124.6ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_1110845144
175 (124.8ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_1110845144
176 (127.0ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_1110845144
177 (123.0ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_1110845144
178 (124.9ms) DROP TABLE IF EXISTS buffer_staff_specializations_1110845144
179 (123.1ms) DROP TABLE IF EXISTS buffer_static_availabilities_1110845144
180 (127.4ms) DROP TABLE IF EXISTS buffer_target_assignments_1110845144
181 (123.2ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_1110845144
182 (123.4ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_1110845144
183 (122.2ms) DROP TABLE IF EXISTS buffer_assignment_limits_1110845144
184 (123.5ms) delete from buffer_target_dates
185 where session_id = 1110845144
186
187INFO : (05-31 19:11:26) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
188 (128.7ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_1110845144 LIKE buffer_assignment_limit_call_types
189 (131.6ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_1110845144 LIKE buffer_assignment_limit_periods
190 (127.9ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_1110845144 LIKE buffer_assignment_limit_staff_types
191 (128.5ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_1110845144 LIKE buffer_assignment_limit_staffs
192 (136.1ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1110845144 LIKE buffer_auto_populate_slots
193 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_1110845144 LIKE buffer_call_type_assignments
194 (127.7ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_1110845144 LIKE buffer_combined_availabilities
195 (133.8ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_1110845144 LIKE buffer_combined_availability_linkages
196 (129.8ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_1110845144 LIKE buffer_counter_increments
197 (129.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_1110845144 LIKE buffer_linkage_assigned_days
198 (141.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_1110845144 LIKE buffer_linkage_call_type_assignments
199 (126.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_1110845144 LIKE buffer_linkage_dow_targets
200 (120.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_1110845144 LIKE buffer_linkage_patterns
201 (126.4ms) CREATE TABLE IF NOT EXISTS buffer_linkages_1110845144 LIKE buffer_linkages
202 (129.4ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1110845144 LIKE buffer_location_privileges
203 (128.3ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_1110845144 LIKE buffer_min_distances
204 (126.2ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_1110845144 LIKE buffer_negative_relations
205 (131.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_1110845144 LIKE buffer_rule_calc_filters
206 (158.0ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_1110845144 LIKE buffer_rule_calc_schedules
207 (129.4ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_1110845144 LIKE buffer_rule_scores
208 (124.9ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_1110845144 LIKE buffer_rule_scores_with_corrections
209 (125.1ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_1110845144 LIKE buffer_schedule_linkages
210 (125.6ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_1110845144 LIKE buffer_schedules_linkage_patterns
211 (130.9ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_1110845144 LIKE buffer_specialization_daily_counts
212 (126.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_1110845144 LIKE buffer_staff_assigned_days
213 (124.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_1110845144 LIKE buffer_staff_daily_counts
214 (125.9ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_1110845144 LIKE buffer_staff_specializations
215 (126.2ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_1110845144 LIKE buffer_static_availabilities
216 (137.9ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_1110845144 LIKE buffer_target_assignments
217 (124.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_1110845144 LIKE buffer_linkage_assignment_limits
218 (125.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_1110845144 LIKE buffer_staff_assignment_limits
219 (131.1ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_1110845144 LIKE buffer_assignment_limits
220 (127.2ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_1110845144 LIKE buffer_annual_hour_targets
221 (130.3ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_1110845144 LIKE buffer_annual_point_targets
222 (125.7ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_1110845144 LIKE buffer_annual_targets_staff_totals
223 (121.7ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1110845144 LIKE buffer_auto_populate_slots
224 (126.6ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_1110845144 LIKE buffer_call_days_targets
225 (130.0ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_1110845144 LIKE buffer_call_type_targets
226 (126.6ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_1110845144 LIKE buffer_cumulative_cd_targets
227 (125.5ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_1110845144 LIKE buffer_cumulative_ct_targets
228 (127.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_1110845144 LIKE buffer_cumulative_dow_targets
229 (124.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_1110845144 LIKE buffer_cumulative_hour_targets
230 (128.2ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_1110845144 LIKE buffer_cumulative_oa_targets
231 (126.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_1110845144 LIKE buffer_cumulative_point_targets
232 (126.9ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_1110845144 LIKE buffer_current_cd_targets
233 (125.5ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_1110845144 LIKE buffer_current_ct_targets
234 (129.4ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_1110845144 LIKE buffer_current_dow_targets
235 (127.7ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_1110845144 LIKE buffer_current_hour_targets
236 (125.8ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_1110845144 LIKE buffer_current_oa_targets
237 (124.5ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_1110845144 LIKE buffer_current_point_targets
238 (130.8ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_1110845144 LIKE buffer_day_of_week_targets
239 (126.4ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_1110845144 LIKE buffer_hour_targets
240 (128.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_1110845144 LIKE buffer_linkage_call_days_targets
241 (123.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_1110845144 LIKE buffer_linkage_call_type_targets
242 (125.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_1110845144 LIKE buffer_linkage_targets
243 (125.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_1110845144 LIKE buffer_linkage_hour_targets
244 (126.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_1110845144 LIKE buffer_linkage_point_targets
245 (123.1ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1110845144 LIKE buffer_location_privileges
246 (125.6ms) CREATE TABLE IF NOT EXISTS buffer_periods_1110845144 LIKE buffer_periods
247 (125.7ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_1110845144 LIKE buffer_point_targets
248 (126.7ms) CREATE TABLE IF NOT EXISTS buffer_schedules_1110845144 LIKE buffer_schedules
249 (129.2ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_1110845144 LIKE buffer_target_call_types
250 (128.7ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_1110845144 LIKE buffer_target_daily_points
251 (127.4ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_1110845144 LIKE buffer_target_dates_list
252 (125.3ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_1110845144 LIKE buffer_target_primary_availabilities
253 (125.5ms) CREATE TABLE IF NOT EXISTS buffer_targets_1110845144 LIKE buffer_targets
254 (125.6ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_1110845144 LIKE buffer_tmp_targets_availabilities
255 (127.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_1110845144 LIKE buffer_tmp_targets_availabilities_exclusions
256 (125.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_1110845144 LIKE buffer_tmp_targets_client_totals
257 (126.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_1110845144 LIKE buffer_tmp_targets_scheduled
258 (127.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_1110845144 LIKE buffer_tmp_targets_staff_totals
259INFO : (05-31 19:11:35) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
260 (120.7ms) select
261 b.start_date,
262 b.end_date,
263 (pb.assignment_ids IS NULL) as fully_published,
264 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
265 pbct.days_of_week_mask
266 from blocks as b
267 inner join published_blocks as pb
268 on pb.block_id = b.id
269 inner join published_block_call_types as pbct
270 on pbct.published_block_id = pb.id
271 where b.client_id = 319
272 and pb.calendar_schedule_id = 1381
273 and b.end_date BETWEEN '2018-07-01' AND '2018-07-31'
274
275 AutopopulateExclusion Load (124.4ms) SELECT `autopopulate_exclusions`.* FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 319 AND `autopopulate_exclusions`.`schedule_on` IN ('2018-07-01', '2018-07-02', '2018-07-03', '2018-07-04', '2018-07-05', '2018-07-06', '2018-07-07', '2018-07-08', '2018-07-09', '2018-07-10', '2018-07-11', '2018-07-12', '2018-07-13', '2018-07-14', '2018-07-15', '2018-07-16', '2018-07-17', '2018-07-18', '2018-07-19', '2018-07-20', '2018-07-21', '2018-07-22', '2018-07-23', '2018-07-24', '2018-07-25', '2018-07-26', '2018-07-27', '2018-07-28', '2018-07-29', '2018-07-30', '2018-07-31')
276 (123.1ms) INSERT INTO buffer_auto_populate_slots_1110845144
277 (schedule_on, day_of_week_mask, call_type_id, display_order, should_be_populated)
278 VALUES ('2018-07-01', 1, 12692, 0, 1), ('2018-07-01', 1, 12693, 1, 1), ('2018-07-01', 1, 12694, 3, 1), ('2018-07-02', 2, 12692, 0, 1), ('2018-07-02', 2, 12693, 1, 1), ('2018-07-02', 2, 12694, 3, 1), ('2018-07-03', 4, 12692, 0, 1), ('2018-07-03', 4, 12693, 1, 1), ('2018-07-03', 4, 12694, 3, 1), ('2018-07-04', 8, 12692, 0, 1), ('2018-07-04', 8, 12693, 1, 1), ('2018-07-04', 8, 12694, 3, 1), ('2018-07-05', 16, 12692, 0, 1), ('2018-07-05', 16, 12693, 1, 1), ('2018-07-05', 16, 12694, 3, 1), ('2018-07-06', 32, 12692, 0, 1), ('2018-07-06', 32, 12693, 1, 1), ('2018-07-06', 32, 12694, 3, 1), ('2018-07-07', 64, 12692, 0, 1), ('2018-07-07', 64, 12693, 1, 1), ('2018-07-07', 64, 12694, 3, 1), ('2018-07-08', 1, 12692, 0, 1), ('2018-07-08', 1, 12693, 1, 1), ('2018-07-08', 1, 12694, 3, 1), ('2018-07-09', 2, 12692, 0, 1), ('2018-07-09', 2, 12693, 1, 1), ('2018-07-09', 2, 12694, 3, 1), ('2018-07-10', 4, 12692, 0, 1), ('2018-07-10', 4, 12693, 1, 1), ('2018-07-10', 4, 12694, 3, 1), ('2018-07-11', 8, 12692, 0, 1), ('2018-07-11', 8, 12693, 1, 1), ('2018-07-11', 8, 12694, 3, 1), ('2018-07-12', 16, 12692, 0, 1), ('2018-07-12', 16, 12693, 1, 1), ('2018-07-12', 16, 12694, 3, 1), ('2018-07-13', 32, 12692, 0, 1), ('2018-07-13', 32, 12693, 1, 1), ('2018-07-13', 32, 12694, 3, 1), ('2018-07-14', 64, 12692, 0, 1), ('2018-07-14', 64, 12693, 1, 1), ('2018-07-14', 64, 12694, 3, 1), ('2018-07-15', 1, 12692, 0, 1), ('2018-07-15', 1, 12693, 1, 1), ('2018-07-15', 1, 12694, 3, 1), ('2018-07-16', 2, 12692, 0, 1), ('2018-07-16', 2, 12693, 1, 1), ('2018-07-16', 2, 12694, 3, 1), ('2018-07-17', 4, 12692, 0, 1), ('2018-07-17', 4, 12693, 1, 1), ('2018-07-17', 4, 12694, 3, 1), ('2018-07-18', 8, 12692, 0, 1), ('2018-07-18', 8, 12693, 1, 1), ('2018-07-18', 8, 12694, 3, 1), ('2018-07-19', 16, 12692, 0, 1), ('2018-07-19', 16, 12693, 1, 1), ('2018-07-19', 16, 12694, 3, 1), ('2018-07-20', 32, 12692, 0, 1), ('2018-07-20', 32, 12693, 1, 1), ('2018-07-20', 32, 12694, 3, 1), ('2018-07-21', 64, 12692, 0, 1), ('2018-07-21', 64, 12693, 1, 1), ('2018-07-21', 64, 12694, 3, 1), ('2018-07-22', 1, 12692, 0, 1), ('2018-07-22', 1, 12693, 1, 1), ('2018-07-22', 1, 12694, 3, 1), ('2018-07-23', 2, 12692, 0, 1), ('2018-07-23', 2, 12693, 1, 1), ('2018-07-23', 2, 12694, 3, 1), ('2018-07-24', 4, 12692, 0, 1), ('2018-07-24', 4, 12693, 1, 1), ('2018-07-24', 4, 12694, 3, 1), ('2018-07-25', 8, 12692, 0, 1), ('2018-07-25', 8, 12693, 1, 1), ('2018-07-25', 8, 12694, 3, 1), ('2018-07-26', 16, 12692, 0, 1), ('2018-07-26', 16, 12693, 1, 1), ('2018-07-26', 16, 12694, 3, 1), ('2018-07-27', 32, 12692, 0, 1), ('2018-07-27', 32, 12693, 1, 1), ('2018-07-27', 32, 12694, 3, 1), ('2018-07-28', 64, 12692, 0, 1), ('2018-07-28', 64, 12693, 1, 1), ('2018-07-28', 64, 12694, 3, 1), ('2018-07-29', 1, 12692, 0, 1), ('2018-07-29', 1, 12693, 1, 1), ('2018-07-29', 1, 12694, 3, 1), ('2018-07-30', 2, 12692, 0, 1), ('2018-07-30', 2, 12693, 1, 1), ('2018-07-30', 2, 12694, 3, 1), ('2018-07-31', 4, 12692, 0, 1), ('2018-07-31', 4, 12693, 1, 1), ('2018-07-31', 4, 12694, 3, 1)
279
280INFO : (05-31 19:11:36) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
281 (124.0ms) insert into buffer_auto_populate_slots_1110845144/* client_id */ (
282 schedule_on,
283 day_of_week_mask,
284 call_type_id,
285 display_order,
286 should_be_populated
287 )
288 select distinct
289 date_add(
290 auto_populate_slots.schedule_on,
291 interval soft_linkages.offset day
292 ) as schedule_on,
293 (1 << (
294 dayofweek(
295 date_add(
296 auto_populate_slots.schedule_on,
297 interval soft_linkages.offset day
298 )
299 ) - 1
300 )
301 ) as day_of_week_mask,
302 soft_linkages.linked_call_type_id as call_type_id,
303 call_types.display_order,
304 0 as should_be_populated
305 from
306 buffer_auto_populate_slots_1110845144/* client_id */ as auto_populate_slots
307 inner join soft_linkages
308 on auto_populate_slots.call_type_id = soft_linkages.call_type_id
309 and auto_populate_slots.day_of_week_mask = soft_linkages.day_of_week_mask
310 left join buffer_auto_populate_slots_1110845144/* client_id */ as auto_populate_slots_2
311 on date_add(
312 auto_populate_slots.schedule_on,
313 interval soft_linkages.offset day
314 ) = auto_populate_slots_2.schedule_on
315 and soft_linkages.linked_call_type_id = auto_populate_slots_2.call_type_id
316 inner join call_types
317 on soft_linkages.linked_call_type_id = call_types.id
318 where auto_populate_slots_2.id is null
319 order by null
320
321INFO : (05-31 19:11:36) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
322 (125.3ms) insert into buffer_auto_populate_slots_1110845144 (
323 schedule_on,
324 day_of_week_mask,
325 call_type_id,
326 display_order,
327 should_be_populated
328 )
329 select distinct
330 date_add(
331 auto_populate_slots.schedule_on,
332 interval linkage_relations.distance_in_days day
333 ) as schedule_on,
334 (1 << (
335 dayofweek(
336 date_add(
337 auto_populate_slots.schedule_on,
338 interval linkage_relations.distance_in_days day
339 )
340 ) - 1
341 )
342 ) as day_of_week_mask,
343 linkage_relations.linked_call_type_id as call_type_id,
344 call_types.display_order,
345 0 as should_be_populated
346 from
347 buffer_auto_populate_slots_1110845144 as auto_populate_slots
348 inner join linkage_relations
349 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
350 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
351 left join buffer_auto_populate_slots_1110845144 as auto_populate_slots_2
352 on date_add(
353 auto_populate_slots.schedule_on,
354 interval linkage_relations.distance_in_days day
355 ) = auto_populate_slots_2.schedule_on
356 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
357 inner join call_types
358 on linkage_relations.linked_call_type_id = call_types.id
359 where auto_populate_slots_2.id is null
360 order by null
361
362INFO : (05-31 19:11:36) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
363 (125.0ms) insert into linkage_relations (
364 client_id,
365 call_type_id,
366 day_of_week_mask,
367 linked_call_type_id,
368 distance_in_days,
369 linkage_template_id
370 )
371 select distinct
372 319 /* client_id */ as client_id,
373 aps.call_type_id,
374 aps.day_of_week_mask,
375 aps.call_type_id as linked_call_type_id,
376 0 as distance_in_days,
377 coalesce(
378 lr.linkage_template_id,
379 @linkage_template_max := @linkage_template_max + 1
380 ) as linkage_template_id
381 from
382 buffer_auto_populate_slots_1110845144/* client_id */ as aps
383 inner join (
384 select @linkage_template_max := count(distinct linkage_template_id) - 1
385 from linkage_relations
386 where client_id = 319 /* client_id */
387 order by null
388 ) as lt
389 on 1 = 1
390 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
391 on aps.call_type_id = lr.call_type_id
392 and aps.day_of_week_mask = lr.day_of_week_mask
393 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
394 on aps.call_type_id = lr_2.call_type_id
395 and aps.day_of_week_mask = lr_2.day_of_week_mask
396 and aps.call_type_id = lr_2.linked_call_type_id
397 and lr_2.distance_in_days = 0
398 where lr_2.id is null
399 order by null
400
401 (134.2ms) insert into buffer_schedules_1110845144 (
402 call_type_id,
403 staff_id,
404 schedule_on,
405 day_of_week_mask,
406 is_weekend,
407 display_order,
408 week_number,
409 should_be_evaluated,
410 should_be_populated,
411 linkage_instance_id
412 )
413 select
414 call_type_id,
415 staff_id,
416 schedule_on,
417 day_of_week_mask,
418 is_weekend,
419 display_order,
420 week_number,
421 max(should_be_evaluated) as should_be_evaluated,
422 max(should_be_populated) as should_be_populated,
423 min(linkage_instance_id) as linkage_instance_id
424 from (
425 select
426 auto_populate_slots_2.call_type_id,
427 schedules.staff_id,
428 auto_populate_slots_2.schedule_on,
429 auto_populate_slots_2.day_of_week_mask,
430 case
431 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
432 else 1
433 end as is_weekend,
434 auto_populate_slots_2.display_order,
435 yearweek(
436 auto_populate_slots_2.schedule_on,
437 case
438 when (clients.start_of_week = 0) then 2
439 else 7
440 end
441 ) as week_number,
442 case
443 when (schedules.staff_id is null) then 1
444 else 0
445 end as should_be_evaluated,
446 (
447 case
448 when (
449 auto_populate_slots_2.should_be_populated = 1
450 and schedules.staff_id is null
451 ) then 1
452 else 0
453 end
454 ) as should_be_populated,
455 (
456 linkage_relations.linkage_template_id * 1000000 +
457 yearweek(
458 auto_populate_slots.schedule_on,
459 case
460 when (clients.start_of_week = 0) then 2
461 else 7
462 end
463 )
464 ) as linkage_instance_id
465 from
466 buffer_auto_populate_slots_1110845144 as auto_populate_slots
467 inner join clients
468 on clients.id = 319 /* client_id */
469 inner join linkage_relations
470 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
471 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
472 inner join buffer_auto_populate_slots_1110845144 as auto_populate_slots_2
473 on date_add(
474 auto_populate_slots.schedule_on,
475 interval linkage_relations.distance_in_days day
476 ) = auto_populate_slots_2.schedule_on
477 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
478 left join schedules
479 on auto_populate_slots_2.call_type_id = schedules.call_type_id
480 and auto_populate_slots_2.schedule_on = schedules.schedule_on
481 order by null
482 ) as t1
483 group by
484 call_type_id,
485 staff_id,
486 schedule_on,
487 day_of_week_mask,
488 display_order,
489 week_number
490 order by null
491
492INFO : (05-31 19:11:36) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
493 (130.3ms) update
494 buffer_schedules_1110845144 as sc
495 inner join call_types as ct
496 on sc.call_type_id = ct.id
497 inner join calendar_schedules as cs
498 on ct.calendar_schedule_id = cs.id
499 inner join blocks as b
500 on cs.block_schedule_id = b.block_schedule_id
501 and sc.schedule_on between b.start_date and b.end_date
502 set
503 sc.calendar_schedule_id = cs.id,
504 sc.block_id = b.id
505
506 BlockSchedule Load (121.8ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 447 LIMIT 1
507 (252.1ms) insert into buffer_periods_1110845144 (
508 calendar_schedule_id,
509 block_schedule_id,
510 period_offset,
511 start_block_display_order,
512 end_block_display_order,
513 start_date,
514 end_date,
515 should_be_populated,
516 should_be_evaluated
517 )
518 select
519 t1.calendar_schedule_id,
520 t1.block_schedule_id,
521 t1.period_offset,
522 t2.start_block_display_order,
523 t2.end_block_display_order,
524 t2.start_date,
525 t2.end_date,
526 t1.should_be_populated,
527 t1.should_be_evaluated
528 from (
529 select
530 sc.calendar_schedule_id,
531 b.block_schedule_id,
532 case
533 when ((month(b.start_date) - pt.begin_period) >= 0)
534 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
535 else
536 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
537 end as period_offset,
538 max(sc.should_be_populated) as should_be_populated,
539 max(sc.should_be_evaluated) as should_be_evaluated
540 from (
541 select
542 sc.block_id,
543 sc.calendar_schedule_id,
544 max(ps.should_be_populated) as should_be_populated,
545 1 as should_be_evaluated
546 from
547 buffer_schedules_1110845144 as sc
548 inner join buffer_auto_populate_slots_1110845144 as ps
549 on sc.schedule_on = ps.schedule_on
550 and sc.call_type_id = ps.call_type_id
551 group by
552 sc.block_id,
553 sc.calendar_schedule_id
554 order by null
555 ) as sc
556 inner join blocks as b
557 on sc.block_id = b.id
558 inner join population_timeframes as pt
559 on pt.client_id = 319
560 and pt.timeframe_type = 1
561 group by
562 sc.calendar_schedule_id,
563 b.block_schedule_id,
564 period_offset
565 order by null
566 ) as t1
567 inner join (
568 select
569 b.block_schedule_id,
570 case
571 when ((month(b.start_date) - pt.begin_period) >= 0)
572 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
573 else
574 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
575 end as period_offset,
576 min(b.display_order) as start_block_display_order,
577 max(b.display_order) as end_block_display_order,
578 min(b.start_date) as start_date,
579 max(b.end_date) as end_date
580 from
581 blocks as b
582 inner join population_timeframes as pt
583 on pt.client_id = 319
584 and pt.timeframe_type = 1
585 where
586 b.client_id = 319
587 group by
588 b.block_schedule_id,
589 period_offset
590 order by null
591 ) as t2
592 on t1.block_schedule_id = t2.block_schedule_id
593 and t1.period_offset = t2.period_offset
594 order by null
595
596 (125.1ms) update
597 buffer_schedules_1110845144 as sc
598 inner join buffer_periods_1110845144 as p
599 on sc.calendar_schedule_id = p.calendar_schedule_id
600 and sc.schedule_on between p.start_date and p.end_date
601 set sc.period_offset = p.period_offset
602
603INFO : (05-31 19:11:37) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
604INFO : (05-31 19:11:37) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
605 (156.8ms) insert into buffer_counter_increments_1110845144 (
606 schedule_id,
607 calls,
608 points,
609 hours,
610 affects_cumulative_targets
611 )
612 select
613 sc.id as schedule_id,
614 case
615 when (ct.included_in_targets > 0) then 1
616 else 0
617 end as calls,
618 coalesce(
619 case
620 when (hl.observed_date is null or dp.point is not null) then dp.point
621 else dp_2.point
622 end,
623 0.0
624 ) as points,
625 ct.hours,
626 case
627 when (b.display_order between (t1.timeframe_ending - (7 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
628 else 0
629 end as affects_cumulative_targets
630 from
631 buffer_schedules_1110845144 as sc
632 inner join (
633 select
634 id,
635 included_in_targets,
636 coalesce(
637 timestampdiff(
638 minute,
639 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
640 concat(
641 '1900-01-0',
642 case
643 when ((
644 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
645 )
646 and (
647 timediff(
648 maketime(end_time_hour, end_time_minute, 0),
649 maketime(start_time_hour, start_time_minute, 0)
650 ) <= 0
651 )
652 ) then '2'
653 else '1'
654 end,
655 ' ',
656 maketime(end_time_hour, end_time_minute, 0)
657 )
658 ) / 60,
659 0.0
660 ) as hours
661 from call_types
662 where
663 client_id = 319
664 and active = 1
665 order by null
666 ) as ct
667 on sc.call_type_id = ct.id
668 inner join blocks as b
669 on sc.block_id = b.id
670 inner join (
671 select
672 calendar_schedule_id,
673 max(
674 case
675 when (should_be_populated = 1) then end_block_display_order
676 else null
677 end
678 ) as timeframe_ending
679 from buffer_periods_1110845144
680 group by calendar_schedule_id
681 order by null
682 ) as t1
683 on sc.calendar_schedule_id = t1.calendar_schedule_id
684 left join (
685 select
686 hd.observed_date,
687 case
688 when (hd.observed_date > hd.extended_date) then hd.extended_date
689 else hd.observed_date
690 end as date_from,
691 case
692 when (hd.observed_date < hd.extended_date) then hd.extended_date
693 else hd.observed_date
694 end as date_to
695 from
696 holidays as h
697 inner join holiday_dates as hd
698 on h.id = hd.holiday_id
699 inner join (
700 select
701 year(min(schedule_on)) as year_from,
702 year(max(schedule_on)) as year_to
703 from buffer_schedules_1110845144
704 where should_be_evaluated = 1
705 ) as y
706 on hd.year between y.year_from and y.year_to
707 where
708 h.client_id = 319
709 and (
710 h.observed = 1
711 or h.extended = 1
712 )
713 order by null
714 ) as hl
715 on sc.schedule_on between hl.date_from and hl.date_to
716 left join assignment_annotations as aa
717 on ct.id = aa.assignmentable_id
718 and aa.assignmentable_type = 'CallType'
719 and aa.annotation_id = 0 /* annotation = "None" */
720 and aa.applicable = 1
721 left join assignment_annotation_daily_points as dp
722 on aa.id = dp.assignment_annotation_id
723 and case
724 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
725 when (sc.schedule_on = hl.observed_date) then 7
726 else 8
727 end = dp.day
728 left join assignment_annotation_daily_points as dp_2
729 on aa.id = dp_2.assignment_annotation_id
730 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
731 where sc.should_be_evaluated = 1
732 order by null
733
734INFO : (05-31 19:11:37) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
735 (124.9ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
736 insert into buffer_linkage_patterns_1110845144 (
737 period_offset,
738 calendar_schedule_id,
739 block_id,
740 block_start_date,
741 block_start_dow,
742 block_length,
743 linkage_pattern_id,
744 pattern_start_dow,
745 days_before_start,
746 pattern_length,
747 start_offset,
748 iterations_count
749 )
750 select
751 b1.period_offset,
752 b1.calendar_schedule_id,
753 b1.block_id,
754 b2.start_date as block_start_date,
755 dayofweek(b2.start_date) as block_start_dow,
756 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
757 lp.id as linkage_pattern_id,
758 (lp.start_on + 1) as pattern_start_dow,
759 lp.days_before_start,
760 (lp.length * 7) as pattern_length,
761 null as start_offset,
762 null as iterations_count
763 from (
764 select
765 calendar_schedule_id,
766 block_id,
767 period_offset
768 from buffer_schedules_1110845144
769 group by
770 calendar_schedule_id,
771 block_id,
772 period_offset
773 order by null
774 ) as b1
775 inner join blocks as b2
776 on b1.block_id = b2.id
777 inner join linkage_patterns as lp
778 on lp.client_id = 319
779 and b1.calendar_schedule_id = lp.calendar_schedule_id
780 order by null
781
782 (124.5ms) -- Calculate the basic offset ("+" or "-") that will be applied to a linkage pattern if a block and a pattern are starting on different day of week
783 update buffer_linkage_patterns_1110845144
784 set start_offset = if(
785 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
786 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
787 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
788 )
789
790 (124.2ms) -- In some cases (when a block and a pattern are starting on different day of week or when a pattern is shorter than a block)
791 -- a pattern should be repeated couple of times (in order to cover the entire block)
792 update buffer_linkage_patterns_1110845144
793 set iterations_count =
794 ceiling(
795 (
796 block_length +
797 case
798 when (start_offset < 0) then abs(start_offset)
799 else 0
800 end
801 ) * 1.0 / pattern_length
802 )
803
804 (123.4ms) -- Generate an assignments sequence for every linkage pattern
805 insert into buffer_schedules_linkage_patterns_1110845144 (
806 call_type_id,
807 staff_id,
808 schedule_on,
809 period_offset,
810 day_of_week_mask,
811 display_order,
812 week_number,
813 should_be_evaluated,
814 should_be_populated,
815 linkage_instance_id,
816 block_id,
817 calendar_schedule_id
818 )
819 select
820 lps.assignment_id as call_type_id,
821 null as staff_id,
822 case
823 when (lp.start_offset < 0)
824 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
825 else
826 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
827 end as schedule_on,
828 lp.period_offset,
829 null as day_of_week_mask,
830 null as display_order,
831 null as week_number,
832 0 as should_be_evaluated,
833 0 as should_be_populated,
834 lp.linkage_pattern_id as linkage_instance_id,
835 lp.block_id,
836 lp.calendar_schedule_id
837 from
838 numeric_sequence as ns
839 inner join buffer_linkage_patterns_1110845144 as lp
840 on ns.item_number <= (lp.iterations_count - 1)
841 inner join linkage_pattern_slots as lps
842 on lp.linkage_pattern_id = lps.linkage_pattern_id
843 and lps.assignment_type = 'CallType'
844 where
845 case
846 when (lp.start_offset < 0)
847 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
848 else
849 (ns.item_number * lp.pattern_length + lps.offset)
850 end between 0 and (lp.block_length - 1)
851 order by null
852
853 (122.1ms) -- Fill the columns that we left blank in the previous "INSERT"
854 update
855 buffer_schedules_linkage_patterns_1110845144 as slp
856 inner join clients as cl
857 on cl.id = 319
858 inner join call_types as ct
859 on slp.call_type_id = ct.id
860 left join buffer_schedules_1110845144 as bs
861 on slp.schedule_on = bs.schedule_on
862 and slp.call_type_id = bs.call_type_id
863 set
864 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
865 slp.display_order = ct.display_order,
866 slp.week_number =
867 yearweek(
868 slp.schedule_on,
869 case
870 when (cl.start_of_week = 0) then 2
871 else 7
872 end
873 ),
874 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
875 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
876
877 (119.8ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
878 delete slp_1
879 from
880 buffer_schedules_linkage_patterns_1110845144 as slp_1
881 inner join (
882 select linkage_instance_id
883 from buffer_schedules_linkage_patterns_1110845144
884 group by linkage_instance_id
885 having
886 max(should_be_evaluated) = 0
887 and max(should_be_populated) = 0
888 order by null
889 ) as slp_2
890 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
891
892 (120.1ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
893 update
894 buffer_schedules_linkage_patterns_1110845144 as slp
895 inner join buffer_schedules_1110845144 as bs_1
896 on slp.schedule_on = bs_1.schedule_on
897 and slp.call_type_id = bs_1.call_type_id
898 inner join buffer_schedules_1110845144 as bs_2
899 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
900 set bs_2.linkage_instance_id = slp.linkage_instance_id
901
902 (119.8ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
903 -- should be copied over to the buffer schedules table (used during the auto-populate process)
904 insert into buffer_schedules_1110845144 (
905 call_type_id,
906 staff_id,
907 schedule_on,
908 period_offset,
909 day_of_week_mask,
910 is_weekend,
911 display_order,
912 week_number,
913 should_be_evaluated,
914 should_be_populated,
915 linkage_instance_id,
916 block_id,
917 calendar_schedule_id
918 )
919 select
920 call_type_id,
921 staff_id,
922 schedule_on,
923 period_offset,
924 day_of_week_mask,
925 case
926 when (day_of_week_mask & 62 > 0) then 0
927 else 1
928 end as is_weekend,
929 display_order,
930 week_number,
931 should_be_evaluated,
932 should_be_populated,
933 linkage_instance_id,
934 block_id,
935 calendar_schedule_id
936 from buffer_schedules_linkage_patterns_1110845144
937 where
938 should_be_evaluated = 0
939 and should_be_populated = 0
940
941INFO : (05-31 19:11:38) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query015
942 (124.0ms) insert into buffer_schedules_1110845144 (
943 call_type_id,
944 staff_id,
945 schedule_on,
946 day_of_week_mask,
947 is_weekend,
948 display_order,
949 should_be_evaluated,
950 should_be_populated,
951 linkage_instance_id
952 )
953 select distinct
954 schedules_call_overlap.call_type_id,
955 schedules_call_overlap.staff_id,
956 schedules_call_overlap.schedule_on,
957 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
958 case
959 when (dayofweek(schedules_call_overlap.schedule_on) between 2 and 6) then 0
960 else 1
961 end as is_weekend,
962 call_types.display_order,
963 0 as should_be_evaluated,
964 0 as should_be_populated,
965 -1 as linkage_instance_id
966 from (
967 select schedule_on
968 from buffer_schedules_1110845144
969 where should_be_evaluated = 1
970 group by schedule_on
971 order by null
972 ) as buffer_schedules_1
973 inner join schedules as schedules_call_overlap
974 on schedules_call_overlap.client_id = 319
975 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
976 left join buffer_schedules_1110845144 as buffer_schedules_2
977 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
978 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
979 inner join call_types
980 on schedules_call_overlap.call_type_id = call_types.id
981 where buffer_schedules_2.id is null
982 order by null
983
984INFO : (05-31 19:11:38) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
985INFO : (05-31 19:11:38) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
986INFO : (05-31 19:11:38) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
987INFO : (05-31 19:11:38) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
988INFO : (05-31 19:11:38) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
989 (123.1ms) insert into buffer_schedules_1110845144 (
990 call_type_id,
991 staff_id,
992 schedule_on,
993 day_of_week_mask,
994 is_weekend,
995 display_order,
996 should_be_evaluated,
997 should_be_populated,
998 linkage_instance_id
999 )
1000 select distinct straight_join
1001 schedules.call_type_id,
1002 schedules.staff_id,
1003 schedules.schedule_on,
1004 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1005 case
1006 when (dayofweek(schedules.schedule_on) between 2 and 6) then 0
1007 else 1
1008 end as is_weekend,
1009 call_types.display_order,
1010 0 as should_be_evaluated,
1011 0 as should_be_populated,
1012 -1 as linkage_instance_id
1013 from
1014 buffer_schedules_1110845144 as buffer_schedules_1
1015 inner join negative_links_influences use index (ix_unique_links)
1016 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1017 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1018 and negative_links_influences.source_type = 'CallType'
1019 and negative_links_influences.target_type = 'CallType'
1020 inner join schedules use index (ix_schedules_call_date)
1021 on date_add(
1022 buffer_schedules_1.schedule_on,
1023 interval negative_links_influences.offset day
1024 ) = schedules.schedule_on
1025 and negative_links_influences.target_id = schedules.call_type_id
1026 left join buffer_schedules_1110845144 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1027 on schedules.schedule_on = buffer_schedules_2.schedule_on
1028 and schedules.call_type_id = buffer_schedules_2.call_type_id
1029 inner join call_types
1030 on schedules.call_type_id = call_types.id
1031 where
1032 buffer_schedules_1.linkage_instance_id >= 0
1033 and buffer_schedules_1.should_be_evaluated = 1
1034 and buffer_schedules_2.id is null
1035 order by null
1036
1037INFO : (05-31 19:11:38) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1038 (124.3ms) insert into buffer_schedules_1110845144 (
1039 call_type_id,
1040 staff_id,
1041 schedule_on,
1042 day_of_week_mask,
1043 is_weekend,
1044 display_order,
1045 should_be_evaluated,
1046 should_be_populated,
1047 linkage_instance_id
1048 )
1049 select distinct straight_join
1050 (-1 * negative_links_influences.target_id) as call_type_id,
1051 vacations.staff_id,
1052 vacations.schedule_on,
1053 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1054 vacations.weekend_on as is_weekend,
1055 -1 as display_order,
1056 0 as should_be_evaluated,
1057 0 as should_be_populated,
1058 -1 as linkage_instance_id
1059 from
1060 buffer_schedules_1110845144 as buffer_schedules_1
1061 inner join negative_links_influences use index (ix_unique_links)
1062 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1063 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1064 and negative_links_influences.source_type = 'CallType'
1065 and negative_links_influences.target_id > 0
1066 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1067 inner join vacations use index (ix_vacations_on_schedule_and_client)
1068 on vacations.client_id = 319 /* client_id */
1069 and date_add(
1070 buffer_schedules_1.schedule_on,
1071 interval negative_links_influences.offset day
1072 ) = vacations.schedule_on
1073 and negative_links_influences.target_id = vacations.non_clinical_type_id
1074 and vacations.status in (3) /* based on the "non-working requests" settings */
1075 left join buffer_schedules_1110845144 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1076 on vacations.staff_id = buffer_schedules_2.staff_id
1077 and vacations.schedule_on = buffer_schedules_2.schedule_on
1078 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1079 where
1080 buffer_schedules_1.linkage_instance_id >= 0
1081 and buffer_schedules_1.should_be_evaluated = 1
1082 and buffer_schedules_2.id is null
1083 order by null
1084
1085INFO : (05-31 19:11:38) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1086 (123.2ms) insert into buffer_linkages_1110845144 (
1087 linkage_instance_id,
1088 slots_count_total,
1089 has_conflicts
1090 )
1091 select straight_join
1092 schedules.linkage_instance_id,
1093 count(1) as slots_count_total,
1094 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1095 from
1096 buffer_schedules_1110845144 as schedules
1097 left join (
1098 select straight_join
1099 linkage_relations.linkage_template_id
1100 from linkage_relations
1101 inner join negative_links_influences use index (ix_unique_links)
1102 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1103 and linkage_relations.call_type_id = negative_links_influences.source_id
1104 and negative_links_influences.source_type = 'CallType'
1105 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1106 and negative_links_influences.target_type = 'CallType'
1107 and linkage_relations.distance_in_days = negative_links_influences.offset
1108 where linkage_relations.client_id = 319 /* client_id */
1109 and (
1110 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1111 or linkage_relations.distance_in_days != 0
1112 )
1113 group by linkage_relations.linkage_template_id
1114 order by null
1115 ) as linkage_conflicts
1116 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1117 where schedules.linkage_instance_id >= 0
1118 group by
1119 schedules.linkage_instance_id,
1120 has_conflicts
1121 order by null
1122
1123INFO : (05-31 19:11:39) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1124 (126.5ms) insert into buffer_target_assignments_1110845144 (
1125 call_type_id,
1126 target_group_id,
1127 assignment_id,
1128 assignment_type
1129 )
1130 select
1131 t1.call_type_id,
1132 ifnull(tg.id, 0) as target_group_id,
1133 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1134 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1135 from (
1136 select ct.id as call_type_id
1137 from
1138 buffer_schedules_1110845144 as sc
1139 inner join call_types as ct
1140 on sc.call_type_id = ct.id
1141 and ct.active = 1
1142 and ct.included_in_targets > 0
1143 where linkage_instance_id >= 0
1144 group by ct.id
1145 order by null
1146 ) as t1
1147 left join target_group_calls as tgc
1148 on t1.call_type_id = tgc.call_type_id
1149 left join target_groups as tg
1150 on tgc.target_group_id = tg.id
1151 and tg.active = 1
1152 order by null
1153
1154INFO : (05-31 19:11:39) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1155INFO : (05-31 19:11:39) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1156INFO : (05-31 19:11:39) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0705
1157 (122.9ms) insert into buffer_linkage_call_type_assignments_1110845144 (
1158 linkage_instance_id,
1159 call_type_id,
1160 is_weekend,
1161 period_offset,
1162 calendar_schedule_id,
1163 max_assignments,
1164 assignments_count
1165 )
1166 select
1167 s.linkage_instance_id,
1168 s.call_type_id,
1169 s.is_weekend,
1170 s.period_offset,
1171 s.calendar_schedule_id,
1172 case
1173 when (s.is_weekend = 0) then ct.max_weekday_assignments
1174 else ct.max_weekend_assignments
1175 end as _max_assignments,
1176 sum(1) as assignments_count
1177 from buffer_schedules_1110845144 as s
1178 inner join call_types as ct
1179 on s.call_type_id = ct.id
1180 and ct.active = 1
1181 and (
1182 ct.max_weekday_assignments is not null
1183 or ct.max_weekend_assignments is not null
1184 )
1185 where s.linkage_instance_id >= 0
1186 and s.staff_id is null
1187 group by
1188 s.linkage_instance_id,
1189 s.call_type_id,
1190 s.is_weekend,
1191 s.period_offset,
1192 s.calendar_schedule_id,
1193 _max_assignments
1194 having _max_assignments >= 0
1195 order by null
1196
1197INFO : (05-31 19:11:39) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0706
1198 (122.3ms) insert into buffer_call_type_assignments_1110845144 (
1199 staff_id,
1200 call_type_id,
1201 is_weekend,
1202 period_offset,
1203 calendar_schedule_id,
1204 assignments_count
1205 )
1206 select
1207 sc.staff_id,
1208 sc.call_type_id,
1209 sc.weekend_on as is_weekend,
1210 p.period_offset,
1211 p.calendar_schedule_id,
1212 sum(1) as assignments_count
1213 from buffer_periods_1110845144 as p
1214 inner join call_types as ct
1215 on p.calendar_schedule_id = ct.calendar_schedule_id
1216 and ct.active = 1
1217 and (
1218 ct.max_weekday_assignments is not null
1219 or ct.max_weekend_assignments is not null
1220 )
1221 inner join schedules as sc
1222 on ct.id = sc.call_type_id
1223 and sc.schedule_on between p.start_date and p.end_date
1224 and sc.staff_id is not null
1225 where p.should_be_evaluated = 1
1226 group by
1227 sc.staff_id,
1228 sc.call_type_id,
1229 sc.weekend_on,
1230 p.period_offset,
1231 p.calendar_schedule_id
1232 order by null
1233
1234INFO : (05-31 19:11:39) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0707
1235INFO : (05-31 19:11:39) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0708
1236 (123.9ms) insert into buffer_linkage_assigned_days_1110845144 (
1237 linkage_instance_id,
1238 period_offset,
1239 week_number,
1240 schedule_on,
1241 is_weekend
1242 )
1243 select distinct
1244 sc.linkage_instance_id,
1245 sc.period_offset,
1246 sc.week_number,
1247 sc.schedule_on,
1248 sc.is_weekend
1249 from
1250 buffer_schedules_1110845144 as sc
1251 inner join call_types as ct
1252 on sc.call_type_id = ct.id
1253 and ct.unassigned_day = 0
1254 where
1255 sc.linkage_instance_id >= 0
1256 and sc.staff_id is null
1257 order by null
1258
1259INFO : (05-31 19:11:39) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0709
1260INFO : (05-31 19:11:39) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query07_10_AL
1261 (124.7ms) insert into buffer_assignment_limits_1110845144 (
1262 id,
1263 name,
1264 calendar_schedule_id,
1265 all_current_schedule_assignments,
1266 all_other_schedule_assignments,
1267 all_staff_types,
1268 considered_days_of_week_mask,
1269 limit_type,
1270 limit_subtypes_mask,
1271 timeframe_type,
1272 max_per_period_limit,
1273 max_weekly_limit,
1274 max_rolling_period,
1275 max_rolling_limit,
1276 max_consecutive_limit
1277 )
1278 select
1279 id,
1280 name,
1281 calendar_schedule_id,
1282 all_current_schedule_assignments,
1283 all_other_schedule_assignments,
1284 all_staff_types,
1285 considered_days_of_week_mask,
1286 0 /* assignments */ as limit_type,
1287 (
1288 (coalesce(max_per_period_limit, -1) >= 0) * 1 +
1289 (coalesce(max_weekly_limit, -1) >= 0) * 2 +
1290 (
1291 coalesce(max_rolling_period, -1) > 0
1292 and coalesce(max_rolling_limit, -1) >= 0
1293 ) * 4 +
1294 (coalesce(max_consecutive_limit, -1) >= 0) * 8
1295 ) as limit_subtypes_mask,
1296 timeframe_type,
1297 max_per_period_limit,
1298 max_weekly_limit,
1299 max_rolling_period,
1300 max_rolling_limit,
1301 max_consecutive_limit
1302 from assignment_limits
1303 where
1304 client_id = 319
1305 and active = 1
1306 and (
1307 max_per_period_limit >= 0
1308 or max_weekly_limit >= 0
1309 or (
1310 max_rolling_period > 0
1311 and max_rolling_limit >= 0
1312 )
1313 or max_consecutive_limit >= 0
1314 )
1315 order by null
1316
1317 (123.6ms) insert into buffer_assignment_limit_call_types_1110845144 (
1318 assignment_limit_id,
1319 call_type_id
1320 )
1321 select
1322 al.id as assignment_limit_id,
1323 ct.id as call_type_id
1324 from
1325 buffer_assignment_limits_1110845144 as al
1326 inner join assignment_limit_call_types as alct
1327 on al.id = alct.assignment_limit_id
1328 inner join call_types as ct
1329 on alct.call_type_id = ct.id
1330 and ct.active = 1
1331 union
1332 select
1333 al.id as assignment_limit_id,
1334 ct.id as call_type_id
1335 from
1336 buffer_assignment_limits_1110845144 as al
1337 inner join call_types as ct
1338 on ct.client_id = 319
1339 and ct.active = 1
1340 and al.calendar_schedule_id = ct.calendar_schedule_id
1341 where al.all_current_schedule_assignments = 1
1342 union
1343 select
1344 al.id as assignment_limit_id,
1345 ct.id as call_type_id
1346 from
1347 buffer_assignment_limits_1110845144 as al
1348 inner join call_types as ct
1349 on ct.client_id = 319
1350 and ct.active = 1
1351 and al.calendar_schedule_id != ct.calendar_schedule_id
1352 where al.all_other_schedule_assignments = 1
1353 order by null
1354
1355 (127.5ms) insert into buffer_assignment_limit_staffs_1110845144 (
1356 assignment_limit_id,
1357 staff_id
1358 )
1359 select
1360 al.id as assignment_limit_id,
1361 s.id as staff_id
1362 from
1363 buffer_assignment_limits_1110845144 as al
1364 inner join assignment_limit_staffs as als
1365 on al.id = als.assignment_limit_id
1366 inner join staffs as s
1367 on als.staff_id = s.id
1368 and s.active = 1
1369 and s.take_call = 1
1370 and s.auto_populatable = 1
1371 union
1372 select
1373 al.id as assignment_limit_id,
1374 s.id as staff_id
1375 from
1376 buffer_assignment_limits_1110845144 as al
1377 inner join staffs as s
1378 on s.client_id = 319
1379 and s.active = 1
1380 and s.take_call = 1
1381 and s.auto_populatable = 1
1382 left join assignment_limit_staffs as als
1383 on al.id = als.assignment_limit_id
1384 where als.id is null
1385 order by null
1386
1387 (129.1ms) insert into buffer_assignment_limit_staff_types_1110845144 (
1388 assignment_limit_id,
1389 staff_type_id
1390 )
1391 select
1392 al.id as assignment_limit_id,
1393 st.id as staff_type_id
1394 from
1395 buffer_assignment_limits_1110845144 as al
1396 inner join assignment_limit_staff_types as alst
1397 on al.id = alst.assignment_limit_id
1398 inner join staff_types as st
1399 on alst.staff_type_id = st.id
1400 and st.active = 1
1401 union
1402 select
1403 al.id as assignment_limit_id,
1404 st.id as staff_type_id
1405 from
1406 buffer_assignment_limits_1110845144 as al
1407 inner join staff_types as st
1408 on st.client_id = 319
1409 and st.active = 1
1410 where al.all_staff_types = 1
1411 order by null
1412
1413INFO : (05-31 19:11:40) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query07_10_AL_01
1414 (122.3ms) insert into buffer_assignment_limit_periods_1110845144 (
1415 assignment_limit_id,
1416 limit_type,
1417 limit_subtype_mask,
1418 limit_value,
1419 start_date,
1420 end_date
1421 )
1422 select
1423 t1.assignment_limit_id,
1424 t1.limit_type,
1425 t1.limit_subtype_mask,
1426 t1.limit_value,
1427 min(b.start_date) as start_date,
1428 max(b.end_date) as end_date
1429 from
1430 (
1431 select
1432 al.id as assignment_limit_id,
1433 al.limit_type,
1434 1 /* per period */ as limit_subtype_mask,
1435 al.max_per_period_limit as limit_value,
1436 al.timeframe_type as period_length,
1437 b.block_schedule_id,
1438 b.display_order as block_position,
1439 case
1440 /* calendar months */
1441 when (bs.blocks_type = 0) then
1442 (year(b.start_date) * 12 + month(b.start_date) - pt.begin_period) mod pt.timeframe_type
1443 /* custom blocks */
1444 else
1445 b.display_order
1446 end as position_inside_period
1447 from
1448 (
1449 select distinct
1450 call_type_id,
1451 day_of_week_mask,
1452 block_id
1453 from buffer_schedules_1110845144
1454 where should_be_evaluated = 1
1455 order by null
1456 ) as sc
1457 inner join buffer_assignment_limit_call_types_1110845144 as alct
1458 on sc.call_type_id = alct.call_type_id
1459 inner join buffer_assignment_limits_1110845144 as al
1460 on alct.assignment_limit_id = al.id
1461 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1462 and (al.limit_subtypes_mask & 1 /* per period */) > 0
1463 inner join blocks as b
1464 on sc.block_id = b.id
1465 inner join block_schedules as bs
1466 on b.block_schedule_id = bs.id
1467 inner join population_timeframes as pt
1468 on pt.client_id = 319
1469 and al.timeframe_type = pt.timeframe_type
1470 group by
1471 al.id,
1472 al.limit_type,
1473 al.max_per_period_limit,
1474 al.timeframe_type,
1475 b.block_schedule_id,
1476 b.display_order,
1477 position_inside_period
1478 order by null
1479 ) as t1
1480 inner join blocks as b
1481 on t1.block_schedule_id = b.block_schedule_id
1482 and b.display_order /* block_position */
1483 between (t1.block_position - t1.position_inside_period)
1484 and ((t1.block_position - t1.position_inside_period) + (t1.period_length - 1))
1485 group by
1486 t1.assignment_limit_id,
1487 t1.limit_type,
1488 t1.limit_subtype_mask,
1489 t1.limit_value
1490 order by null
1491
1492INFO : (05-31 19:11:40) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query07_10_AL_02
1493 (121.0ms) /* AssignmentLimitsPerWeek */
1494 insert into buffer_assignment_limit_periods_1110845144 (
1495 assignment_limit_id,
1496 limit_type,
1497 limit_subtype_mask,
1498 limit_value,
1499 start_date,
1500 end_date
1501 )
1502 select
1503 al.id as assignment_limit_id,
1504 al.limit_type,
1505 2 /* per week */ as limit_subtype_mask,
1506 al.max_weekly_limit as limit_value,
1507 case
1508 when (cl.start_of_week = 0) then
1509 date_add(sc.schedule_on, interval (1 - dayofweek(sc.schedule_on)) day)
1510 else
1511 date_add(sc.schedule_on, interval (0 - weekday(sc.schedule_on)) day)
1512 end as _start_date,
1513 case
1514 when (cl.start_of_week = 0) then
1515 date_add(sc.schedule_on, interval (7 - dayofweek(sc.schedule_on)) day)
1516 else
1517 date_add(sc.schedule_on, interval (6 - weekday(sc.schedule_on)) day)
1518 end as _end_date
1519 from
1520 buffer_schedules_1110845144 as sc
1521 inner join buffer_assignment_limit_call_types_1110845144 as alct
1522 on sc.call_type_id = alct.call_type_id
1523 inner join buffer_assignment_limits_1110845144 as al
1524 on alct.assignment_limit_id = al.id
1525 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1526 and (al.limit_subtypes_mask & 2 /* per week */) > 0
1527 inner join clients as cl
1528 on cl.id = 319
1529 where sc.should_be_evaluated = 1
1530 group by
1531 al.id,
1532 al.limit_type,
1533 al.max_weekly_limit,
1534 _start_date,
1535 _end_date
1536 order by null
1537
1538INFO : (05-31 19:11:40) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query07_10_AL_03
1539 (123.2ms) /* AssignmentLimitsRolling */
1540 insert into buffer_assignment_limit_periods_1110845144 (
1541 assignment_limit_id,
1542 limit_type,
1543 limit_subtype_mask,
1544 limit_value,
1545 start_date,
1546 end_date
1547 )
1548 select
1549 al.id as assignment_limit_id,
1550 al.limit_type,
1551 4 /* rolling */ as limit_subtype_mask,
1552 al.max_rolling_limit as limit_value,
1553 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as _start_date,
1554 date_add(sc.schedule_on, interval (al.max_rolling_period - ns.item_number) day) as _end_date
1555 from
1556 buffer_schedules_1110845144 as sc
1557 inner join buffer_assignment_limit_call_types_1110845144 as alct
1558 on sc.call_type_id = alct.call_type_id
1559 inner join buffer_assignment_limits_1110845144 as al
1560 on alct.assignment_limit_id = al.id
1561 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1562 and (al.limit_subtypes_mask & 4 /* rolling */) > 0
1563 inner join numeric_sequence as ns
1564 on ns.item_number between 1 and al.max_rolling_period
1565 where sc.should_be_evaluated = 1
1566 group by
1567 al.id,
1568 al.limit_type,
1569 al.max_rolling_limit,
1570 _start_date,
1571 _end_date
1572 order by null
1573
1574INFO : (05-31 19:11:40) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query07_10_AL_04
1575 (127.3ms) /* AssignmentLimitsConsecutive */
1576 insert into buffer_assignment_limit_periods_1110845144 (
1577 assignment_limit_id,
1578 limit_type,
1579 limit_subtype_mask,
1580 limit_value,
1581 start_date,
1582 end_date
1583 )
1584 select
1585 al.id as assignment_limit_id,
1586 al.limit_type,
1587 8 /* consecutive */ as limit_subtype_mask,
1588 al.max_consecutive_limit as limit_value,
1589 date_add(sc.schedule_on, interval (1 - ns.item_number) day) as _start_date,
1590 date_add(sc.schedule_on, interval ((al.max_consecutive_limit + 1) - ns.item_number) day) as _end_date
1591 from
1592 buffer_schedules_1110845144 as sc
1593 inner join buffer_assignment_limit_call_types_1110845144 as alct
1594 on sc.call_type_id = alct.call_type_id
1595 inner join buffer_assignment_limits_1110845144 as al
1596 on alct.assignment_limit_id = al.id
1597 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
1598 and (al.limit_subtypes_mask & 8 /* consecutive */) > 0
1599 inner join numeric_sequence as ns
1600 on ns.item_number between 1 and (al.max_consecutive_limit + 1)
1601 where sc.should_be_evaluated = 1
1602 group by
1603 al.id,
1604 al.limit_type,
1605 al.max_consecutive_limit,
1606 _start_date,
1607 _end_date
1608 order by null
1609
1610INFO : (05-31 19:11:40) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query08
1611 (119.9ms) insert into buffer_staff_daily_counts_1110845144 (
1612 staff_id, schedule_on, linkage_instance_id, slots_count
1613 )
1614 select staff_id, schedule_on, null as linkage_instance_id,
1615 count(1) as slots_count
1616 from buffer_schedules_1110845144
1617 where staff_id is not null
1618 and call_type_id > 0 /* VC */
1619 group by staff_id, schedule_on
1620 order by null
1621
1622 (123.6ms) insert into buffer_staff_daily_counts_1110845144 (
1623 staff_id, schedule_on, linkage_instance_id, slots_count
1624 )
1625 select staff_id, schedule_on, linkage_instance_id,
1626 count(1) as slots_count
1627 from buffer_schedules_1110845144
1628 where staff_id is not null
1629 and call_type_id > 0 /* VC */
1630 group by staff_id, schedule_on, linkage_instance_id
1631 order by null
1632
1633INFO : (05-31 19:11:40) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query085
1634 (293.0ms) insert into buffer_staff_specializations_1110845144 (
1635 staff_id, specialization_id
1636 )
1637 select staff_id, specialization_id
1638 from view_staff_specializations
1639 where client_id = 319
1640 order by null
1641
1642 (122.8ms) insert into buffer_specialization_daily_counts_1110845144 (
1643 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1644 )
1645 select sp.id as specialization_id,
1646 null as staff_id,
1647 bs_1.schedule_on,
1648 count(distinct st_sp.staff_id) as staffs_scheduled,
1649 sp.max_scheduled,
1650 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
1651 from (
1652 select schedule_on, day_of_week_mask
1653 from buffer_schedules_1110845144
1654 where should_be_evaluated = 1
1655 group by schedule_on, day_of_week_mask
1656 order by null
1657 ) as bs_1
1658 inner join specializations as sp
1659 on sp.client_id = 319
1660 and sp.active = 1
1661 inner join buffer_schedules_1110845144 as bs_2
1662 on bs_1.schedule_on = bs_2.schedule_on
1663 and bs_2.call_type_id > 0 /* is not VC */
1664 inner join max_applicable_call_types as mact
1665 on sp.id = mact.specialization_id
1666 and bs_2.call_type_id = mact.call_type_id
1667 left join buffer_staff_specializations_1110845144 as st_sp
1668 on bs_2.staff_id = st_sp.staff_id
1669 and sp.id = st_sp.specialization_id
1670 group by sp.id, bs_1.schedule_on, sp.max_scheduled
1671 order by null
1672
1673 (119.9ms) insert into buffer_specialization_daily_counts_1110845144 (
1674 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1675 )
1676 select sp.id as specialization_id,
1677 st_sp.staff_id,
1678 bs_1.schedule_on,
1679 1 as staffs_scheduled,
1680 sp.max_scheduled,
1681 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
1682 from (
1683 select schedule_on, day_of_week_mask
1684 from buffer_schedules_1110845144
1685 where should_be_evaluated = 1
1686 group by schedule_on, day_of_week_mask
1687 order by null
1688 ) as bs_1
1689 inner join specializations as sp
1690 on sp.client_id = 319
1691 and sp.active = 1
1692 inner join buffer_schedules_1110845144 as bs_2
1693 on bs_1.schedule_on = bs_2.schedule_on
1694 and bs_2.call_type_id > 0 /* is not VC */
1695 inner join max_applicable_call_types as mact
1696 on sp.id = mact.specialization_id
1697 and bs_2.call_type_id = mact.call_type_id
1698 inner join buffer_staff_specializations_1110845144 as st_sp
1699 on bs_2.staff_id = st_sp.staff_id
1700 and sp.id = st_sp.specialization_id
1701 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
1702 order by null
1703
1704INFO : (05-31 19:11:41) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query09
1705 (120.8ms) truncate table buffer_location_privileges_1110845144
1706
1707 (128.8ms) insert into buffer_location_privileges_1110845144 (
1708 staff_id,
1709 location_id,
1710 begin_date,
1711 end_date
1712 )
1713 select
1714 s.id as staff_id,
1715 l.location_id,
1716 '0000-00-00' as begin_date,
1717 '9999-12-31' as end_date
1718 from (
1719 select l.id as location_id
1720 from
1721 buffer_schedules_1110845144 as sc
1722 inner join call_types as ct
1723 on sc.call_type_id = ct.id
1724 and ct.active = 1
1725 inner join locations as l
1726 on ct.location_id = l.id
1727 and l.active = 1
1728 where sc.linkage_instance_id >= 0
1729 and sc.should_be_evaluated = 1
1730 group by l.id
1731 order by null
1732 ) as l
1733 inner join staffs as s
1734 on s.client_id = 319
1735 and s.active = 1
1736 and s.take_call = 1
1737 and s.auto_populatable = 1
1738 left join location_privileges as lp
1739 on s.id = lp.staff_id
1740 where lp.id is null
1741 union
1742 select
1743 lp.staff_id,
1744 lp.location_id,
1745 lp.begin_date,
1746 COALESCE(lp.end_date, '9999-12-31')
1747 from (
1748 select l.id as location_id
1749 from
1750 buffer_schedules_1110845144 as sc
1751 inner join call_types as ct
1752 on sc.call_type_id = ct.id
1753 and ct.active = 1
1754 inner join locations as l
1755 on ct.location_id = l.id
1756 and l.active = 1
1757 where sc.linkage_instance_id >= 0
1758 and sc.should_be_evaluated = 1
1759 group by l.id
1760 order by null
1761 ) as l
1762 inner join location_privileges as lp
1763 on l.location_id = lp.location_id
1764 order by null;
1765
1766 (168.6ms) insert into buffer_static_availabilities_1110845144 (
1767 schedule_id,
1768 staff_id,
1769 staff_type_id,
1770 is_primary
1771 )
1772 select
1773 schedules.id as schedule_id,
1774 staffs.id as staff_id,
1775 assign_staff_types.staff_type_id,
1776 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
1777 from
1778 buffer_schedules_1110845144 as schedules
1779 /* AvailabilityRule */
1780 inner join call_types
1781 on schedules.call_type_id = call_types.id
1782 and call_types.active = 1
1783 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
1784 inner join staff_call_types
1785 on call_types.id = staff_call_types.call_type_id
1786 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
1787 inner join staffs
1788 on staff_call_types.staff_id = staffs.id
1789 and staffs.active = 1
1790 and staffs.take_call = 1
1791 and staffs.auto_populatable = 1
1792 inner join buffer_location_privileges_1110845144 as location_privileges
1793 on staffs.id = location_privileges.staff_id
1794 and call_types.location_id = location_privileges.location_id
1795 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
1796 inner join assign_staff_types
1797 on staffs.id = assign_staff_types.staff_id
1798 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
1799 left join eligible_staff_types
1800 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
1801 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
1802 left join staff_rotations
1803 on staffs.id = staff_rotations.staff_id
1804 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
1805 left join daily_rotations
1806 on staffs.id = daily_rotations.staff_id
1807 and schedules.schedule_on = daily_rotations.schedule_on
1808 left join rotations
1809 on rotations.id = ifnull(daily_rotations.rotation_id, staff_rotations.rotation_id)
1810 and rotations.active = 1
1811 left join rotation_call_types
1812 on rotations.id = rotation_call_types.rotation_id
1813 and staff_call_types.call_type_id = rotation_call_types.call_type_id
1814 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
1815 /* RequiredRotationsRule */
1816 left join required_rotations
1817 on staff_call_types.call_type_id = required_rotations.call_type_id
1818 left join staff_rotations as staff_rotations2
1819 on staffs.id = staff_rotations2.staff_id
1820 and required_rotations.rotation_id = staff_rotations2.rotation_id
1821 and schedules.schedule_on >= staff_rotations2.start_date
1822 /* NoCallRequestsHiddenHardRule */
1823 left join request_settings
1824 on assign_staff_types.staff_type_id = request_settings.staff_type_id
1825 left join requests
1826 on schedules.schedule_on = requests.schedule_on
1827 and staffs.id = requests.staff_id
1828 and requests.request_type = 0 /* type = no_call */
1829 and requests.status in (1, 3) /* status = approved or fulfilled */
1830 and call_types.is_no_call_requestable = 1
1831 left join request_call_types
1832 on requests.id = request_call_types.request_id
1833 and schedules.call_type_id = request_call_types.call_type_id
1834 where
1835 schedules.linkage_instance_id >= 0
1836 and schedules.should_be_evaluated = 1
1837 and (
1838 call_types.all_staff_types = 1
1839 or eligible_staff_types.id is not null
1840 )
1841 and (
1842 rotations.id is null
1843 or rotation_call_types.id is not null
1844 )
1845 and (
1846 requests.id is null
1847 or (
1848 requests.all_call_types = 1
1849 and request_settings.call_only_all_call_switch = 1 /* calls only */
1850 and call_types.assignment_type = 1 /* clinical */
1851 )
1852 or (
1853 requests.all_call_types = 0
1854 and request_call_types.id is null
1855 )
1856 )
1857 group by
1858 schedules.id,
1859 staffs.id,
1860 assign_staff_types.staff_type_id,
1861 is_primary
1862 having
1863 sum(if(required_rotations.id is not null and staff_rotations2.id is null, 1, 0)) = 0 /* RequiredRotationsRule */
1864 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
1865 order by null
1866
1867INFO : (05-31 19:11:41) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query092
1868 (122.1ms) /* RotationPostCallRule */
1869 /* Exclude people and slots when a post-call is not allowed on the first day of a rotation */
1870 delete sa
1871 from buffer_static_availabilities_1110845144 as sa
1872 inner join buffer_schedules_1110845144 as sc
1873 on sa.schedule_id = sc.id
1874 inner join call_types as ct
1875 on sc.call_type_id = ct.id
1876 and ct.assignment_type = 0 /* call */
1877 inner join staff_rotations as sr
1878 on sa.staff_id = sr.staff_id
1879 and date_add(sc.schedule_on, interval 1 day) = sr.start_date
1880 inner join rotations as r
1881 on sr.rotation_id = r.id
1882 and r.post_call_allowed = 0
1883
1884INFO : (05-31 19:11:41) --> fill: 34 - Scheduler::AutoPopulate::Queries::Query093
1885INFO : (05-31 19:11:41) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query095
1886 (124.1ms) insert into buffer_negative_relations_1110845144 (
1887 schedule_id,
1888 affected_by_schedule_id,
1889 staff_id
1890 )
1891 select
1892 b_schedules.id as schedule_id,
1893 b_schedules_negative_link.id as affected_by_schedule_id,
1894 b_schedules_negative_link.staff_id
1895 from
1896 buffer_schedules_1110845144 as b_schedules
1897 /* NegativeLinkRule */
1898 inner join negative_links_influences
1899 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1900 and b_schedules.call_type_id = negative_links_influences.source_id
1901 and negative_links_influences.source_type = 'CallType'
1902 and negative_links_influences.target_type = 'CallType'
1903 inner join buffer_schedules_1110845144 as b_schedules_negative_link
1904 on date_add(
1905 b_schedules.schedule_on,
1906 interval negative_links_influences.offset day
1907 ) = b_schedules_negative_link.schedule_on
1908 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
1909 and b_schedules.id != b_schedules_negative_link.id
1910 where
1911 b_schedules.linkage_instance_id >= 0
1912 and b_schedules.should_be_evaluated = 1
1913 order by null
1914
1915 (123.3ms) insert into buffer_negative_relations_1110845144 (
1916 schedule_id,
1917 affected_by_schedule_id,
1918 staff_id
1919 )
1920 select
1921 b_schedules.id as schedule_id,
1922 b_schedules_negative_link.id as affected_by_schedule_id,
1923 b_schedules_negative_link.staff_id
1924 from
1925 buffer_schedules_1110845144 as b_schedules
1926 /* NegativeLinkRule */
1927 inner join negative_links_influences
1928 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1929 and b_schedules.call_type_id = negative_links_influences.source_id
1930 and negative_links_influences.source_type = 'CallType'
1931 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1932 inner join buffer_schedules_1110845144 as b_schedules_negative_link
1933 on date_add(
1934 b_schedules.schedule_on,
1935 interval negative_links_influences.offset day
1936 ) = b_schedules_negative_link.schedule_on
1937 and (-1 * negative_links_influences.target_id) = b_schedules_negative_link.call_type_id
1938 and b_schedules.id != b_schedules_negative_link.id
1939 where
1940 b_schedules.linkage_instance_id >= 0
1941 and b_schedules.should_be_evaluated = 1
1942 order by null
1943
1944 (130.8ms) delete from buffer_schedules_1110845144
1945 where call_type_id < 0 /* vacations and non-clinicals */
1946
1947INFO : (05-31 19:11:42) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query097
1948 (250.4ms) insert into buffer_staff_assigned_days_1110845144 (
1949 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
1950 )
1951 select staffs.staff_id,
1952 if(days.within_period = 1, days.period_offset, null) as period_offset,
1953 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
1954 days.schedule_on,
1955 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
1956 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
1957 from (
1958 select distinct staff_id
1959 from buffer_static_availabilities_1110845144
1960 order by null
1961 ) as staffs
1962 inner join (
1963 select p.period_offset,
1964 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,
1965 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
1966 from buffer_periods_1110845144 as p
1967 inner join (
1968 select 0 as digit union select 1 union select 2 union select 3 union select 4
1969 union select 5 union select 6 union select 7 union select 8 union select 9
1970 ) as digits_1
1971 inner join (
1972 select 0 as digit union select 1 union select 2 union select 3 union select 4
1973 union select 5 union select 6 union select 7 union select 8 union select 9
1974 ) as digits_2
1975 inner join (
1976 select 0 as digit union select 1 union select 2 union select 3 union select 4
1977 union select 5 union select 6 union select 7 union select 8 union select 9
1978 ) as digits_3
1979 where p.calendar_schedule_id = 1381
1980 and p.should_be_evaluated = 1
1981 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
1982 order by null
1983 ) as days
1984 inner join (
1985 select min(start_date) as start_date, max(end_date) as end_date
1986 from buffer_periods_1110845144
1987 where calendar_schedule_id = 1381
1988 and should_be_evaluated = 1
1989 order by null
1990 ) as limits
1991 inner join clients
1992 on clients.id = 319
1993 left join (
1994 select sc.staff_id, sc.schedule_on
1995 from buffer_periods_1110845144 as p
1996 inner join schedules as sc
1997 on sc.client_id = 319
1998 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
1999 inner join call_types as ct
2000 on sc.call_type_id = ct.id
2001 and ct.unassigned_day = 0
2002 where p.calendar_schedule_id = 1381
2003 and p.should_be_evaluated = 1
2004 group by sc.staff_id, sc.schedule_on
2005 order by null
2006 ) as schedules
2007 on staffs.staff_id = schedules.staff_id
2008 and days.schedule_on = schedules.schedule_on
2009 left join (
2010 select v.staff_id, v.schedule_on
2011 from buffer_periods_1110845144 as p
2012 inner join vacations as v
2013 on v.client_id = 319
2014 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
2015 and v.status IN (3)
2016 inner join non_clinical_types as nct
2017 on v.non_clinical_type_id = nct.id
2018 and nct.active = 1
2019 and nct.unassigned_day = 0
2020 where p.calendar_schedule_id = 1381
2021 and p.should_be_evaluated = 1
2022 group by v.staff_id, v.schedule_on
2023 order by null
2024 ) as vacations
2025 on staffs.staff_id = vacations.staff_id
2026 and days.schedule_on = vacations.schedule_on
2027 where days.within_period = 1
2028 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
2029 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
2030 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
2031 )
2032 order by null
2033
2034INFO : (05-31 19:11:42) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query098
2035 (124.4ms) insert into buffer_combined_availabilities_1110845144 (
2036 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
2037 )
2038 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
2039 sc.linkage_instance_id, 0 as has_violations
2040 from buffer_schedules_1110845144 as sc
2041 /* Static availability */
2042 inner join buffer_static_availabilities_1110845144 as sa
2043 on sc.id = sa.schedule_id
2044 inner join buffer_linkages_1110845144 as l
2045 on sc.linkage_instance_id = l.linkage_instance_id
2046 where sc.should_be_evaluated = 1
2047 and l.has_conflicts = 0
2048
2049 (120.4ms) insert into buffer_schedule_linkages_1110845144 (
2050 staff_id, linkage_instance_id, slots_count
2051 )
2052 select staff_id, linkage_instance_id, count(1) as slots_count
2053 from buffer_schedules_1110845144 use index (ix_buffer_schedules_composite_2)
2054 where staff_id is not null
2055 and linkage_instance_id >= 0
2056 group by staff_id, linkage_instance_id
2057 order by null
2058
2059INFO : (05-31 19:11:42) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
2060 (131.4ms) insert into buffer_linkage_assignment_limits_1110845144 (
2061 staff_id,
2062 linkage_instance_id,
2063 assignment_limit_id,
2064 period_id,
2065 consecutive_days_mask,
2066 linkage_value
2067 )
2068 select
2069 ca.staff_id,
2070 ca.linkage_instance_id,
2071 al.id as assignment_limit_id,
2072 alp.id as period_id,
2073 case
2074 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2075 bit_or(1 << datediff(sc.schedule_on, alp.start_date))
2076 else 0
2077 end as consecutive_days_mask,
2078 count(sc.id) as linkage_value
2079 from
2080 buffer_combined_availabilities_1110845144 as ca
2081 inner join buffer_schedules_1110845144 as sc
2082 on ca.schedule_id = sc.id
2083 inner join buffer_assignment_limit_call_types_1110845144 as alct
2084 on sc.call_type_id = alct.call_type_id
2085 inner join buffer_assignment_limits_1110845144 as al
2086 on alct.assignment_limit_id = al.id
2087 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2088 inner join buffer_assignment_limit_staffs_1110845144 as als
2089 on al.id = als.assignment_limit_id
2090 and ca.staff_id = als.staff_id
2091 inner join buffer_assignment_limit_staff_types_1110845144 as alst
2092 on al.id = alst.assignment_limit_id
2093 and ca.staff_type_id = alst.staff_type_id
2094 inner join buffer_assignment_limit_periods_1110845144 as alp
2095 on al.id = alp.assignment_limit_id
2096 and sc.schedule_on between alp.start_date and alp.end_date
2097 group by
2098 ca.staff_id,
2099 ca.linkage_instance_id,
2100 al.id,
2101 alp.id
2102 order by null
2103
2104INFO : (05-31 19:11:42) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
2105 (122.9ms) insert into buffer_staff_assignment_limits_1110845144 (
2106 staff_id,
2107 assignment_limit_id,
2108 period_id,
2109 consecutive_days_mask,
2110 current_value
2111 )
2112 select
2113 ca.staff_id,
2114 al.id as assignment_limit_id,
2115 alp.id as period_id,
2116 case
2117 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2118 bit_or(1 << datediff(sc.schedule_on, alp.start_date))
2119 else 0
2120 end as consecutive_days_mask,
2121 count(sc.id) as current_value
2122 from
2123 (
2124 select
2125 staff_id,
2126 linkage_instance_id
2127 from buffer_combined_availabilities_1110845144
2128 group by
2129 staff_id,
2130 linkage_instance_id
2131 ) as ca
2132 inner join buffer_linkage_assignment_limits_1110845144 as lal
2133 on ca.staff_id = lal.staff_id
2134 and ca.linkage_instance_id = lal.linkage_instance_id
2135 inner join buffer_assignment_limit_staffs_1110845144 as als
2136 on lal.assignment_limit_id = als.assignment_limit_id
2137 and ca.staff_id = als.staff_id
2138 inner join buffer_assignment_limit_periods_1110845144 as alp
2139 on lal.period_id = alp.id
2140 inner join buffer_assignment_limits_1110845144 as al
2141 on lal.assignment_limit_id = al.id
2142 inner join (
2143 select
2144 sc.id,
2145 sc.schedule_on,
2146 (1 << (sc.dayofweek_on - 1)) as day_of_week_mask,
2147 sc.call_type_id,
2148 sc.staff_id
2149 from
2150 schedules as sc
2151 inner join (
2152 select
2153 min(start_date) as start_date,
2154 max(end_date) as end_date
2155 from buffer_assignment_limit_periods_1110845144
2156 ) as alp
2157 on sc.schedule_on between alp.start_date and alp.end_date
2158 where sc.client_id = 319
2159 order by null
2160 ) as sc
2161 on ca.staff_id = sc.staff_id
2162 and sc.schedule_on between alp.start_date and alp.end_date
2163 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2164 inner join buffer_assignment_limit_call_types_1110845144 as alct
2165 on lal.assignment_limit_id = alct.assignment_limit_id
2166 and sc.call_type_id = alct.call_type_id
2167 inner join assign_staff_types as ast
2168 on ca.staff_id = ast.staff_id
2169 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2170 inner join buffer_assignment_limit_staff_types_1110845144 as alst
2171 on lal.assignment_limit_id = alst.assignment_limit_id
2172 and ast.staff_type_id = alst.staff_type_id
2173 group by
2174 ca.staff_id,
2175 al.id,
2176 alp.id
2177 order by null
2178
2179INFO : (05-31 19:11:43) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_12_Points
2180INFO : (05-31 19:11:43) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
2181INFO : (05-31 19:11:43) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query10
2182 (121.7ms) select
2183 coalesce(max(
2184 case
2185 when (ct.max_weekday_assignments > 0) then 1
2186 else 0
2187 end
2188 ), 0) as weekday_flag,
2189 coalesce(max(
2190 case
2191 when (ct.max_weekend_assignments > 0) then 1
2192 else 0
2193 end
2194 ), 0) as weekend_flag
2195 from
2196 buffer_auto_populate_slots_1110845144 as aps
2197 inner join call_types as ct
2198 on aps.call_type_id = ct.id
2199
2200 (125.0ms) /* NegativeLinkRule */
2201 delete ca
2202 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
2203 inner join buffer_negative_relations_1110845144 as nr use index (ix_buffer_negative_relations_composite_1)
2204 on ca.schedule_id = nr.schedule_id
2205 and ca.staff_id = nr.staff_id
2206
2207 (122.8ms) /* SpecializationOverlapRule */
2208 delete ca
2209 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
2210 inner join buffer_schedules_1110845144 as sc
2211 on ca.schedule_id = sc.id
2212 inner join buffer_staff_specializations_1110845144 as ssp
2213 on ca.staff_id = ssp.staff_id
2214 inner join max_applicable_call_types as mact
2215 on ssp.specialization_id = mact.specialization_id
2216 and sc.call_type_id = mact.call_type_id
2217 inner join buffer_specialization_daily_counts_1110845144 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2218 on ssp.specialization_id = sdc.specialization_id
2219 and sc.schedule_on = sdc.schedule_on
2220 and sdc.staff_id is null
2221 left join buffer_specialization_daily_counts_1110845144 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2222 on ssp.specialization_id = sdc_2.specialization_id
2223 and sc.schedule_on = sdc_2.schedule_on
2224 and ca.staff_id = sdc_2.staff_id
2225 where sdc.should_be_evaluated = 1
2226 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2227
2228 (121.9ms) /* BrokenLinkageRule */
2229 truncate table buffer_combined_availability_linkages_1110845144
2230
2231 (126.4ms) insert into buffer_combined_availability_linkages_1110845144 (
2232 staff_id,
2233 linkage_instance_id,
2234 slots_count,
2235 is_primary,
2236 has_violations
2237 )
2238 select
2239 staff_id,
2240 linkage_instance_id,
2241 count(1) as slots_count,
2242 min(is_primary) as is_primary,
2243 max(has_violations) as has_violations
2244 from buffer_combined_availabilities_1110845144 use index (ix_buffer_combined_availabilities_composite_2)
2245 group by
2246 staff_id,
2247 linkage_instance_id
2248 order by null
2249
2250 (124.3ms) delete ca
2251 from
2252 buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
2253 inner join buffer_schedules_1110845144 as sc
2254 on ca.schedule_id = sc.id
2255 inner join buffer_linkages_1110845144 as l
2256 on sc.linkage_instance_id = l.linkage_instance_id
2257 inner join buffer_combined_availability_linkages_1110845144 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2258 on ca.staff_id = ls_1.staff_id
2259 and sc.linkage_instance_id = ls_1.linkage_instance_id
2260 left join buffer_schedule_linkages_1110845144 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2261 on ca.staff_id = ls_2.staff_id
2262 and sc.linkage_instance_id = ls_2.linkage_instance_id
2263 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2264
2265INFO : (05-31 19:11:43) --> fill: 43 - Scheduler::AutoPopulate::Queries::QueryScore02
2266INFO : (05-31 19:11:43) --> fill: 44 - Scheduler::AutoPopulate::Queries::QueryScore03
2267INFO : (05-31 19:11:43) --> fill: 45 - Scheduler::AutoPopulate::Queries::QueryScore04
2268INFO : (05-31 19:11:43) --> fill: 46 - Scheduler::AutoPopulate::Queries::Query11
2269 (123.7ms) update buffer_schedules_1110845144 as sc
2270 left join buffer_combined_availabilities_1110845144 as ca
2271 on sc.id = ca.schedule_id
2272 set sc.should_be_evaluated = 0,
2273 sc.should_be_populated = 0
2274 where sc.should_be_evaluated = 1
2275 and ca.id is null
2276
2277INFO : (05-31 19:11:43) --> fill: 47 - Scheduler::AutoPopulate::Queries::DropTables
2278INFO : (05-31 19:11:43) Setting up lists...
2279 (124.0ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
2280 from buffer_schedules_1110845144 as b_schedules
2281 left join schedules
2282 on schedules.client_id = 319
2283 and schedules.call_type_id = b_schedules.call_type_id
2284 and schedules.schedule_on = b_schedules.schedule_on
2285 where b_schedules.linkage_instance_id != -1
2286 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
2287
2288INFO : (05-31 19:11:44) Begin to populate. Pre-set: 0. Unscheduled: 88.
2289INFO : (05-31 19:11:44) plan unit: [id:201826] [2018-07-01 - OB], [2018-07-02 - OB]
2290INFO : (05-31 19:11:44) plan unit: [id:201827] [2018-07-08 - OB], [2018-07-09 - OB]
2291INFO : (05-31 19:11:44) plan unit: [id:201828] [2018-07-15 - OB], [2018-07-16 - OB]
2292INFO : (05-31 19:11:44) plan unit: [id:201829] [2018-07-22 - OB], [2018-07-23 - OB]
2293INFO : (05-31 19:11:44) plan unit: [id:201830] [2018-07-29 - OB], [2018-07-30 - OB]
2294INFO : (05-31 19:11:44) plan unit: [id:1201826] [2018-07-01 - OR-S]
2295INFO : (05-31 19:11:44) plan unit: [id:1201827] [2018-07-08 - OR-S]
2296INFO : (05-31 19:11:44) plan unit: [id:1201828] [2018-07-15 - OR-S]
2297INFO : (05-31 19:11:44) plan unit: [id:1201829] [2018-07-22 - OR-S]
2298INFO : (05-31 19:11:44) plan unit: [id:1201830] [2018-07-29 - OR-S]
2299INFO : (05-31 19:11:44) plan unit: [id:2201826] [2018-07-01 - BU]
2300INFO : (05-31 19:11:44) plan unit: [id:2201827] [2018-07-08 - BU]
2301INFO : (05-31 19:11:44) plan unit: [id:2201828] [2018-07-15 - BU]
2302INFO : (05-31 19:11:44) plan unit: [id:2201829] [2018-07-22 - BU]
2303INFO : (05-31 19:11:44) plan unit: [id:2201830] [2018-07-29 - BU]
2304INFO : (05-31 19:11:44) plan unit: [id:5201826] [2018-07-02 - OR-S]
2305INFO : (05-31 19:11:44) plan unit: [id:5201827] [2018-07-09 - OR-S]
2306INFO : (05-31 19:11:44) plan unit: [id:5201828] [2018-07-16 - OR-S]
2307INFO : (05-31 19:11:44) plan unit: [id:5201829] [2018-07-23 - OR-S]
2308INFO : (05-31 19:11:44) plan unit: [id:5201830] [2018-07-30 - OR-S]
2309INFO : (05-31 19:11:44) plan unit: [id:6201826] [2018-07-02 - BU]
2310INFO : (05-31 19:11:44) plan unit: [id:6201827] [2018-07-09 - BU]
2311INFO : (05-31 19:11:44) plan unit: [id:6201828] [2018-07-16 - BU]
2312INFO : (05-31 19:11:44) plan unit: [id:6201829] [2018-07-23 - BU]
2313INFO : (05-31 19:11:44) plan unit: [id:6201830] [2018-07-30 - BU]
2314INFO : (05-31 19:11:44) plan unit: [id:9201826] [2018-07-03 - OB]
2315INFO : (05-31 19:11:44) plan unit: [id:9201827] [2018-07-10 - OB]
2316INFO : (05-31 19:11:44) plan unit: [id:9201828] [2018-07-17 - OB]
2317INFO : (05-31 19:11:44) plan unit: [id:9201829] [2018-07-24 - OB]
2318INFO : (05-31 19:11:44) plan unit: [id:9201830] [2018-07-31 - OB]
2319INFO : (05-31 19:11:44) plan unit: [id:10201826] [2018-07-03 - OR-S]
2320INFO : (05-31 19:11:44) plan unit: [id:10201827] [2018-07-10 - OR-S]
2321INFO : (05-31 19:11:44) plan unit: [id:10201828] [2018-07-17 - OR-S]
2322INFO : (05-31 19:11:44) plan unit: [id:10201829] [2018-07-24 - OR-S]
2323INFO : (05-31 19:11:44) plan unit: [id:10201830] [2018-07-31 - OR-S]
2324INFO : (05-31 19:11:44) plan unit: [id:11201826] [2018-07-03 - BU]
2325INFO : (05-31 19:11:44) plan unit: [id:11201827] [2018-07-10 - BU]
2326INFO : (05-31 19:11:44) plan unit: [id:11201828] [2018-07-17 - BU]
2327INFO : (05-31 19:11:44) plan unit: [id:11201829] [2018-07-24 - BU]
2328INFO : (05-31 19:11:44) plan unit: [id:11201830] [2018-07-31 - BU]
2329INFO : (05-31 19:11:44) plan unit: [id:14201826] [2018-07-04 - OB]
2330INFO : (05-31 19:11:44) plan unit: [id:14201827] [2018-07-11 - OB]
2331INFO : (05-31 19:11:44) plan unit: [id:14201828] [2018-07-18 - OB]
2332INFO : (05-31 19:11:44) plan unit: [id:14201829] [2018-07-25 - OB]
2333INFO : (05-31 19:11:44) plan unit: [id:15201826] [2018-07-04 - OR-S]
2334INFO : (05-31 19:11:44) plan unit: [id:15201827] [2018-07-11 - OR-S]
2335INFO : (05-31 19:11:44) plan unit: [id:15201828] [2018-07-18 - OR-S]
2336INFO : (05-31 19:11:44) plan unit: [id:15201829] [2018-07-25 - OR-S]
2337INFO : (05-31 19:11:44) plan unit: [id:16201826] [2018-07-04 - BU]
2338INFO : (05-31 19:11:44) plan unit: [id:16201827] [2018-07-11 - BU]
2339INFO : (05-31 19:11:44) plan unit: [id:16201828] [2018-07-18 - BU]
2340INFO : (05-31 19:11:44) plan unit: [id:16201829] [2018-07-25 - BU]
2341INFO : (05-31 19:11:44) plan unit: [id:19201826] [2018-07-05 - OB]
2342INFO : (05-31 19:11:44) plan unit: [id:19201827] [2018-07-12 - OB]
2343INFO : (05-31 19:11:44) plan unit: [id:19201828] [2018-07-19 - OB]
2344INFO : (05-31 19:11:44) plan unit: [id:19201829] [2018-07-26 - OB]
2345INFO : (05-31 19:11:44) plan unit: [id:20201826] [2018-07-05 - OR-S]
2346INFO : (05-31 19:11:44) plan unit: [id:20201827] [2018-07-12 - OR-S]
2347INFO : (05-31 19:11:44) plan unit: [id:20201828] [2018-07-19 - OR-S]
2348INFO : (05-31 19:11:44) plan unit: [id:20201829] [2018-07-26 - OR-S]
2349INFO : (05-31 19:11:44) plan unit: [id:21201826] [2018-07-05 - BU]
2350INFO : (05-31 19:11:44) plan unit: [id:21201827] [2018-07-12 - BU]
2351INFO : (05-31 19:11:44) plan unit: [id:21201828] [2018-07-19 - BU]
2352INFO : (05-31 19:11:44) plan unit: [id:21201829] [2018-07-26 - BU]
2353INFO : (05-31 19:11:44) plan unit: [id:24201826] [2018-07-06 - OB]
2354INFO : (05-31 19:11:44) plan unit: [id:24201827] [2018-07-13 - OB]
2355INFO : (05-31 19:11:44) plan unit: [id:24201828] [2018-07-20 - OB]
2356INFO : (05-31 19:11:44) plan unit: [id:24201829] [2018-07-27 - OB]
2357INFO : (05-31 19:11:44) plan unit: [id:25201826] [2018-07-06 - OR-S]
2358INFO : (05-31 19:11:44) plan unit: [id:25201827] [2018-07-13 - OR-S]
2359INFO : (05-31 19:11:44) plan unit: [id:25201828] [2018-07-20 - OR-S]
2360INFO : (05-31 19:11:44) plan unit: [id:25201829] [2018-07-27 - OR-S]
2361INFO : (05-31 19:11:44) plan unit: [id:26201826] [2018-07-06 - BU]
2362INFO : (05-31 19:11:44) plan unit: [id:26201827] [2018-07-13 - BU]
2363INFO : (05-31 19:11:44) plan unit: [id:26201828] [2018-07-20 - BU]
2364INFO : (05-31 19:11:44) plan unit: [id:26201829] [2018-07-27 - BU]
2365INFO : (05-31 19:11:44) plan unit: [id:29201826] [2018-07-07 - OB]
2366INFO : (05-31 19:11:44) plan unit: [id:29201827] [2018-07-14 - OB]
2367INFO : (05-31 19:11:44) plan unit: [id:29201828] [2018-07-21 - OB]
2368INFO : (05-31 19:11:44) plan unit: [id:29201829] [2018-07-28 - OB]
2369INFO : (05-31 19:11:44) plan unit: [id:30201826] [2018-07-07 - OR-S]
2370INFO : (05-31 19:11:44) plan unit: [id:30201827] [2018-07-14 - OR-S]
2371INFO : (05-31 19:11:44) plan unit: [id:30201828] [2018-07-21 - OR-S]
2372INFO : (05-31 19:11:44) plan unit: [id:30201829] [2018-07-28 - OR-S]
2373INFO : (05-31 19:11:44) plan unit: [id:31201826] [2018-07-07 - BU]
2374INFO : (05-31 19:11:44) plan unit: [id:31201827] [2018-07-14 - BU]
2375INFO : (05-31 19:11:44) plan unit: [id:31201828] [2018-07-21 - BU]
2376INFO : (05-31 19:11:44) plan unit: [id:31201829] [2018-07-28 - BU]
2377 (120.8ms) select
2378 coalesce(max(
2379 case
2380 when (ct.max_weekday_assignments > 0) then 1
2381 else 0
2382 end
2383 ), 0) as weekday_flag,
2384 coalesce(max(
2385 case
2386 when (ct.max_weekend_assignments > 0) then 1
2387 else 0
2388 end
2389 ), 0) as weekend_flag
2390 from
2391 buffer_auto_populate_slots_1110845144 as aps
2392 inner join call_types as ct
2393 on aps.call_type_id = ct.id
2394
2395 (119.6ms) /* NegativeLinkRule */
2396 delete ca
2397 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
2398 inner join buffer_negative_relations_1110845144 as nr use index (ix_buffer_negative_relations_composite_1)
2399 on ca.schedule_id = nr.schedule_id
2400 and ca.staff_id = nr.staff_id
2401
2402 (120.1ms) /* SpecializationOverlapRule */
2403 delete ca
2404 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
2405 inner join buffer_schedules_1110845144 as sc
2406 on ca.schedule_id = sc.id
2407 inner join buffer_staff_specializations_1110845144 as ssp
2408 on ca.staff_id = ssp.staff_id
2409 inner join max_applicable_call_types as mact
2410 on ssp.specialization_id = mact.specialization_id
2411 and sc.call_type_id = mact.call_type_id
2412 inner join buffer_specialization_daily_counts_1110845144 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2413 on ssp.specialization_id = sdc.specialization_id
2414 and sc.schedule_on = sdc.schedule_on
2415 and sdc.staff_id is null
2416 left join buffer_specialization_daily_counts_1110845144 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2417 on ssp.specialization_id = sdc_2.specialization_id
2418 and sc.schedule_on = sdc_2.schedule_on
2419 and ca.staff_id = sdc_2.staff_id
2420 where sdc.should_be_evaluated = 1
2421 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2422
2423 (124.8ms) /* BrokenLinkageRule */
2424 truncate table buffer_combined_availability_linkages_1110845144
2425
2426 (123.4ms) insert into buffer_combined_availability_linkages_1110845144 (
2427 staff_id,
2428 linkage_instance_id,
2429 slots_count,
2430 is_primary,
2431 has_violations
2432 )
2433 select
2434 staff_id,
2435 linkage_instance_id,
2436 count(1) as slots_count,
2437 min(is_primary) as is_primary,
2438 max(has_violations) as has_violations
2439 from buffer_combined_availabilities_1110845144 use index (ix_buffer_combined_availabilities_composite_2)
2440 group by
2441 staff_id,
2442 linkage_instance_id
2443 order by null
2444
2445 (121.4ms) delete ca
2446 from
2447 buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
2448 inner join buffer_schedules_1110845144 as sc
2449 on ca.schedule_id = sc.id
2450 inner join buffer_linkages_1110845144 as l
2451 on sc.linkage_instance_id = l.linkage_instance_id
2452 inner join buffer_combined_availability_linkages_1110845144 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2453 on ca.staff_id = ls_1.staff_id
2454 and sc.linkage_instance_id = ls_1.linkage_instance_id
2455 left join buffer_schedule_linkages_1110845144 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2456 on ca.staff_id = ls_2.staff_id
2457 and sc.linkage_instance_id = ls_2.linkage_instance_id
2458 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2459
2460 (124.7ms) select straight_join
2461 sc.schedule_on,
2462 sc.call_type_id,
2463 count(distinct ca.staff_id) as available_members_count,
2464 group_concat(
2465 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
2466 order by ls_1.is_primary desc, ca.staff_id
2467 separator ', '
2468 ) as available_members_ids,
2469 sc.linkage_instance_id,
2470 l.slots_count_total as linkage_slots_count
2471 from buffer_combined_availabilities_1110845144 as ca
2472 inner join buffer_schedules_1110845144 as sc
2473 on ca.schedule_id = sc.id
2474 inner join buffer_linkages_1110845144 as l
2475 on sc.linkage_instance_id = l.linkage_instance_id
2476 inner join buffer_combined_availability_linkages_1110845144 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2477 on ca.staff_id = ls_1.staff_id
2478 and sc.linkage_instance_id = ls_1.linkage_instance_id
2479 and ls_1.has_violations = 0
2480 where sc.should_be_evaluated = 1
2481 and sc.should_be_populated = 1
2482 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
2483 order by available_members_count, sc.schedule_on, sc.display_order
2484 limit 1
2485
2486 (123.3ms) truncate table buffer_rule_scores_1110845144
2487
2488 (122.5ms) truncate table buffer_rule_calc_filters_1110845144
2489
2490 (125.3ms) insert into buffer_rule_calc_filters_1110845144 (
2491 linkage_instance_id,
2492 staff_id
2493 ) values (201826, 20704)
2494
2495 (122.9ms) truncate table buffer_rule_scores_with_corrections_1110845144
2496
2497 (121.1ms) insert into buffer_rule_scores_with_corrections_1110845144 (
2498 linkage_instance_id, schedule_id, staff_id, rule_name, value
2499 )
2500 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2501 ifnull(rule_scores.value, max_values.max_value + 1) as value
2502 from buffer_rule_scores_1110845144 as rule_scores
2503 inner join (
2504 select rule_name, max(value) as max_value
2505 from buffer_rule_scores_1110845144
2506 group by rule_name
2507 order by null
2508 ) as max_values
2509 on rule_scores.rule_name = max_values.rule_name
2510 order by null
2511
2512 (122.7ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2513 from (
2514 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2515 sum(
2516 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2517 rules.weight *
2518 total_values.slots_count
2519 ) as slot_scores
2520 from buffer_rule_scores_with_corrections_1110845144 as rule_scores
2521 inner join (
2522 select rule_name, sum(abs(value)) as total_value,
2523 count(distinct schedule_id) as slots_count
2524 from buffer_rule_scores_with_corrections_1110845144
2525 group by rule_name
2526 order by null
2527 ) as total_values
2528 on rule_scores.rule_name = total_values.rule_name
2529 inner join rules
2530 on rule_scores.rule_name = rules.type
2531 where rules.rule_set_id = 1461
2532 and rules.applied = 1
2533 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2534 order by null
2535 ) as staff_scores
2536 group by linkage_instance_id, staff_id
2537 order by round(avg(slot_scores), 5) desc, rand()
2538 limit 1
2539
2540WARN : (05-31 19:11:45) No score found. Using fake score...
2541 PublishedBlock Load (122.7ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 319 AND ('2018-07-01' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1381 ORDER BY `published_blocks`.`id` ASC LIMIT 1
2542 (120.6ms) BEGIN
2543 (125.2ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
2544 FROM call_types AS ct
2545 INNER JOIN calendar_schedules AS cs
2546 ON ct.calendar_schedule_id = cs.id
2547 INNER JOIN blocks AS b
2548 ON b.client_id = ct.client_id
2549 AND b.block_schedule_id = cs.block_schedule_id
2550 AND '2018-07-01' BETWEEN b.start_date AND b.end_date
2551 INNER JOIN published_blocks AS pb
2552 ON pb.client_id = ct.client_id
2553 AND pb.block_id = b.id
2554 AND pb.calendar_schedule_id = cs.id
2555 LEFT JOIN published_block_call_types AS pbct
2556 ON pbct.client_id = ct.client_id
2557 AND pbct.assignment_id = ct.id
2558 AND pbct.assignment_type = 'CallType'
2559 AND pbct.block_id = b.id
2560 AND (
2561 (1 << (DAYOFWEEK('2018-07-01') - 1) & pbct.days_of_week_mask) > 0
2562 )
2563 WHERE ct.id = 12692
2564 AND (
2565 (
2566 pb.assignment_ids IS NULL
2567 AND (1 << (DAYOFWEEK('2018-07-01') - 1) & pb.days_of_week_mask) > 0
2568 )
2569 OR pbct.id IS NOT NULL
2570 )
2571
2572 CallType Load (121.6ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
2573 Client Load (121.8ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
2574 Staff Load (119.4ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20704 LIMIT 1
2575 Schedule Exists (122.1ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12692 AND `schedules`.`client_id` = 319 AND `schedules`.`schedule_on` = '2018-07-01' LIMIT 1
2576 SQL (139.3ms) INSERT INTO `schedules` (`client_id`, `call_type_id`, `staff_id`, `schedule_on`, `created_at`, `updated_at`, `weekend_on`, `dayofweek_on`, `populate_method`, `creator_id`) VALUES (319, 12692, 20704, '2018-07-01', '2018-05-31 19:11:48', '2018-05-31 19:11:48', 1, 1, 'auto_populate_step', 166)
2577 VisibleAssignment Load (122.4ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 319 AND `visible_assignments`.`date` = '2018-07-01' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12692 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
2578 (123.4ms) COMMIT
2579FLUSHED: nil:nil
2580 PublishedBlock Load (121.1ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 319 AND ('2018-07-02' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1381 ORDER BY `published_blocks`.`id` ASC LIMIT 1
2581 (122.7ms) BEGIN
2582 (125.4ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
2583 FROM call_types AS ct
2584 INNER JOIN calendar_schedules AS cs
2585 ON ct.calendar_schedule_id = cs.id
2586 INNER JOIN blocks AS b
2587 ON b.client_id = ct.client_id
2588 AND b.block_schedule_id = cs.block_schedule_id
2589 AND '2018-07-02' BETWEEN b.start_date AND b.end_date
2590 INNER JOIN published_blocks AS pb
2591 ON pb.client_id = ct.client_id
2592 AND pb.block_id = b.id
2593 AND pb.calendar_schedule_id = cs.id
2594 LEFT JOIN published_block_call_types AS pbct
2595 ON pbct.client_id = ct.client_id
2596 AND pbct.assignment_id = ct.id
2597 AND pbct.assignment_type = 'CallType'
2598 AND pbct.block_id = b.id
2599 AND (
2600 (1 << (DAYOFWEEK('2018-07-02') - 1) & pbct.days_of_week_mask) > 0
2601 )
2602 WHERE ct.id = 12692
2603 AND (
2604 (
2605 pb.assignment_ids IS NULL
2606 AND (1 << (DAYOFWEEK('2018-07-02') - 1) & pb.days_of_week_mask) > 0
2607 )
2608 OR pbct.id IS NOT NULL
2609 )
2610
2611 CallType Load (121.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12692 LIMIT 1
2612 Client Load (121.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
2613 Staff Load (125.4ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20704 LIMIT 1
2614 Schedule Exists (159.6ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12692 AND `schedules`.`client_id` = 319 AND `schedules`.`schedule_on` = '2018-07-02' LIMIT 1
2615 SQL (126.9ms) INSERT INTO `schedules` (`client_id`, `call_type_id`, `staff_id`, `schedule_on`, `created_at`, `updated_at`, `weekend_on`, `dayofweek_on`, `populate_method`, `creator_id`) VALUES (319, 12692, 20704, '2018-07-02', '2018-05-31 19:11:49', '2018-05-31 19:11:49', 0, 2, 'auto_populate_step', 166)
2616 VisibleAssignment Load (133.9ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 319 AND `visible_assignments`.`date` = '2018-07-02' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12692 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
2617 (122.1ms) COMMIT
2618FLUSHED: nil:nil
2619 (134.4ms) UPDATE buffer_schedules_1110845144
2620 SET staff_id = 20704,
2621 should_be_populated = 0,
2622 should_be_evaluated = 0
2623 WHERE
2624 call_type_id = 12692
2625 AND schedule_on = '2018-07-01'
2626
2627 (124.6ms) delete from buffer_combined_availabilities_1110845144
2628 where linkage_instance_id = 201826
2629
2630 (116.0ms) UPDATE buffer_rule_calc_schedules_1110845144
2631 SET staff_id = 20704
2632 WHERE call_type_id = 12692
2633 AND schedule_on = '2018-07-01'
2634
2635 (126.5ms) INSERT INTO buffer_rule_calc_schedules_1110845144
2636 SET staff_id = 20704,
2637 call_type_id = 12692,
2638 schedule_on = '2018-07-01',
2639 day_of_week_mask = 1
2640
2641 (120.7ms) /* NegativeLinkRule */
2642 UPDATE buffer_negative_relations_1110845144
2643 SET staff_id = 20704
2644 WHERE affected_by_schedule_id = (
2645 SELECT id
2646 FROM buffer_schedules_1110845144
2647 WHERE schedule_on = '2018-07-01'
2648 AND call_type_id = 12692
2649 )
2650
2651 (122.6ms) /* SpecializationOverlapRule */
2652 update buffer_staff_specializations_1110845144 as st_sp
2653 inner join max_applicable_call_types as mact
2654 on st_sp.specialization_id = mact.specialization_id
2655 and mact.call_type_id = 12692
2656 inner join buffer_specialization_daily_counts_1110845144 as sp_dc
2657 on st_sp.specialization_id = sp_dc.specialization_id
2658 and sp_dc.staff_id is null
2659 and sp_dc.schedule_on = '2018-07-01'
2660 left join buffer_specialization_daily_counts_1110845144 as sp_dc_2
2661 on st_sp.specialization_id = sp_dc_2.specialization_id
2662 and st_sp.staff_id = sp_dc_2.staff_id
2663 and sp_dc_2.schedule_on = '2018-07-01'
2664 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
2665 where st_sp.staff_id = 20704
2666 and sp_dc_2.id is null
2667
2668 (119.9ms) insert into buffer_specialization_daily_counts_1110845144 (
2669 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2670 )
2671 select sp.id as specialization_id,
2672 20704 as staff_id,
2673 '2018-07-01' as schedule_on,
2674 1 as staffs_scheduled,
2675 sp.max_scheduled,
2676 sp_dc.should_be_evaluated
2677 from buffer_staff_specializations_1110845144 as st_sp
2678 inner join specializations as sp
2679 on st_sp.specialization_id = sp.id
2680 inner join max_applicable_call_types as mact
2681 on st_sp.specialization_id = mact.specialization_id
2682 and mact.call_type_id = 12692
2683 inner join buffer_specialization_daily_counts_1110845144 as sp_dc
2684 on st_sp.specialization_id = sp_dc.specialization_id
2685 and sp_dc.staff_id is null
2686 and sp_dc.schedule_on = '2018-07-01'
2687 left join buffer_specialization_daily_counts_1110845144 as sp_dc_2
2688 on st_sp.specialization_id = sp_dc_2.specialization_id
2689 and st_sp.staff_id = sp_dc_2.staff_id
2690 and sp_dc_2.schedule_on = '2018-07-01'
2691 where st_sp.staff_id = 20704
2692 and sp_dc_2.id is null
2693
2694 (123.6ms) /* MaxAssignmentsRule */
2695 delete from buffer_linkage_call_type_assignments_1110845144
2696 where linkage_instance_id = 201826;
2697
2698 (121.8ms) /* MaxAssignmentsRule */
2699 update
2700 buffer_schedules_1110845144 as sc
2701 inner join call_types as ct
2702 on sc.call_type_id = ct.id
2703 and ct.active = 1
2704 inner join buffer_call_type_assignments_1110845144 as cta
2705 on sc.staff_id = cta.staff_id
2706 and sc.call_type_id = cta.call_type_id
2707 and sc.is_weekend = cta.is_weekend
2708 and sc.period_offset = cta.period_offset
2709 and sc.calendar_schedule_id = cta.calendar_schedule_id
2710 set cta.assignments_count = cta.assignments_count + 1
2711 where
2712 sc.schedule_on = '2018-07-01'
2713 and sc.call_type_id = 12692
2714 and sc.staff_id = 20704
2715
2716 (121.5ms) insert into buffer_call_type_assignments_1110845144 (
2717 staff_id,
2718 call_type_id,
2719 is_weekend,
2720 period_offset,
2721 calendar_schedule_id,
2722 assignments_count
2723 )
2724 select
2725 sc.staff_id,
2726 sc.call_type_id,
2727 sc.is_weekend,
2728 sc.period_offset,
2729 sc.calendar_schedule_id,
2730 1 as assignments_count
2731 from buffer_schedules_1110845144 as sc
2732 where
2733 sc.staff_id = 20704
2734 and sc.schedule_on = '2018-07-01'
2735 and sc.call_type_id = 12692
2736
2737 (120.2ms) /* UnassignedDaysRule */
2738 delete from buffer_linkage_assigned_days_1110845144
2739 where linkage_instance_id = 201826
2740
2741 (124.3ms) /* UnassignedDaysRule */
2742 UPDATE buffer_staff_assigned_days_1110845144 as sad
2743 inner join call_types as ct
2744 on ct.id = 12692
2745 and ct.unassigned_day = 0
2746 SET sad.is_assigned = 1
2747 WHERE sad.staff_id = 20704
2748 AND sad.schedule_on = '2018-07-01'
2749
2750 (123.6ms) delete
2751 from buffer_linkage_assignment_limits_1110845144
2752 where
2753 staff_id = 20704
2754 and linkage_instance_id = 201826
2755
2756 (124.9ms) /* AssignmentLimitsPerPeriod */
2757 /* AssignmentLimitsPerWeek */
2758 /* AssignmentLimitsRolling */
2759 /* AssignmentLimitsConsecutive */
2760
2761 insert into buffer_staff_assignment_limits_1110845144 (
2762 staff_id,
2763 assignment_limit_id,
2764 period_id,
2765 consecutive_days_mask,
2766 current_value
2767 )
2768 select
2769 sc.staff_id,
2770 al.id as assignment_limit_id,
2771 alp.id as period_id,
2772 case
2773 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2774 (1 << datediff(sc.schedule_on, alp.start_date))
2775 else 0
2776 end as consecutive_days_mask,
2777 1.0 as current_value
2778 from
2779 buffer_schedules_1110845144 as sc
2780 inner join buffer_assignment_limit_call_types_1110845144 as alct
2781 on sc.call_type_id = alct.call_type_id
2782 inner join buffer_assignment_limits_1110845144 as al
2783 on alct.assignment_limit_id = al.id
2784 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2785 inner join buffer_assignment_limit_staffs_1110845144 as als
2786 on al.id = als.assignment_limit_id
2787 and sc.staff_id = als.staff_id
2788 inner join assign_staff_types as ast
2789 on sc.staff_id = ast.staff_id
2790 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2791 inner join buffer_assignment_limit_staff_types_1110845144 as alst
2792 on al.id = alst.assignment_limit_id
2793 and ast.staff_type_id = alst.staff_type_id
2794 inner join buffer_assignment_limit_periods_1110845144 as alp
2795 on al.id = alp.assignment_limit_id
2796 and sc.schedule_on between alp.start_date and alp.end_date
2797 where
2798 sc.schedule_on = '2018-07-01'
2799 and sc.call_type_id = 12692
2800 and sc.staff_id = 20704
2801 on duplicate key update
2802 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
2803 current_value = current_value + values(current_value)
2804
2805 (122.2ms) UPDATE buffer_schedules_1110845144
2806 SET staff_id = 20704,
2807 should_be_populated = 0,
2808 should_be_evaluated = 0
2809 WHERE
2810 call_type_id = 12692
2811 AND schedule_on = '2018-07-02'
2812
2813 (128.6ms) delete from buffer_combined_availabilities_1110845144
2814 where linkage_instance_id = 201826
2815
2816 (124.9ms) UPDATE buffer_rule_calc_schedules_1110845144
2817 SET staff_id = 20704
2818 WHERE call_type_id = 12692
2819 AND schedule_on = '2018-07-02'
2820
2821 (127.5ms) INSERT INTO buffer_rule_calc_schedules_1110845144
2822 SET staff_id = 20704,
2823 call_type_id = 12692,
2824 schedule_on = '2018-07-02',
2825 day_of_week_mask = 2
2826
2827 (120.3ms) /* NegativeLinkRule */
2828 UPDATE buffer_negative_relations_1110845144
2829 SET staff_id = 20704
2830 WHERE affected_by_schedule_id = (
2831 SELECT id
2832 FROM buffer_schedules_1110845144
2833 WHERE schedule_on = '2018-07-02'
2834 AND call_type_id = 12692
2835 )
2836
2837 (122.0ms) /* SpecializationOverlapRule */
2838 update buffer_staff_specializations_1110845144 as st_sp
2839 inner join max_applicable_call_types as mact
2840 on st_sp.specialization_id = mact.specialization_id
2841 and mact.call_type_id = 12692
2842 inner join buffer_specialization_daily_counts_1110845144 as sp_dc
2843 on st_sp.specialization_id = sp_dc.specialization_id
2844 and sp_dc.staff_id is null
2845 and sp_dc.schedule_on = '2018-07-02'
2846 left join buffer_specialization_daily_counts_1110845144 as sp_dc_2
2847 on st_sp.specialization_id = sp_dc_2.specialization_id
2848 and st_sp.staff_id = sp_dc_2.staff_id
2849 and sp_dc_2.schedule_on = '2018-07-02'
2850 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
2851 where st_sp.staff_id = 20704
2852 and sp_dc_2.id is null
2853
2854 (123.6ms) insert into buffer_specialization_daily_counts_1110845144 (
2855 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2856 )
2857 select sp.id as specialization_id,
2858 20704 as staff_id,
2859 '2018-07-02' as schedule_on,
2860 1 as staffs_scheduled,
2861 sp.max_scheduled,
2862 sp_dc.should_be_evaluated
2863 from buffer_staff_specializations_1110845144 as st_sp
2864 inner join specializations as sp
2865 on st_sp.specialization_id = sp.id
2866 inner join max_applicable_call_types as mact
2867 on st_sp.specialization_id = mact.specialization_id
2868 and mact.call_type_id = 12692
2869 inner join buffer_specialization_daily_counts_1110845144 as sp_dc
2870 on st_sp.specialization_id = sp_dc.specialization_id
2871 and sp_dc.staff_id is null
2872 and sp_dc.schedule_on = '2018-07-02'
2873 left join buffer_specialization_daily_counts_1110845144 as sp_dc_2
2874 on st_sp.specialization_id = sp_dc_2.specialization_id
2875 and st_sp.staff_id = sp_dc_2.staff_id
2876 and sp_dc_2.schedule_on = '2018-07-02'
2877 where st_sp.staff_id = 20704
2878 and sp_dc_2.id is null
2879
2880 (127.8ms) /* MaxAssignmentsRule */
2881 delete from buffer_linkage_call_type_assignments_1110845144
2882 where linkage_instance_id = 201826;
2883
2884 (130.8ms) /* MaxAssignmentsRule */
2885 update
2886 buffer_schedules_1110845144 as sc
2887 inner join call_types as ct
2888 on sc.call_type_id = ct.id
2889 and ct.active = 1
2890 inner join buffer_call_type_assignments_1110845144 as cta
2891 on sc.staff_id = cta.staff_id
2892 and sc.call_type_id = cta.call_type_id
2893 and sc.is_weekend = cta.is_weekend
2894 and sc.period_offset = cta.period_offset
2895 and sc.calendar_schedule_id = cta.calendar_schedule_id
2896 set cta.assignments_count = cta.assignments_count + 1
2897 where
2898 sc.schedule_on = '2018-07-02'
2899 and sc.call_type_id = 12692
2900 and sc.staff_id = 20704
2901
2902 (126.7ms) insert into buffer_call_type_assignments_1110845144 (
2903 staff_id,
2904 call_type_id,
2905 is_weekend,
2906 period_offset,
2907 calendar_schedule_id,
2908 assignments_count
2909 )
2910 select
2911 sc.staff_id,
2912 sc.call_type_id,
2913 sc.is_weekend,
2914 sc.period_offset,
2915 sc.calendar_schedule_id,
2916 1 as assignments_count
2917 from buffer_schedules_1110845144 as sc
2918 where
2919 sc.staff_id = 20704
2920 and sc.schedule_on = '2018-07-02'
2921 and sc.call_type_id = 12692
2922
2923 (122.1ms) /* UnassignedDaysRule */
2924 delete from buffer_linkage_assigned_days_1110845144
2925 where linkage_instance_id = 201826
2926
2927 (127.2ms) /* UnassignedDaysRule */
2928 UPDATE buffer_staff_assigned_days_1110845144 as sad
2929 inner join call_types as ct
2930 on ct.id = 12692
2931 and ct.unassigned_day = 0
2932 SET sad.is_assigned = 1
2933 WHERE sad.staff_id = 20704
2934 AND sad.schedule_on = '2018-07-02'
2935
2936 (120.2ms) delete
2937 from buffer_linkage_assignment_limits_1110845144
2938 where
2939 staff_id = 20704
2940 and linkage_instance_id = 201826
2941
2942 (232.6ms) /* AssignmentLimitsPerPeriod */
2943 /* AssignmentLimitsPerWeek */
2944 /* AssignmentLimitsRolling */
2945 /* AssignmentLimitsConsecutive */
2946
2947 insert into buffer_staff_assignment_limits_1110845144 (
2948 staff_id,
2949 assignment_limit_id,
2950 period_id,
2951 consecutive_days_mask,
2952 current_value
2953 )
2954 select
2955 sc.staff_id,
2956 al.id as assignment_limit_id,
2957 alp.id as period_id,
2958 case
2959 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2960 (1 << datediff(sc.schedule_on, alp.start_date))
2961 else 0
2962 end as consecutive_days_mask,
2963 1.0 as current_value
2964 from
2965 buffer_schedules_1110845144 as sc
2966 inner join buffer_assignment_limit_call_types_1110845144 as alct
2967 on sc.call_type_id = alct.call_type_id
2968 inner join buffer_assignment_limits_1110845144 as al
2969 on alct.assignment_limit_id = al.id
2970 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2971 inner join buffer_assignment_limit_staffs_1110845144 as als
2972 on al.id = als.assignment_limit_id
2973 and sc.staff_id = als.staff_id
2974 inner join assign_staff_types as ast
2975 on sc.staff_id = ast.staff_id
2976 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2977 inner join buffer_assignment_limit_staff_types_1110845144 as alst
2978 on al.id = alst.assignment_limit_id
2979 and ast.staff_type_id = alst.staff_type_id
2980 inner join buffer_assignment_limit_periods_1110845144 as alp
2981 on al.id = alp.assignment_limit_id
2982 and sc.schedule_on between alp.start_date and alp.end_date
2983 where
2984 sc.schedule_on = '2018-07-02'
2985 and sc.call_type_id = 12692
2986 and sc.staff_id = 20704
2987 on duplicate key update
2988 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
2989 current_value = current_value + values(current_value)
2990
2991INFO : (05-31 19:11:53) scheduled for: plan unit: [id:201826] [2018-07-01 - OB], [2018-07-02 - OB], Sharon Davis. DEPTH(1)
2992 (121.8ms) select
2993 coalesce(max(
2994 case
2995 when (ct.max_weekday_assignments > 0) then 1
2996 else 0
2997 end
2998 ), 0) as weekday_flag,
2999 coalesce(max(
3000 case
3001 when (ct.max_weekend_assignments > 0) then 1
3002 else 0
3003 end
3004 ), 0) as weekend_flag
3005 from
3006 buffer_auto_populate_slots_1110845144 as aps
3007 inner join call_types as ct
3008 on aps.call_type_id = ct.id
3009
3010 (122.3ms) /* NegativeLinkRule */
3011 delete ca
3012 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
3013 inner join buffer_negative_relations_1110845144 as nr use index (ix_buffer_negative_relations_composite_1)
3014 on ca.schedule_id = nr.schedule_id
3015 and ca.staff_id = nr.staff_id
3016
3017 (123.0ms) /* SpecializationOverlapRule */
3018 delete ca
3019 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
3020 inner join buffer_schedules_1110845144 as sc
3021 on ca.schedule_id = sc.id
3022 inner join buffer_staff_specializations_1110845144 as ssp
3023 on ca.staff_id = ssp.staff_id
3024 inner join max_applicable_call_types as mact
3025 on ssp.specialization_id = mact.specialization_id
3026 and sc.call_type_id = mact.call_type_id
3027 inner join buffer_specialization_daily_counts_1110845144 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3028 on ssp.specialization_id = sdc.specialization_id
3029 and sc.schedule_on = sdc.schedule_on
3030 and sdc.staff_id is null
3031 left join buffer_specialization_daily_counts_1110845144 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3032 on ssp.specialization_id = sdc_2.specialization_id
3033 and sc.schedule_on = sdc_2.schedule_on
3034 and ca.staff_id = sdc_2.staff_id
3035 where sdc.should_be_evaluated = 1
3036 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3037
3038 (122.4ms) /* BrokenLinkageRule */
3039 truncate table buffer_combined_availability_linkages_1110845144
3040
3041 (116.9ms) insert into buffer_combined_availability_linkages_1110845144 (
3042 staff_id,
3043 linkage_instance_id,
3044 slots_count,
3045 is_primary,
3046 has_violations
3047 )
3048 select
3049 staff_id,
3050 linkage_instance_id,
3051 count(1) as slots_count,
3052 min(is_primary) as is_primary,
3053 max(has_violations) as has_violations
3054 from buffer_combined_availabilities_1110845144 use index (ix_buffer_combined_availabilities_composite_2)
3055 group by
3056 staff_id,
3057 linkage_instance_id
3058 order by null
3059
3060 (122.0ms) delete ca
3061 from
3062 buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
3063 inner join buffer_schedules_1110845144 as sc
3064 on ca.schedule_id = sc.id
3065 inner join buffer_linkages_1110845144 as l
3066 on sc.linkage_instance_id = l.linkage_instance_id
3067 inner join buffer_combined_availability_linkages_1110845144 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3068 on ca.staff_id = ls_1.staff_id
3069 and sc.linkage_instance_id = ls_1.linkage_instance_id
3070 left join buffer_schedule_linkages_1110845144 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3071 on ca.staff_id = ls_2.staff_id
3072 and sc.linkage_instance_id = ls_2.linkage_instance_id
3073 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
3074
3075 (126.9ms) select straight_join
3076 sc.schedule_on,
3077 sc.call_type_id,
3078 count(distinct ca.staff_id) as available_members_count,
3079 group_concat(
3080 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3081 order by ls_1.is_primary desc, ca.staff_id
3082 separator ', '
3083 ) as available_members_ids,
3084 sc.linkage_instance_id,
3085 l.slots_count_total as linkage_slots_count
3086 from buffer_combined_availabilities_1110845144 as ca
3087 inner join buffer_schedules_1110845144 as sc
3088 on ca.schedule_id = sc.id
3089 inner join buffer_linkages_1110845144 as l
3090 on sc.linkage_instance_id = l.linkage_instance_id
3091 inner join buffer_combined_availability_linkages_1110845144 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3092 on ca.staff_id = ls_1.staff_id
3093 and sc.linkage_instance_id = ls_1.linkage_instance_id
3094 and ls_1.has_violations = 0
3095 where sc.should_be_evaluated = 1
3096 and sc.should_be_populated = 1
3097 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3098 order by available_members_count, sc.schedule_on, sc.display_order
3099 limit 1
3100
3101 (115.4ms) truncate table buffer_rule_scores_1110845144
3102
3103 (122.5ms) truncate table buffer_rule_calc_filters_1110845144
3104
3105 (120.5ms) insert into buffer_rule_calc_filters_1110845144 (
3106 linkage_instance_id,
3107 staff_id
3108 ) values (1201826, 20704)
3109
3110 (122.0ms) truncate table buffer_rule_scores_with_corrections_1110845144
3111
3112 (121.1ms) insert into buffer_rule_scores_with_corrections_1110845144 (
3113 linkage_instance_id, schedule_id, staff_id, rule_name, value
3114 )
3115 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3116 ifnull(rule_scores.value, max_values.max_value + 1) as value
3117 from buffer_rule_scores_1110845144 as rule_scores
3118 inner join (
3119 select rule_name, max(value) as max_value
3120 from buffer_rule_scores_1110845144
3121 group by rule_name
3122 order by null
3123 ) as max_values
3124 on rule_scores.rule_name = max_values.rule_name
3125 order by null
3126
3127 (125.0ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3128 from (
3129 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3130 sum(
3131 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3132 rules.weight *
3133 total_values.slots_count
3134 ) as slot_scores
3135 from buffer_rule_scores_with_corrections_1110845144 as rule_scores
3136 inner join (
3137 select rule_name, sum(abs(value)) as total_value,
3138 count(distinct schedule_id) as slots_count
3139 from buffer_rule_scores_with_corrections_1110845144
3140 group by rule_name
3141 order by null
3142 ) as total_values
3143 on rule_scores.rule_name = total_values.rule_name
3144 inner join rules
3145 on rule_scores.rule_name = rules.type
3146 where rules.rule_set_id = 1461
3147 and rules.applied = 1
3148 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3149 order by null
3150 ) as staff_scores
3151 group by linkage_instance_id, staff_id
3152 order by round(avg(slot_scores), 5) desc, rand()
3153 limit 1
3154
3155WARN : (05-31 19:11:55) No score found. Using fake score...
3156 PublishedBlock Load (123.2ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 319 AND ('2018-07-01' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1381 ORDER BY `published_blocks`.`id` ASC LIMIT 1
3157 (119.3ms) BEGIN
3158 (123.7ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
3159 FROM call_types AS ct
3160 INNER JOIN calendar_schedules AS cs
3161 ON ct.calendar_schedule_id = cs.id
3162 INNER JOIN blocks AS b
3163 ON b.client_id = ct.client_id
3164 AND b.block_schedule_id = cs.block_schedule_id
3165 AND '2018-07-01' BETWEEN b.start_date AND b.end_date
3166 INNER JOIN published_blocks AS pb
3167 ON pb.client_id = ct.client_id
3168 AND pb.block_id = b.id
3169 AND pb.calendar_schedule_id = cs.id
3170 LEFT JOIN published_block_call_types AS pbct
3171 ON pbct.client_id = ct.client_id
3172 AND pbct.assignment_id = ct.id
3173 AND pbct.assignment_type = 'CallType'
3174 AND pbct.block_id = b.id
3175 AND (
3176 (1 << (DAYOFWEEK('2018-07-01') - 1) & pbct.days_of_week_mask) > 0
3177 )
3178 WHERE ct.id = 12693
3179 AND (
3180 (
3181 pb.assignment_ids IS NULL
3182 AND (1 << (DAYOFWEEK('2018-07-01') - 1) & pb.days_of_week_mask) > 0
3183 )
3184 OR pbct.id IS NOT NULL
3185 )
3186
3187 CallType Load (121.5ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12693 LIMIT 1
3188 Client Load (120.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 319 LIMIT 1
3189 Staff Load (121.1ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20704 LIMIT 1
3190 Schedule Exists (120.8ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12693 AND `schedules`.`client_id` = 319 AND `schedules`.`schedule_on` = '2018-07-01' LIMIT 1
3191 SQL (130.4ms) INSERT INTO `schedules` (`client_id`, `call_type_id`, `staff_id`, `schedule_on`, `created_at`, `updated_at`, `weekend_on`, `dayofweek_on`, `populate_method`, `creator_id`) VALUES (319, 12693, 20704, '2018-07-01', '2018-05-31 19:11:58', '2018-05-31 19:11:58', 1, 1, 'auto_populate_step', 166)
3192 VisibleAssignment Load (121.3ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 319 AND `visible_assignments`.`date` = '2018-07-01' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12693 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
3193 (124.1ms) COMMIT
3194FLUSHED: nil:nil
3195 (125.7ms) UPDATE buffer_schedules_1110845144
3196 SET staff_id = 20704,
3197 should_be_populated = 0,
3198 should_be_evaluated = 0
3199 WHERE
3200 call_type_id = 12693
3201 AND schedule_on = '2018-07-01'
3202
3203 (124.2ms) delete from buffer_combined_availabilities_1110845144
3204 where linkage_instance_id = 1201826
3205
3206 (122.3ms) UPDATE buffer_rule_calc_schedules_1110845144
3207 SET staff_id = 20704
3208 WHERE call_type_id = 12693
3209 AND schedule_on = '2018-07-01'
3210
3211 (123.1ms) INSERT INTO buffer_rule_calc_schedules_1110845144
3212 SET staff_id = 20704,
3213 call_type_id = 12693,
3214 schedule_on = '2018-07-01',
3215 day_of_week_mask = 1
3216
3217 (120.8ms) /* NegativeLinkRule */
3218 UPDATE buffer_negative_relations_1110845144
3219 SET staff_id = 20704
3220 WHERE affected_by_schedule_id = (
3221 SELECT id
3222 FROM buffer_schedules_1110845144
3223 WHERE schedule_on = '2018-07-01'
3224 AND call_type_id = 12693
3225 )
3226
3227 (122.6ms) /* SpecializationOverlapRule */
3228 update buffer_staff_specializations_1110845144 as st_sp
3229 inner join max_applicable_call_types as mact
3230 on st_sp.specialization_id = mact.specialization_id
3231 and mact.call_type_id = 12693
3232 inner join buffer_specialization_daily_counts_1110845144 as sp_dc
3233 on st_sp.specialization_id = sp_dc.specialization_id
3234 and sp_dc.staff_id is null
3235 and sp_dc.schedule_on = '2018-07-01'
3236 left join buffer_specialization_daily_counts_1110845144 as sp_dc_2
3237 on st_sp.specialization_id = sp_dc_2.specialization_id
3238 and st_sp.staff_id = sp_dc_2.staff_id
3239 and sp_dc_2.schedule_on = '2018-07-01'
3240 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
3241 where st_sp.staff_id = 20704
3242 and sp_dc_2.id is null
3243
3244 (142.2ms) insert into buffer_specialization_daily_counts_1110845144 (
3245 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
3246 )
3247 select sp.id as specialization_id,
3248 20704 as staff_id,
3249 '2018-07-01' as schedule_on,
3250 1 as staffs_scheduled,
3251 sp.max_scheduled,
3252 sp_dc.should_be_evaluated
3253 from buffer_staff_specializations_1110845144 as st_sp
3254 inner join specializations as sp
3255 on st_sp.specialization_id = sp.id
3256 inner join max_applicable_call_types as mact
3257 on st_sp.specialization_id = mact.specialization_id
3258 and mact.call_type_id = 12693
3259 inner join buffer_specialization_daily_counts_1110845144 as sp_dc
3260 on st_sp.specialization_id = sp_dc.specialization_id
3261 and sp_dc.staff_id is null
3262 and sp_dc.schedule_on = '2018-07-01'
3263 left join buffer_specialization_daily_counts_1110845144 as sp_dc_2
3264 on st_sp.specialization_id = sp_dc_2.specialization_id
3265 and st_sp.staff_id = sp_dc_2.staff_id
3266 and sp_dc_2.schedule_on = '2018-07-01'
3267 where st_sp.staff_id = 20704
3268 and sp_dc_2.id is null
3269
3270 (124.0ms) /* MaxAssignmentsRule */
3271 delete from buffer_linkage_call_type_assignments_1110845144
3272 where linkage_instance_id = 1201826;
3273
3274 (120.8ms) /* MaxAssignmentsRule */
3275 update
3276 buffer_schedules_1110845144 as sc
3277 inner join call_types as ct
3278 on sc.call_type_id = ct.id
3279 and ct.active = 1
3280 inner join buffer_call_type_assignments_1110845144 as cta
3281 on sc.staff_id = cta.staff_id
3282 and sc.call_type_id = cta.call_type_id
3283 and sc.is_weekend = cta.is_weekend
3284 and sc.period_offset = cta.period_offset
3285 and sc.calendar_schedule_id = cta.calendar_schedule_id
3286 set cta.assignments_count = cta.assignments_count + 1
3287 where
3288 sc.schedule_on = '2018-07-01'
3289 and sc.call_type_id = 12693
3290 and sc.staff_id = 20704
3291
3292 (122.2ms) insert into buffer_call_type_assignments_1110845144 (
3293 staff_id,
3294 call_type_id,
3295 is_weekend,
3296 period_offset,
3297 calendar_schedule_id,
3298 assignments_count
3299 )
3300 select
3301 sc.staff_id,
3302 sc.call_type_id,
3303 sc.is_weekend,
3304 sc.period_offset,
3305 sc.calendar_schedule_id,
3306 1 as assignments_count
3307 from buffer_schedules_1110845144 as sc
3308 where
3309 sc.staff_id = 20704
3310 and sc.schedule_on = '2018-07-01'
3311 and sc.call_type_id = 12693
3312
3313 (122.8ms) /* UnassignedDaysRule */
3314 delete from buffer_linkage_assigned_days_1110845144
3315 where linkage_instance_id = 1201826
3316
3317 (124.8ms) /* UnassignedDaysRule */
3318 UPDATE buffer_staff_assigned_days_1110845144 as sad
3319 inner join call_types as ct
3320 on ct.id = 12693
3321 and ct.unassigned_day = 0
3322 SET sad.is_assigned = 1
3323 WHERE sad.staff_id = 20704
3324 AND sad.schedule_on = '2018-07-01'
3325
3326 (119.9ms) delete
3327 from buffer_linkage_assignment_limits_1110845144
3328 where
3329 staff_id = 20704
3330 and linkage_instance_id = 1201826
3331
3332 (141.7ms) /* AssignmentLimitsPerPeriod */
3333 /* AssignmentLimitsPerWeek */
3334 /* AssignmentLimitsRolling */
3335 /* AssignmentLimitsConsecutive */
3336
3337 insert into buffer_staff_assignment_limits_1110845144 (
3338 staff_id,
3339 assignment_limit_id,
3340 period_id,
3341 consecutive_days_mask,
3342 current_value
3343 )
3344 select
3345 sc.staff_id,
3346 al.id as assignment_limit_id,
3347 alp.id as period_id,
3348 case
3349 when (alp.limit_subtype_mask = 8 /* consecutive */) then
3350 (1 << datediff(sc.schedule_on, alp.start_date))
3351 else 0
3352 end as consecutive_days_mask,
3353 1.0 as current_value
3354 from
3355 buffer_schedules_1110845144 as sc
3356 inner join buffer_assignment_limit_call_types_1110845144 as alct
3357 on sc.call_type_id = alct.call_type_id
3358 inner join buffer_assignment_limits_1110845144 as al
3359 on alct.assignment_limit_id = al.id
3360 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3361 inner join buffer_assignment_limit_staffs_1110845144 as als
3362 on al.id = als.assignment_limit_id
3363 and sc.staff_id = als.staff_id
3364 inner join assign_staff_types as ast
3365 on sc.staff_id = ast.staff_id
3366 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
3367 inner join buffer_assignment_limit_staff_types_1110845144 as alst
3368 on al.id = alst.assignment_limit_id
3369 and ast.staff_type_id = alst.staff_type_id
3370 inner join buffer_assignment_limit_periods_1110845144 as alp
3371 on al.id = alp.assignment_limit_id
3372 and sc.schedule_on between alp.start_date and alp.end_date
3373 where
3374 sc.schedule_on = '2018-07-01'
3375 and sc.call_type_id = 12693
3376 and sc.staff_id = 20704
3377 on duplicate key update
3378 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
3379 current_value = current_value + values(current_value)
3380
3381INFO : (05-31 19:12:00) scheduled for: plan unit: [id:1201826] [2018-07-01 - OR-S], Sharon Davis. DEPTH(2)
3382 (122.3ms) select
3383 coalesce(max(
3384 case
3385 when (ct.max_weekday_assignments > 0) then 1
3386 else 0
3387 end
3388 ), 0) as weekday_flag,
3389 coalesce(max(
3390 case
3391 when (ct.max_weekend_assignments > 0) then 1
3392 else 0
3393 end
3394 ), 0) as weekend_flag
3395 from
3396 buffer_auto_populate_slots_1110845144 as aps
3397 inner join call_types as ct
3398 on aps.call_type_id = ct.id
3399
3400 (122.0ms) /* NegativeLinkRule */
3401 delete ca
3402 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
3403 inner join buffer_negative_relations_1110845144 as nr use index (ix_buffer_negative_relations_composite_1)
3404 on ca.schedule_id = nr.schedule_id
3405 and ca.staff_id = nr.staff_id
3406
3407 (123.0ms) /* SpecializationOverlapRule */
3408 delete ca
3409 from buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
3410 inner join buffer_schedules_1110845144 as sc
3411 on ca.schedule_id = sc.id
3412 inner join buffer_staff_specializations_1110845144 as ssp
3413 on ca.staff_id = ssp.staff_id
3414 inner join max_applicable_call_types as mact
3415 on ssp.specialization_id = mact.specialization_id
3416 and sc.call_type_id = mact.call_type_id
3417 inner join buffer_specialization_daily_counts_1110845144 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3418 on ssp.specialization_id = sdc.specialization_id
3419 and sc.schedule_on = sdc.schedule_on
3420 and sdc.staff_id is null
3421 left join buffer_specialization_daily_counts_1110845144 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3422 on ssp.specialization_id = sdc_2.specialization_id
3423 and sc.schedule_on = sdc_2.schedule_on
3424 and ca.staff_id = sdc_2.staff_id
3425 where sdc.should_be_evaluated = 1
3426 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3427
3428 (127.2ms) /* BrokenLinkageRule */
3429 truncate table buffer_combined_availability_linkages_1110845144
3430
3431 (123.5ms) insert into buffer_combined_availability_linkages_1110845144 (
3432 staff_id,
3433 linkage_instance_id,
3434 slots_count,
3435 is_primary,
3436 has_violations
3437 )
3438 select
3439 staff_id,
3440 linkage_instance_id,
3441 count(1) as slots_count,
3442 min(is_primary) as is_primary,
3443 max(has_violations) as has_violations
3444 from buffer_combined_availabilities_1110845144 use index (ix_buffer_combined_availabilities_composite_2)
3445 group by
3446 staff_id,
3447 linkage_instance_id
3448 order by null
3449
3450 (125.0ms) delete ca
3451 from
3452 buffer_combined_availabilities_1110845144 as ca use index (ix_buffer_combined_availabilities_composite_1)
3453 inner join buffer_schedules_1110845144 as sc
3454 on ca.schedule_id = sc.id
3455 inner join buffer_linkages_1110845144 as l
3456 on sc.linkage_instance_id = l.linkage_instance_id
3457 inner join buffer_combined_availability_linkages_1110845144 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3458 on ca.staff_id = ls_1.staff_id
3459 and sc.linkage_instance_id = ls_1.linkage_instance_id
3460 left join buffer_schedule_linkages_1110845144 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3461 on ca.staff_id = ls_2.staff_id
3462 and sc.linkage_instance_id = ls_2.linkage_instance_id
3463 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
3464
3465 (129.0ms) select straight_join
3466 sc.schedule_on,
3467 sc.call_type_id,
3468 count(distinct ca.staff_id) as available_members_count,
3469 group_concat(
3470 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3471 order by ls_1.is_primary desc, ca.staff_id
3472 separator ', '
3473 ) as available_members_ids,
3474 sc.linkage_instance_id,
3475 l.slots_count_total as linkage_slots_count
3476 from buffer_combined_availabilities_1110845144 as ca
3477 inner join buffer_schedules_1110845144 as sc
3478 on ca.schedule_id = sc.id
3479 inner join buffer_linkages_1110845144 as l
3480 on sc.linkage_instance_id = l.linkage_instance_id
3481 inner join buffer_combined_availability_linkages_1110845144 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3482 on ca.staff_id = ls_1.staff_id
3483 and sc.linkage_instance_id = ls_1.linkage_instance_id
3484 and ls_1.has_violations = 0
3485 where sc.should_be_evaluated = 1
3486 and sc.should_be_populated = 1
3487 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3488 order by available_members_count, sc.schedule_on, sc.display_order
3489 limit 1
3490
3491 (125.2ms) truncate table buffer_rule_scores_1110845144
3492
3493 (124.6ms) truncate table buffer_rule_calc_filters_1110845144
3494
3495 (122.8ms) insert into buffer_rule_calc_filters_1110845144 (
3496 linkage_instance_id,
3497 staff_id
3498 ) values (2201826, 20704)
3499
3500 (125.2ms) truncate table buffer_rule_scores_with_corrections_1110845144
3501
3502 (124.8ms) insert into buffer_rule_scores_with_corrections_1110845144 (
3503 linkage_instance_id, schedule_id, staff_id, rule_name, value
3504 )
3505 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3506 ifnull(rule_scores.value, max_values.max_value + 1) as value
3507 from buffer_rule_scores_1110845144 as rule_scores
3508 inner join (
3509 select rule_name, max(value) as max_value
3510 from buffer_rule_scores_1110845144
3511 group by rule_name
3512 order by null
3513 ) as max_values
3514 on rule_scores.rule_name = max_values.rule_name
3515 order by null
3516
3517 (135.3ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3518 from (
3519 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3520 sum(
3521 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3522 rules.weight *
3523 total_values.slots_count
3524 ) as slot_scores
3525 from buffer_rule_scores_with_corrections_1110845144 as rule_scores
3526 inner join (
3527 select rule_name, sum(abs(value)) as total_value,
3528 count(distinct schedule_id) as slots_count
3529 from buffer_rule_scores_with_corrections_1110845144
3530 group by rule_name
3531 order by null
3532 ) as total_values
3533 on rule_scores.rule_name = total_values.rule_name
3534 inner join rules
3535 on rule_scores.rule_name = rules.type
3536 where rules.rule_set_id = 1461
3537 and rules.applied = 1
3538 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3539 order by null
3540 ) as staff_scores
3541 group by linkage_instance_id, staff_id
3542 order by round(avg(slot_scores), 5) desc, rand()
3543 limit 1
3544
3545WARN : (05-31 19:12:01) No score found. Using fake score...
3546**Airbrake: closed