public function searchRegistrationByTeamId($teamId)
{
// the SQL query to be executed on the database
$query = "SELECT REGISTRATION_ID, CONVERT(VARCHAR(10),REGISTRATION_ACTIVITY_DATE, 101) AS REGISTRATION_ACTIVITY_DATE,\r\n TOURNAMENT_ID, TOURNAMENT_NAME, CONVERT(VARCHAR(10), TOURNAMENT_DATE, 101) AS TOURNAMENT_DATE, \r\n Right(IsNull(Convert(Varchar,TOURNAMENT_BEGIN_TIME,100),''),7) AS TOURNAMENT_BEGIN_TIME, \r\n Right(IsNull(Convert(Varchar,TOURNAMENT_END_TIME,100),''),7) AS TOURNAMENT_END_TIME, \r\n TOURNAMENT_STREET, \r\n TOURNAMENT_CITY, \r\n TOURNAMENT_STATE_CODE, \r\n TOURNAMENT_ZIP,\r\n TEAM_ID, TEAM_NAME,\r\n SPORT_TYPE_ID, SPORT_TYPE_NAME\r\n FROM dbo.REGISTRATION reg JOIN dbo.TEAM t on (reg.REGISTRATION_TEAM_ID = t.TEAM_ID) \r\n JOIN dbo.TOURNAMENT tour on(reg.REGISTRATION_TEAM_TOURNAMENT_ID = tour.TOURNAMENT_ID)\r\n join dbo.SPORT_TYPE sp ON (sp.SPORT_TYPE_ID = tour.TOURNAMENT_SPORT_TYPE_ID) WHERE t.TEAM_ID =" . $teamId;
$results = executeQuery($query);
$regList = new ArrayObject();
foreach ($results as $result) {
$regVO = new RegistrationVO();
$regVO->set_registrationId($result['REGISTRATION_ID']);
$regVO->set_activityDate($result['REGISTRATION_ACTIVITY_DATE']);
$tournament = new Tournament();
$tournament->set_tournamentName($result['TOURNAMENT_NAME']);
$tournament->set_tournamentDate($result['TOURNAMENT_DATE']);
$tournament->set_tournamentBeginTime($result['TOURNAMENT_BEGIN_TIME']);
$tournament->set_tournamentEndTime($result['TOURNAMENT_END_TIME']);
$tournament->set_sportTypeId($result['SPORT_TYPE_ID']);
$tournament->set_sportTypeName($result['SPORT_TYPE_NAME']);
$tournament->set_tournamentStreet($result['TOURNAMENT_STREET']);
$tournament->set_tourcenameCity($result['TOURNAMENT_CITY']);
$tournament->set_tournamentState($result['TOURNAMENT_STATE_CODE']);
$tournament->set_tournamentZip($result['TOURNAMENT_ZIP']);
$team = new TeamVO();
$team->set_teamId($result['TEAM_ID']);
$team->set_teamName($result['TEAM_NAME']);
$regVO->set_tournament($tournament);
$regVO->set_team($team);
$regList->append($regVO);
}
return $regList;
}
public function getTournaments($stype, $tname)
{
$query = <<<STR
SELECT TOURNAMENT_NAME,
CONVERT(VARCHAR(10), TOURNAMENT_DATE, 101) AS TOURNAMENT_DATE,
Right(IsNull(Convert(Varchar,TOURNAMENT_BEGIN_TIME,100),''),7) AS TOURNAMENT_BEGIN_TIME,
Right(IsNull(Convert(Varchar,TOURNAMENT_END_TIME,100),''),7) AS TOURNAMENT_END_TIME,
SPORT_TYPE_ID,
SPORT_TYPE_NAME,
TOURNAMENT_STREET,
TOURNAMENT_CITY,
TOURNAMENT_STATE_CODE,
TOURNAMENT_ZIP
FROM dbo.TOURNAMENT
INNER JOIN dbo.SPORT_TYPE
ON SPORT_TYPE_ID = TOURNAMENT_SPORT_TYPE_ID
WHERE 0=0
STR;
if ($stype == '%') {
$query .= <<<STR
AND SPORT_TYPE_NAME IS NOT NULL
STR;
} else {
if (strpos($stype, '%') !== FALSE) {
$query .= <<<STR
AND UPPER(SPORT_TYPE_NAME) LIKE UPPER('{$stype}')
STR;
} else {
if ($stype != NULL) {
$query .= <<<STR
AND UPPER(SPORT_TYPE_NAME) = UPPER('{$stype}')
STR;
}
}
}
if ($tname == '%') {
$query .= <<<STR
AND TOURNAMENT_NAME IS NOT NULL
STR;
} else {
if (strpos($tname, '%') !== FALSE) {
$query .= <<<STR
AND UPPER(TOURNAMENT_NAME) LIKE UPPER('{$tname}')
STR;
} else {
if ($tname != NULL) {
$query .= <<<STR
AND UPPER(TOURNAMENT_NAME) = UPPER('{$tname}')
STR;
}
}
}
if ($tname == NULL && $stype == NULL) {
$query .= <<<STR
AND TOURNAMENT_NAME IS NOT NULL
AND SPORT_TYPE_NAME IS NOT NULL
STR;
}
// print_r($query);
$results = executeQuery($query);
$locList = new ArrayObject();
foreach ($results as $result) {
$locations = new Tournament();
$locations->set_tournamentName($result['TOURNAMENT_NAME']);
$locations->set_tournamentDate($result['TOURNAMENT_DATE']);
$locations->set_tournamentBeginTime($result['TOURNAMENT_BEGIN_TIME']);
$locations->set_tournamentEndTime($result['TOURNAMENT_END_TIME']);
$locations->set_sportTypeId($result['SPORT_TYPE_ID']);
$locations->set_sportTypeName($result['SPORT_TYPE_NAME']);
$locations->set_tournamentStreet($result['TOURNAMENT_STREET']);
$locations->set_tourcenameCity($result['TOURNAMENT_CITY']);
$locations->set_tournamentState($result['TOURNAMENT_STATE_CODE']);
$locations->set_tournamentZip($result['TOURNAMENT_ZIP']);
$locList->append($locations);
}
return $locList;
}
