Parts Implemented by Berkan Dinar

1 Database Design

1.1 Database Tables

Entertainment, Location, Transportation, Cities and Countries tables was generated by Berkan Dinar

1.1.1 Entertainment Table

Name Type Not Null Primary K. Foreign K.
id SERIAL 1 1 0
name VARCHAR(255) 1 0 0
score INTERGER 0 0 0
votes INTERGER 0 0 0
info TEXT 0 0 0
photo VARCHAR(255) 0 0 0
activity INTEGER 0 0 1
place INTEGER 0 0 1

Entertainment table was generated for Entertainment page. It has two foreign key. “activity” entity referenced from “id” of Activities table that is generated by Mehmet Tankut Özen. “place” entity referenced from “id” of Location table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
    CREATE TABLE Entertainment(
        ID SERIAL PRIMARY KEY,
        NAME VARCHAR(255) NOT NULL,
        SCORE SCORES DEFAULT 0,
        VOTES INTEGER DEFAULT 0,
        INFO TEXT,
        PHOTO VARCHAR(255) DEFAULT 'https://cdn3.iconfinder.com/data/icons/glypho-movie-and-video/64/theater-masks-512.png',
        ACTIVITY INTEGER REFERENCES Activities (ID) ON DELETE CASCADE,
        PLACE INTEGER REFERENCES Location (ID) ON DELETE CASCADE
        )

1.1.2 Location Table

Name Type Not Null Primary K. Foreign K.
id SERIAL 1 1 0
name VARCHAR(255) 1 0 0
info TEXT 0 0 0
photo VARCHAR(255) 0 0 0
city INTEGER 0 0 1
country INTEGER 0 0 1

Location table is an internal table that was generated for giving foreign keys to other tables like Entertainment and Culture. It has two foreign key. “city” entity referenced from “id” of Cities table. “country” entity referenced from “id” of Country table.

1
2
3
4
5
6
7
8
        CREATE TABLE Location (
            ID SERIAL PRIMARY KEY,
            NAME VARCHAR(255) NOT NULL,
            INFO TEXT,
            PHOTO VARCHAR(255) DEFAULT 'https://d30y9cdsu7xlg0.cloudfront.net/png/1832-200.png',
            CITY INTEGER REFERENCES Cities (ID) ON DELETE CASCADE,
            COUNTRY INTEGER REFERENCES Countries (ID) ON DELETE CASCADE
            )

1.1.3 Transportation Table

Name Type Not Null Primary K. Foreign K.
id SERIAL 1 1 0
name VARCHAR(255) 1 0 0
info TEXT 0 0 0
photo VARCHAR(255) 0 0 0
place INTEGER 0 0 1

Transportation table was generated for Transportation Table. “place” entity referenced from “id” of Location table.

1
2
3
4
5
6
7
        CREATE TABLE Transportation(
            ID SERIAL PRIMARY KEY,
            NAME VARCHAR(255) NOT NULL,
            INFO TEXT,
            PHOTO VARCHAR(255) DEFAULT 'https://cdn3.iconfinder.com/data/icons/glypho-movie-and-video/64/theater-masks-512.png',
            PLACE INTEGER REFERENCES Location (ID) ON DELETE CASCADE
            )

1.1.4 Cities Table

Name Type Not Null Primary K. Foreign K.
id SERIAL 1 1 0
name VARCHAR(255) 1 0 0
info TEXT 0 0 0
photo VARCHAR(255) 0 0 0
country INTEGER 0 0 1

Cities table is an internal table that was generated for giving foreign keys to other tables. It gives foreign keys to Location table and Culture Table, which is generated by Mehmet Tankut Özen. Its “country” entity referenced from “id” of Countries table.

1
2
3
4
5
6
7
     CREATE TABLE Cities (
        ID SERIAL PRIMARY KEY,
        NAME VARCHAR(255) NOT NULL,
        INFO TEXT,
        PHOTO VARCHAR(255),
        COUNTRY INTEGER REFERENCES Countries (ID) ON DELETE CASCADE
        )

1.1.5 Countries Table

Name Type Not Null Primary K. Foreign K.
id SERIAL 1 1 0
name VARCHAR(255) 1 0 0
info TEXT 0 0 0
photo VARCHAR(255) 0 0 0

Countries table is an internal table that was generated for giving foreign keys to other tables. It gives foreign keys to Location table and Cities Table. It has no foreign keys.

1
2
3
4
5
6
       CREATE TABLE Countries (
            ID SERIAL PRIMARY KEY,
            NAME VARCHAR(255) NOT NULL,
            INFO TEXT,
            PHOTO VARCHAR(255)
            )

2 Code

2.1 Python(Flask) Files

2.1.1 Entertainment.py

Main Page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
@app.route('/entertainment')
def entertainment_page():
#if g.user:
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        query = """SELECT Entertainment.ID, Entertainment.NAME, Entertainment.SCORE,
                    Entertainment.VOTES, Entertainment.INFO, Entertainment.PHOTO,
                    Entertainment.ACTIVITY, Entertainment.PLACE, Activities.NAME, Location.NAME FROM Entertainment
                    LEFT OUTER JOIN Location
                    ON Entertainment.PLACE=Location.ID
                    LEFT OUTER JOIN Activities
                    ON Entertainment.ACTIVITY=Activities.ID"""
        cursor.execute(query)
        entertainment_data = json.dumps(cursor.fetchall())
        entertainment = json.loads(entertainment_data)

        for place in entertainment:
            place[2] = "{:2.2f}".format(place[2])

        query = """SELECT ID,NAME FROM Activities"""
        cursor.execute(query)
        activity_data = json.dumps(cursor.fetchall())
        activities = json.loads(activity_data)

        query = """SELECT ID,NAME FROM Location"""
        cursor.execute(query)
        location_data = json.dumps(cursor.fetchall())
        location = json.loads(location_data)

now = datetime.datetime.now()
if g.user:
    if(g.user == "admin"):
        usernum = 0
    else:
        usernum = 1
else:
    usernum = 2
return render_template('entertainment.html', current_time=now.ctime(), entertainment=entertainment, activities=activities, location=location, usernum=usernum)
#return redirect(url_for('login_page'))

Details Page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@app.route('/entertainment/<int:id>')
def entertainment_details(id):
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        statement = """SELECT Entertainment.ID, Entertainment.NAME, Entertainment.SCORE,
                    Entertainment.VOTES, Entertainment.INFO, Entertainment.PHOTO,
                    Entertainment.ACTIVITY, Entertainment.PLACE, Activities.NAME, Location.NAME FROM Entertainment
                    LEFT OUTER JOIN Location
                    ON Entertainment.PLACE=Location.ID
                    LEFT OUTER JOIN Activities
                    ON Entertainment.ACTIVITY=Activities.ID WHERE (Entertainment.ID = %s)"""
        cursor.execute(statement, (id,))
        entertainment_data = json.dumps(cursor.fetchall())
        entertainment = json.loads(entertainment_data)

        query = """SELECT ID,NAME FROM Activities"""
        cursor.execute(query)
        activities_data = json.dumps(cursor.fetchall())
        activities = json.loads(activities_data)

        query = """SELECT ID,NAME FROM Location"""
        cursor.execute(query)
        location_data = json.dumps(cursor.fetchall())
        location = json.loads(location_data)

return render_template('entertainment_details.html', entertainment=entertainment, activities=activities, location=location)

Insert

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@app.route('/entertainment/insert', methods=["POST"])
def entertainment_insert():
name = request.form['entertainment_place_name']
score = request.form['entertainment_place_score']
votes = request.form['entertainment_place_votes']
info = request.form['entertainment_place_info']
photo = request.form['entertainment_place_photo']
activity = request.form['entertainment_activity']
place = request.form['entertainment_place']
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        if name and score and votes and place and activity:
            query = """SELECT * FROM Location WHERE (ID = %s)"""
            cursor.execute(query, (place,))
            exists1_data = json.dumps(cursor.fetchall())
            exists1 = json.loads(exists1_data)
            query = """SELECT * FROM Activities WHERE (ID = %s)"""
            cursor.execute(query, (activity,))
            exists2_data = json.dumps(cursor.fetchall())
            exists2 = json.loads(exists2_data)
            if(exists1 and exists2):
                if photo:
                    statement = """INSERT INTO Entertainment (NAME, SCORE, VOTES, INFO, PHOTO, ACTIVITY, PLACE)
                        VALUES (%s, %s, %s, %s, %s, %s, %s)"""
                    cursor.execute(statement, (name,score,votes,info,photo,activity,place))
                else:
                    statement = """INSERT INTO Entertainment (NAME, SCORE, VOTES, INFO, ACTIVITY, PLACE)
                        VALUES (%s, %s, %s, %s, %s, %s)"""
                    cursor.execute(statement, (name,score,votes,info,activity,place))
return redirect(url_for('entertainment_page'))

Delete

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
@app.route('/entertainment/delete', methods=["POST"])
def entertainment_delete():
id = request.form["select"]
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        statement = """DELETE FROM Entertainment
                    WHERE (ID = %s)"""
        cursor.execute(statement, (id))

return redirect(url_for('entertainment_page'))

Update

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@app.route('/entertainment/update', methods=["POST"])
def entertainment_update():
id = request.form['entertainment_index']
name = request.form['entertainment_update_name']
photo = request.form["entertainment_update_photo"]
info = request.form["entertainment_update_info"]
activity = request.form['entertainment_update_activity']
place = request.form['entertainment_update_place']
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        if name:
            statement = """UPDATE Entertainment
                        SET (NAME) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (name,id))
        if info:
            statement = """UPDATE Entertainment
                        SET (INFO) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (info,id))
        if photo:
            statement = """UPDATE Entertainment
                        SET (PHOTO) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (photo,id))
        if activity:
            statement = """UPDATE Entertainment
                        SET (ACTIVITY) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (activity,id))
        if place:
            statement = """UPDATE Entertainment
                        SET (PLACE) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (place,id))

return redirect(url_for('entertainment_page'))

Delete All

1
2
3
4
5
6
7
8
@app.route('/entertainment/delete_all')
def entertainment_delete_all():
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        query = """DELETE FROM Entertainment"""
        cursor.execute(query)

return redirect(url_for('entertainment_page'))

Vote

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
@app.route('/entertainment/vote', methods=['POST'])
def entertainment_voting():
vote = request.form["vote"]
id = request.form["entertainment_index2"]
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        if vote:
            statement = """UPDATE Entertainment SET SCORE = (SCORE * VOTES + %s) / (VOTES+1),
                        VOTES = VOTES + 1 WHERE (ID = %s)"""
            cursor.execute(statement, (vote,id))

return redirect(url_for('entertainment_details', id=id))

2.1.2 Location.py

Main Page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@app.route('/location')
def location_page():
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        statement = """SELECT Location.ID, Location.NAME, Location.INFO, Location.PHOTO,
                    Cities.NAME, Countries.NAME FROM Location LEFT OUTER JOIN Cities
                    ON Location.City = Cities.ID
                    LEFT OUTER JOIN Countries
                    ON Location.Country=Countries.ID"""
        cursor.execute(statement)
        location_data = json.dumps(cursor.fetchall())
        location = json.loads(location_data)

        query = """SELECT ID,NAME FROM Cities"""
        cursor.execute(query)
        city_data = json.dumps(cursor.fetchall())
        city = json.loads(city_data)

        query = """SELECT ID,NAME FROM Countries"""
        cursor.execute(query)
        country_data = json.dumps(cursor.fetchall())
        country = json.loads(country_data)
now = datetime.datetime.now()
return render_template('location.html', current_time=now.ctime(), location=location, city = city, country = country)

Details Page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
@app.route('/location/<int:id>')
def location_details(id):
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        statement = """SELECT * FROM Location WHERE (ID = %s)"""
        cursor.execute(statement, (id,))
        location_data = json.dumps(cursor.fetchall())
        location = json.loads(location_data)

        query = """SELECT ID,NAME FROM Cities"""
        cursor.execute(query)
        city_data = json.dumps(cursor.fetchall())
        city = json.loads(city_data)

        query = """SELECT ID,NAME FROM Countries"""
        cursor.execute(query)
        country_data = json.dumps(cursor.fetchall())
        country = json.loads(country_data)

return render_template('location_details.html', location=location, city = city, country = country)

Insert

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
@app.route('/location/insert', methods=["POST"])
def location_insert():
name = request.form['location_name']
info = request.form['location_info']
photo = request.form['location_photo']
city = request.form['location_city']
country = request.form['location_country']
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        if name:
            if photo:
                statement = """INSERT INTO Location (NAME, INFO, PHOTO, CITY, COUNTRY)
                    VALUES (%s, %s, %s, %s)"""
                cursor.execute(statement, (name,info,photo,city,country))
            else:
                statement = """INSERT INTO Location (NAME, INFO, CITY, COUNTRY)
                        VALUES (%s, %s, %s, %s)"""
                cursor.execute(statement, (name,info,city,country))
return redirect(url_for('location_page'))

Delete

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
@app.route('/location/delete', methods=["POST"])
def location_delete():
id = request.form["select"]
id = int(id)
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        statement = """DELETE FROM Location WHERE ID = (%s)"""
        cursor.execute(statement, (id,))

return redirect(url_for('location_page'))

Update

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@app.route('/location/update', methods=["POST"])
def location_update():
name = request.form['location_update_name']
photo = request.form['location_update_photo']
info = request.form['location_update_info']
city = request.form['location_update_city']
country = request.form['location_update_country']
id = request.form['location_index']
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        if name:
            statement = """UPDATE Location SET (NAME) = (%s) WHERE (ID = %s)"""
            cursor.execute(statement, (name,id))
        if photo:
            statement = """UPDATE Location SET PHOTO = (%s) WHERE (ID = %s)"""
            cursor.execute(statement, (photo,id))
        if info:
            statement = """UPDATE Location SET INFO = (%s) WHERE (ID = %s)"""
            cursor.execute(statement, (info,id))
        if city:
            statement = """UPDATE Location SET CITY = (%s) WHERE (ID = %s)"""
            cursor.execute(statement, (city,id))
        if country:
            statement = """UPDATE Location SET COUNTRY = (%s) WHERE (ID = %s)"""
            cursor.execute(statement, (country,id))
return redirect(url_for('location_details',id=id))

Delete All

1
2
3
4
5
6
7
8
@app.route('/location/delete_all')
def location_delete_all():
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        query = """DELETE FROM Location"""
        cursor.execute(query)

return redirect(url_for('location_page'))

2.1.3 Transportation.py

Main Page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@app.route('/transportation')
def transportation_page():
#if g.user:
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        query = """SELECT Transportation.ID, Transportation.NAME, Transportation.INFO, Transportation.PHOTO,
                    Transportation.PLACE, Location.NAME FROM Transportation
                    LEFT OUTER JOIN Location
                    ON Transportation.PLACE=Location.ID"""
        cursor.execute(query)
        transportation_data = json.dumps(cursor.fetchall())
        transportation = json.loads(transportation_data)

        query = """SELECT ID,NAME FROM Location"""
        cursor.execute(query)
        location_data = json.dumps(cursor.fetchall())
        location = json.loads(location_data)

now = datetime.datetime.now()
if g.user:
    if(g.user == "admin"):
        usernum = 0
    else:
        usernum = 1
else:
    usernum = 2
return render_template('transportation.html', current_time=now.ctime(), transportation=transportation, location=location, usernum=usernum)
#return redirect(url_for('login_page'))

Details Page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
@app.route('/transportation/<int:id>')
def transportation_details(id):
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        statement = """SELECT Transportation.ID, Transportation.NAME, Transportation.INFO, Transportation.PHOTO,
                    Transportation.PLACE, Location.NAME FROM Transportation
                    LEFT OUTER JOIN Location
                    ON Transportation.PLACE=Location.ID
                    WHERE (Transportation.ID = %s)"""
        cursor.execute(statement, (id,))
        transportation_data = json.dumps(cursor.fetchall())
        transportation = json.loads(transportation_data)

        query = """SELECT ID,NAME FROM Location"""
        cursor.execute(query)
        location_data = json.dumps(cursor.fetchall())
        location = json.loads(location_data)

return render_template('transportation_details.html', transportation=transportation, location=location)

Insert

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@app.route('/transportation/insert', methods=["POST"])
def transportation_insert():
name = request.form['transportation_place_name']
info = request.form['transportation_place_info']
photo = request.form['transportation_place_photo']
place = request.form['transportation_place']
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        if name and place:
            query = """SELECT * FROM Location WHERE (ID = %s)"""
            cursor.execute(query, (place,))
            exists_data = json.dumps(cursor.fetchall())
            exists = json.loads(exists_data)
            if(exists):
                if photo:
                    statement = """INSERT INTO Entertainment (NAME, INFO, PHOTO, PLACE)
                        VALUES (%s, %s, %s, %s)"""
                    cursor.execute(statement, (name,info,photo,place))
                else:
                    statement = """INSERT INTO Entertainment (NAME, INFO, PLACE)
                        VALUES (%s, %s, %s, %s, %s, %s)"""
                    cursor.execute(statement, (name,info,place))
return redirect(url_for('transportation_page'))

Delete

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
@app.route('/transportation/delete', methods=["POST"])
def transportation_delete():
id = request.form["select"]
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        statement = """DELETE FROM Transportation
                    WHERE (ID = %s)"""
        cursor.execute(statement, (id))

return redirect(url_for('transportation_page'))

Update

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@app.route('/transportation/update', methods=["POST"])
def transportation_update():
id = request.form['transportation_index']
name = request.form['transportation_update_name']
photo = request.form["transportation_update_photo"]
info = request.form["transportation_update_info"]
place = request.form['transportation_update_place']
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        if name:
            statement = """UPDATE Transportation
                        SET (NAME) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (name,id))
        if info:
            statement = """UPDATE Transportation
                        SET (INFO) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (info,id))
        if photo:
            statement = """UPDATE Transportation
                        SET (PHOTO) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (photo,id))
        if place:
            statement = """UPDATE Transportation
                        SET (PLACE) = (%s)
                        WHERE (ID = %s)"""
            cursor.execute(statement, (place,id))

return redirect(url_for('transportation_page'))

Delete All

1
2
3
4
5
6
7
8
@app.route('/transportation/delete_all')
def transportation_delete_all():
with dbapi2.connect(app.config['dsn']) as connection:
    with connection.cursor() as cursor:
        query = """DELETE FROM Transportation"""
        cursor.execute(query)

return redirect(url_for('transportation_page'))