def validate(self,controller):
search = self.Combo.get()
if not self.entry_search.get():
messagebox.showwarning("WARNING","Enter the Details")
if(search == "Patient Name"):
y = "patient_name"
elif(search == "Mobile"):
y = "mobile"
elif(search == "Patient Id"):
y = "id_number"
elif(search == "Date"):
y = "date"
elif(search == "Doctor Name"):
y = "doctor_name"
q = self.entry_search.get()
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute("""SELECT patient_name,doctor.id_number,date,doctor_name,treatment_given,additional_remark FROM patient_record JOIN doctor ON patient_record.id_number = doctor.id_number WHERE %s = '%s' ORDER BY doctor.id_number """%(y,q))
'''if (y=="patient_name" or y=="mobile" or y=="patient_id"):
mycursor.execute("SELECT * from patient_record WHERE %s='%s'"%(y,q))
else:
mycursor.execute("SELECT patient_name,doctor.id_number,date,doctor_name,treatment_given,additional_remark FROM patient_record JOIN doctor ON patient_record.id_number = doctor.id_number ORDER BY doctor.id_number WHERE %s ='%s'"%(y,q))
'''
self.fetch = mycursor.fetchall()
mydb = get_connection()
mycursor = mydb.cursor()
if not self.fetch:
messagebox.showerror("ERROR","Entry Doesn't Exist")
self.entry_search.delete(0,tk.END)
else:
self.Combo.current(0)
self.entry_search.delete(0,tk.END)
self.searchDisplay(controller)
def submit(self,controller):
a=str(self.entry_id.get())
b=str(self.entry_pass.get())
if(a == ""):
messagebox.showinfo("MESSAGE","Please Enter ID")
elif(b == ""):
messagebox.showinfo("MESSAGE","Please Enter Password")
else:
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute("SELECT login_name from login WHERE login_name='%s'"%(a))
x = mycursor.fetchone()
n = str(x)
m = "('"+a+"',)"
if(n!=m):
messagebox.showinfo("MESSAGE","ID Doesn't Exist")
self.entry_id.delete(0,tk.END)
self.entry_pass.delete(0,tk.END)
else:
mycursor.execute("SELECT password FROM login WHERE login_name= '%s'"%(a))
for i in mycursor:
a1=list(i)
for i in a1:
c=i
if(b!=c):
messagebox.showinfo("MESSAGE","INCORRECT PASSWORD")
self.entry_pass.delete(0,tk.END)
else:
self.entry_id.delete(0,tk.END)
self.entry_pass.delete(0,tk.END)
controller.show_frame(Record)
mydb.close()
def newlogin(self,controller):
staff_id = str(self.entry_id.get())
password = str(self.entry_pass.get())
conpass = str(self.entry_conpass.get())
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute("SELECT login_name from login WHERE login_name='%s'"%(staff_id))
y = mycursor.fetchone()
i = str(y)
j = "('"+staff_id+"',)"
if(i==j):
messagebox.showinfo("WARNING","ID already exists ")
self.entry_id.delete(0,tk.END)
self.entry_pass.delete(0,tk.END)
self.entry_conpass.delete(0,tk.END)
else:
if(password!=conpass):
messagebox.showinfo("WARNING","Password Doesnt Match ")
self.entry_pass.delete(0,tk.END)
self.entry_conpass.delete(0,tk.END)
else:
mycursor.execute("insert into login(login_name,password)values('%s','%s')" %(staff_id,password))
mydb.commit()
messagebox.showinfo("CREATED","New Login Created")
self.entry_id.delete(0,tk.END)
self.entry_pass.delete(0,tk.END)
self.entry_conpass.delete(0,tk.END)
controller.show_frame(LoginMain)
mydb.close()
def display(self):
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute("select * from patient_record")
result = mycursor.fetchall()
mydb.close()
tree = Treeview(self.root,
height=20,
column=("c1", "c2", "c3", "c4", "c5", "c6"),
show='headings')
#style.configure("Treeview",background="white",foreground="black",fieldbackground="silver")
#style.map("Treeview",background=[('selected','blue')])
tree.column("#1", anchor=CENTER, minwidth=0, width=100, stretch=NO)
tree.heading("#1", text="Patient Name")
tree.column("#2", anchor=CENTER, minwidth=0, width=100, stretch=NO)
tree.heading("#2", text="Mobile No")
tree.column("#3", anchor=CENTER, minwidth=0, width=100, stretch=NO)
tree.heading("#3", text="Patient ID")
tree.column("#4", anchor=CENTER, minwidth=0, width=100, stretch=NO)
tree.heading("#4", text="Address")
tree.column("#5", anchor=CENTER, minwidth=0, width=100, stretch=NO)
tree.heading("#5", text="Age")
tree.column("#6", anchor=CENTER, minwidth=0, width=100, stretch=NO)
tree.heading("#6", text="Gender")
hsb = Scrollbar(self.root, orient="horizontal", command=tree.xview)
hsb.place(x=150, y=428, width=500)
tree.configure(xscrollcommand=hsb.set)
tree.pack()
for x in result:
tree.insert("", END, values=x)
self.b2 = Button(self.root,
text='Quit',
width=10,
command=self.root.destroy).place(x=500, y=460)
def display(self):
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute("""SELECT patient_name,doctor.id_number,date,doctor_name,treatment_given,additional_remark FROM patient_record JOIN doctor ON patient_record.id_number = doctor.id_number ORDER BY doctor.id_number""")
result = mycursor.fetchall()
mydb.close()
tree = Treeview(self.root,height = 20, column=("c1", "c2", "c3","c4","c5","c6"), show='headings')
#style.configure("Treeview",background="white",foreground="black",fieldbackground="silver")
#style.map("Treeview",background=[('selected','blue')])
tree.column("#1", anchor=CENTER,minwidth=0, width=100, stretch=NO)
tree.heading("#1", text="Patient Name")
tree.column("#2", anchor=CENTER,minwidth=0, width=100, stretch=NO)
tree.heading("#2", text="Patient ID")
tree.column("#3", anchor=CENTER,minwidth=0, width=100, stretch=NO)
tree.heading("#3", text="Date")
tree.column("#4", anchor=CENTER,minwidth=0, width=100, stretch=NO)
tree.heading("#4", text="Doctor Name")
tree.column("#5", anchor=CENTER,minwidth=0, width=100, stretch=YES)
tree.heading("#5", text="Treatment Given")
tree.column("#6", anchor=CENTER,minwidth=0, width=120, stretch=YES)
tree.heading("#6", text="Additional Remarks")
hsb = Scrollbar(self.root, orient="horizontal", command=tree.xview)
hsb.place(x=150, y=428,width=500)
tree.configure(xscrollcommand=hsb.set)
tree.pack()
for x in result:
tree.insert("", END, values=x)
self.b2= Button(self.root, text='Quit',width=10,command=self.root.destroy).place(x=350,y=460)
def validate(self, controller):
if not self.entry_name.get():
messagebox.showwarning("WARNING", "Enter the Patient's Name")
if not self.entry_mobile.get():
messagebox.showwarning("WARNING", "Enter Mobile Number")
if self.entry_mobile.get():
if self.entry_mobile.get().isdigit() == False:
messagebox.showwarning(
"WARNING",
"Enter Only Numerical Values in Mobile Number Field")
self.entry_mobile.delete(0, tk.END)
else:
if self.Combo.get() == "+91":
if len(str(self.entry_mobile.get())) != 10:
messagebox.showerror(
"ERROR",
"Incorrect Mobile Number\nEnter Mobile Number Again"
)
self.entry_mobile.delete(0, tk.END)
if self.Combo.get() == "022":
if len(str(self.entry_mobile.get())) != 8:
messagebox.showerror(
"ERROR",
"Incorrect Landline Number\nEnter Landline Number Again"
)
self.entry_mobile.delete(0, tk.END)
if not self.entry_id.get():
messagebox.showinfo("WARNING", "Enter the Patient's ID")
else:
if self.entry_id.get().isdigit() == False:
messagebox.showwarning(
"WARNING", "Enter Only Numerical Values in the ID Field")
self.entry_id.delete(0, tk.END)
else:
q = self.entry_id.get()
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute(
"SELECT id_number from patient_record WHERE id_number='%s'"
% (q))
w = mycursor.fetchone()
mydb.close()
s = str(w)
t = "(" + q + ",)"
if (s == t):
messagebox.showerror("ERROR", "ID Already Exists")
self.entry_id.delete(0, tk.END)
if not self.entry_add.get():
messagebox.showwarning("WARNING", "Enter the Patient's Address")
if not self.entry_age.get():
messagebox.showwarning("WARNING", "Enter the Patient's Age")
else:
if self.entry_age.get().isdigit() == False:
messagebox.showwarning(
"WARNING", "Enter Only Numerical Values in the Age Field")
self.entry_age.delete(0, tk.END)
self.entry(controller)
def excel1():
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute( "select * from patient_record" )
result1 = mycursor.fetchall()
mydb.close()
df1 = pd.DataFrame(result1)
df1.columns = ["Patient Name","Mobile","Patient Id","Address","Age","Gender"]
df1.index = df1.index + 1
df1.to_csv('Patient Details.csv')
def excel():
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute("""SELECT patient_name,doctor.id_number,date,doctor_name,treatment_given,additional_remark FROM patient_record JOIN doctor ON patient_record.id_number = doctor.id_number ORDER BY doctor.id_number""")
result = mycursor.fetchall()
mydb.close()
df = pd.DataFrame(result)
df.columns = ["Patient Name","Patient Id","Date","Doctor Name","Treatment Given","Additional Remarks"]
df.index = df.index + 1
df.to_csv('Records.csv')
def update(self):
x = self.entry_1.get()
y = self.entry_2.get()
if not self.entry_1.get():
messagebox.showwarning("WARNING", "Enter the Patient's Name")
if not self.entry_2.get():
messagebox.showwarning("WARNING", "Enter the Patient's ID")
else:
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute(
"SELECT patient_name,id_number from patient_record WHERE id_number='%s'"
% (y))
u = mycursor.fetchone()
mydb.close()
if not u:
messagebox.showwarning("WARNING",
"Patient Name and ID do not match")
self.entry_1.delete(0, tk.END)
self.entry_2.delete(0, tk.END)
else:
u1 = str(u[0])
if (x != u1):
messagebox.showwarning("WARNING",
"Patient Name and ID do not match")
self.entry_1.delete(0, tk.END)
self.entry_2.delete(0, tk.END)
self.entry_3.delete(0, tk.END)
else:
a = self.Combo.get()
b = self.entry_3.get()
if not self.entry_3.get():
messagebox.showwarning("WARNING",
"Enter the new value")
else:
if (a == "mobile"):
if self.entry_3.get().isdigit() == False:
messagebox.showwarning(
"WARNING", "Enter Only Numerical Values")
self.entry_3.delete(0, tk.END)
else:
if len(str(self.entry_3.get())) > 10:
messagebox.showerror(
"ERROR",
"Incorrect Mobile Number\nEnter Mobile Number Again"
)
self.entry_3.delete(0, tk.END)
if (a == "age"):
if self.entry_3.get().isdigit() == False:
messagebox.showwarning(
"WARNING", "Enter Only Numerical Values")
self.entry_3.delete(0, tk.END)
self.updatesql()
def addsql(self):
x = self.entry_1.get()
y = self.entry_2.get()
if not self.entry_1.get():
messagebox.showwarning("WARNING", "Enter the Patient's Name")
if not self.entry_2.get():
messagebox.showwarning("WARNING", "Enter the Patient's ID")
else:
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute(
"SELECT patient_name,id_number from patient_record WHERE id_number='%s'"
% (y))
u = mycursor.fetchone()
if not u:
messagebox.showwarning("WARNING",
"Patient Name and ID do not match")
self.entry_1.delete(0, tk.END)
self.entry_2.delete(0, tk.END)
else:
u1 = str(u[0])
if (x != u1):
messagebox.showwarning("WARNING",
"Patient Name and ID do not match")
self.entry_1.delete(0, tk.END)
self.entry_2.delete(0, tk.END)
self.entry_doctor.delete(0, tk.END)
self.entry_treatment.delete(0, tk.END)
self.entry_remark.delete(0, tk.END)
else:
a = self.entry_1.get()
b = self.entry_2.get()
c = self.x
doctor_name = str(self.entry_doctor.get())
if (doctor_name == ""):
doctor_name = "NONE"
treatment = str(self.entry_treatment.get())
if (treatment == ""):
treatment = "NONE"
remarks = str(self.entry_remark.get())
if (remarks == ""):
remarks = "NONE"
mycursor.execute(
"insert into doctor(id_number,doctor_name,date,treatment_given,additional_remark)values('%s','%s','%s','%s','%s')"
% (b, doctor_name, c, treatment, remarks))
mydb.commit()
messagebox.showinfo("MESSAGE",
"Record Inserted Successfully!!")
Excel.excel()
Excel.excel1()
self.cleardata()
mydb.close()
def updatesql(self):
a = self.Combo.get()
b = self.entry_3.get()
c = self.entry_2.get()
if not (self.entry_3.get()):
messagebox.showinfo("ERROR","Enter Details first and then click on Submit Button")
else:
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute("UPDATE patient_record SET %s = '%s' WHERE id_number = '%s'" %(a,b,c))
mydb.commit()
mydb.close()
messagebox.showinfo("MESSAGE","Record Updated Successfully!!")
Excel.excel()
Excel.excel1()
self.clear()
def entry(self, controller):
name = str(self.entry_name.get())
mobile = str(self.entry_mobile.get())
id_no = str(self.entry_id.get())
address = str(self.entry_add.get())
age = str(self.entry_age.get())
gender = str(self.Combo1.get())
date = str(self.x)
doctor_name = str(self.entry_doctor.get())
if (doctor_name == ""):
doctor_name = "NONE"
treatment = str(self.entry_treatment.get())
if (treatment == ""):
treatment = "NONE"
remarks = str(self.entry_remark.get())
if (remarks == ""):
remarks = "NONE"
if not ((self.entry_name.get()) and (self.entry_mobile.get()) and
(self.entry_id.get()) and (self.entry_add.get()) and
(self.entry_age.get())):
messagebox.showinfo(
"ERROR", "Enter Details first and then click on Submit Button")
else:
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute(
"insert into doctor(id_number,doctor_name,date,treatment_given,additional_remark)values('%s','%s','%s','%s','%s')"
% (id_no, doctor_name, date, treatment, remarks))
mycursor.execute(
"insert into patient_record(patient_name,mobile,id_number,address,age,gender)values('%s','%s','%s','%s','%s','%s')"
% (name, mobile, id_no, address, age, gender))
mydb.commit()
mydb.close()
Excel.excel()
Excel.excel1()
messagebox.showinfo("MESSAGE", "Record Inserted Successfully!!")
resp = messagebox.askquestion(
"Record Inserted", "Do you want to insert a new Entry?")
if resp == "yes":
self.cleardata()
else:
self.cleardata()
self.back(controller)
def deletevalidate(self):
x = self.entry_1.get()
y = self.entry_2.get()
if not self.entry_1.get():
messagebox.showwarning("WARNING", "Enter the Patient's Name")
if not self.entry_2.get():
messagebox.showwarning("WARNING", "Enter the Patient's ID")
else:
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute(
"SELECT patient_name,id_number from patient_record WHERE id_number='%s'"
% (y))
u = mycursor.fetchone()
if not u:
messagebox.showwarning("WARNING",
"Patient Name and ID do not match")
self.entry_1.delete(0, tk.END)
self.entry_2.delete(0, tk.END)
else:
u1 = str(u[0])
if (x != u1):
messagebox.showwarning("WARNING",
"Patient Name and ID do not match")
self.entry_1.delete(0, tk.END)
self.entry_2.delete(0, tk.END)
else:
i = self.entry_2.get()
resp = messagebox.askquestion(
"DELETE!!", "Are You Sure You want to Delete?")
if resp == "yes":
mycursor.execute(
"DELETE FROM patient_record WHERE id_number= '%s'"
% (i))
mycursor.execute(
"DELETE FROM doctor WHERE id_number= '%s'" % (i))
mydb.commit()
messagebox.showinfo("MESSAGE",
"Records Deleted Successfully!!")
Excel.excel()
Excel.excel1()
self.cleardel()
else:
pass
mydb.close()
def display(self):
mydb = get_connection()
mycursor = mydb.cursor()
mycursor.execute(
"""SELECT patient_name, treatment.patient_id, date, age, gender, doctor_name, treatment_given, payment
FROM patient_record JOIN treatment ON patient_record.patient_id = treatment.patient_id ORDER BY treatment.patient_id"""
)
result = mycursor.fetchall()
mydb.close()
df = pd.DataFrame(result)
df.columns = [
'Name', 'ID', 'date', 'age', 'gender', 'doctor_name', 'treatment',
'payment'
]
#print(df)
date1 = [
"7 April", "8 April", "9 April", "10 April", "11 April",
"12 April", "13 April"
]
no = ["10", "12", "7", "12", "9", "13", "6"]
font1 = {'family': 'serif', 'size': 30}
font2 = {'family': 'serif', 'size': 15}
plt.figure(figsize=(16, 10))
plt.grid()
#plt.plot([row[0] for row in no])
plt.plot(no, marker='.', markerfacecolor='black', markersize=10)
plt.xlabel('Date', fontdict=font2)
plt.ylabel('No of Patients', fontdict=font2)
plt.title("No of Patients", fontdict=font1)
#plt.xticks(x, date1)
#plt.xticks(rotation=90)
#plt.legend(loc=4, bbox_to_anchor=(1,0), fancybox=True, shadow=True, ncol=2)
plt.show()
self.b2 = Button(self.root,
text='Quit',
width=10,
command=self.root.destroy).place(x=350, y=460)
from mysql_connector import get_connection mydb = get_connection() mycursor = db.cursor() table_login = """CREATE TABLE IF NOT EXISTS`login` ( `login_name` varchar(20) NOT NULL, `password` varchar(45) NOT NULL, PRIMARY KEY (`login_name`))""" table_doctor = """CREATE TABLE IF NOT EXISTS`doctor` ( `id_number` int(11) NOT NULL, `doctor_name` varchar(100) NOT NULL, `date` varchar(45) NOT NULL, `treatment_given` varchar(45) DEFAULT NULL, `additional_remark` varchar(45) DEFAULT NULL)""" table_record = """CREATE TABLE IF NOT EXISTS`patient_record` ( `patient_name` varchar(50) NOT NULL, `mobile` bigint(10) NOT NULL, `id_number` int(11) NOT NULL, `address` varchar(100) NOT NULL, `age` int(2) NOT NULL, `gender` varchar(10) NOT NULL, PRIMARY KEY (`patient_name`,`mobile`,`id_number`))""" #table_record = "DROP TABLE patient_record" mycursor.execute(table_login) mycursor.execute(table_doctor) mycursor.execute(table_record) db.commit() db.close()
