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