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_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_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 based on the Signals view
:param location: Directory for saving the CSV file
:returns: Path to CSV file
"""
queryset = TDOSignal.objects.all().order_by('id')
csv_file = queryset_to_csv_file(queryset, os.path.join(location, 'signals.csv'))
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
