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
