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); }