94 lines
4.8 KiB
MySQL
94 lines
4.8 KiB
MySQL
|
-- Миграция: добавление полей для отслеживания производительности и просрочек
|
|||
|
|
-- Выполнить: psql -d mkd_control_center -f migrate_add_performance_tracking.sql
|
|||
|
|
|
|||
|
|
-- Добавляем поля в таблицу applications, если их ещё нет
|
|||
|
|
DO $$
|
|||
|
|
BEGIN
|
|||
|
|
-- Добавляем building_id, если его нет
|
|||
|
|
IF NOT EXISTS (
|
|||
|
|
SELECT 1 FROM information_schema.columns
|
|||
|
|
WHERE table_name = 'applications' AND column_name = 'building_id'
|
|||
|
|
) THEN
|
|||
|
|
ALTER TABLE applications ADD COLUMN building_id VARCHAR(50) REFERENCES buildings(id) ON DELETE SET NULL;
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_applications_building ON applications(building_id);
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
-- Добавляем employee_id, если его нет
|
|||
|
|
IF NOT EXISTS (
|
|||
|
|
SELECT 1 FROM information_schema.columns
|
|||
|
|
WHERE table_name = 'applications' AND column_name = 'employee_id'
|
|||
|
|
) THEN
|
|||
|
|
ALTER TABLE applications ADD COLUMN employee_id VARCHAR(50) REFERENCES employees(id) ON DELETE SET NULL;
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_applications_employee ON applications(employee_id);
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
-- Добавляем is_overdue, если его нет
|
|||
|
|
IF NOT EXISTS (
|
|||
|
|
SELECT 1 FROM information_schema.columns
|
|||
|
|
WHERE table_name = 'applications' AND column_name = 'is_overdue'
|
|||
|
|
) THEN
|
|||
|
|
ALTER TABLE applications ADD COLUMN is_overdue BOOLEAN NOT NULL DEFAULT FALSE;
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_applications_overdue ON applications(is_overdue);
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
-- Добавляем updated_at, если его нет
|
|||
|
|
IF NOT EXISTS (
|
|||
|
|
SELECT 1 FROM information_schema.columns
|
|||
|
|
WHERE table_name = 'applications' AND column_name = 'updated_at'
|
|||
|
|
) THEN
|
|||
|
|
ALTER TABLE applications ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
-- Создаём индексы, если их ещё нет
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_applications_performer ON applications(performer_name);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_applications_deadline ON applications(deadline_at);
|
|||
|
|
END $$;
|
|||
|
|
|
|||
|
|
-- Создаём таблицы для статистики производительности, если их ещё нет
|
|||
|
|
CREATE TABLE IF NOT EXISTS employee_performance_stats (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
employee_name TEXT NOT NULL,
|
|||
|
|
period_start DATE NOT NULL,
|
|||
|
|
period_end DATE NOT NULL,
|
|||
|
|
total_assigned INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
total_completed INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
total_overdue INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
total_in_progress INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
total_deferred INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
completion_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
|||
|
|
overdue_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
|||
|
|
performance_score NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
|||
|
|
district_id VARCHAR(50) REFERENCES districts(id) ON DELETE SET NULL,
|
|||
|
|
calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
UNIQUE(employee_name, period_start, period_end)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_performance_stats_employee ON employee_performance_stats(employee_name);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_performance_stats_district ON employee_performance_stats(district_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_performance_stats_period ON employee_performance_stats(period_start, period_end);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_performance_stats_score ON employee_performance_stats(performance_score DESC);
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS district_performance_stats (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
district_id VARCHAR(50) NOT NULL REFERENCES districts(id) ON DELETE CASCADE,
|
|||
|
|
period_start DATE NOT NULL,
|
|||
|
|
period_end DATE NOT NULL,
|
|||
|
|
total_applications INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
total_completed INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
total_overdue INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
total_in_progress INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
completion_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
|||
|
|
overdue_rate NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
|||
|
|
average_score NUMERIC(5, 2) NOT NULL DEFAULT 0,
|
|||
|
|
calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
UNIQUE(district_id, period_start, period_end)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_district_stats_district ON district_performance_stats(district_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_district_stats_period ON district_performance_stats(period_start, period_end);
|
|||
|
|
|
|||
|
|
RAISE NOTICE 'Миграция завершена: добавлены поля для отслеживания производительности';
|