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

import sys
import os
import sqlite3

from optparse import OptionParser, OptionGroup, SUPPRESS_HELP


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


validLinkNodeInputQuery = """
    select lnc.ZNAME                Name,

           lnc.ZPVDEVICETYPE        DeviceType,
           lnc.ZPVAREA              Area,
           lnc.ZPVPOSITION          Position,
           lnc.ZPVATTRIBUTE         Attribute,
           
           lnc.ZSTATE0              State0,
           lnc.ZSTATE1              State1,

           ln.ZLINKNODEID           LinkNodeID,
           lnc.ZINPUTCARD           Card,
           lnc.ZINPUTCARDCHANNEL    Channel,

           lnc.ZISAUTORECOVERABLE   IsAutorecoverable,
           lnc.ZDEADMAN             Deadman,
           d.ZTIMEINMICROSECONDS    DebounceTimeUs,
           d.ZENUM                  DebounceEnum,

           lnc.ZDESCRIPTION         Cable,

           lnc.ZDESCRIPTION         Description,
           lnc.ZLOCATION            Location
           
    from ZLINKNODEINPUTCHANNEL lnc left outer join ZLINKNODE ln left outer join ZDEBOUNCETIME d
    on lnc.ZLINKNODE = ln.Z_PK and lnc.ZDEBOUNCETIME = d.Z_PK
    where 
        Name        is not NULL and
        LinkNodeID  is not NULL and
        Card        is not NULL and
        Channel     is not NULL and
        DeviceType  is not NULL and
        Area        is not NULL and
        Position    is not NULL and
        Attribute   is not NULL and
        State0      is not NULL and
        State1      is not NULL and

        length(Name      ) > 0 and
        length(DeviceType) > 0 and
        length(Area      ) > 0 and
        length(Position  ) > 0 and
        length(Attribute ) > 0 and
        length(State0    ) > 0 and
        length(State1    ) > 0 and

        Name        not like "%!%" and
        DeviceType  not like "%!%" and
        Area        not like "%!%" and
        Position    not like "%!%" and
        Attribute   not like "%!%" and
        State0      not like "%!%" and
        State1      not like "%!%"
        
    order by LinkNodeID, Card, Channel;
"""

validLinkNodeQuery = """
    select distinct
        ln.ZLINKNODEID      LinkNodeID,
        ln.ZHOSTNAME        Hostname,
        ln.ZTYPE            Type,
        
        ln.ZPVDEVICETYPE    DeviceType,
        ln.ZPVAREA          Area,
        ln.ZPVPOSITION      Position
    
    from ZLINKNODEINPUTCHANNEL inp inner join ZLINKNODE ln
    on inp.ZLINKNODE = ln.Z_PK
    where
        Hostname   is not NULL and
        Type       is not NULL and
        LinkNodeID is not NULL and
        DeviceType is not NULL and
        Area       is not NULL and
        Position   is not NULL and
        
        length(Hostname  ) > 0 and
        length(DeviceType) > 0 and
        length(Area      ) > 0 and
        length(Position  ) > 0 and
        
        Hostname   not like "%!%" and
        DeviceType not like "%!%" and
        Area       not like "%!%" and
        Position   not like "%!%"
    
    order by LinkNodeID;
"""

validEpicsFaultInputQuery = """
    select e.ZNAME                Name,

           e.ZPVDEVICETYPE        DeviceType,
           e.ZPVAREA              Area,
           e.ZPVPOSITION          Position,
           e.ZPVATTRIBUTE         Attribute,
           
           e.ZSTATE0              State0,
           e.ZSTATE1              State1,

           e.ZFAULTNUMBER         FaultNumber,

           e.ZISAUTORECOVERABLE   IsAutorecoverable,

           e.ZDESCRIPTION         Cable,

           e.ZDESCRIPTION         Description,
           e.ZLOCATION            Location
           
    from ZEPICSFAULT e
    where 
        Name        is not NULL and
        DeviceType  is not NULL and
        Area        is not NULL and
        Position    is not NULL and
        Attribute   is not NULL and
        State0      is not NULL and
        State1      is not NULL and

        length(Name      ) > 0 and
        length(DeviceType) > 0 and
        length(Area      ) > 0 and
        length(Position  ) > 0 and
        length(Attribute ) > 0 and
        length(State0    ) > 0 and
        length(State1    ) > 0 and

        Name        not like "%!%" and
        DeviceType  not like "%!%" and
        Area        not like "%!%" and
        Position    not like "%!%" and
        Attribute   not like "%!%" and
        State0      not like "%!%" and
        State1      not like "%!%"
        
    order by FaultNumber;
"""


























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

def exportSubstitutionFile(cursor, path, verbose):

    
    colpad   = 0
    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 = (
                {
                    'query'             : validLinkNodeInputQuery,
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : "Status Buffer (Buffer ID 1)",
                    'bufferid'          : "1",
                    'prefix'            : "",
                    'suffix'            : "_MPS",
                },
                {
                    'query'             : validLinkNodeInputQuery,
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : "Bypass Active Buffer (Buffer ID 2)",
                    'bufferid'          : "2",
                    'prefix'            : "",
                    'suffix'            : "_BYPS",
                },
                {
                    'query'             : validLinkNodeInputQuery,
                    'template'          : "mpsBufferStatus.template",
                    'comment'           : "Bypass Value Buffer (Buffer ID 3)",
                    'bufferid'          : "3",
                    'prefix'            : "",
                    'suffix'            : "_BYPV",
                }
            )
    
    columnLengths = getMaxColumnLengths(cursor)
    colWidth['DEVICETYPE']      = max(len('DEVICETYPE'),    columnLengths['deviceType'] ) + colpad
    colWidth['AREA']            = max(len('AREA'),          columnLengths['area']       ) + colpad
    colWidth['POSITION']        = max(len('POSITION'),      columnLengths['position']   ) + colpad
    colWidth['DESC']            = max(len('DESC'),          columnLengths['name']       ) + colpad
    colWidth['LINKNODE']        = max(len('LINKNODE'),      3                           ) + colpad
    colWidth['CARD']            = max(len('CARD'),          1                           ) + colpad
    colWidth['CHANNEL']         = max(len('CHANNEL'),       2                           ) + colpad
    colWidth['VAL']             = max(len('VAL'),           1                           ) + colpad
    colWidth['BUFFERID']        = max(len('BUFFERID'),      1                           ) + colpad
    colWidth['INDEX']           = max(len('INDEX'),         4                           ) + colpad

    for port in ports:
        colWidth['ATTRIBUTE']   = max(len('ATTRIBUTE'),     columnLengths['attribute'] + len(port['prefix']) + len(port['suffix'])) + colpad

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

        print >> outfile, "file " + port['template']
            
        print >> outfile, "{"
        print >> outfile, "    pattern { ",
        print >> outfile, "BUFFERID"      .ljust(     colWidth['BUFFERID']     ) + 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"     .ljust(     colWidth['ATTRIBUTE']    ) + colsep,
        print >> outfile, "DESC"          .ljust(     colWidth['DESC']         ) + colsep,
        print >> outfile, "LINKNODE"      .ljust(     colWidth['LINKNODE']     ) + colsep,
        print >> outfile, "CARD"          .ljust(     colWidth['CARD']         ) + colsep,
        print >> outfile, "CHANNEL"       .ljust(     colWidth['CHANNEL']      )         ,
        print >> outfile, " }"
        
        cursor.execute(port['query'])
        for row in cursor:
            print >> outfile, "            { ",

            print >> outfile, port['bufferid']              .ljust(     colWidth['BUFFERID']   )  + colsep,
            print >> outfile, row['DeviceType']             .ljust(     colWidth['DEVICETYPE'] )  + colsep,
            print >> outfile, row['Area']                   .ljust(     colWidth['AREA']       )  + colsep,
            print >> outfile, row['Position']               .rjust(     colWidth['POSITION']   )  + colsep,
            print >> outfile, (port['prefix'] +
                               row['Attribute'] +
                               port['suffix'])              .ljust(     colWidth['ATTRIBUTE']  )  + colsep,
            print >> outfile, row['Name']                   .ljust(     colWidth['DESC']       )  + colsep,
            print >> outfile, str(row['LinkNodeID'])        .ljust(     colWidth['LINKNODE']   )  + colsep,
            print >> outfile, str(row['Card'])              .ljust(     colWidth['CARD']       )  + colsep,
            print >> outfile, str(row['Channel'])           .ljust(     colWidth['CHANNEL']    )          ,

            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'             : validLinkNodeInputQuery,
                    'template'          : "mpsBypassControl.template",
                    'comment'           : "Bypass Control",
                    'prefix'            : "",
                    'suffix'            : "_BYPC",
                },
            )

    for port in ports:
        colWidth['ATTRIBUTE']   = max(len('ATTRIBUTE'),     columnLengths['attribute'] + len(port['prefix']) + len(port['suffix'])) + colpad

        print >> outfile, "\n\n"
        print >> outfile, "#" + "-" * (len(port['comment']) * 2)
        print >> outfile, "#",  port['comment']
        print >> outfile, "#" + "-" * (len(port['comment']) * 2)
        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"     .ljust(     colWidth['ATTRIBUTE']    ) + colsep,
        print >> outfile, "DESC"          .ljust(     colWidth['DESC']         ) + colsep,
        print >> outfile, "INDEX"         .ljust(     colWidth['INDEX']        )         ,
        print >> outfile, " }"
        
        cursor.execute(port['query'])
        for row in cursor:
            index = getIndexForLinkNodeCardAndChannel(int(row['LinkNodeID']), int(row['Card']), int(row['Channel']))

            print >> outfile, "            { ",
            print >> outfile, row['DeviceType']             .ljust(     colWidth['DEVICETYPE'] )  + colsep,
            print >> outfile, row['Area']                   .ljust(     colWidth['AREA']       )  + colsep,
            print >> outfile, row['Position']               .rjust(     colWidth['POSITION']   )  + colsep,
            print >> outfile, (port['prefix'] +
                               row['Attribute'] +
                               port['suffix'])              .ljust(     colWidth['ATTRIBUTE']  )  + colsep,
            print >> outfile, row['Name']                   .ljust(     colWidth['DESC']       )  + colsep,            
            print >> outfile, str(index)                    .rjust(     colWidth['INDEX']      )          ,
            print >> outfile, " }"

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

            

































#---------------------------------------------------------------------
# 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['defineName']  = columnLengths['name'] + len("__INDEX")
    

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

    cursor.execute(validLinkNodeInputQuery)
    for row in cursor:
        print >> outfile, "#define " + (row['Name'] + "__INDEX").ljust(colWidth['defineName'] + colpad),
        print >> outfile, "INDEX_FOR( LINK_NODE(  0x%02X  )," % row['LinkNodeID'],
        print >> outfile, "CARD_AND_CHANNEL(" + str(row['Card']).rjust(3) + "," + str(row['Channel']).rjust(3) + " ))"
    
    print >> outfile
    print >> outfile, "/* EPICS Fault Inputs */"
    
    cursor.execute(validEpicsFaultInputQuery)
    for row in cursor:
        print >> outfile, "#define " + (row['Name'] + "__INDEX").ljust(colWidth['defineName'] + colpad),
        print >> outfile, "INDEX_FOR( EPICS_FAULT        ,                      %3d  )" % row['FaultNumber']
    

    print >> outfile, "\n\n\n"
    print >> outfile, "/****************************************************************************************"
    print >> outfile, " * Names"
    print >> outfile, " ***************************************************************************************/\n"

    print >> outfile, "/* Link Node Inputs */"
    
    cursor.execute(validLinkNodeInputQuery)
    for row in cursor:
        print >> outfile, "#define " + row['Name'].ljust(colWidth['defineName'] + colpad),
        print >> outfile, "GET_DEVICE_STATE_FOR(  " +  (row['Name'] + "__INDEX").ljust(columnLengths['name'] + len("__INDEX")) + "  )"

    print >> outfile
    print >> outfile, "/* EPICS Fault Inputs */"
        
    cursor.execute(validEpicsFaultInputQuery)
    for row in cursor:
        print >> outfile, "#define " + row['Name'].ljust(colWidth['defineName'] + colpad),
        print >> outfile, "GET_DEVICE_STATE_FOR(  " +  (row['Name'] + "__INDEX").ljust(columnLengths['name'] + len("__INDEX")) + "  )"
        

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

    colWidth['defineName'] = columnLengths['name'] + max(columnLengths['state0'], columnLengths['state1']) + 1
    cursor.execute(validLinkNodeInputQuery)
    for row in cursor:
        print >> outfile, "#define " + (row['Name'] + "_" + row['State0']).ljust(colWidth['defineName'] + colpad) + "(!(  " + row['Name'].ljust(columnLengths['name']) + "  ))"
        print >> outfile, "#define " + (row['Name'] + "_" + row['State1']).ljust(colWidth['defineName'] + colpad) + "  (  " + row['Name'].ljust(columnLengths['name']) + "  )"
        print >> outfile, ""

    cursor.execute(validEpicsFaultInputQuery)
    for row in cursor:
        print >> outfile, "#define " + (row['Name'] + "_" + row['State0']).ljust(colWidth['defineName'] + colpad) + "(!(  " + row['Name'].ljust(columnLengths['name']) + "  ))"
        print >> outfile, "#define " + (row['Name'] + "_" + row['State1']).ljust(colWidth['defineName'] + colpad) + "  (  " + row['Name'].ljust(columnLengths['name']) + "  )"
        print >> outfile, ""

    
    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 Node Configuration Table',
                  'path'    : path + '/LinkNodeConfig' + extension,
                  'outfile' : None,
                  'query'   : '''   select  ln.ZLINKNODEID          LinkNodeID,
                                            lnt.ZENUM               TypeEnum,
                                            ln.ZHOSTNAME            Hostname,
                                            ln.ZPVDEVICETYPE        DeviceType,
                                            ln.ZPVAREA              Area,
                                            ln.ZPVPOSITION          Position
                                    from ZLINKNODE ln left outer join ZLINKNODETYPE lnt
                                    on ln.ZTYPE = lnt.Z_PK
                                    order by LinkNodeId;'''
                },
                { 'name'    : 'Link Node Input Configuration Table',
                  'path'    : path + '/LinkNodeInputConfig' + extension,
                  'outfile' : None,
                  'query'   : '''   select  ln.ZLINKNODEID          LinkNodeID,
                                            lnc.ZINPUTCARD          Card,
                                            lnc.ZINPUTCARDCHANNEL   Channel,
                                            lnc.ZISAUTORECOVERABLE  IsAutorecoverable,
                                            lnc.ZDEADMAN            Deadman,
                                            d.ZENUM                 DebounceEnum,
                                            lnc.ZPOSITIONX          PosX,
                                            lnc.ZPOSITIONY          PosY,
                                            lnc.ZPOSITIONZ          PosZ,
                                            lnc.ZNAME               Name,
                                            lnc.ZSTATE0             State0,
                                            lnc.ZSTATE1             State1,
                                            lnc.ZPVDEVICETYPE       DeviceType,
                                            lnc.ZPVAREA             Area,
                                            lnc.ZPVPOSITION         Position,
                                            lnc.ZPVATTRIBUTE        Attribute,
                                            lnc.ZLOCATION           Location,
                                            lnc.ZDESCRIPTION        Description
                                    from ZLINKNODEINPUTCHANNEL lnc left outer join ZLINKNODE ln left outer join ZDEBOUNCETIME d
                                    on lnc.ZLINKNODE = ln.Z_PK and lnc.ZDEBOUNCETIME = d.Z_PK
                                    order by LinkNodeId, Card, Channel;'''
                },
                { 'name'    : 'EPICS Fault Configuration Table',
                  'path'    : path + '/EpicsFaultConfig' + extension,
                  'outfile' : None,
                  'query'   : '''   select  e.ZFAULTNUMBER          FaultNumber,
                                            e.ZISAUTORECOVERABLE    IsAutorecoverable,
                                            e.ZPOSITIONX            PosX,
                                            e.ZPOSITIONY            PosY,
                                            e.ZPOSITIONZ            PosZ,
                                            e.ZFAULTPV              FaultPv,
                                            e.ZNAME                 Name,
                                            e.ZSTATE0               State0,
                                            e.ZSTATE1               State1,
                                            e.ZPVDEVICETYPE         DeviceType,
                                            e.ZPVAREA               Area,
                                            e.ZPVPOSITION           Position,
                                            e.ZPVATTRIBUTE          Attribute,
                                            e.ZLOCATION             Location,
                                            e.ZDESCRIPTION          Description
                                    from ZEPICSFAULT e
                                    order by FaultNumber
                                    ;'''
                },
                { 'name'    : 'Link Node Type Table',
                  'path'    : path + '/LinkNodeType' + extension,
                  'outfile' : None,
                  'query'   : '''   select  lnt.ZENUM               TypeEnum,
                                            lnt.ZNAME               Name
                                    from ZLINKNODETYPE lnt
                                    order by TypeEnum;'''
                },
                { 'name'    : 'Link Node Input Debounce Time Table',
                  'path'    : path + '/LinkNodeInputDebounceTime' + extension,
                  'outfile' : None,
                  'query'   : '''   select  d.ZENUM                 DebounceEnum,
                                            d.ZTIMEINMICROSECONDS   TimeInMicroseconds,
                                            d.ZNAME                 Name
                                    from ZDEBOUNCETIME d
                                    order by DebounceEnum;'''
                },
             )
    
    
    # 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(validLinkNodeInputQuery)
    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
    
    header = """4 0 1
beginScreenProperties
major 4
minor 0
release 1
x 981
y 634
w 930
h 640
font "helvetica-medium-r-18.0"
ctlFont "helvetica-medium-r-18.0"
btnFont "helvetica-medium-r-18.0"
fgColor index 14
bgColor index 3
textColor index 14
ctlFgColor1 index 14
ctlFgColor2 index 0
ctlBgColor1 index 0
ctlBgColor2 index 14
topShadowColor index 0
botShadowColor index 14
showGrid
snapToGrid
gridSize 4
endScreenProperties
"""
    
    labelTemplate = Template("""
# (Static Text)
object activeXTextClass
beginObjectProperties
major 4
minor 1
release 0
x $x
y $y
w 230
h 14
font "helvetica-medium-r-12.0"
fontAlign "right"
fgColor index 14
bgColor index 0
useDisplayBg
value {
"$pv"
}
endObjectProperties
""")
    stateTemplate = Template("""
# (Textupdate)
object TextupdateClass
beginObjectProperties
major 10
minor 0
release 0
x $x
y $y
w 50
h 14
controlPv "$pv"
precision 1
fgColor index 16
fgAlarm
bgColor index 11
fill
font "helvetica-medium-r-12.0"
fontAlign "center"
lineWidth 1
lineAlarm
endObjectProperties
""")
    bypassTemplate = Template("""
# (Circle)
object activeCircleClass
beginObjectProperties
major 4
minor 0
release 0
x $x
y $y
w 12
h 12
lineColor index 14
fill
fillColor index 20
lineWidth 0
visPv "$pv"
visMin "1"
visMax "2"
endObjectProperties
""")


    if verbose:
        print "Exporting EDM files to", path
    
    currentRow = 0
    outfile    = ''
    
    cursor.execute(validLinkNodeInputQuery)
    
    for row in cursor:
        if row['LinkNodeID'] != currentRow:

            currentRow = row['LinkNodeID']
            
            # Close current file
            if outfile != '':
                outfile.close()

            # Open new file
            file = path + '/LinkNode' + str(row['LinkNodeID']) + '.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, header
            
        # Print Label
        print >> outfile, labelTemplate.substitute(x  = (row['Card']    - 1) / 2 * 300 + 16,
                                                   y  = (row['Channel'] - 1 )    * 18  + 17 + ((row['Card'] + 1) % 2 * 310),
                                                   pv = row['DeviceType'] + ":" + row['Area'] + ":" + row['Position'] + ":" + row['Attribute'])
    
        # Print State
        print >> outfile, stateTemplate.substitute(x  = (row['Card']    - 1) / 2 * 300 + 16 + 230 + 4,
                                                   y  = (row['Channel'] - 1 )    * 18  + 16 + ((row['Card'] + 1) % 2 * 310),
                                                   pv = row['DeviceType'] + ":" + row['Area'] + ":" + row['Position'] + ":" + row['Attribute'] + "_MPS")
    
        # Print Bypass Active
        print >> outfile, bypassTemplate.substitute(x  = (row['Card']    - 1) / 2 * 300 + 16 + 230 + 4 + 50 + 4,
                                                    y  = (row['Channel'] - 1 )    * 18  + 16 + ((row['Card'] + 1) % 2 * 310),
                                                    pv = row['DeviceType'] + ":" + row['Area'] + ":" + row['Position'] + ":" + row['Attribute'] + "_BYPS")
    




























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


def getMaxColumnLengths(cursor):
    # Get the max lengths of text columns
    stringColumnLengthQuery = """
        select max(lnicNameLengths)         lnicName,
               max(lnicState0Lengths)       lnicState0,
               max(lnicState1Lengths)       lnicState1,
               max(lnicDeviceTypeLengths)   lnicDeviceType,
               max(lnicAreaLengths)         lnicArea,
               max(lnicPositionLengths)     lnicPosition,
               max(lnicAttributeLengths)    lnicAttribute,
               max(lnicDescriptionLengths)  lnicDescription,
               max(lnicLocationLengths)     lnicLocation,

               max(lnHostnameLengths)       lnHostname,
               max(lnDeviceTypeLengths)     lnDeviceType,
               max(lnAreaLengths)           lnArea,
               max(lnPositionLengths)       lnPosition,
               
               max(eNameLengths)            eName,
               max(eState0Lengths)          eState0,
               max(eState1Lengths)          eState1,
               max(eDeviceTypeLengths)      eDeviceType,
               max(eAreaLengths)            eArea,
               max(ePositionLengths)        ePosition,
               max(eAttributeLengths)       eAttribute,
               max(eDescriptionLengths)     eDescription,
               max(eLocationLengths)        eLocation
        from
        (
            select length(lnic.ZNAME)           lnicNameLengths,
                   length(lnic.ZSTATE0)         lnicState0Lengths,
                   length(lnic.ZSTATE1)         lnicState1Lengths,
                   length(lnic.ZPVDEVICETYPE)   lnicDeviceTypeLengths,
                   length(lnic.ZPVAREA)         lnicAreaLengths,
                   length(lnic.ZPVPOSITION)     lnicPositionLengths,
                   length(lnic.ZPVATTRIBUTE)    lnicAttributeLengths,
                   length(lnic.ZDESCRIPTION)    lnicDescriptionLengths,
                   length(lnic.ZLOCATION)       lnicLocationLengths,
                   
                   length(ln.ZHOSTNAME)         lnHostnameLengths,
                   length(ln.ZPVDEVICETYPE)     lnDeviceTypeLengths,
                   length(ln.ZPVAREA)           lnAreaLengths,
                   length(ln.ZPVPOSITION)       lnPositionLengths,
                   
                   length(e.ZNAME)              eNameLengths,
                   length(e.ZSTATE0)            eState0Lengths,
                   length(e.ZSTATE1)            eState1Lengths,
                   length(e.ZPVDEVICETYPE)      eDeviceTypeLengths,
                   length(e.ZPVAREA)            eAreaLengths,
                   length(e.ZPVPOSITION)        ePositionLengths,
                   length(e.ZPVATTRIBUTE)       eAttributeLengths,
                   length(e.ZDESCRIPTION)       eDescriptionLengths,
                   length(e.ZLOCATION)          eLocationLengths

            from ZLINKNODEINPUTCHANNEL lnic left outer join ZLINKNODE ln left outer join ZEPICSFAULT e
        );
    """
            
    try:
        cursor.execute(stringColumnLengthQuery)
        columnLengths = cursor.fetchone()
    except sqlite3.Error, e:
        print "getMaxColumnLengths:", e.args[0]
        sys.exit(1)
    
    # Find max lengths of fields common to both tables
    columnLengths['name']       = max(columnLengths['lnicName'],        columnLengths['lnHostname'],    columnLengths['eName']          );
    columnLengths['deviceType'] = max(columnLengths['lnicDeviceType'],  columnLengths['lnDeviceType'],  columnLengths['eDeviceType']    );
    columnLengths['area']       = max(columnLengths['lnicArea'],        columnLengths['lnArea'],        columnLengths['eArea']          );
    columnLengths['position']   = max(columnLengths['lnicPosition'],    columnLengths['lnPosition'],    columnLengths['ePosition']      );
    
    columnLengths['attribute']      = max(columnLengths['lnicAttribute'],   columnLengths['eAttribute']     );
    columnLengths['state0']         = max(columnLengths['lnicState0'],      columnLengths['eState0']        );
    columnLengths['state1']         = max(columnLengths['lnicState1'],      columnLengths['eState1']        );
    columnLengths['description']    = max(columnLengths['lnicDescription'], columnLengths['eDescription']   );
    columnLengths['location']       = max(columnLengths['lnicLocation'],    columnLengths['eLocation']      );
    
    # Define max lengths of numeric columns
    columnLengths['linkNodeId']         = 3
    columnLengths['card']               = 2
    columnLengths['channel']            = 2
    columnLengths['isAutorecoverable']  = 1
    columnLengths['debounceTimeUs']     = 8
    
    return columnLengths
    


def getIndexForLinkNodeCardAndChannel(linkNodeId, card, channel):
    # To get base index of Link Node section, at booted Link Processor's Cexp prompt,
    # type getIndexForLinkNodeCardAndChannel(0,1,1)

    LN_STATUS_SECTION_BASE      = 3280
    MAX_DATA_SIZE_PER_LINK_NODE = 128
    index = (
                LN_STATUS_SECTION_BASE                     +
                (linkNodeId * MAX_DATA_SIZE_PER_LINK_NODE) +
                ((card   - 1) * 16)                        +
                (channel - 1)
            )
    return index
    

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
                            -c, --csv               CSV files
                            -e, --edm               EDM files
                            -d, --dump              Dump database to text file"""
                
    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("-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)
    
    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("-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")
    parser.add_option_group(group)

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

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

    if (len(args) != 1) or ((options.exportSub          or
                             options.exportMapping      or
                             options.exportCsv          or
                             options.exportEdm          or
                             options.exportDump)        != 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"          )
    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"   )

    
    # 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.exportCsv:       exportCsvFiles          (dbfile, csvPath,           options.verbose)
    if options.exportEdm:       exportEdmFiles          (cursor, edmPath,           options.verbose)
    if options.exportDump:      exportDumpFile          (dbfile, dumpPath,          options.verbose)
    
    connection.close()



if __name__ == "__main__":
    main()

