function save_click_info($arr_click_info, $timeshift = 0) { // User-agent parser require_once _TRACK_LIB_PATH . "/ua-parser/uaparser.php"; $parser = new UAParser(); // WURFL mobile database global $wurflManager; if ($timeshift != 0) { $click_date = date("Y-m-d H:i:s", strtotime($arr_click_info[0]) + $timeshift); } else { $click_date = $arr_click_info[0]; } $click_day = current(explode(' ', $click_date)); $click_hour = get_hour_by_date($click_date); $click_ip = $arr_click_info[1]; // Get geo from IP $geo_data = get_geodata($click_ip); $click_country = $geo_data['country']; $click_state = $geo_data['state']; $click_city = $geo_data['city']; $click_region = $geo_data['region']; $click_isp = $geo_data['isp']; // Get info from user agent $click_user_agent = $arr_click_info[2]; // Set empty initial values $is_mobile_device = false; $is_tablet = false; $is_phone = false; $brand_name = ''; $model_name = ''; $model_extra_info = ''; $device_os = ''; $device_os_version = ''; $device_browser = ''; $device_browser_version = ''; if (extension_loaded('xmlreader')) { $requestingDevice = $wurflManager->getDeviceForUserAgent($click_user_agent); $is_wireless = $requestingDevice->getCapability('is_wireless_device') == 'true'; $is_tablet = $requestingDevice->getCapability('is_tablet') == 'true'; $is_mobile_device = $is_wireless || $is_tablet; // Use WURFL database info for mobile devices only if ($is_mobile_device) { $is_phone = $requestingDevice->getCapability('can_assign_phone_number') == 'true'; $brand_name = $requestingDevice->getCapability('brand_name'); $model_name = $requestingDevice->getCapability('model_name'); $model_extra_info = $requestingDevice->getCapability('model_extra_info'); $device_os = $requestingDevice->getCapability('device_os'); $device_os_version = $requestingDevice->getCapability('device_os_version'); $device_browser = $requestingDevice->getCapability('mobile_browser'); $device_browser_version = $requestingDevice->getCapability('mobile_browser_version'); } else { // Use UAParser to get click info $result = $parser->parse($click_user_agent); $device_browser = $result->ua->family; $device_browser_version = $result->ua->toVersionString; $device_os = $result->os->family; $device_os_version = $result->os->toVersionString; } } $click_referer = $arr_click_info[3]; $click_link_name = $arr_click_info[4]; $click_link_source = $arr_click_info[5]; // Allow to use - as campaign/ads delimiter $link_ads_name = $arr_click_info[6]; if (strpos($link_ads_name, '-') !== false) { $click_link_campaign = current(explode('-', $link_ads_name)); $click_link_ads = substr($link_ads_name, strpos($link_ads_name, '-') + 1); } else { $click_link_campaign = $link_ads_name; $click_link_ads = ''; } $click_subid = $arr_click_info[7]; $click_subaccount = $arr_click_info[8]; $click_rule_id = $arr_click_info[9]; $click_out_id = $arr_click_info[10]; $click_is_unique = $arr_click_info[11]; $click_param1 = $arr_click_info[12]; $click_param2 = $arr_click_info[13]; $click_param3 = $arr_click_info[14]; $click_param4 = $arr_click_info[15]; $click_param5 = $arr_click_info[16]; // Parse get string if (!empty($arr_click_info[17])) { parse_str($arr_click_info[17], $click_get_params); } else { $click_get_params = array(); } if (!empty($_GET['debug'])) { dmp($arr_click_info); //die(); } $sql_click_params = array(); // Save this source params global $source_config; /* to_log('src', $click_link_source); to_log('src', $source_config); to_log('src', $click_get_params); */ $i = 1; // Source config exists if (array_key_exists($click_link_source, $source_config) and array_key_exists('params', $source_config[$click_link_source])) { // Выбираем именованные параметры из того, что пришло foreach ($source_config[$click_link_source]['params'] as $param_name => $param_info) { if (empty($param_info['url'])) { continue; } // "виртуальный" параметр, он определяется не ссылкой, а через другие параметры, см ниже if (array_key_exists($param_name, $click_get_params)) { $param_value = $click_get_params[$param_name]; if ($param_info['url'] == $param_value) { $param_value = ''; } // Пришло site_id={site_id}, то есть значение пустое $sql_click_params[] = "click_param_name{$i}='" . _str($param_name) . "', click_param_value{$i}='" . _str($param_value) . "'"; // Adwords передает в одном параметре и то что это спецразмещение и то, что это реклама в сайдбаре и сразу же позицию объявления. Поэтому мы разбиваем это значение на два параметра, Размещение (если t - Спецразмещение, если s - Реклама справа, если o или что-то другое - Не определено) и Позиция (где выводим значения как есть, то есть 1t1, 1s2 и т.д.) // Пример "виртуального параметра" if ($click_link_source == 'adwords' and $param_name == 'adposition') { $i++; $position_type = 0; if (strstr($param_value, 's') !== false) { $position_type = 's'; } if (strstr($param_value, 't') !== false) { $position_type = 't'; } $sql_click_params[] = "click_param_name{$i}='position_type', click_param_value{$i}='" . _str($position_type) . "'"; } // Поисковые слова Яндекса if ($click_link_source == 'yadirect' and $param_name == 'ad_id') { $i++; // 17 - для прямых ссылок, 3 - для обычных $referer = empty($arr_click_info[3]) ? $arr_click_info[17] : $arr_click_info[3]; $sql_click_params[] = "click_param_name{$i}='text', click_param_value{$i}='" . _str(parse_search_refer($referer)) . "'"; } unset($click_get_params[$param_name]); // Параметр отработан, убираем его чтобы остались только пользовательские } $i++; } // Удаляем параметры из чёрной списка (для трекеров, которые шлют нам много лишнего) if (isset($source_config[$click_link_source]['rapams_ignore'])) { foreach ($source_config[$click_link_source]['rapams_ignore'] as $param_name) { unset($click_get_params[$param_name]); } } // Удаляем дополнительные параметры "прямого перехода" $direct_params = array('utm_source', 'rule_name', 'utm_campaign'); foreach ($direct_params as $param_name) { unset($click_get_params[$param_name]); } /* foreach($click_get_params as $param_name => $param_value) { if(!empty($source_config[$click_link_source]['params'][$param_name]['n'])) { //$i = $source_config[$click_link_source]['params'][$param_name]['n'] + 5; $sql_click_params[]="click_param_name{$i}='"._str($param_name)."', click_param_value{$i}='"._str($param_value)."'"; // Adwords передает в одном параметре и то что это спецразмещение и то, что это реклама в сайдбаре и сразу же позицию объявления. Поэтому мы разбиваем это значение на два параметра, Размещение (если t - Спецразмещение, если s - Реклама справа, если o или что-то другое - Не определено) и Позиция (где выводим значения как есть, то есть 1t1, 1s2 и т.д.) if($click_link_source == 'adwords' and $param_name == 'adposition') { $position_type = 0; if(strstr($param_value, 's') !== false) { $position_type = 's'; } if(strstr($param_value, 't') !== false) { $position_type = 't'; } $sql_click_params[]="click_param_name10='position_type', click_param_value10='"._str($position_type)."'"; } unset($click_get_params[$param_name]); } } */ } // Пользовательские параметры $is_connected = false; $connected_subid = ''; foreach ($click_get_params as $param_name => $param_value) { if ($param_name == '_subid') { $pattern = '/\\d{14}x\\d{5}/'; preg_match_all($pattern, $param_value, $subids); foreach ($subids[0] as $t_key => $t_subid) { if ($t_subid != '') { $is_connected = true; $connected_subid = $t_subid; } break; } continue; } $sql_click_params[] = "click_param_name{$i}='" . _str($param_name) . "', click_param_value{$i}='" . _str($param_value) . "'"; $i++; // Maximum 15 get parameters allowed if ($i > 15) { break; } } $sql_click_params = implode(', ', $sql_click_params); if (strlen($sql_click_params) > 0) { $sql_click_params = ", {$sql_click_params}"; } // Click from landing page if ($is_connected) { // Get parent click id $sql = "select id from tbl_clicks where subid='" . _str($connected_subid) . "' limit 1"; $result = mysql_query($sql); $row = mysql_fetch_assoc($result); if ($row['id'] > 0) { $parent_id = $row['id']; $sql = "update tbl_clicks set is_parent=1 where id='" . _str($parent_id) . "'"; mysql_query($sql); } else { $parent_id = 0; } } $sql = "insert ignore into tbl_clicks SET\n\t\t\t\tdate_add='" . _str($click_date) . "', \n\t\t\t\tdate_add_day='" . _str($click_day) . "', \n\t\t\t\tdate_add_hour='" . _str($click_hour) . "', \n\t\t\t\tuser_ip='" . _str($click_ip) . "', \n\t\t\t\tuser_agent='" . _str($click_user_agent) . "', \n\t\t\t\tuser_os='" . _str($device_os) . "', \n\t\t\t\tuser_os_version='" . _str($device_os_version) . "', \t\t\t\t\n\t\t\t\tuser_platform='" . _str($brand_name) . "', \n\t\t\t\tuser_platform_info='" . _str($model_name) . "', \t\t\n\t\t\t\tuser_platform_info_extra='" . _str($model_extra_info) . "',\t\t\t\n\t\t\t\tuser_browser='" . _str($device_browser) . "', \n\t\t\t\tuser_browser_version='" . _str($device_browser_version) . "',\t\t\t\t\t\n\t\t\t\tis_mobile_device='" . _str($is_mobile_device) . "', \n\t\t\t\tis_phone='" . _str($is_phone) . "', \t\t\n\t\t\t\tis_tablet='" . _str($is_tablet) . "', \t\t\t\t\t\n\t\t\t\tcountry='" . _str($click_country) . "', \n\t\t\t\tstate='" . _str($click_state) . "', \n\t\t\t\tcity='" . _str($click_city) . "', \n\t\t\t\tregion='" . _str($click_region) . "', \n\t\t\t\tisp='" . _str($click_isp) . "', \n\t\t\t\trule_id='" . _str($click_rule_id) . "', \n\t\t\t\tout_id='" . _str($click_out_id) . "', \n\t\t\t\tsubid='" . _str($click_subid) . "', \n\t\t\t\tis_connected='" . _str($is_connected) . "', \n\t\t\t\tis_unique='" . _str($click_is_unique) . "', \n\t\t\t\tparent_id='" . _str($parent_id) . "', \n\t\t\t\tsubaccount='" . _str($click_subaccount) . "', \n\t\t\t\tsource_name='" . _str($click_link_source) . "', \n\t\t\t\tcampaign_name='" . _str($click_link_campaign) . "', \n\t\t\t\tads_name='" . _str($click_link_ads) . "', \n\t\t\t\treferer='" . _str($click_referer) . "', \n\t\t\t\tsearch_string='', \n\t\t\t\tcampaign_param1='" . _str($click_param1) . "', \n\t\t\t\tcampaign_param2='" . _str($click_param2) . "', \n\t\t\t\tcampaign_param3='" . _str($click_param3) . "', \n\t\t\t\tcampaign_param4='" . _str($click_param4) . "', \n\t\t\t\tcampaign_param5='" . _str($click_param5) . "'\n\t\t\t\t{$sql_click_params}"; //echo $sql . '<br />'; echo $click_subid . ' '; mysql_query($sql); // or die($sql . '<br >' . mysql_error()); }
function save_click_info($arr_click_info, $timeshift = 0) { global $link; $IN = array(); $OUT = array(); $parent_subid = ''; $IN['date_time'] = isset($arr_click_info[0]) ? $arr_click_info[0] : ''; $IN['ip'] = isset($arr_click_info[1]) ? $arr_click_info[1] : ''; $IN['user_agent'] = isset($arr_click_info[2]) ? $arr_click_info[2] : ''; $IN['referer'] = isset($arr_click_info[3]) ? $arr_click_info[3] : ''; $IN['link_name'] = isset($arr_click_info[4]) ? $arr_click_info[4] : ''; $IN['source'] = isset($arr_click_info[5]) ? $arr_click_info[5] : ''; $IN['campaign_ads'] = isset($arr_click_info[6]) ? $arr_click_info[6] : ''; $IN['subid'] = isset($arr_click_info[7]) ? $arr_click_info[7] : ''; $IN['subaccount'] = isset($arr_click_info[8]) ? $arr_click_info[8] : ''; $IN['link_id'] = isset($arr_click_info[9]) ? $arr_click_info[9] : ''; $IN['offer_id'] = isset($arr_click_info[10]) ? $arr_click_info[10] : ''; $IN['is_unique'] = isset($arr_click_info[11]) ? $arr_click_info[11] : ''; $IN['link_param1'] = isset($arr_click_info[12]) ? $arr_click_info[12] : ''; $IN['link_param2'] = isset($arr_click_info[13]) ? $arr_click_info[13] : ''; $IN['link_param3'] = isset($arr_click_info[14]) ? $arr_click_info[14] : ''; $IN['link_param4'] = isset($arr_click_info[15]) ? $arr_click_info[15] : ''; $IN['link_param5'] = isset($arr_click_info[16]) ? $arr_click_info[16] : ''; $IN['visit_params'] = isset($arr_click_info[17]) ? $arr_click_info[17] : ''; // 1. Click date $OUT['date_time'] = $timeshift != 0 ? date("Y-m-d H:i:s", strtotime($IN['date_time']) + $timeshift) : $IN['date_time']; // 2. Click day $OUT['day'] = current(explode(' ', $IN['date_time'])); // 3. Click hour $OUT['hour'] = get_hour_by_date($IN['date_time']); // 4. IP address $OUT['ip'] = $IN['ip']; // 5. Country, State, City, Region, ISP list($OUT['country'], $OUT['state'], $OUT['city'], $OUT['region'], $OUT['isp']) = get_country_city_isp($IN['ip']); // 6. User-Agent $OUT['user_agent'] = $IN['user_agent']; // 7. OS, OS version, Platform, Platform Info, Platform info extra, Browser, Browser version, Is mobile device, Is phone, Is tablet list($OUT['os'], $OUT['os_version'], $OUT['platform'], $OUT['platform_info'], $OUT['platform_info_extra'], $OUT['browser'], $OUT['browser_version'], $OUT['is_mobile_device'], $OUT['is_phone'], $OUT['is_tablet']) = get_os_browser_platform($IN['user_agent']); // 8. Campaing and ads list($OUT['campaign'], $OUT['ads']) = parse_campaign_ads($IN['campaign_ads']); // 9. SubID and subaccount $OUT['subid'] = $IN['subid']; $OUT['subaccount'] = $IN['subaccount']; // 10. Link ID and offer ID $OUT['link_id'] = $IN['link_id']; $OUT['offer_id'] = $IN['offer_id']; // 11. Source $OUT['source'] = $IN['source']; // 12. Is unique click $OUT['is_unique'] = $IN['is_unique']; // 13. Referer $OUT['referer'] = $IN['referer']; // 14. Referer domain $OUT['referer_domain'] = parse_url($IN['referer'], PHP_URL_HOST); // 15. Keyword $OUT['keyword'] = parse_search_referer($IN['referer']); // 16. Links params 1-5 $OUT['link_param1'] = $IN['link_param1']; $OUT['link_param2'] = $IN['link_param2']; $OUT['link_param3'] = $IN['link_param3']; $OUT['link_param4'] = $IN['link_param4']; $OUT['link_param5'] = $IN['link_param5']; // 17. Visit params, parse and check for clicks from landing page $OUT['visit_params'] = parse_visit_params($IN, $parent_subid); // 18. Process clicks from landing page $OUT['parent_id'] = 0; $OUT['is_connected'] = false; if ($parent_subid != '') { $id = 0; $sql_prepared = "select id from tbl_clicks where subid= ? limit 1"; $stmt = $link->prepare($sql_prepared); $stmt->bind_param('s', $parent_subid); $stmt->execute(); $stmt->bind_result($id); while ($stmt->fetch()) { } if ($id > 0) { $OUT['is_connected'] = true; $OUT['parent_id'] = $id; $sql_prepared = "update tbl_clicks set is_parent=1 where id= ?"; $stmt = $link->prepare($sql_prepared); $stmt->bind_param('i', $OUT['parent_id']); $result = $stmt->execute(); if ($result === false) { // Were not able to run query, exiting with error exit; } } $stmt->close(); } // 19. Save current click in DB $arr_prepared_visit_params = array(); for ($i = 1; $i <= count($OUT['visit_params']) / 2; $i++) { $arr_prepared_visit_params[] = "`click_param_name{$i}`= ?, `click_param_value{$i}`= ?"; } $bind_visit_params = array_values($OUT['visit_params']); $sql_prepared_visit_params = implode(',', $arr_prepared_visit_params); if (strlen($sql_prepared_visit_params) > 0) { $sql_prepared_visit_params = ", {$sql_prepared_visit_params}"; } $sql_prepared = "INSERT IGNORE INTO tbl_clicks SET `date_add`= ?, `date_add_day`= ?, `date_add_hour`= ?, `user_ip`= ?, `user_agent`= ?, `user_os`= ?, `user_os_version`= ?, `user_platform`= ?, `user_platform_info`= ?, `user_platform_info_extra`= ?, `user_browser`= ?, `user_browser_version`= ?, `is_mobile_device`= ?, `is_phone`= ?, `is_tablet`= ?, `country`= ?, `state`= ?, `city`= ?, `region`= ?, `isp`= ?, `rule_id`= ?, `out_id`= ?, `subid`= ?, `is_connected`= ?, `is_unique`= ?, `parent_id`= ?, `subaccount`= ?, `source_name`= ?, `campaign_name`= ?, `ads_name`= ?, `referer`= ?, `referer_domain`= ?, `search_string`= ?, `campaign_param1`= ?, `campaign_param2`= ?, `campaign_param3`= ?, `campaign_param4`= ?, `campaign_param5`= ?{$sql_prepared_visit_params}"; $arr_bind_values = array($OUT['date_time'], $OUT['day'], $OUT['hour'], $OUT['ip'], $OUT['user_agent'], $OUT['os'], $OUT['os_version'], $OUT['platform'], $OUT['platform_info'], $OUT['platform_info_extra'], $OUT['browser'], $OUT['browser_version'], $OUT['is_mobile_device'], $OUT['is_phone'], $OUT['is_tablet'], $OUT['country'], $OUT['state'], $OUT['city'], $OUT['region'], $OUT['isp'], $OUT['link_id'], $OUT['offer_id'], $OUT['subid'], $OUT['is_connected'], $OUT['is_unique'], $OUT['parent_id'], $OUT['subaccount'], $OUT['source'], $OUT['campaign'], $OUT['ads'], $OUT['referer'], $OUT['referer_domain'], $OUT['keyword'], $OUT['link_param1'], $OUT['link_param2'], $OUT['link_param3'], $OUT['link_param4'], $OUT['link_param5']); $arr_bind_values = array_merge(array('ssisssssssssiiissssssssiiissssssssssss' . str_repeat('s', count($bind_visit_params))), $arr_bind_values, $bind_visit_params); // Create array of references, so bind can work for ($i = 0; $i < count($arr_bind_values); $i++) { $arr_bind_values[$i] =& $arr_bind_values[$i]; } $res = $link->prepare($sql_prepared); $ref = new ReflectionClass('mysqli_stmt'); $method = $ref->getMethod("bind_param"); $method->invokeArgs($res, $arr_bind_values); $result = $res->execute(); if ($result === false) { // Were not able to run query, exiting with error exit; } }