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