function get_visitors_flow_data($filter = '', $offset = 0, $limit = 20, $date = 0)
{
if (empty($date) or !preg_match('/^\\d{4}-\\d{2}-\\d{2}$/', $date)) {
$timezone_shift_simple = get_current_timezone_shift(true);
$date = date('Y-m-d', time() + $timezone_shift_simple);
}
$timezone_shift = get_current_timezone_shift();
$filter_str = '';
if ($filter != '') {
switch ($filter['filter_by']) {
case 'hour':
if (empty($filter['source_name'])) {
$where = "(`source_name` = '' OR `source_name` = 'source' OR `source_name` = 'SOURCE' OR `source_name` = '{empty}')";
} else {
$where = "`source_name` = '" . mysql_real_escape_string($filter['source_name']) . "'";
}
$filter_str .= " and " . $where . " AND CONVERT_TZ(date_add, '+00:00', '" . _str($timezone_shift) . "') BETWEEN STR_TO_DATE('" . _str($filter['date']) . " " . _str(sprintf('%02d', $filter['hour'])) . ":00:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('" . _str($filter['date']) . " " . _str(sprintf('%02d', $filter['hour'])) . ":59:59', '%Y-%m-%d %H:%i:%s')";
break;
//STR_TO_DATE('2014-12-14 00:00:00', '%Y-%m-%d %H:%i:%s')
// поиск по названию кампании, объявления, рефереру, SubID, источнику, IP адресу
//STR_TO_DATE('2014-12-14 00:00:00', '%Y-%m-%d %H:%i:%s')
// поиск по названию кампании, объявления, рефереру, SubID, источнику, IP адресу
case 'search':
if (is_subid($filter['filter_value'])) {
$filter_str .= " and `subid` LIKE '" . mysql_real_escape_string($filter['filter_value']) . "'";
$date = false;
// ищем за всё время
} else {
$filter_str .= " and (\n\t\t\t\t\t\t\t`user_ip` LIKE '" . mysql_real_escape_string($filter['filter_value']) . "' OR\n\t\t\t\t\t\t\t`campaign_name` LIKE '%" . mysql_real_escape_string($filter['filter_value']) . "%' OR\n\t\t\t\t\t\t\t`source_name` LIKE '%" . mysql_real_escape_string($filter['filter_value']) . "%' OR\n\t\t\t\t\t\t\t`referer` LIKE '%" . mysql_real_escape_string($filter['filter_value']) . "%'\n\t\t\t\t\t\t)";
}
break;
//sprintf('%02d', $i)
//sprintf('%02d', $i)
default:
$filter_str .= " and " . mysql_real_escape_string($filter['filter_by']) . "='" . mysql_real_escape_string($filter['filter_value']) . "'";
break;
}
}
//$date2 = date('Y-m-d', strtotime($date) - (12 * 3600));
//tbl_clicks.date_add > STR_TO_DATE('" . $date2 . " 00:00:00', '%Y-%m-%d %H:%i:%s')
$sql = "select SQL_CALC_FOUND_ROWS *, date_format(CONVERT_TZ(tbl_clicks.date_add, '+00:00', '" . _str($timezone_shift) . "'), '%d.%m.%Y %H:%i') as dt, timediff(NOW(), tbl_clicks.date_add) as td from tbl_clicks \n\t\twhere 1 \n\t\t{$filter_str}\n\t\t" . ($date ? "and CONVERT_TZ(tbl_clicks.date_add, '+00:00', '" . _str($timezone_shift) . "') between STR_TO_DATE('" . $date . " 00:00:00', '%Y-%m-%d %H:%i:%s') and STR_TO_DATE('" . $date . " 23:59:59', '%Y-%m-%d %H:%i:%s')" : '') . "\n\t\torder by date_add desc limit {$offset}, {$limit}";
$result = db_query($sql) or die(mysql_error());
$arr_data = array();
$q = "SELECT FOUND_ROWS() as `cnt`";
$total = ap(mysql_fetch_assoc(db_query($q)), 'cnt');
while ($row = mysql_fetch_assoc($result)) {
$row['td'] = get_relative_mysql_time($row['td']);
$arr_data[] = $row;
}
//dmp($arr_data);
return array($total, $arr_data);
}
function get_visitors_flow_data($IN = '', $report_name = '', $limit = 20, $offset = 0)
{
global $allowed_report_in_params;
$date = $IN[$report_name]['date'];
$timezone_shift = get_current_timezone_shift();
$convert_tz_condition = "CONVERT_TZ(t1.date_add, '+00:00', '" . _str($timezone_shift) . "')";
if (in_array($timezone_shift, array('+00:00', '-00:00', '00:00'))) {
$convert_tz_condition = 't1.date_add';
}
if (substr($timezone_shift, 0, 1) == '+') {
$timezone_shift_inverted = str_replace('+', '-', $timezone_shift);
} else {
$timezone_shift_inverted = str_replace('-', '+', $timezone_shift);
}
$more = 0;
// Записей больше нет
$filter_str = '';
switch ($IN[$report_name]['filter_by']) {
case 'none':
break;
case 'hour':
$hour = sprintf('%02d', $IN[$report_name]['filter_value'][0]);
$filter_str .= " AND `source_name` = '" . _str($IN[$report_name]['filter_value'][1]) . "' AND date_add BETWEEN CONVERT_TZ('" . _str($IN[$report_name]['date']) . " " . _str($hour) . ":00:00', '+00:00', '" . _str($timezone_shift_inverted) . "') AND\n CONVERT_TZ('" . _str($IN[$report_name]['date']) . " " . _str($hour) . ":59:59', '+00:00', '" . _str($timezone_shift_inverted) . "')";
$date = '';
break;
case 'search':
if (is_subid($IN[$report_name]['filter_value'])) {
$filter_str .= " and `subid` LIKE '" . _str($IN[$report_name]['filter_value']) . "'";
$date = '';
} else {
$filter_str .= " and (\n `user_ip` LIKE '" . _str($IN[$report_name]['filter_value']) . "' OR\n `campaign_name` LIKE '%" . _str($IN[$report_name]['filter_value']) . "%' OR\n `source_name` LIKE '%" . _str($IN[$report_name]['filter_value']) . "%' OR\n `referer` LIKE '%" . _str($IN[$report_name]['filter_value']) . "%'\n )";
}
break;
default:
if ($IN[$report_name]['filter_by'] == 'source_name' && $IN[$report_name]['filter_value'] == '{empty}') {
$filter_str .= " and " . _str($IN[$report_name]['filter_by']) . "='source'";
} else {
$filter_str .= " and " . _str($IN[$report_name]['filter_by']) . "='" . _str($IN[$report_name]['filter_value']) . "'";
}
break;
}
if ($date != '') {
if (in_array($timezone_shift_inverted, array('+00:00', '-00:00', '00:00'))) {
$date_str = 'AND t1.date_add BETWEEN ' . "'" . _str($date) . " 00:00:00'" . " AND " . "'" . _str($date) . " 23:59:59'";
} else {
$date_str = 'AND t1.date_add BETWEEN ' . "CONVERT_TZ('" . _str($date) . " 00:00:00', '+00:00', '" . _str($timezone_shift_inverted) . "')" . " AND " . "CONVERT_TZ('" . _str($date) . " 23:59:59', '+00:00', '" . _str($timezone_shift_inverted) . "')";
}
} else {
$date_str = '';
}
$q = "SELECT *, " . $convert_tz_condition . " as click_date,\n DATE_FORMAT(" . $convert_tz_condition . ", '%d.%m.%Y %H:%i') as dt,\n timediff(NOW(), t1.date_add) as td\n FROM\n `tbl_clicks` t1\n WHERE\n 1\n {$filter_str}\n {$date_str}\n ORDER BY\n date_add DESC\n LIMIT\n {$offset}, " . ($limit + 1);
$rs = db_query($q);
$arr_data = array();
$i = 0;
while ($row = mysql_fetch_assoc($rs)) {
if ($i == $limit) {
$more = 1;
$offset += $limit;
break;
}
$cur = array();
// 1. Date
$cur['date'] = mysqldate2short($row['click_date']);
// 2. Relative date
$cur['date_relative'] = get_relative_mysql_time($row['td']);
// 3. Location
if ($row['country'] == '') {
$cur['country'] = '';
$cur['country_icon'] = _HTML_TEMPLATE_PATH . "/img/countries/" . 'question.png';
} else {
$cur['country'] = format_cell_value('country', $row['country']);
$cur['country_icon'] = _HTML_TEMPLATE_PATH . "/img/countries/" . strtolower($row['country']) . '.png';
}
$cur['location'] = implode(', ', array_filter(array($cur['country'], $row['state'], $row['city'])));
// 4. ISP
$cur['isp'] = $row['isp'];
// 5. IP
$cur['ip'] = $row['user_ip'];
// 6. Subaccount
$cur['subaccount'] = $row['subaccount'];
// 7. SubID
$cur['subid'] = $row['subid'];
// 8. OS
switch (strtolower($row['user_os'])) {
case 'android':
$cur['os_icon'] = 'android';
break;
case 'linux':
$cur['os_icon'] = 'linux';
break;
case 'ios':
$cur['os_icon'] = 'apple-ios';
break;
default:
$cur['os_icon'] = '';
break;
}
$cur['os'] = implode(' ', array_filter(array($row['user_os'], $row['user_os_version'])));
// 9. Device
$cur['device'] = implode(' ', array_filter(array($row['user_platform'], $row['user_platform_info'], $row['user_platform_info_extra'])));
// 10. Device type
if ($row['is_phone'] == 1) {
$cur['tablet_icon'] = 'phone';
} elseif ($row['is_tablet'] == 1) {
$cur['tablet_icon'] = 'tablet';
} else {
$cur['tablet_icon'] = '';
}
// 11. User-agent
$cur['user_agent'] = $row['user_agent'];
// 12. Browser
$cur['browser'] = implode(' ', array_filter(array($row['user_browser'], $row['user_browser_version'])));
// 13. Referer, converting Punycode domain to UTF-8
$referer_host = parse_url($row['referer'], PHP_URL_HOST);
$referer = str_replace($referer_host, idn_to_utf8($referer_host), $row['referer']);
$cur['full_referer'] = urldecode(str_replace(array('http://www.', 'www.'), '', $referer));
if ($row['source_name'] == 'yadirect' and !empty($row['click_param_value8'])) {
$cur_referrer = $row['click_param_value8'];
if (mb_strlen($cur_referrer, 'UTF-8') > 40) {
$wrapped_referrer = mb_substr($cur_referrer, 0, 38, 'UTF-8') . '…';
} else {
$wrapped_referrer = $cur_referrer;
}
$cur['referer'] = $wrapped_referrer;
} else {
if ($row['search_string'] != '') {
$cur_referrer = $row['search_string'];
if (mb_strlen($cur_referrer, 'UTF-8') > 100) {
$wrapped_referrer = mb_substr($cur_referrer, 0, 98, 'UTF-8') . '…';
} else {
$wrapped_referrer = $cur_referrer;
}
$cur['referer'] = $wrapped_referrer;
} else {
// Converting from Punycode to UTF-8
$referer_host = parse_url($row['referer'], PHP_URL_HOST);
$referer = str_replace($referer_host, idn_to_utf8($referer_host), $row['referer']);
$cur_referrer = str_replace(array('http://www.', 'www.'), '', $referer);
if (strpos($cur_referrer, 'http://') === 0) {
$cur_referrer = substr($cur_referrer, strlen('http://'));
}
if (mb_strlen($cur_referrer, 'UTF-8') > 100) {
$wrapped_referrer = mb_substr($cur_referrer, 0, 98, 'UTF-8') . '…';
} else {
$wrapped_referrer = $cur_referrer;
}
$cur['referer'] = $wrapped_referrer;
}
}
// 14. Keyword
$cur['keyword'] = $row['search_string'];
// 15. Link
// $cur['link']=get_rule_description($row['rule_id']);
// 16. Offer
if ($row['out_id'] > 0) {
$cur['offer'] = current(get_out_description($row['out_id']));
} else {
$cur['offer'] = 'Не определён';
}
// 17. Source
$cur['source'] = format_cell_value('source_name', $row['source_name']);
// 18. Campaign and ads
$cur['campaign'] = $row['campaign_name'];
$cur['ads'] = $row['ads_name'];
// 19. Click price click_price
$cur['click_price'] = $row['click_price'];
// 20. Profit
$cur['profit'] = $row['conversion_price_main'];
// 21. Action type is_lead is_sale
// 22. Parent click is_parent
// 23. LP click is_connected
// 24. Parent click ID parent_id
// 25. Unique click is_unique
// 26. Link params campaign_param1, campaign_param2, campaign_param3, campaign_param4, campaign_param5
$campaign_params = params_list($row, 'campaign_param');
if (!empty($campaign_params)) {
$cur['link_params'] = join('; ', $campaign_params);
}
// 27. Visit params click_param_name1, click_param_value1, …, click_param_name15, click_param_value15
$click_params = params_list($row, 'click_param_value', $row['source_name']);
if (!empty($click_params)) {
$cur['click_params'] = join('; ', $click_params);
}
// Save current row data
$arr_data['flow_rows'][$i++] = $cur;
}
// end $row iteration
// Fill report params, hidden
foreach ($allowed_report_in_params as $report => $t) {
foreach ($allowed_report_in_params[$report] as $key => $value) {
if (isset($IN[$report][$key]) && $IN[$report][$key] != $value) {
if (is_array($IN[$report][$key])) {
foreach ($IN[$report][$key] as $cur) {
$arr_data['report_params'][] = array('name' => $key . '[]', 'value' => $cur);
}
} else {
$arr_data['report_params'][] = array('name' => $key, 'value' => $IN[$report][$key]);
}
}
}
}
return array($more, $arr_data, $offset);
}
