93 lines
3.4 KiB
Python
93 lines
3.4 KiB
Python
|
|
|||
|
|
import os
|
|||
|
|
import pg8000.dbapi as pg
|
|||
|
|
from lawrisk.utils.env_loader import load_env
|
|||
|
|
from lawrisk.services.licensing_repo import _parse_import_workbook, _ensure_risk
|
|||
|
|
|
|||
|
|
def patch_missing_risks():
|
|||
|
|
load_env()
|
|||
|
|
file_path = r"c:\Users\WIN10\Desktop\work\11th-week\法律风险提示-new\市监局-lawRisk-backend\市级初版-20251219\许可风险提示\101（生产科） 风险提示表(食品小作坊登记，市场监管部门) (1)_转自XLS.xlsx"
|
|||
|
|
target_sheet_name = "市级" # Will match partially
|
|||
|
|
target_permit_name = "食品小作坊登记证"
|
|||
|
|
|
|||
|
|
with open(file_path, "rb") as f:
|
|||
|
|
content = f.read()
|
|||
|
|
|
|||
|
|
conn_params = {
|
|||
|
|
"host": os.getenv("LIC_PG_HOST", "172.24.240.1"),
|
|||
|
|
"port": int(os.getenv("LIC_PG_PORT", "5432")),
|
|||
|
|
"user": os.getenv("LIC_PG_USER", "postgres"),
|
|||
|
|
"password": os.getenv("LIC_PG_PASSWORD", ""),
|
|||
|
|
"database": "licensing_risks",
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
try:
|
|||
|
|
conn = pg.connect(**conn_params)
|
|||
|
|
cur = conn.cursor()
|
|||
|
|
|
|||
|
|
# 1. Get IDs
|
|||
|
|
cur.execute("SELECT id FROM regions WHERE name = '市级'")
|
|||
|
|
region_id = cur.fetchone()[0]
|
|||
|
|
cur.execute("SELECT id FROM permits WHERE name = '食品小作坊登记证'")
|
|||
|
|
permit_id = cur.fetchone()[0]
|
|||
|
|
print(f"Targeting: Region={region_id}, Permit={permit_id}")
|
|||
|
|
|
|||
|
|
# 2. Parse Excel
|
|||
|
|
print("Parsing workbook...")
|
|||
|
|
parsed = _parse_import_workbook(content, "101.xlsx")
|
|||
|
|
sheets = parsed.get("sheets", {})
|
|||
|
|
|
|||
|
|
target_rows = []
|
|||
|
|
for s_name, s_data in sheets.items():
|
|||
|
|
if target_sheet_name in s_name:
|
|||
|
|
print(f"Found match sheet: {s_name}")
|
|||
|
|
target_rows = s_data.get("rows", [])
|
|||
|
|
break
|
|||
|
|
|
|||
|
|
if not target_rows:
|
|||
|
|
print("Target sheet not found!")
|
|||
|
|
return
|
|||
|
|
|
|||
|
|
print(f"Processing {len(target_rows)} rows...")
|
|||
|
|
restored_count = 0
|
|||
|
|
|
|||
|
|
for r in target_rows:
|
|||
|
|
# Check if it's the right permit
|
|||
|
|
if target_permit_name not in r.get("permit_name", ""):
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
# Ensure risk exists in 'risks' table (idempotent)
|
|||
|
|
risk_id = _ensure_risk(
|
|||
|
|
conn,
|
|||
|
|
risk_content=r["risk_content"],
|
|||
|
|
legal_basis=r.get("legal_basis"),
|
|||
|
|
document_no=r.get("document_no"),
|
|||
|
|
summary=r.get("summary"),
|
|||
|
|
remark=r.get("remark")
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
serial = r.get("serial_number")
|
|||
|
|
|
|||
|
|
# Upsert into region_permit_risks
|
|||
|
|
# This is the key patching step
|
|||
|
|
cur.execute("""
|
|||
|
|
INSERT INTO region_permit_risks (region_id, permit_id, risk_id, serial_number)
|
|||
|
|
VALUES (%s, %s, %s, %s)
|
|||
|
|
ON CONFLICT (region_id, permit_id, risk_id)
|
|||
|
|
DO UPDATE SET serial_number = EXCLUDED.serial_number
|
|||
|
|
""", (region_id, permit_id, risk_id, serial))
|
|||
|
|
|
|||
|
|
# Simple heuristic to say we 'restored' or updated it
|
|||
|
|
restored_count += 1
|
|||
|
|
if serial in [3, 5]: # The ones we know were missing
|
|||
|
|
print(f"Restored missing serial {serial} (Risk ID: {risk_id})")
|
|||
|
|
|
|||
|
|
conn.commit()
|
|||
|
|
print(f"Done. Processed {restored_count} risks for the target permit.")
|
|||
|
|
conn.close()
|
|||
|
|
except Exception as e:
|
|||
|
|
print(f"Error: {e}")
|
|||
|
|
|
|||
|
|
if __name__ == "__main__":
|
|||
|
|
patch_missing_risks()
|