def __init__(self): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.customer_store = self.builder.get_object('customer_store') self.customer_store.clear() c = DB.cursor() c.execute("SELECT id::text, name, ext_name FROM contacts " "WHERE customer = True ORDER BY name") for row in c.fetchall(): self.customer_store.append(row) c.close() DB.rollback() sw = self.builder.get_object('scrolledwindow1') self.canvas = GooCanvas.Canvas() sw.add(self.canvas) self.canvas.set_property("automatic-bounds", True) self.canvas.set_property("bounds-padding", 10) customer_completion = self.builder.get_object('customer_completion') customer_completion.set_match_func(self.customer_match_func) window = self.builder.get_object('window') window.show_all()
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 create_new_job (self): description = self.get_object('entry1').get_text() time_clock_project = self.get_object('checkbutton2').get_active() contact_name = self.get_object('combobox-entry').get_text() iter_ = self.get_object('job_type_combo').get_active_iter() job_name = self.job_type_store[iter_][1] job_name_description = self.get_object('entry1').get_text() self.cursor.execute("INSERT INTO job_sheets " "(description, job_type_id, time_clock, " "date_inserted, invoiced, completed, contact_id) " "VALUES (%s, %s, %s, %s, %s, %s, %s) " "RETURNING id", (description, self.job_type_id, time_clock_project, datetime.today(), False, False, self.customer_id)) self.job_id = self.cursor.fetchone()[0] self.project_name = job_name + " : " + job_name_description if time_clock_project == True: self.cursor.execute("INSERT INTO time_clock_projects " "(name, start_date, active, permanent, " "job_sheet_id) " "VALUES (%s, %s, True, False, %s)", (self.project_name, datetime.today(), self.job_id)) DB.commit() self.populate_existing_job_combobox () self.get_object('comboboxtext2').set_active_id(str(self.job_id)) self.get_object('button7').set_sensitive(False)
def populate_combos(self): revenue_combo = self.get_object('revenue_combo') c = DB.cursor() c.execute("SELECT number::text, name " "FROM gl_accounts " "WHERE revenue_account = True ORDER BY name") for row in c.fetchall(): revenue_combo.append(row[0], row[1]) revenue_combo.set_active(0) expense_combo = self.get_object('expense_combo') c.execute("SELECT number::text, name " "FROM gl_accounts " "WHERE expense_account = True ORDER BY name") for row in c.fetchall(): expense_combo.append(row[0], row[1]) expense_combo.set_active(0) tax_rate_combo = self.get_object('tax_rate_combo') c.execute("SELECT id::text, name " "FROM tax_rates " "WHERE (deleted, exemption) = (False, False) ORDER BY name") for row in c.fetchall(): tax_rate_combo.append(row[0], row[1]) tax_rate_combo.set_active(0) c.close() DB.rollback()
def product_completion_match_selected(self, combo, model, iter_): c = DB.cursor() product_id = model[iter_][0] c.execute("UPDATE products SET catalog = True" " WHERE id = %s", (product_id, )) DB.commit() GLib.idle_add(self.load_catalog_clicked)
def populate_product_treeview_store(self): progressbar = self.builder.get_object('progressbar') treeview = self.builder.get_object('treeview1') store = self.builder.get_object('product_store') treeview.set_model(None) store.clear() c = DB.cursor() c.execute("SELECT p.id, " "p.name, " "p.ext_name, " "p.barcode, " "COALESCE(c.name, ''), " "COALESCE(vpn.vendor_sku, ''), " "COALESCE(vpn.vendor_barcode, ''), " "p.deleted, " "p.stock, " "p.sellable, " "p.purchasable, " "p.manufactured " "FROM products AS p " "LEFT JOIN vendor_product_numbers AS vpn " "ON vpn.product_id = p.id " "LEFT JOIN contacts AS c ON vpn.vendor_id = c.id") p_tuple = c.fetchall() rows = len(p_tuple) for row_count, row in enumerate(p_tuple): progressbar.set_fraction((row_count + 1) / rows) store.append(row) while Gtk.events_pending(): Gtk.main_iteration() treeview.set_model(self.builder.get_object('sorted_product_store')) DB.rollback()
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) DB.rollback()
def save_clicked (self, button): aisle = self.get_object('entry5').get_text() rack = self.get_object('entry6').get_text() cart = self.get_object('entry7').get_text() shelf = self.get_object('entry8').get_text() cabinet = self.get_object('entry9').get_text() drawer = self.get_object('entry10').get_text() _bin_ = self.get_object('entry11').get_text() location_id = self.get_object('location_combo').get_active_id() c = DB.cursor() c.execute("INSERT INTO product_location " "(product_id, location_id, aisle, rack, cart, " "shelf, cabinet, drawer, bin) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) " "ON CONFLICT (product_id, location_id) " "DO UPDATE SET " "(aisle, rack, cart, shelf, cabinet, drawer, bin) = " "(%s, %s, %s, %s, %s, %s, %s) " "WHERE " "(product_location.product_id, " "product_location.location_id) = (%s, %s)", (self.product_id, location_id, aisle, rack, cart, shelf, cabinet, drawer, _bin_, aisle, rack, cart, shelf, cabinet, drawer, _bin_, self.product_id, location_id)) DB.commit() c.close() self.window.destroy()
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 DB.rollback()
def qty_edited(self, widget, path, text): if self.invoice_store[path][12] == True: try: text = int(text) # only allow whole numbers for inventory except Exception as e: self.show_error_dialog(str(e)) return False 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)) DB.commit() except psycopg2.DataError as e: self.show_error_dialog(str(e)) 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()
def delete_invoice_clicked(self, button): self.cursor.execute( "UPDATE invoices SET canceled = True " "WHERE id = %s", (self.invoice_id, )) DB.commit() self.populate_document_list() self.invoice_store.clear()
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)) DB.commit()
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], )) DB.commit()
def calculate_totals(self, widget=None): c = 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] 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) self.invoice = None
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 populate_vendor_invoice_store(self): self.vendor_invoice_store.clear() self.c_c_multi_payment_store.clear() if self.builder.get_object('checkbutton1').get_active() == True: self.cursor.execute("SELECT id, invoice_description, amount_due, " "date_created::text, " "format_date(date_created) " "FROM purchase_orders " "WHERE (canceled, invoiced, paid) = " "(False, True, False) " "ORDER BY date_created") else: self.cursor.execute( "SELECT id, invoice_description, amount_due, " "date_created::text, " "format_date(date_created) " "FROM purchase_orders " "WHERE (vendor_id, canceled, invoiced, paid) = " "(%s, False, True, False) " "ORDER BY date_created", (self.vendor_id, )) for row in self.cursor.fetchall(): self.vendor_invoice_store.append(row) self.check_cash_entries_valid() self.check_credit_card_entries_valid() self.check_cheque_entries_valid() DB.rollback()
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 location_combo_changed (self, combobox): location_id = combobox.get_active_id() c = DB.cursor() try: c.execute("SELECT aisle, rack, cart, shelf, cabinet, drawer, bin " "FROM product_location " "WHERE (product_id, location_id) = (%s, %s) " "FOR UPDATE NOWAIT", (self.product_id, location_id)) except psycopg2.OperationalError as e: DB.rollback() c.close() error = str(e) + "Hint: somebody else is editing this product location" self.show_message (error) self.window.destroy() return False for row in c.fetchall(): self.get_object('entry5').set_text(row[0]) self.get_object('entry6').set_text(row[1]) self.get_object('entry7').set_text(row[2]) self.get_object('entry8').set_text(row[3]) self.get_object('entry9').set_text(row[4]) self.get_object('entry10').set_text(row[5]) self.get_object('entry11').set_text(row[6]) c.close()
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 remark_edited(self, widget, path, text): _id_ = self.job_sheet_line_item_store[path][0] self.job_sheet_line_item_store[path][3] = text self.cursor.execute( "UPDATE job_sheet_items " "SET remark = %s WHERE id = %s", (text, _id_)) DB.commit()
def __init__ (self, entry_ids): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) store = self.builder.get_object('lookup_store') c = DB.cursor() c.execute( "SELECT " "'Purchase Order', " "po.id, " "p.name, " "poli.price::text, " "v.name, " "po.date_created::text, " "format_date(po.date_created) " "FROM purchase_orders AS po " "JOIN contacts AS v ON v.id = po.vendor_id " "JOIN purchase_order_items AS poli " "ON poli.purchase_order_id = po.id " "JOIN gl_entries AS ge ON poli.gl_entries_id = ge.id " "JOIN products AS p ON p.id = poli.product_id " "WHERE ge.id IN %s " "UNION " "SELECT " "'Invoice', " "i.id, " "p.name, " "ii.price::text, " "c.name, " "i.date_created::text, " "format_date(i.date_created) " "FROM invoices AS i " "JOIN contacts AS c ON c.id = i.customer_id " "JOIN invoice_items AS ii " "ON ii.invoice_id = i.id " "JOIN gl_entries AS ge ON ii.gl_entries_id = ge.id " "JOIN products AS p ON p.id = ii.product_id " "WHERE ge.id IN %s " "UNION " "SELECT " "'Payment', " "p.id, " "payment_info(p.id), " "p.amount::text, " "c.name, " "p.date_inserted::text, " "format_date(p.date_inserted) " "FROM payments_incoming AS p " "JOIN contacts AS c ON c.id = p.customer_id " "JOIN gl_entries AS ge ON p.gl_entries_id = ge.id " "WHERE ge.id IN %s " % (entry_ids, entry_ids, entry_ids) ) for row in c.fetchall(): store.append(row) c.close() DB.rollback() self.builder.get_object('window').show_all()
def __init__(self): self.builder = Gtk.Builder() self.builder.add_from_file(UI_FILE) self.builder.connect_signals(self) self.cursor = DB.cursor() self.sunday_store = self.builder.get_object('liststore1') self.monday_store = self.builder.get_object('liststore2') self.tuesday_store = self.builder.get_object('liststore3') self.wednesday_store = self.builder.get_object('liststore4') self.thursday_store = self.builder.get_object('liststore5') self.friday_store = self.builder.get_object('liststore6') self.saturday_store = self.builder.get_object('liststore7') self.populate_employee_combobox() self.populate_project_combobox() #-----for proper functionality we need to use the last possible second of today----- r = datetime.strftime(datetime.fromtimestamp(time.time()), "%b %d %Y").split() f = ssl.cert_time_to_seconds( str(r[0]) + " " + str(r[1]) + " 00:00:00 " + str(r[2]) + " GMT") self.last_second_of_target_week = float(f) + 86399.5 self.previous_week_time = self.last_second_of_target_week - 604800 self.first_day = True self.add_day_headers() DB.rollback() self.window = self.builder.get_object('window1') self.window.show_all()
def __init__ (self): Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) self.cursor = DB.cursor() self.calendar = DateTimeCalendar() self.calendar.connect('day-selected', self.calendar_day_selected) self.date = None self.contact_store = self.get_object('contact_store') self.invoice_store = self.get_object('invoice_store') self.cursor.execute("SELECT " "id::text, name, ext_name " "FROM contacts AS c " "WHERE deleted = False " "ORDER BY name") for row in self.cursor.fetchall(): self.contact_store.append(row) DB.rollback() contact_completion = self.get_object('contact_completion') contact_completion.set_match_func(self.contact_match_func) self.window = self.get_object('window') self.window.show_all()
def import_with_generated_barcodes(self): revenue_account = self.get_object('revenue_combo').get_active_id() expense_account = self.get_object('expense_combo').get_active_id() tax_rate_id = self.get_object('tax_rate_combo').get_active_id() progressbar = self.get_object('progressbar1') model = self.get_object('product_import_store') c = DB.cursor() total = len(model) for row_count, row in enumerate(model): c.execute( "INSERT INTO products (" "name, " "ext_name, " "description, " "cost, " "weight, " "tare, " "sellable, " "purchasable, " "manufactured, " "job, " "stock," "revenue_account," "default_expense_account," "tax_rate_id, " "unit)" "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," "1)", (row[0], row[1], row[2], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], revenue_account, expense_account, tax_rate_id)) progressbar.set_fraction(float(row_count) / total) while Gtk.events_pending(): Gtk.main_iteration() c.close() DB.commit()
def add_non_stock_product (vendor_id, product_name, product_number, #FIXME expense_account, revenue_account): cursor = DB.cursor() cursor.execute("SELECT id FROM tax_rates WHERE standard = True") default_tax_rate = cursor.fetchone()[0] cursor.execute("INSERT INTO products (name, description, unit, cost, " " tax_rate_id, deleted, sellable, " "purchasable, min_inventory, reorder_qty, tax_exemptible, " "manufactured, weight, tare, ext_name, stock, " "inventory_enabled, default_expense_account, " "revenue_account) " "VALUES (%s, '', 1, 1.00, " "%s, False, False, True, 0, 0, True, False, 0.00, 0.00, " "'', False, False, %s, %s) RETURNING id", ( product_name, default_tax_rate, expense_account, revenue_account)) product_id = cursor.fetchone()[0] cursor.execute("UPDATE products SET barcode = %s WHERE id = %s", (product_id, product_id)) cursor.execute("INSERT INTO vendor_product_numbers " "(vendor_sku, vendor_id, product_id, vendor_barcode) " "VALUES (%s, %s, %s, '')", (product_number, vendor_id, product_id)) DB.commit() cursor.close() return product_id
def customer_match_selected(self, entrycompletion, treemodel, treeiter): self.customer_id = treemodel[treeiter][0] group = GooCanvas.CanvasGroup() self.canvas.set_root_item(group) self.populate_invoices() self.populate_payments() DB.rollback()
def new_product (self): c = DB.cursor() c.execute("SELECT number, name FROM gl_accounts " "WHERE revenue_account = True " "ORDER BY number LIMIT 1") for row in c.fetchall(): self.revenue_account = row[0] self.get_object('revenue_entry').set_text(row[1]) break else: print("No revenue accounts available") self.show_message ("No revenue accounts available") c.execute("SELECT number, name FROM gl_accounts " "WHERE expense_account = True " "ORDER BY number LIMIT 1") for row in c.fetchall(): self.expense_account = row[0] self.get_object('expense_entry').set_text(row[1]) break else: print("No expense accounts available") self.show_message ("No expense accounts available") self.inventory_account = None c.execute("SELECT id FROM tax_rates WHERE standard = True ") default_id = c.fetchone()[0] self.get_object('comboboxtext4').set_active_id(str(default_id)) c.close() DB.rollback()
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 __init__(self): Gtk.Builder.__init__(self) self.add_from_file(UI_FILE) self.connect_signals(self) shipping_store = self.get_object('shipping_store') shipping_store.clear() c = DB.cursor() c.execute("SELECT " "si.id, " "si.tracking_number, " "COALESCE(i.id, 0), " "reason, " "c.name, " "COALESCE(ii.amount, 0.00), " "COALESCE(ii.amount::text, 'N/A'), " "date_shipped::text, " "format_date(date_shipped) " "FROM shipping_info AS si " "JOIN contacts AS c ON c.id = si.contact_id " "LEFT JOIN invoices AS i ON i.id = si.invoice_id " "LEFT JOIN incoming_invoices AS ii " "ON ii.id = si.incoming_invoice_id " "ORDER BY date_shipped") for row in c.fetchall(): shipping_store.append(row) c.close() self.window = self.get_object('window') self.window.show_all() DB.rollback()
def calendar_func(self, calendar, year, month, day): date = "%s %s %s" % (month + 1, day, year) date_time = datetime.strptime(date, "%m %d %Y") string = str() string += self.get_holiday_description(date_time) self.cursor.execute("SELECT subject, red, green, blue, alpha, " "COALESCE(' : ' || name, '') " "FROM resources AS rm " "LEFT JOIN contacts " "ON contacts.id = rm.contact_id " "JOIN resource_tags AS rmt " "ON rm.tag_id = rmt.id WHERE dated_for = '%s' " "AND finished != True %s" % (date_time, self.where_clause)) for row in self.cursor.fetchall(): subject = row[0] red = row[1] green = row[2] blue = row[3] alpha = row[4] contact_name = row[5] hex_color = '#%02x%02x%02x%02x' % (int(red * 255), int( green * 255), int(blue * 255), int(alpha * 255)) string += "<span foreground='%s' weight='bold'>%s%s</span>\n" % ( hex_color, subject, contact_name) DB.rollback() return string
def init_db(): DB.connect() DB.drop_tables( [Company, DeliveryNoteItem, DeliveryNote, Vehicle, Settings], safe=True) DB.create_tables( [Company, DeliveryNoteItem, DeliveryNote, Vehicle, Settings], safe=True)