<?php
require_once("config_db.php");
date_default_timezone_set('Europe/Rome');

try {
	$db = new PDO("mysql:host=$set_db_host;dbname=$set_db_database;charset=utf8mb4", $set_db_username, $set_db_passwd, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",PDO::MYSQL_ATTR_FOUND_ROWS => true));
	$db->exec("set names utf8mb4");
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
	$errore = $e->getMessage();
	echo "<p style='text-align:center;'>";
		echo "ERRORE DATABASE"; 
		echo "<br><b>$errore</b> "; 
	echo "</p>";
	//echo "<pre>"; print_R($e); echo "</pre>"; 
	 
	exit; // ridirigere verso pagina di errore
} 

 
class WriteDBException extends Exception
{
    public function __construct($message, $code = 0, Exception $previous = null) {
         parent::__construct($message, $code, $previous);
    }
 
}

class ReadDBException extends Exception
{
    public function __construct($message, $code = 0, Exception $previous = null) {
         parent::__construct($message, $code, $previous);
    }
 
}

class NumRowInvalidException extends Exception
{
    public function __construct($message, $code = 0, Exception $previous = null) {
         parent::__construct($message, $code, $previous);
    }
 
}

class EmailFailedException extends Exception
{
    public function __construct($message, $code = 0, Exception $previous = null) {
         parent::__construct($message, $code, $previous);
    }
 
}
class GenericException extends Exception
{
    public function __construct($message, $code = 0, Exception $previous = null) {
         parent::__construct($message, $code, $previous);
    }
 
}
//******************************************** funzioni DB
function read_db($sql,$connessione){
	$contenuto_query=Array();
	$nrows_query=0;
	$dbconnect = @mysqli_connect($connessione['host'],$connessione['username'],$connessione['passwd'],$connessione['database']);
	
	if ((!$dbconnect) || ($sql =="")){
		//die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
	} else {
    
		$result_query = @mysqli_query($dbconnect, $sql);
		if ($result_query != false) { 
			$nrows_query = @mysqli_num_rows($result_query); 
			while ($row = mysqli_fetch_array($result_query, MYSQLI_ASSOC)) { 
					array_push($contenuto_query, $row);
						}
		} 
	//mysqli_free_result($result_query);
	}
	return array($contenuto_query,$nrows_query);
}

function read_db_pdo($sql,$arraydati = NULL){
	global $db;

	$status =0;
		try {
		    $stmt = $db->prepare($sql);
		    $stmt->execute($arraydati); 
		    $contenuto_query = $stmt->fetchAll(PDO::FETCH_ASSOC);
			//$contenuto_query = $stmt->fetchAll(); 

		 	$nrows_query = $stmt->rowCount();
		 	$status =1;
		} catch(PDOException $e) {
 

				echo "ERRORE DB" ;

			
		    
 
		    //$errori[] = $e->getMessage();
		}
		 	
	return array($status,$contenuto_query,$nrows_query);
}

function write_db_pdo($sql,$arraydati = NULL){
	//show_array($arraydati);
	global $db;

	$status =0;
		try {
		    $stmt = $db->prepare($sql);
		    $stmt->execute($arraydati); 
 			$nrows_query = $stmt->rowCount();	
 			$status =1;	  
		} catch(PDOException $e) {

				echo "<br>ERRORE DB" ;
				echo $e->getMessage();
			
 
 
		    //$errori[] = $e->getMessage();
		}
	return array($status,$nrows_query);
} 

 
function write_db_pdo_trans($sql,$arraydati = NULL){
	global $db;
	//auto commit false
	//show_array($arraydati);
	$status =0;
		try {
		    $stmt = $db->prepare($sql);
			
		    $stmt->execute($arraydati); 
 			$nrows_query = $stmt->rowCount();	
 			$status =1;	  
		} catch(PDOException $e) {
 
		      throw new WriteDBException( $e->getMessage( ) , (int)$e->getCode( ) );

		}
	return array($status,$nrows_query);
}

function read_db_pdo_trans($sql,$arraydati = NULL){
	global $db;
	$status =0;
		try {
		    $stmt = $db->prepare($sql);
			
		    $stmt->execute($arraydati); 
		    $contenuto_query = $stmt->fetchAll(PDO::FETCH_ASSOC);
			//$contenuto_query = $stmt->fetchAll(); 
           
		 	$nrows_query = $stmt->rowCount();
		 	$status =1;
		} catch(PDOException $e) {
			throw new ReadDBException( $e->getMessage( ) , (int)$e->getCode( ) );
		}
		 	
	return array($status,$contenuto_query,$nrows_query);
}

//** fine funz db */


function check_admin_failed_login(){
		global $db;
		
		$ora_atturale = time();
		$time_to_add = "-5 minutes ";
		$exp_date = strtotime($time_to_add, $ora_atturale);

		$array_sql_delete = array();
		$sql_delete="DELETE  FROM secure_login where  dataora < $exp_date  ";
		list($status_sql_delete,$nrows_delete) = write_db_pdo($sql_delete,$array_sql_delete);
				
		
		
		$ip_address = $_SERVER['REMOTE_ADDR'];
		$array_tentativi_login = array(':ip_address'   => $ip_address  );
		$sql_tentativi_login ="select *  FROM secure_login WHERE ip_address=:ip_address AND fallito = 1";
		list($status_tentativi_login,$contenuto_tentativi_login,$nrows_tentativi_login) = read_db_pdo($sql_tentativi_login,$array_tentativi_login);
		if($status_tentativi_login !=1) { return $nrows_tentativi_login=-1; }
		
		return $nrows_tentativi_login;
} 

function check_failed_login(){
		global $db;
		
		$ora_atturale = time();
		$time_to_add = "-5 minutes ";
		$exp_date = strtotime($time_to_add, $ora_atturale);

		$array_sql_delete = array();
		$sql_delete="DELETE  FROM user_login where  dataora < $exp_date  ";
		list($status_sql_delete,$nrows_delete) = write_db_pdo($sql_delete,$array_sql_delete);
				
		
		
		$ip_address = $_SERVER['REMOTE_ADDR'];
		$array_tentativi_login = array(':ip_address'   => $ip_address  );
		$sql_tentativi_login ="select *  FROM user_login WHERE ip_address=:ip_address AND fallito = 1";
		list($status_tentativi_login,$contenuto_tentativi_login,$nrows_tentativi_login) = read_db_pdo($sql_tentativi_login,$array_tentativi_login);
		if($status_tentativi_login !=1) { return $nrows_tentativi_login=-1; }
		
		return $nrows_tentativi_login;
} 



function write_admin_user_login($username,$fallito=1) {
	global $db;
	$ip_address = $_SERVER['REMOTE_ADDR'];
	$array_insert = array(':fallito'   => $fallito, ':username'   => $username,
	':ip_address'   => $ip_address, ':dataora'   => time());
	$sql_insert = "INSERT INTO secure_login set username=:username,
		ip_address= :ip_address,dataora=:dataora,fallito=:fallito";
	list($status_insert,$nrows_insert) = write_db_pdo($sql_insert,$array_insert);
}


function write_user_login($username,$fallito=1) {
	global $db;
	$ip_address = $_SERVER['REMOTE_ADDR'];
	$array_insert = array(':fallito'   => $fallito, ':username'   => $username,
	':ip_address'   => $ip_address, ':dataora'   => time());
	$sql_insert = "INSERT INTO user_login  set username=:username,
		ip_address= :ip_address,dataora=:dataora,fallito=:fallito";
	list($status_insert,$nrows_insert) = write_db_pdo($sql_insert,$array_insert);
}


function crea_chk_link($sezione_sito,$id_elemento) {
	global $id_user_logged;
	$oggi = date("m/d/Y");
	$phpsession_lock = session_id();
	$chk_link=sha1(md5($id_user_logged.$oggi.$sezione_sito.$id_elemento.$phpsession_lock));
//	$chk_link=sha1($chk_link);
	return	$chk_link;
}

function verifica_chk_link($sezione_sito,$id_elemento,$chk) {
	 global $id_user_logged;
	 $oggi = date("m/d/Y");
 	 $phpsession_lock = session_id();
	 $chk_link=sha1(md5($id_user_logged.$oggi.$sezione_sito.$id_elemento.$phpsession_lock));
	 //echo "------>". $id_user_logged.$oggi.$sezione_sito.$id_elemento.$phpsession_lock."<------";
	 if ($chk == $chk_link ) {
	 return true;	 
	 } else {
	 return false;
	 }
 
} 

function show_array($array){
	echo "<pre>"; 
	print_R($array);
	echo "</pre>"; 

}

function kritta($stringa){
	global $set_server_enc_key;
	$sslc = openssl_encrypt($stringa,"AES-128-ECB",$set_server_enc_key);
	return bin2hex($sslc);
	
}
function dekritta($stringa){
	global $set_server_enc_key;
	$stringa = hex2bin($stringa);
	return $sslc = openssl_decrypt($stringa,"AES-128-ECB",$set_server_enc_key);
	 
	
}



function validateDate($date, $format = 'Y-m-d H:i:s')
{
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) == $date;
}


function close_errore($messaggio,$uscita=0) {

echo " <br/>
					<div class='row'>
                      <div class='col-md-6 offset-3'>
<div class='alert alert-fill-danger' role='alert'>
                    <i class='mdi mdi-alert-circle'></i>
                    $messaggio
                  </div>
</div>
</div>
				  ";
if($uscita==1){
	
	exit;
}
				  
}


function true_false_chk($var){
	if (isset($var)){
		$var = "true";
	}else{
		$var = "false";
	}
	return $var;
}
function true_false_n($var){
	if ($var == "true"){
		$var = 1;
	}else{
		$var = 0;
	}
	return $var;
}
function true_false_s($var){
	if ($var == 1){
		$var = "true";
	}else{
		$var = "false";
	}
	return $var;
}
function strbool($var){
	if ($var == 1){
		$var = "checked";
	}else{
		$var = "";
	}
	return $var;
}
function get_client_ip() {
    $ipaddress = '';
    if (getenv('HTTP_CLIENT_IP'))
        $ipaddress = getenv('HTTP_CLIENT_IP');
    else if(getenv('HTTP_X_FORWARDED_FOR'))
        $ipaddress = getenv('HTTP_X_FORWARDED_FOR');
    else if(getenv('HTTP_X_FORWARDED'))
        $ipaddress = getenv('HTTP_X_FORWARDED');
    else if(getenv('HTTP_FORWARDED_FOR'))
        $ipaddress = getenv('HTTP_FORWARDED_FOR');
    else if(getenv('HTTP_FORWARDED'))
       $ipaddress = getenv('HTTP_FORWARDED');
    else if(getenv('REMOTE_ADDR'))
        $ipaddress = getenv('REMOTE_ADDR');
    else
        $ipaddress = 'UNKNOWN';
    return $ipaddress;
}
 
function log_operazione($id_utente, $descrizione){
	$data_operazione = date("Y-m-d H:i:s");  
	$ip_address = get_client_ip();
	
		$array_log = array(
						'id_utente' => $id_utente,
						'ip_address' => $ip_address,		
						'data_operazione' => $data_operazione,
						'descrizione' => $descrizione								
						);
		$sql_log="INSERT INTO log_operazioni SET 
											id_utente=:id_utente,
											ip_address=:ip_address,
											data_operazione=:data_operazione,
											descrizione=:descrizione													
											";
		list($status_sql_log,$nrows_log) = write_db_pdo_trans($sql_log,$array_log);
		if ($status_sql_log ==0){
			return "ERRORE DURANTE IL LOG";
		}else{
			return 1;
		}
}


function generateRandomString($length = 10) {
    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $charactersLength = strlen($characters);
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, $charactersLength - 1)];
    }
    return $randomString;
}


function isChildren($id_padre, $id_figlio){
	$sottoposti = buildStaffTree(strval($id_padre));
	if(count($sottoposti)>0) {
		$array_figli = flatten($sottoposti);
		$key = array_search($id_figlio, $array_figli);
		if($key != ""){
			return 1;
		}else{
			return 0;
		}
	}else{
		return 0;
	}
}


function flatten(array $array) {
    $return = array();
    array_walk_recursive($array, function($a) use (&$return) { $return[] = $a; });
    return $return;
}

function buildStaffTree($managerId = null) {
	global $staff;
	global $filtro_query_rivenditori;
	global $vettore_permessi;
	global $not_include;
	//if ($managerId != $not_include){
		$filtro_query_rivenditori .="$managerId,";
	//}
	$subordinates = array_values(array_filter(
		$staff,
		function ($staffMember) use ($managerId) {
			return $staffMember['id_superiore'] === $managerId;
		}
	));

	if ($managerId === null) {
		// Tree root - only enumerate the top-level managers
		return array_map(
			function ($subordinate) {
				return buildStaffTree($subordinate['id_rivenditore']);
			},
			$subordinates
		);
	} else {
		$manager = array_values(array_filter(
			$staff,
			function ($staffMember) use ($managerId) {
				return $staffMember['id_rivenditore'] === $managerId;
			}
		))[0];
	
		
		$permessi = $manager['permessi'];
		$permessi_video = $vettore_permessi[$permessi];
		return [
			'id_rivenditore'      => $manager['id_rivenditore'],
	
			'manages' => array_map(
				function ($subordinate) {
					return buildStaffTree($subordinate['id_rivenditore']);
				},
				$subordinates
			)
		];
	}
}





/*

function insert_user_last_activity_time($id_utente) {
			
			//$id_utente = $_SESSION["DAST"]['info_utente_logged']["id_utente"];

// inserire query cancellazion login piu' vecchi di 8 ore.


// query conta quante presenti nel db
 //se < 2
			$array_sql_activity = array("time_activity"=>time(), "id_persona_activity"=>$id_utente);
			$sql_activity = "INSERT INTO users_activity SET id_persona_activity=:id_persona_activity, time_activity=:time_activity";
			list($status_sql_activity,$nrows_sql_activity) = write_db_pdo($sql_activity,$array_sql_activity);
			
// else logout


			
			
}
/*
insert_user_last_activity_time();

CREATE TABLE IF NOT EXISTS `users_activity` (
  `id_persona_activity` int(10) NOT NULL,
  `time_activity` double NOT NULL,
  `timestamp_activity` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users_activity`
  ADD PRIMARY KEY (`id_persona_activity`),
  ADD UNIQUE KEY `id_persona` (`id_persona_activity`);





*/
?>