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
Esempio n. 9
0
    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"
Esempio n. 11
0
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"