def getObserverTable(self, format="html"): if not hasattr(self, '_stationdata'): if self._fluxdata._stations != "": stationcond = " AND upper(station) = upper('%s') " % pg.escape_string(self._fluxdata._stations) else: stationcond = "" sql = """SELECT a.station, a.observer, a.country, a.teff, a.eca, a.met, b.spo FROM ( SELECT UPPER(station) AS station, MAX(meta.observer_firstname || ' ' || meta.observer_lastname) AS observer, MAX(meta.site_country) AS country, SUM(teff) AS teff, SUM(eca) AS eca, SUM(met) AS met FROM metrecflux AS x LEFT JOIN metrecflux_meta AS meta ON x.filename = meta.filename WHERE time BETWEEN '%s' AND '%s' AND shower = '%s' AND eca IS NOT NULL AND eca > 0.00 %s GROUP BY UPPER(station) ORDER BY UPPER(station) ) AS a LEFT JOIN ( SELECT UPPER(station) AS station, SUM(met) AS spo FROM metrecflux WHERE time BETWEEN '%s' AND '%s' AND shower= 'SPO' AND eca IS NOT NULL AND eca > 0.00 GROUP BY UPPER(station) ) AS b ON a.station = b.station """ % (pg.escape_string(str(self._begin)), pg.escape_string(str(self._end)), \ pg.escape_string(self._shower), stationcond, \ pg.escape_string(str(self._begin)), pg.escape_string(str(self._end))) self._stationdata = vmo.sql(sql) if self._stationdata == None: return "" html = u"<table>\n" html += u"\t<thead><th style='text-align:left;'>Station<br/> </th><th style='text-align:left;'>Observer<br/> </th><th style='text-align:left;'>Country<br/> </th>" html += u"<th>Teff<br/>[h]</th><th>ECA<br/>[10<sup>3</sup>·km<sup>2</sup>·h]</th><th>n%s<br/> </th><th>nSPO<br/> </th></thead>\n" % self._shower for row in self._stationdata: html += u"\t<tr><td style='text-align:left;'>%s</td><td style='text-align:left;'>%s</td><td style='text-align:left;'>%s</td>" % (row['station'], row['observer'].decode("utf-8"), row['country']) html += u"<td>%.0f</td><td>%.0f</td><td>%d</td><td>%d</td></tr>\n" % (row['teff']/60.0, row['eca']/1000.0, row['met'], row['spo']) html += u"<table>\n" return html
def _load(self): if self._stations != "": stationcond = " AND UPPER(station) = UPPER('%s') " % pg.escape_string(self._stations) else: stationcond = "" """ SQL Query: fetch the raw counts """ """ if self._min_interval > 2: time = "time" else: time = "date_trunc('hour',time)" """ sql = """SELECT time, SUM(teff) AS teff, -- SUM(eca) AS eca, SUM( eca * (%.7f + (1-%.7f) * (sin(radians(alt))^%.7f) / sin(radians(alt))) ) AS eca, SUM(met) AS met, COUNT(*) AS stations FROM metrecflux WHERE time >= '%s'::timestamp AND time <= '%s'::timestamp AND shower = '%s' AND eca IS NOT NULL AND alt >= %.7f AND eca > 0.50 %s GROUP BY time ORDER BY time""" % (self._delta, self._delta, self._gamma, \ pg.escape_string(str(self._begin)), \ pg.escape_string(str(self._end)), \ pg.escape_string(self._shower), \ self._min_alt, \ stationcond) result = vmo.sql(sql) if result != None: self._data = result else: self._data = []