def create_kto_feedback_csv(location: str) -> str: """ Create CSV file with all `Feedback` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ environment = os.getenv('ENVIRONMENT') if environment is None: raise EnvironmentError('ENVIRONMENT env variable not set') elif environment.upper() not in ['PRODUCTION', 'ACCEPTANCE']: raise EnvironmentError('ENVIRONMENT env variable is wrong {}'.format(environment)) file_name = f'kto-feedback-{environment}.csv' # TODO Fix order of fields queryset = Feedback.objects.values( '_signal_id', 'text', 'text_extra', 'created_at', 'submitted_at', _is_satisfied=map_choices('is_satisfied', [(True, 'True'), (False, 'False')]), _allows_contact=map_choices('allows_contact', [(True, 'True'), (False, 'False')]), ).filter(submitted_at__isnull=False) csv_file = queryset_to_csv_file(queryset, os.path.join(location, file_name)) ordered_field_names = ['_signal_id', 'is_satisfied', 'allows_contact', 'text', 'text_extra', 'created_at', 'submitted_at', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_statuses_csv(location: str) -> str: """ Create CSV file with all `Status` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = Status.objects.values( 'id', 'text', 'user', 'target_api', 'created_at', 'updated_at', '_signal_id', 'state', _extern=map_choices('extern', [(True, 'True'), (False, 'False')]), state_display=map_choices('state', STATUS_CHOICES), _extra_properties=Coalesce(Cast('extra_properties', output_field=CharField()), Value('null', output_field=CharField())) ) csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'statuses.csv')) ordered_field_names = ['id', 'text', 'user', 'target_api', 'state_display', 'extern', 'created_at', 'updated_at', 'extra_properties', '_signal_id', 'state', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_locations_csv(location: str) -> str: """ Create CSV file with all `Location` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = Location.objects.values( 'id', 'buurt_code', 'address_text', 'created_at', 'updated_at', '_signal_id', lat=ExpressionWrapper(Func('geometrie', function='st_x'), output_field=FloatField()), lng=ExpressionWrapper(Func('geometrie', function='st_y'), output_field=FloatField()), _stadsdeel=Coalesce(map_choices('stadsdeel', STADSDELEN), Cast('area_code', output_field=CharField())), _address=Coalesce(Cast('address', output_field=CharField()), Value('null', output_field=CharField())), _extra_properties=Coalesce(Cast('extra_properties', output_field=CharField()), Value('null', output_field=CharField())) ).order_by( 'id' ) csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'locations.csv')) ordered_field_names = ['id', 'lat', 'lng', 'stadsdeel', 'buurt_code', 'address', 'address_text', 'created_at', 'updated_at', 'extra_properties', '_signal_id', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_category_sla_csv(location: str) -> str: """ Create CSV file with all `ServiceLevelObjective` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = ServiceLevelObjective.objects.values( 'id', 'n_days', 'use_calendar_days', 'created_at', main=F('category__parent__name'), sub=F('category__name'), ).order_by('category_id', '-created_at') csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'sla.csv')) ordered_field_names = [ 'id', 'main', 'sub', 'n_days', 'use_calendar_days', 'created_at', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_signals_routing_departments_csv(location: str) -> str: """ Create the CSV file with all `Signal - department relation (filled by routing rules)` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = SignalDepartments.objects.values( 'id', 'created_at', 'updated_at', '_signal_id', _departments=StringAgg('departments__name', delimiter=', '), ).filter(relation_type=SignalDepartments.REL_ROUTING).order_by( '_signal_id', '-created_at', ) csv_file = queryset_to_csv_file( queryset, os.path.join(location, 'routing_departments.csv')) ordered_field_names = [ 'id', 'created_at', 'updated_at', '_signal_id', 'departments', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_signals_csv(location: str) -> str: """ Create the CSV file with all `Signal` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = Signal.objects.annotate( image=Value(None, output_field=CharField()), upload=Value(None, output_field=CharField()), ).values( 'id', 'source', 'text', 'text_extra', 'incident_date_start', 'incident_date_end', 'created_at', 'updated_at', 'operational_date', 'expire_date', 'image', 'upload', 'directing_departments_assignment_id', 'category_assignment_id', 'location_id', 'reporter_id', 'status_id', signal_uuid=F('uuid'), _priority=F('priority__priority'), priority_created_at=F('priority__created_at'), _parent=F('parent_id'), type=F('type_assignment__name'), type_created_at=F('type_assignment__created_at'), _extra_properties=Coalesce(Cast('extra_properties', output_field=CharField()), Value('null', output_field=CharField())) ).order_by('created_at') csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'signals.csv')) ordered_field_names = ['id', 'signal_uuid', 'source', 'text', 'text_extra', 'incident_date_start', 'incident_date_end', 'created_at', 'updated_at', 'operational_date', 'expire_date', 'image', 'upload', 'extra_properties', 'category_assignment_id', 'location_id', 'reporter_id', 'status_id', 'priority', 'priority_created_at', 'parent', 'type', 'type_created_at', 'directing_departments_assignment_id', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_reporters_csv(location: str) -> str: """ Create CSV file with all `Reporter` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = Reporter.objects.annotate( is_anonymized=Case(When( Q(Q(email__isnull=True) | Q(email__exact='')) & Q(Q(phone__isnull=True) | Q(phone__exact='')) & Q(Q(email_anonymized=True) | Q(phone_anonymized=True)), then=True, ), default=False, output_field=BooleanField()), extra_properties=Value('null', output_field=CharField())).values( 'id', 'email', 'phone', 'created_at', 'updated_at', 'extra_properties', '_signal_id', _is_anonymized=map_choices('is_anonymized', [(True, 'True'), (False, 'False')]), ) csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'reporters.csv')) ordered_field_names = [ 'id', 'email', 'phone', 'is_anonymized', 'created_at', 'updated_at', 'extra_properties', '_signal_id', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_signals_assigned_user_csv(location: str) -> str: """ Create the CSV file with all `Signal - assigned user relation` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = Signal.objects.annotate( image=Value(None, output_field=CharField()), ).values( 'id', assigned_to=F('user_assignment__user__email'), assigned_at=F('user_assignment__created_at') ).exclude(user_assignment__user__isnull=True).exclude(user_assignment__user__email__exact='').order_by('created_at') csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'signals_assigned_user.csv')) ordered_field_names = ['id', 'assigned_to', 'assigned_at'] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_category_assignments_csv(location: str) -> str: """ Create CSV file with all `CategoryAssignment` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = CategoryAssignment.objects.values( 'id', 'created_at', 'updated_at', '_signal_id', main=F('category__parent__name'), sub=F('category__name'), _departments=StringAgg( 'category__departments__name', delimiter=', ', filter=Q(category__categorydepartment__is_responsible=True)), _extra_properties=Coalesce( Cast('extra_properties', output_field=CharField()), Value('null', output_field=CharField()))).order_by('id') csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'categories.csv')) ordered_field_names = [ 'id', 'main', 'sub', 'departments', 'created_at', 'updated_at', 'extra_properties', '_signal_id', ] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name
def create_signals_notes_csv(location: str) -> str: """ Create the CSV file with all `Signal - notes relation` objects. :param location: Directory for saving the CSV file :returns: Path to CSV file """ queryset = Note.objects.values( 'id', 'created_at', 'updated_at', '_signal_id', 'text', ).order_by( '_signal_id', '-created_at', ) csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'notes.csv')) ordered_field_names = ['id', 'created_at', 'updated_at', '_signal_id', 'text'] reorder_csv(csv_file.name, ordered_field_names) return csv_file.name