def details(regdent, trtdent, startdate, enddate, filters=""):
'''
returns an html table, for regdent, trtdent,startdate,enddate
'''
dent_conditions = ""
dents = []
try:
if regdent != "*ALL*":
dent_conditions = 'dntid=%s and '
dents.append(localsettings.ops_reverse[regdent])
if trtdent != "*ALL*":
dent_conditions += 'trtid=%s and '
dents.append(localsettings.ops_reverse[trtdent])
except KeyError:
print "Key Error - %s or %s unregconised" % (regdent, trtdent)
return '<html><body>%s</body></html>' % _(
"Error - unrecognised practioner- sorry")
total, nettotal = 0, 0
iterDate = QDate(startdate.year(), startdate.month(), 1)
retarg = '''
<html><body><h4>%s %s %s %s %s %s %s %s %s</h4>''' % (
_("Patients of"), regdent, _("treated by"), trtdent, _("between"),
localsettings.formatDate(startdate.toPyDate()), _("and"),
localsettings.formatDate(enddate.toPyDate()), filters)
retarg += '''<table width="100%" border="1"><tr><th>DATE</th>
<th>Dents</th><th>Serial Number</th><th>Name</th>
<th>Pt Type</th><th>Treatment</th><th></th>
<th>Gross Fee</th><th>Net Fee</th>'''
db = connect.connect()
cursor = db.cursor()
query = DETAILS_QUERY.replace("{{DENT CONDITIONS}}", dent_conditions)
query = query.replace("{{FILTERS}}", filters)
while enddate >= iterDate:
monthtotal, monthnettotal = 0, 0
if startdate > iterDate:
queryStartDate = startdate
else:
queryStartDate = iterDate
queryEndDate = iterDate.addMonths(1).addDays(-1)
if enddate < queryEndDate:
queryEndDate = enddate
values = tuple(
dents + [queryStartDate.toPyDate(), queryEndDate.toPyDate()])
cursor.execute(query, (values))
rows = cursor.fetchall()
for i, row in enumerate(rows):
retarg += '<tr>' if i % 2 else '<tr bgcolor="#eeeeee">'
retarg += "<td>%s</td>" % row[0]
try:
retarg += '<td> %s / ' % localsettings.ops[row[4]]
except KeyError:
retarg += "<td>?? / "
try:
retarg += localsettings.ops[row[5]]
except KeyError:
retarg += "??"
retarg += '</td><td>%s</td><td>%s</td><td>%s</td>' % (row[1:4])
tx = ""
for item in (6, 7, 8, 9, 10, 11, 12, 13, 14, 15):
if row[item] is not None and row[item] != "":
tx += "%s " % row[item]
if ALLOW_TX_EDITS:
extra_link = ' / <a href="daybook_id_edit?%s">%s</a>' % (
row[19], _("Edit Tx"))
else:
extra_link = ""
retarg += '''<td>%s</td>
<td><a href="daybook_id?%sfeesa=%sfeesb=%s">%s</a>%s</td>
<td align="right">%s</td>
<td align="right">%s</td></tr>''' % (tx.strip("%s " % chr(0)),
row[19], row[16], row[17],
_("Ests"),
extra_link,
localsettings.formatMoney(
row[16]),
localsettings.formatMoney(row[17]))
total += int(row[16])
monthtotal += int(row[16])
nettotal += int(row[17])
monthnettotal += int(row[17])
retarg += '''<tr><td colspan="6"></td><td><b>SUBTOTAL - %s %s</b></td>
<td align="right"><b>%s</b></td>
<td align="right"><b>%s</b></td></tr>''' % (
localsettings.monthName(iterDate.toPyDate()),
iterDate.year(),
localsettings.formatMoney(monthtotal),
localsettings.formatMoney(monthnettotal))
iterDate = iterDate.addMonths(1)
cursor.close()
# db.close()
retarg += '''<tr><td colspan="6"></td><td><b>GRAND TOTAL</b></td>
<td align="right"><b>%s</b></td>
<td align="right"><b>%s</b></td></tr></table></body></html>''' % (
localsettings.formatMoney(total), localsettings.formatMoney(nettotal))
return retarg
def details(regdent, trtdent, startdate, enddate):
'''
returns an html table, for regdent, trtdent,startdate,enddate
'''
cond1, cond2 = "", ""
try:
if regdent != "*ALL*":
cond1 = 'dntid=%s and' % localsettings.ops_reverse[regdent]
if trtdent != "*ALL*":
cond2 = 'trtid=%s and' % localsettings.ops_reverse[trtdent]
except KeyError:
print "Key Error - %s or %s unregconised" % (regdent, trtdent)
return '<html><body>%s</body></html>' % _(
"Error - unrecognised practioner- sorry")
total, nettotal = 0, 0
iterDate = QDate(startdate.year(), startdate.month(), 1)
db = connect()
cursor = db.cursor()
retarg = '''<html><body>
<h3>Patients of %s treated by %s between %s and %s (inclusive)</h3>''' % (
regdent, trtdent,
localsettings.formatDate(startdate.toPyDate()),
localsettings.formatDate(enddate.toPyDate()))
retarg += '''<table width="100%" border="1"><tr><th>DATE</th>
<th>Dents</th><th>Serial Number</th><th>Name</th>
<th>Pt Type</th><th>Treatment</th><th>Gross Fee</th><th>Net Fee</th>'''
while enddate >= iterDate:
monthtotal, monthnettotal = 0, 0
if startdate > iterDate:
queryStartDate = startdate
else:
queryStartDate = iterDate
queryEndDate = iterDate.addMonths(1).addDays(-1)
if enddate < queryEndDate:
queryEndDate = enddate
#-- note - mysqldb doesn't play nice with DATE_FORMAT
#-- hence the string is formatted entirely using python formatting
query = '''select DATE_FORMAT(date,'%s'), serialno, coursetype, dntid,
trtid, diagn, perio, anaes, misc, ndu, ndl, odu, odl, other, chart,
feesa, feesb, feesc, id from daybook
where %s %s date >= '%s' and date <= '%s' order by date''' % (
localsettings.OM_DATE_FORMAT, cond1, cond2,
queryStartDate.toPyDate(), queryEndDate.toPyDate())
cursor.execute(query)
rows = cursor.fetchall()
odd = True
for row in rows:
if odd:
retarg += '<tr bgcolor="#eeeeee">'
odd = False
else:
retarg += '<tr>'
odd = True
retarg += "<td>'%s' %s</td>" % (row[18], row[0])
try:
retarg += '<td> %s / ' % localsettings.ops[row[3]]
except KeyError:
retarg += "<td>?? / "
try:
retarg += localsettings.ops[row[4]]
except KeyError:
retarg += "??"
retarg += '</td><td>%s</td>' % row[1]
cursor.execute(
'select fname,sname from patients where serialno=%s' % row[1])
names = cursor.fetchall()
if names != ():
name = names[0]
retarg += '<td>%s %s</td>' % (name[0].title(), name[1].title())
else:
retarg += "<td>NOT FOUND</td>"
retarg += '<td>%s</td>' % row[2]
tx = ""
for item in (5, 6, 7, 8, 9, 10, 11, 12, 13, 14):
if row[item] is not None and row[item] != "":
tx += "%s " % row[item]
retarg += '''<td>%s</td><td align="right">%s</td>
<td align="right">%s</td></tr>''' % (tx.strip("%s " % chr(0)),
localsettings.formatMoney(
row[15]),
localsettings.formatMoney(row[16]))
total += int(row[15])
monthtotal += int(row[15])
nettotal += int(row[16])
monthnettotal += int(row[16])
retarg += '''<tr><td colspan="5"></td><td><b>%s TOTAL</b></td>
<td align="right"><b>%s</b></td>
<td align="right"><b>%s</b></td></tr>''' % (
localsettings.monthName(iterDate.toPyDate()),
localsettings.formatMoney(monthtotal),
localsettings.formatMoney(monthnettotal))
iterDate = iterDate.addMonths(1)
cursor.close()
# db.close()
retarg += '''<tr><td colspan="5"></td><td><b>GRAND TOTAL</b></td>
<td align="right"><b>%s</b></td>
<td align="right"><b>%s</b></td></tr></table></body></html>''' % (
localsettings.formatMoney(total), localsettings.formatMoney(nettotal))
return retarg
