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