;Oracle users (V$SESSION) - 01/2008

#include <GUIConstants.au3>
#include <GuiListView.au3>
#Include <date.au3>
#include <OraCon.au3>

dim $font="Courier New"
dim $font2 = "Comic Sans MS"
dim $font3 = "Arial"
dim $FieldView = ""
dim $FieldNames = ""
dim $adOpenStatic = 3
dim $adLockOptimistic = 3
dim $adLockReadOnly = 1
dim $adOpenKeySet = 1
; the following sets up the sort for the initial display.  Want to sort the 2nd field because the first 2 fields aren't displayed
dim $mySort = 4
dim $mySearch = "%"
dim $sqlText
dim $sqlID
dim $kuser
dim $userinfo
dim $userSearch
dim $adors
dim $origRunSQL
dim $tblListView
dim $dispmsg
dim $retcode
dim $DispTbl
dim $Search
dim $sqlBox
dim $j, $i, $y
; Define Arrays
dim $lineNum [3000]
dim $rowarray
dim $DispTbl, $info, $totrecs
; Declare buttons
dim $bSearch, $bRefresh, $bExit

Global $dbsource
Global $oMyError
; Initializes COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
$retcode = _OpenOra("user","pass","database")
$runSQL = "select v$session.sql_id, sid, SERIAL#, username, osuser, to_char(logon_time,'mm/dd/yyyy HH:MI PM') logon_time, status, machine, program, sql_text from v$session, v$sql where type <> 'BACKGROUND' and upper (osuser) like '"&$mySearch&"' and v$session.sql_id = v$sql.sql_id(+)"
_DisplayTable($runSQL)


Func _DisplayTable($runSQL)
	$adors = ObjCreate( "ADODB.RecordSet" )
	getDatabase()
	$info = "V$SESSION in database " & $dbsource
	$origRunSQL = $runSQL
        $DispTbl = GuiCreate("List Table Contents - " & $info, 1050, 950,-1,-1)

;Setup Search area
	GuiCtrlCreateLabel("Search Criteria", 10, 25, 100, 40)
	GUICtrlSetFont (-1,10, 400, 4, $font2)
	$Search = GuiCtrlCreateInput("", 120, 20, 130, 20)
	GuiCtrlSetData($Search,"")
	$bSearch = GUICtrlCreateButton ("Query", 300, 20, 100, 20)
;End Search area
;Setup SQL Display area
	$sqlBox = GuiCtrlCreateEdit("", 100, 675, 780, 150,$ES_MultiLine+$WS_VSCROLL)
	;$sqlBox = GUICtrlCreateList("", 100, 675, 780, 150)
;End SQL Display area
	$bRefresh    = GUICtrlCreateButton ("Refresh", 675, 20, 60, 20)
	$bDisplaySQL = GUICtrlCreateButton ("FULL SQL", 150, 875, 70, 30)
	$bKillUser   = GUICtrlCreateButton ("Kill User", 425, 875, 70, 30)
	$bExit       = GUICtrlCreateButton ("EXIT", 725, 875, 50, 30)
        GuiSetState()
       	GUICtrlSetState ($bSearch,$GUI_FOCUS)
	Send("{SPACE}")
	GUICtrlSetState ($Search,$GUI_FOCUS)
        Do
        	$dispmsg = GUIGetMsg()
        	Select
          Case $dispmsg = $bSearch
        	   	GUICtrlSetData($sqlBox,"")
        		_clearlistview($tblListView)
        		$mySearch = StringUpper(GUICtrlRead($Search))
				pullInfo($mySearch)
				GuiCtrlCreateLabel("Total Records = " & $totrecs, 425, 20, 200, 20)
				GUICtrlSetFont (-1,12,500, $font)
				GUICtrlSetState ($Search,$GUI_FOCUS)
          Case $dispmsg = $bRefresh
        	   	$y = _GUICtrlListView_GetCurSel($tblListView)
       			_clearlistview($tblListView)
				pullInfo($mySearch)
				if $y > 0 then
					GUICtrlSetState ($lineNum [$y ],$GUI_FOCUS)
				endif
                If _getRowInfo() = 0 then
					displaySQL($sqlID)
				else
					GUICtrlSetData($sqlBox,"")
				Endif
				GuiCtrlCreateLabel("Total Records = " & $totrecs, 425, 20, 200, 20)
				GUICtrlSetFont (-1,12,500, $font)
           Case $dispmsg = $bDisplaySQL
				If _getRowInfo() = 0 then
					displaySQL($sqlID)
				Endif
           Case $dispmsg = $bKillUser
        	    If _getRowInfo() = 0 then
					_killUser($kuser)
				else
					msgbox(0,"","must select user to kill")
				Endif
		   Case $dispmsg = $tblListView
				$sortlbl = GuiCtrlCreateLabel("Sorting...", 300,2,100,20)
				GUICtrlSetFont (-1,11,600, $font)
       			_clearlistview($tblListView)
				$mySort = GUICtrlGetState($tblListView) + 1
				pullInfo($mySearch)
				GUICtrlDelete($sortlbl)
				GuiCtrlCreateLabel("Total Records = " & $totrecs, 425, 20, 200, 20)
				GUICtrlSetFont (-1,12,500, $font)
				GUICtrlSetState ($Search,$GUI_FOCUS)
		   Case $dispmsg = $bexit
			exitLoop
		EndSelect
	Until $dispmsg = $GUI_EVENT_CLOSE
        GUIDelete($DispTbl)
        return 0
EndFunc

Func pullInfo($mySearch)
	dim $lineNum [3000]
	$mySearch = "%"&$mySearch&"%"
	$runSQL = "select v$session.sql_id, sid, SERIAL#, username, osuser, to_char(logon_time,'mm/dd/yyyy HH:MI PM') logon_time, status, machine, program, sql_text from v$session, v$sql where type <> 'BACKGROUND' and upper (osuser) like '"&$mySearch&"' and v$session.sql_id = v$sql.sql_id(+)"
	$runSQL = $runSQL & " order by "&$mySort
	With $adors
	        .ActiveConnection = $adoCon
	        .LockType = $adLockOptimistic ; Set ODBC connection read only
		.CursorType = $adOpenkeyset
		.Source = $runSQL
	        .Open
	        If @error then
	        	msgbox (0,"","Error running SQL in pullInfo " & $runSQL)
	        	exit
	        Endif
	EndWith
	$totrecs = $adors.recordcount
	$j = 0
	$FieldNames = ""
	$FieldView = ""
	if $adors.EOF then
		msgbox(0,"EOF","No records found")
	else
		While not $adors.EOF
		    For $i = 0 To $adors.Fields.Count - 1
	        	$FieldView = $FieldView & $adors.Fields( $i ).Value & "|"
			if $j = 0 then
				$FieldNames = $FieldNames & $adors.Fields($i).Name & "|"
			endif
		    Next
		    if GUICtrlGetState($tblListView) = -1 then
		    	$tblListView = GuiCtrlCreateListView($FieldNames, 30, 60, 950, 600,$LVS_SHOWSELALWAYS,$LVS_EX_GRIDLINES)
		        _GUICtrlListView_SetColumnWidth ( $tblListView, 0, 0 )
		    endif
		    $lineNum[$j] = GuiCtrlCreateListViewItem($FieldView, $tblListView)
		    GuiCtrlSetState(-1,$GUI_DROPACCEPTED)
		    $FieldView = ""
		    $j = $j + 1
		    $adors.MoveNext
		WEnd
		GUICtrlSendMsg($tblListView, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_FULLROWSELECT,$LVS_EX_FULLROWSELECT)
		_GUICtrlListView_SetColumnWidth($tblListView,0,$LVSCW_AUTOSIZE)
		_GUICtrlListView_HideColumn ( $tblListView, 0 )
	endif
	$adors.close
EndFunc


Func getDatabase()
	$runSQL = "select name from v$database"
	With $adors
	        .ActiveConnection = $adoCon
	        .LockType = $adLockOptimistic ; Set ODBC connection read only
		.CursorType = $adOpenkeyset
		.Source = $runSQL
	        .Open
	        If @error then
	        	exit
	        Endif
	EndWith
	$dbsource = $adors.Fields(0).Value
	$adors.close
EndFunc

Func displaySQL($sqlID)
	$runSQL = "select sql_fulltext from v$sql where sql_id = '"&$sqlID&"'"
	With $adors
	        .ActiveConnection = $adoCon
	        .LockType = $adLockOptimistic ; Set ODBC connection read only
		.CursorType = $adOpenkeyset
		.Source = $runSQL
	        .Open
	        If @error then
	        	exit
	        Endif
	EndWith
	GUICtrlSetData($sqlBox,"")
	if $adors.EOF then
	else
		$sqlText = $adors.Fields(0).Value
		;msgbox(0,"",$sqlText)
		GUICtrlSetData($sqlBox,$sqlText)
	endif
	$adors.close
EndFunc

Func _getRowInfo()
	local $rowData
	local $retcode
	$rowArray = _GUICtrlListView_GetItemTextArray ($tblListView)
            If (Not IsArray($rowArray)) Then
                ;Msgbox(0,"error","Must Select Row to display")
                $retcode = -1
            Else
                For $i = 1 To $rowArray[0]
                    $sqlID    = $rowArray[1]
                    $kuser      = $rowArray[2] & ", " & $rowArray[3]
                    $userinfo = "user - " & $rowArray[3] & " osuser - " & $rowArray[4]
                    $rowData  = $rowData & @LF & $rowArray[$i]
					Msgbox(0,"rowData ", $rowData)
                Next
	        $retCode = 0
            EndIf
        $rowData = ""
        local $rowArray[1]
        return $retcode
EndFunc

Func _killUser($kuser)
	$msgret = msgbox(1,"KILL USER!","Are you sure you want to kill user " & $userinfo)
	if $msgret = 1 then
		$runSQL = "alter system kill session '"&$kuser&"'"
		;msgbox(0,"",$runSQL)
		With $adors
		        .ActiveConnection = $adoCon
		        .LockType = $adLockOptimistic ; Set ODBC connection read only
			.CursorType = $adOpenkeyset
			.Source = $runSQL
		        .Open
		        If @error then
				msgbox("4096","ERROR","Unable to Delete User")
		        Endif
		EndWith
	elseif $msgret = 2 then
		msgbox(0,"","User not Killed")
	endif
EndFunc



Func _clearlistview($List_View)
   ;Local $LVM_DELETEALLITEMS = 0x1009
   ;GUICtrlSendMsg($List_View, $LVM_DELETEALLITEMS, 0, 0)
   ;_GUICtrlListView_DeleteAllItems($List_View)
   ;_GUICtrlListView_DeleteAllItems(ControlGetHandle ("", "", $list_view))
   For $i_index = _GUICtrlListView_GetItemCount($List_View) - 1 To 0 Step - 1
	_GUICtrlListView_SetItemSelState($List_View, $i_index, 1)
	$control_ID = GUICtrlRead($List_View)
	If $control_ID Then GUICtrlDelete($control_ID)
   Next
EndFunc

;*******************************************************
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
	            )
  SetError(1)  ; to check for after this function returns
Endfunc
;========================================================