function get_sales_flow_data($request_parameters, $report_name, $limit = 20, $offset = 0)
{
// Default currency for this account: RUB (16)
$main_currency_id = 16;
// Fill currency list
global $arr_currencies_list;
if (!(is_array($arr_currencies_list) && count($arr_currencies_list) > 0)) {
$arr_currencies_list = get_active_currencies();
}
$allowed_report_in_params = array('report_type' => 'actions_count', 'range_type' => 'daily', 'main_column' => 'source_name', 'filter_actions' => 'actions', 'filter_by' => '', 'filter_value' => '', 'currency_id' => '16', 'timezone_offset' => get_current_timezone_shift(), 'date_start' => get_current_day('-7 days'), 'date_end' => get_current_day(), 'report_period' => 'lastweek');
// Remove empty values and get only allowed keys
$IN = array_replace($allowed_report_in_params, array_intersect_key(array_filter($request_parameters), $allowed_report_in_params));
// Add default report params
$IN['report_params'] = isset($request_parameters['report_params']) ? $request_parameters['report_params'] : null;
// Fill report range
report_period_to_dates($IN);
$sql_select = array();
$sql_join = array();
$sql_where = array();
$sql_order = array();
$sql_limit = 'LIMIT ' . _str($limit + 1) . ' OFFSET ' . _str($offset);
// Apply timezone offset
$date_add_tz = "CONVERT_TZ(tbl_conversions.date_add, '+00:00', '" . _str($IN['timezone_offset']) . "') as click_date";
if (in_array($IN['timezone_offset'], array('+00:00', '-00:00', '00:00'))) {
$date_add_tz = 'tbl_conversions.date_add as click_date';
}
$sql_select[] = $date_add_tz;
if (in_array($IN['timezone_offset'], array('+00:00', '-00:00', '00:00'))) {
// Same timezones in DB and report
$sql_date_start = "'" . _str($IN['date_start']) . " 00:00:00'";
$sql_date_end = "'" . _str($IN['date_end']) . " 23:59:59'";
} else {
$timezone_offset_inverted = timezone_shift_invert($IN['timezone_offset']);
$sql_date_start = "CONVERT_TZ('" . _str($IN['date_start']) . " 00:00:00', '+00:00', '" . _str($timezone_offset_inverted) . "')";
$sql_date_end = "CONVERT_TZ('" . _str($IN['date_end']) . " 23:59:59', '+00:00', '" . _str($timezone_offset_inverted) . "')";
}
if ($IN['filter_by'] == 'subid' && $IN['filter_value'] != '') {
// Looking for sale, no need to limit search by date range
$sql_where[] = "tbl_conversions.subid='" . _str($IN['filter_value']) . "'";
} else {
$sql_where[] = "tbl_conversions.date_add BETWEEN {$sql_date_start} AND {$sql_date_end}";
}
$sql_select[] = 'tbl_conversions.*';
$sql_order[] = 'date_add desc';
// 1. Get sales list
$sql = 'SELECT ' . implode(', ', $sql_select) . '
FROM
tbl_conversions
' . implode(' ', $sql_join) . '
WHERE
' . implode(' AND ', $sql_where) . '
ORDER BY ' . implode(', ', $sql_order) . "\n {$sql_limit}";
$result = mysql_query($sql);
$arr_sales = array();
while ($row = mysql_fetch_assoc($result)) {
$arr_sales["'" . _str($row['subid']) . "'"] = $row;
}
// 2. Get clicks for sales
$arr_sales_clicks = array();
$arr_sales_subids = array_keys($arr_sales);
$subids = implode(',', $arr_sales_subids);
$sql = "select\n tbl_clicks.*,\n tbl_offers.offer_name\n FROM\n tbl_clicks\n left join\n tbl_offers on tbl_clicks.out_id=tbl_offers.id\n where\n subid in ({$subids})";
$result = mysql_query($sql);
$arr_parent_ids = array();
while ($row = mysql_fetch_assoc($result)) {
if ($row['parent_id'] > 0) {
$arr_parent_ids[] = "'" . _str($row['parent_id']) . "'";
}
$arr_sales_clicks["'" . _str($row['subid']) . "'"] = $row;
}
$parent_ids = implode(',', $arr_parent_ids);
$sql = "select tbl_clicks.* from tbl_clicks where id in ({$parent_ids})";
$result = mysql_query($sql);
$arr_parent_clicks = array();
while ($row = mysql_fetch_assoc($result)) {
$arr_parent_clicks[$row['id']] = $row;
}
$arr_report_data = array();
function net_loader($class)
{
include_once _TRACK_LIB_PATH . '/postback/' . $class . '.php';
}
spl_autoload_register('net_loader');
include _TRACK_LIB_PATH . "/class/common.php";
include _TRACK_LIB_PATH . "/class/custom.php";
$arr_default_network_params = array('date_add', 'profit', 'status', 'subid', 'txt_status', 'type');
$i = 0;
$more = 0;
foreach ($arr_sales as $subid => $row) {
if ($i == $limit) {
$more = 1;
break;
}
$arr_report_data[$i]['short_date'] = mysqldate2short($row['click_date']);
$arr_report_data[$i]['offer_name'] = $arr_sales_clicks[$subid]['offer_name'];
// Network name
$class_name = $row['network'];
if (class_exists($class_name, true)) {
$t = new $class_name();
if (method_exists($t, 'get_params_info')) {
$arr_class_params = $t->get_params_info();
$arr_report_data[$i]['network_params'] = array();
foreach ($row as $key => $val) {
if (isset($arr_class_params[$key]) && !in_array($key, $arr_default_network_params)) {
if (trim($val) != '') {
$arr_report_data[$i]['network_params']['values'][] = array('caption' => $arr_class_params[$key]['caption'], 'value' => $val);
}
}
}
}
}
$arr_report_data[$i]['network_name'] = $row['network'];
$arr_report_data[$i]['source_name'] = $arr_sales_clicks[$subid]['source_name'] == '' ? '' : format_cell_value('source_name', $arr_sales_clicks[$subid]['source_name']);
// Campaign name
$arr_report_data[$i]['campaign_name'] = implode(' — ', array_filter(array($arr_sales_clicks[$subid]['campaign_name'], $arr_sales_clicks[$subid]['ads_name'])));
$arr_report_data[$i]['placement'] = format_cell_value('referer_domain', $arr_sales_clicks[$subid]['referer_domain']);
$arr_report_data[$i]['subid'] = $row['subid'];
if ($row['currency_id'] == $IN['currency_id']) {
$arr_report_data[$i]['profit'] = format_cell_value('profit', $row['profit_currency'], $IN);
} else {
$arr_report_data[$i]['profit'] = format_cell_value('profit', convert_currency($row['profit'], $main_currency_id, $IN['currency_id'], date('Y-m-d')), $IN);
}
// $arr_report_data[$i]['status']=$row['txt_status'];
switch ($row['txt_status']) {
case 'approved':
$arr_report_data[$i]['status-icon'] = "fa fa-check";
$arr_report_data[$i]['status-title'] = "Подтвержден";
break;
case 'waiting':
$arr_report_data[$i]['status-icon'] = "fa fa-hourglass-o";
$arr_report_data[$i]['status-title'] = "В обработке";
break;
case 'rejected':
$arr_report_data[$i]['status-icon'] = "fa fa-close";
$arr_report_data[$i]['status-title'] = "Отклонен";
break;
default:
$arr_report_data[$i]['status'] = mb_strtolower($row['txt_status'], 'UTF-8');
break;
}
if (isset($arr_parent_clicks[$arr_sales_clicks[$subid]['parent_id']])) {
// Fill sale parameters from parent click
$arr_report_data[$i]['source_name'] = $arr_parent_clicks[$arr_sales_clicks[$subid]['parent_id']]['source_name'];
}
// FILL CLICK INFO
if (isset($arr_parent_clicks[$subid])) {
$click_info = $arr_parent_clicks[$subid];
} else {
$click_info = $arr_sales_clicks[$subid];
}
// Conversion ID
$arr_report_data[$i]['conversion_id'] = $row['id'];
// Date
$arr_report_data[$i]['date'] = mysqldate2string($row['click_date']);
// Location
$arr_report_data[$i]['country'] = format_cell_value('country', $click_info['country']);
$arr_report_data[$i]['location'] = implode(', ', array_filter(array($arr_report_data[$i]['country'], $click_info['state'], $click_info['city'])));
// ISP
$arr_report_data[$i]['isp'] = $click_info['isp'];
// IP
$arr_report_data[$i]['ip'] = $click_info['user_ip'];
// Subaccount
$arr_report_data[$i]['subaccount'] = $click_info['subaccount'];
// SubID
$arr_report_data[$i]['subid'] = $click_info['subid'];
// OS
$arr_report_data[$i]['os'] = implode(' ', array_filter(array($click_info['user_os'], $click_info['user_os_version'])));
// Device
$arr_report_data[$i]['device'] = implode(' ', array_filter(array($click_info['user_platform'], $click_info['user_platform_info'], $click_info['user_platform_info_extra'])));
// Device type
if ($click_info['is_phone'] == 1) {
$arr_report_data[$i]['tablet_icon'] = 'phone';
} elseif ($click_info['is_tablet'] == 1) {
$arr_report_data[$i]['tablet_icon'] = 'tablet';
} else {
$arr_report_data[$i]['tablet_icon'] = '';
}
// User-agent
$arr_report_data[$i]['user_agent'] = $click_info['user_agent'];
// Browser
$arr_report_data[$i]['browser'] = implode(' ', array_filter(array($click_info['user_browser'], $click_info['user_browser_version'])));
// Referer
$referer_host = parse_url($click_info['referer'], PHP_URL_HOST);
$referer = str_replace($referer_host, idn_to_utf8($referer_host), $click_info['referer']);
$arr_report_data[$i]['full_referer'] = urldecode(str_replace(array('http://www.', 'www.'), '', $referer));
// Keyword
$arr_report_data[$i]['keyword'] = $click_info['search_string'];
// Link params campaign_param1, campaign_param2, campaign_param3, campaign_param4, campaign_param5
$campaign_params = params_list($click_info, 'campaign_param');
if (!empty($campaign_params)) {
$arr_report_data[$i]['link_params'] = join('; ', $campaign_params);
}
// Visit params click_param_name1, click_param_value1, …, click_param_name15, click_param_value15
$click_params = params_list($click_info, 'click_param_value', $click_info['source_name']);
if (!empty($click_params)) {
$arr_report_data[$i]['click_params'] = join('; ', $click_params);
}
$i++;
}
return array($more, $arr_report_data, $offset);
}
// Set default value
$IN[$report][$cur] = $allowed_report_in_params[$report][$cur];
}
}
}
// Get hourly report data
$arr_report_data = prepare_report('main-report', $IN['hourly_report'] + array('report_type' => 'stats_flow', 'range_type' => 'hourly', 'date_start' => $IN['flow_report']['date'], 'date_end' => $IN['flow_report']['date']));
// Don't use parameters from hourly report
unset($arr_report_data['report_params']);
// Get clicks flow data
list($more, $arr_flow_data, $s, $s1) = get_visitors_flow_data($IN, 'flow_report');
// Fill variables for stats-flow report
$arr_flow_data['show_more'] = $more;
$date_prev = date('Y-m-d', strtotime('-1 days', strtotime($IN['flow_report']['date'])));
$date_next = date('Y-m-d', strtotime('+1 days', strtotime($IN['flow_report']['date'])));
$arr_template_data = array('hide-table-footer' => true, 'date_current' => $IN['flow_report']['date'], 'date_prev_caption' => mysqldate2string($date_prev), 'date_current_caption' => mysqldate2string($IN['flow_report']['date']), 'date_next_caption' => mysqldate2string($date_next), 'hide_next_day' => $IN['flow_report']['date'] == get_current_day(), 'date_prev' => $date_prev, 'date_next' => $date_next, 'csrf' => CSRF_KEY);
// Render template
echo $mTemplate->render('stats-flow-page', $arr_report_data + $arr_template_data + $arr_flow_data);
?>
<script type="text/javascript">
function refresh_report(param_name, param_value)
{
switch (param_name)
{
default:
var param_found=false;
$('#report_params input[type="hidden"]').each(function() {
if ($(this).attr('name')==param_name)
{
param_found=true;
echo $n['id'];
?>
, this)">
<i class="cpa cpa-check-o"></i>
<span>Отметить как прочитанное</span>
</a>
<?php
}
} else {
$next_row_style = 'class="collapse in" style="height: auto;"';
}
?>
</td>
<td class="text-center accordion-toggle" data-toggle="collapse-next">
<span><?php
echo mysqldate2string($n['date']);
?>
</span>
</td>
<td class="text-center accordion-toggle" data-toggle="collapse-next">
<a class="a-default">
<i class="cpa cpa-angle-down"></i>
</a>
</td>
</tr>
<tr>
<td class="no-padding" colspan="4">
<div <?php
echo $next_row_style;
?>
>
$arr_hourly = array();
foreach ($arr_report_data['data'] as $row_name => $row_data) {
foreach ($row_data as $cur_hour => $data) {
$arr_hourly[$row_name][$cur_hour] = get_clicks_report_element2($data, true, false, $group_actions);
}
}
echo "<div class='row'>";
echo "<div class='col-md-12'>";
echo "<p align=center>";
if ($date != get_current_day()) {
echo "<a style='float:right;' href='?date={$next_date}&report_type={$main_type}'>" . mysqldate2string($next_date) . " →</a>";
} else {
echo "<a style='float:right; visibility:hidden;' href='?date={$next_date}&report_type={$main_type}'>" . mysqldate2string($next_date) . " →</a>";
}
echo "<b>" . mysqldate2string($date) . "</b>";
echo "<a style='float:left;' href='?date={$prev_date}&report_type={$main_type}'>← " . mysqldate2string($prev_date) . "</a></p>";
echo "<table class='table table-striped table-bordered table-condensed'>";
echo "<tbody>";
echo "<tr>";
echo "<td>";
echo "<div class='btn-group'>";
switch ($main_type) {
case 'out_id':
echo "<button class='btn btn-link dropdown-toggle' data-toggle='dropdown' style='padding:0; color:black; font-weight: bold;'>Оффер <span class='caret'></span></button>\n\t\t\t\t\t\t\t <ul class='dropdown-menu'>\n\t\t\t\t\t\t\t <li><a href='?date={$date}&report_type=source_name'>Источник</a></li>\n\t\t\t\t\t\t\t </ul>";
break;
default:
echo "<button class='btn btn-link dropdown-toggle' data-toggle='dropdown' style='padding:0; color:black; font-weight: bold;'>Источник <span class='caret'></span></button>\n\t\t\t\t\t\t\t <ul class='dropdown-menu'>\n\t\t\t\t\t\t\t <li><a href='?date={$date}&report_type=out_id'>Оффер</a></li>\n\t\t\t\t\t\t\t </ul>";
break;
}
echo "</div>";
echo "</td>";
