def update_abon_onyma(file_list):
#
# Функция запуска обработки файлов с сессиями и обновления данных о портах
#
sessions = parsing_update_abon_onyma(file_list)
connect = MySQLdb.connect(host=Settings.db_host, user=Settings.db_user, password=Settings.db_password, db=Settings.db_name, charset='utf8')
cursor = connect.cursor()
current_ports = get_current_ports()
command = "UPDATE IGNORE abon_onyma SET hostname = %s, board = %s, port = %s, mac_address = %s, datetime = %s WHERE account_name = %s"
params = []
for session in sessions:
if session not in current_ports:
continue
if (sessions[session].hostname != current_ports[session]['hostname']) or (sessions[session].board != current_ports[session]['board']) or (sessions[session].port != current_ports[session]['port']):
params.append((sessions[session].hostname, sessions[session].board, sessions[session].port, sessions[session].mac_address, sessions[session].dtime.strftime('%Y-%m-%d %H:%M:%S'), session))
print('Обновление тех. данных об абонентах в таблице abon_onyma...')
SQL.modify_table_many(cursor, command, params)
connect.close()
def run(host):
connect = MySQLdb.connect(host=Settings.db_host,
user=Settings.db_user,
password=Settings.db_password,
db=Settings.db_name,
charset='utf8')
cursor = connect.cursor()
dslam = connect_dslam(host)
if dslam is None:
return (0, host)
command = "INSERT IGNORE INTO data_dsl (hostname, board, port, up_snr, dw_snr, up_att, dw_att, max_up_rate, max_dw_rate, up_rate, dw_rate, datetime) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
params = []
hostname = dslam.get_info()['hostname']
ip = dslam.get_info()['ip']
for board in dslam.boards:
current_time = datetime.datetime.now()
paramConnectBoard = dslam.get_line_operation_board(board)
if not paramConnectBoard:
continue
for port in range(0, dslam.ports):
connect_param = paramConnectBoard[port]
if connect_param['up_snr'] == '-':
param = (hostname, board, port, None, None, None, None, None,
None, None, None,
current_time.strftime('%Y-%m-%d %H:%M:%S'))
else:
param = (hostname, board, port, connect_param['up_snr'],
connect_param['dw_snr'], connect_param['up_att'],
connect_param['dw_att'], connect_param['max_up_rate'],
connect_param['max_dw_rate'],
connect_param['up_rate'], connect_param['dw_rate'],
current_time.strftime('%Y-%m-%d %H:%M:%S'))
params.append(param)
SQL.modify_table_many(cursor, command, params)
connect.close()
del dslam
return (1, host)
def run(host):
#
# Обработка DSLAM и запись данных в базу
#
connect = MySQLdb.connect(host=Settings.db_host,
user=Settings.db_user,
password=Settings.db_password,
db=Settings.db_name,
charset='utf8')
cursor = connect.cursor()
dslam = connect_dslam(host)
if dslam is None:
return (0, host)
command = "INSERT IGNORE INTO data_profiles (hostname, board, port, profile_name, up_limit, dw_limit) VALUES (%s, %s, %s, %s, %s, %s)"
params = []
hostname = dslam.get_info()['hostname']
ip = dslam.get_info()['ip']
if dslam.adsl_line_profile == {}:
print('{}({}) не удалось получить список профайлов'.format(
hostname, ip))
for board in dslam.boards:
ports = dslam.get_adsl_line_profile_board(board)
if not ports:
continue
for port, idx_profile in enumerate(ports):
if dslam.adsl_line_profile.get(idx_profile) is None:
continue
params.append(
(hostname, board, port,
dslam.adsl_line_profile[idx_profile]['profile_name'],
dslam.adsl_line_profile[idx_profile]['up_rate'],
dslam.adsl_line_profile[idx_profile]['dw_rate']))
#print('Занесение данных об профайлах DSLAM {} в таблицу data_profiles'.format(hostname))
SQL.modify_table_many(cursor, command, params)
connect.close()
del dslam
return (1, host)
def parsing_make_abon_argus(file_list):
#
# Функция обработки файлов в папке argus и занесения данных в базу
#
connect = MySQLdb.connect(host=Settings.db_host, user=Settings.db_user, password=Settings.db_password, db=Settings.db_name, charset='utf8')
cursor = connect.cursor()
models = ['Huawei MA 5616', 'Huawei MA 5600']
# Подготовка регулярного выражения
re_phone = re.compile(r'\((\d+)\)(.+)') # Код, телефон
command = "INSERT IGNORE INTO abon_argus (phone_number, area, locality, street, house_number, apartment_number, port) VALUES (%s, %s, %s, %s, %s, %s, %s)"
params = []
# Выбор типа отчета
if Settings.argus_type == 1:
report_len = 10
num_model = 1
num_board = 4
num_address = 6
num_phone = 9
elif Settings.argus_type == 2:
report_len = 11
num_model = 2
num_hostname = 3
num_board = 5
num_port = 6
num_address = 7
num_phone = 10
else:
print('Неизвестный тип отчета АРГУС')
return
# Обработка csv-файлов
for file in file_list:
if file.split('.')[-1] != 'csv':
continue
print('Обработка файла {}'.format(file))
with open(file, encoding='windows-1251') as f:
reader = csv.reader(f, delimiter=';')
for row in reader:
if len(row) < report_len:
continue
cell_model = row[num_model].replace('=', '').replace('"', '')
if cell_model not in models or not re.search(r'ADSL.+\(Л\)', row[num_board]):
continue
cell_phone = row[num_phone].replace('=', '').replace('"', '')
cell_address = row[num_address].replace('=', '').replace('"', '')
if not re_phone.search(cell_phone) or cell_address == '':
continue
try:
area_code = re_phone.search(cell_phone).group(1) # код телефона
phone = re_phone.search(cell_phone).group(2) # телефон
phone_number = '{}{}'.format(area_code, phone).replace('ПППП', 'ПП') # полный номер (код+телефон)
if re.search(r'.*р-н', cell_address): # в адресе есть район
area = re.search(r'.*р-н', cell_address).group(0) # район
locality = re.search(r'р-н, (.*?),', cell_address).group(1) # нас. пункт
elif re.search(r'.+\sг\.,\s+(.+\s(?:п|г|с|х|ст-ца|аул)?\.?),', cell_address): # в адресе есть город, затем еще город, село, поселок, хутор и т.д.
area = re.search(r'^(.+\sг\.),', cell_address).group(1) # район
locality = re.search(r'.+\sг\.,\s+(.+\s(?:п|г|с|х|ст-ца|аул)?\.?),', cell_address).group(1) # нас. пункт
elif re.search(r'^(.+\sг\.),', cell_address): # адрес начинается с города
area = re.search(r'^(.+\sг\.),', cell_address).group(1) # район
locality = area # нас. пункт
street = re.search(r'(?:.+(?:п|г|с|х|ст-ца|аул|аул)?\.?),\s+(.+?),\s+(?:.+),\s?кв\.', cell_address).group(1) # улица
house_number = re.search(r'(\S+?)\s*,кв', cell_address).group(1) # дом
apartment_number = re.search(r'кв.\s?(.*)', cell_address).group(1) # квартира
port = '{}-{}-{}'.format(row[num_hostname].strip().replace('=', '').replace('"', ''), re.search(r'\(Л\)\s+?-\s+?(.+)', row[num_board].replace('=', '').replace('"', '')).group(1), row[num_port].strip().replace('=', '').replace('"', ''))
except Exception as ex:
#print('-------------------------------')
#print(ex)
#print(cell_address)
#print(cell_phone)
continue
#print( '{}, {}, {}, {}, {}, {}, {}, {}, {}'.format(phone_number, area, locality, street, house_number, apartment_number))
## Вставка данных в таблицу
if len(phone_number) > 10:
continue
params.append((phone_number, area, locality, street, house_number, apartment_number, port))
print('Занесение данных об абонентах в таблицу abon_argus...')
SQL.modify_table_many(cursor, command, params)
connect.close()
def parsing_make_abon_onyma(file_list):
#
# Функция обработки файла 'Список подключений ШПД + ТВ' и занесения данных в базу
#
connect = MySQLdb.connect(host=Settings.db_host, user=Settings.db_user, password=Settings.db_password, db=Settings.db_name, charset='utf8')
cursor = connect.cursor()
phones = {} # добавить описание формата
tv = [] # Список лицевых счетов с IPTV
# Чтение информации из файлов
for file in file_list:
if (file.split('.')[-1] != 'csv') or ('Список подключений ШПД + ТВ' not in file):
continue
print('Обработка файла {}'.format(file))
with open(file, encoding='windows-1251') as f:
reader = csv.reader(f, delimiter=';')
for row in reader:
if (row[43] != 'deleted') and (re.search(r'[xA]DSL', row[40])):
area_code = get_area_code(row[1])
if area_code is False:
continue
phone_cell = row[7].replace(' ', '').replace('-', '')
if (len(phone_cell) == 10) and (area_code in phone_cell):
phone_number = phone_cell
elif (len(phone_cell) < 10) and (len(phone_cell) > 0):
phone_number = '{}{}'.format(area_code, phone_cell)
if len(phone_number) > 10:
phone_number = '-'
else:
phone_number = '-'
if row[24] == 'SSG-подключение':
# Определение учетного имени
account_name = row[22]
speed = define_speed(row[27])
if phone_number not in phones:
phones[phone_number] = []
phones[phone_number].append({'account_name': account_name, 'tariff_name': row[27].replace('"', "'").replace(';', " "), 'tariff_speed': speed, 'address': row[6].replace('"', "'").replace(';', " "), 'servis_point': row[1], 'contract': row[3], 'name': row[5].replace('"', "'").replace(';', " ")})
elif row[24] == '[ЮТК] Сервис IPTV':
tv.append(row[3])
# Удаляю обработанный файл (так как нужен список, передаю список)
delete_files([file])
# Занесение в базу данных
command = "INSERT IGNORE INTO abon_onyma (account_name, phone_number, contract, servis_point, address, tariff_name, tariff_speed, name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
params = []
for insert_phone in phones:
for account in phones[insert_phone]:
servis_point = account['servis_point']
contract = account['contract']
account_name = account['account_name']
tariff_name = account['tariff_name']
tariff_speed = account['tariff_speed']
address = account['address']
name = account['name']
if len(phones[insert_phone]) == 1:
params.append((account_name, insert_phone, contract, servis_point, address, tariff_name, tariff_speed, name))
else:
params.append((account_name, None, contract, servis_point, address, tariff_name, tariff_speed, name))
print('Занесение данных об абонентах в таблицу abon_onyma...')
SQL.modify_table_many(cursor, command, params)
command = "UPDATE IGNORE abon_onyma SET tv = 'yes' WHERE contract = %s"
params = []
for contract in tv:
params.append((contract, ))
print('Занесение данных об IPTV в таблицу abon_onyma...')
SQL.modify_table_many(cursor, command, params)
connect.close()