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)