Биллинговая система Nodeny

#!/usr/bin/env bash

# Func connect to mysql abills
connect_abills () {
mysql -D abills -u root -pPASSWORD -Bse "$1" 2>/dev/null
}

# Func connect to mysql nodeny
connect_nodeny () {
mysql -D nodeny -u root -pPASSWORD -Bse "$1" 2>/dev/null
}

# For new NoDeny+ clients id is (id + 5000)
new_id () {
let add=$1+5000
echo $add
}

# Replace date in mysql format
replace_date () {
connect_nodeny "SELECT UNIX_TIMESTAMP('$1')" 2>/dev/null
}

# Date now in mysql format
time_now () {
connect_nodeny "SELECT UNIX_TIMESTAMP(NOW())" 2>/dev/null
}

uid_enabled="/SCRIPTS/tmp/uid.enabled.txt"
uid_disabled="/SCRIPTS/tmp/uid.disabled.txt"

# Выбор пользователей с активными услугами
connect_abills "SELECT users.uid
       FROM users
       LEFT JOIN users_pi ON (users.uid = users_pi.uid)
       WHERE users.disable=0" > $uid_enabled

# Выбор пользователей, которые уже не получают услуги / отключены физически
connect_abills "SELECT users.uid
       FROM users
       LEFT JOIN users_pi ON (users.uid = users_pi.uid)
       WHERE users.disable=1" > $uid_disabled

# Func выбор улиц из групп abills и сопостовление их с data0 _adr_street в nodeny
select_street () {

STREET=$1

if [ "$STREET" == "Артековская" ]; then
   nd_adr_street="284"
     echo "$nd_adr_street";
elif [ "$STREET" == "Гоголя" ]; then
   nd_adr_street="286"
     echo "$nd_adr_street";
elif [ "$STREET" == "Исполкомовская" ]; then
   nd_adr_street="288"
     echo "$nd_adr_street";
elif [ "$STREET" == "пр. Гагарина" ]; then
   nd_adr_street="306"
     echo "$nd_adr_street";
elif [ "$STREET" == "И т.д. для нужного кол-ва улиц" ]; then
   nd_adr_street="299"
     echo "$nd_adr_street";
   else
   nd_adr_street=""
     echo $nd_adr_street;
fi

}

# Для всех uid которые разрешены 
for nodeny_id in $(cat $uid_enabled)
do

# Table users
nd_id=$(connect_abills "select uid from users where uid=${nodeny_id}")
nd_name=$(connect_abills "select id from users where uid=${nodeny_id}")
nd_passwd=`connect_abills "select decode(password,'secret_key') from users where uid=${nodeny_id}"`
nd_grp="5"  # Отдельная группа и её id 5
nd_contract=$(connect_abills "select contract_id from users_pi where uid=${nodeny_id}")
nd_contract_date=$(connect_abills "select contract_date from users_pi where uid=${nodeny_id}")
nd_new_contract_date="$(replace_date $nd_contract_date)"
nd_state="on"
nd_fio="$(connect_abills "select fio from users_pi where uid=${nodeny_id}")"
nd_balance="$(connect_abills "select deposit from bills where uid=${nodeny_id}")"
nd_time_now=$(time_now)

# Table data0
nd_uid="$(connect_abills "select uid from users_pi where uid=${nodeny_id}")"
tmpl_adr_street="$(connect_abills "select address_street from users_pi where uid=${nodeny_id}")"
nd_adr_house="$(connect_abills "select address_build from users_pi where uid=${nodeny_id}")"
nd_adr_room="$(connect_abills "select address_flat from users_pi where uid=${nodeny_id}")"
nd_adr_telefon="$(connect_abills "select phone from users_pi where uid=${nodeny_id}")"
nd_provider="3" # Отдельный провайдер с id=3 в доп поле
nd_new_street=$(select_street $nd_adr_street)

select_street "$tmpl_adr_street"

# Заполняем таблицы в nodeny.
connect_nodeny "
    INSERT into users SET modify_time=UNIX_TIMESTAMP(),
        id='$(new_id $nd_id)',
        name='$nd_name',
        lstate='0',
        limit_balance='0',
        contract='$nd_contract',
        contract_date='$nd_new_contract_date',
        fio='$nd_fio',
        cstate='6',
        grp='5',
        block_if_limit='1',
        state='on',
        cstate_time='$nd_time_now',
        comment='',
        passwd=AES_ENCRYPT('$nd_passwd', 'password key - hardpass');

    DELETE FROM data0 WHERE uid='$(new_id $nd_id)';
    DELETE FROM users_trf WHERE uid='$(new_id $nd_id)';
    DELETE FROM auth_log WHERE uid='$(new_id $nd_id)';
    DELETE FROM users_services WHERE uid='$(new_id $nd_id)';
    DELETE FROM pays WHERE mid='$(new_id $nd_id)';

    INSERT INTO users_trf SET uid='$(new_id $nd_id)',traf4='0';
    UPDATE users SET name='$(new_id $nd_id)1' WHERE id='$(new_id $nd_id)' LIMIT 1;"

# Заполняем таблицу data0 под свои нужды
connect_nodeny "INSERT into data0 (uid, _adr_street,
                           _adr_house, _adr_room,
                           _adr_telefon, _gorod,
                           _adr_place, Sw_port,
                           _olt_numb, _olt_port ,
                           _onu_id, _vlan_custom,
                           _master_id,_provide,
                           _staic_ip,_qinq_vlan)
                        VALUES ( '$(new_id $nd_uid)', '$nd_adr_street',
                                 '$nd_adr_house', '$nd_adr_room' ,
                                 '$nd_adr_telefon',
                                 '12', '',
                                 '', '25',
                                 'newprov','',
                                 '610','master',
                                 '$nd_provider','','3003');"

# Устанавливаем коментарий что это Transfer from Abills, можно не делать
connect_nodeny "INSERT INTO pays (mid,time,creator,creator_id,reason,comment,category,creator_ip)
      VALUES ( '$(new_id $nd_uid)', $(time_now), 'admin' ,'11', 'Transfer from Abills', 'Transfer from Abills','495','3239309826')"

done