268 lines
11 KiB
Python
268 lines
11 KiB
Python
|
#!/usr/bin/env python3
|
|||
|
|
"""
|
|||
|
|
ФИНАЛЬНАЯ ВЕРСИЯ - Генерация Excel из БД БЕЗ ДУБЛИРОВАНИЯ РКН
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
import psycopg2
|
|||
|
|
import json
|
|||
|
|
import openpyxl
|
|||
|
|
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
|
|||
|
|
from openpyxl.utils import get_column_letter
|
|||
|
|
from openpyxl.chart import BarChart, PieChart, Reference
|
|||
|
|
from datetime import datetime
|
|||
|
|
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():
|
|||
|
|
"""Получить результаты аудита из БД"""
|
|||
|
|
try:
|
|||
|
|
conn = psycopg2.connect(**DB_CONFIG)
|
|||
|
|
cursor = conn.cursor()
|
|||
|
|
|
|||
|
|
cursor.execute("""
|
|||
|
|
SELECT
|
|||
|
|
ar.hotel_id,
|
|||
|
|
hm.full_name,
|
|||
|
|
hm.website_address,
|
|||
|
|
hm.rkn_registry_status,
|
|||
|
|
hm.rkn_registry_number,
|
|||
|
|
hm.rkn_registry_date,
|
|||
|
|
ar.score_percentage,
|
|||
|
|
ar.criteria_results,
|
|||
|
|
hm.created_at
|
|||
|
|
FROM hotel_audit_results ar
|
|||
|
|
JOIN hotel_main hm ON ar.hotel_id = hm.id
|
|||
|
|
WHERE hm.region_name = 'Чукотский автономный округ'
|
|||
|
|
ORDER BY hm.created_at DESC
|
|||
|
|
""")
|
|||
|
|
|
|||
|
|
results = []
|
|||
|
|
for row in cursor.fetchall():
|
|||
|
|
# Конвертируем в словарь
|
|||
|
|
result = {
|
|||
|
|
'hotel_id': row[0],
|
|||
|
|
'full_name': row[1],
|
|||
|
|
'website_address': row[2],
|
|||
|
|
'rkn_registry_status': row[3],
|
|||
|
|
'rkn_registry_number': row[4],
|
|||
|
|
'rkn_registry_date': row[5],
|
|||
|
|
'score_percentage': row[6],
|
|||
|
|
'criteria_results': row[7],
|
|||
|
|
'created_at': row[8]
|
|||
|
|
}
|
|||
|
|
results.append(result)
|
|||
|
|
|
|||
|
|
cursor.close()
|
|||
|
|
conn.close()
|
|||
|
|
|
|||
|
|
return results
|
|||
|
|
|
|||
|
|
except Exception as e:
|
|||
|
|
print(f"❌ Ошибка получения данных: {e}")
|
|||
|
|
return []
|
|||
|
|
|
|||
|
|
def create_excel_report(results):
|
|||
|
|
"""Создать Excel отчёт"""
|
|||
|
|
wb = openpyxl.Workbook()
|
|||
|
|
ws = wb.active
|
|||
|
|
ws.title = "Аудит отелей"
|
|||
|
|
|
|||
|
|
# Стили
|
|||
|
|
header_font = Font(size=12, bold=True, color="FFFFFF")
|
|||
|
|
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
|
|||
|
|
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')
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
# Базовые заголовки
|
|||
|
|
base_headers = [
|
|||
|
|
'ID отеля', 'Название отеля', 'Сайт', 'Балл (%)', 'Дата аудита'
|
|||
|
|
]
|
|||
|
|
|
|||
|
|
# Добавляем базовые заголовки
|
|||
|
|
for i, header in enumerate(base_headers, 1):
|
|||
|
|
cell = ws.cell(row=1, column=i, value=header)
|
|||
|
|
cell.fill = header_fill
|
|||
|
|
cell.font = header_font
|
|||
|
|
cell.border = border
|
|||
|
|
ws.column_dimensions[get_column_letter(i)].width = 20
|
|||
|
|
|
|||
|
|
col = len(base_headers) + 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)
|
|||
|
|
|
|||
|
|
if isinstance(criteria_results, list):
|
|||
|
|
for criterion_idx, criterion in enumerate(criteria_results):
|
|||
|
|
# Пропускаем критерий 6 (РКН) - он будет отдельно
|
|||
|
|
if criterion_idx == 5: # индекс 5 = критерий 6
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
criterion_name = f"{criterion.get('criterion_id', criterion_idx+1)}. {criterion.get('criterion_name', f'Критерий {criterion_idx+1}')}"
|
|||
|
|
|
|||
|
|
# 3 колонки на критерий: Статус, URL, Комментарий
|
|||
|
|
headers = [f"{criterion_name} Статус", f"{criterion_name} URL", f"{criterion_name} Комментарий"]
|
|||
|
|
for header in headers:
|
|||
|
|
cell = ws.cell(row=1, column=col, value=header)
|
|||
|
|
cell.fill = header_fill
|
|||
|
|
cell.font = header_font
|
|||
|
|
cell.border = border
|
|||
|
|
ws.column_dimensions[get_column_letter(col)].width = 30
|
|||
|
|
col += 1
|
|||
|
|
|
|||
|
|
# Добавляем РКН колонки после критерия 5
|
|||
|
|
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.border = border
|
|||
|
|
ws.column_dimensions[get_column_letter(col)].width = 30
|
|||
|
|
col += 1
|
|||
|
|
|
|||
|
|
print(f"✅ Заголовки созданы, всего колонок: {col-1}")
|
|||
|
|
|
|||
|
|
# Добавляем данные
|
|||
|
|
for row_idx, result in enumerate(results, 2):
|
|||
|
|
col = 1
|
|||
|
|
|
|||
|
|
# Базовые данные
|
|||
|
|
ws.cell(row=row_idx, column=col, value=result['hotel_id']).border = border
|
|||
|
|
col += 1
|
|||
|
|
ws.cell(row=row_idx, column=col, value=result['full_name']).border = border
|
|||
|
|
col += 1
|
|||
|
|
ws.cell(row=row_idx, column=col, value=result['website_address'] or '-').border = border
|
|||
|
|
col += 1
|
|||
|
|
ws.cell(row=row_idx, column=col, value=result['score_percentage']).border = border
|
|||
|
|
col += 1
|
|||
|
|
ws.cell(row=row_idx, column=col, value=str(result['created_at'])[:10]).border = border
|
|||
|
|
col += 1
|
|||
|
|
|
|||
|
|
# Данные критериев
|
|||
|
|
criteria_results = result['criteria_results']
|
|||
|
|
if isinstance(criteria_results, str):
|
|||
|
|
criteria_results = json.loads(criteria_results)
|
|||
|
|
|
|||
|
|
if isinstance(criteria_results, list):
|
|||
|
|
for criterion_idx, criterion in enumerate(criteria_results):
|
|||
|
|
# Пропускаем критерий 6 (РКН) - он будет отдельно
|
|||
|
|
if criterion_idx == 5:
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
# Статус
|
|||
|
|
status = "ДА" if criterion.get('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.get('found'):
|
|||
|
|
status_cell.fill = found_fill
|
|||
|
|
else:
|
|||
|
|
status_cell.fill = not_found_fill
|
|||
|
|
col += 1
|
|||
|
|
|
|||
|
|
# URL
|
|||
|
|
url = '-'
|
|||
|
|
if criterion.get('ai_agent', {}).get('url'):
|
|||
|
|
url = criterion['ai_agent']['url']
|
|||
|
|
ws.cell(row=row_idx, column=col, value=url).border = border
|
|||
|
|
col += 1
|
|||
|
|
|
|||
|
|
# Комментарий
|
|||
|
|
comment = "Не найдено"
|
|||
|
|
if criterion.get('found'):
|
|||
|
|
if criterion.get('ai_agent', {}).get('details'):
|
|||
|
|
comment = criterion['ai_agent']['details']
|
|||
|
|
elif criterion.get('ai_agent', {}).get('quote'):
|
|||
|
|
comment = criterion['ai_agent']['quote']
|
|||
|
|
elif criterion.get('regex', {}).get('extracted'):
|
|||
|
|
comment = criterion['regex']['extracted']
|
|||
|
|
ws.cell(row=row_idx, column=col, value=comment).border = border
|
|||
|
|
col += 1
|
|||
|
|
|
|||
|
|
# РКН данные из hotel_main
|
|||
|
|
rkn_status = result.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 = found_fill # Зелёный - хорошо если в реестре
|
|||
|
|
else:
|
|||
|
|
rkn_status_cell.fill = not_found_fill # Красный - плохо если НЕ в реестре
|
|||
|
|
col += 1
|
|||
|
|
|
|||
|
|
rkn_number = result.get('rkn_registry_number', '')
|
|||
|
|
rkn_date = result.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
|
|||
|
|
|
|||
|
|
# Высота строки
|
|||
|
|
ws.row_dimensions[row_idx].height = 50
|
|||
|
|
|
|||
|
|
print(f"✅ Данные добавлены, всего строк: {len(results)}")
|
|||
|
|
|
|||
|
|
# Сохраняем файл
|
|||
|
|
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
|
|||
|
|
filename = f"audit_fixed_{timestamp}.xlsx"
|
|||
|
|
wb.save(filename)
|
|||
|
|
|
|||
|
|
return filename
|
|||
|
|
|
|||
|
|
def main():
|
|||
|
|
"""Основная функция"""
|
|||
|
|
print("🚀 ГЕНЕРАЦИЯ EXCEL ИЗ БД - ФИНАЛЬНАЯ ВЕРСИЯ БЕЗ ДУБЛИРОВАНИЯ")
|
|||
|
|
print("=" * 60)
|
|||
|
|
|
|||
|
|
try:
|
|||
|
|
# Получаем данные
|
|||
|
|
print("📡 Подключаюсь к БД...")
|
|||
|
|
results = get_audit_results_from_db()
|
|||
|
|
|
|||
|
|
if not results:
|
|||
|
|
print("❌ Нет данных для отчёта")
|
|||
|
|
return
|
|||
|
|
|
|||
|
|
print(f"✅ Получено результатов: {len(results)}")
|
|||
|
|
|
|||
|
|
# Создаём отчёт
|
|||
|
|
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}%")
|
|||
|
|
|
|||
|
|
except Exception as e:
|
|||
|
|
print(f"❌ Ошибка: {e}")
|
|||
|
|
|
|||
|
|
if __name__ == "__main__":
|
|||
|
|
main()
|
|||
|
|
|