<?php
$servername = "localhost";
$username = "root";
$password = "ssksssoma1";
$dbname = "nyakvago";
echo("<style>.right {    position: absolute;    right: 0px;    width: 189px;    border: 3px solid #ff1818;    padding: 10px;}</style>");
echo("<style>.jobbo {    position: absolute;    right: 0px; padding: 200px 0;} th, td {padding: 5px}</style>");
echo("<center><h1>NYAKVÁGÓ-FUTÁS SPEAKERI FELÜLET</h1></center>");
echo("<div class='jobbo'>");
include("fennmarado.php");
echo("</div>");
echo("<div style='right' class='right'><center><h1>PONTOS IDŐ:<br>");
echo(date('G:i:s',time()));
echo('</h1></div></center>');

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
mysqli_set_charset($conn,"utf8");
$sql = " SELECT beerkezes.indulas,
       beerkezes.beerkezes,
       v.speakerinfo,
       Current_time()                                     AS time,
       v.tav,
       beerkezes.rfiddata,
       v.nev,
       v.rfiddata,
       v.rajtszam,
       CASE
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 16 YEAR) AND date_sub(CURDATE(),INTERVAL 0 YEAR) THEN date_sub(CURDATE(),INTERVAL 16 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 30 YEAR) AND date_sub(CURDATE(),INTERVAL 16 YEAR) THEN date_sub(CURDATE(),INTERVAL 30 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 40 YEAR) AND date_sub(CURDATE(),INTERVAL 30 YEAR) THEN date_sub(CURDATE(),INTERVAL 40 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 50 YEAR) AND date_sub(CURDATE(),INTERVAL 40 YEAR) THEN date_sub(CURDATE(),INTERVAL 50 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 60 YEAR) AND date_sub(CURDATE(),INTERVAL 50 YEAR) THEN date_sub(CURDATE(),INTERVAL 60 YEAR)
       WHEN v.`szuletesidatum`< date_sub(CURDATE(),INTERVAL 60 YEAR) THEN date_sub(CURDATE(),INTERVAL 100 YEAR)
       END AS 'kezdodate',
       CASE
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 16 YEAR) AND date_sub(CURDATE(),INTERVAL 0 YEAR) THEN date_sub(CURDATE(),INTERVAL 0 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 30 YEAR) AND date_sub(CURDATE(),INTERVAL 16 YEAR) THEN date_sub(CURDATE(),INTERVAL 16 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 40 YEAR) AND date_sub(CURDATE(),INTERVAL 30 YEAR) THEN date_sub(CURDATE(),INTERVAL 30 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 50 YEAR) AND date_sub(CURDATE(),INTERVAL 40 YEAR) THEN date_sub(CURDATE(),INTERVAL 40 YEAR)
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 60 YEAR) AND date_sub(CURDATE(),INTERVAL 50 YEAR) THEN date_sub(CURDATE(),INTERVAL 50 YEAR)
       WHEN v.`szuletesidatum`< date_sub(CURDATE(),INTERVAL 60 YEAR) THEN date_sub(CURDATE(),INTERVAL 60 YEAR)
       END AS 'zarodate',
           CASE
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 16 YEAR) AND date_sub(CURDATE(),INTERVAL 0 YEAR) THEN '16 év alatt'
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 30 YEAR) AND date_sub(CURDATE(),INTERVAL 16 YEAR) THEN '16-30 év'
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 40 YEAR) AND date_sub(CURDATE(),INTERVAL 30 YEAR) THEN '30-40 év'
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 50 YEAR) AND date_sub(CURDATE(),INTERVAL 40 YEAR) THEN '40-50 év'
       WHEN v.`szuletesidatum` BETWEEN date_sub(CURDATE(),INTERVAL 60 YEAR) AND date_sub(CURDATE(),INTERVAL 50 YEAR) THEN '50-60 év'
       WHEN v.`szuletesidatum`< date_sub(CURDATE(),INTERVAL 60 YEAR) THEN '60 év felett'
       END AS 'korosztaly',
       Find_in_set(beerkezes, (SELECT Group_concat(beerkezes ORDER BY beerkezes
                                      ASC)
                               FROM   beerkezes
                                      INNER JOIN versenyzo AS x
                                              ON beerkezes.rfiddata = x.rfiddata
                               WHERE  beerkezes <> '00:00:00'
                                      AND v.tav = x.tav AND `szuletesidatum`<`zarodate` AND `szuletesidatum`>`kezdodate` AND v.nem=x.nem)) AS korosztalyosrank,
        Find_in_set(beerkezes, (SELECT Group_concat(beerkezes ORDER BY beerkezes
                                      ASC)
                               FROM   beerkezes
                                      INNER JOIN versenyzo AS x
                                              ON beerkezes.rfiddata = x.rfiddata
                               WHERE  beerkezes <> '00:00:00'
                                      AND v.tav = x.tav AND v.nem=x.nem)) AS rank
FROM   beerkezes
       INNER JOIN versenyzo AS v
               ON beerkezes.rfiddata = v.rfiddata
WHERE  beerkezes <> '00:00:00'
ORDER  BY beerkezes DESC  ";
$result = $conn->query($sql);

/*if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["rajtszam"]. " - Name: " . $row["nev"]. " " . $row["timestamp"]. "<br>";
    }
} else {
    echo "0 results";
}*/
if ($result->num_rows > 0) {
    echo "<table><tr><th>Név</th><th>Rajtszám</th><th>Indulás</th><th>Beérkezés</th><th>Idő</th><th>Táv</th><th>Abszolút helyezés</th><th>Korosztályos helyezés</th><th>Korosztály</th><th>Információ</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["nev"]."</td><td>".$row["rajtszam"]." </td><td>".$row["indulas"]." </td><td>".$row["beerkezes"]."</td> <td>".date('G:i:s',strtotime($row['beerkezes'])-3600-strtotime($row['indulas']))."</td><td>".$row['tav']."</td><td>".$row['rank']."</td><td>".$row['korosztalyosrank']."</td><td>".$row['korosztaly']."</td><td>".$row['speakerinfo']."</td></tr>";
        //echo "<tr><td>".$row["nev"]."</td><td>".$row["rajtszam"]." </td><td>".$row["indulas"]." </td><td>".$row["beerkezes"]."</td> <td>".date('G:i:s',strtotime($row['beerkezes'])-3600-strtotime($row['indulas']))."</td><td>".$row['tav']."</td><td>".$row['korosztaly']."</td><td>".$row['rank']."</td><td>".$row['korosztalyosrank']."</td><td>".$row['speakerinfo']."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();

 ?>