#!/usr/bin/python
#
#  DumpPLCArchiveChannel.py
#
#  Dumps PLC archive data for particular database, channel and date range
#
#  Uses "reader" account, special read-only account for this and similar purposes
#
#  ejw, mmd, 8-aug-2013
#
#
#  still to do:


from optparse import OptionParser, OptionGroup
import MySQLdb as mdb
import sys
import string


#  database info
Dtag       = 26
Ddatabase  = 'Solenoid_OnChange'
Dhost      = 'gluon01.jlab.org'
Duser      = 'reader'
Dpwd       = 'qcd!confine'
Dstart     = '2013:08:01 00:00'
Dend       = '2013:08:01 00:01'


#  misc
con       = None
databases = ['Solenoid_OnChange', 'Solenoid_100ms', 'Solenoid_1s']


#  create parser, define command line options, defaults and help strings
description = ''
parser = OptionParser(usage="\n  %prog [options]\n\nDumps selected PLC archive data from database into file. Based on database name, tag index and date range.\n\n  Example:  $ DumpPLCArchvieChannel --tag=26 --db=Solenoid_OnChange --start='2013:08:01 00:00' --end='2013:08:01 00:10'\n", description=description)


#  add database options group
dbGroup = OptionGroup(parser, "Database Options","")
dbGroup.add_option("--tag",      dest="tag",            action="store",       default=Dtag,        help='Tag index')
dbGroup.add_option("--db",       dest="database",       action="store",       default=Ddatabase,   help=', '.join(databases) )
dbGroup.add_option("--host",     dest="host",           action="store",       default=Dhost,       help='Database host name')
dbGroup.add_option("--user",     dest="user",           action="store",       default=Duser,       help='User name')
dbGroup.add_option("--pwd",      dest="pwd",            action="store",       default=Dpwd,        help='Password')
dbGroup.add_option("--start",    dest="start",          action="store",       default=Dstart,      help='Start time, "yyyy:mm:dd hh:mm"')
dbGroup.add_option("--end",      dest="end",            action="store",       default=Dend,        help='End time,   "yyyy:mm:dd hh:mm"')
parser.add_option_group(dbGroup)


#  add misc  options group
miscGroup = OptionGroup(parser, "Miscellaneous Options","")
miscGroup.add_option("--index",   dest="index",   action="store_true",   default=False,   help="Just print tag index for all three databases")
miscGroup.add_option("--noTime",  dest="noTime",  action="store_true",   default=False,   help="Suppress ascii times in output file")
miscGroup.add_option("--debug",   dest="debug",   action="store_true",   default=False,   help="Turn on debug options")
parser.add_option_group(miscGroup)


#  parse command line
(options, args) = parser.parse_args()


#  check database name
if(options.database not in databases):
    print ""
    print "  ?unknown database " + options.database
    print ""
    print "Legal databases are:  " + ', '.join(databases)
    print ""
    exit(1)
    


try:

#  connect to mysql and get cursor
    con = mdb.connect(options.host,options.user,options.pwd,options.database)
    cur = con.cursor()


#  check if just want index    
    if(options.index):
        for name in databases:
            print '\n   *** ' + name + ' ***\n'
            cur.execute("use " + name)
            cur.execute("SELECT * from TagTable")
            for row in cur.fetchall() :
                print row[1], "  ", row[0]
            print '\n'
        exit(0)


#  get MySQL version
    cur.execute("SELECT VERSION()")
    version = cur.fetchone()[0]


# get tag name from index, form output filename
    cur.execute("select tagname from TagTable where tagindex=%s" % options.tag)
    tagname=cur.fetchone()
    stripname=str(tagname).translate(None, string.punctuation) + '.txt'


# print transaction info
    print ""
    print "database:           %s"   % options.database
    print "tag index:          %s"   % options.tag
    print "tag name:           %s"   % tagname
    print "start time:         %s"   % options.start
    print "end time:           %s"   % options.end
    print "MySQL version:      %s"   % version
    print "output file:        %s"   % stripname
    print "noTime:             %s"   % options.noTime
    print ""
    


# open file, execute query, dump data to file
    with open(stripname, 'w') as f:
        executestring = ("SELECT unix_timestamp(dateandtime)+(millitm/1000), val, dateandtime, millitm/1000 from FloatTable"
                         " where tagindex=%s and dateandtime>'%s'"    
                         " and dateandtime<'%s'"
                         " order by dateandtime asc") % (options.tag,options.start,options.end)        
        if(options.debug):
            print "Query used:         %s\n" % executestring
        cur.execute(executestring)
        for row in cur.fetchall():
            if(options.noTime):
                f.write('%0.3f   %f\n' % (row[0],row[1]) )
            else:
                f.write('%0.3f   %f          %s  %0.3f\n' % row)

# close file
    f.closed


except mdb.Error, e:
    
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
    
finally:    
    
    if con:    
        con.close()