Rob van der Woude's Scripting Pages
Powered by GeSHi

Source code for airregphcmd.ps

(view source code of airregphcmd.ps as plain text)

  1. <#
  2. .SYNOPSIS
  3. Search a downloaded Dutch aircraft registration database for a Dutch aircraft registation and if found, return the aircraft manufacturer and model (tab-delimited)
  4.  
  5. .DESCRIPTION
  6. First, create a subdirectory 'PH' in this script's parent folder.
  7. Next, download the Dutch aircraft registration database (see links section), unzip it and move the Excel file (and optionally the other files as well) to the 'PH' folder.
  8. Now run this script with an aircraft registration as its only parameter (see examples section).
  9. The script will first look for the most recent Excel file in the 'PH' folder.
  10. When found, it will open the first sheet of the Excel file, and search for the specified aircraft registration in the first column of the Excel sheet.
  11. If a match is found, the script will display a tab-delimited string with the registration, the manufacturer and the aircraft model (<registration><tab><manufacturer><tab><model>).
  12. If the script was started by another PowerShell script, the calling PowerShell script may also read the manufacturer and model from the variables $Manufacturer and $Model, passed on by this script.
  13. If the script was started by a batch file, the calling batch file can use 'FOR /F' on this PowerShell script's screen output to find the manufacturer and model.
  14. Get-Help './AirRegPHCmd.ps1' -Examples will show 2 examples of this script being called by another script.
  15.  
  16. .PARAMETER Registration
  17. A valid Dutch aircraft registration, i.e. PH-nn .. PH-nnnn or PH-xxx (where n is a single numeric digit, and x is a single alphanumeric character/digit)
  18.  
  19. .PARAMETER TerminateExcel
  20. Terminate Excel when the script is finished, if and only if Excel was not running when the script was started and the number of Excel processes is exactly one when the script is finished.
  21.  
  22. .PARAMETER Quiet
  23. Ignore all errors and do not display any error messages; in case of errors, just terminate with return code 1.
  24.  
  25. .PARAMETER Debug
  26. Show some progress messages
  27.  
  28. .PARAMETER Help
  29. Show the script's help screen
  30.  
  31. .OUTPUTS
  32. A tab-delimited string <registration><tab><manufacturer><tab><model> and manufacturer and model are also stored in output variables $Manufacturer and $Model.
  33.  
  34. .EXAMPLE
  35. . ./AirRegPHCmd.ps1 "PH-PBB"
  36. Will return tab-delimited string "PH-PBB<tab>Consolidated-Vultee Aircraft Corp., Stinson Division<tab>L-5B", and set variables $Manufacturer to "Consolidated-Vultee Aircraft Corp., Stinson Division" and $Model to "L-5B"
  37.  
  38. .EXAMPLE
  39. "PH-PBB" | . ./AirRegPHCmd.ps1
  40. Will also return tab-delimited string "PH-PBB<tab>Consolidated-Vultee Aircraft Corp., Stinson Division<tab>L-5B", and set variables $Manufacturer to "Consolidated-Vultee Aircraft Corp., Stinson Division" and $Model to "L-5B"
  41.  
  42. .EXAMPLE
  43. Create and run the following PowerShell script:
  44. ===============================================================
  45. $Registration = 'PH-1AE' ; $Manufacturer = '' ; $Model = ''
  46. [void] ( . "$PSScriptRoot\AirRegPHCmd.ps1" -Registration $Registration )
  47. Write-Host ( "Registration : {0}`nManufacturer : {1}`nModel        : {2}" -f $Registration, $Manufacturer, $Model )
  48. ===============================================================
  49.  
  50. Besides setting variables $Manufacturer to "HiSystems GmbH" and $Model to "MK OktoKopter XL2", it will return:
  51.  
  52. Registration : PH-1AE
  53. Manufacturer : HiSystems GmbH
  54. Model        : MK OktoKopter XL2
  55.  
  56. .EXAMPLE
  57. Create and run the following batch file:
  58. ===============================================================
  59. REM Note that there should only be a TAB and nothing else between delims= and the doublequote
  60. FOR /F "tokens=1-3 delims=	" %%A IN ('powershell . ./AirRegPHCmd.ps1 PH-PBA') DO (
  61. 	ECHO Registration : %%A
  62. 	ECHO Manufacturer : %%B
  63. 	ECHO Model        : %%C
  64. )
  65. ===============================================================
  66.  
  67. It will return:
  68.  
  69. Registration : PH-PBA
  70. Manufacturer : Douglas Aircraft Company
  71. Model        : DC-3C-S1C3G               
  72.  
  73. .LINK
  74. Script written by Rob van der Woude:
  75. https://www.robvanderwoude.com/
  76.  
  77. .LINK
  78. Downloadable Dutch aircraft registration database in Excel format:
  79. https://www.ilent.nl/onderwerpen/luchtvaartuigregister/documenten/publicaties/2019/05/27/luchtvaartuigregister-aircraft-registration
  80.  
  81. .LINK
  82. Article "PowerShell - Read an Excel file using COM Interface" by François-Xavier Cat:
  83. https://lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html
  84. #>
  85.  
  86. param (
  87. 	[parameter( ValueFromPipeline )]
  88. 	[ValidatePattern("(^\s*$|[\?/]|^PH-[0-9A-Z]{3}$|^PH-[0-9]{2,4}$)")]
  89. 	[string]$Registration,
  90. 	[switch]$TerminateExcel,
  91. 	[switch]$Quiet,
  92. 	[switch]$Help
  93. )
  94.  
  95. $progver = "1.02"
  96.  
  97. $Registration = $Registration.ToUpper( )
  98. $Manufacturer = ''
  99. $Model = ''
  100. $ExcelError = $false
  101. [bool]$Debug = ( $PSBoundParameters.ContainsKey( 'Debug' ) )
  102.  
  103. function ShowHelp( $errormessage = '' ) {
  104. 	if ( !$Quiet ) {
  105. 		if ( $errormessage ) {
  106. 			Write-Host
  107. 			Write-Host "Error: " -ForegroundColor Red -NoNewline
  108. 			Write-Host $errormessage
  109. 		}
  110. 		Write-Host
  111. 		Write-Host ( "AirRegPHCmd.ps1,  Version {0}" -f $progver )
  112. 		Write-Host "Search downloaded Dutch aircraft registration database for a registation"
  113. 		Write-Host
  114. 		Write-Host "Usage:  " -NoNewline
  115. 		Write-Host ". ./AirRegPHCmd.ps1  [ -Registration ]  PH-***  [ -TerminateExcel ]  [ -Help ]" -ForegroundColor White
  116. 		Write-Host
  117. 		Write-Host "Where:  " -NoNewline
  118. 		Write-Host "PH-***           " -NoNewline -ForegroundColor White
  119. 		Write-Host "is a valid Dutch aircraft registration, e.g. PH-PBA"
  120. 		Write-Host "        -TerminateExcel  " -NoNewline -ForegroundColor White
  121. 		Write-Host "terminates Excel when the script is finished, " -NoNewline
  122. 		Write-Host "if " -NoNewline -ForegroundColor White
  123. 		Write-Host "and " -NoNewline
  124. 		Write-Host "only" -ForegroundColor White
  125. 		Write-Host "                         if " -NoNewline -ForegroundColor White
  126. 		Write-Host "Excel was not running when the script was started"
  127. 		Write-Host "        -Quiet           " -NoNewline -ForegroundColor White
  128. 		Write-Host "all errors are ignored and no error messages displayed"
  129. 		Write-Host "        -Help            " -NoNewline -ForegroundColor White
  130. 		Write-Host "shows this help screen"
  131. 		Write-Host
  132. 		Write-Host "Notes:  This script requires a downloaded Dutch aircraft registration database,"
  133. 		Write-Host "        located in a subfolder 'PH' of this script's parent folder."
  134. 		Write-Host "        The Dutch aircraft registration database can be downloaded at:"
  135. 		Write-Host "        https://www.ilent.nl/onderwerpen/luchtvaartuigregister/documenten" -ForegroundColor DarkGray
  136. 		Write-Host "        /publicaties/2019/05/27/luchtvaartuigregister-aircraft-registration" -ForegroundColor DarkGray
  137. 		Write-Host "        This script also requires Excel, since the downloaded database is in"
  138. 		Write-Host "        Excel format."
  139. 		Write-Host "        The result, if any, of the search is displayed as tab-delimited text:"
  140. 		Write-Host "        <registration><tab><manufacturer><tab><model>"
  141. 		Write-Host "        Besides its screen output, this script will also set the `$Manufacturer"
  142. 		Write-Host "        and `$Model variables with the database search result."
  143. 		Write-Host "        Run " -NoNewline
  144. 		Write-Host "Get-Help `"./AirRegPHCmd.ps1`" -Examples " -NoNewline -ForegroundColor White
  145. 		Write-Host "for some examples of"
  146. 		Write-Host "        `"nesting`" this script in other PowerShell or batch scripts."
  147. 		Write-Host "        Return code (`"ErrorLevel`") 1 in case of errors, otherwise 0."
  148. 		Write-Host
  149. 		Write-Host "Written by Rob van der Woude"
  150. 		Write-Host "https://www.robvanderwoude.com"
  151. 	}
  152. 	Exit 1
  153. }
  154.  
  155. if ( $Help -or $Registration -match "(^\s*$|^-|\?|/)" ) {
  156. 	ShowHelp
  157. 	Exit 1
  158. }
  159.  
  160. # Check if Excel is already running
  161. $excelactive = [bool]( ( Get-Process -Name "Excel" -ErrorAction 'SilentlyContinue' | Measure-Object ).Count -gt 0 )
  162. if ( $Debug ) {
  163. 	if ( $excelactive ) {
  164. 		Write-Host "Excel already active at start"
  165. 	} else {
  166. 		Write-Host "Excel not active at start"
  167. 	}
  168. }
  169.  
  170. $dbfolder = ( Join-Path -Path $PSScriptRoot -ChildPath 'PH' )
  171. if ( Test-Path $dbfolder ) {
  172. 	$excelfile = ( Get-ChildItem -Path $dbfolder -Filter '* Aircraft registrations.xlsx' | Sort-Object $_.Name | Select-Object -Last 1 )
  173. 	if ( $excelfile ) {
  174. 		if ( $Debug ) {
  175. 			Write-Host ( "Using Excel file `"{0}`"" -f $excelfile.FullName )
  176. 			[System.Diagnostics.Stopwatch]::StartNew( )
  177. 			Write-Host ( "Start searching for {0} in Excel file at {1}" -f $Registration, ( Get-Date ) )
  178. 		}
  179. 		$found = $false # will be set to True when a matching aircraft is found in the database
  180. 		$ErrorActionPreference = 'SilentlyContinue' # Temporarily hide all error messages, we will handle the next one ourselves
  181. 		# Open an Excel COM object
  182. 		$objExcel = New-Object -ComObject Excel.Application -ErrorAction 'SilentlyContinue' -ErrorVariable ExcelError
  183. 		$ErrorActionPreference = 'Continue' # Reenable output of future error messages
  184. 		if ( $ExcelError ) {
  185. 			ShowHelp( "Microsoft Excel not found" ) # Actually we were unable to open Excel's COM object
  186. 		} else {
  187. 			$ErrorActionPreference = 'SilentlyContinue' # Temporarily hide all error messages, we will handle the next one ourselves
  188. 			# Open the Excel aircraft database file
  189. 			$workbook = $objExcel.Workbooks.Open( $excelfile.FullName )
  190. 			$ErrorActionPreference = 'Continue' # Reenable output of future error messages
  191. 			# Check if the Excel file was successfully opened
  192. 			if ( ![bool]( $objExcel.WorkBooks | Select-Object -Property Name ) ) {
  193. 				$ErrorActionPreference = 'SilentlyContinue' # Ignore possible error when trying to close the Excel file and program
  194. 				[void] $workbook.Close( )
  195. 				[void] $objExcel.Quit( )
  196. 				$ErrorActionPreference = 'Continue' # Reenable output of future error messages
  197. 				ShowHelp( "Unable to open Excel file `"{0}`"" -f $excelfile.FullName )
  198. 			}
  199. 			# Iterate through all rows in the first sheet of the Excel file
  200. 			$workbook.Sheets.Item( 1 ).UsedRange.Rows | ForEach-Object {
  201. 				# After the first match we don't need to check for other matches, hence the check of the $found variable
  202. 				if ( !$found ) {
  203. 					if ( $_.Columns.Item( 1 ).Text -eq $Registration ) {
  204. 						# We have a match!
  205. 						if ( $Debug ) {
  206. 							Write-Host ( "Found a match at {0}" -f ( Get-Date ) )
  207. 						}
  208. 						$found = $true # used instead of Break, which would also stop parent process; only slightly slower than Break
  209. 						$Manufacturer = $_.Columns.Item( 6 ).Text # Manufacturer is in column 6
  210. 						$Model = $_.Columns.Item( 8 ).Text # Model is in column 8
  211. 						# By not using Write-Host, we allow calling scripts to suppress screen output and use passed on global variables
  212. 						( "{0}`t{1}`t{2}" -f $_.Columns.Item( 1 ).Text, $Manufacturer, $Model ) | Out-String
  213. 					}
  214. 				}
  215. 			}
  216. 			[void] $workbook.Close( )
  217. 			[void] $objExcel.Quit( )
  218. 			if ( $Debug ) {
  219. 				Write-Host ( "Finished at {0} (elapsed time {1}" -f ( Get-Date ), $StopWatch.Elapsed )
  220. 				$StopWatch.Stop( )
  221. 			}
  222. 		}
  223. 	} else {
  224. 		if ( $Debug ) {
  225. 			Write-Host ( "Database folder `"{0}`" not found" -f $dbfolder )
  226. 		}
  227. 		if ( $Quiet ) {
  228. 			Exit 1
  229. 		} else {
  230. 			$message = "No downloaded Dutch aircraft registration database was found.`n`nDo you want to open the download webpage for the database now?"
  231. 			$title   = "No Database Found"
  232. 			$buttons = "YesNo"
  233. 			Add-Type -AssemblyName 'System.Windows.Forms'
  234. 			$answer = [System.Windows.Forms.MessageBox]::Show( $message, $title, $buttons )
  235. 			if ( $answer -eq 'Yes' ) {
  236. 				$url = 'https://www.ilent.nl/onderwerpen/luchtvaartuigregister/documenten/publicaties/2019/05/27/luchtvaartuigregister-aircraft-registration'
  237. 				Start-Process $url
  238. 			} else {
  239. 				ShowHelp( "No downloaded Dutch aircraft registration database found, please download it and try again" )
  240. 			}
  241. 		}
  242. 	}
  243. }
  244.  
  245. # Terminate Excel if requested and if this script's Excel process was the only instance
  246. $processcount = ( Get-Process -Name "Excel" -ErrorAction 'SilentlyContinue' | Measure-Object ).Count
  247. if ( $Debug ) {
  248. 	Write-Host ( "{0} active Excel processes when done" -f $processcount )
  249. }
  250. if ( $TerminateExcel -and !$excelactive -and ( $processcount -eq 1 ) ) {
  251. 	if ( $Debug ) {
  252. 		Write-Host "Terminating Excel now"
  253. 	}
  254. 	Get-Process -Name "Excel" -ErrorAction 'SilentlyContinue' | Stop-Process -ErrorAction 'SilentlyContinue'
  255. }
  256.  

page last uploaded: 2019-01-21, 22:48