Location: PHPKode > projects > vStats > functions.php
<?
/******************************************************************************
 *	Function class for vStats 
 *	(c) by Christian Goesswein - hide@address.com
 *
 *	$Revision: 48 $
 *	$Date: 2006-06-02 09:47:20 +0200 (Fr, 02 Jun 2006) $
 *	$Author: dc_d00de $
 *	$Id: functions.php 48 2006-06-02 07:47:20Z dc_d00de $
 *
 ******************************************************************************/

class func{

	var $queries;
	var $querytime;
	var $starttime;
	var $percent;
	var $errorimg;
	var $cookieexpire;
	var $cookiepath;
	var $cookiedomain;
	var $mylink;
	var $insert_id;

	function func($img){
		$this->errorimg=$img;
		$this->queries=0;
		$this->querytime=0;
		$this->percent=0;
		
		$mtime = microtime();
		$mtime = explode(' ',$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$this->starttime = $mtime;
	}

	function onexit(){
		$this->dbclose();
	}

	function get_queries(){
		return $this->queries;
	}

	function get_querytime(){
		return $this->querytime;
	}

	function get_totaltime(){
		$mtime = microtime();
		$mtime = explode(' ',$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;
		$totaltime = round(($endtime - $this->starttime),5);
		$this->percent=round(($this->querytime/($totaltime/100)),2);
		return $totaltime;
	}

	function get_percent(){
		return $this->percent;
	}

	function dbquery($sql){
		require('./config.php');

		//-----------------------------------------------------------
		// Initialize vars for the querytime calculation.
		$mtime = microtime();
		$mtime = explode(' ',$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;
		//-----------------------------------------------------------

		if(!isset($this->mylink)){
			if(!$this->mylink = mysql_connect ($dbhost,$dbuser,$dbpassword)){
				$this->error="MySQL connection error!";
				$this->print_error($this->error,$this->errorimg);
				die;
			}
		}
		if(!mysql_select_db($dbname)){
			$this->error="MySQL DB error: ".mysql_error();
			$this->print_error($this->error,$this->errorimg);
			die;
		}
		if(!$result = mysql_query($sql, $this->mylink)){
			$this->error="MySQL query error: ".mysql_error()." ".$sql;
			$this->print_error($this->error,$this->errorimg);
			die;
		}
		$this->insert_id=mysql_insert_id();

		//-----------------------------------------------------------
		// Calculate the querytime.
		$mtime = microtime();
		$mtime = explode(' ',$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;
		$this->querytime += round(($endtime - $starttime),5);
		//-----------------------------------------------------------

		$this->queries++;
		
		// Optimize table after a delete.
		if(preg_match("/^DELETE FROM (.*?) WHERE.*?$/",$sql,$matches)){
			$tmpresult=$result;
			$sql="OPTIMIZE TABLE ".$matches[1];
			$this->dbquery($sql);
			$result=$tmpresult;
		}
		return $result;
	}

	function getInsertID(){
		return $this->insert_id;
	}

	function dbclose(){
		if(isset($this->mylink)){
			mysql_close($this->mylink);
		}
	}

	function getDomain($hash){
		if(preg_match("/^[0-9a-f]+$/i",$hash)){
			$sql="SELECT id FROM tbf_domains WHERE hash='".$hash."'";
			$result=$this->dbquery($sql);
			while($data=mysql_fetch_array($result)){
				return $data['id'];
			}
		}
		return 0;
	}

	function createNewDomain($user_id){
		$name=htmlentities(addslashes($_POST['name']));
		$newhash=md5(time().$name.time());
		$sql="INSERT INTO tbf_domains VALUES ('','".$name."','".$newhash."')";
		$this->dbquery($sql);
		$domain_id=$this->getInsertID();

		$sql="INSERT INTO tbf_user_domains VALUES ('".$user_id."','".$domain_id."')";
		$this->dbquery($sql);

		// Insert the default config values for the new domain
		$sql="INSERT INTO `tbf_config` (`config_name`, `config_value`, `domain_id`) VALUES 
				('background_color','#FFFFFF','".$domain_id."'),
				('foreground_color','#000000','".$domain_id."'),
				('idle_time','1','".$domain_id."'),
				('show_in_detail','25','".$domain_id."'),
				('fake_counts','1000','".$domain_id."'),
				('counter_template','set_Default','".$domain_id."'),
				('show_alltime','25','".$domain_id."'),
				('date_format','H:i d.m.Y','".$domain_id."');";
		$this->dbquery($sql);

		// Update the tbf_count_ipcountry table
		$sql="SELECT MAX(ci) FROM tbf_geoip_cc";
		$maxid=mysql_result($this->dbquery($sql),0);
		$sql="INSERT INTO `tbf_count_ipcountry` (`ci`, `domain_id`, `anzahl`)  VALUES ";
		for($i=0;$i<=$maxid;$i++){
			if($i > 0){
				$sql.=",";
			}
			$sql.="('".$i."','".$domain_id."','0')";
			
		}
		if($maxid > 0){
			$sql.=";";
			$this->dbquery($sql);
		}

		// Update the tbf_count_browser table
		$sql="SELECT MAX(id) FROM tbf_browser";
		$maxid=mysql_result($this->dbquery($sql),0);
		$sql="INSERT INTO `tbf_count_browser` (`browser_id`, `domain_id`, `anzahl`) VALUES ";
		for($i=1;$i<=$maxid;$i++){
			if($i > 1){
				$sql.=",";
			}
			$sql.="('".$i."','".$domain_id."','0')";
			
		}
		if($maxid > 0){
			$sql.=";";
			$this->dbquery($sql);
		}

		// Update the tbf_count_os table
		$sql="SELECT MAX(id) FROM tbf_os";
		$maxid=mysql_result($this->dbquery($sql),0);
		$sql="INSERT INTO `tbf_count_os` (`os_id`, `domain_id`, `anzahl`) VALUES ";
		for($i=1;$i<=$maxid;$i++){
			if($i > 1){
				$sql.=",";
			}
			$sql.="('".$i."','".$domain_id."','0')";
			
		}
		if($maxid > 0){
			$sql.=";";
			$this->dbquery($sql);
		}

		// Update the tbf_count_robot table
		$sql="SELECT MAX(id) FROM tbf_robot";
		$maxid=mysql_result($this->dbquery($sql),0);
		$sql="INSERT INTO `tbf_count_robot` (`robot_id`, `domain_id`, `anzahl`) VALUES ";
		for($i=1;$i<=$maxid;$i++){
			if($i > 1){
				$sql.=",";
			}
			$sql.="('".$i."','".$domain_id."','0')";
			
		}
		if($maxid > 0){
			$sql.=";";
			$this->dbquery($sql);
		}
	}

	function getDomainDropdown($uid,$domain){
		/* Return a html dropdown field with all the domains for the provided user id */
		$dropdown="";
		$first=true;
		$return['domaindropdown']="";
		$sql="SELECT d.id,d.name,d.hash FROM tbf_user_domains t, tbf_domains d WHERE t.user_id='".$uid."' AND t.domain_id=d.id";
		$result=$this->dbquery($sql);
		while($data=mysql_fetch_array($result)){
			if($domain==$data['id'] || ($domain==0 && $first)){
				$return['domaindropdown'].='<option value="'.$data['hash'].'" selected>'.stripslashes($data['name']).'</option>';	
				$first=false;
				$return['domain']=$data['id'];
				$return['domainhash']=$data['hash'];
			}
			else{
				$return['domaindropdown'].='<option value="'.$data['hash'].'">'.stripslashes($data['name']).'</option>';
			}
		}
		return $return;
	}

	function load_config($domain){
		/* load the config values for the provided domain */
		$sql="SELECT * FROM tbf_config WHERE domain_id='".$domain."'";
		$result=$this->dbquery($sql);
		$config['error']=1;
		while($data=mysql_fetch_array($result)){
			$config[$data['config_name']]=$data['config_value'];
			$config['error']=0;
		}
		return $config;
	}

	function load_browser(){
		$sql="SELECT id,regexpr FROM tbf_browser ORDER BY id ASC";
		$result=$this->dbquery($sql);
		$browser=array();
		while($data=mysql_fetch_array($result)){
			$browser[$data['id']]=$data['regexpr'];
		}
		return $browser;
	}

	function load_os(){
		$sql="SELECT id,regexpr FROM tbf_os ORDER BY id ASC";
		$result=$this->dbquery($sql);
		$os=array();
		while($data=mysql_fetch_array($result)){
			$os[$data['id']]=$data['regexpr'];
		}
		return $os;
	}

	function load_robot(){
		$sql="SELECT id,regexpr FROM tbf_robot ORDER BY id ASC";
		$result=$this->dbquery($sql);
		$robot=array();
		while($data=mysql_fetch_array($result)){
			$robot[$data['id']]=$data['regexpr'];
		}
		return $robot;
	}

	function getXORCode($browser_id,$os_id,$robot){
		$value=$browser_id ^ ($os_id<<10) ^ ($robot<<20);
		return $value;
	}

	function getXORIds($xor){
		// 0 = browser_id || robot_id
		// 1 = os_id
		// 2 = visitor is a robot (boolean)
		$tmp[0]=$xor & 1023;
		$tmp[1]=($xor & 1047552)>>10;
		$tmp[2]=($xor & 1048576)>>20;
		return $tmp;
	}

	function print_error($error,$pic){
		if($pic){
			$width=strlen($error)*8;
			$errorpic=imagecreatetruecolor($width,16);
			$back = ImageColorAllocate ($errorpic,255,255,255);
			$fore = ImageColorAllocate ($errorpic,0,0,0);
			ImageFilledRectangle ($errorpic,0,0,$width,16,$back);
			ImageString($errorpic,4,0,0,$error,$fore);
			header ("Content-type: image/png");
			imagepng($errorpic);
			imagedestroy($errorpic);
		}
		else{
			echo "<center><br>".$error."<br></center>";
		}
	}

	function login(){
		$id = -1;
		if(isset($_POST['user']) && isset($_POST['pass']) && $_POST['logincheck']=="ok"){
			$user=$_POST['user'];
			$pw=md5($_POST['pass']);
			$sql="SELECT id FROM tbf_users WHERE nick='".addslashes($user)."' AND passw = '".$pw."'";
			$result=$this->dbquery($sql);
			while ($data = mysql_fetch_array($result)){
				$id=$data['id'];
				break;
			}
		}
		else{
			// Cookie is set? => compare session with the one in the db and return the user_id (== -1 if not logged in).
			$tbfcounter="";
			if(isset($_COOKIE['tbfcounter'])){
				$tbfcounter = $_COOKIE['tbfcounter'];
				$sql="SELECT user_id FROM tbf_sessions WHERE session = '".$tbfcounter."'";
				$result=$this->dbquery($sql);
				while ($data = mysql_fetch_array($result)){	
					$id=$data['user_id'];
					break;
				}
			}
		}
		return $id;
	}

	function login_process($info){
		$user = $_POST['user'];
		$pw = md5($_POST['pass']);

		$sql="SELECT id FROM tbf_users WHERE nick='".addslashes($user)."' AND passw = '".$pw."'";
		$result=$this->dbquery($sql);
		while ($data = mysql_fetch_array($result)){
			$user_id=$data['id'];
			$zeit=time();
			$s = md5(mt_rand()+$zeit).md5(mt_rand()*$zeit);
			$sql="INSERT INTO tbf_sessions VALUES ('','".$s."','".$user_id."','".$zeit."')";
			setcookie("tbfcounter",$s,time()+$info['cookie_expire'],$info['cookie_path'],$info['cookie_domain'],0);
			$this->dbquery($sql);			
			break;
		}
	}

	function logout(){
		$sql="DELETE FROM tbf_sessions WHERE session='".$_COOKIE['tbfcounter']."'";
		$this->dbquery($sql);

		// Delete old and unused sessions.
		$zeit = time()-2592000;
		$sql="DELETE FROM tbf_sessions WHERE time<'".$zeit."'";
		$this->dbquery($sql);
		
		return -1;
	}

	function getCntTplDropdown($selected){
		$handle=@opendir("./img/sets");
		$html="";
		while ($dirs = @readdir($handle)) { 
			if(preg_match("/^set_(.*?)$/",$dirs,$matches)){
				$currentset=htmlentities($matches[1]);

				if("set_".$currentset == $selected){
					$html.='<option value="'.$currentset.'" selected>'.$currentset.'</option>';
				}
				else{
					$html.='<option value="'.$currentset.'">'.$currentset.'</option>';
				}
			}	  
		}
		@closedir($handle);
	
		/* Different languages will be supported in the final release. */
		if($selected == "text"){
			$html.='<option value="text" selected>Textual output</option>';
		}
		else{
			$html.='<option value="text">Textual output</option>';
		}
		if($selected == "none"){
			$html.='<option value="none" selected>No output</option>';
		}
		else{
			$html.='<option value="none">No output</option>';
		}	
		return $html;
	}

	function getAdmTplDropdown($selected){
		$handle=@opendir("./styles");
		$html="";
		while($dirs = @readdir($handle)) { 
			if(!preg_match("/^\..*?$/is",$dirs) && is_dir("./styles/".$dirs)){
				$currentset=htmlentities($dirs);

				if($currentset == $selected){
					$html.='<option value="'.$currentset.'" selected>'.$currentset.'</option>';
				}
				else{
					$html.='<option value="'.$currentset.'">'.$currentset.'</option>';
				}
			}	  
		}
		@closedir($handle);
		return $html;
	}

	function generatePercentagePic($percentage,$type){
		$rate=$percentage;
		$tmprating=round($rate,0);
		$xpos=2;

		if(!file_exists("./img/percentage/p_".$type."-".$rate.".png")){
			/* This will be used, if caching of the precentage pics is completly implemented. */
			/*																				  */
			/*$vorlage = @ImageCreateFromPNG ("./img/percentage/p_vorlage-".$type.".png");
			$empty = @ImageCreateFromPNG ("./img/percentage/p_vorlage-empty.png");
			if(!(!$vorlage) && !(!$empty)){
				$org_width=imagesx($vorlage);
				$org_height=imagesy($vorlage);	
				
				while($tmprating != 0){
					imagecopy($empty, $vorlage, $xpos, 1, 0, 0, $org_width, $org_height);
					$xpos=$xpos+2;
					$tmprating--;
				}
				$fore = ImageColorAllocate ($empty,0,0,0);
				ImageString($empty,1,5,3,$percentage."%",$fore);

				imagepng($empty,"./img/percentage/p_".$type."-".$rate.".png");
				imagedestroy($vorlage);
				imagedestroy($empty);
			}*/

			$pic="./../../picgen.php?p=".$percentage."&amp;t=".$type;
			return $pic;
		}
		$pic="p_".$type."-".$rate.".png";
		return $pic;
	}

	/* Return a html dropdown to select one of the available languages. */
	function getLanguageDropdown($uid){
		$return = "";

		// Get the language of the user
		$sql = "SELECT language FROM tbf_users WHERE id = '".$uid."'";
		$language = mysql_result($this->dbquery($sql),0);

		// Get the array with all available languages, build the hmtl code
		// and pre-select the user language
		include("./select_lang.php");
		$avail_langs = getAvailableLanguages();		
		ksort ($avail_langs);
		reset ($avail_langs);
		while (list ($key, $val) = each ($avail_langs)){
			if($key == $language){
				$return .= '<option value="'.$key.'" selected>'.$val.'</option>';
			}
			else{
				$return .= '<option value="'.$key.'">'.$val.'</option>';
			}
		}

		return $return;
	}

	function update_iplist(){
		if(!file_exists("./GeoIPCountryWhois.csv")){
			$this->print_error("The file 'GeoIPCountryWhois.csv' does not exist!",0);
			die;
		}

		// Save the present counts in the array [CC][Domain_id] = count
		$tmp=array();
		$sql="SELECT c.cc,i.domain_id,i.anzahl FROM tbf_count_ipcountry i, tbf_geoip_cc c WHERE i.ci=c.ci";
		$result=$this->dbquery($sql);
		while($data=mysql_fetch_array($result)){
			$tmp[$data['cc']][$data['domain_id']]=$data['anzahl'];
		}
		$sql="SELECT domain_id,anzahl FROM tbf_count_ipcountry WHERE ci=0";
		$result=$this->dbquery($sql);
		while($data=mysql_fetch_array($result)){
			$tmp['tmp'][$data['domain_id']]=$data['anzahl'];
		}

		// Delete and drop the out-dated tables
		$sql="DELETE FROM tbf_count_ipcountry";
		$this->dbquery($sql);
		$sql="DROP TABLE tbf_count_ipcountry;";
		$this->dbquery($sql);

		$sql="DELETE FROM tbf_geoip_cc";
		$this->dbquery($sql);
		$sql="DROP TABLE tbf_geoip_cc;";
		$this->dbquery($sql);

		$sql="DELETE FROM tbf_geoip_iplist";
		$this->dbquery($sql);
		$sql="DROP TABLE tbf_geoip_iplist;";
		$this->dbquery($sql);

		$sql="CREATE TABLE `tbf_geoip_cc` (
		  `ci` smallint(3) unsigned NOT NULL auto_increment,
		  `cc` char(2) NOT NULL default '',
		  `cn` varchar(50) NOT NULL default '',
		  PRIMARY KEY  (`ci`)
		) TYPE=MyISAM;";
		$this->dbquery($sql);

		$sql="CREATE TABLE `tbf_geoip_iplist` (
		  `start` int(10) unsigned NOT NULL default '0',
		  `end` int(10) unsigned NOT NULL default '0',
		  `ci` tinyint(4) NOT NULL default '0'
		) TYPE=MyISAM;";
		$this->dbquery($sql);

		$sql="CREATE TABLE `tbf_count_ipcountry` (
		  `ci` smallint(3) unsigned NOT NULL default '0',
		  `domain_id` mediumint(8) unsigned NOT NULL default '0',
		  `anzahl` int(10) unsigned NOT NULL default '0',
		  PRIMARY KEY  (`ci`,`domain_id`)
		) TYPE=MyISAM;";
		$this->dbquery($sql);

		// Create a temp table and read the CSV file into this table
		$sql="CREATE TABLE tbf_tmp_iplist (
		  start INT UNSIGNED NOT NULL,
		  end INT UNSIGNED NOT NULL,
		  cc CHAR(2) NOT NULL,
		  cn VARCHAR(50) NOT NULL
		);";
		$this->dbquery($sql);

		$handle=fopen("./GeoIPCountryWhois.csv","r");
		$sql="INSERT INTO tbf_tmp_iplist (start, end, cc,cn) VALUES ";
		$counter=0;
		while (!feof($handle)) {
			$buffer=fgets($handle);

			if(preg_match('/^"[0-9\.]+","[0-9\.]+","([0-9]+)","([0-9]+)","([a-zA-Z0-9]+)","(.*?)"$/',$buffer,$matches)){
				if($counter>0){
					$sql.=", ";
				}
				$sql.="('".$matches[1]."','".$matches[2]."','".$matches[3]."','".addslashes($matches[4])."')";
					
			}
			$counter++;

			if (bcmod($counter,5000)==0){
				$sql.=";";
				$this->dbquery($sql);
				$counter = 0;
				$sql="INSERT INTO tbf_tmp_iplist (start, end, cc,cn) VALUES ";
			}			
		}
		fclose($handle);

		if(strlen($sql) > 60){
			$sql.=";";
			$this->dbquery($sql);
		}

		// Insert data from the temp table into the tables tbf_geoip_cc and tbf_geoip_iplist
		$sql="INSERT INTO tbf_geoip_cc SELECT DISTINCT NULL,cc,cn FROM tbf_tmp_iplist;";
		$this->dbquery($sql);
		$sql="INSERT INTO tbf_geoip_iplist SELECT start,end,ci FROM tbf_tmp_iplist NATURAL JOIN tbf_geoip_cc;";
		$this->dbquery($sql);

		// Delete and drop the temp table
		$sql="DELETE FROM tbf_tmp_iplist;";
		$this->dbquery($sql);
		$sql="DROP TABLE tbf_tmp_iplist;";
		$this->dbquery($sql);

		// Insert the UNKNOWN-entry
		$sql="INSERT INTO tbf_geoip_cc VALUES('0','--','UNKNOWN')";
		$this->dbquery($sql);
		$sql="SELECT MAX(ci) FROM tbf_geoip_cc";
		$maxid=mysql_result($this->dbquery($sql),0);

		$sql="UPDATE tbf_geoip_cc SET ci=0 WHERE ci='".$maxid."'";
		$this->dbquery($sql);

		// Insert empty entries into the new tbf_count_ipcountry
		$sql="SELECT COUNT(*) FROM tbf_geoip_cc";
		$result=$this->dbquery($sql);
		$entries=mysql_result($result,0);
		$sql="SELECT id FROM tbf_domains ORDER BY id ASC";
		$result=$this->dbquery($sql);
		while($data=mysql_fetch_array($result)){
			$sql="INSERT INTO `tbf_count_ipcountry` (`ci`, `domain_id`, `anzahl`) VALUES ";
			for($i=0;$i<=$entries;$i++){
				if($i > 0){
					$sql.=",";
				}
				$sql.="('".$i."','".$data['id']."','0')";
			}
			if($entries > 0){
				$sql.=";";
				$this->dbquery($sql);
			}
		}

		// Get the ci, because in tbf_count_ipcountry only ci is used but the count array works with cc
		$ccode=array();
		$sql="SELECT ci,cc FROM tbf_geoip_cc";
		$result=$this->dbquery($sql);
		while($data=mysql_fetch_array($result)){
			$ccode[$data['cc']]=$data['ci'];
		}
		reset($ccode);

		// Insert the counts from the array into the db in tbf_count_ipcountry
		$sql="SELECT id FROM tbf_domains ORDER BY id ASC";
		$result=$this->dbquery($sql);
		while($data=mysql_fetch_array($result)){
			$ipnotfound=$tmp['tmp'][$data['id']];
			while (list ($key, $val) = each ($ccode)) {
				if(isset($tmp[$key][$data['id']])){
					$sql="UPDATE tbf_count_ipcountry SET anzahl='".$tmp[$key][$data['id']]."' WHERE ci='".$val."' AND domain_id='".$data['id']."'";
					$this->dbquery($sql);
				}
			}
			$sql="UPDATE tbf_count_ipcountry SET anzahl='".$ipnotfound."' WHERE ci='0' AND domain_id='".$data['id']."'";
			$this->dbquery($sql);
			reset($ccode);
		}
	}
}
?>
Return current item: vStats