# encoding: ascii
# api: powershell
# title: Excel Auto-frontend
# description: Automatically create an Excel frontend to a cmdlet or advanced function. Not really general purpose yet. There are a couple of examples within the script.
# version: 0.1
# type: function
# license: CC0
# function: New-DummyVM
# x-poshcode-id: 1253
# x-archived: 2009-09-12T11:42:10
#
#
Function New-DummyVM {
	param(
	[Parameter(Mandatory=$true,HelpMessage="Target Host")]
	[VMware.VimAutomation.Types.VMHost]
	$TargetHost,

	[Parameter(Mandatory=$true,HelpMessage="Target Host")]
	[VMware.VimAutomation.Types.ResourcePool]
	$ResourcePool,

	[Parameter(Mandatory=$true,HelpMessage="New VM Name")]
	[String]
	$NewName
	)
	
	Get-VMHost $TargetHost | New-VM -ResourcePool $ResourcePool -Name $NewName -DiskMB 1
}

Function Add-BulkHosts {
	param(
	[Parameter(Mandatory=$true,HelpMessage="Host name or IP address")]
	[String]
	$HostName,

	[Parameter(HelpMessage="Target Datacenter")]
	[VMware.VimAutomation.Types.Datacenter]
	$Datacenter,

	[Parameter(HelpMessage="Target Folder")]
	[VMware.VimAutomation.Types.Folder]
	$Folder,

	[Parameter(HelpMessage="Target Cluster")]
	[VMware.VimAutomation.Types.Cluster]
	$Cluster,

	[Parameter(HelpMessage="Target Resource Pool")]
	[VMware.VimAutomation.Types.ResourcePool]
	$ResourcePool,

	[Parameter(HelpMessage="Target Port")]
	[Int32]
	$Port=443,

	[Parameter(HelpMessage="User")]
	[String]
	$User = "",

	[Parameter(HelpMessage="Password")]
	[String]
	$Password
	)

	Process {
		if ($Datacenter) {
			$location = $Datacenter
		} elseif ($Folder) {
			$location = $Folder
		} elseif ($Cluster) {
			$location = $Cluster
		} elseif ($ResourcePool) {
			$location = $ResourcePool
		}
		if (!$location) {
			Write-Warning "One of Datacenter, Folder, Cluster or ResourcePool must be specified."
			return
		}

		if ($User -ne "") {
			Add-VMHost -Name $HostName -Location $location -Port $Port -User $User -Password $Password -Force
		} else {
			Add-VMHost -Name $HostName -Location $location -Port $Port -Force
		}
	}
}

Function Get-ExcelFrontEnd {
	param($cmdlet)

	# Load in some Excel interop stuff.
	[reflection.assembly]::loadwithpartialname("microsoft.office.interop.excel") | Out-Null
	$xlDvType                  = "Microsoft.Office.Interop.Excel.XlDVType" -as [type]
	$xlDvAlertStyle            = "Microsoft.Office.Interop.Excel.XlDvAlertStyle" -as [type]
	$xlFormatConditionOperator = "Microsoft.Office.Interop.Excel.XlFormatConditionOperator" -as [type]
	$myDocuments = (Get-Itemproperty "hkcu:\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders").Personal
	$csvFile = $myDocuments + "\parameters.csv"

	# We watch this file for writes.
	$startWriteTime = (Get-ChildItem $csvFile -ea SilentlyContinue).LastWriteTime

	# Define what commands will get us various types of data.
	$findObjects = @{
		[VMware.VimAutomation.Types.Cluster]             = { Get-Cluster }             ;
		[VMware.VimAutomation.Types.Datacenter]          = { Get-Datacenter }          ;
		[Datastore]                                      = { Get-Datastore }           ;
		[VMware.VimAutomation.Types.Folder]              = { Get-Folder }              ;
		[VMware.VimAutomation.Types.OSCustomizationSpec] = { Get-OSCustomizationSpec } ;
		[VMware.VimAutomation.Types.ResourcePool]        = { Get-ResourcePool }        ;
		[VMware.VimAutomation.Types.Template]            = { Get-Template }            ;
		[VMware.VimAutomation.Types.VirtualMachine]      = { Get-VM }                  ;
		[VMware.VimAutomation.Types.VMHost]              = { Get-VMHost }
	}
	
	# Parameters to ignore.
	$ignore = @("Verbose", "Debug", "ErrorAction", "WarningAction", "ErrorVariable",
	    "WarningVariable", "OutVariable", "OutBuffer")

	# Determine the names and types of all parameters in the first signature.
	$parameters = (Get-Command $cmdlet).Parameters

	# Locate the cmdlet template.
	$cmdletTemplate = $myDocuments + "\CmdletFrontend.xlsm"
	$ifOffset = 11

	# Launch Excel and initialize things.
	$xl = New-Object -ComObject Excel.Application
	$wb = $xl.Workbooks.Open($cmdletTemplate)
	#$wb = $xl.Workbooks.Add()
	$ifSheet = $wb.worksheets.item(1)
	#$ifSheet.Name = "Interface"
	$ifSheet.StandardWidth = 20
	$dataSheet = $wb.worksheets.item(2)
	#$dataSheet.Name = "Data"
	#$wb.worksheets.item(3).Delete()

	# Populate the easy stuff.
	$ifSheet.Cells.Item(4, 1) = $cmdlet
	#$ifSheet.Cells.Item(7, 5) = $defaultVIServer.Name
	#$ifSheet.Cells.Item(8, 5) = $defaultVIServer.SessionId

	$xl.visible = $true

	# Based on the types, start populating stuff under the Data sheet.
	$i = 0
	foreach ($key in $parameters.keys) {
		if ($ignore -contains $key) {
			continue
		}
		$i++

		$parameter = $parameters[$key]
		$type = $parameter.ParameterType

		# If the type is an array, figure out the real type.
		# XXX

		# Create the column in the Interface sheet.
		$ifSheet.Cells.Item($ifOffset, $i) = $key

		# If we know how to load instances of this type, load the data in now.
		if ($findObjects[$type]) {
			$values = . $findObjects[$type]

			# Put this data in the Data sheet.
			$j = 1
			foreach ($value in $values) {
				$dataSheet.Cells.Item($j++, $i) = $value.Name
			}

			# Turn this into a named data range.
			$rangeName = $key
			Write-Debug $rangeName

			# Convert the range to R1C1 notation.
			$dataRange = "=Data!R1C{0}:R{1}C{2}" -f $i, ($j-1), $i
			Write-Debug $dataRange
			$name = $wb.names.add($rangename, $null, $true, $null, $null, $null, $null, $null, $null, $dataRange, $null)

			$column = [char]([byte][char]"A" + $i - 1)
			$ifRange = "{0}{1}:{2}{3}" -f $column, $ifOffset, $column, 250
			Write-Debug $ifRange
			# Constrain input in the interface sheet based on this data.
			$range = $ifSheet.Range($ifRange).Select()
			$validation = $xl.Selection.Validation.Add($xlDvType::xlValidateList, $xlDvAlertStyle::xlValidAlertStop,
				$xlFormatConditionOperator::xlBetween, "=$rangename", $null)
		}
	}
	
	$range = $ifSheet.Range("A1").Select()

	# Wait for the file to get updated.
	do {
		Write-Debug "Waiting"
		Start-Sleep 1
		$thisWriteTime = (Get-ChildItem $csvFile -ea silentlycontinue).LastWriteTime
	} while ($thisWriteTime -le $startWriteTime)

	# Run the cmdlet with the data specified.
	$xl.Visible = $false
	$wb.Close($false)
	$xl.Quit()
	foreach ($f in (import-csv $csvFile)) {
		$properties = $f | Get-Member -type NoteProperty
 		$argString = ""
		$objects = @()
		$i = 0
		foreach ($property in $properties) {
			$pName = $property.Name
			#Write-Host ("Property name is " + $pName)
			if ($f.$pName -ne "") {
				#Write-Host ("Property value is " + $f.$pName)

				# Resolve the object.
				$parameter = $parameters[$pName]
				$type = $parameter.ParameterType
				$getter = $findObjects[$type]
				$object = $null
				if ($getter) {
					$lookupExpression = ($getter.ToString() + " -name '" + $f.$pName + "' | Select -first 1")
					#Write-Host ("Looking up using " + $lookupExpression)
					$object = Invoke-Expression $lookupExpression
				} else {
					$object = $f.$pName
				}
				$objects += $object
				#Write-Host $objects
				$argString += (" -" + $pName + " `$objects[$i]")
				#Write-Host ("Set " + $pName + " to " + $objects[$i])
				$i++
			}
		}
		$command = $cmdlet + $argString
		#Write-Host $command
		Invoke-Expression $command
	}
}

#Get-ExcelFrontEnd Add-BulkHosts
#Get-ExcelFrontEnd New-DummyVM
