Count unread post for phpBB 3.0.9 (file includes/functions.php)

gistfile1.aw

	// Count posts since last vivist
	if ($user->data['is_registered'] && $config['load_search'] && $auth->acl_get('u_search') && $auth->acl_getf_global('f_search'))
	{
		/*$ex_fid_ary = array_unique(array_merge(array_keys($auth->acl_getf('!f_read', true)), array_keys($auth->acl_getf('!f_search', true))));
		
		if ($auth->acl_get('m_approve'))
		{
			$m_approve_fid_ary = array(-1);
			$m_approve_fid_sql = '';
		}
		else if ($auth->acl_getf_global('m_approve'))
		{
			$m_approve_fid_ary = array_diff(array_keys($auth->acl_getf('!m_approve', true)), $ex_fid_ary);
			$m_approve_fid_sql = ' AND (p.post_approved = 1' . ((sizeof($m_approve_fid_ary)) ? ' OR ' . $db->sql_in_set('p.forum_id', $m_approve_fid_ary, true) : '') . ')';
		}
		else
		{
			$m_approve_fid_ary = array();
			$m_approve_fid_sql = ' AND p.post_approved = 1';
		}
		
		// New posts count
		$sql = 'SELECT COUNT(t.topic_id) as count
			FROM ' . TOPICS_TABLE . ' t
			WHERE t.topic_last_post_time > ' . $user->data['user_lastvisit'] . '
				AND t.topic_moved_id = 0' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '');
		$result = $db->sql_query($sql);
		$new_posts_count = (int) $db->sql_fetchfield('count');
		
		// Unread posts count
		$sql_where = 'AND t.topic_moved_id = 0' . str_replace(array('p.', 'post_'), array('t.', 'topic_'), $m_approve_fid_sql) . ((sizeof($ex_fid_ary)) ? 'AND ' . $db->sql_in_set('t.forum_id', $ex_fid_ary, true) : '');
		$unread_list = array();
		$unread_list = get_unread_topics($user->data['user_id'], $sql_where, 'ORDER BY t.topic_id DESC');
		
		if (!empty($unread_list))
		{
			$sql = 'SELECT COUNT(t.topic_id) as count
				FROM ' . TOPICS_TABLE . ' t
				WHERE ' . $db->sql_in_set('t.topic_id', array_keys($unread_list));
			$result = $db->sql_query($sql);
			$unread_posts_count = (int) $db->sql_fetchfield('count');
		}
		else
		{
			$unread_posts_count = 0;
		}
		
		$template->assign_vars(array(
			'NEW_POSTS_COUNT'		=> $new_posts_count,
			'UNREAD_POSTS_COUNT'	=> $unread_posts_count,
		));
	}*/
		// Include forum_id 0 (globals) in the list and then look up and include all other forums the user is authorized to read
		if (!isset($forum_ids))
		{
			$forum_ids[] = 0;
			
			$sql = 'SELECT forum_id
				FROM ' . FORUMS_TABLE;
			$result = $db->sql_query($sql);
			
			while ($row = $db->sql_fetchrow($result))
			{
				if ($auth->acl_get('f_read', $row['forum_id']))
				{
					$forum_ids[] = $row['forum_id'];
				}
			}
			
			$db->sql_freeresult($result);
		}
		
		$unread_posts_count = 0;
		
		// Unread posts count
		$sql = $db->sql_build_query('SELECT', array(
			'SELECT'	=> 'COUNT(p.post_id) as total',
			
			'FROM'		=> array(
				POSTS_TABLE => 'p'
			),
			
			'LEFT_JOIN'	=> array(
				array(
					'FROM'	=> array(FORUMS_TRACK_TABLE => 'ft'),
					'ON'	=> 'p.forum_id = ft.forum_id AND ft.user_id = ' . $user->data['user_id']
				),
				array(
					'FROM'	=> array(TOPICS_TRACK_TABLE => 'tt'),
					'ON'	=> 'p.topic_id = tt.topic_id AND tt.user_id = ' . $user->data['user_id']
				)
			),
			
			'WHERE'		=> '(p.post_time > tt.mark_time
				OR (tt.mark_time IS NULL AND p.post_time > ft.mark_time)
				OR (ft.mark_time IS NULL AND p.post_time > ' . $user->data['user_lastmark'] . '))
				AND ' . $db->sql_in_set('p.forum_id', $forum_ids)
		));
		
		$result = $db->sql_query($sql);
		$unread_posts_count = $db->sql_fetchfield('total', false, $result);
		$db->sql_freeresult($result);
		
		$template->assign_vars(array(
			//'NEW_POSTS_COUNT'		=> $new_posts_count,
			'UNREAD_POSTS_COUNT'	=> $unread_posts_count,
		));
	}