def get_statistic_of_type_tickets(date_begin, date_end):
tickets = session_crm.query(
Bug.bug_number, BugsCstm.perform_c, BugsCstm.localisation_c).join(
BugsCstm, Bug.id == BugsCstm.id_c).filter(
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') >= date_begin,
func.convert_tz(Bug.date_entered, '+00:00', '+03:00') <
date_end,
).all()
statistic_of_localisation = {}
statistic_of_perform = {}
for ticket in tickets:
update_statistic_of_types(statistic_of_perform, ticket[1])
update_statistic_of_types(statistic_of_localisation, ticket[2])
ordered_statistic_of_localisation = organize_statistic(
statistic_of_localisation, len(tickets), BUG_LOCALISATION)
ordered_statistic_of_perform = organize_statistic(statistic_of_perform,
len(tickets),
BUG_PERFORM)
return StatisticOfTypeTickets(len(tickets),
statistic_of_localisation.get('^none^'),
statistic_of_perform.get('^none^'),
ordered_statistic_of_localisation,
ordered_statistic_of_perform)
def get_file_ops_stats_by_day(session, start, end, offset='+00:00'):
start_str = start.strftime('%Y-%m-%d 00:00:00')
end_str = end.strftime('%Y-%m-%d 23:59:59')
start_at_0 = datetime.strptime(start_str, '%Y-%m-%d %H:%M:%S')
end_at_23 = datetime.strptime(end_str, '%Y-%m-%d %H:%M:%S')
q = session.query(
func.date(func.convert_tz(FileOpsStat.timestamp, '+00:00',
offset)).label("timestamp"),
func.sum(FileOpsStat.number).label("number"),
FileOpsStat.op_type).filter(
FileOpsStat.timestamp.between(
func.convert_tz(start_at_0, offset, '+00:00'),
func.convert_tz(end_at_23, offset, '+00:00'))).group_by(
func.date(
func.convert_tz(FileOpsStat.timestamp, '+00:00',
offset)),
FileOpsStat.op_type).order_by("timestamp")
rows = q.all()
ret = []
for row in rows:
ret.append(
(datetime.strptime(str(row.timestamp),
'%Y-%m-%d'), row.op_type, long(row.number)))
return ret
def fetch_tickets_with_stop_service(date_begin, date_end):
AccountInTicketStopService = namedtuple(
'Account', ('id', 'name', 'address', 'payment', 'company'))
tickets_raw = session_crm.query(
Bug.id, Bug.bug_number, Bug.date_entered, BugsCstm.duration_bug_c,
BugsCstm.duration_min_c, Account.id, Account.name,
Account.billing_address_street,
AccountsCstm.month_profit_acc_c, AccountsCstm.company_acc_c).join(
BugsCstm, BugsCstm.id_c == Bug.id).join(
AccountsBug, AccountsBug.bug_id == Bug.id).join(
Account, AccountsBug.account_id == Account.id).join(
AccountsCstm, AccountsCstm.id_c == Account.id).filter(
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') >= date_begin,
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') < date_end,
or_(BugsCstm.duration_bug_c > 0,
BugsCstm.duration_min_c > 0)).all()
tickets = {}
for ticket in tickets_raw:
ticket_id = ticket[0]
if ticket_id in ('90657094-4fdd-9c4c-dc07-559b8ff0c6ea', ):
# фильтруем лишнее
continue
duration = calc_ticket_duration(ticket[3], ticket[4])
account = AccountInTicketStopService(*ticket[5:9], ticket[9])
if ticket_id in tickets:
tickets[ticket_id].accounts.append(account)
else:
tickets[ticket_id] = NoServiceTicket(*ticket[:3], duration,
[account])
return tickets.values()
def get_user_activity_stats_by_day(session, start, end, offset='+00:00'):
start_str = start.strftime('%Y-%m-%d 00:00:00')
end_str = end.strftime('%Y-%m-%d 23:59:59')
start_at_0 = datetime.strptime(start_str, '%Y-%m-%d %H:%M:%S')
end_at_23 = datetime.strptime(end_str, '%Y-%m-%d %H:%M:%S')
# offset is not supported for now
offset = '+00:00'
q = session.query(
func.date(func.convert_tz(UserActivityStat.timestamp, '+00:00',
offset)).label("timestamp"),
func.count(distinct(
UserActivityStat.username)).label("number")).filter(
UserActivityStat.timestamp.between(
func.convert_tz(start_at_0, offset, '+00:00'),
func.convert_tz(end_at_23, offset, '+00:00'))).group_by(
func.date(
func.convert_tz(UserActivityStat.timestamp,
'+00:00',
offset))).order_by("timestamp")
rows = q.all()
ret = []
for row in rows:
ret.append((datetime.strptime(str(row.timestamp),
'%Y-%m-%d'), row.number))
return ret
def get_user_activity_stats_by_day(session, start, end, offset='+00:00'):
start_str = start.strftime('%Y-%m-%d 00:00:00')
end_str = end.strftime('%Y-%m-%d 23:59:59')
start_at_0 = datetime.strptime(start_str, '%Y-%m-%d %H:%M:%S')
end_at_23 = datetime.strptime(end_str, '%Y-%m-%d %H:%M:%S')
try:
q = session.query(
func.date(
func.convert_tz(UserActivityStatistics.timestamp, '+00:00',
offset)).label("timestamp"),
func.count(UserActivityStatistics.user_time_md5).label("number"))
q = q.filter(
UserActivityStatistics.timestamp.between(
func.convert_tz(start_at_0, offset, '+00:00'),
func.convert_tz(end_at_23, offset, '+00:00')))
rows = q.group_by(func.date(func.convert_tz(UserActivityStatistics.timestamp, '+00:00', offset))).\
order_by("timestamp").all()
except Exception as e:
logger.error('Get user activity statistics failed:', e)
rows = list()
res = list()
for row in rows:
res.append((datetime.strptime(str(row.timestamp),
'%Y-%m-%d'), row.number))
return res
def fetch_wronged_tickets(date_begin, date_end):
wronged_tickets = session_crm.query(
Bug.id, Bug.bug_number, Bug.date_entered, BugsCstm.date_close_c,
BugsCstm.department_bugs_c, BugsCstm.status_bugs_c, BugsCstm.perform_c,
BugsCstm.localisation_c,
BugsCstm.duration_bug_c + BugsCstm.duration_min_c / 60).join(
BugsCstm, Bug.id == BugsCstm.id_c).filter(
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') >= date_begin,
func.convert_tz(Bug.date_entered, '+00:00', '+03:00') <
date_end, Bug.deleted == 0, BugsCstm.status_bugs_c != 'open',
and_(
or_(BugsCstm.perform_c == None, BugsCstm.perform_c == '',
BugsCstm.perform_c == '^none^',
BugsCstm.localisation_c == None,
BugsCstm.localisation_c == '',
BugsCstm.localisation_c == '^none^'))).order_by(
Bug.date_entered).all()
readable_wronged_tickets = []
for ticket in wronged_tickets:
_ticket = WrongedTicket(ticket[0], ticket[1], ticket[2], ticket[3],
ticket[4], ticket[5],
translate_types(ticket[6], BUG_PERFORM),
translate_types(ticket[7],
BUG_LOCALISATION), ticket[8])
readable_wronged_tickets.append(_ticket)
return readable_wronged_tickets
def _get_total_storage_stats(start, end, offset='+00:00', org_id=0):
ret = []
try:
session = appconfig.session_cls()
q = session.query(
func.convert_tz(TotalStorageStat.timestamp, '+00:00',
offset).label("timestamp"),
func.sum(TotalStorageStat.total_size).label("total_size"))
if org_id == 0:
q = q.filter(
TotalStorageStat.timestamp.between(
func.convert_tz(start, offset, '+00:00'),
func.convert_tz(end, offset, '+00:00')))
else:
q = q.filter(
TotalStorageStat.timestamp.between(
func.convert_tz(start, offset, '+00:00'),
func.convert_tz(end, offset, '+00:00')),
TotalStorageStat.org_id == org_id)
q = q.group_by("timestamp").order_by("timestamp")
rows = q.all()
for row in rows:
ret.append((row.timestamp, row.total_size))
except Exception as e:
logging.warning('Failed to get total storage: %s.', e)
finally:
session.close()
return ret
def fetch_wronged_mass_tickets(date_begin, date_end):
tickets = session_crm.query(
Bug.id, Bug.bug_number, Bug.date_entered, Bug.name, Bug.description,
AccountsBug.account_id,
Account.name).join(AccountsBug, AccountsBug.bug_id == Bug.id).join(
Account, Account.id == AccountsBug.account_id).filter(
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') >= date_begin,
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') < date_end, Bug.deleted == 0,
Bug.description != None).order_by(Bug.date_entered).all()
# Группируем таблицу по id, привязанных абонентов собираем в список
# Это не красивое решение, но к сожалению в mysql нет типа array
grouped_tickets = {}
for ticket in tickets:
account = AccountInTicketLite(*ticket[5:7])
if ticket[0] not in grouped_tickets:
accounts = [account]
grouped_tickets[ticket[0]] = WrongedMassTickets(
*ticket[0:5], accounts, [])
else:
grouped_tickets[ticket[0]].accounts.append(account)
accounts = session_crm.query(Account.id, Account.name).join(
AccountsCstm, Account.id == AccountsCstm.id_c).filter(
AccountsCstm.status_acc_c == 'active',
AccountsCstm.company_acc_c == 1, Account.deleted == 0).all()
# Ищем тикеты, в описании которых есть упоминания названий абонентов,
# не привязанных к тикету
tickets_with_not_fixed_accounts = []
for ticket in grouped_tickets.values():
ticket_account_ids = set(account.id for account in ticket.accounts)
# Чистим описание тикета от любымих символов саппорта
description = ticket.description.lower().replace(',', '').replace(
'\r',
'').replace('(', ' ').replace(')',
' ').replace('"',
' ').replace('\'', ' ')
description_lines = description.splitlines()
for account_id, account_name in accounts:
if account_id in ticket_account_ids:
continue
if is_found_account_in_description(account_name,
description_lines):
ticket.not_fixed_accounts.append(
AccountInTicketLite(account_id, account_name))
if ticket.not_fixed_accounts:
tickets_with_not_fixed_accounts.append(ticket)
wronged_tickets = [
ticket for ticket in tickets_with_not_fixed_accounts
if ticket.not_fixed_accounts
]
return wronged_tickets
def fetch_count_created_tickets_at_period(date_begin, date_end):
count_created_tickets_at_period = session_crm.query(
func.date(Bug.date_entered).label('report_date'),
func.count(Bug.id)).filter(
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') >= date_begin,
func.convert_tz(Bug.date_entered, '+00:00', '+03:00') <=
date_end).group_by('report_date').all()
if len(count_created_tickets_at_period) != (date_end - date_begin).days:
return fill_empty_dates_in_statistic(count_created_tickets_at_period,
date_begin, date_end)
return count_created_tickets_at_period
def get_org_traffic_by_day(session,
org_id,
start,
end,
offset='+00:00',
op_type='all'):
start_str = start.strftime('%Y-%m-%d 00:00:00')
end_str = end.strftime('%Y-%m-%d 23:59:59')
start_at_0 = datetime.strptime(start_str, '%Y-%m-%d %H:%M:%S')
end_at_23 = datetime.strptime(end_str, '%Y-%m-%d %H:%M:%S')
# offset is not supported for now
offset = '+00:00'
if op_type == 'web-file-upload' or op_type == 'web-file-download' or op_type == 'sync-file-download' \
or op_type == 'sync-file-upload' or op_type == 'link-file-upload' or op_type == 'link-file-download':
q = session.query(
func.date(func.convert_tz(SysTraffic.timestamp, '+00:00',
offset)).label("timestamp"),
func.sum(SysTraffic.size).label("size"),
SysTraffic.op_type).filter(
SysTraffic.timestamp.between(
func.convert_tz(start_at_0, offset, '+00:00'),
func.convert_tz(end_at_23, offset,
'+00:00')), SysTraffic.org_id == org_id,
SysTraffic.op_type == op_type).group_by(
SysTraffic.org_id,
func.date(
func.convert_tz(SysTraffic.timestamp, '+00:00',
offset)),
SysTraffic.op_type).order_by("timestamp")
elif op_type == 'all':
q = session.query(
func.date(func.convert_tz(SysTraffic.timestamp, '+00:00',
offset)).label("timestamp"),
func.sum(SysTraffic.size).label("size"),
SysTraffic.op_type).filter(
SysTraffic.timestamp.between(
func.convert_tz(start_at_0, offset, '+00:00'),
func.convert_tz(end_at_23, offset, '+00:00')),
SysTraffic.org_id == org_id).group_by(
SysTraffic.org_id,
func.date(
func.convert_tz(SysTraffic.timestamp, '+00:00',
offset)),
SysTraffic.op_type).order_by("timestamp")
else:
return []
rows = q.all()
ret = []
for row in rows:
ret.append(
(datetime.strptime(str(row.timestamp),
'%Y-%m-%d'), row.op_type, long(row.size)))
return ret
def get_statistic_of_opened_tickets(date_begin, date_end):
opened_tickets = session_crm.query(
Bug.bug_number, func.convert_tz(Bug.date_entered, '+00:00', '+03:00'),
func.convert_tz(BugsCstm.date_close_c, '+00:00', '+03:00'),
BugsCstm.status_bugs_c, BugsCstm.department_bugs_c).join(
BugsCstm, Bug.id == BugsCstm.id_c).filter(
func.convert_tz(Bug.date_entered, '+00:00', '+03:00') <=
date_end).order_by(Bug.date_entered).all()
periods = [
date_end - timedelta(days=i)
for i in range((date_end - date_begin).days + 1)
]
ordered_statistic_of_opened_tickets = calculate_statistic_of_open_tickets(
opened_tickets, periods)
return ordered_statistic_of_opened_tickets
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 get_org_file_ops_stats_by_day(org_id, start, end, offset='+00:00'):
start_str = start.strftime('%Y-%m-%d 00:00:00')
end_str = end.strftime('%Y-%m-%d 23:59:59')
start_at_0 = datetime.strptime(start_str, '%Y-%m-%d %H:%M:%S')
end_at_23 = datetime.strptime(end_str, '%Y-%m-%d %H:%M:%S')
ret = []
try:
session = appconfig.session_cls()
q = session.query(
func.date(func.convert_tz(FileOpsStat.timestamp, '+00:00',
offset)).label("timestamp"),
func.sum(FileOpsStat.number).label("number"), FileOpsStat.op_type)
q = q.filter(
FileOpsStat.timestamp.between(
func.convert_tz(start_at_0, offset, '+00:00'),
func.convert_tz(end_at_23, offset, '+00:00')),
FileOpsStat.org_id == org_id)
q = q.group_by(
func.date(func.convert_tz(FileOpsStat.timestamp, '+00:00',
offset)),
FileOpsStat.op_type).order_by("timestamp")
rows = q.all()
for row in rows:
timestamp = datetime.strptime(str(row.timestamp), '%Y-%m-%d')
op_type = row.op_type
num = long(row.number)
ret.append({
"timestamp": timestamp,
"op_type": op_type,
"number": num
})
except Exception as e:
logging.warning('Failed to get org-file operations data: %s.', e)
finally:
session.close()
return ret
def fetch_mass_tickets(date_begin, date_end):
tickets = session_crm.query(
Bug.id, Bug.bug_number, Bug.date_entered, Bug.name, Bug.description,
BugsCstm.status_bugs_c, BugsCstm.address_bugs_c,
BugsCstm.reason_for_closure_bugs_c,
BugsCstm.duration_bug_c + BugsCstm.duration_min_c / 60,
AccountsBug.account_id, Account.name,
Account.billing_address_street, AccountsCstm.month_profit_acc_c).join(
BugsCstm, BugsCstm.id_c == Bug.id).join(
AccountsBug, AccountsBug.bug_id == Bug.id).join(
Account, Account.id == AccountsBug.account_id).join(
AccountsCstm, Account.id == AccountsCstm.id_c).filter(
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') >= date_begin,
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') < date_end,
Bug.deleted == 0,
).order_by(Bug.date_entered).all()
# Группируем таблицу по id, привязанных абонентов собираем в список
# Это не красивое решение, но к сожалению в mysql нет типа array
grouped_tickets = {}
for ticket in tickets:
account = AccountInTicket(*ticket[9:13])
if ticket[0] not in grouped_tickets:
accounts = [account]
payment = account.payment
else:
accounts = grouped_tickets[ticket[0]].accounts
accounts.append(account)
payment = grouped_tickets[ticket[0]].payment + account.payment
grouped_tickets[ticket[0]] = MassTickets(*ticket[0:9], accounts,
payment)
mass_tickets = []
for ticket in grouped_tickets.values():
if len(ticket.accounts) > 1:
mass_tickets.append(MassTickets._make(ticket))
return mass_tickets
def fetch_tickets_of_account_ids(account_ids, date_begin=None, date_end=None):
tickets_query = session_crm.query(
Bug.id, Bug.bug_number, Bug.date_entered, AccountsBug.account_id).join(
AccountsBug, Bug.id == AccountsBug.bug_id).filter(
AccountsBug.account_id.in_(account_ids)).order_by(
Bug.date_entered)
if date_begin:
tickets_query = tickets_query.filter(
func.convert_tz(Bug.date_entered, '+00:00', '+03:00') >= date_begin
)
if date_end:
tickets_query = tickets_query.filter(
func.convert_tz(Bug.date_entered, '+00:00', '+03:00') < date_end)
tickets_raw = tickets_query.all()
tickets = {}
for ticket in tickets_raw:
account_id = ticket[3]
if account_id in tickets:
tickets[account_id].append(TicketLite(*ticket[:3]))
else:
tickets[account_id] = [TicketLite(*ticket[:3])]
return tickets
def fetch_top_tickets(date_begin, date_end, top=50):
top_tickets_raw = session_crm.query(
Account.id, Account.name, Account.billing_address_street,
AccountsCstm.month_profit_acc_c,
func.count(AccountsBug.id).label('count')).join(
AccountsCstm, Account.id == AccountsCstm.id_c).join(
AccountsBug, Account.id == AccountsBug.account_id).join(
Bug, AccountsBug.bug_id == Bug.id).filter(
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') >= date_begin,
func.convert_tz(Bug.date_entered, '+00:00',
'+03:00') < date_end,
Bug.deleted == 0,
AccountsBug.account_id !=
'90657094-4fdd-9c4c-dc07-559b8ff0c6ea',
AccountsBug.account_id !=
'581c9d33-2f3f-88e3-9d28-55e052c92010',
).group_by(Account.id).order_by(
desc('count')).limit(top).all()
top_tickets = []
account_ids = []
for ticket in top_tickets_raw:
top_tickets.append(
TopTicket(AccountInTicket(*ticket[:4]), ticket[4], [], []))
account_ids.append(ticket[0])
tickets_of_account_ids = fetch_tickets_of_account_ids(account_ids)
for ticket in top_tickets:
tickets_account = tickets_of_account_ids.get(ticket.account.id, [])
for ticket_account in tickets_account:
if date_begin <= ticket_account.date_entered.date() < date_end:
ticket.tickets.append(ticket_account)
else:
ticket.tickets_other.append(ticket_account)
return top_tickets
def tz_convert(datetime_col, timedelta_mins):
GMT_TZ_STR = '+00:00'
return func.convert_tz(datetime_col, GMT_TZ_STR, tz_str(timedelta_mins))
def tz_convert(datetime_col, timedelta_mins):
GMT_TZ_STR = '+00:00'
return func.convert_tz(datetime_col, GMT_TZ_STR, tz_str(timedelta_mins))
def tz_convert(datetime_col, timedelta_mins):
return func.convert_tz(datetime_col, '+00:00', tz_str(timedelta_mins))
