def get_news():
mine_id = request.args.get('id')
conn = dbo.db_connect()
google_data = dbo.select_query(
conn,
"select title, link, description, source, date, 'google' from google_news where mine_id = %s limit 5"
% mine_id)
# # scholar_data = dbo.select_query(conn,
# """select title, link, author, cited_by, NULL from scholar_news where mine_id = %s""" %mine_id)
# data = google_data.append(scholar_data)
# google_data = [("title1", "http://www.google.ca", 'hi', 'me', None),
# ("title2", "http://www.google.ca", 'hi', 'me', None),
# ("title3", "http://www.google.ca", 'hi', 'me', None),
# ("title4", "http://www.google.ca", 'hi', 'me', None),
# ("title5", "http://www.google.ca", 'hi', 'me', None)]
if len(google_data) == 0:
google_data = dbo.select_query(
conn,
"select title, link, description, source, date, 'google' from google_news where mine_id = 24439 limit 5"
)
features = []
for article in google_data:
features.append({
"title": article[0],
"link": article[1],
"description": article[2],
"source": article[3],
"date": article[4],
"type": article[5]
})
return jsonify(features=features)
def get_news_in_area():
minlat = request.args.get('minlat')
minlng = request.args.get('minlng')
maxlat = request.args.get('maxlat')
maxlng = request.args.get('maxlng')
conn = dbo.db_connect()
google_data = dbo.select_query(
conn, """select title, link, description, source, date
from google_news a join mines b on a.mine_id = b.mine_id
where geom @ ST_MakeEnvelope(%s, %s, %s, %s, 4326) limit 5""" %
(minlng, minlat, maxlng, maxlat))
if len(google_data) == 0:
google_data = dbo.select_query(
conn,
"select title, link, description, source, date from google_news where mine_id = 24439 limit 5"
)
features = []
for article in google_data:
features.append({
"title": article[0],
"link": article[1],
"description": article[2],
"source": article[3],
"date": article[4]
})
return jsonify(features=features)
def claims_api():
conn = dbo.db_connect()
minlat = request.args.get('minlat')
minlng = request.args.get('minlng')
maxlat = request.args.get('maxlat')
maxlng = request.args.get('maxlng')
data = dbo.select_query(
conn, """select a.mtrs, ST_AsGeoJSON(ST_ForceRHR(poly::geometry)) geom
,sum(case when claim_type LIKE 'LODE%%' THEN claim_count else 0 END) lode
,sum(case when claim_type LIKE 'MILL%%' THEN claim_count else 0 END) mill
,sum(case when claim_type LIKE 'TUNNEL%%' THEN claim_count else 0 END) tunnel
,sum(case when claim_type LIKE 'PLACER%%' THEN claim_count else 0 END) placer
,sum(claim_count)
from claims_geo a join claims_meta b on a.mtrs = b.mtrs
where poly && ST_MakeEnvelope(%s, %s, %s, %s, 4326)
group by 1, 2 having sum(claim_count) > 0""" %
(minlng, minlat, maxlng, maxlat))
claims = []
for claim in data:
claim_data = dict(geometry=eval(claim[1]), type='Feature')
claim_data['properties'] = dict(lode=claim[2],
mill=claim[3],
tunnel=claim[4],
placer=claim[5],
total=claim[6])
claims.append(claim_data)
return jsonify(type='FeatureCollection', features=claims)
def faults_api():
conn = dbo.db_connect()
minlat = request.args.get('minlat')
minlng = request.args.get('minlng')
maxlat = request.args.get('maxlat')
maxlng = request.args.get('maxlng')
query = """select name, ftype, cast(cast(length as decimal(6,1)) as varchar) , sliprate, age, ST_AsGeoJSON(fault_str::geometry)
from faults where length(fault_str) > 50
and fault_str::geometry && ST_MakeEnvelope(%s, %s, %s, %s, 4326)""" % (
minlng, minlat, maxlng, maxlat)
data = dbo.select_query(conn, query)
print(data)
faults = []
for fault in data:
fault_line = dict(type='Feature', geometry=eval(fault[5]))
fault_line['properties'] = dict(name=fault[0],
ftype=fault[1],
length=fault[2],
sliprate=fault[3],
age=fault[4])
faults.append(fault_line)
return jsonify(type='FeatureCollection', features=faults)
def mine_api():
conn = dbo.db_connect()
minlat = request.args.get('minlat')
minlng = request.args.get('minlng')
maxlat = request.args.get('maxlat')
maxlng = request.args.get('maxlng')
data = dbo.select_query(
conn, """SELECT a.mine_id, mine_name, owners,
development_stage, activity_status,
lat, lon,
coalesce(b.mine_id, 0) fav FROM mines a left join favs b on a.mine_id = b.mine_id
WHERE geom @ ST_MakeEnvelope(%s, %s, %s, %s, 4326) limit 500""" %
(minlng, minlat, maxlng, maxlat))
features = []
for mine in data:
features.append(
construct_feature(
mine[6], mine[5],
dict(id=mine[0],
name=mine[1],
owner=mine[2],
stage=mine[3],
activity=mine[4],
fav=mine[7])))
return jsonify(type='FeatureCollection', features=features)
def is_fav():
mine_id = request.args.get('id')
conn = dbo.db_connect()
fav = dbo.select_query(conn,
"SELECT * FROM favs where mine_id = %s" % mine_id)
if fav:
return jsonify(status=1)
else:
return jsonify(status=0)
def update_fav():
mine_id = request.args.get('id')
conn = dbo.db_connect()
fav = dbo.select_query(conn,
"SELECT * FROM favs where mine_id = %s" % mine_id)
if not fav:
dbo.execute_query(conn, "INSERT INTO favs VALUES (%s)" % mine_id)
return jsonify(status="Added Favorite")
else:
dbo.execute_query(conn,
"DELETE FROM favs WHERE mine_id = %s" % mine_id)
return jsonify(status="Deleted Favorite")
def get(self):
conn = dbo.db_connect()
articles = dbo.select_query(
conn,
"""select title, link, description, source, date, a.ticker, name
from company_news as a
join companies as b
on a.ticker = b.ticker
order by a.date desc""")
news_list = []
for article in articles:
news_list.append({
"title": article[0],
"link": article[1],
"description": article[2],
"source": article[3],
"date": article[4],
"ticker": article[5],
"company": article[6]
})
return render_template('news.html', news_list=news_list)
def select_expense(id):
query = "SELECT * FROM Expense WHERE id = %s"
params = [id]
select_query(query, params)
def select_group(id):
query = "SELECT * FROM GroupU WHERE id = %s"
params = [id]
select_query(query, params)
def select_category(id):
query = "SELECT * FROM Categories WHERE id = %s"
params = [id]
select_query(query, params)