def check_sanity():
"""
Email a messsage indicating the scheduler is still functioning correctly.
"""
with scheduler.app.app_context():
send_email(
subject="Scheduler Sanity Check",
to=[OPENRECORDS_DL_EMAIL],
email_content=
"You got this email, so the OpenRecords scheduler is running.")
def update_request_statuses():
with scheduler.app.app_context():
try:
_update_request_statuses()
except Exception:
sentry.captureException()
send_email(subject="Update Request Statuses Failure",
to=[OPENRECORDS_DL_EMAIL],
email_content=traceback.format_exc().replace(
"\n", "<br/>").replace(" ", " "))
def update_request_statuses():
try:
_update_request_statuses()
except Exception:
db.session.rollback()
send_email(
subject="Update Request Statuses Failure",
to=[OPENRECORDS_DL_EMAIL],
email_content=traceback.format_exc().replace("\n", "<br/>").replace(" ", " ")
)
def update_request_statuses():
"""
Update statuses for all requests that are now Due Soon or Overdue
and send a notification email to agency admins listing the requests.
"""
with scheduler.app.app_context():
now = datetime.utcnow()
due_soon_date = calendar.addbusdays(
now, current_app.config['DUE_SOON_DAYS_THRESHOLD']).replace(
hour=23, minute=59, second=59) # the entire day
requests_overdue = Requests.query.filter(
Requests.due_date < now,
Requests.status != request_status.CLOSED).order_by(
Requests.due_date.asc()).all()
requests_due_soon = Requests.query.filter(
Requests.due_date > now, Requests.due_date <= due_soon_date,
Requests.status != request_status.CLOSED).order_by(
Requests.due_date.asc()).all()
agencies_to_requests_overdue = {}
agencies_to_acknowledgments_overdue = {}
agencies_to_requests_due_soon = {}
agencies_to_acknowledgments_due_soon = {}
def add_to_agencies_to_request_dict(req, agencies_to_request_dict):
if req.agency.ein not in agencies_to_request_dict:
agencies_to_request_dict[req.agency.ein] = [req]
else:
agencies_to_request_dict[req.agency.ein].append(req)
# OVERDUE
for request in requests_overdue:
if request.was_acknowledged:
add_to_agencies_to_request_dict(request,
agencies_to_requests_overdue)
else:
add_to_agencies_to_request_dict(
request, agencies_to_acknowledgments_overdue)
if request.status != request_status.OVERDUE:
update_object({"status": request_status.OVERDUE}, Requests,
request.id)
# DUE SOON
for request in requests_due_soon:
if request.was_acknowledged:
add_to_agencies_to_request_dict(request,
agencies_to_requests_due_soon)
else:
add_to_agencies_to_request_dict(
request, agencies_to_acknowledgments_due_soon)
if request.status != request_status.DUE_SOON:
update_object({"status": request_status.DUE_SOON}, Requests,
request.id)
# get all possible agencies to email
agency_eins = set(
list(agencies_to_requests_overdue) +
list(agencies_to_acknowledgments_overdue) +
list(agencies_to_requests_due_soon) +
list(agencies_to_acknowledgments_due_soon))
# mail to agency admins for each agency
for agency_ein in agency_eins:
agency_requests_overdue = agencies_to_requests_overdue.get(
agency_ein, [])
agency_acknowledgments_overdue = agencies_to_acknowledgments_overdue.get(
agency_ein, [])
agency_requests_due_soon = agencies_to_requests_due_soon.get(
agency_ein, [])
agency_acknowledgments_due_soon = agencies_to_acknowledgments_due_soon.get(
agency_ein, [])
user_emails = list(
set(admin.notification_email or admin.email
for admin in Agencies.query.filter_by(
ein=agency_ein).one().administrators))
send_email(
STATUSES_EMAIL_SUBJECT,
to=user_emails,
template=STATUSES_EMAIL_TEMPLATE,
requests_overdue=agency_requests_overdue,
acknowledgments_overdue=agency_acknowledgments_overdue,
requests_due_soon=agency_requests_due_soon,
acknowledgments_due_soon=agency_acknowledgments_due_soon)
email = Emails(
request.id,
PRIVATE,
to=','.join(user_emails),
cc=None,
bcc=None,
subject=STATUSES_EMAIL_SUBJECT,
body=render_template(
STATUSES_EMAIL_TEMPLATE + ".html",
requests_overdue=agency_requests_overdue,
acknowledgments_overdue=agency_acknowledgments_overdue,
requests_due_soon=agency_requests_due_soon,
acknowledgments_due_soon=agency_acknowledgments_due_soon))
create_object(email)
create_object(
Events(request.id,
user_guid=None,
auth_user_type=None,
type_=EMAIL_NOTIFICATION_SENT,
previous_value=None,
new_value=email.val_for_events,
response_id=None,
timestamp=datetime.utcnow()))
def generate_monthly_metrics_report(self, agency_ein: str, date_from: str,
date_to: str, email_to: list):
"""Generates a report of monthly metrics about opened and closed requests.
Generates a report of requests in a time frame with the following tabs:
1) Metrics:
- Received for the current month
- Total remaining open for current month
- Closed in the current month that were received in the current month
- Total closed in current month no matter when received
- Total closed since portal started
- Total remaining Open/Pending
- Inquiries for current month
2) All requests that have been opened in the given month.
3) All requests that are still open or pending. Excludes all closed requests.
4) All requests that have been closed in the given month.
5) All requests that have been closed, since the portal started.
6) All requests that have been opened and closed in the same given month.
7) all emails received using the "Contact the Agency" button in the given month.
Args:
agency_ein: Agency EIN
date_from: Date to filter from
date_to: Date to filter to
email_to: List of recipient emails
"""
# Convert string dates
date_from_utc = local_to_utc(datetime.strptime(date_from, '%Y-%m-%d'),
current_app.config['APP_TIMEZONE'])
date_to_utc = local_to_utc(
datetime.strptime(date_to, '%Y-%m-%d'),
current_app.config['APP_TIMEZONE']) + timedelta(days=1)
# Query for all requests that have been opened in the given month
opened_in_month = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
).filter(
Requests.date_created.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
).order_by(asc(Requests.date_created)).all()
opened_in_month_headers = ('Request ID', 'Status', 'Date Created',
'Due Date')
opened_in_month_dataset = tablib.Dataset(*opened_in_month,
headers=opened_in_month_headers,
title='Opened in month')
# Query for all requests that are still open or pending. Excludes all closed requests.
remaining_open_or_pending = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
).filter(Requests.agency_ein == agency_ein,
Requests.status.in_([OPEN, IN_PROGRESS, DUE_SOON,
OVERDUE])).order_by(
asc(Requests.date_created)).all()
remaining_open_or_pending_headers = ('Request ID', 'Status',
'Date Created', 'Due Date')
remaining_open_or_pending_dataset = tablib.Dataset(
*remaining_open_or_pending,
headers=remaining_open_or_pending_headers,
title='All remaining Open or Pending')
# Query for all requests that have been closed in the given month.
closed_in_month = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.date_closed, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
).filter(
Requests.date_closed.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
Requests.status == CLOSED,
).order_by(asc(Requests.date_created)).all()
closed_in_month_headers = ('Request ID', 'Status', 'Date Created',
'Date Closed', 'Due Date')
closed_in_month_dataset = tablib.Dataset(*closed_in_month,
headers=closed_in_month_headers,
title='Closed in month')
# Query for all requests that have been closed, since the portal started.
total_closed = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.date_closed, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
).filter(
Requests.agency_ein == agency_ein,
Requests.status == CLOSED,
).order_by(asc(Requests.date_created)).all()
total_closed_headers = ('Request ID', 'Status', 'Date Created',
'Date Closed', 'Due Date')
total_closed_dataset = tablib.Dataset(*total_closed,
headers=total_closed_headers,
title='All Closed requests')
# Query for all requests that have been opened and closed in the same given month.
opened_closed_in_month = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
).filter(
Requests.date_created.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
Requests.status == CLOSED,
).order_by(asc(Requests.date_created)).all()
opened_closed_in_month_headers = ('Request ID', 'Status', 'Date Created',
'Due Date')
opened_closed_in_month_dataset = tablib.Dataset(
*opened_closed_in_month,
headers=opened_closed_in_month_headers,
title='Opened then Closed in month')
# Query for all emails received using the "Contact the Agency" button in the given month.
contact_agency_emails = Requests.query.with_entities(
Requests.id, func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Responses.date_modified, 'MM/DD/YYYY'),
Emails.subject).join(
Responses, Responses.request_id == Requests.id).join(
Emails, Emails.id == Responses.id).filter(
Responses.date_modified.between(date_from_utc,
date_to_utc),
Requests.agency_ein == agency_ein,
Emails.subject.ilike('Inquiry about FOIL%')).order_by(
asc(Responses.date_modified)).all()
contact_agency_emails_headers = ('Request ID', 'Date Created', 'Date Sent',
'Subject')
contact_agency_emails_dataset = tablib.Dataset(
*contact_agency_emails,
headers=contact_agency_emails_headers,
title='Contact agency emails received')
# Metrics tab
received_current_month = len(opened_in_month)
total_opened_closed_in_month = len(opened_closed_in_month)
remaining_open_current_month = received_current_month - total_opened_closed_in_month
metrics = [
('Received for the current month', received_current_month),
('Total remaining open from current month',
remaining_open_current_month),
('Closed in the current month that were received in the current month',
total_opened_closed_in_month),
('Total closed in current month no matter when received',
len(closed_in_month)),
('Total closed since portal started', len(total_closed)),
('Total remaining Open/Pending', len(remaining_open_or_pending)),
('Inquiries for current month', len(contact_agency_emails))
]
metrics_headers = ['Metric', 'Count']
metrics_dataset = tablib.Dataset(*metrics,
headers=metrics_headers,
title='Metrics')
# Create Databook from Datasets
excel_spreadsheet = tablib.Databook(
(metrics_dataset, opened_in_month_dataset,
remaining_open_or_pending_dataset, closed_in_month_dataset,
total_closed_dataset, opened_closed_in_month_dataset,
contact_agency_emails_dataset))
# Email report
send_email(subject='OpenRecords Monthly Metrics Report',
to=email_to,
email_content='Report attached',
attachment=excel_spreadsheet.export('xls'),
filename='FOIL_monthly_metrics_report_{}_{}.xls'.format(
date_from, date_to),
mimetype='application/octet-stream')
def generate_acknowledgment_report(self, current_user_guid: str,
date_from: datetime, date_to: datetime):
"""Celery task that generates the acknowledgment report for the user's agency with the specified date range.
Args:
current_user_guid: GUID of the current user
date_from: Date to filter report from
date_to: Date to filter report to
"""
current_user = Users.query.filter_by(guid=current_user_guid).one()
agency_ein = current_user.default_agency_ein
agency = Agencies.query.options(joinedload(Agencies.active_users)).options(
joinedload(
Agencies.inactive_users)).filter(Agencies.ein == agency_ein).one()
agency_users = agency.active_users + agency.inactive_users
request_list = Requests.query.join(
Events, Events.request_id == Requests.id).options(
joinedload(Requests.requester)).add_columns(
Events.user_guid, Events.type).filter(
Requests.agency_ein == agency_ein,
Requests.status != CLOSED,
Events.request_id == Requests.id,
Events.type.in_(
(REQ_ACKNOWLEDGED,
REQ_CREATED))).order_by(asc(Requests.id)).all()
headers = ('Request ID', 'Acknowledged', 'Acknowledged By', 'Date Created',
'Due Date', 'Status', 'Title', 'Description', 'Requester Name',
'Email', 'Phone Number', 'Address 1', 'Address 2', 'City',
'State', 'Zipcode')
data_from_dates = []
all_data = []
acknowledged_requests = {
res.Requests.id: {
"request": res.Requests,
"user_guid": res.user_guid
}
for res in filter(lambda x: x.type == REQ_ACKNOWLEDGED, request_list)
}
unacknowledged_requests = {
res.Requests.id: {
"request": res.Requests
}
for res in filter(
lambda x: x.type == REQ_CREATED and x.Requests.id not in
acknowledged_requests.keys(), request_list)
}
request_id_list = list(acknowledged_requests.keys()) + list(
unacknowledged_requests.keys())
for request_id in sorted(request_id_list):
ack_user = ''
was_acknowledged = False
if acknowledged_requests.get(request_id, None):
ack_user = [
user for user in agency_users
if user.guid == acknowledged_requests[request_id]["user_guid"]
]
ack_user = ack_user[0].name if ack_user else ''
was_acknowledged = True
request = acknowledged_requests.get(request_id)['request']
else:
request = unacknowledged_requests.get(request_id)['request']
req_date_created_local = utc_to_local(
request.date_created, current_app.config['APP_TIMEZONE'])
if date_from < req_date_created_local < date_to:
data_from_dates.append((
request.id,
was_acknowledged,
ack_user,
req_date_created_local.strftime('%m/%d/%Y'),
utc_to_local(
request.due_date,
current_app.config['APP_TIMEZONE']).strftime('%m/%d/%Y'),
request.status,
request.title,
request.description,
request.requester.name,
request.requester.email,
request.requester.phone_number,
request.requester.mailing_address.get('address_one'),
request.requester.mailing_address.get('address_two'),
request.requester.mailing_address.get('city'),
request.requester.mailing_address.get('state'),
request.requester.mailing_address.get('zip'),
))
all_data.append((
request.id,
was_acknowledged,
ack_user,
req_date_created_local.strftime('%m/%d/%Y'),
utc_to_local(
request.due_date,
current_app.config['APP_TIMEZONE']).strftime('%m/%d/%Y'),
request.status,
request.title,
request.description,
request.requester.name,
request.requester.email,
request.requester.phone_number,
request.requester.mailing_address.get('address_one'),
request.requester.mailing_address.get('address_two'),
request.requester.mailing_address.get('city'),
request.requester.mailing_address.get('state'),
request.requester.mailing_address.get('zip'),
))
date_from_string = date_from.strftime('%Y%m%d')
date_to_string = date_to.strftime('%Y%m%d')
dates_dataset = tablib.Dataset(*data_from_dates,
headers=headers,
title='{}_{}'.format(
date_from_string, date_to_string))
all_dataset = tablib.Dataset(*all_data, headers=headers, title='all')
excel_spreadsheet = tablib.Databook((dates_dataset, all_dataset))
send_email(subject='OpenRecords Acknowledgment Report',
to=[current_user.email],
template='email_templates/email_agency_report_generated',
agency_user=current_user.name,
attachment=excel_spreadsheet.export('xls'),
filename='FOIL_acknowledgments_{}_{}.xls'.format(
date_from_string, date_to_string),
mimetype='application/octect-stream')
def generate_request_closing_user_report(agency_ein: str, date_from: str,
date_to: str, email_to: list):
"""Generates a report of requests that were closed in a time frame.
Generates a report of requests in a time frame with the following tabs:
1) Total number of opened and closed requests.
2) Total number of closed requests and percentage closed by user.
3) Total number of requests closed by user per day.
4) All of the requests created.
5) All of the requests closed.
6) All of the requests closed and the user who closed it.
Args:
agency_ein: Agency EIN
date_from: Date to filter from
date_to: Date to filter to
email_to: List of recipient emails
"""
# Convert string dates
date_from_utc = local_to_utc(datetime.strptime(date_from, '%Y-%m-%d'),
current_app.config['APP_TIMEZONE'])
date_to_utc = local_to_utc(datetime.strptime(date_to, '%Y-%m-%d'),
current_app.config['APP_TIMEZONE'])
# Query for all requests opened and create Dataset
total_opened = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
).filter(
Requests.date_created.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
).order_by(asc(Requests.date_created)).all()
total_opened_headers = ('Request ID', 'Status', 'Date Created', 'Due Date')
total_opened_dataset = tablib.Dataset(*total_opened,
headers=total_opened_headers,
title='opened in month Raw Data')
# Query for all requests closed and create Dataset
total_closed = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.date_closed, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
).filter(
Requests.date_closed.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
Requests.status == CLOSED,
).order_by(asc(Requests.date_created)).all()
total_closed_headers = ('Request ID', 'Status', 'Date Created',
'Date Closed', 'Due Date')
total_closed_dataset = tablib.Dataset(*total_closed,
headers=total_closed_headers,
title='closed in month Raw Data')
# Get total number of opened and closed requests and create Dataset
monthly_totals = [[OPEN, len(total_opened)], [CLOSED,
len(total_closed)],
['Total', len(total_opened) + len(total_closed)]]
monthly_totals_headers = ('Status', 'Count')
monthly_totals_dataset = tablib.Dataset(*monthly_totals,
headers=monthly_totals_headers,
title='Monthly Totals')
# Query for all requests closed with user who closed and create Dataset
person_month = Requests.query.with_entities(
Requests.id,
Requests.status,
func.to_char(Requests.date_created, 'MM/DD/YYYY'),
func.to_char(Requests.due_date, 'MM/DD/YYYY'),
func.to_char(Events.timestamp, 'MM/DD/YYYY HH:MI:SS.MS'),
Users.fullname,
).distinct().join(
Events,
Users,
).filter(
Events.timestamp.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
Events.type.in_((REQ_CLOSED, REQ_DENIED)),
Requests.status == CLOSED,
Requests.id == Events.request_id,
Events.user_guid == Users.guid,
).order_by(asc(Requests.id)).all()
person_month_list = [list(r) for r in person_month]
for person_month_item in person_month_list:
person_month_item[4] = person_month_item[4].split(' ', 1)[0]
person_month_headers = ('Request ID', 'Status', 'Date Created', 'Due Date',
'Timestamp', 'Closed By')
person_month_dataset = tablib.Dataset(
*person_month_list,
headers=person_month_headers,
title='month closed by person Raw Data')
# Query for count of requests closed by user
person_month_count = Users.query.with_entities(
Users.fullname,
func.count('*'),
).distinct().join(Events, Requests).filter(
Events.timestamp.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
Events.type.in_((REQ_CLOSED, REQ_DENIED)),
Requests.status == CLOSED,
Requests.id == Events.request_id,
Events.user_guid == Users.guid,
).group_by(Users.fullname).all()
# Convert query result (tuple) into list
person_month_count_list = [list(r) for r in person_month_count]
# Calculate percentage of requests closed by user over total
for person_month_count_item in person_month_count_list:
person_month_count_item.append("{:.0%}".format(
person_month_count_item[1] / len(person_month)))
person_month_percent_headers = ('Closed By', 'Count', 'Percent')
person_month_closing_percent_dataset = tablib.Dataset(
*person_month_count_list,
headers=person_month_percent_headers,
title='Monthly Closing by Person')
# Query for count of requests closed per day by user and create Dataset
person_day = Requests.query.with_entities(
func.to_char(Events.timestamp.cast(Date), 'MM/DD/YYYY'),
Users.fullname, func.count('*')).join(Users).filter(
Events.timestamp.between(date_from_utc, date_to_utc),
Requests.agency_ein == agency_ein,
Events.type.in_((REQ_CLOSED, REQ_DENIED)),
Requests.status == CLOSED,
Requests.id == Events.request_id,
Events.user_guid == Users.guid,
).group_by(
Events.timestamp.cast(Date),
Users.fullname,
).order_by(Events.timestamp.cast(Date)).all()
person_day_headers = ('Date', 'Closed By', 'Count')
person_day_dataset = tablib.Dataset(*person_day,
headers=person_day_headers,
title='day closed by person Raw Data')
# Create Databook from Datasets
excel_spreadsheet = tablib.Databook(
(monthly_totals_dataset, person_month_closing_percent_dataset,
person_day_dataset, total_opened_dataset, total_closed_dataset,
person_month_dataset))
# Email report
send_email(subject='OpenRecords User Closing Report',
to=email_to,
email_content='Report attached',
attachment=excel_spreadsheet.export('xls'),
filename='FOIL_user_closing_{}_{}.xls'.format(
date_from, date_to),
mimetype='application/octet-stream')
def _update_request_statuses():
"""
Update statuses for all requests that are now Due Soon or Overdue
and send a notification email to agency admins listing the requests.
"""
now = datetime.utcnow()
due_soon_date = calendar.addbusdays(
now, current_app.config['DUE_SOON_DAYS_THRESHOLD']).replace(
hour=23, minute=59, second=59) # the entire day
agencies = Agencies.query.with_entities(
Agencies.ein).filter_by(is_active=True).all()
for agency_ein, in agencies:
requests_overdue = Requests.query.filter(
Requests.due_date < now, Requests.status != request_status.CLOSED,
Requests.agency_ein == agency_ein).order_by(
Requests.due_date.asc()).all()
requests_due_soon = Requests.query.filter(
Requests.due_date > now, Requests.due_date <= due_soon_date,
Requests.status != request_status.CLOSED,
Requests.agency_ein == agency_ein).order_by(
Requests.due_date.asc()).all()
if not requests_overdue and not requests_due_soon:
continue
agency_requests_overdue = []
agency_acknowledgments_overdue = []
agency_requests_due_soon = []
agency_acknowledgments_due_soon = []
# OVERDUE
for request in requests_overdue:
if request.was_acknowledged:
agency_requests_overdue.append(request)
else:
agency_acknowledgments_overdue.append(request)
if request.status != request_status.OVERDUE:
create_object(
Events(
request.id,
user_guid=None,
auth_user_type=None,
type_=REQ_STATUS_CHANGED,
previous_value={"status": request.status},
new_value={"status": request_status.OVERDUE},
response_id=None,
))
update_object({"status": request_status.OVERDUE}, Requests,
request.id)
# DUE SOON
for request in requests_due_soon:
if request.was_acknowledged:
agency_requests_due_soon.append(request)
else:
agency_acknowledgments_due_soon.append(request)
if request.status != request_status.DUE_SOON:
create_object(
Events(
request.id,
user_guid=None,
auth_user_type=None,
type_=REQ_STATUS_CHANGED,
previous_value={"status": request.status},
new_value={"status": request_status.DUE_SOON},
response_id=None,
))
update_object({"status": request_status.DUE_SOON}, Requests,
request.id)
# mail to agency admins for each agency
user_emails = list(
set(admin.notification_email or admin.email
for admin in Agencies.query.filter_by(
ein=agency_ein).one().administrators))
send_email(STATUSES_EMAIL_SUBJECT,
to=user_emails,
template=STATUSES_EMAIL_TEMPLATE,
requests_overdue=agency_requests_overdue,
acknowledgments_overdue=agency_acknowledgments_overdue,
requests_due_soon=agency_requests_due_soon,
acknowledgments_due_soon=agency_acknowledgments_due_soon)
email = Emails(
request.id,
PRIVATE,
to=','.join(user_emails),
cc=None,
bcc=None,
subject=STATUSES_EMAIL_SUBJECT,
body=render_template(
STATUSES_EMAIL_TEMPLATE + ".html",
requests_overdue=agency_requests_overdue,
acknowledgments_overdue=agency_acknowledgments_overdue,
requests_due_soon=agency_requests_due_soon,
acknowledgments_due_soon=agency_acknowledgments_due_soon))
create_object(email)
create_object(
Events(request.id,
user_guid=None,
auth_user_type=None,
type_=EMAIL_NOTIFICATION_SENT,
previous_value=None,
new_value=email.val_for_events,
response_id=None,
timestamp=datetime.utcnow()))
def _update_request_statuses():
"""
Update statuses for all requests that are now Due Soon or Overdue
and send a notification email to agency admins listing the requests.
"""
now = datetime.utcnow()
due_soon_date = calendar.addbusdays(
now, current_app.config['DUE_SOON_DAYS_THRESHOLD']
).replace(hour=23, minute=59, second=59) # the entire day
agencies = Agencies.query.with_entities(Agencies.ein).filter_by(is_active=True).all()
for agency_ein, in agencies:
requests_overdue = Requests.query.filter(
Requests.due_date < now,
Requests.status != request_status.CLOSED,
Requests.agency_ein == agency_ein
).order_by(
Requests.due_date.asc()
).all()
requests_due_soon = Requests.query.filter(
Requests.due_date > now,
Requests.due_date <= due_soon_date,
Requests.status != request_status.CLOSED,
Requests.agency_ein == agency_ein
).order_by(
Requests.due_date.asc()
).all()
if not requests_overdue and not requests_due_soon:
continue
agency_requests_overdue = []
agency_acknowledgments_overdue = []
agency_requests_due_soon = []
agency_acknowledgments_due_soon = []
# OVERDUE
for request in requests_overdue:
if request.was_acknowledged:
agency_requests_overdue.append(request)
else:
agency_acknowledgments_overdue.append(request)
if request.status != request_status.OVERDUE:
create_object(
Events(
request.id,
user_guid=None,
type_=REQ_STATUS_CHANGED,
previous_value={"status": request.status},
new_value={"status": request_status.OVERDUE},
response_id=None,
)
)
update_object(
{"status": request_status.OVERDUE},
Requests,
request.id)
# DUE SOON
for request in requests_due_soon:
if request.was_acknowledged:
agency_requests_due_soon.append(request)
else:
agency_acknowledgments_due_soon.append(request)
if request.status != request_status.DUE_SOON:
create_object(
Events(
request.id,
user_guid=None,
type_=REQ_STATUS_CHANGED,
previous_value={"status": request.status},
new_value={"status": request_status.DUE_SOON},
response_id=None,
)
)
update_object(
{"status": request_status.DUE_SOON},
Requests,
request.id)
# mail to agency admins for each agency
user_emails = list(set(admin.notification_email or admin.email for admin
in Agencies.query.filter_by(ein=agency_ein).one().administrators))
send_email(
STATUSES_EMAIL_SUBJECT,
to=user_emails,
template=STATUSES_EMAIL_TEMPLATE,
requests_overdue=agency_requests_overdue,
acknowledgments_overdue=agency_acknowledgments_overdue,
requests_due_soon=agency_requests_due_soon,
acknowledgments_due_soon=agency_acknowledgments_due_soon
)
email = Emails(
request.id,
PRIVATE,
to=','.join(user_emails),
cc=None,
bcc=None,
subject=STATUSES_EMAIL_SUBJECT,
body=render_template(
STATUSES_EMAIL_TEMPLATE + ".html",
requests_overdue=agency_requests_overdue,
acknowledgments_overdue=agency_acknowledgments_overdue,
requests_due_soon=agency_requests_due_soon,
acknowledgments_due_soon=agency_acknowledgments_due_soon
)
)
create_object(email)
create_object(
Events(
request.id,
user_guid=None,
type_=EMAIL_NOTIFICATION_SENT,
previous_value=None,
new_value=email.val_for_events,
response_id=None,
timestamp=datetime.utcnow()
)
)
def make_user_admin(self, modified_user_guid: str, current_user_guid: str,
agency_ein: str):
"""
Make the specified user an admin for the agency.
Args:
modified_user_guid (str): GUID of the user to be modified
current_user_guid (str): GUID of the current user
agency_ein (str): Agency the user is being added to
Returns:
"""
permissions = Roles.query.filter_by(
name=role_name.AGENCY_ADMIN).one().permissions
user = Users.query.filter_by(guid=modified_user_guid).one()
requests = [
request.id
for request in user.agencies.filter_by(ein=agency_ein).one().requests
]
new_user_requests = []
new_user_requests_events = []
update_user_requests = []
update_user_requests_events = []
for request in requests:
existing_value = UserRequests.query.filter_by(
request_id=request, user_guid=user.guid).one_or_none()
if existing_value and existing_value.permissions != permissions:
user_request = bulk_updates.UserRequestsDict(
user_guid=user.guid,
request_id=request,
request_user_type=user_type_request.AGENCY,
permissions=permissions,
point_of_contact=existing_value.point_of_contact)
update_user_requests.append(user_request)
previous_value = {
'user_guid': modified_user_guid,
'permissions': existing_value.permissions
}
new_value = {
'user_guid': modified_user_guid,
'permissions': permissions
}
user_request_event = bulk_updates.UserRequestsEventDict(
request_id=request,
user_guid=user.guid,
response_id=None,
type=event_type.USER_PERM_CHANGED,
timestamp=datetime.utcnow(),
previous_value=previous_value,
new_value=new_value,
)
update_user_requests_events.append(user_request_event)
elif existing_value is None:
user_request = bulk_updates.UserRequestsDict(
user_guid=user.guid,
request_id=request,
request_user_type=user_type_request.AGENCY,
permissions=permissions,
point_of_contact=None)
new_user_requests.append(user_request)
new_value = {
'user_guid': user.guid,
'request_id': request,
'request_user_type': user_type_request.AGENCY,
'permissions': permissions,
'point_of_contact': None
}
user_request_event = bulk_updates.UserRequestsEventDict(
request_id=request,
user_guid=current_user_guid,
response_id=None,
type=event_type.USER_ADDED,
timestamp=datetime.utcnow(),
previous_value=None,
new_value=new_value)
new_user_requests_events.append(user_request_event)
try:
UserRequests.query.filter(UserRequests.user_guid == user.guid).update([
('permissions', permissions)
])
db.session.bulk_insert_mappings(Events, update_user_requests_events)
db.session.bulk_insert_mappings(UserRequests, new_user_requests)
db.session.bulk_insert_mappings(Events, new_user_requests_events)
db.session.commit()
agency = Agencies.query.filter_by(ein=agency_ein).one()
admin_users = get_agency_admin_emails(agency)
es_update_assigned_users.apply_async(args=[requests])
send_email(subject='User {name} Made Admin'.format(name=user.name),
to=admin_users,
template='email_templates/email_user_made_agency_admin',
agency_name=agency.name,
name=user.name)
except SQLAlchemyError:
db.session.rollback()
def remove_user_permissions(self,
modified_user_guid: str,
current_user_guid: str,
agency_ein: str,
action: str = None):
"""
Remove the specified users permissions for the agency identified by agency_ein
Args:
modified_user_guid (str): GUID of the user to be modified
current_user_guid (str): GUID of the current user
agency_ein (str): Agency the user is being removed from
Returns:
"""
user_requests = db.session.query(
UserRequests, Requests).join(Requests).with_entities(
UserRequests.request_id, UserRequests.permissions,
UserRequests.point_of_contact).filter(
Requests.agency_ein == agency_ein,
UserRequests.user_guid == modified_user_guid).all()
request_ids = [ur.request_id for ur in user_requests]
user = Users.query.filter_by(guid=modified_user_guid).one()
remove_user_request_events = [
bulk_updates.UserRequestsEventDict(request_id=ur.request_id,
user_guid=current_user_guid,
response_id=None,
type=event_type.USER_REMOVED,
timestamp=datetime.utcnow(),
previous_value={
'user_guid':
modified_user_guid,
'permissions':
ur.permissions,
'point_of_contact':
ur.point_of_contact
},
new_value={
'user_guid': modified_user_guid,
'point_of_contact': False
}) for ur in user_requests
]
try:
db.session.query(UserRequests).filter(
UserRequests.user_guid == modified_user_guid,
UserRequests.request_id.in_(request_ids)).delete(
synchronize_session=False)
db.session.bulk_insert_mappings(Events, remove_user_request_events)
db.session.commit()
es_update_assigned_users.apply_async(args=[request_ids])
agency = Agencies.query.filter_by(ein=agency_ein).one()
admin_users = get_agency_admin_emails(agency)
if action == event_type.AGENCY_USER_DEACTIVATED:
send_email(
subject='User {name} Deactivated'.format(name=user.name),
to=admin_users,
template='email_templates/email_agency_user_deactivated',
agency_name=agency.name,
name=user.name)
elif action == event_type.USER_MADE_AGENCY_USER:
send_email(
subject='User {name} Made Regular User'.format(name=user.name),
to=admin_users,
template='email_templates/email_user_removed_agency_admin',
agency_name=agency.name,
name=user.name)
except SQLAlchemyError:
db.session.rollback()
def make_user_admin(self, modified_user_guid: str, current_user_guid: str, agency_ein: str):
"""
Make the specified user an admin for the agency.
Args:
modified_user_guid (str): GUID of the user to be modified
current_user_guid (str): GUID of the current user
agency_ein (str): Agency the user is being added to
Returns:
"""
permissions = Roles.query.filter_by(name=role_name.AGENCY_ADMIN).one().permissions
user = Users.query.filter_by(guid=modified_user_guid).one()
requests = [request.id for request in user.agencies.filter_by(ein=agency_ein).one().requests]
new_user_requests = []
new_user_requests_events = []
update_user_requests = []
update_user_requests_events = []
for request in requests:
existing_value = UserRequests.query.filter_by(request_id=request, user_guid=user.guid).one_or_none()
if existing_value and existing_value.permissions != permissions:
user_request = bulk_updates.UserRequestsDict(
user_guid=user.guid,
request_id=request,
request_user_type=user_type_request.AGENCY,
permissions=permissions,
point_of_contact=existing_value.point_of_contact
)
update_user_requests.append(user_request)
previous_value = {
'user_guid': modified_user_guid,
'permissions': existing_value.permissions
}
new_value = {
'user_guid': modified_user_guid,
'permissions': permissions
}
user_request_event = bulk_updates.UserRequestsEventDict(
request_id=request,
user_guid=user.guid,
response_id=None,
type=event_type.USER_PERM_CHANGED,
timestamp=datetime.utcnow(),
previous_value=previous_value,
new_value=new_value,
)
update_user_requests_events.append(user_request_event)
elif existing_value is None:
user_request = bulk_updates.UserRequestsDict(
user_guid=user.guid,
request_id=request,
request_user_type=user_type_request.AGENCY,
permissions=permissions,
point_of_contact=None
)
new_user_requests.append(user_request)
new_value = {
'user_guid': user.guid,
'request_id': request,
'request_user_type': user_type_request.AGENCY,
'permissions': permissions,
'point_of_contact': None
}
user_request_event = bulk_updates.UserRequestsEventDict(
request_id=request,
user_guid=current_user_guid,
response_id=None,
type=event_type.USER_ADDED,
timestamp=datetime.utcnow(),
previous_value=None,
new_value=new_value
)
new_user_requests_events.append(user_request_event)
try:
UserRequests.query.filter(UserRequests.user_guid == user.guid).update([('permissions', permissions)])
db.session.bulk_insert_mappings(Events, update_user_requests_events)
db.session.bulk_insert_mappings(UserRequests, new_user_requests)
db.session.bulk_insert_mappings(Events, new_user_requests_events)
db.session.commit()
agency = Agencies.query.filter_by(ein=agency_ein).one()
admin_users = list(
set(user.notification_email if user.notification_email is not None else user.email for user in
agency.administrators))
es_update_assigned_users.apply_async(args=[requests])
send_email(
subject='User {name} Made Admin'.format(name=user.name),
to=admin_users,
template='email_templates/email_user_made_agency_admin',
agency_name=agency.name,
name=user.name
)
except SQLAlchemyError:
db.session.rollback()
def remove_user_permissions(self, modified_user_guid: str, current_user_guid: str, agency_ein: str, action: str = None):
"""
Remove the specified users permissions for the agency identified by agency_ein
Args:
modified_user_guid (str): GUID of the user to be modified
current_user_guid (str): GUID of the current user
agency_ein (str): Agency the user is being removed from
Returns:
"""
user_requests = db.session.query(UserRequests, Requests).join(Requests).with_entities(
UserRequests.request_id, UserRequests.permissions, UserRequests.point_of_contact).filter(
Requests.agency_ein == agency_ein, UserRequests.user_guid == modified_user_guid).all()
request_ids = [ur.request_id for ur in user_requests]
user = Users.query.filter_by(guid=modified_user_guid).one()
remove_user_request_events = [bulk_updates.UserRequestsEventDict(
request_id=ur.request_id,
user_guid=current_user_guid,
response_id=None,
type=event_type.USER_REMOVED,
timestamp=datetime.utcnow(),
previous_value={
'user_guid': modified_user_guid,
'permissions': ur.permissions,
'point_of_contact': ur.point_of_contact
},
new_value={
'user_guid': modified_user_guid,
'point_of_contact': False
}
) for ur in user_requests]
try:
db.session.query(UserRequests).filter(UserRequests.user_guid == modified_user_guid,
UserRequests.request_id.in_(request_ids)).delete(
synchronize_session=False)
db.session.bulk_insert_mappings(Events, remove_user_request_events)
db.session.commit()
es_update_assigned_users.apply_async(args=[request_ids])
agency = Agencies.query.filter_by(ein=agency_ein).one()
admin_users = list(
set(user.notification_email if user.notification_email is not None else user.email for user in
agency.administrators)
)
if action == event_type.AGENCY_USER_DEACTIVATED:
send_email(
subject='User {name} Deactivated'.format(name=user.name),
to=admin_users,
template='email_templates/email_agency_user_deactivated',
agency_name=agency.name,
name=user.name
)
elif action == event_type.USER_MADE_AGENCY_USER:
send_email(
subject='User {name} Made Regular User'.format(name=user.name),
to=admin_users,
template='email_templates/email_user_removed_agency_admin',
agency_name=agency.name,
name=user.name
)
except SQLAlchemyError:
db.session.rollback()
def add_user():
"""Adds a user to the users and agency_users tables.
Returns:
Template with context.
"""
form = AddAgencyUserForm()
if form.validate_on_submit():
agency_ein = form.agency.data
first_name = form.first_name.data
last_name = form.last_name.data
email = form.email.data
user = Users.query.filter(
func.lower(Users.email) == email.lower(),
Users.is_nyc_employee == True).first()
if user is not None:
flash('{} {} has already been added.'.format(
first_name, last_name),
category='warning')
else:
new_user = Users(
guid=generate_guid(),
first_name=first_name,
last_name=last_name,
email=email,
email_validated=False,
is_nyc_employee=True,
is_anonymous_requester=False,
)
create_object(new_user)
agency_user = AgencyUsers(user_guid=new_user.guid,
agency_ein=agency_ein,
is_agency_active=False,
is_agency_admin=False,
is_primary_agency=True)
create_object(agency_user)
agency = Agencies.query.filter_by(ein=agency_ein).one()
admin_emails = get_agency_admin_emails(agency)
send_email(
subject='User {} Added'.format(new_user.fullname),
to=admin_emails,
template='email_templates/email_agency_user_added',
agency_name=agency.name,
name=new_user.fullname,
)
content_id = 'login_screenshot'
image = {
'path': current_app.config['LOGIN_IMAGE_PATH'],
'content_id': content_id
}
send_email(subject='OpenRecords Portal',
to=[new_user.email],
email_content=render_template(
'email_templates/email_user_added.html',
agency_name=agency.name,
content_id=content_id,
domain=new_user.email.split('@')[1],
name=new_user.fullname),
image=image)
send_email(
subject='User {} Added'.format(new_user.fullname),
to=[OPENRECORDS_DL_EMAIL],
email_content=
'{} has been added to OpenRecords. Add {} to the service desk.'
.format(new_user.fullname, new_user.email))
flash('{} has been added.'.format(new_user.fullname),
category='success')
return redirect(url_for('admin.add_user'))
return render_template('admin/add_user.html', form=form)