def save(self):
sh = shelve.open("data.db")
if self.id:
id = self.id
name = self.name.get()
rate = self.rate.get()
try:
cn = cdb.Db().connection()
cr = cn.cursor()
sql = "update service set name=%s , rate=%s where id=%s;"
cr.execute(sql, (name, rate, id))
cn.commit()
tmb.showinfo("Done", "updated service", parent=self.master)
try:
data = sh['misc']
except:
data = "\n"
data += "updated service " + name + " rate=" + str(rate) + "\n"
sh['misc'] = data
except:
tmb.showerror("Error",
"couldn't update database",
parent=self.master)
else:
if tmb.askyesno("Confirm", "Add New Service?", parent=self.master):
try:
name = self.name.get()
rate = self.rate.get()
cn = cdb.Db().connection()
cr = cn.cursor()
sql = "insert into service(name,rate) values(%s,%s);"
cr.execute(sql, (name, rate))
cn.commit()
tmb.showinfo("Done",
"Added the Service",
parent=self.master)
self.clear()
self.invlist.reload()
try:
data = sh['misc']
except:
data = "\n"
data += "added service " + name + " rate= " + str(
rate) + "\n"
sh['misc'] = data
except Exception, e:
tmb.showerror("Error -couldn't update database",
e,
parent=self.master)
def removePatient(id, name, ip):
output = []
output.extend(["patient discharged: " + name, "IP: " + ip])
sql = "update patient set discharged=1 where id=%s;"
con = cdb.Db().connection()
cur = con.cursor()
cur.execute(sql, [id])
sql = "select bill.id, bill.net, bill.date from bill join credit on bill.id=credit.billid join patient on patient.id=credit.patientid where patient.id=%s"
cur.execute(sql, [id])
result = cur.fetchall()
billtotal = 0
output.append(" ")
for row in result:
output.append(str(row[0]) + " - " + str(row[2]) + " - " + str(row[1]))
billtotal = billtotal + float(row[1])
output.append(" ")
output.append("total: " + str(billtotal))
printbill.printinfo(output)
con.commit()
sh = shelve.open("data.db")
try:
dischargetotal = sh['discharge']
except:
dischargetotal = 0
dischargetotal += billtotal
sh['discharge'] = dischargetotal
sh.close()
def update(self):
index = self.patients.index()
id = self.id
name = self.name.get()
address = self.address.get()
phone = self.phone.get()
note = self.note.get()
con = cdb.Db().connection()
cur = con.cursor()
try:
if not id:
if not tmb.askyesno(
"Confirm", "Add new Patient?", parent=self.master):
return
cur.execute(
"insert into patient(name,address,phone,note) values(%s,%s,%s,%s);",
(name, address, phone, note))
else:
cur.execute(
"update patient set address=%s, phone=%s,note=%s where id=%s;",
(address, phone, note, id))
con.commit()
except cdb.IntegrityError as e:
tmb.showerror("Error " + str(e.args[0]),
e.args[1],
parent=self.master)
return
self.fillPatients()
self.patients.see(index)
tmb.showinfo("Done", "Details updated", parent=self.master)
def __init__(self,
parent=None,
table="doc",
field="name",
title="Add New"):
tk.Frame.__init__(self, parent)
tk.Label(self, text=title).pack(side=tk.TOP)
self.lb = tk.Listbox(self)
sb = tk.Scrollbar(self, orient=tk.VERTICAL)
self.lb.config(yscrollcommand=sb.set)
sb.config(command=self.lb.yview)
sb.pack(side=tk.RIGHT, fill=tk.Y)
self.lb.pack(fill=tk.BOTH, expand=1)
self.tx = tk.StringVar()
fr = tk.Frame(self)
self.en = tk.Entry(fr, textvariable=self.tx)
btn = tk.Button(fr, text="Add", command=self.addnew, default="active")
btn.bind('<Return>', lambda event: self.addnew())
fr.pack(side=tk.BOTTOM)
self.en.pack(side=tk.LEFT)
btn.pack()
self.en.focus()
self.table = table
self.field = field
self.db = cdb.Db().connection()
self.refreshlist()
self.pack(padx=10, pady=10)
def showSponsorings(self):
donor = self.donors.get()[1][1]
cur = cdb.Db().connection().cursor(cdb.dictcursor)
cur.execute(
"select bill.date as date,patient.name as patient,bill.amount as value from donor join sponsorship on donor.id=sponsorship.donor join bill on sponsorship.bill=bill.id join patient on bill.patient=patient.id where donor.id=%s order by bill.date desc",
(donor))
rows = cur.fetchall()
self.sponsorlist.delete(ALL)
i = 0
self.sponsorlist.items = []
for r in rows:
f = Frame(self.sponsorlist, height=30)
date = r['date'].strftime('%d %b,%y')
Label(f, width=3, text=str(i + 1), bd=1,
relief=SUNKEN).pack(side=LEFT)
Label(f, width=8, text=date, bd=1, relief=SUNKEN).pack(side=LEFT)
Label(f,
width=10,
text="{:.10s}".format(r['patient']),
bd=1,
relief=SUNKEN).pack(side=LEFT)
Label(f, width=7, text=r['value'], bd=1,
relief=SUNKEN).pack(side=LEFT)
self.sponsorlist.create_window(1, 1 + i * 18, window=f, anchor=NW)
i += 1
self.sponsorlist.items.append(
"{:%d%b%y} {:10.10s} :{:9.2f}".format(r['date'], r['patient'],
r['value']))
self.sponsorlist.update_idletasks()
self.sponsorlist.config(scrollregion=self.donationlist.bbox(ALL))
def checkdb(self):
try:
db = cdb.Db()
d = db.connection()
except:
a = cdb.DbVariables()
a.wait_window()
def remove(self, f):
con = cdb.Db().connection()
cur = con.cursor()
sql = "delete from productservice where service=%s and product=%s;"
cur.execute(sql, (self.id, f.product))
con.commit()
self.load(self.id)
def refresh(self):
drug = self.drug.get()
self.can.delete(ALL)
con = cdb.Db().connection()
cur = con.cursor()
sql="select products.name,stock.id,stock.ini_count, stock.cur_count,stock.batch,stock.mrp, stock.expiry "\
"from products join stock on products.id=stock.product where products.name=%s order by stock.id desc;"
cur.execute(sql, (drug))
rows = cur.fetchall()
i = 1
f = Frame(self.can, bd=1)
Label(f, text="product", width=20).pack(side=LEFT)
Label(f, text="batch", width=10).pack(side=LEFT)
Label(f, text="count", width=5).pack(side=LEFT)
Label(f, text="stock", width=5).pack(side=LEFT)
Label(f, text="price", width=7).pack(side=LEFT)
Label(f, text="expiry", width=10).pack(side=LEFT)
self.can.create_window(5, 5, window=f, anchor=NW)
for row in rows:
f = Frame(self.can, bd=1, relief=RIDGE)
Label(f, text=row[0], width=20).pack(side=LEFT)
Label(f, text=row[4], width=10).pack(side=LEFT)
Label(f, text=row[2], width=5).pack(side=LEFT)
Label(f, text=row[3], width=5).pack(side=LEFT)
Label(f, text=row[5], width=7).pack(side=LEFT)
Label(f, text=row[6], width=10).pack(side=LEFT)
Button(f, text="edit",
command=lambda x=row[1]: self.edit(x)).pack(side=LEFT)
self.can.create_window(5, 5 + i * 30, window=f, anchor=NW)
i += 1
self.can.update_idletasks()
self.can.config(scrollregion=self.can.bbox(ALL))
self.edit(0)
def purchasepay(self):
if not password.askpass("admin"):
tkMessageBox.showerror("wrong password", "try again")
return
con = cdb.Db().connection()
cur = con.cursor()
sql = "select * from seller order by name;"
cur.execute(sql)
stockists = cur.fetchall()
items = [" PURCHASE PAYMENT", " "]
for stockist in stockists:
sql="select count(amount), sum(amount) ,group_concat(amount) from purchase "\
"where date < date_format(now(),'%Y-%m-01') and paid!=1 and seller="+str(stockist[0])+";"
print sql
cur.execute(sql)
if cur.rowcount > 0:
res = cur.fetchone()
count = res[0]
amount = res[1]
bills = res[2]
if count > 0:
item = " {:30s}{:10.2f} ::{}".format(
stockist[1] + " (" + str(count) + ")", amount, bills)
items.append(item)
sql = "update purchase set paid=1 where seller=" + str(
stockist[0]
) + " and date<date_format(now(),'%Y-%m-01');"
cur.execute(sql)
con.commit()
printer.printinfo(items)
def donate(self):
if not tmb.askyesno("Confirm", "Make Donation?", parent=self.master):
return
con = cdb.Db().connection()
cur = con.cursor()
donor = self.donors.get()[1]
index = self.donors.index()
try:
value = self.value.get()
sql = "update donor set value=value+%s where id=%s"
cur.execute(sql, (self.value.get(), donor[1]))
sql = "insert into donation(donor, value) values(%s,%s);"
cur.execute(sql, (donor[1], value))
con.commit()
self.value.set("")
lines = []
lines.extend(printer.header)
lines.append(" ")
lines.extend([
"Received Rs " + "{:5.2f}".format(value),
"with thanks from " + donor[0]
])
lines.extend([
"towards sponsoship for dialysis for needy patients", " ",
"Thank you"
])
printer.printinfo(lines)
dayreport.dayrep.receive("donation:" + donor[0], float(value))
except Exception as e:
tmb.showinfo("Error ", str(e))
con.rollback()
self.changelist()
self.donors.see(index)
def monthreport(self):
if not password.askpass("admin"):
tkMessageBox.showerror("error", "wrong password")
return
con = cdb.Db().connection()
cur = con.cursor()
sql = "select min(id), max(id) from bill where year(date)=year(current_date-interval 1 month) and month(date)=month(current_date-interval 1 month);"
cur.execute(sql)
row = cur.fetchone()
(firstid, lastid) = (row[0], row[1])
sql = "select date, sum(net) from bill where year(date)=year(current_date-interval 1 month) and month(date)=month(current_date-interval 1 month) group by date;"
cur.execute(sql)
rows = cur.fetchall()
lines = []
today = dt.date.today()
first = today.replace(day=1)
lastmonth = first - dt.timedelta(days=1)
mony = lastmonth.strftime("%Y %B")
lines.append("Sale Report " + mony)
lines.append(" ")
lines.append("from bill #" + str(firstid) + " to #" + str(lastid))
lines.append(" ")
for r in rows:
item = ' {} {:10.2f}'.format(r[0], r[1])
lines.append(item)
printbill.printinfo(lines)
def showbills(self, id=None):
self.container.delete(ALL)
if id is None:
return
sql = "select bill.id as bill, bill.name as patient, bill.date as date, bill.net as amount from bill join credit on bill.id=credit.billid join patient on credit.patientid=patient.id where patient.id=%s;"
cur = cdb.Db().connection().cursor()
try:
cur.execute(sql, [id])
except cdb.mdb.Error as e:
tkMessageBox.showerror("Error " + str(e.args[0]),
e.args[1],
parent=self.master)
return
result = cur.fetchall()
i = 0
total = 0
for row in result:
f = Frame(self.container, relief=RIDGE, bd=1, pady=1)
Label(f, text=row[0], width=8).pack(side=LEFT)
Label(f, text=row[1], width=20).pack(side=LEFT)
Label(f, text=row[2], width=12).pack(side=LEFT)
Label(f, text=row[3], width=10).pack(side=LEFT)
self.container.create_window(1, i * 32, window=f, anchor=NW)
i = i + 1
total += row[3]
if total > 0:
label = Label(self.container, text="Total: " + str(total))
self.container.create_window(1, i * 32, window=label, anchor=NW)
self.container.update_idletasks()
self.container.config(scrollregion=self.container.bbox(ALL))
def loadgroups(self, c):
cur = cdb.Db().connection().cursor()
cur.execute("select * from groups order by name;")
lst = [["all", -1]]
rows = cur.fetchall()
for r in rows:
lst.append([r[1], r[0]])
c.changelist(lst)
def loaddrugs(self):
cur = cdb.Db().connection().cursor()
cur.execute("select * from drug order by name;")
rows = cur.fetchall()
items = []
for r in rows:
items.append([r[1], r[0]])
self.drugs.changelist(items)
def newgroup(self, e=None):
grp = self.newGroup.get()
con = cdb.Db().connection()
cur = con.cursor()
cur.execute("insert into groups (name) values(%s);", [grp])
con.commit()
self.loadgroups()
self.newGroup.set("")
def loadstockists(self, comp):
cur = cdb.Db().connection().cursor()
cur.execute("select * from stockist order by name;")
rows = cur.fetchall()
items = [["all", -1]]
for r in rows:
items.append([r[1], r[0]])
comp.changelist(items)
def loadproducts(self):
cur = cdb.Db().connection().cursor()
sql = "select * from products order by name;"
cur.execute(sql)
rows = cur.fetchall()
items = []
for r in rows:
items.append([r[1], r[0]])
self.product.changelist(items)
def load_gstpercentage(self, gst):
cur = cdb.Db().connection().cursor()
cur.execute("select distinct cgstp from stock order by cgstp;")
lst = []
rows = cur.fetchall()
for r in rows:
if r[0]:
lst.append([str(r[0]), float(r[0])])
gst.changelist(lst)
def reload(self):
cur = cdb.Db().connection().cursor()
sql = "select * from service order by name;"
cur.execute(sql)
rows = cur.fetchall()
temp = []
for r in rows:
temp.append([r[1], r[0]])
self.list.changelist(temp)
def add(self, e=None):
group = self.groups.get()[1]
drug = self.drugs.get()[1]
con = cdb.Db().connection()
cur = con.cursor()
cur.execute("insert into druggroup(groupid,drug) values(%s,%s);",
(group, drug))
con.commit()
self.loadgroups()
def add(self, event=None):
con = cdb.Db().connection()
cur = con.cursor()
sql = "insert into productservice values(%s,%s,%s);"
cur.execute(sql, (self.id, self.product.get()[1], self.count.get()))
con.commit()
self.load(self.id)
self.product.clear()
self.count.set("")
def loadgroupdrugs(self, e=None):
group = self.groups.get()[1]
cur = cdb.Db().connection().cursor()
cur.execute("select drug.name,drug.id,druggroup.groupid,groups.name from drug join druggroup on druggroup.drug=drug.id "\
" join groups on druggroup.groupid=groups.id where druggroup.groupid=%s;",[group])
rows = cur.fetchall()
items = []
for r in rows:
items.append([r[0], [r[1], r[2], r[0], r[3]]])
self.druggroup.changelist(items)
def changelist(self, e=None):
con = cdb.Db().connection()
cur = con.cursor()
sql = "select name, id , value from donor order by name;"
cur.execute(sql)
rows = cur.fetchall()
arr = []
for row in rows:
arr.append([row[0], row])
self.donors.changelist(arr)
def fillPatients(self):
con = cdb.Db().connection()
cur = con.cursor()
sql = "select name,id,address,phone,note from patient order by name;"
cur.execute(sql)
rows = cur.fetchall()
items = []
for r in rows:
items.append([r[0], r])
self.patients.changelist(items)
def fillbanks(self):
i = self.banks.index()
cur = cdb.Db().connection().cursor()
cur.execute("select * from banks order by name")
rows = cur.fetchall()
items = []
for r in rows:
items.append([r[1], r])
self.banks.changelist(listitems=items)
self.banks.see(i)
def add(self):
if not tmb.askyesno("Confirm", "Add Donor?", parent=self.master):
return
con = cdb.Db().connection()
cur = con.cursor()
sql = "insert into donor (name,value) values(%s,0);"
cur.execute(sql, (self.addnew.get()))
con.commit()
self.changelist()
self.addnew.set("")
def getPatients():
con = cdb.Db().connection()
cur = con.cursor()
patients = []
cur.execute("select * from patient where discharged=0 order by name;")
result = cur.fetchall()
for row in result:
name = row[1]
name = name.split("::")
patients.append([name[0], name[1], row[0]]) #ip,name,id
return patients
def fillsponsors(self): con=cdb.Db().connection() cur=con.cursor() sql="select * from donor order by name;" cur.execute(sql) rows=cur.fetchall() items=[] items.append(["",None]) for row in rows: items.append([row[1],row]) self.sponsors.changelist(items)
def delgroup(self, e=None):
grp = self.groups.get()
if not tmb.askyesno("Confirm",
"Delete group {}?".format(grp[0]),
parent=self.master):
return
con = cdb.Db().connection()
cur = con.cursor()
cur.execute("delete from groups where id=%s;", [grp[1]])
con.commit()
self.loadgroups()
def savestock(self, id, count, price, date):
con = cdb.Db().connection()
cur = con.cursor()
cur.execute("select str_to_date('" + date + "','%d-%b-%y');")
r = cur.fetchone()
date = r[0]
sql = "update stock set cur_count=%s,mrp=%s,expiry=%s where id=%s;"
cur.execute(sql, (count, price, date, id))
con.commit()
tkMessageBox.showinfo("Done", "Stock saved", parent=self.master)
self.refresh()
