def findProduct(self, productIdentifier, qty): conn = connectToDB() qry = 'SELECT p.id FROM products p where p.codeName LIKE "%s"' % ( productIdentifier) curs = conn.cursor() curs.execute(qry) r = curs.fetchone() print(qry) if (r is None): qry = 'SELECT p.id FROM products p where p.barcode LIKE "%s"' % ( productIdentifier) print('inside cart.py class terminal findProduct: ' + str(qry)) curs.execute(qry) r = curs.fetchone() print('inside cart.py class terminal print r when none: ' + str(r)) if r is not None: print('inside cart.py class terminal print r when not none : ' + str(r)) for p in self.cart.products: if r['id'] == p.pid: return False x = self.scanProduct(r['id'], qty) return x
def journalChange(self, event): conn = connectToDB() r = event.GetRow() c = event.GetCol() if c == 0: self.m_journalGrid.SetCellValue(r, c, event.GetString()) return if c in [4, 7, 8]: if not self.m_journalGrid.GetCellValue(r, c).isdigit(): self.m_journalGrid.SetCellValue(r, c, event.GetString()) return qry = 'UPDATE `generalLedger` SET dateTime = "%s", headOfAc = "%s", transactionType = "%s", chequeNo = "%s", Debit = "%s", Credit = "%s" WHERE id = %s' % ( self.m_journalGrid.GetCellValue(r, 1) + " " + self.m_journalGrid.GetCellValue(r, 2), self.m_journalGrid.GetCellValue( r, 4), self.m_journalGrid.GetCellValue( r, 5), self.m_journalGrid.GetCellValue( r, 6), self.m_journalGrid.GetCellValue( r, 7), self.m_journalGrid.GetCellValue( r, 8), self.m_journalGrid.GetCellValue(r, 0)) curs = conn.cursor() curs.execute(qry) conn.commit()
def SaveConnString(self, event): username = self.name.GetValue() passwd = self.passwd.GetValue() access = str(int(self.cashSale.GetValue())) + str( int(self.invoice.GetValue()) ) + str(int(self.quote.GetValue())) + str(int(self.purchase.GetValue( ))) + str(int(self.stockLevels.GetValue( ))) + str(int(self.cashSaleInfo.GetValue())) + str( int(self.invoiceInfo.GetValue())) + str( int(self.purchaseInfo.GetValue())) + str( int(self.quoteInfo.GetValue())) + str( int(self.customerInfo.GetValue())) + str( int(self.supplierInfo.GetValue()) ) + str(int(self.journal.GetValue( ))) + str(int(self.accountsByFolio.GetValue())) + str( int(self.controlAccount.GetValue())) + str( int(self.incomeStatement.GetValue())) + str( int(self.users.GetValue())) qry = 'INSERT INTO users (username, password, access) VALUES ("%s", "%s", "%s")' % ( username, passwd, access) conn = connectToDB() curs = conn.cursor() curs.execute(qry) conn.commit() self.Destroy()
def returnPurchase(purchaseId): conn = connectToDB() qry = 'UPDATE purchase SET returned=1 WHERE id = %s' % (purchaseId) curs = conn.cursor() curs.execute(qry) af.purchaseReturnEntry(purchaseId)
def commitInsertQuery(self, qry): conn = connectToDB() curs = conn.cursor() curs.execute(qry) insertId = conn.insert_id() conn.commit() return insertId
def populateTable(self): conn = connectToDB() #print(self.m_startDate.GetValue()) #print(self.m_endDate.GetValue().Format("%F")) qry = 'SELECT gl.id, gl.dateTime, hoa.description, gl.headOfAc, gl.transactionType, gl.chequeNo, gl.Debit, gl.Credit FROM generalLedger gl, headOfAccounts hoa where gl.headOfAc = hoa.id and gl.dateTime BETWEEN "%s" AND "%s" ORDER BY gl.dateTime, id LIMIT 500' % ( self.m_startDate.GetValue().Format("%F") + " 00:00:00", self.m_endDate.GetValue().Format("%F") + " 23:59:59") curs = conn.cursor() curs.execute(qry) row = 0 while (1): r = curs.fetchone() if (r is not None): x = re.search("(?<=Customer)[0-9]*", r['description']) if x is not None: q = 'SELECT name FROM customer WHERE id = %s' % ( x.group(0)) c = conn.cursor() c.execute(q) cust = c.fetchone() r['description'] = cust['name'] + " A/C Recievable" x = re.search("(?<=Supplier)[0-9]*", r['description']) if x is not None: q = 'SELECT name FROM supplier WHERE id = %s' % ( x.group(0)) c = conn.cursor() c.execute(q) cust = c.fetchone() r['description'] = cust['name'] + " A/C Payable" self.m_journalGrid.SetCellValue(row, 0, str(r['id'])) self.m_journalGrid.SetCellValue(row, 1, str(r['dateTime'])[:10]) self.m_journalGrid.SetCellValue(row, 2, str(r['dateTime'])[11:]) if (r['Credit'] > 0): self.m_journalGrid.SetCellValue( row, 3, " " + r['description']) self.m_journalGrid.SetCellValue(row, 8, str(r['Credit'])) else: self.m_journalGrid.SetCellValue(row, 3, r['description']) self.m_journalGrid.SetCellValue(row, 7, str(r['Debit'])) self.m_journalGrid.SetCellValue(row, 4, str(r['headOfAc'])) self.m_journalGrid.SetCellValue(row, 5, r['transactionType']) if (r['chequeNo'] is not None): self.m_journalGrid.SetCellValue(row, 6, r['chequeNo']) #self.m_journalGrid.SetCellValue(row, 7, str(r['Debit'])) #self.m_journalGrid.SetCellValue(row, 8, str(r['Credit'])) row = row + 1 else: break
def fetchAllCodenames (self): qry = 'SELECT codeName FROM products' conn = connectToDB() curs = conn.cursor() curs.execute(qry) r = curs.fetchall() sug = [] for x in r: sug.append(x['codeName']) return sug
def suggestionCandidates (self, searchString): qry = 'SELECT products.name, products.codeName, products.costPrice, products.sellingPrice, products.barcode FROM `currentinventory`, `products` WHERE products.id = currentinventory.productId AND codeName LIKE "%'+str(searchString)+'%"' conn = connectToDB() curs = conn.cursor() curs.execute(qry) r = curs.fetchall() menu = wx.Menu() for x in r: help = "Rs. " + str(x['costPrice']) + " Rs. " + str(x['sellingPrice']) + " " + str(x['barcode']) menu.Append(wx.ID_NEW, x['codeName'], helpString=help) return menu
def __init__(self): self.conn = connectToDB() self.cart = cart() self.customerId = 0 self.customerName = '' self.customerContact = '' self.supplierId = 0 self.supplierName = '' self.operatorId = 0
def suggestionCandidatesAsList (self, searchString): qry = 'SELECT DISTINCT products.name, products.codeName, products.costPrice, products.sellingPrice, products.barcode FROM `currentinventory`, `products` WHERE products.id = currentinventory.productId AND codeName LIKE "%'+str(searchString)+'%" OR BARCODE LIKE "%'+str(searchString)+'%"' conn = connectToDB() curs = conn.cursor() curs.execute(qry) r = curs.fetchall() sug = [] for x in r: prod = x['codeName'] + " - "+x['name']+" Rs. " + str(x['costPrice']) + " Rs. " + str(x['sellingPrice']) + " " + str(x['barcode']) sug.append(prod) sug.append("New Product") return sug
def deleteUser(self, event): x = wx.MessageDialog(self, "Are you sure you want to delete this user?", "Delete User", wx.OK | wx.CANCEL) if x.ShowModal() == wx.ID_OK: qry = 'DELETE FROM users WHERE id = %s' % ( self.usersGrid.GetCellValue(event.GetRow(), 0)) con = connectToDB() curs = con.cursor() curs.execute(qry) con.commit() self.updateUsers()
def fetchSupplierId(self, contact): conn = connectToDB() qry = "SELECT id, name from supplier WHERE contact = '%s'" % str( contact) curs = conn.cursor() curs.execute(qry) r = curs.fetchone() if r is not None: self.supplierId = r['id'] self.supplierName = r['name'] return True return False
def populateTable(self): qry = 'SELECT q.id, SUBSTRING(q.dateTime, 1, 11), SUBSTRING(q.dateTime, 12), q.totalBill, q.expiryDate, q.converted, c.name, c.contact, c.address from quotations q, customer c WHERE q.customer=c.id ORDER BY q.id DESC' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): qry = 'select s.id, s.name, s.iban, s.contact, sum(p.totalBill - p.amountPaid) as balance from purchase p, supplier s where p.supplier = s.id group by s.id' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): qry = 'select c.id, c.name, c.contact, c.address, sum(i.amount - i.amountRecieved) as balance from customer c, invoice i where i.buyerId = c.id group by c.id' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): qry = 'SELECT p.id, p.dateTime, p.totalBill, p.amountPaid, s.id, s.name, s.contact, s.iban FROM purchase p, supplier s where s.id=p.supplier ORDER BY p.id' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): qry = 'select c.id, c.name, c.contact, c.address from customer c' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): qry = 'select id, username from users ORDER BY id' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): qry = 'select s.id, s.dateTime, s.totalBill, s.preparedBy, s.refund, c.name, c.contact, c.address from sales s, customer c where s.customer = c.id' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): qry = 'select i.id, SUBSTRING(i.timeStamp, 1, 11), SUBSTRING(i.timeStamp, 12), i.amount, i.amountRecieved, i.transportKey, i.transportAgency, c.id, c.name, c.contact from customer c, invoice i where i.buyerId = c.id ORDER BY i.id DESC' con = connectToDB() curs = con.cursor() curs.execute(qry) inv = [] while (1): r = curs.fetchone() if (r is not None): inv.append(r) else: return inv
def populateTable(self): #qry = 'SELECT products.id, products.name, products.codeName, products.costPrice, products.sellingPrice, products.minLevel, currentinventory.quantity, products.barcode FROM `currentinventory`, `producttype`, `products` WHERE (producttype.id = products.type) AND (products.id = currentinventory.productId)' qry = 'SELECT products.id, products.name, products.codeName, products.costPrice, products.sellingPrice, products.minLevel, currentinventory.quantity, products.barcode FROM `currentinventory`, `products` WHERE products.id = currentinventory.productId' con = connectToDB() curs = con.cursor() curs.execute(qry) prods = [] while (1): r = curs.fetchone() if (r is not None): prods.append(r) else: break return prods
def tableChange(self, event): r = event.GetRow() qry = 'UPDATE products, currentinventory SET products.name = "%s", products.codeName = "%s", products.costPrice = "%s", products.sellingPrice = "%s", products.minLevel = "%s", currentinventory.quantity = "%s", products.barcode = "%s" WHERE products.id = currentinventory.productId AND products.id = "%s"' % ( self.m_productsGrid.GetCellValue( r, 1), self.m_productsGrid.GetCellValue( r, 2), self.m_productsGrid.GetCellValue( r, 3), self.m_productsGrid.GetCellValue( r, 4), self.m_productsGrid.GetCellValue( r, 5), self.m_productsGrid.GetCellValue(r, 6), self.m_productsGrid.GetCellValue( r, 7), self.m_productsGrid.GetCellValue(r, 0)) con = connectToDB() curs = con.cursor() curs.execute(qry) con.commit()
def SaveConnString(self, event): name = self.name.GetValue() if self.assetRadio.GetValue(): val = 1 elif self.liabilityRadio.GetValue(): val = 0 qry = 'INSERT INTO headOfAccounts (description, computation) VALUES ("%s", "%s")' % ( name, val) conn = connectToDB() curs = conn.cursor() curs.execute(qry) conn.commit() self.Destroy()
def SaveConnString(self, event): username = self.name.GetValue() passwd = self.passwd.GetValue() if username != '' and passwd != '': access = str(int(self.cashSale.GetValue())) \ + str(int(self.invoice.GetValue())) \ + str(int(self.quote.GetValue())) \ + str(int(self.purchase.GetValue())) \ + str(int(self.stockLevels.GetValue())) \ + str(int(self.cashSaleInfo.GetValue())) \ + str(int(self.invoiceInfo.GetValue())) \ + str(int(self.purchaseInfo.GetValue())) \ + str(int(self.quoteInfo.GetValue())) \ + str(int(self.customerInfo.GetValue())) \ + str(int(self.supplierInfo.GetValue())) \ + str(int(self.journal.GetValue())) \ + str(int(self.accountsByFolio.GetValue())) \ + str(int(self.controlAccount.GetValue())) \ + str(int(self.incomeStatement.GetValue())) \ + str(int(self.users.GetValue())) int_access = int(access) if int_access != 0: qry = 'INSERT INTO users (username, password, access) VALUES ("%s", "%s", "%s")' % ( username, passwd, access) conn = connectToDB() curs = conn.cursor() curs.execute(qry) conn.commit() self.Destroy() else: y = wx.MessageDialog(self, "Please tick atleast one checkbox", "Notice", wx.OK) y.ShowModal() else: x = wx.MessageDialog(self, "Please enter both username and password", "Notice", wx.OK) x.ShowModal()
def searchInput(self, event): v = self.search.GetValue() if v == "": self.updateStocks() return if self.m_productsGrid.GetNumberRows() > 0: self.m_productsGrid.DeleteRows( numRows=self.m_productsGrid.GetNumberRows()) qry = 'SELECT products.id, products.name, products.codeName, products.costPrice, products.sellingPrice, products.minLevel, currentinventory.quantity, products.barcode FROM `currentinventory`, `products` WHERE products.id = currentinventory.productId AND (products.id LIKE "%' + v + '%" OR products.name LIKE "%' + v + '%" OR products.codeName LIKE "%' + v + '%" OR products.costPrice LIKE "%' + v + '%" OR products.sellingPrice LIKE "%' + v + '%" OR products.minLevel LIKE "%' + v + '%" OR currentinventory.quantity LIKE "%' + v + '%" OR products.barcode LIKE "%' + v + '%") ORDER BY products.id' con = connectToDB() curs = con.cursor() curs.execute(qry) p = [] while (1): r = curs.fetchone() if (r is not None): p.append(r) else: break lenP = len(p) self.m_productsGrid.InsertRows(numRows=lenP) # Populate Table row = 0 for x in p: col = 0 x = list(x.values()) if float(x[5]) > float(x[6]): self.m_productsGrid.SetCellBackgroundColour( row, 6, wx.Colour(255, 128, 128)) for y in x: self.m_productsGrid.SetCellValue(row, col, str(y)) col = col + 1 row = row + 1
def searchInput(self, event): v = self.search.GetValue() if v == "": self.updateInvoices() return if self.m_invoiceGrid.GetNumberRows() > 0: self.m_invoiceGrid.DeleteRows( numRows=self.m_invoiceGrid.GetNumberRows()) qry = 'select DISTINCT i.id, SUBSTRING(i.timeStamp, 1, 11), SUBSTRING(i.timeStamp, 12), i.amount, i.amountRecieved, i.transportKey, i.transportAgency, c.id, c.name, c.contact from customer c, invoice i where i.buyerId = c.id AND ( i.timestamp LIKE "%' + v + '%" OR i.amount LIKE "%' + v + '%" OR i.amountRecieved LIKE "%' + v + '%" OR i.transportKey LIKE "%' + v + '%" OR i.transportAgency LIKE "%' + v + '%" OR c.id LIKE "%' + v + '%" OR c.name LIKE "%' + v + '%" OR c.contact LIKE "%' + v + '%") ORDER BY i.id DESC' con = connectToDB() curs = con.cursor() curs.execute(qry) p = [] while (1): r = curs.fetchone() if (r is not None): p.append(r) else: break lenP = len(p) self.m_invoiceGrid.InsertRows(numRows=lenP) # Populate Table col = 0 for x in p: row = 0 x = list(x.values()) if float(x[3]) > float(x[4]): self.m_invoiceGrid.SetCellBackgroundColour( x[0], 4, wx.Colour(255, 128, 128)) for y in x: self.m_invoiceGrid.SetCellValue(col, row, str(y)) row = row + 1 col = col + 1
def searchInput(self, event): v = self.search.GetValue() if v == "": self.updatePurchases() return if self.m_purchaseGrid.GetNumberRows() > 0: self.m_purchaseGrid.DeleteRows( numRows=self.m_purchaseGrid.GetNumberRows()) qry = 'SELECT DISTINCT p.id, p.dateTime, p.totalBill, p.amountPaid, s.id, s.name, s.contact FROM purchase p, supplier s where s.id=p.supplier and ( p.id LIKE "%' + v + '%" OR p.dateTime LIKE "%' + v + '%" OR p.totalBill LIKE "%' + v + '%" OR p.amountPaid LIKE "%' + v + '%" OR s.id LIKE "%' + v + '%" OR s.name LIKE "%' + v + '%" OR s.contact LIKE "%' + v + '%") ORDER BY p.id DESC' con = connectToDB() curs = con.cursor() curs.execute(qry) p = [] while (1): r = curs.fetchone() if (r is not None): p.append(r) else: break lenP = len(p) self.m_purchaseGrid.InsertRows(numRows=lenP) # Populate Table col = 0 for x in p: row = 0 x = list(x.values()) if float(x[3]) > float(x[4]): self.m_purchaseGrid.SetCellBackgroundColour( x[0], 4, wx.Colour(255, 128, 128)) for y in x: self.m_purchaseGrid.SetCellValue(col, row, str(y)) row = row + 1 col = col + 1
def searchInput(self, event): v = self.search.GetValue() if v == "": self.updateSales() return if self.m_saleInfoGrid.GetNumberRows() > 0: self.m_saleInfoGrid.DeleteRows( numRows=self.m_saleInfoGrid.GetNumberRows()) qry = 'select DISTINCT s.id, s.dateTime, s.totalBill, s.preparedBy, s.refund, c.name, c.contact, c.address from sales s, customer c where s.customer = c.id AND (s.id LIKE "%' + v + '%" OR s.dateTime LIKE "%' + v + '%" OR s.totalBill LIKE "%' + v + '%" OR s.preparedBy LIKE "%' + v + '%" OR s.refund LIKE "%' + v + '%" OR c.name LIKE "%' + v + '%" OR c.contact LIKE "%' + v + '%" OR c.address LIKE "%' + v + '%") ORDER BY s.id' con = connectToDB() curs = con.cursor() curs.execute(qry) p = [] while (1): r = curs.fetchone() if (r is not None): p.append(r) else: break lenP = len(p) self.m_saleInfoGrid.InsertRows(numRows=lenP) # Populate Table col = 0 for x in p: row = 0 x = list(x.values()) #if float(x[3]) > float(x[4]): # self.m_productsGrid.SetCellBackgroundColour(x[0], 4, wx.Colour(255, 128, 128)) for y in x: self.m_saleInfoGrid.SetCellValue(col, row, str(y)) row = row + 1 col = col + 1
def searchInput(self, event): v = self.search.GetValue() if v == "": self.updateQuotes() return if self.m_quoteInfoGrid.GetNumberRows() > 0: self.m_quoteInfoGrid.DeleteRows( numRows=self.m_quoteInfoGrid.GetNumberRows()) qry = 'SELECT DISTINCT q.id, SUBSTRING(q.dateTime, 1, 11), SUBSTRING(q.dateTime, 12), q.totalBill, q.expiryDate, q.converted, c.name, c.contact, c.address from quotations q, customer c WHERE q.customer=c.id AND (q.id LIKE "%' + v + '%" OR q.dateTime LIKE "%' + v + '%" OR q.totalBill LIKE "%' + v + '%" OR q.expiryDate LIKE "%' + v + '%" OR q.converted LIKE "%' + v + '%" OR c.name LIKE "%' + v + '%" OR c.contact LIKE "%' + v + '%" OR c.address LIKE "%' + v + '%") ORDER BY q.id DESC' con = connectToDB() curs = con.cursor() curs.execute(qry) p = [] while (1): r = curs.fetchone() if (r is not None): p.append(r) else: break lenP = len(p) self.m_quoteInfoGrid.InsertRows(numRows=lenP) # Populate Table col = 0 for x in p: row = 0 x = list(x.values()) #if float(x[3]) > float(x[4]): # self.m_quoteInfoGrid.SetCellBackgroundColour(x[0], 4, wx.Colour(255, 128, 128)) for y in x: self.m_quoteInfoGrid.SetCellValue(col, row, str(y)) row = row + 1 col = col + 1
def searchInput(self, event): v = self.search.GetValue() if v == "": self.updateSuppliers() return if self.m_custInfoGrid.GetNumberRows() > 0: self.m_custInfoGrid.DeleteRows( numRows=self.m_custInfoGrid.GetNumberRows()) qry = 'select s.id, s.name, s.contact from supplier s where (s.id LIKE "%' + v + '%" OR s.name LIKE "%' + v + '%" OR s.contact LIKE "%' + v + '%") ORDER BY s.id' con = connectToDB() curs = con.cursor() curs.execute(qry) p = [] while (1): r = curs.fetchone() if (r is not None): p.append(r) else: break lenP = len(p) self.m_custInfoGrid.InsertRows(numRows=lenP) # Populate Table col = 0 for x in p: row = 0 x = list(x.values()) #if float(x[3]) > float(x[4]): # self.m_custInfoGrid.SetCellBackgroundColour(x[0], 4, wx.Colour(255, 128, 128)) for y in x: self.m_custInfoGrid.SetCellValue(col, row, str(y)) row = row + 1 col = col + 1