ReservationControl.py
5.51 KB
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
# -*- coding: utf-8 -*-
# 한글 인코딩을 위한 주석입니다.
# 사전설정:
# pymysql 패키지가 있어야 합니다.
# pip install pymysql
# 보안상 user_info에서 DB id, password를 가져옵니다.
# user_info.py에서 DB id, password를 설정하고 사용해주세요.
# 외부에서 사용법:
# from ReservationControl import *
import pymysql
import user_info
import datetime
# getReservations - 대여현황반환 함수
# input:
# string user_id
# output:
# 성공시 : tuple형태로 반환 => ( (1 ,"steven123", 7, "전자정보대학", "136호", 10, datetime.datetime(2018,12,5,12,0,0), datetime.datetime(2018,12,5,12,10,0), ), )
# 순서 : reservations_id, user_id, facility_id, location, location_detail, capacity, start_time, end_time
# 실패시 : result[0][0]="SQL Error!"인 tuple 반환 => ( ("SQL Error!", ), )
# 사용예:
# result = getReservations("khucse123")
# reservation_start_time = result[0][3]
def getReservations(user_id):
try:
db = pymysql.connect(host='1.201.139.92', port=3306, user=user_info.user_id, password=user_info.user_passwd,
db='FRS', charset='utf8')
curs = db.cursor()
# curs.execute("select * from reservations where user_id=%s", user_id)
curs.execute(
"select r.reservations_id, r.user_id, r.facility_id, f.location, f.location_detail, f.capacity, r.start_time, r.end_time from reservations r, facilities f where r.user_id=%s AND r.facility_id=f.facility_id;",
user_id)
result = curs.fetchall()
print("Fetch Success!")
return result
except:
print("SQL Error!")
return (("SQL Error!",),)
finally:
db.close()
# deleteReservations - 대여현황삭제 함수
# input:
# int reservations_id
# output:
# 성공시 : True
# 실패시 : False
# 사용예:
# deleteReservations(5)
# 주의사항:
# 테스트환경에서 없는 reservations_id를 넣으면 에러가 날 수 있음
def deleteReservations(reservations_id):
try:
db = pymysql.connect(host='1.201.139.92', port=3306, user=user_info.user_id, password=user_info.user_passwd,
db='FRS', charset='utf8')
curs = db.cursor()
curs.execute("delete from reservations where reservations_id=%s", reservations_id)
db.commit()
print("Delete Success!")
return True
except:
print("SQL Error!")
return False
finally:
db.close()
# addReservations - 대여현황추가 함수
# input:
# string user_id
# int facility_id
# datetime.datetime start_time
# datetime.datetime end_time
# output:
# 성공시 : True
# 실패시 : False
# 사용예:
# tstart = datetime.datetime(2018,12,5,0,11,12)
# tend = datetime.datetime(2018,12,5,0,20,12)
# addReservations("khucse123", 6, tstart, tend)
# 주의사항:
# 테스트환경에서 없는 user_id를 넣으면 에러가 날 수 있음
# 테스트환경에서 없는 facility_id를 넣으면 에러가 날 수 있음
def addReservations(user_id, facility_id, start_time, end_time):
try:
db = pymysql.connect(host='1.201.139.92', port=3306, user=user_info.user_id, password=user_info.user_passwd,
db='FRS', charset='utf8')
curs = db.cursor()
curs.execute("insert into reservations (user_id, facility_id, start_time, end_time) values (%s,%s,%s,%s)",
(user_id, facility_id, start_time, end_time))
db.commit()
print("Add Reservation Success!")
return True
except:
print("SQL Error!")
return False
finally:
db.close()
# getAvailableFacilities - 사용가능시설물 반환함수
# input:
# string location
# int capacity
# datetime.datetime start_time
# datetime.datetime end_time
# output:
# 성공시 : tuple형태로 반환 => ( (1, "전자정보대학", "B01호", 40, "강의실", "빔프로젝터", ), )
# 순서 : facility_id, location, location_detail, capacity, facility_type, equipment
# 사용가능한 시설물이 없을 시 : result[0][0]="NoAvailableFacilites"인 tuple 반환 => ( ("NoAvailableFacilites", ), )
# 실패시 : result[0][0]="SQL Error!"인 tuple 반환 => ( ("SQL Error!", ), )
# 사용예:
# tstart = datetime.datetime(2018,12,5,12,11,12)
# tend = datetime.datetime(2018,12,5,14,20,12)
# result = getAvailableFacilities("전자정보대학", 40, tstart, tend)
# facility_id = result[0][0]
def getAvailableFacilities(location, capacity, start_time, end_time):
try:
db = pymysql.connect(host='1.201.139.92', port=3306, user=user_info.user_id, password=user_info.user_passwd,
db='FRS', charset='utf8')
curs = db.cursor()
curs.execute(
"select facility_id, location, location_detail, capacity, facility_type, equipment from facilities where (facility_id not in(select facility_id from reservations where (start_time<=%s AND %s<end_time) OR (start_time<%s AND %s<=end_time) OR (%s<=start_time AND end_time<=%s))) AND location=%s AND %s<=capacity;",
(start_time, start_time, end_time, end_time, start_time, end_time, location, capacity))
result = curs.fetchall()
print("Fetch Success!")
# 사용 가능한 시설물이 있을 경우
if (len(result) != 0):
return result
# 사용 가능한 시설물이 없을 경우
else:
print("No Available Facilities")
return (("NoAvailableFacilities",),)
except:
print("SQL Error!")
return (("SQL Error!",),)
finally:
db.close()