def update_db_content(self):
"""Populate the payment table ensuring all "visible" data"""
today = QDate.currentDate()
first_month_day = QDate(today.year(), today.month(), 1)
datebefore = first_month_day.addMonths(-self.months_before)
dateafter = first_month_day.addMonths(self.months_after)
query = QSqlQuery(
'SELECT client, machine, selldate, deltamonth, '
'anticiped FROM clients', self._db)
if not query.exec_():
raise StandardError('SYNTAX ERROR')
while query.next():
client = Client(query)
payments_date = client.selldate.addMonths(
0 if client.anticiped else client.deltamonth)
while payments_date < datebefore: # ignora date non visibili
payments_date = payments_date.addMonths(client.deltamonth)
while payments_date < dateafter:
query2 = QSqlQuery(
'SELECT effective_datepayd FROM payments '
'WHERE clients_client = :client AND clients_machine = '
':machine AND clients_selldate = :selldate AND '
'expected_datepayd = :expected_datepayd', self._db)
query2.bindValue(':client', QVariant(client.client))
query2.bindValue(':machine', QVariant(client.machine))
query2.bindValue(':selldate', QVariant(client.selldate))
query2.bindValue(':expected_datepayd', QVariant(payments_date))
if not query2.exec_():
raise StandardError('SYNTAX ERROR')
if not query2.first():
query3 = QSqlQuery(
'INSERT INTO payments (clients_client, '
'clients_machine, clients_selldate, '
'expected_datepayd) VALUES (:client, :machine, '
':selldate, :expected_datepayd)', self._db)
query3.bindValue(':client', QVariant(client.client))
query3.bindValue(':machine', QVariant(client.machine))
query3.bindValue(':selldate', QVariant(client.selldate))
query3.bindValue(':expected_datepayd',
QVariant(payments_date))
if not query3.exec_():
raise StandardError('SYNTAX ERROR')
payments_date = payments_date.addMonths(client.deltamonth)
self.emit(SIGNAL("layoutChanged()"))
def data(self, index, role=None):
"""return a QVariant saying if exists a payment at index.(row|column)"""
if not index.isValid() or role not in (Qt.DisplayRole,
Qt.CheckStateRole):
return QVariant()
#self.update_db_content()
# find the month from the row number
month_year = QDate().fromString(
self.headerData(index.row(), Qt.Vertical, role).toString(),
'MMMM yyyy')
month = month_year.month()
year = month_year.year()
# find the client from the column number
header_infos = self.headerData(index.column(), Qt.Horizontal,
role).toString().split('\n')
client = header_infos[0]
machine = header_infos[1]
selldate = QDate.fromString(header_infos[2], 'd MMMM yyyy')
deltamonth = int(header_infos[3][5:-5]) # [len('Ogni '):-len(' mesi')]
anticiped = header_infos[4][10:-6] == 'anti' # 'Pagamento ':-'cipato'
query = QSqlQuery(
'SELECT expected_datepayd, effective_datepayd FROM '
'payments WHERE clients_client = :client AND clients_machine = '
':machine AND clients_selldate = :selldate AND '
'expected_datepayd BETWEEN :datebefore AND :dateafter', self._db)
query.bindValue(':client', QVariant(client))
query.bindValue(':machine', QVariant(machine))
query.bindValue(':selldate', QVariant(selldate))
# primo giorno del mese
d = QDate(year, month, 1)
query.bindValue(':datebefore', QVariant(d))
# ultimo giorno del mese
query.bindValue(':dateafter', QVariant(d.addMonths(1).addDays(-1)))
if not query.exec_():
raise StandardError('SYNTAX ERROR')
if not query.first():
return QVariant()
expected_datepayd = query.value(0).toDate()
payed = not query.isNull(1)
effective_datepayd = query.value(1).toDate()
if role == Qt.CheckStateRole:
return QVariant(Qt.Checked if payed else Qt.Unchecked)
else: # DisplayRole
date = effective_datepayd if payed else expected_datepayd
return QVariant(date.toString('d MMMM yyyy'))
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
