NEBridge - Database Documentation: attendance tables

1 The attendance table has one record for each ACBL member known to have attended a tournament that was processed. The tournaments currently processed are:

1.1 All tournaments sponsored by District 25, including the Rainbow Weekend, the Cape Cod Sectional, and the GNT and NAP qualifying tournaments.

1.2 All sectionals sponsored by any of the eight New England units.

1.3 The three NABC tournaments.

2. The other_attendance table has one record for each player who attended one of the above tournaments who has no record on the players table. These are, at least for the most part, players who were not members of the ACBL at the time that they attended the tournament.

3. The D25_points table has one record for each player who has attended at least one of the events described in 1.1 for the calendar year.

4 .The current process for all except the NABC tournaments involves the following steps:

4.1 Sending an email to Keith Wells at the ACBL asking for the attendance files.

4.2 When the files are received, they are uploaded into ACBLScore.

4.3 The files are exported from ACBLScore as .csv files.

4.4 The attendance, other_attendance, and D25_points tables are backed up on the local system. Programs on the database menu are then run to upload the .csv files into the local database.

4.5 The results are spot-checked.

4.6 The attendance, other_attendance, and D25_points tables are backed up on the remote system. The programs are then run to upload the .csv files into the result data base. The results are spot-checked.

5. Obtaining the files:

5.1 Determine which tournaments you wish to ask for. The easiest way to do this is to use the ACBL calendar.

5.1.1 Direct your browser to ACBL.org.

5.1.2 Click on the “Tournaments” button near the top of the screen.

5.1.3 On the Tournaments page click on “Calendar/Results.”

5.1.4 On the “Find a Tournament” screen select the month and year that you need and select 25 in the “District” field using the pull-down menus (down-pointing arrowheads). Click on the “Search” button. The D25 tournaments (including sectionals) will be listed.

5.1.5 Record on paper or in a spreadsheet the cities and dates for each tournament that you need.

5.1.6 For the first tournament that you need:

5.1.6.1 Click on the underlined word “Info” on the corresponding line.

5.1.6.2 To obtain the sanction number for the event, place the cursor on the browser's address line and move to the end of the text. After “?sanction=” you will see the seven-digit sanction number. The sanction number for the 199er sectional in Stamford was 1805130. Record the number.

5.1.6.3 Return to the “Find a Tournament” screen using the page-back button in the browser.

5.1.7 Repeat step 5.1.6 for the other tournaments.

5.2 Compose an e-mail to Keith Wells.

5.2.1 For each tournament include the city, state, dates, and file name. The file name is Dxxxxxxx.LZH, where xxxxxxx is the sanction number. The file for the 199er sectional in Stamford is D1805130.LZH.

5.2.2 Send the email to Keith.Wells@ACBL.org.

5.3 He usually responds within a day or two.

6. Save the files you receive in the appropriate subfolder of District 25/Database/ACBLScore_LZH. There is one subfolder per year.

7. Use ACBLScore to process the files one at a time.

7.1 Start the ACBLScore program. Click on “Tourn Utilities” and then “Backup/Restore.”

7.2 Select option 3 to “Restore a database.” Click on the “OK” button.

7.3 Two warning screens will appear. Click on the “Yes” button on each of them.

7.4 Make sure that the folder path displayed on the screen is where the files were saved. If not, click on the “Browse” button. When the correct folder path appears, click on the “OK” button.

7.5 Select the first file that you want to process and click on the “Open” button.

7.6 When the “Select function” menu appears again, click on the “Cancel” button.

7.7 On the main screen click on “Data Base” and then “Reports/Export.”

7.8 There are no “Restrictions.” Just press F9 on this screen.

7.9 On the field selection screen use the space bar to select the eighteen fields shown in red above. They represent the first fourteen listed plus “Paid thru date,” “Total points,” “Recent points,” and “Email.” Click on the “OK” button when all have been selected.

7.10 Click on the “Yes” button to indicate that the first record should be field names.

7.11 Save the file in the appropriate folder. In the District 25/Database/ folder there is a folder for D25 and for each unit. In each of those folders is a folder for each year. The name of the file should correlate with the tournament's name or location. The file extension should always be .csv.

7.12 When the “Select report type” menu appears again, click on the “Cancel” button.

8. In a text editor open the BDB005A.php program.

8.1 Find the lines that begin with “$File_Name,” “$cal_year,” and “unit.” This is done by changing the last parameter of the CondRequest function, i.e., the portion between the last comma and the last parenthesis. If it is currently in quotes, leave it in quotes. Single and double quotes are equivalent. Change the values that match the name, year, and unit that you just exported from ACBLScore.

8.2 DO NOT change the value of the “abbrev” field. It should always be “xxx.”

8.3 Make sure that the value for the “$Tab_File” field is “Y.”

8.4 The other fields are not used.

8.5 Save the file.

9. Access BridgeDatabaseMenu.php on the local system.

9.1 If you have not done so yet, create a record on the tournament table. This process is described here.

9.2 Make backup copies of attendance, other_attendance, and (if you are processing a D25 file) D25_points. This process is described here. The names of the three files to be copied are shown in red on screen #BDB071A. 

9.3 On BridgeDatabaseMenu.php select the option to “Process a File of Results (with City).” It is in the first column.

9.4 Check all the entries on the screen. If they appear OK, click on the “Clear xxx” button. Then click on the “Process File” button.

9.5 The screen will appear again with the results. If any records were inserted into other_attendance:

9.5.1 Click on the “Menu” button.

9.5.2 Click on “Examine other_attendance for xxx.” It is in the first column.

9.5.3 If more names are listed than expected, go back to step 9.3 and be sure to click on “Clear xxx.”

9.5.4 Because the results file includes player numbers, almost never does a name on the screen appear that should actually be in the player database. However, the field on the right does allow you to check by listing all players with a specified last name.

9.5.5 If you find a problem, you can usually fix the .csv file in a spreadsheet or text editing program.

9.6 If the file seems to be OK, on BridgeDatabaseMenu.php select the option to “Process a File of Results (with City)” again.

9.7 Change the “Abbreviation” prompt to match what you entered when you created the record in the tournament table.

9.8 Click on the “Process File” button again. Make sure that the numbers of records inserted agrees with the last successful running of the program.

9.9 Spot-check the results.

10. Access BridgeDatabaseMenu.php on the remote system. Close the tab that was open on the local system to minimize the chance for confusion.

10.1 In the File Manager transfer BDB005A.php to public_html. A description of this process can be found here.

10.2 Transfer the .csv file that contains the results to the equivalent folder on the remote system.

10.3 Execute steps 9.1 through 9.9 on the remote system.

11. Keith Wells may be willing to send the NABC files, too. If not, here is a pretty good alternative.

11.1 Find the tournament on the ACBL calendar. Click on “Results.”

11.2 Click on the phrase "Click here for Masterpoint Winners List".

11.3 Select the text for the entire list but nothing else on the page. Press Ctrl C to copy it to the clipboard.

11.4 Open a text editor and create an empty page. Paste the list using Ctrl V.

11.5 Save the file as a text file in the folder for the appropriate year in District 25/Database/NABC/.

11.6 Open DBD005A in a text editor as before. Change the values of $File_Name, $cal_year, and $unit as in step 8.1. The value for $unit should be “NABC.”

11.7 The following fields are different from step 8:

11.7.1 $pos1 should be “12.”

11.7.2 $pos2 should be “32.”

11.7.3 $Tab_File should be “N.”

11.7.4 $pasted should be “Y.”

11.8 The file should be processed as described in steps 8 and 9. Not the following differences:

11.8.1 The file will take much longer to process than files for sectionals or regionals.

11.8.2 The list of players who are not on the database will be much longer.

11.8.2.1 Many of those are clearly foreign players whose absence from the database is to be expected.

11.8.2.2 The list on the ACBL website does not contain player numbers. Changes of address or name may make it difficult for the program to find some players. In some cases it may be worth the effort to edit the text file so that the program can find them.