def status():
routes = [route['rte_desc'] for route in Helper.get_routes()]
data = Helper.query_route_status()
web_session = Session()
query = web_session.execute("""
SELECT s.rte_desc, sum(s.count) AS count
FROM odk.records_long s
WHERE s.rte_desc LIKE 'Portland Streetcar%'
GROUP BY s.rte_desc;""")
#hardcode streetcar targets, then populate the count
streetcar = {
"Portland Streetcar - NS Line": {
'target': 869,
'count': 0
},
"Portland Streetcar - A Loop": {
'target': 331,
'count': 0
},
"Portland Streetcar - B Loop": {
'target': 343,
'count': 0
}
}
for record in query:
debug(record)
streetcar[record[0]]['count'] = int(record[1])
web_session.close()
summary = Helper.query_routes_summary()
return render_template('long/status.html',
streetcar=streetcar,
routes=routes,
data=data,
summary=summary)
def query_routes_summary():
ret_val = {}
# query web database
# using helper views
web_session = Session()
query = web_session.execute("""
SELECT sq.rte, sq.rte_desc, sum(sq.scount + sq.tcount) AS count,sum(sq.target)*0.2
FROM
(SELECT s.rte, s.rte_desc,s.dir,s.dir_desc,s.time_period, s.target,
s.count AS scount, t.count AS tcount
FROM
summary_ts t
LEFT JOIN summary s
ON t.rte = s.rte AND
t.dir = s.dir AND
t.time_period = s.time_period
) sq
GROUP BY sq.rte,sq.rte_desc
ORDER BY sq.rte;""")
for record in query:
rte_desc = record[1]
count = int(record[2])
target = int(record[3])
if count >= target * 2:
count = target * 1.5
ret_val[rte_desc] = {"count": count, "target": target}
debug(ret_val)
return ret_val
def build_response_summary_status(query):
ret_val = {}
for record in query:
if not record[0]: continue
rte = int(record[0])
rte_desc = record[1]
dir = int(record[2])
dir_desc = record[3]
time = record[4]
count = int(record[5])
target = int(record[6])
if count > target * 2:
count = target * 2
data = {}
data['target'] = target
data['count'] = count
if rte_desc not in ret_val:
ret_val[rte_desc] = {}
# set up each time period of this direction
# populate later when that record is fetched
if str(dir) not in ret_val[rte_desc]:
ret_val[rte_desc][str(dir)] = Helper.build_shell(dir_desc)
if target == 0:
data = {}
ret_val[rte_desc][str(dir)][time] = data
debug(ret_val)
return ret_val
def geo_query():
points, lines = None, None
debug(request.args)
if 'uri' in request.args:
uri = request.args.get('uri')
data = query_locations(uri)
debug(data)
return jsonify({'data': data})
def max_data_query():
response = []
where = ""
args = request.args
where = Helper.buildconditions(args)
debug(where)
response = Helper.query_station_data(where=where)
return jsonify(data=response)
def map():
session = Session()
keys = []
query = session.query(SurveysCore)
for record in query:
#TODO check that survey has not already been flagged by user
debug(record.uri)
#if record.flags.locations:
keys.append(record.uri)
session.close()
return render_template(static('map.html'), keys=keys)
def surveyor_summary_query():
response = []
date = time.strftime("%Y-%m-%d")
debug(request.args)
debug(request.args['date'])
if 'date' in request.args.keys():
date = request.args['date'].strip()
debug(date)
debug(type(date))
response = Helper.get_user_data(date)
debug(response)
return jsonify(data=response)
def get_questions():
ret_val = []
web_session = Session()
questions = web_session.execute("""
SELECT num, questions
FROM odk.ques_lookup
ORDER BY num;""")
ret_val = [[question[0], str(question[1])] for question in questions]
web_session.close()
debug(ret_val)
return ret_val
def get_destination(where, qnum):
ret_val = []
where = where
bar_chart = pygal.Bar(print_values=True)
bar_chart.title = 'Trip Destination Types'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q4_dest_type is not null {0}),
destcount as (
select
case
WHEN q4_dest_type = '1' THEN 'Home'
WHEN q4_dest_type = '2' THEN 'Work'
WHEN q4_dest_type = '3' THEN 'School'
WHEN q4_dest_type = '4' THEN 'Recreation'
WHEN q4_dest_type = '5' THEN 'Shopping'
WHEN q4_dest_type = '6' THEN 'Personal business'
WHEN q4_dest_type = '7' THEN 'Visit family or friends'
WHEN q4_dest_type = '8' THEN 'Medical appointment'
WHEN q4_dest_type = '9' THEN 'Other'
else ''
end as dest_type,
count(*) as count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by q4_dest_type
order by pct desc)
select * from destcount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
bar_chart.add(str(row[0]), int(row[1]))
bar_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def get_routes():
ret_val = []
session = Session()
routes = session.execute("""
SELECT rte, rte_desc
FROM orange_route_direction
ORDER BY route_sort_order;""")
ret_val = [{
'rte': str(route[0]),
'rte_desc': route[1]
} for route in routes]
debug(ret_val)
session.close()
return ret_val
def data_query():
response = []
user = ""
rte_desc = ""
dir_desc = ""
csv = False
if 'rte_desc' in request.args.keys():
rte_desc = request.args['rte_desc'].strip()
debug(rte_desc)
if 'dir_desc' in request.args.keys():
dir_desc = request.args['dir_desc'].strip()
debug(dir_desc)
if 'user' in request.args.keys():
user = request.args['user'].strip()
debug(user)
if 'csv' in request.args.keys():
csv = request.args['csv']
debug(csv)
if csv:
data = Helper.query_route_data(user=user,
rte_desc=rte_desc,
dir_desc=dir_desc,
csv=csv)
response = ""
# build csv string
for record in data:
response += ','.join(record) + '\n'
else:
response = Helper.query_route_data(user=user,
rte_desc=rte_desc,
dir_desc=dir_desc)
return jsonify(data=response)
def query_route_status(rte_desc=''):
# set rte_desc to wildcard to query
# if no route was specified
ret_val = {}
# query web database
# using helper views
web_session = Session()
if rte_desc:
query = web_session.execute(
"""
SELECT s.rte,s.rte_desc,s.dir,s.dir_desc,s.time_period,
s.count, s.target * .1
FROM
odk.summary_long s
WHERE s.rte_desc = :rte_desc
ORDER BY s.rte, s.dir,
CASE s.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END;""", {'rte_desc': rte_desc})
ret_val = Helper.build_response_route_status(query)
else:
# query web database
# using helper views
query = web_session.execute("""
SELECT s.rte,s.rte_desc,s.dir,s.dir_desc,s.time_period,
s.count, s.target * .1
FROM
odk.summary_long s
ORDER BY s.rte, s.dir,
CASE s.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END;""")
ret_val = Helper.build_response_summary_status(query)
web_session.close()
debug(ret_val)
return ret_val
def current_users(date):
ret_val = {}
web_session = Session()
results = web_session.execute(
"""
SELECT u.rte_desc, u.time_period, u.user_id, u.rte
FROM
(SELECT * from users_tod_ts
UNION
SELECT * from users_tod) u
WHERE u.date = :date
ORDER BY
CASE u.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END, u.rte;""", {'date': date})
for result in results:
rte_desc = result[0]
time_period = result[1]
user = "******".join(
sorted(
list(set([user.strip()
for user in result[2].split(",")]))))
#list1 = result[2].split(",")
#list2 = set(list1)
#list3 = ", ".join(list2)
#debug(list1)
#debug(list2)
#debug(list3)
#user = "******".join(list(set(result[2].split(","))))
#debug(user)
if time_period not in ret_val:
ret_val[time_period] = []
data = {'rte_desc': rte_desc, 'user': user}
ret_val[time_period].append(data)
web_session.close()
debug(ret_val)
return ret_val
def query_sep_data(where):
ret_val = []
query_args = {}
where = where
region = " AND f.q5_orig_region='2' and f.q6_dest_region='2' "
validate = " AND f.loc_validated='1' "
not_null = " AND f.q3_orig_type is not null AND f.q4_dest_type is not null;"
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.origin_sep is not null {0}),
sepcount as (
select origin_sep,
count(*) as sep_count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by origin_sep
order by origin_sep)
select * from sepcount;""".format(where)
#query_string += where
#query_string += ;
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
SEP = 0
COUNT = 1
PER = 2
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['sep'] = record[SEP]
data['count'] = record[COUNT]
data['percentage'] = float(record[PER])
ret_val.append(data)
web_session.close()
return ret_val
def get_satisfaction(where, qnum):
ret_val = []
where = where
pie_chart = pygal.Pie(print_values=True)
pie_chart.title = 'Customer Satisfaction'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q1_satisfaction is not null {0}),
satisfactioncount as (
select
CASE
WHEN q1_satisfaction = '1' THEN 'Very satisfied'
WHEN q1_satisfaction = '3' THEN 'Somewhat satisfied'
WHEN q1_satisfaction = '4' THEN 'Neutral'
WHEN q1_satisfaction = '5' THEN 'Somewhat dissatisfied'
WHEN q1_satisfaction = '6' THEN 'Very dissatisfied'
WHEN q1_satisfaction = '7' THEN 'Do not know'
else ''
END as satisfaction,
count(*) as count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by q1_satisfaction
order by pct desc)
select * from satisfactioncount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
pie_chart.add(str(row[0]), float(row[2]))
pie_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def get_travel_change(where, qnum):
ret_val = []
where = where
bar_chart = pygal.Bar(print_values=True)
bar_chart.title = 'Transit Usage Compared to A Year Ago'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q7_travel_change is not null {0}),
changecount as (
select
case
WHEN q7_travel_change = '1' THEN 'More'
WHEN q7_travel_change = '2' THEN 'Same'
WHEN q7_travel_change = '3' THEN 'Less'
WHEN q7_travel_change = '4' THEN 'Do not know'
else ''
end as ride_change,
count(*) as count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by q7_travel_change
order by pct desc)
select * from changecount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
bar_chart.add(str(row[0]), int(row[1]))
bar_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def get_routes():
ret_val = []
web_session = Session()
routes = web_session.execute("""
SELECT distinct rte, rte_desc
FROM odk.rte_lookup
ORDER BY rte;""")
RTE = 0
RTE_DESC = 1
ret_val = [{
'rte': str(route[RTE]),
'rte_desc': route[RTE_DESC]
} for route in routes]
web_session.close()
debug(ret_val)
return ret_val
def map_query():
response = []
stops = []
where = ""
args = request.args
debug(args)
rte = args.get('rte')
dir = args.get('dir')
if rte and dir:
stops = Helper.get_stops(rte=rte, dir=dir)
#debug(stops)
where = Helper.buildconditions(args)
#debug(where)
response = Helper.query_map_data(where=where)
#debug(response)
return jsonify(data=response, stops=stops)
def query_cty_data(where):
ret_val = []
query_args = {}
where = where
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.origin_cty is not null {0}),
ctycount as (
select origin_cty,
count(*) as cty_count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by origin_cty
order by origin_cty)
select * from ctycount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
COUNTY = 0
COUNT = 1
PER = 2
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['COUNTY'] = record[COUNTY]
data['count'] = record[COUNT]
data['percentage'] = float(record[PER])
ret_val.append(data)
web_session.close()
return ret_val
def request_query():
response = []
where = ""
args = request.args
where = Helper.buildconditions(args)
debug(where)
qnum = int(request.args.get('qnum'))
if qnum == 1:
response = Helper.get_satisfaction(where=where, qnum=qnum)
if qnum == 2:
response = Helper.get_origin(where=where, qnum=qnum)
if qnum == 3:
response = Helper.get_destination(where=where, qnum=qnum)
if qnum == 4:
response = Helper.get_travel_change(where=where, qnum=qnum)
if qnum == 5:
response = Helper.get_travel_less(where=where, qnum=qnum)
return jsonify(data=response, metadata=metadata[qnum])
def sep_query():
response = []
where = ""
args = request.args
where = Helper.buildconditions(args)
debug(where)
#get the sel_boundary param from the request.args object
sel_boundary = args.get('boundary')
debug(sel_boundary)
#call functions based on sel_boundary value
if sel_boundary == 'sep':
response = Helper.query_sep_data(where=where)
if sel_boundary == 'zipcode':
response = Helper.query_zipcode_data(where=where)
if sel_boundary == 'cty':
response = Helper.query_cty_data(where=where)
return jsonify(data=response)
def query_routes_summary():
ret_val = {}
# query web database
# using helper views
web_session = Session()
query = web_session.execute("""
SELECT s.rte, s.rte_desc, sum(s.count) AS count,sum(s.target)*0.1
FROM odk.summary_long s
GROUP BY s.rte,s.rte_desc
ORDER BY s.rte;""")
for record in query:
rte_desc = record[1]
count = int(record[2])
target = int(record[3])
if count > target * 2:
count = target * 2
ret_val[rte_desc] = {"count": count, "target": target}
debug(ret_val)
return ret_val
def get_user_data(date):
surveyordata = []
bar_chart = pygal.HorizontalBar(print_values=True)
bar_chart.title = 'Number of Surveys by Surveyor on {0}'.format(date)
web_session = Session()
results = web_session.execute(
"""
select
name,
string_agg(distinct route, ' || ') as routes,
count(route) as count,
round(count(route)*100/(select count(*) from odk.users_tod where _date=:date)::numeric,2) as pct
from odk.users_tod
where _date=:date
group by name
order by count desc;""", {'date': date})
for result in results:
print(result[0], result[1], result[2], result[3])
surveyordata.append(
[result[0], result[1],
int(result[2]),
float(result[3])])
bar_chart.add(result[0], int(result[2]))
web_session.close()
debug(surveyordata)
bar_chart.render_to_file(
os.path.join(DIRPATH,
"static/image/{0}{1}.svg".format('surveyors-', date)))
return surveyordata
def get_travel_less(where, qnum):
ret_val = []
where = where
bar_chart = pygal.Bar(print_values=True)
bar_chart.title = 'Reasons for Riding TriMet Less'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q8_ride_less is not null {0}),
unnest_element as (
select unnest(string_to_array(q8_ride_less, ' ')) as element
from survey),
ridechange as (
select
case
WHEN element = '1' THEN 'Gasoline prices low'
WHEN element = '2' THEN 'Home changed'
WHEN element = '3' THEN 'Work changed'
WHEN element = '4' THEN 'School changed'
WHEN element = '5' THEN 'Life changed'
WHEN element = '6' THEN 'Telecommute more'
WHEN element = '7' THEN 'Take ride hailing services'
WHEN element = '8' THEN 'On time issues'
WHEN element = '9' THEN 'Frequency not enough'
WHEN element = '10' THEN 'Crowding issues'
WHEN element = '11' THEN 'Span of service not enough'
WHEN element = '12' THEN 'Fare prices too high'
WHEN element = '13' THEN 'Drive instead'
WHEN element = '14' THEN 'Bicycle instead'
WHEN element = '15' THEN 'Walk instead'
WHEN element = '16' THEN 'Other'
WHEN element = '17' THEN 'Do not know'
else ''
end as ride_less,
count(*) as count,
round(100*count(*)/(select count(*) from unnest_element)::numeric,1) as pct
from unnest_element
group by element
order by pct desc)
select * from ridechange;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
bar_chart.add(str(row[0]), int(row[1]))
bar_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def query_route_data(user='', rte_desc='', dir_desc='', csv=False):
ret_val = []
query_args = {}
where = ""
#if user: user = "******" + user + "%"
user_filter = " s.name = :user"
rte_desc_filter = " r.rte_desc = :rte_desc "
dir_desc_filter = " r.dir_desc = :dir_desc "
def construct_where(string, param, filt_name):
if not param:
return string
if filt_name == "user": filt = user_filter
elif filt_name == "rte_desc": filt = rte_desc_filter
else: filt = dir_desc_filter
if string:
return string + " AND " + filt
else:
return string + filt
# build where clause
debug(where)
for param in [(user, 'user'), (rte_desc, 'rte_desc'),
(dir_desc, 'dir_desc')]:
where = construct_where(where, param[0], param[1])
debug(where)
query_args[param[1]] = param[0]
if where:
where = " WHERE " + where
limit = "LIMIT 300;"
if csv:
# add headers to csv data
ret_val.append([
'date', 'time', 'user', 'rte_desc', 'dir_desc', 'satisfaction',
'comments'
])
limit = ";"
query_string = """
SELECT
r.rte_desc,
r.dir_desc,
f._date,
date_trunc('second',f._end) as _time,
s.name as user,
case
WHEN q1_satisfaction = '1' THEN 'Very satisfied'
WHEN q1_satisfaction = '3' THEN 'Somewhat satisfied'
WHEN q1_satisfaction = '4' THEN 'Neutral'
WHEN q1_satisfaction = '5' THEN 'Somewhat dissatisfied'
WHEN q1_satisfaction = '6' THEN 'Very dissatisfied'
WHEN q1_satisfaction = '7' THEN 'Do not know'
end as satisfaction,
coalesce(f.q2_satis_comments,'')
from odk.fall_survey_2016_data f
join odk.rte_lookup r
on f.rte::integer = r.rte and f.dir::integer = r.dir
join odk.surveyors s
on f._surveyor = s.username """
query_string += where
query_string += " ORDER BY f._date DESC, f._end DESC "
query_string += limit
debug(query_string)
web_session = Session()
query = web_session.execute(query_string, query_args)
RTE_DESC = 0
DIR_DESC = 1
DATE = 2
TIME = 3
USER = 4
SATISFACTION = 5
COMMENTS = 6
# each record will be converted as json
# and sent back to page
for record in query:
if csv:
data = []
data.append(str(record[DATE]))
data.append(str(record[TIME]))
data.append(record[USER])
data.append(record[RTE_DESC])
data.append(record[DIR_DESC])
data.append(record[SATISFACTION])
data.append(record[COMMENTS])
else:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['user'] = record[USER]
data['rte_desc'] = record[RTE_DESC]
data['dir_desc'] = record[DIR_DESC]
data['satisfaction'] = record[SATISFACTION]
data['comments'] = record[COMMENTS]
ret_val.append(data)
web_session.close()
return ret_val
def query_route_data(user='', rte_desc='', dir_desc='', csv=False):
ret_val = []
query_args = {}
where = ""
if user: user = "******" + user + "%"
user_filter = " first_name LIKE :user "
rte_desc_filter = " rte_desc = :rte_desc "
dir_desc_filter = " dir_desc = :dir_desc "
def construct_where(string, param, filt_name):
if not param:
return string
if filt_name == "user": filt = user_filter
elif filt_name == "rte_desc": filt = rte_desc_filter
else: filt = dir_desc_filter
if string:
return string + " AND " + filt
else:
return string + filt
# build where clause
debug(where)
for param in [(user, 'user'), (rte_desc, 'rte_desc'),
(dir_desc, 'dir_desc')]:
where = construct_where(where, param[0], param[1])
debug(where)
query_args[param[1]] = param[0]
if where:
where = " WHERE " + where
limit = "LIMIT 300;"
if csv:
# add headers to csv data
ret_val.append([
'date', 'time', 'user', 'rte_desc', 'dir_desc', 'on_stop',
'off_stop'
])
limit = ";"
query_string = """
SELECT rte_desc, dir_desc, date, time, first_name,
on_stop_name, off_stop_name
FROM display_data """
query_string_ts = """
SELECT rte_desc, dir_desc, date, time, first_name,
on_stop_name, off_stop_name
FROM display_data_ts """
query_string += where
query_string += " ORDER BY date DESC, time DESC "
query_string += limit
query_string_ts += where
query_string_ts += " ORDER BY date DESC, time DESC "
query_string_ts += limit
debug(query_string)
debug(query_string_ts)
web_session = Session()
query = web_session.execute(query_string, query_args)
# convert query object to list
result = [r for r in query]
query_ts = web_session.execute(query_string_ts, query_args)
# convert query object to list
result_ts = [r for r in query_ts]
# merge two lists to one list
result_all = result + result_ts
debug(type(result_all))
RTE_DESC = 0
DIR_DESC = 1
DATE = 2
TIME = 3
USER = 4
ON_STOP = 5
OFF_STOP = 6
# each record will be converted as json
# and sent back to page
for record in result_all:
if csv:
data = []
data.append(str(record[DATE]))
data.append(str(record[TIME]))
data.append(record[USER])
data.append(record[RTE_DESC])
data.append(record[DIR_DESC])
data.append(record[ON_STOP])
data.append(record[OFF_STOP])
else:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['user'] = record[USER]
data['rte_desc'] = record[RTE_DESC]
data['dir_desc'] = record[DIR_DESC]
data['on_stop'] = record[ON_STOP]
data['off_stop'] = record[OFF_STOP]
ret_val.append(data)
web_session.close()
return ret_val
def query_map_data(where, csv):
ret_val = []
query_args = {}
where = where
csv = csv
debug(csv)
not_null = " location is not null AND manager_supervisor is not null "
if csv == "yes":
# add headers to csv data
ret_val.append([
'date', 'time', 'manager', 'region', 'shelterid',
'location_lat', 'location_lng', 'litter', 'graffiti', 'washed',
'roof', 'glass', 'bench', 'trashcan', 'lid',
'trashcangraffiti', 'repair', 'comments'
])
query_string = """
SELECT
to_char(createdate, 'Mon DD YYYY') as _date,
cast(createdate as time),
manager_supervisor as manager,
contractregion as region,
case
WHEN shelterid = 'None' or shelterid = '' THEN '1234'
else shelterid
end as shelterid,
location,
coalesce(nolitter, '') as litter,
coalesce(nograffiti, '') as graffiti,
coalesce(pressurewashed, '') as washed,
coalesce(roofclean, '') as roof,
coalesce(glassdried, '') as glass,
coalesce(benchdried, '') as bench,
coalesce(trashcanempty, '') as trashcan,
coalesce(lidclean, '') as lid,
coalesce(trashcangraffiti, ''),
case
WHEN needrepair = 'true' THEN 'Yes'
WHEN needrepair = 'false' THEN 'No'
else ''
end as repair,
coalesce(comments, '')
from bus_shelter_inspection """
query_string += " WHERE "
query_string += not_null
query_string += where
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
DATE = 0
TIME = 1
MANAGER = 2
REGION = 3
SHELTER = 4
LOCATION = 5
LITTER = 6
GRAFFITI = 7
WASHED = 8
ROOF = 9
GLASS = 10
BENCH = 11
TRASHCAN = 12
LID = 13
TRASHCANGRAFFITI = 14
REPAIR = 15
COMMENTS = 16
# each record will be converted as json
# and sent back to page
for record in query:
if csv == "yes":
data = []
data.append(str(record[DATE]))
data.append(str(record[TIME]))
data.append(record[MANAGER])
data.append(record[REGION])
data.append(record[SHELTER])
data.append(record[LOCATION])
data.append(record[LITTER])
data.append(record[GRAFFITI])
data.append(record[WASHED])
data.append(record[ROOF])
data.append(record[GLASS])
data.append(record[BENCH])
data.append(record[TRASHCAN])
data.append(record[LID])
data.append(record[TRASHCANGRAFFITI])
data.append(record[REPAIR])
data.append(record[COMMENTS])
else:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['manager'] = record[MANAGER]
data['region'] = record[REGION]
data['shelter'] = record[SHELTER]
data['location'] = record[LOCATION]
data['litter'] = record[LITTER]
data['graffiti'] = record[GRAFFITI]
data['washed'] = record[WASHED]
data['roof'] = record[ROOF]
data['glass'] = record[GLASS]
data['bench'] = record[BENCH]
data['trashcan'] = record[TRASHCAN]
data['lid'] = record[LID]
data['trashcangraffiti'] = record[TRASHCANGRAFFITI]
data['repair'] = record[REPAIR]
data['comments'] = record[COMMENTS]
ret_val.append(data)
web_session.close()
return ret_val
def map_query():
response = []
where = ""
csv = "no"
args = request.args
debug(args)
if 'csv' in args.keys():
csv = request.args['csv']
debug(csv)
where = Helper.buildconditions(args)
debug(where)
if csv == "yes":
debug("executed here!")
data = Helper.query_map_data(where=where, csv=csv)
response = ""
#build csv string
for record in data:
if record:
debug(record)
response += ','.join([(item or ' ') for item in record[:-1]])
debug(response)
debug(record[-1])
response += ',' + record[-1] + '\n'
# else:
# response += ','.join("nodata") + '\n'
else:
response = Helper.query_map_data(where=where, csv=csv)
return jsonify(data=response)
def map_offs_details():
response = {'success': False}
if 'rte' in request.args.keys():
rte = int(request.args['rte'].strip())
debug(rte)
#debug(type(rte))
#rte = h.rte_lookup(rte_desc)
session = Session()
fields = ['dir', 'tad', 'centroid', 'stops', 'ons', 'count']
query_time = session.execute(
"""
SELECT """ + ','.join(fields) + """, bucket
FROM tad_time_stats
WHERE rte = :rte;""", {'rte': rte})
query_markers = session.execute(
"""
SELECT dir, tad, centroid, stops, ons, count
FROM tad_stats
WHERE rte = :rte;""", {'rte': rte})
query_tads = session.execute(
"""
SELECT
r.dir,
t.tadce10 AS tad,
ST_AsGeoJson(ST_Transform(ST_Union(t.geom), 4326)) AS geom,
ST_AsGeoJson(ST_Transform(ST_Centroid(ST_Union(t.geom)), 4326)) AS centroid
FROM tad AS t
JOIN tm_routes AS r
ON ST_Intersects(t.geom, r.geom)
WHERE r.rte = :rte
GROUP BY r.dir, t.tadce10;""", {'rte': rte})
query_data_summary = session.execute(
"""
SELECT
dir,
on_tad,
sum(count) AS ons
FROM tad_onoff
WHERE rte = :rte
GROUP BY dir, on_tad;""", {'rte': rte})
query_data = session.execute(
"""
SELECT
dir,
on_tad,
off_tad,
count
FROM tad_onoff
WHERE rte = :rte;""", {'rte': rte})
query_routes = session.execute(
"""
SELECT dir, ST_AsGeoJson(ST_Transform(ST_Union(geom), 4326))
FROM tm_routes
WHERE rte = :rte
GROUP BY dir;""", {'rte': rte})
query_minmax = session.execute(
"""
SELECT dir, label, stop_name, ST_AsGeoJson(ST_Transform(geom, 4326))
FROM stop_minmax
WHERE rte = :rte;""", {'rte': rte})
def build_data(record):
data = {}
for index in range(1, len(fields)):
field = record[index]
if isinstance(field, Decimal): field = int(field)
data[fields[index]] = field
return data
def int_zero(value):
try:
return int(value)
except:
return 0
tads = []
stops = {}
stops[0] = {}
stops[1] = {}
summary = {}
summary[0] = []
summary[1] = []
data = {}
data[0] = {}
data[1] = {}
routes_geom = {}
minmax = {}
time_data = {}
time_data[0] = {}
time_data[1] = {}
for record in query_tads:
dir_ = record[0]
tad = record[1]
geom = record[2]
centroid = record[3]
tads.append({
'dir': dir_,
'tad': tad,
'geom': geom,
'centroid': centroid
})
for record in query_markers:
dir_ = record[0]
tad = record[1]
centroid = json.loads(record[2])
stops_geom = json.loads(record[3])
ons = int_zero(record[4])
count = int_zero(record[5])
stops[dir_][tad] = {
'tad': tad,
'centroid': centroid,
'count': count,
'stops': stops_geom,
'ons': ons
}
for record in query_data_summary:
dir_ = record[0]
tad_on = record[1]
ons = int(record[2])
summary[dir_].append({'tad': tad_on, 'ons': ons})
for record in query_data:
dir_ = record[0]
tad_on = record[1]
tad_off = record[2]
offs = int(record[3])
if tad_on not in data[dir_]:
data[dir_][tad_on] = {}
data[dir_][tad_on]['offs'] = []
data[dir_][tad_on]['offs'].append({'tad': tad_off, 'offs': offs})
for record in query_routes:
routes_geom[record[0]] = {
'dir': record[0],
'geom': json.loads(record[1])
}
for record in query_minmax:
if record[0] not in minmax:
minmax[record[0]] = {}
minmax[record[0]][record[1]] = {
'geom': record[3],
'stop_name': record[2]
}
# time of day buckets
for record in query_time:
#debug(record)
tad = record[1]
if tad not in time_data[record[0]]:
time_data[record[0]][tad] = []
ons = int_zero(record[4])
count = int_zero(record[5])
bucket = int_zero(record[6])
# insert because data comes in sorted and needs to go out sorted?
time_data[record[0]][tad].insert(bucket, {
"count": count,
"ons": ons
})
response['success'] = True
response['stops'] = stops
response['summary'] = summary
response['data'] = data
response['tads'] = tads
response['routes'] = routes_geom
response['minmax'] = minmax
response['time_data'] = time_data
session.close()
return jsonify(response)
def query_station_data(where):
ret_val = []
query_args = {}
where = where
not_null = " platform is not null AND inspector is not null "
query_string = """
SELECT
to_char(createdate, 'Mon DD YYYY') as _date,
cast(createdate as time),
inspector,
cleaningcrew as crew,
washcrew,
platform as station,
coalesce(bathroom_cleaned, ''),
coalesce(bathroom_stocked, ''),
coalesce(bench_handrail_phone_tvm_cleaned, '') as bench,
coalesce(crew_room_cleaned, ''),
coalesce(electric_panel_cleaned, '') as electric,
coalesce(parking_lot_notrash, '') as lot_notrash,
coalesce(platform_cleaned, ''),
coalesce(platform_hosedoff, ''),
coalesce(shelter_area_hosed_down, '') as shelter_hosed,
coalesce(shelter_enclosure_swept, '') as swept,
coalesce(track_area_no_litter, '') as track_nolitter,
coalesce(trash_emptied, ''),
coalesce(landscaping_notrash, ''),
coalesce(walk_pathway_notrash, '') as path_notrash,
coalesce(platform_novegetation, ''),
coalesce(comments, '')
from max_platform_inspection """
query_string += " WHERE "
query_string += not_null
query_string += where
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
DATE = 0
TIME = 1
INSPECTOR = 2
CREW = 3
WASH = 4
STATION = 5
BRCLEANED = 6
BRSTOCKED = 7
BENCH = 8
CREWROOM = 9
ELECTRIC = 10
LOTNOTRASH = 11
PLATFORM_CLEANED = 12
PLATFORMHOSED = 13
SHELTERHOSED = 14
SWEPT = 15
TRACKLITTER = 16
TRASHEMPTIED = 17
LANDSCAPING = 18
PATHNOTRASH = 19
VEGETATION = 20
COMMENTS = 21
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['inspector'] = record[INSPECTOR]
data['crew'] = record[CREW]
data['wash'] = record[WASH]
data['station'] = record[STATION]
data['bathroom_cleaned'] = record[BRCLEANED]
data['bathroom_stocked'] = record[BRSTOCKED]
data['bench_cleaned'] = record[BENCH]
data['crew_room'] = record[CREWROOM]
data['electric'] = record[ELECTRIC]
data['lot_notrash'] = record[BENCH]
data['platform_cleaned'] = record[PLATFORM_CLEANED]
data['platform_hosed'] = record[PLATFORMHOSED]
data['shelter_hosed'] = record[SHELTERHOSED]
data['swept'] = record[SWEPT]
data['track_litter'] = record[TRACKLITTER]
data['trash_empied'] = record[TRASHEMPTIED]
data['landscaping'] = record[LANDSCAPING]
data['pathway_trash'] = record[PATHNOTRASH]
data['vegetation'] = record[VEGETATION]
data['comments'] = record[COMMENTS]
ret_val.append(data)
web_session.close()
return ret_val
