$c1_id = INDEXES::get_c1_id($db, $c1);
$mysql['c1_id'] = $db->real_escape_string($c1_id);
$c2 = $db->real_escape_string($_GET['c2']);
$c2 = str_replace('%20', ' ', $c2);
$c2_id = INDEXES::get_c2_id($db, $c2);
$mysql['c2_id'] = $db->real_escape_string($c2_id);
$c3 = $db->real_escape_string($_GET['c3']);
$c3 = str_replace('%20', ' ', $c3);
$c3_id = INDEXES::get_c3_id($db, $c3);
$mysql['c3_id'] = $db->real_escape_string($c3_id);
$c4 = $db->real_escape_string($_GET['c4']);
$c4 = str_replace('%20', ' ', $c4);
$c4_id = INDEXES::get_c4_id($db, $c4);
$mysql['c4_id'] = $db->real_escape_string($c4_id);
//$device_id = PLATFORMS::get_device_info($db);
$device_id = PLATFORMS::get_device_info($db, $detect, $_GET['ua']);
$mysql['platform_id'] = $db->real_escape_string($device_id['platform']);
$mysql['browser_id'] = $db->real_escape_string($device_id['browser']);
$mysql['device_id'] = $db->real_escape_string($device_id['device']);
if ($device_id['type'] == '4') {
$mysql['click_bot'] = '1';
}
$mysql['click_in'] = 1;
$mysql['click_out'] = 1;
$ip_id = INDEXES::get_ip_id($db, $_SERVER['HTTP_X_FORWARDED_FOR']);
$mysql['ip_id'] = $db->real_escape_string($ip_id);
//before we finish filter this click
$ip_address = $_SERVER['HTTP_X_FORWARDED_FOR'];
$user_id = $tracker_row['user_id'];
//GEO Lookup
$GeoData = getGeoData($ip_address);
function redirect_process($db, $rule, $ppc_account, $cpc, $rotator_id, $GeoData, $ip_address, $user_id, $IspData, $keyword_type)
{
$mysql['aff_campaign_id'] = $db->real_escape_string($rule['aff_campaign_id']);
$mysql['click_cpc'] = $db->real_escape_string($rule['click_cpc']);
$mysql['click_payout'] = $db->real_escape_string($rule['aff_campaign_payout']);
$mysql['rule_id'] = $db->real_escape_string($rule['rule_id']);
$mysql['ppc_account'] = $db->real_escape_string($ppc_account);
$mysql['cpc'] = $db->real_escape_string($cpc);
$mysql['click_time'] = time();
/* ok, if $_GET['OVRAW'] that is a yahoo keyword, if on the REFER, there is a $_GET['q], that is a GOOGLE keyword... */
//so this is going to check the REFERER URL, for a ?q=, which is the ACUTAL KEYWORD searched.
$referer_url_parsed = @parse_url($_SERVER['HTTP_REFERER']);
$referer_url_query = $referer_url_parsed['query'];
@parse_str($referer_url_query, $referer_query);
switch ($keyword_type) {
case "bidded":
#try to get the bidded keyword first
if ($_GET['OVKEY']) {
//if this is a Y! keyword
$keyword = $db->real_escape_string($_GET['OVKEY']);
} elseif ($_GET['utm_source']) {
$keyword = $db->real_escape_string($_GET['utm_source']);
} elseif ($_GET['t202kw']) {
$keyword = $db->real_escape_string($_GET['t202kw']);
} elseif ($referer_query['p']) {
$keyword = $db->real_escape_string($referer_query['p']);
} elseif ($_GET['target_passthrough']) {
//if this is a mediatraffic! keyword
$keyword = $db->real_escape_string($_GET['target_passthrough']);
} else {
//if this is a zango, or more keyword
$keyword = $db->real_escape_string($_GET['keyword']);
}
break;
case "searched":
#try to get the searched keyword
if ($referer_query['q']) {
$keyword = $db->real_escape_string($referer_query['q']);
} elseif ($referer_query['p']) {
$keyword = $db->real_escape_string($referer_query['p']);
} elseif ($_GET['OVRAW']) {
//if this is a Y! keyword
$keyword = $db->real_escape_string($_GET['OVRAW']);
} elseif ($_GET['target_passthrough']) {
//if this is a mediatraffic! keyword
$keyword = $db->real_escape_string($_GET['target_passthrough']);
} elseif ($_GET['keyword']) {
//if this is a zango, or more keyword
$keyword = $db->real_escape_string($_GET['keyword']);
} elseif ($_GET['search_word']) {
//if this is a eniro, or more keyword
$keyword = $db->real_escape_string($_GET['search_word']);
} elseif ($_GET['query']) {
//if this is a naver, or more keyword
$keyword = $db->real_escape_string($_GET['query']);
} elseif ($_GET['encquery']) {
//if this is a aol, or more keyword
$keyword = $db->real_escape_string($_GET['encquery']);
} elseif ($_GET['terms']) {
//if this is a about.com, or more keyword
$keyword = $db->real_escape_string($_GET['terms']);
} elseif ($_GET['rdata']) {
//if this is a viola, or more keyword
$keyword = $db->real_escape_string($_GET['rdata']);
} elseif ($_GET['qs']) {
//if this is a virgilio, or more keyword
$keyword = $db->real_escape_string($_GET['qs']);
} elseif ($_GET['wd']) {
//if this is a baidu, or more keyword
$keyword = $db->real_escape_string($_GET['wd']);
} elseif ($_GET['text']) {
//if this is a yandex, or more keyword
$keyword = $db->real_escape_string($_GET['text']);
} elseif ($_GET['szukaj']) {
//if this is a wp.pl, or more keyword
$keyword = $db->real_escape_string($_GET['szukaj']);
} elseif ($_GET['qt']) {
//if this is a O*net, or more keyword
$keyword = $db->real_escape_string($_GET['qt']);
} elseif ($_GET['k']) {
//if this is a yam, or more keyword
$keyword = $db->real_escape_string($_GET['k']);
} elseif ($_GET['words']) {
//if this is a Rambler, or more keyword
$keyword = $db->real_escape_string($_GET['words']);
} else {
$keyword = $db->real_escape_string($_GET['t202kw']);
}
break;
}
$keyword = str_replace('%20', ' ', $keyword);
$keyword_id = INDEXES::get_keyword_id($db, $keyword);
$mysql['keyword_id'] = $db->real_escape_string($keyword_id);
$c1 = $db->real_escape_string($_GET['c1']);
$c1 = str_replace('%20', ' ', $c1);
$c1_id = INDEXES::get_c1_id($db, $c1);
$mysql['c1_id'] = $db->real_escape_string($c1_id);
$c2 = $db->real_escape_string($_GET['c2']);
$c2 = str_replace('%20', ' ', $c2);
$c2_id = INDEXES::get_c2_id($db, $c2);
$mysql['c2_id'] = $db->real_escape_string($c2_id);
$c3 = $db->real_escape_string($_GET['c3']);
$c3 = str_replace('%20', ' ', $c3);
$c3_id = INDEXES::get_c3_id($db, $c3);
$mysql['c3_id'] = $db->real_escape_string($c3_id);
$c4 = $db->real_escape_string($_GET['c4']);
$c4 = str_replace('%20', ' ', $c4);
$c4_id = INDEXES::get_c4_id($db, $c4);
$mysql['c4_id'] = $db->real_escape_string($c4_id);
$device_id = PLATFORMS::get_device_info($db, $detect, $_GET['ua']);
$mysql['platform_id'] = $db->real_escape_string($device_id['platform']);
$mysql['browser_id'] = $db->real_escape_string($device_id['browser']);
$mysql['device_id'] = $db->real_escape_string($device_id['device']);
if ($device_id['type'] == '4') {
$mysql['click_bot'] = '1';
}
$mysql['click_in'] = 1;
$mysql['click_out'] = 1;
$ip_id = INDEXES::get_ip_id($db, $ip_address);
$mysql['ip_id'] = $db->real_escape_string($ip_id);
$country_id = INDEXES::get_country_id($db, $GeoData['country'], $GeoData['country_code']);
$mysql['country_id'] = $db->real_escape_string($country_id);
$region_id = INDEXES::get_region_id($db, $GeoData['region'], $mysql['country_id']);
$mysql['region_id'] = $db->real_escape_string($region_id);
$city_id = INDEXES::get_city_id($db, $GeoData['city'], $mysql['country_id']);
$mysql['city_id'] = $db->real_escape_string($city_id);
if ($IspData != null) {
$isp_id = INDEXES::get_isp_id($db, $IspData);
$mysql['isp_id'] = $db->real_escape_string($isp_id);
}
if ($device_id['type'] == '4') {
$mysql['click_filtered'] = '1';
} else {
$click_filtered = FILTER::startFilter($db, $click_id, $ip_id, $ip_address, $user_id);
$mysql['click_filtered'] = $db->real_escape_string($click_filtered);
}
if ($_GET[lpr] != '') {
$click_sql1 = "\tSELECT \t202_clicks.click_id,keyword,keyword_id\n\t\t\t\t\tFROM \t\t202_clicks\n\t\t\t\t\tLEFT JOIN\t202_clicks_advance USING (click_id)\n\t\t\t\t\tLEFT JOIN \t202_ips USING (ip_id) \n\t\t\t\t\tLEFT JOIN \t202_keywords USING (keyword_id) \n\t\t\t\t\tWHERE \t202_ips.ip_address='" . $ip_address . "'\n\t\t\t\t\tAND\t\t202_clicks.user_id='" . $user_id . "' \n\t\t\t\t\tAND\t\t202_clicks.click_time >= '30'\n\t\t\t\t\tORDER BY \t202_clicks.click_id DESC \n\t\t\t\t\tLIMIT \t\t1";
$click_result1 = $db->query($click_sql1) or record_mysql_error($click_sql1);
$click_row1 = $click_result1->fetch_assoc();
$mysql['click_id'] = $db->real_escape_string($click_row1['click_id']);
$keyword = $db->real_escape_string($keyword);
$keyword_id = $db->real_escape_string($click_row1['keyword_id']);
$mysql['keyword_id'] = $db->real_escape_string($keyword_id);
} else {
//ok we have the main data, now insert this row
$click_sql = "INSERT INTO 202_clicks_counter SET click_id=DEFAULT";
$click_result = $db->query($click_sql) or record_mysql_error($db, $click_sql);
//now gather the info for the advance click insert
$click_id = $db->insert_id;
$mysql['click_id'] = $db->real_escape_string($click_id);
}
$mysql['click_alp'] = 0;
$mysql['rotator_id'] = $db->real_escape_string($rotator_id);
$mysql['user_id'] = $db->real_escape_string($user_id);
//ok we have the main data, now insert this row
$click_sql = "REPLACE INTO 202_clicks\n\t\t\t SET \tclick_id='" . $mysql['click_id'] . "',\n\t\t\t\t\t\t\tuser_id = '" . $mysql['user_id'] . "', \n\t\t\t\t\t\t\taff_campaign_id = '" . $mysql['aff_campaign_id'] . "', \n\t\t\t\t\t\t\tppc_account_id = '" . $mysql['ppc_account'] . "', \n\t\t\t\t\t\t\tclick_cpc = '" . $mysql['cpc'] . "', \n\t\t\t\t\t\t\tclick_payout = '" . $mysql['click_payout'] . "', \n\t\t\t\t\t\t\tclick_alp = '" . $mysql['click_alp'] . "',\n\t\t\t\t\t\t\tclick_filtered = '" . $mysql['click_filtered'] . "',\n\t\t\t\t\t\t\tclick_bot = '" . $mysql['click_bot'] . "',\n\t\t\t\t\t\t\tclick_time = '" . $mysql['click_time'] . "',\n\t\t\t\t\t\t\trotator_id = '" . $mysql['rotator_id'] . "',\n\t\t\t\t\t\t\trule_id = '" . $mysql['rule_id'] . "'";
$click_result = $db->query($click_sql) or record_mysql_error($db, $click_sql);
//ok we have the main data, now insert this row
$click_sql = "REPLACE INTO 202_clicks_spy\n\t\t\t\t SET \tclick_id='" . $mysql['click_id'] . "',\n\t\t\t\t\t\t\t\tuser_id = '" . $mysql['user_id'] . "', \n\t\t\t\t\t\t\t\taff_campaign_id = '" . $mysql['aff_campaign_id'] . "', \n\t\t\t\t\t\t\t\tppc_account_id = '" . $mysql['ppc_account'] . "', \n\t\t\t\t\t\t\t\tclick_cpc = '" . $mysql['cpc'] . "', \n\t\t\t\t\t\t\t\tclick_payout = '" . $mysql['click_payout'] . "', \n\t\t\t\t\t\t\t\tclick_filtered = '" . $mysql['click_filtered'] . "',\n\t\t\t\t\t\t\t\tclick_bot = '" . $mysql['click_bot'] . "',\n\t\t\t\t\t\t\t\tclick_alp = '" . $mysql['click_alp'] . "',\n\t\t\t\t\t\t\t\tclick_time = '" . $mysql['click_time'] . "'";
$click_result = $db->query($click_sql) or record_mysql_error($db, $click_sql);
//now we have the click's advance data, now insert this row
$click_sql = "REPLACE INTO 202_clicks_advance\n\t\t\t SET click_id='" . $mysql['click_id'] . "',\n\t\t\t\t\t\t\ttext_ad_id='" . $mysql['text_ad_id'] . "',\n\t\t\t\t\t\t\tkeyword_id='" . $mysql['keyword_id'] . "',\n\t\t\t\t\t\t\tip_id='" . $mysql['ip_id'] . "',\n\t\t\t\t\t\t\tcountry_id='" . $mysql['country_id'] . "',\n\t\t\t\t\t\t\tregion_id='" . $mysql['region_id'] . "',\n\t\t\t\t\t\t\tisp_id='" . $mysql['isp_id'] . "',\n\t\t\t\t\t\t\tcity_id='" . $mysql['city_id'] . "',\n\t\t\t\t\t\t\tplatform_id='" . $mysql['platform_id'] . "',\n\t\t\t\t\t\t\tbrowser_id='" . $mysql['browser_id'] . "',\n\t\t\t\t\t\t\tdevice_id='" . $mysql['device_id'] . "'";
$click_result = $db->query($click_sql) or record_mysql_error($db, $click_sql);
//insert the tracking data
$click_sql = "\n\tREPLACE INTO\n\t\t202_clicks_tracking\n\tSET\n\t\tclick_id='" . $mysql['click_id'] . "',\n\t\tc1_id = '" . $mysql['c1_id'] . "',\n\t\tc2_id = '" . $mysql['c2_id'] . "',\n\t\tc3_id = '" . $mysql['c3_id'] . "',\n\t\tc4_id = '" . $mysql['c4_id'] . "'";
$click_result = $db->query($click_sql) or record_mysql_error($db, $click_sql);
//now gather variables for the clicks record db
//lets determine if cloaking is on
if ($rule['aff_campaign_cloaking'] == 1) {
$cloaking_on = true;
$mysql['click_cloaking'] = 1;
//if cloaking is on, add in a click_id_public, because we will be forwarding them to a cloaked /cl/xxxx link
$click_id_public = rand(1, 9) . $click_id . rand(1, 9);
$mysql['click_id_public'] = $db->real_escape_string($click_id_public);
} else {
$mysql['click_cloaking'] = 0;
}
//ok we have our click recorded table, now lets insert theses
$click_sql = "REPLACE INTO 202_clicks_record\n\t\t\t SET click_id='" . $mysql['click_id'] . "',\n\t\t\t\t\t\t\tclick_id_public='" . $mysql['click_id_public'] . "',\n\t\t\t\t\t\t\tclick_cloaking='" . $mysql['click_cloaking'] . "',\n\t\t\t\t\t\t\tclick_in='" . $mysql['click_in'] . "',\n\t\t\t\t\t\t\tclick_out='" . $mysql['click_out'] . "'";
$click_result = $db->query($click_sql) or record_mysql_error($db, $click_sql);
//now lets get variables for clicks site
//so this is going to check the REFERER URL, for a ?url=, which is the ACUTAL URL, instead of the google content, pagead2.google....
if ($referer_query['url']) {
$click_referer_site_url_id = INDEXES::get_site_url_id($db, $referer_query['url']);
} else {
$click_referer_site_url_id = INDEXES::get_site_url_id($db, $_SERVER['HTTP_REFERER']);
}
$mysql['click_referer_site_url_id'] = $db->real_escape_string($click_referer_site_url_id);
$outbound_site_url = 'http://' . $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];
$click_outbound_site_url_id = INDEXES::get_site_url_id($db, $outbound_site_url);
$mysql['click_outbound_site_url_id'] = $db->real_escape_string($click_outbound_site_url_id);
if ($cloaking_on == true) {
$cloaking_site_url = 'http://' . $_SERVER['SERVER_NAME'] . '/tracking202/redirect/cl.php?pci=' . $click_id_public;
}
if ($rule['aff_campaign_id'] != null) {
//rotate the urls
$redirect_site_url = rotateTrackerUrl($db, $rule);
} else {
if ($rule['default_url'] != null) {
$redirect_site_url = $rule['default_url'];
} elseif ($rule['redirect_url'] != null) {
$redirect_site_url = $rule['redirect_url'];
}
}
$redirect_site_url = replaceTrackerPlaceholders($db, $redirect_site_url, $click_id);
$click_redirect_site_url_id = INDEXES::get_site_url_id($db, $redirect_site_url);
$mysql['click_redirect_site_url_id'] = $db->real_escape_string($click_redirect_site_url_id);
//insert this
$click_sql = "REPLACE INTO 202_clicks_site\n\t\t\t SET click_id='" . $mysql['click_id'] . "',\n\t\t\t\t\t\t\tclick_referer_site_url_id='" . $mysql['click_referer_site_url_id'] . "',\n\t\t\t\t\t\t\tclick_outbound_site_url_id='" . $mysql['click_outbound_site_url_id'] . "',\n\t\t\t\t\t\t\tclick_redirect_site_url_id='" . $mysql['click_redirect_site_url_id'] . "'";
$click_result = $db->query($click_sql) or record_mysql_error($db, $click_sql);
//update the click summary table
$now = time();
$today_day = date('j', time());
$today_month = date('n', time());
$today_year = date('Y', time());
//the click_time is recorded in the middle of the day
$click_time = mktime(12, 0, 0, $today_month, $today_day, $today_year);
$mysql['click_time'] = $db->real_escape_string($click_time);
//check to make sure this click_summary doesn't already exist
$check_sql = "SELECT *\n\t\t\t\t FROM 202_summary_overview\n\t\t\t\t WHERE user_id='" . $mysql['user_id'] . "'\n\t\t\t\t AND aff_campaign_id='" . $mysql['aff_campaign_id'] . "'\n\t\t\t\t AND ppc_account_id='" . $mysql['ppc_account'] . "'\n\t\t\t\t AND click_time='" . $mysql['click_time'] . "'";
$check_result = $db->query($check_sql) or record_mysql_error($db, $check_sql);
$check_count = $check_result->num_rows;
//if this click summary hasn't been recorded do this now
if ($check_count == 0) {
$insert_sql = "INSERT INTO 202_summary_overview\n\t\t\t\t\t SET user_id='" . $mysql['user_id'] . "',\n\t\t\t\t\t\t\t\t aff_campaign_id='" . $mysql['aff_campaign_id'] . "',\n\t\t\t\t\t\t\t\t ppc_account_id='" . $mysql['ppc_account'] . "',\n\t\t\t\t\t\t\t\t click_time='" . $mysql['click_time'] . "'";
$insert_result = $db->query($insert_sql);
}
if ($rule['aff_campaign_id'] != null) {
//set the cookie
setClickIdCookie($mysql['click_id'], $rule['aff_campaign_id']);
}
//now we've recorded, now lets redirect them
if ($cloaking_on == true) {
//if cloaked, redirect them to the cloaked site.
return $cloaking_site_url;
} else {
return $redirect_site_url;
}
}
function get_device_info()
{
global $memcacheWorking, $memcache;
$detect = new Mobile_Detect();
$ua = $detect->getUserAgent();
//If Cache working
if ($memcacheWorking) {
$device_info = $memcache->get(md5("user-agent" . $ua . systemHash()));
if (!$device_info) {
$parse_info = PLATFORMS::parseUserAgentInfo($detect);
$memcache->set(md5("user-agent" . $ua . systemHash()), $parse_info, false);
return $parse_info;
} else {
return $device_info;
}
} else {
return PLATFORMS::parseUserAgentInfo($detect);
}
}
function parseUserAgentInfo($db, $detect)
{
$parser = Parser::create();
$result = $parser->parse($detect->getUserAgent());
//If is not mobile or tablet
if (!$detect->isMobile() && !$detect->isTablet()) {
switch ($result->device->family) {
//Is Bot
case 'Bot':
$type = "4";
$result->device->family = "Bot";
break;
//Is Desktop
//Is Desktop
case 'Other':
$type = "1";
$result->device->family = "Desktop";
break;
}
} else {
//If tablet
if ($detect->isTablet()) {
$type = "3";
//If mobile
} else {
$type = "2";
}
}
if (PLATFORMS::botCheck($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$type = "4";
$result->device->family = "Bot";
}
//Select from DB and return ID's
$mysql['browser'] = $db->real_escape_string($result->ua->family);
$mysql['platform'] = $db->real_escape_string($result->os->family);
$mysql['device'] = $db->real_escape_string($result->device->family);
$mysql['device_type'] = $db->real_escape_string($type);
//Get browser ID
$browser_sql = "SELECT browser_id FROM 202_browsers WHERE browser_name='" . $mysql['browser'] . "'";
$browser_result = _mysqli_query($db, $browser_sql);
$browser_row = $browser_result->fetch_assoc();
if ($browser_row) {
$browser_id = $browser_row['browser_id'];
} else {
$browser_sql = "INSERT INTO 202_browsers SET browser_name='" . $mysql['browser'] . "'";
$browser_result = _mysqli_query($db, $browser_sql);
$browser_id = $db->insert_id;
}
//Get platform ID
$platform_sql = "SELECT platform_id FROM 202_platforms WHERE platform_name='" . $mysql['platform'] . "'";
$platform_result = _mysqli_query($db, $platform_sql);
$platform_row = $platform_result->fetch_assoc();
if ($platform_row) {
$platform_id = $platform_row['platform_id'];
} else {
$platform_sql = "INSERT INTO 202_platforms SET platform_name='" . $mysql['platform'] . "'";
$platform_result = _mysqli_query($db, $platform_sql);
$platform_id = $db->insert_id;
}
//Get device model ID
$device_sql = "SELECT device_id, device_type FROM 202_device_models WHERE device_name='" . $mysql['device'] . "'";
$device_result = _mysqli_query($db, $device_sql);
$device_row = $device_result->fetch_assoc();
if ($device_row) {
$device_id = $device_row['device_id'];
$device_type = $device_row['device_type'];
} else {
$device_sql = "INSERT INTO 202_device_models SET device_name='" . $mysql['device'] . "', device_type='" . $mysql['device_type'] . "'";
$device_result = _mysqli_query($db, $device_sql);
$device_id = $db->insert_id;
$device_type = $type;
}
$data = array('browser' => $browser_id, 'platform' => $platform_id, 'device' => $device_id, 'type' => $device_type);
return $data;
}
