PHP SQL::GROUP_BY方法代码示例

/**
 * Retrieves all tags from published posts
 *
 * @param integer the id of the blog or array of blog ids. Set NULL to use current blog
 * @param integer maximum number of returned tags
 * @param string a comma separated list of tags to ignore/exclude
 * @param bool true to skip tags from pages, intro posts and sidebar stuff
 * @return array of tags
 */
function get_tags($blog_ids, $limit = 0, $filter_list = NULL, $skip_intro_posts = false)
{
    global $DB, $localtimenow, $posttypes_specialtypes;
    $BlogCache =& get_BlogCache();
    if (is_null($blog_ids)) {
        global $blog;
        $blog_ids = $blog;
    }
    if (is_array($blog_ids)) {
        // Get quoted ID list
        $where_cat_clause = 'cat_blog_ID IN ( ' . $DB->quote($blog_ids) . ' )';
    } else {
        // Get list of relevant blogs
        $Blog =& $BlogCache->get_by_ID($blog_ids);
        $where_cat_clause = trim($Blog->get_sql_where_aggregate_coll_IDs('cat_blog_ID'));
    }
    // Build query to get the tags:
    $tags_SQL = new SQL();
    $tags_SQL->SELECT('tag_name, COUNT( DISTINCT itag_itm_ID ) AS tag_count, tag_ID, cat_blog_ID');
    $tags_SQL->FROM('T_items__tag');
    $tags_SQL->FROM_add('INNER JOIN T_items__itemtag ON itag_tag_ID = tag_ID');
    $tags_SQL->FROM_add('INNER JOIN T_items__item ON itag_itm_ID = post_ID');
    $tags_SQL->FROM_add('INNER JOIN T_postcats ON itag_itm_ID = postcat_post_ID');
    $tags_SQL->FROM_add('INNER JOIN T_categories ON postcat_cat_ID = cat_ID');
    $tags_SQL->WHERE($where_cat_clause);
    $tags_SQL->WHERE_and('post_status = "published"');
    $tags_SQL->WHERE_and('post_datestart < ' . $DB->quote(remove_seconds($localtimenow)));
    if ($skip_intro_posts) {
        // Skip "Intro" posts
        $tags_SQL->WHERE_and('post_ityp_ID NOT IN ( ' . implode(', ', $posttypes_specialtypes) . ' )');
    }
    if (!empty($filter_list)) {
        // Filter tags
        $tags_SQL->WHERE_and('tag_name NOT IN ( ' . $DB->quote(explode(', ', $filter_list)) . ' )');
    }
    $tags_SQL->GROUP_BY('tag_name');
    $tags_SQL->ORDER_BY('tag_count DESC');
    if (!empty($limit)) {
        // Limit
        $tags_SQL->LIMIT($limit);
    }
    return $DB->get_results($tags_SQL->get(), OBJECT, 'Get tags');
}

/**
 * Detect timed out cron jobs and Send notifications
 *
 * @param array Task with error
 *             'name'
 *             'message'
 */
function detect_timeout_cron_jobs($error_task = NULL)
{
    global $DB, $time_difference, $cron_timeout_delay, $admin_url;
    $SQL = new SQL('Find cron timeouts');
    $SQL->SELECT('ctsk_ID, ctsk_name, ctsk_key');
    $SQL->FROM('T_cron__log');
    $SQL->FROM_add('INNER JOIN T_cron__task ON ctsk_ID = clog_ctsk_ID');
    $SQL->WHERE('clog_status = "started"');
    $SQL->WHERE_and('clog_realstart_datetime < ' . $DB->quote(date2mysql(time() + $time_difference - $cron_timeout_delay)));
    $SQL->GROUP_BY('ctsk_ID');
    $timeout_tasks = $DB->get_results($SQL->get(), OBJECT, $SQL->title);
    $tasks = array();
    if (count($timeout_tasks) > 0) {
        $cron_jobs_names = get_cron_jobs_config('name');
        foreach ($timeout_tasks as $timeout_task) {
            if (!empty($timeout_task->ctsk_name)) {
                // Task name is defined in DB
                $task_name = $timeout_task->ctsk_name;
            } else {
                // Try to get default task name by key:
                $task_name = isset($cron_jobs_names[$timeout_task->ctsk_key]) ? $cron_jobs_names[$timeout_task->ctsk_key] : $timeout_task->ctsk_key;
            }
            $tasks[$timeout_task->ctsk_ID] = array('name' => $task_name, 'message' => NT_('Cron job has timed out.'));
        }
        // Update timed out cron jobs:
        $DB->query('UPDATE T_cron__log
			  SET clog_status = "timeout"
			WHERE clog_ctsk_ID IN ( ' . $DB->quote(array_keys($tasks)) . ' )', 'Mark timeouts in cron jobs.');
    }
    if (!is_null($error_task)) {
        // Send notification with error task
        $tasks[$error_task['ID']] = $error_task;
    }
    if (count($tasks) > 0) {
        // Send notification email about timed out and error cron jobs to users with edit options permission
        $email_template_params = array('tasks' => $tasks);
        send_admin_notification(NT_('Scheduled task error'), 'scheduled_task_error_report', $email_template_params);
    }
}

    $select_SQL->FROM_add('LEFT JOIN T_messaging__contact_groupusers ON cgu_user_ID = mct_to_user_ID');
    $select_SQL->FROM_add('LEFT JOIN T_messaging__contact_groups ON cgr_ID = cgu_cgr_ID');
    $select_SQL->WHERE_and('cgu_cgr_ID = ' . $DB->quote($g));
    $select_SQL->WHERE_and('cgr_user_ID = ' . $current_User->ID);
    $count_SQL->FROM_add('LEFT JOIN T_messaging__contact_groupusers ON cgu_user_ID = mct_to_user_ID');
    $count_SQL->FROM_add('LEFT JOIN T_messaging__contact_groups ON cgr_ID = cgu_cgr_ID');
    $count_SQL->WHERE_and('cgu_cgr_ID = ' . $DB->quote($g));
    $count_SQL->WHERE_and('cgr_user_ID = ' . $current_User->ID);
    // Get info of filtered group
    $group_filtered_SQL = new SQL();
    $group_filtered_SQL->SELECT('cgr_ID AS ID, cgr_name AS name, COUNT(cgu_user_ID) AS count_users');
    $group_filtered_SQL->FROM('T_messaging__contact_groups');
    $group_filtered_SQL->FROM_add('LEFT JOIN T_messaging__contact_groupusers ON cgu_cgr_ID = cgr_ID');
    $group_filtered_SQL->WHERE('cgr_ID = ' . $DB->quote($g));
    $group_filtered_SQL->WHERE_and('cgr_user_ID = ' . $current_User->ID);
    $group_filtered_SQL->GROUP_BY('cgr_ID');
    $group_filtered = $DB->get_row($group_filtered_SQL->get());
}
// Create result set:
if ($Settings->get('allow_avatars')) {
    $default_order = '--A';
} else {
    $default_order = '-A';
}
$Results = new Results($select_SQL->get(), 'mct_', $default_order, NULL, $count_SQL->get());
$Results->title = T_('Contacts list');
if (is_admin_page()) {
    // Set an url for manual page:
    $Results->title .= get_manual_link('contacts-list');
}
/**

$Results->cols[] = array('th' => T_('# Not active'), 'td' => '%stats_gender_value( #cnt_notactive#, ' . $total_cnt_notactive . ' )%', 'order' => 'cnt_notactive', 'default_dir' => 'D', 'total' => $total_cnt_notactive);
$Results->cols[] = array('th' => T_('# With profile picture'), 'td' => '%stats_gender_value( #cnt_pictured#, ' . $total_cnt_pictured . ' )%', 'order' => 'cnt_pictured', 'default_dir' => 'D', 'total' => $total_cnt_pictured);
echo '<br />';
$Results->display(array('page_url' => $dispatcher . '?ctrl=users&amp;tab=stats'));
/*** Graph of registrations per day ***/
echo '<h2>' . T_('# registrations per day') . '</h2>';
global $AdminUI, $user_gender_color;
$SQL = new SQL();
$SQL->SELECT('SQL_NO_CACHE COUNT(*) AS users,
		CONCAT( IF( user_gender IN ( "M", "F" ), user_gender, "G" ), "_", IF( user_status IN ( "activated", "autoactivated" ), "active", "notactive" ) ) AS user_gender_status,
		EXTRACT(YEAR FROM user_created_datetime) AS year,
		EXTRACT(MONTH FROM user_created_datetime) AS month,
		EXTRACT(DAY FROM user_created_datetime) AS day');
$SQL->FROM('T_users');
$SQL->WHERE('user_created_datetime >= ' . $DB->quote(date('Y-m-d H:i:s', mktime(date('H'), date('i'), date('s'), date('m') - 1, date('d'), date('Y')))));
$SQL->GROUP_BY('year, month, day, user_gender_status');
$SQL->ORDER_BY('year DESC, month DESC, day DESC, user_gender_status');
$res_users = $DB->get_results($SQL->get(), ARRAY_A, 'Get user summary');
/*
 * Chart
 */
if (count($res_users)) {
    $last_date = 0;
    /*
    ONE COLOR for user_gender = F AND status IN ( activated, autoactivated )
    ONE COLOR for user_gender = F AND status NOT IN ( activated, autoactivated )
    ONE COLOR for user_gender = M AND status IN ( activated, autoactivated )
    ONE COLOR for user_gender = M AND status NOT IN ( activated, autoactivated )
    ONE COLOR for user_gender = NULL AND status IN ( activated, autoactivated )
    ONE COLOR for user_gender = NULL AND status NOT IN ( activated, autoactivated )
    */

    return 1;
}
// load all required Blogs
$BlogCache =& get_BlogCache();
$BlogCache->load_list($moderation_blogs);
// count all comments awaiting for moderation in the required blogs, group by blog/comment_status/author_level
$SQL = new SQL();
$SQL->SELECT('cat_blog_ID as blog_ID, comment_status, IF( comment_author_ID IS NULL, 0, user_level ) as author_level, count( comment_ID ) as cmt_count');
$SQL->FROM('T_comments');
$SQL->FROM_add('LEFT JOIN T_users ON user_ID = comment_author_ID');
$SQL->FROM_add('LEFT JOIN T_items__item ON post_ID = comment_post_ID');
$SQL->FROM_add('LEFT JOIN T_categories ON cat_ID = post_main_cat_ID');
$SQL->WHERE('comment_status IN ( ' . $DB->quote($notify_statuses) . ' )');
$SQL->WHERE_and('post_status IN ( ' . $DB->quote(array('published', 'community', 'protected')) . ' )');
$SQL->WHERE_and('cat_blog_ID IN (' . implode(',', $moderation_blogs) . ')');
$SQL->GROUP_BY('cat_blog_ID, comment_status, author_level');
$blog_comments = $DB->get_results($SQL->get());
// Create a comments map by blog_ID:comment_status:author_level:count. This way it will be much easier to get allowed comments for a specific permission
$comments_map = array();
$last_blog_ID = NULL;
foreach ($blog_comments as $row) {
    if ($last_blog_ID != $row->blog_ID) {
        $Blog =& $BlogCache->get_by_ID($row->blog_ID);
        $blog_moderation_statuses = $Blog->get_setting('moderation_statuses');
        $last_blog_ID = $row->blog_ID;
    }
    if (strpos($blog_moderation_statuses, $row->comment_status) === false) {
        // This status shouldn't be notified on this blog
        continue;
    }
    if (isset($comments_map[$row->blog_ID])) {

    // UserCache result is empty which means nobody wants to receive notifications
    $result_message = sprintf('Could not find any moderators wanting to receive post moderation notifications for the blogs that have posts pending moderation!');
    return 1;
}
// load all required Blogs
$BlogCache =& get_BlogCache();
$BlogCache->load_list($moderation_blogs);
// count all posts awaiting for moderation in the required blogs, group by blog/post_status/author_level
$SQL = new SQL();
$SQL->SELECT('cat_blog_ID as blog_ID, post_status, user_level as author_level, count( post_ID ) as post_count');
$SQL->FROM('T_items__item');
$SQL->FROM_add('LEFT JOIN T_users ON user_ID = post_creator_user_ID');
$SQL->FROM_add('LEFT JOIN T_categories ON cat_ID = post_main_cat_ID');
$SQL->WHERE('post_status IN ( ' . $DB->quote($notify_statuses) . ' )');
$SQL->WHERE_and('cat_blog_ID IN (' . implode(',', $moderation_blogs) . ')');
$SQL->GROUP_BY('cat_blog_ID, post_status, author_level');
$blog_posts = $DB->get_results($SQL->get());
// Create a posts map by blog_ID:post_status:author_level:count. This way it will be much easier to get allowed posts for a specific permission
$posts_map = array();
$last_blog_ID = NULL;
foreach ($blog_posts as $row) {
    if ($last_blog_ID != $row->blog_ID) {
        $Blog =& $BlogCache->get_by_ID($row->blog_ID);
        $blog_moderation_statuses = $Blog->get_setting('post_moderation_statuses');
        $last_blog_ID = $row->blog_ID;
    }
    if (strpos($blog_moderation_statuses, $row->post_status) === false) {
        // This status shouldn't be notified on this blog
        continue;
    }
    if (isset($posts_map[$row->blog_ID])) {

 /**
  * Run Query: GET DATA ROWS *** HEAVY ***
  */
 function query()
 {
     global $DB, $Session, $localtimenow;
     if (!is_null($this->rows)) {
         // Query has already executed:
         return;
     }
     // INIT THE QUERY:
     $this->query_init();
     // We are going to proceed in two steps (we simulate a subquery)
     // 1) we get the IDs we need
     // 2) we get all the other fields matching these IDs
     // This is more efficient than manipulating all fields at once.
     // *** STEP 1 ***
     $user_IDs = isset($this->filters['users']) ? $this->filters['users'] : array();
     if ($this->refresh_query || $localtimenow - $Session->get($this->filterset_name . '_refresh_time') > 7200) {
         // We should create new list of user IDs
         global $Timer;
         $Timer->start('Users_IDs', false);
         $step1_SQL = new SQL();
         $step1_SQL->SELECT('T_users.user_ID, IF( user_avatar_file_ID IS NOT NULL, 1, 0 ) as has_picture, COUNT( DISTINCT blog_ID ) AS nb_blogs');
         if (!empty($this->filters['reported']) && $this->filters['reported']) {
             // Filter is set to 'Reported users'
             $step1_SQL->SELECT_add(', COUNT( DISTINCT urep_reporter_ID ) AS user_rep');
         }
         $step1_SQL->FROM($this->UserQuery->get_from(''));
         $step1_SQL->WHERE($this->UserQuery->get_where(''));
         $step1_SQL->GROUP_BY($this->UserQuery->get_group_by(''));
         $step1_SQL->ORDER_BY($this->UserQuery->get_order_by(''));
         $step1_SQL->LIMIT(0);
         // Get list of the IDs we need:
         $user_IDs = $DB->get_col($step1_SQL->get(), 0, 'UserList::Query() Step 1: Get ID list');
         // Update filter with user IDs
         $this->filters['users'] = $user_IDs;
         $this->save_filterset();
         $Timer->stop('Users_IDs');
     }
     // GET TOTAL ROW COUNT:
     parent::count_total_rows(count($user_IDs));
     // Pagination, Get user IDs from array for current page
     $user_IDs_paged = array_slice($user_IDs, ($this->page - 1) * $this->limit, $this->limit);
     // *** STEP 2 ***
     $step2_SQL = $this->UserQuery;
     if (!empty($user_IDs_paged)) {
         // Init sql query to get users by IDs
         $step2_SQL->WHERE($this->Cache->dbIDname . ' IN (' . implode(',', $user_IDs_paged) . ') ');
         $step2_SQL->ORDER_BY('FIND_IN_SET( user_ID, "' . implode(',', $user_IDs_paged) . '" )');
     } else {
         // No users
         $step2_SQL->WHERE('user_ID IS NULL');
     }
     $this->sql = $step2_SQL->get();
     // ATTENTION: we skip the parent on purpose here!! fp> refactor
     DataObjectList2::query(false, false, false, 'UserList::Query() Step 2');
 }

if (!empty($blog)) {
    $SQL->WHERE_and('hit_coll_ID = ' . $blog . ' OR hit_coll_ID IS NULL');
}
$SQL->FROM('T_basedomains LEFT OUTER JOIN T_hitlog ON dom_ID = hit_referer_dom_ID');
if ($tab3 == 'top') {
    // Calculate the counts only for "top" tab
    $SQL->SELECT('SQL_NO_CACHE COUNT( hit_ID ) AS hit_count');
    $total_hit_count = $DB->get_var($SQL->get(), 0, 0, 'Get total hit count - referred hits only');
    $sql_select = ', COUNT( hit_ID ) AS hit_count';
} else {
    // No calc the counts
    $sql_select = '';
}
// Create result set:
$SQL->SELECT('SQL_NO_CACHE dom_name, dom_status, dom_type' . $sql_select);
$SQL->GROUP_BY('dom_ID');
$count_SQL = new SQL();
$count_SQL->SELECT('SQL_NO_CACHE COUNT( DISTINCT dom_ID )');
$count_SQL->FROM($SQL->get_from(''));
$count_SQL->WHERE($SQL->get_where(''));
$Results = new Results($SQL->get(), 'refdom_', '---D', $UserSettings->get('results_per_page'), $count_SQL->get());
if ($current_User->check_perm('stats', 'edit')) {
    // Current user has a permission to create new domain
    global $tab_from;
    $tab_from_param = empty($tab_from) ? '' : '&tab_from=' . $tab_from;
    $Results->global_icon(T_('Add domain'), 'new', $admin_url . '?ctrl=stats&tab=domains&tab3=' . $tab3 . '&action=domain_new' . $tab_from_param . (empty($blog) ? '' : '&blog=' . $blog), T_('Add domain') . ' »', 3, 4, array('class' => 'action_icon btn-primary'));
}
/**
 * Callback to add filters on top of the result set
 *
 * @param Form

         $SQL->WHERE('ghit_goal_ID = ' . $goal_ID);
     } else {
         $SQL->FROM_add('INNER JOIN T_track__goal ON goal_ID = ghit_goal_ID');
         $SQL->WHERE_and('goal_name LIKE ' . $DB->quote($goal_name . '%'));
     }
     // Date param applies to goal hit
     if (!empty($datestart)) {
         $SQL->WHERE_and('goalhit_hit.hit_datetime >= ' . $DB->quote($datestart . ' 00:00:00'));
     }
     if (!empty($datestop)) {
         $SQL->WHERE_and('goalhit_hit.hit_datetime <= ' . $DB->quote($datestop . ' 23:59:59'));
     }
 }
 $SQL->WHERE_and('hit_agent_type = "browser"');
 if ($split_engines) {
     $SQL->GROUP_BY('keyp_ID, T_hitlog.hit_referer_dom_ID');
 } else {
     $SQL->GROUP_BY('keyp_ID');
 }
 if (!empty($blog)) {
     $SQL->WHERE_and('T_hitlog.hit_blog_ID = ' . $blog);
 }
 // COUNT:
 $SQL->SELECT('keyp_ID');
 if (empty($goal_ID) && empty($goal_name)) {
     // We're not restricting to a Goal
     $SQL->SELECT_add(', COUNT(DISTINCT hit_remote_addr) as count');
 } else {
     // We ARE retsrticting to a Goal
     $SQL->SELECT_add(', COUNT(DISTINCT goalhit_hit.hit_ID, T_hitlog.hit_remote_addr) as count');
 }

/**
 * Count reprots by status from the given user
 *
 * @param integer user ID
 * @param boolean set false to get plain result array, or set true to get display format
 * @return mixed array if display format is true, string otherwise
 */
function count_reports_from($user_ID, $display_format = true)
{
    global $DB, $admin_url;
    $SQL = new SQL();
    $SQL->SELECT('urep_status as status, COUNT( DISTINCT( urep_reporter_ID ) ) as num_count');
    $SQL->FROM('T_users__reports');
    $SQL->WHERE('urep_target_user_ID = ' . $DB->quote($user_ID));
    $SQL->GROUP_BY('urep_status');
    $reports = $DB->get_assoc($SQL->get());
    if (!$display_format) {
        // don't display return result
        return $reports;
    }
    if (empty($reports)) {
        // there are no reports yet from the given user
        return '<span style="color:green">' . T_('No reports yet.') . '</span>';
    }
    $result = '<span style="color:red">';
    foreach ($reports as $status => $num_count) {
        $result .= $status . ': ' . $num_count . '; ';
    }
    $result .= '</span>- <a href="' . url_add_param($admin_url, 'ctrl=user&amp;user_ID=' . $user_ID . '&amp;user_tab=activity#reports_result') . '">' . T_('View') . ' &raquo;</a>';
    return $result;
}

/**
 * Generic comments/trackbacks/pingbacks counting
 *
 * @todo check this in a multiblog page...
 * @todo This should support visibility: at least in the default front office (_feedback.php), there should only the number of visible comments/trackbacks get used ({@link Item::feedback_link()}).
 *
 * @param integer
 * @param string what to count
 * @param mixed string or array to count comments with this/these status(es)
 * @param boolean set true to count expired comments, leave on false otherwise
 */
function generic_ctp_number($post_id, $mode = 'comments', $status = 'published', $count_expired = false, $filter_by_perm = true)
{
    global $DB, $debug, $postdata, $cache_ctp_number, $preview, $servertimenow, $blog;
    if ($preview) {
        // we are in preview mode, no comments yet!
        return 0;
    }
    $show_statuses = is_admin_page() ? get_visibility_statuses('keys', array('trash', 'redirected')) : get_inskin_statuses();
    $filter_index = $filter_by_perm ? 0 : 1;
    if (!isset($cache_ctp_number) || !isset($cache_ctp_number[$filter_index][$post_id])) {
        // we need a query to count comments
        $count_SQL = new SQL();
        $count_SQL->SELECT('comment_post_ID, comment_type, comment_status, COUNT(*) AS type_count');
        $count_SQL->FROM('T_comments');
        $count_SQL->GROUP_BY('comment_post_ID, comment_type, comment_status');
        if (!empty($blog)) {
            $count_SQL->WHERE(statuses_where_clause($show_statuses, 'comment_', $blog, 'blog_comment!', $filter_by_perm));
        }
        if (!$count_expired) {
            $count_SQL->FROM_add('LEFT JOIN T_items__item_settings as expiry_setting ON comment_post_ID = iset_item_ID AND iset_name = "post_expiry_delay"');
            $count_SQL->WHERE_and('expiry_setting.iset_value IS NULL OR expiry_setting.iset_value = "" OR TIMESTAMPDIFF(SECOND, comment_date, ' . $DB->quote(date2mysql($servertimenow)) . ') < expiry_setting.iset_value');
        }
    }
    // init statuses count array
    $statuses_array = array('published' => 0, 'community' => 0, 'protected' => 0, 'private' => 0, 'review' => 0, 'draft' => 0, 'deprecated' => 0, 'trash' => 0, 'total' => 0);
    /*
     * Make sure cache is loaded for current display list:
     */
    if (!isset($cache_ctp_number) || !isset($cache_ctp_number[$filter_index])) {
        global $postIDlist, $postIDarray;
        // if( $debug ) echo "LOADING generic_ctp_number CACHE for posts: $postIDlist<br />";
        if (!empty($postIDlist)) {
            foreach ($postIDarray as $tmp_post_id) {
                // Initializes each post to nocount!
                $cache_ctp_number[$filter_index][$tmp_post_id] = array('comments' => $statuses_array, 'trackbacks' => $statuses_array, 'pingbacks' => $statuses_array, 'feedbacks' => $statuses_array);
            }
            $countall_SQL = $count_SQL;
            $countall_SQL->WHERE_and('comment_post_ID IN (' . $postIDlist . ')');
            foreach ($DB->get_results($countall_SQL->get()) as $row) {
                // detail by status, tyep and post:
                $cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's'][$row->comment_status] = $row->type_count;
                // Total for type on post:
                $cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's']['total'] += $row->type_count;
                // Total for status on post:
                $cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks'][$row->comment_status] += $row->type_count;
                // Total for post:
                $cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks']['total'] += $row->type_count;
            }
        }
    }
    /*	else
    	{
    		echo "cache set";
    	}*/
    if (!isset($cache_ctp_number[$filter_index][$post_id])) {
        // this should be extremely rare...
        // echo "CACHE not set for $post_id";
        // Initializes post to nocount!
        $cache_ctp_number[$filter_index][intval($post_id)] = array('comments' => $statuses_array, 'trackbacks' => $statuses_array, 'pingbacks' => $statuses_array, 'feedbacks' => $statuses_array);
        $count_SQL->WHERE_and('comment_post_ID = ' . intval($post_id));
        foreach ($DB->get_results($count_SQL->get()) as $row) {
            // detail by status, type and post:
            $cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's'][$row->comment_status] = $row->type_count;
            // Total for type on post:
            $cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's']['total'] += $row->type_count;
            // Total for status on post:
            $cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks'][$row->comment_status] += $row->type_count;
            // Total for post:
            $cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks']['total'] += $row->type_count;
        }
    }
    if ($mode != 'comments' && $mode != 'trackbacks' && $mode != 'pingbacks') {
        $mode = 'feedbacks';
    }
    if (is_array($status)) {
        // $status is an array and probably contains more then one visibility status
        $result = 0;
        foreach ($status as $one_status) {
            if (isset($cache_ctp_number[$filter_index][$post_id][$mode][$one_status])) {
                $result = $result + $cache_ctp_number[$filter_index][$post_id][$mode][$one_status];
            }
        }
    } elseif (isset($cache_ctp_number[$filter_index][$post_id][$mode][$status])) {
        // $status is a string with one visibility status
        $result = $cache_ctp_number[$filter_index][$post_id][$mode][$status];
    } else {
        // $status is not recognized return total feedback number
        $result = $cache_ctp_number[$filter_index][$post_id][$mode]['total'];
    }
//.........这里部分代码省略.........

 */
if (!defined('EVO_MAIN_INIT')) {
    die('Please, do not access this page directly.');
}
global $blog, $admin_url, $UserSettings, $email, $statuses;
param('email', 'string', '', true);
param('statuses', 'array:string', array('redemption', 'warning', 'suspicious3'), true);
// Create result set:
$SQL = new SQL();
$SQL->SELECT('SQL_NO_CACHE emadr_ID, emadr_address, emadr_status, emadr_last_sent_ts, emadr_sent_count, emadr_sent_last_returnerror, emadr_last_error_ts, 
( emadr_prmerror_count + emadr_tmperror_count + emadr_spamerror_count + emadr_othererror_count ) AS emadr_all_count,
emadr_prmerror_count, emadr_tmperror_count, emadr_spamerror_count, emadr_othererror_count, 
COUNT( user_ID ) AS users_count');
$SQL->FROM('T_email__address');
$SQL->FROM_add('LEFT JOIN T_users ON user_email = emadr_address');
$SQL->GROUP_BY('emadr_ID');
$count_SQL = new SQL();
$count_SQL->SELECT('SQL_NO_CACHE COUNT(emadr_ID)');
$count_SQL->FROM('T_email__address');
if (!empty($email)) {
    // Filter by email
    $email = utf8_strtolower($email);
    $SQL->WHERE_and('emadr_address LIKE ' . $DB->quote($email));
    $count_SQL->WHERE_and('emadr_address LIKE ' . $DB->quote($email));
}
if (!empty($statuses)) {
    // Filter by statuses
    $SQL->WHERE_and('emadr_status IN (' . $DB->quote($statuses) . ')');
    $count_SQL->WHERE_and('emadr_status IN (' . $DB->quote($statuses) . ')');
}
$Results = new Results($SQL->get(), 'emadr_', '---D', $UserSettings->get('results_per_page'), $count_SQL->get());

    die('Please, do not access this page directly.');
}
/**
 * View funcs
 */
require_once dirname(__FILE__) . '/_stats_view.funcs.php';
global $UserSettings, $Plugins;
$SQL = new SQL();
$SQL->SELECT('SQL_NO_CACHE hit_remote_addr, COUNT( hit_ID ) AS hit_count_by_IP');
$SQL->FROM('T_hitlog');
$SQL->GROUP_BY('hit_remote_addr');
$SQL->ORDER_BY('hit_count_by_IP DESC');
$count_SQL = new SQL();
$count_SQL->SELECT('SQL_NO_CACHE hit_ID');
$count_SQL->FROM('T_hitlog');
$count_SQL->GROUP_BY('hit_remote_addr');
$count_top_IPs = count($DB->get_col($count_SQL->get()));
$Results = new Results($SQL->get(), 'topips_', '', $UserSettings->get('results_per_page'), $count_top_IPs);
$Results->title = T_('Top IPs') . get_manual_link('top-ips');
// IP address
$Results->cols[] = array('th' => T_('IP'), 'td' => '$hit_remote_addr$', 'th_class' => 'shrinkwrap', 'td_class' => 'compact_data');
// A count of the hits
$Results->cols[] = array('th' => T_('Hits'), 'td' => '$hit_count_by_IP$', 'td_class' => 'shrinkwrap');
// Reverse DNS
$Results->cols[] = array('th' => T_('Reverse DNS'), 'td_class' => 'nowrap compact_data', 'td' => '%gethostbyaddr( #hit_remote_addr# )%%evo_flush()%');
if (($Plugin =& $Plugins->get_by_code('evo_GeoIP')) !== false) {
    // Country by GeoIP plugin
    $Plugins->trigger_event('GetAdditionalColumnsTable', array('table' => 'top_ips', 'column' => 'hit_remote_addr', 'Results' => $Results, 'order' => false));
} else {
    // No country, Display help icon
    $Results->cols[] = array('th' => T_('Country'), 'td' => '%get_manual_link( "geoip-plugin" )%', 'td_class' => 'shrinkwrap');

    /**
     * Get number of helpful votes for this user
     *
     * @param array Params
     * @return string Result
     */
    function get_reputation_helpful($params = array())
    {
        // Make sure we are not missing any param:
        $params = array_merge(array('text' => T_('%s different users found %s posted %s helpful replies.')), $params);
        global $DB;
        $comments_SQL = new SQL('Get number of helpful votes on comments for this user');
        $comments_SQL->SELECT('cmvt_user_ID AS user_ID, COUNT(*) AS cnt');
        $comments_SQL->FROM('T_comments');
        $comments_SQL->FROM_add('INNER JOIN T_comments__votes ON comment_ID = cmvt_cmt_ID');
        $comments_SQL->WHERE('comment_author_ID = ' . $this->ID);
        $comments_SQL->WHERE_and('comment_status IN ( "published", "community", "protected", "review" )');
        $comments_SQL->WHERE_and('cmvt_helpful = 1');
        $comments_SQL->GROUP_BY('user_ID');
        $links_SQL = new SQL('Get number of helpful votes on links for this user');
        $links_SQL->SELECT('fvot_user_ID AS user_ID, COUNT(*) AS cnt');
        $links_SQL->FROM('T_links');
        $links_SQL->FROM_add('INNER JOIN T_files__vote ON link_file_ID = fvot_file_ID');
        $links_SQL->WHERE('link_creator_user_ID = ' . $this->ID);
        $links_SQL->WHERE_and('fvot_like = 1');
        $links_SQL->GROUP_BY('user_ID');
        $votes = $DB->get_assoc('SELECT user_ID, SUM( cnt )
			 FROM (' . $comments_SQL->get() . ' UNION ALL ' . $links_SQL->get() . ') AS tbl
			GROUP BY user_ID');
        // Calculate total votes from all users
        $users_count = count($votes);
        $votes_count = 0;
        foreach ($votes as $user_votes) {
            $votes_count += $user_votes;
        }
        return sprintf($params['text'], '<b>' . $users_count . '</b>', $this->login, '<b>' . $votes_count . '</b>');
    }

/**
 * Get # of comments for each category in a blog
 *
 * @param integer Category ID
 * @param integer Blog ID
 */
function get_commentcount_in_category($cat_ID, $blog_ID = NULL)
{
    if (is_null($blog_ID)) {
        global $blog;
        $blog_ID = $blog;
    }
    global $DB, $number_of_comments_in_cat;
    if (!isset($number_of_comments_in_cat[(string) $blog_ID])) {
        global $posttypes_specialtypes;
        $SQL = new SQL();
        $SQL->SELECT('cat_ID, COUNT( comment_ID ) c');
        $SQL->FROM('T_comments');
        $SQL->FROM_add('LEFT JOIN T_postcats ON comment_item_ID = postcat_post_ID');
        $SQL->FROM_add('LEFT JOIN T_categories ON postcat_cat_ID = cat_id');
        $SQL->FROM_add('LEFT JOIN T_items__item ON comment_item_ID = post_id');
        $SQL->WHERE('cat_blog_ID = ' . $DB->quote($blog_ID));
        $SQL->WHERE_and('comment_type IN ( "comment", "trackback", "pingback" )');
        $SQL->WHERE_and(statuses_where_clause(get_inskin_statuses($blog_ID, 'comment'), 'comment_', $blog_ID, 'blog_comment!', true));
        // add where condition to show only those posts commetns which are visible for the current User
        $SQL->WHERE_and(statuses_where_clause(get_inskin_statuses($blog_ID, 'post'), 'post_', $blog_ID, 'blog_post!', true));
        if (!empty($posttypes_specialtypes)) {
            // Get content post types, Exclide pages, intros, sidebar links and ads
            $SQL->WHERE_and('post_ityp_ID NOT IN( ' . $DB->quote($posttypes_specialtypes) . ' )');
        }
        $SQL->GROUP_BY('cat_ID');
        $number_of_comments_in_cat[(string) $blog_ID] = $DB->get_assoc($SQL->get());
    }
    return isset($number_of_comments_in_cat[(string) $blog_ID][$cat_ID]) ? (int) $number_of_comments_in_cat[(string) $blog_ID][$cat_ID] : 0;
}