Автоматическое заполнение полей "Номер контракта" и "Дата контракта"

Незаполненные поля «Номер контракта» и «Дата контракта» заполняются автоматически системой. Полю «Номер контракта» присваивается следующий порядковый номер, а полю «Дата контракта» текущая дата. 

Для включения данной возможности нужно создать триггер для MySQL (Данная возможность работает начиная с версии MySQL 5.0).

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 ;


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


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, а а дату договора - дате создания абонента:

UPDATE users_pi
LEFT JOIN users
	ON users_pi.uid = users.uid
SET contract_id = users_pi.uid, contract_date = users.registration

Заполнение "Номер контракта" в случайным числом


В триггере переменную max_value изменяем на нужное число

DELIMITER //
CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi
FOR EACH ROW BEGIN
    DECLARE max_value BIGINT UNSIGNED;
    DECLARE new_contract_id BIGINT UNSIGNED;

    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 ;


Автосоздание счета или квитанции при пополнении счёта.

Автосоздание счета или квитанции при пополнении счёта для платежей через терминалы (Для версии 5.xx).

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 для оплаты

Если надо синхронизировать биллинг с внешними программами и вести параллельный учёт автозаполнение поможет вести параллельную нумерацию документов

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 ;


Функция создания квитанции для выбранных счетов

Функция создания квитанции для выбранных счетов.

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. Реально ящик появится на диске после отправки пользователю первого письма.

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Новый ключ


Пользователи:

UPDATE users SET password=ENCODE(DECODE(password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY');

Администраторы:

UPDATE admins SET password=ENCODE(DECODE(password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY');

Сервера доступа:

UPDATE nas SET mng_password=ENCODE(DECODE(mng_password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY');

Почтовые ящики:

UPDATE mail_boxes SET password=ENCODE(DECODE(password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY');

Карточная платформа:

UPDATE cards_users SET pin=ENCODE(DECODE(pin, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY');

Если используется модуль sql для FreeRadius, нужно также поменять секретный ключ в конце файла /usr/local/etc/raddb/sql.conf

Изменение логина абонента

update users set id='new_login' where id='old_Login'


Автоматическое создание логинов и контрактов идентичным UID

В config.pl установить переменную проверки логинов в

$conf{USERNAMEREGEXP}=".{0,20}";

параметр `TABLE_SCHEMA`='abills' должен соответствовать названию базы в параметре $conf{dbname}

MYSQL тригер

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 ;


Триггер автозамены логина на денежный счёт при создании нового пользователя

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 ;


Удаление нуля как первого символа пин на карте пополнения

Проверка перекодировки

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;

если все нормально

перекодировка

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

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 ;


Изменение тарифного плана абонентам с определённой группой

UPDATE dv_main set tp_id=225 WHERE uid IN (SELECT uid from users WHERE gid=123);


Конвертация денежной единицы для абонентов (смена основной валюты или девальвация)

  1. Конвертация списку/группе абонентов депозита по указанному курсу. для абонентов без компаний.

    UPDATE bills b, users u, companies c 
  SET b.deposit=b.deposit * [ курс ] 
  WHERE b.id=u.bill_id AND u.gid IN ([ группа ], [группа])

    для абонентов в компаниях

    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 ([ группа ])


  2. Конвертация списку/группе абонентов кредита по указанному курсу.

    UPDATE users u SET u.credit=u.credit * [ курс ] WHERE u.gid IN ([ номера групп ])


  3. Определенному списку/группе тарифных планов. Конвертирует стоимость в тарифных планах (ежедневные и ежемесячные снятия, изменение ТП и т.п.) по указанному курсу.
    для ТП



    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 ([ номера тарифных планов ])

    Группы тарифных планов



    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 и заливаем туда данные с дополнительной биллинговой системы.

  1. Увеличиваем uid, billd_id, company_id 100000 чтобы не было конфликтов в новой системе.

  2. Заливаем данные в основную систему.

    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;


    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

UPDATE users_pi SET phone=REPLACE(phone, '+38050', '050')
WHERE phone like '+38050%'


Удаление непривязанных к абонентам записей

Удаляет непривязанные к абонентам записей в базе

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)

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';