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;" )