-
Notifications
You must be signed in to change notification settings - Fork 0
/
admin.py
228 lines (204 loc) · 8.81 KB
/
admin.py
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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
import database
def get_all_the_corona_spreaders():
'''
Question 1
Give the details of all the guests who rented properties.
Display the columns as guest name, rentaltype, rentalprice, signingdate, branch, payment type and payment status.
Sort by the payment type in ascending order and signing date in descending order.
'''
query_string = "SELECT guest.firstname, guest.middleinitial, guest.lastname, property.buildingtype,property.price, booking.startdate, operatingin.branchid, payment.typeofpayment, payment.status from guest\
inner join hasbooking\
on hasbooking.guestid = guest.guestid\
inner join booking\
on hasbooking.bookingid = booking.bookingid\
inner join property\
on property.propertyid = booking.propertyid\
inner join operatingin\
on operatingin.propertyid = property.propertyid\
inner join payedfor\
on payedfor.bookingid = booking.bookingid\
inner join payment\
on payment.paymentid = payedfor.paymentid\
ORDER by payment.typeofpayment asc, booking.startdate desc;"
result = database.execute_query(query_string)
for row in result:
print(row)
def get_guest_list_view():
'''
Question 2
Create a view named GuestListView that gives the details of all the guests.
Sort the guests by the branch id and then by guest id.
'''
query_string = ("select * from GuestListView")
result = database.execute_query(query_string)
print("This is the Guest List View")
for row in result:
print(row)
def get_cheapest_rental():
'''
Question 3
Display the details of the cheapest (completed) rental.
'''
query_string = ("select min(price) from property\
inner join booking\
on booking.propertyid = property.propertyid\
where booking.startdate <= '2020-04-06'")
result = database.execute_query(query_string)
print(f"The lowest rental price is {result}")
def get_all_rented_properties():
'''
Question 4
List all the properties rented and sort based on the branch id and review rating.
'''
#not sure if working
query_string = ("SELECT Booking.propertyID,reviews.rating,operatingin.branchid FROM booking\
INNER JOIN hasreview\
ON booking.propertyID = hasreview.propertyID\
INNER JOIN reviews\
ON hasreview.reviewID = reviews.reviewID\
INNER JOIN OperatingIn\
ON booking.propertyID = OperatingIn.PropertyID\
WHERE booking.enddate <= '2020-04-06'\
ORDER BY branchID, rating")
result = database.execute_query(query_string)
print('The following properties are rented:')
for row in result:
print('Property ID {}, rating {}, branch ID {}'.format( row[0], row[1], row[2]))
def get_all_available_properties():
'''
Question 5
Find the properties that are already listed but not yet rented. Please, avoid duplications.
'''
query_string = "SELECT propertyid\
FROM booking\
where (startdate >= '2020-04-06') and propertyID not in (select propertyID\
from booking\
where startdate < '2020-04-06')"
result = database.execute_query(query_string)
print('The following properties are available:')
for row in result:
print('Property ID {}'.format( row[0]))
def get_the_tenth_legion():
'''
Question 6
List all the details of all properties rented on the 10th day of any month.
Ensure to insert dates in your table that correspond in order to run your query
'''
query_string = "SELECT property.* , booking.startdate FROM property\
inner join booking\
on booking.propertyid = property.propertyid\
where (extract (day from startdate)) = '10'"
result = database.execute_query(query_string)
for row in result:
print(row)
return None
def get_all_rich_people():
'''
Question 7
List all the managers and the employees with salary greater than or equal to $15000
by their: ids, names, branch ids, branch names and salary.
Sort by manager id and then by employee id.
'''
query_string = "select employee.employeeid, employee.firstname, employee.middleinitial, employee.lastname, worksat.branchid,branches.country, employee.salary, employee.eposition from employee\
inner join worksat\
on worksat.employeeid = employee.employeeid\
inner join branches\
on branches.branchid = worksat.branchid\
where employee.salary >= 50000\
order by employee.eposition desc, employee.employeeid;"
result = database.execute_query(query_string)
for row in result:
print(row)
def create_bill():
'''
Question 8
Consider creating a simple bill for a guest stating the property type, host, address, amount paid and payment type.
'''
print('Enter the guestID for the bill: ')
guestID = -1
while guestID < 1:
print('Please enter numerical value for desired option:')
try:
guestID = int(input())
except:
print('Invalid input format')
query_string = f"select property.buildingtype, host.firstname,host.middleinitial,host.lastname, address.addressl1,address.addressl2, address.city,address.province,address.country,address.postalcode, payment.amount, payment.typeofpayment from payment\
inner join payedfor\
on payment.paymentID = payedfor.paymentID\
inner join booking\
on payedfor.bookingid = booking.bookingid\
inner join property\
on booking.propertyid = property.propertyid\
inner join hasaddress\
on property.propertyid = hasaddress.propertyID\
inner join address\
on hasaddress.addressid = address.addressId\
inner join host\
on payment.receiverID = host.hostID\
where booking.guestID = {guestID};"
result = database.execute_query(query_string)
print('This is the output of the create bill')
for row in result:
print(row)
def new_phone_who_dis():
'''
Question 9
Update the phone number of a guest.
'''
guestID = int(input('Please enter guest ID'))
new_number = input('please enter new phone number: ')
s = "{" + new_number + "}"
query_string = f"update guest\
set phonenum = '{s}'\
where guestid = {guestID};"
database.execute_query(query_string)
return None
def firstNameFirst():
'''
Create and test a user-defined function named FirstNameFirst that combines two attributes of the
guest named firstName and lastName into a concatenated value named fullName [e.g., James and Brown will be combined to read James Brown].
'''
query_string = "select firstnamefirst(guest.firstname, guest.lastname) from guest"
result = database.execute_query(query_string)
print('This is the output of the function First Name First')
for row in result:
print(row)
def exit_program():
exit()
if __name__ == "__main__":
print("\nWelcome administrator, we've been expecting you.\n")
while True:
print('\n\nPlease select one of the following options: \n\
1: Give the details of all the guests who rented properties.\n\
2: Create a view named GuestListView that gives the details of all the guests.\n\
3: Display the details of the cheapest (completed) rental.\n\
4: List all the properties rented and sort based on the branch id and review rating.\n\
5: Find the properties that are already listed but not yet rented.\n\
6: List all the details of all properties rented on the 10th day of any month.\n\
7: List all the managers and the employees with salary greater than or equal to $15000.\n\
8: Create a simple bill for a guest.\n\
9: Update the phone number of a guest.\n\
10: Create and test a user-defined function named FirstNameFirst.\n\
11: Exit Program\n\n')
switcher = {
1: get_all_the_corona_spreaders,
2: get_guest_list_view,
3: get_cheapest_rental,
4: get_all_rented_properties,
5: get_all_available_properties,
6: get_the_tenth_legion,
7: get_all_rich_people,
8: create_bill,
9: new_phone_who_dis,
10: firstNameFirst,
11: exit_program
}
chosen_option = -1
while chosen_option < 1 or chosen_option > 11:
print('Please enter numerical value for desired option:')
try:
chosen_option = int(input())
except:
print('Invalid input format')
chosen_func = switcher.get(chosen_option)
chosen_func()