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; }