Light1YT
86e9ea2937
Многоагентный аудит + имплементация: один воркер на файл, точечные правки. Верификация: py_compile (47/47 .py) + tsc --noEmit (0 ошибок). Unit-тесты не прогонялись (окружение не поднято: rollup native dep / нет pytest-venv). Полностью исправлено (169): #1336, #1337, #1339, #1340, #1341, #1342, #1343, #1345, #1346, #1348, #1349, #1350, #1351, #1354, #1356, #1358, #1359, #1360, #1362, #1364, #1365, #1366, #1367, #1368, #1369, #1370, #1371, #1372, #1373, #1374, #1375, #1376, #1377, #1378, #1379, #1380, #1381, #1382, #1384, #1385, #1386, #1387, #1388, #1389, #1390, #1391, #1392, #1394, #1395, #1396, #1397, #1399, #1400, #1401, #1402, #1403, #1404, #1408, #1409, #1410, #1411, #1412, #1413, #1414, #1415, #1416, #1417, #1418, #1420, #1423, #1425, #1426, #1427, #1428, #1429, #1430, #1431, #1432, #1433, #1434, #1435, #1437, #1438, #1439, #1440, #1441, #1442, #1443, #1444, #1445, #1446, #1447, #1448, #1449, #1450, #1451, #1452, #1453, #1454, #1455, #1456, #1457, #1458, #1459, #1460, #1461, #1462, #1463, #1464, #1465, #1466, #1467, #1468, #1469, #1471, #1472, #1473, #1474, #1476, #1478, #1479, #1481, #1482, #1483, #1484, #1485, #1487, #1488, #1489, #1490, #1491, #1492, #1493, #1494, #1495, #1496, #1497, #1499, #1500, #1501, #1502, #1504, #1505, #1506, #1507, #1510, #1514, #1515, #1516, #1517, #1518, #1519, #1521, #1522, #1523, #1524, #1525, #1526, #1527, #1528, #1529, #1531, #1532, #1533, #1534, #1535, #1536, #1537, #1538 Частично (9, in-file часть, остаток cross-file): #1361, #1419, #1422, #1424, #1470, #1475, #1477, #1480, #1498 Требуют cross-file (3, не тронуты): #1338, #1363, #1421 Пропущено (1): #1539 Не входило в партию: 22 needs-Leha issue (нужны решения владельца). Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
316 lines
16 KiB
Python
316 lines
16 KiB
Python
"""Generate JSON + HTML comparison report."""
|
||
import sqlite3, pathlib, json, statistics
|
||
|
||
DB = pathlib.Path(__file__).parent / "analysis.db"
|
||
OUT = pathlib.Path(__file__).parent / "reports"
|
||
OUT.mkdir(exist_ok=True)
|
||
|
||
PARCEL_ID = "parcel:66:41:0204016:10"
|
||
|
||
# Source of truth for scoring weights (mirrors 03_score.py WEIGHTS / scoring_weights table).
|
||
# economic=0.30 is the heaviest component — must be reported, not hidden.
|
||
DEFAULT_WEIGHTS = {
|
||
"education": 0.20,
|
||
"health": 0.10,
|
||
"retail": 0.15,
|
||
"transit": 0.15,
|
||
"leisure": 0.10,
|
||
"economic": 0.30,
|
||
}
|
||
|
||
WEIGHT_LABELS = {
|
||
"education": "образование",
|
||
"health": "здоровье",
|
||
"retail": "ритейл",
|
||
"transit": "транспорт",
|
||
"leisure": "досуг",
|
||
"economic": "экономика",
|
||
"market": "рынок",
|
||
}
|
||
|
||
def load_weights(conn):
|
||
"""Single source of truth for weights used in both JSON and HTML.
|
||
|
||
Prefer the scoring_weights table (written by 10_score_v2.py); fall back to
|
||
DEFAULT_WEIGHTS (== 03_score.py WEIGHTS). Includes economic (and market for v2).
|
||
"""
|
||
try:
|
||
rows = conn.execute("SELECT component, weight FROM scoring_weights").fetchall()
|
||
except sqlite3.OperationalError:
|
||
rows = None
|
||
if rows:
|
||
return {c: float(w) for c, w in rows}
|
||
return dict(DEFAULT_WEIGHTS)
|
||
|
||
|
||
def fetch_economics(conn, site_id):
|
||
row = conn.execute("""SELECT sd.district, sd.method, sd.nearest_jk_dist_m,
|
||
de.n_projects, de.weighted_price_m2, de.median_price_m2,
|
||
de.deals_per_month_avg, de.months_to_sellout,
|
||
de.real_n_lots, de.real_n_sold, de.real_sold_pct,
|
||
de.real_median_price_m2, de.real_p25_price_m2, de.real_p75_price_m2,
|
||
de.real_avg_area_sold, de.real_velocity_per_month,
|
||
de.real_avg_readiness_pct
|
||
FROM site_district sd
|
||
LEFT JOIN district_economics de USING (district)
|
||
WHERE sd.site_id=?""", (site_id,)).fetchone()
|
||
if not row: return None
|
||
cols = ["district","district_method","district_dist_m",
|
||
"n_projects","weighted_price_m2_corp_sum","median_price_m2_corp_sum",
|
||
"deals_per_month_corp_sum","months_to_sellout",
|
||
"n_lots","n_sold","sold_pct",
|
||
"median_price_m2","p25_price_m2","p75_price_m2",
|
||
"avg_area_sold_m2","velocity_per_month","avg_readiness_pct"]
|
||
return dict(zip(cols, row))
|
||
|
||
|
||
def fetch_site_full(conn, site_id):
|
||
s = dict(zip(
|
||
[c[0] for c in conn.execute("PRAGMA table_info(sites)").fetchall()],
|
||
conn.execute("SELECT * FROM sites WHERE site_id=?", (site_id,)).fetchone()
|
||
)) if False else None
|
||
cur = conn.execute("SELECT * FROM sites WHERE site_id=?", (site_id,))
|
||
cols = [c[0] for c in cur.description]
|
||
s = dict(zip(cols, cur.fetchone()))
|
||
s["features"] = dict(conn.execute(
|
||
"SELECT feature,value FROM features WHERE site_id=?", (site_id,)).fetchall())
|
||
s["scores"] = dict(conn.execute(
|
||
"SELECT component,score_0_100 FROM scores WHERE site_id=?", (site_id,)).fetchall())
|
||
tot = conn.execute(
|
||
"SELECT weighted,rank_overall,rank_district FROM scores_total WHERE site_id=?",
|
||
(site_id,)).fetchone()
|
||
s["weighted_total"] = tot[0]
|
||
s["rank_overall"] = tot[1]
|
||
s["rank_district"] = tot[2]
|
||
s["economics"] = fetch_economics(conn, site_id)
|
||
s["nearest_pois"] = {}
|
||
for cat in ["kindergarten","school","university","pharmacy","clinic","hospital",
|
||
"shop_big","shop_med","shop_small","bus_stop","tram_stop","metro",
|
||
"park","playground","sports"]:
|
||
row = conn.execute(
|
||
"""SELECT name, distance_m, lat, lon FROM pois
|
||
WHERE site_id=? AND category=? ORDER BY distance_m LIMIT 5""",
|
||
(site_id, cat)).fetchall()
|
||
s["nearest_pois"][cat] = [
|
||
{"name": r[0] or "—", "distance_m": round(r[1], 1), "lat": r[2], "lon": r[3]}
|
||
for r in row
|
||
]
|
||
return s
|
||
|
||
def main():
|
||
conn = sqlite3.connect(DB)
|
||
parcel = fetch_site_full(conn, PARCEL_ID)
|
||
n_total = conn.execute("SELECT count(*) FROM sites").fetchone()[0]
|
||
weights = load_weights(conn)
|
||
|
||
# Top-10 best-scoring ЖК + parcel position context
|
||
rows = conn.execute("""SELECT s.site_id, s.name, s.district, s.developer, s.obj_class,
|
||
s.flat_count, s.lat, s.lon,
|
||
st.weighted, st.rank_overall
|
||
FROM sites s JOIN scores_total st USING (site_id)
|
||
WHERE s.kind='jk' ORDER BY st.weighted DESC""").fetchall()
|
||
top10 = [dict(zip(['site_id','name','district','developer','obj_class','flat_count',
|
||
'lat','lon','weighted','rank'], r)) for r in rows[:10]]
|
||
|
||
# Distribution stats
|
||
all_w = [r[8] for r in rows]
|
||
stats = {
|
||
"n_jk": len(all_w),
|
||
"mean": round(statistics.mean(all_w), 1),
|
||
"median": round(statistics.median(all_w), 1),
|
||
"p25": round(statistics.quantiles(all_w, n=4)[0], 1),
|
||
"p75": round(statistics.quantiles(all_w, n=4)[2], 1),
|
||
"min": round(min(all_w), 1),
|
||
"max": round(max(all_w), 1),
|
||
}
|
||
|
||
# Component-wise comparison
|
||
parcel_comp = parcel["scores"]
|
||
comp_stats = {}
|
||
for c in parcel_comp:
|
||
vals = [v for (v,) in conn.execute(
|
||
"SELECT score_0_100 FROM scores s JOIN sites si USING (site_id) WHERE component=? AND si.kind='jk'",
|
||
(c,)).fetchall()]
|
||
comp_stats[c] = {
|
||
"parcel": round(parcel_comp[c], 1),
|
||
"median_jk": round(statistics.median(vals), 1),
|
||
"p75_jk": round(statistics.quantiles(vals, n=4)[2], 1),
|
||
}
|
||
|
||
# Closest comparable ЖК (by geographic proximity to parcel)
|
||
plat, plon = parcel["lat"], parcel["lon"]
|
||
nearby = []
|
||
for r in conn.execute("""SELECT s.site_id, s.name, s.district, s.developer, s.obj_class,
|
||
s.lat, s.lon, st.weighted, st.rank_overall
|
||
FROM sites s JOIN scores_total st USING (site_id)
|
||
WHERE s.kind='jk'""").fetchall():
|
||
import math
|
||
R=6371000; p1,p2=math.radians(plat),math.radians(r[5])
|
||
dp=math.radians(r[5]-plat); dl=math.radians(r[6]-plon)
|
||
a=math.sin(dp/2)**2+math.cos(p1)*math.cos(p2)*math.sin(dl/2)**2
|
||
d=2*R*math.asin(math.sqrt(a))
|
||
nearby.append((d, r))
|
||
nearby.sort()
|
||
closest = [
|
||
{"distance_m": round(d, 0), "site_id": r[0], "name": r[1], "district": r[2],
|
||
"developer": r[3], "obj_class": r[4], "weighted": round(r[7], 1), "rank": r[8]}
|
||
for d, r in nearby[:10]
|
||
]
|
||
|
||
out = {
|
||
"generated_at": __import__("datetime").datetime.now().isoformat(timespec="seconds"),
|
||
"parcel": parcel,
|
||
"n_compared_jk": stats["n_jk"],
|
||
"weighted_score_distribution": stats,
|
||
"component_comparison": comp_stats,
|
||
"weights_used": weights,
|
||
"top10_best_jk_ekb": top10,
|
||
"10_closest_jk_to_parcel": closest,
|
||
}
|
||
|
||
json_path = OUT / "parcel_66_41_0204016_10.json"
|
||
with open(json_path, "w") as f:
|
||
json.dump(out, f, ensure_ascii=False, indent=2, default=str)
|
||
print(f"JSON: {json_path}")
|
||
|
||
# Build HTML
|
||
html = build_html(out)
|
||
html_path = OUT / "parcel_66_41_0204016_10.html"
|
||
with open(html_path, "w") as f:
|
||
f.write(html)
|
||
print(f"HTML: {html_path}")
|
||
|
||
conn.close()
|
||
|
||
def econ_block(e):
|
||
if not e:
|
||
return "<p class=muted>Нет данных Объектива для этого района.</p>"
|
||
f = lambda x, suf="": (f"{x:.1f}{suf}" if isinstance(x,(int,float)) else "—")
|
||
method_note = "по совпадению имени ЖК" if e["district_method"]=="name_match" \
|
||
else f"по голосованию 5 ближайших ЖК (расст. до репрезентанта {e['district_dist_m']:.0f} м)"
|
||
return f"""
|
||
<table>
|
||
<tr><th>Район (Объектив)</th><td><b>{e["district"]}</b> <span class=muted>({method_note})</span></td></tr>
|
||
<tr><th>Проектов в районе</th><td>{e["n_projects"]}</td></tr>
|
||
<tr><th>Лотов всего / продано</th><td>{e["n_lots"] or 0:,} / <b>{e["n_sold"] or 0:,}</b> ({f(e["sold_pct"], '%')})</td></tr>
|
||
<tr><th>Цена за м² (медиана)</th><td><b>{f(e["median_price_m2"])} тыс ₽</b> · P25={f(e["p25_price_m2"])} · P75={f(e["p75_price_m2"])}</td></tr>
|
||
<tr><th>Средняя площадь сделки</th><td>{f(e["avg_area_sold_m2"])} м²</td></tr>
|
||
<tr><th>Скорость продаж (real)</th><td><b>{f(e["velocity_per_month"])}</b> зарег. ДДУ/корпус/мес <span class=muted>(по 12 мес)</span></td></tr>
|
||
<tr><th>Средняя готовность</th><td>{f(e["avg_readiness_pct"], '%')}</td></tr>
|
||
<tr><th>Цена corp_sum (взвеш.)</th><td class=muted>{f(e["weighted_price_m2_corp_sum"])} тыс ₽/м² · скорость {f(e["deals_per_month_corp_sum"])}</td></tr>
|
||
<tr><th>Распродажа стока (corp_sum)</th><td class=muted>{f(e["months_to_sellout"], ' мес')}</td></tr>
|
||
</table>
|
||
<p class=muted><b>real_*</b> рассчитаны по {e["n_lots"] or 0:,} лотам из Поквартирные/Лоты (303 677 квартир Екб). Это per-flat, основной источник правды.</p>
|
||
"""
|
||
|
||
def build_html(d):
|
||
p = d["parcel"]
|
||
cs = d["component_comparison"]
|
||
# Single source of weights for the caption — identical to JSON weights_used.
|
||
weights_caption = " · ".join(
|
||
f"{WEIGHT_LABELS.get(c, c)} {w*100:.0f}%"
|
||
for c, w in d.get("weights_used", {}).items()
|
||
)
|
||
poi_table_rows = []
|
||
for cat, items in p["nearest_pois"].items():
|
||
nearest = items[0] if items else None
|
||
poi_table_rows.append(
|
||
f"<tr><td>{cat}</td>"
|
||
f"<td>{nearest['name'] if nearest else '—'}</td>"
|
||
f"<td class='r'>{('%.0f м' % nearest['distance_m']) if nearest else '—'}</td>"
|
||
f"<td class='r'>{p['features'].get(f'{cat}_count_500m','')}</td>"
|
||
f"<td class='r'>{p['features'].get(f'{cat}_count_1km','')}</td></tr>"
|
||
)
|
||
|
||
comp_rows = []
|
||
for c, v in cs.items():
|
||
delta = v["parcel"] - v["median_jk"]
|
||
cls = "g" if delta > 0 else ("neg" if delta < 0 else "")
|
||
comp_rows.append(
|
||
f"<tr><td>{c}</td><td class='r'>{v['parcel']}</td>"
|
||
f"<td class='r'>{v['median_jk']}</td><td class='r'>{v['p75_jk']}</td>"
|
||
f"<td class='r {cls}'>{'+' if delta>=0 else ''}{delta:.1f}</td></tr>"
|
||
)
|
||
|
||
closest_rows = "".join(
|
||
f"<tr><td class='r'>{x['distance_m']:.0f} м</td>"
|
||
f"<td>{x['name'] or '—'}</td><td>{x['district'] or '—'}</td>"
|
||
f"<td>{x['developer'] or ''}</td><td>{x['obj_class'] or ''}</td>"
|
||
f"<td class='r'>{x['weighted']}</td><td class='r'>#{x['rank']}</td></tr>"
|
||
for x in d["10_closest_jk_to_parcel"]
|
||
)
|
||
|
||
top_rows = "".join(
|
||
f"<tr><td class='r'>#{x['rank']}</td><td>{x['name'] or '—'}</td>"
|
||
f"<td>{x['district'] or '—'}</td><td>{x['developer'] or ''}</td>"
|
||
f"<td>{x['obj_class'] or ''}</td><td class='r'>{x['weighted']:.1f}</td></tr>"
|
||
for x in d["top10_best_jk_ekb"]
|
||
)
|
||
|
||
return f"""<!doctype html>
|
||
<html lang=ru><head><meta charset=utf-8>
|
||
<title>Анализ участка {p['name']}</title>
|
||
<style>
|
||
body{{font-family:-apple-system,Segoe UI,sans-serif;max-width:980px;margin:32px auto;padding:0 16px;color:#222;line-height:1.5}}
|
||
h1{{font-size:24px;margin:0 0 8px}}
|
||
h2{{font-size:18px;margin-top:32px;border-bottom:1px solid #eee;padding-bottom:6px}}
|
||
.kpi{{display:grid;grid-template-columns:repeat(5,1fr);gap:12px;margin:18px 0}}
|
||
.kpi div{{background:#f5f7fa;border-radius:8px;padding:14px}}
|
||
.kpi b{{display:block;font-size:11px;color:#666;text-transform:uppercase;margin-bottom:4px}}
|
||
.kpi span{{font-size:24px;font-weight:600}}
|
||
.big{{font-size:42px;font-weight:700;color:#0a6}}
|
||
table{{border-collapse:collapse;width:100%;margin:8px 0;font-size:13px}}
|
||
th,td{{border:1px solid #ddd;padding:6px 10px;text-align:left}}
|
||
th{{background:#f5f7fa;font-weight:600}}
|
||
.r{{text-align:right}}
|
||
.g{{color:#0a6;font-weight:600}}
|
||
.neg{{color:#c33;font-weight:600}}
|
||
.muted{{color:#888;font-size:12px}}
|
||
.note{{background:#fffbe6;border-left:4px solid #f0c000;padding:10px 14px;border-radius:4px;margin:12px 0}}
|
||
</style></head><body>
|
||
<h1>Анализ участка <code>66:41:0204016:10</code></h1>
|
||
<div class=muted>Сгенерировано {d["generated_at"]} · Сравнение с {d["n_compared_jk"]} строящимися ЖК Екатеринбурга</div>
|
||
|
||
<div class="note">
|
||
Координаты участка получены из ссылки на NSPD-карту (EPSG:3857 → WGS84):
|
||
<b>{p['lat']}, {p['lon']}</b>. Район — центрально-северная часть ЕКБ (Пионерский / Втузгородок).
|
||
</div>
|
||
|
||
<div class=kpi>
|
||
<div><b>Итоговый балл</b><span class=big>{p["weighted_total"]:.1f}</span><span class=muted>из 100</span></div>
|
||
<div><b>Ранг по ЕКБ</b><span>#{p["rank_overall"]} / {d["n_compared_jk"]+1}</span></div>
|
||
<div><b>Перцентиль</b><span>{(1 - (p["rank_overall"]-1)/(d["n_compared_jk"]+1))*100:.0f}%</span></div>
|
||
<div><b>Медиана ЖК</b><span>{d["weighted_score_distribution"]["median"]}</span></div>
|
||
<div><b>Топ-25% ЖК ≥</b><span>{d["weighted_score_distribution"]["p75"]}</span></div>
|
||
</div>
|
||
|
||
<h2>Компоненты (взвешенные)</h2>
|
||
<table><thead><tr><th>Компонент</th><th class=r>Участок</th><th class=r>Медиана ЖК</th><th class=r>P75 ЖК</th><th class=r>Δ vs медиана</th></tr></thead>
|
||
<tbody>{"".join(comp_rows)}</tbody></table>
|
||
<div class=muted>Веса: {weights_caption}</div>
|
||
|
||
<h2>Экономика района (Объектив API; скорость — за 12 мес, доли продаж и цены — накопительно за всё время)</h2>
|
||
{econ_block(p.get("economics"))}
|
||
|
||
<h2>Ближайшие POI вокруг участка</h2>
|
||
<table><thead><tr><th>Категория</th><th>Ближайший</th><th class=r>До него</th><th class=r>В 500 м</th><th class=r>В 1 км</th></tr></thead>
|
||
<tbody>{"".join(poi_table_rows)}</tbody></table>
|
||
|
||
<h2>10 ближайших ЖК (для прямого бенчмарка)</h2>
|
||
<table><thead><tr><th class=r>Расст.</th><th>Название</th><th>Район</th><th>Девелопер</th><th>Класс</th><th class=r>Балл</th><th class=r>Ранг</th></tr></thead>
|
||
<tbody>{closest_rows}</tbody></table>
|
||
|
||
<h2>Топ-10 ЖК ЕКБ по локационной привлекательности</h2>
|
||
<table><thead><tr><th class=r>Ранг</th><th>Название</th><th>Район</th><th>Девелопер</th><th>Класс</th><th class=r>Балл</th></tr></thead>
|
||
<tbody>{top_rows}</tbody></table>
|
||
|
||
<h2>Методика</h2>
|
||
<p><b>Источник POI:</b> OpenStreetMap (Overpass API), bbox по всем 381 ЖК Свердл.</p>
|
||
<p><b>Логика:</b> для каждой категории — расстояние-в-балл (piecewise linear от <i>ideal_m</i> к <i>max_m</i>),
|
||
далее агрегация в 5 компонент с весами (max-pool там, где категории альтернативны: ритейл = max(big, 0.7×med); транспорт = max(metro, 0.85×tram, 0.7×bus)).
|
||
Финальный балл — взвешенная сумма компонент.</p>
|
||
<p><b>База данных:</b> локальная SQLite <code>analysis.db</code> (sites/pois/features/scores), под-выборка строящихся ЖК ЕКБ из прода <code>domrf_kn_objects</code>.</p>
|
||
</body></html>"""
|
||
|
||
if __name__ == "__main__":
|
||
main()
|