def export_event_aggregates():
""" Exports the event aggregates as a csv """
database = Database()
# Make sure the user has access to the module
jwt_user = get_jwt_identity()
user = database.get_item('users', jwt_user)
authorized = conf.EVENT_GROUP in user['modules']
log_request(request, jwt_user, authorized)
if not authorized:
response = {'message': '%s does not have acccess to events'%(jwt_user)}
return jsonify(response), 403
q = request.args.get('q')
query = ('name', q) if q else None
database = Database()
df = database.read_table('event_aggregates', query=query)
today = str(datetime.datetime.now())[:10]
buffer = StringIO()
df.to_csv(buffer, encoding='utf-8', index=False)
output = make_response(buffer.getvalue())
output.headers["Content-Disposition"] = "attachment; filename=export.csv"
output.headers["Content-type"] = "text/csv"
return output
def __init__(self, eventbrite_org, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = Database(database=database)
self.eventbrite = Eventbrite()
self.eventbrite_org = eventbrite_org
def count_bad_login_attempts(user, domain, reset_date):
"""Counts the number of bad login attempts the user has made on the
specified domain. This is used to put a lock on the acccount if they
have made too many bad authentication requests.
Paramters
---------
user: string, the domain of the user
domain: string, the prefix for the host url. For https:/dev.shirconnect.com,
the domain would be 'dev'
reset_date: string, the date when the user last set their password. The bad
login count should return to zero after a password reset
Returns
-------
count: int, the number of bad login attempts
"""
database = Database(database='postgres', schema='application_logs')
sql = """
SELECT COUNT(id) AS bad_login_attempts
FROM application_logs.shir_connect_logs
WHERE application_user = '******'
AND host = '{domain}.shirconnect.com'
AND authorized = FALSE
AND load_datetime > NOW() - INTERVAL '1 DAY'
AND load_datetime > '{reset_date}'
""".format(user=user, domain=domain, reset_date=reset_date)
df = pd.read_sql(sql, database.connection)
bad_login_attempts = df.loc[0]['bad_login_attempts']
return int(bad_login_attempts)
def initialize_log_table():
"""Builds the table in the postgres database that is used for storing
application logs."""
database = Database(database='postgres')
LOGGER.info('Creating the application_logs schema ...')
schema_sql = "CREATE SCHEMA IF NOT EXISTS application_logs"
database.run_query(schema_sql)
table_sql = """
CREATE TABLE IF NOT EXISTS application_logs.shir_connect_logs (
id text,
application_user text,
authorized boolean,
base_url text,
endpoint text,
host text,
host_url text,
query_string text,
referrer text,
remote_addr text,
scheme text,
url text,
url_root text,
user_agent text,
load_datetime timestamp
)
"""
LOGGER.info('Creating the shir_connect_logs table ...')
database.run_query(table_sql)
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = database if database else Database()
self.member_loader = MemberLoader(database=database)
self.allowed_extensions = conf.ALLOWED_EXTENSIONS
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = Database() if not database else database
self.name_resolver = NameResolver(database=self.database)
self.participants = Participants(database=self.database)
self.avg_event_age = {}
def initialize(drop_views=False):
""" Initializes the tables for the dashboard """
database = Database()
LOGGER.info('Initializing the database ...')
database.initialize(drop_views=drop_views)
LOGGER.info('Loading member ids into participant match table ..')
name_resolver = NameResolver(database=database)
name_resolver.load_member_ids()
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = database if database else Database()
self.access_groups = conf.ACCESS_GROUPS
self.user_roles = conf.USER_ROLES
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = Database(database=database)
self.path = os.path.dirname(os.path.realpath(__file__))
self.url = 'https://www.zip-codes.com/cache/kml-zip/'
self.search = SearchEngine(simple_zipcode=True)
self.zip_code_cache = {}
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.path = os.path.dirname(os.path.realpath(__file__))
self.database = Database() if not database else database
self.lookup = self._read_names_file()
self.average_age = None
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
# Load column mapping configs
self.path = os.path.dirname(os.path.realpath(__file__))
filename = self.path + '/member_columns.yml'
with open(filename, 'r') as f:
self.column_mapping = yaml.safe_load(f)
self.database = Database() if not database else database
self.fake_news = FakeNews(database=self.database)
def __init__(self):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = Database()
self.events_manager = Events()
self.network = None
self.metrics = {
'node_connectivity': self.node_connectivity,
'edge_connectivity': self.edge_connectivity,
'density': self.density,
'membership_scaled_density': self.membership_scaled_density
}
def log_request(request, user, authorized, database=None):
"""Logs the API request to the database for security monitoring
and analyzing user metrics
Parameters
----------
request : request
The flask request object for the API call
user : str
The user who made the API call. Pulled from the JWT.
authorized : boolean
Indicates whether of not the user was authorized to
access the end point.
database : shir_connect.database.database
A Shir Connect database object. Primarily used for testing
Returns
-------
Logs information about the request to the Postgres database.
"""
# Don't write logs to the table during unit tests or development
if conf.SHIR_CONNECT_ENV in ['DEV', 'TEST']:
return None
else:
if not database:
database = Database(database='postgres', schema='application_logs')
# By default, the remote_addr attribute on the Flask request object
# if the IP address of the referrer, which in our case is NGINX. We
# configure NGINX for put the real remote_addr in the header so we're
# able to track it.
remote_addr = request.environ.get('HTTP_X_FORWARDED_FOR', request.remote_addr)
item = {
'id': uuid.uuid4().hex,
'application_user': user,
'authorized': authorized,
'base_url': request.base_url,
'endpoint': request.endpoint,
'host': request.host,
'host_url': request.host_url,
'query_string': request.query_string.decode('utf-8'),
'referrer': request.referrer,
'remote_addr': remote_addr,
'scheme': request.scheme,
'url': request.url,
'url_root': request.url_root,
'user_agent': str(request.user_agent),
'load_datetime': datetime.datetime.now()
}
database.load_item(item, 'shir_connect_logs')
def map_authorize():
""" Checks whether the users is authorized to view the map """
database = Database()
jwt_user = get_jwt_identity()
user = database.get_item('users', jwt_user)
authorized = conf.MAP_GROUP in user['modules']
log_request(request, jwt_user, authorized)
if not authorized:
response = {'message': '%s does not have access the map' % (jwt_user)}
return jsonify(response), 403
else:
response = {'message': '%s is authorized to view the map' % (jwt_user)}
return jsonify(response), 200
def member_authorize():
""" Checks to see if the user is authorized to see members """
database = Database()
jwt_user = get_jwt_identity()
user = database.get_item('users', jwt_user)
authorized = conf.TRENDS_GROUP in user['modules']
log_request(request, jwt_user, authorized)
if not authorized:
response = {
'message': '%s does not have access to trends' % (jwt_user)
}
return jsonify(response), 403
else:
del user['password']
del user['temporary_password']
return jsonify(user), 200
def test_read_table():
database = Database()
df = database.read_table('event_aggregates', limit=10)
assert len(df) == 10
df = database.read_table(
'event_aggregates',
query=('name', 'Rodef 2100'),
where=[('start_datetime',{'>=': "'2018-01-01'"})],
limit=10
)
assert len(df) > 0
for i in df.index:
row = df.loc[i]
assert str(row['start_datetime']) >= '2018-01-01'
assert '2100' in row['name'] or 'rodef' in row['name'].lower()
count = database.count_rows('event_aggregates', query=('name', 'Rodef 2100'))
assert count > 0
def get_new_members():
"""Pulls in a list of the most recent members of the Congregation."""
database = Database()
jwt_user = get_jwt_identity()
authorized = utils.check_access(jwt_user, conf.REPORT_GROUP, database)
utils.log_request(request, jwt_user, authorized)
if not authorized:
response = {
'message': '{} does not have access to reports.'.format(jwt_user)
}
return jsonify(response), 403
limit_param = request.args.get('limit')
limit = limit_param if limit_param else 25
new_members = database.read_table('members_view',
limit=limit,
order='desc',
sort='membership_date')
response = database.to_json(new_members)
return jsonify(response)
def build_email_content():
"""Builds the email with the weekly usage statistics."""
database = Database(database='postgres', schema='application_logs')
all_time_sql = """
SELECT COUNT(*) as total, application_user,
remote_addr, host, user_agent
FROM application_logs.shir_connect_logs
WHERE load_datetime >= '2018-08-01'
AND application_user <> 'true'
GROUP BY application_user, host, remote_addr, user_agent
ORDER BY application_user ASC
"""
all_time_stats = pd.read_sql(all_time_sql, database.connection)
all_time_html = all_time_stats.to_html()
weekly_sql = """
SELECT COUNT(*) as total, application_user,
remote_addr, host, user_agent
FROM application_logs.shir_connect_logs
WHERE load_datetime >= NOW() - INTERVAL '7 DAYS'
GROUP BY application_user, host, remote_addr, user_agent
ORDER BY application_user ASC
"""
weekly_stats = pd.read_sql(weekly_sql, database.connection)
weekly_html = weekly_stats.to_html()
html = """
<h3>Usage Statistics</h3>
<p>Greetings Fiddlers! Here are the latest usage statistics for Shir Connect.</p>
<h4>Overall Usage</h4>
{all_time_html}
<h4>Weekly Usage</h4>
{weekly_html}
""".format(all_time_html=all_time_html, weekly_html=weekly_html)
return html
def test_load_items():
database = Database()
database.delete_item('members', 'testid1')
database.delete_item('members', 'testid2')
columns = database.get_columns('members')
item1 = {x: None for x in columns}
item1['id'] = 'testid1'
item2 = {x: None for x in columns}
item2['id'] = 'testid2'
items = [item1, item2]
database.load_items(items, 'members')
item1_ = database.get_item('members', 'testid1')
assert item1_['id'] == 'testid1'
item2_ = database.get_item('members', 'testid2')
assert item2_['id'] == 'testid2'
database.delete_item('members', 'testid1')
item1_ = database.get_item('members', 'testid1')
assert item1_ == None
database.delete_item('members', 'testid2')
item2_ = database.get_item('members', 'testid2')
assert item2_ == None
def add_fake_names():
"""Adds fake names that can be used in demo mode."""
database = Database(database='trs')
fake_news = FakeNews(database=database)
fake_news.build_fake_data()
def refresh_materialized_views():
"""Refreshes the materialized views for Shir Connect."""
database = Database(database='trs')
database.refresh_views()
print('Materialized views have been refreshed!')
def match_participants():
"""Runs the fuzzy matching algorithm to match up attendees and members."""
database = Database(database='trs')
participant_matcher = ParticipantMatcher(database=database)
participant_matcher.run()
participant_matcher.estimate_unknown_ages()
def __init__(self, database=None):
self.database = database if database else Database()
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = Database() if not database else database
self.faker = Faker()
self.postal_codes = None
def test_refresh_views():
database = Database()
database.refresh_views(test=True)
def test_initialize():
database = Database()
database.initialize()
assert database.connection.status == 1
def refresh_views():
"""Refreshes the materialized views for the dashboard """
database = Database()
LOGGER.info('Refreshing materialized views ...')
database.refresh_views()
def __init__(self, database=None):
daiquiri.setup(level=logging.INFO)
self.logger = daiquiri.getLogger(__name__)
self.database = database if database else Database()
def test_get_columns():
database = Database()
columns = database.get_columns('events')
assert len(columns) > 0