/**
* Find all youth players of a specified team, ordered by position, last name and first name.
* Query is cached.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @param int $teamId ID of team.
* @return array array of youth players or empty array if team has no youth players.
*/
public static function getYouthPlayersOfTeam(WebSoccer $websoccer, DbConnection $db, $teamId)
{
$fromTable = $websoccer->getConfig("db_prefix") . "_youthplayer";
$whereCondition = "team_id = %d ORDER BY position ASC, lastname ASC, firstname ASC";
$players = $db->queryCachedSelect("*", $fromTable, $whereCondition, $teamId);
return $players;
}
/**
* Creates a new unseen notification about any event which shall catch the user's attention.
*
* @param WebSoccer $websoccer application context.
* @param DbConnection $db DB connection.
* @return int ID of national team managed by the current user, or NULL if user does not manage a national team.
*/
public static function getNationalTeamManagedByCurrentUser(WebSoccer $websoccer, DbConnection $db)
{
$result = $db->queryCachedSelect("id", $websoccer->getConfig("db_prefix") . "_verein", "user_id = %d AND nationalteam = '1'", $websoccer->getUser()->id, 1);
if (count($result)) {
return $result[0]["id"];
}
return NULL;
}
/**
* Provides leagues in a by country sorted order.
* Query is cached.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @return array list of leagues or empty array.
*/
public static function getLeaguesSortedByCountry(WebSoccer $websoccer, DbConnection $db)
{
$fromTable = $websoccer->getConfig("db_prefix") . "_liga AS L";
// where
$whereCondition = "1 ORDER BY league_country ASC, league_name ASC";
// select
$columns["L.id"] = "league_id";
$columns["L.name"] = "league_name";
$columns["L.kurz"] = "league_short";
$columns["L.land"] = "league_country";
return $db->queryCachedSelect($columns, $fromTable, $whereCondition);
}
/**
* Provides info about player, its team and lender.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB Connection.
* @param int $playerId ID of player.
* @return array assoc. array with data about player.
*/
public static function getPlayerById(WebSoccer $websoccer, DbConnection $db, $playerId)
{
$columns['P.id'] = 'player_id';
$columns['P.vorname'] = 'player_firstname';
$columns['P.nachname'] = 'player_lastname';
$columns['P.kunstname'] = 'player_pseudonym';
$columns['P.position'] = 'player_position';
$columns['P.position_main'] = 'player_position_main';
$columns['P.position_second'] = 'player_position_second';
$columns['P.geburtstag'] = 'player_birthday';
$columns['P.nation'] = 'player_nationality';
$columns['P.picture'] = 'player_picture';
if ($websoccer->getConfig('players_aging') == 'birthday') {
$ageColumn = 'TIMESTAMPDIFF(YEAR,P.geburtstag,CURDATE())';
} else {
$ageColumn = 'P.age';
}
$columns[$ageColumn] = 'player_age';
$columns['P.verletzt'] = 'player_matches_injured';
$columns['P.gesperrt'] = 'player_matches_blocked';
$columns['P.gesperrt_cups'] = 'player_matches_blocked_cups';
$columns['P.gesperrt_nationalteam'] = 'player_matches_blocked_nationalteam';
$columns['P.vertrag_gehalt'] = 'player_contract_salary';
$columns['P.vertrag_spiele'] = 'player_contract_matches';
$columns['P.vertrag_torpraemie'] = 'player_contract_goalbonus';
$columns['P.w_staerke'] = 'player_strength';
$columns['P.w_technik'] = 'player_strength_technique';
$columns['P.w_kondition'] = 'player_strength_stamina';
$columns['P.w_frische'] = 'player_strength_freshness';
$columns['P.w_zufriedenheit'] = 'player_strength_satisfaction';
$columns['P.sa_tore'] = 'player_season_goals';
$columns['P.sa_assists'] = 'player_season_assists';
$columns['P.sa_spiele'] = 'player_season_matches';
$columns['P.sa_karten_gelb'] = 'player_season_yellow';
$columns['P.sa_karten_gelb_rot'] = 'player_season_yellow_red';
$columns['P.sa_karten_rot'] = 'player_season_red';
$columns['P.st_tore'] = 'player_total_goals';
$columns['P.st_assists'] = 'player_total_assists';
$columns['P.st_spiele'] = 'player_total_matches';
$columns['P.st_karten_gelb'] = 'player_total_yellow';
$columns['P.st_karten_gelb_rot'] = 'player_total_yellow_red';
$columns['P.st_karten_rot'] = 'player_total_red';
$columns['P.transfermarkt'] = 'player_transfermarket';
$columns['P.marktwert'] = 'player_marketvalue';
$columns['P.transfer_start'] = 'transfer_start';
$columns['P.transfer_ende'] = 'transfer_end';
$columns['P.transfer_mindestgebot'] = 'transfer_min_bid';
$columns['P.history'] = 'player_history';
$columns['P.unsellable'] = 'player_unsellable';
$columns['P.lending_owner_id'] = 'lending_owner_id';
$columns['L.name'] = 'lending_owner_name';
$columns['P.lending_fee'] = 'lending_fee';
$columns['P.lending_matches'] = 'lending_matches';
$columns['C.id'] = 'team_id';
$columns['C.name'] = 'team_name';
$columns['C.finanz_budget'] = 'team_budget';
$columns['C.user_id'] = 'team_user_id';
$columns['(SELECT CONCAT(AVG(S.note), \';\', SUM(S.assists)) FROM ' . $websoccer->getConfig('db_prefix') . '_spiel_berechnung AS S WHERE S.spieler_id = P.id AND S.minuten_gespielt > 0 AND S.note > 0)'] = 'matches_info';
$fromTable = $websoccer->getConfig('db_prefix') . '_spieler AS P';
$fromTable .= ' LEFT JOIN ' . $websoccer->getConfig('db_prefix') . '_verein AS C ON C.id = P.verein_id';
$fromTable .= ' LEFT JOIN ' . $websoccer->getConfig('db_prefix') . '_verein AS L ON L.id = P.lending_owner_id';
$whereCondition = 'P.status = 1 AND P.id = %d';
$players = $db->queryCachedSelect($columns, $fromTable, $whereCondition, $playerId, 1);
if (count($players)) {
$player = $players[0];
$player['player_position'] = self::_convertPosition($player['player_position']);
$player['player_marketvalue'] = self::getMarketValue($websoccer, $player);
$player['player_nationality_filename'] = self::getFlagFilename($player['player_nationality']);
$matchesInfo = explode(';', $player['matches_info']);
$player['player_avg_grade'] = round($matchesInfo[0], 2);
if (isset($matchesInfo[1])) {
$player['player_assists'] = $matchesInfo[1];
} else {
$player['player_assists'] = 0;
}
} else {
$player = array();
}
return $player;
}
public static function getMatchReportPlayerRecords(WebSoccer $websoccer, DbConnection $db, $matchId, $teamId)
{
$fromTable = $websoccer->getConfig('db_prefix') . '_spiel_berechnung AS M';
$fromTable .= ' INNER JOIN ' . $websoccer->getConfig('db_prefix') . '_spieler AS P ON P.id = M.spieler_id';
$columns['P.id'] = 'id';
$columns['P.vorname'] = 'firstName';
$columns['P.nachname'] = 'lastName';
$columns['P.kunstname'] = 'pseudonym';
$columns['P.position'] = 'position';
$columns['M.position_main'] = 'position_main';
$columns['M.note'] = 'grade';
$columns['M.tore'] = 'goals';
$columns['M.verletzt'] = 'injured';
$columns['M.gesperrt'] = 'blocked';
$columns['M.karte_gelb'] = 'yellowCards';
$columns['M.karte_rot'] = 'redCard';
$columns['M.feld'] = 'playstatus';
$columns['M.minuten_gespielt'] = 'minutesPlayed';
$columns['M.assists'] = 'assists';
$columns['M.ballcontacts'] = 'ballcontacts';
$columns['M.wontackles'] = 'wontackles';
$columns['M.losttackles'] = 'losttackles';
$columns['M.shoots'] = 'shoots';
$columns['M.passes_successed'] = 'passes_successed';
$columns['M.passes_failed'] = 'passes_failed';
$columns['M.age'] = 'age';
$columns['M.w_staerke'] = 'strength';
$order = 'field(M.position_main, \'T\', \'LV\', \'IV\', \'RV\', \'DM\', \'LM\', \'ZM\', \'RM\', \'OM\', \'LS\', \'MS\', \'RS\')';
$whereCondition = 'M.spiel_id = %d AND M.team_id = %d AND M.feld != \'Ersatzbank\' ORDER BY ' . $order . ', M.id ASC';
$parameters = array($matchId, $teamId);
$players = $db->queryCachedSelect($columns, $fromTable, $whereCondition, $parameters);
return $players;
}
/**
* Provides name, budget, user ID (without name), league name and league id of team with specified ID.
* Query is cached.
*
* @param WebSoccer $websoccer Application Context
* @param DbConnection $db DB connection
* @param int $teamId ID of requested team.
* @return array data about requested team as assoc. array. If not found, then empty array. If no team ID provided, then NULL.
*/
public static function getTeamSummaryById(WebSoccer $websoccer, DbConnection $db, $teamId)
{
if (!$teamId) {
return NULL;
}
$tablePrefix = $websoccer->getConfig('db_prefix');
// from
$fromTable = $tablePrefix . '_verein AS C';
$fromTable .= ' LEFT JOIN ' . $tablePrefix . '_liga AS L ON C.liga_id = L.id';
// where
$whereCondition = 'C.status = 1 AND C.id = %d';
$parameters = $teamId;
// select
$columns['C.id'] = 'team_id';
$columns['C.name'] = 'team_name';
$columns['C.finanz_budget'] = 'team_budget';
$columns['C.bild'] = 'team_picture';
$columns['C.user_id'] = 'user_id';
$columns['L.name'] = 'team_league_name';
$columns['L.id'] = 'team_league_id';
$teaminfos = $db->queryCachedSelect($columns, $fromTable, $whereCondition, $parameters, 1);
$team = isset($teaminfos[0]) ? $teaminfos[0] : array();
return $team;
}
