def acknowledgment():
"""Generates the acknowledgment report.
Returns:
Template with context.
"""
acknowledgment_form = AcknowledgmentForm()
if acknowledgment_form.validate_on_submit():
# Only agency administrators can access endpoint
if not current_user.is_agency_admin:
return jsonify({
'error': 'Only Agency Administrators can access this endpoint.'
}), 403
date_from = local_to_utc(datetime.strptime(request.form['date_from'], '%m/%d/%Y'),
current_app.config['APP_TIMEZONE'])
date_to = local_to_utc(datetime.strptime(request.form['date_to'], '%m/%d/%Y'),
current_app.config['APP_TIMEZONE'])
redis_key = '{current_user_guid}-{report_type}-{agency_ein}-{timestamp}'.format(
current_user_guid=current_user.guid,
report_type='acknowledgment',
agency_ein=current_user.default_agency_ein,
timestamp=datetime.now(),
)
generate_acknowledgment_report.apply_async(args=[current_user.guid,
date_from,
date_to],
serializer='pickle',
task_id=redis_key)
flash('Your report is being generated. You will receive an email with the report attached once its complete.',
category='success')
else:
for field, _ in acknowledgment_form.errors.items():
flash(acknowledgment_form.errors[field][0], category='danger')
return redirect(url_for("report.show_report"))
def transfer_requests(agency_next_request_number, request):
"""
Since categories were not stored per request in v1, transferred
requests are given a category of "All" (the default for v2 requests).
Since agency description privacy was not stored in v1, the privacy for
transferred requests is set if no description due date is present.
"""
CUR_V1.execute("SELECT name FROM department WHERE id = %s" %
request.department_id)
agency_ein = AGENCY_V1_NAME_TO_EIN[CUR_V1.fetchone().name]
privacy = {
"title": bool(request.title_private),
"agency_description": request.agency_description_due_date is None
}
query = ("INSERT INTO requests ("
"id, "
"agency_ein, "
"category, "
"title, "
"description, "
"date_created, "
"date_submitted, "
"due_date, "
"submission, "
"status, "
"privacy, "
"agency_description, "
"agency_description_release_date) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")
CUR_V2.execute(
query,
(
request.id, # id
agency_ein, # agency id
DEFAULT_CATEGORY, # category
request.summary, # title
request.text, # description
local_to_utc(request.date_created, TZ_NY), # date_created
local_to_utc(request.date_received, TZ_NY), # date_submitted
local_to_utc(
request.due_date, TZ_NY
), # due_date # FIXME: we did not get_due_date (shift to 5 PM)
request.offline_submission_type, # submission
_get_compatible_status(request), # status
json.dumps(privacy), # privacy
request.agency_description, # agency_description
request.
agency_description_due_date # agency_description_release_date
))
if agency_next_request_number.get(agency_ein) is not None:
agency_next_request_number[agency_ein] = max(
agency_next_request_number[agency_ein], int(request.id[14:]))
else:
agency_next_request_number[agency_ein] = int(request.id[14:])
def acknowledgment():
"""Generates the acknowledgment report.
Returns:
Template with context.
"""
acknowledgment_form = AcknowledgmentForm()
if acknowledgment_form.validate_on_submit():
# Only agency administrators can access endpoint
if not current_user.is_agency_admin:
return jsonify({
'error': 'Only Agency Administrators can access this endpoint.'
}), 403
date_from = local_to_utc(datetime.strptime(request.form['date_from'], '%m/%d/%Y'),
current_app.config['APP_TIMEZONE'])
date_to = local_to_utc(datetime.strptime(request.form['date_to'], '%m/%d/%Y'),
current_app.config['APP_TIMEZONE'])
redis_key = '{current_user_guid}-{report_type}-{agency_ein}-{timestamp}'.format(
current_user_guid=current_user.guid,
report_type='acknowledgment',
agency_ein=current_user.default_agency_ein,
timestamp=datetime.now(),
)
generate_acknowledgment_report.apply_async(args=[current_user.guid,
date_from,
date_to],
serializer='pickle',
task_id=redis_key)
flash('Your report is being generated. You will receive an email with the report attached once its complete.',
category='success')
else:
for field, _ in acknowledgment_form.errors.items():
flash(acknowledgment_form.errors[field][0], category='danger')
return redirect(url_for("report.show_report"))
def transfer_requests(agency_next_request_number, request):
"""
Since categories were not stored per request in v1, transferred
requests are given a category of "All" (the default for v2 requests).
Since agency description privacy was not stored in v1, the privacy for
transferred requests is set if no description due date is present.
"""
CUR_V1.execute("SELECT name FROM department WHERE id = %s" % request.department_id)
agency_ein = AGENCY_V1_NAME_TO_EIN[CUR_V1.fetchone().name]
privacy = {
"title": bool(request.title_private),
"agency_description": request.agency_description_due_date is None
}
query = ("INSERT INTO requests ("
"id, "
"agency_ein, "
"category, "
"title, "
"description, "
"date_created, "
"date_submitted, "
"due_date, "
"submission, "
"status, "
"privacy, "
"agency_description, "
"agency_description_release_date) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")
CUR_V2.execute(query, (
request.id, # id
agency_ein, # agency id
DEFAULT_CATEGORY, # category
request.summary, # title
request.text, # description
local_to_utc(request.date_created, TZ_NY), # date_created
local_to_utc(request.date_received, TZ_NY), # date_submitted
local_to_utc(request.due_date, TZ_NY), # due_date # FIXME: we did not get_due_date (shift to 5 PM)
request.offline_submission_type, # submission
_get_compatible_status(request), # status
json.dumps(privacy), # privacy
request.agency_description, # agency_description
request.agency_description_due_date # agency_description_release_date
))
if agency_next_request_number.get(agency_ein) is not None:
agency_next_request_number[agency_ein] = max(
agency_next_request_number[agency_ein],
int(request.id[14:]))
else:
agency_next_request_number[agency_ein] = int(request.id[14:])
def reopen(self, date, user=None):
return self.__extend(
determination_type.REOPENING,
process_due_date(local_to_utc(date, self.__tz_name)), user, {
"status": request_status.IN_PROGRESS,
"agency_request_summary_release_date": None
})
def transfer_reopenings(email):
"""
Since v1 Re-openings do not require a new request due date, the `date` field of
transferred re-openings is set to the current due date of the associated request.
"""
response_id = _create_response(email,
response_type.DETERMINATION,
_get_release_date(email.time_sent),
privacy=response_privacy.RELEASE_AND_PUBLIC,
date_created=email.time_sent)
query = ("INSERT INTO determinations ("
"id, "
"dtype, "
'"date") '
"VALUES (%s, %s, %s)")
CUR_V1.execute("SELECT due_date FROM request WHERE id = '%s'" %
email.request_id)
due_date = CUR_V1.fetchone().due_date
date = local_to_utc(due_date, TZ_NY)
CUR_V2.execute(query,
(response_id, determination_type.ACKNOWLEDGMENT, date))
def transfer_reopenings(email):
"""
Since v1 Re-openings do not require a new request due date, the `date` field of
transferred re-openings is set to the current due date of the associated request.
"""
response_id = _create_response(email, response_type.DETERMINATION,
_get_release_date(email.time_sent),
privacy=response_privacy.RELEASE_AND_PUBLIC,
date_created=email.time_sent)
query = ("INSERT INTO determinations ("
"id, "
"dtype, "
'"date") '
"VALUES (%s, %s, %s)")
CUR_V1.execute("SELECT due_date FROM request WHERE id = '%s'" % email.request_id)
due_date = CUR_V1.fetchone().due_date
date = local_to_utc(due_date, TZ_NY)
CUR_V2.execute(query, (
response_id,
determination_type.ACKNOWLEDGMENT,
date
))
def test_extend_date_due_soon(self):
request = self.rf.create_request_as_anonymous_user(due_date=datetime.utcnow())
date = calendar.addbusdays(utc_to_local(request.due_date, self.tz_name), 1)
due_date = process_due_date(local_to_utc(date, self.tz_name))
response = request.extend(date=date)
self.__test_extension(
response, determination_type.EXTENSION, str, due_date, request_status.DUE_SOON, request=request)
def _get_record_release_date(record):
if PRIVACY[record.privacy] == response_privacy.RELEASE_AND_PUBLIC:
if record.release_date is not None:
release_date = record.release_date
else:
release_date = CAL.addbusdays(record.date_created, RELEASE_PUBLIC_DAYS)
return local_to_utc(release_date, TZ_NY)
return None
def _process_due_date(due_date):
"""
Script-compatible app.lib.date_utils.process_due_date
:param due_date: local time expected (to match v1 db)
"""
date = due_date.replace(hour=17, minute=00, second=00, microsecond=00)
return local_to_utc(date, TZ_NY)
def _process_due_date(due_date):
"""
Script-compatible app.lib.date_utils.process_due_date
:param due_date: local time expected (to match v1 db)
"""
date = due_date.replace(hour=17, minute=00, second=00, microsecond=00)
return local_to_utc(date, TZ_NY)
def __get_new_due_date(self, days=None, date=None):
assert days is not None or date is not None
if days is None:
new_due_date = process_due_date(local_to_utc(date, self.__tz_name))
else:
new_due_date = get_due_date(
utc_to_local(self.request.due_date, self.__tz_name), days,
self.__tz_name)
return new_due_date
def _get_record_release_date(record):
if PRIVACY[record.privacy] == response_privacy.RELEASE_AND_PUBLIC:
if record.release_date is not None:
release_date = record.release_date
else:
release_date = CAL.addbusdays(record.date_created,
RELEASE_PUBLIC_DAYS)
return local_to_utc(release_date, TZ_NY)
return None
def _create_response(obj,
type_,
release_date,
privacy=None,
date_created=None):
"""
Create openrecords_v2.public.responses row.
:param obj: database record containing request id (via `id` or `request_id`)
and privacy (if not provided as kwarg)
:returns: response id
"""
query = ("INSERT INTO responses ("
"request_id, "
"privacy, "
"date_modified, "
"release_date, "
"deleted, "
'"type", '
"is_editable) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
if date_created is None:
date_created = obj.date_created
date_created_utc = local_to_utc(date_created, TZ_NY)
try:
request_id = obj.request_id
except AttributeError:
assert 'FOIL' in obj.id
request_id = obj.id
CUR_V2.execute(
query,
(
request_id, # request_id
privacy or PRIVACY[obj.privacy], # privacy
date_created_utc, # date_modified
release_date, # release_date
False, # deleted
type_, # type
True # is_editable
))
CONN_V2.commit()
CUR_V2.execute("SELECT LASTVAL()")
return CUR_V2.fetchone().lastval
def _create_response(obj, type_, release_date, privacy=None, date_created=None):
"""
Create openrecords_v2.public.responses row.
:param obj: database record containing request id (via `id` or `request_id`)
and privacy (if not provided as kwarg)
:returns: response id
"""
query = ("INSERT INTO responses ("
"request_id, "
"privacy, "
"date_modified, "
"release_date, "
"deleted, "
'"type", '
"is_editable) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
if date_created is None:
date_created = obj.date_created
date_created_utc = local_to_utc(date_created, TZ_NY)
try:
request_id = obj.request_id
except AttributeError:
assert 'FOIL' in obj.id
request_id = obj.id
CUR_V2.execute(query, (
request_id, # request_id
privacy or PRIVACY[obj.privacy], # privacy
date_created_utc, # date_modified
release_date, # release_date
False, # deleted
type_, # type
True # is_editable
))
CONN_V2.commit()
CUR_V2.execute("SELECT LASTVAL()")
return CUR_V2.fetchone().lastval
def _get_release_date(start_date):
release_date = CAL.addbusdays(start_date, RELEASE_PUBLIC_DAYS)
return local_to_utc(release_date, TZ_NY)
def _get_release_date(start_date):
release_date = CAL.addbusdays(start_date, RELEASE_PUBLIC_DAYS)
return local_to_utc(release_date, TZ_NY)
def create_request(title,
description,
category,
tz_name,
agency_ein=None,
first_name=None,
last_name=None,
submission=DIRECT_INPUT,
agency_date_submitted_local=None,
email=None,
user_title=None,
organization=None,
phone=None,
fax=None,
address=None,
upload_path=None,
custom_metadata=None):
"""
Creates a new FOIL Request and associated Users, UserRequests, and Events.
:param title: request title
:param description: detailed description of the request
:param tz_name: client's timezone name
:param agency_ein: agency_ein selected for the request
:param first_name: first name of the requester
:param last_name: last name of the requester
:param submission: request submission method
:param agency_date_submitted_local: submission date chosen by agency
:param email: requester's email address
:param user_title: requester's organizational title
:param organization: requester's organization
:param phone: requester's phone number
:param fax: requester's fax number
:param address: requester's mailing address
:param upload_path: file path of the validated upload
:param custom_metadata: JSON containing all data from custom request forms
"""
# 1. Generate the request id
request_id = generate_request_id(agency_ein)
# 2a. Generate Email Notification Text for Agency
# agency_email = generate_email_template('agency_acknowledgment.html', request_id=request_id)
# 2b. Generate Email Notification Text for Requester
# 3a. Send Email Notification Text for Agency
# 3b. Send Email Notification Text for Requester
# 4a. Calculate Request Submitted Date (Round to next business day)
date_created_local = utc_to_local(datetime.utcnow(), tz_name)
if current_user.is_agency:
date_submitted_local = agency_date_submitted_local
else:
date_submitted_local = date_created_local
# 4b. Calculate Request Due Date (month day year but time is always 5PM, 5 Days after submitted date)
due_date = get_due_date(date_submitted_local, ACKNOWLEDGMENT_PERIOD_LENGTH,
tz_name)
date_created = local_to_utc(date_created_local, tz_name)
date_submitted = local_to_utc(date_submitted_local, tz_name)
# 5. Create Request
request = Requests(id=request_id,
title=title,
agency_ein=agency_ein,
category=category,
description=description,
date_created=date_created,
date_submitted=date_submitted,
due_date=due_date,
submission=submission,
custom_metadata=custom_metadata)
create_object(request)
guid_for_event = current_user.guid if not current_user.is_anonymous else None
# 6. Get or Create User
if current_user.is_public:
user = current_user
else:
user = Users(guid=generate_guid(),
email=email,
first_name=first_name,
last_name=last_name,
title=user_title or None,
organization=organization or None,
email_validated=False,
terms_of_use_accepted=False,
phone_number=phone,
fax_number=fax,
mailing_address=address,
is_anonymous_requester=True)
create_object(user)
# user created event
create_object(
Events(request_id,
guid_for_event,
event_type.USER_CREATED,
previous_value=None,
new_value=user.val_for_events,
response_id=None,
timestamp=datetime.utcnow()))
if upload_path is not None:
# 7. Move file to upload directory
upload_path = _move_validated_upload(request_id, upload_path)
# 8. Create response object
filename = os.path.basename(upload_path)
response = Files(request_id,
RELEASE_AND_PRIVATE,
filename,
filename,
fu.get_mime_type(upload_path),
fu.getsize(upload_path),
fu.get_hash(upload_path),
is_editable=False)
create_object(obj=response)
# 8. Create upload Event
upload_event = Events(user_guid=user.guid,
response_id=response.id,
request_id=request_id,
type_=event_type.FILE_ADDED,
timestamp=datetime.utcnow(),
new_value=response.val_for_events)
create_object(upload_event)
# Create response token if requester is anonymous
if current_user.is_anonymous or current_user.is_agency:
create_object(ResponseTokens(response.id))
role_to_user = {
role.PUBLIC_REQUESTER: user.is_public,
role.ANONYMOUS: user.is_anonymous_requester,
}
role_name = [k for (k, v) in role_to_user.items() if v][0]
# (key for "truthy" value)
# 9. Create Event
timestamp = datetime.utcnow()
event = Events(user_guid=user.guid
if current_user.is_anonymous else current_user.guid,
request_id=request_id,
type_=event_type.REQ_CREATED,
timestamp=timestamp,
new_value=request.val_for_events)
create_object(event)
if current_user.is_agency:
agency_event = Events(user_guid=current_user.guid,
request_id=request.id,
type_=event_type.AGENCY_REQ_CREATED,
timestamp=timestamp)
create_object(agency_event)
# 10. Create UserRequest for requester
user_request = UserRequests(
user_guid=user.guid,
request_user_type=user_type_request.REQUESTER,
request_id=request_id,
permissions=Roles.query.filter_by(name=role_name).first().permissions)
create_object(user_request)
create_object(
Events(request_id,
guid_for_event,
event_type.USER_ADDED,
previous_value=None,
new_value=user_request.val_for_events,
response_id=None,
timestamp=datetime.utcnow()))
# 11. Create the elasticsearch request doc only if agency has been onboarded
agency = Agencies.query.filter_by(ein=agency_ein).one()
# 12. Add all agency administrators to the request.
if agency.administrators:
# b. Store all agency users objects in the UserRequests table as Agency users with Agency Administrator
# privileges
_create_agency_user_requests(request_id=request_id,
agency_admins=agency.administrators,
guid_for_event=guid_for_event)
# 13. Add all parent agency administrators to the request.
if agency != agency.parent:
if (agency.parent.agency_features is not None
and agency_ein in agency.parent.agency_features.get(
'monitor_agency_requests', []) and agency.parent.is_active
and agency.parent.administrators):
_create_agency_user_requests(
request_id=request_id,
agency_admins=agency.parent.administrators,
guid_for_event=guid_for_event)
# (Now that we can associate the request with its requester AND agency users.)
if current_app.config['ELASTICSEARCH_ENABLED'] and agency.is_active:
request.es_create()
return request_id
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 datestr_local_to_utc(datestr):
return local_to_utc(
datetime.strptime(datestr, DT_DATE_RANGE_FORMAT), tz_name
).strftime(DT_DATE_RANGE_FORMAT)
def datestr_local_to_utc(datestr):
return local_to_utc(datetime.strptime(datestr, DT_DATE_RANGE_FORMAT),
tz_name).strftime(DT_DATE_RANGE_FORMAT)
def test_reopen(self):
date = calendar.addbusdays(utc_to_local(self.request.due_date, self.tz_name), 1)
response = self.request.reopen(date)
due_date = process_due_date(local_to_utc(date, self.tz_name))
self.__test_extension(response, determination_type.REOPENING, None, due_date)
self.assertEqual(self.request.agency_request_summary, None)
def create_request(title,
description,
category,
tz_name,
agency_ein=None,
first_name=None,
last_name=None,
submission=DIRECT_INPUT,
agency_date_submitted_local=None,
email=None,
user_title=None,
organization=None,
phone=None,
fax=None,
address=None,
upload_path=None,
custom_metadata=None):
"""
Creates a new FOIL Request and associated Users, UserRequests, and Events.
:param title: request title
:param description: detailed description of the request
:param tz_name: client's timezone name
:param agency_ein: agency_ein selected for the request
:param first_name: first name of the requester
:param last_name: last name of the requester
:param submission: request submission method
:param agency_date_submitted_local: submission date chosen by agency
:param email: requester's email address
:param user_title: requester's organizational title
:param organization: requester's organization
:param phone: requester's phone number
:param fax: requester's fax number
:param address: requester's mailing address
:param upload_path: file path of the validated upload
:param custom_metadata: JSON containing all data from custom request forms
"""
# 1. Generate the request id
request_id = generate_request_id(agency_ein)
# 2a. Generate Email Notification Text for Agency
# agency_email = generate_email_template('agency_acknowledgment.html', request_id=request_id)
# 2b. Generate Email Notification Text for Requester
# 3a. Send Email Notification Text for Agency
# 3b. Send Email Notification Text for Requester
# 4a. Calculate Request Submitted Date (Round to next business day)
date_created_local = utc_to_local(datetime.utcnow(), tz_name)
if current_user.is_agency:
date_submitted_local = agency_date_submitted_local
else:
date_submitted_local = date_created_local
# 4b. Calculate Request Due Date (month day year but time is always 5PM, 5 Days after submitted date)
due_date = get_due_date(
date_submitted_local,
ACKNOWLEDGMENT_PERIOD_LENGTH,
tz_name)
date_created = local_to_utc(date_created_local, tz_name)
date_submitted = local_to_utc(date_submitted_local, tz_name)
# 5. Create Request
request = Requests(
id=request_id,
title=title,
agency_ein=agency_ein,
category=category,
description=description,
date_created=date_created,
date_submitted=date_submitted,
due_date=due_date,
submission=submission,
custom_metadata=custom_metadata
)
create_object(request)
guid_for_event = current_user.guid if not current_user.is_anonymous else None
# 6. Get or Create User
if current_user.is_public:
user = current_user
else:
user = Users(
guid=generate_guid(),
email=email,
first_name=first_name,
last_name=last_name,
title=user_title or None,
organization=organization or None,
email_validated=False,
terms_of_use_accepted=False,
phone_number=phone,
fax_number=fax,
mailing_address=address,
is_anonymous_requester=True
)
create_object(user)
# user created event
create_object(Events(
request_id,
guid_for_event,
event_type.USER_CREATED,
previous_value=None,
new_value=user.val_for_events,
response_id=None,
timestamp=datetime.utcnow()
))
if upload_path is not None:
# 7. Move file to upload directory
upload_path = _move_validated_upload(request_id, upload_path)
# 8. Create response object
filename = os.path.basename(upload_path)
response = Files(request_id,
RELEASE_AND_PRIVATE,
filename,
filename,
fu.get_mime_type(upload_path),
fu.getsize(upload_path),
fu.get_hash(upload_path),
is_editable=False)
create_object(obj=response)
# 8. Create upload Event
upload_event = Events(user_guid=user.guid,
response_id=response.id,
request_id=request_id,
type_=event_type.FILE_ADDED,
timestamp=datetime.utcnow(),
new_value=response.val_for_events)
create_object(upload_event)
# Create response token if requester is anonymous
if current_user.is_anonymous or current_user.is_agency:
create_object(ResponseTokens(response.id))
role_to_user = {
role.PUBLIC_REQUESTER: user.is_public,
role.ANONYMOUS: user.is_anonymous_requester,
}
role_name = [k for (k, v) in role_to_user.items() if v][0]
# (key for "truthy" value)
# 9. Create Event
timestamp = datetime.utcnow()
event = Events(user_guid=user.guid if current_user.is_anonymous else current_user.guid,
request_id=request_id,
type_=event_type.REQ_CREATED,
timestamp=timestamp,
new_value=request.val_for_events)
create_object(event)
if current_user.is_agency:
agency_event = Events(user_guid=current_user.guid,
request_id=request.id,
type_=event_type.AGENCY_REQ_CREATED,
timestamp=timestamp)
create_object(agency_event)
# 10. Create UserRequest for requester
user_request = UserRequests(user_guid=user.guid,
request_user_type=user_type_request.REQUESTER,
request_id=request_id,
permissions=Roles.query.filter_by(
name=role_name).first().permissions)
create_object(user_request)
create_object(Events(
request_id,
guid_for_event,
event_type.USER_ADDED,
previous_value=None,
new_value=user_request.val_for_events,
response_id=None,
timestamp=datetime.utcnow()
))
# 11. Create the elasticsearch request doc only if agency has been onboarded
agency = Agencies.query.filter_by(ein=agency_ein).one()
# 12. Add all agency administrators to the request.
if agency.administrators:
# b. Store all agency users objects in the UserRequests table as Agency users with Agency Administrator
# privileges
_create_agency_user_requests(request_id=request_id,
agency_admins=agency.administrators,
guid_for_event=guid_for_event)
# 13. Add all parent agency administrators to the request.
if agency != agency.parent:
if (
agency.parent.agency_features is not None and
agency_ein in agency.parent.agency_features.get('monitor_agency_requests', []) and
agency.parent.is_active and
agency.parent.administrators
):
_create_agency_user_requests(request_id=request_id,
agency_admins=agency.parent.administrators,
guid_for_event=guid_for_event)
# (Now that we can associate the request with its requester AND agency users.)
if current_app.config['ELASTICSEARCH_ENABLED'] and agency.is_active:
request.es_create()
return request_id
def test_acknowledge_date(self):
date = calendar.addbusdays(datetime.now(), 100)
response = self.request.acknowledge(date=date)
due_date = process_due_date(local_to_utc(date, self.tz_name))
self.__test_extension(response, determination_type.ACKNOWLEDGMENT, str, due_date)
def __assert_request_data_correct(self,
user,
request,
agency_parent_ein,
default=False,
title=None,
description=None,
agency_request_summary=None,
agency_ein=None,
date_created=None,
due_date=None,
category='All',
title_privacy=True,
agency_request_summary_privacy=True,
submission=None,
status=request_status.OPEN):
request = Requests.query.get(request.id)
privacy = {"title": title_privacy, "agency_request_summary": agency_request_summary_privacy}
agency_ein = agency_ein or user.agency_ein or request.agency_ein
date_created_local = utc_to_local(date_created or request.date_created, self.tz_name)
date_submitted_local = get_following_date(date_created_local)
if default:
self.assertTrue(request.submission in submission_methods.ALL)
request_list = [
type(request.title),
type(request.description),
]
check_list = [
str,
str,
]
else:
request_list = [
request.title,
request.description,
request.agency_request_summary,
request.date_created,
request.submission,
]
check_list = [
title,
description,
agency_request_summary,
date_created,
submission,
]
request_list += [
request.id,
request.category,
request.privacy,
request.agency_ein,
request.status,
request.date_submitted,
request.due_date,
]
check_list += [
"FOIL-{}-{}-00001".format(datetime.today().year, agency_parent_ein),
category,
privacy,
agency_ein,
status,
local_to_utc(date_submitted_local, self.tz_name),
due_date or get_due_date(date_submitted_local, ACKNOWLEDGMENT_DAYS_DUE, self.tz_name)
]
self.assertEqual(request_list, check_list)
# check associated events
event_req_created = Events.query.filter_by(type=event_type.REQ_CREATED).one()
self.assertEqual(
[
event_req_created.user_guid,
event_req_created.auth_user_type,
event_req_created.request_id,
event_req_created.response_id,
event_req_created.previous_value,
event_req_created.new_value,
],
[
user.guid,
user.auth_user_type,
request.id,
None, # response_id
None, # previous_value
request.val_for_events # new_value
]
)
if user.is_agency:
event_agency_req_created = Events.query.filter_by(type=event_type.AGENCY_REQ_CREATED).one()
self.assertEqual(
[
event_agency_req_created.user_guid,
event_agency_req_created.auth_user_type,
event_agency_req_created.request_id,
event_agency_req_created.response_id,
event_agency_req_created.previous_value,
event_agency_req_created.new_value,
],
[
user.guid,
user.auth_user_type,
request.id,
None, # response_id
None, # previous_value
None, # new_value
]
)
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 create_request(
self,
user,
title=None,
description=None,
agency_request_summary=None, # TODO: agency_request_summary_release_date
agency_ein=None,
date_created=None,
date_submitted=None,
due_date=None,
category=None,
title_privacy=True,
agency_request_summary_privacy=True,
submission=None,
status=request_status.OPEN,
tz_name=current_app.config["APP_TIMEZONE"]):
"""
Create a request as the supplied user. An anonymous requester
will be created if the supplied user is an agency user.
:rtype: RequestWrapper
"""
# check due date
if (date_created is not None
or date_submitted is not None) and due_date is not None:
def assert_date(date, date_var_str):
assert (
due_date - date
).days >= 1, "due_date must be at least 1 day after " + date_var_str
if date_created is not None:
assert_date(date_created, "date_created")
if date_submitted is not None:
assert_date(date_submitted, "date_submitted")
# check agency_ein
if (agency_ein is not None or self.agency_ein is not None) \
and user.auth_user_type == user_type_auth.AGENCY_USER:
assert (agency_ein or self.agency_ein) == user.agency_ein, \
"user's agency ein must match supplied agency ein"
agency_ein = agency_ein or self.agency_ein or user.agency_ein or get_random_agency(
).ein
# create dates
date_created_local = utc_to_local(date_created or datetime.utcnow(),
tz_name)
date_submitted_local = date_submitted or get_following_date(
date_created_local)
due_date = due_date or get_due_date(date_submitted_local,
ACKNOWLEDGMENT_DAYS_DUE, tz_name)
date_created = date_created or local_to_utc(date_created_local,
tz_name)
date_submitted = date_submitted or local_to_utc(
date_submitted_local, tz_name)
# create request
request = Requests(
generate_request_id(agency_ein),
title or fake.title(),
description or fake.description(),
agency_ein=agency_ein,
date_created=date_created or datetime.utcnow(),
date_submitted=date_submitted,
due_date=due_date,
category=category,
privacy={
"title": title_privacy,
"agency_request_summary": agency_request_summary_privacy
},
submission=submission or random.choice(submission_methods.ALL),
status=status,
)
if agency_request_summary is not None:
request.agency_request_summary = agency_request_summary
if agency_request_summary_privacy is not None:
request.agency_request_summary_release_date = calendar.addbusdays(
datetime.utcnow(), RELEASE_PUBLIC_DAYS
) if not agency_request_summary_privacy else None
create_object(request)
request = RequestWrapper(request, self.agency_user)
# create events
timestamp = datetime.utcnow()
create_object(
Events(user_guid=user.guid,
auth_user_type=user.auth_user_type,
request_id=request.id,
type_=event_type.REQ_CREATED,
timestamp=timestamp,
new_value=request.val_for_events))
if user.is_agency:
create_object(
Events(user_guid=user.guid,
auth_user_type=user.auth_user_type,
request_id=request.id,
type_=event_type.AGENCY_REQ_CREATED,
timestamp=timestamp))
# add users
if user.is_public or user.is_anonymous_requester:
request.add_user(user)
if user.is_agency: # then create and add anonymous requester
request.add_user(self.__uf.create_anonymous_user())
for admin in Agencies.query.filter_by(
ein=agency_ein).one().administrators:
request.add_user(admin)
# create request doc now that requester is set
request.es_create()
return request
