def test_column_retrieve_all_duplicate_error(self):
seed_models.Column.objects.create(column_name='custom_id_1',
table_name='PropertyState',
organization=self.fake_org,
is_extra_data=True)
with self.assertRaisesRegexp(Exception, 'Duplicate name'):
Column.retrieve_all(self.fake_org.pk, 'property', False)
def check_data(self, record_type, rows):
"""
Send in data as a queryset from the Property/Taxlot ids.
:param record_type: one of PropertyState | TaxLotState
:param rows: rows of data to be checked for data quality
:return: None
"""
# grab the columns so we can grab the display names, create lookup tuple for display name
for c in Column.retrieve_all(self.organization, record_type, False):
self.column_lookup[(c['table_name'], c['column_name'])] = c['display_name']
# grab all the rules once, save query time
rules = self.rules.filter(enabled=True, table_name=record_type).order_by('field',
'severity')
# Get the list of the field names that will show in every result
fields = self.get_fieldnames(record_type)
for row in rows:
# Initialize the ID if it does not exist yet. Add in the other
# fields that are of interest to the GUI
if row.id not in self.results:
self.results[row.id] = {}
for field in fields:
self.results[row.id][field] = getattr(row, field)
self.results[row.id]['data_quality_results'] = []
# Run the checks
self._check(rules, row)
# Prune the results will remove any entries that have zero data_quality_results
for k, v in self.results.copy().items():
if not v['data_quality_results']:
del self.results[k]
def shared_fields(self, request, pk=None):
"""
Retrieves all fields marked as shared for the organization. Will only return used fields.
---
parameter_strategy: replace
parameters:
- name: pk
description: Organization ID (Primary key)
type: integer
required: true
paramType: path
response_serializer: SharedFieldsActualReturnSerializer
"""
result = {
'status': 'success',
'public_fields': []
}
columns = Column.retrieve_all(pk, 'property', True)
for c in columns:
if c['sharedFieldType'] == 'Public':
new_column = {
'table_name': c['table_name'],
'name': c['name'],
'column_name': c['column_name'],
# this is the field name in the db. The other name can have tax_
'display_name': c['display_name']
}
result['public_fields'].append(new_column)
return JsonResponse(result)
def test_columns_extra_tag(self):
columns = Column.retrieve_all(self.fake_org.pk, 'taxlot', False)
# go through and delete all the results.ids so that it is easy to do a compare
for result in columns:
del result['id']
del result['name']
del result['organization_id']
c = {
'table_name': 'TaxLotState',
'column_name': 'Gross Floor Area',
'display_name': 'Gross Floor Area',
'data_type': 'None',
'geocoding_order': 0,
'is_extra_data': True,
'merge_protection': 'Favor New',
'sharedFieldType': 'None',
'related': False,
'unit_name': None,
'unit_type': None,
'is_matching_criteria': False,
'recognize_empty': False,
'comstock_mapping': None,
}
self.assertIn(c, columns)
def test_xlxs_export(self):
for i in range(50):
p = self.property_view_factory.get_property_view()
self.properties.append(p.id)
columns = []
for c in Column.retrieve_all(self.org.id, 'property', False):
columns.append(c['name'])
# call the API
url = reverse_lazy('api:v2.1:tax_lot_properties-export')
response = self.client.post(url + '?{}={}&{}={}&{}={}'.format(
'organization_id', self.org.pk, 'cycle_id', self.cycle,
'inventory_type', 'properties'),
data=json.dumps({
'columns': columns,
'export_type': 'xlsx'
}),
content_type='application/json')
# parse the content as array
wb = open_workbook(file_contents=response.content)
data = [row.value for row in wb.sheet_by_index(0).row(0)]
self.assertTrue('Address Line 1' in data)
self.assertTrue('Property Labels' in data)
self.assertEqual(len([r for r in wb.sheet_by_index(0).get_rows()]), 52)
def test_csv_export(self):
"""Test to make sure get_related returns the fields"""
for i in range(50):
p = self.property_view_factory.get_property_view()
self.properties.append(p.id)
columns = []
for c in Column.retrieve_all(self.org.id, 'property', False):
columns.append(c['name'])
# call the API
url = reverse_lazy('api:v2.1:tax_lot_properties-export')
response = self.client.post(url + '?{}={}&{}={}&{}={}'.format(
'organization_id', self.org.pk, 'cycle_id', self.cycle,
'inventory_type', 'properties'),
data=json.dumps({
'columns': columns,
'export_type': 'csv'
}),
content_type='application/json')
# parse the content as array
data = response.content.decode('utf-8').split('\n')
self.assertTrue('Address Line 1' in data[0].split(','))
self.assertTrue('Property Labels\r' in data[0].split(','))
self.assertEqual(len(data), 53)
# last row should be blank
self.assertEqual(data[52], '')
def check_data(self, record_type, rows):
"""
Send in data as a queryset from the Property/Taxlot ids.
:param record_type: one of property/taxlot
:param rows: rows of data to be checked for data quality
:return: None
"""
# grab the columns in order to grab the display names
columns = Column.retrieve_all(self.organization, record_type)
# create lookup tuple for the display name
for c in columns:
self.column_lookup[(c['table'], c['name'])] = c['displayName']
# Get the list of the field names that will show in every result
fields = self.get_fieldnames(record_type)
for row in rows:
# Initialize the ID if it does not exist yet. Add in the other
# fields that are of interest to the GUI
if row.id not in self.results:
self.results[row.id] = {}
for field in fields:
self.results[row.id][field] = getattr(row, field)
self.results[row.id]['data_quality_results'] = []
# Run the checks
self._check(row)
# Prune the results will remove any entries that have zero data_quality_results
for k, v in self.results.items():
if not v['data_quality_results']:
del self.results[k]
def columns(self, request):
"""
List all tax lot columns
---
parameters:
- name: organization_id
description: The organization_id for this user's organization
required: true
paramType: query
- name: used_only
description: Determine whether or not to show only the used fields. Ones that have been mapped
type: boolean
required: false
paramType: query
"""
organization_id = int(request.query_params.get('organization_id'))
organization = Organization.objects.get(pk=organization_id)
only_used = json.loads(request.query_params.get('only_used', 'false'))
columns = Column.retrieve_all(organization_id, 'taxlot', only_used)
unitted_columns = [
add_pint_unit_suffix(organization, x) for x in columns
]
return JsonResponse({'status': 'success', 'columns': unitted_columns})
def test_column_name(self):
# verify that the column name is in the form of <column_name>_<id>.
# Note that most of the tests remove the name and id field from the column listings to make it easier,
# so this test is really important!
columns = Column.retrieve_all(self.fake_org.pk, 'property', False)
for c in columns:
if c['column_name'] == 'PropertyState':
self.assertEqual(c['name'], "%s_%s" % (c['column_name'], c['id']))
def columns(self, request):
"""
List all property columns
parameters:
- name: organization_id
description: The organization_id for this user's organization
required: true
paramType: query
"""
organization_id = int(request.query_params.get('organization_id'))
columns = Column.retrieve_all(organization_id, 'property')
return JsonResponse({'columns': columns})
def return_columns(cls,
organization_id,
profile_id,
inventory_type='properties'):
"""
Return a list of columns based on the profile_id. If the profile ID doesn't exist, then it
will return the list of raw database fields for the organization (i.e. all the fields).
:param organization_id: int, ID of the organization
:param profile_id: int, ID of the profile id to retrieve
:param inventory_type: str, type of inventory (either properties or taxlots)
:return: list, column_ids, column_name_mappings, and selected_columns_from_database
"""
try:
profile = ColumnListSetting.objects.get(
organization=organization_id,
id=profile_id,
settings_location=ColumnListSetting.VIEW_LIST,
inventory_type=cls.PROFILE_TYPE[inventory_type])
profile_id = profile.id
except ColumnListSetting.DoesNotExist:
profile_id = False
column_ids = []
column_name_mappings = OrderedDict()
columns_from_database = Column.retrieve_all(
organization_id, cls.COLUMN_TYPE[inventory_type], False)
selected_columns_from_database = []
if profile_id:
for c in apps.get_model(
'seed', 'ColumnListSettingColumn').objects.filter(
column_list_setting_id=profile_id).order_by('order'):
# find the items from the columns_from_database object and return only the ones that are in the
# selected profile
for c_db in columns_from_database:
if "%s_%s" % (c.column.column_name,
c.column.id) == c_db['name']:
selected_columns_from_database.append(c_db)
column_ids.append(c_db['id'])
column_name_mappings[
c_db['name']] = c_db['display_name']
else:
# return all the columns for the organization
for c in columns_from_database:
column_ids.append(c['id'])
column_name_mappings[c['name']] = c['display_name']
selected_columns_from_database.append(c)
return column_ids, column_name_mappings, selected_columns_from_database
def test_columns_extra_tag(self):
columns = Column.retrieve_all(self.fake_org.pk, 'taxlot', False)
# go through and delete all the results.ids so that it is easy to do a compare
for result in columns:
del result['id']
del result['name']
c = {
"table_name": "TaxLotState",
"column_name": "gross_floor_area",
"display_name": "Gross Floor Area",
"data_type": "None",
"is_extra_data": True,
"sharedFieldType": "None",
"related": False,
}
self.assertIn(c, columns)
def test_tax_lot_property_get_related(self):
"""Test to make sure get_related returns the fields"""
for i in range(50):
p = self.property_view_factory.get_property_view()
self.properties.append(p.id)
qs_filter = {"pk__in": self.properties}
qs = PropertyView.objects.filter(**qs_filter)
columns = [
'address_line_1',
'generation_date',
'energy_alerts',
'space_alerts',
'building_count',
'owner',
'source_eui',
'jurisdiction_tax_lot_id',
'city',
'confidence',
'district',
'best_guess_confidence',
'site_eui',
'building_certification',
'modified',
'match_type',
'source_eui_weather_normalized',
u'id',
'property_name',
'conditioned_floor_area',
'pm_property_id',
'use_description',
'source_type',
'year_built',
'release_date',
'gross_floor_area',
'owner_city_state',
'owner_telephone',
'recent_sale_date',
]
columns_from_database = Column.retrieve_all(self.org.id, 'property',
False)
data = TaxLotProperty.get_related(qs, columns, columns_from_database)
self.assertEqual(len(data), 50)
self.assertEqual(len(data[0]['related']), 0)
def list(self, request):
"""
Retrieves all columns for the user's organization including the raw database columns. Will
return all the columns across both the Property and Tax Lot tables. The related field will
be true if the column came from the other table that is not the 'inventory_type' (which
defaults to Property)
"""
organization_id = self.get_organization(self.request)
inventory_type = request.query_params.get('inventory_type', 'property')
only_used = json.loads(request.query_params.get('only_used', 'false'))
columns = Column.retrieve_all(organization_id, inventory_type, only_used)
organization = Organization.objects.get(pk=organization_id)
if json.loads(request.query_params.get('display_units', 'true')):
columns = [add_pint_unit_suffix(organization, x) for x in columns]
return JsonResponse({
'status': 'success',
'columns': columns,
})
def columns(self, request):
"""
List all property columns
parameters:
- name: organization_id
description: The organization_id for this user's organization
required: true
paramType: query
- name: used_only
description: Determine whether or not to show only the used fields. Ones that have been mapped
type: boolean
required: false
paramType: query
"""
organization_id = int(request.query_params.get('organization_id'))
only_used = request.query_params.get('only_used', False)
columns = Column.retrieve_all(organization_id, 'property', only_used)
return JsonResponse({'status': 'success', 'columns': columns})
def shared_fields(self, request, pk=None):
"""
Retrieves all fields marked as shared for the organization. Will only return used fields.
"""
result = {'status': 'success', 'public_fields': []}
columns = Column.retrieve_all(pk, 'property', True)
for c in columns:
if c['sharedFieldType'] == 'Public':
new_column = {
'table_name': c['table_name'],
'name': c['name'],
'column_name': c['column_name'],
# this is the field name in the db. The other name can have tax_
'display_name': c['display_name']
}
result['public_fields'].append(new_column)
return JsonResponse(result)
def taxlots_across_cycles(org_id, profile_id, cycle_ids=[]):
# Identify column preferences to be used to scope fields/values
columns_from_database = Column.retrieve_all(org_id, 'taxlot', False)
if profile_id == -1:
show_columns = list(
Column.objects.filter(organization_id=org_id).values_list(
'id', flat=True))
else:
try:
profile = ColumnListSetting.objects.get(
organization_id=org_id,
id=profile_id,
settings_location=VIEW_LIST,
inventory_type=VIEW_LIST_TAXLOT)
show_columns = list(
ColumnListSettingColumn.objects.filter(
column_list_setting_id=profile.id).values_list('column_id',
flat=True))
except ColumnListSetting.DoesNotExist:
show_columns = None
results = {}
for cycle_id in cycle_ids:
# get -Views for this Cycle
taxlot_views = TaxLotView.objects.select_related('taxlot', 'state', 'cycle') \
.filter(taxlot__organization_id=org_id, cycle_id=cycle_id) \
.order_by('id')
related_results = TaxLotProperty.get_related(taxlot_views,
show_columns,
columns_from_database)
org = Organization.objects.get(pk=org_id)
unit_collapsed_results = [
apply_display_unit_preferences(org, x) for x in related_results
]
results[cycle_id] = unit_collapsed_results
return results
def test_csv_export_with_notes(self):
multi_line_note = self.property_view.notes.create(
name='Manually Created',
note_type=Note.NOTE,
text='multi\nline\nnote')
single_line_note = self.property_view.notes.create(
name='Manually Created', note_type=Note.NOTE, text='single line')
self.properties.append(self.property_view.id)
columns = []
for c in Column.retrieve_all(self.org.id, 'property', False):
columns.append(c['name'])
# call the API
url = reverse_lazy('api:v2.1:tax_lot_properties-export')
response = self.client.post(url + '?{}={}&{}={}&{}={}'.format(
'organization_id', self.org.pk, 'cycle_id', self.cycle,
'inventory_type', 'properties'),
data=json.dumps({
'columns': columns,
'export_type': 'csv'
}),
content_type='application/json')
# parse the content as array
data = response.content.decode('utf-8').split('\r\n')
notes_string = (multi_line_note.created.astimezone().strftime(
"%Y-%m-%d %I:%M:%S %p") + "\n" + multi_line_note.text +
"\n----------\n" +
single_line_note.created.astimezone().strftime(
"%Y-%m-%d %I:%M:%S %p") + "\n" +
single_line_note.text)
self.assertEqual(len(data), 3)
self.assertTrue('Property Notes' in data[0].split(','))
self.assertTrue(notes_string in data[1])
def test_json_export(self):
"""Test to make sure get_related returns the fields"""
for i in range(50):
p = self.property_view_factory.get_property_view()
self.properties.append(p.id)
columns = []
for c in Column.retrieve_all(self.org.id, 'property', False):
columns.append(c['name'])
# call the API
url = reverse_lazy('api:v2.1:tax_lot_properties-export')
response = self.client.post(url + '?{}={}&{}={}&{}={}'.format(
'organization_id', self.org.pk, 'cycle_id', self.cycle,
'inventory_type', 'properties'),
data=json.dumps({
'columns': columns,
'export_type': 'geojson'
}),
content_type='application/json')
# parse the content as dictionary
data = json.loads(response.content)
first_level_keys = list(data.keys())
self.assertIn("type", first_level_keys)
self.assertIn("crs", first_level_keys)
self.assertIn("features", first_level_keys)
record_level_keys = list(data['features'][0]['properties'].keys())
self.assertIn('Address Line 1', record_level_keys)
self.assertTrue('Gross Floor Area', record_level_keys)
# ids 52 up to and including 102
self.assertEqual(len(data['features']), 51)
def csv(self, request):
"""
Download a csv of the TaxLot and Properties
.. code-block::
{
"ids": [1,2,3],
"columns": ["tax_jurisdiction_tax_lot_id", "address_line_1", "property_view_id"]
}
---
parameter_strategy: replace
parameters:
- name: cycle
description: cycle
required: true
paramType: query
- name: inventory_type
description: properties or taxlots (as defined by the inventory list page)
required: true
paramType: query
- name: ids
description: list of property ids to export (not property views)
required: true
paramType: body
- name: columns
description: list of columns to export
required: true
paramType: body
- name: filename
description: name of the file to create
required: false
paramType: body
"""
cycle_pk = request.query_params.get('cycle_id', None)
if not cycle_pk:
return JsonResponse({
'status':
'error',
'message':
'Must pass in cycle_id as query parameter'
})
columns = request.data.get('columns', None)
if columns is None:
# default the columns for now if no columns are passed
columns = [
'pm_property_id', 'pm_parent_property_id',
'tax_jurisdiction_tax_lot_id', 'ubid', 'custom_id_1',
'tax_custom_id_1', 'city', 'state', 'postal_code',
'tax_primary', 'property_name', 'campus', 'gross_floor_area',
'use_description', 'energy_score', 'site_eui',
'property_notes', 'property_type', 'year_ending', 'owner',
'owner_email', 'owner_telephone', 'building_count',
'year_built', 'recent_sale_date', 'conditioned_floor_area',
'occupied_floor_area', 'owner_address', 'owner_city_state',
'owner_postal_code', 'home_energy_score_id', 'generation_date',
'release_date', 'source_eui_weather_normalized',
'site_eui_weather_normalized', 'source_eui', 'energy_alerts',
'space_alerts', 'building_certification', 'number_properties',
'block_number', 'district', 'BLDGS', 'property_state_id',
'taxlot_state_id', 'property_view_id', 'taxlot_view_id'
]
# get the class to operate on and the relationships
view_klass_str = request.query_params.get('inventory_type',
'properties')
view_klass = INVENTORY_MODELS[view_klass_str]
# Grab all the columns and create a column name lookup
col_inventory_type = 'property' if view_klass_str == 'properties' else 'taxlot'
columns_db = Column.retrieve_all(
request.query_params['organization_id'], col_inventory_type, False)
column_lookup = {}
for c in columns_db:
column_lookup[c['name']] = c['displayName']
# make the csv header
header = []
for c in columns:
if c in column_lookup:
header.append(column_lookup[c])
else:
header.append(c)
select_related = ['state', 'cycle']
ids = request.data.get('ids', [])
filter_str = {'cycle': cycle_pk}
if hasattr(view_klass, 'property'):
select_related.append('property')
filter_str = {
'property__organization_id':
request.query_params['organization_id']
}
if ids:
filter_str['property__id__in'] = ids
# always export the labels
columns += ['property_labels']
header.append('Property Labels')
elif hasattr(view_klass, 'taxlot'):
select_related.append('taxlot')
filter_str = {
'taxlot__organization_id':
request.query_params['organization_id']
}
if ids:
filter_str['taxlot__id__in'] = ids
# always export the labels
columns += ['taxlot_labels']
header.append('Tax Lot Labels')
model_views = view_klass.objects.select_related(
*select_related).filter(**filter_str).order_by('id')
filename = request.data.get('filename', "ExportedData.csv")
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="{}"'.format(
filename)
writer = csv.writer(response)
# get the data in a dict which includes the related data
data = TaxLotProperty.get_related(model_views, columns)
# force the data into the same order as the IDs
if ids:
order_dict = {obj_id: index for index, obj_id in enumerate(ids)}
data.sort(key=lambda x: order_dict[x['id']]
) # x is the property/taxlot object
# note that the labels are in the property_labels column and are returned by the
# TaxLotProperty.get_related method.
# header
writer.writerow(header)
# iterate over the results to preserve column order and write row.
# The front end returns columns with prepended tax_ and property_ columns for the
# related fields. This is an expensive operation and can cause issues with stripping
# off property_ from items such as property_name, property_notes, and property_type
# which are explicitly excluded below
for datum in data:
row = []
for column in columns:
if column in [
'property_name', 'property_notes', 'property_type',
'property_labels'
]:
row.append(datum.get(column, None))
elif column.startswith('tax_'):
# There are times when there are duplicate column names in tax/property
if datum.get('related') and len(datum['related']) > 0:
# Looks like related returns a list. Is this as expected?
row.append(datum['related'][0].get(
re.sub(r'^tax_', '', column), None))
else:
row.append(None)
elif column.startswith('property_'):
# There are times when there are duplicate column names in tax/property
if datum.get('related') and len(datum['related']) > 0:
# Looks like related returns a list. Is this as expected?
row.append(datum['related'][0].get(
re.sub(r'^property_', '', column), None))
else:
row.append(None)
else:
# check if the data is in the normal section of the result, if not, then try to grab it from
# the related section. There shouldn't be any duplicates here because the former two
# if methods will grab those instances.
result = datum.get(column, None)
if not result:
if datum.get('related'):
result = datum['related'][0].get(column, None)
row.append(result)
writer.writerow(row)
return response
def test_column_retrieve_all(self):
columns = Column.retrieve_all(self.fake_org.pk, 'property', False)
# Check for new column
c = {
'table': u'PropertyState',
'extraData': True,
'displayName': u'Column A',
'name': u'Column A',
'dbName': u'Column A',
'related': False,
'sharedFieldType': 'Public',
}
self.assertIn(c, columns)
# Check that display_name doesn't capitalize after apostrophe
c = {
'table': u'PropertyState',
'extraData': True,
'displayName': u"Apostrophe's Field",
'name': u"Apostrophe's Field",
'dbName': u"Apostrophe's Field",
'related': False,
'sharedFieldType': 'None',
}
self.assertIn(c, columns)
# Check 'id' field if extra_data
c = {
'table': 'PropertyState',
'extraData': True,
'displayName': 'Id',
'name': 'id_extra',
'dbName': 'id',
'related': False,
'sharedFieldType': 'None',
}
self.assertIn(c, columns)
# check the 'pinIfNative' argument
c = {
'name': 'pm_property_id',
'dbName': 'pm_property_id',
'related': False,
'table': 'PropertyState',
'displayName': 'PM Property ID',
'dataType': 'string',
'pinnedLeft': True,
'sharedFieldType': 'None',
}
self.assertIn(c, columns)
# verity that the 'duplicateNameInOtherTable' is working
c = {
'related': True,
'table': 'TaxLotState',
'displayName': 'State (Tax Lot)',
'dataType': 'string',
'name': 'tax_state',
'dbName': 'state',
'sharedFieldType': 'None',
}
self.assertIn(c, columns)
self.assertNotIn('not extra data', [d['name'] for d in columns])
self.assertNotIn('not mapped data', [d['name'] for d in columns])
def test_column_retrieve_all(self):
columns = Column.retrieve_all(self.fake_org.pk, 'property', False)
# go through and delete all the results.ids so that it is easy to do a compare
for result in columns:
del result['id']
del result['name']
# Check for columns
c = {
'table_name': u'PropertyState',
'column_name': u'Column A',
'display_name': u'Column A',
'is_extra_data': True,
'data_type': 'None',
'related': False,
'sharedFieldType': 'Public',
}
self.assertIn(c, columns)
# Check that display_name doesn't capitalize after apostrophe
c = {
'table_name': u'PropertyState',
'column_name': u"Apostrophe's Field",
'display_name': u"Apostrophe's Field",
'is_extra_data': True,
'data_type': 'None',
'related': False,
'sharedFieldType': 'None',
}
self.assertIn(c, columns)
# Check 'id' field if extra_data
c = {
'table_name': 'PropertyState',
'column_name': 'id',
'display_name': 'Id',
'is_extra_data': True,
'data_type': 'None',
'related': False,
'sharedFieldType': 'None',
}
self.assertIn(c, columns)
# check the 'pinIfNative' argument
c = {
'table_name': 'PropertyState',
'column_name': 'pm_property_id',
'display_name': 'PM Property ID',
'is_extra_data': False,
'data_type': 'string',
'pinnedLeft': True,
'related': False,
'sharedFieldType': 'None',
}
self.assertIn(c, columns)
# verity that the 'duplicateNameInOtherTable' is working
c = {
'table_name': 'TaxLotState',
'column_name': 'state',
'display_name': 'State (Tax Lot)',
'data_type': 'string',
'is_extra_data': False,
'sharedFieldType': 'None',
'related': True,
}
self.assertIn(c, columns)
c = {
"table_name": "TaxLotState",
"column_name": "gross_floor_area",
"display_name": "Gross Floor Area (Tax Lot)",
"data_type": "None",
"is_extra_data": True,
"sharedFieldType": "None",
"related": True,
}
self.assertIn(c, columns)
# TODO: 4/25/2018 Need to decide how to check for bad columns and not return them in the request
self.assertNotIn('not mapped data',
[d['column_name'] for d in columns])
def test_column_retrieve_all(self):
seed_models.Column.objects.create(
column_name=u'Column A',
table_name=u'PropertyState',
organization=self.fake_org,
is_extra_data=True
)
seed_models.Column.objects.create(
column_name=u'id',
table_name=u'PropertyState',
organization=self.fake_org,
is_extra_data=True
)
seed_models.Column.objects.create(
column_name=u'not extra data',
table_name=u'PropertyState',
organization=self.fake_org,
is_extra_data=False
)
seed_models.Column.objects.create(
column_name=u'not mapped data',
organization=self.fake_org,
)
columns = Column.retrieve_all(self.fake_org.pk, 'property')
# import json
# print json.dumps(columns, indent=2)
# Check for new column
c = {
'table': u'PropertyState',
'extraData': True,
'displayName': u'Column A',
'name': u'Column A',
'related': False,
}
self.assertIn(c, columns)
# Check 'id' field if extra_data
c = {
"table": "PropertyState",
"extraData": True,
"displayName": "Id",
"name": "id_extra",
"related": False,
}
self.assertIn(c, columns)
# check the 'pinIfNative' argument
c = {
"name": "pm_property_id",
"related": False,
"table": "PropertyState",
"displayName": "PM Property ID",
"dataType": "string",
"type": "number",
"pinnedLeft": True,
}
self.assertIn(c, columns)
# verity that the 'duplicateNameInOtherTable' is working
c = {
"related": True,
"table": "TaxLotState",
"displayName": "State (Tax Lot)",
"dataType": "string",
"name": "tax_state",
}
self.assertIn(c, columns)
self.assertNotIn('not extra data', [d['name'] for d in columns])
self.assertNotIn('not mapped data', [d['name'] for d in columns])
def csv(self, request):
"""
Download a csv of the TaxLot and Properties
.. code-block::
{
"ids": [1,2,3],
"columns": ["tax_jurisdiction_tax_lot_id", "address_line_1", "property_view_id"]
}
---
parameter_strategy: replace
parameters:
- name: cycle
description: cycle
required: true
paramType: query
- name: inventory_type
description: properties or taxlots (as defined by the inventory list page)
required: true
paramType: query
- name: ids
description: list of property ids to export (not property views)
required: true
paramType: body
- name: columns
description: list of columns to export
required: true
paramType: body
- name: filename
description: name of the file to create
required: false
paramType: body
"""
cycle_pk = request.query_params.get('cycle_id', None)
if not cycle_pk:
return JsonResponse({
'status':
'error',
'message':
'Must pass in cycle_id as query parameter'
})
columns = request.data.get('columns', None)
if columns is None:
# default the columns for now if no columns are passed
columns = Column.retrieve_db_fields(
request.query_params['organization_id'])
# get the class to operate on and the relationships
view_klass_str = request.query_params.get('inventory_type',
'properties')
view_klass = INVENTORY_MODELS[view_klass_str]
# Grab all the columns and create a column name lookup
col_inventory_type = 'property' if view_klass_str == 'properties' else 'taxlot'
columns_db = Column.retrieve_all(
request.query_params['organization_id'], col_inventory_type, False)
column_lookup = {}
db_column_name_lookup = {}
column_related_lookup = {}
for c in columns_db:
column_lookup[c['name']] = c['display_name']
db_column_name_lookup[c['name']] = c['column_name']
column_related_lookup[c['name']] = c['related']
# add a couple of other Display Names
column_lookup['notes_count'] = 'Notes Count'
column_lookup['id'] = 'ID'
# make the csv header
header = []
for c in columns:
if c in column_lookup:
header.append(column_lookup[c])
else:
header.append(c)
select_related = ['state', 'cycle']
ids = request.data.get('ids', [])
filter_str = {'cycle': cycle_pk}
if hasattr(view_klass, 'property'):
select_related.append('property')
filter_str = {
'property__organization_id':
request.query_params['organization_id']
}
if ids:
filter_str['property__id__in'] = ids
# always export the labels
columns += ['property_labels']
header.append('Property Labels')
elif hasattr(view_klass, 'taxlot'):
select_related.append('taxlot')
filter_str = {
'taxlot__organization_id':
request.query_params['organization_id']
}
if ids:
filter_str['taxlot__id__in'] = ids
# always export the labels
columns += ['taxlot_labels']
header.append('Tax Lot Labels')
model_views = view_klass.objects.select_related(
*select_related).filter(**filter_str).order_by('id')
filename = request.data.get('filename', "ExportedData.csv")
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="{}"'.format(
filename)
writer = csv.writer(response)
# get the data in a dict which includes the related data
data = TaxLotProperty.get_related(model_views,
db_column_name_lookup.values(),
columns_db)
# force the data into the same order as the IDs
if ids:
order_dict = {obj_id: index for index, obj_id in enumerate(ids)}
data.sort(key=lambda x: order_dict[x['id']]
) # x is the property/taxlot object
# note that the labels are in the property_labels column and are returned by the
# TaxLotProperty.get_related method.
# header
writer.writerow(header)
# iterate over the results to preserve column order and write row.
for datum in data:
row = []
for column in columns:
row_result = None
if column in column_related_lookup and column_related_lookup[
column]:
# this is a related column, grab out of the related section
if datum.get('related'):
row_result = datum['related'][0].get(column, None)
else:
row_result = datum.get(column, None)
# Convert quantities (this is typically handled in the JSON Encoder, but that isn't here).
if isinstance(row_result, ureg.Quantity):
row_result = row_result.magnitude
elif isinstance(row_result, datetime.datetime):
row_result = row_result.strftime("%Y-%m-%d %H:%M:%S")
elif isinstance(row_result, datetime.date):
row_result = row_result.strftime("%Y-%m-%d")
row.append(row_result)
writer.writerow(row)
return response
def test_column_retrieve_all(self):
columns = Column.retrieve_all(self.fake_org.pk, 'property', False)
# go through and delete all the results.ids so that it is easy to do a compare
for result in columns:
del result['id']
del result['name']
del result['organization_id'] # org changes based on test
# Check for columns
c = {
'table_name': 'PropertyState',
'column_name': 'Column A',
'display_name': 'Column A',
'is_extra_data': True,
'merge_protection': 'Favor New',
'data_type': 'None',
'related': False,
'sharedFieldType': 'Public',
'unit_name': None,
'unit_type': None,
'is_matching_criteria': False,
}
self.assertIn(c, columns)
# Check that display_name doesn't capitalize after apostrophe
c = {
'table_name': 'PropertyState',
'column_name': "Apostrophe's Field",
'display_name': "Apostrophe's Field",
'is_extra_data': True,
'merge_protection': 'Favor New',
'data_type': 'None',
'related': False,
'sharedFieldType': 'None',
'unit_name': None,
'unit_type': None,
'is_matching_criteria': False,
}
self.assertIn(c, columns)
# Check 'id' field if extra_data
c = {
'table_name': 'PropertyState',
'column_name': 'id',
'display_name': 'id',
'is_extra_data': True,
'merge_protection': 'Favor New',
'data_type': 'None',
'related': False,
'sharedFieldType': 'None',
'unit_name': None,
'unit_type': None,
'is_matching_criteria': False,
}
self.assertIn(c, columns)
# check the 'pinIfNative' argument
c = {
'table_name': 'PropertyState',
'column_name': 'pm_property_id',
'display_name': 'PM Property ID',
'is_extra_data': False,
'merge_protection': 'Favor New',
'data_type': 'string',
'pinnedLeft': True,
'related': False,
'sharedFieldType': 'None',
'unit_name': None,
'unit_type': None,
'is_matching_criteria': True,
}
self.assertIn(c, columns)
# verity that the 'duplicateNameInOtherTable' is working
c = {
'table_name': 'TaxLotState',
'column_name': 'state',
'display_name': 'State (Tax Lot)',
'data_type': 'string',
'is_extra_data': False,
'merge_protection': 'Favor New',
'sharedFieldType': 'None',
'related': True,
'unit_name': None,
'unit_type': None,
'is_matching_criteria': False,
}
self.assertIn(c, columns)
c = {
'table_name': 'TaxLotState',
'column_name': 'Gross Floor Area',
'display_name': 'Gross Floor Area (Tax Lot)',
'data_type': 'None',
'is_extra_data': True,
'merge_protection': 'Favor New',
'sharedFieldType': 'None',
'related': True,
'unit_name': None,
'unit_type': None,
'is_matching_criteria': False,
}
self.assertIn(c, columns)
# TODO: 4/25/2018 Need to decide how to check for bad columns and not return them in the request
self.assertNotIn('not mapped data',
[d['column_name'] for d in columns])
def test_column_retrieve_only_used(self):
columns = Column.retrieve_all(self.fake_org.pk, 'property', True)
self.assertEqual(len(columns), 1)
for c in columns:
if c['name'] == 'Column A':
self.assertEqual(c['sharedFieldType'], 'Public')
def _get_filtered_results(self, request, profile_id):
page = request.query_params.get('page', 1)
per_page = request.query_params.get('per_page', 1)
org_id = request.query_params.get('organization_id', None)
cycle_id = request.query_params.get('cycle')
# check if there is a query paramater for the profile_id. If so, then use that one
profile_id = request.query_params.get('profile_id', profile_id)
if not org_id:
return JsonResponse(
{
'status': 'error',
'message':
'Need to pass organization_id as query parameter'
},
status=status.HTTP_400_BAD_REQUEST)
if cycle_id:
cycle = Cycle.objects.get(organization_id=org_id, pk=cycle_id)
else:
cycle = Cycle.objects.filter(
organization_id=org_id).order_by('name')
if cycle:
cycle = cycle.first()
else:
return JsonResponse({
'status': 'error',
'message': 'Could not locate cycle',
'pagination': {
'total': 0
},
'cycle_id': None,
'results': []
})
# Return taxlot views limited to the 'inventory_ids' list. Otherwise, if selected is empty, return all
if 'inventory_ids' in request.data and request.data['inventory_ids']:
taxlot_views_list = TaxLotView.objects.select_related('taxlot', 'state', 'cycle') \
.filter(taxlot_id__in=request.data['inventory_ids'], taxlot__organization_id=org_id,
cycle=cycle) \
.order_by('id')
else:
taxlot_views_list = TaxLotView.objects.select_related('taxlot', 'state', 'cycle') \
.filter(taxlot__organization_id=org_id, cycle=cycle) \
.order_by('id')
paginator = Paginator(taxlot_views_list, per_page)
try:
taxlot_views = paginator.page(page)
page = int(page)
except PageNotAnInteger:
taxlot_views = paginator.page(1)
page = 1
except EmptyPage:
taxlot_views = paginator.page(paginator.num_pages)
page = paginator.num_pages
org = Organization.objects.get(pk=org_id)
# Retrieve all the columns that are in the db for this organization
columns_from_database = Column.retrieve_all(org_id, 'taxlot', False)
# This uses an old method of returning the show_columns. There is a new method that
# is preferred in v2.1 API with the ProfileIdMixin.
if profile_id is None:
show_columns = None
elif profile_id == -1:
show_columns = list(
Column.objects.filter(organization_id=org_id).values_list(
'id', flat=True))
else:
try:
profile = ColumnListProfile.objects.get(
organization=org,
id=profile_id,
profile_location=VIEW_LIST,
inventory_type=VIEW_LIST_TAXLOT)
show_columns = list(
ColumnListProfileColumn.objects.filter(
column_list_profile_id=profile.id).values_list(
'column_id', flat=True))
except ColumnListProfile.DoesNotExist:
show_columns = None
related_results = TaxLotProperty.get_related(taxlot_views,
show_columns,
columns_from_database)
# collapse units here so we're only doing the last page; we're already a
# realized list by now and not a lazy queryset
unit_collapsed_results = [
apply_display_unit_preferences(org, x) for x in related_results
]
response = {
'pagination': {
'page': page,
'start': paginator.page(page).start_index(),
'end': paginator.page(page).end_index(),
'num_pages': paginator.num_pages,
'has_next': paginator.page(page).has_next(),
'has_previous': paginator.page(page).has_previous(),
'total': paginator.count
},
'cycle_id': cycle.id,
'results': unit_collapsed_results
}
return JsonResponse(response)
def _get_filtered_results(self, request, columns):
page = request.query_params.get('page', 1)
per_page = request.query_params.get('per_page', 1)
org_id = request.query_params.get('organization_id', None)
cycle_id = request.query_params.get('cycle')
if not org_id:
return JsonResponse(
{
'status': 'error',
'message':
'Need to pass organization_id as query parameter'
},
status=status.HTTP_400_BAD_REQUEST)
if cycle_id:
cycle = Cycle.objects.get(organization_id=org_id, pk=cycle_id)
else:
cycle = Cycle.objects.filter(
organization_id=org_id).order_by('name')
if cycle:
cycle = cycle.first()
else:
return JsonResponse({
'status': 'error',
'message': 'Could not locate cycle',
'pagination': {
'total': 0
},
'cycle_id': None,
'results': []
})
taxlot_views_list = TaxLotView.objects.select_related('taxlot', 'state', 'cycle') \
.filter(taxlot__organization_id=org_id, cycle=cycle) \
.order_by('id')
paginator = Paginator(taxlot_views_list, per_page)
try:
taxlot_views = paginator.page(page)
page = int(page)
except PageNotAnInteger:
taxlot_views = paginator.page(1)
page = 1
except EmptyPage:
taxlot_views = paginator.page(paginator.num_pages)
page = paginator.num_pages
columns_from_database = Column.retrieve_all(org_id, 'taxlot', False)
related_results = TaxLotProperty.get_related(taxlot_views, columns,
columns_from_database)
# collapse units here so we're only doing the last page; we're already a
# realized list by now and not a lazy queryset
org = Organization.objects.get(pk=org_id)
unit_collapsed_results = \
[apply_display_unit_preferences(org, x) for x in related_results]
response = {
'pagination': {
'page': page,
'start': paginator.page(page).start_index(),
'end': paginator.page(page).end_index(),
'num_pages': paginator.num_pages,
'has_next': paginator.page(page).has_next(),
'has_previous': paginator.page(page).has_previous(),
'total': paginator.count
},
'cycle_id': cycle.id,
'results': unit_collapsed_results
}
return JsonResponse(response)
def mapping_results(self, request, pk=None):
"""
Retrieves a paginated list of Properties and Tax Lots for an import file after mapping.
"""
import_file_id = pk
org_id = request.query_params.get('organization_id', None)
org = Organization.objects.get(pk=org_id)
try:
# get the field names that were in the mapping
import_file = ImportFile.objects.get(
pk=import_file_id, import_record__super_organization_id=org_id)
except ImportFile.DoesNotExist:
return JsonResponse(
{
'status': 'error',
'message': 'Could not find import file with pk=' + str(pk)
},
status=status.HTTP_400_BAD_REQUEST)
# List of the only fields to show
field_names = import_file.get_cached_mapped_columns
# set of fields
fields = {
'PropertyState': ['id', 'extra_data', 'lot_number'],
'TaxLotState': ['id', 'extra_data']
}
columns_from_db = Column.retrieve_all(org_id)
property_column_name_mapping = {}
taxlot_column_name_mapping = {}
for field_name in field_names:
# find the field from the columns in the database
for column in columns_from_db:
if column['table_name'] == 'PropertyState' and \
field_name[0] == 'PropertyState' and \
field_name[1] == column['column_name']:
property_column_name_mapping[
column['column_name']] = column['name']
if not column['is_extra_data']:
fields['PropertyState'].append(
field_name[1]) # save to the raw db fields
continue
elif column['table_name'] == 'TaxLotState' and \
field_name[0] == 'TaxLotState' and \
field_name[1] == column['column_name']:
taxlot_column_name_mapping[
column['column_name']] = column['name']
if not column['is_extra_data']:
fields['TaxLotState'].append(
field_name[1]) # save to the raw db fields
continue
inventory_type = request.data.get('inventory_type', 'all')
result = {'status': 'success'}
if inventory_type == 'properties' or inventory_type == 'all':
properties = PropertyState.objects.filter(
import_file_id=import_file_id,
data_state__in=[DATA_STATE_MAPPING, DATA_STATE_MATCHING],
merge_state__in=[
MERGE_STATE_UNKNOWN, MERGE_STATE_NEW
]).only(*fields['PropertyState']).order_by('id')
property_results = []
for prop in properties:
prop_dict = TaxLotProperty.model_to_dict_with_mapping(
prop,
property_column_name_mapping,
fields=fields['PropertyState'],
exclude=['extra_data'])
prop_dict.update(
TaxLotProperty.extra_data_to_dict_with_mapping(
prop.extra_data,
property_column_name_mapping,
fields=prop.extra_data.keys(),
).items())
prop_dict = apply_display_unit_preferences(org, prop_dict)
property_results.append(prop_dict)
result['properties'] = property_results
if inventory_type == 'taxlots' or inventory_type == 'all':
tax_lots = TaxLotState.objects.filter(
import_file_id=import_file_id,
data_state__in=[DATA_STATE_MAPPING, DATA_STATE_MATCHING],
merge_state__in=[MERGE_STATE_UNKNOWN, MERGE_STATE_NEW
]).only(*fields['TaxLotState']).order_by('id')
tax_lot_results = []
for tax_lot in tax_lots:
tax_lot_dict = TaxLotProperty.model_to_dict_with_mapping(
tax_lot,
taxlot_column_name_mapping,
fields=fields['TaxLotState'],
exclude=['extra_data'])
tax_lot_dict.update(
TaxLotProperty.extra_data_to_dict_with_mapping(
tax_lot.extra_data,
taxlot_column_name_mapping,
fields=tax_lot.extra_data.keys(),
).items())
tax_lot_dict = apply_display_unit_preferences(
org, tax_lot_dict)
tax_lot_results.append(tax_lot_dict)
result['tax_lots'] = tax_lot_results
return result