'Published',
'draft' => 'Drafted',
'pending' => 'Pending',
'trash' => 'Trashed',
'future' => 'Scheduled',
];
return $map[ $status ] ?? ucfirst( $status );
}
function astr_build_where_clause( $wpdb, $table, $filters ) {
$where = 'WHERE 1=1';
$args = [];
// Post type
if ( ! empty( $filters['post_type'] ) ) {
$where .= " AND t.post_type = %s";
$args[] = $filters['post_type'];
}
// Date
if ( ! empty( $filters['month'] ) && preg_match( '/^\d{6}$/', $filters['month'] ) ) {
$year = substr( $filters['month'], 0, 4 );
$month = substr( $filters['month'], 4, 2 );
$where .= " AND YEAR(time) = %d AND MONTH(time) = %d";
$args[] = (int) $year;
$args[] = (int) $month;
}
// Category
if ( ! empty( $filters['cat'] ) ) {
$where .= " AND t.post_id IN (
SELECT tr.object_id
FROM {$wpdb->term_relationships} tr
INNER JOIN {$wpdb->term_taxonomy} tt
ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.term_id = %d AND tt.taxonomy = 'category'
)";
$args[] = (int) $filters['cat'];
}
// Status
if ( ! empty( $filters['status'] ) ) {
$status = sanitize_text_field( $filters['status'] );
$where .= " AND t.status LIKE %s";
$args[] = $status . '%';
}
// Search
if ( ! empty( $filters['search'] ) ) {
$like = '%' . $wpdb->esc_like( $filters['search'] ) . '%';
$where .= " AND (t.post_title LIKE %s OR t.user_name LIKE %s)";
$args[] = $like;
$args[] = $like;
}
return [ $where, $args ];
}
function astr_get_latest_rows( $wpdb, $table, $where, $args, $orderby_sql, $order, $limit, $offset ) {
$allowed_order = ['ASC','DESC'];
$allowed_columns = [
'time' => 't.time',
'post_id' => 't.post_id',
'user_name' => 't.user_name',
'post_title' => 't.post_title',
'status' => 't.status',
'post_type' => 't.post_type',
];
if ( ! in_array(strtoupper($order), $allowed_order, true) ) $order = 'DESC';
if ( ! isset($allowed_columns[$orderby_sql]) ) {
$orderby_sql = 't.time';
} else {
$orderby_sql = $allowed_columns[$orderby_sql];
}
$rev_where = $where . " AND EXISTS (SELECT 1 FROM {$wpdb->posts} p WHERE p.post_parent = t.post_id AND p.post_type = 'revision')";
$table_safe = $table;
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
$sql = $wpdb->prepare( "SELECT t.* FROM ( SELECT post_id, MAX(time) as max_time FROM {$table_safe} GROUP BY post_id ) latest INNER JOIN {$table_safe} t ON t.post_id = latest.post_id AND t.time = latest.max_time {$rev_where} ORDER BY {$orderby_sql} {$order}, latest.max_time {$order} LIMIT %d OFFSET %d", ...array_merge( $args, [ $limit, $offset ] ) );
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $wpdb->get_results( $sql );
}
function astr_get_total_count( $wpdb, $table, $where, $args ) {
$rev_where = $where . " AND EXISTS (SELECT 1 FROM {$wpdb->posts} p WHERE p.post_parent = t.post_id AND p.post_type = 'revision')";
$table_safe = esc_sql( $table );
$sql = "SELECT COUNT(*) FROM ( SELECT post_id, MAX(time) as max_time FROM {$table_safe} GROUP BY post_id ) latest INNER JOIN {$table_safe} t ON t.post_id = latest.post_id AND t.time = latest.max_time {$rev_where}";
if ( ! empty( $args ) ) {
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$sql = $wpdb->prepare( $sql, ...$args );
}
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
return (int) $wpdb->get_var( $sql );
}
function astr_get_status_label( $status ) {
$obj = get_post_status_object( $status );
return $obj && ! empty( $obj->label ) ? $obj->label : $status;
}
function astr_sortable_th( $label, $key, $current_orderby, $current_order ) {
$is_active = ( $current_orderby === $key );
$current_order = strtolower( $current_order );
$current_order = in_array( $current_order, [ 'asc', 'desc' ], true ) ? $current_order : 'asc';
$new_order = ( $is_active && $current_order === 'asc' ) ? 'desc' : 'asc';
$arrow_class = $is_active ? ( $current_order === 'asc' ? 'dashicons dashicons-arrow-down' : 'dashicons dashicons-arrow-up' ) : 'dashicons dashicons-arrow-down';
$url = add_query_arg([
'orderby' => $key,
'order' => $new_order,
]);
$classes = [ 'sortable' ];
if ( $is_active ) {
$classes[] = 'sorted';
$classes[] = $current_order;
}
return '
' . esc_html( $label ) . '
| ';
}
function astr_plugins_sortable_th( $label, $key, $current_by, $current_order ) {
$is_active = $current_by === $key;
$new_order = ( $is_active && $current_order === 'asc' ) ? 'desc' : 'asc';
$arrow_class = 'dashicons ';
if ( $is_active ) {
$arrow_class .= ( $current_order === 'asc' ) ? 'dashicons-arrow-down' : 'dashicons-arrow-up';
} else {
$arrow_class .= 'dashicons-arrow-down';
}
$arrow = '';
$url = add_query_arg( [ 'orderby' => $key, 'order' => $new_order ] );
$link_class = 'astr-sort-link' . ( $is_active ? ' active' : '' );
return "{$label} {$arrow}";
}
function astr_pagination( $total, $total_pages, $paged ) {
$total = max( 0, (int) $total );
$total_pages = max( 1, (int) $total_pages );
$paged = max( 1, (int) $paged );
$allowed_keys = [ 'page', 'paged', 'orderby', 'order', 'post_type', 'month', 'cat', 'status', 'search', 's', 'filter_status', 'filter_date', 'filter_user', 'tab', '_astr_nonce', 'm', 'theme', 'change_type', ];
$all_query = [];
foreach ( $allowed_keys as $key ) {
$value = astr_get_request_var( $key );
if ( null === $value ) { continue; }
switch ( $key ) {
case 'page':
case 'orderby':
case 'order':
case 'post_type':
case 'status':
case 'tab':
$value = sanitize_key( $value );
break;
case 'paged':
case 'cat':
$value = absint( $value );
break;
default:
$value = sanitize_text_field( $value );
break;
}
if ( '' !== $value && null !== $value ) { $all_query[ $key ] = $value; }
}
if ( empty( $all_query['page'] ) ) { $all_query['page'] = 'astr-plugins-history'; }
$first_query = $all_query;
$first_query['paged'] = 1;
$prev_query = $all_query;
$prev_query['paged'] = max( 1, $paged - 1 );
$next_query = $all_query;
$next_query['paged'] = min( $total_pages, $paged + 1 );
$last_query = $all_query;
$last_query['paged'] = $total_pages;
$base_url = admin_url( 'admin.php' );
$first_link = add_query_arg( $first_query, $base_url );
$prev_link = add_query_arg( $prev_query, $base_url );
$next_link = add_query_arg( $next_query, $base_url );
$last_link = add_query_arg( $last_query, $base_url );
$is_first = ( $paged <= 1 );
$is_last = ( $paged >= $total_pages );
echo '';
echo '' . esc_html( $total ) . ' items';
echo '';
echo '
';
}
function astr_get_request_var( $key ) {
$value = filter_input( INPUT_GET, $key, FILTER_UNSAFE_RAW );
if ( null === $value ) { return null; }
$value = wp_unslash( $value );
if ( is_array( $value ) ) { return array_map( 'sanitize_text_field', $value ); }
return sanitize_text_field( $value );
}
function astr_get_admin_pages_tabs() {
return [
'adwise-sitetrace' => 'Dashboard',
'astr-plugins-history' => 'Plugins History',
'astr-pages-history' => 'Pages History',
'astr-posts-history' => 'Posts History',
'astr-css-history' => 'CSS History',
'astr-options' => 'Options',
];
}
function astr_render_admin_tabs( $active_page ) {
$tabs = astr_get_admin_pages_tabs();
echo '';
foreach ( $tabs as $slug => $label ) {
$is_active = ( (string) $active_page === (string) $slug );
$class = $is_active ? 'nav-tab nav-tab-active' : 'nav-tab';
$url = add_query_arg( [ 'page' => $slug ], admin_url('admin.php') );
echo '';
echo esc_html($label);
echo '';
}
echo '
';
}
function astr_get_current_admin_page() { return astr_safe_get('page', 'key', 'adwise-sitetrace', 'manage_options', false); }
function astr_get_revision_or_restore_button( $post_id, $wpdb ) {
$post = get_post( $post_id );
if ( ! $post ) { return ''; }
if ( $post->post_status === 'trash' ) {
$url = wp_nonce_url( admin_url( 'admin-post.php?action=astr_restore_post&post_id=' . $post_id ), 'astr_restore_post_' . $post_id );
return 'Restore';
}
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$last_revision_id = $wpdb->get_var( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_parent = %d AND post_type = 'revision' ORDER BY post_date DESC LIMIT 1", $post_id ) );
if ( ! $last_revision_id ) { return ''; }
return 'View Last Revision';
}
add_action( 'admin_post_astr_restore_post', 'astr_restore_post_handler' );
function astr_restore_post_handler() {
if ( ! isset( $_GET['post_id'], $_GET['_wpnonce'] ) ) { wp_die( 'Invalid request' ); }
$post_id = absint( $_GET['post_id'] );
$nonce = sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) );
if ( ! wp_verify_nonce( $nonce, 'astr_restore_post_' . $post_id ) ) { wp_die( 'Security check failed' ); }
if ( ! current_user_can( 'delete_post', $post_id ) ) { wp_die( 'Permission denied' ); }
$post = get_post( $post_id );
if ( $post && $post->post_status === 'trash' ) {
wp_update_post( [
'ID' => $post_id,
'post_status' => 'draft',
] );
}
wp_safe_redirect( wp_get_referer() );
exit;
}
/**
* Safe GET param (with nonce + capability check)
*/
function astr_safe_get( $key, $type = 'text', $default = '', $cap = 'manage_options', $require_nonce = true ) {
if ( ! current_user_can( $cap ) ) { return $default; }
if ( ! isset( $_GET[ $key ] ) ) { return $default; }
if ( $require_nonce ) {
$nonce = isset( $_GET['_astr_nonce'] ) ? sanitize_key( wp_unslash( $_GET['_astr_nonce'] ) ) : '';
if ( ! $nonce || ! wp_verify_nonce( $nonce, 'astr_filter_action' ) ) { return $default; }
}
// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
$value = wp_unslash( $_GET[ $key ] );
switch ( $type ) {
case 'int': return intval( $value );
case 'absint': return absint( $value );
case 'key': return sanitize_key( $value );
default: return sanitize_text_field( $value );
}
}
function astr_human_change_type( $type ) {
if ( $type === 'theme_switch' ) {
return 'Theme switched';
}
return 'CSS updated';
}
// ==========================
// dashboard
// ==========================
function astr_get_posts() {
global $wpdb;
$posts_table = $wpdb->prefix . 'astr_posts_history';
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $wpdb->get_results( "SELECT h1.post_id, h1.post_title, h1.post_type, h1.user_name, h1.time, h1.status FROM $posts_table h1 INNER JOIN ( SELECT post_id, MAX(time) as max_time FROM $posts_table GROUP BY post_id ) h2 ON h1.post_id = h2.post_id AND h1.time = h2.max_time ORDER BY h1.time DESC LIMIT 5");
}
function astr_get_pages() {
global $wpdb;
$pages_table = $wpdb->prefix . 'astr_pages_history';
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $wpdb->get_results( "SELECT h1.post_id, h1.post_title, h1.user_name, h1.time, h1.status FROM $pages_table h1 INNER JOIN ( SELECT post_id, MAX(time) as max_time FROM $pages_table GROUP BY post_id ) h2 ON h1.post_id = h2.post_id AND h1.time = h2.max_time ORDER BY h1.time DESC LIMIT 5");
}
function astr_get_plugins() {
global $wpdb;
$plugins_table = $wpdb->prefix . 'astr_plugins_history';
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $wpdb->get_results("SELECT h1.plugin_name, h1.user_name, h1.action, h1.time, h1.old_version, h1.new_version FROM $plugins_table h1 INNER JOIN ( SELECT plugin_name, MAX(time) as max_time FROM $plugins_table WHERE action NOT IN ('tracking_started','tracking_stopped') GROUP BY plugin_name ) h2 ON h1.plugin_name = h2.plugin_name AND h1.time = h2.max_time WHERE h1.action NOT IN ('tracking_started','tracking_stopped') ORDER BY h1.time DESC LIMIT 5" );
}
function astr_get_css() {
global $wpdb;
$css_table = $wpdb->prefix . 'astr_customizer_snapshots';
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $wpdb->get_results(" SELECT user_name, change_type, theme, time, action FROM $css_table ORDER BY time DESC LIMIT 5");
}
function astr_count_by_time($table, $time_condition, $time_value, $user_id = '', $exclude_action = '') {
global $wpdb;
$table = preg_replace('/[^a-zA-Z0-9_$]/', '', $table);
switch ($time_condition) {
case 'day': $sql_part = "DATE_FORMAT(time, '%%Y-%%m-%%d %%H:00:00') = %s";
break;
case 'month':
case 'week': $sql_part = "DATE(time) = DATE(%s)";
break;
default: return 0;
}
$sql = "SELECT COUNT(*) FROM {$table} WHERE {$sql_part}";
$args = [ $time_value ];
if ($user_id !== '') {
$sql .= " AND TRIM(user_name) = %s";
$args[] = $user_id;
}
if (!empty($exclude_action)) {
if (is_array($exclude_action)) {
$placeholders = implode(',', array_fill(0, count($exclude_action), '%s'));
$sql .= " AND action NOT IN ($placeholders)";
$args = array_merge($args, $exclude_action);
} else {
$sql .= " AND action != %s";
$args[] = $exclude_action;
}
}
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
return (int) $wpdb->get_var( $wpdb->prepare($sql, ...$args) );
}
function astr_get_chart_day($user = '') {
global $wpdb;
$posts_table = $wpdb->prefix . 'astr_posts_history';
$pages_table = $wpdb->prefix . 'astr_pages_history';
$plugins_table = $wpdb->prefix . 'astr_plugins_history';
$css_table = $wpdb->prefix . 'astr_customizer_snapshots';
$exclude_action = ['tracking_started', 'tracking_stopped'];
$start_of_day = strtotime('today', current_time('timestamp'));
$data = [];
for ($i = 0; $i < 24; $i++) {
$target_timestamp = $start_of_day + ($i * HOUR_IN_SECONDS);
$time = gmdate('Y-m-d H:00:00', $target_timestamp);
$label = gmdate('H:00', $target_timestamp);
$data[] = [
'date' => $label,
'posts' => astr_count_by_time($posts_table, 'day', $time, $user),
'pages' => astr_count_by_time($pages_table, 'day', $time, $user),
'plugins' => astr_count_by_time($plugins_table, 'day', $time, $user, $exclude_action),
'css' => astr_count_by_time($css_table, 'day', $time, $user),
];
}
return $data;
}
function astr_get_chart_week($user = '') {
global $wpdb;
$posts_table = $wpdb->prefix . 'astr_posts_history';
$pages_table = $wpdb->prefix . 'astr_pages_history';
$plugins_table = $wpdb->prefix . 'astr_plugins_history';
$css_table = $wpdb->prefix . 'astr_customizer_snapshots';
$data = [];
$exclude_action = ['tracking_started', 'tracking_stopped'];
$start_of_week = strtotime('monday this week');
for ($i = 0; $i < 7; $i++) {
$ts = strtotime("+$i days", $start_of_week);
$date = gmdate('Y-m-d', $ts);
$label = gmdate('j M', $ts);
$data[] = [
'date' => $label,
'posts' => astr_count_by_time($posts_table, 'week', $date, $user),
'pages' => astr_count_by_time($pages_table, 'week', $date, $user),
'plugins' => astr_count_by_time($plugins_table, 'week', $date, $user, $exclude_action),
'css' => astr_count_by_time($css_table, 'week', $date, $user),
];
}
return $data;
}
function astr_get_chart_month($user = '') {
global $wpdb;
$posts_table = $wpdb->prefix . 'astr_posts_history';
$pages_table = $wpdb->prefix . 'astr_pages_history';
$plugins_table = $wpdb->prefix . 'astr_plugins_history';
$css_table = $wpdb->prefix . 'astr_customizer_snapshots';
$data = [];
$exclude_action = ['tracking_started', 'tracking_stopped'];
$days_in_month = (int) gmdate('t');
$start_of_month = strtotime(gmdate('Y-m-01'));
for ($i = 0; $i < $days_in_month; $i++) {
$ts = strtotime("+$i days", $start_of_month);
$date = gmdate('Y-m-d', $ts);
$label = gmdate('j M', $ts);
$data[] = [
'date' => $label,
'posts' => astr_count_by_time($posts_table, 'month', $date, $user),
'pages' => astr_count_by_time($pages_table, 'month', $date, $user),
'plugins' => astr_count_by_time($plugins_table, 'month', $date, $user, $exclude_action),
'css' => astr_count_by_time($css_table, 'month', $date, $user),
];
}
return $data;
}
function astr_get_chart_data($period = 'week', $user = '') {
switch ($period) {
case 'day': return astr_get_chart_day($user);
case 'month': return astr_get_chart_month($user);
case 'week': default: return astr_get_chart_week($user);
}
}
function astr_get_users_from_logs() {
global $wpdb;
$tables = [
$wpdb->prefix . 'astr_posts_history',
$wpdb->prefix . 'astr_pages_history',
$wpdb->prefix . 'astr_plugins_history',
$wpdb->prefix . 'astr_customizer_snapshots',
];
$users = [];
foreach ($tables as $table) {
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$results = $wpdb->get_results("SELECT DISTINCT user_name FROM $table WHERE user_name IS NOT NULL AND user_name != ''");
foreach ($results as $row) {
$name = trim($row->user_name);
if ($name !== '') {
$users[$name] = $name;
}
}
}
return $users;
}