def query_login(email, pwd):
db = Database()
if db.conn:
pwd = hashlib.md5(bytes(pwd, encoding='utf-8')).hexdigest()
cur = db.getcursor()
query = cur.execute(""" SELECT EMAIL, PASSWORD, RFID FROM `login_main` WHERE EMAIL='{}' AND PASSWORD='******' """.format(email, pwd))
if query == True:
data = cur.fetchone()
return data['RFID']
else:
return 'false'
class Support:
def __init__(self):
self.db = Database() # Just for testing
self.cur = self.db.getcursor()
def get_cases(self):
self.cur.execute("SELECT * FROM `cases_table`")
data = self.cur.fetchall()
self.db.conn.close()
return data
def add_case(self,customerid,name,product,info):
import random
caseid = int(str(random.randint(0,99)) + str(random.randint(0,99)))
self.cur.execute(""" INSERT INTO `cases_table`(caseid,customerid,name,product,info) VALUES('{}','{}','{}','{}','{}')""".format(caseid,customerid,name,product,info))
self.db.conn.commit()
def edit_case(self,caseid,customerid,name,product,info):
self.cur.execute(""" UPDATE `cases_table` SET customerid='{}',name='{}',product='{}',info='{}' WHERE caseid='{}'""".format(customerid,name,product,info,caseid))
self.db.conn.commit()
def delete_case(self,caseid):
self.cur.execute(""" DELETE FROM `cases_table` WHERE caseid='{}' """.format(caseid))
self.db.conn.commit()
def get_case(self,caseid):
self.cur.execute(""" SELECT * FROM `cases_table` WHERE caseid='{}' """.format(caseid))
data = self.cur.fetchall()
return data[0]
def auth_user(self,username=None,password=None):
db = Database()
if db.conn.open:
cur = db.getcursor()
if username is not None and password is not None:
status = cur.execute(""" SELECT * FROM `login_test` WHERE USERNAME='******' """.format(username))
if status > 0:
data = cur.fetchone()
usr = data['USERNAME']
pwd = data['PASSWORD']
if pwd == hashlib.md5(bytes(password,encoding='utf-8')).hexdigest():
return 1 # Authentication Successful
else:
return 0 # Incorrect Password
else:
return 0.2 # Invalid Username
class Dashboard:
def __init__(self):
self.db = Database()
self.cur = self.db.getcursor()
def page_views(self):
query = self.cur.execute(""" SELECT count(*) FROM page_views """)
if query == True:
data = self.cur.fetchone()
return data['count(*)']
def orders(self):
query = self.cur.execute(""" SELECT count(*) FROM product_ordering """)
if query == True:
data = self.cur.fetchone()
return data['count(*)']
def newUsers(self):
query = self.cur.execute(""" SELECT count(*) FROM login_main """)
if query == True:
data = self.cur.fetchone()
return data['count(*)']
def customer(self):
header = "<tr><th>Name<hr/></th><th>Phone<hr/></th><th>Email<hr/></th><th>Address<hr/></tr>"
html = ""
query = self.cur.execute("SELECT * FROM customer_mastertable ORDER BY CUST_ID<=6 DESC")
if query == False:
return Markup("<tr><td>Empty</td></tr>")
for data in self.cur.fetchall():
html += "<tr><td>" + data['CUST_NAME'] + "<hr/></td><td>" + str(data['CUST_PHONE']) + "<hr/></td><td>" + data['CUST_EMAIL'] + "<hr/></td><td>" + data['CUST_ADDRESS'] + "<hr/></td></tr>"
html = header+html
return Markup(html)
def salesman(self):
header = "<tr><th>Name<hr/></th><th>Phone<hr/></th><th>Email<hr/></th><th>Address<hr/></tr>"
html = ""
query = self.cur.execute("SELECT * FROM salesman_master ORDER BY SL_ID<=6 DESC")
if query == False:
return Markup("<tr><td>Empty</td></tr>")
for data in self.cur.fetchall():
html += "<tr><td>" + data['SL_NAME'] + "<hr/></td><td>" + str(data['SL_PHONE']) + "<hr/></td><td>" + data['SL_EMAIL'] + "<hr/></td><td>" + data['SL_ADDRESS'] + "<hr/></td></tr>"
html = header+html
return Markup(html)
def salesperformance(self):
header = "<tr><th>Sales ID<hr/></th><th>Product ID<hr/></th><th>Month<hr/></th><th>Year<hr/></th><th>Sales Quality<hr/></th></tr>"
html = ""
query = self.cur.execute("SELECT * FROM salesman_performance ORDER BY SL_ID<=6 DESC")
if query == False:
return Markup("<tr><td>Empty</td></tr>")
for data in self.cur.fetchall():
html += "<tr><td>" + data['SL_ID'] + "<hr/></td><td>" + str(data['PDT_ID']) + "<hr/></td><td>" + data['MONTH'] + "<hr/></td><td>" + data['YEAR'] + "<hr/></td><td>" + data['SALES_QUANTITY'] + "<hr/></td></tr>"
html = header+html
return Markup(html)
def __init__(self):
self.db = Database()
self.cur = self.db.getcursor()
self.cur1 = self.db.getcursor()
self.cur2 = self.db.getcursor()
class Marketing:
def __init__(self):
self.db = Database()
self.cur = self.db.getcursor()
self.cur1 = self.db.getcursor()
self.cur2 = self.db.getcursor()
def result(self, GET):
if GET == "sales":
return Marketing().sales()
elif GET == "discount":
return Marketing().discount()
elif GET == "pricing":
return Marketing().pricing()
def sales(self):
pid_i = ""
stock = ""
reorder = ""
update = ""
customer = ""
product = ""
sales_man = ""
sales_man_email = ""
sales_man_phone = ""
sales_man_territory = ""
query = self.cur.execute(" SELECT * FROM inventory_table ORDER BY ID<=10 DESC ")
query_customer = self.cur1.execute(" SELECT * FROM cases_table ")
query_salesman = self.cur2.execute(" SELECT * FROM salesman_master ORDER BY SL_ID<=10 DESC ")
if query == True:
return False
for data in self.cur.fetchall():
self.sales_c = data
pid_i += '"'+ data['PDT_ID'] + '",'
stock += '"'+ data['STOCK'] +'",'
reorder += '"'+ data['REORDER_LEVEL'] +'",'
update += '"' + data['LAST_UPDATE'] +'",'
for data in self.cur1.fetchall():
self.customer_c = data
customer += '"'+ data['name'] +'",'
product += '"'+ data['product'] +'",'
for data in self.cur2.fetchall():
self.salesman_c = data
sales_man += '"'+ data['SL_NAME'] +'",'
sales_man_email += '"'+ data['SL_EMAIL'] + '",'
sales_man_phone += '"'+ data['SL_PHONE'] + '",'
sales_man_territory += '"'+ data['TERRITORY'] + '",'
sales_man = sales_man[:-1]
sales_man_email = sales_man_email[:-1]
sales_man_phone = sales_man_phone[:-1]
sales_man_territory = sales_man_territory[:-1]
customer = customer[:-1]
pid_i = pid_i[:-1]
product = product[:-1]
stock = stock[:-1]
reorder = reorder[:-1]
update = update[:-1]
data = '{"productNum_id":["Product ID",' + pid_i + '],"productNum":["Stock",' + stock + '],"reorder":["Orders",' + reorder + '],"update":["Date",' + update + '],"customer":["Name",' + customer + '], "productName":["Product Name",' + product + '],"salesman":["Salesman",' + sales_man + '], "salesman_email":["Email",' + sales_man_email + '], "salesman_phone":["Phone",' + sales_man_phone + '],"salesman_territory":["Territory",' + sales_man_territory + ']}'
return data
def discount(self):
pid = ""
purchase = ""
max_d = ""
min_d = ""
query = self.cur.execute(" SELECT * FROM discount_allowed ORDER BY ID<=10 DESC ")
if query == True:
return False
for data in self.cur.fetchall():
self.discount_c = data
pid += '"' + str(data['PDT_ID']) + '",'
purchase += '"' + str(data['PURCHASE_PRICE']) +'",'
max_d += '"' + str(data['MAX_DISCOUNT']) +'",'
min_d += '"' + str(data['MIN_DISCOUNT']) +'",'
pid = pid[:-1]
min_d = min_d[:-1]
purchase = purchase[:-1]
max_d = max_d[:-1]
data = '{"pid":["Product ID",' + pid + '],"purchase":["Purchase",' + purchase + '],"maxd":["Max. Price",' + max_d + '],"mind":["Min. Price",' + min_d + ']}'
return data
def pricing(self):
id = ""
market = ""
mrp = ""
query = self.cur.execute(" SELECT * FROM discount_allowed ORDER BY ID<=10 DESC ")
if query == True:
return False
for data in self.cur.fetchall():
self.pricing_c = data
id += '"'+ str(data['PDT_ID']) +'",'
market += '"'+ str(data['PURCHASE_PRICE']) +'",'
mrp += '"' + str(data['PDT_MRP']) +'",'
id = id[:-1]
market = market[:-1]
mrp = mrp[:-1]
data = '{"product":["Product ID",' + id + '],"market":["Market Price",' + market + '],"mrp":["Max. Retail Price",' + mrp + ']}'
return data
def __init__(self):
self.db = Database() # Just for testing
self.cur = self.db.getcursor()