';
$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 );
}
// current page
$paged = isset( $_REQUEST['paged'] ) ? intval( $_REQUEST['paged'] ) : 1;
if ( $paged < 1 ) {
$paged = 1;
}
$orderby = isset( $_GET['orderby'] ) ? $_GET['orderby'] : null;
switch ( $orderby ) {
case 'date' :
case 'visits' :
case 'hits' :
case 'referrals' :
case 'ratio' :
break;
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:
$u2s_from_date = $from_date ? date( 'Y-m-d', strtotime( DateHelper::u2s( $from_date ) ) ) : null;
$u2s_thru_date = $thru_date ? date( 'Y-m-d', strtotime( DateHelper::u2s( $thru_date ) ) ) : null;
if ( $u2s_from_date && $u2s_thru_date ) {
$filters .= " AND date >= %s AND date <= %s ";
$filter_params[] = $u2s_from_date;
$filter_params[] = $u2s_thru_date;
} else if ( $u2s_from_date ) {
$filters .= " AND date >= %s ";
$filter_params[] = $u2s_from_date;
} else if ( $u2s_thru_date ) {
$filters .= " AND date <= %s ";
$filter_params[] = $u2s_thru_date;
}
if ( $affiliate_id ) {
$filters .= " AND affiliate_id = %d ";
$filter_params[] = $affiliate_id;
}
do {
$repeat = false;
$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(
"SELECT SQL_CALC_FOUND_ROWS " .
"hits.date, " .
"hits.hits, " .
"hits.visits, " .
"IF ( referrals.count IS NOT NULL, referrals.count, 0 ) AS referrals, " .
"IF ( referrals.count IS NOT NULL AND hits.visits > 0, referrals.count / hits.visits, 0 ) AS ratio " .
"FROM " .
"( SELECT date, COUNT(DISTINCT ip) AS visits, COUNT(*) AS hits FROM $hits_table $filters GROUP BY date ) AS hits " .
"LEFT JOIN ( SELECT COUNT(*) AS count, date(datetime) AS date FROM $referrals_table GROUP BY date(datetime) ) AS referrals ON hits.date = referrals.date " .
"ORDER BY $orderby $order " .
"LIMIT $row_count OFFSET $offset",
$filter_params
);
$results = $wpdb->get_results( $query, OBJECT );
$count = intval( $wpdb->get_var( "SELECT FOUND_ROWS()" ) );
if ( $count > $row_count ) {
$paginate = true;
} else {
$paginate = false;
}
$pages = max( array( 1, ceil( $count / $row_count ) ) );
if ( $paged > $pages ) {
$paged = $pages;
$repeat = true;
}
} while ( $repeat );
$column_display_names = array(
'date' => __( 'Date', 'affiliates' ) . '*',
'visits' => __( 'Visits', 'affiliates' ),
'hits' => __( 'Hits', 'affiliates' ),
'referrals' => __( 'Referrals', 'affiliates' ),
'ratio' => __( 'Ratio', 'affiliates' )
);
$output .= '
';
$output .= sprintf(
__( "* Date is given for the server's time zone : %s, which has an offset of %s hours with respect to GMT.", 'affiliates' ),
$server_dtz->getName(),
$server_dtz->getOffset( new DateTime() ) / 3600.0
);
$output .= '
';
$output .= '
'; // .visits-overview
echo $output;
affiliates_footer();
} // function affiliates_admin_hits()