Portfolio

				
					from fastapi import FastAPI, UploadFile, File, Response, Depends
from fastapi.middleware.cors import CORSMiddleware
from openpyxl import load_workbook
import json
from models import Session
from models.e911Model import E911data, create_table, drop_table


app = FastAPI()
origins = [
]
app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=['*'],
    allow_headers=['*'],
)


def get_db():
    session = Session()
    try:
        yield session
    finally:
        session.close()


@app.get("/ping")
async def healthcheck():
    return {'Test': 'OK'}


def load_file_data(request):
    with open('temp.xlsx', 'wb') as f:
        b = request.read()
        f.write(b)

    book = load_workbook('temp.xlsx')
    sheet = book.active
    rows = list(sheet.rows)
    headers = [x.value for x in rows[0]]
    return [dict(zip(headers, [x.value for x in row])) for row in rows[1:]]


def validator(x):
    if isinstance(x, int):
        return x >= 0
    elif isinstance(x, str):
        return x != ''
    else:
        print(x)
        return x


def strip_periods_and_spaces(string: str):
    string = string.strip()
    if string.endswith('.'):
        return string.replace('.', '')
    else:
        return string


def import_data(session, filename=None):

    formatted_data = load_file_data(filename)

    drop_table()
    create_table()
    for d in formatted_data:
        if d['DIR']:
            dir = strip_periods_and_spaces(d['DIR'])
        else:
            dir=''

        street = strip_periods_and_spaces(d['STREET'])
        length = len(street)
        # Getting rid of the abbreviation and spelling out the whole direction ie N = NORTH
        if street[-1] == 'N' and street[length-2] == ' ':
            street = street.replace('N', 'NORTH')
        if street[-1] == 'S' and street[length-2] == ' ':
            street = street.replace('S', 'SOUTH')
        if street[-1] == 'E' and street[length-2] == ' ':
            street = street.replace('E', 'EAST')
        if street[-1] == 'W' and street[length-2] == ' ':
            street = street.replace('W', 'WEST')

        houselow = d['HOUSELOW']
        househigh = d['HOUSEHIGH']
        city = strip_periods_and_spaces(d['CITY'])
        state = strip_periods_and_spaces(d['STATE'])
        esn = d['ESN']
        validated = list(map(validator, [street, houselow, househigh, city, state, esn]))
        if not all(validated):
            print(d, [street, houselow, househigh, city, state, esn])
            break

        entry = E911data(
            dir=dir,
            street=street,
            houselow=houselow,
            househigh=househigh,
            city=city,
            state=state,
            esn=esn,
        )
        session.add(entry)
    session.commit()


@app.post("/file_upload")
async def file_upload(file: UploadFile = File(...), session: Session = Depends(get_db)):
    if not file:
        return {"message": "No upload file sent"}
    else:
        import_data(session, file.file)
        return {"message": "file sent"}


def query_address(session, city, final_dir, house_number, street_name):
    query_object = session.query(E911data.dir, E911data.city, E911data.state, E911data.street, E911data.esn).filter(
        E911data.city == city,
        E911data.dir == final_dir,
        E911data.houselow <= int(house_number),
        E911data.househigh >= int(house_number),
        E911data.street == street_name).all()
    return query_object


@app.get("/validate")
async def validate_address(street: str = '', city: str = '', state: str = 'UT', country: str = 'US',
                           postalcode: str = 00000, session: Session = Depends(get_db)):
    final_dir = ''
    house_number = ''
    street_name = ''
    street = street.upper().strip()
    street_list = street.split(' ')
    need_direction = True

    if street == '' or city == '':
        return {'validate': False}

    # strip out any .
    for index, item in enumerate(street_list):
        street_list[index] = item.strip('.')

    direction = street_list[0]

    # If there is no direction
    if direction[-1].isnumeric() and (street_list[1] != 'N'
                                      and street_list[1] != 'S'
                                      and street_list[1] != 'E'
                                      and street_list[1] != 'W'
                                      and street_list[1] != 'NORTH'
                                      and street_list[1] != 'SOUTH'
                                      and street_list[1] != 'EAST'
                                      and street_list[1] != 'WEST'):
        house_number = street_list[0]
        final_dir = ''
        for ins in street_list[1:]:
            street_name = street_name + ' ' + ins

        # strip spaces off of beginning and end of street_name
        street_name = street_name.strip()
        need_direction = False

    # if direction is typed out it needs to be an abbreviation
    if direction.endswith('NORTH'):
        direction = direction.replace('NORTH', 'N')
    elif direction.endswith('SOUTH'):
        direction = direction.replace('SOUTH', 'S')
    elif direction.endswith('EAST'):
        direction = direction.replace('EAST', 'E')
    elif direction.endswith('WEST'):
        direction = direction.replace('WEST', 'W')
    size = len(direction)

    # separating the direction and house number
    if direction.endswith('N') or direction.endswith('S') or direction.endswith('E') or direction.endswith('W'):
        final_dir = direction[-1]
        house_number = direction[0:size-1]

        for ins in street_list[1:]:
            street_name = street_name + ' ' + ins
            # strip spaces off of beginning and end of street_name
            street_name = street_name.strip()

    else:
        # if there is no direction this can be skipped
        if need_direction:
            final_dir = street_list[1]
            house_number = street_list[0]

            for ins in street_list[2:]:
                street_name = street_name + ' ' + ins

            # strip spaces off of beginning and end of street_name
            street_name = street_name.strip()
    city = city.upper().strip()

    # Setting the direction to be an abbreviation
    if final_dir == 'NORTH':
        final_dir = 'N'
    if final_dir == 'SOUTH':
        final_dir = 'S'
    if final_dir == 'EAST':
        final_dir = 'E'
    if final_dir == 'WEST':
        final_dir = 'W'

    # if there is no space after the street number and the direction, this adds the space
    length = len(street_name)
    if street_name.endswith('NORTH') and street_name[length - 6] != ' ':
        street_name = street_name.replace('NORTH', ' NORTH')
    if street_name.endswith('SOUTH') and street_name[length - 6] != ' ':
        street_name = street_name.replace('SOUTH', ' SOUTH')
    if street_name.endswith('EAST') and street_name[length - 5] != ' ':
        street_name = street_name.replace('EAST', ' EAST')
    if street_name.endswith('WEST') and street_name[length - 5] != ' ':
        street_name = street_name.replace('WEST', ' WEST')

    # if there is only an abbreviation, it will replace it with the full direction name
    length = len(street_name)
    if street_name.endswith('N') and street_name[length - 2] == ' ':
        street_name = street_name.replace('N', 'NORTH')
    if street_name.endswith('S') and street_name[length - 2] == ' ':
        street_name = street_name.replace('S', 'SOUTH')
    if street_name.endswith('E') and street_name[length - 2] == ' ':
        street_name = street_name.replace('E', 'EAST')
    if street_name.endswith('W') and street_name[length - 2] == ' ':
        street_name = street_name.replace('W', 'WEST')

    # if there is only an abbreviation and no space it will put in a space and the full direction
    length = len(street_name)
    if street_name.endswith('N') and street_name[length - 2].isnumeric():
        street_name = street_name.replace('N', ' NORTH')
    if street_name.endswith('S') and street_name[length - 2].isnumeric():
        street_name = street_name.replace('S', ' SOUTH')
    if street_name.endswith('E') and street_name[length - 2].isnumeric():
        street_name = street_name.replace('E', ' EAST')
    if street_name.endswith('W') and street_name[length - 2].isnumeric():
        street_name = street_name.replace('W', ' WEST')

    if not house_number.isnumeric():
        return {'validate': False}

    # If we have an exact hit, return that the address was found
    query_object = query_address(session, city, final_dir, house_number, street_name)
    if len(query_object) > 0:
        return {'validate': 'exists',
                'query_object': query_object}
    else:
        # Iterate though all strings in the street name and get a query of each string, put them in a list
        final_address_list = set()
        c = city.split()
        for street in street_name.split():

            query_object = session.query(E911data.dir, E911data.city, E911data.state, E911data.street, E911data.esn).filter(
                E911data.street.ilike(f'%{street}%'),
                E911data.city.ilike(f'{c[0]}%'),
                # E911data.city.match(f'{c[0]}%'),
                E911data.houselow <= int(house_number), E911data.househigh >= int(house_number)).all()
            if query_object:
                for obj in query_object:
                    address = obj
                    if len(address) == 5:
                        final_dir = address[0]
                        city = address[1]
                        street_name = address[3]
                        esn = address[-1]
                        if house_number + ' ' + final_dir + ' ' + street_name not in final_address_list:
                            final_address_list.add(house_number + ' ' + final_dir + ' ' + street_name + ', ' + city)
                    if len(address) == 4:
                        city = address[0]
                        street_name = address[2]
                        esn = address[-1]
                        if house_number + ' ' + final_dir + ' ' + street_name not in final_address_list:
                            final_address_list.add(house_number + ' ' + final_dir + ' ' + street_name + ', ' + city)
        if len(final_address_list) > 0:
            f_address_list = sorted(final_address_list)
            return {'validate': 'maybe',
                    'addressList': f_address_list
                    }
        else:
            return {'validate': False}