# -*- coding: utf-8 -*-
"""Trích toàn bộ vụ việc trên portal KPH + thống kê CSDL KPH 823 hồ sơ."""
import io, re, os, json, sqlite3, html as H
from bs4 import BeautifulSoup

PORTAL = r"C:\Claw 2\Bao cao - Hanh chinh\BAOCAOTONGHOP\HTML\KPH_TongHop_0508\index.html"
OUT = r"C:\Claw 2\QC\Base\kph_portal_raw.json"
h = io.open(PORTAL, encoding="utf-8").read()
soup = BeautifulSoup(h, "html.parser")
blocks = soup.select("div.inc")
vus = []
for b in blocks:
    bid = b.get("id", "")
    no = b.select_one("span.no")
    h3 = b.select_one("h3")
    sub = b.select_one(".inc-head span")
    body = b.select_one(".inc-body")
    txt = body.get_text(" ", strip=True) if body else ""
    kv = {}
    for d in (body.select("div.kv > div") if body else []):
        t = d.get_text(" ", strip=True)
        if ":" in t:
            k, v = t.split(":", 1); kv[k.strip()] = v.strip()
    vus.append({"id": bid, "no": no.get_text(strip=True) if no else "",
                "tieu_de": h3.get_text(" ", strip=True) if h3 else "",
                "sub": sub.get_text(" ", strip=True) if sub else "",
                "data_st": b.get("data-st", ""), "kv": kv,
                "text": re.sub(r"\s{2,}", " ", txt)})
print("portal:", len(vus), "vụ | số khối class=inc:", h.count('class="inc"'))
print("số vụ ghi ở 3 chỗ:", re.findall(r"(\d+)\s*vụ việc", h)[:4])
os.makedirs(os.path.dirname(OUT), exist_ok=True)
json.dump(vus, io.open(OUT, "w", encoding="utf-8"), ensure_ascii=False, indent=1)

KH = r"C:\Claw\Khieu Nai KHach Hang\btchem_kph.db"
con = sqlite3.connect(KH, timeout=60)
print("\n=== ho_so_kph 823 ===")
print("theo loai_hs:", con.execute("SELECT loai_hs, COUNT(*) FROM ho_so_kph GROUP BY 1 ORDER BY 2 DESC").fetchall())
print("theo năm (ngay_nhan):", con.execute("""SELECT substr(ngay_nhan,-4), COUNT(*) FROM ho_so_kph
      WHERE ngay_nhan IS NOT NULL GROUP BY 1 ORDER BY 1""").fetchall()[-6:])
print("top 12 khách:", con.execute("""SELECT k.ten, COUNT(*) c FROM ho_so_kph h JOIN khach_hang k ON k.id=h.id_kh
      GROUP BY 1 ORDER BY c DESC LIMIT 12""").fetchall())
print("đầy đủ dữ liệu:", con.execute("""SELECT
  SUM(CASE WHEN mo_ta<>'' THEN 1 ELSE 0 END) mo_ta,
  SUM(CASE WHEN ket_qua_kiem_tra<>'' THEN 1 ELSE 0 END) ket_qua,
  SUM(CASE WHEN nguyen_nhan<>'' THEN 1 ELSE 0 END) nguyen_nhan,
  SUM(CASE WHEN phuong_an<>'' THEN 1 ELSE 0 END) phuong_an,
  SUM(CASE WHEN cong_viec_tiep<>'' THEN 1 ELSE 0 END) cong_viec,
  SUM(CASE WHEN cap_nhat<>'' THEN 1 ELSE 0 END) cap_nhat,
  SUM(CASE WHEN ghi_chu<>'' THEN 1 ELSE 0 END) ghi_chu FROM ho_so_kph""").fetchone())
print("sheet:", con.execute("SELECT sheet, COUNT(*) FROM ho_so_kph GROUP BY 1 ORDER BY 2 DESC LIMIT 6").fetchall())
con.close()
print("\nlưu:", OUT)