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