"SELECT ID AS id, post_title, comment_count FROM `" . $wpdb->posts . "` WHERE post_type = 'post' AND post_status = 'publish' AND comment_count > 0 ORDER BY comment_count DESC LIMIT 5",
// When do your posts generate the most comments?
'comments_per_day' => "SELECT COUNT(comment_ID) AS count, DATE_FORMAT(comment_date, '%w') AS day FROM `" . $wpdb->comments . "` WHERE comment_approved = 1 AND DATE_FORMAT(comment_date, '%w') IS NOT NULL GROUP BY DATE_FORMAT(comment_date, '%w') ORDER BY day ASC",
// Total comments approved
'comments_total' => "SELECT COUNT(comment_ID) AS count FROM `" . $wpdb->comments . "` WHERE comment_approved = '1'",
// Total words per comments approved
'comments_word' => "SELECT SUM(LENGTH(comment_content) - LENGTH(REPLACE(comment_content, ' ', ''))+1) AS count FROM `" . $wpdb->comments . "` WHERE comment_approved = '1'",
// 5 authors who comment the most
'comments_per_author' => "SELECT u.ID, u.user_login, u.display_name, COUNT(c.comment_ID) AS comment_count FROM `" . $wpdb->comments . "` c LEFT JOIN `" . $wpdb->users . "` u ON c.user_id = u.ID WHERE c.user_id != 0 GROUP BY c.user_id ORDER BY comment_count DESC LIMIT 5",
// Total posts published
'posts_total' => "SELECT COUNT(ID) FROM `" . $wpdb->posts . "` WHERE post_type = 'post' AND post_status = 'publish'",
// Total words per posts approved
'posts_word' => "SELECT SUM(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', ''))+1) AS count FROM `" . $wpdb->posts . "` WHERE post_status = 'publish'",
// Date of first post
'first_post' => "SELECT post_date FROM `" . $wpdb->posts . "` WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date ASC LIMIT 1",
// When do you post ?
'posts_per_day' => "SELECT COUNT(ID) AS count, DATE_FORMAT(post_date, '%w') AS day FROM `" . $wpdb->posts . "` WHERE post_status = 'publish' AND post_type = 'post' GROUP BY DATE_FORMAT(post_date, '%w') ORDER BY day ASC"
);
// Comment Registration required
$commentregistration = (bool)get_option('comment_registration');
$startofweek = get_option(('start_of_week'));
// Options
$options = (array)get_option('abm_options');
// Starting date
if (!empty($options['starting_date'])) {
$startingdate = $options['starting_date'] . ' 00:00:00';
} else {
$startingdate = (string)current( $wpdb->get_col( $queries['first_post'], 0 ) );
}
// Elapsed days
$elapseddays = date_diff_days( $startingdate, date( 'Y-m-d H:i:s' ) );
add_action( 'admin_init', 'abm_admin_init' );
add_action( 'admin_menu', 'abm_admin_menu' );
add_action( 'wp_dashboard_setup', 'abm_dashboard_init' );
add_filter( 'plugin_action_links_'. plugin_basename( __FILE__ ), 'abm_plugin_action_links', 10, 2 );
function abm_admin_init() {
wp_register_style( 'advanced-blog-metrics', plugins_url( 'style.css', __FILE__ ) );
wp_enqueue_style( 'advanced-blog-metrics' );
register_setting( 'abm_options', 'abm_options', 'abm_options_validate' );
add_settings_section( 'abm_options_general', 'Settings', 'abm_options_general_text', 'abm_options' );
add_settings_field( 'abm_options_starting_date', '', 'abm_options_starting_date_text', 'abm_options', 'abm_options_general' );
}
function abm_admin_menu() {
add_menu_page( 'Advanced Blog Metrics', 'Advanced Blog Metrics', 'administrator', 'advanced-blog-metrics', 'abm_options_page' );
}
function abm_dashboard_init() {
wp_add_dashboard_widget( 'dashboard_comments_per_post', '5 posts which generate the most comments', 'dashboard_comments_per_post' );
wp_add_dashboard_widget( 'dashboard_comments_per_day', 'When do your posts generate the most comments?', 'dashboard_comments_per_day' );
wp_add_dashboard_widget( 'dashboard_comments', 'Comments', 'dashboard_comments' );
wp_add_dashboard_widget( 'dashboard_comments_per_author', '5 authors who comment the most', 'dashboard_comments_per_author' );
wp_add_dashboard_widget( 'dashboard_posts', 'Posts', 'dashboard_posts' );
wp_add_dashboard_widget( 'dashboard_posts_per_day', 'When do you post the most?', 'dashboard_posts_per_day' );
}
function abm_plugin_action_links( $links, $file ) {
array_unshift( $links, '' . __( 'Settings' ) . '' );
return $links;
}
function abm_options_page() {
ob_start();
echo '
If you leave this field empty, Advanced Blog Metrics uses the date of your first post by default.
';
}
function abm_options_validate($posted) {
$options = get_option( 'abm_options' );
$cleaned = array();
if ( !empty( $posted['starting_date'] ) && !preg_match( '`[0-9]{4}\-[0-9]{2}\-[0-9]{2}`', $posted['starting_date'] ) ) {
add_settings_error('abm_options_starting_date', 'abm_options_starting_date_bad_format', 'You did not use the expected date format. Please, fill in the starting date with the following format: YYYY-MM-DD');
} elseif ( !empty( $posted['starting_date'] ) ) {
list( $year, $month, $day ) = explode( '-', $posted['starting_date'] );
if ( !checkdate( $month, $day, $year ) ) {
add_settings_error('abm_options_starting_date', 'abm_options_starting_date_not_valid', 'You have entered a date which does not exist. Please, fill in the starting date with a valid date.');
}
}
if ( count( get_settings_errors() ) > 0 && array_key_exists( 'starting_date', $options ) ) {
$cleaned['starting_date'] = $options['starting_date'];
} else {
$cleaned['starting_date'] = $posted['starting_date'];
}
return $cleaned;
}
// Posts which generate the most comments
function dashboard_comments_per_post() {
global $queries, $wpdb;
$posts = $wpdb->get_results( $queries['comments_per_post'] );
$html = '
';
echo $html;
}
// Authors who comment the most
function dashboard_comments_per_author() {
global $queries, $wpdb, $commentregistration;
if ($commentregistration) {
$authors = $wpdb->get_results($queries['comments_per_author']);
}
$html = '
';
$html.= '
Author
Comments
';
if ($commentregistration) {
foreach ($authors as $author) {
$html.= '
Note that you need to check "Users must be registered and logged in to comment" in the Wordpress Settings->Discussion to see data in the "5 authors who comments the most" widget.
';
echo $html;
}
// When do you post the most ?
function dashboard_posts_per_day() {
global $queries, $wpdb, $days, $startofweek;
$posts = array();
$results = $wpdb->get_results( $queries['posts_per_day'] );
foreach($results as $result) {
$posts[$result->day] = $result->count;
}
$max = max($posts);
$html.= '