#!/usr/bin/env python3 """ Финансовый анализ транзакций из tblGLDetail Анализирует 32.7M транзакций из 43 компаний """ import json import decimal from pathlib import Path from collections import defaultdict from decimal import Decimal class DecimalEncoder(json.JSONEncoder): """Кастомный JSON encoder для Decimal""" def default(self, obj): if isinstance(obj, Decimal): return float(obj) return super().default(obj) print("=== ФИНАНСОВЫЙ АНАЛИЗ ТРАНЗАКЦИЙ ===\n") DUMPS_DIR = Path('/root/dumps/50.21.183.111') OUTPUT_FILE = Path('/root/ir-assessment/redteam/irelydata/financial_analysis.json') # Структура для сбора данных company_financials = defaultdict(lambda: { 'db_name': None, 'total_transactions': 0, 'total_debit': Decimal('0'), 'total_credit': Decimal('0'), 'large_transactions': [], # >$100K 'suspicious_transactions': [], # >$1M 'date_range': {'min': None, 'max': None}, 'accounts': defaultdict(Decimal) }) # Сканирование всех БД all_dbs = [d for d in DUMPS_DIR.iterdir() if d.is_dir() and not d.name.startswith(('.', 'To', 'This', 'user', 'learn', 'visit', 'running', 'as', 'more', 'is', 'mssql', 'container'))] print(f"Сканирование {len(all_dbs)} баз данных...\n") for db_dir in all_dbs: db_name = db_dir.name gl_file = db_dir / 'tblGLDetail.dat' if not gl_file.exists(): continue company_financials[db_name]['db_name'] = db_name print(f"[{db_name}] Анализ транзакций...") # Читаем файл построчно with open(gl_file, 'r', encoding='utf-8', errors='ignore') as f: lines = f.readlines() # Пропускаем заголовок (первые 3 строки) for line in lines[3:]: if not '|' in line or line.startswith('---'): continue parts = line.strip().split('|') # intGLDetailId|intMultiCompanyId|dtmDate|strAccount|strDescription|dblDebit|dblCredit if len(parts) < 7: continue try: date = parts[2] account = parts[3] description = parts[4] try: # Парсинг debit/credit с обработкой ошибок debit_str = parts[5] if parts[5] and parts[5] != 'NULL' else '0' credit_str = parts[6] if parts[6] and parts[6] != 'NULL' else '0' debit = Decimal(debit_str) credit = Decimal(credit_str) except (decimal.InvalidOperation, ValueError): # Пропускаем некорректные записи continue company_financials[db_name]['total_transactions'] += 1 company_financials[db_name]['total_debit'] += debit company_financials[db_name]['total_credit'] += credit # Обновляем date range if company_financials[db_name]['date_range']['min'] is None or date < company_financials[db_name]['date_range']['min']: company_financials[db_name]['date_range']['min'] = date if company_financials[db_name]['date_range']['max'] is None or date > company_financials[db_name]['date_range']['max']: company_financials[db_name]['date_range']['max'] = date # Обновляем accounts if account: company_financials[db_name]['accounts'][account] += abs(debit - credit) # Крупные транзакции amount = abs(debit - credit) if amount >= 100000: # >$100K company_financials[db_name]['large_transactions'].append({ 'date': date, 'account': account, 'description': description[:100], 'amount': float(amount) }) # Подозрительные транзакции if amount >= 1000000: # >$1M company_financials[db_name]['suspicious_transactions'].append({ 'date': date, 'account': account, 'description': description[:100], 'amount': float(amount) }) except (ValueError, IndexError) as e: continue print(f" ✓ {company_financials[db_name]['total_transactions']} транзакций") # Конвертация в JSON-совместимый формат result = [] for db_name, data in company_financials.items(): result.append({ 'db_name': db_name, 'total_transactions': data['total_transactions'], 'total_debit': float(data['total_debit']), 'total_credit': float(data['total_credit']), 'net_position': float(data['total_debit'] - data['total_credit']), 'date_range': data['date_range'], 'large_transactions_count': len(data['large_transactions']), 'suspicious_transactions_count': len(data['suspicious_transactions']), 'top_accounts': dict(sorted(data['accounts'].items(), key=lambda x: float(x[1]), reverse=True)[:10]), 'large_transactions': data['large_transactions'][:20], # Top 20 'suspicious_transactions': data['suspicious_transactions'][:10] # Top 10 }) # Сортировка по объему result.sort(key=lambda x: abs(x['net_position']), reverse=True) # Сохранение with open(OUTPUT_FILE, 'w', encoding='utf-8') as f: json.dump(result, f, indent=2, ensure_ascii=False, cls=DecimalEncoder) print(f"\n{'='*60}") print("ИТОГИ АНАЛИЗА") print("="*60) total_transactions = sum(d['total_transactions'] for d in result) total_debit = sum(d['total_debit'] for d in result) total_credit = sum(d['total_credit'] for d in result) total_large = sum(d['large_transactions_count'] for d in result) total_suspicious = sum(d['suspicious_transactions_count'] for d in result) print(f"\nВсего проанализировано:") print(f" • Компаний: {len(result)}") print(f" • Транзакций: {total_transactions:,}") print(f" • Дебет: ${total_debit:,.2f}") print(f" • Кредит: ${total_credit:,.2f}") print(f" • Крупных транзакций (>$100K): {total_large:,}") print(f" • Подозрительных (>$1M): {total_suspicious:,}") print(f"\n{'='*60}") print("ТОП-10 КОМПАНИЙ ПО ОБЪЕМУ") print("="*60) for i, company in enumerate(result[:10], 1): print(f"\n{i}. {company['db_name']}") print(f" Транзакций: {company['total_transactions']:,}") print(f" Дебет: ${company['total_debit']:,.2f}") print(f" Кредит: ${company['total_credit']:,.2f}") print(f" Net: ${company['net_position']:,.2f}") print(f" Крупных: {company['large_transactions_count']}") print(f" Подозрительных: {company['suspicious_transactions_count']}") if company['suspicious_transactions']: print(f" Пример крупной транзакции:") tx = company['suspicious_transactions'][0] print(f" {tx['date']} - ${tx['amount']:,.2f}") print(f" {tx['description'][:60]}") print(f"\n{'='*60}") print(f"Результаты сохранены: {OUTPUT_FILE}") print("="*60)