497 lines
27 KiB
Python
497 lines
27 KiB
Python
"""
|
||
Финансовая аналитика
|
||
"""
|
||
import logging
|
||
from typing import Dict
|
||
|
||
from ..base import OracleDatabase
|
||
|
||
|
||
class FinanceAnalytics(OracleDatabase):
|
||
"""Класс для финансовой аналитики"""
|
||
|
||
async def get_finance_revenue_stats(self, admin_user_id: int = 0) -> Dict:
|
||
"""Анализ доходов"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Общая статистика доходов (исключаем админа)
|
||
revenue_query = """
|
||
SELECT
|
||
SUM(payment_amount) as total_revenue,
|
||
COUNT(*) as total_transactions,
|
||
AVG(payment_amount) as avg_transaction,
|
||
COUNT(DISTINCT user_id) as unique_customers,
|
||
SUM(CASE WHEN created_date >= SYSDATE - 1 THEN payment_amount ELSE 0 END) as revenue_24h,
|
||
SUM(CASE WHEN created_date >= SYSDATE - 7 THEN payment_amount ELSE 0 END) as revenue_7d,
|
||
SUM(CASE WHEN created_date >= SYSDATE - 30 THEN payment_amount ELSE 0 END) as revenue_30d,
|
||
COUNT(CASE WHEN created_date >= SYSDATE - 1 THEN 1 END) as transactions_24h,
|
||
COUNT(CASE WHEN created_date >= SYSDATE - 7 THEN 1 END) as transactions_7d,
|
||
COUNT(CASE WHEN created_date >= SYSDATE - 30 THEN 1 END) as transactions_30d
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
"""
|
||
cur.execute(revenue_query, {"admin_user_id": admin_user_id})
|
||
revenue = cur.fetchone()
|
||
|
||
# Доходы по дням за последние 10 дней (исключаем админа)
|
||
daily_query = """
|
||
SELECT
|
||
TO_CHAR(created_date, 'DD.MM') as day,
|
||
SUM(payment_amount) as revenue,
|
||
COUNT(*) as transactions
|
||
FROM payment_logs
|
||
WHERE created_date >= SYSDATE - 10 AND payment_status = 'completed' AND user_id != :admin_user_id
|
||
GROUP BY TO_CHAR(created_date, 'DD.MM'), TRUNC(created_date)
|
||
ORDER BY TRUNC(created_date) DESC
|
||
"""
|
||
cur.execute(daily_query, {"admin_user_id": admin_user_id})
|
||
daily_revenue = cur.fetchall()
|
||
|
||
return {
|
||
"total_revenue": float(revenue[0]) if revenue[0] else 0.0,
|
||
"total_transactions": revenue[1] or 0,
|
||
"avg_transaction": round(float(revenue[2]), 2) if revenue[2] else 0.0,
|
||
"unique_customers": revenue[3] or 0,
|
||
"revenue_24h": float(revenue[4]) if revenue[4] else 0.0,
|
||
"revenue_7d": float(revenue[5]) if revenue[5] else 0.0,
|
||
"revenue_30d": float(revenue[6]) if revenue[6] else 0.0,
|
||
"transactions_24h": revenue[7] or 0,
|
||
"transactions_7d": revenue[8] or 0,
|
||
"transactions_30d": revenue[9] or 0,
|
||
"daily_revenue": daily_revenue[:10] if daily_revenue else []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_finance_revenue_stats:")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
try:
|
||
return await self._execute_query(_get_stats)
|
||
except Exception as e:
|
||
logging.error(f"Error getting revenue stats: {e}")
|
||
return {}
|
||
|
||
async def get_finance_services_stats(self, admin_user_id: int = 0) -> Dict:
|
||
"""Анализ доходов по услугам"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Сначала проверим, есть ли данные в таблице (исключаем админа)
|
||
check_query = "SELECT COUNT(*) FROM payment_logs WHERE payment_status = 'completed' AND user_id != :admin_user_id"
|
||
cur.execute(check_query, {"admin_user_id": admin_user_id})
|
||
total_records = cur.fetchone()[0]
|
||
|
||
if total_records == 0:
|
||
return {
|
||
"services_breakdown": [],
|
||
"trends": {}
|
||
}
|
||
|
||
# Статистика по типам услуг (исключаем админа)
|
||
services_query = """
|
||
SELECT
|
||
service_type,
|
||
COUNT(*) as transactions,
|
||
SUM(payment_amount) as revenue,
|
||
AVG(payment_amount) as avg_price,
|
||
COUNT(DISTINCT user_id) as unique_users,
|
||
COUNT(CASE WHEN service_status = 'success' THEN 1 END) as success_count,
|
||
COUNT(CASE WHEN refund_status <> 'no_refund' THEN 1 END) as refunds,
|
||
AVG(data_found_count) as avg_data_found
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
GROUP BY service_type
|
||
ORDER BY SUM(payment_amount) DESC
|
||
"""
|
||
cur.execute(services_query, {"admin_user_id": admin_user_id})
|
||
services = cur.fetchall()
|
||
|
||
# Тренды по услугам за последние 30 дней (исключаем админа)
|
||
trends_query = """
|
||
SELECT
|
||
service_type,
|
||
SUM(CASE WHEN created_date >= SYSDATE - 7 THEN 1 ELSE 0 END) as week_transactions,
|
||
SUM(CASE WHEN created_date >= SYSDATE - 30 THEN 1 ELSE 0 END) as month_transactions
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
GROUP BY service_type
|
||
"""
|
||
cur.execute(trends_query, {"admin_user_id": admin_user_id})
|
||
trends_result = cur.fetchall()
|
||
|
||
trends = {row[0]: {"week": row[1], "month": row[2]} for row in trends_result} if trends_result else {}
|
||
|
||
return {
|
||
"services_breakdown": services if services else [],
|
||
"trends": trends
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_finance_services_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
"services_breakdown": [],
|
||
"trends": {}
|
||
}
|
||
|
||
try:
|
||
return await self._execute_query(_get_stats)
|
||
except Exception as e:
|
||
logging.error(f"Error getting services stats: {e}")
|
||
return {
|
||
"services_breakdown": [],
|
||
"trends": {}
|
||
}
|
||
|
||
async def get_finance_conversion_stats(self, admin_user_id: int = 0) -> Dict:
|
||
"""Анализ конверсии"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Сначала получаем общее количество пользователей
|
||
users_query = "SELECT COUNT(DISTINCT id) FROM bot_users WHERE is_active = 1"
|
||
cur.execute(users_query)
|
||
total_users = cur.fetchone()[0] or 1
|
||
|
||
# Статистика по платежам (исключаем админа)
|
||
payment_query = """
|
||
SELECT
|
||
COUNT(DISTINCT user_id) as paying_users,
|
||
COUNT(*) as total_transactions,
|
||
COUNT(DISTINCT CASE WHEN user_is_premium = 1 THEN user_id END) as premium_buyers,
|
||
COUNT(DISTINCT CASE WHEN user_is_premium = 0 THEN user_id END) as regular_buyers,
|
||
AVG(payment_amount) as avg_purchase
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
"""
|
||
cur.execute(payment_query, {"admin_user_id": admin_user_id})
|
||
payment_result = cur.fetchone()
|
||
|
||
if not payment_result:
|
||
return {
|
||
"total_users": total_users,
|
||
"paying_users": 0,
|
||
"conversion_rate": 0.0,
|
||
"total_transactions": 0,
|
||
"premium_buyers": 0,
|
||
"regular_buyers": 0,
|
||
"avg_purchase": 0.0,
|
||
"one_time_buyers": 0,
|
||
"regular_buyers_repeat": 0,
|
||
"loyal_buyers": 0,
|
||
"avg_purchases_per_user": 0.0,
|
||
"repeat_rate": 0.0
|
||
}
|
||
|
||
paying_users = payment_result[0] or 0
|
||
|
||
# Повторные покупки (исключаем админа)
|
||
repeat_query = """
|
||
SELECT
|
||
COUNT(CASE WHEN purchase_count = 1 THEN 1 END) as one_time_buyers,
|
||
COUNT(CASE WHEN purchase_count BETWEEN 2 AND 5 THEN 1 END) as regular_buyers_count,
|
||
COUNT(CASE WHEN purchase_count > 5 THEN 1 END) as loyal_buyers,
|
||
AVG(purchase_count) as avg_purchases_per_user
|
||
FROM (
|
||
SELECT user_id, COUNT(*) as purchase_count
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
GROUP BY user_id
|
||
)
|
||
"""
|
||
cur.execute(repeat_query, {"admin_user_id": admin_user_id})
|
||
repeat = cur.fetchone()
|
||
|
||
return {
|
||
"total_users": total_users,
|
||
"paying_users": paying_users,
|
||
"conversion_rate": round(paying_users / total_users * 100, 2) if total_users > 0 else 0,
|
||
"total_transactions": payment_result[1] or 0,
|
||
"premium_buyers": payment_result[2] or 0,
|
||
"regular_buyers": payment_result[3] or 0,
|
||
"avg_purchase": round(float(payment_result[4]), 2) if payment_result[4] else 0.0,
|
||
"one_time_buyers": repeat[0] or 0 if repeat else 0,
|
||
"regular_buyers_repeat": repeat[1] or 0 if repeat else 0,
|
||
"loyal_buyers": repeat[2] or 0 if repeat else 0,
|
||
"avg_purchases_per_user": round(float(repeat[3]), 2) if repeat and repeat[3] else 0.0,
|
||
"repeat_rate": round(((repeat[1] or 0) + (repeat[2] or 0)) / paying_users * 100, 2) if paying_users > 0 and repeat else 0
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_finance_conversion_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
"total_users": 0,
|
||
"paying_users": 0,
|
||
"conversion_rate": 0.0,
|
||
"total_transactions": 0,
|
||
"premium_buyers": 0,
|
||
"regular_buyers": 0,
|
||
"avg_purchase": 0.0,
|
||
"one_time_buyers": 0,
|
||
"regular_buyers_repeat": 0,
|
||
"loyal_buyers": 0,
|
||
"avg_purchases_per_user": 0.0,
|
||
"repeat_rate": 0.0
|
||
}
|
||
|
||
try:
|
||
return await self._execute_query(_get_stats)
|
||
except Exception as e:
|
||
logging.error(f"Error getting conversion stats: {e}")
|
||
return {
|
||
"total_users": 0,
|
||
"paying_users": 0,
|
||
"conversion_rate": 0.0,
|
||
"total_transactions": 0,
|
||
"premium_buyers": 0,
|
||
"regular_buyers": 0,
|
||
"avg_purchase": 0.0,
|
||
"one_time_buyers": 0,
|
||
"regular_buyers_repeat": 0,
|
||
"loyal_buyers": 0,
|
||
"avg_purchases_per_user": 0.0,
|
||
"repeat_rate": 0.0
|
||
}
|
||
|
||
async def get_finance_refunds_stats(self, admin_user_id: int = 0) -> Dict:
|
||
"""Анализ возвратов"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Статистика возвратов (исключаем админа)
|
||
refunds_query = """
|
||
SELECT
|
||
COUNT(*) as total_transactions,
|
||
COUNT(CASE WHEN refund_status <> 'no_refund' THEN 1 END) as refund_count,
|
||
SUM(CASE WHEN refund_status <> 'no_refund' THEN payment_amount ELSE 0 END) as refund_amount,
|
||
COUNT(CASE WHEN refund_status = 'auto_refund' THEN 1 END) as auto_refunds,
|
||
COUNT(CASE WHEN refund_status = 'manual_refund' THEN 1 END) as manual_refunds,
|
||
COUNT(CASE WHEN refund_status = 'admin_refund' THEN 1 END) as admin_refunds
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
"""
|
||
cur.execute(refunds_query, {"admin_user_id": admin_user_id})
|
||
refunds = cur.fetchone()
|
||
|
||
# Причины возвратов по типам услуг (исключаем админа)
|
||
reasons_query = """
|
||
SELECT
|
||
service_type,
|
||
refund_status,
|
||
COUNT(*) as count,
|
||
SUM(payment_amount) as amount
|
||
FROM payment_logs
|
||
WHERE refund_status <> 'no_refund' AND payment_status = 'completed' AND user_id != :admin_user_id
|
||
GROUP BY service_type, refund_status
|
||
ORDER BY service_type, COUNT(*) DESC
|
||
"""
|
||
cur.execute(reasons_query, {"admin_user_id": admin_user_id})
|
||
reasons = cur.fetchall()
|
||
|
||
total_transactions = refunds[0] or 1
|
||
refund_count = refunds[1] or 0
|
||
|
||
return {
|
||
"total_transactions": total_transactions,
|
||
"refund_count": refund_count,
|
||
"refund_rate": round(refund_count / total_transactions * 100, 2),
|
||
"refund_amount": float(refunds[2]) if refunds[2] else 0.0,
|
||
"auto_refunds": refunds[3] or 0,
|
||
"manual_refunds": refunds[4] or 0,
|
||
"admin_refunds": refunds[5] or 0,
|
||
"refund_breakdown": reasons if reasons else []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_finance_refunds_stats:")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
try:
|
||
return await self._execute_query(_get_stats)
|
||
except Exception as e:
|
||
logging.error(f"Error getting refunds stats: {e}")
|
||
return {}
|
||
|
||
async def get_finance_transactions_stats(self, admin_user_id: int = 0) -> Dict:
|
||
"""Анализ транзакций"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Статистика транзакций (исключаем админа)
|
||
transactions_query = """
|
||
SELECT
|
||
COUNT(*) as total_attempts,
|
||
COUNT(CASE WHEN payment_status = 'completed' THEN 1 END) as completed_count,
|
||
COUNT(CASE WHEN payment_status = 'pending' THEN 1 END) as pending_count,
|
||
COUNT(CASE WHEN payment_status = 'failed' THEN 1 END) as failed_count,
|
||
COUNT(CASE WHEN service_status = 'success' THEN 1 END) as service_success_count,
|
||
COUNT(CASE WHEN service_status = 'no_data' THEN 1 END) as no_data_count,
|
||
COUNT(CASE WHEN service_status = 'error' THEN 1 END) as service_error_count
|
||
FROM payment_logs
|
||
WHERE user_id != :admin_user_id
|
||
"""
|
||
cur.execute(transactions_query, {"admin_user_id": admin_user_id})
|
||
transactions = cur.fetchone()
|
||
|
||
# Статистика ошибок (исключаем админа)
|
||
errors_query = """
|
||
SELECT
|
||
service_type,
|
||
COUNT(CASE WHEN payment_status = 'failed' THEN 1 END) as payment_failures,
|
||
COUNT(CASE WHEN service_status = 'error' THEN 1 END) as service_errors,
|
||
COUNT(CASE WHEN service_status = 'no_data' THEN 1 END) as no_data_cases
|
||
FROM payment_logs
|
||
WHERE user_id != :admin_user_id
|
||
GROUP BY service_type
|
||
"""
|
||
cur.execute(errors_query, {"admin_user_id": admin_user_id})
|
||
errors = cur.fetchall()
|
||
|
||
total_attempts = transactions[0] or 1
|
||
|
||
return {
|
||
"total_attempts": total_attempts,
|
||
"completed": transactions[1] or 0,
|
||
"pending": transactions[2] or 0,
|
||
"failed": transactions[3] or 0,
|
||
"payment_success_rate": round((transactions[1] or 0) / total_attempts * 100, 2),
|
||
"service_success": transactions[4] or 0,
|
||
"no_data": transactions[5] or 0,
|
||
"service_error": transactions[6] or 0,
|
||
"service_success_rate": round((transactions[4] or 0) / (transactions[1] or 1) * 100, 2),
|
||
"error_breakdown": errors if errors else []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_finance_transactions_stats:")
|
||
logging.error(f"Error: {e}")
|
||
raise
|
||
|
||
try:
|
||
return await self._execute_query(_get_stats)
|
||
except Exception as e:
|
||
logging.error(f"Error getting transactions stats: {e}")
|
||
return {}
|
||
|
||
async def get_finance_efficiency_stats(self, admin_user_id: int = 0) -> Dict:
|
||
"""Анализ эффективности"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# 1. Средний доход на транзакцию (исключаем админа)
|
||
avg_revenue_query = """
|
||
SELECT AVG(payment_amount) as avg_revenue_per_transaction
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
"""
|
||
cur.execute(avg_revenue_query, {"admin_user_id": admin_user_id})
|
||
avg_revenue_result = cur.fetchone()
|
||
avg_revenue_per_transaction = round(float(avg_revenue_result[0]), 2) if avg_revenue_result and avg_revenue_result[0] else 0.0
|
||
|
||
# 2. Количество уникальных дней с транзакциями (исключаем админа)
|
||
days_query = """
|
||
SELECT COUNT(DISTINCT TRUNC(created_date)) as unique_days
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
"""
|
||
cur.execute(days_query, {"admin_user_id": admin_user_id})
|
||
days_result = cur.fetchone()
|
||
unique_days = days_result[0] if days_result and days_result[0] else 1
|
||
|
||
# 3. Общее количество транзакций (исключаем админа)
|
||
total_transactions_query = """
|
||
SELECT COUNT(*) as total_transactions
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
"""
|
||
cur.execute(total_transactions_query, {"admin_user_id": admin_user_id})
|
||
total_transactions_result = cur.fetchone()
|
||
total_transactions = total_transactions_result[0] if total_transactions_result else 0
|
||
|
||
avg_transactions_per_day = round(total_transactions / unique_days, 2) if unique_days > 0 else 0
|
||
|
||
# 4. Доход и транзакции на клиента (исключаем админа)
|
||
customer_stats_query = """
|
||
SELECT
|
||
SUM(payment_amount) as total_revenue,
|
||
COUNT(*) as total_transactions,
|
||
COUNT(DISTINCT user_id) as unique_customers
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
"""
|
||
cur.execute(customer_stats_query, {"admin_user_id": admin_user_id})
|
||
customer_result = cur.fetchone()
|
||
|
||
if customer_result and customer_result[2] and customer_result[2] > 0:
|
||
revenue_per_customer = round(float(customer_result[0]) / customer_result[2], 2)
|
||
transactions_per_customer = round(float(customer_result[1]) / customer_result[2], 2)
|
||
else:
|
||
revenue_per_customer = 0.0
|
||
transactions_per_customer = 0.0
|
||
|
||
# 5. Эффективность по часам дня (исключаем админа)
|
||
hourly_query = """
|
||
SELECT
|
||
EXTRACT(HOUR FROM created_date) as hour,
|
||
COUNT(*) as transactions,
|
||
SUM(payment_amount) as revenue
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND created_date >= SYSDATE - 30 AND user_id != :admin_user_id
|
||
GROUP BY EXTRACT(HOUR FROM created_date)
|
||
ORDER BY EXTRACT(HOUR FROM created_date)
|
||
"""
|
||
cur.execute(hourly_query, {"admin_user_id": admin_user_id})
|
||
hourly = cur.fetchall()
|
||
|
||
# 6. Топ VIN по доходам (исключаем админа)
|
||
top_vins_query = """
|
||
SELECT
|
||
vin_number,
|
||
COUNT(*) as requests,
|
||
SUM(payment_amount) as revenue
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed' AND user_id != :admin_user_id
|
||
GROUP BY vin_number
|
||
ORDER BY SUM(payment_amount) DESC
|
||
FETCH FIRST 10 ROWS ONLY
|
||
"""
|
||
cur.execute(top_vins_query, {"admin_user_id": admin_user_id})
|
||
top_vins = cur.fetchall()
|
||
|
||
return {
|
||
"avg_revenue_per_transaction": avg_revenue_per_transaction,
|
||
"avg_transactions_per_day": avg_transactions_per_day,
|
||
"revenue_per_customer": revenue_per_customer,
|
||
"transactions_per_customer": transactions_per_customer,
|
||
"hourly_distribution": hourly if hourly else [],
|
||
"top_vins": top_vins[:10] if top_vins else []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_finance_efficiency_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
"avg_revenue_per_transaction": 0.0,
|
||
"avg_transactions_per_day": 0.0,
|
||
"revenue_per_customer": 0.0,
|
||
"transactions_per_customer": 0.0,
|
||
"hourly_distribution": [],
|
||
"top_vins": []
|
||
}
|
||
|
||
try:
|
||
return await self._execute_query(_get_stats)
|
||
except Exception as e:
|
||
logging.error(f"Error getting efficiency stats: {e}")
|
||
return {
|
||
"avg_revenue_per_transaction": 0.0,
|
||
"avg_transactions_per_day": 0.0,
|
||
"revenue_per_customer": 0.0,
|
||
"transactions_per_customer": 0.0,
|
||
"hourly_distribution": [],
|
||
"top_vins": []
|
||
} |