Based on the great post and script from Wayne Sheffield I decided to try again pre-printing the SpeedPASS for everyone this year. I dislike the line of shame and I dislike having attendees wait in line for more than a couple minutes, printing them at least reduces the pain.
I wanted a little more from the script though; the bbility to only print “net new” so that we didn’t have to wait until the last minute to print
Here’s what the instructions look like on our Trello board:
Getting the ability to print only for those who registered since the last batch required a hack. Wayne uses an export to get the InvoiceID to allow sorting by name but that export doesn’t have a date or other ID that can be used for sequence. There is an “attendee report” that does have registrationid. If you join those two, then you have enough to accomplish the task.
I’ll try to write another post soon about improving the entire process, but just in case I’ll call out to PASS to say this is an area worth polling and understanding the use cases to see what can be done better. It’s important to organizers and sponsors that the the process work well. A small change that would help would be adding RegistrationID to the attendee export.
Anyway, here’s my revision/version of what Wayne wrote:
<#
.SYNOPSIS
This script will merge the individual SQL Saturday SpeedPASS files for attendees into one PDF file, sorted by name.
.DESCRIPTION
This script will merge the individual SQL Saturday SpeedPASS files for attendees into one PDF file, sorted by name.
It automatically filters for just people attending the event, and then optionally filters based on their lunch status.
Those with a comped lunch status are usually speakers and volunteers, and can be printed ahead of time. This version allows you
to create a PDF containing registrations after a given registration id (so you can only do the new ones and not have to reprint all)
.PARAMETER LunchStatus
The lunch status of the attendee. This is how we separate speakers / volunteers (with comped lunches) from all other attendees.
.PARAMETER ExcelInvoiceIDRegistrationFile
The location of the downloaded Registrations Excel file for the SQL Saturday Event ("SQLSaturday Event Registration.xlsx"").
.PARAMETER PdfSharpPath
The path containing the binaries for the PdfSharp utility. (PDFSharp.dll)
.PARAMETER $ExcelRegidRegistrationFile
The location.name of the excel file downloaded from the reports page ("Event Registrations.xlsx")
.PARAMETER $SpeedPASSFolder
The folder containing the original PDF's (and everything else if you use the defaults)
.PARAMETER $StartAtRegID
This is the starting point for filtering/combining PDF's based on their registration id. It's a way to "process all since".
.NOTES
Directions:
Save this script to C:SpeedPASS.
Get the PDFSharp-GDI.DLL. Source code is at https://github.com/empira/PDFsharp. Compile with VS2017, or install the nuget package. Copy the PDFShpart-GDI.DLL to the $SpeedPASSFolder
Install the Import-Excel module: Install-Module ImportExcel (requires running Powershell with Administrator rights).
Log in to the SQLSaturday admin site
Get the RegistrationInvoice File
Navigate to Event Settings > Manage Registrations.
Export to $SpeedPASSFolder as Excel.
Get the RegistrationRegID File
Navigate to Reports, select "Event Registrations:
Export to $SpeedPASSFolder as Excel
Download all the SpeedPASS PDF's
(todo)Navigate to Event Settings > Manage SpeedPASS.
Generate all SpeedPass (in the week prior to your event, this is done automatically every 4 hours).
Download and save to disk in the $SpeedPASSFolder
Run this script. For the way we do it in Orlando:
Friday Morning (D-1)
Do Steps 3 thru 6
Run .SpeedPASSProcessor.ps1 -SpeedPASSFolder 'C:SpeedPASS' -LunchStatus AllExceptComped
Run .SpeedPASSProcessor.ps1 -SpeedPASSFolder 'C:SpeedPASS' -LunchStatus Comped
Generates 2 SpeedPASSMerge*.pdf files
Print those 2 files
Fri Night (D-1, as late as possible)
Do Steps 3 thru 5
Get the max RegID from the AllExceptComped file from the morning, use it as X in the next line
Run .SpeedPASSProcessor.ps1 -SpeedPASSFolder 'C:SpeedPASS' -LunchStatus AllExceptComped -StartAtRegID X
Print the new file, insert them into the first set
Sat morning (if needed
Same as Fri Night
.LINK
http://www.kendalvandyke.com/2013/09/practical-powershell-merge-sqlsaturday.html
.LINK.LINK http://pdfsharp.codeplex.com/releases .LINK https://www.powershellgallery.com/packages/ImportExcel .LINK https://blog.waynesheffield.com/wayne/archive/2019/05/working-with-sqlsaturday-speedpasses-revamped/ .LINK https://www.powershellgallery.com/packages/ImportExcel/5.4.0 .EXAMPLE .SpeedPASSProcessor.ps1 -SpeedPASSFolder 'C:SpeedPASS' -LunchStatus All This example produces one PDF file consisting of all of the PDF files for the attending registrations. .EXAMPLE .SpeedPASSProcessor.ps1 -SpeedPASSFolder 'C:SpeedPASS' -LunchStatus AllExceptComped This example produces one PDF file consisting of all of the PDF files for the attending registrations where their lunch has not been comped. .EXAMPLE .SpeedPASSProcessor.ps1 -SpeedPASSFolder 'C:SpeedPASS' -LunchStatus Comped This example produces one PDF file consisting of all of the PDF files for the attending registrations where their lunch has been comped. .EXAMPLE .SpeedPASSProcessor.ps1 -SpeedPASSFolder 'C:SpeedPASS' -LunchStatus Comped -StartAtRegID 400,123 This example produces one PDF file consisting of all of the PDF files for the attending registrations where their lunch has been comped AND their registrationID is greater than 400,123. This allows you to only print "new" speedpasses by checking the maxid already printed > Param( [Parameter(Mandatory=$false)] [String] $ExcelInvoiceIDRegistrationFile = "SQLSaturday Event Registration.xlsx", [Parameter(Mandatory=$false)] [String] $ExcelRegidRegistrationFile = "Event Registrations.xlsx", [Parameter(Mandatory=$false)] [String] $PdfSharpPath = "PDFSharp-gdi.dll", [Parameter(Mandatory=$false)] [String] $SpeedPASSFolder = "C:SpeedPASS", [Parameter(Mandatory=$true)] [ValidateSet("All","AllExceptComped","Comped")] [String] $LunchStatus, [Parameter(Mandatory=$false)] [String] $SpeedPASSDownloadZIP = "SpeedPASS.Zip", [Parameter(Mandatory=$false)] [int] $StartAtRegID = 1 ) we need the slash! if ($SpeedPASSFolder.Substring($SpeedPASSFolder.Length-1,1) -ne "") { $SpeedPASSFolder = $SpeedPASSFolder + "" } make it all local cd $SpeedPASSFolder Load the PdfSharp Assembly Add-Type -Path $PdfSharpPath; Create the filter. Always get just those planning to attend. $Filter = '($_."Attend Status" -eq "Planning to Attend")'; Add the desired lunch status to the filter. if ($LunchStatus -eq 'All') {} elseif ($LunchStatus -eq 'AllExceptComped') {$Filter += ' -and ($_."Lunch Status" -ne "Comped by Event Team")'} elseif ($LunchStatus -eq 'Comped') {$Filter += ' -and ($_."Lunch Status" -eq "Comped by Event Team")'}; Pump the filter into a script block $SBFilter = [scriptblock]::Create($Filter); remove all previous attendee pdfs and unzip again remove-item ($SpeedPASSFolder + ".pdf") -Force -Exclude SpeedPASSMerge.pdf Expand-Archive -Path $SpeedPASSDownloadZIP -DestinationPath $SpeedPASSFolder -Force get both files because we need the invoiceid from one and regid from the other $Invoices = Import-Excel $ExcelInvoiceIDRegistrationFile | SELECT email, InvoiceID -ExpandProperty InvoiceID; $Registrations = Import-Excel $ExcelRegidRegistrationFile -StartRow 2 | Where-Object RegID -GT $StartAtRegID | Where-Object $SBFilter | Sort-Object "Last Name", "First Name" | Select Regid, "Email Address" $ThruRegID = $Registrations | Measure-Object -Property RegID -Maximum Define the export path - the parent of the root path. $OutputPath = $SpeedPASSFolder $OutputFile = $OutputPath + 'SpeedPassMerge_' + (Get-Date -Format o).ToString().Replace('-','').Replace(':','').Replace('T','').Substring(0,15)+ "_StartatRegID" + $StartAtRegID + 'ThruRegID' + $ThruRegID.Maximum + "_"+ $LunchStatus + '.PDF'; if (Test-Path $OutputFile) {Remove-Item $OutputFile}; Create the output object. $output = New-Object PdfSharp.Pdf.PdfDocument; $PdfReader = [PdfSharp.Pdf.IO.PdfReader]; $PdfDocumentOpenMode = [PdfSharp.Pdf.IO.PdfDocumentOpenMode]; Define counter for the progress meter. $counter = 0; foreach ($Invoice in $Invoices) foreach ($Registration in $Registrations) { $Invoice = $Invoices | Where-Object "email" -EQ $Registration."email address" | select InvoiceID -ExpandProperty InvoiceID $ThisSpeedPass = "$SpeedPASSFolder$Invoice.PDF" if (Test-Path $ThisSpeedPass) # Check to see if the file exists. { #"Processing $SpeedPASSFolder$Invoice.PDF" # Load the file and add it to the output object. $input = New-Object PdfSharp.Pdf.PdfDocument; $input = $PdfReader::Open($ThisSpeedPass, $PdfDocumentOpenMode::Import); $input.Pages | %{$output.AddPage($_)} | Out-Null; } else { #Display File not found message "File not found: $ThisSpeedPass, Registration email: $($Registration."email address")" } # Increment the counter, and display the progress meter. $counter += 1; $progressPct = $counter / $Invoices.Count * 100; Write-Progress -Activity "Merge Speedpass Files" -Status "$progressPct% Complete:" -PercentComplete $progressPct; } Turn off the progress meter. Write-Progress -Activity "Merge Speedpass Files" -Completed; Notify user that file save is in progress. Write-Host "Saving Destination File: $OutputFile"; Write-Output "Registrations Merged: $Counter" $output.Save($OutputFile); Notify user that we are finished. "Completed";
