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)