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 CreditMemoGUI:
credit_memo_template = None
def __init__(self):
self.builder = Gtk.Builder()
self.builder.add_from_file(UI_FILE)
self.builder.connect_signals(self)
self.cursor = DB.cursor()
self.customer_store = self.builder.get_object('customer_store')
self.product_store = self.builder.get_object('credit_products_store')
self.credit_items_store = self.builder.get_object('credit_items_store')
self.handler_ids = list()
for connection in (("contacts_changed",
self.populate_customer_store), ):
handler = broadcaster.connect(connection[0], connection[1])
self.handler_ids.append(handler)
self.populate_customer_store()
self.date_returned_calendar = DateTimeCalendar()
self.date_returned_calendar.connect('day-selected',
self.return_day_selected)
date_column = self.builder.get_object('label3')
self.date_returned_calendar.set_relative_to(date_column)
self.date_calendar = DateTimeCalendar()
self.date_calendar.connect('day-selected', self.day_selected)
product_completion = self.builder.get_object('product_completion')
product_completion.set_match_func(self.product_match_func)
customer_completion = self.builder.get_object('customer_completion')
customer_completion.set_match_func(self.customer_match_func)
self.window = self.builder.get_object('window1')
self.window.show_all()
def window_destroy(self, window):
for handler in self.handler_ids:
broadcaster.disconnect(handler)
self.cursor.close()
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
return True
def product_match_selected(self, completion, model, _iter_):
invoice_item_id = model[_iter_][0]
selection = self.builder.get_object('treeview-selection1')
model, path = selection.get_selected_rows()
if path == []:
return
self.update_product_row(path, invoice_item_id)
def product_renderer_changed(self, combo, path, tree_iter):
invoice_item_id = self.product_store[tree_iter][0]
self.update_product_row(path, invoice_item_id)
def update_product_row(self, path, invoice_item_id):
c = DB.cursor()
iter_ = self.credit_items_store.get_iter(path)
self.check_row_id(iter_)
row_id = self.credit_items_store[iter_][0]
c.execute(
"WITH tax_cte AS "
"(SELECT tr.rate / 100 AS rate, price "
"FROM invoice_items AS ii "
"JOIN tax_rates AS tr ON tr.id = ii.tax_rate_id "
"WHERE ii.id = %s"
") "
"UPDATE credit_memo_items AS cmi "
"SET (invoice_item_id, "
"price, "
"ext_price, "
"tax"
") "
"= "
"(%s, "
"(SELECT price FROM tax_cte), "
"qty * (SELECT price FROM tax_cte), "
"qty * (SELECT price FROM tax_cte) * (SELECT rate FROM tax_cte)"
") "
"WHERE id = %s; " #new sql; this updates values
"SELECT "
"p.id, "
"p.name, "
"p.ext_name, "
"ii.price::text, "
"cmi.ext_price::text, "
"cmi.tax::text, "
"cmi.invoice_item_id, "
"ii.invoice_id "
"FROM credit_memo_items AS cmi "
"JOIN invoice_items AS ii ON ii.id = cmi.invoice_item_id "
"JOIN products AS p ON p.id = ii.product_id "
"WHERE cmi.id = %s",
(invoice_item_id, invoice_item_id, row_id, row_id))
for row in c.fetchall():
self.credit_items_store[iter_][2] = row[0]
self.credit_items_store[iter_][3] = row[1]
self.credit_items_store[iter_][4] = row[2]
self.credit_items_store[iter_][5] = row[3]
self.credit_items_store[iter_][6] = row[4]
self.credit_items_store[iter_][7] = row[5]
self.credit_items_store[iter_][8] = row[6]
self.credit_items_store[iter_][9] = row[7]
self.calculate_totals()
def product_editing_started(self, renderer, combo, path):
renderer_invoice = Gtk.CellRendererText()
combo.pack_start(renderer_invoice, True)
combo.add_attribute(renderer_invoice, "text", 2)
renderer_date = Gtk.CellRendererText()
combo.pack_start(renderer_date, True)
combo.add_attribute(renderer_date, "text", 3)
entry = combo.get_child()
entry.set_completion(self.builder.get_object('product_completion'))
def price_edited(self, cellrenderer, path, text):
c = DB.cursor()
iter_ = self.credit_items_store.get_iter(path)
self.check_row_id(iter_)
row_id = self.credit_items_store[iter_][0]
invoice_item_id = self.credit_items_store[iter_][8]
try:
c.execute(
"WITH tax_cte AS "
"(SELECT tr.rate / 100 AS rate "
"FROM invoice_items AS ii "
"JOIN tax_rates AS tr ON tr.id = ii.tax_rate_id "
"WHERE ii.id = %s"
") "
"UPDATE credit_memo_items "
"SET "
"(price, "
"ext_price, "
"tax) "
"= "
"(%s, "
"qty*%s, "
"qty*%s*(SELECT rate FROM tax_cte)) "
"WHERE id = %s "
"RETURNING price::text, ext_price::text, tax::text",
(invoice_item_id, text, text, text, row_id))
except psycopg2.DataError as e:
self.show_message(str(e))
DB.rollback()
return
for row in c.fetchall():
price = row[0]
ext_price = row[1]
tax = row[2]
self.credit_items_store[iter_][5] = price
self.credit_items_store[iter_][6] = ext_price
self.credit_items_store[iter_][7] = tax
c.close()
self.calculate_totals()
def qty_edited(self, cellrenderertext, path, text):
c = DB.cursor()
iter_ = self.credit_items_store.get_iter(path)
self.check_row_id(iter_)
row_id = self.credit_items_store[iter_][0]
invoice_item_id = self.credit_items_store[iter_][8]
try:
c.execute(
"WITH tax_cte AS "
"(SELECT tr.rate / 100 AS rate "
"FROM invoice_items AS ii "
"JOIN tax_rates AS tr ON tr.id = ii.tax_rate_id "
"WHERE ii.id = %s"
") "
"UPDATE credit_memo_items "
"SET "
"(qty, "
"ext_price, "
"tax) "
"= "
"(%s, "
"%s*price, "
"%s*price*(SELECT rate FROM tax_cte)) "
"WHERE id = %s "
"RETURNING qty::text, ext_price::text",
(invoice_item_id, text, text, text, row_id))
except psycopg2.DataError as e:
self.show_message(str(e))
DB.rollback()
return
for row in c.fetchall():
qty = row[0]
ext_price = row[1]
self.credit_items_store[iter_][1] = qty
self.credit_items_store[iter_][6] = ext_price
c.close()
self.calculate_totals()
def tax_edited(self, cellrendererspin, path, text):
iter_ = self.credit_items_store.get_iter(path)
self.check_row_id(iter_)
row_id = self.credit_items_store[iter_][0]
try:
self.cursor.execute(
"UPDATE credit_memo_items "
"SET tax = %s "
"WHERE id = %s "
"RETURNING tax::text", (text, row_id))
except psycopg2.DataError as e:
self.show_message(str(e))
DB.rollback()
return
for row in self.cursor.fetchall():
tax = row[0]
self.credit_items_store[iter_][7] = tax
self.calculate_totals()
def product_match_func(self, completion, key, tree_iter):
split_search_text = key.split()
for text in split_search_text:
if text not in self.product_store[tree_iter][1].lower():
return False
return True
def calculate_totals(self):
c = DB.cursor()
c.execute(
"WITH cte AS "
"(SELECT "
"SUM(ext_price) AS subtotal, "
"SUM(tax) AS tax, "
"SUM(tax + ext_price) AS total "
"FROM credit_memo_items WHERE "
"(credit_memo_id, deleted) = (%s, False) "
")"
"UPDATE credit_memos "
"SET "
"(total, "
"tax, "
"amount_owed) "
"= "
"((SELECT subtotal FROM cte),"
"(SELECT tax FROM cte),"
"(SELECT total FROM cte)"
")"
"WHERE id = %s "
"RETURNING "
"total::money, "
"tax::money, "
"amount_owed::money", (self.credit_memo_id, self.credit_memo_id))
for row in c.fetchall():
subtotal = row[0]
tax = row[1]
total = row[2]
self.builder.get_object('subtotal_entry').set_text(subtotal)
self.builder.get_object('tax_entry').set_text(tax)
self.builder.get_object('total_entry').set_text(total)
c.close()
DB.commit()
self.credit_memo_template = None # credit memo changed, force regenerate
def populate_customer_store(self, m=None, i=None):
self.customer_store.clear()
self.cursor.execute("SELECT c.id::text, c.name, c.ext_name "
"FROM contacts AS c "
"JOIN invoices AS i ON c.id = i.customer_id "
"WHERE (c.deleted, c.customer, i.paid) = "
"(False, True, True) "
"GROUP BY c.id, c.name, c.ext_name "
"ORDER BY name")
for row in self.cursor.fetchall():
self.customer_store.append(row)
def customer_combo_changed(self, combo):
customer_id = combo.get_active_id()
if customer_id != None:
self.select_customer(customer_id)
def customer_match_selected(self, completion, model, _iter):
customer_id = model[_iter][0]
self.select_customer(customer_id)
def select_customer(self, customer_id):
self.customer_id = customer_id
self.cursor.execute(
"SELECT "
"address, "
"COALESCE(cm.id, NULL), "
"COALESCE(-total, -0.00)::money, "
"COALESCE(-tax, -0.00)::money, "
"COALESCE(-amount_owed, -0.00)::money, "
"COALESCE(dated_for, now()), "
"COALESCE(comments, '') "
"FROM contacts AS c "
"LEFT JOIN credit_memos AS cm "
"ON cm.customer_id = c.id AND cm.posted = False "
"WHERE c.id = %s", (customer_id, ))
for row in self.cursor.fetchall():
address = row[0]
self.credit_memo_id = row[1]
subtotal = row[2]
tax = row[3]
total = row[4]
self.date = row[5]
comments = row[6]
self.builder.get_object('address_entry').set_text(address)
self.builder.get_object('subtotal_entry').set_text(subtotal)
self.builder.get_object('tax_entry').set_text(tax)
self.builder.get_object('total_entry').set_text(total)
self.date_calendar.set_date(self.date)
self.builder.get_object('comments_buffer').set_text(comments)
self.populate_credit_memo()
self.populate_product_store()
self.builder.get_object('menuitem2').set_sensitive(True)
self.builder.get_object('button1').set_sensitive(True)
self.builder.get_object('button2').set_sensitive(True)
self.builder.get_object('comments_textview').set_sensitive(True)
def populate_credit_memo(self):
c = DB.cursor()
self.credit_items_store.clear()
c.execute(
"SELECT "
"cmi.id, "
"cmi.qty::text, "
"p.id, "
"p.name, "
"p.ext_name, "
"cmi.price::text, "
"cmi.ext_price::text, "
"cmi.tax::text, "
"cmi.invoice_item_id, "
"ili.invoice_id, "
"date_returned::text, "
"format_date(date_returned) "
"FROM credit_memo_items AS cmi "
"JOIN invoice_items AS ili ON ili.id = cmi.invoice_item_id "
"JOIN products AS p ON p.id = ili.product_id "
"WHERE (credit_memo_id, cmi.deleted) = (%s, False) "
"ORDER BY cmi.id", (self.credit_memo_id, ))
for row in c.fetchall():
self.credit_items_store.append(row)
c.close()
def populate_product_store(self, m=None, i=None):
self.product_store.clear()
c = DB.cursor()
c.execute(
"SELECT ili.id::text, p.name || ' {' || ext_name || '}', "
"i.id::text, format_date(i.dated_for) "
"FROM products AS p "
"JOIN invoice_items AS ili ON ili.product_id = p.id "
"JOIN invoices AS i ON ili.invoice_id = i.id "
"WHERE (customer_id, posted) = (%s, True) "
"ORDER BY p.name", (self.customer_id, ))
for row in c.fetchall():
self.product_store.append(row)
c.close()
def treeview_cursor_changed(self, treeview):
selection = treeview.get_selection()
model, path = selection.get_selected_rows()
if path == []:
return
product_id = model[path][2]
store = self.builder.get_object('serial_number_store')
store.clear()
self.cursor.execute(
"SELECT ii.id, sn.serial_number "
"FROM serial_numbers AS sn "
"JOIN invoice_items AS ii ON ii.id = sn.invoice_item_id "
"JOIN invoices AS i ON i.id = ii.invoice_id "
"WHERE (i.customer_id, ii.product_id) = (%s, %s)",
(self.customer_id, product_id))
for row in self.cursor.fetchall():
store.append(row)
def serial_number_changed(self, combo, path, tree_iter):
model = self.builder.get_object('serial_number_store')
invoice_item_id = model[tree_iter][0]
serial_number = model[tree_iter][1]
self.credit_items_store[path][6] = invoice_item_id
self.credit_items_store[path][11] = serial_number
def return_day_selected(self, calendar):
date = calendar.get_date()
_iter = self.credit_items_store.get_iter(self.path)
row_id = self.credit_items_store[_iter][0]
self.cursor.execute(
"UPDATE credit_memo_items "
"SET date_returned = %s "
"WHERE id = %s "
"RETURNING date_returned::text, "
"format_date (date_returned)", (date, row_id))
for row in self.cursor.fetchall():
date = row[0]
date_formatted = row[1]
self.credit_items_store[_iter][10] = str(date)
self.credit_items_store[_iter][11] = date_formatted
def date_entry_icon_released(self, entry, icon, position):
self.date_calendar.set_relative_to(entry)
self.date_calendar.show_all()
def day_selected(self, calendar):
self.date = calendar.get_date()
text = calendar.get_text()
self.builder.get_object('entry1').set_text(text)
if self.credit_memo_id:
self.cursor.execute(
"UPDATE credit_memos "
"SET dated_for = %s "
"WHERE id = %s", (self.date, self.credit_memo_id))
DB.commit()
def date_returned_editing_started(self, renderer, entry, path):
self.path = path
current_date = self.credit_items_store[path][10]
self.date_returned_calendar.set_date(current_date)
GLib.idle_add(self.date_returned_calendar.show_all)
entry.destroy()
def check_row_id(self, _iter):
c = DB.cursor()
row_id = self.credit_items_store[_iter][0]
qty = self.credit_items_store[_iter][1]
price = self.credit_items_store[_iter][5]
tax = self.credit_items_store[_iter][7]
invoice_item_id = self.credit_items_store[_iter][8]
if row_id == 0:
c.execute(
"INSERT INTO credit_memo_items "
"(qty, "
"invoice_item_id, "
"price, "
"tax, "
"date_returned, "
"credit_memo_id) "
"VALUES "
"(%s, %s, %s, %s, %s, %s) RETURNING id",
(qty, invoice_item_id, price, tax, self.date,
self.credit_memo_id))
row_id = c.fetchone()[0]
self.credit_items_store[_iter][0] = row_id
DB.commit()
c.close()
def new_item_clicked(self, button):
c = DB.cursor()
self.check_credit_memo_id()
invoice_item_id = self.product_store[0][0]
c.execute(
"SELECT "
"0, "
"1.0::text, "
"p.id, "
"p.name, "
"p.ext_name, "
"price::text, "
"price::text, "
"ROUND(1.0 * price * tr.rate/100, 2)::text, "
"ii.id, "
"ii.invoice_id, "
"CURRENT_DATE::text, "
"format_date(CURRENT_DATE) "
"FROM invoice_items AS ii "
"JOIN products AS p ON p.id = ii.product_id "
"JOIN tax_rates AS tr ON tr.id = ii.tax_rate_id "
"WHERE ii.id = %s LIMIT 1", (invoice_item_id, ))
for row in c.fetchall():
iter_ = self.credit_items_store.append(row)
treeview = self.builder.get_object('treeview1')
column = treeview.get_column(0)
path = self.credit_items_store.get_path(iter_)
treeview.set_cursor(path, column, True)
def delete_item_clicked(self, menuitem):
selection = self.builder.get_object('treeview-selection1')
model, path = selection.get_selected_rows()
if path == []:
return
row_id = model[path][0]
self.cursor.execute(
"UPDATE credit_memo_items "
"SET deleted = True "
"WHERE id = %s", (row_id, ))
DB.commit()
self.populate_credit_memo()
def treeview_key_release_event(self, treeview, event):
keyname = Gdk.keyval_name(event.keyval)
path, col = treeview.get_cursor()
# only visible columns!!
columns = [c for c in treeview.get_columns() if c.get_visible()]
colnum = columns.index(col)
if keyname == "Tab" or keyname == "Esc":
if colnum + 1 < len(columns):
next_column = columns[colnum + 1]
else:
tmodel = treeview.get_model()
titer = tmodel.iter_next(tmodel.get_iter(path))
if titer is None:
titer = tmodel.get_iter_first()
path = tmodel.get_path(titer)
next_column = columns[0]
if keyname == 'Tab':
GLib.idle_add(treeview.set_cursor, path, next_column, True)
elif keyname == 'Escape':
pass
def check_credit_memo_id(self):
if self.credit_memo_id == None:
self.cursor.execute(
"INSERT INTO credit_memos "
"(name, customer_id, date_created, total) "
"VALUES ('Credit Memo', %s, now(), 0.00) "
"RETURNING id", (self.customer_id, ))
self.credit_memo_id = self.cursor.fetchone()[0]
DB.commit()
def post_credit_memo_clicked(self, button):
import credit_memo_template as cmt
self.credit_memo_template = cmt.Setup(self.credit_items_store,
self.credit_memo_id,
self.customer_id)
self.credit_memo_template.print_pdf(self.window)
self.credit_memo_template.post()
DB.commit()
self.window.destroy()
def view_document_activated(self, button):
if not self.credit_memo_template:
import credit_memo_template as cmt
self.credit_memo_template = cmt.Setup(self.credit_items_store,
self.credit_memo_id,
self.customer_id)
self.credit_memo_template.view_odt()
def comments_buffer_changed(self, textbuffer):
start = textbuffer.get_start_iter()
end = textbuffer.get_end_iter()
notes = textbuffer.get_text(start, end, True)
self.cursor.execute(
"UPDATE credit_memos SET comments = %s "
"WHERE id = %s", (notes, self.credit_memo_id))
DB.commit()
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()
class FiscalYearGUI:
def __init__(self):
self.builder = Gtk.Builder()
self.builder.add_from_file(UI_FILE)
self.builder.connect_signals(self)
self.cursor = DB.cursor()
self.start_calendar = DateTimeCalendar(True)
self.start_calendar.connect('day-selected', self.start_day_selected)
self.start_calendar.set_today()
self.end_calendar = DateTimeCalendar(True)
self.end_calendar.connect('day-selected', self.end_day_selected)
self.end_calendar.set_date(datetime.today() + timedelta(days=365))
self.fiscal_year_store = self.builder.get_object('fiscal_year_store')
self.window = self.builder.get_object('window')
self.window.show_all()
self.populate_fiscal_years()
def destroy(self, widget):
self.cursor.close()
def populate_fiscal_years(self):
self.fiscal_year_store.clear()
self.cursor.execute("SELECT "
"id, "
"name, "
"start_date::text, "
"format_date(start_date), "
"end_date::text, "
"format_date(end_date), "
"active "
"FROM fiscal_years ORDER BY start_date")
for row in self.cursor.fetchall():
self.fiscal_year_store.append(row)
DB.rollback()
def active_toggled(self, toggle_renderer, path):
active = not toggle_renderer.get_active()
id_ = self.fiscal_year_store[path][0]
self.cursor.execute(
"UPDATE fiscal_years SET active = %s "
"WHERE id = %s", (active, id_))
DB.commit()
self.populate_fiscal_years()
def fiscal_years_name_edited(self, textrenderer, path, text):
id_ = self.fiscal_year_store[path][0]
self.cursor.execute(
"UPDATE fiscal_years SET name = %s "
"WHERE id = %s", (text, id_))
DB.commit()
self.populate_fiscal_years()
def create_fiscal_year_clicked(self, button):
dialog = self.builder.get_object('dialog1')
response = dialog.run()
dialog.hide()
if response == Gtk.ResponseType.ACCEPT:
fiscal_name = self.builder.get_object('entry1').get_text()
self.cursor.execute(
"INSERT INTO fiscal_years "
"(name, start_date, end_date, active) "
"VALUES (%s, %s, %s, True)",
(fiscal_name, self.start_date, self.end_date))
DB.commit()
self.builder.get_object('entry1').set_text('')
self.populate_fiscal_years()
def start_day_selected(self, calendar):
self.start_date = calendar.get_datetime()
day_text = calendar.get_text()
self.builder.get_object('entry2').set_text(day_text)
def end_day_selected(self, calendar):
self.end_date = calendar.get_datetime()
day_text = calendar.get_text()
self.builder.get_object('entry3').set_text(day_text)
def start_date_icon_released(self, entry, position, event):
self.start_calendar.set_relative_to(entry)
#self.start_calendar.set_position (Gtk.PositionType.TOP)
self.start_calendar.show()
def end_date_icon_released(self, entry, position, event):
self.end_calendar.set_relative_to(entry)
self.end_calendar.show()
def fiscal_year_name_entry_changed(self, entry):
if entry.get_text() != '':
self.builder.get_object('button1').set_sensitive(True)
else:
self.builder.get_object('button1').set_sensitive(False)
class ResourceDiaryGUI:
def __init__(self, db):
self.builder = Gtk.Builder()
self.builder.add_from_file(UI_FILE)
self.builder.connect_signals(self)
self.db = db
self.cursor = self.db.cursor()
self.populating = False
textview = self.builder.get_object('textview1')
spell_check.add_checker_to_widget(textview)
self.calendar = DateTimeCalendar(self.db)
self.calendar.connect('day-selected', self.calendar_day_selected)
self.calendar.set_today()
self.window = self.builder.get_object('window1')
self.window.show_all()
def add_day_info(self):
self.populating = True
cursor = self.db.cursor()
cursor.execute(
"WITH date_range AS "
"(SELECT generate_series "
"(%s - INTERVAL '4 year', "
"%s, '1 year' "
") AS diary_date "
") "
"SELECT "
"COALESCE(subject, ''), "
"to_char(date_range.diary_date, 'Dy FMMonth DD YYYY') "
"FROM date_range "
"LEFT JOIN resources "
"ON date_range.diary_date = resources.dated_for "
"AND diary = True "
"ORDER BY date_range.diary_date DESC", (self.day, self.day))
tupl = cursor.fetchall()
subject0 = tupl[0][0]
date0 = tupl[0][1]
self.builder.get_object('label1').set_label(date0)
self.builder.get_object('entry1').set_text(date0)
self.builder.get_object('textbuffer1').set_text(subject0)
subject1 = tupl[1][0]
date1 = tupl[1][1]
self.builder.get_object('label2').set_label(date1)
self.builder.get_object('textbuffer2').set_text(subject1)
subject2 = tupl[2][0]
date2 = tupl[2][1]
self.builder.get_object('label3').set_label(date2)
self.builder.get_object('textbuffer3').set_text(subject2)
subject3 = tupl[3][0]
date3 = tupl[3][1]
self.builder.get_object('label4').set_label(date3)
self.builder.get_object('textbuffer4').set_text(subject3)
subject4 = tupl[4][0]
date4 = tupl[4][1]
self.builder.get_object('label5').set_label(date4)
self.builder.get_object('textbuffer5').set_text(subject4)
cursor.close()
self.populating = False
def next_day_clicked(self, button):
self.day = (self.day + timedelta(days=1))
self.calendar.set_date(
self.day) # this will fire signal and update views
def previous_day_clicked(self, button):
self.day = (self.day - timedelta(days=1))
self.calendar.set_date(
self.day) # this will fire signal and update views
def diary_textbuffer_changed(self, textbuffer):
if self.populating == True:
return
end = textbuffer.get_end_iter()
start = textbuffer.get_start_iter()
text = textbuffer.get_text(start, end, True)
self.cursor.execute(
"INSERT INTO resources "
"(subject, dated_for, diary) "
"VALUES (%s, %s, True) "
"ON CONFLICT (dated_for, diary) "
"DO UPDATE SET subject = %s "
"WHERE (resources.diary, resources.dated_for) = "
"(True, %s)", (text, self.day, text, self.day))
self.db.commit()
def entry_icon_release(self, entry, position, button):
self.calendar.set_relative_to(entry)
self.calendar.show()
def calendar_day_selected(self, calendar):
self.day = calendar.get_date()
text = calendar.get_user_text()
self.add_day_info()
class IncomingInvoiceGUI(Gtk.Builder):
__gsignals__ = {
'invoice_applied': (GObject.SignalFlags.RUN_FIRST, GObject.TYPE_NONE, ())
}
"""the invoice_applied signal is used to send a message to the parent
window that the incoming invoice is now finished"""
def __init__(self):
GObject.GObject.__init__(self)
Gtk.Builder.__init__(self)
self.add_from_file(UI_FILE)
self.connect_signals(self)
self.cursor = DB.cursor()
self.handler_ids = list()
for connection in (("contacts_changed", self.populate_service_providers ),):
handler = broadcaster.connect(connection[0], connection[1])
self.handler_ids.append(handler)
self.expense_account_store = expense_tree
self.get_object('cellrenderercombo1').set_property('model', expense_tree)
self.get_object('account_completion').set_model(expense_list)
self.calendar = DateTimeCalendar()
self.calendar.connect('day-selected', self.calendar_day_selected)
self.date = None
price_column = self.get_object ('treeviewcolumn2')
price_renderer = self.get_object ('cellrenderertext1')
price_column.set_cell_data_func(price_renderer, self.price_cell_func)
provider_completion = self.get_object('provider_completion')
provider_completion.set_match_func(self.provider_match_func)
self.populating = False
self.service_provider_store = self.get_object(
'service_provider_store')
self.expense_percentage_store = self.get_object(
'expense_percentage_store')
self.bank_account_store = self.get_object('bank_account_store')
self.cash_account_store = self.get_object('cash_account_store')
self.credit_card_store = self.get_object('credit_card_store')
self.populate_stores ()
self.populate_service_providers ()
self.expense_percentage_store.append([0, Decimal('1.00'), 0, "", "", ""])
self.window = self.get_object('window1')
self.window.show_all()
self.file_data = None
def destroy (self, widget):
for handler in self.handler_ids:
broadcaster.disconnect(handler)
self.cursor.close()
def provider_match_func(self, completion, key, iter_):
split_search_text = key.split()
for text in split_search_text:
if text not in self.service_provider_store[iter_][1].lower():
return False
return True
def amount_focus_in_event (self, spinbutton, event):
GLib.idle_add(spinbutton.select_region, 0, -1)
def focus (self, window, event):
pass
self.populating = False
def populate_service_providers (self, m=None, i=None):
self.populating = True
combo = self.get_object('combobox1')
active_sp = combo.get_active_id()
self.service_provider_store.clear()
self.cursor.execute("SELECT id::text, name, ext_name FROM contacts "
"WHERE service_provider = True "
"ORDER BY name")
for row in self.cursor.fetchall():
self.service_provider_store.append(row)
combo.set_active_id(active_sp)
self.populating = False
DB.rollback()
def populate_stores (self):
self.cursor.execute("SELECT number::text, name FROM gl_accounts "
"WHERE check_writing = True ORDER BY name ")
for row in self.cursor.fetchall():
self.bank_account_store.append(row)
self.cursor.execute("SELECT number::text, name FROM gl_accounts "
"WHERE cash_account = True ORDER BY name ")
for row in self.cursor.fetchall():
self.cash_account_store.append(row)
self.cursor.execute("SELECT number::text, name FROM gl_accounts "
"WHERE credit_card_account = True ORDER BY name ")
for row in self.cursor.fetchall():
self.credit_card_store.append(row)
DB.rollback()
def set_shipping_description (self, text):
self.get_object('entry1').set_text(text)
def set_date (self, date):
self.calendar.set_date(date)
def service_provider_clicked (self, button):
import contacts_overview
contacts_overview.ContactsOverviewGUI()
def provider_match_selected(self, completion, model, iter_):
self.provider_id = model[iter_][0]
self.get_object('combobox1').set_active_id(self.provider_id)
def service_provider_combo_changed (self, combo):
if self.populating == True:
return
a_iter = combo.get_active_iter()
if a_iter == None:
return
self.check_if_all_entries_valid ()
contact_name = self.service_provider_store[a_iter][1]
self.get_object('label14').set_label(contact_name)
def add_percentage_row_clicked (self, button):
self.expense_percentage_store.append([0, Decimal('1.00'), 0, "", "", ""])
self.add_expense_totals ()
def delete_percentage_row_clicked (self, button):
selection = self.get_object('treeview-selection1')
model, path = selection.get_selected_rows()
if path != []:
tree_iter = model.get_iter(path)
self.expense_percentage_store.remove(tree_iter)
self.add_expense_totals ()
def equalize_amounts_clicked (self, button):
lines = self.expense_percentage_store.iter_n_children()
if lines == 0:
return
percentage = Decimal('100') / lines
invoice_amount = self.get_object('spinbutton1').get_text()
percent = Decimal(percentage) / Decimal('100')
split_amount = Decimal(invoice_amount) * percent
for row in self.expense_percentage_store:
row[0] = percentage
row[1] = split_amount
self.add_expense_totals ()
def invoice_spinbutton_changed (self, spinbutton):
self.add_expense_totals ()
def expense_amount_edited (self, renderer, path, text):
value = Decimal(text).quantize(Decimal('0.01'), rounding = ROUND_HALF_UP)
self.expense_percentage_store[path][1] = value
self.add_expense_totals ()
def price_cell_func(self, column, cellrenderer, model, iter1, data):
price = model.get_value(iter1, 1)
cellrenderer.set_property("text" , str(price))
def percent_render_edited (self, renderer, path, text):
self.expense_percentage_store[path][0] = int(text)
invoice_amount = self.get_object('spinbutton1').get_text()
percent = Decimal(text) / Decimal('100')
split_amount = Decimal(invoice_amount) * percent
self.expense_percentage_store[path][1] = split_amount
self.add_expense_totals ()
def add_expense_totals (self):
total = Decimal()
for row in self.expense_percentage_store:
total += row[1]
money_text = get_written_check_amount_text (total)
self.get_object('label10').set_label(money_text)
self.get_object('entry4').set_text('${:,.2f}'.format(total))
self.get_object('entry5').set_text('${:,.2f}'.format(total))
self.get_object('entry6').set_text('${:,.2f}'.format(total))
self.get_object('entry8').set_text('${:,.2f}'.format(total))
self.check_if_all_entries_valid ()
def expense_account_render_changed (self, renderer, path, tree_iter):
account_number = self.expense_account_store[tree_iter][0]
account_name = self.expense_account_store[tree_iter][1]
account_path = self.expense_account_store[tree_iter][2]
self.expense_percentage_store[path][2] = int(account_number)
self.expense_percentage_store[path][3] = account_name
self.expense_percentage_store[path][4] = account_path
self.check_if_all_entries_valid ()
def expense_combo_editing_started (self, cellrenderer, celleditable, path):
entry = celleditable.get_child()
entry.set_completion(self.get_object('account_completion'))
def account_match_selected (self, entrycompletion, model, treeiter):
selection = self.get_object('treeview-selection1')
treeview_model, path = selection.get_selected_rows()
if path == []:
return
account_number = model[treeiter][0]
account_name = model[treeiter][1]
account_path = model[treeiter][2]
treeview_model[path][2] = int(account_number)
treeview_model[path][3] = account_name
treeview_model[path][4] = account_path
self.check_if_all_entries_valid()
def remark_edited (self, cellrenderertext, path, text):
self.expense_percentage_store[path][5] = text
def bank_credit_card_combo_changed (self, combo):
if combo.get_active() == None:
self.get_object('entry3').set_sensitive(False)
self.get_object('entry5').set_sensitive(False)
else:
self.get_object('entry3').set_sensitive(True)
self.get_object('entry5').set_sensitive(True)
bank_account = combo.get_active_id()
check_number = get_check_number(bank_account)
self.get_object('entry7').set_text(str(check_number))
self.check_if_all_entries_valid ()
def cash_combo_changed (self, combo):
self.check_if_all_entries_valid ()
def transaction_entry_changed (self, entry):
self.check_if_all_entries_valid ()
def credit_card_changed (self, combo):
self.check_if_all_entries_valid ()
def check_if_all_entries_valid (self):
check_button = self.get_object('button3')
transfer_button = self.get_object('button4')
cash_button = self.get_object('button5')
credit_card_button = self.get_object('button7')
check_button.set_sensitive(False)
#transfer_button.set_sensitive(False)
cash_button.set_sensitive(False)
credit_card_button.set_sensitive(False)
if self.get_object('combobox1').get_active() == -1:
self.set_button_message('No service provider')
return # no service provider selected
if self.date == None:
self.set_button_message('No date selected')
return
invoice_amount = Decimal(self.get_object('spinbutton1').get_text())
if invoice_amount == 0.00:
self.set_button_message('No invoice amount')
return
text = self.get_object('entry4').get_text()
payment_amount = Decimal(re.sub("[^0-9.]", "", text))
if invoice_amount != payment_amount:
self.set_button_message('Invoice amount does not match payment')
return
for row in self.expense_percentage_store:
if row[2] == 0:
self.set_button_message('Missing expense accounts')
return
if self.get_object('combobox3').get_active() > -1:
#cash account selected
cash_button.set_label('Cash payment')
cash_button.set_sensitive(True)
else:
cash_button.set_label('No cash account selected')
if self.get_object('combobox4').get_active() > -1:
#cash account selected
credit_card_button.set_label('Credit card payment')
credit_card_button.set_sensitive(True)
else:
credit_card_button.set_label('No credit card selected')
if self.get_object('combobox2').get_active() > -1:
# bank / credit card selected
check_button.set_label('Check payment')
check_button.set_sensitive(True)
transfer_button.set_label('Transfer payment')
transfer_button.set_sensitive(True)
else:
check_button.set_label('No bank account selected')
transfer_button.set_label('No bank account selected')
def set_button_message (self, message):
self.get_object('button3').set_label(message)
self.get_object('button4').set_label(message)
self.get_object('button5').set_label(message)
self.get_object('button7').set_label(message)
def cash_payment_clicked (self, button):
total = self.save_incoming_invoice ()
cash_account = self.get_object('combobox3').get_active_id()
self.invoice.cash_payment (total, cash_account)
DB.commit()
button.set_sensitive(False)
self.emit('invoice_applied')
def credit_card_payment_clicked (self, button):
total = self.save_incoming_invoice ()
credit_card = self.get_object('combobox4').get_active_id()
transfer_number = self.get_object('entry3').get_text()
active = self.get_object('combobox1').get_active()
service_provider = self.service_provider_store[active][1]
description = "%s : %s" % (service_provider, transfer_number)
self.invoice.credit_card_payment (total, description, credit_card)
DB.commit()
button.set_sensitive(False)
self.emit('invoice_applied')
def transfer_clicked (self, button):
total = self.save_incoming_invoice ()
checking_account = self.get_object('combobox2').get_active_id()
transfer_number = self.get_object('entry3').get_text()
active = self.get_object('combobox1').get_active()
service_provider = self.service_provider_store[active][1]
description = "%s : %s" % (service_provider, transfer_number)
self.invoice.transfer (total, description, checking_account)
DB.commit()
button.set_sensitive(False)
self.emit('invoice_applied')
def post_only_clicked (self,button):
self.perform_payment()
button.set_sensitive(False)
def perform_payment(self):
total = self.save_incoming_invoice ()
checking_account = self.get_object('combobox2').get_active_id()
check_number = self.get_object('entry7').get_text()
active = self.get_object('combobox1').get_active()
description = self.service_provider_store[active][1]
self.invoice.check_payment(total, check_number, checking_account, description)
DB.commit()
self.emit('invoice_applied')
check_number = get_check_number(checking_account)
self.get_object('entry7').set_text(str(check_number))
self.file_data = None
def save_incoming_invoice (self):
c = DB.cursor()
contact_id = self.get_object('combobox1').get_active_id()
description = self.get_object('entry1').get_text()
total = Decimal(self.get_object('spinbutton1').get_text())
self.invoice = transactor.ServiceProviderPayment (
self.date,
total)
c.execute( "INSERT INTO incoming_invoices "
"(contact_id, "
"date_created, "
"amount, "
"description, "
"gl_transaction_id, "
"attached_pdf) "
"VALUES (%s, %s, %s, %s, %s, %s) RETURNING id",
(contact_id, self.date, total, description,
self.invoice.transaction_id, self.file_data))
self.invoice_id = c.fetchone()[0] # self.invoice_id is a public variable
self.invoice.incoming_invoice_id = self.invoice_id
for row in self.expense_percentage_store:
amount = row[1]
account = row[2]
remark = row[5]
self.invoice.expense(amount, account, remark)
c.close()
return total
def balance_this_row_activated (self, menuitem):
selection = self.get_object('treeview-selection1')
model, path = selection.get_selected_rows()
if path == []:
return
tree_sum = Decimal()
for row in self.expense_percentage_store:
if row.path != path[0]:
tree_sum += row[1]
total = Decimal(self.get_object('spinbutton1').get_text())
model[path][1] = (total - tree_sum)
self.add_expense_totals ()
def treeview_button_release_event (self, treeview, event):
if event.button == 3:
menu = self.get_object('menu1')
menu.popup_at_pointer()
def calendar_day_selected (self, calendar):
self.date = calendar.get_date()
day_text = calendar.get_text()
self.get_object('entry2').set_text(day_text)
self.check_if_all_entries_valid ()
def calendar_entry_icon_released (self, widget, icon, event):
self.calendar.set_relative_to(widget)
self.calendar.show()
def attach_button_clicked (self, button):
import pdf_attachment
paw = pdf_attachment.PdfAttachmentWindow(self.window)
paw.connect("pdf_optimized", self.optimized_callback)
def optimized_callback (self, pdf_attachment_window):
self.file_data = pdf_attachment_window.get_pdf ()
def print_and_post_clicked (self, button):
total = Decimal(self.get_object('spinbutton1').get_text())
check_number = self.get_object('entry7').get_text()
bank_account = self.get_object('combobox2').get_active_id()
self.cursor.execute("SELECT "
"name, "
"checks_payable_to, "
"address, "
"city, "
"state, "
"zip, "
"phone "
"FROM contacts WHERE id = %s",(self.provider_id,))
provider = Item()
for line in self.cursor.fetchall():
provider.name = line[0]
provider.pay_to = line[1]
provider.street = line[2]
provider.city = line[3]
provider.state = line[4]
provider.zip = line[5]
provider.phone = line[6]
pay_to = line[1].split()[0]
items = list()
'''for row in self.provider_invoice_store:
if row[3] == True:'''
item = Item()
item.po_number = ''#row[0]
item.amount = ''#row[2]
item.date = ''#row[4]
items.append(item)
check = Item()
check.check_number = check_number
check.checking_account = bank_account
check.date = self.date
check.amount = total
check.amount_text = get_written_check_amount_text (total)
from py3o.template import Template
data = dict(contact = provider, check = check, items = items )#- kept this
#in case we ever wish to list the accountbreakdown on the check stubs
self.tmp_file = "/tmp/check" + pay_to +".odt"
self.tmp_file_pdf = "/tmp/check" + pay_to + ".pdf"
t = Template(template_dir+"/vendor_check_template.odt", self.tmp_file, True)
t.render(data)
subprocess.call(["odt2pdf", self.tmp_file])
p = printing.Operation(settings_file = 'Vendor_check')
p.set_file_to_print(self.tmp_file_pdf)
p.set_parent(self.window)
result = p.print_dialog()
if result != "user canceled":
self.perform_payment()
class EmployeeInfoGUI:
def __init__(self, db):
self.builder = Gtk.Builder()
self.builder.add_from_file(UI_FILE)
self.builder.connect_signals(self)
self.employee_store = self.builder.get_object('employee_store')
self.s_s_medicare_store = self.builder.get_object('s_s_medicare_store')
self.federal_withholding_store = self.builder.get_object(
'federal_withholding_store')
self.state_withholding_store = self.builder.get_object(
'state_withholding_store')
self.db = db
self.cursor = db.cursor()
self.populate_employee_store()
self.born_calendar = DateTimeCalendar(self.db)
self.on_payroll_since_calendar = DateTimeCalendar(self.db)
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.builder.get_object('window1')
self.window.show_all()
self.builder.get_object("button5").set_label("No scanner selected")
self.builder.get_object("button5").set_sensitive(False)
#self.data_queue = Queue()
#self.scanner_store = self.builder.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 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():
employee_id = row[0]
employee_name = row[1]
self.employee_store.append([employee_id, employee_name])
self.populating = False
def employee_treeview_cursor_changed(self, treeview):
if self.populating == True:
return
selection = self.builder.get_object('treeview-selection1')
model, path = selection.get_selected_rows()
self.employee_id = model[path][0]
self.employee_selected()
def employee_selected(self):
self.populating = True
self.cursor.execute(
"SELECT COALESCE(born, '1970-1-1'), "
"COALESCE (social_security, ''), "
"COALESCE (social_security_exempt, False), "
"COALESCE (on_payroll_since, '1970-1-1'), "
"COALESCE (wage, 10.00), "
"COALESCE (payment_frequency, 10), "
"COALESCE (married, False), "
"COALESCE (last_updated, '1970-1-1'), "
"COALESCE (state_withholding_exempt, False), "
"COALESCE (state_credits, 0), "
"COALESCE (state_extra_withholding, 0.00), "
"COALESCE (fed_withholding_exempt, False), "
"COALESCE (fed_credits, 0), "
"COALESCE (fed_extra_withholding, 0.00) "
"FROM payroll.employee_info WHERE employee_id = %s",
(self.employee_id, ))
for row in self.cursor.fetchall():
self.born_calendar.set_date(row[0])
self.builder.get_object('entry2').set_text(row[1])
self.builder.get_object('checkbutton3').set_active(row[2])
self.on_payroll_since_calendar.set_date(row[3])
self.builder.get_object('spinbutton6').set_value(row[4])
self.builder.get_object('spinbutton5').set_value(row[5])
self.builder.get_object('checkbutton4').set_active(row[6])
self.builder.get_object('label6').set_text(row[7])
self.builder.get_object('checkbutton2').set_active(row[8])
self.builder.get_object('spinbutton3').set_value(row[9])
self.builder.get_object('spinbutton2').set_value(row[10])
self.builder.get_object('checkbutton1').set_active(row[11])
self.builder.get_object('spinbutton4').set_value(row[12])
self.builder.get_object('spinbutton1').set_value(row[13])
break
else:
self.cursor.execute(
"INSERT INTO payroll.employee_info (employee_id) "
"VALUES (%s)", (self.employee_id, ))
self.db.commit()
GLib.timeout_add(50, self.employee_selected)
self.populating = False
self.populate_exemption_forms()
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, 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():
id = row[0]
date_formatted = date_to_user_format(row[1])
self.s_s_medicare_store.append([id, date_formatted])
self.cursor.execute(
"SELECT id, 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():
id = row[0]
date_formatted = date_to_user_format(row[1])
self.state_withholding_store.append([id, date_formatted])
self.cursor.execute(
"SELECT id, 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():
id = row[0]
date_formatted = date_to_user_format(row[1])
self.federal_withholding_store.append([id, date_formatted])
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 payment_frequency_value_changed(self, spinbutton):
if self.populating == True:
return
payment_frequency = spinbutton.get_value()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (payment_frequency, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(payment_frequency, datetime.today(), self.employee_id))
self.db.commit()
def state_income_status_toggled(self, checkbutton):
if self.populating == True:
return
status = checkbutton.get_active()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (state_withholding_exempt, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(status, datetime.today(), self.employee_id))
self.db.commit()
def state_credits_value_changed(self, spinbutton):
if self.populating == True:
return
state_credits = spinbutton.get_value()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (state_credits, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(state_credits, datetime.today(), self.employee_id))
self.db.commit()
def state_extra_withholding_value_changed(self, spinbutton):
if self.populating == True:
return
state_withholding = spinbutton.get_value()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (state_extra_withholding, last_updated) = "
"(%s, %s) "
"WHERE employee_id = %s",
(state_withholding, datetime.today(), self.employee_id))
self.db.commit()
def federal_income_status_toggled(self, checkbutton):
if self.populating == True:
return
status = checkbutton.get_active()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (fed_withholding_exempt, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(status, datetime.today(), self.employee_id))
self.db.commit()
def federal_credits_value_changed(self, spinbutton):
if self.populating == True:
return
federal_credits = spinbutton.get_value()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (fed_credits, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(federal_credits, datetime.today(), self.employee_id))
self.db.commit()
def federal_extra_withholding_value_changed(self, spinbutton):
if self.populating == True:
return
fed_withholding = spinbutton.get_value()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (fed_extra_withholding, last_updated) = "
"(%s, %s) "
"WHERE employee_id = %s",
(fed_withholding, datetime.today(), self.employee_id))
self.db.commit()
def married_checkbutton_toggled(self, checkbutton):
if self.populating == True:
return
married = checkbutton.get_active()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (married, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(married, datetime.today(), self.employee_id))
self.db.commit()
def social_security_entry_changed(self, entry):
if self.populating == True:
return
s_s = entry.get_text()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (social_security, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(s_s, datetime.today(), self.employee_id))
self.db.commit()
def wage_spinbutton_value_changed(self, spinbutton):
if self.populating == True:
return
wage = spinbutton.get_value()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (wage, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(wage, datetime.today(), self.employee_id))
self.db.commit()
def social_security_exemption_changed(self, checkbutton):
if self.populating == True:
return
exemption = checkbutton.get_active()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (social_security_exempt, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(exemption, datetime.today(), self.employee_id))
self.db.commit()
def scanner_combo_changed(self, combo):
if combo.get_active() > -1:
self.builder.get_object("button5").set_label("Scan")
self.builder.get_object("button5").set_sensitive(True)
def show_scan_pdf_dialog(self, column):
global device
dialog = self.builder.get_object("dialog1")
result = dialog.run()
dialog.hide()
if result != Gtk.ResponseType.ACCEPT:
return
if device == None:
device_address = self.builder.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()))
self.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.builder.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.builder.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.builder.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.builder.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.builder.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.builder.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.builder.get_object('entry1').set_text(date_text)
if self.populating == True:
return
date = calendar.get_date()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (born, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(date, datetime.today(), self.employee_id))
self.db.commit()
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.builder.get_object('entry3').set_text(date_text)
if self.populating == True:
return
date = calendar.get_date()
self.cursor.execute(
"UPDATE payroll.employee_info "
"SET (on_payroll_since, last_updated) = (%s, %s) "
"WHERE employee_id = %s",
(date, datetime.today(), self.employee_id))
self.db.commit()
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()
class InvoiceGUI:
def __init__(self, main, invoice_id = None):
self.invoice_id = invoice_id
self.builder = Gtk.Builder()
self.builder.add_from_file(UI_FILE)
self.builder.connect_signals(self)
self.window = self.builder.get_object('window')
self.edited_renderer_text = 1
self.qty_renderer_value = 1
self.invoice = None
self.main = main
self.db = main.db
self.populating = False
self.invoice_store = self.builder.get_object('invoice_store')
self.location_store = self.builder.get_object('location_store')
self.document_list_store = self.builder.get_object('document_list_store')
enforce_target = Gtk.TargetEntry.new('text/plain', Gtk.TargetFlags(1), 129)
self.treeview = self.builder.get_object('treeview2')
self.treeview.drag_dest_set(Gtk.DestDefaults.ALL, [enforce_target], Gdk.DragAction.COPY)
self.treeview.connect("drag-data-received", self.on_drag_data_received)
self.treeview.drag_dest_set_target_list([enforce_target])
self.cursor = self.db.cursor()
self.handler_c_id = main.connect ("contacts_changed", self.populate_customer_store )
self.handler_p_id = main.connect ("products_changed", self.populate_product_store )
self.customer_id = 0
self.menu_visible = False
textview = self.builder.get_object('comment_textview')
spell_check.add_checker_to_widget (textview)
self.customer_store = self.builder.get_object('customer_store')
self.product_store = self.builder.get_object('product_store')
self.barcodes_not_found_store = self.builder.get_object('barcodes_not_found_store')
product_completion = self.builder.get_object('product_completion')
product_completion.set_match_func(self.product_match_func)
customer_completion = self.builder.get_object('customer_completion')
customer_completion.set_match_func(self.customer_match_func)
qty_column = self.builder.get_object ('treeviewcolumn1')
qty_renderer = self.builder.get_object ('cellrenderertext2')
qty_column.set_cell_data_func(qty_renderer, self.qty_cell_func)
price_column = self.builder.get_object ('treeviewcolumn4')
price_renderer = self.builder.get_object ('cellrenderertext5')
price_column.set_cell_data_func(price_renderer, self.price_cell_func)
tax_column = self.builder.get_object ('treeviewcolumn5')
tax_renderer = self.builder.get_object ('cellrenderertext6')
tax_column.set_cell_data_func(tax_renderer, self.tax_cell_func)
ext_price_column = self.builder.get_object ('treeviewcolumn6')
ext_price_renderer = self.builder.get_object ('cellrenderertext7')
ext_price_column.set_cell_data_func(ext_price_renderer, self.ext_price_cell_func)
self.document_type = "Invoice"
self.populate_location_store ()
self.populate_customer_store ()
self.populate_product_store ()
self.builder.get_object('combobox2').set_active(0)
self.calendar = DateTimeCalendar(self.db)
self.calendar.connect('day-selected', self.calendar_day_selected)
self.calendar.set_relative_to(self.builder.get_object('entry1'))
self.calendar.set_today()
if invoice_id != None: # edit an existing invoice; put all the existing items in the liststore
self.populate_customer_store ()
self.set_widgets_sensitive ()
self.populate_invoice_items()
self.cursor.execute("SELECT customer_id, COALESCE(dated_for, now())"
"FROM invoices "
"WHERE id = %s", (self.invoice_id,))
for row in self.cursor.fetchall():
customer_id = row[0]
date = row[1]
self.calendar.set_date(date)
self.builder.get_object('combobox1').set_active_id(str(customer_id))
self.tax = 0
self.window.show_all()
self.calculate_totals ()
GLib.idle_add(self.load_settings)
def load_settings (self):
self.cursor.execute("SELECT column_id, visible FROM settings.invoice_columns")
for row in self.cursor.fetchall():
column_id = row[0]
visible = row[1]
self.builder.get_object(column_id).set_visible(visible)
self.cursor.execute("SELECT print_direct, email_when_possible FROM settings")
print_direct, email = self.cursor.fetchone()
self.builder.get_object('menuitem1').set_active(print_direct) #set the direct print checkbox
self.builder.get_object('menuitem4').set_active(email) #set the email checkbox
def populate_location_store (self):
location_combo = self.builder.get_object('combobox2')
active_location = location_combo.get_active_id()
self.location_store.clear()
self.cursor.execute("SELECT id, name FROM locations ORDER BY name")
for row in self.cursor.fetchall():
location_id = row[0]
location_name = row[1]
self.location_store.append([str(location_id), location_name])
location_combo.set_active_id(active_location)
def transfer_invoice_activated (self, menuitem):
dialog = self.builder.get_object('transfer_invoice_dialog')
result = dialog.run()
dialog.hide()
if result == Gtk.ResponseType.ACCEPT:
combo = self.builder.get_object('combobox3')
transfer_customer_id = combo.get_active_id()
self.cursor.execute("UPDATE invoices SET customer_id = %s "
"WHERE id = %s",
(transfer_customer_id, self.invoice_id))
self.db.commit()
combo = self.builder.get_object('combobox1')
combo.set_active_id(transfer_customer_id)
self.update_invoice_name ("Inv")
def invoice_transfer_match_selected (self, completion, model, iter):
transfer_customer_id = model[iter][0]
combo = self.builder.get_object('combobox3')
combo.set_active_id(transfer_customer_id)
def barcode_entry_key_pressed (self, entry, event):
if event.get_state() & Gdk.ModifierType.SHIFT_MASK: #shift held down
barcode = entry.get_text()
if barcode == "":
return # blank barcode
self.cursor.execute("SELECT id FROM products "
"WHERE (barcode, deleted, sellable, stock) = "
"(%s, False, True, True)", (barcode,))
for row in self.cursor.fetchall():
product_id = row[0]
break
else:
return
keyname = Gdk.keyval_name(event.keyval)
if keyname == 'Return' or keyname == "KP_Enter": # enter key(s)
for row in self.invoice_store:
if row[2] == product_id:
row[1] -= 1
break
def barcode_entry_activate (self, entry):
self.check_invoice_id()
barcode = entry.get_text()
entry.select_region(0,-1)
if barcode == "":
return # blank barcode
self.cursor.execute("SELECT process_invoice_barcode(%s, %s)",
(barcode, self.invoice_id))
for row in self.cursor.fetchall():
if row[0] != 0:
self.populate_invoice_items()
row_id = row[0]
else: #barcode not found
for row in self.barcodes_not_found_store:
if row[2] == barcode:
row[1] += 1
break
continue
else:
self.barcodes_not_found_store.append([0, 1, barcode])
self.builder.get_object('entry10').grab_focus()
barcode_error_dialog = self.builder.get_object('barcode_error_dialog')
barcode_error_dialog.run()
barcode_error_dialog.hide()
return
for row in self.invoice_store: #select the item we scanned
if row[0] == row_id:
treeview = self.builder.get_object('treeview2')
c = treeview.get_column(0)
#path = self.invoice_store.get_path(row.path)
treeview.set_cursor(row.path, c, False)
def import_time_clock_window(self, widget):
self.check_invoice_id ()
from invoice import import_time_clock_entries as itce
self.time_clock_import = itce.ImportGUI(self.db, self.customer_id, self.invoice_id)
def on_drag_data_received(self, widget, drag_context, x,y, data,info, time):
list_ = data.get_text().split(' ')
if len(list_) != 2:
raise Exception("invalid drag data received")
return
if self.customer_id == 0:
return
qty, product_id = list_[0], list_[1]
self.check_invoice_id()
iter_ = self.invoice_store.append([0, qty, int(product_id), '',
'', '', '1', '1', '1', '',
True, '', False])
self.check_invoice_item_id (iter_)
treeview = self.builder.get_object('treeview2')
c = treeview.get_column(0)
path = self.invoice_store.get_path(iter_)
treeview.set_cursor(path, c, True)
self.product_selected (product_id, path)
def destroy(self, window):
self.main.disconnect(self.handler_c_id)
self.main.disconnect(self.handler_p_id)
self.cursor.close()
def focus (self, window, event):
self.populate_location_store ()
if self.invoice_id != None:
self.populate_invoice_items ()
def treeview_button_release_event (self, treeview, event):
if event.button == 3 and self.menu_visible == False:
selection = self.builder.get_object("treeview-selection")
model, path = selection.get_selected_rows ()
cancel_time_import_menuitem = self.builder.get_object("menuitem13")
cancel_time_import_menuitem.set_visible(False)
if path != []:
line_id = model[path][0]
self.cursor.execute("SELECT id FROM time_clock_entries "
"WHERE invoice_line_id = %s LIMIT 1",
(line_id,))
self.time_clock_entries_ids = self.cursor.fetchall()
for row in self.time_clock_entries_ids:
cancel_time_import_menuitem.set_visible(True)
menu = self.builder.get_object('invoice_line_item_menu')
menu.popup(None, None, None, None, event.button, event.time)
menu.show_all()
self.menu_visible = True
else:
self.menu_visible = False
def cancel_time_clock_import_activated (self, menuitem):
for row in self.time_clock_entries_ids:
self.cursor.execute("UPDATE time_clock_entries "
"SET (invoiced, invoice_line_id) = "
"(False, NULL) WHERE id = %s", (row[0],))
self.db.commit()
def refresh_price_activated (self, menuitem):
selection = self.builder.get_object("treeview-selection")
model, path = selection.get_selected_rows ()
if path == []:
return
self.set_product_price (path)
def customer_combo_populate_popup (self, entry, menu):
separator = Gtk.SeparatorMenuItem ()
separator.show ()
menu.prepend(separator)
edit_customer_menu = Gtk.MenuItem.new_with_label("Edit customer")
edit_customer_menu.connect("activate", self.edit_customer_clicked)
edit_customer_menu.show()
menu.prepend(edit_customer_menu)
def edit_customer_clicked (self, menuitem):
if self.customer_id != None:
import contacts
contacts.GUI(self.main, self.customer_id)
def product_hub_activated (self, menuitem):
selection = self.builder.get_object("treeview-selection")
model, path = selection.get_selected_rows ()
if path == []:
return
product_id = model[path][2]
import product_hub
product_hub.ProductHubGUI(self.main, product_id)
def tax_exemption_window (self, widget):
import customer_tax_exemptions as cte
cte.CustomerTaxExemptionsGUI(self.window, self.db, self.customer_id)
self.populate_tax_exemption_combo ()
def product_clicked (self, menuitem):
import products
products.ProductsGUI(self.main)
def document_list_clicked (self, menuitem):
self.show_document_list_window ()
def show_document_list_window (self):
self.populate_document_list ()
pos = self.window.get_position()
x, y = pos[0], pos[1]
x_size = self.window.get_size()[0]
x = int(x) + int(x_size)
window = self.builder.get_object('document_list_window')
window.move(x , int(y))
window.show_all()
def populate_document_list (self):
selection = self.builder.get_object('treeview-selection4')
self.document_list_store.clear()
self.cursor.execute("SELECT id, name, doc_type, active "
"FROM invoices "
"WHERE (customer_id, posted, canceled) = "
"(%s, False, False)", (self.customer_id,))
for row in self.cursor.fetchall():
_id_ = row[0]
name = row[1]
doc_type = row[2]
active = row[3]
self.document_list_store.append ([_id_, name, doc_type, active])
for row in self.document_list_store:
if row[0] == self.invoice_id:
selection.select_path(row.path)
def active_cell_renderer_toggled (self, renderer, path):
active = self.document_list_store[path][3]
invoice_id = self.document_list_store[path][0]
self.document_list_store[path][3] = not active
self.cursor.execute("UPDATE invoices SET active = %s WHERE id = %s",
(not active, invoice_id))
self.db.commit()
def document_window_delete (self, window, event):
window.hide()
return True
def document_list_row_activated (self, treeview, path, column):
self.invoice_id = self.document_list_store[path][0]
self.cursor.execute("SELECT comments FROM invoices WHERE id = %s",
(self.invoice_id,))
comments = self.cursor.fetchone()[0]
self.builder.get_object("comment_buffer").set_text(comments)
self.document_type = self.document_list_store[path][2]
self.populate_invoice_items()
self.cursor.execute("SELECT dated_for FROM invoices WHERE id = %s", (self.invoice_id,))
date = self.cursor.fetchone()[0]
self.calendar.set_date(date)
def update_invoice_name (self, document_prefix):
self.cursor.execute("SELECT name FROM contacts WHERE id = %s", (self.customer_id,))
name = self.cursor.fetchone()[0]
split_name = name.split(' ')
name_str = ""
for i in split_name:
name_str += i[0:3]
name = name_str.lower()
invoice_date = str(self.datetime)[0:10]
doc_prefix = document_prefix[0:3]
doc_name = doc_prefix + "_" + str(self.invoice_id) + "_" + name + "_" + invoice_date
self.cursor.execute("UPDATE invoices SET name = %s WHERE id = %s", (doc_name, self.invoice_id))
self.db.commit()
def document_type_edited(self, cell_renderer, path, text):
self.document_type = text
self.update_invoice_name (text)
self.cursor.execute("UPDATE invoices SET doc_type = %s WHERE id = %s", (text, self.invoice_id))
self.populate_document_list()
def contacts_window(self, widget):
import contacts
contacts.GUI(self.main)
def delete_invoice_clicked (self, button):
self.cursor.execute("UPDATE invoices SET canceled = True "
"WHERE id = %s", (self.invoice_id,))
self.db.commit()
self.populate_document_list ()
self.invoice_store.clear()
def new_invoice_clicked (self, button):
self.invoice_id = 0
self.invoice_store.clear()
def comment_textbuffer_changed (self, buf):
start = buf.get_start_iter()
end = buf.get_end_iter()
comment = buf.get_text(start, end, True)
self.cursor.execute("UPDATE invoices SET comments = %s WHERE id = %s",
(comment, self.invoice_id))
self.db.commit()
self.invoice = None #comments changed, recreate odt file
def view_invoice(self, widget):
buf = self.builder.get_object('comment_buffer')
start = buf.get_start_iter()
end = buf.get_end_iter()
comment = buf.get_text(start, end, True)
if not self.invoice:
self.invoice = invoice_create.Setup(self.db, self.invoice_store,
self.customer_id, comment,
self.datetime, self.invoice_id,
self, self.document_type)
self.invoice.view()
def post_invoice(self, widget):
buf = self.builder.get_object('comment_buffer')
start = buf.get_start_iter()
end = buf.get_end_iter()
comment = buf.get_text(start, end, True)
if not self.invoice:
self.invoice = invoice_create.Setup(self.db, self.invoice_store,
self.customer_id, comment,
self.datetime, self.invoice_id,
self, self.document_type)
else:
self.invoice.save()
if os.path.exists(self.invoice.lock_file):
dialog = self.builder.get_object('dialog1')
response = dialog.run()
dialog.hide()
if response != Gtk.ResponseType.ACCEPT:
return
if self.builder.get_object('menuitem1').get_active() == True:
self.invoice.print_directly(self.window)
else:
self.invoice.print_dialog(self.window)
self.invoice.post()
if self.builder.get_object('menuitem4').get_active() == True:
self.cursor.execute("SELECT * FROM contacts WHERE id = %s",
(self.customer_id, ))
for row in self.cursor.fetchall():
name = row[1]
email = row[9]
if email != "":
email = "%s '< %s >'" % (name, email)
self.invoice.email(email)
location_id = self.builder.get_object('combobox2').get_active_id()
from inventory import inventorying
inventorying.sell(self.db, self.invoice_store, location_id, self.customer_id, self.datetime)
self.db.commit()
self.window.destroy()
def populate_invoice_items (self):
self.invoice_store.clear()
self.cursor.execute("SELECT ili.id, qty, product_id, products.name, "
"ext_name, remark, price::text, tax::text, ext_price::text, "
"ili.tax_rate_id, COALESCE(tax_letter, ''), "
"products.invoice_serial_numbers "
"FROM invoice_items AS ili "
"JOIN products ON products.id = ili.product_id "
"LEFT JOIN tax_rates "
"ON tax_rates.id = ili.tax_rate_id "
"WHERE (invoice_id, canceled) = (%s, False) "
"ORDER BY ili.id", (self.invoice_id,))
for row in self.cursor.fetchall():
id = row[0]
qty = row[1]
product_id = row[2]
product_name = row[3]
ext_name = row[4]
remark = row[5]
price = row[6]
tax = row[7]
ext_price = row[8]
tax_rate_id = str(row[9])
tax_letter = row[10]
serial_number = row[11]
if serial_number == True:
self.builder.get_object('treeviewcolumn13').set_visible(True)
qty = int(qty)
self.invoice_store.append([id, str(qty), product_id, product_name,
ext_name, remark, price, tax,
ext_price, tax_rate_id, False,
tax_letter, serial_number])
self.check_serial_numbers()
def tax_exemption_combo_changed(self, combo):
'''if tax_rate_id is NULL, trigger will use default tax rate'''
if self.populating == True:
return
tax_rate_id = combo.get_active_id()
self.cursor.execute("UPDATE invoice_items SET tax_rate_id = %s "
"WHERE invoice_id = %s",
(tax_rate_id, self.invoice_id))
self.db.commit()
self.populate_invoice_items ()
self.calculate_totals ()
################## start customer
def populate_customer_store (self, m=None, i=None):
self.customer_store.clear()
self.cursor.execute("SELECT "
"id::text, "
"name || ' Unpaid : ' || "
"(( SELECT COALESCE(SUM(amount_due), 0.00) "
"FROM invoices "
"WHERE canceled = False "
"AND customer_id = c_outer.id) - "
"(SELECT COALESCE(SUM(amount), 0.00) "
"FROM payments_incoming "
"WHERE (customer_id, misc_income) = "
"(c_outer.id, False) ))::money, "
"ext_name "
"FROM contacts AS c_outer "
"WHERE (deleted, customer) = "
"(False, True) ORDER BY name")
for row in self.cursor.fetchall():
self.customer_store.append(row)
def customer_match_selected(self, completion, model, iter):
self.customer_id = model[iter][0]
self.customer_selected (self.customer_id)
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
return True
def customer_combobox_changed(self, widget, toggle_button=None): #updates the customer
customer_id = widget.get_active_id()
if customer_id != None:
self.customer_id = customer_id
self.customer_selected (self.customer_id)
self.calculate_totals ()
def populate_tax_exemption_combo (self):
self.populating = True
exemption_combo = self.builder.get_object('comboboxtext1')
active = exemption_combo.get_active_id()
exemption_combo.remove_all()
exemption_combo.append(None, "No exemption")
final_id = '0'
self.cursor.execute("SELECT tax_rates.id, tax_rates.name FROM "
"customer_tax_exemptions "
"JOIN tax_rates "
"ON customer_tax_exemptions.tax_rate_id = "
"tax_rates.id "
"WHERE customer_tax_exemptions.customer_id = (%s)",
(self.customer_id,))
for row in self.cursor.fetchall():
exemption_combo.append(str(row[0]), row[1])
final_id = str(row[0])
if active == None:
self.populating = False
exemption_combo.set_active_id(final_id)
return
exemption_combo.set_active_id(active)
self.populating = False
def customer_selected(self, name_id):
self.cursor.execute("SELECT address, phone, city, state, zip FROM contacts "
"WHERE id = (%s)",(name_id,))
for row in self.cursor.fetchall() :
self.builder.get_object('entry6').set_text(row[0])
self.builder.get_object('entry8').set_text(row[1])
self.builder.get_object('entry15').set_text(row[2])
self.builder.get_object('entry16').set_text(row[3])
self.builder.get_object('entry17').set_text(row[4])
self.populate_tax_exemption_combo ()
self.set_widgets_sensitive ()
self.cursor.execute("SELECT id, comments FROM invoices "
"WHERE (customer_id, posted) = (%s, False) ",
(name_id,))
tupl = self.cursor.fetchall()
if len(tupl) > 1:
self.invoice_id = 0
self.show_document_list_window ()
self.builder.get_object("comment_buffer").set_text('')
elif len(tupl) == 1:
self.invoice_id = tupl[0][0]
comments = tupl[0][1]
self.builder.get_object("comment_buffer").set_text(comments)
else:
self.invoice_id = 0
self.builder.get_object("comment_buffer").set_text('')
self.populate_invoice_items()
self.cursor.execute("SELECT dated_for FROM invoices WHERE id = %s "
"AND dated_for IS NOT NULL", (self.invoice_id,))
for row in self.cursor.fetchall():
date = row[0]
self.calendar.set_date(date)
break
else:
self.calendar.set_today()
################## start qty
def qty_cell_func(self, column, cellrenderer, model, iter_, data):
return
if model.get_value(iter_, 12) == True:
qty = int(model.get_value(iter_, 1))
cellrenderer.set_property("text" , str(qty))
def qty_edited(self, widget, path, text):
if self.invoice_store[path][12] == True:
text = int(text) # only allow whole numbers for inventory
iter_ = self.invoice_store.get_iter (path)
self.check_invoice_item_id (iter_)
line_id = self.invoice_store[iter_][0]
try:
self.cursor.execute("UPDATE invoice_items SET qty = %s "
"WHERE id = %s;"
"SELECT qty::text, price::text, "
"ext_price::text, tax::text "
"FROM invoice_items WHERE id = %s",
(text, line_id, line_id))
self.db.commit()
except psycopg2.DataError as e:
self.show_error_dialog (str(e))
self.db.rollback()
return
for row in self.cursor.fetchall():
qty = row[0]
price = row[1]
ext_price = row[2]
tax = row[3]
self.invoice_store[iter_][1] = qty
self.invoice_store[iter_][6] = price
self.invoice_store[iter_][7] = tax
self.invoice_store[iter_][8] = ext_price
self.check_serial_numbers ()
self.calculate_totals ()
################## start remark
def remark_edited(self, widget, path, text):
iter_ = self.invoice_store.get_iter(path)
self.invoice_store[iter_][5] = text
line_id = self.invoice_store[iter_][0]
self.cursor.execute("UPDATE invoice_items SET remark = %s "
"WHERE id = %s", (text, line_id))
self.db.commit()
################## start price
def price_cell_func(self, column, cellrenderer, model, iter_, data):
return
price = '{:,.2f}'.format(model.get_value(iter_, 6))
cellrenderer.set_property("text" , price)
def price_edited(self, widget, path, text):
iter_ = self.invoice_store.get_iter(path)
line_id = self.invoice_store[iter_][0]
try:
self.cursor.execute("UPDATE invoice_items SET price = %s "
"WHERE id = %s;"
"SELECT price::text, ext_price::text, tax::text "
"FROM invoice_items WHERE id = %s",
(text, line_id, line_id))
self.db.commit()
except psycopg2.DataError as e:
self.show_error_dialog (str(e))
self.db.rollback()
return
for row in self.cursor.fetchall():
price = row[0]
ext_price = row[1]
tax = row[2]
self.invoice_store[iter_][6] = price
self.invoice_store[iter_][7] = tax
self.invoice_store[iter_][8] = ext_price
self.calculate_totals()
################## end price
def tax_cell_func(self, column, cellrenderer, model, iter_, data):
return
tax = '{:,.2f}'.format(model.get_value(iter_, 7))
cellrenderer.set_property("text" , tax)
def ext_price_cell_func(self, column, cellrenderer, model, iter_, data):
return
ext_price = '{:,.2f}'.format(model.get_value(iter_, 8))
cellrenderer.set_property("text" , ext_price)
################## start product
def product_match_func(self, completion, key, tree_iter):
split_search_text = key.split()
for text in split_search_text:
if text not in self.product_store[tree_iter][1].lower():
return False
return True
def product_match_selected(self, completion, model, iter_):
product_id = self.product_store[iter_][0]
selection = self.builder.get_object('treeview-selection')
model, path = selection.get_selected_rows()
self.product_selected (product_id, path)
def product_renderer_started (self, renderer, combo, path):
completion = self.builder.get_object('product_completion')
entry = combo.get_child()
entry.set_completion(completion)
def product_renderer_changed (self, widget, path, iter_):
product_id = self.product_store[iter_][0]
self.product_selected (product_id, path)
def product_selected (self, product_id, path):
invoice_line_id = self.invoice_store[path][0]
self.cursor.execute("UPDATE serial_numbers "
"SET invoice_item_id = NULL "
"WHERE invoice_item_id = %s",
(invoice_line_id,))
self.cursor.execute("SELECT products.name, ext_name, tax_letter, "
"invoice_serial_numbers "
"FROM products JOIN tax_rates "
"ON tax_rates.id = products.tax_rate_id "
"WHERE products.id = %s", (product_id,))
for row in self.cursor.fetchall ():
product_name = row[0]
ext_name = row[1]
tax_letter = row[2]
serial_number = row[3]
iter_ = self.invoice_store.get_iter(path)
if serial_number == True:
self.builder.get_object('treeviewcolumn13').set_visible(True)
#allow only whole numbers for inventory
qty = self.invoice_store[iter_][1].split('.')[0]
self.invoice_store[iter_][1] = qty
self.invoice_store[iter_][2] = int(product_id)
self.invoice_store[iter_][3] = product_name
self.invoice_store[iter_][4] = ext_name
self.invoice_store[iter_][10] = False
self.invoice_store[iter_][11] = tax_letter
self.invoice_store[iter_][12] = serial_number
self.set_product_price (iter_)
line_id = self.invoice_store[iter_][0]
self.cursor.execute("UPDATE invoice_items SET product_id = %s "
"WHERE id = %s;"
"SELECT price::text, tax::text, ext_price::text "
"FROM invoice_items WHERE id = %s",
(product_id, line_id, line_id))
for row in self.cursor.fetchall():
price = row[0]
tax = row[1]
ext_price = row[2]
self.invoice_store[iter_][6] = price
self.invoice_store[iter_][7] = tax
self.invoice_store[iter_][8] = ext_price
self.db.commit()
self.set_serial_number_box_state(serial_number)
self.check_serial_numbers ()
self.populate_serial_numbers ()
self.calculate_totals()
def populate_serial_numbers (self):
serial_number_store = self.builder.get_object('serial_number_store')
serial_number_store.clear()
selection = self.builder.get_object('treeview-selection')
model, path = selection.get_selected_rows()
if path == []:
return # no row selected
invoice_line_id = model[path][0]
product_id = model[path][2]
product_name = model[path][3]
self.cursor.execute("SELECT id, serial_number FROM serial_numbers "
"WHERE invoice_item_id = %s",
(invoice_line_id,))
for row in self.cursor.fetchall():
id_ = row[0]
serial_number = row[1]
serial_number_store.append([product_id, invoice_line_id, product_name, serial_number, id_])
def check_serial_numbers (self):
mismatch = False
box = self.builder.get_object('box4')
for row in self.invoice_store:
if row[12] == True:
box.set_visible(True)
invoice_line_id = row[0]
qty = int(row[1])
product_id = row[2]
product_name = row[3]
self.cursor.execute("SELECT COUNT(id) FROM serial_numbers "
"WHERE invoice_item_id = %s",
(invoice_line_id,))
ser_qty = self.cursor.fetchone()[0]
if qty != ser_qty:
mismatch = True
break
button = self.builder.get_object('button2')
if mismatch == True:
button.set_label('Qty/serial number mismatch')
button.set_sensitive(False)
else:
button.set_label('Post invoice')
button.set_sensitive(True)
def invoice_item_row_activated (self, treeview, path, column):
store = treeview.get_model()
invoice_item_id = store[path][0]
if invoice_item_id == 0:
return # no valid invoice item yet
enforce_serial_numbers = store[path][12]
self.set_serial_number_box_state(enforce_serial_numbers)
product_id = store[path][2]
self.check_serial_numbers()
self.populate_serial_numbers ()
def set_serial_number_box_state (self, sensitive):
box = self.builder.get_object('box4')
box.set_sensitive(sensitive)
def serial_number_entry_activated (self, entry):
serial_number = entry.get_text()
item_selection = self.builder.get_object('treeview-selection')
model, path = item_selection.get_selected_rows()
if path == []:
self.show_error_dialog ("No invoice item selected!")
return # no invoice item selected
invoice_line_id = model[path][0]
product_id = model[path][2]
self.cursor.execute("SELECT c.name, i.id FROM serial_numbers AS sn "
"JOIN invoice_items AS ili "
"ON ili.id = sn.invoice_item_id "
"JOIN invoices AS i ON i.id = ili.invoice_id "
"JOIN contacts AS c ON c.id = i.customer_id "
"WHERE (sn.product_id, sn.serial_number) = (%s, %s) ",
(product_id, serial_number))
for row in self.cursor.fetchall():
customer_name = row[0]
invoice_number = row[1]
error = "Serial number %s is in use on invoice number %s, "\
"customer name %s.\n Most likely you entered the serial number "\
"twice or maybe the serial number\n got entered wrong now, or "\
"sometime before today."\
%(serial_number, invoice_number, customer_name)
self.show_error_dialog(error)
return # serial number is in use
self.cursor.execute("UPDATE serial_numbers "
"SET invoice_item_id = %s "
"WHERE (product_id, serial_number) = (%s, %s) "
"AND invoice_item_id IS NULL RETURNING id",
(invoice_line_id, product_id, serial_number ))
for row in self.cursor.fetchall():
product_serial_number_id = row[0]
break
else:
error = ("That serial number was not found in the system.\n "
"This means it was not manufactured or received yet.\n "
"If you want to add it manually, you can go to\n "
"General>Serial Numbers on the main window.")
self.show_error_dialog(error)
return # no serial number found in the system
self.db.commit()
self.check_serial_numbers ()
self.populate_serial_numbers ()
entry.select_region(0, -1)
entry.grab_focus()
def show_error_dialog (self, error):
self.builder.get_object('label2').set_label(error)
dialog = self.builder.get_object('error_dialog')
dialog.run()
dialog.hide()
def serial_number_treeview_row_activated (self, treeview, path, column):
entry = self.builder.get_object('entry11')
entry.select_region(0, -1)
entry.grab_focus()
def remove_serial_number_clicked (self, button):
selection = self.builder.get_object('treeview-selection5')
model, path = selection.get_selected_rows()
if path != []:
product_serial_number_id = model[path][4]
self.cursor.execute("UPDATE serial_numbers "
"SET invoice_item_id = NULL "
"WHERE id = %s", (product_serial_number_id,))
self.db.commit()
self.check_serial_numbers ()
self.populate_serial_numbers ()
def populate_product_store(self, m=None, i=None):
self.product_store.clear()
self.cursor.execute("SELECT id, name, ext_name FROM products "
"WHERE (deleted, sellable) = (False, True) "
"ORDER BY name")
for i in self.cursor.fetchall():
_id_ = i[0]
name = i[1]
ext_name = i[2]
self.product_store.append([str(_id_), "%s {%s}" % (name, ext_name)])
def set_product_price (self, iter_):
product_id = self.invoice_store[iter_][2]
price = get_customer_product_price (self.db,
self.customer_id, product_id)
self.invoice_store[iter_][6] = str(price)
line_id = self.invoice_store[iter_][0]
self.cursor.execute("UPDATE invoice_items SET price = %s "
"WHERE id = %s", (price, line_id))
self.db.commit()
################## end product
def set_widgets_sensitive (self):
self.builder.get_object('button1').set_sensitive(True)
self.builder.get_object('button2').set_sensitive(True)
self.builder.get_object('button3').set_sensitive(True)
self.builder.get_object('menuitem14').set_sensitive(True)
self.builder.get_object('entry9').set_sensitive(True)
self.builder.get_object('menuitem2').set_sensitive(True)
self.builder.get_object('menuitem6').set_sensitive(True)
self.builder.get_object('menuitem7').set_sensitive(True)
self.builder.get_object('menuitem8').set_sensitive(True)
self.builder.get_object('comment_textview').set_sensitive(True)
def refresh_all_prices_clicked (self, menuitem):
for row in self.invoice_store:
self.set_product_price (row.path)
def calculate_totals (self, widget = None):
c = self.db.cursor()
c.execute("WITH totals AS "
"(SELECT COALESCE(SUM(ext_price), 0.00) AS subtotal, "
"COALESCE(SUM(tax), 0.00) AS tax, "
"(COALESCE(SUM(ext_price) + SUM(tax), 0.00)) "
"AS total FROM invoice_items "
"WHERE invoice_id = %s"
"),"
"update AS "
"(UPDATE invoices SET (subtotal, tax, total) = "
"((SELECT subtotal FROM totals), "
"(SELECT tax FROM totals), "
"(SELECT total FROM totals)) "
"WHERE id = %s"
")"
"SELECT * FROM totals",
(self.invoice_id, self.invoice_id))
for row in c.fetchall():
self.subtotal = row[0]
self.tax = row[1]
self.total = row[2]
self.db.commit()
subtotal = '${:,.2f}'.format(self.subtotal)
tax = '${:,.2f}'.format(self.tax)
total = '${:,.2f}'.format(self.total)
self.builder.get_object('entry3').set_text(subtotal)
self.builder.get_object('entry4').set_text(tax)
self.builder.get_object('entry5').set_text(total)
def check_invoice_item_id (self, iter_):
id = self.invoice_store[iter_][0]
qty = self.invoice_store[iter_][1]
product_id = self.invoice_store[iter_][2]
remark = self.invoice_store[iter_] [5]
price = self.invoice_store[iter_][6]
tax = self.invoice_store[iter_][7]
ext_price = self.invoice_store[iter_][8]
tax_id = self.builder.get_object('comboboxtext1').get_active_id()
if id == 0:
self.check_invoice_id()
self.cursor.execute("INSERT INTO invoice_items "
"(invoice_id, qty, product_id, remark, price, "
"canceled, tax_rate_id) "
"VALUES (%s, %s, %s, %s, %s, %s, %s) "
"RETURNING id",
(self.invoice_id, qty, product_id, remark,
price, False, tax_id))
self.invoice_store[iter_][0] = self.cursor.fetchone()[0]
self.db.commit()
self.invoice = None #the generated .odt is no longer valid
def check_invoice_id (self):
if self.invoice_id == 0:
self.invoice_id = create_new_invoice(self.cursor, self.datetime, self.customer_id)
self.db.commit()
self.populate_document_list()
def new_item_clicked (self, button):
self.cursor.execute("SELECT id, name "
"FROM products "
"WHERE (deleted, sellable, stock) = "
"(False, True, True) "
"ORDER BY invoice_serial_numbers ASC, id ASC "
"LIMIT 1")
for i in self.cursor.fetchall():
product_id = i[0]
product_name = i[1]
iter_ = self.invoice_store.append([0, '1', product_id, product_name,
"", "", '1', '1', '1', "",
True, '', False])
self.check_invoice_item_id (iter_)
treeview = self.builder.get_object('treeview2')
c = treeview.get_column(0)
path = self.invoice_store.get_path(iter_)
treeview.set_cursor(path, c, True)
self.set_serial_number_box_state(False)
def delete_line_item_activated (self, menuitem):
selection = self.builder.get_object("treeview-selection")
model, path = selection.get_selected_rows ()
if path == []:
return
row_id = model[path][0]
self.cursor.execute("WITH deleted AS "
"(DELETE FROM invoice_items WHERE id = %s "
"RETURNING gl_entries_id) "
"DELETE FROM gl_entries WHERE id = "
"(SELECT gl_entries_id FROM deleted)"
, (row_id,))
self.db.commit()
self.populate_invoice_items ()
def key_tree_tab(self, treeview, event):
keyname = Gdk.keyval_name(event.keyval)
path, col = treeview.get_cursor()
# only visible columns!!
columns = [c for c in treeview.get_columns() if c.get_visible()]
colnum = columns.index(col)
if keyname=="Tab" or keyname=="Esc":
if colnum + 1 < len(columns):
next_column = columns[colnum + 1]
else:
tmodel = treeview.get_model()
titer = tmodel.iter_next(tmodel.get_iter(path))
if titer is None:
titer = tmodel.get_iter_first()
path = tmodel.get_path(titer)
next_column = columns[0]
if keyname == 'Tab':
GLib.timeout_add(10, treeview.set_cursor, path, next_column, True)
elif keyname == 'Escape':
pass
def help_clicked (self, widget):
import subprocess
subprocess.Popen(["yelp", main.help_dir + "/invoice.page"])
def window_key_event(self, window, event):
keyname = Gdk.keyval_name(event.keyval)
if keyname == 'F1':
self.help_clicked (None)
if keyname == 'F2':
self.new_item_clicked (None)
if keyname == 'F3':
self.delete_entry(None)
def calendar_day_selected (self, calendar):
self.datetime = calendar.get_date()
day_text = calendar.get_text()
self.builder.get_object('entry1').set_text(day_text)
def calendar(self, widget, icon, event):
self.calendar.show()
class SalesTaxReportGUI:
def __init__(self):
self.builder = Gtk.Builder()
self.builder.add_from_file(UI_FILE)
self.builder.connect_signals(self)
self.cursor = DB.cursor()
self.tax_store = self.builder.get_object('tax_store')
self.end_datetime = None
start_entry = self.builder.get_object('entry1')
end_entry = self.builder.get_object('entry2')
self.start_calendar = DateTimeCalendar()
self.start_calendar.set_relative_to(start_entry)
self.end_calendar = DateTimeCalendar()
self.end_calendar.set_relative_to(end_entry)
self.start_calendar.connect('day-selected', self.start_date_selected)
self.end_calendar.connect('day-selected', self.end_date_selected)
self.start_calendar.set_date(datetime.today() - timedelta(days = 365))
self.end_calendar.set_today ()
self.window = self.builder.get_object('window1')
self.window.show_all()
def destroy (self, widget):
self.cursor.close()
def populate_tax_treeview(self):
self.tax_store.clear()
self.cursor.execute("SELECT tax_rates.name, "
"SUM(i.tax)::money, "
"SUM(i.ext_price)::money "
"FROM invoice_items AS i "
"JOIN tax_rates ON i.tax_rate_id = tax_rates.id "
"JOIN invoices ON invoices.id = i.invoice_id "
"WHERE (invoices.canceled, invoices.posted) "
"= (False, True) "
"AND date_created >= %s "
"AND date_created <= %s "
"GROUP BY tax_rates.name",
(self.start_datetime, self.end_datetime))
for row in self.cursor.fetchall():
self.tax_store.append(row)
self.cursor.execute("SELECT "
"COALESCE(SUM(i.ext_price), 0.00)::money, "
"COALESCE(SUM(i.tax), 0.00)::money "
"FROM invoice_items AS i "
"JOIN invoices ON invoices.id = i.invoice_id "
"WHERE (invoices.canceled, invoices.posted) "
"= (False, True) "
"AND date_created >= %s "
"AND date_created <= %s",
(self.start_datetime, self.end_datetime))
for row in self.cursor.fetchall():
self.builder.get_object('label6').set_label(row[0])
self.builder.get_object('label4').set_label(row[1])
DB.rollback()
def start_date_selected(self, calendar):
self.start_datetime = calendar.get_datetime()
day_text = calendar.get_text()
self.builder.get_object('entry1').set_text(day_text)
if self.end_datetime != None: #end date not available before start date
self.populate_tax_treeview ()
def end_date_selected (self, calendar):
self.end_datetime = calendar.get_datetime()
day_text = calendar.get_text()
self.builder.get_object('entry2').set_text(day_text)
self.populate_tax_treeview ()
def start_icon_clicked (self, entry, icon, event):
self.start_calendar.show()
def end_icon_clicked (self, entry, icon, event):
self.end_calendar.show()
class SalesTaxReportGUI:
def __init__(self, db):
self.builder = Gtk.Builder()
self.builder.add_from_file(UI_FILE)
self.builder.connect_signals(self)
self.db = db
self.cursor = self.db.cursor()
self.tax_store = self.builder.get_object('tax_store')
self.end_datetime = None
start_entry = self.builder.get_object('entry1')
end_entry = self.builder.get_object('entry2')
self.start_calendar = DateTimeCalendar()
self.start_calendar.set_relative_to(start_entry)
self.end_calendar = DateTimeCalendar()
self.end_calendar.set_relative_to(end_entry)
self.start_calendar.connect('day-selected', self.start_date_selected)
self.end_calendar.connect('day-selected', self.end_date_selected)
self.start_calendar.set_date(datetime.today() - timedelta(days=365))
self.end_calendar.set_today()
tax_column = self.builder.get_object('treeviewcolumn2')
tax_renderer = self.builder.get_object('cellrenderertext2')
tax_column.set_cell_data_func(tax_renderer, self.tax_cell_func)
total_column = self.builder.get_object('treeviewcolumn3')
total_renderer = self.builder.get_object('cellrenderertext3')
total_column.set_cell_data_func(total_renderer, self.total_cell_func)
self.window = self.builder.get_object('window1')
self.window.show_all()
def populate_tax_treeview(self):
self.tax_store.clear()
self.cursor.execute(
"SELECT SUM(i.ext_price), SUM(i.tax), "
"tax_rates.name "
"FROM invoice_items AS i "
"JOIN tax_rates ON i.tax_rate_id = tax_rates.id "
"JOIN invoices ON invoices.id = i.invoice_id "
"WHERE (invoices.canceled, invoices.posted) "
"= (False, True) "
"AND date_created >= %s "
"AND date_created <= %s "
"GROUP BY tax_rates.name",
(self.start_datetime, self.end_datetime))
for row in self.cursor.fetchall():
sale_amount = row[0]
tax_amount = row[1]
tax_rate_name = row[2]
self.tax_store.append(
[tax_rate_name,
float(tax_amount),
float(sale_amount)])
self.cursor.execute(
"SELECT "
"COALESCE(SUM(i.ext_price), 0.00), "
"COALESCE(SUM(i.tax), 0.00) "
"FROM invoice_items AS i "
"JOIN invoices ON invoices.id = i.invoice_id "
"WHERE (invoices.canceled, invoices.posted) "
"= (False, True) "
"AND date_created >= %s "
"AND date_created <= %s", (self.start_datetime, self.end_datetime))
for row in self.cursor.fetchall():
sale_total = '${:,.2f}'.format(row[0])
tax_total = '${:,.2f}'.format(row[1])
self.builder.get_object('label4').set_label(tax_total)
self.builder.get_object('label6').set_label(sale_total)
def tax_cell_func(self, column, cellrenderer, model, iter1, data):
tax = '${:,.2f}'.format(model.get_value(iter1, 1))
cellrenderer.set_property("text", tax)
def total_cell_func(self, column, cellrenderer, model, iter1, data):
total = '${:,.2f}'.format(model.get_value(iter1, 2))
cellrenderer.set_property("text", total)
def start_date_selected(self, calendar):
self.start_datetime = calendar.get_datetime()
day_text = calendar.get_text()
self.builder.get_object('entry1').set_text(day_text)
if self.end_datetime != None: #end date not available before start date
self.populate_tax_treeview()
def end_date_selected(self, calendar):
self.end_datetime = calendar.get_datetime()
day_text = calendar.get_text()
self.builder.get_object('entry2').set_text(day_text)
self.populate_tax_treeview()
def start_icon_clicked(self, entry, icon, event):
self.start_calendar.show()
def end_icon_clicked(self, entry, icon, event):
self.end_calendar.show()
