<?php

	ini_set( 'display_errors', 1 );
	ini_set( 'memory_limit', '500M' );
	ini_set( 'max_execution_time', '9000' );
	define( 'GEOSTATS_CACHE_TIME', 0 );
	class Geostats_API extends API {
		protected $db = null;
		var $default_settings = array( 'country_format' => '1', 'region_format' => '1' );

		/**
     * __construct
     * Sets up the database connection for easy use.
     *
     * @uses IEM::getDatabase()
     * @see db
     */
		function __construct() {
			$this->db = IEM::getDatabase();
		}

		function instant($statid = 0) {
			$data_openners = $this->get_openners( $statid, 3000 );
			$data_clickers = $this->get_clickers( $statid, 3000 );
			$this->template_system->assign( 'statid', $statid );
			$this->template_system->assign( 'openners_count', count( $data_openners ) );
			$this->template_system->assign( 'clickers_count', count( $data_clickers ) );
			$this->template_system->assign( 'openners', $data_openners );
			$this->template_system->assign( 'clickers', $data_clickers );
			$this->template_system->assign( 'openners_html', $this->format( $data_openners ) );
			$this->template_system->assign( 'clickers_html', $this->format( $data_clickers ) );
			$this->template_system->assign( 'geo_openners_chart', $this->format_chart( $data_openners ) );
			$this->template_system->assign( 'geo_clickers_chart', $this->format_chart( $data_clickers ) );

			if (!$statid) {
				return $this->template_system->ParseTemplate( 'home_instant', true );
			}

			return $this->template_system->ParseTemplate( 'instant', true );
		}

		function status_report() {
			$total = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens' ) );
			$total = $total['total'];
			$result = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens WHERE country_code!=\'-\'' ) );
			echo 'COUNTRY Openners: ' . $result['total'] . ( ( '/' ) . $total ) . PHP_EOL;
			$result = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens WHERE region!=\'-\'' ) );
			echo 'REGION Openners: ' . $result['total'] . ( ( '/' ) . $total ) . PHP_EOL;
			$result = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens WHERE city!=\'-\'' ) );
			echo 'CITY Openners: ' . $result['total'] . ( ( '/' ) . $total ) . PHP_EOL;
			$total = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks' ) );
			$total = $total['total'];
			$result = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks WHERE country_code!=\'-\'' ) );
			echo 'COUNTRY Clickers: ' . $result['total'] . ( ( '/' ) . $total ) . PHP_EOL;
			$result = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks WHERE region!=\'-\'' ) );
			echo 'REGION Clickers: ' . $result['total'] . ( ( '/' ) . $total ) . PHP_EOL;
			$result = $this->db->Fetch( $this->db->Query( 'SELECT COUNT(*) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks WHERE city!=\'-\'' ) );
			echo 'CITY Clickers: ' . $result['total'] . ( ( '/' ) . $total ) . PHP_EOL;
			echo PHP_EOL;
		}

		function update($force = false) {
			$path = dirname( realpath( __FILE__ ) );
			$this->status_report();
			echo 'Reseting Email Opens: ';
			$this->db->UpdateQuery( 'stats_emailopens', array( 'country_code' => '-', 'country_name' => '-', 'region' => '-', 'city' => '-' ), '`city`=\'-\'' );
			echo ($ru ? 'SUCCESS [' . $this->db->NumAffected() . ']' : 'FAILED [' . $this->db->GetErrorMsg() . ']') . PHP_EOL;
			echo 'Reseting Email Clicks: ';
			$ru = $this->db->UpdateQuery( 'stats_linkclicks', array( 'country_code' => '-', 'country_name' => '-', 'region' => '-', 'city' => '-' ), '`city`=\'-\'' );
			echo ($ru ? 'SUCCESS [' . $this->db->NumAffected() . ']' : 'FAILED [' . $this->db->GetErrorMsg() . ']') . PHP_EOL;
			echo 'Removing Geostats cache: ';
			$ru = $this->db->Query( 'TRUNCATE TABLE ' . SENDSTUDIO_TABLEPREFIX . 'geostats_cache' );
			echo ($ru ? 'SUCCESS [' . $this->db->NumAffected() . ']' : 'FAILED [' . $this->db->GetErrorMsg() . ']') . PHP_EOL;
			echo 'Removing Geostats Table: ';
			$ru = $this->db->Query( 'TRUNCATE TABLE ' . SENDSTUDIO_TABLEPREFIX . 'geostats' );
			echo ($ru ? 'SUCCESS [' . $this->db->NumAffected() . ']' : 'FAILED [' . $this->db->GetErrorMsg() . ']') . PHP_EOL;
			echo 'Removing INDEX: ';
			$ru = $this->db->Query( 'DROP INDEX `geo` ON ' . SENDSTUDIO_TABLEPREFIX . 'geostats' );
			echo ($ru ? 'SUCCESS [' . $this->db->NumAffected() . ']' : 'FAILED [' . $this->db->GetErrorMsg() . ']') . PHP_EOL;
			echo 'Importing IP DATABASE: OK' . PHP_EOL;
			$lquery = 'LOAD DATA LOCAL INFILE \'' . $path . ( '/ip.csv\' INTO TABLE `' . $this->db->TablePrefix . 'geostats` FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\r\n\'' );
			echo 'Executing ' . $lquery . ': ';
			$ru = $ru = $this->db->Query( $lquery );
			echo ($ru ? 'SUCCESS [' . $this->db->NumAffected() . ']' : 'FAILED [' . $this->db->GetErrorMsg() . ']') . PHP_EOL;
			echo PHP_EOL;
		}

		function country_filtered($country = '') {
			$country = str_replace( 'RUSSIAN FEDERATION', 'RUSSIA', $country );
			return $country;
		}

		function get_openners($statid = 0, $limit = 10, $global = false, $country = '') {
			$city = $this->db->Quote( $country );
			$city = ($country ? 'country_code=\'' . $city . '\' AND' : '');
			$gb = ($country ? 'city' : 'country_name');

			if (!$global) {
				if (!$statid) {
					$userid = &GetUser();

					$userid = $userid->userid;
					$city = $this->db->Quote( $country );
					$city = ($country ? 'a.country_code=\'' . $city . '\' AND' : '');
					$gb = ($country ? 'a.city' : 'a.country_name');
					$q = 'SELECT b.sentby, a.region, a.country_code, a.country_name, a.statid, a.city, COUNT(a.country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_newsletters b ON a.statid=b.statid WHERE ' . $city . ' b.sentby=' . $userid . ' AND a.country_name!=\'\' GROUP BY ' . $gb . ' ORDER BY total DESC LIMIT ' . $limit );
				} else {
					//$q = 'SELECT country_code, region, country_name, statid, city, COUNT(country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_emailopens WHERE ' . $city . ' statid=' . $statid . ' AND country_name!=\'-\' AND country_name!=\'\' GROUP BY country_name ORDER BY total DESC LIMIT ' . $limit );
					$q = 'SELECT country_code, region, country_name, statid, city, COUNT(country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_emailopens WHERE ' . $city . ' statid=' . $statid . ' AND country_name!=\'\' GROUP BY ' . $gb . ' ORDER BY total DESC LIMIT ' . $limit );
				}
			} else {
				$q = 'SELECT country_code, region, country_name, statid, city, COUNT(country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_emailopens ' . $city . ' GROUP BY ' . $gb . ' ORDER BY total DESC LIMIT ' . $limit );
			}

			$cache = $this->query_cache_load( $q );

			if ($cache) {
				return $cache;
			}

			$result = $this->db->Query( $q );
			$data = array();

			while ($row = $this->db->Fetch( $result )) {
				$row['country_name'] = ($row['country_name'] == '-' ? 'Unknown' : $row['country_name']);
				$row['city'] = ($row['city'] == '-' ? 'Unknown' : $row['city']);
				$row['region'] = ($row['region'] == '-' ? 'Unknown' : $row['region']);
				$a = explode( ',', $row['country_name'] );
				$row['country_name'] = addslashes( trim( $a[0] ) );
				$row['country_name_filtered'] = $this->country_filtered( $row['country_name'] );
				$data[] = $row;
			}

			$this->query_cache_save( $q, $data );
			return $data;
		}

		function export_openners($statid = 0, $country = '', $region = '', $city = '', $global = false) {
			$user = &GetUser();

			$userid = $user->userid;
			$country = ($country ? 'a.country_code=\'' . $this->db->Quote( $country ) . '\' ' : '');
			$region = ($region ? 'AND a.region=\'' . $this->db->Quote( $region ) . '\' ' : '');
			$city = ($city ? 'AND a.city=\'' . $this->db->Quote( $city ) . '\' ' : '');

			if (!$statid) {
				$user = (( $user->isAdmin() && $global == true ) ? '' : ' AND b.sentby=' . $userid);
				$q = 'SELECT c.emailaddress, b.sentby, a.region, a.country_code, a.country_name, a.statid, a.city, a.openip as ip 
       				FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens a 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . 'stats_newsletters b ON a.statid=b.statid 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( ( ( ( 'list_subscribers c ON a.subscriberid=c.subscriberid 
       		WHERE ' . $country . ' ' ) . $region . ' ' ) . $city . ' ' ) . $user );
			} else {
				$user = ($user->isAdmin() ? '' : ' AND b.sentby=' . $userid);
				$q = 'SELECT c.emailaddress, b.sentby, a.region, a.country_code, a.country_name, a.statid, a.city, a.openip as ip 
       				FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_emailopens a 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . 'stats_newsletters b ON a.statid=b.statid 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( ( ( ( 'list_subscribers c ON a.subscriberid=c.subscriberid 
       		WHERE ' . $country . ' ' ) . $region . ' ' ) . $city . ' ' ) . $user . ' AND b.statid=' . $statid . ' ' );
			}

			$result = $this->db->Query( $q );

			while ($row = $this->db->Fetch( $result )) {
				$row['country_name'] = str_replace( '\'', '', $row['country_name'] );
				$row['region'] = str_replace( '\'', '', $row['region'] );
				$row['city'] = str_replace( '\'', '', $row['city'] );
				echo ( ( '\'' ) . $row['emailaddress'] . '\',\'' . $row['ip'] . '\',\'' . $row['country_code'] . '\',\'' . $row['country_name'] . '\',\'' . $row['region'] . '\',\'' . $row['city'] . '\'' ) . PHP_EOL;
			}

		}

		function export_clickers($statid = 0, $country = '', $region = '', $city = '', $global = false) {
			$user = &GetUser();

			$userid = $user->userid;
			$country = ($country ? 'a.country_code=\'' . $this->db->Quote( $country ) . '\' ' : '');
			$region = ($region ? 'AND a.region=\'' . $this->db->Quote( $region ) . '\' ' : '');
			$city = ($city ? 'AND a.city=\'' . $this->db->Quote( $city ) . '\' ' : '');

			if (!$statid) {
				$user = (( $user->isAdmin() && $global == true ) ? '' : ' AND b.sentby=' . $userid);
				$q = 'SELECT c.emailaddress, b.sentby, a.region, a.country_code, a.country_name, a.statid, a.city, a.clickip as ip 
       				FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks a 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . 'stats_newsletters b ON a.statid=b.statid 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( ( ( ( 'list_subscribers c ON a.subscriberid=c.subscriberid 
       		WHERE ' . $country . ' ' ) . $region . ' ' ) . $city . ' ' ) . $user );
			} else {
				$user = ($user->isAdmin() ? '' : ' AND b.sentby=' . $userid);
				$q = 'SELECT c.emailaddress, b.sentby, a.region, a.country_code, a.country_name, a.statid, a.city, a.clickip as ip  
       				FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks a 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . 'stats_newsletters b ON a.statid=b.statid 
       				LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( ( ( ( 'list_subscribers c ON a.subscriberid=c.subscriberid 
       		WHERE ' . $country . ' ' ) . $region . ' ' ) . $city . ' ' ) . $user . ' AND b.statid=' . $statid . ' ' );
			}

			$result = $this->db->Query( $q );

			while ($row = $this->db->Fetch( $result )) {
				$row['country_name'] = str_replace( '\'', '', $row['country_name'] );
				$row['region'] = str_replace( '\'', '', $row['region'] );
				$row['city'] = str_replace( '\'', '', $row['city'] );
				echo ( ( '\'' ) . $row['emailaddress'] . '\',\'' . $row['ip'] . '\',\'' . $row['country_code'] . '\',\'' . $row['country_name'] . '\',\'' . $row['region'] . '\',\'' . $row['city'] . '\'' ) . PHP_EOL;
			}

		}

		function get_countries() {
			$q = 'SELECT country_code,country_name FROM ' . SENDSTUDIO_TABLEPREFIX . 'geostats WHERE country_code!=\'-\' GROUP BY country_code';
			$result = $this->db->Query( $q );
			$c = array();

			if ($row = $this->db->Fetch( $result )) {
				$c[] = $row;
			}

			return $c;
		}

		function get_clickers($statid = 0, $limit = 10, $global = false, $country = '') {
			$city = $this->db->Quote( $country );
			$city = ($country ? 'country_code=\'' . $city . '\' AND' : '');
			$gb = ($country ? 'city' : 'country_name');

			if (!$global) {
				if (!$statid) {
					$userid = &GetUser();

					$userid = $userid->userid;
					$city = $this->db->Quote( $country );
					$city = ($country ? 'a.country_code=\'' . $city . '\' AND' : '');
					$gb = ($country ? 'a.city' : 'a.country_name');
					$q = 'SELECT b.sentby, a.region, a.country_code, a.country_name, a.statid, a.city, COUNT(a.country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . 'stats_linkclicks a LEFT JOIN ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_newsletters b ON a.statid=b.statid WHERE ' . $city . ' b.sentby=' . $userid . ' AND a.country_name!=\'\' GROUP BY ' . $gb . ' ORDER BY total DESC LIMIT ' . $limit );
				} else {
					$q = 'SELECT country_code, region, country_name, statid, city, COUNT(country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_linkclicks WHERE ' . $city . ' statid=' . $statid . ' AND country_name!=\'-\' AND country_name!=\'\' GROUP BY country_name ORDER BY total DESC LIMIT ' . $limit );
					$q = 'SELECT country_code, region, country_name, statid, city, COUNT(country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_linkclicks WHERE ' . $city . ' statid=' . $statid . ' AND country_name!=\'\' GROUP BY ' . $gb . ' ORDER BY total DESC LIMIT ' . $limit );
				}
			} else {
				$q = 'SELECT country_code, region, country_name, statid, city, COUNT(country_name) as total FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_linkclicks ' . $city . ' GROUP BY ' . $gb . ' ORDER BY total DESC LIMIT ' . $limit );
			}

			$cache = $this->query_cache_load( $q );

			if ($cache) {
				return $cache;
			}

			$result = $this->db->Query( $q );
			$data = array();

			while ($row = $this->db->Fetch( $result )) {
				$row['country_name'] = ($row['country_name'] == '-' ? 'Unknown' : $row['country_name']);
				$row['city'] = ($row['city'] == '-' ? 'Unknown' : $row['city']);
				$row['region'] = ($row['region'] == '-' ? 'Unknown' : $row['region']);
				$a = explode( ',', $row['country_name'] );
				$row['country_name'] = addslashes( trim( $a[0] ) );
				$row['country_name_filtered'] = $this->country_filtered( $row['country_name'] );
				$data[] = $row;
			}

			$this->query_cache_save( $q, $data );
			return $data;
		}

		function query_cache_load($query = '') {
			$geostats_cache_time = GEOSTATS_CACHE_TIME;
			$q = 'CREATE TABLE IF NOT EXISTS ' . SENDSTUDIO_TABLEPREFIX . 'geostats_cache_stats (
  `uid` int(11) NOT NULL auto_increment,
  `user` int(11) NOT NULL,
  `data` longtext NOT NULL,
  `query` varchar(255) NOT NULL,
  `updated` varchar(255) NOT NULL,
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1';
			$mdquery = md5( trim( $query ) );
			$this->db->Query( 'SELECT * FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'geostats_cache_stats  WHERE query=\'' . $mdquery . '\'' ) );
			$result = $this->db->Query( $q );
			$row = $this->db->Fetch( $result );
			$now = time();

			if ($row) {
				$updated = (int)$row['updated'];

				if ($now - $updated < $geostats_cache_time) {
					$data = unserialize( base64_decode( $row['data'] ) );
					return $data;
				}
			} else {
				return false;
			}

		}

		function query_cache_save($query = '', $data = array()) {
			$updated = time();
			$query = md5( trim( $query ) );
			$data = base64_encode( serialize( $data ) );
			$this->db->Query( 'DELETE FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'geostats_cache_stats WHERE query=\'' . $query . '\'' ) );
			$q = 'INSERT INTO ' . SENDSTUDIO_TABLEPREFIX . ( 'geostats_cache_stats SET query=\'' . $query . '\',updated=\'' . $updated . '\',data=\'' . $data . '\'' );
			$this->db->Query( $q );
		}

		function format($data) {
			$d = array();
			foreach ($data as $k => $v) {

				if (isset( $v['total'] )) {
					$v['total'] = number_format( (int)$v['total'] );
				}


				if (isset( $v['country_name'] )) {
					$v['country_name'] = ucwords( strtolower( $v['country_name'] ) );
				}


				if (isset( $v['city'] )) {
					$v['city'] = ucwords( strtolower( $v['city'] ) );
				}


				if (isset( $v['region'] )) {
					$v['region'] = ucwords( strtolower( $v['region'] ) );
				}

				$d[] = $v;
			}

			return $d;
		}

		function format_chart($data) {
			$d = array();
			foreach ($data as $k => $v) {
				$v['total_f'] = number_format( (int)$v['total'] );
				$v['country_name'] = ucwords( strtolower( addslashes( $v['country_name'] ) ) );
				$d[] = ( '[\'' . $v['country_name'] . '[' ) . $v['total_f'] . ']\',' . $v['total'] . ']';

				if ($k == 9) {
					break;
				}
			}

			return implode( ',', $d );
		}

		function process_emailopens($limit = 100, $statid = 0) {
			echo 'Processing Email Opens: ' . PHP_EOL;
			$tt = $this->get_unprocessed_emailopens();
			$tp = 10;

			if ($statid) {
				$result = $this->db->Query( 'SELECT openid,openip FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_emailopens WHERE country_code=\'-\' AND statid=' . $statid . ' LIMIT ' . $limit ) );
			} else {
				$result = $this->db->Query( 'SELECT openid,openip FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_emailopens WHERE country_code=\'-\' LIMIT ' . $limit ) );
			}

			$total = $this->db->CountResult( $result );

			while ($row = $this->db->Fetch( $result )) {
				$geo_data = $this->get_data( $row['openip'], $tp, $total );

				if (!$geo_data) {
					continue;
				}

				$ru = $this->db->UpdateQuery( 'stats_emailopens', array( 'country_code' => $geo_data['CountryCode'], 'country_name' => $geo_data['CountryName'], 'region' => $geo_data['RegionName'], 'city' => $geo_data['City'] ), '`openid`=' . $row['openid'] );
				++$tp;
			}

			return array( $tp, $tt );
		}

		function process_emailclicks($limit = 100, $statid = 0) {
			time();
			echo 'Processing Email Clicks: ' . PHP_EOL;
			$tt = $this->get_unprocessed_emailclicks();

			if ($statid) {
				$result = $this->db->Query( 'SELECT clickid,clickip FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_linkclicks WHERE statid=' . $statid . ' AND country_code=\'-\' LIMIT ' . $limit ) );
			} else {
				$result = $this->db->Query( 'SELECT clickid,clickip FROM ' . SENDSTUDIO_TABLEPREFIX . ( 'stats_linkclicks WHERE country_code=\'-\' LIMIT ' . $limit ) );
			}

			$total = $this->db->CountResult( $result );
			$tt = 10;
			$tp = 10;

			while ($row = $start = $this->db->Fetch( $result )) {
				$geo_data = $this->get_data( $row['clickip'], $tp, $total );
				$ru = $this->db->UpdateQuery( 'stats_linkclicks', array( 'country_code' => $geo_data['CountryCode'], 'country_name' => $geo_data['CountryName'], 'region' => $geo_data['RegionName'], 'city' => $geo_data['City'] ), '`clickid`=' . $row['clickid'] );
				++$tp;
			}

			$end = time() - $start;
			echo ( 'Finished in: ' . $end . 's' ) . PHP_EOL;
			return array( $tp, $tt );
		}

		function get_unprocessed_emailopens() {
			return 0;
		}

		function get_unprocessed_emailclicks() {
			return 0;
		}

		function get_data($ip, $index = 0, $total = 0) {
                        $xml = @file_get_contents('http://freegeoip.net/xml/' . $ip);


                        if (get_magic_quotes_runtime()){
                                $xml = stripslashes($xml);
                        }

                        try{
                                $response = @new SimpleXMLElement($xml);

                                foreach($response as $field=>$value){
                                        $result[(string)$field] = (string)$value;
                                }

                                echo ( ( '[' ) . $index . '/' ) . $total . '] IP: ' . $ip . ' - ' . implode( ' - ', $result ) . PHP_EOL;

                                return $result;
                        }
                        catch(Exception $e){
                                echo 'Erro: ' . $e->getMessage();
                                $this->errors[] = $e->getMessage();
                                return;
                        }
		
		}
	}

?>