PHP SQL::FROM_add方法代码示例

/**
 * Get SQL for active users which accept newsletter email
 *
 * @param array users IDs
 * @return object SQL
 */
function get_newsletter_users_sql($users_IDs)
{
    global $Settings;
    $SQL = new SQL();
    $SQL->SELECT('u.user_ID');
    $SQL->FROM('T_users u');
    $SQL->FROM_add('LEFT OUTER JOIN T_users__usersettings us ON u.user_ID = us.uset_user_ID');
    $SQL->FROM_add('AND us.uset_name = \'newsletter_news\'');
    $SQL->WHERE('u.user_ID IN ( ' . implode(', ', $users_IDs) . ' )');
    $SQL->WHERE_and('u.user_status IN ( \'activated\', \'autoactivated\' )');
    if ($Settings->get('def_newsletter_news')) {
        // If General setting "newsletter_news" = 1 we also should include all users without defined user's setting "newsletter_news"
        $SQL->WHERE_and('( us.uset_value = 1 OR us.uset_value IS NULL )');
    } else {
        // If General setting "newsletter_news" = 0 we take only users which accept newsletter email
        $SQL->WHERE_and('us.uset_value = 1');
    }
    return $SQL;
}

 /**
  * Get a count of blog members
  *
  * @return integer Members count
  */
 function get_members_count()
 {
     global $Blog, $DB;
     // Get blog owner
     $blogowner_SQL = new SQL();
     $blogowner_SQL->SELECT('user_ID, user_status');
     $blogowner_SQL->FROM('T_users');
     $blogowner_SQL->FROM_add('INNER JOIN T_blogs ON blog_owner_user_ID = user_ID');
     $blogowner_SQL->WHERE('blog_ID = ' . $DB->quote($Blog->ID));
     // Calculate what users are members of the blog
     $userperms_SQL = new SQL();
     $userperms_SQL->SELECT('user_ID, user_status');
     $userperms_SQL->FROM('T_users');
     $userperms_SQL->FROM_add('INNER JOIN T_coll_user_perms ON ( bloguser_user_ID = user_ID AND bloguser_ismember = 1 )');
     $userperms_SQL->WHERE('bloguser_blog_ID = ' . $DB->quote($Blog->ID));
     // Calculate what user groups are members of the blog
     $usergroups_SQL = new SQL();
     $usergroups_SQL->SELECT('user_ID, user_status');
     $usergroups_SQL->FROM('T_users');
     $usergroups_SQL->FROM_add('INNER JOIN T_groups ON grp_ID = user_grp_ID');
     $usergroups_SQL->FROM_add('LEFT JOIN T_coll_group_perms ON ( bloggroup_group_ID = grp_ID AND bloggroup_ismember = 1 )');
     $usergroups_SQL->WHERE('bloggroup_blog_ID = ' . $DB->quote($Blog->ID));
     $members_count_sql = 'SELECT COUNT( user_ID ) AS member_count FROM ( ' . $blogowner_SQL->get() . ' UNION ' . $userperms_SQL->get() . ' UNION ' . $usergroups_SQL->get() . ' ) AS members ' . 'WHERE members.user_status != "closed"';
     return intval($DB->get_var($members_count_sql));
 }

 * @license GNU GPL v2 - {@link http://b2evolution.net/about/gnu-gpl-license}
 *
 * @copyright (c)2009-2015 by Francois Planque - {@link http://fplanque.com/}
 * Parts of this file are copyright (c)2009 by The Evo Factory - {@link http://www.evofactory.com/}.
 *
 * @package evocore
 */
if (!defined('EVO_MAIN_INIT')) {
    die('Please, do not access this page directly.');
}
global $Blog;
// Create query
$SQL = new SQL();
$SQL->SELECT('t.*, IF( tb.itc_ityp_ID > 0, 1, 0 ) AS type_enabled');
$SQL->FROM('T_items__type AS t');
$SQL->FROM_add('LEFT JOIN T_items__type_coll AS tb ON itc_ityp_ID = ityp_ID AND itc_coll_ID = ' . $Blog->ID);
// Create result set:
$Results = new Results($SQL->get(), 'ityp_');
$Results->title = T_('Item/Post/Page types') . get_manual_link('managing-item-types');
// get reserved and default ids
global $default_ids;
$default_ids = ItemType::get_default_ids();
/**
 * Callback to build possible actions depending on post type id
 *
 */
function get_actions_for_itemtype($id)
{
    global $default_ids;
    $action = action_icon(T_('Duplicate this Post Type...'), 'copy', regenerate_url('action', 'ityp_ID=' . $id . '&action=new'));
    if (!ItemType::is_reserved($id)) {

    $s = empty($goal_filters['s']) ? '' : $goal_filters['s'];
    $cat = empty($goal_filters['cat']) ? 0 : $goal_filters['cat'];
} else {
    // Save new values to Session
    $goal_filters['final'] = $final;
    $goal_filters['s'] = $s;
    $goal_filters['cat'] = $cat;
}
// Save new filters
$Session->set('goal_filters', $goal_filters);
$Session->dbsave();
// Create query:
$SQL = new SQL();
$SQL->SELECT('g.*, gcat_name, gcat_color');
$SQL->FROM('T_track__goal AS g');
$SQL->FROM_add('LEFT JOIN T_track__goalcat ON gcat_ID = goal_gcat_ID');
if (!empty($final)) {
    // We want to filter on final goals only:
    $SQL->WHERE_and('goal_redir_url IS NULL');
}
if (!empty($s)) {
    // We want to filter on search keyword:
    // Note: we use CONCAT_WS (Concat With Separator) because CONCAT returns NULL if any arg is NULL
    $SQL->WHERE_and('CONCAT_WS( " ", goal_name, goal_key, goal_redir_url ) LIKE "%' . $DB->escape($s) . '%"');
}
if (!empty($cat)) {
    // We want to filter on category:
    $SQL->WHERE_and('goal_gcat_ID = ' . $DB->quote($cat));
}
// Create result set:
$Results = new Results($SQL->get(), 'goals_', '-A');

/**
 * Create sample comments and display a process of creating
 *
 * @param integer Blog ID
 * @param integer Number of comments
 * @param integer Number of posts
 */
function tool_create_sample_comments($blog_ID, $num_comments, $num_posts)
{
    global $DB, $posttypes_specialtypes, $localtimenow, $Hit, $Messages, $Debuglog;
    $BlogCache =& get_BlogCache();
    $selected_Blog = $BlogCache->get_by_ID($blog_ID, false, false);
    if ($selected_Blog == NULL) {
        // Incorrect blog ID, Exit here
        return;
    }
    echo T_('Creating of the sample comments...');
    evo_flush();
    /**
     * Disable log queries because it increases the memory and stops the process with error "Allowed memory size of X bytes exhausted..."
     */
    $DB->log_queries = false;
    $curr_orderby = $selected_Blog->get_setting('orderby');
    if ($curr_orderby == 'RAND') {
        $curr_orderby .= '()';
    } else {
        $curr_orderby = 'post_' . $curr_orderby;
    }
    $curr_orderdir = $selected_Blog->get_setting('orderdir');
    // find the $num_posts latest posts in blog
    $SQL = new SQL();
    $SQL->SELECT('post_ID');
    $SQL->FROM('T_items__item');
    $SQL->FROM_add('INNER JOIN T_categories ON post_main_cat_ID = cat_ID');
    $SQL->WHERE('cat_blog_ID = ' . $DB->quote($blog_ID));
    $SQL->WHERE_and('post_status = ' . $DB->quote('published'));
    // Set condition to not create sample comments for special posts
    $SQL->WHERE_and('post_ptyp_ID NOT IN ( ' . $DB->quote($posttypes_specialtypes) . ' )');
    $SQL->ORDER_BY($curr_orderby . ' ' . $curr_orderdir . ', post_ID ' . $curr_orderdir);
    $SQL->LIMIT($num_posts);
    $items_result = $DB->get_results($SQL->get(), ARRAY_A, 'Find the x latest posts in blog');
    $count = 1;
    $fix_content = 'This is an auto generated comment for testing the moderation features.
					http://www.test.com/test_comment_';
    // go through on selected items
    foreach ($items_result as $row) {
        $item_ID = $row['post_ID'];
        $ItemCache =& get_ItemCache();
        $commented_Item =& $ItemCache->get_by_ID($item_ID);
        // create $num_comments comments for each item
        for ($i = 0; $i < $num_comments; $i++) {
            $author = 'Test ' . $count;
            $email = 'test_' . $count . '@test.com';
            $url = 'http://www.test-' . rand(1, 3) . '.com/test_comment_' . $count;
            $content = $fix_content . $count;
            for ($j = 0; $j < 50; $j++) {
                // create 50 random word
                $length = rand(1, 15);
                $word = generate_random_key($length, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ');
                $content = $content . ' ' . $word;
            }
            // create and save a new comment
            $Comment = new Comment();
            $Comment->set_Item($commented_Item);
            $Comment->set('status', 'draft');
            $Comment->set('author', $author);
            $Comment->set('author_email', $email);
            $Comment->set('author_url', $url);
            $Comment->set('content', $content);
            $Comment->set('date', date('Y-m-d H:i:s', $localtimenow));
            $Comment->set('author_IP', $Hit->IP);
            $Comment->dbsave();
            $count++;
            if ($count % 100 == 0) {
                // Display a process of creating by one dot for 100 comments
                echo ' .';
                evo_flush();
            }
            // Clear all debug messages, To avoid an error about full memory
            $Debuglog->clear('all');
        }
    }
    echo ' OK.';
    $Messages->add(sprintf(T_('Created %d comments.'), $count - 1), 'success');
}

 /**
  * Restrict by members
  *
  * @param boolean TRUE to select only member of the current Blog
  */
 function where_members($members)
 {
     global $DB, $Blog;
     if (empty($members) || is_admin_page() || empty($Blog) || $Blog->get_setting('allow_access') != 'members') {
         // Don't restrict
         return;
     }
     // Get blog owner
     $blogowner_SQL = new SQL();
     $blogowner_SQL->SELECT('user_ID');
     $blogowner_SQL->FROM('T_users');
     $blogowner_SQL->FROM_add('INNER JOIN T_blogs ON blog_owner_user_ID = user_ID');
     $blogowner_SQL->WHERE('blog_ID = ' . $DB->quote($Blog->ID));
     // Calculate what users are members of the blog
     $userperms_SQL = new SQL();
     $userperms_SQL->SELECT('user_ID');
     $userperms_SQL->FROM('T_users');
     $userperms_SQL->FROM_add('INNER JOIN T_coll_user_perms ON ( bloguser_user_ID = user_ID AND bloguser_ismember = 1 )');
     $userperms_SQL->WHERE('bloguser_blog_ID = ' . $DB->quote($Blog->ID));
     // Calculate what user groups are members of the blog
     $usergroups_SQL = new SQL();
     $usergroups_SQL->SELECT('user_ID');
     $usergroups_SQL->FROM('T_users');
     $usergroups_SQL->FROM_add('INNER JOIN T_groups ON grp_ID = user_grp_ID');
     $usergroups_SQL->FROM_add('LEFT JOIN T_coll_group_perms ON ( bloggroup_group_ID = grp_ID AND bloggroup_ismember = 1 )');
     $usergroups_SQL->WHERE('bloggroup_blog_ID = ' . $DB->quote($Blog->ID));
     $members_count_sql = 'SELECT DISTINCT user_ID FROM ( ' . $blogowner_SQL->get() . ' UNION ' . $userperms_SQL->get() . ' UNION ' . $usergroups_SQL->get() . ' ) members';
     $this->WHERE_and('user_ID IN ( ' . $members_count_sql . ' ) ');
 }

/**
 * 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);
    }
}

 /**
  * Load members of a given blog
  *
  * @todo make a UNION query when we upgrade to MySQL 4
  * @param integer Blog ID to load members for
  * @param integer Limit, 0 - for unlimit
  */
 function load_blogmembers($blog_ID, $limit = 0)
 {
     global $DB, $Debuglog;
     if (isset($this->alreadyCached['blogmembers']) && isset($this->alreadyCached['blogmembers'][$blog_ID])) {
         $Debuglog->add("Already loaded <strong>{$this->objtype}(Blog #{$blog_ID} members)</strong> into cache", 'dataobjects');
         return false;
     }
     // Clear previous users to get only the members of this blog
     $this->clear();
     // Remember this special load:
     $this->alreadyCached['blogmembers'][$blog_ID] = true;
     $Debuglog->add("Loading <strong>{$this->objtype}(Blog #{$blog_ID} members)</strong> into cache", 'dataobjects');
     // Get users which are members of the blog:
     $user_perms_SQL = new SQL();
     $user_perms_SQL->SELECT('T_users.*');
     $user_perms_SQL->FROM('T_users');
     $user_perms_SQL->FROM_add('INNER JOIN T_coll_user_perms ON user_ID = bloguser_user_ID');
     $user_perms_SQL->WHERE('bloguser_blog_ID = ' . $DB->quote($blog_ID));
     $user_perms_SQL->WHERE_and('bloguser_ismember <> 0');
     // Get users which groups are members of the blog:
     $group_perms_SQL = new SQL();
     $group_perms_SQL->SELECT('T_users.*');
     $group_perms_SQL->FROM('T_users');
     $group_perms_SQL->FROM_add('INNER JOIN T_coll_group_perms ON user_grp_ID = bloggroup_group_ID');
     $group_perms_SQL->WHERE('bloggroup_blog_ID = ' . $DB->quote($blog_ID));
     $group_perms_SQL->WHERE_and('bloggroup_ismember <> 0');
     // Union two sql queries to execute one query and save an order as one list
     $users_sql = '( ' . $user_perms_SQL->get() . ' )' . ' UNION ' . '( ' . $group_perms_SQL->get() . ' )' . ' ORDER BY user_login';
     if ($limit > 0) {
         // Limit the users
         $users_sql .= ' LIMIT ' . $limit;
     }
     $users = $DB->get_results($users_sql);
     foreach ($users as $row) {
         if (!isset($this->cache[$row->user_ID])) {
             // Save reinstatiating User if it's already been added
             $this->add(new User($row));
         }
     }
     return true;
 }

 $SQL_count = new SQL();
 $SQL_count->SELECT('COUNT(ghit_ID)');
 $SQL_count->FROM('T_track__goalhit LEFT JOIN T_hitlog ON ghit_hit_ID = hit_ID');
 if (!empty($datestart)) {
     $SQL->WHERE_and('hit_datetime >= ' . $DB->quote($datestart . ' 00:00:00'));
     $SQL_count->WHERE_and('hit_datetime >= ' . $DB->quote($datestart . ' 00:00:00'));
 }
 if (!empty($datestop)) {
     $SQL->WHERE_and('hit_datetime <= ' . $DB->quote($datestop . ' 23:59:59'));
     $SQL_count->WHERE_and('hit_datetime <= ' . $DB->quote($datestop . ' 23:59:59'));
 }
 if (!empty($sess_ID)) {
     // We want to filter on the session ID:
     $operator = $exclude ? ' <> ' : ' = ';
     $SQL->WHERE_and('hit_sess_ID' . $operator . $sess_ID);
     $SQL_count->FROM_add('LEFT JOIN T_sessions ON hit_sess_ID = sess_ID');
     $SQL_count->WHERE_and('hit_sess_ID' . $operator . $sess_ID);
 }
 if (!empty($goal_name) || !empty($goal_cat)) {
     $SQL_count->FROM_add('LEFT JOIN T_track__goal ON ghit_goal_ID = goal_ID');
     if (!empty($goal_name)) {
         // We want to filter on the goal name:
         $operator = $exclude ? ' NOT LIKE ' : ' LIKE ';
         $SQL->WHERE_and('goal_name' . $operator . $DB->quote($goal_name . '%'));
         $SQL_count->WHERE_and('goal_name' . $operator . $DB->quote($goal_name . '%'));
     }
     if (!empty($goal_cat)) {
         // We want to filter on the goal category:
         $operator = $exclude ? ' != ' : ' = ';
         $SQL->WHERE_and('goal_gcat_ID' . $operator . $DB->quote($goal_cat));
         $SQL_count->WHERE_and('goal_gcat_ID' . $operator . $DB->quote($goal_cat));

/**
 * Generate .PO file
 *
 * @param string Locale
 */
function translation_generate_po_file($locale)
{
    global $DB, $locales_path, $locales;
    $po_folder_name = $locales_path . $locales[$locale]['messages'] . '/LC_MESSAGES/';
    $po_file_name = $po_folder_name . 'messages.po';
    if (!file_exists($po_file_name)) {
        if (!file_exists($locales_path . $locales[$locale]['messages'])) {
            evo_mkdir($locales_path . $locales[$locale]['messages']);
        }
        if (!file_exists($locales_path . $locales[$locale]['messages'] . '/LC_MESSAGES')) {
            evo_mkdir($locales_path . $locales[$locale]['messages'] . '/LC_MESSAGES');
        }
    }
    $locale_name = explode(' ', $locales[$locale]['name']);
    $po_content = array();
    $po_content[] = '# b2evolution - ' . $locale_name[0] . ' language file';
    $po_content[] = '# Copyright (C) ' . date('Y') . ' Francois PLANQUE';
    $po_content[] = '# This file is distributed under the same license as the b2evolution package.';
    $po_content[] = '';
    // Get the translated strings from DB
    $SQL = new SQL();
    $SQL->SELECT('iost_string, itst_standard');
    $SQL->FROM('T_i18n_original_string');
    $SQL->FROM_add('RIGHT OUTER JOIN T_i18n_translated_string ON iost_ID = itst_iost_ID');
    $SQL->WHERE('itst_locale = ' . $DB->quote($locale));
    $SQL->ORDER_BY('iost_string');
    $translated_strings = $DB->get_results($SQL->get());
    foreach ($translated_strings as $string) {
        $po_content[] = 'msgid "' . $string->iost_string . '"';
        $po_content[] = 'msgstr "' . $string->itst_standard . '"';
        $po_content[] = '';
    }
    // Write to .PO file
    $ok = save_to_file(implode("\r\n", $po_content), $po_file_name, 'w+');
    return (bool) $ok;
}

/**
 * Get thread's recipients
 *
 * @param integer Thread ID
 * @param boolean TRUE for abuse management mode
 * @return string Recipients (avatar + login)
 */
function col_thread_recipients($thread_ID, $abuse_management)
{
    global $DB, $Blog;
    $SQL = new SQL();
    $SQL->SELECT('user_login');
    $SQL->FROM('T_messaging__threadstatus mts');
    $SQL->FROM_add('LEFT JOIN T_users u ON user_ID = tsta_user_ID');
    $SQL->WHERE('tsta_thread_ID = ' . $thread_ID);
    if (!$abuse_management) {
        // Show current user only in abuse management
        global $current_User;
        $SQL->WHERE_and('tsta_user_ID != ' . $current_User->ID);
    }
    $recipients = $DB->get_col($SQL->get());
    $image_size = isset($Blog) ? $Blog->get_setting('image_size_messaging') : 'crop-top-32x32';
    return get_avatar_imgtags($recipients, true, true, $image_size, 'avatar_before_login_middle mb1', '', NULL, true, '<br />', true);
}

$UserCache->clear(true);
$UserCache->load_by_sql($SQL);
$loaded_ids = $UserCache->get_ID_array();
if (empty($loaded_ids)) {
    // 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) {

/**
 * 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;
}

}
// Get all distinct hash values from what we have duplicates and at least one file from duplicates corresponds to filters
$hash_results = $DB->get_assoc($hash_SQL->get());
$file_hash_values = array_keys($hash_results);
// set the number of count all files which will be displayed
$num_file_results = array_sum($hash_results);
if ($num_file_results > 0) {
    // Create SQL query to build a results table
    $SQL = new SQL();
    $SQL->SELECT('f.*,
		SUM( IFNULL( lvot_like, 0 ) ) as total_like,
		SUM( IFNULL( lvot_inappropriate, 0 ) ) as total_inappropriate,
		SUM( IFNULL( lvot_spam, 0 ) ) as total_spam,
		( SELECT COUNT( file_ID ) FROM T_files AS f2 WHERE f.file_hash = f2.file_hash ) AS total_duplicates');
    $SQL->FROM('T_files AS f');
    $SQL->FROM_add('LEFT JOIN T_links AS l ON l.link_file_ID = f.file_ID');
    $SQL->FROM_add('LEFT JOIN T_links__vote AS lv ON lv.lvot_link_ID = l.link_ID');
    $SQL->WHERE('f.file_hash IN ( ' . $DB->quote($file_hash_values) . ' )');
    $SQL->GROUP_BY('f.file_ID, f.file_hash');
    $SQL->ORDER_BY('f.file_hash, *, total_spam DESC, total_inappropriate DESC');
}
$Results = new Results($num_file_results ? $SQL->get() : NULL, 'fdupl_', $default_order, $UserSettings->get('results_per_page'), $num_file_results);
$Results->Cache =& get_FileCache();
$Results->Cache->clear();
$Results->title = T_('Duplicate files') . get_manual_link('file-moderation-duplicates');
/*
 * Grouping params:
 */
$Results->group_by = 'file_hash';
/*
 * Group columns:

$UserCache->clear(true);
$UserCache->load_by_sql($SQL);
$loaded_ids = $UserCache->get_ID_array();
if (empty($loaded_ids)) {
    // UserCache result is empty which means nobody wants to receive notifications
    $result_message = sprintf(T_('Could not find any moderators wanting to receive comment moderation notifications for the blogs that have comments pending moderation!'));
    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;