· 7 years ago · Sep 14, 2018, 11:54 AM
1(145.8ms) SET NAMES utf8 COLLATE utf8_unicode_ci, @@SESSION.sql_mode = 'NO_ENGINE_SUBSTITUTION', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
2 AutopopulateStepStatus Load (145.6ms) SELECT `autopopulate_step_statuses`.* FROM `autopopulate_step_statuses` WHERE `autopopulate_step_statuses`.`id` = 11684 LIMIT 1
3 Client Load (297.0ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
4 CalendarSchedule Load (148.5ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`id` = 6 LIMIT 1
5 Block Load (145.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`id` = 24525 LIMIT 1
6 AutopopulateStep Load (147.9ms) SELECT `autopopulate_steps`.* FROM `autopopulate_steps` WHERE `autopopulate_steps`.`calendar_schedule_id` = 6 AND `autopopulate_steps`.`step` = 5 ORDER BY autopopulate_steps.step ASC, autopopulate_steps.display_order ASC
7 PrepopulateRule Exists (151.3ms) SELECT 1 AS one FROM `prepopulate_rules` WHERE `prepopulate_rules`.`autopopulate_step_id` = 1708 LIMIT 1
8 BlockSchedule Load (145.4ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
9ClientTools::SchedulesPublisher.set_options({:timeframe=>1})
10 CallType Load (150.5ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 6 AND `call_types`.`calendar_schedule_id` = 6 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
11 TargetGroupCall Load (147.7ms) SELECT `target_group_calls`.* FROM `target_group_calls` WHERE `target_group_calls`.`call_type_id` IN (46, 47, 48, 49, 51, 2716, 397, 1343, 1344)
12 TargetGroup Load (145.7ms) SELECT `target_groups`.* FROM `target_groups` WHERE `target_groups`.`id` = 6
13 (184.7ms) select all_cts.call_type_id,
14 cts_masks.assignment_type,
15 cts_masks.assignment_id,
16 cts_masks.own_n_sc_dow_mask
17 from (
18 select
19 if(tgc.target_group_id is null, 'CallType', 'TargetGroup') AS assignment_type,
20 ifnull(tgc.target_group_id, ct.id) AS assignment_id,
21 ct.id as call_type_id
22 from call_types as ct
23 left join target_group_calls as tgc
24 on tgc.call_type_id = ct.id
25 where ct.calendar_schedule_id = 6
26 and ct.active = 1
27 ) as all_cts
28 left join (
29 select
30 if(tgc.target_group_id is null, 'CallType', 'TargetGroup') AS assignment_type,
31 ifnull(tgc.target_group_id, ct.call_type_id) AS assignment_id,
32 BIT_OR(ct.own_n_sc_dow_mask) AS own_n_sc_dow_mask
33 from (
34 SELECT
35 ct1.id AS call_type_id,
36 (BIT_OR(1 << (sc.dayofweek_on - 1)) | ct1.days_of_week_mask) AS own_n_sc_dow_mask
37 FROM call_types AS ct1
38 LEFT JOIN schedules AS sc USE INDEX (ix_schedules_call_date)
39 ON sc.call_type_id = ct1.id
40 AND sc.schedule_on BETWEEN '2018-10-01' AND '2018-10-31'
41 WHERE ct1.calendar_schedule_id = 6
42 AND ct1.active = 1
43 GROUP BY ct1.id
44 ) as ct
45 left join target_group_calls as tgc
46 on tgc.call_type_id = ct.call_type_id
47 group by assignment_type, assignment_id
48 ) as cts_masks
49 on cts_masks.assignment_type = all_cts.assignment_type
50 and cts_masks.assignment_id = all_cts.assignment_id
51
52 CallType Load (154.2ms) /* UNPUBLISHED CALL TYPES */
53 SELECT DISTINCT ct.*,
54 s_ct.own_n_sc_dow_mask AS own_n_sc_dow_mask
55 FROM call_types AS ct
56 INNER JOIN (
57 SELECT
58 46 AS call_type_id,
59 46 AS assignment_id,
60 'CallType' AS assignment_type,
61 127 AS own_n_sc_dow_mask
62 UNION SELECT
63 47 AS call_type_id,
64 6 AS assignment_id,
65 'TargetGroup' AS assignment_type,
66 127 AS own_n_sc_dow_mask
67 UNION SELECT
68 48 AS call_type_id,
69 6 AS assignment_id,
70 'TargetGroup' AS assignment_type,
71 127 AS own_n_sc_dow_mask
72 UNION SELECT
73 49 AS call_type_id,
74 49 AS assignment_id,
75 'CallType' AS assignment_type,
76 62 AS own_n_sc_dow_mask
77 UNION SELECT
78 51 AS call_type_id,
79 51 AS assignment_id,
80 'CallType' AS assignment_type,
81 62 AS own_n_sc_dow_mask
82 UNION SELECT
83 397 AS call_type_id,
84 397 AS assignment_id,
85 'CallType' AS assignment_type,
86 62 AS own_n_sc_dow_mask
87 UNION SELECT
88 1343 AS call_type_id,
89 1343 AS assignment_id,
90 'CallType' AS assignment_type,
91 62 AS own_n_sc_dow_mask
92 UNION SELECT
93 1344 AS call_type_id,
94 1344 AS assignment_id,
95 'CallType' AS assignment_type,
96 62 AS own_n_sc_dow_mask
97 UNION SELECT
98 2716 AS call_type_id,
99 2716 AS assignment_id,
100 'CallType' AS assignment_type,
101 62 AS own_n_sc_dow_mask
102
103 ) s_ct ON s_ct.call_type_id = ct.id
104 LEFT JOIN published_blocks AS pb
105 ON pb.client_id = ct.client_id
106 AND pb.calendar_schedule_id = ct.calendar_schedule_id
107 AND pb.block_id = 24525
108 LEFT JOIN published_block_call_types AS pbct
109 ON pbct.client_id = ct.client_id
110 AND pbct.published_block_id = pb.id
111 AND pbct.assignment_type = s_ct.assignment_type
112 AND pbct.assignment_id = s_ct.assignment_id
113 WHERE ct.client_id = 6
114 AND ct.calendar_schedule_id = 6
115 AND ct.active = 1
116 AND s_ct.own_n_sc_dow_mask > 0
117 AND (
118 pb.id IS NULL
119 OR (
120 (pb.assignment_ids IS NOT NULL OR pb.days_of_week_mask != 127)
121 AND (
122 pbct.id IS NULL
123 OR (s_ct.own_n_sc_dow_mask & pbct.days_of_week_mask) != s_ct.own_n_sc_dow_mask
124 )
125 )
126 )
127 ORDER BY display_order ASC, abbreviation ASC
128
129 CallType Load (146.5ms) SELECT `call_types`.* FROM `call_types` INNER JOIN `autopopulate_step_call_types` ON `call_types`.`id` = `autopopulate_step_call_types`.`call_type_id` WHERE `autopopulate_step_call_types`.`autopopulate_step_id` = 1708
130 CalendarSchedule Load (148.4ms) SELECT `calendar_schedules`.* FROM `calendar_schedules` WHERE `calendar_schedules`.`client_id` = 6 AND `calendar_schedules`.`id` = 6 ORDER BY `calendar_schedules`.`position` ASC LIMIT 1
131 Block Load (145.5ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND `blocks`.`id` = 24525 ORDER BY start_date asc, end_date asc LIMIT 1
132 BlockSchedule Load (145.7ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 6 LIMIT 1
133 PopulationTimeframe Load (145.7ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 6 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`id` ASC LIMIT 1
134 RuleSet Load (147.3ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1456 LIMIT 1
135 Block Load (146.5ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date >= '2018-10-01' AND blocks.end_date <= '2018-10-31') ORDER BY start_date asc, end_date asc
136INFO : (09-14 10:29:02) phase: Scheduler::Base
137INFO : (09-14 10:29:02) from: 2018-10-01 to: 2018-10-31
138 Rule Load (146.2ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1456 ORDER BY applied desc, weight desc
139INFO : (09-14 10:29:02) rule configurations: CURRENT DAILY ASSIGNMENT TARGETS
140 - 'Current Day of Week Targets' applied {"opt1"=>{"applied"=>"0"}, "opt2"=>{"applied"=>"0"}, "opt3"=>{"applied"=>"0"}, "opt4"=>{"applied"=>"0"}, "opt5"=>{"applied"=>"0"}, "opt6"=>{"applied"=>"0"}, "opt7"=>{"applied"=>"0"}, "round_targets"=>"true"} weight:5
141 - 'Cumulative Assignment Targets' not applied
142 - 'Cumulative Combined Assignment Targets' not applied
143 - 'Cumulative Daily Assignment Targets' not applied
144 - 'Cumulative Day of Week Targets' not applied
145 - 'Cumulative Hour Targets' not applied
146 - 'Cumulative Point Targets' not applied
147 - 'Cumulative Weekend / Weekday Assignment Targets' not applied
148 - 'Cumulative Weekend / Weekday Combined Assignment Targets' not applied
149 - 'Cumulative Weekend / Weekday Hour Targets' not applied
150 - 'Cumulative Weekend / Weekday Point Targets' not applied
151 - 'Current Assignment Targets' not applied
152 - 'Current Combined Assignment Targets' not applied
153 - 'Current Daily Assignment Targets' not applied
154 - 'Current Hour Targets' not applied
155 - 'Current Point Targets' not applied
156 - 'Current Weekend / Weekday Assignment Targets' not applied
157 - 'Current Weekend / Weekday Combined Assignment Targets' not applied
158 - 'Current Weekend / Weekday Hour Targets' not applied
159 - 'Current Weekend / Weekday Point Targets' not applied
160 - 'Pending Call Requests' not applied
161 - 'Pending No-call Requests' not applied
162 - 'Assignments Per Day' not applied
163 - 'Days Between Assignments' not applied
164 - 'Days Between Same Assignment' not applied
165 - 'Unassigned Days' not applied
166 - 'Weeks Between Same Assignment On Same Day Of Week' not applied
167 - 'Weeks Between Same Day Of Week' not applied
168INFO : (09-14 10:29:02) started: Scheduler::Cache::Collector.collect_all
169INFO : (09-14 10:29:02) collect_staffs
170 Staff Load (148.0ms) 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` = 6 AND `staffs`.`take_call` = 1 AND `staffs`.`active` = 1 ORDER BY `staffs`.`display_order` ASC, last_name, first_name
171 Client Load (292.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6
172 StaffSpecialization Load (145.8ms) SELECT `staff_specializations`.* FROM `staff_specializations` WHERE `staff_specializations`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 18127, 78, 8510)
173 Specialization Load (146.3ms) SELECT `specializations`.* FROM `specializations` WHERE `specializations`.`id` = 7 ORDER BY `specializations`.`priority` ASC
174 StaffCallType Load (290.0ms) SELECT `staff_call_types`.* FROM `staff_call_types` WHERE `staff_call_types`.`staff_id` IN (66, 77, 68, 74, 72, 3351, 79, 80, 6913, 81, 65, 18125, 70, 6960, 71, 73, 20694, 75, 18127, 78, 8510)
175INFO : (09-14 10:29:05) initial eligibles: [66,77,68,74,72,3351,79,80,6913,81,65,18125,70,6960,71,73,20694,75,18127,78,8510]
176INFO : (09-14 10:29:05) collect_call_types
177 CallType Load (147.1ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`client_id` = 6 AND `call_types`.`active` = 1 ORDER BY `call_types`.`display_order` ASC
178INFO : (09-14 10:29:05) initial call types: [46,47,48,49,51,2716,397,1343,1344]
179INFO : (09-14 10:29:05) collect_chain_links
180 ChainLink Load (145.7ms) SELECT `chain_links`.* FROM `chain_links` WHERE `chain_links`.`client_id` = 6
181 CallType Load (146.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (46, 48, 47)
182 CallType Load (151.2ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` IN (48, 46, 47)
183INFO : (09-14 10:29:06) collect_linkages
184INFO : (09-14 10:29:06) collect_schedules
185 Schedule Load (145.5ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 6 AND (`schedules`.`schedule_on` BETWEEN '2018-10-01' AND '2018-10-31')
186INFO : (09-14 10:29:06) collect_slots
187INFO : (09-14 10:29:06) collect_approved_call_requests
188 Request Load (153.1ms) SELECT `requests`.* FROM `requests` INNER JOIN `staffs` ON `staffs`.`id` = `requests`.`staff_id` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 1 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-10-01' AND '2018-10-31') ORDER BY requests.schedule_on ASC, staffs.request_priority ASC, requests.created_at ASC
189INFO : (09-14 10:29:07) collect_approved_no_call_requests
190 Request Load (146.3ms) SELECT `requests`.* FROM `requests` WHERE `requests`.`client_id` = 6 AND `requests`.`request_type` = 0 AND `requests`.`status` = 1 AND (`requests`.`schedule_on` BETWEEN '2018-10-01' AND '2018-10-31') ORDER BY requests.schedule_on asc, requests.created_at asc
191INFO : (09-14 10:29:07) finished: Scheduler::Cache::Collector.collect_all
192INFO : (09-14 10:29:07) == begin to process requests
193 CallType Load (146.3ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`calendar_schedule_id` = 6 AND `call_types`.`active` = 1
194 (152.3ms) SELECT MAX(blocks.end_date) FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`calendar_schedule_id` = 6 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127))
195 Request Load (146.8ms) SELECT distinct requests.* FROM `requests` INNER JOIN `request_call_types` ON `request_call_types`.`request_id` = `requests`.`id` WHERE `requests`.`client_id` = 6 AND `requests`.`status` = 1 AND `requests`.`request_type` = 1 AND `request_call_types`.`call_type_id` IN (46, 47, 48, 49, 51, 397, 1343, 1344, 2716) AND (requests.schedule_on > '2017-09-30')
196 SQL (164.3ms) UPDATE `schedules` SET `schedules`.`populate_method` = 'auto_approved_request', `schedules`.`creator_id` = 166 WHERE `schedules`.`client_id` = 6 AND 1=0
197INFO : (09-14 10:29:08) == end of requests processing
198INFO : (09-14 10:29:08) == begin prepopulate process
199 Block Load (147.7ms) SELECT `blocks`.* FROM `blocks` INNER JOIN `block_schedules` ON `blocks`.`block_schedule_id` = `block_schedules`.`id` WHERE `block_schedules`.`id` = 6 AND (blocks.start_date <= '2018-10-30' AND blocks.end_date >= '2018-10-30') ORDER BY start_date asc, end_date asc LIMIT 1
200 Client Load (291.6ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 6 LIMIT 1
201 (145.2ms) SELECT `autopopulate_exclusions`.`schedule_on`, `autopopulate_exclusions`.`call_type_id` FROM `autopopulate_exclusions` WHERE `autopopulate_exclusions`.`client_id` = 6 AND `autopopulate_exclusions`.`schedule_on` IN ('2018-10-02', '2018-10-05', '2018-10-09', '2018-10-12', '2018-10-16', '2018-10-19', '2018-10-23', '2018-10-26', '2018-10-30')
202 (145.8ms) select
203 b.start_date,
204 b.end_date,
205 (pb.assignment_ids IS NULL) as fully_published,
206 concat(pbct.assignment_type, '#', pbct.assignment_id) as assignment_uid,
207 pbct.days_of_week_mask
208 from blocks as b
209 inner join published_blocks as pb
210 on pb.block_id = b.id
211 inner join published_block_call_types as pbct
212 on pbct.published_block_id = pb.id
213 where b.client_id = 6
214 and pb.calendar_schedule_id = 6
215 and b.end_date BETWEEN '2018-10-02' AND '2018-10-30'
216
217INFO : (09-14 10:29:09) <-- clear: 0 - Scheduler::AutoPopulate::Queries::CreateTables
218INFO : (09-14 10:29:09) <-- clear: 1 - Scheduler::AutoPopulate::Queries::Query001_PR_01
219INFO : (09-14 10:29:09) <-- clear: 2 - Scheduler::AutoPopulate::Queries::Query001_PR_02
220INFO : (09-14 10:29:09) <-- clear: 3 - Scheduler::AutoPopulate::Queries::Query001_PR_03
221INFO : (09-14 10:29:09) <-- clear: 4 - Scheduler::AutoPopulate::Queries::Query001_PR_04
222INFO : (09-14 10:29:09) <-- clear: 5 - Scheduler::AutoPopulate::Queries::Query001_PR_05
223INFO : (09-14 10:29:09) <-- clear: 6 - Scheduler::AutoPopulate::Queries::Query005
224INFO : (09-14 10:29:09) <-- clear: 7 - Scheduler::AutoPopulate::Queries::Query01
225INFO : (09-14 10:29:09) <-- clear: 8 - Scheduler::AutoPopulate::Queries::Query011_PR
226INFO : (09-14 10:29:09) <-- clear: 9 - Scheduler::AutoPopulate::Queries::Query012
227INFO : (09-14 10:29:09) <-- clear: 10 - Scheduler::AutoPopulate::Queries::QueryTargets
228INFO : (09-14 10:29:09) <-- clear: 11 - Scheduler::AutoPopulate::Queries::Query013
229INFO : (09-14 10:29:09) <-- clear: 12 - Scheduler::AutoPopulate::Queries::Query014
230INFO : (09-14 10:29:09) <-- clear: 13 - Scheduler::AutoPopulate::Queries::Query015
231INFO : (09-14 10:29:09) <-- clear: 14 - Scheduler::AutoPopulate::Queries::Query02
232INFO : (09-14 10:29:09) <-- clear: 15 - Scheduler::AutoPopulate::Queries::Query03
233INFO : (09-14 10:29:09) <-- clear: 16 - Scheduler::AutoPopulate::Queries::Query04
234INFO : (09-14 10:29:09) <-- clear: 17 - Scheduler::AutoPopulate::Queries::Query045
235INFO : (09-14 10:29:09) <-- clear: 18 - Scheduler::AutoPopulate::Queries::QueryA
236INFO : (09-14 10:29:09) <-- clear: 19 - Scheduler::AutoPopulate::Queries::QueryB
237INFO : (09-14 10:29:09) <-- clear: 20 - Scheduler::AutoPopulate::Queries::Query05
238INFO : (09-14 10:29:09) <-- clear: 21 - Scheduler::AutoPopulate::Queries::Query06
239INFO : (09-14 10:29:09) <-- clear: 22 - Scheduler::AutoPopulate::Queries::Query07
240INFO : (09-14 10:29:09) <-- clear: 23 - Scheduler::AutoPopulate::Queries::Query0702
241INFO : (09-14 10:29:09) <-- clear: 24 - Scheduler::AutoPopulate::Queries::Query0705
242INFO : (09-14 10:29:09) <-- clear: 25 - Scheduler::AutoPopulate::Queries::Query0706
243INFO : (09-14 10:29:09) <-- clear: 26 - Scheduler::AutoPopulate::Queries::Query0707
244INFO : (09-14 10:29:09) <-- clear: 27 - Scheduler::AutoPopulate::Queries::Query0708
245INFO : (09-14 10:29:09) <-- clear: 28 - Scheduler::AutoPopulate::Queries::Query0709
246INFO : (09-14 10:29:09) <-- clear: 29 - Scheduler::AutoPopulate::Queries::Query08
247INFO : (09-14 10:29:09) <-- clear: 30 - Scheduler::AutoPopulate::Queries::Query085
248INFO : (09-14 10:29:09) <-- clear: 31 - Scheduler::AutoPopulate::Queries::Query09_PR
249INFO : (09-14 10:29:09) <-- clear: 32 - Scheduler::AutoPopulate::Queries::Query092
250INFO : (09-14 10:29:09) <-- clear: 33 - Scheduler::AutoPopulate::Queries::Query093
251INFO : (09-14 10:29:09) <-- clear: 34 - Scheduler::AutoPopulate::Queries::Query095
252INFO : (09-14 10:29:09) <-- clear: 35 - Scheduler::AutoPopulate::Queries::Query097
253INFO : (09-14 10:29:09) <-- clear: 36 - Scheduler::AutoPopulate::Queries::Query098
254INFO : (09-14 10:29:09) <-- clear: 37 - Scheduler::AutoPopulate::Queries::Query09_09_AL
255INFO : (09-14 10:29:09) <-- clear: 38 - Scheduler::AutoPopulate::Queries::AssignmentLimitApplicability
256INFO : (09-14 10:29:09) <-- clear: 39 - Scheduler::AutoPopulate::Queries::Query09_09_AL_01
257INFO : (09-14 10:29:09) <-- clear: 40 - Scheduler::AutoPopulate::Queries::Query09_09_AL_02
258INFO : (09-14 10:29:09) <-- clear: 41 - Scheduler::AutoPopulate::Queries::Query09_09_AL_03
259INFO : (09-14 10:29:09) <-- clear: 42 - Scheduler::AutoPopulate::Queries::Query09_09_AL_04
260INFO : (09-14 10:29:09) <-- clear: 43 - Scheduler::AutoPopulate::Queries::Query09_09_AL_05
261INFO : (09-14 10:29:09) <-- clear: 44 - Scheduler::AutoPopulate::Queries::Query09_09_AL_06
262INFO : (09-14 10:29:09) <-- clear: 45 - Scheduler::AutoPopulate::Queries::Query09_12_Points
263INFO : (09-14 10:29:09) <-- clear: 46 - Scheduler::AutoPopulate::Queries::Query09_12_Hours
264INFO : (09-14 10:29:09) <-- clear: 47 - Scheduler::AutoPopulate::Queries::Query10
265INFO : (09-14 10:29:09) <-- clear: 48 - Scheduler::AutoPopulate::Queries::QueryScore02
266INFO : (09-14 10:29:09) <-- clear: 49 - Scheduler::AutoPopulate::Queries::QueryScore03
267INFO : (09-14 10:29:09) <-- clear: 50 - Scheduler::AutoPopulate::Queries::QueryScore04
268INFO : (09-14 10:29:09) <-- clear: 51 - Scheduler::AutoPopulate::Queries::Query11
269INFO : (09-14 10:29:09) <-- clear: 52 - Scheduler::AutoPopulate::Queries::DropTables
270 (146.7ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_1937390740
271 (146.4ms) DROP TABLE IF EXISTS buffer_annual_point_targets_1937390740
272 (146.4ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_1937390740
273 (146.2ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1937390740
274 (147.1ms) DROP TABLE IF EXISTS buffer_call_days_targets_1937390740
275 (146.7ms) DROP TABLE IF EXISTS buffer_call_type_targets_1937390740
276 (149.0ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_1937390740
277 (147.0ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_1937390740
278 (148.7ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_1937390740
279 (147.4ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_1937390740
280 (147.0ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_1937390740
281 (148.6ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_1937390740
282 (152.3ms) DROP TABLE IF EXISTS buffer_current_cd_targets_1937390740
283 (147.8ms) DROP TABLE IF EXISTS buffer_current_ct_targets_1937390740
284 (151.1ms) DROP TABLE IF EXISTS buffer_current_dow_targets_1937390740
285 (148.0ms) DROP TABLE IF EXISTS buffer_current_hour_targets_1937390740
286 (159.0ms) DROP TABLE IF EXISTS buffer_current_oa_targets_1937390740
287 (146.2ms) DROP TABLE IF EXISTS buffer_current_point_targets_1937390740
288 (148.6ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_1937390740
289 (146.4ms) DROP TABLE IF EXISTS buffer_hour_targets_1937390740
290 (148.9ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_1937390740
291 (149.6ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_1937390740
292 (151.5ms) DROP TABLE IF EXISTS buffer_linkage_targets_1937390740
293 (146.2ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_1937390740
294 (148.5ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_1937390740
295 (146.2ms) DROP TABLE IF EXISTS buffer_location_privileges_1937390740
296 (147.1ms) DROP TABLE IF EXISTS buffer_periods_1937390740
297 (148.0ms) DROP TABLE IF EXISTS buffer_point_targets_1937390740
298 (147.1ms) DROP TABLE IF EXISTS buffer_schedules_1937390740
299 (148.9ms) DROP TABLE IF EXISTS buffer_target_call_types_1937390740
300 (149.2ms) DROP TABLE IF EXISTS buffer_target_daily_points_1937390740
301 (146.5ms) DROP TABLE IF EXISTS buffer_target_dates_list_1937390740
302 (146.5ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_1937390740
303 (147.7ms) DROP TABLE IF EXISTS buffer_targets_1937390740
304 (148.4ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_1937390740
305 (147.3ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_1937390740
306 (155.9ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_1937390740
307 (147.5ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_1937390740
308 (156.5ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_1937390740
309 (148.2ms) DROP TABLE IF EXISTS buffer_prepopulate_steps_1937390740
310 (153.9ms) DROP TABLE IF EXISTS buffer_prepopulate_dates_1937390740
311 (146.5ms) DROP TABLE IF EXISTS buffer_prepopulate_periods_1937390740
312 (152.0ms) DROP TABLE IF EXISTS buffer_prepopulate_assignments_1937390740
313 (147.0ms) DROP TABLE IF EXISTS buffer_prepopulate_staff_schedules_1937390740
314 (152.7ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_1937390740
315 (146.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_1937390740
316 (154.0ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_1937390740
317 (146.9ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_1937390740
318 (156.4ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_1937390740
319 (147.0ms) DROP TABLE IF EXISTS buffer_call_type_assignments_1937390740
320 (148.1ms) DROP TABLE IF EXISTS buffer_combined_availabilities_1937390740
321 (146.9ms) DROP TABLE IF EXISTS buffer_combined_availability_linkages_1937390740
322 (155.2ms) DROP TABLE IF EXISTS buffer_counter_increments_1937390740
323 (146.9ms) DROP TABLE IF EXISTS buffer_linkage_assigned_days_1937390740
324 (149.3ms) DROP TABLE IF EXISTS buffer_linkage_call_type_assignments_1937390740
325 (149.1ms) DROP TABLE IF EXISTS buffer_linkage_dow_targets_1937390740
326 (147.3ms) DROP TABLE IF EXISTS buffer_linkage_patterns_1937390740
327 (147.9ms) DROP TABLE IF EXISTS buffer_linkages_1937390740
328 (156.4ms) DROP TABLE IF EXISTS buffer_location_privileges_1937390740
329 (146.8ms) DROP TABLE IF EXISTS buffer_min_distances_1937390740
330 (146.6ms) DROP TABLE IF EXISTS buffer_negative_relations_1937390740
331 (146.7ms) DROP TABLE IF EXISTS buffer_rule_calc_filters_1937390740
332 (159.7ms) DROP TABLE IF EXISTS buffer_rule_calc_schedules_1937390740
333 (146.6ms) DROP TABLE IF EXISTS buffer_rule_scores_1937390740
334 (151.7ms) DROP TABLE IF EXISTS buffer_rule_scores_with_corrections_1937390740
335 (146.7ms) DROP TABLE IF EXISTS buffer_schedule_linkages_1937390740
336 (147.0ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_1937390740
337 (151.7ms) DROP TABLE IF EXISTS buffer_specialization_daily_counts_1937390740
338 (148.5ms) DROP TABLE IF EXISTS buffer_staff_assigned_days_1937390740
339 (148.1ms) DROP TABLE IF EXISTS buffer_staff_daily_counts_1937390740
340 (155.8ms) DROP TABLE IF EXISTS buffer_staff_specializations_1937390740
341 (146.5ms) DROP TABLE IF EXISTS buffer_static_availabilities_1937390740
342 (149.9ms) DROP TABLE IF EXISTS buffer_target_assignments_1937390740
343 (146.6ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_1937390740
344 (152.1ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_1937390740
345 (146.5ms) DROP TABLE IF EXISTS buffer_assignment_limits_1937390740
346 (154.4ms) delete from buffer_target_dates
347 where session_id = 1937390740
348
349INFO : (09-14 10:29:21) --> fill: 0 - Scheduler::AutoPopulate::Queries::CreateTables
350 (149.9ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_1937390740 LIKE buffer_assignment_limit_call_types
351 (149.1ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_1937390740 LIKE buffer_assignment_limit_periods
352 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_1937390740 LIKE buffer_assignment_limit_staff_types
353 (161.8ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_1937390740 LIKE buffer_assignment_limit_staffs
354 (150.6ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1937390740 LIKE buffer_auto_populate_slots
355 (152.7ms) CREATE TABLE IF NOT EXISTS buffer_call_type_assignments_1937390740 LIKE buffer_call_type_assignments
356 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_combined_availabilities_1937390740 LIKE buffer_combined_availabilities
357 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_combined_availability_linkages_1937390740 LIKE buffer_combined_availability_linkages
358 (153.7ms) CREATE TABLE IF NOT EXISTS buffer_counter_increments_1937390740 LIKE buffer_counter_increments
359 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assigned_days_1937390740 LIKE buffer_linkage_assigned_days
360 (149.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_assignments_1937390740 LIKE buffer_linkage_call_type_assignments
361 (152.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_dow_targets_1937390740 LIKE buffer_linkage_dow_targets
362 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_1937390740 LIKE buffer_linkage_patterns
363 (149.6ms) CREATE TABLE IF NOT EXISTS buffer_linkages_1937390740 LIKE buffer_linkages
364 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1937390740 LIKE buffer_location_privileges
365 (151.2ms) CREATE TABLE IF NOT EXISTS buffer_min_distances_1937390740 LIKE buffer_min_distances
366 (149.4ms) CREATE TABLE IF NOT EXISTS buffer_negative_relations_1937390740 LIKE buffer_negative_relations
367 (150.5ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_filters_1937390740 LIKE buffer_rule_calc_filters
368 (150.5ms) CREATE TABLE IF NOT EXISTS buffer_rule_calc_schedules_1937390740 LIKE buffer_rule_calc_schedules
369 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_1937390740 LIKE buffer_rule_scores
370 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_rule_scores_with_corrections_1937390740 LIKE buffer_rule_scores_with_corrections
371 (153.0ms) CREATE TABLE IF NOT EXISTS buffer_schedule_linkages_1937390740 LIKE buffer_schedule_linkages
372 (150.7ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_1937390740 LIKE buffer_schedules_linkage_patterns
373 (151.2ms) CREATE TABLE IF NOT EXISTS buffer_specialization_daily_counts_1937390740 LIKE buffer_specialization_daily_counts
374 (149.4ms) CREATE TABLE IF NOT EXISTS buffer_staff_assigned_days_1937390740 LIKE buffer_staff_assigned_days
375 (151.2ms) CREATE TABLE IF NOT EXISTS buffer_staff_daily_counts_1937390740 LIKE buffer_staff_daily_counts
376 (149.6ms) CREATE TABLE IF NOT EXISTS buffer_staff_specializations_1937390740 LIKE buffer_staff_specializations
377 (152.8ms) CREATE TABLE IF NOT EXISTS buffer_static_availabilities_1937390740 LIKE buffer_static_availabilities
378 (164.1ms) CREATE TABLE IF NOT EXISTS buffer_target_assignments_1937390740 LIKE buffer_target_assignments
379 (152.6ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_1937390740 LIKE buffer_linkage_assignment_limits
380 (152.1ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_1937390740 LIKE buffer_staff_assignment_limits
381 (150.5ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_1937390740 LIKE buffer_assignment_limits
382 (152.1ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_steps_1937390740 LIKE buffer_prepopulate_steps
383 (150.4ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_dates_1937390740 LIKE buffer_prepopulate_dates
384 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_periods_1937390740 LIKE buffer_prepopulate_periods
385 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_assignments_1937390740 LIKE buffer_prepopulate_assignments
386 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_prepopulate_staff_schedules_1937390740 LIKE buffer_prepopulate_staff_schedules
387 (155.4ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_1937390740 LIKE buffer_annual_hour_targets
388 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_1937390740 LIKE buffer_annual_point_targets
389 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_1937390740 LIKE buffer_annual_targets_staff_totals
390 (146.4ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_1937390740 LIKE buffer_auto_populate_slots
391 (155.9ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_1937390740 LIKE buffer_call_days_targets
392 (151.4ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_1937390740 LIKE buffer_call_type_targets
393 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_1937390740 LIKE buffer_cumulative_cd_targets
394 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_1937390740 LIKE buffer_cumulative_ct_targets
395 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_1937390740 LIKE buffer_cumulative_dow_targets
396 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_1937390740 LIKE buffer_cumulative_hour_targets
397 (149.4ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_1937390740 LIKE buffer_cumulative_oa_targets
398 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_1937390740 LIKE buffer_cumulative_point_targets
399 (150.9ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_1937390740 LIKE buffer_current_cd_targets
400 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_1937390740 LIKE buffer_current_ct_targets
401 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_1937390740 LIKE buffer_current_dow_targets
402 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_1937390740 LIKE buffer_current_hour_targets
403 (150.2ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_1937390740 LIKE buffer_current_oa_targets
404 (150.4ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_1937390740 LIKE buffer_current_point_targets
405 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_1937390740 LIKE buffer_day_of_week_targets
406 (151.5ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_1937390740 LIKE buffer_hour_targets
407 (150.4ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_1937390740 LIKE buffer_linkage_call_days_targets
408 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_1937390740 LIKE buffer_linkage_call_type_targets
409 (149.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_1937390740 LIKE buffer_linkage_targets
410 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_1937390740 LIKE buffer_linkage_hour_targets
411 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_1937390740 LIKE buffer_linkage_point_targets
412 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_1937390740 LIKE buffer_location_privileges
413 (152.9ms) CREATE TABLE IF NOT EXISTS buffer_periods_1937390740 LIKE buffer_periods
414 (149.8ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_1937390740 LIKE buffer_point_targets
415 (150.1ms) CREATE TABLE IF NOT EXISTS buffer_schedules_1937390740 LIKE buffer_schedules
416 (149.9ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_1937390740 LIKE buffer_target_call_types
417 (149.9ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_1937390740 LIKE buffer_target_daily_points
418 (150.0ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_1937390740 LIKE buffer_target_dates_list
419 (149.7ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_1937390740 LIKE buffer_target_primary_availabilities
420 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_targets_1937390740 LIKE buffer_targets
421 (150.3ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_1937390740 LIKE buffer_tmp_targets_availabilities
422 (150.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_1937390740 LIKE buffer_tmp_targets_availabilities_exclusions
423 (149.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_1937390740 LIKE buffer_tmp_targets_client_totals
424 (150.9ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_1937390740 LIKE buffer_tmp_targets_scheduled
425 (149.2ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_1937390740 LIKE buffer_tmp_targets_staff_totals
426INFO : (09-14 10:29:32) --> fill: 1 - Scheduler::AutoPopulate::Queries::Query001_PR_01
427 (148.4ms) insert into buffer_prepopulate_steps_1937390740 (
428 rule_id,
429 rule_type,
430 rule_subtype,
431 staff_id,
432 x,
433 days_of_week_mask,
434 start_date,
435 start_date_first_dow,
436 required_assignments
437 )
438 select
439 pr.id as rule_id,
440 pr.rule_type,
441 coalesce(rrt.recurring_type, mrt.timeframe) as rule_subtype,
442 pr.staff_id,
443 rrt.frequency as x,
444 case
445 when (rrt.days_of_week_mask = 0) then 127
446 else coalesce(rrt.days_of_week_mask, mrt.days_of_week_mask)
447 end as days_of_week_mask,
448 rrt.start_date,
449 case
450 when (cl.start_of_week = 0) then
451 date_add(rrt.start_date, interval -(dayofweek(rrt.start_date) - 1) day)
452 else
453 date_add(rrt.start_date, interval -weekday(rrt.start_date) day)
454 end as start_date_first_dow,
455 case
456 when (rrt.id is not null) then 1
457 else mrt.number_to_assign
458 end as required_assignments
459 from
460 clients as cl
461 inner join prepopulate_rules as pr
462 on pr.autopopulate_step_id = 1708
463 left join recurring_rule_types as rrt
464 on pr.id = rrt.prepopulate_rule_id
465 left join minimum_rule_types as mrt
466 on pr.id = mrt.prepopulate_rule_id
467 where cl.id = 6
468 order by
469 pr.display_order,
470 coalesce(rrt.display_order, mrt.display_order)
471
472INFO : (09-14 10:29:32) --> fill: 2 - Scheduler::AutoPopulate::Queries::Query001_PR_02
473 (149.3ms) insert into buffer_prepopulate_dates_1937390740 (
474 day,
475 schedule_on,
476 day_of_week_mask,
477 week,
478 first_dow,
479 last_dow,
480 month,
481 first_dom,
482 last_dom,
483 block,
484 first_dob,
485 last_dob
486 )
487 select
488 dl.day,
489 dl.schedule_on,
490 (1 << (dayofweek(dl.schedule_on) - 1)) as day_of_week_mask,
491 yearweek(dl.schedule_on, dl.week_mode) as week,
492 case
493 when (dl.start_of_week = 0) then
494 date_add(dl.schedule_on, interval -(dayofweek(dl.schedule_on) - 1) day)
495 else
496 date_add(dl.schedule_on, interval -weekday(dl.schedule_on) day)
497 end as first_dow,
498 case
499 when (dl.start_of_week = 0) then
500 date_add(dl.schedule_on, interval (7 - dayofweek(dl.schedule_on)) day)
501 else
502 date_add(dl.schedule_on, interval (6 - weekday(dl.schedule_on)) day)
503 end as last_dow,
504 (year(dl.schedule_on) * 100 + month(dl.schedule_on)) as month,
505 date_add(dl.schedule_on, interval (1 - dayofmonth(dl.schedule_on)) day) as first_dom,
506 last_day(dl.schedule_on) as last_dom,
507 dl.block_id as block,
508 dl.start_date as first_dob,
509 dl.end_date as last_dob
510 from (
511 select
512 (ns.item_number + 1) as day,
513 date_add(b.start_date, interval ns.item_number day) as schedule_on,
514 b.id as block_id,
515 b.start_date,
516 b.end_date,
517 cl.start_of_week,
518 case
519 when (cl.start_of_week = 0) then 2
520 else 7
521 end as week_mode
522 from
523 clients as cl
524 inner join blocks as b
525 on b.id in (24525)
526 inner join numeric_sequence as ns
527 on ns.item_number <= datediff(b.end_date, b.start_date)
528 where cl.id = 6
529 order by null
530 ) as dl
531 order by null
532
533INFO : (09-14 10:29:32) --> fill: 3 - Scheduler::AutoPopulate::Queries::Query001_PR_03
534 (146.1ms) insert into buffer_prepopulate_periods_1937390740 (
535 step_id,
536 start_date,
537 end_date
538 )
539 select
540 ps.id as step_id,
541 min(
542 case
543 when (ps.rule_type = 'recurring') then d.schedule_on
544 when (ps.rule_subtype = 'per_week') then d.first_dow
545 when (ps.rule_subtype = 'per_month') then d.first_dom
546 when (ps.rule_subtype = 'per_block') then d.first_dob
547 end
548 ) as _start_date,
549 max(
550 case
551 when (ps.rule_type = 'recurring') then d.schedule_on
552 when (ps.rule_subtype = 'per_week') then d.last_dow
553 when (ps.rule_subtype = 'per_month') then d.last_dom
554 when (ps.rule_subtype = 'per_block') then d.last_dob
555 end
556 ) as _end_date
557 from
558 buffer_prepopulate_steps_1937390740 as ps
559 inner join buffer_prepopulate_dates_1937390740 as d
560 on ps.rule_type = 'minimum'
561 or (
562 ps.start_date <= d.schedule_on
563 and (ps.days_of_week_mask & d.day_of_week_mask) > 0
564 )
565 where
566 ps.rule_type = 'minimum'
567 or (
568 ps.rule_subtype = 'every_x_days'
569 and (datediff(d.schedule_on, ps.start_date) mod ps.x) = 0
570 )
571 or (
572 ps.rule_subtype = 'every_x_weeks'
573 and (datediff(d.first_dow, ps.start_date_first_dow) mod ps.x) = 0
574 )
575 or (
576 ps.rule_subtype = 'x_day_of_week_month'
577 and ((datediff(d.schedule_on, d.first_dom) div 7 + 1) / ps.x) = 0
578 )
579 or (
580 ps.rule_subtype = 'x_day_of_week_block'
581 and ((datediff(d.schedule_on, d.first_dob) div 7 + 1) / ps.x) = 0
582 )
583 group by
584 ps.id,
585 case
586 when (ps.rule_type = 'recurring') then d.day
587 when (ps.rule_subtype = 'per_week') then d.week
588 when (ps.rule_subtype = 'per_month') then d.month
589 when (ps.rule_subtype = 'per_block') then d.block
590 end
591 order by
592 ps.id,
593 _start_date
594
595INFO : (09-14 10:29:33) --> fill: 4 - Scheduler::AutoPopulate::Queries::Query001_PR_04
596 (153.9ms) insert into buffer_prepopulate_assignments_1937390740 (
597 period_id,
598 required_assignments,
599 scheduled_assignments
600 )
601 select
602 pp.id as period_id,
603 ps.required_assignments,
604 count(sc.id) as scheduled_assignments
605 from (
606 select
607 sc.id,
608 sc.schedule_on,
609 (1 << (sc.dayofweek_on - 1)) as day_of_week_mask,
610 sc.call_type_id,
611 sc.staff_id
612 from
613 schedules as sc
614 inner join (
615 select
616 min(start_date) as start_date,
617 max(end_date) as end_date
618 from buffer_prepopulate_periods_1937390740
619 ) as pp
620 on sc.schedule_on between pp.start_date and pp.end_date
621 where sc.client_id = 6
622 order by null
623 ) as sc
624 inner join prepopulate_rule_call_types as prct
625 on sc.call_type_id = prct.call_type_id
626 inner join buffer_prepopulate_steps_1937390740 as ps
627 on prct.prepopulate_rule_id = ps.rule_id
628 and sc.staff_id = ps.staff_id
629 and (sc.day_of_week_mask & ps.days_of_week_mask) > 0
630 inner join buffer_prepopulate_periods_1937390740 as pp
631 on ps.id = pp.step_id
632 and sc.schedule_on between pp.start_date and pp.end_date
633 group by
634 pp.id,
635 ps.required_assignments
636 order by null
637
638 (147.5ms) insert into buffer_prepopulate_assignments_1937390740 (
639 period_id,
640 required_assignments,
641 scheduled_assignments
642 )
643 select
644 pp.id as period_id,
645 ps.required_assignments,
646 0 as scheduled_assignments
647 from
648 buffer_prepopulate_periods_1937390740 as pp
649 left join buffer_prepopulate_assignments_1937390740 as pa
650 on pp.id = pa.period_id
651 inner join buffer_prepopulate_steps_1937390740 as ps
652 on pp.step_id = ps.id
653 where pa.id is null
654 order by null
655
656INFO : (09-14 10:29:33) --> fill: 5 - Scheduler::AutoPopulate::Queries::Query001_PR_05
657 (145.9ms) insert into buffer_auto_populate_slots_1937390740 (
658 schedule_on,
659 day_of_week_mask,
660 call_type_id,
661 display_order,
662 should_be_populated
663 )
664 select distinct
665 pd.schedule_on,
666 pd.day_of_week_mask,
667 ct.id as call_type_id,
668 ct.display_order,
669 1 as should_be_populated
670 from
671 buffer_prepopulate_steps_1937390740 as ps
672 inner join buffer_prepopulate_periods_1937390740 as pp
673 on ps.id = pp.step_id
674 inner join buffer_prepopulate_dates_1937390740 as pd
675 on pd.schedule_on between pp.start_date and pp.end_date
676 inner join prepopulate_rule_call_types as prct
677 on ps.rule_id = prct.prepopulate_rule_id
678 inner join call_types as ct
679 on prct.call_type_id = ct.id
680 and ct.active = 1
681 and (pd.day_of_week_mask & ct.days_of_week_mask) > 0
682 order by null
683
684INFO : (09-14 10:29:33) --> fill: 6 - Scheduler::AutoPopulate::Queries::Query005
685 (145.7ms) insert into buffer_auto_populate_slots_1937390740 (
686 schedule_on,
687 day_of_week_mask,
688 call_type_id,
689 display_order,
690 should_be_populated
691 )
692 select distinct
693 date_add(
694 auto_populate_slots.schedule_on,
695 interval linkage_relations.distance_in_days day
696 ) as schedule_on,
697 (1 << (
698 dayofweek(
699 date_add(
700 auto_populate_slots.schedule_on,
701 interval linkage_relations.distance_in_days day
702 )
703 ) - 1
704 )
705 ) as day_of_week_mask,
706 linkage_relations.linked_call_type_id as call_type_id,
707 call_types.display_order,
708 0 as should_be_populated
709 from
710 buffer_auto_populate_slots_1937390740 as auto_populate_slots
711 inner join linkage_relations
712 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
713 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
714 left join buffer_auto_populate_slots_1937390740 as auto_populate_slots_2
715 on date_add(
716 auto_populate_slots.schedule_on,
717 interval linkage_relations.distance_in_days day
718 ) = auto_populate_slots_2.schedule_on
719 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
720 inner join call_types
721 on linkage_relations.linked_call_type_id = call_types.id
722 where auto_populate_slots_2.id is null
723 order by null
724
725INFO : (09-14 10:29:33) --> fill: 7 - Scheduler::AutoPopulate::Queries::Query01
726 (147.4ms) insert into linkage_relations (
727 client_id,
728 call_type_id,
729 day_of_week_mask,
730 linked_call_type_id,
731 distance_in_days,
732 linkage_template_id
733 )
734 select distinct
735 6 /* client_id */ as client_id,
736 aps.call_type_id,
737 aps.day_of_week_mask,
738 aps.call_type_id as linked_call_type_id,
739 0 as distance_in_days,
740 coalesce(
741 lr.linkage_template_id,
742 @linkage_template_max := @linkage_template_max + 1
743 ) as linkage_template_id
744 from
745 buffer_auto_populate_slots_1937390740/* client_id */ as aps
746 inner join (
747 select @linkage_template_max := count(distinct linkage_template_id) - 1
748 from linkage_relations
749 where client_id = 6 /* client_id */
750 order by null
751 ) as lt
752 on 1 = 1
753 left join linkage_relations as lr use index (ix_linkage_relations_composite1)
754 on aps.call_type_id = lr.call_type_id
755 and aps.day_of_week_mask = lr.day_of_week_mask
756 left join linkage_relations as lr_2 use index (ix_linkage_relations_composite1)
757 on aps.call_type_id = lr_2.call_type_id
758 and aps.day_of_week_mask = lr_2.day_of_week_mask
759 and aps.call_type_id = lr_2.linked_call_type_id
760 and lr_2.distance_in_days = 0
761 where lr_2.id is null
762 order by null
763
764 (146.3ms) insert into buffer_schedules_1937390740 (
765 call_type_id,
766 staff_id,
767 schedule_on,
768 day_of_week_mask,
769 is_weekend,
770 display_order,
771 week_number,
772 should_be_evaluated,
773 should_be_populated,
774 linkage_instance_id
775 )
776 select
777 call_type_id,
778 staff_id,
779 schedule_on,
780 day_of_week_mask,
781 is_weekend,
782 display_order,
783 week_number,
784 max(should_be_evaluated) as should_be_evaluated,
785 max(should_be_populated) as should_be_populated,
786 min(linkage_instance_id) as linkage_instance_id
787 from (
788 select
789 auto_populate_slots_2.call_type_id,
790 schedules.staff_id,
791 auto_populate_slots_2.schedule_on,
792 auto_populate_slots_2.day_of_week_mask,
793 case
794 when (auto_populate_slots_2.day_of_week_mask & 62 > 0) then 0
795 else 1
796 end as is_weekend,
797 auto_populate_slots_2.display_order,
798 yearweek(
799 auto_populate_slots_2.schedule_on,
800 case
801 when (clients.start_of_week = 0) then 2
802 else 7
803 end
804 ) as week_number,
805 case
806 when (schedules.staff_id is null) then 1
807 else 0
808 end as should_be_evaluated,
809 (
810 case
811 when (
812 auto_populate_slots_2.should_be_populated = 1
813 and schedules.staff_id is null
814 ) then 1
815 else 0
816 end
817 ) as should_be_populated,
818 (
819 linkage_relations.linkage_template_id * 1000000 +
820 yearweek(
821 auto_populate_slots.schedule_on,
822 case
823 when (clients.start_of_week = 0) then 2
824 else 7
825 end
826 )
827 ) as linkage_instance_id
828 from
829 buffer_auto_populate_slots_1937390740 as auto_populate_slots
830 inner join clients
831 on clients.id = 6 /* client_id */
832 inner join linkage_relations
833 on auto_populate_slots.call_type_id = linkage_relations.call_type_id
834 and auto_populate_slots.day_of_week_mask = linkage_relations.day_of_week_mask
835 inner join buffer_auto_populate_slots_1937390740 as auto_populate_slots_2
836 on date_add(
837 auto_populate_slots.schedule_on,
838 interval linkage_relations.distance_in_days day
839 ) = auto_populate_slots_2.schedule_on
840 and linkage_relations.linked_call_type_id = auto_populate_slots_2.call_type_id
841 left join schedules
842 on auto_populate_slots_2.call_type_id = schedules.call_type_id
843 and auto_populate_slots_2.schedule_on = schedules.schedule_on
844 order by null
845 ) as t1
846 group by
847 call_type_id,
848 staff_id,
849 schedule_on,
850 day_of_week_mask,
851 display_order,
852 week_number
853 order by null
854
855INFO : (09-14 10:29:34) --> fill: 8 - Scheduler::AutoPopulate::Queries::Query011_PR
856 (145.8ms) insert into buffer_prepopulate_staff_schedules_1937390740 (
857 step_id,
858 period_id,
859 schedule_id,
860 linkage_instance_id,
861 staff_id
862 )
863 select
864 ps.id as step_id,
865 pp.id as period_id,
866 bs.id as schedule_id,
867 bs.linkage_instance_id,
868 ps.staff_id
869 from
870 buffer_prepopulate_steps_1937390740 as ps
871 inner join buffer_prepopulate_periods_1937390740 as pp
872 on ps.id = pp.step_id
873 inner join buffer_prepopulate_dates_1937390740 as pd
874 on pd.schedule_on between pp.start_date and pp.end_date
875 inner join prepopulate_rule_call_types as prct
876 on ps.rule_id = prct.prepopulate_rule_id
877 inner join buffer_schedules_1937390740 as bs
878 on pd.schedule_on = bs.schedule_on
879 and prct.call_type_id = bs.call_type_id
880 order by pp.id
881
882 (144.7ms) select 1
883 from buffer_prepopulate_staff_schedules_1937390740
884 limit 1
885
886INFO : (09-14 10:29:34) Setting up lists...
887 (145.3ms) select b_schedules.schedule_on, b_schedules.call_type_id, b_schedules.linkage_instance_id, schedules.id as schedule_id
888 from buffer_schedules_1937390740 as b_schedules
889 left join schedules
890 on schedules.client_id = 6
891 and schedules.call_type_id = b_schedules.call_type_id
892 and schedules.schedule_on = b_schedules.schedule_on
893 where b_schedules.linkage_instance_id != -1
894 order by b_schedules.linkage_instance_id, b_schedules.schedule_on
895
896INFO : (09-14 10:29:34) Begin to populate. Pre-set: 0. Unscheduled: 0.
897 (146.7ms) select
898 pp.id as period_id,
899 ca.staff_id,
900 group_concat(
901 distinct ca.linkage_instance_id
902 order by ca.linkage_instance_id
903 separator ','
904 ) as linkage_instance_ids
905 from
906 buffer_prepopulate_periods_1937390740 as pp
907 inner join buffer_prepopulate_assignments_1937390740 as pa
908 on pp.id = pa.period_id
909 and pa.required_assignments > pa.scheduled_assignments
910 inner join buffer_prepopulate_staff_schedules_1937390740 as pss
911 on pp.id = pss.period_id
912 inner join buffer_combined_availabilities_1937390740 as ca
913 on pss.schedule_id = ca.schedule_id
914 and pss.staff_id = ca.staff_id
915 group by
916 pp.id,
917 ca.staff_id
918 order by pp.id
919 limit 1
920
921INFO : (09-14 10:31:53) Apply target credits...
922 Schedule Load (147.3ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 6 AND 1=0
923INFO : (09-14 10:31:53) Success!