def get_all_resources(self, country_id=0, type_id=0, state_id=0):
"""
Fetches the most recent version for all the resources
for the given country and type.
@param: country_id: country id
@param: type_id: type id
@param: state_id: state id
"""
db_cxn = self.db_conn.session
db_cur = db_cxn.cursor(dictionary=True)
if int(country_id) <= 0 or int(type_id) <= 0:
return ([], [])
main = Main(self.db_conn)
type_name = main.get_type_name(type_id)
if not type_name:
return ([], [])
"""
parent_ids = select.read("History",
columns=["distinct(Parent_Plant_ID)"],
where=[["Country_ID", "=", country_id],
["and"],
["Type_ID", "=", type_id],
["and"], ["Accepted", "=", 1]
]
)
description_ids = select.read("History",
columns=["Parent_Plant_ID",
"Description_ID"],
where=[["Parent_Plant_ID",
"in",
[x[0] for x in parent_ids]]
]
)
"""
name_field = "Name_omit"
if type_id in self.types_with_segments:
name_field = "Name_of_this_Segment"
sql = "SELECT Parent_Plant_ID,Description_ID FROM History WHERE \
Parent_Plant_ID in \
(SELECT distinct(Parent_Plant_ID) \
FROM History \
WHERE Country_ID=%(country_id)s \
and Type_ID=%(type_id)s \
and Accepted=%(accepted)s \
) \
and Accepted=1;"
data = {
"country_id": country_id,
"type_id": type_id,
"accepted": 1
}
if state_id > 0:
sql = "SELECT Parent_Plant_ID,Description_ID FROM History WHERE \
Parent_Plant_ID in \
(SELECT distinct(Parent_Plant_ID) \
FROM History \
WHERE Country_ID=%(country_id)s \
and State_ID=%(state_id)s \
and Type_ID=%(type_id)s \
and Accepted=%(accepted)s \
) \
and Accepted=1;"
data = {
"country_id": country_id,
"type_id": type_id,
"state_id": state_id,
"accepted": 1
}
db_cur.execute(sql, data)
keys = ["Description_ID", "Name"]
select = Select(self.db_conn)
# get the latest description id for all resources
resources = {}
for did in db_cur:
if resources.get(did['Parent_Plant_ID'], 0) < int(did['Description_ID']):
resources[did['Parent_Plant_ID']] = did['Description_ID']
result = select.read(type_name + "_Description",
columns=["Description_ID", name_field],
where=[["Description_ID", "in",
list(resources.values())]
],
order_by=[name_field, "ASC"])
db_cxn.close()
return select.process_result_set(result)
class Main(object):
"""
Utility class for retrieving non-geo-resource centric data.
"""
def __init__(self, connection):
#self.connection = connection
self.select = Select(connection)
self.session = flask.session
def get_type_name(self, type_id):
"""
Returns the type name given a type id.
:@param type_id: the type id
:@returns type name as a string
"""
result = self.select.read("Type",
columns=["Type"],
where=[["Type_ID", "=", type_id]]
)
if result.with_rows:
return result.fetchone()['Type']
def get_type_id(self, type_name):
"""
Returns the type id or None given a type name.
:@param type_name: the type
:@returns type id or None
"""
try:
type_id = int(type_name)
#FIXME: check if the id is valid
# this is already an id, so return
return type_id
except ValueError:
pass
result = self.select.read("Type",
columns=["Type_ID"],
where=[["Type",
"like",
type_name]]
)
if result.with_rows:
return result.fetchone()['Type_ID']
def get_types(self, db_name):
"""
Returns all type ids, type name for the give db name.
:@param db_name: db name
:@returns List of tuples: [(type_ids, type),...]
"""
result = self.select.read("Type",
columns=["Type_ID", "Type"],
where=[["Database_Type", "like",
db_name]],
order_by=["Type_ID", "asc"]
)
return self.select.process_result_set(result)
def get_databases(self, type_id=None):
"""
Returns the database names for all types.
:@returns List of tuples: [(db_name),..]
"""
where = []
if type_id:
where.append(["Type_ID", "=", type_id])
result = self.select.read("Type",
columns=["distinct(Database_Type)"],
where=where
)
return self.select.process_result_set(result)
def get_country_name(self, country_id):
"""
Returns the country name given a country id.
:@param country_id: the country id
:@returns country name as a string
"""
result = self.select.read("Country",
columns=["Country"],
where=[["Country_ID", "=", country_id]]
)
if result.with_rows:
return result.fetchone()['Country']
def get_country_id_from_abbr(self, country_abbr):
"""
Returns the country_id or None for the five 3-letter abbr.
"""
result = self.select.read("Country",
columns=["Country_ID"],
where=[["Country_Code", "=",
country_abbr.lower()]])
if result.with_rows:
return result.fetchone()['Country_ID']
def get_country_id(self, country_name):
"""
Returns the country id or None given a country name.
:@param country_name: the country
:@returns country id or None
"""
try:
country_id = int(country_name)
#FIXME: check if the id is valid
# this is already an id, so return
return country_id
except ValueError:
pass
result = self.select.read("Country",
columns=["Country_ID"],
where=[["Country", "like",
country_name]]
)
if result.with_rows:
return result.fetchone()['Country_ID']
def get_types_for_country(self, country_id):
"""
Returns all the types for a given country
:@param country_id: country id or country name
:@returns Keys and values as a list of tuples:
['k1', 'k2'], [(type_id, type),..]
"""
type_ids = self.select.read("History",
columns=["distinct(Type_ID)"],
where=[["Country_ID", "=",
country_id],
["AND"],
["Accepted", "=", "1"]],
order_by=["Type_ID", "asc"])
keys = ['Type_ID', 'Type']
types = self.select.read("Country",
columns=keys,
where=[["Type_ID", "in",
[type_id[0] for type_id in type_ids]]]
)
return keys, [list(typ) for typ in types.fetchall()]
def get_countries(self, typ, new_resource=False):
"""
Returns all the countries that has data for the given type.
:@param typ: type id or type name
:@returns Keys and values as a list of tuples:
['k1', 'k2'], [(country_id, country),..]
"""
try:
type_id = int(typ)
except ValueError:
type_id = self.get_type_id(typ)
if not type_id:
return
keys = ["Country_ID", "Country"]
# if a new resource, return all the countries.
if new_resource:
countries = self.select.read("Country",
columns=["Country_ID", "Country"])
country_list = countries.fetchall()
return keys, [list(country) for country in country_list]
country_ids = self.select.read("History",
columns=["distinct(Country_ID)"],
where=[["Type_ID", "=", type_id]]
)
countries = self.select.read("Country",
columns=["Country_ID", "Country"],
where=[["Country_ID", "in",
[country_id["Country_ID"]
for country_id in country_ids]]]
)
#print(countries)
#country_list = countries.fetchall()
return self.select.process_result_set(countries)
def get_states(self, country, new_resource=False):
"""
Returns all the states for a country.
:@param country: country name or id
:@returns List of tuples [(state_id, state), ...]
"""
try:
country_id = int(country)
except ValueError:
country_id = self.get_country_id(country)
if not country_id:
return
result = self.select.read("State",
columns=["State_ID", "State"],
where=[["Country_ID", "=", country_id]],
order_by=["State", "asc"]
)
return self.select.process_result_set(result)
def get_state_name(self, state_id):
"""
Returns the state name given a state id.
:@param country_id: the state id
:@returns state name as a string
"""
result = self.select.read("State",
columns=["State"],
where=[["State_ID", "=", state_id]]
)
if result.with_rows:
return result.fetchone()['State']
def get_user_pref(self):
"""
Return the user preferences for the search parameters.
"""
return (self.session.get('pref_db_type', 'powerplants'),
self.session.get('pref_type', '1'),
self.session.get('pref_country',
self.get_first_country_for_type(
self.session.get('pref_type')
)
),
self.session.get('pref_state', '0')
)
def get_first_country_for_type(self, type_id):
"""
Returns the first country for the requested type.
Used in setting default user pref.
"""
return str(10)
def store_user_pref(self, db_type, country, typ, state):
"""
Store user choices for country, type, etc in cookies
for easy loading default url params when loading.
"""
self.session['pref_country'] = str(country)
self.session['pref_type'] = str(typ)
self.session['pref_db_type'] = db_type
self.session['pref_state'] = str(state)
return
def make_html_user_pref(self):
"""
Makes html hidden elements with user pref values for client
side js processing.
"""
prefs = self.get_user_pref()
html = []
html.extend(["<input type='hidden' id='user_pref_db_type' name='user_pref_db_type'",
"value='", prefs[0], "' />"])
html.extend(["<input type='hidden' id='user_pref_country' name='user_pref_country'",
"value='", prefs[2], "' />"])
html.extend(["<input type='hidden' id='user_pref_type' name='user_pref_type'",
"value='", prefs[1], "' />"])
html.extend(["<input type='hidden' id='user_pref_state' name='user_pref_state'",
"value='", prefs[3], "' />"])
return "".join(html)
