224 lines
8.8 KiB
Python
224 lines
8.8 KiB
Python
|
#!/usr/bin/env python3
|
|||
|
|
"""
|
|||
|
|
ИСПРАВЛЕННЫЙ CSV скрипт - работает напрямую с данными из БД
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
import psycopg2
|
|||
|
|
import json
|
|||
|
|
import csv
|
|||
|
|
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():
|
|||
|
|
"""Получить результаты аудита из БД"""
|
|||
|
|
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_csv_report(results):
|
|||
|
|
"""Создать CSV отчёт из данных БД"""
|
|||
|
|
|
|||
|
|
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
|
|||
|
|
filename = f"audit_fixed_{timestamp}.csv"
|
|||
|
|
|
|||
|
|
with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
|
|||
|
|
writer = csv.writer(csvfile)
|
|||
|
|
|
|||
|
|
# Получаем РКН данные для всех отелей
|
|||
|
|
hotel_ids = [str(result['hotel_id']) for result in results]
|
|||
|
|
rkn_data = get_hotel_rkn_info(hotel_ids)
|
|||
|
|
|
|||
|
|
# Заголовки
|
|||
|
|
headers = ['Отель', 'Сайт', 'Есть сайт', 'Балл', 'Процент']
|
|||
|
|
|
|||
|
|
# Добавляем РКН колонки
|
|||
|
|
headers.extend(['6. РКН Реестр', '6. РКН Номер/Дата', '6. РКН Ссылка'])
|
|||
|
|
|
|||
|
|
# Добавляем колонки для всех критериев
|
|||
|
|
print(f"🔍 Отладка: results={len(results)}")
|
|||
|
|
if results:
|
|||
|
|
# Берём критерии из первого результата
|
|||
|
|
first_result = results[0]
|
|||
|
|
print(f"🔍 Отладка: first_result keys={list(first_result.keys())}")
|
|||
|
|
criteria_results = first_result.get('criteria_results')
|
|||
|
|
print(f"🔍 Отладка: criteria_results type={type(criteria_results)}")
|
|||
|
|
print(f"🔍 Отладка: criteria_results bool={bool(criteria_results)}")
|
|||
|
|
|
|||
|
|
if criteria_results:
|
|||
|
|
if isinstance(criteria_results, str):
|
|||
|
|
criteria_results = json.loads(criteria_results)
|
|||
|
|
|
|||
|
|
print(f"🔍 Отладка: criteria_results length={len(criteria_results)}")
|
|||
|
|
print(f"🔍 Отладка: criteria_results keys={list(criteria_results.keys())[:5]}")
|
|||
|
|
|
|||
|
|
for i in range(1, 19): # критерии 1-18
|
|||
|
|
key = f'criterion_{i:02d}'
|
|||
|
|
if key in criteria_results:
|
|||
|
|
criterion_data = criteria_results[key]
|
|||
|
|
criterion_name = criterion_data.get('name', f'Критерий {i}')
|
|||
|
|
headers.extend([
|
|||
|
|
f"{i}. {criterion_name}",
|
|||
|
|
f"{i}. URL",
|
|||
|
|
f"{i}. Комментарий"
|
|||
|
|
])
|
|||
|
|
print(f"🔍 Добавлен критерий {i}: {criterion_name}")
|
|||
|
|
else:
|
|||
|
|
print("🔍 criteria_results пустой или None")
|
|||
|
|
# Добавляем заглушки для критериев
|
|||
|
|
for i in range(1, 19):
|
|||
|
|
headers.extend([
|
|||
|
|
f"{i}. Критерий {i}",
|
|||
|
|
f"{i}. URL",
|
|||
|
|
f"{i}. Комментарий"
|
|||
|
|
])
|
|||
|
|
else:
|
|||
|
|
print("🔍 results пустой")
|
|||
|
|
|
|||
|
|
writer.writerow(headers)
|
|||
|
|
|
|||
|
|
# Данные
|
|||
|
|
for result in results:
|
|||
|
|
row = [
|
|||
|
|
result['hotel_name'],
|
|||
|
|
result.get('website', 'НЕТ САЙТА'),
|
|||
|
|
"Да" if result.get('has_website') else "Нет",
|
|||
|
|
result['total_score'],
|
|||
|
|
f"{result['score_percentage']:.1f}%"
|
|||
|
|
]
|
|||
|
|
|
|||
|
|
# РКН данные
|
|||
|
|
rkn_info = rkn_data.get(str(result['hotel_id']), {})
|
|||
|
|
rkn_status = rkn_info.get('rkn_registry_status', '')
|
|||
|
|
rkn_in_registry = "ДА" if rkn_status and rkn_status.lower() == 'found' else "НЕТ"
|
|||
|
|
rkn_number = rkn_info.get('rkn_registry_number', '')
|
|||
|
|
rkn_date = rkn_info.get('rkn_registry_date', '')
|
|||
|
|
rkn_info_text = f"{rkn_number} {rkn_date}" if rkn_number or rkn_date else "-"
|
|||
|
|
rkn_url = f"https://rkn.gov.ru/mass-communications/reestr/search/?q={rkn_number}" if rkn_number else "-"
|
|||
|
|
|
|||
|
|
row.extend([rkn_in_registry, rkn_info_text, rkn_url])
|
|||
|
|
|
|||
|
|
# Данные по критериям - работаем напрямую со словарём
|
|||
|
|
criteria_results = result['criteria_results']
|
|||
|
|
if isinstance(criteria_results, str):
|
|||
|
|
criteria_results = json.loads(criteria_results)
|
|||
|
|
|
|||
|
|
for i in range(1, 19): # критерии 1-18
|
|||
|
|
key = f'criterion_{i:02d}'
|
|||
|
|
if key in criteria_results:
|
|||
|
|
criterion_data = criteria_results[key]
|
|||
|
|
|
|||
|
|
# Статус
|
|||
|
|
status = "ДА" if criterion_data.get('found') else "НЕТ"
|
|||
|
|
|
|||
|
|
# URL
|
|||
|
|
url = '-'
|
|||
|
|
if criterion_data.get('approval_urls'):
|
|||
|
|
url = criterion_data['approval_urls'][0]
|
|||
|
|
|
|||
|
|
# Комментарий
|
|||
|
|
comment = "Не найдено"
|
|||
|
|
if criterion_data.get('found'):
|
|||
|
|
if criterion_data.get('quote'):
|
|||
|
|
comment = criterion_data['quote']
|
|||
|
|
elif criterion_data.get('approval_quotes'):
|
|||
|
|
first_quote = criterion_data['approval_quotes'][0]
|
|||
|
|
if isinstance(first_quote, dict):
|
|||
|
|
comment = first_quote.get('quote', 'Найдено')
|
|||
|
|
else:
|
|||
|
|
comment = str(first_quote)
|
|||
|
|
elif criterion_data.get('keywords_found'):
|
|||
|
|
comment = f"Ключевые слова: {', '.join(criterion_data['keywords_found'])}"
|
|||
|
|
else:
|
|||
|
|
comment = "Найдено"
|
|||
|
|
|
|||
|
|
comment = comment[:100] + "..." if len(comment) > 100 else comment
|
|||
|
|
|
|||
|
|
row.extend([status, url, comment])
|
|||
|
|
|
|||
|
|
writer.writerow(row)
|
|||
|
|
|
|||
|
|
return filename
|
|||
|
|
|
|||
|
|
def main():
|
|||
|
|
"""Основная функция"""
|
|||
|
|
print("🚀 ИСПРАВЛЕННЫЙ CSV СКРИПТ")
|
|||
|
|
print("=" * 40)
|
|||
|
|
|
|||
|
|
# Получаем данные из БД
|
|||
|
|
results = get_audit_results_from_db()
|
|||
|
|
|
|||
|
|
if not results:
|
|||
|
|
print("❌ Нет данных аудита в БД")
|
|||
|
|
return
|
|||
|
|
|
|||
|
|
print(f"📊 Найдено результатов аудита: {len(results)}")
|
|||
|
|
|
|||
|
|
# Создаём CSV отчёт
|
|||
|
|
filename = create_csv_report(results)
|
|||
|
|
|
|||
|
|
print(f"✅ CSV отчёт сохранён: {filename}")
|
|||
|
|
print(f"📊 Обработано отелей: {len(results)}")
|
|||
|
|
|
|||
|
|
if results:
|
|||
|
|
avg_score = sum(r['score_percentage'] for r in results) / len(results)
|
|||
|
|
print(f"📈 Средний % соответствия: {avg_score:.1f}%")
|
|||
|
|
|
|||
|
|
print(f"\n🔍 Для просмотра: head -2 {filename}")
|
|||
|
|
|
|||
|
|
if __name__ == "__main__":
|
|||
|
|
main()
|