/* This is the script to download current season players for import into the ASA spreadsheet
The only information required for players is:
Last name, First name, Zip Code, Gender & DOB
This uses AsaGroupType from PositionTypes to filter
*/
include ($_SERVER['DOCUMENT_ROOT']."/inc/config.php");
if ($ses_AuthLevel >= $AuthLevel_BoardTop) {
$TeamYear = $Year;
//header("Content-type: application/text");
//header("Content-Disposition: attachment; filename=AsaSeasonRoster.txt");
// Connect to Asset database
include ($_SERVER['DOCUMENT_ROOT']."/inc/sql_connect.php");
//$SeasonSearch = $TeamYear."R%";
$SeasonSearch = $TeamYear."R%";
// Create SQL statement
$sql = "SELECT Positions.*, People.*, PositionTypes.*, Groups.*, GroupDiv.*, GroupClass.*, PeopleExt.*
FROM Positions, PositionTypes, Groups, GroupDiv, GroupClass, People
LEFT JOIN PeopleExt
ON People.PeopleID = PeopleExt.PID
WHERE Positions.GroupID LIKE '$SeasonSearch' AND Groups.GroupDivAge = GroupDiv.GroupDivAge AND Positions.PositionID = PositionTypes.PositionID AND Groups.GroupID = Positions.GroupID AND Positions.PeopleID = People.PeopleID AND Groups.GroupClass = GroupClass.ClassCode
ORDER BY Groups.GroupID, LastName, FirstName ASC";
// Execute Query
include ($_SERVER['DOCUMENT_ROOT']."/inc/sql_execute.php");
$Eol = "\n";
if ($Format != "Print") {
$TableStart = "";
$TableEnd = "";
$RowStart = "";
$RowEnd = ";/n";
$Delim = ";";
} ELSE {
$TableStart = "
";
$RowStart = "";
$RowEnd = " |
";
$Delim = "";
}
$numrows = mysql_numrows($sql_result);
$PeopleIDCurrent = "";
$LineCount = 0;
//echo "Level = $ses_AuthLevel $Eol";
//echo "$numrows total position responses $Eol";
echo $TableStart;
echo $RowStart."ASA#".$Delim."LastName".$Delim."FirstName".$Delim."MiddleName".$Delim."Suffix".$Delim."Address".$Delim."City".$Delim."State".$Delim."Zip".$Delim."Phone".$Delim."Sex".$Delim."DOB".$Delim."Email".$Delim."DL".$Delim."DLSt".$Delim."DLExp".$Delim."PeopleID".$Delim."TeamID".$Delim."Date".$RowEnd;
// Format results by row
while ($row = mysql_fetch_array($sql_result))
{
$GroupID = $row["GroupID"];
$PositionID = $row["PositionID"];
$AsaMemberID = $row["AsaMemberID"];
$AsaGroupType = $row["AsaGroupType"];
$PeopleID = $row["PeopleID"];
$LastName = $row["LastName"];
$FirstName = $row["FirstName"];
$MiddleName = $row["MiddleName"];
if ($MiddleName == "~") { // MI not required, but may use if known. Tilde "~" is used in our DB for NONE
$MiddleName = "";
}
$NameSuffix = $row["NameSuffix"];
unset($Addr);
$Street = $row["Street"];
$Street2 = $row["Street2"];
if ($Street) {$Addr = $Street;}
if ($Street2) {$Addr .= " - ".$Street2;}
unset($PostCodePlus4);
unset($PostCode5);
$City = $row["City"];
$State = $row["State"];
$PostalCode = $row["PostalCode"];
$PostalCode5 = substr("$PostalCode", 0, 5);
$PostalCodePlus4 = substr("$PostalCode", 5, 4);
$PostalCode = $PostalCode5; // ASA only wants first 5
if (!$PostalCode) {
$PostalCode = "MISSING";
}
$Gender = $row["Gender"];
if ($Gender == "F") {
$Gender = "Female";
} elseif ($Gender == "M") {
$Gender = "Male";
} else {
$Gender = "MISSING";
}
unset($Phone1AC);
unset($Phone1Prefix);
unset($Phone1Suffix);
unset($Phone1Ext);
unset($Phone1Dsp);
$Phone1 = $row["Phone1"];
$Phone1AC = substr("$Phone1", 0, 3);
$Phone1Prefix = substr("$Phone1", 3, 3);
$Phone1Suffix = substr("$Phone1", 6, 4);
$Phone1Ext = substr("$Phone1", 10, 10);
if ($Phone1) {
$Phone1Dsp = "(".$Phone1AC.") ".$Phone1Prefix."-".$Phone1Suffix;
}
unset($BirthDsp);
unset($BirthYear);
unset($BirthMonth);
unset($BirthDay);
unset($BirthMonth1st);
$BirthDate = $row["BirthDate"];
$BirthDate = explode("-", "$BirthDate");
$BirthYear = $BirthDate[0];
$BirthMonth = $BirthDate[1];
$BirthMonth1st = substr("$BirthMonth", 0, 1);
if ($BirthMonth1st < 1) {
$BirthMonth = substr("$BirthMonth", 1, 1);
}
$BirthDay = $BirthDate[2];
if ($BirthYear > "0000") {
$BirthDsp = $BirthMonth."/".$BirthDay."/".$BirthYear;
} else {
$BirthDsp = "MISSING";
}
unset($EMailName);
$EMailName = $row["EMailName"];
if ($AsaGroupType == "P") {
echo $RowStart."$AsaMemberID".$Delim."$LastName".$Delim."$FirstName".$Delim."$MiddleName".$Delim."$NameSuffix".$Delim."$Addr".$Delim."$City".$Delim."$State".$Delim."$PostalCode".$Delim."$Phone1Dsp".$Delim."$Gender".$Delim."$BirthDsp".$Delim."$EMailName".$Delim."$PeopleDLID".$Delim."$PeopleDLST".$Delim."$PeopleDLExpDsp".$Delim."$PeopleID".$Delim."$GroupID".$Delim." ".$RowEnd;
}
}
echo $TableEnd;
include ($_SERVER['DOCUMENT_ROOT']."/inc/sql_close.php");
exit;
} ELSE {
echo "Only Board of Directors and above may view this file.";
}
?> |