##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)
);
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 (
Description LIKE '%Medication Reconciliation%'
OR cron_header LIKE '%medications to review%'
OR Description LIKE '%medication%'
)
ORDER BY ID
LIMIT 1
INSERT INTO #Request.prefix_db_lookup#.Dashboard
(Block_ID, Header, Order_ID, Description, Link, cron_header, Status)
VALUES (
,
,
,
,
,
,
0
)
Dashboard — link added
INSERT INTO #Request.prefix_db_lookup#.Dashboard
(Block_ID, Header, Order_ID, Description, Link, cron_header, emp_permissions, Status)
VALUES (
,
,
,
,
,
,
'',
0
)
Dashboard — link added (with emp_permissions)
Dashboard — #HTMLEditFormat(cfcatch.message)#
Dashboard — link already exists (ID #checkDash.ID#)
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