Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

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

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

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

Code Block
languagebash
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 = '' or NEW.contract_date = '0000-00-00' THEN
    SET NEW.contract_date=curdate();
  END IF;

END;
//
DELIMITER ;


Code Block
languagebash
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 = '' or 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 = '' or 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


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

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

Code Block
languagebash
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
languagebash
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
languagebash
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
languagebash
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
languagebash
UPDATE users SET password=ENCODE(DECODE(password, 'OLD_SECRET_KEY'), 'NEW_SECRET_KEY');

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

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

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

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

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

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

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

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

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

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

Code Block
languagebash
update users set id='new_login' where id='old_Login'


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

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

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

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

MYSQL тригер

Code Block
languagebash
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 NEW.contract_date = '' or NEW.contract_date = '0000-00-00' THEN
    SET NEW.contract_date=curdate();
  END IF;

END;  
//
DELIMITER ;


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

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

Code Block
languagebash
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
languagebash
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
languagebash
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
languagebash
UPDATE dv_main set tp_id=225 WHERE uid IN (SELECT uid from users WHERE gid=123);


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

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

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

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

    Code Block
    languagebash
    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. Конвертация списку/группе абонентов кредита по указанному курсу.

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


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



    Code Block
    languagebash
    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
    languagebash
    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. Заливаем данные в основную систему.

    Code Block
    languagebash
    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+1000000, aid=aid+10000;
    UPDATE fees SET uid=uid+100000, bill_id=bill_id+100000, id=id+1000000, 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
    languagebash
    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
languagebash
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;