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