#!/usr/bin/env python
#---------------------------------------------------------------------
# Imports
#---------------------------------------------------------------------

import sys
import os
import sqlite3
from optparse import OptionParser, OptionGroup, SUPPRESS_HELP



#---------------------------------------------------------------------
# Common Queries
#---------------------------------------------------------------------


faultCount      = 'select count(*) count from fault_number'
epicsFaultCount = 'select count(*) count from epics_fault;'    


linkNodeChannelFaultQuery = 'select * from link_node_channel_fault'
# 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

epicsFaultQuery = 'select * from epics_fault'
# 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


linkNodeFaultQuery = 'select * from link_node_fault'
# 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


linkProcessorFaultQuery = 'select * from link_processor_fault'
# 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






#---------------------------------------------------------------------
# Export Substitution File
#---------------------------------------------------------------------

def exportSnlProggram(cursor, path, verbose):
    from string import Template
    from time   import ctime


    ## State notation language file templates ##
    sttHeaderPart1 = Template("""/*
 * Implementation of $programName.
 */
 
program $programName
option -c;

%%#include "MachineStateBuffer.h"

int     v[$faultCount];""")

    sttHeaderPart2 = Template("""assign  v[$i] to "$pv";""")
    sttHeaderPart3 = """monitor v;"""
    
    stateSet = Template("""
/* State set for PV $i ($pv), fault number $fault_number */
ss epicsFaultStateSet$i
{    
    state init
    {
        when() {
            epicsFaultStateMakeFaulted($fault_number);
        } state low
    }

    state low
    {
        when((pvConnected(v[$i])) && (v[$i] != 0)) {
            epicsFaultStateMakeOk($fault_number);
        } state high
        when(delay(.1)) {} state low
    }
    
    state high
    {
        when(!pvConnected(v[$i])) {
            epicsFaultStateMakeFaulted($fault_number);
        } state low
              
        when(v[$i] == 0) {
            epicsFaultStateMakeFaulted($fault_number);
        } state low
     
        when(delay(.1)) {} state high
    }
}\n\n""")


    try:
        sttFile = open(path, 'w')
    except IOError, e:
        sys.stderr.write('Could not open "' + path + '": ' + e.strerror + "\n")
        return 1

    
    try:
        cursor.execute(epicsFaultCount)
        count = cursor.fetchone()
    except sqlite3.Error, e:
        print "exportSnlProggram:", e.args[0]
        sys.exit(1)

    numberOfEpicsFaults = count['count']

    query = epicsFaultQuery + ' order by fault_number'
    programName = "mpsEpicsFaultInputs"

    ## Create the stt file ##
    if verbose:
        print "Creating program, \"" + programName + "\", at " + path

    # Header Part 1
    print >> sttFile, sttHeaderPart1.substitute(programName = programName, faultCount = numberOfEpicsFaults)
    
    # Header Part 2
    i = 0
    cursor.execute(query)
    for row in cursor:
        print >> sttFile, sttHeaderPart2.substitute(i = i, pv = row['input_pv'])
        i += 1

    # Header Part 3
    print >> sttFile, sttHeaderPart3

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















#---------------------------------------------------------------------
# Export Substitution File
#---------------------------------------------------------------------

def exportSubstitutionFile(cursor, path, verbose):

    colsep   = "  , "
    colWidth = {}

    #
    # MPS Input State Buffers
    #

    filePath = path + "/mpsBufferStatus.substitutions"

    if verbose:
        print "Exporting MPS Buffer Status substitutions file to", filePath
    
    try:
        outfile = open(filePath, "w")
    except IOError, e:
        sys.stderr.write('Could not open "' + filePath + '": ' + e.strerror + "\n")
        return 1
    
            
    ports = (
                #
                # Link Processor Faults
                #
                {
                    'query'             : linkProcessorFaultQuery + ' 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'             : linkProcessorFaultQuery + ' 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'             : epicsFaultQuery + ' 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'             : epicsFaultQuery + ' 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'             : linkNodeFaultQuery + ' 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'             : linkNodeFaultQuery + ' 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'             : linkNodeChannelFaultQuery + ' 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'             : linkNodeChannelFaultQuery + ' 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
                }
            )
    
    columnLengths = getMaxColumnLengths(cursor)
    colWidth['PORT']            = max(len('PORT'),          32                                  )
    colWidth['DEVICETYPE']      = max(len('DEVICETYPE'),    columnLengths['pv_device_type']     )
    colWidth['AREA']            = max(len('AREA'),          columnLengths['pv_area']            )
    colWidth['POSITION']        = max(len('POSITION'),      columnLengths['pv_position']        )
    colWidth['FAULTNUM']        = max(len('FAULTNUM'),      columnLengths['fault_number']       )
    colWidth['ZNAM']            = max(len('ZNAM'),          columnLengths['faulted_state_name'] )
    colWidth['ONAM']            = max(len('ONAM'),          columnLengths['ok_state_name']      )
    colWidth['DESC']            = max(len('DESC'),          columnLengths['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'),     columnLengths['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,
            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
    #

    filePath = path + "/mpsBufferControl.substitutions"

    if verbose:
        print "Exporting MPS Buffer Control substitutions file to", filePath
    
    try:
        outfile = open(filePath, "w")
    except IOError, e:
        sys.stderr.write('Could not open "' + filePath + '": ' + e.strerror + "\n")
        return 1
    
            
    ports = (
                #
                # Link Processor Faults
                #
                {
                    'query'             : linkProcessorFaultQuery + ' 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'             : epicsFaultQuery + ' 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'             : linkNodeFaultQuery + ' 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'             : linkNodeChannelFaultQuery + ' 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
                }
            )
    
    columnLengths = getMaxColumnLengths(cursor)
    colWidth['PORT']            = max(len('PORT'),          32                                  )
    colWidth['DEVICETYPE']      = max(len('DEVICETYPE'),    columnLengths['pv_device_type']     )
    colWidth['AREA']            = max(len('AREA'),          columnLengths['pv_area']            )
    colWidth['POSITION']        = max(len('POSITION'),      columnLengths['pv_position']        )
    colWidth['FAULTNUM']        = max(len('FAULTNUM'),      columnLengths['fault_number']       )
    colWidth['ZNAM']            = max(len('ZNAM'),          columnLengths['faulted_state_name'] )
    colWidth['ONAM']            = max(len('ONAM'),          columnLengths['ok_state_name']      )
    colWidth['DESC']            = max(len('DESC'),          columnLengths['device_name']        )
    colWidth['BUFFERID']        = max(len('BUFFERID'),      1                                   )
    colWidth['INDEX']           = max(len('INDEX'),         8                                   )

    for port in ports:
        colWidth['ATTRIBUTE']   = max(len('ATTRIBUTE'),     columnLengths['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
    #

    filePath = path + "/mpsUnlatchControl.substitutions"

    if verbose:
        print "Exporting MPS Unlatch Control substitutions file to", filePath
    
    try:
        outfile = open(filePath, "w")
    except IOError, e:
        sys.stderr.write('Could not open "' + filePath + '": ' + e.strerror + "\n")
        return 1
    
            
    ports = (
                #
                # Link Processor Faults
                #
                {
                    'query'             : linkProcessorFaultQuery + ' 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'             : epicsFaultQuery + ' 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'             : linkNodeFaultQuery + ' 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'             : linkNodeChannelFaultQuery + ' 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"

    columnLengths = getMaxColumnLengths(cursor)
    colWidth['PORT']            = max(len('PORT'),          32                                  )
    colWidth['DEVICETYPE']      = max(len('DEVICETYPE'),    columnLengths['pv_device_type']     )
    colWidth['AREA']            = max(len('AREA'),          columnLengths['pv_area']            )
    colWidth['POSITION']        = max(len('POSITION'),      columnLengths['pv_position']        )
    colWidth['FAULTNUM']        = max(len('FAULTNUM'),      columnLengths['fault_number']       )
    colWidth['ZNAM']            = max(len('ZNAM'),          len(unlatchZnam)                    )
    colWidth['ONAM']            = max(len('ONAM'),          len(unlatchOnam)                    )
    colWidth['DESC']            = max(len('DESC'),          columnLengths['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'),     columnLengths['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
    #
    filePath = path + "/mpsBypassControl.substitutions"

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

    ports = (
                {
                    'query'             : linkProcessorFaultQuery + ' order by device_name, fault_name',
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : epicsFaultQuery + ' order by device_name, fault_name',
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : linkNodeFaultQuery + ' order by link_node_id, device_name, fault_name',
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                    'isLinkNodeFault'   : True
                },
                {
                    'query'             : linkNodeChannelFaultQuery + ' 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'),     columnLengths['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
    #
    filePath = path + "/mpsBypassReadbackArray.substitutions"

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

    try:
        cursor.execute(faultCount)
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "exportSnlProggram:", 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()
            



























#---------------------------------------------------------------------
# Export Mapping File
#---------------------------------------------------------------------

def exportMappingFile(cursor, path, verbose):

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

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


    groups = (
                {
                    'query'             : linkProcessorFaultQuery + ' order by fault_number',
                    'comment'           : "Link Processor Faults",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : epicsFaultQuery + ' order by fault_number',
                    'comment'           : "EPICS Faults",
                    'isLinkNodeFault'   : False
                },
                {
                    'query'             : linkNodeFaultQuery + ' order by fault_number',
                    'comment'           : "Link Node Faults",
                    'isLinkNodeFault'   : True
                },
                {
                    'query'             : linkNodeChannelFaultQuery + ' 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(faultCount)
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "exportSnlProggram:", 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(epicsFaultCount)
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "exportSnlProggram:", e.args[0]
        sys.exit(1)

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

    try:
        cursor.execute(epicsFaultQuery + ' order by fault_number')
        results = cursor.fetchone()
    except sqlite3.Error, e:
        print "exportSnlProggram:", 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()
















#---------------------------------------------------------------------
# Export Dump File
#---------------------------------------------------------------------

def exportDumpFile(dbfile, path, verbose):

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

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
























#---------------------------------------------------------------------
# Export CSV Files
#---------------------------------------------------------------------

def exportCsvFiles(dbfile, path, verbose):

    if verbose:
        print "Exporting CSV files to directory", path
    
    extension = '.csv'
    tables = (
                { 'name'    : 'Link Processor Table',
                  'path'    : path + '/LinkProcessor' + 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'    : path + '/LinkNode' + 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'    : path + '/LinkNodeChannel' + 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'    : path + '/EpicsFault' + 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 "' + table['path'] + '": ' + e.strerror + '\n')
            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()






























#---------------------------------------------------------------------
# Export WWW File
#---------------------------------------------------------------------

def exportWwwFile(dbfile, path, verbose):

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

    stdin, stdout = os.popen2(command)
    stdin.write(linkNodeChannelFaultQuery)
    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()






























#---------------------------------------------------------------------
# Export EDM Files
#---------------------------------------------------------------------

def exportEdmFiles(cursor, path, verbose):
    from string import Template
    
    headerTemplate = Template("""4 0 1
beginScreenProperties
major 4
minor 0
release 1
x 150
y 150
w 748
h $windowSize
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 $boxSize
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 = 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 {
  "$deviceName"
}
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 {
  "$faultName"
}
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 412
y $y
w 100
h 16
controlPv "$statusPv"
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 "$bypassValuePv"
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 "$bypassStatusPv"
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
    
    currentLnId = 0
    outfile    = ''
    
    # 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(linkNodeChannelFaultQuery + " order by link_node_id, card, channel")
    for row in cursor:
        if row['link_node_id'] != currentLnId:

            currentLnId = 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'])]
            windowSize        = 106 + number_of_channels * 16
            boxSize           = 64  + number_of_channels * 16

            # Open new file
            file = path + '/LinkNode' + str(row['link_node_id']) + '.edl'
            try:
                outfile = open(file, 'w')
            except IOError, e:
                sys.stderr.write('Could not open "' + file + '": ' + e.strerror + "\n")
                return 1
            
            if verbose:
                print "Exporting", file
            
            # Print header
            print >> outfile, headerTemplate.substitute(linkNodeName = currentLnId, windowSize = windowSize, boxSize = boxSize)
            y = 68
            
            
        pvPrefix = 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'],
                                                deviceName     = pvPrefix, #row['device_name'],
                                                faultName      = row['fault_name'],
                                                statusPv       = pvPrefix + "_MPS",
                                                bypassValuePv  = pvPrefix + "_BYPV",
                                                bypassStatusPv = pvPrefix + "_BYPS")    
        
        y += 16
    

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


















#---------------------------------------------------------------------
# Export EDM Files
#---------------------------------------------------------------------

def exportEpicsFaultEdmFile(cursor, path, verbose):
    from string import Template
    
    headerTemplate = Template("""4 0 1
beginScreenProperties
major 4
minor 0
release 1
x 150
y 150
w 920
h $windowSize
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 $boxSize
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 = 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 {
  "$deviceName"
}
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 {
  "$faultName"
}
endObjectProperties

# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x 532
y $y
w 100
h 16
controlPv "$statusPv"
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 "$bypassValuePv"
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 "$bypassStatusPv"
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=$pvprefix"
}
endObjectProperties

""")


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

    # Write out the files
    cursor.execute(epicsFaultQuery + " order by area, input_pv")
    for row in cursor:

        pvPrefix = row['pv_device_type'] + ":" + row['pv_area'] + ":" + row['pv_position'] + ":" + row['pv_attribute']
        # Print row
        print >> outfile, rowTemplate.substitute(y             = y,
                                                 inputpv        = row['input_pv'],
                                                 deviceName     = row['device_name'],
                                                 faultName      = row['fault_name'],
                                                 statusPv       = pvPrefix + "_MPS",
                                                 bypassValuePv  = pvPrefix + "_BYPV",
                                                 bypassStatusPv = pvPrefix + "_BYPS",
                                                 pvprefix       = pvPrefix)    
         
        y += 16
    
    outfile.close()







def exportLatexFileOld(cursor, path, embedLatex, verbose):
    from string import Template
    
    headerTemplate = Template("""\n\\section{Link Node $lnid}%$lnhostname\n\n""")
    rowTemplate = Template("$card & $channel & $device_name & $fault_name & $pvPrefix \\\\")

    tableHeader = """
\\begin{longtable}{@{}cclll@{}}
\\toprule
\\multicolumn{1}{@{}c}{Card} & \\multicolumn{1}{c}{Channel} & \\multicolumn{1}{c}{Device Name} & \\multicolumn{1}{c}{Fault Name} & \\multicolumn{1}{c@{}}{PV Prefix}\\\\
\\midrule
\\endfirsthead


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

\\midrule
\\multicolumn{5}{r}{{Continued on next page\\ldots}} \\\\
\\endfoot

\\bottomrule
\\endlastfoot

"""
    tableFooter = """
\\end{longtable}
"""


    
    # Open new file
    try:
        outfile = open(path, 'w')
    except IOError, e:
        sys.stderr.write('Could not open "' + path + '": ' + e.strerror + "\n")
        return 1
    
    if verbose:
        print "Exporting", path
    
    lnid = None
    # Write out the files
    cursor.execute(linkNodeChannelFaultQuery + " order by link_node_id, card, channel")
    for row in cursor:
    
        if lnid != row['link_node_id']:            
            if lnid != None:
                print >> outfile, tableFooter
            lnid = row['link_node_id']
            lnhostname = "---"
            print >> outfile, headerTemplate.substitute(lnid = lnid, lnhostname = lnhostname)
            print >> outfile, tableHeader

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

    print >> outfile, tableFooter
            
    outfile.close()










def exportLatexFile(cursor, path, embedLatex, verbose):
    from string import Template
    
    headerTemplate = Template("""\n\\section{Link Node $lnid}%$lnhostname\n\n""")
    rowTemplate = Template("$card & $channel & $pvPrefix & $fault_name \\\\")

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


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

\\midrule
\\multicolumn{4}{c}{{Continued on next page\\ldots}} \\\\
\\endfoot

\\bottomrule
\\endlastfoot

"""
    tableFooter = """
\\end{longtable}
"""


    
    # Open new file
    try:
        outfile = open(path, 'w')
    except IOError, e:
        sys.stderr.write('Could not open "' + path + '": ' + e.strerror + "\n")
        return 1
    
    if verbose:
        print "Exporting", path
    
    lnid = None
    # Write out the files
    cursor.execute(linkNodeChannelFaultQuery + " order by link_node_id, card, channel")
    for row in cursor:
    
        if lnid != row['link_node_id']:            
            if lnid != None:
                print >> outfile, tableFooter
            lnid = row['link_node_id']
            lnhostname = "---"
            print >> outfile, headerTemplate.substitute(lnid = lnid, lnhostname = lnhostname)
            print >> outfile, tableHeader

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

    print >> outfile, tableFooter
            
    outfile.close()










#---------------------------------------------------------------------
# Helper Functions
#---------------------------------------------------------------------


def getMaxColumnLengths(cursor):

    # 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 "getMaxColumnLengths:", e.args[0]
        sys.exit(1)
    
    columnLengths = result
    
    # 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 "getMaxColumnLengths:", e.args[0]
        sys.exit(1)
    
    columnLengths['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 "getMaxColumnLengths:", e.args[0]
        sys.exit(1)
    
    columnLengths['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 "getMaxColumnLengths:", e.args[0]
        sys.exit(1)
    
    
    columnLengths['fault_name']         = result['fault_name']
    columnLengths['pv_attribute']       = result['pv_attribute']
    columnLengths['ok_state_name']      = result['ok_state_name']
    columnLengths['faulted_state_name'] = result['faulted_state_name']
    columnLengths['description']        = result['description']
    
        
    columnLengths['state_name']         = max(columnLengths['ok_state_name'], columnLengths['faulted_state_name'])
    columnLengths['fault_number']       = 6
    columnLengths['link_node_id']       = 3
    columnLengths['card']               = 2
    columnLengths['channel']            = 2
    columnLengths['autorecoverable']    = 1
    columnLengths['debounce_time_us']   = 8
    
    return columnLengths
    
    

def dict_factory(cursor, row):
    # From http://docs.python.org/lib/sqlite3-Connection-Objects.html
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


    
def handleOptions():
    # Create parse options
    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.1"
    parser = 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="exportSub",          help=SUPPRESS_HELP)
    parser.add_option("-m", "--mapping",           action="store_true",  dest="exportMapping",      help=SUPPRESS_HELP)
    parser.add_option("-n", "--state_notation",    action="store_true",  dest="exportSnl",          help=SUPPRESS_HELP)
    parser.add_option("-c", "--csv",               action="store_true",  dest="exportCsv",          help=SUPPRESS_HELP)
    parser.add_option("-e", "--edm",               action="store_true",  dest="exportEdm",          help=SUPPRESS_HELP)
    parser.add_option("-d", "--dump",              action="store_true",  dest="exportDump",         help=SUPPRESS_HELP)
    parser.add_option("-l", "--latex",             action="store_true",  dest="exportLatex",        help=SUPPRESS_HELP)
    parser.add_option("-b", "--embedlatex",        action="store_true",  dest="embedLatex",         help=SUPPRESS_HELP)
    
    group = 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)

    # Defaults
    parser.set_defaults(verbose         = False,
                        exportSub       = False,
                        exportMapping   = False,
                        exportSnl       = False,
                        exportCsv       = False,
                        exportEdm       = False,
                        exportDump      = False,
                        exportLatex     = False,
                        embedLatex      = False)

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

    if (len(args) != 1) or ((options.exportSub          or
                             options.exportMapping      or
                             options.exportSnl          or
                             options.exportCsv          or
                             options.exportEdm          or
                             options.exportDump         or
                             options.exportLatex)       != True):
        parser.error("incorrect number of arguments")
        
    return (options, args)































#---------------------------------------------------------------------
# Main
#---------------------------------------------------------------------

def main():
    
    # Options
    (options, args) = handleOptions()
    
    # Paths
    dbfile = os.path.normcase(args[0])
    dbbase, dbext = os.path.splitext(dbfile)
    dbdir  = os.path.dirname(dbfile)

    # Check if the dbfile exists
    if os.path.isfile(dbfile) == False:
        sys.stderr.write("Error: database \"%s\" does not exist\n" % dbfile)
        return 1

    substitutionsPath = os.path.normcase( options.subdir         or dbdir                  )
    mappingPath       = os.path.normcase( options.mappingpath    or dbbase + ".h"          )
    snlPath           = os.path.normcase( options.snlpath        or dbbase + ".stt"        )
    csvPath           = os.path.normcase( options.csvpath        or dbdir                  )
    edmPath           = os.path.normcase( options.edmpath        or dbdir                  )
    dumpPath          = os.path.normcase( options.dumppath       or dbbase + ".dump.txt"   )
    latexPath         = os.path.normcase( options.latexpath      or dbbase + ".tex"        )

    
    # Connect to database
    connection             = sqlite3.connect(dbfile)
    connection.row_factory = dict_factory
    cursor                 = connection.cursor()
            
    if options.exportSub:       exportSubstitutionFile  (cursor, substitutionsPath, options.verbose)
    if options.exportMapping:   exportMappingFile       (cursor, mappingPath,       options.verbose)
    if options.exportSnl:       exportSnlProggram       (cursor, snlPath,           options.verbose)
    if options.exportCsv:       exportCsvFiles          (dbfile, csvPath,           options.verbose)
    if options.exportEdm:       exportEdmFiles          (cursor, edmPath,           options.verbose)
    if options.exportDump:      exportDumpFile          (dbfile, dumpPath,          options.verbose)
    if options.exportLatex:     exportLatexFile         (cursor, latexPath,         options.embedLatex, options.verbose)
    
    connection.close()



if __name__ == "__main__":
    main()

