physiobase

physiobase/internal/store/patients.go
patients.go
⎇
Raw
1package store
2
3import (
4 "context"
5 "database/sql"
6 "log/slog"
7 "strings"
8 "time"
9)
10
11type Patient struct {
12 ID int64
13 Name, Dob, Phone, Notes string
14 Email string
15 EmailConsent bool // Einwilligung in den Versand von Gesundheitsinformationen per E-Mail
16 Archived bool
17 Created, Updated time.Time
18 Befunde, Sessions int
19 LastDate string // letzter Befund oder letzte Behandlung (YYYY-MM-DD)
20}
21
22type Befund struct {
23 ID, PatientID int64
24 Date, Title string
25 Data string // JSON, siehe Paket befund
26 Created, Updated time.Time
27}
28
29type Session struct {
30 ID, PatientID int64
31 Date, Goal, Note string
32}
33
34// ---------- Patienten ----------
35
36func (s *Store) Patients(ctx context.Context, q string, archived bool) ([]Patient, error) {
37 rows, err := s.db.QueryContext(ctx, `SELECT p.id, p.name, p.dob, p.phone, p.notes, p.email, p.email_consent, p.archived, p.created_at, p.updated_at,
38 (SELECT count(*) FROM befunde b WHERE b.patient_id = p.id),
39 (SELECT count(*) FROM sessions x WHERE x.patient_id = p.id),
40 COALESCE(max((SELECT max(date) FROM befunde b WHERE b.patient_id = p.id),
41 (SELECT max(date) FROM sessions x WHERE x.patient_id = p.id)), '')
42 FROM patients p
43 WHERE p.archived = ? AND (? = '' OR p.name LIKE '%' || ? || '%' OR p.dob LIKE '%' || ? || '%')
44 ORDER BY p.updated_at DESC LIMIT 500`, archived, q, q, q)
45 if err != nil {
46 return nil, err
47 }
48 defer rows.Close()
49 var out []Patient
50 for rows.Next() {
51 p, err := scanPatient(rows, true)
52 if err != nil {
53 return nil, err
54 }
55 out = append(out, p)
56 }
57 return out, rows.Err()
58}
59
60func scanPatient(sc scanner, withStats bool) (Patient, error) {
61 var p Patient
62 var c, u int64
63 var last sql.NullString
64 dest := []any{&p.ID, &p.Name, &p.Dob, &p.Phone, &p.Notes, &p.Email, &p.EmailConsent, &p.Archived, &c, &u}
65 if withStats {
66 dest = append(dest, &p.Befunde, &p.Sessions, &last)
67 }
68 err := sc.Scan(dest...)
69 p.Created, p.Updated, p.LastDate = time.Unix(c, 0), time.Unix(u, 0), last.String
70 return p, err
71}
72
73func (s *Store) Patient(ctx context.Context, id int64) (Patient, error) {
74 p, err := scanPatient(s.db.QueryRowContext(ctx, `SELECT id, name, dob, phone, notes, email, email_consent, archived, created_at, updated_at
75 FROM patients WHERE id = ?`, id), false)
76 return p, notFound(err)
77}
78
79func (s *Store) CreatePatient(ctx context.Context, p Patient) (int64, error) {
80 res, err := s.db.ExecContext(ctx, `INSERT INTO patients (name, dob, phone, notes, email, email_consent) VALUES (?, ?, ?, ?, ?, ?)`,
81 p.Name, p.Dob, p.Phone, p.Notes, p.Email, p.EmailConsent)
82 if err != nil {
83 return 0, err
84 }
85 return res.LastInsertId()
86}
87
88func (s *Store) UpdatePatient(ctx context.Context, p Patient) error {
89 res, err := s.db.ExecContext(ctx, `UPDATE patients SET name=?, dob=?, phone=?, notes=?, email=?, email_consent=?,
90 updated_at=unixepoch() WHERE id=?`, p.Name, p.Dob, p.Phone, p.Notes, p.Email, p.EmailConsent, p.ID)
91 if err != nil {
92 return err
93 }
94 if n, _ := res.RowsAffected(); n == 0 {
95 return ErrNotFound
96 }
97 return nil
98}
99
100func (s *Store) SetArchived(ctx context.Context, id int64, archived bool) error {
101 _, err := s.db.ExecContext(ctx, `UPDATE patients SET archived=?, updated_at=unixepoch() WHERE id=?`, archived, id)
102 return err
103}
104
105func (s *Store) DeletePatient(ctx context.Context, id int64) error {
106 _, err := s.db.ExecContext(ctx, `DELETE FROM patients WHERE id = ?`, id)
107 return err
108}
109
110// touchPatient sortiert den Patienten in der Liste nach oben; ein Fehler ist nicht kritisch.
111func (s *Store) touchPatient(ctx context.Context, id int64) {
112 if _, err := s.db.ExecContext(ctx, `UPDATE patients SET updated_at = unixepoch() WHERE id = ?`, id); err != nil {
113 slog.Warn("patient updated_at", "id", id, "err", err)
114 }
115}
116
117// FindPatient sucht exakt nach Name (+ Geburtsdatum, falls angegeben) – für den Import.
118func (s *Store) FindPatient(ctx context.Context, name, dob string) (int64, bool, error) {
119 var id int64
120 err := s.db.QueryRowContext(ctx, `SELECT id FROM patients WHERE lower(trim(name)) = lower(trim(?))
121 AND (? = '' OR dob = '' OR dob = ?) ORDER BY id LIMIT 1`, name, dob, dob).Scan(&id)
122 if err == sql.ErrNoRows {
123 return 0, false, nil
124 }
125 return id, err == nil, err
126}
127
128// ---------- Befunde ----------
129
130const befundCols = `id, patient_id, date, title, data, created_at, updated_at`
131
132func scanBefund(sc scanner) (Befund, error) {
133 var b Befund
134 var c, u int64
135 err := sc.Scan(&b.ID, &b.PatientID, &b.Date, &b.Title, &b.Data, &c, &u)
136 b.Created, b.Updated = time.Unix(c, 0), time.Unix(u, 0)
137 return b, err
138}
139
140func (s *Store) Befunde(ctx context.Context, pid int64) ([]Befund, error) {
141 rows, err := s.db.QueryContext(ctx, `SELECT `+befundCols+` FROM befunde WHERE patient_id = ? ORDER BY date DESC, id DESC`, pid)
142 if err != nil {
143 return nil, err
144 }
145 defer rows.Close()
146 var out []Befund
147 for rows.Next() {
148 b, err := scanBefund(rows)
149 if err != nil {
150 return nil, err
151 }
152 out = append(out, b)
153 }
154 return out, rows.Err()
155}
156
157func (s *Store) Befund(ctx context.Context, id int64) (Befund, error) {
158 b, err := scanBefund(s.db.QueryRowContext(ctx, `SELECT `+befundCols+` FROM befunde WHERE id = ?`, id))
159 return b, notFound(err)
160}
161
162func (s *Store) CreateBefund(ctx context.Context, b Befund) (int64, error) {
163 if b.Date == "" {
164 b.Date = time.Now().Format("2006-01-02")
165 }
166 if strings.TrimSpace(b.Title) == "" {
167 b.Title = "Befund"
168 }
169 if b.Data == "" {
170 b.Data = "{}"
171 }
172 res, err := s.db.ExecContext(ctx, `INSERT INTO befunde (patient_id, date, title, data) VALUES (?, ?, ?, ?)`,
173 b.PatientID, b.Date, b.Title, b.Data)
174 if err != nil {
175 return 0, err
176 }
177 s.touchPatient(ctx, b.PatientID)
178 return res.LastInsertId()
179}
180
181func (s *Store) UpdateBefund(ctx context.Context, b Befund) error {
182 res, err := s.db.ExecContext(ctx, `UPDATE befunde SET date=?, title=?, data=?, updated_at=unixepoch() WHERE id=?`,
183 b.Date, b.Title, b.Data, b.ID)
184 if err != nil {
185 return err
186 }
187 if n, _ := res.RowsAffected(); n == 0 {
188 return ErrNotFound
189 }
190 s.touchPatient(ctx, b.PatientID)
191 return nil
192}
193
194func (s *Store) DeleteBefund(ctx context.Context, id int64) (int64, error) {
195 var pid int64
196 err := s.db.QueryRowContext(ctx, `DELETE FROM befunde WHERE id = ? RETURNING patient_id`, id).Scan(&pid)
197 return pid, notFound(err)
198}
199
200// ---------- Behandlungsverlauf ----------
201
202func (s *Store) Sessions(ctx context.Context, pid int64) ([]Session, error) {
203 rows, err := s.db.QueryContext(ctx, `SELECT id, patient_id, date, goal, note FROM sessions
204 WHERE patient_id = ? ORDER BY date DESC, id DESC`, pid)
205 if err != nil {
206 return nil, err
207 }
208 defer rows.Close()
209 var out []Session
210 for rows.Next() {
211 var x Session
212 if err := rows.Scan(&x.ID, &x.PatientID, &x.Date, &x.Goal, &x.Note); err != nil {
213 return nil, err
214 }
215 out = append(out, x)
216 }
217 return out, rows.Err()
218}
219
220func (s *Store) AddSession(ctx context.Context, x Session) error {
221 if x.Date == "" {
222 x.Date = time.Now().Format("2006-01-02")
223 }
224 _, err := s.db.ExecContext(ctx, `INSERT INTO sessions (patient_id, date, goal, note) VALUES (?, ?, ?, ?)`, x.PatientID, x.Date, x.Goal, x.Note)
225 if err == nil {
226 s.touchPatient(ctx, x.PatientID)
227 }
228 return err
229}
230
231func (s *Store) DeleteSession(ctx context.Context, id int64) (int64, error) {
232 var pid int64
233 err := s.db.QueryRowContext(ctx, `DELETE FROM sessions WHERE id = ? RETURNING patient_id`, id).Scan(&pid)
234 return pid, notFound(err)
235}
236
237// ---------- Wissen ↔ Patient ----------
238
239func (s *Store) PatientEntries(ctx context.Context, pid int64) ([]Entry, error) {
240 return s.entries(ctx, `SELECT `+entryCols+` FROM patient_entries pe JOIN entries e ON e.id = pe.entry_id
241 WHERE pe.patient_id = ? ORDER BY e.kind, e.title COLLATE NOCASE`, pid)
242}
243
244func (s *Store) LinkPatientEntry(ctx context.Context, pid, eid int64) error {
245 _, err := s.db.ExecContext(ctx, `INSERT OR IGNORE INTO patient_entries (patient_id, entry_id) VALUES (?, ?)`, pid, eid)
246 return err
247}
248
249func (s *Store) UnlinkPatientEntry(ctx context.Context, pid, eid int64) error {
250 _, err := s.db.ExecContext(ctx, `DELETE FROM patient_entries WHERE patient_id = ? AND entry_id = ?`, pid, eid)
251 return err
252}
253
254// EntriesOfKind z. B. alle Tests als Vorschlagsliste im Befund.
255func (s *Store) EntriesOfKind(ctx context.Context, kinds ...string) ([]Entry, error) {
256 if len(kinds) == 0 {
257 return s.entries(ctx, `SELECT `+entryCols+` FROM entries e ORDER BY e.kind, e.title COLLATE NOCASE`)
258 }
259 ph := strings.TrimSuffix(strings.Repeat("?,", len(kinds)), ",")
260 args := make([]any, len(kinds))
261 for i, k := range kinds {
262 args[i] = k
263 }
264 return s.entries(ctx, `SELECT `+entryCols+` FROM entries e WHERE e.kind IN (`+ph+`) ORDER BY e.kind, e.title COLLATE NOCASE`, args...)
265}
266
267// ---------- Handouts je Patient ----------
268
269func (s *Store) HandoutsForPatient(ctx context.Context, pid int64) ([]Handout, error) {
270 rows, err := s.db.QueryContext(ctx, `SELECT h.id, h.title, h.recipient, h.notes, h.created_at, h.updated_at,
271 (SELECT count(*) FROM handout_items i WHERE i.handout_id = h.id)
272 FROM handouts h WHERE h.patient_id = ? ORDER BY h.updated_at DESC`, pid)
273 if err != nil {
274 return nil, err
275 }
276 defer rows.Close()
277 var out []Handout
278 for rows.Next() {
279 var h Handout
280 var c, u int64
281 if err := rows.Scan(&h.ID, &h.Title, &h.Recipient, &h.Notes, &c, &u, &h.Count); err != nil {
282 return nil, err
283 }
284 h.Created, h.Updated = time.Unix(c, 0), time.Unix(u, 0)
285 out = append(out, h)
286 }
287 return out, rows.Err()
288}
289
290func (s *Store) CreateHandoutFor(ctx context.Context, title string, p Patient) (int64, error) {
291 res, err := s.db.ExecContext(ctx, `INSERT INTO handouts (title, recipient, patient_id) VALUES (?, ?, ?)`, title, p.Name, p.ID)
292 if err != nil {
293 return 0, err
294 }
295 return res.LastInsertId()
296}
297
298// HandoutPatient liefert den zugeordneten Patienten eines Handouts (0 = keiner).
299func (s *Store) HandoutPatient(ctx context.Context, hid int64) (int64, string, error) {
300 var pid sql.NullInt64
301 var name sql.NullString
302 err := s.db.QueryRowContext(ctx, `SELECT h.patient_id, p.name FROM handouts h LEFT JOIN patients p ON p.id = h.patient_id
303 WHERE h.id = ?`, hid).Scan(&pid, &name)
304 return pid.Int64, name.String, notFound(err)
305}
306