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