F1 Slingshot Attendance Updates

The F1 export contains two tables with attendance data: Attendance, and Groups_Attendance. The former is more structured for attendance that is entered via check-in applications, while the former is typically for manually recorded attendance entered by a small group leader.

The F1 slingshot client exports a single .csv file with attendance entries for bulk import into Rock.

The MDB client does a union between these two tables. One specific item that is present in the Attendance table is an Attendance Id value, which is missing and has no analog in the Groups_Attendance table. Therefore, those values are disregarded, and instead, a new attendance id value is generated by the F1Attendance.cs routine, in which certain values are gathered from the tables, run through a hashing routine, and then converted back to an integer.

In order to make the union possible, there was a WHERE clause on the query that read FROM [Attendance] where Check_In_Time is not null. This had the net effect of excluding any attendance that was added for F1 Participants manually, since Check_In_Time was a value that was entered by the check-in stations. This presumably helped to avoid redundant Attendance ID values

For one Sparkability client, this resulted in nearly 600k attendance records being lost.

In order to limit the number of attendance records that are lost, the query was updated to use Start_Date_Time rather than Check_In_Time. Also, rather than entirely rejecting the Attendance ID value, Attendance_Id is kept for records in the Attendance table, and a new Attendance Id value generated for the Groups_Attendance table, consisting of the Individual ID + GroupId + a day count since 12/30/1899 for the Attendance Created Date value.

In testing, this resulted in, rather than losing nearly 600k records, the presence of 118 duplicate Attendance_Id values in the resulting Attendance.csv file.