// This is the script to download current season Board staff for import into the ASA spreadsheet
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."B%";
// Create SQL statement
$sql = "SELECT Positions.*, People.*, PositionTypes.*, Groups.*, PeopleExt.*
FROM Positions, PositionTypes, Groups, People
LEFT JOIN PeopleExt
ON People.PeopleID = PeopleExt.PID
WHERE Positions.GroupID LIKE '$SeasonSearch' AND Positions.PositionID = PositionTypes.PositionID AND Groups.GroupID = Positions.GroupID AND Positions.PeopleID = People.PeopleID AND PositionTypes.AsaGroupType = 'B'
ORDER BY LastName ASC, FirstName ASC, Groups.GroupID DESC, PositionTypes.PositionOrder DESC";
// 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;
unset($PeopleIDTmp);
// Format results by row
while ($row = mysql_fetch_array($sql_result))
{
unset($MemberType);
$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 = "NONE";
} ELSEIF ($MiddleName == "") {
$MiddleName = "MISSING";
}
$NameSuffix = $row["NameSuffix"];
$PeopleDLID = $row["PeopleDLID"];
if (!$PeopleDLID) { $PeopleDLID = "MISSING"; }
$PeopleDLST = $row["PeopleDLST"];
if (!$PeopleDLST) { $PeopleDLST = "MISSING"; }
unset($DLExpDateDsp);
unset($DLExpYear);
unset($DLExpMonth);
unset($DLExpDay);
unset($DLExpMonth1st);
unset($PeopleDLExpDateString);
if (!$PeopleDLID || $PeopleDLID == "MISSING") {
$DLExpDateDsp = "MISSING";
} else {
$PeopleDLExp = $row["PeopleDLExp"];
$PeopleDLExpDateString = preg_replace('/[^0-9]/', '', $PeopleDLExp);
$PeopleDLExp = explode("-", "$PeopleDLExp");
$DLExpYear = $PeopleDLExp[0];
$DLExpMonth = $PeopleDLExp[1];
$DLExpMonth1st = substr("$DLExpMonth", 0, 1);
if ($DLExpMonth1st < 1) {
$DLExpMonth = substr("$DLExpMonth", 1, 1);
}
$DLExpDay = $PeopleDLExp[2];
$PeopleDLExp = $DLExpYear.$DLExpMonth.$DLExpDay;
$DLExpDateDsp = $DLExpMonth."/".$DLExpDay."/".$DLExpYear;
/* if ($SanDateStamp < $PeopleDLExpDateString) {
$DLExpDateDsp = $DLExpMonth."/".$DLExpDay."/".$DLExpYear;
} else {
$DLExpDateDsp = "EXPIRED";
} */
}
unset($Addr);
$Street = $row["Street"];
$Street2 = $row["Street2"];
if ($Street) {$Addr = $Street;}
if ($Street2) {$Addr .= " - ".$Street2;}
unset($PostCodePlus4);
unset($PostCode5);
$City = $row["City"];
if (!$City) {
$City = "MISSING";
}
$State = $row["State"];
if (!$State) {
$State = "MISSING";
}
$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) {
$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;
}
if (!$Phone1) {
$Phone1 = "MISSING";
}
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($Mismatch);
if($BirthMonth.$BirthDay != $DLExpMonth.$DLExpDay) {
if($PeopleID != "4531"){ // Special License Exception for Nettles
$Mismatch = "BAD";
}
}
$EMailName = $row["EMailName"];
if (!$EMailName) {
$EMailName = "MISSING";
}
// * * * * * * * * * * * * * * * * * * * * * * * BEGIN PRINT AREA * * * * * * * * * * * * * * * * * * * * * * *
if ($PeopleID != "910") { // Exclude Volunteer Needed
if ($PeopleIDTmp != $PeopleID) {
$PeopleIDTmp = $PeopleID;
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."$DLExpDateDsp".$Delim."$PeopleID".$Delim."$GroupID".$Delim."$Mismatch".$RowEnd;
}
}
}
echo $TableEnd;
include ($_SERVER['DOCUMENT_ROOT']."/inc/sql_close.php");
exit;
} ELSE {
echo "Only Board of Directors and above may view this file.";
}
?> |