def view():
type_id = flask.request.args.get("type_id", 0)
country_id = flask.request.args.get("country_id", 0)
#state_id = flask.request.args.get("state_id", 0)
if not type_id and not country_id:
flask.abort(404)
where = [["Country_ID", "=", country_id]]
columns = []
if int(type_id) > 0:
where.append(["AND"])
where.append(["Type_ID", "=", type_id])
else:
columns.extend(["Type_ID", "Country_ID", "Number_of_Plants", "Cumulative_Capacity"])
select = Select(db)
result = select.read("metadata", columns=columns, where=where, dict_cursor=False)
keys = result.column_names
values = []
if not result.with_rows:
return flask.jsonify({'keys': keys, 'values': values})
rows = result.fetchall()
for r in rows[0]:
r = r.decode("utf8")
values.append(json.loads(r))
#values = [json.loads(str(r)) for r in rows]
#keys, values = select.process_result_set(result)
data = {'keys': keys, 'values': values}
return flask.jsonify(data)
def get_resources_to_moderate(self):
"""
Returns a list of resources awaiting moderation.
Will return both new and edited resources.
"""
select = Select(self.db_conn)
description_ids = select.read("History",
columns=[
"Parent_Plant_ID",
"Description_ID"
],
where=[["Moderated", "=", "0"]],
order_by=["Description_ID", "desc"]
)
main = Main(self.db_conn)
new_submits = []
edits = []
for description_id in description_ids:
if not description_id['Parent_Plant_ID']:
#TODO: this should not happen. all versions shd have a parent. error from old code?
continue
geo_resource = GeoResource(self.db_conn, description_id['Description_ID'])
type_id = geo_resource.type_id
country_id = geo_resource.country_id
type_name = main.get_type_name(type_id)
country_name = main.get_country_name(country_id)
geo_name = geo_resource.get_resource_name(type_name=type_name)
if description_id['Description_ID'] == description_id['Parent_Plant_ID']:
new_submits.append({
'type_name': type_name,
'country_name': country_name,
'geo_name': geo_name,
'description_id': str(description_id['Description_ID'])
})
else:
edits.append({
'type_name': type_name,
'country_name': country_name,
'geo_name': geo_name,
'description_id': str(description_id['Description_ID'])
})
return new_submits, edits
def __init__(self, connection, description_id,
type_id=None, country_id=None, state_id=None):
"""
The primitive class for all geo resources.
"""
#Html.__init__(self)
if not description_id or int(description_id) == 0:
if not type_id and not country_id and not state_id:
raise AttributeError("Invalid request. Necessary details were not provided.")
self.description_id = description_id
self.connection = connection
self.type_id = type_id
self.country_id = country_id
self.state_id = state_id
self.parent_plant_id = 0
self.latest_revision_id = 0
self.is_moderated = 0
self.name = ""
self.select = Select(self.connection)
self.main = Main(self.connection)
if not self.type_id or not self.country_id or not self.state_id:
self.__get_ids()
self.type_name = self.main.get_type_name(self.type_id)
self.types_with_segments = [19, 20, 24, 25, 26, 27]
def get_resources_to_moderate(self):
"""
Returns a list of resources awaiting moderation.
Will return both new and edited resources.
"""
select = Select(self.db_conn)
description_ids = select.read(
"History",
columns=["Parent_Plant_ID", "Description_ID"],
where=[["Moderated", "=", "0"]],
order_by=["Description_ID", "desc"],
)
main = Main(self.db_conn)
new_submits = []
edits = []
for description_id in description_ids:
geo_resource = GeoResource(self.db_conn, description_id["Description_ID"])
type_id = geo_resource.type_id
country_id = geo_resource.country_id
type_name = main.get_type_name(type_id)
country_name = main.get_country_name(country_id)
geo_name = geo_resource.get_resource_name(type_name=type_name)
if description_id["Description_ID"] == description_id["Parent_Plant_ID"]:
new_submits.append(
{
"type_name": type_name,
"country_name": country_name,
"geo_name": geo_name,
"description_id": str(description_id["Description_ID"]),
}
)
else:
edits.append(
{
"type_name": type_name,
"country_name": country_name,
"geo_name": geo_name,
"description_id": str(description_id["Description_ID"]),
}
)
return new_submits, edits
def view(did=None):
if not did:
did = flask.request.args.get("did", "")
resource = GeoResource(db, description_id=did)
select = Select(db)
result = select.read("Associated_Infrastructure",
where=[["Parent_Plant_ID",
"=",
resource.parent_plant_id]]
)
keys = result.column_names
values = result.fetchall()
html = []
html.append("<ul>")
for value in values:
ai_parent_plant_id = value["Associated_Parent_Plant_ID"]
ai_res = GeoResource(db, description_id=ai_parent_plant_id)
html.append("<li>")
html.append("<b><a href=\"/geoid/%s\" target=\"_blank\">%s</a></b><br/>" % (ai_res.get_latest_revision_id(),
ai_res.get_resource_name()))
html.append("</li>")
html.append("</ul>")
"""
html.append('<div id="searchAI" class="ai-search-module">')
html.append("<div id='aiDatabase_Type' class='aiSelectable'></div>")
html.append("<div id='aiType' class='aiSelectable'></div>")
html.append("<div id='aiCountry' class='aiSelectable'></div>")
html.append("<div id='aiState' class='aiSelectable'></div>")
html.append("<div class='aiUpdateButton' id='aiUpdateButton' style='padding-top: 10px;'>")
html.append("<button id='createAIResource' class='createAIResource'>Add Associated Infrastructure</button>")
html.append("</div>")
html.append("</div>")
html.append("<div id='aiResources' class='aiSelectable' style='top: 20px;'>")
html.append("</div>")
"""
return "".join(html)
def __init__(self):
self.conn = connection.Db().session
self.main = Main(connection.Db())
self.moderation = Moderation(connection.Db())
self.select = Select(connection.Db())
self.perf_fields = ("_Performance", "Year_yr",
["Total_Gigawatt_Hours_Generated_nbr", "CO2_Emitted_(Tonnes)_nbr"])
self.unit_fields = ("_Unit_Description", "Date_Commissioned_dt", "Capacity_(MWe)_nbr")
self.alt_types = [4, 7, 8, 10]
self.alt_perf_fields = ("_Performance", "Year_yr",
["Total_Gigawatt_Hours_Generated_nbr", "Plant_Load_Factor_(%)_nbr"])
self.nuclear_perf_fields = ("_Performance", "Year_yr",
["Plant_Load_Factor_(%)_nbr"])
self.nuclear_ghg_fields = ("_Gigawatt_Hours_Generated", "Year_yr",
["Gigawatt_Hours_Generated_nbr"])
self.wind_unit_fields = ("_Unit_Description", "Date_Commissioned_dt",
"Peak_MWe_per_Turbine_nbr", "Number_Of_Turbines_nbr")
def insert_dual_node_description(self, table_name, form_data, description_id,
station_1_id, station_2_id, connection_id):
"""
for dual node descriptions
"""
sql_values = {}
sql_fields = []
alt_sql_fields = []
alt_sql_statement = []
sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id,"
"Station_1_ID=:station_1_id,"
"Station_2_ID=:station_2_id,"
"Connection_ID=:connection_id")
sql_values['description_id'] = description_id
alt_sql_fields.append("`Description_ID`=%(description_id)s")
sql_values['station_1_id'] = station_1_id
alt_sql_fields.append("`Station_1_ID`=%(station_1_id)s")
sql_values['station_2_id'] = station_2_id
alt_sql_fields.append("`Station_2_ID`=%(station_2_id)s")
sql_values['connection_id'] = connection_id
alt_sql_fields.append("`Connection_ID`=%(connection_id)s")
select = Select(self.db)
column_names = select.read_column_names(table_name)
print(form_data)
for k in column_names:
if not form_data.get(k[0]):
continue
value = form_data.get(k[0])
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`='%(" + key.lower() + ")s'")
sql_values[key.lower()] = value.strip()
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor()
try:
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
except Exception:
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = re.sub(r"(\d+)%", "\g<1>##", sql_stmt)
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = re.sub(r"(\d+)##", "\g<1>%", sql_stmt)
#print(sql_stmt)
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
def view(country_id=None, country_abbr=None):
main = Main(db)
pref = main.get_user_pref()
if not country_id and not country_abbr:
url = "/".join(["/summary/country", pref[2]])
#url = main.get_search_redirect_url("summary/country",
# return_type='country_summary')
return flask.redirect(url)
elif country_abbr:
country_id = main.get_country_id_from_abbr(country_abbr)
return flask.redirect("/summary/country/" + str(country_id))
country_name = main.get_country_name(country_id)
modules = []
select = Select(db)
res = select.read("metadata",
columns=["Type_ID",
"Number_of_Plants",
"Cumulative_Capacity_Total"],
where=[["Country_ID", "=", country_id]],
order_by=["Type_ID", "asc"])
types = res.fetchall()
module_content = """
<table style="width: 90%">
<tr>
<td style="width: 70%">Number of {type_name} {db}:</td>
<td>{total}</td>
</tr>
<tr>
<td style="width: 70%">Total Cumulative Capacity (MWe):</td>
<td>{cumulative_capacity}</td>
</tr>
<tr>
<td style="width: 70%">Map All {type_name} {db}:</td>
<td>{map_link}</td>
</tr>
<tr>
<td style="width: 70%">List All Units in Individual Plants</td>
<td>{all_units_link}</td>
</tr>
<tr>
<td style="width: 70%">Complete summary:</td>
<td>{summary_link}</td>
</tr>
</table>
<input type='hidden' id='{pie_id}' class='pie_chart_values' value='{pie_value}' />
"""
module = {}
module['heading'] = "Installed Capacity by Category"
module['content'] = []
module['content'].append("<p style='text-align: center'><b>Total Cumulative Capacity: %s MWe</b></p>"
% sum([t['Cumulative_Capacity_Total'] for t in types]))
module['content'].extend(["<div id='pie_chart'>",
"<div id='pie_chart_1'>",
"<div id='pie_chart_1_title' style='text-align: center'>",
"<b>Traditional Energy Sources</b></div>",
"</div>",
"<div id='pie_chart_2'>",
"<div id='pie_chart_2_title' style='text-align: center'>",
"<b>Renewable Energy Sources</b></div>",
"</div>",
"</div>"])
module['content'] = "".join(module['content'])
modules.append(module)
#keys, types = main.get_types_for_country(country_id)
for t in types:
type_name = main.get_type_name(t['Type_ID']).replace("_", " ")
db_name = main.get_databases(type_id=t['Type_ID'])[1][0][0]
dbn = ""
if db_name == "PowerPlants":
dbn = "power plants"
else:
dbn = ""
s_link = []
s_link.append("<a href='/summary/type/")
s_link.append(str(t['Type_ID']))
s_link.append("/" + str(country_id))
s_link.append("'>Click Here</a>")
map_link = []
map_link.append("<a href='/map/")
map_link.append(db_name.lower() + "/")
map_link.append(str(t['Type_ID']) + "/")
map_link.append(str(country_id) + "/0/")
map_link.append("'>Map</a>")
all_units = []
all_units.append("<a href='/allunits/")
all_units.append(pref[0] + "/")
all_units.append(str(t['Type_ID']) + "/")
all_units.append(str(country_id))
all_units.append("'>All Units</a>")
module = {}
module['heading'] = type_name
module['content'] = module_content.format(type_name=type_name,
db=dbn,
total=t['Number_of_Plants'],
cumulative_capacity=t['Cumulative_Capacity_Total'],
summary_link="".join(s_link),
map_link="".join(map_link),
all_units_link="".join(all_units),
pie_id=type_name,
pie_value=t['Cumulative_Capacity_Total'])
modules.append(module)
main.store_user_pref(pref[0], country_id, pref[1], pref[3])
user_pref = main.make_html_user_pref()
title = "Summary for " + country_name
return flask.render_template("country_summary.html",
modules=modules, title=title,
country=country_name,
user_pref=user_pref,
body_onload="CountrySummary.init()")
def __insert_enum_table(self, table_name, form_data, description_id):
"""
Inserts a row for spl cases where the label is read from enum
and a text box is provided for the value. Like Gas field contaminants.
"""
type_name = form_data.get("Type_Name")
label = table_name.replace(type_name + "_", "")
select = Select(self.db)
column_names = select.read_column_names(table_name)
#enum_result = select.read_column_names(table_name, where=label)
enum_value = column_names[1][1].replace("enum(", "")
enum_value = enum_value[:-1]
enum = []
replace_comma = False
for val in enum_value:
if val == "'":
replace_comma = True if not replace_comma else False
if replace_comma and val == ',':
val = "@"
enum.append(val)
enum = "".join(enum)
sql_values = {}
sql_fields = []
sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id")
sql_values['description_id'] = description_id
for option in enum.split(","):
option = option.replace("'", "")
option = option.replace("@", ",")
print(option, label+"_"+option)
value = form_data.get(label+"_"+option).strip()
if value:
sql_fields.append("`" + label + "`=%(" + label.lower() + ")s")
sql_values[label.lower()] = option
sql_fields.append("`" + column_names[2][0] + "`=%(" + column_names[2][0].lower() + ")s")
sql_values[column_names[2][0].lower()] = value
sql_statement.append(",".join(sql_fields))
print("".join(sql_statement), sql_values)
session = self.db_conn.cursor()
try:
session.execute("".join(sql_statement), sql_values)
self.db_conn.commit()
except Exception as e:
print(e)
finally:
session.close()
return 1
def __insert_nuclear_performance_data(self, table_name, form_data, description_id):
select = Select(self.db)
column_names = select.read_column_names(table_name)
number_of_units = form_data.get("numberOfNuclear_Unit_Description", 0)
if int(number_of_units) == 0:
return 0
unit_result = select.read("Nuclear_Unit_Description",
columns=["Unit_Description_ID"],
where=[["Description_ID",
"=",
description_id]]
)
unit_ids = unit_result.fetchall()
start_decade = 1950
end_decade = 2020
for r, unit_id in enumerate(unit_ids):
row_num = r + 1
unit_id = unit_id[0]
for year in range(start_decade, end_decade):
sql_values = {}
sql_statement = []
sql_fields = []
alt_sql_statement = []
alt_sql_fields = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id")
alt_sql_fields.append("`Description_ID`=%(description_id)s")
sql_values['description_id'] = description_id
sql_fields.append("`Unit_Description_ID`=:unit_description_id")
alt_sql_fields.append("`Unit_Description_ID`=%(unit_description_id)s")
sql_values['unit_description_id'] = unit_id
sql_fields.append("`Year_yr`=:year_yr")
alt_sql_fields.append("`Year_yr`=%(year_yr)s")
sql_values['year_yr'] = year
for k in column_names:
field_name = k[0] + "_" + str(row_num) + "_###_" + str(year)
if not form_data.get(field_name) or form_data.get(field_name) == "None" or form_data.get(field_name) == "":
continue
value = form_data.get(field_name)
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`='%(" + key.lower() + ")s'")
sql_values[key.lower()] = value.strip()
if len(sql_values) <= 3:
continue
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor()
try:
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
except Exception:
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = sql_stmt.replace("%_", "##_")
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = sql_stmt.replace("##_", "%_")
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
def __insert_row_column_module(self,
table_name,
form_data,
description_id):
"""
Handles multiple column modules like unit_description.
"""
select = Select(self.db)
column_names = select.read_column_names(table_name)
number_of_rows = form_data.get("numberOf" + table_name, 0)
if int(number_of_rows) == 0:
return 0
for row_num in range(1, int(number_of_rows)+1):
sql_values = {}
sql_statement = []
sql_fields = []
alt_sql_statement = []
alt_sql_fields = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id")
alt_sql_fields.append("`Description_ID`=%(description_id)s")
sql_values['description_id'] = description_id
for k in column_names:
field_name = k[0] + "_###_" + str(row_num)
if not form_data.get(field_name):
continue
value = form_data.get(field_name)
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`=%(" + key.lower() + ")s")
sql_values[key.lower()] = value.strip()
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor(raw=True)
try:
#print("".join(alt_sql_statement) % sql_values)
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
print("SQL: " + session.statement)
except Exception as e:
print("ERROR: " + str(e))
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = sql_stmt.replace("%_", "##_")
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = sql_stmt.replace("##_", "%_")
print("ALT SQL:")
print(sql_stmt)
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
def __insert_performance_module(self,
table_name,
form_data,
description_id):
"""
Handles performance data.
"""
start_decade = 1950
end_decade = 2020
table_name = table_name.replace("_Annual", "")
select = Select(self.db)
column_names = select.read_column_names(table_name)
for year in range(start_decade, end_decade):
sql_values = {}
sql_fields = []
alt_sql_fields = []
sql_statement = []
alt_sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.extend(["`Description_ID`=:description_id",
"`Year_yr`=:year_yr"])
alt_sql_fields.extend(["`Description_ID`=%(description_id)s",
"`Year_yr`=%(year_yr)s"])
sql_values['description_id'] = description_id
sql_values['year_yr'] = year
for k in column_names:
field_name = k[0] + "_###_" + str(year)
if not form_data.get(field_name):
continue
value = form_data.get(field_name)
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`='%(" + key.lower() + ")s'")
sql_values[key.lower()] = value.strip()
if len(sql_values) == 0:
continue
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor()
try:
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
except Exception:
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
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)
class Metadata(object):
"""
Computes the values and updates the metadata table.
This table provides summary info for the views.
"""
def __init__(self):
self.conn = connection.Db().session
self.main = Main(connection.Db())
self.moderation = Moderation(connection.Db())
self.select = Select(connection.Db())
self.perf_fields = ("_Performance", "Year_yr",
["Total_Gigawatt_Hours_Generated_nbr", "CO2_Emitted_(Tonnes)_nbr"])
self.unit_fields = ("_Unit_Description", "Date_Commissioned_dt", "Capacity_(MWe)_nbr")
self.alt_types = [4, 7, 8, 10]
self.alt_perf_fields = ("_Performance", "Year_yr",
["Total_Gigawatt_Hours_Generated_nbr", "Plant_Load_Factor_(%)_nbr"])
self.nuclear_perf_fields = ("_Performance", "Year_yr",
["Plant_Load_Factor_(%)_nbr"])
self.nuclear_ghg_fields = ("_Gigawatt_Hours_Generated", "Year_yr",
["Gigawatt_Hours_Generated_nbr"])
self.wind_unit_fields = ("_Unit_Description", "Date_Commissioned_dt",
"Peak_MWe_per_Turbine_nbr", "Number_Of_Turbines_nbr")
def get_unit_data(self, typ, plants):
# units
results = self.select.read(typ[1] + self.unit_fields[0],
columns=["`" + self.unit_fields[1] + "`",
"`" + self.unit_fields[2] + "`", "Description_ID"],
where=[["Description_ID", "in",
[plant[0] for plant in plants]]],
dict_cursor=False)
units = results.fetchall()
cumulative_capacity_total = 0
cumulative_capacity = {}
new_capacity = {}
new_capacity['keys'] = [self.unit_fields[1], self.unit_fields[2]]
cumulative_capacity['keys'] = [self.unit_fields[1], self.unit_fields[2]]
cap_values = {}
cum_cap_values = {}
for unit in units:
if not unit[1] or not unit[0]:
continue
unit_val = math.ceil(float(unit[1]))
cumulative_capacity_total += unit_val
year = unit[0].decode("utf8").split("-")[0]
val = cap_values.get(year, -1)
if val == -1:
cap_values[year] = 0
cap_values[year] += unit_val
prev_year = 0
for year in sorted(cap_values.keys()):
cum_cap_values[year] = cap_values[year] + cum_cap_values.get(prev_year, 0)
prev_year = year
new_capacity['values'] = sorted([list(values) for values in
zip(cap_values.keys(),
cap_values.values())]
)
cumulative_capacity['values'] = sorted([list(values) for values in
zip(cum_cap_values.keys(),
cum_cap_values.values())]
)
return cumulative_capacity_total, cumulative_capacity, new_capacity
def get_wind_unit_data(self, typ, plants):
# units
results = self.select.read(typ[1] + self.wind_unit_fields[0],
columns=["`" + self.wind_unit_fields[1] + "`",
"`" + self.wind_unit_fields[2] + "`",
"`" + self.wind_unit_fields[3] + "`"],
where=[["Description_ID", "in",
[plant[0] for plant in plants]]],
dict_cursor=False)
units = results.fetchall()
cumulative_capacity_total = 0
new_capacity = {}
new_capacity['keys'] = [self.unit_fields[1], self.unit_fields[2]]
cap_values = {}
for unit in units:
if not unit[1] or not unit[0]:
continue
unit_val = math.ceil(float(unit[1]))
cumulative_capacity_total += unit_val
year = unit[0].decode("utf8").split("-")[0]
val = cap_values.get(year, -1)
if val == -1:
cap_values[year] = 0
if unit[2] and float(unit[2]):
cap_values[year] += unit_val * math.ceil(float(unit[2]))
cum_cap_values = {}
prev_year = 0
for year in sorted(cap_values.keys()):
cum_cap_values[year] = cap_values[year] + cum_cap_values.get(prev_year, 0)
prev_year = year
new_capacity['values'] = sorted([list(values) for values in
zip(cap_values.keys(),
cap_values.values())])
cumulative_capacity = {}
cumulative_capacity['values'] = sorted([list(values) for values in
zip(cum_cap_values.keys(),
cum_cap_values.values())]
)
return cumulative_capacity_total, cumulative_capacity, new_capacity
def get_general_performance_data(self, typ, plants):
# performance
cols = [self.perf_fields[1]]
if typ[0] in self.alt_types:
cols.extend(["`" + p_col + "`" for p_col in self.alt_perf_fields[2]])
else:
cols.extend(["`" + p_col + "`" for p_col in self.perf_fields[2]])
results = self.select.read(typ[1] + self.perf_fields[0],
columns=cols,
where=[["Description_ID", "in",
[plant[0] for plant in plants]]],
dict_cursor=False)
performances = results.fetchall()
co2 = {}
ghg = {}
for perf in performances:
year = perf[0].decode("utf8")
if perf[1]:
g_val = ghg.get(year, -1)
if g_val == -1:
ghg[year] = 0
ghg[year] += math.ceil(float(perf[1]))
if perf[2]:
c_val = co2.get(year, -1)
if c_val == -1:
co2[year] = 0
co2[year] += math.ceil(float(perf[2]))
# for plotting purposes, the years in both the dicts must
# be the same. so adding zeros to missing years.
for year in co2.keys():
val = ghg.get(year, -1)
if val == -1:
ghg[year] = 0
for year in ghg.keys():
val = co2.get(year, -1)
if val == -1:
co2[year] = 0
ghg_values = {"keys": [self.perf_fields[1], self.perf_fields[2][0]],
"values": sorted([list(values)
for values in zip(ghg.keys(),
ghg.values())]
)}
co2_values = {"keys": [self.perf_fields[1], self.perf_fields[2][1]],
"values": sorted([list(values) for values in
zip(co2.keys(), co2.values())])}
return ghg_values, co2_values
def get_nuclear_performance_data(self, typ, plants):
# performance
cols = [self.perf_fields[1]]
cols.append("`" + self.nuclear_ghg_fields[2][0] + "`")
ghg_res = self.select.read(typ[1] + self.nuclear_ghg_fields[0],
columns=cols,
where=[["Description_ID", "in",
[plant[0] for plant in plants]]],
dict_cursor=False)
ghg_perf = ghg_res.fetchall()
cols = [self.perf_fields[1]]
cols.append("`" + self.nuclear_perf_fields[2][0] + "`")
reg_res = self.select.read(typ[1] + self.nuclear_perf_fields[0],
columns=cols,
where=[["Description_ID", "in",
[plant[0] for plant in plants]]],
dict_cursor=False)
reg_perf = reg_res.fetchall()
co2 = {}
ghg = {}
for perf in ghg_perf:
year = perf[0].decode("utf8")
if perf[1]:
g_val = ghg.get(year, -1)
if g_val == -1:
ghg[year] = 0
ghg[year] += math.ceil(float(perf[1]))
for perf in reg_perf:
if perf[1]:
c_val = co2.get(year, -1)
if c_val == -1:
co2[year] = 0
co2[year] += math.ceil(float(perf[1]))
# for plotting purposes, the years in both the dicts must
# be the same. so adding zeros to missing years.
for year in co2.keys():
val = ghg.get(year, -1)
if val == -1:
ghg[year] = 0
for year in ghg.keys():
val = co2.get(year, -1)
if val == -1:
co2[year] = 0
ghg_values = {"keys": [self.nuclear_ghg_fields[1], self.nuclear_ghg_fields[2][0]],
"values": sorted([list(values) for values in
zip(ghg.keys(), ghg.values())]
)}
co2_values = {"keys": [self.nuclear_perf_fields[1], self.nuclear_perf_fields[2][0]],
"values": sorted([list(values) for values in
zip(co2.keys(), co2.values())]
)}
return ghg_values, co2_values
def compute(self):
t_keys, types = self.main.get_types("PowerPlants")
#types = [[1, "Coal"]]
session = self.conn.cursor()
for typ in types:
c_keys, countries = self.main.get_countries(typ[0])
#countries = [[38, "Canada"]]
for country in countries:
p_keys, plants = self.moderation.get_all_resources(country_id=country[0], type_id=typ[0])
# unit data
if typ[0] == 10:
cumulative_capacity_total, cumulative_capacity, new_capacity = self.get_wind_unit_data(typ, plants)
else:
cumulative_capacity_total, cumulative_capacity, new_capacity = self.get_unit_data(typ, plants)
# perf data
if typ[0] == 5:
ghg, co2 = self.get_nuclear_performance_data(typ, plants)
else:
ghg, co2 = self.get_general_performance_data(typ, plants)
sql = "INSERT INTO metadata SET Country_ID=%(country_id)s, \
Type_ID=%(type_id)s, Number_of_Plants=%(number_of_plants)s, \
Cumulative_Capacity=%(cum_cap)s, \
Cumulative_Capacity_Total=%(cum_cap_tot)s, New_Capacity_Added=%(new_cap)s, \
Annual_Gigawatt_Hours_Generated=%(ghg)s, Annual_CO2_Emitted=%(co2)s"
session.execute(sql, {
"country_id": country[0],
"type_id": typ[0],
"number_of_plants": str(len(plants)),
"cum_cap": json.dumps(cumulative_capacity),
"cum_cap_tot": str(cumulative_capacity_total),
"new_cap": json.dumps(new_capacity),
"ghg": json.dumps(ghg),
"co2": json.dumps(co2)
})
self.conn.commit()
session.close()
class GeoResource(object):
"""
Defines an energy resource.
"""
parent_plant_id = 0
type_id = 0
country_id = 0
state_id = 0
description_id = 0
def __init__(self, connection, description_id,
type_id=None, country_id=None, state_id=None):
"""
The primitive class for all geo resources.
"""
#Html.__init__(self)
if not description_id or int(description_id) == 0:
if not type_id and not country_id and not state_id:
raise AttributeError("Invalid request. Necessary details were not provided.")
self.description_id = description_id
self.connection = connection
self.type_id = type_id
self.country_id = country_id
self.state_id = state_id
self.parent_plant_id = 0
self.latest_revision_id = 0
self.is_moderated = 0
self.name = ""
self.select = Select(self.connection)
self.main = Main(self.connection)
if not self.type_id or not self.country_id or not self.state_id:
self.__get_ids()
self.type_name = self.main.get_type_name(self.type_id)
self.types_with_segments = [19, 20, 24, 25, 26, 27]
def __get_ids(self):
"""
Private class, gets the necessary ids from the History
table for a resource.
"""
result = self.select.read("History",
where=[["Description_ID", "=",
self.description_id]])
if result.rowcount == 0:
raise LookupError("Description ID %s does not exist." %
self.description_id)
ids = result.fetchone()
self.type_id = ids['Type_ID']
self.country_id = ids['Country_ID']
self.state_id = ids['State_ID']
self.parent_plant_id = ids['Parent_Plant_ID']
self.is_moderated = ids['Moderated']
def get_latest_revision_id(self, moderated=True):
"""
Get the latest revision id for this resource.
"""
if self.latest_revision_id > 0:
return self.latest_revision_id
if self.description_id == 0:
# new plant creation
return None
if self.parent_plant_id == 0:
self.__get_ids()
where = [["Parent_Plant_ID", "=", self.parent_plant_id]]
if moderated:
where.extend([["and"], ["Accepted", "=", "1"]])
ids = self.select.read("History", columns=["max(Description_ID)"],
where=where)
res = ids.fetchone()
print(res)
self.latest_revision_id = res.get("max(Description_ID)")
return self.latest_revision_id
def get_resource_name(self, type_name=None):
"""
Get the name of the resource. Requires type name to be passed
as the name is in the description table.
"""
if self.description_id == 0:
return None
if self.name:
return self.name
if not type_name:
type_name = self.type_name
name_field = "Name_omit"
if self.type_id in self.types_with_segments:
name_field = "Name_of_this_Segment"
desc_table = type_name + "_Description"
desc = self.select.read(desc_table,
columns=[name_field],
where=[["Description_ID", "=",
self.get_latest_revision_id(moderated=False)]]
)
self.name = desc.fetchone().get(name_field)
return self.name
def view():
request = flask.request
if not request.form:
# FIXME: ERROR
flask.abort(406)
return
form_data = request.form.copy()
insert = InsertFactSheet(db)
#description_id = insert.insert("History", form_data, "history")
#insert.insert("Coal_Description", form_data, "generic", description_id)
result = ""
old_description_id = form_data.get("Description_ID")
parent_plant_id = 0
if int(old_description_id) > 0:
geo_resource = GeoResource(db, old_description_id)
parent_plant_id = geo_resource.parent_plant_id
form_data["Moderated"] = "0"
form_data["Moderator_ID"] = "0"
form_data["Accepted"] = "0"
form_data["User_ID"] = flask.session['user_id']
form_data["Parent_Plant_ID"] = str(parent_plant_id)
description_id = insert.insert("History", form_data, "history")
#form_data["Description_ID"] = description_id
geo_resource = GeoResource(db, description_id)
if not geo_resource:
return flask.abort(404)
select = Select(db)
res_modules = select.read("Type_Features",
where=[["Type_ID", "=", geo_resource.type_id]]
)
modules = res_modules.fetchone()
print(modules)
features = list(modules['Features'])
features.remove("Annual_Performance")
features.append("Annual_Performance")
for f in features:
table_name = geo_resource.type_name + "_" + f
if f.startswith("Unit_Description") or\
f.startswith("Environmental_Issues") or \
f.startswith("Comments") or \
f.startswith("References") or \
f.startswith("Upgrades"):
insert.insert(table_name, form_data, "row_columns", description_id)
elif f == "Annual_Performance":
if geo_resource.type_id == 5:
cap_gen_table_name = "Nuclear_Capacity_Generated"
gwh_table_name = "Nuclear_Gigawatt_Hours_Generated"
insert.insert(cap_gen_table_name, form_data, "nuclear_performance", description_id)
insert.insert(gwh_table_name, form_data, "nuclear_performance", description_id)
insert.insert(table_name, form_data, "performance", description_id)
elif f == "Location":
insert.insert(table_name, form_data, "generic", description_id)
insert.insert(geo_resource.type_name + "_Overlays", form_data, "row_columns", description_id)
elif f == "Identifiers" or f == "Dual_Node_Identifiers" or f == "Dual_Node_Locations":
continue
elif f == "History":
continue
elif f == "Owner_Details":
insert.insert(geo_resource.type_name + "_Owners", form_data, "row_columns", description_id)
insert.insert(table_name, form_data, "generic", description_id)
elif f == "Description":
insert.insert(table_name, form_data, "generic", description_id)
if geo_resource.type_id == 11:
insert.insert(geo_resource.type_name + "_Contaminants", form_data, "enum_table", description_id)
elif f == "Dual_Node_Description":
connection_id = insert.insert(geo_resource.type_name + "_Connection_Description",
form_data, "generic")
station_1_id = insert.insert(geo_resource.type_name + "_Station_Description",
form_data, "generic", dual=1)
station_2_id = insert.insert(geo_resource.type_name + "_Station_Description",
form_data, "generic", dual=2)
print(connection_id, station_1_id, station_2_id)
insert.insert_dual_node_description(geo_resource.type_name + "_Description", form_data, description_id,
station_1_id, station_2_id, connection_id)
elif f != "Associated_Infrastructure":
insert.insert(table_name, form_data, "generic", description_id)
#return flask.render_template("form.html", modules=html, title=title)
flask.flash("Your changes were successfully submitted and is awaiting moderation.<br/>\
Link to <a href='" +
flask.url_for('form.factsheet', description_id=old_description_id) +
"' target='_blank'>Original FactSheet</a>", "warning")
return flask.redirect(flask.url_for('form.factsheet', description_id=description_id))
def __init__(self, connection):
self.connection = connection
self.select = Select(self.connection)
self.main = Main(self.connection)
class Location(object):
"""
A class for all the location information.
"""
def __init__(self, connection):
self.connection = connection
self.select = Select(self.connection)
self.main = Main(self.connection)
def __get_lat_lng(self, table_name, description_id):
"""
A private class to get lat and lng for a resource.
"""
result = self.select.read(table_name,
where=[["Description_ID",
"=",
description_id]]
)
if not result.with_rows:
return (None, None)
loc = result.fetchone()
lat = loc['Latitude_Start']
lng = loc['Longitude_Start']
lat_end = loc['Latitude_End']
lng_end = loc['Longitude_End']
return (lat, lng), (lat_end, lng_end)
def for_one_resource(self, description_id):
"""
Returns the location for a resource.
:@param description_id
:@returns dict: {lat, lng, overlays}
"""
gresource = GeoResource(self.connection, description_id)
type_id = gresource.type_id
type_name = self.main.get_type_name(type_id)
(lat, lng), (lat_end, lng_end) = self.__get_lat_lng(type_name + "_Location", description_id)
overlay_result = self.select.read(type_name + "_Overlays",
where=[["Description_ID",
"=",
description_id]]
)
o_results = overlay_result.fetchall()
overlays = []
for overlay in o_results:
details = {}
details['color'] = overlay['Color']
details['weight'] = overlay['Weight']
details['opacity'] = overlay['Opacity']
details['points'] = overlay['Points']
details['numLevels'] = overlay['Num_Levels']
details['zoomFactor'] = overlay['Zoom_Factor']
details['overlayType'] = overlay['Overlay_Type']
details['overlayName'] = overlay['Overlay_Name']
overlays.append(details)
locations = {}
locations['lat'] = lat
locations['lng'] = lng
if lat_end and lng_end:
locations['lat_end'] = lat_end
locations['lng_end'] = lng_end
locations['name'] = gresource.get_resource_name(type_name)
locations['overlays'] = overlays
return {"locations": [locations]}
def for_many_resources(self, country_id=None, type_id=None):
"""
Returns the location information for all the resources
in a country for the type.
:@param country: the country id
:@param typ: type id
"""
if int(country_id) <= 0 or int(type_id) <= 0:
return {}
moderation = Moderation(self.connection)
keys, values = moderation.get_all_resources(country_id=country_id, type_id=type_id)
del keys
table_name = self.main.get_type_name(type_id) + "_Location"
loc = []
locations = self.select.read(table_name,
columns=["Description_ID",
"Latitude_Start",
"Longitude_Start"],
where=[["Description_ID",
"in",
[value[0] for value in values]]],
dict_cursor=False)
locations = locations.fetchall()
for value in values:
for location in locations:
if value[0] == int(location[0].decode("utf8")):
loc.append([location[1].decode("utf8"),
location[2].decode("utf8"),
value[1]
])
"""
lat = loc['Latitude_Start']
lng = loc['Longitude_Start']
for value in values:
desc_id = value[0]
name = value[1]
lat, lng = self.__get_lat_lng(table_name, desc_id)
loc.append([lat, lng, name])
"""
return {"locations": loc, "boundLocation":
self.main.get_country_name(country_id)}
def __init__(self):
self.conn = connection.Db().session
self.main = Main(connection.Db())
self.moderation = Moderation(connection.Db())
self.select = Select(connection.Db())
class DecodePolygon(object):
"""
Decodes the Google encoded polygon/polyline string into
a list of lat lng tuples.
"""
def __init__(self):
self.conn = connection.Db().session
self.main = Main(connection.Db())
self.moderation = Moderation(connection.Db())
self.select = Select(connection.Db())
@staticmethod
def decode_GMap(encoded_string):
index = 0
lat = 0
lng = 0
coords = []
str_len = len(encoded_string)
#print(encoded_string)
#print()
while index < str_len:
shift = 0
result = 0
stay_in_loop = True
while stay_in_loop and index < str_len: # GET THE LATITUDE
b = ord(encoded_string[index]) - 63
result |= (b & 0x1f) << shift
shift += 5
index += 1
if not b >= 0x20:
stay_in_loop = False
# Python ternary instruction..
d_lat = ~(result >> 1) if (result & 1) else (result >> 1)
lat += d_lat
shift = 0
result = 0
stay_in_loop = True
while stay_in_loop and index < str_len: # GET THE LONGITUDE
b = ord(encoded_string[index]) - 63
result |= (b & 0x1f) << shift
shift += 5
index += 1
if not b >= 0x20:
stay_in_loop = False
# Python ternary instruction..
d_lng = ~(result >> 1) if (result & 1) else (result >> 1)
lng += d_lng
lng_num = lng * 1e-5
lat_num = lat * 1e-5
coords.append([str(lat_num), str(lng_num)])
return coords
def decode_all(self):
databases = self.main.get_databases()
session = self.conn.cursor()
for db in databases[1]:
print(db[0])
t_keys, types = self.main.get_types(db[0])
overlays_table = "_Overlays"
for typ in types:
type_overlay = typ[1] + overlays_table
result = self.select.read(type_overlay,
columns=['Overlay_ID', 'Points'])
points = result.fetchall()
for point in points:
if not point["Points"]:
continue
decoded_point = self.decode_GMap(point["Points"])
#print(point, decoded_point)
sql = "UPDATE " + type_overlay + " SET Points=%(decoded_point)s WHERE Overlay_ID=%(overlay_id)s"
print(sql, point["Overlay_ID"])
params = {
"overlay_id": point["Overlay_ID"],
"decoded_point": json.dumps(decoded_point)
}
session.execute(sql, params)
self.conn.commit()
session.close()
def view():
main = Main(db)
pref = main.get_user_pref()
modules = []
select = Select(db)
met = select.read("metadata")
metadata = met.fetchall()
module = {}
module['heading'] = "Cumulative Capacity by Category"
module['content'] = []
module['content'].append("<p style='text-align: center'><b>Total Cumulative Capacity: %s MWe</b></p>"
% sum([t['Cumulative_Capacity'] for t in types]))
module['content'].append("<div id='pie_chart' style='width: 900px; height: 500px;'></div>")
module['content'] = "".join(module['content'])
modules.append(module)
#keys, types = main.get_types_for_country(country_id)
for t in types:
type_name = main.get_type_name(t['Type_ID']).replace("_", " ")
db_name = main.get_databases(type_id=t['Type_ID'])[1][0][0]
dbn = ""
if db_name == "PowerPlants":
dbn = "power plants"
else:
dbn = ""
s_link = []
s_link.append("<a href='/summary/type/")
s_link.append(str(t['Type_ID']))
s_link.append("/" + str(country_id))
s_link.append("'>Click Here</a>")
map_link = []
map_link.append("<a href='/map/")
map_link.append(db_name.lower() + "/")
map_link.append(str(t['Type_ID']) + "/")
map_link.append(str(country_id) + "/0/")
map_link.append("'>Map</a>")
module = {}
module['heading'] = type_name
module['content'] = module_content.format(type_name=type_name,
db=dbn,
total=t['Number_of_Plants'],
cumulative_capacity=t['Cumulative_Capacity'],
summary_link="".join(s_link),
map_link="".join(map_link),
pie_id=type_name,
pie_value=t['Cumulative_Capacity'])
modules.append(module)
main.store_user_pref(pref[0], country_id, pref[1], pref[3])
user_pref = main.make_html_user_pref()
title = "Summary for " + country_name
return flask.render_template("country_summary.html",
modules=modules, title=title,
country=country_name,
user_pref=user_pref,
body_onload="Chart.plotPieChart('', 'pie_chart')")
def __insert_generic_module(self, table_name, form_data, description_id, dual=0):
"""
Prepares and saves data for a generic module like _Description,
_Location, etc.
"""
sql_values = {}
sql_fields = []
alt_sql_fields = []
insert_id = 0
alt_sql_statement = []
sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
if description_id > 0:
sql_fields.append("`Description_ID`=:description_id")
sql_values['description_id'] = description_id
alt_sql_fields.append("`Description_ID`=%(description_id)s")
select = Select(self.db)
column_names = select.read_column_names(table_name)
for k in column_names:
kk = k[0]
if dual > 0:
kk = kk + "_stn" + str(dual)
if not form_data.get(kk):
continue
value = form_data.get(kk)
if kk.find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`=%(" + key.lower() + ")s")
value = value.strip().replace("'", "")
value = value.replace('"', "")
sql_values[key.lower()] = value
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor(dictionary=True)
try:
print("".join(alt_sql_statement))
print(sql_values)
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
insert_id = session.lastrowid
except Exception:
print(session.statement)
"""
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = re.sub(r"(\d+)%", "\g<1>##", sql_stmt)
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = re.sub(r"(\d+)##", "\g<1>%", sql_stmt)
#print(sql_stmt)
result = session.execute(sql_stmt)
self.db_conn.commit()
insert_id = result.lastrowid
except Exception:
#session.rollback()
raise
"""
raise
finally:
session.close()
return insert_id
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)
def __init__(self, connection):
#self.connection = connection
self.select = Select(connection)
self.session = flask.session
