medicus/MedicusWithClaudeFaktury/find_edavky2.py

# -*- coding: utf-8 -*-
import sys, io
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')
import fdb

conn = fdb.connect(
    dsn=r'localhost:c:\medicus 3\data\medicus.fdb',
    user='SYSDBA', password='masterkey', charset='win1250'
)
cur = conn.cursor()

# Ze screenshotu vime:
# - sloupce: Vytvoreno, Odeslano, ZP, Stav, Zpravy, Podaci c., Faktura
# - ZP: 111, 201, 205, 207, 209, 211
# - Podaci c.: 58933293, 174804160, 26082877, D01F260218...
# - datum: 01.03.2026, 05.03.2026, 12.03.2026, 23.03.2026, 24.03.2026
# - typ: "Reg. listy" (bez faktury), vykonove davky (s fakturou)

print("=" * 60)
print("1. EOCK_DAVKA - sloupce a ukazka")
print("=" * 60)
cur.execute("""
    SELECT rf.RDB$FIELD_NAME
    FROM RDB$RELATION_FIELDS rf
    WHERE rf.RDB$RELATION_NAME = 'EOCK_DAVKA'
    ORDER BY rf.RDB$FIELD_POSITION
""")
eock_cols = [r[0].strip() for r in cur.fetchall()]
print(f"  Sloupce: {eock_cols}")
cur.execute("SELECT COUNT(*) FROM EOCK_DAVKA")
print(f"  Pocet: {cur.fetchone()[0]}")
safe = [c for c in eock_cols if c not in ('DATA', 'DAVKA', 'ODPOVED')]
if safe:
    cur.execute(f"SELECT FIRST 3 {', '.join(safe)} FROM EOCK_DAVKA ORDER BY ID DESC")
    for r in cur.fetchall():
        print(f"  {dict(zip(safe, r))}")

print()
print("=" * 60)
print("2. FAK - zaznamy z brezna 2026")
print("=" * 60)
cur.execute("""
    SELECT ID, CISFAK, POJ, DATVYS, DATODE, OBDOB, CENA, DRUH, STAV
    FROM FAK
    WHERE DATVYS >= '2026-03-01' OR DATODE >= '2026-03-01'
    ORDER BY ID DESC
""")
rows = cur.fetchall()
print(f"  Celkem: {len(rows)}")
for r in rows:
    print(f"  ID={r[0]} CISFAK={r[1]} POJ={r[2]} DATVYS={r[3]} DATODE={r[4]} OBDOB={r[5]} CENA={r[6]} DRUH={r[7]} STAV={r[8]}")

print()
print("=" * 60)
print("3. Hledej tabulky ktere maji sloupec VYTVORENO nebo PODACI")
print("=" * 60)
cur.execute("""
    SELECT DISTINCT rf.RDB$RELATION_NAME, rf.RDB$FIELD_NAME
    FROM RDB$RELATION_FIELDS rf
    JOIN RDB$RELATIONS r ON r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME
    WHERE r.RDB$SYSTEM_FLAG = 0
    AND (TRIM(rf.RDB$FIELD_NAME) LIKE '%VYTVOR%'
         OR TRIM(rf.RDB$FIELD_NAME) LIKE '%PODACI%')
    ORDER BY rf.RDB$RELATION_NAME
""")
for r in cur.fetchall():
    print(f"  {r[0].strip():<40} {r[1].strip()}")

print()
print("=" * 60)
print("4. Hledej tabulky ktere maji POJ sloupec a zaznamy z brezna 2026")
print("=" * 60)
# Vsechny tabulky s POJ sloupcem
cur.execute("""
    SELECT DISTINCT rf.RDB$RELATION_NAME
    FROM RDB$RELATION_FIELDS rf
    JOIN RDB$RELATIONS r ON r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME
    WHERE r.RDB$SYSTEM_FLAG = 0
    AND TRIM(rf.RDB$FIELD_NAME) = 'POJ'
    ORDER BY rf.RDB$RELATION_NAME
""")
poj_tables = [r[0].strip() for r in cur.fetchall()]

for table in poj_tables:
    # Najdi vsechny datumove sloupce
    cur.execute(f"""
        SELECT rf.RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS rf
        WHERE rf.RDB$RELATION_NAME = '{table}'
        AND TRIM(rf.RDB$FIELD_NAME) LIKE '%DAT%'
    """)
    date_cols = [r[0].strip() for r in cur.fetchall()]
    for dc in date_cols:
        try:
            cur.execute(f"SELECT COUNT(*) FROM {table} WHERE {dc} >= '2026-03-01'")
            cnt = cur.fetchone()[0]
            if cnt > 0:
                print(f"  {table:<40} {dc}: {cnt} od 2026-03-01")
        except Exception:
            pass

print()
print("=" * 60)
print("5. Prohledej vsechny tabulky - ktere maji ~15 zaznamu od 2026-03-01")
print("   (v eDavky screenshotu je ~15 radku z brezna)")
print("=" * 60)
cur.execute("""
    SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
    WHERE RDB$SYSTEM_FLAG = 0
    ORDER BY RDB$RELATION_NAME
""")
all_tables = [r[0].strip() for r in cur.fetchall()]

# Hledej tabulky s datumovym sloupcem a 10-50 zaznamy od 2026-03
hits = []
for table in all_tables:
    cur.execute(f"""
        SELECT rf.RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS rf
        WHERE rf.RDB$RELATION_NAME = '{table}'
        AND (TRIM(rf.RDB$FIELD_NAME) IN ('ODESLANO', 'VYTVORENO', 'DATODE', 'DATVYS', 'DATUM_ODESLANI'))
    """)
    date_cols = [r[0].strip() for r in cur.fetchall()]
    for dc in date_cols:
        try:
            cur.execute(f"SELECT COUNT(*) FROM {table} WHERE {dc} >= '2026-03-01'")
            cnt = cur.fetchone()[0]
            if 5 <= cnt <= 100:
                hits.append((table, dc, cnt))
        except Exception:
            pass

hits.sort(key=lambda x: x[2])
for table, dc, cnt in hits:
    # Vypis sloupce teto tabulky
    cur.execute(f"""
        SELECT rf.RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS rf
        WHERE rf.RDB$RELATION_NAME = '{table}'
        ORDER BY rf.RDB$FIELD_POSITION
    """)
    cols = [r[0].strip() for r in cur.fetchall()]
    print(f"  {table:<40} {dc}: {cnt} | sloupce: {cols[:10]}")

conn.close()
print("\nHotovo.")