· 5 years ago · Jul 19, 2020, 10:18 PM
1<#
2 # *** Requires Powershell Core
3 #>
4
5function Get-ExcelData {
6 <#
7 .SYNOPSIS
8 Get data from Excel workbook
9
10 .DESCRIPTION
11 Requires ExcelImport module. .XLS documents need to be converted to .XLSX. Reads uniform column row data
12 from all worksheets into a dataset
13
14 .PARAMETER inFile
15 The input .xlsx file path
16
17 .EXAMPLE
18 $ExcelData = Get-ExcelData -InFile $Infile
19
20 .NOTES
21 See Get-Help Get-ExcelSheetInfo and Get-Help Import-Excel for the ImportExcel module cmdlet help
22 #>
23
24 [CmdletBinding()]
25 param (
26 [parameter()]
27 [string]$inFile
28 )
29
30 Process {
31 $sheets = Get-ExcelSheetInfo $Infile
32 $ExcelData = @()
33
34 foreach ($worksheet in $sheets) {
35 $ExcelData += Import-Excel -Path $Infile -WorksheetName $worksheet.name
36 }
37 return $ExcelData
38 }
39}
40
41function Initialize-OAuthSession {
42 [CmdletBinding()]
43 param (
44 [parameter()]
45 [string]$ClientPath,
46 [parameter()]
47 [string]$ServiceUri
48 )
49
50 process
51 {
52 $hasCredFile = Test-Path -Path $(Join-Path $ClientPath '\EM_Credentials.xml') -PathType leaf
53
54 if (!$hasCredFile) {
55 Get-Credential -Credential (Get-Credential) | Export-Clixml -Path $(Join-Path $ClientPath '\EM_Credentials.xml')
56 }
57 $Credentials = Import-Clixml -Path $(Join-Path $ClientPath '\EM_Credentials.xml')
58 $user = $Credentials.UserName.ToString()
59 $password = $Credentials.GetNetworkCredential().Password.trim('{}')
60 $pair = "$User`:$Password"
61 $bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)
62 $base64 = [System.Convert]::ToBase64String($bytes)
63 $Headers = @{Authorization = "Basic $base64" }
64 <# Initialize API session #>
65 $OAuthToken = (Invoke-WebRequest -Uri $([System.Uri]::new($ServiceUri + '/sdk_initialize')) -Method Get -Headers $Headers)
66 $OAuthToken | Export-Clixml -Path $(Join-Path $ClientPath $((get-date -f yyyyMMddhhmmss) + "_token.xml"))
67 <# Let's trash any cleartext we have generated in memory #>
68 Remove-Variable -Name * -ErrorAction SilentlyContinue
69 }
70}
71
72function Test-OAuthToken {
73 [CmdletBinding()]
74 param (
75 [ValidateScript( {
76 If (!(test-path $_)) {
77 New-Item -Path $($_) -ItemType "directory"
78 }
79 return $true
80 })]
81 [System.IO.FileInfo]$ClientPath
82 )
83
84 process
85 {
86 $fileDate =
87 Get-ChildItem -Path $ClientPath -Filter "*token.xml" |
88 Select-Object -Last 1 |
89 ForEach-Object {
90 $date = ($_.Name).Split("_", 2)[0]
91 Write-Output $date
92 }
93
94 if (!$fileDate) {
95 Write-Host "-- No token file found, initializing a new OAuth session..."
96 return $False
97 }
98
99 else {
100 $matchFileDate = $([datetime]::parseexact($fileDate, 'yyyyMMddhhmmss', $null))
101
102 if ($matchFileDate) {
103 $OAuthTokenAge = $(New-TimeSpan -Start $matchFileDate -End $(Get-Date)).Days
104
105 if ($OAuthTokenAge -ge 7) {
106 Write-Host "-- Token age more than 7 days, initializing a new OAuth session..."
107 Remove-Item -Path $ClientPath"\*token.xml"
108 return $False
109 }
110
111 else {
112 Write-Host "-- Token is nominally valid, requesting resource"
113 return $True
114 }
115 }
116 }
117 }
118}
119
120function New-ApiRequest {
121 [CmdletBinding()]
122 [Alias("napi")]
123 param (
124 [parameter()]
125 [string]$ClientPath,
126 [parameter()]
127 [string]$Method,
128 [parameter()]
129 [string]$ServiceUri,
130 [parameter()]
131 [string]$Query,
132 [parameter()]
133 [string]$Body,
134 [parameter()]
135 [switch]$Search # Search cached server results
136 )
137
138 process
139 {
140
141 try {
142 $OAuthToken = Get-ChildItem -Path $ClientPath -Filter "*token.xml" |
143 Select-Object -Last 1 |
144 Import-Clixml -PipelineVariable $_
145 $headers = @{'token' = $OAuthToken.content.trim('"') }
146
147 if ($method -eq "POST") {
148 $Response = Invoke-RestMethod -Method $Method -Headers $headers -Uri "$ServiceUri$Query" -ContentType "application/json" -Body $Body
149 # return $Response
150 }
151
152 elseif ((!$Search) -and ($Method -eq "GET")) {
153 $Response = Invoke-RestMethod -Uri "$ServiceUri$Query" -Method $Method -Headers $headers -ContentType "application/json" -ResponseHeadersVariable ResponseHeader
154 $SearchMetadata = $ResponseHeader.'X-SearchMetadata' | ConvertFrom-Json
155 $SearchMetadata.Links.Self -match '(\S+\?)(.+.\|)([^&]*)(\$[a-z]+=.+)' ## match on APISearch GUID in query string
156 $APISearchGUID = $Matches[3]
157 $MyResponseObject = [PSCustomObject]@{
158 Headers = $ResponseHeader
159 Search = $SearchMetadata
160 APISearch = $APISearchGUID
161 Response = $Response
162 }
163 return $MyResponseObject
164 }
165
166 else {
167 $Response = Invoke-RestMethod -Uri "$ServiceUri$Query" -Method $Method -Headers $headers -ContentType "application/json"
168 return $Response
169 }
170 }
171
172 catch {
173 Write-Warning "$($_.Exception.Response.ReasonPhrase) - Session has expired, or credential is invalid. If your password is no longer valid, delete $ClientPath \EM_Credentials.xml"
174
175 if ($_.Exception.Response.ReasonPhrase -eq "Unauthorized") {
176 Remove-Item -Path "$ClientPath\*token.xml"
177 Read-Host "Session has expired. Click ENTER to exit, then re-run this script"
178 }
179 Exit
180 }
181
182 finally {
183 Remove-Variable -Name * -Force -ErrorAction SilentlyContinue # Nothing to see here
184 }
185 }
186}
187
188Clear-Host
189
190if ((Get-Host).Version.Major -lt 6) {
191 Write-Host "This script requires PowerShell Core. Go to https://github.com/PowerShell/PowerShell for information"
192 Read-Host -Prompt "Click ENTER to exit"
193 Exit
194}
195
196if (-not (Get-InstalledModule -Name ImportExcel -MinimumVersion 7.0.1)) {
197 Install-Module -Name ImportExcel -RequiredVersion 7.0.1
198}
199Write-Host "OERD Credit Courses Event Management Import`r`n=====================================" -ForegroundColor White -BackgroundColor Blue
200$opt = (Get-Host).PrivateData
201$opt.WarningBackgroundColor = "Yellow"
202$opt.WarningForegroundColor = "Black"
203$opt.ErrorBackgroundColor = "DarkRed"
204$opt.ErrorForegroundColor = "White"
205$ClientPath = "C:\CIS\API"
206$ServiceUri = "https://registeruodev.niu.edu/eventmanagement/api/v1"
207$hasValidToken = Test-OAuthToken -ClientPath $ClientPath
208$ImportData = @()
209$AccountException = $False
210$EventException = $False
211$EventAdded = 0
212
213$Infile = [Environment]::GetFolderPath("Desktop") + "\Course Import\2208courses_.xlsx"
214Write-Host "-- Loading course data `"$Infile`""
215$CourseData = Get-ExcelData -InFile $Infile
216
217$Infile = [Environment]::GetFolderPath("Desktop") + "\Course Import\AccountMapPrdtst.xlsx"
218Write-Host "-- Loading Department account map `"$Infile`""
219$AccountMap = Get-ExcelData -InFile $Infile
220
221<# Check if department is mapped to an Event Management account code #>
222foreach ($course in $CourseData) {
223
224 if (!$AccountMap.DeptCode.Contains($course.Department)) {
225 Write-Host "$($course.Department) is not in account map"
226 $AccountException = $True
227 }
228}
229
230if ($AccountException) {
231 Write-Warning "Add missing Department account code(s) to `"$Infile`", then re-run this script"
232 Read-Host "Click ENTER to exit"
233 Exit
234}
235
236if (!($hasValidToken)) { Initialize-OAuthSession -ClientPath $ClientPath -ServiceUri $ServiceUri }
237$Start = [system.diagnostics.stopwatch]::StartNew()
238<# Initiallize cached search #>
239$CachedSearch = New-ApiRequest -ClientPath $ClientPath -Method GET -ServiceUri $ServiceUri -Query "/Events/07?search=StartDate gt datetime'2020-07-01' and Type eq 'CREDI'`$orderby=EventID desc"
240
241if ($CachedSearch.Search.PageTotal -lt 1) {
242 Write-Host "-- There are no events in Event Management matching query, adding courses to event list"
243
244 foreach ($course in $CourseData) {
245 $ImportData += $course
246 }
247}
248<# Load results from cached search #>
249else {
250 Write-Host "-- Getting Event Management event data"
251 Write-Host "-- $($CachedSearch.Search.ResultsTotal) results in $($CachedSearch.Search.PageTotal) cached page(s):"
252
253 for ($i = 1; $i -le ($CachedSearch.Search.PageTotal); $i++) {
254 Write-Host " Loading page $i"
255 $CachedSearchResults = New-ApiRequest -Method GET -ClientPath $ClientPath -ServiceUri $ServiceUri -Search -Query "/Events/07?search=APISearch|$($CachedSearch.'APISearch')`$page=$i"
256 $ImportData += , $CachedSearchResults
257 }
258 Write-Host "-- Checking for existing course events in Event Management:"
259
260 foreach ($event in $ImportData) {
261
262 foreach ($course in $CourseData) {
263
264 if (($event.Description2 -eq $course."Course ID") -and ($event.Description3 -eq $course."Class Nbr")) {
265 Write-Host " " $course."SEMject" $course."Catalog" $course."Section" $course."Title" $course."Course ID" $course."Class Nbr" "exists in Event Management"
266 }
267
268 else {
269 $ImportData += $course
270 $EventException = $True
271 }
272 }
273 }
274}
275
276if ($EventException) {
277
278 foreach ($exception in $ImportData) {
279
280 if ($exception.SEMject.GetType().FullName -eq "System.String") {
281 Write-Warning "$($exception.SEMject) $($exception.Catalog) $($exception.Section) $($exception.Title) $($exception."Course ID") $($exception."Class Nbr") is missing from Event Management"
282 }
283 }
284 Write-Host "-- Posting events to Event Management:"
285
286 <# Create Events #>
287 foreach ($course in $ImportData) {
288
289 foreach ($account in $AccountMap) {
290
291 if ($course.Department -eq $account.DeptCode) {
292 $course | Where-Object { $_ -ne $null } | Add-Member -MemberType NoteProperty "EMAcctCode" -Value $account.EMAcctCode
293 $EMAcctCode = $course.EMAcctCode
294 $Description = $course.Title
295 $StartDate = Get-Date -Date $course."Start Date" -format o
296 $EndDate = Get-Date -Date $course."End Date" -format o
297 $AnchorSpace = $course."Location Code"
298 $EnrlCap = $course."Enrl Cap"
299 $Description = -join ($course.SEMject, " ", $course.Catalog, " ", $course.Section, " ", $course.Title)
300 $AltDescription1 = $course.Title
301 $AltDescription2 = $course."Course ID"
302 $AltDescription3 = $course."Class Nbr"
303 Write-Host " $Description added to Event Management"
304
305 # Forego proper indentation for Here-String convienence
306 # POST payload JSON
307 # Enjoy this comment block!
308
309 $Body =
310@"
311{
312 'Organization': '07',
313 'AnchorVenue': '$AnchorSpace',
314 'Type': 'CREDI',
315 'Description': '$Description',
316 'Status': '05',
317 'Account': '$EMAcctCode',
318 'StartDate': '$StartDate',
319 'EndDate': '$EndDate',
320 'Attendance': '$EnrlCap',
321 'Description1': '$AltDescription1',
322 'Description2': '$AltDescription2',
323 'Description3': '$AltDescription3'
324}
325"@
326
327 New-ApiRequest -ClientPath $ClientPath -Method POST -ServiceUri $ServiceUri -Query "/Events/07?" -Body $Body
328 $EventAdded++
329 }
330 }
331 }
332}
333
334Write-Host "`r`nScript completed. $($EventAdded) event(s) added.`r`nExecution time:`t"$Start.Elapsed.TotalMilliseconds"milliseconds"