Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
man-populate-mar-2019 session_id: 59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] Started GET "/mat/admin/schedules/new?_=1549887799820&call_type_id=12733&date=2019-03-12&calendar_schedule=1396" for 127.0.0.1 at 2019-02-11 14:23:19 +0200
[13554a52-f902-4cae-bec0-6e019c72ca70] (140.0ms) SET NAMES utf8 COLLATE utf8_unicode_ci, @@SESSION.sql_mode = 'NO_ENGINE_SUBSTITUTION', @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
[13554a52-f902-4cae-bec0-6e019c72ca70] (391.9ms) SELECT `schema_migrations`.`version` FROM `schema_migrations` ORDER BY `schema_migrations`.`version` ASC
[13554a52-f902-4cae-bec0-6e019c72ca70] Full URL: "http://sm.local.ezcall.com:3215/mat/admin/schedules/new?_=1549887799820&call_type_id=12733&date=2019-03-12&calendar_schedule=1396"
[13554a52-f902-4cae-bec0-6e019c72ca70] Remote Addr: 127.0.0.1
[13554a52-f902-4cae-bec0-6e019c72ca70] UserAgent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36
[13554a52-f902-4cae-bec0-6e019c72ca70] Tenant Load (129.7ms) SELECT `tenants`.* FROM `tenants` WHERE `tenants`.`active` = 1 AND `tenants`.`subdomain` IN ('sm.local.ezcall.com', 'sm') ORDER BY `tenants`.`id` ASC LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Client Exists (128.6ms) SELECT 1 AS one FROM `clients` WHERE `clients`.`tenant_id` = 319 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Client Load (130.4ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`tenant_id` = 319 AND `clients`.`subdomain` = 'mat' ORDER BY `clients`.`id` ASC LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Processing by Admin::SchedulesController#new as */*
[13554a52-f902-4cae-bec0-6e019c72ca70] Parameters: {"_"=>"1549887799820", "call_type_id"=>"12733", "date"=>"2019-03-12", "calendar_schedule"=>"1396", "entity"=>"mat"}
[13554a52-f902-4cae-bec0-6e019c72ca70] User Load (130.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 5501 ORDER BY `users`.`id` ASC LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] User: [5501] aaron
[13554a52-f902-4cae-bec0-6e019c72ca70] CACHE Tenant Load (0.0ms) SELECT `tenants`.* FROM `tenants` WHERE `tenants`.`active` = 1 AND `tenants`.`subdomain` IN ('sm.local.ezcall.com', 'sm') ORDER BY `tenants`.`id` ASC LIMIT 1 [["active", 1], ["LIMIT", 1]]
[13554a52-f902-4cae-bec0-6e019c72ca70] Client Load (130.7ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`tenant_id` = 319 AND `clients`.`subdomain` = 'mat' LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] LinkedClient Load (129.9ms) SELECT `linked_clients`.* FROM `linked_clients` WHERE `linked_clients`.`client_id` = 325 AND `linked_clients`.`id` IS NULL ORDER BY linked_clients.position asc LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Staff Load (129.4ms) SELECT `staffs`.* FROM `staffs` WHERE `staffs`.`id` = 20695 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Tenant Load (129.2ms) SELECT `tenants`.* FROM `tenants` WHERE `tenants`.`id` = 319 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.6ms) SELECT `staff_entities`.`entity_id` FROM `staff_entities` INNER JOIN `staffs` ON `staff_entities`.`staff_id` = `staffs`.`id` WHERE `staffs`.`id` = 20695
[13554a52-f902-4cae-bec0-6e019c72ca70] format:*/* true
[13554a52-f902-4cae-bec0-6e019c72ca70] CalendarSchedule Load (130.1ms) 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
[13554a52-f902-4cae-bec0-6e019c72ca70] GroupSchedule Load (129.4ms) SELECT `group_schedules`.* FROM `group_schedules` WHERE `group_schedules`.`client_id` = 325 AND `group_schedules`.`call_type_id` = 12733 AND `group_schedules`.`schedule_on` = '2019-03-12' ORDER BY `group_schedules`.`id` ASC LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Schedule Load (129.9ms) SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`client_id` = 325 AND `schedules`.`call_type_id` = 12733 AND `schedules`.`schedule_on` = '2019-03-12' ORDER BY `schedules`.`id` ASC LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] CallType Load (130.7ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12733 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] RuleSet Load (139.0ms) SELECT `rule_sets`.* FROM `rule_sets` WHERE `rule_sets`.`id` = 1550 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Rule Load (131.4ms) SELECT `rules`.* FROM `rules` WHERE `rules`.`rule_set_id` = 1550 ORDER BY applied desc, weight desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Client Load (131.1ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) 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-03-12' AND blocks.end_date >= '2019-03-12') ORDER BY start_date asc, end_date asc LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] CallType Exists (129.0ms) SELECT 1 AS one FROM `call_types` WHERE (call_types.days_of_week_mask & 4 > 0) AND `call_types`.`id` = 12733 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (137.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] BlockSchedule Load (130.0ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 456 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Client Load (131.0ms) SELECT `clients`.* FROM `clients` WHERE `clients`.`id` = 325 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] PopulationTimeframe Load (129.8ms) SELECT `population_timeframes`.* FROM `population_timeframes` WHERE `population_timeframes`.`client_id` = 325 AND `population_timeframes`.`timeframe_type` = 1 ORDER BY `population_timeframes`.`timeframe_type` ASC LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query005
[13554a52-f902-4cae-bec0-6e019c72ca70] (136.0ms) drop view if exists view_schedules_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (131.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::CreateTables
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_annual_hour_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (138.1ms) DROP TABLE IF EXISTS buffer_annual_point_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.3ms) DROP TABLE IF EXISTS buffer_annual_targets_staff_totals_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (150.2ms) DROP TABLE IF EXISTS buffer_auto_populate_slots_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.0ms) DROP TABLE IF EXISTS buffer_call_days_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_call_type_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.9ms) DROP TABLE IF EXISTS buffer_cumulative_cd_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_cumulative_ct_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.4ms) DROP TABLE IF EXISTS buffer_cumulative_dow_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.2ms) DROP TABLE IF EXISTS buffer_cumulative_hour_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.2ms) DROP TABLE IF EXISTS buffer_cumulative_oa_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.5ms) DROP TABLE IF EXISTS buffer_cumulative_point_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (135.8ms) DROP TABLE IF EXISTS buffer_current_cd_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.1ms) DROP TABLE IF EXISTS buffer_current_ct_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.2ms) DROP TABLE IF EXISTS buffer_current_dow_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.7ms) DROP TABLE IF EXISTS buffer_current_hour_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.1ms) DROP TABLE IF EXISTS buffer_current_oa_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (143.9ms) DROP TABLE IF EXISTS buffer_current_point_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.3ms) DROP TABLE IF EXISTS buffer_day_of_week_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.4ms) DROP TABLE IF EXISTS buffer_hour_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.8ms) DROP TABLE IF EXISTS buffer_linkage_call_days_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (141.2ms) DROP TABLE IF EXISTS buffer_linkage_call_type_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.9ms) DROP TABLE IF EXISTS buffer_linkage_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_linkage_hour_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.4ms) DROP TABLE IF EXISTS buffer_linkage_point_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.4ms) DROP TABLE IF EXISTS buffer_location_privileges_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.7ms) DROP TABLE IF EXISTS buffer_periods_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_point_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.5ms) DROP TABLE IF EXISTS buffer_schedules_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.2ms) DROP TABLE IF EXISTS buffer_target_call_types_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (137.5ms) DROP TABLE IF EXISTS buffer_target_daily_points_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.6ms) DROP TABLE IF EXISTS buffer_target_dates_list_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.4ms) DROP TABLE IF EXISTS buffer_target_primary_availabilities_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.5ms) DROP TABLE IF EXISTS buffer_targets_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.3ms) DROP TABLE IF EXISTS buffer_tmp_targets_availabilities_exclusions_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.6ms) DROP TABLE IF EXISTS buffer_tmp_targets_client_totals_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (140.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_scheduled_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_tmp_targets_staff_totals_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (138.8ms) DROP TABLE IF EXISTS buffer_manual_populate_slots_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_manual_staff_slots_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.2ms) DROP TABLE IF EXISTS buffer_manual_assigned_days_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_manual_availabilities_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.5ms) DROP TABLE IF EXISTS buffer_manual_violations_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.4ms) DROP TABLE IF EXISTS buffer_periods_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.0ms) DROP TABLE IF EXISTS buffer_manual_slots_to_ignore_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.3ms) DROP TABLE IF EXISTS buffer_linkage_patterns_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.8ms) DROP TABLE IF EXISTS buffer_schedules_linkage_patterns_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (152.1ms) DROP TABLE IF EXISTS buffer_staff_assignment_dates_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.6ms) DROP TABLE IF EXISTS buffer_location_privileges_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_assignment_limit_call_types_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (135.6ms) DROP TABLE IF EXISTS buffer_assignment_limit_staffs_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.4ms) DROP TABLE IF EXISTS buffer_assignment_limit_staff_types_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.5ms) DROP TABLE IF EXISTS buffer_assignment_limit_periods_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.7ms) DROP TABLE IF EXISTS buffer_linkage_assignment_limits_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (134.4ms) DROP TABLE IF EXISTS buffer_staff_assignment_limits_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.4ms) DROP TABLE IF EXISTS buffer_assignment_limits_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (151.6ms) delete from buffer_target_dates
where session_id = '59135044'
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (148.3ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::CreateTables
[13554a52-f902-4cae-bec0-6e019c72ca70] (135.4ms) CREATE TABLE IF NOT EXISTS buffer_manual_populate_slots_59135044 LIKE buffer_manual_populate_slots
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.4ms) CREATE TABLE IF NOT EXISTS buffer_manual_staff_slots_59135044 LIKE buffer_manual_staff_slots
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.1ms) CREATE TABLE IF NOT EXISTS buffer_manual_assigned_days_59135044 LIKE buffer_manual_assigned_days
[13554a52-f902-4cae-bec0-6e019c72ca70] (134.7ms) CREATE TABLE IF NOT EXISTS buffer_manual_availabilities_59135044 LIKE buffer_manual_availabilities
[13554a52-f902-4cae-bec0-6e019c72ca70] (134.4ms) CREATE TABLE IF NOT EXISTS buffer_manual_violations_59135044 LIKE buffer_manual_violations
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.4ms) CREATE TABLE IF NOT EXISTS buffer_periods_59135044 LIKE buffer_periods
[13554a52-f902-4cae-bec0-6e019c72ca70] (136.9ms) CREATE TABLE IF NOT EXISTS buffer_manual_slots_to_ignore_59135044 LIKE buffer_manual_slots_to_ignore
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_patterns_59135044 LIKE buffer_linkage_patterns
[13554a52-f902-4cae-bec0-6e019c72ca70] (140.4ms) CREATE TABLE IF NOT EXISTS buffer_schedules_linkage_patterns_59135044 LIKE buffer_schedules_linkage_patterns
[13554a52-f902-4cae-bec0-6e019c72ca70] (136.4ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_dates_59135044 LIKE buffer_staff_assignment_dates
[13554a52-f902-4cae-bec0-6e019c72ca70] (165.5ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_59135044 LIKE buffer_location_privileges
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.4ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_call_types_59135044 LIKE buffer_assignment_limit_call_types
[13554a52-f902-4cae-bec0-6e019c72ca70] (148.1ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staffs_59135044 LIKE buffer_assignment_limit_staffs
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.7ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_staff_types_59135044 LIKE buffer_assignment_limit_staff_types
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.2ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limit_periods_59135044 LIKE buffer_assignment_limit_periods
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_assignment_limits_59135044 LIKE buffer_linkage_assignment_limits
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.7ms) CREATE TABLE IF NOT EXISTS buffer_staff_assignment_limits_59135044 LIKE buffer_staff_assignment_limits
[13554a52-f902-4cae-bec0-6e019c72ca70] (134.3ms) CREATE TABLE IF NOT EXISTS buffer_assignment_limits_59135044 LIKE buffer_assignment_limits
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.3ms) CREATE TABLE IF NOT EXISTS buffer_annual_hour_targets_59135044 LIKE buffer_annual_hour_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (142.9ms) CREATE TABLE IF NOT EXISTS buffer_annual_point_targets_59135044 LIKE buffer_annual_point_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.7ms) CREATE TABLE IF NOT EXISTS buffer_annual_targets_staff_totals_59135044 LIKE buffer_annual_targets_staff_totals
[13554a52-f902-4cae-bec0-6e019c72ca70] (146.1ms) CREATE TABLE IF NOT EXISTS buffer_auto_populate_slots_59135044 LIKE buffer_auto_populate_slots
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.8ms) CREATE TABLE IF NOT EXISTS buffer_call_days_targets_59135044 LIKE buffer_call_days_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (134.7ms) CREATE TABLE IF NOT EXISTS buffer_call_type_targets_59135044 LIKE buffer_call_type_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.7ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_cd_targets_59135044 LIKE buffer_cumulative_cd_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.5ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_ct_targets_59135044 LIKE buffer_cumulative_ct_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_dow_targets_59135044 LIKE buffer_cumulative_dow_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.3ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_hour_targets_59135044 LIKE buffer_cumulative_hour_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.7ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_oa_targets_59135044 LIKE buffer_cumulative_oa_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.1ms) CREATE TABLE IF NOT EXISTS buffer_cumulative_point_targets_59135044 LIKE buffer_cumulative_point_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (141.1ms) CREATE TABLE IF NOT EXISTS buffer_current_cd_targets_59135044 LIKE buffer_current_cd_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.7ms) CREATE TABLE IF NOT EXISTS buffer_current_ct_targets_59135044 LIKE buffer_current_ct_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (142.7ms) CREATE TABLE IF NOT EXISTS buffer_current_dow_targets_59135044 LIKE buffer_current_dow_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.3ms) CREATE TABLE IF NOT EXISTS buffer_current_hour_targets_59135044 LIKE buffer_current_hour_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.2ms) CREATE TABLE IF NOT EXISTS buffer_current_oa_targets_59135044 LIKE buffer_current_oa_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.5ms) CREATE TABLE IF NOT EXISTS buffer_current_point_targets_59135044 LIKE buffer_current_point_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.3ms) CREATE TABLE IF NOT EXISTS buffer_day_of_week_targets_59135044 LIKE buffer_day_of_week_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.1ms) CREATE TABLE IF NOT EXISTS buffer_hour_targets_59135044 LIKE buffer_hour_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.3ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_days_targets_59135044 LIKE buffer_linkage_call_days_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (138.9ms) CREATE TABLE IF NOT EXISTS buffer_linkage_call_type_targets_59135044 LIKE buffer_linkage_call_type_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (135.8ms) CREATE TABLE IF NOT EXISTS buffer_linkage_targets_59135044 LIKE buffer_linkage_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (138.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_hour_targets_59135044 LIKE buffer_linkage_hour_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (135.0ms) CREATE TABLE IF NOT EXISTS buffer_linkage_point_targets_59135044 LIKE buffer_linkage_point_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (145.0ms) CREATE TABLE IF NOT EXISTS buffer_location_privileges_59135044 LIKE buffer_location_privileges
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.8ms) CREATE TABLE IF NOT EXISTS buffer_periods_59135044 LIKE buffer_periods
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.2ms) CREATE TABLE IF NOT EXISTS buffer_point_targets_59135044 LIKE buffer_point_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.7ms) CREATE TABLE IF NOT EXISTS buffer_schedules_59135044 LIKE buffer_schedules
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.7ms) CREATE TABLE IF NOT EXISTS buffer_target_call_types_59135044 LIKE buffer_target_call_types
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.8ms) CREATE TABLE IF NOT EXISTS buffer_target_daily_points_59135044 LIKE buffer_target_daily_points
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.8ms) CREATE TABLE IF NOT EXISTS buffer_target_dates_list_59135044 LIKE buffer_target_dates_list
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.8ms) CREATE TABLE IF NOT EXISTS buffer_target_primary_availabilities_59135044 LIKE buffer_target_primary_availabilities
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.5ms) CREATE TABLE IF NOT EXISTS buffer_targets_59135044 LIKE buffer_targets
[13554a52-f902-4cae-bec0-6e019c72ca70] (139.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_59135044 LIKE buffer_tmp_targets_availabilities
[13554a52-f902-4cae-bec0-6e019c72ca70] (134.1ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_availabilities_exclusions_59135044 LIKE buffer_tmp_targets_availabilities_exclusions
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.5ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_client_totals_59135044 LIKE buffer_tmp_targets_client_totals
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.7ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_scheduled_59135044 LIKE buffer_tmp_targets_scheduled
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.0ms) CREATE TABLE IF NOT EXISTS buffer_tmp_targets_staff_totals_59135044 LIKE buffer_tmp_targets_staff_totals
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (137.5ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query005
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.1ms) create or replace view view_schedules_59135044
as
select sc.*
from schedules as sc
left join buffer_manual_slots_to_ignore_59135044 as sti
on sc.id = sti.schedule_id
where sc.client_id = 325
and sti.id is null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (132.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::FillManualPopulateSlots
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.6ms) INSERT INTO buffer_manual_populate_slots_59135044 (
thread_id,
schedule_on,
day_of_week_mask,
call_type_id,
should_be_populated,
staff_id
) VALUES(
1,
'2019-03-12',
4,
12733,
1,
-1
)
[13554a52-f902-4cae-bec0-6e019c72ca70] CallType Load (268.4ms) SELECT `call_types`.* FROM `call_types` WHERE `call_types`.`id` = 12733 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (138.5ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query01
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.9ms) insert into buffer_manual_populate_slots_59135044 (
thread_id,
schedule_on,
day_of_week_mask,
call_type_id,
should_be_populated,
staff_id
)
select
t1.thread_id,
t1.schedule_on,
(1 << (dayofweek(t1.schedule_on) - 1)) as day_of_week_mask,
t1.call_type_id,
0 as should_be_populated,
t1.staff_id
from
(
select
mps.thread_id,
date_add(
mps.schedule_on,
interval lr.distance_in_days day
) as schedule_on,
lr.linked_call_type_id as call_type_id,
mps.staff_id
from
buffer_manual_populate_slots_59135044 as mps
inner join linkage_relations as lr
on mps.day_of_week_mask = lr.day_of_week_mask
and mps.call_type_id = lr.call_type_id
-- this shouldn't be a link to itself on the same day
and (
lr.call_type_id != lr.linked_call_type_id
or lr.distance_in_days != 0
)
order by null
) as t1
left join schedules as sc
on t1.call_type_id = sc.call_type_id
and t1.schedule_on = sc.schedule_on
-- we should exclude do-not-populate slots
left join autopopulate_exclusions as ae
on t1.call_type_id = ae.call_type_id
and t1.schedule_on = ae.schedule_on
where
-- only empty slots can behave as do-not-populate (when they are marked appropriately)
-- once we added someone to that slot then it should be considered as a regular one
sc.id is not null
or ae.id is null
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query012
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.1ms) update
buffer_manual_populate_slots_59135044 as mps
inner join call_types as ct
on mps.call_type_id = ct.id
inner join calendar_schedules as cs
on ct.calendar_schedule_id = cs.id
inner join blocks as b
on cs.block_schedule_id = b.block_schedule_id
and mps.schedule_on between b.start_date and b.end_date
set
mps.block_id = b.id,
mps.calendar_schedule_id = cs.id
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.4ms) update
buffer_manual_slots_to_ignore_59135044 as sti
inner join call_types as ct
on sti.call_type_id = ct.id
inner join calendar_schedules as cs
on ct.calendar_schedule_id = cs.id
inner join blocks as b
on cs.block_schedule_id = b.block_schedule_id
and sti.schedule_on between b.start_date and b.end_date
set
sti.day_of_week_mask = (1 << (dayofweek(sti.schedule_on) - 1)),
sti.wday = (dayofweek(sti.schedule_on) - 1),
sti.is_weekend = (dayofweek(sti.schedule_on) in (1, 7)),
sti.block_id = b.id,
sti.calendar_schedule_id = cs.id
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query013
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.3ms) select distinct calendar_schedule_id
from buffer_manual_populate_slots_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (142.3ms) select 1
from linkage_patterns
where calendar_schedule_id in (1396) /* : list of distinct calendar_schedule_ids from buffer_manual_populate_slots */
limit 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query014
[13554a52-f902-4cae-bec0-6e019c72ca70] (140.2ms) update buffer_manual_populate_slots_59135044
set
wday = (dayofweek(schedule_on) - 1),
is_weekend = (day_of_week_mask & 65 > 0);
[13554a52-f902-4cae-bec0-6e019c72ca70] BlockSchedule Load (129.4ms) SELECT `block_schedules`.* FROM `block_schedules` WHERE `block_schedules`.`id` = 456 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.6ms) insert into buffer_periods_59135044 (
calendar_schedule_id, block_schedule_id, period_offset, start_block_display_order, end_block_display_order,
start_date, end_date, should_be_populated, should_be_evaluated
)
select t1.calendar_schedule_id,
t1.block_schedule_id,
t1.period_offset,
t2.start_block_display_order,
t2.end_block_display_order,
t2.start_date,
t2.end_date,
t1.should_be_populated,
t1.should_be_evaluated
from (
select mps.calendar_schedule_id,
b.block_schedule_id,
if (
(month(b.start_date) - pt.begin_period) >= 0,
year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type,
(year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type
) as period_offset,
max(mps.should_be_populated) as should_be_populated,
max(mps.should_be_evaluated) as should_be_evaluated
from (
select calendar_schedule_id, block_id,
max(should_be_populated) as should_be_populated,
1 as should_be_evaluated
from buffer_manual_populate_slots_59135044
group by calendar_schedule_id, block_id
order by null
) as mps
inner join blocks as b
on mps.block_id = b.id
inner join population_timeframes as pt
on pt.client_id = 325
and pt.timeframe_type = 1 /* current targets period */
group by mps.calendar_schedule_id, b.block_schedule_id, period_offset
order by null
) as t1
inner join (
select b.block_schedule_id,
if (
(month(b.start_date) - pt.begin_period) >= 0,
year(b.start_date) * 100 + (month(b.start_date) - pt.begin_period) div pt.timeframe_type,
(year(b.start_date) - 1) * 100 + (month(b.start_date) + 12 - pt.begin_period) div pt.timeframe_type
) as period_offset,
min(b.display_order) as start_block_display_order,
max(b.display_order) as end_block_display_order,
min(b.start_date) as start_date,
max(b.end_date) as end_date
from blocks as b
inner join population_timeframes as pt
on pt.client_id = 325
and pt.timeframe_type = 1 /* current targets period */
where b.client_id = 325
group by b.block_schedule_id, period_offset
order by null
) as t2
on t1.block_schedule_id = t2.block_schedule_id
and t1.period_offset = t2.period_offset
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.7ms) update buffer_manual_populate_slots_59135044 as mps
inner join buffer_periods_59135044 as p
on mps.calendar_schedule_id = p.calendar_schedule_id
and mps.schedule_on between p.start_date and p.end_date
inner join blocks as b
on mps.block_id = b.id
inner join (
select calendar_schedule_id,
max(if(should_be_populated = 1, end_block_display_order, null)) as timeframe_ending
from buffer_periods_59135044
group by calendar_schedule_id
order by null
) as t1
on mps.calendar_schedule_id = t1.calendar_schedule_id
set mps.period_offset = p.period_offset,
mps.affects_cumulative_targets = if(
b.display_order between (t1.timeframe_ending - (3 /* cumulative targets period */ - 1)) and t1.timeframe_ending, 1, 0
)
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.0ms) update buffer_manual_slots_to_ignore_59135044 as sti
inner join buffer_periods_59135044 as p
on sti.calendar_schedule_id = p.calendar_schedule_id
and sti.schedule_on between p.start_date and p.end_date
inner join blocks as b
on sti.block_id = b.id
inner join (
select calendar_schedule_id,
max(if(should_be_populated = 1, end_block_display_order, null)) as timeframe_ending
from buffer_periods_59135044
group by calendar_schedule_id
order by null
) as t1
on sti.calendar_schedule_id = t1.calendar_schedule_id
set sti.period_offset = p.period_offset,
sti.affects_cumulative_targets = if(
b.display_order between (t1.timeframe_ending - (3 /* cumulative targets period */ - 1)) and t1.timeframe_ending, 1, 0
)
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query015
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.2ms) select 1
from assignment_limits
where
client_id = 325
and active = 1
and limit_type = 1 /* points */
limit 1
[13554a52-f902-4cae-bec0-6e019c72ca70] (134.4ms) select 1
from assignment_limits
where
client_id = 325
and active = 1
and limit_type = 2 /* hours */
limit 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query016
[13554a52-f902-4cae-bec0-6e019c72ca70] (146.1ms) truncate table buffer_location_privileges_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.9ms) insert into buffer_location_privileges_59135044 (
staff_id,
location_id,
begin_date,
end_date
)
select
s.id as staff_id,
l.location_id,
'0000-00-00' as begin_date,
'9999-12-31' as end_date
from (
select l.id as location_id
from
buffer_manual_populate_slots_59135044 as mps
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
inner join locations as l
on ct.location_id = l.id
and l.active = 1
group by l.id
order by null
) as l
inner join staff_entities as se
on se.entity_id = 325
inner join staffs as s
on se.staff_id = s.id
and s.active = 1
and s.take_call = 1
left join location_privileges as lp use index (ix_location_privileges_on_staff_id)
on s.id = lp.staff_id
and lp.client_id = 325
where lp.id is null
union
select
lp.staff_id,
lp.location_id,
lp.begin_date,
coalesce(lp.end_date, '9999-12-31') as end_date
from (
select l.id as location_id
from
buffer_manual_populate_slots_59135044 as mps
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
inner join locations as l
on ct.location_id = l.id
and l.active = 1
group by l.id
order by null
) as l
inner join location_privileges as lp
on l.location_id = lp.location_id
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (144.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query02
[13554a52-f902-4cae-bec0-6e019c72ca70] (142.7ms) /* AvailabilityRule */
insert into buffer_manual_violations_59135044 (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select straight_join
mps.id as schedule_id,
s.id as staff_id,
mps.schedule_on as violation_date,
null as violation_details,
'AvailabilityRule' as violation_type
from
buffer_manual_populate_slots_59135044 as mps
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
and mps.day_of_week_mask & ct.days_of_week_mask > 0
inner join staff_entities as se
on se.entity_id = 325
and (
mps.staff_id = -1
or mps.staff_id = se.staff_id
)
inner join staffs as s
on se.staff_id = s.id
and s.active = 1
and s.take_call = 1
left join buffer_location_privileges_59135044 as lp
on s.id = lp.staff_id
and ct.location_id = lp.location_id
and mps.schedule_on between lp.begin_date and ifnull(lp.end_date, '9999-12-31')
left join assign_staff_types as ast use index (ix_assign_staff_types_composite_1)
on s.id = ast.staff_id
and mps.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
and ast.client_id = 325
left join eligible_staff_types as est
on ct.id = est.call_type_id
and ast.staff_type_id = est.staff_type_id
left join staff_call_types as sct
on s.id = sct.staff_id
and ct.id = sct.call_type_id
left join assign_rotation_schedules as ars use index (ix_assign_rotation_schedules_composite_1)
on s.id = ars.staff_id
and mps.schedule_on between ars.begin_date and ars.end_date
and ars.client_id = 325
left join staff_rotations as sr
on s.id = sr.staff_id
and mps.schedule_on between sr.start_date and sr.end_date
and ars.rotation_schedule_id = sr.rotation_schedule_id
left join daily_rotations as dr
on s.id = dr.staff_id
and mps.schedule_on = dr.schedule_on
and ars.rotation_schedule_id = dr.rotation_schedule_id
and sr.id is not null
left join rotations as r
on coalesce(dr.rotation_id, sr.rotation_id) = r.id
and r.active = 1
left join rotation_call_types as rct
on r.id = rct.rotation_id
and ct.id = rct.call_type_id
where
mps.should_be_populated = 1
and (
lp.id is null
or ast.id is null
or (
ct.all_staff_types = 0
and ast.id is not null
and est.id is null
)
or (
case
when (
mps.day_of_week_mask &
ifnull(sct.days_of_week_mask, 0) &
if(
r.id is null,
127,
ifnull(rct.days_of_week_mask, 0)
)
) > 0 then 3 /* primary */
when (
mps.day_of_week_mask &
(
ifnull(sct.days_of_week_mask, 0) |
ifnull(sct.secondary_availability_days_of_week_mask, 0)
) &
(
if(
r.id is null,
127,
ifnull(rct.days_of_week_mask, 0)
) |
if(
r.id is null,
127,
ifnull(rct.secondary_availability_days_of_week_mask, 0)
)
)
) > 0 then 2 /* secondary */
when (
ifnull(sct.backup, 0) |
ifnull(rct.backup, 0)
) > 0 then 1 /* backup */
else 0 /* not available */
end
) = 0
)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (133.2ms) /* RequiredRotationsRule */
insert into buffer_manual_violations_59135044 (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select
mps.id as schedule_id,
s.id as staff_id,
mps.schedule_on as violation_date,
null as violation_details,
'RequiredRotationsRule' as violation_type
from
buffer_manual_populate_slots_59135044 as mps
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
and mps.day_of_week_mask & ct.days_of_week_mask > 0
inner join staff_entities as se
on se.entity_id = 325
and (
mps.staff_id = -1
or mps.staff_id = se.staff_id
)
inner join staffs as s
on se.staff_id = s.id
and s.active = 1
and s.take_call = 1
left join buffer_manual_violations_59135044 as mv
on mps.id = mv.schedule_id
and s.id = mv.staff_id
and mv.violation_type = 'AvailabilityRule'
inner join required_rotations as rr
on ct.id = rr.call_type_id
left join staff_rotations as sr force index (ix_staff_rotations_composite_3)
on s.id = sr.staff_id
and rr.rotation_id = sr.rotation_id
left join assign_rotation_schedules as ars force index (ix_assign_rotation_schedules_composite_1)
on s.id = ars.staff_id
and sr.start_date <= ars.end_date
and sr.end_date >= ars.begin_date
and sr.rotation_schedule_id = ars.rotation_schedule_id
where
mps.should_be_populated = 1
and mv.id is null -- not breaking the basic availability rule
group by
mps.id,
s.id,
mps.schedule_on
having
count(distinct
case
when (
sr.id is not null
and ars.id is not null
and mps.schedule_on >= greatest(sr.start_date, ars.begin_date)
)
then sr.rotation_id
else null
end
) < count(distinct rr.rotation_id)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query02_2
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.9ms) insert into buffer_manual_availabilities_59135044 (
thread_id,
staff_id,
is_linkage
)
select straight_join
mps.thread_id,
s.id as staff_id,
if(
t1.staffs_count is not null, 1, 0
) as is_linkage
from
buffer_manual_populate_slots_59135044 as mps
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
and mps.day_of_week_mask & ct.days_of_week_mask > 0
inner join staff_entities as se
on se.entity_id = 325
and (
mps.staff_id = -1
or mps.staff_id = se.staff_id
)
inner join staffs as s
on se.staff_id = s.id
and s.active = 1
and s.take_call = 1
left join buffer_manual_violations_59135044 as mv
on mps.id = mv.schedule_id
and s.id = mv.staff_id
and mv.violation_type in (
'AvailabilityRule',
'RequiredRotationsRule'
)
/* is_linkage */
left join (
select straight_join
mps_0.thread_id,
count(distinct sch_0.staff_id) as staffs_count,
min(sch_0.staff_id) as staff_id
from
buffer_manual_populate_slots_59135044 as mps_0
left join view_schedules_59135044 as sch_0
on mps_0.schedule_on = sch_0.schedule_on
and mps_0.call_type_id = sch_0.call_type_id
and sch_0.staff_id is not null
where mps_0.should_be_populated = 0
group by mps_0.thread_id
order by null
) as t1
on mps.thread_id = t1.thread_id
and (
t1.staffs_count = 0
or (
t1.staffs_count = 1
and s.id = t1.staff_id
)
)
where
mps.should_be_populated = 1
and mv.id is null
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (133.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query03
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.1ms) insert into buffer_manual_staff_slots_59135044 (
schedule_id,
staff_id,
staff_type_id,
is_linkage
)
select
mps.id as schedule_id,
ma.staff_id,
ast.staff_type_id,
ma.is_linkage
from
buffer_manual_availabilities_59135044 as ma
inner join buffer_manual_populate_slots_59135044 as mps
on ma.thread_id = mps.thread_id
inner join assign_staff_types as ast use index (ix_assign_staff_types_composite_1)
on ma.staff_id = ast.staff_id
and mps.schedule_on between ast.begin_date and coalesce(ast.end_date, '9999-12-31')
and ast.client_id = 325
left join schedules as sc
on mps.schedule_on = sc.schedule_on
and mps.call_type_id = sc.call_type_id
left join buffer_manual_slots_to_ignore_59135044 as sti
on sc.id = sti.schedule_id
where
mps.should_be_populated = 1
or (
ma.is_linkage = 1
and (
sc.id is null
or sti.id is not null
)
)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query04
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.7ms) insert into buffer_manual_violations_59135044 (
schedule_id, staff_id, violation_date, violation_details, violation_type
)
select mps_2.id as schedule_id, ma.staff_id,
sch.schedule_on as violation_date,
concat(sch.call_type_id, ',', sch.staff_id) as violation_details,
'BrokenLinkageRule' as violation_type
from buffer_manual_availabilities_59135044 as ma
inner join buffer_manual_populate_slots_59135044 as mps
on ma.thread_id = mps.thread_id
and mps.should_be_populated = 0
inner join buffer_manual_populate_slots_59135044 as mps_2
on mps.thread_id = mps_2.thread_id
and mps_2.should_be_populated = 1
inner join view_schedules_59135044 as sch
on mps.schedule_on = sch.schedule_on
and mps.call_type_id = sch.call_type_id
and ma.staff_id != sch.staff_id
where ma.is_linkage = 0
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (138.3ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query05
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.0ms) /* BrokenLinkageAvailabilityRule */
insert into buffer_manual_violations_59135044 (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select straight_join
mps_2.id as schedule_id,
mss.staff_id,
mps.schedule_on as violation_date,
mps.call_type_id as violation_details,
'BrokenLinkageAvailabilityRule' as violation_type
from
buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
and mps.should_be_populated = 0
inner join buffer_manual_populate_slots_59135044 as mps_2
on mps.thread_id = mps_2.thread_id
and mps_2.should_be_populated = 1
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
and mps.day_of_week_mask & ct.days_of_week_mask > 0
inner join staffs as s
on mss.staff_id = s.id
and s.active = 1
and s.take_call = 1
left join buffer_location_privileges_59135044 as lp
on s.id = lp.staff_id
and ct.location_id = lp.location_id
and mps.schedule_on between lp.begin_date and ifnull(lp.end_date, '9999-12-31')
left join assign_staff_types as ast use index (ix_assign_staff_types_composite_1)
on s.id = ast.staff_id
and mps.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
and ast.client_id = 325
left join eligible_staff_types as est
on ct.id = est.call_type_id
and ast.staff_type_id = est.staff_type_id
left join staff_call_types as sct
on s.id = sct.staff_id
and ct.id = sct.call_type_id
left join assign_rotation_schedules as ars use index (ix_assign_rotation_schedules_composite_1)
on s.id = ars.staff_id
and mps.schedule_on between ars.begin_date and ars.end_date
and ars.client_id = 325
left join staff_rotations as sr
on s.id = sr.staff_id
and mps.schedule_on between sr.start_date and sr.end_date
and ars.rotation_schedule_id = sr.rotation_schedule_id
left join daily_rotations as dr
on s.id = dr.staff_id
and mps.schedule_on = dr.schedule_on
and ars.rotation_schedule_id = dr.rotation_schedule_id
and sr.id is not null
left join rotations as r
on coalesce(dr.rotation_id, sr.rotation_id) = r.id
and r.active = 1
left join rotation_call_types as rct
on r.id = rct.rotation_id
and ct.id = rct.call_type_id
where
mss.is_linkage = 1
and (
lp.id is null
or ast.id is null
or (
ct.all_staff_types = 0
and ast.id is not null
and est.id is null
)
or (
case
when (
mps.day_of_week_mask &
ifnull(sct.days_of_week_mask, 0) &
if(
r.id is null,
127,
ifnull(rct.days_of_week_mask, 0)
)
) > 0 then 3 /* primary */
when (
mps.day_of_week_mask &
(
ifnull(sct.days_of_week_mask, 0) |
ifnull(sct.secondary_availability_days_of_week_mask, 0)
) &
(
if(
r.id is null,
127,
ifnull(rct.days_of_week_mask, 0)
) |
if(
r.id is null,
127,
ifnull(rct.secondary_availability_days_of_week_mask, 0)
)
)
) > 0 then 2 /* secondary */
when (
ifnull(sct.backup, 0) |
ifnull(rct.backup, 0)
) > 0 then 1 /* backup */
else 0 /* not available */
end
) = 0
)
group by
mps_2.id,
mss.staff_id,
mps.schedule_on,
mps.call_type_id
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.0ms) /* BrokenLinkageRequiredRotationsRule */
insert into buffer_manual_violations_59135044 (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select
mps_2.id as schedule_id,
mss.staff_id,
mps.schedule_on as violation_date,
mps.call_type_id as violation_details,
'BrokenLinkageRequiredRotationsRule' as violation_type
from
buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
and mps.should_be_populated = 0
inner join buffer_manual_populate_slots_59135044 as mps_2
on mps.thread_id = mps_2.thread_id
and mps_2.should_be_populated = 1
left join buffer_manual_violations_59135044 as mv
on mps.id = mv.schedule_id
and mss.staff_id = mv.staff_id
and mv.violation_type = 'BrokenLinkageAvailabilityRule'
inner join required_rotations as rr
on mps.call_type_id = rr.call_type_id
left join staff_rotations as sr force index (ix_staff_rotations_composite_3)
on mss.staff_id = sr.staff_id
and rr.rotation_id = sr.rotation_id
left join assign_rotation_schedules as ars force index (ix_assign_rotation_schedules_composite_1)
on mss.staff_id = ars.staff_id
and sr.start_date <= ars.end_date
and sr.end_date >= ars.begin_date
and sr.rotation_schedule_id = ars.rotation_schedule_id
where
mss.is_linkage = 1
and mv.id is null -- not breaking the broken linkage availability rule
group by
mps_2.id,
mss.staff_id,
mps.schedule_on,
mps.call_type_id
having
count(distinct
case
when (
sr.id is not null
and ars.id is not null
and mps.schedule_on >= greatest(sr.start_date, ars.begin_date)
)
then sr.rotation_id
else null
end
) < count(distinct rr.rotation_id)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (134.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query08
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.7ms) update buffer_manual_availabilities_59135044 as ma
inner join (
select mps.thread_id, mv.staff_id
from buffer_manual_violations_59135044 as mv
inner join buffer_manual_populate_slots_59135044 as mps
on mv.schedule_id = mps.id
where mv.violation_type in (
'BrokenLinkageAvailabilityRule',
'BrokenLinkageRequiredRotationsRule'
)
group by mps.thread_id, mv.staff_id
order by null
) as t1
on ma.thread_id = t1.thread_id
and ma.staff_id = t1.staff_id
set ma.is_linkage = 0
where ma.is_linkage = 1
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.0ms) update buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
inner join buffer_manual_availabilities_59135044 as ma
on mps.thread_id = ma.thread_id
and mss.staff_id = ma.staff_id
set mss.is_linkage = 0
where mss.is_linkage = 1
and ma.is_linkage = 0
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.1ms) delete mss
from buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
and mps.should_be_populated = 0
left join buffer_manual_availabilities_59135044 as ma
on mps.thread_id = ma.thread_id
and mss.staff_id = ma.staff_id
where mss.is_linkage = 0
or ma.id is null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (137.7ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.8ms) delete from buffer_target_dates
where session_id = 59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (139.8ms) select distinct calendar_schedule_id
from buffer_periods_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (137.9ms) insert into buffer_target_dates (
session_id,
client_id,
calendar_schedule_id,
block_id,
start_date,
end_date,
block_display_order,
period_offset,
build_cd_targets,
cd_targets_source,
copy_cd_targets,
build_ct_targets,
ct_targets_source,
copy_ct_targets,
build_oa_targets,
oa_targets_source,
copy_oa_targets,
build_dow_targets,
dow_targets_source,
copy_dow_targets,
build_point_targets,
point_targets_source,
copy_point_targets,
build_hour_targets,
hour_targets_source,
copy_hour_targets,
is_published,
affected_by_populate,
within_cumulative_period
)
select
59135044 as session_id,
325 as client_id,
t1.calendar_schedule_id,
t1.block_id,
t1.start_date,
t1.end_date,
t1.block_display_order,
t1.period_offset,
case
when (
(
FALSE
)
and t1.is_published = 0
and coalesce(pb.has_unpublished, 1) = 1
and cd_u.block_id is null
) then 1
else 0
end as build_cd_targets,
case
when (
t1.is_published = 1
or (
pb.has_published = 1
and pb.has_unpublished = 0
)
) then 'published'
when (
coalesce(pb.has_published, 0) = 0
and coalesce(pb.has_unpublished, 1) = 1
and cd_u.block_id is not null
) then 'unpublished'
when (
pb.has_published = 1
and pb.has_unpublished = 1
and cd_u.block_id is not null
) then 'both'
else ''
end as cd_targets_source,
0 as copy_cd_targets,
case
when (
(
t1.affected_by_populate = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule */
)
and t1.is_published = 0
and coalesce(pb.has_unpublished, 1) = 1
and ct_u.block_id is null
) then 1
else 0
end as build_ct_targets,
case
when (
t1.is_published = 1
or (
pb.has_published = 1
and pb.has_unpublished = 0
)
) then 'published'
when (
coalesce(pb.has_published, 0) = 0
and coalesce(pb.has_unpublished, 1) = 1
and ct_u.block_id is not null
) then 'unpublished'
when (
pb.has_published = 1
and pb.has_unpublished = 1
and ct_u.block_id is not null
) then 'both'
else ''
end as ct_targets_source,
0 as copy_ct_targets,
case
when (
(
FALSE
)
and t1.is_published = 0
and oa_u.block_id is null
) then 1
else 0
end as build_oa_targets,
case
when (t1.is_published = 1) then 'published'
when (oa_u.block_id is not null) then 'unpublished'
else ''
end as oa_targets_source,
0 as copy_oa_targets,
case
when (
(
FALSE
)
and t1.is_published = 0
and dow_u.block_id is null
) then 1
else 0
end as build_dow_targets,
case
when (t1.is_published = 1) then 'published'
when (dow_u.block_id is not null) then 'unpublished'
else ''
end as dow_targets_source,
0 as copy_dow_targets,
case
when (
(
FALSE
)
and t1.is_published = 0
and p_u.block_id is null
) then 1
else 0
end as build_point_targets,
case
when (t1.is_published = 1) then 'published'
when (p_u.block_id is not null) then 'unpublished'
else ''
end as point_targets_source,
0 as copy_point_targets,
case
when (
(
FALSE
)
and t1.is_published = 0
and h_u.block_id is null
) then 1
else 0
end as build_hour_targets,
case
when (t1.is_published = 1) then 'published'
when (h_u.block_id is not null) then 'unpublished'
else ''
end as hour_targets_source,
0 as copy_hour_targets,
t1.is_published,
t1.affected_by_populate,
t1.within_cumulative_period
from (
select
t1.calendar_schedule_id,
b.id as block_id,
b.start_date,
b.end_date,
t1.block_display_order,
p.period_offset,
case
when (t1.block_display_order between t1.start_block_display_order and t1.end_block_display_order) then 1
else 0
end as affected_by_populate,
case
when (t1.block_display_order between (t1.timeframe_ending - (3 - 1)) and t1.timeframe_ending) then 1
else 0
end as within_cumulative_period,
case
when (pb.id is not null) then 1
else 0
end as is_published
from (
select
t1.calendar_schedule_id,
t1.block_schedule_id,
(t1.end_block_display_order - ns.item_number) as block_display_order,
t1.start_block_display_order,
t1.end_block_display_order,
t1.timeframe_ending
from (
select
calendar_schedule_id,
block_schedule_id,
min(start_block_display_order) as start_block_display_order,
max(end_block_display_order) as end_block_display_order,
max(
case
when (should_be_populated = 1) then end_block_display_order
else null
end
) as timeframe_ending
from
buffer_periods_59135044
group by
calendar_schedule_id,
block_schedule_id
order by null
) as t1
inner join numeric_sequence as ns
on ns.item_number < greatest(
t1.end_block_display_order - t1.start_block_display_order + 1,
t1.end_block_display_order - t1.timeframe_ending + 3 /* cumulative targets period */
)
and t1.end_block_display_order >= ns.item_number
where
(t1.end_block_display_order - ns.item_number) /* block display order */
between t1.start_block_display_order and t1.end_block_display_order
or (t1.end_block_display_order - ns.item_number)
between (t1.timeframe_ending - (3 /* cumulative targets period */ - 1)) and t1.timeframe_ending
order by null
) as t1
inner join blocks as b
on t1.block_schedule_id = b.block_schedule_id
and t1.block_display_order = b.display_order
left join buffer_periods_59135044 as p
on t1.calendar_schedule_id = p.calendar_schedule_id
and t1.block_display_order between p.start_block_display_order and p.end_block_display_order
left join published_blocks as pb
on b.id = pb.block_id
and t1.calendar_schedule_id = pb.calendar_schedule_id
and pb.assignment_ids is null
and pb.days_of_week_mask = 127
order by null
) as t1
left join (
select
calendar_schedule_id,
block_id
from
unpublished_call_days_targets
where
calendar_schedule_id in (1396) /* list of calendar schedules */
group by
calendar_schedule_id,
block_id
order by null
) as cd_u
on t1.calendar_schedule_id = cd_u.calendar_schedule_id
and t1.block_id = cd_u.block_id
left join (
select
calendar_schedule_id,
block_id
from
unpublished_call_type_targets
where
calendar_schedule_id in (1396) /* list of calendar schedules */
group by
calendar_schedule_id,
block_id
order by null
) as ct_u
on t1.calendar_schedule_id = ct_u.calendar_schedule_id
and t1.block_id = ct_u.block_id
left join (
select
calendar_schedule_id,
block_id
from
unpublished_targets
where
calendar_schedule_id in (1396) /* list of calendar schedules */
group by
calendar_schedule_id,
block_id
order by null
) as oa_u
on t1.calendar_schedule_id = oa_u.calendar_schedule_id
and t1.block_id = oa_u.block_id
left join (
select
calendar_schedule_id,
block_id
from
unpublished_day_of_week_targets
where
calendar_schedule_id in (1396) /* list of calendar schedules */
group by
calendar_schedule_id,
block_id
order by null
) as dow_u
on t1.calendar_schedule_id = dow_u.calendar_schedule_id
and t1.block_id = dow_u.block_id
left join (
select
calendar_schedule_id,
block_id
from
unpublished_point_targets
where
calendar_schedule_id in (1396) /* list of calendar schedules */
group by
calendar_schedule_id,
block_id
order by null
) as p_u
on t1.calendar_schedule_id = p_u.calendar_schedule_id
and t1.block_id = p_u.block_id
left join (
select
calendar_schedule_id,
block_id
from
unpublished_hour_targets
where
calendar_schedule_id in (1396) /* list of calendar schedules */
group by
calendar_schedule_id,
block_id
order by null
) as h_u
on t1.calendar_schedule_id = h_u.calendar_schedule_id
and t1.block_id = h_u.block_id
left join (
select
pb.calendar_schedule_id,
pb.block_id,
max(
case
when (coalesce(pbct.days_of_week_mask, 0) > 0) then 1
else 0
end
) as has_published,
max(
case
when (coalesce(pbct.days_of_week_mask, 0) < 127) then 1
else 0
end
) as has_unpublished
from (
select
case
when (tg.id is null) then ct.id
else tg.id
end as assignment_id,
case
when (tg.id is null) then 'CallType'
else 'TargetGroup'
end as assignment_type,
ct.calendar_schedule_id
from
call_types as ct
left join target_group_calls as tgc
on ct.id = tgc.call_type_id
left join target_groups as tg
on tgc.target_group_id = tg.id
and tg.active = 1
where
ct.calendar_schedule_id in (1396) /* list of calendar schedules */
and ct.active = 1
and ct.included_in_targets > 0
and ct.days_of_week_mask > 0
group by
assignment_id,
assignment_type,
ct.calendar_schedule_id
order by null
) as ct
inner join published_blocks as pb
on ct.calendar_schedule_id = pb.calendar_schedule_id
and (
pb.assignment_ids is not null
or pb.days_of_week_mask < 127
)
left join published_block_call_types as pbct
on pb.id = pbct.published_block_id
and ct.assignment_id = pbct.assignment_id
and ct.assignment_type = pbct.assignment_type
group by
pb.calendar_schedule_id,
pb.block_id
order by null
) as pb
on t1.calendar_schedule_id = pb.calendar_schedule_id
and t1.block_id = pb.block_id
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0101 - 414.489ms
[13554a52-f902-4cae-bec0-6e019c72ca70] (147.8ms) /*
MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
*/
update buffer_target_dates
set build_cd_targets = 1
where
session_id = 59135044
and build_cd_targets = 0
and cd_targets_source = ''
and (
build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
)
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.1ms) /*
MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
*/
update buffer_target_dates
set
build_ct_targets = 1, ct_targets_source = '' /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
where
session_id = 59135044
and build_cd_targets = 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0102 - 278.715ms
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.6ms) /*
MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
*/
insert into buffer_call_days_targets_59135044 (
calendar_schedule_id,
block_id,
staff_id,
assignment_id,
assignment_type,
day_of_week_mask,
is_weekend,
raw_target,
target,
initial_target,
scheduled,
available_days
)
select
cdt.calendar_schedule_id,
cdt.block_id,
cdt.staff_id,
cdt.assignment_id,
cdt.assignment_type,
cdt.day_of_week_mask,
cdt.is_weekend,
cdt.raw_target,
cdt.target,
cdt.initial_target,
cdt.scheduled,
cdt.available_days
from
buffer_target_dates as td
inner join call_days_targets as cdt
on td.calendar_schedule_id = cdt.calendar_schedule_id
and td.block_id = cdt.block_id
where
td.session_id = 59135044
and td.build_cd_targets = 0
and td.cd_targets_source in ('published', 'both')
and (
td.build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.5ms) /*
MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule,
StaffCapacityRule, CurrentWeeklyOverallTargetsRule, AnnualTargetsRule, CumulativeWeeklyOverallTargetsRule,
MonthlyDayOfWeekMixRule, AnnualDayOfWeekMixRule
*/
insert into buffer_call_days_targets_59135044 (
calendar_schedule_id,
block_id,
staff_id,
assignment_id,
assignment_type,
day_of_week_mask,
is_weekend,
raw_target,
target,
initial_target,
scheduled,
available_days
)
select
ucdt.calendar_schedule_id,
ucdt.block_id,
ucdt.staff_id,
ucdt.assignment_id,
ucdt.assignment_type,
ucdt.day_of_week_mask,
ucdt.is_weekend,
ucdt.raw_target,
ucdt.target,
ucdt.initial_target,
ucdt.scheduled,
ucdt.available_days
from
buffer_target_dates as td
inner join unpublished_call_days_targets as ucdt
on td.calendar_schedule_id = ucdt.calendar_schedule_id
and td.block_id = ucdt.block_id
where
td.session_id = 59135044
and td.build_cd_targets = 0
and td.cd_targets_source in ('unpublished', 'both')
and (
td.build_ct_targets = 1 /* MonthlyCallMixRule, CurrentWeeklyCallTypeTargetsRule, AnnualCallMixRule, CumulativeWeeklyCallTypeTargetsRule */
)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0103 - 262.16ms
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.4ms) select
max(build_cd_targets) as build_cd_targets,
max(build_ct_targets) as build_ct_targets,
max(build_oa_targets) as build_oa_targets,
max(build_dow_targets) as build_dow_targets,
max(build_point_targets) as build_point_targets,
max(build_hour_targets) as build_hour_targets
from buffer_target_dates
where session_id = 59135044
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets02 - 129.801ms
[13554a52-f902-4cae-bec0-6e019c72ca70] Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0702 - 0.058ms
[13554a52-f902-4cae-bec0-6e019c72ca70] (258.7ms) /* CurrentWeeklyCallTypeTargetsRule */
insert into buffer_current_ct_targets_59135044 (
staff_id,
assignment_id,
assignment_type,
is_weekend,
period_offset,
calendar_schedule_id,
raw_target,
adjusted_target,
scheduled,
available_days
)
select
t.staff_id,
t.assignment_id,
t.assignment_type,
-1 as is_weekend,
t.period_offset,
t.calendar_schedule_id,
sum(t.raw_target) as raw_target,
sum(t.adjusted_target) as adjusted_target,
sum(t.scheduled) as scheduled,
sum(t.available_days) as available_days
from (
select
t.calendar_schedule_id,
t.block_id,
t.staff_id,
td.period_offset,
t.assignment_id,
t.assignment_type,
t.is_weekend,
t.raw_target,
t.target as adjusted_target,
t.scheduled,
t.available_days
from
buffer_target_dates as td
inner join buffer_call_type_targets_59135044 as t
on td.calendar_schedule_id = t.calendar_schedule_id
and td.block_id = t.block_id
where
td.session_id = 59135044
and td.build_ct_targets = 1
and td.affected_by_populate = 1
union
select
ctt.calendar_schedule_id,
ctt.block_id,
ctt.staff_id,
td.period_offset,
ctt.assignment_id,
ctt.assignment_type,
ctt.is_weekend,
ctt.raw_target,
ctt.target,
ctt.scheduled,
ctt.available_days
from
buffer_target_dates as td
inner join call_type_targets as ctt
on td.calendar_schedule_id = ctt.calendar_schedule_id
and td.block_id = ctt.block_id
where
td.session_id = 59135044
and td.build_ct_targets = 0
and td.ct_targets_source in ('published', 'both')
and td.affected_by_populate = 1
union
select
uctt.calendar_schedule_id,
uctt.block_id,
uctt.staff_id,
td.period_offset,
uctt.assignment_id,
uctt.assignment_type,
uctt.is_weekend,
uctt.raw_target,
uctt.target,
uctt.scheduled,
uctt.available_days
from
buffer_target_dates as td
inner join unpublished_call_type_targets as uctt
on td.calendar_schedule_id = uctt.calendar_schedule_id
and td.block_id = uctt.block_id
where
td.session_id = 59135044
and td.build_ct_targets = 0
and td.ct_targets_source in ('unpublished', 'both')
and td.affected_by_populate = 1
union all
select
tad.calendar_schedule_id,
tad.block_id,
tad.staff_id,
td.period_offset,
tad.assignment_id,
tad.assignment_type,
tad.is_weekend,
0 as raw_target,
0 as target,
tad.adjustment_value as scheduled,
0 as available_days
from
buffer_target_dates as td
inner join target_adjustment_details as tad
on td.calendar_schedule_id = tad.calendar_schedule_id
and td.block_id = tad.block_id
and tad.staff_id > 0
where
td.session_id = 59135044
and td.affected_by_populate = 1
order by null
) as t
group by
t.staff_id,
t.assignment_id,
t.assignment_type,
t.period_offset,
t.calendar_schedule_id
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets0703 - 260.667ms
[13554a52-f902-4cae-bec0-6e019c72ca70] Execution Logger: Scheduler::AutoPopulate::Queries::Targets::Targets07Reports - 0.044ms
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (137.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query11
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.3ms) insert into buffer_manual_violations_59135044/* session_id */ (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select straight_join
mps.id as schedule_id,
mss.staff_id,
r.schedule_on as violation_date,
null as violation_details,
'NoCallRequestsHiddenHardRule' as violation_type
from
buffer_manual_staff_slots_59135044/* session_id */ as mss
inner join buffer_manual_populate_slots_59135044/* session_id */ as mps
on mss.schedule_id = mps.id
/* NoCallRequestsHiddenHardRule */
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
and ct.is_no_call_requestable = 1
inner join assign_staff_types as ast use index (ix_assign_staff_types_composite_1)
on mss.staff_id = ast.staff_id
and mps.schedule_on between ast.begin_date and ifnull(ast.end_date, '9999-12-31')
and ast.client_id = 325
inner join request_settings as rs
on ast.staff_type_id = rs.staff_type_id
inner join requests as r use index (ix_requests_composite_2)
on mps.schedule_on = r.schedule_on
and mss.staff_id = r.staff_id
and r.client_id = 325
and r.request_type = 0 /* type = no_call */
and r.status in (1, 3) /* status = approved, fulfilled */
left join request_call_types as rct
on r.id = rct.request_id
and mps.call_type_id = rct.call_type_id
where (
r.all_call_types = 1
and (
rs.call_only_all_call_switch = 0 /* any assignment */
or (
rs.call_only_all_call_switch = 1 /* calls only */
and ct.assignment_type = 0 /* call */
)
)
)
or (
r.all_call_types = 0
and rct.id is not null
)
group by
mps.id,
mss.staff_id,
r.schedule_on
having
count(distinct r.id) >= 1
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (149.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query12_1
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.3ms) insert into buffer_manual_violations_59135044 (
schedule_id, staff_id, violation_date, violation_details, violation_type
)
select mps.id as schedule_id, mss.staff_id,
sch.schedule_on as violation_date,
CONCAT_WS(',', nli.target_type, nli.target_id) as violation_details,
'NegativeLinkRule' as violation_type
from buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
/* NegativeLinkRule */
left join negative_links_influences as nli
on mps.day_of_week_mask = nli.day_of_week_mask
and mps.call_type_id = nli.source_id
and nli.source_type = 'CallType'
and nli.target_type = 'CallType'
left join view_schedules_59135044 as sch
on date_add(mps.schedule_on, interval nli.offset day) = sch.schedule_on
and nli.target_id = sch.call_type_id
and mss.staff_id = sch.staff_id
and not (
mps.schedule_on = sch.schedule_on
and mps.call_type_id = sch.call_type_id
)
where sch.id is not null
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.7ms) insert into buffer_manual_violations_59135044 (
schedule_id, staff_id, violation_date, violation_details, violation_type
)
select mps.id as schedule_id, mss.staff_id,
mps_2.schedule_on as violation_date,
CONCAT_WS(',', nli.target_type, nli.target_id) as violation_details,
'NegativeLinkRule' as violation_type
from buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
/* NegativeLinkRule */
left join negative_links_influences as nli
on mps.day_of_week_mask = nli.day_of_week_mask
and mps.call_type_id = nli.source_id
and nli.source_type = 'CallType'
and nli.target_type = 'CallType'
left join buffer_manual_populate_slots_59135044 as mps_2
on mps.thread_id = mps_2.thread_id
and date_add(mps.schedule_on, interval nli.offset day) = mps_2.schedule_on
and nli.target_id = mps_2.call_type_id
and mps.id != mps_2.id
left join buffer_manual_staff_slots_59135044 as mss_2
on mps_2.id = mss_2.schedule_id
and mss.staff_id = mss_2.staff_id
where mss_2.id is not null
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query12_2
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.8ms) insert into buffer_manual_violations_59135044/* session_id */ (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select
mps.id as schedule_id,
mss.staff_id,
v.schedule_on as violation_date,
concat_ws(
',',
nli.target_type,
nli.target_id
) as violation_details,
'NegativeLinkRule' as violation_type
from
buffer_manual_staff_slots_59135044/* session_id */ as mss
inner join buffer_manual_populate_slots_59135044/* session_id */ as mps
on mss.schedule_id = mps.id
/* NegativeLinkRule */
inner join negative_links_influences as nli
on mps.day_of_week_mask = nli.day_of_week_mask
and mps.call_type_id = nli.source_id
and nli.source_type = 'CallType'
and nli.target_id > 0
and nli.target_type = 'Vacation'
inner join vacations as v use index (ix_vacations_composite1)
on date_add(
mps.schedule_on,
interval nli.offset day
) = v.schedule_on
and mss.staff_id = v.staff_id
and v.client_id = 325
and v.vacation_type = 0 /* vacation */
and nli.target_id = v.non_clinical_type_id
and v.status IN (3)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (261.0ms) insert into buffer_manual_violations_59135044/* session_id */ (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select
mps.id as schedule_id,
mss.staff_id,
v.schedule_on as violation_date,
concat_ws(
',',
nli.target_type,
nli.target_id
) as violation_details,
'NegativeLinkRule' as violation_type
from
buffer_manual_staff_slots_59135044/* session_id */ as mss
inner join buffer_manual_populate_slots_59135044/* session_id */ as mps
on mss.schedule_id = mps.id
/* NegativeLinkRule */
inner join negative_links_influences as nli
on mps.day_of_week_mask = nli.day_of_week_mask
and mps.call_type_id = nli.source_id
and nli.source_type = 'CallType'
and nli.target_id > 0
and nli.target_type = 'NonClinicalType'
inner join vacations as v use index (ix_vacations_composite1)
on date_add(
mps.schedule_on,
interval nli.offset day
) = v.schedule_on
and mss.staff_id = v.staff_id
and v.client_id = 325
and v.vacation_type = 1 /* non-clinical */
and nli.target_id = v.non_clinical_type_id
and v.status in (3)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (136.4ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query14
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (131.3ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query15
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (136.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query16
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query17
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (132.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query18
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.7ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query18_2
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query18_3
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.6ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query18_4
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query19
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.6ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query19_2
[13554a52-f902-4cae-bec0-6e019c72ca70] (140.2ms) /* CurrentWeeklyCallTypeTargetsRule */
insert into buffer_manual_violations_59135044 (
schedule_id,
staff_id,
violation_date,
violation_details,
violation_type
)
select
mps.id as schedule_id,
t1.staff_id,
t1.target_on as violation_date,
concat_ws(',',
t1.calendar_schedule_id,
t1.assignment_id,
t1.assignment_type,
'weekly'
) as violation_details,
'CurrentWeeklyCallTypeTargetsRule' as violation_type
from (
select
t1.thread_id,
t1.staff_id,
t1.assignment_id,
t1.assignment_type,
t1.calendar_schedule_id,
p.start_date as target_on,
ctt.adjusted_target as target,
(t1.scheduled + coalesce(t2.scheduled, 0) + ctt.scheduled) as scheduled
from (
select
mps.thread_id,
mss.staff_id,
case
when (tg.id is null) then mps.call_type_id
else tg.id
end as assignment_id,
case
when (tg.id is null) then 'CallType'
else 'TargetGroup'
end as assignment_type,
mps.period_offset,
mps.calendar_schedule_id,
count(mps.id) as scheduled
from
buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
inner join call_types as ct
on mps.call_type_id = ct.id
and ct.active = 1
and ct.included_in_targets > 0
left join target_group_calls as tgc
on mps.call_type_id = tgc.call_type_id
left join target_groups as tg
on tgc.target_group_id = tg.id
and tg.active = 1
group by
mps.thread_id,
mss.staff_id,
assignment_id,
assignment_type,
mps.period_offset,
mps.calendar_schedule_id
order by null
) as t1
left join (
select
sti.staff_id,
case
when (tg.id is null) then sti.call_type_id
else tg.id
end as assignment_id,
case
when (tg.id is null) then 'CallType'
else 'TargetGroup'
end as assignment_type,
sti.period_offset,
sti.calendar_schedule_id,
(-1 * count(sti.id)) as scheduled
from
buffer_manual_slots_to_ignore_59135044 as sti
inner join call_types as ct
on sti.call_type_id = ct.id
and ct.active = 1
and ct.included_in_targets > 0
left join target_group_calls as tgc
on sti.call_type_id = tgc.call_type_id
left join target_groups as tg
on tgc.target_group_id = tg.id
and tg.active = 1
group by
sti.staff_id,
assignment_id,
assignment_type,
sti.period_offset,
sti.calendar_schedule_id
order by null
) as t2
on t1.staff_id = t2.staff_id
and t1.assignment_id = t2.assignment_id
and t1.assignment_type = t2.assignment_type
and t1.period_offset = t2.period_offset
and t1.calendar_schedule_id = t2.calendar_schedule_id
inner join buffer_periods_59135044 as p
on t1.period_offset = p.period_offset
and t1.calendar_schedule_id = p.calendar_schedule_id
inner join buffer_current_ct_targets_59135044 as ctt
on t1.staff_id = ctt.staff_id
and t1.assignment_id = ctt.assignment_id
and t1.assignment_type = ctt.assignment_type
and ctt.is_weekend = -1
and t1.period_offset = ctt.period_offset
and t1.calendar_schedule_id = ctt.calendar_schedule_id
order by null
) as t1
inner join buffer_manual_populate_slots_59135044 as mps
on t1.thread_id = mps.thread_id
and mps.should_be_populated = 1
where t1.scheduled > round(t1.target, 2)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query19_3
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.8ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query19_4
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.7ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query20
[13554a52-f902-4cae-bec0-6e019c72ca70] (132.7ms) create table buffer_staff_specializations_59135044
like buffer_staff_specializations
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.6ms) insert into buffer_staff_specializations_59135044 (
staff_id,
specialization_id
)
select
s.id as staff_id,
sp.id as specialization_id
from
staff_entities as se
inner join staffs as s
on se.staff_id = s.id
and s.active = 1
and s.take_call = 1
inner join staff_specializations as st_sp
on s.id = st_sp.staff_id
inner join specializations as sp
on st_sp.specialization_id = sp.id
and sp.client_id = 325
and sp.active = 1
-- We will only take staff-specialization pairs that need to be validated
and sp.max_scheduled is not null
where
se.entity_id = 325
union
select
s.id as staff_id,
sp.id as specialization_id
from
staff_entities as se
inner join staffs as s
on se.staff_id = s.id
and s.active = 1
and s.take_call = 1
and s.all_specializations = 1
inner join specializations as sp
on sp.client_id = 325
and sp.active = 1
-- We will only take staff-specialization pairs that need to be validated
and sp.max_scheduled is not null
where
se.entity_id = 325
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.2ms) select 1 from buffer_staff_specializations_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.6ms) drop table buffer_staff_specializations_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (134.6ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query21
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.7ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query21_1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (171.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query21_2
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query21_3
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (147.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query21_4
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.3ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query22
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.8ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query22_1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query22_2
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.6ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query22_3
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query22_4
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (131.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query23
[13554a52-f902-4cae-bec0-6e019c72ca70] (135.3ms) insert into buffer_manual_violations_59135044 (
schedule_id, staff_id, violation_date, violation_details, violation_type
)
select mps.id as schedule_id, t1.staff_id, t1.period_starting_date as violation_date,
concat(
'calendar_schedule_id:', t1.calendar_schedule_id, ',',
'call_type_id:', t1.call_type_id, ',',
'limit:', t1.max_assignments, ',',
'days:', if(t1.is_weekend = 0, 'weekday', 'weekend')
) as violation_details,
'MaxAssignmentsRule' as violation_type
from (
select t1.thread_id, t1.staff_id, t1.calendar_schedule_id, p.start_date as period_starting_date, t1.call_type_id, t1.is_weekend,
if(t1.is_weekend = 0, ct.max_weekday_assignments, ct.max_weekend_assignments) as max_assignments, (t1.scheduled + count(sch.id)) as scheduled
from (
select mps.thread_id, mss.staff_id, mps.calendar_schedule_id, mps.period_offset, mps.call_type_id,
if((dayofweek(mps.schedule_on) - 1) in (0, 6), 1, 0) as is_weekend, count(mps.id) as scheduled
from buffer_manual_staff_slots_59135044 as mss
inner join buffer_manual_populate_slots_59135044 as mps
on mss.schedule_id = mps.id
group by mps.thread_id, mss.staff_id, mps.calendar_schedule_id, mps.period_offset, mps.call_type_id, is_weekend
order by null
) as t1
inner join buffer_periods_59135044 as p
on t1.calendar_schedule_id = p.calendar_schedule_id
and t1.period_offset = p.period_offset
inner join call_types as ct
on t1.call_type_id = ct.id
and t1.calendar_schedule_id = ct.calendar_schedule_id
and ct.active = 1
left join view_schedules_59135044 as sch
on t1.staff_id = sch.staff_id
and sch.schedule_on between p.start_date and p.end_date
and ct.id = sch.call_type_id
and t1.is_weekend = if((dayofweek(sch.schedule_on) - 1) in (0, 6), 1, 0)
group by t1.thread_id, t1.staff_id, t1.calendar_schedule_id, period_starting_date, t1.call_type_id, t1.is_weekend, max_assignments, t1.scheduled
order by null
) as t1
inner join buffer_manual_populate_slots_59135044 as mps
on t1.thread_id = mps.thread_id
and mps.should_be_populated = 1
where t1.scheduled > t1.max_assignments
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.3ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query25
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (131.4ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query252
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query26
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query26_2
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query28
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.3ms) update buffer_manual_violations_59135044 as mv
inner join buffer_manual_populate_slots_59135044 as mps
on mv.schedule_id = mps.id
set mv.calendar_schedule_id = mps.calendar_schedule_id
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.7ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query29
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.8ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query29_2
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (137.6ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30
[13554a52-f902-4cae-bec0-6e019c72ca70] (131.1ms) insert into buffer_assignment_limits_59135044 (
id,
name,
calendar_schedule_id,
considered_days_of_week_mask,
limit_type,
limit_subtypes_mask,
timeframe_type,
max_per_period_limit,
max_weekly_limit,
max_rolling_period,
max_rolling_limit,
max_consecutive_limit
)
select
id,
name,
calendar_schedule_id,
considered_days_of_week_mask,
limit_type,
(
(coalesce(max_per_period_limit, -1) >= 0) * 1 +
(coalesce(max_weekly_limit, -1) >= 0) * 2 +
(
coalesce(max_rolling_period, -1) > 0
and coalesce(max_rolling_limit, -1) >= 0
) * 4 +
(coalesce(max_consecutive_limit, -1) > 0) * 8
) as limit_subtypes_mask,
timeframe_type,
max_per_period_limit,
max_weekly_limit,
max_rolling_period,
max_rolling_limit,
max_consecutive_limit
from assignment_limits
where
client_id = 325
and active = 1
and (
max_per_period_limit >= 0
or max_weekly_limit >= 0
or (
max_rolling_period > 0
and max_rolling_limit >= 0
)
or max_consecutive_limit > 0
)
order by null
[13554a52-f902-4cae-bec0-6e019c72ca70] (137.5ms) select id from buffer_assignment_limits_59135044 limit 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.1ms) select
coalesce(bit_or(limit_subtypes_mask), 0)
from buffer_assignment_limits_59135044
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30_01
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.0ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30_02
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (132.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30_03
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30_04
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.7ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30_05
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30_06
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (140.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query30_07
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::AssignmentLimitApplicability
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (129.9ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::Query40
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.1ms) update buffer_manual_violations_59135044 as mv
inner join buffer_manual_populate_slots_59135044 as mps
on mv.schedule_id = mps.id
set mv.calendar_schedule_id = mps.calendar_schedule_id
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.7ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::QueryPopup
[13554a52-f902-4cae-bec0-6e019c72ca70] (141.8ms) select distinct ma.thread_id, ma.staff_id, s.first_name, s.last_name,
mps_ct.call_type_id, ct.abbreviation,
if(sc_ct.id is null, 0, 1) as call_type_scheduled,
if(
t1.staff_id is null
or (
t1.main_slot_violations_count = 0
and (
t2.unique_staffs_count > 1
or (t2.main_slot_staff_id is not null and t2.filled_slots_count > 1)
or t3.eligible_staffs_count = 0
)
and not (
ma.is_linkage = 1
and t1.other_slots_violations_count > 0
)
),
1, 0
) as is_eligible,
if(
t1.broken_linkage_violations_count > 0,
1, 0
) as breaks_linkage
from buffer_manual_availabilities_59135044 as ma
inner join staffs as s
on ma.staff_id = s.id
left join buffer_manual_populate_slots_59135044 as mps_ct
on ma.thread_id = mps_ct.thread_id
and ma.staff_id = mps_ct.staff_id
left join call_types as ct
on mps_ct.call_type_id = ct.id
and mps_ct.should_be_populated = 1
left join view_schedules_59135044 sc_ct
on sc_ct.schedule_on = mps_ct.schedule_on
and sc_ct.call_type_id = mps_ct.call_type_id
left join (
select mps.thread_id, mv.staff_id,
sum(
if(mps.should_be_populated = 1 and mv.violation_type not like 'BrokenLinkage%', 1, 0)
) as main_slot_violations_count,
sum(
if(mps.should_be_populated != 1, 1, 0)
) as other_slots_violations_count,
sum(
if(mv.violation_type like 'BrokenLinkage%', 1, 0)
) as broken_linkage_violations_count
from buffer_manual_violations_59135044 as mv
inner join buffer_manual_populate_slots_59135044 as mps
on mv.schedule_id = mps.id
group by mps.thread_id, mv.staff_id
order by null
) as t1
on ma.thread_id = t1.thread_id
and ma.staff_id = t1.staff_id
left join (
select mps.thread_id,
max(if(mps.should_be_populated = 1, sch.staff_id, null)) as main_slot_staff_id,
count(distinct sch.staff_id) as unique_staffs_count,
count(sch.id) as filled_slots_count
from buffer_manual_populate_slots_59135044 as mps
left join view_schedules_59135044 as sch
on mps.schedule_on = sch.schedule_on
and mps.call_type_id = sch.call_type_id
group by mps.thread_id
order by null
) as t2
on ma.thread_id = t2.thread_id
left join (
select ma.thread_id, sum(if(t1.staff_id is null, 1, 0)) as eligible_staffs_count
from buffer_manual_availabilities_59135044 as ma
left join (
select mps.thread_id, mv.staff_id
from buffer_manual_violations_59135044 as mv
inner join buffer_manual_populate_slots_59135044 as mps
on mv.schedule_id = mps.id
group by mps.thread_id, mv.staff_id
order by null
) as t1
on ma.thread_id = t1.thread_id
and ma.staff_id = t1.staff_id
group by ma.thread_id
order by null
) as t3
on ma.thread_id = t3.thread_id
where 1=1
order by ma.thread_id, is_eligible desc,
concat(if(s.schedule_alias is not null, s.schedule_alias, ''), s.last_name), s.first_name
[13554a52-f902-4cae-bec0-6e019c72ca70]
call_types result for popup:
{:thread_id=>1, :staff_id=>20704, :first_name=>"Sharon", :last_name=>"Davis", :call_type_id=>nil, :abbreviation=>nil, :call_type_scheduled=>false, :is_eligible=>true, :breaks_linkage=>false}
{:thread_id=>1, :staff_id=>20739, :first_name=>"Ron", :last_name=>"Dobrovinsky", :call_type_id=>nil, :abbreviation=>nil, :call_type_scheduled=>false, :is_eligible=>true, :breaks_linkage=>false}
{:thread_id=>1, :staff_id=>20695, :first_name=>"Aaron", :last_name=>"Goldberg", :call_type_id=>nil, :abbreviation=>nil, :call_type_scheduled=>false, :is_eligible=>true, :breaks_linkage=>false}
{:thread_id=>1, :staff_id=>20722, :first_name=>"Chelovek", :last_name=>"Gordon", :call_type_id=>nil, :abbreviation=>nil, :call_type_scheduled=>false, :is_eligible=>true, :breaks_linkage=>false}
{:thread_id=>1, :staff_id=>20740, :first_name=>"Eduardq", :last_name=>"Katz", :call_type_id=>nil, :abbreviation=>nil, :call_type_scheduled=>false, :is_eligible=>true, :breaks_linkage=>false}
{:thread_id=>1, :staff_id=>20738, :first_name=>"Ezergiel", :last_name=>"Liebman", :call_type_id=>nil, :abbreviation=>nil, :call_type_scheduled=>false, :is_eligible=>true, :breaks_linkage=>false}
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.8ms) select * from buffer_manual_populate_slots_59135044 where should_be_populated = 1
[13554a52-f902-4cae-bec0-6e019c72ca70]
mps:
{"id"=>1, "thread_id"=>1, "schedule_on"=>Tue, 12 Mar 2019, "day_of_week_mask"=>4, "call_type_id"=>12733, "should_be_populated"=>1, "staff_id"=>-1, "period_offset"=>201902, "block_id"=>39643, "calendar_schedule_id"=>1396, "affects_cumulative_targets"=>1, "wday"=>2, "is_weekend"=>0, "points"=>nil, "hours"=>nil}
[13554a52-f902-4cae-bec0-6e019c72ca70] Staff Load (144.3ms) SELECT DISTINCT `staffs`.* FROM `staffs` INNER JOIN `staff_entities` `staff_entities_staffs` ON `staff_entities_staffs`.`staff_id` = `staffs`.`id` INNER JOIN `staff_entities` ON `staffs`.`id` = `staff_entities`.`staff_id` WHERE `staff_entities`.`entity_id` = 325 AND `staffs`.`id` IN (20704, 20739, 20695, 20722, 20740, 20738) ORDER BY staff_entities.entity_id, staff_entities.display_order
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.5ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::QueryAvailabilityType
[13554a52-f902-4cae-bec0-6e019c72ca70] (138.2ms) select mss.staff_id,
case
when (mps.day_of_week_mask & ifnull(sct.days_of_week_mask, 0) & if(r.id is null, 127, ifnull(rct.days_of_week_mask, 0))) > 0 then 3 /* primary */
when (
mps.day_of_week_mask &
(ifnull(sct.days_of_week_mask, 0) | ifnull(sct.secondary_availability_days_of_week_mask, 0)) &
(if(r.id is null, 127, ifnull(rct.days_of_week_mask, 0)) | if(r.id is null, 127, ifnull(rct.secondary_availability_days_of_week_mask, 0)))
) > 0 then 2 /* secondary */
when (ifnull(sct.backup, 0) | ifnull(rct.backup, 0)) > 0 then 1 /* backup */
else 0 /* not available */
end as availability_type
from buffer_manual_populate_slots_59135044 as mps
inner join buffer_manual_staff_slots_59135044 as mss
on mps.id = mss.schedule_id
left join staff_call_types as sct
on mss.staff_id = sct.staff_id
and mps.call_type_id = sct.call_type_id
left join assign_rotation_schedules as ars use index (ix_assign_rotation_schedules_composite_1)
on mss.staff_id = ars.staff_id
and mps.schedule_on between ars.begin_date and ars.end_date
and ars.client_id = 325
left join staff_rotations as sr
on mss.staff_id = sr.staff_id
and mps.schedule_on between sr.start_date and sr.end_date
and ars.rotation_schedule_id = sr.rotation_schedule_id
left join daily_rotations as dr
on mss.staff_id = dr.staff_id
and mps.schedule_on = dr.schedule_on
and ars.rotation_schedule_id = dr.rotation_schedule_id
and sr.id is not null
left join rotations as r
on coalesce(dr.rotation_id, sr.rotation_id) = r.id
and r.active = 1
left join rotation_call_types as rct
on r.id = rct.rotation_id
and mps.call_type_id = rct.call_type_id
where mps.should_be_populated = 1
order by mss.staff_id
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.6ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::QueryStaffsFillableSlots
[13554a52-f902-4cae-bec0-6e019c72ca70] PublishedBlock Load (135.9ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND (((published_blocks.assignment_ids IS NOT NULL) OR (published_blocks.days_of_week_mask < 127))) ORDER BY blocks.start_date LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] PublishedBlock Load (130.2ms) SELECT `published_blocks`.* FROM `published_blocks` INNER JOIN `blocks` ON `blocks`.`id` = `published_blocks`.`block_id` WHERE `published_blocks`.`client_id` = 325 AND ((published_blocks.assignment_ids IS NULL) AND (published_blocks.days_of_week_mask = 127)) ORDER BY blocks.start_date DESC LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (139.2ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`id` = 39642 LIMIT 1
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.1ms) select distinct ss.staff_id, ps.call_type_id, ps.schedule_on
from buffer_manual_staff_slots_59135044 as ss
inner join buffer_manual_populate_slots_59135044 as ps on ss.schedule_id = ps.id
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.4ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::QueryStaffsFillableSlots
[13554a52-f902-4cae-bec0-6e019c72ca70] (129.4ms) select distinct ss.staff_id, ps.call_type_id, ps.schedule_on
from buffer_manual_staff_slots_59135044 as ss
inner join buffer_manual_populate_slots_59135044 as ps on ss.schedule_id = ps.id
[13554a52-f902-4cae-bec0-6e019c72ca70] Block Load (130.1ms) SELECT `blocks`.* FROM `blocks` WHERE `blocks`.`client_id` = 325 AND `blocks`.`block_schedule_id` = 456 AND (blocks.end_date < '2019-03-01') AND (blocks.id != 39643) ORDER BY start_date desc
[13554a52-f902-4cae-bec0-6e019c72ca70] Running Scheduler::Manual::Queries::QueryViolations
[13554a52-f902-4cae-bec0-6e019c72ca70] (130.4ms) select v.*, ps.call_type_id, ps.schedule_on, ps.thread_id
from buffer_manual_violations_59135044 as v
inner join buffer_manual_populate_slots_59135044 as ps
on v.schedule_id = ps.id
[13554a52-f902-4cae-bec0-6e019c72ca70] (491.5ms) select null as dow, mps.call_type_id,
ctt.staff_id, mps.call_type_id, mps.thread_id, ctt.scheduled, ctt.raw_target,