def set_sc_not_free(sc_sn): # updates is_free attr of a SC once it's assigned to a product query = """UPDATE SpecialComponents SET sc_is_free=0 WHERE sc_sn =""" + str( sc_sn) + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query)
def get_db_work_orders(): # Load SQL query for work order data # select all columns except employee password from employee table, and site city name from site table query = """SELECT WorkOrders.wo_id, WorkOrders.wo_open_date, WorkOrders.wo_close_date, WorkOrders.wo_status, WorkOrders.wo_reference_number, CONCAT(Employees.employee_first_name, ' ', Employees.employee_last_name) as wo_employee_full_name FROM WorkOrders INNER JOIN Employees ON Employees.employee_id=WorkOrders.wo_employee_id;""" #TODO: also get the work order details (products involved) db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) work_order_results = cursor.fetchall() # Check if the query was successful: if it returned content we are good. If not, use the dummy dataset instead. if len(work_order_results) == 0: return {} return work_order_results
def get_a_work_order(workorder_id): # returns a sinlge worker information query = """SELECT WorkOrders.wo_id, WorkOrders.wo_open_date, WorkOrders.wo_close_date, WorkOrders.wo_status, WorkOrders.wo_reference_number, WorkOrders.wo_employee_id, CONCAT(Employees.employee_first_name, ' ', Employees.employee_last_name) as wo_employee_full_name FROM WorkOrders INNER JOIN Employees ON Employees.employee_id=WorkOrders.wo_employee_id where WorkOrders.wo_id=""" + workorder_id + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) work_order_result = cursor.fetchall() # Check if the query was successful: if it returned content we are good. If not, use the dummy dataset instead. if len(work_order_result) == 0: return {} return work_order_result[0]
def get_db_locations(): # Load SQL query for location data # select all columns from location table and site city name from site table # do not select sites or locations where query = """SELECT Locations.location_id, Locations.location_room_number, Locations.location_shelf_number, Locations.location_site_id, Sites.site_address_city as location_site_name FROM Locations INNER JOIN Sites ON Locations.location_site_id=Sites.site_id WHERE Locations.location_id <> 1 OR Sites.site_id <> 1 ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) location_results = cursor.fetchall() # Check if the query was successful: if it returned content we are good. If not, use the dummy dataset instead. if len(location_results) == 0: location_results = data.get_loc() return location_results
def filter(filter_query_to_run, data_to_filter): """ Since all filter queries will share the same steps, this is just a validation wrapper that handles whether an filterion was successful or not. """ # Attempt to filter. If successful, return True try: # Connect to the database. If we don't do this each time, MySQL Will Go Away db_connection = db.connect_to_database() # Execute the provided query using the provided data cursor = db.execute_query(db_connection=db_connection, query=filter_query_to_run, query_params=data_to_filter) result = cursor.fetchall() return result # If unsuccessful, print the error to the server log and return False except Exception as e: print( f'An error occurred when attempting to filter into CIMDB: {str(e)}' ) return None
def get_newest_regular_component_part_number(): # returns the most recently added regular component part number from the regular components entity query = """SELECT rc_pn FROM RegularComponents WHERE rc_pn=(SELECT max(rc_pn) FROM RegularComponents);""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) regular_component_pn_result = cursor.fetchall() return regular_component_pn_result[0]['rc_pn']
def query_for_location(provided_site_id): query = "SELECT location_id FROM Locations WHERE Locations.location_site_id = '%s' AND Locations.location_room_number = 1 LIMIT 1" db_connection = db.connect_to_database() params = (provided_site_id, ) cursor = db.execute_query(db_connection=db_connection, query=query, query_params=params) return cursor.fetchall()
def set_workorder_status(wo_id, wo_status): # updates status of a workorder. used for assembly and QC and shipping approval query = """UPDATE WorkOrders SET wo_status= """ + wo_status + """ WHERE wo_id =""" + wo_id + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query)
def set_product_location(product_sn, product_location_id): # updates product_location_id of a product. used for assembly and QC approval query = """UPDATE Products SET product_location_id= """ + product_location_id + """ WHERE product_sn =""" + product_sn + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query)
def set_product_qc_date(product_sn, product_qc_date): # updates product_qc_date of a product. used for QC approval query = """UPDATE Products SET product_qc_date= """ + product_qc_date + """ WHERE product_sn =""" + product_sn + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query)
def set_sc_sn_of_a_product(sc_sn, product_sn): # updates sc_sn of a product query = """UPDATE Products SET product_sc_sn= """ + sc_sn + """ WHERE product_sn =""" + product_sn + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query)
def set_rc_quantity_in_a_location(rc_pn, sc_location_id, quantity): # updates quantity of a location query = """UPDATE LocationsRegularComps SET lrc_quantity= """ + quantity + """ WHERE lrc_location_id =""" + sc_location_id + """ AND lrc_rc_pn= """ + rc_pn + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query)
def set_sc_location(sc_sn, sc_location_id): # updates location of a SC query = """UPDATE SpecialComponents SET sc_location_id= """ + sc_location_id + """ WHERE sc_sn =""" + str( sc_sn) + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query)
def get_site_id(city_name): # provided a site city name, return the site ID instead query = """SELECT site_id from Sites WHERE site_city_name == %s""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query, query_params=(city_name)) site_id = cursor.fetchall() return site_id
def get_db_products_in_a_workorder(wo_id): # returns the product_sn of all products in a work_order query = """select wop_product_sn from WorkOrderProducts where wop_wo_id = """ + wo_id + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) products_in_a_workorder_results = cursor.fetchall() return products_in_a_workorder_results
def get_wo_of_a_product(product_sn): # returns the product_sn of all products in a work_order query = """select wop_wo_id from WorkOrderProducts where wop_product_sn = """ + product_sn + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) wo_of_a_product_results = cursor.fetchall() return wo_of_a_product_results[0]
def get_db_regular_component_desc(rc_pn): # returns a regular compoenent desc of a given rc_pn query = """select RegularComponents.rc_pn_desc from RegularComponents where RegularComponents.rc_pn=""" + str(rc_pn) + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) regular_component_desc_result = cursor.fetchall() return regular_component_desc_result
def get_db_regular_component_pn(rc_pn_desc): # returns a regular compoenent part number of a given rc_pn_desc query = """select RegularComponents.rc_pn from RegularComponents where RegularComponents.rc_pn_desc='""" + rc_pn_desc + """';""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) regular_component_pn_result = cursor.fetchall() return regular_component_pn_result
def get_db_get_employee_id(employee_first_name, employee_last_name): # returns employee_id of the given first and last name # TODO: add another argument in the case there were employees with simialr names query = """select Employees.employee_id from Employees where Employees.employee_first_name='""" + employee_first_name + """' and Employees.employee_last_name='""" + employee_last_name + """' ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) employee_id_result = cursor.fetchall() return employee_id_result[0]
def get_db_sites(): # Load SQL query for site data (except for 'customer' site ie shipped products/work orders) query = """SELECT * FROM Sites WHERE site_id <> 1;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) site_results = cursor.fetchall() # Check if the query was successful: if it returned content we are good. If not, use the dummy dataset instead. if len(site_results) == 0: site_results = data.get_sites() return site_results
def get_rc_quantity_in_a_location(rc_pn, location_id): # returns quantity of a rc in a location query = """select lrc_quantity from LocationsRegularComps where lrc_rc_pn = """ + rc_pn + """ and lrc_location_id= """ + location_id + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) rc_qunatity_in_a_location_results = cursor.fetchall() if len(rc_qunatity_in_a_location_results) == 0: return -1 return rc_qunatity_in_a_location_results[0]['lrc_quantity']
def get_is_free(sc_sn): # returns True if a SC is free otherwise flase query = """select sc_is_free from SpecialComponents where sc_sn=""" + str(sc_sn) + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) is_free_result = cursor.fetchall() # print(f'is_free_result is: {is_free_result[0]["sc_is_free"]}') if is_free_result[0]["sc_is_free"] == 1: return True else: return False
def get_db_workorder_details(workorder_id): # Load SQL query for work order details query = """select * from Products inner join WorkOrderProducts on WorkOrderProducts.wop_product_sn=Products.product_sn where WorkOrderProducts.wop_wo_id=""" + workorder_id + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) workorder_details_result = cursor.fetchall() # Check if the query was successful: if it returned content we are good. If not, use the dummy dataset instead. if len(workorder_details_result) == 0: return {} return workorder_details_result
def get_free_sc_sn(sc_pn, sc_location_id): # returns SN of the free special compoenents of a family query = """select sc_sn from SpecialComponents where sc_is_free=1 AND sc_pn='""" + sc_pn + """' AND sc_location_id='""" + sc_location_id + """' ORDER BY sc_sn DESC;""" print(f'querry is {query}') db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) free_sc_sn_results = cursor.fetchall() # Check if the query was successful: if it returned content we are good. If not, use the dummy dataset instead. if len(free_sc_sn_results) == 0: return -1 return free_sc_sn_results
def delete(delete_query_to_run): """ Since all deletion queries will share the same steps, this is just a validation wrapper that handles whether a delete was successful or not. """ # Attempt to delete. If successful, return True try: db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=delete_query_to_run) return True # If unsuccessful, print the error to the server log and return False except Exception as e: print(f'An error occurred when attempting to delete from CIMDB: {str(e)}') return False
def get_location_id(location_site_id, location_room_number, location_shelf_number): # returns locations ID of a given site, room and shelf number query = """select location_id from Locations where location_room_number= """ + location_room_number + """ and location_shelf_number= """ + location_shelf_number + """ and location_site_id=""" + location_site_id + """ ;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) location_id_results = cursor.fetchall() if len(location_id_results) == 0: return -1 return location_id_results[0]['location_id']
def get_db_special_components(): # Load SQL query for regular component data query = """SELECT SpecialComponents.sc_sn AS sc_sn, SpecialComponents.sc_pn AS sc_pn, SpecialComponents.sc_is_free AS sc_free, SpecialComponents.sc_product_sn AS sc_product_sn, SpecialComponents.sc_location_id AS sc_loc_id, Locations.location_room_number AS sc_room, Locations.location_shelf_number AS sc_shelf, Sites.site_address_city AS sc_site_city FROM SpecialComponents INNER JOIN Locations ON Locations.location_id=SpecialComponents.sc_location_id INNER JOIN Sites ON Locations.location_site_id=Sites.site_id """ db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) spec_comp_results = cursor.fetchall() return spec_comp_results
def regular_component_locations(regular_component_id): # provided a regular component ID, return the locations of all regular components with that ID query = """ SELECT Locations.location_site_id AS location_site_id , Locations.location_id AS location_id , Locations.location_room_number AS RoomNumber , Locations.location_shelf_number AS ShelfNumber , LocationsRegularComps.lrc_quantity AS Quantity, Sites.site_address_city AS SiteCity FROM Locations INNER JOIN LocationsRegularComps ON Locations.location_id=LocationsRegularComps.lrc_location_id WHERE LocationsRegularComps.lrc_rc_pn == %s;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query, query_params=(regular_component_id)) reg_comp_location_results = cursor.fetchall() return reg_comp_location_results
def get_product_sn(sc_sn): # returns SN of the product using its SC SN, if SC is not used, returns False # if get_is_free(sc_sn) is True: # return False query = """select product_sn from Products where product_sc_sn=""" + str(sc_sn) + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) get_product_sn_result = cursor.fetchall() # print(f'get_product_sn_result is: {get_product_sn_result[0]["product_sn"]}') if len(get_product_sn_result) == 0: return -1 else: return get_product_sn_result[0]["product_sn"]
def get_db_product_components(product_sn): # returns products compoenent based on it's SN. returns an empty dictionary if the sn does not exists if len(get_db_product_details(product_sn)) == 0: print("product is not found") product_compoenent_result = {} else: query = """select ProductsRegularComps.prc_rc_pn , ProductsRegularComps.prc_quantity_needed, RegularComponents.rc_pn_desc , RegularComponents.rc_category FROM ProductsRegularComps inner join RegularComponents on ProductsRegularComps.prc_rc_pn = RegularComponents.rc_pn where ProductsRegularComps.prc_product_sn=""" + str(product_sn) + """;""" db_connection = db.connect_to_database() cursor = db.execute_query(db_connection=db_connection, query=query) product_compoenent_result = cursor.fetchall() return list(product_compoenent_result)