Код: Выделить всё
CREATE TABLE `portal_phoenix`.`portal_phoenix_system_service` ( `id` TINYINT UNSIGNED NULL DEFAULT NULL AUTO_INCREMENT , `name` VARCHAR(254) NULL , `description` VARCHAR(254) NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `portal_phoenix`.`portal_phoenix_system_service` (`id`, `name`, `description`) VALUES ('1', 'Elma365', 'Последние документы'), ('2', 'Pro.rent', 'График задолженности по дням просрочки');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_service` (`id`, `name`, `description`) VALUES ('3', 'Почта', 'Последние входящие письма'), ('4', 'Файлы', 'Новые файлы на сервере');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_service` (`id`, `name`, `description`) VALUES ('5', 'Контур', 'Новости бухгалтерии'), ('6', 'Задачи', 'Просроченные и забытые задачи');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_service` (`id`, `name`, `description`) VALUES ('7', 'Напоминания', 'напоминания из «Задач», «Контура» и раздела «Напоминания»');
Код: Выделить всё
CREATE TABLE `portal_phoenix`.`portal_phoenix_system_department` ( `id` TINYINT UNSIGNED NULL AUTO_INCREMENT , `name` VARCHAR(254) CHARACTER SET utf8 COLLATE utf8_general_ci NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `portal_phoenix`.`portal_phoenix_system_department` (`id`, `name`) VALUES (NULL, 'API'), (NULL, 'WebRDP');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_department` (`id`, `name`) VALUES (NULL, 'Админ.'), (NULL, 'Рецеп.');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_department` (`id`, `name`) VALUES (NULL, 'Ком.'), (NULL, 'Экс.');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_department` (`id`, `name`) VALUES (NULL, 'Разв.'), (NULL, 'Юр.');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_department` (`id`, `name`) VALUES (NULL, 'IT'), (NULL, 'Фин.');
Код: Выделить всё
CREATE TABLE `portal_phoenix`.`portal_phoenix_system_chmod` ( `id` TINYINT NULL DEFAULT NULL AUTO_INCREMENT , `service_id` TINYINT NULL DEFAULT NULL , `department_id` INT NULL DEFAULT NULL , `status` ENUM('+','-') NULL DEFAULT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM;
ALTER TABLE `portal_phoenix`.`portal_phoenix_system_chmod` ADD INDEX (`service_id`, `department_id`);
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '1', '1', '+'), (NULL, '2', '1', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '3', '1', '+'), (NULL, '4', '1', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '5', '1', '+'), (NULL, '6', '1', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '7', '1', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '1', '2', '+'), (NULL, '2', '2', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '3', '2', '+'), (NULL, '4', '2', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '5', '2', '+'), (NULL, '6', '2', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '7', '2', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '1', '3', '+'), (NULL, '2', '3', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '3', '3', '+'), (NULL, '4', '3', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '5', '3', '+'), (NULL, '6', '3', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '7', '3', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '1', '4', '+'), (NULL, '2', '4', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '3', '4', '+'), (NULL, '4', '4', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '5', '4', '+'), (NULL, '6', '4', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '7', '4', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '1', '5', '+'), (NULL, '2', '5', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '3', '5', '+'), (NULL, '4', '5', '-');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '5', '5', '+'), (NULL, '6', '5', '+');
INSERT INTO `portal_phoenix`.`portal_phoenix_system_chmod` (`id`, `service_id`, `department_id`, `status`) VALUES (NULL, '7', '5', '+');
Код: Выделить всё
SELECT
`portal_phoenix`.`portal_phoenix_system_chmod`.`id`,
`portal_phoenix`.`portal_phoenix_system_chmod`.`status` ,
`portal_phoenix`.`portal_phoenix_system_service`.`id` AS service_id ,
`portal_phoenix`.`portal_phoenix_system_service`.`name` AS service_name ,
`portal_phoenix`.`portal_phoenix_system_department`.`id` AS department_id ,
`portal_phoenix`.`portal_phoenix_system_department`.`name` AS department_name
FROM
`portal_phoenix`.`portal_phoenix_system_chmod`
LEFT JOIN `portal_phoenix`.`portal_phoenix_system_service` USING( `service_id` )
LEFT JOIN `portal_phoenix`.`portal_phoenix_system_department` USING( `department_id` )
WHERE 1;