mysql_queries.php

来自「sabreipb 2.1.6 utf-8中文版本！」· PHP 代码 · 共 746 行 · 第 1/2 页

<?php

/*
+--------------------------------------------------------------------------
|   Invision Power Board v2.1.5
|   =============================================
|   by Matthew Mecham
|   (c) 2001 - 2005 Invision Power Services, Inc.
|   
|   =============================================
|   Web: 
|   Time: Wed, 01 Mar 2006 19:11:29 GMT
|   Release: 
|   Licence Info: 
+---------------------------------------------------------------------------
|   > $Date: 2006-02-02 17:23:13 +0000 (Thu, 02 Feb 2006) $
|   > $Revision: 133 $
|   > $Author: bfarber $
+---------------------------------------------------------------------------
|
|   > MySQL DB Queries abstraction module
|   > Module written by Matt Mecham
|   > Date started: 26th November 2003
|
|	> Module Version Number: 1.0.0
+--------------------------------------------------------------------------
*/



class sql_queries extends db_driver
{

     var $db  = "";
     var $tbl = "";
     
    /*========================================================================*/
    // Set up...             
    /*========================================================================*/  
                   
    function sql_queries( &$obj )
    {
    	$this->db = &$obj;
    	
    	if ( ! $this->db->obj['sql_tbl_prefix'] )
    	{
    		$this->db->obj['sql_tbl_prefix'] = 'ibf_';
    	}
    	
    	$this->tbl = $this->db->obj['sql_tbl_prefix'];
    }
    
    /*========================================================================*/
    
    # NEW: 2.1 (a5)
    function general_get_by_display_name( $a )
    {
    	return "SELECT id, members_display_name, name, email, mgroup, member_login_key, ip_address, login_anonymous
				FROM ".SQL_PREFIX."members
				WHERE LOWER(members_display_name)='{$a['members_display_name']}'";
    }
    
    function forums_get_active_users( $a )
    {
    	# forums.php
    	
    	return "SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time, t.forum_id
				FROM ".SQL_PREFIX."sessions s
				 LEFT JOIN ".SQL_PREFIX."topics t ON ( t.tid=s.location_1_id)
				WHERE s.location_2_type='forum' AND (s.location_2_id={$a['fid']} OR t.forum_id={$a['fid']})
				AND s.running_time > {$a['time']}
				AND s.in_error=0";
    }
    
    function forums_get_replied_topics( $a )
    {
    	return  "SELECT COUNT(DISTINCT(p.topic_id)) as max FROM ".SQL_PREFIX."topics t
				  LEFT JOIN ".SQL_PREFIX."posts p ON (p.topic_id=t.tid)
				 WHERE t.forum_id={$a['fid']} AND p.author_id={$a['mid']} AND p.new_topic=0
				 {$a['approved']} {$a['prune_filter']}";
    }
    
    function forums_get_replied_topics_actual( $a )
    {
    	return  "SELECT DISTINCT(p.author_id), t.* FROM ".SQL_PREFIX."topics t
				  LEFT JOIN ".SQL_PREFIX."posts p ON (p.topic_id=t.tid AND p.author_id={$a['mid']})
				 WHERE t.forum_id={$a['fid']} 
				 AND {$a['query']}  AND p.new_topic=0
				 ORDER BY t.pinned desc,{$a['topic_sort']} {$a['sort_key']} {$a['r_sort_by']}
				 LIMIT {$a['limit_a']}, {$a['limit_b']}";
    }
    
    function topics_check_for_mod( $a )
    {
    	# topics.php
    	
    	return "SELECT * FROM ".SQL_PREFIX."moderators WHERE forum_id={$a['fid']} AND (member_id={$a['mid']} OR (is_group=1 AND group_id={$a['gid']}))";
    	
    }
    
    
    function topics_get_posts( $a )
    {
    	# topics.php
    	
    	return "SELECT p.*,
				m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn,
				me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size, m.members_display_name
				FROM ".SQL_PREFIX."posts p
				  LEFT JOIN ".SQL_PREFIX."members m ON (p.author_id=m.id)
				  LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=m.id)
				WHERE p.pid IN(".implode(',', $a['pids']).") ORDER BY {$a['scol']} {$a['sord']}";
    	
    }
    
    function topics_get_posts_with_join( $a )
    {
    	# topics.php
    	
    	return "SELECT p.*,
				m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn,
				me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size, m.members_display_name,
				pc.*
				FROM ".SQL_PREFIX."posts p
				  LEFT JOIN ".SQL_PREFIX."members m ON (p.author_id=m.id)
				  LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=m.id)
				  LEFT JOIN ".SQL_PREFIX."pfields_content pc ON (pc.member_id=p.author_id)
				WHERE p.pid IN(".implode(',', $a['pids']).") ORDER BY {$a['scol']} {$a['sord']}";
    	
    }
    
    function topics_get_active_users( $a )
    {
    	# topics.php
    	
    	return "SELECT s.member_id, s.member_name, s.member_group, s.id, s.login_type, s.location, s.running_time
				FROM ".SQL_PREFIX."sessions s
				WHERE s.location_1_type='topic' AND s.location_1_id={$a['tid']}
				AND s.running_time > {$a['time']}
				AND s.in_error=0";
	}

	# Deleted			
	function topics_replace_topic_read( $a )
	{
		# topics.php
		# Not got REPLACE INTO? Use delete from .. where, then insert into ... set...
		
		return "REPLACE INTO ".SQL_PREFIX."topics_read SET read_tid={$a['tid']},read_mid={$a['mid']},read_date={$a['date']}";	
	}

	
	function post_topic_tracker( $a )
	{
		#post
		
		return "SELECT tr.trid, tr.topic_id, m.name, m.email, m.id, m.email_full, m.language, m.org_perm_id, m.mgroup, m.mgroup_others, m.last_activity, t.title, t.forum_id, t.approved
				FROM ".SQL_PREFIX."tracker tr, ".SQL_PREFIX."topics t,".SQL_PREFIX."members m
				WHERE tr.topic_id='{$a['tid']}'
				AND tr.member_id=m.id
				AND m.id <> {$a['mid']}
				AND t.tid=tr.topic_id
				AND ( ( tr.topic_track_type='delayed' AND m.last_activity > {$a['last_post']} ) OR tr.topic_track_type='immediate' )";
	
	}
	
	#REMOVED
	/*function post_forum_tracker( $a )
	{
		#post
		
		return "SELECT tr.frid, m.name, m.email, m.id, m.language, m.last_activity, m.org_perm_id, g.g_perm_id
				FROM ".SQL_PREFIX."forum_tracker tr,".SQL_PREFIX."members m, ".SQL_PREFIX."groups g
				WHERE tr.forum_id={$a['fid']}
				AND tr.member_id=m.id
				AND m.mgroup=g.g_id
				AND m.id <> {$a['mid']}
				AND ( ( tr.forum_track_type='delayed' AND m.last_activity < {$a['last_post']} ) OR tr.forum_track_type='immediate' )";
	}*/
	
	
	function post_get_quoted( $a )
	{
		return "select p.*,t.forum_id FROM ".SQL_PREFIX."posts p LEFT JOIN ".SQL_PREFIX."topics t ON (t.tid=p.topic_id)
				WHERE pid IN (".implode(",", $a['quoted_pids']).")";
	
	}
	
	function msg_get_msg_poster( $a )
	{
		return "SELECT m.*, g.* FROM ".SQL_PREFIX."members m, ".SQL_PREFIX."groups g WHERE m.id={$a['mid']} AND g.g_id=m.mgroup";
	}
	
	function msg_get_msg_archive( $a )
	{
		return "SELECT m.id,m.name, m.members_disable_pm, m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn,
				g.g_id, g.g_title, g.g_icon, g.g_dohtml, m.members_display_name, 
				me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size,
				mt.*, msg.*
				FROM ".SQL_PREFIX."message_topics mt
				 LEFT JOIN ".SQL_PREFIX."message_text msg ON (mt.mt_msg_id=msg.msg_id)
				 LEFT JOIN ".SQL_PREFIX."members m ON (mt.mt_from_id=m.id)
				 LEFT JOIN ".SQL_PREFIX."groups g ON (g.g_id=m.mgroup)
				 LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=m.id)
				WHERE mt.mt_owner_id={$a['mid']} AND mt.mt_date {$a['older_newer']} {$a['time_cut']} {$a['folder_query']}
				LIMIT 0, {$a['limit_b']}";
	}
	
	function msg_get_msg_to_show( $a )
	{
		return "SELECT m.id,m.name,m.members_disable_pm,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn,
				g.g_id, g.g_title, g.g_icon, g.g_dohtml, m.members_display_name,
				me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size,
				mt.*, msg.*
				FROM ".SQL_PREFIX."message_topics mt
				 LEFT JOIN ".SQL_PREFIX."message_text msg ON (mt.mt_msg_id=msg.msg_id)
				 LEFT JOIN ".SQL_PREFIX."members m ON (mt.mt_from_id=m.id)
				 LEFT JOIN ".SQL_PREFIX."groups g ON (g.g_id=m.mgroup)
				 LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=m.id)
				WHERE mt.mt_id={$a['msgid']} AND mt.mt_owner_id={$a['mid']}";
	}
	
	function msg_get_saved_msg( $a )
	{
		return "SELECT m.id, m.name, m.members_display_name, m.members_disable_pm, mt.*, msg.*
				FROM ".SQL_PREFIX."message_topics mt
				 LEFT JOIN ".SQL_PREFIX."message_text msg ON (mt.mt_msg_id=msg.msg_id)
				 LEFT JOIN ".SQL_PREFIX."members m ON (mt.mt_to_id=m.id)
				WHERE mt.mt_id={$a['msgid']} AND mt.mt_owner_id={$a['mid']}";
	}
	#IPB 2.1: Changed m.members_display_name AND LOWER(m.members_display_name)
    function msg_get_cc_users( $a )
    {

            return "SELECT m.mgroup_others, m.id, m.name, m.members_disable_pm, m.members_display_name, m.msg_total, m.view_pop, m.email_pm, m.language, m.email, me.vdirs, g.g_max_messages, g.g_use_pm FROM ".SQL_PREFIX."members m
                    INNER JOIN ".SQL_PREFIX."groups g ON (m.mgroup=g.g_id)
                    LEFT JOIN ".SQL_PREFIX."member_extra me ON (m.id=me.id)
                       WHERE LOWER(m.members_display_name) IN (".implode(",",$a['name_array']).")";


    }
	
	function msg_get_cc_blocked( $a )
	{
	
		return "SELECT m.name, m.members_display_name, m.members_disable_pm, c.allow_msg
				FROM ".SQL_PREFIX."members m, ".SQL_PREFIX."contacts c
				WHERE contact_id={$a['mid']}
				AND member_id IN (".implode(",",$a['cc_array']).") AND m.id=c.member_id";
	
	}
	
	# Changed: mem.members_display_name as from_name
	function msg_get_sent_list( $a )
	{
		return "SELECT mem.members_display_name as from_name, mem.id as from_id, mt.*, msg.msg_id, msg.msg_cc_users
				 FROM ".SQL_PREFIX."message_topics mt
				 LEFT JOIN ".SQL_PREFIX."members mem ON ( mt.mt_to_id=mem.id )
				 LEFT JOIN ".SQL_PREFIX."message_text msg ON ( mt.mt_msg_id=msg.msg_id )
				WHERE mt.mt_owner_id={$a['mid']} AND (mt.mt_from_id={$a['mid']} OR mt.mt_to_id={$a['mid']}) AND mt.mt_vid_folder='{$a['vid']}'
				ORDER BY {$a['sort']} LIMIT {$a['limita']}, {$a['limitb']}";
	}
	
	# Changed: mem.members_display_name as from_name
	function msg_get_folder_list( $a )
	{
	
		return "SELECT mt.*,mem.members_display_name as from_name, mem.id as from_id
				 FROM ".SQL_PREFIX."message_topics mt
				 LEFT JOIN ".SQL_PREFIX."members mem ON ( mt.mt_from_id=mem.id )
				WHERE mt.mt_owner_id={$a['mid']} AND mt.mt_to_id={$a['mid']}  AND mt.mt_vid_folder='{$a['vid']}'
				ORDER BY {$a['sort']} LIMIT {$a['limita']}, {$a['limitb']}";
	}
	
	# Changed: mp.members_display_name as to_name
	function msg_get_tracking( $a )
	{
		return "SELECT msg.*, mt.*, mp.members_display_name as to_name, mp.id as memid
				 FROM ".SQL_PREFIX."message_topics mt
				  LEFT JOIN ".SQL_PREFIX."message_text msg ON ( mt.mt_msg_id=msg.msg_id )
				  LEFT JOIN ".SQL_PREFIX."members mp ON (mt.mt_to_id=mp.id)
				WHERE mt.mt_from_id={$a['mid']} AND mt.mt_tracking=1";
	
	}
	
	# Updated (msg_get_new_pm_notification) in v2.1 [changed LIMIT ADDED m.members_display_name ]
	function msg_get_new_pm_notification( $a )
	{
		return "SELECT m.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,
				 m.warn_level, m.warn_lastwarn, m.members_display_name,
				 me.*,
				g.g_id, g.g_title, g.g_icon, g.g_dohtml, mt.*, msg.*
				FROM ".SQL_PREFIX."message_topics mt
				 LEFT JOIN ".SQL_PREFIX."message_text msg ON (mt.mt_msg_id=msg.msg_id)
				 LEFT JOIN ".SQL_PREFIX."members m ON (mt.mt_from_id=m.id)
				 LEFT JOIN ".SQL_PREFIX."member_extra me ON (mt.mt_from_id=me.id)
				 LEFT JOIN ".SQL_PREFIX."groups g ON (g.g_id=m.mgroup)
				WHERE mt.mt_owner_id={$a['mid']} AND mt.mt_vid_folder='in' ORDER BY mt_date DESC LIMIT {$a['limit_a']},1";
	}
	
	function ucp_tracker_prune( $a )
	{
		return "SELECT tr.trid FROM ".SQL_PREFIX."tracker tr, ".SQL_PREFIX."topics t WHERE t.tid=tr.topic_id AND t.last_post < {$a['time']}";
	}
	# Changed
	function profile_get_all( $a )
	{
		return "SELECT m.*, me.*, s.location_1_id, s.location_2_id, s.location_1_type, s.location_2_type, s.running_time, s.location as sesslocation FROM ".SQL_PREFIX."members m
					LEFT JOIN ".SQL_PREFIX."sessions s ON (s.member_id=m.id)
					LEFT JOIN ".SQL_PREFIX."member_extra me ON ( me.id=m.id )
				WHERE m.id={$a['mid']}";
	}
	
	function profile_get_favourite( $a )
	{
		return "SELECT t.forum_id, COUNT(p.author_id) as f_posts
    				FROM ".SQL_PREFIX."posts p
    				  LEFT JOIN ".SQL_PREFIX."topics t ON ( p.topic_id=t.tid AND t.forum_id IN (".implode(",",$a['fid_array']).") )
    			    WHERE p.author_id={$a['mid']} AND t.tid IS NOT NULL
    			    GROUP BY t.forum_id
    			    ORDER BY f_posts DESC";
	}
	
	function attach_get_perms( $a )
	{
		return "SELECT p.pid, p.topic_id, t.forum_id
    				FROM ".SQL_PREFIX."posts p
    				  LEFT JOIN ".SQL_PREFIX."topics t ON ( p.topic_id=t.tid )
    			    WHERE p.pid={$a['apid']}";
	}
	
	
	function usercp_get_attachments( $a )
	{
		return "SELECT a.*, t.*, p.topic_id
				 FROM ".SQL_PREFIX."attachments a
				  LEFT JOIN ".SQL_PREFIX."posts p ON ( a.attach_pid=p.pid )
				  LEFT JOIN ".SQL_PREFIX."topics t ON ( t.tid=p.topic_id )
				 WHERE a.attach_member_id={$a['mid']}
				 ORDER BY {$a['order']}
				 LIMIT {$a['limit_a']}, {$a['limit_b']}";
	
	}
	
	function usercp_get_to_delete( $a )
	{
		return "SELECT a.*, p.topic_id, p.pid
				 FROM ".SQL_PREFIX."attachments a
				  LEFT JOIN ".SQL_PREFIX."posts p ON ( a.attach_pid=p.pid )
				 WHERE a.attach_id IN (".implode(",",$a['aid_array']).")
				 AND attach_member_id={$a['mid']}";
	
	}
	
	
	
	function stats_get_all_members( $a )
	{
		return "SELECT m.*, me.*
    			 FROM ".SQL_PREFIX."members m
    				LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=m.id)
    			 WHERE m.id IN(".implode(',', $a['member_ids']).")
    			 ORDER BY m.members_display_name";
	}
	
	function stats_get_all_members_groups( $a )
	{
		return "SELECT m.*, me.*
    			 FROM ".SQL_PREFIX."members m
    				LEFT JOIN ".SQL_PREFIX."member_extra me ON (me.id=m.id)
    			 WHERE m.mgroup IN (".implode( ',', $a['group_ids'] ).")
    			 ORDER BY m.members_display_name";
	}