<?php
  function showInscriptionsProgressTableau()
  {
    // Ce qu'il faut que ça fasse :
    // 1 saison à la fois
    // Loop les IR's, show nb écoles P dans un case
    // et écoles S dans l'autre case et total dans l'autre

    global $connexion;
    //https://www.amcharts.com/demos/stacked-clustered-column-chart/
    //https://bl.ocks.org/mbostock/3887051
    //https://www.zingchart.com/docs/chart-types/bar-charts/

    $html = '';

    $html = '<br />';

    $data = array();

    ### 08-09 #################################################
    $data['2009'] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2009'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,t_usager_grille WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSi4BlocsCompletsP0809($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSi4BlocsCompletsS0809($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRS ++;
        }
      } // End School's loop


      $data['2009'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2009'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2009']['totalSaison'] = $totalSaison;
    ###########################################################


    ### 09-10 #################################################
    $data['2010'] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2010'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,t_usager_grille WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSi4BlocsCompletsP0910($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSi4BlocsCompletsS0910($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2010'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2010'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2010']['totalSaison'] = $totalSaison;
    ###########################################################


    ### 10-11 #################################################
    $data['2011'] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2011'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,t_usager_grille WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSi4BlocsCompletsP1011($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSi4BlocsCompletsS1011($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2011'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2011'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2011']['totalSaison'] = $totalSaison;
    ###########################################################


    ### 11-12 #################################################
    $data['2012'] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2012'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,t_usager_grille WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSi4BlocsCompletsP1112($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSi4BlocsCompletsS1112($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2012'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2012'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2012']['totalSaison'] = $totalSaison;
    ###########################################################


    ### 12-13 #################################################
    $data['2013'] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2013'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,t_usager_grille WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSi4BlocsCompletsP1213($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSi4BlocsCompletsS1213($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2013'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2013'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2013']['totalSaison'] = $totalSaison;
    ###########################################################


    ### 13-14 #################################################
    $data['2014'] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2014'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,isoactif_t_usager_ecole WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2013_2014') == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2013_2014') == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2014'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2014'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2014']['totalSaison'] = $totalSaison;
    ###########################################################



    ### 14-15 #################################################
    $data['2015'][$ligneIRs['a_nom']] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2015'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,isoactif_t_usager_ecole WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2014_2015') == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2014_2015') == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2015'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2015'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2015']['totalSaison'] = $totalSaison;
    ###########################################################


    ### 15-16 #################################################
    $data['2016'][$ligneIRs['a_nom']] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2016'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,isoactif_t_usager_ecole WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2015_2016') == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2015_2016') == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2016'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2016'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2016']['totalSaison'] = $totalSaison;
    ###########################################################


    ### 16-17 #################################################
    $data['2017'][$ligneIRs['a_nom']] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2017'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,isoactif_t_usager_ecole WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2016_2017') == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2016_2017') == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2017'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2017'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2017']['totalSaison'] = $totalSaison;
    ###########################################################

     ### 17-18 #################################################
    $data['2018'][$ligneIRs['a_nom']] = array();
    $totalSaison = 0;

    // Loop les IR's
    $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
    $resultatIRs = $connexion->query($requeteIRs);
    $nombreIRs = $resultatIRs->num_rows;
    while ($ligneIRs = $resultatIRs->fetch_assoc())
    {
      $data['2018'][$ligneIRs['a_nom']] = array();

      // on va chercher tout les comptes ISO-Actif de l'IR
      $requete = 'SELECT * FROM t_liste_ecoles_08,isoactif_t_usager_ecole WHERE u_e_id_fk = ID AND a_id_fk = \'' . $ligneIRs['a_id'] . '\' ORDER BY NOM ASC';
      $resultat = $connexion->query($requete);

      // Find how many schools completed the form fot that season
      $nbEcolesIRP = 0;
      $nbEcolesIRS = 0;

      while ($ligne = $resultat->fetch_assoc())
      {
        if ($ligne['u_g_prisec'] == 'P' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2017_2018') == 'oui')
        {
          $nbEcolesIRP ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSiFormComplet($ligne['u_id'],$ligne['u_g_prisec'],'_2017_2018') == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $data['2018'][$ligneIRs['a_nom']]['P'] = $nbEcolesIRP;
      $data['2018'][$ligneIRs['a_nom']]['S'] = $nbEcolesIRS;
      $totalSaison += $nbEcolesIRP;
      $totalSaison += $nbEcolesIRS;
    } // End IR's loop

    $data['2018']['totalSaison'] = $totalSaison;
    ###########################################################

    $html .= '<table class="tableProgressTotals">';

      $html .= '<tr>';

        $html .= '<th></th>';

        while($element = current($data))
        {
          $html .= '<th>' . key($data) . '</th>';
          next($data);
        }

        $html .= '<th>Total &darr;</th>';

      $html .= '</tr>';

      // Loop les IR's
      $requeteIRs = 'SELECT * FROM t_arse ORDER BY a_nom ASC';
      $resultatIRs = $connexion->query($requeteIRs);
      while ($ligneIRs = $resultatIRs->fetch_assoc())
      {
        $html .= '<tr>';
          $html .= '<td><strong>' . $ligneIRs['a_nom'] . '</strong></td>';
          $grandTotalEcolesIR = 0;
          for ($year = 2009; $year <= 2018; $year ++)
          {
            $totalEcolesIR = $data[$year][$ligneIRs['a_nom']]['P'] + $data[$year][$ligneIRs['a_nom']]['S'];
            $html .= '<td><strong>P</strong> : ' . $data[$year][$ligneIRs['a_nom']]['P'] . '<br /><strong>S</strong> : ' . $data[$year][$ligneIRs['a_nom']]['S'] . '<br /><strong>T</strong> : ' . $totalEcolesIR . '</td>';
            $grandTotalEcolesIR += $totalEcolesIR;
          }

          $html .= '<td>' . $grandTotalEcolesIR . '</td>';

        $html .= '</tr>';
      }

      // Ligne total
      $html .= '<tr>';

        $html .= '<td><strong>Total &rarr;</strong></td>';

        $grandTotal = 0;
        for ($year = 2009; $year <= 2018; $year ++)
        {
          $grandTotal += $data[$year]['totalSaison'];
          $html .= '<td>' . $data[$year]['totalSaison'] . '</td>';
        }

      $html .= '<td>' . $grandTotal . '</td>';

    $html .= '</table>';

    //print("<pre>" . print_r($data, true) . "</pre>");

    return utf8_decode($html);
  }
?>
