def window_destroy(self, widget):
self.parent.populate_projects()
self.cursor.execute(
"SELECT pg_advisory_unlock(%s) "
"FROM serial_numbers", (self.manufacturing_id, ))
DB.commit()
self.cursor.close()
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 post_employee_payment_clicked (self, button):
actual_hours = self.actual_seconds / 3600
adjusted_hours = self.adjusted_seconds / 3600
cost_sharing_hours = self.cost_sharing_seconds / 3600
profit_sharing_hours = self.profit_sharing_seconds / 3600
wage = self.builder.get_object('spinbutton1').get_value()
self.cursor.execute ("INSERT INTO pay_stubs "
"(employee_id, date_inserted, regular_hours, "
"cost_sharing_hours, profit_sharing_hours, "
"hourly_wage) "
"VALUES (%s, %s, %s, %s, %s, %s) RETURNING id",
(self.employee_id, datetime.today(), adjusted_hours,
cost_sharing_hours, profit_sharing_hours, wage))
self.pay_stub_id = self.cursor.fetchone()[0]
self.view_report ()
subprocess.call('odt2pdf ' + self.time_file, shell = True)
f = open(self.time_file_pdf,'rb')
data = f.read()
binary = psycopg2.Binary(data)
f.close()
self.cursor.execute("UPDATE pay_stubs SET pdf_data = %s WHERE id = %s",
(binary, self.pay_stub_id))
for row in self.time_clock_entries_ids:
row_id = row[0]
self.cursor.execute("UPDATE time_clock_entries SET "
"(employee_paid, pay_stub_id) = "
"(True, %s) WHERE id = %s",
(self.pay_stub_id, row_id))
DB.commit()
self.populate_employees ()
self.builder.get_object('button1').set_sensitive(False)
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 save_row_ordering(self):
for row_count, row in enumerate(self.resource_store):
row_id = row[0]
self.cursor.execute(
"UPDATE resources "
"SET sort = %s WHERE id = %s", (row_count, row_id))
DB.commit()
def post_project_clicked(self, button):
selection = self.get_object('treeview-selection')
model, path = selection.get_selected_rows()
if path == []:
return
project_id = model[path][0]
project_qty = model[path][3]
serial_qty = model[path][5]
cursor = DB.cursor()
if serial_qty < project_qty:
cursor.execute(
"SELECT invoice_serial_numbers FROM products AS p "
"JOIN manufacturing_projects AS mp "
"ON mp.product_id = p.id "
"WHERE mp.id = %s", (project_id, ))
if cursor.fetchone()[0] == True:
self.show_message("Missing serial numbers!")
cursor.close()
DB.rollback()
return
cursor.execute(
"UPDATE time_clock_projects "
"SET (active, stop_date) = "
"(False, CURRENT_DATE) "
"WHERE id = "
"(SELECT time_clock_projects_id "
"FROM manufacturing_projects WHERE id = %s);"
"UPDATE manufacturing_projects "
"SET active = False WHERE id = %s", (project_id, project_id))
DB.commit()
cursor.close()
self.populate_projects()
def name_edited (self, renderer, path, text):
model = self.get_object('liststore1')
row_id = model[path][0]
self.cursor.execute("UPDATE mailing_lists SET name = %s WHERE id = %s",
(text, row_id))
DB.commit()
model[path][1] = text
def date_edited (self, cellrenderertext, path, text):
if self.get_object('edit_mode_checkbutton').get_active() == False:
return
selection = self.get_object('incoming_invoices_tree_selection')
model, path = selection.get_selected_rows()
if path == []:
return
row_id = model[path][0]
c = DB.cursor()
try:
c.execute("WITH update_ii AS (UPDATE incoming_invoices "
"SET date_created = %s WHERE id = %s "
"RETURNING gl_transaction_id), "
"update_ge AS (UPDATE gl_entries SET date_inserted = %s "
"WHERE gl_transaction_id = "
"(SELECT gl_transaction_id FROM update_ii)) "
"UPDATE gl_transactions SET date_inserted = %s "
"WHERE id = "
"(SELECT gl_transaction_id FROM update_ii)",
(text, row_id, text, text))
except psycopg2.DataError as e:
DB.rollback()
self.show_error_dialog(str(e))
return
DB.commit()
self.populate_incoming_invoice_store()
def date_renderer_edited(self, renderer, path, text):
if not self.get_object('edit_mode_checkbutton').get_active():
return
store = self.get_object('statement_store')
c = DB.cursor()
row_id = store[path][0]
try:
c.execute(
"UPDATE gl_entries "
"SET date_inserted = %s WHERE id = %s "
"RETURNING date_inserted::text, "
"format_date(date_inserted)", (text, row_id))
except psycopg2.DataError as e:
DB.rollback()
print(e)
self.get_object('label10').set_label(str(e))
dialog = self.get_object('dialog2')
dialog.run()
dialog.hide()
return
DB.commit()
for row in c.fetchall():
date_sorted = row[0]
date_formatted = row[1]
store[path][1] = date_sorted
store[path][2] = date_formatted
def edit_account_clicked(self, menuitem):
c = DB.cursor()
c.execute(
"SELECT number, parent_number FROM gl_accounts "
"WHERE number = %s", (self.active_account_number, ))
for row in c.fetchall():
if row[1] == None:
return #do not allow changing top level accounts
account_number = row[0]
parent_number = row[1]
self.populate_parent_account_store()
self.builder.get_object('spinbutton3').set_value(account_number)
self.builder.get_object('combobox1').set_active_id(str(parent_number))
dialog = self.builder.get_object('dialog3')
result = dialog.run()
dialog.hide()
if result == Gtk.ResponseType.ACCEPT:
new_number = self.builder.get_object('spinbutton3').get_value()
parent_number = self.builder.get_object(
'combobox1').get_active_id()
c.execute(
"UPDATE gl_accounts SET (number, parent_number) "
"= (%s, %s) WHERE number = %s",
(new_number, parent_number, account_number))
DB.commit()
self.populate_account_treestore()
else:
DB.rollback()
c.close()
def p_o_name_edited(self, cellrenderertext, path, text):
po_id = self.open_po_store[path][0]
self.cursor.execute(
"UPDATE purchase_orders SET name = %s "
"WHERE id = %s", (text, po_id))
DB.commit()
self.open_po_store[path][1] = text
def add_document_type_clicked(self, widget):
document_type_entry = self.builder.get_object('entry2')
document_type_name = document_type_entry.get_text()
text1 = self.builder.get_object('entry3').get_text()
text2 = self.builder.get_object('entry4').get_text()
text3 = self.builder.get_object('entry5').get_text()
text4 = self.builder.get_object('entry6').get_text()
text5 = self.builder.get_object('entry26').get_text()
text6 = self.builder.get_object('entry27').get_text()
text7 = self.builder.get_object('entry28').get_text()
text8 = self.builder.get_object('entry29').get_text()
text9 = self.builder.get_object('entry30').get_text()
text10 = self.builder.get_object('entry31').get_text()
text11 = self.builder.get_object('entry32').get_text()
text12 = self.builder.get_object('entry33').get_text()
self.cursor.execute(
"INSERT INTO document_types "
"(name, text1, text2, text3, text4, text5, text6, "
"text7, text8, text9, text10, text11, text12) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, "
"%s, %s, %s, %s)",
(document_type_name, text1, text2, text3, text4, text5, text6,
text7, text8, text9, text10, text11, text12))
DB.commit()
self.populate_document_types()
document_type_entry.set_text("")
def process_job(self, path):
project_id = self.project_store[path][0]
if project_id == 0: # cancel row clicked
self.stack.set_visible_child_name ('employee_page')
return
self.project_id = project_id
job_name = self.project_store[path][1]
self.get_object('project_label').set_label(job_name)
self.stack.set_visible_child_name ('punchin_page')
self.get_object('punchin_button').grab_focus()
DB.commit()
self.cursor.execute("SELECT EXTRACT ('epoch' FROM CURRENT_TIMESTAMP);")
self.clock_in_out_time = int(self.cursor.fetchone()[0])
c = DB.cursor()
c.execute("SELECT "
"to_char(now() - start_time, 'HH24:MI:SS'), "
"to_char(now() - stop_time, 'HH24:MI:SS'), "
"(SELECT COUNT(id)::text FROM time_clock_entries "
"WHERE (project_id, employee_id) = (%s, %s))"
"FROM time_clock_entries "
"WHERE (project_id, employee_id) = (%s, %s) "
"ORDER BY id DESC LIMIT 1",
(self.project_id, self.employee_id,
self.project_id, self.employee_id))
for row in c.fetchall():
self.get_object('last_punched_in_label').set_label(row[0])
self.get_object('last_punched_out_label').set_label(row[1])
self.get_object('punched_in_count_label').set_label(row[2])
break
else:
self.get_object('last_punched_in_label').set_label('0:00:00')
self.get_object('last_punched_out_label').set_label('0:00:00')
self.get_object('punched_in_count_label').set_label('0')
c.close()
self.show_date_from_seconds ()
def process_deposit(self, widget):
d = transactor.Deposit(self.date)
total_amount = 0.00
checking_account = self.builder.get_object(
'comboboxtext1').get_active_id()
for row in self.deposit_store:
if row[6] is True:
total_amount += float(row[2])
if total_amount != 0.00:
d.check(total_amount)
cash_amount = self.builder.get_object('spinbutton1').get_value()
if cash_amount != 0.00:
total_amount += cash_amount
cash_account = self.builder.get_object('combobox1').get_active_id()
d.cash(cash_amount, cash_account)
deposit_id = d.bank(total_amount, checking_account)
for row in self.deposit_store:
if row[6] is True:
row_id = row[0]
self.cursor.execute(
"UPDATE payments_incoming "
"SET (check_deposited, "
"gl_entries_deposit_id) = (True, %s) "
"WHERE id = %s", (deposit_id, row_id))
DB.commit()
self.window.destroy()
def delete_button_clicked (self, button):
selection = self.builder.get_object('treeview-selection1')
model, path = selection.get_selected_rows()
if path == []:
return
term_id = model[path][0]
try:
self.cursor.execute("DELETE FROM terms_and_discounts "
"WHERE id = %s", (term_id,))
DB.rollback()
self.cursor.execute("UPDATE terms_and_discounts "
"SET deleted = True "
"WHERE id = %s", (term_id,))
except Exception as e:
DB.rollback()
self.builder.get_object('label6').set_label(str(e))
self.builder.get_object('button4').set_sensitive(False)
dialog = self.builder.get_object('dialog1')
result = dialog.run()
dialog.hide()
if result == Gtk.ResponseType.ACCEPT:
new_term_id = self.builder.get_object('combobox1').get_active_id()
self.cursor.execute("UPDATE contacts "
"SET terms_and_discounts_id = %s "
"WHERE terms_and_discounts_id = %s",
(new_term_id, term_id))
self.cursor.execute("UPDATE terms_and_discounts "
"SET deleted = True "
"WHERE id = %s", (term_id,))
DB.commit()
self.populate_terms_store()
def delete_file_clicked(self, button):
selection = self.get_object('treeview-selection1')
model, path = selection.get_selected_rows()
if path == []:
return
dialog = Gtk.Dialog(title="")
dialog.set_transient_for(self.window)
dialog.add_button("Go back", Gtk.ResponseType.REJECT)
box = dialog.get_content_area()
from constants import is_admin
if is_admin == False:
label = Gtk.Label(label="You are not admin !")
else:
file_name = model[path][1]
dialog.add_button("Delete file", Gtk.ResponseType.ACCEPT)
label = Gtk.Label(
label="Are you sure you want to delete \n'%s' ?" % file_name)
box.add(label)
box.show_all()
result = dialog.run()
if result == Gtk.ResponseType.ACCEPT:
file_id = model[path][0]
self.cursor.execute("DELETE FROM files WHERE id = %s", (file_id, ))
DB.commit()
self.populate_file_store()
dialog.hide()
def set_all_printed_clicked(self, button):
self.cursor.execute(
"UPDATE mailing_list_register "
"SET printed = True "
"WHERE mailing_list_id = %s", (self.mailing_list_id, ))
DB.commit()
self.populate_contact_mailing_store()
def new_clicked(self, button):
qty = self.get_object('spinbutton1').get_text()
manufacturing_name = self.get_object('entry2').get_text()
project_name = self.get_object('entry1').get_text()
self.get_object('batch_notes_buffer').set_text('')
if self.get_object('checkbutton1').get_active() == True:
# this manufacturing project is time tracked
self.cursor.execute(
"INSERT INTO time_clock_projects "
"(name, start_date, active, permanent) "
"VALUES (%s, CURRENT_DATE, True, False) "
"RETURNING id", (project_name, ))
time_clock_projects_id = self.cursor.fetchone()[0]
self.cursor.execute(
"INSERT INTO manufacturing_projects "
"(product_id, name, qty, time_clock_projects_id, "
"active) VALUES (%s, %s, %s, %s, True) "
"RETURNING id", (self.product_id, manufacturing_name, qty,
time_clock_projects_id))
else:
self.cursor.execute(
"INSERT INTO manufacturing_projects "
"(product_id, name, qty, active) "
"VALUES (%s, %s, %s, True) RETURNING id",
(self.product_id, manufacturing_name, qty))
self.project_id = self.cursor.fetchone()[0]
DB.commit()
self.product_selected()
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 update_clicked(self, button):
qty = self.get_object('spinbutton1').get_text()
manufacturing_name = self.get_object('entry2').get_text()
project_name = self.get_object('entry1').get_text()
time_clock_active = self.get_object('checkbutton1').get_active()
self.cursor.execute(
"UPDATE manufacturing_projects SET "
"(name, qty) = (%s, %s) WHERE id = %s",
(manufacturing_name, qty, self.project_id))
self.cursor.execute(
"UPDATE time_clock_projects "
"SET (name, active, stop_date) = "
"(%s, %s, CURRENT_TIMESTAMP) WHERE id = "
"(SELECT time_clock_projects_id "
"FROM manufacturing_projects WHERE id = %s) "
"RETURNING id", (project_name, time_clock_active, self.project_id))
for row in self.cursor.fetchall():
break # updated successfully
else:
if time_clock_active == True: # create new time clock project
self.cursor.execute(
"WITH cte AS (INSERT INTO time_clock_projects "
"(name, start_date, active, permanent) "
"VALUES (%s, CURRENT_DATE, True, False) "
"RETURNING *) "
"UPDATE manufacturing_projects "
"SET time_clock_projects_id = (SELECT id FROM cte) "
"WHERE id = %s", (project_name, self.project_id))
DB.commit()
def save_clicked (self, button):
c = DB.cursor()
product_id = self.get_object('product_combo').get_active_id()
name = self.get_object('description_entry').get_text()
qty = self.get_object('units_spinbutton').get_value()
buf = self.get_object('batch_notes_buffer')
start_iter = buf.get_start_iter()
end_iter = buf.get_end_iter()
notes = buf.get_text(start_iter, end_iter, True)
if self.project_id == None:
time_clock_id = self.get_time_clock_id (name)
c.execute("INSERT INTO manufacturing_projects "
"(product_id, name, qty, time_clock_projects_id, "
"batch_notes, active, version_id) VALUES "
"(%s, %s, %s, %s, %s, True, %s)",
(product_id, name, qty, time_clock_id,
notes, self.version_id))
else:
c.execute("UPDATE manufacturing_projects SET "
"(name, qty, batch_notes, version_id) = "
"(%s, %s, %s, %s) WHERE id = %s "
"RETURNING time_clock_projects_id",
(name, qty, notes, self.version_id, self.project_id))
active = self.get_object('time_clock_checkbutton').get_active()
for row in c.fetchall():
time_clock_projects_id = row[0]
c.execute("UPDATE time_clock_projects "
"SET (name, active, stop_date) = "
"(%s, %s, CURRENT_DATE) "
"WHERE id = %s",
(name, active, time_clock_projects_id))
DB.commit()
c.close()
self.window.destroy ()
self.parent_class.populate_projects()
def bank_account_combo_changed(self, combobox):
account_number = combobox.get_active_id()
if account_number == None:
return
c = DB.cursor()
c.execute(
"CREATE OR REPLACE TEMP VIEW "
"bank_statement_report_view AS "
"WITH account_numbers AS "
"(SELECT number FROM gl_accounts "
"WHERE number = %s OR parent_number = %s"
") "
"SELECT id, amount, debit_account, "
"credit_account, check_number, date_inserted, "
"reconciled, transaction_description, "
"date_reconciled, TRUE AS debit, FALSE AS credit, "
"gl_transaction_id "
"FROM gl_entries WHERE debit_account "
"IN (SELECT * FROM account_numbers) "
"UNION "
"SELECT id, amount, debit_account, "
"credit_account, check_number, date_inserted, "
"reconciled, transaction_description, "
"date_reconciled, FALSE AS debit, TRUE AS credit, "
"gl_transaction_id "
"FROM gl_entries WHERE credit_account "
"IN (SELECT * FROM account_numbers)",
(account_number, account_number))
c.close()
DB.commit()
self.account_number = account_number
self.populate_bank_statement_store()
def save_notes(self):
if self.timeout_id:
GLib.source_remove(self.timeout_id)
self.cursor.execute("UPDATE resources SET notes = %s "
"WHERE id = %s", (self.notes, self.row_id))
DB.commit()
self.timeout_id = None
def save_row_ordering (self):
for row_count, row in enumerate (self.purchase_order_items_store):
row_id = row[0]
self.cursor.execute("UPDATE purchase_order_items "
"SET sort = %s WHERE id = %s",
(row_count, row_id))
DB.commit()
def to_do_toggled(self, renderer, path):
active = not self.resource_store[path][9]
self.resource_store[path][9] = active
id_ = self.resource_store[path][0]
self.cursor.execute("UPDATE resources SET to_do = %s "
"WHERE id = %s", (active, id_))
DB.commit()
def save_invoice_button_clicked (self, button):
if self.request_po_attachment and not self.attachment:
dialog = self.get_object('missing_attachment_dialog')
result = dialog.run()
dialog.hide()
if result == Gtk.ResponseType.CANCEL:
return
elif result == 0:
import pdf_attachment
paw = pdf_attachment.PdfAttachmentWindow(self.window)
paw.connect("pdf_optimized", self.optimized_callback)
return
invoice_number = self.get_object('entry6').get_text()
self.cursor.execute("UPDATE purchase_orders "
"SET (amount_due, invoiced, total, "
"invoice_description) = "
"(%s, True, %s, %s) WHERE id = %s",
(self.total, self.total, invoice_number,
self.purchase_order_id))
post_purchase_order (self.total, self.purchase_order_id)
self.cursor.execute("SELECT accrual_based FROM settings")
if self.cursor.fetchone()[0] == True:
post_purchase_order_accounts (self.purchase_order_id,
datetime.today())
DB.commit()
self.window.destroy()
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 save_product_terms_prices (self):
cost = self.get_object('spinbutton1').get_value()
self.cursor.execute("UPDATE products SET cost = %s "
"WHERE id = %s", (cost, self.product_id))
listbox = self.get_object('listbox2')
for list_box_row in listbox:
if list_box_row.get_index() == 0:
continue # skip the header
box = list_box_row.get_child()
widget_list = box.get_children()
terms_id_label = widget_list[0]
terms_id = terms_id_label.get_label()
sell_spin = widget_list[3]
sell_price = sell_spin.get_value()
self.cursor.execute("SELECT id FROM products_markup_prices "
"WHERE (product_id, markup_id) = (%s, %s)",
(self.product_id, terms_id))
for row in self.cursor.fetchall():
_id_ = row[0]
self.cursor.execute("UPDATE products_markup_prices "
"SET price = %s WHERE id = %s",
(sell_price, _id_))
break
else:
self.cursor.execute("INSERT INTO products_markup_prices "
"(product_id, markup_id, price) "
"VALUES (%s, %s, %s)",
(self.product_id, terms_id, sell_price))
DB.commit()
def new_expense_product_clicked(self, button):
c = DB.cursor()
c.execute("INSERT INTO products "
"(name, "
"unit, "
"cost, "
"expense, "
"tax_rate_id, "
"revenue_account, "
"default_expense_account) "
"VALUES "
"('New expense product', "
"1, "
"0.00, "
"True, "
"(SELECT id FROM tax_rates "
"WHERE standard = True "
"), "
"(SELECT number FROM gl_accounts "
"WHERE revenue_account = True LIMIT 1 "
"), "
"(SELECT number FROM gl_accounts "
"WHERE expense_account = True LIMIT 1 "
"))")
DB.commit()
c.close()
self.emit('expense-products-changed')
def cash_payment_clicked(self, button):
self.principal_and_interest_payment()
cash_account = self.builder.get_object('combobox3').get_active_id()
self.total_id = self.loan_payment.cash(cash_account)
self.update_loan_payment_ids()
DB.commit()
self.window.destroy()