Export_groups_and_perms.vbs #3

Option explicit

' Using the TeamTrack admin console, export groups and group permissions to XML
'  The reason for this script is that the XML export requires the operator/user
'  to select each and every group to be exported.  When you've got several hundred
'  groups, it's a real pain to have to select all of 'em.

'	If you don't specify the number of groups to export on the command line, 
'  you will need to specify an ODBC connection name, userid, and password for
'	a known-good ODBC System DSN to the TeamTrack database.  The database will
'	be queried for the number of groups.

' Usage:
' CSCRIPT Export_groups_and_perms "ODBC System DSN Name"  "DSN User ID"  "DSN User Pwd"
' or
' CSCRIPT Export_groups_and_perms  123

' First syntax queries Teamtrack to get number of groups.  The specified
'		System DSN name, user and password must be valid.
' Second syntax uses a user-supplied number of groups.
' If the user-supplied value is too high, the last group in the list
' will be repeatedly enabled and disabled.  No harm is done, but it might
' end up not being selected.

' ** *********************************************************************
' ** -- $Id: //guest/paul_m_thompson/TeamTrack/Export_groups_and_perms.vbs#3 $
' ** -- $Change: 7352 $
' ** -- $DateTime: 2009/08/17 12:56:11 $
' ** -- $Author: paul_m_thompson $
' ** *********************************************************************

' Sometimes have to fiddle with these to get the timing right.
' they're very machine-dependent
Const SHORT_SLEEP = 15
Const MEDIUM_SLEEP = 600
Const LONG_SLEEP = 4000

Const vbAdStateOpen=1
Const vbAdOpenForwardOnly=0
Const vbAdOpenStatic=3
Const vbAdLockReadOnly=1
Const vbAdCmdText=1
Const vbAdExecuteRecord=512


' We're expecting to be at the main TT admin screen.
Const TT_ADMIN_CONSOLE_NAME = "TeamTrack Administrator"

Dim strSystemDsn , strUserId , strUserPwd


' For command line params
dim objArgs , nGroups
set objArgs = WScript.Arguments
wscript.echo "argcount=" + cstr(objArgs.count)

' If a numeric param is specified, use it as the rep count.
Select Case objArgs.Count
	Case 1
		If IsNumeric(objArgs(0)) Then nGroups = CInt(objArgs(0))
	Case 3
		strSystemDsn = CStr(objArgs(0))
		strUserId = CStr(objArgs(1))
		strUserPwd = CStr(objArgs(2))
		Wscript.Echo "Extract group count from ODBC connection '" + strSystemDsn + "'"
		nGroups = CountTeamtrackGroups(strSystemDsn,strUserId,strUserPwd)
	Case Else
		Wscript.Echo "Sorry bucko, but I need either the number of groups to export,"
		Wscript.Echo "or an ODBC System DSN Name, User ID, and password."
End Select

Wscript.Echo "Number of groups=" & cstr(nGroups)

Dim wsh
Set wsh = WScript.CreateObject("WScript.Shell")
WScript.Sleep LONG_SLEEP

If wsh.AppActivate (TT_ADMIN_CONSOLE_NAME) then
	Wscript.Echo "Successfully activated '" + TT_ADMIN_CONSOLE_NAME + "'"
	WScript.Sleep LONG_SLEEP

	' SHIFT == +
	' CTRL  == ^
	' ALT   == %
	' +(ab) == SHIFT-A then SHIFT-B
	' {z 10} == Send 'z' ten times.  Doesn't work for SHIFT/CTRL/ALT prefixed keys.

	' Tools menu
	wsh.SendKeys "%T"
	WScript.Sleep MEDIUM_SLEEP

	' From Tools menu, Export (DO NOT USE ALT!)
	wsh.SendKeys "E"
	WScript.Sleep MEDIUM_SLEEP

	' Next
	wsh.SendKeys "%N"
	WScript.Sleep MEDIUM_SLEEP

	' TAB to move focus to Radio Button list
	wsh.SendKeys "{TAB}"
	WScript.Sleep MEDIUM_SLEEP

	' Down 3
	wsh.SendKeys "{DOWN}{DOWN}{DOWN}"
	WScript.Sleep MEDIUM_SLEEP

	' Next
	wsh.SendKeys "%N"
	WScript.Sleep MEDIUM_SLEEP

	' Teamtrack pops up a dialog with an OK.  We have to respond to that before continuing
	' Enter
	wsh.SendKeys "{ENTER}"
	WScript.Sleep MEDIUM_SLEEP

	' We should now be at the "Export Groups" : "Select one or more groups to export" dialog
	' Focus should be in the checkbox list containing the groups.

	' Make sure the first item is highlighted
	wsh.SendKeys "{DOWN}"
	WScript.Sleep MEDIUM_SLEEP
	wsh.SendKeys "{UP}"
	WScript.Sleep MEDIUM_SLEEP

	' For each group in the list, press SPACE to select that group,
	' then down to go to next group.
	Dim i
	For i=1 to nGroups
		wsh.SendKeys " {DOWN}"
		WScript.Sleep SHORT_SLEEP
	Next
	WScript.Sleep MEDIUM_SLEEP

	' Off to the filename dialog screen.  User takes over from here.
	wsh.SendKeys "{ENTER}"

Else
	Wscript.Echo "Can't activate '" + TT_ADMIN_CONSOLE_NAME + "'"
End If


' -------------------------------------------------------------------------
' Count and return number of teamtrack groups marked as ACTIVE
Function CountTeamtrackGroups(strDsn, strUid, strPwd)

	Dim objConnection
   Set objConnection = CreateObject("ADODB.Connection")

	' DSN=myDsn;Uid=myUsername;Pwd=;
	dim strConn
	strConn = "DSN=" + strDsn
	If len(strUid) > 0 then strConn = strConn + ";Uid=" + strUid
	If len(strPwd) > 0 then strConn = strConn + ";Pwd=" + strPwd
	'	Wscript.Echo "debug -- connection string '" + strConn + "'"


   objConnection.ConnectionString = strConn
   If Not objConnection.Open Then

		If objConnection.State = vbAdStateOpen Then
			Dim objRecSet
			Set objRecSet=CreateObject("ADODB.RecordSet")
			' objRecordset.Open source,actconn,cursortyp,locktyp,opt

			Dim strSql
			' The XML export dialog includes inactive groups, so ignore TS_STATUS
			strSql = "select count(*) as active_group_count from ts_groups"
			' need to use OpenStatic mode.
			If Not objRecSet.Open(strSql, objConnection, vbAdOpenStatic, vbAdLockReadOnly) Then

				' Field name is from the query clause
				CountTeamtrackGroups=objRecSet.fields("active_group_count").value

				If objRecSet.close Then Wscript.Echo "Failed to close recordset"
			End if

			If objConnection.close Then Wscript.Echo "Failed to close connection"

		End If
	End if

End Function