PhpMyNewsLetter 2.0

 <?php
if(!empty($_GET['m'])&&!empty($_GET['h'])&&!empty($_GET['l'])&&!empty($_GET['r'])){
	include("_loader.php");
	foreach($_GET as $key=>$value){
		$$key = $cnx->CleanInput($value);
	}
	$r = urldecode($r);
	$row_config_globale = $cnx->SqlRow("SELECT * FROM ".$table_global_config);
	$row_id = $cnx->query("SELECT id FROM ".$row_config_globale['table_track_links']." 
				WHERE list_id ='".$l."'
					AND msg_id='".$m."'
					AND hash  ='".$h."'
					AND link  ='".$r."'")->fetchAll();
	$nb_result=count($row_id);
	if($nb_result==0){
		$cnx->query("INSERT INTO ".$row_config_globale['table_track_links']."(list_id,msg_id,link,hash,cpt,dt_track_link)
				VALUES ('".$l."','".$m."','".$r."','".$h."','1',now())");
	}elseif($nb_result==1){
		$cnx->query("UPDATE ".$row_config_globale['table_track_links']."
				SET cpt=cpt+1,dt_track_link=now()
			WHERE list_id ='".$l."' 
				AND msg_id='".$m."' 
				AND hash  ='".$h."' 
				AND link  ='".$r."'");
	}
}
include("_loader.php");
$row_config_globale = $cnx->SqlRow("SELECT * FROM ".$table_global_config);
$filesize=filesize('blank.gif');
//********************************************************* Recherche IP
if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
		$ip = $_SERVER['HTTP_CLIENT_IP'];
	} elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
		$ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
	} else {
		$ip = $_SERVER['REMOTE_ADDR'];
	}
	$urlGoogle = gethostbyaddr($ip);
	$pattern = '/(.*)\.google\.com$/';
	preg_match($pattern, $urlGoogle , $matchesGoogle);
	$isGoogle = strstr($urlGoogle ,'google.com');;
	if(count($matchesGoogle)>0&&$isGoogle!=FALSE) {
		$ip='IP Gmail Proxy';
	}
//******************************************************** Rechercher  du navigateur

	include('include/lib/class.browser.php');
	$this_browser = new Browser();
	$browser	  = $this_browser->getBrowser();
	$browser_version  = $this_browser->getVersion();
	$browser_platform = $this_browser->getPlatform();
	if ( $browser_platform == 'iPhone' || $browser_platform == 'iPad' ) {
		$browser_platform = 'iOS';
	}
	if ( $browser_platform == 'Apple' ) {
		$browser_platform = 'macOS';
	}
	$browser_user_agent=$this_browser->getUserAgent();
	require_once 'include/lib/class.mobile.php';
	$detect = new Mobile_Detect;
	$devicetype = ( $detect->isMobile() ? 'mobile' : ( $detect->isTablet() ? 'tablet' : 'computer' ) );
//******************************************************* Rechercher  si deja inscrit
        $sql="SELECT id
			FROM ".$row_config_globale['table_tracking']."
		WHERE hash='".$_GET['h']."'
			AND ip = '".$ip."'
			AND devicetype = '".$devicetype."'
			AND subject = (
				SELECT id 
					FROM ".$row_config_globale['table_archives']."
				WHERE id='".$m."')";
	$row_id = $cnx->query($sql)->fetchAll();
	$nb_result=count($row_id);
//********************************************************  Geo localisation
	include("geoloc/geoipcity.inc");
	include("geoloc/geoipregionvars.php");
	$gi = geoip_open(realpath("geoloc/GeoLiteCity.dat"),GEOIP_STANDARD);
	$record = geoip_record_by_addr($gi,$ip);
//********************************************************  si pas inscrit
 if( $nb_result==0 ) {
$cnx->query("INSERT INTO ".$row_config_globale['table_tracking']."
				 (hash,subject,date,open_count,ip,browser,
				 version,platform,useragent,devicetype,
				 lat,lng,city,postal_code,region,country) 
			 VALUES 
				 ('".$_GET['h']."','".$m."',NOW(),'1','".$ip."','".$browser."',
				  '".@$browser_version."','".@$browser_platform."','".@$browser_user_agent."','".@$devicetype."',
				  '".@$record->latitude ."','".@$record->longitude ."','".@addslashes(htmlspecialchars($record->city)) ."',
				  '".@$record->postal_code ."','".@addslashes(htmlspecialchars($GEOIP_REGION_NAME[$record->country_code][$record->region]))."',
				  '".@addslashes(htmlspecialchars($record->country_name))."')");

 }  //if( $nb_result==0 )
//********************************************************  si inscrit  on modifie le Nb de vues
 elseif( $nb_result==1 ) {
	   $cnx->query("UPDATE ".$row_config_globale['table_tracking']." 
		SET date = NOW(),
			open_count = open_count+1,
			ip = '".$ip."',
			browser = '".$browser."',
			version = '".$browser_version."',
			platform = '".$browser_platform."',
			useragent = '".$browser_user_agent."',
			devicetype = '" . $devicetype . "'
		WHERE hash='".$_GET['h']."' AND subject='".$m."'");
	}
//***********************************************Fin 



$redirect = urldecode(htmlspecialchars_decode($_GET['r']));
header("Location:$redirect");
?>