Script - datalog download form (Jython)

{jython:output=wiki}
'''
KEPServerEX Purifier Datalog Request Form

This script allows the user to request a datalog by serial number then passes
the request to the datalog retrieval script. To build the form, the script
queries the datalogging DB for all available tables and then creates a run
macro selection list populated with those entries.
'''

from __future__ import with_statement

from com.ziclix.python.sql import zxJDBC


def query_db(query):
    '''
    Query the datalogging DB, return the results, and close the connection.

    Use of JNDI lookup to locate a preconfigured datasource, instead of direct
    connection, is recommended when running Jython in an application server
    like Tomcat. See <confluence install>/conf/server.xml for the datasource
    configuration.
    '''

    with zxJDBC.lookup('java:/comp/env/jdbc/datalogging') as conn:
        with conn.cursor() as c:
            c.execute(query)
            return c.fetchall()


def build_selection_list():
    '''
    Returns wiki markup to create a selection list given the body text.

    This wiki markup creates a selection list of DB tables as a run macro. It
    is assumed that all table names begin with an underscore.
    '''

    heading = '{run:id=request|' \
              'titleRun=Download|' \
              'hideColumnHeadings=true|' \
              'heading=Serial #|' \
              'replace=serial:::select-required:25:'

    result_set = query_db('SHOW TABLES')
    for row in result_set:
        heading += row[0].lstrip('_') + '::'

    return heading + '}\n' \
           '{goto-url:url=' \
           'http://localhost:8085/display/~easjos/Purifier+Datalogs?' \
           'run_retrieve_serial=$serial&run_retrieve=run}\n' + \
           '{run}\n'


#Main routine

print(build_selection_list())

{jython}


{run:id=retrieve|replace=serial|hideParameters=true|hideRun=true}

{jython:serial_num=$serial}
'''
KEPServerEX Purifier Datalog Retrieval

Upon user-confirmation, this script establishes a connection to the
datalogging database and queries for the datalog matching the supplied serial
number/table name. The log is returned to the user-specifed location as a CSV
file.
'''

from __future__ import with_statement

import csv

from com.ziclix.python.sql import zxJDBC
from java.io import File
from javax.swing import UIManager, JFileChooser, JFrame


def get_save_destination(default_file_name):
    '''
    Display a file save dialog to the user and return the file path or None.

    Jython relies on Java Swing to create UI elements, but to avoid mixing
    languages too much, JFileChooser is only used to determine the file path.
    The file reference it returns is discarded, and Jython file handling is
    used from that point.
    '''

    UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName())

    chooser = JFileChooser()
    chooser.setSelectedFile(File(default_file_name))
    user_choice = chooser.showSaveDialog(None)

    if user_choice == JFileChooser.APPROVE_OPTION:
        return chooser.getSelectedFile().getPath()
    else:
        return None


def query_db(query):
    '''
    Query the datalogging DB, return the results, and close the connection.

    Use of JNDI lookup to locate a preconfigured datasource, instead of direct
    connection, is recommended when running Jython in an application server
    like Tomcat. See <confluence install>/conf/server.xml for the datasource
    configuration.
    '''

    with zxJDBC.lookup('java:/comp/env/jdbc/datalogging') as conn:
        with conn.cursor() as c:
            c.execute(query)
            return c.fetchall()


def retrieve_datalog(serial):
    '''
    Formats and returns the datalog for the supplied table name.

    Datalog is returned as a sequence of sequences.
    '''

    table_columns = retrieve_columns(serial)

    result_set = query_db(
        'SELECT ' + str.join(', ', table_columns) + '\n' + \
        'FROM _' + serial + '\n')

    headings = ['TIMESTAMP']
    headings.extend([get_tag_name(column) for column in table_columns[1:]])
    result_set.insert(0, headings)

    return result_set


def retrieve_columns(serial):
    '''
    Returns a list of the required columns for the desired table.

    Includes the first TIMESTAMP column and any VALUE columns.
    '''

    result_set = query_db('SHOW COLUMNS\n' \
                          'FROM _' + serial + '\n' \
                          'LIKE "%\_VALUE"\n')

    result_set = [row[0] for row in result_set]
    result_set.insert(0, result_set[0].rstrip('VALUE') + 'TIMESTAMP')

    return result_set


def get_tag_name(column):
    '''
    Returns just the tag name from the supplied column heading

    Also strips off READ if that has been appended to the tag name
    '''

    tokens = column.split('_')
    tokens = tokens[2:len(tokens) - 1]

    if tokens[len(tokens) - 1] == 'READ':
        tokens = tokens[:len(tokens) - 1]

    return str.join('_', tokens)


def save_as_csv(file_path, datalog):
    '''
    Save datalog to specified location as a CSV file.
    '''

    with open(file_path, 'w') as csv_file:
        csv_writer = csv.writer(csv_file, lineterminator = '\n')

        for entry in datalog:
            csv_writer.writerow(entry)


#Main routine

file_path = get_save_destination(serial_num + '.csv')

if file_path:
    save_as_csv(file_path, retrieve_datalog(serial_num))
    print('Log saved to ' + file_path + '\n')
else:
    print('Operation cancelled.')

{jython}

{run}
\\
\\
\\
\\
{jython}
'''Print the Jython version information'''

import sys

print('Built using Jython ' + sys.version.partition('(')[0])
{jython}