def get_stops(rte, dir):
ret_val = []
rte = rte
dir = dir
fil_val = {"rte": rte, "dir": dir}
session = Session()
stops = session.execute(
"""
SELECT rte, dir, stop_id, stop_name, ST_AsGeoJson(ST_Transform(geom, 4236)) AS geom
FROM tm_route_stops
WHERE rte = :rte AND
dir = :dir""", fil_val)
RTE = 0
DIR = 1
STOP_ID = 2
STOP_NAME = 3
GEOM = 4
ret_val = [{
'rte': row[RTE],
'dir': row[DIR],
'stop_id': row[STOP_ID],
'stop_name': row[STOP_NAME],
'geom': row[GEOM]
} for row in stops]
session.close()
#debug(ret_val)
return ret_val
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 summary_status_query():
# TODO add union for streetcar data
ret_val = []
# get count and target for each route
web_session = Session()
query = web_session.execute("""
SELECT
rte,
rte_desc,
sum(count) AS count,
sum(target) AS target
FROM summary
GROUP BY rte, rte_desc
ORDER BY rte;""")
# indexes for tuples returned by query
RTE = 0
RTE_DESC = 1
COUNT = 2
TARGET = 3
# build data for each route
# and add to ret_val list
# sorted by route number
for record in query:
data = {}
data['rte'] = str(record[RTE])
data['rte_desc'] = record[RTE_DESC]
data['count'] = float(record[COUNT])
data['target'] = float(record[TARGET])
ret_val.append(data)
web_session.close()
return ret_val
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 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 rte_lookup(rte_desc):
rte = None
session = Session()
query = session.execute(
"""
SELECT rte
FROM lookup_rte
WHERE rte_desc = :rte_desc""", {'rte_desc': rte_desc})
for record in query:
rte = record[0]
session.close()
return rte
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 get_directions():
ret_val = []
session = Session()
directions = session.execute("""
SELECT rte, rte_desc, dir, dir_desc
FROM lookup_dir
ORDER BY rte, dir;""")
ret_val = [{
'rte': str(direction[0]),
'rte_desc': direction[1],
'dir': int(direction[2]),
'dir_desc': direction[3]
} for direction in directions]
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 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_tad_stops(rte):
web_session = Session()
query = web_session.execute("""
SELECT
rte,
dir,
stop_id,
sum(ons),
ST_AsGeoJSON(min(ST_Transform(geom, 4326))),
tad_gid
FROM apc_time_stops
WHERE rte_desc = 4 AND dir = 0
GROUP BY tad_gid, stop_id, rte, dir;""")
ret_val = []
for r in query:
geojson = {}
properties = {}
properties["rte"] = str(r[0])
properties["dir"] = str(r[1])
properties["stop_id"] = str(r[2])
geojson["type"] = "Feature"
geojson["geometry"] = json.loads(str(r[4]))
geojson["properties"] = properties
ret_val.append(geojson)
web_session.close()
return ret_val
def get_tad_centroid(rte_desc, dir_desc):
if not rte_desc:
rte_desc = "%"
if not dir_desc:
dir_desc = "%"
ret_val = []
web_session = Session()
query = web_session.execute(
"""
SELECT rte_desc, tad_gid, sum(ons),
ST_AsGeoJSON(ST_Transform(ST_Centroid(ST_Union(geom)), 4326))
FROM stop_tad
WHERE rte_desc = :rte_desc
GROUP BY rte_desc, tad_gid
ORDER BY rte_desc, tad_gid;""", {"rte_desc": rte_desc})
for r in query:
geojson = {}
properties = {}
properties["rte_desc"] = str(r[0])
#properties["dir_desc"] = str(r[1])
properties["tad"] = str(r[1])
properties["sum"] = str(r[2])
geojson["type"] = "Feature"
geojson["geometry"] = json.loads(str(r[3]))
geojson["properties"] = properties
ret_val.append(geojson)
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 rte, rte_desc
FROM orange_route_direction
ORDER BY route_sort_order;""")
RTE = 0
RTE_DESC = 1
ret_val = [{
'rte': str(route[RTE]),
'rte_desc': route[RTE_DESC]
} for route in routes]
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 get_users():
users = []
web_session = Session()
results = web_session.execute("""
SELECT first
FROM users
WHERE first IS NOT NULL
ORDER BY first;""")
for result in results:
#print((dict(result)))
#print("Type:", type(dict(result)))
user_dict = dict(result)
#print(user_dict)
user = user_dict.get('first')
users.append(str(user))
web_session.close()
return users
def get_users():
users = []
session = Session()
results = session.execute("""
SELECT name
FROM odk.surveyors
ORDER BY name;""")
for result in results:
print((dict(result)))
print("Type:", type(dict(result)))
user_dict = dict(result)
print(user_dict)
user = user_dict.get('name')
users.append(str(user))
session.close()
return users
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 query_routes_summary():
ret_val = {}
# query web database
# using helper views
web_session = Session()
query = web_session.execute("""
SELECT rte, rte_desc, sum(count), sum(target) * 0.2
FROM summary
GROUP BY rte, rte_desc
ORDER BY rte;""")
for record in query:
rte_desc = record[1]
count = int(record[2])
target = int(record[3])
ret_val[rte_desc] = {"count": count, "target": target}
return ret_val
def get_directions():
ret_val = []
web_session = Session()
directions = web_session.execute("""
SELECT rte, rte_desc, dir, dir_desc
FROM lookup_dir
ORDER BY rte, dir;""")
RTE = 0
RTE_DESC = 1
DIR = 2
DIR_DESC = 3
ret_val = [{
'rte': str(direction[RTE]),
'rte_desc': direction[RTE_DESC],
'dir': int(direction[DIR]),
'dir_desc': direction[DIR_DESC]
} for direction in directions]
web_session.close()
return ret_val
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 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 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 sq.rte,sq.rte_desc,sq.dir,sq.dir_desc,sq.time_period,
sq.scount + sq.tcount AS count, sq.target * .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
WHERE sq.rte_desc = :rte_desc
ORDER BY sq.rte, sq.dir,
CASE sq.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 sq.rte,sq.rte_desc,sq.dir,sq.dir_desc,sq.time_period,
sq.scount + sq.tcount AS count, sq.target * .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
ORDER BY sq.rte, sq.dir,
CASE sq.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 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 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