##10785 Medication Interactions Physician Report Setup

##10785 Medication Interactions → Physician Report

Creates pMed_Interaction_Report on every agency DB, adds Communication subject Medication Interactions, and registers the dashboard link.

Important: Backup databases before running. Safe to re-run (skips existing objects).

What this does

  • CREATE TABLE IF NOT EXISTS agency_*.pMed_Interaction_Report (flag that interactions were reported)
  • INSERT CommunicationSubjects subject Medication Interactions per agency
  • INSERT lookup Dashboard row for the report link (if missing)
CREATE TABLE IF NOT EXISTS {agency}.pMed_Interaction_Report ( Report_ID INT NOT NULL AUTO_INCREMENT, Patient_ID INT NOT NULL DEFAULT 0, Fingerprint VARCHAR(500) NOT NULL DEFAULT '', Comm_ID INT NULL, Interaction_Summary TEXT NULL, Date_Create DATETIME NULL, Created_By INT NOT NULL DEFAULT 0, Status INT NOT NULL DEFAULT 0, PRIMARY KEY (Report_ID) );
Back

Migration Progress

SELECT ID FROM #Request.prefix_db_lookup#.Dashboard WHERE Status = 0 AND ( Description = OR Link LIKE ) LIMIT 1 SELECT Block_ID, Header, Order_ID FROM #Request.prefix_db_lookup#.Dashboard WHERE Status = 0 AND ( cron_header IN ('vbp alerts', 'vbp_alerts') OR Description LIKE '%VBP Alert%' OR Header LIKE '%Careplan%' ) ORDER BY CASE WHEN cron_header IN ('vbp alerts', 'vbp_alerts') OR Description LIKE '%VBP Alert%' THEN 0 WHEN Header LIKE '%Careplan%' THEN 1 ELSE 2 END, Order_ID, ID LIMIT 1 INSERT INTO #Request.prefix_db_lookup#.Dashboard (Block_ID, Header, Order_ID, Description, Link, cron_header, emp_permissions, Status) VALUES ( , , , , , , , 0 )
Dashboard — link added under Careplans (#dashPerm#)
INSERT INTO #Request.prefix_db_lookup#.Dashboard (Block_ID, Header, Order_ID, Description, Link, cron_header, Status) VALUES ( , , , , , , 0 )
Dashboard — link added under Careplans (no emp_permissions column)
Dashboard — #HTMLEditFormat(cfcatch.message)#
SELECT Block_ID, Header, Order_ID FROM #Request.prefix_db_lookup#.Dashboard WHERE Status = 0 AND ( cron_header IN ('vbp alerts', 'vbp_alerts') OR Description LIKE '%VBP Alert%' OR Header LIKE '%Careplan%' ) ORDER BY CASE WHEN cron_header IN ('vbp alerts', 'vbp_alerts') OR Description LIKE '%VBP Alert%' THEN 0 WHEN Header LIKE '%Careplan%' THEN 1 ELSE 2 END, Order_ID, ID LIMIT 1 UPDATE #Request.prefix_db_lookup#.Dashboard SET Block_ID = , Header = , Order_ID = , cron_header = , emp_permissions = WHERE ID =
Dashboard — existing ID #checkDash.ID# moved under Careplans (#dashPerm#)
Dashboard — link already exists (ID #checkDash.ID#); Careplans/#dashPerm# update skipped (#HTMLEditFormat(cfcatch.message)#)
SELECT SCHEMA_NAME AS db_name FROM information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE 'agency\_%' ESCAPE '\\' AND SCHEMA_NAME NOT LIKE '%lookup%' ORDER BY SCHEMA_NAME
Found #getAgencies.recordCount# agency database(s)
SELECT COUNT(*) AS table_exists FROM information_schema.TABLES WHERE TABLE_SCHEMA = AND TABLE_NAME = 'pMed_Interaction_Report'
#db_name# — table exists
CREATE TABLE IF NOT EXISTS #db_name#.pMed_Interaction_Report ( Report_ID INT NOT NULL AUTO_INCREMENT, Patient_ID INT NOT NULL DEFAULT 0, Fingerprint VARCHAR(500) NOT NULL DEFAULT '', Comm_ID INT NULL DEFAULT NULL, Interaction_Summary TEXT NULL, Date_Create DATETIME NULL, Created_By INT NOT NULL DEFAULT 0, Status INT NOT NULL DEFAULT 0, PRIMARY KEY (Report_ID), KEY idx_patient_status (Patient_ID, Status), KEY idx_fingerprint (Fingerprint(191)) )
#db_name# — pMed_Interaction_Report created
SELECT CommSub_ID FROM #db_name#.CommunicationSubjects WHERE Agency_ID = AND Subject = LIMIT 1 INSERT INTO #db_name#.CommunicationSubjects (Agency_ID, Subject, active, Created_By, Created_Date) VALUES ( , , 'Y', 1, NOW() )
#db_name# — subject "#subjectName#" added
#db_name# — subject exists (###checkSubj.CommSub_ID#)
#db_name# — #HTMLEditFormat(cfcatch.message)#
Done. Success: #totalSuccess#   Skipped: #totalSkipped#   Errors: #totalErrors#
Fatal: #HTMLEditFormat(cfcatch.message)# #HTMLEditFormat(cfcatch.detail)#

Back to Migrations