def inforesources_name(request):
dbsession = DBSession()
numRows = 0
inforesources = []
success = True
if ('id' in request.params) and request.params['id'].isdigit():
id = int(request.params['id'])
try:
inforesources = dbsession.query(Inforesources.id, Inforesources.filename)\
.filter(Inforesources.id == id).all()
numRows = 1
except DBAPIError:
success = False
else:
start, count = helpers.get_paging_params(request.params)
parsed_filename = helpers.get_parsed_search_attr(
request.params, 'filename')
filter_conditions = []
if parsed_filename:
filter_conditions.append(
Inforesources.filename.ilike(parsed_filename))
try:
if (start is not None) and (count is not None):
inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \
.filter(*filter_conditions) \
.order_by(Inforesources.filename) \
.slice(start, start + count) \
.all()
numRows = dbsession.query(Inforesources) \
.filter(*filter_conditions) \
.count()
else:
inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \
.filter(*filter_conditions) \
.order_by(Inforesources.filename) \
.all()
numRows = len(inforesources)
except DBAPIError:
success = False
inforesources_json = []
for (id, name) in inforesources:
inforesources_json.append({'id': id, 'filename': name})
dbsession.close()
return {
'items': inforesources_json,
'success': success,
'numRows': numRows,
'identifier': 'id'
}
def inforesources_name(request):
dbsession = DBSession()
numRows = 0
inforesources = []
success = True
if ('id' in request.params) and request.params['id'].isdigit():
id = int(request.params['id'])
try:
inforesources = dbsession.query(Inforesources.id, Inforesources.filename)\
.filter(Inforesources.id == id).all()
numRows = 1
except DBAPIError:
success = False
else:
start, count = helpers.get_paging_params(request.params)
parsed_filename = helpers.get_parsed_search_attr(request.params, 'filename')
filter_conditions = []
if parsed_filename:
filter_conditions.append(Inforesources.filename.ilike(parsed_filename))
try:
if (start is not None) and (count is not None):
inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \
.filter(*filter_conditions) \
.order_by(Inforesources.filename) \
.slice(start, start + count) \
.all()
numRows = dbsession.query(Inforesources) \
.filter(*filter_conditions) \
.count()
else:
inforesources = dbsession.query(Inforesources.id, Inforesources.filename) \
.filter(*filter_conditions) \
.order_by(Inforesources.filename) \
.all()
numRows = len(inforesources)
except DBAPIError:
success = False
inforesources_json = []
for (id, name) in inforesources:
inforesources_json.append({'id': id, 'filename': name})
dbsession.close()
return {
'items': inforesources_json,
'success': success,
'numRows': numRows,
'identifier': 'id'
}
def species_filter(request):
with transaction.manager:
dbsession = DBSession()
try:
query_str = request.params['name']
except KeyError:
query_str = ''
taxon_id = request.matchdict['id']
taxon_type = request.matchdict['type']
species_types = {'mammalia': MAMMALIA, 'aves': AVES, 'plantae': PLANTAE, 'ara': ARA,
'arthropoda': ARTHROPODA, 'moss': MOSS, 'lichenes': LICHENES}
try:
sp = species_types[taxon_type]
except KeyError:
return {'success': False, 'msg': 'Неверный вид организма'}
known_tax, all_taxons, all_russian_name, all_synonyms = [], [], [], []
success = True
try:
# Если id известен, выберем запись:
if ('id' in request.params) and request.params['id']:
taxon_id = int(request.params['id'])
known_tax = dbsession.query(Taxon.id, Taxon.name, Taxon.author, Taxon.source).filter(
Taxon.id == taxon_id).all()
else:
if query_str or query_str == '':
target = dbsession.query(Taxon).filter(Taxon.name.in_(sp)).all()
target_ids = [t.id for t in target]
target_ids = ", ".join([str(num) for num in target_ids])
qs = TAXON_ALL_QUERY % (target_ids, TAXON_TYPES[len(TAXON_TYPES) - 1])
subquery = " AND UPPER(%s) LIKE '%s%s%s' ORDER BY name" % ('name', '%', query_str.upper(), '%')
qs = qs + subquery + ';'
all_taxons = dbsession.query(Taxon.id, Taxon.name, Taxon.author, Taxon.source).from_statement(qs).all()
qs = TAXON_ALL_QUERY % (target_ids, TAXON_TYPES[len(TAXON_TYPES) - 1])
subquery = " AND UPPER(%s) LIKE '%s%s%s' ORDER BY russian_name" % ('russian_name', '%', query_str.upper(), '%')
qs = qs + subquery + ';'
all_russian_name = dbsession.query(Taxon.id, Taxon.russian_name, Taxon.author, Taxon.source).from_statement(qs).all()
subquery = TAXON_ID_QUERY % (target_ids, TAXON_TYPES[len(TAXON_TYPES) - 1])
synonyms_query = "SELECT * FROM synonym WHERE UPPER(%s) LIKE '%s%s%s' AND species_id IN ( %s) ORDER BY synonym;" % \
('synonym', '%', query_str.upper(), '%', subquery)
all_synonyms = dbsession.query(Synonym.species_id, Synonym.synonym, Synonym.author,
Synonym.source).from_statement(synonyms_query).all()
except DBAPIError:
success = False
taxons = known_tax + all_taxons + all_russian_name + all_synonyms
numRows = len(taxons)
start, count = helpers.get_paging_params(request.params)
if (start is not None) and (count is not None):
taxons = taxons[start:start + count]
taxons_json = []
if taxons:
taxons_json = [{'id': id, 'name': name} for id, name, author, source in taxons]
return {
'items': taxons_json,
'success': success,
'numRows': numRows,
'identifier': 'id'
}
def species_filter(request):
with transaction.manager:
dbsession = DBSession()
try:
query_str = request.params['name']
except KeyError:
query_str = ''
taxon_id = request.matchdict['id']
taxon_type = request.matchdict['type']
species_types = {
'mammalia': MAMMALIA,
'aves': AVES,
'plantae': PLANTAE,
'ara': ARA,
'arthropoda': ARTHROPODA,
'moss': MOSS,
'lichenes': LICHENES
}
try:
sp = species_types[taxon_type]
except KeyError:
return {'success': False, 'msg': 'Неверный вид организма'}
known_tax, all_taxons, all_russian_name, all_synonyms = [], [], [], []
success = True
try:
# Если id известен, выберем запись:
if ('id' in request.params) and request.params['id']:
taxon_id = int(request.params['id'])
known_tax = dbsession.query(
Taxon.id, Taxon.name, Taxon.author,
Taxon.source).filter(Taxon.id == taxon_id).all()
else:
if query_str or query_str == '':
target = dbsession.query(Taxon).filter(
Taxon.name.in_(sp)).all()
target_ids = [t.id for t in target]
target_ids = ", ".join([str(num) for num in target_ids])
qs = TAXON_ALL_QUERY % (target_ids,
TAXON_TYPES[len(TAXON_TYPES) - 1])
subquery = " AND UPPER(%s) LIKE '%s%s%s' ORDER BY name" % (
'name', '%', query_str.upper(), '%')
qs = qs + subquery + ';'
all_taxons = dbsession.query(
Taxon.id, Taxon.name, Taxon.author,
Taxon.source).from_statement(qs).all()
qs = TAXON_ALL_QUERY % (target_ids,
TAXON_TYPES[len(TAXON_TYPES) - 1])
subquery = " AND UPPER(%s) LIKE '%s%s%s' ORDER BY russian_name" % (
'russian_name', '%', query_str.upper(), '%')
qs = qs + subquery + ';'
all_russian_name = dbsession.query(
Taxon.id, Taxon.russian_name, Taxon.author,
Taxon.source).from_statement(qs).all()
subquery = TAXON_ID_QUERY % (
target_ids, TAXON_TYPES[len(TAXON_TYPES) - 1])
synonyms_query = "SELECT * FROM synonym WHERE UPPER(%s) LIKE '%s%s%s' AND species_id IN ( %s) ORDER BY synonym;" % \
('synonym', '%', query_str.upper(), '%', subquery)
all_synonyms = dbsession.query(
Synonym.species_id, Synonym.synonym, Synonym.author,
Synonym.source).from_statement(synonyms_query).all()
except DBAPIError:
success = False
taxons = known_tax + all_taxons + all_russian_name + all_synonyms
numRows = len(taxons)
start, count = helpers.get_paging_params(request.params)
if (start is not None) and (count is not None):
taxons = taxons[start:start + count]
taxons_json = []
if taxons:
taxons_json = [{
'id': id,
'name': name
} for id, name, author, source in taxons]
return {
'items': taxons_json,
'success': success,
'numRows': numRows,
'identifier': 'id'
}
def table_browse(request):
dbsession = DBSession()
tablename = request.matchdict['table']
try:
table = table_by_name(tablename)
except KeyError:
return {'success': False, 'msg': 'Ошибка: отсутствует таблица с указанным именем'}
numRows = 0
items = []
success = True
if ('id' in request.params) and request.params['id'].isdigit():
id = int(request.params['id'])
try:
items = dbsession.query(table) \
.filter(table.id == id) \
.all()
numRows = 1
except DBAPIError:
success = False
else:
start, count = helpers.get_paging_params(request.params)
filter_conditions = []
parsed_name = helpers.get_parsed_search_attr(request.params, tablename)
if parsed_name:
filter_conditions.append(getattr(table, tablename).ilike(parsed_name))
org_type = request.params['org_type'] if 'org_type' in request.params else None
if org_type and hasattr(table, 'org_type'):
filter_conditions.append(getattr(table, 'org_type') == org_type)
try:
if (start is not None) and (count is not None):
items = dbsession.query(table)\
.filter(*filter_conditions) \
.order_by(tablename + ' asc') \
.slice(start, start+count) \
.all()
numRows = dbsession.query(table) \
.filter(*filter_conditions) \
.count()
else:
items = dbsession.query(table) \
.filter(*filter_conditions) \
.order_by(tablename + ' asc') \
.all()
numRows = len(items)
except DBAPIError:
success = False
dbsession.close()
items_json = []
for row in items:
items_json.append(row.as_json_dict())
return {
'items': items_json,
'success': success,
'numRows': numRows,
'identifier': 'id'
}
def table_browse(request):
dbsession = DBSession()
tablename = request.matchdict['table']
try:
table = table_by_name(tablename)
except KeyError:
return {
'success': False,
'msg': 'Ошибка: отсутствует таблица с указанным именем'
}
numRows = 0
items = []
success = True
if ('id' in request.params) and request.params['id'].isdigit():
id = int(request.params['id'])
try:
items = dbsession.query(table) \
.filter(table.id == id) \
.all()
numRows = 1
except DBAPIError:
success = False
else:
start, count = helpers.get_paging_params(request.params)
filter_conditions = []
parsed_name = helpers.get_parsed_search_attr(request.params, tablename)
if parsed_name:
filter_conditions.append(
getattr(table, tablename).ilike(parsed_name))
org_type = request.params[
'org_type'] if 'org_type' in request.params else None
if org_type and hasattr(table, 'org_type'):
filter_conditions.append(getattr(table, 'org_type') == org_type)
try:
if (start is not None) and (count is not None):
items = dbsession.query(table)\
.filter(*filter_conditions) \
.order_by(tablename + ' asc') \
.slice(start, start+count) \
.all()
numRows = dbsession.query(table) \
.filter(*filter_conditions) \
.count()
else:
items = dbsession.query(table) \
.filter(*filter_conditions) \
.order_by(tablename + ' asc') \
.all()
numRows = len(items)
except DBAPIError:
success = False
dbsession.close()
items_json = []
for row in items:
items_json.append(row.as_json_dict())
return {
'items': items_json,
'success': success,
'numRows': numRows,
'identifier': 'id'
}
