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