def login_test():
if request.method == 'POST':
username = request.form['username']
db = get_db()
error = None
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT * FROM user WHERE username= %s', (username,))
user = cursor.fetchone()
if error is None:
session.clear()
session['user_id'] = user['user_id']
flash(error)
return redirect(url_for('apartment.index'))
def get_ownerList():
db = get_db()
cursor = db.cursor()
cursor.execute(
'SELECT a.name, a.street_address, a.price, username,room_number,bathroom_number,street_address,zip,city,state,price,sqft'
' FROM apartment a JOIN user u ON a.landlord_id = u.user_id'
' WHERE u.user_id = %s', (g.user['user_id'], ))
ownerList = cursor.fetchall()
if not ownerList:
abort(404, "There is no apartments in your account:(")
result = []
for index in range(len(ownerList)):
apt = ownerList[index]
item = {}
item['name'] = apt['name']
item['room_number'] = apt['room_number']
item['bathroom_number'] = apt['bathroom_number']
item['street_address'] = apt['street_address']
item['zip'] = apt['zip']
item['city'] = apt['city']
item['state'] = apt['state']
item['price'] = apt['price']
item['sqft'] = apt['sqft']
result.append(item)
return jsonify(result)
def accept_offer(reservation_id):
reservation = get_reservation(reservation_id)
nest = get_nest(reservation['nest_id'])
if nest['status'] != 'APPROVED':
abort(403, "Can't accept offer without approval from landlord.")
db = get_db()
cursor = db.cursor()
cursor.execute(
"""UPDATE reservation SET accept_offer = %s
WHERE reservation_id = %s""", (1, reservation_id))
'''
if this is the last person in the nest who accept offer,
change the other nests in the associated apartment to be rejected.
'''
if all_accept_offer(nest['nest_id']):
nests = get_nests(nest['apartment_id'])
for n in nests:
if n['nest_id'] != nest['nest_id']:
cursor.execute(
"""UPDATE nest SET status = %s
WHERE nest_id = %s""", ('REJECTED', n['nest_id']))
db.commit()
rv = get_reservation(reservation_id)
row_headers = ['reservation_id', 'nest_id', 'tenant_id', 'accept_offer']
json_data = dict(zip(row_headers, rv))
return json.dumps(json_data)
def get_reservations():
db = get_db()
cursor = db.cursor()
cursor.execute(
'SELECT reservation_id, r.nest_id, created, accept_offer'
' FROM reservation r'
' WHERE r.tenant_id = %s', (g.user['user_id'], ))
reserveList = cursor.fetchall()
if reserveList is None:
abort(
404,
"Nest id {0} doesn't exist or doesn't have reservations.".format(
g.user['user_id']))
result = []
for index in range(len(reserveList)):
apt = reserveList[index]
item = {}
item['reservation_id'] = apt['reservation_id']
item['nest_id'] = apt['nest_id']
item['created'] = apt['created']
item['accept_offer'] = apt['accept_offer']
result.append(item)
return jsonify(result)
def get_reserveNest():
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT nest_id, accept_offer
FROM reservation
WHERE tenant_id = %s
ORDER BY created DESC""", (g.user['user_id'], ))
reservation_list = cursor.fetchall()
result = []
for index in range(len(reservation_list)):
reservation = reservation_list[index]
nest_id = reservation['nest_id']
cursor.execute(
"""SELECT apartment_id, status
FROM nest
WHERE nest_id = %s""", (nest_id, ))
nest = cursor.fetchone()
cursor.execute(
"""SELECT name
FROM apartment
WHERE apartment_id = %s""", (nest['apartment_id'], ))
apartment = cursor.fetchone()
res = nestTwoNumber(nest_id)
item = {}
item['room_number'] = res['room_number']
item['user_number'] = res['user_number']
item['apartment_name'] = apartment['name']
item['status'] = nest['status']
item['nest_id'] = nest_id
result.append(item)
return jsonify(result)
def app():
app = create_app({
'TESTING': True,
# 'DATABASE': db_path,
})
url = urllib.parse.urlparse(os.environ['TEST_DATABASE_URL'])
app.config["DATABASE_HOSTNAME"] = url.hostname
app.config["DATABASE_USERNAME"] = url.username
app.config["DATABASE_PASSWORD"] = url.password
app.config["DATABASE_NAME"] = url.path[1:]
with app.app_context():
init_db()
db = get_db()
# get_db().executescript(_data_sql)
ret = _data_sql.split(';')
# drop last empty entry
ret.pop()
for stmt in ret:
db.cursor().execute(stmt + ";")
db.commit()
yield app
def test_create(client, auth, app):
auth.login()
response = client.get('/nest/20/create')
assert response.status_code == 404
assert b'Apartment not found.' in response.data
assert client.get('/nest/2/create').status_code == 200
for i in range(5):
response = client.post('/nest/12/create', data={})
with app.app_context(), client:
db = get_db()
cursor = db.cursor()
client.get('/')
cursor.execute(
"""SELECT DISTINCT n.nest_id
FROM nest n JOIN reservation r ON n.nest_id = r.nest_id
WHERE r.tenant_id = %s
AND n.apartment_id = %s""",
# ORDER BY created DESC""",
(g.user['user_id'], 12))
record = cursor.fetchall()
len(record) == 2 + i
if i < 4:
assert len(record) == 2 + i
else:
assert b'User has already been added to five nests belong to this apartment. Please cancal the previous reservation and create a new nest again.' in response.data
assert len(record) == 5
def get_apartment(nestId):
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT name, room_number, n.apartment_id
FROM apartment p JOIN nest n ON p.apartment_id = n.apartment_id
WHERE n.nest_id = %s""", (nestId, ))
apartment = cursor.fetchone()
return apartment
def index():
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT *'
' FROM apartment'
' ORDER BY created DESC'
' LIMIT 10')
apartments = cursor.fetchall()
return jsonify(apartments)
def load_logged_in_user():
user_id = session.get('user_id')
cursor = get_db().cursor()
if user_id is None:
g.user = None
else:
cursor.execute('SELECT * FROM user WHERE user_id = %s', (user_id, ))
g.user = cursor.fetchone()
print(user_id)
def test_index(client, auth, app):
client.get('/')
with app.app_context():
db = get_db()
cursor = db.cursor()
cursor.execute(
'SELECT * FROM apartment ORDER BY created DESC LIMIT 10')
apartment = cursor.fetchall()
assert apartment is not None
assert apartment[0]['room_number'] == 2
assert apartment[1]['zip'] == 98107
def test_create(client, auth, app):
with app.app_context():
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT COUNT(reservation_id) FROM reservation')
prevCount = cursor.fetchone()['COUNT(reservation_id)']
auth.login()
client.post('reservation/create/nest_id/22', data={})
cursor.execute('SELECT COUNT(reservation_id) FROM reservation')
count = cursor.fetchone()['COUNT(reservation_id)']
assert count == prevCount + 1
def get_apartment(apartmentId, check_user=True):
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT *'
' FROM apartment'
' WHERE apartment_id = %s', (apartmentId, ))
apartment = cursor.fetchone()
if apartment is None:
abort(404, "Apartment id {0} doesn't exist.".format(apartmentId))
return apartment
def get_nest(nest_id):
db = get_db()
cursor = db.cursor()
cursor.execute("""SELECT *
FROM nest
WHERE nest_id = %s""", (nest_id, ))
nest = cursor.fetchone()
if nest is None:
abort(404, "Nest id {0} doesn't exist.".format(nest_id))
return nest
def test_update(client, auth, app):
auth.login()
response = client.get('/nest/20/update')
assert response.status_code == 404
assert b'Nest not found' in response.data
response = client.get('/nest/2/update')
assert b'Redirecting' in response.data
response = client.post('/nest/2/update', data={'decision': ''})
assert b'Decision is required.' in response.data
response = client.post('/nest/72/update', data={'decision': 'APPROVED'})
assert response.status_code == 302
assert b'Redirecting' in response.data
with app.app_context():
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT status FROM nest WHERE nest_id = 2')
nest = cursor.fetchone()
assert nest['status'] == 'APPROVED'
response = client.post('/nest/2/update', data={'decision': 'REJECTED'})
assert b'Cannot change nest status that is not PENDING' in response.data
response = client.post('/nest/22/update', data={'decision': 'REJECTED'})
assert b'Current user is not authorized to alter the status of this nest.' in response.data
response = client.post('/nest/12/update', data={'decision': 'APPROVED'})
assert b'Landlord has already approved one nest' in response.data
with app.app_context():
db = get_db()
cursor = db.cursor()
cursor.execute(
"""UPDATE nest SET status = %s
WHERE nest_id = %s""", ('PENDING', 2))
db.commit()
response = client.post('/nest/32/update', data={'decision': 'APPROVED'})
assert b'This nest is not full yet, landlord cannot alter nest status' in response.data
def create():
"""Create a new post for the current user."""
if request.method == 'POST':
name = request.form['name']
room_number = request.form['room_number']
bathroom_number = request.form['bathroom_number']
street_address = request.form['street_address']
city = request.form['city']
state = request.form['state']
zip = request.form['zip']
price = request.form['price']
sqft = request.form['sqft']
description = request.form['description']
photo_URL = request.form['photo_URL']
error = None
if not name:
error = 'name is required.'
if not room_number:
error = 'room number is required.'
if not bathroom_number:
error = 'bathroom number is required.'
if not street_address:
error = 'street address is required.'
if not city:
error = 'city is required.'
if not state:
error = 'state is required.'
if not zip:
error = 'zip is required.'
if not price:
error = 'price is required.'
if not sqft:
error = 'sqft is required.'
if error is not None:
flash(error)
else:
db = get_db()
cursor = db.cursor()
#Store apartment inforamtion
cursor.execute(
'INSERT INTO apartment (room_number, bathroom_number, street_address, city,state,zip ,price,sqft,name,description,landlord_id, photo_URL)'
' VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(room_number, bathroom_number, street_address, city, state,
zip, price, sqft, name, description, g.user['user_id'],
photo_URL))
db.commit()
return redirect(url_for('apartment.index'))
return render_template('apartment/create.html')
def test_accept_offer(client, auth, app):
auth.login()
client.put('reservation/2/accept_offer', data={})
with app.app_context():
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT * FROM reservation WHERE reservation_id = 2')
reservation = cursor.fetchone()
assert reservation['accept_offer'] == 1
cursor.execute('SELECT * FROM nest WHERE nest_id = 12')
other_nest = cursor.fetchone()
assert other_nest['status'] == 'REJECTED'
def test_delete(client, auth, app):
auth.login()
response = client.delete('reservation/2/delete')
with app.app_context():
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT * FROM reservation WHERE reservation_id = 2')
reservation = cursor.fetchone()
assert reservation is None
cursor.execute('SELECT * FROM nest WHERE nest_id = 2')
nest = cursor.fetchone()
assert nest['status'] == 'PENDING'
def get_num_of_nests_in_one_apartment(user_id, apartment_id):
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT COUNT(DISTINCT r.nest_id) c
FROM reservation r JOIN nest n ON r.nest_id = n.nest_id
WHERE r.tenant_id = %s and n.apartment_id = %s""",
(user_id, apartment_id))
num_of_nests = cursor.fetchone()
print("number of nests in apartment ", apartment_id, " that user ",
user_id, " joined is: ", num_of_nests['c'])
return num_of_nests['c']
def get_nests(apartment_id):
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT *
FROM nest
WHERE apartment_id = %s""", (apartment_id, ))
nests = cursor.fetchall()
if nests is None:
abort(404,
"No nest associated with apartment id {0}.".format(apartment_id))
return nests
def get_nestUser(nestId):
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT username, first_name, last_name, email, gender, description
FROM reservation p JOIN user u ON p.tenant_id = u.user_id
WHERE p.nest_id = %s
ORDER BY created DESC""", (nestId, ))
users = cursor.fetchall()
# columns = ['username', 'first_name', 'last_name', 'email', 'gender', 'description']
res = []
for user in users:
# res.append(dict(zip(columns, user)))
res.append(user)
return res
def get_apartment(apartmentId, check_user=True):
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT *'
' FROM apartment'
' WHERE apartment_id = %s', (apartmentId, ))
apartment = cursor.fetchone()
if apartment is None:
abort(404, "Apartment id {0} doesn't exist.".format(apartmentId))
# if check_user and apartment['landlord_id'] != g.user['user_id']:
# abort(403, "You can only modify your own apartment.")
return apartment
def get_apartment(nest_id):
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT n.apartment_id, room_number
FROM nest n JOIN apartment a ON n.apartment_id = a.apartment_id
WHERE nest_id = %s""", (nest_id, ))
apartment = cursor.fetchone()
if apartment is None:
abort(
404,
"Nest id {0} doesn't exist or doesn't have an associated appartment."
.format(nest_id))
return apartment
def get_reservations(nest_id):
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT reservation_id, r.nest_id, tenant_id, created, accept_offer, apartment_id, status
FROM reservation r JOIN nest n ON r.nest_id = n.nest_id
WHERE r.nest_id = %s""", (nest_id, ))
reservations = cursor.fetchall()
if reservations is None:
abort(
404,
"Nest id {0} doesn't exist or doesn't have reservations.".format(
nest_id))
return reservations
def get_reservation(reservation_id, check_user=True):
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT reservation_id, nest_id, tenant_id, accept_offer
FROM reservation
WHERE reservation_id = %s""", (reservation_id, ))
reservation = cursor.fetchone()
if reservation is None:
abort(404, "Reservation id {0} doesn't exist.".format(reservation_id))
if check_user and reservation['tenant_id'] != g.user['user_id']:
abort(403, "You can only modify your own reservation.")
return reservation
def test_update_appartment(client, auth, app):
auth.login()
response = client.get('/apartment/2/update')
assert b'Are you sure?' in response.data
assert client.get('/apartment/12/update').status_code == 403
response = client.post('/apartment/2/update',
data={
'name': '',
'room_number': '2',
'bathroom_number': '2',
'zip': '98107',
'street_address': 'HAHA',
'city': 'Seattle',
'state': 'WA',
'price': '1000',
'sqft': '200',
'description': '',
'photo_URL': ''
})
assert b'Name is required.' in response.data
response = client.post('/apartment/2/update',
data={
'name': 'AAA',
'room_number': '2',
'bathroom_number': '2',
'zip': '98107',
'street_address': 'HAHA',
'city': 'Seattle',
'state': 'WA',
'price': '1000',
'sqft': '200',
'description': '',
'photo_URL': ''
})
assert b'Redirecting' in response.data
with app.app_context():
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT * FROM apartment WHERE apartment_id = 2')
apartment = cursor.fetchone()
assert apartment['name'] == 'AAA'
def test_delete_appartment(client, auth, app):
auth.login()
with app.app_context():
db = get_db()
cursor = db.cursor()
assert client.post('/apartment/7/delete').status_code == 404
response = client.post('/apartment/9/delete')
assert b'doesn\'t exist.' in response.data
client.post('/apartment/2/delete')
cursor.execute('SELECT * FROM apartment WHERE apartment_id = 2')
apartment = cursor.fetchone()
assert apartment is None
cursor.execute('SELECT * FROM nest WHERE nest_id = 2')
nest = cursor.fetchall()
assert len(nest) == 0
cursor.execute('SELECT * FROM reservation WHERE reservation_id = 2')
reservation = cursor.fetchall()
assert len(reservation) == 0
def get_nests(apartmentId):
db = get_db()
cursor = db.cursor()
# check if the given apartmentId is valid
cursor.execute('SELECT name'
' FROM apartment'
' WHERE apartment_id = %s', (apartmentId, ))
apartment = cursor.fetchall()
if len(apartment) == 0:
abort(404, "Apartment id {0} doesn't exist.".format(id))
cursor.execute('SELECT *'
' FROM nest'
' WHERE apartment_id = %s', (apartmentId, ))
nestList = cursor.fetchall()
return nestList
def search():
db = get_db()
cursor = db.cursor()
zip = request.args.get('zipcode', 0, type=int)
error = None
if not zip:
error = 'ZipCode is required.'
cached_zip_result = r.get(zip)
if error is not None:
flash(error)
elif cached_zip_result is not None:
apartments = r.get(zip)
cacheresult = apartments.decode('utf8').replace("'", '"')
cacheresult_re = re.sub('"created".*?\),', '', cacheresult)
print('cache result: ', cacheresult_re)
return cacheresult_re
else:
print(2)
cursor.execute(
'SELECT *'
' FROM apartment'
' WHERE zip = %s'
' ORDER BY created DESC', (zip, ))
apartments = cursor.fetchall()
if apartments:
print('apartments')
print(apartments)
json_result = jsonify(apartments)
print('json_result')
print(json_result)
r.set(zip, apartments)
r.expire(zip, 100)
# return json_result
return jsonify(apartments)
else:
abort(
404,
"No such apartment matching given zipcode exists in our databse. Sorry! :("
)
return jsonify([])
def get_ownerNest():
result = []
db = get_db()
cursor = db.cursor()
cursor.execute(
"""SELECT apartment_id, name
FROM apartment
WHERE landlord_id = %s
ORDER BY created DESC""", (g.user['user_id'], ))
apartment_list = cursor.fetchall()
for index in range(len(apartment_list)):
apartment = apartment_list[index]
item = {}
item['fullnest'] = fullNest_helper(apartment['apartment_id'])
item['notFullnest'] = notFullNest_helper(apartment['apartment_id'])
item['apartment_name'] = apartment['name']
result.append(item)
return jsonify(result)