def data_summary_table():
'''
summary of data in main databases
Returns
-------
html: data summary table
'''
start_utc, end_utc, pol, era_type, host, filetype = page_form()
pol_strs, era_type_strs,\
host_strs, filetype_strs = misc_utils.get_set_strings()
file_map = {
host_str:
{filetype_str: {
'file_count': 0
}
for filetype_str in filetype_strs}
for host_str in host_strs
}
dbi, obs_table, file_table, log_table = db_objs()
with dbi.session_scope() as s:
file_query = s.query(file_table.host,
func.substring_index(
file_table.filename, '.', -1),
func.count(file_table))\
.group_by(file_table.host,
func.substring_index(file_table.filename, '.', -1))\
.all()
for host, filetype, count in file_query:
file_map[host][filetype].update({'file_count': count})
all_file_strs = host_strs + filetype_strs
file_total = {all_file_str: {'count': 0} for all_file_str in all_file_strs}
for host in host_strs:
for filetype in filetype_strs:
file_count = file_map[host][filetype]['file_count']
file_total[filetype]['count'] += file_count
file_total[host]['count'] += file_count
no_files = {filetype: {'file_count': 0} for filetype in filetype_strs}
host_strs = tuple(host for host, filetype_dict in file_map.items()
if filetype_dict != no_files)
return render_template('data_summary_table.html',
host_strs=host_strs,
filetype_strs=filetype_strs,
file_map=file_map,
file_total=file_total)
def substring(self, session, column, delimeter):
"""
https://stackoverflow.com/a/57763081
"""
if session.bind.dialect.name == 'sqlite':
return func.substr(column, func.instr(column, delimeter) + 1)
elif session.bind.dialect.name == 'mysql':
return func.substring_index(column, delimeter, -1)
def substring(column, delimeter, session):
if session.bind.dialect.name == 'sqlite':
return func.iif(
func.instr(column, delimeter) > 0,
func.substr(column, 1,
func.instr(column, delimeter) - 1), column)
elif session.bind.dialect.name == 'mysql':
return func.substring_index(column, delimeter, 1)
def fetch_top_calls_to_support(date_begin, date_end, top=50):
calls_to_support_raw = session_crm.query(
func.substring_index(func.substring_index(Call.name, ' ', -3), ' ',
1).label('from_number'),
func.count(Call.id).label('count'),
AccountsCalls1C.accounts_calls_1accounts_ida,
Account.name,
Account.billing_address_street,
AccountsCstm.month_profit_acc_c,
).outerjoin(
AccountsCalls1C,
Call.id == AccountsCalls1C.accounts_calls_1calls_idb).outerjoin(
Account, AccountsCalls1C.accounts_calls_1accounts_ida == Account.id
).outerjoin(AccountsCstm, Account.id == AccountsCstm.id_c).filter(
func.convert_tz(Call.date_start, '+00:00', '+03:00') >= date_begin,
func.convert_tz(Call.date_start, '+00:00', '+03:00') < date_end,
Call.direction == 'Inbound', Call.status == 'autoheld',
Call.created_by == "9daf7540-986e-8385-7040-55b63cc60145",
~func.substring_index(
func.substring_index(Call.name, ' ', -3), ' ',
1).label('from_number').in_(INTERNAL_PHONES)).group_by(
'from_number').order_by(desc('count')).limit(top).all()
calls_to_support = []
account_ids = []
for call in calls_to_support_raw:
if call[2]:
calls_to_support.append(
TopCall(*call[:2], AccountInTicket(*call[2:6]), []))
account_ids.append(call[2])
else:
calls_to_support.append(TopCall(*call[:2], None, None))
tickets_of_account_ids = fetch_tickets_of_account_ids(
account_ids, date_begin, date_end)
for call in calls_to_support:
if not call.account:
continue
tickets = tickets_of_account_ids.get(call.account.id)
if tickets:
call.tickets.extend(tickets)
return calls_to_support
def data_summary_table():
'''
summary of data in main databases
Returns
-------
html: data summary table
'''
start_utc, end_utc, pol, era_type, host, filetype = page_form()
pol_strs, era_type_strs,\
host_strs, filetype_strs = misc_utils.get_set_strings()
file_map = {host_str: {filetype_str: {'file_count': 0}\
for filetype_str in filetype_strs} for host_str in host_strs}
dbi, obs_table, file_table, log_table = db_objs()
with dbi.session_scope() as s:
file_query = s.query(file_table.host,
func.substring_index(file_table.filename, '.', -1),
func.count(file_table))\
.group_by(file_table.host,
func.substring_index(file_table.filename, '.', -1))\
.all()
for host, filetype, count in file_query:
file_map[host][filetype].update({'file_count': count})
all_file_strs = host_strs + filetype_strs
file_total = {all_file_str: {'count': 0} for all_file_str in all_file_strs}
for host in host_strs:
for filetype in filetype_strs:
file_count = file_map[host][filetype]['file_count']
file_total[filetype]['count'] += file_count
file_total[host]['count'] += file_count
no_files = {filetype: {'file_count': 0} for filetype in filetype_strs}
host_strs = tuple(host for host, filetype_dict in file_map.items()\
if filetype_dict != no_files)
return render_template('data_summary_table.html',
host_strs=host_strs, filetype_strs=filetype_strs,
file_map=file_map, file_total=file_total)
def file_filter(file_query, file_table, host, filetype):
'''
filters file query
Parameters
----------
file_query | object: SQLalchemy file table query object
file_table | object: SQLalchemy file table object
host | str: host to limit
filetype | str: file type to limit
Returns
-------
object: file query
'''
if host != 'all':
file_query = file_query.filter(file_table.host == host)
if filetype != 'all':
file_query = file_query\
.filter(func.substring_index(file_table.filename, '.', -1) == filetype)
return file_query
def file_filter(file_query, file_table, host, filetype):
'''
filters file query
Parameters
----------
file_query | object: SQLalchemy file table query object
file_table | object: SQLalchemy file table object
host | str: host to limit
filetype | str: file type to limit
Returns
-------
object: file query
'''
if host != 'all':
file_query = file_query.filter(file_table.host == host)
if filetype != 'all':
file_query = file_query\
.filter(func.substring_index(file_table.filename, '.', -1) == filetype)
return file_query
