Beispiel #1
0
def create_location_image(location_name):
    cur.execute(
        "SELECT * FROM Photo WHERE LocationName='{}'".format(location_name))
    photo = cur.fetchone()
    if photo:
        return

    key = 'AIzaSyCoIJcakxVen5qGdu_PsV_ajdl33qwGskI'
    rv = requests.get(
        'https://maps.googleapis.com/maps/api/place/autocomplete/json?key={}&input={}&type=geocode&'
        .format(key, location_name))
    data = rv.json()
    if data['status'] != 'OK':
        return

    location = data['predictions'][0]
    place_id = location['place_id']
    rv = requests.get(
        'https://maps.googleapis.com/maps/api/place/details/json?key={}&placeid={}'
        .format(key, place_id))
    data = rv.json()['result']
    if 'photos' not in data:
        url = None
    else:
        photo_ref = data['photos'][0]['photo_reference']
        url = 'https://maps.googleapis.com/maps/api/place/photo?maxwidth=400&photoreference={}&key={}'.format(
            photo_ref, key)

    cur.execute("INSERT INTO Photo VALUES ('{}', '{}')".format(
        url, location_name))
    conn.commit()
Beispiel #2
0
def create_trip(keyword, location_name, user, lpnum):
    '''
    Create trip from location's go next
    '''
    go_nexts = get_location_go_nexts(location_name)
    if not go_nexts:
        raise ValueError('Location {} does not exist'.format(location_name))

    create_trip_location(keyword, location_name, lpnum)
    create_location_image(location_name)

    # Get info and create location for all go nexts
    num_go_nexts = min(len(go_nexts), 5)
    for location in go_nexts[:num_go_nexts]:
        name = location[0]
        coords = location[1]
        if name:
            create_trip_location(keyword, name, lpnum)
            create_location_image(name)

    # Create trip
    cur.execute('''
                INSERT INTO Trip
                VALUES ('{}', '{}', {})
                '''.format(keyword, location_name, lpnum))
    conn.commit()
Beispiel #3
0
def recommend_trip(username):
    query = '''	
                      	SELECT u.TripKeyword, MAX(u.LPNum)
	                FROM TripUser u
        	        WHERE u.Assessment = 1 AND u.Username = '******'
			GROUP BY u.TripKeyword
			
			
			'''.format(username)

    cur.execute(query)
    rv = cur.fetchall()
    recommended = [[], []]

    if not rv:
        return []
    else:
        for trip_info in rv:
            keyword = trip_info[0]
            lpnum = trip_info[1]
            recommended[0].append(
                "http://fa16-cs411-47.cs.illinois.edu/{}/search/{}/{}".format(
                    username, keyword, lpnum + 1))
            recommended[1].append(keyword + " {}".format(lpnum + 1))

    return recommended
Beispiel #4
0
def delete_past_trip(username, keyword, lpnum):
    cur.execute('''
                DELETE FROM TripUser
                WHERE Username='******' AND TripKeyword='{}' AND LPNum={}
                '''.format(username, keyword, lpnum))
    conn.commit()
    profile_url = "{}/{}".format(url, username)
    return redirect(profile_url)
Beispiel #5
0
def example_query():
    cur.execute(
        "SELECT page_title FROM page WHERE page_title LIKE '%Republic%'")
    query_name = "SELECT page_title FROM page WHERE page_title LIKE '%Republic%'"
    query = str(cur.fetchall())
    return render_template('example_query.html',
                           query_name=query_name,
                           query=query)
Beispiel #6
0
def create_user(username, password, name):
    user = get_user_by_username(username)
    if user:
        return False
    else:
        cur.execute("INSERT INTO User VALUES ('{}', '{}', '{}', {})".format(
            username, password, name, True))
        conn.commit()
        return True
Beispiel #7
0
def get_location_go_nexts(location_name):
    # Get all associated go nexts with location
    db_location_name = location_name.replace(" ", "_")
    cur.execute('''
                SELECT NextName, NextCoords
                FROM LocationGoNext
                WHERE SourceName LIKE '{}%'
                '''.format(db_location_name))
    rv = cur.fetchall()
    return rv
Beispiel #8
0
def get_trip_locations(keyword, lpnum):
    cur.execute('''
                SELECT LocationName 
                FROM TripLocation 
                WHERE TripKeyword='{}' AND LPNum={}
                '''.format(keyword, lpnum))

    trip_locations = cur.fetchall()
    locations = [get_location_by_name(loc[0]) for loc in trip_locations]
    return locations
Beispiel #9
0
def like_trip(username, keyword, lpnum, like):
    like = like == 'True'
    cur.execute('''
                UPDATE TripUser
                SET Assessment={}
                WHERE TripKeyword='{}' AND Username='******' AND LPNum={}
                '''.format(like, keyword, username, lpnum))

    conn.commit()
    return redirect("{}/{}/search/{}/{}".format(url, username, keyword, lpnum))
Beispiel #10
0
def get_location_by_name(name):
    cur.execute('''
                SELECT l.Description, l.Eat, l.See, l.Do, l.Name, p.URL
                FROM Location l, Photo p
                WHERE l.Name='{}' AND p.LocationName = '{}'
                '''.format(name, name))
    location = cur.fetchone()

    if location:
        return location_to_dict(location)
    return None
Beispiel #11
0
def past_trips(username):
    cur.execute('''
			SELECT TripKeyword, LPNum
			FROM TripUser
			WHERE Username = '******'
			'''.format(username))

    rv = cur.fetchall()
    past = [[], []]

    if not rv:
        return []
    else:
        for trip_info in rv:
            keyword = trip_info[0]
            lpnum = trip_info[1]
            past[0].append(
                "http://fa16-cs411-47.cs.illinois.edu/{}/search/{}/{}".format(
                    username, keyword, lpnum))
            past[1].append(keyword + " {}".format(lpnum))

    return past
Beispiel #12
0
def create_trip_user(keyword, username, lpnum):
    trip_user = get_trip_user(keyword, username, lpnum)
    if not trip_user:
        cur.execute("INSERT INTO TripUser VALUES ('{}','{}', {}, 0)".format(
            keyword, username, lpnum))
        conn.commit()
Beispiel #13
0
def get_trip_by_keyword(keyword, lpnum):
    cur.execute("SELECT * FROM Trip WHERE Keyword='{}' AND LPNum='{}'".format(
        keyword, lpnum))
    trip = cur.fetchone()
    return trip
Beispiel #14
0
def get_location_by_coords(coords):
    cur.execute("SELECT * FROM Location WHERE Coordinates='{}'".format(coords))
    location = cur.fetchone()
    return location
Beispiel #15
0
def create_trip_location(keyword, location_name, lpnum):
    cur.execute("INSERT INTO TripLocation VALUES ('{}','{}', {})".format(
        keyword, location_name, lpnum))
    conn.commit()
Beispiel #16
0
def change_user_logged_in(username, logged_in):
    cur.execute("UPDATE User SET LoggedIn={} WHERE Username='******'".format(
        logged_in, username))
    conn.commit()
Beispiel #17
0
def get_user_by_username(username):
    cur.execute("SELECT * FROM `User` WHERE Username='******'".format(username))
    return cur.fetchone()
Beispiel #18
0
from __init__ import cur, conn

cur.execute("DELETE FROM Photo")
cur.execute("DELETE FROM Trip")
cur.execute("DELETE FROM TripUser")
cur.execute("DELETE FROM TripLocation")
conn.commit()
Beispiel #19
0
def get_trip_user(keyword, username, lpnum):
    cur.execute(
        "SELECT * FROM TripUser WHERE TripKeyword='{}' AND Username='******' AND LPNum={}"
        .format(keyword, username, lpnum))
    trip_user = cur.fetchone()
    return trip_user