ordinaceprojekt/Insurance/StavPojisteni/10 FinalSaveInsuranceStatusScript(R).py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# this script can be run several times on the same day, in such case it works incrementally

import sys
from pathlib import Path

# ==========================================
# PROJECT ROOT (import fix)
# ==========================================
PROJECT_ROOT = Path(__file__).resolve().parent.parent
sys.path.insert(0, str(PROJECT_ROOT))

import time
import logging
import pymysql
import pymysql.cursors
from datetime import date
from Knihovny.medicus_db import get_medicus_db
from Knihovny.vzpb2b_client import VZPB2BClient
from Knihovny.mysql_db import connect_mysql

# ==========================================
# LOGGING SETUP
# ==========================================
logging.basicConfig(
    filename="insurance_check.log",
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    encoding="utf-8"
)

console = logging.getLogger("console")
console.setLevel(logging.INFO)
handler = logging.StreamHandler()
handler.setFormatter(logging.Formatter("%(message)s"))
console.addHandler(handler)

def log_info(msg):
    logging.info(msg)
    console.info(msg)

def log_error(msg):
    logging.error(msg)
    console.error(msg)

# ==========================================
# MYSQL CONNECTION
# ==========================================
mysql = connect_mysql(cursorclass=pymysql.cursors.DictCursor)

# ==========================================
# SAVE RESULT
# ==========================================
def save_insurance_status(mysql_conn, rc, prijmeni, jmeno, k_datu, result, xml_text):
    """
    Uloží čistou odpověď VZP + identifikační údaje pacienta.
    Pojišťovna je VÝHRADNĚ z odpovědi VZP.
    """
    sql = """
    INSERT INTO vzp_stav_pojisteni
        (rc, prijmeni, jmeno, k_datu,
         stav, kod_pojistovny, nazev_pojistovny,
         pojisteni_kod, stav_vyrizeni, response_xml)
    VALUES (%s, %s, %s, %s,
            %s, %s, %s,
            %s, %s, %s)
    """

    with mysql_conn.cursor() as cur:
        cur.execute(sql, (
            rc,
            prijmeni,
            jmeno,
            k_datu,
            result["stav"],
            result["kodPojistovny"],      # ← VZP
            result["nazevPojistovny"],    # ← VZP
            result["pojisteniKod"],       # ← VZP
            result["stavVyrizeni"],       # ← VZP
            xml_text
        ))


# ==========================================
# CONFIGURATION
# ==========================================
PFX_PATH = Path(__file__).resolve().parent.parent / "Certificates" / "picka.pfx"
PFX_PASSWORD = "Vlado7309208104+"

ENV = "prod"
ICZ = "00000000"
DIC = "00000000"

# sanity check
if not PFX_PATH.exists():
    raise FileNotFoundError(f"PFX certificate not found: {PFX_PATH}")

# ==========================================
# INIT CONNECTIONS
# ==========================================
db = get_medicus_db()
vzp = VZPB2BClient(
    ENV,
    str(PFX_PATH),   # <-- important: pass as string
    PFX_PASSWORD,
    icz=ICZ,
    dic=DIC
)

# ==========================================
# FETCH REGISTERED PATIENTS
# ==========================================
patients = db.get_active_registered_patients()
log_info(f"Loaded {len(patients)} registered patients")

today = date.today()

# ==========================================
# FILTER: ONLY PATIENTS NOT CHECKED TODAY
# ==========================================
with mysql.cursor() as cur:
    cur.execute("SELECT DISTINCT rc FROM vzp_stav_pojisteni WHERE k_datu = %s", (today,))
    already_checked = {row["rc"] for row in cur.fetchall()}

patients_to_check = [
    (rc, prijmeni, jmeno)
    for rc, prijmeni, jmeno, poj in patients
    if rc not in already_checked
]

log_info(f"Incremental run: {len(patients_to_check)} patients to check today\n")

# ==========================================
# MAIN LOOP (1 of N)
# ==========================================
total = len(patients_to_check)

for idx, (rodcis, prijmeni, jmeno) in enumerate(patients_to_check, 1):

    log_info(f"[{idx}/{total}] Checking {prijmeni} {jmeno} ({rodcis})")

    try:
        xml = vzp.stav_pojisteni(rc=rodcis, k_datu=today.isoformat())
    except Exception as e:
        log_error(f"❌ VZP REQUEST FAILED for {rodcis}: {e}")
        time.sleep(2)
        continue

    if not xml.strip().startswith("<"):
        log_error(f"❌ INVALID XML for RC {rodcis}")
        time.sleep(2)
        continue

    try:
        result = vzp.parse_stav_pojisteni(xml)
    except Exception as e:
        log_error(f"❌ XML PARSE ERROR for RC {rodcis}: {e}")
        time.sleep(2)
        continue

    try:
        save_insurance_status(
            mysql,
            rodcis,
            prijmeni,
            jmeno,
            today,
            result,
            xml
        )
    except Exception as e:
        log_error(f"❌ MYSQL INSERT ERROR for RC {rodcis}: {e}")
        time.sleep(2)
        continue

    log_info(f"   ✔ OK ({result['nazevPojistovny']})")
    time.sleep(1.4)

db.close()
mysql.close()
log_info("\nDONE – incremental insurance check finished.")