예제 #1
0
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']
예제 #2
0
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']
예제 #3
0
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()
예제 #4
0
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
예제 #5
0
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()
예제 #6
0
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
예제 #7
0
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
예제 #8
0
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