class ChartBlock(SurrogatePK, Model):
__tablename__ = 'chart_blocks'
department_id = Column(db.Integer, db.ForeignKey('departments.id'), nullable=False)
title = Column(db.String(255), unique=False, nullable=False)
caption = Column(db.String(255), unique=False, nullable=True)
slug = Column(db.String(255), unique=False, nullable=False)
dataset = Column(db.String(255), unique=False, nullable=False)
content = Column(db.Text, unique=False, nullable=True)
date_updated = Column(db.DateTime, nullable=True)
date_edited = Column(db.DateTime, nullable=True)
order = Column(db.Integer, unique=False, nullable=True)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
class DemographicValue(SurrogatePK, Model):
__tablename__ = "demographic_values"
department_id = Column(db.Integer, db.ForeignKey('departments.id'), nullable=False)
race = Column(db.String(255), unique=False, nullable=True)
count = Column(db.Integer, unique=False, nullable=True)
department_value = Column(db.Boolean, nullable=True)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
class Interested(SurrogatePK, Model):
__tablename__ = 'interesteds'
name = Column(db.String(255), unique=False, nullable=False)
agency = Column(db.String(255), unique=False, nullable=False)
location = Column(db.String(255), unique=False, nullable=False)
phone = Column(db.String(255), unique=False, nullable=False)
email = Column(db.String(255), unique=False, nullable=False)
comments = Column(db.String(255), unique=False, nullable=False)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
class Department(SurrogatePK, Model):
__tablename__ = 'departments'
id = Column(db.Integer, primary_key=True, index=True)
name = Column(db.String(80), unique=True, nullable=False)
short_name = Column(db.String(80), unique=True, nullable=False)
is_public = Column(db.Boolean, default=True, nullable=False)
is_public_use_of_force_incidents = Column(db.Boolean,
default=True,
nullable=False)
is_public_citizen_complaints = Column(db.Boolean,
default=True,
nullable=False)
is_public_officer_involved_shootings = Column(db.Boolean,
default=True,
nullable=False)
is_public_assaults_on_officers = Column(db.Boolean,
default=True,
nullable=False)
invite_codes = relationship("Invite_Code", backref="department")
users = relationship("User",
secondary=user_department_relationship_table,
backref="departments")
chart_blocks = relationship("ChartBlock", backref="department")
denominator_values = relationship("DenominatorValue", backref="department")
demographic_values = relationship("DemographicValue", backref="department")
def __init__(self, name, load_defaults=True, **kwargs):
db.Model.__init__(self, name=name, **kwargs)
if load_defaults:
for default_chart_block in ChartBlockDefaults.defaults:
self.chart_blocks.append(copy.deepcopy(default_chart_block))
self.save()
def get_uof_blocks(self):
blocks = PageBlockLookup.get_uof_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_ois_blocks(self):
blocks = PageBlockLookup.get_ois_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_complaint_blocks(self):
blocks = PageBlockLookup.get_complaints_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_assaults_blocks(self):
blocks = PageBlockLookup.get_assaults_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_complaint_schema_blocks(self):
blocks = PageBlockLookup.get_complaint_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_uof_schema_blocks(self):
blocks = PageBlockLookup.get_uof_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_ois_schema_blocks(self):
blocks = PageBlockLookup.get_ois_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_assaults_schema_blocks(self):
blocks = PageBlockLookup.get_assaults_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_introduction_blocks(self):
return dict([(block.slug, block) for block in self.chart_blocks
if block.dataset in ["introduction"]])
def get_raw_department_demographics(self):
return [v for v in self.demographic_values if v.department_value]
def get_raw_city_demographics(self):
return [v for v in self.demographic_values if not v.department_value]
def get_city_demographics(self):
result = []
demographic_values = [
v for v in self.demographic_values if not v.department_value
]
total = 0
for value in demographic_values:
total += value.count
for value in demographic_values:
result.append({
"gender":
value.gender,
"race":
value.race,
"count":
value.count,
"percent":
"{0:.0f}%".format(value.count / total * 100)
})
return result
def serialize_demographics(self):
results = []
for v in self.demographic_values:
results.append({
'race':
v.race,
'count':
v.count,
'entity':
'department' if v.department_value else 'city'
})
return json.dumps(results)
def get_extractor(self):
extractors = list(filter(lambda u: u.type == "extractors", self.users))
return extractors[0] if extractors else None
def get_block_by_slug(self, slug):
next_block = None
try:
next_block = next(b for b in self.chart_blocks if b.slug == slug)
except StopIteration:
abort(500)
return next_block
def get_blocks_by_slugs(self, slugs, sort_by_order=False):
''' Get chart blocks matching the passed list of slugs
'''
arr = []
if sort_by_order:
arr = [b for b in self.chart_blocks if b.slug in slugs]
try:
arr.sort(key=lambda k: k.order)
except TypeError:
pass
# return the blocks in the order the slugs were passed
else:
for b in slugs:
block = ChartBlock.query.filter_by(department_id=self.id,
slug=b).first()
if block:
arr.append(block)
return arr
def get_blocks_by_slug_startswith(self, partial_slug):
arr = [b for b in self.chart_blocks if b.slug.startswith(partial_slug)]
try:
arr.sort(key=lambda k: k.order)
except TypeError:
pass
return arr
def __repr__(self):
return '<Department({name})>'.format(name=self.name)
def get_uof_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
uof_class = getattr(importlib.import_module("comport.data.models"),
"UseOfForceIncident{}".format(self.short_name))
csv_schema = uof_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
use_of_force_incidents = uof_class.query.all()
for incident in use_of_force_incidents:
values = []
for incident_var in csv_vars:
incident_value = getattr(incident, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_ois_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
ois_class = getattr(
importlib.import_module("comport.data.models"),
"OfficerInvolvedShooting{}".format(self.short_name))
csv_schema = ois_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
officer_involved_shootings = ois_class.query.all()
for incident in officer_involved_shootings:
values = []
for incident_var in csv_vars:
incident_value = getattr(incident, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_complaint_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
complaint_class = getattr(
importlib.import_module("comport.data.models"),
"CitizenComplaint{}".format(self.short_name))
csv_schema = complaint_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
complaints = complaint_class.query.all()
for complaint in complaints:
values = []
for incident_var in csv_vars:
incident_value = getattr(complaint, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_assaults_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
assaults_class = getattr(
importlib.import_module("comport.data.models"),
"AssaultOnOfficer{}".format(self.short_name))
csv_schema = assaults_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
incidents = assaults_class.query.all()
for incident in incidents:
values = []
for incident_var in csv_vars:
incident_value = getattr(incident, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_demographic_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["race", "count", "cityOrDepartment"])
values = sorted(self.demographic_values,
key=lambda x: (x.department_value, x.race))
for value in values:
cityOrDepartment = "department" if value.department_value else "city"
row = [value.race, value.count, cityOrDepartment]
writer.writerow(row)
return output.getvalue()
def get_denominator_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["year", "month", "officers out on service"])
values = sorted(self.denominator_values,
key=lambda x: (x.year, x.month))
for value in values:
row = [value.year, value.month, value.officers_out_on_service]
writer.writerow(row)
return output.getvalue()
class Department(SurrogatePK, Model):
__tablename__ = 'departments'
id = Column(db.Integer, primary_key=True, index=True)
name = Column(db.String(80), unique=True, nullable=False)
short_name = Column(db.String(80), unique=True, nullable=False)
is_public = Column(db.Boolean, default=True, nullable=False)
is_public_use_of_force_incidents = Column(db.Boolean,
default=True,
nullable=False)
is_public_citizen_complaints = Column(db.Boolean,
default=True,
nullable=False)
is_public_officer_involved_shootings = Column(db.Boolean,
default=True,
nullable=False)
is_public_assaults_on_officers = Column(db.Boolean,
default=True,
nullable=False)
invite_codes = relationship("Invite_Code", backref="department")
users = relationship("User",
secondary=user_department_relationship_table,
backref="departments")
chart_blocks = relationship("ChartBlock", backref="department")
denominator_values = relationship("DenominatorValue", backref="department")
demographic_values = relationship("DemographicValue", backref="department")
def __init__(self, name, load_defaults=True, **kwargs):
db.Model.__init__(self, name=name, **kwargs)
if load_defaults:
for default_chart_block in ChartBlockDefaults.defaults:
self.chart_blocks.append(copy.deepcopy(default_chart_block))
self.save()
@classmethod
def get_dataset_lookup(cls, dataset_name):
''' Look up the name for particular aspects of a dataset.
'''
lookup = [{
"in": ["complaints", "citizen_complaints"],
"var_suffix": "citizen_complaints",
"class_prefix": "CitizenComplaint",
"path": "department.public_complaints"
}, {
"in": ["uof", "use_of_force_incidents"],
"var_suffix": "use_of_force_incidents",
"class_prefix": "UseOfForceIncident",
"path": "department.public_uof"
}, {
"in": ["ois", "officer_involved_shootings"],
"var_suffix": "officer_involved_shootings",
"class_prefix": "OfficerInvolvedShooting",
"path": "department.public_ois"
}, {
"in": ["assaults", "assaults_on_officers"],
"var_suffix": "assaults_on_officers",
"class_prefix": "AssaultOnOfficer",
"path": "department.public_assaults"
}]
found = False
for check in lookup:
if dataset_name in check["in"]:
var_suffix = check["var_suffix"]
class_prefix = check["class_prefix"]
path = check["path"]
found = True
break
if not found:
return {}
return {
"var_suffix": var_suffix,
"class_prefix": class_prefix,
"path": path
}
def dataset_is_public_and_has_data(self, dataset_name):
''' Return true if the dataset is public and has data.
'''
# look up what dataset we're looking for
lookup = self.get_dataset_lookup(dataset_name)
# if we didn't recognize it, just return false
if not lookup:
return False
# get the current is_public value
is_public = getattr(self, "is_public_{}".format(lookup["var_suffix"]))
# check to see whether the model exists
try:
model_class = getattr(
importlib.import_module("comport.data.models"),
"{}{}".format(lookup["class_prefix"], self.short_name))
except AttributeError:
# this department doesn't have this dataset
return False
# check to see whether there's data in the dataset
first_record = model_class.query.first()
return (is_public and first_record is not None)
def get_uof_blocks(self):
blocks = PageBlockLookup.get_uof_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_ois_blocks(self):
blocks = PageBlockLookup.get_ois_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_complaint_blocks(self):
blocks = PageBlockLookup.get_complaints_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_assaults_blocks(self):
blocks = PageBlockLookup.get_assaults_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'first-block': self.get_block_by_slug(blocks['first-block']),
'blocks': self.get_blocks_by_slugs(blocks['blocks'])
}
def get_complaint_schema_blocks(self):
blocks = PageBlockLookup.get_complaint_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_uof_schema_blocks(self):
blocks = PageBlockLookup.get_uof_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_ois_schema_blocks(self):
blocks = PageBlockLookup.get_ois_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_assaults_schema_blocks(self):
blocks = PageBlockLookup.get_assaults_schema_blocks(self.short_name)
return {
'introduction': self.get_block_by_slug(blocks['introduction']),
'footer': self.get_block_by_slug(blocks['footer']),
'disclaimer': self.get_block_by_slug(blocks['disclaimer']),
'blocks': self.get_blocks_by_slug_startswith(blocks['blocks'])
}
def get_introduction_blocks(self):
return dict([(block.slug, block) for block in self.chart_blocks
if block.dataset in ["introduction"]])
def get_raw_department_demographics(self):
return [v for v in self.demographic_values if v.department_value]
def get_raw_city_demographics(self):
return [v for v in self.demographic_values if not v.department_value]
def get_city_demographics(self):
result = []
demographic_values = [
v for v in self.demographic_values if not v.department_value
]
total = 0
for value in demographic_values:
total += value.count
for value in demographic_values:
result.append({
"gender":
value.gender,
"race":
value.race,
"count":
value.count,
"percent":
"{0:.0f}%".format(value.count / total * 100)
})
return result
def serialize_demographics(self):
results = []
for v in self.demographic_values:
results.append({
'race':
v.race,
'count':
v.count,
'entity':
'department' if v.department_value else 'city'
})
return json.dumps(results)
def get_extractor(self):
extractors = list(filter(lambda u: u.type == "extractors", self.users))
return extractors[0] if extractors else None
def get_block_by_slug(self, slug):
next_block = None
try:
next_block = next(b for b in self.chart_blocks if b.slug == slug)
except StopIteration:
# no matching chart block was found
return None
return next_block
def get_blocks_by_slugs(self, slugs, sort_by_order=False):
''' Get chart blocks matching the passed list of slugs
'''
arr = []
if sort_by_order:
arr = [b for b in self.chart_blocks if b.slug in slugs]
try:
arr.sort(key=lambda k: k.order)
except TypeError:
pass
# return the blocks in the order the slugs were passed
else:
for b in slugs:
block = ChartBlock.query.filter_by(department_id=self.id,
slug=b).first()
if block:
arr.append(block)
return arr
def get_blocks_by_slug_startswith(self, partial_slug):
arr = [b for b in self.chart_blocks if b.slug.startswith(partial_slug)]
try:
arr.sort(key=lambda k: k.order)
except TypeError:
pass
return arr
def __repr__(self):
return '<Department({name})>'.format(name=self.name)
def get_first_dataset_path(self):
''' Return a string representing the path to the first existing dataset page for this department.
For use in url_for calls.
'''
datasets = ["complaints", "uof", "ois", "assaults"]
for check in datasets:
lookup = self.get_dataset_lookup(check)
# if there's a class for this dataset, return its path
try:
getattr(importlib.import_module("comport.data.models"),
"{}{}".format(lookup["class_prefix"], self.short_name))
except AttributeError:
continue
else:
return lookup["path"]
# no dataset classes were found
return None
def get_uof_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
uof_class = getattr(importlib.import_module("comport.data.models"),
"UseOfForceIncident{}".format(self.short_name))
csv_schema = uof_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
use_of_force_incidents = uof_class.query.all()
for incident in use_of_force_incidents:
values = []
for incident_var in csv_vars:
incident_value = getattr(incident, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_ois_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
ois_class = getattr(
importlib.import_module("comport.data.models"),
"OfficerInvolvedShooting{}".format(self.short_name))
csv_schema = ois_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
officer_involved_shootings = ois_class.query.all()
for incident in officer_involved_shootings:
values = []
for incident_var in csv_vars:
incident_value = getattr(incident, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_complaint_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
complaint_class = getattr(
importlib.import_module("comport.data.models"),
"CitizenComplaint{}".format(self.short_name))
csv_schema = complaint_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
complaints = complaint_class.query.all()
for complaint in complaints:
values = []
for incident_var in csv_vars:
incident_value = getattr(complaint, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_assaults_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
assaults_class = getattr(
importlib.import_module("comport.data.models"),
"AssaultOnOfficer{}".format(self.short_name))
csv_schema = assaults_class.get_csv_schema()
csv_headers = [col[0] for col in csv_schema]
csv_vars = [col[1] for col in csv_schema]
writer.writerow(csv_headers)
incidents = assaults_class.query.all()
for incident in incidents:
values = []
for incident_var in csv_vars:
incident_value = getattr(incident, incident_var)
if incident_var == "occured_date":
incident_value = coalesce_date(incident_value)
values.append(incident_value)
writer.writerow(values)
return output.getvalue()
def get_demographic_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["race", "count", "cityOrDepartment"])
values = sorted(self.demographic_values,
key=lambda x: (x.department_value, x.race))
for value in values:
cityOrDepartment = "department" if value.department_value else "city"
row = [value.race, value.count, cityOrDepartment]
writer.writerow(row)
return output.getvalue()
def get_denominator_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["year", "month", "officers out on service"])
values = sorted(self.denominator_values,
key=lambda x: (x.year, x.month))
for value in values:
row = [value.year, value.month, value.officers_out_on_service]
writer.writerow(row)
return output.getvalue()
class Department(SurrogatePK, Model):
__tablename__ = 'departments'
id = Column(db.Integer, primary_key=True, index=True)
name = Column(db.String(80), unique=True, nullable=False)
short_name = Column(db.String(80), unique=True, nullable=False)
invite_codes = relationship("Invite_Code", backref="department")
users = relationship("User", secondary=user_department_relationship_table, backref="departments")
use_of_force_incidents = relationship(
"UseOfForceIncident", backref="department")
citizen_complaints = relationship("CitizenComplaint", backref="department")
officer_involved_shootings = relationship(
"OfficerInvolvedShooting", backref="department")
chart_blocks = relationship("ChartBlock", backref="department")
denominator_values = relationship("DenominatorValue", backref="department")
demographic_values = relationship("DemographicValue", backref="department")
def __init__(self, name, load_defaults=True, **kwargs):
db.Model.__init__(self, name=name, **kwargs)
if load_defaults:
for default_chart_block in ChartBlockDefaults.defaults:
self.chart_blocks.append(copy.deepcopy(default_chart_block))
self.save()
def get_uof_blocks(self):
return {
'introduction': self.get_block_by_slug('uof-introduction'),
'first-block': self.get_block_by_slug('uof-force-type'),
'blocks': self.get_blocks_by_slugs([
'uof-by-inc-district',
'officer-demographics',
'uof-race'
])
}
def get_ois_blocks(self):
return {
'introduction': self.get_block_by_slug('ois-introduction'),
'first-block': self.get_block_by_slug('ois-by-inc-district'),
'blocks': self.get_blocks_by_slugs([
'ois-weapon-type',
'officer-demographics',
'ois-race',
])
}
def get_complaint_blocks(self):
return {
'introduction': self.get_block_by_slug('complaints-introduction'),
'first-block': self.get_block_by_slug('complaints-by-month'),
'blocks': self.get_blocks_by_slugs([
'complaints-by-allegation',
'complaints-by-allegation-type',
'complaints-by-disposition',
'complaints-by-precinct',
'officer-demographics',
'complaints-by-demographic',
'complaints-by-officer',
])
}
def get_introduction_blocks(self):
return dict([(block.slug, block) for block in self.chart_blocks if block.dataset in ["introduction"]])
def get_raw_department_demographics(self):
return [v for v in self.demographic_values if v.department_value]
def get_raw_city_demographics(self):
return [v for v in self.demographic_values if not v.department_value]
def get_city_demographics(self):
result = []
demographic_values = [
v for v in self.demographic_values if not v.department_value]
total = 0
for value in demographic_values:
total += value.count
for value in demographic_values:
result.append({
"gender": value.gender,
"race": value.race,
"count": value.count,
"percent": "{0:.0f}%".format(value.count / total * 100)
})
return result
def serialize_demographics(self):
results = []
for v in self.demographic_values:
results.append({
'race': v.race,
'count': v.count,
'entity': 'department' if v.department_value else 'city'
})
return json.dumps(results)
def get_extractor(self):
extractors = list(filter(lambda u: u.type == "extractors", self.users))
return extractors[0] if extractors else None
def get_block_by_slug(self, slug):
return next(b for b in self.chart_blocks if b.slug == slug)
def get_blocks_by_slugs(self, slugs):
return [b for b in self.chart_blocks if b.slug in slugs]
def __repr__(self):
return '<Department({name})>'.format(name=self.name)
def get_uof_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["id", "occurredDate", "division", "district", "shift", "beat",
"useOfForceReason", "officerForceType", "disposition",
"serviceType", "arrestMade", "arrestCharges", "residentInjured",
"residentHospitalized", "residentCondition", "officerInjured",
"officerHospitalized", "officerCondition", "residentRace",
"residentSex", "residentAge", "officerRace", "officerSex",
"officerAge", "officerYearsOfService", "officerIdentifier"])
use_of_force_incidents = self.use_of_force_incidents
for incident in use_of_force_incidents:
occured_date = coalesce_date(incident.occured_date)
values = [
incident.opaque_id,
occured_date,
incident.division,
incident.precinct,
incident.shift,
incident.beat,
incident.use_of_force_reason,
incident.officer_force_type,
incident.disposition,
incident.service_type,
incident.arrest_made,
incident.arrest_charges,
incident.resident_injured,
incident.resident_hospitalized,
incident.resident_condition,
incident.officer_injured,
incident.officer_hospitalized,
incident.officer_condition,
incident.resident_race,
incident.resident_sex,
incident.resident_age,
incident.officer_race,
incident.officer_sex,
incident.officer_age,
incident.officer_years_of_service,
incident.officer_identifier
]
writer.writerow(values)
return output.getvalue()
def get_ois_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["id", "occurredDate", "division", "district", "shift", "beat",
"disposition", "residentWeaponUsed", "officerWeaponUsed",
"serviceType", "residentCondition", "officerCondition",
"residentRace", "residentSex", "residentAge", "officerRace",
"officerSex", "officerAge", "officerYearsOfService",
"officerIdentifier"])
officer_involved_shootings = self.officer_involved_shootings
for incident in officer_involved_shootings:
occured_date = coalesce_date(incident.occured_date)
values = [
incident.opaque_id,
occured_date,
incident.division,
incident.precinct,
incident.shift,
incident.beat,
incident.disposition,
incident.resident_weapon_used,
incident.officer_weapon_used,
incident.service_type,
incident.resident_condition,
incident.officer_condition,
incident.resident_race,
incident.resident_sex,
incident.resident_age,
incident.officer_race,
incident.officer_sex,
incident.officer_age,
incident.officer_years_of_service,
incident.officer_identifier
]
writer.writerow(values)
return output.getvalue()
def get_complaint_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["id", "occurredDate", "division", "district", "shift",
"beat", "serviceType", "source", "allegationType",
"allegation", "finding", "residentRace", "residentSex",
"residentAge", "officerRace", "officerSex", "officerAge",
"officerYearsOfService", "officerIdentifier"])
complaints = self.citizen_complaints
for complaint in complaints:
occured_date = coalesce_date(complaint.occured_date)
values = [
complaint.opaque_id,
occured_date,
complaint.division,
complaint.precinct,
complaint.shift,
complaint.beat,
complaint.service_type,
complaint.source,
complaint.allegation_type,
complaint.allegation,
complaint.disposition,
complaint.resident_race,
complaint.resident_sex,
complaint.resident_age,
complaint.officer_race,
complaint.officer_sex,
complaint.officer_age,
complaint.officer_years_of_service,
complaint.officer_identifier
]
writer.writerow(values)
return output.getvalue()
def get_demographic_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["race", "count", "cityOrDepartment"])
values = sorted(self.demographic_values,
key=lambda x: (x.department_value, x.race))
for value in values:
cityOrDepartment = "department" if value.department_value else "city"
row = [
value.race,
value.count,
cityOrDepartment
]
writer.writerow(row)
return output.getvalue()
def get_denominator_csv(self):
output = io.StringIO()
writer = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(["year", "month", "officers out on service"])
values = sorted(self.denominator_values,
key=lambda x: (x.year, x.month))
for value in values:
row = [
value.year,
value.month,
value.officers_out_on_service
]
writer.writerow(row)
return output.getvalue()
class OfficerInvolvedShootingBPD(SurrogatePK, Model):
__tablename__ = 'officer_involved_shootings_bpd'
department_id = Column(db.Integer, db.ForeignKey('departments.id'), nullable=False)
opaque_id = Column(db.String(255), unique=False, nullable=False)
occured_date = Column(db.DateTime, nullable=True)
bureau = Column(db.String(255), unique=False, nullable=True)
division = Column(db.String(255), unique=False, nullable=True)
assignment = Column(db.String(255), unique=False, nullable=True)
disposition = Column(db.String(255), unique=False, nullable=True)
resident_weapon_used = Column(db.String(255), unique=False, nullable=True)
officer_weapon_used = Column(db.String(255), unique=False, nullable=True)
service_type = Column(db.String(255), unique=False, nullable=True)
resident_condition = Column(db.String(255), unique=False, nullable=True)
officer_condition = Column(db.String(255), unique=False, nullable=True)
resident_identifier = Column(db.String(255), unique=False, nullable=True)
resident_race = Column(db.String(255), unique=False, nullable=True)
resident_sex = Column(db.String(255), unique=False, nullable=True)
resident_age = Column(db.String(255), unique=False, nullable=True)
officer_race = Column(db.String(255), unique=False, nullable=True)
officer_sex = Column(db.String(255), unique=False, nullable=True)
officer_age = Column(db.String(255), unique=False, nullable=True)
officer_years_of_service = Column(db.Integer, unique=False, nullable=True)
officer_identifier = Column(db.String(255), unique=False, nullable=True)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
@classmethod
def get_csv_schema(cls):
''' Get the CSV column headers and variable names.
'''
return [("id", "opaque_id"), ("occurredDate", "occured_date"), ("bureau", "bureau"), ("division", "division"), ("assignment", "assignment"), ("disposition", "disposition"), ("residentWeaponUsed", "resident_weapon_used"), ("officerWeaponUsed", "officer_weapon_used"), ("serviceType", "service_type"), ("residentCondition", "resident_condition"), ("officerCondition", "officer_condition"), ("residentIdentifier", "resident_identifier"), ("residentRace", "resident_race"), ("residentSex", "resident_sex"), ("residentAge", "resident_age"), ("officerRace", "officer_race"), ("officerSex", "officer_sex"), ("officerAge", "officer_age"), ("officerYearsOfService", "officer_years_of_service"), ("officerIdentifier", "officer_identifier")]
@classmethod
def add_or_update_incident(cls, department, incident):
''' Add a new OIS incident or update an existing one
'''
row_added = False
# get a cleaner instance
cleaner = Cleaners()
# capitalize the location
incident["bureau"] = cleaner.capitalize(incident["bureau"])
incident["division"] = cleaner.capitalize(incident["division"])
incident["assignment"] = cleaner.capitalize(incident["assignment"])
# clean weapon, race, gender
incident["residentWeaponUsed"] = cleaner.resident_weapon_used(incident["residentWeaponUsed"])
incident["residentSex"] = cleaner.sex(incident["residentSex"])
incident["residentRace"] = cleaner.race(incident["residentRace"])
incident["officerSex"] = cleaner.sex(incident["officerSex"])
incident["officerRace"] = cleaner.race(incident["officerRace"])
# make sure values that might've been sent as integers are strings
incident["residentAge"] = cleaner.number_to_string(incident["residentAge"])
incident["officerAge"] = cleaner.number_to_string(incident["officerAge"])
# and values that might've been sent as strings are integers
incident["officerYearsOfService"] = cleaner.string_to_integer(incident["officerYearsOfService"])
found_incident = False
# found_incident = cls.query.filter_by(
# opaque_id=incident["opaqueId"],
# department_id=department.id,
# officer_identifier=incident["officerIdentifier"]
# ).first()
if not found_incident:
found_incident = cls.create(
department_id=department.id,
opaque_id=incident["opaqueId"]
)
row_added = True
found_incident.department_id = department.id
found_incident.opaque_id = incident["opaqueId"]
found_incident.occured_date = parse_date(incident["occuredDate"])
found_incident.bureau = incident["bureau"]
found_incident.division = incident["division"]
found_incident.assignment = incident["assignment"]
found_incident.disposition = incident["disposition"]
found_incident.resident_weapon_used = incident["residentWeaponUsed"]
found_incident.officer_weapon_used = incident["officerWeaponUsed"]
found_incident.service_type = incident["serviceType"]
found_incident.resident_condition = incident["residentCondition"]
found_incident.officer_condition = incident["officerCondition"]
found_incident.resident_identifier = incident["residentIdentifier"]
found_incident.resident_race = incident["residentRace"]
found_incident.resident_sex = incident["residentSex"]
found_incident.resident_age = incident["residentAge"]
found_incident.officer_race = incident["officerRace"]
found_incident.officer_sex = incident["officerSex"]
found_incident.officer_age = incident["officerAge"]
found_incident.officer_years_of_service = parse_int(incident["officerYearsOfService"])
found_incident.officer_identifier = incident["officerIdentifier"]
found_incident.save()
return row_added
class CitizenComplaintBPD(SurrogatePK, Model):
__tablename__ = 'citizen_complaints_bpd'
department_id = Column(db.Integer, db.ForeignKey('departments.id'), nullable=False)
opaque_id = Column(db.String(255), unique=False, nullable=False)
occured_date = Column(db.DateTime, nullable=True)
bureau = Column(db.String(255), unique=False, nullable=True)
division = Column(db.String(255), unique=False, nullable=True)
assignment = Column(db.String(255), unique=False, nullable=True)
service_type = Column(db.String(255), unique=False, nullable=True)
source = Column(db.String(255), unique=False, nullable=True)
allegation = Column(db.String(255), unique=False, nullable=True)
disposition = Column(db.String(255), unique=False, nullable=True)
resident_identifier = Column(db.String(255), unique=False, nullable=True)
resident_race = Column(db.String(255), unique=False, nullable=True)
resident_sex = Column(db.String(255), unique=False, nullable=True)
resident_age = Column(db.String(255), unique=False, nullable=True)
officer_identifier = Column(db.String(255), unique=False, nullable=True)
officer_race = Column(db.String(255), unique=False, nullable=True)
officer_sex = Column(db.String(255), unique=False, nullable=True)
officer_age = Column(db.String(255), unique=False, nullable=True)
officer_years_of_service = Column(db.String(255), unique=False, nullable=True)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
@classmethod
def get_csv_schema(cls):
''' Get the CSV column headers and variable names.
'''
return [("id", "opaque_id"), ("occurredDate", "occured_date"), ("bureau", "bureau"), ("division", "division"), ("assignment", "assignment"), ("serviceType", "service_type"), ("source", "source"), ("allegation", "allegation"), ("disposition", "disposition"), ("residentIdentifier", "resident_identifier"), ("residentRace", "resident_race"), ("residentSex", "resident_sex"), ("residentAge", "resident_age"), ("officerIdentifier", "officer_identifier"), ("officerRace", "officer_race"), ("officerSex", "officer_sex"), ("officerAge", "officer_age"), ("officerYearsOfService", "officer_years_of_service")]
@classmethod
def add_or_update_incident(cls, department, incident):
''' Add a new Citizen Complaints incident or update an existing one
'''
row_added = False
# get a cleaner instance
cleaner = Cleaners()
# make sure values that might've been sent as integers are strings
incident["residentAge"] = cleaner.number_to_string(incident["residentAge"])
incident["officerAge"] = cleaner.number_to_string(incident["officerAge"])
incident["officerYearsOfService"] = cleaner.number_to_string(incident["officerYearsOfService"])
# capitalize all the fields in the incident
incident = cleaner.capitalize_incident(incident)
# clean sex & race
incident["residentSex"] = cleaner.sex(incident["residentSex"])
incident["residentRace"] = cleaner.race(incident["residentRace"])
incident["officerSex"] = cleaner.sex(incident["officerSex"])
incident["officerRace"] = cleaner.race(incident["officerRace"])
found_incident = False
# found_incident = cls.query.filter_by(
# opaque_id=incident["opaqueId"],
# allegation=incident["allegation"],
# officer_identifier=incident["officerIdentifier"],
# department_id=department.id,
# resident_race=incident["residentRace"],
# resident_sex=incident["residentSex"],
# resident_age=incident["residentAge"]
# ).first()
if not found_incident:
# check for multiple complainants
# :TODO: validate this practice!
# multiple_complaintant_check = cls.query.filter_by(
# opaque_id=incident["opaqueId"],
# allegation=incident["allegation"],
# officer_identifier=incident["officerIdentifier"],
# department_id=department.id
# ).first()
# if multiple_complaintant_check:
# return None
found_incident = cls.create(
department_id=department.id,
opaque_id=incident["opaqueId"]
)
row_added = True
found_incident.department_id = department.id
found_incident.opaque_id = incident["opaqueId"]
found_incident.occured_date = parse_date(incident["occuredDate"])
found_incident.bureau = incident["bureau"]
found_incident.division = incident["division"]
found_incident.assignment = incident["assignment"]
found_incident.service_type = incident["serviceType"]
found_incident.source = incident["source"]
found_incident.allegation = incident["allegation"]
found_incident.disposition = incident["disposition"]
found_incident.resident_identifier = incident["residentIdentifier"]
found_incident.resident_race = incident["residentRace"]
found_incident.resident_sex = incident["residentSex"]
found_incident.resident_age = incident["residentAge"]
found_incident.officer_identifier = incident["officerIdentifier"]
found_incident.officer_race = incident["officerRace"]
found_incident.officer_sex = incident["officerSex"]
found_incident.officer_age = incident["officerAge"]
found_incident.officer_years_of_service = incident["officerYearsOfService"]
found_incident.save()
return row_added
class UseOfForceIncidentIMPD(SurrogatePK, Model):
__tablename__ = 'use_of_force_incidents_impd'
department_id = Column(db.Integer, db.ForeignKey('departments.id'), nullable=False)
opaque_id = Column(db.String(255), unique=False, nullable=False)
occured_date = Column(db.DateTime, nullable=True)
division = Column(db.String(255), unique=False, nullable=True)
precinct = Column(db.String(255), unique=False, nullable=True)
shift = Column(db.String(255), unique=False, nullable=True)
beat = Column(db.String(255), unique=False, nullable=True)
disposition = Column(db.String(255), unique=False, nullable=True)
census_tract = Column(db.String(255), unique=False, nullable=True)
officer_force_type = Column(db.String(255), unique=False, nullable=True)
resident_resist_type = Column(db.String(255), unique=False, nullable=True)
officer_weapon_used = Column(db.String(255), unique=False, nullable=True)
resident_weapon_used = Column(db.String(255), unique=False, nullable=True)
service_type = Column(db.String(255), unique=False, nullable=True)
arrest_made = Column(db.Boolean, nullable=True)
arrest_charges = Column(db.String(255), unique=False, nullable=True)
resident_injured = Column(db.Boolean, nullable=True)
resident_hospitalized = Column(db.Boolean, nullable=True)
officer_injured = Column(db.Boolean, nullable=True)
officer_hospitalized = Column(db.Boolean, nullable=True)
use_of_force_reason = Column(db.String(255), unique=False, nullable=True)
resident_race = Column(db.String(255), unique=False, nullable=True)
officer_race = Column(db.String(255), unique=False, nullable=True)
resident_sex = Column(db.String(255), unique=False, nullable=True)
officer_sex = Column(db.String(255), unique=False, nullable=True)
officer_identifier = Column(db.String(255), unique=False, nullable=True)
officer_years_of_service = Column(db.String(255), unique=False, nullable=True)
officer_age = Column(db.String(255), unique=False, nullable=True)
resident_age = Column(db.String(255), unique=False, nullable=True)
officer_condition = Column(db.String(255), unique=False, nullable=True)
resident_condition = Column(db.String(255), unique=False, nullable=True)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
@classmethod
def get_csv_schema(cls):
''' Get the CSV column headers and variable names.
'''
return [("id", "opaque_id"), ("occurredDate", "occured_date"), ("division", "division"), ("district", "precinct"), ("shift", "shift"), ("beat", "beat"), ("useOfForceReason", "use_of_force_reason"), ("officerForceType", "officer_force_type"), ("disposition", "disposition"), ("serviceType", "service_type"), ("arrestMade", "arrest_made"), ("arrestCharges", "arrest_charges"), ("residentInjured", "resident_injured"), ("residentHospitalized", "resident_hospitalized"), ("residentCondition", "resident_condition"), ("officerInjured", "officer_injured"), ("officerHospitalized", "officer_hospitalized"), ("officerCondition", "officer_condition"), ("residentRace", "resident_race"), ("residentSex", "resident_sex"), ("residentAge", "resident_age"), ("officerRace", "officer_race"), ("officerSex", "officer_sex"), ("officerAge", "officer_age"), ("officerYearsOfService", "officer_years_of_service"), ("officerIdentifier", "officer_identifier")]
@classmethod
def add_or_update_incident(cls, department, incident):
''' Add a new UOF incident or update an existing one
'''
row_added = False
# get a cleaner instance
cleaner = Cleaners()
# capitalize the location
incident["division"] = cleaner.capitalize(incident["division"])
incident["precinct"] = cleaner.capitalize(incident["precinct"])
incident["shift"] = cleaner.capitalize(incident["shift"])
incident["beat"] = cleaner.capitalize(incident["beat"])
# clean force type, race, gender
incident["officerForceType"] = cleaner.officer_force_type(incident["officerForceType"])
incident["residentRace"] = cleaner.race(incident["residentRace"])
incident["residentSex"] = cleaner.sex(incident["residentSex"])
incident["officerRace"] = cleaner.race(incident["officerRace"])
incident["officerSex"] = cleaner.sex(incident["officerSex"])
# make sure values that might've been sent as integers are strings
incident["residentAge"] = cleaner.number_to_string(incident["residentAge"])
incident["officerAge"] = cleaner.number_to_string(incident["officerAge"])
incident["officerYearsOfService"] = cleaner.number_to_string(incident["officerYearsOfService"])
found_incident = cls.query.filter_by(
opaque_id=incident["opaqueId"],
department_id=department.id,
officer_identifier=incident["officerIdentifier"],
officer_force_type=incident["officerForceType"]
).first()
if not found_incident:
found_incident = cls.create(
department_id=department.id,
opaque_id=incident["opaqueId"])
row_added = True
found_incident.department_id = department.id
found_incident.opaque_id = incident["opaqueId"]
found_incident.occured_date = parse_date(incident["occuredDate"])
found_incident.division = incident["division"]
found_incident.precinct = incident["precinct"]
found_incident.shift = incident["shift"]
found_incident.beat = incident["beat"]
found_incident.disposition = incident["disposition"]
found_incident.census_tract = None
found_incident.officer_force_type = incident["officerForceType"]
found_incident.use_of_force_reason = incident["useOfForceReason"]
found_incident.service_type = incident["serviceType"]
found_incident.arrest_made = incident["arrestMade"]
found_incident.arrest_charges = incident["arrestCharges"]
found_incident.resident_weapon_used = incident["residentWeaponUsed"]
found_incident.resident_injured = incident["residentInjured"]
found_incident.resident_hospitalized = incident["residentHospitalized"]
found_incident.officer_injured = incident["officerInjured"]
found_incident.officer_hospitalized = incident["officerHospitalized"]
found_incident.resident_race = incident["residentRace"]
found_incident.resident_sex = incident["residentSex"]
found_incident.resident_age = incident["residentAge"]
found_incident.resident_condition = incident["residentCondition"]
found_incident.officer_identifier = incident["officerIdentifier"]
found_incident.officer_race = incident["officerRace"]
found_incident.officer_sex = incident["officerSex"]
found_incident.officer_age = incident["officerAge"]
found_incident.officer_years_of_service = incident["officerYearsOfService"]
found_incident.officer_condition = incident["officerCondition"]
found_incident.save()
return row_added
class AssaultOnOfficerIMPD(SurrogatePK, Model):
__tablename__ = 'assaults_on_officers_impd'
department_id = Column(db.Integer, db.ForeignKey('departments.id'), nullable=False)
opaque_id = Column(db.String(255), unique=False, nullable=False)
officer_identifier = Column(db.String(255), unique=False, nullable=True)
service_type = Column(db.String(255), unique=False, nullable=True)
force_type = Column(db.String(255), unique=False, nullable=True)
assignment = Column(db.String(255), unique=False, nullable=True)
arrest_made = Column(db.Boolean, nullable=True)
officer_injured = Column(db.Boolean, nullable=True)
officer_killed = Column(db.Boolean, nullable=True)
report_filed = Column(db.Boolean, nullable=True)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
@classmethod
def get_csv_schema(cls):
''' Get the CSV column headers and variable names.
'''
return [("id", "opaque_id"), ("officerIdentifier", "officer_identifier"), ("serviceType", "service_type"), ("forceType", "force_type"), ("assignment", "assignment"), ("arrestMade", "arrest_made"), ("officerInjured", "officer_injured"), ("officerKilled", "officer_killed"), ("reportFiled", "report_filed")]
@classmethod
def add_or_update_incident(cls, department, incident):
''' Add a new Assaults on Officers incident or update an existing one
'''
row_added = False
# get a cleaner instance
cleaner = Cleaners()
# capitalize all the fields in the incident
incident = cleaner.capitalize_incident(incident)
found_incident = cls.query.filter_by(
department_id=department.id,
opaque_id=incident["opaqueId"],
officer_identifier=incident["officerIdentifier"]
).first()
if not found_incident:
found_incident = cls.create(
department_id=department.id,
opaque_id=incident["opaqueId"]
)
row_added = True
found_incident.department_id = department.id
found_incident.opaque_id = incident["opaqueId"]
found_incident.officer_identifier = incident["officerIdentifier"]
found_incident.service_type = incident["serviceType"]
found_incident.force_type = incident["forceType"]
found_incident.force_type = incident["forceType"]
found_incident.assignment = incident["assignment"]
found_incident.arrest_made = incident["arrestMade"]
found_incident.officer_injured = incident["officerInjured"]
found_incident.officer_killed = incident["officerKilled"]
found_incident.report_filed = incident["reportFiled"]
found_incident.save()
return row_added
