def run_define_param(account_list):
count_processed = 0
connect = MySQLdb.connect(host=Settings.db_host, user=Settings.db_user, password=Settings.db_password, db=Settings.db_name, charset='utf8')
cursor = connect.cursor()
onyma = Onyma.get_onyma()
if onyma is None:
return count_processed
for account in account_list:
account_name = account[0]
account_param = Onyma.find_account_param(onyma, account_name)
if account_param is False:
continue
elif account_param == -1:
onyma = Onyma.get_onyma()
if onyma is None:
return count_processed
continue
else:
bill, dmid, tmid = account_param
count_processed += 1
options = {'cursor': cursor,
'table_name': 'abon_onyma',
'str1': 'account_name, bill, dmid, tmid',
'str2': '"{}", "{}", "{}", "{}"'.format(account_name, bill, dmid, tmid)}
SQL.insert_table(**options)
connect.close()
del onyma
return count_processed
def main():
dslam_ok = 0
dslam_bad = []
# Создание таблицы(если еще нет)
SQL.create_data_dsl()
# Запуск основного кода
current_time = datetime.datetime.now()
arguments = [(current_time, host) for host in Settings.hosts]
with ThreadPoolExecutor(max_workers=Settings.threads) as executor:
results = executor.map(run, arguments)
for result in results:
if result is None:
continue
elif result[0] == 1:
dslam_ok += 1
else:
dslam_bad.append(result[1])
print('Время: {}'.format(current_time.strftime('%Y-%m-%d %H:%M')))
print('Всего DSLAM: {}'.format(len(Settings.hosts)))
print('Обработано: {}'.format(dslam_ok))
print('Необработанные: {}'.format(', '.join(dslam_bad)))
print('---------\n')
# Удаление старых записей
options = {
'table_name':
'data_dsl',
'str1':
'CAST(datetime AS DATE) < DATE_ADD(CURRENT_DATE(), INTERVAL -{} DAY)'.
format(Settings.days)
}
SQL.delete_table(**options)
def make_abon_argus(file_list):
#
# Функция формирования таблицы abon_argus
#
# Пересоздаем таблицу
SQL.create_abon_argus(drop=True)
parsing_make_abon_argus(file_list)
delete_files(file_list)
def argus_files(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()
# Подготовка регулярных выражений
re_phone = re.compile(r'\((\d+)\)(.+)') # Код, телефон
re_address = re.compile(r'(.*),\s?(.*),\s?(.*),\s?(.*),\s?кв\.(.*)') # Район, нас. пункт, улица, дом, кв.
re_board = re.compile(r'.+0.(\d+)') # Board
re_onyma = re.compile(r'.+Onyma\s*(\d+)') # Onyma id
# Обработка 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) < 8:
continue
cell_hostname = row[2].replace('=', '').replace('"', '')
cell_board = row[4].replace('=', '').replace('"', '')
cell_port = row[5].replace('=', '').replace('"', '')
cell_phone = row[8].replace('=', '').replace('"', '')
cell_address = row[10].replace('=', '').replace('"', '')
cell_onyma = row[12].replace('=', '').replace('"', '')
cell_type = row[7].replace('=', '').replace('"', '')
if cell_type not in ('Телефон', 'Прямой провод') or not re_phone.search(cell_phone) or not re_address.search(cell_address):
continue
hostname = '"{}"'.format(cell_hostname) # hostname
board = re_board.search(cell_board).group(1) # board
port = cell_port # port
area_code = re_phone.search(cell_phone).group(1) # код телефона
phone = re_phone.search(cell_phone).group(2) # телефон
phone_number = '"{}{}"'.format(area_code, phone).replace('ПППП', 'ПП') # полный номер (код+телефон)
area = '"{}"'.format(re_address.search(cell_address).group(1)) # район
locality = '"{}"'.format(re_address.search(cell_address).group(2)) # нас. пункт
street = '"{}"'.format(re_address.search(cell_address).group(3)) # улица
house_number = '"{}"'.format(re_address.search(cell_address).group(4)) # номер дома
apartment_number = '"{}"'.format(re_address.search(cell_address).group(5)) # квартира
try:
onyma_equ = re_onyma.search(cell_onyma).group(1) # onyma equ
except:
onyma_equ = ''
# Вставка данных в таблицу
options = {'cursor': cursor,
'table_name': 'abon_dsl',
'str1': 'phone_number, area, locality, street, house_number, apartment_number, hostname, board, port',
'str2': '{}, {}, {}, {}, {}, {}, {}, {}, {}'.format(phone_number, area, locality, street, house_number, apartment_number, hostname, board, port)}
try:
SQL.insert_table(**options)
except:
continue
argus_phone[onyma_equ] = phone_number
connect.close()
def main():
#
# Запуск программы
#
print('Время запуска: {}'.format(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M')))
# Загрузка списка DSLAM
dslams = load_dslams()
if len(dslams) == 0:
print('Не найден dslams.db!')
return
# Обнуление таблицы
SQL.create_data_profiles(drop=True)
dslam_ok = 0
dslam_repeat = []
dslam_bad = []
# Создание таблицы(если еще нет)
SQL.create_data_dsl()
# Запуск основного кода
with ThreadPoolExecutor(max_workers=Settings.threads) as executor:
results = executor.map(run, dslams)
for result in results:
if result is None:
continue
elif result[0] == 1:
dslam_ok += 1
else:
dslam_repeat.append(result[1])
if len(dslam_repeat) > 0:
print('Пауза 5 мин, и повторная обработка DSLAM:')
for dslam in dslam_repeat:
print(dslam[0])
print()
# Задержка
time.sleep(60 * 5)
# Повторная обработка DSLAM
with ThreadPoolExecutor(max_workers=Settings.threads) as executor:
results = executor.map(run, dslam_repeat)
for result in results:
if result is None:
continue
elif result[0] == 1:
dslam_ok += 1
else:
dslam_bad.append(result[1][0])
# Распечатка результатов
print('Время окончания: {}'.format(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M')))
print('Всего DSLAM: {}'.format(len(dslams)))
print('Обработано: {}'.format(dslam_ok))
print('Необработанные: {}'.format(', '.join(dslam_bad)))
print('---------\n')
def run(arguments):
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_time = arguments[0]
host = arguments[1]
dslam = connect_dslam(host)
if dslam is None:
return (0, host[0])
hostname = dslam.get_info()['hostname']
ip = dslam.get_info()['ip']
for board in dslam.boards:
paramConnectBoard = dslam.get_line_operation_board(board)
if paramConnectBoard == False:
continue
for port in range(0, dslam.ports):
param = paramConnectBoard[port]
if param['up_snr'] == '-':
options = {
'cursor':
cursor,
'table_name':
'data_dsl',
'str1':
'hostname, board, port, datetime',
'str2':
'"{}", {}, {}, "{}"'.format(
hostname, board, port,
current_time.strftime('%Y-%m-%d %H:%M:%S'))
}
SQL.insert_table(**options)
continue
options = {
'cursor':
cursor,
'table_name':
'data_dsl',
'str1':
'hostname, board, port, up_snr, dw_snr, up_att, dw_att, max_up_rate, max_dw_rate, up_rate, dw_rate, datetime',
'str2':
'"{}", {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, "{}"'.format(
hostname, board, port, param['up_snr'], param['dw_snr'],
param['up_att'], param['dw_att'], param['max_up_rate'],
param['max_dw_rate'], param['up_rate'], param['dw_rate'],
current_time.strftime('%Y-%m-%d %H:%M:%S'))
}
SQL.insert_table(**options)
connect.close()
del dslam
return (1, host[0])
def onyma_file(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()
onyma = True
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 (row[41] != 'deleted') and (re.search(r'[xA]DSL', row[37])) and (row[23] == 'SSG-подключение'):
area_code = get_area_code(row[1])
if area_code is False:
continue
if (len(row[7]) == 10) or (area_code in row[7]):
phone_number = '"{}"'.format(row[7])
elif (len(row[7]) < 10) and (len(row[7]) > 0):
phone_number = '"{}{}"'.format(area_code, row[7]).replace('-', '')
else:
continue
# Определение учетного имени
account_name = '"{}"'.format(row[21])
onyma_id = row[19]
if phone_number not in phones:
phones[phone_number] = []
phones[phone_number].append((account_name, onyma_id))
for phone in phones:
if len(phones[phone]) == 1:
options = {'cursor': cursor,
'table_name': 'abon_dsl',
'str1': 'account_name = {}'.format(phones[phone][0][0]),
'str2': 'phone_number = {}'.format(phone)}
SQL.update_table(**options)
else:
if onyma is True:
find_phones = find_phone_account(phones[phone])
if find_phones is None:
onyma = False
continue
else:
continue
for find_phone in find_phones:
options = {'cursor': cursor,
'table_name': 'abon_dsl',
'str1': 'account_name = {}'.format(find_phone[0]),
'str2': 'phone_number = {}'.format(find_phone[1])}
SQL.update_table(**options)
connect.close()
def make_abon_onyma(file_list):
#
# Функция формирования таблицы abon_onyma
#
# Пересоздаем таблицу
SQL.create_abon_onyma(drop=True)
# Обнуляем список файлов с сессиями
with open('resources{}session_files.db'.format(os.sep), 'bw') as file_dump:
pickle.dump([], file_dump)
# Заполнение таблицы из отчета Онимы 'Список подключений ШПД + ТВ'
parsing_make_abon_onyma(file_list)
# Заполнение данных о портах из отчета Онимы 'Абонентские сессии'
update_abon_onyma(file_list)
def main():
# Просмотр файлов в директории in/argus/
argus_file_list = ['in' + os.sep + 'argus' + os.sep + x for x in os.listdir('in' + os.sep + 'argus')]
# Просмотр файлов в директории in/onyma/
onyma_file_list = ['in' + os.sep + 'onyma' + os.sep + x for x in os.listdir('in' + os.sep + 'onyma')]
if len(argus_file_list) == 0 or len(onyma_file_list) == 0:
return
print("Начало работы: {}".format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
SQL.create_abon_dsl(drop=True)
SQL.create_abon_onyma(drop=True)
# Обработка файлов в директории in/argus/
argus_files(argus_file_list)
delete_files(argus_file_list)
# Обработка файлов в директории in/onyma/
onyma_file(onyma_file_list)
delete_files(onyma_file_list)
# Заполнение полей bill, dmid, tmid таблицы abon_onyma
options = {'table_name': 'abon_dsl',
'str1': 'account_name',
'str2': 'account_name IS NOT NULL'}
account_list = SQL.get_table_data(**options)
if len(account_list) == 0:
print('\n!!! Не сформирована таблица abon_dsl !!!\n')
return
arguments = [account_list[x::Settings.threads_count] for x in range(0, Settings.threads_count)]
print('Получение данных из Онимы для таблицы abon_onyma...')
with ThreadPoolExecutor(max_workers=Settings.threads_count) as executor:
result = executor.map(run_define_param, arguments)
count = 0
for i in result:
count += i
print('Обработано: {}'.format(count))
# Заполнение тарифов в abon_dsl
print('Получение данных из Онимы для заполнения тарифов...')
with ThreadPoolExecutor(max_workers=Settings.threads_count) as executor:
result = executor.map(run_define_speed, arguments)
count = 0
for i in result:
count += i
print('Обработано: {}'.format(count))
print("Завершение работы: {}".format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
print('---------\n')
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 main():
print('Начало работы: {}'.format(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
count_processed = 0
count_insert = 0
count_update = 0
count_tv = 0
count_tech_data = 0
SQL.create_data_sessions()
SQL.create_abon_onyma()
options = {
'table_name': 'abon_dsl',
'str1': 'account_name, tv, hostname, board, port',
'str2': 'account_name IS NOT NULL'
}
account_list = SQL.get_table_data(**options)
if len(account_list) == 0:
print('\n!!! Необходимо сформировать таблицу abon_dsl !!!\n')
return
onyma_param_list = get_onyma_params()
arguments = [(account_list[x::Settings.threads_count], onyma_param_list)
for x in range(0, Settings.threads_count)]
while True:
try:
arguments.remove(((), onyma_param_list))
except:
break
with ThreadPoolExecutor(max_workers=Settings.threads_count) as executor:
result = executor.map(run, arguments)
for count in result:
count_processed += count[0]
count_insert += count[1]
count_update += count[2]
count_tv += count[3]
count_tech_data += count[4]
print('Обработано: {}'.format(count_processed))
print('Добавлено: {}'.format(count_insert))
print('Обновлено данных Онимы: {}'.format(count_update))
print('Обнаружено ТВ: {}'.format(count_tv))
print('Обновлено тех. данных: {}'.format(count_tech_data))
options = {
'table_name':
'data_sessions',
'str1':
'date < DATE_ADD(CURRENT_DATE(), INTERVAL -{} DAY)'.format(
Settings.days)
}
SQL.delete_table(**options)
print('Завершение работы: {}'.format(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
print('---------\n')
def run_define_speed(account_list):
count_processed = 0
connect = MySQLdb.connect(host=Settings.db_host, user=Settings.db_user, password=Settings.db_password, db=Settings.db_name, charset='utf8')
cursor = connect.cursor()
onyma = Onyma.get_onyma()
if onyma is None:
return count_processed
for account in account_list:
account_name = account[0]
speed = Onyma.find_account_speed(onyma, account_name)
if speed is not False:
options = {'cursor': cursor,
'table_name': 'abon_dsl',
'str1': 'tariff = {}'.format(speed),
'str2': 'account_name = "{}"'.format(account_name)}
SQL.update_table(**options)
count_processed += 1
connect.close()
del onyma
return count_processed
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 get_onyma_params():
options = {
'table_name': 'abon_onyma',
'str1': 'account_name, bill, dmid, tmid'
}
onyma_param = SQL.get_all_table_data(**options)
result = {}
for param in onyma_param:
result[param[0]] = {
'bill': param[1],
'dmid': param[2],
'tmid': param[3]
}
return result
def get_current_ports():
#
# Функция получения текущих значений портов для учетных имен
# возвращает словарь вида {'account_name': {'hostname': '', 'board': '', 'port': ''}}
#
result = {}
# Получение данных из базы данных
options = {'table_name': 'abon_onyma',
'str1': 'account_name, hostname, board, port',
'str2': 'account_name IS NOT NULL'}
accounts = SQL.get_table_data(**options)
for account in accounts:
result[account[0]] = {'hostname': account[1], 'board': account[2], 'port': account[3]}
return result
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_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()
def run(arguments):
count_processed = 0
count_insert = 0
count_update = 0
count_tv = 0
count_tech_data = 0
connect = MySQLdb.connect(host=Settings.db_host,
user=Settings.db_user,
password=Settings.db_password,
db=Settings.db_name,
charset='utf8')
cursor = connect.cursor()
onyma = Onyma.get_onyma()
if onyma is None:
return (count_processed, count_insert, count_update, count_tv,
count_tech_data)
account_list = arguments[0]
onyma_param_list = arguments[1]
prev_day = datetime.date.today() - datetime.timedelta(days=1)
for account in account_list:
account_name = account[0]
account_tv = account[1]
account_hostname = account[2]
account_board = account[3]
account_port = account[4]
if account_name in onyma_param_list:
bill = onyma_param_list[account_name]['bill']
dmid = onyma_param_list[account_name]['dmid']
tmid = onyma_param_list[account_name]['tmid']
else:
onyma_param = Onyma.find_account_param(onyma, account_name)
if onyma_param == -1:
onyma = Onyma.get_onyma()
if onyma is None:
return (count_processed, count_insert, count_update,
count_tv, count_tech_data)
continue
elif onyma_param is False:
count_processed += 1
options = {
'cursor':
cursor,
'table_name':
'data_sessions',
'str1':
'account_name, date, count',
'str2':
'"{}", "{}", {}'.format(account_name,
prev_day.strftime('%Y-%m-%d'), 0)
}
SQL.insert_table(**options)
continue
else:
bill, dmid, tmid = onyma_param
options = {
'cursor':
cursor,
'table_name':
'abon_onyma',
'str1':
'account_name, bill, dmid, tmid'.format(),
'str2':
'"{}", "{}", "{}", "{}"'.format(account_name, bill, dmid,
tmid)
}
SQL.insert_table(**options)
count_insert += 1
data = Onyma.count_sessions(onyma, bill, dmid, tmid, prev_day)
tv = Onyma.update_tv(onyma, bill, prev_day)
if (data == -1) or (tv == -1):
onyma = Onyma.get_onyma()
if onyma is None:
return (count_processed, count_insert, count_update, count_tv,
count_tech_data)
continue
if (tv is True) and (account_tv == 'no'):
options = {
'cursor': cursor,
'table_name': 'abon_dsl',
'str1': 'tv = "yes"',
'str2': 'account_name = "{}"'.format(account_name)
}
SQL.update_table(**options)
count = data['count']
if count == 0:
onyma_param = Onyma.find_account_param(onyma, account_name)
if onyma_param == -1:
onyma = Onyma.get_onyma()
if onyma is None:
return (count_processed, count_insert, count_update,
count_tv, count_tech_data)
continue
elif onyma_param is False:
count_processed += 1
options = {
'cursor':
cursor,
'table_name':
'data_sessions',
'str1':
'account_name, date, count',
'str2':
'"{}", "{}", {}'.format(account_name,
prev_day.strftime('%Y-%m-%d'), 0)
}
SQL.insert_table(**options)
continue
else:
cur_bill, cur_dmid, cur_tmid = onyma_param
if cur_bill != bill or cur_tmid != tmid or cur_dmid != dmid:
options = {
'cursor':
cursor,
'table_name':
'abon_onyma',
'str1':
'bill = "{}", dmid = "{}", tmid = "{}"'.format(
cur_bill, cur_dmid, cur_tmid),
'str2':
'account_name = "{}"'.format(account_name)
}
SQL.update_table(**options)
count_update += 1
data = Onyma.count_sessions(onyma, bill, dmid, tmid, prev_day)
if data == -1:
onyma = Onyma.get_onyma()
if onyma is None:
return (count_processed, count_insert, count_update,
count_tv, count_tech_data)
continue
count = data['count']
if data['hostname'] is not None:
if (data['hostname'] != account_hostname) or (
data['board'] != account_board) or (data['port'] !=
account_port):
options = {
'cursor':
cursor,
'table_name':
'abon_dsl',
'str1':
'hostname = "{}", board = {}, port = {}'.format(
data['hostname'], data['board'], data['port']),
'str2':
'account_name = "{}"'.format(account_name)
}
SQL.update_table(**options)
count_tech_data += 1
#print(account_name, data['hostname'], data['board'], data['port'])
count_processed += 1
options = {
'cursor':
cursor,
'table_name':
'data_sessions',
'str1':
'account_name, date, count',
'str2':
'"{}", "{}", {}'.format(account_name,
prev_day.strftime('%Y-%m-%d'), count)
}
SQL.insert_table(**options)
connect.close()
del onyma
return (count_processed, count_insert, count_update, count_tv,
count_tech_data)
def generate_report_file(incidents):
# Имя файла с отчетом
report_file = 'files{}Край {}.xlsx'.format(
os.sep,
datetime.datetime.now().strftime('%Y-%m-%d'))
# Подключение к MySQL
connect = MySQLdb.connect(host=Settings.db_host,
user=Settings.db_user,
password=Settings.db_password,
db=Settings.db_name,
charset='utf8')
cursor = connect.cursor()
# Получение данных о абонентах из базы данных
accounts_info = SQL.get_accounts_info(cursor)
#for account in accounts_info:
#print(account, accounts_info[account])
# Получение профилей линий с DSLAM
data_profiles = SQL.get_data_profiles(cursor)
#Получение скоростей
all_speed = SQL.get_all_speed(cursor)
# Сортировка инцидентов по дате
incidents = sort_incidents(incidents)
# Добавление информации к инцидентам ADSL
for incident in incidents:
#if incident.area != 'Петровский р-н':
#continue
incident.day_count = (datetime.datetime.now() - incident.end_time).days
if incident.technology == 'по технологии ADSL':
#continue
if incident.account_name in accounts_info:
incident.phone_number = accounts_info[
incident.account_name]['phone_number']
incident.tariff_speed = accounts_info[
incident.account_name]['tariff_speed']
incident.tv = accounts_info[incident.account_name]['tv']
speed = all_speed.get(
'{}/{}/{}'.format(incident.hostname, incident.board,
incident.port), {
'min_speed': '-',
'avg_speed': '-',
'up_snr': '-',
'dw_snr': '-'
})
incident.min_speed = speed['min_speed']
incident.avg_speed = speed['avg_speed']
incident.up_snr = speed['up_snr']
incident.dw_snr = speed['dw_snr']
try:
wb = openpyxl.load_workbook('resources' + os.sep + 'Template.xlsx')
except Exception as ex:
print('Не удалось прочитать файл - {}'.format(file))
print(ex)
else:
sh_adsl = wb['ADSL']
sh_adsl[
'B2'].value = 'Дата формирования отчета {}. Данные в отчете за {}.'.format(
datetime.datetime.now().strftime('%d-%m-%Y'),
(datetime.datetime.now().date() -
datetime.timedelta(days=1)).strftime('%d-%m-%Y'))
sh_fttx = wb['FTTX']
sh_fttx[
'B2'].value = 'Дата формирования отчета {}. Данные в отчете за {}.'.format(
datetime.datetime.now().strftime('%d-%m-%Y'),
(datetime.datetime.now().date() -
datetime.timedelta(days=1)).strftime('%d-%m-%Y'))
# ADSL
fill_report(sh_adsl, incidents[:], 'по технологии ADSL', data_profiles)
fill_report(sh_fttx, incidents[:], 'с использованием FTTx')
wb.save(report_file)
# Закрытие подключения к MySQL
connect.close()
# Копирование файла на сетевой диск
if os.path.exists(report_file):
try:
shutil.copy(
report_file,
'{}{}{}'.format(Settings.path_name, os.sep,
datetime.datetime.now().strftime('%Y-%m')))
except Exception as ex:
print('Ошибка копирования на сетевой диск')
print(ex)
else:
print('Файл скопирован')
else:
print('Файл отчета не создан!')
def get_onyma_params(arguments):
try:
time.sleep(random.randint(10, 180))
count = 0
incidents = arguments[0]
keys = arguments[1]
thread_number = arguments[2]
re_port = re.compile(r'(STV.+?)\[.*?\(Л\)\s+?-\s+?(.+?)-\s?(\d+)')
print('запуск {} потока обработки инцидентов...'.format(thread_number))
# Подключение к MySQL
connect = MySQLdb.connect(host=Settings.db_host,
user=Settings.db_user,
password=Settings.db_password,
db=Settings.db_name,
charset='utf8')
cursor = connect.cursor()
# Открытие соединений Onyma и Argus
onyma = Web.connect_onyma()
argus = Web.connect_argus()
for incident in keys:
count += 1
# Если параметры уже установлены
if incidents[incident].bill is not None:
continue
# Если нет
#print('Поток {}, обработка инцидента {}'.format(thread_number, incident))
params = False
if incidents[incident].account_name:
# Если номер карты есть, но параметры еще не определены
print('Новая попытка получить параметры для {}'.format(
incidents[incident].account_name))
params = Web.find_login_param(
onyma, account_name=incidents[incident].account_name)
if params:
if not params['bill']:
print('Не удалось найти параметры для {}'.format(
incidents[incident].account_name))
else:
if 'СПД' in incidents[
incident].service_number and 'EQU' not in incidents[
incident].service_number:
account_name = re.search(
r'СПД(\S+)',
incidents[incident].service_number).group(1)
params = Web.find_login_param(
onyma, account_name=account_name.lower())
#elif 'IPTV' in incidents[incident].service_number:
#account_name = re.search(r'IPTV(\S+)', incidents[incident].service_number).group(1)
#params = Web.find_login_param(onyma, account_name=account_name.lower())
else:
login = Web.get_login(argus,
incidents[incident].incident_number)
if login:
params = Web.find_login_param(onyma, login=login)
else:
# В комментариях Argus нет логина
try:
port = '{}-{}-{}'.format(
re_port.search(
incidents[incident].ldn).group(1).strip(),
re_port.search(
incidents[incident].ldn).group(2).strip(),
re_port.search(
incidents[incident].ldn).group(3).strip())
except:
# Не удалось распознать порт DSLAM
print(
'В комментариях нет логина, порт DSLAM в линейных данных не распознан ({})'
.format(incident))
continue
phone_number = SQL.get_phone_number(cursor, port)
if phone_number:
account_name = SQL.get_account_name_phone(
cursor, phone_number)
if account_name:
params = Web.find_login_param(
onyma, account_name=account_name)
else:
# Не удалось найти аккаунт по номеру телефона
print(
'Не удалось найти учетное имя по номеру телефона ({})'
.format(incident))
continue
else:
# Не удалось найти номер телефона по порту
print(
'Не удалось найти номер телефона по порту DSLAM ({})'
.format(incident))
continue
if params:
incidents[incident].account_name = params['account_name']
incidents[incident].bill = params['bill']
incidents[incident].dmid = params['dmid']
incidents[incident].tmid = params['tmid']
if params['bill']:
print(
'Найдены параметры для инцидента {}: account_name - {}, bill - {}, dmid - {}, tmid - {}'
.format(incident, params['account_name'],
params['bill'], params['dmid'],
params['tmid']))
else:
print('Найдено имя аккаунта для {}: {}'.format(
incident, params['account_name']))
else:
continue
# Закрытие соединений
connect.close()
onyma.close()
argus.close()
return count
except Exception as ex:
print(ex)
print('В потоке {} обработано {} инцидентов.'.format(
thread_number, count))
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 main():
print('Время запуска: {}'.format(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M')))
# Загрузка списка DSLAM
dslams = load_dslams()
if len(dslams) == 0:
print('Не найден dslams.db!')
return
dslam_ok = 0
dslam_repeat = []
dslam_bad = []
# Создание таблицы(если еще нет)
SQL.create_data_dsl()
# Запуск основного кода
#current_time = datetime.datetime.now()
#arguments = [(current_time, host) for host in dslams]
with ThreadPoolExecutor(max_workers=Settings.threads) as executor:
results = executor.map(run, dslams)
for result in results:
if result is None:
continue
elif result[0] == 1:
dslam_ok += 1
else:
dslam_repeat.append(result[1])
if len(dslam_repeat) > 0:
print('Пауза 5 мин, и повторная обработка DSLAM:')
for dslam in dslam_repeat:
print(dslam[0])
print()
# Задержка
time.sleep(60 * 5)
# Повторная обработка DSLAM
with ThreadPoolExecutor(max_workers=Settings.threads) as executor:
results = executor.map(run, dslam_repeat)
for result in results:
if result is None:
continue
elif result[0] == 1:
dslam_ok += 1
else:
dslam_bad.append(result[1][0])
# Распечатка результатов
print('Время окончания: {}'.format(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M')))
print('Всего DSLAM: {}'.format(len(dslams)))
print('Обработано: {}'.format(dslam_ok))
print('Необработанные: {}'.format(', '.join(dslam_bad)))
print('---------\n')
# Удаление старых записей (раз в день в 0 часов)
hour_now = datetime.datetime.now().hour
#if (hour_now >= 0) and (hour_now < 2):
if hour_now == 0:
options = {
'table_name':
'data_dsl',
'str1':
'CAST(datetime AS DATE) <= DATE_ADD(CURRENT_DATE(), INTERVAL -{} DAY)'
.format(Settings.days)
}
SQL.delete_table(**options)