administrator/ordinaceprojekt
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
ordinaceprojekt/Medicus/MedicusWithClaudeSelects/SELECTS.md
T
Vladimir Buzalka a9c143ba24 notebookvb
2026-04-29 06:51:47 +02:00

24 KiB
Raw Permalink Blame History

MedicusWithClaudeSelects – SQL dotazy

Registrovaní pacienti

Přesný select který Medicus používá pro záložku Registrovaní (zachycen přes FBScanner, dotaz č. 143).

Počet registrovaných pacientů

SELECT COUNT(*) FROM KAR
WHERE (vyrazen = 'N')
AND EXISTS (
    SELECT id FROM registr r
    JOIN icp i ON r.idicp = i.idicp
    WHERE r.idpac = kar.idpac
      AND (r.datum <= '2026-03-20')
      AND (r.datum_zruseni IS NULL OR r.datum_zruseni >= '2026-03-20')
      AND (r.priznak IN ('V','D','A'))
      AND (i.icp = '09305001')
      AND (i.odb = '001')
)

Vrátí: 1618 pacientů (ověřit na Windows).

Podmínky registrace – vysvětlení

  • vyrazen = 'N' – pacient není vyřazen z kartotéky
  • r.datum <= dnes – registrace již začala
  • r.datum_zruseni IS NULL OR r.datum_zruseni >= dnes – registrace dosud platí
  • r.priznak IN ('V','D','A') – aktivní příznak (ne 'Z' = zrušen, ne 'N')
  • i.icp = '09305001' – IČP naší ordinace
  • i.odb = '001' – odbornost praktický lékař

Skript pro Python

Viz count_registrovani.py v této složce – spustit na Windows.

Plný select Medicusu (seznam pacientů s metadaty)

SELECT
    KAR.DATNAR,
    KAR.IDPAC,
    KAR.INFORMACE,
    KAR.INFORMACE_COL,
    KAR.JMENO,
    KAR.POHLAVI,
    GPP.POJ,
    KAR.POZNAMKA,
    KAR.PRIJMENI,
    KAR.PRIJMENI_UP,
    (SELECT DATUM_REGISTRACE FROM SP_GETREGDAT(kar.IDPAC)) AS REGDATUM,
    KAR.REGISTROVAL,
    (SELECT PRIZNAK FROM SP_GETREGDAT(kar.IDPAC)) AS REGPRIZNAK,
    KAR.RODCIS,
    KAR.ROZENA,
    KAR.TITUL,
    KAR.TITULZA,
    KAR.TRVOBEC,
    KAR.TRVPSC,
    KAR.TRVULICE,
    KAR.VYRAZEN
FROM KAR
LEFT JOIN GETPACPOJ(KAR.IDPAC, '2026-03-20') GPP ON GPP.IDPAC = KAR.IDPAC
WHERE (vyrazen = 'N')
AND EXISTS (
    SELECT id FROM registr r
    JOIN icp i ON r.idicp = i.idicp
    WHERE r.idpac = kar.idpac
      AND (r.datum <= '2026-03-20')
      AND (r.datum_zruseni IS NULL OR r.datum_zruseni >= '2026-03-20')
      AND (r.priznak IN ('V','D','A'))
      AND (i.icp = '09305001')
      AND (i.odb = '001')
)
ORDER BY KAR.PRIJMENI_UP ASC, KAR.RODCIS ASC

Poznámka: GETPACPOJ a SP_GETREGDAT jsou uložené procedury Medicusu – fungují v kontextu Firebird připojení přes SYSDBA/masterkey.

Panel pacienta – hlavní UNION dotaz

Zachycen přes Firebird AuditTrace (default_trace.log) při otevření karty pacienta (IDPAC=9733, datum 28.03.2026). Dotaz vrací 28 UNION částí – každá část má pevnou strukturu sloupců: ID, VAR1, VAR2, DATE1, DATE2, TIME1, INT1, TEXT1, NUM1, NUM2.

Parametry při volání:

  • :IDPAC – ID pacienta (např. 9733)
  • :RODCIS – rodné číslo pacienta (např. '0308020152')
  • :DATUM – dnešní datum ve formátu YYYY-MM-DD (např. '2026-03-28')
  • :DATUM_CZ – dnešní datum ve formátu DD.MM.YYYY (např. '28.03.2026')
  • :ROK – aktuální rok (např. '2026')

Mapování UNION části → položka panelu Medicusu

ID (UNION část) Tabulka Položka v panelu Medicusu
BalickyPac BALICKYPAC Balíčky pacienta (aktivní/budoucí)
Dluh PLA / PLADET Dluh (nezaplacené faktury po splatnosti)
SouhlasPac HISTDOC / SOUHLASPACSABL Souhlasy pacienta
sCenaVykZUM DOKLADD / LECD Cena výkonů v aktuálním roce (ZUM)
Registrl KAR Kdo registroval pacienta
OseLekPrak KARUZIV_SEL Ošetřující lékař (odb. 001/002)
SledLek SLEDLEK Sledující lékař (specialista)
HistDoc HISTDOC Historie dokumentů (posledních 10)
LastSms SMS Datum poslední odeslané SMS
PozadLekar DOKLADH Požadující lékař (EICZ) z posledního dokladu
Prilohy FILES Přílohy pacienta (posledních 10)
Objednavky OBJOBJ Objednávky od dnešního dne
OseLek KARUZIV_SEL Všichni lékaři přiřazení ke kartě
PeProhlidky PREH / PREINIH Preventivní a examinační prohlídky
Medikace MEDIKACE Aktuální medikace (platná k dnešku)
NextDispenz DISPAC / DISSKU Příští dispenzarizace (s termínem)
Dispenz DISPAC / DISSKU Dispenzarizace – všechny záznamy
Prohlidky PREPRI / PREINIH Preventivní prohlídky – záznamy
NextOck OCKPRI / KLK Příští očkování (plánované)
LastVykon DOKLADD / DOKLADH Poslední výkon (kód + datum)
LastDekurs DEKURS Poslední dekurz (datum)
Karta KAR Informace a poznámka z karty
Saldo PACIENT_SALDO(SP) Saldo pacienta (funkce)
Anamneza ANAMNEZA Anamnéza + krevní skupina
Ockovani OCKZAZ Očkovací záznamy (max 20, seskupeno po látce)
NeschopenOd NES Aktuální neschopenka (od – do)
Alergie ANAMNEZA Alergie (z posledního záznamu anamnézy)
Pojistovna ICP / ICZ IDICP naší ordinace pro pojišťovnu pacienta

SQL dotaz (parametrizovaný)

SELECT
  cast('BalickyPac' as varchar(11)) as ID, substring(cast(BPAC.KOD as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(BPAC.CENPASMO as VARCHAR(70)) from 1 for 30) as VAR2, cast(BPAC.DATUMOD as DATE) as DATE1, cast(BPAC.DATUMDO as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from BALICKYPAC BPAC left join SP_BALICKYPAC_PRIZNAK(BPAC.ID, ':DATUM_CZ') PRI on 1 = 1 where BPAC.IDPAC = :IDPAC and PRI.PRIZNAK in ('A', 'B')

UNION SELECT
  cast('Dluh' as varchar(11)) as ID, substring(cast(P.MENA as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast((SUM(P.CENA - P.SLEVAC) - (SUM(P.PLATBA) + SUM((COALESCE((select SUM(case ZD.TYP when 'R' then ZD.CELKEM else -ZD.CELKEM end) from PLADET ZD where ZD.IDPLA = P.IDPLA and (ZD.TYP <> P.DOKLADTYP) and (ZD.TYP <> '|') and ((ZD.CENA < 0) or (ZD.TYP = 'R'))), 0))))) as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 FROM PLA P WHERE (P.IDPAC = :IDPAC) AND (P.DOKLADTYP = 'F') AND (P.STORNO IS NULL) AND (P.NENISALDO = 'F') AND ((P.SPLATNOST IS NULL) OR (P.SPLATNOST < ':DATUM')) AND (P.VALID = 'F') GROUP BY P.MENA

UNION SELECT
  cast('SouhlasPac' as varchar(11)) as ID, substring(cast(case when S.NAZEV is null then case when H.TYP = 'ZSOUPOS' then 'Souhlas/Nesouhlas s poskytnutím zdravotních služeb nezletilému' when H.TYP = 'ZSOUPOZ' then 'Souhlas zákonného zástupce nezletilého pacienta staršího 15ti let' when H.TYP = 'ZSOUPO2' then 'Nesouhlas s poskytnutím zdravotních služeb - povinné očkování' when H.TYP = 'ZPOSIN2' then 'Určení osoby oprávněné dle zákona o zdravotních službách' when H.TYP = 'OdmPece' then 'Prohlášení o odmítnutí zdravotní péče pacientem - Negativní revers' end else S.NAZEV end as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(H.DATUM as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from HISTDOC H left join SOUHLASPACSABL S on H.IDSOUHLASPACSABL = S.ID where H.TYP in ('IndSou', 'ZSOUPOS', 'ZSOUPOZ', 'ZSOUPO2', 'ZPOSIN2', 'OdmPece') and H.IDPACI = :IDPAC

UNION SELECT
  cast('sCenaVykZUM' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(coalesce(sum(cenabod),0) + coalesce(sum(cenamat),0) as NUMERIC(15,2)) as NUM1, cast((select coalesce(sum(cena),0) from LECD d where d.RODCIS = ':RODCIS' and extract(year from d.DATOSE) = ':ROK' and ((d.KAT is null) or (d.KAT <> 'N')) and exists (select h.IDLEC from LECH h where h.IDLEC = d.IDLEC and h.POJ = '207' and h.ICZ in ('09305001'))) as NUMERIC(15,2)) as NUM2 from DOKLADD d where d.RODCIS = ':RODCIS' and extract(year from d.DATOSE) = ':ROK' and ((d.KAT is null) or (d.KAT <> 'N' and d.KAT <> 'K' and d.KAT <> 'A')) and exists (select h.IDHLAV from DOKLADH h where h.IDHLAV = d.IDHLAV and h.POJ = '207' and h.ICZ in ('09305001'))

UNION SELECT
  cast('Registrl' as varchar(11)) as ID, substring(cast(REGISTROVAL as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from KAR where IDPAC = :IDPAC

UNION SELECT
  cast('OseLekPrak' as varchar(11)) as ID, substring(cast(F_CONCAT(PRIJMENI, F_CONCAT(JMENO, TITUL, ', '), ' ') as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(TITUL2 as VARCHAR(70)) from 1 for 30) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(CAST(ODBORN as INTEGER) as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 FROM KARUZIV_SEL(:IDPAC, 'T') WHERE ODBORN in ('001', '002')

UNION SELECT
  cast('SledLek' as varchar(11)) as ID, substring(cast(KOD as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(LEK as VARCHAR(70)) from 1 for 30) as VAR2, cast(DATUM as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from SLEDLEK where IDPAC = :IDPAC and DATUM <= ':DATUM'

UNION SELECT
  first 10 cast('HistDoc' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(DATUM as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from HISTDOC where IDPACI = :IDPAC and STAV is NULL and IDZARPR = 2 and IDODDPR = 2 and IDPRACPR = 2

UNION SELECT
  first 1 cast('LastSms' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(MAX(SENDTIME) as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from SMS where PACID = :IDPAC and SENDTIME is not NULL and not(STATUS in (100,1000))

UNION SELECT
  first 1 cast('PozadLekar' as varchar(11)) as ID, substring(cast(H.EICZ as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(H.EODZ as VARCHAR(70)) from 1 for 30) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from DOKLADH H where H.IDHLAV = (select first 1 I.IDHLAV from DOKLADH I where I.RODCIS = ':RODCIS' and I.EICZ is not NULL order by I.IDHLAV desc)

UNION SELECT
  first 10 cast('Prilohy' as varchar(11)) as ID, substring(cast(FILENAME as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from FILES where IDPAC = :IDPAC

UNION SELECT
  first 10 cast('Objednavky' as varchar(11)) as ID, substring(cast(F_CONCAT(U.PRIJMENI, F_CONCAT(U.JMENO, U.TITUL, ', '), ' ') as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(PRAC as VARCHAR(70)) from 1 for 30) as VAR2, cast(DATUM as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(CAS as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from OBJOBJ O join PRACOVISTE P on (P.ID = O.IDPRAC) join UZIVATEL U on (U.IDUZI = O.IDUZI) where IDPAC = :IDPAC and DATUM >= ':DATUM_CZ'

UNION SELECT
  cast('OseLek' as varchar(11)) as ID, substring(cast(F_CONCAT(PRIJMENI, F_CONCAT(JMENO, TITUL, ', '), ' ') as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(TITUL2 as VARCHAR(70)) from 1 for 30) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 FROM KARUZIV_SEL(:IDPAC, 'T')

UNION SELECT
  cast('PeProhlidky' as varchar(11)) as ID, substring(cast(NAZEV as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(DATUM as DATE) as DATE1, cast(TERMIN as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from PREH join PREINIH on (PREH.IDPREINI = PREINIH.IDPREINI) where IDPAC = :IDPAC

UNION SELECT
  cast('Medikace' as varchar(11)) as ID, substring(cast(NAZ as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(PLATI_OD as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from MEDIKACE where IDPAC = :IDPAC and PLATI_OD <= ':DATUM_CZ' and (PLATI_DO >= ':DATUM_CZ' or PLATI_DO is NULL)

UNION SELECT
  cast('NextDispenz' as varchar(11)) as ID, substring(cast(NAZEV as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(SKUPINA as VARCHAR(70)) from 1 for 30) as VAR2, cast(PRISTI as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from DISPAC join DISSKU on (DISSKU.IDDIS = DISPAC.IDDIS) where IDPAC = :IDPAC and PRISTI is not NULL

UNION SELECT
  cast('Dispenz' as varchar(11)) as ID, substring(cast(NAZEV as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(SKUPINA as VARCHAR(70)) from 1 for 30) as VAR2, cast(DATZAR as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from DISPAC join DISSKU on (DISSKU.IDDIS = DISPAC.IDDIS) where IDPAC = :IDPAC

UNION SELECT
  cast('Prohlidky' as varchar(11)) as ID, substring(cast(NAZEV as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(DATUM as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from PREPRI join PREINIH on (PREPRI.IDPREINI = PREINIH.IDPREINI) where IDPAC = :IDPAC and datum is not null

UNION SELECT
  cast('NextOck' as varchar(11)) as ID, substring(cast(coalesce(NAZ,ZKRATKA) as VARCHAR(254)) from 1 for 250) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(DATUMD as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from OCKPRI o left join KLK k on o.ZKRATKA = k.KOD where IDPAC = :IDPAC

UNION SELECT
  first 1 cast('LastVykon' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, substring(cast(D.KOD as VARCHAR(70)) from 1 for 30) as VAR2, cast(D.DATOSE as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from DOKLADD D where D.ID = (select first 1 dd.id from dokladd dd join dokladh dh on (dh.idhlav = dd.idhlav) where dd.rodcis = ':RODCIS' and (dh.hodb = '001' or dh.hodb is null) order by dd.datose desc)

UNION SELECT
  first 1 cast('LastDekurs' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(MAX(DATUM) as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from DEKURS where IDPAC = :IDPAC and (IDPRAC = 2 or IDPRAC = -1)

UNION SELECT
  first 1 cast('Karta' as varchar(11)) as ID, substring(cast(INFORMACE as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(CIZINEC as VARCHAR(70)) from 1 for 30) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(INFORMACE_COL as INTEGER) as INT1, POZNAMKA as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from KAR where IDPAC = :IDPAC

UNION SELECT
  first 1 cast('Saldo' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(SALDO as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from PACIENT_SALDO(:IDPAC, 1, 0, 0)

UNION SELECT
  first 1 cast('Anamneza' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, substring(cast(KREVSKUP as VARCHAR(70)) from 1 for 30) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, ANAMNEZA as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from ANAMNEZA where ID = (select first 1 ID from ANAMNEZA where IDPAC = :IDPAC order by DATUM DESC, ID desc)

UNION SELECT
  first 20 cast('Ockovani' as varchar(11)) as ID, substring(cast(ockzaz.LATKA as VARCHAR(254)) from 1 for 250) as VAR1, substring(cast(ockzaz.ZKRATKA as VARCHAR(70)) from 1 for 30) as VAR2, cast(max(ockzaz.DATUM) as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from ockzaz where ockzaz.idpac = :IDPAC group by ockzaz.ZKRATKA, ockzaz.LATKA

UNION SELECT
  first 1 cast('NeschopenOd' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(MAX(ZACNES) as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from NES where (IDPAC = :IDPAC) and (ZACNES <= ':DATUM_CZ') and ((KONNES is NULL) or (KONNES > ':DATUM_CZ')) and (STORNO = 'F')

UNION SELECT
  first 1 cast('Alergie' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(NULL as INTEGER) as INT1, ALERGIE as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from ANAMNEZA where IDPAC = :IDPAC and ID = (select first 1 ID from ANAMNEZA where IDPAC = :IDPAC and DATUM <= ':DATUM_CZ' order by DATUM desc, ID desc)

UNION SELECT
  first 1 cast('Pojistovna' as varchar(11)) as ID, cast(NULL as VARCHAR(254)) as VAR1, cast(NULL as VARCHAR(70)) as VAR2, cast(NULL as DATE) as DATE1, cast(NULL as DATE) as DATE2, cast(NULL as TIMESTAMP) as TIME1, cast(P.IDICP as INTEGER) as INT1, NULL as TEXT1, cast(NULL as NUMERIC(15,2)) as NUM1, cast(NULL as NUMERIC(15,2)) as NUM2 from ICP P join ICZ Z on (Z.IDICZ = P.IDICZ) where Z.POJ = '207' and P.ODB = '001'

Ošetřující lékař pacienta

Kde je uložen

Ošetřující lékař není přímo v tabulce KAR. Je uložen v tabulce KARUZIV a čte se přes stored procedure KARUZIV_SEL.

Tabulka KARUZIV

Vazba pacient → lékař. Jeden pacient může mít více záznamů (více lékařů/pracovišť).

Sloupec Popis
IDPAC FK na KAR – pacient
IDLEKAR FK na LEKARI – externí lékař (specialista, cizí ordinace)
IDUZI FK na UZIVATEL – interní uživatel Medicusu (vlastní lékař)
IDPRAC FK na PRACOVISTE – pracoviště
IDODD FK na ODDEL – oddělení
AUTOMAT 'F' = ručně přiřazen, 'T' = automaticky

Pokud je vyplněn IDLEKAR → jde se do tabulky LEKARI (cizí lékaři). Pokud je vyplněn IDUZI → jde se do tabulky UZIVATEL (lékaři v Medicusu).

Tabulka REGISTR

Druhý zdroj – registrace pacienta u lékaře/pojišťovny.

Sloupec Popis
IDPAC FK na KAR
IDICP FK na ICP – identifikace pracoviště/pojišťovny
IDUZI FK na UZIVATEL – lékař (nepovinný, dohledává se přes ICP)
DATUM Datum začátku registrace
DATUM_ZRUSENI Datum zrušení (NULL = stále platná)
PRIZNAK 'V'/'D'/'A' = aktivní; 'Z'/'N' = zrušená/neaktivní

Stored procedure KARUZIV_SEL(IIDPAC, INCL_AUTOMAT)

Parametry:

  • IIDPAC – IDPAC pacienta
  • INCL_AUTOMAT – 'T' = vrátit i automaticky přiřazené, 'F' = jen ruční

Vrací sloupce: ID, IDPAC, IDODD, ODD, IDUZI, IDPRAC, IDLEKAR, AUTOMAT, TITUL, PRIJMENI, JMENO, TITUL2, ODBORN

Logika (3 průchody):

  1. KARUZIV kde AUTOMAT = 'F' – ručně přiřazení lékaři
  2. KARUZIV kde AUTOMAT = 'T' – automaticky přiřazení (jen pokud INCL_AUTOMAT = 'T')
  3. REGISTR – aktivní registrace (datum platný, PRIZNAK ≠ 'Z'/'N', nezrušená)
    • přes IDICP → ICP → PRACOVISTE → PRACUZIV → UZIVATEL

Použití v panelu pacienta (UNION dotaz)

-- Ošetřující lékař praktický (odbornost 001 nebo 002)
SELECT ... FROM KARUZIV_SEL(:IDPAC, 'T') WHERE ODBORN in ('001', '002')
-- → UNION část ID = 'OseLekPrak'

-- Všichni lékaři přiřazení ke kartě
SELECT ... FROM KARUZIV_SEL(:IDPAC, 'T')
-- → UNION část ID = 'OseLek'

Zapojené tabulky (přehled)

KAR
 └── KARUZIV ──► LEKARI        (externí lékaři, specialisté)
              └► UZIVATEL       (interní lékaři v Medicusu)
              └► PRACOVISTE     (pracoviště / odbornost)
              └► ODDEL          (oddělení)
 └── REGISTR ──► ICP            (identifikace pracoviště)
              └► PRACOVISTE ──► PRACUZIV ──► UZIVATEL

Barevné rozlišení v GUI Medicusu

  • Černá = záznam pochází z KARUZIV (explicitně přiřazený ošetřující lékař, IDUZI vyplněno)
  • Červená = záznam pochází z REGISTR (registrující lékař – SP vrací ID = 0 - REGISTR.ID)
  • Červená (ext.) = záznam z KARUZIV kde je vyplněno IDLEKAR (externí lékař z tabulky LEKARI)

Duplikát ošetřujícího lékaře – known issue

KARUZIV_SEL prochází vždy oba zdroje (KARUZIV i REGISTR) bez ohledu na to, zda už byl lékař nalezen. Pokud má pacient záznam v KARUZIV (černá) i v REGISTR (červená) se stejným lékařem, zobrazí se dvakrát.

Příčina: SP neobsahuje podmínku „přeskoč REGISTR, pokud KARUZIV již vrátil výsledky".

Stav ordinace Buzalková (duben 2026):

  • Všech 1620 registrovaných pacientů má v KARUZIV záznam IDUZI=4 (Michaela, černá)
  • 1537 pacientů má v REGISTR IDUZI=4 (Michaela, červená) → duplikát
  • Chování je konzistentní, ale GUI zobrazuje oba řádky – čeká se na vyjádření supportu Medicusu

Možná řešení (zatím neaplikováno):

  • A) Smazat KARUZIV záznamy → zůstane jen červená z REGISTR (jeden řádek)
  • B) Nastavit REGISTR.IDUZI zpět na NULL → REGISTR path hledá přes PRACOVISTE (najde Michalu jako první NOSVYK='A') → duplikát stále, ale přes jiný lookup
  • C) Řešení přes support Medicusu
Reference in New Issue View Git Blame Copy Permalink