' .
'affiliates_data_cleaner_ajax_nonce = \'' . wp_create_nonce( 'affiliates-data-cleaner-ajax-nonce' ) . '\';' . // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
'';
}
/**
* Ajax wp_ajax_affiliates_data_cleaner_clean cleaner handler.
*/
public static function clean() {
global $wpdb;
if ( !current_user_can( AFFILIATES_ADMINISTER_AFFILIATES ) ) {
exit;
}
if ( check_ajax_referer( 'affiliates-data-cleaner-ajax-nonce', 'affiliates_data_cleaner_ajax_nonce' ) ) {
affiliates_request_execution_unlimited();
// delete unrelated hits beyond validity timeframe
$hits_table = _affiliates_get_tablename( 'hits' );
$uris_table = _affiliates_get_tablename( 'uris' );
$referrals_table = _affiliates_get_tablename( 'referrals' );
$user_agents_table = _affiliates_get_tablename( 'user_agents' );
$hits_rows = 0;
$uris_rows = 0;
$user_agents_rows = 0;
ob_start();
// hits
$count_hits = $wpdb->get_var( "SELECT COUNT(*) FROM $hits_table WHERE hit_id NOT IN (SELECT hit_id FROM $referrals_table WHERE hit_id IS NOT NULL)" );
if ( is_numeric( $count_hits ) ) {
$count_hits = intval( $count_hits );
if ( $count_hits > 0 ) {
// until as many days or up to an hour before
$days = get_option( 'aff_cookie_timeout_days', AFFILIATES_COOKIE_TIMEOUT_DAYS );
$days = max( 0, is_numeric( $days ) ? intval( $days ) : 0 );
$seconds = $days * AFFILIATES_COOKIE_TIMEOUT_BASE;
$seconds = max( $seconds, self::HOUR );
$timestamp = time() - $seconds;
$until = date( 'Y-m-d H:i:s', $timestamp );
$query = $wpdb->prepare(
"DELETE FROM $hits_table WHERE datetime < %s AND hit_id NOT IN (SELECT hit_id FROM $referrals_table WHERE hit_id IS NOT NULL)",
$until
);
$result = $wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
if ( $result ) {
$hits_rows = $wpdb->get_var( "SELECT ROW_COUNT()" );
}
}
}
// URIs
$count_uris = $wpdb->get_var( "SELECT COUNT(*) FROM $uris_table WHERE uri_id NOT IN (SELECT src_uri_id FROM $hits_table WHERE src_uri_id IS NOT NULL) AND uri_id NOT IN (SELECT dest_uri_id FROM $hits_table WHERE dest_uri_id IS NOT NULL)" );
if ( is_numeric( $count_uris ) ) {
$count_uris = intval( $count_uris );
if ( $count_uris > 0 ) {
$query = "DELETE FROM $uris_table WHERE uri_id NOT IN (SELECT src_uri_id FROM $hits_table WHERE src_uri_id IS NOT NULL) AND uri_id NOT IN (SELECT dest_uri_id FROM $hits_table WHERE dest_uri_id IS NOT NULL)";
$result = $wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
if ( $result ) {
$uris_rows = $wpdb->get_var( "SELECT ROW_COUNT()" );
}
}
}
// user agents
$count_user_agents = $wpdb->get_var( "SELECT COUNT(*) FROM $user_agents_table WHERE user_agent_id NOT IN (SELECT user_agent_id FROM $hits_table WHERE user_agent_id IS NOT NULL)" );
if ( is_numeric( $count_user_agents ) ) {
$count_user_agents = intval( $count_user_agents );
if ( $count_user_agents > 0 ) {
$query = "DELETE FROM $user_agents_table WHERE user_agent_id NOT IN (SELECT user_agent_id FROM $hits_table WHERE user_agent_id IS NOT NULL)";
$result = $wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
if ( $result ) {
$user_agents_rows = $wpdb->get_var( "SELECT ROW_COUNT()" );
}
}
}
$total_hits = $wpdb->get_var( "SELECT COUNT(*) FROM $hits_table" );
$total_uris = $wpdb->get_var( "SELECT COUNT(*) FROM $uris_table" );
$total_user_agents = $wpdb->get_var( "SELECT COUNT(*) FROM $user_agents_table" );
$ob = ob_get_clean();
affiliates_log_warning( $ob );
echo json_encode( array(
'count_hits' => $count_hits,
'count_uris' => $count_uris,
'count_user_agents' => $count_user_agents,
'hits' => $hits_rows,
'uris' => $uris_rows,
'user_agents' => $user_agents_rows,
'total_hits' => $total_hits,
'total_uris' => $total_uris,
'total_user_agents' => $total_user_agents
) );
}
exit;
}
public static function admin() {
global $wpdb, $affiliates_version;
if ( !current_user_can( AFFILIATES_ADMINISTER_AFFILIATES ) ) {
wp_die( esc_html__( 'Access denied.', 'affiliates' ) );
}
add_action( 'admin_footer', array( __CLASS__, 'admin_footer' ) );
wp_enqueue_script( 'affiliates-data-cleaner', AFFILIATES_PLUGIN_URL . 'js/affiliates-data-cleaner.js', array( 'jquery' ), $affiliates_version, true );
wp_localize_script(
'affiliates-data-cleaner',
'affiliates_data_cleaner',
array(
'hits_deleted' => _x( 'Hits deleted:', 'data cleaner', 'affiliates' ),
'uris_deleted' => _x( 'URIs deleted:', 'data cleaner', 'affiliates' ),
'user_agents_deleted' => _x( 'User Agents deleted:', 'data cleaner', 'affiliates' ),
'failed' => _x( 'Failed', 'data cleaner', 'affiliates' )
)
);
$action = affiliates_sanitize_request( 'action' );
if ( $action !== null && !affiliates_verify_request_nonce( 'data-cleaner-nonce', 'data-cleaner-action' ) ) {
wp_die( esc_html__( 'Access denied.', 'affiliates' ) );
}
echo '' . esc_html__( 'Data Cleaner', 'affiliates' ) . '
';
switch ( $action ) {
case null :
$hits_table = _affiliates_get_tablename( 'hits' );
$uris_table = _affiliates_get_tablename( 'uris' );
$referrals_table = _affiliates_get_tablename( 'referrals' );
$user_agents_table = _affiliates_get_tablename( 'user_agents' );
$total_hits = $wpdb->get_var( "SELECT COUNT(*) FROM $hits_table" );
$total_uris = $wpdb->get_var( "SELECT COUNT(*) FROM $uris_table" );
$total_user_agents = $wpdb->get_var( "SELECT COUNT(*) FROM $user_agents_table" );
echo '';
printf( '- %1$s —
%2$d ', esc_html__( 'Hits', 'affiliates' ), intval( $total_hits ) );
printf( '- %1$s —
%2$d ', esc_html__( 'URIs', 'affiliates' ), intval( $total_uris ) );
printf( '- %1$s —
%2$d ', esc_html__( 'User Agents', 'affiliates' ), intval( $total_user_agents ) );
echo '
';
echo '';
esc_html_e( 'Check for data that can be removed …', 'affiliates' );
echo '
';
echo '';
echo '';
break;
case 'confirm' :
$hits_table = _affiliates_get_tablename( 'hits' );
$uris_table = _affiliates_get_tablename( 'uris' );
$referrals_table = _affiliates_get_tablename( 'referrals' );
$user_agents_table = _affiliates_get_tablename( 'user_agents' );
$total_hits = $wpdb->get_var( "SELECT COUNT(*) FROM $hits_table" );
$total_uris = $wpdb->get_var( "SELECT COUNT(*) FROM $uris_table" );
$total_user_agents = $wpdb->get_var( "SELECT COUNT(*) FROM $user_agents_table" );
$count_hits = $wpdb->get_var( "SELECT COUNT(*) FROM $hits_table WHERE hit_id NOT IN (SELECT hit_id FROM $referrals_table WHERE hit_id IS NOT NULL)" );
$count_uris = $wpdb->get_var( "SELECT COUNT(*) FROM $uris_table WHERE uri_id NOT IN (SELECT src_uri_id FROM $hits_table WHERE src_uri_id IS NOT NULL) AND uri_id NOT IN (SELECT dest_uri_id FROM $hits_table WHERE dest_uri_id IS NOT NULL)" );
$count_user_agents = $wpdb->get_var( "SELECT COUNT(*) FROM $user_agents_table WHERE user_agent_id NOT IN (SELECT user_agent_id FROM $hits_table WHERE user_agent_id IS NOT NULL)" );
echo '
';
printf( '- %1$s —
%2$d / %3$d ', esc_html__( 'Hits', 'affiliates' ), intval( $count_hits ), intval( $total_hits ) );
printf( '- %1$s —
%2$d / %3$d ', esc_html__( 'URIs', 'affiliates' ), intval( $count_uris ), intval( $total_uris ) );
printf( '- %1$s —
%2$d / %3$d ', esc_html__( 'User Agents', 'affiliates' ), intval( $count_user_agents ), intval( $total_user_agents ) );
echo '
';
esc_html_e( 'If you want to delete the data, click the button to proceed.', 'affiliates' );
echo ' ';
esc_html_e( 'Once you click the button, the data will be deleted immediately.', 'affiliates' );
echo ' ';
esc_html_e( 'It can take a while to clean up a large number of data.', 'affiliates' );
echo ' ';
esc_html_e( 'This action cannot be undone.', 'affiliates' );
echo '';
echo '';
echo '
';
echo '';
echo '';
break;
}
}
}
Affiliates_Data_Cleaner::init();