qSh8Dm86

1DELIMITER $$
2DROP PROCEDURE IF EXISTS GetForumsWithPermissionCheck;
3CREATE DEFINER=`root`@`%` PROCEDURE `GetForumsWithPermissionCheck`(categoryID int, siteUserID int)
4BEGIN
5        #Get vBulletin user ID using the site user ID
6        DECLARE vbUserId INT DEFAULT ( SELECT userid FROM userfield WHERE field6 = siteUserID );    
7
8        DECLARE m_forumId, m_numOfPosts, m_numOfThreads, m_hasChildren, m_isPrivate INT;
9        DECLARE m_forumName, str VARCHAR(100);
10        DECLARE lastRow, result INT DEFAULT FALSE;      
11
12        DECLARE curForums CURSOR FOR select * from forumsList;
13        DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastRow = TRUE;
14
15        DROP TABLE IF EXISTS vbForumPermissions;
16        DROP TABLE IF EXISTS usergroupMembership;
17        DROP TABLE IF EXISTS forumsList;
18
19        #Build a table with a list of all forums to display
20        CREATE TABLE forumsList AS (select forumid,replace(replace(title_clean,'&','&'),'"','') as forumName,replycount as NumOfPosts, threadcount as NumOfThreads, 0 as hasChildren, showprivate as isprivate from forum where parentid=categoryID and displayorder!=0 and options&1=1 order by displayorder);
21
22        #Build table with usergroups member belongs to. This splits the comma-separated values from the vBulletin db into a table. 
23        CREATE TABLE usergroupMembership (val CHAR(255));
24        SET @S1 = CONCAT("INSERT INTO usergroupMembership (val) VALUES ('",REPLACE((SELECT GROUP_CONCAT(DISTINCT `membergroupids`) AS data FROM `user` WHERE `userid`=vbUserId), ",", "'),('"),"');");
25        PREPARE stmt1 FROM @s1;
26        EXECUTE stmt1;
27
28        #Iterate over the table containing a list of forums to display
29        OPEN curForums;
30        iterateForums: LOOP
31                FETCH curForums INTO m_forumId, m_forumName, m_numOfPosts, m_numOfThreads, m_hasChildren, m_isPrivate;
32                IF (lastRow) THEN 
33                    LEAVE iterateForums;
34                END IF;
35
36                #Check if current forum is private
37                IF (m_isPrivate = 1) THEN 
38
39                    #Build a table with list of usergroups which have access to this forum
40                    CREATE TABLE vbForumPermissions AS (SELECT usergroupid FROM forumpermission WHERE forumid=m_forumId AND forumpermissions <> 0);
41
42                    #Cross-check the permitted usergroups against the usergroups to which the member belongs
43                    select 1 into result from vbForumPermissions where exists (select 1 from usergroupMembership join vbForumPermissions ON usergroupMembership.val = vbForumPermissions.usergroupid);
44
45                    #If user does not belong to permitted usergroup, remove the private forum from the result set
46                    IF (result = 0) THEN
47                        DELETE FROM forumsList WHERE forumid=m_forumId;
48                    END IF;
49                END IF;                 
50        END LOOP;       
51        CLOSE curForums;
52
53    SELECT * FROM forumsList;   
54
55END$$
56DELIMITER ;