lumiQueryAPI.py

Go to the documentation of this file.

from __future__ import print_function
import os
import coral,datetime
from RecoLuminosity.LumiDB import nameDealer,lumiTime,CommonUtil,lumiCorrections
import array
import argparse

import six
from RecoLuminosity.LumiDB import  nameDealer, selectionParser, hltTrgSeedMapper, \
     connectstrParser, cacheconfigParser, tablePrinter, csvReporter, csvSelectionParser
from RecoLuminosity.LumiDB.wordWrappers import wrap_always, wrap_onspace, wrap_onspace_strict
from pprint import pprint, pformat

'''
This module defines lowlevel SQL query API for lumiDB 
We do not like range queries so far because of performance of range scan.Use only necessary.
The principle is to query by runnumber and per each coral queryhandle
Try reuse db session/transaction and just renew query handle each time to reduce metadata queries.
Avoid unnecessary explicit order by
Do not handle transaction in here.
Do not do explicit del queryhandle in here.
Note: all the returned dict format are not sorted by itself.Sort it outside if needed.
'''
###==============temporarilly here======###

class ParametersObject (object):

    def __init__ (self):
        self.norm            = 1.0
        self.lumiversion     = '0001'
        self.NBX             = 3564  # number beam crossings
        self.rotationRate    = 11245.613 # for 3.5 TeV Beam energy
        self.normFactor      = 6.37
        self.beammode        = '' #possible choices stable, quiet, either
        self.verbose         = False
        self.noWarnings      = False
        self.lumischema      = 'CMS_LUMI_PROD'
        #self.lumidb          = 'oracle://cms_orcoff_prod/cms_lumi_prod'
        self.lumisummaryname = 'LUMISUMMARY'
        self.lumidetailname  = 'LUMIDETAIL'
        self.lumiXing        = False
        self.xingMinLum      = 1.0e-4
        self.xingIndex       = 5
        self.minBiasXsec     = 71300 # unit: microbarn
        self.pileupHistName  = 'pileup'
        self.maxPileupBin    = 10
        self.calculateTimeParameters()

    def calculateTimeParameters (self):
        '''Given the rotation rate, calculate lumi section length and
        rotation time.  This should be called if rotationRate is
        updated.'''
        self.rotationTime    = 1 / self.rotationRate
        self.lumiSectionLen  = 2**18 * self.rotationTime
        
    def defaultfrontierConfigString (self):
        return '''<frontier-connect><proxy url = "http://cmst0frontier.cern.ch:3128"/><proxy url = "http://cmst0frontier.cern.ch:3128"/><proxy url = "http://cmst0frontier1.cern.ch:3128"/><proxy url = "http://cmst0frontier2.cern.ch:3128"/><server url = "http://cmsfrontier.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier1.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier2.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier3.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier4.cern.ch:8000/FrontierInt"/></frontier-connect>'''

def lslengthsec (numorbit, numbx):
    #print numorbit, numbx
    l = numorbit * numbx * 25.0e-09
    return l

def lsBylsLumi (deadtable):
    """
    input: {lsnum:[deadtime, instlumi, bit_0, norbits,prescale...]}
    output: {lsnum:[instlumi, recordedlumi...]}
    """
    result = {}
    for myls, deadArray in deadtable.items():
        lstime = lslengthsec (deadArray[3], 3564)
        instlumi = deadArray[1] * lstime
        if float( deadArray[2] ) ==  0.0:
            deadfrac = 1.0
        else:
            deadfrac = float (deadArray[0]) / (float (deadArray[2])*float(deadArray[4]))
        recordedLumi = instlumi * (1.0 - deadfrac)
        myLsList = [instlumi, recordedLumi]
        #print myls,instlumi,recordedLumi,lstime,deadfrac
        if len (deadArray) > 5:
            myLsList.extend (deadArray[5:])
        result[myls] = myLsList
    return result


def deliveredLumiForRange (dbsession, parameters,inputRange,finecorrections=None):
    '''Takes either single run as a string or dictionary of run ranges'''
    lumidata = []
    runs=[]
    if isinstance(inputRange, str):
        runs.append(int(inputRange))
    else:
        runs=inputRange.runs()
    for r in sorted(runs):
        if parameters.verbose:
            print("run", run)
        c=None
        if finecorrections:
            c=finecorrections[r]
        lumidata.append( deliveredLumiForRun (dbsession, parameters,r,finecorrections=c) )       
    return lumidata

def recordedLumiForRange (dbsession, parameters, inputRange,finecorrections=None):
    '''Takes either single run as a string or dictionary of run ranges'''
    lumidata = []
    if isinstance(inputRange,str):
        run=int(inputRange)
        if finecorrections and finecorrections[run]:
            lumiDataPiece = recordedLumiForRun (dbsession, parameters,inputRange,lslist=None,finecorrections=finecorrections[run])
        else:
            lumiDataPiece = recordedLumiForRun(dbsession, parameters,run)
        if parameters.lumiXing:            
            # get the xing information for the run
            if  finecorrections and finecorrections[run]:
                xingLumiDict = xingLuminosityForRun (dbsession, inputRange,
                                                     parameters,finecorrections=finecorrections[run])
            else:
                xingLumiDict = xingLuminosityForRun (dbsession, inputRange,
                                                     parameters)
            mergeXingLumi (lumiDataPiece, xingLumiDict)
        lumidata.append (lumiDataPiece)
    else:
        # we want to collapse the lists so that every run is considered once.
        runLsDict = {}
        maxLumiSectionDict = {}
        for (run, lslist) in sorted (inputRange.runsandls().items() ):
            if(len(lslist)!=0):
                maxLumiSectionDict[run] = max ( max (lslist),
                                           maxLumiSectionDict.get(run,0) )
            runLsDict.setdefault (run, []).append (lslist)
        for run, metaLsList in sorted (six.iteritems(runLsDict)):
            if parameters.verbose:
                print("run", run)
            runLumiData = []
            for lslist in metaLsList:
                if finecorrections and finecorrections[run]:
                    runLumiData.append( recordedLumiForRun (dbsession, parameters,run,lslist=lslist,finecorrections=finecorrections[run]) )
                    
                else:
                    runLumiData.append( recordedLumiForRun (dbsession, parameters,run,lslist=lslist) )
            if parameters.lumiXing:
                # get the xing information once for the whole run
                if finecorrections and finecorrections[run]:
                    xingLumiDict = xingLuminosityForRun (dbsession, run,
                                                         parameters,
                                                         maxLumiSection = \
                                                         maxLumiSectionDict[run],
                                                         finecorrections=finecorrections[run])
                else:
                    xingLumiDict = xingLuminosityForRun (dbsession, run,
                                                         parameters,
                                                         maxLumiSection = \
                                                         maxLumiSectionDict[run])
                # merge it with every piece of lumi data for this run
                for lumiDataPiece in runLumiData:
                    mergeXingLumi (lumiDataPiece, xingLumiDict)
                    lumidata.append (lumiDataPiece)
            else:
                lumidata.extend( runLumiData )
    return lumidata

def deliveredLumiForRun (dbsession, parameters, runnum, finecorrections=None ):    
    """
    select sum (INSTLUMI), count (INSTLUMI) from lumisummary where runnum = 124025 and lumiversion = '0001';
    select INSTLUMI,NUMORBIT  from lumisummary where runnum = 124025 and lumiversion = '0001'
    query result unit E27cm^-2 (= 1 / mb)

    optional corrections=None/(constfactor,afterglowfactor,nonlinearfactor)
    """    
    #if parameters.verbose:
    #    print 'deliveredLumiForRun : norm : ', parameters.norm, ' : run : ', runnum
    #output ['run', 'totalls', 'delivered', 'beammode']
    delivered = 0.0
    totalls = 0
    try:
        conditionstring="RUNNUM = :runnum AND LUMIVERSION = :lumiversion"
        dbsession.transaction().start (True)
        schema = dbsession.nominalSchema()
        query = schema.tableHandle (nameDealer.lumisummaryTableName()).newQuery()
        query.addToOutputList("INSTLUMI",'instlumi')
        query.addToOutputList ("NUMORBIT", "norbits")
        queryBind = coral.AttributeList()
        queryBind.extend ("runnum", "unsigned int")
        queryBind.extend ("lumiversion", "string")
        queryBind["runnum"].setData (int (runnum))
        queryBind["lumiversion"].setData (parameters.lumiversion)
        #print parameters.beammode
        if len(parameters.beammode)!=0:
            conditionstring=conditionstring+' and BEAMSTATUS=:beamstatus'
            queryBind.extend('beamstatus','string')
            queryBind['beamstatus'].setData(parameters.beammode)
        result = coral.AttributeList()
        result.extend ("instlumi", "float")
        result.extend ("norbits", "unsigned int")
        query.defineOutput (result)
        query.setCondition (conditionstring,queryBind)
        cursor = query.execute()
        while next(cursor):
            instlumi = cursor.currentRow()['instlumi'].data()
            norbits = cursor.currentRow()['norbits'].data()
            if finecorrections is not None:
                instlumi=lumiCorrections.applyfinecorrection(instlumi,finecorrections[0],finecorrections[1],finecorrections[2])
            if instlumi is not None and norbits is not None:
                lstime = lslengthsec(norbits, parameters.NBX)
                delivered=delivered+instlumi*parameters.norm*lstime
                totalls+=1
        del query
        dbsession.transaction().commit()
        lumidata = []
        if delivered == 0.0:
            lumidata = [str (runnum), 'N/A', 'N/A', 'N/A']
        else:
            lumidata = [str (runnum), str (totalls), '%.3f'%delivered, parameters.beammode]
        return lumidata
    except Exception as e:
        print(str (e))
        dbsession.transaction().rollback()
        del dbsession

def recordedLumiForRun (dbsession, parameters, runnum, lslist=None,finecorrections=None):
    """
    lslist = [] means take none in the db
    lslist = None means to take all in the db
    output: ['runnumber', 'trgtable{}', 'deadtable{}']
    """
    recorded = 0.0
    lumidata = [] #[runnumber, trgtable, deadtable]
    trgtable = {} #{hltpath:[l1seed, hltprescale, l1prescale]}
    deadtable = {} #{lsnum:[deadtime, instlumi, bit_0, norbits,bitzero_prescale]}
    lumidata.append (runnum)
    lumidata.append (trgtable)
    lumidata.append (deadtable)
    collectedseeds = [] #[ (hltpath, l1seed)]
    conditionstring='trghltmap.HLTKEY = cmsrunsummary.HLTKEY AND cmsrunsummary.RUNNUM = :runnumber'
    try:
        dbsession.transaction().start (True)
        schema = dbsession.nominalSchema()
        query = schema.newQuery()
        query.addToTableList (nameDealer.cmsrunsummaryTableName(), 'cmsrunsummary')
        query.addToTableList (nameDealer.trghltMapTableName(), 'trghltmap')#small table first
        queryCondition = coral.AttributeList()
        queryCondition.extend ("runnumber", "unsigned int")
        queryCondition["runnumber"].setData (int (runnum))
        query.setCondition (conditionstring,queryCondition)
        query.addToOutputList ("trghltmap.HLTPATHNAME", "hltpathname")
        query.addToOutputList ("trghltmap.L1SEED", "l1seed")
        result = coral.AttributeList()
        result.extend ("hltpathname", "string")
        result.extend ("l1seed", "string")
        query.defineOutput (result)
        cursor = query.execute()
        while next(cursor):
            hltpathname = cursor.currentRow()["hltpathname"].data()
            l1seed = cursor.currentRow()["l1seed"].data()
            collectedseeds.append ( (hltpathname, l1seed))
        #print 'collectedseeds ', collectedseeds
        del query
        dbsession.transaction().commit()
        #loop over hltpath
        for (hname, sname) in collectedseeds:
            l1bitname = hltTrgSeedMapper.findUniqueSeed (hname, sname)
            #print 'found unque seed ', hname, l1bitname
            if l1bitname:
                lumidata[1][hname] = []
                lumidata[1][hname].append (l1bitname.replace ('\"', ''))
        dbsession.transaction().start (True)
        schema = dbsession.nominalSchema()
        hltprescQuery = schema.tableHandle (nameDealer.hltTableName()).newQuery()
        hltprescQuery.addToOutputList ("PATHNAME", "hltpath")
        hltprescQuery.addToOutputList ("PRESCALE", "hltprescale")
        hltprescCondition = coral.AttributeList()
        hltprescCondition.extend ('runnumber', 'unsigned int')
        hltprescCondition.extend ('cmslsnum', 'unsigned int')
        hltprescCondition.extend ('inf', 'unsigned int')
        hltprescResult = coral.AttributeList()
        hltprescResult.extend ('hltpath', 'string')
        hltprescResult.extend ('hltprescale', 'unsigned int')
        hltprescQuery.defineOutput (hltprescResult)
        hltprescCondition['runnumber'].setData (int (runnum))
        hltprescCondition['cmslsnum'].setData (1)
        hltprescCondition['inf'].setData (0)
        hltprescQuery.setCondition ("RUNNUM = :runnumber and CMSLSNUM = :cmslsnum and PRESCALE != :inf",hltprescCondition)
        cursor = hltprescQuery.execute()
        while next(cursor):
            hltpath = cursor.currentRow()['hltpath'].data()
            hltprescale = cursor.currentRow()['hltprescale'].data()
            if hltpath in lumidata[1]:
                lumidata[1][hltpath].append (hltprescale)
                
        cursor.close()
        del hltprescQuery
        dbsession.transaction().commit()      
        dbsession.transaction().start (True)
        schema = dbsession.nominalSchema()
        query = schema.newQuery()
        query.addToTableList (nameDealer.trgTableName(), 'trg')
        query.addToTableList (nameDealer.lumisummaryTableName(), 'lumisummary')#small table first--right-most
        queryCondition = coral.AttributeList()
        queryCondition.extend ("runnumber", "unsigned int")
        queryCondition.extend ("lumiversion", "string")
        queryCondition["runnumber"].setData (int (runnum))
        queryCondition["lumiversion"].setData (parameters.lumiversion)
        conditionstring='lumisummary.RUNNUM =:runnumber and lumisummary.LUMIVERSION =:lumiversion AND lumisummary.CMSLSNUM=trg.CMSLSNUM and lumisummary.RUNNUM=trg.RUNNUM'
        if len(parameters.beammode)!=0:
            conditionstring=conditionstring+' and lumisummary.BEAMSTATUS=:beamstatus'
            queryCondition.extend('beamstatus','string')
            queryCondition['beamstatus'].setData(parameters.beammode)
        query.setCondition(conditionstring,queryCondition)
        query.addToOutputList ("lumisummary.CMSLSNUM", "cmsls")
        query.addToOutputList ("lumisummary.INSTLUMI", "instlumi")
        query.addToOutputList ("lumisummary.NUMORBIT", "norbits")
        query.addToOutputList ("trg.TRGCOUNT",         "trgcount")
        query.addToOutputList ("trg.BITNAME",          "bitname")
        query.addToOutputList ("trg.DEADTIME",         "trgdeadtime")
        query.addToOutputList ("trg.PRESCALE",         "trgprescale")
        query.addToOutputList ("trg.BITNUM",           "trgbitnum")
        
        result = coral.AttributeList()
        result.extend ("cmsls",       "unsigned int")
        result.extend ("instlumi",    "float")
        result.extend ("norbits",     "unsigned int")
        result.extend ("trgcount",    "unsigned int")
        result.extend ("bitname",     "string")
        result.extend ("trgdeadtime", "unsigned long long")
        result.extend ("trgprescale", "unsigned int")
        result.extend ("trgbitnum",   "unsigned int")
        trgprescalemap = {}
        query.defineOutput (result)
        cursor = query.execute()
        while next(cursor):
            cmsls       = cursor.currentRow()["cmsls"].data()
            instlumi    = cursor.currentRow()["instlumi"].data()*parameters.norm
            if finecorrections:
                instlumi=lumiCorrections.applyfinecorrection(instlumi,finecorrections[0],finecorrections[1],finecorrections[2])
            norbits     = cursor.currentRow()["norbits"].data()
            trgcount    = cursor.currentRow()["trgcount"].data()
            trgbitname  = cursor.currentRow()["bitname"].data()
            trgdeadtime = cursor.currentRow()["trgdeadtime"].data()
            trgprescale = cursor.currentRow()["trgprescale"].data()
            trgbitnum   = cursor.currentRow()["trgbitnum"].data()
            if cmsls == 1:
                if trgbitname not in trgprescalemap:
                    trgprescalemap[trgbitname] = trgprescale
            if trgbitnum == 0:
                if cmsls not in deadtable:
                    deadtable[cmsls] = []
                    deadtable[cmsls].append (trgdeadtime)
                    deadtable[cmsls].append (instlumi)
                    deadtable[cmsls].append (trgcount)
                    deadtable[cmsls].append (norbits)
                    deadtable[cmsls].append (trgprescale)
        cursor.close()
        del query
        dbsession.transaction().commit()
        
        #
        #consolidate results
        #
        #trgtable
        #print 'trgprescalemap', trgprescalemap
        #print lumidata[1]
        for hpath, trgdataseq in lumidata[1].items():   
            bitn = trgdataseq[0]
            if bitn in trgprescalemap and len (trgdataseq) == 2:
                lumidata[1][hpath].append (trgprescalemap[bitn])                
        #filter selected cmsls
        lumidata[2] = filterDeadtable (deadtable, lslist)
        if not parameters.noWarnings:
            if len(lumidata[2])!=0:
                for lumi, deaddata in lumidata[2].items():
                    if deaddata[1] == 0.0 and deaddata[2]!=0 and deaddata[0]!=0:
                        print('[Warning] : run %s :ls %d has 0 instlumi but trigger has data' % (runnum, lumi))
                    if (deaddata[2] == 0 or deaddata[0] == 0) and deaddata[1]!=0.0:
                        print('[Warning] : run %s :ls %d has 0 dead counts or 0 zerobias bit counts, but inst!=0' % (runnum, lumi))
        #print 'lumidata[2] ', lumidata[2]
    except Exception as e:
        print(str (e))
        dbsession.transaction().rollback()
        del dbsession
    #print 'before return lumidata ', lumidata
    ## if parameters.lumiXing:
    ##     xingLumiDict =  xingLuminosityForRun (dbsession, runnum, parameters)
    ##     mergeXingLumi (lumidata, xingLumiDict)
    return lumidata


def filterDeadtable (inTable, lslist):
    result = {}
    if lslist is None:
        return inTable
    if len (lslist) == 0: #if request no ls, then return nothing
        return result
    for existingLS in inTable.keys():
        if existingLS in lslist:
            result[existingLS] = inTable[existingLS]
    return result


def printDeliveredLumi (lumidata, mode):
    labels = [ ('Run', 'Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Beam Mode')]
    print(tablePrinter.indent (labels+lumidata, hasHeader = True, separateRows = False,
                               prefix = '| ', postfix = ' |', justify = 'right',
                               delim = ' | ', wrapfunc = lambda x: wrap_onspace (x, 20) ))

def dumpData (lumidata, filename):
    """
    input params: lumidata [{'fieldname':value}]
                  filename csvname
    """
    
    r = csvReporter.csvReporter(filename)
    r.writeRows(lumidata)

def calculateTotalRecorded (deadtable):
    """
    input: {lsnum:[deadtime, instlumi, bit_0, norbits,prescale]}
    output: recordedLumi
    """
    recordedLumi = 0.0
    for myls, d in deadtable.items():
        instLumi = d[1]
        #deadfrac = float (d[0])/float (d[2]*3564)
        #print myls, float (d[2])
        if float (d[2]) == 0.0:
            deadfrac = 1.0
        else:
            deadfrac = float (d[0])/(float (d[2])*float (d[-1]))
        lstime = lslengthsec (d[3], 3564)
        recordedLumi += instLumi* (1.0-deadfrac)*lstime
    return recordedLumi


def splitlistToRangeString (inPut):
    result = []
    first = inPut[0]
    last = inPut[0]
    result.append ([inPut[0]])
    counter = 0
    for i in inPut[1:]:
        if i == last+1:
            result[counter].append (i)
        else:
            counter += 1
            result.append ([i])
        last = i
    return ', '.join (['['+str (min (x))+'-'+str (max (x))+']' for x in result])


def calculateEffective (trgtable, totalrecorded):
    """
    input: trgtable{hltpath:[l1seed, hltprescale, l1prescale]}, totalrecorded (float)
    output:{hltpath, recorded}
    """
    #print 'inputtrgtable', trgtable
    result = {}
    for hltpath, data in trgtable.items():
        if len (data) ==  3:
            result[hltpath] = totalrecorded/ (data[1]*data[2])
        else:
            result[hltpath] = 0.0
    return result


def getDeadfractions (deadtable):
    """
    inputtable: {lsnum:[deadtime, instlumi, bit_0, norbits,bit_0_prescale]}
    output: {lsnum:deadfraction}
    """
    result = {}
    for myls, d in deadtable.items():
        #deadfrac = float (d[0])/ (float (d[2])*float (3564))
        if float (d[2]) == 0.0: ##no beam
            deadfrac = -1.0
        else:
            deadfrac = float (d[0])/ (float (d[2])*float(d[-1]))
        result[myls] = deadfrac
    return result

def printPerLSLumi (lumidata, isVerbose = False):
    '''
    input lumidata  [['runnumber', 'trgtable{}', 'deadtable{}']]
    deadtable {lsnum:[deadtime, instlumi, bit_0, norbits,prescale]}
    '''
    datatoprint = []
    totalrow = []
    labels = [ ('Run', 'LS', 'Delivered', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
    lastrowlabels = [ ('Selected LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
    totalDeliveredLS = 0
    totalSelectedLS = 0
    totalDelivered = 0.0
    totalRecorded = 0.0
    
    for perrundata in lumidata:
        runnumber = perrundata[0]
        deadtable = perrundata[2]
        lumiresult = lsBylsLumi (deadtable)
        totalSelectedLS = totalSelectedLS+len (deadtable)
        for lsnum, dataperls in lumiresult.items():
            rowdata = []
            if len (dataperls) == 0:
                rowdata  +=  [str (runnumber), str (lsnum), 'N/A', 'N/A']
            else:
                rowdata  +=  [str (runnumber), str (lsnum), '%.3f' % (dataperls[0]), '%.3f' % (dataperls[1])]
                totalDelivered = totalDelivered+dataperls[0]
                totalRecorded = totalRecorded+dataperls[1]
            datatoprint.append (rowdata)
    totalrow.append ([str (totalSelectedLS), '%.3f'% (totalDelivered), '%.3f'% (totalRecorded)])
    print(' ==  = ')
    print(tablePrinter.indent (labels+datatoprint, hasHeader = True, separateRows = False, prefix = '| ',
                               postfix = ' |', justify = 'right', delim = ' | ',
                               wrapfunc = lambda x: wrap_onspace_strict (x, 22)))
    print(' ==  =  Total : ')
    print(tablePrinter.indent (lastrowlabels+totalrow, hasHeader = True, separateRows = False, prefix = '| ',
                               postfix = ' |', justify = 'right', delim = ' | ',
                               wrapfunc = lambda x: wrap_onspace (x, 20)))    

    
def dumpPerLSLumi (lumidata):
    datatodump = []
    for perrundata in lumidata:
        runnumber = perrundata[0]
        deadtable = perrundata[2]
        lumiresult = lsBylsLumi (deadtable)
        for lsnum, dataperls in lumiresult.items():
            rowdata = []
            if len (dataperls) == 0:
                rowdata += [str (runnumber), str (lsnum), 'N/A', 'N/A']
            else:
                rowdata += [str (runnumber), str (lsnum), dataperls[0], dataperls[1]]
            if len (dataperls) > 2:
                rowdata.extend ( flatten (dataperls[2:]) )
            datatodump.append (rowdata)
    return datatodump


def printRecordedLumi (lumidata, isVerbose = False, hltpath = ''):
    datatoprint = []
    totalrow = []
    labels = [ ('Run', 'HLT path', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
    lastrowlabels = [ ('Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
    if len (hltpath) != 0 and hltpath != 'all':
        lastrowlabels = [ ('Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'),
                           'Effective '+u' (/\u03bcb) '.encode ('utf-8')+hltpath)]
    if isVerbose:
        labels = [ ('Run', 'HLT-path', 'L1-bit', 'L1-presc', 'HLT-presc', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
    totalSelectedLS = 0
    totalRecorded = 0.0
    totalRecordedInPath = 0.0
    
    for dataperRun in lumidata:
        runnum = dataperRun[0]
        if len (dataperRun[1]) == 0:
            rowdata = []
            rowdata += [str (runnum)]+2*['N/A']
            datatoprint.append (rowdata)
            continue
        perlsdata = dataperRun[2]
        totalSelectedLS = totalSelectedLS+len (perlsdata)
        recordedLumi = 0.0
        #norbits = perlsdata.values()[0][3]
        recordedLumi = calculateTotalRecorded (perlsdata)
        totalRecorded = totalRecorded+recordedLumi
        trgdict = dataperRun[1]
        effective = calculateEffective (trgdict, recordedLumi)
        if hltpath in trgdict and hltpath in effective:
            rowdata = []
            l1bit = trgdict[hltpath][0]
            if len (trgdict[hltpath]) !=  3:
                if not isVerbose:
                    rowdata += [str (runnum), hltpath, 'N/A']
                else:
                    rowdata += [str (runnum), hltpath, l1bit, 'N/A', 'N/A', 'N/A']
            else:
                if not isVerbose:
                    rowdata += [str (runnum), hltpath, '%.3f'% (effective[hltpath])]
                else:
                    hltprescale = trgdict[hltpath][1]
                    l1prescale = trgdict[hltpath][2]
                    rowdata += [str (runnum), hltpath, l1bit, str (l1prescale), str (hltprescale),
                                '%.3f'% (effective[hltpath])]
                totalRecordedInPath = totalRecordedInPath+effective[hltpath]
            datatoprint.append (rowdata)
            continue
        
        for trg, trgdata in trgdict.items():
            #print trg, trgdata
            rowdata = []                    
            if trg == trgdict.keys()[0]:
                rowdata += [str (runnum)]
            else:
                rowdata += ['']
            l1bit = trgdata[0]
            if len (trgdata) == 3:
                if not isVerbose:
                    rowdata += [trg, '%.3f'% (effective[trg])]
                else:
                    hltprescale = trgdata[1]
                    l1prescale = trgdata[2]
                    rowdata += [trg, l1bit, str (l1prescale), str (hltprescale), '%.3f'% (effective[trg])]
            else:
                if not isVerbose:
                    rowdata += [trg, 'N/A']
                else:
                    rowdata += [trg, l1bit, 'N/A', 'N/A', '%.3f'% (effective[trg])]
            datatoprint.append (rowdata)
    #print datatoprint
    print(' ==  = ')
    print(tablePrinter.indent (labels+datatoprint, hasHeader = True, separateRows = False, prefix = '| ',
                               postfix = ' |', justify = 'right', delim = ' | ',
                               wrapfunc = lambda x: wrap_onspace_strict (x, 22)))

    if len (hltpath) != 0 and hltpath != 'all':
        totalrow.append ([str (totalSelectedLS), '%.3f'% (totalRecorded), '%.3f'% (totalRecordedInPath)])
    else:
        totalrow.append ([str (totalSelectedLS), '%.3f'% (totalRecorded)])
    print(' ==  =  Total : ')
    print(tablePrinter.indent (lastrowlabels+totalrow, hasHeader = True, separateRows = False, prefix = '| ',
                               postfix = ' |', justify = 'right', delim = ' | ',
                               wrapfunc = lambda x: wrap_onspace (x, 20)))    
    if isVerbose:
        deadtoprint = []
        deadtimelabels = [ ('Run', 'Lumi section : Dead fraction')]

        for dataperRun in lumidata:
            runnum = dataperRun[0]
            if len (dataperRun[1]) == 0:
                deadtoprint.append ([str (runnum), 'N/A'])
                continue
            perlsdata = dataperRun[2]
            #print 'perlsdata 2 : ', perlsdata
            deadT = getDeadfractions (perlsdata)
            t = ''
            for myls, de in deadT.items():
                if de<0:
                    t += str (myls)+':nobeam '
                else:
                    t += str (myls)+':'+'%.5f'% (de)+' '
            deadtoprint.append ([str (runnum), t])
        print(' ==  = ')
        print(tablePrinter.indent (deadtimelabels+deadtoprint, hasHeader = True, separateRows = True, prefix = '| ',
                                   postfix = ' |', justify = 'right', delim = ' | ',
                                   wrapfunc = lambda x: wrap_onspace (x, 80)))


def dumpRecordedLumi (lumidata, hltpath = ''):
    #labels = ['Run', 'HLT path', 'Recorded']
    datatodump = []
    for dataperRun in lumidata:
        runnum = dataperRun[0]
        if len (dataperRun[1]) == 0:
            rowdata = []
            rowdata += [str (runnum)]+2*['N/A']
            datatodump.append (rowdata)
            continue
        perlsdata = dataperRun[2]
        recordedLumi = 0.0
        #norbits = perlsdata.values()[0][3]
        recordedLumi = calculateTotalRecorded (perlsdata)
        trgdict = dataperRun[1]
        effective = calculateEffective (trgdict, recordedLumi)
        if hltpath in trgdict and hltpath in effective:
            rowdata = []
            l1bit = trgdict[hltpath][0]
            if len (trgdict[hltpath]) !=  3:
                rowdata += [str (runnum), hltpath, 'N/A']
            else:
                hltprescale = trgdict[hltpath][1]
                l1prescale = trgdict[hltpath][2]
                rowdata += [str (runnum), hltpath, effective[hltpath]]
            datatodump.append (rowdata)
            continue
        
        for trg, trgdata in trgdict.items():
            #print trg, trgdata
            rowdata = []                    
            rowdata += [str (runnum)]
            l1bit = trgdata[0]
            if len (trgdata) == 3:
                rowdata += [trg, effective[trg]]
            else:
                rowdata += [trg, 'N/A']
            datatodump.append (rowdata)
    return datatodump


def printOverviewData (delivered, recorded, hltpath = ''):
    if len (hltpath) == 0 or hltpath == 'all':
        toprowlabels = [ ('Run', 'Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8') )]
        lastrowlabels = [ ('Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8') ) ]
    else:
        toprowlabels = [ ('Run', 'Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'), 'Effective'+u' (/\u03bcb) '.encode ('utf-8')+hltpath )]
        lastrowlabels = [ ('Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'), 'Effective '+u' (/\u03bcb) '.encode ('utf-8')+hltpath)]
    datatable = []
    totaldata = []
    totalDeliveredLS = 0
    totalSelectedLS = 0
    totalDelivered = 0.0
    totalRecorded = 0.0
    totalRecordedInPath = 0.0
    totaltable = []
    for runidx, deliveredrowdata in enumerate (delivered):
        rowdata = []
        rowdata += [deliveredrowdata[0], deliveredrowdata[1], deliveredrowdata[2]]
        if deliveredrowdata[1] == 'N/A': #run does not exist
            if  hltpath != '' and hltpath != 'all':
                rowdata += ['N/A', 'N/A', 'N/A']
            else:
                rowdata += ['N/A', 'N/A']
            datatable.append (rowdata)
            continue
        totalDeliveredLS += int (deliveredrowdata[1])
        totalDelivered += float (deliveredrowdata[2])
        selectedls = recorded[runidx][2].keys()
        #print 'runidx ', runidx, deliveredrowdata
        #print 'selectedls ', selectedls
        if len (selectedls) == 0:
            selectedlsStr = '[]'
            recordedLumi = 0
            if  hltpath != '' and hltpath != 'all':
                rowdata += [selectedlsStr, 'N/A', 'N/A']
            else:
                rowdata += [selectedlsStr, 'N/A']
        else:
            selectedlsStr = splitlistToRangeString (selectedls)
            recordedLumi = calculateTotalRecorded (recorded[runidx][2])
            lumiinPaths = calculateEffective (recorded[runidx][1], recordedLumi)
            if hltpath != '' and hltpath != 'all':
                if hltpath in lumiinPaths:
                    rowdata += [selectedlsStr, '%.3f'% (recordedLumi), '%.3f'% (lumiinPaths[hltpath])]
                    totalRecordedInPath += lumiinPaths[hltpath]
                else:
                    rowdata += [selectedlsStr, '%.3f'% (recordedLumi), 'N/A']
            else:
                #rowdata += [selectedlsStr, '%.3f'% (recordedLumi), '%.3f'% (recordedLumi)]
                rowdata += [selectedlsStr, '%.3f'% (recordedLumi)]
        totalSelectedLS += len (selectedls)
        totalRecorded += recordedLumi
        datatable.append (rowdata)

    if hltpath != '' and hltpath != 'all':
        totaltable = [[str (totalDeliveredLS), '%.3f'% (totalDelivered), str (totalSelectedLS),
                       '%.3f'% (totalRecorded), '%.3f'% (totalRecordedInPath)]]
    else:
        totaltable = [[str (totalDeliveredLS), '%.3f'% (totalDelivered), str (totalSelectedLS),
                       '%.3f'% (totalRecorded)]]
    print(tablePrinter.indent (toprowlabels+datatable, hasHeader = True, separateRows = False, prefix = '| ',
                               postfix = ' |', justify = 'right', delim = ' | ',
                               wrapfunc = lambda x: wrap_onspace (x, 20)))
    print(' ==  =  Total : ')
    print(tablePrinter.indent (lastrowlabels+totaltable, hasHeader = True, separateRows = False, prefix = '| ',
                               postfix = ' |', justify = 'right', delim = ' | ',
                               wrapfunc = lambda x: wrap_onspace (x, 20)))


def dumpOverview (delivered, recorded, hltpath = ''):
    #toprowlabels = ['run', 'delivered', 'recorded', 'hltpath']
    datatable = []
    for runidx, deliveredrowdata in enumerate (delivered):
        rowdata = []
        rowdata += [deliveredrowdata[0], deliveredrowdata[2]]
        if deliveredrowdata[1] == 'N/A': #run does not exist
            rowdata += ['N/A', 'N/A']
            datatable.append (rowdata)
            continue
        recordedLumi = calculateTotalRecorded (recorded[runidx][2])
        lumiinPaths = calculateEffective (recorded[runidx][1], recordedLumi)
        if hltpath != '' and hltpath != 'all':
            if hltpath in lumiinPaths:
                rowdata += [recordedLumi, lumiinPaths[hltpath]]
            else:
                rowdata += [recordedLumi, 'N/A']
        else:
            rowdata += [recordedLumi, recordedLumi]
        datatable.append (rowdata)
    return datatable


def xingLuminosityForRun (dbsession, runnum, parameters, lumiXingDict = {},
                          maxLumiSection = None, finecorrections=None):
    '''Given a run number and a minimum xing luminosity value,
    returns a dictionary (keyed by (run, lumi section)) where the
    value is a list of tuples of (xingID, xingLum).

    - For all xing luminosities, simply set minLumValue to 0.

    - If you want one dictionary for several runs, pass it in to
      "lumiXingDict"

    select 
    s.cmslsnum, d.bxlumivalue, d.bxlumierror, d.bxlumiquality, d.algoname from LUMIDETAIL d, LUMISUMMARY s where s.runnum = 133885 and d.algoname = 'OCC1' and s.lumisummary_id = d.lumisummary_id order by s.startorbit, s.cmslsnum
    '''
    try:
        runnum = int (runnum)
        dbsession.transaction().start (True)
        schema = dbsession.schema (parameters.lumischema)
        if not schema:
            raise RuntimeError('cannot connect to schema '+ parameters.lumischema)
        detailOutput = coral.AttributeList()
        detailOutput.extend ('startorbit',    'unsigned int')
        detailOutput.extend ('cmslsnum',      'unsigned int')
        detailOutput.extend ('bxlumivalue',   'blob')
        detailOutput.extend ('bxlumierror',   'blob')
        detailOutput.extend ('bxlumiquality', 'blob')
        detailOutput.extend ('algoname',      'string')
        detailCondition = coral.AttributeList()
        detailCondition.extend ('runnum',   'unsigned int')
        detailCondition.extend ('algoname', 'string')
        detailCondition['runnum'].setData (runnum)
        detailCondition['algoname'].setData ('OCC1')
        query = schema.newQuery()
        query.addToTableList(nameDealer.lumisummaryTableName(), 's')
        query.addToTableList(nameDealer.lumidetailTableName(),  'd')
        query.addToOutputList ('s.STARTORBIT',    'startorbit')
        query.addToOutputList ('s.CMSLSNUM',      'cmslsnum')
        query.addToOutputList ('d.BXLUMIVALUE',   'bxlumivalue')
        query.addToOutputList ('d.BXLUMIERROR',   'bxlumierror')
        query.addToOutputList ('d.BXLUMIQUALITY', 'bxlumiquality')
        query.addToOutputList ('d.ALGONAME',      'algoname')
        query.setCondition ('s.RUNNUM =:runnum and d.ALGONAME =:algoname and s.LUMISUMMARY_ID=d.LUMISUMMARY_ID',detailCondition)
        query.addToOrderList ('s.CMSLSNUM')
        query.defineOutput (detailOutput)
        cursor = query.execute()
        count = 0
        while next(cursor):
            ## ## Note: If you are going to break out of this loop early,
            ## ## make sure you call cursor.close():
            ## 
            ## if count > 20 :
            ##     cursor.close()
            ##     break
            ## count  +=  1
            cmslsnum    = cursor.currentRow()['cmslsnum'].data()
            algoname    = cursor.currentRow()['algoname'].data()
            bxlumivalue = cursor.currentRow()['bxlumivalue'].data()
            startorbit  = cursor.currentRow()['startorbit'].data()
            
            if maxLumiSection and maxLumiSection < cmslsnum:
                cursor.close()
                break
            
            xingArray = array.array ('f')
            xingArray.fromstring( bxlumivalue.readline() )
            numPrinted = 0
            xingLum = []
            avginstlumi=0.0
            if len(xingArray)!=0:
                avginstlumi=sum(xingArray)
            for index, lum in enumerate (xingArray):
                mynorm=parameters.normFactor
                if finecorrections:
                    lum=lumiCorrections.applyfinecorrectionBX(lum,avginstlumi*mynorm,mynorm,finecorrections[0],finecorrections[1],finecorrections[2])
                else:
                    lum*=mynorm
                if lum < parameters.xingMinLum:
                    continue
                xingLum.append( (index, lum) )
            lumiXingDict[ (runnum, cmslsnum) ] = xingLum
        del query
        dbsession.transaction().commit()
        return lumiXingDict      
    except Exception as e:
        print(str (e))
        print("whoops")
        dbsession.transaction().rollback()
        del dbsession


def flatten (obj):
    '''Given nested lists or tuples, returns a single flattened list'''
    result = []
    for piece in obj:
        if hasattr (piece, '__iter__') and not isinstance (piece, str):
            result.extend( flatten (piece) )
        else:
            result.append (piece)
    return result    


def mergeXingLumi (triplet, xingLumiDict):
    '''Given general xing information and a xingLumiDict, the xing
    luminosity information is merged with the general information'''
    runNumber = triplet[0]
    deadTable = triplet[2]
    for lumi, lumiList in six.iteritems(deadTable):
        key = ( int(runNumber), int(lumi) )
        xingLumiValues = xingLumiDict.get (key)
        if xingLumiValues:
            lumiList.append( flatten (xingLumiValues) )


def setupSession (connectString, siteconfpath, parameters, debug = False):
    '''returns database session'''
    connectparser = connectstrParser.connectstrParser (connectString)
    connectparser.parse()
    usedefaultfrontierconfig = False
    cacheconfigpath = ''
    if connectparser.needsitelocalinfo():
        if not siteconfpath:
            cacheconfigpath = os.environ['CMS_PATH']
            if cacheconfigpath:
                cacheconfigpath = os.path.join (cacheconfigpath, 'SITECONF', 'local', 'JobConfig', 'site-local-config.xml')
            else:
                usedefaultfrontierconfig = True
        else:
            cacheconfigpath = siteconfpath
            cacheconfigpath = os.path.join (cacheconfigpath, 'site-local-config.xml')
        ccp = cacheconfigParser.cacheconfigParser()
        if usedefaultfrontierconfig:
            ccp.parseString (parameters.defaultfrontierConfigString)
        else:
            ccp.parse (cacheconfigpath)
        connectString = connectparser.fullfrontierStr (connectparser.schemaname(), ccp.parameterdict())
    svc = coral.ConnectionService()
    if debug :
        msg = coral.MessageStream ('')
        msg.setMsgVerbosity (coral.message_Level_Debug)
        parameters.verbose = True
    session = svc.connect (connectString, accessMode = coral.access_ReadOnly)
    session.typeConverter().setCppTypeForSqlType ("unsigned int", "NUMBER (10)")
    session.typeConverter().setCppTypeForSqlType ("unsigned long long", "NUMBER (20)")
    return session, svc



###==============real api=====###

def allruns(schemaHandle,requireRunsummary=True,requireLumisummary=False,requireTrg=False,requireHlt=False):
    '''
    find all runs in the DB. By default requires cmsrunsummary table contain the run. The condition can be loosed in situation where db loading failed on certain data portions.
    '''
    if not requireRunsummary and not requireLumiummary and not requireTrg and not requireHlt:
        print('must require at least one table')
        raise RuntimeError('one table')
    runresult=[]
    runlist=[]
    numdups=0
    if requireRunsummary:
        numdups=numdups+1
        queryHandle=schemaHandle.newQuery()
        queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
        queryHandle.addToOutputList("RUNNUM","run")
        #queryBind=coral.AttributeList()
        result=coral.AttributeList()
        result.extend("run","unsigned int")
        queryHandle.defineOutput(result)
        cursor=queryHandle.execute()
        while next(cursor):
            r=cursor.currentRow()['run'].data()
            runlist.append(r)
        del queryHandle
    if requireLumisummary:
        numdups=numdups+1
        queryHandle=schemaHandle.newQuery()
        queryHandle.addToTableList(nameDealer.lumisummaryTableName())
        queryHandle.addToOutputList("distinct RUNNUM","run")
        #queryBind=coral.AttributeList()
        result=coral.AttributeList()
        result.extend("run","unsigned int")
        queryHandle.defineOutput(result)
        cursor=queryHandle.execute()
        while next(cursor):
            r=cursor.currentRow()['run'].data()
            runlist.append(r)
        del queryHandle
    if requireTrg:
        numdups=numdups+1
        queryHandle=schemaHandle.newQuery()
        queryHandle.addToTableList(nameDealer.trgTableName())
        queryHandle.addToOutputList("distinct RUNNUM","run")
        #queryBind=coral.AttributeList()
        result=coral.AttributeList()
        result.extend("run","unsigned int")
        queryHandle.defineOutput(result)
        cursor=queryHandle.execute()
        while next(cursor):
            r=cursor.currentRow()['run'].data()
            runlist.append(r)
        del queryHandle
    if requireHlt:
        numdups=numdups+1
        queryHandle=schemaHandle.newQuery()
        queryHandle.addToTableList(nameDealer.hltTableName())
        queryHandle.addToOutputList("distinct RUNNUM","run")
        #queryBind=coral.AttributeList()
        result=coral.AttributeList()
        result.extend("run","unsigned int")
        queryHandle.defineOutput(result)
        cursor=queryHandle.execute()
        while next(cursor):
            r=cursor.currentRow()['run'].data()
            runlist.append(r)
        del queryHandle
    dupresult=CommonUtil.count_dups(runlist)
    for dup in dupresult:
        if dup[1]==numdups:
            runresult.append(dup[0])
    runresult.sort()
    return runresult

def validation(queryHandle,run=None,cmsls=None):
    '''retrieve validation data per run or all
    input: run. if not run, retrive all; if cmslsnum selection list pesent, filter out unselected result
    output: {run:[[cmslsnum,status,comment]]}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.lumivalidationTableName())
    queryHandle.addToOutputList('RUNNUM','runnum')
    queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('FLAG','flag')
    queryHandle.addToOutputList('COMMENT','comment')
    if run:
        queryCondition='RUNNUM=:runnum'
        queryBind=coral.AttributeList()
        queryBind.extend('runnum','unsigned int')
        queryBind['runnum'].setData(run)
        queryHandle.setCondition(queryCondition,queryBind)
    queryResult=coral.AttributeList()
    queryResult.extend('runnum','unsigned int')
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('flag','string')
    queryResult.extend('comment','string')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        runnum=cursor.currentRow()['runnum'].data()
        if runnum not in result:
            result[runnum]=[]
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        flag=cursor.currentRow()['flag'].data()
        comment=cursor.currentRow()['comment'].data()
        result[runnum].append([cmslsnum,flag,comment])
    if run and cmsls and len(cmsls)!=0:
        selectedresult={}
        for runnum,perrundata in result.items():
            for lsdata in perrundata:
                if lsdata[0] not in cmsls:
                    continue
                if runnum not in selectedresult:
                    selectedresult[runnum]=[]
                selectedresult[runnum].append(lsdata)
        return selectedresult
    else:
        return result
    
def allfills(queryHandle,filtercrazy=True):
    '''select distinct fillnum from cmsrunsummary
    there are crazy fill numbers. we assume they are not valid runs
    '''
    result=[]
    queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
    queryHandle.addToOutputList('distinct FILLNUM','fillnum')
    
    if filtercrazy:
        queryCondition='FILLNUM>:zero and FILLNUM<:crazybig'
        queryBind=coral.AttributeList()
        queryBind.extend('zero','unsigned int')
        queryBind.extend('crazybig','unsigned int')
        queryBind['zero'].setData(int(0))
        queryBind['crazybig'].setData(int(29701))
        queryHandle.setCondition(queryCondition,queryBind)
    queryResult=coral.AttributeList()
    queryResult.extend('fillnum','unsigned int')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        result.append(cursor.currentRow()['fillnum'].data())
    result.sort()
    return result
def runsummaryByrun(queryHandle,runnum):
    '''
    select fillnum,sequence,hltkey,to_char(starttime),to_char(stoptime) from cmsrunsummary where runnum=:runnum
    output: [fillnum,sequence,hltkey,starttime,stoptime]
    '''
    t=lumiTime.lumiTime()
    result=[]
    queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition['runnum'].setData(int(runnum))
    queryHandle.addToOutputList('FILLNUM','fillnum')
    queryHandle.addToOutputList('SEQUENCE','sequence')
    queryHandle.addToOutputList('HLTKEY','hltkey')
    queryHandle.addToOutputList('to_char(STARTTIME,\''+t.coraltimefm+'\')','starttime')
    queryHandle.addToOutputList('to_char(STOPTIME,\''+t.coraltimefm+'\')','stoptime')
    queryHandle.setCondition('RUNNUM=:runnum',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('fillnum','unsigned int')
    queryResult.extend('sequence','string')
    queryResult.extend('hltkey','string')
    queryResult.extend('starttime','string')
    queryResult.extend('stoptime','string')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        result.append(cursor.currentRow()['fillnum'].data())
        result.append(cursor.currentRow()['sequence'].data())
        result.append(cursor.currentRow()['hltkey'].data())
        result.append(cursor.currentRow()['starttime'].data())
        result.append(cursor.currentRow()['stoptime'].data())
    #if len(result)!=5:
    #    print 'wrong runsummary result'
    #    raise
    return result

def lumisummaryByrun(queryHandle,runnum,lumiversion,beamstatus=None,beamenergy=None,beamenergyfluctuation=0.09,finecorrections=None):
    '''
    one can impose beamstatus, beamenergy selections at the SQL query level or process them later from the general result
    select cmslsnum,instlumi,numorbit,startorbit,beamstatus,beamenery from lumisummary where runnum=:runnum and lumiversion=:lumiversion order by startorbit;
    output: [[cmslsnum,instlumi,numorbit,startorbit,beamstatus,beamenergy,cmsalive]]
    Note: the non-cmsalive LS are included in the result
    '''
    result=[]
    queryHandle.addToTableList(nameDealer.lumisummaryTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition.extend('lumiversion','string')
    conditionstring='RUNNUM=:runnum and LUMIVERSION=:lumiversion'
    queryCondition['runnum'].setData(int(runnum))
    queryCondition['lumiversion'].setData(lumiversion)
    queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('INSTLUMI','instlumi')
    queryHandle.addToOutputList('NUMORBIT','numorbit')
    queryHandle.addToOutputList('STARTORBIT','startorbit')
    queryHandle.addToOutputList('BEAMSTATUS','beamstatus')
    queryHandle.addToOutputList('BEAMENERGY','beamenergy')
    queryHandle.addToOutputList('CMSALIVE','cmsalive')
    if beamstatus and len(beamstatus)!=0:
        conditionstring=conditionstring+' and BEAMSTATUS=:beamstatus'
        queryCondition.extend('beamstatus','string')
        queryCondition['beamstatus'].setData(beamstatus)
    if beamenergy:
        minBeamenergy=float(beamenergy*(1.0-beamenergyfluctuation))
        maxBeamenergy=float(beamenergy*(1.0+beamenergyfluctuation))
        conditionstring=conditionstring+' and BEAMENERGY>:minBeamenergy and BEAMENERGY<:maxBeamenergy'
        queryCondition.extend('minBeamenergy','float')
        queryCondition.extend('maxBeamenergy','float')
        queryCondition['minBeamenergy'].setData(float(minBeamenergy))
        queryCondition['maxBeamenergy'].setData(float(maxBeamenergy))
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('instlumi','float')
    queryResult.extend('numorbit','unsigned int')
    queryResult.extend('startorbit','unsigned int')
    queryResult.extend('beamstatus','string')
    queryResult.extend('beamenergy','float')
    queryResult.extend('cmsalive','unsigned int')
    queryHandle.defineOutput(queryResult)
    queryHandle.setCondition(conditionstring,queryCondition)
    queryHandle.addToOrderList('startorbit')
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        instlumi=cursor.currentRow()['instlumi'].data()
        if finecorrections:
            instlumi=lumiCorrections.applyfinecorrection(instlumi,finecorrections[0],finecorrections[1],finecorrections[2])
        numorbit=cursor.currentRow()['numorbit'].data()
        startorbit=cursor.currentRow()['startorbit'].data()
        beamstatus=cursor.currentRow()['beamstatus'].data()
        beamenergy=cursor.currentRow()['beamenergy'].data()
        cmsalive=cursor.currentRow()['cmsalive'].data()
        result.append([cmslsnum,instlumi,numorbit,startorbit,beamstatus,beamenergy,cmsalive])
    return result

def lumisumByrun(queryHandle,runnum,lumiversion,beamstatus=None,beamenergy=None,beamenergyfluctuation=0.09,finecorrections=None):
    '''
    beamenergy unit : GeV
    beamenergyfluctuation : fraction allowed to fluctuate around beamenergy value
    select instlumi from lumisummary where runnum=:runnum and lumiversion=:lumiversion
    output: float sum(instlumi)
    Note: the output is the raw result, need to apply LS length in time(sec)
    '''
    result=[]
    queryHandle.addToTableList(nameDealer.lumisummaryTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition.extend('lumiversion','string')
    
    queryCondition['runnum'].setData(int(runnum))
    queryCondition['lumiversion'].setData(lumiversion)
    queryHandle.addToOutputList('INSTLUMI','instlumi')
    conditionstring='RUNNUM=:runnum and LUMIVERSION=:lumiversion'
    if beamstatus and len(beamstatus)!=0:
        conditionstring=conditionstring+' and BEAMSTATUS=:beamstatus'
        queryCondition.extend('beamstatus','string')
        queryCondition['beamstatus'].setData(beamstatus)
    if beamenergy and beamenergy!=0.0:
        minBeamenergy=float(beamenergy*(1.0-beamenergyfluctuation))
        maxBeamenergy=float(beamenergy*(1.0+beamenergyfluctuation))
        conditionstring=conditionstring+' and BEAMENERGY>:minBeamenergy and BEAMENERGY<:maxBeamenergy'
        queryCondition.extend('minBeamenergy','float')
        queryCondition.extend('maxBeamenergy','float')
        queryCondition['minBeamenergy'].setData(float(minBeamenergy))
        queryCondition['maxBeamenergy'].setData(float(maxBeamenergy))
    queryHandle.setCondition(conditionstring,queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('instlumi','float')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        instlumi=cursor.currentRow()['instlumi'].data()
        if instlumi:
            if finecorrections:
                instlumi=lumiCorrections.applyfinecorrection(instlumi,finecorrections[0],finecorrections[1],finecorrections[2])
            result.append(instlumi)
    if result:
        return sum(result)
    else:
        return 0.0

def trgbitzeroByrun(queryHandle,runnum):
    '''
    select cmslsnum,trgcount,deadtime,bitname,prescale from trg where runnum=:runnum and bitnum=0;
    output: {cmslsnum:[trgcount,deadtime,bitname,prescale]}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.trgTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition.extend('bitnum','unsigned int')
    queryCondition['runnum'].setData(int(runnum))
    queryCondition['bitnum'].setData(int(0))
    queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('TRGCOUNT','trgcount')
    queryHandle.addToOutputList('DEADTIME','deadtime')
    queryHandle.addToOutputList('BITNAME','bitname')
    queryHandle.addToOutputList('PRESCALE','prescale')
    queryHandle.setCondition('RUNNUM=:runnum and BITNUM=:bitnum',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('trgcount','unsigned int')
    queryResult.extend('deadtime','unsigned int')
    queryResult.extend('bitname','string')
    queryResult.extend('prescale','unsigned int')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        trgcount=cursor.currentRow()['trgcount'].data()
        deadtime=cursor.currentRow()['deadtime'].data()
        bitname=cursor.currentRow()['bitname'].data()
        prescale=cursor.currentRow()['prescale'].data()
        if cmslsnum not in result:
            result[cmslsnum]=[trgcount,deadtime,bitname,prescale]
    return result

def lumisummarytrgbitzeroByrun(queryHandle,runnum,lumiversion,beamstatus=None,beamenergy=None,beamenergyfluctuation=0.09,finecorrections=None):
    '''
    select l.cmslsnum,l.instlumi,l.numorbit,l.startorbit,l.beamstatus,l.beamenery,t.trgcount,t.deadtime,t.bitname,t.prescale from trg t,lumisummary l where t.bitnum=:bitnum and l.runnum=:runnum and l.lumiversion=:lumiversion and l.runnum=t.runnum and t.cmslsnum=l.cmslsnum; 
    Everything you ever need to know about bitzero and avg luminosity. Since we do not know if joint query is better of sperate, support both.
    output: {cmslsnum:[instlumi,numorbit,startorbit,beamstatus,beamenergy,bitzerocount,deadtime,bitname,prescale]}
    Note: only cmsalive LS are included in the result. Therefore, this function cannot be used for calculating delivered!
    '''
    result={}
    queryHandle.addToTableList(nameDealer.trgTableName(),'t')
    queryHandle.addToTableList(nameDealer.lumisummaryTableName(),'l')
    queryCondition=coral.AttributeList()
    queryCondition.extend('bitnum','unsigned int')
    queryCondition.extend('runnum','unsigned int')
    queryCondition.extend('lumiversion','string')
    queryCondition['bitnum'].setData(int(0))        
    queryCondition['runnum'].setData(int(runnum))
    queryCondition['lumiversion'].setData(lumiversion)
    
    queryHandle.addToOutputList('l.CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('l.INSTLUMI','instlumi')
    queryHandle.addToOutputList('l.NUMORBIT','numorbit')
    queryHandle.addToOutputList('l.STARTORBIT','startorbit')
    queryHandle.addToOutputList('l.BEAMSTATUS','beamstatus')
    queryHandle.addToOutputList('l.BEAMENERGY','beamenergy')
    queryHandle.addToOutputList('t.TRGCOUNT','trgcount')
    queryHandle.addToOutputList('t.DEADTIME','deadtime')
    queryHandle.addToOutputList('t.BITNAME','bitname')
    queryHandle.addToOutputList('t.PRESCALE','prescale')
    conditionstring='t.BITNUM=:bitnum and l.RUNNUM=:runnum and l.LUMIVERSION=:lumiversion and l.RUNNUM=t.RUNNUM and t.CMSLSNUM=l.CMSLSNUM'
    if beamstatus and len(beamstatus)!=0:
        conditionstring=conditionstring+' and l.BEAMSTATUS=:beamstatus'
        queryCondition.extend('beamstatus','string')
        queryCondition['beamstatus'].setData(beamstatus)
    if beamenergy and beamenergy!=0.0:
        minBeamenergy=float(beamenergy*(1-beamenergyfluctuation))
        maxBeamenergy=float(beamenergy*(1+beamenergyfluctuation))
        conditionstring=conditionstring+' and l.BEAMENERGY>:minBeamenergy and l.BEAMENERGY<:maxBeamenergy'
        queryCondition.extend('minBeamenergy','float')
        queryCondition.extend('maxBeamenergy','float')
        queryCondition['minBeamenergy'].setData(float(minBeamenergy))
        queryCondition['maxBeamenergy'].setData(float(maxBeamenergy))
    queryHandle.setCondition(conditionstring,queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('instlumi','float')
    queryResult.extend('numorbit','unsigned int')
    queryResult.extend('startorbit','unsigned int')
    queryResult.extend('beamstatus','string')
    queryResult.extend('beamenergy','float')  
    queryResult.extend('trgcount','unsigned int')
    queryResult.extend('deadtime','unsigned int')
    queryResult.extend('bitname','string')
    queryResult.extend('prescale','unsigned int')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        instlumi=cursor.currentRow()['instlumi'].data()
        if finecorrections:
            instlumi=lumiCorrections.applyfinecorrection(instlumi,finecorrections[0],finecorrections[1],finecorrections[2])
        numorbit=cursor.currentRow()['numorbit'].data()
        startorbit=cursor.currentRow()['startorbit'].data()
        beamstatus=cursor.currentRow()['beamstatus'].data()
        beamenergy=cursor.currentRow()['beamenergy'].data()
        trgcount=cursor.currentRow()['trgcount'].data()
        deadtime=cursor.currentRow()['deadtime'].data()
        bitname=cursor.currentRow()['bitname'].data()
        prescale=cursor.currentRow()['prescale'].data()
        if cmslsnum not in result:
            result[cmslsnum]=[instlumi,numorbit,startorbit,beamstatus,beamenergy,trgcount,deadtime,bitname,prescale]
    return result

def trgBybitnameByrun(queryHandle,runnum,bitname):
    '''
    select cmslsnum,trgcount,deadtime,bitnum,prescale from trg where runnum=:runnum and bitname=:bitname;
    output: {cmslsnum:[trgcount,deadtime,bitnum,prescale]}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.trgTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition.extend('bitname','string')
    queryCondition['runnum'].setData(int(runnum))
    queryCondition['bitname'].setData(bitname)        
    queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('TRGCOUNT','trgcount')
    queryHandle.addToOutputList('DEADTIME','deadtime')
    queryHandle.addToOutputList('BITNUM','bitnum')
    queryHandle.addToOutputList('PRESCALE','prescale')
    queryHandle.setCondition('RUNNUM=:runnum and BITNAME=:bitname',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('trgcount','unsigned int')
    queryResult.extend('deadtime','unsigned long long')
    queryResult.extend('bitnum','unsigned int')
    queryResult.extend('prescale','unsigned int')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        trgcount=cursor.currentRow()['trgcount'].data()
        deadtime=cursor.currentRow()['deadtime'].data()
        bitnum=cursor.currentRow()['bitnum'].data()
        prescale=cursor.currentRow()['prescale'].data()
        if cmslsnum not in result:
            result[cmslsnum]=[trgcount,deadtime,bitnum,prescale]
    return result

def trgAllbitsByrun(queryHandle,runnum):
    '''
    all you ever want to know about trigger
    select cmslsnum,trgcount,deadtime,bitnum,bitname,prescale from trg where runnum=:runnum order by  bitnum,cmslsnum
    this can be changed to blob query later
    output: {cmslsnum:{bitname:[bitnum,trgcount,deadtime,prescale]}}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.trgTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition['runnum'].setData(int(runnum))
    queryHandle.addToOutputList('cmslsnum')
    queryHandle.addToOutputList('trgcount')
    queryHandle.addToOutputList('deadtime')
    queryHandle.addToOutputList('bitnum')
    queryHandle.addToOutputList('bitname')
    queryHandle.addToOutputList('prescale')
    queryHandle.setCondition('runnum=:runnum',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('trgcount','unsigned int')
    queryResult.extend('deadtime','unsigned long long')
    queryResult.extend('bitnum','unsigned int')
    queryResult.extend('bitname','string')
    queryResult.extend('prescale','unsigned int')
    queryHandle.defineOutput(queryResult)
    queryHandle.addToOrderList('bitnum')
    queryHandle.addToOrderList('cmslsnum')
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        trgcount=cursor.currentRow()['trgcount'].data()
        deadtime=cursor.currentRow()['deadtime'].data()
        bitnum=cursor.currentRow()['bitnum'].data()
        bitname=cursor.currentRow()['bitname'].data()
        prescale=cursor.currentRow()['prescale'].data()
        if cmslsnum not in result:
            dataperLS={}
            dataperLS[bitname]=[bitnum,trgcount,deadtime,prescale]
            result[cmslsnum]=dataperLS
        else:
            result[cmslsnum][bitname]=[bitnum,trgcount,deadtime,prescale]
    return result


def hltBypathByrun(queryHandle,runnum,hltpath):
    '''
    select cmslsnum,inputcount,acceptcount,prescale from hlt where runnum=:runnum and pathname=:pathname
    output: {cmslsnum:[inputcount,acceptcount,prescale]}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.hltTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition.extend('pathname','string')
    queryCondition['runnum'].setData(int(runnum))
    queryCondition['pathname'].setData(hltpath)
    queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('INPUTCOUNT','inputcount')
    queryHandle.addToOutputList('ACCEPTCOUNT','acceptcount')
    queryHandle.addToOutputList('PRESCALE','prescale')
    queryHandle.setCondition('RUNNUM=:runnum and PATHNAME=:pathname',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('inputcount','unsigned int')
    queryResult.extend('acceptcount','unsigned int')
    queryResult.extend('prescale','unsigned int')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        inputcount=cursor.currentRow()['inputcount'].data()
        acceptcount=cursor.currentRow()['acceptcount'].data()
        prescale=cursor.currentRow()['prescale'].data()
        if cmslsnum not in result:
            result[cmslsnum]=[inputcount,acceptcount,prescale]
    return result

def hltAllpathByrun(queryHandle,runnum):
    '''
    select cmslsnum,inputcount,acceptcount,prescale,pathname from hlt where runnum=:runnum
    this can be changed to blob query later
    output: {cmslsnum:{pathname:[inputcount,acceptcount,prescale]}}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.hltTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition['runnum'].setData(int(runnum))
    queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('INPUTCOUNT','inputcount')
    queryHandle.addToOutputList('ACCEPTCOUNT','acceptcount')
    queryHandle.addToOutputList('PRESCALE','prescale')
    queryHandle.addToOutputList('PATHNAME','pathname')
    queryHandle.setCondition('RUNNUM=:runnum',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('inputcount','unsigned int')
    queryResult.extend('acceptcount','unsigned int')
    queryResult.extend('prescale','unsigned int')
    queryResult.extend('pathname','string')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        inputcount=cursor.currentRow()['inputcount'].data()
        acceptcount=cursor.currentRow()['acceptcount'].data()
        prescale=cursor.currentRow()['prescale'].data()
        pathname=cursor.currentRow()['pathname'].data()
        if cmslsnum not in result:
            dataperLS={}
            dataperLS[pathname]=[inputcount,acceptcount,prescale]
            result[cmslsnum]=dataperLS
        else:
            result[cmslsnum][pathname]=[inputcount,acceptcount,prescale]
    return result


def beamIntensityForRun(query,parameters,runnum):
    '''
    select CMSBXINDEXBLOB,BEAMINTENSITYBLOB_1,BEAMINTENSITYBLOB_2 from LUMISUMMARY where runnum=146315 and LUMIVERSION='0001'
    
    output : result {startorbit: [(bxidx,beam1intensity,beam2intensity)]}
    '''
    result={} #{startorbit:[(bxidx,occlumi,occlumierr,beam1intensity,beam2intensity)]}
    
    lumisummaryOutput=coral.AttributeList()
    lumisummaryOutput.extend('cmslsnum','unsigned int')
    lumisummaryOutput.extend('startorbit','unsigned int')
    lumisummaryOutput.extend('bxindexblob','blob');
    lumisummaryOutput.extend('beamintensityblob1','blob');
    lumisummaryOutput.extend('beamintensityblob2','blob');
    condition=coral.AttributeList()
    condition.extend('runnum','unsigned int')
    condition.extend('lumiversion','string')
    condition['runnum'].setData(int(runnum))
    condition['lumiversion'].setData(parameters.lumiversion)
    
    query.addToTableList(parameters.lumisummaryname)
    query.addToOutputList('CMSLSNUM','cmslsnum')
    query.addToOutputList('STARTORBIT','startorbit')
    query.addToOutputList('CMSBXINDEXBLOB','bxindexblob')
    query.addToOutputList('BEAMINTENSITYBLOB_1','beamintensityblob1')
    query.addToOutputList('BEAMINTENSITYBLOB_2','beamintensityblob2')
    query.setCondition('RUNNUM=:runnum AND LUMIVERSION=:lumiversion',condition)
    query.defineOutput(lumisummaryOutput)
    cursor=query.execute()
    while next(cursor):
        #cmslsnum=cursor.currentRow()['cmslsnum'].data()
        startorbit=cursor.currentRow()['startorbit'].data()
        bxidx=[]
        bb1=[]
        bb2=[]
        bxindexblob=None
        beamintensityblob1=None
        beamintensityblob2=None
        if not cursor.currentRow()["bxindexblob"].isNull():
            bxindexblob=cursor.currentRow()['bxindexblob'].data()
            if bxindexblob and bxindexblob.readline()!=None:
                bxidx=CommonUtil.unpackBlobtoArray(bxindexblob,'h')
                if not cursor.currentRow()['beamintensityblob1'].isNull():
                    beamintensityblob1=cursor.currentRow()['beamintensityblob1'].data()
                    if beamintensityblob1 and beamintensityblob1.readline()!=None:
                        bb1=CommonUtil.unpackBlobtoArray(beamintensityblob1,'f')
                if not cursor.currentRow()['beamintensityblob2'].isNull():
                    beamintensityblob2=cursor.currentRow()['beamintensityblob2'].data()
                    if beamintensityblob2 and beamintensityblob2.readline()!=None:
                        bb2=CommonUtil.unpackBlobtoArray(beamintensityblob2,'f')
        if startorbit not in result:
            result[startorbit]=[]
        for idx,bxidxvalue in enumerate(bxidx):
            try:
                b1intensity=bb1[idx]
            except IndexError:
                b1intensity=0.0
            try:
                b2intensity=bb2[idx]
            except IndexError:
                b2intensity=0.0
            result[startorbit].append((bxidxvalue,b1intensity,b2intensity))
        del bxidx[:]
        del bb1[:]
        del bb2[:]
    return result
    
def calibratedDetailForRunLimitresult(query,parameters,runnum,algoname='OCC1',finecorrection=None):
    '''select 
    s.cmslsnum,d.bxlumivalue,d.bxlumierror,d.bxlumiquality,d.algoname from LUMIDETAIL d,LUMISUMMARY s where s.runnum=133885 and d.algoname='OCC1' and s.lumisummary_id=d.lumisummary_id order by s.startorbit,s.cmslsnum
    result={(startorbit,cmslsnum):[(index,lumivalue,lumierr),]}
    '''
    result={}
    detailOutput=coral.AttributeList()
    detailOutput.extend('cmslsnum','unsigned int')
    detailOutput.extend('startorbit','unsigned int')
    detailOutput.extend('bxlumivalue','blob')
    detailOutput.extend('bxlumierror','blob')
    detailCondition=coral.AttributeList()
    detailCondition.extend('runnum','unsigned int')
    detailCondition.extend('algoname','string')
    detailCondition['runnum'].setData(runnum)
    detailCondition['algoname'].setData(algoname)

    query.addToTableList(parameters.lumisummaryname,'s')
    query.addToTableList(parameters.lumidetailname,'d')
    query.addToOutputList('s.CMSLSNUM','cmslsnum')
    query.addToOutputList('s.STARTORBIT','startorbit')
    query.addToOutputList('d.BXLUMIVALUE','bxlumivalue')
    query.addToOutputList('d.BXLUMIERROR','bxlumierror')
    query.addToOutputList('d.BXLUMIQUALITY','bxlumiquality')
    query.setCondition('s.RUNNUM=:runnum and d.ALGONAME=:algoname and s.LUMISUMMARY_ID=d.LUMISUMMARY_ID',detailCondition)
    query.defineOutput(detailOutput)
    cursor=query.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        bxlumivalue=cursor.currentRow()['bxlumivalue'].data()
        bxlumierror=cursor.currentRow()['bxlumierror'].data()
        startorbit=cursor.currentRow()['startorbit'].data()
        bxlumivalueArray=array.array('f')
        bxlumivalueArray.fromstring(bxlumivalue.readline())
        bxlumierrorArray=array.array('f')
        bxlumierrorArray.fromstring(bxlumierror.readline())
        xingLum=[]
        #apply selection criteria
        maxlumi=0.0
        if len(bxlumivalueArray)!=0:
            maxlumi=max(bxlumivalueArray)
        avginstlumi=0.0
        if len(bxlumivalueArray)!=0:
            avginstlumi=sum(bxlumivalueArray)
        for index,lum in enumerate(bxlumivalueArray):
            lumierror = bxlumierrorArray[index]
            if lum<max(parameters.xingMinLum,maxlumi*0.2): 
                continue
            mynorm=parameters.normFactor
            if finecorrection:
                lum=lumiCorrections.applyfinecorrectionBX(lum,avginstlumi*mynorm,mynorm,finecorrection[0],finecorrection[1],finecorrection[2])
                lumierror=lumiCorrections.applyfinecorrectionBX(lumierror,avginstlumi*mynorm,mynorm,finecorrection[0],finecorrection[1],finecorrection[2])
            else:
                lum*=mynorm
                lumierror*=mynorm
            xingLum.append( (index,lum,lumierror) )
        if len(xingLum)!=0:
            result[(startorbit,cmslsnum)]=xingLum
    return result
   
def lumidetailByrunByAlgo(queryHandle,runnum,algoname='OCC1'):
    '''
    select s.cmslsnum,d.bxlumivalue,d.bxlumierror,d.bxlumiquality,s.startorbit from LUMIDETAIL d,LUMISUMMARY s where s.runnum=:runnum and d.algoname=:algoname and s.lumisummary_id=d.lumisummary_id order by s.startorbit
    output: [[cmslsnum,bxlumivalue,bxlumierror,bxlumiquality,startorbit]]
    since the output is ordered by time, it has to be in seq list format
    '''
    result=[]
    queryHandle.addToTableList(nameDealer.lumidetailTableName(),'d')
    queryHandle.addToTableList(nameDealer.lumisummaryTableName(),'s')
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition.extend('algoname','string')
    queryCondition['runnum'].setData(int(runnum))
    queryCondition['algoname'].setData(algoname)
    queryHandle.addToOutputList('s.CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('d.BXLUMIVALUE','bxlumivalue')
    queryHandle.addToOutputList('d.BXLUMIERROR','bxlumierror')
    queryHandle.addToOutputList('d.BXLUMIQUALITY','bxlumiquality')
    queryHandle.addToOutputList('s.STARTORBIT','startorbit')
    queryHandle.setCondition('s.runnum=:runnum and d.algoname=:algoname and s.lumisummary_id=d.lumisummary_id',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('bxlumivalue','blob')
    queryResult.extend('bxlumierror','blob')
    queryResult.extend('bxlumiquality','blob')
    queryResult.extend('startorbit','unsigned int')    
    queryHandle.addToOrderList('s.STARTORBIT')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        bxlumivalue=cursor.currentRow()['bxlumivalue'].data()
        bxlumierror=cursor.currentRow()['bxlumierror'].data()
        bxlumiquality=cursor.currentRow()['bxlumiquality'].data()
        startorbit=cursor.currentRow()['startorbit'].data()
        result.append([cmslsnum,bxlumivalue,bxlumierror,bxlumiquality,startorbit])
    return result

def lumidetailAllalgosByrun(queryHandle,runnum):
    '''
    select s.cmslsnum,d.bxlumivalue,d.bxlumierror,d.bxlumiquality,d.algoname,s.startorbit from LUMIDETAIL d,LUMISUMMARY s where s.runnum=:runnumber and s.lumisummary_id=d.lumisummary_id order by s.startorbit,d.algoname
    output: {algoname:{cmslsnum:[bxlumivalue,bxlumierror,bxlumiquality,startorbit]}}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.lumidetailTableName(),'d')
    queryHandle.addToTableList(nameDealer.lumisummaryTableName(),'s')
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition['runnum'].setData(int(runnum))
    queryHandle.addToOutputList('s.CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('d.BXLUMIVALUE','bxlumivalue')
    queryHandle.addToOutputList('d.BXLUMIERROR','bxlumierror')
    queryHandle.addToOutputList('d.BXLUMIQUALITY','bxlumiquality')
    queryHandle.addToOutputList('d.ALGONAME','algoname')
    queryHandle.addToOutputList('s.STARTORBIT','startorbit')
    queryHandle.setCondition('s.RUNNUM=:runnum and s.LUMISUMMARY_ID=d.LUMISUMMARY_ID',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('bxlumivalue','blob')
    queryResult.extend('bxlumierror','blob')
    queryResult.extend('bxlumiquality','blob')
    queryResult.extend('algoname','string')
    queryResult.extend('startorbit','unsigned int')    
    queryHandle.addToOrderList('startorbit')
    queryHandle.addToOrderList('algoname')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        bxlumivalue=cursor.currentRow()['bxlumivalue'].data()
        bxlumierror=cursor.currentRow()['bxlumierror'].data()
        bxlumiquality=cursor.currentRow()['bxlumiquality'].data()
        algoname=cursor.currentRow()['algoname'].data()
        startorbit=cursor.currentRow()['startorbit'].data()
        if algoname not in result:
            dataPerAlgo={}
            dataPerAlgo[cmslsnum]=[bxlumivalue,bxlumierror,bxlumiquality,startorbit]
            result[algoname]=dataPerAlgo
        else:
            result[algoname][cmslsnum]=[bxlumivalue,bxlumierror,bxlumiquality,startorbit]           
    return result

def hlttrgMappingByrun(queryHandle,runnum):
    '''
    select m.hltpathname,m.l1seed from cmsrunsummary r,trghltmap m where r.runnum=:runnum and m.hltkey=r.hltkey
    output: {hltpath:l1seed}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName(),'r')
    queryHandle.addToTableList(nameDealer.trghltMapTableName(),'m')
    queryCondition=coral.AttributeList()
    queryCondition.extend('runnum','unsigned int')
    queryCondition['runnum'].setData(int(runnum))
    queryHandle.addToOutputList('m.HLTPATHNAME','hltpathname')
    queryHandle.addToOutputList('m.L1SEED','l1seed')
    queryHandle.setCondition('r.RUNNUM=:runnum and m.HLTKEY=r.HLTKEY',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('hltpathname','string')
    queryResult.extend('l1seed','string')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        hltpathname=cursor.currentRow()['hltpathname'].data()
        l1seed=cursor.currentRow()['l1seed'].data()
        if hltpathname not in result:
            result[hltpathname]=l1seed
    return result

def runsByfillrange(queryHandle,minFill,maxFill):
    '''
    find all runs in the fill range inclusive
    select runnum,fillnum from cmsrunsummary where fillnum>=:minFill and fillnum<=:maxFill
    output: fillDict={fillnum:[runlist]}
    '''
    result={}
    queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('minFill','unsigned int')
    queryCondition.extend('maxFill','unsigned int')
    queryCondition['minFill'].setData(int(minFill))
    queryCondition['maxFill'].setData(int(maxFill))
    queryHandle.addToOutputList('RUNNUM','runnum')
    queryHandle.addToOutputList('FILLNUM','fillnum')
    queryHandle.setCondition('FILLNUM>=:minFill and FILLNUM<=:maxFill',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('runnum','unsigned int')
    queryResult.extend('fillnum','unsigned int')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        runnum=cursor.currentRow()['runnum'].data()
        fillnum=cursor.currentRow()['fillnum'].data()
        if fillnum not in result:
            result[fillnum]=[runnum]
        else:
            result[fillnum].append(runnum)
    return result

def runsByTimerange(queryHandle,minTime,maxTime):
    '''
    find all runs in the time range inclusive
    the selected run must have started after minTime and finished by maxTime
    select runnum,to_char(startTime),to_char(stopTime) from cmsrunsummary where startTime>=timestamp(minTime) and stopTime<=timestamp(maxTime);
    input: minTime,maxTime in python obj datetime.datetime
    output: {runnum:[starttime,stoptime]} return in python obj datetime.datetime
    '''
    t=lumiTime.lumiTime()
    result={}
    coralminTime=coral.TimeStamp(minTime.year,minTime.month,minTime.day,minTime.hour,minTime.minute,minTime.second,0)
    coralmaxTime=coral.TimeStamp(maxTime.year,maxTime.month,maxTime.day,maxTime.hour,maxTime.minute,maxTime.second,0)
    queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
    queryCondition=coral.AttributeList()
    queryCondition.extend('minTime','time stamp')
    queryCondition.extend('maxTime','time stamp')
    queryCondition['minTime'].setData(coralminTime)
    queryCondition['maxTime'].setData(coralmaxTime)
    queryHandle.addToOutputList('RUNNUM','runnum')
    queryHandle.addToOutputList('TO_CHAR(STARTTIME,\''+t.coraltimefm+'\')','starttime')
    queryHandle.addToOutputList('TO_CHAR(STOPTIME,\''+t.coraltimefm+'\')','stoptime')
    queryHandle.setCondition('STARTTIME>=:minTime and STOPTIME<=:maxTime',queryCondition)
    queryResult=coral.AttributeList()
    queryResult.extend('runnum','unsigned int')
    queryResult.extend('starttime','string')
    queryResult.extend('stoptime','string')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while next(cursor):
        runnum=cursor.currentRow()['runnum'].data()
        starttimeStr=cursor.currentRow()['starttime'].data()
        stoptimeStr=cursor.currentRow()['stoptime'].data()
        if runnum not in result:
            result[runnum]=[t.StrToDatetime(starttimeStr),t.StrToDatetime(stoptimeStr)]
    return result
    
if __name__=='__main__':
    msg=coral.MessageStream('')
    #msg.setMsgVerbosity(coral.message_Level_Debug)
    msg.setMsgVerbosity(coral.message_Level_Error)
    os.environ['CORAL_AUTH_PATH']='/afs/cern.ch/cms/DB/lumi'
    svc = coral.ConnectionService()
    connectstr='oracle://cms_orcoff_prod/cms_lumi_prod'
    session=svc.connect(connectstr,accessMode=coral.access_ReadOnly)
    session.typeConverter().setCppTypeForSqlType("unsigned int","NUMBER(10)")
    session.typeConverter().setCppTypeForSqlType("unsigned long long","NUMBER(20)")
    session.transaction().start(True)
    schema=session.nominalSchema()
    allruns=allruns(schema,requireLumisummary=True,requireTrg=True,requireHlt=True)
    print('allruns in runsummary and lumisummary and trg and hlt ',len(allruns))
    #q=schema.newQuery()
    #runsummaryOut=runsummaryByrun(q,139400)
    #del q
    #q=schema.newQuery()
    #lumisummaryOut=lumisummaryByrun(q,139400,'0001')
    #del q
    #q=schema.newQuery()
    #lumisummaryOutStablebeam7TeV=lumisummaryByrun(q,139400,'0001',beamstatus='STABLE BEAMS',beamenergy=3.5E003,beamenergyfluctuation=0.09)
    #del q
    #q=schema.newQuery()
    #lumitotal=lumisumByrun(q,139400,'0001')
    #del q
    #q=schema.newQuery()
    #lumitotalStablebeam7TeV=lumisumByrun(q,139400,'0001',beamstatus='STABLE BEAMS',beamenergy=3.5E003,beamenergyfluctuation=0.09)
    #del q
    #q=schema.newQuery()
    #trgbitzero=trgbitzeroByrun(q,139400)
    #del q
    #q=schema.newQuery()
    #lumijointrg=lumisummarytrgbitzeroByrun(q,135525,'0001')
    #del q
    #q=schema.newQuery()
    #lumijointrgStablebeam7TeV=lumisummarytrgbitzeroByrun(q,135525,'0001',beamstatus='STABLE BEAMS',beamenergy=3.5E003,beamenergyfluctuation=0.09)
    #del q
    #q=schema.newQuery()
    #trgforbit=trgBybitnameByrun(q,139400,'L1_ZeroBias')
    #del q
    #q=schema.newQuery()
    #trgallbits=trgAllbitsByrun(q,139400)
    #del q
    #q=schema.newQuery()
    #hltbypath=hltBypathByrun(q,139400,'HLT_Mu5')
    #del q
    #q=schema.newQuery()
    #hltallpath=hltAllpathByrun(q,139400)
    #del q
    #q=schema.newQuery()
    #hlttrgmap=hlttrgMappingByrun(q,139400)
    #del q
    #q=schema.newQuery()
    #occ1detail=lumidetailByrunByAlgo(q,139400,'OCC1')
    #del q
    #q=schema.newQuery()
    #alldetail=lumidetailAllalgosByrun(q,139400)
    #del q
    #q=schema.newQuery()
    #runsbyfill=runsByfillrange(q,1150,1170)
    #del q
    #now=datetime.datetime.now()
    #aweek=datetime.timedelta(weeks=1)
    #lastweek=now-aweek
    #print lastweek
    #q=schema.newQuery()
    #runsinaweek=runsByTimerange(q,lastweek,now)
    #del q
    q=schema.newQuery()
    allfills=allfills(q)
    del q
    session.transaction().commit()  
    del session
    del svc
    #print 'runsummaryByrun : ',runsummaryOut
    #print
    #print 'lumisummaryByrun : ',lumisummaryOut
    #print '######'
    #print 'lumisummaryByrun stable beams 7TeV : ',lumisummaryOutStablebeam7TeV
    #print '######'
    #print 'totallumi : ',lumitotal
    #print
    #print
    #print 'totallumi stable beam and 7TeV: ',lumitotalStablebeam7TeV
    #print
    #print 'trgbitzero : ',trgbitzero
    #print 
    #print 'lumijointrg : ', lumijointrg
    #print 'total LS : ',len(lumijointrg)
    #print 'lumijointrg stable beams 7TeV :', lumijointrgStablebeam7TeV
    #print 'total LS : ',len(lumijointrgStablebeam7TeV)
    #print 'trgforbit L1_ZeroBias ',trgforbit
    #print
    #print 'trgallbits ',trgallbits[1] #big query. be aware of speed
    #print
    #print 'hltforpath HLT_Mu5',hltbypath
    #print
    #print 'hltallpath ',hltallpath
    #print
    #print 'hlttrgmap ',hlttrgmap
    #print
    #print 'lumidetail occ1 ',len(occ1detail)
    #print
    #print 'runsbyfill ',runsbyfill
    #print
    #print 'runsinaweek ',runsinaweek.keys()
    print('all fills ',allfills)