@python -x %~f0 %* & goto :EOF

import DCOracle
import re
import string
import time
import win32pdh
import win32api
import win32gui

from com.creo.araxi.database import GetAccountInfo
from pywintypes import FALSE, TRUE
from win32pdh import PDH_FMT_DOUBLE, PDH_FMT_LARGE, PDH_FMT_LONG, PDH_FMT_NOSCALE, PDH_FMT_1000

THREAD_ID = 'ID Thread'
PROCESSOR_TIME = r'% Processor Time'
THREAD_ID_HANDLE = 0
CPU_TIME_HANDLE  = 1


def main():
    computer = win32api.GetComputerName()
    ( threadIDs, threadProcessorTime ) = ThreadStatistics( computer, 'ORACLE' )
    ( sqlTextForProcesses, backgroundProcesses ) = OracleProcessInfo( )
    
    threadIDKeys = threadIDs.keys()
    threadIDKeys.sort()

    sumTime = 0.0
    print 'Thread             bgProcess Thread id % Processor time   Process                Sql text'
    print '------------------ --------- --------- ------------------ ---------------------- ------------------------------'

    for threadName in threadIDKeys:
        threadId = threadIDs[ threadName ]
        backgroundProcess = ''
        processName = ''
        sqlText = ''
        if threadId in sqlTextForProcesses.keys():
            ( processName, sqlText ) = sqlTextForProcesses[ threadId ]
        if threadId in backgroundProcesses.keys():
            backgroundProcess = backgroundProcesses[ threadId ][ 0 ]

        processorTime = threadProcessorTime[ threadId ]
        print '%-18s %9s %9s %-18s %-22s %s' %  ( threadName, backgroundProcess, threadId, processorTime, processName, sqlText )
        sumTime = sumTime + processorTime

    print '\nTotal %% processor time: %s' % sumTime


def OracleProcessInfo( ):
    myDb = Db()
    processInfoQuery = \
        '''\
            SELECT p.spid, s.program, a.sql_text
            FROM   v$session s, v$process p, v$sqlarea a
            WHERE  p.addr=s.paddr and s.sql_address=a.address
        ''' 

    processQueryResults = myDb.Query( processInfoQuery )

    backgroundProcessQuery = \
        '''\
            SELECT vp.spid THREADID, vb.name NAME, vp.program PROCESSNAME, vs.sid SID    
            FROM   v$session vs, v$process vp, v$bgprocess vb    
            WHERE  vb.paddr <> '00' and vb.paddr = vp.addr and vp.addr = vs.paddr
        ''' 
    backgroundProcessQueryResults = myDb.Query( backgroundProcessQuery )
    backgroundProcesses = {}
    for entry in backgroundProcessQueryResults:
        backgroundProcesses[ int( entry[ 0 ] ) ] = entry[ 1: ]
        
    sqlTextForProcesses = {}
    for entry in processQueryResults:
        sqlTextForProcesses[ int( entry[ 0 ] ) ] = entry[ 1: ]

    return sqlTextForProcesses, backgroundProcesses


def ThreadStatistics( computer, processName ):
    ( statNames, threadlist ) = win32pdh.EnumObjectItems( None, None, 'Thread', win32pdh.PERF_DETAIL_WIZARD, 0 )

    cpuTimeCounters = []
    threadPerfHandles= {}

    query = win32pdh.OpenQuery( None, 0 )
    for thread in threadlist:
        if re.search( processName, thread ):
            counterPath = win32pdh.MakeCounterPath( ( computer, 'Thread',  thread, None, -1, PROCESSOR_TIME ), 0 )
            cpuTimeCounter = win32pdh.AddCounter( query, counterPath )
            counterPath = win32pdh.MakeCounterPath( ( computer, 'Thread',  thread, None, -1, THREAD_ID ), 0 )
            threadIdCounter = win32pdh.AddCounter( query, counterPath )
            threadPerfHandles[ thread ] = ( threadIdCounter, cpuTimeCounter )


    # Collect data for a while.
    for a in range(0,5):
        win32pdh.CollectQueryData( query )
        time.sleep(1)

    threadIDs = {}
    threadProcessorTime = {}

    perfHandleKeys = threadPerfHandles.keys()
    perfHandleKeys.sort()
    for thread in perfHandleKeys:
           ( handle, processorTime ) = win32pdh.GetFormattedCounterValue( threadPerfHandles[ thread ][ CPU_TIME_HANDLE ], win32pdh.PDH_FMT_DOUBLE  )
           ( handle, threadId ) = win32pdh.GetFormattedCounterValue( threadPerfHandles[ thread ][ THREAD_ID_HANDLE ], win32pdh.PDH_FMT_LONG )
           threadIDs[ thread ] = threadId
           threadProcessorTime[ threadId ] = processorTime

    return threadIDs, threadProcessorTime


class Db:
    def __init__( self ):
        user, password, alias = GetAccountInfo.OracleAccountInfo()
        oracleLogin = user + '/' + password + '@' + alias
        self.dbConnection = DCOracle.Connect( oracleLogin )
        self.dbCursor = self.dbConnection.cursor()


    def Query( self, query ):
        self.dbCursor.execute( query )
        return self.dbCursor.fetchall()

    def Commit( self ):
        self.dbConnection.commit()

    def Close( self ):
        self.dbConnection.close()        

if __name__ == '__main__':
    main()