HomeBasedDDLSClassTools.psm1
|
function Submit-ClassRoll { <# .SYNOPSIS Converts DDLS student list into web output .DESCRIPTION Extract the list of students from the Teams trainer portal as a CSV file. Once this has been done you can then run this script which will ask you for attendance for each student and produce a report that you can paste into an email sent to Training. This script also has the ability to print an intoduction list that allows you to write down information about each of the students as they introduce themselves, it also gives you the ability to print a list of labs that you can tick off as the class members finish their labs. .EXAMPLE Submit-ClassRoll -CsvFileName students.csv -TrainerName 'Brent Denny' -PathToFiles 'c:\class' This will ask for confirmation for each students attendance on the course and then produce a report which we need to email to training. .EXAMPLE Submit-ClassRoll -CsvFileName students.csv -StudentIntro -PathToFiles 'c:\class' This will produce a report that allows writing space beside each students name to record the info from their introduction. .EXAMPLE Submit-ClassRoll -CsvFileName students.csv -LabList -NumberOfLabs 12 -PathToFiles 'c:\class' This will produce a report showing a list of lab number next to each student so that you can track which labs they have finished in the course. .EXAMPLE Submit-ClassRoll -CsvFileName students.csv -ShowAllDetails This lists all of the student details on screen and does nothing else, this is not compatible with any other parameters other than CsvFileName. .PARAMETER CsvFileName This points to the file path of the CSV file that we export from the Teams trainer portal. .PARAMETER TrainerName This is the name of the trainer that gets added to the attendance report so that when we paste this into the body of the email, we can then cut the "Attendance BRENT DENNY" from the body and this into the mail subject, as required. .PARAMETER StudentIntro This will create a report to record the introduction information from the students as they talk about themselves. .PARAMETER LabList This will create a report that will list lab numbers per student that we can tick off as they finish each lab. .PARAMETER NumberOfLabs This will give you the ability to specify how many lab numbers show on the web page, the default is 18, this was chosen as it would cover most courses, not many courses exceed 18 labs. .PARAMETER PathToFiles This is the path to find the CSV file and where this script will create the reports to be opened by the systems default browser, this is the folder path not the file names, the default path points to your current Downloads directory. .PARAMETER ShowAllDetails This lists all of the student details on screen and does nothing else, this is not compatible with any other parameters other than CsvFileName. .NOTES General notes Created By: Brent Denny Created On: 19 Jan 2021 Last Change: 5 Apr 2021 #> [CmdletBinding(DefaultParameterSetName='DefaultParams')] Param( [Parameter(Mandatory=$true)] [string]$CsvFileName, [string]$TrainerName = 'Brent Denny', [switch]$StudentIntro, [switch]$LabList, [int]$NumberOfLabs = 18, [string]$PathToFiles = ((New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path), [switch]$ShowAllDetails ) # Extracting failename from $CsvFileName $CsvFileName = $CsvFileName | Split-Path -leaf # Setup file paths $CsvFullPath = $PathToFiles.TrimEnd('\') + '\' + $CsvFileName if (Test-Path -Path $CsvFullPath -PathType Leaf) { $AttendanceReportPath = $PathToFiles.TrimEnd('\') + '\ClassAttendance.html' $LabListReportPath = $PathToFiles.TrimEnd('\') + '\ClassLabList.html' $IntroReportPath = $PathToFiles.TrimEnd('\') + '\ClassIntro.html' $UpCaseTrainer = $TrainerName.ToUpper() # Locate the default browser on the computer $DefaultSettingPath = 'HKCU:\SOFTWARE\Microsoft\Windows\Shell\Associations\UrlAssociations\http\UserChoice' $DefaultBrowserName = (Get-Item $DefaultSettingPath | Get-ItemProperty).ProgId $DefaultBrowserOpenCommand = (Get-Item "HKLM:SOFTWARE\Classes\$DefaultBrowserName\shell\open\command" | Get-ItemProperty).'(default)' $DefaultBrowserPath = [regex]::Match($DefaultBrowserOpenCommand,'\".+?\"') $BrowserPath = $DefaultBrowserPath.Value # Getting student details into a PowerShell object $RawStudentListCsv = Get-Content $CsvFullPath # Remove any non-student entries and removing spaces from CSV property names $RemoveSpacesFromCsvTitles = (($RawStudentListCSV | Select-Object -First 1) -replace '(\s)|(\(.*?\)|\-)','' ) -split ',' $RawStudentListCsvNoTitle = $RawStudentListCsv | Select-Object -Skip 1 $StudentListCSVtoObj = $RawStudentListCsvNoTitle | ConvertFrom-Csv -Header $RemoveSpacesFromCsvTitles | Sort-Object -Property StudentName if ($ShowAllDetails -eq $true) { return $StudentListCSVtoObj } $StudentCount = $StudentListCSVtoObj.Count # Calculating how much space for padding to fit all students one one page if ($StudentCount -gt 12) {$Padding = 40} else {$Padding = 200 - (12.5 * $StudentCount) } # Setting up the CSS for the web output $CSSsmall = @" <style> table, tr,td,th {border:black 1pt solid;border-collapse:collapse;} td,th {padding-left:4pt;padding-right:4px;} td:nth-child(1) {width: auto;} td:nth-child(2) {width: auto;} </style> "@ $CSSwide = @" <style> table, tr,td,th {border:black 1pt solid;border-collapse:collapse;} td,th {padding-left:4pt;padding-right:4px;} td {padding-bottom: ${Padding}px;} td:nth-child(1) {width: 20%;} td:nth-child(2) {text-align-last: justify;font-size:20px} table {width: 100%} </style> "@ if ($LabList -eq $false -and $StudentIntro -eq $false) { #Check for attendance $StudentsWithAttendance = foreach ($Student in $StudentListCSVtoObj) { $AttendanceResult = Read-Host -Prompt "Is $($Student.StudentName) in attendance (Y or N) Default-Y" if ($AttendanceResult -eq '' -or $AttendanceResult -like 'y*') { $Student | Select-Object -Property *,@{n='InClass';e={'Y'}} } else { $Student | Select-Object -Property *,@{n='InClass';e={'N'}} } } try { $StudentsWithAttendance | Select-Object -Property StudentName,InClass | ConvertTo-Html -Head $CSSsmall -precontent "<h2>Attendance $UpCaseTrainer</h2> <br>" | Out-File $AttendanceReportPath Start-Process -FilePath $BrowserPath -ArgumentList $AttendanceReportPath } catch {Write-Warning "The Attendance html document could not be written to disk"; break} } if ($LabList -eq $true) { $StudentsLabNumbers = $StudentListCSVtoObj | Select-Object *, @{n='LabNumbers';e={(1..$NumberOfLabs) -join ' '}} try { $StudentsLabNumbers | Select-Object -Property StudentName,LabNumbers | ConvertTo-Html -Head $CSSwide | Out-File $LabListReportPath Start-Process -FilePath $BrowserPath -ArgumentList $LabListReportPath } catch {Write-Warning "The Lablist html document could not be written to disk"; break} } if ($StudentIntro -eq $true) { try { write-debug "intro" $StudentListCSVtoObj | Select-Object -Property StudentName,Intro | ConvertTo-Html -Head $CSSwide | Out-File $IntroReportPath Start-Process -FilePath $BrowserPath -ArgumentList $IntroReportPath } catch {Write-Warning "The Intro html document could not be written to disk"; break} } } else { Write-Warning "The CSV file path $CsvFullPath is not found" } } function Invoke-ClassTimer { <# .Synopsis Calculates the end of break times in multiple cities and shows a countdown timer .DESCRIPTION This program calclates event end times for Brisbane, Melbourne/Sydney, Adelaide Perth, Auckland and the USA Pacific TZ by default. Determining when each city should end the event they are undertaking break/lab, relevant to their timezone. You can enter the "Event Length" in minutes time and then click "Calculate End Time" and it will automatically determine when each city's end time should be. It will also start a countdown timer in color stages Green for > 2 min left, Orange for 0 >= time left >= 2, and Red for time is up! .EXAMPLE Invoke-ClassTimer This will start a gui tool to determine end of break times .NOTES General notes Created By: Brent Denny Created On: 28 May 2020 Last Changed: 24 Aug 2022 Change Log: 14Sep21: Changing the timer to be a general time and not just for breaks Cleand up the help and disabled Melbourne in the GUI 24Aug22: Rewrote the entire command to be able to run form anywhere using UTC time values. also added color coding as the timer runs out, added each city as a combo box so that all of the cities can be changed. 25Aug22: Modified the count down timer to refresh the window every second. 12Dec22: Modified the colors of the countdown box and set the value back to actual minutes left. #> [cmdletbinding()] Param () function Get-CountDownValue { Param ( $TrigTime,$EventTS ) return (($TrigTime + $EventTS) - ((Get-Date).ToUniversalTime())).TotalMinutes -as [string] } $AllTimeZones = Get-TimeZone -ListAvailable | Select-Object -Property *,@{n='TimeSpan';e={[timespan]$_.BaseUtcOffset}} $TriggeredUtcTime = (Get-Date).ToUniversalTime() Add-Type -AssemblyName System.Windows.Forms [System.Windows.Forms.Application]::EnableVisualStyles() $Form = New-Object system.Windows.Forms.Form $Form.ClientSize = New-Object System.Drawing.Point(524,683) $Form.text = "DDLS Class Timer" $Form.TopMost = $true $Form.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#f4f4f4") $TopGroupBox = New-Object system.Windows.Forms.Groupbox $TopGroupBox.height = 256 $TopGroupBox.width = 480 $TopGroupBox.location = New-Object System.Drawing.Point(16,16) $BottomGroupBox = New-Object system.Windows.Forms.Groupbox $BottomGroupBox.height = 340 $BottomGroupBox.width = 480 $BottomGroupBox.location = New-Object System.Drawing.Point(16,268) $CalcButton = New-Object system.Windows.Forms.Button $CalcButton.text = "Calculate End Time" $CalcButton.width = 170 $CalcButton.height = 30 $CalcButton.location = New-Object System.Drawing.Point(32,125) $CalcButton.Font = New-Object System.Drawing.Font('Arial',10) $CalcButton.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#ffffff") $ResetButton = New-Object system.Windows.Forms.Button $ResetButton.text = "Reset" $ResetButton.width = 100 $ResetButton.height = 30 $ResetButton.location = New-Object System.Drawing.Point(274,618) $ResetButton.Font = New-Object System.Drawing.Font('Arial',10) $ResetButton.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#ffffff") $CloseButton = New-Object system.Windows.Forms.Button $CloseButton.text = "Close" $CloseButton.width = 100 $CloseButton.height = 30 $CloseButton.location = New-Object System.Drawing.Point(392,618) $CloseButton.Font = New-Object System.Drawing.Font('Arial',10) $CloseButton.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#ffffff") $EnterTimeTextBox = New-Object system.Windows.Forms.TextBox $EnterTimeTextBox.multiline = $false $EnterTimeTextBox.width = 42 $EnterTimeTextBox.height = 42 $EnterTimeTextBox.location = New-Object System.Drawing.Point(32,81) $EnterTimeTextBox.Font = New-Object System.Drawing.Font('Arial',14) $EnterTimeTextBox.Text = 15 $EnterTimeTextBox.TextAlign = 'Right' $EventLengthLabel = New-Object system.Windows.Forms.Label $EventLengthLabel.text = "Event Length (Min.)" $EventLengthLabel.AutoSize = $true $EventLengthLabel.width = 25 $EventLengthLabel.height = 10 $EventLengthLabel.location = New-Object System.Drawing.Point(92,90) $EventLengthLabel.Font = New-Object System.Drawing.Font('Arial',10) $ReturnTimeLabel = New-Object system.Windows.Forms.Label $ReturnTimeLabel.text = "Return time" $ReturnTimeLabel.AutoSize = $true $ReturnTimeLabel.width = 25 $ReturnTimeLabel.height = 10 $ReturnTimeLabel.location = New-Object System.Drawing.Point(310,16) $ReturnTimeLabel.Font = New-Object System.Drawing.Font('Arial',10) $ReturnTimeLabel.BorderStyle = [System.Windows.Forms.BorderStyle]::FixedSingle $ReturnTimeLabel.Visible = $false $CountDownBox = New-Object system.Windows.Forms.TextBox $CountDownBox.multiline = $false $CountDownBox.width = 174 $CountDownBox.height = 20 $CountDownBox.location = New-Object System.Drawing.Point(284,58) $CountDownBox.Font = New-Object System.Drawing.Font('Arial',80) $CountDownBox.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#42f545") $CountDownBox.Visible = $false $CountDownBox.TextAlign = 'Right' $BrisbaneTZ = $AllTimeZones | Where-Object {$_.DisplayName -match 'Brisbane'} $ComboBox1 = New-Object system.Windows.Forms.ComboBox $ComboBox1.width = 260 $ComboBox1.height = 56 $ComboBox1.location = New-Object System.Drawing.Point(27,40) $ComboBox1.Font = New-Object System.Drawing.Font('Arial',10) $ComboBox1.text = $BrisbaneTZ.DisplayName $ComboBox1.Items.AddRange($AllTimeZones.DisplayName) $ComboBox1.Enabled = $false $SydneyTZ = $AllTimeZones | Where-Object {$_.DisplayName -match 'Sydney'} $ComboBox2 = New-Object system.Windows.Forms.ComboBox $ComboBox2.width = 260 $ComboBox2.height = 56 $ComboBox2.location = New-Object System.Drawing.Point(27,90) $ComboBox2.Font = New-Object System.Drawing.Font('Arial',10) $ComboBox2.text = $SydneyTZ.DisplayName $ComboBox2.Items.AddRange($AllTimeZones.DisplayName) $ComboBox2.Enabled = $false $AdelaideTZ = $AllTimeZones | Where-Object {$_.DisplayName -match 'Adelaide'} $ComboBox3 = New-Object system.Windows.Forms.ComboBox $ComboBox3.width = 260 $ComboBox3.height = 56 $ComboBox3.location = New-Object System.Drawing.Point(27,140) $ComboBox3.Font = New-Object System.Drawing.Font('Arial',10) $ComboBox3.text = $AdelaideTZ.DisplayName $ComboBox3.Items.AddRange($AllTimeZones.DisplayName) $ComboBox3.Enabled = $false $PerthTZ = $AllTimeZones | Where-Object {$_.DisplayName -match 'Perth'} $ComboBox4 = New-Object system.Windows.Forms.ComboBox $ComboBox4.width = 260 $ComboBox4.height = 56 $ComboBox4.location = New-Object System.Drawing.Point(27,190) $ComboBox4.Font = New-Object System.Drawing.Font('Arial',10) $ComboBox4.text = $PerthTZ.DisplayName $ComboBox4.Items.AddRange($AllTimeZones.DisplayName) $ComboBox4.Enabled = $false $AucklandTZ = $AllTimeZones | Where-Object {$_.DisplayName -match 'Auckland'} $ComboBox5 = New-Object system.Windows.Forms.ComboBox $ComboBox5.width = 260 $ComboBox5.height = 56 $ComboBox5.location = New-Object System.Drawing.Point(27,240) $ComboBox5.Font = New-Object System.Drawing.Font('Arial',10) $ComboBox5.text = $AucklandTZ.DisplayName $ComboBox5.Items.AddRange($AllTimeZones.DisplayName) $ComboBox5.Enabled = $false $WestUsaTZ = $AllTimeZones | Where-Object {$_.DisplayName -match 'Pacific'} $ComboBox6 = New-Object system.Windows.Forms.ComboBox $ComboBox6.width = 260 $ComboBox6.height = 56 $ComboBox6.location = New-Object System.Drawing.Point(27,290) $ComboBox6.Font = New-Object System.Drawing.Font('Arial',10) $ComboBox6.text = $WestUsaTZ.DisplayName $ComboBox6.Items.AddRange($AllTimeZones.DisplayName) $ComboBox6.Enabled = $false $ComboLabel1 = New-Object system.Windows.Forms.Label $ComboLabel1.text = "" $ComboLabel1.AutoSize = $true $ComboLabel1.width = 29 $ComboLabel1.height = 10 $ComboLabel1.location = New-Object System.Drawing.Point(310,42) $ComboLabel1.Font = New-Object System.Drawing.Font('Arial',10) $ComboLabel2 = New-Object system.Windows.Forms.Label $ComboLabel2.text = "" $ComboLabel2.AutoSize = $true $ComboLabel2.width = 29 $ComboLabel2.height = 10 $ComboLabel2.location = New-Object System.Drawing.Point(310,92) $ComboLabel2.Font = New-Object System.Drawing.Font('Arial',10) $ComboLabel3 = New-Object system.Windows.Forms.Label $ComboLabel3.text = "" $ComboLabel3.AutoSize = $true $ComboLabel3.width = 29 $ComboLabel3.height = 10 $ComboLabel3.location = New-Object System.Drawing.Point(310,142) $ComboLabel3.Font = New-Object System.Drawing.Font('Arial',10) $ComboLabel4 = New-Object system.Windows.Forms.Label $ComboLabel4.text = "" $ComboLabel4.AutoSize = $true $ComboLabel4.width = 29 $ComboLabel4.height = 10 $ComboLabel4.location = New-Object System.Drawing.Point(310,192) $ComboLabel4.Font = New-Object System.Drawing.Font('Arial',10) $ComboLabel5 = New-Object system.Windows.Forms.Label $ComboLabel5.text = "" $ComboLabel5.AutoSize = $true $ComboLabel5.width = 29 $ComboLabel5.height = 10 $ComboLabel5.location = New-Object System.Drawing.Point(310,242) $ComboLabel5.Font = New-Object System.Drawing.Font('Arial',10) $ComboLabel6 = New-Object system.Windows.Forms.Label $ComboLabel6.text = "" $ComboLabel6.AutoSize = $true $ComboLabel6.width = 29 $ComboLabel6.height = 10 $ComboLabel6.location = New-Object System.Drawing.Point(310,292) $ComboLabel6.Font = New-Object System.Drawing.Font('Arial',10) $HourGlassPicBox = New-Object system.Windows.Forms.PictureBox $HourGlassPicBox.width = 30 $HourGlassPicBox.height = 30 $HourGlassPicBox.location = New-Object System.Drawing.Point(210,168) $HourGlassPicBox.imageLocation = "https://media4.giphy.com/media/l4FGIO2vCfJkakBtC/source.gif" $HourGlassPicBox.SizeMode = [System.Windows.Forms.PictureBoxSizeMode]::zoom $HourGlassPicBox.Visible = $false $Form.controls.AddRange(@( $TopGroupBox, $BottomGroupBox, $ResetButton, $CloseButton) ) $TopGroupBox.controls.AddRange(@( $EnterTimeTextBox, $CalcButton, $EventLengthLabel, $CountDownBox, $HourGlassPicBox) ) $BottomGroupBox.controls.AddRange(@( $ComboBox1, $ComboBox2, $ComboBox3, $ComboBox4, $ComboBox5, $ComboBox6, $ReturnTimeLabel, $ComboLabel1, $ComboLabel2, $ComboLabel3, $ComboLabel4, $ComboLabel5, $ComboLabel6) ) $ComboBox1.Add_SelectedValueChanged({ $EventTimeSpan = [timespan]::New(0,[int](($CountDownBox.Text).Trim())-1,59) $TZ1 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox1.Text} $TargetTime1 = $Global:TriggeredUtcTime + $Global:EventTimeSpan + $TZ1.TimeSpan $TZ1Info = Get-TimeZone -Name $TZ1.Id if ($TZ1Info.IsDayLightSavingTime($TargetTime1)) {$TargetTime1 = $TargetTime1.AddHours(1)} $ComboLabel1.Text = "$($TargetTime1.ToShortTimeString()) `($($TargetTime1.DayOfWeek)`)" }) $ComboBox2.Add_SelectedValueChanged({ $TZ2 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox2.Text} $TargetTime2 = $Global:TriggeredUtcTime + $Global:EventTimeSpan + $TZ2.TimeSpan $TZ2Info = Get-TimeZone -Name $TZ2.Id if ($TZ2Info.IsDayLightSavingTime($TargetTime2)) {$TargetTime2 = $TargetTime2.AddHours(1)} $ComboLabel2.Text = "$($TargetTime2.ToShortTimeString()) `($($TargetTime2.DayOfWeek)`)" }) $ComboBox3.Add_SelectedValueChanged({ $TZ3 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox3.Text} $TargetTime3 = $Global:TriggeredUtcTime + $Global:EventTimeSpan + $TZ3.TimeSpan $TZ3Info = Get-TimeZone -Name $TZ3.Id if ($TZ3Info.IsDayLightSavingTime($TargetTime3)) {$TargetTime3 = $TargetTime3.AddHours(1)} $ComboLabel3.Text = "$($TargetTime3.ToShortTimeString()) `($($TargetTime3.DayOfWeek)`)" }) $ComboBox4.Add_SelectedValueChanged({ $TZ4 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox4.Text} $TargetTime4 = $Global:TriggeredUtcTime + $Global:EventTimeSpan + $TZ4.TimeSpan $TZ4Info = Get-TimeZone -Name $TZ4.Id if ($TZ4Info.IsDayLightSavingTime($TargetTime4)) {$TargetTime4 = $TargetTime4.AddHours(1)} $ComboLabel4.Text = "$($TargetTime4.ToShortTimeString()) `($($TargetTime4.DayOfWeek)`)" }) $ComboBox5.Add_SelectedValueChanged({ $TZ5 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox5.Text} $TargetTime5 = $Global:TriggeredUtcTime + $Global:EventTimeSpan + $TZ5.TimeSpan $TZ5Info = Get-TimeZone -Name $TZ5.Id if ($TZ5Info.IsDayLightSavingTime($TargetTime5)) {$TargetTime5 = $TargetTime5.AddHours(1)} $ComboLabel5.Text = "$($TargetTime5.ToShortTimeString()) `($($TargetTime5.DayOfWeek)`)" }) $ComboBox6.Add_SelectedValueChanged({ $EventTimeSpan = [timespan]::New(0,[int](($EnterTimeTextBox.Text).Trim()),0) $TZ6 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox6.Text} $TargetTime6 = $TriggeredUtcTime + $EventTimeSpan + $TZ6.TimeSpan $TZ6Info = Get-TimeZone -Name $TZ6.Id if ($TZ6Info.IsDayLightSavingTime($TargetTime6)) {$TargetTime6 = $TargetTime6.AddHours(1)} $ComboLabel6.Text = "$($TargetTime6.ToShortTimeString()) `($($TargetTime6.DayOfWeek)`)"}) $CalcButton.Add_Click({ if ($EnterTimeTextBox.Text -match '^\s*\d+\s*$') { $Global:EventTimeSpan = [timespan]::New(0,[int](($EnterTimeTextBox.Text).Trim()),0) $Global:TriggeredUtcTime = (Get-Date).ToUniversalTime() $TZ1 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox1.Text} $TZ1Info = Get-TimeZone -Name $TZ1.Id $TZ2 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox2.Text} $TZ2Info = Get-TimeZone -Name $TZ2.Id $TZ3 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox3.Text} $TZ3Info = Get-TimeZone -Name $TZ3.Id $TZ4 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox4.Text} $TZ4Info = Get-TimeZone -Name $TZ4.Id $TZ5 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox5.Text} $TZ5Info = Get-TimeZone -Name $TZ5.Id $TZ6 = $AllTimeZones | Where-Object {$_.DisplayName -eq $ComboBox6.Text} $TZ6Info = Get-TimeZone -Name $TZ6.Id $TargetTime1 = $Global:TriggeredUtcTime + $TZ1.TimeSpan + $Global:EventTimeSpan if ($TZ1Info.IsDayLightSavingTime($TargetTime1)) {$TargetTime1 = $TargetTime1.AddHours(1)} $TargetTime2 = $Global:TriggeredUtcTime + $TZ2.TimeSpan + $Global:EventTimeSpan if ($TZ2Info.IsDayLightSavingTime($TargetTime2)) {$TargetTime2 = $TargetTime2.AddHours(1)} $TargetTime3 = $Global:TriggeredUtcTime + $TZ3.TimeSpan + $Global:EventTimeSpan if ($TZ3Info.IsDayLightSavingTime($TargetTime3)) {$TargetTime3 = $TargetTime3.AddHours(1)} $TargetTime4 = $Global:TriggeredUtcTime + $TZ4.TimeSpan + $Global:EventTimeSpan if ($TZ4Info.IsDayLightSavingTime($TargetTime4)) {$TargetTime4 = $TargetTime4.AddHours(1)} $TargetTime5 = $Global:TriggeredUtcTime + $TZ5.TimeSpan + $Global:EventTimeSpan if ($TZ5Info.IsDayLightSavingTime($TargetTime5)) {$TargetTime5 = $TargetTime5.AddHours(1)} $TargetTime6 = $Global:TriggeredUtcTime + $TZ6.TimeSpan + $Global:EventTimeSpan if ($TZ6Info.IsDayLightSavingTime($TargetTime6)) {$TargetTime6 = $TargetTime6.AddHours(1)} $ComboBox1.Enabled = $true $ComboBox2.Enabled = $true $ComboBox3.Enabled = $true $ComboBox4.Enabled = $true $ComboBox5.Enabled = $true $ComboBox6.Enabled = $true $ComboLabel1.Text = "$($TargetTime1.ToShortTimeString()) `($($TargetTime1.DayOfWeek)`)" $ComboLabel2.Text = "$($TargetTime2.ToShortTimeString()) `($($TargetTime2.DayOfWeek)`)" $ComboLabel3.Text = "$($TargetTime3.ToShortTimeString()) `($($TargetTime3.DayOfWeek)`)" $ComboLabel4.Text = "$($TargetTime4.ToShortTimeString()) `($($TargetTime4.DayOfWeek)`)" $ComboLabel5.Text = "$($TargetTime5.ToShortTimeString()) `($($TargetTime5.DayOfWeek)`)" $ComboLabel6.Text = "$($TargetTime6.ToShortTimeString()) `($($TargetTime6.DayOfWeek)`)" $HourGlassPicBox.Visible = $true $CountDownBox.Visible = $true $Timer=New-Object System.Windows.Forms.Timer $Timer.Interval=1000 $Timer.add_Tick({ $MinutesRemaining = (($Global:TriggeredUtcTime + $Global:EventTimeSpan) - [System.DateTime]::UtcNow).Minutes if ($MinutesRemaining -ge 3) {$CountDownBox.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#42f545")} elseif ($MinutesRemaining -ge 1) {$CountDownBox.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#fcb605")} else {$CountDownBox.BackColor = [System.Drawing.ColorTranslator]::FromHtml("#fc2003")} $CountDownBox.text = $MinutesRemaining }) $Timer.Start() } }) $ResetButton.Add_Click({ $ComboLabel1.Text = '' $ComboLabel2.Text = '' $ComboLabel3.Text = '' $ComboLabel4.Text = '' $ComboLabel5.Text = '' $ComboLabel6.Text = '' $HourGlassPicBox.Visible = $false $CountDownBox.Visible = $false $EnterTimeTextBox.Text = 15 $ComboBox1.Enabled = $false $ComboBox2.Enabled = $false $ComboBox3.Enabled = $false $ComboBox4.Enabled = $false $ComboBox5.Enabled = $false $ComboBox6.Enabled = $false if ($Timer) {$Timer.Dispose()} }) $CloseButton.Add_Click({ if ($Timer) {$Timer.Dispose()} if ($Form) { $Form.Close() $Form.Dispose() } }) [void]$Form.ShowDialog() } Function New-MyDDLSClassListIntro { <# .SYNOPSIS Takes the XLSX file from My DDLS classlist and converts it to CSV and HTML files .DESCRIPTION The My DDLS classlist for trainers can be downloaded as an Excel spreadsheet file. This script takes that xlsx file and converts it into an html file ready for printing it has the student's name and a box for notes when running the intro. It also calculates how many students are on the course and creates the table column height high enough to fit a class of 1 - 13 on the same page. .EXAMPLE New-MyDDLSClassListIntro This expects to find a class list downloaded into the downloads directory, if there are several it chooses the most recent file to convert, it then uses the filename to produce csv and html files in the same directory .EXAMPLE New-MyDDLSClassListIntro -ClassRollFile c:\Demo\ClassList.xlsx This locates the class list from the demo directory, it then uses the filename to produce csv and html files in the same directory .PARAMETER ClassRollFile This is the path to, and including the name of the classlist excel file .NOTES General notes Created By: Brent Denny Created On: 20-Apr-2022 Last Modified: 20-Apr-2022 ChangeLog Version Date Details ------- ---- ------- v0.1.0 20-Apr-2022 Created, this converts xlsx DDLS class roll files into csv and html files v0.1.1 02-May-2022 Fixed the Excel discovery issue #> Param ( [string]$ClassRollFile = ((Get-ChildItem ~\downloads\portal_class*.xlsx | Sort-Object -Property LastWriteTime -Descending | Select-Object -First 1).FullName) ) $ExcelAppReg = (Get-ChildItem HKLM:\Software\Classes).Name | Where-Object {$_ -like '*Excel.Application'} if ($ExcelAppReg.Count -eq 0) { Write-Warning "Excel needs to be installed on your computer before running this script" break } $ExcelObj = New-Object -ComObject Excel.Application $ExcelObj.Visible = $false $ExcelObj.DisplayAlerts = $false $WorkBook = $ExcelObj.Workbooks.Open($ClassRollFile) $FileLocation = (Split-Path $ClassRollFile -Parent).TrimEnd('\') + '\' $FileName = Split-Path $ClassRollFile -Leaf foreach ($WorkSheet in $WorkBook.Worksheets) { $CSVName = $FileLocation + $FileName + "_" + $WorkSheet.Name + '.csv' $HtmlName = $FileLocation + $FileName + "_" + $WorkSheet.Name + '.html' $WorkSheet.SaveAs($CSVName, 6) $CsvObj = Import-Csv -Path $CSVName $StudentCount = $CsvObj.count $RowHeight = [math]::Round(900/$StudentCount,0) $CSS = @" <style> table {border:solid 2pt black;border-collapse:collapse;width:100%;} th,tr {border:solid 2pt black;border-collapse:collapse;} td {border:solid 2pt black;border-collapse:collapse;height:${RowHeight}px;} </style> "@ $RawHtml = $CsvObj | Sort-Object -Property Student | Select-Object Student,Notes | ConvertTo-Html -Head $CSS $RawHtml -replace '\<th\>Student\<\/th\>','<th style=width:200px;>Student</th>' | Out-File $HtmlName } $ExcelObj.Quit() } function New-LumifyClassList { <# .Synopsis Lumify classlist exported from the Instructor Portal show as html .DESCRIPTION This command will take a CSV file exported from the Instructor Portal and convert it into a HTML document and launch the browser or it will show the information on screen in a table. The Csv data is obtained by going to the "Trainer Portal" in MS Teams, left-click within the area where the student info is found, choose the ellipsis (More Options) and then choose "Export Data" than "Summarized Data" and finally choose the file format as CSV and Click the Export button. .EXAMPLE New-LumifyClassList -CourseTitle 'AZ104' This will look for the data.csv file (by default) in the downloads folder of the currently loggedon user, it will then convert it to a timestamped csv filename and will create a timstamped html document that will show in Chrome browser that can be easily printed to show who is on the course. These files are created in the same directory that the original csv was found in. .EXAMPLE New-LumifyClassList -ExportedFilePath e:\data1.csv -NoHtml -CourseTitle 'AZ104' This will look for a data1.csv file in the e:\ drive and will then show the details on screen. .PARAMETER ExportedFilePath This is the path to the exported csv file. This includes that path and the filename of the CSV exported file. .PARAMETER CourseTitle The course title will be presented above the HTML table, it is a mandatory parameter .PARAMETER NoHtml This will prevent this command creating a HTML document but will instead display the details about the attendees in a table on the screen. More information is shown on the screen than would be shown as the html output. .PARAMETER ShowPersonalDetails This will show the basic information and the email address and phone number for each student. Without this parameter the phone number and email address will not be shown. .INPUTS String CSV .OUTPUTS file HTML .NOTES General notes Created by: Brent Denny Change Notes Version When What ------- ---- ---- 1.0 15-May-2023 finished the script initial code 2.0 06-Jun-2023 Changed to height and alignment of the <TR> so that 12 students fit on one page 3.0 06-Jul-2023 Saves the original file with a timestamp filename, opens the browser if using html 4.0 05-Sep-2023 Rewritten to make the code simpler to read and understand also added updates to help 5.0 08-Sep-2023 Changed the output to have a course title and sorting of students by Location and then name If nohtml is used it does not rename the file, and if original data file not found it searches for the last one that was accessed. #> [CmdletBinding(DefaultParameterSetName='Html')] Param( [string]$ExportedFilePath = $env:HOMEDRIVE + $env:HOMEPATH + '\downloads\data.csv', [Parameter(Mandatory=$true)] [string]$CourseTitle, [Parameter(ParameterSetName = 'Html')] [switch]$ShowPersonalDetails, [Parameter(ParameterSetName = 'NoHtml')] [switch]$NoHtml ) # Check to see if the exported file exisits if (-not (Test-Path -Path $ExportedFilePath)) { $ExportedFileDir = ($ExportedFilePath | Split-Path).TrimEnd('\') $PreviousDataFiles = Get-ChildItem -Path $ExportedFileDir | Where-Object {$_.Name -like 'StudentList-*.csv'} | Sort-Object -Property LastAccessTime -Descending $ExportedFilePath = ($PreviousDataFiles | Select-Object -First 1 ).FullName } if (Test-Path -Path $ExportedFilePath){ # Check to see if the Exported file can be imported if ($ExportedFilePath -match 'csv$') { $Css = '<style>h1 {text-align:center} table {border:1px solid black;border-collapse:collapse;width:100%;} th {border:1px solid black;border-collapse:collapse;text-align:center;} tr {text-align:left;vertical-align:top;border:1px solid black;border-collapse:collapse;height:75px;vertical-align:top;} tr:first-child {vertical-align:left;height:40px;}</style>' $ExportedFileDir = ($ExportedFilePath | Split-Path).TrimEnd('\') $NewHtmlFilePath = $ExportedFileDir + '\' + (Get-Date -UFormat 'StudentList-%Y%m%d%H%M%S.html') $NewCsvFilePath = $ExportedFileDir + '\' + (Get-Date -UFormat 'StudentList-%Y%m%d%H%M%S.csv') try { if ($NoHtml -eq $false) { Rename-Item -Path $ExportedFilePath -NewName $NewCsvFilePath -Force -ErrorAction Stop } else { $NewCsvFilePath = $ExportedFilePath } } catch {Write-Warning "$ExportedFilePath was not able to be renamed"; break} $ExportedDataObj = Get-Content -Path $NewCsvFilePath | ConvertFrom-Csv -Header 'StudentName','Company','Phone','Email','TrainingSite','State','Code1','Code2','LabCode','LabPassword','AccountManager' | Select-Object -Skip 1 -Property 'StudentName', 'Company', 'Phone', 'Email', 'TrainingSite', 'State', 'Code1', 'Code2', 'LabCode', 'LabPassword', 'AccountManager' | Sort-Object -Property TrainingSite,StudentName if ($ShowPersonalDetails -eq $true) {$StudentInfo = $ExportedDataObj | Select-Object -Property StudentName,Company,TrainingSite,Email} else {$StudentInfo = $ExportedDataObj | Select-Object -Property StudentName,Company,TrainingSite,AccountManager,State} if ($NoHtml) { Clear-Host $ExportedDataObj | Select-Object -Property StudentName,Company,TrainingSite,Email,Phone,AccountManager | Format-Table } else { try { $StudentInfo | ConvertTo-Html -Head $Css -PreContent "<h1>Course Title: $CourseTitle</h1>" | Out-File -FilePath $NewHtmlFilePath -ErrorAction Stop } catch {Write-Warning "Could not save $NewHtmlFilePath file "; break} & "C:\Program Files\Google\Chrome\Application\chrome.exe" $NewHtmlFilePath } } else { Write-Warning "$ExportedFilePath is not in a CSV format please re-export the file from the Teams Instructor Portal again" break } } else { Write-Warning "$ExportedFilePath is not found" break } } |