def split_goitein_typedtexts(apps, schema_editor):
# after import from the metadata spreadsheet,
# there were too many footnotes associated with the generic
# unpublished source "Goitein, typed texts"
# To make it manageable, segment that source into volumes
# based on shelfmark prefixes
Source = apps.get_model("footnotes", "Source")
Footnote = apps.get_model("footnotes", "Footnote")
Fragment = apps.get_model("corpus", "Fragment")
Document = apps.get_model("corpus", "Document")
# get the source with too many footnotes
g_typedtexts = Source.objects.filter(title_en="typed texts",
authors__last_name="Goitein",
volume="").first()
# bail out if nothing to do
if not g_typedtexts:
return
footnotes = g_typedtexts.footnote_set.all()
if not footnotes.exists():
return
# we can't use our generic relation from document to footnotes in
# migration, so work with a list of document ids
footnote_doc_ids = footnotes.values_list("object_id", flat=True)
# get a list of shelfmark prefixes for all fragments associated
# with documents that are linked to our source via footnote
# For all but T-S, use string index & substring to get shelfmark
# portion before the first space
shelfmark_prefixes = set(
Fragment.objects.filter(documents__id__in=footnote_doc_ids).exclude(
shelfmark__startswith="T-S").annotate(prefix=Substr(
"shelfmark",
1,
StrIndex("shelfmark", V(" ")) - 1,
output_field=CharField(),
)).values_list("prefix", flat=True))
ts_prefixes = set(
# for T-S shelfmarks, get first 6 characters of shelfmark
Fragment.objects.filter(documents__id__in=footnote_doc_ids).filter(
shelfmark__startswith="T-S").annotate(prefix=Substr(
"shelfmark", 1, 6,
output_field=CharField()), ).values_list("prefix", flat=True))
# one exception: We want T-S Misc instead of T-S Mi
ts_prefixes.remove("T-S Mi")
ts_prefixes.add("T-S Misc")
shelfmark_prefixes = shelfmark_prefixes.union(ts_prefixes)
# create sources & footnote subsets for each prefix
for prefix in shelfmark_prefixes:
# create a new source with prefix as volume
vol_source = Source.objects.create(
title_en="typed texts",
volume=prefix,
source_type=g_typedtexts.source_type)
# associate Goitein as author of the new source
vol_source.authors.add(g_typedtexts.authors.first())
# move footnotes for fragments with this prefix to the new source
doc_ids = Document.objects.filter(
id__in=footnote_doc_ids,
fragments__shelfmark__startswith=prefix).values_list("id",
flat=True)
updated = footnotes.filter(object_id__in=doc_ids).update(
source=vol_source)
class RackViewSet(viewsets.ModelViewSet):
# View Housekeeping (permissions, serializers, filter fields, etc
def get_permissions(self):
if self.action in ADMIN_ACTIONS:
try:
user = User.objects.get(username=self.request.user.username)
datacenter_url = self.request.data.get('datacenter')
datacenter = Datacenter.objects.all().get(pk=datacenter_url[-2])
print(user.is_staff)
print(user.is_superuser)
print(len(user.permission_set.all().filter(name='global_asset')))
print(len(user.permission_set.all().filter(name='asset', datacenter=datacenter)))
if user.is_staff or user.is_superuser or len(user.permission_set.all().filter(name='global_asset')) > 0 or len(user.permission_set.all().filter(name='asset', datacenter=datacenter)) > 0:
permission_classes = [IsAuthenticated]
else:
permission_classes = [IsAdmin]
except:
print('exception')
permission_classes = [IsAdmin]
else:
permission_classes = [IsAuthenticated]
return [permission() for permission in permission_classes]
queryset = Rack.objects.all() \
.annotate(rack_letter=Substr('rack_number', 1, 1)) \
.annotate(numstr_in_rack=Substr('rack_number', 2))
queryset = queryset.annotate(number_in_rack=Cast('numstr_in_rack', IntegerField()))
ordering = ['datacenter', 'rack_letter', 'number_in_rack'] #['rack_letter', 'number_in_rack']
ordering_fields = RACK_ORDERING_FILTERING_FIELDS
filter_backends = [OrderingFilter,
djfiltBackend.DjangoFilterBackend,
RackFilter]
filterset_fields = RACK_ORDERING_FILTERING_FIELDS
def get_serializer_class(self):
if self.request.method == GET:
serializer_class = RackFetchSerializer
else:
serializer_class = RackSerializer
return serializer_class
# Overriding of super functions
def destroy(self, request, *args, **kwargs):
slots = ['u{}'.format(i) for i in range(1, 43)]
offending_assets = []
for slot in slots:
match = getattr(self.get_object(), slot)
if match:
offending_assets.append(match.hostname.__str__()
+ ' at ' +
match.rack.rack_number.__str__() +
' ' +
slot.__str__())
if len(offending_assets) > 0:
err_message = RACK_DESTROY_SINGLE_ERR_MSG + ', '.join(offending_assets)
return Response({
'Error:', err_message
}, status=status.HTTP_400_BAD_REQUEST)
return super().destroy(self, request, *args, **kwargs)
# New Actions
@action(detail=False, methods=[GET])
def filter_fields(self, request, *args, **kwargs):
fields = RACK_ORDERING_FILTERING_FIELDS.copy()
fields.extend(['rack_num_start', 'rack_num_end'])
return Response({
'filter_fields': fields
})
@action(detail=False, methods=[GET])
def sorting_fields(self, request, *args, **kwargs):
return Response({
'sorting_fields': self.ordering_fields
})
@action(detail=False, methods=[POST, DELETE])
def many(self, request, *args, **kwargs):
try:
dc = request.data['datacenter']
srn = request.data['rack_num_start']
ern = request.data['rack_num_end']
except KeyError:
return Response({
'Error': RACK_MANY_INCOMPLETE_QUERY_PARAMS_ERROR_MSG
}, status=status.HTTP_400_BAD_REQUEST)
try:
s_letter = srn[0].upper()
e_letter = ern[0].upper()
s_number = int(srn[1:])
e_number = int(ern[1:])
try:
assert (s_letter <= e_letter)
except AssertionError:
return Response({
'Error': RACK_MANY_BAD_LETTER_ERROR_MSG
}, status=status.HTTP_400_BAD_REQUEST)
try:
assert (s_number <= e_number)
except AssertionError:
return Response({
'Error': RACK_MANY_BAD_NUMBER_ERROR_MSG
}, status=status.HTTP_400_BAD_REQUEST)
rack_numbers = [x + y
for x in
(chr(i) for i in range(ord(s_letter), ord(e_letter) + 1))
for y in
(str(j) for j in range(s_number, e_number + 1))
]
create_success = []
create_failure = []
delete_success = []
delete_nonexistent = []
delete_failure = []
for rn in rack_numbers:
rn_request_data = {
"datacenter": dc,
"rack_number": rn
}
if request.method == POST:
try:
serializer = self.get_serializer(data=rn_request_data)
serializer.is_valid(raise_exception=True)
serializer.save()
create_success.append(rn)
except ValidationError:
create_failure.append(rn)
elif request.method == DELETE:
try:
rack = self.queryset.filter(datacenter=dc).get(rack_number__iexact=rn)
except self.queryset.model.DoesNotExist:
delete_nonexistent.append(rn)
continue
try:
rack.delete()
except ProtectedError:
delete_failure.append(rn)
continue
delete_success.append(rn)
except (IndexError, ValueError) as e:
return Response({
'Error': e.detail
}, status=status.HTTP_400_BAD_REQUEST)
# return Response({
# 'results': ', '.join(results)
# }, status=status.HTTP_207_MULTI_STATUS)
if request.method == POST:
return Response({
'results': {
'successfully_created': '{} racks'.format(len(create_success)),
'failed_to_create': '{} racks'.format(len(create_failure)),
'failed_racks': ', '.join(create_failure)
}
}, status=status.HTTP_207_MULTI_STATUS)
if request.method == DELETE:
return Response({
'results': {
'successfully_deleted': '{} racks'.format(len(delete_success)),
'failed_to_delete_nonexistent': '{} racks'.format(len(delete_nonexistent)),
'failed_to_delete_occupied': '{} racks'.format(len(delete_failure)),
'failed_racks': ', '.join(delete_failure)
}
}, status=status.HTTP_207_MULTI_STATUS)
@action(detail=True, methods=[GET])
def assets(self, request, *args, **kwargs):
matches = self.get_object().asset_set
serializer = AssetShortSerializer(matches, many=True, context={'request': request})
return Response(serializer.data)
@action(detail=True, methods=[GET])
def is_empty(self, request, *args, **kwargs):
u_filled = 0
slots = ['u{}'.format(i) for i in range(1, 43)]
for field_name in slots:
if getattr(self.get_object(), field_name):
u_filled += 1
if u_filled > 0:
return Response({
'is_empty': 'false'
})
return Response({
'is_empty': 'true'
})
@action(detail=True, methods=[GET])
def get_open_pdu_slots(self, request, *args, **kwargs):
pdu_l = self.get_object().pdu_l
pdu_r = self.get_object().pdu_r
pp_l = pdu_l.power_port_set.all() if pdu_l else []
pp_r = pdu_r.power_port_set.all() if pdu_r else []
l_occ = [int(pp.port_number) for pp in pp_l]
r_occ = [int(pp.port_number) for pp in pp_r]
l_free = [x for x in range(1, 25) if x not in l_occ]
r_free = [x for x in range(1, 25) if x not in r_occ]
resp_list = {'left': l_free, 'right': r_free}
# r_free = [True if x not in r_occ else False for x in range(0, 25)]
#
# l_free = [True if x not in l_occ else False for x in range(0, 25)]
# r_free = [True if x not in r_occ else False for x in range(0, 25)]
#
# resp_list = []
# for x in range(0,25):
# resp_list.append({'pduSlot': x, 'left': l_free[x], 'right': r_free[x]})
return Response({
'pdu_slots': resp_list
})
def lookups(self, request, model_admin):
return [
(x, x)
for x in Term.objects.annotate(year=Substr('code', 1, 4))
.order_by('-year').distinct().values_list('year', flat=True)
]
def context_navbar(request):
channels = Channel.objects.filter(
visible=True, video__is_draft=False).distinct().annotate(
video_count=Count("video", distinct=True)).prefetch_related(
Prefetch("themes",
queryset=Theme.objects.filter(
parentId=None).distinct().annotate(
video_count=Count("video", distinct=True))))
all_channels = Channel.objects.all().distinct().annotate(
video_count=Count("video", distinct=True)).prefetch_related(
Prefetch("themes",
queryset=Theme.objects.all().distinct().annotate(
video_count=Count("video", distinct=True))))
types = Type.objects.filter(video__is_draft=False).distinct().annotate(
video_count=Count("video", distinct=True))
disciplines = Discipline.objects.filter(
video__is_draft=False).distinct().annotate(
video_count=Count("video", distinct=True))
linkFooter = LinkFooter.objects.all()
owners_filter_args = {
'video__is_draft': False,
}
if MENUBAR_HIDE_INACTIVE_OWNERS:
owners_filter_args['is_active'] = True
if MENUBAR_SHOW_STAFF_OWNERS_ONLY:
owners_filter_args['is_staff'] = True
VALUES_LIST.append('video_count')
VALUES_LIST.append('fl_name')
VALUES_LIST.append('fl_firstname')
owners = Owner.objects.filter(
**owners_filter_args).distinct().order_by(ORDER_BY).annotate(
video_count=Count("video", distinct=True)).annotate(
fl_name=Lower(Substr("last_name", 1, 1))).annotate(
fl_firstname=Lower(Substr("first_name", 1, 1))).order_by(
'fl_name').values(*list(VALUES_LIST))
if not request.user.is_authenticated:
listowner = {}
else:
listowner = get_list_owner(owners)
LAST_VIDEOS = get_last_videos() if request.path == "/" else None
list_videos = Video.objects.filter(encoding_in_progress=False,
is_draft=False)
VIDEOS_COUNT = list_videos.count()
VIDEOS_DURATION = str(
timedelta(seconds=list_videos.aggregate(Sum(
'duration'))['duration__sum'])) if list_videos.aggregate(
Sum('duration'))['duration__sum'] else 0
return {
'ALL_CHANNELS': all_channels,
'CHANNELS': channels,
'TYPES': types,
'OWNERS': owners,
'DISCIPLINES': disciplines,
'LISTOWNER': json.dumps(listowner),
'LAST_VIDEOS': LAST_VIDEOS,
'LINK_FOOTER': linkFooter,
'VIDEOS_COUNT': VIDEOS_COUNT,
'VIDEOS_DURATION': VIDEOS_DURATION
}
def _handle(self, *args, **options):
king = kings[options['king']]
run_timestamp = datetime.now()
inputfile = f'/tmp/osm-{king["name"]}.pbf'
if options['download']:
self.out1(f'Descargando mapa de {king["name"]} de geofabrik')
# url = 'http://download.geofabrik.de/south-america-latest.osm.pbf'
url = king['url']
self.out2(url)
f, d = request.urlretrieve(url, inputfile, lambda nb, bs, fs, url=url: self.reporthook(nb, bs, fs, url))
if 'clip_country' in king and king['clip_country'] == True:
self.out1('Clip big pbf file to the country adminarea')
self.out2('get full country admin area polygon')
KING_ADMIN_AREA = get_admin_area(inputfile, king['id'], self.out2)
self.out2('make poly file for osmconvert')
filename = make_poly_file(KING_ADMIN_AREA['geometry'].buffer(0.001))
self.out2(f'generated temp poly file at {filename}')
self.out2('run osmconvert to clip pbf file')
# result = subprocess.run(['osmconvert', inputfile, f'-B={filename}', '--complete-ways', '--complete-multipolygons', f'-o={inputfile}-cropped.pbf'], stdout=subprocess.PIPE)
result = subprocess.run(['osmium', 'extract', f'-p{filename}', inputfile, f'-o{inputfile}-cropped.pbf'], stdout=subprocess.PIPE)
inputfile = f'{inputfile}-cropped.pbf'
self.out2(f'pbf clipped at {inputfile}')
#######################
# Adminareas de osm #
#######################
# 1. ./manage.py update_osm --download --admin_areas
# 2. ./manage.py update_osm -f /tmp/part-all.o5m --admin_areas
if options['admin_areas']:
self.out1('Admin Areas')
KING_ID = king['id'] # osm_id king
OLD_KING = list(AdministrativeArea.objects.filter(osm_id=KING_ID))
admin_areas, KING = get_admin_areas(run_timestamp, inputfile, king['country_code'], KING_ID, self.out2)
KING_GEOM_BUFF = KING['geometry_simple'].buffer(0.01)
def fuzzy_contains(out_geom, in_geom, buffer=0, attempts=3):
try:
return (
out_geom.intersects(in_geom) and # optimization
out_geom.buffer(buffer).contains(in_geom)
)
except GEOSException as e:
if attempts > 0:
self.out2(f'''
out_geom.valid: {out_geom.valid}
out_geom.valid_reason: {out_geom.valid_reason}
in_geom.valid: {in_geom.valid}
in_geom.valid_reason: {in_geom.valid_reason}
''')
return fuzzy_contains(maybe_make_valid(out_geom), maybe_make_valid(in_geom), buffer, attempts - 1)
else:
raise
def get_parent_aa(node, geometry):
try:
if (
node['data']['osm_id'] is KING_ID or
fuzzy_contains(node['data']['geometry_simple'], geometry, 0.01)
):
parent_aa = None
for child in node['children']:
parent_aa = get_parent_aa(child, geometry)
if parent_aa is not None:
break
if parent_aa is None:
return node
else:
return parent_aa
else:
return None
except Exception:
# print('node.geometry', node['data']['geometry'])
print('node.data', node['data']['name'])
print('node.osm_id', node['data']['osm_id'])
print('node.osm_type', node['data']['osm_type'])
# traceback.print_exc()
raise
tree = {
'children': [],
'data': {
'import_timestamp': run_timestamp,
'geometry': KING['geometry_simple'],
'geometry_simple': KING['geometry_simple'],
'osm_id': KING['osm_id'],
'osm_type': KING['osm_type'],
'name': KING['name'],
'tags': KING['tags'],
'country_code': king['country_code'],
}
}
for li in admin_areas:
# aa = admin area
for aa in li:
try:
if not aa['geometry'].intersects(KING_GEOM_BUFF):
continue
except GEOSException as e:
self.out2(f'{str(e)}\n{aa["osm_id"]} {aa["name"]}')
try:
parent_aa = get_parent_aa(tree, aa['geometry'])
aa.pop('admin_level')
if 'ways' in aa:
aa.pop('ways')
else:
self.out2(f" {aa['osm_id']}: {aa['name']}, does not have 'ways' attribute")
if parent_aa is None:
tree['children'].append({'children': [], 'data': aa})
else:
parent_aa['children'].append({'children': [], 'data': aa})
except GEOSException as e:
self.out2(f'{str(e)}\n{tree["data"]["osm_id"]} {tree["data"]["name"]}\n{aa["osm_id"]} {aa["name"]}')
def print_tree(node, level=0):
print(f'{" " * level} {level} {node["data"]["name"].decode("utf-8")}')
for node in node['children']:
print_tree(node, level + 1)
# print_tree(tree)
AdministrativeArea.load_bulk([tree])
for K in OLD_KING:
K.delete()
# fix invalid geometries
# TODO: I think these should be makeValid(ated) earlier in the process, not here but ASAP
# that way we would avoid some issues around intersections that fail earlier in the process of creating the adminareas tree
# the makevalid function is only available in postgis (is not in a library like GEOS)
# in ~4000 shapes we had 10 not valid, so we can use something like `if not geom.valid: cursor.exec('SELECT ST_MAKEVALID(POLYGON('WKT text here'));')`
AdministrativeArea.objects.filter(geometry_simple__isvalid=False).update(geometry_simple=MakeValid(F('geometry_simple')))
AdministrativeArea.objects.filter(geometry__isvalid=False).update(geometry_simple=MakeValid(F('geometry')))
#######################
# recorridos de osm #
#######################
if options['cross']:
crs = {'init': 'epsg:4326'}
self.out1('Cross osm recorridos')
self.out2('Obteniendo bus routes de osm planet_osm_line')
bus_routes = gpd.read_postgis(
"""
# esto cambiarlo para no usar mas planet_osm_line (osm2pgsql), usar osmosis para construir las bus_routes
# SELECT
# @osm_id AS osm_id, -- @=modulus operator
# name,
# ref,
# st_linemerge(st_union(way)) AS way
# FROM
# planet_osm_line
# WHERE
# route = 'bus'
# GROUP BY
# osm_id,
# name,
# ref
""",
connection,
geom_col='way',
crs=crs
)
bus_routes.set_index('osm_id', inplace=True)
self.out2('Creando geodataframe')
bus_routes_buffer = gpd.GeoDataFrame({
'osm_id': bus_routes.index,
'way': bus_routes.way,
'way_buffer_40': bus_routes.way.buffer(0.0004),
'way_buffer_40_simplify': bus_routes.way.simplify(0.0001).buffer(0.0004),
'name': bus_routes.name
}, crs=crs).set_geometry('way_buffer_40_simplify')
self.out2('Obteniendo recorridos de cualbondi core_recorridos')
core_recorrido = gpd.read_postgis(
"""
SELECT
cr.id,
cr.nombre,
cr.linea_id,
cr.ruta,
cl.nombre AS linea_nombre
FROM
core_recorrido cr
JOIN core_linea cl ON (cr.linea_id = cl.id)
-- JOIN catastro_ciudad_recorridos ccr ON (ccr.recorrido_id = cr.id)
--WHERE
-- ccr.ciudad_id = 1
;
""",
connection,
geom_col='ruta',
crs=crs
)
core_recorrido.set_index('id', inplace=True)
self.out2('Creando geodataframe')
core_recorrido_buffer = gpd.GeoDataFrame({
'id': core_recorrido.index,
'ruta': core_recorrido.ruta.simplify(0.0001),
'ruta_buffer_40_simplify': core_recorrido.ruta.simplify(0.0001).buffer(0.0004),
'nombre': core_recorrido.nombre,
'linea_id': core_recorrido.linea_id,
}, crs=crs).set_geometry('ruta')
self.out2('Generando intersecciones')
intersections = gpd.sjoin(core_recorrido_buffer, bus_routes_buffer, how='inner', op='intersects')
self.out2('Copiando indice, id')
intersections['id'] = intersections.index
self.out2('Copiando indice, osm_id')
intersections['osm_id'] = intersections.index_right
self.out2('Drop indice, osm_id')
intersections.drop('index_right', inplace=True, axis=1)
self.out2('Generando match [id, osm_id]')
intersections = intersections[['id', 'osm_id']]
self.out2('Generando indice de match [id, osm_id]')
intersections.index = range(len(intersections))
self.out2('Generando way_buffer_40_simplify')
way_buffer_40_simplify = gpd.GeoSeries(
bus_routes_buffer.loc[intersections.osm_id].way_buffer_40_simplify.values, crs=crs)
self.out2('Generando ruta_buffer_40_simplify')
ruta_buffer_40_simplify = gpd.GeoSeries(
core_recorrido_buffer.loc[intersections.id].ruta_buffer_40_simplify.values, crs=crs)
self.out2('Generando symmetric_difference')
diffs = ruta_buffer_40_simplify.symmetric_difference(way_buffer_40_simplify).area.values
self.out2('Generando norm_factor')
norm_factor = ruta_buffer_40_simplify.area.values + way_buffer_40_simplify.area.values
self.out2('Generando diffs')
diffs = (diffs / norm_factor).tolist()
self.out2('Pasando osm_ids a lista')
osm_ids = intersections.osm_id.values.tolist()
self.out2('Pasando osm_names a lista')
osm_names = bus_routes.loc[osm_ids].name.values.tolist()
# ways = bus_routes.loc[osm_ids].way.map(lambda x: x.wkb).values.tolist()
self.out2('Pasando recorrido_ids de intersections a lista')
recorrido_ids = intersections['id'].values.tolist()
self.out2('Pasando linea_ids a lista')
linea_ids = core_recorrido.loc[recorrido_ids].linea_id.values.tolist()
# rutas = core_recorrido.loc[recorrido_ids].ruta.map(lambda x: x.wkb).values.tolist()
self.out2('Pasando recorrido_nombres a lista')
recorrido_nombres = core_recorrido.loc[recorrido_ids].nombre.values.tolist()
# ruta_buffer_40_simplifys = ruta_buffer_40_simplify.map(lambda x: x.wkb).values.tolist()
# way_buffer_40_simplifys = way_buffer_40_simplify.map(lambda x: x.wkb).values.tolist()
self.out2('Pasando linea_nombres a lista')
linea_nombres = core_recorrido.loc[recorrido_ids].linea_nombre.values.tolist()
self.out2('DROP TABLE crossed_areas')
cu = connection.cursor()
cu.execute("DROP TABLE IF EXISTS crossed_areas;")
cu.execute('DROP INDEX IF EXISTS crossed_areas_recorrido_id;')
cu.execute('DROP INDEX IF EXISTS crossed_areas_area;')
self.out2('CREATE TABLE crossed_areas')
cu.execute(
"""
CREATE TABLE crossed_areas (
area FLOAT,
linea_id INTEGER,
recorrido_id INTEGER,
osm_id BIGINT,
linea_nombre VARCHAR(100),
recorrido_nombre VARCHAR(100),
osm_name TEXT
);
"""
)
self.out2('Preparando lista de values')
data = list(zip(diffs, linea_ids, recorrido_ids, osm_ids, linea_nombres, recorrido_nombres, osm_names))
self.out2('Ejecutando insert query')
insert_query = """
INSERT INTO crossed_areas (
area,
linea_id,
recorrido_id,
osm_id,
linea_nombre,
recorrido_nombre,
osm_name
)
VALUES %s
"""
execute_values(cu, insert_query, data)
self.out2('Commit insert query')
connection.commit()
self.out2('Generando indice crossed_areas_recorrido_id')
cu.execute('CREATE INDEX crossed_areas_recorrido_id ON crossed_areas (recorrido_id);')
cu.execute('CREATE INDEX crossed_areas_area ON crossed_areas (area);')
self.out2('LISTO!')
if options['update_routes']:
# TODO: consider also trains / trams / things that have fixed stops
self.out1('UPDATE ROUTES FROM OSM')
self.out2('process .osm input file')
# we can see if all tags are ok and give hints to mappers according to spec:
# https://wiki.openstreetmap.org/w/index.php?oldid=625726
# https://wiki.openstreetmap.org/wiki/Buses
p = pyosmptparser.Parser(inputfile)
pts = p.get_public_transports(500)
routetypes_stops = ['train', 'subway', 'monorail', 'tram', 'light_rail']
routetypes = routetypes_stops + ['bus', 'trolleybus']
buses = {}
counters = {}
for pt in pts:
self.out2(pt.id, end=': ')
buses[pt.id] = {
'pt': pt,
'way': LineString(pt.geometry[0]) if pt.status.code < 500 and len(pt.geometry) == 1 else None,
'paradas_completas': (pt.tags['route'] in routetypes_stops or len(pt.stops) > 20) and king['paradas_completas'],
}
counters.setdefault(pt.status.code, 0)
counters[pt.status.code] += 1
self.out2('{}: {} > {}'.format(pt.status.code, pt.status.detail, pt.tags['name'], start=''))
self.out2('status | count')
for key, counter in sorted(counters.items(), key=lambda e: e[1], reverse=True):
self.out2('{} | {}'.format(key, counter))
# HINT: run migrations in order to have osmbot in the db
user_bot_osm = get_user_model().objects.get(username='******')
self.out2('fixer routine')
# add all as new routes
if options['add_routes']:
for bus_osm_id, bus in buses.items():
# try to fix way, returns None if it can't
way = bus['way']
# recorrido proposed creation checks
if bus['way'] is None:
self.out2('{} : SKIP {}'.format(bus['pt'].id, bus['pt'].status.code))
continue
# set proposal fields
# rp.paradas_completas = len(bus['stops']) > options['paradas_completas_threshold'] o usar una config en el pais KINGs
rp = RecorridoProposed(nombre=bus['pt'].tags['name'][:199])
rp.osm_id = bus['pt'].id
rp.ruta = bus['way']
rp.ruta_last_updated = datetime.utcfromtimestamp(int(bus['pt'].info['timestamp'])).replace(tzinfo=pytz.utc)
rp.osm_version = int(bus['pt'].info['version'])
rp.import_timestamp = run_timestamp
rp.paradas_completas = bus['paradas_completas']
rp.type = bus['pt'].tags['route']
rp.king = king['id']
rp.country_code = king['country_code']
if not options['dry-run']:
rp.save(user=user_bot_osm)
self.out2('{} | AUTO ACCEPTED!'.format(bus['pt'].id))
if not options['dry-run']:
rp.aprobar(user_bot_osm)
# add stops!
if not options['dry-run'] and len(bus['pt'].stops) > 0:
self.out2(f'ADDing STOPS {len(bus["pt"].stops)}', end=' > ')
count_created = 0
count_associated = 0
for s in bus['pt'].stops:
parada, created = Parada.objects.update_or_create(
osm_id=s.id,
defaults={
'import_timestamp': run_timestamp,
'nombre': s.tags['name'] if 'name' in s.tags else f'{s.lon}, {s.lat}',
'latlng': Point(s.lon, s.lat),
'tags': s.tags,
'country_code': king['country_code'],
}
)
if created:
count_created = count_created + 1
horario, created = Horario.objects.update_or_create(
recorrido=rp.recorrido,
parada=parada,
)
if created:
count_associated = count_associated + 1
self.out2(f'CREATED STOPS {count_created}, ASSOCIATED {count_associated}')
else:
for rec in Recorrido.objects.filter(osm_id__isnull=False, ruta__intersects=AdministrativeArea.objects.get(osm_id=king['id']).geometry):
# try to fix way, returns None if it can't
adminareas_str = ', '.join(AdministrativeArea.objects.filter(geometry__intersects=rec.ruta).order_by('depth').values_list('name', flat=True))
osm_id = rec.osm_id
if osm_id in buses:
way = buses[osm_id]['way']
status = buses[osm_id]['pt'].status.code
osm_osm_version = buses[osm_id]['pt'].info['version']
osm_last_updated = buses[osm_id]['pt'].info['timestamp']
name = buses[osm_id]['pt'].tags['name']
paradas_completas = buses[osm_id]['paradas_completas']
routetype = buses[osm_id]['pt'].tags['route']
else:
way = None
status = None
osm_osm_version = -1
osm_last_updated = None
name = None
paradas_completas = None
routetype = None
ilog = ImporterLog(
osm_id=osm_id,
osm_version=osm_osm_version,
osm_timestamp=osm_last_updated,
run_timestamp=run_timestamp,
proposed=False,
accepted=False,
status=status,
proposed_reason='',
accepted_reason='',
osm_administrative=adminareas_str,
osm_name=name,
type=routetype,
king=king['name'],
)
ilog.save()
# recorrido proposed creation checks
if way is None:
self.out2('{} | {} : SKIP {}'.format(rec.id, osm_id, status))
ilog.proposed_reason = 'broken'
ilog.save()
continue
if rec.ruta_last_updated >= osm_last_updated:
self.out2('{} | {} : SKIP, older than current recorrido {}, ({} >= {})'.format(rec.id, osm_id, status, rec.ruta_last_updated, osm_last_updated))
ilog.proposed_reason = 'older than current recorrido'
ilog.save()
continue
# check if there is another proposal already submitted (with same timestamp or greater)
if RecorridoProposed.objects.filter(osm_id=osm_id, parent=rec.uuid, ruta_last_updated__gte=osm_last_updated).exists():
self.out2('{} | {} : SKIP, older than prev proposal {}'.format(rec.id, osm_id, status))
ilog.proposed_reason = 'older than previous proposal'
ilog.save()
continue
# update previous proposal if any
previous_proposals = RecorridoProposed.objects.filter(
osm_id=osm_id,
parent=rec.uuid,
ruta_last_updated__lt=osm_last_updated,
logmoderacion__newStatus='E'
).order_by('-ruta_last_updated')
if len(previous_proposals) > 0:
proposal_info = 'UPDATE prev proposal'
rp = previous_proposals[0]
# else create a new proposal
else:
proposal_info = 'NEW prev proposal'
rp = RecorridoProposed.from_recorrido(rec)
# set proposal fields
rp.ruta = way
rp.ruta_last_updated = osm_last_updated
rp.osm_version = osm_osm_version # to not be confsed with Recorrido.osm_version
rp.import_timestamp = run_timestamp
rp.paradas_completas = paradas_completas if paradas_completas is not None else king['paradas_completas']
rp.type = routetype
if not options['dry-run']:
rp.save(user=user_bot_osm)
ilog.proposed = True
ilog.save()
# AUTO ACCEPT CHECKS
if rec.osm_version is None:
self.out2('{} | {} : {} | NOT auto accepted: previous accepted proposal does not come from osm'.format(rec.id, osm_id, proposal_info))
ilog.accepted_reason = 'previous accepted proposal does not come from osm'
ilog.save()
continue
if RecorridoProposed.objects.filter(parent=rec.uuid).count() > 1:
self.out2('{} | {} : {} | NOT auto accepted: another not accepted recorridoproposed exists for this recorrido'.format(rec.id, osm_id, proposal_info))
ilog.accepted_reason = 'another not accepted proposal exists for this recorrido'
ilog.save()
continue
self.out2('{} | {} : {} | AUTO ACCEPTED!'.format(rec.id, osm_id, proposal_info))
if not options['dry-run']:
rp.aprobar(user_bot_osm)
ilog.accepted = True
ilog.save()
#
# TODO: think how to do "stops" change proposals
# el recorrido podria tener una lista de paradas, una lista de latlongs nada mas
# cambiar una parada es cambiar el recorrido tambien. El problema es que las paradas se comparten
#
#######################
# POIs de osm #
#######################
if options['pois']:
self.out2('Eliminando indices viejos de la base de datos')
cu = connection.cursor()
# cu.execute('DROP INDEX IF EXISTS catastrocalle_nomnormal_gin;')
cu.execute('DROP INDEX IF EXISTS catastropoi_nomnormal_gin;')
self.out2('counting')
result = subprocess.run(f'osmium fileinfo -g data.count.nodes -e {inputfile}'.split(' '), stdout=subprocess.PIPE)
nodes_count = int(result.stdout)
self.out2(f'end counting = {nodes_count}')
class Unaccent(Func):
function = 'UNACCENT'
arity = 1
class RegexpReplace(Func):
function = 'REGEXP_REPLACE'
arity = 3
class POIsHandler(osmium.SimpleHandler):
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.nodes_current = 0
self.nodes_added = 0
# # TODO: necesito las calles por ahora? son muchas en espana
# def way(self, w):
# if 'highway' in w.tags and 'route' not in w.tags and 'name' in w.tags:
# points = []
# for node in w.nodes:
# points.append([float(node.x) / 10000000, float(node.y) / 10000000])
# linestring = LineString(points, srid=4326)
# if Recorrido.objects.filter(ruta__intersects=linestring).exists():
# print('|', end='')
# Calle.objects.update_or_create(
# osm_id=w.id,
# defaults={
# 'nom': w.tags['name'][:200],
# 'nom_normal': Substr(Trim(RegexpReplace(Upper(Unaccent(Value(w.tags['name']))), r'AV\.|AVENIDA|CALLE|DIAGONAL|BOULEVARD', '')), 1, 199),
# 'way': linestring,
# }
# )
def node(self, n):
self.nodes_current += 1
# TODO: add 'shop' in n.tags also, more info in https://github.com/gravitystorm/openstreetmap-carto/blob/96c64fa5b0449e79c17e39626f3b8f38c96a12bb/project.mml#L1504
if 'amenity' in n.tags and 'name' in n.tags and len(n.tags['name']) > 2:
try:
point = Point([float(n.location.x) / 10000000, float(n.location.y) / 10000000], srid=4326)
# this is a little slow, but it uses indexes :)
# TODO: improve this by using in-memory pandas queries like we did with the 'cross'
q = Recorrido.objects \
.order_by() \
.annotate(cond=RawSQL("ST_Intersects(ST_Buffer(%s::geography, 400, 2)::geometry, ruta)", (point.ewkb,), output_field=BooleanField())) \
.filter(cond=True) \
.only('id') \
.exists()
if q:
defaults = {
'tags': {k:v for k,v in n.tags},
'nom': n.tags['name'][:200],
'nom_normal': Substr(Trim(Upper(Unaccent(Value(n.tags['name'])))), 1, 200),
'latlng': point,
'country_code': king['country_code'],
}
Poi.objects.update_or_create(
osm_id=n.id,
osm_type='n',
defaults=defaults
)
self.nodes_added += 1
print(f'[{self.nodes_current*100/nodes_count:7.3f}%] / Nodes added: {self.nodes_added} | processed: {self.nodes_current} / {nodes_count}')
except Exception as e:
print(f'Could not save, exception {e}')
self.out2('POIS from ways and nodes with osmosis')
h = POIsHandler()
with transaction.atomic():
h.apply_file(inputfile, locations=True)
self.out2('POIS from AdministrativeAreas in database')
adminareas = AdministrativeArea.objects.get(osm_id=king['id']).get_descendants()
adminareascount = adminareas.count()
i = 0
for aa in adminareas:
i = i + 1
self.out2(f' [{i*100/adminareascount:7.3f}%] {aa.name}')
Poi.objects.update_or_create(
osm_id=aa.osm_id,
osm_type=aa.osm_type,
defaults={
'tags': aa.tags,
'nom': aa.name,
'nom_normal': Substr(Trim(Upper(Unaccent(Value(aa.name)))), 1, 200),
'latlng': aa.geometry.centroid,
'country_code': king['country_code'],
}
)
self.out2('Generando slugs')
total = Poi.objects.filter(slug__isnull=True).count()
i = 0
start = time.time()
for o in Poi.objects.filter(slug__isnull=True):
o.save()
i = i + 1
if i % 50 == 0 and time.time() - start > 1:
start = time.time()
self.out2('{}/{} ({:2.0f}%)'.format(i, total, i * 100.0 / total))
# unir catastro_poicb (13 y 60, 13 y 66, 13 y 44) con catastro_poi (osm_pois)
# self.out2('Mergeando POIs propios de cualbondi')
# for poicb in Poicb.objects.all():
# Poi.objects.create(nom_normal = poicb.nom_normal.upper(), nom = poicb.nom, latlng = poicb.latlng)
# self.out2('Purgando nombres repetidos')
# cu.execute('delete from catastro_poi where id not in (select min(id) from catastro_poi group by nom_normal)')
self.out1('Regenerando indices')
# self.out2('Generando catastro_calle')
# cu.execute('CREATE INDEX catastrocalle_nomnormal_gin ON catastro_calle USING gin (nom_normal gin_trgm_ops);')
self.out2('Generando catastro_poi')
cu.execute('CREATE INDEX catastropoi_nomnormal_gin ON catastro_poi USING gin (nom_normal gin_trgm_ops);')
##########################
# Intersections de osm #
##########################
if options['intersections']:
self.out1('Generando Intersecciones')
cu = connection.cursor()
cu.execute('delete from catastro_interseccion')
cu.execute('''
SELECT
SEL1.nom || ' y ' || SEL2.nom as nom,
upper(translate(SEL1.nom || ' y ' || SEL2.nom, 'áéíóúÁÉÍÓÚäëïöüÄËÏÖÜñÑàèìòùÀÈÌÒÙ', 'AEIOUAEIOUAEIOUAEIOUNNAEIOUAEIOU')) as nom_normal,
ST_Intersection(SEL1.way, SEL2.way) as latlng
FROM
catastro_calle AS SEL1
join catastro_calle as SEL2 on (ST_Intersects(SEL1.way, SEL2.way) and ST_GeometryType(ST_Intersection(SEL1.way, SEL2.way):: Geometry)='ST_Point' )
''')
self.out2('Generando slugs')
intersections = cu.fetchall()
total = len(intersections)
i = 0
for inter in intersections:
i = i + 1
Interseccion.objects.create(nom=inter[0], nom_normal=inter[1], latlng=inter[2])
if (i * 100.0 / total) % 1 == 0:
self.out2('{:2.0f}%'.format(i * 100.0 / total))
# self.out1('Eliminando tablas no usadas')
# cu.execute('drop table planet_osm_roads;')
# cu.execute('drop table planet_osm_polygon;')
# cx.commit()
# cx.close()
self.out1('LISTO!')
def get_task_list(pk=None,
skill=None,
filter_author=False,
author=None,
filter_order=False,
filter_status=False,
order=None,
add_author_data=False,
add_skill_data=False,
add_order_data=False,
add_is_my_task=False,
add_rating=False,
add_fl_count=False,
add_is_fl=False,
user=None,
ordered='-created',
count=None):
"""
Return a list of task.
The arguments:
``skill`` - set filter on Task.skill
``filter_author`` - set filter on Task.author. A string. Can be:
- '==': select all task with author == author
- '!=': select all task with author != author
- None: select all task without filter
``author`` - author of task (model - User)
``filter_order`` - set filter on Order. A string. Can be:
- '==': select all task for which Order == order
- '!=': select all task for which Order != order
- '*': select all task for which there is an Order
- '-': select all task for which there is not an Order
- None: select all task without filter
``add_author_data`` - add select_related('author__profile')
``add_skill_data`` - add select_related('skill')
``add_fl_count`` - add fields:
favourite_count=Count('task_favourites')
like_count=Count('task_like')
"""
task_qset = Task.objects
if add_author_data:
task_qset = task_qset.select_related('author__profile')
if add_skill_data:
task_qset = task_qset.select_related('skill')
if pk:
task_qset = task_qset.filter(id=pk)
elif not skill and not filter_author and not filter_order:
task_qset = task_qset.all()
else:
# set filter on skill
if skill:
task_qset = task_qset.filter(skill=skill)
# set filter on author
if filter_author == '==':
task_qset = task_qset.filter(author=author)
elif filter_author == '!=':
task_qset = task_qset.filter(~Q(author=author))
# set filter on order
if filter_order == '==':
task_qset = task_qset.filter(Q(order_task=order))
elif filter_order == '!=':
task_qset = task_qset.filter(~Q(order_task=order))
elif filter_order == '*':
task_qset = task_qset.filter(~Q(order_task=None))
elif filter_order == '-':
task_qset = task_qset.filter(Q(order_task=None))
if add_fl_count:
# get favourite_count via Subquery
task_qset = task_qset.annotate(favourite_count=Subquery(
TaskFavourites.objects.filter(task=OuterRef('pk')).values(
'task').annotate(favourite_count=Count('task'), ).values(
'favourite_count')[:1]))
# get like_count via Subquery
task_qset = task_qset.annotate(like_count=Subquery(
TaskLike.objects.filter(task=OuterRef('pk')).values('task').
annotate(like_count=Count('task'), ).values('like_count')[:1]))
if user and user.is_authenticated:
if add_is_my_task:
task_qset = task_qset.annotate(is_my_task=Max(
Case(
When(author=user, then=1),
default=0,
output_field=IntegerField(),
)), )
if add_order_data:
# get data of order of task via Subquery
task_qset = task_qset.annotate(order_status=Subquery(
Order.objects.filter(Q(task=OuterRef('pk')) & Q(
user=user)).values('status', ).annotate(order_status=Max(
'status'), ).values('order_status')[:1]))
# task_qset = task_qset.annotate(
# order_id=Max(Case(
# When(order_task__user=user, then='order_task__id'),
# default=None,
# output_field=CharField(),
# )),
# order_status=Max(Case(
# When(order_task__user=user, then='order_task__status'),
# default=None,
# output_field=CharField(),
# )),
# order_created=Max(Case(
# When(order_task__user=user, then='order_task__status'),
# default=None,
# output_field=CharField(),
# )),
# )
# add order_status_display
choices = dict(Order._meta.get_field('status').flatchoices)
whens = [
When(order_status=k, then=Value(v))
for k, v in choices.items()
]
task_qset = task_qset.annotate(order_status_display=Case(
*whens, default=None, output_field=CharField()))
if filter_status == '-':
task_qset = task_qset.filter(Q(order_status=None))
elif filter_status == '*':
task_qset = task_qset.filter(~Q(order_status=None))
elif filter_status:
task_qset = task_qset.filter(Q(order_status=filter_status))
# if add_is_fl:
# task_qset = task_qset.annotate(
# is_favorite=Sum(Case(
# When(task_favourites__user=user, then=1),
# default=0,
# output_field=IntegerField(),
# )),
# is_like=Sum(Case(
# When(task_like__user=user, then=1),
# default=0,
# output_field=IntegerField(),
# ))
# )
if add_rating:
# get rating via Subquery
task_qset = task_qset.annotate(rating=Subquery(
TaskRatings.objects.filter(task=OuterRef('pk')).values('task').
annotate(the_sum=Avg('value'), ).values('the_sum')[:1]))
task_qset = task_qset.annotate(
title_short=Substr('title', 1, settings.TASK_TITLE_LEN_SHORT))
task_qset = task_qset.order_by(ordered)
if count:
return list(task_qset[:count])
else:
return list(task_qset)
def get_base_value_query():
return Value.objects.annotate(field_and_string_value=Concat(
'contact_field_id',
Val('|'),
Upper(Substr('string_value', 1, STRING_VALUE_COMPARISON_LIMIT)),
output_field=CharField())).values('contact_id')
def get_query(self, q, request):
queryset = self.model.objects.annotate(search_name=Concat(
'comune', V(' '), Substr('foglio', 5, 3), V(' '), 'part'))
return queryset.filter(search_name__icontains=q)[:5]
def export(src_lang, tgt_lang, directory_path, ignore_file=None):
main_template = 'export/latex.html'
chapter_template = 'export/latex-chapter.html'
# get all approved relations
# 1) get approved translation relations (e.g. fin->sms, for our case)
# 2) group them by their first character
# 3) order them
to_ignore_ids = read_first_ids_from(ignore_file)
relations = Relation.objects.filter(checked=True, type=TRANSLATION) \
.exclude(pk__in=to_ignore_ids) \
.prefetch_related(
Prefetch('lexeme_from', queryset=Lexeme.objects.prefetch_related('miniparadigm_set')),
Prefetch('lexeme_to', queryset=Lexeme.objects.prefetch_related('miniparadigm_set')),
'relationexample_set', 'relationmetadata_set') \
.filter(lexeme_from__language=src_lang, lexeme_to__language=tgt_lang) \
.annotate(lexeme_fc=Upper(Substr(Cast('lexeme_from__lexeme', models.CharField()), 1, 1)),
lexeme_fcl=Substr(Cast('lexeme_from__lexeme_lang', models.CharField()), 1, 1)) \
.order_by('lexeme_fcl') \
.all()
grouped_relations = groupby(sorted(relations, key=lambda r: r.lexeme_fcl),
key=lambda r: r.lexeme_fcl)
in_memory = BytesIO()
zip_file = ZipFile(in_memory, "a")
keys = []
for key, relations in grouped_relations:
# group relations based on the lexeme_from (source)
grouped_relations_source = groupby(sorted(
relations, key=lambda r: r.lexeme_from.id),
key=lambda r: r.lexeme_from.id)
grouped_relations_source = [(
k,
list(g),
) for k, g in grouped_relations_source]
grouped_relations_source = list(
sorted(grouped_relations_source,
key=lambda k: k[1][0].lexeme_from.lexeme_lang))
_chapter_html = render_to_string(
chapter_template, {'grouped_relations': grouped_relations_source})
alphabet = grouped_relations_source[0][1][0].lexeme_fc
keys.append(alphabet)
zip_file.writestr("chapter-{}.tex".format(alphabet),
_chapter_html.encode('utf-8'))
_main_html = render_to_string(main_template, {'relation_keys': keys})
zip_file.writestr("dictionary.tex", _main_html.encode('utf-8'))
for _file in zip_file.filelist:
_file.create_system = 0
zip_file.close()
_filename = "{}-{}-{}{}-LaTeX-export.zip".format(
time.strftime("%Y%m%d-%H%M%S"), src_lang, tgt_lang,
str(uuid.uuid4())[:5])
in_memory.seek(0)
with open("{}/{}".format(directory_path, _filename), 'wb') as f:
f.write(in_memory.getvalue())
def get_queryset(self):
source_id = self.request.GET.get('source', None)
document_type = self.request.GET.get('document_type', None)
user_data = additional_user_info(self.request)
user_role = user_data['service_role'].get('LILDBI')
# getting action parameter
self.actions = {}
for key in ACTIONS.keys():
self.actions[key] = self.request.GET.get(key, ACTIONS[key])
search_field = self.search_field + '__icontains'
# search by field
search = self.actions['s']
if ':' in search:
search_parts = search.split(':')
lookup_expr = '__exact' if search_parts[
0] == "LILACS_original_id" else '__icontains'
search_field, search = "%s%s" % (search_parts[0],
lookup_expr), search_parts[1]
if search:
object_list = self.model.objects.filter(**{search_field: search})
else:
object_list = self.model.objects.all()
if source_id:
object_list = object_list.filter(source_id=source_id)
if self.actions['filter_status'] != '':
object_list = object_list.filter(
status=self.actions['filter_status'])
if self.actions['filter_indexed_database'] != '':
object_list = object_list.filter(
indexed_database=self.actions['filter_indexed_database'])
# filter by specific document type and remove filter by user (filter_owner)
if document_type:
literature_type = re.sub(
'[^A-Z]|[CP]', '', document_type
) # get only uppercase chars excepct CP (congress/project)
treatment_level = re.sub('[A-Z]', '',
document_type) # get only lowercase chars
object_list = object_list.filter(
literature_type__startswith=literature_type,
treatment_level=treatment_level)
if document_type == JOURNALS_FASCICLE:
object_list = object_list.annotate(
publication_year=Substr("publication_date_normalized", 1, 4))
if self.model.__name__ == "Reference":
volume_serial_field = "referencesource__volume_serial"
issue_number_field = "referencesource__issue_number"
else:
volume_serial_field = "volume_serial"
issue_number_field = "issue_number"
object_list = object_list.order_by(
"-publication_year", "-{}".format(volume_serial_field),
"-{}".format(issue_number_field))
elif self.actions['order'] == "-":
object_list = object_list.order_by(
"%s%s" % (self.actions["order"], self.actions["orderby"]))
# if not at main reference list and source or document_type remove filter by user
if self.model.__name__ != 'Reference' and (source_id or document_type):
self.actions['filter_owner'] = '*'
# profile lilacs express editor - restrict by CC code when list sources
if document_type and user_role == 'editor_llxp':
self.actions['filter_owner'] = 'center'
# filter by user
if not self.actions['filter_owner'] or self.actions[
'filter_owner'] == 'user':
object_list = object_list.filter(created_by=self.request.user)
# filter by cooperative center
elif self.actions['filter_owner'] == 'center':
user_cc = self.request.user.profile.get_attribute('cc')
object_list = object_list.filter(cooperative_center_code=user_cc)
# filter by titles of responsibility of current user CC
elif self.actions['filter_owner'] == 'indexed':
user_cc = self.request.user.profile.get_attribute('cc')
titles_indexed = [
t.shortened_title for t in Title.objects.filter(
indexrange__indexer_cc_code=user_cc)
]
if titles_indexed:
filter_title_qs = Q()
for title in titles_indexed:
filter_title_qs = filter_title_qs | Q(
referenceanalytic__source__title_serial=title) | Q(
referencesource__title_serial=title)
object_list = object_list.filter(filter_title_qs)
# by default filter by LILACS express status
if self.actions['filter_status'] == '':
object_list = object_list.filter(status=0)
# by default filter by articles (exclude sources of list)
if self.actions['document_type'] == '':
object_list = object_list.filter(treatment_level='as')
else:
# if no indexed journals are found return a empty list
object_list = self.model.objects.none()
# filter by records changed by others
elif self.actions['filter_owner'] == 'review':
if self.actions['review_type'] == 'user':
ref_list = refs_changed_by_other_user(self.request.user)
else:
ref_list = refs_changed_by_other_cc(self.request.user)
if ref_list:
# get only ID's from filter reference list
reference_id_list = ref_list.keys
object_list = object_list.filter(id__in=reference_id_list)
else:
object_list = object_list.none()
# exclude from the standard result list (filter status=all) sources with deleted status (#914)
if self.actions['filter_status'] == '':
object_list = object_list.exclude(status='3',
literature_type='S',
treatment_level='')
return object_list
def dehydrate(self, bundle):
# default thesaurus 1 (decs)
ths = bundle.request.GET.get('ths', 1)
# default language pt
lang = bundle.request.GET.get('lang', 'pt')
if lang == 'pt':
# language_code in DB is pt-br not pt
lang_code = 'pt-br'
else:
lang_code = lang
by_tree_id = bundle.request.GET.get('tree_id', None)
if by_tree_id:
if bundle.obj.tree_number == "A":
# first level categories
decsws_response = {
'attr': {
'service': "",
'tree_id': "",
},
'tree': {
'term_list': first_level_list(lang_code),
'attr': {
'lang': lang
}
}
}
else:
if bundle.obj.tree_number[0:1] not in ["Q", "Y"]:
is_descriptor = True
# descriptor models
TermList = TermListDesc
TreeNumbersList = TreeNumbersListDesc
ConceptList = ConceptListDesc
Description = DescriptionDesc
else:
is_descriptor = False
# qualifier models
TermList = TermListQualif
TreeNumbersList = TreeNumbersListQualif
ConceptList = ConceptListQualif
Description = DescriptionQualif
decsws_response = {
'attr': {
'service': "",
'tree_id': bundle.obj.tree_number,
}
}
identifier_id = bundle.obj.identifier.id
record_list = {}
record = {
"attr": {
"lang": lang,
"db": 'decs',
"mfn": bundle.obj.identifier.decs_code,
}
}
if is_descriptor:
record[
"unique_identifier_nlm"] = bundle.obj.identifier.descriptor_ui
else:
record[
"unique_identifier_nlm"] = bundle.obj.identifier.qualifier_ui
term_attr_list = []
term_string = None
term_list = list(
TermList.objects.filter(
identifier_concept__identifier_id=identifier_id,
identifier_concept__preferred_concept='Y',
concept_preferred_term='Y',
status=1).values('term_string', 'language_code'))
for term in term_list:
if term['language_code'] == 'pt-br' and lang == 'pt':
show_lang = 'pt'
else:
show_lang = term['language_code']
if not is_descriptor:
term['term_string'] = "/" + term['term_string']
term_attr = {
'attr': {
'lang': show_lang
},
'descriptor': term['term_string'],
}
term_attr_list.append(term_attr)
if term['language_code'] == lang_code:
# to use in <tree><self><term_list><term>
term_string = term['term_string']
if term['language_code'] == 'en':
term_string_en = term['term_string']
record['descriptor_list'] = term_attr_list
synonym_list = list(
TermList.objects.filter(
identifier_concept__identifier_id=identifier_id,
identifier_concept__preferred_concept='N',
concept_preferred_term='N',
language_code=lang_code,
status=1).values('term_string'))
for synonym in synonym_list:
if not is_descriptor:
synonym['term_string'] = "/" + synonym['term_string']
synonym['synonym'] = synonym.pop('term_string')
record['synonym_list'] = synonym_list
tree_ids = []
tree_id_list = list(
TreeNumbersList.objects.filter(
identifier_id=identifier_id).values('tree_number'))
for tree_id in tree_id_list:
if is_descriptor or (not is_descriptor
and tree_id['tree_number'][0:1]
== bundle.obj.tree_number[0:1]):
tree_ids.append({'tree_id': tree_id['tree_number']})
record["tree_id_list"] = tree_ids
# definition = ConceptListDesc.scope_note
definition = ConceptList.objects.filter(
identifier_concept__identifier_id=identifier_id,
identifier_concept__preferred_concept='Y',
language_code=lang_code).values('scope_note').first()
if definition:
record['definition'] = {
"occ": {
"attr": {
"n": definition['scope_note']
}
}
}
annotation = Description.objects.filter(
identifier_id=identifier_id,
language_code=lang_code).values().first()
if annotation and annotation['annotation']:
record['indexing_annotation'] = annotation['annotation']
if is_descriptor:
if annotation and annotation['consider_also']:
record['consider_also_terms_at'] = annotation[
'consider_also']
pharmacological_action = list(
PharmacologicalActionList.objects.filter(
identifier_id=identifier_id,
descriptor_ui__isnull=False,
language_code='en').order_by(
'descriptor_ui').values_list('descriptor_ui',
flat=True))
pharmacological_action_list = []
if pharmacological_action:
action_terms = TermList.objects.filter(
identifier_concept__identifier__descriptor_ui__in=
pharmacological_action,
record_preferred_term='Y',
language_code=lang_code,
status=1).order_by(
'identifier_concept__identifier')
for action_term in action_terms:
pharmacological_action_list.append({
'pharmacological_action':
action_term.term_string,
'attr': {
'lang': lang
}
})
record[
'pharmacological_action_list'] = pharmacological_action_list
entry_combination_list = []
combination_list = list(
EntryCombinationListDesc.objects.filter(
identifier_id=identifier_id).values(
'ecin_id', 'ecout_desc_id', 'ecout_qualif_id'))
for combination in combination_list:
ec_attr = {'lang': lang}
qualifier_abbr = IdentifierQualif.objects.get(
qualifier_ui=combination['ecin_id'])
ec_attr['sh_abbr1'] = qualifier_abbr.abbreviation
if combination['ecout_qualif_id']:
if combination['ecout_qualif_id'] != combination[
'ecin_id']:
qualifier_abbr1 = IdentifierQualif.objects.get(
qualifier_ui=combination['ecout_qualif_id']
)
ec_attr[
'sh_abbr2'] = qualifier_abbr1.abbreviation
else:
ec_attr[
'sh_abbr2'] = qualifier_abbr.abbreviation
combination_term = TermList.objects.filter(
identifier_concept__identifier__descriptor_ui=
combination['ecout_desc_id'],
identifier_concept__preferred_concept='Y',
concept_preferred_term='Y',
record_preferred_term='Y',
language_code=lang_code,
status=1)
if combination_term:
term_text = combination_term[0].term_string
else:
term_text = ""
entry_combination_list.append({
'entry_combination': term_text,
'attr': ec_attr
})
record['entry_combination_list'] = entry_combination_list
see_related_list = []
related_list = list(
SeeRelatedListDesc.objects.filter(
identifier_id=identifier_id).values(
'descriptor_ui'))
for related in related_list:
related_term = TermList.objects.filter(
identifier_concept__identifier__descriptor_ui=
related['descriptor_ui'],
identifier_concept__preferred_concept='Y',
concept_preferred_term='Y',
record_preferred_term='Y',
language_code=lang_code,
status=1)
if related_term:
term_text = related_term[0].term_string.encode(
'utf-8')
else:
# does not exists translation
term_text = ""
see_related_list.append({
'see_related': term_text,
'attr': {
'lang': lang
}
})
record['see_related_list'] = see_related_list
allowable_qualifier_list = list(
bundle.obj.identifier.abbreviation.values(
'abbreviation',
'decs_code').order_by('abbreviation'))
for abbr in allowable_qualifier_list:
abbr['allowable_qualifier'] = abbr.pop('abbreviation')
abbr['attr'] = {'id': str(abbr['decs_code'])}
abbr.pop('decs_code')
record[
'allowable_qualifier_list'] = allowable_qualifier_list
else:
record['pharmacological_action_list'] = []
record['entry_combination_list'] = []
record['see_related_list'] = []
record['allowable_qualifier_list'] = []
record_list['record'] = record
decsws_response['record_list'] = record_list
tree = {}
tree_id_ancestor_list = []
ancestor_term_list = []
ancestors_tree_id = []
for tree_id in tree_ids:
parts = tree_id['tree_id'].split(".")
# delete last
parts.pop()
# Ancestor only from first level categories (ex: tree_id:A01 ancestor A)
if not parts:
category_id = get_category_id(tree_id['tree_id'])
tree_id_ancestor_list.append({
'tree_id': tree_id,
'ancestor': category_id
})
ancestor = ''
for p in parts:
if ancestor == '':
ancestor = p
else:
ancestor = ancestor + '.' + p
ancestors_tree_id.append(ancestor)
tree_id_ancestor_list.append({
'tree_id': tree_id,
'ancestor': ancestor
})
# Hay q hacerlo en 2 pasos pq no se puede relacionar mas de 3 tablas
ancestors_list = list(
TreeNumbersList.objects.filter(
tree_number__in=ancestors_tree_id,
identifier__thesaurus=ths,
).order_by('tree_number').values('identifier_id',
'tree_number'))
part1 = ""
for tree_id_ancestor in tree_id_ancestor_list:
if len(tree_id_ancestor['ancestor']) < 3:
category = get_category(tree_id_ancestor['ancestor'],
is_descriptor, lang)
ancestor_term_list.append({
'term': category,
'attr': {
'tree_id': tree_id_ancestor['ancestor']
}
})
else:
for ancestor_ids in ancestors_list:
if ancestor_ids['tree_number'] == tree_id_ancestor[
'ancestor']:
# add first level categories
parts = ancestor_ids['tree_number'].split(".")
if parts[0] != part1:
part1 = parts[0]
category_id = get_category_id(part1)
category = get_category(
category_id, is_descriptor, lang)
ancestor_term_list.append({
'term': category,
'attr': {
'tree_id': category_id
}
})
ancestor_term = TermList.objects.filter(
identifier_concept__identifier=ancestor_ids[
'identifier_id'],
identifier_concept__preferred_concept='Y',
concept_preferred_term='Y',
record_preferred_term='Y',
language_code=lang_code,
status=1)
if ancestor_term:
term_text = ancestor_term[0].term_string
if not is_descriptor:
term_text = "/" + term_text
else:
# does notexist translation
term_text = ""
term = {
'term': term_text,
'attr': {
'tree_id': ancestor_ids['tree_number']
}
}
ancestor_term_list.append(term)
break
tree['ancestors'] = {
'term_list': ancestor_term_list,
'attr': {
'lang': lang
}
}
preceding_sibling = []
following_sibling = []
tam = len(bundle.obj.tree_number)
if tam > 4:
ancestor_tree_id = bundle.obj.tree_number[0:tam - 4]
else:
ancestor_tree_id = get_category_id(bundle.obj.tree_number)
# En 2 pasos pq no se puede relacionar mas de 3 tablas
sibling_list = list(
TreeNumbersList.objects.annotate(
tree_number_tam=Length('tree_number')).filter(
tree_number__startswith=ancestor_tree_id,
tree_number_tam=tam,
identifier__thesaurus=ths,
).exclude(tree_number__exact=bundle.obj.tree_number).
order_by('tree_number').values('identifier_id',
'tree_number'))
with_descendant = list(
TreeNumbersList.objects.annotate(
descendant=Substr('tree_number', 1, tam),
tree_number_tam=Length('tree_number')).filter(
tree_number__startswith=ancestor_tree_id,
tree_number_tam__gt=tam,
identifier__thesaurus=ths,
).order_by('tree_number').values('descendant'))
for sibling_ids in sibling_list:
sibling_term = TermList.objects.filter(
identifier_concept__identifier=sibling_ids[
'identifier_id'],
identifier_concept__preferred_concept='Y',
concept_preferred_term='Y',
record_preferred_term='Y',
language_code=lang_code,
status=1)
if sibling_term:
term_text = sibling_term[0].term_string
if not is_descriptor:
term_text = "/" + term_text
else:
# does notexist translation
term_text = ""
term = {
'term': term_text,
'attr': {
'tree_id': sibling_ids['tree_number']
}
}
if {
'descendant': sibling_ids['tree_number']
} not in with_descendant:
term['attr']['leaf'] = "true"
if sibling_ids['tree_number'] < bundle.obj.tree_number:
preceding_sibling.append(term)
else:
following_sibling.append(term)
tree['preceding_sibling'] = {
'term_list': preceding_sibling,
'attr': {
'lang': lang
}
}
tree['following_sibling'] = {
'term_list': following_sibling,
'attr': {
'lang': lang
}
}
if not term_string:
term_string = term_string_en
if {
'descendant': bundle.obj.tree_number
} not in with_descendant:
attr_self = {
'tree_id': bundle.obj.tree_number,
'leaf': 'true'
}
leaf = 1
else:
attr_self = {'tree_id': bundle.obj.tree_number}
leaf = 0
self_descriptor = {
'attr': {
'lang': lang
},
'term_list': [
{
'term': term_string,
'attr': attr_self
},
],
}
tree['self'] = self_descriptor
descendant_term_list = []
tree_number_starts = bundle.obj.tree_number + '.'
tam1 = tam + 4
if not leaf:
descendant_list = list(
TreeNumbersList.objects.annotate(
tree_number_tam=Length('tree_number')).filter(
tree_number__startswith=tree_number_starts,
tree_number_tam=tam1,
identifier__thesaurus=ths,
).order_by('tree_number').values(
'identifier_id', 'tree_number'))
with_descendant = list(
TreeNumbersList.objects.annotate(
descendant=Substr('tree_number', 1, tam1),
tree_number_tam=Length('tree_number')).filter(
tree_number__startswith=tree_number_starts,
tree_number_tam__gt=tam1,
identifier__thesaurus=ths,
).order_by('tree_number').values('descendant'))
for descendant_ids in descendant_list:
descendant_term = TermList.objects.filter(
identifier_concept__identifier=descendant_ids[
'identifier_id'],
identifier_concept__preferred_concept='Y',
concept_preferred_term='Y',
record_preferred_term='Y',
language_code=lang_code,
status=1)
if descendant_term:
term_text = descendant_term[0].term_string
if not is_descriptor:
term_text = "/" + term_text
else:
# does notexist translation
term_text = ""
term = {
'term': term_text,
'attr': {
'tree_id': descendant_ids['tree_number']
}
}
if {
'descendant': descendant_ids['tree_number']
} not in with_descendant:
term['attr']['leaf'] = "true"
descendant_term_list.append(term)
tree['descendants'] = {
'term_list': descendant_term_list,
'attr': {
'lang': lang
}
}
decsws_response['tree'] = tree
bundle.data['decsws_response'] = decsws_response
else:
bundle.data['by_words'] = 'yes'
# bundle.data.pop('identifier_descriptor')
# bundle.data.pop('identifier_qualifier')
# bundle.data.pop('tree_number')
bundle.data.pop('id')
return bundle
def metadata_metrics(request):
t_samplings = models.BiosourceSamplingProcess.objects.filter(
collection_location_country__isnull=False).count()
with_collection_date = models.BiosourceSamplingProcess.objects.filter(
collection_location_country__isnull=False,
collection_date__isnull=False).count()
with_adm2 = models.BiosourceSamplingProcess.objects.filter(
collection_location_country__isnull=False,
collection_location_adm2__isnull=False).exclude(
collection_location_adm2="").count()
with_adm2_private = models.BiosourceSamplingProcess.objects.filter(
collection_location_country__isnull=False,
private_collection_location_adm2__isnull=False).exclude(
private_collection_location_adm2="").count()
with_age = models.BiosourceSamplingProcess.objects.filter(
collection_location_country__isnull=False,
source_age__isnull=False).count()
with_sex = models.BiosourceSamplingProcess.objects.filter(
collection_location_country__isnull=False,
source_sex__isnull=False).exclude(source_sex="").count()
t_noph = models.BiosampleArtifact.objects.exclude(
Q(created__who__profile__institute__code="PHEC")
| Q(created__who__profile__institute__code="PHWC"))
with_sender = t_noph.filter(sender_sample_id__isnull=False).exclude(
sender_sample_id="").exclude(
sender_sample_id__exact=F('dice_name')).exclude(
sender_sample_id__startswith=Substr(F('dice_name'), 1, 3))
supps = models.COGUK_BiosourceSamplingProcessSupplement.objects.filter(
is_surveillance__isnull=False)
with_hcw = supps.filter(is_hcw__isnull=False)
supps_cc = supps.filter(
Q(is_care_home_worker=True) | Q(is_care_home_resident=True))
with_carecode = supps_cc.filter(
anonymised_care_home_code__isnull=False).exclude(
anonymised_care_home_code="")
return render(
request, 'public/special/metrics.html', {
"no_ph_senders": (with_sender.count(), "%.2f" %
(with_sender.count() / t_noph.count() * 100.0)),
"collections_with_collection_date":
(with_collection_date, "%.2f" %
(with_collection_date / t_samplings * 100.0)),
"collections_with_adm2":
(with_adm2, "%.2f" % (with_adm2 / t_samplings * 100.0)),
"collections_with_adm2_private":
(with_adm2_private, "%.2f" %
(with_adm2_private / t_samplings * 100.0)),
"collections_with_age":
(with_age, "%.2f" % (with_age / t_samplings * 100.0)),
"collections_with_sex":
(with_sex, "%.2f" % (with_sex / t_samplings * 100.0)),
"supps_with_hcw": (with_hcw.count(), "%.2f" %
(with_hcw.count() / supps.count() * 100.0)),
"supps_with_carecode":
(with_carecode.count(), "%.2f" %
(with_carecode.count() / supps_cc.count() * 100.0)),
})
def annotate_with_first_letter(qs):
"""A function to annotate the queryset with the first letter of the concept's name. (Currently unused)"""
return qs.annotate(first_letter=Upper(Substr('name', 1, 1)))
def filter(self, qs, value):
if value in EMPTY_VALUES:
return qs
if self.distinct:
qs = qs.distinct()
(start, start_suffix, start_suffix_pos) = value[0]
(stop, stop_suffix, stop_suffix_pos) = value[1]
suffix_pos = start_suffix_pos if start else stop_suffix_pos
prefix_regex = r'^([\D|0]*)[0-9]*$'
start_prefix = re.match(prefix_regex, start).group(1)
stop_prefix = re.match(prefix_regex, stop).group(1)
if start and stop:
common_prefix = os.path.commonprefix([start_prefix, stop_prefix])
suffix_filter = {
'suffix_number__range': (start_suffix, stop_suffix),
}
elif start:
common_prefix = start_prefix
suffix_filter = {
'suffix_number__gte': start_suffix,
}
else:
common_prefix = stop_prefix
suffix_filter = {
'suffix_number__lte': stop_suffix,
}
if connection.vendor == 'microsoft':
from sql_server.pyodbc.functions import TryCast
cast_func = TryCast
base = qs.filter(
**{
'{}__startswith'.format(self.field_name): common_prefix
}, ).annotate(suffix_string=Substr(
F(self.field_name), suffix_pos + 1,
len(start_suffix)), ).exclude(
suffix_string__contains='%[^0-9]%', )
else:
cast_func = Cast
base = qs.filter(
**{
'{}__regex'.format(self.field_name):
r'{}[0-9]+$'.format(common_prefix)
}, )
return base.annotate(
full_length=Length(self.field_name),
suffix_number=cast_func(
Substr(
F(self.field_name), suffix_pos + 1,
len(start_suffix) if start_suffix else len(stop_suffix)),
IntegerField(),
)).filter(
full_length=len(start) if start else len(stop),
**suffix_filter,
)
IN (SELECT U0."id" FROM "auth_user" U0 WHERE U0."id" <= 30)
"""
# EXPLAIN QUERY PLAN
"""
Execution time: 0.000090s [Database: default]
'3 0 0 SEARCH TABLE orm_practice_app_userinfo USING INDEX orm_practice_app_userinfo_owned_user_id_e85907f1 (owned_user_id=?)
7 0 0 LIST SUBQUERY 1
9 7 0 SEARCH TABLE auth_user AS U0 USING INTEGER PRIMARY KEY (rowid<?)'
"""
User.objects.annotate(first=Substr("first_name", 1, 1),
last=Substr("last_name", 1, 1)).filter(first=F("last"))
"""
SELECT "auth_user"."id",
"auth_user"."password",
"auth_user"."last_login",
"auth_user"."is_superuser",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."date_joined",
SUBSTR("auth_user"."first_name", 1, 1) AS "first",
SUBSTR("auth_user"."last_name", 1, 1) AS "last"
def get_redirect_path_with_status(self, path, full_path=None, language=None, version_slug=None):
# add extra fields with the ``path`` and ``full_path`` to perform a
# filter at db level instead with Python
queryset = self.annotate(
path=Value(
path,
output_field=CharField(),
),
full_path=Value(
full_path,
output_field=CharField(),
),
from_url_length=ExpressionWrapper(
Length('from_url'),
output_field=IntegerField(),
),
# 1-indexed
from_url_without_rest=Substr(
'from_url',
1,
F('from_url_length') - 5, # Strip "$rest"
output_field=CharField(),
),
# 1-indexed
full_path_without_rest=Substr(
'full_path',
1,
F('from_url_length') - 5, # Strip "$rest"
output_field=CharField(),
),
)
prefix = Q(
redirect_type='prefix',
path__startswith=F('from_url'),
)
page = Q(
redirect_type='page',
path__iexact=F('from_url'),
)
exact = (
Q(
redirect_type='exact',
from_url__endswith='$rest',
# This works around a bug in Django doing a substr and an endswith,
# so instead we do 2 substrs and an exact
# https://code.djangoproject.com/ticket/29155
full_path_without_rest=F('from_url_without_rest'),
) | Q(
redirect_type='exact',
full_path__iexact=F('from_url'),
)
)
sphinx_html = (
Q(
redirect_type='sphinx_html',
path__endswith='/',
) | Q(
redirect_type='sphinx_html',
path__endswith='/index.html',
)
)
sphinx_htmldir = Q(
redirect_type='sphinx_htmldir',
path__endswith='.html',
)
# There should be one and only one redirect returned by this query. I
# can't think in a case where there can be more at this point. I'm
# leaving the loop just in case for now
queryset = queryset.filter(prefix | page | exact | sphinx_html | sphinx_htmldir)
for redirect in queryset.select_related('project'):
new_path = redirect.get_redirect_path(
path=path,
language=language,
version_slug=version_slug,
)
if new_path:
return new_path, redirect.http_status
return (None, None)
def test_pos_gt_zero(self):
with self.assertRaisesMessage(ValueError,
"'pos' must be greater than 0"):
Author.objects.annotate(raises=Substr('name', 0))
def get_ancestors_parent_annotated(self, include_self=False):
"""
Creates a queryset containing all parents of the queryset.
Also annotates the parent pk as `_parent_pk`.
"""
# django mptt got a ready to go method
if self.treetype == MPTT:
parent_field = self.qs.model._mptt_meta.parent_attr
return TreeQuerySet(
self.qs.get_ancestors(include_self=include_self).annotate(
_parent_pk=F(parent_field + '__pk')))
# for treebeard we have to get the parents ourself
elif self.treetype == TREEBEARD:
if issubclass(self.qs.model, NS_Node):
filters = Q()
for node in self.qs:
if include_self:
filters |= Q(tree_id=node.tree_id,
lft__lte=node.lft,
rgt__gte=node.rgt)
else:
filters |= Q(tree_id=node.tree_id,
lft__lt=node.lft,
rgt__gt=node.rgt)
sub = self.qs.model.objects.filter(
tree_id=OuterRef('tree_id'),
lft__lt=OuterRef('lft'),
rgt__gt=OuterRef('rgt')).reverse()[:1]
qs = self.qs.model.objects.filter(filters)\
.annotate(_parent_pk=Subquery(sub.values('pk')))
return TreeQuerySet(qs)
elif issubclass(self.qs.model, MP_Node):
paths = set()
for node in self.qs:
length = len(node.path)
if include_self:
length += node.steplen
paths.update(
node.path[0:pos]
for pos in range(node.steplen, length, node.steplen))
sub = self.qs.model.objects.filter(path=OuterRef('parentpath'))
expr = Substr('path',
1,
Length('path') - self.qs.model.steplen,
output_field=CharField())
qs = self.qs.model.objects.filter(path__in=paths)\
.annotate(parentpath=expr)\
.annotate(_parent_pk=Subquery(sub.values('pk')))
return TreeQuerySet(qs)
elif issubclass(self.qs.model, AL_Node):
# worst for parent querying
# we have to walk all levels up to root
# adds roughly a one query per level
nodes = self.qs.select_related('parent')
pks = set()
parents = set()
for node in nodes:
if include_self:
pks.add(node.pk)
if node.parent:
parents.add(node.parent.pk)
missing = parents - pks
while missing:
pks.update(parents)
parents.clear()
for node in self.qs.model.objects.filter(
pk__in=missing).select_related('parent'):
if node.parent:
parents.add(node.parent.pk)
missing = parents - pks
return TreeQuerySet(
self.qs.model.objects.filter(pk__in=pks).annotate(
_parent_pk=F('parent__pk')))
raise UnknownTreeImplementation('dont know how to annotate _parent_pk')
def filter_query(request):
qs = Student.objects.all()
''' for printing SQL query of ORM model'''
print("query for all students is \n", qs.query)
# print(str(qs.query))
print("======for getting the dictionary from queryset(Values) =========")
qs11 = Student.objects.all().values(
'name', 'age'
) # it will show key value pair of field and values in list of dictionaries
print(qs11)
qs12 = Student.objects.filter(name__startswith='n').values('name', 'age')
print(qs12)
print("======Values list =========")
qs1 = Student.objects.values_list(
'name', 'age') # it will show only values of field
print(qs1)
print("======Value list of single attribute without flat =========")
qset = Student.objects.values_list(
'name') # it will show list of tuples with ,
print(qset)
print("======Value list of single attribute with flat=true =========")
qset1 = Student.objects.values_list(
'name', flat=True
) # it will show list of content that can be used anywhere(valid for single attribute only)
print(qset1)
qs2 = Student.objects.get(
id=2) # get single record/object from query set (row)
# qs2 = Student.objects.get(name='Pinkesh')
print("name of the student with id 2 is :",
qs2.name) # get single field from qs (column value)
''' OR operation , 2 ways'''
print("============ OR operation ====================")
qs3 = Student.objects.filter(
name__startswith='P') | Student.objects.filter(name__startswith='N')
print(qs3)
print("students whose name starts with P or N are :")
for obj in qs3: # using for loop becoz getting multiple records
print(obj.name)
qs4 = Student.objects.filter(
Q(name__startswith='P')
| ~Q(name__istartswith='J')) # start with p but not starts with j
# startswith is case sensitive and istartswith is case insensitive
print(qs4)
''' == We can also use range, date, year, iso_year, months, day, week, weekday, iso_weekday, isnull, regex etc'''
''' AND operation , 3 ways'''
print("============ AND operation ====================")
qs5 = Student.objects.filter(
name__startswith='P',
name__iendswith='h') # iendswith is case insesnsitive
print(qs5)
qs6 = Student.objects.filter(
name__startswith='n') & Student.objects.filter(name__endswith='a')
print(qs6)
qs7 = Student.objects.filter(
Q(name__startswith='P') & Q(name__endswith='a'))
print(qs7)
''' NOT operation , 2 ways'''
print("============ NOT operation ====================")
qs8 = Student.objects.exclude(age__lt=30) # less than
print(qs8)
qs9 = Student.objects.filter(~Q(age__gte=24)) # greater than equal to
print(qs9)
''' UNION operation '''
print("============ UNION operation on querysets ====================")
print(qs8.union(qs9))
print(
"============ UNION operation on Models with same field ===================="
)
qs10 = Student.objects.values_list('name').union(
Department.objects.values_list('name'))
print(
qs10
) # only union two models if they have same fields, and using values_list you can only filter fields
print("============ SUB-Query ====================")
# display names of student whose age is greater than age of pinkesh
qs13 = Student.objects.filter(name='Pinkesh').values(
'age') # will fetch age of st where name = pinkesh
print(qs13)
qs14 = Student.objects.filter(
age__gt=Subquery(Student.objects.filter(name='Pinkesh').values('age')))
print(qs14)
''' Use F object to compare two fields '''
print(
"============ Filter by comparing fields using F object ===================="
)
# filter the fields where age = dept_id
qs17 = Student.objects.filter(age=F("dept_id"))
print(qs17)
''' used with foreign key name in student(dep_id__name) is equal to name in dept F("name")'''
qss = Department.objects.filter(dep_id__name=F("name"))
print("===", qss)
print(
"============ Filter by comparing fields using annotate, F object and Substr ===================="
)
# filter value where first 2 char of first and last name are similar
qs18 = Details.objects.annotate(fname=Substr("f_name", 1, 2),
lname=Substr("l_name", 1,
2)).filter(fname=F("lname"))
# annotate is use to give alt name
# Substr is used to take sub string starting from 1 and till 2 char like Substr("pinkesh",1,3) is pin
print(qs18)
print("============ Select Student with maximum age ====================")
qs19 = Student.objects.order_by('age')[2] # second lowest age
print(qs19.age)
qs20 = Student.objects.order_by('age')[Student.objects.all().count() -
2] # second highest age
print(qs20.age)
print("============ Find student with duplicate name ====================")
qs21 = Student.objects.values('name').annotate(count=Count('name'))
print(qs21)
qs22 = Student.objects.values('name').annotate(count=Count('name')).filter(
count__gt=1)
print(qs22, "\n", qs22[0]['name'])
print("============ Find student with distinct name ====================")
qs23 = Student.objects.values('name').annotate(count=Count('name')).filter(
count=1)
print(qs23, "\n", qs23[0]['name'])
print(i['name'] for i in qs23)
'''similar to this below'''
# for i in qs23:
# print(i['name'])
print("============ Select random object efficiently ====================")
qs24 = Student.objects.all().aggregate(
max_id=Max('id')) # return {'max_id': 11}
print(qs24)
max = Student.objects.all().aggregate(
max_id=Max('id'))['max_id'] # return 11
# import random
# ran = random.randint(1,max)
# qs25 = Student.objects.get(id=ran)
# print(qs25)
return HttpResponse("<h1>Check console</h1> ")
def set_init_sname(apps, schema_editor):
Artist = apps.get_model('records', 'Artist')
Artist.objects.all().update(sname=Lower(Substr('name', 1, 20)))
def get_order_list(prefix='',
filter_user=None,
user=None,
add_author_data=False,
add_task_data=False,
add_skill_data=False,
add_order_in_check_data=False,
task=None,
status=None,
add_fl_count=False,
ordered='-created',
count=None):
"""
Return a list of order.
The arguments:
``filter_user`` - set filter on Order.user. A string. Can be:
- '==': select all orders with order.user == user
- '!=': select all orders with order.user != user
- None: select all orders without filter
``user`` - user of order (model - User)
``add_skill_data`` - add select_related('task__skill')
``add_fl_count`` - add fields:
favourite_count=Count('order_favourites')
like_count=Count('order_like')
"""
from django.db.models import Q, Count, Case, When, Sum, Max, IntegerField, CharField, DateTimeField, Subquery, OuterRef
from django.db.models.functions import Substr
order_qset = Order.objects
if add_skill_data:
order_qset = order_qset.select_related('task__skill')
if add_task_data:
order_qset = order_qset.select_related('task')
if add_author_data:
order_qset = order_qset.select_related('user__profile')
if not filter_user and not task and not status:
order_qset = order_qset.all()
else:
if user and user.is_authenticated:
# set filter on user
if filter_user == '==':
order_qset = order_qset.filter(Q(user=user))
elif filter_user == '!=':
order_qset = order_qset.filter(~Q(user=user))
# set filter on task
if task:
order_qset = order_qset.filter(Q(task=task))
# set filter on status
if status:
order_qset = order_qset.filter(Q(status=status))
if add_fl_count:
# get favourite_count via Subquery
order_qset = order_qset.annotate(favourite_count=Subquery(
OrderFavourites.objects.filter(order=OuterRef('pk')).values(
'order').annotate(favourite_count=Count('order'), ).values(
'favourite_count')[:1]))
# get like_count via Subquery
order_qset = order_qset.annotate(like_count=Subquery(
OrderLike.objects.filter(order=OuterRef('pk')).values('order').
annotate(like_count=Count('order'), ).values('like_count')[:1]))
if user and user.is_authenticated:
if add_order_in_check_data:
# get order_in_check data via Subquery
order_qset = order_qset.annotate(order_in_check_id=Subquery(
OrderInCheck.objects.
filter(Q(order=OuterRef('pk')) & Q(
user=user)).values('id').annotate(order_in_check_id=Max(
'id'), ).values('order_in_check_id')[:1]))
order_qset = order_qset.annotate(order_in_check_status=Subquery(
OrderInCheck.objects.filter(
Q(order=OuterRef('pk')) & Q(user=user)).values(
'status').annotate(order_in_check_status=Max(
'status'), ).values('order_in_check_status')[:1]))
order_qset = order_qset.annotate(order_in_check_update=Subquery(
OrderInCheck.objects.filter(
Q(order=OuterRef('pk')) & Q(user=user)).values(
'updated').annotate(order_in_check_update=Max(
'updated'), ).values('order_in_check_update')[:1]))
# order_qset = order_qset.annotate(
# order_in_check_id=Max(Case(
# When(order_order_in_check__user=user, then='order_order_in_check__id'),
# default=None,
# output_field=CharField(),
# )),
# order_in_check_status=Max(Case(
# When(order_order_in_check__user=user, then='order_order_in_check__status'),
# default=None,
# output_field=CharField(),
# )),
# order_in_check_update=Max(Case(
# When(order_order_in_check__user=user, then='order_order_in_check__updated'),
# default=None,
# output_field=DateTimeField(),
# )),
# )
order_qset = order_qset.annotate(
task_title_short=Substr('task__title', 1,
settings.TASK_TITLE_LEN_SHORT),
task_description_short=Substr('task__description_short', 1,
settings.TASK_DESCRIPTION_LEN_SHORT))
order_qset = order_qset.order_by(ordered)
if count:
return list(order_qset[:count])
else:
return list(order_qset)
def tickets_as_list(self):
result = [
ticket for ticket in self.tickets.annotate(
order=Substr('title', 4)).all()
]
return result
def retrieve_layers(purpose,
category=None,
bbox=None,
authorized_objects=None):
"""List all required layers.
:param purpose: InaSAFE layer purpose that want to be filtered.
Can be 'hazard', 'exposure', or 'impact'
:type purpose: str
:param category: InaSAFE layer category that want to be filtered.
Vary, depend on purpose. Example: 'flood', 'tsunami'
:type category: str
:param bbox: Layer bbox to filter (x0,y0,x1,y1)
:type bbox: (float, float, float, float)
:param authorized_objects: List of authorized objects (list of dict of id)
:type authorized_objects: list
:returns: filtered layer and a status for filtered.
Status will return True, if it is filtered.
:rtype: list[Layer], bool
"""
if not category:
category = None
if bbox:
bbox = json.loads(bbox)
# normalize bbox
if bbox[2] < bbox[0]:
temp = bbox[0]
bbox[0] = bbox[2]
bbox[2] = temp
if bbox[3] < bbox[1]:
temp = bbox[1]
bbox[1] = bbox[3]
bbox[3] = temp
bbox_poly = Polygon.from_bbox(tuple(bbox))
bbox_poly.set_srid(4326)
# Extract from string EPSG:code of field layer__srid
# We use length=10 for maximum length
# Starting from position 6
# EPSG:4326 will extract 4326 of string type
srid_extract = Substr(F('layer__srid'),
Value(6),
Value(10),
output_field=IntegerField())
# Construct WKT representation of bounding box poly geom
poly_expression = Concat(Value('SRID='),
srid_extract,
Value(';POLYGON(('),
F('layer__bbox_x0'),
Value(' '),
F('layer__bbox_y0'),
Value(','),
F('layer__bbox_x1'),
Value(' '),
F('layer__bbox_y0'),
Value(','),
F('layer__bbox_x1'),
Value(' '),
F('layer__bbox_y1'),
Value(','),
F('layer__bbox_x0'),
Value(' '),
F('layer__bbox_y1'),
Value(','),
F('layer__bbox_x0'),
Value(' '),
F('layer__bbox_y0'),
Value('))'),
output_field=CharField())
# Convert WKT to Geom type
layer_poly = Func(poly_expression,
function='ST_GEOMFROMTEXT',
output_field=GeometryField())
# Convert Geom of previous SRID to 4326
layer_poly_transform = Func(layer_poly,
Value(4326),
function='ST_TRANSFORM',
output_field=GeometryField())
# Only filters layer where SRID is defined (excluding EPSG:None)
metadatas_count_filter = Metadata.objects.filter(
Q(layer_purpose=purpose) & ~Q(layer__srid__iexact='EPSG:None'))
# Create a queryset with extra field called bbox_poly
# From the previous constructed function
query_set = metadatas_count_filter.annotate(
bbox_poly=layer_poly_transform)
# Filter metadata by intersections with a given bbox
metadatas = query_set.filter(bbox_poly__intersects=bbox_poly)
if category:
metadatas = metadatas.filter(category=category)
metadatas_count_filter = metadatas_count_filter.filter(
category=category)
layer_count = metadatas_count_filter.count()
if len(metadatas) == layer_count:
# it means unfiltered by bbox
is_filtered = False
else:
# it means filtered by bbox
is_filtered = True
else:
metadatas = Metadata.objects.filter(layer_purpose=purpose)
if category:
metadatas = metadatas.filter(category=category)
is_filtered = False
# Filter by permissions
if not authorized_objects:
# default to anonymous user permission
user = AnonymousUser()
authorized_objects = get_objects_for_user(user,
_VIEW_PERMS).values('id')
metadatas = metadatas.filter(layer__id__in=authorized_objects)
return Layer.objects.filter(inasafe_metadata__in=metadatas), is_filtered
def sorted(self):
return self.annotate(prefix=Substr('name', 1, 1),
index=Cast(Substr('name', 2),
IntegerField())).order_by(
'prefix', 'index')
def annotate_document(self, documents: QuerySet) -> QuerySet:
return documents.annotate(
GL_date=Concat(Substr(F('invoices__RPDGJ'), 1, 4),
Substr(F('invoices__RPDGJ'), 6, 2),
Substr(F('invoices__RPDGJ'), 9, 2)))
def get_queryset(
self,
project: Optional[Project] = None,
source: Optional[Source] = None,
snapshot: Optional[Snapshot] = None,
):
"""
Get project files.
Allows for filtering:
- using a search string
- using path prefix (e.g for subdirectory listing)
- using a mimetype
Allows for aggregation (the default) by directory.
"""
project = project or self.get_project()
# Avoid using select_related and prefetch_related here
# as it can slow down queries significantly
queryset = File.objects.filter(project=project).order_by("path")
source = source or self.request.GET.get("source")
if source:
queryset = queryset.filter(source=source)
snapshot = snapshot or self.request.GET.get("snapshot")
if snapshot:
queryset = queryset.filter(snapshot=snapshot)
else:
queryset = queryset.filter(snapshot__isnull=True, current=True)
prefix = self.get_prefix()
if prefix:
queryset = queryset.filter(path__startswith=prefix)
search = self.request.GET.get("search", "").strip()
if search:
queryset = queryset.filter(path__istartswith=prefix + search)
mimetype = self.request.GET.get("mimetype", "").strip()
if mimetype:
queryset = queryset.filter(mimetype__startswith=mimetype)
queryset = queryset.annotate(name=Substr(
"path",
pos=len(prefix) + 1,
length=StrIndex(
# Add a trailing slash to ensure all paths
# will have a length
Concat(Substr("path",
len(prefix) + 1), Value("/")),
Value("/"),
) - 1,
output_field=TextField(),
))
expand = self.request.GET.get("expand")
if expand is not None:
return queryset
# Fetch the files, limiting to 10,000 so the following grouping
# does not take forever
# TODO: Put a message in the result if this limit is reached
files = list(queryset.all()[:10000])
groups: Dict[str, Dict[str, Any]] = {}
for file in files:
if "/" in file.path[len(prefix) + 1:]:
name = file.name
info = groups.get(name)
if not info:
groups[name] = dict(
path=prefix + name,
name=file.name,
is_directory=True,
count=1,
source=[file.source_id],
size=file.size,
modified=file.modified,
)
else:
info["count"] += 1
info["size"] += file.size
info["source"] += [file.source_id]
info["modified"] = (file.modified
if file.modified > info["modified"]
else info["modified"])
else:
file.is_directory = False
groups[file.path] = file
# Return items sorted by path again
return [value for key, value in sorted(groups.items())]
def LipList(request):
lmclass, lmdict = lm_class()
lipid_list = Lipid.objects.annotate(
lmid_split=Substr('lmid', 3)).order_by('lmid_split')
params = request.GET.copy()
selectparams = {}
for param in [
'category', 'main_class', 'sub_class', 'l4_class', 'lipidid'
]:
if param in request.GET.keys():
if request.GET[param] != '':
if param == 'lipidid':
liplist = request.GET[param].split(',')
selectparams['lipidid'] = liplist
else:
selectparams[param] = request.GET[param]
if 'category' in selectparams.keys():
lipid_list = lipid_list.filter(core=lmdict[selectparams['category']])
if 'main_class' in selectparams.keys():
lipid_list = lipid_list.filter(
main_class=lmdict[selectparams['main_class']])
if 'sub_class' in selectparams.keys():
lipid_list = lipid_list.filter(
sub_class=lmdict[selectparams['sub_class']])
if 'l4_class' in selectparams.keys():
lipid_list = lipid_list.filter(
l4_class=lmdict[selectparams['l4_class']])
if 'lipidid' in selectparams.keys():
form_select = SelectLipidForm({'lipidid': selectparams['lipidid']})
if form_select.is_valid():
querylist = []
for i in liplist:
querylist.append(Q(id=i))
lipid_list = lipid_list.filter(reduce(operator.or_, querylist))
else:
form_select = SelectLipidForm()
if 'curator' in request.GET.keys():
try:
curator = int(request.GET['curator'])
except ValidationError:
curator = 0
if curator > 0:
lipid_list = lipid_list.filter(curator=User.objects.filter(
id=curator)[0])
sort = request.GET.get('sort')
sortdir = request.GET.get('dir')
lipheaders = ['name', 'lmid', 'com_name', 'sys_name']
if sort is not None and sort in lipheaders:
if sort == 'lmid':
lipid_list = lipid_list.annotate(
lmid_split=Substr('lmid', 3)).order_by('lmid_split')
else:
lipid_list = lipid_list.order_by(sort)
if sortdir == 'des':
lipid_list = lipid_list.reverse()
per_page = 25
if 'per_page' in request.GET.keys():
try:
per_page = int(request.GET['per_page'])
except ValidationError:
per_page = 25
if per_page not in [10, 25, 100]:
per_page = 25
paginator = Paginator(lipid_list, per_page)
page = request.GET.get('page')
try:
lipids = paginator.page(page)
except PageNotAnInteger:
# If page is not an integer, deliver first page.
lipids = paginator.page(1)
except EmptyPage:
# If page is out of range (e.g. 9999), deliver last page of results.
lipids = paginator.page(paginator.num_pages)
data = {}
data['form_select'] = form_select
data['page_objects'] = lipids
data['per_page'] = per_page
data['sort'] = sort
if sort is not None and sort in lipheaders:
data['dir'] = sortdir
data['lipids'] = True
data['params'] = params
return render(request, 'lipids/lipids.html', data)
def get_all_brands(self, **kwargs):
"""
전체 브랜드 리스트 *(initial 혹은 cursor 값으로 paging )
"""
limit = kwargs.get('limit', 20)
cursor = kwargs.get('cursor')
brand_category_id = kwargs.get('brand_category_id')
initial = kwargs.get('initial')
brands = Brand.objects.filter(is_display=True)
if initial:
sch_filter = [
None,
Q(name__regex=r'(ㄱ|ㄲ)') | Q(name__gte='가', name__lt='나'),
Q(name__regex=r'ㄴ') | Q(name__gte='나', name__lt='다'),
Q(name__regex=r'(ㄷ|ㄸ)') | Q(name__gte='다', name__lt='라'),
Q(name__regex=r'ㄹ') | Q(name__gte='라', name__lt='마'),
Q(name__regex=r'ㅁ') | Q(name__gte='마', name__lt='바'),
Q(name__regex=r'ㅂ') | Q(name__gte='바', name__lt='사'),
Q(name__regex=r'(ㅅ|ㅆ)') | Q(name__gte='사', name__lt='아'),
Q(name__regex=r'ㅇ') | Q(name__gte='아', name__lt='자'),
Q(name__regex=r'(ㅈ|ㅉ)') | Q(name__gte='자', name__lt='차'),
Q(name__regex=r'ㅊ') | Q(name__gte='차', name__lt='카'),
Q(name__regex=r'ㅋ') | Q(name__gte='카', name__lt='타'),
Q(name__regex=r'ㅌ') | Q(name__gte='타', name__lt='파'),
Q(name__regex=r'ㅍ') | Q(name__gte='파', name__lt='하'),
Q(name__regex=r'ㅎ') | Q(name__gte='하'),
Q(name__regex=r'^[a-zA-Z]') | Q(name__regex=r'^[0-9]')
]
brands = brands.filter(sch_filter[initial])
if brand_category_id:
brands = brands.filter(categories__id=brand_category_id)
brands = brands.annotate(init=Substr('name', 1), )
brands = brands.annotate(custom_order=Case(
When(Q(init__gte=chr(48)) & Q(init__lt=chr(57)), then=Value(3)),
When(Q(init__lt=chr(128)), then=Value(2)),
default=Value(1),
output_field=IntegerField(),
))
brands = brands.order_by('custom_order', 'name').distinct()
if not initial:
cursor = int(cursor or 1)
offset = (cursor - 1) * limit
brands = brands.all()[offset:offset + limit + 1]
if not brands:
return {
'list': [],
'next_offset': None,
}
if not len(brands):
return {
'list': [],
'next_offset': None,
}
if len(brands) == limit + 1 and not initial:
results = list(brands)
next_offset = cursor + 1
del results[-1]
else:
results = list(brands)
next_offset = None
return {
'list': results,
'next_offset': next_offset,
}
def first_common_ancestor(self, include_self=False, strict=False):
"""
Find the first ancestor that all pages in this queryset have in common.
For example, consider a page hierarchy like::
- Home/
- Foo Event Index/
- Foo Event Page 1/
- Foo Event Page 2/
- Bar Event Index/
- Bar Event Page 1/
- Bar Event Page 2/
The common ancestors for some queries would be:
.. code-block:: python
>>> Page.objects\\
... .type(EventPage)\\
... .first_common_ancestor()
<Page: Home>
>>> Page.objects\\
... .type(EventPage)\\
... .filter(title__contains='Foo')\\
... .first_common_ancestor()
<Page: Foo Event Index>
This method tries to be efficient, but if you have millions of pages
scattered across your page tree, it will be slow.
If `include_self` is True, the ancestor can be one of the pages in the
queryset:
.. code-block:: python
>>> Page.objects\\
... .filter(title__contains='Foo')\\
... .first_common_ancestor()
<Page: Foo Event Index>
>>> Page.objects\\
... .filter(title__exact='Bar Event Index')\\
... .first_common_ancestor()
<Page: Bar Event Index>
A few invalid cases exist: when the queryset is empty, when the root
Page is in the queryset and ``include_self`` is False, and when there
are multiple page trees with no common root (a case Wagtail does not
support). If ``strict`` is False (the default), then the first root
node is returned in these cases. If ``strict`` is True, then a
``ObjectDoesNotExist`` is raised.
"""
# An empty queryset has no ancestors. This is a problem
if not self.exists():
if strict:
raise self.model.DoesNotExist(
"Can not find ancestor of empty queryset")
return self.model.get_first_root_node()
if include_self:
# Get all the paths of the matched pages.
paths = self.order_by().values_list("path", flat=True)
else:
# Find all the distinct parent paths of all matched pages.
# The empty `.order_by()` ensures that `Page.path` is not also
# selected to order the results, which makes `.distinct()` works.
paths = (self.order_by().annotate(parent_path=Substr(
"path",
1,
Length("path") - self.model.steplen,
output_field=CharField(max_length=255),
)).values_list("parent_path", flat=True).distinct())
# This method works on anything, not just file system paths.
common_parent_path = posixpath.commonprefix(paths)
# That may have returned a path like (0001, 0002, 000), which is
# missing some chars off the end. Fix this by trimming the path to a
# multiple of `Page.steplen`
extra_chars = len(common_parent_path) % self.model.steplen
if extra_chars != 0:
common_parent_path = common_parent_path[:-extra_chars]
if common_parent_path == "":
# This should only happen when there are multiple trees,
# a situation that Wagtail does not support;
# or when the root node itself is part of the queryset.
if strict:
raise self.model.DoesNotExist("No common ancestor found!")
# Assuming the situation is the latter, just return the root node.
# The root node is not its own ancestor, so this is technically
# incorrect. If you want very correct operation, use `strict=True`
# and receive an error.
return self.model.get_first_root_node()
# Assuming the database is in a consistent state, this page should
# *always* exist. If your database is not in a consistent state, you've
# got bigger problems.
return self.model.objects.get(path=common_parent_path)
def summarise_by_day() -> Tuple[dict, dict]:
"""
Generate a list of all genus, and the species within, plus number of recordings per species
Returns:
"""
def f_day(date_string):
return parse_date(date_string).strftime(
'%Y-%m-%d') if date_string is not None else None
# Take all the objects
# Annotate them with a day record
# Identify (group) by values
# Annotate the groups with a count of rows
# Spit out the summary
days_by_species = AudioRecording.objects \
.filter(hide=False) \
.annotate(day=Substr('recorded_at_iso', 1, 10)) \
.values('day', 'genus', 'species') \
.annotate(count=Count('id')) \
.values('day', 'genus', 'species', 'count')
# FIXME: R1718: Consider using a set comprehension (consider-using-set-comprehension)
# pylint: disable=R1718
unique_days = set([f_day(row['day']) for row in days_by_species])
unique_genus = set([row['genus'] for row in days_by_species])
days = {
day: {
'day': day,
'count': 0,
'genus': {g: {}
for g in unique_genus}
}
for day in unique_days
}
genus_species = {g: [] for g in unique_genus}
for row in days_by_species:
day_key = f_day(row['day'])
days[day_key]['count'] += row['count']
for genus_abbr in unique_genus:
if row['genus'] == genus_abbr:
days[day_key]['genus'][genus_abbr][
row['species']] = row['count']
genus_species[genus_abbr].append(row['species'])
lookup = SpeciesLookup()
genus_lookup = lookup.genus_name_by_abbreviation
# genus_species = Map of (eg) { PYP: [NAT, PIP, PYG], …} - species lists are unsorted here
genus_map = {}
for genus_abbr in genus_species:
name = genus_lookup(genus_abbr)
genus_map[genus_abbr] = {
'name': name if isinstance(name, str) else None,
'species': []
}
species_abbreviations = sorted(set(genus_species[genus_abbr]))
for species_abbr in species_abbreviations:
try:
species_data = lookup.species_by_abbreviations(
genus_abbr, species_abbr)
except NonUniqueSpeciesLookup:
species_data = None
species_item = {
'abbreviation': species_abbr,
'species': species_data
}
genus_map[genus_abbr]['species'].append(species_item)
return days, genus_map
