This Processing sketch is used to generate plots that illustrate shared playing time between teammates on a soccer team. An example can be seen at https://twitter.com/bernhardtsoccer/status/627678873422045184

soccer_combos_4.pde

/* Player Combinations 4 - complete rebuild
 * ---
 * This is a ground-up rebuild of the player combinations sketch
 * that attempts to roll all calculations into a single workflow.
 * 
 * If I can work in >2 combination information, that would be 
 * awesome.
 */

// Import libraries
import de.bezier.data.sql.*;

// Objects
PrintWriter output; // log file
PFont font; // loading font - this is located in the sketch's data directory
MySQL msql; // database connection

// Init global variables
int intWidth = 200; // output resolution (overridden in setup)
int intHeight = 200;
int intYear = 2015; // what year we are testing for
int intTeamID = 340; // 11 is the Crew
int intSeasonLength = 0; // how many minutes?
int intGameCount = 0; // how many games?
int intPlayerCount = 0; // how many players?
boolean boolConnection = false; // is there a database connection?
int x = 0; // counter
int y = 0;
int z = 0;
int[][] arrPlayerData = new int[99][99]; // array for player minutes played
String[] arrPlayerNames = new String[99]; // array for player names
String SQL = ""; // database query command

void setup() {
  output = createWriter("soccer_combos_4.txt");
  output.println("Setup started");
  
  // Init variables
  font = loadFont("ArialUnicodeMS-48.vlw");
    
  dbConnect();  

  if (boolConnection) {
    //get basic information (season length, etc.)
    msql.query("SELECT tbl_games.ID, MatchTime, HTeamID, ATeamID, MAX(TimeOff) AS GameLength FROM tbl_games INNER JOIN tbl_gameminutes ON tbl_games.ID = tbl_gameminutes.GameID WHERE year(MatchTime) = "+intYear+" AND (HTeamID = "+intTeamID+" OR ATeamID = "+intTeamID+") GROUP BY tbl_games.ID");
    while(msql.next()){
      intSeasonLength += msql.getInt("GameLength");
      intGameCount ++;
    }
    
    //get player ID list, player count
    x = 0;
    msql.query("SELECT DISTINCT PlayerID FROM tbl_gameminutes INNER JOIN tbl_games ON tbl_gameminutes.GameID = tbl_games.ID WHERE YEAR(matchTime) = "+intYear+" AND TeamID = "+intTeamID);
    while(msql.next()){
      x++;
      intPlayerCount++;
    }
    
    x = 0;
    //retrieve player information
    msql.query("SELECT DISTINCT PlayerID, SUM(TimeOff-TimeOn) AS Minutes, CONCAT(FirstName,' ',LastName) AS PlayerName FROM tbl_gameminutes INNER JOIN tbl_games ON tbl_gameminutes.GameID = tbl_games.ID INNER JOIN tbl_players ON tbl_gameminutes.PlayerID = tbl_players.ID WHERE YEAR(matchTime) = "+intYear+" AND TeamID = "+intTeamID+" GROUP BY tbl_gameminutes.PlayerID ORDER BY Minutes DESC");
    while(msql.next()){
      arrPlayerNames[x] = msql.getString(3);
      arrPlayerData[x][0] = msql.getInt(1); // Player ID
      arrPlayerData[x][1] = msql.getInt(2); // Minutes Played
      x++;
    }    
  } else {
    output.println("! Database connection error");
  }
  
  output.println(intPlayerCount+" players, "+intSeasonLength+" minutes in "+intGameCount+" games.");
  
  intHeight = intSeasonLength;
  intHeight = 768;
  intWidth = intPlayerCount * 50;
  output.println(intWidth+" by "+intHeight+" pixels.");
  
  size(intWidth,intHeight);
  background(255);

  //reset variables  
  x=0;

  output.println("Setup completed");
}

void draw() {
  float tempY; // used to map values from season length to graphic height
  output.println("Draw started");
  
  output.println("  "+arrPlayerNames[x]+" "+arrPlayerData[x][0]);
  //init variables
  String strFileName = arrPlayerNames[x];
  int intTimeOn = 0;
  int intTimeOff = 0;
  int intTimeTogether = 0;
  float offset = 0;
  textFont(font, 32);

  if(arrPlayerData[x][0]==0) {
    keyPressed();
  } else {
    output.println("  Valid player record");
  }
    
  //white background
  background(255);
  output.println("  Background set");
  
  //vertical scale
  y = 0;
  while(y<intSeasonLength){
    tempY = height-map(y,0,intSeasonLength,0,height);
    stroke(128);
    line(0,tempY,width,tempY);
    y = y + 90;
  }
  output.println("  Vertical scale drawn");
  
  //this player's minutes played
  noStroke();
  fill(color(255,212,7),128); // Crew yellow, half transparent
  tempY = height-map((arrPlayerData[x][1]),0,intSeasonLength,0,height);
  rect(0,tempY,width,height);
  output.println("  "+arrPlayerData[x][1]+" minutes");  
  
  //loop through teammate minutes
  float tempY2;
  y=0;
  while(y<intPlayerCount) {
    if(x!=y){
      fill(color(128,128,128),128);
      intTimeTogether=0;
      // need to reverse engineer this next SQL call
      SQL = "SELECT g.ID, MatchTime, p1.ID, m1.TimeOn, m1.TimeOff, m2.TimeOn, m2.TimeOff FROM tbl_games g LEFT OUTER JOIN tbl_gameminutes m1 ON g.ID = m1.GameID LEFT OUTER JOIN tbl_players p1 ON m1.PlayerID = p1.ID LEFT OUTER JOIN tbl_gameminutes m2 ON g.ID = m2.GameID LEFT OUTER JOIN tbl_players p2 ON m2.PlayerID = p2.ID WHERE YEAR(MatchTime) = "+intYear+" AND (HTeamID="+intTeamID+" OR ATeamID="+intTeamID+") AND (p1.ID="+arrPlayerData[x][0]+" OR p1.ID = NULL) AND (p2.ID="+arrPlayerData[y][0]+" OR p2.ID = NULL) GROUP BY g.ID ORDER BY MatchTime ASC";
      output.println("    "+SQL);
      msql.query(SQL);
      while(msql.next()){
        intTimeOn = msql.getInt(4); // Player 1 time on
        if(msql.getInt(6)>intTimeOn){
          intTimeOn = msql.getInt(6);
        }
        intTimeOff = msql.getInt(5); // Player 1 time off
        if(msql.getInt(7)<intTimeOff){
          intTimeOff = msql.getInt(7);
        }
        if(intTimeOn<intTimeOff){
          intTimeTogether = intTimeTogether + (intTimeOff-intTimeOn);
        }        
      }
      output.println("    "+arrPlayerNames[x]+" played "+arrPlayerData[x][1]+" minutes");
      output.println("    "+arrPlayerNames[y]+" played "+arrPlayerData[y][1]+" minutes");
      output.println("    "+intTimeTogether+" minutes together");
      // top of this player's minutes are: 
      // height-map((arrPlayerData[x][1]),0,intSeasonLength,0,height)
      tempY = height-map(arrPlayerData[x][1]-intTimeTogether,0,intSeasonLength,0,height);
      tempY2 = -1*map(arrPlayerData[y][1],0,intSeasonLength,0,height);
      output.println("    tempY: "+tempY);
      output.println("    tempY2: "+tempY2);
      rect(y*50,tempY,45,tempY2);
      
      //label this player's bar
    }
    
    // label this player's bar
    if(x!=y){
      fill(color(128,128,128));
      // recalculating tempY2
      // offset = map(arrPlayerData[y][1],0,intSeasonLength,0,height);
      offset = height;
    } else {
      fill(color(0,0,0));
      offset = height;
    }
    pushMatrix();
    translate((y*50)+40,offset-5);
    rotate(-1*HALF_PI);
    text(arrPlayerNames[y], 0, 0);
    popMatrix();
    output.println("    Label written");
    
    y++;
  }
  
  saveFrame(intTeamID + "_" + intYear+"_####_"+strFileName+".png");
  output.println("  Frame saved");
  
  output.println("Draw completed");
  output.println("");
  
  x++;
}

void keyPressed() {
  //Abort operation
  output.println("!!! Abort - key pressed");
  output.flush(); // write the rest of the cached data to the output file
  output.close(); // finishes the file
  exit(); // stops the program  
}

void dbConnect() {
  String user = "";
  String pass = "";
  String database = "";  
  String server = "";
  msql = new MySQL( this, server, database, user, pass );
  if (msql.connect() ) {
    output.println("Connection established");
    boolConnection = true;
  } else {
    output.println("Connection problem");
    boolConnection = false;
  }
}