def get_base_sql(cls):
return u"""SELECT
sub.name AS name,
%(sub_columns)s
'dummy' AS dummy
FROM
landmatrix_activity AS a """ + "\n" \
+ "LEFT JOIN landmatrix_publicinterfacecache AS pi ON a.id = pi.fk_activity_id AND pi.is_deal\n" \
+ join_attributes('intended_size') + "\n" \
+ join_attributes('contract_size') + "\n" \
+ join_attributes('production_size') + "\n" \
+ \
"""
JOIN (
SELECT DISTINCT
a.id AS id,
%(name)s AS name,
%(columns)s 'dummy' AS dummy
FROM landmatrix_activity AS a
%(from)s""" + "\n" + \
"LEFT JOIN landmatrix_publicinterfacecache AS pi ON a.id = pi.fk_activity_id AND pi.is_deal\n" +\
join_attributes('deal_scope') + """
%(from_filter)s
WHERE """ + "\nAND ".join([
# cls.max_version_condition(),
cls.status_active_condition(), cls.is_deal_condition(), cls.not_mining_condition()
]) + """
def _tables_activity(self):
tables_from = []
if self.filters.get("activity", {}).get("tags"):
tags = self.filters.get("activity").get("tags")
for index, (tag, value) in enumerate(tags.items()):
i = index + FilterToSQL.count_offset
variable_operation = tag.split("__")
variable = variable_operation[0]
# join tag tables for each condition
if variable == "region":
tables_from.append(
"LEFT JOIN landmatrix_activityattributegroup AS attr_%(count)i, countries AS ac%(count)i, regions AS ar%(count)i \n" % {"count": i} +\
" ON (a.id = attr_%(count)i.fk_activity_id AND attr_%(count)i.attributes ? 'target_country' AND attr_%(count)i.value = ac%(count)i.name AND ar%(count)i.id = ac%(count)i.fk_region)"%{"count": i, "key": variable}
)
if variable.isdigit():
tables_from.append(
"LEFT JOIN landmatrix_activityattributegroup AS attr_%(count)i\n" % {"count": i} +\
" ON (a.id = attr_%(count)i.fk_activity_id AND attr_%(count)i.key_id = '%(key)s')"%{"count": i, "key": variable}
)
else:
tables_from.append(
join_attributes("attr_%(count)i" % {"count": i},
variable))
return '\n'.join(tables_from)
def _tables_activity(self, taggroup=None, last_index=0):
tables_from = []
tags = taggroup or self.filters.get("activity").get("tags")
for index, (tag, value) in enumerate(tags.items()):
i = last_index + index
# Multiple rules?
if isinstance(value, dict):
if value:
tables_from.append(
self._tables_activity(taggroup=value, last_index=i))
last_index += len(value) - 1
continue
variable_operation = tag.split("__")
variable = variable_operation[0]
key = variable_operation[1]
no_join_required = (
variable == 'activity_identifier'
or (variable in ('negotiation_status', 'implementation_status')
and key == 'value'))
if no_join_required:
continue
elif variable in ('target_region', 'deal_country'):
tables_from.append("""
LEFT JOIN landmatrix_activityattribute AS attr_%(count)i
ON (a.id = attr_%(count)i.fk_activity_id AND attr_%(count)i.name = 'target_country')
""" % {
'count': i,
})
tables_from.append("""
LEFT JOIN landmatrix_country AS ac%(count)i
ON CAST(NULLIF(attr_%(count)i.value, '0') AS NUMERIC) = ac%(count)i.id
""" % {
'count': i,
})
if variable == 'target_region':
tables_from.append("""
LEFT JOIN landmatrix_region AS ar%(count)i
ON ar%(count)i.id = ac%(count)i.fk_region_id
""" % {
'count': i,
})
elif variable.isdigit():
tables_from.append(
"LEFT JOIN landmatrix_activityattribute AS attr_%(count)i\n" \
" ON (a.id = attr_%(count)i.fk_activity_id AND attr_%(count)i.key_id = '%(key)s')" % {
"count": i,
"key": variable
}
)
else:
tables_from.append(
join_attributes("attr_%(count)i" % {"count": i}, variable))
return '\n'.join(tables_from)
def get_base_sql(cls):
return u"""SELECT DISTINCT
a.activity_identifier,
%(columns)s, a.id AS id
FROM landmatrix_activity AS a
%(from)s
""" + join(PublicInterfaceCache, 'pi', 'a.id = pi.fk_activity_id AND pi.is_deal') + '\n'\
+ join_attributes('deal_scope') + """
%(from_filter)s
WHERE """ + "\nAND ".join([
cls.status_active_condition(), cls.is_deal_condition(), cls.not_mining_condition()
]) + """
def get_base_sql(self):
return u"""SELECT DISTINCT
a.activity_identifier,
%(columns)s, a.id AS id
FROM landmatrix_activity AS a
%(from)s
""" + join_attributes('deal_scope') + """
%(from_filter)s
WHERE """ + "\nAND ".join(
filter(
None,
[
self.status_active_condition(),
self.is_public_condition(),
#self.not_mining_condition()
])) + """
def _browse_filters_to_sql(self, filters):
sql = {
"activity": {
"where": "",
"from": "",
},
"stakeholder": {
"where": "",
"from": "",
}
}
if not filters:
return sql
tables_from_act, where_act, tables_from_inv, where_inv = "", "", "", ""
if filters.get("activity", {}).get("identifier"):
# print(filters.get("activity").get("identifier"))
for f in filters.get("activity").get("identifier"):
# print('f:',f)
operation = f.get("op")
value = ",".join(filter(None, [s.strip() for s in f.get("value").split(",")]))
where_act += "AND a.activity_identifier %s " % self.OPERATION_MAP[operation][0] % value
if filters.get("deal_scope") and filters.get("deal_scope") != "all":
where_act += " AND deal_scope.attributes->'deal_scope' = '%s' " % filters.get("deal_scope")
if filters.get("activity", {}).get("tags"):
tags = filters.get("activity").get("tags")
for i, (tag, value) in enumerate(tags.items()):
variable_operation = tag.split("__")
variable = variable_operation[0]
# choose operation depending on variable type default 0(int)
operation = ""
if len(variable_operation) > 1:
operation = variable_operation[1]
# empty as operation or no value given
if operation == "is_empty" or not value or (value and not value[0]):
where_act += " AND attr_%(count)i.value IS NULL " % {
"count": i,
}
elif operation in ("in", "not_in"):
# value = value[0].split(",")
in_values = ",".join(["'%s'" % v.strip().replace("'", "\\'") for v in value])
if variable == "region":
where_act += " AND ar%(count)i.name %(op)s " % {
"count": i,
"op": self.OPERATION_MAP[operation][0] % in_values,
}
else:
where_act += " AND attr_%(count)i.attributes->'%(variable)s' %(op)s " % {
"count": i,
"op": self.OPERATION_MAP[operation][0] % in_values,
'variable': variable
}
else:
for v in value:
year = None
if "##!##" in v:
v,year = v.split("##!##")[0], v.split("##!##")[1]
operation_type = not v.isdigit() and 1 or 0
if variable == "region":
where_act += " AND ar%(count)i.name %(op)s " % { "count": i, "op": self.OPERATION_MAP[operation][operation_type] % v.replace("'", "\\'")}
else:
where_act += " %(value)s %(year)s " % {
"value": v and " AND attr_%(count)i.value %(op)s " % { "count": i, "op": self.OPERATION_MAP[operation][operation_type] % v.replace("'", "\\'")} or "",
"year": year and " AND attr_%i.year = '%s' " % (i, year) or ""
}
# join tag tables for each condition
if variable == "region":
tables_from_act += "LEFT JOIN landmatrix_activityattributegroup AS attr_%(count)i, countries AS ac%(count)i, regions AS ar%(count)i \n" % {"count": i}
tables_from_act += " ON (a.id = attr_%(count)i.fk_activity_id AND attr_%(count)i.attributes ? 'target_country' AND attr_%(count)i.value = ac%(count)i.name AND ar%(count)i.id = ac%(count)i.fk_region)"%{"count": i, "key": variable}
if variable.isdigit():
tables_from_act += "LEFT JOIN landmatrix_activityattributegroup AS attr_%(count)i\n" % {"count": i}
tables_from_act += " ON (a.id = attr_%(count)i.fk_activity_id AND attr_%(count)i.key_id = '%(key)s')"%{"count": i, "key": variable}
else:
from api.query_sets.sql_generation.join_functions import join_attributes
tables_from_act += join_attributes("attr_%(count)i" % {"count": i}, variable)
# tables_from_act += "LEFT JOIN landmatrix_activityattributegroup AS attr_%(count)i\n" % {"count": i}
# tables_from_act += " ON (a.id = attr_%(count)i.fk_activity_id AND attr_%(count)i.attributes ? '%(key)s')"%{"count": i, "key": variable}
sql["activity"]["from"] = tables_from_act
sql["activity"]["where"] = where_act
if filters.get("investor", {}).get("tags"):
tags = filters.get("investor").get("tags")
for i, (tag, value) in enumerate(tags.items()):
if not value:
continue
variable_operation = tag.split("__")
variable = variable_operation[0]
# choose operation depending on variable type default 0(int)
operation = ""
if len(variable_operation) > 1:
operation = variable_operation[1]
if operation == "is_empty" or not value or (value and not value[0]):
where_inv += " AND skv%(count)i.value IS NULL " % {
"count": i,
}
elif operation in ("in", "not_in"):
value = value[0].split(",")
in_values = ",".join(["'%s'" % v.strip().replace("'", "\\'") for v in value])
if variable == "region":
where_inv += " AND skvr%(count)i.name %(op)s" % {
"count": i,
"op": self.OPERATION_MAP[operation][0] % in_values
}
else:
where_inv += " AND skv%(count)i.value %(op)s" % {
"count": i,
"op": self.OPERATION_MAP[operation][0] % in_values
}
else:
for v in value:
operation_type = not v.isdigit() and 1 or 0
if variable == "region":
where_inv += " AND skvr%i.name %s" % (i, self.OPERATION_MAP[operation][operation_type] % v.replace("'", "\\'"))
else:
where_inv += " AND skv%i.value %s" % (i, self.OPERATION_MAP[operation][operation_type] % v.replace("'", "\\'"))
#query_params.append(v)
# join tag tables for each condition
if variable == "region":
tables_from_inv += "LEFT JOIN (sh_key_value_lookup skv%(count)i, countries skvc%(count)i, regions skvr%(count)i) \n" % {"count": i}
tables_from_inv += " ON (skv%(count)i.stakeholder_identifier = s.stakeholder_identifier AND skv%(count)i.key = 'country' AND skv%(count)i.value = skvc%(count)i.name AND skvr%(count)i.id = skvc%(count)i.fk_region)"%{"count": i, "key": variable}
else:
tables_from_inv += "LEFT JOIN landmatrix_stakeholderattributegroup AS skv%(count)i\n" % {"count": i}
tables_from_inv += " ON (skv%(count)i.fk_stakeholder_id = s.id AND skv%(count)i.attributes ? '%(key)s')\n" % {"count": i, "key": variable}
sql["stakeholder"]["from"] = tables_from_inv
sql["stakeholder"]["where"] = where_inv
return sql