2025-10-16 10:52:09 +03:00
|
|
|
|
#!/usr/bin/env python3
|
|
|
|
|
|
"""
|
|
|
|
|
|
ФИНАЛЬНАЯ РАБОЧАЯ ВЕРСИЯ - Excel из БД
|
|
|
|
|
|
"""
|
|
|
|
|
|
|
|
|
|
|
|
import psycopg2
|
|
|
|
|
|
import json
|
|
|
|
|
|
from datetime import datetime
|
|
|
|
|
|
import openpyxl
|
|
|
|
|
|
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
|
|
|
|
|
|
from openpyxl.chart import BarChart, PieChart, Reference
|
|
|
|
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
|
|
from urllib.parse import unquote
|
|
|
|
|
|
|
|
|
|
|
|
# Конфигурация БД
|
|
|
|
|
|
DB_CONFIG = {
|
|
|
|
|
|
'host': '147.45.189.234',
|
|
|
|
|
|
'port': 5432,
|
|
|
|
|
|
'database': 'default_db',
|
|
|
|
|
|
'user': 'gen_user',
|
|
|
|
|
|
'password': unquote('2~~9_%5EkVsU%3F2%5CS')
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
def get_audit_results_from_db():
|
|
|
|
|
|
"""Получить результаты аудита из БД"""
|
|
|
|
|
|
conn = psycopg2.connect(**DB_CONFIG)
|
|
|
|
|
|
cur = conn.cursor()
|
|
|
|
|
|
|
|
|
|
|
|
query = """
|
|
|
|
|
|
SELECT
|
|
|
|
|
|
hotel_id, region_name, hotel_name, website, has_website,
|
|
|
|
|
|
criteria_results, total_score, max_score, score_percentage,
|
|
|
|
|
|
audit_date, audit_version
|
|
|
|
|
|
FROM hotel_audit_results
|
|
|
|
|
|
WHERE audit_version = 'v1.0_with_rkn'
|
|
|
|
|
|
ORDER BY region_name, hotel_name
|
|
|
|
|
|
"""
|
|
|
|
|
|
|
|
|
|
|
|
cur.execute(query)
|
|
|
|
|
|
results = cur.fetchall()
|
|
|
|
|
|
|
|
|
|
|
|
# Преобразуем в словари
|
|
|
|
|
|
columns = [desc[0] for desc in cur.description]
|
|
|
|
|
|
results = [dict(zip(columns, row)) for row in results]
|
|
|
|
|
|
|
|
|
|
|
|
cur.close()
|
|
|
|
|
|
conn.close()
|
|
|
|
|
|
|
|
|
|
|
|
return results
|
|
|
|
|
|
|
|
|
|
|
|
def get_hotel_rkn_info(hotel_ids):
|
|
|
|
|
|
"""Получить РКН информацию для отелей"""
|
|
|
|
|
|
conn = psycopg2.connect(**DB_CONFIG)
|
|
|
|
|
|
cur = conn.cursor()
|
|
|
|
|
|
|
|
|
|
|
|
placeholders = ','.join(['%s'] * len(hotel_ids))
|
|
|
|
|
|
query = f"""
|
|
|
|
|
|
SELECT id, rkn_registry_status, rkn_registry_number, rkn_registry_date
|
|
|
|
|
|
FROM hotel_main
|
|
|
|
|
|
WHERE id IN ({placeholders})
|
|
|
|
|
|
"""
|
|
|
|
|
|
|
|
|
|
|
|
cur.execute(query, hotel_ids)
|
|
|
|
|
|
results = cur.fetchall()
|
|
|
|
|
|
|
|
|
|
|
|
# Преобразуем в словари
|
|
|
|
|
|
columns = [desc[0] for desc in cur.description]
|
|
|
|
|
|
rkn_data = {row[0]: dict(zip(columns, row)) for row in results}
|
|
|
|
|
|
|
|
|
|
|
|
cur.close()
|
|
|
|
|
|
conn.close()
|
|
|
|
|
|
|
|
|
|
|
|
return rkn_data
|
|
|
|
|
|
|
|
|
|
|
|
def create_excel_report(results):
|
|
|
|
|
|
"""Создать Excel отчёт из данных БД"""
|
|
|
|
|
|
|
|
|
|
|
|
wb = openpyxl.Workbook()
|
|
|
|
|
|
ws = wb.active
|
|
|
|
|
|
ws.title = "Аудит"
|
|
|
|
|
|
|
|
|
|
|
|
# Стили
|
|
|
|
|
|
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
|
|
|
|
|
|
header_font = Font(color="FFFFFF", bold=True, size=10)
|
|
|
|
|
|
found_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
|
|
|
|
|
|
not_found_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
|
|
|
|
|
|
border = Border(
|
|
|
|
|
|
left=Side(style='thin'),
|
|
|
|
|
|
right=Side(style='thin'),
|
|
|
|
|
|
top=Side(style='thin'),
|
|
|
|
|
|
bottom=Side(style='thin')
|
|
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
|
|
# ЗАГОЛОВКИ (строка 1)
|
|
|
|
|
|
col = 1
|
|
|
|
|
|
base_headers = ['Отель', 'Сайт', 'Есть сайт', 'Балл', 'Процент']
|
|
|
|
|
|
for header in base_headers:
|
|
|
|
|
|
cell = ws.cell(row=1, column=col, value=header)
|
|
|
|
|
|
cell.fill = header_fill
|
|
|
|
|
|
cell.font = header_font
|
|
|
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Заголовки критериев (включая РКН в правильном месте)
|
|
|
|
|
|
if results and results[0]['criteria_results']:
|
|
|
|
|
|
criteria_results = results[0]['criteria_results']
|
|
|
|
|
|
if isinstance(criteria_results, str):
|
|
|
|
|
|
criteria_results = json.loads(criteria_results)
|
|
|
|
|
|
|
|
|
|
|
|
# Преобразуем словарь в список для удобства
|
|
|
|
|
|
criteria_list = []
|
|
|
|
|
|
for i in range(1, 19): # критерии 1-18
|
|
|
|
|
|
key = f'criterion_{i:02d}'
|
|
|
|
|
|
if key in criteria_results:
|
|
|
|
|
|
criterion_data = criteria_results[key]
|
|
|
|
|
|
criteria_list.append({
|
|
|
|
|
|
'criterion_id': i,
|
|
|
|
|
|
'criterion_name': criterion_data.get('name', f'Критерий {i}'),
|
|
|
|
|
|
'found': criterion_data.get('found', False),
|
|
|
|
|
|
'quote': criterion_data.get('quote', ''),
|
|
|
|
|
|
'score': criterion_data.get('score', 0),
|
|
|
|
|
|
'verdict': criterion_data.get('verdict', 'НЕТ'),
|
|
|
|
|
|
'confidence': criterion_data.get('confidence', 0),
|
|
|
|
|
|
'approval_urls': criterion_data.get('approval_urls', []),
|
|
|
|
|
|
'keywords_found': criterion_data.get('keywords_found', []),
|
|
|
|
|
|
'patterns_found': criterion_data.get('patterns_found', []),
|
|
|
|
|
|
'approval_quotes': criterion_data.get('approval_quotes', [])
|
|
|
|
|
|
})
|
|
|
|
|
|
|
|
|
|
|
|
for criterion_idx, criterion in enumerate(criteria_list):
|
|
|
|
|
|
# Вставляем РКН заголовки после критерия 5 (индекс 4)
|
|
|
|
|
|
if criterion_idx == 5: # После критерия 5 (индекс 5 = 6-й критерий)
|
|
|
|
|
|
# Колонки РКН (критерий #6)
|
|
|
|
|
|
rkn_headers = ['6. РКН Реестр', '6. РКН Номер/Дата', '6. РКН Ссылка']
|
|
|
|
|
|
for header in rkn_headers:
|
|
|
|
|
|
cell = ws.cell(row=1, column=col, value=header)
|
|
|
|
|
|
cell.fill = header_fill
|
|
|
|
|
|
cell.font = header_font
|
|
|
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
if 'Номер' in header:
|
|
|
|
|
|
ws.column_dimensions[get_column_letter(col)].width = 30
|
|
|
|
|
|
elif 'Ссылка' in header:
|
|
|
|
|
|
ws.column_dimensions[get_column_letter(col)].width = 50
|
|
|
|
|
|
else:
|
|
|
|
|
|
ws.column_dimensions[get_column_letter(col)].width = 20
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
criterion_name = f"{criterion['criterion_id']}. {criterion['criterion_name']}"
|
|
|
|
|
|
|
|
|
|
|
|
# Колонка 1: Статус (ДА/НЕТ)
|
|
|
|
|
|
cell = ws.cell(row=1, column=col, value=criterion_name)
|
|
|
|
|
|
cell.fill = header_fill
|
|
|
|
|
|
cell.font = header_font
|
|
|
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
ws.column_dimensions[get_column_letter(col)].width = 35
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Колонка 2: URL
|
|
|
|
|
|
cell = ws.cell(row=1, column=col, value=f"{criterion['criterion_id']}. Апрув URL")
|
|
|
|
|
|
cell.fill = header_fill
|
|
|
|
|
|
cell.font = header_font
|
|
|
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
ws.column_dimensions[get_column_letter(col)].width = 40
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Колонка 3: Цитата/Детали
|
|
|
|
|
|
cell = ws.cell(row=1, column=col, value=f"{criterion['criterion_id']}. Комментарий")
|
|
|
|
|
|
cell.fill = header_fill
|
|
|
|
|
|
cell.font = header_font
|
|
|
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
ws.column_dimensions[get_column_letter(col)].width = 50
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Высота строки заголовков
|
|
|
|
|
|
ws.row_dimensions[1].height = 40
|
|
|
|
|
|
|
|
|
|
|
|
# Получаем РКН данные для всех отелей
|
|
|
|
|
|
hotel_ids = [str(result['hotel_id']) for result in results]
|
|
|
|
|
|
rkn_data = get_hotel_rkn_info(hotel_ids)
|
|
|
|
|
|
|
|
|
|
|
|
# ДАННЫЕ (строки 2+)
|
|
|
|
|
|
for row_idx, result in enumerate(results, 2):
|
|
|
|
|
|
col = 1
|
|
|
|
|
|
|
|
|
|
|
|
# Базовые данные
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=result['hotel_name'])
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(vertical='top', wrap_text=True)
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=result.get('website', 'НЕТ САЙТА'))
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(vertical='top')
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
has_website = "Да" if result.get('has_website') else "Нет"
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=has_website)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=result['total_score'])
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
perc_cell = ws.cell(row=row_idx, column=col, value=f"{result['score_percentage']:.1f}%")
|
|
|
|
|
|
perc_cell.border = border
|
|
|
|
|
|
perc_cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
if result['score_percentage'] >= 70:
|
|
|
|
|
|
perc_cell.fill = found_fill
|
|
|
|
|
|
elif result['score_percentage'] < 50:
|
|
|
|
|
|
perc_cell.fill = not_found_fill
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Данные по критериям
|
|
|
|
|
|
criteria_results = result['criteria_results']
|
|
|
|
|
|
if isinstance(criteria_results, str):
|
|
|
|
|
|
criteria_results = json.loads(criteria_results)
|
|
|
|
|
|
|
|
|
|
|
|
# Преобразуем словарь в список для удобства
|
|
|
|
|
|
criteria_list = []
|
|
|
|
|
|
for i in range(1, 19): # критерии 1-18
|
|
|
|
|
|
key = f'criterion_{i:02d}'
|
|
|
|
|
|
if key in criteria_results:
|
|
|
|
|
|
criterion_data = criteria_results[key]
|
|
|
|
|
|
criteria_list.append({
|
|
|
|
|
|
'criterion_id': i,
|
|
|
|
|
|
'criterion_name': criterion_data.get('name', f'Критерий {i}'),
|
|
|
|
|
|
'found': criterion_data.get('found', False),
|
|
|
|
|
|
'quote': criterion_data.get('quote', ''),
|
|
|
|
|
|
'score': criterion_data.get('score', 0),
|
|
|
|
|
|
'verdict': criterion_data.get('verdict', 'НЕТ'),
|
|
|
|
|
|
'confidence': criterion_data.get('confidence', 0),
|
|
|
|
|
|
'approval_urls': criterion_data.get('approval_urls', []),
|
|
|
|
|
|
'keywords_found': criterion_data.get('keywords_found', []),
|
|
|
|
|
|
'patterns_found': criterion_data.get('patterns_found', []),
|
|
|
|
|
|
'approval_quotes': criterion_data.get('approval_quotes', [])
|
|
|
|
|
|
})
|
|
|
|
|
|
|
|
|
|
|
|
rkn_info = rkn_data.get(str(result['hotel_id']), {})
|
|
|
|
|
|
|
|
|
|
|
|
for criterion_idx, criterion in enumerate(criteria_list):
|
|
|
|
|
|
# Вставляем РКН колонки после критерия 5 (индекс 4)
|
|
|
|
|
|
if criterion_idx == 5: # После критерия 5 (индекс 5 = 6-й критерий)
|
|
|
|
|
|
# Колонки РКН (критерий #6)
|
|
|
|
|
|
rkn_status = rkn_info.get('rkn_registry_status', '')
|
|
|
|
|
|
rkn_in_registry = "ДА" if rkn_status and rkn_status.lower() == 'found' else "НЕТ"
|
|
|
|
|
|
rkn_status_cell = ws.cell(row=row_idx, column=col, value=rkn_in_registry)
|
|
|
|
|
|
rkn_status_cell.border = border
|
|
|
|
|
|
rkn_status_cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
if rkn_in_registry == "ДА":
|
|
|
|
|
|
rkn_status_cell.fill = not_found_fill # Красный - плохо если в реестре
|
|
|
|
|
|
else:
|
|
|
|
|
|
rkn_status_cell.fill = found_fill # Зелёный - хорошо если НЕ в реестре
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
rkn_number = rkn_info.get('rkn_registry_number', '')
|
|
|
|
|
|
rkn_date = rkn_info.get('rkn_registry_date', '')
|
|
|
|
|
|
rkn_info_text = f"{rkn_number}\n{rkn_date}" if rkn_number or rkn_date else "-"
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=rkn_info_text)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(vertical='top', wrap_text=True)
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
rkn_url = f"https://rkn.gov.ru/mass-communications/reestr/search/?q={rkn_number}" if rkn_number else "-"
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=rkn_url)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(vertical='top')
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Колонка 1: Статус (ДА/НЕТ)
|
|
|
|
|
|
status = "ДА" if criterion['found'] else "НЕТ"
|
|
|
|
|
|
status_cell = ws.cell(row=row_idx, column=col, value=status)
|
|
|
|
|
|
status_cell.border = border
|
|
|
|
|
|
status_cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
if criterion['found']:
|
|
|
|
|
|
status_cell.fill = found_fill
|
|
|
|
|
|
else:
|
|
|
|
|
|
status_cell.fill = not_found_fill
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Колонка 2: URL
|
|
|
|
|
|
url = criterion['approval_urls'][0] if criterion['approval_urls'] else '-'
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=url)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(vertical='top')
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Колонка 3: Комментарий/Цитата
|
|
|
|
|
|
if criterion['found']:
|
|
|
|
|
|
comment = ""
|
|
|
|
|
|
|
|
|
|
|
|
# Приоритет: цитата → approval_quotes → keywords_found → "Найдено"
|
|
|
|
|
|
if criterion['quote']:
|
|
|
|
|
|
comment = criterion['quote']
|
|
|
|
|
|
elif criterion['approval_quotes']:
|
|
|
|
|
|
first_quote = criterion['approval_quotes'][0]
|
|
|
|
|
|
if isinstance(first_quote, dict):
|
|
|
|
|
|
comment = first_quote.get('quote', 'Найдено')
|
|
|
|
|
|
else:
|
|
|
|
|
|
comment = str(first_quote)
|
|
|
|
|
|
elif criterion['keywords_found']:
|
|
|
|
|
|
comment = f"Ключевые слова: {', '.join(criterion['keywords_found'])}"
|
|
|
|
|
|
else:
|
|
|
|
|
|
comment = "Найдено"
|
|
|
|
|
|
|
|
|
|
|
|
# Ограничиваем длину
|
|
|
|
|
|
comment = comment[:200] + "..." if len(comment) > 200 else comment
|
|
|
|
|
|
else:
|
|
|
|
|
|
comment = "Не найдено"
|
|
|
|
|
|
|
|
|
|
|
|
cell = ws.cell(row=row_idx, column=col, value=comment)
|
|
|
|
|
|
cell.border = border
|
|
|
|
|
|
cell.alignment = Alignment(vertical='top', wrap_text=True)
|
|
|
|
|
|
col += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Высота строки
|
|
|
|
|
|
ws.row_dimensions[row_idx].height = 50
|
|
|
|
|
|
|
|
|
|
|
|
# Создаём дашборд
|
|
|
|
|
|
create_dashboard(wb, results)
|
|
|
|
|
|
|
|
|
|
|
|
# Сохраняем файл
|
|
|
|
|
|
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
|
|
|
|
|
|
filename = f"audit_final_{timestamp}.xlsx"
|
|
|
|
|
|
wb.save(filename)
|
|
|
|
|
|
|
|
|
|
|
|
return filename
|
|
|
|
|
|
|
|
|
|
|
|
def create_dashboard(wb, results):
|
|
|
|
|
|
"""Создать дашборд с графиками и статистикой"""
|
|
|
|
|
|
|
|
|
|
|
|
# Создаём новый лист для дашборда
|
|
|
|
|
|
ws = wb.create_sheet("📊 Дашборд", 0) # Вставляем первым
|
|
|
|
|
|
|
|
|
|
|
|
# Стили
|
|
|
|
|
|
title_font = Font(size=16, bold=True, color="366092")
|
|
|
|
|
|
header_font = Font(size=12, bold=True, color="FFFFFF")
|
|
|
|
|
|
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
|
|
|
|
|
|
value_font = Font(size=14, bold=True)
|
|
|
|
|
|
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
|
|
|
|
|
|
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
|
|
|
|
|
|
yellow_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
|
|
|
|
|
|
|
|
|
|
|
|
# Заголовок
|
|
|
|
|
|
ws['A1'] = '📊 ДАШБОРД АУДИТА ОТЕЛЕЙ'
|
|
|
|
|
|
ws['A1'].font = title_font
|
|
|
|
|
|
ws.merge_cells('A1:F1')
|
|
|
|
|
|
|
|
|
|
|
|
# Общая статистика
|
|
|
|
|
|
row = 3
|
|
|
|
|
|
ws[f'A{row}'] = 'ОБЩАЯ СТАТИСТИКА'
|
|
|
|
|
|
ws[f'A{row}'].font = Font(size=14, bold=True)
|
|
|
|
|
|
ws.merge_cells(f'A{row}:B{row}')
|
|
|
|
|
|
row += 1
|
|
|
|
|
|
|
|
|
|
|
|
total_hotels = len(results)
|
|
|
|
|
|
hotels_with_website = sum(1 for r in results if r.get('has_website'))
|
|
|
|
|
|
hotels_without_website = total_hotels - hotels_with_website
|
|
|
|
|
|
|
|
|
|
|
|
# Считаем РКН
|
|
|
|
|
|
hotel_ids = [str(r['hotel_id']) for r in results]
|
|
|
|
|
|
rkn_data = get_hotel_rkn_info(hotel_ids)
|
|
|
|
|
|
hotels_in_rkn = sum(1 for r in results
|
|
|
|
|
|
if rkn_data.get(str(r['hotel_id']), {}).get('rkn_registry_status', '').lower() == 'found')
|
|
|
|
|
|
|
|
|
|
|
|
avg_score = sum(r['score_percentage'] for r in results) / total_hotels if total_hotels > 0 else 0
|
|
|
|
|
|
|
|
|
|
|
|
stats = [
|
|
|
|
|
|
('Всего отелей:', total_hotels, None),
|
|
|
|
|
|
('С сайтами:', hotels_with_website, green_fill),
|
|
|
|
|
|
('Без сайтов:', hotels_without_website, red_fill),
|
|
|
|
|
|
('В реестре РКН:', hotels_in_rkn, red_fill if hotels_in_rkn > 0 else green_fill),
|
|
|
|
|
|
('Средний балл:', f"{avg_score:.1f}%", yellow_fill if avg_score < 50 else green_fill),
|
|
|
|
|
|
]
|
|
|
|
|
|
|
|
|
|
|
|
for label, value, fill in stats:
|
|
|
|
|
|
ws[f'A{row}'] = label
|
|
|
|
|
|
ws[f'B{row}'] = value
|
|
|
|
|
|
ws[f'B{row}'].font = value_font
|
|
|
|
|
|
if fill:
|
|
|
|
|
|
ws[f'B{row}'].fill = fill
|
|
|
|
|
|
ws[f'B{row}'].alignment = Alignment(horizontal='center')
|
|
|
|
|
|
row += 1
|
|
|
|
|
|
|
|
|
|
|
|
# Настройка ширины колонок
|
|
|
|
|
|
ws.column_dimensions['A'].width = 35
|
|
|
|
|
|
ws.column_dimensions['B'].width = 15
|
|
|
|
|
|
ws.column_dimensions['C'].width = 15
|
|
|
|
|
|
|
|
|
|
|
|
print(" 📊 Дашборд создан")
|
|
|
|
|
|
|
|
|
|
|
|
def main():
|
|
|
|
|
|
"""Основная функция"""
|
|
|
|
|
|
print("🚀 ФИНАЛЬНАЯ РАБОЧАЯ ВЕРСИЯ - Excel из БД")
|
|
|
|
|
|
print("=" * 50)
|
|
|
|
|
|
|
|
|
|
|
|
# Получаем данные из БД
|
|
|
|
|
|
results = get_audit_results_from_db()
|
|
|
|
|
|
|
|
|
|
|
|
if not results:
|
|
|
|
|
|
print("❌ Нет данных аудита в БД")
|
|
|
|
|
|
return
|
|
|
|
|
|
|
|
|
|
|
|
print(f"📊 Найдено результатов аудита: {len(results)}")
|
|
|
|
|
|
|
|
|
|
|
|
# Создаём Excel отчёт
|
|
|
|
|
|
filename = create_excel_report(results)
|
|
|
|
|
|
|
|
|
|
|
|
print(f"✅ Excel отчёт сохранён: {filename}")
|
|
|
|
|
|
print(f"📊 Обработано отелей: {len(results)}")
|
|
|
|
|
|
|
|
|
|
|
|
if results:
|
|
|
|
|
|
avg_score = sum(r['score_percentage'] for r in results) / len(results)
|
|
|
|
|
|
print(f"📈 Средний % соответствия: {avg_score:.1f}%")
|
|
|
|
|
|
|
|
|
|
|
|
if __name__ == "__main__":
|
|
|
|
|
|
main()
|
|
|
|
|
|
|
2025-10-27 22:49:42 +03:00
|
|
|
|
|
