<?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;
	$contenuto_query = [];
	$nrows_query = 0;
	$error_message = '';

	try {
		$stmt = $db->prepare($sql);
		$stmt->execute($arraydati);
		$contenuto_query = $stmt->fetchAll(PDO::FETCH_ASSOC);
		$nrows_query = $stmt->rowCount();
		$status = 1;
	} catch (PDOException $e) {
		$error_message = $e->getMessage();
		echo "ERRORE DB: " . $error_message;
	}

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

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 getBrowser()
{
	$u_agent = $_SERVER['HTTP_USER_AGENT'];
	$bname = 'Unknown';
	$platform = 'Unknown';
	$version = "";

	//First get the platform?
	if (preg_match('/linux/i', $u_agent)) {
		$platform = 'linux';
	} elseif (preg_match('/macintosh|mac os x/i', $u_agent)) {
		$platform = 'mac';
	} elseif (preg_match('/windows|win32/i', $u_agent)) {
		$platform = 'windows';
	}

	// Next get the name of the useragent yes seperately and for good reason
	if (preg_match('/MSIE/i', $u_agent) && !preg_match('/Opera/i', $u_agent)) {
		$bname = 'Internet Explorer';
		$ub = "MSIE";
	} elseif (preg_match('/Trident/i', $u_agent)) { // this condition is for IE11
		$bname = 'Internet Explorer';
		$ub = "rv";
	} elseif (preg_match('/Firefox/i', $u_agent)) {
		$bname = 'Mozilla Firefox';
		$ub = "Firefox";
	} elseif (preg_match('/Chrome/i', $u_agent)) {
		$bname = 'Google Chrome';
		$ub = "Chrome";
	} elseif (preg_match('/Safari/i', $u_agent)) {
		$bname = 'Apple Safari';
		$ub = "Safari";
	} elseif (preg_match('/Opera/i', $u_agent)) {
		$bname = 'Opera';
		$ub = "Opera";
	} elseif (preg_match('/Netscape/i', $u_agent)) {
		$bname = 'Netscape';
		$ub = "Netscape";
	}

	// finally get the correct version number
	// Added "|:"
	$known = array('Version', $ub, 'other');
	$pattern = '#(?<browser>' . join('|', $known) .
		')[/|: ]+(?<version>[0-9.|a-zA-Z.]*)#';
	if (!preg_match_all($pattern, $u_agent, $matches)) {
		// we have no matching number just continue
	}



	// check if we have a number
	if ($version == null || $version == "") {
		$version = "?";
	}

	return array(
		'userAgent' => $u_agent,
		'name' => $bname,
		'ub' => $ub,
		'version' => $version,
		'platform' => $platform,
		'pattern' => $pattern
	);
}




/*

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`);





*/
?>