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 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 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 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')")
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(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_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 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))
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)}
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()
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 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)