class EmployeeInfoGUI(Gtk.Builder): def __init__(self): Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.cursor = DB.cursor() self.employee_store = self.get_object('employee_store') self.s_s_medicare_store = self.get_object('s_s_medicare_store') self.federal_withholding_store = self.get_object( 'federal_withholding_store') self.state_withholding_store = self.get_object( 'state_withholding_store') self.populate_employee_store() self.born_calendar = DateTimeCalendar(override=True) self.on_payroll_since_calendar = DateTimeCalendar(override=True) self.born_calendar.connect("day-selected", self.born_calendar_date_selected) self.on_payroll_since_calendar.connect( "day-selected", self.on_payroll_since_calendar_date_selected) self.window = self.get_object('window1') self.window.show_all() broadcaster.connect("shutdown", self.main_shutdown) self.get_object("button5").set_label("No scanner selected") self.get_object("button5").set_sensitive(False) self.data_queue = Queue() self.scanner_store = self.get_object("scanner_store") thread = Process(target=self.get_scanners) thread.start() GLib.timeout_add(100, self.populate_scanners) def populate_scanners(self): try: devices = self.data_queue.get_nowait() for scanner in devices: device_id = scanner[0] device_manufacturer = scanner[1] name = scanner[2] given_name = scanner[3] self.scanner_store.append( [str(device_id), device_manufacturer, name, given_name]) except Empty: return True def get_scanners(self): sane.init() devices = sane.get_devices() self.data_queue.put(devices) def main_shutdown(self): # commit all changes before shutdown, # because committing changes releases the row lock DB.commit() def populate_employee_store(self): self.populating = True self.employee_store.clear() self.cursor.execute("SELECT id, name FROM contacts " "WHERE employee = True") for row in self.cursor.fetchall(): self.employee_store.append(row) self.populating = False DB.rollback() def employee_treeview_cursor_changed(self, treeview): if self.populating == True: return selection = self.get_object('treeview-selection1') model, path = selection.get_selected_rows() self.employee_id = model[path][0] self.select_employee() def select_employee(self): self.populating = True DB.commit() # save and unlock the active employee cursor = DB.cursor() try: cursor.execute( "SELECT " "born, " "social_security, " "social_security_exempt, " "on_payroll_since, " "wage, " "payments_per_year, " "married, " "format_date(last_updated), " "state_withholding_exempt, " "state_credits, " "state_extra_withholding, " "fed_withholding_exempt, " "fed_credits, " "fed_extra_withholding " "FROM payroll.employee_info " "WHERE employee_id = %s " "ORDER BY active DESC, id DESC " "LIMIT 1 FOR UPDATE NOWAIT", (self.employee_id, )) except psycopg2.OperationalError as e: DB.rollback() cursor.close() self.get_object('box1').set_sensitive(False) error = str( e) + "Hint: somebody else is editing this employee info" self.show_message(error) self.populating = False return False for row in cursor.fetchall(): self.born_calendar.set_date(row[0]) self.get_object('entry2').set_text(row[1]) self.get_object('checkbutton3').set_active(row[2]) self.on_payroll_since_calendar.set_date(row[3]) self.get_object('spinbutton6').set_value(row[4]) self.get_object('spinbutton5').set_value(row[5]) self.get_object('checkbutton4').set_active(row[6]) self.get_object('label6').set_text(row[7]) self.get_object('checkbutton2').set_active(row[8]) self.get_object('spinbutton3').set_value(row[9]) self.get_object('spinbutton2').set_value(row[10]) self.get_object('checkbutton1').set_active(row[11]) self.get_object('spinbutton4').set_value(row[12]) self.get_object('spinbutton1').set_value(row[13]) break else: cursor.execute( "INSERT INTO payroll.employee_info (employee_id) " "VALUES (%s)", (self.employee_id, )) DB.commit() GLib.timeout_add(50, self.select_employee) self.populating = False self.populate_exemption_forms() cursor.close() self.get_object('box1').set_sensitive(True) def populate_exemption_forms(self): self.s_s_medicare_store.clear() self.state_withholding_store.clear() self.federal_withholding_store.clear() self.cursor.execute( "SELECT id, format_date(date_inserted) " "FROM payroll.emp_pdf_archive " "WHERE employee_id = %s " "AND s_s_medicare_exemption_pdf IS NOT NULL " "ORDER BY id", (self.employee_id, )) for row in self.cursor.fetchall(): self.s_s_medicare_store.append(row) self.cursor.execute( "SELECT id, format_date(date_inserted) " "FROM payroll.emp_pdf_archive " "WHERE employee_id = %s " "AND state_withholding_pdf IS NOT NULL " "ORDER BY id", (self.employee_id, )) for row in self.cursor.fetchall(): self.state_withholding_store.append(row) self.cursor.execute( "SELECT id, format_date(date_inserted) " "FROM payroll.emp_pdf_archive " "WHERE employee_id = %s " "AND fed_withholding_pdf IS NOT NULL " "ORDER BY id", (self.employee_id, )) for row in self.cursor.fetchall(): self.federal_withholding_store.append(row) def s_s_m_row_activated(self, treeview, path, column): model = treeview.get_model() id = model[path][0] self.cursor.execute( "SELECT s_s_medicare_exemption_pdf " "FROM payroll.emp_pdf_archive " "WHERE id = %s", (id, )) for row in self.cursor.fetchall(): file_data = row[0] file_name = "/tmp/Social_security_medicare_exemption.pdf" f = open(file_name, 'wb') f.write(file_data) subprocess.Popen("xdg-open %s" % file_name, shell=True) f.close() def federal_withholding_row_activated(self, treeview, path, column): model = treeview.get_model() id = model[path][0] self.cursor.execute( "SELECT fed_withholding_pdf " "FROM payroll.emp_pdf_archive " "WHERE id = %s", (id, )) for row in self.cursor.fetchall(): file_data = row[0] file_name = "/tmp/Federal_withholding_exemption.pdf" f = open(file_name, 'wb') f.write(file_data) subprocess.Popen("xdg-open %s" % file_name, shell=True) f.close() def state_withholding_row_activated(self, treeview, path, column): model = treeview.get_model() id = model[path][0] self.cursor.execute( "SELECT state_withholding_pdf " "FROM payroll.emp_pdf_archive " "WHERE id = %s", (id, )) for row in self.cursor.fetchall(): file_data = row[0] file_name = "/tmp/State_withholding_exemption.pdf" f = open(file_name, 'wb') f.write(file_data) subprocess.Popen("xdg-open %s" % file_name, shell=True) f.close() def payments_per_year_value_changed(self, spinbutton): if self.populating == True: return self.auto_save_employee_info() def state_income_status_toggled(self, checkbutton): if self.populating == True: return self.auto_save_employee_info() def state_credits_value_changed(self, spinbutton): if self.populating == True: return self.auto_save_employee_info() def state_extra_withholding_value_changed(self, spinbutton): if self.populating == True: return self.auto_save_employee_info() def federal_income_status_toggled(self, checkbutton): if self.populating == True: return self.auto_save_employee_info() def federal_credits_value_changed(self, spinbutton): if self.populating == True: return self.auto_save_employee_info() def federal_extra_withholding_value_changed(self, spinbutton): if self.populating == True: return self.auto_save_employee_info() def married_checkbutton_toggled(self, checkbutton): if self.populating == True: return self.auto_save_employee_info() def social_security_entry_changed(self, entry): if self.populating == True: return self.auto_save_employee_info() def wage_spinbutton_value_changed(self, spinbutton): if self.populating == True: return self.auto_save_employee_info() def social_security_exemption_changed(self, checkbutton): if self.populating == True: return self.auto_save_employee_info() def scanner_combo_changed(self, combo): if combo.get_active() > -1: self.get_object("button5").set_label("Scan") self.get_object("button5").set_sensitive(True) def show_scan_pdf_dialog(self, column): global device dialog = self.get_object("dialog1") result = dialog.run() dialog.hide() if result != Gtk.ResponseType.ACCEPT: return if device == None: device_address = self.get_object("combobox1").get_active_id() device = sane.open(device_address) document = device.scan() path = "/tmp/posting_pdf.pdf" document.save(path) f = open(path, 'rb') file_data = f.read() binary = psycopg2.Binary(file_data) f.close() self.cursor.execute( "UPDATE payroll.emp_pdf_archive " "SET archived = True " "WHERE employee_id = %s " "AND " + column + " IS NOT NULL", (self.employee_id, )) self.cursor.execute( "INSERT INTO payroll.emp_pdf_archive " "( " + column + ", employee_id, date_inserted) " "VALUES (%s, %s, %s)", (binary, self.employee_id, datetime.today())) DB.commit() self.populate_exemption_forms() def state_button_release_event(self, button, event): if event.button == 1: self.cursor.execute( "SELECT state_withholding_pdf " "FROM payroll.emp_pdf_archive " "WHERE (employee_id, archived) = (%s, False) " "AND state_withholding_pdf IS NOT NULL", (self.employee_id, )) for row in self.cursor.fetchall(): file_data = row[0] file_name = "/tmp/State_withholding_status.pdf" f = open(file_name, 'wb') f.write(file_data) subprocess.Popen("xdg-open %s" % file_name, shell=True) f.close() break else: label = 'Do you want to add a file from the scanner?' self.get_object('label9').set_label(label) self.show_scan_pdf_dialog("state_withholding_pdf") elif event.button == 3: label = 'Do you want to update the file from the scanner?' self.get_object('label9').set_label(label) self.show_scan_pdf_dialog("state_withholding_pdf") def s_s_m_button_release_event(self, button, event): if event.button == 1: self.cursor.execute( "SELECT s_s_medicare_exemption_pdf " "FROM payroll.emp_pdf_archive " "WHERE (employee_id, archived) = (%s, False) " "AND s_s_medicare_exemption_pdf IS NOT NULL", (self.employee_id, )) for row in self.cursor.fetchall(): file_data = row[0] file_name = "/tmp/Social_security_and_medicare_exemption.pdf" f = open(file_name, 'wb') f.write(file_data) subprocess.Popen("xdg-open %s" % file_name, shell=True) f.close() break else: label = 'Do you want to add a file from the scanner?' self.get_object('label9').set_label(label) self.show_scan_pdf_dialog("s_s_medicare_exemption_pdf") elif event.button == 3: label = 'Do you want to update the file from the scanner?' self.get_object('label9').set_label(label) self.show_scan_pdf_dialog("s_s_medicare_exemption_pdf") def fed_button_release_event(self, button, event): if event.button == 1: self.cursor.execute( "SELECT fed_withholding_pdf " "FROM payroll.emp_pdf_archive " "WHERE (employee_id, archived) = (%s, False) " "AND fed_withholding_pdf IS NOT NULL", (self.employee_id, )) for row in self.cursor.fetchall(): file_data = row[0] file_name = "/tmp/Federal_withholding_exemption.pdf" f = open(file_name, 'wb') f.write(file_data) subprocess.Popen("xdg-open %s" % file_name, shell=True) f.close() break else: # table label = 'Do you want to add a file from the scanner?' self.get_object('label9').set_label(label) self.show_scan_pdf_dialog("fed_withholding_pdf") elif event.button == 3: label = 'Do you want to update the file from the scanner?' self.get_object('label9').set_label(label) self.show_scan_pdf_dialog("fed_withholding_pdf") def born_calendar_date_selected(self, calendar): date_text = calendar.get_text() self.get_object('entry1').set_text(date_text) if self.populating == True: return self.auto_save_employee_info() def born_entry_icon_released(self, entry, icon, event): self.born_calendar.set_relative_to(entry) self.born_calendar.show() def on_payroll_since_calendar_date_selected(self, calendar): date_text = calendar.get_text() self.get_object('entry3').set_text(date_text) if self.populating == True: return self.auto_save_employee_info() def on_payroll_since_entry_icon_released(self, entry, icon, event): self.on_payroll_since_calendar.set_relative_to(entry) self.on_payroll_since_calendar.show() def save_clicked(self, button): born = self.born_calendar.get_date() social_security = self.get_object('entry2').get_text() social_security_exempt = self.get_object('checkbutton3').get_active() on_payroll_since = self.on_payroll_since_calendar.get_date() wage = self.get_object('spinbutton6').get_value() payments_per_year = self.get_object('spinbutton5').get_value() married = self.get_object('checkbutton4').get_active() state_withholding_exempt = self.get_object('checkbutton2').get_active() state_credits = self.get_object('spinbutton3').get_value() state_extra_withholding = self.get_object('spinbutton2').get_value() fed_withholding_exempt = self.get_object('checkbutton1').get_active() fed_credits = self.get_object('spinbutton4').get_value() fed_extra_withholding = self.get_object('spinbutton1').get_value() c = DB.cursor() c.execute( "INSERT INTO payroll.employee_info " "(born, " "social_security, " "social_security_exempt, " "on_payroll_since, " "wage, " "payments_per_year, " "married, " "state_withholding_exempt, " "state_credits, " "state_extra_withholding, " "fed_withholding_exempt, " "fed_credits, " "fed_extra_withholding, " "employee_id) " "VALUES (%s, %s, %s, %s, %s, %s, %s, " "%s, %s, %s, %s, %s, %s, %s) " "ON CONFLICT (active, employee_id) " "WHERE active = True " "DO UPDATE SET " "(born, " "social_security, " "social_security_exempt, " "on_payroll_since, " "wage, " "payments_per_year, " "married, " "state_withholding_exempt, " "state_credits, " "state_extra_withholding, " "fed_withholding_exempt, " "fed_credits, " "fed_extra_withholding, " "last_updated) " "= " "(EXCLUDED.born, " "EXCLUDED.social_security, " "EXCLUDED.social_security_exempt, " "EXCLUDED.on_payroll_since, " "EXCLUDED.wage, " "EXCLUDED.payments_per_year, " "EXCLUDED.married, " "EXCLUDED.state_withholding_exempt, " "EXCLUDED.state_credits, " "EXCLUDED.state_extra_withholding, " "EXCLUDED.fed_withholding_exempt, " "EXCLUDED.fed_credits, " "EXCLUDED.fed_extra_withholding, " "now()) " "WHERE (employee_info.employee_id, employee_info.active) = " "(EXCLUDED.employee_id, True) ", (born, social_security, social_security_exempt, on_payroll_since, wage, payments_per_year, married, state_withholding_exempt, state_credits, state_extra_withholding, fed_withholding_exempt, fed_credits, fed_extra_withholding, self.employee_id)) c.close() self.get_object('treeview1').set_sensitive(True) def cancel_clicked(self, button): self.select_employee() self.get_object('treeview1').set_sensitive(True) def auto_save_employee_info(self): # if we ever decide to implement autosave again, the caveats are : # 1 locking the row # 2 upserts increment the id self.get_object('treeview1').set_sensitive(False) def show_message(self, message): dialog = Gtk.MessageDialog(message_type=Gtk.MessageType.ERROR, buttons=Gtk.ButtonsType.CLOSE) dialog.set_transient_for(self.window) dialog.set_markup(message) dialog.run() dialog.destroy() def window_delete_event(self, window, event): # save any uncommitted changes and unlock the selected row DB.commit()
class GUI: def __init__(self, customer_id = None): self.customer_id = customer_id self.payment_type_id = 0 self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.cursor = DB.cursor() self.cursor.execute ("SELECT enforce_exact_payment FROM settings") self.exact_payment = self.cursor.fetchone()[0] self.cursor.execute("SELECT accrual_based FROM settings") self.accrual = self.cursor.fetchone()[0] self.expense_trees = expense_tree customer_completion = self.builder.get_object('customer_completion') customer_completion.set_match_func(self.customer_match_func) self.invoice_store = self.builder.get_object ('unpaid_invoice_store') self.customer_store = self.builder.get_object ('customer_store') self.cash_account_store = self.builder.get_object ('cash_account_store') self.cursor.execute("SELECT number::text, name FROM gl_accounts " "WHERE (is_parent, cash_account) = " "(False, True)") for row in self.cursor.fetchall(): self.cash_account_store.append(row) self.populate_contacts () total_column = self.builder.get_object ('treeviewcolumn3') total_renderer = self.builder.get_object ('cellrenderertext5') total_column.set_cell_data_func(total_renderer, self.total_cell_func) amount_due_column = self.builder.get_object ('treeviewcolumn4') amount_due_renderer = self.builder.get_object ('cellrendererspin7') amount_due_column.set_cell_data_func(amount_due_renderer, self.amount_due_cell_func) self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today () self.date = self.calendar.get_date() self.builder.get_object ('combobox1').set_active_id(str(customer_id)) self.check_entry = self.builder.get_object('entry3') self.credit_entry = self.builder.get_object('entry4') self.cash_entry = self.builder.get_object('entry5') self.window = self.builder.get_object('window1') self.window.show_all() self.check_amount_totals_validity () def destroy(self, window): self.cursor.close() def spinbutton_focus_in_event (self, spinbutton, event): GLib.idle_add(spinbutton.select_region, 0, -1) def help_button_clicked (self, button): subprocess.Popen (["yelp", help_dir + "/customer_payment.page"]) def total_cell_func(self, column, cellrenderer, model, iter1, data): amount = model.get_value(iter1, 4) cellrenderer.set_property("text" , str(amount)) def amount_due_cell_func(self, column, cellrenderer, model, iter1, data): amount = model.get_value(iter1, 5) cellrenderer.set_property("text" , str(amount)) def populate_contacts (self): self.cursor.execute("SELECT id::text, name, ext_name FROM contacts " "WHERE customer = True ORDER BY name") for row in self.cursor.fetchall(): self.customer_store.append(row) DB.rollback() def view_invoice_clicked (self, widget): invoice_combo = self.builder.get_object('comboboxtext1') invoice_id = invoice_combo.get_active_id() self.cursor.execute("SELECT * FROM invoices WHERE id = %s", (invoice_id,)) for cell in self.cursor.fetchall(): file_name = cell[1] + ".pdf" file_data = cell[14] f = open("/tmp/" + file_name,'wb') f.write(file_data) subprocess.call("xdg-open /tmp/" + str(file_name), shell = True) f.close() DB.rollback() def apply_payment_method_toggled (self, togglebutton): active = togglebutton.get_active() self.builder.get_object('amount_spinbutton').set_sensitive(active) def calculate_discount (self, discount, total): discount_percent = (float(discount) / 100.00) discount_amount = total * discount_percent discounted_amount = total - discount_amount discounted_amount = round(discounted_amount, 2) return discounted_amount def calculate_invoice_discount (self, invoice_id): self.cursor.execute("SELECT cash_only, discount_percent, pay_in_days, " "pay_by_day_of_month, pay_in_days_active, " "pay_by_day_of_month_active FROM contacts " "JOIN terms_and_discounts " "ON contacts.terms_and_discounts_id = " "terms_and_discounts.id WHERE contacts.id = %s", (self.customer_id,)) for row in self.cursor.fetchall(): cash_only = row[0] discount = row[1] pay_in_days = row[2] pay_by_day_of_month = row[3] pay_in_days_active = row[4] pay_by_day_of_month_active = row[5] self.cursor.execute("SELECT tax, total, date_created FROM invoices " "WHERE id = %s", (invoice_id,)) if cash_only == True: for row in self.cursor.fetchall(): tax = row[0] self.builder.get_object('label4').set_label("Not applicable") self.builder.get_object('label9').set_label("Not applicable") elif pay_in_days_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] date_difference = self.date - date_created discount_due_date = date_created + timedelta(pay_in_days) due_date_text = date_to_text (discount_due_date) self.builder.get_object('label9').set_label(due_date_text) discounted_amount = self.calculate_discount (discount, total) self.builder.get_object('label4').set_label(str(discounted_amount)) elif pay_by_day_of_month_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] discount_date = date_created.replace(day=pay_by_day_of_month) due_date_text = date_to_text (discount_date) self.builder.get_object('label9').set_label(due_date_text) discounted_amount = self.calculate_discount (discount, total) self.builder.get_object('label4').set_label(str(discounted_amount)) else: raise Exception("the terms_and_discounts table has invalid entries") DB.rollback() def customer_match_func(self, completion, key, iter): split_search_text = key.split() for text in split_search_text: if text not in self.customer_store[iter][1].lower(): return False# no match return True# it's a hit! def customer_combo_changed(self, widget): self.check_amount_totals_validity () customer_id = widget.get_active_id() if customer_id != None: self.customer_id = customer_id self.select_customer() def customer_match_selected(self, completion, model, iter): self.customer_id = model[iter][0] self.select_customer () def select_customer (self): self.cursor.execute("SELECT address, city, state, phone From contacts " "WHERE id = %s", (self.customer_id,)) for row in self.cursor.fetchall(): self.builder.get_object('label11').set_label(row[0]) self.builder.get_object('label18').set_label(row[1]) self.builder.get_object('label17').set_label(row[2]) self.builder.get_object('label12').set_label(row[3]) self.populate_invoices() DB.rollback() def populate_invoices (self): self.update_invoice_amounts_due () self.invoice_store.clear() self.cursor.execute("SELECT " "i.id, " "i.name, " "date_created::text, " "format_date(date_created), " "total, " "amount_due " "FROM invoices AS i " "JOIN contacts AS c ON i.customer_id = c.id " "WHERE (canceled, paid, posted) = " "(False, False, True) " "AND customer_id = %s ORDER BY i.date_created", (self.customer_id,)) for row in self.cursor.fetchall(): self.invoice_store.append(row) self.builder.get_object('amount_spinbutton').set_value(0) def invoice_selection_changed (self, selection): total = Decimal() model, path = selection.get_selected_rows () for row in path: total += model[row][5] if len(path) == 1: invoice_id = model[path][0] amount_due = model[path][5] self.builder.get_object('amount_spinbutton').set_value(amount_due) self.calculate_invoice_discount (invoice_id) else: self.builder.get_object('label9').set_label('Select a single invoice') self.builder.get_object('label4').set_label('Select a single invoice') self.builder.get_object('amount_spinbutton').set_value(total) self.builder.get_object('label22').set_label('{:,.2f}'.format(total, 2)) def invoice_treeview_button_release_event (self, treeview, event): if event.button == 3: menu = self.builder.get_object('menu1') menu.popup_at_pointer() def amount_due_edited (self, renderer, path, amount): invoice_id = self.invoice_store[path][0] if amount == '' or Decimal(amount) > self.invoice_store[path][4]: amount = self.invoice_store[path][4] self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (amount, invoice_id)) DB.commit() self.builder.get_object('amount_spinbutton').set_value(float(amount)) self.invoice_store[path][5] = Decimal(amount).quantize(Decimal('.01')) def amount_due_editing_started (self, renderer, spinbutton, path): upper_limit = self.invoice_store[path][4] spinbutton.set_numeric(True) self.builder.get_object('amount_due_adjustment').set_upper(upper_limit) spinbutton.set_value(self.invoice_store[path][5]) def apply_discount_activated (self, menuitem): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return amount = model[path][5] self.builder.get_object('spinbutton3').set_value(amount) dialog = self.builder.get_object('invoice_discount_dialog') result = dialog.run() dialog.hide() invoice_id = model[path][0] discounted_amount = self.builder.get_object('spinbutton3').get_value() if result == Gtk.ResponseType.ACCEPT: self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (discounted_amount, invoice_id)) DB.commit() self.builder.get_object('amount_spinbutton').set_value(discounted_amount) model[path][5] = Decimal(discounted_amount).quantize(Decimal('.01')) #self.populate_invoices () def check_btn_toggled(self, widget): self.check_entry.set_sensitive(True) self.credit_entry.set_sensitive(False) self.cash_entry.set_sensitive(False) self.payment_type_id = 0 self.check_amount_totals_validity() def credit_btn_toggled(self, widget): self.check_entry.set_sensitive(False) self.credit_entry.set_sensitive(True) self.cash_entry.set_sensitive(False) self.payment_type_id = 1 def cash_btn_toggled(self, widget): self.check_entry.set_sensitive(False) self.credit_entry.set_sensitive(False) self.cash_entry.set_sensitive(True) self.payment_type_id = 2 def post_payment_clicked (self, widget): comments = self.builder.get_object('entry2').get_text() total = self.builder.get_object('amount_spinbutton').get_text() total = Decimal(total) self.payment = transactor.CustomerInvoicePayment(self.date, total) if self.payment_type_id == 0: payment_text = self.check_entry.get_text() self.cursor.execute("INSERT INTO payments_incoming " "(check_payment, cash_payment, " "credit_card_payment, payment_text , " "check_deposited, customer_id, amount, " "date_inserted, comments) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (True, False, False, payment_text, False, self.customer_id, total, self.date, comments)) self.payment_id = self.cursor.fetchone()[0] self.payment.bank_check (self.payment_id) elif self.payment_type_id == 1: payment_text = self.credit_entry.get_text() self.cursor.execute("INSERT INTO payments_incoming " "(check_payment, cash_payment, " "credit_card_payment, payment_text , " "check_deposited, customer_id, amount, " "date_inserted, comments) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (False, False, True, payment_text, False, self.customer_id, total, self.date, comments)) self.payment_id = self.cursor.fetchone()[0] self.payment.credit_card (self.payment_id) elif self.payment_type_id == 2: payment_text = self.cash_entry.get_text() self.cursor.execute("INSERT INTO payments_incoming " "(check_payment, cash_payment, " "credit_card_payment, payment_text , " "check_deposited, customer_id, amount, " "date_inserted, comments) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (False, True, False, payment_text, False, self.customer_id, total, self.date, comments)) self.payment_id = self.cursor.fetchone()[0] self.payment.cash (self.payment_id) if self.builder.get_object('fifo_payment_checkbutton').get_active(): self.pay_invoices_fifo() else: self.pay_selected_invoices() DB.commit() self.cursor.close() self.window.destroy () def pay_invoices_fifo (self): c = DB.cursor() c_id = self.customer_id c.execute("(SELECT id, total - amount_due AS discount FROM " "(SELECT id, total, amount_due, SUM(amount_due) " "OVER (ORDER BY date_created, id) invoice_totals " "FROM invoices WHERE (paid, posted, canceled, customer_id) " "= (False, True, False, %s)" ") i " "WHERE invoice_totals <= " "(SELECT payment_total - invoice_total FROM " "(SELECT COALESCE(SUM(amount_due), 0.0) " "AS invoice_total FROM invoices " "WHERE (paid, canceled, customer_id) = " "(True, False, %s)" ") it, " "(SELECT amount + amount_owed AS payment_total FROM " "(SELECT COALESCE(SUM(amount), 0.0) " "AS amount " "FROM payments_incoming " "WHERE (customer_id, misc_income) = " "(%s, False)" ") pi, " "(SELECT COALESCE(SUM(amount_owed), 0.0) " "AS amount_owed " "FROM credit_memos " "WHERE (customer_id, posted) = " "(%s, True)" ") cm " ") pt " ")" "ORDER BY id);", (c_id, c_id, c_id, c_id )) for row in c.fetchall(): invoice_id = row[0] discount = row[1] if discount != Decimal('0.00'): self.payment.customer_discount (discount) if self.accrual == False: transactor.post_invoice_accounts (self.date, invoice_id) c.execute("UPDATE invoices " "SET (paid, payments_incoming_id, date_paid) " "= (True, %s, %s) " "WHERE id = %s", (self.payment_id, self.date, invoice_id)) c.close() def pay_selected_invoices (self): c = DB.cursor() selection = self.builder.get_object('treeview-selection1') model, paths = selection.get_selected_rows() discount = Decimal('0.00') for row in paths: invoice_id = model[row][0] if self.accrual == False: transactor.post_invoice_accounts (self.date, invoice_id) c.execute("UPDATE invoices " "SET (paid, payments_incoming_id, date_paid) = " "(True, %s, %s) WHERE id = %s " "RETURNING total - amount_due AS discount", (self.payment_id, self.date, invoice_id)) discount += c.fetchone()[0] if discount != Decimal('0.00'): self.payment.customer_discount (discount) c.close() def calendar_day_selected (self, calendar): self.date = calendar.get_date() day_text = calendar.get_text() self.builder.get_object('entry1').set_text(day_text) self.populate_invoices () def calendar_entry_icon_released (self, widget, icon, event): self.calendar.set_relative_to(widget) self.calendar.show() def account_combo_changed (self, combo): self.check_amount_totals_validity () def payment_amount_value_changed (self, spinbutton): self.check_amount_totals_validity () def update_invoice_amounts_due (self): if self.customer_id == None : return self.cursor.execute("SELECT cash_only, discount_percent, pay_in_days, " "pay_by_day_of_month, pay_in_days_active, " "pay_by_day_of_month_active FROM contacts " "JOIN terms_and_discounts " "ON contacts.terms_and_discounts_id = " "terms_and_discounts.id WHERE contacts.id = %s", (self.customer_id,)) for row in self.cursor.fetchall(): cash_only = row[0] discount = row[1] pay_in_days = row[2] pay_by_day_of_month = row[3] pay_in_days_active = row[4] pay_by_day_of_month_active = row[5] self.cursor.execute("SELECT id, total, date_created FROM invoices " "WHERE (customer_id, paid, posted) " "= (%s, False, True)", (self.customer_id,)) if cash_only == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = row[1] self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (total, invoice_id)) elif pay_in_days_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] date_difference = self.date - date_created if date_difference <= timedelta(pay_in_days): discounted_amount = self.calculate_discount(discount, total) self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (discounted_amount, invoice_id)) else: self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (total, invoice_id)) elif pay_by_day_of_month_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] discount_date = date_created.replace(day=pay_by_day_of_month) if self.date <= discount_date: discounted_amount = self.calculate_discount(discount, total) self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (discounted_amount, invoice_id)) else: self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (total, invoice_id)) else: raise Exception("your terms_and_discounts table has invalid entries") DB.commit() def discount_cash_back_amount_changed (self, spinbutton): self.check_amount_totals_validity () amount = spinbutton.get_value() combobox = self.builder.get_object('combobox2') if amount == 0.00: return elif amount < 0.00: combobox.set_model(self.expense_trees) elif amount > 0.00: combobox.set_model(self.cash_account_store) def check_number_changed (self, entry): self.check_amount_totals_validity () def check_amount_totals_validity (self): button = self.builder.get_object('button1') button.set_sensitive (False) if self.date == None: button.set_label("No date selected") return if self.customer_id == None: button.set_label("No contact selected") return check_text = self.builder.get_object('entry3').get_text() check_active = self.builder.get_object('check_radiobutton').get_active() if check_active == True and check_text == '': button.set_label('No check number') return # no check number if self.exact_payment: self.check_amount_totals_absolute () else: self.check_amount_totals_flexible () def check_amount_totals_flexible (self): button = self.builder.get_object('button1') button.set_sensitive (False) label = self.builder.get_object('label20') label.set_visible (True) payment = self.builder.get_object('amount_spinbutton').get_value() selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() invoice_amount_due_totals = Decimal() for row in path: invoice_amount_due_totals += model[row][5] self.builder.get_object('label23').set_label ('{:,.2f}'.format(payment)) if float(invoice_amount_due_totals) == payment : label.set_visible (False) #hide the off balance alert button.set_sensitive (True) button.set_label('Post payment') def check_amount_totals_absolute (self): button = self.builder.get_object('button1') button.set_sensitive (False) payment = self.builder.get_object('amount_spinbutton').get_value() selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if len(path) == 0: button.set_label ('No invoices selected') return invoice_amount_due_totals = Decimal() for row in path: invoice_amount_due_totals += model[row][5] self.builder.get_object('label23').set_label ('{:,.2f}'.format(payment)) if float(invoice_amount_due_totals) != payment : button.set_label ("Totals do not match") return button.set_label ('Post payment') button.set_sensitive (True)