This is an automated email from the git hooks/post-receive script. tille pushed a commit to branch master in repository gnumed-server.
commit 1e599e1f0ab2c9b3108f2778f0d9c96cd6edeca3 Author: Andreas Tille <[email protected]> Date: Thu Jul 7 17:58:25 2016 +0200 Imported Upstream version 21.7 --- server/bootstrap/fixup_db-v21.conf | 6 + server/bootstrap/update_db-v20_v21.conf | 10 +- server/doc/schema/gnumed-entire_schema.html | 2 +- server/pycommon/gmI18N.py | 8 +- server/pycommon/gmMimeLib.py | 32 +-- server/pycommon/gmTools.py | 21 +- server/sql/v20-v21/data/v21-CD_DVD-sleeve.tex | 81 ++++++ .../v20-v21/dynamic/v21-release_notes-dynamic.sql | 32 +-- server/sql/v20-v21/fixups/v21-CD_DVD-sleeve.sql | 37 +++ server/sql/v20-v21/fixups/v21-ref-GKV_CU-fixup.sql | 45 ++++ .../v21-ref-auto_hint-klug_entscheiden_angio.sql | 52 ++++ ...21-ref-auto_hint-klug_entscheiden_endokrino.sql | 46 ++++ .../v21-ref-auto_hint-klug_entscheiden_pulmo.sql | 277 +++++++++++++++++++++ .../v21-ref-auto_hint-smoking_status-fixup.sql | 48 ++++ .../fixups/v21-ref-auto_hint-tetanus_STIKO.sql | 29 ++- .../python/v21-import-form-template-fixups.py | 31 +++ 16 files changed, 698 insertions(+), 59 deletions(-) diff --git a/server/bootstrap/fixup_db-v21.conf b/server/bootstrap/fixup_db-v21.conf index 2522fa3..6ff6ffa 100644 --- a/server/bootstrap/fixup_db-v21.conf +++ b/server/bootstrap/fixup_db-v21.conf @@ -30,6 +30,11 @@ schema = $schema$ v21-dem-view_grants-fixup.sql ../dynamic/v21-release_notes-dynamic.sql v21-ref-auto_hint-tetanus_STIKO.sql +v21-ref-auto_hint-klug_entscheiden_pulmo.sql +v21-ref-auto_hint-klug_entscheiden_endokrino.sql +v21-CD_DVD-sleeve.sql +v21-ref-auto_hint-smoking_status-fixup.sql +v21-ref-GKV_CU-fixup.sql $schema$ #---------------------------------- @@ -59,6 +64,7 @@ $upgrade plausibility checks$ script base directory = ../sql/v20-v21/python/ data import scripts = $data import scripts$ +v21-import-form-template-fixups.py $data import scripts$ #---------------------------------- diff --git a/server/bootstrap/update_db-v20_v21.conf b/server/bootstrap/update_db-v20_v21.conf index d72086e..12d41f9 100644 --- a/server/bootstrap/update_db-v20_v21.conf +++ b/server/bootstrap/update_db-v20_v21.conf @@ -121,6 +121,11 @@ schema base directory = ../sql/v20-v21/fixups/ schema = $schema$ v21-dem-view_grants-fixup.sql v21-ref-auto_hint-tetanus_STIKO.sql +v21-ref-auto_hint-klug_entscheiden_pulmo.sql +v21-ref-auto_hint-klug_entscheiden_endokrino.sql +v21-CD_DVD-sleeve.sql +v21-ref-auto_hint-smoking_status-fixup.sql +v21-ref-GKV_CU-fixup.sql $schema$ #---------------------------------- @@ -157,6 +162,7 @@ script base directory = ../sql/v20-v21/python/ data import scripts = $data import scripts$ v21-import-plot-scripts.py v21-import-form-templates.py +v21-import-form-template-fixups.py $data import scripts$ @@ -244,8 +250,8 @@ requests on results::::select count(1) from clin.test_result where fk_request is clinical code links (total) - internal consistency::::select True select ((select count(1) from clin.lnk_code2item_root) = (select ((select count(1) from clin.lnk_code2procedure) + (select count(1) from clin.lnk_code2rfe) + (select count(1) from clin.lnk_code2aoe) + (select count(1) from clin.lnk_code2episode) + (select count(1) from clin.lnk_code2h_issue) + (select count(1) from clin.lnk_code2narrative)))) paperwork templates::::select count(1) from ref.paperwork_templates - select count(1) - 3 from ref.paperwork_templates -automatic hints::::select count(1) + 5 from ref.auto_hint + select count(1) - 4 from ref.paperwork_templates +automatic hints::::select count(1) + 10 from ref.auto_hint select count(1) from ref.auto_hint suppressed hints::::select count(1) from clin.suppressed_hint select count(1) from clin.suppressed_hint diff --git a/server/doc/schema/gnumed-entire_schema.html b/server/doc/schema/gnumed-entire_schema.html index 1bd1ec2..82a80df 100644 --- a/server/doc/schema/gnumed-entire_schema.html +++ b/server/doc/schema/gnumed-entire_schema.html @@ -112,7 +112,7 @@ <body> <!-- Primary Index --> - <p><br><br>Dumped on 2016-05-26</p> + <p><br><br>Dumped on 2016-07-07</p> <h1><a name="index">Index of database - gnumed_v21</a></h1> <ul> diff --git a/server/pycommon/gmI18N.py b/server/pycommon/gmI18N.py index c1508f2..10c9907 100644 --- a/server/pycommon/gmI18N.py +++ b/server/pycommon/gmI18N.py @@ -358,11 +358,11 @@ def __install_domain(domain, prefer_local_catalog, language=u'?'): # strip one directory level # this is a rather neat trick :-) loc_dir = os.path.abspath(os.path.join(os.path.dirname(sys.argv[0]), '..', 'po')) - _log.debug('looking above binary install directory [%s]' % loc_dir) + _log.debug('looking one level above binary install directory: %s', loc_dir) candidate_PO_dirs.append(loc_dir) # - in path to binary loc_dir = os.path.abspath(os.path.join(os.path.dirname(sys.argv[0]), 'po')) - _log.debug('looking in binary install directory [%s]' % loc_dir) + _log.debug('looking in binary install directory: %s', loc_dir) candidate_PO_dirs.append(loc_dir) # - standard places @@ -400,8 +400,8 @@ def __install_domain(domain, prefer_local_catalog, language=u'?'): # now try to actually install it for candidate_PO_dir in candidate_PO_dirs: - _log.debug('trying with (%s, %s, %s)', candidate_PO_dir, language, domain) - _log.debug(' -> %s/%s/LC_MESSAGES/%s.mo', candidate_PO_dir, language, domain) + _log.debug('trying with (base=%s, %s, domain=%s)', candidate_PO_dir, language, domain) + _log.debug(' -> %s.mo', os.path.join(candidate_PO_dir, language, domain)) if not os.path.exists(candidate_PO_dir): continue try: diff --git a/server/pycommon/gmMimeLib.py b/server/pycommon/gmMimeLib.py index c773ec5..417f853 100644 --- a/server/pycommon/gmMimeLib.py +++ b/server/pycommon/gmMimeLib.py @@ -1,6 +1,8 @@ # -*- coding: utf-8 -*- """This module encapsulates mime operations. + + http://www.dwheeler.com/essays/open-files-urls.html """ #======================================================================================= __author__ = "Karsten Hilbert <[email protected]>" @@ -185,7 +187,8 @@ open_cmds = { 'gnome-open': 'gnome-open "%s"', # GNOME 'exo-open': 'exo-open "%s"', 'op': 'op "%s"', - 'open': 'open "%s"' # MacOSX: "open -a AppName file" (-a allows to override the default app for the file type) + 'open': 'open "%s"', # MacOSX: "open -a AppName file" (-a allows to override the default app for the file type) + 'cmd.exe': 'cmd.exe /c "%s"' # Windows #'run-mailcap' #'explorer' } @@ -200,7 +203,7 @@ def _get_system_startfile_cmd(filename): if _system_startfile_cmd is not None: return True, _system_startfile_cmd % filename - open_cmd_candidates = [u'xdg-open', u'kfmclient', u'gnome-open', u'exo-open', u'op', u'open'] + open_cmd_candidates = open_cmds.keys() for candidate in open_cmd_candidates: found, binary = gmShellAPI.detect_external_binary(binary = candidate) @@ -327,6 +330,7 @@ def call_viewer_on_file(aFile = None, block=None): _log.warning("no viewer found via standard mailcap system") if os.name == "posix": _log.warning("you should add a viewer for this mime type to your mailcap file") + _log.info("let's see what the OS can do about that") # does the file already have an extension ? @@ -351,21 +355,19 @@ def call_viewer_on_file(aFile = None, block=None): try: os.startfile(file_to_display) + return True, '' + except AttributeError: + _log.exception('os.startfile() does not exist on this platform') except: _log.exception('os.startfile(%s) failed', file_to_display) - msg = _("Unable to display the file:\n\n" - " [%s]\n\n" - "Your system does not seem to have a (working)\n" - "viewer registered for the file type\n" - " [%s]" - ) % (file_to_display, mime_type) - return False, msg - - # don't kill the file from under the (possibly async) viewer -# if file_to_display != aFile: -# os.remove(file_to_display) - - return True, '' + + msg = _("Unable to display the file:\n\n" + " [%s]\n\n" + "Your system does not seem to have a (working)\n" + "viewer registered for the file type\n" + " [%s]" + ) % (file_to_display, mime_type) + return False, msg #======================================================================================= if __name__ == "__main__": diff --git a/server/pycommon/gmTools.py b/server/pycommon/gmTools.py index 8c574d1..bbf50cc 100644 --- a/server/pycommon/gmTools.py +++ b/server/pycommon/gmTools.py @@ -67,18 +67,19 @@ u_greek_ALPHA = u'\u0391' u_greek_alpha = u'\u03b1' u_greek_OMEGA = u'\u03A9' u_greek_omega = u'\u03c9' -u_triangular_bullet = u'\u2023' # triangular bullet (>) -u_ellipsis = u'\u2026' # ... -u_euro = u'\u20AC' # EURO sign -u_numero = u'\u2116' # No. / # sign -u_down_left_arrow = u'\u21B5' # <-' -u_left_arrow = u'\u2190' # <-- +u_triangular_bullet = u'\u2023' # triangular bullet (>) +u_ellipsis = u'\u2026' # ... +u_euro = u'\u20AC' # EURO sign +u_numero = u'\u2116' # No. / # sign +u_down_left_arrow = u'\u21B5' # <-' +u_left_arrow = u'\u2190' # <-- u_up_arrow = u'\u2191' -u_arrow2right = u'\u2192' # --> +u_arrow2right = u'\u2192' # --> u_down_arrow = u'\u2193' -u_left_arrow_with_tail = u'\u21a2' # <--< -u_sum = u'\u2211' # sigma -u_almost_equal_to = u'\u2248' # approximately / nearly / roughly +u_left_arrow_with_tail = u'\u21a2' # <--< +u_arrow2right_until_vertical_bar = u'\u21e5' # -->| +u_sum = u'\u2211' # sigma +u_almost_equal_to = u'\u2248' # approximately / nearly / roughly u_corresponds_to = u'\u2258' u_infinity = u'\u221E' u_diameter = u'\u2300' diff --git a/server/sql/v20-v21/data/v21-CD_DVD-sleeve.tex b/server/sql/v20-v21/data/v21-CD_DVD-sleeve.tex new file mode 100644 index 0000000..9217a45 --- /dev/null +++ b/server/sql/v20-v21/data/v21-CD_DVD-sleeve.tex @@ -0,0 +1,81 @@ +%------------------------------------------------------------------ +% CD/DVD cutout sleeve +% +% http://blog.thenetimpact.com/2011/07/decoding-qr-codes-how-to-format-data-for-qr-code-generators/ +% https://www.nttdocomo.co.jp/english/service/developer/make/content/barcode/function/application/addressbook/index.html +% +% use with pdflatex, NOT xelatex +% +% License: GPL v2 or later +%------------------------------------------------------------------ + +\documentclass[a4paper,10pt,oneside]{article} +\pagestyle{empty} + +\usepackage[cjkjis,graphics]{ucs} % lots of UTF8 symbols, breaks with xelatex +\usepackage[utf8x]{inputenc} % content is UTF8, breaks with xelatex +\usepackage[T1]{fontenc} % fonts are T1 +\usepackage[ngerman]{babel} % Deutsch und Trennung +\usepackage{marvosym} % Symbole: Handy, Telefon, E-Mail +\usepackage{textcomp} % Symbole für Textmodus zum Escapen +\usepackage{lmodern} % sans serif Latin Modern +\usepackage{graphicx} % Grafiken laden (Logo und Unterschrift) +\usepackage[space]{grffile} % besserer Zugriff auf Grafikdateien +\usepackage{multirow} +\usepackage[left=1cm, top=1cm, right=2cm, bottom=1cm]{geometry} +\usepackage{qrcode} +\usepackage{simplecd} + +\listfiles + +\begin{document} + +\sleeve[ + + % back page (flap) -- praxis data + + {\Large $<praxis::%(praxis)s, %(branch)s::120>$}\\ + \ \\ + \begin{tabular}{llc} + %MECARD:N:NAME;ADR:pobox,subunit,unit,street,ort,region,zip,country;TEL:111111111;FAX:22222222;EMAIL:[email protected]; + \multicolumn{2}{l}{$<praxis_address::%(street)s %(number)s %(subunit)s::60>$} & \multirow{5}*{{\qrcode[level=H]{MECARD:N:$<praxis::%(praxis)s, %(branch)s::>$;ADR:$<praxis_address::,%(subunit)s,%(number)s,%(street)s,%(urb)s,,%(postcode)s,%(l10n_country)s::>$;TEL:$<praxis_comm::workphone::>$;FAX:$<praxis_comm::fax::>$;EMAIL:$<praxis_comm::email::60>$;}}} \\ + \multicolumn{2}{l}{$<praxis_address::%(postcode)s %(urb)s, %(l10n_country)s::60>$} & \\ + \Telefon{} & {\small $<praxis_comm::workphone::60>$} & \\ + \FAX{} & {\small $<praxis_comm::fax::60>$} & \\ + \Email{} & {\small $<praxis_comm::email::60>$} & \\ + \ComputerMouse{} & \multicolumn{2}{l}{\tiny $<praxis_comm::web::60>$} \\ + \end{tabular}\\ + %$<data_snippet::praxis-logo//{\includegraphics[width=30mm]{%s}}//image/png//.png::250>$ +]{ + + % front page -- patient data + + {\large GNUmed Patient Data Pack}\\ + {\small erstellt: $<today::%d.%B %Y::50>$}\\ + \ \\ + \hrule + \ \\ + \ \\ + {\Large $<firstname::::>$ $<lastname::::>$} + \ \\ + \ \\ + \begin{tabular}{lc} + %MECARD:N:lastname,firstname;BDAY:YYYYMMDD;ADR:pobox,subunit,number,street,location,region,zip,country;; + & \multirow{5}*{{\qrcode[height=2.5cm,level=H]{MECARD:N:$<lastname::::>$,$<firstname::::>$;BDAY:$<date_of_birth::%Y%m%d::>$;ADR:,$<adr_subunit::home::>$,$<adr_number::home::>$,$<adr_street::home::>$,$<adr_location::home::>$,,$<adr_postcode::home::>$,$<adr_country::home::>$;;}}} \\ + born: $<date_of_birth::%d.%B %Y::>$ & \\ + {\small $<adr_street::home::>$ $<adr_number::home::>$} & \\ + {\small $<adr_postcode::home::>$ $<adr_location::home::>$} & \\ + {\small $<adr_country::home::>$} & \\ + \end{tabular}\\ + \ \\ + \ \\ + $<free_text::A bit of descriptive text for this media (in LaTeX)::300>$ + \ \\ + \ \\ + \hrule + \ \\ + {\tiny GNUmed $<client_version::::>$ (www.gnumed.org)}\\ + {\qrcode[hyperlink,height=1cm,level=H]{http://www.gnumed.org}} +} + +\end{document} diff --git a/server/sql/v20-v21/dynamic/v21-release_notes-dynamic.sql b/server/sql/v20-v21/dynamic/v21-release_notes-dynamic.sql index f83e454..8fed502 100644 --- a/server/sql/v20-v21/dynamic/v21-release_notes-dynamic.sql +++ b/server/sql/v20-v21/dynamic/v21-release_notes-dynamic.sql @@ -17,24 +17,28 @@ INSERT INTO dem.message_inbox ( ) VALUES ( (select pk from dem.staff where db_user = 'any-doc'), (select pk_type from dem.v_inbox_item_type where type = 'memo' and category = 'administrative'), - 'Release Notes for GNUmed 1.6.6 (database v21.6)', - 'GNUmed 1.6.6 Release Notes: + 'Release Notes for GNUmed 1.6.7 (database v21.7)', + 'GNUmed 1.6.7 Release Notes: - 1.6.6 + 1.6.7 -FIX: error when running gm-import_incoming as root -FIX: failure to show entries with soap_cat=NULL in EMR list journal -FIX: copy-pasto "nicotine" -> "ethanol" +FIX: constrain hospital stay PRW to current patient +FIX: smoking status detection in dynamic hints +FIX: GKV checkup auto hint +FIX: tetanus shot auto hint +FIX: substance intake discontinuation reason field behaviour +FIX: exception in clinical calculator with pre-birth test results -IMPROVED: clear metadata panel after importing new document -IMRPOVED: enable editing of document source org -IMPROVED: list context menu layout -IMPROVED: handling of Windows locale names like Hungarian_Hungary [thanks Attila] -IMPROVED: AppData file -IMRPOVED: OOo/LO/SO detection [thanks John] -IMRPOVED: tree display of documents +IMPROVED: file viewer detection on Windows [thanks John] +IMPROVED: DICOM studies/series display +IMPROVED: ZIP-with-DICOMDIR support +IMPROVED: browse index.html after saving/burning from export area +IMPROVED: substance abuse management workflow +IMPROVED: check for tools im gm-describe_file +IMPROVED: substance intake start/end formatting -NEW: calculate distance of patient address to your praxis +NEW: a few hints from the German Choosing Wisely initiative +NEW: CD/DVD sleeve LaTeX template '); -- -------------------------------------------------------------- diff --git a/server/sql/v20-v21/fixups/v21-CD_DVD-sleeve.sql b/server/sql/v20-v21/fixups/v21-CD_DVD-sleeve.sql new file mode 100644 index 0000000..df52fd9 --- /dev/null +++ b/server/sql/v20-v21/fixups/v21-CD_DVD-sleeve.sql @@ -0,0 +1,37 @@ +-- ============================================================== +-- GNUmed database schema change script +-- +-- License: GPL v2 or later +-- Author: [email protected] +-- +-- ============================================================== +\set ON_ERROR_STOP 1 +--set default_transaction_read_only to off; + +-- -------------------------------------------------------------- +delete from ref.paperwork_templates where name_long = 'CD/DVD sleeve [K.Hilbert]'; + +insert into ref.paperwork_templates ( + fk_template_type, + instance_type, + name_short, + name_long, + external_version, + engine, + filename, + edit_after_substitution, + data +) values ( + (select pk from ref.form_types where name = 'other letter'), + 'other letter', + 'CD/DVD sleeve', + 'CD/DVD sleeve [K.Hilbert]', + '21.7', + 'L', + 'gm-cd_dvd-sleeve.tex', + false, + 'real template missing'::bytea +); + +-- -------------------------------------------------------------- +select gm.log_script_insertion('v21-CD_DVD-sleeve.sql', '21.7'); diff --git a/server/sql/v20-v21/fixups/v21-ref-GKV_CU-fixup.sql b/server/sql/v20-v21/fixups/v21-ref-GKV_CU-fixup.sql new file mode 100644 index 0000000..e494283 --- /dev/null +++ b/server/sql/v20-v21/fixups/v21-ref-GKV_CU-fixup.sql @@ -0,0 +1,45 @@ +-- ============================================================== +-- GNUmed database schema change script +-- +-- License: GPL v2 or later +-- Author: [email protected] +-- +-- ============================================================== +\set ON_ERROR_STOP 1 +--set default_transaction_read_only to off; + +-- -------------------------------------------------------------- +UPDATE ref.auto_hint SET + query = 'SELECT ( + SELECT + -- known DOB + d_i.dob is not NULL + AND + -- not deceased + d_i.deceased is NULL + FROM + dem.identity d_i + WHERE + d_i.pk = ID_ACTIVE_PATIENT + +) AND ( + SELECT + age(d_i.dob) >= ''35 years''::interval + FROM + dem.identity d_i + WHERE + d_i.pk = ID_ACTIVE_PATIENT + +) AND NOT EXISTS ( + SELECT 1 FROM clin.v_emr_journal + WHERE + pk_patient = ID_ACTIVE_PATIENT + AND + narrative ~* ''.*checkup.*'' + AND + age(clin_when) < ''2 years''::interval +);' +WHERE title = 'GKV-Checkup überfällig'; + +-- -------------------------------------------------------------- +select gm.log_script_insertion('v21-ref-GKV_CU-fixup.sql', '21.7'); diff --git a/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_angio.sql b/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_angio.sql new file mode 100644 index 0000000..a90c34d --- /dev/null +++ b/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_angio.sql @@ -0,0 +1,52 @@ +-- ============================================================== +-- GNUmed database schema change script +-- +-- License: GPL v2 or later +-- Author: [email protected] +-- +-- ============================================================== +\set ON_ERROR_STOP 1 +--set default_transaction_read_only to off; + +-- -------------------------------------------------------------- +DELETE FROM ref.auto_hint WHERE title = 'Screening Bauchaortenaneurysma (DGA/DGIM)'; + +INSERT INTO ref.auto_hint(title, hint, source, lang, query, recommendation_query) VALUES ( + 'Screening Bauchaortenaneurysma (DGA/DGIM)', + 'Männern >65J soll Sonographie auf Bauchaortenaneurysma angeboten werden.', + '"Gemeinsam klug entscheiden" (DGIM: DGA, 2016)', + 'de', + 'SELECT +( -- male + SELECT gender IN (''m'',''tm'') + FROM dem.identity WHERE pk = ID_ACTIVE_PATIENT +) AND ( + -- >65 years + SELECT dob < now() - ''65 years''::interval + FROM dem.identity WHERE pk = ID_ACTIVE_PATIENT +) AND NOT EXISTS ( + -- no procedure ''abdo/bauch ultras/sono'' after 65th birthday + SELECT 1 FROM clin.v_procedures WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + ( + (performed_procedure ILIKE ''%ultras%'') + OR + (performed_procedure ILIKE ''%sono%'') + ) + AND + ( + (performed_procedure ILIKE ''%bauch%'') + OR + (performed_procedure ILIKE ''%abdo%'') + ) + AND + (clin_when > (SELECT dob + ''65 years''::interval FROM dem.idenity WHERE pk = ID_ACTIVE_PATIENT)) + );', + 'SELECT + ''Männlicher Patient (>65 Jahre), noch keine Maßnahme "Ultraschall des Abdomens" (als Screening auf Bauchaortenaneurysma) dokumentiert.'' + AS recommendation;' +); + +-- -------------------------------------------------------------- +SELECT gm.log_script_insertion('v21-ref-auto_hint-klug_entscheiden_angio.sql', '21.7'); diff --git a/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_endokrino.sql b/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_endokrino.sql new file mode 100644 index 0000000..94fa785 --- /dev/null +++ b/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_endokrino.sql @@ -0,0 +1,46 @@ +-- ============================================================== +-- GNUmed database schema change script +-- +-- License: GPL v2 or later +-- Author: [email protected] +-- +-- ============================================================== +\set ON_ERROR_STOP 1 +--set default_transaction_read_only to off; + +-- -------------------------------------------------------------- +DELETE FROM ref.auto_hint WHERE title = 'Schwangerschaft->Jod (DGE/DGIM)'; + +insert into ref.auto_hint(title, hint, source, lang, query, recommendation_query) values ( + 'Schwangerschaft->Jod (DGE/DGIM)', + 'Schwangeren soll eine Jodsupplementation angeboten werden.', + '"Gemeinsam klug entscheiden" (DGIM: DGE, 2016)', + 'de', + '-- pregnancy check +SELECT EXISTS ( + SELECT 1 FROM clin.patient WHERE + fk_identity = ID_ACTIVE_PATIENT + AND + coalesce(edc BETWEEN now() - ''1 month''::interval AND now() + ''11 months''::interval, FALSE) +-- but no jod in medication +) AND NOT EXISTS ( + SELECT 1 FROM clin.v_substance_intakes WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + -- iodine + (atc_substance = ''D08AG03'') + AND + ((discontinued IS NULL) OR (discontinued > now())) + );', + 'SELECT + ''Patientin ist schwanger ('' + || ''Termin: '' + || to_char(edc, ''YYYY Mon DD'') + || ''), nimmt aber kein Jod [D08AG03].'' + as recommendation + FROM clin.patient + WHERE fk_identity = ID_ACTIVE_PATIENT; +'); + +-- -------------------------------------------------------------- +SELECT gm.log_script_insertion('v21-ref-auto_hint-klug_entscheiden_endokrino.sql', '21.7'); diff --git a/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_pulmo.sql b/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_pulmo.sql new file mode 100644 index 0000000..5822ab8 --- /dev/null +++ b/server/sql/v20-v21/fixups/v21-ref-auto_hint-klug_entscheiden_pulmo.sql @@ -0,0 +1,277 @@ +-- ============================================================== +-- GNUmed database schema change script +-- +-- License: GPL v2 or later +-- Author: [email protected] +-- +-- ============================================================== +\set ON_ERROR_STOP 1 +--set default_transaction_read_only to off; + +-- -------------------------------------------------------------- +DELETE FROM ref.auto_hint WHERE title = 'Raucher->Spiro (DGP/DGIM)'; + +insert into ref.auto_hint(title, hint, source, lang, query, recommendation_query) values ( + 'Raucher->Spiro (DGP/DGIM)', + 'Raucher sollen mit Spirometrie überwacht werden', + '"Gemeinsam klug entscheiden" (DGIM: DGP, 2016)', + 'de', + '-- recently confirmed to be a smoker +SELECT EXISTS ( + SELECT 1 FROM clin.v_substance_intakes WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (atc_substance = ''N07BA01'') + AND + (coalesce(harmful_use_type, -1) IN (1,2)) + AND + ((discontinued IS NULL) OR (discontinued > now())) + AND + (last_checked_when > now() - ''1 year''::interval) +-- but no FEV1 documented within the last year +) AND NOT EXISTS ( + SELECT 1 FROM clin.v_test_results WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + ( + (unified_loinc = ''20150-9'') + OR + (lower(unified_name) = ''fev1'') + OR + (lower(unified_abbrev) = ''fev1'') + ) + AND + (clin_when > now() - ''1 year''::interval) +);', + 'SELECT ( + SELECT + ''Patient raucht ('' || to_char(last_checked_when, ''YYYY Mon'') || ''), aber '' + FROM clin.v_substance_intakes WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (atc_substance = ''N07BA01'') +) || (SELECT + coalesce ( + (SELECT ''die letzte Spirometrie (FEV1 [20150-9)] ist über 1 Jahr her ('' || to_char(clin_when, ''YYYY Mon'') || '').'' + FROM clin.v_test_results WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + ( + (unified_loinc = ''20150-9'') + OR + (lower(unified_name) = ''fev1'') + OR + (lower(unified_abbrev) = ''fev1'') + ) + )::TEXT, + ''es ist keine Spirometrie (FEV1 [20150-9]) dokumentiert.''::TEXT + ) +) as recommendation;' +); + +-- -------------------------------------------------------------- +DELETE FROM ref.auto_hint WHERE title = 'Lunge->Pneumokkken-Impfg (DGP/DGIM)'; + +insert into ref.auto_hint(title, hint, source, lang, query, recommendation_query) values ( + 'Lunge->Pneumokkken-Impfg (DGP/DGIM)', + 'Lungenkranke älter 60 sollen eine Pneumokokkenimpfung angeboten bekommen.', + '"Gemeinsam klug entscheiden" (DGIM: DGP, 2016)', + 'de', + '-- >60 years +SELECT EXISTS ( + SELECT 1 FROM dem.identity WHERE + (pk = ID_ACTIVE_PATIENT) + AND + (dob < now() - ''60 years''::interval) +) AND EXISTS ( +-- Asthma/COPD/Emphysem/Lungenfibrose +SELECT 1 FROM clin.v_problem_list WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + ( -- should check ICPC/ICD10 + (problem ilike ''%asthma%'') + OR + (problem ilike ''%COPD%'') + OR + (problem ilike ''%emphysem%'') + OR + ( + (problem ilike ''%fibros%'') + AND + ( + (problem ilike ''%lung%'') + OR + (problem ilike ''%pulmon%'') + ) + ) + ) +) AND NOT EXISTS ( +-- keine Impfung Pneumokokken +SELECT 1 FROM clin.v_pat_vaccs4indication WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (indication = ''pneumococcus'') +)', + 'SELECT +( -- explain + SELECT E''Es ist keine Impfung gegen Pneumokokken dokumentiert.\n\n'' + || E''Es sollte eine Impfung angeboten werden, weil\n'' + -- age + || '' das Alter ('' || trim(leading ''0'' from to_char(justify_interval(now() - dob), ''YYY'')) || E'') > 60 Jahre ist und\n'' + FROM dem.identity WHERE pk = ID_ACTIVE_PATIENT +) || ( + -- problem + SELECT '' ein Lungenproblem "'' || problem || ''" dokumentiert ist'' + FROM clin.v_problem_list WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + ( -- should check ICPC/ICD10 + (problem ilike ''%asthma%'') + OR + (problem ilike ''%COPD%'') + OR + (problem ilike ''%emphysem%'') + OR + ( + (problem ilike ''%fibros%'') + AND + ( + (problem ilike ''%lung%'') + OR + (problem ilike ''%pulmon%'') + ) + ) + ) +)' +); + +-- -------------------------------------------------------------- +DELETE FROM ref.auto_hint WHERE title = 'Lunge->Influenza-Impfg (DGP/DGIM)'; + +insert into ref.auto_hint(title, hint, source, lang, query, recommendation_query) values ( + 'Lunge->Influenza-Impfg (DGP/DGIM)', + 'Lungenkranke älter 60 sollen eine Influenzaimpfung angeboten bekommen.', + '"Gemeinsam klug entscheiden" (DGIM: DGP, 2016)', + 'de', + ' +SELECT + -- beyond August ? -> probably only valid on the Northern Hemisphere + (extract(month from now()) > 8) +AND EXISTS ( + -- >60 years + SELECT 1 FROM dem.identity WHERE + (pk = ID_ACTIVE_PATIENT) + AND + (dob < now() - ''60 years''::interval) +) AND EXISTS ( +-- Asthma/COPD/Emphysem/Lungenfibrose +SELECT 1 FROM clin.v_problem_list WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + ( -- should check ICPC/ICD10 + (problem ilike ''%asthma%'') + OR + (problem ilike ''%COPD%'') + OR + (problem ilike ''%emphysem%'') + OR + ( + (problem ilike ''%fibros%'') + AND + ( + (problem ilike ''%lung%'') + OR + (problem ilike ''%pulmon%'') + ) + ) + ) +) AND NOT EXISTS ( +-- keine Impfung Influenza in den letzten 6 Monaten rückblickend, falls wir im September sind +SELECT 1 FROM clin.v_pat_vaccs4indication WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (indication = ''influenza (seasonal)'') + AND + date_given > now() - ''6 months''::interval +ORDER BY + date_given DESC +LIMIT 1 +)', + 'SELECT +( -- explain + SELECT E''Es ist in den letzten 6 Monaten keine Impfung gegen Influenza dokumentiert.\n\n'' + || E''Es sollte eine Impfung angeboten werden, weil\n'' + -- age + || '' das Alter ('' || trim(leading ''0'' from to_char(justify_interval(now() - dob), ''YYY'')) || E'') > 60 Jahre ist und\n'' + FROM dem.identity WHERE pk = ID_ACTIVE_PATIENT +) || ( + -- problem + SELECT '' ein Lungenproblem "'' || problem || ''" dokumentiert ist'' + FROM clin.v_problem_list WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + ( -- should check ICPC/ICD10 + (problem ilike ''%asthma%'') + OR + (problem ilike ''%COPD%'') + OR + (problem ilike ''%emphysem%'') + OR + ( + (problem ilike ''%fibros%'') + AND + ( + (problem ilike ''%lung%'') + OR + (problem ilike ''%pulmon%'') + ) + ) + ) +)' +); + +-- -------------------------------------------------------------- +DELETE FROM ref.auto_hint WHERE title = 'O²-Gabe->BGA/SpO² (DGP/DGIM)'; + +insert into ref.auto_hint(title, hint, source, lang, query, recommendation_query) values ( + 'O²-Gabe->BGA/SpO² (DGP/DGIM)', + 'Bei ambulanter O²-Therapie soll aller 3 Monate die Indikation geprüft werden.', + '"Gemeinsam klug entscheiden" (DGIM: DGP, 2016)', + 'de', + -- query + 'SELECT EXISTS ( + -- takes oxygen + SELECT 1 FROM clin.v_substance_intakes WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (atc_substance = ''V03AN01'') -- oxygen + AND + ((discontinued IS NULL) OR (discontinued > now())) +) AND NOT EXISTS ( + -- no BGA or SpO2 within last 3 months + SELECT 1 FROM clin.v_test_results WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (unified_loinc IN (select code from ref.loinc where term ilike ''%oxygen%'' and term ilike ''%pressure%'')) + AND + (clin_when > now() - ''3 months''::interval) +);', + -- recommendation query + ' +SELECT coalesce ( + (SELECT + ''Die Hypoxämie wurde zuletzt im '' || to_char(clin_when, ''Mon YYYY'') || '' überprüft.'' + FROM clin.v_test_results WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (unified_loinc IN (select code from ref.loinc where term ilike ''%oxygen%'' and term ilike ''%pressure%'')) + ORDER BY + clin_when DESC + LIMIT 1 + )::text, + (SELECT ''Es ist keine Überprüfung der Hypoxämie in den letzten 3 Monaten dokumentiert.''::text) +);' +); + +-- -------------------------------------------------------------- +SELECT gm.log_script_insertion('v21-ref-auto_hint-klug_entscheiden_pulmo.sql', '21.7'); diff --git a/server/sql/v20-v21/fixups/v21-ref-auto_hint-smoking_status-fixup.sql b/server/sql/v20-v21/fixups/v21-ref-auto_hint-smoking_status-fixup.sql new file mode 100644 index 0000000..35c52b1 --- /dev/null +++ b/server/sql/v20-v21/fixups/v21-ref-auto_hint-smoking_status-fixup.sql @@ -0,0 +1,48 @@ +-- ============================================================== +-- GNUmed database schema change script +-- +-- License: GPL v2 or later +-- Author: [email protected] +-- +-- ============================================================== +\set ON_ERROR_STOP 1 + +--set default_transaction_read_only to off; + +-- -------------------------------------------------------------- +DELETE FROM ref.auto_hint WHERE title = 'Outdated smoking status documentation'; + +INSERT INTO ref.auto_hint(title, hint, source, lang, query, recommendation_query) VALUES ( + 'Outdated smoking status documentation', + 'Smoking status was last recorded more than one year ago for this smoker.', + 'AWMF NVL Schädlicher Tabakgebrauch', + 'en', + 'SELECT EXISTS ( + SELECT 1 FROM clin.v_nonbrand_intakes WHERE + (pk_patient = ID_ACTIVE_PATIENT) + AND + (atc_substance = ''N07BA01'') + AND + (coalesce(harmful_use_type, -1) IN (1,2)) + AND + ((discontinued IS NULL) OR (discontinued > now())) + AND + (last_checked_when < now() - ''1 year''::interval) + );', + 'SELECT + _(''Smoking status'') || E''\n'' + || '' '' || _(''Last checked:'') || '' '' || to_char(last_checked_when, ''Mon YYYY'') + || (case + when harmful_use_type = 1 then E''\n'' || _(''harmful use'') + when harmful_use_type = 2 then E''\n'' || _(''addiction'') + when harmful_use_type = 3 then E''\n'' || _(''previous addiction'') + end) + || coalesce(E''\n '' || _(''Quit date:'') || '' '' || to_char(discontinued, ''YYYY Mon DD''), '''') + || coalesce(E''\n '' || _(''Notes:'') || '' '' || notes, '''') + FROM + clin.v_nonbrand_intakes + WHERE pk_patient = ID_ACTIVE_PATIENT;' +); + +-- -------------------------------------------------------------- +select gm.log_script_insertion('v21-ref-auto_hint-smoking_status-fixup.sql', '21.7'); diff --git a/server/sql/v20-v21/fixups/v21-ref-auto_hint-tetanus_STIKO.sql b/server/sql/v20-v21/fixups/v21-ref-auto_hint-tetanus_STIKO.sql index 50fd538..b1922a6 100644 --- a/server/sql/v20-v21/fixups/v21-ref-auto_hint-tetanus_STIKO.sql +++ b/server/sql/v20-v21/fixups/v21-ref-auto_hint-tetanus_STIKO.sql @@ -16,18 +16,21 @@ insert into ref.auto_hint(title, hint, source, lang, query, recommendation_query 'Letzte Tetanusimpfung vor mehr als 10 Jahren dokumentiert.', 'STIKO 2016', 'de', - 'SELECT NOT EXISTS ( - SELECT pk_vaccination - FROM clin.v_pat_vaccs4indication - WHERE - pk_patient = ID_ACTIVE_PATIENT - AND - indication = ''tetanus'' - AND - date_given > now() - ''10 years''::interval - ORDER BY - date_given DESC - LIMIT 1 + 'SELECT ( + -- not deceased + SELECT deceased is NULL FROM dem.identity WHERE pk = ID_ACTIVE_PATIENT + ) AND NOT EXISTS ( + -- no tetanus shot documented + SELECT 1 FROM clin.v_pat_vaccs4indication + WHERE + pk_patient = ID_ACTIVE_PATIENT + AND + indication = ''tetanus'' + AND + date_given > now() - ''10 years''::interval + ORDER BY + date_given DESC + LIMIT 1 );', 'SELECT coalesce ( (SELECT @@ -45,4 +48,4 @@ insert into ref.auto_hint(title, hint, source, lang, query, recommendation_query ); -- -------------------------------------------------------------- -SELECT gm.log_script_insertion('v21-ref-auto_hint-tetanus_STIKO.sql', '21.4'); +SELECT gm.log_script_insertion('v21-ref-auto_hint-tetanus_STIKO.sql', '21.7'); diff --git a/server/sql/v20-v21/python/v21-import-form-template-fixups.py b/server/sql/v20-v21/python/v21-import-form-template-fixups.py new file mode 100644 index 0000000..c2f555e --- /dev/null +++ b/server/sql/v20-v21/python/v21-import-form-template-fixups.py @@ -0,0 +1,31 @@ +# coding: utf8 +#============================================================== +# GNUmed database schema change script +# +# License: GPL v2 or later +# Author: [email protected] +# +#============================================================== +import os + +from Gnumed.pycommon import gmPG2 + +#-------------------------------------------------------------- + +def run(conn=None): + + # CD/DVD sleeve + gmPG2.file2bytea ( + query = u""" + UPDATE ref.paperwork_templates SET + data = %(data)s::bytea, + external_version = '21.0' + WHERE + name_long = 'CD/DVD sleeve [K.Hilbert]'""", + filename = os.path.join('..', 'sql', 'v20-v21', 'data', 'v21-CD_DVD-sleeve.tex'), + conn = conn + ) + + return True + +#============================================================== -- Alioth's /usr/local/bin/git-commit-notice on /srv/git.debian.org/git/debian-med/gnumed-server.git _______________________________________________ debian-med-commit mailing list [email protected] http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/debian-med-commit
