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 print_trans_list(self, e=None):
num = self.transactionlistprint.get()
lines = [
"Last " + str(num) + " transactions by " + self.banks.get()[1][1]
]
lines.extend(self.transactionlist.items[:num])
printer.printinfo(lines)
def printsponsorlist(self, e=None):
num = self.sponsorlistprint.get()
lines = [
"Last " + str(num) + " sponsorships by " + self.donors.get()[1][0]
]
lines.extend(self.sponsorlist.items[:num])
printer.printinfo(lines)
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 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 print_day_bills_summery(self):
if not password.askpass("admin"):
return
sh = shelve.open("data.db")
lines = []
myar = sh['bills-yesterday']
for l in myar['sale']:
lines.append(" {:7d} - {:8.2f}".format(l[0], l[1]))
printbill.printinfo(lines)
sh.close()
def print_dayreport(self, day):
d = day.strftime("%Y%m%d")
rep = self.get(d)
lines = ["Day closing report " + d]
for r in rep:
if r[2] == 0:
l = [r[0], r[1], r[3]]
else:
l = [r[0], 0 - float(r[2]), r[3]]
lines.append("{:20.20s}{:10.2f}{:10.2f}".format(*l))
printer.printinfo(lines)
def liststockists(self):
if not password.askpass("admin"):
tkMessageBox.showerror("error", "wrong password")
return
sql = "select name from stockist order by name;"
lines = ["STOCKISTS", " "]
con = cdb.Db().connection()
cur = con.cursor()
cur.execute(sql)
result = cur.fetchall()
for r in result:
lines.append(r[0])
printbill.printinfo(lines)
def dayreport(self):
if not password.askpass():
tkMessageBox.showerror("wrong password", "try again")
return
sh = shelve.open("data.db")
lines = [" DAY REPORT", " " + str(dt.date.today())]
try:
lastbill = sh['lastbill']
except:
lastbill = 0
try:
lastprint = sh['lastprint']
except:
lastprint = 0
lines.append("report from bill#" + str(lastprint + 1) + " to #" +
str(lastbill))
lines.append("")
lines.append('sale :' + "{0:.2f}".format(sh['sale']))
lines.append('ip sale :' + "{0:.2f}".format(sh['ipsale']))
lines.append('self sale:' + "{0:.2f}".format(sh['selfsale']))
lines.append('return :' + "{0:.2f}".format(sh['return']))
lines.append('discharge:' + "{0:.2f}".format(sh['discharge']))
lines.append('purchase :' + "{0:.2f}".format(sh['purchase']))
lines.append("")
csvstring = "{},{},{},{},{},{},{},{},{}\n".format(
str(dt.date.today()), lastprint + 1, lastbill, sh['sale'],
sh['ipsale'], sh['selfsale'], sh['return'], sh['discharge'],
sh['purchase'])
printbill.printinfo(lines)
sh['sale'] = 0
sh['purchase'] = 0
sh['return'] = 0
sh['ipsale'] = 0
sh['selfsale'] = 0
sh['discharge'] = 0
sh['lastprint'] = lastbill
self.statusSale.set(sh['sale'])
self.statusIp.set(sh['ipsale'])
sh['bills-yesterday'] = sh['bills']
myar = {"sale": [], "ipsale": []}
sh['bills'] = myar
sh.close()
try:
fil = open("dayreports.csv", "a")
fil.write(csvstring)
fil.close()
except:
pass
self.restatus()
def monthreport(self):
if not password.askpass("admin"):
tkMessageBox.showerror("error", "wrong password")
return
cur = cdb.Db().connection().cursor()
cur.execute("select count(id),sum(amount) from bill where date >= date_format(current_date-interval 1 month, '%Y/%m/01') and "\
"date <date_format(current_date,'%Y/%m/01')")
r = cur.fetchone()
lines = [
"Report of Month " + (dt.date.today().replace(day=1) -
dt.timedelta(days=1)).strftime("%b"), " "
]
lines.append("Number of bills: " + str(r[0]))
lines.append("Total Value: " + str(r[1]))
cur.execute("select count(bill.amount), sum(bill.amount) from bill join sponsorship on sponsorship.bill=bill.id "\
"where bill.date >= date_format(current_date-interval 1 month, '%Y/%m/01') and bill.date <date_format(current_date,'%Y/%m/01')")
r = cur.fetchone()
lines.append("Number of free bills: " + str(r[0]))
lines.append("Total value of free: " + str(r[1]))
printer.printinfo(lines)
def print_monthreport(self, day):
numdays = calendar.monthrange(day.year, day.month)[1]
days = [
datetime.date(day.year, day.month, d)
for d in range(1, numdays + 1)
]
lines = []
lines.append("{:10.10s}{:10.10s}{:10.10s}".format(
"date", "op_balance", "cl_balance"))
for d in days:
dd = d.strftime("%Y%m%d")
rep = self.get(dd)
op = ""
cl = ""
for line in rep:
if line[0] == "opening balance" and op == "":
op = line[2]
if line[0] == "closing balance":
cl = line[2]
lines.append("{:10.10s}{:>10.10s}{:>10.10s}".format(
d.strftime("%d %b,%y"), str(op), str(cl)))
printer.printinfo(lines)
def addcredit(self):
if not tmb.askokcancel("Add Credit Note?", "", parent=self.master):
return
con = cdb.Db().connection()
cur = con.cursor()
b = self.crnumber.get()
s = self.stockists.get()
d = self.crdate.get()
a = -(self.cramount.get())
sql = "insert into purchase(bill,stockist,date,amount,paid) values(" + str(
b) + "," + str(
s[1]) + ",str_to_date('" + d + "','%d-%b-%y')," + str(
a) + ",0);"
cur.execute(sql)
con.commit()
self.crnumber.set(0)
self.cramount.set(0)
self.showcredits(cur)
printout = []
printout.extend(printer.header)
printout.extend(("Credit Note : " + str(b), "stockists : " + str(s[0]),
"amount = " + str(-a), "date : " + str(d), ""))
printer.printinfo(printout)
def updatebill(self):
if not tkMessageBox.askyesno(
"confirm update",
"Are you sure you want to modify bill " + str(self.curbill),
parent=self.parent):
return
self.billno.set(self.curbill)
con = cdb.Db().connection()
cur = con.cursor()
returnamount = 0
dat = dt.date.today()
dat = " {:%d %b %y, %a}".format(dat)
printout = []
printout.extend(printbill.header)
printout.extend(("", " BILL RETURN ",
"Bill number: " + str(self.curbill), dat, ""))
ip = False
if self.isip(self.curbill, cur):
ip = True
try:
for f in self.items:
count = f.count.get()
if count != f.oldcount:
if count >= f.oldcount or self.isexpired(f.id, cur):
printout.append(
"cant return " + f.drug +
" since either count entered is wrong, or the stock is expired"
)
continue
sql = "update sale set count = %s where id=%s"
cur.execute(sql, (str(count), str(f.id)))
returnamount = returnamount + (f.oldcount -
count) * f.price
printout.append(f.drug + " " + str(f.oldcount - count) +
" " +
str((f.oldcount - count) * f.price))
sql = "update stock set cur_count=cur_count+%s where stock.id = (select stock from sale where id=%s);"
cur.execute(sql, (str(f.oldcount - count), str(f.id)))
if returnamount > 0:
sql = "update bill set net = net-%s where id=%s;"
cur.execute(sql, (str(returnamount), str(self.curbill)))
printout.extend(("", " RETURN AMOUNT: " + str(returnamount)))
con.commit()
self.master.restock()
if not ip:
printbill.printinfo(printout)
self.reprint()
sh = shelve.open("data.db")
try:
billreturn = sh['return']
except:
billreturn = 0
try:
bills = sh["bills"]
except:
sh['bills'] = {"sale": [], "ipsale": []}
sh['return'] = float(billreturn) + float(returnamount)
myar = sh['bills']
myar['sale'].append([self.curbill, -(returnamount)])
sh['bills'] = myar
sh.close()
else:
tkMessageBox.showinfo("Bill Updated",
"bill print out only if not IP bill",
parent=self.parent)
except cdb.mdb.Error as e:
tkMessageBox.showerror("Error " + str(e.args[0]),
e.args[1],
parent=self.parent)
con.rollback()
finally:
con.close()
self.searchbill()
def cancelbill(self):
if not tkMessageBox.askyesno(
"confirm Cancel",
"Are you sure you want to cancel bill " + str(self.curbill),
parent=self.parent):
return
self.billno.set(self.curbill)
con = cdb.Db().connection()
cur = con.cursor()
sql = "select sale.id from sale join bill on sale.bill=bill.id join stock on sale.stock=stock.id where stock.expiry < curdate() + interval 30 day and bill.id= %s;"
cur.execute(sql, [str(self.curbill)])
if cur.rowcount > 0:
tkMessageBox.showerror("Can not cancel bill",
"looks like one of the item is near expiry",
parent=self.parent)
return
try:
ip = False
if self.isip(self.curbill, cur):
ip = True
sql = "select sale.id as saleid, sale.count as count,sale.stock as stock from sale where sale.bill=%s;"
cur.execute(sql, [self.curbill])
rows = cur.fetchall()
for row in rows:
sql = "update stock set cur_count=cur_count+%s where stock.id=%s"
cur.execute(sql, (row[1], row[2]))
sql = "update sale set count=0 where id=%s"
cur.execute(sql, [row[0]])
sql = "select net from bill where id=%s;"
cur.execute(sql, [self.curbill])
row = cur.fetchone()
returnamount = row[0]
sql = "update bill set net=0 where id=%s;"
cur.execute(sql, [self.curbill])
con.commit()
self.master.restock()
if not ip:
printout = []
printout.extend(printbill.header)
dat = dt.date.today()
dat = " {:%d %b %y, %a}".format(dat)
printout.extend(("", " BILL CANCEL", dat))
printout.extend(("Bill no:" + str(self.curbill), "",
"Refund amount " + str(returnamount)))
printbill.printinfo(printout)
sh = shelve.open("data.db")
try:
billreturn = sh['return']
except:
billreturn = 0
sh['return'] = float(billreturn) + float(returnamount)
myar = sh['bills']
myar['sale'].append([self.curbill, -(returnamount)])
sh['bills'] = myar
sh.close()
else:
tkMessageBox.showinfo("Bill Cancelled",
"Refund only if bill is not IP",
parent=self.parent)
except cdb.mdb.Error as e:
tkMessageBox.showerror("Error " + str(e.args[0]),
e.args[1],
parent=self.parent)
con.rollback()
finally:
con.close()
self.searchbill()
def addbill(self, event=None):
if not tkMessageBox.askyesno(
"Confirm", "Save the purchase?", parent=self.parent):
return
db = cdb.Db().connection()
cur = db.cursor()
stockist = self.stockists.get()
billno = self.billno.get()
date = self.date.get()
total = str(self.total.get())
bill_cgst = str(self.cgst.get())
bill_sgst = str(self.sgst.get())
gstbill = self.gstbill.get()
try:
sql = "select id from stockist where name='" + stockist + "';"
cur.execute(sql)
row = cur.fetchone()
stockistid = row[0]
sql = "insert into purchase (bill,stockist,date,amount,cgst,sgst) values ('" + str(
billno
) + "','" + str(
stockistid
) + "',str_to_date('" + date + "','%d-%b-%y')," + total + "," + bill_cgst + "," + bill_sgst + ");"
cur.execute(sql)
billid = cur.lastrowid
billtotal = 0
printout = [
"", "", "PURCHASE", "", stockist, "bill: " + billno, ""
]
printout.append(
"{0:10s}{1:4s}{2:8s}{3:8s}{4:4s}{5:6s}{6:3s}".format(
"drug", "ct", "rate", "mrp", "gst", "exp", "sl"))
for f in self.items:
drug = f.drug
batch = f.batch
count = f.count
rate = f.rate
if gstbill == 1:
mrp = f.mrp / (1 + f.cgst / 100 + f.sgst / 100)
else:
mrp = f.mrp
print mrp
disc = f.disc
expiry = f.expiry
print expiry
dsql = "select id from drug where name='" + drug + "';"
cur.execute(dsql)
row = cur.fetchone()
drugid = row[0]
sql = "select sum(stock.cur_count) from stock where stock.drug_id=%s and stock.expiry> curdate();"
print "1"
cur.execute(sql, [drugid])
r = cur.fetchone()
existing_stock = r[0]
sql = "select sum(sale.count) from sale join bill on sale.bill=bill.id join stock on sale.stock=stock.id where bill.date> date_add(curdate(), interval -1 month) and stock.drug_id=%s;"
print "2"
cur.execute(sql, [drugid])
r = cur.fetchone()
lastmonth_sale = r[0]
if gstbill == 1:
sql = "insert into stock (batch,expiry,start_count,cur_count,drug_id,price,cgstp,sgstp, purchase_id,buy_price, discount,terminate,tax) values (%s,str_to_date(%s,%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,0,0)"
print "3"
cur.execute(
sql, (batch, expiry, '%d-%b-%y', count, count, drugid,
mrp, f.cgst, f.sgst, billid, rate, disc))
else:
sql = "insert into stock (batch,expiry,start_count,cur_count,drug_id,price, purchase_id,buy_price, discount,terminate) values (%s,str_to_date(%s,%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,0)"
print "4"
cur.execute(sql, (batch, expiry, '%d-%b-%y', count, count,
drugid, mrp, billid, rate, disc))
billtotal = billtotal + count * rate
if gstbill == 1:
mrp = f.mrp
printout.append(
"{0:10.10s}-{1:4d}-{2:6.2f}-{3:6.2f}-{4:2.2s}-{5:%y%m}-{6:2.1f}"
.format(
drug, int(count), float(rate), float(mrp),
str(int(f.cgst)),
dt.datetime.strptime(expiry, "%d-%b-%y").date(),
float(lastmonth_sale or 0) /
float(existing_stock or 1)))
db.commit()
printout.append(" ")
printout.extend([
"net total: " + str(billtotal), "bill total: " + total, "", ""
])
printbill.printinfo(printout)
sh = shelve.open("data.db")
try:
curpurchase = float(sh['purchase'])
except:
curpurchase = 0
sh['purchase'] = curpurchase + float(total)
sh.close()
self.items = []
self.refreshcanvas()
self.master.restock()
self.drug.focus()
except cdb.mdb.Error, e:
tkMessageBox.showerror("Error in database:",
"error %d: %s" % (e.args[0], e.args[1]),
parent=self.parent)
if db:
db.rollback()
def printout(self):
if len(self.lines) == 0:
return
if not password.askpass("admin"):
return
printbill.printinfo(self.lines)
def printlines(self):
printer.printinfo(self.lines)