/**
* Provides players of specified national team, grouped by position.
*
* @param WebSoccer $websoccer Application context.
* @param DbConnection $db DB connection.
* @param int $clubId ID of team.
* @param string $positionSort ASC|DESC
* @return array two dim array. first dim: Position name, second dim: List of players of this position.
*/
public static function getNationalPlayersOfTeamByPosition(WebSoccer $websoccer, DbConnection $db, $clubId, $positionSort = "ASC")
{
$columns = array("P.id" => "id", "vorname" => "firstname", "nachname" => "lastname", "kunstname" => "pseudonym", "verletzt" => "matches_injured", "gesperrt_nationalteam" => "matches_blocked", "position" => "position", "position_main" => "position_main", "position_second" => "position_second", "w_staerke" => "strength", "w_technik" => "strength_technique", "w_kondition" => "strength_stamina", "w_frische" => "strength_freshness", "w_zufriedenheit" => "strength_satisfaction", "transfermarkt" => "transfermarket", "nation" => "player_nationality", "picture" => "picture", "P.sa_tore" => "st_goals", "P.sa_spiele" => "st_matches", "P.sa_karten_gelb" => "st_cards_yellow", "P.sa_karten_gelb_rot" => "st_cards_yellow_red", "P.sa_karten_rot" => "st_cards_red", "marktwert" => "marketvalue", "verein_id" => "team_id", "C.name" => "team_name");
if ($websoccer->getConfig('players_aging') == 'birthday') {
$ageColumn = 'TIMESTAMPDIFF(YEAR,geburtstag,CURDATE())';
} else {
$ageColumn = 'age';
}
$columns[$ageColumn] = 'age';
$fromTable = $websoccer->getConfig("db_prefix") . "_spieler AS P";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_nationalplayer AS NP ON NP.player_id = P.id";
$fromTable .= " LEFT JOIN " . $websoccer->getConfig("db_prefix") . "_verein AS C ON C.id = P.verein_id";
$whereCondition = "P.status = 1 AND NP.team_id = %d ORDER BY position " . $positionSort . ", position_main ASC, nachname ASC, vorname ASC";
$result = $db->querySelect($columns, $fromTable, $whereCondition, $clubId, 50);
$players = array();
while ($player = $result->fetch_array()) {
$player["position"] = PlayersDataService::_convertPosition($player["position"]);
$player["player_nationality_filename"] = PlayersDataService::getFlagFilename($player["player_nationality"]);
$player["marketvalue"] = PlayersDataService::getMarketValue($websoccer, $player, "");
$players[$player["position"]][] = $player;
}
$result->free();
return $players;
}
private static function _queryOffers(WebSoccer $websoccer, DbConnection $db, $startIndex, $entries_per_page, $whereCondition, $parameters)
{
$columns = array("O.id" => "offer_id", "O.submitted_date" => "offer_submitted_date", "O.offer_amount" => "offer_amount", "O.offer_message" => "offer_message", "O.rejected_date" => "offer_rejected_date", "O.rejected_message" => "offer_rejected_message", "O.rejected_allow_alternative" => "offer_rejected_allow_alternative", "O.admin_approval_pending" => "offer_admin_approval_pending", "P.id" => "player_id", "P.vorname" => "player_firstname", "P.nachname" => "player_lastname", "P.kunstname" => "player_pseudonym", "P.vertrag_gehalt" => "player_salary", "P.marktwert" => "player_marketvalue", "P.w_staerke" => "player_strength", "P.w_technik" => "player_strength_technique", "P.w_kondition" => "player_strength_stamina", "P.w_frische" => "player_strength_freshness", "P.w_zufriedenheit" => "player_strength_satisfaction", "P.position_main" => "player_position_main", "SU.id" => "sender_user_id", "SU.nick" => "sender_user_name", "SC.id" => "sender_club_id", "SC.name" => "sender_club_name", "RU.id" => "receiver_user_id", "RU.nick" => "receiver_user_name", "RC.id" => "receiver_club_id", "RC.name" => "receiver_club_name", "EP1.id" => "explayer1_id", "EP1.vorname" => "explayer1_firstname", "EP1.nachname" => "explayer1_lastname", "EP1.kunstname" => "explayer1_pseudonym", "EP2.id" => "explayer2_id", "EP2.vorname" => "explayer2_firstname", "EP2.nachname" => "explayer2_lastname", "EP2.kunstname" => "explayer2_pseudonym");
$fromTable = $websoccer->getConfig("db_prefix") . "_transfer_offer AS O";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_spieler AS P ON P.id = O.player_id";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_user AS SU ON SU.id = O.sender_user_id";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_verein AS SC ON SC.id = O.sender_club_id";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_verein AS RC ON RC.id = O.receiver_club_id";
$fromTable .= " INNER JOIN " . $websoccer->getConfig("db_prefix") . "_user AS RU ON RU.id = RC.user_id";
$fromTable .= " LEFT JOIN " . $websoccer->getConfig("db_prefix") . "_spieler AS EP1 ON EP1.id = O.offer_player1";
$fromTable .= " LEFT JOIN " . $websoccer->getConfig("db_prefix") . "_spieler AS EP2 ON EP2.id = O.offer_player2";
$whereCondition .= " ORDER BY O.submitted_date DESC";
$limit = $startIndex . "," . $entries_per_page;
$offers = array();
$result = $db->querySelect($columns, $fromTable, $whereCondition, $parameters, $limit);
while ($offer = $result->fetch_array()) {
$offer["player_marketvalue"] = PlayersDataService::getMarketValue($websoccer, $offer);
$offers[] = $offer;
}
$result->free();
return $offers;
}