84 lines
3.1 KiB
Python
84 lines
3.1 KiB
Python
|
#!/usr/bin/env python3
|
|||
|
|
"""
|
|||
|
|
Скрипт для подсчёта отелей, готовых к аудиту
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
import psycopg2
|
|||
|
|
from psycopg2.extras import RealDictCursor
|
|||
|
|
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 main():
|
|||
|
|
conn = psycopg2.connect(**DB_CONFIG)
|
|||
|
|
cur = conn.cursor(cursor_factory=RealDictCursor)
|
|||
|
|
|
|||
|
|
print("📊 ГОТОВНОСТЬ ОТЕЛЕЙ К АУДИТУ\n" + "="*60)
|
|||
|
|
|
|||
|
|
# 1. Сколько отелей с chunks
|
|||
|
|
cur.execute("""
|
|||
|
|
SELECT COUNT(DISTINCT metadata->>'hotel_id') AS count
|
|||
|
|
FROM hotel_website_chunks
|
|||
|
|
WHERE metadata->>'hotel_id' IS NOT NULL
|
|||
|
|
""")
|
|||
|
|
hotels_with_chunks = cur.fetchone()['count']
|
|||
|
|
|
|||
|
|
# 2. Общее количество chunks
|
|||
|
|
cur.execute("SELECT COUNT(*) AS count FROM hotel_website_chunks")
|
|||
|
|
total_chunks = cur.fetchone()['count']
|
|||
|
|
|
|||
|
|
# 3. Средний размер chunks на отель
|
|||
|
|
cur.execute("""
|
|||
|
|
SELECT
|
|||
|
|
ROUND(AVG(chunk_count), 0) AS avg_chunks
|
|||
|
|
FROM (
|
|||
|
|
SELECT COUNT(*) AS chunk_count
|
|||
|
|
FROM hotel_website_chunks
|
|||
|
|
WHERE metadata->>'hotel_id' IS NOT NULL
|
|||
|
|
GROUP BY metadata->>'hotel_id'
|
|||
|
|
) sub
|
|||
|
|
""")
|
|||
|
|
avg_chunks = cur.fetchone()['avg_chunks'] or 0
|
|||
|
|
|
|||
|
|
# 4. Топ-10 регионов по готовности
|
|||
|
|
cur.execute("""
|
|||
|
|
SELECT
|
|||
|
|
h.region_name,
|
|||
|
|
COUNT(DISTINCT hwc.metadata->>'hotel_id') AS hotels_ready,
|
|||
|
|
COUNT(DISTINCT h.id) AS total_hotels,
|
|||
|
|
ROUND(100.0 * COUNT(DISTINCT hwc.metadata->>'hotel_id') / COUNT(DISTINCT h.id), 1) AS percentage
|
|||
|
|
FROM hotel_main h
|
|||
|
|
LEFT JOIN hotel_website_chunks hwc ON hwc.metadata->>'hotel_id' = h.id::text
|
|||
|
|
WHERE h.region_name IS NOT NULL
|
|||
|
|
GROUP BY h.region_name
|
|||
|
|
HAVING COUNT(DISTINCT hwc.metadata->>'hotel_id') > 0
|
|||
|
|
ORDER BY hotels_ready DESC
|
|||
|
|
LIMIT 10
|
|||
|
|
""")
|
|||
|
|
top_regions = cur.fetchall()
|
|||
|
|
|
|||
|
|
print(f"✅ Отелей с chunks (готовы к аудиту): {hotels_with_chunks:,}")
|
|||
|
|
print(f"📦 Всего chunks в базе: {total_chunks:,}")
|
|||
|
|
print(f"📊 Среднее chunks на отель: {avg_chunks}")
|
|||
|
|
print(f"\n⏱️ ПРОГНОЗ ВРЕМЕНИ АУДИТА:")
|
|||
|
|
print(f" • При 42 сек/отель: {hotels_with_chunks * 42 / 3600:.1f} часов ({hotels_with_chunks * 42 / 86400:.1f} дней)")
|
|||
|
|
print(f" • При 20 сек/отель: {hotels_with_chunks * 20 / 3600:.1f} часов ({hotels_with_chunks * 20 / 86400:.1f} дней)")
|
|||
|
|
print(f" • При 10 сек/отель: {hotels_with_chunks * 10 / 3600:.1f} часов ({hotels_with_chunks * 10 / 86400:.1f} дней)")
|
|||
|
|
|
|||
|
|
print(f"\n🏆 ТОП-10 РЕГИОНОВ ПО ГОТОВНОСТИ:\n{'-'*60}")
|
|||
|
|
for region in top_regions:
|
|||
|
|
print(f"{region['region']:<30} {region['hotels_ready']:>5} отелей ({region['percentage']:>5}%)")
|
|||
|
|
|
|||
|
|
cur.close()
|
|||
|
|
conn.close()
|
|||
|
|
|
|||
|
|
if __name__ == '__main__':
|
|||
|
|
main()
|
|||
|
|
|