#!/usr/bin/env python

import sys
import os
import string
import optparse 
import sqlite3

__all__ = ['export_csv_files',
           'export_dump_file',
           'export_edm_files',
           'export_epics_fault_edm_file',
           'export_html_file',
           'export_latex_file',
           'export_mapping_file',
           'export_snl_program',
           'export_substitution_file',
           'process_options']

class MpsConfigurationQuery(object):
    """
    A query using `fault_count` returns the total number of faults as `count`.
    
    A query using `epics_fault_count` returns the total number of epics faults as `count`.
    
    A query using `link_node_channel_faults` returns the following columns:
        device_name
        fault_name
        fault_id
        fault_number
        pv_device_type
        pv_area
        pv_position
        pv_attribute
        ok_state_name
        faulted_state_name
        link_node_id
        card
        channel
        debounce_enum
        debounce_time
        debounce_name
        is_deadman
        is_autorecoverable
        position_x
        position_y
        position_z
        cable
        description
        area

    A query using `epics_faults returns` the following columns:
        device_name
        fault_name
        fault_id
        fault_number
        pv_device_type
        pv_area
        pv_position
        pv_attribute
        input_pv
        ok_state_name
        faulted_state_name
        is_autorecoverable
        position_x
        position_y
        position_z
        description
        area

    A query using `link_node_faults` returns the following columns:
        device_name
        base_fault_name
        fault_name
        fault_id
        fault_number
        link_node_id
        pv_device_type
        pv_area
        pv_position
        pv_attribute
        ok_state_name
        faulted_state_name
        is_autorecoverable
        description
        area

    A query using `link_processor_faults` returns the following columns:
        device_name
        fault_name
        fault_id
        fault_number
        pv_device_type
        pv_area
        pv_position
        pv_attribute
        ok_state_name
        faulted_state_name
        is_autorecoverable
        position_x
        position_y
        position_z
        description
        area
    """
    
    fault_count = 'select count(*) count from fault_number'    
    epics_fault_count = 'select count(*) count from epics_fault;'    
    link_node_channel_faults = 'select * from link_node_channel_fault'
    epics_faults = 'select * from epics_fault'
    link_node_faults = 'select * from link_node_fault'
    link_processor_faults = 'select * from link_processor_fault'



def export_snl_program(cursor, path, verbose):
    """Write a state notation language program to path"""

    stt_header_part_1 = string.Template("""/*
 * Implementation of $programName.
 */
 
program $programName
option -c;

%%#include "MachineStateBuffer.h"

int     v[$fault_count];""")

    stt_header_part_2 = string.Template('assign  v[$i] to "$pv";')
    stt_header_part_3 = 'monitor v;'
    
    state_set = string.Template("""
/* State set for PV $i ($pv), fault number $fault_number */
ss epicsFaultStateSet$i
{
    state low  {
        entry { epicsFaultStateMakeFaulted($fault_number); }
        when(pvConnected(v[$i]) && (v[$i] != 0)) { } state high
    }
    
    state high {
        entry { epicsFaultStateMakeOk($fault_number); }
        when(!pvConnected(v[$i]) || (v[$i] == 0)) { } state low
        when((delay(.1)) && (getDeviceState($fault_number) == 0)) { } state low
    }
}\n\n""")


    try:
        sttFile = open(path, 'w')
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (path, e.strerror))
        return 1
    
    try:
        cursor.execute(MpsConfigurationQuery.epics_fault_count)
        count = cursor.fetchone()
    except sqlite3.Error, e:
        print "export_snl_program:", e.args[0]
        sys.exit(1)

    number_of_epics_faults = count['count']

    query = MpsConfigurationQuery.epics_faults + ' order by fault_number'
    programName = "mpsEpicsFaultInputs"

    # Create the stt file
    if verbose:
        print 'Creating program "%s" at %s' % (programName, path)

    # Header Part 1
    print >> sttFile, stt_header_part_1.substitute(programName = programName, fault_count = number_of_epics_faults)
    
    # Header Part 2
    i = 0
    cursor.execute(query)
    for row in cursor:
        print >> sttFile, stt_header_part_2.substitute(i = i, pv = row['input_pv'])
        i += 1

    # Header Part 3
    print >> sttFile, stt_header_part_3

    # State Set Content
    i = 0
    cursor.execute(query)
    for row in cursor:
        print >> sttFile, state_set.substitute(i = i, pv = row['input_pv'], fault_number = row['fault_number'])
        i += 1
        
    sttFile.close()


def export_substitution_file(cursor, path, verbose):
    """Write substitution files to the folder specified by path"""

    colsep   = "  , "
    colWidth = {}

    #
    # MPS Input State Buffers
    #

    file_path = os.path.join(path, 'mpsBufferStatus.substitutions')

    if verbose:
        print "Exporting MPS Buffer Status substitutions file to", file_path
    
    try:
        outfile = open(file_path, "w")
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (file_path, e.strerror))
        return 1
    
            
    ports = (
                #
                # Link Processor Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_processor_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : '=' * 60 + "\n# Link Processor Fault Status (Buffer ID 1)",
                    'bufferid'          : "1",
                    'prefix'            : "",
                    'suffix'            : "_MPS",
                    'portName'          : "devMpsStatusBufferReadback",
                    'nonDbStateNames'   : False,
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : MpsConfigurationQuery.link_processor_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : "Link Processor Fault Bypass Active (Buffer ID 2)",
                    'bufferid'          : "2",
                    'prefix'            : "",
                    'suffix'            : "_BYPS",
                    'portName'          : "devMpsBypassActiveBufferReadback",
                    'nonDbStateNames'   : True,
                    'znam'              : "No",
                    'onam'              : "Yes",
                    'isLinkNodeFault'   : False
                },
                #
                # External EPICS Faults
                #
                {
                    'query'             : MpsConfigurationQuery.epics_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : '=' * 60 + "\n# External EPICS Fault Status (Buffer ID 1)",
                    'bufferid'          : "1",
                    'prefix'            : "",
                    'suffix'            : "_MPS",
                    'portName'          : "devMpsStatusBufferReadback",
                    'nonDbStateNames'   : False,
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : MpsConfigurationQuery.epics_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : "External EPICS Fault Bypass Active (Buffer ID 2)",
                    'bufferid'          : "2",
                    'prefix'            : "",
                    'suffix'            : "_BYPS",
                    'portName'          : "devMpsBypassActiveBufferReadback",
                    'nonDbStateNames'   : True,
                    'znam'              : "No",
                    'onam'              : "Yes",
                    'isLinkNodeFault'   : False
                },
                #
                # Link Node Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_node_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : '=' * 60 + "\n# Link Node Fault Status (Buffer ID 1)",
                    'bufferid'          : "1",
                    'prefix'            : "",
                    'suffix'            : "_MPS",
                    'portName'          : "devMpsStatusBufferReadback",
                    'nonDbStateNames'   : False,
                    'isLinkNodeFault'   : True
                },
                {
                    'query'             : MpsConfigurationQuery.link_node_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : "Link Node Fault Bypass Active (Buffer ID 2)",
                    'bufferid'          : "2",
                    'prefix'            : "",
                    'suffix'            : "_BYPS",
                    'portName'          : "devMpsBypassActiveBufferReadback",
                    'nonDbStateNames'   : True,
                    'znam'              : "No",
                    'onam'              : "Yes",
                    'isLinkNodeFault'   : True
                },
                #
                # Link Node Channel Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_node_channel_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : '=' * 60 + "\n# Link Node Channel Fault Status (Buffer ID 1)",
                    'bufferid'          : "1",
                    'prefix'            : "",
                    'suffix'            : "_MPS",
                    'portName'          : "devMpsStatusBufferReadback",
                    'nonDbStateNames'   : False,
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : MpsConfigurationQuery.link_node_channel_faults + ' order by fault_number',
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : "Link Node Channel Fault Bypass Active (Buffer ID 2)",
                    'bufferid'          : "2",
                    'prefix'            : "",
                    'suffix'            : "_BYPS",
                    'portName'          : "devMpsBypassActiveBufferReadback",
                    'nonDbStateNames'   : True,
                    'znam'              : "No",
                    'onam'              : "Yes",
                    'isLinkNodeFault'   : False
                }
            )
    
    column_len = get_max_column_lengths(cursor)
    colWidth['PORT']            = max(len('PORT'),          32                                  )
    colWidth['DEVICETYPE']      = max(len('DEVICETYPE'),    column_len['pv_device_type']     )
    colWidth['AREA']            = max(len('AREA'),          column_len['pv_area']            )
    colWidth['POSITION']        = max(len('POSITION'),      column_len['pv_position']        )
    colWidth['FAULTNUM']        = max(len('FAULTNUM'),      column_len['fault_number']       )
    colWidth['ZNAM']            = max(len('ZNAM'),          column_len['faulted_state_name'] )
    colWidth['ONAM']            = max(len('ONAM'),          column_len['ok_state_name']      )
    colWidth['DESC']            = max(len('DESC'),          column_len['device_name']        )
    colWidth['BUFFERID']        = max(len('BUFFERID'),      1                                   )
    colWidth['INDEX']           = max(len('INDEX'),         8                                   )
    
    if verbose:
        print "Found Max Column Lengths"

    for port in ports:
        if verbose:
            print "Exporting port %s with template %s" % (port['portName'], port['template'])

        colWidth['ATTRIBUTE']   = max(len('ATTRIBUTE'),     column_len['pv_attribute'] + len(port['prefix']) + len(port['suffix']) + len("_LN") + 3)

        print >> outfile, "\n\n"
        print >> outfile, "#"
        print >> outfile, "#",  port['comment']
        print >> outfile, "#"

        print >> outfile, "file " + port['template']
            
        print >> outfile, "{"
        print >> outfile, "    pattern { ",
        print >> outfile, "PORT"          .ljust(     colWidth['PORT']         ) + colsep,
        print >> outfile, "DEVICETYPE"    .ljust(     colWidth['DEVICETYPE']   ) + colsep,
        print >> outfile, "AREA"          .ljust(     colWidth['AREA']         ) + colsep,
        print >> outfile, "POSITION"      .ljust(     colWidth['POSITION']     ) + colsep,
        print >> outfile, "ATTRIBUTE"     .rjust(     colWidth['ATTRIBUTE']    ) + colsep,
        print >> outfile, "DESC"          .ljust(     colWidth['DESC']         ) + colsep,
        print >> outfile, "ZNAM"          .ljust(     colWidth['ZNAM']         ) + colsep,
        print >> outfile, "ONAM"          .ljust(     colWidth['ONAM']         ) + colsep,
        print >> outfile, "FAULTNUM"      .ljust(     colWidth['FAULTNUM']     )         ,
        print >> outfile, " }"
        
        cursor.execute(port['query'])
        for row in cursor:
            if port['isLinkNodeFault']:
                attribute = port['prefix'] + row['pv_attribute'] + "_LN%d" % row['link_node_id'] + port['suffix']
            else:
                try:
                    attribute = port['prefix'] + row['pv_attribute'] + port['suffix']
                except TypeError:
                    print "(Prefix, PV Attribute, Suffix) = (", port['prefix'], row['pv_attribute'], port['suffix'], ")"
                    sys.exit(1)
                                
            print >> outfile, "            { ",

            print >> outfile, port['portName']              .ljust(     colWidth['PORT']       )  + colsep,
            print >> outfile, row['pv_device_type']         .ljust(     colWidth['DEVICETYPE'] )  + colsep,
            print >> outfile, row['pv_area']                .ljust(     colWidth['AREA']       )  + colsep,
            print >> outfile, row['pv_position']            .rjust(     colWidth['POSITION']   )  + colsep,
            print >> outfile, attribute                     .rjust(     colWidth['ATTRIBUTE']  )  + colsep,
            print >> outfile, row['device_name']            .ljust(     colWidth['DESC']       )  + colsep,
            if port['nonDbStateNames']:
                print >> outfile, port['znam']              .ljust(     colWidth['ZNAM']       )  + colsep,
                print >> outfile, port['onam']              .ljust(     colWidth['ONAM']       )  + colsep,
            else:
                print >> outfile, row['faulted_state_name'] .ljust(     colWidth['ZNAM']       )  + colsep,
                print >> outfile, row['ok_state_name']      .ljust(     colWidth['ONAM']       )  + colsep,
            print >> outfile, str(row['fault_number'])      .ljust(     colWidth['FAULTNUM']   )          ,

            print >> outfile, " }"

        print >> outfile, "}\n\n"

    outfile.close()


    #
    # MPS Input State Buffer Control
    #

    file_path = os.path.join(path, 'mpsBufferControl.substitutions')

    if verbose:
        print "Exporting MPS Buffer Control substitutions file to", file_path
    
    try:
        outfile = open(file_path, "w")
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (file_path, e.strerror))
        return 1
    
            
    ports = (
                #
                # Link Processor Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_processor_faults + ' order by fault_number',
                    'template'          : "mpsBufferControl.template",
                    'comment'           : '=' * 60 + "\n# Link Processor Fault Bypass Value",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_BYPV",
                    'portName'          : "devMpsBypassValueBufferControl",
                    'isLinkNodeFault'   : False
                },
                #
                # External EPICS Faults
                #
                {
                    'query'             : MpsConfigurationQuery.epics_faults + ' order by fault_number',
                    'template'          : "mpsBufferControl.template",
                    'comment'           : '=' * 60 + "\n# External EPICS Fault Bypass Value",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_BYPV",
                    'portName'          : "devMpsBypassValueBufferControl",
                    'isLinkNodeFault'   : False
                },
                #
                # Link Node Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_node_faults + ' order by fault_number',
                    'template'          : "mpsBufferControl.template",
                    'comment'           : '=' * 60 + "\n# Link Node Fault Bypass Value",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_BYPV",
                    'portName'          : "devMpsBypassValueBufferControl",
                    'isLinkNodeFault'   : True
                },
                #
                # Link Node Channel Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_node_channel_faults + ' order by fault_number',
                    'template'          : "mpsBufferControl.template",
                    'comment'           : '=' * 60 + "\n# Link Node Channel Fault Bypass Value",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_BYPV",
                    'portName'          : "devMpsBypassValueBufferControl",
                    'isLinkNodeFault'   : False
                }
            )
    
    column_len = get_max_column_lengths(cursor)
    colWidth['PORT']            = max(len('PORT'),          32                                  )
    colWidth['DEVICETYPE']      = max(len('DEVICETYPE'),    column_len['pv_device_type']     )
    colWidth['AREA']            = max(len('AREA'),          column_len['pv_area']            )
    colWidth['POSITION']        = max(len('POSITION'),      column_len['pv_position']        )
    colWidth['FAULTNUM']        = max(len('FAULTNUM'),      column_len['fault_number']       )
    colWidth['ZNAM']            = max(len('ZNAM'),          column_len['faulted_state_name'] )
    colWidth['ONAM']            = max(len('ONAM'),          column_len['ok_state_name']      )
    colWidth['DESC']            = max(len('DESC'),          column_len['device_name']        )
    colWidth['BUFFERID']        = max(len('BUFFERID'),      1                                   )
    colWidth['INDEX']           = max(len('INDEX'),         8                                   )

    for port in ports:
        colWidth['ATTRIBUTE']   = max(len('ATTRIBUTE'),     column_len['pv_attribute'] + len(port['prefix']) + len(port['suffix']) + len("_LN") + 3)

        print >> outfile, "\n\n"
        print >> outfile, "#"
        print >> outfile, "#",  port['comment']
        print >> outfile, "#"

        print >> outfile, "file " + port['template']
            
        print >> outfile, "{"
        print >> outfile, "    pattern { ",
        print >> outfile, "PORT"          .ljust(     colWidth['PORT']         ) + colsep,
        print >> outfile, "DEVICETYPE"    .ljust(     colWidth['DEVICETYPE']   ) + colsep,
        print >> outfile, "AREA"          .ljust(     colWidth['AREA']         ) + colsep,
        print >> outfile, "POSITION"      .ljust(     colWidth['POSITION']     ) + colsep,
        print >> outfile, "ATTRIBUTE"     .rjust(     colWidth['ATTRIBUTE']    ) + colsep,
        print >> outfile, "DESC"          .ljust(     colWidth['DESC']         ) + colsep,
        print >> outfile, "ZNAM"          .ljust(     colWidth['ZNAM']         ) + colsep,
        print >> outfile, "ONAM"          .ljust(     colWidth['ONAM']         ) + colsep,
        print >> outfile, "FAULTNUM"      .ljust(     colWidth['FAULTNUM']     )         ,
        print >> outfile, " }"
        
        cursor.execute(port['query'])
        for row in cursor:
            if port['isLinkNodeFault']:
                attribute = port['prefix'] + row['pv_attribute'] + "_LN%d" % row['link_node_id'] + port['suffix']
            else:
                attribute = port['prefix'] + row['pv_attribute'] + port['suffix']
                
            print >> outfile, "            { ",

            print >> outfile, port['portName']              .ljust(     colWidth['PORT']       )  + colsep,
            print >> outfile, row['pv_device_type']         .ljust(     colWidth['DEVICETYPE'] )  + colsep,
            print >> outfile, row['pv_area']                .ljust(     colWidth['AREA']       )  + colsep,
            print >> outfile, row['pv_position']            .rjust(     colWidth['POSITION']   )  + colsep,
            print >> outfile, attribute                     .rjust(     colWidth['ATTRIBUTE']  )  + colsep,
            print >> outfile, row['device_name']            .ljust(     colWidth['DESC']       )  + colsep,
            print >> outfile, row['faulted_state_name']     .ljust(     colWidth['ZNAM']       )  + colsep,
            print >> outfile, row['ok_state_name']          .ljust(     colWidth['ONAM']       )  + colsep,
            print >> outfile, str(row['fault_number'])      .ljust(     colWidth['FAULTNUM']   )          ,

            print >> outfile, " }"

        print >> outfile, "}\n\n"

    outfile.close()




    #
    # MPS Unlatch Control
    #

    file_path = os.path.join(path, 'mpsUnlatchControl.substitutions')

    if verbose:
        print "Exporting MPS Unlatch Control substitutions file to", file_path
    
    try:
        outfile = open(file_path, "w")
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (file_path, e.strerror))
        return 1
    
            
    ports = (
                #
                # Link Processor Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_processor_faults + ' order by fault_number',
                    'template'          : "mpsUnlatchControl.template",
                    'comment'           : '=' * 60 + "\n# Link Processor Fault Unlatch",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_UNLH",
                    'portName'          : "devMpsUnlatchBufferControl",
                    'isLinkNodeFault'   : False
                },
                #
                # External EPICS Faults
                #
                {
                    'query'             : MpsConfigurationQuery.epics_faults + ' order by fault_number',
                    'template'          : "mpsUnlatchControl.template",
                    'comment'           : '=' * 60 + "\n# External EPICS Fault Unlatch",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_UNLH",
                    'portName'          : "devMpsUnlatchBufferControl",
                    'isLinkNodeFault'   : False
                },
                #
                # Link Node Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_node_faults + ' order by fault_number',
                    'template'          : "mpsUnlatchControl.template",
                    'comment'           : '=' * 60 + "\n# Link Node Fault Unlatch",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_UNLH",
                    'portName'          : "devMpsUnlatchBufferControl",
                    'isLinkNodeFault'   : True
                },
                #
                # Link Node Channel Faults
                #
                {
                    'query'             : MpsConfigurationQuery.link_node_channel_faults + ' order by fault_number',
                    'template'          : "mpsUnlatchControl.template",
                    'comment'           : '=' * 60 + "\n# Link Node Channel Fault Unlatch",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_UNLH",
                    'portName'          : "devMpsUnlatchBufferControl",
                    'isLinkNodeFault'   : False
                }
            )
    
    unlatchZnam = "No"
    unlatchOnam = "Yes"
    unlatchEventNumber = "109"

    column_len = get_max_column_lengths(cursor)
    colWidth['PORT']            = max(len('PORT'),          32                                  )
    colWidth['DEVICETYPE']      = max(len('DEVICETYPE'),    column_len['pv_device_type']     )
    colWidth['AREA']            = max(len('AREA'),          column_len['pv_area']            )
    colWidth['POSITION']        = max(len('POSITION'),      column_len['pv_position']        )
    colWidth['FAULTNUM']        = max(len('FAULTNUM'),      column_len['fault_number']       )
    colWidth['ZNAM']            = max(len('ZNAM'),          len(unlatchZnam)                    )
    colWidth['ONAM']            = max(len('ONAM'),          len(unlatchOnam)                    )
    colWidth['DESC']            = max(len('DESC'),          column_len['device_name']        )
    colWidth['BUFFERID']        = max(len('BUFFERID'),      1                                   )
    colWidth['INDEX']           = max(len('INDEX'),         8                                   )
    colWidth['EVNTNUM']         = max(len('EVNTNUM'),       3                                   )

    for port in ports:
        colWidth['ATTRIBUTE']   = max(len('ATTRIBUTE'),     column_len['pv_attribute'] + len(port['prefix']) + len(port['suffix']) + len("_LN") + 3)

        print >> outfile, "\n\n"
        print >> outfile, "#"
        print >> outfile, "#",  port['comment']
        print >> outfile, "#"

        print >> outfile, "file " + port['template']
            
        print >> outfile, "{"
        print >> outfile, "    pattern { ",
        print >> outfile, "PORT"          .ljust(     colWidth['PORT']         ) + colsep,
        print >> outfile, "DEVICETYPE"    .ljust(     colWidth['DEVICETYPE']   ) + colsep,
        print >> outfile, "AREA"          .ljust(     colWidth['AREA']         ) + colsep,
        print >> outfile, "POSITION"      .ljust(     colWidth['POSITION']     ) + colsep,
        print >> outfile, "ATTRIBUTE"     .rjust(     colWidth['ATTRIBUTE']    ) + colsep,
        print >> outfile, "DESC"          .ljust(     colWidth['DESC']         ) + colsep,
        print >> outfile, "ZNAM"          .ljust(     colWidth['ZNAM']         ) + colsep,
        print >> outfile, "ONAM"          .ljust(     colWidth['ONAM']         ) + colsep,
        print >> outfile, "FAULTNUM"      .ljust(     colWidth['FAULTNUM']     ) + colsep,
        print >> outfile, "EVNTNUM"       .ljust(     colWidth['EVNTNUM']      )         ,
        print >> outfile, " }"
        
        cursor.execute(port['query'])
        for row in cursor:
            if port['isLinkNodeFault']:
                attribute = port['prefix'] + row['pv_attribute'] + "_LN%d" % row['link_node_id'] + port['suffix']
            else:
                attribute = port['prefix'] + row['pv_attribute'] + port['suffix']
                
            print >> outfile, "            { ",

            print >> outfile, port['portName']              .ljust(     colWidth['PORT']       )  + colsep,
            print >> outfile, row['pv_device_type']         .ljust(     colWidth['DEVICETYPE'] )  + colsep,
            print >> outfile, row['pv_area']                .ljust(     colWidth['AREA']       )  + colsep,
            print >> outfile, row['pv_position']            .rjust(     colWidth['POSITION']   )  + colsep,
            print >> outfile, attribute                     .rjust(     colWidth['ATTRIBUTE']  )  + colsep,
            print >> outfile, row['device_name']            .ljust(     colWidth['DESC']       )  + colsep,
            print >> outfile, unlatchZnam                   .ljust(     colWidth['ZNAM']       )  + colsep,
            print >> outfile, unlatchOnam                   .ljust(     colWidth['ONAM']       )  + colsep,
            print >> outfile, str(row['fault_number'])      .ljust(     colWidth['FAULTNUM']   )  + colsep,
            print >> outfile, unlatchEventNumber            .ljust(     colWidth['EVNTNUM']    )          ,

            print >> outfile, " }"

        print >> outfile, "}\n\n"

    outfile.close()





    #
    # MPS Bypass Times
    #
    file_path = os.path.join(path, 'mpsBypassControl.substitutions')

    if verbose:
        print "Exporting MPS Bypass Control substitutions file to", file_path
    
    try:
        outfile = open(file_path, "w")
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (file_path, e.strerror))
        return 1
    

    ports = (
                {
                    'query'             : MpsConfigurationQuery.link_processor_faults + ' order by device_name, fault_name',
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : MpsConfigurationQuery.epics_faults + ' order by device_name, fault_name',
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : MpsConfigurationQuery.link_node_faults + ' order by link_node_id, device_name, fault_name',
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                    'isLinkNodeFault'   : True
                },
                {
                    'query'             : MpsConfigurationQuery.link_node_channel_faults + ' order by link_node_id, card, channel',
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                    'isLinkNodeFault'   : False
                },
            )

    for port in ports:
        colWidth['ATTRIBUTE']   = max(len('ATTRIBUTE'),     column_len['pv_attribute'] + len(port['prefix']) + len(port['suffix']))

        print >> outfile, "\n\n"
        print >> outfile, "#" + "-" * (len(port['comment']) + 15)
        print >> outfile, "#",  port['comment']
        print >> outfile, "#" + "-" * (len(port['comment']) + 15)
        print >> outfile, ""

        print >> outfile, "file " + port['template']
            
        print >> outfile, "{"
        print >> outfile, "    pattern { ",
        print >> outfile, "DEVICETYPE"    .ljust(     colWidth['DEVICETYPE']   ) + colsep,
        print >> outfile, "AREA"          .ljust(     colWidth['AREA']         ) + colsep,
        print >> outfile, "POSITION"      .ljust(     colWidth['POSITION']     ) + colsep,
        print >> outfile, "ATTRIBUTE"     .rjust(     colWidth['ATTRIBUTE']    ) + colsep,
        print >> outfile, "DESC"          .ljust(     colWidth['DESC']         ) + colsep,
        print >> outfile, "FAULTNUM"      .ljust(     colWidth['FAULTNUM']     )         ,
        print >> outfile, " }"
        
        cursor.execute(port['query'])
        for row in cursor:
            if port['isLinkNodeFault']:
                attribute = port['prefix'] + row['pv_attribute'] + "_LN%d" % row['link_node_id'] + port['suffix']
            else:
                attribute = port['prefix'] + row['pv_attribute'] + port['suffix']

            print >> outfile, "            { ",
            print >> outfile, row['pv_device_type']            .ljust(     colWidth['DEVICETYPE'] )  + colsep,
            print >> outfile, row['pv_area']                   .ljust(     colWidth['AREA']       )  + colsep,
            print >> outfile, row['pv_position']               .rjust(     colWidth['POSITION']   )  + colsep,
            print >> outfile, attribute                        .rjust(     colWidth['ATTRIBUTE']  )  + colsep,
            print >> outfile, row['device_name']               .ljust(     colWidth['DESC']       )  + colsep,            
            print >> outfile, str(row['fault_number'])         .rjust(     colWidth['FAULTNUM']   )          ,
            print >> outfile, " }"

        print >> outfile, "}\n\n"

    outfile.close()


    #
    # MPS Bypass Array Readback
    #
    file_path = os.path.join(path, 'mpsBypassReadbackArray.substitutions')

    if verbose:
        print "Exporting MPS Bypass Array Readback substitutions file to", file_path
    
    try:
        outfile = open(file_path, "w")
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (file_path, e.strerror))
        return 1
    

    try:
        cursor.execute(MpsConfigurationQuery.fault_count)
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "export_snl_program:", e.args[0]
        sys.exit(1)

    numberOfFaults = results['count']

    print >> outfile, "\n\n"
    print >> outfile, "#" + "-" * (60)
    print >> outfile, "# MPS Bypass Array Readback"
    print >> outfile, "#" + "-" * (60)
    print >> outfile, ""

    print >> outfile, "file mpsBypassReadbackArray.template"
        
    print >> outfile, "{"
    print >> outfile, "    pattern { ",
    print >> outfile, "EVNTNUM"         .rjust(     len("EVNTNUM")        ) + colsep,
    print >> outfile, "FAULTCOUNT"      .rjust(     len("FAULTCOUNT")     )         ,
    print >> outfile, " }"
    
    print >> outfile, "            { ",
    print >> outfile, "101"                     .rjust(     len("EVNTNUM")      )  + colsep,
    print >> outfile, str(numberOfFaults)       .rjust(     len("FAULTCOUNT")   )          ,
    print >> outfile, " }"

    print >> outfile, "}\n\n"

    outfile.close()
            


def export_mapping_file(cursor, path, verbose):
    """Write mapping (include) file to path"""

    if verbose:
        print "Exporting mapping file to", path
    
    try:
        outfile = open(path, "w")
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (path, e.strerror))
        return 1
        

    colpad   = 5
    colWidth = {}
    
    column_len = get_max_column_lengths(cursor)
    colWidth['full_fault_name'] = column_len['device_name'] + column_len['fault_name'] + len("__INDEX") + 6
    colWidth['full_fault_name_with_state'] = column_len['device_name'] + column_len['fault_name'] + column_len['state_name'] + 8


    groups = (
                {
                    'query'             : MpsConfigurationQuery.link_processor_faults + ' order by fault_number',
                    'comment'           : "Link Processor Faults",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : MpsConfigurationQuery.epics_faults + ' order by fault_number',
                    'comment'           : "EPICS Faults",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : MpsConfigurationQuery.link_node_faults + ' order by fault_number',
                    'comment'           : "Link Node Faults",
                    'isLinkNodeFault'   : True
                },
                {
                    'query'             : MpsConfigurationQuery.link_node_channel_faults + ' order by fault_number',
                    'comment'           : "Link Node Channel Faults",
                    'isLinkNodeFault'   : False
                },
            )


    #     
    # Export base values
    #
    print >> outfile, "\n\n\n#ifndef LINK_NODE_MAPPING_H"
    print >> outfile, "#define LINK_NODE_MAPPING_H"
    print >> outfile, "#define GET_DEVICE_STATE_FOR(_fn)  ((bypassActiveBuffer [(_fn)] ) ? (bypassValueBuffer  [(_fn)] ) : (deviceStateBuffer  [(_fn)] ))"
    print >> outfile, "\n\n\n"

    print >> outfile, "/****************************************************************************************"
    print >> outfile, " * Base Fault Numbers"
    print >> outfile, " ***************************************************************************************/\n"
    
    baseFaultNumberWidth = 60
    try:
        cursor.execute(MpsConfigurationQuery.fault_count)
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "export_snl_program:", e.args[0]
        sys.exit(1)

    numberOfFaults = results['count']
    
    print >> outfile, "#define FAULT_COUNT"     .ljust(baseFaultNumberWidth) + "( %d )" % numberOfFaults
    print >> outfile, "#define FAULT_ARRAY_SIZE".ljust(baseFaultNumberWidth) + "( %d )" % (numberOfFaults + 1)
    
    
    try:
        cursor.execute(MpsConfigurationQuery.epics_fault_count)
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "export_snl_program:", e.args[0]
        sys.exit(1)

    number_of_epics_faults = results['count']
    
    print >> outfile, "#define EPICS_FAULT_COUNT".ljust(baseFaultNumberWidth) + "( %d )" % number_of_epics_faults

    try:
        cursor.execute(MpsConfigurationQuery.epics_faults + ' order by fault_number')
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "export_snl_program:", e.args[0]
        sys.exit(1)

    epicsFaultBase = results['fault_number']

    print >> outfile, "#define EPICS_FAULT_NUMBER_BASE".ljust(baseFaultNumberWidth) + "( %d )" % epicsFaultBase


    #     
    # Export indices
    #
    print >> outfile, "\n\n\n"
    print >> outfile, "/****************************************************************************************"
    print >> outfile, " * Indices"
    print >> outfile, " ***************************************************************************************/\n"
    

    currentFaultPrefix = ""
    for group in groups:
        print >> outfile, "\n/* %s */" % group['comment']
        cursor.execute(group['query'])
        for row in cursor:
            if group['isLinkNodeFault']:
                baseFaultName = row['device_name'] + "_" + row['base_fault_name'] + "_BASE__INDEX"
                
                if currentFaultPrefix != baseFaultName:
                    print >> outfile, "\n#define " + baseFaultName.ljust(colWidth['full_fault_name'] + colpad),
                    print >> outfile, "(  %6u  )" % row['fault_number']
                    currentFaultPrefix = baseFaultName

            defineName = row['device_name'] + "_" + row['fault_name'] + "__INDEX"
            print >> outfile, "#define " + defineName.ljust(colWidth['full_fault_name'] + colpad),
            print >> outfile, "(  %6u  )" % row['fault_number']
    


    

    #     
    # Names
    #
    print >> outfile, "\n\n\n"
    print >> outfile, "/****************************************************************************************"
    print >> outfile, " * Names"
    print >> outfile, " ***************************************************************************************/\n"
    
    for group in groups:
        print >> outfile, "\n/* %s */" % group['comment']
        cursor.execute(group['query'])
        for row in cursor:
            defineName = row['device_name'] + "_" + row['fault_name']
            print >> outfile, "#define " + defineName.ljust(colWidth['full_fault_name'] + colpad),
            print >> outfile, "GET_DEVICE_STATE_FOR(  " +  (defineName + "__INDEX").ljust(colWidth['full_fault_name']) + "  )"





    #     
    # States
    #
    print >> outfile, "\n\n\n"
    print >> outfile, "/****************************************************************************************"
    print >> outfile, " * States"
    print >> outfile, " ***************************************************************************************/\n"

    for group in groups:
        print >> outfile, "\n/* %s */" % group['comment']
        cursor.execute(group['query'])
        for row in cursor:
            defineName = row['device_name'] + "_" + row['fault_name']

            print >> outfile, "#define " + (defineName + "_" + row['faulted_state_name']).ljust(colWidth['full_fault_name_with_state'] + colpad),
            print >> outfile, "(!(  " + defineName.ljust(colWidth['full_fault_name']) + "  ))"

            print >> outfile, "#define " + (defineName + "_" + row['ok_state_name']     ).ljust(colWidth['full_fault_name_with_state'] + colpad),
            print >> outfile, "  (  " + defineName.ljust(colWidth['full_fault_name']) + "  )"
            print >> outfile, ""

    print >> outfile, "#endif"
    outfile.close()



def export_dump_file(dbfile, path, verbose):
    """Write dump file to path"""

    if verbose:
        print "Exporting dump file to", path
    
    try:
        outfile = open(path, 'w')
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (path, e.strerror))
        return 1

    os.popen('echo \'.dump\' | sqlite3 %r > %r ' % (dbfile, path))



def export_csv_files(dbfile, path, verbose):
    """Write csv files to the folder specified by path"""

    if verbose:
        print "Exporting CSV files to directory", path
    
    extension = '.csv'
    tables = (
                { 'name'    : 'Link Processor Table',
                  'path'    : os.path.join(path, 'LinkProcessor%s' % extension),
                  'outfile' : None,
                  'query'   : """select
                                    fault_number,
                                    is_autorecoverable,
                                    
                                    device_name,
                                    fault_name,
                                    fault_id,
                                    pv_device_type,
                                    pv_area,
                                    pv_position,
                                    pv_attribute,
                                    ok_state_name,
                                    faulted_state_name,
                                    position_x,
                                    position_y,
                                    position_z,
                                    description,
                                    area
                                from link_processor_fault
                                order by device_name, fault_name;"""
                },
                { 'name'    : 'Link Node Table',
                  'path'    : os.path.join(path, 'LinkNode%s' % extension),
                  'outfile' : None,
                  'query'   : """select
                                    fault_number,
                                    is_autorecoverable,
                                    link_node_id,
                                    link_node_type_enum,
                                    fault_id,

                                    device_name,
                                    fault_name,
                                    pv_device_type,
                                    pv_area,
                                    pv_position,
                                    pv_attribute,
                                    ok_state_name,
                                    faulted_state_name,
                                    link_node_type_name,
                                    description,
                                    area
                                from link_node_fault
                                order by link_node_id, device_name, fault_name;"""
                },
                { 'name'    : 'Link Node Channel Configuration Table',
                  'path'    : os.path.join(path, 'LinkNodeChannel%s' % extension),
                  'outfile' : None,
                  'query'   : """select
                                    fault_number,

                                    link_node_id,
                                    card,
                                    channel,
                                    is_autorecoverable,
                                    is_deadman,
                                    debounce_enum,
                                    debounce_time,

                                    device_name,
                                    fault_name,
                                    fault_id,
                                    pv_device_type,
                                    pv_area,
                                    pv_position,
                                    pv_attribute,
                                    ok_state_name,
                                    faulted_state_name,
                                    debounce_name,
                                    position_x,
                                    position_y,
                                    position_z,
                                    cable,
                                    description,
                                    area
                                from link_node_channel_fault
                                order by link_node_id, card, channel;"""
                },
                { 'name'    : 'EPICS Fault Configuration Table',
                  'path'    : os.path.join(path, 'EpicsFault%s' % extension),
                  'outfile' : None,
                  'query'   : """select
                                    fault_number,
                                    is_autorecoverable,

                                    input_pv,

                                    device_name,
                                    fault_name,

                                    fault_id,
                                    pv_device_type,
                                    pv_area,
                                    pv_position,
                                    pv_attribute,
                                    ok_state_name,
                                    faulted_state_name,
                                    position_x,
                                    position_y,
                                    position_z,
                                    description,
                                    area
                                from epics_fault
                                order by input_pv, device_name, fault_name;"""
                },
             )
    
    
    # See if all output files are writable
    for table in tables:
        try:
            table['outfile'] = open(table['path'], 'w')
        except IOError, e:
            sys.stderr.write('Could not open "%s": %s\n' % (table['path'], e.strerror))
            return 1
    
    
    for table in tables:
        if verbose:
            print "Exporting CSV file:", table['name'], "to", table['path']
        # Setup database to output as csv with headers
        command = 'sqlite3 %r' % (dbfile)
        stdin, stdout = os.popen2(command)
        stdin.write('.mode csv\n')
        stdin.write('.headers on\n')
        stdin.write(table['query'])
        stdin.close()
        print >> table['outfile'], '#',
        print >> table['outfile'], stdout.read()
        table['outfile'].close()



def export_html_file(dbfile, path, verbose):
    """Write html file to path"""

    if verbose:
        print "Exporting HTML file to", path
    
    try:
        outfile = open(path, "w")
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (path, e.strerror))
        return 1
    
    command = "sqlite3 -html -header %r" % (dbfile)

    stdin, stdout = os.popen2(command)
    stdin.write(MpsConfigurationQuery.link_node_channel_faults)
    stdin.close()
    
    print >> outfile, """
<html>
<head>
<style>
body {
    font: 10px Monaco, Courier, monospace;
}
#content {
    margin: 2em 5em;
}
table {
    border-top:   2px solid black;
    border-bottom:2px solid black;
    border-collapse:collapse;
}

th {
    border-bottom: 1px solid black;
}

tr {
    text-align:center;
}

tr:nth-child(even) {
    background:#eef3ff;
}

td, th {
    white-space: nowrap;
    padding-left: 0.5em;
    padding-right: 0.5em;
}

td:nth-child(13n+1),
td:nth-child(13n+6),
td:nth-child(13n+7),
td:nth-child(13n+8),
td:nth-child(13n+12),
td:nth-child(13n+13)
{
    text-align:left;
}

</style>
</head>
<body>
<div id="content">
<table>
"""        

    print >> outfile, stdout.read()   
    
    print >> outfile, """
</table>
</div>
</body>
</html>
"""
    outfile.close()


def export_edm_files(cursor, path, verbose):    
    """Write EDM files to the folder specified by path"""
    
    headerTemplate = string.Template("""4 0 1
beginScreenProperties
major 4
minor 0
release 1
x 150
y 150
w 748
h $window_size
font "helvetica-medium-r-10.0"
ctlFont "helvetica-medium-r-10.0"
btnFont "helvetica-medium-r-10.0"
fgColor index 14
bgColor index 5
textColor index 14
ctlFgColor1 index 25
ctlFgColor2 index 18
ctlBgColor1 index 4
ctlBgColor2 index 4
topShadowColor index 1
botShadowColor index 11
title "Link Node $linkNodeName"
showGrid
snapToGrid
gridSize 4
endScreenProperties

# (Rectangle)
object activeRectangleClass
beginObjectProperties
major 4
minor 0
release 0
x 16
y 20
w 712
h $box_size
lineColor index 3
fill
fillColor index 3
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 16
y 12
w 160
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 3
value {
  "Link Node $linkNodeName"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 44
y 48
w 44
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Card"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 88
y 48
w 60
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Channel"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 148
y 48
w 172
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Device Name"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 320
y 48
w 92
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Fault Name"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 402
y 48
w 100
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Current State"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 512
y 48
w 100
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Bypass Value"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 612
y 48
w 88
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Bypass Status"
}
endObjectProperties
""")
    
    rowTemplate = string.Template("""
# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 44
y $y
w 44
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 16
bgColor index 11
value {
  "$card"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 88
y $y
w 60
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 16
bgColor index 11
value {
  "$channel"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 148
y $y
w 172
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 16
bgColor index 11
value {
  "$device_name"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 320
y $y
w 92
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 16
bgColor index 11
value {
  "$fault_name"
}
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 412
y $y
w 100
h 16
controlPv "$status_pv"
precision 1
fgColor index 16
fgAlarm
bgColor index 11
fill
font "helvetica-medium-r-12.0"
fontAlign "center"
lineWidth 0
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 512
y $y
w 100
h 16
controlPv "$bypass_value_pv"
precision 1
fgColor index 16
fgAlarm
bgColor index 11
fill
font "helvetica-medium-r-12.0"
fontAlign "center"
lineWidth 0
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 612
y $y
w 88
h 16
controlPv "$bypass_status_pv"
precision 1
fgColor index 16
fgAlarm
bgColor index 11
fill
font "helvetica-medium-r-12.0"
fontAlign "center"
lineWidth 0
endObjectProperties
""")

    if verbose:
        print "Exporting EDM files to", path
    
    row_height = 16
    current_lnid = 0
    outfile = None
    
    # Find the number of channels for each link node
    link_node_channel_count = {}
    cursor.execute("select link_node_id, count(*) number_of_channels from link_node_channel_fault group by link_node_id")
    for row in cursor:
        link_node_channel_count[str(row['link_node_id'])] = row['number_of_channels']
    
    # Write out the files
    cursor.execute(MpsConfigurationQuery.link_node_channel_faults + " order by link_node_id, card, channel")
    for row in cursor:
        if row['link_node_id'] != current_lnid:

            current_lnid = row['link_node_id']

            # Close current file
            if outfile:
                outfile.close()

            # Get the number of input channels for this link node
            number_of_channels = link_node_channel_count[str(row['link_node_id'])]
            window_size = 106 + number_of_channels * row_height
            box_size = 64 + number_of_channels * row_height

            # Open new file
            file_name = 'LinkNode%d.edl' % row['link_node_id']
            file_path = os.path.join(path, file_name)
            try:
                outfile = open(file_path, 'w')
            except IOError, e:
                sys.stderr.write('Could not open "%s": %s\n' % (file_path, e.strerror))
                return 1
            
            if verbose:
                print "Exporting", file_path
            
            # Print header
            print >> outfile, headerTemplate.substitute(linkNodeName = current_lnid, window_size = window_size, box_size = box_size)
            y = 68
            
        pv_prefix = '%s:%s:%s:%s' % (row['pv_device_type'], row['pv_area'], row['pv_position'], row['pv_attribute'])
        # Print row
        print >> outfile, rowTemplate.substitute(y               = y,
                                                card             = row['card'],
                                                channel          = row['channel'],
                                                device_name      = pv_prefix, #row['device_name'],
                                                fault_name       = row['fault_name'],
                                                status_pv        = pv_prefix + "_MPS",
                                                bypass_value_pv  = pv_prefix + "_BYPV",
                                                bypass_status_pv = pv_prefix + "_BYPS")    
        y += row_height

    outfile.close()
    export_epics_fault_edm_file(cursor, path, verbose)



def export_epics_fault_edm_file(cursor, path, verbose):
    """Write EDM file showing EPICS fault status to path"""

    headerTemplate = string.Template("""4 0 1
beginScreenProperties
major 4
minor 0
release 1
x 150
y 150
w 920
h $window_size
font "helvetica-medium-r-10.0"
ctlFont "helvetica-medium-r-10.0"
btnFont "helvetica-medium-r-10.0"
fgColor index 14
bgColor index 5
textColor index 14
ctlFgColor1 index 25
ctlFgColor2 index 18
ctlBgColor1 index 4
ctlBgColor2 index 4
topShadowColor index 1
botShadowColor index 11
title "EPICS Fault Inputs"
showGrid
snapToGrid
gridSize 4
endScreenProperties

# (Rectangle)
object activeRectangleClass
beginObjectProperties
major 4
minor 0
release 0
x 16
y 20
w 884
h $box_size
lineColor index 3
fill
fillColor index 3
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 16
y 12
w 160
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 3
value {
  "EPICS Fault Inputs"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 44
y 48
w 224
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Input PV"
}
endObjectProperties


# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 268
y 48
w 172
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Device Name"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 440
y 48
w 92
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Fault Name"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 522
y 48
w 100
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Current State"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 632
y 48
w 100
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Bypass Value"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 732
y 48
w 88
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 14
bgColor index 4
useDisplayBg
value {
  "Bypass Status"
}
endObjectProperties
""")
    
    rowTemplate = string.Template("""
# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 44
y $y
w 224
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 16
bgColor index 11
value {
  "$inputpv"
}
endObjectProperties


# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 268
y $y
w 172
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 16
bgColor index 11
value {
  "$device_name"
}
endObjectProperties

# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x 440
y $y
w 92
h 16
font "helvetica-medium-r-12.0"
fontAlign "center"
fgColor index 16
bgColor index 11
value {
  "$fault_name"
}
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 532
y $y
w 100
h 16
controlPv "$status_pv"
precision 1
fgColor index 16
fgAlarm
bgColor index 11
fill
font "helvetica-medium-r-12.0"
fontAlign "center"
lineWidth 0
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 632
y $y
w 100
h 16
controlPv "$bypass_value_pv"
precision 1
fgColor index 16
fgAlarm
bgColor index 11
fill
font "helvetica-medium-r-12.0"
fontAlign "center"
lineWidth 0
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 732
y $y
w 88
h 16
controlPv "$bypass_status_pv"
precision 1
fgColor index 16
fgAlarm
bgColor index 11
fill
font "helvetica-medium-r-12.0"
fontAlign "center"
lineWidth 0
endObjectProperties

# (Related Display)
object relatedDisplayClass
beginObjectProperties
major 4
minor 2
release 0
x 820
y $y
w 52
h 16
fgColor index 14
bgColor index 4
topShadowColor index 1
botShadowColor index 11
font "helvetica-medium-r-10.0"
buttonLabel "Bypass"
numPvs 4
numDsps 1
displayFileName {
  0 "mps_bypass_control"
}
menuLabel {
  0 "Bypass"
}
symbols {
  0 "PV=$pv_prefix"
}
endObjectProperties

""")

    row_height = 16

    # Open new file
    file_path = os.path.join(path, 'EPICSFaultInputs.edl')
    try:
        outfile = open(file_path, 'w')
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (file_path, e.strerror))
        return 1
    
    if verbose:
        print "Exporting", file_path
    
    # Find the number of epics faults
    cursor.execute("select count(*) count from epics_fault")
    count_of_channels = cursor.fetchone()
    
    if not count_of_channels:
        return 1    
    
    number_of_channels = int(count_of_channels['count'])
    window_size = 106 + number_of_channels * row_height
    box_size = 64  + number_of_channels * row_height
            
    # Print header
    print >> outfile, headerTemplate.substitute(window_size = window_size, box_size = box_size)
    y = 68

    # Write out the files
    cursor.execute(MpsConfigurationQuery.epics_faults + " order by area, input_pv")
    for row in cursor:
        pv_prefix = '%s:%s:%s:%s' % (row['pv_device_type'], row['pv_area'], row['pv_position'], row['pv_attribute'])
        print >> outfile, rowTemplate.substitute(y                = y,
                                                 inputpv          = row['input_pv'],
                                                 device_name      = row['device_name'],
                                                 fault_name       = row['fault_name'],
                                                 status_pv        = pv_prefix + "_MPS",
                                                 bypass_value_pv  = pv_prefix + "_BYPV",
                                                 bypass_status_pv = pv_prefix + "_BYPS",
                                                 pv_prefix        = pv_prefix)    
        y += row_height
    
    outfile.close()




def export_latex_file(cursor, path, rev_string='', embed_latex = False, verbose = False):
    """Write LaTeX file to path"""
    
    headerTemplate = string.Template("""\n\\chapter{Link Node $lnid}%$lnhostname\n\n""")
    rowTemplate = string.Template("$$\\Box$$ & $card & $channel & $pv_prefix & $fault_name \\\\")

    document_header_template = string.Template("""\\documentclass[10pt, oneside]{book}
\\usepackage{lcls-article}
\\usepackage{longtable}

\\newcommand\\code{\\begingroup \\urlstyle{tt}\\Url}
\\renewcommand\\revnum{$rev_string}

\\title{MPS Link Node Inputs:\\\\$rev_string}
\\date{}

\\begin{document}

\\maketitle

\\addtocounter{page}{1}

\\tableofcontents
""")
    
    document_footer = """\end{document}"""

    table_header = """
\\begin{longtable}{@{}cccll@{}}
\\toprule
\\multicolumn{1}{@{}c}{Verified} & \\multicolumn{1}{c}{Card} & \\multicolumn{1}{c}{Channel} & \\multicolumn{1}{c}{PV Prefix} & \\multicolumn{1}{c@{}}{Fault Name} \\\\
\\midrule
\\endfirsthead


\multicolumn{5}{c}{\\ldots continued from previous page} \\\\
\\midrule
\\multicolumn{1}{@{}c}{Verified} & \\multicolumn{1}{c}{Card} & \\multicolumn{1}{c}{Channel} & \\multicolumn{1}{c}{PV Prefix} & \\multicolumn{1}{c@{}}{Fault Name} \\\\
\\midrule
\\endhead

\\midrule
\\multicolumn{5}{c}{{continued on next page\\ldots}} \\\\
\\endfoot

\\bottomrule
\\endlastfoot

"""

    table_footer = """
\\end{longtable}
"""


    
    # Open new file
    try:
        outfile = open(path, 'w')
    except IOError, e:
        sys.stderr.write('Could not open "%s": %s\n' % (path, e.strerror))
        return 1
    
    if verbose:
        print "Exporting", path
    
    if not embed_latex:
        print >> outfile, document_header_template.substitute(rev_string = rev_string)

    # Write out the files
    lnid = None
    cursor.execute(MpsConfigurationQuery.link_node_channel_faults + " order by link_node_id, card, channel")
    for row in cursor:
    
        if lnid != row['link_node_id']:            
            if lnid != None:
                print >> outfile, table_footer
            lnid = row['link_node_id']
            lnhostname = "---"
            print >> outfile, headerTemplate.substitute(lnid = lnid, lnhostname = lnhostname)
            print >> outfile, table_header

        card        = row['card']
        channel     = row['channel']
        device_name = row['device_name'].replace("_", "\\_")
        fault_name  = row['fault_name'].replace("_", "\\_")
        pv_prefix    = '%s:%s:%s:%s' % (row['pv_device_type'], row['pv_area'], row['pv_position'], row['pv_attribute'])
        pv_prefix    = pv_prefix.replace("_", "\\_")
        
        print >> outfile, rowTemplate.substitute(card = card, channel = channel, fault_name = fault_name, pv_prefix = pv_prefix)

    print >> outfile, table_footer
    
    if not embed_latex:
        print >> outfile, document_footer


            
    outfile.close()



def get_max_column_lengths(cursor):
    """Return a dictionary containing the count of the longest piece of data for column"""

    column_len = {}

    # Get the max lengths of text columns from device
    query = """
        select
            max(device_name_lengths)        device_name,
            max(pv_position_lengths)	    pv_position
        from 
            (   select
                    length(device.ZNAME)                device_name_lengths,
                    length(device.ZPVPOSITION)          pv_position_lengths
                from
                    ZDEVICE         device
            );"""
    
    try:
        cursor.execute(query)
        result = cursor.fetchone()
    except sqlite3.Error, e:
        print "get_max_column_lengths:", e.args[0]
        sys.exit(1)
    
    column_len['device_name'] = result['device_name']
    column_len['pv_position'] = result['pv_position']
    
    # Get the max PV Device Type length
    query = """
        select
            max(pv_device_type_lengths)  pv_device_type
        from 
            (select length(pvdevt.ZNAME) pv_device_type_lengths from ZPVDEVICETYPE pvdevt);
        """
    
    try:
        cursor.execute(query)
        result = cursor.fetchone()
    except sqlite3.Error, e:
        print "get_max_column_lengths:", e.args[0]
        sys.exit(1)
    
    column_len['pv_device_type'] = result['pv_device_type']
    
    
    # Get the max PV Device Type length
    query = """
        select
            max(pv_area_lengths) pv_area
        from 
            (select length(pvarea.ZNAME) pv_area_lengths from ZPVAREA pvarea);
        """
    
    try:
        cursor.execute(query)
        result = cursor.fetchone()
    except sqlite3.Error, e:
        print "get_max_column_lengths:", e.args[0]
        sys.exit(1)
    
    column_len['pv_area'] = result['pv_area']
    
    # Get the fault name, pv attribute, ok and fault statenames, and description   
    query = """
        select
            max(fault_name_lengths)         fault_name,
            max(pv_attribute_lengths)	    pv_attribute,
            max(ok_state_name_lengths)	    ok_state_name,
            max(faulted_state_name_lengths)	faulted_state_name,
            max(description_lengths)        description
        from 
            (   select
                    length( fault.ZNAME)                pv_attribute_lengths,
                    length( fault.ZNAME)                fault_name_lengths,
                    length( fault.ZOKSTATENAME)         ok_state_name_lengths,
                    length( fault.ZFAULTEDSTATENAME)    faulted_state_name_lengths,
                    length( fault.ZDESCRIPT)            description_lengths
                from
                    ZFAULT fault
            );
    """
    
    try:
        cursor.execute(query)
        result = cursor.fetchone()
    except sqlite3.Error, e:
        print "get_max_column_lengths:", e.args[0]
        sys.exit(1)
    
    
    column_len['fault_name']         = result['fault_name']
    column_len['pv_attribute']       = result['pv_attribute']
    column_len['ok_state_name']      = result['ok_state_name']
    column_len['faulted_state_name'] = result['faulted_state_name']
    column_len['description']        = result['description']
    
        
    column_len['state_name']         = max(column_len['ok_state_name'], column_len['faulted_state_name'])
    column_len['fault_number']       = 6
    column_len['link_node_id']       = 3
    column_len['card']               = 2
    column_len['channel']            = 2
    column_len['autorecoverable']    = 1
    column_len['debounce_time_us']   = 8
    
    return column_len


    
def process_options(argv):
    """
    Return parsed command-line options found in the list of
    arguments, `argv`, or ``sys.argv[1:]`` if `argv` is `None`.
    """
    
    if argv is None:
        argv = sys.argv[1:]
    
    usage   = """Usage: %prog DATABASE EXPORT_TYPE... [options]
    
DATABASE                SQLite3 file containing MPS data
EXPORT_TYPE             One or more of the following types:
                            -s, --substitution      Substitution file
                            -m, --mapping           Mapping header file
                            -n, --state_notation    State notation program
                            -c, --csv               CSV files
                            -e, --edm               EDM files
                            -d, --dump              Dump database to text file
                            -l, --latex             LaTeX Device/fault checklist"""
                
    version = "%prog 0.9.2"
    parser = optparse.OptionParser(usage=usage, version=version)

    parser.add_option("-v", "--verbose",           action="store_true",  dest="verbose",            help="print verbose output")

    parser.add_option("-s", "--substitutions",     action="store_true",  dest="export_sub",          help=optparse.SUPPRESS_HELP)
    parser.add_option("-m", "--mapping",           action="store_true",  dest="export_mapping",      help=optparse.SUPPRESS_HELP)
    parser.add_option("-n", "--state_notation",    action="store_true",  dest="export_snl",          help=optparse.SUPPRESS_HELP)
    parser.add_option("-c", "--csv",               action="store_true",  dest="export_csv",          help=optparse.SUPPRESS_HELP)
    parser.add_option("-e", "--edm",               action="store_true",  dest="export_edm",          help=optparse.SUPPRESS_HELP)
    parser.add_option("-d", "--dump",              action="store_true",  dest="export_dump",         help=optparse.SUPPRESS_HELP)
    parser.add_option("-l", "--latex",             action="store_true",  dest="export_latex",        help=optparse.SUPPRESS_HELP)
    
    group = optparse.OptionGroup(parser, "File Export Paths", "Use the following options to set destinations of exported files.")
    group.add_option("-S", "--substitutions_dir",   action="store", type="string", dest="subdir",       metavar="FILE", help="substitutions file destination directory")
    group.add_option("-M", "--mapping_path",        action="store", type="string", dest="mappingpath",  metavar="FILE", help="mapping file path")
    group.add_option("-N", "--state_notation_path", action="store", type="string", dest="snlpath",      metavar="FILE", help="state notation program path")
    group.add_option("-C", "--csv_path",            action="store", type="string", dest="csvpath",      metavar="PATH", help="CSV file destination directory")
    group.add_option("-E", "--edm_dir",             action="store", type="string", dest="edmpath",      metavar="PATH", help="EDM file destination directory")
    group.add_option("-D", "--dump_path",           action="store", type="string", dest="dumppath",     metavar="FILE", help="Dump file path")
    group.add_option("-L", "--latex_path",          action="store", type="string", dest="latexpath",    metavar="FILE", help="LaTeX file path")
    parser.add_option_group(group)

    group = optparse.OptionGroup(parser, "LaTeX Options", "Use the following options to set destinations of exported files.")
    group.add_option("-b", "--embedlatex",      action="store_true",  dest="embed_latex",                        help="Export a LaTeX file that can be included in other documents")
    group.add_option("-r", "--latexrev",        action="store", type="string", dest="latex_rev",  metavar="REV", help="Document revision")
    parser.add_option_group(group)


    # Defaults
    parser.set_defaults(verbose        = False,
                        export_sub     = False,
                        export_mapping = False,
                        export_snl     = False,
                        export_csv     = False,
                        export_edm     = False,
                        export_dump    = False,
                        export_latex   = False,
                        embed_latex    = False,
                        latex_rev      = '')

    # Parse arguments
    (options, args) = parser.parse_args(argv)


    # Check if argument count
    has_at_least_one_export_type = (options.export_sub     or
                                    options.export_mapping or
                                    options.export_snl     or
                                    options.export_csv     or
                                    options.export_edm     or
                                    options.export_dump    or
                                    options.export_latex)

    if (len(args) != 1) or (not has_at_least_one_export_type):
        parser.error("incorrect number of arguments")
    
    # Paths
    options.dbfile = os.path.normcase(args[0])
    dbbase, dbext = os.path.splitext(options.dbfile)
    dbdir = os.path.dirname(options.dbfile)

    options.substitutions_path = os.path.normcase( options.subdir      or dbdir                  )
    options.mapping_path       = os.path.normcase( options.mappingpath or dbbase + ".h"          )
    options.snl_path           = os.path.normcase( options.snlpath     or dbbase + ".stt"        )
    options.csv_path           = os.path.normcase( options.csvpath     or dbdir                  )
    options.edm_path           = os.path.normcase( options.edmpath     or dbdir                  )
    options.dump_path          = os.path.normcase( options.dumppath    or dbbase + ".dump.txt"   )
    options.latex_path         = os.path.normcase( options.latexpath   or dbbase + ".tex"        )

    return options 



def main(argv=None):
    options = process_options(argv)
        
    dbfile = options.dbfile
    
    # Check if the dbfile exists
    if not os.path.isfile(dbfile):
        sys.stderr.write('Error: database "%s" does not exist\n' % dbfile)
        return 1

    if options.export_csv: export_csv_files(dbfile, options.csv_path, options.verbose)
    if options.export_dump: export_dump_file(dbfile, options.dump_path, options.verbose)

    if (options.export_sub or options.export_mapping or options.export_snl or options.export_edm or options.export_latex):
        # Connect to database
        connection = sqlite3.connect(dbfile)
        connection.row_factory = sqlite3.Row
        cursor = connection.cursor()
            
        if options.export_sub: export_substitution_file(cursor, options.substitutions_path, options.verbose)
        if options.export_mapping: export_mapping_file(cursor, options.mapping_path, options.verbose)
        if options.export_snl: export_snl_program(cursor, options.snl_path, options.verbose)
        if options.export_edm: export_edm_files(cursor, options.edm_path, options.verbose)
        if options.export_latex: export_latex_file(cursor, options.latex_path, options.latex_rev, options.embed_latex, options.verbose)
    
        connection.close()
    
    return 0



if __name__ == "__main__":
    status = main()
    sys.exit(status)



