def job_calc_days(day):
logging.debug('ran job_calc_days(' + day.strftime('%Y-%m-%d') + ')')
sql = 'CALL proc_day_calcs("' + day.strftime('%Y-%m-%d') + '")'
conn = functions.db_connect()
functions.db_query(conn, sql)
functions.db_disconnect(conn)
return [True, 'job calc_days']
def job_calc_days(day):
logging.debug('ran job_calc_days(' + day.strftime('%Y-%m-%d') + ')')
sql = 'CALL proc_day_calcs("' + day.strftime('%Y-%m-%d') + '")'
conn = functions.db_connect()
functions.db_query(conn, sql)
functions.db_disconnect(conn)
return [True, 'job calc_days']
def get_minutes_data_as_csv(owner, day):
"""
Get yesterday's minutes data for an owner's stations as CSV
:param owner: string
:param day: datetime
:return: string (CSV data)
"""
logging.debug("call get_minutes_data_as_csv(" + owner + ', ' + day.strftime('%Y-%m-%d') + ')')
# get the data
sql = """
SELECT dfw_id, DATE(stamp) AS d, TIME_FORMAT(TIME(stamp), '%H:%i') AS t, airT, appT, dp, rh, deltaT, soilT, gsr, Wmin, Wavg, Wmax, Wdir, rain, leaf, canT, canRH
FROM tbl_data_minutes
INNER JOIN tbl_stations
ON tbl_data_minutes.aws_id = tbl_stations.aws_id
WHERE
owner = '""" + owner + """'
AND DATE(stamp) = '""" + day.strftime('%Y-%m-%d') + """'
AND dfw_id IS NOT NULL
ORDER BY dfw_id, stamp;"""
# write it as CSV to a string
output = StringIO.StringIO()
fieldnames = ['dfw_id', 'd', 't', 'airT', 'appT', 'dp', 'rh', 'deltaT', 'soilT', 'gsr', 'Wmin', 'Wavg', 'Wmax', 'Wdir', 'rain', 'leaf', 'canT', 'canRH']
csv_writer = csv.DictWriter(output,
delimiter=',',
lineterminator='\n',
quotechar='"',
quoting=csv.QUOTE_NONNUMERIC,
fieldnames=fieldnames)
conn = functions.db_connect()
csv_writer.writerows(functions.db_query(conn, sql))
functions.db_disconnect(conn)
# add header
header_row = ','.join([
'DfW ID',
'Date',
'Time',
'Ave AirTemp (AWS) (degC)',
'Ave AppTemp (degC)',
'Ave DewPoint (degC)',
'Ave Humidity (AWS) (%)',
'Ave DeltaT (degC)',
'Ave Soil Temperature (degC)',
'Ave GSR (W/m^2)',
'Min WndSpd (m/s)',
'Ave WndSpd (m/s)',
'Max WndSpd (m/s)',
'Ave WndDir (deg)',
'Total Rain (mm)',
'Ave LeafWet (% Wet)',
'Ave AirTemp (Canopy) (degC)',
'Ave Humidity (Canopy) (%)'])
# return CSV data as string
return header_row + '\n' + output.getvalue()
def job_check_values(day, minutes_or_days):
logging.debug('ran job_check_minutes_values(' + day.strftime('%Y-%m-%d') + ',' + minutes_or_days + ')')
if minutes_or_days == 'minutes':
timestep = 'minutes'
view = 'today'
sql = functions.make_check_minutes_sql(day)
else: # days
timestep = 'days'
view = '7days'
sql = functions.make_check_days_sql(day)
# get the data
conn = functions.db_connect()
rows = functions.db_query(conn, sql)
functions.db_disconnect(conn)
last_owner = ''
last_owner_email = ''
last_owner_html = ''
admin_html = ''
html_header = '<h4>Errors in ' + timestep + ' readings for ' + day.strftime('%Y-%m-%d') + ':</h4>\n'
table_top = '<table>\n'
table_header_owner = '\t<tr><th>aws_id</th><th>Variable</th><th>Message</th></tr>\n'
table_header_admin = '\t<tr><th>aws_id</th><th>Variable</th><th>Message</th><th>Owner</th></tr>\n'
table_bottom = '</table>\n'
for row in rows:
print row
# if we have a new owner...
if row['owner'] != last_owner:
# if last owner was a real owner, send email
if last_owner != '':
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email], timestep + ' data errors', 'message is in html', msg)
# create new owner
last_owner = row['owner']
last_owner_email = row['manager_email']
last_owner_html = ''
last_owner_html += '\t<tr><td><a href="' + row['station_base_url'] + '?aws_id=' + row['aws_id'] + '&view=' + view + '">' + row['aws_id'] + '</a></td><td>' + row['var'] + '</td><td>' + row['msg'] + '</td></tr>\n'
admin_html += '\t<tr><td><a href="' + row['station_base_url'] + '?aws_id=' + row['aws_id'] + '&view=' + view + '">' + row['aws_id'] + '</a></td><td>' + row['var'] + '</td><td>' + row['msg'] + '</td><td>' + last_owner + '</td></tr>\n'
# send to the last owner
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email], timestep + ' data errors', 'message is in html', msg)
# send the admin email (all stations)
msg = html_header + table_top + table_header_admin + admin_html + table_bottom
functions.gmail_send(settings.ERROR_MSG_RECEIVERS, timestep + ' data errors', 'message is in html', msg)
return
def get_minutes_data_as_csv(owner, day):
"""
Get yesterday's minutes data for an owner's stations as CSV
:param owner: string
:param day: datetime
:return: string (CSV data)
"""
logging.debug("call get_minutes_data_as_csv(" + owner + ', ' +
day.strftime('%Y-%m-%d') + ')')
# get the data
sql = """
SELECT dfw_id, DATE(stamp) AS d, TIME_FORMAT(TIME(stamp), '%H:%i') AS t, airT, appT, dp, rh, deltaT, soilT, gsr, Wmin, Wavg, Wmax, Wdir, rain, leaf, canT, canRH
FROM tbl_data_minutes
INNER JOIN tbl_stations
ON tbl_data_minutes.aws_id = tbl_stations.aws_id
WHERE
owner = '""" + owner + """'
AND DATE(stamp) = '""" + day.strftime('%Y-%m-%d') + """'
AND dfw_id IS NOT NULL
ORDER BY dfw_id, stamp;"""
# write it as CSV to a string
output = StringIO.StringIO()
fieldnames = [
'dfw_id', 'd', 't', 'airT', 'appT', 'dp', 'rh', 'deltaT', 'soilT',
'gsr', 'Wmin', 'Wavg', 'Wmax', 'Wdir', 'rain', 'leaf', 'canT', 'canRH'
]
csv_writer = csv.DictWriter(output,
delimiter=',',
lineterminator='\n',
quotechar='"',
quoting=csv.QUOTE_NONNUMERIC,
fieldnames=fieldnames)
conn = functions.db_connect()
csv_writer.writerows(functions.db_query(conn, sql))
functions.db_disconnect(conn)
# add header
header_row = ','.join([
'DfW ID', 'Date', 'Time', 'Ave AirTemp (AWS) (degC)',
'Ave AppTemp (degC)', 'Ave DewPoint (degC)', 'Ave Humidity (AWS) (%)',
'Ave DeltaT (degC)', 'Ave Soil Temperature (degC)', 'Ave GSR (W/m^2)',
'Min WndSpd (m/s)', 'Ave WndSpd (m/s)', 'Max WndSpd (m/s)',
'Ave WndDir (deg)', 'Total Rain (mm)', 'Ave LeafWet (% Wet)',
'Ave AirTemp (Canopy) (degC)', 'Ave Humidity (Canopy) (%)'
])
# return CSV data as string
return header_row + '\n' + output.getvalue()
def job_check_latest_readings():
logging.debug('ran job_check_latest_readings()')
sql = '''
SELECT m.aws_id, m.name, m.owner, m.manager_email, m.station_base_url FROM (
SELECT
a.aws_id AS aws_id,
a.name,
a.owner,
a.manager_email,
a.station_base_url,
b.aws_id AS other
FROM (
SELECT
aws_id,
NAME,
OWNER,
manager_email,
station_base_url
FROM tbl_stations
INNER JOIN tbl_owners
ON tbl_stations.owner = tbl_owners.owner_id
WHERE STATUS = 'on' ORDER BY aws_id) AS a
LEFT JOIN (SELECT DISTINCT aws_id FROM tbl_data_minutes WHERE DATE(stamp) = CURDATE()) AS b
ON a.aws_id = b.aws_id
HAVING b.aws_id IS NULL) AS m
ORDER BY OWNER, aws_id;
'''
# get the data
conn = functions.db_connect()
rows = functions.db_query(conn, sql)
functions.db_disconnect(conn)
last_owner = ''
last_owner_email = ''
last_owner_html = ''
admin_html = ''
html_header = '<h4>Stations that are on but have failed to report today:</h4>\n'
table_top = '<table>\n'
table_header_owner = '\t<tr><th>aws_id</th><th>Name</th></tr>\n'
table_header_admin = '\t<tr><th>aws_id</th><th>Name</th><th>Owner</th></tr>\n'
table_bottom = '</table>\n'
for row in rows:
print row
# if we have a new owner...
if row['owner'] != last_owner:
# if last owner was a real owner, send email
if last_owner != '':
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email], 'stations failing to report today', 'message is in html', msg)
# create new owner
last_owner = row['owner']
last_owner_email = row['manager_email']
last_owner_html = ''
last_owner_html += '\t<tr><td><a href="' + row['station_base_url'] + '?aws_id=' + row['aws_id'] + '">' + row['aws_id'] + '</a></td><td>' + row['name'] + '</td></tr>\n'
admin_html += '\t<tr><td><a href="' + row['station_base_url'] + '?aws_id=' + row['aws_id'] + '">' + row['aws_id'] + '</a></td><td>' + row['name'] + '</td><td>' + last_owner + '</td></tr>\n'
# send to the last owner
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email], 'stations failing to report today', 'message is in html', msg)
# send the admin email (all stations)
msg = html_header + table_top + table_header_admin + admin_html + table_bottom
functions.gmail_send(settings.ERROR_MSG_RECEIVERS, 'stations failing to report today', 'message is in html', msg)
return
def job_check_values(day, minutes_or_days):
logging.debug('ran job_check_minutes_values(' + day.strftime('%Y-%m-%d') +
',' + minutes_or_days + ')')
if minutes_or_days == 'minutes':
timestep = 'minutes'
view = 'today'
sql = functions.make_check_minutes_sql(day)
else: # days
timestep = 'days'
view = '7days'
sql = functions.make_check_days_sql(day)
# get the data
conn = functions.db_connect()
rows = functions.db_query(conn, sql)
functions.db_disconnect(conn)
last_owner = ''
last_owner_email = ''
last_owner_html = ''
admin_html = ''
html_header = '<h4>Errors in ' + timestep + ' readings for ' + day.strftime(
'%Y-%m-%d') + ':</h4>\n'
table_top = '<table>\n'
table_header_owner = '\t<tr><th>aws_id</th><th>Variable</th><th>Message</th></tr>\n'
table_header_admin = '\t<tr><th>aws_id</th><th>Variable</th><th>Message</th><th>Owner</th></tr>\n'
table_bottom = '</table>\n'
for row in rows:
print row
# if we have a new owner...
if row['owner'] != last_owner:
# if last owner was a real owner, send email
if last_owner != '':
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email],
timestep + ' data errors',
'message is in html', msg)
# create new owner
last_owner = row['owner']
last_owner_email = row['manager_email']
last_owner_html = ''
last_owner_html += '\t<tr><td><a href="' + row[
'station_base_url'] + '?aws_id=' + row[
'aws_id'] + '&view=' + view + '">' + row[
'aws_id'] + '</a></td><td>' + row[
'var'] + '</td><td>' + row['msg'] + '</td></tr>\n'
admin_html += '\t<tr><td><a href="' + row[
'station_base_url'] + '?aws_id=' + row[
'aws_id'] + '&view=' + view + '">' + row[
'aws_id'] + '</a></td><td>' + row[
'var'] + '</td><td>' + row[
'msg'] + '</td><td>' + last_owner + '</td></tr>\n'
# send to the last owner
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email], timestep + ' data errors',
'message is in html', msg)
# send the admin email (all stations)
msg = html_header + table_top + table_header_admin + admin_html + table_bottom
functions.gmail_send(settings.ERROR_MSG_RECEIVERS,
timestep + ' data errors', 'message is in html', msg)
return
def job_check_latest_readings():
logging.debug('ran job_check_latest_readings()')
sql = '''
SELECT m.aws_id, m.name, m.owner, m.manager_email, m.station_base_url FROM (
SELECT
a.aws_id AS aws_id,
a.name,
a.owner,
a.manager_email,
a.station_base_url,
b.aws_id AS other
FROM (
SELECT
aws_id,
NAME,
OWNER,
manager_email,
station_base_url
FROM tbl_stations
INNER JOIN tbl_owners
ON tbl_stations.owner = tbl_owners.owner_id
WHERE STATUS = 'on' ORDER BY aws_id) AS a
LEFT JOIN (SELECT DISTINCT aws_id FROM tbl_data_minutes WHERE DATE(stamp) = CURDATE()) AS b
ON a.aws_id = b.aws_id
HAVING b.aws_id IS NULL) AS m
ORDER BY OWNER, aws_id;
'''
# get the data
conn = functions.db_connect()
rows = functions.db_query(conn, sql)
functions.db_disconnect(conn)
last_owner = ''
last_owner_email = ''
last_owner_html = ''
admin_html = ''
html_header = '<h4>Stations that are on but have failed to report today:</h4>\n'
table_top = '<table>\n'
table_header_owner = '\t<tr><th>aws_id</th><th>Name</th></tr>\n'
table_header_admin = '\t<tr><th>aws_id</th><th>Name</th><th>Owner</th></tr>\n'
table_bottom = '</table>\n'
for row in rows:
print row
# if we have a new owner...
if row['owner'] != last_owner:
# if last owner was a real owner, send email
if last_owner != '':
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email],
'stations failing to report today',
'message is in html', msg)
# create new owner
last_owner = row['owner']
last_owner_email = row['manager_email']
last_owner_html = ''
last_owner_html += '\t<tr><td><a href="' + row[
'station_base_url'] + '?aws_id=' + row['aws_id'] + '">' + row[
'aws_id'] + '</a></td><td>' + row['name'] + '</td></tr>\n'
admin_html += '\t<tr><td><a href="' + row[
'station_base_url'] + '?aws_id=' + row['aws_id'] + '">' + row[
'aws_id'] + '</a></td><td>' + row[
'name'] + '</td><td>' + last_owner + '</td></tr>\n'
# send to the last owner
msg = html_header + table_top + table_header_owner + last_owner_html + table_bottom
functions.gmail_send([last_owner_email],
'stations failing to report today',
'message is in html', msg)
# send the admin email (all stations)
msg = html_header + table_top + table_header_admin + admin_html + table_bottom
functions.gmail_send(settings.ERROR_MSG_RECEIVERS,
'stations failing to report today',
'message is in html', msg)
return