function get_clicks_rows($params, $start = 0, $start_s = 0, $limit = 0, $campaign_params, $click_params)
{
$more = 0;
// Записей больше нет, этот запрос крайний
$spot_ids = array();
// Споты, в которых будем искать данные
// Вставка для Turbo режима
if (_CLICKS_SPOT_SIZE > 0) {
$spot_ids = clicks_spot_get($params['from'], $params['to']);
if (empty($spot_ids)) {
return array(0, array(), array(), array());
}
$clicks_table = "tbl_clicks_s" . $spot_ids[$start_s];
} else {
$clicks_table = "tbl_clicks";
}
// Применяем фильтры
if (!empty($params['filter'][0]) and is_array($params['filter'][0])) {
$tmp = array();
foreach ($params['filter'][0] as $k => $v) {
if ($k == 'referer') {
if ($v == '{empty}') {
$tmp[] = "`" . $k . "` = ''";
} else {
$tmp[] = "`" . $k . "` LIKE '%" . mysql_real_escape_string($v) . "%'";
}
} elseif ($k == 'ads_name') {
list($campaign_name, $ads_name) = explode('-', $v);
$tmp[] = "`campaign_name` = '" . mysql_real_escape_string($campaign_name) . "'";
$tmp[] = "`ads_name` = '" . mysql_real_escape_string($ads_name) . "'";
} elseif ($k == 'source_name' and empty($v)) {
$tmp[] = "(`source_name` = '' or `source_name` = 'source' or `source_name` = 'SOURCE' or `source_name` = '{empty}')";
} else {
if ($v == '{empty}') {
$v = '';
}
$tmp[] = "`" . $k . "` = '" . mysql_real_escape_string($v) . "'";
}
}
if (!empty($tmp)) {
$where = ' and (' . join(' and ', $tmp) . ')';
} else {
$where = '';
}
} else {
$where = '';
}
// Дополнительные поля для режима популярных параметров
if ($params['mode'] == 'popular' or 1) {
$select = ', out_id, source_name, ads_name, referer, user_os, user_ip, user_platform, user_browser, country, state, city, isp, campaign_param1, campaign_param2, campaign_param3, campaign_param4, campaign_param5 ';
for ($i = 1; $i <= 15; $i++) {
$select .= ', click_param_value' . $i . ' ';
}
} else {
$select = '';
}
if (strlen($params['from']) == 10) {
$params['from'] .= ' 00:00:00';
}
if (strlen($params['to']) == 10) {
$params['to'] .= ' 23:59:59';
}
if (empty($params['cache'])) {
// Кэш мы считаем без смещения по часовому поясу
$timezone_shift = get_current_timezone_shift();
// Смещение часового пояса
$where .= " and CONVERT_TZ(t1.`date_add`, '+00:00', '" . _str($timezone_shift) . "') BETWEEN STR_TO_DATE('" . $params['from'] . "', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('" . $params['to'] . "', '%Y-%m-%d %H:%i:%s')";
$time_add = "UNIX_TIMESTAMP(CONVERT_TZ(t1.`date_add`, '+00:00', '" . _str($timezone_shift) . "')) as `time_add`,";
} else {
$where .= " and t1.`date_add` BETWEEN STR_TO_DATE('" . $params['from'] . "', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('" . $params['to'] . "', '%Y-%m-%d %H:%i:%s')";
$time_add = "UNIX_TIMESTAMP(t1.`date_add`) as `time_add`,";
}
// Выбираем все переходы за период
$q = "SELECT " . (empty($params['group_by']) ? '' : " " . mysql_real_escape_string($params['group_by']) . " as `name`, ") . (($params['group_by'] == $params['subgroup_by'] or empty($params['subgroup_by'])) ? '' : " " . mysql_real_escape_string($params['subgroup_by']) . ", ") . "\t1 as `cnt`,\n t1.id,\n t1.source_name,\n " . $time_add . "\n t1.rule_id,\n t1.out_id,\n t1.parent_id,\n t1.campaign_name,\n t1.click_price,\n t1.is_unique,\n t1.conversion_price_main,\n t1.is_sale,\n t1.is_lead,\n t1.is_parent,\n t1.is_connected " . $select . "\n FROM `" . $clicks_table . "` t1\n WHERE 1 " . $where . (empty($params['where']) ? '' : " and " . $params['where']) . "\n ORDER BY t1.id ASC\n LIMIT {$start}, {$limit}";
if ($rs = db_query($q) and mysql_num_rows($rs) > 0) {
while ($r = mysql_fetch_assoc($rs)) {
$rows[$r['id']] = $r;
// Определяем наличие пользовательских параметров
for ($i = 1; $i <= 5; $i++) {
if ($r['campaign_param' . $i] != '') {
$campaign_params[$i] = 1;
}
}
for ($i = 1; $i <= 15; $i++) {
if ($r['click_param_value' . $i] != '') {
$click_params[$i] = 1;
}
}
}
// Мы получили максимальное число строчек
if (count($rows) == $limit) {
$start += $limit;
$more = 1;
} elseif ($start_s + 1 < count($spot_ids)) {
$start = 0;
$start_s++;
$more = 1;
}
// Нет тут больше строчек
} else {
// Если спот не последний - смещаем указатель
if ($start_s + 1 < count($spot_ids)) {
$start = 0;
$start_s++;
$more = 1;
}
}
return array($more, $start, $start_s, $rows, $campaign_params, $click_params);
}
/**
* Создание нового спота для кликов
*/
function clicks_spot_add()
{
$current_spot_id = clicks_spot_get();
$q = "select max(`date_add`) as `max_time` from `tbl_clicks_s" . $current_spot_id . "`";
$rs = db_query($q);
$r = mysql_fetch_assoc($rs);
$max_spot_time = $r['max_time'];
// Завершаем текущий спот
$q = "update tbl_clicks_map set `time_end` = '" . $max_spot_time . "', `current` = '0' where `id` = '" . $current_spot_id . "'";
// Создание нового спота
$ins = array('time_begin' => '2000-01-01 00:00:00', 'time_end' => '2020-01-01 00:00:00', 'current' => '1');
$q = insertsql($ins, 'tbl_clicks_map');
db_query($q);
$new_spot_id = mysql_insert_id();
$q = "CREATE TABLE IF NOT EXISTS `tbl_clicks_s" . $new_spot_id . "` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `date_add` datetime NOT NULL,\n `user_ip` varchar(255) NOT NULL,\n `user_agent` text CHARACTER SET utf8 NOT NULL,\n `user_os` varchar(255) CHARACTER SET utf8 NOT NULL,\n `user_os_version` varchar(255) CHARACTER SET utf8 NOT NULL,\n `user_platform` varchar(255) CHARACTER SET utf8 NOT NULL,\n `user_platform_info` varchar(255) CHARACTER SET utf8 NOT NULL,\n `user_platform_info_extra` varchar(255) CHARACTER SET utf8 NOT NULL,\n `user_browser` varchar(255) CHARACTER SET utf8 NOT NULL,\n `user_browser_version` varchar(255) CHARACTER SET utf8 NOT NULL,\n `is_mobile_device` tinyint(1) NOT NULL,\n `is_phone` tinyint(1) NOT NULL,\n `is_tablet` tinyint(1) NOT NULL,\n `country` varchar(255) NOT NULL,\n `state` varchar(255) CHARACTER SET utf8 NOT NULL,\n `city` varchar(255) CHARACTER SET utf8 NOT NULL,\n `region` varchar(255) CHARACTER SET utf8 NOT NULL,\n `isp` varchar(255) CHARACTER SET utf8 NOT NULL,\n `rule_id` int(11) NOT NULL,\n `out_id` int(11) NOT NULL,\n `subid` varchar(255) CHARACTER SET utf8 NOT NULL,\n `subaccount` varchar(255) CHARACTER SET utf8 NOT NULL,\n `source_name` varchar(255) CHARACTER SET utf8 NOT NULL,\n `campaign_name` varchar(255) CHARACTER SET utf8 NOT NULL,\n `ads_name` varchar(255) CHARACTER SET utf8 NOT NULL,\n `referer` text CHARACTER SET utf8 NOT NULL,\n `search_string` text CHARACTER SET utf8 NOT NULL,\n `click_price` decimal(10,4) NOT NULL,\n `conversion_price_main` decimal(10,4) NOT NULL,\n `is_lead` tinyint(1) NOT NULL,\n `is_sale` tinyint(1) NOT NULL,\n `is_parent` tinyint(1) NOT NULL,\n `is_connected` tinyint(1) NOT NULL,\n `parent_id` int(11) NOT NULL,\n `is_unique` tinyint(1) NOT NULL DEFAULT '0',\n `campaign_param1` varchar(255) CHARACTER SET utf8 NOT NULL,\n `campaign_param2` varchar(255) CHARACTER SET utf8 NOT NULL,\n `campaign_param3` varchar(255) CHARACTER SET utf8 NOT NULL,\n `campaign_param4` varchar(255) CHARACTER SET utf8 NOT NULL,\n `campaign_param5` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_name1` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value1` text CHARACTER SET utf8 NOT NULL,\n `click_param_name2` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value2` text CHARACTER SET utf8 NOT NULL,\n `click_param_name3` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value3` text CHARACTER SET utf8 NOT NULL,\n `click_param_name4` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value4` text CHARACTER SET utf8 NOT NULL,\n `click_param_name5` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value5` text CHARACTER SET utf8 NOT NULL,\n `click_param_name6` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value6` text CHARACTER SET utf8 NOT NULL,\n `click_param_name7` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value7` text CHARACTER SET utf8 NOT NULL,\n `click_param_name8` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value8` text CHARACTER SET utf8 NOT NULL,\n `click_param_name9` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value9` text CHARACTER SET utf8 NOT NULL,\n `click_param_name10` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value10` text CHARACTER SET utf8 NOT NULL,\n `click_param_name11` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value11` text CHARACTER SET utf8 NOT NULL,\n `click_param_name12` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value12` text CHARACTER SET utf8 NOT NULL,\n `click_param_name13` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value13` text CHARACTER SET utf8 NOT NULL,\n `click_param_name14` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value14` text CHARACTER SET utf8 NOT NULL,\n `click_param_name15` varchar(255) CHARACTER SET utf8 NOT NULL,\n `click_param_value15` text CHARACTER SET utf8 NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `subid` (`subid`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
db_query($q);
}
