private function _getUserInfoByTeam($teamId)
{
$columns = 'U.id AS user_id, nick, email, picture';
$fromTable = $this->_websoccer->getConfig('db_prefix') . '_user AS U INNER JOIN ' . $this->_websoccer->getConfig('db_prefix') . '_verein AS C ON C.user_id = U.id';
$result = $this->_db->querySelect($columns, $fromTable, 'C.id = %d', $teamId);
$user = $result->fetch_array();
$result->free();
if ($user) {
$user['picture'] = UsersDataService::getUserProfilePicture($this->_websoccer, $user['picture'], $user['email'], 120);
}
return $user;
}
/**
* Provides open match requests.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @param int $startIndex Fetch start index.
* @param int $entries_per_page Number of items to fetch.
* @return array list of found requests incl. team and user summary.
*/
public static function getMatchRequests(WebSoccer $websoccer, DbConnection $db, $startIndex, $entries_per_page)
{
$columns = array("R.id" => "request_id", "R.matchdate" => "matchdate", "R.reward" => "reward", "C.name" => "team_name", "C.id" => "team_id", "U.id" => "user_id", "U.nick" => "user_nick", "U.email" => "user_email", "U.picture" => "user_picture");
$fromTable = $websoccer->getConfig("db_prefix") . "_youthmatch_request AS R";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_verein AS C ON C.id = R.team_id";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_user AS U ON U.id = C.user_id";
$whereCondition = "1=1 ORDER BY R.matchdate ASC";
$requests = array();
$limit = $startIndex . "," . $entries_per_page;
$result = $db->querySelect($columns, $fromTable, $whereCondition, null, $limit);
while ($request = $result->fetch_array()) {
$request["user_picture"] = UsersDataService::getUserProfilePicture($websoccer, $request["user_picture"], $request["user_email"]);
$requests[] = $request;
}
$result->free();
return $requests;
}
/**
*
* @param WebSoccer $websoccer
* @param DbConnection $db
* @param string $whereCondition
* @param array $parameters
* @param string $limit
* @return array list of matches.
*/
public static function getMatchesByCondition(WebSoccer $websoccer, DbConnection $db, $whereCondition, $parameters, $limit)
{
$fromTable = self::_getFromPart($websoccer);
// select
$columns['M.id'] = 'id';
$columns['M.spieltyp'] = 'type';
$columns['M.pokalname'] = 'cup_name';
$columns['M.pokalrunde'] = 'cup_round';
$columns['M.home_noformation'] = 'home_noformation';
$columns['M.guest_noformation'] = 'guest_noformation';
$columns['HOME.name'] = 'home_team';
$columns['HOME.bild'] = 'home_team_picture';
$columns['HOME.id'] = 'home_id';
$columns['HOMEUSER.id'] = 'home_user_id';
$columns['HOMEUSER.nick'] = 'home_user_nick';
$columns['HOMEUSER.email'] = 'home_user_email';
$columns['HOMEUSER.picture'] = 'home_user_picture';
$columns['GUEST.name'] = 'guest_team';
$columns['GUEST.bild'] = 'guest_team_picture';
$columns['GUEST.id'] = 'guest_id';
$columns['GUESTUSER.id'] = 'guest_user_id';
$columns['GUESTUSER.nick'] = 'guest_user_nick';
$columns['GUESTUSER.email'] = 'guest_user_email';
$columns['GUESTUSER.picture'] = 'guest_user_picture';
$columns['M.home_tore'] = 'home_goals';
$columns['M.gast_tore'] = 'guest_goals';
$columns['M.berechnet'] = 'simulated';
$columns['M.minutes'] = 'minutes';
$columns['M.datum'] = 'date';
$matches = array();
$result = $db->querySelect($columns, $fromTable, $whereCondition, $parameters, $limit);
while ($matchinfo = $result->fetch_array()) {
$matchinfo['home_user_picture'] = UsersDataService::getUserProfilePicture($websoccer, $matchinfo['home_user_picture'], $matchinfo['home_user_email']);
$matchinfo['guest_user_picture'] = UsersDataService::getUserProfilePicture($websoccer, $matchinfo['guest_user_picture'], $matchinfo['guest_user_email']);
$matches[] = $matchinfo;
}
$result->free();
return $matches;
}
/**
*
* @param WebSoccer $websoccer Application Context
* @param DbConnection $db DB connection
* @param int $leagueId league id
* @param string $type table type: null|home|guest
* @return array array of teams, ordered by table criteria.
*/
public static function getTeamsOfLeagueOrderedByAlltimeTableCriteria(WebSoccer $websoccer, DbConnection $db, $leagueId, $type = null)
{
$fromTable = $websoccer->getConfig('db_prefix') . '_team_league_statistics AS S';
$fromTable .= ' INNER JOIN ' . $websoccer->getConfig('db_prefix') . '_verein AS C ON C.id = S.team_id';
$fromTable .= ' INNER JOIN ' . $websoccer->getConfig('db_prefix') . '_saison AS SEASON ON SEASON.id = S.season_id';
$fromTable .= ' LEFT JOIN ' . $websoccer->getConfig('db_prefix') . '_user AS U ON C.user_id = U.id';
$whereCondition = 'SEASON.liga_id = %d';
$parameters = $leagueId;
$columns['C.id'] = 'id';
$columns['C.name'] = 'name';
$columns['C.bild'] = 'picture';
$fieldPrefix = 'total';
if ($type == 'home') {
$fieldPrefix = 'home';
} else {
if ($type == 'guest') {
$fieldPrefix = 'guest';
}
}
$columns['SUM(S.' . $fieldPrefix . '_points)'] = 'score';
$columns['SUM(S.' . $fieldPrefix . '_goals)'] = 'goals';
$columns['SUM(S.' . $fieldPrefix . '_goalsreceived)'] = 'goals_received';
$columns['SUM(S.' . $fieldPrefix . '_goalsdiff)'] = 'goals_diff';
$columns['SUM(S.' . $fieldPrefix . '_wins)'] = 'wins';
$columns['SUM(S.' . $fieldPrefix . '_draws)'] = 'draws';
$columns['SUM(S.' . $fieldPrefix . '_losses)'] = 'defeats';
$columns['SUM((S.' . $fieldPrefix . '_wins + S.' . $fieldPrefix . '_draws + S.' . $fieldPrefix . '_losses))'] = 'matches';
$columns['U.id'] = 'user_id';
$columns['U.nick'] = 'user_name';
$columns['U.email'] = 'user_email';
$columns['U.picture'] = 'user_picture';
$teams = array();
$whereCondition .= ' GROUP BY C.id ORDER BY score DESC, goals_diff DESC, wins DESC, draws DESC, goals DESC, name ASC';
$result = $db->querySelect($columns, $fromTable, $whereCondition, $parameters);
while ($team = $result->fetch_array()) {
$team['user_picture'] = UsersDataService::getUserProfilePicture($websoccer, $team['user_picture'], $team['user_email'], 20);
$teams[] = $team;
}
$result->free();
return $teams;
}
/**
* Provides list of matches of team (including pictures and user info), ordered by date descending.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @param int $teamId ID of team.
* @param int $startIndex start fetch index.
* @param int $entries_per_page number of items to fetch
* @return array list of matches or empty array if no matches found.
*/
public static function getMatchesOfTeam(WebSoccer $websoccer, DbConnection $db, $teamId, $startIndex, $entries_per_page)
{
$tablePrefix = $websoccer->getConfig("db_prefix");
$fromTable = $tablePrefix . "_youthmatch AS M";
$fromTable .= " INNER JOIN " . $tablePrefix . "_verein AS HOME ON M.home_team_id = HOME.id";
$fromTable .= " INNER JOIN " . $tablePrefix . "_verein AS GUEST ON M.guest_team_id = GUEST.id";
$fromTable .= " LEFT JOIN " . $tablePrefix . "_user AS HOMEUSER ON HOME.user_id = HOMEUSER.id";
$fromTable .= " LEFT JOIN " . $tablePrefix . "_user AS GUESTUSER ON GUEST.user_id = GUESTUSER.id";
// select
$columns["M.id"] = "match_id";
$columns["HOME.name"] = "home_team";
$columns["HOME.bild"] = "home_team_picture";
$columns["HOME.id"] = "home_id";
$columns["HOMEUSER.id"] = "home_user_id";
$columns["HOMEUSER.nick"] = "home_user_nick";
$columns["HOMEUSER.email"] = "home_user_email";
$columns["HOMEUSER.picture"] = "home_user_picture";
$columns["GUEST.name"] = "guest_team";
$columns["GUEST.bild"] = "guest_team_picture";
$columns["GUEST.id"] = "guest_id";
$columns["GUESTUSER.id"] = "guest_user_id";
$columns["GUESTUSER.nick"] = "guest_user_nick";
$columns["GUESTUSER.email"] = "guest_user_email";
$columns["GUESTUSER.picture"] = "guest_user_picture";
$columns["M.home_goals"] = "home_goals";
$columns["M.guest_goals"] = "guest_goals";
$columns["M.simulated"] = "simulated";
$columns["M.matchdate"] = "date";
$matches = array();
$limit = $startIndex . "," . $entries_per_page;
$result = $db->querySelect($columns, $fromTable, "(home_team_id = %d OR guest_team_id = %d) ORDER BY M.matchdate DESC", array($teamId, $teamId), $limit);
while ($matchinfo = $result->fetch_array()) {
$matchinfo["home_user_picture"] = UsersDataService::getUserProfilePicture($websoccer, $matchinfo["home_user_picture"], $matchinfo["home_user_email"]);
$matchinfo["guest_user_picture"] = UsersDataService::getUserProfilePicture($websoccer, $matchinfo["guest_user_picture"], $matchinfo["guest_user_email"]);
$matches[] = $matchinfo;
}
$result->free();
return $matches;
}
/**
*
* @param WebSoccer $websoccer Application context.
* @param String $fileName file name of picture.
*/
public function setProfilePicture(WebSoccer $websoccer, $fileName)
{
if (strlen($fileName)) {
$this->_profilePicture = UsersDataService::getUserProfilePicture($websoccer, $fileName, null);
}
}
