Unlimited custom reports in OD with Python

[jimgaas]

I thought this would be worth sharing. It is somewhat technical but I see there are many tech-heads who use OD. Our office pre-screens every patient at the beginning of each day. We check whether the patient needs premedication, has allergies, takes meds, for health conditions, delinquent treatment and recall schedules, both what is due and what the insurance company allows. A single printout looks like this:

*************************************

Chart: 533801
LastName FirstName
Patient does not require Premedication
Recall:
Prophy Recall due: 2013-07-09 Covered every: 393216
4 BW's due: 2013-06-20 Covered every: 786432
FM Due: 2014-06-20 Covered every: 1572864
Allergies: Penicillin, Latex
Meds: Prilosec Vitamin D Xyzal Sudafed Astepro
Conditions: Asthma
Del Tx: *Beg* D0120 periodic oral evaluation - established patient 1*End* *Beg* D1110 prophylaxis - adult 1*End*
*************************************
The weird numbers after "Covered every:" is the internally stored number that signifies the recall interval. 393216 is six months. This can be programmed to read "Six Months" instead of the number but we are used to the number and still haven't changed it.
This report is great for a morning huddle since it reviews all the important details of the patients for that day. Delinquent Treatment has "Beg" and "End" tags because we let the report wrap which causes it to be shorter than is we put a line feed in and have one item on every line. The program can be changed to do this easily. Here is the Python Program that creates this report:

import MySQLdb

a=open("daysheet.txt", 'w')

db = MySQLdb.connect(host="server", # your host or localhost
user="root", # your username
passwd="", # your password
db="your db name") # name of the data base, the default is opendental but you really should change that so it doesn't get accidentally overwritten if you redownload the software
cur = db.cursor()

#change the date below to do a daysheet for a different day, or better yet, change the program to put the date in on the
#command line. The limit statement is for debugging. This statement should only return one row.

cur.execute("SELECT * FROM appointment a where a.AptDateTime Like '2013-07-24%' limit 0,100")

for row in cur.fetchall() :
PatNum1=row[1]
a.write("\n" + "*************************************")
page= "\n \n" + "Chart: " + str(PatNum1) +"\n"
a.write(page)

cur.execute("select * from patient p where p.PatNum=%s",PatNum1)
for row in cur.fetchall():
page=str(row[1]) + " " + str(row[2]) + "\n"
a.write(page)
if row[53]==1:
a.write("Patient requires Premedication" + "\n")
if row[53]==0:
a.write("Patient does not require Premedication" + "\n")
if row[53] !=1 & row[53] !=2:
a.write("Premedication Status not marked in records: please correct")
cur.execute("select * from recall r where r.PatNum=%s",PatNum1)

a.write("Recall:" + "\n")

for row in cur.fetchall():
# row[10] is the recall type: 1 is prophy, 4 is 4 BW, and 5 is FM this may be different in your office
if row[10] == 1:
page="Prophy Recall due: " + str(row[2]) + " Covered every: " + str(row[5]) + "\n"
a.write(page)
if row[10] == 4:
page="4 BW's due: " + str(row[2]) + " Covered every: " + str(row[5]) + "\n"
a.write(page)
if row[10] == 5:
page="FM Due: " + str(row[2]) + " Covered every: " + str(row[5]) + "\n"
a.write(page)
#page=str(row[2]) + " " + str(row[3]) + " " + str(row[4]) + " " + str(row[5]) + " " + str(row[6]) + " " + str(row[10]) + "\n"
#a.write(page)
#row 2 is DateDueCalc, row 3 is DateDue, row 4 is DatePrevious, row 5 is RecallInterval
#row 6 is RecallStatus, row 10 is RecallType
# row 5 gets recall interval and row 10 gets recall type 4=4BW and 5=FM

cur.execute("select * from allergy a inner join allergydef ad on a.AllergyDefNum=ad.AllergyDefNum where a.PatNum=%s",PatNum1)
a.write("Allergies:" + " ")
for row in cur.fetchall():
page=" " + str(row[8]) + " "
a.write(page)
cur.execute("select * from medicationpat mp inner join medication m on mp.MedicationNum=m.MedicationNum where mp.PatNum=%s",PatNum1)
a.write("\n" + "Meds:")
for row in cur.fetchall():
page=" " + str(row[9]) + " "
a.write(page)
cur.execute("select * from disease d inner join diseasedef df on d.DiseaseDefNum=df.DiseaseDefNum where d.PatNum=%s",PatNum1)
a.write("\n" + "Conditions:")
for row in cur.fetchall():
page=" " + str(row[10] + " ")
a.write(page)
cur.execute("select * from procedurelog pl inner join procedurecode pc on pl.CodeNum=pc.CodeNum where pl.PatNum=%s",PatNum1)
a.write("\n" + "Del Tx:" + " ")
for row in cur.fetchall():
if row[10]==1:
print row[50]
print row[6],row[7],row[8],row[10]
page="*Beg* " + str(row[50]) + " " + str(row[51]) + " " + str(row[6]) + " " + str(row[7]) + " " + str(row[8]) + " " + str(row[10]) + "*End* "
a.write(page)

a.close


To run this program you must install python 2.7 on your computer and download MySqldb from the web. It seems that MySqldb doesn't run with newer versions of python. In a nutshell, the program gets the appointments for the day from the appointment table. You must change the date in the sql query to get a different day. If your computer is updated with meds, allergies and medical conditions, you should get that information. The program loops through the patients in the appointment table and then has other queries nested under that.

You can see that this is a very simple structure with almost unlimited potential to make custom reports in OD. If you keep your SQL queries to "SELECT" queries, you will not damage your database since you are just reading it. If you store the program in the python 2.7 directory, it will run automatically by double clicking it. Your daysheet report will be located here. You may print it if you open it in notepad, notepad++, or whatever you choose.

[jimgaas]

I just reviewed my post and I see that line indentations have been dropped in the python program. The program will not run unless this is changed. If you know python you will be able to fix this easily since you will know where the indentations are supposed to be. When I posted the program the indentations where put in correctly but it changed after posting. I am sorry about this. If there is any interest in this, I will try to arrange a correct posting. Sorry!

[jimgaas]

I tried to edit my post of the python program but the message board strips out leading spaces. I can write in
[tab][tab]for row in ... to signify that there would be two tabs for indentation on this line,

but I won't bother unless there is some interest. I suspect anyone interested in tackling this already knows python and doesn't need that help.

I should just say that the main loop is around the appointment table so pretty much everything after that point in indented except the last line a.close.

[jsalmon]

When using the Code box, I think it will allow tabs.

Code: Select all

if(tabs) {
	string tab="Tabs all[	]over the place!";
}

[jimgaas]

Code: Select all

import MySQLdb

a=open("daysheet.txt", 'w')

db = MySQLdb.connect(host="server", # your host, usually localhost
                     user="root", # your username
                      passwd="", # your password
                      db="your database name") # name of the data base opendental is the default

cur = db.cursor() 

cur.execute("SELECT * FROM appointment a where a.AptDateTime Like '2013-07-25%' limit 0,100")

for row in cur.fetchall() :
    PatNum1=row[1]

   
    a.write("\n" + "*************************************")
    page= "\n \n" + "Chart: " + str(PatNum1) +"\n"
    a.write(page)

    cur.execute("select * from  patient p where p.PatNum=%s",PatNum1)
    for row in cur.fetchall():
    	
    	page=str(row[1]) + " " + str(row[2]) + "\n"
    	a.write(page)
    	if row[53]==1:
    		
    		a.write("Patient requires Premedication" + "\n") 
    	if row[53]==0:
    		
    		a.write("Patient does not require Premedication" + "\n")
    	if row[53] !=1 & row[53] !=2:
    		
    		a.write("Premedication Status not marked in records: please correct")
    cur.execute("select * from recall r where r.PatNum=%s",PatNum1)
    

    a.write("Recall:" + "\n")
    
    for row in cur.fetchall():
    	
        if row[10] == 1:
            page="Prophy Recall due: " + str(row[2]) + " Covered every: " + str(row[5]) + "\n"
            a.write(page)
        if row[10] == 4:
            page="4 BW's due: " + str(row[2]) + " Covered every: " + str(row[5]) + "\n"
            a.write(page)  
        if row[10] == 5:
            page="FM Due: "  + str(row[2]) + " Covered every: " + str(row[5]) + "\n"
            a.write(page)  
    
    	#row 2 is DateDueCalc, row 3 is DateDue, row 4 is DatePrevious, row 5 is RecallInterval
    	#row 6 is RecallStatus, row 10 is RecallType
    	# row 5 gets recall interval and row 10 gets recall type 4=4BW and 5=FM
    
    cur.execute("select * from allergy a inner join allergydef ad on a.AllergyDefNum=ad.AllergyDefNum where a.PatNum=%s",PatNum1)

    a.write("Allergies:" + " ")
    for row in cur.fetchall():
   
    	page=" " + str(row[8]) + " "
    	a.write(page)
    cur.execute("select * from medicationpat mp inner join medication m on mp.MedicationNum=m.MedicationNum where mp.PatNum=%s",PatNum1)		
   
    a.write("\n" + "Meds:")
    for row in cur.fetchall():
    	
    	page=" " + str(row[9]) + " "
    	a.write(page)
    cur.execute("select * from disease d inner join diseasedef df on d.DiseaseDefNum=df.DiseaseDefNum where d.PatNum=%s",PatNum1)

    a.write("\n" + "Conditions:")
    for row in cur.fetchall():
        
        page=" " + str(row[10] + " ")
        a.write(page)
    cur.execute("select * from procedurelog pl inner join procedurecode pc on pl.CodeNum=pc.CodeNum where pl.PatNum=%s",PatNum1)

    a.write("\n" + "Del Tx:" + " ")
    for row in cur.fetchall():
    	#print row
    	if row[10]==1:
    		
    		page="*Beg* " + str(row[50]) + " " + str(row[51]) + " " + str(row[6]) + " " + str(row[7]) + " " + str(row[8]) + " " + str(row[10]) + "*End* "
    		a.write(page)

  
a.close

[jimgaas]

OK, that looks better. Thanks!

[Manny Ramirez]

Gr8 post. It believe it belongs in the Advanced Topic Section though!
Tested and it worked fine. I setup an Environment Variable path in Windows for Python. Created a folder in my Desktop and named it Daily-Sheet (it can be whatever you want). That way is easy to find the text (or csv) file and execute the program. I changed the format to csv instead of txt. Easier to read in Excel in my opinion. Also, why not just send it to print automatically ? The staff would be happier you did. Lastly, it would be nice if the code will automatically select "Today's Date", that way you don't have manually type it all the time

[jimgaas]

I am mildly surprised that someone got this up an running so easily. I am glad for that. I suppose you must have had to tweak your recall numbers, didn't you? My son wrote this program for me after seeing me labor manually looking up all this information in the charts. Needless to say, we are all striving to be paperless. Printing the output makes for a great morning huddle, but that fact that all this information was checked should be persisted. I laughed when you said you had changed the output to CSV because it was originally written for output in html with the thought of putting it into the WIKI. We thought about putting it into the little box that the describes the reason for the patient visit on the lower left of the patient chart or writing it into the notes field of the procedurelog table. I really do not want to write anything to the database for safety reasons but the WIKI seems like a good way to go. I think I am going to add another output file that will be in append mode in order to permanently save this output. The program is rough around the edges, for sure. I will try to post a fix to the date problem by having a date box with today's date but it can be changed to accept a different date.