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
Ins No.
Name
Add. 1
Add. 2
City
State
Zip
Sub Name
Group Name
Group Number
Policy Number
Ins Code
Gender
DOB
Other
<%
'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.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")%>
<%
objIRst.MoveNext
Loop
else ' NOT objIRst.EOF
Response.Write "No patients matched your search.
"
end if
%>
<%
objRst.close
Set objRst = Nothing
Set objConnect = Nothing
' End NewQuery
End If
%>
Hope this helps someone out there.
Comments