def unassign_requests():
"""
Unassign a request. This feature would be used by the volunteer.
Request: application/json
request_id int
Request ID which is to be Unassigned.
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
request_detail_update_query = (
"UPDATE request_details "
f"SET volunteer_id='NULL', updated_at='{timestamp}'"
f"WHERE request_id = {data['request_id']}"
)
cursor.execute(request_detail_update_query)
transaction_update_query = (
"UPDATE token_transactions"
f"SET volunteer_id='NULL', updated_at='{timestamp}'"
f"WHERE request_id = {data['request_id']}"
)
cursor.execute(transaction_update_query)
conn.commit()
return utils.make_response("{error: None}", 200)
def modify_requests():
"""
Modify a request. This feature would be used by the requester.
Request: application/json
request_id int
Request ID which is to be Assigned.
request_information str
Modified content of the request
request_note str
Note associated with a request
tokens str
Tokens associated with a request
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
request_update_query = (
"UPDATE request_details "
f"SET request_information={data['request_information']}, "
f"request_note='{data.get('request_note', 'NULL')}', "
f"updated_at='{timestamp}' WHERE request_id = {data['request_id']}"
)
cursor.execute(request_update_query)
if "tokens" in data:
token_update_query = (
"UPDATE token_transactions"
f"SET tokens={data['tokens']}, updated_at='{timestamp}' "
f"WHERE request_id= {data['request_id']}"
)
cursor.execute(token_update_query)
conn.commit()
return utils.make_response("{error: None}", 200)
def acknowledge_health():
"""
Acknowledge the health of the person, to ensure that he is not showing symptomps of
COVID-19 on a given day.
Rquest: application/json
login_id int
Login id of the user for whom the health record is being logged.
has_fever boolean
If the user has fever
has_cough boolean
If the user has cough
has_tiredness boolean
If the user has tiredness
has_breath_shortness boolean
If user is having difficult time breathing
has_headech boolean
If the user has headech
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
query = (
"INSERT INTO health_history (login_id, has_fever, has_cough, has_tiredness, "
"has_breath_shortness, has_headache, created_at, updated_at) VALUES ( "
f"{data['login_id']}, '{data['has_fever']}', '{data['has_cough']}', "
f"'{data['has_tiredness']}', '{data['has_breath_shortness']}', '{data['has_headache']}'"
f"'{timestamp}', '{timestamp}')")
cursor.execute(query)
conn.commit()
return utils.make_response("{error: None}", 200)
def completed_requests():
"""
Complete a request. This feature would be used by the volunteer.
Request: application/json
request_id int
Request ID which is to be complete.
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
request_update_query = (
"UPDATE request_details "
f"SET is_completed='true', updated_at='{timestamp}'"
f"WHERE request_id = {data['request_id']}"
)
cursor.execute(request_update_query)
transaction_update_query = (
f"UPDATE token_transactions SET is_complete='true', updated_at='{timestamp}' "
f"WHERE request_id={data['request_id']} RETURNING volunteer_id, tokens"
)
cursor.execute(transaction_update_query)
volunteer_id, total_tokens = cursor.fetchone()
volunteer_token_update_query = (
f"UPDATE volunteer_details SET tokens = tokens+{total_tokens}, updated_at='{timestamp}' "
f"WHERE volunteer_id={volunteer_id}"
)
cursor.execute(volunteer_token_update_query)
conn.commit()
return utils.make_response("{error: None}", 200)
def cancel_requests():
"""
Cancle the submitted request.
Request: application/json
request_id int
Request ID to be cancelled.
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
transaction_update_query = f"DELETE FROM token_transactions WHERE request_id = {data['request_id']} RETURNING tokens"
cursor.execute(transaction_update_query)
tokens = cursor.fetchone()[0]
request_update_query = (
"UPDATE request_details "
f"SET is_cancelled='true', updated_at='{timestamp}'"
f"WHERE request_id = {data['request_id']} RETURNING requester_id"
)
cursor.execute(request_update_query)
requester_id = cursor.fetchone()[0]
token_update_query = f"UPDATE requester_details SET tokens=tokens+{tokens} WHERE requester_id = {requester_id}"
cursor.execute(token_update_query)
conn.commit()
return utils.make_response("{error: None}", 200)
def submit_request():
"""
Submit requests to get help.
Request: application/json
request_type str
Type of the request. Currently only grocery type is supported.
request_information string
Things to be brought at the grocery. This is more like a list or can be
text. But it is preffered that it is a list to keep it short and concise.
request_notes string (optional)
Additional notes pertaining to request.
request_id int
Id of the requester sending the request.
volunteer_id int (optional)
Volunteer Id picking up the request
tokens int
Number of tokens awarded for completing the request
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
requester_id = 37
request_query = (
"INSERT INTO request_details (request_date, request_type, request_information, "
"request_note, requester_id, volunteer_id, is_cancelled, is_commenced, "
f"is_completed, created_at, updated_at) VALUES ('{timestamp}', '{data['request_type']}', "
f"'{data['request_information']}', '{data.get('request_note', 'NULL')}', "
f"'{requester_id}', {data.get('volunteer_id', 'NULL')}, "
f"'{data.get('is_cancelled', 'false')}', '{data.get('is_commenced', 'false')}', "
f"'{data.get('is_completed', 'false')}', '{timestamp}', '{timestamp}') RETURNING request_id"
)
cursor.execute(request_query)
# request_id = cursor.fetchone()[0]
# transaction_query = (
# "INSERT INTO token_transactions (volunteer_id, requester_id, request_id, tokens, "
# f"is_complete, created_at, updated_at) VALUES ({data.get('volunteer_id', None)}, "
# f"{data.get('requester_id', None)}, {data.get('request_id', None)}, {data['tokens']}, "
# f"'{data.get('is_complete', 'false')}', '{timestamp}', '{timestamp}') "
# "RETURNING transaction_id"
# )
# transaction_id = None
# cursor.execute(transaction_query)
# transaction_id = cursor.fetchone()[0]
#
# transaction_table_update = (
# "UPDATE token_transactions"
# f"SET request_id={request_id} WHERE transaction_id={transaction_id}"
# )
# cursor.execute(transaction_table_update)
conn.commit()
return utils.make_response("{error: None}", 200)
def check_acknowledgement():
"""
Check if the user has verified health for today.
Request: application/json
login_id int
ID of the user for whom the health is to be verified
"""
data = flask.request.get_json()
login_id = data["login_id"]
_, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
query = (
"SELECT COUNT(*) FROM health_history"
f"WHERE created_at::date = date {timestamp[:-9]} and login_id = {login_id}"
)
cursor.execute(query)
count = cursor.fetchone()[0]
if count:
return utils.make_response("{error: None, data: 'true'}", 200)
return utils.make_response("{error: None, data: 'true'}", 200)
def login():
"""
Login the user
Request: application/json
username str
Username of the user
password str
Password of the user
"""
data = request.get_json()
_, cursor = utils.get_database_connection()
query = f"SELECT username, password FROM login_details WHERE username='******'username']}'"
cursor.execute(query)
username, password = cursor.fetchone()
if username == None:
return utils.make_response("{error: User does not exist}", 404)
if generate_password_hash(data["password"]) == password:
user = User()
user.id = username
flask_login.login_user(user)
return utils.make_response("{error: None}", 200)
def fetch_volunteer_requests():
"""
Fetch all requests taken or completed by a volunteer.
Request: application/json
volunteer_id int
Volunteer ID for whom the requests are to be fetched
"""
data = flask.request.get_json()
_, cursor = utils.get_database_connection()
request_fetch_query = (
f"SELECT * FROM request_details WHERE volunteer_id={data['volunteer_id']}"
)
cursor.execute(request_fetch_query)
request_data = cursor.fetchall()
return utils.make_response(request_data, 200)
def fetch_all_requests():
"""
Fetch all requests submitted by a requester.
Request: application/json
requester_id int
Requester ID for whom the requests are to be fetched
"""
data = flask.request.get_json()
_, cursor = utils.get_database_connection()
request_fetch_query = (
f"SELECT * FROM request_details WHERE requester_id={data['requester_id']}"
)
cursor.execute(request_fetch_query)
request_data = cursor.fetchall()
return utils.make_response(request_data, 200)
def get_request_content():
"""
Fetch content of the request.
Request: application/json
request_id int
Request ID for which the content is to be fetched
"""
data = flask.request.get_json()
_, cursor = utils.get_database_connection()
request_fetch_query = (
f"SELECT * FROM request_details WHERE request_id={data['request_id']}"
)
cursor.execute(request_fetch_query)
request_data = cursor.fetchall()
return utils.make_response(request_data, 200)
def fetch_active_volunteer_request():
"""
Fetch all active requests of the voluteer.
Request: application/json
volunteer_id int
Volunteer ID for whom the active requests are to be fetched.
"""
data = flask.request.get_json()
_, cursor = utils.get_database_connection()
request_fetch_query = (
f"SELECT * FROM request_details WHERE volunteer_id={data['volunteer_id']} "
"AND is_completed=false"
)
cursor.execute(request_fetch_query)
request_data = cursor.fetchall()
return utils.make_response(request_data, 200)
def commenced_requests():
"""
Commence a request. This feature would be used by the volunteer.
Request: application/json
request_id int
Request ID which is to be commenced.
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
query = (
"UPDATE request_details "
f"SET is_commenced='true', updated_at='{timestamp}'"
f"WHERE request_id = {data['request_id']}"
)
cursor.execute(query)
conn.commit()
return utils.make_response("{error: None}", 200)
def get_requests():
"""
Given voluteer_id fetch list of requests that would be suitable for him to do.
This is done using special matching algorithm which would use FSA (first 3 digits of Postal Code) of the voluntter and requester to determine their proximity. Currently the algorithm
would only match the people having same FSA.
Request: application/json
volunteer_id int
Volunteer ID for whom the matching requests are to be found.
"""
volunteer_id = 5
_, cursor = utils.get_database_connection()
volunteer_postal_query = (
"SELECT b.postal_code FROM address_dir AS b INNER JOIN volunteer_details AS a ON a.login_id = b.login_id "
f"WHERE a.volunteer_id = {volunteer_id}"
)
cursor.execute(volunteer_postal_query)
postal_code = cursor.fetchone()[0][:3]
request_queries = (
f"""SELECT a.request_id ,a.request_type, TO_CHAR(CAST(a.request_date as date), 'YYYY-MM-DD') as request_date,
b.unit_no, b.street_number,
b.street_name, b.city, b.postal_code, e.tokens FROM request_details as a
INNER JOIN (
SELECT c.requester_id, d.unit_no, d.street_number, d.street_name, d.city,
d.postal_code FROM requester_details as c INNER JOIN address_dir as d
ON c.login_id = d.login_id
) as b ON a.requester_id = b.requester_id
LEFT JOIN token_transactions e ON a.transaction_id = e.transaction_id
WHERE LEFT(b.postal_code, 3) = '{postal_code}'"""
)
cursor.execute(request_queries)
columns = [column[0] for column in cursor.description]
requests_list = cursor.fetchall()
results = []
for row in requests_list:
results.append(dict(zip(columns, row)))
print(results)
return utils.make_response(results, 200)
def buy_token_request():
"""
Buy tokens for a given requester_id.
Request: application/json
requester_id int
Requester ID who is buying the tokens
tokens int
Number of tokens bought by the requester
"""
data = flask.request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
token_update_query = (
"UPDATE requester_details"
f"SET tokens = tokens+{data['tokens']}, updated_at='{timestamp}' "
f"WHERE requester_id={data['requester_id']}")
cursor.execute(token_update_query)
conn.commit()
return utils.make_response("{error: None}", 200)
def signup():
"""
Sign up the user.
Request: application/json
username str
Username of the user signing up. It has to be unique.
password str
Password of the user
unitNo int
Unit number of the user's address
streetNo int
Street number of the user's address
streetName str
Street name of the user's address
additional str
Additional infomration about user's address
city str
City of the user's address
postalCode str
Postal code of the user's address
name str
Name of the user
email str
Email of the user
phone str
Phone number of the user
age int
Age of the user
medicalCondition boolean
If the user has any medical condition.
"""
data = request.get_json()
conn, cursor = utils.get_database_connection()
timestamp = utils.get_utc_timestamp_now()
is_active = "true"
password = generate_password_hash(data["password"])
try:
login_query = (
"INSERT INTO login_details (username, password, is_active, created_at, updated_at)"
f"VALUES ('{data['username']}', '{password}', {is_active}, '{timestamp}', "
f"'{timestamp}') RETURNING login_id")
cursor.execute(login_query)
login_id = cursor.fetchone()[0]
except psycopg2.IntegrityError:
message = f"{{error: Username {data['username']} is taken}}"
return utils.make_response(message, 409)
address_query = (
"INSERT INTO address_dir (unit_no, street_number, street_name, additional_info, "
"city, province, postal_code, login_id, created_at, updated_at) VALUES ("
f"'{data.get('unitNo', 'NULL')}', '{data.get('streetNo', 'NULL')}',"
f"'{data['streetName']}', '{data.get('additional', 'NULL')}', '{data['city']}', "
f"'{data['province']}', '{data['postalCode']}', {login_id}, '{timestamp}', '{timestamp}')"
)
cursor.execute(address_query)
if data["category"] == "Volunteer":
query = (
"INSERT INTO volunteer_details (volunteer_name, volunteer_email, volunteer_phone,"
f"volunteer_age, login_id, created_at, updated_at) VALUES ('{data['name']}',"
f"'{data['email']}', {data['phone']}, {data['age']}, {login_id}, "
f"'{timestamp}', '{timestamp}')")
try:
cursor.execute(query)
except psycopg2.IntegrityError:
message = f"{{error: Email {data['email']} is already in the system}}"
return utils.make_response(message, 409)
else:
medical_condition = True if data["medicalCondition"] == "Yes" else False
query = (
"INSERT INTO requester_details (requester_name, requester_email, requester_phone, requester_age,"
f"has_medical_condition, login_id, created_at, updated_at) VALUES ('{data['name']}', "
f"'{data['email']}', '{data['phone']}', {data['age']}, {medical_condition}, "
f"{login_id}, '{timestamp}', '{timestamp}')")
try:
cursor.execute(query)
except psycopg2.IntegrityError:
message = f"{{error: Email {data['email']} is already in the system}}"
return utils.make_response(message, 409)
conn.commit()
return utils.make_response("{error: None}", 200)