We've taken it a step farther, and it may be farther than many are interested in taking it but it's working for us. This involves a combination of SQL and MEL.
First thing to bear in mind is the difference between a patient who's been issued a PIN, and one who has actually gone to the site and registered. It's the ones who've registered that count.
To get a count or list of patients who've been issued a PIN, query the Centricity db's DOCUMENT table for records where DOCTYPE=1623583212002750 and STATUS='S'.
To get a count or list of patients who've actually registered, you have to query the Portal db. This may or may not be on the same SQL Server instance as your Centricity db, so there may be some server linking involved before you can do the following. In the following query, which is run from the Centricity db, the Portal db resides on linked server EMRDMPP. We're looking for a list of patient identifiers (PIds) for patients who've registered:
SELECT p.PId
FROM EMRDMPP.Enterprise.dbo.KUser ku
INNER JOIN EMRDMPP.Enterprise.dbo.DCommunityAccount dca
ON ku.UserID = dca.UserID
INNER JOIN EMRDMPP.Enterprise.dbo.DPartner dp
ON dca.DCommunityAccountKey = dp.DCommunityAccountKey
INNER JOIN PatientProfile p
ON dp.Identifier = p.PId
We can use that list of PIds to pull whatever kind of information we want from the Centricity db for reporting, etc.
We can also use it to maintain the "preferred method of contact" in our registrations, which is how we control the banner display. We have a SQL Agent job that runs nightly, and updates the PatientProfile.ContactByMId field to either "Secure Message" (if they have registered) or "Letter" (if they have not):
;WITH cte_PIDS AS
( SELECT DISTINCT p.PId
FROM EMRDMPP.Enterprise.dbo.KUser ku
INNER JOIN EMRDMPP.Enterprise.dbo.DCommunityAccount dca
ON ku.UserID = dca.UserID
INNER JOIN EMRDMPP.Enterprise.dbo.DPartner dp
ON dca.DCommunityAccountKey = dp.DCommunityAccountKey
INNER JOIN PatientProfile p
ON dp.Identifier = p.PId)
UPDATE p
SET ContactByMId = CASE WHEN c.PId IS NULL THEN 1648 ELSE 226704 END
FROM PatientProfile p
LEFT JOIN cte_PIDS c
ON p.PId = c.PId;
Now we have a value we can look at for the banner. Next step is to add this value to a data object in one of our mldefs*.txt files. We chose mldefs3.txt, and we added it to an object we'd created some time ago called _EstablishedPatient:
Object: _EstablishedPatient
Table: PATIENTPROFILE
Refresh: RBook
Property: Snapshot
Property: Prepare
Field: NewEstPatient Type: String Length: 25
Field: ContactByMId Type: Long
Filter: PatientFilter, PatientProfileId = Patient.PatientProfileId Hidden
Note that this will not work for you as-is, because we have added a calculated field to PatientProfile called NewEstPatient that lets us easily determine whether a patient is new or established. (That's also in our banner, and I'll be happy to make those details available if anyone is interested.)
Next, this needs to be in mellib.txt:
global _MELNewEstPatient = _EstablishedPatient
Finally, this goes in the banner:
{! fn CONTACT_METHOD(){
local a = getRow("_MELNewEstPatient",0,"ContactByMId")
local ret
if a == 226704
then ret = "MyChart"
else ret = ""
endif
return ret
}}{CONTACT_METHOD()}
That puts the text "MyChart" wherever we want it in the patient's banner.
Posted : September 3, 2014 11:32 pm
