def select_reservation_by_id(data):
info = []
for el in SQLS.db_fetch("SELECT * FROM reservations WHERE resId = '%s';" % str(data))[0]:
info.append(str(el))
info.append("|")
info.pop(-1)
SQLS.output_update_to_screen("Received a request reservation by ID . . . . . . . ")
return info
def select_schedule():
info = []
info_get = SQLS.db_fetch("SELECT inventoryItemID, datesUnavailable from inventorySchedule;")
try:
for el in info_get:
for ell in el:
info.append(str(ell))
info.append(", ")
info.append("%")
info.pop(-1)
except:
info.append("NA")
SQLS.output_update_to_screen("Received a request for schedule . . . . . . . ")
return info
def select_rooms():
info = []
info_get = SQLS.db_fetch("SELECT * from rooms ORDER BY roomName;")
try:
for el in info_get:
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
except :
info.append("NA")
SQLS.output_update_to_screen("Received a request for room names . . . . . . . ")
return info
def select_inventory():
info = []
info_get = SQLS.db_fetch("SELECT * from inventory ORDER BY title;")
try:
for el in info_get:
for ell in el:
info.append(str(ell).replace('\n', ' '))
info.append("|")
info.append("%")
info.pop(-1)
except :
info.append("NA")
SQLS.output_update_to_screen("Received a request for inventory items . . . . . . . ")
return info
def select_links():
info = []
info_get = SQLS.db_fetch("SELECT * FROM repeated ;")
try:
for el in info_get:
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
except:
info.append("NA")
SQLS.output_update_to_screen("Received a request for linked items . . . . . . . ")
return info;
def select_reserved_items_not_returned():
info = []
reservations = SQLS.db_fetch(
"SELECT * FROM reservations WHERE timeDelivered != '0000-00-00 00:00:00' AND timePickedup = '0000-00-00 00:00:00';"
)
for el in reservations:
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
SQLS.output_update_to_screen("Request for items not in reservation room . . . . . . . . ")
return info
def select_past_deliveries():
info = []
info_get = SQLS.db_fetch("SELECT * FROM reservations WHERE timeDelivered != '0000-00-00 00:00:00' AND timePickedup \
= '0000-00-00 00:00:00' AND DATE(resEnd) <= CURDATE() ORDER BY resEnd;")
# "SELECT * FROM reservations WHERE timeDelivered != '0000-00-00 00:00:00' AND timePickedup \
# = '0000-00-00 00:00:00' AND resEnd <= NOW() ORDER BY resEnd;"
try:
for el in info_get:
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
except:
info.append("NA")
SQLS.output_update_to_screen("Request for today's pickups . . . . . . . . . . . . . ")
return info
def select_future_deliveries():
info = []
info_get = SQLS.db_fetch(
"select * from reservations where DATE(resStart) = CURDATE() AND deliveredBy = 'NA' ORDER BY resStart;")
try:
for el in info_get:
for ell in el:
info.append(str(ell))
info.append("|")
info.append("%")
info.pop(-1)
if info[-1] == "|":
info.pop(-1)
except :
# If there are no deliveries for the day, send this back :
info.append("NA")
SQLS.output_update_to_screen("Received a request for today's deliveries . . . . . ")
return info
def handle(self):
reply = None
# Receive data from client
self.data = self.request.recv(sql_settings.BLOCK).split("^")
if self.data[0] in recognized_queries:
reply = request_handler.request_handler(self.data)
if self.data[0] not in recognized_queries:
reply = "?"
sql_settings.output_update_to_screen("UNRECOGNIZED QUERY !")
sql_settings.output_update_to_screen(self.data)
if reply is not None:
self.request.send(reply)
self.request.close()
def request_handler(request):
info = []
if internet_on():
# ===================[ CONNECTION INQUIRIES] =============
if request[0] == 'ci':
# Ask if api connected to 10. network
if request[1] == 'inet':
print "\n\tRequest to test server connection\n"
if internet_on():
info.append("y")
else:
info.append("n")
# Ask if api running at all
if request[1] == 'ira':
print "\n\tRequest to rest api connection\n"
info.append("y")
# ===================[ SELECT QUERIES ]===================
if request[0] == 's':
# Select All future deliveries
if request[1] == 'fd':
info = SELECT.select_future_deliveries()
if request[1] == 'pd':
info = SELECT.select_past_deliveries()
if request[1] == 'lnks':
info = SELECT.select_links()
if request[1] == 'inv':
info = SELECT.select_inventory()
if request[1] == 'sch':
info = SELECT.select_schedule()
if request[1] == 'rms':
info = SELECT.select_rooms()
if request[1] == 'rbi':
info = SELECT.select_reservation_by_id(request[2])
################## [EDITING] ###################################
if request[1] == 'inir':
info = SELECT.select_reserved_items_not_returned()
# ===================[ UPDATE QUERIES ]===================
if request[0] == 'u':
if request[1] == "rls":
SQLS.output_update_to_screen("URLS")
if request[1] == "spe":
info = UPDATE.update_reservation_for_pickup(request)
if request[1] == "sde":
info = UPDATE.update_delivery_set_delivered(request)
if request[1] == "eds":
info = UPDATE.update_delivery_series(request)
# ===================[ INSERT QUERIES ]===================
if request[0] == 'i':
if request[1] == "rn":
info = INSERT.insert_new_delivery_location(request[2])
if request[1] == "newr":
info = INSERT.insert_new_delivery(request)
# Update existing record
SQLS.output_update_to_screen("Insert Requested")
# ====================[ ADMIN QUERIES ]====================
if request[0] == 'a':
if request[1] == 'lgn':
info = ADMIN.admin_check_login(request)
if request[1] == 'udbi':
info = ADMIN.admin_edit_device_by_id(request[2])
if request[1] == 'add':
info = ADMIN.admin_add_device(request[2])
if request[1] == 'rem':
info = ADMIN.admin_remove_device(request[2])
if request[1] == 'frem':
info = ADMIN.admin_do_remove_device(request[2])
if request[1] == 'chng':
info = ADMIN.change_user_password(request)
if request[1] == "fdel":
info = ADMIN.proxy_select_future_reservations(request[2])
# Used to show the end of transfer
reply = ''.join(info).replace('\n', ' ')
reply += '\n'
return reply
else:
print "NOT ABLE TO CONTACT SQL"
return "NC\n"
def admin_remove_device(request):
# Get list of reservations
future_reservations = SQLS.db_fetch(
"SELECT * from reservations WHERE DATE(resStart) >= CURDATE();"
)
conflicts = []
repeated_ids_checked = []
for el in future_reservations:
each_device_in_reservation = el[11].split(", ")
for ell in each_device_in_reservation:
if ell == request:
conflict_map = ""
if el[12] != "NA" and el[12] not in repeated_ids_checked:
repeated_ids_checked = el[12]
startEndDateTimes = SQLS.db_fetch(
"SELECT firstOccurance, lastOccurance, type FROM repeated WHERE repeatedid = %s;" % el[12]
)
# If current reservation is part of a series, place in it's id and dates for repeated table
conflict_map = "r" + "|" + str(el[12]) + "|" + str(startEndDateTimes[0][0]) \
+ "|" + str(startEndDateTimes[0][1]) + "|" + startEndDateTimes[0][2] + "|" + str(ell)
elif el[12] == "NA":
startEndDateTimes = SQLS.db_fetch(
"SELECT resStart, resEnd FROM reservations WHERE resId = %s;" % el[0]
)
# If current reservation is not part of a series, place in its resId, and dateTimes
conflict_map = "s" + "|" + str(el[0]) + "|" + str(startEndDateTimes[0][0]) \
+ "|" + str(startEndDateTimes[0][1]) + "|" + str(ell)
if conflict_map != "":
conflicts.append(conflict_map)
conflicts.append("%")
if len(conflicts) > 0:
if conflicts[-1] == "%":
conflicts.pop(-1)
conflicts_to_return = ""
for el in conflicts:
conflicts_to_return += el
# If there are conflicts with removing the device, alert the user to do a replacement call
if conflicts_to_return != "":
return conflicts_to_return
else:
SQLS.output_update_to_screen(("Removing device : " + request + ". No conflicts found."))
SQLS.db_update(
"DELETE FROM inventory WHERE invID = %s;" % request
)
SQLS.db_update(
"DELETE FROM inventoryschedule WHERE inventoryItemID = %s;" % request
)
return "1"