=====Дополнения для WEB  интерфейса=====

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

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

  DELIMITER //
  CREATE TRIGGER next_contract_id BEFORE INSERT ON users_pi
  FOR EACH ROW BEGIN
    DECLARE new_id INTEGER;
   
    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 ;

  DELIMITER //
  CREATE TRIGGER next_contract_id_change BEFORE UPDATE ON users_pi
  FOR EACH ROW BEGIN
  
    DECLARE new_id INTEGER;
    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 ;

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



  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 тригер

<code>
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>



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

проверка перекодировки
<code>
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>

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

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

<code>
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'
</code>


====Создание логинов с UID определённого диапазона===
создание логинов в диапазоне ниже 100 000 Ghb при наличии свободных UID и существовании UID  выше 100 000

<code>

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>

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

  UPDATE dv_main set tp_id=225 WHERE uid IN (SELECT uid from users WHERE gid=123);
  
  
====Конвертация денежной единицы для абонентов (смена основной валюты или девальвация)====

1.Конвертация списку/группе абонентов депозита по указанному курсу.
для абонентов без компаний 
<code>
UPDATE bills b, users u, companies c 
  SET b.deposit=b.deposit * [ курс ] 
  WHERE b.id=u.bill_id AND u.gid IN ([ группа ], [группа])
</code>

для абонентов в компаниях
<code>
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>

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

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

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

для ТП

<code>
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>

Группы тарифных планов
<code>
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 ([ номера групп тарифных планов ])
</code>
====Объединение 2 ABillS  систем====

Создаём базу abills2 и заливаем туда данные с дополнительной  биллинговой системы
  - увеличиваем  uid, billd_id, company_id 100000  чтобы не было конфликтов в новой системе. 
  - Заливаем данные в основную систему

  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 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 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 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 admin_actions SELECT * from abills2.admin_actions;


====Приведение номеров телефонов к общему формату====

перевести  номера +38050xxxxxx в 050xxxxxx

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