function get_sales($from, $to, $days, $month)
{
$timezone_shift = get_current_timezone_shift();
$sql = "SELECT *, `cnv`.`date_add` as `date` \n FROM `tbl_conversions` `cnv` \n LEFT JOIN `tbl_clicks` `clc` ON `cnv`.`subid` = `clc`.`subid` \n WHERE (`cnv`.`status` = 0 or `cnv`.`status` = 1)\n AND CONVERT_TZ(`cnv`.`date_add`, '+00:00', '" . _str($timezone_shift) . "') BETWEEN STR_TO_DATE('" . _str($from) . " 00:00:00', '%Y-%m-%d %H:%i:%s') \n AND STR_TO_DATE('" . _str($to) . " 23:59:59', '%Y-%m-%d %H:%i:%s') \n ORDER BY `cnv`.`date_add` ASC";
$r = mysql_query($sql);
if (mysql_num_rows($r) == 0) {
return false;
}
$data = array();
$return = array();
while ($f = mysql_fetch_assoc($r)) {
$data[] = $f;
}
foreach ($data as $row) {
if ($row['source_name'] == '') {
$row['source_name'] = '_';
}
foreach ($days as $day) {
$d = !$month ? date('d.m', strtotime($day)) : $day;
if ($d == date(!$month ? 'd.m' : 'm.Y', strtotime($row['date']))) {
$return[$row['source_name']][$d]++;
}
}
}
return $return;
}
$date_start = date2mysql($date_start);
$date_end = date2mysql($date_end);
$where = '';
if ($campaign_name != '') {
$where .= " and campaign_name='" . mysql_real_escape_string($campaign_name) . "'";
}
if ($ads_name != '') {
$where .= " and ads_name='" . mysql_real_escape_string($ads_name) . "'";
}
$sql = "select count(id) as cnt from tbl_clicks where CONVERT_TZ(date_add, '+00:00', '" . _str($timezone_shift) . "') BETWEEN '" . mysql_real_escape_string($date_start) . " 00:00:00' AND '" . mysql_real_escape_string($date_end) . " 23:59:59' and source_name='" . mysql_real_escape_string($source_name) . "' {$where}";
$rs = mysql_query($sql);
$row = mysql_fetch_assoc($rs);
if ($row['cnt'] > 0) {
$click_price = $costs_value / $row['cnt'];
$click_price = number_format($click_price, 5);
$sql = "update tbl_clicks set click_price='" . mysql_real_escape_string($click_price) . "' where CONVERT_TZ(date_add, '+00:00', '" . _str($timezone_shift) . "') BETWEEN '" . mysql_real_escape_string($date_start) . " 00:00:00' AND '" . mysql_real_escape_string($date_end) . " 23:59:59' and source_name='" . mysql_real_escape_string($source_name) . "' {$where}";
mysql_query($sql);
}
exit;
break;
case 'change_current_timezone':
change_current_timezone($_REQUEST['id']);
exit;
break;
case 'add_adnet':
$id = rq('adnet_id', 2);
add_adnet($id, $_REQUEST['name'], $_REQUEST['url']);
exit;
break;
case 'add_timezone':
add_timezone($_REQUEST['timezone_name'], $_REQUEST['timezone_offset_h']);
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());
}
} else {
$cost_per_click = 0;
}
} else {
if ($IN['clicks_count'] > 0) {
$cost_per_click = convert_currency($IN['cost'], $IN['currency_id'], $main_currency_id, $IN['date_start'], $IN) / $IN['clicks_count'];
} else {
$cost_per_click = 0;
}
}
$result = mysql_query($sql);
$arr_ids = array();
while ($row = mysql_fetch_assoc($result)) {
$arr_ids[] = $row['id'];
}
$sql = "update tbl_clicks set tbl_clicks.click_price='" . _str($cost_per_click) . "' where id IN (" . implode(',', $arr_ids) . ")";
mysql_query($sql);
exit;
break;
case 'change_current_timezone':
change_current_timezone($_REQUEST['id']);
exit;
break;
case 'add_timezone':
add_timezone($_REQUEST['timezone_name'], $_REQUEST['timezone_offset_h']);
exit;
break;
case 'edit_timezone':
update_timezone($_REQUEST['timezone_name'], $_REQUEST['timezone_offset_h'], $_REQUEST['timezone_id']);
exit;
break;
function edit_offer($category_id, $link_name, $link_url)
{
$link_name = trim(str_replace(array("\r\n", "\r", "\n", "\t"), '', $link_name));
$link_url = trim(str_replace(array("\r\n", "\r", "\n", "\t"), '', $link_url));
if (trim($link_url) != '') {
if (!(strpos($link_url, 'http://') === 0 || strpos($link_url, 'https://') === 0)) {
$link_url = "http://{$link_url}";
}
if ($category_id > 0) {
if ($link_name != '') {
// Check if we already have this offer in selected category
$sql = "select id from tbl_offers where offer_name='" . _str($link_name) . "' and offer_tracking_url='" . _str($link_url) . "' and status=0";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
if ($row['id'] > 0) {
$offer_id = $row['id'];
// Offer found, check category
$sql = "select id from tbl_links_categories where offer_id='" . _str($offer_id) . "' and category_id='" . _str($category_id) . "'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
if ($row['id'] > 0) {
// Offer found, don't add
return $offer_id;
}
}
}
} else {
if ($link_name != '') {
// Check if we already have this offer without category
$sql = "select id from tbl_offers where offer_name='" . _str($link_name) . "' and offer_tracking_url='" . _str($link_url) . "' and status=0";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
if ($row['id'] > 0) {
$offer_id = $row['id'];
// Offer found, don't add
return $offer_id;
}
}
}
// Add link
$sql = "insert into tbl_offers (offer_name, offer_tracking_url, date_add) values('" . mysql_real_escape_string($link_name) . "', '" . mysql_real_escape_string($link_url) . "', NOW())";
db_query($sql);
$link_id = mysql_insert_id();
// Set link name instead of empty name
if ($link_name == '') {
$link_name = "Ссылка #{$link_id}";
$sql = "update tbl_offers set offer_name='" . mysql_real_escape_string($link_name) . "' where id='" . mysql_real_escape_string($link_id) . "'";
db_query($sql);
}
if ($category_id > 0) {
// Add link to selected category
$sql = "insert into tbl_links_categories (category_id, offer_id) values ('" . mysql_real_escape_string($category_id) . "', '" . mysql_real_escape_string($link_id) . "')";
db_query($sql);
}
}
cache_links_update();
return $link_id;
}
exit;
}
global $table_n;
if (!isset($table_n)) {
$table_n = 0;
} else {
$table_n++;
}
echo $table_n . '------------------------------';
$days = getDatesBetween($from, $to);
$group_by = rq('group_by', 0, $subtype);
$limited_to = rq('limited_to');
$main_type = $subtype;
$where = '';
if (!empty($limited_to)) {
$where = " and `" . _str($subtype) . "` = '" . _str($limited_to) . "'";
} else {
//$subtype = $group_by;
}
/*
if(empty($params['limited_to'])) {
$group_by = $params['subtype'];
} else {
$group_by = $params['group_by'];
$where = " and `" . _str($params['subtype']) . "` = '" . _str($params['limited_to']) . "'";
}
*/
if ($subtype == 'out_id') {
$id_fld = 'out_id';
} else {
$id_fld = 'name';
?>
',
hour: '<?php
echo _str($hour);
?>
',
filter_by: '<?php
echo _str($_REQUEST['filter_by']);
?>
',
value: '<?php
echo _str($_REQUEST['value']);
?>
',
source_name: '<?php
echo _str($_REQUEST['source_name']);
?>
'
}
).done(
function(data) {
if(data == '') {
$(obj).hide();
} else {
$('#stats-flow tbody').children().last().after(data);
}
}
);
return false;
}
</script><?php
/**
* Выясняем какие споты входят во временой промежуток
* @param string $from начало
* @param string $to конец
* @param type $timezone_shift смещение временного пояса
* @return array
*
* Примеры использования
* clicks_spot_get() - текущий спот
* clicks_spot_get('all') - все споты
* clicks_spot_get('2014-04-12') - все споты, в которых есть записи на весь этот день
* clicks_spot_get('2014-04-12 10:00:00', '2014-04-13 10:00:00') - споты, в которых есть записи за это время
*/
function clicks_spot_get($from = '', $to = '', $timezone_shift = '+00:00')
{
$out = array();
if (empty($from) and empty($to)) {
$where = "`current` = 1";
} elseif ($from == 'all') {
$where = " 1";
} else {
if (!empty($from) and empty($to)) {
$to = $from;
}
if (strlen($from) == 10) {
$from .= ' 00:00:00';
}
if (strlen($to) == 10) {
$to .= ' 23:59:59';
}
if ($timezone_shift == '+00:00') {
$where = "(`time_begin` < STR_TO_DATE('" . $to . "', '%Y-%m-%d %H:%i:%s') AND `time_end` > STR_TO_DATE('" . $from . "', '%Y-%m-%d %H:%i:%s'))";
} else {
$where = "(CONVERT_TZ(`time_begin`, '+00:00', '" . _str($timezone_shift) . "') < STR_TO_DATE('" . $to . "', '%Y-%m-%d %H:%i:%s') AND CONVERT_TZ(`time_end`, '+00:00', '" . _str($timezone_shift) . "') > STR_TO_DATE('" . $from . "', '%Y-%m-%d %H:%i:%s'))";
}
}
$q = "select `id` from `tbl_clicks_map` where " . $where . " order by `id`";
if ($rs = db_query($q) and mysql_num_rows($rs) > 0) {
while ($r = mysql_fetch_assoc($rs)) {
$out[] = $r['id'];
}
}
return $out;
}
