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