def login_user(name_user, id_user):
try:
return utilities.query(
"SELECT * FROM Staff LEFT JOIN Manager ON Staff.Staff_ID=Manager.Staff_ID WHERE Staff.Name='"
+ name_user + "' and Staff.Staff_ID=" + id_user)
except Exception as e:
print("error logging in: ")
print(e)
return ""
def query_props(req):
#parse POST REQUEST
req = json.loads(req)
#craft query
query = "SELECT "
fields = ""
#fill in query
for key, value in req.items():
if key != "SKU":
if value == True:
fields = fields + key + ", "
fields = fields + "SKU "
query = query + fields + "FROM Products WHERE SKU='" + req["SKU"] + "'"
return utilities.query(query)
def query_vendor():
return utilities.query("SELECT * FROM Vendors")
def query_modify_info():
return utilities.query(
"SELECT q.SKU, p.Product_Name, q.Date_info, q.Staff_ID "
"FROM Modify q, Products p "
"WHERE q.SKU=p.SKU")
def query_all_purchased():
return utilities.query(
"SELECT v.V_ID FROM Vendors v WHERE NOT EXISTS (SELECT * from Products p WHERE NOT EXISTS (SELECT * FROM Order_Exports o,Export_Desc i WHERE i.SKU=p.SKU AND o.Export_ID=i.Export_ID AND v.V_ID=o.V_ID));"
)
def query_all_employee(self):
return utilities.query("SELECT * FROM Staff")
def query_vendor(self):
utilities.query("SELECT * FROM Vendors")
def query_producer():
return utilities.query("SELECT * FROM Producers")
def query_quantify_info(self, staffid):
return utilities.query(
"SELECT q.SKU, p.Product_Name FROM Quantify q, Product p where q.SKU=p.SKU and q.Staff_ID='"
+ staffid + "'")
def query_all_products_info(self):
return utilities.query("select * from Products")
def query_product_info(self, SKU):
return utilities.query("select * from Products where SKU='" + SKU +
"'")
def query_manager(self):
return utilities.query("select * from Manager NATURAL JOIN Staff")
def query_worker(self):
return utilities.query("select * from Workers NATURAL JOIN Staff")
def query_dependants(self, staffid):
return utilities.query("SELECT * from Dependants "
"WHERE Staff_ID=" + staffid)
def query_mentor(self):
return utilities.query(
"SELECT a1.Name ASMentoree, a.Staff_ID ASMentoreeID, b1.Name ASMentor, b.Staff_ID ASMentorID "
"FROM Workers a, Workers b, Staff a1, Staff b1 "
"WHERE a.Mentor_ID = b.Staff_ID and a.Staff_ID=a1.Staff_ID and b.Staff_ID=b1.Staff_ID"
)
def query_count_order_producer(pid):
return utilities.query("SELECT COUNT(*) FROM Order_Imports "
"WHERE P_ID='" + pid + "'")
def query_popular_producer():
return utilities.query("SELECT SKU, SUM(Amount) as sum "
"FROM Import_Desc GROUP BY SKU "
"ORDER BY SUM(Amount)")
def query_count_order_vendor(self, vid):
return utilities.query(
"SELECT COUNT(*) FROM Order_Exports where V_ID='" + vid + "'")
def query_user_info(self, userid):
utilities.query("select * from users where User_ID='" + userid + "'")
def query_count_order_producer(self, pid):
return utilities.query(
"SELECT COUNT(*) FROM Order_Imports where P_ID='" + pid + "'")
def query_producer(self):
utilities.query("SELECT * FROM Producers")
def query_popular_vendor(self):
return utilities.query(
"SELECT SKU, SUM(Amount) FROM Export_Desc GROUP BY SKU ORDER BY SUM(Amount)"
)
def query_popular_producer(self):
return utilities.query(
"SELECT SKU, SUM(Amount) FROM Import_Desc GROUP BY SKU ORDER BY SUM(Amount)"
)
def query_max_producer():
query = "SELECT p.P_ID,SUM(p.Revenue) as sum FROM (SELECT SUM(Total_Price) AS Revenue, P_ID FROM Order_Imports GROUP BY P_ID) p GROUP BY P_ID ORDER BY SUM(Revenue) DESC"
return utilities.query(query)
def query_all_products_info():
query = "SELECT * from Products ORDER BY LENGTH(SKU)"
# query = "SELECT * from Products ORDER BY SKU"
return utilities.query(query)
def query_distinct_producer():
return utilities.query(
"SELECT p.Date_info,SUM(p.Revenue) as sum,COUNT(p.P_ID) as num_prods FROM (SELECT SUM(Total_Price) AS Revenue, P_ID, Date_info FROM Order_Imports GROUP BY P_ID,Date_info) p GROUP BY Date_info ORDER BY SUM(Revenue) DESC;"
)
def query_product_export_all():
return utilities.query(
"select p.SKU from Products p where not exists (select * from Vendors v where not exists (select * from Order_Exports o, Export_Desc e where o.V_ID = v.V_ID and e.SKU = p.SKU and o.Export_ID = e.Export_ID));"
)
def query_imports():
return utilities.query(
"SELECT * "
"FROM Import_Desc LEFT JOIN Order_Imports on Import_Desc.Import_ID=Order_Imports.Import_ID"
)
def query_product_info(sku):
return utilities.query("SELECT * from Products " "WHERE SKU='" + sku + "'")
def query_distinct_vendor():
return utilities.query(
"SELECT p.Date_info,SUM(p.Revenue) as sum,COUNT(p.V_ID) as num_vens FROM (SELECT SUM(Total_Price) AS Revenue, V_ID, Date_info FROM Order_Exports GROUP BY V_ID,Date_info) p GROUP BY Date_info ORDER BY SUM(Revenue) DESC;"
)
