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 = []
