def delete(self, reservation_id):
claims = get_jwt_claims()
# Get target reservation
try:
result = app.database.execute(
text('''
SELECT count(user_email) count, user_email
FROM reservations
WHERE id= :reservation_id;
'''), {
'reservation_id': reservation_id
}).fetchone()
except Exception as exc:
return error_response(404, "해당 예약 건을 찾을 수 없습니다. " + str(exc))
if result['count'] == 0:
return error_response(404, "해당 예약 건을 찾을 수 없습니다.")
# Check level
if claims['level'] != 9:
if claims['email'] != result['user_email']:
return error_response(403, "관리자 혹은 예약 당사자만 취소가 가능합니다.")
# Querying
try:
result = app.database.execute(
text('''
DELETE FROM reservations
WHERE id= :reservation_id
'''), {'reservation_id': reservation_id})
except Exception as exc:
return error_response(500, str(exc))
return ok_response(None)
def get(self):
# Handle query parameters
params = {'user_email': None, 'query': None}
if 'user_email' in request.args:
params['user_email'] = request.args['user_email']
if 'query' in request.args:
params['query'] = request.args['query']
# Querying
_sql_email, _sql_query = '', ''
if params['user_email'] is not None:
_sql_email = ' AND R.user_email="{}" '.format(params['user_email'])
if params['query'] is not None:
_sql_query = ' AND (R.subject LIKE "%{}%" OR U.name LIKE "%{}%") '.format(
params['query'], params['query'])
rows = app.database.execute(
text('''
SELECT R.id id, R.classroom_id classroom_id, CL.id college_id, CL.name college_name,
R.user_email user_email, U.name user_name, R.start_time start_time, R.end_Time end_time, R.subject subject
FROM reservations R, users U, colleges CL, classrooms CR
WHERE R.user_email=U.email
AND R.classroom_id=CR.id
AND CR.college_id=CL.id
{}
{}
ORDER BY id DESC;
'''.format(_sql_email, _sql_query)), {}).fetchall()
result = {'reservation_count': 0, 'reservations': []}
for row in rows:
result['reservations'].append({
'reservation_id':
row['id'],
'classroom_id':
row['classroom_id'],
'college_id':
row['college_id'],
'college_name':
row['college_name'],
'user_email':
row['user_email'],
'user_name':
row['user_name'],
'start_time':
row['start_time'].strftime('%Y-%m-%d %H:%M'),
'end_time':
row['end_time'].strftime('%Y-%m-%d %H:%M'),
'subject':
row['subject']
})
result['reservation_count'] = len(result['reservations'])
return ok_response(result)
def get(self):
# Handle query parameters
params = {'room_id': None}
if 'room_id' in request.args:
params['room_id'] = request.args['room_id']
_room_id = params['room_id']
roomList = list(_room_id.split())
# 1 room
if len(roomList) == 1:
rows = app.database.execute(
text('''
SELECT start_time, end_time
FROM reservations
WHERE classroom_id = :classroom_id
ORDER BY start_time
'''), {
'classroom_id': _room_id
}).fetchall()
# 2 rooms
elif len(roomList) == 2:
rows = app.database.execute(
text('''
SELECT DISTINCT start_time, end_time
FROM reservations
WHERE classroom_id in (:room1, :room2)
ORDER BY start_time
'''), {
'room1': roomList[0],
'room2': roomList[1]
}).fetchall()
# Fetch
result = {'times': []}
for row in rows:
result['times'].append({
'start_time':
row['start_time'].strftime('%Y-%m-%d %H:%M'),
'end_time':
row['end_time'].strftime('%Y-%m-%d %H:%M')
})
return ok_response(result)
def get(self):
# Handle query parameters
params = {'room_id': None, 'start_time': None, 'end_time': None}
if 'room_id' in request.args:
params['room_id'] = request.args['room_id']
if 'start_time' in request.args:
params['start_time'] = request.args['start_time']
if 'end_time' in request.args:
params['end_time'] = request.args['end_time']
_room_id = params['room_id']
_start_time = dt.datetime.strptime(params['start_time'],
'%Y-%m-%d %H:%M')
_end_time = dt.datetime.strptime(params['end_time'], '%Y-%m-%d %H:%M')
# Querying
targetTuple = (_start_time, _end_time)
timeList = []
rows = app.database.execute(
text('''
SELECT start_time, end_time
FROM reservations
WHERE classroom_id = :classroom_id
'''), {
'classroom_id': _room_id
}).fetchall()
for row in rows:
tmpList = []
for value in row.values():
tmpList.append(value)
timeList.append(tmpList)
available = is_available(timeList, targetTuple)
# Fetch
result = {'available': []}
result['available'] = available
return ok_response(result)
def get(self, reservation_id):
# Querying
row = app.database.execute(
text('''
SELECT R.id reservation_id, R.classroom_id, U.email user_email, U.name user_name, R.start_time, R.end_time, R.subject
FROM reservations R, users U
WHERE R.user_email=U.email
AND R.id= :id;
'''), {
'id': reservation_id
}).fetchone()
result = {'reservation': {}}
result['reservation'] = {
'reservation_id': row['reservation_id'],
'classroom_id': row['classroom_id'],
'user_name': row['user_name'],
'user_email': row['user_email'],
'start_time': row['start_time'].strftime('%Y-%m-%d %H:%M'),
'end_time': row['end_time'].strftime('%Y-%m-%d %H:%M'),
'subject': row['subject']
}
return ok_response(result)
def get(self):
# Parse body params
params = {'college_id': None, 'capacity': None}
if 'college_id' in request.args:
params['college_id'] = request.args['college_id']
if 'capacity' in request.args:
params['capacity'] = request.args['capacity']
_college_id = params['college_id']
_capacity = int(params['capacity'])
# Querying
query = '''
SELECT C.name, R.id, R.capacity
FROM classrooms R, colleges C
WHERE R.college_id=C.id
'''
# all_rows = app.db_driver.execute_all(query)
all_rows = app.database.execute(
text('''
SELECT C.name, R.id, R.capacity
FROM classrooms R, colleges C
WHERE R.college_id=C.id
'''), {}).fetchall()
query += 'AND R.college_id = ' + str(
_college_id) + ' AND R.capacity >= ' + str(_capacity * 2)
# rows = app.db_driver.execute_all(query)
rows = app.database.execute(text(query), {}).fetchall()
class_dict = {}
name_list = []
#호수별 인원수 저장
for row in all_rows:
if row['id'] not in class_dict:
class_dict[row['id']] = row['capacity']
name_list.append(row['id'])
#조합 구하기
class_combinations = itertools.combinations(name_list, 2)
#조합별 인원수 계산 저장
comb_dict = {}
for combination in class_combinations:
comb_name = combination[0] + ' ' + combination[1]
if comb_name not in comb_dict:
tmp = int(class_dict[combination[0]]) + int(
class_dict[combination[1]])
if tmp >= int(_capacity) * 2:
comb_dict[comb_name] = tmp
# Fetch
result = {'rooms': []}
for row in rows:
result['rooms'].append({
'college': row['name'],
'classroom_id': row['id'],
'capacity': str(int(row['capacity']) // 2)
})
for row in comb_dict:
result['rooms'].append({
'college': '전자정보대학',
'classroom_id': row,
'capacity': str(comb_dict[row] // 2)
})
return ok_response(result)
def post(self):
if not request.is_json:
return error_response(400, 'JSON 형식으로 전달해주세요.')
claims = get_jwt_claims()
reserveList = request.json.get('reservations', None)
queryList = []
# check each reservation
for reserve in reserveList:
tmpEmail = reserve['user_email']
if not tmpEmail:
tmpEmail = claims['email']
is_user = True
else:
is_user = False
start_time = datetime.datetime.strptime(reserve['start_time'],
'%Y-%m-%d %H:%M')
end_time = datetime.datetime.strptime(reserve['end_time'],
'%Y-%m-%d %H:%M')
tmpTarget = (start_time, end_time)
tmpClassroom_id = reserve['classroom_id']
tmpSubject = reserve['subject']
tmpList = [
tmpClassroom_id, tmpEmail, tmpTarget[0], tmpTarget[1],
tmpSubject
]
queryList.append(tmpList)
if not is_user:
emailList = []
rows = app.database.execute(
text('''
SELECT *
FROM users
'''), {}).fetchall()
for row in rows:
emailList.append(row['email'])
if tmpEmail not in emailList:
return error_response(400, '유효하지 않은 이메일입니다.')
# is_available
rows = app.database.execute(
text('''
SELECT start_time, end_time
FROM reservations
WHERE classroom_id = :classroom_id
'''), {
'classroom_id': tmpClassroom_id
}).fetchall()
timeList = []
for row in rows:
tmpList = []
for value in row.values():
tmpList.append(value)
timeList.append(tmpList)
available = is_available(timeList, tmpTarget)
if available == False:
return error_response(400, '예약 불가능한 시간입니다.')
# Insert Querying
try:
app.database.execute(
text('''
INSERT INTO reservations (classroom_id, user_email, start_time, end_time, subject)
VALUES ( :classroom_id1, :user_email1, :start_time1, :end_time1, :subject1),( :classroom_id2, :user_email2, :start_time2, :end_time2, :subject2)
'''), {
'classroom_id1': queryList[0][0],
'user_email1': queryList[0][1],
'start_time1': queryList[0][2],
'end_time1': queryList[0][3],
'subject1': queryList[0][4],
'classroom_id2': queryList[1][0],
'user_email2': queryList[1][1],
'start_time2': queryList[1][2],
'end_time2': queryList[1][3],
'subject2': queryList[1][4]
})
except Exception as exc:
return error_response(500, str(exc))
return ok_response(None)
def put(self, reservation_id):
claims = get_jwt_claims()
# Get target reservation
try:
result = app.database.execute(
text('''
SELECT count(user_email) count, user_email
FROM reservations WHERE id= :reservation_id
'''), {
'reservation_id': reservation_id
}).fetchone()
except Exception as exc:
return error_response(404, "해당 예약 건을 찾을 수 없습니다. " + str(exc))
if result['count'] == 0:
return error_response(404, "해당 예약 건을 찾을 수 없습니다.")
# Check level
if claims['level'] != 9:
if claims['email'] != result['user_email']:
return error_response(403, "관리자 혹은 예약 당사자만 수정이 가능합니다.")
# Handle body parameters
if not request.is_json:
return error_response(400, 'JSON 형식으로 전달해주세요.')
try:
start_time = request.json.get('start_time', None)
end_time = request.json.get('end_time', None)
classroom_id = request.json.get('classroom_id', None)
subject = request.json.get('subject', None)
origin_start_time = request.json.get('origin_start_time', None)
origin_end_time = request.json.get('origin_end_time', None)
if not start_time:
return error_response(400, 'start_time을 전달해주세요.')
else:
start_time = datetime.datetime.strptime(
start_time, '%Y-%m-%d %H:%M')
if not end_time:
return error_response(400, 'end_time을 전달해주세요.')
else:
end_time = datetime.datetime.strptime(end_time,
'%Y-%m-%d %H:%M')
if not origin_start_time:
return error_response(400, 'origin_start_time을 전달해주세요.')
else:
origin_start_time = datetime.datetime.strptime(
origin_start_time, '%Y-%m-%d %H:%M')
if not origin_end_time:
return error_response(400, 'origin_end_time 전달해주세요.')
else:
origin_end_time = datetime.datetime.strptime(
origin_end_time, '%Y-%m-%d %H:%M')
if not classroom_id:
return error_response(400, 'classroom_id를 전달해주세요.')
if not subject:
return error_response(400, 'subject를 전달해주세요.')
except Exception as exc:
return error_response(400, 'JSON 파싱 에러가 발생했습니다 : ' + str(exc))
targetTuple = (start_time, end_time)
originTuple = [origin_start_time, origin_end_time]
timeList = []
rows = app.database.execute(
text('''
SELECT start_time, end_time
FROM reservations
WHERE classroom_id = :classroom_id
'''), {
'classroom_id': classroom_id
}).fetchall()
for row in rows:
tmpList = []
for value in row.values():
tmpList.append(value)
timeList.append(tmpList)
timeList.remove(originTuple)
available = is_available(timeList, targetTuple)
if available == False:
return error_response(400, '예약 불가능한 시간입니다.')
# Querying
try:
app.database.execute(
text('''
UPDATE reservations SET start_time= :start_time, end_time= :end_time, classroom_id= :classroom_id, subject= :subject WHERE id= :reservation_id;
'''), {
'start_time': start_time,
'end_time': end_time,
'classroom_id': classroom_id,
'subject': subject,
'reservation_id': reservation_id
})
except Exception as exc:
return error_response(500, str(exc))
return ok_response(None)
def post(self):
if not request.is_json:
return error_response(400, 'JSON 형식으로 전달해주세요.')
claims = get_jwt_claims()
# Handle body parameters
try:
user_email = request.json.get('user_email', None)
start_time = request.json.get('start_time', None)
end_time = request.json.get('end_time', None)
classroom_id = request.json.get('classroom_id', None)
subject = request.json.get('subject', None)
if not user_email:
user_email = claims['email']
is_user = True
else:
is_user = False
if not start_time:
return error_response(400, 'start_time을 전달해주세요.')
else:
start_time = datetime.datetime.strptime(
start_time, '%Y-%m-%d %H:%M')
if not end_time:
return error_response(400, 'end_time을 전달해주세요.')
else:
end_time = datetime.datetime.strptime(end_time,
'%Y-%m-%d %H:%M')
if not classroom_id:
return error_response(400, 'classroom_id를 전달해주세요.')
if not subject:
return error_response(400, 'subject를 전달해주세요.')
except Exception as exc:
return error_response(400, 'JSON 파싱 에러가 발생했습니다 : ' + str(exc))
if not is_user:
emailList = []
rows = app.database.execute(
text('''
SELECT *
FROM users
'''), {}).fetchall()
for row in rows:
emailList.append(row['email'])
if user_email not in emailList:
return error_response(400, '유효하지 않은 이메일입니다.')
# is_available
targetTuple = (start_time, end_time)
timeList = []
rows = app.database.execute(
text('''
SELECT start_time, end_time
FROM reservations
WHERE classroom_id = :classroom_id
'''), {
'classroom_id': classroom_id
}).fetchall()
for row in rows:
tmpList = []
for value in row.values():
tmpList.append(value)
timeList.append(tmpList)
available = is_available(timeList, targetTuple)
if available == False:
return error_response(400, '예약 불가능한 시간입니다.')
try:
app.database.execute(
text('''
INSERT INTO reservations (classroom_id, user_email, start_time, end_time, subject) VALUES ( :classroom_id, :user_email, :start_time, :end_time, :subject);
'''), {
'classroom_id': classroom_id,
'user_email': user_email,
'start_time': start_time,
'end_time': end_time,
'subject': subject
})
except Exception as exc:
return error_response(500, str(exc))
return ok_response(None)