def get_query_all_agregated_messages_stat(self,
date_start: str,
date_end: str,
type_msg='mt',
group_by_date: bool = False,
group_by_conn: bool = False,
exclude_conn=True):
if not date_start:
date_start = Date().convert_date_to_str(self.date_start)
if not date_end:
date_end = Date().convert_date_to_str(self.date_end)
DAY = ''
CONN = ''
GROUP = ''
EXCLUDE = "(conn!='tgwnew' OR conn!='testconn' OR conn!='wsag2' OR conn!='msag2') "
if group_by_date:
DAY = 'day,'
GROUP = 'day,'
if group_by_conn:
CONN = 'conn,'
GROUP = f"{GROUP} conn,"
if not exclude_conn:
EXCLUDE = "conn!='tgwnew' "
query = f"SELECT {DAY}{CONN} sum(segs_submitted) as submitted, sum(segs_delivered) as delivered, " \
f"sum(segs_total) as total, status FROM {type_msg}_result WHERE (day>='{date_start}' AND day<'{date_end}') " \
f"AND {EXCLUDE} " \
f"GROUP BY {GROUP} status " \
f"ORDER by {DAY}{CONN} status"
return query
def __init__(self,
date_start,
date_end,
config,
output_dir='./report',
contract='COVID',
segs_count='segs_submitted_ok'):
super().__init__(config, output_dir)
if not date_start:
date_start = Date().get_prev_date()
if not date_end:
date_end = Date().get_date(None, True)
self.date_start = Date().get_date(date_start)
self.date_end = Date().get_date(date_end)
'''if not config:
config = 'config/config.xml'
if isinstance(config, str):
config = Config(config)
self.__config=config'''
if not output_dir:
if 'output_dir' in self.get_config().data:
output_dir = self.get_config().data['output_dir']
else:
output_dir = '../report'
self.__output_dir = output_dir
if not contract:
contract = 'CONTRACT'
self.contract = contract
self.__full_output_dir = self.__output_dir
self.set_full_output_dir(self.get_output_dir(), self.get_contract())
self.base_dirs = ['mt', 'mo', 'out']
self.create_def_dirs([])
self.conn_list = {
'mts_epp': 'mts',
'beeline_epp': 'beeline',
'beeline_epp2': 'beeline',
'megafon_epp': 'megafon',
'tele2_epp': 'tele2',
'tele2_7377': 'tele2',
'sber_mt': 'sber',
'beeline_epp2': 'beeline',
'sim': 'sim_telecom',
}
self.segs_count = 5
if segs_count not in ['segs_total', 'segs_submitted_ok']:
segs_count = 'segs_submitted_ok'
if segs_count == 'segs_total':
self.segs_count = 7
self._type_msg = {'mt': 'out', 'mo': 'in'}
self.tunnels = SSH_manager(self.get_config())
def get_uuid_msg_query(self,
tmpls: list,
start: str = None,
end: str = None):
if not start:
start = Date().convert_date_to_str(self.date_start)
if not end:
end = Date().convert_date_to_str(self.date_end)
order = 'ORDER BY created'
tmpls = self.convert_cursor_to_list(tmpls)
return f"SELECT message_uuid FROM new_message WHERE template_id IN {tuple(tmpls)} AND (day>='{start}' and day<'{end}') AND (status!=6 AND status!=9) {order}"
def get_dates(self, date_start, date_end):
if not date_start:
date_start = Date().get_prev_date(first_day=True)
if not date_end:
date_end = Date().get_date(None)
if isinstance(date_start, str):
date_start = Date().get_date(date_start)
if isinstance(date_end, str):
date_end = Date().get_date(date_end)
if date_start >= date_end:
date_start = Date().get_prev_day(date_end)
return (date_start, date_end)
def get_uuid_messages_with_tmpls(self,
tmpls: list = [],
prefix: str = 'tgw',
config: Config = None,
start=None,
end=None):
if not start:
start = self.date_start
if not end:
end = self.date_end
query = self.get_uuid_msg_query(tmpls,
Date().convert_date_to_str(start),
Date().convert_date_to_str(end))
self.get_db().set_config(**self.get_config_db(prefix))
return self.get_db().call_select(query)
def __init__(self,
mapi_db,
mapi_host,
mapi_port,
mapi_user,
mapi_pass,
month=None,
tmpl_db=None,
tmpl_host=None,
tmpl_port=None,
tmpl_user=None,
tmpl_pass=None):
self.mapi_db = mapi_db
self.mapi_host = mapi_host
self.mapi_port = mapi_port
self.mapi_user = mapi_user
self.mapi_pass = mapi_pass
if not tmpl_db:
tmpl_db = mapi_db
if not tmpl_host:
tmpl_host = mapi_host
if not tmpl_port:
tmpl_port = mapi_port
if not tmpl_user:
tmpl_user = mapi_user
if not tmpl_pass:
tmpl_pass = mapi_pass
self.tmpl_db = tmpl_db
self.tmpl_host = tmpl_host
self.tmpl_port = tmpl_port
self.tmpl_user = tmpl_user
self.tmpl_pass = tmpl_pass
self.month = Date().get_date(month)
def create_oper_by_day_table(self,
sheet,
name,
data,
row_index=0,
col_index=0):
title = ['Дата']
[
title.append(self.__fileds_compare[field])
for field in self.__operator_tbl_fields
]
self.add_xlsx_table_header(sheet, title, row_index, col_index)
tbL_data = []
for date, operators in data.items():
row = [Date().convert_date_to_str(date)]
[
row.append(operators[name].get(field))
if name in operators.keys() else 0
for field in self.__operator_tbl_fields
]
tbL_data.append(row)
print(name, tbL_data)
(row_index,
col_index) = self.add_xlsx_table_body(sheet, tbL_data, row_index + 1,
col_index)
return (row_index, col_index + len(title))
return (row_index, col_index)
def get_uuid_messages_with_tmpls(self,
tmpls: list = [],
prefix: str = 'tgw',
config: Config = None,
start=None,
end=None):
if not start:
start = self.date_start
if not end:
end = self.date_end
query = self.get_uuid_msg_query(tmpls,
Date().convert_date_to_str(start),
Date().convert_date_to_str(end))
config = self.get_connection_data(prefix, config)
config['query'] = query
return self.call_select(**config)
def save_as_xlsx(self, data=None, file=None, date=None):
if not date:
date = self.month
else:
date = Date().get_date(date)
if not file:
file = './tmp.xlsx'
wb = openpyxl.Workbook()
wb.create_sheet(title='Отчёт по шаблонам', index=0)
sheet = wb['Отчёт по шаблонам']
# sheet['A1'] = 'Маска/Нейминг'
sheet['A1'].alignment = Alignment(horizontal='right')
self.add_data_to_single_cell(
sheet, f'Отчёт за {date.year:04d}-{date.month:02d}', 1, 1, True)
sheet.merge_cells('A1:B1')
sheet.column_dimensions['A'].width = 8
sheet.column_dimensions['B'].width = 15
sheet.column_dimensions['C'].width = 72
sheet.column_dimensions['D'].width = 65
row_index = 2
col_index = 1
self.add_xlsx_table_header(
sheet, ['ID', 'Маска/Имя', 'Шаблон MDP', 'Шаблон оператора'],
row_index, col_index)
row_index += 1
self.add_xlsx_table_body(sheet, data, row_index, col_index)
FullRange = "A2:D" + str(sheet.max_row)
sheet.auto_filter.ref = FullRange
print(f"save file: {file}")
wb.save(file)
return True
def test_def_values2(self):
params = {
'date_start': self.date_start,
'date_end': self.date_start,
'tgw_db': self.tgw_db,
'tgw_host': self.tgw_host,
'tgw_port': self.tgw_port,
'tgw_user': self.tgw_user,
'tgw_pass': self.tgw_pass
}
smpp = SMPP(**params)
self.assertEqual(Date().get_next_date(self.date_start, True),
smpp.date_end)
print(self.date_start,
Date().get_next_date(self.date_start, True), smpp.date_end)
self.assertEqual(self.date_end,
Date().convert_date_to_str(smpp.date_end, True))
def get_query_used_tmpls(self,
month: datetime.date,
ext_query: str = None) -> str:
next_date = Date().get_next_date(month)
if not ext_query:
ext_query = ''
else:
ext_query = f"AND ({ext_query})"
return f"SELECT template_id FROM new_message WHERE (day>='{month.year:04d}-{month.month:02d}-01' AND " \
f"day<'{next_date.year:04d}-{next_date.month:02d}-01') {ext_query} GROUP BY template_id ORDER BY template_id"
def get_used_tmpl_list(self, month=None, ext_query=None):
if not month:
month = self.month
else:
month = Date().get_date(month, first_day=True)
query = self.get_query_used_tmpls(month, ext_query)
return self.call_select(query,
db=self.mapi_db,
host=self.mapi_host,
port=self.mapi_port,
user=self.mapi_user,
password=self.mapi_pass)
def __init__(self,
date_start,
date_end,
config,
output_dir='./report',
contract='COVID'):
if not date_start:
date_start = Date().get_prev_date()
if not date_end:
date_end = Date().get_date(None, True)
self.date_start = Date().get_date(date_start)
self.date_end = Date().get_date(date_end)
if not config:
config = 'config/config.xml'
if isinstance(config, str):
config = Config(config)
self.config = config
if not output_dir:
if 'output_dir' in config.data:
output_dir = config.data['output_dir']
else:
output_dir = '../report'
self.output_dir = output_dir
if not contract:
contract = 'CONTRACT'
self.contract = contract
self.base_dirs = ['mt', 'mo', 'out']
self.create_def_dirs([])
self.conn_list = {
'mts_epp': 'mts',
'beeline_epp': 'beeline',
'beeline_epp2': 'beeline',
'megafon_epp': 'megafon',
'tele2_epp': 'tele2',
'sber_mt': 'sber',
'beeline_epp2': 'beeline',
'sim': 'sim_telecom',
}
def get_all_agregated_messages(self,
query,
prefix: str = 'tgw',
type_msg: str = 'mt',
submitted: bool = True,
status: str = 'all',
exclude_conn: bool = True,
save: bool = True,
return_all: bool = False):
'''
:param prefix: префикс для формирования запроса с параметрами подключения к базе
:param config: объект Config
:param type_msg: тип сообщения (mt/mo)
:param submitted: тип трафика, который считается (segs_submitted_ok - всё что успешно отправлено в опетора
или segs_total количство pdu согласно контракта)
:param status: [all,delivered,undelivered] - какие соощения отбирать. Со всеми статусами, только достиавленные
или только недоставленные
:param exclude_conn: исключить соединения testconn и wsag2
:param save: сохранять в файл полученные данные или нет
:param return_all: вернуть полученные значения в виде списка или нет
:return:
'''
result = []
if not query:
query = self.get_query_all_agregated_messages(
Date().convert_date_to_str(self.date_start),
Date().convert_date_to_str(self.date_end), type_msg, submitted,
status, exclude_conn)
index = 0
data = self.get_aggregated_messages(query, type_msg, index, save)
if return_all:
result.append(data)
return result
def create_summary_oper_by_day_table(self,
sheet,
operators_list,
data,
row_index=0,
col_index=0):
top_titles = ['Дата']
bottom_titles = [
self.__fileds_compare[field] for field in self.__summary_tbl_fields
]
skip_first = True
tbl_data = []
# Подготовка данных
[top_titles.append(name) for name in operators_list]
for date, operators in data.items():
row = [Date().convert_date_to_str(date, True)]
print(date)
for name in operators_list:
#print(name, self.__summary_tbl_fields, oper)
for field in self.__summary_tbl_fields:
field_data = 0
if name in operators.keys():
oper = operators[name]
field_data = oper.get(field)
print(field, field_data)
row.append(field_data)
#for name, oper in operators.items():
# #titles.append(name)
# print(name,self.__summary_tbl_fields, oper)
# for field in self.__summary_tbl_fields:
# print(field, oper.get(field))
# row.append(oper.get(field))
print(row)
tbl_data.append(row)
# Создать заголовок с операторами
(row_index, col_index) = self.add_xlsx_table_2level_header(
sheet, top_titles, bottom_titles, skip_first, row_index, col_index)
# Заполнить данными
row_index += 1
(row_index,
col_index) = self.add_xlsx_table_body(sheet, tbl_data, row_index,
col_index)
return (row_index, col_index)
def create_summary_by_day_table(self, sheet, detail, row_index, col_index):
title = ['Дата']
[
title.append(self.__fileds_compare[field])
for field in self.__summary_tbl_fields
]
self.add_xlsx_table_header(sheet, title, row_index, col_index)
tbl_data = []
for date, operators in detail.items():
row = [Date().convert_date_to_str(date)]
fields = {field: 0 for field in self.__summary_tbl_fields}
for oper in operators.values():
for field in self.__summary_tbl_fields:
fields[field] += oper.get(field)
[row.append(fields[field]) for field in self.__summary_tbl_fields]
tbl_data.append(row)
(row_index,
col_index) = self.add_xlsx_table_body(sheet, tbl_data, row_index + 1,
col_index)
return (row_index, col_index)
default=f"{yesterday.year:04d}-{yesterday.month:02d}-{yesterday.day:02d}",
help="Set start date, example 2020-01-01. ")
parser.add_argument(
"--date_end",
default=f"{today.year:04d}-{today.month:02d}-{today.day:02d}",
help="Set end date, example 2020-01-01. ")
parser.add_argument("--config", default=None, help="Set path to config file")
parser.add_argument("--output", default=None, help="Set path to output file")
args = parser.parse_args()
config = dict()
# Press the green button in the gutter to run the script.
if __name__ == '__main__':
date_list = Date().get_date_list(args.date_start, args.date_end)
for item in date_list:
report = DIT_report(item['date_start'], item['date_end'], args.config,
args.output)
query = report.get_tmpl_query('covid')
tmpl_ids = report.get_used_tmpl_list(query, 'tmpl')
msgs_uuid = report.get_uuid_messages_with_tmpls(
tmpl_ids, 'mapi', None, report.date_start, report.date_end)
max = 200000
list_of_list_msgs_uuid = report.chunk_using_generators(msgs_uuid, max)
for uuids in list_of_list_msgs_uuid:
import sys
import subprocess
import argparse
from classes.date import Date
from classes.ditreport import DIT_report
conn_list = {
'mts_epp': 'mts',
'beeline_epp': 'beeline',
'megafon_epp': 'megafon',
'tele2_epp': 'tele2',
'sber_mt': 'sber',
'beeline_epp2': 'beeline',
'sim': 'sim_telecom',
}
today = datetime.date.today()
prevmonth = Date().get_prev_date(today, first_day=True)
date_end = f"{today.year:04d}-{today.month:02d}-01"
date_start = f"{prevmonth.year:04d}-{prevmonth.month:02d}-01"
print(date_start, date_end)
parser = argparse.ArgumentParser(description='A tutorial of argparse!')
parser.add_argument("--date_start",
default=f"{date_start}",
help="Set start date, example 2020-01. ")
parser.add_argument("--date_end",
default=f"{date_end}",
help="Set end date, example 2020-01. ")
parser.add_argument("--contract",
default="7757",
help="Set contract, example 7757. ")
import argparse
import datetime
from classes.ditreport import DIT_report
from classes.date import Date
from classes.logger import Logger
import classes.time as timer
from classes.file import FileXLSX
before = datetime.date.today() - datetime.timedelta(days=2)
today = datetime.date.today() - datetime.timedelta(days=2)
if today.day==1:
before = Date().get_prev_date(today, first_day=True)
else:
before = datetime.date(today.year, today.month, 1)
print(f"{before.year:04d}-{before.month:02d}-{before.day:02d}")
print(f"{today.year:04d}-{today.month:02d}-{today.day:02d}")
parser = argparse.ArgumentParser(description='A tutorial of argparse!')
parser.add_argument("--date_start", default=f"{before.year:04d}-{before.month:02d}-{before.day:02d}", help="Set start date, example 2020-01-01. ")
parser.add_argument("--date_end", default=f"{today.year:04d}-{today.month:02d}-{today.day:02d}", help="Set end date, example 2020-01-01. ")
parser.add_argument("--config", default="./config/config_ditrep.xml", help="Set path to config file")
parser.add_argument("--output", default=None, help="Set path to output file")
parser.add_argument("--contract", default='covid', help="Set name contract tag")
parser.add_argument("--connection", default='tgwnew', help="Set name of connection")
parser.add_argument("--max_part", default=10000, help="Set max part of uuids list")
parser.add_argument("--contract_start", default=None, help="Set date when contract start, example= 2020-01-01")
parser.add_argument("--contract_end", default=None, help="Set date when contract finish, example= 2020-02-01")
parser.add_argument("--contract_number", default=0000, help="Set contract number, example=7377-1")
parser.add_argument("--verbose", default=True, help="Set dublicate output to stdout")
args = parser.parse_args()
def get_time_period(self, period='hour'):
if period not in ['day', 'hour', 'min', '10min']:
return False
return Date().get_date_list(self.date_start, self.date_end, period)