'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 ''; if ( $is_first ) { echo '«'; } else { echo '«'; } if ( $is_first ) { echo '‹'; } else { echo '‹'; } echo ''; echo '
'; foreach ( $all_query as $k => $v ) { if ( 'paged' === $k ) { continue; } echo ''; } echo ''; echo ' of ' . esc_html( $total_pages ) . ''; echo '
'; echo '
'; if ( $is_last ) { echo '›'; } else { echo '›'; } if ( $is_last ) { echo '»'; } else { echo '»'; } 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 ''; } 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; }