examples/Process-AthleticObservers.ps1
|
#Import Useful Modules Import-Module .\PSBlackboard.psm1 -Verbose -force #$NewDataSource = Set-BBDataSource -ExternalId "LMSConnect" -description "Used for processing Athletic Observer Users and Assiciations" -Verbose # Get a list of users # Get datasources $Datasources = Get-BBDataSources -ExternalId 'ATHLETICS_OBSERVERS' -Verbose $Observers = Get-BBUsers -Filter 'userName:obs-' -verbose #Location of the file to pull information from: # Note, headers should be as follows: # CMU ID Sport #If additional headers are there, it's fine. It must be an excel document, though. #Install-Module -Name ImportExcel $PSScriptRoot = 'C:\Users\it-jacks3m\Desktop\bb-athletics-observer-parser-master' $ticketnumber = "4000473" $importFile = $PSScriptRoot+"\input\$($ticketnumber).xlsx" $outputFile = $PSScriptRoot+"\output\$($ticketnumber)_Associations.txt" #$newUserFile = $PSScriptRoot+"\output\$($ticketnumber)_NewUsers.txt" $erroroutputFile = $PSScriptRoot+"\output\$($ticketnumber)_Errors.txt" Clear-Content $outputFile Clear-Content $erroroutputFile $studentAthletes = Import-Excel -Path ($importFile) -ErrorAction Stop Write-Output "Number of Student Athletes: $($studentAthletes.length)" #$Observers = @("cotto1js", "albre2tl", "maria1l", "batty1m", "peaco1ml", "weave2ta", "griff7c", "blunt2r") $sportSpecific = @{ "Baseball" = @("obs-cotto1js", "obs-weave2ta"); "Men's Basketball" = @("obs-dance1mr"); "Men's Cross Country" = @("obs-weave2ta","obs-cotto1js"); "Men's Football" = @("obs-dance1mr","obs-maria1l"); "Men's Indoor Track" = @("obs-weave2ta","obs-cotto1js"); "Men's Outdoor Track" = @("obs-weave2ta","obs-cotto1js"); "Men's Wrestling" = @("obs-batty1m"); "Women's Basketball" = @("obs-cotto1js","obs-weave2ta"); "Women's Cross Country" = @("obs-weave2ta","obs-cotto1js"); "Women's Field Hockey" = @("obs-batty1m"); "Women's Golf" = @("obs-batty1m","obs-weave2ta"); "Women's Gymnastics" = @("obs-dance1mr"); "Women's Indoor Track" = @("obs-weave2ta","obs-cotto1js"); "Women's Lacrosse" = @("obs-batty1m"); "Women's Outdoor Track" = @("obs-weave2ta","obs-cotto1js"); "Women's Soccer" = @("obs-batty1m"); "Women's Softball" = @("obs-maria1l"); "Women's Volleyball" = @("obs-cotto1js","obs-weave2ta"); } $allSports = @("obs-griff7c","obs-blunt2r") $conn=new-object System.Data.SqlClient.SQLConnection $ConnectionString = "Server=it-webdb.central.cmich.local;Database=ADBFeed;Integrated Security=True;" $conn.ConnectionString=$ConnectionString $conn.Open() "external_user_key|external_observer_key|data_source_key" | Out-File $outputFile foreach ($athlete in $studentAthletes) { $cmid = $athlete."Campus ID" $firstname = $athlete."First Name" $lastname = $athlete."Last Name" $sport = $athlete."Athlete Code description" Write-Output "($($cmid)) is a $($sport) Student-Athlete" if (![string]::IsNullOrWhiteSpace($cmid)) { $get_user = Get-BBUsers -ExternalId 'obs-albre2tl' -verbose $Observers = Get-BBUsers -ExternalId 'obs-albre2tl' -verbose $get_user = New-Object System.Data.SqlClient.SqlCommand("SELECT user_id FROM users WHERE student_id LIKE '%$($cmid)'",$conn) $user_data_reader = $get_user.ExecuteReader() if($user_data_reader.Read()) { $gid = $user_data_reader.GetString(0) foreach ($observer in $allSports) { "$($gid)|$($observer)|ATHLETICS_OBSERVER" | Out-File $outputFile -Append Write-Host "`t`tAssign $($observer) who gets all sports" } if ($sportSpecific.ContainsKey($sport)) { foreach ($observer in $sportSpecific.Get_Item($sport)) { "$($gid)|$($observer)|ATHLETICS_OBSERVER" | Out-File $outputFile -Append Write-Host "`t`tAssign $($observer) who gets $($sport)" } } } else { #$gid = "ERROR_DO_NOT_SUBMIT_THIS_LINE - $cmid" "$cmid $firstname $lastname was not found in blackboard. This usually means they are not an admitted student."| Out-File $erroroutputFile -Append } $user_data_reader.Close() } } Invoke-Item $PSScriptRoot |