def agencies(app):
with app.app_context():
if not db.session.query(Agency).filter(Agency.id == 10).one_or_none():
db.session.add(
Agency(id=10,
name='Team Test Agency',
domain='teamtest.gov.au',
category='Commonwealth',
whitelisted=True,
domains=[
AgencyDomain(domain='teamtest.gov.au', active=True)
]))
db.session.add(
Agency(id=11,
name='Team Test Agency 2',
domain='teamtest2.gov.au',
category='Commonwealth',
whitelisted=True,
domains=[
AgencyDomain(domain='teamtest2.gov.au', active=True)
]))
db.session.commit()
yield Agency.query.all()
def agencies(client, app):
with app.app_context():
db.session.add(
Agency(id=1,
name="TEST",
domain="test.gov.au",
category="Commonwealth",
whitelisted=True,
state="ACT",
body_type="other",
reports=True,
must_join_team=True))
db.session.add(
Agency(id=2,
name="TEST 2",
domain="test2.gov.au",
category="Commonwealth",
whitelisted=True,
state="ACT",
body_type="other",
reports=True,
must_join_team=False))
db.session.commit()
yield db.session.query(Agency).all()
def update_agency_totals():
all_agencies = Agency.all()
# For each agency, update their report counts for every domain they have.
for agency in all_agencies:
# TODO: Do direct DB queries for answers, rather than iterating.
# Analytics
eligible = Domain.eligible_for_agency(agency['slug'], 'analytics')
agency_report = {
'eligible': len(eligible),
'participating': 0
}
for domain in eligible:
report = domain['analytics']
if report['participating'] == True:
agency_report['participating'] += 1
print("[%s][%s] Adding report." % (agency['slug'], 'analytics'))
Agency.add_report(agency['slug'], 'analytics', agency_report)
# HTTPS
eligible = Domain.eligible_for_agency(agency['slug'], 'https')
eligible_https = list(map(lambda w: w['https'], eligible))
agency_report = total_https_report(eligible_https)
# agency_report['subdomains'] = total_https_subdomain_report(eligible)
print("[%s][%s] Adding report." % (agency['slug'], 'https'))
Agency.add_report(agency['slug'], 'https', agency_report)
def agencies(app, request):
with app.app_context():
db.session.add(
Agency(
id=1,
name='Department of Adventure',
domain='adventure.gov.au',
category='Commonwealth',
state='ACT',
whitelisted=True,
domains=[AgencyDomain(domain='adventure.gov.au',
active=True)]))
db.session.add(
Agency(id=2,
name='Department of Millenium Falcons',
domain='falcon.net.au',
category='Commonwealth',
state='NSW',
whitelisted=True,
domains=[AgencyDomain(domain='falcon.net.au',
active=True)]))
db.session.add(
Agency(id=3,
name='Jedi Temple',
domain='jedi.edu.au',
category='State',
state='NSW',
whitelisted=False,
domains=[AgencyDomain(domain='jedi.edu.au', active=True)]))
db.session.commit()
yield Agency.query.all()
def agencies(app, request):
with app.app_context():
db.session.add(
Agency(
id=1,
name='Digital Transformation Agency',
domain='digital.gov.au',
category='Commonwealth',
whitelisted=True,
domains=[AgencyDomain(domain='digital.gov.au', active=True)]))
db.session.add(
Agency(id=2,
name='Test Agency',
domain='test.gov.au',
category='Commonwealth',
whitelisted=True,
domains=[AgencyDomain(domain='test.gov.au', active=True)]))
db.session.add(
Agency(id=3,
name='Another Test Agency',
domain='asdf.com.au',
category='Commonwealth',
whitelisted=True,
domains=[AgencyDomain(domain='asdf.com.au', active=True)]))
db.session.commit()
yield Agency.query.all()
def run(date):
if date is None:
date = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
# Reset the database.
print("Clearing the database.")
models.clear_database()
Report.create(date)
# Read in domains and agencies from domains.csv.
# Returns dicts of values ready for saving as Domain and Agency objects.
domains, agencies = load_domain_data()
# Read in domain-scan CSV data.
scan_data = load_scan_data(domains)
# Pull out a few inspect.csv fields as general domain metadata.
for domain_name in scan_data.keys():
inspect = scan_data[domain_name].get('inspect', None)
if inspect is None:
# generally means scan was on different domains.csv, but
# invalid domains can hit this (e.g. fed.us).
print("[%s][WARNING] No inspect data for domain!" % domain_name)
# Remove the domain from further consideration.
del domains[domain_name]
else:
# print("[%s] Updating with inspection metadata." % domain_name)
domains[domain_name]['live'] = boolean_for(inspect['Live'])
domains[domain_name]['redirect'] = boolean_for(inspect['Redirect'])
domains[domain_name]['canonical'] = inspect['Canonical']
# Save what we've got to the database so far.
for domain_name in domains.keys():
Domain.create(domains[domain_name])
print("[%s] Created." % domain_name)
for agency_name in agencies.keys():
Agency.create(agencies[agency_name])
# print("[%s] Created." % agency_name)
# Calculate high-level per-domain conclusions for each report.
domain_reports = process_domains(domains, agencies, scan_data)
# Save them in the database.
for report_type in domain_reports.keys():
for domain_name in domain_reports[report_type].keys():
print("[%s][%s] Adding report." % (report_type, domain_name))
Domain.add_report(domain_name, report_type, domain_reports[report_type][domain_name])
# Calculate agency-level summaries.
update_agency_totals()
# Create top-level summaries.
reports = latest_reports()
for report in reports:
Report.update(report)
print_report()
def update_agency_totals():
all_agencies = Agency.all()
# For each agency, update their report counts for every domain they have.
for agency in all_agencies:
# TODO: Do direct DB queries for answers, rather than iterating.
# Analytics
eligible = Domain.eligible_for_agency(agency['slug'], 'analytics')
agency_report = {
'eligible': len(eligible),
'participating': 0
}
for domain in eligible:
report = domain['analytics']
if report['participating'] == True:
agency_report['participating'] += 1
print("[%s][%s] Adding report." % (agency['slug'], 'analytics'))
Agency.add_report(agency['slug'], 'analytics', agency_report)
# HTTPS
eligible = Domain.eligible_for_agency(agency['slug'], 'https')
agency_report = {
'eligible': len(eligible),
'uses': 0,
'enforces': 0,
'hsts': 0,
'grade': 0
}
for domain in eligible:
report = domain['https']
# Needs to be enabled, with issues is allowed
if report['uses'] >= 1:
agency_report['uses'] += 1
# Needs to be Default or Strict to be 'Yes'
if report['enforces'] >= 2:
agency_report['enforces'] += 1
# Needs to be present with >= 1 year max-age for canonical endpoint
if report['hsts'] >= 2:
agency_report['hsts'] += 1
# Needs to be A- or above
if report['grade'] >= 4:
agency_report['grade'] += 1
print("[%s][%s] Adding report." % (agency['slug'], 'https'))
Agency.add_report(agency['slug'], 'https', agency_report)
def update_agency_totals():
all_agencies = Agency.all()
# For each agency, update their report counts for every domain they have.
for agency in all_agencies:
# TODO: Do direct DB queries for answers, rather than iterating.
# Analytics
eligible = Domain.eligible_for_agency(agency['slug'], 'analytics')
agency_report = {
'eligible': len(eligible),
'participating': 0
}
for domain in eligible:
report = domain['analytics']
if report['participating'] == True:
agency_report['participating'] += 1
print("[%s][%s] Adding report." % (agency['slug'], 'analytics'))
Agency.add_report(agency['slug'], 'analytics', agency_report)
# HTTPS
eligible = Domain.eligible_for_agency(agency['slug'], 'https')
agency_report = {
'eligible': len(eligible),
'uses': 0,
'enforces': 0,
'hsts': 0,
'grade': 0
}
for domain in eligible:
report = domain['https']
# Needs to be enabled, with issues is allowed
if report['uses'] >= 1:
agency_report['uses'] += 1
# Needs to be Default or Strict to be 'Yes'
if report['enforces'] >= 2:
agency_report['enforces'] += 1
# Needs to be at least partially present
if report['hsts'] >= 1:
agency_report['hsts'] += 1
# Needs to be A- or above
if report['grade'] >= 4:
agency_report['grade'] += 1
print("[%s][%s] Adding report." % (agency['slug'], 'https'))
Agency.add_report(agency['slug'], 'https', agency_report)
def test_agency_with_user_no_incident_report(self):
Agency.insert_agencies()
agency = Agency.get_agency_by_name('SEPTA')
u1 = User(email='*****@*****.**', password='******')
u2 = User(email='*****@*****.**', password='******')
agency.users = [u1, u2]
self.assertEqual(agency.name, 'SEPTA')
self.assertTrue(agency.is_official)
self.assertFalse(agency.is_public)
self.assertEqual(agency.users, [u1, u2])
def test_agency_with_incident_report_no_user(self):
Agency.insert_agencies()
agency = Agency.get_agency_by_name('SEPTA')
incident1 = IncidentReport(description='Truck idling on the road!',
send_email_upon_creation=False)
incident2 = IncidentReport(description='Another one!',
send_email_upon_creation=False)
agency.incident_reports = [incident1, incident2]
self.assertEqual(agency.name, 'SEPTA')
self.assertTrue(agency.is_official)
self.assertFalse(agency.is_public)
self.assertEqual(agency.incident_reports, [incident1, incident2])
def post(self, agency_id):
if agency_id:
agency = Agency.query.get(agency_id)
else:
agency = Agency()
db.session.add(agency)
form = AgencyForm(request.form, agency, csrf_enabled=False)
if form.validate_on_submit():
form.populate_obj(agency)
db.session.commit()
flash('Agency updated.', 'success')
return redirect(url_for('agencies.agencies'))
return render_template('form.html', form=form, agency=agency)
def agencies(self, app):
with app.app_context():
db.session.add(
Agency(
id=1,
name='Department of Schmidt',
domain='ng.gov.au',
whitelisted=True,
body_type='cc',
reports=False
)
)
db.session.commit()
yield db.session.query(Agency).all()
def get_or_add_agency(self, domain):
from app.tasks import publish_tasks
domain = domain.lower()
agency = self.get_agency_by_domain(domain)
if not agency:
agency = self.save(
Agency(name=domain,
domain=domain,
category='Commonwealth',
state='ACT',
whitelisted=True,
body_type='other',
reports=True,
domains=[AgencyDomain(domain=domain, active=True)]))
publish_tasks.agency.delay(publish_tasks.compress_agency(agency),
'created')
return agency
def setup_dev():
"""Runs the set-up needed for local development."""
setup_general()
# Create a default admin user
admin = User(email='*****@*****.**',
phone_number='+12345678910',
password='******',
first_name='Admin',
last_name='User',
role=Role.query.filter_by(permissions=Permission.ADMINISTER)
.first(),
confirmed=True)
# Create a default agency worker user
worker = User(email='*****@*****.**',
phone_number='+11098764321',
password='******',
first_name='AgencyWorker',
last_name='User',
role=Role.query
.filter_by(permissions=Permission.AGENCY_WORKER)
.first(),
confirmed=True)
worker.agencies = [Agency.get_agency_by_name('SEPTA')]
# Create a default general user
general = User(email='*****@*****.**',
phone_number='+15434549876',
password='******',
first_name='General',
last_name='User',
role=Role.query.filter_by(permissions=Permission.GENERAL)
.first(),
confirmed=True)
db.session.add(admin)
db.session.add(worker)
db.session.add(general)
db.session.commit()
def run(date):
if date is None:
date = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
# Reset the database.
print("Clearing the database.")
models.clear_database()
Report.create(date)
# Read in domains and agencies from domains.csv.
# Returns dicts of values ready for saving as Domain and Agency objects.
domains, agencies = load_domain_data()
# Read in domain-scan CSV data.
scan_data = load_scan_data(domains)
# Load in some manual exclusion data.
analytics_ineligible = yaml.safe_load(
open(os.path.join(this_dir, "ineligible/analytics.yml")))
analytics_ineligible_map = {}
for domain in analytics_ineligible:
analytics_ineligible_map[domain] = True
# Pull out a few pshtt.csv fields as general domain metadata.
for domain_name in scan_data.keys():
analytics = scan_data[domain_name].get('analytics', None)
if analytics:
ineligible = analytics_ineligible_map.get(domain_name, False)
domains[domain_name]['exclude']['analytics'] = ineligible
pshtt = scan_data[domain_name].get('pshtt', None)
if pshtt is None:
# generally means scan was on different domains.csv, but
# invalid domains can hit this.
print("[%s][WARNING] No pshtt data for domain!" % domain_name)
# Remove the domain from further consideration.
# Destructive, so have this done last.
del domains[domain_name]
else:
# print("[%s] Updating with pshtt metadata." % domain_name)
domains[domain_name]['live'] = boolean_for(pshtt['Live'])
domains[domain_name]['redirect'] = boolean_for(pshtt['Redirect'])
domains[domain_name]['canonical'] = pshtt['Canonical URL']
# Save what we've got to the database so far.
for domain_name in domains.keys():
Domain.create(domains[domain_name])
print("[%s] Created." % domain_name)
for agency_name in agencies.keys():
Agency.create(agencies[agency_name])
# print("[%s] Created." % agency_name)
# Calculate high-level per-domain conclusions for each report.
domain_reports = process_domains(domains, agencies, scan_data)
# Save them in the database.
for report_type in domain_reports.keys():
for domain_name in domain_reports[report_type].keys():
print("[%s][%s] Adding report." % (report_type, domain_name))
Domain.add_report(domain_name, report_type,
domain_reports[report_type][domain_name])
# Calculate agency-level summaries.
update_agency_totals()
# Create top-level summaries.
reports = latest_reports()
for report in reports:
Report.update(report)
print_report()
def run(date):
if date is None:
date = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
# Reset the database.
print("Clearing the database.")
models.clear_database()
Report.create(date)
# Read in domains and agencies from domains.csv.
# Returns dicts of values ready for saving as Domain and Agency objects.
domains, agencies = load_domain_data()
# Read in domain-scan CSV data.
scan_data = load_scan_data(domains)
# Load in some manual exclusion data.
analytics_ineligible = yaml.safe_load(open(os.path.join(this_dir, "ineligible/analytics.yml")))
analytics_ineligible_map = {}
for domain in analytics_ineligible:
analytics_ineligible_map[domain] = True
# Pull out a few pshtt.csv fields as general domain metadata.
for domain_name in scan_data.keys():
analytics = scan_data[domain_name].get('analytics', None)
if analytics:
ineligible = analytics_ineligible_map.get(domain_name, False)
domains[domain_name]['exclude']['analytics'] = ineligible
pshtt = scan_data[domain_name].get('pshtt', None)
if pshtt is None:
# generally means scan was on different domains.csv, but
# invalid domains can hit this.
print("[%s][WARNING] No pshtt data for domain!" % domain_name)
# Remove the domain from further consideration.
# Destructive, so have this done last.
del domains[domain_name]
else:
# print("[%s] Updating with pshtt metadata." % domain_name)
domains[domain_name]['live'] = boolean_for(pshtt['Live'])
domains[domain_name]['redirect'] = boolean_for(pshtt['Redirect'])
domains[domain_name]['canonical'] = pshtt['Canonical URL']
# Save what we've got to the database so far.
for domain_name in domains.keys():
Domain.create(domains[domain_name])
print("[%s] Created." % domain_name)
for agency_name in agencies.keys():
Agency.create(agencies[agency_name])
# print("[%s] Created." % agency_name)
# Calculate high-level per-domain conclusions for each report.
domain_reports = process_domains(domains, agencies, scan_data)
# Save them in the database.
for report_type in domain_reports.keys():
for domain_name in domain_reports[report_type].keys():
print("[%s][%s] Adding report." % (report_type, domain_name))
Domain.add_report(domain_name, report_type, domain_reports[report_type][domain_name])
# Calculate agency-level summaries.
update_agency_totals()
# Create top-level summaries.
reports = latest_reports()
for report in reports:
Report.update(report)
print_report()
def update_agency_totals():
all_agencies = Agency.all()
# For each agency, update their report counts for every domain they have.
for agency in all_agencies:
# TODO: Do direct DB queries for answers, rather than iterating.
# Analytics
eligible = Domain.eligible_for_agency(agency['slug'], 'analytics')
agency_report = {'eligible': len(eligible), 'participating': 0}
for domain in eligible:
report = domain['analytics']
if report['participating'] == True:
agency_report['participating'] += 1
print("[%s][%s] Adding report." % (agency['slug'], 'analytics'))
Agency.add_report(agency['slug'], 'analytics', agency_report)
# HTTPS
eligible = Domain.eligible_for_agency(agency['slug'], 'https')
eligible_https = list(map(lambda w: w['https'], eligible))
agency_report = total_https_report(eligible_https)
# agency_report['subdomains'] = total_https_subdomain_report(eligible)
print("[%s][%s] Adding report." % (agency['slug'], 'https'))
Agency.add_report(agency['slug'], 'https', agency_report)
# A11Y
eligible = Domain.eligible_for_agency(agency['slug'], 'a11y')
pages_count = len(eligible)
errors = {e: 0 for e in A11Y_ERRORS.values()}
for domain in eligible:
a11y = domain['a11y']
for error in a11y['errorlist']:
errors[error] += a11y['errorlist'][error]
total_errors = sum(errors.values())
avg_errors_per_page = ('n/a' if pages_count == 0 else round(
float(total_errors) / pages_count, 2))
agency_report = {
'pages_count': pages_count,
'eligible': pages_count,
'Average Errors per Page': avg_errors_per_page
}
if pages_count:
averages = ({
e: round(mean([d['a11y']['errorlist'][e] for d in eligible]),
2)
for e in A11Y_ERRORS.values()
})
else:
averages = {e: 'n/a' for e in A11Y_ERRORS.values()}
agency_report.update(averages)
print("[%s][%s] Adding report." % (agency['slug'], 'a11y'))
Agency.add_report(agency['slug'], 'a11y', agency_report)
# Customer satisfaction
eligible = Domain.eligible_for_agency(agency['slug'], 'cust_sat')
agency_report = {'eligible': len(eligible), 'participating': 0}
agency_report['participating'] += len(
[d for d in eligible if d['cust_sat']['participating']])
print("[%s][%s] Adding report." % (agency['slug'], 'cust_sat'))
Agency.add_report(agency['slug'], 'cust_sat', agency_report)
def test_agency_case_insensitive(self):
db.session.add(Agency(name='sEpTa'))
db.session.commit()
agency = Agency.get_agency_by_name('septa')
self.assertEqual(agency.name, 'SEPTA')
def run(date, options):
if date is None:
date = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
# Read in domains and agencies from domains.csv.
# Returns dicts of values ready for saving as Domain and Agency objects.
#
# Also returns gathered subdomains, which need more filtering to be useful.
domains, agencies, gathered_subdomains = load_domain_data()
# Read in domain-scan CSV data.
parent_scan_data = load_parent_scan_data(domains)
subdomains, subdomain_scan_data = load_subdomain_scan_data(
domains, parent_scan_data, gathered_subdomains)
# Load in some manual exclusion data.
analytics_ineligible = yaml.safe_load(
open(os.path.join(this_dir, "ineligible/analytics.yml")))
analytics_ineligible_map = {}
for domain in analytics_ineligible:
analytics_ineligible_map[domain] = True
# Capture manual exclusions and pull out some high-level data from pshtt.
for domain_name in parent_scan_data.keys():
# mark manual ineligiblity for analytics if present
analytics = parent_scan_data[domain_name].get('analytics', None)
if analytics:
ineligible = analytics_ineligible_map.get(domain_name, False)
domains[domain_name]['exclude']['analytics'] = ineligible
# Pull out a few pshtt.csv fields as general domain-level metadata.
pshtt = parent_scan_data[domain_name].get('pshtt', None)
if pshtt is None:
# generally means scan was on different domains.csv, but
# invalid domains can hit this.
print("[%s][WARNING] No pshtt data for domain!" % domain_name)
# Remove the domain from further consideration.
# Destructive, so have this done last.
del domains[domain_name]
else:
# print("[%s] Updating with pshtt metadata." % domain_name)
domains[domain_name]['live'] = boolean_for(pshtt['Live'])
domains[domain_name]['redirect'] = boolean_for(pshtt['Redirect'])
domains[domain_name]['canonical'] = pshtt['Canonical URL']
# Prepare subdomains the same way
for subdomain_name in subdomain_scan_data.keys():
pshtt = subdomain_scan_data[subdomain_name].get('pshtt')
subdomains[subdomain_name]['live'] = boolean_for(pshtt['Live'])
subdomains[subdomain_name]['redirect'] = boolean_for(pshtt['Redirect'])
subdomains[subdomain_name]['canonical'] = pshtt['Canonical URL']
# Save what we've got to the database so far.
sorted_domains = list(domains.keys())
sorted_domains.sort()
sorted_subdomains = list(subdomains.keys())
sorted_subdomains.sort()
sorted_agencies = list(agencies.keys())
sorted_agencies.sort()
# Calculate high-level per-domain conclusions for each report.
# Overwrites `domains` and `subdomains` in-place.
process_domains(domains, agencies, subdomains, parent_scan_data,
subdomain_scan_data)
# Reset the database.
print("Clearing the database.")
models.clear_database()
# Calculate agency-level summaries. Updates `agencies` in-place.
update_agency_totals(agencies, domains, subdomains)
# Calculate government-wide summaries.
report = full_report(domains, subdomains)
report['report_date'] = date
print("Creating all domains.")
Domain.create_all(domains[domain_name] for domain_name in sorted_domains)
print("Creating all subdomains.")
Domain.create_all(subdomains[subdomain_name]
for subdomain_name in sorted_subdomains)
print("Creating all agencies.")
Agency.create_all(agencies[agency_name] for agency_name in sorted_agencies)
# Create top-level summaries.
print("Creating government-wide totals.")
Report.create(report)
# Print and exit
print_report(report)
def update_agency_totals():
all_agencies = Agency.all()
# For each agency, update their report counts for every domain they have.
for agency in all_agencies:
# TODO: Do direct DB queries for answers, rather than iterating.
# Analytics
eligible = Domain.eligible_for_agency(agency['slug'], 'analytics')
agency_report = {
'eligible': len(eligible),
'participating': 0
}
for domain in eligible:
report = domain['analytics']
if report['participating'] == True:
agency_report['participating'] += 1
print("[%s][%s] Adding report." % (agency['slug'], 'analytics'))
Agency.add_report(agency['slug'], 'analytics', agency_report)
# HTTPS
eligible = Domain.eligible_for_agency(agency['slug'], 'https')
eligible_https = list(map(lambda w: w['https'], eligible))
agency_report = total_https_report(eligible_https)
# agency_report['subdomains'] = total_https_subdomain_report(eligible)
print("[%s][%s] Adding report." % (agency['slug'], 'https'))
Agency.add_report(agency['slug'], 'https', agency_report)
# A11Y
eligible = Domain.eligible_for_agency(agency['slug'], 'a11y')
pages_count = len(eligible)
errors = {e:0 for e in A11Y_ERRORS.values()}
for domain in eligible:
a11y = domain['a11y']
for error in a11y['errorlist']:
errors[error] += a11y['errorlist'][error]
total_errors = sum(errors.values())
avg_errors_per_page = (
'n/a' if pages_count == 0 else round(float(total_errors) / pages_count, 2)
)
agency_report = {
'pages_count': pages_count,
'eligible': pages_count,
'Average Errors per Page': avg_errors_per_page
}
if pages_count:
averages = ({
e: round(mean([d['a11y']['errorlist'][e] for d in eligible]), 2)
for e in A11Y_ERRORS.values()
})
else:
averages = {e: 'n/a' for e in A11Y_ERRORS.values()}
agency_report.update(averages)
print("[%s][%s] Adding report." % (agency['slug'], 'a11y'))
Agency.add_report(agency['slug'], 'a11y', agency_report)
# Customer satisfaction
eligible = Domain.eligible_for_agency(agency['slug'], 'cust_sat')
agency_report = {
'eligible': len(eligible),
'participating': 0
}
agency_report['participating'] += len([d for d in eligible if
d['cust_sat']['participating']])
print("[%s][%s] Adding report." % (agency['slug'], 'cust_sat'))
Agency.add_report(agency['slug'], 'cust_sat', agency_report)
def test_agency_no_user_no_incident_report(self):
Agency.insert_agencies()
agency = Agency.get_agency_by_name('SEPTA')
self.assertEqual(agency.name, 'SEPTA')
self.assertTrue(agency.is_official)
self.assertFalse(agency.is_public)
def setup_general():
"""Runs the set-up needed for both local development and production."""
Role.insert_roles()
Agency.insert_agencies()
EditableHTML.add_default_faq()
def parse_to_db(db, filename):
"""Reads a csv and imports the data into a database."""
# The indices in the csv of different data
vehicle_id_index = 8
license_plate_index = 9
location_index = 4
date_index = 0
agency_index = 6
picture_index = 13
description_index = 11
validator_form = IncidentReportForm()
with open(filename, 'rb') as csv_file:
reader = csv.reader(csv_file)
columns = reader.next()
for i, row in enumerate(reader, start=2): # i is the row number
address_text = row[location_index]
coords = geocode(address_text)
# Ignore rows that do not have correct geocoding
if coords[0] is None or coords[1] is None:
print_error(i, 'Failed to geocode "{:s}"'.format(address_text))
# Insert correctly geocoded row to database
else:
loc = Location(
latitude=coords[0],
longitude=coords[1],
original_user_text=address_text)
db.session.add(loc)
time1, time2 = parse_start_end_time(date_index, row)
# Assign correct agency id
agency_name = row[agency_index].rstrip()
if agency_name.upper() == 'OTHER':
agency_name = row[agency_index + 1].rstrip()
agency = Agency.get_agency_by_name(agency_name)
# Create new agency object if not in database
if agency is None:
agency = Agency(name=agency_name)
agency.is_public = True
agency.is_official = False
db.session.add(agency)
db.session.commit()
vehicle_id_text = row[vehicle_id_index].strip()
license_plate_text = row[license_plate_index].strip()
# If the license plate is too short, just ignore it
if len(strip_non_alphanumeric_chars(license_plate_text)) < 3:
license_plate_text = ''
# Validate all the fields
validate_field = functools.partial(
validate_field_partial,
form=validator_form,
row_number=i
)
errors = 0
if not validate_field(
field=validator_form.vehicle_id,
data=vehicle_id_text
):
errors += 1
if not validate_field(
field=validator_form.description,
data=row[description_index]
):
errors += 1
if not validate_field(
field=validator_form.picture_url,
data=row[picture_index]
):
errors += 1
if errors == 0:
vehicle_id_text = strip_non_alphanumeric_chars(
vehicle_id_text)
license_plate_text = strip_non_alphanumeric_chars(
license_plate_text)
incident = IncidentReport(
vehicle_id=vehicle_id_text if len(vehicle_id_text) > 0
else None,
license_plate=license_plate_text if
len(license_plate_text) > 0 else None,
location=loc,
date=time1,
duration=time2 - time1,
agency=agency,
picture_url=row[picture_index],
description=row[description_index],
send_email_upon_creation=False,
)
db.session.add(incident)
db.session.commit()
return columns
