/**
  * Adjust from und until dates from UTZ to STZ and take into account the
  * for option which will adjust the from date to that of the current
  * day, the start of the week or the month, leaving the until date
  * set to null.
  * 
  * @param string $for "day", "week" or "month"
  * @param string $from date/datetime
  * @param string $until date/datetime
  */
 private static function for_from_until($for, &$from, &$until)
 {
     include_once AFFILIATES_CORE_LIB . '/class-affiliates-date-helper.php';
     if ($for === null) {
         if ($from !== null) {
             $from = date('Y-m-d H:i:s', strtotime(DateHelper::u2s($from)));
         }
         if ($until !== null) {
             $until = date('Y-m-d H:i:s', strtotime(DateHelper::u2s($until)));
         }
     } else {
         $user_now = strtotime(DateHelper::s2u(date('Y-m-d H:i:s', time())));
         $user_now_datetime = date('Y-m-d H:i:s', $user_now);
         $user_daystart_datetime = date('Y-m-d', $user_now) . ' 00:00:00';
         $server_now_datetime = DateHelper::u2s($user_now_datetime);
         $server_user_daystart_datetime = DateHelper::u2s($user_daystart_datetime);
         $until = null;
         switch (strtolower($for)) {
             case 'day':
                 $from = date('Y-m-d H:i:s', strtotime($server_user_daystart_datetime));
                 break;
             case 'week':
                 $fdow = intval(get_option('start_of_week'));
                 $dow = intval(date('w', strtotime($server_user_daystart_datetime)));
                 $d = $dow - $fdow;
                 $from = date('Y-m-d H:i:s', mktime(0, 0, 0, date('m', strtotime($server_user_daystart_datetime)), date('d', strtotime($server_user_daystart_datetime)) - $d, date('Y', strtotime($server_user_daystart_datetime))));
                 break;
             case 'month':
                 $from = date('Y-m', strtotime($server_user_daystart_datetime)) . '-01 00:00:00';
                 break;
             default:
                 $from = null;
         }
     }
 }
function affiliates_admin_hits()
{
    global $wpdb, $affiliates_options;
    $output = '';
    if (!current_user_can(AFFILIATES_ACCESS_AFFILIATES)) {
        wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
    }
    if (isset($_POST['from_date']) || isset($_POST['thru_date']) || isset($_POST['clear_filters']) || isset($_POST['affiliate_id']) || isset($_POST['expanded']) || isset($_POST['expanded_hits']) || isset($_POST['expanded_referrals']) || isset($_POST['show_inoperative'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_FILTER_NONCE], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    // filters
    $from_date = $affiliates_options->get_option('hits_from_date', null);
    $thru_date = $affiliates_options->get_option('hits_thru_date', null);
    $affiliate_id = $affiliates_options->get_option('hits_affiliate_id', null);
    $expanded = $affiliates_options->get_option('hits_expanded', null);
    // @todo input ist not shown, eventually remove unless ...
    $expanded_referrals = $affiliates_options->get_option('hits_expanded_referrals', null);
    $expanded_hits = $affiliates_options->get_option('hits_expanded_hits', null);
    $show_inoperative = $affiliates_options->get_option('hits_show_inoperative', null);
    if (isset($_POST['clear_filters'])) {
        $affiliates_options->delete_option('hits_from_date');
        $affiliates_options->delete_option('hits_thru_date');
        $affiliates_options->delete_option('hits_affiliate_id');
        $affiliates_options->delete_option('hits_expanded');
        $affiliates_options->delete_option('hits_expanded_referrals');
        $affiliates_options->delete_option('hits_expanded_hits');
        $affiliates_options->delete_option('hits_show_inoperative');
        $from_date = null;
        $thru_date = null;
        $affiliate_id = null;
        $expanded = null;
        $expanded_hits = null;
        $expanded_referrals = null;
        $show_inoperative = null;
    } else {
        if (isset($_POST['submitted'])) {
            // filter by date(s)
            if (!empty($_POST['from_date'])) {
                $from_date = date('Y-m-d', strtotime($_POST['from_date']));
                $affiliates_options->update_option('hits_from_date', $from_date);
            } else {
                $from_date = null;
                $affiliates_options->delete_option('hits_from_date');
            }
            if (!empty($_POST['thru_date'])) {
                $thru_date = date('Y-m-d', strtotime($_POST['thru_date']));
                $affiliates_options->update_option('hits_thru_date', $thru_date);
            } else {
                $thru_date = null;
                $affiliates_options->delete_option('hits_thru_date');
            }
            if ($from_date && $thru_date) {
                if (strtotime($from_date) > strtotime($thru_date)) {
                    $thru_date = null;
                    $affiliates_options->delete_option('hits_thru_date');
                }
            }
            // filter by affiliate id
            if (!empty($_POST['affiliate_id'])) {
                $affiliate_id = affiliates_check_affiliate_id($_POST['affiliate_id']);
                if ($affiliate_id) {
                    $affiliates_options->update_option('hits_affiliate_id', $affiliate_id);
                }
            } else {
                if (isset($_POST['affiliate_id'])) {
                    // empty && isset => '' => all
                    $affiliate_id = null;
                    $affiliates_options->delete_option('hits_affiliate_id');
                }
            }
            // expanded details?
            if (!empty($_POST['expanded'])) {
                $expanded = true;
                $affiliates_options->update_option('hits_expanded', true);
            } else {
                $expanded = false;
                $affiliates_options->delete_option('hits_expanded');
            }
            if (!empty($_POST['expanded_hits'])) {
                $expanded_hits = true;
                $affiliates_options->update_option('hits_expanded_hits', true);
            } else {
                $expanded_hits = false;
                $affiliates_options->delete_option('hits_expanded_hits');
            }
            if (!empty($_POST['expanded_referrals'])) {
                $expanded_referrals = true;
                $affiliates_options->update_option('hits_expanded_referrals', true);
            } else {
                $expanded_referrals = false;
                $affiliates_options->delete_option('hits_expanded_referrals');
            }
            if (!empty($_POST['show_inoperative'])) {
                $show_inoperative = true;
                $affiliates_options->update_option('hits_show_inoperative', true);
            } else {
                $show_inoperative = false;
                $affiliates_options->delete_option('hits_show_inoperative');
            }
        }
    }
    if (isset($_POST['row_count'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_NONCE_1], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    if (isset($_POST['paged'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_NONCE_2], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    $current_url = (is_ssl() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
    $current_url = remove_query_arg('paged', $current_url);
    $affiliates_table = _affiliates_get_tablename('affiliates');
    $referrals_table = _affiliates_get_tablename('referrals');
    $hits_table = _affiliates_get_tablename('hits');
    $output .= '<div>' . '<h1>' . __('Visits & Referrals', AFFILIATES_PLUGIN_DOMAIN) . '</h1>' . '</div>';
    $row_count = isset($_POST['row_count']) ? intval($_POST['row_count']) : 0;
    if ($row_count <= 0) {
        $row_count = $affiliates_options->get_option('affiliates_hits_per_page', AFFILIATES_HITS_PER_PAGE);
    } else {
        $affiliates_options->update_option('affiliates_hits_per_page', $row_count);
    }
    $offset = isset($_GET['offset']) ? intval($_GET['offset']) : 0;
    if ($offset < 0) {
        $offset = 0;
    }
    $paged = isset($_REQUEST['paged']) ? intval($_REQUEST['paged']) : 0;
    if ($paged < 0) {
        $paged = 0;
    }
    $orderby = isset($_GET['orderby']) ? $_GET['orderby'] : null;
    switch ($orderby) {
        case 'date':
        case 'visits':
        case 'hits':
        case 'referrals':
        case 'ratio':
            break;
        case 'affiliate_id':
            $orderby = 'name';
        default:
            $orderby = 'date';
    }
    $order = isset($_GET['order']) ? $_GET['order'] : null;
    switch ($order) {
        case 'asc':
        case 'ASC':
            $switch_order = 'DESC';
            break;
        case 'desc':
        case 'DESC':
            $switch_order = 'ASC';
            break;
        default:
            $order = 'DESC';
            $switch_order = 'ASC';
    }
    $filters = " WHERE 1=%d ";
    $filter_params = array(1);
    // We now have the desired dates from the user's point of view, i.e. in her timezone.
    // If supported, adjust the dates for the site's timezone:
    if ($from_date) {
        $from_datetime = DateHelper::u2s($from_date);
    }
    if ($thru_date) {
        $thru_datetime = DateHelper::u2s($thru_date, 24 * 3600);
    }
    if ($from_date && $thru_date) {
        $filters .= " AND datetime >= %s AND datetime < %s ";
        $filter_params[] = $from_datetime;
        $filter_params[] = $thru_datetime;
    } else {
        if ($from_date) {
            $filters .= " AND datetime >= %s ";
            $filter_params[] = $from_datetime;
        } else {
            if ($thru_date) {
                $filters .= " AND datetime < %s ";
                $filter_params[] = $thru_datetime;
            }
        }
    }
    if ($affiliate_id) {
        $filters .= " AND affiliate_id = %d ";
        $filter_params[] = $affiliate_id;
    }
    // how many are there ?
    $count_query = $wpdb->prepare("SELECT date FROM {$hits_table} h\n\t\t{$filters}\n\t\tGROUP BY date\n\t\t", $filter_params);
    $wpdb->query($count_query);
    $count = $wpdb->num_rows;
    if ($count > $row_count) {
        $paginate = true;
    } else {
        $paginate = false;
    }
    $pages = ceil($count / $row_count);
    if ($paged > $pages) {
        $paged = $pages;
    }
    if ($paged != 0) {
        $offset = ($paged - 1) * $row_count;
    }
    // Get the summarized results, these are grouped by date.
    // If there were any referral on a date without a hit, it would not be included:
    // Example conditions:
    // - 2011-02-01 23:59:59 hit recorded
    // - 2011-02-02 00:10:05 referral recorded
    // - no hits recorded on 2011-02-02
    // =>
    // - the referral will not show up
    // So, for ratio calculation, only the date with actual visits and referrals will show up.
    // Referrals on dates without visits would give an infinite ratio (x referrals / 0 visits).
    // We have a separate page which shows all referrals.
    $query = $wpdb->prepare("\n\t\tSELECT\n\t\t\t*,\n\t\t\tcount(distinct ip) visits,\n\t\t\tsum(count) hits,\n\t\t\t(select count(*) from {$referrals_table} where date(datetime) = h.date " . ($affiliate_id ? " AND affiliate_id = " . intval($affiliate_id) . " " : "") . ") referrals,\n\t\t\t((select count(*) from {$referrals_table} where date(datetime) = h.date " . ($affiliate_id ? " AND affiliate_id = " . intval($affiliate_id) . " " : "") . ")/count(distinct ip)) ratio\n\t\tFROM {$hits_table} h\n\t\t{$filters}\n\t\tGROUP BY date\n\t\tORDER BY {$orderby} {$order}\n\t\tLIMIT {$row_count} OFFSET {$offset}\n\t\t", $filter_params);
    $results = $wpdb->get_results($query, OBJECT);
    $column_display_names = array('date' => __('Date', AFFILIATES_PLUGIN_DOMAIN) . '*', 'visits' => __('Visits', AFFILIATES_PLUGIN_DOMAIN), 'hits' => __('Hits', AFFILIATES_PLUGIN_DOMAIN), 'referrals' => __('Referrals', AFFILIATES_PLUGIN_DOMAIN), 'ratio' => __('Ratio', AFFILIATES_PLUGIN_DOMAIN));
    $output .= '<div id="" class="hits-overview">';
    $affiliates = affiliates_get_affiliates(true, !$show_inoperative);
    $affiliates_select = '';
    if (!empty($affiliates)) {
        $affiliates_select .= '<label class="affiliate-id-filter">';
        $affiliates_select .= __('Affiliate', AFFILIATES_PLUGIN_DOMAIN);
        $affiliates_select .= ' ';
        $affiliates_select .= '<select class="affiliate-id-filter" name="affiliate_id">';
        $affiliates_select .= '<option value="">--</option>';
        foreach ($affiliates as $affiliate) {
            if ($affiliate_id == $affiliate['affiliate_id']) {
                $selected = ' selected="selected" ';
            } else {
                $selected = '';
            }
            $affiliates_select .= '<option ' . $selected . ' value="' . esc_attr($affiliate['affiliate_id']) . '">' . esc_attr(stripslashes($affiliate['name'])) . '</option>';
        }
        $affiliates_select .= '</select>';
        $affiliates_select .= '</label>';
    }
    $output .= '<div class="filters">' . '<label class="description" for="setfilters">' . __('Filters', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<form id="setfilters" action="" method="post">' . '<div class="filter-section">' . $affiliates_select . '</div>' . '<div class="filter-section">' . '<label class="from-date-filter">' . __('From', AFFILIATES_PLUGIN_DOMAIN) . ' ' . '<input class="datefield from-date-filter" name="from_date" type="text" value="' . esc_attr($from_date) . '"/>' . '</label>' . ' ' . '<label class="thru-date-filter">' . __('Until', AFFILIATES_PLUGIN_DOMAIN) . ' ' . '<input class="datefield thru-date-filter" name="thru_date" type="text" class="datefield" value="' . esc_attr($thru_date) . '"/>' . '</label>' . '</div>' . '<div class="filter-section">' . '<label class="expanded-filter">' . '<input class="expanded-filter" name="expanded_referrals" type="checkbox" ' . ($expanded_referrals ? 'checked="checked"' : '') . '/>' . ' ' . __('Expand referrals', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . ' ' . '<label class="expanded-filter">' . '<input class="expanded-filter" name="expanded_hits" type="checkbox" ' . ($expanded_hits ? 'checked="checked"' : '') . '/>' . ' ' . __('Expand hits', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . ' ' . '<label class="show-inoperative-filter">' . '<input class="show-inoperative-filter" name="show_inoperative" type="checkbox" ' . ($show_inoperative ? 'checked="checked"' : '') . '/>' . ' ' . __('Include inoperative affiliates', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '</div>' . '<div class="filter-buttons">' . wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_FILTER_NONCE, true, false) . '<input class="button" type="submit" value="' . __('Apply', AFFILIATES_PLUGIN_DOMAIN) . '"/>' . '<input class="button" type="submit" name="clear_filters" value="' . __('Clear', AFFILIATES_PLUGIN_DOMAIN) . '"/>' . '<input type="hidden" value="submitted" name="submitted"/>' . '</div>' . '</form>' . '</div>';
    $output .= '
		<div class="page-options">
			<form id="setrowcount" action="" method="post">
				<div>
					<label for="row_count">' . __('Results per page', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input name="row_count" type="text" size="2" value="' . esc_attr($row_count) . '" />
					' . wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_NONCE_1, true, false) . '
					<input class="button" type="submit" value="' . __('Apply', AFFILIATES_PLUGIN_DOMAIN) . '"/>
				</div>
			</form>
		</div>
		';
    if ($paginate) {
        require_once AFFILIATES_CORE_LIB . '/class-affiliates-pagination.php';
        $pagination = new Affiliates_Pagination($count, null, $row_count);
        $output .= '<form id="posts-filter" method="post" action="">';
        $output .= '<div>';
        $output .= wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_NONCE_2, true, false);
        $output .= '</div>';
        $output .= '<div class="tablenav top">';
        $output .= $pagination->pagination('top');
        $output .= '</div>';
        $output .= '</form>';
    }
    $output .= '
		<table id="" class="wp-list-table widefat fixed" cellspacing="0">
		<thead>
			<tr>
			';
    foreach ($column_display_names as $key => $column_display_name) {
        $options = array('orderby' => $key, 'order' => $switch_order);
        $class = "";
        if (strcmp($key, $orderby) == 0) {
            $lorder = strtolower($order);
            $class = "{$key} manage-column sorted {$lorder}";
        } else {
            $class = "{$key} manage-column sortable";
        }
        $column_display_name = '<a href="' . esc_url(add_query_arg($options, $current_url)) . '"><span>' . $column_display_name . '</span><span class="sorting-indicator"></span></a>';
        $output .= "<th scope='col' class='{$class}'>{$column_display_name}</th>";
    }
    $output .= '</tr>
		</thead>
		<tbody>
		';
    if (count($results) > 0) {
        for ($i = 0; $i < count($results); $i++) {
            $result = $results[$i];
            $output .= '<tr class=" ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
            $output .= "<td class='date'>{$result->date}</td>";
            //			$output .= '<td class="date">' . DateHelper::formatDate( DateHelper::s2u( $result->datetime ) ) . '</td>';
            $output .= "<td class='visits'>{$result->visits}</td>";
            $output .= "<td class='hits'>{$result->hits}</td>";
            $output .= "<td class='referrals'>{$result->referrals}</td>";
            $output .= "<td class='ratio'>{$result->ratio}</td>";
            $output .= '</tr>';
            if ($expanded || $expanded_referrals || $expanded_hits) {
                //
                // expanded : referrals ----------------------------------------
                //
                if ($expanded_referrals) {
                    $referrals_filters = " WHERE date(datetime) = %s ";
                    $referrals_filter_params = array($result->date);
                    if ($affiliate_id) {
                        $referrals_filters .= " AND r.affiliate_id = %d ";
                        $referrals_filter_params[] = $affiliate_id;
                    }
                    $referrals_orderby = "datetime {$order}";
                    $referrals_query = $wpdb->prepare("SELECT *\n\t\t\t\t\t\tFROM {$referrals_table} r\n\t\t\t\t\t\tLEFT JOIN {$affiliates_table} a ON r.affiliate_id = a.affiliate_id\n\t\t\t\t\t\t{$referrals_filters}\n\t\t\t\t\t\tORDER BY {$referrals_orderby}\n\t\t\t\t\t\t", $referrals_filter_params);
                    $referrals = $wpdb->get_results($referrals_query, OBJECT);
                    if (count($referrals) > 0) {
                        $output .= '<tr class=" ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                        $output .= '<td colspan="5">';
                        $output .= '<div class="details-referrals">';
                        $output .= '<p class="description">' . __('Referrals', AFFILIATES_PLUGIN_DOMAIN) . '</p>';
                        $output .= '
							<table id="details-referrals-' . esc_attr($result->date) . '" class="details-referrals" cellspacing="0">
							<thead>
							<tr>
							<th scope="col" class="datetime">' . __('Time', AFFILIATES_PLUGIN_DOMAIN) . '</th>
							<th scope="col" class="post-id">' . __('Post', AFFILIATES_PLUGIN_DOMAIN) . '</th>
							<th scope="col" class="affiliate-id">' . __('Affiliate', AFFILIATES_PLUGIN_DOMAIN) . '</th>
							</tr>
							</thead>
							<tbody>
							';
                        foreach ($referrals as $referral) {
                            $output .= '<tr class="details-referrals ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                            //							$output .= "<td class='datetime'>$referral->datetime</td>";
                            $output .= '<td class="datetime">' . DateHelper::s2u($referral->datetime) . '</td>';
                            $link = get_permalink($referral->post_id);
                            $title = get_the_title($referral->post_id);
                            $output .= '<td class="post-id"><a href="' . esc_attr($link) . '" target="_blank">' . wp_filter_nohtml_kses($title) . '</a></td>';
                            $output .= "<td class='affiliate-id'>" . stripslashes(wp_filter_nohtml_kses($referral->name)) . "</td>";
                            $output .= '</tr>';
                        }
                        $output .= '</tbody></table>';
                        $output .= '</div>';
                        // .details-referrals
                        $output .= '</td></tr>';
                    }
                }
                // if $expanded_referrals
                //
                // expanded : hits ----------------------------------------
                //
                if ($expanded_hits) {
                    // get the detailed results for hits
                    $details_orderby = "date {$order}, time {$order}";
                    $details_filters = " WHERE h.date = %s ";
                    $details_filter_params = array($result->date);
                    if ($affiliate_id) {
                        $details_filters .= " AND h.affiliate_id = %d ";
                        $details_filter_params[] = $affiliate_id;
                    }
                    $details_query = $wpdb->prepare("SELECT *\n\t\t\t\t\t\tFROM {$hits_table} h\n\t\t\t\t\t\tLEFT JOIN {$affiliates_table} a ON h.affiliate_id = a.affiliate_id\n\t\t\t\t\t\t{$details_filters}\n\t\t\t\t\t\tORDER BY {$details_orderby}\n\t\t\t\t\t\t", $details_filter_params);
                    $hits = $wpdb->get_results($details_query, OBJECT);
                    $output .= '<tr class=" ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                    $output .= '<td colspan="5">';
                    $output .= '<div class="details-hits">';
                    $output .= '<p class="description">' . __('Hits', AFFILIATES_PLUGIN_DOMAIN) . '</p>';
                    $output .= '
						<table id="details-hits-' . esc_attr($result->date) . '" class="details-hits" cellspacing="0">
						<thead>
						<tr>
						<th scope="col" class="time">' . __('Time', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						<th scope="col" class="ip">' . __('IP', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						<th scope="col" class="affiliate-id">' . __('Affiliate', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						</tr>
						</thead>
						<tbody>
						';
                    foreach ($hits as $hit) {
                        $output .= '<tr class=" details ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                        //						$output .= "<td class='time'>$hit->time</td>";
                        $output .= '<td class="time">' . DateHelper::s2u($hit->datetime) . '</td>';
                        $output .= "<td class='ip'>" . long2ip($hit->ip) . "</td>";
                        $output .= "<td class='affiliate-id'>" . stripslashes(wp_filter_nohtml_kses($hit->name)) . "</td>";
                        $output .= '</tr>';
                    }
                    $output .= '</tbody></table>';
                    $output .= '</div>';
                    // .details-hits
                    $output .= '</td></tr>';
                }
                // if $expanded_hits
            }
            // expanded
        }
    } else {
        $output .= '<tr><td colspan="5">' . __('There are no results.', AFFILIATES_PLUGIN_DOMAIN) . '</td></tr>';
    }
    $output .= '</tbody>';
    $output .= '</table>';
    if ($paginate) {
        require_once AFFILIATES_CORE_LIB . '/class-affiliates-pagination.php';
        $pagination = new Affiliates_Pagination($count, null, $row_count);
        $output .= '<div class="tablenav bottom">';
        $output .= $pagination->pagination('bottom');
        $output .= '</div>';
    }
    $server_dtz = DateHelper::getServerDateTimeZone();
    $output .= '<p>' . sprintf(__("* Date is given for the server's time zone : %s, which has an offset of %s hours with respect to GMT.", AFFILIATES_PLUGIN_DOMAIN), $server_dtz->getName(), $server_dtz->getOffset(new DateTime()) / 3600.0) . '</p>';
    $output .= '</div>';
    // .visits-overview
    echo $output;
    affiliates_footer();
}
 public static function render_results($results)
 {
     $output = "";
     $column_display_names = array('datetime' => __('Date', AFFILIATES_PLUGIN_DOMAIN), 'post_title' => __('Post', AFFILIATES_PLUGIN_DOMAIN), 'name' => __('Affiliate', AFFILIATES_PLUGIN_DOMAIN), 'amount' => __('Amount', AFFILIATES_PLUGIN_DOMAIN), 'currency_id' => __('Currency', AFFILIATES_PLUGIN_DOMAIN), 'status' => __('Status', AFFILIATES_PLUGIN_DOMAIN));
     $status_descriptions = array(AFFILIATES_REFERRAL_STATUS_ACCEPTED => __('Accepted', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_CLOSED => __('Closed', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_PENDING => __('Pending', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_REJECTED => __('Rejected', AFFILIATES_PLUGIN_DOMAIN));
     $status_icons = array(AFFILIATES_REFERRAL_STATUS_ACCEPTED => "<img class='icon' alt='" . __('Accepted', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/accepted.png'/>", AFFILIATES_REFERRAL_STATUS_CLOSED => "<img class='icon' alt='" . __('Closed', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/closed.png'/>", AFFILIATES_REFERRAL_STATUS_PENDING => "<img class='icon' alt='" . __('Pending', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/pending.png'/>", AFFILIATES_REFERRAL_STATUS_REJECTED => "<img class='icon' alt='" . __('Rejected', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/rejected.png'/>");
     $output .= '<table id="referrals" class="referrals wp-list-table widefat fixed" cellspacing="0">';
     $output .= "<thead>";
     $output .= "<tr>";
     foreach ($column_display_names as $key => $column_display_name) {
         $output .= "<th scope='col'>{$column_display_name}</th>";
     }
     $output .= "</tr>";
     $output .= "</thead>";
     $output .= "<tbody>";
     if (count($results) > 0) {
         for ($i = 0; $i < count($results); $i++) {
             $result = $results[$i];
             $output .= '<tr class="details-referrals ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
             $output .= '<td class="datetime">' . DateHelper::s2u($result->datetime) . '</td>';
             $title = get_the_title($result->post_id);
             $output .= '<td class="post_title">' . wp_filter_nohtml_kses($title) . '</td>';
             $output .= "<td class='name'>" . stripslashes(wp_filter_nohtml_kses($result->name)) . "</td>";
             $output .= "<td class='amount'>" . stripslashes(wp_filter_nohtml_kses($result->amount)) . "</td>";
             $output .= "<td class='currency_id'>" . stripslashes(wp_filter_nohtml_kses($result->currency_id)) . "</td>";
             $output .= "<td class='status'>";
             $output .= isset($status_icons[$result->status]) ? $status_icons[$result->status] : '';
             $output .= isset($status_descriptions[$result->status]) ? $status_descriptions[$result->status] : '';
             $output .= "</td>";
             $output .= '</tr>';
         }
     } else {
         $output .= '<tr><td colspan="' . count($column_display_names) . '">' . __('There are no results.', AFFILIATES_PLUGIN_DOMAIN) . '</td></tr>';
     }
     $output .= '</tbody>';
     $output .= '</table>';
     return $output;
 }
function affiliates_admin_hits_affiliate()
{
    global $wpdb, $affiliates_options;
    $output = '';
    if (!current_user_can(AFFILIATES_ACCESS_AFFILIATES)) {
        wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
    }
    if (isset($_POST['from_date']) || isset($_POST['thru_date']) || isset($_POST['clear_filters']) || isset($_POST['affiliate_id']) || isset($_POST['expanded']) || isset($_POST['expanded_hits']) || isset($_POST['expanded_referrals']) || isset($_POST['show_inoperative'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_AFF_FILTER_NONCE], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    // filters
    $from_date = $affiliates_options->get_option('hits_affiliate_from_date', null);
    $thru_date = $affiliates_options->get_option('hits_affiliate_thru_date', null);
    $affiliate_id = $affiliates_options->get_option('hits_affiliate_affiliate_id', null);
    $status = $affiliates_options->get_option('hits_affiliate_status', null);
    $expanded = $affiliates_options->get_option('hits_affiliate_expanded', null);
    // @todo input ist not shown, eventually remove unless ...
    $expanded_referrals = $affiliates_options->get_option('hits_affiliate_expanded_referrals', null);
    $expanded_hits = $affiliates_options->get_option('hits_affiliate_expanded_hits', null);
    $show_inoperative = $affiliates_options->get_option('hits_affiliate_show_inoperative', null);
    if (isset($_POST['clear_filters'])) {
        $affiliates_options->delete_option('hits_affiliate_from_date');
        $affiliates_options->delete_option('hits_affiliate_thru_date');
        $affiliates_options->delete_option('hits_affiliate_affiliate_id');
        $affiliates_options->delete_option('hits_affiliate_status');
        $affiliates_options->delete_option('hits_affiliate_expanded');
        $affiliates_options->delete_option('hits_affiliate_expanded_referrals');
        $affiliates_options->delete_option('hits_affiliate_expanded_hits');
        $affiliates_options->delete_option('hits_affiliate_show_inoperative');
        $from_date = null;
        $thru_date = null;
        $affiliate_id = null;
        $status = null;
        $expanded = null;
        $expanded_hits = null;
        $expanded_referrals = null;
        $show_inoperative = null;
    } else {
        if (isset($_POST['submitted'])) {
            // filter by date(s)
            if (!empty($_POST['from_date'])) {
                $from_date = date('Y-m-d', strtotime($_POST['from_date']));
                $affiliates_options->update_option('hits_affiliate_from_date', $from_date);
            } else {
                $from_date = null;
                $affiliates_options->delete_option('hits_affiliate_from_date');
            }
            if (!empty($_POST['thru_date'])) {
                $thru_date = date('Y-m-d', strtotime($_POST['thru_date']));
                $affiliates_options->update_option('hits_affiliate_thru_date', $thru_date);
            } else {
                $thru_date = null;
                $affiliates_options->delete_option('hits_affiliate_thru_date');
            }
            if ($from_date && $thru_date) {
                if (strtotime($from_date) > strtotime($thru_date)) {
                    $thru_date = null;
                    $affiliates_options->delete_option('hits_affiliate_thru_date');
                }
            }
            // filter by affiliate id
            if (!empty($_POST['affiliate_id'])) {
                $affiliate_id = affiliates_check_affiliate_id($_POST['affiliate_id']);
                if ($affiliate_id) {
                    $affiliates_options->update_option('hits_affiliate_affiliate_id', $affiliate_id);
                }
            } else {
                if (isset($_POST['affiliate_id'])) {
                    // empty && isset => '' => all
                    $affiliate_id = null;
                    $affiliates_options->delete_option('hits_affiliate_affiliate_id');
                }
            }
            if (!empty($_POST['status'])) {
                if (is_array($_POST['status'])) {
                    $stati = array();
                    foreach ($_POST['status'] as $status) {
                        if ($status = Affiliates_Utility::verify_referral_status_transition($status, $status)) {
                            $stati[] = $status;
                        }
                    }
                    if (count($stati) > 0) {
                        $status = $stati;
                        $affiliates_options->update_option('hits_affiliate_status', $stati);
                    } else {
                        $status = null;
                        $affiliates_options->delete_option('hits_affiliate_status');
                    }
                }
            } else {
                $status = null;
                $affiliates_options->delete_option('hits_affiliate_status');
            }
            // expanded details?
            if (!empty($_POST['expanded'])) {
                $expanded = true;
                $affiliates_options->update_option('hits_affiliate_expanded', true);
            } else {
                $expanded = false;
                $affiliates_options->delete_option('hits_affiliate_expanded');
            }
            if (!empty($_POST['expanded_hits'])) {
                $expanded_hits = true;
                $affiliates_options->update_option('hits_affiliate_expanded_hits', true);
            } else {
                $expanded_hits = false;
                $affiliates_options->delete_option('hits_affiliate_expanded_hits');
            }
            if (!empty($_POST['expanded_referrals'])) {
                $expanded_referrals = true;
                $affiliates_options->update_option('hits_affiliate_expanded_referrals', true);
            } else {
                $expanded_referrals = false;
                $affiliates_options->delete_option('hits_affiliate_expanded_referrals');
            }
            if (!empty($_POST['show_inoperative'])) {
                $show_inoperative = true;
                $affiliates_options->update_option('hits_affiliate_show_inoperative', true);
            } else {
                $show_inoperative = false;
                $affiliates_options->delete_option('hits_affiliate_show_inoperative');
            }
        }
    }
    if (isset($_POST['row_count'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_AFF_NONCE_1], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    if (isset($_POST['paged'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_AFF_NONCE_2], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    $current_url = (is_ssl() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
    $current_url = remove_query_arg('paged', $current_url);
    $affiliates_table = _affiliates_get_tablename('affiliates');
    $referrals_table = _affiliates_get_tablename('referrals');
    $hits_table = _affiliates_get_tablename('hits');
    $output .= '<div>' . '<h1>' . __('Affiliates & Referrals', AFFILIATES_PLUGIN_DOMAIN) . '</h1>' . '</div>';
    $row_count = isset($_POST['row_count']) ? intval($_POST['row_count']) : 0;
    if ($row_count <= 0) {
        $row_count = $affiliates_options->get_option('hits_affiliate_per_page', AFFILIATES_HITS_AFFILIATE_PER_PAGE);
    } else {
        $affiliates_options->update_option('hits_affiliate_per_page', $row_count);
    }
    $offset = isset($_GET['offset']) ? intval($_GET['offset']) : 0;
    if ($offset < 0) {
        $offset = 0;
    }
    $paged = isset($_REQUEST['paged']) ? intval($_REQUEST['paged']) : 0;
    if ($paged < 0) {
        $paged = 0;
    }
    $orderby = isset($_GET['orderby']) ? $_GET['orderby'] : null;
    switch ($orderby) {
        case 'date':
        case 'visits':
        case 'hits':
        case 'referrals':
        case 'ratio':
        case 'name':
            break;
        default:
            $orderby = 'name';
    }
    $order = isset($_GET['order']) ? $_GET['order'] : null;
    switch ($order) {
        case 'asc':
        case 'ASC':
            $switch_order = 'DESC';
            break;
        case 'desc':
        case 'DESC':
            $switch_order = 'ASC';
            break;
        default:
            $order = 'ASC';
            $switch_order = 'DESC';
    }
    $filters = " WHERE 1=%d ";
    $filter_params = array(1);
    // We now have the desired dates from the user's point of view, i.e. in her timezone.
    // If supported, adjust the dates for the site's timezone:
    if ($from_date) {
        $from_datetime = DateHelper::u2s($from_date);
    }
    if ($thru_date) {
        $thru_datetime = DateHelper::u2s($thru_date, 24 * 3600);
    }
    if ($from_date && $thru_date) {
        $filters .= " AND datetime >= %s AND datetime < %s ";
        $filter_params[] = $from_datetime;
        $filter_params[] = $thru_datetime;
    } else {
        if ($from_date) {
            $filters .= " AND datetime >= %s ";
            $filter_params[] = $from_datetime;
        } else {
            if ($thru_date) {
                $filters .= " AND datetime < %s ";
                $filter_params[] = $thru_datetime;
            }
        }
    }
    if ($affiliate_id) {
        $filters .= " AND h.affiliate_id = %d ";
        $filter_params[] = $affiliate_id;
    }
    // how many are there ?
    $count_query = $wpdb->prepare("SELECT affiliate_id FROM {$hits_table} h\n\t\t{$filters}\n\t\tGROUP BY affiliate_id\n\t\t", $filter_params);
    $wpdb->query($count_query);
    $count = $wpdb->num_rows;
    if ($count > $row_count) {
        $paginate = true;
    } else {
        $paginate = false;
    }
    $pages = ceil($count / $row_count);
    if ($paged > $pages) {
        $paged = $pages;
    }
    if ($paged != 0) {
        $offset = ($paged - 1) * $row_count;
    }
    // Get the summarized results, these are grouped by date.
    // Note: Referrals on dates without a hit will not be included.
    // @see notes about this in affiliates_admin_hits()
    $date_condition = "";
    if ($from_date && $thru_date) {
        $date_condition = " AND datetime >= '" . $from_datetime . "' AND datetime < '" . $thru_datetime . "' ";
    } else {
        if ($from_date) {
            $date_condition = " AND datetime >= '" . $from_datetime . "' ";
        } else {
            if ($thru_date) {
                $date_condition = " AND datetime < '" . $thru_datetime . "' ";
            }
        }
    }
    $status_condition = "";
    if (is_array($status) && count($status) > 0) {
        $status_condition = " AND status IN ('" . implode("','", $status) . "') ";
    }
    $query = $wpdb->prepare("\n\t\t\tSELECT\n\t\t\t\t*,\n\t\t\t\tcount(distinct ip) visits,\n\t\t\t\tsum(count) hits,\n\t\t\t\t(SELECT COUNT(*) FROM {$referrals_table} WHERE affiliate_id = h.affiliate_id {$date_condition} {$status_condition} ) referrals,\n\t\t\t\t((SELECT COUNT(*) FROM {$referrals_table} WHERE affiliate_id = h.affiliate_id {$date_condition} {$status_condition} )/COUNT(DISTINCT ip)) ratio\n\t\t\tFROM {$hits_table} h\n\t\t\tLEFT JOIN {$affiliates_table} a ON h.affiliate_id = a.affiliate_id\n\t\t\t{$filters}\n\t\t\tGROUP BY h.affiliate_id\n\t\t\tORDER BY {$orderby} {$order}\n\t\t\tLIMIT {$row_count} OFFSET {$offset}\n\t\t\t", $filter_params);
    $results = $wpdb->get_results($query, OBJECT);
    $column_display_names = array('name' => __('Affiliate', AFFILIATES_PLUGIN_DOMAIN), 'visits' => __('Visitors', AFFILIATES_PLUGIN_DOMAIN), 'hits' => __('Hits', AFFILIATES_PLUGIN_DOMAIN), 'referrals' => __('Referrals', AFFILIATES_PLUGIN_DOMAIN), 'ratio' => __('Ratio', AFFILIATES_PLUGIN_DOMAIN));
    $output .= '<div id="" class="hits-affiliates-overview">';
    $affiliates = affiliates_get_affiliates(true, !$show_inoperative);
    $affiliates_select = '';
    if (!empty($affiliates)) {
        $affiliates_select .= '<label class="affiliate-id-filter">';
        $affiliates_select .= __('Affiliate', AFFILIATES_PLUGIN_DOMAIN);
        $affiliates_select .= ' ';
        $affiliates_select .= '<select class="affiliate-id-filter" name="affiliate_id">';
        $affiliates_select .= '<option value="">--</option>';
        foreach ($affiliates as $affiliate) {
            if ($affiliate_id == $affiliate['affiliate_id']) {
                $selected = ' selected="selected" ';
            } else {
                $selected = '';
            }
            $affiliates_select .= '<option ' . $selected . ' value="' . esc_attr($affiliate['affiliate_id']) . '">' . esc_attr(stripslashes($affiliate['name'])) . '</option>';
        }
        $affiliates_select .= '</select>';
        $affiliates_select .= '</label>';
    }
    $status_descriptions = array(AFFILIATES_REFERRAL_STATUS_ACCEPTED => __('Accepted', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_CLOSED => __('Closed', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_PENDING => __('Pending', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_REJECTED => __('Rejected', AFFILIATES_PLUGIN_DOMAIN));
    $status_icons = array(AFFILIATES_REFERRAL_STATUS_ACCEPTED => "<img class='icon' alt='" . __('Accepted', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/accepted.png'/>", AFFILIATES_REFERRAL_STATUS_CLOSED => "<img class='icon' alt='" . __('Closed', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/closed.png'/>", AFFILIATES_REFERRAL_STATUS_PENDING => "<img class='icon' alt='" . __('Pending', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/pending.png'/>", AFFILIATES_REFERRAL_STATUS_REJECTED => "<img class='icon' alt='" . __('Rejected', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/rejected.png'/>");
    $status_checkboxes = '';
    foreach ($status_descriptions as $key => $label) {
        $checked = empty($status) || is_array($status) && in_array($key, $status) ? ' checked="checked" ' : '';
        $status_checkboxes .= '<label style="padding-right:1em;">';
        $status_checkboxes .= sprintf('<input type="checkbox" name="status[]" value="%s" %s />', esc_attr($key), $checked);
        $status_checkboxes .= $status_icons[$key] . ' ' . $label;
        $status_checkboxes .= '</label>';
    }
    $output .= '<div class="filters">' . '<label class="description" for="setfilters">' . __('Filters', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<form id="setfilters" action="" method="post">' . '<div class="filter-section">' . $affiliates_select . '</div>' . '<div class="filter-section">' . '<label class="from-date-filter">' . __('From', AFFILIATES_PLUGIN_DOMAIN) . ' ' . '<input class="datefield from-date-filter" name="from_date" type="text" value="' . esc_attr($from_date) . '"/>' . '</label>' . ' ' . '<label class="thru-date-filter">' . __('Until', AFFILIATES_PLUGIN_DOMAIN) . ' ' . '<input class="datefield thru-date-filter" name="thru_date" type="text" class="datefield" value="' . esc_attr($thru_date) . '"/>' . '</label>' . '</div>' . '<div class="filter-section">' . '<span style="padding-right:1em">' . __('Status', AFFILIATES_PLUGIN_DOMAIN) . '</span>' . ' ' . $status_checkboxes . '</div>' . '<div class="filter-section">' . '<label class="expanded-filter">' . '<input class="expanded-filter" name="expanded_referrals" type="checkbox" ' . ($expanded_referrals ? 'checked="checked"' : '') . '/>' . ' ' . __('Expand referrals', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . ' ' . '<label class="expanded-filter">' . '<input class="expanded-filter" name="expanded_hits" type="checkbox" ' . ($expanded_hits ? 'checked="checked"' : '') . '/>' . ' ' . __('Expand hits', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . ' ' . '<label class="show-inoperative-filter">' . '<input class="show-inoperative-filter" name="show_inoperative" type="checkbox" ' . ($show_inoperative ? 'checked="checked"' : '') . '/>' . ' ' . __('Include inoperative affiliates', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '</div>' . '<div class="filter-buttons">' . wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_AFF_FILTER_NONCE, true, false) . '<input class="button" type="submit" value="' . __('Apply', AFFILIATES_PLUGIN_DOMAIN) . '"/>' . '<input class="button" type="submit" name="clear_filters" value="' . __('Clear', AFFILIATES_PLUGIN_DOMAIN) . '"/>' . '<input type="hidden" value="submitted" name="submitted"/>' . '</div>' . '</form>' . '</div>';
    $output .= '
		<div class="page-options">
			<form id="setrowcount" action="" method="post">
				<div>
					<label for="row_count">' . __('Results per page', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input name="row_count" type="text" size="2" value="' . esc_attr($row_count) . '" />
					' . wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_AFF_NONCE_1, true, false) . '
					<input class="button" type="submit" value="' . __('Apply', AFFILIATES_PLUGIN_DOMAIN) . '"/>
				</div>
			</form>
		</div>
		';
    if ($paginate) {
        require_once AFFILIATES_CORE_LIB . '/class-affiliates-pagination.php';
        $pagination = new Affiliates_Pagination($count, null, $row_count);
        $output .= '<form id="posts-filter" method="post" action="">';
        $output .= '<div>';
        $output .= wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_AFF_NONCE_2, true, false);
        $output .= '</div>';
        $output .= '<div class="tablenav top">';
        $output .= $pagination->pagination('top');
        $output .= '</div>';
        $output .= '</form>';
    }
    $output .= '
		<table id="" class="wp-list-table widefat fixed" cellspacing="0">
		<thead>
			<tr>
			';
    foreach ($column_display_names as $key => $column_display_name) {
        $options = array('orderby' => $key, 'order' => $switch_order);
        $class = "";
        if (strcmp($key, $orderby) == 0) {
            $lorder = strtolower($order);
            $class = "{$key} manage-column sorted {$lorder}";
        } else {
            $class = "{$key} manage-column sortable";
        }
        $column_display_name = '<a href="' . esc_url(add_query_arg($options, $current_url)) . '"><span>' . $column_display_name . '</span><span class="sorting-indicator"></span></a>';
        $output .= "<th scope='col' class='{$class}'>{$column_display_name}</th>";
    }
    $output .= '</tr>
		</thead>
		<tbody>
		';
    if (count($results) > 0) {
        for ($i = 0; $i < count($results); $i++) {
            $result = $results[$i];
            $output .= '<tr class=" ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
            $affiliate = affiliates_get_affiliate($result->affiliate_id);
            $output .= "<td class='affiliate-name'>" . stripslashes(wp_filter_nohtml_kses($affiliate['name'])) . "</td>";
            $output .= "<td class='visits'>{$result->visits}</td>";
            $output .= "<td class='hits'>{$result->hits}</td>";
            $output .= "<td class='referrals'>{$result->referrals}</td>";
            $output .= "<td class='ratio'>{$result->ratio}</td>";
            $output .= '</tr>';
            if ($expanded || $expanded_referrals || $expanded_hits) {
                //
                // expanded : referrals ----------------------------------------
                //
                if ($expanded_referrals) {
                    // get the detailed results for referrals
                    $referrals_filters = " WHERE r.affiliate_id = %d ";
                    $referrals_filter_params = array($result->affiliate_id);
                    if ($from_date && $thru_date) {
                        $referrals_filters .= " AND datetime >= %s AND datetime < %s ";
                        $referrals_filter_params[] = $from_datetime;
                        $referrals_filter_params[] = $thru_datetime;
                    } else {
                        if ($from_date) {
                            $referrals_filters .= " AND datetime >= %s ";
                            $referrals_filter_params[] = $from_datetime;
                        } else {
                            if ($thru_date) {
                                $referrals_filters .= " datetime < %s ";
                                $referrals_filter_params[] = $thru_datetime;
                            }
                        }
                    }
                    $referrals_orderby = "datetime {$order}";
                    $referrals_query = $wpdb->prepare("SELECT *\n\t\t\t\t\t\tFROM {$referrals_table} r\n\t\t\t\t\t\tLEFT JOIN {$affiliates_table} a ON r.affiliate_id = a.affiliate_id\n\t\t\t\t\t\t{$referrals_filters}\n\t\t\t\t\t\tORDER BY {$referrals_orderby}\n\t\t\t\t\t\t", $referrals_filter_params);
                    $referrals = $wpdb->get_results($referrals_query, OBJECT);
                    if (count($referrals) > 0) {
                        $output .= '<tr class=" ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                        $output .= '<td colspan="5">';
                        $output .= '<div class="details-referrals">';
                        $output .= '<p class="description">' . __('Referrals', AFFILIATES_PLUGIN_DOMAIN) . '</p>';
                        $output .= '
							<table id="details-referrals-' . esc_attr($result->date) . '" class="details-referrals" cellspacing="0">
							<thead>
							<tr>
							<th scope="col" class="datetime">' . __('Time', AFFILIATES_PLUGIN_DOMAIN) . '</th>
							<th scope="col" class="post-id">' . __('Post', AFFILIATES_PLUGIN_DOMAIN) . '</th>
							<th scope="col" class="affiliate-id">' . __('Affiliate', AFFILIATES_PLUGIN_DOMAIN) . '</th>
							</tr>
							</thead>
							<tbody>
							';
                        foreach ($referrals as $referral) {
                            $output .= '<tr class="details-referrals ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                            $output .= "<td class='datetime'>" . DateHelper::s2u($referral->datetime) . "</td>";
                            $link = get_permalink($referral->post_id);
                            $title = get_the_title($referral->post_id);
                            $output .= '<td class="post-id"><a href="' . esc_attr($link) . '" target="_blank">' . stripslashes(wp_filter_nohtml_kses($title)) . '</a></td>';
                            $output .= "<td class='affiliate-id'>" . stripslashes(wp_filter_nohtml_kses($referral->name)) . "</td>";
                            $output .= '</tr>';
                        }
                        $output .= '</tbody></table>';
                        $output .= '</div>';
                        // .details-referrals
                        $output .= '</td></tr>';
                    }
                }
                // if $expanded_referrals
                //
                // expanded : hits ----------------------------------------
                //
                if ($expanded_hits) {
                    // get the detailed results for hits
                    $details_orderby = "date {$order}, time {$order}";
                    $details_filters = " WHERE h.affiliate_id = %d ";
                    $details_filter_params = array($result->affiliate_id);
                    if ($from_date && $thru_date) {
                        $details_filters .= " AND datetime >= %s AND datetime < %s ";
                        $details_filter_params[] = $from_datetime;
                        $details_filter_params[] = $thru_datetime;
                    } else {
                        if ($from_date) {
                            $details_filters .= " AND datetime >= %s ";
                            $details_filter_params[] = $from_datetime;
                        } else {
                            if ($thru_date) {
                                $details_filters .= " datetime < %s ";
                                $details_filter_params[] = $thru_datetime;
                            }
                        }
                    }
                    $details_query = $wpdb->prepare("SELECT *\n\t\t\t\t\t\tFROM {$hits_table} h\n\t\t\t\t\t\tLEFT JOIN {$affiliates_table} a ON h.affiliate_id = a.affiliate_id\n\t\t\t\t\t\t{$details_filters}\n\t\t\t\t\t\tORDER BY {$details_orderby}\n\t\t\t\t\t\t", $details_filter_params);
                    $hits = $wpdb->get_results($details_query, OBJECT);
                    $output .= '<tr class=" ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                    $output .= '<td colspan="5">';
                    $output .= '<div class="details-hits">';
                    $output .= '<p class="description">' . __('Hits', AFFILIATES_PLUGIN_DOMAIN) . '</p>';
                    $output .= '
						<table id="details-hits-' . esc_attr($result->date) . '" class="details-hits" cellspacing="0">
						<thead>
						<tr>
						<th scope="col" class="date">' . __('Date', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						<th scope="col" class="time">' . __('Time', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						<th scope="col" class="ip">' . __('IP', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						<th scope="col" class="count">' . __('Count', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						<th scope="col" class="affiliate-id">' . __('Affiliate', AFFILIATES_PLUGIN_DOMAIN) . '</th>
						</tr>
						</thead>
						<tbody>
						';
                    foreach ($hits as $hit) {
                        $output .= '<tr class="details ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                        //						$output .= "<td class='date'>$hit->date</td>";
                        $output .= '<td class="date">' . DateHelper::formatDate(DateHelper::s2u($hit->datetime)) . '</td>';
                        //						$output .= "<td class='time'>$hit->time</td>";
                        $output .= '<td class="time">' . DateHelper::formatTime(DateHelper::s2u($hit->datetime)) . '</td>';
                        $output .= "<td class='ip'>" . long2ip($hit->ip) . "</td>";
                        $output .= "<td class='count'>{$hit->count}</td>";
                        $output .= "<td class='affiliate-id'>" . stripslashes(wp_filter_nohtml_kses($hit->name)) . "</td>";
                        $output .= '</tr>';
                    }
                    $output .= '</tbody></table>';
                    $output .= '</div>';
                    // .details-hits
                    $output .= '</td></tr>';
                }
                // if $expanded_hits
            }
            // expanded
        }
    } else {
        $output .= '<tr><td colspan="5">' . __('There are no results.', AFFILIATES_PLUGIN_DOMAIN) . '</td></tr>';
    }
    $output .= '</tbody>';
    $output .= '</table>';
    if ($paginate) {
        require_once AFFILIATES_CORE_LIB . '/class-affiliates-pagination.php';
        $pagination = new Affiliates_Pagination($count, null, $row_count);
        $output .= '<div class="tablenav bottom">';
        $output .= $pagination->pagination('bottom');
        $output .= '</div>';
    }
    $output .= '</div>';
    // .visits-overview
    echo $output;
    affiliates_footer();
}
/**
 * Referrals screen.
 */
function affiliates_admin_referrals()
{
    global $wpdb, $affiliates_options;
    $output = '';
    if (!current_user_can(AFFILIATES_ACCESS_AFFILIATES)) {
        wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
    }
    // $_GET actions
    if (isset($_GET['action'])) {
        switch ($_GET['action']) {
            case 'edit':
                require_once AFFILIATES_CORE_LIB . '/affiliates-admin-referral-edit.php';
                if (isset($_GET['referral_id'])) {
                    return affiliates_admin_referral_edit(intval($_GET['referral_id']));
                } else {
                    return affiliates_admin_referral_edit();
                }
                break;
            case 'remove':
                if (isset($_GET['referral_id'])) {
                    require_once AFFILIATES_CORE_LIB . '/affiliates-admin-referral-remove.php';
                    return affiliates_admin_referral_remove($_GET['referral_id']);
                }
                break;
        }
    }
    if (isset($_POST['from_date']) || isset($_POST['thru_date']) || isset($_POST['clear_filters']) || isset($_POST['affiliate_id']) || isset($_POST['status']) || isset($_POST['search']) || isset($_POST['expanded']) || isset($_POST['expanded_data']) || isset($_POST['expanded_description']) || isset($_POST['show_inoperative'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_FILTER_NONCE], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    $affiliates_table = _affiliates_get_tablename('affiliates');
    $referrals_table = _affiliates_get_tablename('referrals');
    $hits_table = _affiliates_get_tablename('hits');
    $posts_table = $wpdb->prefix . 'posts';
    // actions
    if (isset($_POST['affiliate_id']) && isset($_POST['post_id']) && isset($_POST['datetime']) && isset($_POST['action'])) {
        if (isset($_POST['status'])) {
            $referral = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$referrals_table} WHERE affiliate_id = %d AND post_id = %d AND datetime = %s", intval($_POST['affiliate_id']), intval($_POST['post_id']), $_POST['datetime']));
            if ($referral) {
                if (Affiliates_Utility::verify_referral_status_transition($referral->status, $_POST['status'])) {
                    $wpdb->query($wpdb->prepare("UPDATE {$referrals_table} SET status = %s WHERE affiliate_id = %d AND post_id = %d AND datetime = %s AND status = %s", $_POST['status'], intval($referral->affiliate_id), intval($referral->post_id), $referral->datetime, $referral->status));
                }
            }
        }
    }
    // filters
    $from_date = $affiliates_options->get_option('referrals_from_date', null);
    $thru_date = $affiliates_options->get_option('referrals_thru_date', null);
    $affiliate_id = $affiliates_options->get_option('referrals_affiliate_id', null);
    $status = $affiliates_options->get_option('referrals_status', null);
    $search = $affiliates_options->get_option('referrals_search', null);
    $search_description = $affiliates_options->get_option('referrals_search_description', null);
    $expanded = $affiliates_options->get_option('referrals_expanded', null);
    $expanded_description = $affiliates_options->get_option('referrals_expanded_description', null);
    $expanded_data = $affiliates_options->get_option('referrals_expanded_data', null);
    $show_inoperative = $affiliates_options->get_option('referrals_show_inoperative', null);
    if (!isset($_POST['action']) && isset($_POST['clear_filters'])) {
        $affiliates_options->delete_option('referrals_from_date');
        $affiliates_options->delete_option('referrals_thru_date');
        $affiliates_options->delete_option('referrals_affiliate_id');
        $affiliates_options->delete_option('referrals_status');
        $affiliates_options->delete_option('referrals_search');
        $affiliates_options->delete_option('referrals_expanded');
        $affiliates_options->delete_option('referrals_expanded_description');
        $affiliates_options->delete_option('referrals_expanded_data');
        $affiliates_options->delete_option('referrals_show_inoperative');
        $from_date = null;
        $thru_date = null;
        $affiliate_id = null;
        $status = null;
        $search = null;
        $search_description = null;
        $expanded = null;
        $expanded_data = null;
        $expanded_description = null;
        $show_inoperative = null;
    } else {
        if (!isset($_POST['action']) && isset($_POST['submitted'])) {
            // filter by date(s)
            if (!empty($_POST['from_date'])) {
                $from_date = date('Y-m-d', strtotime($_POST['from_date']));
                $affiliates_options->update_option('referrals_from_date', $from_date);
            } else {
                $from_date = null;
                $affiliates_options->delete_option('referrals_from_date');
            }
            if (!empty($_POST['thru_date'])) {
                $thru_date = date('Y-m-d', strtotime($_POST['thru_date']));
                $affiliates_options->update_option('referrals_thru_date', $thru_date);
            } else {
                $thru_date = null;
                $affiliates_options->delete_option('referrals_thru_date');
            }
            if ($from_date && $thru_date) {
                if (strtotime($from_date) > strtotime($thru_date)) {
                    $thru_date = null;
                    $affiliates_options->delete_option('referrals_thru_date');
                }
            }
            // filter by affiliate id
            if (!empty($_POST['affiliate_id'])) {
                $affiliate_id = affiliates_check_affiliate_id($_POST['affiliate_id']);
                if ($affiliate_id) {
                    $affiliates_options->update_option('referrals_affiliate_id', $affiliate_id);
                }
            } else {
                if (isset($_POST['affiliate_id'])) {
                    // empty && isset => '' => all
                    $affiliate_id = null;
                    $affiliates_options->delete_option('referrals_affiliate_id');
                }
            }
            if (!empty($_POST['status'])) {
                if ($status = Affiliates_Utility::verify_referral_status_transition($_POST['status'], $_POST['status'])) {
                    $affiliates_options->update_option('referrals_status', $status);
                } else {
                    $status = null;
                    $affiliates_options->delete_option('referrals_status');
                }
            } else {
                $status = null;
                $affiliates_options->delete_option('referrals_status');
            }
            if (!empty($_POST['search'])) {
                $search = $_POST['search'];
                $affiliates_options->update_option('referrals_search', $_POST['search']);
            } else {
                $search = null;
                $affiliates_options->delete_option('referrals_search');
            }
            if (!empty($_POST['search_description'])) {
                $search_description = true;
                $affiliates_options->update_option('referrals_search_description', true);
            } else {
                $search_description = false;
                $affiliates_options->delete_option('referrals_search_description');
            }
            // expanded details?
            if (!empty($_POST['expanded'])) {
                $expanded = true;
                $affiliates_options->update_option('referrals_expanded', true);
            } else {
                $expanded = false;
                $affiliates_options->delete_option('referrals_expanded');
            }
            if (!empty($_POST['expanded_data'])) {
                $expanded_data = true;
                $affiliates_options->update_option('referrals_expanded_data', true);
            } else {
                $expanded_data = false;
                $affiliates_options->delete_option('referrals_expanded_data');
            }
            if (!empty($_POST['expanded_description'])) {
                $expanded_description = true;
                $affiliates_options->update_option('referrals_expanded_description', true);
            } else {
                $expanded_description = false;
                $affiliates_options->delete_option('referrals_expanded_description');
            }
            if (!empty($_POST['show_inoperative'])) {
                $show_inoperative = true;
                $affiliates_options->update_option('referrals_show_inoperative', true);
            } else {
                $show_inoperative = false;
                $affiliates_options->delete_option('referrals_show_inoperative');
            }
        }
    }
    if (isset($_POST['row_count'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_NONCE_1], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    if (isset($_POST['paged'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_HITS_NONCE_2], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    $current_url = (is_ssl() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
    $current_url = remove_query_arg('paged', $current_url);
    $output .= '<div>' . '<h2>' . __('Referrals', AFFILIATES_PLUGIN_DOMAIN) . '</h2>' . '</div>';
    $output .= '<div class="manage add">';
    $output .= sprintf('<a title="%s" class="add button" href="%s"><img class="icon" alt="%s" src="%s" /><span class="label">%s</span></a>', __('Click to add a referral manually', AFFILIATES_PLUGIN_DOMAIN), esc_url(add_query_arg('action', 'edit', $current_url)), __('Add', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_PLUGIN_URL . 'images/add.png', __('Add', AFFILIATES_PLUGIN_DOMAIN));
    $output .= '</div>';
    $row_count = isset($_POST['row_count']) ? intval($_POST['row_count']) : 0;
    if ($row_count <= 0) {
        $row_count = $affiliates_options->get_option('referrals_per_page', AFFILIATES_HITS_PER_PAGE);
    } else {
        $affiliates_options->update_option('referrals_per_page', $row_count);
    }
    $offset = isset($_GET['offset']) ? intval($_GET['offset']) : 0;
    if ($offset < 0) {
        $offset = 0;
    }
    $paged = isset($_GET['paged']) ? intval($_GET['paged']) : 0;
    if ($paged < 0) {
        $paged = 0;
    }
    $orderby = isset($_GET['orderby']) ? $_GET['orderby'] : null;
    switch ($orderby) {
        case 'datetime':
        case 'name':
        case 'post_title':
        case 'amount':
        case 'currency_id':
        case 'status':
            break;
        default:
            $orderby = 'datetime';
    }
    $order = isset($_GET['order']) ? $_GET['order'] : null;
    switch ($order) {
        case 'asc':
        case 'ASC':
            $switch_order = 'DESC';
            break;
        case 'desc':
        case 'DESC':
            $switch_order = 'ASC';
            break;
        default:
            $order = 'DESC';
            $switch_order = 'ASC';
    }
    if ($from_date || $thru_date || $affiliate_id || $status || $search) {
        $filters = " WHERE ";
    } else {
        $filters = '';
    }
    $filter_params = array();
    // We have the desired dates from the user's point of view, i.e. in her timezone.
    // If supported, adjust the dates for the site's timezone:
    if ($from_date) {
        $from_datetime = DateHelper::u2s($from_date);
    }
    if ($thru_date) {
        $thru_datetime = DateHelper::u2s($thru_date, 24 * 3600);
    }
    if ($from_date && $thru_date) {
        $filters .= " datetime >= %s AND datetime < %s ";
        $filter_params[] = $from_datetime;
        $filter_params[] = $thru_datetime;
    } else {
        if ($from_date) {
            $filters .= " datetime >= %s ";
            $filter_params[] = $from_datetime;
        } else {
            if ($thru_date) {
                $filters .= " datetime < %s ";
                $filter_params[] = $thru_datetime;
            }
        }
    }
    if ($affiliate_id) {
        if ($from_date || $thru_date) {
            $filters .= " AND ";
        }
        $filters .= " r.affiliate_id = %d ";
        $filter_params[] = $affiliate_id;
    }
    if ($status) {
        if ($from_date || $thru_date || $affiliate_id) {
            $filters .= " AND ";
        }
        $filters .= " r.status = %s ";
        $filter_params[] = $status;
    }
    if ($search) {
        if ($from_date || $thru_date || $affiliate_id || $status) {
            $filters .= " AND ";
        }
        if ($search_description) {
            $filters .= " ( r.data LIKE '%%%s%%' OR r.description LIKE '%%%s%%' ) ";
            $filter_params[] = $search;
            $filter_params[] = $search;
        } else {
            $filters .= " r.data LIKE '%%%s%%' ";
            $filter_params[] = $search;
        }
    }
    // how many are there ?
    $count_query = $wpdb->prepare("SELECT count(*) FROM {$referrals_table} r\n\t\t{$filters}\n\t\t", $filter_params);
    $count = $wpdb->get_var($count_query);
    if ($count > $row_count) {
        $paginate = true;
    } else {
        $paginate = false;
    }
    $pages = ceil($count / $row_count);
    if ($paged > $pages) {
        $paged = $pages;
    }
    if ($paged != 0) {
        $offset = ($paged - 1) * $row_count;
    }
    $query = $wpdb->prepare("\n\t\tSELECT r.*, a.affiliate_id, a.name \n\t\tFROM {$referrals_table} r\n\t\tLEFT JOIN {$affiliates_table} a ON r.affiliate_id = a.affiliate_id\n\t\tLEFT JOIN {$posts_table} p ON r.post_id = p.ID\n\t\t{$filters}\n\t\tORDER BY {$orderby} {$order}\n\t\tLIMIT {$row_count} OFFSET {$offset}\n\t\t", $filter_params + $filter_params);
    $results = $wpdb->get_results($query, OBJECT);
    $column_display_names = array('datetime' => __('Date', AFFILIATES_PLUGIN_DOMAIN), 'post_title' => __('Post', AFFILIATES_PLUGIN_DOMAIN), 'name' => __('Affiliate', AFFILIATES_PLUGIN_DOMAIN), 'amount' => __('Amount', AFFILIATES_PLUGIN_DOMAIN), 'currency_id' => __('Currency', AFFILIATES_PLUGIN_DOMAIN), 'status' => __('Status', AFFILIATES_PLUGIN_DOMAIN), 'edit' => __('', AFFILIATES_PLUGIN_DOMAIN), 'remove' => __('', AFFILIATES_PLUGIN_DOMAIN));
    $column_count = count($column_display_names);
    $output .= '<div id="referrals-overview" class="referrals-overview">';
    $affiliates = affiliates_get_affiliates(true, !$show_inoperative);
    $affiliates_select = '';
    if (!empty($affiliates)) {
        $affiliates_select .= '<label class="affiliate-id-filter" for="affiliate_id">' . __('Affiliate', AFFILIATES_PLUGIN_DOMAIN) . '</label>';
        $affiliates_select .= '<select class="affiliate-id-filter" name="affiliate_id">';
        $affiliates_select .= '<option value="">--</option>';
        foreach ($affiliates as $affiliate) {
            if ($affiliate_id == $affiliate['affiliate_id']) {
                $selected = ' selected="selected" ';
            } else {
                $selected = '';
            }
            $affiliates_select .= '<option ' . $selected . ' value="' . esc_attr($affiliate['affiliate_id']) . '">' . esc_attr(stripslashes($affiliate['name'])) . '</option>';
        }
        $affiliates_select .= '</select>';
    }
    $status_descriptions = array(AFFILIATES_REFERRAL_STATUS_ACCEPTED => __('Accepted', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_CLOSED => __('Closed', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_PENDING => __('Pending', AFFILIATES_PLUGIN_DOMAIN), AFFILIATES_REFERRAL_STATUS_REJECTED => __('Rejected', AFFILIATES_PLUGIN_DOMAIN));
    $status_icons = array(AFFILIATES_REFERRAL_STATUS_ACCEPTED => "<img class='icon' alt='" . __('Accepted', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/accepted.png'/>", AFFILIATES_REFERRAL_STATUS_CLOSED => "<img class='icon' alt='" . __('Closed', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/closed.png'/>", AFFILIATES_REFERRAL_STATUS_PENDING => "<img class='icon' alt='" . __('Pending', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/pending.png'/>", AFFILIATES_REFERRAL_STATUS_REJECTED => "<img class='icon' alt='" . __('Rejected', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/rejected.png'/>");
    $status_select = '<label class="status-filter" for="status">' . __('Status', AFFILIATES_PLUGIN_DOMAIN) . '</label>';
    $status_select .= '<select name="status">';
    $status_select .= '<option value="" ' . (empty($status) ? ' selected="selected" ' : '') . '>--</option>';
    foreach ($status_descriptions as $key => $label) {
        $selected = $key == $status ? ' selected="selected" ' : '';
        $status_select .= '<option ' . $selected . ' value="' . esc_attr($key) . '">' . $label . '</option>';
    }
    $status_select .= '</select>';
    $output .= '<div class="filters">' . '<label class="description" for="setfilters">' . __('Filters', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<form id="setfilters" action="" method="post">' . '<p>' . $affiliates_select . $status_select . ' <label class="search-filter" for="search" title="Search in data">' . __('Search', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . ' <input class="search-filter" name="search" type="text" value="' . esc_attr($search) . '"/>' . ' ' . sprintf('<label class="search-description-filter" title="%s">', __('Also search in descriptions', AFFILIATES_PLUGIN_DOMAIN)) . '<input class="search-description-filter" name="search_description" type="checkbox" ' . ($search_description ? 'checked="checked"' : '') . '/>' . ' ' . __('Descriptions', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '</p>
				<p>' . '<label class="from-date-filter" for="from_date">' . __('From', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input class="datefield from-date-filter" name="from_date" type="text" value="' . esc_attr($from_date) . '"/>' . '<label class="thru-date-filter" for="thru_date">' . __('Until', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input class="datefield thru-date-filter" name="thru_date" type="text" class="datefield" value="' . esc_attr($thru_date) . '"/>' . '</p>
				<p>' . wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_FILTER_NONCE, true, false) . '<input class="button" type="submit" value="' . __('Apply', AFFILIATES_PLUGIN_DOMAIN) . '"/>' . '<label class="expanded-filter">' . '<input class="expanded-filter" name="expanded" type="checkbox" ' . ($expanded ? 'checked="checked"' : '') . '/>' . ' ' . __('Expand details', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<label class="expanded-filter">' . '<input class="expanded-filter" name="expanded_description" type="checkbox" ' . ($expanded_description ? 'checked="checked"' : '') . '/>' . ' ' . __('Expand descriptions', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<label class="expanded-filter">' . '<input class="expanded-filter" name="expanded_data" type="checkbox" ' . ($expanded_data ? 'checked="checked"' : '') . '/>' . ' ' . __('Expand data', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<label class="show-inoperative-filter">' . '<input class="show-inoperative-filter" name="show_inoperative" type="checkbox" ' . ($show_inoperative ? 'checked="checked"' : '') . '/>' . ' ' . __('Include inoperative affiliates', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input class="button" type="submit" name="clear_filters" value="' . __('Clear', AFFILIATES_PLUGIN_DOMAIN) . '"/>' . '<input type="hidden" value="submitted" name="submitted"/>' . '</p>' . '</form>' . '</div>';
    $output .= '
		<div class="page-options">
			<form id="setrowcount" action="" method="post">
				<div>
					<label for="row_count">' . __('Results per page', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input name="row_count" type="text" size="2" value="' . esc_attr($row_count) . '" />
					' . wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_NONCE_1, true, false) . '
					<input class="button" type="submit" value="' . __('Apply', AFFILIATES_PLUGIN_DOMAIN) . '"/>
				</div>
			</form>
		</div>
		';
    if ($paginate) {
        require_once AFFILIATES_CORE_LIB . '/class-affiliates-pagination.php';
        $pagination = new Affiliates_Pagination($count, null, $row_count);
        $output .= '<form id="posts-filter" method="post" action="">';
        $output .= '<div>';
        $output .= wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_NONCE_2, true, false);
        $output .= '</div>';
        $output .= '<div class="tablenav top">';
        $output .= $pagination->pagination('top');
        $output .= '</div>';
        $output .= '</form>';
    }
    $output .= '
		<table id="referrals" class="referrals wp-list-table widefat fixed" cellspacing="0">
		<thead>
			<tr>
			';
    foreach ($column_display_names as $key => $column_display_name) {
        $options = array('orderby' => $key, 'order' => $switch_order);
        $class = "";
        if (strcmp($key, $orderby) == 0) {
            $lorder = strtolower($order);
            $class = "{$key} manage-column sorted {$lorder}";
        } else {
            $class = "{$key} manage-column sortable";
        }
        $column_display_name = '<a href="' . esc_url(add_query_arg($options, $current_url)) . '"><span>' . $column_display_name . '</span><span class="sorting-indicator"></span></a>';
        $output .= "<th scope='col' class='{$class}'>{$column_display_name}</th>";
    }
    $output .= '</tr>
		</thead>
		<tbody>
		';
    if (count($results) > 0) {
        for ($i = 0; $i < count($results); $i++) {
            $result = $results[$i];
            $output .= '<tr class="details-referrals ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
            $output .= '<td class="datetime">' . DateHelper::s2u($result->datetime) . '</td>';
            $link = get_permalink($result->post_id);
            $title = get_the_title($result->post_id);
            $output .= '<td class="post_title"><a href="' . esc_attr($link) . '" target="_blank">' . wp_filter_nohtml_kses($title) . '</a></td>';
            $output .= "<td class='name'>" . stripslashes(wp_filter_nohtml_kses($result->name)) . "</td>";
            $output .= "<td class='amount'>" . stripslashes(wp_filter_nohtml_kses($result->amount)) . "</td>";
            $output .= "<td class='currency_id'>" . stripslashes(wp_filter_nohtml_kses($result->currency_id)) . "</td>";
            $output .= "<td class='status'>";
            $output .= isset($status_icons[$result->status]) ? $status_icons[$result->status] : '';
            $output .= "<form method='post' action=''>";
            $output .= "<div>";
            $output .= "<select name='status'>";
            foreach ($status_descriptions as $status_key => $status_value) {
                if ($status_key == $result->status) {
                    $selected = "selected='selected'";
                } else {
                    $selected = "";
                }
                $output .= "<option value='{$status_key}' {$selected}>{$status_value}</option>";
            }
            $output .= "</select>";
            $output .= '<input class="button" type="submit" value="' . __('Set', AFFILIATES_PLUGIN_DOMAIN) . '"/>';
            $output .= '<input name="affiliate_id" type="hidden" value="' . esc_attr($result->affiliate_id) . '"/>';
            $output .= '<input name="post_id" type="hidden" value="' . esc_attr($result->post_id) . '"/>';
            $output .= '<input name="datetime" type="hidden" value="' . esc_attr($result->datetime) . '"/>';
            $output .= '<input name="action" type="hidden" value="set_status"/>';
            $output .= wp_nonce_field('admin', AFFILIATES_ADMIN_HITS_FILTER_NONCE, true, false);
            $output .= "</div>";
            $output .= "</form>";
            $output .= "</td>";
            $output .= '<td class="edit">';
            $edit_url = add_query_arg('referral_id', $result->referral_id, add_query_arg('action', 'edit', $current_url));
            $output .= sprintf('<a href="%s">', esc_url(add_query_arg('paged', $paged, $edit_url)));
            $output .= sprintf('<img src="%s" alt="%s"/>', AFFILIATES_PLUGIN_URL . 'images/edit.png', __('Edit', AFFILIATES_PLUGIN_DOMAIN));
            $output .= '</a>';
            $output .= '</td>';
            $output .= '<td class="remove">';
            $remove_url = add_query_arg('referral_id', $result->referral_id, add_query_arg('action', 'remove', $current_url));
            $output .= sprintf('<a href="%s">', esc_url(add_query_arg('paged', $paged, $remove_url)));
            $output .= sprintf('<img src="%s" alt="%s"/>', AFFILIATES_PLUGIN_URL . 'images/remove.png', __('Remove', AFFILIATES_PLUGIN_DOMAIN));
            $output .= '</a>';
            $output .= '</td>';
            $output .= '</tr>';
            $data = $result->data;
            if (!empty($data) && $expanded) {
                if ($expanded_data) {
                    $data_view_style = '';
                    $expander = AFFILIATES_EXPANDER_RETRACT;
                } else {
                    $data_view_style = ' style="display:none;" ';
                    $expander = AFFILIATES_EXPANDER_EXPAND;
                }
                $data = unserialize($data);
                if ($data) {
                    $output .= '<tr class="data ' . ($i % 2 == 0 ? 'even' : 'odd') . '">';
                    $output .= "<td colspan='{$column_count}'>";
                    $output .= '<div class="view-toggle">';
                    $output .= "<div class='expander'>{$expander}</div>";
                    $output .= '<div class="view-toggle-label">' . __('Data', AFFILIATES_PLUGIN_DOMAIN) . '</div>';
                    $output .= "<div class='view' {$data_view_style}>";
                    $output .= '<table class="referral-data wp-list-table widefat fixed" cellspacing="0">';
                    if (is_array($data)) {
                        foreach ($data as $key => $info) {
                            $title = __($info['title'], $info['domain']);
                            $value = $info['value'];
                            $output .= "<tr id='referral-data-{$i}'>";
                            $output .= '<td class="referral-data-title">';
                            $output .= stripslashes(wp_filter_nohtml_kses($title));
                            $output .= '</td>';
                            $output .= '<td class="referral-data-value">';
                            // @todo revise
                            // $output .= wp_filter_nohtml_kses( $value );
                            $output .= stripslashes($value);
                            $output .= '</td>';
                            $output .= '</tr>';
                        }
                    } else {
                        $output .= "<tr id='referral-data-{$i}'>";
                        $output .= '<td class="referral-data-title">';
                        $output .= __('Data', AFFILIATES_PLUGIN_DOMAIN);
                        $output .= '</td>';
                        $output .= '<td class="referral-data-value">';
                        // @todo revise
                        //$output .= wp_filter_nohtml_kses( $data );
                        $output .= stripslashes($value);
                        $output .= '</td>';
                        $output .= '</tr>';
                    }
                    $output .= '</table>';
                    $output .= '</div>';
                    // .view
                    $output .= '</div>';
                    // .view-toggle
                    $output .= '</td>';
                    $output .= '</tr>';
                }
            }
            if (!empty($result->description) && $expanded) {
                if ($expanded_description) {
                    $description_view_style = '';
                    $expander = AFFILIATES_EXPANDER_RETRACT;
                } else {
                    $description_view_style = ' style="display:none;" ';
                    $expander = AFFILIATES_EXPANDER_EXPAND;
                }
                $output .= sprintf("<tr id='referral-description-%d' class='%s'>", $i, $i % 2 == 0 ? 'even' : 'odd') . '<td colspan="' . $column_count . '">' . '<div class="view-toggle">' . "<div class='expander'>{$expander}</div>" . '<div class="view-toggle-label">' . __('Description', AFFILIATES_PLUGIN_DOMAIN) . '</div>' . "<div class='view' {$description_view_style}>" . wp_filter_kses(addslashes($result->description)) . '</div>' . '</div>' . '</td>' . '</tr>';
            }
        }
    } else {
        $output .= '<tr><td colspan="' . $column_count . '">' . __('There are no results.', AFFILIATES_PLUGIN_DOMAIN) . '</td></tr>';
    }
    $output .= '</tbody>';
    $output .= '</table>';
    if ($paginate) {
        require_once AFFILIATES_CORE_LIB . '/class-affiliates-pagination.php';
        $pagination = new Affiliates_Pagination($count, null, $row_count);
        $output .= '<div class="tablenav bottom">';
        $output .= $pagination->pagination('bottom');
        $output .= '</div>';
    }
    $output .= '</div>';
    // .referrals-overview
    echo $output;
    affiliates_footer();
}