<?php

// security check
if( !defined( 'WP_PLUGIN_DIR' ) || ! current_user_can( 'manage_options' ) )
{
	die( 'There is nothing to see here...' );
}

$counterize_plugin_dir = WP_PLUGIN_DIR . '/' . plugin_basename( dirname( __FILE__ ) );
if( counterize_is_ssl() )
{
	$counterize_plugin_url = str_replace( 'http://', 'https://', WP_PLUGIN_URL ) . '/' . plugin_basename( dirname( __FILE__ ) );
}
else
{
	$counterize_plugin_url = WP_PLUGIN_URL . '/' . plugin_basename( dirname( __FILE__ ) );
}

//Content of the Counterize options page
function counterize_options_page_callback()
{
	global $wpdb;

	//Not admin? Go away.
	if( ! current_user_can( 'manage_options' ) )
	{
		die ( __( "You don't have sufficient privileges to display this page", COUNTERIZE_TD ) );
	}

	$MajorVersion	= counterize_get_version( 'major' );
	$MinorVersion	= counterize_get_version( 'minor' );
	$Revision		= counterize_get_version( 'revision' );

	?>

	<div class="wrap">
		<div class="icon32" id="icon-options-general"></div>

		<h2><?php echo __( 'Counterize configuration - Version ', COUNTERIZE_TD ) . $MajorVersion . '.' . $MinorVersion . '.' . $Revision; ?></h2>

		<form method="post" action="options.php">

			<?php
			settings_fields( 'counterize_options_group' );
			do_settings_sections( 'counterize_options_page_id' );
			?>

			<p class="submit">
				<input type="submit" class="button-primary" value="<?php _e( 'Save Changes', COUNTERIZE_TD ) ?>" />
			</p>
		</form>

	</div>

	<?php

	counterize_pagefooter();
}

//Executed when WordPress initialize the administration section.
//Inside we register our javascript ans stylesheet files.
function counterize_admin_init_callback()
{
	global $counterize_plugin_url;
	wp_register_script( 'counterize_javascript', $counterize_plugin_url . '/counterize.js.php', __FILE__ );
	wp_register_style( 'counterize_stylesheet', $counterize_plugin_url . '/counterize.css.php', __FILE__ );


	//register option group
	register_setting( 'counterize_options_group', 'counterize_options', 'counterize_options_validate_callback' );

	//add sections
	add_settings_section( 'counterize_options_section_appearance', __( 'Appearance'         , COUNTERIZE_TD ), 'counterize_options_display_section_appearance_callback', 'counterize_options_page_id' );
	add_settings_section( 'counterize_options_section_exclusions', __( 'Exclusions'         , COUNTERIZE_TD ), 'counterize_options_display_section_exclusions_callback', 'counterize_options_page_id' );
	add_settings_section( 'counterize_options_section_ip'        , __( 'IP-related'         , COUNTERIZE_TD ), 'counterize_options_display_section_ip_callback'        , 'counterize_options_page_id' );
	add_settings_section( 'counterize_options_section_db'        , __( 'Database operations', COUNTERIZE_TD ), 'counterize_options_display_section_db_callback'        , 'counterize_options_page_id' );


	//section Appearance
	add_settings_field( 'counterize_setting_amount'  , __( 'Rows to show in latest entries', COUNTERIZE_TD ) , 'counterize_options_display_amount_callback'  , 'counterize_options_page_id', 'counterize_options_section_appearance' );
	add_settings_field( 'counterize_setting_amount2' , __( 'Rows to show in vertical charts', COUNTERIZE_TD ), 'counterize_options_display_amount2_callback' , 'counterize_options_page_id', 'counterize_options_section_appearance' );
	add_settings_field( 'counterize_setting_maxwidth', __( 'Maximum width for labels', COUNTERIZE_TD )       , 'counterize_options_display_maxwidth_callback', 'counterize_options_page_id', 'counterize_options_section_appearance' );
	add_settings_field( 'counterize_setting_display_dashboard_capability', __( 'Capability needed to display the Counterize dashboard', COUNTERIZE_TD )      , 'counterize_options_display_display_dashboard_capability_callback', 'counterize_options_page_id', 'counterize_options_section_appearance' );

	//section Exclusions
	add_settings_field( 'counterize_setting_excludedusers', __( 'Select users that may not be counted &#x202A;(press Ctrl for multi-selection)&#x202C;', COUNTERIZE_TD ), 'counterize_options_display_excludedusers_callback', 'counterize_options_page_id', 'counterize_options_section_exclusions' );
	add_settings_field( 'counterize_setting_excludedip'   , __( 'Comma-separated list of IP to exclude from the statistics', COUNTERIZE_TD )                            , 'counterize_options_display_excludedip_callback'   , 'counterize_options_page_id', 'counterize_options_section_exclusions' );
	add_settings_field( 'counterize_setting_logbots'      , __( 'Enable <a href="http://en.wikipedia.org/wiki/Internet_bot">bots</a> logging', COUNTERIZE_TD )          , 'counterize_options_display_logbots_callback'      , 'counterize_options_page_id', 'counterize_options_section_exclusions' );


	//section IP
	add_settings_field( 'counterize_setting_enableip', __( 'My country allows to collect IPs', COUNTERIZE_TD ), 'counterize_options_display_enableip_callback', 'counterize_options_page_id', 'counterize_options_section_ip' );
	add_settings_field( 'counterize_setting_whois'   , __( 'Whois URL string'                , COUNTERIZE_TD ), 'counterize_options_display_whois_callback'   , 'counterize_options_page_id', 'counterize_options_section_ip' );
	add_settings_field( 'counterize_setting_geoip'   , __( 'Geo IP tool URL string'          , COUNTERIZE_TD ), 'counterize_options_display_geoip_callback'   , 'counterize_options_page_id', 'counterize_options_section_ip' );

	//section DB
	add_settings_field( 'counterize_setting_flushdb'   , __( 'Flush the Counterize Database', COUNTERIZE_TD ), 'counterize_options_display_flushdb_callback'   , 'counterize_options_page_id', 'counterize_options_section_db' );
	add_settings_field( 'counterize_setting_refreshua' , __( 'Refresh the user-agent table' , COUNTERIZE_TD ), 'counterize_options_display_refreshua_callback' , 'counterize_options_page_id', 'counterize_options_section_db' );
	add_settings_field( 'counterize_setting_deletebots', __( 'Manually delete the bots'     , COUNTERIZE_TD ), 'counterize_options_display_deletebots_callback', 'counterize_options_page_id', 'counterize_options_section_db' );
}

//display the Appearance section
function counterize_options_display_section_appearance_callback()
{
	echo '<p>' . __( 'This section concerns the look and feel of Counterize.', COUNTERIZE_TD ) . '</p>';
}

//display the Exclusions section
function counterize_options_display_section_exclusions_callback()
{
	echo '<p>' . __( 'This section allows you to set some exclusions.', COUNTERIZE_TD ) . '</p>';
}

//display the IP-related section
function counterize_options_display_section_ip_callback()
{
	echo '<p>' . __( 'This section is about IP-related options.', COUNTERIZE_TD ) . '</p>';
}

//display the Database operations section
function counterize_options_display_section_db_callback()
{
	echo '<p>' . __( 'Database maintenance operations are in this section.', COUNTERIZE_TD ) . '</p>';
}

function counterize_options_display_amount_callback()
{
	$options = get_option( 'counterize_options' );

	//set a default value if no value is set
	if( ! isset( $options['amount'] ) || empty( $options['amount'] ) )
	{
		$options['amount'] = 50;
	}
	echo "<input id='counterize_setting_amount' name='counterize_options[amount]' size='10' type='text' value='{$options['amount']}' />";
	echo '<br /><small>&#x202A;<strong>0</strong>' . __( ' to view all (not recommended because this exposes you to get an "out of memory" error)', COUNTERIZE_TD ) . '&#x202C;</small>';
}
function counterize_options_display_amount2_callback()
{
	$options = get_option( 'counterize_options' );
	//set a default value if no value is set
	if(! isset( $options['amount2'] ) || empty( $options['amount2'] ) )
	{
		$options['amount2'] = 50;
	}
	echo "<input id='counterize_setting_amount2' name='counterize_options[amount2]' size='10' type='text' value='{$options['amount2']}' />";
}
function counterize_options_display_maxwidth_callback()
{
	$options = get_option( 'counterize_options' );
	//set a default value if no value is set
	if( ! isset( $options['maxwidth'] ) || empty( $options['maxwidth'] ) )
	{
		$options['maxwidth'] = 50;
	}
	echo "<input id='counterize_setting_maxwidth' name='counterize_options[maxwidth]' size='10' type='text' value='{$options['maxwidth']}' />";
}

function counterize_options_display_display_dashboard_capability_callback()
{
	global $wp_roles;
	$options = get_option( 'counterize_options' );
	//set a default value if no value is set
	if( ! isset( $options['display_dashboard_capability'] ) || empty( $options['display_dashboard_capability'] ) )
	{
		$options['display_dashboard_capability'] = 'manage_options';
	}
	echo "
	<select id='counterize_setting_display_dashboard_capability' name='counterize_options[display_dashboard_capability]'>
	";

	//retrieve all the capabilities of the current user (usually an administrator)
	$current_user = wp_get_current_user();
	$current_user->get_role_caps();
	$capabilities = $current_user->allcaps;
	ksort( $capabilities );
	foreach( $capabilities as $cap=>$whatever )
	{
		echo "
		<option value='{$cap}'" . ( $options['display_dashboard_capability'] == $cap ? ' selected="selected"' : '' ) . ">{$cap}</option>
		";
	}

	echo "
	</select>
	";
}

function counterize_options_display_excludedusers_callback()
{
	global $wpdb;
	$options = get_option( 'counterize_options' );
	$excluded_users	= explode( ',', counterize_get_option( 'excludedusers' ) );

	$sql = "SELECT * FROM {$wpdb->users}";
	$users = $wpdb->get_results( $sql );

	?>

	<select id='counterize_setting_excludedusers' name='counterize_options[excludedusers][]' multiple='multiple' size='10' style='height: auto;'>

	<?php
	foreach( $users as $user )
	{
		?>

		<option value="<?php echo $user->ID; ?>" <?php if( in_array( $user->ID, $excluded_users ) ) { echo 'selected="selected"'; } ?>><?php echo $user->display_name . '&nbsp;&nbsp;&nbsp;[' . $user->user_login . ']'; ?></option>

		<?php
	}
	unset( $users );
	?>

	</select>

	<?php
}

function counterize_options_display_excludedip_callback()
{
	$options = get_option( 'counterize_options' );
	echo "<textarea id='counterize_setting_excludedip' name='counterize_options[excludedip]' cols='50' rows='3'>{$options['excludedip']}</textarea>";
}

function counterize_options_display_logbots_callback()
{
	$options = get_option( 'counterize_options' );
	echo "<input id='counterize_setting_logbots' name='counterize_options[logbots]' type='checkbox' value='1'" . ( $options['logbots'] > 0 ? ' checked="checked"' : '' ) . ' />';
}

function counterize_options_display_enableip_callback()
{
	$options = get_option( 'counterize_options' );
	echo "<input id='counterize_setting_enableip' name='counterize_options[enableip]' type='checkbox' value='1'" . ( $options['enableip'] > 0 ? ' checked="checked"' : '' ) . ' />';
}

function counterize_options_display_whois_callback()
{
	$options = get_option( 'counterize_options' );
	echo "<input id='counterize_setting_whois' name='counterize_options[whois]' size='60' type='text' value='{$options['whois']}' />";
	echo '<br /><small>' . __( 'Example:', COUNTERIZE_TD ) . ' &#x202A;http://www.ripe.net/whois?form_type=simple&amp;searchtext=&#x202C;</small>';
}

function counterize_options_display_geoip_callback()
{
	$options = get_option( 'counterize_options' );
	echo "<input id='counterize_setting_geoip' name='counterize_options[geoip]' size='60' type='text' value='{$options['geoip']}' />";
	echo '<br /><small>' . __( 'Example:', COUNTERIZE_TD ) . ' &#x202A;http://whatismyipaddress.com/ip/&#x202C;</small>';
}

/* these options don't have any value stored in the database */
function counterize_options_display_flushdb_callback()
{
	$options = get_option( 'counterize_options' );
	?>
	<input id='counterize_setting_flushdb' name='counterize_options[flushdb]' type='checkbox' value='wantflushdb' /><br />
	<small>
		<strong>
			<?php _e( "&#x202A;(This means deleting all records and all stats! Don't forget to backup your database before if your data is important)&#x202C;", COUNTERIZE_TD ); ?>
		</strong>
	</small>
	<?php
	if( isset( $_SESSION['counterize_flusheddb'] ) )
	{
		if( $_SESSION['counterize_flusheddb']== 'flushed' )
		{
			counterize_updateText( __( 'Database flushed!', COUNTERIZE_TD ) );
			unset( $_SESSION['counterize_flusheddb'] );
		}
	}
}

function counterize_options_display_refreshua_callback()
{
	$options = get_option( 'counterize_options' );
	?>
	<input id='counterize_setting_refreshua' name='counterize_options[refreshua]' type='checkbox' value='wantrefreshua' /><br />
	<small>
		<?php _e( '&#x202A;(You may do this if you just upgraded from a previous version)&#x202C;', COUNTERIZE_TD ); ?>
	</small>
	<?php
	if( isset( $_SESSION['counterize_refreshedua'] ) )
	{
		if( $_SESSION['counterize_refreshedua'] == 'refreshed' )
		{
			counterize_updateText( __( 'User-agent table refreshed!', COUNTERIZE_TD ) );
			unset( $_SESSION['counterize_refreshedua'] );
		}
	}
}

function counterize_options_display_deletebots_callback()
{
	$options = get_option( 'counterize_options' );
	?>
	<input id='counterize_setting_deletebots' name='counterize_options[deletebots]' type='checkbox' value='wantdeletebots' /><br />
	<small>
		<?php _e( '&#x202A;(You should do this if you modified <strong>botlist.txt</strong>)&#x202C;', COUNTERIZE_TD ); ?>
	</small>
	<?php
	if( isset( $_SESSION['counterize_deletedbots'] ) )
	{
		if( $_SESSION['counterize_deletedbots'] == 'deleted' )
		{
			counterize_updateText( __( 'Bots have been deleted!', COUNTERIZE_TD ) );
			unset( $_SESSION['counterize_deletedbots'] );
		}
	}
}

//validate the input data
function counterize_options_validate_callback( $input )
{
	//load the current options
	$newinput = get_option( 'counterize_options' );

	if( isset( $input['amount'] ) )
	{
		$newinput['amount'] = trim( $input['amount'] );
		if( ! preg_match( '/^[0-9]+$/i', $newinput['amount'] ) )
		{
			$newinput['amount'] = '50';
		}
	}

	if( isset( $input['amount2'] ) )
	{
		$newinput['amount2'] = trim( $input['amount2'] );
		if( ! preg_match( '/^[0-9]+$/i', $newinput['amount2'] ) )
		{
			$newinput['amount2'] = '50';
		}
	}

	if( isset( $input['maxwidth'] ) )
	{
		$newinput['maxwidth'] = trim( $input['maxwidth'] );
		if( ! preg_match( '/^[0-9]+$/i', $newinput['maxwidth'] ) )
		{
			$newinput['maxwidth'] = '50';
		}
	}

	if( isset( $input['display_dashboard_capability'] ) )
	{
		global $wp_roles;
		$current_user = wp_get_current_user();
		$current_user->get_role_caps();
		$capabilities = $current_user->allcaps;
		foreach( $capabilities as $cap=>$whatever )
		{
			if( $input['display_dashboard_capability'] == $cap )
			{
				$newinput['display_dashboard_capability'] = $input['display_dashboard_capability'];
			}
		}
	}


	if( isset( $input['excludedusers'] ) )
	{
		$newinput['excludedusers'] = implode( ',', $input['excludedusers'] );
	}
	else
	{
		$newinput['excludedusers'] = '';
	}

	//ip exclusion list
	$target_ip_array = Array();
	if( isset( $input['excludedip'] ) )
	{
		$input_ip_array = explode( ',', str_replace( ' ', '', $input['excludedip'] ) );
		foreach( $input_ip_array as $ip )
		{
			if( preg_match( '/^([1-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])(\.([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])){3}$/', $ip ) )
			{
				$target_ip_array[] = $ip;
			}
		}
		unset( $input_ip_array );
	}
	$newinput['excludedip'] = implode( ',', $target_ip_array );

	if( isset( $input['logbots'] ) )
	{
		$newinput['logbots'] = trim( $input['logbots'] );
		if( ! preg_match( '/^(0|1)+$/i', $newinput['logbots'] ) )
		{
			$newinput['logbots'] = 0;
		}
	}
	else
	{
		$newinput['logbots'] = 0;
	}

	if( isset( $input['whois'] ) )
	{
		$newinput['whois'] = trim( $input['whois'] );
		if( ! preg_match( '#^http[a-zA-Z0-9-\?\.=_:;/]+$#i', $newinput['whois'] ) )
		{
			$newinput['whois'] = 'http://www.ripe.net/whois?form_type=simple&amp;searchtext=';
		}
	}

	if( isset( $input['geoip'] ) )
	{
		$newinput['geoip'] = trim( $input['geoip'] );
		if( ! preg_match( '#^http[a-zA-Z0-9-\?\.=_:;/]+$#i', $newinput['geoip'] ) )
		{
			$newinput['geoip'] = 'http://whatismyipaddress.com/ip/';
		}
	}

	if( isset( $input['enableip'] ) )
	{
		$newinput['enableip'] = trim( $input['enableip'] );
		if( ! preg_match( '/^(0|1)+$/i', $newinput['enableip'] ) )
		{
			$newinput['enableip'] = 0;
		}
	}
	else
	{
		$newinput['enableip'] = 0;
	}

	if( isset( $input['flushdb'] ) )
	{
		if( $input['flushdb'] == 'wantflushdb' )
		{
			counterize_flush();
			$_SESSION['counterize_flusheddb'] = 'flushed';
		}
	}
	$newinput['flushdb'] = '';

	if( isset( $input['refreshua'] ) )
	{
		if( $input['refreshua'] == 'wantrefreshua' )
		{
			counterize_update_all_userAgents();
			$_SESSION['counterize_refreshedua'] = 'refreshed';
		}
	}
	$newinput['refreshua'] = '';

	if( isset( $input['deletebots'] ) )
	{
		if( $input['deletebots'] == 'wantdeletebots' )
		{
			counterize_delete_bots();
			$_SESSION['counterize_deletedbots'] = 'deleted';
		}
	}
	$newinput['deletebots'] = '';


	return $newinput;
}








// flushes the db and reset the auto_increment to 1 - be careful
function counterize_flush()
{
	global $wpdb;

	$sql = 'DELETE FROM `' . counterize_logTable() . '`';
	$num = $wpdb->query( $sql );
	$sql = 'ALTER IGNORE TABLE `' . counterize_logTable() . '` AUTO_INCREMENT=1';
	$num = $wpdb->query( $sql );

	$sql = 'DELETE FROM `' . counterize_pageTable() . '`';
	$num = $wpdb->query( $sql );
	$sql = 'ALTER IGNORE TABLE `' . counterize_pageTable() . '` AUTO_INCREMENT=1';
	$num = $wpdb->query( $sql );

	$sql = 'DELETE FROM `' . counterize_agentsTable() . '`';
	$num = $wpdb->query( $sql );
	$sql = 'ALTER IGNORE TABLE `' . counterize_agentsTable() . '` AUTO_INCREMENT=1';
	$num = $wpdb->query( $sql );

	$sql = 'DELETE FROM `' . counterize_refererTable() . '`';
	$num = $wpdb->query( $sql );
	$sql = 'ALTER IGNORE TABLE `' . counterize_refererTable() . '` AUTO_INCREMENT=1';
	$num = $wpdb->query( $sql );

	$sql = 'DELETE FROM `' . counterize_keywordTable() . '`';
	$num = $wpdb->query( $sql );
	$sql = 'ALTER IGNORE TABLE `' . counterize_keywordTable() . '` AUTO_INCREMENT=1';
	$num = $wpdb->query( $sql );

	$sql = 'UPDATE IGNORE `' . counterize_countryTable() . '` SET `count`=0';
	$num = $wpdb->query( $sql );
}


function counterize_update_all_userAgents()
{
	global $wpdb;
	$agents = $wpdb->get_results( 'SELECT agentID, name FROM `' . counterize_agentsTable() . '`' );
	foreach( $agents as $agent )
	{
		list(
			$browser_name, $browser_code, $browser_ver, $browser_url,
			$os_name, $os_code, $os_ver, $os_url,
			$pda_name, $pda_code, $pda_ver, $pda_url
		) = counterize_detect_browser( $agent->name );

		$query = $wpdb->prepare( 'UPDATE IGNORE `' . counterize_agentsTable() . '` SET '
			. ' `browserName`    = %s, '
			. ' `browserCode`    = %s, '
			. ' `browserVersion` = %s, '
			. ' `browserURL`     = %s, '
			. ' `osName`         = %s, '
			. ' `osCode`         = %s, '
			. ' `osVersion`      = %s, '
			. ' `osURL`          = %s '
			. ' WHERE `agentID` = %d ',
			$browser_name,
			$browser_code,
			$browser_ver,
			$browser_url,
			$os_name,
			$os_code,
			$os_ver,
			$os_url,
			$agent->agentID );

		$wpdb->query( $query );
	}
	unset( $agents );
}

//Delete the bots from the database. There is probably a more efficient way to do this (especially, I don't like those two imbricated foreach).
function counterize_delete_bots()
{
	global $wpdb;
	global $counterize_plugin_dir;

	//load the bot array
	$botarray = Array();
	if( file_exists( $counterize_plugin_dir . '/botlist.txt' ) )
	{
		$botarray = file( $counterize_plugin_dir . '/botlist.txt', FILE_SKIP_EMPTY_LINES | FILE_IGNORE_NEW_LINES );
	}

	//load the user-agents
	$agents = $wpdb->get_results( 'SELECT `agentID`, `name` FROM `' . counterize_agentsTable() . '` ORDER BY `agentID` DESC' );


	//for each agent, search if it contains a bot string
	foreach( $agents as $agent )
	{

		// Run through bot-array and see if there's anything we don't like...
		foreach( $botarray as $entry )
		{
			//Check for special parameters to allow complex bot search without
			// blocking the user agent completely.
			//Formats are :
			// "complexfilter:Suspicious user-agent string###/page_url_without_domain_name###Complete_referer_string" without quotes
			// "regexp:regular_expression" without quotes
			//Desired effect of the first format is: remove bot entries without deleting legitimate
			// entries, in case the bot is faking a real browser user agent but
			// always use the same referer and page, which real users do not.
			$is_regexp  = ( strpos( $entry, 'regexp:' ) !== FALSE );
			$is_complex = ( strpos( $entry, 'complexfilter:' ) !== FALSE );

			if( $is_complex )
			{
				$entry = str_replace( 'complexfilter:', '', $entry );
				$temp = explode( '###', $entry );
				$bot = $temp[0];
				if( isset( $temp[1] ) && isset( $temp[2] ) )
				{
					$page = $temp[1];
					$referer = $temp[2];
					if( $referer == "%HTTP_HOST%" )
					{
						$referer = 'http://' . $_SERVER['HTTP_HOST'] . '/';
					}
				}
				else
				{
					//by security
					$is_complex = false;
				}
				unset( $temp );
				$condition = stristr( $agent->name, $bot ) !== FALSE;
			}
			elseif( $is_regexp )
			{
				$bot = str_replace( 'regexp:', '', $entry );
				$referer = '';
				$page = '';
				$condition = preg_match( $bot, $agent->name );
			}
			else
			{
				$bot = $entry;
				$referer = '';
				$page = '';
				$condition = stristr( $agent->name, $bot ) !== FALSE;
			}

			//if we found a bot then
			if( $condition )
			{
				//if we have a complex filter
				if( $is_complex && !empty( $referer ) && !empty( $page ) )
				{
					if( $page != '*' )
					{
						//retrieve the pageID to decount
						$pageid = $wpdb->get_var( 'SELECT `pageID` FROM `' . counterize_pageTable() . "` WHERE `url`='" . $wpdb->prepare( $page ) . "' LIMIT 1" );
					}

					//retrieve the refererID to decount
					$refererid = $wpdb->get_var( 'SELECT `refererID` FROM `' . counterize_refererTable() . "` WHERE `name`='" . $wpdb->prepare( $referer ) . "' LIMIT 1" );

					//get the number of records to decount from page and referer tables
					if( $page != '*' )
					{
						$sql = "SELECT COUNT(`id`) "
							. " FROM `" . counterize_logTable() . "`"
							. " WHERE `agentID`=%d "
							. " AND `refererID`=%d "
							. " AND `pageID`=%d";
						$query = $wpdb->prepare( $sql, $agent->agentID, $pageid, $refererid );
					}
					else
					{
						$sql = "SELECT COUNT(`id`) "
							. " FROM `" . counterize_logTable() . "`"
							. " WHERE `agentID`=%d "
							. " AND `refererID`=%d ";
						$query = $wpdb->prepare( $sql, $agent->agentID, $refererid );
					}
					$cnt = intval( $wpdb->get_var( $query ) );
					unset( $sql, $query );

					//if the number is > 0 we have to decount this number from some tables
					if( $cnt > 0 )
					{
						//substract the visit count of the bot from the agent table
						$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_agentsTable() . "` SET `count`=`count` - %d WHERE `agentID`=%d", $cnt, $agent->agentID );
						$wpdb->query( $query );
						unset( $query );

						if( $page != '*' )
						{
							//substract the visit count of the bot from the page table
							$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_pageTable() . "` SET `count`=`count` - %d WHERE `pageID`=%d", $cnt, $pageid );
							$wpdb->query( $query );
						}
						else
						{
							//we have to search in all the records the one who match the agentID and refererID, and decount them
							$query = $wpdb->prepare( "SELECT COUNT(`id`) AS NB, `pageID` FROM `" . counterize_logTable() . "` WHERE `agentID`=%d AND `refererID`=%d GROUP BY `pageID`", $agent->agentID, $refererid );
							$page_entries = $wpdb->get_results( $query );
							unset( $query );
							if( count( $page_entries ) > 0 )
							{
								foreach( $page_entries as $page_entry )
								{
									//substract the visit count of the bot from the page table
									$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_pageTable() . "` SET `count`=`count` - %d WHERE `pageID`=%d", $page_entry->NB, $page_entry->pageID );
									$wpdb->query( $query );
									unset( $query );
								}
								unset( $page_entries );
							}
						}

						//substract the visit count of the bot from the referer table
						$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_refererTable() . "` SET `count`=`count` - %d WHERE `refererID`=%d", $cnt, $refererid );
						$wpdb->query( $query );
						unset( $query );

						//substract it from the countries table
						if( $page != '*' )
						{
							$query = $wpdb->prepare( "SELECT COUNT(`id`) AS NB, `countryID` FROM `" . counterize_logTable() . "` WHERE `agentID`=%d AND `refererID`=%d AND `pageID`=%d GROUP BY `countryID`", $agent->agentID, $refererid, $pageid );
						}
						else
						{
							$query = $wpdb->prepare( "SELECT COUNT(`id`) AS NB, `countryID` FROM `" . counterize_logTable() . "` WHERE `agentID`=%d AND `refererID`=%d GROUP BY `countryID`", $agent->agentID, $refererid );
						}
						$country_entries = $wpdb->get_results( $query );
						unset( $query );
						if( count( $country_entries ) > 0 )
						{
							foreach( $country_entries as $country_entry )
							{
								$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_countryTable() . "` SET `count`=`count` - %d WHERE `countryID`=%d", $country_entry->NB, $country_entry->countryID );
								$wpdb->query( $query );
								unset( $query );
							}
							unset( $country_entries );
						}

						//delete the bot entries in the logtable
						if( $page != '*' )
						{
							$query = $wpdb->prepare( "DELETE FROM `" . counterize_logTable() . "` WHERE `agentID`=%d AND `pageID`=%d AND `refererID`=%d", $agent->agentID, $pageid, $refererid );
						}
						else
						{
							$query = $wpdb->prepare( "DELETE FROM `" . counterize_logTable() . "` WHERE `agentID`=%d AND `refererID`=%d", $agent->agentID, $refererid );
						}
						$wpdb->query( $query );
						unset( $query );

						//delete invalid entries from the log table
						$query = "DELETE FROM `" . counterize_logTable() . "` WHERE `agentID`=0";
						$wpdb->query( $query );
						unset( $query );
					}
				}
				else //we just have to delete this bot and substract its hit count
				{
					//substract it from the pages table
					$query = $wpdb->prepare( "SELECT COUNT(`id`) AS NB, `pageID` FROM `" . counterize_logTable() . "` WHERE `agentID`= %d GROUP BY `pageID`", $agent->agentID );
					$page_entries = $wpdb->get_results( $query );
					unset( $query );
					if( count( $page_entries ) > 0 )
					{
						foreach( $page_entries as $page_entry )
						{
							$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_pageTable() . "` SET `count`=`count` - %d WHERE `pageID`=%d", $page_entry->NB, $page_entry->pageID );
							$wpdb->query( $query );
							unset( $query );
						}
						unset( $page_entries );
					}

					//substract it from the referers table
					$query = $wpdb->prepare( "SELECT COUNT(`id`) AS NB, `refererID` FROM `" . counterize_logTable() . "` WHERE `agentID`= %d GROUP BY `refererID`", $agent->agentID );
					$referer_entries = $wpdb->get_results( $query );
					unset( $query );
					if( count( $referer_entries ) > 0 )
					{
						foreach( $referer_entries as $referer_entry )
						{
							$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_refererTable() . "` SET `count`=`count` - %d WHERE `refererID`=%d", $referer_entry->NB, $referer_entry->refererID );
							$wpdb->query( $query );
						}
						unset( $referer_entries );
					}

					//substract it from the countries table
					$query = $wpdb->prepare( "SELECT COUNT(`id`) AS NB, `countryID` FROM `" . counterize_logTable() . "` WHERE `agentID`=%d GROUP BY `countryID`", $agent->agentID );
					$country_entries = $wpdb->get_results( $query );
					unset( $query );
					if( count( $country_entries ) > 0 )
					{
						foreach( $country_entries as $country_entry )
						{
							$query = $wpdb->prepare( "UPDATE IGNORE `" . counterize_countryTable() . "` SET `count`=`count` - %d WHERE `countryID`=%d", $country_entry->NB, $country_entry->countryID );
							$wpdb->query( $query );
							unset( $query );
						}
						unset( $country_entries );
					}

					//delete it from the log table
					$query = $wpdb->prepare( "DELETE FROM `" . counterize_logTable() . "` WHERE `agentID`=%d OR `agentID`=0", $agent->agentID );
					$num = $wpdb->query( $query );
					unset( $query );

					//delete it from the user-agent table
					$query = $wpdb->prepare( "DELETE FROM `" . counterize_agentsTable() . "` WHERE `agentID`=%d", $agent->agentID );
					$num = $wpdb->query( $query );
					unset( $query );
				}
				break;
			}
		}
		unset( $botarray );
	}
	unset( $agents );

	//we need to do some cleaning...
	counterize_delete_broken_entries();
}

// deletes a entry from the database
function counterize_killEntry( $entryID )
{
	global $wpdb;
	$entries_tmp = counterize_getentries_sql( 1, $entryID );
	$entries = $wpdb->get_results( $entries_tmp[0] );

	foreach( $entries as $entry )
	{
		$sql = "DELETE FROM `" . counterize_logTable() . "` WHERE `id`={$entry->id}";
		$num = $wpdb->query( $sql );
		unset( $sql );

		$sql = "UPDATE IGNORE `" . counterize_pageTable() . "` SET `count` = `count` - 1 WHERE `pageID`={$entry->pageID}";
		$num = $wpdb->query( $sql );
		unset( $sql );

		$sql = "UPDATE IGNORE `" . counterize_refererTable() . "` SET `count` = `count` - 1 WHERE `refererID`={$entry->refererID}";
		$num = $wpdb->query( $sql );
		unset( $sql );

		$sql = "UPDATE IGNORE `" . counterize_agentsTable() . "` SET `count` = `count` - 1 WHERE `agentID`={$entry->agentID}";
		$num = $wpdb->query( $sql );
		unset( $sql );

		$sql = "UPDATE IGNORE `" . counterize_keywordTable() . "` SET `count` = `count` - 1 WHERE `keywordID`={$entry->keywordID}";
		$num = $wpdb->query( $sql );
		unset( $sql );

		$sql = "UPDATE IGNORE `" . counterize_countryTable() . "` SET `count` = `count` - 1 WHERE `countryID`={$entry->countryID}";
		$num = $wpdb->query( $sql );
		unset( $sql );
	}
	unset( $entries_tmp, $entries );
}

/*
 * delete broken entries from the log table and decount them from the other tables
 */
function counterize_delete_broken_entries()
{
	global $wpdb;

	//delete entries related to inexistant agentID
	$sql = "SELECT `agentID`, `pageID`, `refererID`, `countryID` "
		. " FROM `" . counterize_logTable() . "` "
		. " WHERE `agentID` NOT IN ( SELECT `agentID` FROM `" . counterize_agentsTable() . "` )";
	$entries = $wpdb->get_results( $sql );
	unset( $sql );
	if( count( $entries ) > 0 )
	{
		foreach( $entries as $entry )
		{
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_pageTable()    . "` SET `count`=`count` - 1 WHERE `pageID`=%d"   , $entry->pageID    ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_refererTable() . "` SET `count`=`count` - 1 WHERE `refererID`=%d", $entry->refererID ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_countryTable() . "` SET `count`=`count` - 1 WHERE `countryID`=%d", $entry->countryID ) );
		}
		unset( $entries );
		$sql = "DELETE FROM `" . counterize_logTable() . "` "
			. " WHERE `agentID` NOT IN ( SELECT `agentID` FROM `" . counterize_agentsTable() . "` )";
		$wpdb->query( $sql );
		unset( $sql );
	}

	//delete entries related to inexistant refererID
	$sql = "SELECT `agentID`, `pageID`, `refererID`, `countryID` "
		. " FROM `" . counterize_logTable() . "` "
		. " WHERE `refererID` NOT IN ( SELECT `refererID` FROM `" . counterize_refererTable() . "` )";
	$entries = $wpdb->get_results( $sql );
	unset( $sql );
	if( count( $entries ) > 0 )
	{
		foreach( $entries as $entry )
		{
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_agentsTable()  . "` SET `count`=`count` - 1 WHERE `agentID`=%d"  , $entry->agentID   ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_pageTable()    . "` SET `count`=`count` - 1 WHERE `pageID`=%d"   , $entry->pageID    ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_countryTable() . "` SET `count`=`count` - 1 WHERE `countryID`=%d", $entry->countryID ) );
		}
		unset( $entries );
		$sql = "DELETE FROM `" . counterize_logTable() . "` "
			. " WHERE `refererID` NOT IN ( SELECT `refererID` FROM `" . counterize_refererTable() . "` )";
		$wpdb->query( $sql );
		unset( $sql );
	}

	//delete entries related to inexistant pageID
	$sql = "SELECT `agentID`, `pageID`, `refererID`, `countryID` "
		. " FROM `" . counterize_logTable() . "` "
		. " WHERE `pageID` NOT IN ( SELECT `pageID` FROM `" . counterize_pageTable() . "` )";
	$entries = $wpdb->get_results( $sql );
	unset( $sql );
	if( count( $entries ) > 0 )
	{
		foreach( $entries as $entry )
		{
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_agentsTable()  . "` SET `count`=`count` - 1 WHERE `agentID`=%d"  , $entry->agentID   ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_refererTable() . "` SET `count`=`count` - 1 WHERE `refererID`=%d", $entry->refererID ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_countryTable() . "` SET `count`=`count` - 1 WHERE `countryID`=%d", $entry->countryID ) );
		}
		unset( $entries );
		$sql = "DELETE FROM `" . counterize_logTable() . "` "
			. " WHERE `pageID` NOT IN ( SELECT `pageID` FROM `" . counterize_pageTable() . "` )";
		$wpdb->query( $sql );
		unset( $sql );
	}

	//delete entries related to inexistant countryID
	$sql = "SELECT `agentID`, `pageID`, `refererID`, `countryID` "
		. " FROM `" . counterize_logTable() . "` "
		. " WHERE `countryID` NOT IN ( SELECT `countryID` FROM `" . counterize_countryTable() . "` )";
	$entries = $wpdb->get_results( $sql );
	unset( $sql );
	if( count( $entries ) > 0 )
	{
		foreach( $entries as $entry )
		{
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_agentsTable()  . "` SET `count`=`count` - 1 WHERE `agentID`=%d"  , $entry->agentID   ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_refererTable() . "` SET `count`=`count` - 1 WHERE `refererID`=%d", $entry->refererID ) );
			$wpdb->query( $wpdb->prepare( "UPDATE IGNORE `" . counterize_pageTable()    . "` SET `count`=`count` - 1 WHERE `pageID`=%d"   , $entry->pageID    ) );
		}
		unset( $entries );
		$sql = "DELETE FROM `" . counterize_logTable() . "` "
			. " WHERE `countryID` NOT IN ( SELECT `countryID` FROM `" . counterize_countryTable() . "` )";
		$wpdb->query( $sql );
		unset( $sql );
	}

	//delete orphan keywords records (not linked to any referer entry)
	$sql = "DELETE FROM `" . counterize_keywordTable() . "` "
		. " WHERE `keywordID` NOT IN ( SELECT `keywordID` FROM `" . counterize_refererTable() . "` )";
	$wpdb->query( $sql );
	unset( $sql );
}


?>