· 7 years ago · Jan 10, 2019, 09:32 PM
1(174.2ms) SET NAMES utf8 COLLATE utf8_unicode_ci, @@SESSION.sql_mode = 'NO_ENGINE_SUBSTITUTION', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2 Client Load (179.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
3 CalendarSchedule Load (174.0ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 6 AND `calendar_schedules`.`id` = 6 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
4 CallType Load (175.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (46, 47, 48, 49, 51)
5 CalendarSchedule Load (172.3ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 6 AND `calendar_schedules`.`id` = 6 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
6 Block Load (171.7ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND `blocks`.`id` = 17087 ORDER BY start_date asc, end_date asc LIMIT 1
7 BlockSchedule Load (169.7ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
8 PopulationTimeframe Load (171.6ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 6 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
9 RuleSet Load (568.7ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1547 LIMIT 1
10 Block Load (172.8ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date >= '2017-10-01' AND blocks.end_date <= '2017-10-31') ORDER BY start_date asc, end_date asc
11INFO : (01-10 08:55:40) phase: Scheduler::Base
12INFO : (01-10 08:55:40) from: 2017-10-01 to: 2017-10-31
13 Rule Load (172.8ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1547 ORDER BY applied desc, weight desc
14 RuleSet Load (690.6ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1547 LIMIT 1
15 Client Load (342.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
16 RuleSet Load (169.5ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1547 LIMIT 1
17 Client Load (350.7ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
18INFO : (01-10 08:55:42) rule configurations: AssignmentsPerDay
19 - Assignments Per Day applied {"opt1"=>{"applied"=>"1", "value"=>"0000002"}} weight:5
20 - Cumulative Assignment Targets not applied
21 - Cumulative Combined Assignment Targets not applied
22 - Cumulative Daily Assignment Targets not applied
23 - Cumulative Day of Week Targets not applied
24 - Cumulative Hour Targets not applied
25 - Cumulative Point Targets not applied
26 - Cumulative Weekend / Weekday Assignment Targets not applied
27 - Cumulative Weekend / Weekday Combined Assignment Targets not applied
28 - Cumulative Weekend / Weekday Hour Targets not applied
29 - Cumulative Weekend / Weekday Point Targets not applied
30 - Current Assignment Targets not applied
31 - Current Combined Assignment Targets not applied
32 - Current Daily Assignment Targets not applied
33 - Current Day of Week Targets not applied
34 - Current Hour Targets not applied
35 - Current Point Targets not applied
36 - Current Weekend / Weekday Assignment Targets not applied
37 - Current Weekend / Weekday Combined Assignment Targets not applied
38 - Current Weekend / Weekday Hour Targets not applied
39 - Current Weekend / Weekday Point Targets not applied
40 - Pending No call Requests not applied
41 - Pending call Requests not applied
42 - Days Between Assignments not applied
43 - Days Between Same Assignment not applied
44 - Unassigned Days not applied
45 - Weeks Between Same Assignment On Same Day Of Week not applied
46 - Weeks Between Same Day Of Week not applied
47INFO : (01-10 08:55:42) started: Scheduler::Cache::Collector.collect_all
48INFO : (01-10 08:55:42) collect_staffs
49 Staff Load (361.1ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `staff_entities` `staff_entities_staffs` ON `staff_entities_staffs`.`staff_id` = `staffs`.`id` 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` INNER JOIN `staff_entities` ON `staffs`.`id` = `staff_entities`.`staff_id` WHERE `staff_entities`.`entity_id` = 6 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY staff_entities.entity_id, staff_entities.display_order, last_name, first_name
50 Client Load (373.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6
51 StaffSpecialization Load (177.6ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 20729, 18127, 78, 8510)
52 Specialization Load (180.5ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` = 7 ORDER BY `specializations`.`priority` ASC
53 StaffCallType Load (340.3ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 20729, 18127, 78, 8510)
54INFO : (01-10 08:55:46) initial eligibles: [66,77,68,74,72,3351,79,80,6913,81,65,18125,70,6960,71,73,20694,75,20729,18127,78,8510]
55INFO : (01-10 08:55:46) collect_call_types
56 CallType Load (176.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 6 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
57INFO : (01-10 08:55:46) initial call types: [46,47,48,49,51,2716,397,1343,1344]
58INFO : (01-10 08:55:46) collect_chain_links
59 ChainLink Load (169.2ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 6
60INFO : (01-10 08:55:46) collect_linkages
61INFO : (01-10 08:55:46) collect_schedules
62 Schedule Load (176.1ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 6 AND (`schedules`.`schedule_on` BETWEEN '2017-10-01' AND '2017-10-31')
63INFO : (01-10 08:55:46) collect_slots
64INFO : (01-10 08:55:46) collect_approved_call_requests
65 Request Load (176.0ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2017-10-01' AND '2017-10-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
66INFO : (01-10 08:55:47) collect_approved_no_call_requests
67 Request Load (172.5ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2017-10-01' AND '2017-10-31') ORDER BY requests.schedule_on asc, requests.created_at asc
68INFO : (01-10 08:55:47) finished: Scheduler::Cache::Collector.collect_all
69INFO : (01-10 08:55:47) == begin to process requests
70 CallType Load (176.9ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 6 AND `call_types`.`active` = 1
71 (197.8ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 6 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
72 Request Load (174.3ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 6 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (46, 47, 48, 49, 51, 397, 1343, 1344, 2716) AND (requests.schedule_on > '2017-09-30')
73 SQL (188.1ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 6 AND 1=0
74INFO : (01-10 08:55:48) == end of requests processing
75 Block Load (173.5ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date <= '2017-10-31' AND blocks.end_date >= '2017-10-31') ORDER BY start_date asc, end_date asc LIMIT 1
76 Client Load (338.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
77 (176.1ms) SELECT `autopopulate_exclusions`.`schedule_on`, `autopopulate_exclusions`.`call_type_id` FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 6 AND `autopopulate_exclusions`.`schedule_on` IN ('2017-10-01', '2017-10-02', '2017-10-03', '2017-10-04', '2017-10-05', '2017-10-06', '2017-10-07', '2017-10-08', '2017-10-09', '2017-10-10', '2017-10-11', '2017-10-12', '2017-10-13', '2017-10-14', '2017-10-15', '2017-10-16', '2017-10-17', '2017-10-18', '2017-10-19', '2017-10-20', '2017-10-21', '2017-10-22', '2017-10-23', '2017-10-24', '2017-10-25', '2017-10-26', '2017-10-27', '2017-10-28', '2017-10-29', '2017-10-30', '2017-10-31')
78 (175.8ms) select
79 b.start_date,
80 b.end_date,
81 (pb.assignment_ids IS NULL) as fully_published,
82 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
83 pbct.days_of_week_mask
84 from blocks as b
85 inner join published_blocks as pb
86 on pb.block_id = b.id
87 inner join published_block_call_types as pbct
88 on pbct.published_block_id = pb.id
89 where b.client_id = 6
90 and pb.calendar_schedule_id = 6
91 and b.end_date BETWEEN '2017-10-01' AND '2017-10-31'
92
93INFO : (01-10 08:55:50) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
94INFO : (01-10 08:55:50) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
95INFO : (01-10 08:55:50) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
96INFO : (01-10 08:55:50) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
97INFO : (01-10 08:55:50) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
98INFO : (01-10 08:55:50) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
99INFO : (01-10 08:55:50) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
100INFO : (01-10 08:55:50) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
101INFO : (01-10 08:55:50) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
102INFO : (01-10 08:55:50) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query016
103INFO : (01-10 08:55:50) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
104INFO : (01-10 08:55:50) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
105INFO : (01-10 08:55:50) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
106INFO : (01-10 08:55:50) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
107INFO : (01-10 08:55:50) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
108INFO : (01-10 08:55:50) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
109INFO : (01-10 08:55:50) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
110INFO : (01-10 08:55:50) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
111INFO : (01-10 08:55:50) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
112INFO : (01-10 08:55:50) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
113INFO : (01-10 08:55:50) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0705
114INFO : (01-10 08:55:50) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0706
115INFO : (01-10 08:55:50) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0707
116INFO : (01-10 08:55:50) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0708
117INFO : (01-10 08:55:50) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0709
118INFO : (01-10 08:55:50) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query08
119INFO : (01-10 08:55:50) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query085
120INFO : (01-10 08:55:50) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query09
121INFO : (01-10 08:55:50) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query092
122INFO : (01-10 08:55:50) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query093
123INFO : (01-10 08:55:50) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query095
124INFO : (01-10 08:55:50) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query097
125INFO : (01-10 08:55:50) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query098
126INFO : (01-10 08:55:50) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query09_09_AL
127INFO : (01-10 08:55:50) <-- clear: 34 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
128INFO : (01-10 08:55:50) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
129INFO : (01-10 08:55:50) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
130INFO : (01-10 08:55:50) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
131INFO : (01-10 08:55:50) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
132INFO : (01-10 08:55:50) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
133INFO : (01-10 08:55:50) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
134INFO : (01-10 08:55:50) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Points
135INFO : (01-10 08:55:50) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
136INFO : (01-10 08:55:50) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query10
137INFO : (01-10 08:55:50) <-- clear: 44 - Scheduler::AutoPopulate::Queries::QueryScore02
138INFO : (01-10 08:55:50) <-- clear: 45 - Scheduler::AutoPopulate::Queries::QueryScore03
139INFO : (01-10 08:55:50) <-- clear: 46 - Scheduler::AutoPopulate::Queries::QueryScore04
140INFO : (01-10 08:55:50) <-- clear: 47 - Scheduler::AutoPopulate::Queries::Query11
141INFO : (01-10 08:55:50) <-- clear: 48 - Scheduler::AutoPopulate::Queries::DropTables
142 (170.8ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_2013398260
143 (171.5ms) DROP TABLE IF EXISTS buffer_annual_point_targets_2013398260
144 (173.4ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_2013398260
145 (171.1ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_2013398260
146 (173.0ms) DROP TABLE IF EXISTS buffer_call_days_targets_2013398260
147 (169.5ms) DROP TABLE IF EXISTS buffer_call_type_targets_2013398260
148 (169.4ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_2013398260
149 (171.1ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_2013398260
150 (171.3ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_2013398260
151 (169.4ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_2013398260
152 (171.3ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_2013398260
153 (170.6ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_2013398260
154 (170.0ms) DROP TABLE IF EXISTS buffer_current_cd_targets_2013398260
155 (170.3ms) DROP TABLE IF EXISTS buffer_current_ct_targets_2013398260
156 (170.7ms) DROP TABLE IF EXISTS buffer_current_dow_targets_2013398260
157 (171.6ms) DROP TABLE IF EXISTS buffer_current_hour_targets_2013398260
158 (171.4ms) DROP TABLE IF EXISTS buffer_current_oa_targets_2013398260
159 (171.0ms) DROP TABLE IF EXISTS buffer_current_point_targets_2013398260
160 (171.3ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_2013398260
161 (170.2ms) DROP TABLE IF EXISTS buffer_hour_targets_2013398260
162 (908.8ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_2013398260
163 (177.1ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_2013398260
164 (170.3ms) DROP TABLE IF EXISTS buffer_linkage_targets_2013398260
165 (170.0ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_2013398260
166 (190.6ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_2013398260
167 (171.0ms) DROP TABLE IF EXISTS buffer_location_privileges_2013398260
168 (170.0ms) DROP TABLE IF EXISTS buffer_periods_2013398260
169 (195.5ms) DROP TABLE IF EXISTS buffer_point_targets_2013398260
170 (171.6ms) DROP TABLE IF EXISTS buffer_schedules_2013398260
171 (171.8ms) DROP TABLE IF EXISTS buffer_target_call_types_2013398260
172 (170.9ms) DROP TABLE IF EXISTS buffer_target_daily_points_2013398260
173 (169.3ms) DROP TABLE IF EXISTS buffer_target_dates_list_2013398260
174 (171.1ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_2013398260
175 (169.8ms) DROP TABLE IF EXISTS buffer_targets_2013398260
176 (170.0ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_2013398260
177 (170.2ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_2013398260
178 (170.1ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_2013398260
179 (171.5ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_2013398260
180 (175.0ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_2013398260
181 (172.8ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_2013398260
182 (170.2ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_2013398260
183 (178.3ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_2013398260
184 (170.3ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_2013398260
185 (170.7ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_2013398260
186 (169.8ms) DROP TABLE IF EXISTS buffer_call_type_assignments_2013398260
187 (170.6ms) DROP TABLE IF EXISTS buffer_combined_availabilities_2013398260
188 (171.5ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_2013398260
189 (171.2ms) DROP TABLE IF EXISTS buffer_counter_increments_2013398260
190 (174.4ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_2013398260
191 (172.4ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_2013398260
192 (170.1ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_2013398260
193 (169.3ms) DROP TABLE IF EXISTS buffer_linkage_patterns_2013398260
194 (172.5ms) DROP TABLE IF EXISTS buffer_linkages_2013398260
195 (169.4ms) DROP TABLE IF EXISTS buffer_location_privileges_2013398260
196 (171.1ms) DROP TABLE IF EXISTS buffer_min_distances_2013398260
197 (169.4ms) DROP TABLE IF EXISTS buffer_negative_relations_2013398260
198 (170.1ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_2013398260
199 (170.4ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_2013398260
200 (169.9ms) DROP TABLE IF EXISTS buffer_rule_scores_2013398260
201 (170.6ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_2013398260
202 (170.2ms) DROP TABLE IF EXISTS buffer_schedule_linkages_2013398260
203 (172.5ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_2013398260
204 (171.7ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_2013398260
205 (170.0ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_2013398260
206 (183.2ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_2013398260
207 (171.4ms) DROP TABLE IF EXISTS buffer_staff_specializations_2013398260
208 (170.5ms) DROP TABLE IF EXISTS buffer_static_availabilities_2013398260
209 (171.5ms) DROP TABLE IF EXISTS buffer_target_assignments_2013398260
210 (179.5ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_2013398260
211 (169.9ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_2013398260
212 (170.1ms) DROP TABLE IF EXISTS buffer_assignment_limits_2013398260
213 (170.9ms) delete from buffer_target_dates
214 where session_id = 2013398260
215
216INFO : (01-10 08:56:03) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
217 (174.4ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_2013398260 LIKE buffer_assignment_limit_call_types
218 (174.0ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_2013398260 LIKE buffer_assignment_limit_periods
219 (176.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_2013398260 LIKE buffer_assignment_limit_staff_types
220 (172.6ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_2013398260 LIKE buffer_assignment_limit_staffs
221 (173.5ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_2013398260 LIKE buffer_auto_populate_slots
222 (173.9ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_2013398260 LIKE buffer_call_type_assignments
223 (180.3ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_2013398260 LIKE buffer_combined_availabilities
224 (175.4ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_2013398260 LIKE buffer_combined_availability_linkages
225 (179.9ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_2013398260 LIKE buffer_counter_increments
226 (173.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_2013398260 LIKE buffer_linkage_assigned_days
227 (173.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_2013398260 LIKE buffer_linkage_call_type_assignments
228 (173.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_2013398260 LIKE buffer_linkage_dow_targets
229 (172.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_2013398260 LIKE buffer_linkage_patterns
230 (178.0ms) CREATE TABLE IF NOT EXISTS buffer_linkages_2013398260 LIKE buffer_linkages
231 (172.5ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_2013398260 LIKE buffer_location_privileges
232 (175.6ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_2013398260 LIKE buffer_min_distances
233 (174.6ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_2013398260 LIKE buffer_negative_relations
234 (179.2ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_2013398260 LIKE buffer_rule_calc_filters
235 (179.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_2013398260 LIKE buffer_rule_calc_schedules
236 (171.5ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_2013398260 LIKE buffer_rule_scores
237 (182.1ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_2013398260 LIKE buffer_rule_scores_with_corrections
238 (201.0ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_2013398260 LIKE buffer_schedule_linkages
239 (174.0ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_2013398260 LIKE buffer_schedules_linkage_patterns
240 (173.3ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_2013398260 LIKE buffer_specialization_daily_counts
241 (174.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_2013398260 LIKE buffer_staff_assigned_days
242 (181.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_2013398260 LIKE buffer_staff_daily_counts
243 (171.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_2013398260 LIKE buffer_staff_specializations
244 (172.2ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_2013398260 LIKE buffer_static_availabilities
245 (185.9ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_2013398260 LIKE buffer_target_assignments
246 (172.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_2013398260 LIKE buffer_linkage_assignment_limits
247 (333.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_2013398260 LIKE buffer_staff_assignment_limits
248 (172.7ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_2013398260 LIKE buffer_assignment_limits
249 (173.7ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_2013398260 LIKE buffer_annual_hour_targets
250 (175.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_2013398260 LIKE buffer_annual_point_targets
251 (174.6ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_2013398260 LIKE buffer_annual_targets_staff_totals
252 (169.5ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_2013398260 LIKE buffer_auto_populate_slots
253 (172.9ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_2013398260 LIKE buffer_call_days_targets
254 (172.2ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_2013398260 LIKE buffer_call_type_targets
255 (187.3ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_2013398260 LIKE buffer_cumulative_cd_targets
256 (174.2ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_2013398260 LIKE buffer_cumulative_ct_targets
257 (173.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_2013398260 LIKE buffer_cumulative_dow_targets
258 (175.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_2013398260 LIKE buffer_cumulative_hour_targets
259 (173.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_2013398260 LIKE buffer_cumulative_oa_targets
260 (172.0ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_2013398260 LIKE buffer_cumulative_point_targets
261 (174.9ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_2013398260 LIKE buffer_current_cd_targets
262 (175.9ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_2013398260 LIKE buffer_current_ct_targets
263 (172.6ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_2013398260 LIKE buffer_current_dow_targets
264 (174.1ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_2013398260 LIKE buffer_current_hour_targets
265 (174.4ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_2013398260 LIKE buffer_current_oa_targets
266 (175.2ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_2013398260 LIKE buffer_current_point_targets
267 (172.8ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_2013398260 LIKE buffer_day_of_week_targets
268 (172.1ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_2013398260 LIKE buffer_hour_targets
269 (173.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_2013398260 LIKE buffer_linkage_call_days_targets
270 (173.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_2013398260 LIKE buffer_linkage_call_type_targets
271 (181.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_2013398260 LIKE buffer_linkage_targets
272 (174.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_2013398260 LIKE buffer_linkage_hour_targets
273 (174.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_2013398260 LIKE buffer_linkage_point_targets
274 (179.2ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_2013398260 LIKE buffer_location_privileges
275 (179.5ms) CREATE TABLE IF NOT EXISTS buffer_periods_2013398260 LIKE buffer_periods
276 (177.2ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_2013398260 LIKE buffer_point_targets
277 (172.7ms) CREATE TABLE IF NOT EXISTS buffer_schedules_2013398260 LIKE buffer_schedules
278 (173.8ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_2013398260 LIKE buffer_target_call_types
279 (172.5ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_2013398260 LIKE buffer_target_daily_points
280 (174.3ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_2013398260 LIKE buffer_target_dates_list
281 (173.5ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_2013398260 LIKE buffer_target_primary_availabilities
282 (173.2ms) CREATE TABLE IF NOT EXISTS buffer_targets_2013398260 LIKE buffer_targets
283 (172.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_2013398260 LIKE buffer_tmp_targets_availabilities
284 (173.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_2013398260 LIKE buffer_tmp_targets_availabilities_exclusions
285 (171.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_2013398260 LIKE buffer_tmp_targets_client_totals
286 (184.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_2013398260 LIKE buffer_tmp_targets_scheduled
287 (174.8ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_2013398260 LIKE buffer_tmp_targets_staff_totals
288INFO : (01-10 08:56:16) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
289 (174.5ms) select
290 b.start_date,
291 b.end_date,
292 (pb.assignment_ids IS NULL) as fully_published,
293 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
294 pbct.days_of_week_mask
295 from blocks as b
296 inner join published_blocks as pb
297 on pb.block_id = b.id
298 inner join published_block_call_types as pbct
299 on pbct.published_block_id = pb.id
300 where b.client_id = 6
301 and pb.calendar_schedule_id = 6
302 and b.end_date BETWEEN '2017-10-01' AND '2017-10-31'
303
304 AutopopulateExclusion Load (177.0ms) SELECT `autopopulate_exclusions`.* FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 6 AND `autopopulate_exclusions`.`schedule_on` IN ('2017-10-01', '2017-10-02', '2017-10-03', '2017-10-04', '2017-10-05', '2017-10-06', '2017-10-07', '2017-10-08', '2017-10-09', '2017-10-10', '2017-10-11', '2017-10-12', '2017-10-13', '2017-10-14', '2017-10-15', '2017-10-16', '2017-10-17', '2017-10-18', '2017-10-19', '2017-10-20', '2017-10-21', '2017-10-22', '2017-10-23', '2017-10-24', '2017-10-25', '2017-10-26', '2017-10-27', '2017-10-28', '2017-10-29', '2017-10-30', '2017-10-31')
305 (210.5ms) INSERT INTO buffer_auto_populate_slots_2013398260
306 (schedule_on, day_of_week_mask, call_type_id, display_order, should_be_populated)
307 VALUES ('2017-10-01', 1, 46, 0, 1), ('2017-10-01', 1, 48, 3, 1), ('2017-10-02', 2, 46, 0, 1), ('2017-10-02', 2, 47, 2, 1), ('2017-10-02', 2, 48, 3, 1), ('2017-10-02', 2, 49, 4, 1), ('2017-10-02', 2, 51, 7, 1), ('2017-10-03', 4, 46, 0, 1), ('2017-10-03', 4, 47, 2, 1), ('2017-10-03', 4, 48, 3, 1), ('2017-10-03', 4, 49, 4, 1), ('2017-10-03', 4, 51, 7, 1), ('2017-10-04', 8, 46, 0, 1), ('2017-10-04', 8, 47, 2, 1), ('2017-10-04', 8, 48, 3, 1), ('2017-10-04', 8, 49, 4, 1), ('2017-10-04', 8, 51, 7, 1), ('2017-10-05', 16, 46, 0, 1), ('2017-10-05', 16, 47, 2, 1), ('2017-10-05', 16, 48, 3, 1), ('2017-10-05', 16, 49, 4, 1), ('2017-10-05', 16, 51, 7, 1), ('2017-10-06', 32, 46, 0, 1), ('2017-10-06', 32, 47, 2, 1), ('2017-10-06', 32, 48, 3, 1), ('2017-10-06', 32, 49, 4, 1), ('2017-10-06', 32, 51, 7, 1), ('2017-10-07', 64, 46, 0, 1), ('2017-10-07', 64, 48, 3, 1), ('2017-10-08', 1, 46, 0, 1), ('2017-10-08', 1, 48, 3, 1), ('2017-10-09', 2, 46, 0, 1), ('2017-10-09', 2, 47, 2, 1), ('2017-10-09', 2, 48, 3, 1), ('2017-10-09', 2, 49, 4, 1), ('2017-10-09', 2, 51, 7, 1), ('2017-10-10', 4, 46, 0, 1), ('2017-10-10', 4, 47, 2, 1), ('2017-10-10', 4, 48, 3, 1), ('2017-10-10', 4, 49, 4, 1), ('2017-10-10', 4, 51, 7, 1), ('2017-10-11', 8, 46, 0, 1), ('2017-10-11', 8, 47, 2, 1), ('2017-10-11', 8, 48, 3, 1), ('2017-10-11', 8, 49, 4, 1), ('2017-10-11', 8, 51, 7, 1), ('2017-10-12', 16, 46, 0, 1), ('2017-10-12', 16, 47, 2, 1), ('2017-10-12', 16, 48, 3, 1), ('2017-10-12', 16, 49, 4, 1), ('2017-10-12', 16, 51, 7, 1), ('2017-10-13', 32, 46, 0, 1), ('2017-10-13', 32, 47, 2, 1), ('2017-10-13', 32, 48, 3, 1), ('2017-10-13', 32, 49, 4, 1), ('2017-10-13', 32, 51, 7, 1), ('2017-10-14', 64, 46, 0, 1), ('2017-10-14', 64, 48, 3, 1), ('2017-10-15', 1, 46, 0, 1), ('2017-10-15', 1, 48, 3, 1), ('2017-10-16', 2, 46, 0, 1), ('2017-10-16', 2, 47, 2, 1), ('2017-10-16', 2, 48, 3, 1), ('2017-10-16', 2, 49, 4, 1), ('2017-10-16', 2, 51, 7, 1), ('2017-10-17', 4, 46, 0, 1), ('2017-10-17', 4, 47, 2, 1), ('2017-10-17', 4, 48, 3, 1), ('2017-10-17', 4, 49, 4, 1), ('2017-10-17', 4, 51, 7, 1), ('2017-10-18', 8, 46, 0, 1), ('2017-10-18', 8, 47, 2, 1), ('2017-10-18', 8, 48, 3, 1), ('2017-10-18', 8, 49, 4, 1), ('2017-10-18', 8, 51, 7, 1), ('2017-10-19', 16, 46, 0, 1), ('2017-10-19', 16, 47, 2, 1), ('2017-10-19', 16, 48, 3, 1), ('2017-10-19', 16, 49, 4, 1), ('2017-10-19', 16, 51, 7, 1), ('2017-10-20', 32, 46, 0, 1), ('2017-10-20', 32, 47, 2, 1), ('2017-10-20', 32, 48, 3, 1), ('2017-10-20', 32, 49, 4, 1), ('2017-10-20', 32, 51, 7, 1), ('2017-10-21', 64, 46, 0, 1), ('2017-10-21', 64, 48, 3, 1), ('2017-10-22', 1, 46, 0, 1), ('2017-10-22', 1, 48, 3, 1), ('2017-10-23', 2, 46, 0, 1), ('2017-10-23', 2, 47, 2, 1), ('2017-10-23', 2, 48, 3, 1), ('2017-10-23', 2, 49, 4, 1), ('2017-10-23', 2, 51, 7, 1), ('2017-10-24', 4, 46, 0, 1), ('2017-10-24', 4, 47, 2, 1), ('2017-10-24', 4, 48, 3, 1), ('2017-10-24', 4, 49, 4, 1), ('2017-10-24', 4, 51, 7, 1), ('2017-10-25', 8, 46, 0, 1), ('2017-10-25', 8, 47, 2, 1), ('2017-10-25', 8, 48, 3, 1), ('2017-10-25', 8, 49, 4, 1), ('2017-10-25', 8, 51, 7, 1), ('2017-10-26', 16, 46, 0, 1), ('2017-10-26', 16, 47, 2, 1), ('2017-10-26', 16, 48, 3, 1), ('2017-10-26', 16, 49, 4, 1), ('2017-10-26', 16, 51, 7, 1), ('2017-10-27', 32, 46, 0, 1), ('2017-10-27', 32, 47, 2, 1), ('2017-10-27', 32, 48, 3, 1), ('2017-10-27', 32, 49, 4, 1), ('2017-10-27', 32, 51, 7, 1), ('2017-10-28', 64, 46, 0, 1), ('2017-10-28', 64, 48, 3, 1), ('2017-10-29', 1, 46, 0, 1), ('2017-10-29', 1, 48, 3, 1), ('2017-10-30', 2, 46, 0, 1), ('2017-10-30', 2, 47, 2, 1), ('2017-10-30', 2, 48, 3, 1), ('2017-10-30', 2, 49, 4, 1), ('2017-10-30', 2, 51, 7, 1), ('2017-10-31', 4, 46, 0, 1), ('2017-10-31', 4, 47, 2, 1), ('2017-10-31', 4, 48, 3, 1), ('2017-10-31', 4, 49, 4, 1), ('2017-10-31', 4, 51, 7, 1)
308
309INFO : (01-10 08:56:16) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
310 (185.7ms) insert into buffer_auto_populate_slots_2013398260/* client_id */ (
311 schedule_on,
312 day_of_week_mask,
313 call_type_id,
314 display_order,
315 should_be_populated
316 )
317 select distinct
318 date_add(
319 auto_populate_slots.schedule_on,
320 interval soft_linkages.offset day
321 ) as schedule_on,
322 (1 << (
323 dayofweek(
324 date_add(
325 auto_populate_slots.schedule_on,
326 interval soft_linkages.offset day
327 )
328 ) - 1
329 )
330 ) as day_of_week_mask,
331 soft_linkages.linked_call_type_id as call_type_id,
332 call_types.display_order,
333 0 as should_be_populated
334 from
335 buffer_auto_populate_slots_2013398260/* client_id */ as auto_populate_slots
336 inner join soft_linkages
337 on auto_populate_slots.call_type_id = soft_linkages.call_type_id
338 and auto_populate_slots.day_of_week_mask = soft_linkages.day_of_week_mask
339 left join buffer_auto_populate_slots_2013398260/* client_id */ as auto_populate_slots_2
340 on date_add(
341 auto_populate_slots.schedule_on,
342 interval soft_linkages.offset day
343 ) = auto_populate_slots_2.schedule_on
344 and soft_linkages.linked_call_type_id = auto_populate_slots_2.call_type_id
345 inner join call_types
346 on soft_linkages.linked_call_type_id = call_types.id
347 where auto_populate_slots_2.id is null
348 order by null
349
350INFO : (01-10 08:56:16) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
351 (188.2ms) insert into buffer_auto_populate_slots_2013398260 (
352 schedule_on,
353 day_of_week_mask,
354 call_type_id,
355 display_order,
356 should_be_populated
357 )
358 select distinct
359 date_add(
360 auto_populate_slots.schedule_on,
361 interval linkage_relations.distance_in_days day
362 ) as schedule_on,
363 (1 << (
364 dayofweek(
365 date_add(
366 auto_populate_slots.schedule_on,
367 interval linkage_relations.distance_in_days day
368 )
369 ) - 1
370 )
371 ) as day_of_week_mask,
372 linkage_relations.linked_call_type_id as call_type_id,
373 call_types.display_order,
374 0 as should_be_populated
375 from
376 buffer_auto_populate_slots_2013398260 as auto_populate_slots
377 inner join linkage_relations
378 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
379 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
380 left join buffer_auto_populate_slots_2013398260 as auto_populate_slots_2
381 on date_add(
382 auto_populate_slots.schedule_on,
383 interval linkage_relations.distance_in_days day
384 ) = auto_populate_slots_2.schedule_on
385 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
386 inner join call_types
387 on linkage_relations.linked_call_type_id = call_types.id
388 where auto_populate_slots_2.id is null
389 order by null
390
391INFO : (01-10 08:56:17) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
392 (183.2ms) insert into linkage_relations (
393 client_id,
394 call_type_id,
395 day_of_week_mask,
396 linked_call_type_id,
397 distance_in_days,
398 linkage_template_id
399 )
400 select distinct
401 6 /* client_id */ as client_id,
402 aps.call_type_id,
403 aps.day_of_week_mask,
404 aps.call_type_id as linked_call_type_id,
405 0 as distance_in_days,
406 coalesce(
407 lr.linkage_template_id,
408 @linkage_template_max := @linkage_template_max + 1
409 ) as linkage_template_id
410 from
411 buffer_auto_populate_slots_2013398260/* client_id */ as aps
412 inner join (
413 select @linkage_template_max := count(distinct linkage_template_id) - 1
414 from linkage_relations
415 where client_id = 6 /* client_id */
416 order by null
417 ) as lt
418 on 1 = 1
419 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
420 on aps.call_type_id = lr.call_type_id
421 and aps.day_of_week_mask = lr.day_of_week_mask
422 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
423 on aps.call_type_id = lr_2.call_type_id
424 and aps.day_of_week_mask = lr_2.day_of_week_mask
425 and aps.call_type_id = lr_2.linked_call_type_id
426 and lr_2.distance_in_days = 0
427 where lr_2.id is null
428 order by null
429
430 (213.1ms) insert into buffer_schedules_2013398260 (
431 call_type_id,
432 staff_id,
433 schedule_on,
434 day_of_week_mask,
435 is_weekend,
436 display_order,
437 week_number,
438 should_be_evaluated,
439 should_be_populated,
440 linkage_instance_id
441 )
442 select
443 call_type_id,
444 staff_id,
445 schedule_on,
446 day_of_week_mask,
447 is_weekend,
448 display_order,
449 week_number,
450 max(should_be_evaluated) as should_be_evaluated,
451 max(should_be_populated) as should_be_populated,
452 min(linkage_instance_id) as linkage_instance_id
453 from (
454 select
455 auto_populate_slots_2.call_type_id,
456 schedules.staff_id,
457 auto_populate_slots_2.schedule_on,
458 auto_populate_slots_2.day_of_week_mask,
459 case
460 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
461 else 1
462 end as is_weekend,
463 auto_populate_slots_2.display_order,
464 yearweek(
465 auto_populate_slots_2.schedule_on,
466 case
467 when (clients.start_of_week = 0) then 2
468 else 7
469 end
470 ) as week_number,
471 case
472 when (schedules.staff_id is null) then 1
473 else 0
474 end as should_be_evaluated,
475 (
476 case
477 when (
478 auto_populate_slots_2.should_be_populated = 1
479 and schedules.staff_id is null
480 ) then 1
481 else 0
482 end
483 ) as should_be_populated,
484 (
485 linkage_relations.linkage_template_id * 1000000 +
486 yearweek(
487 auto_populate_slots.schedule_on,
488 case
489 when (clients.start_of_week = 0) then 2
490 else 7
491 end
492 )
493 ) as linkage_instance_id
494 from
495 buffer_auto_populate_slots_2013398260 as auto_populate_slots
496 inner join clients
497 on clients.id = 6 /* client_id */
498 inner join linkage_relations
499 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
500 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
501 inner join buffer_auto_populate_slots_2013398260 as auto_populate_slots_2
502 on date_add(
503 auto_populate_slots.schedule_on,
504 interval linkage_relations.distance_in_days day
505 ) = auto_populate_slots_2.schedule_on
506 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
507 left join schedules
508 on auto_populate_slots_2.call_type_id = schedules.call_type_id
509 and auto_populate_slots_2.schedule_on = schedules.schedule_on
510 -- we should exclude do-not-populate slots
511 left join autopopulate_exclusions
512 on auto_populate_slots_2.call_type_id = autopopulate_exclusions.call_type_id
513 and auto_populate_slots_2.schedule_on = autopopulate_exclusions.schedule_on
514 where
515 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
516 -- once we added someone to that slot then it should be considered as a regular one
517 schedules.id is not null
518 or autopopulate_exclusions.id is null
519 order by null
520 ) as t1
521 group by
522 call_type_id,
523 staff_id,
524 schedule_on,
525 day_of_week_mask,
526 display_order,
527 week_number
528 order by null
529
530INFO : (01-10 08:56:17) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
531 (187.4ms) update
532 buffer_schedules_2013398260 as sc
533 inner join call_types as ct
534 on sc.call_type_id = ct.id
535 inner join calendar_schedules as cs
536 on ct.calendar_schedule_id = cs.id
537 inner join blocks as b
538 on cs.block_schedule_id = b.block_schedule_id
539 and sc.schedule_on between b.start_date and b.end_date
540 set
541 sc.calendar_schedule_id = cs.id,
542 sc.block_id = b.id
543
544 BlockSchedule Load (171.4ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
545 (209.8ms) insert into buffer_periods_2013398260 (
546 calendar_schedule_id,
547 block_schedule_id,
548 period_offset,
549 start_block_display_order,
550 end_block_display_order,
551 start_date,
552 end_date,
553 should_be_populated,
554 should_be_evaluated
555 )
556 select
557 t1.calendar_schedule_id,
558 t1.block_schedule_id,
559 t1.period_offset,
560 t2.start_block_display_order,
561 t2.end_block_display_order,
562 t2.start_date,
563 t2.end_date,
564 t1.should_be_populated,
565 t1.should_be_evaluated
566 from (
567 select
568 sc.calendar_schedule_id,
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 max(sc.should_be_populated) as should_be_populated,
577 max(sc.should_be_evaluated) as should_be_evaluated
578 from (
579 select
580 sc.block_id,
581 sc.calendar_schedule_id,
582 max(ps.should_be_populated) as should_be_populated,
583 1 as should_be_evaluated
584 from
585 buffer_schedules_2013398260 as sc
586 inner join buffer_auto_populate_slots_2013398260 as ps
587 on sc.schedule_on = ps.schedule_on
588 and sc.call_type_id = ps.call_type_id
589 group by
590 sc.block_id,
591 sc.calendar_schedule_id
592 order by null
593 ) as sc
594 inner join blocks as b
595 on sc.block_id = b.id
596 inner join population_timeframes as pt
597 on pt.client_id = 6
598 and pt.timeframe_type = 1
599 group by
600 sc.calendar_schedule_id,
601 b.block_schedule_id,
602 period_offset
603 order by null
604 ) as t1
605 inner join (
606 select
607 b.block_schedule_id,
608 case
609 when ((month(b.start_date) - pt.begin_period) >= 0)
610 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
611 else
612 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
613 end as period_offset,
614 min(b.display_order) as start_block_display_order,
615 max(b.display_order) as end_block_display_order,
616 min(b.start_date) as start_date,
617 max(b.end_date) as end_date
618 from
619 blocks as b
620 inner join population_timeframes as pt
621 on pt.client_id = 6
622 and pt.timeframe_type = 1
623 where
624 b.client_id = 6
625 group by
626 b.block_schedule_id,
627 period_offset
628 order by null
629 ) as t2
630 on t1.block_schedule_id = t2.block_schedule_id
631 and t1.period_offset = t2.period_offset
632 order by null
633
634 (199.3ms) update
635 buffer_schedules_2013398260 as sc
636 inner join buffer_periods_2013398260 as p
637 on sc.calendar_schedule_id = p.calendar_schedule_id
638 and sc.schedule_on between p.start_date and p.end_date
639 set sc.period_offset = p.period_offset
640
641INFO : (01-10 08:56:18) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
642INFO : (01-10 08:56:18) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
643 (288.7ms) insert into buffer_counter_increments_2013398260 (
644 schedule_id,
645 calls,
646 points,
647 hours,
648 affects_cumulative_targets
649 )
650 select
651 sc.id as schedule_id,
652 case
653 when (ct.included_in_targets > 0) then 1
654 else 0
655 end as calls,
656 coalesce(
657 case
658 when (hl.observed_date is null or dp.point is not null) then dp.point
659 else dp_2.point
660 end,
661 0.0
662 ) as points,
663 ct.hours,
664 case
665 when (b.display_order between (t1.timeframe_ending - (12 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
666 else 0
667 end as affects_cumulative_targets
668 from
669 buffer_schedules_2013398260 as sc
670 inner join (
671 select
672 id,
673 included_in_targets,
674 coalesce(
675 timestampdiff(
676 minute,
677 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
678 concat(
679 '1900-01-0',
680 case
681 when ((
682 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
683 )
684 and (
685 timediff(
686 maketime(end_time_hour, end_time_minute, 0),
687 maketime(start_time_hour, start_time_minute, 0)
688 ) <= 0
689 )
690 ) then '2'
691 else '1'
692 end,
693 ' ',
694 maketime(end_time_hour, end_time_minute, 0)
695 )
696 ) / 60,
697 0.0
698 ) as hours
699 from call_types
700 where
701 client_id = 6
702 and active = 1
703 order by null
704 ) as ct
705 on sc.call_type_id = ct.id
706 inner join blocks as b
707 on sc.block_id = b.id
708 inner join (
709 select
710 calendar_schedule_id,
711 max(
712 case
713 when (should_be_populated = 1) then end_block_display_order
714 else null
715 end
716 ) as timeframe_ending
717 from buffer_periods_2013398260
718 group by calendar_schedule_id
719 order by null
720 ) as t1
721 on sc.calendar_schedule_id = t1.calendar_schedule_id
722 left join (
723 select
724 hd.observed_date,
725 case
726 when (hd.observed_date > hd.extended_date) then hd.extended_date
727 else hd.observed_date
728 end as date_from,
729 case
730 when (hd.observed_date < hd.extended_date) then hd.extended_date
731 else hd.observed_date
732 end as date_to
733 from
734 holidays as h
735 inner join holiday_dates as hd
736 on h.id = hd.holiday_id
737 inner join (
738 select
739 year(min(schedule_on)) as year_from,
740 year(max(schedule_on)) as year_to
741 from buffer_schedules_2013398260
742 where should_be_evaluated = 1
743 ) as y
744 on hd.year between y.year_from and y.year_to
745 where
746 h.client_id = 6
747 and (
748 h.observed = 1
749 or h.extended = 1
750 )
751 order by null
752 ) as hl
753 on sc.schedule_on between hl.date_from and hl.date_to
754 left join assignment_annotations as aa
755 on ct.id = aa.assignmentable_id
756 and aa.assignmentable_type = 'CallType'
757 and aa.annotation_id = 0 /* annotation = "None" */
758 and aa.applicable = 1
759 left join assignment_annotation_daily_points as dp
760 on aa.id = dp.assignment_annotation_id
761 and case
762 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
763 when (sc.schedule_on = hl.observed_date) then 7
764 else 8
765 end = dp.day
766 left join assignment_annotation_daily_points as dp_2
767 on aa.id = dp_2.assignment_annotation_id
768 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
769 where sc.should_be_evaluated = 1
770 order by null
771
772INFO : (01-10 08:56:18) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
773 (186.7ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
774 insert into buffer_linkage_patterns_2013398260 (
775 period_offset,
776 calendar_schedule_id,
777 block_id,
778 block_start_date,
779 block_start_dow,
780 block_length,
781 linkage_pattern_id,
782 pattern_start_dow,
783 days_before_start,
784 pattern_length,
785 start_offset,
786 iterations_count
787 )
788 select
789 b1.period_offset,
790 b1.calendar_schedule_id,
791 b1.block_id,
792 b2.start_date as block_start_date,
793 dayofweek(b2.start_date) as block_start_dow,
794 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
795 lp.id as linkage_pattern_id,
796 (lp.start_on + 1) as pattern_start_dow,
797 lp.days_before_start,
798 (lp.length * 7) as pattern_length,
799 null as start_offset,
800 null as iterations_count
801 from (
802 select
803 calendar_schedule_id,
804 block_id,
805 period_offset
806 from buffer_schedules_2013398260
807 group by
808 calendar_schedule_id,
809 block_id,
810 period_offset
811 order by null
812 ) as b1
813 inner join blocks as b2
814 on b1.block_id = b2.id
815 inner join linkage_patterns as lp
816 on lp.client_id = 6
817 and b1.calendar_schedule_id = lp.calendar_schedule_id
818 order by null
819
820 (178.3ms) -- 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
821 update buffer_linkage_patterns_2013398260
822 set start_offset = if(
823 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
824 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
825 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
826 )
827
828 (175.0ms) -- 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)
829 -- a pattern should be repeated couple of times (in order to cover the entire block)
830 update buffer_linkage_patterns_2013398260
831 set iterations_count =
832 ceiling(
833 (
834 block_length +
835 case
836 when (start_offset < 0) then abs(start_offset)
837 else 0
838 end
839 ) * 1.0 / pattern_length
840 )
841
842 (187.0ms) -- Generate an assignments sequence for every linkage pattern
843 insert into buffer_schedules_linkage_patterns_2013398260 (
844 call_type_id,
845 staff_id,
846 schedule_on,
847 period_offset,
848 day_of_week_mask,
849 display_order,
850 week_number,
851 should_be_evaluated,
852 should_be_populated,
853 linkage_instance_id,
854 block_id,
855 calendar_schedule_id
856 )
857 select
858 lps.assignment_id as call_type_id,
859 null as staff_id,
860 case
861 when (lp.start_offset < 0)
862 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
863 else
864 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
865 end as schedule_on,
866 lp.period_offset,
867 null as day_of_week_mask,
868 null as display_order,
869 null as week_number,
870 0 as should_be_evaluated,
871 0 as should_be_populated,
872 lp.linkage_pattern_id as linkage_instance_id,
873 lp.block_id,
874 lp.calendar_schedule_id
875 from
876 numeric_sequence as ns
877 inner join buffer_linkage_patterns_2013398260 as lp
878 on ns.item_number <= (lp.iterations_count - 1)
879 inner join linkage_pattern_slots as lps
880 on lp.linkage_pattern_id = lps.linkage_pattern_id
881 and lps.assignment_type = 'CallType'
882 where
883 case
884 when (lp.start_offset < 0)
885 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
886 else
887 (ns.item_number * lp.pattern_length + lps.offset)
888 end between 0 and (lp.block_length - 1)
889 order by null
890
891 (178.4ms) -- Fill the columns that we left blank in the previous "INSERT"
892 update
893 buffer_schedules_linkage_patterns_2013398260 as slp
894 inner join clients as cl
895 on cl.id = 6
896 inner join call_types as ct
897 on slp.call_type_id = ct.id
898 left join buffer_schedules_2013398260 as bs
899 on slp.schedule_on = bs.schedule_on
900 and slp.call_type_id = bs.call_type_id
901 set
902 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
903 slp.display_order = ct.display_order,
904 slp.week_number =
905 yearweek(
906 slp.schedule_on,
907 case
908 when (cl.start_of_week = 0) then 2
909 else 7
910 end
911 ),
912 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
913 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
914
915 (176.1ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
916 delete slp_1
917 from
918 buffer_schedules_linkage_patterns_2013398260 as slp_1
919 inner join (
920 select linkage_instance_id
921 from buffer_schedules_linkage_patterns_2013398260
922 group by linkage_instance_id
923 having
924 max(should_be_evaluated) = 0
925 and max(should_be_populated) = 0
926 order by null
927 ) as slp_2
928 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
929
930 (175.0ms) -- Delete do-not-populate slots
931 delete slp
932 from
933 buffer_schedules_linkage_patterns_2013398260 as slp
934 inner join autopopulate_exclusions as ae
935 on slp.call_type_id = ae.call_type_id
936 and slp.schedule_on = ae.schedule_on
937 left join schedules as sc
938 on slp.call_type_id = sc.call_type_id
939 and slp.schedule_on = sc.schedule_on
940 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
941 -- once we added someone to that slot then it should be considered as a regular one
942 where sc.id is null
943
944 (174.0ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
945 update
946 buffer_schedules_linkage_patterns_2013398260 as slp
947 inner join buffer_schedules_2013398260 as bs_1
948 on slp.schedule_on = bs_1.schedule_on
949 and slp.call_type_id = bs_1.call_type_id
950 inner join buffer_schedules_2013398260 as bs_2
951 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
952 set bs_2.linkage_instance_id = slp.linkage_instance_id
953
954 (180.5ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
955 -- should be copied over to the buffer schedules table (used during the auto-populate process)
956 insert into buffer_schedules_2013398260 (
957 call_type_id,
958 staff_id,
959 schedule_on,
960 period_offset,
961 day_of_week_mask,
962 is_weekend,
963 display_order,
964 week_number,
965 should_be_evaluated,
966 should_be_populated,
967 linkage_instance_id,
968 block_id,
969 calendar_schedule_id
970 )
971 select
972 call_type_id,
973 staff_id,
974 schedule_on,
975 period_offset,
976 day_of_week_mask,
977 case
978 when (day_of_week_mask & 62 > 0) then 0
979 else 1
980 end as is_weekend,
981 display_order,
982 week_number,
983 should_be_evaluated,
984 should_be_populated,
985 linkage_instance_id,
986 block_id,
987 calendar_schedule_id
988 from buffer_schedules_linkage_patterns_2013398260
989 where
990 should_be_evaluated = 0
991 and should_be_populated = 0
992
993INFO : (01-10 08:56:20) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query016
994 (185.1ms) insert into buffer_schedules_2013398260 (
995 call_type_id,
996 staff_id,
997 schedule_on,
998 day_of_week_mask,
999 is_weekend,
1000 display_order,
1001 should_be_evaluated,
1002 should_be_populated,
1003 linkage_instance_id
1004 )
1005 select distinct
1006 schedules_call_overlap.call_type_id,
1007 schedules_call_overlap.staff_id,
1008 schedules_call_overlap.schedule_on,
1009 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
1010 case
1011 when (dayofweek(schedules_call_overlap.schedule_on) between 2 and 6) then 0
1012 else 1
1013 end as is_weekend,
1014 call_types.display_order,
1015 0 as should_be_evaluated,
1016 0 as should_be_populated,
1017 -1 as linkage_instance_id
1018 from (
1019 select schedule_on
1020 from buffer_schedules_2013398260
1021 where should_be_evaluated = 1
1022 group by schedule_on
1023 order by null
1024 ) as buffer_schedules_1
1025 inner join schedules as schedules_call_overlap
1026 on schedules_call_overlap.client_id = 6
1027 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
1028 left join buffer_schedules_2013398260 as buffer_schedules_2
1029 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1030 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1031 inner join call_types
1032 on schedules_call_overlap.call_type_id = call_types.id
1033 where buffer_schedules_2.id is null
1034 order by null
1035
1036INFO : (01-10 08:56:20) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1037INFO : (01-10 08:56:20) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1038INFO : (01-10 08:56:20) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1039INFO : (01-10 08:56:20) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1040INFO : (01-10 08:56:20) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1041 (197.0ms) insert into buffer_schedules_2013398260 (
1042 call_type_id,
1043 staff_id,
1044 schedule_on,
1045 day_of_week_mask,
1046 is_weekend,
1047 display_order,
1048 should_be_evaluated,
1049 should_be_populated,
1050 linkage_instance_id
1051 )
1052 select distinct straight_join
1053 schedules.call_type_id,
1054 schedules.staff_id,
1055 schedules.schedule_on,
1056 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1057 case
1058 when (dayofweek(schedules.schedule_on) between 2 and 6) then 0
1059 else 1
1060 end as is_weekend,
1061 call_types.display_order,
1062 0 as should_be_evaluated,
1063 0 as should_be_populated,
1064 -1 as linkage_instance_id
1065 from
1066 buffer_schedules_2013398260 as buffer_schedules_1
1067 inner join negative_links_influences use index (ix_unique_links)
1068 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1069 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1070 and negative_links_influences.source_type = 'CallType'
1071 and negative_links_influences.target_type = 'CallType'
1072 inner join schedules use index (ix_schedules_call_date)
1073 on date_add(
1074 buffer_schedules_1.schedule_on,
1075 interval negative_links_influences.offset day
1076 ) = schedules.schedule_on
1077 and negative_links_influences.target_id = schedules.call_type_id
1078 left join buffer_schedules_2013398260 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1079 on schedules.schedule_on = buffer_schedules_2.schedule_on
1080 and schedules.call_type_id = buffer_schedules_2.call_type_id
1081 inner join call_types
1082 on schedules.call_type_id = call_types.id
1083 where
1084 buffer_schedules_1.linkage_instance_id >= 0
1085 and buffer_schedules_1.should_be_evaluated = 1
1086 and buffer_schedules_2.id is null
1087 order by null
1088
1089INFO : (01-10 08:56:20) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1090 (197.7ms) insert into buffer_schedules_2013398260 (
1091 call_type_id,
1092 staff_id,
1093 schedule_on,
1094 day_of_week_mask,
1095 is_weekend,
1096 display_order,
1097 should_be_evaluated,
1098 should_be_populated,
1099 linkage_instance_id
1100 )
1101 select distinct straight_join
1102 (-1 * negative_links_influences.target_id) as call_type_id,
1103 vacations.staff_id,
1104 vacations.schedule_on,
1105 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1106 vacations.weekend_on as is_weekend,
1107 -1 as display_order,
1108 0 as should_be_evaluated,
1109 0 as should_be_populated,
1110 -1 as linkage_instance_id
1111 from
1112 buffer_schedules_2013398260 as buffer_schedules_1
1113 inner join negative_links_influences use index (ix_unique_links)
1114 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1115 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1116 and negative_links_influences.source_type = 'CallType'
1117 and negative_links_influences.target_id > 0
1118 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1119 inner join vacations use index (ix_vacations_on_schedule_and_client)
1120 on vacations.client_id = 6 /* client_id */
1121 and date_add(
1122 buffer_schedules_1.schedule_on,
1123 interval negative_links_influences.offset day
1124 ) = vacations.schedule_on
1125 and negative_links_influences.target_id = vacations.non_clinical_type_id
1126 and vacations.status in (3) /* based on the "non-working requests" settings */
1127 left join buffer_schedules_2013398260 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1128 on vacations.staff_id = buffer_schedules_2.staff_id
1129 and vacations.schedule_on = buffer_schedules_2.schedule_on
1130 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1131 where
1132 buffer_schedules_1.linkage_instance_id >= 0
1133 and buffer_schedules_1.should_be_evaluated = 1
1134 and buffer_schedules_2.id is null
1135 order by null
1136
1137INFO : (01-10 08:56:20) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1138 (191.4ms) insert into buffer_linkages_2013398260 (
1139 linkage_instance_id,
1140 slots_count_total,
1141 has_conflicts
1142 )
1143 select straight_join
1144 schedules.linkage_instance_id,
1145 count(1) as slots_count_total,
1146 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1147 from
1148 buffer_schedules_2013398260 as schedules
1149 left join (
1150 select straight_join
1151 linkage_relations.linkage_template_id
1152 from linkage_relations
1153 inner join negative_links_influences use index (ix_unique_links)
1154 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1155 and linkage_relations.call_type_id = negative_links_influences.source_id
1156 and negative_links_influences.source_type = 'CallType'
1157 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1158 and negative_links_influences.target_type = 'CallType'
1159 and linkage_relations.distance_in_days = negative_links_influences.offset
1160 where linkage_relations.client_id = 6 /* client_id */
1161 and (
1162 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1163 or linkage_relations.distance_in_days != 0
1164 )
1165 group by linkage_relations.linkage_template_id
1166 order by null
1167 ) as linkage_conflicts
1168 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1169 where schedules.linkage_instance_id >= 0
1170 group by
1171 schedules.linkage_instance_id,
1172 has_conflicts
1173 order by null
1174
1175INFO : (01-10 08:56:20) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1176 (181.7ms) insert into buffer_target_assignments_2013398260 (
1177 call_type_id,
1178 target_group_id,
1179 assignment_id,
1180 assignment_type
1181 )
1182 select
1183 t1.call_type_id,
1184 ifnull(tg.id, 0) as target_group_id,
1185 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1186 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1187 from (
1188 select ct.id as call_type_id
1189 from
1190 buffer_schedules_2013398260 as sc
1191 inner join call_types as ct
1192 on sc.call_type_id = ct.id
1193 and ct.active = 1
1194 and ct.included_in_targets > 0
1195 where linkage_instance_id >= 0
1196 group by ct.id
1197 order by null
1198 ) as t1
1199 left join target_group_calls as tgc
1200 on t1.call_type_id = tgc.call_type_id
1201 left join target_groups as tg
1202 on tgc.target_group_id = tg.id
1203 and tg.active = 1
1204 order by null
1205
1206INFO : (01-10 08:56:21) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1207INFO : (01-10 08:56:21) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1208INFO : (01-10 08:56:21) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0705
1209 (182.8ms) insert into buffer_linkage_call_type_assignments_2013398260 (
1210 linkage_instance_id,
1211 call_type_id,
1212 is_weekend,
1213 period_offset,
1214 calendar_schedule_id,
1215 max_assignments,
1216 assignments_count
1217 )
1218 select
1219 s.linkage_instance_id,
1220 s.call_type_id,
1221 s.is_weekend,
1222 s.period_offset,
1223 s.calendar_schedule_id,
1224 case
1225 when (s.is_weekend = 0) then ct.max_weekday_assignments
1226 else ct.max_weekend_assignments
1227 end as _max_assignments,
1228 sum(1) as assignments_count
1229 from buffer_schedules_2013398260 as s
1230 inner join call_types as ct
1231 on s.call_type_id = ct.id
1232 and ct.active = 1
1233 and (
1234 ct.max_weekday_assignments is not null
1235 or ct.max_weekend_assignments is not null
1236 )
1237 where s.linkage_instance_id >= 0
1238 and s.staff_id is null
1239 group by
1240 s.linkage_instance_id,
1241 s.call_type_id,
1242 s.is_weekend,
1243 s.period_offset,
1244 s.calendar_schedule_id,
1245 _max_assignments
1246 having _max_assignments >= 0
1247 order by null
1248
1249INFO : (01-10 08:56:21) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0706
1250 (183.7ms) insert into buffer_call_type_assignments_2013398260 (
1251 staff_id,
1252 call_type_id,
1253 is_weekend,
1254 period_offset,
1255 calendar_schedule_id,
1256 assignments_count
1257 )
1258 select
1259 sc.staff_id,
1260 sc.call_type_id,
1261 sc.weekend_on as is_weekend,
1262 p.period_offset,
1263 p.calendar_schedule_id,
1264 sum(1) as assignments_count
1265 from buffer_periods_2013398260 as p
1266 inner join call_types as ct
1267 on p.calendar_schedule_id = ct.calendar_schedule_id
1268 and ct.active = 1
1269 and (
1270 ct.max_weekday_assignments is not null
1271 or ct.max_weekend_assignments is not null
1272 )
1273 inner join schedules as sc
1274 on ct.id = sc.call_type_id
1275 and sc.schedule_on between p.start_date and p.end_date
1276 and sc.staff_id is not null
1277 where p.should_be_evaluated = 1
1278 group by
1279 sc.staff_id,
1280 sc.call_type_id,
1281 sc.weekend_on,
1282 p.period_offset,
1283 p.calendar_schedule_id
1284 order by null
1285
1286INFO : (01-10 08:56:21) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0707
1287INFO : (01-10 08:56:21) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0708
1288 (176.8ms) insert into buffer_linkage_assigned_days_2013398260 (
1289 linkage_instance_id,
1290 period_offset,
1291 week_number,
1292 schedule_on,
1293 is_weekend
1294 )
1295 select distinct
1296 sc.linkage_instance_id,
1297 sc.period_offset,
1298 sc.week_number,
1299 sc.schedule_on,
1300 sc.is_weekend
1301 from
1302 buffer_schedules_2013398260 as sc
1303 inner join call_types as ct
1304 on sc.call_type_id = ct.id
1305 and ct.unassigned_day = 0
1306 where
1307 sc.linkage_instance_id >= 0
1308 and sc.staff_id is null
1309 order by null
1310
1311INFO : (01-10 08:56:21) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0709
1312INFO : (01-10 08:56:21) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query08
1313 (172.9ms) insert into buffer_staff_daily_counts_2013398260 (
1314 staff_id, schedule_on, linkage_instance_id, slots_count
1315 )
1316 select staff_id, schedule_on, null as linkage_instance_id,
1317 count(1) as slots_count
1318 from buffer_schedules_2013398260
1319 where staff_id is not null
1320 and call_type_id > 0 /* VC */
1321 group by staff_id, schedule_on
1322 order by null
1323
1324 (183.8ms) insert into buffer_staff_daily_counts_2013398260 (
1325 staff_id, schedule_on, linkage_instance_id, slots_count
1326 )
1327 select staff_id, schedule_on, linkage_instance_id,
1328 count(1) as slots_count
1329 from buffer_schedules_2013398260
1330 where staff_id is not null
1331 and call_type_id > 0 /* VC */
1332 group by staff_id, schedule_on, linkage_instance_id
1333 order by null
1334
1335INFO : (01-10 08:56:21) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query085
1336 (272.0ms) insert into buffer_staff_specializations_2013398260 (
1337 staff_id, specialization_id
1338 )
1339 select staff_id, specialization_id
1340 from view_staff_specializations
1341 where client_id = 6
1342 order by null
1343
1344 (183.6ms) insert into buffer_specialization_daily_counts_2013398260 (
1345 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1346 )
1347 select sp.id as specialization_id,
1348 null as staff_id,
1349 bs_1.schedule_on,
1350 count(distinct st_sp.staff_id) as staffs_scheduled,
1351 sp.max_scheduled,
1352 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
1353 from (
1354 select schedule_on, day_of_week_mask
1355 from buffer_schedules_2013398260
1356 where should_be_evaluated = 1
1357 group by schedule_on, day_of_week_mask
1358 order by null
1359 ) as bs_1
1360 inner join specializations as sp
1361 on sp.client_id = 6
1362 and sp.active = 1
1363 inner join buffer_schedules_2013398260 as bs_2
1364 on bs_1.schedule_on = bs_2.schedule_on
1365 and bs_2.call_type_id > 0 /* is not VC */
1366 inner join max_applicable_call_types as mact
1367 on sp.id = mact.specialization_id
1368 and bs_2.call_type_id = mact.call_type_id
1369 left join buffer_staff_specializations_2013398260 as st_sp
1370 on bs_2.staff_id = st_sp.staff_id
1371 and sp.id = st_sp.specialization_id
1372 group by sp.id, bs_1.schedule_on, sp.max_scheduled
1373 order by null
1374
1375 (184.4ms) insert into buffer_specialization_daily_counts_2013398260 (
1376 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1377 )
1378 select sp.id as specialization_id,
1379 st_sp.staff_id,
1380 bs_1.schedule_on,
1381 1 as staffs_scheduled,
1382 sp.max_scheduled,
1383 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
1384 from (
1385 select schedule_on, day_of_week_mask
1386 from buffer_schedules_2013398260
1387 where should_be_evaluated = 1
1388 group by schedule_on, day_of_week_mask
1389 order by null
1390 ) as bs_1
1391 inner join specializations as sp
1392 on sp.client_id = 6
1393 and sp.active = 1
1394 inner join buffer_schedules_2013398260 as bs_2
1395 on bs_1.schedule_on = bs_2.schedule_on
1396 and bs_2.call_type_id > 0 /* is not VC */
1397 inner join max_applicable_call_types as mact
1398 on sp.id = mact.specialization_id
1399 and bs_2.call_type_id = mact.call_type_id
1400 inner join buffer_staff_specializations_2013398260 as st_sp
1401 on bs_2.staff_id = st_sp.staff_id
1402 and sp.id = st_sp.specialization_id
1403 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
1404 order by null
1405
1406INFO : (01-10 08:56:22) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query09
1407 (173.1ms) truncate table buffer_location_privileges_2013398260
1408
1409 (193.5ms) insert into buffer_location_privileges_2013398260 (
1410 staff_id,
1411 location_id,
1412 begin_date,
1413 end_date
1414 )
1415 select
1416 s.id as staff_id,
1417 l.location_id,
1418 '0000-00-00' as begin_date,
1419 '9999-12-31' as end_date
1420 from (
1421 select l.id as location_id
1422 from
1423 buffer_schedules_2013398260 as sc
1424 inner join call_types as ct
1425 on sc.call_type_id = ct.id
1426 and ct.active = 1
1427 inner join locations as l
1428 on ct.location_id = l.id
1429 and l.active = 1
1430 where sc.linkage_instance_id >= 0
1431 and sc.should_be_evaluated = 1
1432 group by l.id
1433 order by null
1434 ) as l
1435 inner join staffs as s
1436 on s.client_id = 6
1437 and s.active = 1
1438 and s.take_call = 1
1439 and s.auto_populatable = 1
1440 left join location_privileges as lp
1441 on s.id = lp.staff_id
1442 where lp.id is null
1443 union
1444 select
1445 lp.staff_id,
1446 lp.location_id,
1447 lp.begin_date,
1448 coalesce(lp.end_date, '9999-12-31') as end_date
1449 from (
1450 select l.id as location_id
1451 from
1452 buffer_schedules_2013398260 as sc
1453 inner join call_types as ct
1454 on sc.call_type_id = ct.id
1455 and ct.active = 1
1456 inner join locations as l
1457 on ct.location_id = l.id
1458 and l.active = 1
1459 where sc.linkage_instance_id >= 0
1460 and sc.should_be_evaluated = 1
1461 group by l.id
1462 order by null
1463 ) as l
1464 inner join location_privileges as lp
1465 on l.location_id = lp.location_id
1466 order by null;
1467
1468 (288.6ms) insert into buffer_static_availabilities_2013398260 (
1469 schedule_id,
1470 staff_id,
1471 staff_type_id,
1472 is_primary
1473 )
1474 select
1475 schedules.id as schedule_id,
1476 staffs.id as staff_id,
1477 assign_staff_types.staff_type_id,
1478 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
1479 from
1480 buffer_schedules_2013398260 as schedules
1481 /* AvailabilityRule */
1482 inner join call_types
1483 on schedules.call_type_id = call_types.id
1484 and call_types.active = 1
1485 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
1486 inner join staff_call_types
1487 on call_types.id = staff_call_types.call_type_id
1488 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
1489 inner join staffs
1490 on staff_call_types.staff_id = staffs.id
1491 and staffs.active = 1
1492 and staffs.take_call = 1
1493 and staffs.auto_populatable = 1
1494 inner join buffer_location_privileges_2013398260 as location_privileges
1495 on staffs.id = location_privileges.staff_id
1496 and call_types.location_id = location_privileges.location_id
1497 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
1498 inner join assign_staff_types
1499 on staffs.id = assign_staff_types.staff_id
1500 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
1501 left join eligible_staff_types
1502 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
1503 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
1504 left join assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1505 on staffs.id = assign_rotation_schedules.staff_id
1506 and schedules.schedule_on between assign_rotation_schedules.begin_date and assign_rotation_schedules.end_date
1507 left join staff_rotations force index (ix_staff_rotations_composite_1)
1508 on staffs.id = staff_rotations.staff_id
1509 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
1510 and assign_rotation_schedules.rotation_schedule_id = staff_rotations.rotation_schedule_id
1511 left join daily_rotations
1512 on staffs.id = daily_rotations.staff_id
1513 and schedules.schedule_on = daily_rotations.schedule_on
1514 and assign_rotation_schedules.rotation_schedule_id = daily_rotations.rotation_schedule_id
1515 and staff_rotations.id is not null
1516 left join rotations
1517 on coalesce(daily_rotations.rotation_id, staff_rotations.rotation_id) = rotations.id
1518 and rotations.active = 1
1519 left join rotation_call_types
1520 on rotations.id = rotation_call_types.rotation_id
1521 and staff_call_types.call_type_id = rotation_call_types.call_type_id
1522 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
1523 /* RequiredRotationsRule */
1524 left join required_rotations
1525 on staff_call_types.call_type_id = required_rotations.call_type_id
1526 left join staff_rotations as rr_staff_rotations force index (ix_staff_rotations_composite_3)
1527 on staffs.id = rr_staff_rotations.staff_id
1528 and required_rotations.rotation_id = rr_staff_rotations.rotation_id
1529 left join assign_rotation_schedules as rr_assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1530 on staffs.id = rr_assign_rotation_schedules.staff_id
1531 and rr_staff_rotations.start_date <= rr_assign_rotation_schedules.end_date
1532 and rr_staff_rotations.end_date >= rr_assign_rotation_schedules.begin_date
1533 and rr_staff_rotations.rotation_schedule_id = rr_assign_rotation_schedules.rotation_schedule_id
1534 /* NoCallRequestsHiddenHardRule */
1535 left join request_settings
1536 on assign_staff_types.staff_type_id = request_settings.staff_type_id
1537 left join requests
1538 on schedules.schedule_on = requests.schedule_on
1539 and staffs.id = requests.staff_id
1540 and requests.request_type = 0 /* type = no_call */
1541 and requests.status in (1, 3) /* status = approved or fulfilled */
1542 and call_types.is_no_call_requestable = 1
1543 left join request_call_types
1544 on requests.id = request_call_types.request_id
1545 and schedules.call_type_id = request_call_types.call_type_id
1546 where
1547 schedules.linkage_instance_id >= 0
1548 and schedules.should_be_evaluated = 1
1549 and (
1550 call_types.all_staff_types = 1
1551 or eligible_staff_types.id is not null
1552 )
1553 and (
1554 rotations.id is null
1555 or rotation_call_types.id is not null
1556 )
1557 and (
1558 requests.id is null
1559 or (
1560 requests.all_call_types = 1
1561 and request_settings.call_only_all_call_switch = 1 /* calls only */
1562 and call_types.assignment_type = 1 /* clinical */
1563 )
1564 or (
1565 requests.all_call_types = 0
1566 and request_call_types.id is null
1567 )
1568 )
1569 group by
1570 schedules.id,
1571 staffs.id,
1572 assign_staff_types.staff_type_id,
1573 is_primary
1574 having
1575 count(distinct
1576 case
1577 when (
1578 rr_staff_rotations.id is not null
1579 and rr_assign_rotation_schedules.id is not null
1580 and schedules.schedule_on >= greatest(rr_staff_rotations.start_date, rr_assign_rotation_schedules.begin_date)
1581 )
1582 then rr_staff_rotations.rotation_id
1583 else null
1584 end
1585 ) >= count(distinct required_rotations.rotation_id) /* RequiredRotationsRule */
1586 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
1587 order by null
1588
1589INFO : (01-10 08:56:23) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query092
1590 (186.6ms) /* RotationPostCallRule */
1591 /*
1592 Exclude cases when people have a rotation starting on the next day after a call we're trying to make,
1593 if such rotation is configured appropriately ("Post-call on first day" = "Not permitted").
1594 */
1595 delete sa
1596 from buffer_static_availabilities_2013398260 as sa
1597 inner join buffer_schedules_2013398260 as sc
1598 on sa.schedule_id = sc.id
1599 inner join call_types as ct
1600 on sc.call_type_id = ct.id
1601 and ct.assignment_type = 0 /* call */
1602 inner join staff_rotations as sr
1603 on sa.staff_id = sr.staff_id
1604 and date_add(sc.schedule_on, interval 1 day) between sr.start_date and sr.end_date
1605 inner join assign_rotation_schedules as ars
1606 on sa.staff_id = ars.staff_id
1607 and date_add(sc.schedule_on, interval 1 day) between ars.begin_date and ars.end_date
1608 and sr.rotation_schedule_id = ars.rotation_schedule_id
1609 inner join rotations as r
1610 on sr.rotation_id = r.id
1611 and r.post_call_allowed = 0
1612 left join assign_rotation_schedules as ars_2
1613 on sa.staff_id = ars_2.staff_id
1614 and sr.start_date <= ars_2.end_date
1615 and sr.end_date >= ars_2.begin_date
1616 and sr.rotation_schedule_id = ars_2.rotation_schedule_id
1617 -- Earlier intersection
1618 and ars.begin_date > ars_2.begin_date
1619 where
1620 date_add(sc.schedule_on, interval 1 day) = greatest(sr.start_date, ars.begin_date)
1621 and ars_2.id is null;
1622
1623INFO : (01-10 08:56:23) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query093
1624INFO : (01-10 08:56:23) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query095
1625 (189.4ms) insert into buffer_negative_relations_2013398260 (
1626 schedule_id,
1627 affected_by_schedule_id,
1628 staff_id
1629 )
1630 select
1631 b_schedules.id as schedule_id,
1632 b_schedules_negative_link.id as affected_by_schedule_id,
1633 b_schedules_negative_link.staff_id
1634 from
1635 buffer_schedules_2013398260 as b_schedules
1636 /* NegativeLinkRule */
1637 inner join negative_links_influences
1638 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1639 and b_schedules.call_type_id = negative_links_influences.source_id
1640 and negative_links_influences.source_type = 'CallType'
1641 and negative_links_influences.target_type = 'CallType'
1642 inner join buffer_schedules_2013398260 as b_schedules_negative_link
1643 on date_add(
1644 b_schedules.schedule_on,
1645 interval negative_links_influences.offset day
1646 ) = b_schedules_negative_link.schedule_on
1647 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
1648 and b_schedules.id != b_schedules_negative_link.id
1649 where
1650 b_schedules.linkage_instance_id >= 0
1651 and b_schedules.should_be_evaluated = 1
1652 order by null
1653
1654 (187.3ms) insert into buffer_negative_relations_2013398260 (
1655 schedule_id,
1656 affected_by_schedule_id,
1657 staff_id
1658 )
1659 select
1660 b_schedules.id as schedule_id,
1661 b_schedules_negative_link.id as affected_by_schedule_id,
1662 b_schedules_negative_link.staff_id
1663 from
1664 buffer_schedules_2013398260 as b_schedules
1665 /* NegativeLinkRule */
1666 inner join negative_links_influences
1667 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1668 and b_schedules.call_type_id = negative_links_influences.source_id
1669 and negative_links_influences.source_type = 'CallType'
1670 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1671 inner join buffer_schedules_2013398260 as b_schedules_negative_link
1672 on date_add(
1673 b_schedules.schedule_on,
1674 interval negative_links_influences.offset day
1675 ) = b_schedules_negative_link.schedule_on
1676 and (-1 * negative_links_influences.target_id) = b_schedules_negative_link.call_type_id
1677 and b_schedules.id != b_schedules_negative_link.id
1678 where
1679 b_schedules.linkage_instance_id >= 0
1680 and b_schedules.should_be_evaluated = 1
1681 order by null
1682
1683 (174.6ms) delete from buffer_schedules_2013398260
1684 where call_type_id < 0 /* vacations and non-clinicals */
1685
1686INFO : (01-10 08:56:24) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query097
1687 (381.2ms) insert into buffer_staff_assigned_days_2013398260 (
1688 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
1689 )
1690 select staffs.staff_id,
1691 if(days.within_period = 1, days.period_offset, null) as period_offset,
1692 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
1693 days.schedule_on,
1694 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
1695 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
1696 from (
1697 select distinct staff_id
1698 from buffer_static_availabilities_2013398260
1699 order by null
1700 ) as staffs
1701 inner join (
1702 select p.period_offset,
1703 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,
1704 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
1705 from buffer_periods_2013398260 as p
1706 inner join (
1707 select 0 as digit union select 1 union select 2 union select 3 union select 4
1708 union select 5 union select 6 union select 7 union select 8 union select 9
1709 ) as digits_1
1710 inner join (
1711 select 0 as digit union select 1 union select 2 union select 3 union select 4
1712 union select 5 union select 6 union select 7 union select 8 union select 9
1713 ) as digits_2
1714 inner join (
1715 select 0 as digit union select 1 union select 2 union select 3 union select 4
1716 union select 5 union select 6 union select 7 union select 8 union select 9
1717 ) as digits_3
1718 where p.calendar_schedule_id = 6
1719 and p.should_be_evaluated = 1
1720 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
1721 order by null
1722 ) as days
1723 inner join (
1724 select min(start_date) as start_date, max(end_date) as end_date
1725 from buffer_periods_2013398260
1726 where calendar_schedule_id = 6
1727 and should_be_evaluated = 1
1728 order by null
1729 ) as limits
1730 inner join clients
1731 on clients.id = 6
1732 left join (
1733 select sc.staff_id, sc.schedule_on
1734 from buffer_periods_2013398260 as p
1735 inner join schedules as sc
1736 on sc.client_id = 6
1737 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
1738 inner join call_types as ct
1739 on sc.call_type_id = ct.id
1740 and ct.unassigned_day = 0
1741 where p.calendar_schedule_id = 6
1742 and p.should_be_evaluated = 1
1743 group by sc.staff_id, sc.schedule_on
1744 order by null
1745 ) as schedules
1746 on staffs.staff_id = schedules.staff_id
1747 and days.schedule_on = schedules.schedule_on
1748 left join (
1749 select v.staff_id, v.schedule_on
1750 from buffer_periods_2013398260 as p
1751 inner join vacations as v
1752 on v.client_id = 6
1753 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
1754 and v.status IN (3)
1755 inner join non_clinical_types as nct
1756 on v.non_clinical_type_id = nct.id
1757 and nct.active = 1
1758 and nct.unassigned_day = 0
1759 where p.calendar_schedule_id = 6
1760 and p.should_be_evaluated = 1
1761 group by v.staff_id, v.schedule_on
1762 order by null
1763 ) as vacations
1764 on staffs.staff_id = vacations.staff_id
1765 and days.schedule_on = vacations.schedule_on
1766 where days.within_period = 1
1767 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
1768 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
1769 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
1770 )
1771 order by null
1772
1773INFO : (01-10 08:56:24) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query098
1774 (183.3ms) insert into buffer_combined_availabilities_2013398260 (
1775 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
1776 )
1777 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
1778 sc.linkage_instance_id, 0 as has_violations
1779 from buffer_schedules_2013398260 as sc
1780 /* Static availability */
1781 inner join buffer_static_availabilities_2013398260 as sa
1782 on sc.id = sa.schedule_id
1783 inner join buffer_linkages_2013398260 as l
1784 on sc.linkage_instance_id = l.linkage_instance_id
1785 where sc.should_be_evaluated = 1
1786 and l.has_conflicts = 0
1787
1788 (172.8ms) insert into buffer_schedule_linkages_2013398260 (
1789 staff_id, linkage_instance_id, slots_count
1790 )
1791 select staff_id, linkage_instance_id, count(1) as slots_count
1792 from buffer_schedules_2013398260 use index (ix_buffer_schedules_composite_2)
1793 where staff_id is not null
1794 and linkage_instance_id >= 0
1795 group by staff_id, linkage_instance_id
1796 order by null
1797
1798INFO : (01-10 08:56:24) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query09_09_AL
1799 (183.6ms) insert into buffer_assignment_limits_2013398260 (
1800 id,
1801 name,
1802 calendar_schedule_id,
1803 considered_days_of_week_mask,
1804 limit_type,
1805 limit_subtypes_mask,
1806 timeframe_type,
1807 max_per_period_limit,
1808 max_weekly_limit,
1809 max_rolling_period,
1810 max_rolling_limit,
1811 max_consecutive_limit
1812 )
1813 select
1814 id,
1815 name,
1816 calendar_schedule_id,
1817 considered_days_of_week_mask,
1818 limit_type,
1819 (
1820 (coalesce(max_per_period_limit, -1) >= 0) * 1 +
1821 (coalesce(max_weekly_limit, -1) >= 0) * 2 +
1822 (
1823 coalesce(max_rolling_period, -1) > 0
1824 and coalesce(max_rolling_limit, -1) >= 0
1825 ) * 4 +
1826 (coalesce(max_consecutive_limit, -1) > 0) * 8
1827 ) as limit_subtypes_mask,
1828 timeframe_type,
1829 max_per_period_limit,
1830 max_weekly_limit,
1831 max_rolling_period,
1832 max_rolling_limit,
1833 max_consecutive_limit
1834 from assignment_limits
1835 where
1836 client_id = 6
1837 and active = 1
1838 and (
1839 max_per_period_limit >= 0
1840 or max_weekly_limit >= 0
1841 or (
1842 max_rolling_period > 0
1843 and max_rolling_limit >= 0
1844 )
1845 or max_consecutive_limit > 0
1846 )
1847 order by null
1848
1849 (171.1ms) select id from buffer_assignment_limits_2013398260 limit 1
1850INFO : (01-10 08:56:25) --> fill: 34 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
1851 (167.8ms) select
1852 coalesce(bit_or(limit_subtypes_mask), 0)
1853 from buffer_assignment_limits_2013398260
1854
1855INFO : (01-10 08:56:25) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
1856INFO : (01-10 08:56:25) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
1857INFO : (01-10 08:56:25) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
1858INFO : (01-10 08:56:25) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
1859INFO : (01-10 08:56:25) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
1860INFO : (01-10 08:56:25) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
1861INFO : (01-10 08:56:25) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Points
1862INFO : (01-10 08:56:25) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
1863INFO : (01-10 08:56:25) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query10
1864 RuleSet Load (179.9ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1547 LIMIT 1
1865 Client Load (185.2ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
1866 (179.3ms) select
1867 coalesce(max(
1868 case
1869 when (ct.max_weekday_assignments > 0) then 1
1870 else 0
1871 end
1872 ), 0) as weekday_flag,
1873 coalesce(max(
1874 case
1875 when (ct.max_weekend_assignments > 0) then 1
1876 else 0
1877 end
1878 ), 0) as weekend_flag
1879 from
1880 buffer_auto_populate_slots_2013398260 as aps
1881 inner join call_types as ct
1882 on aps.call_type_id = ct.id
1883
1884 (178.5ms) /* CallOverlapRule */
1885 delete ca
1886 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
1887 inner join buffer_schedules_2013398260 as sc
1888 on ca.schedule_id = sc.id
1889 left join buffer_staff_daily_counts_2013398260 as sdc use index (ix_buffer_staff_daily_counts_composite_1)
1890 on ca.staff_id = sdc.staff_id
1891 and sc.schedule_on = sdc.schedule_on
1892 and sdc.linkage_instance_id is null
1893 left join buffer_staff_daily_counts_2013398260 as sdc_2 use index (ix_buffer_staff_daily_counts_composite_1)
1894 on ca.staff_id = sdc_2.staff_id
1895 and sc.schedule_on = sdc_2.schedule_on
1896 and sc.linkage_instance_id = sdc_2.linkage_instance_id
1897 where (ifnull(sdc.slots_count, 0) - ifnull(sdc_2.slots_count, 0) + 1) > 2
1898
1899 (172.5ms) /* NegativeLinkRule */
1900 delete ca
1901 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
1902 inner join buffer_negative_relations_2013398260 as nr use index (ix_buffer_negative_relations_composite_1)
1903 on ca.schedule_id = nr.schedule_id
1904 and ca.staff_id = nr.staff_id
1905
1906 (181.5ms) /* SpecializationOverlapRule */
1907 delete ca
1908 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
1909 inner join buffer_schedules_2013398260 as sc
1910 on ca.schedule_id = sc.id
1911 inner join buffer_staff_specializations_2013398260 as ssp
1912 on ca.staff_id = ssp.staff_id
1913 inner join max_applicable_call_types as mact
1914 on ssp.specialization_id = mact.specialization_id
1915 and sc.call_type_id = mact.call_type_id
1916 inner join buffer_specialization_daily_counts_2013398260 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
1917 on ssp.specialization_id = sdc.specialization_id
1918 and sc.schedule_on = sdc.schedule_on
1919 and sdc.staff_id is null
1920 left join buffer_specialization_daily_counts_2013398260 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
1921 on ssp.specialization_id = sdc_2.specialization_id
1922 and sc.schedule_on = sdc_2.schedule_on
1923 and ca.staff_id = sdc_2.staff_id
1924 where sdc.should_be_evaluated = 1
1925 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
1926
1927 (171.4ms) /* BrokenLinkageRule */
1928 truncate table buffer_combined_availability_linkages_2013398260
1929
1930 (183.9ms) insert into buffer_combined_availability_linkages_2013398260 (
1931 staff_id,
1932 linkage_instance_id,
1933 slots_count,
1934 is_primary,
1935 has_violations
1936 )
1937 select
1938 staff_id,
1939 linkage_instance_id,
1940 count(1) as slots_count,
1941 min(is_primary) as is_primary,
1942 max(has_violations) as has_violations
1943 from buffer_combined_availabilities_2013398260 use index (ix_buffer_combined_availabilities_composite_2)
1944 group by
1945 staff_id,
1946 linkage_instance_id
1947 order by null
1948
1949 (176.6ms) delete ca
1950 from
1951 buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
1952 inner join buffer_schedules_2013398260 as sc
1953 on ca.schedule_id = sc.id
1954 inner join buffer_linkages_2013398260 as l
1955 on sc.linkage_instance_id = l.linkage_instance_id
1956 inner join buffer_combined_availability_linkages_2013398260 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
1957 on ca.staff_id = ls_1.staff_id
1958 and sc.linkage_instance_id = ls_1.linkage_instance_id
1959 left join buffer_schedule_linkages_2013398260 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
1960 on ca.staff_id = ls_2.staff_id
1961 and sc.linkage_instance_id = ls_2.linkage_instance_id
1962 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
1963
1964INFO : (01-10 08:56:26) --> fill: 44 - Scheduler::AutoPopulate::Queries::QueryScore02
1965INFO : (01-10 08:56:26) --> fill: 45 - Scheduler::AutoPopulate::Queries::QueryScore03
1966INFO : (01-10 08:56:26) --> fill: 46 - Scheduler::AutoPopulate::Queries::QueryScore04
1967INFO : (01-10 08:56:26) --> fill: 47 - Scheduler::AutoPopulate::Queries::Query11
1968 (173.0ms) update buffer_schedules_2013398260 as sc
1969 left join buffer_combined_availabilities_2013398260 as ca
1970 on sc.id = ca.schedule_id
1971 set sc.should_be_evaluated = 0,
1972 sc.should_be_populated = 0
1973 where sc.should_be_evaluated = 1
1974 and ca.id is null
1975
1976INFO : (01-10 08:56:27) --> fill: 48 - Scheduler::AutoPopulate::Queries::DropTables
1977INFO : (01-10 08:56:27) Setting up lists...
1978 (183.2ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
1979 from buffer_schedules_2013398260 as b_schedules
1980 left join schedules
1981 on schedules.client_id = 6
1982 and schedules.call_type_id = b_schedules.call_type_id
1983 and schedules.schedule_on = b_schedules.schedule_on
1984 where b_schedules.linkage_instance_id != -1
1985 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
1986
1987INFO : (01-10 08:56:27) Begin to populate. Pre-set: 0. Unscheduled: 128.
1988INFO : (01-10 08:56:27) plan unit: [id:201739] [2017-10-01 - OR]
1989INFO : (01-10 08:56:27) plan unit: [id:201740] [2017-10-08 - OR]
1990INFO : (01-10 08:56:27) plan unit: [id:201741] [2017-10-15 - OR]
1991INFO : (01-10 08:56:27) plan unit: [id:201742] [2017-10-22 - OR]
1992INFO : (01-10 08:56:27) plan unit: [id:201743] [2017-10-29 - OR]
1993INFO : (01-10 08:56:27) plan unit: [id:1201739] [2017-10-01 - OB]
1994INFO : (01-10 08:56:27) plan unit: [id:1201740] [2017-10-08 - OB]
1995INFO : (01-10 08:56:27) plan unit: [id:1201741] [2017-10-15 - OB]
1996INFO : (01-10 08:56:27) plan unit: [id:1201742] [2017-10-22 - OB]
1997INFO : (01-10 08:56:27) plan unit: [id:1201743] [2017-10-29 - OB]
1998INFO : (01-10 08:56:27) plan unit: [id:2201740] [2017-10-02 - OR]
1999INFO : (01-10 08:56:27) plan unit: [id:2201741] [2017-10-09 - OR]
2000INFO : (01-10 08:56:27) plan unit: [id:2201742] [2017-10-16 - OR]
2001INFO : (01-10 08:56:27) plan unit: [id:2201743] [2017-10-23 - OR]
2002INFO : (01-10 08:56:27) plan unit: [id:2201744] [2017-10-30 - OR]
2003INFO : (01-10 08:56:27) plan unit: [id:3201740] [2017-10-02 - BU]
2004INFO : (01-10 08:56:27) plan unit: [id:3201741] [2017-10-09 - BU]
2005INFO : (01-10 08:56:27) plan unit: [id:3201742] [2017-10-16 - BU]
2006INFO : (01-10 08:56:27) plan unit: [id:3201743] [2017-10-23 - BU]
2007INFO : (01-10 08:56:27) plan unit: [id:3201744] [2017-10-30 - BU]
2008INFO : (01-10 08:56:27) plan unit: [id:4201740] [2017-10-02 - OB]
2009INFO : (01-10 08:56:27) plan unit: [id:4201741] [2017-10-09 - OB]
2010INFO : (01-10 08:56:27) plan unit: [id:4201742] [2017-10-16 - OB]
2011INFO : (01-10 08:56:27) plan unit: [id:4201743] [2017-10-23 - OB]
2012INFO : (01-10 08:56:27) plan unit: [id:4201744] [2017-10-30 - OB]
2013INFO : (01-10 08:56:27) plan unit: [id:5201740] [2017-10-02 - GI1]
2014INFO : (01-10 08:56:27) plan unit: [id:5201741] [2017-10-09 - GI1]
2015INFO : (01-10 08:56:27) plan unit: [id:5201742] [2017-10-16 - GI1]
2016INFO : (01-10 08:56:27) plan unit: [id:5201743] [2017-10-23 - GI1]
2017INFO : (01-10 08:56:27) plan unit: [id:5201744] [2017-10-30 - GI1]
2018INFO : (01-10 08:56:27) plan unit: [id:6201740] [2017-10-02 - PR]
2019INFO : (01-10 08:56:27) plan unit: [id:6201741] [2017-10-09 - PR]
2020INFO : (01-10 08:56:27) plan unit: [id:6201742] [2017-10-16 - PR]
2021INFO : (01-10 08:56:27) plan unit: [id:6201743] [2017-10-23 - PR]
2022INFO : (01-10 08:56:27) plan unit: [id:6201744] [2017-10-30 - PR]
2023INFO : (01-10 08:56:27) plan unit: [id:11201740] [2017-10-03 - OR]
2024INFO : (01-10 08:56:27) plan unit: [id:11201741] [2017-10-10 - OR]
2025INFO : (01-10 08:56:27) plan unit: [id:11201742] [2017-10-17 - OR]
2026INFO : (01-10 08:56:27) plan unit: [id:11201743] [2017-10-24 - OR]
2027INFO : (01-10 08:56:27) plan unit: [id:11201744] [2017-10-31 - OR]
2028INFO : (01-10 08:56:27) plan unit: [id:12201740] [2017-10-03 - BU]
2029INFO : (01-10 08:56:27) plan unit: [id:12201741] [2017-10-10 - BU]
2030INFO : (01-10 08:56:27) plan unit: [id:12201742] [2017-10-17 - BU]
2031INFO : (01-10 08:56:27) plan unit: [id:12201743] [2017-10-24 - BU]
2032INFO : (01-10 08:56:27) plan unit: [id:12201744] [2017-10-31 - BU]
2033INFO : (01-10 08:56:27) plan unit: [id:13201740] [2017-10-03 - OB]
2034INFO : (01-10 08:56:27) plan unit: [id:13201741] [2017-10-10 - OB]
2035INFO : (01-10 08:56:27) plan unit: [id:13201742] [2017-10-17 - OB]
2036INFO : (01-10 08:56:27) plan unit: [id:13201743] [2017-10-24 - OB]
2037INFO : (01-10 08:56:27) plan unit: [id:13201744] [2017-10-31 - OB]
2038INFO : (01-10 08:56:27) plan unit: [id:14201740] [2017-10-03 - GI1]
2039INFO : (01-10 08:56:27) plan unit: [id:14201741] [2017-10-10 - GI1]
2040INFO : (01-10 08:56:27) plan unit: [id:14201742] [2017-10-17 - GI1]
2041INFO : (01-10 08:56:27) plan unit: [id:14201743] [2017-10-24 - GI1]
2042INFO : (01-10 08:56:27) plan unit: [id:14201744] [2017-10-31 - GI1]
2043INFO : (01-10 08:56:27) plan unit: [id:15201740] [2017-10-03 - PR]
2044INFO : (01-10 08:56:27) plan unit: [id:15201741] [2017-10-10 - PR]
2045INFO : (01-10 08:56:27) plan unit: [id:15201742] [2017-10-17 - PR]
2046INFO : (01-10 08:56:27) plan unit: [id:15201743] [2017-10-24 - PR]
2047INFO : (01-10 08:56:27) plan unit: [id:15201744] [2017-10-31 - PR]
2048INFO : (01-10 08:56:27) plan unit: [id:20201740] [2017-10-04 - OR]
2049INFO : (01-10 08:56:27) plan unit: [id:20201741] [2017-10-11 - OR]
2050INFO : (01-10 08:56:27) plan unit: [id:20201742] [2017-10-18 - OR]
2051INFO : (01-10 08:56:27) plan unit: [id:20201743] [2017-10-25 - OR]
2052INFO : (01-10 08:56:27) plan unit: [id:21201740] [2017-10-04 - BU]
2053INFO : (01-10 08:56:27) plan unit: [id:21201741] [2017-10-11 - BU]
2054INFO : (01-10 08:56:27) plan unit: [id:21201742] [2017-10-18 - BU]
2055INFO : (01-10 08:56:27) plan unit: [id:21201743] [2017-10-25 - BU]
2056INFO : (01-10 08:56:27) plan unit: [id:22201740] [2017-10-04 - OB]
2057INFO : (01-10 08:56:27) plan unit: [id:22201741] [2017-10-11 - OB]
2058INFO : (01-10 08:56:27) plan unit: [id:22201742] [2017-10-18 - OB]
2059INFO : (01-10 08:56:27) plan unit: [id:22201743] [2017-10-25 - OB]
2060INFO : (01-10 08:56:27) plan unit: [id:23201740] [2017-10-04 - GI1]
2061INFO : (01-10 08:56:27) plan unit: [id:23201741] [2017-10-11 - GI1]
2062INFO : (01-10 08:56:27) plan unit: [id:23201742] [2017-10-18 - GI1]
2063INFO : (01-10 08:56:27) plan unit: [id:23201743] [2017-10-25 - GI1]
2064INFO : (01-10 08:56:27) plan unit: [id:24201740] [2017-10-04 - PR]
2065INFO : (01-10 08:56:27) plan unit: [id:24201741] [2017-10-11 - PR]
2066INFO : (01-10 08:56:27) plan unit: [id:24201742] [2017-10-18 - PR]
2067INFO : (01-10 08:56:27) plan unit: [id:24201743] [2017-10-25 - PR]
2068INFO : (01-10 08:56:27) plan unit: [id:29201740] [2017-10-05 - OR]
2069INFO : (01-10 08:56:27) plan unit: [id:29201741] [2017-10-12 - OR]
2070INFO : (01-10 08:56:27) plan unit: [id:29201742] [2017-10-19 - OR]
2071INFO : (01-10 08:56:27) plan unit: [id:29201743] [2017-10-26 - OR]
2072INFO : (01-10 08:56:27) plan unit: [id:30201740] [2017-10-05 - BU]
2073INFO : (01-10 08:56:27) plan unit: [id:30201741] [2017-10-12 - BU]
2074INFO : (01-10 08:56:27) plan unit: [id:30201742] [2017-10-19 - BU]
2075INFO : (01-10 08:56:27) plan unit: [id:30201743] [2017-10-26 - BU]
2076INFO : (01-10 08:56:27) plan unit: [id:31201740] [2017-10-05 - OB]
2077INFO : (01-10 08:56:27) plan unit: [id:31201741] [2017-10-12 - OB]
2078INFO : (01-10 08:56:27) plan unit: [id:31201742] [2017-10-19 - OB]
2079INFO : (01-10 08:56:27) plan unit: [id:31201743] [2017-10-26 - OB]
2080INFO : (01-10 08:56:27) plan unit: [id:32201740] [2017-10-05 - GI1]
2081INFO : (01-10 08:56:27) plan unit: [id:32201741] [2017-10-12 - GI1]
2082INFO : (01-10 08:56:27) plan unit: [id:32201742] [2017-10-19 - GI1]
2083INFO : (01-10 08:56:27) plan unit: [id:32201743] [2017-10-26 - GI1]
2084INFO : (01-10 08:56:27) plan unit: [id:33201740] [2017-10-05 - PR]
2085INFO : (01-10 08:56:27) plan unit: [id:33201741] [2017-10-12 - PR]
2086INFO : (01-10 08:56:27) plan unit: [id:33201742] [2017-10-19 - PR]
2087INFO : (01-10 08:56:27) plan unit: [id:33201743] [2017-10-26 - PR]
2088INFO : (01-10 08:56:27) plan unit: [id:38201740] [2017-10-06 - OR]
2089INFO : (01-10 08:56:27) plan unit: [id:38201741] [2017-10-13 - OR]
2090INFO : (01-10 08:56:27) plan unit: [id:38201742] [2017-10-20 - OR]
2091INFO : (01-10 08:56:27) plan unit: [id:38201743] [2017-10-27 - OR]
2092INFO : (01-10 08:56:27) plan unit: [id:39201740] [2017-10-06 - BU]
2093INFO : (01-10 08:56:27) plan unit: [id:39201741] [2017-10-13 - BU]
2094INFO : (01-10 08:56:27) plan unit: [id:39201742] [2017-10-20 - BU]
2095INFO : (01-10 08:56:27) plan unit: [id:39201743] [2017-10-27 - BU]
2096INFO : (01-10 08:56:27) plan unit: [id:40201740] [2017-10-06 - OB]
2097INFO : (01-10 08:56:27) plan unit: [id:40201741] [2017-10-13 - OB]
2098INFO : (01-10 08:56:27) plan unit: [id:40201742] [2017-10-20 - OB]
2099INFO : (01-10 08:56:27) plan unit: [id:40201743] [2017-10-27 - OB]
2100INFO : (01-10 08:56:27) plan unit: [id:41201740] [2017-10-06 - GI1]
2101INFO : (01-10 08:56:27) plan unit: [id:41201741] [2017-10-13 - GI1]
2102INFO : (01-10 08:56:27) plan unit: [id:41201742] [2017-10-20 - GI1]
2103INFO : (01-10 08:56:27) plan unit: [id:41201743] [2017-10-27 - GI1]
2104INFO : (01-10 08:56:27) plan unit: [id:42201740] [2017-10-06 - PR]
2105INFO : (01-10 08:56:27) plan unit: [id:42201741] [2017-10-13 - PR]
2106INFO : (01-10 08:56:27) plan unit: [id:42201742] [2017-10-20 - PR]
2107INFO : (01-10 08:56:27) plan unit: [id:42201743] [2017-10-27 - PR]
2108INFO : (01-10 08:56:27) plan unit: [id:47201740] [2017-10-07 - OR]
2109INFO : (01-10 08:56:27) plan unit: [id:47201741] [2017-10-14 - OR]
2110INFO : (01-10 08:56:27) plan unit: [id:47201742] [2017-10-21 - OR]
2111INFO : (01-10 08:56:27) plan unit: [id:47201743] [2017-10-28 - OR]
2112INFO : (01-10 08:56:27) plan unit: [id:48201740] [2017-10-07 - OB]
2113INFO : (01-10 08:56:27) plan unit: [id:48201741] [2017-10-14 - OB]
2114INFO : (01-10 08:56:27) plan unit: [id:48201742] [2017-10-21 - OB]
2115INFO : (01-10 08:56:27) plan unit: [id:48201743] [2017-10-28 - OB]
2116 (172.8ms) select
2117 coalesce(max(
2118 case
2119 when (ct.max_weekday_assignments > 0) then 1
2120 else 0
2121 end
2122 ), 0) as weekday_flag,
2123 coalesce(max(
2124 case
2125 when (ct.max_weekend_assignments > 0) then 1
2126 else 0
2127 end
2128 ), 0) as weekend_flag
2129 from
2130 buffer_auto_populate_slots_2013398260 as aps
2131 inner join call_types as ct
2132 on aps.call_type_id = ct.id
2133
2134 (176.4ms) /* CallOverlapRule */
2135 delete ca
2136 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2137 inner join buffer_schedules_2013398260 as sc
2138 on ca.schedule_id = sc.id
2139 left join buffer_staff_daily_counts_2013398260 as sdc use index (ix_buffer_staff_daily_counts_composite_1)
2140 on ca.staff_id = sdc.staff_id
2141 and sc.schedule_on = sdc.schedule_on
2142 and sdc.linkage_instance_id is null
2143 left join buffer_staff_daily_counts_2013398260 as sdc_2 use index (ix_buffer_staff_daily_counts_composite_1)
2144 on ca.staff_id = sdc_2.staff_id
2145 and sc.schedule_on = sdc_2.schedule_on
2146 and sc.linkage_instance_id = sdc_2.linkage_instance_id
2147 where (ifnull(sdc.slots_count, 0) - ifnull(sdc_2.slots_count, 0) + 1) > 2
2148
2149 (171.3ms) /* NegativeLinkRule */
2150 delete ca
2151 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2152 inner join buffer_negative_relations_2013398260 as nr use index (ix_buffer_negative_relations_composite_1)
2153 on ca.schedule_id = nr.schedule_id
2154 and ca.staff_id = nr.staff_id
2155
2156 (181.0ms) /* SpecializationOverlapRule */
2157 delete ca
2158 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2159 inner join buffer_schedules_2013398260 as sc
2160 on ca.schedule_id = sc.id
2161 inner join buffer_staff_specializations_2013398260 as ssp
2162 on ca.staff_id = ssp.staff_id
2163 inner join max_applicable_call_types as mact
2164 on ssp.specialization_id = mact.specialization_id
2165 and sc.call_type_id = mact.call_type_id
2166 inner join buffer_specialization_daily_counts_2013398260 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2167 on ssp.specialization_id = sdc.specialization_id
2168 and sc.schedule_on = sdc.schedule_on
2169 and sdc.staff_id is null
2170 left join buffer_specialization_daily_counts_2013398260 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2171 on ssp.specialization_id = sdc_2.specialization_id
2172 and sc.schedule_on = sdc_2.schedule_on
2173 and ca.staff_id = sdc_2.staff_id
2174 where sdc.should_be_evaluated = 1
2175 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2176
2177 (171.3ms) /* BrokenLinkageRule */
2178 truncate table buffer_combined_availability_linkages_2013398260
2179
2180 (180.5ms) insert into buffer_combined_availability_linkages_2013398260 (
2181 staff_id,
2182 linkage_instance_id,
2183 slots_count,
2184 is_primary,
2185 has_violations
2186 )
2187 select
2188 staff_id,
2189 linkage_instance_id,
2190 count(1) as slots_count,
2191 min(is_primary) as is_primary,
2192 max(has_violations) as has_violations
2193 from buffer_combined_availabilities_2013398260 use index (ix_buffer_combined_availabilities_composite_2)
2194 group by
2195 staff_id,
2196 linkage_instance_id
2197 order by null
2198
2199 (176.9ms) delete ca
2200 from
2201 buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2202 inner join buffer_schedules_2013398260 as sc
2203 on ca.schedule_id = sc.id
2204 inner join buffer_linkages_2013398260 as l
2205 on sc.linkage_instance_id = l.linkage_instance_id
2206 inner join buffer_combined_availability_linkages_2013398260 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2207 on ca.staff_id = ls_1.staff_id
2208 and sc.linkage_instance_id = ls_1.linkage_instance_id
2209 left join buffer_schedule_linkages_2013398260 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2210 on ca.staff_id = ls_2.staff_id
2211 and sc.linkage_instance_id = ls_2.linkage_instance_id
2212 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2213
2214 (200.8ms) select straight_join
2215 sc.schedule_on,
2216 sc.call_type_id,
2217 count(distinct ca.staff_id) as available_members_count,
2218 group_concat(
2219 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
2220 order by ls_1.is_primary desc, ca.staff_id
2221 separator ', '
2222 ) as available_members_ids,
2223 sc.linkage_instance_id,
2224 l.slots_count_total as linkage_slots_count
2225 from buffer_combined_availabilities_2013398260 as ca
2226 inner join buffer_schedules_2013398260 as sc
2227 on ca.schedule_id = sc.id
2228 inner join buffer_linkages_2013398260 as l
2229 on sc.linkage_instance_id = l.linkage_instance_id
2230 inner join buffer_combined_availability_linkages_2013398260 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2231 on ca.staff_id = ls_1.staff_id
2232 and sc.linkage_instance_id = ls_1.linkage_instance_id
2233 and ls_1.has_violations = 0
2234 where sc.should_be_evaluated = 1
2235 and sc.should_be_populated = 1
2236 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
2237 order by available_members_count, sc.schedule_on, sc.display_order
2238 limit 1
2239
2240 (171.8ms) truncate table buffer_rule_scores_2013398260
2241
2242 (170.3ms) truncate table buffer_rule_calc_filters_2013398260
2243
2244 (172.4ms) insert into buffer_rule_calc_filters_2013398260 (
2245 linkage_instance_id,
2246 staff_id
2247 ) values (14201741, 65),(14201741, 68),(14201741, 70),(14201741, 71),(14201741, 73),(14201741, 75),(14201741, 78),(14201741, 79),(14201741, 80),(14201741, 81),(14201741, 6960)
2248
2249 (190.1ms) /* CallOverlapRule */
2250 insert into buffer_rule_scores_2013398260 (
2251 linkage_instance_id,
2252 schedule_id,
2253 staff_id,
2254 rule_name,
2255 value
2256 )
2257 select straight_join
2258 b_schedules.linkage_instance_id,
2259 b_schedules.id as schedule_id,
2260 availabilities.staff_id,
2261 'CallOverlapRule' as rule_name,
2262 (
2263 2 /* CallOverlap limit */ -
2264 case
2265 when (
2266 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0)) > 1 /* CallOverlap limit */
2267 and 2 /* CallOverlap limit */ != 0
2268 ) then 2 /* CallOverlap limit */
2269 else
2270 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0))
2271 end
2272 ) as value
2273 from
2274 buffer_rule_calc_filters_2013398260 as filters
2275 inner join buffer_schedules_2013398260 as b_schedules
2276 on filters.linkage_instance_id = b_schedules.linkage_instance_id
2277 inner join buffer_combined_availabilities_2013398260 as availabilities
2278 on b_schedules.id = availabilities.schedule_id
2279 and filters.staff_id = availabilities.staff_id
2280 left join buffer_staff_daily_counts_2013398260 as daily_counts
2281 on availabilities.staff_id = daily_counts.staff_id
2282 and b_schedules.schedule_on = daily_counts.schedule_on
2283 and daily_counts.linkage_instance_id is null
2284 left join buffer_staff_daily_counts_2013398260 as daily_counts_2
2285 on availabilities.staff_id = daily_counts_2.staff_id
2286 and b_schedules.schedule_on = daily_counts_2.schedule_on
2287 and b_schedules.linkage_instance_id = daily_counts_2.linkage_instance_id
2288 order by null
2289
2290 (171.1ms) truncate table buffer_rule_scores_with_corrections_2013398260
2291
2292 (177.5ms) insert into buffer_rule_scores_with_corrections_2013398260 (
2293 linkage_instance_id, schedule_id, staff_id, rule_name, value
2294 )
2295 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2296 ifnull(rule_scores.value, max_values.max_value + 1) as value
2297 from buffer_rule_scores_2013398260 as rule_scores
2298 inner join (
2299 select rule_name, max(value) as max_value
2300 from buffer_rule_scores_2013398260
2301 group by rule_name
2302 order by null
2303 ) as max_values
2304 on rule_scores.rule_name = max_values.rule_name
2305 order by null
2306
2307 (180.2ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2308 from (
2309 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2310 sum(
2311 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2312 rules.weight *
2313 total_values.slots_count
2314 ) as slot_scores
2315 from buffer_rule_scores_with_corrections_2013398260 as rule_scores
2316 inner join (
2317 select rule_name, sum(abs(value)) as total_value,
2318 count(distinct schedule_id) as slots_count
2319 from buffer_rule_scores_with_corrections_2013398260
2320 group by rule_name
2321 order by null
2322 ) as total_values
2323 on rule_scores.rule_name = total_values.rule_name
2324 inner join rules
2325 on rule_scores.rule_name = rules.type
2326 where rules.rule_set_id = 1547
2327 and rules.applied = 1
2328 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2329 order by null
2330 ) as staff_scores
2331 group by linkage_instance_id, staff_id
2332 order by round(avg(slot_scores), 5) desc, rand()
2333 limit 1
2334
2335 PublishedBlock Load (174.0ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 6 AND ('2017-10-10' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 6 ORDER BY `published_blocks`.`id` ASC LIMIT 1
2336 (167.1ms) BEGIN
2337 (186.2ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
2338 FROM call_types AS ct
2339 INNER JOIN calendar_schedules AS cs
2340 ON ct.calendar_schedule_id = cs.id
2341 INNER JOIN blocks AS b
2342 ON b.client_id = ct.client_id
2343 AND b.block_schedule_id = cs.block_schedule_id
2344 AND '2017-10-10' BETWEEN b.start_date AND b.end_date
2345 INNER JOIN published_blocks AS pb
2346 ON pb.client_id = ct.client_id
2347 AND pb.block_id = b.id
2348 AND pb.calendar_schedule_id = cs.id
2349 LEFT JOIN published_block_call_types AS pbct
2350 ON pbct.client_id = ct.client_id
2351 AND pbct.assignment_id = ct.id
2352 AND pbct.assignment_type = 'CallType'
2353 AND pbct.block_id = b.id
2354 AND (
2355 (1 << (DAYOFWEEK('2017-10-10') - 1) & pbct.days_of_week_mask) > 0
2356 )
2357 WHERE ct.id = 49
2358 AND (
2359 (
2360 pb.assignment_ids IS NULL
2361 AND (1 << (DAYOFWEEK('2017-10-10') - 1) & pb.days_of_week_mask) > 0
2362 )
2363 OR pbct.id IS NOT NULL
2364 )
2365
2366 CallType Load (178.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 49 LIMIT 1
2367 Client Load (334.8ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
2368 Staff Load (176.6ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 68 LIMIT 1
2369 Schedule Exists (175.7ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 49 AND `schedules`.`client_id` = 6 AND `schedules`.`schedule_on` = '2017-10-10' LIMIT 1
2370 SQL (190.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 (6, 49, 68, '2017-10-10', '2019-01-10 08:56:40', '2019-01-10 08:56:40', 0, 3, 'auto_populate_step', 166)
2371 VisibleAssignment Load (172.9ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 6 AND `visible_assignments`.`date` = '2017-10-10' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 49 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
2372 DailyListSlot Load (171.8ms) SELECT `daily_list_slots`.* FROM `daily_list_slots` WHERE `daily_list_slots`.`schedule_on` = '2017-10-10' AND `daily_list_slots`.`staff_id` = 68 AND `daily_list_slots`.`call_type_id` = 49 ORDER BY daily_list_slots.position asc LIMIT 1
2373 (173.9ms) COMMIT
2374FLUSHED: nil:nil
2375 (185.6ms) UPDATE buffer_schedules_2013398260
2376 SET staff_id = 68,
2377 should_be_populated = 0,
2378 should_be_evaluated = 0
2379 WHERE
2380 call_type_id = 49
2381 AND schedule_on = '2017-10-10'
2382
2383 (179.0ms) delete from buffer_combined_availabilities_2013398260
2384 where linkage_instance_id = 14201741
2385
2386 (170.3ms) UPDATE buffer_rule_calc_schedules_2013398260
2387 SET staff_id = 68
2388 WHERE call_type_id = 49
2389 AND schedule_on = '2017-10-10'
2390
2391 (174.8ms) INSERT INTO buffer_rule_calc_schedules_2013398260
2392 SET staff_id = 68,
2393 call_type_id = 49,
2394 schedule_on = '2017-10-10',
2395 day_of_week_mask = 4
2396
2397 (173.6ms) /* CallOverlapRule */
2398 UPDATE buffer_staff_daily_counts_2013398260
2399 SET slots_count = slots_count + 1
2400 WHERE
2401 staff_id = 68
2402 AND schedule_on = '2017-10-10'
2403 AND linkage_instance_id is null
2404
2405 (188.2ms) INSERT INTO buffer_staff_daily_counts_2013398260
2406 SET slots_count = 1,
2407 staff_id = 68,
2408 schedule_on = '2017-10-10',
2409 linkage_instance_id = null
2410
2411 (186.4ms) /* CallOverlapRule */
2412 UPDATE buffer_staff_daily_counts_2013398260
2413 SET slots_count = slots_count + 1
2414 WHERE
2415 staff_id = 68
2416 AND schedule_on = '2017-10-10'
2417 AND linkage_instance_id = 14201741
2418
2419 (186.8ms) INSERT INTO buffer_staff_daily_counts_2013398260
2420 SET slots_count = 1,
2421 staff_id = 68,
2422 schedule_on = '2017-10-10',
2423 linkage_instance_id = 14201741
2424
2425 (172.2ms) /* NegativeLinkRule */
2426 UPDATE buffer_negative_relations_2013398260
2427 SET staff_id = 68
2428 WHERE affected_by_schedule_id = (
2429 SELECT id
2430 FROM buffer_schedules_2013398260
2431 WHERE schedule_on = '2017-10-10'
2432 AND call_type_id = 49
2433 )
2434
2435 (178.3ms) /* SpecializationOverlapRule */
2436 update buffer_staff_specializations_2013398260 as st_sp
2437 inner join max_applicable_call_types as mact
2438 on st_sp.specialization_id = mact.specialization_id
2439 and mact.call_type_id = 49
2440 inner join buffer_specialization_daily_counts_2013398260 as sp_dc
2441 on st_sp.specialization_id = sp_dc.specialization_id
2442 and sp_dc.staff_id is null
2443 and sp_dc.schedule_on = '2017-10-10'
2444 left join buffer_specialization_daily_counts_2013398260 as sp_dc_2
2445 on st_sp.specialization_id = sp_dc_2.specialization_id
2446 and st_sp.staff_id = sp_dc_2.staff_id
2447 and sp_dc_2.schedule_on = '2017-10-10'
2448 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
2449 where st_sp.staff_id = 68
2450 and sp_dc_2.id is null
2451
2452 (188.9ms) insert into buffer_specialization_daily_counts_2013398260 (
2453 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2454 )
2455 select sp.id as specialization_id,
2456 68 as staff_id,
2457 '2017-10-10' as schedule_on,
2458 1 as staffs_scheduled,
2459 sp.max_scheduled,
2460 sp_dc.should_be_evaluated
2461 from buffer_staff_specializations_2013398260 as st_sp
2462 inner join specializations as sp
2463 on st_sp.specialization_id = sp.id
2464 inner join max_applicable_call_types as mact
2465 on st_sp.specialization_id = mact.specialization_id
2466 and mact.call_type_id = 49
2467 inner join buffer_specialization_daily_counts_2013398260 as sp_dc
2468 on st_sp.specialization_id = sp_dc.specialization_id
2469 and sp_dc.staff_id is null
2470 and sp_dc.schedule_on = '2017-10-10'
2471 left join buffer_specialization_daily_counts_2013398260 as sp_dc_2
2472 on st_sp.specialization_id = sp_dc_2.specialization_id
2473 and st_sp.staff_id = sp_dc_2.staff_id
2474 and sp_dc_2.schedule_on = '2017-10-10'
2475 where st_sp.staff_id = 68
2476 and sp_dc_2.id is null
2477
2478 (170.3ms) /* MaxAssignmentsRule */
2479 delete from buffer_linkage_call_type_assignments_2013398260
2480 where linkage_instance_id = 14201741;
2481
2482 (176.6ms) /* MaxAssignmentsRule */
2483 update
2484 buffer_schedules_2013398260 as sc
2485 inner join call_types as ct
2486 on sc.call_type_id = ct.id
2487 and ct.active = 1
2488 inner join buffer_call_type_assignments_2013398260 as cta
2489 on sc.staff_id = cta.staff_id
2490 and sc.call_type_id = cta.call_type_id
2491 and sc.is_weekend = cta.is_weekend
2492 and sc.period_offset = cta.period_offset
2493 and sc.calendar_schedule_id = cta.calendar_schedule_id
2494 set cta.assignments_count = cta.assignments_count + 1
2495 where
2496 sc.schedule_on = '2017-10-10'
2497 and sc.call_type_id = 49
2498 and sc.staff_id = 68
2499
2500 (178.8ms) insert into buffer_call_type_assignments_2013398260 (
2501 staff_id,
2502 call_type_id,
2503 is_weekend,
2504 period_offset,
2505 calendar_schedule_id,
2506 assignments_count
2507 )
2508 select
2509 sc.staff_id,
2510 sc.call_type_id,
2511 sc.is_weekend,
2512 sc.period_offset,
2513 sc.calendar_schedule_id,
2514 1 as assignments_count
2515 from buffer_schedules_2013398260 as sc
2516 where
2517 sc.staff_id = 68
2518 and sc.schedule_on = '2017-10-10'
2519 and sc.call_type_id = 49
2520
2521 (171.4ms) /* UnassignedDaysRule */
2522 delete from buffer_linkage_assigned_days_2013398260
2523 where linkage_instance_id = 14201741
2524
2525 (173.5ms) /* UnassignedDaysRule */
2526 UPDATE buffer_staff_assigned_days_2013398260 as sad
2527 inner join call_types as ct
2528 on ct.id = 49
2529 and ct.unassigned_day = 0
2530 SET sad.is_assigned = 1
2531 WHERE sad.staff_id = 68
2532 AND sad.schedule_on = '2017-10-10'
2533
2534 (169.8ms) delete
2535 from buffer_linkage_assignment_limits_2013398260
2536 where
2537 staff_id = 68
2538 and linkage_instance_id = 14201741
2539
2540 (200.2ms) insert into buffer_staff_assignment_limits_2013398260 (
2541 staff_id,
2542 assignment_limit_id,
2543 period_id,
2544 consecutive_days_mask,
2545 current_value
2546 )
2547 select
2548 sc.staff_id,
2549 al.id as assignment_limit_id,
2550 alp.id as period_id,
2551 case
2552 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2553 (1 << datediff(sc.schedule_on, alp.start_date))
2554 else 0
2555 end as consecutive_days_mask,
2556 case
2557 when (al.limit_type = 0 /* assignments */) then 1.0
2558 when (al.limit_type = 1 /* points */) then ci.points
2559 when (al.limit_type = 2 /* hours */) then ci.hours
2560 else 0
2561 end as current_value
2562 from
2563 buffer_schedules_2013398260 as sc
2564 inner join buffer_assignment_limit_call_types_2013398260 as alct
2565 on sc.call_type_id = alct.call_type_id
2566 inner join buffer_assignment_limits_2013398260 as al
2567 on alct.assignment_limit_id = al.id
2568 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2569 inner join buffer_assignment_limit_staffs_2013398260 as als
2570 on al.id = als.assignment_limit_id
2571 and sc.staff_id = als.staff_id
2572 inner join assign_staff_types as ast
2573 on sc.staff_id = ast.staff_id
2574 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2575 inner join buffer_assignment_limit_staff_types_2013398260 as alst
2576 on al.id = alst.assignment_limit_id
2577 and ast.staff_type_id = alst.staff_type_id
2578 inner join buffer_assignment_limit_periods_2013398260 as alp
2579 on al.id = alp.assignment_limit_id
2580 and sc.schedule_on between alp.start_date and alp.end_date
2581 inner join buffer_counter_increments_2013398260 as ci
2582 on sc.id = ci.schedule_id
2583 where
2584 sc.schedule_on = '2017-10-10'
2585 and sc.call_type_id = 49
2586 and sc.staff_id = 68
2587 on duplicate key update
2588 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
2589 current_value = current_value + values(current_value)
2590
2591INFO : (01-10 08:56:45) scheduled for: plan unit: [id:14201741] [2017-10-10 - GI1], Andrew Church. DEPTH(1)
2592 (184.2ms) select
2593 coalesce(max(
2594 case
2595 when (ct.max_weekday_assignments > 0) then 1
2596 else 0
2597 end
2598 ), 0) as weekday_flag,
2599 coalesce(max(
2600 case
2601 when (ct.max_weekend_assignments > 0) then 1
2602 else 0
2603 end
2604 ), 0) as weekend_flag
2605 from
2606 buffer_auto_populate_slots_2013398260 as aps
2607 inner join call_types as ct
2608 on aps.call_type_id = ct.id
2609
2610 (178.3ms) /* CallOverlapRule */
2611 delete ca
2612 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2613 inner join buffer_schedules_2013398260 as sc
2614 on ca.schedule_id = sc.id
2615 left join buffer_staff_daily_counts_2013398260 as sdc use index (ix_buffer_staff_daily_counts_composite_1)
2616 on ca.staff_id = sdc.staff_id
2617 and sc.schedule_on = sdc.schedule_on
2618 and sdc.linkage_instance_id is null
2619 left join buffer_staff_daily_counts_2013398260 as sdc_2 use index (ix_buffer_staff_daily_counts_composite_1)
2620 on ca.staff_id = sdc_2.staff_id
2621 and sc.schedule_on = sdc_2.schedule_on
2622 and sc.linkage_instance_id = sdc_2.linkage_instance_id
2623 where (ifnull(sdc.slots_count, 0) - ifnull(sdc_2.slots_count, 0) + 1) > 2
2624
2625 (174.7ms) /* NegativeLinkRule */
2626 delete ca
2627 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2628 inner join buffer_negative_relations_2013398260 as nr use index (ix_buffer_negative_relations_composite_1)
2629 on ca.schedule_id = nr.schedule_id
2630 and ca.staff_id = nr.staff_id
2631
2632 (180.4ms) /* SpecializationOverlapRule */
2633 delete ca
2634 from buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2635 inner join buffer_schedules_2013398260 as sc
2636 on ca.schedule_id = sc.id
2637 inner join buffer_staff_specializations_2013398260 as ssp
2638 on ca.staff_id = ssp.staff_id
2639 inner join max_applicable_call_types as mact
2640 on ssp.specialization_id = mact.specialization_id
2641 and sc.call_type_id = mact.call_type_id
2642 inner join buffer_specialization_daily_counts_2013398260 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2643 on ssp.specialization_id = sdc.specialization_id
2644 and sc.schedule_on = sdc.schedule_on
2645 and sdc.staff_id is null
2646 left join buffer_specialization_daily_counts_2013398260 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2647 on ssp.specialization_id = sdc_2.specialization_id
2648 and sc.schedule_on = sdc_2.schedule_on
2649 and ca.staff_id = sdc_2.staff_id
2650 where sdc.should_be_evaluated = 1
2651 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2652
2653 (178.5ms) /* BrokenLinkageRule */
2654 truncate table buffer_combined_availability_linkages_2013398260
2655
2656 (180.4ms) insert into buffer_combined_availability_linkages_2013398260 (
2657 staff_id,
2658 linkage_instance_id,
2659 slots_count,
2660 is_primary,
2661 has_violations
2662 )
2663 select
2664 staff_id,
2665 linkage_instance_id,
2666 count(1) as slots_count,
2667 min(is_primary) as is_primary,
2668 max(has_violations) as has_violations
2669 from buffer_combined_availabilities_2013398260 use index (ix_buffer_combined_availabilities_composite_2)
2670 group by
2671 staff_id,
2672 linkage_instance_id
2673 order by null
2674
2675 (179.3ms) delete ca
2676 from
2677 buffer_combined_availabilities_2013398260 as ca use index (ix_buffer_combined_availabilities_composite_1)
2678 inner join buffer_schedules_2013398260 as sc
2679 on ca.schedule_id = sc.id
2680 inner join buffer_linkages_2013398260 as l
2681 on sc.linkage_instance_id = l.linkage_instance_id
2682 inner join buffer_combined_availability_linkages_2013398260 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2683 on ca.staff_id = ls_1.staff_id
2684 and sc.linkage_instance_id = ls_1.linkage_instance_id
2685 left join buffer_schedule_linkages_2013398260 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2686 on ca.staff_id = ls_2.staff_id
2687 and sc.linkage_instance_id = ls_2.linkage_instance_id
2688 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2689
2690 (203.1ms) select straight_join
2691 sc.schedule_on,
2692 sc.call_type_id,
2693 count(distinct ca.staff_id) as available_members_count,
2694 group_concat(
2695 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
2696 order by ls_1.is_primary desc, ca.staff_id
2697 separator ', '
2698 ) as available_members_ids,
2699 sc.linkage_instance_id,
2700 l.slots_count_total as linkage_slots_count
2701 from buffer_combined_availabilities_2013398260 as ca
2702 inner join buffer_schedules_2013398260 as sc
2703 on ca.schedule_id = sc.id
2704 inner join buffer_linkages_2013398260 as l
2705 on sc.linkage_instance_id = l.linkage_instance_id
2706 inner join buffer_combined_availability_linkages_2013398260 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2707 on ca.staff_id = ls_1.staff_id
2708 and sc.linkage_instance_id = ls_1.linkage_instance_id
2709 and ls_1.has_violations = 0
2710 where sc.should_be_evaluated = 1
2711 and sc.should_be_populated = 1
2712 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
2713 order by available_members_count, sc.schedule_on, sc.display_order
2714 limit 1
2715
2716 (169.1ms) truncate table buffer_rule_scores_2013398260
2717
2718 (170.6ms) truncate table buffer_rule_calc_filters_2013398260
2719
2720 (174.3ms) insert into buffer_rule_calc_filters_2013398260 (
2721 linkage_instance_id,
2722 staff_id
2723 ) values (23201740, 65),(23201740, 68),(23201740, 70),(23201740, 71),(23201740, 72),(23201740, 73),(23201740, 75),(23201740, 78),(23201740, 79),(23201740, 80),(23201740, 81),(23201740, 6960)
2724
2725 (190.1ms) /* CallOverlapRule */
2726 insert into buffer_rule_scores_2013398260 (
2727 linkage_instance_id,
2728 schedule_id,
2729 staff_id,
2730 rule_name,
2731 value
2732 )
2733 select straight_join
2734 b_schedules.linkage_instance_id,
2735 b_schedules.id as schedule_id,
2736 availabilities.staff_id,
2737 'CallOverlapRule' as rule_name,
2738 (
2739 2 /* CallOverlap limit */ -
2740 case
2741 when (
2742 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0)) > 1 /* CallOverlap limit */
2743 and 2 /* CallOverlap limit */ != 0
2744 ) then 2 /* CallOverlap limit */
2745 else
2746 (coalesce(daily_counts.slots_count, 0) - coalesce(daily_counts_2.slots_count, 0))
2747 end
2748 ) as value
2749 from
2750 buffer_rule_calc_filters_2013398260 as filters
2751 inner join buffer_schedules_2013398260 as b_schedules
2752 on filters.linkage_instance_id = b_schedules.linkage_instance_id
2753 inner join buffer_combined_availabilities_2013398260 as availabilities
2754 on b_schedules.id = availabilities.schedule_id
2755 and filters.staff_id = availabilities.staff_id
2756 left join buffer_staff_daily_counts_2013398260 as daily_counts
2757 on availabilities.staff_id = daily_counts.staff_id
2758 and b_schedules.schedule_on = daily_counts.schedule_on
2759 and daily_counts.linkage_instance_id is null
2760 left join buffer_staff_daily_counts_2013398260 as daily_counts_2
2761 on availabilities.staff_id = daily_counts_2.staff_id
2762 and b_schedules.schedule_on = daily_counts_2.schedule_on
2763 and b_schedules.linkage_instance_id = daily_counts_2.linkage_instance_id
2764 order by null
2765
2766 (168.8ms) truncate table buffer_rule_scores_with_corrections_2013398260
2767
2768 (179.7ms) insert into buffer_rule_scores_with_corrections_2013398260 (
2769 linkage_instance_id, schedule_id, staff_id, rule_name, value
2770 )
2771 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2772 ifnull(rule_scores.value, max_values.max_value + 1) as value
2773 from buffer_rule_scores_2013398260 as rule_scores
2774 inner join (
2775 select rule_name, max(value) as max_value
2776 from buffer_rule_scores_2013398260
2777 group by rule_name
2778 order by null
2779 ) as max_values
2780 on rule_scores.rule_name = max_values.rule_name
2781 order by null
2782
2783 (182.7ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2784 from (
2785 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2786 sum(
2787 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2788 rules.weight *
2789 total_values.slots_count
2790 ) as slot_scores
2791 from buffer_rule_scores_with_corrections_2013398260 as rule_scores
2792 inner join (
2793 select rule_name, sum(abs(value)) as total_value,
2794 count(distinct schedule_id) as slots_count
2795 from buffer_rule_scores_with_corrections_2013398260
2796 group by rule_name
2797 order by null
2798 ) as total_values
2799 on rule_scores.rule_name = total_values.rule_name
2800 inner join rules
2801 on rule_scores.rule_name = rules.type
2802 where rules.rule_set_id = 1547
2803 and rules.applied = 1
2804 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2805 order by null
2806 ) as staff_scores
2807 group by linkage_instance_id, staff_id
2808 order by round(avg(slot_scores), 5) desc, rand()
2809 limit 1