def all_locations() -> List[Tuple[str, str, str]]:
"""
Returns a list of tuples comprising of the distinct sites,
towns and counties.
For example:
[
("Atlantic City", "Atlantic City", "Atlantic"),
("New Jersey Ave", "Atlantic City", "Atlantic"),
...
]
Returns:
A list of tuples.
"""
query = """
SELECT
DISTINCT coa_summary_view.site_name,
coa_summary_view.town,
coa_summary_view.county
FROM coa.coa_summary_view
"""
with Accessor() as db_handle:
db_handle.execute(query)
return db_handle.fetchall()
def valid_date_range(location_category: str,
location_name: str) -> Dict[str, str]:
"""
Returns the date range for a location.
Args:
location_category: The category of location, site, town, or county.
location_name: The name of the location.
Returns:
The date range.
"""
if location_category not in {'site_name', 'town', 'county'}:
return {}
query = """
SELECT
MIN(volunteer_date),
MAX(volunteer_date)
FROM coa_summary_view
WHERE """ + location_category + """ = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (location_name))
first, last = db_handle.fetchone()
return {
'firstDate': first.strftime('%Y-%m-%d'),
'lastDate': last.strftime('%Y-%m-%d')
}
def get_trash_items() -> Dict[str, List[str]]:
"""
Get all the trash items for the drop downs.
Returns:
The mapping of material to trash items.
"""
query = """
SELECT
DISTINCT item_id,
material,
category,
item_name
FROM coa.item
"""
with Accessor() as db_handle:
db_handle.execute(query)
items = [(row[1], f'{row[2]}, {row[3]}[{row[0]}]')
for row in db_handle.fetchall()]
trash_items: Dict[str, List[str]] = {}
for material, item in items:
if material in trash_items:
# This handles inserting in sorted order
bisect.insort(trash_items[material], item)
else:
trash_items[material] = [item]
return trash_items
def update(
event_id: int,
updated_by: str,
site_id: int,
volunteer_year: int,
volunteer_season: str,
volunteer_cnt: Optional[int],
trashbag_cnt: Optional[float],
trash_weight: Optional[float],
walking_distance: Optional[float],
) -> None:
"""
Updates an event.
Args:
event_id: The ID of the event.
updated_by: The user adding the item.
site_id: The ID of the site where the event took place.
volunteer_year: The year of event.
volunteer_season: The season of the event.
volunteer_cnt: The count of volunteers at the event.
trashbag_cnt: The count of trashbags collected.
trash_weight: The weight of the trashbags.
walking_distance: The total distance walked of the volunteers.
"""
mon = 4 if volunteer_season == "Spring" else 10
volunteer_date = datetime.strptime(f"{volunteer_year}-{mon}",
"%Y-%m").date()
query = """
UPDATE coa_data.event
SET
updated_by = %s,
site_id = %s,
volunteer_date = %s,
volunteer_cnt = %s,
trashbag_cnt = %s,
trash_weight = %s,
walking_distance = %s
WHERE event_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(
query,
(
updated_by,
site_id,
volunteer_date,
volunteer_cnt,
trashbag_cnt,
trash_weight,
walking_distance,
event_id,
),
)
def remove(record_id: int) -> None:
"""
Removes an event item.
Args:
record_id: The ID of the event item.
"""
query = """
DELETE FROM coa_data.event_items
WHERE record_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (record_id, ))
def remove(site_id: int) -> None:
"""
Removes an site.
Args:
site_id: The ID of the site.
"""
query = """
DELETE FROM coa_data.site
WHERE site_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (site_id,))
def remove(item_id: int) -> None:
"""
Removes an item.
Args:
item_id: The ID of the item.
"""
query = """
DELETE FROM coa_data.item
WHERE item_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (item_id, ))
def remove(event_id: int) -> None:
"""
Removes an event.
Args:
event_id: The ID of the event.
"""
query = """
DELETE FROM coa_data.event
WHERE event_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (event_id,))
def add(
updated_by: str,
site_id: int,
volunteer_year: int,
volunteer_season: str,
volunteer_cnt: Optional[int],
trashbag_cnt: Optional[float],
trash_weight: Optional[float],
walking_distance: Optional[float],
) -> None:
"""
Adds an item.
Args:
updated_by: The user adding the item.
site_id: The ID of the site where the event took place.
volunteer_year: The year of event.
volunteer_season: The season of the event.
volunteer_cnt: The count of volunteers at the event.
trashbag_cnt: The count of trashbags collected.
trash_weight: The weight of the trashbags.
walking_distance: The total distance walked of the volunteers.
"""
mon = 4 if volunteer_season == "Spring" else 10
volunteer_date = datetime.strptime(f"{volunteer_year}-{mon}",
"%Y-%m").date()
query = """
INSERT INTO coa_data.event(
updated_by,
site_id,
volunteer_date,
volunteer_cnt,
trashbag_cnt,
trash_weight,
walking_distance
)
VALUES(%s, %s, %s, %s, %s, %s, %s)
"""
with Accessor() as db_handle:
db_handle.execute(
query,
(
updated_by,
site_id,
volunteer_date,
volunteer_cnt,
trashbag_cnt,
trash_weight,
walking_distance,
),
)
def insert_contribution(post_str: str) -> None:
"""
Inserts into the database that a contribution was made.
"""
# TODO: This should be changed as this is all tied to how the
# data was passed in the older version.
team_query = """
INSERT INTO coa.team_info
(site_id,
volunteer_date,
team_captain,
num_of_people,
num_of_trashbags,
trash_weight,
walking_distance,
updated_by)
VALUES
"""
volunteer_query = """
INSERT INTO coa.volunteer_info
(team_id,
item_id,
quantity,
brand,
updated_by,
event_code)
VALUES
"""
team_info, volunteer_info = post_str.split('----')
row = team_info.split('#')
team_query += "(%s,'%s','%s',%s,%s,%s,%s,'%s')" % (
row[0], datetime.strptime(row[1], '%m/%d/%Y').strftime("%Y-%m-%d"),
row[2], row[3], row[4], row[5], row[6], row[7])
volunteer_reader = csv.reader(volunteer_info.split('||'), delimiter='#')
for row in volunteer_reader:
if row:
volunteer_query += "(%s,%s,%s,'%s','%s','%s')," % (
'LAST_INSERT_ID()', row[0].split('[')[1].split(']')[0], row[1],
row[2], row[3], row[4])
query = f"""BEGIN;
{team_query}';
{volunteer_query[:-1]};
COMMIT;
"""
with Accessor() as db_handle:
db_handle.execute(query)
def get_tls() -> List[str]:
"""
Get all the team leads for the drop downs.
Returns:
The list of all the team leads.
"""
query = """
SELECT
DISTINCT team_captain
FROM coa.team_info
ORDER BY team_captain
"""
with Accessor() as db_handle:
db_handle.execute(query)
return [tl[0] for tl in db_handle.fetchall()]
def get(volunteer_year: int, volunteer_season: str) -> List[Event]:
"""
Gets a list of events.
Returns:
A list of events.
"""
query = """
SELECT
cde.event_id,
cde.site_id,
cde.volunteer_cnt,
IFNULL(SUM(cei.quantity), 0) AS trash_items_cnt,
cde.trashbag_cnt,
cde.trash_weight,
cde.walking_distance,
cde.updated_by,
cde.updated_tsp
FROM coa_data.event AS cde
LEFT JOIN coa_data.event_items AS cei ON cei.event_id = cde.event_id
WHERE
cde.volunteer_year = %s AND
cde.volunteer_season = %s
GROUP BY cde.event_id
"""
with Accessor() as db_handle:
db_handle.execute(query, (volunteer_year, volunteer_season))
return [{
"event_id":
record["event_id"],
"site_id":
record["site_id"],
"volunteer_cnt":
record["volunteer_cnt"],
"trash_items_cnt":
int(record["trash_items_cnt"]),
"trashbag_cnt":
record["trashbag_cnt"],
"trash_weight":
record["trash_weight"],
"walking_distance":
record["walking_distance"],
"updated_by":
record["updated_by"],
"updated_tsp":
record["updated_tsp"].strftime("%Y-%m-%d %H:%M"),
} for record in db_handle.fetchall()]
def get() -> List[Item]:
"""
Gets a list of items.
Returns:
A list of items.
"""
query = """
SELECT
item_id,
material,
category,
item_name
FROM coa_data.item
"""
with Accessor() as db_handle:
db_handle.execute(query)
return db_handle.fetchall()
def update(
site_id: int,
site_name: str,
state: str,
county: str,
town: str,
street: str,
zipcode: str,
lat: float,
long_f: float,
) -> None:
"""
Updates a site.
Args:
site_id: The ID of the site.
site_name: The name of the site.
state: The state the site is in.
county: The county the site is in.
town: The town the site is in.
street: The street the site is on.
zipcode: The zipcode the site is in.
lat: The latitude of the site.
long_f: The longitude of the site.
"""
query = """
UPDATE coa_data.site
SET
site_name = %s,
state = %s,
county = %s,
town = %s,
street = %s,
zipcode = %s,
lat = %s,
`long` = %s
WHERE site_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(
query,
(site_name, state, county, town, street, zipcode, lat, long_f,
site_id),
)
def add(material: str, category: str, item_name: str) -> None:
"""
Adds an item.
Args:
material: The material of the item.
category: The category of the item.
item_name: The name of the item.
"""
query = """
INSERT INTO coa_data.item(
material,
category,
item_name
)
VALUES(%s, %s, %s)
"""
with Accessor() as db_handle:
db_handle.execute(query, (material, category, item_name))
def get() -> List[Site]:
"""
Gets a list of sites.
Returns:
A list of sites.
"""
query = """
SELECT
site_id,
site_name,
state,
county,
town,
street,
zipcode,
lat,
`long`
FROM coa_data.site
"""
with Accessor() as db_handle:
db_handle.execute(query)
return [{
"site_id":
record["site_id"],
"site_name":
record["site_name"],
"state":
record["state"],
"county":
record["county"],
"town":
record["town"],
"street":
record["street"],
"zipcode":
record["zipcode"],
"lat":
None if record["lat"] is None else float(record["lat"]),
"long":
None if record["long"] is None else float(record["long"]),
} for record in db_handle.fetchall()]
def update(item_id: int, material: str, category: str, item_name: str) -> None:
"""
Updates an item.
Args:
item_id: The ID of the item.
material: The material of the item.
category: The category of the item.
item_name: The name of the item.
"""
query = """
UPDATE coa_data.item
SET
material = %s,
category = %s,
item_name = %s
WHERE item_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (material, category, item_name, item_id))
def login(username: str, password: str) -> str:
"""
A login function to handle authentication.
Args:
username: The username of person trying to login.
password: The password of the person trying to login.
Returns:
The JWT if there are authorized.
Raises:
This can raise Unauthorized errors if the login attempt fails.
"""
query = """
SELECT
password
FROM coa.ab_user AS cau
WHERE
cau.username = %s AND
cau.active = 1
"""
with Accessor() as db_handle:
db_handle.execute(query, (username,))
record = db_handle.fetchone()
if record is None or not check_password_hash(record["password"], password):
raise Unauthorized()
return str(
jwt.encode(
{
"exp": datetime.datetime.utcnow()
+ datetime.timedelta(days=0, hours=6),
"iat": datetime.datetime.utcnow(),
"sub": username,
},
os.environ["SECRET_KEY"],
algorithm="HS256",
)
)
def add(event_id: int, item_id: int, quantity: int, updated_by: str) -> None:
"""
Adds an event item.
Args:
event_id: The ID of the event.
item_id: The ID of the item collected.
quantity: The quantity of the item collected.
updated_by: The user making the update.
"""
query = """
INSERT INTO coa_data.event_items(
event_id,
item_id,
quantity,
updated_by
)
VALUES(%s, %s, %s, %s)
"""
with Accessor() as db_handle:
db_handle.execute(query, (event_id, item_id, quantity, updated_by))
def add(
site_name: str,
state: str,
county: str,
town: str,
street: Optional[str],
zipcode: Optional[str],
lat: Optional[float],
long_f: Optional[float],
) -> None:
"""
Adds a site.
Args:
site_name: The name of the site.
state: The state the site is in.
county: The county the site is in.
town: The town the site is in.
street: The street the site is on.
zipcode: The zipcode the site is in.
lat: The latitude of the site.
long_f: The longitude of the site.
"""
query = """
INSERT INTO coa_data.site(
site_name,
state,
county,
town,
street,
zipcode,
lat,
`long`
)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s)
"""
with Accessor() as db_handle:
db_handle.execute(
query, (site_name, state, county, town, street, zipcode, lat, long_f)
)
def item_breakdown(location_category: str,
location_name: str,
start_date: str,
end_date: str) -> List[Tuple[int, str, str, str, int]]:
"""
Returns a list of tuples comprising of the item id, item name, category,
material, and quantity.
Args:
location_category: The type of location.
location_name: The name of the location.
start_date: The start date.
end_date: The end date.
Returns:
A list of item id, item name, category, material, quantity.
"""
if (location_category not in {'site_name', 'town', 'county'}
or end_date < start_date):
return []
query = """
SELECT
item_id,
item_name,
category,
material,
SUM(quantity) AS quantity_sum
FROM coa_summary_view
WHERE %s <= volunteer_date
AND volunteer_date <= %s
AND """ + location_category + """ = %s
GROUP BY item_name
"""
with Accessor() as db_handle:
db_handle.execute(query, (start_date,
end_date,
location_name))
return db_handle.fetchall()
def get(volunteer_year: int, volunteer_season: str) -> List[Event]:
"""
Gets a list of events.
Returns:
A list of events.
"""
query = """
SELECT
event_id,
site_id,
volunteer_cnt,
trashbag_cnt,
trash_weight,
walking_distance
FROM coa_data.event AS cde
WHERE
cde.volunteer_year = %s AND
cde.volunteer_season = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (volunteer_year, volunteer_season))
return db_handle.fetchall()
def get(event_id: int) -> List[EventItem]:
"""
Gets a list of event items.
Args:
event_id: The ID of the event.
Returns:
A list of event items.
"""
query = """
SELECT
record_id,
event_id,
item_id,
quantity,
updated_by,
updated_tsp
FROM coa_data.event_items AS cdei
WHERE cdei.event_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(query, (event_id, ))
return [{
"record_id":
record["record_id"],
"event_id":
record["event_id"],
"item_id":
record["item_id"],
"quantity":
record["quantity"],
"updated_by":
record["updated_by"],
"updated_tsp":
record["updated_tsp"].strftime("%Y-%m-%d %H:%M"),
} for record in db_handle.fetchall()]
def update(record_id: int, event_id: int, item_id: int, quantity: int,
updated_by: str) -> None:
"""
Updates an event item.
Args:
record_id: The ID of the event item.
event_id: The ID of the event.
item_id: The ID of the item collected.
quantity: The quantity of the item collected.
updated_by: The user making the update.
"""
query = """
UPDATE coa_data.event_items
SET
event_id = %s,
item_id = %s,
quantity = %s,
updated_by = %s
WHERE record_id = %s
"""
with Accessor() as db_handle:
db_handle.execute(query,
(event_id, item_id, quantity, updated_by, record_id))