def test_formatResults_simple_XHTML_missing_field(self): """ One of the later entries is missing a field. The function should be able to deal with it. """ result = [{"attrib_1": "1", "attrib_2": 2}, {"attrib_1": "1"}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>attrib_2</th> </tr> <tr> <td>1</td> <td>2</td> </tr> <tr> <td>1</td> <td></td> </tr> </table> </body> </html>"""))
def test_formatResults_nested_XHTML(self): """ Tests nested XHTML formatting. I cannot think of pretty way to output nested data in a table. """ result = [{"attrib_1": "a", "nested_attribs": {"a": 1, "b": 2}}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>nested_attribs</th> </tr> <tr> <td>a</td> <td>{'a': 1, 'b': 2}</td> </tr> </table> </body> </html>"""))
def test_formatResults_simple_XML(self): """ Tests simple XML formatting. """ result = [{ "attrib_1": "1", "attrib_2": 2 }, { "attrib_1": "1", "attrib_2": 2 }] formatted_result = util.formatResults(dummy_request("xml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <?xml version='1.0' encoding='utf-8'?> <ResultSet totalResultsReturned="2" totalResultsAvailable="2" firstResultPosition="0"> <Item> <attrib_1>1</attrib_1> <attrib_2>2</attrib_2> </Item> <Item> <attrib_1>1</attrib_1> <attrib_2>2</attrib_2> </Item> </ResultSet>"""))
def test_formatResults_simple_XHTML_missing_field(self): """ One of the later entries is missing a field. The function should be able to deal with it. """ result = [{"attrib_1": "1", "attrib_2": 2}, {"attrib_1": "1"}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>attrib_2</th> </tr> <tr> <td>1</td> <td>2</td> </tr> <tr> <td>1</td> <td></td> </tr> </table> </body> </html>"""))
def test_formatResults_lists_XHTML(self): """ Tests nested XHTML formatting. I cannot think of pretty way to output a list inside a table. """ result = [{"attrib_1": "a", "list_of_a": [{"a": "2"}, {"a": "3"}]}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>list_of_a</th> </tr> <tr> <td>a</td> <td>[{'a': '2'}, {'a': '3'}]</td> </tr> </table> </body> </html>"""))
def test_formatResults_simple_XHTML(self): """ Tests simple XHTML formatting. """ result = [{ "attrib_1": "1", "attrib_2": 2 }, { "attrib_1": "1", "attrib_2": 2 }] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>attrib_2</th> </tr> <tr> <td>1</td> <td>2</td> </tr> <tr> <td>1</td> <td>2</td> </tr> </table> </body> </html>"""))
def getListForEvent(self, event_id, request): # Get all waveform channels corresponding to that id. session = self.env.db.session(bind=self.env.db.engine) query = session.query(WaveformChannelObject)\ .filter(WaveformChannelObject.event_resource_id == event_id)\ .all() result = [] for q in query: chan = q.channel stat = chan.station result.append({ "network": stat.network, "station": stat.station, "location": chan.location, "channel": chan.channel, "filepath_id": q.filepath_id, "tag": q.tag, "starttime": q.starttime.isoformat(), "endtime": q.endtime.isoformat(), "sampling_rate": q.sampling_rate, "format": q.format, "is_synthetic": q.is_synthetic}) result = formatResults(request, result) return result
def test_formatResults_nested_XHTML(self): """ Tests nested XHTML formatting. I cannot think of pretty way to output nested data in a table. """ result = [{"attrib_1": "a", "nested_attribs": {"a": 1, "b": 2}}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>nested_attribs</th> </tr> <tr> <td>a</td> <td>{'a': 1, 'b': 2}</td> </tr> </table> </body> </html>"""))
def test_formatResults_simple_XHTML(self): """ Tests simple XHTML formatting. """ result = [{"attrib_1": "1", "attrib_2": 2}, {"attrib_1": "1", "attrib_2": 2}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>attrib_2</th> </tr> <tr> <td>1</td> <td>2</td> </tr> <tr> <td>1</td> <td>2</td> </tr> </table> </body> </html>"""))
def test_formatResults_simple_XHTML_order(self): """ Tests simple XHTML formatting and checks that the fields are ordered correctly. """ result = [{ "attrib_a": "1", "attrib_b": 2, "attrib_c": 3 }, { "attrib_a": "1", "attrib_c": 3, "attrib_b": 2 }, { "attrib_b": "2", "attrib_a": 1, "attrib_c": 3 }, { "attrib_c": "3", "attrib_b": 2, "attrib_a": 1 }] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_a</th> <th>attrib_b</th> <th>attrib_c</th> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> </table> </body> </html>"""))
def process_GET(self, request): """ Generates a list of available seismic stations. The 'format' argument supports xml, xhtml, json, and geojson and will output the appropriate type. """ # If network and station are given, return details. network = request.args0.get("network", None) station = request.args0.get("station", None) if network and station: network = network station = station return self.get_station_details(request, network, station) # XXX: This is likely not optimal. session = self.env.db.session(bind=self.env.db.engine) query = session.query(StationObject).order_by(StationObject.network)\ .order_by(StationObject.station).all() query = [{ "id": i.id, "network": i.network, "station": i.station, "latitude": i.latitude, "longitude": i.longitude, "elevation_in_m": i.elevation_in_m, "local_depth_in_m": i.local_depth_in_m, "channels": [{"channel": j.channel} for j in i.channel]} for i in query] # Encode geojson manually, let the rest be handled by a convenience # method. formats = request.args0.get("format", []) if "geojson" in formats: result = {"type": "FeatureCollection", "features": []} for item in query: feature = {"type": "Feature", "geometry": {"type": "Point", "coordinates": [item["longitude"], item["latitude"]]}, "properties": { "elevation_in_m": item["elevation_in_m"], "latitude": item["latitude"], "longitude": item["longitude"], "network": item["network"], "station": item["station"], "local_depth_in_m": item["local_depth_in_m"], "channels": ", ".join([_i["channel"] for _i in item["channels"]])}, "id": "%s.%s" % (item["network"], item["station"])} result["features"].append(feature) result = json.dumps(result) request.setHeader('content-type', 'application/json; charset=UTF-8') else: result = formatResults(request, query) return result
def renderResourceList(self, request): """ Returns the indexed values of every resource of the given type. This directly accesses the database and does not use the catalog. This should be faster as the heavy lifting is done by the database. The downside is that it is not easily testable. It supports the limit and offset parameters in the standard fashion. It furthermore allows filtering by any indexed values. Assume the resource has an index called indexed_value. Filtering is then possible by: * min_indexed_value=12 * max_indexed_value=23 * indexed_value=23 Only rows satisfying these requirements will be returned. """ limit = int(request.args0.get("limit", 20)) offset = int(request.args0.get("offset", 0)) table = "/%s/%s" % (self.package_id, self.resourcetype_id) # Directly access the database via an SQLView which is automatically # created for every resource type and filled with all indexed values. tab = Table(table, request.env.db.metadata, autoload=True) columns = tab.c.keys() query = sql.select([tab]) # Now loop over all parameters and apply the filters. for param, value in request.args0.iteritems(): if param in columns: query = query.where( tab.c[param] == tab.c[param].type.python_type(value)) elif param.startswith("min_") and param[4:] in columns: name = param[4:] query = query.where( tab.c[name] >= tab.c[name].type.python_type(value)) elif param.startswith("max_") and param[4:] in columns: name = param[4:] query = query.where( tab.c[name] <= tab.c[name].type.python_type(value)) count = query.count() count = request.env.db.query(count).first()[0] query = query.limit(limit).offset(offset) # Execute the query. result = request.env.db.query(query) result = formatResults(request, result, limit=limit, offset=offset, count=count) return result
def process_GET(self, request): """ Fetches all station id's grouped by network_id. """ query = sql.text(""" SELECT network_id, station_id FROM "/seismology/station" GROUP BY network_id, station_id ORDER BY network_id, station_id """) results = self.env.db.query(query) return formatResults(request, results)
def test_formatResults_lists_JSON(self): """ Tests nested JSON formatting. """ result = [{"attrib_1": "a", "list_of_a": [{"a": "2"}, {"a": "3"}]}] formatted_result = util.formatResults(dummy_request("json"), result) # Read again. output = json.loads(formatted_result)["ResultSet"] # Test the headers. self.assertEqual(output["totalResultsReturned"], 1) self.assertEqual(output["totalResultsAvailable"], 1) self.assertEqual(output["firstResultPosition"], 0) # Assert the actual contents. self.assertEqual(result, output["Result"])
def test_formatResults_simple_JSON(self): """ Tests simple JSON formatting. """ result = [{"attrib_1": "1", "attrib_2": 2}, {"attrib_1": "1", "attrib_2": 2}] formatted_result = util.formatResults(dummy_request("json"), result) # Read again. output = json.loads(formatted_result)["ResultSet"] # Test the headers. self.assertEqual(output["totalResultsReturned"], 2) self.assertEqual(output["totalResultsAvailable"], 2) self.assertEqual(output["firstResultPosition"], 0) # Assert the actual contents. self.assertEqual(result, output["Result"])
def test_formatResults_lists_XML(self): """ Tests nested XML formatting. """ result = [{"attrib_1": "a", "list_of_a": [{"a": "2"}, {"a": "3"}]}] formatted_result = util.formatResults(dummy_request("xml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <?xml version='1.0' encoding='utf-8'?> <ResultSet totalResultsReturned="1" totalResultsAvailable="1" firstResultPosition="0"> <Item> <attrib_1>a</attrib_1> <list_of_a> <a>2</a> <a>3</a> </list_of_a> </Item> </ResultSet>"""))
def get_event_list(self, request): """ Return a formatted list of events. """ # Directly access the database via an SQLView which is automatically # created for every resource type and filled with all indexed values. tab = Table("/event_based_data/event", request.env.db.metadata, autoload=True) # Build up the query. query = sql.select([tab]) # Execute the query. result = request.env.db.query(query) # Use a convenience function provided by SeisHub to get a nicely # formatted output. result = formatResults(request, result) return result
def test_formatResults_simple_XHTML_order(self): """ Tests simple XHTML formatting and checks that the fields are ordered correctly. """ result = [{"attrib_a": "1", "attrib_b": 2, "attrib_c": 3}, {"attrib_a": "1", "attrib_c": 3, "attrib_b": 2}, {"attrib_b": "2", "attrib_a": 1, "attrib_c": 3}, {"attrib_c": "3", "attrib_b": 2, "attrib_a": 1}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_a</th> <th>attrib_b</th> <th>attrib_c</th> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> <tr> <td>1</td> <td>2</td> <td>3</td> </tr> </table> </body> </html>"""))
def test_formatResults_nested_XML(self): """ Tests nested XML formatting. """ result = [{"attrib_1": "a", "nested_attribs": {"a": 1, "b": 2}}] formatted_result = util.formatResults(dummy_request("xml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <?xml version='1.0' encoding='utf-8'?> <ResultSet totalResultsReturned="1" totalResultsAvailable="1" firstResultPosition="0"> <Item> <attrib_1>a</attrib_1> <nested_attribs> <a>1</a> <b>2</b> </nested_attribs> </Item> </ResultSet>"""))
def test_formatResults_nested_XML(self): """ Tests nested XML formatting. """ result = [{"attrib_1": "a", "nested_attribs": {"a": 1, "b": 2}}] formatted_result = util.formatResults(dummy_request("xml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <?xml version='1.0' encoding='utf-8'?> <ResultSet totalResultsReturned="1" totalResultsAvailable="1" firstResultPosition="0"> <Item> <attrib_1>a</attrib_1> <nested_attribs> <a>1</a> <b>2</b> </nested_attribs> </Item> </ResultSet>"""))
def test_formatResults_simple_JSON(self): """ Tests simple JSON formatting. """ result = [{ "attrib_1": "1", "attrib_2": 2 }, { "attrib_1": "1", "attrib_2": 2 }] formatted_result = util.formatResults(dummy_request("json"), result) # Read again. output = json.loads(formatted_result)["ResultSet"] # Test the headers. self.assertEqual(output["totalResultsReturned"], 2) self.assertEqual(output["totalResultsAvailable"], 2) self.assertEqual(output["firstResultPosition"], 0) # Assert the actual contents. self.assertEqual(result, output["Result"])
def process_GET(self, request): if len(request.path) < 7: return {} xpath = request.path[6:] # get resources try: resources = self.env.catalog.query(xpath, full=True) except: return {} # get indexed data results = [] for resource in resources: data = self.env.catalog.getIndexData(resource) data['package_id'] = resource.package._id data['resourcetype_id'] = resource.resourcetype._id data['document_id'] = resource.document._id data['resource_name'] = str(resource._name) results.append(data) # generate output return formatResults(request, results, count=len(results))
def test_formatResults_lists_XML(self): """ Tests nested XML formatting. """ result = [{"attrib_1": "a", "list_of_a": [{"a": "2"}, {"a": "3"}]}] formatted_result = util.formatResults(dummy_request("xml"), result) self.assertEqual( normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <?xml version='1.0' encoding='utf-8'?> <ResultSet totalResultsReturned="1" totalResultsAvailable="1" firstResultPosition="0"> <Item> <attrib_1>a</attrib_1> <list_of_a> <a>2</a> <a>3</a> </list_of_a> </Item> </ResultSet>"""))
def test_formatResults_simple_XML(self): """ Tests simple XML formatting. """ result = [{"attrib_1": "1", "attrib_2": 2}, {"attrib_1": "1", "attrib_2": 2}] formatted_result = util.formatResults(dummy_request("xml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <?xml version='1.0' encoding='utf-8'?> <ResultSet totalResultsReturned="2" totalResultsAvailable="2" firstResultPosition="0"> <Item> <attrib_1>1</attrib_1> <attrib_2>2</attrib_2> </Item> <Item> <attrib_1>1</attrib_1> <attrib_2>2</attrib_2> </Item> </ResultSet>"""))
def getListForStationAndEvent(self, event_id, station_id, request): split_station = station_id.split(".") if len(split_station) != 2: msg = "'station_id' has to be of the form NET.STA" raise InvalidParameterError(msg) network, station = split_station print network, station session = self.env.db.session(bind=self.env.db.engine) stat_id = get_station_id(network, station, session) if stat_id is False: msg = "Could not find station '%s'" % station_id raise InvalidParameterError(msg) query = session.query(WaveformChannelObject)\ .join(ChannelObject)\ .filter(WaveformChannelObject.event_resource_id == event_id)\ .filter(ChannelObject.station_id == stat_id).all() result = [] for q in query: chan = q.channel stat = chan.station result.append({ "network": stat.network, "station": stat.station, "location": chan.location, "channel": chan.channel, "filepath_id": q.filepath_id, "tag": q.tag, "starttime": q.starttime.isoformat(), "endtime": q.endtime.isoformat(), "sampling_rate": q.sampling_rate, "format": q.format, "is_synthetic": q.is_synthetic}) result = formatResults(request, result) return result
def test_formatResults_lists_XHTML(self): """ Tests nested XHTML formatting. I cannot think of pretty way to output a list inside a table. """ result = [{"attrib_1": "a", "list_of_a": [{"a": "2"}, {"a": "3"}]}] formatted_result = util.formatResults(dummy_request("xhtml"), result) self.assertEqual(normalize_xml_whitespace(formatted_result), normalize_xml_whitespace(""" <html> <body> <table border="1"> <tr> <th>attrib_1</th> <th>list_of_a</th> </tr> <tr> <td>a</td> <td>[{'a': '2'}, {'a': '3'}]</td> </tr> </table> </body> </html>"""))
def process_GET(self, request): tab = Table('/seismology/event', request.env.db.metadata, autoload=True) # fetch arguments try: limit = int(request.args0.get('limit')) except: limit = 50 # restrict maximal number of events to 2500 if limit > 2500: limit = 2500 offset = int(request.args0.get('offset', 0)) # build up query query = sql.select([tab]) # process arguments # this is a string value, it returns None if nothing is given for key in ['localisation_method', 'account', 'user']: temp = request.args0.get(key) if temp: query = query.where(tab.c[key] == temp) # min-max datetime values for key in ['datetime', 'first_pick', 'last_pick']: try: temp = UTCDateTime(request.args0.get(key)) query = query.where(tab.c[key] == temp.datetime) except: pass try: temp = UTCDateTime(request.args0.get('min_' + key)) query = query.where(tab.c[key] >= temp.datetime) except: pass try: temp = UTCDateTime(request.args0.get('max_' + key)) query = query.where(tab.c[key] <= temp.datetime) except: pass # min-max float values for key in ['latitude', 'longitude', 'magnitude', 'depth']: try: temp = float(request.args0.get(key)) query = query.where(tab.c[key] == temp) except: pass try: temp = float(request.args0.get('min_' + key)) query = query.where(tab.c[key] >= temp) except: pass try: temp = float(request.args0.get('max_' + key)) query = query.where(tab.c[key] <= temp) except: pass # min-max integer values for key in ['used_p', 'used_s']: try: temp = int(request.args0.get(key)) query = query.where(tab.c[key] == temp) except: pass try: temp = int(request.args0.get('min_' + key)) query = query.where(tab.c[key] >= temp) except: pass try: temp = int(request.args0.get('max_' + key)) query = query.where(tab.c[key] <= temp) except: pass # execute query results = request.env.db.query(query.offset(offset).limit(limit)) # ok count all distinct values query = sql.select([sql.func.count(tab.c['document_id'].distinct())]) # execute query try: count = request.env.db.query(query).fetchone()[0] except: count = 0 return formatResults(request, results, limit=limit, offset=offset, count=count)
def get_station_details(self, request, network, station): session = self.env.db.session(bind=self.env.db.engine) try: query = session.query(StationObject)\ .filter(StationObject.network == network)\ .filter(StationObject.station == station).one() except sqlalchemy.orm.exc.NoResultFound: session.close() msg = "Station %s.%s could not be found." % (network, station) raise NotFoundError(msg) result = { "network_code": query.network, "network_name": "", "station_code": query.station, "station_name": "", "latitude": query.latitude, "longitude": query.longitude, "elevation_in_m": query.elevation_in_m, "local_depth_in_m": query.local_depth_in_m, "channels": []} # Also parse information about all channels. for channel in query.channel: md = channel.channel_metadata if md: md = md[0] info = { "channel_code": channel.channel, "location_code": channel.location, "start_date": str(md.starttime) if md else None, "end_date": str(md.starttime) if md else None, "instrument": "", "sampling_rate": "", "format": md.format if md else None, "channel_filepath_id": md.filepath_id if md else None} # Attempt to get long descriptions for the station and network # codes. This is only possible for SEED and XSEED files. if info["format"] and info["format"].lower() in ["seed", "xseed"]: parser = Parser(md.filepath.filepath) inv = parser.getInventory() if not result["network_name"] and inv["networks"]: for network in inv["networks"]: if network["network_code"] != result["network_code"]: continue result["network_name"] = network["network_name"] break if not result["station_name"] and inv["stations"]: for station in inv["stations"]: station_code = station["station_id"].split(".")[1] if station_code != result["station_code"]: continue result["station_name"] = station["station_name"] for channel in inv["channels"]: location_code, channel_code = \ channel["channel_id"].split(".")[2:] if location_code == info["location_code"] and \ channel_code == info["channel_code"]: info["start_date"] = str(channel["start_date"]) info["end_date"] = str(channel["end_date"]) info["instrument"] = channel["instrument"] info["sampling_rate"] = channel["sampling_rate"] result["channels"].append({"channel": info}) session.close() return formatResults(request, [result])
def process_GET(self, request): tab = Table("/seismology/event", request.env.db.metadata, autoload=True) # fetch arguments try: limit = int(request.args0.get("limit")) except: limit = 50 # restrict maximal number of events to 2500 if limit > 2500: limit = 2500 offset = int(request.args0.get("offset", 0)) # build up query query = sql.select([tab]) # process arguments # this is a string value, it returns None if nothing is given for key in ["evaluation_mode", "author"]: temp = request.args0.get(key) if temp: query = query.where(tab.c[key] == temp) # min-max datetime values for key in ["datetime", "first_pick", "last_pick"]: try: temp = UTCDateTime(request.args0.get(key)) query = query.where(tab.c[key] == temp.datetime) except: pass try: temp = UTCDateTime(request.args0.get("min_" + key)) query = query.where(tab.c[key] >= temp.datetime) except: pass try: temp = UTCDateTime(request.args0.get("max_" + key)) query = query.where(tab.c[key] <= temp.datetime) except: pass # min-max float values for key in ["latitude", "longitude", "magnitude", "depth"]: try: temp = float(request.args0.get(key)) query = query.where(tab.c[key] == temp) except: pass try: temp = float(request.args0.get("min_" + key)) query = query.where(tab.c[key] >= temp) except: pass try: temp = float(request.args0.get("max_" + key)) query = query.where(tab.c[key] <= temp) except: pass # min-max integer values for key in ["used_phase_count"]: try: temp = int(request.args0.get(key)) query = query.where(tab.c[key] == temp) except: pass try: temp = int(request.args0.get("min_" + key)) query = query.where(tab.c[key] >= temp) except: pass try: temp = int(request.args0.get("max_" + key)) query = query.where(tab.c[key] <= temp) except: pass # execute query results = request.env.db.query(query.offset(offset).limit(limit)) # ok count all distinct values query = sql.select([sql.func.count(tab.c["document_id"].distinct())]) # execute query try: count = request.env.db.query(query).fetchone()[0] except: count = 0 return formatResults(request, results, limit=limit, offset=offset, count=count)
def process_GET(self, request): """ """ # parse input arguments tab = Table('/seismology/station', request.env.db.metadata, autoload=True) # fetch arguments try: limit = int(request.args0.get('limit')) except: limit = None offset = int(request.args0.get('offset', 0)) # build up query columns = [tab.c['document_id'], tab.c['package_id'], tab.c['resourcetype_id'], tab.c['resource_name'], tab.c['network_id'], tab.c['station_id'], tab.c['station_name'], tab.c['latitude'], tab.c['longitude'], tab.c['elevation'], tab.c['quality'], tab.c['start_datetime'], tab.c['end_datetime']] query = sql.select(columns, distinct=True, order_by=[tab.c['network_id'], tab.c['station_id'], tab.c['start_datetime']]) # process arguments # datetime try: datetime = UTCDateTime(request.args0.get('datetime')).datetime query = query.where(tab.c['start_datetime'] <= datetime) query = query.where(sql.or_(tab.c['end_datetime'] >= datetime, tab.c['end_datetime'] == None)) except: pass # status try: status = request.args0.get('status') if status == 'active': query = query.where(tab.c['end_datetime'] == None) elif status == 'inactive': query = query.where(tab.c['end_datetime'] != None) except: pass # network, station, location. channel for col in ['network_id', 'station_id', 'location_id', 'channel_id']: text = request.args0.get(col, None) if text is None: continue elif text == '': query = query.where(tab.c[col] == None) elif '*' in text or '?' in text: text = text.replace('?', '_') text = text.replace('*', '%') query = query.where(tab.c[col].like(text)) else: query = query.where(tab.c[col] == text) # execute query results = request.env.db.query(query.offset(offset).limit(limit)) try: count = len([1 for _i in request.env.db.query(query)]) except: count = 0 return formatResults(request, results, limit=limit, offset=offset, count=count, build_url=True)