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