54 lines
2.8 KiB
MySQL
54 lines
2.8 KiB
MySQL
|
ALTER TABLE `users`
|
|||
|
|
ADD `agency_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Название АН' AFTER `fio`,
|
|||
|
|
ADD `last_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Фамилия сотрудника' AFTER `agency_name`,
|
|||
|
|
ADD `first_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Имя сотрудника' AFTER `last_name`,
|
|||
|
|
ADD `middle_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Отчество сотрудника' AFTER `first_name`;
|
|||
|
|
|
|||
|
|
UPDATE `users` SET `agency_name` = '', `last_name` = '', `first_name` = '', `middle_name` = '';
|
|||
|
|
|
|||
|
|
UPDATE `users` SET `agency_name` = `fio` WHERE `agency` = 1;
|
|||
|
|
|
|||
|
|
UPDATE users AS `users`
|
|||
|
|
INNER JOIN (
|
|||
|
|
SELECT
|
|||
|
|
`u`.`id`,
|
|||
|
|
SUBSTRING_INDEX(`u`.`director`, ' ', 1) AS `l_name`,
|
|||
|
|
IF(
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`director`, ' ', 2), ' ', -1) = SUBSTRING_INDEX(`u`.`director`, ' ', 1),
|
|||
|
|
'',
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`director`, ' ', 2), ' ', -1)
|
|||
|
|
) AS `f_name`,
|
|||
|
|
IF(
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`director`, ' ', -1), ' ', -1) = SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`director`, ' ', 2), ' ', -1),
|
|||
|
|
'',
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`director`, ' ', -1), ' ', -1)
|
|||
|
|
) AS `m_name`
|
|||
|
|
FROM `users` AS `u` WHERE `u`.`agency` = 1 AND `u`.`director` != '' GROUP BY `u`.`id`
|
|||
|
|
) AS `directors` ON `directors`.`id` = `users`.id
|
|||
|
|
SET `users`.`last_name` = `directors`.`l_name`,
|
|||
|
|
`users`.`first_name` = `directors`.`f_name`,
|
|||
|
|
`users`.`middle_name` = `directors`.`m_name`
|
|||
|
|
WHERE `users`.`id` = `directors`.`id`;
|
|||
|
|
|
|||
|
|
UPDATE users AS `users`
|
|||
|
|
INNER JOIN (
|
|||
|
|
SELECT
|
|||
|
|
`u`.`id`,
|
|||
|
|
SUBSTRING_INDEX(`u`.`fio`, ' ', 1) AS `l_name`,
|
|||
|
|
IF(
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`fio`, ' ', 2), ' ', -1) = SUBSTRING_INDEX(`u`.`fio`, ' ', 1),
|
|||
|
|
'',
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`fio`, ' ', 2), ' ', -1)
|
|||
|
|
) AS `f_name`,
|
|||
|
|
IF(
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`fio`, ' ', -1), ' ', -1) = SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`fio`, ' ', 2), ' ', -1),
|
|||
|
|
'',
|
|||
|
|
SUBSTRING_INDEX(SUBSTRING_INDEX(`u`.`fio`, ' ', -1), ' ', -1)
|
|||
|
|
) AS `m_name`
|
|||
|
|
FROM `users` AS `u` WHERE `u`.`agency` <> 1 AND `u`.`fio` != '' GROUP BY `u`.`id`
|
|||
|
|
) AS `customers` ON `customers`.`id` = `users`.id
|
|||
|
|
SET `users`.`last_name` = `customers`.`l_name`,
|
|||
|
|
`users`.`first_name` = `customers`.`f_name`,
|
|||
|
|
`users`.`middle_name` = `customers`.`m_name`
|
|||
|
|
WHERE `users`.`id` = `customers`.`id`;
|