API tools faq
paste
Guest User

Untitled

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