SQLSaturday SpeedPASSes

I’ve been working with running SQLSaturdays in the Richmond, VA area for several years now. It seems that every year, I end up blogging something new. Frequently, this ends up being a script of some kind to help me out. This post follows this theme by introducing a script for working with SQLSaturday SpeedPASSes.

The SpeedPASS for SQLSaturday is used for many things. It has the attendee’s admission ticket, lunch ticket, name badge and raffle tickets for all of the vendors. What most organizers do for their local SQLSaturday is to do whatever it takes to entice the attendee to pre-print their ticket. However, it seems that there are always people that forget, or didn’t realize that they should have done this. Last year, at our event, we had about 40% of the people that needed their tickets printed out for them on the spot. This causes lines waiting as we hunt up their SpeedPASS on the registration site, or find it in an Excel document to get the link to those that have already been downloaded.

I mentioned getting the link in an Excel document. This is because PASS generates the SpeedPASS into a PDF file that has a GUID as its filename. Specifically, it is the “Invoice ID” guid in the attendees registration. The Excel document that the site administrators can download of the registrations has this, along with their name. So we look up the name, find the invoice id, and match this to the PDF file for that person. Open it up, and print it out. One by one. Does this sound like a cursor to you also?

Changing things up

This year, our organizing committee decided that this was too much hassle, and that we were going to print out the SpeedPASSes for everyone ahead of time and to have it ready for them as they walk in the door. What we have accomplished is to shift the line from waiting to print out their SpeedPASS, to finding their SpeedPASS. However, we have hundreds of these SpeedPASSes to deal with. Now we have to get all of the PDFs, open them up (one-by-one), and print them out. After they have all been printed, they need to be sorted by last name. Manually.

Hello… this is insane. Surely, there has to be a better way. So I started looking.

A better way…

My good friend, Mr. Google, found this post by Kendal Van Dyke. This post has a PowerShell script that will download and merge all of the PDFs for selected attendees into one big PDF. This enables printing out all of the SpeedPASSes at once, instead of one-by-one. However, I have a couple of problems with this script in it’s current form. First, the instructions for how to get the information from the SQLSaturday admin site have changed (they did do a major web site change last year). Secondly, it downloads all of the PDFs one-by-one, and puts them into a temporary directory, where they are all merged together. In file-name order. Not alphabetically. This means that the manual sorting is still necessary. But hey – it’s PowerShell. Surely we can come up with a way to do this sorting for us!

So I decided to re-write this script to suit my needs. Kendal’s script downloads the SpeedPASS PDF files one-by one. However, the admin site allows us to download all of them in one zip file. I like this approach better. I ended up making two major changes to the script. The first change requires pre-downloading and extracting all of the SpeedPASS files. The second change is to get them to merge alphabetically. Like Kendal’s script, this uses the PDFSharp assemblies. This requires using PowerShell 3.0 or higher.

The new instructions for using this script

1. Download and save the PDFSharp Assemblies from http://pdfsharp.codeplex.com/releases.
   1. Unblock the zip file (right-click the file, select properties, and click “Unblock”. See Kendal’s post for pictures showing you how to do this), and save the files in the zip file to your desired location.
   2. Put this location in the $PdfSharpPath variable in the script.
2. Log into the SQLSaturday admin site and navigate to Event Settings > Manage SpeedPASS.
   1. Cick the button to “Generate All” SpeedPASSes.
   2. Click the button to “Download SpeedPASSs” and save to disk.
   3. Extract the files from the zip file.
   4. Put this location in the $SpeedPASSPDFPath variable in the script.
3. Go to Event Settings > Manage Registrations.
   1. Click the “Export to Excel” button.
   2. Save to disk.
4. Open the Excel file and Sort as desired (I used lastname / firstname).
   1. If you only want to do the speakers, filter for complimentary lunches or not.
5. Copy the InvoiceID column from Excel to a text file and save the file.
   1. Put the complete filename for this file in the $SpeedPASSUrlPath variable in the script.
6. Ensure that the following variables are set. Run the script to merge the PDF files into a single PDF.
   1. Set the $PdfSharpPath variable to the location of PDFSharp DLL (from #1 above).
   2. Set the $SpeedPASSPDFPath variable to the location where the extracted PDF files are (from #2 above).
   3. Set the $SpeedPASSUrlPath variable to the location of the text file with the InvoiceID values in it (from #5 above).
7. Finally, open the merged PDF file. You will see that they are ordered by how you ordered the Excel spreadsheet. When you’re ready, print them out.

The PowerShell script.

Some of it is exactly how Kendal’s is, but the Merge-PDF2 function is a re-write of his Merge-PDF function to handle the differences from above.

<#
Source: http://www.kendalvandyke.com/2013/09/practical-powershell-merge-sqlsaturday.html
Directions:
1. Download and save the PDFSharp Assemblies from http://pdfsharp.codeplex.com/releases. 
    Unblock zip (see above link), and save files to a location. 
    Put this location in the $PdfSharpPath variable below.
1. Log into admin site & navigate to Event Settings > Manage SpeedPASS. 
    Generate all SpeedPass. 
    Download and save to disk. 
    Extract zip file. Put this location in the $SpeedPASSPDFPath variable below.
2. Go to Event Settings > Manage Registrations. 
    Export to Excel. 
    Save to disk
3. Open Excel file and Filter for comp or not. Sort as desired (lastname / firstname?)
4. Copy the column with the InvoiceID from Excel to a text file and save. 
    Put the full path & filename of this file in the $SpeedPASSUrlPath variable below.
5. Run the script to merge the PDF files into a single PDF file.
    a. Set the $PdfSharpPath variable to the location of PDFSharp DLL (from #1 above)
    b. Set the $SpeedPASSUrlPath variable to the location of the text file with the InvoiceID values in it.
    c. Set the $SpeedPASSPDFPath variable to the location where the extracted PDF files are.
6. Print the merged PDF.
#>
# Change the following path to where you've put the PDFsharp DLL  
$PdfSharpPath = 'D:\PDFsharp\GDI+\PdfSharp.dll'
 
# Change the following path to where the TXT file with SpeedPASS URLs is
$SpeedPASSUrlPath = "I:\SQL Saturday\610\SpeedPass\SpeedPass-Comp.txt"
#$SpeedPASSUrlPath = "I:\SQL Saturday\610\SpeedPass-NoComp.txt"
# Change the following path to where you have the extracted SpeedPasses at
$SpeedPASSPDFPath = 'I:\SQL Saturday\610\SpeedPASS\PDF\'
# Load the PdfSharp Assembly
Add-Type -Path $PdfSharpPath
 
$OutputPath = [System.IO.Path]::ChangeExtension($SpeedPASSUrlPath, 'PDF')
 
Function Merge-PDF2 {
<#
Based on Merge-PDF.
Get the PDF Files in the order that the InvoiceID is within the text file.
#>
    Param($SourcePath, $DestinationFile)
    
    #Delete $DestinationFile if it exists
    if (Test-Path $DestinationFile) {
        Remove-Item $DestinationFile
    }
    $output = New-Object PdfSharp.Pdf.PdfDocument;
    $PdfReader = [PdfSharp.Pdf.IO.PdfReader];
    $PdfDocumentOpenMode = [PdfSharp.Pdf.IO.PdfDocumentOpenMode];
    Get-Content -Path $SpeedPASSUrlPath | Where-Object {
        -not [String]::IsNullOrEmpty($_)
    } | ForEach-Object {
        Write-Host "Source File: $SourcePath$_.PDF";
        $input = New-Object PdfSharp.Pdf.PdfDocument;
        $input = $PdfReader::Open("$SourcePath$_.PDF", $PdfDocumentOpenMode::Import);
        $input.Pages | %{$output.AddPage($_)} | Out-Null;
    }
    Write-Host "Saving Destination File: $DestinationFile";
    $output.Save($DestinationFile);
}
 
try {
    # Call the merge function 
    Merge-PDF2 -SourcePath $SpeedPASSPDFPath -DestinationFile $OutputPath;
    Write-Host "Merge Complete!"
    Write-Host "The merged SpeedPASS file is located at $OutputPath"
 
} catch {
    Write-Host "Uh-oh, something went wrong..."
    Write-Host 'Review $Error to figure out what happened'
}
 
Remove-Variable -Name OutputPath

There you have it. Working with SQLSaturday SpeedPASSes to generate one PDF for all selected SpeedPASSes sorted by name. Eazy-Peazy.

You might be wondering what is up with that picture up above. Yes, it’s a Dr. Who picture. My friend, Grant Fritchey, challenged folks to incorporate this picture into a technical post – read his challenge here. I hope that I did it justice.

The post Working with SQLSaturday SpeedPASSes appeared first on Wayne Sheffield.