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)