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 CreditCardMerchantGUI: def __init__(self): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.cursor = DB.cursor() self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() date_text = self.calendar.get_text() self.builder.get_object('entry1').set_text(date_text) self.contact_id = 0 self.exists = True self.populate_stores() self.window = self.builder.get_object('window1') self.window.show_all() def destroy(self, window=None): self.exists = False self.cursor.close() def populate_stores(self): # debit accounts store = self.builder.get_object('debit_account_store') store.clear() self.cursor.execute("SELECT number, name FROM gl_accounts " "WHERE (type = 3) " "AND parent_number IS NULL " "OR bank_account " "ORDER BY number") for row in self.cursor.fetchall(): account_number = row[0] tree_parent = store.append(None, row) self.get_child_accounts(store, account_number, tree_parent) # credit accounts store = self.builder.get_object('credit_account_store') store.clear() self.cursor.execute("SELECT number, name FROM gl_accounts " "WHERE (type = 4) " "AND parent_number IS NULL " "OR bank_account " "ORDER BY number") for row in self.cursor.fetchall(): account_number = row[0] tree_parent = store.append(None, row) self.get_child_accounts(store, account_number, tree_parent) def get_child_accounts(self, store, parent_number, tree_parent): self.cursor.execute( "SELECT number, name FROM gl_accounts " "WHERE parent_number = %s", (parent_number, )) for row in self.cursor.fetchall(): account_number = row[0] parent = store.append(tree_parent, row) self.get_child_accounts(store, account_number, parent) def check_if_all_requirements_valid(self): post_button = self.builder.get_object('button2') post_button.set_sensitive(False) amount = self.builder.get_object('spinbutton1').get_value() if amount == 0.00: post_button.set_label('Amount is $0.00') return # no account selected credit_selection = self.builder.get_object('treeview-selection3') model, path = credit_selection.get_selected_rows() if path != []: treeiter = model.get_iter(path) if model.iter_has_child(treeiter) == True: post_button.set_label('Credit parent account selected') return # parent account selected else: post_button.set_label('No credit account selected') return # no account selected debit_selection = self.builder.get_object('treeview-selection2') model, path = debit_selection.get_selected_rows() if path != []: treeiter = model.get_iter(path) if model.iter_has_child(treeiter) == True: post_button.set_label('Debit parent account selected') return # parent account selected else: post_button.set_label('No debit account selected') return # no account selected post_button.set_sensitive(True) post_button.set_label('Post transaction') def debit_row_activate(self, treeview, path, treeviewcolumn): self.check_if_all_requirements_valid() store = self.builder.get_object('debit_account_store') account_number = store[path][0] account_name = store[path][1] acc_type = str(account_number)[0:1] if acc_type == '3' or acc_type == '4': treeviewcolumn.set_title('%s+' % account_name) else: treeviewcolumn.set_title('%s+' % account_name) def credit_row_activate(self, treeview, path, treeviewcolumn): self.check_if_all_requirements_valid() store = self.builder.get_object('credit_account_store') account_number = store[path][0] account_name = store[path][1] acc_type = str(account_number)[0:1] if acc_type == '3' or acc_type == '4': treeviewcolumn.set_title('%s+' % account_name) else: treeviewcolumn.set_title('%s-' % account_name) def description_changed(self, entry): self.check_if_all_requirements_valid() def amount_value_changed(self, entry): self.check_if_all_requirements_valid() def post_transaction_clicked(self, button): amount = self.builder.get_object('spinbutton1').get_value() #### debit debit_selection = self.builder.get_object('treeview-selection2') model, path = debit_selection.get_selected_rows() debit_account = model[path][0] #### credit credit_selection = self.builder.get_object('treeview-selection3') model, path = credit_selection.get_selected_rows() credit_account = model[path][0] double_entry_transaction(self.date, debit_account, credit_account, amount, '') DB.commit() self.window.destroy() def refresh_accounts_clicked(self, button): self.populate_stores() self.check_if_all_requirements_valid() debit_title = 'Debit | Expenses+ Credit card+ ' self.builder.get_object('treeviewcolumn1').set_title(debit_title) credit_title = 'Credit | Revenue+ Credit card- ' self.builder.get_object('treeviewcolumn2').set_title(credit_title) def calendar_day_selected(self, calendar): self.date = calendar.get_date() day_text = calendar.get_text() self.builder.get_object('entry1').set_text(day_text) def calendar_entry_icon_released(self, entry, icon, event): self.calendar.set_relative_to(entry) self.calendar.show()
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 CreditCardStatementGUI: def __init__(self, db): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.transactions_store = self.builder.get_object('transactions_store') self.income_expense_accounts_store = self.builder.get_object( 'income_expense_accounts_store') self.fees_rewards_store = self.builder.get_object( 'fees_rewards_description_store') self.db = db self.cursor = self.db.cursor() self.calendar = DateTimeCalendar(self.db) self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() self.populate_accounts_combo() self.window = self.builder.get_object('window1') self.window.show_all() def focus(self, window, event): return self.populate_accounts_combo() def spinbutton_focus_in_event(self, spinbutton, event): GLib.idle_add(self.highlight, spinbutton) def highlight(self, spinbutton): spinbutton.select_region(0, -1) def populate_accounts_combo(self): credit_card_store = self.builder.get_object('credit_card_store') #cc_id = credit_card_combo.get_active_id() credit_card_store.clear() self.cursor.execute( "SELECT number, name, " "(SELECT COALESCE(SUM(amount), 0.00) FROM gl_entries " "WHERE credit_account = gla.number) " "- " "(SELECT COALESCE(SUM(amount), 0.00) FROM gl_entries " "WHERE debit_account = gla.number) " "FROM gl_accounts AS gla " "WHERE credit_card_account = True") for row in self.cursor.fetchall(): number = row[0] name = row[1] amount = row[2] credit_card_store.append([str(number), name, str(amount)]) ################################################### checking_account_combo = self.builder.get_object('comboboxtext4') active_account = checking_account_combo.get_active_id() checking_account_combo.remove_all() self.cursor.execute("SELECT number, name FROM gl_accounts " "WHERE bank_account = True") for i in self.cursor.fetchall(): number = i[0] name = i[1] checking_account_combo.append(str(number), name) checking_account_combo.set_active_id(active_account) #################################################### self.fees_rewards_store.clear() self.cursor.execute("SELECT DISTINCT transaction_description " "FROM gl_entries " "WHERE fees_rewards = True") for row in self.cursor.fetchall(): description = row[0] self.fees_rewards_store.append([description]) #################################################### self.income_expense_accounts_store.clear() self.cursor.execute("SELECT number, name, type FROM gl_accounts " "WHERE (type = 3 OR type = 4) " "AND parent_number IS NULL ORDER BY name") for row in self.cursor.fetchall(): account_number = str(row[0]) account_name = row[1] account_type = row[2] p = self.income_expense_accounts_store.append( None, [account_number, account_name, account_type]) self.get_child_accounts(account_number, p) def get_child_accounts(self, number, parent): self.cursor.execute( "SELECT number, name, type FROM gl_accounts WHERE " "parent_number = %s", (number, )) for row in self.cursor.fetchall(): account_number = str(row[0]) account_name = row[1] account_type = row[2] p = self.income_expense_accounts_store.append( parent, [account_number, account_name, account_type]) self.get_child_accounts(account_number, p) def reconcile_clicked(self, widget): self.cursor.execute( "UPDATE gl_entries " "SET date_reconciled = %s " "WHERE date_reconciled IS NULL " "AND reconciled = True " "AND (credit_account = %s OR debit_account = %s) ", (self.date, self.credit_card_account, self.credit_card_account)) self.db.commit() self.populate_statement_treeview() def description_edited(self, renderer, path, text): row_id = self.transactions_store[path][0] self.cursor.execute( "UPDATE gl_entries SET transaction_description = %s " "WHERE id = %s", (text, row_id)) self.db.commit() self.transactions_store[path][3] = text def date_renderer_edited(self, renderer, path, text): row_id = self.transactions_store[path][0] try: self.cursor.execute( "UPDATE gl_entries SET date_inserted = %s " "WHERE id = %s RETURNING format_date(date_inserted)", (text, row_id)) #Postgres has a powerful date resolver, let it figure out the date date_formatted = self.cursor.fetchone()[0] except psycopg2.DataError as e: self.db.rollback() print(e) self.builder.get_object('label10').set_label(str(e)) dialog = self.builder.get_object('date_error_dialog') dialog.run() dialog.hide() return self.db.commit() self.transactions_store[path][1] = text self.transactions_store[path][2] = date_formatted def date_renderer_editing_started(self, renderer, entry, path): date = self.transactions_store[path][1] entry.set_text(date) def populate_statement_treeview(self, widget=None): self.transactions_store.clear() self.cursor.execute( "SELECT " "id, " "transaction_description, " "amount, " "date_inserted::text, " "format_date(date_inserted), " "reconciled, " "debit_account, " "credit_account " "FROM gl_entries " "WHERE (debit_account = %s OR credit_account = %s) " "AND date_reconciled IS NULL ORDER BY date_inserted", (self.credit_card_account, self.credit_card_account)) for row in self.cursor.fetchall(): row_id = row[0] description = row[1] amount = float(row[2]) date = row[3] date_formatted = row[4] reconciled = row[5] if str(row[6]) == self.credit_card_account: amount = '{:,.2f}'.format(amount) self.transactions_store.append([ row_id, str(date), date_formatted, description, amount, '', reconciled ]) else: amount = '{:,.2f}'.format(amount) self.transactions_store.append([ row_id, str(date), date_formatted, description, '', amount, reconciled ]) def reconcile_toggled(self, toggle_renderer, path): active = not toggle_renderer.get_active() row_id = self.transactions_store[path][0] self.transactions_store[path][6] = active self.cursor.execute( "UPDATE gl_entries " "SET reconciled = %s WHERE id = %s", (active, row_id)) self.db.commit() def credit_combo_changed(self, combo): account = combo.get_active_id() if account == None: return store = combo.get_model() iter_ = combo.get_active_iter() balance = store[iter_][2] self.credit_card_account = account self.populate_statement_treeview() self.builder.get_object('label9').set_label(str(balance)) self.builder.get_object('combobox1').set_sensitive(True) self.builder.get_object('spinbutton2').set_sensitive(True) self.builder.get_object('button5').set_sensitive(True) def fees_rewards_description_changed(self, entry): if entry.get_text() == '': self.builder.get_object('spinbutton1').set_sensitive(False) else: self.builder.get_object('spinbutton1').set_sensitive(True) def fees_rewards_amount_value_changed(self, spinbutton): self.builder.get_object('combobox2').set_sensitive(True) self.penalty_amount = spinbutton.get_value() def fees_rewards_account_combo_changed(self, combo): account = combo.get_active_id() if account == None: return path = combo.get_active() self.fees_rewards_type = self.income_expense_accounts_store[path][2] self.fees_rewards_account = account self.builder.get_object('button1').set_sensitive(True) def save_fee_reward_clicked(self, button): description = self.builder.get_object('combobox-entry').get_text() if self.fees_rewards_type == 3: transactor.credit_card_fee_reward(self.db, self.date, self.credit_card_account, self.fees_rewards_account, float(self.penalty_amount), description) else: transactor.credit_card_fee_reward(self.db, self.date, self.fees_rewards_account, self.credit_card_account, float(self.penalty_amount), description) self.populate_statement_treeview() self.builder.get_object('combobox2').set_active(-1) self.builder.get_object('combobox2').set_sensitive(False) self.builder.get_object('combobox1').set_active(-1) self.builder.get_object('combobox-entry').set_text('') button.set_sensitive(False) self.db.commit() def payment_amount_value_changed(self, spinbutton): self.builder.get_object('comboboxtext4').set_sensitive(True) self.payment_amount = spinbutton.get_value() def bank_account_combo_changed(self, combo): contact_name = self.builder.get_object('combobox-entry2').get_text() self.builder.get_object('entry3').set_text(contact_name + ' ') self.builder.get_object('entry3').set_sensitive(True) self.bank_account = combo.get_active_id() def transaction_number_changed(self, widget): self.builder.get_object('button2').set_sensitive(True) def save_payment(self, widget): transaction_number = self.builder.get_object('entry3').get_text() transactor.bank_to_credit_card_transfer(self.db, self.bank_account, self.credit_card_account, self.payment_amount, self.date, transaction_number) self.populate_statement_treeview() self.builder.get_object('entry3').set_sensitive(False) self.builder.get_object('entry3').set_text("") self.builder.get_object('button2').set_sensitive(False) self.builder.get_object('comboboxtext4').set_sensitive(False) self.db.commit() def calendar_day_selected(self, calendar): self.date = calendar.get_date() day_text = calendar.get_text() self.builder.get_object('entry4').set_text(day_text) def calendar_entry_icon_released(self, widget, icon, event): self.calendar.set_relative_to(widget) self.calendar.show()
class ProductSerialNumbersGUI(Gtk.Builder): def __init__(self): Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.cursor = DB.cursor() self.product_store = self.get_object('product_store') self.contact_store = self.get_object('contact_store') self.handler_ids = list() for connection in (("products_changed", self.populate_product_store), ): handler = broadcaster.connect(connection[0], connection[1]) self.handler_ids.append(handler) product_completion = self.get_object('add_product_completion') product_completion.set_match_func(self.product_match_func) product_completion = self.get_object('event_product_completion') product_completion.set_match_func(self.product_match_func) contact_completion = self.get_object('contact_completion') contact_completion.set_match_func(self.contact_match_func) self.product_name = '' self.contact_name = '' self.serial_number = '' self.filtered_store = self.get_object('serial_number_treeview_filter') self.filtered_store.set_visible_func(self.filter_func) sort_model = self.get_object('serial_number_treeview_sort') sort_model.set_sort_func(4, self.serial_number_sort_func) self.product_id = 0 self.populate_product_store() self.populate_contact_store() self.populate_serial_number_history() self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() self.window = self.get_object('window1') self.window.show_all() def destroy(self, window): for handler in self.handler_ids: broadcaster.disconnect(handler) self.cursor.close() def search_changed(self, entry): '''This signal is hooked up to all search entries''' self.product_name = self.get_object('searchentry2').get_text().lower() self.contact_name = self.get_object('searchentry3').get_text().lower() self.serial_number = self.get_object('searchentry4').get_text().lower() self.filtered_store.refilter() def filter_func(self, model, tree_iter, r): for i in self.product_name.split(): if i not in model[tree_iter][3].lower(): return False for i in self.serial_number.split(): if i not in model[tree_iter][4].lower(): return False return True def serial_number_sort_func(self, model, iter_a, iter_b, arg): a = model[iter_a][4] b = model[iter_b][4] try: return int(a) - int(b) except Exception as e: if a < b: return -1 elif a > b: return 1 return 0 # indentical def calendar_day_selected(self, calendar): self.date = calendar.get_date() day_text = calendar.get_text() self.get_object('entry3').set_text(day_text) self.get_object('entry4').set_text(day_text) 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 contact_match_func(self, completion, key, tree_iter): split_search_text = key.split() for text in split_search_text: if text not in self.contact_store[tree_iter][1].lower(): return False return True def populate_product_store(self, m=None, i=None): self.product_store.clear() self.cursor.execute("SELECT id::text, name, invoice_serial_numbers " "FROM products " "WHERE (deleted, stock, sellable) = " "(False, True, True) ORDER BY name") for row in self.cursor.fetchall(): self.product_store.append(row) DB.rollback() def populate_contact_store(self, m=None, i=None): self.contact_store.clear() self.cursor.execute("SELECT id::text, name, ext_name FROM contacts " "WHERE deleted = False ORDER BY name") for row in self.cursor.fetchall(): self.contact_store.append(row) DB.rollback() def populate_serial_number_history(self): treeview = self.get_object('serial_number_treeview') original_model = treeview.get_model() treeview.set_model(None) store = self.get_object('serial_number_treeview_store') store.clear() self.cursor.execute("SELECT " "sn.id, " "COALESCE(manufacturing_id::text, ''), " "p.id, " "p.name, " "sn.serial_number, " "sn.date_inserted::text, " "format_date(sn.date_inserted), " "COALESCE(COUNT(snh.id)::text, ''), " "COALESCE(ili.invoice_id, 0), " "COALESCE(ili.invoice_id::text, ''), " "COALESCE(i.dated_for::text, ''), " "COALESCE(format_date(i.dated_for), ''), " "COALESCE(poli.purchase_order_id::text, ''), " "COALESCE(po.date_printed::text, ''), " "COALESCE(format_date(po.date_printed), '') " "FROM serial_numbers AS sn " "JOIN products AS p ON p.id = sn.product_id " "LEFT JOIN serial_number_history AS snh " "ON snh.serial_number_id = sn.id " "LEFT JOIN invoice_items AS ili " "ON ili.id = sn.invoice_item_id " "LEFT JOIN invoices AS i " "ON i.id = ili.invoice_id " "LEFT JOIN purchase_order_items AS poli " "ON poli.id = sn.purchase_order_item_id " "LEFT JOIN purchase_orders AS po " "ON po.id = poli.purchase_order_id " "GROUP BY sn.id, p.id, p.name, sn.serial_number, " "sn.date_inserted, invoice_id, poli.id, " "manufacturing_id, i.dated_for, " "po.date_printed " "ORDER BY sn.id") for row in self.cursor.fetchall(): store.append(row) treeview.set_model(original_model) DB.rollback() def serial_number_treeview_row_activated(self, treeview, path, column): model = treeview.get_model() serial_id = model[path][0] self.populate_serial_event_store(serial_id) def populate_serial_event_store(self, serial_id): store = self.get_object('events_store') store.clear() self.cursor.execute( "SELECT " "snh.id, " "c.name, " "snh.date_inserted::text, " "format_date(snh.date_inserted), " "snh.description " "FROM serial_number_history AS snh " "JOIN contacts AS c ON c.id = snh.contact_id " "WHERE serial_number_id = %s", (serial_id, )) for row in self.cursor.fetchall(): store.append(row) DB.rollback() def add_serial_event_clicked(self, button): window = self.get_object('add_event_window') window.show_all() def add_event_clicked(self, button): serial_number = self.get_object('entry2').get_text() buf = self.get_object('textbuffer1') start_iter = buf.get_start_iter() end_iter = buf.get_end_iter() description = buf.get_text(start_iter, end_iter, True) self.cursor.execute( "INSERT INTO serial_number_history " "(contact_id, " "date_inserted, description, serial_number_id) " "VALUES (%s, %s, %s, %s)", (self.contact_id, self.date, description, self.serial_id)) DB.commit() self.populate_serial_number_history() self.get_object('combobox2').set_sensitive(False) self.get_object('combobox3').set_sensitive(False) self.get_object('textview1').set_sensitive(False) self.get_object('combobox-entry2').set_text('') self.get_object('combobox-entry4').set_text('') self.get_object('combobox-entry3').set_text('') self.get_object('textbuffer1').set_text('') self.get_object('add_event_window').hide() button.set_sensitive(False) def cancel_event_clicked(self, button): self.get_object('add_event_window').hide() def add_event_window_delete_event(self, window, event): window.hide() return True def add_serial_number_window_clicked(self, button): self.get_object('exception_label').set_text('') self.get_object('add_serial_number_button').set_sensitive(False) self.get_object('add_serial_number_window').show_all() def add_serial_number_clicked(self, button): serial_number = self.get_object('entry2').get_text() try: self.cursor.execute( "INSERT INTO serial_numbers " "(product_id, serial_number, " "date_inserted) " "VALUES (%s, %s, %s)", (self.product_id, serial_number, self.date)) DB.commit() self.get_object('add_serial_number_window').hide() self.populate_serial_number_history() except psycopg2.IntegrityError as e: self.get_object('exception_label').set_text(str(e)) DB.rollback() def cancel_serial_number_clicked(self, button): self.get_object('add_serial_number_window').hide() def add_serial_number_window_delete_event(self, window, event): window.hide() return True def refresh_clicked(self, button): self.populate_serial_number_history() def date_entry_icon_released(self, entry, icon, event): self.calendar.set_relative_to(entry) self.calendar.show() def event_product_match_selected(self, completion, model, iter_): product_id = model[iter_][0] self.get_object('combobox1').set_active_id(product_id) self.get_object('combobox4').set_active_id(product_id) def add_product_match_selected(self, completion, model, iter_): product_id = model[iter_][0] self.get_object('combobox1').set_active_id(product_id) self.get_object('combobox4').set_active_id(product_id) def event_product_combo_changed(self, combo): product_id = combo.get_active_id() if product_id != None: self.product_id = product_id self.get_object('combobox2').set_sensitive(True) store = self.get_object('serial_number_store') store.clear() self.cursor.execute( "SELECT id::text, serial_number " "FROM serial_numbers " "WHERE product_id = %s " "ORDER BY serial_number", (product_id, )) for row in self.cursor.fetchall(): store.append(row) DB.rollback() def add_product_combo_changed(self, combo): product_id = combo.get_active_id() if product_id != None: self.product_id = product_id self.get_object('entry2').set_sensitive(True) def contact_match_selected(self, completion, model, iter_): contact_id = model[iter_][0] if contact_id != None: self.contact_id = contact_id self.get_object('textview1').set_sensitive(True) def contact_combo_changed(self, combo): contact_id = combo.get_active_id() if contact_id != None: self.contact_id = contact_id self.get_object('textview1').set_sensitive(True) def add_serial_number_changed(self, entry): self.get_object('add_serial_number_button').set_sensitive(True) def serial_number_match_selected(self, completion, model, iter_): serial_number = model[iter_][0] self.get_object('combobox2').set_active_id(serial_number) def event_serial_number_changed(self, combobox): serial_id = combobox.get_active_id() if serial_id != None: self.serial_id = serial_id self.get_object('combobox3').set_sensitive(True) def event_description_changed(self, entry): self.get_object('add_event_button').set_sensitive(True) def reprint_serial_number_clicked(self, button): barcode = self.get_object('serial_number_entry').get_text() label = Item() label.code128 = barcode_generator.makeCode128(str(barcode)) label.barcode = barcode from py3o.template import Template label_file = "/tmp/manufacturing_serial_label.odt" t = Template(template_dir + "/manufacturing_serial_template.odt", label_file) data = dict(label=label) t.render(data) subprocess.call(["soffice", "--headless", "-p", label_file]) def treeview_button_release_event(self, widget, event): if event.button != 3: return menu = self.get_object('right_click_menu') menu.popup_at_pointer() def add_serial_number_event_activated(self, menuitem): selection = self.get_object('serial_number_treeselection') model, path = selection.get_selected_rows() if path == []: return product_id = model[path][2] serial_number = model[path][4] window = self.get_object('add_event_window') window.show_all() self.get_object('combobox4').set_active_id(str(product_id)) self.get_object('combobox2').set_active_id(str(serial_number)) def invoice_hub_activated(self, menuitem): selection = self.get_object('serial_number_treeselection') model, path = selection.get_selected_rows() if path == []: return invoice_number = model[path][9] if invoice_number == "": return import invoice_hub invoice_hub.InvoiceHubGUI(invoice_number) def select_serial_number_activated(self, menuitem): selection = self.get_object('serial_number_treeselection') model, path = selection.get_selected_rows() if path == []: return serial_number = model[path][4] self.get_object('serial_number_entry').set_text(serial_number)
class GUI(Gtk.Builder): def __init__(self): Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.get_object('treeview2').set_model(expense_tree) self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() self.reconcile_calendar = DateTimeCalendar() self.reconcile_calendar.connect('day-selected', self.reconcile_calendar_day_selected) self.reconcile_calendar.set_relative_to(self.get_object('entry6')) self.reconcile_date = None self.voided_cheque_calendar = DateTimeCalendar() self.voided_cheque_calendar.connect('day-selected', self.voided_cheque_day_selected) self.voided_cheque_calendar.set_relative_to(self.get_object('entry5')) self.voided_cheque_date = None self.account_number = 0 self.bank_transaction_store = self.get_object('bank_transaction_store') self.bank_account_store = self.get_object('bank_account_store') self.transaction_description_store = self.get_object( 'transaction_description_store') self.populate_account_stores() self.window = self.get_object('window1') self.window.show_all() def spinbutton_focus_in_event(self, spinbutton, event): GLib.idle_add(spinbutton.select_region, 0, -1) def view_closed_items(self, check_button): self.populate_treeview() def voided_cheque_activated(self, menuitem): dialog = self.get_object('check_dialog') response = dialog.run() dialog.hide() if response == Gtk.ResponseType.ACCEPT: bank_account = self.get_object('combobox3').get_active_id() cheque_number = self.get_object('entry3').get_text() transactor.post_voided_check(bank_account, self.voided_cheque_date, cheque_number) def date_entry_icon_release(self, entry, entryiconposition, event): if self.account_number != 0: self.reconcile_calendar.show() def reconcile_calendar_day_selected(self, calendar): c = DB.cursor() entry = self.get_object('entry6') date = calendar.get_date() c.execute( "SELECT COUNT(id) FROM gl_entries " "WHERE date_reconciled = %s", (date, )) if c.fetchone()[0] != 0: entry.set_text("Date already used !") self.reconcile_date = None else: entry.set_text(calendar.get_text()) self.reconcile_date = calendar.get_date() self.account_statement_difference() c.close() DB.rollback() def voided_cheque_day_selected(self, calendar): self.voided_cheque_date = calendar.get_date() date = calendar.get_text() self.get_object('entry5').set_text(date) self.check_voided_cheque_entries_valid() def voided_cheque_bank_account_changed(self, combobox): self.check_voided_cheque_entries_valid() def voided_cheque_number_changed(self, entry): self.check_voided_cheque_entries_valid() def voided_check_date_entry_icon_released(self, entry, icon, position): self.voided_cheque_calendar.show() def check_voided_cheque_entries_valid(self): button = self.get_object('button6') button.set_sensitive(False) if self.get_object('combobox3').get_active_id() == None: button.set_label('No bank account selected') return if self.get_object('entry3').get_text() == '': button.set_label('No check number entered') return if self.voided_cheque_date == None: button.set_label('No date selected') return button.set_sensitive(True) button.set_label('Apply voided check') def populate_bank_charge_stores(self): c = DB.cursor() self.transaction_description_store.clear() c.execute( "SELECT transaction_description AS td " "FROM gl_entries " "WHERE (debit_account = %s OR credit_account = %s) " "AND transaction_description IS NOT NULL " "AND fees_rewards = True " "GROUP BY td ORDER BY td", (self.account_number, self.account_number)) for row in c.fetchall(): description = row[0] self.transaction_description_store.append([description]) c.close() DB.rollback() def populate_account_stores(self): c = DB.cursor() c.execute("SELECT number::text, name FROM gl_accounts " "WHERE bank_account = True") for row in c.fetchall(): self.bank_account_store.append(row) c.close() DB.rollback() def bank_account_changed(self, combo): c = DB.cursor() account_number = combo.get_active_id() if account_number == None: self.account_number = 0 self.bank_transaction_store.clear() return c.execute( "CREATE OR REPLACE TEMP VIEW " "bank_statement_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 " "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 " "FROM gl_entries WHERE credit_account " "IN (SELECT * FROM account_numbers)", (account_number, account_number)) c.close() DB.commit() self.calculate_bank_account_total(account_number) self.get_object('button2').set_sensitive(True) self.get_object('refresh_button').set_sensitive(True) self.get_object('label13').set_label(str(self.bank_account_total)) self.account_number = account_number self.populate_treeview() self.calculate_reconciled_balance() def calculate_bank_account_total(self, account_number): c = DB.cursor() c.execute("SELECT SUM(debits - credits) AS total FROM " "(SELECT COALESCE(SUM(amount),0.00) AS debits " "FROM bank_statement_view " "WHERE debit = True) d, " "(SELECT COALESCE(SUM(amount),0.00) AS credits " "FROM bank_statement_view " "WHERE credit = True) c ") bank_account_total = c.fetchone()[0] c.close() self.bank_account_total = bank_account_total def refresh_clicked(self, button): self.populate_treeview() def populate_treeview(self): c = DB.cursor() self.bank_transaction_store.clear() c.execute( "SELECT ge.id, " "COALESCE(ge.check_number::text, ''), " "ge.date_inserted::text, " "format_date(ge.date_inserted), " "CASE WHEN contacts.name IS NOT NULL " "THEN contacts.name " "ELSE transaction_description END, " "reconciled, " "CASE WHEN ge.credit THEN ge.amount::text ELSE '' END, " "CASE WHEN ge.debit THEN ge.amount::text ELSE '' END " "FROM bank_statement_view AS ge " "LEFT JOIN payments_incoming AS pi " "ON ge.id = pi.gl_entries_id " "LEFT JOIN contacts ON contacts.id = " "pi.customer_id " "WHERE date_reconciled IS NULL " "ORDER BY date_inserted;", (self.account_number, self.account_number)) for row in c.fetchall(): self.bank_transaction_store.append(row) c.close() DB.rollback() def on_reconciled_toggled(self, widget, path): iter_ = self.bank_transaction_store.get_iter(path) c = DB.cursor() active = not self.bank_transaction_store[iter_][5] self.bank_transaction_store[iter_][ 5] = active #toggle the button state row_id = self.bank_transaction_store[iter_][0] c.execute("UPDATE gl_entries " "SET reconciled = %s WHERE id = %s", (active, row_id)) DB.commit() self.calculate_reconciled_balance() self.account_statement_difference() c.close() def calculate_reconciled_balance(self): c = DB.cursor() c.execute("SELECT SUM(debits - credits) AS total FROM " "(SELECT COALESCE(SUM(amount),0.00) AS debits " "FROM bank_statement_view " "WHERE (reconciled, debit) = (True, True)" ") d, " "(SELECT COALESCE(SUM(amount),0.00) AS credits " "FROM bank_statement_view " "WHERE (reconciled, credit) = (True, True)" ") c ") self.reconciled_total = c.fetchone()[0] t = '${:,.2f}'.format(float(self.reconciled_total)) self.get_object('entry2').set_text(t) c.close() DB.rollback() def statement_balance_spinbutton_changed(self, entry): self.account_statement_difference() def account_statement_difference(self): button = self.get_object('button1') statement_amount = Decimal(self.get_object('spinbutton1').get_text()) diff = statement_amount - self.reconciled_total self.get_object('entry4').set_text('${:,.2f}'.format(diff)) if diff == Decimal('0.00') and statement_amount != Decimal('0.00'): button.set_label("Save reconciled items") button.set_sensitive(True) else: button.set_label("Reconciled amount does not match") button.set_sensitive(False) return if self.reconcile_date == None: button.set_label("No reconcile date") button.set_sensitive(False) else: button.set_label("Save reconciled items") button.set_sensitive(True) def save_reconciled_items_clicked(self, button): c = DB.cursor() c.execute( "WITH account_numbers AS " "(SELECT number FROM gl_accounts " "WHERE number = %s OR parent_number = %s" ") " "UPDATE gl_entries " "SET date_reconciled = %s " "WHERE " "(debit_account IN " "(SELECT * FROM account_numbers) " "OR credit_account IN " "(SELECT * FROM account_numbers)" ") " "AND date_reconciled IS NULL " "AND reconciled = True", (self.account_number, self.account_number, self.reconcile_date)) DB.commit() c.close() self.populate_treeview() self.get_object('spinbutton1').set_value(0.00) def add_bank_charge_clicked(self, button): if self.account_number == 0: return self.populate_bank_charge_stores() dialog = self.get_object('dialog1') self.check_bank_charge_validity() result = dialog.run() dialog.hide() if result == Gtk.ResponseType.ACCEPT: selection = self.get_object('treeview-selection2') model, path = selection.get_selected_rows() expense_account_number = model[path][0] transactor.bank_charge(self.account_number, self.date, self.amount, self.description, expense_account_number) DB.commit() self.calculate_bank_account_total(self.account_number) self.get_object('label13').set_label(str(self.bank_account_total)) self.populate_treeview() def number_edited(self, renderer, path, text): iter_ = self.bank_transaction_store.get_iter(path) row_id = self.bank_transaction_store[iter_][0] c = DB.cursor() try: c.execute( "UPDATE gl_entries SET check_number = %s " "WHERE id = %s", (text, row_id)) c.close() DB.commit() self.bank_transaction_store[iter_][1] = text except psycopg2.DataError as e: DB.rollback() self.get_object('label10').set_label(str(e)) dialog = self.get_object('date_error_dialog') dialog.run() dialog.hide() def description_edited(self, renderer, path, text): iter_ = self.bank_transaction_store.get_iter(path) c = DB.cursor() row_id = self.bank_transaction_store[iter_][0] c.execute( "UPDATE gl_entries SET transaction_description = %s " "WHERE id = %s", (text, row_id)) DB.commit() c.close() self.bank_transaction_store[iter_][4] = text def date_renderer_editing_started(self, renderer, entry, path): date = self.bank_transaction_store[path][3] entry.set_text(date) def date_renderer_edited(self, renderer, path, text): c = DB.cursor() transaction_id = self.bank_transaction_store[path][0] try: c.execute( "UPDATE gl_entries " "SET date_inserted = %s WHERE id = %s", (text, transaction_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() DB.commit() c.close() self.populate_treeview() def expense_treeview_row_activate(self, treeview, path, treeview_column): self.check_bank_charge_validity() def bank_charge_spinbutton_amount_changed(self, button): self.check_bank_charge_validity() def description_entry_changed(self, entry): self.check_bank_charge_validity() def check_bank_charge_validity(self): button = self.get_object('button3') button.set_sensitive(False) self.amount = self.get_object('spinbutton2').get_value() if self.amount == 0.00: button.set_label('No amount entered') return self.description = self.get_object('combobox-entry').get_text() if self.description == '': button.set_label('No description') return selection = self.get_object('treeview-selection2') model, path = selection.get_selected_rows() if path != []: iter_ = model.get_iter(path) if model.iter_has_child(iter_) == True: button.set_label('Expense parent account selected') return # parent account selected else: button.set_label('No expense account selected') return # no account selected button.set_sensitive(True) button.set_label('Add bank charge') def calendar_day_selected(self, calendar): self.date = calendar.get_date() day_text = calendar.get_text() self.get_object('entry1').set_text(day_text) def calendar_entry_icon_released(self, entry, icon, event): self.calendar.set_relative_to(entry) self.calendar.show() def credit_card_statement_activated(self, menuitem): import credit_card_statements credit_card_statements.CreditCardStatementGUI() def miscellaneous_revenue_activated(self, button): import miscellaneous_revenue miscellaneous_revenue.MiscellaneousRevenueGUI() def loan_payment_activated(self, widget): import loan_payment loan_payment.LoanPaymentGUI() def double_entry_transaction_activated(self, menuitem): import double_entry_transaction double_entry_transaction.DoubleEntryTransactionGUI() def incoming_invoices_activated(self, menuitem): import incoming_invoice incoming_invoice.IncomingInvoiceGUI()
class PurchaseOrderGUI(Gtk.Builder): def __init__(self, edit_po_id = None ): self.purchase_order_id = None self.vendor_id = 0 Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.cursor = DB.cursor() self.edited_renderer_text = 1 self.qty_renderer_value = 1 self.focusing = False self.order_number_completion = self.get_object ('order_number_completion') self.order_number_store = self.get_object ('order_number_store') self.revenue_account_store = self.get_object ('revenue_account_store') self.expense_account_store = self.get_object ('expense_account_store') self.p_o_store = self.get_object('purchase_order_store') self.vendor_store = self.get_object('vendor_store') self.barcodes_not_found_store = self.get_object('barcodes_not_found_store') vendor_completion = self.get_object('vendor_completion') vendor_completion.set_match_func(self.vendor_match_func) self.handler_ids = list() for connection in (("contacts_changed", self.populate_vendor_store ), ("products_changed", self.populate_product_store ), ("purchase_orders_changed", self.show_reload_infobar )): handler = broadcaster.connect(connection[0], connection[1]) self.handler_ids.append(handler) self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() self.product_store = self.get_object('product_store') product_completion = self.get_object('product_completion') product_completion.set_match_func(self.product_match_string ) self.populate_product_store () enforce_target = Gtk.TargetEntry.new('text/plain', Gtk.TargetFlags(1), 129) self.treeview = self.get_object('treeview2') self.treeview.drag_dest_set(Gtk.DestDefaults.ALL, [enforce_target], Gdk.DragAction.COPY) self.treeview.drag_dest_set_target_list([enforce_target]) self.populate_vendor_store () if edit_po_id != None: self.cursor.execute("SELECT name, vendor_id FROM purchase_orders " "WHERE id = %s", (edit_po_id,)) for row in self.cursor.fetchall(): po_name = row[0] self.vendor_id = row[1] self.get_object('po_name_entry').set_text(po_name) self.get_object('po_number_entry').set_text(str(edit_po_id)) self.get_object('combobox1').set_active_id(str(self.vendor_id)) self.get_object('button2').set_sensitive(True) self.get_object('button3').set_sensitive(True) self.get_object('menuitem5').set_sensitive(True) self.get_object('menuitem2').set_sensitive(True) self.purchase_order_id = int(edit_po_id) self.populate_purchase_order_items () self.cursor.execute("SELECT print_direct FROM settings") self.get_object('menuitem1').set_active(self.cursor.fetchone()[0]) #set the direct print checkbox self.window = self.get_object('window') self.window.show_all() GLib.idle_add(self.load_settings) def load_settings (self): self.cursor.execute("SELECT column_id, visible " "FROM settings.po_columns") for row in self.cursor.fetchall(): column_id = row[0] visible = row[1] self.get_object(column_id).set_visible(visible) DB.rollback() def destroy(self, window): for handler in self.handler_ids: broadcaster.disconnect(handler) self.unlock_po() self.cursor.close() def widget_focus_in_event (self, widget, event): GLib.idle_add(widget.select_region, 0, -1) 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.vendor_id == 0: return self.check_po_id() qty, product_id = list_[0], list_[1] cursor = DB.cursor() cursor.execute("SELECT COALESCE(vpn.vendor_sku, ''), p.name " "FROM products AS p " "LEFT JOIN vendor_product_numbers AS vpn " "ON p.id = vpn.product_id AND vendor_id = %s " "WHERE p.id = %s", (self.vendor_id, product_id)) for row in cursor.fetchall(): order_number = row[0] name = row[1] _iter = self.p_o_store.append([0, '1', int(product_id), order_number, True, name, '', '', '0', '0', True, int(self.vendor_id), '', self.purchase_order_id, False]) self.check_po_item_id(_iter) cursor.close() def export_to_csv_activated (self, menuitem): import csv vendor_name = self.get_object('combobox-entry').get_text() dialog = self.get_object ('filechooserdialog1') uri = os.path.expanduser('~') dialog.set_current_folder_uri("file://" + uri) dialog.set_current_name(vendor_name + ".csv") response = dialog.run() dialog.hide() if response != Gtk.ResponseType.ACCEPT: return selected_file = dialog.get_filename() with open(selected_file, 'w') as csvfile: exportfile = csv.writer( csvfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL) cursor = DB.cursor() cursor.execute("SELECT " "qty, " "name, " "order_number, " "price, " "ext_price " "FROM purchase_order_items AS poli " "JOIN products ON poli.product_id = products.id " "WHERE (purchase_order_id, hold) = (%s, False) " "ORDER BY poli.sort, poli.id", (self.purchase_order_id,)) for row in cursor.fetchall(): exportfile.writerow(row) cursor.close() DB.rollback() def help_clicked (self, widget): subprocess.Popen(["yelp", help_dir + "/purchase_order.page"]) def view_all_toggled (self, checkbutton): self.populate_purchase_order_items () def hold_togglebutton_toggled (self, togglebutton, path): cursor = DB.cursor() row_id = self.p_o_store[path][0] try: cursor.execute("UPDATE purchase_order_items " "SET hold = NOT " "(SELECT hold FROM purchase_order_items " "WHERE id = %s FOR UPDATE NOWAIT) " "WHERE id = %s RETURNING hold", (row_id, row_id)) except psycopg2.OperationalError as e: DB.rollback() cursor.close() error = str(e) + "Somebody else is editing this row" self.show_message (error) return False for row in cursor.fetchall(): active = row[0] self.p_o_store[path][14] = active DB.commit() self.calculate_totals () cursor.close() def populate_purchase_order_items (self): cursor = DB.cursor() self.p_o_store.clear() if self.get_object ('checkbutton1').get_active() == True: self.get_object('treeviewcolumn11').set_visible(True) cursor.execute("SELECT " "poli.id, " "poli.qty::text, " "poli.product_id, " "COALESCE(order_number, vendor_sku, 'No sku'), " "products.stock, " "products.name, " "products.ext_name, " "poli.remark, " "poli.price::text, " "(poli.qty * poli.price)::text, " "False, " "po.vendor_id, " "c.name, " "po.id, " "poli.hold " "FROM purchase_order_items AS poli " "JOIN products ON products.id = poli.product_id " "JOIN purchase_orders AS po " "ON po.id = poli.purchase_order_id " "JOIN contacts AS c ON c.id = po.vendor_id " "LEFT JOIN vendor_product_numbers AS vpn " "ON (vpn.vendor_id, vpn.product_id) " "= (poli.product_id, po.vendor_id) " "WHERE (po.canceled, po.closed, po.paid) = " "(False, False, False) " "ORDER BY poli.sort, poli.id") else: self.get_object('treeviewcolumn11').set_visible(False) cursor.execute("SELECT " "poli.id, " "poli.qty::text, " "poli.product_id, " "COALESCE(order_number, vendor_sku, 'No sku'), " "products.stock, " "products.name, " "products.ext_name, " "poli.remark, " "poli.price::text, " "(poli.qty * poli.price)::text, " "False, " "po.vendor_id, " "c.name, " "po.id, " "poli.hold " "FROM purchase_order_items AS poli " "JOIN products ON products.id = poli.product_id " "JOIN purchase_orders AS po " "ON po.id = poli.purchase_order_id " "JOIN contacts AS c ON c.id = po.vendor_id " "LEFT JOIN vendor_product_numbers AS vpn " "ON (vpn.vendor_id, vpn.product_id) " "= (poli.product_id, po.vendor_id) " "WHERE purchase_order_id = %s " "ORDER BY poli.sort, poli.id", (self.purchase_order_id, ) ) for row in cursor.fetchall(): self.p_o_store.append(row) self.calculate_totals () cursor.close() DB.rollback() def populate_product_store (self, m=None, i=None): self.product_store.clear() self.cursor.execute("SELECT id::text, name ||'{' || ext_name ||'}' " "FROM products " "WHERE (deleted, purchasable, stock) = " "(False, True, True) ORDER BY name") for row in self.cursor.fetchall(): self.product_store.append(row) self.order_number_store.clear() self.cursor.execute("SELECT product_id, vendor_sku " "FROM vendor_product_numbers " "WHERE vendor_id = %s", (self.vendor_id,)) for row in self.cursor.fetchall(): self.order_number_store.append(row) DB.rollback() def line_items_treeview_vendor_changed (self, combo, path, iter_): vendor_id = self.vendor_store[iter_][0] vendor_name = self.vendor_store[iter_][1] _iter = self.p_o_store.get_iter(path) product_id = self.p_o_store[_iter][2] self.p_o_store[_iter][11] = int(vendor_id) self.p_o_store[_iter][12] = vendor_name self.update_line_item_vendor (_iter, vendor_id) self.save_product (_iter, product_id) def focus (self, widget, event): self.focusing = True self.populate_product_store () self.populate_vendor_store () self.focusing = False def vendor_combo_populate_popup (self, entry, menu): separator = Gtk.SeparatorMenuItem () separator.show () menu.prepend(separator) contact_hub_menu = Gtk.MenuItem.new_with_label("Contact hub") contact_hub_menu.connect("activate", self.contact_hub_clicked) contact_hub_menu.show() menu.prepend(contact_hub_menu) def contact_hub_clicked (self, menuitem): if self.vendor_id != 0: import contact_hub contact_hub.ContactHubGUI(self.vendor_id) def treeview_button_release_event (self, treeview, event): if event.button == 3: menu = self.get_object('p_o_item_menu') menu.popup_at_pointer() def product_hub_activated (self, menuitem): selection = self.get_object('treeview-selection') model, path = selection.get_selected_rows() if path == []: return product_id = model[path][2] import product_hub product_hub.ProductHubGUI(product_id) def move_up_activated (self, menuitem): selection = self.get_object('treeview-selection') model, path = selection.get_selected_rows() if path == []: return iter_ = model.get_iter(path) iter_prev = model.iter_previous(iter_) if iter_prev == None: return model.swap(iter_, iter_prev) self.save_row_ordering() def move_down_activated (self, menuitem): selection = self.get_object('treeview-selection') model, path = selection.get_selected_rows() if path == []: return iter_ = model.get_iter(path) iter_next = model.iter_next(iter_) if iter_next == None: return model.swap(iter_, iter_next) self.save_row_ordering() def save_row_ordering (self): for row_count, row in enumerate (self.p_o_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 update_line_item_vendor (self, _iter, vendor_id): row_id = self.p_o_store[_iter][0] self.cursor.execute("SELECT po.id, c.name FROM purchase_orders AS po " "JOIN contacts AS c ON c.id = vendor_id " "WHERE vendor_id = %s " "AND (paid, closed, canceled) = " "(False, False, False)", (vendor_id, )) for row in self.cursor.fetchall() : # check for active PO purchase_order_id = row[0] vendor_name = row[1] break else: self.cursor.execute("SELECT name FROM contacts WHERE id = %s", (vendor_id,)) vendor_name = self.cursor.fetchone()[0] self.cursor.execute("INSERT INTO purchase_orders " "( vendor_id, closed, paid, canceled, " "received, date_created) " "VALUES (%s, %s, %s, %s, %s, CURRENT_DATE) " "RETURNING id, date_created", (vendor_id, False, False, False, False)) for row in self.cursor.fetchall(): purchase_order_id = row[0] date = row[1] name_str = "" for i in vendor_name.split(' '): name_str = name_str + i[0:3] name = name_str.lower() po_date = re.sub("-", "_", str(date)) document_name = "PO_" + str(purchase_order_id) + "_" + name + "_" + po_date self.cursor.execute("UPDATE purchase_orders " "SET name = %s WHERE id = %s", (document_name, purchase_order_id)) self.p_o_store[_iter][11] = int(vendor_id) self.p_o_store[_iter][12] = vendor_name self.p_o_store[_iter][13] = purchase_order_id self.cursor.execute("UPDATE purchase_order_items " "SET purchase_order_id = %s " "WHERE id = %s", (purchase_order_id, row_id)) DB.commit() def products_activated (self, column): import products_overview products_overview.ProductsOverviewGUI() def populate_vendor_store (self, m=None, i=None): self.populating = True name_combo = self.get_object('combobox1') active_customer = name_combo.get_active() self.vendor_store.clear() cursor = DB.cursor() cursor.execute("SELECT " "id::text, " "name, " "COALESCE((SELECT name FROM purchase_orders " "WHERE (closed, canceled) = (False, False) " "AND vendor_id = c_outer.id LIMIT 1), 'No PO')" "FROM contacts AS c_outer " "WHERE (deleted, vendor) " "= (False, True) ORDER BY name") for row in cursor.fetchall(): self.vendor_store.append(row) cursor.close() DB.rollback() self.populating = False def contacts_window(self, widget): import contacts_overview c = contacts_overview.ContactsOverviewGUI () c.get_object('radiobutton2').set_active(True) def vendor_match_func(self, completion, key, iter): split_search_text = key.split() for text in split_search_text: if text not in self.vendor_store[iter][1].lower(): return False return True def view_purchase_order(self, widget): comment = self.get_object('entry2').get_text() purchase_order = purchase_ordering.Setup( self.vendor_id, comment, self.datetime, self.purchase_order_id) purchase_order.view() def post_and_process(self, widget): self.post_purchase_order () import unprocessed_po unprocessed_po.GUI () def post_purchase_order(self, widget = None): cursor = DB.cursor() cursor.execute("SELECT " "pg_try_advisory_lock(id) " "FROM purchase_orders " "WHERE id = %s ", (self.purchase_order_id, )) for row in cursor.fetchall(): if row[0] == False: self.show_message("Somebody else is still accessing this PO") return comment = self.get_object('entry2').get_text() purchase_order = purchase_ordering.Setup(self.vendor_id, comment, self.datetime, self.purchase_order_id) if self.get_object('menuitem1').get_active() == True: result = purchase_order.print_directly() else: result = purchase_order.print_dialog(self.window) purchase_order.post(self.purchase_order_id, self.vendor_id, self.datetime) hold = False for row in self.p_o_store: if row[14] == True: hold = True break if hold == True: # create new po and show it old_purchase_id = self.purchase_order_id self.purchase_order_id = None self.check_po_id () cursor.execute ("UPDATE purchase_order_items " "SET (purchase_order_id, hold) = (%s, False) " "WHERE (purchase_order_id, hold) = " "(%s, True) RETURNING id", (self.purchase_order_id, old_purchase_id)) DB.commit() self.populate_purchase_order_items () else: #no products held DB.commit() self.window.destroy () cursor.close() def vendor_match_selected(self, completion, model, iter): vendor_id = model[iter][0] self.select_vendor (vendor_id) def vendor_combobox_changed(self, widget, toggle_button = None): if self.focusing == True: return vendor_id = widget.get_active_id() if vendor_id != None: self.select_vendor(vendor_id) def unlock_po (self): if self.purchase_order_id: self.cursor.execute("SELECT " "pg_advisory_unlock_shared(id) " "FROM purchase_orders " "WHERE id = %s ", (self.purchase_order_id, )) DB.commit() def select_vendor (self, vendor_id): self.p_o_store.clear() self.get_object ('checkbutton1').set_active(False) if vendor_id != None and self.populating == False: self.unlock_po() self.vendor_id = vendor_id self.get_object('button2').set_sensitive(True) self.get_object('button3').set_sensitive(True) self.get_object('menuitem5').set_sensitive(True) self.get_object('menuitem2').set_sensitive(True) self.cursor.execute("SELECT * FROM " "(SELECT " "po.id, " "po.date_created, " "format_date(po.date_created), " "c.phone, " "po.name, " "pg_try_advisory_lock_shared(po.id) AS lock " "FROM purchase_orders AS po " "JOIN contacts AS c ON c.id = po.vendor_id " "WHERE vendor_id = %s " "AND (paid, closed, canceled) = " "(False, False, False)) s " "WHERE lock = True", (vendor_id, )) for row in self.cursor.fetchall() : # check for active PO self.purchase_order_id = row[0] self.datetime = row[1] self.get_object('entry1').set_text(row[2]) self.get_object('entry8').set_text(row[3]) self.get_object('po_name_entry').set_text(row[4]) self.get_object('po_number_entry').set_text(str(row[0])) self.populate_purchase_order_items () break else: self.cursor.execute("SELECT " "CURRENT_DATE, " "format_date(CURRENT_DATE), " "phone " "FROM contacts WHERE id = %s", (vendor_id,)) for row in self.cursor.fetchall() : self.purchase_order_id = None self.datetime = row[0] self.get_object('entry1').set_text(row[1]) self.get_object('entry8').set_text(row[2]) self.get_object('po_name_entry').set_text('') self.get_object('po_number_entry').set_text('') self.calculate_totals () DB.rollback() def editing_canceled (self, cellrenderer): "all widgets need to connect to this function to release row locks" "removing row locks is as simple as doing a rollback or commit" "all rows need to be locked whenever a widget is opened to " "edit an invoice row" DB.rollback() #remove row lock by rolling back ################## start qty def qty_editing_started (self, cellrenderer, celleditable, path): row_id = self.p_o_store[path][0] cursor = DB.cursor() try: cursor.execute("SELECT qty::text FROM purchase_order_items " "WHERE id = %s FOR UPDATE NOWAIT", (row_id,)) except psycopg2.OperationalError as e: DB.rollback() cursor.close() error = str(e) + "Somebody else is editing this row" self.show_message (error) celleditable.destroy() return False for row in cursor.fetchall(): celleditable.set_text(row[0]) cursor.close() def qty_edited(self, widget, path, text): cursor = DB.cursor() _iter = self.p_o_store.get_iter (path) self.check_po_item_id (_iter) line_id = self.p_o_store[_iter][0] try: cursor.execute("UPDATE purchase_order_items " "SET (qty, ext_price) = (%s, %s * price) " "WHERE id = %s " "RETURNING qty::text, ext_price::text", (text, text, line_id)) except psycopg2.DataError as e: self.show_message (str(e)) DB.rollback() return for row in cursor.fetchall(): qty = row[0] ext_price = row[1] self.p_o_store[_iter][1] = qty self.p_o_store[_iter][9] = ext_price DB.commit() self.calculate_totals () ################## start order number def order_number_editing_started (self, renderer, entry, path): row_id = self.p_o_store[path][0] cursor = DB.cursor() try: cursor.execute("SELECT order_number FROM purchase_order_items " "WHERE id = %s FOR UPDATE NOWAIT", (row_id,)) except psycopg2.OperationalError as e: DB.rollback() cursor.close() error = str(e) + "Somebody else is editing this row" self.show_message (error) entry.destroy() return False for row in cursor.fetchall(): entry.set_text(row[0]) cursor.close() entry.set_completion(self.order_number_completion) self.path = path def order_number_edited(self, widget, path, text): order_number = text row_id = self.p_o_store[path][0] product_id = self.p_o_store[path][2] if product_id == 0: self.show_message ("Please select a product first.\n" "Alternatively, you can type in a " "partial order number\nand select an " "order number from the popup.") return if order_number != self.p_o_store[path][3]: self.show_temporary_permanent_dialog(order_number, product_id) else: return # order number not updated self.p_o_store[path][3] = order_number self.cursor.execute("UPDATE purchase_order_items " "SET order_number = %s WHERE id = %s", (order_number, row_id)) DB.commit() def order_number_match_selected (self, completion, store, _iter): product_id = store[_iter][0] _iter = self.p_o_store.get_iter(self.path) self.save_product (_iter, product_id) def show_temporary_permanent_dialog (self, order_number, product_id): if self.get_object('checkbutton2').get_active() == True: if self.order_number_response == 1: self.update_vendor_order_number (order_number, product_id) return # user selected to always have the same action dialog = self.get_object('temp_permanent_dialog') self.order_number_response = dialog.run() dialog.hide() if self.order_number_response == 1: self.update_vendor_order_number (order_number, product_id) def update_vendor_order_number (self, order_number, product_id): if self.vendor_id == 0: return cursor = DB.cursor() cursor.execute("INSERT INTO vendor_product_numbers AS vpn " "(vendor_sku, " "vendor_id, " "product_id) " "VALUES (%s, %s, %s) " "ON CONFLICT (vendor_id, product_id) " "DO UPDATE SET " "vendor_sku = %s " "WHERE (vpn.vendor_id, vpn.product_id) = (%s, %s)", (order_number, self.vendor_id, product_id, order_number, self.vendor_id, product_id)) cursor.close() DB.commit() ################## start remark def remark_edited(self, widget, path, text): _iter = self.p_o_store.get_iter(path) self.p_o_store[_iter][7] = text row_id = self.p_o_store[_iter][0] cursor = DB.cursor() cursor.execute("UPDATE purchase_order_items SET remark = %s " "WHERE id = %s", (text, row_id)) cursor.close() DB.commit() def remark_editing_started (self, cellrenderer, entry, path): row_id = self.p_o_store[path][0] cursor = DB.cursor() try: cursor.execute("SELECT remark FROM purchase_order_items " "WHERE id = %s FOR UPDATE NOWAIT", (row_id,)) except psycopg2.OperationalError as e: DB.rollback() cursor.close() error = str(e) + "Somebody else is editing this row" self.show_message (error) entry.destroy() return False for row in cursor.fetchall(): entry.set_text(row[0]) cursor.close() ################## end remark def product_renderer_changed (self, widget, path, iter_): product_id = self.product_store[iter_][0] _iter = self.p_o_store.get_iter(path) self.save_product (_iter, int(product_id)) def product_renderer_editing_started (self, renderer, combo, path): combo.connect('remove-widget', self.product_widget_removed, path) entry = combo.get_child() row_id = self.p_o_store[path][0] cursor = DB.cursor() try: cursor.execute("SELECT p.name " "FROM purchase_order_items AS poi " "JOIN products AS p ON p.id = poi.product_id " "WHERE poi.id = %s " "FOR UPDATE OF poi NOWAIT", (row_id,)) except psycopg2.OperationalError as e: DB.rollback() cursor.close() error = str(e) + "Somebody else is editing this row" self.show_message (error) combo.destroy() return False for row in cursor.fetchall(): entry.set_text(row[0]) completion = self.get_object("product_completion") entry.set_completion(completion) def populate_account_store (self): self.expense_account_store.clear() self.revenue_account_store.clear() self.cursor.execute("SELECT number::text, name FROM gl_accounts " "WHERE expense_account = True ORDER BY name") for row in self.cursor.fetchall(): self.expense_account_store.append(row) self.cursor.execute("SELECT number::text, name FROM gl_accounts " "WHERE revenue_account = True ORDER BY name") for row in self.cursor.fetchall(): self.revenue_account_store.append(row) DB.rollback() def create_product_widgets_changed (self, widget): self.get_object ('button1').set_sensitive(False) product_name = self.get_object ('entry4').get_text() if product_name == '': return # no product name else: self.cursor.execute("SELECT id FROM products " "WHERE name = %s", (product_name,)) for row in self.cursor.fetchall(): self.get_object('label15').set_visible(True) break else: self.get_object('label15').set_visible(False) order_number = self.get_object ('entry3').get_text() if order_number == '': return # no order number else: self.cursor.execute("SELECT name FROM vendor_product_numbers " "JOIN products " "ON products.id = " "vendor_product_numbers.product_id " "WHERE (vendor_sku, vendor_id) = " "(%s, %s)", (order_number, self.vendor_id)) for row in self.cursor.fetchall(): product_name = row[0] self.get_object('label13').set_text(product_name) self.get_object('box8').set_visible(True) break else: self.get_object('box8').set_visible(False) if self.get_object ('combobox2').get_active_id() == None: return # no expense account if self.get_object ('combobox3').get_active_id() == None: return # no income account self.get_object ('button1').set_sensitive(True) DB.rollback() def product_widget_removed (self, combo, path): _iter = self.p_o_store.get_iter(path) entry = combo.get_child() product_text = entry.get_text() self.populate_account_store () self.get_object ('entry4').set_text(product_text) dialog = self.get_object ('non_stock_product_dialog') result = dialog.run () self.get_object('box8').set_visible(False) self.get_object('label15').set_visible(False) dialog.hide () product_name = self.get_object ('entry4').get_text() product_number = self.get_object ('entry3').get_text() expense_account = self.get_object ('combobox2').get_active_id() revenue_account = self.get_object ('combobox3').get_active_id() if result == Gtk.ResponseType.ACCEPT: product_id = add_non_stock_product( self.vendor_id, product_name, product_number, expense_account, revenue_account) self.p_o_store[_iter][2] = product_id self.save_product (_iter, product_id) self.calculate_totals () self.get_object ('entry3').set_text('') self.get_object ('button1').set_sensitive(False) def product_edited (self, cellrenderertext, text, path): "Posting does not allow product names to be edited directly" DB.rollback () # remove row lock, see editing_canceled def save_product (self, _iter, product_id): if self.check_for_duplicate_products (product_id, _iter) == True: DB.rollback() # remove row lock, see editing_canceled return # duplicate product, skip the rest of the code self.save_product_without_duplicate_check (_iter, product_id) # retrieve path again after all sorting has happened for the updates path = self.p_o_store.get_path(_iter) treeview = self.get_object('treeview2') c = treeview.get_column(5) treeview.set_cursor(path, c, True) def save_product_without_duplicate_check(self, _iter, product_id): cursor = DB.cursor() row_id = self.p_o_store[_iter][0] vendor_id = self.p_o_store[_iter][11] self.p_o_store[_iter][2] = int(product_id) cursor.execute("WITH p_info AS " "(SELECT " "name, " "cost, " "ext_name, " "stock, " "COALESCE(vendor_sku, '') AS vendor_sku " "FROM products AS p " "LEFT JOIN vendor_product_numbers AS vpn " "ON vpn.product_id = p.id AND vendor_id = %s" "WHERE p.id = %s), " "poi_update AS " "(UPDATE purchase_order_items " "SET " "(product_id, " "price, " "ext_price, " "order_number, " "expense_account " ") " "= " "(%s, " "(SELECT cost FROM p_info), " "qty * (SELECT cost FROM p_info), " "(SELECT vendor_sku FROM p_info), " "(SELECT default_expense_account " "FROM products WHERE id = %s)" ") " "WHERE id = %s RETURNING ext_price" ") " "SELECT " "name, " "cost::text, " "(SELECT ext_price FROM poi_update)::text, " "ext_name, " "stock, " "vendor_sku " "FROM p_info", (self.vendor_id, product_id, product_id, product_id, row_id)) for row in cursor.fetchall(): name = row[0] price = row[1] ext_price = row[2] ext_name = row[3] stock = row[4] order_number = row[5] self.p_o_store[_iter][3] = order_number self.p_o_store[_iter][4] = stock self.p_o_store[_iter][5] = name self.p_o_store[_iter][6] = ext_name self.p_o_store[_iter][8] = price self.p_o_store[_iter][9] = ext_price cursor.close() DB.commit() self.calculate_totals () def product_match_string(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 = model[iter_][0] product_name = model[iter_][1] selection = self.get_object('treeview-selection') model, path_list = selection.get_selected_rows() path = path_list[0].to_string() _iter = self.p_o_store.get_iter(path) self.save_product (_iter, product_id) def check_for_duplicate_products(self, product_id, _iter ): path = self.p_o_store.get_path (_iter) for row in self.p_o_store: if row.path == path: continue # continue with the rest of the liststore if product_id == row[2]: # the liststore has duplicates product_name = row[5] self.get_object('label5').set_label(product_name) qty = row[1] qty_spinbutton = self.get_object('spinbutton1') qty_spinbutton.set_value(int(qty)) dialog = self.get_object('duplicate_product_dialog') result = dialog.run() if result == Gtk.ResponseType.ACCEPT : row_id = row[0] qty = qty_spinbutton.get_text() self.cursor.execute("UPDATE purchase_order_items " "SET qty = %s WHERE id = %s", (qty, row_id)) DB.commit() self.calculate_totals () self.delete_item_activated () elif result == Gtk.ResponseType.REJECT: self.save_product_without_duplicate_check(_iter, product_id) dialog.hide() return True return False def check_po_item_id (self, _iter): line = self.p_o_store[_iter] row_id = line[0] if row_id != 0: return # we have a valid id cursor = DB.cursor() qty = line[1] product_id = line[2] order_number = line[3] remark = line[7] price = line[8] ext_price = line[9] line[10] = False purchase_order_id = line[13] cursor.execute("INSERT INTO purchase_order_items " "(purchase_order_id, " "qty, " "product_id, " "remark, " "price, " "ext_price, " "canceled, " "expense_account, " "order_number) " "VALUES " "(%s, " "%s, " "%s, " "%s, " "%s, " "%s, " "%s, " "(SELECT default_expense_account " "FROM products WHERE id = %s), " "%s) " "RETURNING id", (purchase_order_id, qty, product_id, remark, price, ext_price, False, product_id, order_number)) row_id = cursor.fetchone()[0] line[0] = row_id cursor.close() DB.commit() self.calculate_totals () def calculate_totals(self): cursor = DB.cursor() cursor.execute("SELECT COALESCE(SUM(ext_price), 0.0)::money " "FROM purchase_order_items " "WHERE purchase_order_id = %s", (self.purchase_order_id,)) for row in cursor.fetchall(): total = row[0] self.get_object('entry5').set_text(total) rows = len(self.p_o_store) self.get_object('rows_entry').set_text(str(rows)) def check_po_id (self): if self.purchase_order_id == None: comment = self.get_object('entry2').get_text() self.cursor.execute("INSERT INTO purchase_orders " "(name, " "vendor_id, " "closed, " "paid, " "canceled, " "received, " "date_created) " "VALUES " "(%s, %s, False, False, False, False, %s) " "RETURNING id", ("", self.vendor_id, self.datetime )) po_id = self.cursor.fetchone()[0] self.purchase_order_id = po_id self.get_object('po_name_entry').set_text('') self.get_object('po_number_entry').set_text(str(po_id)) def new_entry_clicked (self, button): _iter = self.add_entry () treeview = self.get_object('treeview2') c = treeview.get_column(0) path = self.p_o_store.get_path(_iter) treeview.set_cursor(path, c, True) def add_entry (self): self.check_po_id () self.cursor.execute("SELECT id, name " "FROM products " "WHERE (deleted, purchasable, stock) = " "(False, True, True) " "ORDER BY id, name " "LIMIT 1") for i in self.cursor.fetchall(): product_id = i[0] product_name = i[1] _iter = self.p_o_store.append([0, '1', product_id, "Select order number", False, "Select a stock item" , "", "", '1', '1', True, int(self.vendor_id), '', self.purchase_order_id, False]) self.check_po_item_id (_iter) DB.commit() return _iter def delete_item_activated (self, menuitem = None): selection = self.get_object("treeview-selection") model, path = selection.get_selected_rows () if path != []: line_id = model[path][0] self.cursor.execute("DELETE FROM purchase_order_items " "WHERE id = %s", (line_id,)) DB.commit() self.populate_purchase_order_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 window_key_event(self, window, event): keyname = Gdk.keyval_name(event.keyval) if event.get_state() & Gdk.ModifierType.CONTROL_MASK: #Ctrl held down if keyname == "h": self.product_hub_activated (None) elif keyname == "Down": self.move_down_activated (None) elif keyname == "Up": self.move_up_activated (None) elif keyname == 'F1': self.help_clicked (None) elif keyname == 'F2': self.add_entry() elif keyname == 'F3': self.delete_entry_activated () def barcode_entry_key_released (self, entry, event): keyname = Gdk.keyval_name(event.keyval) if keyname != 'Return' and keyname != "KP_Enter": # enter key(s) if event.get_state() & Gdk.ModifierType.CONTROL_MASK: # process keypresses with CTRL held down entry.delete_selection() position = entry.get_position() number = re.sub("[^0-9]", "", keyname) entry.insert_text(number, position) entry.set_position(position + 1) return barcode = entry.get_text() if barcode == "": return # blank barcode self.cursor.execute("SELECT id FROM products " "WHERE barcode = %s", (barcode,)) for row in self.cursor.fetchall(): product_id = row[0] break else: self.process_missing_barcode (barcode) return if event.get_state() & Gdk.ModifierType.SHIFT_MASK: #shift held down entry.select_region(0,-1) for index, row in enumerate(self.p_o_store): if row[2] == product_id: row[1] -= 1 self.save_purchase_order_line (index) break elif event.get_state() & Gdk.ModifierType.CONTROL_MASK: #ctrl held down entry.select_region(0,-1) selection = self.get_object('treeview-selection') model, path = selection.get_selected_rows() if path == []: return _iter = self.p_o_store.get_iter(path) self.save_product (_iter, product_id) else: entry.select_region(0,-1) self.add_product (product_id) def process_missing_barcode (self, barcode): 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.get_object('entry10').grab_focus() barcode_error_dialog = self.get_object('barcode_error_dialog') barcode_error_dialog.run() barcode_error_dialog.hide() def add_product (self, product_id): for index, row in enumerate(self.p_o_store): if row[2] == product_id: row[1] += 1 self.save_purchase_order_line (index) break continue else: _iter = self.add_entry () self.save_product (_iter, product_id) def calendar_day_selected (self, calendar): self.datetime = calendar.get_date() day_text = calendar.get_text() self.get_object('entry1').set_text(day_text) def calendar_entry_icon_release (self, widget, icon, void): self.calendar.set_relative_to (widget) self.calendar.show() def po_name_icon_release (self, entry, entryiconposition, event): po_name = entry.get_text() self.cursor.execute("UPDATE purchase_orders SET name = %s " "WHERE id = %s", (po_name, self.purchase_order_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() def show_reload_infobar (self, broadcaster, po_id): if po_id == self.purchase_order_id: infobar = self.get_object('po_changed_infobar') infobar.set_revealed(True) def info_bar_close (self, infobar): infobar.set_revealed(False) def info_bar_response (self, infobar, response): if response == Gtk.ResponseType.APPLY: self.populate_purchase_order_items () infobar.set_revealed(False)
class PurchaseOrderGUI: def __init__(self, main, edit_po_id=None): self.purchase_order_id = None self.vendor_id = 0 #self.contact_id_from_existing = contact_id_from_existing self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.edited_renderer_text = 1 self.qty_renderer_value = 1 self.focusing = False self.menu_visible = False self.order_number_completion = self.builder.get_object( 'order_number_completion') self.order_number_store = self.builder.get_object('order_number_store') self.revenue_account_store = self.builder.get_object( 'revenue_account_store') self.expense_account_store = self.builder.get_object( 'expense_account_store') self.p_o_store = self.builder.get_object('purchase_order_store') self.vendor_store = self.builder.get_object('vendor_store') self.barcodes_not_found_store = self.builder.get_object( 'barcodes_not_found_store') vendor_completion = self.builder.get_object('vendor_completion') vendor_completion.set_match_func(self.vendor_match_func) self.main = main self.db = main.db self.cursor = self.db.cursor() self.handler_c_id = main.connect("contacts_changed", self.populate_vendor_store) self.handler_p_id = main.connect("products_changed", self.populate_product_store) self.calendar = DateTimeCalendar(self.db) self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() self.cursor.execute( "SELECT qty_prec, price_prec FROM settings.purchase_order") for row in self.cursor.fetchall(): qty_prec = row[0] price_prec = row[1] self.qty_places = Decimal(10)**-qty_prec self.price_places = Decimal(10)**-price_prec self.product_store = self.builder.get_object('product_store') product_completion = self.builder.get_object('product_completion') product_completion.set_match_func(self.product_match_string) self.populate_product_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.drag_dest_set_target_list([enforce_target]) self.populate_vendor_store() if edit_po_id != None: self.cursor.execute( "SELECT name, vendor_id FROM purchase_orders " "WHERE id = %s", (edit_po_id, )) for row in self.cursor.fetchall(): po_name = row[0] self.vendor_id = row[1] self.builder.get_object('combobox1').set_active_id( str(self.vendor_id)) self.builder.get_object('button2').set_sensitive(True) self.builder.get_object('button3').set_sensitive(True) self.builder.get_object('menuitem5').set_sensitive(True) self.builder.get_object('menuitem2').set_sensitive(True) self.purchase_order_id = int(edit_po_id) self.products_from_existing_po() qty_column = self.builder.get_object('treeviewcolumn1') qty_renderer = self.builder.get_object('cellrenderertext') qty_column.set_cell_data_func(qty_renderer, self.qty_cell_func) price_column = self.builder.get_object('treeviewcolumn6') price_renderer = self.builder.get_object('cellrenderertext6') price_column.set_cell_data_func(price_renderer, self.price_cell_func) ext_price_column = self.builder.get_object('treeviewcolumn7') ext_price_renderer = self.builder.get_object('cellrenderertext7') ext_price_column.set_cell_data_func(ext_price_renderer, self.ext_price_cell_func) self.tax = 0 self.cursor.execute("SELECT print_direct FROM settings") self.builder.get_object('menuitem1').set_active( self.cursor.fetchone()[0]) #set the direct print checkbox self.window = self.builder.get_object('window') self.window.show_all() GLib.idle_add(self.load_settings) def load_settings(self): self.cursor.execute( "SELECT column_id, visible FROM settings.po_columns") for row in self.cursor.fetchall(): column_id = row[0] visible = row[1] self.builder.get_object(column_id).set_visible(visible) def destroy(self, window): self.main.disconnect(self.handler_c_id) self.main.disconnect(self.handler_p_id) self.cursor.close() 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.vendor_id == 0: return qty, product_id = list_[0], list_[1] _iter = self.p_o_store.append([ 0, Decimal(qty), int(product_id), '', True, '', '', '', Decimal(0.0), Decimal(0.0), True, int(self.vendor_id), '', self.purchase_order_id, False ]) self.product_edited(_iter, product_id) def export_to_csv_activated(self, menuitem): import csv dialog = self.builder.get_object('filechooserdialog1') uri = os.path.expanduser('~') dialog.set_current_folder_uri("file://" + uri) dialog.set_current_name("untitled.csv") response = dialog.run() dialog.hide() if response != Gtk.ResponseType.ACCEPT: return selected_file = dialog.get_filename() with open(selected_file, 'w') as csvfile: exportfile = csv.writer(csvfile, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL) self.cursor.execute( "SELECT qty, name, order_number, price, " "ext_price " "FROM purchase_order_line_items AS poli " "JOIN products ON poli.product_id = products.id " "WHERE (purchase_order_id, hold) = (%s, False) " "ORDER BY poli.id", (self.purchase_order_id, )) for row in self.cursor.fetchall(): exportfile.writerow(row) def help_clicked(self, widget): subprocess.Popen(["yelp", main.help_dir + "/purchase_order.page"]) def view_all_toggled(self, checkbutton): self.products_from_existing_po() def hold_togglebutton_toggled(self, togglebutton, path): active = not togglebutton.get_active() row_id = self.p_o_store[path][0] self.p_o_store[path][14] = active self.cursor.execute( "UPDATE purchase_order_line_items " "SET hold = %s WHERE id = %s", (active, row_id)) self.db.commit() self.calculate_totals() def products_from_existing_po(self): self.p_o_store.clear() if self.builder.get_object('checkbutton1').get_active() == True: self.builder.get_object('treeviewcolumn11').set_visible(True) self.cursor.execute( "SELECT poli.id, poli.qty, poli.remark, " "poli.price, poli.product_id, poli.expense_account, " "products.name, products.ext_name, products.stock, " "COALESCE(order_number, vendor_sku, 'No sku'), " "po.vendor_id, c.name, po.id, poli.hold " "FROM purchase_order_line_items AS poli " "JOIN products ON products.id = poli.product_id " "JOIN purchase_orders AS po " "ON po.id = poli.purchase_order_id " "JOIN contacts AS c ON c.id = po.vendor_id " "LEFT JOIN vendor_product_numbers AS vpn " "ON (vpn.vendor_id, vpn.product_id) " "= (poli.product_id, po.vendor_id) " "WHERE (po.canceled, po.closed, po.paid) = " "(False, False, False) ORDER BY poli.id") else: self.builder.get_object('treeviewcolumn11').set_visible(False) self.cursor.execute( "SELECT poli.id, poli.qty, poli.remark, " "poli.price, poli.product_id, poli.expense_account, " "products.name, products.ext_name, products.stock, " "COALESCE(order_number, vendor_sku, 'No sku'), " "po.vendor_id, c.name, po.id, poli.hold " "FROM purchase_order_line_items AS poli " "JOIN products ON products.id = poli.product_id " "JOIN purchase_orders AS po " "ON po.id = poli.purchase_order_id " "JOIN contacts AS c ON c.id = po.vendor_id " "LEFT JOIN vendor_product_numbers AS vpn " "ON (vpn.vendor_id, vpn.product_id) " "= (poli.product_id, po.vendor_id) " "WHERE purchase_order_id = %s ORDER BY poli.id", (self.purchase_order_id, )) for row in self.cursor.fetchall(): row_id = row[0] qty = row[1] remark = row[2] cost = row[3] ext_cost = qty * cost product_id = row[4] expense_account = row[5] product_name = row[6] ext_name = row[7] stock = row[8] order_number = row[9] vendor_id = row[10] vendor_name = row[11] purchase_order_id = row[12] hold = row[13] self.p_o_store.append([ row_id, qty, product_id, order_number, stock, product_name, ext_name, remark, cost, ext_cost, False, vendor_id, vendor_name, purchase_order_id, hold ]) self.calculate_totals() 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, purchasable, stock) = " "(False, True, True) ORDER BY name") for row in self.cursor.fetchall(): product_id = row[0] name = row[1] ext_name = row[2] self.product_store.append( [str(product_id), "%s {%s}" % (name, ext_name)]) self.order_number_store.clear() self.cursor.execute( "SELECT product_id, vendor_sku " "FROM vendor_product_numbers " "WHERE vendor_id = %s", (self.vendor_id, )) for row in self.cursor.fetchall(): product_id = row[0] order_number = row[1] self.order_number_store.append([product_id, order_number]) def line_items_treeview_vendor_changed(self, combo, path, iter_): vendor_id = self.vendor_store[iter_][0] vendor_name = self.vendor_store[iter_][1] _iter = self.p_o_store.get_iter(path) product_id = self.p_o_store[_iter][2] self.p_o_store[_iter][11] = int(vendor_id) self.p_o_store[_iter][12] = vendor_name self.update_line_item_vendor(_iter, vendor_id) self.save_product(_iter, product_id) def focus(self, widget, event): self.focusing = True self.populate_product_store() self.populate_vendor_store() self.focusing = False def treeview_button_release_event(self, treeview, event): if event.button == 3 and self.menu_visible == False: menu = self.builder.get_object('right_click_menu') menu.popup(None, None, None, None, event.button, event.time) menu.show_all() self.menu_visible = True else: self.menu_visible = False 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 update_line_item_vendor(self, _iter, vendor_id): row_id = self.p_o_store[_iter][0] self.cursor.execute( "SELECT id FROM purchase_orders " "WHERE vendor_id = (%s) " "AND (paid, closed, canceled) = " "(False, False, False)", (vendor_id, )) for row in self.cursor.fetchall(): # check for active PO purchase_order_id = row[0] break else: self.cursor.execute("SELECT name FROM contacts WHERE id = %s", (vendor_id, )) vendor_name = self.cursor.fetchone()[0] self.cursor.execute( "INSERT INTO purchase_orders " "( vendor_id, closed, paid, canceled, " "received, date_created) " "VALUES ( %s, %s, %s, %s, %s, CURRENT_DATE) " "RETURNING id, date_created", (vendor_id, False, False, False, False)) for row in self.cursor.fetchall(): purchase_order_id = row[0] date = row[1] name_str = "" for i in vendor_name.split(' '): name_str = name_str + i[0:3] name = name_str.lower() po_date = re.sub("-", "_", str(date)) document_name = "PO_" + str( purchase_order_id) + "_" + name + "_" + po_date self.cursor.execute( "UPDATE purchase_orders " "SET name = %s WHERE id = %s", (document_name, purchase_order_id)) self.p_o_store[_iter][13] = purchase_order_id self.db.commit() def products_activated(self, column): import products products.ProductsGUI(self.main) def populate_vendor_store(self, m=None, i=None): self.populating = True name_combo = self.builder.get_object('combobox1') active_customer = name_combo.get_active() self.vendor_store.clear() self.cursor.execute("SELECT id, name FROM contacts " "WHERE (deleted, vendor) = " "(False, True) ORDER BY name") for i in self.cursor.fetchall(): vendor_id = i[0] name = i[1] po = "No active purchase order" self.cursor.execute( "SELECT name, COALESCE(total, 0.00) " "FROM purchase_orders " "WHERE (canceled, paid, closed) = " "(False, False, False) AND vendor_id = %s", [str(vendor_id)]) unpaid_balance = 0 for row in self.cursor.fetchall(): po = row[0] unpaid_balance = unpaid_balance + float(row[1]) unpaid = "Unpaid Balance: " + '${:,.2f}'.format(unpaid_balance) self.vendor_store.append([str(vendor_id), name, po]) self.populating = False def contacts_window(self, widget): import contacts c = contacts.GUI(self.main) c.builder.get_object('radiobutton2').set_active(True) def vendor_match_func(self, completion, key, iter): if key in self.vendor_store[iter][1].lower(): return True # it's a hit! return False # no match def view_purchase_order(self, widget): comment = self.builder.get_object('entry2').get_text() purchase_order = purchase_ordering.Setup(self.db, self.p_o_store, self.vendor_id, comment, self.datetime, self.purchase_order_id) purchase_order.view() def post_and_process(self, widget): self.post_purchase_order() import unprocessed_po unprocessed_po.GUI(self.main) def post_purchase_order(self, widget=None): comment = self.builder.get_object('entry2').get_text() purchase_order = purchase_ordering.Setup(self.db, self.p_o_store, self.vendor_id, comment, self.datetime, self.purchase_order_id) if self.builder.get_object('menuitem1').get_active() == True: purchase_order.print_directly() else: purchase_order.print_dialog(self.window) purchase_order.post(self.purchase_order_id, self.vendor_id, self.datetime) old_purchase_id = self.purchase_order_id self.purchase_order_id = 0 self.check_po_id() self.cursor.execute( "UPDATE purchase_order_line_items " "SET (purchase_order_id, hold) = (%s, False) " "WHERE (purchase_order_id, hold) = " "(%s, True) RETURNING id", (self.purchase_order_id, old_purchase_id)) if self.cursor.fetchone() == None: #no products held self.db.rollback() self.window.destroy() else: #new po created; show it self.db.commit() self.products_from_existing_po() def vendor_match_selected(self, completion, model, iter): vendor_id = model[iter][0] self.vendor_selected(vendor_id) def vendor_combobox_changed(self, widget, toggle_button=None): if self.focusing == True: return vendor_id = widget.get_active_id() if vendor_id != None: self.vendor_selected(vendor_id) def vendor_selected(self, vendor_id): self.p_o_store.clear() self.builder.get_object('checkbutton1').set_active(False) if vendor_id != None and self.populating == False: self.vendor_id = vendor_id self.cursor.execute("SELECT * FROM contacts WHERE id = (%s)", (vendor_id, )) for cell in self.cursor.fetchall(): self.builder.get_object('entry8').set_text(cell[8]) self.builder.get_object('button2').set_sensitive(True) self.builder.get_object('button3').set_sensitive(True) self.builder.get_object('menuitem5').set_sensitive(True) self.builder.get_object('menuitem2').set_sensitive(True) self.cursor.execute( "SELECT " "id, " "date_created, " "format_date(date_created) " "FROM purchase_orders " "WHERE vendor_id = (%s) " "AND (paid, closed, canceled) = " "(False, False, False)", (vendor_id, )) for row in self.cursor.fetchall(): # check for active PO self.purchase_order_id = row[0] self.datetime = row[1] self.builder.get_object('entry1').set_text(row[2]) self.products_from_existing_po() break else: self.cursor.execute("SELECT " "0, " "CURRENT_DATE, " "format_date(CURRENT_DATE) ") for row in self.cursor.fetchall(): self.purchase_order_id = row[0] self.datetime = row[1] self.builder.get_object('entry1').set_text(row[2]) self.calculate_totals() ################## start qty def qty_cell_func(self, column, cellrenderer, model, iter1, data): qty = model.get_value(iter1, 1) cellrenderer.set_property("text", str(qty)) def qty_edited(self, widget, path, text): t = Decimal(text).quantize(self.qty_places, rounding=ROUND_HALF_UP) _iter = self.p_o_store.get_iter(path) self.p_o_store[_iter][1] = t self.calculate_row_total(_iter) self.calculate_totals() self.save_purchase_order_line(_iter) ################## start order number def order_number_editing_started(self, renderer, entry, path): entry.set_completion(self.order_number_completion) self.path = path def order_number_edited(self, widget, path, text): order_number = text product_id = self.p_o_store[path][2] if product_id == 0: self.show_message("Please select a product first.\n" "Alternatively, you can type in a " "partial order number\nand select an " "order number from the popup.") return if order_number != self.p_o_store[path][3]: self.show_temporary_permanent_dialog(order_number, product_id) self.p_o_store[path][3] = order_number self.save_purchase_order_line(path) def order_number_match_selected(self, completion, store, _iter): product_id = store[_iter][0] _iter = self.p_o_store.get_iter(self.path) self.product_edited(_iter, product_id) def show_temporary_permanent_dialog(self, order_number, product_id): if self.builder.get_object('checkbutton2').get_active() == True: if self.order_number_response == 1: self.update_vendor_order_number(order_number, product_id) return dialog = self.builder.get_object('temp_permanent_dialog') self.order_number_response = dialog.run() dialog.hide() if self.order_number_response == 1: self.update_vendor_order_number(order_number, product_id) def update_vendor_order_number(self, order_number, product_id): if self.vendor_id == 0: return self.cursor.execute( "UPDATE vendor_product_numbers SET " "vendor_sku = %s WHERE (vendor_id, product_id) = " "(%s, %s) RETURNING id", (order_number, self.vendor_id, product_id)) for row in self.cursor.fetchall(): return #update successful self.cursor.execute( "INSERT INTO vendor_product_numbers " "(vendor_sku, vendor_id, product_id) " "VALUES (%s, %s, %s)", (order_number, self.vendor_id, product_id)) ################## start price def price_cell_func(self, column, cellrenderer, model, iter1, data): price = model.get_value(iter1, 8) cellrenderer.set_property("text", str(price)) def price_edited(self, widget, path, text): t = Decimal(text).quantize(self.price_places, rounding=ROUND_HALF_UP) _iter = self.p_o_store.get_iter(path) self.p_o_store[_iter][8] = t self.calculate_row_total(_iter) self.calculate_totals() self.save_purchase_order_line(_iter) ################## start remark def remark_edited(self, widget, path, text): _iter = self.p_o_store.get_iter(path) self.p_o_store[_iter][7] = text self.save_purchase_order_line(_iter) ################## end remark def ext_price_cell_func(self, column, cellrenderer, model, iter1, data): ext_cost = model.get_value(iter1, 9) cellrenderer.set_property("text", str(ext_cost)) def product_renderer_changed(self, widget, path, iter_): product_id = self.product_store[iter_][0] _iter = self.p_o_store.get_iter(path) self.product_edited(_iter, product_id) def product_renderer_editing_started(self, renderer, combo, path): completion = self.builder.get_object("product_completion") combo.connect('remove-widget', self.product_widget_removed, path) entry = combo.get_child() entry.set_completion(completion) def populate_account_store(self): self.expense_account_store.clear() self.revenue_account_store.clear() self.cursor.execute("SELECT number, name FROM gl_accounts " "WHERE expense_account = True ORDER BY name") for row in self.cursor.fetchall(): account_number = row[0] account_name = row[1] self.expense_account_store.append( [str(account_number), account_name]) self.cursor.execute("SELECT number, name FROM gl_accounts " "WHERE revenue_account = True ORDER BY name") for row in self.cursor.fetchall(): account_number = row[0] account_name = row[1] self.revenue_account_store.append( [str(account_number), account_name]) def create_product_widgets_changed(self, widget): self.builder.get_object('button1').set_sensitive(False) product_name = self.builder.get_object('entry4').get_text() if product_name == '': return # no product name else: self.cursor.execute("SELECT id FROM products " "WHERE name = %s", (product_name, )) for row in self.cursor.fetchall(): self.builder.get_object('label15').set_visible(True) break else: self.builder.get_object('label15').set_visible(False) order_number = self.builder.get_object('entry3').get_text() if order_number == '': return # no order number else: self.cursor.execute( "SELECT name FROM vendor_product_numbers " "JOIN products " "ON products.id = " "vendor_product_numbers.product_id " "WHERE (vendor_sku, vendor_id) = " "(%s, %s)", (order_number, self.vendor_id)) for row in self.cursor.fetchall(): product_name = row[0] self.builder.get_object('label13').set_text(product_name) self.builder.get_object('box8').set_visible(True) break else: self.builder.get_object('box8').set_visible(False) if self.builder.get_object('combobox2').get_active_id() == None: return # no expense account if self.builder.get_object('combobox3').get_active_id() == None: return # no income account self.builder.get_object('button1').set_sensitive(True) def product_widget_removed(self, combo, path): if self.p_o_store[path][4] == True: return _iter = self.p_o_store.get_iter(path) entry = combo.get_child() product_text = entry.get_text() self.populate_account_store() self.builder.get_object('entry4').set_text(product_text) dialog = self.builder.get_object('non_stock_product_dialog') result = dialog.run() self.builder.get_object('box8').set_visible(False) self.builder.get_object('label15').set_visible(False) dialog.hide() product_name = self.builder.get_object('entry4').get_text() product_number = self.builder.get_object('entry3').get_text() expense_account = self.builder.get_object('combobox2').get_active_id() revenue_account = self.builder.get_object('combobox3').get_active_id() if result == Gtk.ResponseType.ACCEPT: import products product_id = products.add_non_stock_product( self.db, self.vendor_id, product_name, product_number, expense_account, revenue_account) self.p_o_store[_iter][2] = product_id self.p_o_store[_iter][3] = product_number self.p_o_store[_iter][5] = product_name self.builder.get_object('entry3').set_text('') self.builder.get_object('button1').set_sensitive(False) self.calculate_row_total(_iter) self.calculate_totals() self.save_purchase_order_line(_iter) def product_edited(self, _iter, product_id): product_id = int(product_id) if self.check_for_duplicate_products(product_id, _iter) == True: return # skip the rest of the code self.save_product(_iter, product_id) def save_product(self, _iter, product_id): vendor_id = self.p_o_store[_iter][11] self.p_o_store[_iter][2] = product_id self.cursor.execute( "SELECT " "name, " "cost, " "ext_name, " "stock, " "COALESCE(vendor_sku, '') " "FROM products AS p " "LEFT JOIN vendor_product_numbers AS vpn " "ON vpn.product_id = p.id AND vendor_id = %s" "WHERE p.id = %s", (self.vendor_id, product_id)) for row in self.cursor.fetchall(): name = row[0] price = row[1] ext_name = row[2] stock = row[3] order_number = row[4] self.p_o_store[_iter][5] = name self.p_o_store[_iter][8] = price self.p_o_store[_iter][6] = ext_name self.p_o_store[_iter][4] = stock self.p_o_store[_iter][3] = order_number self.calculate_row_total(_iter) self.calculate_totals() self.save_purchase_order_line(_iter) def product_match_string(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 = model[iter_][0] product_name = model[iter_][1] model, path_list = self.builder.get_object( 'treeview-selection').get_selected_rows() path = path_list[0].to_string() _iter = self.p_o_store.get_iter(path) self.product_edited(_iter, product_id) def check_for_duplicate_products(self, product_id, _iter): path = self.p_o_store.get_path(_iter) for row in self.p_o_store: if row.path == path: continue # continue with the rest of the liststore if product_id == row[2]: # the liststore has duplicates product_name = row[5] self.builder.get_object('label5').set_label(product_name) qty = row[1] qty_spinbutton = self.builder.get_object('spinbutton1') qty_spinbutton.set_value(int(qty)) dialog = self.builder.get_object('duplicate_product_dialog') qty_spinbutton = self.builder.get_object('spinbutton1') qty = qty_spinbutton.get_text() result = dialog.run() if result == Gtk.ResponseType.ACCEPT: qty = qty_spinbutton.get_text() row[1] = int(qty) self.calculate_row_total(row) self.calculate_totals() self.save_purchase_order_line(row) self.delete_entry_activated() elif result == -4: self.save_product(_iter, product_id) dialog.hide() return True def calculate_row_total(self, _iter): line = self.p_o_store[_iter] price = line[8] qty = line[1] ext_price = price * qty line[9] = ext_price def save_purchase_order_line(self, _iter): line = self.p_o_store[_iter] if line[2] == 0: return # no valid product yet row_id = line[0] qty = line[1] product_id = line[2] order_number = line[3] remark = line[7] price = line[8] ext_price = line[9] line[10] = False purchase_order_id = line[13] #if a default expense account is available, use it self.cursor.execute( "SELECT default_expense_account FROM products WHERE id = %s", (product_id, )) for row in self.cursor.fetchall(): expense_account = row[0] break else: expense_account = None if row_id == 0: self.cursor.execute( "INSERT INTO purchase_order_line_items " "(purchase_order_id, qty, product_id, remark, " "price, ext_price, canceled, expense_account, " "order_number) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (purchase_order_id, qty, product_id, remark, price, ext_price, False, expense_account, order_number)) row_id = self.cursor.fetchone()[0] line[0] = row_id else: self.cursor.execute( "UPDATE purchase_order_line_items " "SET (purchase_order_id, qty, product_id, " "remark, price, ext_price, expense_account, " "order_number) = " "(%s, %s, %s, %s, %s, %s, %s, %s) " "WHERE id = %s", (purchase_order_id, qty, product_id, remark, price, ext_price, expense_account, order_number, row_id)) self.db.commit() self.calculate_totals() def calculate_totals(self): self.tax = 0 #we need to make a global variable with subtotal, tax, and total so we can store it to self.total = Decimal( ) #the database without all the fancy formatting (making it easier to retrieve later on) for row in self.p_o_store: if row[14] == False: self.total += row[9] total = '${:,.2f}'.format(self.total) self.builder.get_object('entry5').set_text(total) rows = len(self.p_o_store) self.builder.get_object('rows_entry').set_text(str(rows)) def check_po_id(self): if self.purchase_order_id == 0: comment = self.builder.get_object('entry2').get_text() self.cursor.execute( "INSERT INTO purchase_orders " "(name, vendor_id, closed, paid, canceled, " "received, date_created) " "VALUES ( %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", ("", self.vendor_id, False, False, False, False, self.datetime)) self.purchase_order_id = self.cursor.fetchone()[0] def new_entry_clicked(self, button): self.add_entry() self.select_new_entry() def add_entry(self): self.check_po_id() self.db.commit() self.p_o_store.append([ 0, Decimal(1.0), 0, "Select order number", False, "Select a stock item", "", "", Decimal(1), Decimal(1), True, int(self.vendor_id), '', self.purchase_order_id, False ]) def select_new_entry(self): treeview = self.builder.get_object('treeview2') for index, row in enumerate(self.p_o_store): if row[10] == True: c = treeview.get_column(0) treeview.set_cursor(index, c, True) break def delete_entry_activated(self, menuitem=None): model, path = self.builder.get_object( "treeview-selection").get_selected_rows() if path != []: line_id = model[path][0] self.cursor.execute( "DELETE FROM purchase_order_line_items " "WHERE id = %s", (line_id, )) self.db.commit() self.products_from_existing_po() 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 window_key_event(self, window, event): keyname = Gdk.keyval_name(event.keyval) if keyname == 'F1': self.help_clicked(None) if keyname == 'F2': self.add_entry() self.select_new_entry() if keyname == 'F3': self.delete_entry_activated() def barcode_entry_key_released(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 = %s", (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 index, row in enumerate(self.p_o_store): if row[2] == product_id: row[1] -= 1 self.save_purchase_order_line(index) break def barcode_entry_activated(self, entry): barcode = entry.get_text() entry.select_region(0, -1) if barcode == "": return # blank barcode self.cursor.execute("SELECT id FROM products " "WHERE barcode = %s", (barcode, )) for row in self.cursor.fetchall(): product_id = row[0] break else: 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 index, row in enumerate(self.p_o_store): if row[2] == product_id: row[1] += 1 self.save_purchase_order_line(index) break continue else: self.p_o_store.append([ 0, 1, 0, '', True, '', '', '', 0.00, 0.00, False, int(self.vendor_id), '', self.purchase_order_id, False ]) path = self.p_o_store.iter_n_children() path -= 1 #iter_n_children starts at 1 ; path starts at 0 _iter = self.p_o_store.get_iter(path) self.product_edited(_iter, product_id) 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_entry_icon_release(self, widget, icon, void): self.calendar.set_relative_to(widget) self.calendar.show() def show_message(self, message): dialog = Gtk.MessageDialog(self.window, 0, Gtk.MessageType.ERROR, Gtk.ButtonsType.CLOSE, message) dialog.run() dialog.destroy()
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 WriteCheckGUI: 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.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() date_text = self.calendar.get_text() self.builder.get_object('entry2').set_text(date_text) self.populating = False self.service_provider_store = self.builder.get_object( 'service_provider_store') self.expense_account_store = self.builder.get_object( 'expense_account_store') self.expense_percentage_store = self.builder.get_object( 'expense_percentage_store') self.bank_account_store = self.builder.get_object('bank_account_store') self.cash_account_store = self.builder.get_object('cash_account_store') self.populate_stores() self.expense_percentage_store.append([0, 0.00, 0, ""]) self.calculate_percentages() self.window = self.builder.get_object('window1') self.window.show_all() def focus(self, window, event): self.populating = True self.expense_account_store.clear() self.cursor.execute("SELECT number, name FROM accounts " " WHERE number > 3000 AND number < 4000 " "AND is_parent = False") for row in self.cursor.fetchall(): account_number = row[0] account_name = row[1] self.expense_account_store.append( [str(account_number), account_name]) combo = self.builder.get_object('combobox1') active_sp = combo.get_active_id() self.service_provider_store.clear() self.cursor.execute("SELECT id, name FROM contacts " "WHERE service_provider = True") for row in self.cursor.fetchall(): contact_id = row[0] contact_name = row[1] self.service_provider_store.append([str(contact_id), contact_name]) combo.set_active_id(active_sp) self.populating = False def populate_stores(self): self.cursor.execute("SELECT id, name FROM contacts " "WHERE service_provider = True") for row in self.cursor.fetchall(): contact_id = row[0] contact_name = row[1] self.service_provider_store.append([str(contact_id), contact_name]) self.cursor.execute("SELECT number, name FROM accounts " "WHERE bank_account = True ") for row in self.cursor.fetchall(): account_number = row[0] account_name = row[1] self.bank_account_store.append([str(account_number), account_name]) self.cursor.execute("SELECT number, name FROM accounts " "WHERE cash_account = True ") for row in self.cursor.fetchall(): account_number = row[0] account_name = row[1] self.cash_account_store.append([str(account_number), account_name]) self.cursor.execute("SELECT number, name FROM accounts " " WHERE number > 3000 AND number < 4000") for row in self.cursor.fetchall(): account_number = row[0] account_name = row[1] self.expense_account_store.append( [str(account_number), account_name]) def service_provider_clicked(self, button): contacts.GUI(self.db) def service_provider_combo_changed(self, combo): if self.populating == True: return self.check_if_all_entries_valid() a_iter = combo.get_active_iter() path = self.service_provider_store.get_path(a_iter) contact_name = self.service_provider_store[path][1] self.builder.get_object('label14').set_label(contact_name) def add_percentage_row_clicked(self, button): self.expense_percentage_store.append([0, 0.00, 0, ""]) self.calculate_percentages() def delete_percentage_row_clicked(self, button): selection = self.builder.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.calculate_percentages() def calculate_percentages(self): lines = self.expense_percentage_store.iter_n_children() if lines == 0: return percentage = 100 / lines invoice_amount = self.builder.get_object('spinbutton1').get_text() percent = float(percentage) / 100.00 split_amount = float(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.update_expense_amounts() def update_expense_amounts(self): invoice_amount = self.builder.get_object('spinbutton1').get_text() for row in self.expense_percentage_store: percentage = row[0] percent = float(percentage) / 100.00 split_amount = float(invoice_amount) * percent row[1] = split_amount self.add_expense_totals() def percent_render_edited(self, renderer, path, text): self.expense_percentage_store[path][0] = int(text) invoice_amount = self.builder.get_object('spinbutton1').get_text() percent = float(text) / 100.00 split_amount = float(invoice_amount) * percent self.expense_percentage_store[path][1] = split_amount self.add_expense_totals() def add_expense_totals(self): total = 0.00 for row in self.expense_percentage_store: total += row[1] cents = '{:.2f}'.format(total % 1) self.builder.get_object('label11').set_label(cents[2:]) money_text = convert_numeric_to_text(total) self.builder.get_object('label10').set_label(money_text) self.builder.get_object('entry4').set_text('${:,.2f}'.format(total)) self.builder.get_object('entry5').set_text('${:,.2f}'.format(total)) self.builder.get_object('entry6').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] self.expense_percentage_store[path][2] = int(account_number) self.expense_percentage_store[path][3] = account_name self.check_if_all_entries_valid() def bank_credit_card_combo_changed(self, combo): if combo.get_active() == None: self.builder.get_object('entry3').set_sensitive(False) self.builder.get_object('entry5').set_sensitive(False) else: self.builder.get_object('entry3').set_sensitive(True) self.builder.get_object('entry5').set_sensitive(True) bank_account = combo.get_active_id() check_number = get_check_number(self.db, bank_account) self.builder.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 check_if_all_entries_valid(self): check_button = self.builder.get_object('button3') transfer_button = self.builder.get_object('button4') cash_button = self.builder.get_object('button5') check_button.set_sensitive(False) transfer_button.set_sensitive(False) cash_button.set_sensitive(False) if self.builder.get_object('combobox1').get_active() == -1: self.set_button_message('No service provider') return # no service provider selected invoice_amount = float( self.builder.get_object('spinbutton1').get_text()) if invoice_amount == 0.00: self.set_button_message('No invoice amount') return text = self.builder.get_object('entry4').get_text() payment_amount = float(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.builder.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.builder.get_object('combobox2').get_active() > -1: # bank / credit card selected check_button.set_label('Check payment') check_button.set_sensitive(True) if self.builder.get_object('entry3').get_text() != "": transfer_button.set_label('Transfer payment') transfer_button.set_sensitive(True) else: transfer_button.set_label('No transfer number') else: check_button.set_label('No bank account selected') transfer_button.set_label('No bank account selected') def set_button_message(self, message): self.builder.get_object('button3').set_label(message) self.builder.get_object('button4').set_label(message) self.builder.get_object('button5').set_label(message) def cash_payment_clicked(self, button): invoice_id, total = self.save_incoming_invoice() cash_account = self.builder.get_object('combobox3').get_active_id() service_provider_cash_payment(self.db, self.datetime, total, cash_account) self.db.commit() self.window.destroy() def transfer_clicked(self, button): invoice_id, total = self.save_incoming_invoice() checking_account = self.builder.get_object('combobox2').get_active_id() transfer_number = self.builder.get_object('entry3').get_text() service_provider_transfer(self.db, self.datetime, total, transfer_number, checking_account) self.db.commit() self.window.destroy() def print_check_clicked(self, button): invoice_id, total = self.save_incoming_invoice() checking_account = self.builder.get_object('combobox2').get_active_id() check_number = self.builder.get_object('entry7').get_text() service_provider_check_payment(self.db, self.datetime, total, check_number, checking_account) self.db.commit() self.window.destroy() def save_incoming_invoice(self): contact_id = self.builder.get_object('combobox1').get_active_id() description = self.builder.get_object('entry1').get_text() total = float(self.builder.get_object('spinbutton1').get_text()) self.cursor.execute( "INSERT INTO incoming_invoices " "(contact_id, date_created, amount, description) " "VALUES (%s, %s, %s, %s) RETURNING id", (contact_id, self.datetime, total, description)) invoice_id = self.cursor.fetchone()[0] for row in self.expense_percentage_store: amount = row[1] expense_account = row[2] post_incoming_invoice_expense(self.db, self.datetime, amount, expense_account) return invoice_id, total def calendar_day_selected(self, calendar): self.datetime = calendar.get_datetime() day_text = calendar.get_text() self.builder.get_object('entry2').set_text(day_text) def calendar_entry_icon_released(self, widget, icon, event): self.calendar.set_relative_to(widget) 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()
class DocumentGUI: def __init__(self): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.cursor = DB.cursor() self.edited_renderer_text = 1 self.qty_renderer_value = 1 self.handler_ids = list() for connection in (("contacts_changed", self.populate_customer_store ), ("products_changed", self.populate_product_store )): handler = broadcaster.connect(connection[0], connection[1]) self.handler_ids.append(handler) self.document_id = 0 self.documents_store = self.builder.get_object('documents_store') self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) 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.existing_store = self.builder.get_object('existing_store') self.customer_store = self.builder.get_object('customer_store') self.product_store = self.builder.get_object('product_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) self.retailer_completion = self.builder.get_object('retailer_completion') self.retailer_completion.set_match_func(self.customer_match_func) self.populate_product_store () self.populate_customer_store () self.calculate_totals () self.load_settings() self.window = self.builder.get_object('window') self.window.show_all() def load_settings (self): self.cursor.execute("SELECT column_id, column_name, visible " "FROM settings.document_columns") for row in self.cursor.fetchall(): column_id = row[0] column_name = row[1] visible = row[2] tree_column = self.builder.get_object(column_id) tree_column.set_title(column_name) tree_column.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 DB.rollback() def drag_finish(self, wid, context, x, y, time): print (wid, context, x, y, time) def on_drag_motion(self, wid, context, x, y, time): #print wid #l.set_text('\n'.join([str(t) for t in context.targets])) #context.drag_status(gtk.gdk.ACTION_COPY, time) #print context.list_targets() # Returning True which means "I accept this data". #print "movement" #return False pass def on_drag_data_received(self, widget, drag_context, x,y, data,info, time): _list_ = data.get_text().split(' ') if len(_list_) != 2: return table, _id_ = _list_[0], _list_[1] self.cursor.execute("SELECT product, remark, price FROM %s WHERE id = %s" % (table, _id_)) for row in self.cursor.fetchall(): product = row[0] remark = row[1] price = row[2] print ("please implement me") #FIXME def destroy(self, window): for handler in self.handler_ids: broadcaster.disconnect(handler) self.cursor.close() def treeview_button_release_event (self, treeview, event): if event.button == 3: menu = self.builder.get_object('right_click_menu') menu.popup_at_pointer() def focus (self, window, event): document_type_combo = self.builder.get_object('comboboxtext2') active_type_id = document_type_combo.get_active_id() document_type_combo.remove_all() self.cursor.execute("SELECT id::text, name FROM document_types") for row in self.cursor.fetchall(): type_id = row[0] type_name = row[1] document_type_combo.append(str(type_id), type_name) document_type_combo.set_active_id(str(active_type_id)) def product_window(self, column): import products_overview products_overview.ProductsOverviewGUI() def contacts_window(self, widget): import contacts_overview contacts_overview.ContactsOverviewGUI() def view_document_clicked(self, widget): comment = self.builder.get_object('entry3').get_text() d = documenting.Setup(self.documents_store, self.contact_id, comment, self.date, self.document_type_id, self.document_name) d.view() def post_document_clicked(self, widget): comment = self.builder.get_object('entry3').get_text() d = documenting.Setup( self.documents_store, self.contact_id, comment, self.date, self.document_type_id, self.document_name ) if self.builder.get_object('menuitem1').get_active() == True: d.print_directly() else: d.print_dialog(self.window) d.post(self.document_id) if self.builder.get_object('menuitem4').get_active() == True: self.cursor.execute("SELECT name, email FROM contacts " "WHERE id = %s", (self.contact_id, )) for row in self.cursor.fetchall(): name = row[0] email = row[1] if email != "": email = "%s '< %s >'" % (name, email) d.email(email) DB.commit() self.window.destroy() ################## start customer def populate_customer_store (self, m=None, i=None): self.customer_store.clear() self.cursor.execute("SELECT id::text, name FROM contacts " "WHERE (deleted, customer) = " "(False, True) ORDER BY name") for row in self.cursor.fetchall(): self.customer_store.append(row) DB.rollback() def customer_match_selected(self, completion, model, iter): self.contact_id = model[iter][0] self.customer_selected (self.contact_id) def customer_match_func(self, completion, key, iter): for text in key.split(): 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 contact_id = widget.get_active_id() if contact_id != None: self.contact_id = contact_id self.customer_selected (self.contact_id) self.calculate_totals () def customer_selected(self, name_id): self.builder.get_object('comboboxtext2').set_sensitive(True) self.builder.get_object('button4').set_sensitive(True) self.builder.get_object('button11').set_sensitive(True) self.builder.get_object('button12').set_sensitive(True) self.cursor.execute("SELECT " "name, " "ext_name, " "address, " "phone " "FROM contacts " "WHERE id = %s",(name_id,)) for row in self.cursor.fetchall() : self.customer_name_default_label = row[0] self.builder.get_object('entry11').set_text(row[1]) self.builder.get_object('entry10').set_text(row[2]) self.builder.get_object('entry12').set_text(row[3]) self.builder.get_object('button2').set_sensitive(True) self.builder.get_object('menuitem2').set_sensitive(True) job_type_combo = self.builder.get_object('comboboxtext2') if job_type_combo.get_active() < 0 : job_type_combo.set_active(0) self.populate_existing_store () ################## start qty def qty_edited(self, widget, path, text): row_id = self.documents_store[path][0] try: self.cursor.execute("UPDATE document_items " "SET (qty, ext_price) = " "(%s, (%s * price)) " "WHERE id = %s " "RETURNING qty::text, ext_price::text", (text, text, row_id)) for row in self.cursor.fetchall(): self.documents_store[path][1] = row[0] self.documents_store[path][14] = row[1] except psycopg2.DataError as e: DB.rollback() self.show_message (e) return False DB.commit() self.calculate_totals () ################## start minimum def minimum_edited(self, widget, path, text): row_id = self.documents_store[path][0] try: self.cursor.execute("UPDATE document_items SET min = %s " "WHERE id = %s " "RETURNING min::text", (text, row_id)) DB.commit() for row in self.cursor.fetchall(): minimum = row[0] self.documents_store[path][5] = minimum except psycopg2.DataError as e: DB.rollback() self.show_message (e) return False ################## start maximum def maximum_edited(self, widget, path, text): row_id = self.documents_store[path][0] try: self.cursor.execute("UPDATE document_items SET max = %s " "WHERE id = %s " "RETURNING max::text", (text, row_id)) DB.commit() for row in self.cursor.fetchall(): maximum = row[0] self.documents_store[path][6] = maximum except psycopg2.DataError as e: DB.rollback() self.show_message (e) return False ################## start freeze def freeze_toggled (self, cell_renderer, path): row_id = self.documents_store[path][0] self.cursor.execute("UPDATE document_items SET type_1 = NOT " "(SELECT type_1 FROM document_items " "WHERE id = %s) " "WHERE id = %s " "RETURNING type_1", (row_id, row_id)) DB.commit() for row in self.cursor.fetchall(): freeze = row[0] self.documents_store[path][9] = freeze ################## start remark def remark_edited(self, widget, path, text): row_id = self.documents_store[path][0] self.cursor.execute("UPDATE document_items SET remark = %s " "WHERE id = %s " "RETURNING remark", (text, row_id)) DB.commit() for row in self.cursor.fetchall(): remark = row[0] self.documents_store[path][10] = remark ################## start priority def priority_edited(self, widget, path, text): row_id = self.documents_store[path][0] self.cursor.execute("UPDATE document_items SET priority = %s " "WHERE id = %s " "RETURNING priority", (text, row_id)) DB.commit() for row in self.cursor.fetchall(): priority = row[0] self.documents_store[path][11] = priority ################## start retailer def retailer_editing_started (self, renderer, combo, path): entry = combo.get_child() entry.set_completion (self.retailer_completion) def retailer_match_selected(self, completion, model, iter): retailer_id = model[iter][0] retailer_name = model[iter][1] selection = self.builder.get_object('treeview-selection') model, path = selection.get_selected_rows() self.documents_store[path][7] = int(retailer_id) self.documents_store[path][8] = retailer_name self.save_document_line (path) def retailer_match_func(self, completion, key, iter): for text in key.split(): if text not in self.customer_store[iter][1].lower(): return False return True def retailer_changed (self, combo, path, _iter): retailer_id = self.customer_store[_iter][0] retailer_name = self.customer_store[_iter][1] self.documents_store[path][7] = int(retailer_id) self.documents_store[path][8] = retailer_name self.save_document_line (path) ################## start price def s_price_edited (self, widget, path, text): row_id = self.documents_store[path][0] try: self.cursor.execute("UPDATE document_items " "SET s_price = %s " "WHERE id = %s " "RETURNING s_price::text", (text, row_id)) for row in self.cursor.fetchall(): self.documents_store[path][13] = row[0] except psycopg2.DataError as e: DB.rollback() self.show_message (e) return False DB.commit() def price_edited(self, widget, path, text): row_id = self.documents_store[path][0] try: self.cursor.execute("UPDATE document_items " "SET (price, ext_price) = " "(%s, (qty * %s)) " "WHERE id = %s " "RETURNING price::text, ext_price::text", (text, text, row_id)) for row in self.cursor.fetchall(): self.documents_store[path][12] = row[0] self.documents_store[path][14] = row[1] except psycopg2.DataError as e: DB.rollback() self.show_message (e) return False DB.commit() self.calculate_totals () ################## 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_renderer_editing_started (self, renderer, combo, path): completion = self.builder.get_object('product_completion') entry = combo.get_child() entry.set_completion(completion) 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_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): if int(product_id) == self.documents_store[path][2]: return # product did not change iter_ = self.documents_store.get_iter(path) row_id = self.documents_store[iter_][0] self.cursor.execute("UPDATE document_items " "SET product_id = %s WHERE id = %s;" "SELECT name, ext_name FROM products " "WHERE id = %s", (product_id, row_id, product_id)) tupl = self.cursor.fetchone() DB.commit() product_name, product_ext_name = tupl[0], tupl[1] self.documents_store[iter_][2] = int(product_id) self.documents_store[iter_][3] = product_name self.documents_store[iter_][4] = product_ext_name price = get_customer_product_price(self.contact_id, product_id) self.documents_store[iter_][12] = str(price) self.calculate_totals() # retrieve path again after all sorting has happened for the updates path = self.documents_store.get_path(iter_) treeview = self.builder.get_object('treeview2') c = treeview.get_column(3) treeview.set_cursor(path, c, True) def populate_product_store (self, m=None, i=None): self.product_store.clear() self.cursor.execute("SELECT " "id::text, " "name ||' {' || ext_name ||'}' FROM products " "WHERE (deleted, sellable, stock) = " "(False, True, True) ORDER BY name ") for row in self.cursor.fetchall(): self.product_store.append(row) DB.rollback() ################## end product def calculate_totals (self, widget = None): self.cursor.execute("SELECT " "COALESCE(SUM(ext_price), 0.00)::text " "FROM document_items WHERE document_id = %s", (self.document_id,)) for row in self.cursor.fetchall(): self.total = row[0] self.builder.get_object('entry8').set_text(self.total) DB.rollback() def add_entry (self, widget): c = DB.cursor() c.execute("INSERT INTO document_items " "(document_id, product_id, retailer_id) " "VALUES " "(%s, " "(SELECT id FROM products " "WHERE (deleted, sellable, stock) = " "(False, True, True) LIMIT 1), " "%s) " "RETURNING " "id ", (self.document_id, self.contact_id)) row_id = c.fetchone()[0] c.execute("SELECT " "di.id, " "qty::text, " "p.id, " "p.name, " "p.ext_name, " "min::text, " "max::text, " "retailer_id, " "COALESCE(c.name, ''), " "type_1, " "remark, " "priority, " "price::text, " "s_price::text, " "ext_price::text " "FROM document_items AS di " "JOIN products AS p ON di.product_id = p.id " "LEFT JOIN contacts AS c ON di.retailer_id = c.id " "WHERE di.id = %s", (row_id,)) self.builder.get_object('button15').set_sensitive(True) DB.commit() for row in c.fetchall(): iter_ = self.documents_store.append(row) treeview = self.builder.get_object('treeview2') c = treeview.get_column(0) path = self.documents_store.get_path(iter_) treeview.set_cursor(path , c, True)#set the cursor to the last appended item return row_id = row[0] product_id = row[1] product_name = i[1] price = get_customer_product_price (self.contact_id, product_id) self.documents_store.append([0, 1.0, product_id, product_name, "", 0.0, 100.00, int(self.contact_id), self.customer_name_default_label, False, "", "1", price, 0.00, 1]) last = self.documents_store.iter_n_children () last -= 1 #iter_n_children starts at 1 ; set_cursor starts at 0 treeview = self.builder.get_object('treeview2') c = treeview.get_column(0) treeview.set_cursor(last , c, True) #set the cursor to the last appended item break c.close() def delete_entry (self, widget): selection = self.builder.get_object("treeview-selection") row, path = selection.get_selected_rows () if path == []: return document_line_item_id = self.documents_store[path][0] self.cursor.execute("DELETE FROM document_items WHERE id = %s", (document_line_item_id,)) DB.commit() self.populate_document_store () 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 document_name_changed (self, widget): if self.document_id == 0: return document_name = widget.get_text() if " " in document_name: document_name = re.sub(" ", "", document_name) widget.set_text(document_name) return self.cursor.execute("UPDATE documents SET name = %s " "WHERE id = %s", (document_name, self.document_id)) DB.commit() def delete_document_clicked (self, widget): self.cursor.execute("DELETE FROM documents WHERE id = %s", (self.document_id,)) DB.commit() self.builder.get_object('entry3').set_text('') self.populate_existing_store () def document_type_changed (self, widget): document_type = widget.get_active_text() if document_type != None: self.document_type = document_type self.document_type_id = widget.get_active_id() self.builder.get_object('window').set_title("New " + document_type) self.builder.get_object('button15').set_label("Post " + document_type) self.calendar.set_today() self.populate_existing_store () def document_type_clicked (self, widget): import settings settings.GUI('document_types') def new_document_clicked (self, widget): if self.document_type == "": return self.document_id = 0 self.documents_store.clear() self.builder.get_object('label6').set_text(" Current %s : " % self.document_type) comment = self.builder.get_object('entry3').get_text() self.cursor.execute("INSERT INTO documents " "(contact_id, " "closed, " "invoiced, " "canceled, " "date_created, " "dated_for, " "document_type_id, " "pending_invoice) " "VALUES " "(%s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (self.contact_id, False, False, False, datetime.today(), self.date, self.document_type_id, False)) self.document_id = self.cursor.fetchone()[0] self.set_document_name () DB.commit() self.builder.get_object('button13').set_sensitive(True) self.builder.get_object('button14').set_sensitive(True) self.builder.get_object('import_from_history').set_sensitive(True) def set_document_name (self): type_text = self.document_type[0:3] contact_name = self.builder.get_object('combobox-entry5').get_text() split_name = contact_name.split(' ') name_str = "" for i in split_name: name_str = name_str + i[0:3] name = name_str.lower() self.cursor.execute("SELECT format_date(%s)", (self.date,)) date = self.cursor.fetchone()[0] date = re.sub (" ", "_", date) self.document_name = type_text + "_" + str(self.document_id) + "_" + name + "_" + date self.cursor.execute("UPDATE documents SET name = %s WHERE id = %s", (self.document_name, self.document_id)) DB.commit() self.builder.get_object('entry5').set_text(self.document_name) def populate_existing_store (self): model, path = self.builder.get_object('treeview-selection5').get_selected_rows() self.existing_store.clear() doc_count = 0 self.cursor.execute("SELECT id, name FROM documents " "WHERE " "(document_type_id, closed, " "canceled, contact_id) " "= " "(%s, False, False, %s) ORDER BY id", (self.document_type_id, self.contact_id)) for row in self.cursor.fetchall(): doc_count += 1 self.existing_store.append(row) self.builder.get_object('button11').set_label("Existing documents (%s)" % doc_count) if path != []: self.builder.get_object('treeview-selection5').select_path(path) DB.rollback() def existing_documents_clicked (self, widget): existing_dialog = self.builder.get_object('existing_dialog') self.populate_existing_store () result = existing_dialog.run() if result == Gtk.ResponseType.ACCEPT: self.existing_document () existing_dialog.hide() def existing_document (self): selection = self.builder.get_object('treeview-selection5') model, path = selection.get_selected_rows() if path == []: return self.document_id = model[path][0] self.populate_document_store() self.builder.get_object('button13').set_sensitive(True) self.builder.get_object('button14').set_sensitive(True) self.builder.get_object('button15').set_sensitive(True) self.builder.get_object('import_from_history').set_sensitive(True) def populate_document_store (self): self.documents_store.clear() self.cursor.execute("SELECT " "name, " "dated_for, " "format_date(dated_for) " "FROM documents WHERE id = %s", (self.document_id,)) for row in self.cursor.fetchall(): self.document_name = row[0] self.date = row[1] self.builder.get_object('entry1').set_text(row[2]) self.builder.get_object('entry5').set_text(self.document_name) self.cursor.execute("SELECT " "di.id, " "qty::text, " "p.id, " "p.name, " "p.ext_name, " "min::text, " "max::text, " "retailer_id, " "COALESCE(c.name, ''), " "type_1, " "remark, " "priority, " "price::text, " "s_price::text, " "ext_price::text " "FROM document_items AS di " "JOIN products AS p ON di.product_id = p.id " "LEFT JOIN contacts AS c ON di.retailer_id = c.id " "WHERE document_id = %s ORDER BY di.id", (self.document_id, ) ) for row in self.cursor.fetchall(): self.documents_store.append(row) self.calculate_totals () DB.rollback() def import_items_from_history_activated (self, menuitem): button = Gtk.Button(label = 'Import items from document history') button.show() from reports import document_history dh = document_history.DocumentHistoryGUI() dh.window.set_transient_for (self.window) dh.get_object("box1").pack_start(button, False, False, 10) dh.get_object("all_customer_checkbutton").set_active(True) dh.get_object("box3").set_visible(False) selection = dh.get_object("treeview-selection1") selection.set_mode(Gtk.SelectionMode.SINGLE) selection.select_path(0) button.connect("clicked", self.import_items_from_history, dh) def import_items_from_history(self, button, dh): model, path = dh.get_object("treeview-selection1").get_selected_rows() if path == []: return old_id = model[path][0] self.cursor.execute("INSERT INTO document_items " "(qty, " "document_id, " "product_id, " "min, " "max, " "retailer_id, " "type_1, " "remark, " "priority, " "price, " "s_price, " "ext_price) " "(SELECT " "qty, " "%s, " "product_id, " "min, " "max, " "%s, " "type_1, " "remark, " "priority, " "price, " "s_price, " "ext_price " "FROM document_items WHERE document_id = %s)", (self.document_id, self.contact_id, old_id)) DB.commit() self.populate_document_store () dh.window.destroy() def help_clicked (self, widget): subprocess.Popen(["yelp", help_dir + "/document.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.add_entry(None) if keyname == 'F3': self.delete_entry(None) def calendar_day_selected (self, calendar): day_text = calendar.get_text() self.date = calendar.get_date() self.builder.get_object('entry1').set_text(day_text) self.cursor.execute("UPDATE documents SET dated_for = %s " "WHERE id = %s", (self.date, self.document_id)) self.set_document_name () DB.commit() def calendar_entry_icon_release (self, widget, icon, void): self.calendar.set_relative_to(widget) self.calendar.show() def clear_retailer_entry (self, menuitem): selection = self.builder.get_object("treeview-selection") store, path = selection.get_selected_rows () self.documents_store[path][7]= None self.documents_store[path][8] = '' line_id = self.documents_store[path][0] self.cursor.execute("UPDATE document_items SET retailer_id = NULL " "WHERE id = %s", (line_id, )) DB.commit() #barcode entry support code ******* def barcode_entry_activated (self, entry2): barcode = entry2.get_text() entry2.set_text('') self.cursor.execute("SELECT id, name, 1.00, ext_name " "FROM products WHERE barcode = %s",(barcode, )) for i in self.cursor.fetchall(): product_id = i[0] for index, row in enumerate(self.documents_store): if row[2] == product_id: row[1] += 1.0 # increase the qty by one treeview = self.builder.get_object('treeview2') c = treeview.get_column(0) treeview.set_cursor(index , c, False) #set the cursor to the last appended item return product_name = i[1] price = i[2] ext_name = i[3] self.documents_store.append([0, 1.0, product_id, product_name, ext_name, 0.0, 100.00, int(self.contact_id), self.customer_name_default_label, False, "", "1", price, 0.00, 1]) #FIXME last = self.documents_store.iter_n_children() - 1 treeview = self.builder.get_object('treeview2') c = treeview.get_column(0) treeview.set_cursor(last , c, False) #set the cursor to the last appended item return else: raise Exception ("please make a window to alert the user that the barcode does not exist!") DB.rollback() 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 LoanGUI: def __init__(self, main): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.db = main.db self.cursor = self.db.cursor() self.loan_store = self.builder.get_object('loan_store') self.contact_store = self.builder.get_object('contact_store') self.populate_contacts() self.populate_accounts() self.populate_loans() self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.day_selected) self.calendar.set_today() entry = self.builder.get_object('entry1') self.calendar.set_relative_to(entry) contact_completion = self.builder.get_object('contact_completion') contact_completion.set_match_func(self.contact_match_func) amount_column = self.builder.get_object('treeviewcolumn4') amount_renderer = self.builder.get_object('cellrenderertext5') amount_column.set_cell_data_func(amount_renderer, self.amount_cell_func) self.window = self.builder.get_object('window1') self.window.show_all() def window_destroy(self, window): self.cursor.close() def spinbutton_focus_in_event(self, entry, event): GLib.idle_add(self.highlight, entry) def highlight(self, entry): entry.select_region(0, -1) def amount_cell_func(self, column, cellrenderer, model, iter1, data): amount = "{:,.2f}".format(model[iter1][5]) cellrenderer.set_property("text", amount) def populate_contacts(self): self.cursor.execute("SELECT id::text, name, ext_name FROM contacts " "ORDER BY name") for row in self.cursor.fetchall(): self.contact_store.append(row) def populate_accounts(self): accounts_store = self.builder.get_object('liability_account_store') accounts_store.clear() self.cursor.execute("SELECT number::text, name " "FROM gl_accounts WHERE type = 5 " "AND parent_number IS NULL") for row in self.cursor.fetchall(): parent = accounts_store.append(None, row) number = row[0] self.populate_child_accounts(number, parent, accounts_store) def populate_child_accounts(self, number, parent, accounts_store): self.cursor.execute( "SELECT number::text, name FROM gl_accounts WHERE " "parent_number = %s", (number, )) for row in self.cursor.fetchall(): p = accounts_store.append(parent, row) number = row[0] self.populate_child_accounts(number, p, accounts_store) def populate_loans(self): self.loan_store.clear() self.cursor.execute("SELECT " "l.id, " "c.name, " "l.date_received::text, " "format_date(l.date_received), " "l.description, " "l.amount, " "l.period_amount, " "l.period||'(s)' " "FROM loans AS l " "JOIN contacts AS c ON c.id = l.contact_id " "WHERE finished = False") for row in self.cursor.fetchall(): self.loan_store.append(row) def description_edited(self, cellrenderertext, path, text): row_id = self.loan_store[path][0] self.cursor.execute("UPDATE loans SET description = %s WHERE id = %s", (text, row_id)) self.db.commit() self.loan_store[path][4] = text def contact_combo_changed(self, combobox): contact_id = combobox.get_active_id() if contact_id != None: self.contact_id = contact_id self.contact_selected() def contact_match_selected(self, completion, model, iter): self.contact_id = model[iter][0] self.contact_selected() def contact_selected(self): self.builder.get_object('entry2').set_sensitive(True) def contact_match_func(self, completion, key, iter_): split_search_text = key.split() for text in split_search_text: if text not in self.contact_store[iter_][1].lower(): return False return True def day_selected(self, calendar): text = calendar.get_text() self.builder.get_object('entry1').set_text(text) self.date = calendar.get_date() def date_entry_icon_release(self, entry, iconposition, event): self.calendar.show() def description_entry_changed(self, entry): self.description = entry.get_text() self.builder.get_object('spinbutton1').set_sensitive(True) def amount_value_changed(self, spinbutton): self.amount = spinbutton.get_text() self.builder.get_object('comboboxtext1').set_sensitive(True) def payment_period_combo_changed(self, combobox): self.payment_period = combobox.get_active_id() self.builder.get_object('combobox2').set_sensitive(True) def liability_combo_changed(self, combobox): account = combobox.get_active_id() if account != None: self.account = account self.builder.get_object('button1').set_sensitive(True) def save_clicked(self, button): gl_entries_id = transactor.create_loan(self.db, self.date, self.amount, self.account) period_amount = self.builder.get_object( 'period_amount_spin').get_text() self.cursor.execute( "INSERT INTO loans " "(description, " "contact_id, " "date_received, " "amount, " "period, " "period_amount, " "last_payment_date, " "gl_entries_id) " "VALUES (%s, %s, %s, %s, %s, %s, now(), %s) ", (self.description, self.contact_id, self.date, self.amount, self.payment_period, period_amount, gl_entries_id)) self.db.commit() self.populate_loans() button.set_sensitive(False) self.window.destroy()
class GUI: def __init__(self, customer_id = None): self.customer_id = customer_id self.payment_type_id = 0 self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.cursor = DB.cursor() self.cursor.execute ("SELECT enforce_exact_payment FROM settings") self.exact_payment = self.cursor.fetchone()[0] self.cursor.execute("SELECT accrual_based FROM settings") self.accrual = self.cursor.fetchone()[0] self.expense_trees = expense_tree customer_completion = self.builder.get_object('customer_completion') customer_completion.set_match_func(self.customer_match_func) self.invoice_store = self.builder.get_object ('unpaid_invoice_store') self.customer_store = self.builder.get_object ('customer_store') self.cash_account_store = self.builder.get_object ('cash_account_store') self.cursor.execute("SELECT number::text, name FROM gl_accounts " "WHERE (is_parent, cash_account) = " "(False, True)") for row in self.cursor.fetchall(): self.cash_account_store.append(row) self.populate_contacts () total_column = self.builder.get_object ('treeviewcolumn3') total_renderer = self.builder.get_object ('cellrenderertext5') total_column.set_cell_data_func(total_renderer, self.total_cell_func) amount_due_column = self.builder.get_object ('treeviewcolumn4') amount_due_renderer = self.builder.get_object ('cellrendererspin7') amount_due_column.set_cell_data_func(amount_due_renderer, self.amount_due_cell_func) self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today () self.date = self.calendar.get_date() self.builder.get_object ('combobox1').set_active_id(str(customer_id)) self.check_entry = self.builder.get_object('entry3') self.credit_entry = self.builder.get_object('entry4') self.cash_entry = self.builder.get_object('entry5') self.window = self.builder.get_object('window1') self.window.show_all() self.check_amount_totals_validity () def destroy(self, window): self.cursor.close() def spinbutton_focus_in_event (self, spinbutton, event): GLib.idle_add(spinbutton.select_region, 0, -1) def help_button_clicked (self, button): subprocess.Popen (["yelp", help_dir + "/customer_payment.page"]) def total_cell_func(self, column, cellrenderer, model, iter1, data): amount = model.get_value(iter1, 4) cellrenderer.set_property("text" , str(amount)) def amount_due_cell_func(self, column, cellrenderer, model, iter1, data): amount = model.get_value(iter1, 5) cellrenderer.set_property("text" , str(amount)) def populate_contacts (self): self.cursor.execute("SELECT id::text, name, ext_name FROM contacts " "WHERE customer = True ORDER BY name") for row in self.cursor.fetchall(): self.customer_store.append(row) DB.rollback() def view_invoice_clicked (self, widget): invoice_combo = self.builder.get_object('comboboxtext1') invoice_id = invoice_combo.get_active_id() self.cursor.execute("SELECT * FROM invoices WHERE id = %s", (invoice_id,)) for cell in self.cursor.fetchall(): file_name = cell[1] + ".pdf" file_data = cell[14] f = open("/tmp/" + file_name,'wb') f.write(file_data) subprocess.call("xdg-open /tmp/" + str(file_name), shell = True) f.close() DB.rollback() def apply_payment_method_toggled (self, togglebutton): active = togglebutton.get_active() self.builder.get_object('amount_spinbutton').set_sensitive(active) def calculate_discount (self, discount, total): discount_percent = (float(discount) / 100.00) discount_amount = total * discount_percent discounted_amount = total - discount_amount discounted_amount = round(discounted_amount, 2) return discounted_amount def calculate_invoice_discount (self, invoice_id): self.cursor.execute("SELECT cash_only, discount_percent, pay_in_days, " "pay_by_day_of_month, pay_in_days_active, " "pay_by_day_of_month_active FROM contacts " "JOIN terms_and_discounts " "ON contacts.terms_and_discounts_id = " "terms_and_discounts.id WHERE contacts.id = %s", (self.customer_id,)) for row in self.cursor.fetchall(): cash_only = row[0] discount = row[1] pay_in_days = row[2] pay_by_day_of_month = row[3] pay_in_days_active = row[4] pay_by_day_of_month_active = row[5] self.cursor.execute("SELECT tax, total, date_created FROM invoices " "WHERE id = %s", (invoice_id,)) if cash_only == True: for row in self.cursor.fetchall(): tax = row[0] self.builder.get_object('label4').set_label("Not applicable") self.builder.get_object('label9').set_label("Not applicable") elif pay_in_days_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] date_difference = self.date - date_created discount_due_date = date_created + timedelta(pay_in_days) due_date_text = date_to_text (discount_due_date) self.builder.get_object('label9').set_label(due_date_text) discounted_amount = self.calculate_discount (discount, total) self.builder.get_object('label4').set_label(str(discounted_amount)) elif pay_by_day_of_month_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] discount_date = date_created.replace(day=pay_by_day_of_month) due_date_text = date_to_text (discount_date) self.builder.get_object('label9').set_label(due_date_text) discounted_amount = self.calculate_discount (discount, total) self.builder.get_object('label4').set_label(str(discounted_amount)) else: raise Exception("the terms_and_discounts table has invalid entries") DB.rollback() def customer_match_func(self, completion, key, iter): split_search_text = key.split() for text in split_search_text: if text not in self.customer_store[iter][1].lower(): return False# no match return True# it's a hit! def customer_combo_changed(self, widget): self.check_amount_totals_validity () customer_id = widget.get_active_id() if customer_id != None: self.customer_id = customer_id self.select_customer() def customer_match_selected(self, completion, model, iter): self.customer_id = model[iter][0] self.select_customer () def select_customer (self): self.cursor.execute("SELECT address, city, state, phone From contacts " "WHERE id = %s", (self.customer_id,)) for row in self.cursor.fetchall(): self.builder.get_object('label11').set_label(row[0]) self.builder.get_object('label18').set_label(row[1]) self.builder.get_object('label17').set_label(row[2]) self.builder.get_object('label12').set_label(row[3]) self.populate_invoices() DB.rollback() def populate_invoices (self): self.update_invoice_amounts_due () self.invoice_store.clear() self.cursor.execute("SELECT " "i.id, " "i.name, " "date_created::text, " "format_date(date_created), " "total, " "amount_due " "FROM invoices AS i " "JOIN contacts AS c ON i.customer_id = c.id " "WHERE (canceled, paid, posted) = " "(False, False, True) " "AND customer_id = %s ORDER BY i.date_created", (self.customer_id,)) for row in self.cursor.fetchall(): self.invoice_store.append(row) self.builder.get_object('amount_spinbutton').set_value(0) def invoice_selection_changed (self, selection): total = Decimal() model, path = selection.get_selected_rows () for row in path: total += model[row][5] if len(path) == 1: invoice_id = model[path][0] amount_due = model[path][5] self.builder.get_object('amount_spinbutton').set_value(amount_due) self.calculate_invoice_discount (invoice_id) else: self.builder.get_object('label9').set_label('Select a single invoice') self.builder.get_object('label4').set_label('Select a single invoice') self.builder.get_object('amount_spinbutton').set_value(total) self.builder.get_object('label22').set_label('{:,.2f}'.format(total, 2)) def invoice_treeview_button_release_event (self, treeview, event): if event.button == 3: menu = self.builder.get_object('menu1') menu.popup_at_pointer() def amount_due_edited (self, renderer, path, amount): invoice_id = self.invoice_store[path][0] if amount == '' or Decimal(amount) > self.invoice_store[path][4]: amount = self.invoice_store[path][4] self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (amount, invoice_id)) DB.commit() self.builder.get_object('amount_spinbutton').set_value(float(amount)) self.invoice_store[path][5] = Decimal(amount).quantize(Decimal('.01')) def amount_due_editing_started (self, renderer, spinbutton, path): upper_limit = self.invoice_store[path][4] spinbutton.set_numeric(True) self.builder.get_object('amount_due_adjustment').set_upper(upper_limit) spinbutton.set_value(self.invoice_store[path][5]) def apply_discount_activated (self, menuitem): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return amount = model[path][5] self.builder.get_object('spinbutton3').set_value(amount) dialog = self.builder.get_object('invoice_discount_dialog') result = dialog.run() dialog.hide() invoice_id = model[path][0] discounted_amount = self.builder.get_object('spinbutton3').get_value() if result == Gtk.ResponseType.ACCEPT: self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (discounted_amount, invoice_id)) DB.commit() self.builder.get_object('amount_spinbutton').set_value(discounted_amount) model[path][5] = Decimal(discounted_amount).quantize(Decimal('.01')) #self.populate_invoices () def check_btn_toggled(self, widget): self.check_entry.set_sensitive(True) self.credit_entry.set_sensitive(False) self.cash_entry.set_sensitive(False) self.payment_type_id = 0 self.check_amount_totals_validity() def credit_btn_toggled(self, widget): self.check_entry.set_sensitive(False) self.credit_entry.set_sensitive(True) self.cash_entry.set_sensitive(False) self.payment_type_id = 1 def cash_btn_toggled(self, widget): self.check_entry.set_sensitive(False) self.credit_entry.set_sensitive(False) self.cash_entry.set_sensitive(True) self.payment_type_id = 2 def post_payment_clicked (self, widget): comments = self.builder.get_object('entry2').get_text() total = self.builder.get_object('amount_spinbutton').get_text() total = Decimal(total) self.payment = transactor.CustomerInvoicePayment(self.date, total) if self.payment_type_id == 0: payment_text = self.check_entry.get_text() self.cursor.execute("INSERT INTO payments_incoming " "(check_payment, cash_payment, " "credit_card_payment, payment_text , " "check_deposited, customer_id, amount, " "date_inserted, comments) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (True, False, False, payment_text, False, self.customer_id, total, self.date, comments)) self.payment_id = self.cursor.fetchone()[0] self.payment.bank_check (self.payment_id) elif self.payment_type_id == 1: payment_text = self.credit_entry.get_text() self.cursor.execute("INSERT INTO payments_incoming " "(check_payment, cash_payment, " "credit_card_payment, payment_text , " "check_deposited, customer_id, amount, " "date_inserted, comments) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (False, False, True, payment_text, False, self.customer_id, total, self.date, comments)) self.payment_id = self.cursor.fetchone()[0] self.payment.credit_card (self.payment_id) elif self.payment_type_id == 2: payment_text = self.cash_entry.get_text() self.cursor.execute("INSERT INTO payments_incoming " "(check_payment, cash_payment, " "credit_card_payment, payment_text , " "check_deposited, customer_id, amount, " "date_inserted, comments) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (False, True, False, payment_text, False, self.customer_id, total, self.date, comments)) self.payment_id = self.cursor.fetchone()[0] self.payment.cash (self.payment_id) if self.builder.get_object('fifo_payment_checkbutton').get_active(): self.pay_invoices_fifo() else: self.pay_selected_invoices() DB.commit() self.cursor.close() self.window.destroy () def pay_invoices_fifo (self): c = DB.cursor() c_id = self.customer_id c.execute("(SELECT id, total - amount_due AS discount FROM " "(SELECT id, total, amount_due, SUM(amount_due) " "OVER (ORDER BY date_created, id) invoice_totals " "FROM invoices WHERE (paid, posted, canceled, customer_id) " "= (False, True, False, %s)" ") i " "WHERE invoice_totals <= " "(SELECT payment_total - invoice_total FROM " "(SELECT COALESCE(SUM(amount_due), 0.0) " "AS invoice_total FROM invoices " "WHERE (paid, canceled, customer_id) = " "(True, False, %s)" ") it, " "(SELECT amount + amount_owed AS payment_total FROM " "(SELECT COALESCE(SUM(amount), 0.0) " "AS amount " "FROM payments_incoming " "WHERE (customer_id, misc_income) = " "(%s, False)" ") pi, " "(SELECT COALESCE(SUM(amount_owed), 0.0) " "AS amount_owed " "FROM credit_memos " "WHERE (customer_id, posted) = " "(%s, True)" ") cm " ") pt " ")" "ORDER BY id);", (c_id, c_id, c_id, c_id )) for row in c.fetchall(): invoice_id = row[0] discount = row[1] if discount != Decimal('0.00'): self.payment.customer_discount (discount) if self.accrual == False: transactor.post_invoice_accounts (self.date, invoice_id) c.execute("UPDATE invoices " "SET (paid, payments_incoming_id, date_paid) " "= (True, %s, %s) " "WHERE id = %s", (self.payment_id, self.date, invoice_id)) c.close() def pay_selected_invoices (self): c = DB.cursor() selection = self.builder.get_object('treeview-selection1') model, paths = selection.get_selected_rows() discount = Decimal('0.00') for row in paths: invoice_id = model[row][0] if self.accrual == False: transactor.post_invoice_accounts (self.date, invoice_id) c.execute("UPDATE invoices " "SET (paid, payments_incoming_id, date_paid) = " "(True, %s, %s) WHERE id = %s " "RETURNING total - amount_due AS discount", (self.payment_id, self.date, invoice_id)) discount += c.fetchone()[0] if discount != Decimal('0.00'): self.payment.customer_discount (discount) c.close() def calendar_day_selected (self, calendar): self.date = calendar.get_date() day_text = calendar.get_text() self.builder.get_object('entry1').set_text(day_text) self.populate_invoices () def calendar_entry_icon_released (self, widget, icon, event): self.calendar.set_relative_to(widget) self.calendar.show() def account_combo_changed (self, combo): self.check_amount_totals_validity () def payment_amount_value_changed (self, spinbutton): self.check_amount_totals_validity () def update_invoice_amounts_due (self): if self.customer_id == None : return self.cursor.execute("SELECT cash_only, discount_percent, pay_in_days, " "pay_by_day_of_month, pay_in_days_active, " "pay_by_day_of_month_active FROM contacts " "JOIN terms_and_discounts " "ON contacts.terms_and_discounts_id = " "terms_and_discounts.id WHERE contacts.id = %s", (self.customer_id,)) for row in self.cursor.fetchall(): cash_only = row[0] discount = row[1] pay_in_days = row[2] pay_by_day_of_month = row[3] pay_in_days_active = row[4] pay_by_day_of_month_active = row[5] self.cursor.execute("SELECT id, total, date_created FROM invoices " "WHERE (customer_id, paid, posted) " "= (%s, False, True)", (self.customer_id,)) if cash_only == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = row[1] self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (total, invoice_id)) elif pay_in_days_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] date_difference = self.date - date_created if date_difference <= timedelta(pay_in_days): discounted_amount = self.calculate_discount(discount, total) self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (discounted_amount, invoice_id)) else: self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (total, invoice_id)) elif pay_by_day_of_month_active == True: for row in self.cursor.fetchall(): invoice_id = row[0] total = float(row[1]) date_created = row[2] discount_date = date_created.replace(day=pay_by_day_of_month) if self.date <= discount_date: discounted_amount = self.calculate_discount(discount, total) self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (discounted_amount, invoice_id)) else: self.cursor.execute("UPDATE invoices SET amount_due = %s " "WHERE id = %s", (total, invoice_id)) else: raise Exception("your terms_and_discounts table has invalid entries") DB.commit() def discount_cash_back_amount_changed (self, spinbutton): self.check_amount_totals_validity () amount = spinbutton.get_value() combobox = self.builder.get_object('combobox2') if amount == 0.00: return elif amount < 0.00: combobox.set_model(self.expense_trees) elif amount > 0.00: combobox.set_model(self.cash_account_store) def check_number_changed (self, entry): self.check_amount_totals_validity () def check_amount_totals_validity (self): button = self.builder.get_object('button1') button.set_sensitive (False) if self.date == None: button.set_label("No date selected") return if self.customer_id == None: button.set_label("No contact selected") return check_text = self.builder.get_object('entry3').get_text() check_active = self.builder.get_object('check_radiobutton').get_active() if check_active == True and check_text == '': button.set_label('No check number') return # no check number if self.exact_payment: self.check_amount_totals_absolute () else: self.check_amount_totals_flexible () def check_amount_totals_flexible (self): button = self.builder.get_object('button1') button.set_sensitive (False) label = self.builder.get_object('label20') label.set_visible (True) payment = self.builder.get_object('amount_spinbutton').get_value() selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() invoice_amount_due_totals = Decimal() for row in path: invoice_amount_due_totals += model[row][5] self.builder.get_object('label23').set_label ('{:,.2f}'.format(payment)) if float(invoice_amount_due_totals) == payment : label.set_visible (False) #hide the off balance alert button.set_sensitive (True) button.set_label('Post payment') def check_amount_totals_absolute (self): button = self.builder.get_object('button1') button.set_sensitive (False) payment = self.builder.get_object('amount_spinbutton').get_value() selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if len(path) == 0: button.set_label ('No invoices selected') return invoice_amount_due_totals = Decimal() for row in path: invoice_amount_due_totals += model[row][5] self.builder.get_object('label23').set_label ('{:,.2f}'.format(payment)) if float(invoice_amount_due_totals) != payment : button.set_label ("Totals do not match") return button.set_label ('Post payment') button.set_sensitive (True)
class ProductSerialNumbersGUI: def __init__(self, main): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.main = main self.db = main.db self.cursor = self.db.cursor() self.product_store = self.builder.get_object('product_store') self.contact_store = self.builder.get_object('contact_store') self.serial_number_store = self.builder.get_object( 'serial_number_treeview_store') self.handler_p_id = main.connect("products_changed", self.populate_product_store) self.handler_c_id = main.connect("contacts_changed", self.populate_contact_store) product_completion = self.builder.get_object('add_product_completion') product_completion.set_match_func(self.product_match_func) product_completion = self.builder.get_object( 'event_product_completion') product_completion.set_match_func(self.product_match_func) contact_completion = self.builder.get_object('contact_completion') contact_completion.set_match_func(self.contact_match_func) self.product_name = '' self.contact_name = '' self.serial_number = '' self.filtered_serial_store = self.builder.get_object( 'serial_number_treeview_filter') self.filtered_serial_store.set_visible_func(self.filter_func) self.product_id = 0 self.populate_product_store() self.populate_contact_store() self.populate_serial_number_history() 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 destroy(self, window): self.main.disconnect(self.handler_c_id) self.main.disconnect(self.handler_p_id) self.cursor.close() def search_changed(self, entry): '''This signal is hooked up to all search entries''' self.product_name = self.builder.get_object( 'searchentry2').get_text().lower() self.contact_name = self.builder.get_object( 'searchentry3').get_text().lower() self.serial_number = self.builder.get_object( 'searchentry4').get_text().lower() self.filtered_serial_store.refilter() def filter_func(self, model, tree_iter, r): for i in self.product_name.split(): if i not in model[tree_iter][1].lower(): return False for i in self.serial_number.split(): if i not in model[tree_iter][2].lower(): return False return True def calendar_day_selected(self, calendar): self.date = calendar.get_date() day_text = calendar.get_text() self.builder.get_object('entry3').set_text(day_text) self.builder.get_object('entry4').set_text(day_text) 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 contact_match_func(self, completion, key, tree_iter): split_search_text = key.split() for text in split_search_text: if text not in self.contact_store[tree_iter][1].lower(): return False return True def populate_product_store(self, m=None, i=None): self.product_store.clear() self.cursor.execute("SELECT id, name, invoice_serial_numbers " "FROM products " "WHERE (deleted, stock, sellable) = " "(False, True, True) ORDER BY name") for row in self.cursor.fetchall(): product_id = row[0] name = row[1] serial = row[2] self.product_store.append([str(product_id), name, serial]) def populate_contact_store(self, m=None, i=None): self.contact_store.clear() self.cursor.execute("SELECT id, name, ext_name FROM contacts " "WHERE (deleted) = (False) ORDER BY name") for row in self.cursor.fetchall(): contact_id = row[0] name = row[1] ext_name = row[2] self.contact_store.append([str(contact_id), name, ext_name]) def populate_serial_number_history(self): self.serial_number_store.clear() self.cursor.execute("SELECT " "sn.id, " "p.name, " "sn.serial_number, " "sn.date_inserted::text, " "format_date(sn.date_inserted), " "COALESCE(COUNT(snh.id)::text, ''), " "COALESCE(ili.invoice_id::text, ''), " "COALESCE(poli.purchase_order_id::text, ''), " "COALESCE(manufacturing_id::text, '') " "FROM serial_numbers AS sn " "JOIN products AS p ON p.id = sn.product_id " "LEFT JOIN serial_number_history AS snh " "ON snh.serial_number_id = sn.id " "LEFT JOIN invoice_items AS ili " "ON ili.id = sn.invoice_item_id " "LEFT JOIN purchase_order_line_items AS poli " "ON poli.id = purchase_order_line_item_id " "GROUP BY sn.id, p.name, sn.serial_number, " "sn.date_inserted, invoice_id, poli.id, " "manufacturing_id " "ORDER BY sn.id") for row in self.cursor.fetchall(): self.serial_number_store.append(row) def serial_number_treeview_row_activated(self, treeview, path, column): model = treeview.get_model() serial_id = model[path][0] self.populate_serial_event_store(serial_id) def populate_serial_event_store(self, serial_id): store = self.builder.get_object('events_store') store.clear() self.cursor.execute( "SELECT " "snh.id, " "c.name, " "snh.date_inserted::text, " "format_date(snh.date_inserted), " "snh.description " "FROM serial_number_history AS snh " "JOIN contacts AS c ON c.id = snh.contact_id " "WHERE serial_number_id = %s", (serial_id, )) for row in self.cursor.fetchall(): store.append(row) def add_serial_event_clicked(self, button): dialog = self.builder.get_object('event_dialog') response = dialog.run() if response == Gtk.ResponseType.ACCEPT: serial_number = self.builder.get_object('entry2').get_text() buf = self.builder.get_object('textbuffer1') start_iter = buf.get_start_iter() end_iter = buf.get_end_iter() description = buf.get_text(start_iter, end_iter, True) self.cursor.execute( "INSERT INTO serial_number_history " "(contact_id, " "date_inserted, description, serial_number_id) " "VALUES (%s, %s, %s, %s)", (self.contact_id, self.date, description, self.serial_id)) self.db.commit() self.populate_serial_number_history() self.builder.get_object('combobox2').set_sensitive(False) self.builder.get_object('combobox3').set_sensitive(False) self.builder.get_object('textview1').set_sensitive(False) self.builder.get_object('button4').set_sensitive(False) self.builder.get_object('combobox-entry2').set_text('') self.builder.get_object('combobox-entry4').set_text('') self.builder.get_object('combobox-entry5').set_text('') self.builder.get_object('textbuffer1').set_text('') dialog.hide() def add_serial_number_dialog_clicked(self, button): dialog = self.builder.get_object('add_serial_number_dialog') response = dialog.run() if response == Gtk.ResponseType.ACCEPT: self.populate_serial_number_history() dialog.hide() self.builder.get_object('label10').set_text('') def add_serial_number_clicked(self, button): serial_number = self.builder.get_object('entry2').get_text() try: self.cursor.execute( "INSERT INTO serial_numbers " "(product_id, serial_number, " "date_inserted) " "VALUES (%s, %s, %s)", (self.product_id, serial_number, self.date)) self.db.commit() dialog = self.builder.get_object('add_serial_number_dialog') dialog.response(Gtk.ResponseType.ACCEPT) except psycopg2.IntegrityError as e: self.builder.get_object('label10').set_text(str(e)) self.db.rollback() def date_entry_icon_released(self, entry, icon, event): self.calendar.set_relative_to(entry) self.calendar.show() def event_product_match_selected(self, completion, model, iter_): product_id = model[iter_][0] self.builder.get_object('combobox1').set_active_id(product_id) self.builder.get_object('combobox4').set_active_id(product_id) def add_product_match_selected(self, completion, model, iter_): product_id = model[iter_][0] self.builder.get_object('combobox1').set_active_id(product_id) self.builder.get_object('combobox4').set_active_id(product_id) def event_product_combo_changed(self, combo): product_id = combo.get_active_id() if product_id != None: self.product_id = product_id self.builder.get_object('combobox2').set_sensitive(True) store = self.builder.get_object('serial_number_store') store.clear() self.cursor.execute( "SELECT id, serial_number FROM serial_numbers " "WHERE product_id = %s", (product_id, )) for row in self.cursor.fetchall(): store.append([str(row[0]), row[1]]) def add_product_combo_changed(self, combo): product_id = combo.get_active_id() if product_id != None: self.product_id = product_id self.builder.get_object('entry2').set_sensitive(True) def contact_match_selected(self, completion, model, iter_): contact_id = model[iter_][0] if contact_id != None: self.contact_id = contact_id self.builder.get_object('textview1').set_sensitive(True) def contact_combo_changed(self, combo): contact_id = combo.get_active_id() if contact_id != None: self.contact_id = contact_id self.builder.get_object('textview1').set_sensitive(True) def add_serial_number_changed(self, entry): self.builder.get_object('button2').set_sensitive(True) def serial_number_match_selected(self, completion, model, iter_): serial_number = model[iter_][0] self.builder.get_object('combobox2').set_active_id(serial_number) def event_serial_number_changed(self, combobox): serial_id = combobox.get_active_id() if serial_id != None: self.serial_id = serial_id self.builder.get_object('combobox3').set_sensitive(True) def event_description_changed(self, entry): self.builder.get_object('button4').set_sensitive(True)
class ResourceManagementGUI: timeout_id = None time_clock = None def __init__(self, id_=None): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.cursor = DB.cursor() self.handler_ids = list() for connection in (("shutdown", self.main_shutdown), ): handler = broadcaster.connect(connection[0], connection[1]) self.handler_ids.append(handler) renderer = CellRendererRgbaArray() treecolumn = self.builder.get_object('treeviewcolumn6') treecolumn.pack_start(renderer, True) treecolumn.set_attributes(renderer, RGBA_array=7) renderer.set_property('editable', True) renderer.connect('editing-started', self.tag_editing_started) self.timer_timeout = None self.row_id = None self.join_filter = '' self.resource_store = self.builder.get_object('resource_store') self.contact_store = self.builder.get_object('contact_store') self.contact_completion = self.builder.get_object('contact_completion') self.contact_completion.set_match_func(self.contact_match_func) textview = self.builder.get_object('textview1') spell_check.add_checker_to_widget(textview) self.dated_for_calendar = DateTimeCalendar() no_date_button = self.builder.get_object('button5') self.dated_for_calendar.pack_start(no_date_button) treeview = self.builder.get_object('treeview1') self.dated_for_calendar.set_relative_to(treeview) self.dated_for_calendar.connect('day-selected', self.dated_for_calendar_day_selected) self.older_than_calendar = DateTimeCalendar() self.older_than_calendar.connect('day-selected', self.older_than_date_selected) entry = self.builder.get_object('entry1') self.older_than_calendar.set_relative_to(entry) if id_ != None: selection = self.builder.get_object('treeview-selection1') for row in self.resource_store: if row[0] == id_: selection.select_path(row.path) self.cursor.execute("SELECT notes FROM resources " "WHERE id = %s", (id_, )) for row in self.cursor.fetchall(): text = row[0] self.builder.get_object('notes_buffer').set_text(text) self.window = self.builder.get_object('window1') self.window.show_all() self.older_than_calendar.set_today() self.populate_stores() def destroy(self, widget): if self.timeout_id: self.save_notes() for handler in self.handler_ids: broadcaster.disconnect(handler) self.cursor.close() def main_shutdown(self, main): if self.timeout_id: self.save_notes() def focus_in_event(self, window, event): self.populate_stores() def row_limit_value_changed(self, spinbutton): self.populate_resource_store() def resource_threaded_checkbutton_toggled(self, checkbutton): self.populate_resource_store() def treeview_button_release_event(self, treeview, event): if event.button == 3: menu = self.builder.get_object('menu1') menu.popup_at_pointer() def delete_activated(self, menuitem): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path != []: row_id = model[path][0] try: self.cursor.execute("DELETE FROM resources " "WHERE id = %s", (row_id, )) DB.commit() except Exception as e: self.show_message(e) DB.rollback() self.populate_resource_store() def block_number_activated(self, menuitem): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path != []: phone_number = model[path][11] try: self.cursor.execute( "INSERT INTO phone_blacklist " "(number, blocked_calls) VALUES (%s, 0)", (phone_number, )) DB.commit() except Exception as e: self.show_message(e) DB.rollback() def contact_hub_activated(self, menuitem): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path != []: contact_id = model[path][2] if contact_id == 0: self.show_message("No contact selected for this row!") return import contact_hub contact_hub.ContactHubGUI(contact_id) def report_hub_activated(self, button): treeview = self.builder.get_object('treeview1') from reports import report_hub report_hub.ReportHubGUI(treeview) def remove_manual_sort_activated(self, menuitem): c = DB.cursor() c.execute("UPDATE resources SET sort = 0 WHERE posted = False") DB.commit() self.populate_resource_store() def remove_tags(self): flowbox = self.builder.get_object('tag_flowbox') for child in flowbox.get_children(): flowbox.remove(child) def populate_tag_flowbox(self): self.remove_tags() flowbox = self.builder.get_object('tag_flowbox') c = DB.cursor() c.execute( "SELECT " "tag, " "red, " "green, " "blue, " "alpha " "FROM resource_ids_tag_ids AS riti " "JOIN resource_tags AS rt ON rt.id = riti.resource_tag_id " "WHERE riti.resource_id = %s", (self.row_id, )) for row in c.fetchall(): tag_name = row[0] red = int(row[1] * 255) green = int(row[2] * 255) blue = int(row[3] * 255) alpha = int(row[4] * 255) hex_color = '#%02x%02x%02x%02x' % (red, green, blue, alpha) string = "<span foreground='%s'>%s</span>\n" % (hex_color, tag_name) label = Gtk.Label() label.set_markup(string) label.show() flowbox.add(label) def populate_tags_applied_store(self): store = self.builder.get_object('tags_applied_store') store.clear() c = DB.cursor() c.execute( "SELECT id::text, tag, red, green, blue, alpha, " "(SELECT True FROM resource_ids_tag_ids AS riti " "WHERE (riti.resource_id, resource_tag_id) = (%s, rt.id)) " "FROM resource_tags AS rt " "ORDER BY tag", (self.row_id, )) for row in c.fetchall(): tag_id = row[0] tag_name = row[1] rgba = Gdk.RGBA(1, 1, 1, 1) rgba.red = row[2] rgba.green = row[3] rgba.blue = row[4] rgba.alpha = row[5] applied = row[6] store.append([tag_id, tag_name, rgba, applied]) def tag_toggled(self, cellrenderertoggle, path): store = self.builder.get_object('tags_applied_store') active = cellrenderertoggle.get_active() tag_id = store[path][0] store[path][3] = not active selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return resource_id = model[path][0] c = DB.cursor() if not active: c.execute( "INSERT INTO resource_ids_tag_ids " "(resource_id, resource_tag_id) VALUES " "(%s, %s) " "ON CONFLICT (resource_id, resource_tag_id) " "DO NOTHING ", (resource_id, tag_id)) else: c.execute( "DELETE FROM resource_ids_tag_ids WHERE " "(resource_id, resource_tag_id) = " "(%s, %s)", (resource_id, tag_id)) DB.commit() def tag_popover_closed(self, popover): self.populate_tag_flowbox() selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return iter_ = self.resource_store.get_iter(path) self.populate_row_tag_list(iter_) def row_activated(self, treeview, path, treeview_column): if self.timeout_id: self.save_notes() self.row_id = self.resource_store[path][0] self.populate_notes() self.populate_tag_flowbox() self.populate_tags_applied_store() def populate_notes(self): if self.row_id == None: return self.cursor.execute("SELECT notes FROM resources " "WHERE id = %s", (self.row_id, )) for row in self.cursor.fetchall(): text = row[0] self.builder.get_object('notes_buffer').set_text(text) break else: self.builder.get_object('notes_buffer').set_text('') DB.rollback() def contact_match_func(self, completion, key, iter): split_search_text = key.split() for text in split_search_text: if text not in self.contact_store[iter][1].lower(): return False # no match return True # it's a hit! def contact_match_selected(self, completion, model, iter_): contact_id = model[iter_][0] selection = self.builder.get_object('treeview-selection1') tree_model, path = selection.get_selected_rows() id_ = tree_model[path][0] self.cursor.execute( "UPDATE resources " "SET (contact_id, phone_number) = " "(%s, (SELECT phone FROM contacts WHERE id = %s)) " "WHERE id = %s ", (contact_id, contact_id, id_)) DB.commit() self.populate_resource_store() def contact_editing_started(self, renderer_combo, combobox, path): entry = combobox.get_child() entry.set_completion(self.contact_completion) def contact_changed(self, renderer_combo, path, tree_iter): contact_id = self.contact_store[tree_iter][0] id_ = self.resource_store[path][0] self.cursor.execute( "UPDATE resources " "SET (contact_id, phone_number) = " "(%s, (SELECT phone FROM contacts WHERE id = %s)) " "WHERE id = %s", (contact_id, contact_id, id_)) DB.commit() self.populate_resource_store() def populate_stores(self): self.contact_store.clear() self.cursor.execute("SELECT " "id::text, " "name, " "ext_name, " "phone " "FROM contacts " "WHERE deleted = False ORDER BY name") for row in self.cursor.fetchall(): self.contact_store.append(row) active = self.builder.get_object('tag_combo').get_active() store = self.builder.get_object('tag_store') store.clear() store.append(['', 'All tags', Gdk.RGBA(0, 0, 0, 0)]) self.cursor.execute("SELECT id::text, tag, red, green, blue, alpha " "FROM resource_tags " "ORDER BY tag") for row in self.cursor.fetchall(): tag_id = row[0] tag_name = row[1] rgba = Gdk.RGBA(1, 1, 1, 1) rgba.red = row[2] rgba.green = row[3] rgba.blue = row[4] rgba.alpha = row[5] store.append([tag_id, tag_name, rgba]) self.builder.get_object('tag_combo').set_active(active) DB.rollback() def new_entry_clicked(self, button): self.cursor.execute("INSERT INTO resources " "(tag_id) " "SELECT id FROM resource_tags " "WHERE finished = False LIMIT 1 " "RETURNING id") new_id = self.cursor.fetchone()[0] DB.commit() self.populate_resource_store() for row in self.resource_store: if row[0] == new_id: treeview = self.builder.get_object('treeview1') c = treeview.get_column(0) treeview.set_cursor(row.path[0], c, True) break def post_entry_clicked(self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return row_id = model[path][0] c = DB.cursor() c.execute( "UPDATE resources SET posted = True WHERE id = %s; " "UPDATE time_clock_projects " "SET active = False " "WHERE resource_id = %s ", (row_id, row_id)) DB.commit() self.populate_resource_store() self.builder.get_object('notes_buffer').set_text('') def subject_edited(self, renderer_text, path, text): self.editing = False id_ = self.resource_store[path][0] self.cursor.execute( "UPDATE resources " "SET subject = %s " "WHERE id = %s", (text, id_)) DB.commit() self.resource_store[path][1] = text def dated_for_calendar_day_selected(self, calendar): date = calendar.get_date() selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() id_ = model[path][0] self.cursor.execute( "UPDATE resources " "SET dated_for = %s " "WHERE id = %s", (date, id_)) DB.commit() self.populate_resource_store() def dated_for_editing_started(self, widget, entry, text): event = Gtk.get_current_event() rect = Gdk.Rectangle() rect.x = event.x rect.y = event.y + 30 rect.width = rect.height = 1 self.dated_for_calendar.set_pointing_to(rect) selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() row_id = model[path][0] self.cursor.execute( "SELECT COALESCE(dated_for, CURRENT_DATE) " "FROM resources WHERE id = %s", (row_id, )) for row in self.cursor.fetchall(): self.dated_for_calendar.set_datetime(row[0]) GLib.idle_add(self.dated_for_calendar.show) # this hides the entry DB.rollback() def tag_editing_started(self, renderer_combo, combobox, path): event = Gtk.get_current_event() rect = Gdk.Rectangle() rect.x = event.x rect.y = event.y + 30 rect.width = rect.height = 1 combobox.hide() popover = self.builder.get_object('tag_popover') popover.set_pointing_to(rect) GLib.idle_add(popover.show) def sort_by_combo_changed(self, combobox): self.populate_resource_store() def tag_combo_changed(self, combobox): tag_id = combobox.get_active_id() if tag_id == None: return if tag_id == '': self.join_filter = '' else: self.join_filter = 'JOIN resource_ids_tag_ids AS riti '\ 'ON riti.resource_id = rm.id '\ 'AND riti.resource_tag_id = %s' % tag_id self.populate_resource_store() def notes_buffer_changed(self, text_buffer): #only save changes created by user if not self.builder.get_object('textview1').is_focus(): return selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return self.row_id = model[path][0] start = text_buffer.get_start_iter() end = text_buffer.get_end_iter() self.notes = text_buffer.get_text(start, end, True) if self.timeout_id: GLib.source_remove(self.timeout_id) self.timeout_id = GLib.timeout_add_seconds(10, self.save_notes) 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 populate_resource_store(self): id_ = None selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path != []: id_ = model[path][0] self.resource_store.clear() self.builder.get_object('notes_buffer').set_text('') row_limit = self.builder.get_object('spinbutton1').get_value() sort = self.builder.get_object('sort_by_combo').get_active_id() c = DB.cursor() c.execute("SELECT " "rm.id, " "subject, " "COALESCE(contact_id, 0), " "COALESCE(name, ''), " "COALESCE(ext_name, ''), " "to_char(timed_seconds, 'HH24:MI:SS')::text AS time, " "format_date(dated_for), " "'', " "phone_number, " "to_do " "FROM resources AS rm " "%s " "LEFT JOIN contacts " "ON rm.contact_id = contacts.id " "WHERE (dated_for <= '%s' OR dated_for IS NULL) " "AND posted = False " "ORDER BY %s, rm.id " "LIMIT %s" % (self.join_filter, self.older_than_date, sort, row_limit)) for row in c.fetchall(): row_id = row[0] iter_ = self.resource_store.append(row) self.populate_row_tag_list(iter_) if row_id == id_: selection.select_iter(iter_) c.close() self.populate_notes() DB.rollback() def populate_row_tag_list(self, iter_): row_id = self.resource_store[iter_][0] c = DB.cursor() tag_list = list() c.execute( "SELECT " "red, " "green, " "blue, " "alpha " "FROM resources AS r " "JOIN resource_ids_tag_ids AS riti " "ON riti.resource_id = r.id " "JOIN resource_tags AS rt " "ON rt.id = riti.resource_tag_id " "WHERE r.id = %s ORDER BY rt.id", (row_id, )) for row in c.fetchall(): rgba = Gdk.RGBA(row[0], row[1], row[2], row[3]) tag_list.append(rgba) self.resource_store[iter_][7] = tag_list def time_clock_project_clicked(self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: self.show_message("Please select a row") return resource_id = model[path][0] subject = model[path][1] self.builder.get_object('project_name_entry').set_text(subject) dialog = self.builder.get_object('time_clock_create_dialog') result = dialog.run() dialog.hide() if result != Gtk.ResponseType.ACCEPT: return subject = self.builder.get_object('project_name_entry').get_text() try: self.cursor.execute( "INSERT INTO time_clock_projects " "(name, start_date, active, permanent, " "resource_id) " "VALUES (%s, now(), True, False, %s)" "ON CONFLICT (resource_id) " "DO UPDATE SET name = %s " "WHERE time_clock_projects.resource_id = %s", (subject, resource_id, subject, resource_id)) except Exception as e: self.show_message(e) DB.rollback() return DB.commit() if self.builder.get_object( 'time_clock_checkbutton').get_active() == True: if not self.time_clock: import time_clock self.time_clock = time_clock.TimeClockGUI() else: self.time_clock.present() def older_than_entry_icon_released(self, entry, icon, event): self.older_than_calendar.set_relative_to(entry) self.older_than_calendar.show() def older_than_date_selected(self, calendar): date_text = calendar.get_text() self.builder.get_object('entry1').set_text(date_text) self.older_than_date = calendar.get_date() if self.older_than_date == None: self.older_than_date = datetime.today() self.populate_resource_store() def tags_activated(self, button): import resource_management_tags resource_management_tags.ResourceManagementTagsGUI() def down_clicked(self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return iter_ = model.get_iter(path) iter_next = model.iter_next(iter_) model.move_after(iter_, iter_next) self.save_row_ordering() self.builder.get_object('sort_by_combo').set_active_id('sort') def up_clicked(self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return iter_ = model.get_iter(path) iter_prev = model.iter_previous(iter_) model.move_before(iter_, iter_prev) self.save_row_ordering() self.builder.get_object('sort_by_combo').set_active_id('sort') 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 no_date_clicked(self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return id_ = model[path][0] self.cursor.execute( "UPDATE resources " "SET dated_for = NULL " "WHERE id = %s", (id_, )) DB.commit() self.populate_resource_store() self.dated_for_calendar.hide() 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 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 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.timeout_add(10, treeview.set_cursor, path, next_column, True) elif keyname == 'Escape': pass
class DocumentGUI: def __init__(self, main): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.edited_renderer_text = 1 self.qty_renderer_value = 1 self.main = main self.db = main.db 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.document_id = 0 self.documents_store = self.builder.get_object('documents_store') self.calendar = DateTimeCalendar(self.db) self.calendar.connect('day-selected', self.calendar_day_selected) 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.drag_dest_set(Gtk.DestDefaults.ALL, [], Gdk.DragAction.COPY) self.treeview.connect("drag-data-received", self.on_drag_data_received) self.treeview.drag_dest_set_target_list([enforce_target]) #self.treeview.drag_dest_set_target_list(None) #self.treeview.drag_dest_add_text_targets() self.import_store = self.builder.get_object('import_store') self.customer_store = self.builder.get_object('customer_store') self.product_store = self.builder.get_object('product_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) self.retailer_completion = self.builder.get_object('retailer_completion') self.retailer_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) minimum_column = self.builder.get_object ('treeviewcolumn8') minimum_renderer = self.builder.get_object ('cellrenderertext9') minimum_column.set_cell_data_func(minimum_renderer, self.minimum_cell_func) maximum_column = self.builder.get_object ('treeviewcolumn9') maximum_renderer = self.builder.get_object ('cellrenderertext10') maximum_column.set_cell_data_func(maximum_renderer, self.maximum_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) s_price_column = self.builder.get_object ('treeviewcolumn13') s_price_renderer = self.builder.get_object ('cellrenderertext13') s_price_column.set_cell_data_func(s_price_renderer, self.s_price_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.populate_product_store () self.populate_customer_store () self.calculate_totals () self.load_settings() self.window = self.builder.get_object('window') self.window.show_all() def load_settings (self): self.cursor.execute("SELECT column_id, column_name, visible " "FROM settings.document_columns") for row in self.cursor.fetchall(): column_id = row[0] column_name = row[1] visible = row[2] tree_column = self.builder.get_object(column_id) tree_column.set_title(column_name) tree_column.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 drag_finish(self, wid, context, x, y, time): print (wid, context, x, y, time) def on_drag_motion(self, wid, context, x, y, time): #print wid #l.set_text('\n'.join([str(t) for t in context.targets])) #context.drag_status(gtk.gdk.ACTION_COPY, time) #print context.list_targets() # Returning True which means "I accept this data". #print "movement" #return False pass def on_drag_data_received(self, widget, drag_context, x,y, data,info, time): _list_ = data.get_text().split(' ') if len(_list_) != 2: return table, _id_ = _list_[0], _list_[1] self.cursor.execute("SELECT product, remark, price FROM %s WHERE id = %s" % (table, _id_)) for row in self.cursor.fetchall(): product = row[0] remark = row[1] price = row[2] print ("please implement me") #FIXME def destroy(self, window): self.main.disconnect(self.handler_c_id) self.main.disconnect(self.handler_p_id) self.cursor.close() def treeview_button_release_event (self, treeview, event): if event.button == 3: menu = self.builder.get_object('right_click_menu') menu.popup(None, None, None, None, event.button, event.time) menu.show_all() def focus (self, window, event): document_type_combo = self.builder.get_object('comboboxtext2') active_type_id = document_type_combo.get_active_id() document_type_combo.remove_all() self.cursor.execute("SELECT id, name FROM document_types") for row in self.cursor.fetchall(): type_id = row[0] type_name = row[1] document_type_combo.append(str(type_id), type_name) document_type_combo.set_active_id(str(active_type_id)) def product_window(self, column): import products products.ProductsGUI(self.db) def contacts_window(self, widget): import contacts contacts.GUI(self.main, True) def view_document_clicked(self, widget): comment = self.builder.get_object('entry3').get_text() d = documenting.Setup(self.db, self.documents_store, self.contact_id, comment, self.date, self.document_type_id, self.document_name) d.view() def post_document_clicked(self, widget): comment = self.builder.get_object('entry3').get_text() d = documenting.Setup(self.db, self.documents_store, self.contact_id, comment, self.date, self.document_type_id, self.document_name ) if self.builder.get_object('menuitem1').get_active() == True: d.print_directly() #print "print_directly" else: d.print_dialog(self.window) #print "print dialog" d.post(self.document_id) if self.builder.get_object('menuitem4').get_active() == True: self.cursor.execute("SELECT * FROM contacts " "WHERE id = %s", (self.contact_id, )) for row in self.cursor.fetchall(): name = row[1] email = row[9] if email != "": email = "%s '< %s >'" % (name, email) d.email(email) self.db.commit() self.window.destroy() ################## start customer def populate_customer_store (self, m=None, i=None): self.customer_store.clear() self.cursor.execute("SELECT id, name FROM contacts " "WHERE (deleted, customer) = " "(False, True) ORDER BY 2") for i in self.cursor.fetchall(): contact_id = i[0] name = i[1] self.customer_store.append([str(contact_id),name ]) def customer_match_selected(self, completion, model, iter): self.contact_id = model[iter][0] self.customer_selected (self.contact_id) #return True def customer_match_func(self, completion, key, iter): if key in self.customer_store[iter][1].lower(): #key is the typed text (always lower case ?!) ; model[iter][1] is to match for every line in the model return True# it's a hit! return False # no match def customer_combobox_changed(self, widget, toggle_button=None): #updates the customer contact_id = widget.get_active_id() if contact_id != None: self.contact_id = contact_id self.customer_selected (self.contact_id) self.calculate_totals () def customer_selected(self, name_id): self.builder.get_object('comboboxtext2').set_sensitive(True) self.builder.get_object('button4').set_sensitive(True) self.builder.get_object('button11').set_sensitive(True) self.builder.get_object('button12').set_sensitive(True) self.cursor.execute("SELECT * FROM contacts WHERE id = (%s)",(name_id,)) for row in self.cursor.fetchall() : self.customer_name_default_label = row[1] self.builder.get_object('entry10').set_text(row[3]) self.builder.get_object('entry11').set_text(row[2]) self.builder.get_object('entry12').set_text(row[8]) self.builder.get_object('button2').set_sensitive(True) self.builder.get_object('menuitem2').set_sensitive(True) job_type_combo = self.builder.get_object('comboboxtext2') if job_type_combo.get_active() < 0 : job_type_combo.set_active(0) self.populate_import_store () ################## start qty def qty_cell_func(self, column, cellrenderer, model, iter1, data): qty = '{:,.1f}'.format(model.get_value(iter1, 1)) cellrenderer.set_property("text" , qty) def qty_edited(self, widget, path, text): self.documents_store[path][1] = round(float(text), 1) self.calculate_row_total(path) self.calculate_totals () self.save_document_line (path) ################## start minimum def minimum_cell_func(self, column, cellrenderer, model, iter1, data): minimum = '{:,.2f}'.format(model.get_value(iter1, 5)) cellrenderer.set_property("text" , minimum) def minimum_edited(self, widget, path, text): self.documents_store[path][5] = round(float(text), 2) self.save_document_line (path) ################## start maximum def maximum_cell_func(self, column, cellrenderer, model, iter1, data): maximum = '{:,.2f}'.format(model.get_value(iter1, 6)) cellrenderer.set_property("text" , maximum) def maximum_edited(self, widget, path, text): self.documents_store[path][6] = round(float(text), 2) self.save_document_line (path) ################## start freeze def freeze_toggled (self, cell_renderer, path): is_active = cell_renderer.get_active() self.documents_store[path][9] = not is_active self.save_document_line (path) ################## start remark def remark_edited(self, widget, path, text): self.documents_store[path][10] = text self.save_document_line (path) ################## start priority def priority_edited(self, widget, path, text): self.documents_store[path][11] = text self.calculate_totals () self.save_document_line (path) ################## start retailer def retailer_editing_started (self, renderer, combo, path): entry = combo.get_child() entry.set_completion (self.retailer_completion) def retailer_match_selected(self, completion, model, iter): retailer_id = model[iter][0] retailer_name = model[iter][1] selection = self.builder.get_object('treeview-selection') model, path = selection.get_selected_rows() self.documents_store[path][7] = int(retailer_id) self.documents_store[path][8] = retailer_name self.save_document_line (path) #return True def retailer_match_func(self, completion, key, iter): if key in self.customer_store[iter][1].lower(): #key is the typed text (always lower case ?!) ; model[iter][1] is to match for every line in the model return True# it's a hit! return False # no match def retailer_changed (self, combo, path, _iter): retailer_id = self.customer_store[_iter][0] retailer_name = self.customer_store[_iter][1] self.documents_store[path][7] = int(retailer_id) self.documents_store[path][8] = retailer_name self.save_document_line (path) ################## start price def s_price_cell_func(self, column, cellrenderer, model, iter1, data): price = '{:,.2f}'.format(model.get_value(iter1, 13)) cellrenderer.set_property("text" , price) def price_cell_func(self, column, cellrenderer, model, iter1, data): price = '{:,.2f}'.format(model.get_value(iter1, 12)) cellrenderer.set_property("text" , price) def s_price_edited (self, widget, path, text): self.documents_store[path][13] = float(text) self.save_document_line (path) def price_edited(self, widget, path, text): self.documents_store[path][12] = float(text) self.calculate_row_total(path) self.calculate_totals() self.save_document_line (path) def set_sticky_price(self, widget): self.price_renderer_value = widget.get_chars(0, -1) ################## end price def ext_price_cell_func(self, column, cellrenderer, model, iter1, data): ext_price = '{:,.2f}'.format(model.get_value(iter1, 14)) 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_renderer_editing_started (self, renderer, combo, path): completion = self.builder.get_object('product_completion') entry = combo.get_child() entry.set_completion(completion) 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_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): if int(product_id) == self.documents_store[path][2]: return # product did not change self.cursor.execute("SELECT name, ext_name FROM products " "WHERE id = %s", (product_id,)) tupl = self.cursor.fetchone() product_name, product_ext_name = tupl[0], tupl[1] self.documents_store[path][3] = product_name self.documents_store[path][4] = product_ext_name self.documents_store[path][2] = int(product_id) price = get_customer_product_price(self.db, self.contact_id, product_id) self.documents_store[path][12] = price self.calculate_row_total(path) self.calculate_totals() self.save_document_line (path)# auto save feature 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, stock) = " "(False, True, True) ORDER BY name ") for row in self.cursor.fetchall(): _id_ = row[0] name = row[1] ext_name = row[2] total_name = "%s {%s}"% (name, ext_name) self.product_store.append([str(_id_), total_name]) ################## end product def calculate_row_total (self, path): line = self.documents_store[path] qty = line[1] price = line[12] ext_price = qty * price line[14] = ext_price def calculate_totals (self, widget = None): self.subtotal = 0 self.tax = 0 self.total = 0 for item in self.documents_store: self.total = self.total + item[14] total = '${:,.2f}'.format(self.total) self.builder.get_object('entry8').set_text(total) def add_entry (self, widget): self.cursor.execute("SELECT id, name FROM products " "WHERE (deleted, sellable) = (False, True)") self.builder.get_object('button15').set_sensitive(True) for i in self.cursor.fetchall(): product_id = i[0] product_name = i[1] price = get_customer_product_price (self.db, self.contact_id, product_id) self.documents_store.append([0, 1.0, product_id, product_name, "", 0.0, 100.00, int(self.contact_id), self.customer_name_default_label, False, "", "1", price, 0.00, 1]) last = self.documents_store.iter_n_children () last -= 1 #iter_n_children starts at 1 ; set_cursor starts at 0 treeview = self.builder.get_object('treeview2') c = treeview.get_column(0) treeview.set_cursor(last , c, True) #set the cursor to the last appended item break def delete_entry (self, widget): selection = self.builder.get_object("treeview-selection") row, path = selection.get_selected_rows () document_line_item_id = self.documents_store[path][0] self.cursor.execute("DELETE FROM document_lines WHERE id = %s", (document_line_item_id,)) self.db.commit() self.populate_document_store () def save_document_line (self, path): line = self.documents_store[path] line_id = line[0] qty = line[1] product_id = line[2] product_name = line[3] product_ext_name = line[4] minimum = line[5] maximum = line[6] retailer_id = line[7] retailer_name = line[8] type_1 = line[9] remark = line[10] priority = line[11] price = line[12] s_price = line[13] ext_price = line[14] if retailer_id == 0: retailer_id = None if line_id == 0: self.cursor.execute("INSERT INTO document_lines (document_id, qty, product_id, min, max, retailer_id, type_1, remark, priority, price, s_price, ext_price, canceled, finished) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id", (self.document_id, qty, product_id, minimum, maximum, retailer_id, type_1, remark, priority, price, s_price, ext_price, False, 0.00)) line_id = self.cursor.fetchone()[0] line[0] = line_id else: self.cursor.execute("UPDATE document_lines SET (document_id, qty, product_id, min, max, retailer_id, type_1, remark, priority, price, s_price, ext_price) = (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) WHERE id = %s", (self.document_id, qty, product_id, minimum, maximum, retailer_id, type_1, remark, priority, price, s_price, ext_price, line_id)) self.db.commit() self.calculate_totals () 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 document_name_changed (self, widget): if self.document_id == 0: return document_name = widget.get_text() if " " in document_name: document_name = re.sub(" ", "", document_name) widget.set_text(document_name) return self.cursor.execute("UPDATE documents SET name = %s " "WHERE id = %s", (document_name, self.document_id)) self.db.commit() def delete_document_clicked (self, widget): self.cursor.execute("DELETE FROM documents WHERE id = %s", (self.document_id,)) self.db.commit() self.builder.get_object('entry3').set_text('') self.populate_import_store () def document_type_changed (self, widget): document_type = widget.get_active_text() if document_type != None: self.document_type = document_type self.document_type_id = widget.get_active_id() self.builder.get_object('window').set_title("New " + document_type) self.builder.get_object('button15').set_label("Post " + document_type) self.calendar.set_today() self.populate_import_store () def document_type_clicked (self, widget): import settings settings.GUI(self.db, 'document_types') def new_document_clicked (self, widget): if self.document_type == "": return self.document_id = 0 self.documents_store.clear() self.builder.get_object('label6').set_text(" Current %s : " % self.document_type) comment = self.builder.get_object('entry3').get_text() self.cursor.execute("INSERT INTO documents (contact_id, closed, invoiced, canceled, date_created, dated_for, document_type_id, pending_invoice) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING id", (self.contact_id, False, False, False, datetime.today(), self.date, self.document_type_id, False)) self.document_id = self.cursor.fetchone()[0] self.set_document_name () self.db.commit() self.builder.get_object('button13').set_sensitive(True) self.builder.get_object('button14').set_sensitive(True) def set_document_name (self): type_text = self.document_type[0:3] contact_name = self.builder.get_object('combobox-entry5').get_text() split_name = contact_name.split(' ') name_str = "" for i in split_name: name_str = name_str + i[0:3] name = name_str.lower() self.cursor.execute("SELECT format_date(%s)", (self.date,)) date = self.cursor.fetchone()[0] date = re.sub (" ", "_", date) self.document_name = type_text + "_" + str(self.document_id) + "_" + name + "_" + date self.cursor.execute("UPDATE documents SET name = %s WHERE id = %s", (self.document_name, self.document_id)) self.db.commit() self.builder.get_object('entry5').set_text(self.document_name) def populate_import_store (self): model, path = self.builder.get_object('treeview-selection5').get_selected_rows() self.import_store.clear() doc_count = 0 self.cursor.execute("SELECT id, name FROM documents " "WHERE (document_type_id, closed, canceled, " "contact_id) = " "(%s, False, False, %s)", (self.document_type_id, self.contact_id)) for row in self.cursor.fetchall(): doc_count += 1 line_id = row[0] line_name = row[1] self.import_store.append([line_id, line_name]) self.builder.get_object('button11').set_label("Existing documents (%s)" % doc_count) if path != []: self.builder.get_object('treeview-selection5').select_path(path) def import_window_clicked (self, widget): import_dialog = self.builder.get_object('import_dialog') self.populate_import_store () result = import_dialog.run() if result == Gtk.ResponseType.ACCEPT: self.import_document () import_dialog.hide() def import_document (self): selection = self.builder.get_object('treeview-selection5') model, path = selection.get_selected_rows() if path == []: return tree_iter = model.get_iter(path) self.document_id = model.get_value(tree_iter, 0) self.populate_document_store() self.builder.get_object('button13').set_sensitive(True) self.builder.get_object('button14').set_sensitive(True) self.builder.get_object('button15').set_sensitive(True) def populate_document_store (self): self.documents_store.clear() self.cursor.execute("SELECT " "name, " "dated_for, " "format_date(dated_for) " "FROM documents WHERE id = %s", (self.document_id,)) for row in self.cursor.fetchall(): self.document_name = row[0] self.date = row[1] self.builder.get_object('entry1').set_text(row[2]) self.builder.get_object('entry5').set_text(self.document_name) self.cursor.execute("SELECT dli.id, qty, p.id, p.name, ext_name, min, max, " "type_1, type_2, priority, remark, price, s_price, " "retailer_id, COALESCE(c.name, '') " "FROM document_lines AS dli " "JOIN products AS p ON dli.product_id = p.id " "LEFT JOIN contacts AS c ON dli.retailer_id = c.id " "WHERE document_id = %s ORDER BY dli.id", (self.document_id, ) ) for row in self.cursor.fetchall(): row_id = row[0] qty = row[1] product_id = row[2] product_name = row[3] ext_name = row[4] min = row[5] max = row[6] type_1 = row[7] type_2 = row[8] priority = row[9] remark = row[10] price = row[11] s_price = row[12] retailer_id = row[13] retailer_name = row[14] ext_price = qty * price self.documents_store.append([row_id, qty, product_id, product_name, ext_name, min, max, retailer_id, retailer_name, type_1, remark, priority, price, s_price, ext_price]) self.calculate_totals () def help_clicked (self, widget): 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.add_entry(None) if keyname == 'F3': self.delete_entry(None) def calendar_day_selected (self, calendar): day_text = calendar.get_text() self.date = calendar.get_date() self.builder.get_object('entry1').set_text(day_text) self.cursor.execute("UPDATE documents SET dated_for = %s " "WHERE id = %s", (self.date, self.document_id)) self.set_document_name () self.db.commit() def calendar_entry_icon_release (self, widget, icon, void): self.calendar.set_relative_to(widget) self.calendar.show() def clear_retailer_entry (self, menuitem): selection = self.builder.get_object("treeview-selection") store, path = selection.get_selected_rows () self.documents_store[path][7]= None self.documents_store[path][8] = '' line_id = self.documents_store[path][0] self.cursor.execute("UPDATE document_lines SET retailer_id = NULL " "WHERE id = %s", (line_id, )) self.db.commit() #barcode entry support code ******* def barcode_entry_activated (self, entry2): barcode = entry2.get_text() entry2.set_text('') self.cursor.execute("SELECT id, name, 1.00, ext_name " "FROM products WHERE barcode = %s",(barcode, )) for i in self.cursor.fetchall(): product_id = i[0] for index, row in enumerate(self.documents_store): if row[2] == product_id: row[1] += 1.0 # increase the qty by one self.save_document_line(index) treeview = self.builder.get_object('treeview2') c = treeview.get_column(0) treeview.set_cursor(index , c, False) #set the cursor to the last appended item return product_name = i[1] price = i[2] ext_name = i[3] self.documents_store.append([0, 1.0, product_id, product_name, ext_name, 0.0, 100.00, int(self.contact_id), self.customer_name_default_label, False, "", "1", price, 0.00, 1]) last = self.documents_store.iter_n_children ()-1 #last -= 1 #iter_n_children starts at 1 ; set_cursor starts at 0 ## function merged with above line self.save_document_line(last) treeview = self.builder.get_object('treeview2') c = treeview.get_column(0) treeview.set_cursor(last , c, False) #set the cursor to the last appended item return else: print ("please make a window to alert the user that the barcode does not exist!")
class PayStubGUI: def __init__(self): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.cursor = DB.cursor() self.calendar = DateTimeCalendar(DB) self.calendar.connect('day-selected', self.calendar_day_selected) self.calendar.set_today() self.time_sheet_copy_info = '' check_number = get_check_number(None) self.populate_employee_combobox() self.window = self.builder.get_object('window1') self.window.show_all() #t = '10/3' #print(get_gcalccmd_result(t)) #self.cursor.execute("DELETE FROM time_clock_entries WHERE employee_id = 179") #self.cursor.execute("DELETE FROM time_clock_entries WHERE employee_id = 78") #self.cursor.execute("UPDATE time_clock_entries SET (employee_paid,pay_stub_id) = (False,NULL) WHERE pay_stub_id >= 750 ") #self.cursor.execute("INSERT INTO payroll.fed_id_binder (date_entered,active,table_name) VALUES (CURRENT_TIMESTAMP,True,%s)",("single_daily",)) #self.cursor.execute("DELETE FROM payroll.pay_stubs WHERE id > 329") #print (self.cursor.execute("SELECT payroll.complete_paystubs(%s,%s)",(49,'Jan 31 2018'))) #DB.commit() def focus(self, winow, r): self.populate_bank_combo() self.builder.get_object('comboboxtext3').set_sensitive(True) def populate_bank_combo(self): bank_combo = self.builder.get_object('comboboxtext3') bank_id = bank_combo.get_active_id() bank_combo.remove_all() self.cursor.execute( "SELECT number, name FROM gl_accounts WHERE check_writing = True") for row in self.cursor.fetchall(): bank_number = row[0] bank_name = row[1] bank_combo.append(str(bank_number), bank_name) bank_combo.set_active_id(bank_id) def bank_combo_changed(self, combo=None): bank_account = combo.get_active_id() if bank_account != None: self.builder.get_object('button2').set_sensitive(True) self.bank_account = bank_account check_number = get_check_number(bank_account) self.builder.get_object('entry4').set_text(str(check_number)) self.builder.get_object('entry4').set_sensitive(True) self.builder.get_object('button2').set_sensitive(True) self.builder.get_object('button3').set_sensitive(True) def employee_combo_changed(self, widget): employee_combo = self.builder.get_object('combobox1') self.employee_id = employee_combo.get_active_id() #print(self.employee_id) def populate_employee_combobox(self): employee_combo = self.builder.get_object('combobox1') self.employee_store = self.builder.get_object("employee_store") self.employee_store.clear() self.cursor.execute( "SELECT DISTINCT employee_id,name " "FROM time_clock_entries " "JOIN contacts ON contacts.id = " "time_clock_entries.employee_id " "WHERE " "time_clock_entries.employee_paid = FALSE AND " "DATE_TRUNC('day', time_clock_entries.stop_time) <= %s " "ORDER BY employee_id ASC", (self.pay_ending_date, )) for row in self.cursor.fetchall(): employee_id = row[0] employee_name = row[1] self.employee_store.append([str(employee_id), employee_name]) self.employee_store.append([str(0), "Print All"]) #employee_combo.set_active_iter(0) #print(employee_combo.get_id_column()) def calendar_day_selected(self, calendar): self.end_date_text = calendar.get_text() #print(self.end_date_text) self.builder.get_object('entry2').set_text(self.end_date_text) self.pay_ending_date = calendar.get_datetime() self.populate_employee_combobox() def calendar_entry_icon_released(self, entry, icon, event): self.calendar.set_relative_to(entry) self.calendar.show() def populate_py3o_self_data(self): pp_info = Item() #print(str(self.employee_id) + ' line 134') self.cursor.execute( "SELECT " "name, " "address, " "city, " "state, " "zip, " "phone, " "checks_payable_to, " "employee_info.id, " "wage " "FROM " " payroll.employee_info JOIN public.contacts ON " " payroll.employee_info.employee_id = public.contacts.id " " WHERE " " employee_info.employee_id = %s " "ORDER BY payroll.employee_info.id DESC LIMIT 1 ", (self.employee_id, )) #print(self.cursor.fetchall()) for row in self.cursor.fetchall(): pp_info.ppd_end = self.end_date_text pp_info.temporary_copy = self.time_sheet_copy_info customer = Item() customer.name = row[0] self.name = row[0] customer.street = row[1] customer.city = row[2] customer.state = row[3] customer.zip = row[4] customer.phone = row[5] customer.pay_to = row[6] self.emp_info_id = row[7] wage = row[8] time_card = Item() #print(row[8]) self.cursor.execute( "SELECT ss,dentry_hrs,arrived,left_premises," "over_eight,time_out FROM payroll.daily_overtime_consolidation WHERE " "employee_id = %s and ss <= %s", (self.employee_id, self.pay_ending_date)) items = list() for row in self.cursor.fetchall(): item = Item() item.hrs_today = row[1] item.roll_call_date = datetime_to_user_date(row[0]) item.time_out = row[5] item.start_time = datetime_to_time_card_format(row[2]) item.ending_time = datetime_to_time_card_format(row[3]) item.over_eight = row[4] items.append(item) self.cursor.execute("SELECT * FROM company_info") company = Item() for row6 in self.cursor.fetchall(): company.name = row6[1] company.street = row6[2] company.city = row6[3] company.state = row6[4] company.zip = row6[5] company.country = row6[6] company.phone = row6[7] company.email = row6[9] company.fax = row6[8] company.website = row6[10] company.tax_number = row6[11] pp_info.ppd_end = datetime_to_user_date(self.pay_ending_date) pp_info.temporary_copy = 'Temporary Copy' deductions = list() self.cursor.execute( "SELECT description,amount FROM payroll.emp_pretax_div_ded" " WHERE (type,emp_id) = ('subtract',%s) AND pay_stubs_id IS NULL ", (self.employee_id, )) for line in self.cursor.fetchall(): deductions_item = Item() deductions_item.description = line[0] deductions_item.amount = line[1] deductions.append(deductions_item) dividends = list() self.cursor.execute( "SELECT description,amount FROM payroll.emp_pretax_div_ded" " WHERE (type,emp_id) = ('add',%s) AND pay_stubs_id IS NULL ", (self.employee_id, )) for line in self.cursor.fetchall(): dividends_item = Item() dividends_item.description = line[0] dividends_item.amount = line[1] dividends.append(dividends_item) cash_advance = list() self.cursor.execute( "SELECT date_inserted,amount_paid" " FROM payroll.emp_payments WHERE (employee_id) = (%s) AND pay_stub_id" " IS NULL ", (self.employee_id, )) for line in self.cursor.fetchall(): cash_advance_item = Item() cash_advance_item.date = str(datetime_to_user_date(line[0])) cash_advance_item.amount = line[1] cash_advance.append(cash_advance_item) self.cursor.execute( "SELECT sum(amount_paid)" " FROM payroll.emp_payments WHERE (employee_id) = (%s) AND pay_stub_id" " IS NULL", (self.employee_id, )) totals = Item() totals.prepayment_totals = self.cursor.fetchone()[0] check_number = self.builder.get_object('entry4').get_text() self.cursor.execute("SELECT payroll.initiate_paystubs(%s,%s)", (self.employee_id, self.end_date_text)) self.paystubs_id = self.cursor.fetchone()[0] self.cursor.execute( "SELECT payroll.complete_paystubs(%s,%s,%s)", (self.paystubs_id, check_number, self.bank_account)) i = (self.cursor.fetchone()[0].strip('()')).split(',') self.wage_ttl_pmt = i[1] #print(type(self.wage_ttl_pmt)) self.emp_payments_id = i[0] self.cursor.execute( "SELECT reg_hrs,overtime_hrs,cost_sharing," "profit_sharing,s_s_withheld,medicare_withheld,state_withheld," "fed_withheld,reg_ttl,overtime_ttl,pretax_payment_amnt FROM " "payroll.pay_stubs WHERE id = %s", (self.paystubs_id, )) totals.chk_payment_info = '' + check_number for line in self.cursor.fetchall(): totals.days_in_pp = '' totals.av_hr_day = '' totals.ttl_time_out = '' totals.acc_overtime = line[1] totals.base_pay_rate = wage totals.non_overtime_hrs = line[0] totals.overtime_pay_rate = wage * Decimal(1.5) totals.reg_rate_ttl = line[8] totals.overtime_rate_ttl = line[9] totals.tip = line[3] totals.dues = line[2] totals.pretax_sub_ttl = line[10] totals.ss_emp_share = line[4] totals.med_emp_share = line[5] totals.state_with_holding = line[6] totals.federal_with_holding = line[7] totals.wage_ttl_pmt = self.wage_ttl_pmt totals.ttl_in_ck_fmt = get_written_check_amount_text(self.wage_ttl_pmt) self.data = dict(contact = customer,company = company,totals = totals,\ items = items,pp_info = pp_info,dividends =dividends, deductions = deductions,cash_advance = cash_advance) def view_temp_copy(self, widget): if self.employee_id == str(0): for line in self.employee_store: if line[0] == 0: print(line[0] + ' returning') continue else: self.employee_id = line[0] self.print_directly = True self.view_temporary = True if self.employee_id == str(0): print('payroll complete') pass else: self.populate_py3o_self_data() if self.wage_ttl_pmt != "0.00": self.print_time_sheet() print(self.employee_id + ' printing temporary') else: print(self.employee_id + ' wage total is zero this month - skipping') else: self.print_directly = False self.view_temporary = True if self.employee_id == str(0): print('payroll complete') pass else: self.populate_py3o_self_data() if self.wage_ttl_pmt != "0.00": self.print_time_sheet() print(self.employee_id + ' printing temporary') else: print(self.employee_id + ' wage total is zero this month - skipping') self.populate_employee_combobox() DB.rollback() def enter_cash_advance(self, button): #self.print_check_clicked() pass def print_pay_close_clicked(self, button): if self.employee_id == str(0): for line in self.employee_store: if line[0] != 0: self.employee_id = line[0] self.print_directly = True check_number = get_check_number(self.bank_account) self.builder.get_object('entry4').set_text( str(check_number)) self.close_pay_period_pay_balance() else: return else: self.print_directly = False self.close_pay_period_pay_balance() self.populate_employee_combobox() check_number = get_check_number(self.bank_account) self.builder.get_object('entry4').set_text(str(check_number)) def close_pay_period_pay_balance(self): self.view_temporary = False if self.employee_id == str(0): print('payroll complete') pass else: self.populate_py3o_self_data() #return #self.print_time_sheet () if self.wage_ttl_pmt != "0.00": self.print_pay_check() DB.commit() else: DB.rollback() def help(self, button): pass def print_pay_check(self): from py3o.template import Template #import for every invoice or #there is an error about invalid magic header numbers self.check_name = "/tmp/pay_check" + self.name.split()[0] self.check_file_odt = self.check_name + ".odt" self.check_file_pdf = self.check_name + ".pdf" t = Template("./templates/pay_check.odt", self.check_file_odt, True) t.render( self.data ) #the self.data holds all the info to be passed to the template subprocess.call("odt2pdf " + self.check_file_odt, shell=True) p = printing.Operation(settings_file='pay_check', file_to_print=self.check_file_pdf, parent=self.window) if self.print_directly == False: result = p.print_dialog() else: result = p.print_directly() f = open(self.check_file_pdf, 'rb') dat = f.read() f.close() self.cursor.execute( "UPDATE payroll.emp_payments SET check_pdf = %s WHERE id = %s", (dat, self.emp_payments_id)) def print_time_sheet(self): from py3o.template import Template #import for every invoice or there is #an error about invalid magic header numbers self.time_card_name = "/tmp/time_card_" + self.name.split()[0] self.time_card_odt = self.time_card_name + ".odt" self.time_card_pdf = self.time_card_name + ".pdf" #self.tmp_timecard_file = "/tmp/" + self.document_odt t = Template("./templates/time_card_template.odt", self.time_card_odt, True) t.render( self.data ) #the self.data holds all the info to be passed to the template subprocess.call("odt2pdf " + self.time_card_odt, shell=True) p = printing.Operation(settings_file='time_card', file_to_print=self.time_card_pdf, parent=self.window) if self.print_directly == False: result = p.print_dialog() else: result = p.print_directly() f = open(self.time_card_pdf, 'rb') dat = f.read() f.close() self.cursor.execute( "UPDATE payroll.pay_stubs SET timecard_pdf = %s WHERE id = %s", (dat, self.paystubs_id)) #dividends window code********************************************************** def type_renderer_changed(self, cellrenderercombo, path, treeiter): #path is treeview path model = cellrenderercombo.get_property("model") text = model[treeiter][0] operator = model[treeiter][1] self.dividends_store[path][0] = text self.dividends_store[path][3] = operator def add_row_clicked(self, button): self.dividends_store.append([ "", "", '0.00', "", None, True, ]) pass def delete_row_clicked(self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path != []: tree_iter = model.get_iter(path) self.dividends_store.remove(tree_iter) def launch_dividends_window(self, button): self.window2 = self.builder.get_object("window2") self.dividends_store = self.builder.get_object("dividends_store") self.populate_employee_dividends_combobox() self.populate_dividends_store() self.view_paid_entries = self.builder.get_object("checkbutton1") self.view_paid_entries = False self.window2.show_all() def view_paid_entries(self, togglebutton): self.view_paid_entries = not self.view_paid_entries #self.employee_id = employee_combo.get_active_id() self.dividends_store.clear() self.populate_dividends_store() def description_edited(self, cellrenderertext, path, text): self.dividends_store[path][1] = text def amount_edited(self, cellrenderertext, path, text): self.dividends_store[path][2] = text def amount_editing_started(self, cellrenderer, celleditable, path): celleditable.set_numeric(True) def dividends_employee_combo_changed(self, widget): employee_combo = self.builder.get_object('comboboxtext4') self.employee_id = employee_combo.get_active_id() self.dividends_store.clear() self.populate_dividends_store() def populate_employee_dividends_combobox(self): dividends_employee_combo = self.builder.get_object('comboboxtext4') dividends_employee_combo.remove_all() self.cursor.execute( "SELECT DISTINCT employee_id,name " "FROM time_clock_entries " "JOIN contacts ON contacts.id = " "time_clock_entries.employee_id " "WHERE " "time_clock_entries.employee_paid = FALSE AND " "DATE_TRUNC('day', time_clock_entries.stop_time) <= %s " "ORDER BY employee_id ASC", (self.pay_ending_date, )) dividends_employee_combo.append(str(0), "Print All") for row in self.cursor.fetchall(): self.employee_id = row[0] employee_name = row[1] dividends_employee_combo.append(str(self.employee_id), employee_name) def populate_dividends_store(self): self.cursor.execute( "SELECT id,amount,description,pay_stubs_id,type " "FROM payroll.emp_pretax_div_ded WHERE emp_id" "= %s ", (self.employee_id, )) for row in self.cursor.fetchall(): paid = row[3] entry_id = row[0] amount = str(row[1]) description = row[2] types = row[4] if self.view_paid_entries == False: if paid == None: self.dividends_store.append( [types, description, amount, "", entry_id, not paid]) else: self.dividends_store.append( [types, description, amount, "", entry_id, not paid]) def save_dividends(self, button): for row in self.dividends_store: types = row[0] description = row[1] amount = row[2] entry_id = row[4] print(entry_id) paid = row[5] if paid == False: return if entry_id == 0: self.cursor.execute( "INSERT INTO payroll.emp_pretax_div_ded " " (emp_id,amount,description,type) VALUES (%s,%s,%s,%s)", (self.employee_id, amount, description, types)) else: self.cursor.execute( "UPDATE payroll.emp_pretax_div_ded " "SET (amount,description,type) = (%s,%s,%s) WHERE id = %s ", (amount, description, types, entry_id)) DB.commit() #end dividends window code****************************************************** def view_posted_checks(self, menuitem): self.cursor.execute( "SELECT check_pdf,employee_id FROM payroll.emp_payments WHERE pay_stub_id > 136" ) for line in self.cursor.fetchall(): file_data = line[0] emp_id = line[1] f = open("/tmp/test" + str(emp_id), 'wb') f.write(file_data) subprocess.call("xdg-open /tmp/test" + str(emp_id), shell=True) f.close()
class ResourceManagementGUI: timeout_id = None def __init__(self, main, id_ = None): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) main.connect("shutdown", self.main_shutdown) self.main = main self.db = main.db self.cursor = main.db.cursor() self.editing = False self.timer_timeout = None self.resource_store = self.builder.get_object('resource_store') self.contact_store = self.builder.get_object('contact_store') self.contact_completion = self.builder.get_object('contact_completion') self.contact_completion.set_match_func(self.contact_match_func) self.tag_store = self.builder.get_object('tag_store') textview = self.builder.get_object('textview1') spell_check.add_checker_to_widget (textview) self.dated_for_calendar = DateTimeCalendar() no_date_button = self.builder.get_object('button5') self.dated_for_calendar.pack_start(no_date_button) date_label = self.builder.get_object('treeviewcolumn5').get_widget() self.dated_for_calendar.set_relative_to(date_label) self.dated_for_calendar.connect('day-selected', self.dated_for_calendar_day_selected ) self.older_than_calendar = DateTimeCalendar() self.older_than_calendar.connect('day-selected', self.older_than_date_selected ) self.older_than_calendar.set_today() self.populate_stores() self.populate_resource_store () if id_ != None: selection = self.builder.get_object('treeview-selection1') for row in self.resource_store: if row[0] == id_: selection.select_path(row.path) self.editing_buffer = True self.cursor.execute("SELECT notes FROM resources " "WHERE id = %s", (id_,)) for row in self.cursor.fetchall(): text = row[0] self.builder.get_object('textbuffer1').set_text(text) break else: self.builder.get_object('textbuffer1').set_text('') self.editing_buffer = False self.window = self.builder.get_object('window1') self.window.show_all() def main_shutdown (self, main): if self.timeout_id: self.save_notes() def focus_in_event (self, window, event): self.populate_stores () def unfinished_only_toggled (self, togglebutton): self.populate_resource_store () def row_limit_value_changed (self, spinbutton): self.populate_resource_store () def resource_threaded_checkbutton_toggled (self, checkbutton): self.populate_resource_store () def treeview_button_release_event (self, treeview, event): if event.button == 3: menu = self.builder.get_object('menu1') menu.popup(None, None, None, None, event.button, event.time) menu.show_all() def delete_activated (self, menuitem): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path != []: row_id = model[path][0] try: self.cursor.execute("DELETE FROM resources " "WHERE id = %s", (row_id,)) self.db.commit() except Exception as e: self.show_message (e) self.db.rollback() self.populate_resource_store() def block_number_activated (self, menuitem): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path != []: phone_number = model[path][11] try: self.cursor.execute("INSERT INTO phone_blacklist " "(number, blocked_calls) VALUES (%s, 0)", (phone_number,)) self.db.commit() except Exception as e: self.show_message (e) self.db.rollback() def row_activated (self, treeview, path, treeview_column): if self.timeout_id: self.save_notes() self.editing_buffer = True row_id = self.resource_store[path][0] self.cursor.execute("SELECT notes FROM resources " "WHERE id = %s", (row_id,)) for row in self.cursor.fetchall(): text = row[0] self.builder.get_object('textbuffer1').set_text(text) break else: self.builder.get_object('textbuffer1').set_text('') self.editing_buffer = False def contact_match_func(self, completion, key, iter): split_search_text = key.split() for text in split_search_text: if text not in self.contact_store[iter][1].lower(): return False # no match return True # it's a hit! def contact_match_selected(self, completion, model, iter_): contact_id = model[iter_][0] selection = self.builder.get_object('treeview-selection1') tree_model, path = selection.get_selected_rows() id_ = tree_model[path][0] self.cursor.execute("UPDATE resources " "SET contact_id = %s " "WHERE id = %s", (contact_id, id_)) self.db.commit() self.editing = False self.populate_resource_store(id_) def contact_editing_started (self, renderer_combo, combobox, path): self.editing = True entry = combobox.get_child() entry.set_completion(self.contact_completion) def contact_changed (self, renderer_combo, path, tree_iter): contact_id = self.contact_store[tree_iter][0] id_ = self.resource_store[path][0] self.cursor.execute("UPDATE resources " "SET contact_id = %s " "WHERE id = %s", (contact_id, id_)) self.db.commit() self.editing = False self.populate_resource_store(id_) def contact_edited (self, renderer_combo, path, text): self.editing = False def contact_editing_canceled (self, renderer): self.editing = False def populate_stores (self): self.contact_store.clear() self.cursor.execute("SELECT id::text, name, ext_name, phone FROM contacts " "WHERE deleted = False ORDER BY name") for row in self.cursor.fetchall(): contact_id = row[0] contact_name = row[1] ext_name = row[2] contact_phone = row[3] self.contact_store.append([contact_id, contact_name, ext_name, contact_phone]) self.tag_store.clear() self.cursor.execute("SELECT id, tag, red, green, blue, alpha " "FROM resource_tags " "ORDER BY tag") for row in self.cursor.fetchall(): tag_id = row[0] tag_name = row[1] rgba = Gdk.RGBA(1, 1, 1, 1) rgba.red = row[2] rgba.green = row[3] rgba.blue = row[4] rgba.alpha = row[5] self.tag_store.append([str(tag_id), tag_name, rgba]) for row in self.resource_store: if row[8] == tag_id: row[10] = rgba def new_entry_clicked (self, button): self.cursor.execute("INSERT INTO resources " "(tag_id) " "SELECT id FROM resource_tags " "WHERE finished = False LIMIT 1 " "RETURNING id") new_id = self.cursor.fetchone()[0] self.populate_resource_store(new_id, new = True) def subject_editing_started (self, renderer_entry, entry, path): self.editing = True def subject_edited (self, renderer_text, path, text): self.editing = False id_ = self.resource_store[path][0] self.cursor.execute("UPDATE resources " "SET subject = %s " "WHERE id = %s", (text, id_)) self.db.commit() self.resource_store[path][1] = text def subject_editing_canceled (self, renderer): self.editing = False def dated_for_calendar_day_selected (self, calendar): date = calendar.get_date() selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() id_ = model[path][0] self.cursor.execute("UPDATE resources " "SET dated_for = %s " "WHERE id = %s", (date, id_)) self.db.commit() self.populate_resource_store(id_) def dated_for_editing_started (self, widget, entry, text): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() row_id = model[path][0] self.cursor.execute("SELECT dated_for FROM resources " "WHERE id = %s AND dated_for IS NOT NULL", (row_id,)) for row in self.cursor.fetchall(): self.dated_for_calendar.set_datetime(row[0]) break else: self.dated_for_calendar.set_today() GLib.idle_add(self.dated_for_calendar.show) def tag_editing_started (self, renderer_combo, combobox, path): self.editing = True def tag_changed (self, renderer_combo, path, tree_iter): self.editing = False tag_id = self.tag_store[tree_iter][0] id_ = self.resource_store[path][0] self.cursor.execute("UPDATE resources " "SET tag_id = %s " "WHERE id = %s; " "UPDATE time_clock_projects AS tcp " "SET active = NOT rt.finished " "FROM resource_tags AS rt " "WHERE (rt.id, rt.finished) = (%s, True) " "AND tcp.resource_id = %s", (tag_id, id_, tag_id, id_)) self.db.commit() self.populate_resource_store(id_) def tag_editing_canceled (self, renderer): self.editing = False def text_buffer_changed (self, text_buffer): if self.editing_buffer == True: return selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: self.show_message("Please select a row") return self.row_id = model[path][0] start = text_buffer.get_start_iter() end = text_buffer.get_end_iter() self.notes = text_buffer.get_text(start,end,True) if self.timeout_id: GLib.source_remove(self.timeout_id) self.timeout_id = GLib.timeout_add_seconds(10, self.save_notes) 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)) self.db.commit() self.timeout_id = None def populate_resource_store (self, id_ = None, new = False): self.resource_store.clear() row_limit = self.builder.get_object('spinbutton1').get_value() finished = self.builder.get_object('checkbutton3').get_active() self.cursor.execute("SELECT " "rm.id, " "subject, " "COALESCE(contact_id, 0), " "COALESCE(name, ''), " "COALESCE(ext_name, ''), " "to_char(timed_seconds, 'HH24:MI:SS')::text, " "dated_for, " "format_date(dated_for), " "rmt.id, " "tag, " "red, " "green, " "blue, " "alpha, " "phone_number, " "call_received_time, " "format_timestamp(call_received_time), " "to_do " "FROM resources AS rm " "LEFT JOIN resource_tags AS rmt " "ON rmt.id = rm.tag_id " "LEFT JOIN contacts " "ON rm.contact_id = contacts.id " "WHERE date_created <= %s " "AND finished != %s OR finished IS NULL " "AND diary != True ORDER BY rm.id " "LIMIT %s", (self.older_than_date, finished, row_limit)) for row in self.cursor.fetchall(): rgba = Gdk.RGBA(1, 1, 1, 1) row_id = row[0] subject = row[1] contact_id = row[2] contact_name = row[3] ext_name = row[4] time_formatted = row[5] dated_for = row[6] date_formatted = row[7] tag_id = row[8] tag_name = row[9] if tag_id == None: tag_id = 0 tag_name = '' else: rgba.red = row[10] rgba.green = row[11] rgba.blue = row[12] rgba.alpha = row[13] phone_number = row[14] call_received_time = row[15] c_r_time_formatted = row[16] to_do = row[17] iter_ = self.resource_store.append([row_id, subject, contact_id, contact_name, ext_name, 0, time_formatted, date_formatted, tag_id, tag_name, rgba, phone_number, call_received_time, c_r_time_formatted, to_do]) if row_id == id_: self.builder.get_object('treeview-selection1').select_iter(iter_) if new == True: treeview = self.builder.get_object('treeview1') c = treeview.get_column(0) path = self.resource_store.get_path(iter_) treeview.set_cursor(path, c, True) def time_clock_project_clicked (self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: self.show_message("Please select a row") return resource_id = model[path][0] subject = model[path][1] self.builder.get_object('project_name_entry').set_text(subject) dialog = self.builder.get_object('time_clock_create_dialog') result = dialog.run() dialog.hide() if result != Gtk.ResponseType.ACCEPT: return subject = self.builder.get_object('project_name_entry').get_text() try: self.cursor.execute("INSERT INTO time_clock_projects " "(name, start_date, active, permanent, " "resource_id) " "VALUES (%s, now(), True, False, %s)" "ON CONFLICT (resource_id) " "DO UPDATE SET name = %s " "WHERE time_clock_projects.resource_id = %s", (subject, resource_id, subject, resource_id)) except Exception as e: self.show_message (e) self.db.rollback () return self.db.commit() if self.builder.get_object('time_clock_checkbutton').get_active() == True: if not self.time_clock: import time_clock self.time_clock = time_clock.TimeClockGUI(self.main) else: self.time_clock.present() def older_than_entry_icon_released (self, entry, icon, event): self.older_than_calendar.set_relative_to(entry) self.older_than_calendar.show() def older_than_date_selected (self, calendar): date_text = calendar.get_text () self.builder.get_object('entry1').set_text(date_text) self.older_than_date = calendar.get_date() self.populate_resource_store () def tags_clicked (self, button): import resource_management_tags resource_management_tags.ResourceManagementTagsGUI (self.db) def no_date_clicked (self, button): selection = self.builder.get_object('treeview-selection1') model, path = selection.get_selected_rows() if path == []: return id_ = model[path][0] self.cursor.execute("UPDATE resources " "SET dated_for = NULL " "WHERE id = %s", ( id_, )) self.db.commit() self.populate_resource_store(id_) self.dated_for_calendar.hide() def to_do_toggled (self, renderer, path): active = not self.resource_store[path][14] self.resource_store[path][14] = active id_ = self.resource_store[path][0] self.cursor.execute("UPDATE resources SET to_do = %s " "WHERE id = %s", (active, id_)) self.db.commit() def show_message (self, message): dialog = Gtk.MessageDialog( self.window, 0, Gtk.MessageType.ERROR, Gtk.ButtonsType.CLOSE, str(message)) dialog.run() dialog.destroy() 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.timeout_add(10, treeview.set_cursor, path, next_column, True) elif keyname == 'Escape': pass