<?php
  function showInscriptionsProgress()
  {
    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 .= '
    <script>
    var chart = AmCharts.makeChart( "chartdiv", {
      "type": "serial",
      "theme": "light",
      "depth3D": 20,
      "angle": 30,
      "legend": {
        "horizontalGap": 10,
        "useGraphSettings": true,
        "markerSize": 10
      },';

    $html .= '
    "dataProvider": [ ';


    ### 08-09 #################################################
    $html .= '
    {"year": 2009,';

    // 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())
    {
      // 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 ++;
          //$nbEcolesTotalP ++;
        }
        else if ($ligne['u_g_prisec'] == 'S' && verifierSi4BlocsCompletsS0809($ligne['u_id']) == 'oui')
        {
          $nbEcolesIRS ++;
          //$nbEcolesTotalS ++;
        }
      } // End School's loop

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################


    ### 09-10 #################################################
    $html .= '
    {"year": 2010,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################


    ### 10-11 #################################################
    $html .= '
    {"year": 2011,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################


    ### 11-12 #################################################
    $html .= '
    {"year": 2012,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################


    ### 12-13 #################################################
    $html .= '
    {"year": 2013,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################


    ### 13-14 #################################################
    $html .= '
    {"year": 2014,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################



    ### 14-15 #################################################
    $html .= '
    {"year": 2015,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################


    ### 15-16 #################################################
    $html .= '
    {"year": 2016,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################


    ### 16-17 #################################################
    $html .= '
    {"year": 2017,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################

    ### 17-18 #################################################
    $html .= '
    {"year": 2018,';

    // 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())
    {
      // 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

      $nbTotalEcolesIR = $nbEcolesIRP + $nbEcolesIRS;
      $html .= '"' . $ligneIRs['a_nom'] . '": ' . $nbTotalEcolesIR . ',';
    } // End IR's loop

    $html .= '}, ';
    ###########################################################

    $html .= ' ],
    "valueAxes":
    [
      {
       "stackType": "regular",
       "axisAlpha": 0.5,
       "gridAlpha": 0.1
      }
    ],
      "graphs": [ {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "AT",
      "type": "column",
      "color": "#000000",
      "valueField": "AT"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "CE",
      "type": "column",
      "color": "#000000",
      "valueField": "CE"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "CN",
      "type": "column",
      "color": "#000000",
      "valueField": "CN"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "EQ",
      "type": "column",
      "color": "#000000",
      "valueField": "EQ"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "GMAA",
      "type": "column",
      "color": "#000000",
      "valueField": "GMAA"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "LL",
      "type": "column",
      "color": "#000000",
      "valueField": "LL"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "LAV",
      "type": "column",
      "color": "#000000",
      "valueField": "LAV"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "LSL",
      "type": "column",
      "color": "#000000",
      "valueField": "LSL"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "MAU",
      "type": "column",
      "color": "#000000",
      "valueField": "MAU"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "MRG",
      "type": "column",
      "color": "#000000",
      "valueField": "MRG"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "MTL",
      "type": "column",
      "color": "#000000",
      "valueField": "MTL"
    }, {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "OUT",
      "type": "column",
      "color": "#000000",
      "valueField": "OUT"
    },  {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "PROV",
      "type": "column",
      "color": "#000000",
      "valueField": "PROV"
    },  {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "QCA",
      "type": "column",
      "color": "#000000",
      "valueField": "QCA"
    },  {
      "balloonText": "<b>[[title]]</b><br><span style=\'font-size:14px\'>[[category]]: <b>[[value]]</b></span>",
      "fillAlphas": 0.8,
      "labelText": "[[value]]",
      "lineAlpha": 0.3,
      "title": "SLSJ",
      "type": "column",
      "color": "#000000",
      "valueField": "SLSJ"
    } ],
      "categoryField": "year",
      "categoryAxis": {
       "gridPosition": "start",
       "axisAlpha": 0,
       "gridAlpha": 0,
       "position": "left"
      }

      } );
      </script>';

    $html .= '<div id="chartdiv"></div>';

    return utf8_decode($html);

    // AFFICHER PAR IR ET NOMBRE TOTAL

    return $html;
  }
?>
