Clever Geek Handbook

UEFA Country Ratio Calculation

image






There are two popular tournaments in football: the Champions League and the Europa League. Based on their results, the so-called Rating of Football Associations is calculated. Based on this rating, it is further determined how many teams from each country will participate in subsequent tournaments.



In this article, I will create an application based on the open and free lsFusion platform that will calculate this rating. It will store all its data in PostgreSQL, provide a web interface for changing and displaying them with filtering and sorting capabilities, as well as import match results using a special API.



All code for implementing this application will consist of about 300 significant lines.



Domain logic



The creation of any information system begins with the task of domain logic.

First of all, it is logical to single out the simplest directories that have only a code and a name:





Since lsFusion uses the same logic to declare them, we will declare a metacode (or code template) that will generate the corresponding logic:



Directory Declaration Metacode
META defineMasterObject(object, caption, captions, nameLength)

CLASS ### object caption;



id '' = DATA INTEGER ( ### object);

object ( INTEGER id) = GROUP AGGR ### object o BY id(o); //



name '' = DATA ISTRING ( ### object) CHARWIDTH nameLength;

object ( ISTRING name) = GROUP AGGR ### object o BY name(o); //



FORM object caption

OBJECTS o = ### object PANEL

PROPERTIES (o) id, name



EDIT ### object OBJECT o

;



FORM object ## s captions

OBJECTS o = ### object

PROPERTIES (o) READONLY id, name

PROPERTIES (o) NEWSESSION NEW , EDIT , DELETE

;



FORM dialog ### object ## s captions

OBJECTS o = ### object

PROPERTIES (o) READONLY id, name



LIST ### object OBJECT o

;



NAVIGATOR {

NEW object ## s;

}

END









He will announce:





Four parameters are passed to the metacode:





Using the created metacode, add the five entities described above:

@defineMasterObject (tournament, '' , '' , 20 );

@defineMasterObject (season, '' , '' , 5 );

@defineMasterObject (round, '' , '' , 15 );

@defineMasterObject (country, '' , '' , 10 );

@defineMasterObject (team, '' , '' , 20 );









The generated code, for example, for a tournament will look like this:

CLASS Tournament '' ;



id '' = DATA INTEGER (Tournament);

tournament ( INTEGER id) = GROUP AGGR Tournament o BY id(o);



name '' = DATA ISTRING (Tournament) CHARWIDTH 20 ;

tournament ( ISTRING name) = GROUP AGGR Tournament o BY name(o);



FORM tournament ''

OBJECTS o = Tournament PANEL

PROPERTIES (o) id, name



EDIT Tournament OBJECT o

;



FORM tournaments ''

OBJECTS o = Tournament

PROPERTIES (o) READONLY id, name

PROPERTIES (o) NEWSESSION NEW , EDIT , DELETE

;



FORM dialogTournaments ''

OBJECTS o = Tournament

PROPERTIES (o) READONLY id, name



LIST Tournament OBJECT o

;



NAVIGATOR {

NEW tournaments;

}







Add the link to the country to the generated club logic. To do this, first create the corresponding property, which we then put on the editing and viewing forms of the club:

country = DATA Country (Team);

nameCountry '' (Team t) = name(country(t));



EXTEND FORM team PROPERTIES (o) nameCountry;

EXTEND FORM teams PROPERTIES (o) READONLY nameCountry;







We put all the created logic in a separate Master module (Master.lsf file).



Now create an entity League . She will determine the tournament of a particular season. For example, Champions League 2017-18 or Europa League 2018-19. The league will not have a name, but only links to the tournament and season. Therefore, we will not use the previous metacode, but we will do the same logic and put it in the new League module:

MODULE League;



REQUIRE Master;



CLASS League '' ;



id '' = DATA INTEGER (League);

league ( INTEGER id) = GROUP AGGR League o BY id(o);



tournament = DATA Tournament (League);

nameTournament '' (League l)= name(tournament(l));



season = DATA Season(League);

nameSeason '' (League l)= name(season(l));



FORM league ''

OBJECTS o = League PANEL

PROPERTIES (o) id, nameTournament, nameSeason



EDIT League OBJECT o

;



FORM leagues ''

OBJECTS o = League

PROPERTIES (o) READONLY id, nameTournament, nameSeason

PROPERTIES (o) NEWSESSION NEW , EDIT , DELETE

;



FORM dialogLeagues ''

OBJECTS o = League

PROPERTIES (o) READONLY id, nameTournament, nameSeason



LIST League OBJECT o

;



NAVIGATOR {

NEW leagues;

}







And finally, add the logic of matches. To do this, create the class Match , which will refer to the league and round. For him will also be asked the clubs that participated in it, and the result. We put all this in a separate Match module:

MODULE Match;



REQUIRE League;



CLASS Match '' ;



id '' = DATA INTEGER (Match);

match ( INTEGER id) = GROUP AGGR Match o BY id(o);



dateTime '' = DATA DATETIME (Match);



league = DATA League (Match);



tournament (Match m) = tournament(league(m));

nameTournament '' (Match m) = name(tournament(m));



season(Match m) = season(league(m));

nameSeason '' (Match m) = name(season(m));



round = DATA Round (Match);

nameRound '' (Match m) = name(round(m));



homeTeam = DATA Team (Match);

nameHomeTeam '' (Match m) = name(homeTeam(m));



awayTeam = DATA Team (Match);

nameAwayTeam '' (Match m) = name(awayTeam(m));



goalsHome ' ()' = DATA INTEGER (Match);

goalsAway ' ()' = DATA INTEGER (Match);



FORM match ''

OBJECTS o = Match PANEL

PROPERTIES (o) id, dateTime, nameTournament, nameSeason, nameRound,

nameHomeTeam, goalsHome, goalsAway, nameAwayTeam



EDIT Match OBJECT o

;



FORM matches ''

OBJECTS o = Match

PROPERTIES (o) READONLY id, dateTime, nameTournament, nameSeason, nameRound,

nameHomeTeam, goalsHome, goalsAway, nameAwayTeam

PROPERTIES (o) NEWSESSION NEW , EDIT , DELETE



LIST Match OBJECT o

;



NAVIGATOR {

NEW matches;

}









Data import



Unfortunately, I managed to find only one public and free API that supports all Eurocups. This is the Football API . However, there are problems:







Data errors can be corrected manually using previously created forms. However, since the calculation of the total coefficient is based on the last five years, unfortunately, it will not work to calculate the total coefficient from the API Football data. If someone in the comments suggests where to get the necessary data in any format from previous years, I will be very grateful. But, since there are complete data for 2018, it will be possible to verify the correctness of the calculation for at least this year.



The API we need is implemented in the form of HTTP requests, where parameters are transmitted via url, and a special access key is indicated in the header. Declare the corresponding logic:

host = 'api-football-v1.p.rapidapi.com' ;

key ' API Football' = DATA STRING () CHARWIDTH 50 ;



url = 'https://' + host() + '/v2' ;



headers( TEXT name) = CASE

WHEN name = 'x-rapidapi-host' THEN host()

WHEN name = 'x-rapidapi-key' THEN key();







All data import actions will be placed on the previously created leagues form. There we will place the access key in the toolbar of the table with the list of leagues:

EXTEND FORM leagues

PROPERTIES () key DRAW o TOOLBAR

;









First, we implement the list of leagues. For this, the Football API has a special url: / leagues. A GET request to it returns JSON of the form:



Answer 
 { "api":{ "results":2, "leagues":[ { "league_id":1, "name":"2018 Russia World Cup", "country":"World", "country_code":null, "season":2018, "season_start":"2018-06-14", "season_end":"2018-07-15", "logo":"https://www.api-football.com/public/leagues/1.png", "flag":null, "standings":0, "is_current":1 }, { "league_id":2, "name":"Premier League", "country":"England", "country_code":"GB", "season":2018, "season_start":"2018-08-10", "season_end":"2019-05-12", "logo":"https://www.api-football.com/public/leagues/2.png", "flag":"https://www.api-football.com/public/flags/gb.svg", "standings":1, "is_current":1 } ] } }




To generate a GET request to it and record the response body, the following construction is used:

LOCAL result = FILE ();

EXTERNAL HTTP GET url() + '/leagues' HEADERS headers TO result;







It writes the result to the local result property without parameters of type FILE.



To parse a file in JSON format, a form is built whose structure corresponds to the JSON structure. You can generate it in the IDE using the menu item:



image



For the above JSON, the form will look like this (taking into account only those values ​​that will be imported):

GROUP api;



tournamentName = DATA LOCAL STRING ( INTEGER );

seasonName = DATA LOCAL STRING ( INTEGER );

leagueId = DATA LOCAL INTEGER ( INTEGER );



FORM importLeagues

OBJECTS leagues = INTEGER IN api

PROPERTIES (leagues) name = tournamentName, season = seasonName, league_id = leagueId

;







To directly import from the JSON result property in the format of the importLeagues form , use the following command:

IMPORT importLeagues JSON FROM result();







After its execution, the corresponding values ​​from the JSON file will be placed in the tournamentName , seasonName and leagueId properties:



image



That is, the value for tournamentName (0) will be “World Cup”, and in tournamentName (1) it will be “Premier League”.



Unfortunately, API Football does not have a tournament entity at all. The only way to link all the leagues is to have a name that matches the leagues of the same tournament from different seasons. To do this, in the import, we first group all the names of the imported leagues and, if not in the database, create new tournaments:

FOR [ GROUP SUM 1 BY tournamentName( INTEGER i)]( STRING tn) AND NOT tournament(tn) DO NEW t = Tournament {

name(t) <- tn;

}







There are also no codes for the seasons, so when importing leagues, they are created in the same way. After the missing objects are created, the leagues are imported directly. Tournaments and seasons are searched by name using the properties built earlier through GROUP AGGR :

FOR leagueId( INTEGER i) AND NOT league(leagueId(i)) DO NEW l = League {

id(l) <- leagueId(i);

tournament(l) <- tournament(tournamentName(i));

season(l) <- season(seasonName(i));

}







By default, the data will be loaded, but it will be saved to the database only when the user clicks the Save button on the form. If necessary, you can add the APPLY command at the end of the action so that it is immediately saved to the database without a preview.



And finally, add the import action to the league list form:

EXTEND FORM leagues

PROPERTIES () importLeagues DRAW o TOOLBAR

;







Similarly, we import clubs and matches. However, since the API provides the ability to import them only for a specific league, the action must accept the league as an input:



Import clubs and matches
//

teamId = DATA LOCAL INTEGER ( INTEGER );

teamName = DATA LOCAL STRING ( INTEGER );

countryName = DATA LOCAL STRING ( INTEGER );



FORM importTeams

OBJECTS teams = INTEGER IN api

PROPERTIES (teams) team_id = teamId, name = teamName, country = countryName

;



importTeams ' ' (League l) {

LOCAL result = FILE ();

EXTERNAL HTTP GET url() + '/teams/league/' + id(l) HEADERS headers TO result;



IMPORT importTeams JSON FROM result();

FOR [ GROUP SUM 1 BY countryName( INTEGER i)]( STRING cn) AND NOT country(cn) DO NEW c = Country {

name(c) <- cn;

}



FOR teamId( INTEGER i) AND NOT team(teamId(i)) DO NEW t = Team {

id(t) <- teamId(i);

name(t) <- teamName(i);

country(t) <- country(countryName(i));

}

}



//



matchId = DATA LOCAL INTEGER ( INTEGER );

dateTime = DATA LOCAL STRING ( INTEGER );

roundName = DATA LOCAL STRING ( INTEGER );



GROUP homeTeam;

homeTeamId = DATA LOCAL INTEGER ( INTEGER );



GROUP awayTeam;

awayTeamId = DATA LOCAL INTEGER ( INTEGER );



goalsHome = DATA LOCAL INTEGER ( INTEGER );

goalsAway = DATA LOCAL INTEGER ( INTEGER );



FORM importMatches

OBJECTS fixtures = INTEGER IN api

PROPERTIES (fixtures) fixture_id = matchId, league_id = leagueId, event_date = dateTime, round = roundName,

homeTeamId IN homeTeam EXTID 'team_id' ,

awayTeamId IN awayTeam EXTID 'team_id' ,

goalsHomeTeam = goalsHome, goalsAwayTeam = goalsAway

;



importMatches ' ' (League l) {

LOCAL result = FILE ();

EXTERNAL HTTP GET url() + '/fixtures/league/' + id(l) HEADERS headers TO result;



IMPORT importMatches JSON FROM result();

FOR [ GROUP SUM 1 BY awayTeamId( INTEGER i)]( INTEGER id) AND NOT team(id) DO {

MESSAGE ' ' + id;

RETURN ;

}



FOR [ GROUP SUM 1 BY awayTeamId( INTEGER i)]( INTEGER id) AND NOT team(id) DO {

MESSAGE ' ' + id;

RETURN ;

}



FOR [ GROUP SUM 1 BY roundName( INTEGER i)]( STRING rn) AND NOT round(rn) DO NEW r = Round {

name(r) <- rn;

}



FOR matchId( INTEGER i) AND NOT match(matchId(i)) DO NEW m = Match {

id(m) <- matchId(i);

dateTime(m) <- toDateTimeFormat(left(dateTime(i), 19 ), 'yyyy-MM-ddThh24:mi:ss' );

league(m) <- league(leagueId(i));

round(m) <- round(roundName(i));

homeTeam(m) <- team(homeTeamId(i));

awayTeam(m) <- team(awayTeamId(i));

goalsHome(m) <- goalsHome(i);

goalsAway(m) <- goalsAway(i);

}

}







There is a peculiarity for matches: team codes go inside additional homeTeam and awayTeam tags . Corresponding groups are created for them by analogy with api. Moreover, inside they have the same team_id tags. Since properties with the same name cannot be added to the form, the special EXTID keyword is used , which defines the tag name in the imported JSON.



In order for all imports to be on the same form, and since they are tied to leagues, we take them all out to one form. In addition, we add teams and matches to the form in order to be able to see what is imported before saving:

EXTEND FORM leagues

OBJECTS t = Team

PROPERTIES (t) READONLY id, name



PROPERTIES importTeams(o) DRAW t TOOLBAR



OBJECTS m = Match

PROPERTIES (m) READONLY id, dateTime, nameRound, nameHomeTeam, goalsHome, goalsAway, nameAwayTeam

FILTERS league(m) = o



PROPERTIES importMatches(o) DRAW m TOOLBAR

;



DESIGN leagues {

OBJECTS {

NEW leagueDetails {

fill = 2 ;

type = SPLITH ;

MOVE BOX (t);

MOVE BOX (m);

}

}

}









The resulting form will look like this:

image



All imports will be put in a separate APIFootball module.



Coefficient calculation



We proceed directly to the calculation of the UEFA country coefficient. It is logical to put all the code in a specially installed UEFA module for this.



First, keep in mind that the Football API provides an interface for importing all matches, and not just Eurocups. Therefore, we separate Eurocup matches according to the name of the tournament (it’s more correct to have a separate primary property for this, but the implementation of the properties can always be changed without modifying the rest of the logic):

isCL (Tournament t) = name(t) = 'Champions League' ;

isEL (Tournament t) = name(t) = 'Europa League' ;



isUL (Tournament t) = isCL(t) OR isEL(t);

isUL (Match m) = isUL(tournament(m));







First, let's calculate the points that each club receives in a season for the results of specific matches.

During this period, each team receives:

2 points in case of victory;

1 point in case of a tie.

Since 1999, these points are divided into two if they are earned in qualification rounds, that is:

1 point in case of victory;

0.5 points for a tie.





Create auxiliary properties that determine the relationship between the match and the club:

played (Team t, Match m) = homeTeam(m) = t OR awayTeam(m) = t;

won (Team t, Match m) = (homeTeam(m) = t AND goalsHome(m) > goalsAway(m)) OR (awayTeam(m) = t AND goalsHome(m) < goalsAway(m));

draw (Team t, Match m) = played(t, m) AND goalsHome(m) = goalsAway(m);







To determine how many points are scored in each match, we add the primary property of the numeric type for the round, which by default will be equal to one:

dataMatchCoeff = DATA NUMERIC [ 10 , 1 ] (Round);

matchCoeff ' ' (Round r) = OVERRIDE dataMatchCoeff(r), 1.0 ;







Then we count points for victories and draws and add together:

wonPoints ' ' (Season s, Team t) =

GROUP SUM 2 * matchCoeff(round(Match m)) IF won(t, m) AND season(m) = s AND isUL(m);

drawPoints ' ' (Season s, Team t) =

GROUP SUM 1 * matchCoeff(round(Match m)) IF draw(t, m) AND season(m) = s AND isUL(m);

matchPoints ' ' (Season s, Team t) = wonPoints(s, t) (+) drawPoints(s, t) MATERIALIZED ;







Points for matches are marked as MATERIALIZED so that they are saved in the table and not calculated every time.



Now you need to count bonus points:

In addition, bonus points are awarded:

1 point is given in case the team reaches the quarter finals, semifinals and finals in European cups;

4 points for entering the group stage of the Champions League (until 1996 - 2 points, from 1997 to 2003 - 1 point, from 2004 to 2008 - 3 points);

5 points in the event of a team leaving for the 1/8 finals of the Champions League (before 2008 - 1 point).

Only played matches are taken into account (technical losses are not taken into account). Matches ending in a series of penalty shootouts, when calculating the coefficient are considered in accordance with the result, which is fixed by the results of the game in regular and extra time.



In this implementation, we assume that the club went into the round of the tournament if he played at least one match in it. To do this, we calculate how many matches the club played in a particular season, tournament, round:

played '' (Season s, Tournament t, Round r, Team tm) =

GROUP SUM 1 IF played(tm, Match m) AND round(m) = r AND tournament(m) = t AND season(m) = s;









Now you need to determine how many points to score for the passage in a particular round. Since it depends on the tournament (for example, a passage in the ⅛ Champions League is given 5 points, but nothing in the Europa League). To do this, we introduce the primary property:

bonusPoints ' ' = DATA NUMERIC [ 10 , 1 ] (Tournament, Round);







Now let's calculate bonus points and the total number of points for the club for the season:

bonusPoints ' ' (Season s, Team tm) = GROUP SUM bonusPoints(Tournament t, Round r) IF played(s, t, r, tm) MATERIALIZED ;



points '' (Season s, Team tm) = matchPoints(s, tm) (+) bonusPoints(s, tm);







Finally, we go directly to the country coefficient.

To calculate the rating of the association, all the points scored by the clubs that participated in the Champions League and the Europa League are added up, and the result is divided by the number of clubs from this association [2] [3].



Let's calculate the number of clubs for each association that participated in European competitions:

matchesUL ' ' (Season s, Team t) = GROUP SUM 1 IF played(t, Match m) AND season(m) = s AND isUL(m);

teams '' (Season s, Country c) = GROUP SUM 1 IF matchesUL(s, Team t) AND country(t) = c;







Now we consider the total number of association points for the season and divide by the number of clubs:

totalPoints ' ()' (Season s, Country c) = GROUP SUM points(s, Team t) IF country(t) = c;

points '' (Season s, Country c) = trunc( NUMERIC [ 13 , 4 ](totalPoints(s, c)) / teams(s, c), 3 );









A country's rating is the sum of the country's coefficients for the previous 5 years.





To do this, we number all the seasons starting from the last according to the internal code (we assume that the latter were added later and have a larger code):

index '' (Season s) = PARTITION SUM 1 IF s IS Season ORDER DESC s;







If necessary, you can enter a separate field or number by name.

It remains only to calculate the final rating for the country:

rating '' (Country c) = GROUP SUM points(Season s, c) IF index(s) <= 5 ;







Above, we announced odds for tournaments and rounds. Add them to the tournament editing form, while filtering only those rounds that were in these tournaments:

matches (Tournament t, Round r) = GROUP SUM 1 IF tournament(Match m) = t AND round(m) = r;



EXTEND FORM tournament

OBJECTS r = Round

PROPERTIES name(r) READONLY , matchCoeff(r), bonusPoints(o, r)

FILTERS matches(o, r)

;









Odds settings, for example, for the Champions League you need to set like this:

image



Let's draw a form that will display the rating, where teams will be shown for each country, and for each team its matches:

FORM countryCoefficientUEFA ' UEFA'

OBJECTS s = Season

FILTERS index(s) <= 5



OBJECTS c = Country

PROPERTIES (c) READONLY name, rating

PROPERTIES (s, c) COLUMNS (s) points HEADER ' : ' + name(s), teams HEADER ' : ' + name(s)



OBJECTS t = Team

PROPERTIES (t) READONLY nameCountry, name

PROPERTIES (s,t) COLUMNS (s) HEADER name(s) points BACKGROUND matchesUL(s, t)

FILTERGROUP country

FILTER ' ' country(t) = c DEFAULT



OBJECTS m = Match

PROPERTIES (m) READONLY dateTime, nameTournament, nameSeason, nameRound,

nameHomeTeam,

goalsHome BACKGROUND goalsHome(m) > goalsAway(m),

goalsAway BACKGROUND goalsHome(m) < goalsAway(m),

nameAwayTeam

FILTERS played(t, m)

ORDER dateTime(m) DESC

;



DESIGN countryCoefficientUEFA {

OBJECTS {

NEW countryDetails {

type = SPLITH ;

fill = 0.5 ;

MOVE BOX (t);

MOVE BOX (m);

}

}



}



NAVIGATOR {

NEW countryCoefficientUEFA;

}







The resulting form will look like this:

image

The color in the tables of clubs shows when he took part in the seasons, and in the table of matches - who won.

The picture shows that the ratings for 2018 are calculated exactly the same as on Wikipedia. For previous years, as mentioned above, Football API does not provide all the information.



Total





We built a small application that is fully described by the above code and stores its data in PostgreSQL, provides a web interface for viewing and editing data. At the same time, it will work effectively on large volumes, since all forms read only the visible window. Also out of the box are filters, sorting, uploading to Excel, and more.



It should be noted how easily using the platform the task of calculating the coefficient was decomposed into individual properties. When executed, all this logic will be translated into SQL queries, and all calculations will be performed directly on the database server using all DBMS optimizations.



An example of how the application works with the data loaded into it can be found at: https://demo.lsfusion.org/euroleague . Guest login without password. The user is in readonly mode.



Those who wish can set themselves everything locally and, for example, model the coefficients by entering the results of future matches. All the application modules described above are hosted on github . After an automatic installation, you just need to drop these files into the appropriate folder from the instructions and restart the server.



In order to download data from the Football API, you need to register with them and get the API key. It requires a card, but if you make no more than 50 requests per day, then nothing will be deducted from it.



In addition, you can run this application online in the appropriate section on the site. On the Platform tab, select the UEFA Odds Calculation example and click Play.



By the way, if someone needs to implement some kind of simple system for which Excel is no longer suitable, then write in the comments. In order to learn the capabilities of the platform, we will try to implement it and write the corresponding article.


More articles:


All Articles