Table of Contents |
---|
Автоматическое заполнение полей "Номер контракта" и "Дата контракта"
Незаполненные поля «Номер контракта» и «Дата контракта» заполняются автоматически системой. Полю «Номер контракта» присваивается следующий порядковый номер, а полю «Дата контракта» текущая дата.
Для включения данной возможности нужно создать триггер для MySQL (Данная возможность работает начиная с версии MySQL 5.0).
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi FOR EACH ROW BEGIN DECLARE new_id BIGINT UNSIGNED; SET new_id=1; IF NEW.contract_id = '' THEN SELECT CAST(contract_id AS UNSIGNED)+1 INTO new_id FROM users_pi WHERE contract_sufix=NEW.contract_sufix ORDER BY 1 DESC LIMIT 1; SET NEW.contract_id=new_id; END IF; IF NEW.contract_date = '0000-00-00' THEN SET NEW.contract_date=curdate(); END IF; END; // DELIMITER ; |
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE TRIGGER next_contract_id_change BEFORE UPDATE ON users_pi FOR EACH ROW BEGIN DECLARE new_id BIGINT UNSIGNED; DECLARE old_contract_sufix CHAR; IF NEW.contract_id = '' THEN SET new_id=1; SELECT contract_sufix INTO old_contract_sufix FROM users_pi WHERE uid=NEW.uid; SELECT CAST(contract_id AS UNSIGNED)+1 INTO new_id FROM users_pi WHERE contract_sufix=NEW.contract_sufix ORDER BY 1 DESC LIMIT 1; SET NEW.contract_id=new_id; IF NEW.contract_date = '0000-00-00' THEN SET NEW.contract_date=curdate(); END IF; END IF; END // DELIMITER ; |
Заполнение "Номер контракта" в соответствие с uid
Code Block |
---|
DELIMITER // CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi FOR EACH ROW BEGIN IF NEW.contract_id = '' THEN SET NEW.contract_id=NEW.uid; END IF; IF NEW.contract_date = '0000-00-00' THEN SET NEW.contract_date=curdate(); END IF; END; // DELIMITER ; |
Задать всем пользователям номер договора равный их uid, а а дату договора - дате создания абонента:
Code Block |
---|
UPDATE users_pi LEFT JOIN users ON users_pi.uid = users.uid SET contract_id = users_pi.uid, contract_date = users.registration |
Заполнение "Номер контракта" в случайным числом
В триггере переменную max_value изменяем на нужное число
Code Block |
---|
DELIMITER //
CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi
FOR EACH ROW BEGIN
DECLARE max_value INT;
DECLARE new_contract_id INT;
IF NEW.contract_id = '' THEN
SET max_value = 9999999999;
REPEAT
SET new_contract_id = FLOOR(RAND() * max_value);
UNTIL NOT EXISTS (SELECT * FROM users_pi WHERE contract_id = new_contract_id)
END REPEAT;
SET NEW.contract_id=new_contract_id;
END IF;
IF NEW.contract_date = '0000-00-00' THEN
SET NEW.contract_date=curdate();
END IF;
END;
//
DELIMITER ; |
Автосоздание счета или квитанции при пополнении счёта.
Автосоздание счета или квитанции при пополнении счёта для платежей через терминалы.
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE TRIGGER add_docs AFTER INSERT ON payments FOR EACH ROW BEGIN DECLARE payment_id INTEGER; DECLARE payment_sum INTEGER; DECLARE admin_id INTEGER; DECLARE next_acct_id INTEGER; DECLARE doc_id INTEGER; DECLARE next_invoice_id INTEGER; SET payment_id = NEW.id; SET payment_sum = NEW.sum; SET admin_id = NEW.aid; SET next_acct_id= 0; SET doc_id = 0; SET next_invoice_id= 0; IF NEW.method > 40 THEN SELECT if(max(acct_id) IS NULL, 0, max(acct_id))+1 INTO next_acct_id FROM docs_acct WHERE DATE_FORMAT(date,'%Y')=DATE_FORMAT(curdate(), '%Y'); INSERT INTO docs_acct (acct_id, date, created, customer, phone, aid, uid, payment_id) values (next_acct_id, now(), now(), '', '', admin_id, NEW.uid, payment_id); SET doc_id = LAST_INSERT_ID(); INSERT INTO docs_acct_orders (acct_id, orders, counts, unit, price) values (doc_id, NEW.dsc, 1, 0, payment_sum); SELECT if(max(invoice_id) IS NULL, 0, max(invoice_id))+1 INTO next_invoice_id FROM docs_invoice WHERE DATE_FORMAT(date, '%Y')=DATE_FORMAT(curdate(), '%Y'); INSERT INTO docs_invoice (invoice_id, date, created, customer, phone, aid, uid, payment_id) values (next_invoice_id, now(), now(), '', '', admin_id, NEW.uid, payment_id); SET doc_id = LAST_INSERT_ID(); INSERT INTO docs_invoice_orders (invoice_id, orders, counts, unit, price) values (doc_id, NEW.dsc, 1, 0, payment_sum); END IF; END; // DELIMITER ; |
Триггер автозаполнения поля ext_id для оплаты
Если надо синхронизировать биллинг с внешними программами и вести параллельный учёт автозаполнение поможет вести параллельную нумерацию документов
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE TRIGGER next_ext_id BEFORE INSERT ON payments FOR EACH ROW BEGIN DECLARE new_id INTEGER; SET new_id='1C:1'; IF NEW.ext_id = '' THEN SELECT CAST(SUBSTRING_INDEX(ext_id, ':', -1) AS UNSIGNED)+1 INTO new_id FROM payments WHERE ext_id LIKE '1C%' and date_format(date, '%Y-%m-%d')=curdate() ORDER BY 1 DESC LIMIT 1; SET NEW.ext_id=CONCAT('1C:', new_id); END IF; END; // DELIMITER ; |
Функция создания квитанции для выбранных счетов
Функция создания квитанции для выбранных счетов.
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE FUNCTION add_docs (new_date datetime, admin_id int, new_uid INT, new_payment_id INT, payment_sum DOUBLE(10,2), new_dsc VARCHAR(50)) RETURNS INT DETERMINISTIC BEGIN DECLARE doc_id INT; DECLARE next_invoice_id INT; DECLARE exists_doc INT; DECLARE next_acct_id INT; SELECT count(*) INTO exists_doc FROM docs_acct WHERE payment_id=new_payment_id; IF exists_doc > 0 THEN RETURN 0; END IF; SELECT if(max(acct_id) IS NULL, 0, max(acct_id))+1 INTO next_acct_id FROM docs_acct WHERE DATE_FORMAT(date, '%Y')=DATE_FORMAT(curdate(), '%Y'); INSERT INTO docs_acct (acct_id, date, created, customer, phone, aid, uid, payment_id) VALUES (next_acct_id, new_date, now(), '', '', admin_id, new_uid, new_payment_id); SET doc_id = LAST_INSERT_ID(); INSERT INTO docs_acct_orders (acct_id, orders, counts, unit, price) VALUES (doc_id, new_dsc, 1, 0, payment_sum); SELECT count(*) INTO exists_doc FROM docs_invoice WHERE payment_id=new_payment_id; IF exists_doc > 0 THEN RETURN 0; END IF; SELECT if(max(invoice_id)is NULL, 0, max(invoice_id))+1 INTO next_invoice_id FROM docs_invoice WHERE DATE_FORMAT(date, '%Y')=DATE_FORMAT(curdate(), '%Y'); INSERT INTO docs_invoice (invoice_id, date, created, customer, phone, aid, uid, payment_id) values (next_invoice_id, new_date, now(), '', '', admin_id, new_uid, new_payment_id); SET doc_id = LAST_INSERT_ID(); INSERT INTO docs_invoice_orders (invoice_id, orders, counts, unit, price) values (doc_id, new_dsc, 1, 0, payment_sum); RETURN 1; END // DELIMITER ; |
Создание почтовых ящиков для всех абонентов
При создании ящика пароль берётся из логина абонента, домен по умолчанию 1. Реально ящик появится на диске после отправки пользователю первого письма.
Code Block | ||
---|---|---|
| ||
INSERT INTO mail_boxes (uid, username, password, domain_id, descr, create_date) SELECT uid, id, ENCODE(DECODE(password, 'test12345678901234567890'), 'test12345678901234567890'), 1, 'User mailbox', now() FROM users |
Изменение секретного слова шифрования паролей
Секретный ключ сохраняется в переменной $conf{secretkey} конфигурационного файла config.pl
OLD_SECRET_KEY | Старый ключ |
---|---|
NEW_SECRET_KEY | Новый ключ |
Пользователи:
Code Block | ||
---|---|---|
| ||
UPDATE users SET password=ENCODE(DECODE(password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY'); |
Администраторы:
Code Block | ||
---|---|---|
| ||
UPDATE admins SET password=ENCODE(DECODE(password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY'); |
Сервера доступа:
Code Block | ||
---|---|---|
| ||
UPDATE nas SET mng_password=ENCODE(DECODE(mng_password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY'); |
Почтовые ящики:
Code Block | ||
---|---|---|
| ||
UPDATE mail_boxes SET password=ENCODE(DECODE(password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY'); |
Карточная платформа:
Code Block | ||
---|---|---|
| ||
UPDATE cards_users SET pin=ENCODE(DECODE(pin, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY'); |
Если используется модуль sql для FreeRadius, нужно также поменять секретный ключ в конце файла /usr/local/etc/raddb/sql.conf
Изменение логина абонента
Code Block | ||
---|---|---|
| ||
update users set id='new_login' where id='old_Login' |
Автоматическое создание логинов и контрактов идентичным UID
В config.pl установить переменную проверки логинов в
Code Block | ||
---|---|---|
| ||
$conf{USERNAMEREGEXP}=".{0,20}"; |
параметр `TABLE_SCHEMA`='abills' должен соответствовать названию базы в параметре $conf{dbname}
MYSQL тригер
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE TRIGGER login_id BEFORE INSERT ON users FOR EACH ROW BEGIN DECLARE user_id INT; IF NEW.id = '' THEN SELECT `AUTO_INCREMENT` INTO user_id FROM `information_schema`.TABLES WHERE `TABLE_SCHEMA`='abills' AND `TABLE_NAME`='users'; SET NEW.id=CONCAT(if(@login_prefix IS NOT NULL, @login_prefix, ''), user_id); END IF; END; // DELIMITER ; DELIMITER // CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi FOR EACH ROW BEGIN IF NEW.contract_id = '' THEN SET NEW.contract_id=NEW.uid; END IF; IF or NEW.contract_date = '0000-00-00' THEN SET NEW.contract_date=curdate(); END IF; END; // DELIMITER ; |
Триггер автозамены логина на денежный счёт при создании нового пользователя
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE TRIGGER login_replace AFTER INSERT ON bills FOR EACH ROW BEGIN UPDATE users SET users.id = NEW.id WHERE users.uid = NEW.uid; END // DELIMITER ; |
Удаление нуля как первого символа пин на карте пополнения
Проверка перекодировки
Code Block | ||
---|---|---|
| ||
SELECT id, serial, number, DECODE(pin, 'test12345678901234567890') as pin , if (DECODE(pin, 'test12345678901234567890') REGEXP '^0' =1, SUBSTRING(DECODE(pin, 'test12345678901234567890') FROM 2), DECODE(pin, 'test12345678901234567890')) AS result, ENCODE( if (DECODE(pin, 'test12345678901234567890') REGEXP '^0' =1, SUBSTRING(DECODE(pin, 'test12345678901234567890') FROM 2), DECODE(pin, 'test12345678901234567890')), 'test12345678901234567890' ) AS hex_result FROM cards_users WHERE serial = 'seria' LIMIT 100; |
если все нормально
перекодировка
Code Block | ||
---|---|---|
| ||
UPDATE cards_users SET pin=ENCODE( if (DECODE(pin, 'test12345678901234567890') REGEXP '^0' =1, SUBSTRING(DECODE(pin, 'test12345678901234567890') FROM 2), DECODE(pin, 'test12345678901234567890')), 'test12345678901234567890' ) WHERE serial = 'seria' |
Создание логинов с UID определённого диапазона
Создание логинов в диапазоне ниже 100 000 Ghb при наличии свободных UID и существовании UID выше 100 000
Code Block | ||
---|---|---|
| ||
DELIMITER // CREATE TRIGGER create_uid BEFORE INSERT ON users FOR EACH ROW BEGIN DECLARE new_uid INTEGER; IF NEW.uid = '' THEN SET new_uid=1; SELECT uid+1 INTO new_uid FROM users WHERE uid < 100000 ORDER BY uid DESC LIMIT 1; SET NEW.uid=new_uid; END IF; END // DELIMITER ; |
Изменение тарифного плана абонентам с определённой группой
Code Block | ||
---|---|---|
| ||
UPDATE dv_main set tp_id=225 WHERE uid IN (SELECT uid from users WHERE gid=123); |
Конвертация денежной единицы для абонентов (смена основной валюты или девальвация)
Конвертация списку/группе абонентов депозита по указанному курсу. для абонентов без компаний.
Code Block language bash UPDATE bills b, users u, companies c SET b.deposit=b.deposit * [ курс ] WHERE b.id=u.bill_id AND u.gid IN ([ группа ], [группа])
для абонентов в компаниях
Code Block language bash UPDATE bills b, users u, companies company SET b.deposit=b.deposit * [ курс ] WHERE u.company_id=company.id AND company.bill_id=b.id AND u.gid IN ([ группа ])
Конвертация списку/группе абонентов кредита по указанному курсу.
Code Block language bash UPDATE users u SET u.credit=u.credit * [ курс ] WHERE u.gid IN ([ номера групп ])
- Определенному списку/группе тарифных планов. Конвертирует стоимость в тарифных планах (ежедневные и ежемесячные снятия, изменение ТП и т.п.) по указанному курсу.
для ТПCode Block language bash UPDATE tarif_plans tp SET tp.day_fee=tp.day_fee * [ курс ], tp.month_fee=tp.month_fee * [ курс ], tp.activate_price=tp.activate_price * [ курс ], tp.change_price=tp.change_price * [ курс ] WHERE tp.id IN ([ номера тарифных планов ])
Группы тарифных планов
Code Block language bash UPDATE tarif_plans tp, tp_groups tp_g SET tp.day_fee=tp.day_fee * [ курс ], tp.month_fee=tp.month_fee * [ курс ], tp.activate_price=tp.activate_price * [ курс ], tp.change_price=tp.change_price * [ курс ] WHERE tp.gid=tp_g.id AND tp_g.id IN ([ номера групп тарифных планов ])
Объединение 2 ABillS систем
Создаём базу abills2 и заливаем туда данные с дополнительной биллинговой системы.
Увеличиваем uid, billd_id, company_id 100000 чтобы не было конфликтов в новой системе.
Заливаем данные в основную систему.
Code Block language bash CREATE DATABASE abills2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; #Заливаем данные с дампа use abills2; UPDATE users SET uid=uid+100000, bill_id=bill_id+100000, company_id=company_id+10000; UPDATE companies SET id=id+10000, bill_id=bill_id+100000; UPDATE users_pi SET uid=uid+100000; UPDATE users_contacts SET uid=uid+100000; UPDATE bills SET uid=uid+100000, id=id+100000; UPDATE payments SET uid=uid+100000, bill_id=bill_id+100000, id=id+10000000, aid=aid+10000; UPDATE fees SET uid=uid+100000, bill_id=bill_id+100000, id=id+10000000, aid=aid+10000; UPDATE dv_main SET uid=uid+100000; UPDATE dv_log SET uid=uid+100000; UPDATE internet_main SET uid=uid+100000; UPDATE internet_log SET uid=uid+100000; UPDATE admin_actions SET uid=uid+100000, id=id+1000000, aid=aid+10000; UPDATE admins SET aid=aid+1000;
Code Block language bash use abills; INSERT INTO users SELECT * from abills2.users; INSERT INTO companies SELECT * from abills2.companies; INSERT INTO users_pi SELECT * from abills2.users_pi; INSERT INTO users_contacts SELECT * from abills2.users_contacts; INSERT INTO bills SELECT * from abills2.bills; INSERT INTO payments SELECT * from abills2.payments; INSERT INTO fees SELECT * from abills2.fees; INSERT INTO dv_main SELECT * from abills2.dv_main; INSERT INTO dv_log SELECT * from abills2.dv_log; INSERT INTO internet_main SELECT * from abills2.internet_main; INSERT INTO internet_log SELECT * from abills2.internet_log; INSERT INTO admin_actions SELECT * from abills2.admin_actions;
Приведение номеров телефонов к общему формату
Перевести номера +38050xxxxxx в 050xxxxxx
Code Block | ||
---|---|---|
| ||
UPDATE users_pi SET phone=REPLACE(phone, '+38050', '050') WHERE phone like '+38050%' |
Удаление непривязанных к абонентам записей
Удаляет непривязанные к абонентам записей в базе
Code Block |
---|
DELETE i FROM internet_main AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM internet_log AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM iptv_main AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM msgs_messages AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM msgs_reply AS i LEFT JOIN msgs_messages AS m ON i.main_msg=m.uid LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM admin_actions AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM docs_invoice2payments AS i LEFT JOIN payments AS p ON i.payment_id=p.id LEFT JOIN users AS u ON p.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM docs_invoice2payments AS i LEFT JOIN docs_invoices AS p ON i.invoice_id=p.id LEFT JOIN users AS u ON p.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM docs_invoice_orders AS i LEFT JOIN docs_invoices AS d ON d.uid=i.invoice_id LEFT JOIN users AS u ON d.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM payments AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM docs_invoices AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM docs_receipt_orders AS i LEFT JOIN docs_receipts AS r ON i.receipt_id=r.id LEFT JOIN users AS u ON r.uid=u.uid WHERE u.uid IS NULL; DELETE i FROM docs_receipts AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE internet_online FROM internet_online LEFT JOIN users ON (users.uid=internet_online.uid) WHERE users.uid IS null; DELETE i FROM fees AS i LEFT JOIN users AS u ON i.uid=u.uid WHERE u.uid IS NULL; DELETE streets FROM streets LEFT JOIN districts on (districts.id=streets.district_id) WHERE districts.id IS NULL; |
Массовое изменения пароля абонентов через их данные
За пример возьмём изменение паролей абонентов по номеру их телефона, и его соответствие шаблону (0987654321)
Code Block | ||
---|---|---|
| ||
UPDATE users INNER JOIN users_pi ON users.uid = users_pi.uid SET password = ENCODE(LEFT(phone, 10), 'test12345678901234567890') WHERE LEFT(phone, 10) REGEXP '^-?[0-9]+$' AND LENGTH(LEFT(phone, 10)) = 10 AND LEFT(phone, 1) = '0'; |