<?php require_once("dbconn.php"); //connectie db

if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

//geeft items terug
function database_shopping($type = 0, $rows = 0)
{
	//global $errors;
	$db = database_connect();
    if($type == 0){ //get everything
        $doquery = 'SELECT `stoffen`.*, `stoftype`.`Naam` AS `Categorie` FROM `stoffen` INNER JOIN `stoftype` ON `stoffen`.`Type` = `stoftype`.`ID` ORDER BY `stoffen`.`ID` DESC';
    }
    else if($type == 1){ //only shows item with given rowid
        $doquery = 'SELECT `stoffen`.*, `stoftype`.`Naam` AS `Categorie` FROM `stoffen` INNER JOIN `stoftype` ON `stoffen`.`Type` = `stoftype`.`ID` WHERE `stoffen`.`ID`='.$rows;
    }
    else if($type == 2){ //only shows the lastest added item(s)
        $doquery = 'SELECT `stoffen`.*, `stoftype`.`Naam` AS `Categorie` FROM `stoffen` INNER JOIN `stoftype` ON `stoffen`.`Type` = `stoftype`.`ID` ORDER BY `ID` DESC LIMIT '.$rows;
    }
    else if($type == 3){ //items by category
        $doquery = 'SELECT `stoffen`.*, `stoftype`.`Naam` AS `Categorie` FROM `stoffen` INNER JOIN `stoftype` ON `stoffen`.`Type` = `stoftype`.`ID` WHERE `Type`='.$rows;
    }
    else if($type == 4){ //items by search
        $doquery = "SELECT `stoffen`.*, `stoftype`.`Naam` AS `Categorie` FROM `stoffen` INNER JOIN `stoftype` ON `stoffen`.`Type` = `stoftype`.`ID` WHERE `stoffen`.`Naam` LIKE '%".$rows."%'";
    }
    
    $items = [];

    $result = mysqli_query($db, $doquery); 

    while($row = mysqli_fetch_assoc($result))
    {
        $items[] = $row;
    }
    mysqli_close($db);
    return $items;
}

function database_reservlist($id)
{
    $db = database_connect();

    $doquery = 'SELECT `stoffen`.*, `stoftype`.`Naam` AS `Categorie`, `gereserveerd`.`Meters` AS `Meterbesteld`, `gereserveerd`.`ID` AS `ResID` FROM `gereserveerd` INNER JOIN `stoffen` ON `stoffen`.`ID`=`gereserveerd`.`StofID` LEFT JOIN `gebruiker` ON `gebruiker`.`ID`=`gereserveerd`.`GebruikerID` RIGHT JOIN `stoftype` ON `stoffen`.`Type`=`stoftype`.`ID` WHERE `gebruiker`.`ID`='.$id;

    $list = [];

    $result = mysqli_query($db, $doquery); 

    while($row = mysqli_fetch_assoc($result))
    {
        $list[] = $row;
    }

    mysqli_close($db);
    return $list;
}

function database_categorys($type, $catid = 0)
{
    $db = database_connect();
    if($type == 0) {
        $doquery = 'SELECT * FROM `stoftype`';
    } else if($type == 1) {
        $doquery = 'SELECT * FROM `stoftype` WHERE `ID`='.$catid;
    }    

    $category = [];

    $result = mysqli_query($db, $doquery); 

    while($row = mysqli_fetch_assoc($result))
    {
        $category[] = $row;
    }

    mysqli_close($db);
    return $category;
}

//geeft users terug
function database_users($type = 0, $row = 0)
{
	//global $errors;
	$db = database_connect();
    if($type == 0){ //haalt alles op 
        
        $doquery = 'SELECT * FROM `gebruiker`';
    }else if($type == 1){ //haal 1 rij op
        $doquery = 'SELECT `ID`, `Naam`, `Email`, `Beschrijving`, `Foto`, `Admin` FROM `gebruiker` WHERE `ID`='.$row;
    }
    $users = [];

    $result = mysqli_query($db, $doquery); 

    while($row = mysqli_fetch_assoc($result))
    {
       $users[] = $row;
    }
    mysqli_close($db);
    return $users;
}

//snijd de meters van de totaal aantal meters af, en toont dan de beschikbare meters
function database_getMetersAv($rowid)
{
	$db = database_connect();
        
    $doquery = 'SELECT `Meters` FROM `stoffen` WHERE `ID`='. $rowid;
    $doquery2 = 'SELECT `Meters` FROM `gereserveerd` WHERE `StofID`='. $rowid;

    $result = mysqli_query($db, $doquery); 

    //haal totaal meter op
    while($row = mysqli_fetch_assoc($result))
    {
        $totalmeters = $row['Meters'];
    }

	$rmeters = [];		
	
	//kijkt naar hoeveel meter er is besteld 
    $result = mysqli_query($db, $doquery2); 

    while($row = mysqli_fetch_assoc($result))
    {
        $rmeters[] = $row['Meters'];
    }
    mysqli_close($db);

    if(!empty($rmeters) && count($rmeters) > 0)   
    {
    	$reservedmeters = 0;
    	for($i = 0; $i < count($rmeters); $i++)
    	{
    		$reservedmeters = $reservedmeters + $rmeters[$i];
    	}
    	$reservedmeters = $totalmeters - $reservedmeters;
    	return $reservedmeters;
    }else{
    	return $totalmeters;
    }
}

//rij aanmaken
function database_create($type)
{
    $db = database_connect();
    if($type == 0) //categoriëen
    {
        $naam = mysqli_escape_string($db, $_POST['inputnaam']);

        $doquery = "INSERT INTO `stoftype` (`Naam`) VALUES ('".$naam."')";

        if(mysqli_query($db, $doquery))
        {
            $naam = '';
            mysqli_close($db);
            return true;
        }else{
            mysqli_close($db);
            return false;
        }
    }
    else if($type == 1) //items
    {
        $naam = mysqli_escape_string($db, $_POST['inputnaam']);
        $stoftype = mysqli_escape_string($db, $_POST['inputstoftype']);
        $plaatje = mysqli_escape_string($db, $_POST['inputplaatje']);
        $beschrijving = mysqli_escape_string($db, $_POST['inputbeschrijving']);
        $meters = mysqli_escape_string($db, $_POST['inputmeters']);
        $prijs = mysqli_escape_string($db, $_POST['inputprijs']);
        
        $meters = floatval(str_replace(",",".", $meters));
        $prijs = floatval(str_replace(",",".", $prijs));

        $doquery = "INSERT INTO `stoffen` (`Naam`, `Type`, `Meters`, `Beschrijving`, `Plaatje`, `Prijs`) VALUES ('".$naam."', '".$stoftype."', '".floatval($meters)."', '".$beschrijving."', '".$plaatje."', '".floatval($prijs)."')";

        if(mysqli_query($db, $doquery))
        {
            $id = '';
            $naam = '';
            $plaatje = '';
            $beschrijving = '';
            $meters = '';
            $prijs = '';
            mysqli_close($db);
            return true;
        }else{
            mysqli_close($db);
            return false;
        }
    }
    else if($type == 2) //reserveringen
    {
        $id = mysqli_escape_string($db, $_POST['id']);
        $meter = mysqli_escape_string($db, $_POST['meter']);
        $doquery = "INSERT INTO `stoffig`.`gereserveerd` (`StofID`, `GebruikerID`, `Meters`) VALUES ('".$id."', '".$_SESSION['ID']."', '".$meter."')";
        if(mysqli_query($db, $doquery))
        {
            $id = '';
            $meter = '';
            mysqli_close($db);
            return true;
        }else{
            mysqli_close($db);
            return false;
        }
    }
}

//rij wijzigen
function database_modify($type, $row)
{
    $db = database_connect();
	if($type == 0) //categoriëen
    {
        $id = mysqli_escape_string($db, $row);
        $naam = mysqli_escape_string($db, $_POST['inputnaam']);

        $doquery = "UPDATE `stoftype` SET Naam='".$naam."' WHERE ID=".$id;

        if(mysqli_query($db, $doquery))
        {
            $id = '';
            $naam = '';
            mysqli_close($db);
            return true;
        }else{
            mysqli_close($db);
            return false;
        }
    }
    else if($type == 1) //items
	{
		$id = mysqli_escape_string($db, $row);
	    $naam = mysqli_escape_string($db, $_POST['inputnaam']);
	    $stoftype = mysqli_escape_string($db, $_POST['inputstoftype']);
        $plaatje = mysqli_escape_string($db, $_POST['inputplaatje']);
	    $beschrijving = mysqli_escape_string($db, $_POST['inputbeschrijving']);
	    $meters = mysqli_escape_string($db, $_POST['inputmeters']);
	    $prijs = mysqli_escape_string($db, $_POST['inputprijs']);
	    
	    $meters = floatval(str_replace(",",".", $meters));
		$prijs = floatval(str_replace(",",".", $prijs));

		$doquery = "UPDATE `stoffen` SET Naam='".$naam."', Type='".$stoftype."', Plaatje='".$plaatje."',  Beschrijving='".$beschrijving."', Meters='".floatval($meters)."', Prijs='".floatval($prijs)."' WHERE ID=".$id;

        if(mysqli_query($db, $doquery))
        {
        	$id = '';
            $naam = '';
            $plaatje = '';
            $beschrijving = '';
            $meters = '';
            $prijs = '';
            mysqli_close($db);
            return true;
        }else{
        	mysqli_close($db);
            return false;
        }
	}
	else if($type == 2) //reserveringen
	{
        //is er niet
	}
	else if($type == 3) //gebruikers
	{
        $id = mysqli_escape_string($db, $row);
        $admin = mysqli_escape_string($db, $_POST['inputadmin']);
        $doquery = "UPDATE `gebruiker` SET Admin=".$admin." WHERE ID=".$id;
        
        if(mysqli_query($db, $doquery))
        {
            $id = '';
            $admin = '';
            mysqli_close($db);
            return true;
        }else{
            mysqli_close($db);
            return false;
        }
	}
}

function database_delete($type, $row)
{
    $db = database_connect();
    $id = mysqli_escape_string($db, $row);
	if($type == 0) //categoriëen
    {
        $doquery = 'DELETE FROM `stoftype` WHERE `ID`='.$id;  
    }
    else if($type == 1) //items
    {
		$doquery = 'DELETE FROM `stoffen` WHERE `ID`='.$id;
	}
	else if($type == 2) //reserveringen
	{
		$doquery = 'DELETE FROM `gereserveerd` WHERE `ID`='.$id;
	}
	else if($type == 3) //gebruikers
	{
		$doquery = 'DELETE FROM `gebruiker` WHERE `ID`='.$id;
	}
    mysqli_query($db, $doquery);
    mysqli_close($db);
    return false;
}

//receptie van inloggen/registeren
function database_account($type = 0, $email, $ww)
{	
	global $errors;
	$db = database_connect();
    
	$esc_email = mysqli_escape_string($db, $email);
	$esc_passw = mysqli_escape_string($db, $ww);
	
	$checkquery = 'SELECT `ID`, `Email`, `Wachtwoord`, `Admin` FROM `gebruiker`';

	$found = false;

	$checkresult = mysqli_query($db, $checkquery); 

	if($type == 0) //log in account
	{
	    while($row = mysqli_fetch_assoc($checkresult))
	    {
	        if($row['Email'] == $esc_email)
	        {
	        	if(password_verify($esc_passw, $row['Wachtwoord']))
	        	{
	        		$found = true;
	        		$_SESSION['ID'] = $row['ID'];
	        		$_SESSION['email'] = $row['Email'];
	        		$_SESSION['admin'] = $row['Admin'];
	        		mysqli_close($db);
	        		
	        		header("Location: receptie.php?message=login");
	        		exit;
	        	}else{
					$errors[] = "Uw wachtwoord is onjuist";
					mysqli_close($db);
				}
			}
	    }

	    if(!$found)
	   	{
			$errors[] = "Uw email kan niet worden gevonden in het database";
	   	}
	}else{ //create new account
		if(!empty($esc_email))
		{
			if(!empty($esc_email))
			{
				while($row = mysqli_fetch_assoc($checkresult))
			    {
			    	if($row['Email'] == $esc_email)
			    	{
			    		$found = true;
			    	}
		        }

		        if(!$found)
		        {
		        	$wwhashed = password_hash($esc_passw, PASSWORD_BCRYPT);

		        	$doquery = "INSERT INTO `gebruiker` (`Email`, `Wachtwoord`) VALUES ('".$esc_email."', '".$wwhashed."')";
		        	if(mysqli_query($db, $doquery))
			        {
			        	$newcheckresult = mysqli_query($db, $checkquery);
			        	while($row = mysqli_fetch_assoc($newcheckresult)) //direct after register account logs in
					    {
					    	if($row['Email'] == $esc_email)
					    	{
					    		$_SESSION['ID'] = $row['ID'];
			        			$_SESSION['email'] = $row['Email'];
			        			$_SESSION['admin'] = $row['Admin'];
					    	}
				        }
			            mysqli_close($db);
			            header("Location: receptie.php?message=register");
			        	exit;
			        }else{
			        	mysqli_close($db);
			            $errors[] = "Verzending naar database mislukt";
			        }
		        }else{
		        	$errors[] = "Deze email is al in gebruik";
		        }
	        }else{
    			$errors[] = "U heeft geen wachtwoord opgegeven";
    		}
    	}else{
    		$errors[] = "U heeft geen email opgegeven";
    	}
        
	}
    mysqli_close($db);
}