· 7 years ago · Feb 17, 2019, 05:22 PM
1(146.7ms) SET NAMES utf8 COLLATE utf8_unicode_ci, @@SESSION.sql_mode = 'NO_ENGINE_SUBSTITUTION', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2 Client Load (1064.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
3 CalendarSchedule Load (143.6ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 325 AND `calendar_schedules`.`id` = 1396 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
4 CallType Load (985.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (12732, 12733, 12734)
5 CalendarSchedule Load (1003.4ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 325 AND `calendar_schedules`.`id` = 1396 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
6 Block Load (145.1ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 456 AND `blocks`.`id` = 39648 ORDER BY start_date asc, end_date asc LIMIT 1
7 BlockSchedule Load (618.6ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 456 LIMIT 1
8 PopulationTimeframe Load (587.0ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 325 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
9 RuleSet Load (154.5ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1538 LIMIT 1
10 Block Load (151.3ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 456 AND (blocks.start_date >= '2019-08-01' AND blocks.end_date <= '2019-08-31') ORDER BY start_date asc, end_date asc
11INFO : (02-15 13:32:05) phase: Scheduler::Base
12INFO : (02-15 13:32:05) from: 2019-08-01 to: 2019-08-31
13 Rule Load (658.2ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1538 ORDER BY applied desc, weight desc
14INFO : (02-15 13:32:06) rule configurations: Default
15 -
16INFO : (02-15 13:32:06) started: Scheduler::Cache::Collector.collect_all
17INFO : (02-15 13:32:06) collect_staffs
18 Staff Load (304.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` = 325 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY staff_entities.entity_id, staff_entities.display_order, last_name, first_name
19 Client Load (596.4ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` IN (319, 325)
20 StaffSpecialization Load (151.8ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (20702, 20733, 20704, 20739, 20695, 20722, 20740, 20738, 20747)
21 Specialization Load (340.9ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` IN (711, 712) ORDER BY `specializations`.`priority` ASC
22 StaffCallType Load (744.7ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (20702, 20733, 20704, 20739, 20695, 20722, 20740, 20738, 20747)
23INFO : (02-15 13:32:14) initial eligibles: [20702,20733,20704,20739,20695,20722,20740,20738,20747]
24INFO : (02-15 13:32:14) collect_call_types
25 CallType Load (1073.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 325 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
26INFO : (02-15 13:32:15) initial call types: [12732,12733,12734,12753]
27INFO : (02-15 13:32:15) collect_chain_links
28 ChainLink Load (153.0ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 325
29INFO : (02-15 13:32:15) collect_linkages
30INFO : (02-15 13:32:15) collect_schedules
31 Schedule Load (152.4ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 325 AND (`schedules`.`schedule_on` BETWEEN '2019-08-01' AND '2019-08-31')
32INFO : (02-15 13:32:16) collect_slots
33INFO : (02-15 13:32:16) collect_approved_call_requests
34 Request Load (141.9ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 325 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2019-08-01' AND '2019-08-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
35INFO : (02-15 13:32:16) collect_approved_no_call_requests
36 Request Load (183.5ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 325 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2019-08-01' AND '2019-08-31') ORDER BY requests.schedule_on asc, requests.created_at asc
37INFO : (02-15 13:32:16) finished: Scheduler::Cache::Collector.collect_all
38INFO : (02-15 13:32:16) == begin to process requests
39 CallType Load (492.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 1396 AND `call_types`.`active` = 1
40 (146.4ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 1396 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
41 Request Load (155.3ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 325 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (12732, 12733, 12734) AND (requests.schedule_on > '2019-02-28')
42 SQL (162.5ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 20695 WHERE `schedules`.`client_id` = 325 AND 1=0
43INFO : (02-15 13:32:18) == end of requests processing
44 Block Load (152.4ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 456 AND (blocks.start_date <= '2019-08-31' AND blocks.end_date >= '2019-08-31') ORDER BY start_date asc, end_date asc LIMIT 1
45 Block Load (151.4ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-08-01') AND (blocks.id != 39648) ORDER BY start_date desc
46 Client Load (1418.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
47 (498.9ms) SELECT `autopopulate_exclusions`.`schedule_on`, `autopopulate_exclusions`.`call_type_id` FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 325 AND `autopopulate_exclusions`.`schedule_on` IN ('2019-08-01', '2019-08-02', '2019-08-03', '2019-08-04', '2019-08-05', '2019-08-06', '2019-08-07', '2019-08-08', '2019-08-09', '2019-08-10', '2019-08-11', '2019-08-12', '2019-08-13', '2019-08-14', '2019-08-15', '2019-08-16', '2019-08-17', '2019-08-18', '2019-08-19', '2019-08-20', '2019-08-21', '2019-08-22', '2019-08-23', '2019-08-24', '2019-08-25', '2019-08-26', '2019-08-27', '2019-08-28', '2019-08-29', '2019-08-30', '2019-08-31')
48 (141.9ms) select
49 b.start_date,
50 b.end_date,
51 (pb.assignment_ids IS NULL) as fully_published,
52 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
53 pbct.days_of_week_mask
54 from blocks as b
55 inner join published_blocks as pb
56 on pb.block_id = b.id
57 inner join published_block_call_types as pbct
58 on pbct.published_block_id = pb.id
59 where b.client_id = 325
60 and pb.calendar_schedule_id = 1396
61 and b.end_date BETWEEN '2019-08-01' AND '2019-08-31'
62
63INFO : (02-15 13:32:21) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
64INFO : (02-15 13:32:21) <-- clear: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
65INFO : (02-15 13:32:21) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query003
66INFO : (02-15 13:32:21) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query005
67INFO : (02-15 13:32:21) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query01
68INFO : (02-15 13:32:21) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query012
69INFO : (02-15 13:32:21) <-- clear: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
70INFO : (02-15 13:32:21) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query013
71INFO : (02-15 13:32:21) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query014
72INFO : (02-15 13:32:21) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query016
73INFO : (02-15 13:32:21) <-- clear: 10 - Scheduler::AutoPopulate::Queries::Query02
74INFO : (02-15 13:32:21) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query03
75INFO : (02-15 13:32:21) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query04
76INFO : (02-15 13:32:21) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query045
77INFO : (02-15 13:32:21) <-- clear: 14 - Scheduler::AutoPopulate::Queries::QueryA
78INFO : (02-15 13:32:21) <-- clear: 15 - Scheduler::AutoPopulate::Queries::QueryB
79INFO : (02-15 13:32:21) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query05
80INFO : (02-15 13:32:21) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query06
81INFO : (02-15 13:32:21) <-- clear: 18 - Scheduler::AutoPopulate::Queries::Query07
82INFO : (02-15 13:32:21) <-- clear: 19 - Scheduler::AutoPopulate::Queries::Query0702
83INFO : (02-15 13:32:21) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query0705
84INFO : (02-15 13:32:21) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query0706
85INFO : (02-15 13:32:21) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query0707
86INFO : (02-15 13:32:21) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0708
87INFO : (02-15 13:32:21) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0709
88INFO : (02-15 13:32:21) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query08
89INFO : (02-15 13:32:21) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query085
90INFO : (02-15 13:32:21) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query09
91INFO : (02-15 13:32:21) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query092
92INFO : (02-15 13:32:21) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query093
93INFO : (02-15 13:32:21) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query095
94INFO : (02-15 13:32:21) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query097
95INFO : (02-15 13:32:21) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query098
96INFO : (02-15 13:32:21) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query09_09_AL
97INFO : (02-15 13:32:21) <-- clear: 34 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
98INFO : (02-15 13:32:21) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
99INFO : (02-15 13:32:21) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
100INFO : (02-15 13:32:21) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
101INFO : (02-15 13:32:21) <-- clear: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
102INFO : (02-15 13:32:21) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
103INFO : (02-15 13:32:21) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
104INFO : (02-15 13:32:21) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Points
105INFO : (02-15 13:32:21) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
106INFO : (02-15 13:32:21) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query10
107INFO : (02-15 13:32:21) <-- clear: 44 - Scheduler::AutoPopulate::Queries::QueryScore02
108INFO : (02-15 13:32:21) <-- clear: 45 - Scheduler::AutoPopulate::Queries::QueryScore03
109INFO : (02-15 13:32:21) <-- clear: 46 - Scheduler::AutoPopulate::Queries::QueryScore04
110INFO : (02-15 13:32:21) <-- clear: 47 - Scheduler::AutoPopulate::Queries::Query11
111INFO : (02-15 13:32:21) <-- clear: 48 - Scheduler::AutoPopulate::Queries::DropTables
112 (143.3ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_1511818252
113 (148.3ms) DROP TABLE IF EXISTS buffer_annual_point_targets_1511818252
114 (156.3ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_1511818252
115 (521.9ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1511818252
116 (155.0ms) DROP TABLE IF EXISTS buffer_call_days_targets_1511818252
117 (148.5ms) DROP TABLE IF EXISTS buffer_call_type_targets_1511818252
118 (141.9ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_1511818252
119 (151.4ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_1511818252
120 (142.1ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_1511818252
121 (543.4ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_1511818252
122 (154.8ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_1511818252
123 (151.8ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_1511818252
124 (151.4ms) DROP TABLE IF EXISTS buffer_current_cd_targets_1511818252
125 (154.8ms) DROP TABLE IF EXISTS buffer_current_ct_targets_1511818252
126 (154.9ms) DROP TABLE IF EXISTS buffer_current_dow_targets_1511818252
127 (152.4ms) DROP TABLE IF EXISTS buffer_current_hour_targets_1511818252
128 (517.2ms) DROP TABLE IF EXISTS buffer_current_oa_targets_1511818252
129 (143.3ms) DROP TABLE IF EXISTS buffer_current_point_targets_1511818252
130 (149.8ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_1511818252
131 (142.8ms) DROP TABLE IF EXISTS buffer_hour_targets_1511818252
132 (509.5ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_1511818252
133 (153.2ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_1511818252
134 (525.7ms) DROP TABLE IF EXISTS buffer_linkage_targets_1511818252
135 (178.9ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_1511818252
136 (150.7ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_1511818252
137 (148.5ms) DROP TABLE IF EXISTS buffer_location_privileges_1511818252
138 (152.4ms) DROP TABLE IF EXISTS buffer_periods_1511818252
139 (146.4ms) DROP TABLE IF EXISTS buffer_point_targets_1511818252
140 (142.2ms) DROP TABLE IF EXISTS buffer_schedules_1511818252
141 (149.1ms) DROP TABLE IF EXISTS buffer_target_call_types_1511818252
142 (149.0ms) DROP TABLE IF EXISTS buffer_target_daily_points_1511818252
143 (148.8ms) DROP TABLE IF EXISTS buffer_target_dates_list_1511818252
144 (144.6ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_1511818252
145 (156.0ms) DROP TABLE IF EXISTS buffer_targets_1511818252
146 (153.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_1511818252
147 (144.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_1511818252
148 (153.3ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_1511818252
149 (152.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_1511818252
150 (153.9ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_1511818252
151 (147.1ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_1511818252
152 (142.5ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_1511818252
153 (150.7ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_1511818252
154 (152.5ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_1511818252
155 (144.9ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1511818252
156 (145.9ms) DROP TABLE IF EXISTS buffer_call_type_assignments_1511818252
157 (157.8ms) DROP TABLE IF EXISTS buffer_combined_availabilities_1511818252
158 (151.7ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_1511818252
159 (147.6ms) DROP TABLE IF EXISTS buffer_counter_increments_1511818252
160 (144.8ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_1511818252
161 (501.4ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_1511818252
162 (152.2ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_1511818252
163 (152.1ms) DROP TABLE IF EXISTS buffer_linkage_patterns_1511818252
164 (148.0ms) DROP TABLE IF EXISTS buffer_linkages_1511818252
165 (456.9ms) DROP TABLE IF EXISTS buffer_location_privileges_1511818252
166 (154.3ms) DROP TABLE IF EXISTS buffer_min_distances_1511818252
167 (153.4ms) DROP TABLE IF EXISTS buffer_negative_relations_1511818252
168 (148.2ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_1511818252
169 (142.6ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_1511818252
170 (156.0ms) DROP TABLE IF EXISTS buffer_rule_scores_1511818252
171 (152.0ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_1511818252
172 (154.8ms) DROP TABLE IF EXISTS buffer_schedule_linkages_1511818252
173 (153.3ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_1511818252
174 (144.6ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_1511818252
175 (152.9ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_1511818252
176 (144.3ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_1511818252
177 (887.1ms) DROP TABLE IF EXISTS buffer_staff_specializations_1511818252
178 (509.6ms) DROP TABLE IF EXISTS buffer_static_availabilities_1511818252
179 (158.2ms) DROP TABLE IF EXISTS buffer_target_assignments_1511818252
180 (151.3ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_1511818252
181 (146.4ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_1511818252
182 (153.5ms) DROP TABLE IF EXISTS buffer_assignment_limits_1511818252
183 (154.4ms) delete from buffer_target_dates
184 where session_id = 1511818252
185
186INFO : (02-15 13:32:35) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
187 (148.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_1511818252 LIKE buffer_assignment_limit_call_types
188 (154.2ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_1511818252 LIKE buffer_assignment_limit_periods
189 (151.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_1511818252 LIKE buffer_assignment_limit_staff_types
190 (164.9ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_1511818252 LIKE buffer_assignment_limit_staffs
191 (144.7ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1511818252 LIKE buffer_auto_populate_slots
192 (155.1ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_1511818252 LIKE buffer_call_type_assignments
193 (155.5ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_1511818252 LIKE buffer_combined_availabilities
194 (157.6ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_1511818252 LIKE buffer_combined_availability_linkages
195 (164.0ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_1511818252 LIKE buffer_counter_increments
196 (151.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_1511818252 LIKE buffer_linkage_assigned_days
197 (148.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_1511818252 LIKE buffer_linkage_call_type_assignments
198 (153.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_1511818252 LIKE buffer_linkage_dow_targets
199 (147.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_1511818252 LIKE buffer_linkage_patterns
200 (153.7ms) CREATE TABLE IF NOT EXISTS buffer_linkages_1511818252 LIKE buffer_linkages
201 (152.8ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1511818252 LIKE buffer_location_privileges
202 (143.8ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_1511818252 LIKE buffer_min_distances
203 (147.0ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_1511818252 LIKE buffer_negative_relations
204 (147.6ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_1511818252 LIKE buffer_rule_calc_filters
205 (154.0ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_1511818252 LIKE buffer_rule_calc_schedules
206 (143.2ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_1511818252 LIKE buffer_rule_scores
207 (154.7ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_1511818252 LIKE buffer_rule_scores_with_corrections
208 (514.2ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_1511818252 LIKE buffer_schedule_linkages
209 (156.7ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_1511818252 LIKE buffer_schedules_linkage_patterns
210 (155.3ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_1511818252 LIKE buffer_specialization_daily_counts
211 (190.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_1511818252 LIKE buffer_staff_assigned_days
212 (144.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_1511818252 LIKE buffer_staff_daily_counts
213 (152.8ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_1511818252 LIKE buffer_staff_specializations
214 (153.7ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_1511818252 LIKE buffer_static_availabilities
215 (164.1ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_1511818252 LIKE buffer_target_assignments
216 (147.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_1511818252 LIKE buffer_linkage_assignment_limits
217 (171.0ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_1511818252 LIKE buffer_staff_assignment_limits
218 (145.0ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_1511818252 LIKE buffer_assignment_limits
219 (144.1ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_1511818252 LIKE buffer_annual_hour_targets
220 (148.0ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_1511818252 LIKE buffer_annual_point_targets
221 (156.8ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_1511818252 LIKE buffer_annual_targets_staff_totals
222 (153.5ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1511818252 LIKE buffer_auto_populate_slots
223 (157.6ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_1511818252 LIKE buffer_call_days_targets
224 (156.9ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_1511818252 LIKE buffer_call_type_targets
225 (158.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_1511818252 LIKE buffer_cumulative_cd_targets
226 (507.2ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_1511818252 LIKE buffer_cumulative_ct_targets
227 (152.6ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_1511818252 LIKE buffer_cumulative_dow_targets
228 (156.7ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_1511818252 LIKE buffer_cumulative_hour_targets
229 (152.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_1511818252 LIKE buffer_cumulative_oa_targets
230 (158.9ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_1511818252 LIKE buffer_cumulative_point_targets
231 (154.9ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_1511818252 LIKE buffer_current_cd_targets
232 (151.3ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_1511818252 LIKE buffer_current_ct_targets
233 (155.3ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_1511818252 LIKE buffer_current_dow_targets
234 (155.2ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_1511818252 LIKE buffer_current_hour_targets
235 (160.0ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_1511818252 LIKE buffer_current_oa_targets
236 (155.5ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_1511818252 LIKE buffer_current_point_targets
237 (153.0ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_1511818252 LIKE buffer_day_of_week_targets
238 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_1511818252 LIKE buffer_hour_targets
239 (150.7ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_1511818252 LIKE buffer_linkage_call_days_targets
240 (539.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_1511818252 LIKE buffer_linkage_call_type_targets
241 (144.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_1511818252 LIKE buffer_linkage_targets
242 (165.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_1511818252 LIKE buffer_linkage_hour_targets
243 (166.1ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_1511818252 LIKE buffer_linkage_point_targets
244 (143.1ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1511818252 LIKE buffer_location_privileges
245 (160.5ms) CREATE TABLE IF NOT EXISTS buffer_periods_1511818252 LIKE buffer_periods
246 (155.5ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_1511818252 LIKE buffer_point_targets
247 (151.8ms) CREATE TABLE IF NOT EXISTS buffer_schedules_1511818252 LIKE buffer_schedules
248 (155.5ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_1511818252 LIKE buffer_target_call_types
249 (155.9ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_1511818252 LIKE buffer_target_daily_points
250 (147.6ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_1511818252 LIKE buffer_target_dates_list
251 (462.4ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_1511818252 LIKE buffer_target_primary_availabilities
252 (652.6ms) CREATE TABLE IF NOT EXISTS buffer_targets_1511818252 LIKE buffer_targets
253 (156.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_1511818252 LIKE buffer_tmp_targets_availabilities
254 (144.1ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_1511818252 LIKE buffer_tmp_targets_availabilities_exclusions
255 (1183.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_1511818252 LIKE buffer_tmp_targets_client_totals
256 (145.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_1511818252 LIKE buffer_tmp_targets_scheduled
257 (148.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_1511818252 LIKE buffer_tmp_targets_staff_totals
258INFO : (02-15 13:32:49) --> fill: 1 - Scheduler::AutoPopulate::Queries::AutoPopulateSlots
259 (143.8ms) select
260 b.start_date,
261 b.end_date,
262 (pb.assignment_ids IS NULL) as fully_published,
263 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
264 pbct.days_of_week_mask
265 from blocks as b
266 inner join published_blocks as pb
267 on pb.block_id = b.id
268 inner join published_block_call_types as pbct
269 on pbct.published_block_id = pb.id
270 where b.client_id = 325
271 and pb.calendar_schedule_id = 1396
272 and b.end_date BETWEEN '2019-08-01' AND '2019-08-31'
273
274 AutopopulateExclusion Load (571.6ms) SELECT `autopopulate_exclusions`.* FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 325 AND `autopopulate_exclusions`.`schedule_on` IN ('2019-08-01', '2019-08-02', '2019-08-03', '2019-08-04', '2019-08-05', '2019-08-06', '2019-08-07', '2019-08-08', '2019-08-09', '2019-08-10', '2019-08-11', '2019-08-12', '2019-08-13', '2019-08-14', '2019-08-15', '2019-08-16', '2019-08-17', '2019-08-18', '2019-08-19', '2019-08-20', '2019-08-21', '2019-08-22', '2019-08-23', '2019-08-24', '2019-08-25', '2019-08-26', '2019-08-27', '2019-08-28', '2019-08-29', '2019-08-30', '2019-08-31')
275 (156.6ms) INSERT INTO buffer_auto_populate_slots_1511818252
276 (schedule_on, day_of_week_mask, call_type_id, display_order, should_be_populated)
277 VALUES ('2019-08-01', 16, 12732, 0, 1), ('2019-08-01', 16, 12733, 1, 1), ('2019-08-01', 16, 12734, 2, 1), ('2019-08-02', 32, 12732, 0, 1), ('2019-08-02', 32, 12733, 1, 1), ('2019-08-02', 32, 12734, 2, 1), ('2019-08-03', 64, 12732, 0, 1), ('2019-08-03', 64, 12733, 1, 1), ('2019-08-04', 1, 12732, 0, 1), ('2019-08-04', 1, 12733, 1, 1), ('2019-08-05', 2, 12732, 0, 1), ('2019-08-05', 2, 12733, 1, 1), ('2019-08-05', 2, 12734, 2, 1), ('2019-08-06', 4, 12732, 0, 1), ('2019-08-06', 4, 12733, 1, 1), ('2019-08-06', 4, 12734, 2, 1), ('2019-08-07', 8, 12732, 0, 1), ('2019-08-07', 8, 12733, 1, 1), ('2019-08-07', 8, 12734, 2, 1), ('2019-08-08', 16, 12732, 0, 1), ('2019-08-08', 16, 12733, 1, 1), ('2019-08-08', 16, 12734, 2, 1), ('2019-08-09', 32, 12732, 0, 1), ('2019-08-09', 32, 12733, 1, 1), ('2019-08-09', 32, 12734, 2, 1), ('2019-08-10', 64, 12732, 0, 1), ('2019-08-10', 64, 12733, 1, 1), ('2019-08-11', 1, 12732, 0, 1), ('2019-08-11', 1, 12733, 1, 1), ('2019-08-12', 2, 12732, 0, 1), ('2019-08-12', 2, 12733, 1, 1), ('2019-08-12', 2, 12734, 2, 1), ('2019-08-13', 4, 12732, 0, 1), ('2019-08-13', 4, 12733, 1, 1), ('2019-08-13', 4, 12734, 2, 1), ('2019-08-14', 8, 12732, 0, 1), ('2019-08-14', 8, 12733, 1, 1), ('2019-08-14', 8, 12734, 2, 1), ('2019-08-15', 16, 12732, 0, 1), ('2019-08-15', 16, 12733, 1, 1), ('2019-08-15', 16, 12734, 2, 1), ('2019-08-16', 32, 12732, 0, 1), ('2019-08-16', 32, 12733, 1, 1), ('2019-08-16', 32, 12734, 2, 1), ('2019-08-17', 64, 12732, 0, 1), ('2019-08-17', 64, 12733, 1, 1), ('2019-08-18', 1, 12732, 0, 1), ('2019-08-18', 1, 12733, 1, 1), ('2019-08-19', 2, 12732, 0, 1), ('2019-08-19', 2, 12733, 1, 1), ('2019-08-19', 2, 12734, 2, 1), ('2019-08-20', 4, 12732, 0, 1), ('2019-08-20', 4, 12733, 1, 1), ('2019-08-20', 4, 12734, 2, 1), ('2019-08-21', 8, 12732, 0, 1), ('2019-08-21', 8, 12733, 1, 1), ('2019-08-21', 8, 12734, 2, 1), ('2019-08-22', 16, 12732, 0, 1), ('2019-08-22', 16, 12733, 1, 1), ('2019-08-22', 16, 12734, 2, 1), ('2019-08-23', 32, 12732, 0, 1), ('2019-08-23', 32, 12733, 1, 1), ('2019-08-23', 32, 12734, 2, 1), ('2019-08-24', 64, 12732, 0, 1), ('2019-08-24', 64, 12733, 1, 1), ('2019-08-25', 1, 12732, 0, 1), ('2019-08-25', 1, 12733, 1, 1), ('2019-08-26', 2, 12732, 0, 1), ('2019-08-26', 2, 12733, 1, 1), ('2019-08-26', 2, 12734, 2, 1), ('2019-08-27', 4, 12732, 0, 1), ('2019-08-27', 4, 12733, 1, 1), ('2019-08-27', 4, 12734, 2, 1), ('2019-08-28', 8, 12732, 0, 1), ('2019-08-28', 8, 12733, 1, 1), ('2019-08-28', 8, 12734, 2, 1), ('2019-08-29', 16, 12732, 0, 1), ('2019-08-29', 16, 12733, 1, 1), ('2019-08-29', 16, 12734, 2, 1), ('2019-08-30', 32, 12732, 0, 1), ('2019-08-30', 32, 12733, 1, 1), ('2019-08-30', 32, 12734, 2, 1), ('2019-08-31', 64, 12732, 0, 1), ('2019-08-31', 64, 12733, 1, 1)
278
279INFO : (02-15 13:32:50) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query003
280 (678.8ms) insert into buffer_auto_populate_slots_1511818252/* client_id */ (
281 schedule_on,
282 day_of_week_mask,
283 call_type_id,
284 display_order,
285 should_be_populated
286 )
287 select distinct
288 date_add(
289 auto_populate_slots.schedule_on,
290 interval soft_linkages.offset day
291 ) as schedule_on,
292 (1 << (
293 dayofweek(
294 date_add(
295 auto_populate_slots.schedule_on,
296 interval soft_linkages.offset day
297 )
298 ) - 1
299 )
300 ) as day_of_week_mask,
301 soft_linkages.linked_call_type_id as call_type_id,
302 call_types.display_order,
303 0 as should_be_populated
304 from
305 buffer_auto_populate_slots_1511818252/* client_id */ as auto_populate_slots
306 inner join soft_linkages
307 on auto_populate_slots.call_type_id = soft_linkages.call_type_id
308 and auto_populate_slots.day_of_week_mask = soft_linkages.day_of_week_mask
309 left join buffer_auto_populate_slots_1511818252/* client_id */ as auto_populate_slots_2
310 on date_add(
311 auto_populate_slots.schedule_on,
312 interval soft_linkages.offset day
313 ) = auto_populate_slots_2.schedule_on
314 and soft_linkages.linked_call_type_id = auto_populate_slots_2.call_type_id
315 inner join call_types
316 on soft_linkages.linked_call_type_id = call_types.id
317 where auto_populate_slots_2.id is null
318 order by null
319
320INFO : (02-15 13:32:51) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query005
321 (170.7ms) insert into buffer_auto_populate_slots_1511818252 (
322 schedule_on,
323 day_of_week_mask,
324 call_type_id,
325 display_order,
326 should_be_populated
327 )
328 select distinct
329 date_add(
330 auto_populate_slots.schedule_on,
331 interval linkage_relations.distance_in_days day
332 ) as schedule_on,
333 (1 << (
334 dayofweek(
335 date_add(
336 auto_populate_slots.schedule_on,
337 interval linkage_relations.distance_in_days day
338 )
339 ) - 1
340 )
341 ) as day_of_week_mask,
342 linkage_relations.linked_call_type_id as call_type_id,
343 call_types.display_order,
344 0 as should_be_populated
345 from
346 buffer_auto_populate_slots_1511818252 as auto_populate_slots
347 inner join linkage_relations
348 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
349 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
350 left join buffer_auto_populate_slots_1511818252 as auto_populate_slots_2
351 on date_add(
352 auto_populate_slots.schedule_on,
353 interval linkage_relations.distance_in_days day
354 ) = auto_populate_slots_2.schedule_on
355 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
356 inner join call_types
357 on linkage_relations.linked_call_type_id = call_types.id
358 where auto_populate_slots_2.id is null
359 order by null
360
361INFO : (02-15 13:32:51) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query01
362 (156.3ms) insert into linkage_relations (
363 client_id,
364 call_type_id,
365 day_of_week_mask,
366 linked_call_type_id,
367 distance_in_days,
368 linkage_template_id
369 )
370 select distinct
371 325 /* client_id */ as client_id,
372 aps.call_type_id,
373 aps.day_of_week_mask,
374 aps.call_type_id as linked_call_type_id,
375 0 as distance_in_days,
376 coalesce(
377 lr.linkage_template_id,
378 @linkage_template_max := @linkage_template_max + 1
379 ) as linkage_template_id
380 from
381 buffer_auto_populate_slots_1511818252/* client_id */ as aps
382 inner join (
383 select @linkage_template_max := count(distinct linkage_template_id) - 1
384 from linkage_relations
385 where client_id = 325 /* client_id */
386 order by null
387 ) as lt
388 on 1 = 1
389 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
390 on aps.call_type_id = lr.call_type_id
391 and aps.day_of_week_mask = lr.day_of_week_mask
392 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
393 on aps.call_type_id = lr_2.call_type_id
394 and aps.day_of_week_mask = lr_2.day_of_week_mask
395 and aps.call_type_id = lr_2.linked_call_type_id
396 and lr_2.distance_in_days = 0
397 where lr_2.id is null
398 order by null
399
400 (158.0ms) insert into buffer_schedules_1511818252 (
401 call_type_id,
402 staff_id,
403 schedule_on,
404 day_of_week_mask,
405 is_weekend,
406 display_order,
407 week_number,
408 should_be_evaluated,
409 should_be_populated,
410 linkage_instance_id
411 )
412 select
413 call_type_id,
414 staff_id,
415 schedule_on,
416 day_of_week_mask,
417 is_weekend,
418 display_order,
419 week_number,
420 max(should_be_evaluated) as should_be_evaluated,
421 max(should_be_populated) as should_be_populated,
422 min(linkage_instance_id) as linkage_instance_id
423 from (
424 select
425 auto_populate_slots_2.call_type_id,
426 schedules.staff_id,
427 auto_populate_slots_2.schedule_on,
428 auto_populate_slots_2.day_of_week_mask,
429 case
430 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
431 else 1
432 end as is_weekend,
433 auto_populate_slots_2.display_order,
434 yearweek(
435 auto_populate_slots_2.schedule_on,
436 case
437 when (clients.start_of_week = 0) then 2
438 else 7
439 end
440 ) as week_number,
441 case
442 when (schedules.staff_id is null) then 1
443 else 0
444 end as should_be_evaluated,
445 (
446 case
447 when (
448 auto_populate_slots_2.should_be_populated = 1
449 and schedules.staff_id is null
450 ) then 1
451 else 0
452 end
453 ) as should_be_populated,
454 (
455 linkage_relations.linkage_template_id * 1000000 +
456 yearweek(
457 auto_populate_slots.schedule_on,
458 case
459 when (clients.start_of_week = 0) then 2
460 else 7
461 end
462 )
463 ) as linkage_instance_id
464 from
465 buffer_auto_populate_slots_1511818252 as auto_populate_slots
466 inner join clients
467 on clients.id = 325 /* client_id */
468 inner join linkage_relations
469 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
470 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
471 inner join buffer_auto_populate_slots_1511818252 as auto_populate_slots_2
472 on date_add(
473 auto_populate_slots.schedule_on,
474 interval linkage_relations.distance_in_days day
475 ) = auto_populate_slots_2.schedule_on
476 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
477 left join schedules
478 on auto_populate_slots_2.call_type_id = schedules.call_type_id
479 and auto_populate_slots_2.schedule_on = schedules.schedule_on
480 -- we should exclude do-not-populate slots
481 left join autopopulate_exclusions
482 on auto_populate_slots_2.call_type_id = autopopulate_exclusions.call_type_id
483 and auto_populate_slots_2.schedule_on = autopopulate_exclusions.schedule_on
484 where
485 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
486 -- once we added someone to that slot then it should be considered as a regular one
487 schedules.id is not null
488 or autopopulate_exclusions.id is null
489 order by null
490 ) as t1
491 group by
492 call_type_id,
493 staff_id,
494 schedule_on,
495 day_of_week_mask,
496 display_order,
497 week_number
498 order by null
499
500INFO : (02-15 13:32:51) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query012
501 (154.8ms) update
502 buffer_schedules_1511818252 as sc
503 inner join call_types as ct
504 on sc.call_type_id = ct.id
505 inner join calendar_schedules as cs
506 on ct.calendar_schedule_id = cs.id
507 inner join blocks as b
508 on cs.block_schedule_id = b.block_schedule_id
509 and sc.schedule_on between b.start_date and b.end_date
510 set
511 sc.calendar_schedule_id = cs.id,
512 sc.block_id = b.id
513
514 BlockSchedule Load (154.0ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 456 LIMIT 1
515 (159.1ms) insert into buffer_periods_1511818252 (
516 calendar_schedule_id,
517 block_schedule_id,
518 period_offset,
519 start_block_display_order,
520 end_block_display_order,
521 start_date,
522 end_date,
523 should_be_populated,
524 should_be_evaluated
525 )
526 select
527 t1.calendar_schedule_id,
528 t1.block_schedule_id,
529 t1.period_offset,
530 t2.start_block_display_order,
531 t2.end_block_display_order,
532 t2.start_date,
533 t2.end_date,
534 t1.should_be_populated,
535 t1.should_be_evaluated
536 from (
537 select
538 sc.calendar_schedule_id,
539 b.block_schedule_id,
540 case
541 when ((month(b.start_date) - pt.begin_period) >= 0)
542 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
543 else
544 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
545 end as period_offset,
546 max(sc.should_be_populated) as should_be_populated,
547 max(sc.should_be_evaluated) as should_be_evaluated
548 from (
549 select
550 sc.block_id,
551 sc.calendar_schedule_id,
552 max(ps.should_be_populated) as should_be_populated,
553 1 as should_be_evaluated
554 from
555 buffer_schedules_1511818252 as sc
556 inner join buffer_auto_populate_slots_1511818252 as ps
557 on sc.schedule_on = ps.schedule_on
558 and sc.call_type_id = ps.call_type_id
559 group by
560 sc.block_id,
561 sc.calendar_schedule_id
562 order by null
563 ) as sc
564 inner join blocks as b
565 on sc.block_id = b.id
566 inner join population_timeframes as pt
567 on pt.client_id = 325
568 and pt.timeframe_type = 1
569 group by
570 sc.calendar_schedule_id,
571 b.block_schedule_id,
572 period_offset
573 order by null
574 ) as t1
575 inner join (
576 select
577 b.block_schedule_id,
578 case
579 when ((month(b.start_date) - pt.begin_period) >= 0)
580 then (year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type)
581 else
582 ((year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type)
583 end as period_offset,
584 min(b.display_order) as start_block_display_order,
585 max(b.display_order) as end_block_display_order,
586 min(b.start_date) as start_date,
587 max(b.end_date) as end_date
588 from
589 blocks as b
590 inner join population_timeframes as pt
591 on pt.client_id = 325
592 and pt.timeframe_type = 1
593 where
594 b.client_id = 325
595 group by
596 b.block_schedule_id,
597 period_offset
598 order by null
599 ) as t2
600 on t1.block_schedule_id = t2.block_schedule_id
601 and t1.period_offset = t2.period_offset
602 order by null
603
604 (154.0ms) update
605 buffer_schedules_1511818252 as sc
606 inner join buffer_periods_1511818252 as p
607 on sc.calendar_schedule_id = p.calendar_schedule_id
608 and sc.schedule_on between p.start_date and p.end_date
609 set sc.period_offset = p.period_offset
610
611INFO : (02-15 13:32:52) --> fill: 6 - Scheduler::AutoPopulate::Queries::QueryTargets
612INFO : (02-15 13:32:52) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query013
613 (167.7ms) insert into buffer_counter_increments_1511818252 (
614 schedule_id,
615 calls,
616 points,
617 hours,
618 affects_cumulative_targets
619 )
620 select
621 sc.id as schedule_id,
622 case
623 when (ct.included_in_targets > 0) then 1
624 else 0
625 end as calls,
626 coalesce(
627 case
628 when (hl.observed_date is null or dp.point is not null) then dp.point
629 else dp_2.point
630 end,
631 0.0
632 ) as points,
633 ct.hours,
634 case
635 when (b.display_order between (t1.timeframe_ending - (8 /* cumulative targets period */ - 1)) and t1.timeframe_ending) then 1
636 else 0
637 end as affects_cumulative_targets
638 from
639 buffer_schedules_1511818252 as sc
640 inner join (
641 select
642 id,
643 included_in_targets,
644 coalesce(
645 timestampdiff(
646 minute,
647 concat('1900-01-01 ', maketime(start_time_hour, start_time_minute, 0)),
648 concat(
649 '1900-01-0',
650 case
651 when ((
652 start_time_hour | start_time_minute | end_time_hour | end_time_minute > 0
653 )
654 and (
655 timediff(
656 maketime(end_time_hour, end_time_minute, 0),
657 maketime(start_time_hour, start_time_minute, 0)
658 ) <= 0
659 )
660 ) then '2'
661 else '1'
662 end,
663 ' ',
664 maketime(end_time_hour, end_time_minute, 0)
665 )
666 ) / 60,
667 0.0
668 ) as hours
669 from call_types
670 where
671 client_id = 325
672 and active = 1
673 order by null
674 ) as ct
675 on sc.call_type_id = ct.id
676 inner join blocks as b
677 on sc.block_id = b.id
678 inner join (
679 select
680 calendar_schedule_id,
681 max(
682 case
683 when (should_be_populated = 1) then end_block_display_order
684 else null
685 end
686 ) as timeframe_ending
687 from buffer_periods_1511818252
688 group by calendar_schedule_id
689 order by null
690 ) as t1
691 on sc.calendar_schedule_id = t1.calendar_schedule_id
692 left join (
693 select
694 hd.observed_date,
695 case
696 when (hd.observed_date > hd.extended_date) then hd.extended_date
697 else hd.observed_date
698 end as date_from,
699 case
700 when (hd.observed_date < hd.extended_date) then hd.extended_date
701 else hd.observed_date
702 end as date_to
703 from
704 holidays as h
705 inner join holiday_dates as hd
706 on h.id = hd.holiday_id
707 inner join (
708 select
709 year(min(schedule_on)) as year_from,
710 year(max(schedule_on)) as year_to
711 from buffer_schedules_1511818252
712 where should_be_evaluated = 1
713 ) as y
714 on hd.year between y.year_from and y.year_to
715 where
716 h.client_id = 325
717 and (
718 h.observed = 1
719 or h.extended = 1
720 )
721 order by null
722 ) as hl
723 on sc.schedule_on between hl.date_from and hl.date_to
724 left join assignment_annotations as aa
725 on ct.id = aa.assignmentable_id
726 and aa.assignmentable_type = 'CallType'
727 and aa.annotation_id = 0 /* annotation = "None" */
728 and aa.applicable = 1
729 left join assignment_annotation_daily_points as dp
730 on aa.id = dp.assignment_annotation_id
731 and case
732 when (hl.observed_date is null) then (dayofweek(sc.schedule_on) - 1)
733 when (sc.schedule_on = hl.observed_date) then 7
734 else 8
735 end = dp.day
736 left join assignment_annotation_daily_points as dp_2
737 on aa.id = dp_2.assignment_annotation_id
738 and (dayofweek(sc.schedule_on) - 1) = dp_2.day
739 where sc.should_be_evaluated = 1
740 order by null
741
742INFO : (02-15 13:32:52) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query014
743 (153.7ms) -- Prepare the information about the blocks we're populating and the linkage patterns which will be used on those blocks
744 insert into buffer_linkage_patterns_1511818252 (
745 period_offset,
746 calendar_schedule_id,
747 block_id,
748 block_start_date,
749 block_start_dow,
750 block_length,
751 linkage_pattern_id,
752 pattern_start_dow,
753 days_before_start,
754 pattern_length,
755 start_offset,
756 iterations_count
757 )
758 select
759 b1.period_offset,
760 b1.calendar_schedule_id,
761 b1.block_id,
762 b2.start_date as block_start_date,
763 dayofweek(b2.start_date) as block_start_dow,
764 (datediff(b2.end_date, b2.start_date) + 1) as block_length,
765 lp.id as linkage_pattern_id,
766 (lp.start_on + 1) as pattern_start_dow,
767 lp.days_before_start,
768 (lp.length * 7) as pattern_length,
769 null as start_offset,
770 null as iterations_count
771 from (
772 select
773 calendar_schedule_id,
774 block_id,
775 period_offset
776 from buffer_schedules_1511818252
777 group by
778 calendar_schedule_id,
779 block_id,
780 period_offset
781 order by null
782 ) as b1
783 inner join blocks as b2
784 on b1.block_id = b2.id
785 inner join linkage_patterns as lp
786 on lp.client_id = 325
787 and b1.calendar_schedule_id = lp.calendar_schedule_id
788 order by null
789
790 (158.7ms) -- 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
791 update buffer_linkage_patterns_1511818252
792 set start_offset = if(
793 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow) <= days_before_start,
794 (pattern_start_dow + (case when (pattern_start_dow < block_start_dow) then 7 else 0 end) - block_start_dow),
795 (block_start_dow + (case when (block_start_dow < pattern_start_dow) then 7 else 0 end) - pattern_start_dow) * (-1)
796 )
797
798 (149.5ms) -- 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)
799 -- a pattern should be repeated couple of times (in order to cover the entire block)
800 update buffer_linkage_patterns_1511818252
801 set iterations_count =
802 ceiling(
803 (
804 block_length +
805 case
806 when (start_offset < 0) then abs(start_offset)
807 else 0
808 end
809 ) * 1.0 / pattern_length
810 )
811
812 (2385.3ms) -- Generate an assignments sequence for every linkage pattern
813 insert into buffer_schedules_linkage_patterns_1511818252 (
814 call_type_id,
815 staff_id,
816 schedule_on,
817 period_offset,
818 day_of_week_mask,
819 display_order,
820 week_number,
821 should_be_evaluated,
822 should_be_populated,
823 linkage_instance_id,
824 block_id,
825 calendar_schedule_id
826 )
827 select
828 lps.assignment_id as call_type_id,
829 null as staff_id,
830 case
831 when (lp.start_offset < 0)
832 then date_add(lp.block_start_date, interval (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) day)
833 else
834 date_add(lp.block_start_date, interval ((lp.start_offset + ns.item_number * lp.pattern_length + lps.offset) mod lp.block_length) day)
835 end as schedule_on,
836 lp.period_offset,
837 null as day_of_week_mask,
838 null as display_order,
839 null as week_number,
840 0 as should_be_evaluated,
841 0 as should_be_populated,
842 lp.linkage_pattern_id as linkage_instance_id,
843 lp.block_id,
844 lp.calendar_schedule_id
845 from
846 numeric_sequence as ns
847 inner join buffer_linkage_patterns_1511818252 as lp
848 on ns.item_number <= (lp.iterations_count - 1)
849 inner join linkage_pattern_slots as lps
850 on lp.linkage_pattern_id = lps.linkage_pattern_id
851 and lps.assignment_type = 'CallType'
852 where
853 case
854 when (lp.start_offset < 0)
855 then (lp.start_offset + ns.item_number * lp.pattern_length + lps.offset)
856 else
857 (ns.item_number * lp.pattern_length + lps.offset)
858 end between 0 and (lp.block_length - 1)
859 order by null
860
861 (147.5ms) -- Fill the columns that we left blank in the previous "INSERT"
862 update
863 buffer_schedules_linkage_patterns_1511818252 as slp
864 inner join clients as cl
865 on cl.id = 325
866 inner join call_types as ct
867 on slp.call_type_id = ct.id
868 left join buffer_schedules_1511818252 as bs
869 on slp.schedule_on = bs.schedule_on
870 and slp.call_type_id = bs.call_type_id
871 set
872 slp.day_of_week_mask = (1 << (dayofweek(slp.schedule_on) - 1)),
873 slp.display_order = ct.display_order,
874 slp.week_number =
875 yearweek(
876 slp.schedule_on,
877 case
878 when (cl.start_of_week = 0) then 2
879 else 7
880 end
881 ),
882 slp.should_be_evaluated = coalesce(bs.should_be_evaluated, 0),
883 slp.should_be_populated = coalesce(bs.should_be_populated, 0);
884
885 (152.5ms) -- Delete linkage patterns that don't have intersection with assignments we should auto-populate
886 delete slp_1
887 from
888 buffer_schedules_linkage_patterns_1511818252 as slp_1
889 inner join (
890 select linkage_instance_id
891 from buffer_schedules_linkage_patterns_1511818252
892 group by linkage_instance_id
893 having
894 max(should_be_evaluated) = 0
895 and max(should_be_populated) = 0
896 order by null
897 ) as slp_2
898 on slp_1.linkage_instance_id = slp_2.linkage_instance_id
899
900 (152.6ms) -- Delete do-not-populate slots
901 delete slp
902 from
903 buffer_schedules_linkage_patterns_1511818252 as slp
904 inner join autopopulate_exclusions as ae
905 on slp.call_type_id = ae.call_type_id
906 and slp.schedule_on = ae.schedule_on
907 left join schedules as sc
908 on slp.call_type_id = sc.call_type_id
909 and slp.schedule_on = sc.schedule_on
910 -- only empty slots can behave as do-not-populate (when they are marked appropriately)
911 -- once we added someone to that slot then it should be considered as a regular one
912 where sc.id is null
913
914 (511.9ms) -- Use a linkage pattern ID instead of linkage instance ID for those assignments that intersect with linkage patterns
915 update
916 buffer_schedules_linkage_patterns_1511818252 as slp
917 inner join buffer_schedules_1511818252 as bs_1
918 on slp.schedule_on = bs_1.schedule_on
919 and slp.call_type_id = bs_1.call_type_id
920 inner join buffer_schedules_1511818252 as bs_2
921 on bs_1.linkage_instance_id = bs_2.linkage_instance_id
922 set bs_2.linkage_instance_id = slp.linkage_instance_id
923
924 (165.0ms) -- Linkage pattern slots that don't have intersection with auto-populate assignments but are a part of a pattern that has such intersection
925 -- should be copied over to the buffer schedules table (used during the auto-populate process)
926 insert into buffer_schedules_1511818252 (
927 call_type_id,
928 staff_id,
929 schedule_on,
930 period_offset,
931 day_of_week_mask,
932 is_weekend,
933 display_order,
934 week_number,
935 should_be_evaluated,
936 should_be_populated,
937 linkage_instance_id,
938 block_id,
939 calendar_schedule_id
940 )
941 select
942 call_type_id,
943 staff_id,
944 schedule_on,
945 period_offset,
946 day_of_week_mask,
947 case
948 when (day_of_week_mask & 62 > 0) then 0
949 else 1
950 end as is_weekend,
951 display_order,
952 week_number,
953 should_be_evaluated,
954 should_be_populated,
955 linkage_instance_id,
956 block_id,
957 calendar_schedule_id
958 from buffer_schedules_linkage_patterns_1511818252
959 where
960 should_be_evaluated = 0
961 and should_be_populated = 0
962
963INFO : (02-15 13:32:56) --> fill: 9 - Scheduler::AutoPopulate::Queries::Query016
964 (152.5ms) insert into buffer_schedules_1511818252 (
965 call_type_id,
966 staff_id,
967 schedule_on,
968 day_of_week_mask,
969 is_weekend,
970 display_order,
971 should_be_evaluated,
972 should_be_populated,
973 linkage_instance_id
974 )
975 select distinct
976 schedules_call_overlap.call_type_id,
977 schedules_call_overlap.staff_id,
978 schedules_call_overlap.schedule_on,
979 (1 << (dayofweek(schedules_call_overlap.schedule_on) - 1)) as day_of_week_mask,
980 case
981 when (dayofweek(schedules_call_overlap.schedule_on) between 2 and 6) then 0
982 else 1
983 end as is_weekend,
984 call_types.display_order,
985 0 as should_be_evaluated,
986 0 as should_be_populated,
987 -1 as linkage_instance_id
988 from (
989 select schedule_on
990 from buffer_schedules_1511818252
991 where should_be_evaluated = 1
992 group by schedule_on
993 order by null
994 ) as buffer_schedules_1
995 inner join schedules as schedules_call_overlap
996 on schedules_call_overlap.client_id = 325
997 and buffer_schedules_1.schedule_on = schedules_call_overlap.schedule_on
998 left join buffer_schedules_1511818252 as buffer_schedules_2
999 on schedules_call_overlap.schedule_on = buffer_schedules_2.schedule_on
1000 and schedules_call_overlap.call_type_id = buffer_schedules_2.call_type_id
1001 inner join call_types
1002 on schedules_call_overlap.call_type_id = call_types.id
1003 where buffer_schedules_2.id is null
1004 order by null
1005
1006INFO : (02-15 13:32:56) --> fill: 10 - Scheduler::AutoPopulate::Queries::Query02
1007INFO : (02-15 13:32:56) --> fill: 11 - Scheduler::AutoPopulate::Queries::Query03
1008INFO : (02-15 13:32:56) --> fill: 12 - Scheduler::AutoPopulate::Queries::Query04
1009INFO : (02-15 13:32:56) --> fill: 13 - Scheduler::AutoPopulate::Queries::Query045
1010INFO : (02-15 13:32:56) --> fill: 14 - Scheduler::AutoPopulate::Queries::QueryA
1011 (154.6ms) insert into buffer_schedules_1511818252 (
1012 call_type_id,
1013 staff_id,
1014 schedule_on,
1015 day_of_week_mask,
1016 is_weekend,
1017 display_order,
1018 should_be_evaluated,
1019 should_be_populated,
1020 linkage_instance_id
1021 )
1022 select distinct straight_join
1023 schedules.call_type_id,
1024 schedules.staff_id,
1025 schedules.schedule_on,
1026 (1 << (dayofweek(schedules.schedule_on) - 1)) as day_of_week_mask,
1027 case
1028 when (dayofweek(schedules.schedule_on) between 2 and 6) then 0
1029 else 1
1030 end as is_weekend,
1031 call_types.display_order,
1032 0 as should_be_evaluated,
1033 0 as should_be_populated,
1034 -1 as linkage_instance_id
1035 from
1036 buffer_schedules_1511818252 as buffer_schedules_1
1037 inner join negative_links_influences use index (ix_unique_links)
1038 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1039 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1040 and negative_links_influences.source_type = 'CallType'
1041 and negative_links_influences.target_type = 'CallType'
1042 inner join schedules use index (ix_schedules_call_date)
1043 on date_add(
1044 buffer_schedules_1.schedule_on,
1045 interval negative_links_influences.offset day
1046 ) = schedules.schedule_on
1047 and negative_links_influences.target_id = schedules.call_type_id
1048 left join buffer_schedules_1511818252 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1049 on schedules.schedule_on = buffer_schedules_2.schedule_on
1050 and schedules.call_type_id = buffer_schedules_2.call_type_id
1051 inner join call_types
1052 on schedules.call_type_id = call_types.id
1053 where
1054 buffer_schedules_1.linkage_instance_id >= 0
1055 and buffer_schedules_1.should_be_evaluated = 1
1056 and buffer_schedules_2.id is null
1057 order by null
1058
1059INFO : (02-15 13:32:57) --> fill: 15 - Scheduler::AutoPopulate::Queries::QueryB
1060 (142.8ms) insert into buffer_schedules_1511818252 (
1061 call_type_id,
1062 staff_id,
1063 schedule_on,
1064 day_of_week_mask,
1065 is_weekend,
1066 display_order,
1067 should_be_evaluated,
1068 should_be_populated,
1069 linkage_instance_id
1070 )
1071 select distinct straight_join
1072 (-1 * negative_links_influences.target_id) as call_type_id,
1073 vacations.staff_id,
1074 vacations.schedule_on,
1075 (1 << (dayofweek(vacations.schedule_on) - 1)) as day_of_week_mask,
1076 vacations.weekend_on as is_weekend,
1077 -1 as display_order,
1078 0 as should_be_evaluated,
1079 0 as should_be_populated,
1080 -1 as linkage_instance_id
1081 from
1082 buffer_schedules_1511818252 as buffer_schedules_1
1083 inner join negative_links_influences use index (ix_unique_links)
1084 on buffer_schedules_1.day_of_week_mask = negative_links_influences.day_of_week_mask
1085 and buffer_schedules_1.call_type_id = negative_links_influences.source_id
1086 and negative_links_influences.source_type = 'CallType'
1087 and negative_links_influences.target_id > 0
1088 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1089 inner join vacations use index (ix_vacations_on_schedule_and_client)
1090 on vacations.client_id = 325 /* client_id */
1091 and date_add(
1092 buffer_schedules_1.schedule_on,
1093 interval negative_links_influences.offset day
1094 ) = vacations.schedule_on
1095 and negative_links_influences.target_id = vacations.non_clinical_type_id
1096 and vacations.status in (3) /* based on the "non-working requests" settings */
1097 left join buffer_schedules_1511818252 as buffer_schedules_2 use index (ix_buffer_schedules_composite_1)
1098 on vacations.staff_id = buffer_schedules_2.staff_id
1099 and vacations.schedule_on = buffer_schedules_2.schedule_on
1100 and (-1 * negative_links_influences.target_id) = buffer_schedules_2.call_type_id
1101 where
1102 buffer_schedules_1.linkage_instance_id >= 0
1103 and buffer_schedules_1.should_be_evaluated = 1
1104 and buffer_schedules_2.id is null
1105 order by null
1106
1107INFO : (02-15 13:32:57) --> fill: 16 - Scheduler::AutoPopulate::Queries::Query05
1108 (145.6ms) insert into buffer_linkages_1511818252 (
1109 linkage_instance_id,
1110 slots_count_total,
1111 has_conflicts
1112 )
1113 select straight_join
1114 schedules.linkage_instance_id,
1115 count(1) as slots_count_total,
1116 if(linkage_conflicts.linkage_template_id is not null, 1, 0) as has_conflicts
1117 from
1118 buffer_schedules_1511818252 as schedules
1119 left join (
1120 select straight_join
1121 linkage_relations.linkage_template_id
1122 from linkage_relations
1123 inner join negative_links_influences use index (ix_unique_links)
1124 on linkage_relations.day_of_week_mask = negative_links_influences.day_of_week_mask
1125 and linkage_relations.call_type_id = negative_links_influences.source_id
1126 and negative_links_influences.source_type = 'CallType'
1127 and linkage_relations.linked_call_type_id = negative_links_influences.target_id
1128 and negative_links_influences.target_type = 'CallType'
1129 and linkage_relations.distance_in_days = negative_links_influences.offset
1130 where linkage_relations.client_id = 325 /* client_id */
1131 and (
1132 linkage_relations.call_type_id != linkage_relations.linked_call_type_id
1133 or linkage_relations.distance_in_days != 0
1134 )
1135 group by linkage_relations.linkage_template_id
1136 order by null
1137 ) as linkage_conflicts
1138 on (schedules.linkage_instance_id div 1000000) = linkage_conflicts.linkage_template_id
1139 where schedules.linkage_instance_id >= 0
1140 group by
1141 schedules.linkage_instance_id,
1142 has_conflicts
1143 order by null
1144
1145INFO : (02-15 13:32:57) --> fill: 17 - Scheduler::AutoPopulate::Queries::Query06
1146 (156.8ms) insert into buffer_target_assignments_1511818252 (
1147 call_type_id,
1148 target_group_id,
1149 assignment_id,
1150 assignment_type
1151 )
1152 select
1153 t1.call_type_id,
1154 ifnull(tg.id, 0) as target_group_id,
1155 if(tg.id is null, t1.call_type_id, tg.id) as assignment_id,
1156 if(tg.id is null, 'CallType', 'TargetGroup') as assignment_type
1157 from (
1158 select ct.id as call_type_id
1159 from
1160 buffer_schedules_1511818252 as sc
1161 inner join call_types as ct
1162 on sc.call_type_id = ct.id
1163 and ct.active = 1
1164 and ct.included_in_targets > 0
1165 where linkage_instance_id >= 0
1166 group by ct.id
1167 order by null
1168 ) as t1
1169 left join target_group_calls as tgc
1170 on t1.call_type_id = tgc.call_type_id
1171 left join target_groups as tg
1172 on tgc.target_group_id = tg.id
1173 and tg.active = 1
1174 order by null
1175
1176INFO : (02-15 13:32:57) --> fill: 18 - Scheduler::AutoPopulate::Queries::Query07
1177INFO : (02-15 13:32:57) --> fill: 19 - Scheduler::AutoPopulate::Queries::Query0702
1178INFO : (02-15 13:32:57) --> fill: 20 - Scheduler::AutoPopulate::Queries::Query0705
1179 (153.6ms) insert into buffer_linkage_call_type_assignments_1511818252 (
1180 linkage_instance_id,
1181 call_type_id,
1182 is_weekend,
1183 period_offset,
1184 calendar_schedule_id,
1185 max_assignments,
1186 assignments_count
1187 )
1188 select
1189 s.linkage_instance_id,
1190 s.call_type_id,
1191 s.is_weekend,
1192 s.period_offset,
1193 s.calendar_schedule_id,
1194 case
1195 when (s.is_weekend = 0) then ct.max_weekday_assignments
1196 else ct.max_weekend_assignments
1197 end as _max_assignments,
1198 sum(1) as assignments_count
1199 from buffer_schedules_1511818252 as s
1200 inner join call_types as ct
1201 on s.call_type_id = ct.id
1202 and ct.active = 1
1203 and (
1204 ct.max_weekday_assignments is not null
1205 or ct.max_weekend_assignments is not null
1206 )
1207 where s.linkage_instance_id >= 0
1208 and s.staff_id is null
1209 group by
1210 s.linkage_instance_id,
1211 s.call_type_id,
1212 s.is_weekend,
1213 s.period_offset,
1214 s.calendar_schedule_id,
1215 _max_assignments
1216 having _max_assignments >= 0
1217 order by null
1218
1219INFO : (02-15 13:32:57) --> fill: 21 - Scheduler::AutoPopulate::Queries::Query0706
1220 (155.2ms) insert into buffer_call_type_assignments_1511818252 (
1221 staff_id,
1222 call_type_id,
1223 is_weekend,
1224 period_offset,
1225 calendar_schedule_id,
1226 assignments_count
1227 )
1228 select
1229 sc.staff_id,
1230 sc.call_type_id,
1231 sc.weekend_on as is_weekend,
1232 p.period_offset,
1233 p.calendar_schedule_id,
1234 sum(1) as assignments_count
1235 from buffer_periods_1511818252 as p
1236 inner join call_types as ct
1237 on p.calendar_schedule_id = ct.calendar_schedule_id
1238 and ct.active = 1
1239 and (
1240 ct.max_weekday_assignments is not null
1241 or ct.max_weekend_assignments is not null
1242 )
1243 inner join schedules as sc
1244 on ct.id = sc.call_type_id
1245 and sc.schedule_on between p.start_date and p.end_date
1246 and sc.staff_id is not null
1247 where p.should_be_evaluated = 1
1248 group by
1249 sc.staff_id,
1250 sc.call_type_id,
1251 sc.weekend_on,
1252 p.period_offset,
1253 p.calendar_schedule_id
1254 order by null
1255
1256INFO : (02-15 13:32:57) --> fill: 22 - Scheduler::AutoPopulate::Queries::Query0707
1257INFO : (02-15 13:32:57) --> fill: 23 - Scheduler::AutoPopulate::Queries::Query0708
1258 (154.7ms) insert into buffer_linkage_assigned_days_1511818252 (
1259 linkage_instance_id,
1260 period_offset,
1261 week_number,
1262 schedule_on,
1263 is_weekend
1264 )
1265 select distinct
1266 sc.linkage_instance_id,
1267 sc.period_offset,
1268 sc.week_number,
1269 sc.schedule_on,
1270 sc.is_weekend
1271 from
1272 buffer_schedules_1511818252 as sc
1273 inner join call_types as ct
1274 on sc.call_type_id = ct.id
1275 and ct.unassigned_day = 0
1276 where
1277 sc.linkage_instance_id >= 0
1278 and sc.staff_id is null
1279 order by null
1280
1281INFO : (02-15 13:32:57) --> fill: 24 - Scheduler::AutoPopulate::Queries::Query0709
1282INFO : (02-15 13:32:57) --> fill: 25 - Scheduler::AutoPopulate::Queries::Query08
1283 (152.5ms) insert into buffer_staff_daily_counts_1511818252 (
1284 staff_id, schedule_on, linkage_instance_id, slots_count
1285 )
1286 select staff_id, schedule_on, null as linkage_instance_id,
1287 count(1) as slots_count
1288 from buffer_schedules_1511818252
1289 where staff_id is not null
1290 and call_type_id > 0 /* VC */
1291 group by staff_id, schedule_on
1292 order by null
1293
1294 (153.8ms) insert into buffer_staff_daily_counts_1511818252 (
1295 staff_id, schedule_on, linkage_instance_id, slots_count
1296 )
1297 select staff_id, schedule_on, linkage_instance_id,
1298 count(1) as slots_count
1299 from buffer_schedules_1511818252
1300 where staff_id is not null
1301 and call_type_id > 0 /* VC */
1302 group by staff_id, schedule_on, linkage_instance_id
1303 order by null
1304
1305INFO : (02-15 13:32:58) --> fill: 26 - Scheduler::AutoPopulate::Queries::Query085
1306 (248.8ms) insert into buffer_staff_specializations_1511818252 (
1307 staff_id, specialization_id
1308 )
1309 select staff_id, specialization_id
1310 from view_staff_specializations
1311 where client_id = 325
1312 order by null
1313
1314 (588.9ms) insert into buffer_specialization_daily_counts_1511818252 (
1315 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1316 )
1317 select sp.id as specialization_id,
1318 null as staff_id,
1319 bs_1.schedule_on,
1320 count(distinct st_sp.staff_id) as staffs_scheduled,
1321 sp.max_scheduled,
1322 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
1323 from (
1324 select schedule_on, day_of_week_mask
1325 from buffer_schedules_1511818252
1326 where should_be_evaluated = 1
1327 group by schedule_on, day_of_week_mask
1328 order by null
1329 ) as bs_1
1330 inner join specializations as sp
1331 on sp.client_id = 325
1332 and sp.active = 1
1333 inner join buffer_schedules_1511818252 as bs_2
1334 on bs_1.schedule_on = bs_2.schedule_on
1335 and bs_2.call_type_id > 0 /* is not VC */
1336 inner join max_applicable_call_types as mact
1337 on sp.id = mact.specialization_id
1338 and bs_2.call_type_id = mact.call_type_id
1339 left join buffer_staff_specializations_1511818252 as st_sp
1340 on bs_2.staff_id = st_sp.staff_id
1341 and sp.id = st_sp.specialization_id
1342 group by sp.id, bs_1.schedule_on, sp.max_scheduled
1343 order by null
1344
1345 (153.2ms) insert into buffer_specialization_daily_counts_1511818252 (
1346 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
1347 )
1348 select sp.id as specialization_id,
1349 st_sp.staff_id,
1350 bs_1.schedule_on,
1351 1 as staffs_scheduled,
1352 sp.max_scheduled,
1353 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
1354 from (
1355 select schedule_on, day_of_week_mask
1356 from buffer_schedules_1511818252
1357 where should_be_evaluated = 1
1358 group by schedule_on, day_of_week_mask
1359 order by null
1360 ) as bs_1
1361 inner join specializations as sp
1362 on sp.client_id = 325
1363 and sp.active = 1
1364 inner join buffer_schedules_1511818252 as bs_2
1365 on bs_1.schedule_on = bs_2.schedule_on
1366 and bs_2.call_type_id > 0 /* is not VC */
1367 inner join max_applicable_call_types as mact
1368 on sp.id = mact.specialization_id
1369 and bs_2.call_type_id = mact.call_type_id
1370 inner join buffer_staff_specializations_1511818252 as st_sp
1371 on bs_2.staff_id = st_sp.staff_id
1372 and sp.id = st_sp.specialization_id
1373 group by sp.id, st_sp.staff_id, bs_1.schedule_on, sp.max_scheduled
1374 order by null
1375
1376INFO : (02-15 13:32:59) --> fill: 27 - Scheduler::AutoPopulate::Queries::Query09
1377 (169.2ms) truncate table buffer_location_privileges_1511818252
1378
1379 (158.3ms) insert into buffer_location_privileges_1511818252 (
1380 staff_id,
1381 location_id,
1382 begin_date,
1383 end_date
1384 )
1385 select
1386 s.id as staff_id,
1387 l.location_id,
1388 '0000-00-00' as begin_date,
1389 '9999-12-31' as end_date
1390 from (
1391 select l.id as location_id
1392 from
1393 buffer_schedules_1511818252 as sc
1394 inner join call_types as ct
1395 on sc.call_type_id = ct.id
1396 and ct.active = 1
1397 inner join locations as l
1398 on ct.location_id = l.id
1399 and l.active = 1
1400 where sc.linkage_instance_id >= 0
1401 and sc.should_be_evaluated = 1
1402 group by l.id
1403 order by null
1404 ) as l
1405 inner join staffs as s
1406 on s.client_id = 325
1407 and s.active = 1
1408 and s.take_call = 1
1409 and s.auto_populatable = 1
1410 left join location_privileges as lp
1411 on s.id = lp.staff_id
1412 where lp.id is null
1413 union
1414 select
1415 lp.staff_id,
1416 lp.location_id,
1417 lp.begin_date,
1418 coalesce(lp.end_date, '9999-12-31') as end_date
1419 from (
1420 select l.id as location_id
1421 from
1422 buffer_schedules_1511818252 as sc
1423 inner join call_types as ct
1424 on sc.call_type_id = ct.id
1425 and ct.active = 1
1426 inner join locations as l
1427 on ct.location_id = l.id
1428 and l.active = 1
1429 where sc.linkage_instance_id >= 0
1430 and sc.should_be_evaluated = 1
1431 group by l.id
1432 order by null
1433 ) as l
1434 inner join location_privileges as lp
1435 on l.location_id = lp.location_id
1436 order by null;
1437
1438 (444.2ms) insert into buffer_static_availabilities_1511818252 (
1439 schedule_id,
1440 staff_id,
1441 staff_type_id,
1442 is_primary
1443 )
1444 select
1445 schedules.id as schedule_id,
1446 staffs.id as staff_id,
1447 assign_staff_types.staff_type_id,
1448 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
1449 from
1450 buffer_schedules_1511818252 as schedules
1451 /* AvailabilityRule */
1452 inner join call_types
1453 on schedules.call_type_id = call_types.id
1454 and call_types.active = 1
1455 and schedules.day_of_week_mask & call_types.days_of_week_mask > 0
1456 inner join staff_call_types
1457 on call_types.id = staff_call_types.call_type_id
1458 and schedules.day_of_week_mask & (staff_call_types.days_of_week_mask | staff_call_types.secondary_availability_days_of_week_mask) > 0
1459 inner join staffs
1460 on staff_call_types.staff_id = staffs.id
1461 and staffs.active = 1
1462 and staffs.take_call = 1
1463 and staffs.auto_populatable = 1
1464 inner join buffer_location_privileges_1511818252 as location_privileges
1465 on staffs.id = location_privileges.staff_id
1466 and call_types.location_id = location_privileges.location_id
1467 and schedules.schedule_on between location_privileges.begin_date and ifnull(location_privileges.end_date, '9999-12-31')
1468 inner join assign_staff_types
1469 on staffs.id = assign_staff_types.staff_id
1470 and schedules.schedule_on between assign_staff_types.begin_date and ifnull(assign_staff_types.end_date, '9999-12-31')
1471 left join eligible_staff_types
1472 on staff_call_types.call_type_id = eligible_staff_types.call_type_id
1473 and assign_staff_types.staff_type_id = eligible_staff_types.staff_type_id
1474 left join assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1475 on staffs.id = assign_rotation_schedules.staff_id
1476 and schedules.schedule_on between assign_rotation_schedules.begin_date and assign_rotation_schedules.end_date
1477 left join staff_rotations force index (ix_staff_rotations_composite_1)
1478 on staffs.id = staff_rotations.staff_id
1479 and schedules.schedule_on between staff_rotations.start_date and staff_rotations.end_date
1480 and assign_rotation_schedules.rotation_schedule_id = staff_rotations.rotation_schedule_id
1481 left join daily_rotations
1482 on staffs.id = daily_rotations.staff_id
1483 and schedules.schedule_on = daily_rotations.schedule_on
1484 and assign_rotation_schedules.rotation_schedule_id = daily_rotations.rotation_schedule_id
1485 and staff_rotations.id is not null
1486 left join rotations
1487 on coalesce(daily_rotations.rotation_id, staff_rotations.rotation_id) = rotations.id
1488 and rotations.active = 1
1489 left join rotation_call_types
1490 on rotations.id = rotation_call_types.rotation_id
1491 and staff_call_types.call_type_id = rotation_call_types.call_type_id
1492 and schedules.day_of_week_mask & (rotation_call_types.days_of_week_mask | rotation_call_types.secondary_availability_days_of_week_mask) > 0
1493 /* RequiredRotationsRule */
1494 left join required_rotations
1495 on staff_call_types.call_type_id = required_rotations.call_type_id
1496 left join staff_rotations as rr_staff_rotations force index (ix_staff_rotations_composite_3)
1497 on staffs.id = rr_staff_rotations.staff_id
1498 and required_rotations.rotation_id = rr_staff_rotations.rotation_id
1499 left join assign_rotation_schedules as rr_assign_rotation_schedules force index (ix_assign_rotation_schedules_composite_1)
1500 on staffs.id = rr_assign_rotation_schedules.staff_id
1501 and rr_staff_rotations.start_date <= rr_assign_rotation_schedules.end_date
1502 and rr_staff_rotations.end_date >= rr_assign_rotation_schedules.begin_date
1503 and rr_staff_rotations.rotation_schedule_id = rr_assign_rotation_schedules.rotation_schedule_id
1504 /* NoCallRequestsHiddenHardRule */
1505 left join request_settings
1506 on assign_staff_types.staff_type_id = request_settings.staff_type_id
1507 left join requests
1508 on schedules.schedule_on = requests.schedule_on
1509 and staffs.id = requests.staff_id
1510 and requests.request_type = 0 /* type = no_call */
1511 and requests.status in (1, 3) /* status = approved or fulfilled */
1512 and call_types.is_no_call_requestable = 1
1513 left join request_call_types
1514 on requests.id = request_call_types.request_id
1515 and schedules.call_type_id = request_call_types.call_type_id
1516 where
1517 schedules.linkage_instance_id >= 0
1518 and schedules.should_be_evaluated = 1
1519 and (
1520 call_types.all_staff_types = 1
1521 or eligible_staff_types.id is not null
1522 )
1523 and (
1524 rotations.id is null
1525 or rotation_call_types.id is not null
1526 )
1527 and (
1528 requests.id is null
1529 or (
1530 requests.all_call_types = 1
1531 and request_settings.call_only_all_call_switch = 1 /* calls only */
1532 and call_types.assignment_type = 1 /* clinical */
1533 )
1534 or (
1535 requests.all_call_types = 0
1536 and request_call_types.id is null
1537 )
1538 )
1539 group by
1540 schedules.id,
1541 staffs.id,
1542 assign_staff_types.staff_type_id,
1543 is_primary
1544 having
1545 count(distinct
1546 case
1547 when (
1548 rr_staff_rotations.id is not null
1549 and rr_assign_rotation_schedules.id is not null
1550 and schedules.schedule_on >= greatest(rr_staff_rotations.start_date, rr_assign_rotation_schedules.begin_date)
1551 )
1552 then rr_staff_rotations.rotation_id
1553 else null
1554 end
1555 ) >= count(distinct required_rotations.rotation_id) /* RequiredRotationsRule */
1556 and is_primary IN (0,1) /* PrimaryAvailabilityRule */
1557 order by null
1558
1559INFO : (02-15 13:32:59) --> fill: 28 - Scheduler::AutoPopulate::Queries::Query092
1560 (149.7ms) /* RotationPostCallRule */
1561 /*
1562 Exclude cases when people have a rotation starting on the next day after a call we're trying to make,
1563 if such rotation is configured appropriately ("Post-call on first day" = "Not permitted").
1564 */
1565 delete sa
1566 from buffer_static_availabilities_1511818252 as sa
1567 inner join buffer_schedules_1511818252 as sc
1568 on sa.schedule_id = sc.id
1569 inner join call_types as ct
1570 on sc.call_type_id = ct.id
1571 and ct.assignment_type = 0 /* call */
1572 inner join staff_rotations as sr
1573 on sa.staff_id = sr.staff_id
1574 and date_add(sc.schedule_on, interval 1 day) between sr.start_date and sr.end_date
1575 inner join assign_rotation_schedules as ars
1576 on sa.staff_id = ars.staff_id
1577 and date_add(sc.schedule_on, interval 1 day) between ars.begin_date and ars.end_date
1578 and sr.rotation_schedule_id = ars.rotation_schedule_id
1579 inner join rotations as r
1580 on sr.rotation_id = r.id
1581 and r.post_call_allowed = 0
1582 left join assign_rotation_schedules as ars_2
1583 on sa.staff_id = ars_2.staff_id
1584 and sr.start_date <= ars_2.end_date
1585 and sr.end_date >= ars_2.begin_date
1586 and sr.rotation_schedule_id = ars_2.rotation_schedule_id
1587 -- Earlier intersection
1588 and ars.begin_date > ars_2.begin_date
1589 where
1590 date_add(sc.schedule_on, interval 1 day) = greatest(sr.start_date, ars.begin_date)
1591 and ars_2.id is null;
1592
1593INFO : (02-15 13:33:00) --> fill: 29 - Scheduler::AutoPopulate::Queries::Query093
1594INFO : (02-15 13:33:00) --> fill: 30 - Scheduler::AutoPopulate::Queries::Query095
1595 (542.1ms) insert into buffer_negative_relations_1511818252 (
1596 schedule_id,
1597 affected_by_schedule_id,
1598 staff_id
1599 )
1600 select
1601 b_schedules.id as schedule_id,
1602 b_schedules_negative_link.id as affected_by_schedule_id,
1603 b_schedules_negative_link.staff_id
1604 from
1605 buffer_schedules_1511818252 as b_schedules
1606 /* NegativeLinkRule */
1607 inner join negative_links_influences
1608 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1609 and b_schedules.call_type_id = negative_links_influences.source_id
1610 and negative_links_influences.source_type = 'CallType'
1611 and negative_links_influences.target_type = 'CallType'
1612 inner join buffer_schedules_1511818252 as b_schedules_negative_link
1613 on date_add(
1614 b_schedules.schedule_on,
1615 interval negative_links_influences.offset day
1616 ) = b_schedules_negative_link.schedule_on
1617 and negative_links_influences.target_id = b_schedules_negative_link.call_type_id
1618 and b_schedules.id != b_schedules_negative_link.id
1619 where
1620 b_schedules.linkage_instance_id >= 0
1621 and b_schedules.should_be_evaluated = 1
1622 order by null
1623
1624 (153.4ms) insert into buffer_negative_relations_1511818252 (
1625 schedule_id,
1626 affected_by_schedule_id,
1627 staff_id
1628 )
1629 select
1630 b_schedules.id as schedule_id,
1631 b_schedules_negative_link.id as affected_by_schedule_id,
1632 b_schedules_negative_link.staff_id
1633 from
1634 buffer_schedules_1511818252 as b_schedules
1635 /* NegativeLinkRule */
1636 inner join negative_links_influences
1637 on b_schedules.day_of_week_mask = negative_links_influences.day_of_week_mask
1638 and b_schedules.call_type_id = negative_links_influences.source_id
1639 and negative_links_influences.source_type = 'CallType'
1640 and negative_links_influences.target_type in ('Vacation', 'NonClinicalType')
1641 inner join buffer_schedules_1511818252 as b_schedules_negative_link
1642 on date_add(
1643 b_schedules.schedule_on,
1644 interval negative_links_influences.offset day
1645 ) = b_schedules_negative_link.schedule_on
1646 and (-1 * negative_links_influences.target_id) = b_schedules_negative_link.call_type_id
1647 and b_schedules.id != b_schedules_negative_link.id
1648 where
1649 b_schedules.linkage_instance_id >= 0
1650 and b_schedules.should_be_evaluated = 1
1651 order by null
1652
1653 (155.3ms) delete from buffer_schedules_1511818252
1654 where call_type_id < 0 /* vacations and non-clinicals */
1655
1656INFO : (02-15 13:33:00) --> fill: 31 - Scheduler::AutoPopulate::Queries::Query097
1657 (323.3ms) insert into buffer_staff_assigned_days_1511818252 (
1658 staff_id, period_offset, week_number, schedule_on, is_weekend, is_assigned
1659 )
1660 select staffs.staff_id,
1661 if(days.within_period = 1, days.period_offset, null) as period_offset,
1662 yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) as week_number,
1663 days.schedule_on,
1664 if(dayofweek(days.schedule_on) in (7, 1), 1, 0) as is_weekend,
1665 if(schedules.staff_id is not null or vacations.staff_id is not null, 1, 0) as is_assigned
1666 from (
1667 select distinct staff_id
1668 from buffer_static_availabilities_1511818252
1669 order by null
1670 ) as staffs
1671 inner join (
1672 select p.period_offset,
1673 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,
1674 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
1675 from buffer_periods_1511818252 as p
1676 inner join (
1677 select 0 as digit union select 1 union select 2 union select 3 union select 4
1678 union select 5 union select 6 union select 7 union select 8 union select 9
1679 ) as digits_1
1680 inner join (
1681 select 0 as digit union select 1 union select 2 union select 3 union select 4
1682 union select 5 union select 6 union select 7 union select 8 union select 9
1683 ) as digits_2
1684 inner join (
1685 select 0 as digit union select 1 union select 2 union select 3 union select 4
1686 union select 5 union select 6 union select 7 union select 8 union select 9
1687 ) as digits_3
1688 where p.calendar_schedule_id = 1396
1689 and p.should_be_evaluated = 1
1690 and (digits_1.digit * 100 + digits_2.digit * 10 + digits_3.digit) <= datediff(p.end_date, p.start_date) + 12
1691 order by null
1692 ) as days
1693 inner join (
1694 select min(start_date) as start_date, max(end_date) as end_date
1695 from buffer_periods_1511818252
1696 where calendar_schedule_id = 1396
1697 and should_be_evaluated = 1
1698 order by null
1699 ) as limits
1700 inner join clients
1701 on clients.id = 325
1702 left join (
1703 select sc.staff_id, sc.schedule_on
1704 from buffer_periods_1511818252 as p
1705 inner join schedules as sc
1706 on sc.client_id = 325
1707 and sc.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
1708 inner join call_types as ct
1709 on sc.call_type_id = ct.id
1710 and ct.unassigned_day = 0
1711 where p.calendar_schedule_id = 1396
1712 and p.should_be_evaluated = 1
1713 group by sc.staff_id, sc.schedule_on
1714 order by null
1715 ) as schedules
1716 on staffs.staff_id = schedules.staff_id
1717 and days.schedule_on = schedules.schedule_on
1718 left join (
1719 select v.staff_id, v.schedule_on
1720 from buffer_periods_1511818252 as p
1721 inner join vacations as v
1722 on v.client_id = 325
1723 and v.schedule_on between date_add(p.start_date, interval -6 day) and date_add(p.end_date, interval 6 day)
1724 and v.status IN (3)
1725 inner join non_clinical_types as nct
1726 on v.non_clinical_type_id = nct.id
1727 and nct.active = 1
1728 and nct.unassigned_day = 0
1729 where p.calendar_schedule_id = 1396
1730 and p.should_be_evaluated = 1
1731 group by v.staff_id, v.schedule_on
1732 order by null
1733 ) as vacations
1734 on staffs.staff_id = vacations.staff_id
1735 and days.schedule_on = vacations.schedule_on
1736 where days.within_period = 1
1737 or yearweek(days.schedule_on, if(clients.start_of_week = 0, 2, 7)) in (
1738 yearweek(limits.start_date, if(clients.start_of_week = 0, 2, 7)),
1739 yearweek(limits.end_date, if(clients.start_of_week = 0, 2, 7))
1740 )
1741 order by null
1742
1743INFO : (02-15 13:33:01) --> fill: 32 - Scheduler::AutoPopulate::Queries::Query098
1744 (517.6ms) insert into buffer_combined_availabilities_1511818252 (
1745 schedule_id, staff_id, staff_type_id, is_primary, linkage_instance_id, has_violations
1746 )
1747 select sc.id as schedule_id, sa.staff_id, sa.staff_type_id, sa.is_primary,
1748 sc.linkage_instance_id, 0 as has_violations
1749 from buffer_schedules_1511818252 as sc
1750 /* Static availability */
1751 inner join buffer_static_availabilities_1511818252 as sa
1752 on sc.id = sa.schedule_id
1753 inner join buffer_linkages_1511818252 as l
1754 on sc.linkage_instance_id = l.linkage_instance_id
1755 where sc.should_be_evaluated = 1
1756 and l.has_conflicts = 0
1757
1758 (153.9ms) insert into buffer_schedule_linkages_1511818252 (
1759 staff_id, linkage_instance_id, slots_count
1760 )
1761 select staff_id, linkage_instance_id, count(1) as slots_count
1762 from buffer_schedules_1511818252 use index (ix_buffer_schedules_composite_2)
1763 where staff_id is not null
1764 and linkage_instance_id >= 0
1765 group by staff_id, linkage_instance_id
1766 order by null
1767
1768INFO : (02-15 13:33:01) --> fill: 33 - Scheduler::AutoPopulate::Queries::Query09_09_AL
1769 (146.6ms) insert into buffer_assignment_limits_1511818252 (
1770 id,
1771 name,
1772 calendar_schedule_id,
1773 considered_days_of_week_mask,
1774 limit_type,
1775 limit_subtypes_mask,
1776 timeframe_type,
1777 max_per_period_limit,
1778 max_weekly_limit,
1779 max_rolling_period,
1780 max_rolling_limit,
1781 max_consecutive_limit
1782 )
1783 select
1784 id,
1785 name,
1786 calendar_schedule_id,
1787 considered_days_of_week_mask,
1788 limit_type,
1789 (
1790 (coalesce(max_per_period_limit, -1) >= 0) * 1 +
1791 (coalesce(max_weekly_limit, -1) >= 0) * 2 +
1792 (
1793 coalesce(max_rolling_period, -1) > 0
1794 and coalesce(max_rolling_limit, -1) >= 0
1795 ) * 4 +
1796 (coalesce(max_consecutive_limit, -1) > 0) * 8
1797 ) as limit_subtypes_mask,
1798 timeframe_type,
1799 max_per_period_limit,
1800 max_weekly_limit,
1801 max_rolling_period,
1802 max_rolling_limit,
1803 max_consecutive_limit
1804 from assignment_limits
1805 where
1806 client_id = 325
1807 and active = 1
1808 and (
1809 max_per_period_limit >= 0
1810 or max_weekly_limit >= 0
1811 or (
1812 max_rolling_period > 0
1813 and max_rolling_limit >= 0
1814 )
1815 or max_consecutive_limit > 0
1816 )
1817 order by null
1818
1819 (150.0ms) select id from buffer_assignment_limits_1511818252 limit 1
1820INFO : (02-15 13:33:02) --> fill: 34 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
1821 (146.0ms) select
1822 coalesce(bit_or(limit_subtypes_mask), 0)
1823 from buffer_assignment_limits_1511818252
1824
1825INFO : (02-15 13:33:02) --> fill: 35 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
1826INFO : (02-15 13:33:02) --> fill: 36 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
1827INFO : (02-15 13:33:02) --> fill: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
1828INFO : (02-15 13:33:02) --> fill: 38 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
1829INFO : (02-15 13:33:02) --> fill: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
1830INFO : (02-15 13:33:02) --> fill: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
1831INFO : (02-15 13:33:02) --> fill: 41 - Scheduler::AutoPopulate::Queries::Query09_12_Points
1832INFO : (02-15 13:33:02) --> fill: 42 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
1833INFO : (02-15 13:33:02) --> fill: 43 - Scheduler::AutoPopulate::Queries::Query10
1834 (151.8ms) select
1835 coalesce(max(
1836 case
1837 when (ct.max_weekday_assignments > 0) then 1
1838 else 0
1839 end
1840 ), 0) as weekday_flag,
1841 coalesce(max(
1842 case
1843 when (ct.max_weekend_assignments > 0) then 1
1844 else 0
1845 end
1846 ), 0) as weekend_flag
1847 from
1848 buffer_auto_populate_slots_1511818252 as aps
1849 inner join call_types as ct
1850 on aps.call_type_id = ct.id
1851
1852 (151.3ms) /* NegativeLinkRule */
1853 delete ca
1854 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
1855 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
1856 on ca.schedule_id = nr.schedule_id
1857 and ca.staff_id = nr.staff_id
1858
1859 (465.8ms) /* SpecializationOverlapRule */
1860 delete ca
1861 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
1862 inner join buffer_schedules_1511818252 as sc
1863 on ca.schedule_id = sc.id
1864 inner join buffer_staff_specializations_1511818252 as ssp
1865 on ca.staff_id = ssp.staff_id
1866 inner join max_applicable_call_types as mact
1867 on ssp.specialization_id = mact.specialization_id
1868 and sc.call_type_id = mact.call_type_id
1869 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
1870 on ssp.specialization_id = sdc.specialization_id
1871 and sc.schedule_on = sdc.schedule_on
1872 and sdc.staff_id is null
1873 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
1874 on ssp.specialization_id = sdc_2.specialization_id
1875 and sc.schedule_on = sdc_2.schedule_on
1876 and ca.staff_id = sdc_2.staff_id
1877 where sdc.should_be_evaluated = 1
1878 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
1879
1880 (152.6ms) /* BrokenLinkageRule */
1881 truncate table buffer_combined_availability_linkages_1511818252
1882
1883 (154.7ms) insert into buffer_combined_availability_linkages_1511818252 (
1884 staff_id,
1885 linkage_instance_id,
1886 slots_count,
1887 is_primary,
1888 has_violations
1889 )
1890 select
1891 staff_id,
1892 linkage_instance_id,
1893 count(1) as slots_count,
1894 min(is_primary) as is_primary,
1895 max(has_violations) as has_violations
1896 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
1897 group by
1898 staff_id,
1899 linkage_instance_id
1900 order by null
1901
1902 (153.0ms) delete ca
1903 from
1904 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
1905 inner join buffer_schedules_1511818252 as sc
1906 on ca.schedule_id = sc.id
1907 inner join buffer_linkages_1511818252 as l
1908 on sc.linkage_instance_id = l.linkage_instance_id
1909 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
1910 on ca.staff_id = ls_1.staff_id
1911 and sc.linkage_instance_id = ls_1.linkage_instance_id
1912 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
1913 on ca.staff_id = ls_2.staff_id
1914 and sc.linkage_instance_id = ls_2.linkage_instance_id
1915 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
1916
1917INFO : (02-15 13:33:03) --> fill: 44 - Scheduler::AutoPopulate::Queries::QueryScore02
1918INFO : (02-15 13:33:03) --> fill: 45 - Scheduler::AutoPopulate::Queries::QueryScore03
1919INFO : (02-15 13:33:03) --> fill: 46 - Scheduler::AutoPopulate::Queries::QueryScore04
1920INFO : (02-15 13:33:03) --> fill: 47 - Scheduler::AutoPopulate::Queries::Query11
1921 (149.5ms) update buffer_schedules_1511818252 as sc
1922 left join buffer_combined_availabilities_1511818252 as ca
1923 on sc.id = ca.schedule_id
1924 set sc.should_be_evaluated = 0,
1925 sc.should_be_populated = 0
1926 where sc.should_be_evaluated = 1
1927 and ca.id is null
1928
1929INFO : (02-15 13:33:03) --> fill: 48 - Scheduler::AutoPopulate::Queries::DropTables
1930INFO : (02-15 13:33:03) Setting up lists...
1931 (303.0ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
1932 from buffer_schedules_1511818252 as b_schedules
1933 left join schedules
1934 on schedules.client_id = 325
1935 and schedules.call_type_id = b_schedules.call_type_id
1936 and schedules.schedule_on = b_schedules.schedule_on
1937 where b_schedules.linkage_instance_id != -1
1938 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
1939
1940INFO : (02-15 13:33:04) Begin to populate. Pre-set: 0. Unscheduled: 84.
1941INFO : (02-15 13:33:04) plan unit: [id:201930] [2019-08-03 - OR]
1942INFO : (02-15 13:33:04) plan unit: [id:201931] [2019-08-10 - OR]
1943INFO : (02-15 13:33:04) plan unit: [id:201932] [2019-08-17 - OR]
1944INFO : (02-15 13:33:04) plan unit: [id:201933] [2019-08-24 - OR]
1945INFO : (02-15 13:33:04) plan unit: [id:201934] [2019-08-31 - OR]
1946INFO : (02-15 13:33:04) plan unit: [id:1201930] [2019-08-03 - OB]
1947INFO : (02-15 13:33:04) plan unit: [id:1201931] [2019-08-10 - OB]
1948INFO : (02-15 13:33:04) plan unit: [id:1201932] [2019-08-17 - OB]
1949INFO : (02-15 13:33:04) plan unit: [id:1201933] [2019-08-24 - OB]
1950INFO : (02-15 13:33:04) plan unit: [id:1201934] [2019-08-31 - OB]
1951INFO : (02-15 13:33:04) plan unit: [id:2201931] [2019-08-04 - OR]
1952INFO : (02-15 13:33:04) plan unit: [id:2201932] [2019-08-11 - OR]
1953INFO : (02-15 13:33:04) plan unit: [id:2201933] [2019-08-18 - OR]
1954INFO : (02-15 13:33:04) plan unit: [id:2201934] [2019-08-25 - OR]
1955INFO : (02-15 13:33:04) plan unit: [id:3201931] [2019-08-04 - OB]
1956INFO : (02-15 13:33:04) plan unit: [id:3201932] [2019-08-11 - OB]
1957INFO : (02-15 13:33:04) plan unit: [id:3201933] [2019-08-18 - OB]
1958INFO : (02-15 13:33:04) plan unit: [id:3201934] [2019-08-25 - OB]
1959INFO : (02-15 13:33:04) plan unit: [id:4201931] [2019-08-05 - OR]
1960INFO : (02-15 13:33:04) plan unit: [id:4201932] [2019-08-12 - OR]
1961INFO : (02-15 13:33:04) plan unit: [id:4201933] [2019-08-19 - OR]
1962INFO : (02-15 13:33:04) plan unit: [id:4201934] [2019-08-26 - OR]
1963INFO : (02-15 13:33:04) plan unit: [id:5201931] [2019-08-05 - OB]
1964INFO : (02-15 13:33:04) plan unit: [id:5201932] [2019-08-12 - OB]
1965INFO : (02-15 13:33:04) plan unit: [id:5201933] [2019-08-19 - OB]
1966INFO : (02-15 13:33:04) plan unit: [id:5201934] [2019-08-26 - OB]
1967INFO : (02-15 13:33:04) plan unit: [id:6201931] [2019-08-05 - SSGE]
1968INFO : (02-15 13:33:04) plan unit: [id:6201932] [2019-08-12 - SSGE]
1969INFO : (02-15 13:33:04) plan unit: [id:6201933] [2019-08-19 - SSGE]
1970INFO : (02-15 13:33:04) plan unit: [id:6201934] [2019-08-26 - SSGE]
1971INFO : (02-15 13:33:04) plan unit: [id:7201931] [2019-08-06 - OR]
1972INFO : (02-15 13:33:04) plan unit: [id:7201932] [2019-08-13 - OR]
1973INFO : (02-15 13:33:04) plan unit: [id:7201933] [2019-08-20 - OR]
1974INFO : (02-15 13:33:04) plan unit: [id:7201934] [2019-08-27 - OR]
1975INFO : (02-15 13:33:04) plan unit: [id:8201931] [2019-08-06 - OB]
1976INFO : (02-15 13:33:04) plan unit: [id:8201932] [2019-08-13 - OB]
1977INFO : (02-15 13:33:04) plan unit: [id:8201933] [2019-08-20 - OB]
1978INFO : (02-15 13:33:04) plan unit: [id:8201934] [2019-08-27 - OB]
1979INFO : (02-15 13:33:04) plan unit: [id:9201931] [2019-08-06 - SSGE]
1980INFO : (02-15 13:33:04) plan unit: [id:9201932] [2019-08-13 - SSGE]
1981INFO : (02-15 13:33:04) plan unit: [id:9201933] [2019-08-20 - SSGE]
1982INFO : (02-15 13:33:04) plan unit: [id:9201934] [2019-08-27 - SSGE]
1983INFO : (02-15 13:33:04) plan unit: [id:10201931] [2019-08-07 - OR]
1984INFO : (02-15 13:33:04) plan unit: [id:10201932] [2019-08-14 - OR]
1985INFO : (02-15 13:33:04) plan unit: [id:10201933] [2019-08-21 - OR]
1986INFO : (02-15 13:33:04) plan unit: [id:10201934] [2019-08-28 - OR]
1987INFO : (02-15 13:33:04) plan unit: [id:11201931] [2019-08-07 - OB]
1988INFO : (02-15 13:33:04) plan unit: [id:11201932] [2019-08-14 - OB]
1989INFO : (02-15 13:33:04) plan unit: [id:11201933] [2019-08-21 - OB]
1990INFO : (02-15 13:33:04) plan unit: [id:11201934] [2019-08-28 - OB]
1991INFO : (02-15 13:33:04) plan unit: [id:12201931] [2019-08-07 - SSGE]
1992INFO : (02-15 13:33:04) plan unit: [id:12201932] [2019-08-14 - SSGE]
1993INFO : (02-15 13:33:04) plan unit: [id:12201933] [2019-08-21 - SSGE]
1994INFO : (02-15 13:33:04) plan unit: [id:12201934] [2019-08-28 - SSGE]
1995INFO : (02-15 13:33:04) plan unit: [id:13201930] [2019-08-01 - OR]
1996INFO : (02-15 13:33:04) plan unit: [id:13201931] [2019-08-08 - OR]
1997INFO : (02-15 13:33:04) plan unit: [id:13201932] [2019-08-15 - OR]
1998INFO : (02-15 13:33:04) plan unit: [id:13201933] [2019-08-22 - OR]
1999INFO : (02-15 13:33:04) plan unit: [id:13201934] [2019-08-29 - OR]
2000INFO : (02-15 13:33:04) plan unit: [id:14201930] [2019-08-01 - OB]
2001INFO : (02-15 13:33:04) plan unit: [id:14201931] [2019-08-08 - OB]
2002INFO : (02-15 13:33:04) plan unit: [id:14201932] [2019-08-15 - OB]
2003INFO : (02-15 13:33:04) plan unit: [id:14201933] [2019-08-22 - OB]
2004INFO : (02-15 13:33:04) plan unit: [id:14201934] [2019-08-29 - OB]
2005INFO : (02-15 13:33:04) plan unit: [id:15201930] [2019-08-01 - SSGE]
2006INFO : (02-15 13:33:04) plan unit: [id:15201931] [2019-08-08 - SSGE]
2007INFO : (02-15 13:33:04) plan unit: [id:15201932] [2019-08-15 - SSGE]
2008INFO : (02-15 13:33:04) plan unit: [id:15201933] [2019-08-22 - SSGE]
2009INFO : (02-15 13:33:04) plan unit: [id:15201934] [2019-08-29 - SSGE]
2010INFO : (02-15 13:33:04) plan unit: [id:16201930] [2019-08-02 - OR]
2011INFO : (02-15 13:33:04) plan unit: [id:16201931] [2019-08-09 - OR]
2012INFO : (02-15 13:33:04) plan unit: [id:16201932] [2019-08-16 - OR]
2013INFO : (02-15 13:33:04) plan unit: [id:16201933] [2019-08-23 - OR]
2014INFO : (02-15 13:33:04) plan unit: [id:16201934] [2019-08-30 - OR]
2015INFO : (02-15 13:33:04) plan unit: [id:17201930] [2019-08-02 - OB]
2016INFO : (02-15 13:33:04) plan unit: [id:17201931] [2019-08-09 - OB]
2017INFO : (02-15 13:33:04) plan unit: [id:17201932] [2019-08-16 - OB]
2018INFO : (02-15 13:33:04) plan unit: [id:17201933] [2019-08-23 - OB]
2019INFO : (02-15 13:33:04) plan unit: [id:17201934] [2019-08-30 - OB]
2020INFO : (02-15 13:33:04) plan unit: [id:18201930] [2019-08-02 - SSGE]
2021INFO : (02-15 13:33:04) plan unit: [id:18201931] [2019-08-09 - SSGE]
2022INFO : (02-15 13:33:04) plan unit: [id:18201932] [2019-08-16 - SSGE]
2023INFO : (02-15 13:33:04) plan unit: [id:18201933] [2019-08-23 - SSGE]
2024INFO : (02-15 13:33:04) plan unit: [id:18201934] [2019-08-30 - SSGE]
2025 (152.2ms) select
2026 coalesce(max(
2027 case
2028 when (ct.max_weekday_assignments > 0) then 1
2029 else 0
2030 end
2031 ), 0) as weekday_flag,
2032 coalesce(max(
2033 case
2034 when (ct.max_weekend_assignments > 0) then 1
2035 else 0
2036 end
2037 ), 0) as weekend_flag
2038 from
2039 buffer_auto_populate_slots_1511818252 as aps
2040 inner join call_types as ct
2041 on aps.call_type_id = ct.id
2042
2043 (508.6ms) /* NegativeLinkRule */
2044 delete ca
2045 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2046 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
2047 on ca.schedule_id = nr.schedule_id
2048 and ca.staff_id = nr.staff_id
2049
2050 (141.9ms) /* SpecializationOverlapRule */
2051 delete ca
2052 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2053 inner join buffer_schedules_1511818252 as sc
2054 on ca.schedule_id = sc.id
2055 inner join buffer_staff_specializations_1511818252 as ssp
2056 on ca.staff_id = ssp.staff_id
2057 inner join max_applicable_call_types as mact
2058 on ssp.specialization_id = mact.specialization_id
2059 and sc.call_type_id = mact.call_type_id
2060 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2061 on ssp.specialization_id = sdc.specialization_id
2062 and sc.schedule_on = sdc.schedule_on
2063 and sdc.staff_id is null
2064 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2065 on ssp.specialization_id = sdc_2.specialization_id
2066 and sc.schedule_on = sdc_2.schedule_on
2067 and ca.staff_id = sdc_2.staff_id
2068 where sdc.should_be_evaluated = 1
2069 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2070
2071 (148.2ms) /* BrokenLinkageRule */
2072 truncate table buffer_combined_availability_linkages_1511818252
2073
2074 (146.0ms) insert into buffer_combined_availability_linkages_1511818252 (
2075 staff_id,
2076 linkage_instance_id,
2077 slots_count,
2078 is_primary,
2079 has_violations
2080 )
2081 select
2082 staff_id,
2083 linkage_instance_id,
2084 count(1) as slots_count,
2085 min(is_primary) as is_primary,
2086 max(has_violations) as has_violations
2087 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
2088 group by
2089 staff_id,
2090 linkage_instance_id
2091 order by null
2092
2093 (158.2ms) delete ca
2094 from
2095 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2096 inner join buffer_schedules_1511818252 as sc
2097 on ca.schedule_id = sc.id
2098 inner join buffer_linkages_1511818252 as l
2099 on sc.linkage_instance_id = l.linkage_instance_id
2100 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2101 on ca.staff_id = ls_1.staff_id
2102 and sc.linkage_instance_id = ls_1.linkage_instance_id
2103 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2104 on ca.staff_id = ls_2.staff_id
2105 and sc.linkage_instance_id = ls_2.linkage_instance_id
2106 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2107
2108 (162.1ms) select straight_join
2109 sc.schedule_on,
2110 sc.call_type_id,
2111 count(distinct ca.staff_id) as available_members_count,
2112 group_concat(
2113 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
2114 order by ls_1.is_primary desc, ca.staff_id
2115 separator ', '
2116 ) as available_members_ids,
2117 sc.linkage_instance_id,
2118 l.slots_count_total as linkage_slots_count
2119 from buffer_combined_availabilities_1511818252 as ca
2120 inner join buffer_schedules_1511818252 as sc
2121 on ca.schedule_id = sc.id
2122 inner join buffer_linkages_1511818252 as l
2123 on sc.linkage_instance_id = l.linkage_instance_id
2124 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2125 on ca.staff_id = ls_1.staff_id
2126 and sc.linkage_instance_id = ls_1.linkage_instance_id
2127 and ls_1.has_violations = 0
2128 where sc.should_be_evaluated = 1
2129 and sc.should_be_populated = 1
2130 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
2131 order by available_members_count, sc.schedule_on, sc.display_order
2132 limit 1
2133
2134 (148.5ms) truncate table buffer_rule_scores_1511818252
2135
2136 (143.1ms) truncate table buffer_rule_calc_filters_1511818252
2137
2138 (528.0ms) insert into buffer_rule_calc_filters_1511818252 (
2139 linkage_instance_id,
2140 staff_id
2141 ) values (15201930, 20739)
2142
2143 (144.5ms) truncate table buffer_rule_scores_with_corrections_1511818252
2144
2145 (141.8ms) insert into buffer_rule_scores_with_corrections_1511818252 (
2146 linkage_instance_id, schedule_id, staff_id, rule_name, value
2147 )
2148 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2149 ifnull(rule_scores.value, max_values.max_value + 1) as value
2150 from buffer_rule_scores_1511818252 as rule_scores
2151 inner join (
2152 select rule_name, max(value) as max_value
2153 from buffer_rule_scores_1511818252
2154 group by rule_name
2155 order by null
2156 ) as max_values
2157 on rule_scores.rule_name = max_values.rule_name
2158 order by null
2159
2160 (178.2ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2161 from (
2162 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2163 sum(
2164 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2165 rules.weight *
2166 total_values.slots_count
2167 ) as slot_scores
2168 from buffer_rule_scores_with_corrections_1511818252 as rule_scores
2169 inner join (
2170 select rule_name, sum(abs(value)) as total_value,
2171 count(distinct schedule_id) as slots_count
2172 from buffer_rule_scores_with_corrections_1511818252
2173 group by rule_name
2174 order by null
2175 ) as total_values
2176 on rule_scores.rule_name = total_values.rule_name
2177 inner join rules
2178 on rule_scores.rule_name = rules.type
2179 where rules.rule_set_id = 1538
2180 and rules.applied = 1
2181 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2182 order by null
2183 ) as staff_scores
2184 group by linkage_instance_id, staff_id
2185 order by round(avg(slot_scores), 5) desc, rand()
2186 limit 1
2187
2188WARN : (02-15 13:33:06) No score found. Using fake score...
2189 PublishedBlock Load (143.2ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND ('2019-08-01' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1396 ORDER BY `published_blocks`.`id` ASC LIMIT 1
2190 (150.9ms) BEGIN
2191 (153.9ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
2192 FROM call_types AS ct
2193 INNER JOIN calendar_schedules AS cs
2194 ON ct.calendar_schedule_id = cs.id
2195 INNER JOIN blocks AS b
2196 ON b.client_id = ct.client_id
2197 AND b.block_schedule_id = cs.block_schedule_id
2198 AND '2019-08-01' BETWEEN b.start_date AND b.end_date
2199 INNER JOIN published_blocks AS pb
2200 ON pb.client_id = ct.client_id
2201 AND pb.block_id = b.id
2202 AND pb.calendar_schedule_id = cs.id
2203 LEFT JOIN published_block_call_types AS pbct
2204 ON pbct.client_id = ct.client_id
2205 AND pbct.assignment_id = ct.id
2206 AND pbct.assignment_type = 'CallType'
2207 AND pbct.block_id = b.id
2208 AND (
2209 (1 << (DAYOFWEEK('2019-08-01') - 1) & pbct.days_of_week_mask) > 0
2210 )
2211 WHERE ct.id = 12734
2212 AND (
2213 (
2214 pb.assignment_ids IS NULL
2215 AND (1 << (DAYOFWEEK('2019-08-01') - 1) & pb.days_of_week_mask) > 0
2216 )
2217 OR pbct.id IS NOT NULL
2218 )
2219
2220 CallType Load (586.8ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12734 LIMIT 1
2221 Client Load (437.5ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
2222 Staff Load (150.0ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20739 LIMIT 1
2223 Schedule Exists (142.4ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12734 AND `schedules`.`client_id` = 325 AND `schedules`.`schedule_on` = '2019-08-01' LIMIT 1
2224 SQL (163.1ms) 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 (325, 12734, 20739, '2019-08-01', '2019-02-15 13:33:36', '2019-02-15 13:33:36', 0, 5, 'auto_populate_step', 20695)
2225 VisibleAssignment Load (149.0ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 325 AND `visible_assignments`.`date` = '2019-08-01' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12734 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
2226 (144.9ms) COMMIT
2227FLUSHED: nil:nil
2228 (145.3ms) UPDATE buffer_schedules_1511818252
2229 SET staff_id = 20739,
2230 should_be_populated = 0,
2231 should_be_evaluated = 0
2232 WHERE
2233 call_type_id = 12734
2234 AND schedule_on = '2019-08-01'
2235
2236 (143.6ms) delete from buffer_combined_availabilities_1511818252
2237 where linkage_instance_id = 15201930
2238
2239 (156.5ms) UPDATE buffer_rule_calc_schedules_1511818252
2240 SET staff_id = 20739
2241 WHERE call_type_id = 12734
2242 AND schedule_on = '2019-08-01'
2243
2244 (143.0ms) INSERT INTO buffer_rule_calc_schedules_1511818252
2245 SET staff_id = 20739,
2246 call_type_id = 12734,
2247 schedule_on = '2019-08-01',
2248 day_of_week_mask = 16
2249
2250 (147.8ms) /* NegativeLinkRule */
2251 UPDATE buffer_negative_relations_1511818252
2252 SET staff_id = 20739
2253 WHERE affected_by_schedule_id = (
2254 SELECT id
2255 FROM buffer_schedules_1511818252
2256 WHERE schedule_on = '2019-08-01'
2257 AND call_type_id = 12734
2258 )
2259
2260 (149.4ms) /* SpecializationOverlapRule */
2261 update buffer_staff_specializations_1511818252 as st_sp
2262 inner join max_applicable_call_types as mact
2263 on st_sp.specialization_id = mact.specialization_id
2264 and mact.call_type_id = 12734
2265 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
2266 on st_sp.specialization_id = sp_dc.specialization_id
2267 and sp_dc.staff_id is null
2268 and sp_dc.schedule_on = '2019-08-01'
2269 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
2270 on st_sp.specialization_id = sp_dc_2.specialization_id
2271 and st_sp.staff_id = sp_dc_2.staff_id
2272 and sp_dc_2.schedule_on = '2019-08-01'
2273 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
2274 where st_sp.staff_id = 20739
2275 and sp_dc_2.id is null
2276
2277 (143.3ms) insert into buffer_specialization_daily_counts_1511818252 (
2278 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2279 )
2280 select sp.id as specialization_id,
2281 20739 as staff_id,
2282 '2019-08-01' as schedule_on,
2283 1 as staffs_scheduled,
2284 sp.max_scheduled,
2285 sp_dc.should_be_evaluated
2286 from buffer_staff_specializations_1511818252 as st_sp
2287 inner join specializations as sp
2288 on st_sp.specialization_id = sp.id
2289 inner join max_applicable_call_types as mact
2290 on st_sp.specialization_id = mact.specialization_id
2291 and mact.call_type_id = 12734
2292 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
2293 on st_sp.specialization_id = sp_dc.specialization_id
2294 and sp_dc.staff_id is null
2295 and sp_dc.schedule_on = '2019-08-01'
2296 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
2297 on st_sp.specialization_id = sp_dc_2.specialization_id
2298 and st_sp.staff_id = sp_dc_2.staff_id
2299 and sp_dc_2.schedule_on = '2019-08-01'
2300 where st_sp.staff_id = 20739
2301 and sp_dc_2.id is null
2302
2303 (145.7ms) /* MaxAssignmentsRule */
2304 delete from buffer_linkage_call_type_assignments_1511818252
2305 where linkage_instance_id = 15201930;
2306
2307 (507.1ms) /* MaxAssignmentsRule */
2308 update
2309 buffer_schedules_1511818252 as sc
2310 inner join call_types as ct
2311 on sc.call_type_id = ct.id
2312 and ct.active = 1
2313 inner join buffer_call_type_assignments_1511818252 as cta
2314 on sc.staff_id = cta.staff_id
2315 and sc.call_type_id = cta.call_type_id
2316 and sc.is_weekend = cta.is_weekend
2317 and sc.period_offset = cta.period_offset
2318 and sc.calendar_schedule_id = cta.calendar_schedule_id
2319 set cta.assignments_count = cta.assignments_count + 1
2320 where
2321 sc.schedule_on = '2019-08-01'
2322 and sc.call_type_id = 12734
2323 and sc.staff_id = 20739
2324
2325 (528.2ms) insert into buffer_call_type_assignments_1511818252 (
2326 staff_id,
2327 call_type_id,
2328 is_weekend,
2329 period_offset,
2330 calendar_schedule_id,
2331 assignments_count
2332 )
2333 select
2334 sc.staff_id,
2335 sc.call_type_id,
2336 sc.is_weekend,
2337 sc.period_offset,
2338 sc.calendar_schedule_id,
2339 1 as assignments_count
2340 from buffer_schedules_1511818252 as sc
2341 where
2342 sc.staff_id = 20739
2343 and sc.schedule_on = '2019-08-01'
2344 and sc.call_type_id = 12734
2345
2346 (151.3ms) /* UnassignedDaysRule */
2347 delete from buffer_linkage_assigned_days_1511818252
2348 where linkage_instance_id = 15201930
2349
2350 (142.4ms) /* UnassignedDaysRule */
2351 UPDATE buffer_staff_assigned_days_1511818252 as sad
2352 inner join call_types as ct
2353 on ct.id = 12734
2354 and ct.unassigned_day = 0
2355 SET sad.is_assigned = 1
2356 WHERE sad.staff_id = 20739
2357 AND sad.schedule_on = '2019-08-01'
2358
2359 (147.8ms) delete
2360 from buffer_linkage_assignment_limits_1511818252
2361 where
2362 staff_id = 20739
2363 and linkage_instance_id = 15201930
2364
2365 (154.2ms) insert into buffer_staff_assignment_limits_1511818252 (
2366 staff_id,
2367 assignment_limit_id,
2368 period_id,
2369 consecutive_days_mask,
2370 current_value
2371 )
2372 select
2373 sc.staff_id,
2374 al.id as assignment_limit_id,
2375 alp.id as period_id,
2376 case
2377 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2378 (1 << datediff(sc.schedule_on, alp.start_date))
2379 else 0
2380 end as consecutive_days_mask,
2381 case
2382 when (al.limit_type = 0 /* assignments */) then 1.0
2383 when (al.limit_type = 1 /* points */) then ci.points
2384 when (al.limit_type = 2 /* hours */) then ci.hours
2385 else 0
2386 end as current_value
2387 from
2388 buffer_schedules_1511818252 as sc
2389 inner join buffer_assignment_limit_call_types_1511818252 as alct
2390 on sc.call_type_id = alct.call_type_id
2391 inner join buffer_assignment_limits_1511818252 as al
2392 on alct.assignment_limit_id = al.id
2393 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2394 inner join buffer_assignment_limit_staffs_1511818252 as als
2395 on al.id = als.assignment_limit_id
2396 and sc.staff_id = als.staff_id
2397 inner join assign_staff_types as ast
2398 on sc.staff_id = ast.staff_id
2399 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2400 inner join buffer_assignment_limit_staff_types_1511818252 as alst
2401 on al.id = alst.assignment_limit_id
2402 and ast.staff_type_id = alst.staff_type_id
2403 inner join buffer_assignment_limit_periods_1511818252 as alp
2404 on al.id = alp.assignment_limit_id
2405 and sc.schedule_on between alp.start_date and alp.end_date
2406 inner join buffer_counter_increments_1511818252 as ci
2407 on sc.id = ci.schedule_id
2408 where
2409 sc.schedule_on = '2019-08-01'
2410 and sc.call_type_id = 12734
2411 and sc.staff_id = 20739
2412 on duplicate key update
2413 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
2414 current_value = current_value + values(current_value)
2415
2416INFO : (02-15 13:33:40) scheduled for: plan unit: [id:15201930] [2019-08-01 - SSGE], Ron Dobrovinsky. DEPTH(1)
2417 (150.8ms) select
2418 coalesce(max(
2419 case
2420 when (ct.max_weekday_assignments > 0) then 1
2421 else 0
2422 end
2423 ), 0) as weekday_flag,
2424 coalesce(max(
2425 case
2426 when (ct.max_weekend_assignments > 0) then 1
2427 else 0
2428 end
2429 ), 0) as weekend_flag
2430 from
2431 buffer_auto_populate_slots_1511818252 as aps
2432 inner join call_types as ct
2433 on aps.call_type_id = ct.id
2434
2435 (145.8ms) /* NegativeLinkRule */
2436 delete ca
2437 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2438 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
2439 on ca.schedule_id = nr.schedule_id
2440 and ca.staff_id = nr.staff_id
2441
2442 (449.1ms) /* SpecializationOverlapRule */
2443 delete ca
2444 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2445 inner join buffer_schedules_1511818252 as sc
2446 on ca.schedule_id = sc.id
2447 inner join buffer_staff_specializations_1511818252 as ssp
2448 on ca.staff_id = ssp.staff_id
2449 inner join max_applicable_call_types as mact
2450 on ssp.specialization_id = mact.specialization_id
2451 and sc.call_type_id = mact.call_type_id
2452 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2453 on ssp.specialization_id = sdc.specialization_id
2454 and sc.schedule_on = sdc.schedule_on
2455 and sdc.staff_id is null
2456 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2457 on ssp.specialization_id = sdc_2.specialization_id
2458 and sc.schedule_on = sdc_2.schedule_on
2459 and ca.staff_id = sdc_2.staff_id
2460 where sdc.should_be_evaluated = 1
2461 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2462
2463 (150.3ms) /* BrokenLinkageRule */
2464 truncate table buffer_combined_availability_linkages_1511818252
2465
2466 (153.9ms) insert into buffer_combined_availability_linkages_1511818252 (
2467 staff_id,
2468 linkage_instance_id,
2469 slots_count,
2470 is_primary,
2471 has_violations
2472 )
2473 select
2474 staff_id,
2475 linkage_instance_id,
2476 count(1) as slots_count,
2477 min(is_primary) as is_primary,
2478 max(has_violations) as has_violations
2479 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
2480 group by
2481 staff_id,
2482 linkage_instance_id
2483 order by null
2484
2485 (153.2ms) delete ca
2486 from
2487 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2488 inner join buffer_schedules_1511818252 as sc
2489 on ca.schedule_id = sc.id
2490 inner join buffer_linkages_1511818252 as l
2491 on sc.linkage_instance_id = l.linkage_instance_id
2492 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2493 on ca.staff_id = ls_1.staff_id
2494 and sc.linkage_instance_id = ls_1.linkage_instance_id
2495 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2496 on ca.staff_id = ls_2.staff_id
2497 and sc.linkage_instance_id = ls_2.linkage_instance_id
2498 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2499
2500 (161.4ms) select straight_join
2501 sc.schedule_on,
2502 sc.call_type_id,
2503 count(distinct ca.staff_id) as available_members_count,
2504 group_concat(
2505 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
2506 order by ls_1.is_primary desc, ca.staff_id
2507 separator ', '
2508 ) as available_members_ids,
2509 sc.linkage_instance_id,
2510 l.slots_count_total as linkage_slots_count
2511 from buffer_combined_availabilities_1511818252 as ca
2512 inner join buffer_schedules_1511818252 as sc
2513 on ca.schedule_id = sc.id
2514 inner join buffer_linkages_1511818252 as l
2515 on sc.linkage_instance_id = l.linkage_instance_id
2516 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2517 on ca.staff_id = ls_1.staff_id
2518 and sc.linkage_instance_id = ls_1.linkage_instance_id
2519 and ls_1.has_violations = 0
2520 where sc.should_be_evaluated = 1
2521 and sc.should_be_populated = 1
2522 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
2523 order by available_members_count, sc.schedule_on, sc.display_order
2524 limit 1
2525
2526 (149.0ms) truncate table buffer_rule_scores_1511818252
2527
2528 (141.0ms) truncate table buffer_rule_calc_filters_1511818252
2529
2530 (541.2ms) insert into buffer_rule_calc_filters_1511818252 (
2531 linkage_instance_id,
2532 staff_id
2533 ) values (18201930, 20739)
2534
2535 (152.7ms) truncate table buffer_rule_scores_with_corrections_1511818252
2536
2537 (147.2ms) insert into buffer_rule_scores_with_corrections_1511818252 (
2538 linkage_instance_id, schedule_id, staff_id, rule_name, value
2539 )
2540 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2541 ifnull(rule_scores.value, max_values.max_value + 1) as value
2542 from buffer_rule_scores_1511818252 as rule_scores
2543 inner join (
2544 select rule_name, max(value) as max_value
2545 from buffer_rule_scores_1511818252
2546 group by rule_name
2547 order by null
2548 ) as max_values
2549 on rule_scores.rule_name = max_values.rule_name
2550 order by null
2551
2552 (162.9ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2553 from (
2554 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2555 sum(
2556 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2557 rules.weight *
2558 total_values.slots_count
2559 ) as slot_scores
2560 from buffer_rule_scores_with_corrections_1511818252 as rule_scores
2561 inner join (
2562 select rule_name, sum(abs(value)) as total_value,
2563 count(distinct schedule_id) as slots_count
2564 from buffer_rule_scores_with_corrections_1511818252
2565 group by rule_name
2566 order by null
2567 ) as total_values
2568 on rule_scores.rule_name = total_values.rule_name
2569 inner join rules
2570 on rule_scores.rule_name = rules.type
2571 where rules.rule_set_id = 1538
2572 and rules.applied = 1
2573 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2574 order by null
2575 ) as staff_scores
2576 group by linkage_instance_id, staff_id
2577 order by round(avg(slot_scores), 5) desc, rand()
2578 limit 1
2579
2580WARN : (02-15 13:33:43) No score found. Using fake score...
2581 PublishedBlock Load (149.9ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND ('2019-08-02' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1396 ORDER BY `published_blocks`.`id` ASC LIMIT 1
2582 (149.6ms) BEGIN
2583 (152.5ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
2584 FROM call_types AS ct
2585 INNER JOIN calendar_schedules AS cs
2586 ON ct.calendar_schedule_id = cs.id
2587 INNER JOIN blocks AS b
2588 ON b.client_id = ct.client_id
2589 AND b.block_schedule_id = cs.block_schedule_id
2590 AND '2019-08-02' BETWEEN b.start_date AND b.end_date
2591 INNER JOIN published_blocks AS pb
2592 ON pb.client_id = ct.client_id
2593 AND pb.block_id = b.id
2594 AND pb.calendar_schedule_id = cs.id
2595 LEFT JOIN published_block_call_types AS pbct
2596 ON pbct.client_id = ct.client_id
2597 AND pbct.assignment_id = ct.id
2598 AND pbct.assignment_type = 'CallType'
2599 AND pbct.block_id = b.id
2600 AND (
2601 (1 << (DAYOFWEEK('2019-08-02') - 1) & pbct.days_of_week_mask) > 0
2602 )
2603 WHERE ct.id = 12734
2604 AND (
2605 (
2606 pb.assignment_ids IS NULL
2607 AND (1 << (DAYOFWEEK('2019-08-02') - 1) & pb.days_of_week_mask) > 0
2608 )
2609 OR pbct.id IS NOT NULL
2610 )
2611
2612 CallType Load (285.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12734 LIMIT 1
2613 Client Load (739.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
2614 Staff Load (146.7ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20739 LIMIT 1
2615 Schedule Exists (151.6ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12734 AND `schedules`.`client_id` = 325 AND `schedules`.`schedule_on` = '2019-08-02' LIMIT 1
2616 SQL (151.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 (325, 12734, 20739, '2019-08-02', '2019-02-15 13:33:46', '2019-02-15 13:33:46', 0, 6, 'auto_populate_step', 20695)
2617 VisibleAssignment Load (524.9ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 325 AND `visible_assignments`.`date` = '2019-08-02' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12734 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
2618 (169.9ms) COMMIT
2619FLUSHED: nil:nil
2620 (154.0ms) UPDATE buffer_schedules_1511818252
2621 SET staff_id = 20739,
2622 should_be_populated = 0,
2623 should_be_evaluated = 0
2624 WHERE
2625 call_type_id = 12734
2626 AND schedule_on = '2019-08-02'
2627
2628 (150.0ms) delete from buffer_combined_availabilities_1511818252
2629 where linkage_instance_id = 18201930
2630
2631 (510.0ms) UPDATE buffer_rule_calc_schedules_1511818252
2632 SET staff_id = 20739
2633 WHERE call_type_id = 12734
2634 AND schedule_on = '2019-08-02'
2635
2636 (481.5ms) INSERT INTO buffer_rule_calc_schedules_1511818252
2637 SET staff_id = 20739,
2638 call_type_id = 12734,
2639 schedule_on = '2019-08-02',
2640 day_of_week_mask = 32
2641
2642 (160.6ms) /* NegativeLinkRule */
2643 UPDATE buffer_negative_relations_1511818252
2644 SET staff_id = 20739
2645 WHERE affected_by_schedule_id = (
2646 SELECT id
2647 FROM buffer_schedules_1511818252
2648 WHERE schedule_on = '2019-08-02'
2649 AND call_type_id = 12734
2650 )
2651
2652 (143.2ms) /* SpecializationOverlapRule */
2653 update buffer_staff_specializations_1511818252 as st_sp
2654 inner join max_applicable_call_types as mact
2655 on st_sp.specialization_id = mact.specialization_id
2656 and mact.call_type_id = 12734
2657 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
2658 on st_sp.specialization_id = sp_dc.specialization_id
2659 and sp_dc.staff_id is null
2660 and sp_dc.schedule_on = '2019-08-02'
2661 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
2662 on st_sp.specialization_id = sp_dc_2.specialization_id
2663 and st_sp.staff_id = sp_dc_2.staff_id
2664 and sp_dc_2.schedule_on = '2019-08-02'
2665 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
2666 where st_sp.staff_id = 20739
2667 and sp_dc_2.id is null
2668
2669 (296.8ms) insert into buffer_specialization_daily_counts_1511818252 (
2670 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
2671 )
2672 select sp.id as specialization_id,
2673 20739 as staff_id,
2674 '2019-08-02' as schedule_on,
2675 1 as staffs_scheduled,
2676 sp.max_scheduled,
2677 sp_dc.should_be_evaluated
2678 from buffer_staff_specializations_1511818252 as st_sp
2679 inner join specializations as sp
2680 on st_sp.specialization_id = sp.id
2681 inner join max_applicable_call_types as mact
2682 on st_sp.specialization_id = mact.specialization_id
2683 and mact.call_type_id = 12734
2684 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
2685 on st_sp.specialization_id = sp_dc.specialization_id
2686 and sp_dc.staff_id is null
2687 and sp_dc.schedule_on = '2019-08-02'
2688 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
2689 on st_sp.specialization_id = sp_dc_2.specialization_id
2690 and st_sp.staff_id = sp_dc_2.staff_id
2691 and sp_dc_2.schedule_on = '2019-08-02'
2692 where st_sp.staff_id = 20739
2693 and sp_dc_2.id is null
2694
2695 (166.0ms) /* MaxAssignmentsRule */
2696 delete from buffer_linkage_call_type_assignments_1511818252
2697 where linkage_instance_id = 18201930;
2698
2699 (152.0ms) /* MaxAssignmentsRule */
2700 update
2701 buffer_schedules_1511818252 as sc
2702 inner join call_types as ct
2703 on sc.call_type_id = ct.id
2704 and ct.active = 1
2705 inner join buffer_call_type_assignments_1511818252 as cta
2706 on sc.staff_id = cta.staff_id
2707 and sc.call_type_id = cta.call_type_id
2708 and sc.is_weekend = cta.is_weekend
2709 and sc.period_offset = cta.period_offset
2710 and sc.calendar_schedule_id = cta.calendar_schedule_id
2711 set cta.assignments_count = cta.assignments_count + 1
2712 where
2713 sc.schedule_on = '2019-08-02'
2714 and sc.call_type_id = 12734
2715 and sc.staff_id = 20739
2716
2717 (152.0ms) /* UnassignedDaysRule */
2718 delete from buffer_linkage_assigned_days_1511818252
2719 where linkage_instance_id = 18201930
2720
2721 (822.2ms) /* UnassignedDaysRule */
2722 UPDATE buffer_staff_assigned_days_1511818252 as sad
2723 inner join call_types as ct
2724 on ct.id = 12734
2725 and ct.unassigned_day = 0
2726 SET sad.is_assigned = 1
2727 WHERE sad.staff_id = 20739
2728 AND sad.schedule_on = '2019-08-02'
2729
2730 (152.6ms) delete
2731 from buffer_linkage_assignment_limits_1511818252
2732 where
2733 staff_id = 20739
2734 and linkage_instance_id = 18201930
2735
2736 (154.7ms) insert into buffer_staff_assignment_limits_1511818252 (
2737 staff_id,
2738 assignment_limit_id,
2739 period_id,
2740 consecutive_days_mask,
2741 current_value
2742 )
2743 select
2744 sc.staff_id,
2745 al.id as assignment_limit_id,
2746 alp.id as period_id,
2747 case
2748 when (alp.limit_subtype_mask = 8 /* consecutive */) then
2749 (1 << datediff(sc.schedule_on, alp.start_date))
2750 else 0
2751 end as consecutive_days_mask,
2752 case
2753 when (al.limit_type = 0 /* assignments */) then 1.0
2754 when (al.limit_type = 1 /* points */) then ci.points
2755 when (al.limit_type = 2 /* hours */) then ci.hours
2756 else 0
2757 end as current_value
2758 from
2759 buffer_schedules_1511818252 as sc
2760 inner join buffer_assignment_limit_call_types_1511818252 as alct
2761 on sc.call_type_id = alct.call_type_id
2762 inner join buffer_assignment_limits_1511818252 as al
2763 on alct.assignment_limit_id = al.id
2764 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
2765 inner join buffer_assignment_limit_staffs_1511818252 as als
2766 on al.id = als.assignment_limit_id
2767 and sc.staff_id = als.staff_id
2768 inner join assign_staff_types as ast
2769 on sc.staff_id = ast.staff_id
2770 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
2771 inner join buffer_assignment_limit_staff_types_1511818252 as alst
2772 on al.id = alst.assignment_limit_id
2773 and ast.staff_type_id = alst.staff_type_id
2774 inner join buffer_assignment_limit_periods_1511818252 as alp
2775 on al.id = alp.assignment_limit_id
2776 and sc.schedule_on between alp.start_date and alp.end_date
2777 inner join buffer_counter_increments_1511818252 as ci
2778 on sc.id = ci.schedule_id
2779 where
2780 sc.schedule_on = '2019-08-02'
2781 and sc.call_type_id = 12734
2782 and sc.staff_id = 20739
2783 on duplicate key update
2784 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
2785 current_value = current_value + values(current_value)
2786
2787INFO : (02-15 13:33:50) scheduled for: plan unit: [id:18201930] [2019-08-02 - SSGE], Ron Dobrovinsky. DEPTH(2)
2788 (571.6ms) select
2789 coalesce(max(
2790 case
2791 when (ct.max_weekday_assignments > 0) then 1
2792 else 0
2793 end
2794 ), 0) as weekday_flag,
2795 coalesce(max(
2796 case
2797 when (ct.max_weekend_assignments > 0) then 1
2798 else 0
2799 end
2800 ), 0) as weekend_flag
2801 from
2802 buffer_auto_populate_slots_1511818252 as aps
2803 inner join call_types as ct
2804 on aps.call_type_id = ct.id
2805
2806 (145.9ms) /* NegativeLinkRule */
2807 delete ca
2808 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2809 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
2810 on ca.schedule_id = nr.schedule_id
2811 and ca.staff_id = nr.staff_id
2812
2813 (173.6ms) /* SpecializationOverlapRule */
2814 delete ca
2815 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2816 inner join buffer_schedules_1511818252 as sc
2817 on ca.schedule_id = sc.id
2818 inner join buffer_staff_specializations_1511818252 as ssp
2819 on ca.staff_id = ssp.staff_id
2820 inner join max_applicable_call_types as mact
2821 on ssp.specialization_id = mact.specialization_id
2822 and sc.call_type_id = mact.call_type_id
2823 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
2824 on ssp.specialization_id = sdc.specialization_id
2825 and sc.schedule_on = sdc.schedule_on
2826 and sdc.staff_id is null
2827 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
2828 on ssp.specialization_id = sdc_2.specialization_id
2829 and sc.schedule_on = sdc_2.schedule_on
2830 and ca.staff_id = sdc_2.staff_id
2831 where sdc.should_be_evaluated = 1
2832 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
2833
2834 (145.8ms) /* BrokenLinkageRule */
2835 truncate table buffer_combined_availability_linkages_1511818252
2836
2837 (152.2ms) insert into buffer_combined_availability_linkages_1511818252 (
2838 staff_id,
2839 linkage_instance_id,
2840 slots_count,
2841 is_primary,
2842 has_violations
2843 )
2844 select
2845 staff_id,
2846 linkage_instance_id,
2847 count(1) as slots_count,
2848 min(is_primary) as is_primary,
2849 max(has_violations) as has_violations
2850 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
2851 group by
2852 staff_id,
2853 linkage_instance_id
2854 order by null
2855
2856 (153.4ms) delete ca
2857 from
2858 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
2859 inner join buffer_schedules_1511818252 as sc
2860 on ca.schedule_id = sc.id
2861 inner join buffer_linkages_1511818252 as l
2862 on sc.linkage_instance_id = l.linkage_instance_id
2863 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2864 on ca.staff_id = ls_1.staff_id
2865 and sc.linkage_instance_id = ls_1.linkage_instance_id
2866 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
2867 on ca.staff_id = ls_2.staff_id
2868 and sc.linkage_instance_id = ls_2.linkage_instance_id
2869 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
2870
2871 (182.0ms) select straight_join
2872 sc.schedule_on,
2873 sc.call_type_id,
2874 count(distinct ca.staff_id) as available_members_count,
2875 group_concat(
2876 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
2877 order by ls_1.is_primary desc, ca.staff_id
2878 separator ', '
2879 ) as available_members_ids,
2880 sc.linkage_instance_id,
2881 l.slots_count_total as linkage_slots_count
2882 from buffer_combined_availabilities_1511818252 as ca
2883 inner join buffer_schedules_1511818252 as sc
2884 on ca.schedule_id = sc.id
2885 inner join buffer_linkages_1511818252 as l
2886 on sc.linkage_instance_id = l.linkage_instance_id
2887 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
2888 on ca.staff_id = ls_1.staff_id
2889 and sc.linkage_instance_id = ls_1.linkage_instance_id
2890 and ls_1.has_violations = 0
2891 where sc.should_be_evaluated = 1
2892 and sc.should_be_populated = 1
2893 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
2894 order by available_members_count, sc.schedule_on, sc.display_order
2895 limit 1
2896
2897 (153.6ms) truncate table buffer_rule_scores_1511818252
2898
2899 (152.4ms) truncate table buffer_rule_calc_filters_1511818252
2900
2901 (152.5ms) insert into buffer_rule_calc_filters_1511818252 (
2902 linkage_instance_id,
2903 staff_id
2904 ) values (6201931, 20739)
2905
2906 (155.7ms) truncate table buffer_rule_scores_with_corrections_1511818252
2907
2908 (149.3ms) insert into buffer_rule_scores_with_corrections_1511818252 (
2909 linkage_instance_id, schedule_id, staff_id, rule_name, value
2910 )
2911 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
2912 ifnull(rule_scores.value, max_values.max_value + 1) as value
2913 from buffer_rule_scores_1511818252 as rule_scores
2914 inner join (
2915 select rule_name, max(value) as max_value
2916 from buffer_rule_scores_1511818252
2917 group by rule_name
2918 order by null
2919 ) as max_values
2920 on rule_scores.rule_name = max_values.rule_name
2921 order by null
2922
2923 (303.2ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
2924 from (
2925 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
2926 sum(
2927 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
2928 rules.weight *
2929 total_values.slots_count
2930 ) as slot_scores
2931 from buffer_rule_scores_with_corrections_1511818252 as rule_scores
2932 inner join (
2933 select rule_name, sum(abs(value)) as total_value,
2934 count(distinct schedule_id) as slots_count
2935 from buffer_rule_scores_with_corrections_1511818252
2936 group by rule_name
2937 order by null
2938 ) as total_values
2939 on rule_scores.rule_name = total_values.rule_name
2940 inner join rules
2941 on rule_scores.rule_name = rules.type
2942 where rules.rule_set_id = 1538
2943 and rules.applied = 1
2944 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
2945 order by null
2946 ) as staff_scores
2947 group by linkage_instance_id, staff_id
2948 order by round(avg(slot_scores), 5) desc, rand()
2949 limit 1
2950
2951WARN : (02-15 13:33:53) No score found. Using fake score...
2952 PublishedBlock Load (150.6ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND ('2019-08-05' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1396 ORDER BY `published_blocks`.`id` ASC LIMIT 1
2953 (169.2ms) BEGIN
2954 (147.8ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
2955 FROM call_types AS ct
2956 INNER JOIN calendar_schedules AS cs
2957 ON ct.calendar_schedule_id = cs.id
2958 INNER JOIN blocks AS b
2959 ON b.client_id = ct.client_id
2960 AND b.block_schedule_id = cs.block_schedule_id
2961 AND '2019-08-05' BETWEEN b.start_date AND b.end_date
2962 INNER JOIN published_blocks AS pb
2963 ON pb.client_id = ct.client_id
2964 AND pb.block_id = b.id
2965 AND pb.calendar_schedule_id = cs.id
2966 LEFT JOIN published_block_call_types AS pbct
2967 ON pbct.client_id = ct.client_id
2968 AND pbct.assignment_id = ct.id
2969 AND pbct.assignment_type = 'CallType'
2970 AND pbct.block_id = b.id
2971 AND (
2972 (1 << (DAYOFWEEK('2019-08-05') - 1) & pbct.days_of_week_mask) > 0
2973 )
2974 WHERE ct.id = 12734
2975 AND (
2976 (
2977 pb.assignment_ids IS NULL
2978 AND (1 << (DAYOFWEEK('2019-08-05') - 1) & pb.days_of_week_mask) > 0
2979 )
2980 OR pbct.id IS NOT NULL
2981 )
2982
2983 CallType Load (309.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12734 LIMIT 1
2984 Client Load (597.3ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
2985 Staff Load (287.4ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20739 LIMIT 1
2986 Schedule Exists (162.8ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12734 AND `schedules`.`client_id` = 325 AND `schedules`.`schedule_on` = '2019-08-05' LIMIT 1
2987 SQL (457.5ms) INSERT INTO `schedules` (`client_id`, `call_type_id`, `staff_id`, `schedule_on`, `created_at`, `updated_at`, `weekend_on`, `dayofweek_on`, `populate_method`, `creator_id`) VALUES (325, 12734, 20739, '2019-08-05', '2019-02-15 13:33:55', '2019-02-15 13:33:55', 0, 2, 'auto_populate_step', 20695)
2988 VisibleAssignment Load (145.9ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 325 AND `visible_assignments`.`date` = '2019-08-05' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12734 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
2989 (143.7ms) COMMIT
2990FLUSHED: nil:nil
2991 (459.9ms) UPDATE buffer_schedules_1511818252
2992 SET staff_id = 20739,
2993 should_be_populated = 0,
2994 should_be_evaluated = 0
2995 WHERE
2996 call_type_id = 12734
2997 AND schedule_on = '2019-08-05'
2998
2999 (150.4ms) delete from buffer_combined_availabilities_1511818252
3000 where linkage_instance_id = 6201931
3001
3002 (149.1ms) UPDATE buffer_rule_calc_schedules_1511818252
3003 SET staff_id = 20739
3004 WHERE call_type_id = 12734
3005 AND schedule_on = '2019-08-05'
3006
3007 (147.1ms) INSERT INTO buffer_rule_calc_schedules_1511818252
3008 SET staff_id = 20739,
3009 call_type_id = 12734,
3010 schedule_on = '2019-08-05',
3011 day_of_week_mask = 2
3012
3013 (154.3ms) /* NegativeLinkRule */
3014 UPDATE buffer_negative_relations_1511818252
3015 SET staff_id = 20739
3016 WHERE affected_by_schedule_id = (
3017 SELECT id
3018 FROM buffer_schedules_1511818252
3019 WHERE schedule_on = '2019-08-05'
3020 AND call_type_id = 12734
3021 )
3022
3023 (152.6ms) /* SpecializationOverlapRule */
3024 update buffer_staff_specializations_1511818252 as st_sp
3025 inner join max_applicable_call_types as mact
3026 on st_sp.specialization_id = mact.specialization_id
3027 and mact.call_type_id = 12734
3028 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
3029 on st_sp.specialization_id = sp_dc.specialization_id
3030 and sp_dc.staff_id is null
3031 and sp_dc.schedule_on = '2019-08-05'
3032 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
3033 on st_sp.specialization_id = sp_dc_2.specialization_id
3034 and st_sp.staff_id = sp_dc_2.staff_id
3035 and sp_dc_2.schedule_on = '2019-08-05'
3036 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
3037 where st_sp.staff_id = 20739
3038 and sp_dc_2.id is null
3039
3040 (318.5ms) insert into buffer_specialization_daily_counts_1511818252 (
3041 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
3042 )
3043 select sp.id as specialization_id,
3044 20739 as staff_id,
3045 '2019-08-05' as schedule_on,
3046 1 as staffs_scheduled,
3047 sp.max_scheduled,
3048 sp_dc.should_be_evaluated
3049 from buffer_staff_specializations_1511818252 as st_sp
3050 inner join specializations as sp
3051 on st_sp.specialization_id = sp.id
3052 inner join max_applicable_call_types as mact
3053 on st_sp.specialization_id = mact.specialization_id
3054 and mact.call_type_id = 12734
3055 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
3056 on st_sp.specialization_id = sp_dc.specialization_id
3057 and sp_dc.staff_id is null
3058 and sp_dc.schedule_on = '2019-08-05'
3059 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
3060 on st_sp.specialization_id = sp_dc_2.specialization_id
3061 and st_sp.staff_id = sp_dc_2.staff_id
3062 and sp_dc_2.schedule_on = '2019-08-05'
3063 where st_sp.staff_id = 20739
3064 and sp_dc_2.id is null
3065
3066 (462.0ms) /* MaxAssignmentsRule */
3067 delete from buffer_linkage_call_type_assignments_1511818252
3068 where linkage_instance_id = 6201931;
3069
3070 (461.1ms) /* MaxAssignmentsRule */
3071 update
3072 buffer_schedules_1511818252 as sc
3073 inner join call_types as ct
3074 on sc.call_type_id = ct.id
3075 and ct.active = 1
3076 inner join buffer_call_type_assignments_1511818252 as cta
3077 on sc.staff_id = cta.staff_id
3078 and sc.call_type_id = cta.call_type_id
3079 and sc.is_weekend = cta.is_weekend
3080 and sc.period_offset = cta.period_offset
3081 and sc.calendar_schedule_id = cta.calendar_schedule_id
3082 set cta.assignments_count = cta.assignments_count + 1
3083 where
3084 sc.schedule_on = '2019-08-05'
3085 and sc.call_type_id = 12734
3086 and sc.staff_id = 20739
3087
3088 (149.5ms) /* UnassignedDaysRule */
3089 delete from buffer_linkage_assigned_days_1511818252
3090 where linkage_instance_id = 6201931
3091
3092 (153.9ms) /* UnassignedDaysRule */
3093 UPDATE buffer_staff_assigned_days_1511818252 as sad
3094 inner join call_types as ct
3095 on ct.id = 12734
3096 and ct.unassigned_day = 0
3097 SET sad.is_assigned = 1
3098 WHERE sad.staff_id = 20739
3099 AND sad.schedule_on = '2019-08-05'
3100
3101 (473.8ms) delete
3102 from buffer_linkage_assignment_limits_1511818252
3103 where
3104 staff_id = 20739
3105 and linkage_instance_id = 6201931
3106
3107 (175.7ms) insert into buffer_staff_assignment_limits_1511818252 (
3108 staff_id,
3109 assignment_limit_id,
3110 period_id,
3111 consecutive_days_mask,
3112 current_value
3113 )
3114 select
3115 sc.staff_id,
3116 al.id as assignment_limit_id,
3117 alp.id as period_id,
3118 case
3119 when (alp.limit_subtype_mask = 8 /* consecutive */) then
3120 (1 << datediff(sc.schedule_on, alp.start_date))
3121 else 0
3122 end as consecutive_days_mask,
3123 case
3124 when (al.limit_type = 0 /* assignments */) then 1.0
3125 when (al.limit_type = 1 /* points */) then ci.points
3126 when (al.limit_type = 2 /* hours */) then ci.hours
3127 else 0
3128 end as current_value
3129 from
3130 buffer_schedules_1511818252 as sc
3131 inner join buffer_assignment_limit_call_types_1511818252 as alct
3132 on sc.call_type_id = alct.call_type_id
3133 inner join buffer_assignment_limits_1511818252 as al
3134 on alct.assignment_limit_id = al.id
3135 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3136 inner join buffer_assignment_limit_staffs_1511818252 as als
3137 on al.id = als.assignment_limit_id
3138 and sc.staff_id = als.staff_id
3139 inner join assign_staff_types as ast
3140 on sc.staff_id = ast.staff_id
3141 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
3142 inner join buffer_assignment_limit_staff_types_1511818252 as alst
3143 on al.id = alst.assignment_limit_id
3144 and ast.staff_type_id = alst.staff_type_id
3145 inner join buffer_assignment_limit_periods_1511818252 as alp
3146 on al.id = alp.assignment_limit_id
3147 and sc.schedule_on between alp.start_date and alp.end_date
3148 inner join buffer_counter_increments_1511818252 as ci
3149 on sc.id = ci.schedule_id
3150 where
3151 sc.schedule_on = '2019-08-05'
3152 and sc.call_type_id = 12734
3153 and sc.staff_id = 20739
3154 on duplicate key update
3155 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
3156 current_value = current_value + values(current_value)
3157
3158INFO : (02-15 13:33:59) scheduled for: plan unit: [id:6201931] [2019-08-05 - SSGE], Ron Dobrovinsky. DEPTH(3)
3159 (146.0ms) select
3160 coalesce(max(
3161 case
3162 when (ct.max_weekday_assignments > 0) then 1
3163 else 0
3164 end
3165 ), 0) as weekday_flag,
3166 coalesce(max(
3167 case
3168 when (ct.max_weekend_assignments > 0) then 1
3169 else 0
3170 end
3171 ), 0) as weekend_flag
3172 from
3173 buffer_auto_populate_slots_1511818252 as aps
3174 inner join call_types as ct
3175 on aps.call_type_id = ct.id
3176
3177 (155.2ms) /* NegativeLinkRule */
3178 delete ca
3179 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3180 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
3181 on ca.schedule_id = nr.schedule_id
3182 and ca.staff_id = nr.staff_id
3183
3184 (834.2ms) /* SpecializationOverlapRule */
3185 delete ca
3186 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3187 inner join buffer_schedules_1511818252 as sc
3188 on ca.schedule_id = sc.id
3189 inner join buffer_staff_specializations_1511818252 as ssp
3190 on ca.staff_id = ssp.staff_id
3191 inner join max_applicable_call_types as mact
3192 on ssp.specialization_id = mact.specialization_id
3193 and sc.call_type_id = mact.call_type_id
3194 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3195 on ssp.specialization_id = sdc.specialization_id
3196 and sc.schedule_on = sdc.schedule_on
3197 and sdc.staff_id is null
3198 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3199 on ssp.specialization_id = sdc_2.specialization_id
3200 and sc.schedule_on = sdc_2.schedule_on
3201 and ca.staff_id = sdc_2.staff_id
3202 where sdc.should_be_evaluated = 1
3203 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3204
3205 (931.4ms) /* BrokenLinkageRule */
3206 truncate table buffer_combined_availability_linkages_1511818252
3207
3208 (152.5ms) insert into buffer_combined_availability_linkages_1511818252 (
3209 staff_id,
3210 linkage_instance_id,
3211 slots_count,
3212 is_primary,
3213 has_violations
3214 )
3215 select
3216 staff_id,
3217 linkage_instance_id,
3218 count(1) as slots_count,
3219 min(is_primary) as is_primary,
3220 max(has_violations) as has_violations
3221 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
3222 group by
3223 staff_id,
3224 linkage_instance_id
3225 order by null
3226
3227 (149.0ms) delete ca
3228 from
3229 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3230 inner join buffer_schedules_1511818252 as sc
3231 on ca.schedule_id = sc.id
3232 inner join buffer_linkages_1511818252 as l
3233 on sc.linkage_instance_id = l.linkage_instance_id
3234 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3235 on ca.staff_id = ls_1.staff_id
3236 and sc.linkage_instance_id = ls_1.linkage_instance_id
3237 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3238 on ca.staff_id = ls_2.staff_id
3239 and sc.linkage_instance_id = ls_2.linkage_instance_id
3240 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
3241
3242 (157.4ms) select straight_join
3243 sc.schedule_on,
3244 sc.call_type_id,
3245 count(distinct ca.staff_id) as available_members_count,
3246 group_concat(
3247 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3248 order by ls_1.is_primary desc, ca.staff_id
3249 separator ', '
3250 ) as available_members_ids,
3251 sc.linkage_instance_id,
3252 l.slots_count_total as linkage_slots_count
3253 from buffer_combined_availabilities_1511818252 as ca
3254 inner join buffer_schedules_1511818252 as sc
3255 on ca.schedule_id = sc.id
3256 inner join buffer_linkages_1511818252 as l
3257 on sc.linkage_instance_id = l.linkage_instance_id
3258 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3259 on ca.staff_id = ls_1.staff_id
3260 and sc.linkage_instance_id = ls_1.linkage_instance_id
3261 and ls_1.has_violations = 0
3262 where sc.should_be_evaluated = 1
3263 and sc.should_be_populated = 1
3264 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3265 order by available_members_count, sc.schedule_on, sc.display_order
3266 limit 1
3267
3268 (451.3ms) truncate table buffer_rule_scores_1511818252
3269
3270 (150.6ms) truncate table buffer_rule_calc_filters_1511818252
3271
3272 (165.7ms) insert into buffer_rule_calc_filters_1511818252 (
3273 linkage_instance_id,
3274 staff_id
3275 ) values (9201931, 20739)
3276
3277 (141.6ms) truncate table buffer_rule_scores_with_corrections_1511818252
3278
3279 (162.9ms) insert into buffer_rule_scores_with_corrections_1511818252 (
3280 linkage_instance_id, schedule_id, staff_id, rule_name, value
3281 )
3282 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3283 ifnull(rule_scores.value, max_values.max_value + 1) as value
3284 from buffer_rule_scores_1511818252 as rule_scores
3285 inner join (
3286 select rule_name, max(value) as max_value
3287 from buffer_rule_scores_1511818252
3288 group by rule_name
3289 order by null
3290 ) as max_values
3291 on rule_scores.rule_name = max_values.rule_name
3292 order by null
3293
3294 (152.3ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3295 from (
3296 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3297 sum(
3298 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3299 rules.weight *
3300 total_values.slots_count
3301 ) as slot_scores
3302 from buffer_rule_scores_with_corrections_1511818252 as rule_scores
3303 inner join (
3304 select rule_name, sum(abs(value)) as total_value,
3305 count(distinct schedule_id) as slots_count
3306 from buffer_rule_scores_with_corrections_1511818252
3307 group by rule_name
3308 order by null
3309 ) as total_values
3310 on rule_scores.rule_name = total_values.rule_name
3311 inner join rules
3312 on rule_scores.rule_name = rules.type
3313 where rules.rule_set_id = 1538
3314 and rules.applied = 1
3315 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3316 order by null
3317 ) as staff_scores
3318 group by linkage_instance_id, staff_id
3319 order by round(avg(slot_scores), 5) desc, rand()
3320 limit 1
3321
3322WARN : (02-15 13:34:03) No score found. Using fake score...
3323 PublishedBlock Load (568.5ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND ('2019-08-06' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1396 ORDER BY `published_blocks`.`id` ASC LIMIT 1
3324 (139.4ms) BEGIN
3325 (456.8ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
3326 FROM call_types AS ct
3327 INNER JOIN calendar_schedules AS cs
3328 ON ct.calendar_schedule_id = cs.id
3329 INNER JOIN blocks AS b
3330 ON b.client_id = ct.client_id
3331 AND b.block_schedule_id = cs.block_schedule_id
3332 AND '2019-08-06' BETWEEN b.start_date AND b.end_date
3333 INNER JOIN published_blocks AS pb
3334 ON pb.client_id = ct.client_id
3335 AND pb.block_id = b.id
3336 AND pb.calendar_schedule_id = cs.id
3337 LEFT JOIN published_block_call_types AS pbct
3338 ON pbct.client_id = ct.client_id
3339 AND pbct.assignment_id = ct.id
3340 AND pbct.assignment_type = 'CallType'
3341 AND pbct.block_id = b.id
3342 AND (
3343 (1 << (DAYOFWEEK('2019-08-06') - 1) & pbct.days_of_week_mask) > 0
3344 )
3345 WHERE ct.id = 12734
3346 AND (
3347 (
3348 pb.assignment_ids IS NULL
3349 AND (1 << (DAYOFWEEK('2019-08-06') - 1) & pb.days_of_week_mask) > 0
3350 )
3351 OR pbct.id IS NOT NULL
3352 )
3353
3354 CallType Load (297.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12734 LIMIT 1
3355 Client Load (590.3ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
3356 Staff Load (1423.6ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20739 LIMIT 1
3357 Schedule Exists (146.9ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12734 AND `schedules`.`client_id` = 325 AND `schedules`.`schedule_on` = '2019-08-06' LIMIT 1
3358 SQL (151.7ms) INSERT INTO `schedules` (`client_id`, `call_type_id`, `staff_id`, `schedule_on`, `created_at`, `updated_at`, `weekend_on`, `dayofweek_on`, `populate_method`, `creator_id`) VALUES (325, 12734, 20739, '2019-08-06', '2019-02-15 13:34:06', '2019-02-15 13:34:06', 0, 3, 'auto_populate_step', 20695)
3359 VisibleAssignment Load (145.3ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 325 AND `visible_assignments`.`date` = '2019-08-06' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12734 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
3360 (143.6ms) COMMIT
3361FLUSHED: nil:nil
3362 (154.1ms) UPDATE buffer_schedules_1511818252
3363 SET staff_id = 20739,
3364 should_be_populated = 0,
3365 should_be_evaluated = 0
3366 WHERE
3367 call_type_id = 12734
3368 AND schedule_on = '2019-08-06'
3369
3370 (147.5ms) delete from buffer_combined_availabilities_1511818252
3371 where linkage_instance_id = 9201931
3372
3373 (144.8ms) UPDATE buffer_rule_calc_schedules_1511818252
3374 SET staff_id = 20739
3375 WHERE call_type_id = 12734
3376 AND schedule_on = '2019-08-06'
3377
3378 (153.6ms) INSERT INTO buffer_rule_calc_schedules_1511818252
3379 SET staff_id = 20739,
3380 call_type_id = 12734,
3381 schedule_on = '2019-08-06',
3382 day_of_week_mask = 4
3383
3384 (544.4ms) /* NegativeLinkRule */
3385 UPDATE buffer_negative_relations_1511818252
3386 SET staff_id = 20739
3387 WHERE affected_by_schedule_id = (
3388 SELECT id
3389 FROM buffer_schedules_1511818252
3390 WHERE schedule_on = '2019-08-06'
3391 AND call_type_id = 12734
3392 )
3393
3394 (142.9ms) /* SpecializationOverlapRule */
3395 update buffer_staff_specializations_1511818252 as st_sp
3396 inner join max_applicable_call_types as mact
3397 on st_sp.specialization_id = mact.specialization_id
3398 and mact.call_type_id = 12734
3399 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
3400 on st_sp.specialization_id = sp_dc.specialization_id
3401 and sp_dc.staff_id is null
3402 and sp_dc.schedule_on = '2019-08-06'
3403 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
3404 on st_sp.specialization_id = sp_dc_2.specialization_id
3405 and st_sp.staff_id = sp_dc_2.staff_id
3406 and sp_dc_2.schedule_on = '2019-08-06'
3407 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
3408 where st_sp.staff_id = 20739
3409 and sp_dc_2.id is null
3410
3411 (148.6ms) insert into buffer_specialization_daily_counts_1511818252 (
3412 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
3413 )
3414 select sp.id as specialization_id,
3415 20739 as staff_id,
3416 '2019-08-06' as schedule_on,
3417 1 as staffs_scheduled,
3418 sp.max_scheduled,
3419 sp_dc.should_be_evaluated
3420 from buffer_staff_specializations_1511818252 as st_sp
3421 inner join specializations as sp
3422 on st_sp.specialization_id = sp.id
3423 inner join max_applicable_call_types as mact
3424 on st_sp.specialization_id = mact.specialization_id
3425 and mact.call_type_id = 12734
3426 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
3427 on st_sp.specialization_id = sp_dc.specialization_id
3428 and sp_dc.staff_id is null
3429 and sp_dc.schedule_on = '2019-08-06'
3430 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
3431 on st_sp.specialization_id = sp_dc_2.specialization_id
3432 and st_sp.staff_id = sp_dc_2.staff_id
3433 and sp_dc_2.schedule_on = '2019-08-06'
3434 where st_sp.staff_id = 20739
3435 and sp_dc_2.id is null
3436
3437 (512.5ms) /* MaxAssignmentsRule */
3438 delete from buffer_linkage_call_type_assignments_1511818252
3439 where linkage_instance_id = 9201931;
3440
3441 (154.9ms) /* MaxAssignmentsRule */
3442 update
3443 buffer_schedules_1511818252 as sc
3444 inner join call_types as ct
3445 on sc.call_type_id = ct.id
3446 and ct.active = 1
3447 inner join buffer_call_type_assignments_1511818252 as cta
3448 on sc.staff_id = cta.staff_id
3449 and sc.call_type_id = cta.call_type_id
3450 and sc.is_weekend = cta.is_weekend
3451 and sc.period_offset = cta.period_offset
3452 and sc.calendar_schedule_id = cta.calendar_schedule_id
3453 set cta.assignments_count = cta.assignments_count + 1
3454 where
3455 sc.schedule_on = '2019-08-06'
3456 and sc.call_type_id = 12734
3457 and sc.staff_id = 20739
3458
3459 (143.9ms) /* UnassignedDaysRule */
3460 delete from buffer_linkage_assigned_days_1511818252
3461 where linkage_instance_id = 9201931
3462
3463 (153.8ms) /* UnassignedDaysRule */
3464 UPDATE buffer_staff_assigned_days_1511818252 as sad
3465 inner join call_types as ct
3466 on ct.id = 12734
3467 and ct.unassigned_day = 0
3468 SET sad.is_assigned = 1
3469 WHERE sad.staff_id = 20739
3470 AND sad.schedule_on = '2019-08-06'
3471
3472 (152.9ms) delete
3473 from buffer_linkage_assignment_limits_1511818252
3474 where
3475 staff_id = 20739
3476 and linkage_instance_id = 9201931
3477
3478 (152.1ms) insert into buffer_staff_assignment_limits_1511818252 (
3479 staff_id,
3480 assignment_limit_id,
3481 period_id,
3482 consecutive_days_mask,
3483 current_value
3484 )
3485 select
3486 sc.staff_id,
3487 al.id as assignment_limit_id,
3488 alp.id as period_id,
3489 case
3490 when (alp.limit_subtype_mask = 8 /* consecutive */) then
3491 (1 << datediff(sc.schedule_on, alp.start_date))
3492 else 0
3493 end as consecutive_days_mask,
3494 case
3495 when (al.limit_type = 0 /* assignments */) then 1.0
3496 when (al.limit_type = 1 /* points */) then ci.points
3497 when (al.limit_type = 2 /* hours */) then ci.hours
3498 else 0
3499 end as current_value
3500 from
3501 buffer_schedules_1511818252 as sc
3502 inner join buffer_assignment_limit_call_types_1511818252 as alct
3503 on sc.call_type_id = alct.call_type_id
3504 inner join buffer_assignment_limits_1511818252 as al
3505 on alct.assignment_limit_id = al.id
3506 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3507 inner join buffer_assignment_limit_staffs_1511818252 as als
3508 on al.id = als.assignment_limit_id
3509 and sc.staff_id = als.staff_id
3510 inner join assign_staff_types as ast
3511 on sc.staff_id = ast.staff_id
3512 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
3513 inner join buffer_assignment_limit_staff_types_1511818252 as alst
3514 on al.id = alst.assignment_limit_id
3515 and ast.staff_type_id = alst.staff_type_id
3516 inner join buffer_assignment_limit_periods_1511818252 as alp
3517 on al.id = alp.assignment_limit_id
3518 and sc.schedule_on between alp.start_date and alp.end_date
3519 inner join buffer_counter_increments_1511818252 as ci
3520 on sc.id = ci.schedule_id
3521 where
3522 sc.schedule_on = '2019-08-06'
3523 and sc.call_type_id = 12734
3524 and sc.staff_id = 20739
3525 on duplicate key update
3526 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
3527 current_value = current_value + values(current_value)
3528
3529INFO : (02-15 13:34:09) scheduled for: plan unit: [id:9201931] [2019-08-06 - SSGE], Ron Dobrovinsky. DEPTH(4)
3530 (160.8ms) select
3531 coalesce(max(
3532 case
3533 when (ct.max_weekday_assignments > 0) then 1
3534 else 0
3535 end
3536 ), 0) as weekday_flag,
3537 coalesce(max(
3538 case
3539 when (ct.max_weekend_assignments > 0) then 1
3540 else 0
3541 end
3542 ), 0) as weekend_flag
3543 from
3544 buffer_auto_populate_slots_1511818252 as aps
3545 inner join call_types as ct
3546 on aps.call_type_id = ct.id
3547
3548 (143.3ms) /* NegativeLinkRule */
3549 delete ca
3550 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3551 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
3552 on ca.schedule_id = nr.schedule_id
3553 and ca.staff_id = nr.staff_id
3554
3555 (315.1ms) /* SpecializationOverlapRule */
3556 delete ca
3557 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3558 inner join buffer_schedules_1511818252 as sc
3559 on ca.schedule_id = sc.id
3560 inner join buffer_staff_specializations_1511818252 as ssp
3561 on ca.staff_id = ssp.staff_id
3562 inner join max_applicable_call_types as mact
3563 on ssp.specialization_id = mact.specialization_id
3564 and sc.call_type_id = mact.call_type_id
3565 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3566 on ssp.specialization_id = sdc.specialization_id
3567 and sc.schedule_on = sdc.schedule_on
3568 and sdc.staff_id is null
3569 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3570 on ssp.specialization_id = sdc_2.specialization_id
3571 and sc.schedule_on = sdc_2.schedule_on
3572 and ca.staff_id = sdc_2.staff_id
3573 where sdc.should_be_evaluated = 1
3574 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3575
3576 (949.5ms) /* BrokenLinkageRule */
3577 truncate table buffer_combined_availability_linkages_1511818252
3578
3579 (144.9ms) insert into buffer_combined_availability_linkages_1511818252 (
3580 staff_id,
3581 linkage_instance_id,
3582 slots_count,
3583 is_primary,
3584 has_violations
3585 )
3586 select
3587 staff_id,
3588 linkage_instance_id,
3589 count(1) as slots_count,
3590 min(is_primary) as is_primary,
3591 max(has_violations) as has_violations
3592 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
3593 group by
3594 staff_id,
3595 linkage_instance_id
3596 order by null
3597
3598 (152.9ms) delete ca
3599 from
3600 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3601 inner join buffer_schedules_1511818252 as sc
3602 on ca.schedule_id = sc.id
3603 inner join buffer_linkages_1511818252 as l
3604 on sc.linkage_instance_id = l.linkage_instance_id
3605 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3606 on ca.staff_id = ls_1.staff_id
3607 and sc.linkage_instance_id = ls_1.linkage_instance_id
3608 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3609 on ca.staff_id = ls_2.staff_id
3610 and sc.linkage_instance_id = ls_2.linkage_instance_id
3611 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
3612
3613 (645.5ms) select straight_join
3614 sc.schedule_on,
3615 sc.call_type_id,
3616 count(distinct ca.staff_id) as available_members_count,
3617 group_concat(
3618 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3619 order by ls_1.is_primary desc, ca.staff_id
3620 separator ', '
3621 ) as available_members_ids,
3622 sc.linkage_instance_id,
3623 l.slots_count_total as linkage_slots_count
3624 from buffer_combined_availabilities_1511818252 as ca
3625 inner join buffer_schedules_1511818252 as sc
3626 on ca.schedule_id = sc.id
3627 inner join buffer_linkages_1511818252 as l
3628 on sc.linkage_instance_id = l.linkage_instance_id
3629 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3630 on ca.staff_id = ls_1.staff_id
3631 and sc.linkage_instance_id = ls_1.linkage_instance_id
3632 and ls_1.has_violations = 0
3633 where sc.should_be_evaluated = 1
3634 and sc.should_be_populated = 1
3635 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
3636 order by available_members_count, sc.schedule_on, sc.display_order
3637 limit 1
3638
3639 (150.7ms) truncate table buffer_rule_scores_1511818252
3640
3641 (151.3ms) truncate table buffer_rule_calc_filters_1511818252
3642
3643 (152.2ms) insert into buffer_rule_calc_filters_1511818252 (
3644 linkage_instance_id,
3645 staff_id
3646 ) values (12201931, 20739)
3647
3648 (157.3ms) truncate table buffer_rule_scores_with_corrections_1511818252
3649
3650 (155.0ms) insert into buffer_rule_scores_with_corrections_1511818252 (
3651 linkage_instance_id, schedule_id, staff_id, rule_name, value
3652 )
3653 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
3654 ifnull(rule_scores.value, max_values.max_value + 1) as value
3655 from buffer_rule_scores_1511818252 as rule_scores
3656 inner join (
3657 select rule_name, max(value) as max_value
3658 from buffer_rule_scores_1511818252
3659 group by rule_name
3660 order by null
3661 ) as max_values
3662 on rule_scores.rule_name = max_values.rule_name
3663 order by null
3664
3665 (152.8ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
3666 from (
3667 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
3668 sum(
3669 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
3670 rules.weight *
3671 total_values.slots_count
3672 ) as slot_scores
3673 from buffer_rule_scores_with_corrections_1511818252 as rule_scores
3674 inner join (
3675 select rule_name, sum(abs(value)) as total_value,
3676 count(distinct schedule_id) as slots_count
3677 from buffer_rule_scores_with_corrections_1511818252
3678 group by rule_name
3679 order by null
3680 ) as total_values
3681 on rule_scores.rule_name = total_values.rule_name
3682 inner join rules
3683 on rule_scores.rule_name = rules.type
3684 where rules.rule_set_id = 1538
3685 and rules.applied = 1
3686 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
3687 order by null
3688 ) as staff_scores
3689 group by linkage_instance_id, staff_id
3690 order by round(avg(slot_scores), 5) desc, rand()
3691 limit 1
3692
3693WARN : (02-15 13:34:13) No score found. Using fake score...
3694 PublishedBlock Load (141.4ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND ('2019-08-07' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1396 ORDER BY `published_blocks`.`id` ASC LIMIT 1
3695 (534.7ms) BEGIN
3696 (152.2ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
3697 FROM call_types AS ct
3698 INNER JOIN calendar_schedules AS cs
3699 ON ct.calendar_schedule_id = cs.id
3700 INNER JOIN blocks AS b
3701 ON b.client_id = ct.client_id
3702 AND b.block_schedule_id = cs.block_schedule_id
3703 AND '2019-08-07' BETWEEN b.start_date AND b.end_date
3704 INNER JOIN published_blocks AS pb
3705 ON pb.client_id = ct.client_id
3706 AND pb.block_id = b.id
3707 AND pb.calendar_schedule_id = cs.id
3708 LEFT JOIN published_block_call_types AS pbct
3709 ON pbct.client_id = ct.client_id
3710 AND pbct.assignment_id = ct.id
3711 AND pbct.assignment_type = 'CallType'
3712 AND pbct.block_id = b.id
3713 AND (
3714 (1 << (DAYOFWEEK('2019-08-07') - 1) & pbct.days_of_week_mask) > 0
3715 )
3716 WHERE ct.id = 12734
3717 AND (
3718 (
3719 pb.assignment_ids IS NULL
3720 AND (1 << (DAYOFWEEK('2019-08-07') - 1) & pb.days_of_week_mask) > 0
3721 )
3722 OR pbct.id IS NOT NULL
3723 )
3724
3725 CallType Load (297.0ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12734 LIMIT 1
3726 Client Load (601.9ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
3727 Staff Load (351.7ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20739 LIMIT 1
3728 Schedule Exists (151.9ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12734 AND `schedules`.`client_id` = 325 AND `schedules`.`schedule_on` = '2019-08-07' LIMIT 1
3729 SQL (159.8ms) INSERT INTO `schedules` (`client_id`, `call_type_id`, `staff_id`, `schedule_on`, `created_at`, `updated_at`, `weekend_on`, `dayofweek_on`, `populate_method`, `creator_id`) VALUES (325, 12734, 20739, '2019-08-07', '2019-02-15 13:34:15', '2019-02-15 13:34:15', 0, 4, 'auto_populate_step', 20695)
3730 VisibleAssignment Load (152.1ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 325 AND `visible_assignments`.`date` = '2019-08-07' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12734 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
3731 (153.4ms) COMMIT
3732FLUSHED: nil:nil
3733 (453.3ms) UPDATE buffer_schedules_1511818252
3734 SET staff_id = 20739,
3735 should_be_populated = 0,
3736 should_be_evaluated = 0
3737 WHERE
3738 call_type_id = 12734
3739 AND schedule_on = '2019-08-07'
3740
3741 (142.0ms) delete from buffer_combined_availabilities_1511818252
3742 where linkage_instance_id = 12201931
3743
3744 (910.8ms) UPDATE buffer_rule_calc_schedules_1511818252
3745 SET staff_id = 20739
3746 WHERE call_type_id = 12734
3747 AND schedule_on = '2019-08-07'
3748
3749 (511.0ms) INSERT INTO buffer_rule_calc_schedules_1511818252
3750 SET staff_id = 20739,
3751 call_type_id = 12734,
3752 schedule_on = '2019-08-07',
3753 day_of_week_mask = 8
3754
3755 (150.3ms) /* NegativeLinkRule */
3756 UPDATE buffer_negative_relations_1511818252
3757 SET staff_id = 20739
3758 WHERE affected_by_schedule_id = (
3759 SELECT id
3760 FROM buffer_schedules_1511818252
3761 WHERE schedule_on = '2019-08-07'
3762 AND call_type_id = 12734
3763 )
3764
3765 (151.5ms) /* SpecializationOverlapRule */
3766 update buffer_staff_specializations_1511818252 as st_sp
3767 inner join max_applicable_call_types as mact
3768 on st_sp.specialization_id = mact.specialization_id
3769 and mact.call_type_id = 12734
3770 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
3771 on st_sp.specialization_id = sp_dc.specialization_id
3772 and sp_dc.staff_id is null
3773 and sp_dc.schedule_on = '2019-08-07'
3774 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
3775 on st_sp.specialization_id = sp_dc_2.specialization_id
3776 and st_sp.staff_id = sp_dc_2.staff_id
3777 and sp_dc_2.schedule_on = '2019-08-07'
3778 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
3779 where st_sp.staff_id = 20739
3780 and sp_dc_2.id is null
3781
3782 (146.5ms) insert into buffer_specialization_daily_counts_1511818252 (
3783 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
3784 )
3785 select sp.id as specialization_id,
3786 20739 as staff_id,
3787 '2019-08-07' as schedule_on,
3788 1 as staffs_scheduled,
3789 sp.max_scheduled,
3790 sp_dc.should_be_evaluated
3791 from buffer_staff_specializations_1511818252 as st_sp
3792 inner join specializations as sp
3793 on st_sp.specialization_id = sp.id
3794 inner join max_applicable_call_types as mact
3795 on st_sp.specialization_id = mact.specialization_id
3796 and mact.call_type_id = 12734
3797 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
3798 on st_sp.specialization_id = sp_dc.specialization_id
3799 and sp_dc.staff_id is null
3800 and sp_dc.schedule_on = '2019-08-07'
3801 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
3802 on st_sp.specialization_id = sp_dc_2.specialization_id
3803 and st_sp.staff_id = sp_dc_2.staff_id
3804 and sp_dc_2.schedule_on = '2019-08-07'
3805 where st_sp.staff_id = 20739
3806 and sp_dc_2.id is null
3807
3808 (144.3ms) /* MaxAssignmentsRule */
3809 delete from buffer_linkage_call_type_assignments_1511818252
3810 where linkage_instance_id = 12201931;
3811
3812 (148.0ms) /* MaxAssignmentsRule */
3813 update
3814 buffer_schedules_1511818252 as sc
3815 inner join call_types as ct
3816 on sc.call_type_id = ct.id
3817 and ct.active = 1
3818 inner join buffer_call_type_assignments_1511818252 as cta
3819 on sc.staff_id = cta.staff_id
3820 and sc.call_type_id = cta.call_type_id
3821 and sc.is_weekend = cta.is_weekend
3822 and sc.period_offset = cta.period_offset
3823 and sc.calendar_schedule_id = cta.calendar_schedule_id
3824 set cta.assignments_count = cta.assignments_count + 1
3825 where
3826 sc.schedule_on = '2019-08-07'
3827 and sc.call_type_id = 12734
3828 and sc.staff_id = 20739
3829
3830 (148.4ms) /* UnassignedDaysRule */
3831 delete from buffer_linkage_assigned_days_1511818252
3832 where linkage_instance_id = 12201931
3833
3834 (150.4ms) /* UnassignedDaysRule */
3835 UPDATE buffer_staff_assigned_days_1511818252 as sad
3836 inner join call_types as ct
3837 on ct.id = 12734
3838 and ct.unassigned_day = 0
3839 SET sad.is_assigned = 1
3840 WHERE sad.staff_id = 20739
3841 AND sad.schedule_on = '2019-08-07'
3842
3843 (154.5ms) delete
3844 from buffer_linkage_assignment_limits_1511818252
3845 where
3846 staff_id = 20739
3847 and linkage_instance_id = 12201931
3848
3849 (287.5ms) insert into buffer_staff_assignment_limits_1511818252 (
3850 staff_id,
3851 assignment_limit_id,
3852 period_id,
3853 consecutive_days_mask,
3854 current_value
3855 )
3856 select
3857 sc.staff_id,
3858 al.id as assignment_limit_id,
3859 alp.id as period_id,
3860 case
3861 when (alp.limit_subtype_mask = 8 /* consecutive */) then
3862 (1 << datediff(sc.schedule_on, alp.start_date))
3863 else 0
3864 end as consecutive_days_mask,
3865 case
3866 when (al.limit_type = 0 /* assignments */) then 1.0
3867 when (al.limit_type = 1 /* points */) then ci.points
3868 when (al.limit_type = 2 /* hours */) then ci.hours
3869 else 0
3870 end as current_value
3871 from
3872 buffer_schedules_1511818252 as sc
3873 inner join buffer_assignment_limit_call_types_1511818252 as alct
3874 on sc.call_type_id = alct.call_type_id
3875 inner join buffer_assignment_limits_1511818252 as al
3876 on alct.assignment_limit_id = al.id
3877 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
3878 inner join buffer_assignment_limit_staffs_1511818252 as als
3879 on al.id = als.assignment_limit_id
3880 and sc.staff_id = als.staff_id
3881 inner join assign_staff_types as ast
3882 on sc.staff_id = ast.staff_id
3883 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
3884 inner join buffer_assignment_limit_staff_types_1511818252 as alst
3885 on al.id = alst.assignment_limit_id
3886 and ast.staff_type_id = alst.staff_type_id
3887 inner join buffer_assignment_limit_periods_1511818252 as alp
3888 on al.id = alp.assignment_limit_id
3889 and sc.schedule_on between alp.start_date and alp.end_date
3890 inner join buffer_counter_increments_1511818252 as ci
3891 on sc.id = ci.schedule_id
3892 where
3893 sc.schedule_on = '2019-08-07'
3894 and sc.call_type_id = 12734
3895 and sc.staff_id = 20739
3896 on duplicate key update
3897 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
3898 current_value = current_value + values(current_value)
3899
3900INFO : (02-15 13:34:19) scheduled for: plan unit: [id:12201931] [2019-08-07 - SSGE], Ron Dobrovinsky. DEPTH(5)
3901 (149.2ms) select
3902 coalesce(max(
3903 case
3904 when (ct.max_weekday_assignments > 0) then 1
3905 else 0
3906 end
3907 ), 0) as weekday_flag,
3908 coalesce(max(
3909 case
3910 when (ct.max_weekend_assignments > 0) then 1
3911 else 0
3912 end
3913 ), 0) as weekend_flag
3914 from
3915 buffer_auto_populate_slots_1511818252 as aps
3916 inner join call_types as ct
3917 on aps.call_type_id = ct.id
3918
3919 (491.2ms) /* NegativeLinkRule */
3920 delete ca
3921 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3922 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
3923 on ca.schedule_id = nr.schedule_id
3924 and ca.staff_id = nr.staff_id
3925
3926 (142.6ms) /* SpecializationOverlapRule */
3927 delete ca
3928 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3929 inner join buffer_schedules_1511818252 as sc
3930 on ca.schedule_id = sc.id
3931 inner join buffer_staff_specializations_1511818252 as ssp
3932 on ca.staff_id = ssp.staff_id
3933 inner join max_applicable_call_types as mact
3934 on ssp.specialization_id = mact.specialization_id
3935 and sc.call_type_id = mact.call_type_id
3936 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
3937 on ssp.specialization_id = sdc.specialization_id
3938 and sc.schedule_on = sdc.schedule_on
3939 and sdc.staff_id is null
3940 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
3941 on ssp.specialization_id = sdc_2.specialization_id
3942 and sc.schedule_on = sdc_2.schedule_on
3943 and ca.staff_id = sdc_2.staff_id
3944 where sdc.should_be_evaluated = 1
3945 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
3946
3947 (173.9ms) /* BrokenLinkageRule */
3948 truncate table buffer_combined_availability_linkages_1511818252
3949
3950 (516.2ms) insert into buffer_combined_availability_linkages_1511818252 (
3951 staff_id,
3952 linkage_instance_id,
3953 slots_count,
3954 is_primary,
3955 has_violations
3956 )
3957 select
3958 staff_id,
3959 linkage_instance_id,
3960 count(1) as slots_count,
3961 min(is_primary) as is_primary,
3962 max(has_violations) as has_violations
3963 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
3964 group by
3965 staff_id,
3966 linkage_instance_id
3967 order by null
3968
3969 (141.9ms) delete ca
3970 from
3971 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
3972 inner join buffer_schedules_1511818252 as sc
3973 on ca.schedule_id = sc.id
3974 inner join buffer_linkages_1511818252 as l
3975 on sc.linkage_instance_id = l.linkage_instance_id
3976 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
3977 on ca.staff_id = ls_1.staff_id
3978 and sc.linkage_instance_id = ls_1.linkage_instance_id
3979 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
3980 on ca.staff_id = ls_2.staff_id
3981 and sc.linkage_instance_id = ls_2.linkage_instance_id
3982 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
3983
3984 (150.2ms) select straight_join
3985 sc.schedule_on,
3986 sc.call_type_id,
3987 count(distinct ca.staff_id) as available_members_count,
3988 group_concat(
3989 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
3990 order by ls_1.is_primary desc, ca.staff_id
3991 separator ', '
3992 ) as available_members_ids,
3993 sc.linkage_instance_id,
3994 l.slots_count_total as linkage_slots_count
3995 from buffer_combined_availabilities_1511818252 as ca
3996 inner join buffer_schedules_1511818252 as sc
3997 on ca.schedule_id = sc.id
3998 inner join buffer_linkages_1511818252 as l
3999 on sc.linkage_instance_id = l.linkage_instance_id
4000 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
4001 on ca.staff_id = ls_1.staff_id
4002 and sc.linkage_instance_id = ls_1.linkage_instance_id
4003 and ls_1.has_violations = 0
4004 where sc.should_be_evaluated = 1
4005 and sc.should_be_populated = 1
4006 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
4007 order by available_members_count, sc.schedule_on, sc.display_order
4008 limit 1
4009
4010 (155.2ms) truncate table buffer_rule_scores_1511818252
4011
4012 (142.1ms) truncate table buffer_rule_calc_filters_1511818252
4013
4014 (154.8ms) insert into buffer_rule_calc_filters_1511818252 (
4015 linkage_instance_id,
4016 staff_id
4017 ) values (15201931, 20739)
4018
4019 (509.4ms) truncate table buffer_rule_scores_with_corrections_1511818252
4020
4021 (816.5ms) insert into buffer_rule_scores_with_corrections_1511818252 (
4022 linkage_instance_id, schedule_id, staff_id, rule_name, value
4023 )
4024 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
4025 ifnull(rule_scores.value, max_values.max_value + 1) as value
4026 from buffer_rule_scores_1511818252 as rule_scores
4027 inner join (
4028 select rule_name, max(value) as max_value
4029 from buffer_rule_scores_1511818252
4030 group by rule_name
4031 order by null
4032 ) as max_values
4033 on rule_scores.rule_name = max_values.rule_name
4034 order by null
4035
4036 (154.6ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
4037 from (
4038 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
4039 sum(
4040 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
4041 rules.weight *
4042 total_values.slots_count
4043 ) as slot_scores
4044 from buffer_rule_scores_with_corrections_1511818252 as rule_scores
4045 inner join (
4046 select rule_name, sum(abs(value)) as total_value,
4047 count(distinct schedule_id) as slots_count
4048 from buffer_rule_scores_with_corrections_1511818252
4049 group by rule_name
4050 order by null
4051 ) as total_values
4052 on rule_scores.rule_name = total_values.rule_name
4053 inner join rules
4054 on rule_scores.rule_name = rules.type
4055 where rules.rule_set_id = 1538
4056 and rules.applied = 1
4057 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
4058 order by null
4059 ) as staff_scores
4060 group by linkage_instance_id, staff_id
4061 order by round(avg(slot_scores), 5) desc, rand()
4062 limit 1
4063
4064WARN : (02-15 13:34:23) No score found. Using fake score...
4065 PublishedBlock Load (298.0ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND ('2019-08-08' BETWEEN blocks.start_date AND blocks.end_date) AND `published_blocks`.`calendar_schedule_id` = 1396 ORDER BY `published_blocks`.`id` ASC LIMIT 1
4066 (148.1ms) BEGIN
4067 (153.4ms) SELECT COALESCE(pb.updated_at, pbct.updated_at)
4068 FROM call_types AS ct
4069 INNER JOIN calendar_schedules AS cs
4070 ON ct.calendar_schedule_id = cs.id
4071 INNER JOIN blocks AS b
4072 ON b.client_id = ct.client_id
4073 AND b.block_schedule_id = cs.block_schedule_id
4074 AND '2019-08-08' BETWEEN b.start_date AND b.end_date
4075 INNER JOIN published_blocks AS pb
4076 ON pb.client_id = ct.client_id
4077 AND pb.block_id = b.id
4078 AND pb.calendar_schedule_id = cs.id
4079 LEFT JOIN published_block_call_types AS pbct
4080 ON pbct.client_id = ct.client_id
4081 AND pbct.assignment_id = ct.id
4082 AND pbct.assignment_type = 'CallType'
4083 AND pbct.block_id = b.id
4084 AND (
4085 (1 << (DAYOFWEEK('2019-08-08') - 1) & pbct.days_of_week_mask) > 0
4086 )
4087 WHERE ct.id = 12734
4088 AND (
4089 (
4090 pb.assignment_ids IS NULL
4091 AND (1 << (DAYOFWEEK('2019-08-08') - 1) & pb.days_of_week_mask) > 0
4092 )
4093 OR pbct.id IS NOT NULL
4094 )
4095
4096 CallType Load (2045.6ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12734 LIMIT 1
4097 Client Load (441.9ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
4098 Staff Load (141.0ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20739 LIMIT 1
4099 Schedule Exists (142.8ms) SELECT 1 AS one FROM `schedules` WHERE `schedules`.`call_type_id` = 12734 AND `schedules`.`client_id` = 325 AND `schedules`.`schedule_on` = '2019-08-08' LIMIT 1
4100 SQL (144.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 (325, 12734, 20739, '2019-08-08', '2019-02-15 13:34:26', '2019-02-15 13:34:26', 0, 5, 'auto_populate_step', 20695)
4101 VisibleAssignment Load (149.2ms) SELECT `visible_assignments`.* FROM `visible_assignments` WHERE `visible_assignments`.`client_id` = 325 AND `visible_assignments`.`date` = '2019-08-08' AND `visible_assignments`.`assignment_type` = 'CallType' AND `visible_assignments`.`assignment_id` = 12734 ORDER BY `visible_assignments`.`id` ASC LIMIT 1
4102 (148.3ms) COMMIT
4103FLUSHED: nil:nil
4104 (144.9ms) UPDATE buffer_schedules_1511818252
4105 SET staff_id = 20739,
4106 should_be_populated = 0,
4107 should_be_evaluated = 0
4108 WHERE
4109 call_type_id = 12734
4110 AND schedule_on = '2019-08-08'
4111
4112 (144.2ms) delete from buffer_combined_availabilities_1511818252
4113 where linkage_instance_id = 15201931
4114
4115 (140.0ms) UPDATE buffer_rule_calc_schedules_1511818252
4116 SET staff_id = 20739
4117 WHERE call_type_id = 12734
4118 AND schedule_on = '2019-08-08'
4119
4120 (141.6ms) INSERT INTO buffer_rule_calc_schedules_1511818252
4121 SET staff_id = 20739,
4122 call_type_id = 12734,
4123 schedule_on = '2019-08-08',
4124 day_of_week_mask = 16
4125
4126 (148.7ms) /* NegativeLinkRule */
4127 UPDATE buffer_negative_relations_1511818252
4128 SET staff_id = 20739
4129 WHERE affected_by_schedule_id = (
4130 SELECT id
4131 FROM buffer_schedules_1511818252
4132 WHERE schedule_on = '2019-08-08'
4133 AND call_type_id = 12734
4134 )
4135
4136 (162.9ms) /* SpecializationOverlapRule */
4137 update buffer_staff_specializations_1511818252 as st_sp
4138 inner join max_applicable_call_types as mact
4139 on st_sp.specialization_id = mact.specialization_id
4140 and mact.call_type_id = 12734
4141 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
4142 on st_sp.specialization_id = sp_dc.specialization_id
4143 and sp_dc.staff_id is null
4144 and sp_dc.schedule_on = '2019-08-08'
4145 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
4146 on st_sp.specialization_id = sp_dc_2.specialization_id
4147 and st_sp.staff_id = sp_dc_2.staff_id
4148 and sp_dc_2.schedule_on = '2019-08-08'
4149 set sp_dc.staffs_scheduled = sp_dc.staffs_scheduled + 1
4150 where st_sp.staff_id = 20739
4151 and sp_dc_2.id is null
4152
4153 (152.4ms) insert into buffer_specialization_daily_counts_1511818252 (
4154 specialization_id, staff_id, schedule_on, staffs_scheduled, max_scheduled, should_be_evaluated
4155 )
4156 select sp.id as specialization_id,
4157 20739 as staff_id,
4158 '2019-08-08' as schedule_on,
4159 1 as staffs_scheduled,
4160 sp.max_scheduled,
4161 sp_dc.should_be_evaluated
4162 from buffer_staff_specializations_1511818252 as st_sp
4163 inner join specializations as sp
4164 on st_sp.specialization_id = sp.id
4165 inner join max_applicable_call_types as mact
4166 on st_sp.specialization_id = mact.specialization_id
4167 and mact.call_type_id = 12734
4168 inner join buffer_specialization_daily_counts_1511818252 as sp_dc
4169 on st_sp.specialization_id = sp_dc.specialization_id
4170 and sp_dc.staff_id is null
4171 and sp_dc.schedule_on = '2019-08-08'
4172 left join buffer_specialization_daily_counts_1511818252 as sp_dc_2
4173 on st_sp.specialization_id = sp_dc_2.specialization_id
4174 and st_sp.staff_id = sp_dc_2.staff_id
4175 and sp_dc_2.schedule_on = '2019-08-08'
4176 where st_sp.staff_id = 20739
4177 and sp_dc_2.id is null
4178
4179 (155.5ms) /* MaxAssignmentsRule */
4180 delete from buffer_linkage_call_type_assignments_1511818252
4181 where linkage_instance_id = 15201931;
4182
4183 (154.9ms) /* MaxAssignmentsRule */
4184 update
4185 buffer_schedules_1511818252 as sc
4186 inner join call_types as ct
4187 on sc.call_type_id = ct.id
4188 and ct.active = 1
4189 inner join buffer_call_type_assignments_1511818252 as cta
4190 on sc.staff_id = cta.staff_id
4191 and sc.call_type_id = cta.call_type_id
4192 and sc.is_weekend = cta.is_weekend
4193 and sc.period_offset = cta.period_offset
4194 and sc.calendar_schedule_id = cta.calendar_schedule_id
4195 set cta.assignments_count = cta.assignments_count + 1
4196 where
4197 sc.schedule_on = '2019-08-08'
4198 and sc.call_type_id = 12734
4199 and sc.staff_id = 20739
4200
4201 (147.5ms) /* UnassignedDaysRule */
4202 delete from buffer_linkage_assigned_days_1511818252
4203 where linkage_instance_id = 15201931
4204
4205 (147.3ms) /* UnassignedDaysRule */
4206 UPDATE buffer_staff_assigned_days_1511818252 as sad
4207 inner join call_types as ct
4208 on ct.id = 12734
4209 and ct.unassigned_day = 0
4210 SET sad.is_assigned = 1
4211 WHERE sad.staff_id = 20739
4212 AND sad.schedule_on = '2019-08-08'
4213
4214 (455.3ms) delete
4215 from buffer_linkage_assignment_limits_1511818252
4216 where
4217 staff_id = 20739
4218 and linkage_instance_id = 15201931
4219
4220 (610.4ms) insert into buffer_staff_assignment_limits_1511818252 (
4221 staff_id,
4222 assignment_limit_id,
4223 period_id,
4224 consecutive_days_mask,
4225 current_value
4226 )
4227 select
4228 sc.staff_id,
4229 al.id as assignment_limit_id,
4230 alp.id as period_id,
4231 case
4232 when (alp.limit_subtype_mask = 8 /* consecutive */) then
4233 (1 << datediff(sc.schedule_on, alp.start_date))
4234 else 0
4235 end as consecutive_days_mask,
4236 case
4237 when (al.limit_type = 0 /* assignments */) then 1.0
4238 when (al.limit_type = 1 /* points */) then ci.points
4239 when (al.limit_type = 2 /* hours */) then ci.hours
4240 else 0
4241 end as current_value
4242 from
4243 buffer_schedules_1511818252 as sc
4244 inner join buffer_assignment_limit_call_types_1511818252 as alct
4245 on sc.call_type_id = alct.call_type_id
4246 inner join buffer_assignment_limits_1511818252 as al
4247 on alct.assignment_limit_id = al.id
4248 and (sc.day_of_week_mask & al.considered_days_of_week_mask) > 0
4249 inner join buffer_assignment_limit_staffs_1511818252 as als
4250 on al.id = als.assignment_limit_id
4251 and sc.staff_id = als.staff_id
4252 inner join assign_staff_types as ast
4253 on sc.staff_id = ast.staff_id
4254 and sc.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
4255 inner join buffer_assignment_limit_staff_types_1511818252 as alst
4256 on al.id = alst.assignment_limit_id
4257 and ast.staff_type_id = alst.staff_type_id
4258 inner join buffer_assignment_limit_periods_1511818252 as alp
4259 on al.id = alp.assignment_limit_id
4260 and sc.schedule_on between alp.start_date and alp.end_date
4261 inner join buffer_counter_increments_1511818252 as ci
4262 on sc.id = ci.schedule_id
4263 where
4264 sc.schedule_on = '2019-08-08'
4265 and sc.call_type_id = 12734
4266 and sc.staff_id = 20739
4267 on duplicate key update
4268 consecutive_days_mask = consecutive_days_mask | values(consecutive_days_mask),
4269 current_value = current_value + values(current_value)
4270
4271INFO : (02-15 13:34:30) scheduled for: plan unit: [id:15201931] [2019-08-08 - SSGE], Ron Dobrovinsky. DEPTH(6)
4272 (145.3ms) select
4273 coalesce(max(
4274 case
4275 when (ct.max_weekday_assignments > 0) then 1
4276 else 0
4277 end
4278 ), 0) as weekday_flag,
4279 coalesce(max(
4280 case
4281 when (ct.max_weekend_assignments > 0) then 1
4282 else 0
4283 end
4284 ), 0) as weekend_flag
4285 from
4286 buffer_auto_populate_slots_1511818252 as aps
4287 inner join call_types as ct
4288 on aps.call_type_id = ct.id
4289
4290 (152.5ms) /* NegativeLinkRule */
4291 delete ca
4292 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
4293 inner join buffer_negative_relations_1511818252 as nr use index (ix_buffer_negative_relations_composite_1)
4294 on ca.schedule_id = nr.schedule_id
4295 and ca.staff_id = nr.staff_id
4296
4297 (147.1ms) /* SpecializationOverlapRule */
4298 delete ca
4299 from buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
4300 inner join buffer_schedules_1511818252 as sc
4301 on ca.schedule_id = sc.id
4302 inner join buffer_staff_specializations_1511818252 as ssp
4303 on ca.staff_id = ssp.staff_id
4304 inner join max_applicable_call_types as mact
4305 on ssp.specialization_id = mact.specialization_id
4306 and sc.call_type_id = mact.call_type_id
4307 inner join buffer_specialization_daily_counts_1511818252 as sdc use index (ix_buffer_specialization_daily_counts_composite_1)
4308 on ssp.specialization_id = sdc.specialization_id
4309 and sc.schedule_on = sdc.schedule_on
4310 and sdc.staff_id is null
4311 left join buffer_specialization_daily_counts_1511818252 as sdc_2 use index (ix_buffer_specialization_daily_counts_composite_1)
4312 on ssp.specialization_id = sdc_2.specialization_id
4313 and sc.schedule_on = sdc_2.schedule_on
4314 and ca.staff_id = sdc_2.staff_id
4315 where sdc.should_be_evaluated = 1
4316 and (sdc.staffs_scheduled + if(sdc_2.id is null, 1, 0)) > sdc.max_scheduled
4317
4318 (441.2ms) /* BrokenLinkageRule */
4319 truncate table buffer_combined_availability_linkages_1511818252
4320
4321 (520.8ms) insert into buffer_combined_availability_linkages_1511818252 (
4322 staff_id,
4323 linkage_instance_id,
4324 slots_count,
4325 is_primary,
4326 has_violations
4327 )
4328 select
4329 staff_id,
4330 linkage_instance_id,
4331 count(1) as slots_count,
4332 min(is_primary) as is_primary,
4333 max(has_violations) as has_violations
4334 from buffer_combined_availabilities_1511818252 use index (ix_buffer_combined_availabilities_composite_2)
4335 group by
4336 staff_id,
4337 linkage_instance_id
4338 order by null
4339
4340 (154.2ms) delete ca
4341 from
4342 buffer_combined_availabilities_1511818252 as ca use index (ix_buffer_combined_availabilities_composite_1)
4343 inner join buffer_schedules_1511818252 as sc
4344 on ca.schedule_id = sc.id
4345 inner join buffer_linkages_1511818252 as l
4346 on sc.linkage_instance_id = l.linkage_instance_id
4347 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
4348 on ca.staff_id = ls_1.staff_id
4349 and sc.linkage_instance_id = ls_1.linkage_instance_id
4350 left join buffer_schedule_linkages_1511818252 as ls_2 use index (ix_buffer_schedule_linkages_composite_1)
4351 on ca.staff_id = ls_2.staff_id
4352 and sc.linkage_instance_id = ls_2.linkage_instance_id
4353 where (ls_1.slots_count + coalesce(ls_2.slots_count, 0)) < l.slots_count_total /* not covering the whole chain */;
4354
4355 (195.0ms) select straight_join
4356 sc.schedule_on,
4357 sc.call_type_id,
4358 count(distinct ca.staff_id) as available_members_count,
4359 group_concat(
4360 distinct concat(if(ls_1.is_primary = 1, 'p', 's'), ca.staff_id)
4361 order by ls_1.is_primary desc, ca.staff_id
4362 separator ', '
4363 ) as available_members_ids,
4364 sc.linkage_instance_id,
4365 l.slots_count_total as linkage_slots_count
4366 from buffer_combined_availabilities_1511818252 as ca
4367 inner join buffer_schedules_1511818252 as sc
4368 on ca.schedule_id = sc.id
4369 inner join buffer_linkages_1511818252 as l
4370 on sc.linkage_instance_id = l.linkage_instance_id
4371 inner join buffer_combined_availability_linkages_1511818252 as ls_1 use index (ix_buffer_combined_availability_linkages_composite_1)
4372 on ca.staff_id = ls_1.staff_id
4373 and sc.linkage_instance_id = ls_1.linkage_instance_id
4374 and ls_1.has_violations = 0
4375 where sc.should_be_evaluated = 1
4376 and sc.should_be_populated = 1
4377 group by sc.schedule_on, sc.call_type_id, sc.linkage_instance_id, l.slots_count_total
4378 order by available_members_count, sc.schedule_on, sc.display_order
4379 limit 1
4380
4381 (150.3ms) truncate table buffer_rule_scores_1511818252
4382
4383 (145.7ms) truncate table buffer_rule_calc_filters_1511818252
4384
4385 (536.1ms) insert into buffer_rule_calc_filters_1511818252 (
4386 linkage_instance_id,
4387 staff_id
4388 ) values (18201931, 20739)
4389
4390 (149.1ms) truncate table buffer_rule_scores_with_corrections_1511818252
4391
4392 (140.7ms) insert into buffer_rule_scores_with_corrections_1511818252 (
4393 linkage_instance_id, schedule_id, staff_id, rule_name, value
4394 )
4395 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id, rule_scores.rule_name,
4396 ifnull(rule_scores.value, max_values.max_value + 1) as value
4397 from buffer_rule_scores_1511818252 as rule_scores
4398 inner join (
4399 select rule_name, max(value) as max_value
4400 from buffer_rule_scores_1511818252
4401 group by rule_name
4402 order by null
4403 ) as max_values
4404 on rule_scores.rule_name = max_values.rule_name
4405 order by null
4406
4407 (141.1ms) select linkage_instance_id, staff_id, avg(slot_scores) as scores
4408 from (
4409 select rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id,
4410 sum(
4411 if((total_values.total_value > 0), (rule_scores.value / total_values.total_value), 0) *
4412 rules.weight *
4413 total_values.slots_count
4414 ) as slot_scores
4415 from buffer_rule_scores_with_corrections_1511818252 as rule_scores
4416 inner join (
4417 select rule_name, sum(abs(value)) as total_value,
4418 count(distinct schedule_id) as slots_count
4419 from buffer_rule_scores_with_corrections_1511818252
4420 group by rule_name
4421 order by null
4422 ) as total_values
4423 on rule_scores.rule_name = total_values.rule_name
4424 inner join rules
4425 on rule_scores.rule_name = rules.type
4426 where rules.rule_set_id = 1538
4427 and rules.applied = 1
4428 group by rule_scores.linkage_instance_id, rule_scores.schedule_id, rule_scores.staff_id
4429 order by null
4430 ) as staff_scores
4431 group by linkage_instance_id, staff_id
4432 order by round(avg(slot_scores), 5) desc, rand()
4433 limit 1
4434
4435WARN : (02-15 13:34:33) No score found. Using fake score...
4436**Airbrake: closed