Option Explicit

' Shell object
Dim m_wshShell
Set m_wshShell=WScript.CreateObject("Wscript.Shell")

' Some defaults
Dim m_strServername, m_strDir, m_strLoginID, m_strPassword
Dim m_strErrLogFile, m_strBadArgument

' Argument prefix length
Const ARG_PREFIX_LEN = 3

Call GetCommandLineArgs

' Name of the script and welcome message
Dim m_strSource, m_strWelcome
m_strSource = "Exploration Air Database Install v1.04.1003"

If m_strBadArgument = "True" Then
	m_strWelcome = vbTab & vbTab & "Incorrect Parameter Entered" & vbCRLF & _
					vbTab & vbTab & "Unable to continue" & vbCRLF & vbCRLF
Else
	m_strWelcome = ""
End If

m_strWelcome = 	m_strWelcome & vbTab & vbTab & "**** PLEASE READ ****" & vbCRLF & vbCRLF & _
				"This script builds up the SQL Server databases for Exploration Air." & vbCRLF & vbCRLF & _
				"Usage is: BuildExAir.cmd [-S:server] [-U:login ID] [-P:password] [-D:dir]" & vbCRLF & vbCRLF & _ 
				"Where: " & vbCRLF & _
				vbTab & "[server] name of the server where SQL Server resides (default is '" & m_strServername & "')." & vbCRLF & _
				vbTab & "[login ID] is the SQL Server admin login (default is 'sa')." & vbCRLF & _
				vbTab & "[password] is the login ID password (default is no password)." & vbCRLF & _ 
				vbTab & "[dir] is the location of SQL Server (default is 'c:\mssql')." & vbCRLF & vbCRLF & _ 
				"Note: Any existing database named ExAir or ExAirBenefits will be deleted." & vbCRLF & vbCRLF

If m_strBadArgument = "True" Then
	m_strWelcome = m_strWelcome & "To build up the SQL Server databases for Exploration Air, rerun with correct parameters."
	Msgbox m_strWelcome, vbOKOnly, m_strSource 
Else
	m_strWelcome = m_strWelcome & "Do you wish to continue?"
	If Msgbox(m_strWelcome,vbYesNo + vbQuestion, m_strSource) = vbNo Then
		Err.Raise 1,m_strSource,"Execution halted by the user."
	End If
	Call BuildDatabases
	Call BuildDSN

	MsgBox "Database Setup is completed!",vbInformation+vbOK,m_strSource
End If

'''''''''''''''''''''''''''''''''''''''''''''''''
' GetCommandLineArges parses the command line
Sub GetCommandLineArgs
	' Set defaults
	m_strDir="c:\mssql"
	m_strLoginID="sa"
	m_strPassword=""
	m_strErrLogFile = "ExAirLog."
	m_strBadArgument = "False"

	Dim wshNet
	set wshNet = WScript.CreateObject("Wscript.Network")
	m_strServername = wshNet.ComputerName

	' Get argument object
	Dim wshArg, str
	'Set wshArg=Wscript.Arguments
	For each str in Wscript.Arguments
		Select Case UCase(Left(str,ARG_PREFIX_LEN))
			Case "-S:"
				m_strServerName = PruneOffPrefix(str)

			Case "-P:"
				m_strPassword = PruneOffPrefix(str)

			Case "-D:"
				m_strDir = PruneOffPrefix(str)

			Case "-U:"
				m_strLoginID = PruneOffPrefix(str)

			Case Else
				m_strBadArgument = "True"
				Exit Sub
		End Select
	Next

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''
' PruneOffPrefix removes the -X: from a 
' command-line option
Function PruneOffPrefix(str)
	If Len(str) > ARG_PREFIX_LEN Then 
		PruneOffPrefix = Mid(str, ARG_PREFIX_LEN+1)
	Else
		PruneOffPrefix = ""
	End If
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''
' ExecuteBCP executes a .BCP file 
' Throws an exception on failure
Sub ExecuteBCP(strDatabase,strTable, strSwitch)
	Dim strComment
	strComment = "   Populating " & strTable
	WScript.echo strComment

	dim strCommand
	strCommand = "bcp " & strDatabase & ".." & strTable	& _ 
				 " in " & strTable & ".bcp" & _
				 " -S" & m_strServerName & _
				 " -U" & m_strLoginID & _
				 " -P" & m_strPassword & _ 
				 " -o " & m_strErrLogFile & _
				 " -n"
	If strSwitch <> "" Then
		strCommand = strCommand & strSwitch
	End If

	Dim ret
	ret=m_wshShell.Run(strCommand,2,True)
	If ret <> 0 Then
		Err.Raise 1,m_strSource,"BCP failed, please look at the " & m_strErrLogFile & " file for any errors."
	End If
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''
' ExecuteSQLScript executes a .SQL file 
' Throws an exception on failure
Sub ExecuteSQLScript(strWhich, strComment, strSQLScript, strOption)
	
	WScript.echo vbCRLF & strComment

	Dim strCommand
	strCommand = "isql -U" & m_strLoginID & _ 
				 " -P" & m_strPassword & _
				 " -S" & m_strServerName & _
				 " -i " & strSQLScript & _
				 " -o " & m_strErrLogFile & strWhich & " " & strOption

	Dim ret
	ret=m_wshShell.Run(strCommand,2,True)
	If ret <> 0 Then
		Err.Raise 1,m_strSource,"ISQL failed, please look at the " & m_strErrLogFile & strWhich & " file for any errors."
	End If
	 
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''
' ExecuteISQL executes the SQL statement in strSQL 
' Throws an exception on failure
Sub ExecuteISQL(strWhich, strComment, strSQL, strOption)
	
	WScript.echo vbCRLF & strComment

	Dim strCommand
	strCommand = "isql -U" & m_strLoginID & _ 
				 " -P" & m_strPassword & _
				 " -S" & m_strServerName & _
				 " /Q " & chr(34) & strSQL & chr(34) & _
				 " -o " & m_strErrLogFile & strWhich & " " & strOption

	Dim ret
	ret=m_wshShell.Run(strCommand,2,True)
	If ret <> 0 Then
		Err.Raise 1,m_strSource,"ISQL failed, please look at the " & m_strErrLogFile & strWhich & " file for any errors."
	End If
	 
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''
' BuildDatabases builds and populates all the data
Sub BuildDatabases
	' Nuke old stuff
	ExecuteSQLScript "1", "Clearing old data", "NukeDev.sql", ""						 

	' Build main ExAir database
	ExecuteISQL "2", "Creating ExAir database device", "disk init name='ExAirDev', physname='" & m_strDir & "\Data\ExAirDev.Dat', vdevno=42, size=2048", "-b"
	ExecuteSQLScript "2", "Building ExAir database (this can be time-consuming!)", "ExAir.sql", "-b"
	ExecuteBCP "ExAir", "Ads", ""
	ExecuteBCP "ExAir", "AdsInterests", ""
	ExecuteBCP "ExAir", "Company", ""
	ExecuteBCP "ExAir", "Destination", ""
	ExecuteBCP "ExAir", "FlightSchedule", ""
	ExecuteBCP "ExAir", "InterestCategories", ""
	ExecuteBCP "ExAir", "Interests", ""
	ExecuteBCP "ExAir", "Member", ""
	ExecuteBCP "ExAir", "Membership", ""
	ExecuteBCP "ExAir", "MembershipType", ""
	ExecuteBCP "ExAir", "MembersInterests", ""
	ExecuteBCP "ExAir", "Promotions", ""
	ExecuteBCP "ExAir", "PromotionsInterests", ""
	ExecuteBCP "ExAir", "Special", ""
	ExecuteBCP "ExAir", "TakeANumber", ""
	ExecuteBCP "ExAir", "Transactions", ""
	ExecuteBCP "ExAir", "TransactionType", ""

	' Build Benefits database
	ExecuteISQL "3", "Creating Benefits database device", "disk init name='ExAirBenefitsDev', physname='" & m_strDir & "\Data\ExAirBenefitsDev.Dat', vdevno=43, size=2048", "-b"
	ExecuteSQLScript "3", "Building Benefits database (this can be time-consuming!)", "ExAirBenefits.sql", "-b"
	ExecuteBCP "ExAirBenefits", "BenefitStatus", " -E"
	ExecuteBCP "ExAirBenefits", "EBDStatus", " -E"
	ExecuteBCP "ExAirBenefits", "EDStatus", " -E"
	ExecuteBCP "ExAirBenefits", "EmployeeStatus", " -E"
	ExecuteBCP "ExAirBenefits", "PlanStatus", " -E"
	ExecuteBCP "ExAirBenefits", "QualifierStatus", " -E"
	ExecuteBCP "ExAirBenefits", "Benefit", " -E"
	ExecuteBCP "ExAirBenefits", "DependentType", " -E"
	ExecuteBCP "ExAirBenefits", "Field", " -E"
	ExecuteBCP "ExAirBenefits", "Gender", " -E"
	ExecuteBCP "ExAirBenefits", "GeoArea", " -E"
	ExecuteBCP "ExAirBenefits", "Physician", " -E"
	ExecuteBCP "ExAirBenefits", "Plans", " -E"
	ExecuteBCP "ExAirBenefits", "QualifierClass", " -E"
	ExecuteBCP "ExAirBenefits", "TaxStatus", " -E"
	ExecuteBCP "ExAirBenefits", "BenefitPlan", ""
	ExecuteBCP "ExAirBenefits", "BenefitTaxStatus", ""
	ExecuteBCP "ExAirBenefits", "Dependent", " -E"
	ExecuteBCP "ExAirBenefits", "Employee", " -E"
	ExecuteBCP "ExAirBenefits", "PlanField", ""
	ExecuteBCP "ExAirBenefits", "PlanGeoArea", ""
	ExecuteBCP "ExAirBenefits", "Qualifier", " -E"
	ExecuteBCP "ExAirBenefits", "BenefitQualifier", ""
	ExecuteBCP "ExAirBenefits", "EmployeeBenefit", ""
	ExecuteBCP "ExAirBenefits", "EmployeeDependent", ""
	ExecuteBCP "ExAirBenefits", "EmployeeQualifier", " -E"
	ExecuteBCP "ExAirBenefits", "EmployeeBenefitDependent", ""
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''
' BuildDSN builds the two File DSN's
Sub BuildDSN
	WScript.echo vbCRLF & "Building ODBC data sources (DSNs)"
	WriteOneDSN "SQLFreq.dsn", "ExAir"
	WriteOneDSN "SQLBenefits.dsn", "ExAirBenefits"
End Sub

Sub WriteOneDSN(strDSNName, strDatabase)

	Dim obFileSys, obDSN, WSHShell, DefaultODBCDir

	Set WSHShell = WScript.CreateObject("WScript.Shell")
    DefaultODBCDir = WSHShell.RegRead("HKLM\Software\ODBC\ODBC.INI\ODBC File DSN\DefaultDSNDir")

	Set obFileSys = WScript.CreateObject("Scripting.FileSystemObject")
	Set obDSN = obFileSys.CreateTextFile(DefaultODBCDir & "\" & strDSNName)

	obDSN.WriteLine("[ODBC]")
	obDSN.WriteLine("DRIVER=SQL Server")
	obDSN.WriteLine("UID=" & m_strLoginID)
	obDSN.WriteLine("PWD=" & m_strPassword)
	obDSN.WriteLine("DATABASE=" & strDatabase)
	obDSN.WriteLine("WSID=" & m_strServername)
	obDSN.WriteLine("APP=Microsoft Win32")
	obDSN.WriteLine("SERVER=" & m_strServername)
	obDSN.Close

End Sub