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.

Comments

Post a Comment

Popular posts from this blog

freeFTPD

Image
Image by Micah68 via Flickr I've been using FileZilla FTP server for some time now and have been happy for the performance. Recently, we needed the ability to expose the FTP service to another client, and the documents that we'd be receiving would be arriving in an un-encrypted form, unlike our other clients. I decided I could simply enable FTPS , the SSL enabled FTP protocol and open a port to 990 on my ASA 5525 Security Appliance and NAT traffic to our server. Unfortunately I quickly found out that a passive FTPS server behind my firewall won't work without some specific configuration changes as discussed in this article . With all that fussing around, I decided to check out freeFTPd, a single deamon that offers both FTP and SFTP, not to be confused with FTPS, but the secure file transfer protocol that is common to the SSH ( secure shell ) protocol. It's fairly straight forward, but is a bit quirky and the documentation is non-existent. Follow some of my ti...
Read more

DotNetNuke SEO

Image
I've been reading quite a bit lately on improving SEO for our DotNetNuke website , IHBSOnline.com and quickly realized it just isn't enough to throw up a website, add a listing in a couple of search engines and wait for the masses. First off, the DnnFriendlyUrlProvider, while writing friendlier URL's than older versions of DNN, could be better. This is why you need the iFinity FriendlyURL provider. It is free, but keep in mind it is really designed for a single domain site, and presents other issues with some other 3rd party modules. If you fall into that category, I'd suggest you purchase the full version, as it adds additional functionality you'll need and want. Image by AFP/Getty Images via Daylife I've learned that Google and possibly other search engines actually penalize you if you create duplicate content. The duplicate content issue could easily arise if you have a host that responds to both http://mydomain.com and http://www.mydomain.com. Yo...
Read more

Traveling via Amtrak

Image
My wife, son and I recently took a trip to St Louis , to visit her Mom and Dad. We always have a good time, but don't look forward to the drive, as it takes 7 hours or so one way from 'lil old SE Wisconsin . Image via Wikipedia Thankfully, she looked into an older style of travel for our most recent trip to visit G'ma and G'pa; Amtrak . The cost is not much more than it is to drive. Fuel costs driving our mini-van is usually about $120, not to mention the fuel my son expends attempting to break out of his car seat ... The cost for our train ride was $188 with tax. Our son rode for 1/2 price since he is only 3. The trip took nearly the same amount of time, and we could ALL nap on the way down. We were able to bring a small cooler with lunch, play for a couple of hours in the dining car ( coloring books , puzzles, etc.) The only complaint I will lodge is customer service is a bit lacking. Our initial inquiries indicated we had the ability to check a bag, but foun...
Read more