##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 (
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