Текущая версия |
Ваш текст |
Строка 24: |
Строка 24: |
| | | |
| = Посчитать уменьшение дохода, если убрать правило 14-ти дней = | | = Посчитать уменьшение дохода, если убрать правило 14-ти дней = |
− | = СЧИТАЕТ НЕКОРРЕКТНО!!! =
| + | |
| <pre> | | <pre> |
| # How much money we will lose if remove 14days-rule. | | # How much money we will lose if remove 14days-rule. |
Строка 56: |
Строка 56: |
| | | |
| <pre> | | <pre> |
− | update Ip INNER JOIN users ON users.uid=Ip.user_uid SET Ip.user_uid = NULL, Ip.mac = NULL | + | update Ip INNER JOIN users ON users.uid=Ip.user_uid SET Ip.user_uid = NULL |
− | WHERE users.disabled > 0 and users.disable_date < '2019-01-01' and Ip.subnet_id= xx ; | + | WHERE users.disabled > 0 and users.disable_date < '2017-01-01' and Ip.subnet_id= xx ; |
| </pre> | | </pre> |
| | | |
Строка 72: |
Строка 72: |
| | | |
| | | |
− | = Найти свободные диапазоны UID, выполнять в DBeaver = | + | = Найти свободные диапазоны UID = |
| | | |
| | | |
Строка 93: |
Строка 93: |
| AND gap_end >= 10000; -- if you need a specific range | | AND gap_end >= 10000; -- if you need a specific range |
| </pre> | | </pre> |
− |
| |
− | = Найти свободные диапазоны в номерах вланов, выполнять в DBeaver =
| |
− |
| |
− |
| |
− | <pre>
| |
− | -- Select gaps in Vlan number ranges
| |
− | SELECT gaps.*, gaps.gap_end - gaps.gap_start + 1 AS gap_size
| |
− | FROM (
| |
− | SELECT z.expected AS gap_start, IF(z.got - 1 > z.expected, z.got - 1, z.expected) AS gap_end
| |
− | FROM (
| |
− | SELECT @rownum := CONVERT(@rownum + 1, SIGNED INTEGER) AS expected, IF(@rownum = number, 0, @rownum := number) AS got
| |
− | FROM (SELECT @rownum := 0) AS a
| |
− | JOIN Vlan
| |
− | ORDER BY number
| |
− | ) AS z
| |
− | WHERE z.got != 0
| |
− | UNION
| |
− | SELECT MAX(number) + 1 AS gap_start, 4096 AS gap_end FROM Vlan
| |
− | ) AS gaps
| |
− | WHERE gap_start <= gap_end
| |
− | AND gap_end >= 1 -- if you need a specific range
| |
− | ORDER BY gap_size DESC;
| |
− | </pre>
| |
− |
| |
| | | |
| = Выбор юзеров вместе в полными адресами в файл= | | = Выбор юзеров вместе в полными адресами в файл= |
Строка 141: |
Строка 117: |
| <pre> | | <pre> |
| select user from users u LEFT JOIN map_flat_uid mfu ON u.uid=mfu.uid WHERE u.gid NOT IN (2,15,34,49) AND mfu.uid IS NULL; | | select user from users u LEFT JOIN map_flat_uid mfu ON u.uid=mfu.uid WHERE u.gid NOT IN (2,15,34,49) AND mfu.uid IS NULL; |
− |
| |
− | </pre>
| |
− |
| |
− | = Выбор логинов, ФИО, телефонов и адресов отключенных юзеров в файл =
| |
− |
| |
− | <pre>
| |
− | SELECT u.user, u.lastname, u.firstname, u.patronymic, u.disabled, u.disable_date,
| |
− | ms.name, mh.number, mf.number, GROUP_CONCAT(ph.number)
| |
− | FROM users u, map_streets ms, map_houses mh, map_porches mp, map_flats mf, map_flat_uid mfu,
| |
− | users_phones up, phones ph
| |
− | WHERE mh.id = mp.house_id
| |
− | AND mp.id = mf.porch_id
| |
− | AND mf.id = mfu.flat_id
| |
− | AND mfu.uid = u.uid
| |
− | AND mh.street_id = ms.id
| |
− | AND up.uid=u.uid
| |
− | AND ph.id = up.phone_id
| |
− | AND u.disabled > 0
| |
− | AND disable_date > '2020-09-30'
| |
− | AND disable_date < '2020-11-01'
| |
− | GROUP BY u.user
| |
− | ORDER BY ms.name
| |
− | INTO OUTFILE '/var/db/mysql/disabled.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
| |
− | </pre>
| |
− |
| |
− |
| |
− | = Изменение логина юзера=
| |
− |
| |
− | Т.к в экшенс записи пишутся по логину, а не по юид, надо во всех экшенсах обновить логины
| |
− |
| |
− | <pre>
| |
− | update users set user='NEW_USERNAME' where user='OLD_USERNAME';
| |
− | update zayavki set login ='NEW_USERNAME' where login ='OLD_USERNAME';
| |
− | update actions set user ='NEW_USERNAME' where user='OLD_USERNAME';
| |
| </pre> | | </pre> |
− |
| |
− |
| |
− | = Все дома, в которых есть хотя бы 1 коммутатор, но при этом меньше 4-х активных абонентов =
| |
− |
| |
− | <pre>
| |
− | SELECT ms.name street, house_with_commutator.number house, house_with_low_active_users.active_users
| |
− | FROM (
| |
− | SELECT mh.id, mh.number, mh.street_id
| |
− | FROM map_houses mh
| |
− | INNER JOIN map_porches mp ON mp.house_id = mh.id
| |
− | INNER JOIN map_flats mf ON mf.porch_id = mp.id
| |
− | INNER JOIN NetworkNode nn ON nn.flat_id = mf.id
| |
− | INNER JOIN Commutator c ON c.networkNode_id = nn.id
| |
− | GROUP BY mh.id
| |
− | ) house_with_commutator
| |
− | INNER JOIN (
| |
− | SELECT mh.id, COUNT(u.uid) active_users
| |
− | FROM map_houses mh
| |
− | INNER JOIN map_porches mp ON mp.house_id = mh.id
| |
− | INNER JOIN map_flats mf ON mf.porch_id = mp.id
| |
− | INNER JOIN map_flat_uid mfu ON mfu.flat_id = mf.id
| |
− | INNER JOIN users u ON u.uid = mfu.uid
| |
− | WHERE u.disabled = 0
| |
− | GROUP BY mh.id
| |
− | HAVING active_users < 4
| |
− | ) house_with_low_active_users ON house_with_low_active_users.id = house_with_commutator.id
| |
− | INNER JOIN map_streets ms ON ms.id = house_with_commutator.street_id
| |
− | ORDER BY ms.name, house_with_commutator.number;
| |
− | </pre>
| |
− |
| |
− | = Исправление MySQL Replication not running=
| |
− |
| |
− | SHOW SLAVE STATUS\G;
| |
− | STOP SLAVE;
| |
− | SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1000000000;
| |
− | START SLAVE;
| |
− | SELECT SLEEP(5);
| |
− | SHOW SLAVE STATUS\G;
| |