def change_or_remove_areas_for_program(change, programName, areas):
prog = Program.find_by_name(programName)
if (prog == None):
raise ValueError
if (areas == []):
return True
if (change == "add"):
for i in areas:
tempArea = Area.find_by_name(i)
if (tempArea == None):
tempArea = Area(i)
tempArea.save_to_db()
prog.add_area(tempArea)
prog.save_to_db()
elif (change == "remove"):
for i in areas:
tempArea = Area.find_by_name(i)
if (tempArea == None):
raise ValueError
prog.remove_area(tempArea)
prog.save_to_db()
#This if statement checks to see if the removed Language has any more existing relationships
# If there are none, then delete the language from the database
if (Area.query.join(Programs_Areas).filter(
(Programs_Areas.c.area_id == tempArea.id)).first() == None):
db.session.delete(tempArea)
prog.save_to_db()
else:
raise ValueError
db.session.commit()
return True
def main():
#------------------Remove Actions-----------------
#Test Language: Remove Spanish from SU Buenos Aires
Program.find_by_name("SU Buenos Aires").remove_language(
Language.find_by_name("Spanish"))
db.session.commit()
#Test Loaction: Remove Lisbon, Portugal from SU European Cultural Exploration
Program.find_by_name("SU European Cultural Exploration").remove_location(
Location.find_by_name("Lisbon", "Portugal"))
db.session.commit()
#Removed the connection, but didnt remove Lisbon from the database
#Test Area: Remove Biology from SU London
Program.find_by_name("SU London").remove_area(Area.find_by_name("Biology"))
db.session.commit()
#Test Term: Remove Summer 1 from SU Amsterdam
Program.find_by_name("SU Amsterdam").remove_term(
Term.find_by_name("Summer 1"))
db.session.commit()
#------------------Modify Actions-----------------
#Test removing Description from SU European Cultural Exploration
Program.find_by_name("SU European Cultural Exploration").description = None
db.session.commit()
#Test Changing community engagement learning opportunity to False for SU Amsterdam
Program.find_by_name("SU Amsterdam").comm_eng = False
db.session.commit()
#Test removing provider
Provider.find_by_name("A1").remove_program(
Program.find_by_name("SU Amsterdam"))
db.session.commit()
#------------------Add Actions-----------------
#Add Spanish and French to SU ECE
languages = ["Spanish", "French"]
for i in languages:
if (Language.get_language_id(i) == -1):
tempLanguage = Language(i)
tempLanguage.save_to_db()
else:
tempLanguage = Language.find_by_name(i)
Program.find_by_name("SU European Cultural Exploration").add_language(
tempLanguage)
db.session.commit()
def create_new_program(providerName, programName, com, res, intern, cost,
cost_stipulations, description, url, areas, terms,
languages, locations):
#-----------------------------PROVIDER RELATIONSHIP----------------------------
# Check if the provider already exist, if it doesn't then make a new provider
if (Provider.get_provider_id(providerName) == -1):
prov = Provider(providerName)
prov.save_to_db()
else:
prov = Provider.find_by_name(providerName)
# Check if the program already exist, if it doesn't then make a new program
if (Program.get_program_id(programName) == -1):
prog = Program(programName, com, res, intern, cost, cost_stipulations,
description, url)
prog.save_to_db()
else:
prog = Program.find_by_name(programName)
#Add the program to the provider
prov.add_program(prog)
prov.save_to_db()
#-----------------------------PROGRAM RELATIONSHIPS----------------------------
# AREA, TERM, LANGUAGES, LOCATION
# AREA:
# Cyle through all area names: check to see if the area already exist in the db,
# if it doesn't, add it to the program and create the relationship.
for i in areas:
if (Area.get_area_id(i) == -1):
tempArea = Area(i)
tempArea.save_to_db()
else:
tempArea = Area.find_by_name(i)
prog.add_area(tempArea)
prog.save_to_db()
#might need save to db methods
# TERM:
# Cyle through all term names: check to see if the term already exist in the db,
# if it doesn't, add it to the program and create the relationship.
for i in terms:
if (Term.get_term_id(i) == -1):
tempTerm = Term(i)
tempTerm.save_to_db()
else:
tempTerm = Term.find_by_name(i)
prog.add_term(tempTerm)
prog.save_to_db()
#might need save to db methods
# LANGUAGES:
# Cyle through all term names: check to see if the term already exist in the db,
# if it doesn't, add it to the program and create the relationship.
for i in languages:
if (Language.get_language_id(i) == -1):
tempLanguage = Language(i)
tempLanguage.save_to_db()
else:
tempLanguage = Language.find_by_name(i)
prog.add_language(tempLanguage)
prog.save_to_db()
#might need save to db methods
# LOCATION:
# Cyle through all term names: check to see if the term already exist in the db,
# if it doesn't, add it to the program and create the relationship.
for i in locations:
if (Location.get_location_id(i[0], i[1]) == -1):
tempLocation = Location(i[0], i[1])
tempLocation.save_to_db()
else:
tempLocation = Location.find_by_name(i[0], i[1])
prog.add_location(tempLocation)
prog.save_to_db()
def results():
# the following values are taken from the get request and can be used to filter
#if null, dont filter by it
languageRequest = flask.request.values.get('lan')
locationRequestCity = flask.request.values.get('loccity')
locationRequestCountry = flask.request.values.get('loccountry')
areaRequest = flask.request.values.get('area')
termRequest = flask.request.values.get('term')
#providerRequest = flask.request.values.get('prov')str' object has no attribute '_sa_instance_state
#the following handles the presence of multiple values per param by
#splitting them into an array. all values are strings
langArray = []
locArray = []
areaArray = []
termArray = []
if languageRequest is not None:
langArray = languageRequest.split(',')
if locationRequestCity is not None:
locArray = [[locationRequestCity, locationRequestCountry]]
if areaRequest is not None:
areaArray = areaRequest.split(',')
if termRequest is not None:
termArray = termRequest.split(',')
#https://stackoverflow.com/questions/27810523/sqlalchemy-elegant-way-to-deal-with-several-optional-filters used for reference below
#here the arrays of values are taken and used to filter the database query before returning it
toQuery = Program.query
print("Past toQuery")
if languageRequest is not None:
for lang in langArray:
language = Language.find_by_name(lang)
toQuery = toQuery.join(Programs_Languages).join(Language).filter(
Programs_Langauges.c.language_id == language.id)
print(lang)
if locationRequestCity is not None:
for loc in locArray:
location = Location.find_by_name(loc[0], loc[1])
toQuery = toQuery.join(Programs_Locations).join(Location).filter(
Programs_Locations.c.location_id == location.id)
if areaRequest is not None:
for a in areaArray:
area = Area.find_by_name(a)
toQuery = toQuery.join(Programs_Areas).join(Area).filter(
Programs_Areas.c.area_id == area.id)
if termRequest is not None:
for t in termArray:
term = Term.find_by_name(t)
toQuery = toQuery.join(Programs_Terms).join(Term).filter(
Programs_Terms.c.term_id == term.id)
toQuery = toQuery.all()
# all items returned are converted to a json object and returned
json_results = [i.serialize for i in toQuery]
print(json_results)
return jsonify(json_results)
#provArray = providerRequest.split(',')
#http://127.0.0.1:5000/results?loc=Spain,Madrid&lan=Spanish
# /request = approute
#? = query
#loc=Spain,Madrid = the location is sent two or more locatons using ,. These could be grouped using (Spain Madrid)
#& is used to add another value
# here should be the methods to filter
#the filters should go through each array and use them for the filter inputs
#these should be put in a variable called filterResults
#Message for Daniel: use this webiste to nest queries.
# https://medium.com/shoprunner/multi-table-filters-in-sqlalchemy-d64e2166199f
#after results are gathered
#json_list = [i.serialize for i in filterResults]
return jsonify(langArray)
return