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()