angel/site_aegisone
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
site_aegisone/data/migration.sql
T
angel ca4d00c895 Initial commit
2026-05-17 05:22:06 +03:00

434 lines
25 KiB
SQL
Raw Permalink Blame History

-- AegisOne Engineering — Full Database Schema
-- Run once on MySQL 5.7+ / MariaDB 10.3+
CREATE TABLE IF NOT EXISTS `users` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`login` varchar(50) NOT NULL UNIQUE,
`password_hash` varchar(255) NOT NULL,
`role` enum('owner','engineer','technician') NOT NULL DEFAULT 'technician',
`full_name` varchar(255) NOT NULL,
`phone` varchar(50) DEFAULT '',
`email` varchar(255) DEFAULT '',
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `login_attempts` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`ip_address` varchar(45) NOT NULL,
`login` varchar(50) NOT NULL DEFAULT '',
`attempt_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_ip` (`ip_address`),
INDEX `idx_time` (`attempt_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `audit_log` (
`id` bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` int UNSIGNED DEFAULT NULL,
`action` varchar(255) NOT NULL,
`details` text DEFAULT NULL,
`ip_address` varchar(45) DEFAULT '',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_user` (`user_id`),
INDEX `idx_action` (`action`),
INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `objects` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` varchar(255) NOT NULL,
`address` varchar(500) DEFAULT '',
`object_type` varchar(100) DEFAULT '',
`area_sqm` decimal(10,2) DEFAULT 0,
`employees_count` int DEFAULT 0,
`contact_person` varchar(255) DEFAULT '',
`contact_phone` varchar(50) DEFAULT '',
`status` enum('active','inactive','audit','prospective') NOT NULL DEFAULT 'active',
`notes` text DEFAULT NULL,
`risk_score` decimal(5,2) DEFAULT 0,
`complexity_index` decimal(5,2) DEFAULT 0,
`infrastructure_load` decimal(5,2) DEFAULT 0,
`service_history` decimal(5,2) DEFAULT 0,
`object_index` decimal(5,2) DEFAULT 0,
`sla_price_monthly` decimal(12,2) DEFAULT 0,
`region_factor` decimal(3,2) DEFAULT 1.00,
`created_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `object_assignments` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`object_id` int UNSIGNED NOT NULL,
`user_id` int UNSIGNED NOT NULL,
`role` enum('engineer','technician') NOT NULL,
`assigned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`unassigned_at` datetime DEFAULT NULL,
INDEX `idx_object` (`object_id`),
INDEX `idx_user` (`user_id`),
UNIQUE KEY `uq_active` (`object_id`,`user_id`,`unassigned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tasks` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`object_id` int UNSIGNED NOT NULL,
`assigned_to` int UNSIGNED DEFAULT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`title` varchar(500) NOT NULL,
`description` text DEFAULT NULL,
`priority` enum('P1','P2','P3','P4') NOT NULL DEFAULT 'P3',
`status` enum('open','in_progress','completed','cancelled') NOT NULL DEFAULT 'open',
`deadline` datetime DEFAULT NULL,
`sla_remaining_hours` decimal(6,1) DEFAULT 0,
`response_time_minutes` int DEFAULT 0,
`resolution_time_minutes` int DEFAULT 0,
`sla_compliant` tinyint(1) DEFAULT NULL,
`checklist_data` text DEFAULT NULL,
`result_notes` text DEFAULT NULL,
`closed_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_object` (`object_id`),
INDEX `idx_assigned` (`assigned_to`),
INDEX `idx_status` (`status`),
INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `task_comments` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`task_id` int UNSIGNED NOT NULL,
`user_id` int UNSIGNED DEFAULT NULL,
`comment` text NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `task_photos` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`task_id` int UNSIGNED DEFAULT NULL,
`report_id` int UNSIGNED DEFAULT NULL,
`file_path` varchar(500) NOT NULL,
`description` varchar(500) DEFAULT '',
`type` enum('before','after','evidence','other') DEFAULT 'other',
`uploaded_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_task` (`task_id`),
INDEX `idx_report` (`report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `reports` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`object_id` int UNSIGNED NOT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`report_type` enum('inspection','service','emergency','audit','monthly') NOT NULL DEFAULT 'service',
`title` varchar(500) DEFAULT '',
`description` text DEFAULT NULL,
`findings` text DEFAULT NULL,
`recommendations` text DEFAULT NULL,
`work_done` text DEFAULT NULL,
`cause_classification` varchar(100) DEFAULT NULL,
`result_status` enum('fixed','partial','revisit','ok') DEFAULT 'ok',
`status` enum('draft','final','cancelled') NOT NULL DEFAULT 'draft',
`client_confirmed` tinyint(1) DEFAULT 0,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_object` (`object_id`),
INDEX `idx_author` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `questionnaire_sessions` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`object_id` int UNSIGNED DEFAULT NULL,
`client_name` varchar(255) DEFAULT '',
`created_by` int UNSIGNED DEFAULT NULL,
`status` enum('draft','completed','cancelled') NOT NULL DEFAULT 'draft',
`current_step` tinyint UNSIGNED NOT NULL DEFAULT 1,
`risk_score` decimal(5,2) DEFAULT 0,
`complexity_index` decimal(5,2) DEFAULT 0,
`infrastructure_load` decimal(5,2) DEFAULT 0,
`service_history` decimal(5,2) DEFAULT 0,
`object_index` decimal(5,2) DEFAULT 0,
`sla_price_monthly` decimal(12,2) DEFAULT 0,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_object` (`object_id`),
INDEX `idx_author` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `questionnaire_answers` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`session_id` int UNSIGNED NOT NULL,
`step` tinyint UNSIGNED NOT NULL,
`question_key` varchar(100) NOT NULL,
`answer_value` text DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_session` (`session_id`),
INDEX `idx_step` (`step`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `object_passports` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`session_id` int UNSIGNED DEFAULT NULL,
`object_id` int UNSIGNED DEFAULT NULL,
`object_index` decimal(5,2) DEFAULT 0,
`risk_score` decimal(5,2) DEFAULT 0,
`complexity_index` decimal(5,2) DEFAULT 0,
`sla_price_monthly` decimal(12,2) DEFAULT 0,
`passport_data` json DEFAULT NULL,
`created_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_object` (`object_id`),
INDEX `idx_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `sla_contracts` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`object_id` int UNSIGNED NOT NULL,
`client_name` varchar(255) DEFAULT '',
`start_date` date NOT NULL,
`end_date` date DEFAULT NULL,
`sla_price_monthly` decimal(12,2) NOT NULL DEFAULT 0,
`penalty_rate` decimal(5,2) DEFAULT 0,
`response_time_p1` int DEFAULT 4,
`response_time_p2` int DEFAULT 8,
`response_time_p3` int DEFAULT 24,
`service_level` enum('start','business','enterprise') NOT NULL DEFAULT 'business',
`status` enum('active','expired','cancelled','negotiation') NOT NULL DEFAULT 'negotiation',
`created_by` int UNSIGNED DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_object` (`object_id`),
INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `incidents` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`object_id` int UNSIGNED NOT NULL,
`reported_by` int UNSIGNED DEFAULT NULL,
`assigned_to` int UNSIGNED DEFAULT NULL,
`title` varchar(500) NOT NULL,
`description` text DEFAULT NULL,
`severity` enum('P1','P2','P3') NOT NULL DEFAULT 'P3',
`status` enum('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
`resolution` text DEFAULT NULL,
`cause` varchar(500) DEFAULT '',
`resolution_time_minutes` int DEFAULT 0,
`sla_breached` tinyint(1) DEFAULT 0,
`resolved_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_object` (`object_id`),
INDEX `idx_severity` (`severity`),
INDEX `idx_status` (`status`),
INDEX `idx_assigned` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `engineer_kpi` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`engineer_id` int UNSIGNED NOT NULL,
`period_start` date NOT NULL,
`period_end` date NOT NULL,
`sla_compliance` decimal(5,2) DEFAULT 0,
`response_time_score` decimal(5,2) DEFAULT 0,
`resolution_time_score` decimal(5,2) DEFAULT 0,
`diagnosis_accuracy` decimal(5,2) DEFAULT 0,
`reopen_rate` decimal(5,2) DEFAULT 0,
`risk_coverage_score` decimal(5,2) DEFAULT 0,
`documentation_quality` decimal(5,2) DEFAULT 0,
`engineer_score` decimal(5,2) DEFAULT 0,
`grade` varchar(20) DEFAULT '',
`components_json` json DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_engineer` (`engineer_id`),
INDEX `idx_period` (`period_start`,`period_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `shs_records` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`recorded_by` int UNSIGNED DEFAULT NULL,
`period_start` date NOT NULL,
`period_end` date NOT NULL,
`sla_stability` decimal(5,2) DEFAULT 0,
`revenue_stability` decimal(5,2) DEFAULT 0,
`retention` decimal(5,2) DEFAULT 0,
`engineer_performance` decimal(5,2) DEFAULT 0,
`incident_stability` decimal(5,2) DEFAULT 0,
`sales_flow` decimal(5,2) DEFAULT 0,
`operational_efficiency` decimal(5,2) DEFAULT 0,
`shs_score` decimal(5,2) DEFAULT 0,
`shs_status` varchar(20) DEFAULT '',
`components_json` json DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_period` (`period_start`,`period_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `blog_posts` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`title` varchar(500) NOT NULL,
`slug` varchar(500) NOT NULL UNIQUE,
`content` longtext NOT NULL,
`excerpt` varchar(1000) DEFAULT '',
`category` enum('risk-engineering','sla-service','incident-cases','infrastructure-deep-dive','compliance-mchs','economics-security','case-studies') NOT NULL DEFAULT 'risk-engineering',
`author_id` int UNSIGNED DEFAULT NULL,
`status` enum('draft','published','archived') NOT NULL DEFAULT 'draft',
`published_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_slug` (`slug`),
INDEX `idx_category` (`category`),
INDEX `idx_status` (`status`),
INDEX `idx_published` (`published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Seed: default owner (login: owner, password: AegisOne2024!)
INSERT INTO `users` (`login`, `password_hash`, `role`, `full_name`, `is_active`)
VALUES ('owner', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'owner', 'System Owner', 1)
ON DUPLICATE KEY UPDATE `id`=`id`;
-- =====================================================================
-- formula_coefficients — редактируемые коэффициенты формул
-- =====================================================================
CREATE TABLE IF NOT EXISTS `formula_coefficients` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`key` varchar(100) NOT NULL UNIQUE,
`value` decimal(10,4) NOT NULL DEFAULT 0,
`description` varchar(500) DEFAULT '',
`formula_ref` varchar(255) DEFAULT '',
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`updated_by` int UNSIGNED DEFAULT NULL,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_key` (`key`),
INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Seed: коэффициенты формул (значения по умолчанию из functions.php)
INSERT INTO `formula_coefficients` (`key`, `value`, `description`, `formula_ref`) VALUES
-- Risk Score
('risk_score.no_archive', 25, 'Нет архива видеонаблюдения', 'calc_risk_score()'),
('risk_score.no_power_backup', 20, 'Нет резервного питания', 'calc_risk_score()'),
('risk_score.no_regulations', 15, 'Нет регламента обслуживания', 'calc_risk_score()'),
('risk_score.system_failures', 20, 'Частые сбои систем', 'calc_risk_score()'),
('risk_score.no_documentation', 10, 'Нет документации', 'calc_risk_score()'),
('risk_score.bound_low', 20, 'Граница низкого риска', 'risk_label()'),
('risk_score.bound_medium', 50, 'Граница среднего риска', 'risk_label()'),
('risk_score.bound_high', 75, 'Граница высокого риска', 'risk_label()'),
-- Risk Multiplier
('risk_multiplier.low', 1.0, 'Множитель низкого риска (0-20)', 'risk_multiplier()'),
('risk_multiplier.medium', 1.3, 'Множитель среднего риска (21-50)', 'risk_multiplier()'),
('risk_multiplier.high', 1.6, 'Множитель высокого риска (51-75)', 'risk_multiplier()'),
('risk_multiplier.critical', 2.0, 'Множитель критического риска (76-100)', 'risk_multiplier()'),
-- Complexity
('complexity.video_0_20', 10, 'Видеонаблюдение до 20 камер', 'calc_complexity()'),
('complexity.video_20_100', 20, 'Видеонаблюдение 20-100 камер', 'calc_complexity()'),
('complexity.video_100plus', 35, 'Видеонаблюдение 100+ камер', 'calc_complexity()'),
('complexity.access_0_5', 10, 'СКУД до 5 точек', 'calc_complexity()'),
('complexity.access_5_20', 20, 'СКУД 5-20 точек', 'calc_complexity()'),
('complexity.access_20plus', 30, 'СКУД 20+ точек', 'calc_complexity()'),
('complexity.fire_simple', 15, 'Пожарная сигнализация простая', 'calc_complexity()'),
('complexity.fire_medium', 25, 'Пожарная сигнализация средняя', 'calc_complexity()'),
('complexity.fire_complex', 40, 'Пожарная сигнализация сложная (>5000 м²)', 'calc_complexity()'),
('complexity.it', 10, 'IT-инфраструктура', 'calc_complexity()'),
-- Infrastructure Load
('infra.server_none', 15, 'Нет сервера', 'calc_infrastructure_load()'),
('infra.server_weak', 10, 'Слабый сервер', 'calc_infrastructure_load()'),
('infra.network_unstable', 20, 'Нестабильная сеть', 'calc_infrastructure_load()'),
('infra.network_partial', 10, 'Частичная сеть', 'calc_infrastructure_load()'),
('infra.power_none', 20, 'Нет UPS', 'calc_infrastructure_load()'),
('infra.power_weak', 10, 'Слабый UPS', 'calc_infrastructure_load()'),
-- Service History
('history.none', 30, 'Нет обслуживания', 'calc_service_history()'),
('history.irregular', 20, 'Нерегулярное обслуживание', 'calc_service_history()'),
('history.formal', 10, 'Формальный подрядчик', 'calc_service_history()'),
('history.sla', 0, 'Есть SLA', 'calc_service_history()'),
-- Object Index
('object_index.risk_weight', 0.4, 'Вес Risk Score', 'calc_object_index()'),
('object_index.complexity_weight', 0.3, 'Вес Complexity', 'calc_object_index()'),
('object_index.infra_weight', 0.2, 'Вес Infra Load', 'calc_object_index()'),
('object_index.history_weight', 0.1, 'Вес Service History', 'calc_object_index()'),
-- SLA Price
('sla_price.base_cost', 15000, 'Базовая стоимость инженера', 'calc_sla_price()'),
('sla_price.region_default', 1.0, 'Региональный коэффициент по умолчанию', 'calc_sla_price()'),
-- Engineer Score
('engineer.score.sla_weight', 0.25, 'Вес SLA Compliance', 'calc_engineer_score()'),
('engineer.score.response_weight', 0.20, 'Вес Response Time', 'calc_engineer_score()'),
('engineer.score.resolution_weight', 0.20, 'Вес Resolution Time', 'calc_engineer_score()'),
('engineer.score.diagnosis_weight', 0.15, 'Вес Diagnosis Accuracy', 'calc_engineer_score()'),
('engineer.score.reopen_weight', 0.10, 'Вес Reopen Rate', 'calc_engineer_score()'),
('engineer.score.risk_coverage_weight', 0.10, 'Вес Risk Coverage', 'calc_engineer_score()'),
('engineer.grade.senior', 90, 'Граница Senior', 'engineer_grade()'),
('engineer.grade.strong', 80, 'Граница Strong', 'engineer_grade()'),
('engineer.grade.middle', 70, 'Граница Middle', 'engineer_grade()'),
-- ECS (Engineer Control Score)
('ecs.sla_weight', 0.30, 'Вес SLA Control', 'calc_engineer_control_score()'),
('ecs.task_dist_weight', 0.25, 'Вес Task Distribution', 'calc_engineer_control_score()'),
('ecs.incident_red_weight', 0.20, 'Вес Incident Reduction', 'calc_engineer_control_score()'),
('ecs.team_perf_weight', 0.15, 'Вес Team Performance', 'calc_engineer_control_score()'),
('ecs.response_coord_weight', 0.10, 'Вес Response Coordination', 'calc_engineer_control_score()'),
-- SHS (System Health Score)
('shs.sla_stability_weight', 0.22, 'Вес SLA Stability', 'calc_shs()'),
('shs.revenue_stability_weight', 0.18, 'Вес Revenue Stability', 'calc_shs()'),
('shs.retention_weight', 0.18, 'Вес Retention', 'calc_shs()'),
('shs.engineer_perf_weight', 0.15, 'Вес Engineer Performance', 'calc_shs()'),
('shs.incident_stability_weight', 0.12, 'Вес Incident Stability', 'calc_shs()'),
('shs.sales_flow_weight', 0.10, 'Вес Sales Flow', 'calc_shs()'),
('shs.operational_eff_weight', 0.05, 'Вес Operational Efficiency', 'calc_shs()'),
('shs.zone_growth', 85, 'Граница зоны Growth', 'shs_status(), shs_zone()'),
('shs.zone_stable', 70, 'Граница зоны Stable', 'shs_status(), shs_zone()'),
('shs.zone_risk', 50, 'Граница зоны Risk', 'shs_status(), shs_zone()'),
('shs.delta_warning', 5, 'Порог предупреждения ΔSHS', 'shs_delta()'),
('shs.delta_critical', 10, 'Порог критического ΔSHS', 'shs_delta()'),
-- CEO SHS
('ceo_shs.mrr_weight', 0.25, 'Вес MRR Growth', 'calc_ceo_shs()'),
('ceo_shs.sla_weight', 0.20, 'Вес SLA Compliance', 'calc_ceo_shs()'),
('ceo_shs.retention_weight', 0.20, 'Вес Retention', 'calc_ceo_shs()'),
('ceo_shs.productivity_weight', 0.15, 'Вес Productivity', 'calc_ceo_shs()'),
('ceo_shs.conversion_weight', 0.10, 'Вес Conversion', 'calc_ceo_shs()'),
('ceo_shs.incident_weight', 0.10, 'Вес Incident Stability', 'calc_ceo_shs()'),
-- SSI (SLA Stability Index)
('ssi.breach_severity_p1', 1.0, 'Severity P1', 'calc_breach_severity()'),
('ssi.breach_severity_p2', 0.5, 'Severity P2', 'calc_breach_severity()'),
('ssi.breach_severity_p3', 0.2, 'Severity P3', 'calc_breach_severity()'),
-- ISI (Incident Stability Index)
('isi.severity_p1', 1.0, 'Severity P1', 'calc_incident_stability_index()'),
('isi.severity_p2', 0.5, 'Severity P2', 'calc_incident_stability_index()'),
('isi.severity_p3', 0.2, 'Severity P3', 'calc_incident_stability_index()'),
-- Automation Rules
('rules.shs_threshold', 70, 'Порог SHS', 'check_automation_rules()'),
('rules.sla_threshold', 90, 'Порог SLA Compliance', 'check_automation_rules()'),
('rules.retention_threshold', 90, 'Порог Retention', 'check_automation_rules()'),
-- Bonus
('bonus.score_90plus', 90, 'Граница премии 90+', 'calc_bonus_percent()'),
('bonus.score_80_89', 80, 'Граница премии 80-89', 'calc_bonus_percent()'),
('bonus.premium_90plus', 20, 'Премия при Score >= 90 (%)', 'calc_bonus_percent()'),
('bonus.premium_80_89', 10, 'Премия при Score 80-89 (%)', 'calc_bonus_percent()'),
('bonus.premium_below_80', 0, 'Премия при Score < 80 (%)', 'calc_bonus_percent()'),
-- Retention
('retention.key_client_penalty', 0.1, 'Штраф за потерю ключевого клиента', 'calc_retention_key_client()')
ON DUPLICATE KEY UPDATE `id`=`id`;
-- =====================================================================
-- cases — примеры из практики (карусель на главной)
-- =====================================================================
CREATE TABLE IF NOT EXISTS `cases` (
`id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`title` varchar(500) NOT NULL,
`text` text NOT NULL,
`effect` text NOT NULL,
`sort_order` int UNSIGNED NOT NULL DEFAULT 0,
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_sort` (`sort_order`),
INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `cases` (`title`, `text`, `effect`, `sort_order`) VALUES
('Склад крупной торговой сети', 'Выявлено: слепые зоны в зонах разгрузки, недостаточное разрешение камер в зонах погрузки/разгрузки. Одна камера не исправна, у двух не настроена запись.', 'предотвращение убытков на сумму более 5 млн рублей в год.', 1),
('Производство пищевых продуктов', 'Неправильная настройка СКУД позволяла несанкционированный доступ в производственные помещения, создавая риск нарушения норм СанПин.', 'снижение риска порчи продукции и утечки конфиденциальной информации.', 2),
('Банковский офис', 'Недостатки в системе ОПС приводили к невозможности срабатывания или задержкам в реагировании при ЧП.', 'сокращение рисков утраты имущества и ценностей, повышение уровня безопасности.', 3),
('Торгово-развлекательный центр', 'Метод «Тайный покупатель» выявил: охрана не реагирует на срабатывание металлодетекторов, оставляет посты без присмотра. Камеры на парковке не охватывают часть машиномест.', 'снижение риска краж, повышение дисциплины службы охраны.', 4),
('Гостиница', 'СКУД не синхронизирована с системой бронирования — доступ в занятые номера. ОПС в части номеров отключена. Видеокамеры имеют мёртвые зоны.', 'устранение репутационных рисков и риска хищения личных вещей гостей.', 5),
('Логистический центр', 'Отсутствие видеоконтроля на участках приёмки и отгрузки приводило к систематическим хищениям. СКУД на въезде не фиксирует данные транспортных средств.', 'сокращение потерь товара на 15%.', 6)
ON DUPLICATE KEY UPDATE `id`=`id`;
Reference in New Issue View Git Blame Copy Permalink