1.4-Arcade-Website/games.php

<?php
    $page = 2;
    require("inc_header.php");

    // ======================================================
    // LATEST GAMES
    // ======================================================
    
    $table = "";

    $query = "SELECT `title`, `difficulty`, songinstance.id as siid, song.id as sid, (`enemies_hit` / (`enemies_missed` + `enemies_hit`)) * 100 as ratio, `buttons_pressed`, `joystick_moved`, `start_time`, `play_time` 
              FROM `playdata`, `songinstance`, `song`
              WHERE playdata.songinstance = songinstance.id AND songinstance.song = song.id
              ORDER BY `start_time` DESC";

    $result_temp = mysqli_query($connection, $query);
    while($result = mysqli_fetch_assoc($result_temp))
    {
      $ratio = round($result['ratio']);
      $label = "danger";
      if($ratio >= 40)
        $label = "warning";
      if($ratio >= 80)
        $label = "success";

      $table .= "<tr>";
      $table .= "<td><a href='" . returnurl('song.php?id=' . $result['sid']) . "'>" . $result['title'] . "</a></td>";
      $table .= "<td><a href='" . returnurl('songinstance.php?id=' . $result['siid']) . "'>" . $result['difficulty'] . "</a></td>";
      $table .= "<td><span class='label label-" . $label . "'>" . $ratio . "<small>%</small></span></td>";
      $table .= "<td>" . $result['buttons_pressed'] . "</td>";
      $table .= "<td>" . $result['joystick_moved'] . "</td>";
      $table .= "<td>" . format($result['play_time'])['string'] . "</td>";
      $table .= "<td>" . $result['start_time'] . "</td>";
      $table .= "</tr>";
    }  


    // ======================================================
    // DATA POINTS FOR MOST PLAYED GRAPH
    // DATA POINTS FOR AVERAGE PLAY TIME GRAPH
    // ======================================================

    $most_played_array = array();
    $average_play_time_array = array();

    $query = "SELECT DATE(`start_time`) AS date, COUNT(*) AS data, AVG(`play_time`) AS average 
              FROM `playdata` 
              GROUP BY DATE(`start_time`) 
              ORDER BY `start_time` DESC
              LIMIT 0,14";

    $result_temp = mysqli_query($connection, $query);
    while($result = mysqli_fetch_assoc($result_temp))
    {
      $most_played_array[] = "{y: '" . $result['date'] . "', gp: " . $result['data'] . "}";
      $average_play_time_array[] = "{y: '" . $result['date'] . "', pt: " . format($result['average'])['tseconds'] . "}";
    }

    $most_played_array = array_reverse($most_played_array);
    $average_play_time_array = array_reverse($average_play_time_array);



    // ======================================================
    // PERCENTAGE OF DIFFICULTY
    // ======================================================
    
    $easy = 0;
    $medium = 0;
    $hard = 0;
    $other = 0;

    $query = "SELECT `difficulty`, COUNT(1) as num, COUNT(1) / (SELECT COUNT(1) FROM `playdata` ) * 100 as avg
              FROM `songinstance` AS si, `playdata` as pd 
              WHERE si.id = pd.songinstance
              GROUP BY `difficulty`";

    $result_temp = mysqli_query($connection, $query);
    while($result = mysqli_fetch_assoc($result_temp))
    {
      if(strtolower($result['difficulty']) == "easy")
      {
        $easy = $easy + round($result['avg']);
      }
      else if(strtolower($result['difficulty']) == "medium")
      {
        $medium = $medium + round($result['avg']);
      }
      else if(strtolower($result['difficulty']) == "hard")
      {
        $hard = $hard + round($result['avg']);
      }
      else
      {
        $other = $other + $result['avg'];
      }
    }
    $other = round($other);
?>

<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">

  <!-- Content Header (Page header) -->
  <section class="content-header">
    <h1>
      Games
      <small>All game activity in one quick overview</small>
    </h1>
    <ol class="breadcrumb">
      <li class="active"><a href="#"><i class="fa fa-gamepad"></i> Games</a></li>
    </ol>
  </section>

  <section class="content">

    <div class="row">

      <div class="col-lg-4">

        <!-- games played graph -->
        <div class="box box-solid bg-teal">
          <div class="box-header">
            <i class="fa fa-gamepad"></i>
            <h3 class="box-title">Games played</h3>
          </div>

          <div class="box-body border-radius-none">
            <div class="chart" id="games-played-chart" style="height: 145px;"></div>
          </div><!-- /.box-body -->
        </div><!-- /.box -->

      </div>

            <div class="col-lg-4">

        <!-- Avarage Play Time -->
         <div class="box box-solid bg-orange">
          <div class="box-header">
            <i class="ion ion-clock"></i>
            <h3 class="box-title">Average Play Time</h3>
          </div>

          <div class="box-body border-radius-none">
            <div class="chart" id="average-play-time-chart" style="height: 145px;"></div>
          </div><!-- /.box-body -->
        </div><!-- /.box -->

      </div>

      <div class="col-lg-4">
        <!-- gamemodes -->
        <div class="box">
          <div class="box-header with-border">
            <i class="fa fa-gamepad"></i>
            <h3 class="box-title">Gamemodes</h3>
          </div><!-- /.box-header -->
          <div class="box-body">
              <div class="row">
              <div class="col-xs-3 text-center" style="border-right: 1px solid #f4f4f4">
                <input type="text" class="knob" data-readonly="true" value="<?php echo $easy; ?>" data-width="60" data-height="60" data-fgColor="#39CCCC"/>
                <div class="knob-label">Easy</div>
              </div><!-- ./col -->
              <div class="col-xs-3 text-center" style="border-right: 1px solid #f4f4f4">
                <input type="text" class="knob" data-readonly="true" value="<?php echo $medium; ?>" data-width="60" data-height="60" data-fgColor="#39CCCC"/>
                <div class="knob-label">Medium</div>
              </div><!-- ./col -->
              <div class="col-xs-3 text-center" style="border-right: 1px solid #f4f4f4">
                <input type="text" class="knob" data-readonly="true" value="<?php echo $hard; ?>" data-width="60" data-height="60" data-fgColor="#39CCCC"/>
                <div class="knob-label">Hard</div>
              </div><!-- ./col -->
              <div class="col-xs-3 text-center">
                <input type="text" class="knob" data-readonly="true" value="<?php echo $other; ?>" data-width="60" data-height="60" data-fgColor="#39CCCC"/>
                <div class="knob-label">Other</div>
              </div><!-- ./col -->
            </div><!-- /.row -->
          </div><!-- /.box-body -->
        </div><!-- /.box -->

      </div>
    </div>

    <div class="row">

      <div class="col-lg-12">
        <!-- TABLE: LATEST SONGS -->
        <div class="box">
          <div class="box-header with-border">
            <i class="fa fa-gamepad"></i>
            <h3 class="box-title">Latest Games</h3>
          </div><!-- /.box-header -->
          <div class="box-body">
              <table id="games-played-table" class="table table-bordered">
                <thead>
                  <tr>
                    <th>Song Name</th>
                    <th>Difficulty</th>
                    <th>Hit Ratio</th>
                    <th>Buttons Pressed</th>
                    <th>Joystick Moved</th>
                    <th>Length</th>
                    <th>Time</th>
                  </tr>
                </thead>
                <tbody>
                  <?php
                       echo $table;
                  ?>
                </tbody>
              </table>
          </div><!-- /.box-body -->
        </div><!-- /.box -->
      </div><!-- /.col -->

    </div><!-- /.row -->
    

  </section><!-- /.content -->
</div><!-- /.content-wrapper -->

<!-- Morris.js charts -->
    <script src="http://cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>
    <script src="plugins/morris/morris.min.js" type="text/javascript"></script>
<!-- jQuery Knob Chart -->
    <script src="plugins/knob/jquery.knob.js" type="text/javascript"></script>
<!-- DATA TABES SCRIPT -->
    <script src="plugins/datatables/jquery.dataTables.min.js" type="text/javascript"></script>
    <script src="plugins/datatables/dataTables.bootstrap.min.js" type="text/javascript"></script>
<!-- Page specific javascript -->
    <script type="text/javascript">
        var most_played_data = [ <?php
             foreach ($most_played_array as $value)
             {
                echo $value . ",\n";
             }
        ?> ];

        var average_play_time_data = [ <?php
             foreach ($average_play_time_array as $value)
             {
                echo $value . ",\n";
             }
        ?> ];
    </script>
    <script src="dist/js/pages/games.js" type="text/javascript"></script> 

<?php
    require("inc_footer.php");
?>