<?php
include('../../db_connect.php');
$dbname = "cijferlijst";
$conn = new mysqli($servername, $username, $password, $dbname);
if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}
$cls = array('','','','','');
$error = array();

if(isset($_POST['invoerknop'])){

        $leerlingnaam = mysqli_real_escape_string($conn, $_POST['leerlingnaam']);
        $leerlingnaam = test_input($leerlingnaam);
        $leerlingvak = mysqli_real_escape_string($conn, $_POST['vakinvoer']);
        $leerlingvak = test_input($leerlingvak);
        $leerlingcijfer = mysqli_real_escape_string($conn, $_POST['cijferinvoer']);
        $leerlingcijfer = test_input($leerlingcijfer);

    if($leerlingnaam==''){
        $error[] = 'leerling naam';
        $cls[0] = ' class="errorclass"';
    }
    if($leerlingvak=='') {
        $error[] = 'vak naam';
        $cls[1] = ' class="errorclass"';
    }
    if (!preg_match("/([0-9]{1,2}[\.]{1}[0-9]{1})/",$leerlingcijfer)){
        $error[] = 'leerling cijfer';
        $cls[2] = ' class="errorclass"';
    }

    if(count($error)>1){
        echo 'De volgende velden zijn onjuist: <ul><li>' . implode('</li><li>', $error) . '</li></ul>';
    }elseif(count($error)>0){
        echo 'Het volgende veld is onjuist: <ul><li>' . implode('</li><li>', $error) . '</li></ul>';
    }else{
        $sql= "SELECT id FROM vakkenlijst WHERE vak = '$leerlingvak'";
        $result = $conn->query($sql);
        $resultcount = mysqli_num_rows($result);
        if ($resultcount == 0) {
            die("Er is geen vak gevonden onder deze naam");
        }else{
            $row = mysqli_fetch_assoc($result);
            $vakid = $row["id"];
            $sql= "SELECT id FROM leerlingenlijst WHERE leerling = '$leerlingnaam'";
            $result = $conn->query($sql);
            $resultcount = mysqli_num_rows($result);

            if($resultcount == 0){
                die("Geen leerling met die naam gevonden");
            }else{
                $row = mysqli_fetch_assoc($result);
                $leerlingid = $row['id'];
                if ($leerlingid==''  || $vakid=='' || $leerlingcijfer=='') {
                    die("test");
                }else{
                $sql = "INSERT INTO resultaat (leerling_id, vak_id, cijfer) VALUES ('$leerlingid', '$vakid', '$leerlingcijfer')";
                $result = $conn->query($sql);
                $sql = "INSERT INTO cijfer (vak, leerlingnaam, cijfer) VALUES ('$leerlingvak', '$leerlingnaam', '$leerlingcijfer')";
                $result = $conn->query($sql);
                }
            }
        }
    }
}

if(isset($_POST['nieuweleerlingbtn'])){

    $nieuweleerlingnaam = mysqli_real_escape_string($conn, $_POST['nieuweleerlingnaam']);
    $nieuweleerlingnaam = test_input($nieuweleerlingnaam);

    if ($_POST['nieuweleerlingnaam']=='' ){
        $cls[3] = ' class="errorclass"';
    }elseif ($_POST['nieuweleerlingnaam']=='adminadminadmin' ){
        $hiddenmessage = "";
    }elseif (!preg_match("/^[a-zA-Z ,.'-]+$/i",$nieuweleerlingnaam))  {
        $cls[3] = ' class="errorclass"';
    }else{
        $sql= "SELECT leerling FROM leerlingenlijst WHERE leerling = '$nieuweleerlingnaam'";
        $result = $conn->query($sql);
        $resultcount = mysqli_num_rows($result);
        if ($resultcount == 0) {
            $sql = "INSERT INTO leerlingenlijst (leerling) VALUES ('$nieuweleerlingnaam')";
            $result = $conn->query($sql);
        }else{
        $cls[3] = ' class="errorclass"';
        }
    }
}

if(isset($_POST['deleteleerlingbtn'])){

    $deletellnaam = mysqli_real_escape_string($conn, $_POST['leerlingnaamdelete']);
    $deletellnaam = test_input($deletellnaam);

    if ($_POST['leerlingnaamdelete']=='' ){
        $cls[3] = ' class="errorclass"';
    }else{
        $sql = "DELETE FROM leerlingenlijst WHERE leerling = '$deletellnaam'";
        $result = $conn->query($sql);
    }
}

if(isset($_POST['nieuwvakbtn'])){

    $nieuwvaknaam = mysqli_real_escape_string($conn, $_POST['nieuwvaknaam']);
    $nieuwvaknaam = test_input($nieuwvaknaam);

    if ($_POST['nieuwvaknaam']=='' ){
        $cls[4] = ' class="errorclass"';
    }elseif (!preg_match("/^[a-zA-Z ,.'-]+$/i",$nieuwvaknaam))  {
        $cls[4] = ' class="errorclass"';
    }else{
        $sql= "SELECT vak FROM vakkenlijst WHERE vak = '$nieuwvaknaam'";
        $result = $conn->query($sql);
        $resultcount = mysqli_num_rows($result);
        if ($resultcount == 0) {
            $sql = "INSERT INTO vakkenlijst (vak) VALUES ('$nieuwvaknaam')";
            $result = $conn->query($sql);
        }else{
            $cls[4] = ' class="errorclass"';
        }
    }
}

if(isset($_POST['deletevakbtn'])){

    $deletevknaam = mysqli_real_escape_string($conn, $_POST['vaknaamdelete']);
    $deletevknaam = test_input($deletevknaam);

    if ($_POST['vaknaamdelete']=='' ){
        $cls[4] = ' class="errorclass"';
    }else{
        $sql = "DELETE FROM vakkenlijst WHERE vak = '$deletevknaam'";
        $result = $conn->query($sql);
    }
}


function test_input($data) {
   $data = trim($data);
   $data = stripslashes($data);
   $data = htmlspecialchars($data);
   return $data;
}

?>
<html lang="en">
<head>
    <!-- Bootstrap Core CSS -->
    <link href="css/validationcss.css" rel="stylesheet">
</head>
<body>
<div class="maindiv">
    <form  class="defaultform" method="post" action="cijferinvoer.php" >
            <table style='padding: 0px;'>
                <tr class="formtable">
                    <td class="formtable rowtable">
                        <p <?php echo $cls[0]; ?> >Leerling naam:</p>
                    </td>
                    <td class="formtable rowtable">
                        <?php
                            $sql= "SELECT * FROM leerlingenlijst";

                            $result = $conn->query($sql);

                            echo '<select name="leerlingnaam">';
                            while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
                                $temp = $row['leerling'];
                                echo '<option value="'. $temp .'">'. $temp .'</option>';
                            }
                            echo '</select>';
                        ?>
                        <br>
                    </td>
                </tr>
                <tr class="formtable">
                    <td class="formtable rowtable">
                        <p  <?php echo $cls[1]; ?>>Vak:</p>
                    </td>
                    <td class="formtable rowtable">
                        <?php
                            $sql= "SELECT * FROM vakkenlijst" or die("Error in the consult.." . mysqli_error($conn));
                            $result = $conn->query($sql) or die("Error in the consult.." . mysqli_error($conn));
                            echo '<select name="vakinvoer">';
                            while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
                                $temp = $row['vak'];
                                echo '<option value="'. $temp .'">'. $temp .'</option>';
                            }
                            echo '</select>';// Close your drop down box
                        ?>
                        <br>
                    </td>
                </tr>
                <tr class="formtable">
                    <td class="formtable rowtable">
                        <p <?php echo $cls[2]; ?> >Cijfer:</p>
                    </td>
                    <td class="formtable rowtable">
                        <input maxlength="32" type="text" name="cijferinvoer">
                        <br>
                    </td>
                </tr>
                <tr class="formtable ">
                    <td class="formtable rowtable">
                    </td >

                    <td class="formtable rowtable">
                        <input id="buttontest2" type="submit" name="invoerknop" value="Voer in">
                    </td>
                </tr>
        </table>
    </form>
    <br>
    <form class="defaultform" method="post" action="cijferinvoer.php" >
                <table style='padding: 0px;'>
                <tr class="formtable">
                    <td class="formtable rowtable">
                        <p  <?php echo $cls[3]; ?> >Leerling naam:</p>
                    </td>
                    <td  class="formtable rowtable">
                        <input maxlength="32" type="text" name="nieuweleerlingnaam">
                        <br>
                    </td>
                    <td  class="formtable rowtable">
                        <?php
                            $sql= "SELECT * FROM leerlingenlijst";
                            $result = $conn->query($sql);
                            echo '<select name="leerlingnaamdelete">';
                            while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
                                $temp = $row['leerling'];
                                echo '<option value="'. $temp .'">'. $temp .'</option>';
                            }
                            echo '</select>';
                        ?>
                    </td>
                </tr>
                <tr class="formtable">
                    <td>
                    </td >

                    <td  class="formtable rowtable">
                        <input id="buttontest2" type="submit" name="nieuweleerlingbtn" value="Voeg nieuwe leerling toe">
                    </td>
                    <td  class="formtable rowtable">
                        <input id="buttontest2" type="submit" name="deleteleerlingbtn" value="Verwijder leerling">
                    </td>
                </tr>
        </table>

    </form>

    <br>
    <form class="defaultform" method="post" action="cijferinvoer.php" >
                <table style='padding: 0px;'>
                <tr class="formtable">
                    <td class="formtable rowtable">
                        <p  <?php echo $cls[4]; ?>  >Vak naam:</p>
                    </td>
                    <td class="formtable rowtable">
                        <input maxlength="32" type="text" name="nieuwvaknaam">
                        <br>
                    </td>
                    <td  class="formtable rowtable">
                        <?php
                            $sql= "SELECT * FROM vakkenlijst";
                            $result = $conn->query($sql);
                            echo '<select name="vaknaamdelete">';
                            while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
                                $temp = $row['vak'];
                                echo '<option value="'. $temp .'">'. $temp .'</option>';
                            }
                            echo '</select>';
                        ?>
                    </td>
                </tr>
                <tr class="formtable">
                    <td>
                    </td >

                    <td  class="formtable rowtable">
                        <input id="buttontest2" type="submit" name="nieuwvakbtn" value="Voeg nieuw vak toe">
                    </td>
                    <td  class="formtable rowtable">
                        <input id="buttontest2" type="submit" name="deletevakbtn" value="Verwijder vak">
                    </td>
                </tr>
        </table>

    </form>
    <form class="defaultform" method="post" action="cijferinvoer.php" >
        <input id="reloadresults" type="submit" name="Loadresults" value="Laad de resultaten">
        <input id="reloadstudents" type="submit" name="Loadstudents" value="Laad de leerlingenlijst">
    </form>
    <?php
    if(isset($_POST['Loadresults'])){
        $sqlselect = "SELECT * FROM resultaat";
        $resultselect = $conn->query($sqlselect);
        echo '<table style="padding:10px">';
        while($row = mysqli_fetch_array($resultselect, MYSQLI_BOTH)) {
        $temp = $row['tijdstip'];
        echo '
            <tr >
                <td style="padding:6px;  border: 1px solid black;">ID: </td>
                <td style="padding:6px;  border: 1px solid black;"> ' . $row['id'] . '   </td>
                <td style="padding:6px;  border: 1px solid black;">Tijdstip:</td>
                <td style="padding:6px;  border: 1px solid black;"> ' . $temp . '   </td>
                <td style="padding:6px;  border: 1px solid black;">Leerling id:</td>
                <td style="padding:6px;  border: 1px solid black;"> ' . $row['leerling_id'] . ' </td>
                <td style="padding:6px;  border: 1px solid black;">Vak id:</td>
                <td style="padding:6px;  border: 1px solid black;"> ' . $row['vak_id'] . ' </td>
                <td style="padding:6px;  border: 1px solid black;">Cijfer:</td>
                <td style="padding:6px;  border: 1px solid black;"> ' . $row['cijfer'] . ' </td>
            </tr>';

        }
        echo '</table>';
    }
        ?>

    <?php
    if(isset($_POST['Loadstudents'])){
        $sqlselect = "SELECT * FROM leerlingenlijst";
        $resultselect = $conn->query($sqlselect);
        echo '<table style="padding:10px">';
        while($row = mysqli_fetch_array($resultselect, MYSQLI_BOTH)) {
        echo '
            <tr >
                <td style="padding:6px;  border: 1px solid black;">ID: </td>
                <td style="padding:6px;  border: 1px solid black;"> ' . $row['id'] . '   </td>
                <td style="padding:6px;  border: 1px solid black;">Leerling:</td>
                <td style="padding:6px;  border: 1px solid black;"> ' . $row['leerling']. '   </td>
            </tr>';

        }
        echo '</table>';
    }
        ?>
    </div>
</body>

</html>