Friday, September 12, 2008

Using Batch to decrypt PGP and more

This script was recently written to automatically process files on our FTP server. They come in daily, in pairs, and dated with todays date, e.g. XXERUP0912.dat.pgp.

The script checks for existence, ensures a pair is found, makes sure they are the right and same date, then decrypts them and calls an external program I wrote to inject them into a SQL database for querying via ASP, which I also wrote. If any of the steps fail, it gets logged, and an email notification is sent to a number of folks.

I had a bit of an issue with expansion, but found the error of my ways in short order...

@ECHO On
SETLOCAL ENABLEDELAYEDEXPANSION

:: //////////////////////////////////////////////
::
::  Set path variables for key access, passphrase,
::  and source our key files.

SET PGPPATH=C:\PGP_Keys\
SET PGPPASS=MYPGPPASS

:: The "KEY" to a successful import of our keys is running these commands as the user
:: that will be running this script.
::  pgp +batchmode -ka %PGPPATH%SECRING.SKR
::  pgp +batchmode -ka %PGPPATH%PUBRING.PKR
::  pgp -ke 0xEC671710 <-- after this command, you will be asked to trust the key; this must be done!


:: //////////////////////////////////////////////
::
::  Set our working dir, destination dir, _LOGFILE
::  and get in there!

SET _LOGFILE=H:\COVNENT\LOGS\DEMOUPDATELOG.TXT
SET _OPATH=H:\COVNENT\MEDICA~1\
H:
CD COVNENT

:: //////////////////////////////////////////////
::
:: Log beautification
ECHO --------------------------------------------------------------------------- >> %_LOGFILE%
ECHO -- START %DATE% - %TIME% -- >> %_LOGFILE%
ECHO --------------------------------------------------------------------------- >> %_LOGFILE%

::  //////////////////////////////////////////////
::
::  First test for existance of files, and
::  notify us if they don't exist.

DIR /B *ERUP*.PGP
IF NOT %ERRORLEVEL%==0 SET _MSG=No EMS Demographic Update files were found. & ECHO #### ERROR - NO FILES FOUND #### >> %_LOGFILE% & GOTO SENDMAIL


:: //////////////////////////////////////////////
::
::  Make sure our file count is correct. It should
::  be 2.
DIR /B *ERUP*.PGP | FIND /C /V "" > COUNT.TXT
FOR /F "tokens=1" %%f IN (COUNT.TXT) DO (
IF NOT %%f==2 SET _MSG=The file count of the EMS Demographic files is %%f, and 2 were expected. & DEL COUNT.TXT & ECHO #### ERROR - FILE COUNT WAS ONLY %%f. #### >> %_LOGFILE% & GOTO SENDMAIL
)


:: //////////////////////////////////////////////
::
::  Now, get the MMDD, and compare it to
::  what we received from Wheaton, which SHOULD
::  be the same. If not, send an email so it
::  can be checked out.


DEL COUNT.TXT
SET _TODAY=%DATE:~4,2%%DATE:~7,2%

FOR /F %%f IN ('DIR /B *ERUP*.PGP') DO (
SET _FILE=%%f
SET _FDATE=!_FILE:~6,4!
IF NOT !_FDATE! == !_TODAY! SET _MSG=The date of the EMS Update Demographic files do not match. --- FILE DATE = !_FDATE!, RUN DATE = !_TODAY! & ECHO #### ERROR - DATE MISMATCH - FILE DATE = !_FDATE!, RUN DATE = !_TODAY! #### >> %_LOGFILE% & GOTO SENDMAIL
)

:: //////////////////////////////////////////////
::
::  Loop through our encrypted files,
::  decrypt them and enter a log entry for each
::  file processed.

FOR /F %%f IN ('DIR /B *ERUP*.PGP') DO (
PGP +BATCHMODE %%f
IF %ERRORLEVEL% NEQ 0 (SET _ERR=!ERRORLEVEL! & SET _FILE=%%f & SET _MSG=There has been PGP decryption error on the EMS Demographic Update file, %%f. & GOTO PGPERR) ELSE (ECHO DECRYPTED %%f >> %_LOGFILE% & DEL %%f)
)

:: //////////////////////////////////////////////
::
::  Run our utility to import the EMS update
::  data into the database, echoing any output
::  to our log file.

C:\SCRIPTS\EMSUpdate.exe >> %_LOGFILE% 2>&1
GOTO EOF

:PGPERR
:: //////////////////////////////////////////////
::
:: Log all PGP errors, send an email, and quit
IF %_ERR%==33 ECHO #### %_FILE% CAUSED ERROR - DECOMPRESSION ERROR #### >> %_LOGFILE%
IF %_ERR%==32 ECHO #### %_FILE% CAUSED ERROR - DECRYPTION ERROR #### >> %_LOGFILE%
IF %_ERR%==31 ECHO #### %_FILE% CAUSED ERROR - PUBLIC KEY DECRYPTION ERROR #### >> %_LOGFILE%
IF %_ERR%==30 ECHO #### %_FILE% CAUSED ERROR - SIGNATURE CHECK ERROR #### >> %_LOGFILE%
IF %_ERR%==19 ECHO #### %_FILE% CAUSED ERROR - KEY SIGNATURE REMOVAL ERROR #### >> %_LOGFILE%
IF %_ERR%==18 ECHO #### %_FILE% CAUSED ERROR - KEY SIGNATURE ERROR OR KEY SIGNATURE REVOKE ERROR #### >> %_LOGFILE%
IF %_ERR%==17 ECHO #### %_FILE% CAUSED ERROR - KEYRING CHECK ERROR #### >> %_LOGFILE%
IF %_ERR%==16 ECHO #### %_FILE% CAUSED ERROR - KEYRING REMOVAL ERROR #### >> %_LOGFILE%
IF %_ERR%==15 ECHO #### %_FILE% CAUSED ERROR - KEYRING VIEW ERROR #### >> %_LOGFILE%
IF %_ERR%==14 ECHO #### %_FILE% CAUSED ERROR - KEYRING EDIT ERROR #### >> %_LOGFILE%
IF %_ERR%==13 ECHO #### %_FILE% CAUSED ERROR - KEYRING EXTRACT ERROR #### >> %_LOGFILE%
IF %_ERR%==12 ECHO #### %_FILE% CAUSED ERROR - KEYRING ADD ERROR #### >> %_LOGFILE%
IF %_ERR%==11 ECHO #### %_FILE% CAUSED ERROR - NON-EXISTING KEY ERROR #### >> %_LOGFILE%
IF %_ERR%==10 ECHO #### %_FILE% CAUSED ERROR - KEY GENERATION ERROR #### >> %_LOGFILE%
IF %_ERR%==7 ECHO #### %_FILE% CAUSED ERROR - OUT OF MEMORY ERROR #### >> %_LOGFILE%
IF %_ERR%==6 ECHO #### %_FILE% CAUSED ERROR - PROCESS INTERRUPTED #### >> %_LOGFILE%
IF %_ERR%==5 ECHO #### %_FILE% CAUSED ERROR - BAD ARGUMENT #### >> %_LOGFILE%
IF %_ERR%==4 ECHO #### %_FILE% CAUSED ERROR - BATCHMODE ERROR #### >> %_LOGFILE%
IF %_ERR%==3 ECHO #### %_FILE% CAUSED ERROR - UNKNOWN FILE #### >> %_LOGFILE%
IF %_ERR%==2 ECHO #### %_FILE% CAUSED ERROR - FILE NOT FOUND #### >> %_LOGFILE%
IF %_ERR%==1 ECHO #### %_FILE% CAUSED ERROR - INVALID FILE #### >> %_LOGFILE%
GOTO SENDMAIL


:SENDMAIL
:: //////////////////////////////////////////////
::
:: Set the temp file location
SET _TEMPMAIL=%TEMP%\TEMPMAIL.%RANDOM%.TXT

:: //////////////////////////////////////////////
::
:: Echo the basic headers to the temp file
ECHO TO: "Croson, John" ^<JOHN.CROSON@IHBSONLINE.COM^> > %_TEMPMAIL%
ECHO FROM: "IHBS Administrator" ^<ADMIN@IHBSONLINE.COM^> >> %_TEMPMAIL%
ECHO SUBJECT: EMS Demographic Update Failure >> %_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Echo the blank line that separates the header from the body text

ECHO.>>%_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Echo the body text to the temp file

ECHO %_MSG% >> %_TEMPMAIL%
ECHO Check %_LOGFILE% for details.>> %_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Move the temp file to the mail pickup directory
:: adjust this location for your system

MOVE %_TEMPMAIL% C:\INETPUB\MAILROOT\PICKUP
GOTO EOF


:EOF
ENDLOCAL
:: EXIT

Now for the bit that pushes it to the MSSQL server.

I wrote this small vb program that steps through each line in the flat text file, converts the line feeds from Unix to Windows, sanitizes the data, and injects it into a MS SQL database.

Here is the asp page that queries the database. Yes, I'm aware of SQL injection, but since this is a private page on an intranet web, I don't need to deal with script kiddies... ;)

<%@LANGUAGE=VBSCRIPT%>

<%

'Check when the last update was done
' Set the connection info
Conn = "Provider=SQLOLEDB; Data Source=IBSFP; Initial Catalog=dbUpdates; User ID=user; Password=pwd" 
'Create the connection
Set oCon = Server.CreateObject("ADODB.Connection")
'Open the connection object
oCon.Open Conn
'fetch the recordsets
Set oRst = Server.CreateObject("ADODB.Recordset")
sSql="SELECT MAX(update_date) AS update_date, MAX(file_date) AS file_date FROM tblPatient"
oRst.Open sSql,oCon,1,1
'Do While Not oRst.EOF
FileDate=oRst.Fields("file_date")
LastUpdate=oRst.Fields("update_date")
'Loop
Set oCon=Nothing
Set oRst=Nothing


	' Customization variables
	DebugFlag = False        ' set TRUE for debugging
	QueryForm = Request.ServerVariables("PATH_INFO")


If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
        CaseNo = Request.Form("CaseNo")
        MRN = Request.Form("MRN")

	       If Request.Form("Action") = "Search" Then
        	    NewQuery = TRUE
		End If
End If


%> 





EMS Demographic Update Search

Last Updated on <%=LastUpdate%>, with a file date of <%=FileDate%>.

    Important notes on use:
  • Search on Case Number OR MRN. Not both.
  • Wildcards are not used, and will return nothing.
  • Do not use letters in your MRN search, you will find nothing.


<% If DebugFlag then %>
	Case Number	= <%=CaseNo%>
	MRN		= <%=MRN%>
	NewQuery	= <%=NewQuery%>
	strSql		= <%=strSql%>
    
<% End If %> <% If NewQuery Then ' Set the connection info ConnStr = "Provider=SQLOLEDB; Data Source=IBSFP; Initial Catalog=dbUpdates; User ID=user; Password=pwd" 'Create the connection Set objConnect = Server.CreateObject("ADODB.Connection") 'Open the connection object objConnect.Open ConnStr ' Make sure a query is used If Trim(CaseNo) = "" And Trim(MRN) = "" Then Response.Write "

You failed to query for anything...
Try again.

" Response.End End If 'Set the query statement strSql = "Select tblGuarantor.*, tblPatient.*, tblSubscriber.* From " _ & "tblGuarantor Inner Join " _ & "tblPatient On tblGuarantor.case_number = tblPatient.case_number Inner Join " _ & "tblSubscriber On tblGuarantor.case_number = tblSubscriber.case_number WHERE (" If Trim(CaseNo) <> "" Then strSql = strSql & "tblSubscriber.case_number = '" & CaseNo & "'" End If If Trim(MRN) <> "" And Trim(CaseNo) = "" Then ' MRN Sql search strSql = strSql & "tblSubscriber.mrn = '" & MRN & "'" End If strSql = strSql & ")" 'fetch the recordsets Set objRst = Server.CreateObject("ADODB.Recordset") If DebugFlag Then Response.Write "
" & strSQL & "
" End If objRst.Open strSql,objConnect,1,1 Do While Not objRst.EOF If DebugFlag Then Response.Write objRst.Fields.Count For i = 0 to objRst.Fields.Count - 1 Response.Write "

" & objRst.Fields.Item(i).Name & " - " & objRst.Fields.Item(i) & "
" Next End If CaseNumber = objRst.Fields("case_number") %>

Case Number : <%=objRst.Fields("case_number")%>
MRN :<%=objRst.Fields("campus") & objRst.Fields("mrn")%>
File Date :<%=objRst.Fields("file_date")%>
Date Modified :<%=objRst.Fields("update_date")%>
Patient Information
Name :<%=objRst.Fields("pname")%>
Address 1 :<%=objRst.Fields("paddress1")%>
Address 2 :<%=objRst.Fields("paddress2")%>
City :<%=objRst.Fields("pcity")%>
State :<%=objRst.Fields("pstate")%>
Zip :<%=objRst.Fields("pzip")%>
Phone :<%=objRst.Fields("area") & "-" & Left(objRst.Fields("pphone"),3) & "-" & Right(objRst.Fields("pphone"),4)%>
Gender :<%=objRst.Fields("psex")%>
DOB :<%=objRst.Fields("dob")%>
SSN :<% Select Case Trim(objRst.Fields("ssn")) Case "" 'Do nothing Case "NONE" Response.Write objRst.Fields("ssn") Case Else Response.Write Left(objRst.Fields("ssn"),3) & "-" & Mid(objRst.Fields("ssn"),4,2) & "-" & Right(objRst.Fields("ssn"),4) End Select %>
Marital Status :<%=objRst.Fields("marital_status")%>
Cheif Complaint :<%=objRst.Fields("cc")%>
Personal MD :<%=objRst.Fields("per_md")%>
Admitting MD :<%=objRst.Fields("adm_md")%>
Disposition :<%=objRst.Fields("disposition")%>
Admit Date-Time :<%=objRst.Fields("adm_date") & " - " & objRst.Fields("adm_time")%>
Discharge Date :<%=objRst.Fields("dc_date")%>
Other :<%=objRst.Fields("pother")%>
Guarantor Information
Name :<%=objRst.Fields("gname")%>
Address 1 :<%=objRst.Fields("gaddress1")%>
Address 2 :<%=objRst.Fields("gaddress2")%>
City :<%=objRst.Fields("gcity")%>
State :<%=objRst.Fields("gstate")%>
Zip :<%=Left(objRst.Fields("gzip"),5) & "-" & Right(objRst.Fields("gzip"),4)%>
Phone :<%=Left(objRst.Fields("gphone"),3) & "-" & Mid(objRst.Fields("gphone"),5,3) & "-" & Right(objRst.Fields("gphone"),4)%>
Employer :<%=objRst.Fields("employer")%>
Employer Address 1 :<%=objRst.Fields("employer_address1")%>
Employer Address 2 :<%=objRst.Fields("employer_address2")%>
Employer City :<%=objRst.Fields("employer_city")%>
Employer State :<%=objRst.Fields("employer_state")%>
Employer Zip :<%=objRst.Fields("employer_zip")%>
Employer Phone :<%=Left(objRst.Fields("employer_phone"),3) & "-" & Mid(objRst.Fields("employer_phone"),5,3) & "-" & Right(objRst.Fields("employer_phone"),4)%>
Injury Date :<%=objRst.Fields("injury_date")%>
Injury Date :<%=objRst.Fields("injury_code")%>
Other :<%=objRst.Fields("gother1")%>
Subscriber Information
Primary Employer :<%=objRst.Fields("pri_sub_emp")%>
Secondary Employer :<%=objRst.Fields("sec_sub_emp")%>
Tertiary Employer :<%=objRst.Fields("tert_sub_emp")%>
Primary Subscriber :<%=objRst.Fields("pri_sub")%>
Secondary Subscriber :<%=objRst.Fields("sec_sub")%>
Tertiary Subscriber :<%=objRst.Fields("tert_sub")%>
Gender :<%=objRst.Fields("ssex")%>
SSN :<% Select Case Trim(objRst.Fields("sssn")) Case "" 'Do nothing Case "NONE" Response.Write objRst.Fields("sssn") Case Else Response.Write Left(objRst.Fields("sssn"),3) & "-" & Mid(objRst.Fields("sssn"),4,2) & "-" & Right(objRst.Fields("sssn"),4) End Select %>
Employer Name :<%=objRst.Fields("emp_name")%>
Employer Phone :<%=objRst.Fields("emp_phone")%>
Chief Complaint :<%=objRst.Fields("chief_complaint")%>
Hospital Discharge Date :<%=objRst.Fields("hdc_date")%>
Hospital Admit Mode :<%=objRst.Fields("hp_adm_mode")%>
Hospital Disposition :<%=objRst.Fields("hp_disp")%>
Other :<%=objRst.Fields("sother")%>
Campus :<%=objRst.Fields("campus")%>
<% objRst.MoveNext Loop strISql = "SELECT * FROM tblInsurance WHERE (case_number = '" & Trim(CaseNumber) & "')" Set objIRst = Server.CreateObject("ADODB.Recordset") objIRst.Open strISql,objConnect,1,1 If DebugFlag Then %>
<%=strISql%>
<%Response.Write objIRst.Fields.Count For i = 0 to objIRst.Fields.Count - 1 Response.Write "

" & objIRst.Fields.Item(i).Name & " - " & objIRst.Fields.Item(i) & "
" Next%> <% End If %>

Insurance Information

<% 'Shade every other record strOddColor = "bgcolor='#FFFFFF'" strEvenColor = "bgcolor='#C0C0C0'" intLineNum = 0 If Not objIRst.BOF And Not objIRst.EOF Then Do While Not objIRst.EOF ' This bit sets the alternating shades of rows intLineNum = intLineNum + 1 If intLineNum Mod 2 = 0 Then strLineColor = strEvenColor Else strLineColor = strOddColor End If %> > <% objIRst.MoveNext Loop else ' NOT objIRst.EOF Response.Write "

No patients matched your search.

" end if %>
Ins No. Name Add. 1 Add. 2 City State Zip Sub Name Group Name Group Number Policy Number Ins Code Gender DOB Other
<%=objIRst.Fields("ins_number")%> <%=objIRst.Fields("iname")%> <%=objIRst.Fields("iaddress1")%> <%=objIRst.Fields("iaddress2")%> <%=objIRst.Fields("icity")%> <%=objIRst.Fields("istate")%> <%=objIRst.Fields("izip")%> <%=objIRst.Fields("sub_name")%> <%=objIRst.Fields("group_name")%> <%=objIRst.Fields("group_number")%> <%=objIRst.Fields("policy_number")%> <%=objIRst.Fields("insurance_code")%> <%=objIRst.Fields("extra")%> <%=objIRst.Fields("auth")%> <%=objIRst.Fields("iother")%>
<% objRst.close Set objRst = Nothing Set objConnect = Nothing ' End NewQuery End If %>

Hope this helps someone out there.

No comments: