def test_intersects(self):
r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one()
r2 = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
r3 = session.query(Road).filter(Road.road_name=='Geordie Rd').one()
intersecting_roads = session.query(Road).filter(Road.road_geom.intersects(r1.road_geom)).all()
ok_(r2 in intersecting_roads)
ok_(r3 not in intersecting_roads)
eq_(session.scalar(functions.intersects('POINT(0 0)', 'LINESTRING ( 2 0, 0 2 )')), False)
def test_intersects(self):
r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one()
r2 = session.query(Road).filter(Road.road_name=='Graeme Ave').one()
r3 = session.query(Road).filter(Road.road_name=='Geordie Rd').one()
intersecting_roads = session.query(Road).filter(Road.road_geom.intersects(r1.road_geom)).all()
ok_(r2 in intersecting_roads)
ok_(r3 not in intersecting_roads)
eq_(session.scalar(functions.intersects('POINT(0 0)', 'LINESTRING ( 2 0, 0 2 )')), False)
def _get_profile_filter(self, request):
"""
Return a spatial filter based on the profile boundary of the
current profile which is queried from the database.
Copied from the ActivityProtocol3
"""
profile = self.Session.query(Profile).\
filter(Profile.code == get_current_profile(request)).\
first()
if profile is None:
return (Activity.id == 0)
return functions.intersects(Activity.point, profile.geometry)
def evaluation_json(self):
request = self.request
ret = {'success': False}
# temp
tempSwitch = request.matchdict.get('temp', None)
if tempSwitch == '1':
groupby = request.params.get('groupby', 'Intention of Investment')
input = {
'item': 'Activity',
'filter': {
'geometry': '',
'sql': ''
},
'attributes': {
'Activity': 'count',
'Intended area (ha)': 'sum'
},
'group_by': [groupby]
}
elif tempSwitch == '2':
input = {
'item': 'Activity',
'filter': {
'geometry': '',
'sql': ''
},
'attributes': {
'Activity': 'count',
'Name of Investor': 'count distinct'
},
'group_by': ['Country']
}
elif tempSwitch == '3':
input = {
'item': 'Activity',
'filter': {
'geometry': '',
'sql': ''
},
'attributes': {
'Contract area (ha)': 'sum',
'Activity': 'count'
},
'group_by': ['Country', 'Country of Investor']
}
elif tempSwitch == '4':
input = {
'item': 'Activity',
'filter': {
'geometry': '',
'sql': ''
},
'attributes': {
'Activity': 'count',
'Contract area (ha)': 'sum'
},
'group_by': ['Year of agreement']
}
elif tempSwitch == '5':
input = {
'item': 'Activity',
'filter': {
'geometry': '',
'sql': ''
},
'attributes': {
'Activity': 'count',
'Contract area (ha)': 'sum'
},
'group_by': ['Year of agreement', 'Intention of Investment']
}
elif tempSwitch == '6':
input = {
'item': 'Stakeholder',
'filter': {
'geometry': '',
'sql': ''
},
'attributes': {
'Stakeholder': 'count'
},
'group_by': ['Country']
}
else:
ret['msg'] = 'Temporarily only /1, /2 and /3 available.'
return ret
if input['item'] == 'Stakeholder':
Item = Stakeholder
Tag_Group = SH_Tag_Group
Tag = SH_Tag
Key = SH_Key
Value = SH_Value
else:
Item = Activity
Tag_Group = A_Tag_Group
Tag = A_Tag
Key = A_Key
Value = A_Value
# Test input
if 'group_by' not in input:
ret['msg'] = "Missing parameter 'group by': At least one column needs to be specified."
return ret
if not isinstance(input['group_by'], list):
ret['msg'] = "Parameter 'group by' needs to be an array."
return ret
if 'attributes' not in input:
ret['msg'] = "Missing attributes: No attributes were specified."
return ret
for attr in input['attributes']:
test = self._checkFunction(Item, Tag_Group, Tag, Key, Value, input['attributes'][attr], attr)
if test is not True:
ret['msg'] = test
return ret
# Get groups
groups_subqueries, groups_columns = self._getGroupBy(Item, Tag_Group, Tag, Key, Value, input['group_by'])
# Get functions
functions_subqueries, functions_columns = self._getAttributeFunctions(Item, Tag_Group, Tag, Key, Value, input['attributes'])
# Prepare basic query (already joins first group)
q = Session.query(*groups_columns + functions_columns).\
join(Tag_Group).\
join(Item)
# Join with further groups
for g_sq in groups_subqueries[1:]:
q = q.outerjoin(g_sq, g_sq.c.a_id == Item.id)
# Join with functions
for f_sq in functions_subqueries:
q = q.outerjoin(f_sq, f_sq.c.a_id == Item.id)
# Apply status filter (fix: active)
fk_status = Session.query(Status.id).filter(Status.name == 'active')
q = q.filter(Item.fk_status == fk_status)
# Apply profile boundary filter
if Item == Activity:
profile = Session.query(Profile).\
filter(Profile.code == get_current_profile(request)).\
first()
if profile is not None:
q = q.filter(functions.intersects(Item.point, profile.geometry))
# Apply grouping and ordering
q = q.group_by(*groups_columns).\
order_by(groups_columns[0])
data = []
for res in q.all():
entry = {}
# first go through group_by
for i, group in enumerate(input['group_by']):
entry[group] = res[i]
# then go through functions
for i, attr in enumerate(input['attributes']):
displayAttr = attr
if attr == 'Activity':
displayAttr = 'Deals'
entry["%s (%s)" % (displayAttr, input['attributes'][attr])] = res[i + len(input['group_by'])]
data.append(entry)
ret['success'] = True
ret['data'] = data
return ret
def evaluation(self, data=None):
ret = {'success': False}
json_data = self.request.json_body if data is None else data
if json_data is None:
ret['msg'] = 'No data provided'
return ret
if validate_item_type(json_data.get('item', 'a')) == 'sh':
self.db_item = Stakeholder
self.db_taggroup = SH_Tag_Group
self.db_tag = SH_Tag
self.db_key = SH_Key
self.db_value = SH_Value
self.protocol = StakeholderProtocol3(Session)
else:
self.db_item = Activity
self.db_taggroup = A_Tag_Group
self.db_tag = A_Tag
self.db_key = A_Key
self.db_value = A_Value
self.protocol = ActivityProtocol3(Session)
# Make sure the json is valid
if 'group_by' not in json_data:
ret['msg'] = "Missing parameter 'group by': At least one column "
"needs to be specified."
return ret
if not isinstance(json_data['group_by'], list):
ret['msg'] = "Parameter 'group by' needs to be an array."
return ret
if 'attributes' not in json_data:
ret['msg'] = "Missing attributes: No attributes were specified."
return ret
for attr in json_data['attributes']:
test, msg = self._check_function(
json_data['attributes'][attr], attr)
if test is not True:
ret['msg'] = msg
return ret
if 'locales' in json_data and not isinstance(
json_data['locales'], list):
ret['msg'] = "Parameter 'locales' needs to be an array."
return ret
translate_keys = json_data.get('translate', {}).get('keys', [])
if translate_keys and not isinstance(translate_keys, list):
ret['msg'] = "Parameter 'translate[\'keys\']' needs to be an "
"array."
return ret
for k in translate_keys:
if not isinstance(k, list):
ret['msg'] = "Value of 'translate[\'keys\']' needs to be "
"an array of arrays."
return ret
a_ids = json_data.get('a_ids', [])
if not isinstance(a_ids, list):
ret['msg'] = "Parameter 'a_ids' needs to be an array."
return ret
for i in a_ids:
if not isinstance(i, str):
ret['msg'] = "Entries of parameter 'a_ids' need to be "
"strings (the UUIDs of Activities)"
return ret
sh_ids = json_data.get('sh_ids', [])
if not isinstance(sh_ids, list):
ret['msg'] = "Parameter 'sh_ids' needs to be an array."
return ret
for i in sh_ids:
if not isinstance(i, str):
ret['msg'] = "Entries of parameter 'sh_ids' need to be "
"strings (the UUIDs of Stakeholders)"
return ret
if self.db_item == Activity:
this_id_filter = a_ids
other_id_filter = sh_ids
else:
this_id_filter = sh_ids
other_id_filter = a_ids
this_filter = []
other_filter = []
if 'filter' in json_data:
params = []
for filters in json_data.get('filter', '').split('&'):
try:
f = filters.split('=')
if len(f) == 2:
params.append((f[0], f[1]))
except:
pass
# Simulate a request to send the filters
req = DummyRequest()
req.params = MultiDict(params)
a_tag_filter, __, sh_tag_filter, __ = self.protocol._filter(req)
if self.db_item == Activity:
this_filter = a_tag_filter
other_filter = sh_tag_filter
else:
this_filter = sh_tag_filter
other_filter = a_tag_filter
isInvolvementRequired = (
self.db_item == Stakeholder
or len(other_filter) + len(other_id_filter) > 0)
# Collect all keys to be translated (values are translated in the
# query)
locales = ['default']
langs = []
locales.extend(json_data.get('locales', []))
translated_keys = {}
exclude_from_translation = ['Activity', 'Stakeholder']
keys = []
for key, __ in json_data.get('attributes', {}).iteritems():
if key not in exclude_from_translation and key not in keys:
keys.append(key)
for key in json_data.get('group_by', []):
if key not in exclude_from_translation and key not in keys:
keys.append(key)
for key in translate_keys:
for k in key:
if k not in keys:
keys.append(k)
for l in locales:
locale = l
if l == 'default':
locale = get_current_locale(self.request)
db_lang = Session.query(Language).filter(
Language.locale == locale).first()
langs.append((l, db_lang))
translated_keys[l] = get_translated_db_keys(
self.db_key, keys, db_lang)
# Get groups
groups_subqueries, groups_columns = self._get_group_by(
json_data['group_by'], langs)
# Get functions
functions_subqueries, functions_columns = \
self._get_attribute_functions(json_data['attributes'])
# Prepare basic query
q = Session.query(*groups_columns + functions_columns).\
join(self.db_taggroup).\
join(self.db_item)
# Join with further groups
for g_sq in groups_subqueries[1:]:
q = q.outerjoin(g_sq, g_sq.c.item_id == self.db_item.id)
# Join with functions
for f_sq in functions_subqueries:
q = q.outerjoin(f_sq, f_sq.c.item_id == self.db_item.id)
# Apply status filter (fix: active)
q = q.filter(self.db_item.fk_status == 2)
if (this_id_filter):
q = q.filter(self.db_item.identifier.in_(this_id_filter))
# Apply filters
filter_subqueries = self.protocol.Session.query(
self.db_item.id.label('a_filter_id')
)
for x in this_filter:
# Collect the IDs for each filter
taggroups_sq = x.subquery()
single_subquery = self.protocol.Session.query(
self.db_item.id.label('a_filter_id')
).\
join(self.db_taggroup).\
join(taggroups_sq,
taggroups_sq.c.a_filter_tg_id == self.db_taggroup.id).\
subquery()
# Join each found ID with previously found IDs
filter_subqueries = filter_subqueries.\
join(single_subquery,
single_subquery.c.a_filter_id == self.db_item.id)
filter_subqueries = filter_subqueries.subquery()
q = q.join(
filter_subqueries,
filter_subqueries.c.a_filter_id == self.db_item.id)
# Apply profile boundary filter
if self.db_item == Activity:
p = json_data.get('profile', get_current_profile(self.request))
profile = Session.query(Profile).\
filter(Profile.code == p).\
first()
if profile is not None:
q = q.filter(geofunctions.intersects(
self.db_item.point, profile.geometry))
# Apply grouping and ordering
q = q.group_by(*groups_columns).\
order_by(groups_columns[0])
if isInvolvementRequired:
if self.db_item == Stakeholder:
inv_subquery = Session.query(
Involvement.fk_stakeholder.label('id')
).\
join(Activity).\
filter(Activity.fk_status == 2)
p = json_data.get('profile', get_current_profile(self.request))
profile = Session.query(Profile).\
filter(Profile.code == p).\
first()
if profile is not None:
inv_subquery = inv_subquery.filter(geofunctions.intersects(
Activity.point, profile.geometry))
other_db_item = Activity
other_db_taggroup = A_Tag_Group
else:
inv_subquery = Session.query(
Involvement.fk_activity.label('id')
).\
join(Stakeholder).\
filter(Stakeholder.fk_status == 2)
other_db_item = Stakeholder
other_db_taggroup = SH_Tag_Group
if (other_id_filter):
inv_subquery = inv_subquery.filter(
other_db_item.identifier.in_(other_id_filter))
# Apply filters
filter_subqueries = self.protocol.Session.query(
other_db_item.id.label('a_filter_id')
)
for x in other_filter:
# Collect the IDs for each filter
taggroups_sq = x.subquery()
try:
single_subquery = self.protocol.Session.query(
other_db_item.id.label('a_filter_id')
).\
join(other_db_taggroup).\
join(taggroups_sq,
taggroups_sq.c.a_filter_tg_id == other_db_taggroup.id).\
subquery()
except AttributeError:
single_subquery = self.protocol.Session.query(
other_db_item.id.label('a_filter_id')
).\
join(other_db_taggroup).\
join(taggroups_sq,
taggroups_sq.c.sh_filter_tg_id == other_db_taggroup.id).\
subquery()
# Join each found ID with previously found IDs
filter_subqueries = filter_subqueries.\
join(single_subquery,
single_subquery.c.a_filter_id == other_db_item.id)
filter_subqueries = filter_subqueries.subquery()
inv_subquery = inv_subquery.join(
filter_subqueries,
filter_subqueries.c.a_filter_id == other_db_item.id)
inv_subquery = inv_subquery.subquery()
q = q.filter(self.db_item.id.in_(
select([inv_subquery.c.id])
))
data = []
for res in q.all():
data = _handle_single_line(
data, res, json_data.get('group_by'),
json_data.get('attributes'), translated_keys)
# Do a translation of groupable if available
groupable_translated = []
for key in translate_keys:
translations = []
for k in key:
t = {
'key': k,
'default': k
}
for locale, key_translations in translated_keys.iteritems():
translation = (
None if k not in exclude_from_translation else k)
for k_t in key_translations:
if len(k_t) >= 2 and k_t[0] == k:
translation = k_t[1]
t[locale] = translation
translations.append(t)
groupable_translated.append(translations)
if len(groupable_translated):
ret.update({
'translate': {'keys': groupable_translated}
})
ret.update({
'success': True,
'data': data
})
return ret
