/**
  * 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();
}
/**
 * Affiliate table and action handling.
 */
function affiliates_admin_affiliates()
{
    global $wpdb, $wp_rewrite, $affiliates_options;
    $output = '';
    $today = date('Y-m-d', time());
    $pname = get_option('aff_pname', AFFILIATES_PNAME);
    if (!current_user_can(AFFILIATES_ADMINISTER_AFFILIATES)) {
        wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
    }
    if (!$wp_rewrite->using_permalinks()) {
        $output .= '<p class="warning">' . sprintf(__('Your site is not using pretty <a href="%s">permalinks</a>. You will only be able to use URL parameter based <span class="affiliate-link">affiliate links</span> but not pretty <span class="affiliate-permalink">affiliate permalinks</span>, unless you change your permalink settings.', AFFILIATES_PLUGIN_DOMAIN), get_admin_url(null, 'options-permalink.php')) . '</p>';
    }
    //
    // handle actions
    //
    if (isset($_POST['action'])) {
        //  handle action submit - do it
        switch ($_POST['action']) {
            case 'add':
                if (!affiliates_admin_affiliates_add_submit()) {
                    return affiliates_admin_affiliates_add();
                }
                break;
            case 'edit':
                if (!affiliates_admin_affiliates_edit_submit()) {
                    return affiliates_admin_affiliates_edit($_POST['affiliate-id-field']);
                }
                break;
            case 'remove':
                affiliates_admin_affiliates_remove_submit();
                break;
        }
    } else {
        if (isset($_GET['action'])) {
            // handle action request - show form
            switch ($_GET['action']) {
                case 'add':
                    return affiliates_admin_affiliates_add();
                    break;
                case 'edit':
                    if (isset($_GET['affiliate_id'])) {
                        return affiliates_admin_affiliates_edit($_GET['affiliate_id']);
                    }
                    break;
                case 'remove':
                    if (isset($_GET['affiliate_id'])) {
                        return affiliates_admin_affiliates_remove($_GET['affiliate_id']);
                    }
                    break;
            }
        }
    }
    //
    // affiliate table
    //
    if (isset($_POST['from_date']) || isset($_POST['thru_date']) || isset($_POST['clear_filters']) || isset($_POST['affiliate_id']) || isset($_POST['affiliate_name']) || isset($_POST['affiliates_email']) || isset($_POST['affiliate_user_login']) || isset($_POST['show_deleted']) || isset($_POST['show_inoperative'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_AFFILIATES_FILTER_NONCE], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    // filters
    $from_date = $affiliates_options->get_option('affiliates_from_date', null);
    $from_datetime = null;
    $thru_date = $affiliates_options->get_option('affiliates_thru_date', null);
    $thru_datetime = null;
    $affiliate_id = $affiliates_options->get_option('affiliates_affiliate_id', null);
    $affiliate_name = $affiliates_options->get_option('affiliates_affiliate_name', null);
    $affiliate_email = $affiliates_options->get_option('affiliates_affiliate_email', null);
    $affiliate_user_login = $affiliates_options->get_option('affiliates_affiliate_user_login', null);
    $show_deleted = $affiliates_options->get_option('affiliates_show_deleted', false);
    $show_inoperative = $affiliates_options->get_option('affiliates_show_inoperative', false);
    if (isset($_POST['clear_filters'])) {
        $affiliates_options->delete_option('affiliates_from_date');
        $affiliates_options->delete_option('affiliates_thru_date');
        $affiliates_options->delete_option('affiliates_affiliate_id');
        $affiliates_options->delete_option('affiliates_affiliate_name');
        $affiliates_options->delete_option('affiliates_affiliate_email');
        $affiliates_options->delete_option('affiliates_affiliate_user_login');
        $affiliates_options->delete_option('affiliates_show_deleted');
        $affiliates_options->delete_option('affiliates_show_inoperative');
        $from_date = null;
        $from_datetime = null;
        $thru_date = null;
        $thru_datetime = null;
        $affiliate_id = null;
        $affiliate_name = null;
        $affiliate_email = null;
        $affiliate_user_login = null;
        $show_deleted = false;
        $show_inoperative = false;
    } else {
        if (isset($_POST['submitted'])) {
            if (!empty($_POST['affiliate_name'])) {
                $affiliate_name = trim($_POST['affiliate_name']);
                if (strlen($affiliate_name) > 0) {
                    $affiliates_options->update_option('affiliates_affiliate_name', $affiliate_name);
                } else {
                    $affiliate_name = null;
                    $affiliates_options->delete_option('affiliates_affiliate_name');
                }
            } else {
                $affiliate_name = null;
                $affiliates_options->delete_option('affiliates_affiliate_name');
            }
            if (!empty($_POST['affiliate_email'])) {
                $affiliate_email = trim($_POST['affiliate_email']);
                if (strlen($affiliate_email) > 0) {
                    $affiliates_options->update_option('affiliates_affiliate_email', $affiliate_email);
                } else {
                    $affiliate_email = null;
                    $affiliates_options->delete_option('affiliates_affiliate_email');
                }
            } else {
                $affiliate_email = null;
                $affiliates_options->delete_option('affiliates_affiliate_email');
            }
            if (!empty($_POST['affiliate_user_login'])) {
                $affiliate_user_login = trim($_POST['affiliate_user_login']);
                if (strlen($affiliate_user_login) > 0) {
                    $affiliates_options->update_option('affiliates_affiliate_user_login', $affiliate_user_login);
                } else {
                    $affiliate_user_login = null;
                    $affiliates_options->delete_option('affiliates_affiliate_user_login');
                }
            } else {
                $affiliate_user_login = null;
                $affiliates_options->delete_option('affiliates_affiliate_user_login');
            }
            $show_deleted = isset($_POST['show_deleted']);
            $affiliates_options->update_option('affiliates_show_deleted', $show_deleted);
            $show_inoperative = isset($_POST['show_inoperative']);
            $affiliates_options->update_option('affiliates_show_inoperative', $show_inoperative);
            // filter by date(s)
            if (!empty($_POST['from_date'])) {
                $from_date = date('Y-m-d', strtotime($_POST['from_date']));
                $affiliates_options->update_option('affiliates_from_date', $from_date);
            } else {
                $from_date = null;
                $affiliates_options->delete_option('affiliates_from_date');
            }
            if (!empty($_POST['thru_date'])) {
                $thru_date = date('Y-m-d', strtotime($_POST['thru_date']));
                $affiliates_options->update_option('affiliates_thru_date', $thru_date);
            } else {
                $thru_date = null;
                $affiliates_options->delete_option('affiliates_thru_date');
            }
            if ($from_date && $thru_date) {
                if (strtotime($from_date) > strtotime($thru_date)) {
                    $thru_date = null;
                    $affiliates_options->delete_option('affiliates_thru_date');
                }
            }
            // 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);
            }
            // 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('affiliates_affiliate_id', $affiliate_id);
                }
            } else {
                if (isset($_POST['affiliate_id'])) {
                    // empty && isset => '' => all
                    $affiliate_id = null;
                    $affiliates_options->delete_option('affiliates_affiliate_id');
                }
            }
        }
    }
    if (isset($_POST['row_count'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_AFFILIATES_NONCE_1], 'admin')) {
            wp_die(__('Access denied.', AFFILIATES_PLUGIN_DOMAIN));
        }
    }
    if (isset($_POST['paged'])) {
        if (!wp_verify_nonce($_POST[AFFILIATES_ADMIN_AFFILIATES_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);
    $current_url = remove_query_arg('action', $current_url);
    $current_url = remove_query_arg('affiliate_id', $current_url);
    $affiliates_table = _affiliates_get_tablename('affiliates');
    $affiliates_users_table = _affiliates_get_tablename('affiliates_users');
    $output .= '<div class="manage-affiliates">' . '<div>' . '<h2>' . __('Manage Affiliates', AFFILIATES_PLUGIN_DOMAIN) . '</h2>' . '</div>';
    $output .= '<div class="manage">' . "<a title='" . __('Click to add a new affiliate', AFFILIATES_PLUGIN_DOMAIN) . "' class='button add' href='" . esc_url($current_url) . "&action=add'><img class='icon' alt='" . __('Add', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/add.png'/><span class='label'>" . __('New Affiliate', AFFILIATES_PLUGIN_DOMAIN) . "</span></a>" . '</div>';
    $row_count = isset($_POST['row_count']) ? intval($_POST['row_count']) : 0;
    if ($row_count <= 0) {
        $row_count = $affiliates_options->get_option('affiliates_per_page', AFFILIATES_AFFILIATES_PER_PAGE);
    } else {
        $affiliates_options->update_option('affiliates_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 'from_date':
        case 'thru_date':
        case 'email':
        case 'affiliate_id':
        case 'name':
        case 'user_login':
            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';
    }
    //	if ( $from_date || $thru_date || $affiliate_id ) {
    //		$filters = " WHERE ";
    //	} else {
    //		$filters = '';
    //	}
    $filters = array();
    $filter_params = array();
    if ($affiliate_id) {
        $filters[] = " {$affiliates_table}.affiliate_id = %d ";
        $filter_params[] = $affiliate_id;
    }
    if ($affiliate_name) {
        $filters[] = " {$affiliates_table}.name LIKE '%%%s%%' ";
        $filter_params[] = $affiliate_name;
    }
    if ($affiliate_email) {
        $filters[] = " {$affiliates_table}.email LIKE '%%%s%%' ";
        $filter_params[] = $affiliate_email;
    }
    if ($affiliate_user_login) {
        $filters[] = " {$wpdb->users}.user_login LIKE '%%%s%%' ";
        $filter_params[] = $affiliate_user_login;
    }
    if (!$show_deleted) {
        $filters[] = " {$affiliates_table}.status = %s ";
        $filter_params[] = 'active';
    }
    if (!$show_inoperative) {
        $filters[] = " {$affiliates_table}.from_date <= %s AND ( {$affiliates_table}.thru_date IS NULL OR {$affiliates_table}.thru_date >= %s ) ";
        $filter_params[] = $today;
        $filter_params[] = $today;
    }
    if ($from_datetime && $thru_datetime) {
        $filters[] = " {$affiliates_table}.from_date >= %s AND ( {$affiliates_table}.thru_date IS NULL OR {$affiliates_table}.thru_date < %s ) ";
        $filter_params[] = $from_datetime;
        $filter_params[] = $thru_datetime;
    } else {
        if ($from_datetime) {
            $filters[] = " {$affiliates_table}.from_date >= %s ";
            $filter_params[] = $from_datetime;
        } else {
            if ($thru_datetime) {
                $filters[] = " {$affiliates_table}.thru_date < %s ";
                $filter_params[] = $thru_datetime;
            }
        }
    }
    if (!empty($filters)) {
        $filters = " WHERE " . implode(" AND ", $filters);
    } else {
        $filters = '';
    }
    $count_query = $wpdb->prepare("SELECT COUNT(*) FROM {$affiliates_table} LEFT JOIN {$affiliates_users_table} ON {$affiliates_table}.affiliate_id = {$affiliates_users_table}.affiliate_id LEFT JOIN {$wpdb->users} on {$affiliates_users_table}.user_id = {$wpdb->users}.ID {$filters}", $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("SELECT {$affiliates_table}.*, {$wpdb->users}.* FROM {$affiliates_table} LEFT JOIN {$affiliates_users_table} ON {$affiliates_table}.affiliate_id = {$affiliates_users_table}.affiliate_id LEFT JOIN {$wpdb->users} on {$affiliates_users_table}.user_id = {$wpdb->users}.ID {$filters} ORDER BY {$orderby} {$order} LIMIT {$row_count} OFFSET {$offset}", $filter_params);
    $results = $wpdb->get_results($query, OBJECT);
    $column_display_names = array('affiliate_id' => __('Id', AFFILIATES_PLUGIN_DOMAIN), 'name' => __('Affiliate', AFFILIATES_PLUGIN_DOMAIN), 'email' => __('Email', AFFILIATES_PLUGIN_DOMAIN), 'user_login' => __('Username', AFFILIATES_PLUGIN_DOMAIN), 'from_date' => __('From', AFFILIATES_PLUGIN_DOMAIN), 'thru_date' => __('Until', AFFILIATES_PLUGIN_DOMAIN), 'edit' => __('Edit', AFFILIATES_PLUGIN_DOMAIN), 'remove' => __('Remove', AFFILIATES_PLUGIN_DOMAIN), 'links' => __('Links', AFFILIATES_PLUGIN_DOMAIN));
    $output .= '<div class="affiliates-overview">';
    $output .= '<div class="filters">' . '<label class="description" for="setfilters">' . __('Filters', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<form id="setfilters" action="" method="post">' . '<p>' . '<label class="affiliate-id-filter" for="affiliate_id">' . __('Affiliate Id', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input class="affiliate-id-filter" name="affiliate_id" type="text" value="' . esc_attr($affiliate_id) . '"/>' . '<label class="affiliate-name-filter" for="affiliate_name">' . __('Affiliate Name', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input class="affiliate-name-filter" name="affiliate_name" type="text" value="' . $affiliate_name . '"/>' . '<label class="affiliate-email-filter" for="affiliate_email">' . __('Affiliate Email', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input class="affiliate-email-filter" name="affiliate_email" type="text" value="' . $affiliate_email . '"/>' . '<label class="affiliate-user-login-filter" for="affiliate_user_login">' . __('Affiliate Username', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '<input class="affiliate-user-login-filter" name="affiliate_user_login" type="text" value="' . $affiliate_user_login . '" />' . '</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) . '"/>' . '<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>' . '<label class="show-deleted-filter">' . '<input class="show-deleted-filter" name="show_deleted" type="checkbox" ' . ($show_deleted ? 'checked="checked"' : '') . '/>' . ' ' . __('Include removed affiliates', AFFILIATES_PLUGIN_DOMAIN) . '</label>' . '</p>
				<p>' . wp_nonce_field('admin', AFFILIATES_ADMIN_AFFILIATES_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"/>' . '</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_AFFILIATES_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_AFFILIATES_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 = $key;
        if (!in_array($key, array('edit', 'remove', 'links'))) {
            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];
            $name_suffix = '';
            $class_deleted = '';
            if ($is_deleted = strcmp($result->status, 'deleted') == 0) {
                $class_deleted = ' deleted ';
                $name_suffix .= " " . __('(removed)', AFFILIATES_PLUGIN_DOMAIN);
            }
            $class_inoperative = '';
            if ($is_inoperative = !($result->from_date <= $today && ($result->thru_date == null || $result->thru_date >= $today))) {
                $class_inoperative = ' inoperative ';
                $name_suffix .= " " . __('(inoperative)', AFFILIATES_PLUGIN_DOMAIN);
            }
            $output .= '<tr class="' . $class_deleted . $class_inoperative . ($i % 2 == 0 ? 'even' : 'odd') . '">';
            $output .= "<td class='affiliate-id'>";
            if (affiliates_encode_affiliate_id($result->affiliate_id) != $result->affiliate_id) {
                $output .= '<span class="encoded-hint" title="' . affiliates_encode_affiliate_id($result->affiliate_id) . '">' . $result->affiliate_id . '</span>';
            } else {
                $output .= $result->affiliate_id;
            }
            $output .= "</td>";
            $output .= "<td class='affiliate-name'>" . stripslashes(wp_filter_nohtml_kses($result->name)) . $name_suffix . "</td>";
            $output .= "<td class='affiliate-email'>" . $result->email;
            if (isset($result->email) && isset($result->user_email) && strcmp($result->email, $result->user_email) !== 0) {
                $output .= '<span title="' . sprintf(__('There are different email addresses on record for the affiliate and the associated user. This might be ok, but if in doubt please check. The email address on file for the user is %s', AFFILIATES_PLUGIN_DOMAIN), $result->user_email) . '" class="warning"> [&nbsp;!&nbsp]</span>';
            }
            $output .= "</td>";
            $output .= "<td class='affiliate-user-login'>";
            if (!empty($result->ID)) {
                if (current_user_can('edit_user', $result->ID)) {
                    $output .= '<a target="_blank" href="' . esc_url("user-edit.php?user_id={$result->ID}") . '">' . $result->user_login . '</a>';
                } else {
                    $output .= $result->user_login;
                }
            }
            $output .= "</td>";
            $output .= "<td class='from-date'>{$result->from_date}</td>";
            $output .= "<td class='thru-date'>{$result->thru_date}</td>";
            $output .= "<td class='edit'><a href='" . esc_url(add_query_arg('paged', $paged, $current_url)) . "&action=edit&affiliate_id=" . $result->affiliate_id . "' alt='" . __('Edit', AFFILIATES_PLUGIN_DOMAIN) . "'><img src='" . AFFILIATES_PLUGIN_URL . "images/edit.png'/></a></td>";
            $output .= "<td class='remove'>" . (!$is_deleted && (!isset($result->type) || $result->type != AFFILIATES_DIRECT_TYPE) ? "<a href='" . esc_url($current_url) . "&action=remove&affiliate_id=" . $result->affiliate_id . "' alt='" . __('Remove', AFFILIATES_PLUGIN_DOMAIN) . "'><img src='" . AFFILIATES_PLUGIN_URL . "images/remove.png'/></a>" : "") . "</td>";
            $output .= "<td class='links'>";
            $encoded_id = affiliates_encode_affiliate_id($result->affiliate_id);
            $output .= __('Link', AFFILIATES_PLUGIN_DOMAIN) . ': ' . '<span class="affiliate-link">' . get_bloginfo('url') . '?' . $pname . '=' . $encoded_id . '</span>' . ' &nbsp; ' . __('URL Parameter', AFFILIATES_PLUGIN_DOMAIN) . ': ' . '<span class="affiliate-link-param">' . '?' . $pname . '=' . $encoded_id . '</span>' . '<br/>' . __('Pretty', AFFILIATES_PLUGIN_DOMAIN) . ': ' . '<span class="affiliate-permalink">' . get_bloginfo('url') . '/' . $pname . '/' . $encoded_id . '</span>' . ($wp_rewrite->using_permalinks() ? '' : '<span class="warning">' . '&nbsp;' . sprintf(__('you need to adjust your <a href="%s">permalink settings</a>.', AFFILIATES_PLUGIN_DOMAIN), get_admin_url(null, 'options-permalink.php')) . '</span>');
            $output .= "</td>";
            $output .= '</tr>';
        }
    } else {
        $output .= '<tr><td colspan="10">' . __('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>';
    // .affiliates-overview
    $output .= '</div>';
    // .manage-affiliates
    echo $output;
    affiliates_footer();
}
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();
}
 public static function update_status($new_status, $params = null)
 {
     global $wpdb;
     $output = "";
     $from_date = isset($params['from_date']) ? $params['from_date'] : null;
     $from_datetime = $from_date ? DateHelper::u2s($from_date) : null;
     $thru_date = isset($params['thru_date']) ? $params['thru_date'] : null;
     $thru_datetime = $thru_date ? DateHelper::u2s($thru_date, 24 * 3600) : null;
     $referral_status = isset($params['referral_status']) ? Affiliates_Utility::verify_referral_status_transition($params['referral_status'], $params['referral_status']) : null;
     $currency_id = isset($params['currency_id']) ? Affiliates_Utility::verify_currency_id($params['currency_id']) : null;
     $orderby = isset($params['orderby']) ? $params['orderby'] : null;
     $order = isset($params['order']) ? $params['order'] : null;
     switch ($orderby) {
         case 'affiliate_id':
         case 'name':
         case 'email':
             $orderby = 'a.' . $orderby;
             break;
         case 'user_login':
             $orderby = 'au.' . $orderby;
             break;
         case 'currency_id':
             $orderby = 'r.' . $orderby;
             break;
         default:
             $orderby = 'a.name';
     }
     switch ($order) {
         case 'asc':
         case 'ASC':
         case 'desc':
         case 'DESC':
             break;
         default:
             $order = 'ASC';
     }
     if (isset($params['tables'])) {
         $output .= "<h1>" . __("Closing referrals", AFFILIATES_PLUGIN_DOMAIN) . "</h1>";
         $output .= "<div class='closing-referrals-overview'>";
         $affiliates_table = $params['tables']['affiliates'];
         $affiliates_users_table = $params['tables']['affiliates_users'];
         $referrals_table = $params['tables']['referrals'];
         $users_table = $params['tables']['users'];
         $filters = array(" 1=%d ");
         $filter_params = array(1);
         if ($from_datetime && $thru_datetime) {
             $filters[] = " r.datetime >= %s AND r.datetime < %s ";
             $filter_params[] = $from_datetime;
             $filter_params[] = $thru_datetime;
         } else {
             if ($from_datetime) {
                 $filters[] = " r.datetime >= %s ";
                 $filter_params[] = $from_datetime;
             } else {
                 if ($thru_datetime) {
                     $filters[] = " r.datetime < %s ";
                     $filter_params[] = $thru_datetime;
                 }
             }
         }
         if ($referral_status) {
             $filters[] = " r.status = %s ";
             $filter_params[] = $referral_status;
         }
         if ($currency_id) {
             $filters[] = " r.currency_id = %s ";
             $filter_params[] = $currency_id;
         }
         if (!empty($filters)) {
             $filters = " WHERE " . implode(" AND ", $filters);
         } else {
             $filters = '';
         }
         $order_by = '';
         if ($orderby && $order) {
             $order_by .= " ORDER BY {$orderby} {$order} ";
         }
         $step = isset($params['step']) ? intval($params['step']) : 1;
         switch ($step) {
             case 1:
                 $results = $wpdb->get_results($wpdb->prepare("\n\t\t\t\t\t\tSELECT a.*, r.*, u.user_login\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\tLEFT JOIN {$affiliates_users_table} au ON a.affiliate_id = au.affiliate_id\n\t\t\t\t\t\tLEFT JOIN {$users_table} u on au.user_id = u.ID\n\t\t\t\t\t\t{$filters}\n\t\t\t\t\t\t{$order_by}\n\t\t\t\t\t\t", $filter_params));
                 $output .= "<div class='manage'>";
                 $output .= "<div class='warning'>";
                 $output .= "<p>";
                 $output .= "<strong>";
                 $output .= __("Please review the list of referrals that will be <em>closed</em>.", AFFILIATES_PLUGIN_DOMAIN);
                 $output .= "</strong>";
                 $output .= "</p>";
                 $output .= "</div>";
                 // .warning
                 $output .= "<p>";
                 $output .= __("Usually only referrals that are <em>accepted</em> and have been paid out should be <em>closed</em>. If there are unwanted or too many referrals shown, restrict your filter settings.", AFFILIATES_PLUGIN_DOMAIN);
                 $output .= "</p>";
                 $output .= "<p>";
                 $output .= __("If these referrals can be closed, click the confirmation button below.", AFFILIATES_PLUGIN_DOMAIN);
                 $output .= "</p>";
                 $output .= "</div>";
                 $output .= '<div id="referrals-overview" class="referrals-overview">';
                 $output .= self::render_results($results);
                 $output .= '</div>';
                 // .referrals-overview
                 if (count($results > 0)) {
                     $mp_params = "";
                     if (!empty($from_date)) {
                         $mp_params .= "&from_date=" . urlencode($from_date);
                     }
                     if (!empty($thru_date)) {
                         $mp_params .= "&thru_date=" . urlencode($thru_date);
                     }
                     if (!empty($referral_status)) {
                         $mp_params .= "&referral_status=" . urlencode($referral_status);
                     }
                     if (!empty($currency_id)) {
                         $mp_params .= "&currency_id=" . urlencode($currency_id);
                     }
                     if (!empty($orderby)) {
                         $mp_params .= "&orderby=" . urlencode($orderby);
                     }
                     if (!empty($order)) {
                         $mp_params .= "&order=" . urlencode($order);
                     }
                     $output .= '<div class="manage confirm">';
                     $current_url = (is_ssl() ? 'https://' : 'http://') . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
                     $current_url = remove_query_arg('paged', $current_url);
                     $current_url = remove_query_arg('action', $current_url);
                     $current_url = remove_query_arg('affiliate_id', $current_url);
                     $output .= '<style type="text/css">';
                     $output .= '.close-referrals img, .close-referrals .label { vertical-align: middle; }';
                     $output .= '</style>';
                     $output .= "<p>";
                     $output .= __("Close these referrals by clicking:", AFFILIATES_PLUGIN_DOMAIN);
                     $output .= "</p>";
                     $output .= "<a title='" . __('Click to close these referrals', AFFILIATES_PLUGIN_DOMAIN) . "' " . "class='close-referrals button' " . "href='" . esc_url($current_url) . "&action=close_referrals&step=2" . $mp_params . "'>" . "<img class='icon' alt='" . __('Close referrals', AFFILIATES_PLUGIN_DOMAIN) . "' src='" . AFFILIATES_PLUGIN_URL . "images/closed.png'/>" . "<span class='label'>" . __('Close Referrals', AFFILIATES_PLUGIN_DOMAIN) . "</span>" . "</a>";
                     $output .= "<div class='warning'>";
                     $output .= "<p>";
                     $output .= "<strong>";
                     $output .= __("This action can not be undone*.", AFFILIATES_PLUGIN_DOMAIN);
                     $output .= "</strong>";
                     $output .= "</p>";
                     $output .= "<p>";
                     $output .= "<span style='font-size:0.8em;'>";
                     $output .= __("*To undo, each referral would have to be set to the desired status individually.", AFFILIATES_PLUGIN_DOMAIN);
                     $output .= "</span>";
                     $output .= "</p>";
                     $output .= "</div>";
                     // .warning
                     $output .= '</div>';
                     // .manage.confirm
                 }
                 break;
                 // step 1 - ask for confirmation confirmation
             // step 1 - ask for confirmation confirmation
             case 2:
                 // try to make the changes
                 $results = $wpdb->get_results($wpdb->prepare("\n\t\t\t\t\t\tSELECT a.*, r.*, u.user_login\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\tLEFT JOIN {$affiliates_users_table} au ON a.affiliate_id = au.affiliate_id\n\t\t\t\t\t\tLEFT JOIN {$users_table} u on au.user_id = u.ID\n\t\t\t\t\t\t{$filters}\n\t\t\t\t\t\t{$order_by}\n\t\t\t\t\t\t", $filter_params));
                 $updated = array();
                 $omitted = array();
                 $failed = array();
                 foreach ($results as $result) {
                     if ($s = Affiliates_Utility::verify_referral_status_transition($result->status, $new_status)) {
                         if ($wpdb->query($wpdb->prepare("UPDATE {$referrals_table} SET status = %s WHERE affiliate_id = %d AND post_id = %d AND datetime = %s ", $s, $result->affiliate_id, $result->post_id, $result->datetime))) {
                             $result->status = $s;
                             $updated[] = $result;
                         } else {
                             $failed[] = $result;
                         }
                     } else {
                         $omitted[] = $result;
                     }
                 }
                 // always show at least the updated table because this will
                 // also give information if no results have been updated
                 $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));
                 $output .= "<h2>" . __("Updated", AFFILIATES_PLUGIN_DOMAIN) . "</h2>";
                 $output .= "<p>";
                 $output .= sprintf(__("These referrals have been updated to <em>%s</em>.", AFFILIATES_PLUGIN_DOMAIN), isset($status_descriptions[$new_status]) ? $status_descriptions[$new_status] : $new_status);
                 $output .= "</p>";
                 $output .= self::render_results($updated);
                 if (count($omitted) > 0) {
                     $output .= "<h2>" . __("Omitted", AFFILIATES_PLUGIN_DOMAIN) . "</h2>";
                     $output .= "<p>";
                     $output .= sprintf(__("These referrals have been omitted because their status must not be changed to <em>%s</em>.", AFFILIATES_PLUGIN_DOMAIN), isset($status_descriptions[$new_status]) ? $status_descriptions[$new_status] : $new_status);
                     $output .= "</p>";
                     $output .= self::render_results($omitted);
                 }
                 if (count($failed) > 0) {
                     $output .= "<h2>" . __("Failed", AFFILIATES_PLUGIN_DOMAIN) . "</h2>";
                     $output .= "<p>";
                     $output .= sprintf(__("These referrals could not be updated to <em>%s</em>.", AFFILIATES_PLUGIN_DOMAIN), isset($status_descriptions[$new_status]) ? $status_descriptions[$new_status] : $new_status);
                     $output .= "</p>";
                     $output .= self::render_results($failed);
                 }
                 break;
                 // step 2 -commit changes
         }
         $output .= "</div>";
         // .closing-referrals-overview
     }
     return $output;
 }
/**
 * 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();
}