def viewprof(username): # logic for the view profile page # pull profile from the DB and return the results results = db_connection.execute("""SELECT p.doc FROM user_profile p WHERE p.doc.username = :x""", x=username) results = results.fetchone()[0] data = json.loads(results) return render_template('viewprof.html', comments=data)
def index(): # logic for the edit my profile page # pull text from input fields and rewrite JSON entry in the DB associated with that profile form = SearchForm() if form.validate_on_submit(): # creating dictionary to store text from input text fields s = {} for i in ['Profname','about','age','email','phone','loc','empid','school','gradYear','involv', 'picture', 'groupd']: if request.form[i] != "": s[i] = request.form[i] else: s[i] = '' s['inter'] = {} s['username'] = current_user.username for i in ['exploring', 'nightlife', 'outdoors', 'sports', 'videogames']: if i in request.form.getlist('interests'): s['inter'][i] = 1 else: s['inter'][i] = 0 #converting dictionary to JSON json_string = json.dumps(s) #checking if a profile with the username already exists in the db result = db_connection.execute("""SELECT p.comment_id, p.doc FROM user_profile p WHERE p.doc.username = :x""", x=current_user.username).fetchone()[0] if result: this_profile = UserProfile.get(result) this_profile.doc = json_string db.session.commit() else: db.session.add(UserProfile(json_string)) db.session.commit() return redirect(url_for('viewprof', username = current_user.username)) return render_template('index.html', form=form)
def gensearch(): # logic for the general search page # creates SQL query based on text input and searches all fields in the JSON doc # example SQL statement created will look like: # SELECT p.doc # from user_profile p # where json_textcontains(p.doc, '$', :xvar) form = genSearchForm() if form.validate_on_submit(): if request.form['searchInput'] != "": search_results = db_connection.execute("""SELECT p.doc FROM user_profile p WHERE json_textcontains(p.doc, '$', :xvar)""", xvar = request.form['searchInput']).fetchall() else: search_results = db_connection.execute("""SELECT p.doc FROM user_profile p""").fetchall() results =[] for i in range(len(search_results)): dict_string = json.loads(search_results[i][0]) results.append(dict_string) return render_template('results.html', comments=results) return render_template('generalsearch.html', form=form)
def gensearch(): # logic for the general search page # creates SQL query based on text input and searches all fields in the JSON doc # example SQL statement created will look like: # SELECT p.doc # from user_profile p # where json_textcontains(p.doc, '$', :xvar) form = genSearchForm() if form.validate_on_submit(): if request.form['searchInput'] != "": search_results = db_connection.execute( """SELECT p.doc FROM user_profile p WHERE json_textcontains(p.doc, '$', :xvar)""", xvar=request.form['searchInput']).fetchall() else: search_results = db_connection.execute( """SELECT p.doc FROM user_profile p""").fetchall() results = [] for i in range(len(search_results)): dict_string = json.loads(search_results[i][0]) results.append(dict_string) return render_template('results.html', comments=results) return render_template('generalsearch.html', form=form)
def search(): # logic for the search page # creates sql query based on input fields in the search page, executes sql query on the DB, redirects to the results page # example sql statement created will look like this: # SELECT p.doc # FROM user_profile p # WHERE lower(p.doc.empid) like :empid # AND lower(p.doc.groupd) like :groupd # AND lower(p.doc.school) like :school # AND lower(p.doc.phone) like :phone # AND lower(p.doc.involv) like :involv # AND lower(p.doc.loc) like :loc # AND lower(p.doc.about) like :about # AND lower(p.doc.age) like :age # AND lower(p.doc.gradYear) like :gradYear # AND lower(p.doc.Profname) like :Profname # AND lower(p.doc.email) like :email form = SearchForm() if form.validate_on_submit(): sql = """SELECT p.doc FROM user_profile p """ where_clause = [] # search through each input field to check if there was inputted text # if yes, create a sql statement to search the JSON field for that text d = {'Profname':'%','about':'%','age':'%','email':'%','phone':'%','loc':'%','empid':'%','school':'%','gradYear':'%','involv':'%','groupd':'%'} for i in d.keys(): if request.form[i] != "": d[i] = '%' + request.form[i].lower() + '%' where_clause.append("lower(p.doc." + i + ") like :" + i +" ") for i in request.form.getlist('interests'): where_clause.append("p.doc.inter." + i +" like 1") #append all the AND sequel clauses #add in the WHERE clause followed by all the AND clauses if len(where_clause) > 0: all_wheres = " AND ".join(where_clause) sql = sql + "WHERE " + all_wheres # execute the sql statement on the DB and return the results page showing all the matches search_results = db_connection.execute(sql, Profname = d['Profname'].lower(), about=d['about'], age=d['age'], email=d['email'], phone=d['phone'],loc=d['loc'],groupd=d['groupd'], empid=d['empid'], school=d['school'], gradYear=d['gradYear'], involv=d['involv']).fetchall() results =[] for i in range(len(search_results)): dict_string = json.loads(search_results[i][0]) results.append(dict_string) return render_template('results.html', comments=results) return render_template('search.html', form=form)
def index(): # logic for the edit my profile page # pull text from input fields and rewrite JSON entry in the DB associated with that profile form = SearchForm() if form.validate_on_submit(): # creating dictionary to store text from input text fields s = {} for i in [ 'Profname', 'about', 'age', 'email', 'phone', 'loc', 'empid', 'school', 'gradYear', 'involv', 'picture', 'groupd' ]: if request.form[i] != "": s[i] = request.form[i] else: s[i] = '' s['inter'] = {} s['username'] = current_user.username for i in [ 'exploring', 'nightlife', 'outdoors', 'sports', 'videogames' ]: if i in request.form.getlist('interests'): s['inter'][i] = 1 else: s['inter'][i] = 0 #converting dictionary to JSON json_string = json.dumps(s) #checking if a profile with the username already exists in the db result = db_connection.execute( """SELECT p.comment_id, p.doc FROM user_profile p WHERE p.doc.username = :x""", x=current_user.username).fetchone()[0] if result: this_profile = UserProfile.get(result) this_profile.doc = json_string db.session.commit() else: db.session.add(UserProfile(json_string)) db.session.commit() return redirect(url_for('viewprof', username=current_user.username)) return render_template('index.html', form=form)
def search(): # logic for the search page # creates sql query based on input fields in the search page, executes sql query on the DB, redirects to the results page # example sql statement created will look like this: # SELECT p.doc # FROM user_profile p # WHERE lower(p.doc.empid) like :empid # AND lower(p.doc.groupd) like :groupd # AND lower(p.doc.school) like :school # AND lower(p.doc.phone) like :phone # AND lower(p.doc.involv) like :involv # AND lower(p.doc.loc) like :loc # AND lower(p.doc.about) like :about # AND lower(p.doc.age) like :age # AND lower(p.doc.gradYear) like :gradYear # AND lower(p.doc.Profname) like :Profname # AND lower(p.doc.email) like :email form = SearchForm() if form.validate_on_submit(): sql = """SELECT p.doc FROM user_profile p """ where_clause = [] # search through each input field to check if there was inputted text # if yes, create a sql statement to search the JSON field for that text d = { 'Profname': '%', 'about': '%', 'age': '%', 'email': '%', 'phone': '%', 'loc': '%', 'empid': '%', 'school': '%', 'gradYear': '%', 'involv': '%', 'groupd': '%' } for i in d.keys(): if request.form[i] != "": d[i] = '%' + request.form[i].lower() + '%' where_clause.append("lower(p.doc." + i + ") like :" + i + " ") for i in request.form.getlist('interests'): where_clause.append("p.doc.inter." + i + " like 1") #append all the AND sequel clauses #add in the WHERE clause followed by all the AND clauses if len(where_clause) > 0: all_wheres = " AND ".join(where_clause) sql = sql + "WHERE " + all_wheres # execute the sql statement on the DB and return the results page showing all the matches search_results = db_connection.execute(sql, Profname=d['Profname'].lower(), about=d['about'], age=d['age'], email=d['email'], phone=d['phone'], loc=d['loc'], groupd=d['groupd'], empid=d['empid'], school=d['school'], gradYear=d['gradYear'], involv=d['involv']).fetchall() results = [] for i in range(len(search_results)): dict_string = json.loads(search_results[i][0]) results.append(dict_string) return render_template('results.html', comments=results) return render_template('search.html', form=form)