A19 Query response from database

[Pensive-Potato]

Hello,

I'm new to Mirth Connect. I would like to set up a channel that accepts A19 queries and connects to a postgres database to find the patient id from the A19 query [QRD.8.1] in the database's 'patients' table. It then needs to send a query response and in that response also return some additional info such as visit number, gender, first and last name, which it extracts from the database for that patient id.

So, how would I go about this? I don't think I can use a DB reader in the source right? I found the code example in mirth itself for connecting to a database, but then I'm a bit lost as to how to continue. Where would I place this javascript code, in the source filter? Once connected to the database, how to query the 'patientid' field in the 'patients' database for the contents of msg['QRD']['QRD.8']['QRD.8.1']? And, if found, return that same patient id in the query response, as well as some other data that it gets from fields in the database?

Many thanks in advance for any help you can offer.

[Hueli]

You could place your javascript code in the postprocessor section. In this example (for Oracle DB), the query structure is reused for the response. Of course, you could build it from scratch. You could put some extra checks in the source filter section

I'm not a qualified mirth expert, but this works:

// This script executes once after a message has been processed
const DEBUG = false
    
// allowed device prefix CLE_ or RAP_
var rDevName = /^(LUD_CLE)|(LUD_RAP)(?:_.*)$/

// must be set in configuratio map
var sDBAddress = configurationMap.get('ORBIS_DB_ADDRESS_KHVAM')
var sDBUser  = configurationMap.get('ORBIS_DB_USER')
var sDBPassword = configurationMap.get('ORBIS_DB_PW')
var status = SENT
var statusMessage = 'QRY response successfully generated'

var sPID
var sName
var sVorname
var sBirthDate
var sSex

var sResponse = new XML(SerializerFactory.getSerializer('HL7V2').toXML(message.getConnectorMessages().get(0).getRawData()))

var sSendingApp = sResponse['MSH']['MSH.3']['MSH.3.1'].toString()
var sSendingDevType = sResponse['MSH']['MSH.4']['MSH.4.1'].toString() // device type
var sSendingDevName = sResponse['MSH']['MSH.4']['MSH.4.2'].toString() // device name
var sReceivingApp = sResponse['MSH']['MSH.5']['MSH.5.1'].toString()
var sReceivingFac = sResponse['MSH']['MSH.6']['MSH.6.1'].toString()

//
// Build MSH
//

// Swap Direction
sResponse['MSH']['MSH.3'] = sReceivingApp
sResponse['MSH']['MSH.4'] = sReceivingFac
sResponse['MSH']['MSH.5'] = sSendingApp
sResponse['MSH']['MSH.6']['MSH.6.1'] = sSendingDevType
sResponse['MSH']['MSH.6']['MSH.6.2'] = sSendingDevName

//Time
sResponse['MSH']['MSH.7']['MSH.7.1'] = DateUtil.getCurrentDate("yyyyMMddHHmmss")

// Response Type
sResponse['MSH']['MSH.9']['MSH.9.1'] = 'ADR'
sResponse['MSH']['MSH.9']['MSH.9.2'] = 'A19'

//
// Build PID
//

if (sSendingDevName.search(rDevName) == -1) {  // 'ERROR 04'
	sPID = ''
	sName = 'unknown device (ERR 04)'
	sVorname = 'Please inform lab!'
	sBirthDate = '19000101'
	sSex = 'U'
} else {
	var sVN = sResponse['QRD']['QRD.8']['QRD.8.1'].toString()
	if (DEBUG) logger.info(channelName + ": VN = " + sVN)
	

	var sSQL = "SELECT "
	sSQL += "    T3_NATPERSON.PID PID ,"
	sSQL += "    T3_NATPERSON.NAME NAME ,"
	sSQL += "    T3_NATPERSON.VORNAME VORNAME ,"
	sSQL += "    TO_CHAR(T3_NATPERSON.GEBDAT,'yyyymmdd') BIRTHDATE ,"
	sSQL += "    DECODE(T7_CATALOGDEF.LID,'MALE','M','FEMALE','F','U') SEX "
	sSQL += "FROM"
	sSQL += "    NATPERSON T3_NATPERSON,"
	sSQL += "    FALL T5_FALL,"
	sSQL += "    PATIENT T6_PATIENT,"
	sSQL += "    CATALOGDEF T7_CATALOGDEF "
	sSQL += "WHERE"
	sSQL += "    T7_CATALOGDEF.DBUID = T3_NATPERSON.SEX AND"
	sSQL += "    T6_PATIENT.PERSNR = T5_FALL.PERSNR AND"
	sSQL += "    T3_NATPERSON.PERSNR = T6_PATIENT.PERSNR AND"
	sSQL += "    T5_FALL.FALLNR = '" + sVN + "'"
	
	if (DEBUG) logger.info(channelName + ": sSQL = " + sSQL)
	
	try {
		var dbConn = DatabaseConnectionFactory.createDatabaseConnection('oracle.jdbc.OracleDriver', sDBAddress,sDBUser, sDBPassword)
		try { 
			var oResult = dbConn.executeCachedQuery(sSQL)
			if (oResult.next()) {
				sPID = oResult.getString("PID")
				sName = oResult.getString("NAME")
				sVorname = oResult.getString("VORNAME")
				sBirthDate = oResult.getString("BIRTHDATE")
				sSex = oResult.getString("SEX")
			} else { // 'ERROR 03'
				sPID = ''
				sName = 'Case not found!'
				sVorname = 'Wrong label?'
				sBirthDate = '19000101'
				sSex = 'U'
			}
			
		} catch(e) { // 'ERROR 02'
			sErrorMsg = channelName + ': SELECT statement Failed - ' + e.toString()
		  	logger.error(sErrorMsg)
	  		sPID = ''
			sName = 'ERROR 02'
			sVorname = 'Inform IT!'
			sBirthDate = '19000101'
			sSex = 'U'
		} finally {
			dbConn.close()		
		}
	}
	catch(e){ // 'ERROR 01'
		sErrorMsg = channelName + ': DB connection failed. - ' + e.toString()
		logger.error(sErrorMsg)
		sPID = ''
		sName = 'ERROR 01'
		sVorname = 'inform IT!'
		sBirthDate = '19000101'
		sSex = 'U'
	}
}

sResponse['PID']['PID.1'] = '1'
sResponse['PID']['PID.2'] = sPID
sResponse['PID']['PID.3'] = sPID
sResponse['PID']['PID.5']['PID.5.1'] = sName
sResponse['PID']['PID.5']['PID.5.2'] = sVorname
sResponse['PID']['PID.7'] = sBirthDate
sResponse['PID']['PID.8'] = sSex  // F/M/U
sResponse['PID']['PID.12'] = 'DE'  // F/M/U
sResponse['PID']['PID.18'] = ''  // Billing Number
sResponse['PID']['PID.19'] = ''  // Accession Number

sHL7Response = SerializerFactory.getSerializer('HL7V2').fromXML(sResponse)

return new Response(status, sHL7Response, statusMessage)

[Pensive-Potato]

Thank You Hueli! That looks like a ready made solution :)
Now I need to go through it and figure out what to adapt; I'm very new to javascript so this will be a fun challenge :)

[Pensive-Potato]

Ok, so I adapted the code a little and added the database connection data to the configuration map. My adaptations to the SQL query are probably wrong, but I don't even get that far. When I send a message to this channel:

On the mappings tab I see a postprocessor response is generated, but in the messages view, I just see a regular ack, not the formatted response message from the postprocessor:

Do I need to edit something somewhere still? (sorry for all the noob questions)

Many thanks in advance!

[Hueli]

Sorry, I missed to mention, that in the source connector "Response" listbox, you have to select "Postprocessor".

[Pensive-Potato]

Works like a charm! Thank you so much, this is exactly what I was looking for :)

[Hueli]

That's nice to hear. If you have no further questions, please mark your question as "answered".