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): return func.convert_tz(datetime_col, '+00:00', tz_str(timedelta_mins))