"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",
// 5 posts which generate the most Facebook shares and likes
'posts' => "SELECT ID, post_title FROM `" . $wpdb->posts . "` WHERE post_status = 'publish' AND post_type = 'post'"
);
// 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
function date_diff_days($date1, $date2) {
$s = strtotime( $date2 ) - strtotime( $date1 );
$d = intval( $s / 86400 ) + 1;
return $d;
}
$elapseddays = date_diff_days( $startingdate, date( 'Y-m-d H:i:s' ) );
/********** ACTIONS **********/
add_action( 'admin_init', 'abm_admin_init' );
add_action( 'admin_menu', 'abm_admin_menu' );
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' );
// access only for administrator
if (current_user_can('administrator') ) {
add_action( 'wp_dashboard_setup', 'abm_dashboard_init' );
}
}
function abm_admin_menu() {
add_menu_page( 'Advanced Blog Metrics', 'Advanced Blog Metrics', 'administrator', 'advanced-blog-metrics', 'abm_options_page' );
}
/********** SETTINGS **********/
function abm_options_page() {
ob_start();
echo '
';
echo $html;
}
// Widget #4 : 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.
