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_validate_import(self, event):
try:
for payment in self.ok:
id_bill = payment[0]
reminder_cts = payment[2]
credit_date = payment[10]
print("payment:", payment)
self.cursor.execute(
"UPDATE bills SET payment_date = %s WHERE id = %s",
[credit_date, id_bill])
if reminder_cts > 0:
self.cursor.execute(
"SELECT id, amount_cts FROM reminders WHERE id_bill = %s ORDER BY reminder_date",
[id_bill])
for reminder_id, billed_reminder_cts in list(self.cursor):
if reminder_cts >= billed_reminder_cts:
self.cursor.execute(
"UPDATE reminders SET status = 'P' WHERE id = %s",
[reminder_id])
else:
break
reminder_cts -= billed_reminder_cts
self.Close()
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_update)
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_refresh(self, event):
"Populate liste based on the filter fields"
self.on_deselect_payment(None)
self.payments.DeleteAllItems()
try:
self.data = list(Bill.yield_all(self.cursor, "type = 'C' AND timestamp > CURDATE() AND (status = 'O' OR status = 'I' AND payment_method = 'BVR')", "timestamp"))
for bill in self.data:
self.payments.Append((bill.sex, bill.lastname, bill.firstname, bill.consultation.therapeute, bill.timestamp.strftime('%H:%M'), '%0.2f' % (bill.total_cts/100), bill.payment_method))
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_read)
def real_validate(self):
bill = self.data[self.selected_idx]
try:
if self.payment_method.StringSelection == 'BVR':
bill.status = STATUS_SENT
bill.save(self.cursor)
elif self.payment_method.StringSelection not in (u'Dû', u'PVPE'):
bill.status = STATUS_PAYED
bill.payment_date = datetime.date.today()
bill.save(self.cursor)
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_update)
def mark_status(self, status):
bill_ids = [id for i, id in enumerate(self.data) if self.payments.IsSelected(i)]
try:
for id_bill in bill_ids:
self.cursor.execute("""SELECT id FROM reminders WHERE id_bill = %s ORDER BY reminder_date DESC LIMIT 1""", [id_bill])
last_reminder_id, = self.cursor.fetchone() or (None,)
self.cursor.execute("""UPDATE bills SET status = %s WHERE status != 'P' AND id = %s""", [status, id_bill])
if last_reminder_id is not None:
self.cursor.execute("""UPDATE reminders SET status = %s WHERE status != 'P' AND id = %s""", [status, last_reminder_id])
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:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_update)
self.update_list()
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
try:
self.cursor.execute("SELECT therapeute FROM therapeutes ORDER BY therapeute")
therapeutes = ['Tous'] + [t for t, in self.cursor]
except:
traceback.print_exc()
showwarning(windows_title.db_error, errors_text.db_read)
sys.exit(1)
self.therapeute.SetItems(therapeutes)
self.therapeute.StringSelection = therapeutes[0]
self.payment_method.SetItems([''] + PAYMENT_METHODS + OLD_PAYMENT_METHODS)
self.payment_method.StringSelection = ''
self.bill_status.SetItems(BILL_STATUSES)
self.bill_status.StringSelection = BILL_STATUSES[0]
today = datetime.date.today()
month_end = datetime.date(today.year, today.month, 1) - datetime.timedelta(days=1)
last_month = datetime.date(month_end.year, month_end.month, 1)
self.filter_start.Value = str(last_month)
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
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 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.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:
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.)
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.)
