def on_update_list(self, *args):
from dateutil import parse_ISO
upto = parse_ISO(self.upto.Value.strip())
self.reminders.DeleteAllItems()
self.total.Value = ''
self.data = []
self.is_updating_list = True
try:
where = dict(payment_date__isnull=True, bv_ref__isnull=False, bv_ref__ne='', timestamp__le=upto, status__notin=('P', 'A'))
for bill in Bill.yield_all(self.cursor, where=where, order='timestamp'):
if bill.reminders:
reminders_last = sorted(bill.reminders, key=attrgetter('reminder_date'))[-1].reminder_date
if reminders_last > upto:
continue
else:
reminders_last = ''
index = self.reminders.Append((bill.sex, bill.lastname, bill.firstname, bill.timestamp.date(), '%0.2f' % ((bill.total_cts)/100.), reminders_last, len(bill.reminders)))
self.reminders.Select(index)
if len(bill.reminders) == 1:
self.reminders.SetItemTextColour(index, wx.Colour(64, 0, 0))
self.reminders.SetItemFont(index, self.GetFont().Italic())
elif len(bill.reminders) > 1:
self.reminders.SetItemTextColour(index, wx.Colour(128, 0, 0))
self.reminders.SetItemFont(index, self.GetFont().Bold())
self.data.append((bill.id, bill.total_cts, bill.sex, bill.lastname, bill.firstname, bill.timestamp, len(bill.reminders), sum(p.total_cts for p in bill.positions), sum(r.amount_cts for r in bill.reminders)))
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_read)
self.is_updating_list = False
for c in range(self.reminders.ColumnCount):
self.reminders.SetColumnWidth(c, wx.LIST_AUTOSIZE)
self.reminders.SetColumnWidth(c, self.reminders.GetColumnWidth(c) + 5) # Workaround for font style not being taken into account
self.on_update_selection()
def on_update_list(self, *args):
from dateutil import parse_ISO
upto = parse_ISO(self.upto.Value.strip())
self.reminders.DeleteAllItems()
self.total.Value = ''
self.data = []
self.is_updating_list = True
try:
self.cursor.execute(
"""SELECT bills.id,
bills.type,
bills.status,
bills.timestamp,
bills.payment_date,
bills.sex,
bills.lastname,
bills.firstname,
CAST(COALESCE((SELECT SUM(quantity * price_cts) FROM positions WHERE id_bill = bills.id), 0) AS SIGNED),
CAST(COALESCE((SELECT SUM(amount_cts) FROM reminders WHERE id_bill = bills.id), 0) AS SIGNED),
(SELECT count(*) FROM reminders WHERE id_bill = bills.id),
(SELECT max(reminders.reminder_date) FROM reminders WHERE id_bill = bills.id)
FROM bills
WHERE bills.payment_date IS NULL
AND bills.bv_ref IS NOT NULL AND bills.bv_ref != ''
AND bills.timestamp <= %s
AND bills.status NOT IN ('P', 'A')
ORDER BY bills.timestamp""", [upto])
for id_bill, type, status, timestamp, payment_date, sex, lastname, firstname, bill_cts, reminders_cts, reminders_cnt, reminders_last in self.cursor:
if reminders_last is None:
reminders_last = ''
elif reminders_last > upto:
continue
index = self.reminders.Append(
(sex, lastname, firstname, timestamp.date(),
'%0.2f' % ((bill_cts + reminders_cts) / 100.),
reminders_last, reminders_cnt))
self.reminders.Select(index)
if reminders_cnt == 1:
self.reminders.SetItemTextColour(index,
wx.Colour(64, 0, 0))
self.reminders.SetItemFont(index, self.GetFont().Italic())
elif reminders_cnt > 1:
self.reminders.SetItemTextColour(index,
wx.Colour(128, 0, 0))
self.reminders.SetItemFont(index, self.GetFont().Bold())
self.data.append((id_bill, bill_cts + reminders_cts, sex,
lastname, firstname, timestamp,
reminders_cnt, bill_cts, reminders_cts))
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_read)
self.is_updating_list = False
for c in range(self.reminders.ColumnCount):
self.reminders.SetColumnWidth(c, wx.LIST_AUTOSIZE)
self.reminders.SetColumnWidth(
c,
self.reminders.GetColumnWidth(c) +
5) # Workaround for font style not being taken into account
self.on_update_selection()
def on_mark_paid(self, event):
from dateutil import parse_ISO
payment_date = parse_ISO(self.payment_date.Value.strip())
bill_ids = [id for i, id in enumerate(self.data) if self.payments.IsSelected(i)]
try:
if len(bill_ids) > 1:
self.cursor.execute("""UPDATE bills SET payment_date = %s, status = 'P'
WHERE payment_date IS NULL AND id IN %s""",
[payment_date, tuple(bill_ids)])
elif len(bill_ids) == 1:
self.cursor.execute("""UPDATE bills SET payment_date = %s, status = 'P' WHERE id = %s""", [payment_date, bill_ids[0]])
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_update)
self.update_list()
def on_mark_paid(self, event):
from dateutil import parse_ISO
payment_date = parse_ISO(self.payment_date.Value.strip())
bill_ids = [
id for i, id in enumerate(self.data) if self.payments.IsSelected(i)
]
try:
if len(bill_ids) > 1:
self.cursor.execute(
"""UPDATE bills SET payment_date = %s, status = 'P'
WHERE payment_date IS NULL AND id IN %s""",
[payment_date, tuple(bill_ids)])
elif len(bill_ids) == 1:
self.cursor.execute(
"""UPDATE bills SET payment_date = %s, status = 'P' WHERE id = %s""",
[payment_date, bill_ids[0]])
except:
show_db_warning(logger, 'update')
self.update_list()
def update_list(self):
from dateutil import parse_ISO
therapeute = self.therapeute.StringSelection
payment_method = self.payment_method.StringSelection
filter_start = parse_ISO(self.filter_start.Value.strip())
filter_end = parse_ISO(self.filter_end.Value.strip())
bill_status = self.bill_status.StringSelection
site = self.site.StringSelection
filter_firstname = self.filter_firstname.Value.strip()
filter_lastname = self.filter_lastname.Value.strip()
where = {}
if therapeute != 'Tous':
where['author_id'] = therapeute
if payment_method != '':
where['payment_method'] = payment_method
if filter_start:
where['timestamp__ge'] = filter_start
if filter_end:
where['timestamp__lt'] = filter_end + datetime.timedelta(days=1)
if bill_status != 'Tous':
bill_status = bill_status[0]
if bill_status == 'O':
where['status__in'] = ('O', 'I', 'E')
else:
where['status'] = bill_status
if site != 'Tous':
where['site'] = site
if filter_firstname:
where['firstname__like'] = filter_firstname.replace('*', '%')
if filter_lastname:
where['lastname__like'] = filter_lastname.replace('*', '%')
self.payments.DeleteAllItems()
self.payments_count.Value = ''
self.total_bills.Value = ''
self.total_reminder_costs.Value = ''
self.total.Value = ''
self.data = []
count = 0
total_bills = 0
total_reminder_costs = 0
try:
for bill in Bill.yield_all(self.cursor,
where=where,
order='timestamp'):
if bill.status not in (STATUS_ABANDONED,
STATUS_PAYED) and bill.reminders:
status = sorted(bill.reminders,
key=attrgetter('reminder_date'))[-1].status
else:
status = bill.status
index = self.payments.Append(
(status, bill.sex, bill.lastname, bill.firstname,
bill.site, bill.timestamp.date(),
'%6.2f' % (bill.total_cts / 100), bill.payment_date
or ''))
if len(bill.reminders) == 1:
self.payments.GetItem(index).SetTextColour(
wx.Colour(64, 0, 0))
elif len(bill.reminders) > 1:
self.payments.GetItem(index).SetTextColour(
wx.Colour(128, 0, 0))
self.data.append(bill.id)
total_bills += sum(p.total_cts for p in bill.positions)
total_reminder_costs += sum(r.amount_cts
for r in bill.reminders)
count += 1
except:
show_db_warning(logger, 'read')
for c in range(self.payments.ColumnCount):
self.payments.SetColumnWidth(
c, wx.LIST_AUTOSIZE_USEHEADER if c == 1 else wx.LIST_AUTOSIZE)
self.payments_count.Value = str(count)
self.total_bills.Value = '%0.2f CHF' % (total_bills / 100.)
self.total_reminder_costs.Value = '%0.2f CHF' % (total_reminder_costs /
100.)
self.total.Value = '%0.2f CHF' % (
(total_bills + total_reminder_costs) / 100.)
def update_list(self):
from dateutil import parse_ISO
therapeute = self.therapeute.StringSelection
payment_method = self.payment_method.StringSelection
filter_start = parse_ISO(self.filter_start.Value.strip())
filter_end = parse_ISO(self.filter_end.Value.strip())
bill_status = self.bill_status.StringSelection
filter_firstname = self.filter_firstname.Value.strip()
filter_lastname = self.filter_lastname.Value.strip()
conditions = ['TRUE']
args = []
if therapeute != 'Tous':
conditions.append('bills.author_id = %s')
args.append(therapeute)
if payment_method != '':
conditions.append('bills.payment_method = %s')
args.append(payment_method)
if filter_start:
conditions.append('bills.timestamp >= %s')
args.append(filter_start)
if filter_end:
conditions.append('bills.timestamp < %s')
args.append(filter_end + datetime.timedelta(days=1))
if bill_status != 'Tous':
bill_status = bill_status[0]
if bill_status == 'O':
conditions.append("bills.status in ('O', 'I', 'E')")
else:
conditions.append('bills.status = %s')
args.append(bill_status)
if filter_firstname:
conditions.append('bills.firstname LIKE %s')
args.append(filter_firstname.replace('*', '%'))
if filter_lastname:
conditions.append('bills.lastname LIKE %s')
args.append(filter_lastname.replace('*', '%'))
self.payments.DeleteAllItems()
self.payments_count.Value = ''
self.total_bills.Value = ''
self.total_reminder_costs.Value = ''
self.total.Value = ''
self.data = []
count = 0
total_bills = 0
total_reminder_costs = 0
try:
self.cursor.execute(
"""SELECT bills.id,
bills.type,
bills.status,
bills.timestamp,
bills.payment_date,
bills.sex,
bills.lastname,
bills.firstname,
CAST(COALESCE((SELECT SUM(quantity * price_cts) FROM positions WHERE id_bill = bills.id), 0) AS SIGNED),
CAST(COALESCE((SELECT SUM(amount_cts) FROM reminders WHERE id_bill = bills.id), 0) AS SIGNED),
(SELECT count(*) FROM reminders WHERE id_bill = bills.id)
FROM bills
WHERE %s
ORDER BY bills.timestamp""" %
' AND '.join(conditions), args)
data = list(self.cursor)
aux_cursor = self.connection.cursor()
for id_bill, type_, status, timestamp, payment_date, sex, lastname, firstname, amount_cts, reminders_cts, reminders_cnt in data:
if status not in (STATUS_ABANDONED,
STATUS_PAYED) and reminders_cnt != 0:
aux_cursor.execute(
"""SELECT status FROM reminders WHERE id_bill = %s ORDER BY reminder_date DESC LIMIT 1""",
[id_bill])
status, = aux_cursor.fetchone()
index = self.payments.Append(
(status, sex, lastname, firstname, timestamp.date(),
'%6.2f' % ((amount_cts + reminders_cts) / 100.),
payment_date or ''))
if reminders_cnt == 1:
self.payments.GetItem(index).SetTextColour(
wx.Colour(64, 0, 0))
elif reminders_cnt > 1:
self.payments.GetItem(index).SetTextColour(
wx.Colour(128, 0, 0))
self.data.append(id_bill)
total_bills += amount_cts
total_reminder_costs += reminders_cts
count += 1
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_read)
for c in range(self.payments.ColumnCount):
self.payments.SetColumnWidth(
c, wx.LIST_AUTOSIZE_USEHEADER if c == 1 else wx.LIST_AUTOSIZE)
self.payments_count.Value = str(count)
self.total_bills.Value = '%0.2f CHF' % (total_bills / 100.)
self.total_reminder_costs.Value = '%0.2f CHF' % (total_reminder_costs /
100.)
self.total.Value = '%0.2f CHF' % (
(total_bills + total_reminder_costs) / 100.)