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