-- phpMyAdmin SQL Dump
-- version 3.4.3.2
-- http://www.phpmyadmin.net
--
-- Хост: mysql56.1gb.ru
-- Время создания: Дек 10 2012 г., 21:32
-- Версия сервера: 5.1.43
-- Версия PHP: 5.2.14

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- База данных: `gb_bsportal2`
--

DELIMITER $$
--
-- Процедуры
--
CREATE  PROCEDURE `add_forummessages`(parent_idforummessage INT, o_guid VARCHAR(36), iduser INT, frmm_body TEXT)
BEGIN

    declare cnt BIGINT DEFAULT null;
    declare parent_iright BIGINT;
    declare parent_depth BIGINT;

    SELECT COUNT(*) into cnt from `forummessages`;
    IF (cnt = 0 ) THEN
	INSERT INTO `forummessages` (`idforummessage`, `parent_idforummessage`, `o_guid`, `iduser`, `frmm_ileft`, `frmm_iright`, `frmm_depth`, `frmm_body`, `frmm_datechange`, `frmm_isdelete`) VALUES ('0', '0', '00000000-0000-0000-0000-000000000000', '0', '1', '2', '0', 'root', '2000-01-01 00:00:00', 0);	
	UPDATE `forummessages` SET `idforummessage` = 0;
    END IF;
		
    IF (CHAR_LENGTH(o_guid) <= 36 AND iduser > 1) THEN
	select `frmm_iright`, `frmm_depth` into parent_iright, parent_depth from `forummessages` as frmm where frmm.`idforummessage` = parent_idforummessage;
	UPDATE forummessages SET `frmm_ileft`=`frmm_ileft`+2,`frmm_iright`=`frmm_iright`+2 where `frmm_ileft`>parent_iright;
	UPDATE forummessages SET `frmm_iright`=`frmm_iright`+2 where `frmm_iright`>=parent_iright AND `frmm_ileft`<parent_iright;
	INSERT INTO `forummessages` (`idforummessage`, `parent_idforummessage`, `o_guid`, `iduser`, `frmm_ileft`, `frmm_iright`, `frmm_depth`, `frmm_body`, `frmm_datechange`, `frmm_isdelete`) VALUES (NULL, parent_idforummessage, o_guid, iduser, parent_iright, parent_iright+1, parent_depth+1, frmm_body, NOW(), 0);
    END IF;

END$$

CREATE  PROCEDURE `add_objects`(parent_guid CHAR(36), name VARCHAR(500), guid CHAR(36), icon INT, datechange DATETIME, ver VARCHAR(10), datever DATE, statever INT, hasreports TINYINT, useinsearch TINYINT)
BEGIN
    declare parent_iright INT DEFAULT null;
    declare parent_id INT  DEFAULT null;
    declare parent_depth TINYINT DEFAULT null;

    SELECT `o_iright`, `o_depth`, `idobject` into parent_iright, parent_depth, parent_id from objects where o_guid=parent_guid;
    IF ( parent_id IS NOT null ) THEN	
	UPDATE objects SET `o_ileft`=`o_ileft`+2,`o_iright`=`o_iright`+2 where `o_ileft`>parent_iright;
    	UPDATE objects SET `o_iright`=`o_iright`+2 where `o_iright`>=parent_iright AND `o_ileft`<parent_iright;
    	INSERT INTO `objects` (`parent_idobject`, `o_ileft`, `o_iright`, `o_depth`, `o_name`, `o_guid`, `o_icon`, `o_datechange`, `o_ver` , `o_datever` , `o_statever`, `o_hasreports`, `o_useinsearch` ) VALUES (parent_id, parent_iright, parent_iright+1, parent_depth+1, name, guid, icon, datechange, ver , datever , statever, hasreports, useinsearch );
    END IF;	      
END$$

CREATE  PROCEDURE `add_personallinks`(iduser INT, o_guid varchar(36), r_guid varchar(36))
BEGIN
    declare pl_pos INT DEFAULT null;
    declare cnt INT DEFAULT null;
    SELECT COUNT(*) into cnt from `personallinks` as pl where pl.`iduser` = iduser AND pl.`o_guid` = o_guid AND pl.`r_guid` = r_guid;
    IF (cnt = 0 ) THEN
	    SELECT MAX(pl.`pl_pos`) + 1 into pl_pos from `personallinks` as pl where pl.`iduser` = iduser;
	  	
	    IF ( pl_pos is null ) THEN
		INSERT INTO `personallinks` (`idpersonallink`, `iduser`, `o_guid`, `r_guid`, `pl_pos`) VALUES (NULL, iduser, o_guid, r_guid, 1);
	    ELSE	
		INSERT INTO `personallinks` (`idpersonallink`, `iduser`, `o_guid`, `r_guid`, `pl_pos`) VALUES (NULL, iduser, o_guid, r_guid, pl_pos);
	    END IF;	      
    END IF;	      
END$$

CREATE  PROCEDURE `add_rating_reportdata`(rd_session CHAR(32), o_guid VARCHAR(36), r_guid VARCHAR(36), iduser INT, rd_rating INT)
BEGIN

    declare rating FLOAT;
    declare l_idreportdata INT;
    declare l_hasrecord INT default NULL;
    SELECT rd.`idreportdata` INTO l_idreportdata FROM `reportdata` as rd WHERE rd.`o_guid` = o_guid AND rd.`r_guid` = r_guid AND rd.`iduser` = iduser;
    SELECT 1 INTO l_hasrecord FROM `avgreportdata` as ard WHERE ard.`o_guid` = o_guid  AND ard.`r_guid` = r_guid;

    IF (l_idreportdata) THEN
	UPDATE `reportdata` as rd SET  rd.`rd_rating` = rd_rating WHERE rd.`idreportdata` = l_idreportdata;
    ELSE
	INSERT INTO `reportdata` (`idreportdata`, `rd_session`, `o_guid`, `r_guid`, `iduser`, `rd_rating`, `rd_viewcount`) VALUES (null, rd_session, o_guid, r_guid, iduser, rd_rating, 1);
    END IF;

    SELECT IFNULL(SUM(rd.`rd_rating`) / COUNT(0),0) INTO rating from`reportdata` as rd WHERE  rd.`o_guid` = o_guid AND rd.`r_guid` = r_guid AND rd.`rd_rating` > 0; 

    IF (l_hasrecord) THEN
	UPDATE `avgreportdata` as ard SET  ard.`ard_rating` = rating WHERE ard.`o_guid` = o_guid AND ard.`r_guid` = r_guid; 
    ELSE
	INSERT INTO `avgreportdata` (`o_guid` ,`r_guid` ,`ard_rating` ,`ard_viewcount` ,`ard_msgcount` ) VALUES (o_guid, r_guid, rating, 1, 0);
    END IF;
END$$

CREATE  PROCEDURE `add_viewcount_reportdata`(session CHAR(32), o_guid VARCHAR(36), r_guid VARCHAR(36), iduser INT)
BEGIN


    declare viewcount INT;
    declare ses_viewcount INT;
    declare l_hasrecord INT default NULL;
    declare l_idreportdata INT default null;
    declare ses_idreportdata INT default null;

    SELECT 1 INTO l_hasrecord FROM `avgreportdata` as ard WHERE ard.`o_guid` = o_guid  AND ard.`r_guid` = r_guid;
    SELECT rd.`idreportdata` INTO l_idreportdata FROM `reportdata` as rd WHERE rd.`o_guid` = o_guid AND rd.`r_guid` = r_guid AND rd.`iduser` = iduser;

    IF (l_idreportdata) THEN
	SELECT rd.`idreportdata`, rd.`rd_viewcount` INTO ses_idreportdata, ses_viewcount FROM `reportdata` as rd WHERE rd.idreportdata = l_idreportdata AND rd.`rd_session` = session;
	IF ((ses_idreportdata is null) OR (ses_viewcount = 0)) THEN
	    UPDATE `reportdata` as rd SET rd.`rd_viewcount` = rd.`rd_viewcount` + 1, rd.`rd_session` = session WHERE rd.`idreportdata` = l_idreportdata;
	END IF;
    ELSE
	INSERT INTO `reportdata` (`idreportdata`, `rd_session`, `o_guid`, `r_guid`, `iduser`, `rd_rating`, `rd_viewcount`) VALUES (null, session, o_guid, r_guid, iduser, 0, 1);
    END IF;

    SELECT IFNULL(SUM(rd.`rd_viewcount`),0) INTO viewcount from`reportdata` as rd WHERE  rd.`o_guid` = o_guid AND rd.`r_guid` = r_guid; 

    IF (l_hasrecord) THEN
	UPDATE `avgreportdata` as ard SET ard.`ard_viewcount` = viewcount WHERE ard.`o_guid` = o_guid AND ard.`r_guid` = r_guid ;
    ELSE
	INSERT INTO `avgreportdata` (`o_guid` ,`r_guid` ,`ard_rating` ,`ard_viewcount` ,`ard_msgcount` ) VALUES (o_guid, r_guid, 0, viewcount, 0);
    END IF;

END$$

CREATE  PROCEDURE `clear_forummessages`()
BEGIN
	DELETE FROM `forummessages`;
	INSERT INTO `forummessages` (`idforummessage`, `parent_idforummessage`, `o_guid`, `iduser`, `frmm_ileft`, `frmm_iright`, `frmm_depth`, `frmm_body`, `frmm_datechange`, `frmm_isdelete`) VALUES ('0', '0', '00000000-0000-0000-0000-000000000000', '0', '1', '2', '1', NULL, NOW(), 0);
	UPDATE `forummessages` SET `idforummessage` = 0;

END$$

CREATE  PROCEDURE `clear_objects`()
BEGIN
    DELETE FROM objects;
    INSERT INTO `objects` (`parent_idobject`, `o_ileft`, `o_iright`, `o_depth`, `o_name`, `o_guid`, `o_icon`, `o_datechange`, `o_hasreports`, `o_useinsearch`  ) VALUES (0, 1, 2, 0, 'root', '00000000-0000-0000-0000-000000000000', 0, NOW(), 0, 0);
END$$

CREATE  PROCEDURE `create_objectsrightstmp`( users_id VARCHAR(250) )
BEGIN
    DROP TEMPORARY TABLE IF EXISTS `or_temp` ;

    CREATE TEMPORARY TABLE or_temp (    
    `idobject` INT NOT NULL,
    `sumr` INT NOT NULL,
     PRIMARY KEY (`idobject`)
    )ENGINE=MEMORY;

    SET @query = CONCAT('INSERT INTO `or_temp` SELECT `idobject`, SUM(IF( ( `deny_allow` = 0 ) , -3,  `deny_allow` )) FROM `rightsobjects` WHERE `iduser` IN (',users_id,') GROUP BY `idobject`;');
    PREPARE zxc FROM @query;
    EXECUTE zxc;

    DELETE FROM `or_temp` WHERE sumr <> 3;
END$$

CREATE  PROCEDURE `create_reportsrightstmp`( users_id VARCHAR(250), o_guid CHAR(36) )
BEGIN
    declare idobject INT DEFAULT -1;
    DROP TEMPORARY TABLE IF EXISTS `rr_temp` ;

    CREATE TEMPORARY TABLE rr_temp (    
    `idreport` INT NOT NULL,
    `sumr` INT NOT NULL,
     PRIMARY KEY (`idreport`)
    )ENGINE=MEMORY;

    SELECT obj.`idobject` into idobject from `objects` as obj where obj.`o_guid` = o_guid;

    SET @query = CONCAT('INSERT INTO `or_temp` SELECT `idobject`, SUM(IF( ( `deny_allow` = 0 ) , -3,  `deny_allow` )) FROM `rightsobjects` WHERE `iduser` IN (',users_id,') AND `idreport` IN (SELECT `idreport` FROM `objectsreports` WHERE `idobject` = ',idobject,') GROUP BY `idobject`;');
    PREPARE zxc FROM @query;
    EXECUTE zxc;

    DELETE FROM `or_temp` WHERE sumr <> 3;
END$$

CREATE  PROCEDURE `del_personallinks`(iduser INT, id INT)
BEGIN
  declare _pos INT DEFAULT null;
  SELECT `pl_pos` INTO _pos FROM `personallinks` WHERE `idpersonallink` = id and `personallinks`.`iduser` = iduser;
  DELETE FROM `personallinks` WHERE `idpersonallink` = id and `personallinks`.`iduser` = iduser;  	
  UPDATE `personallinks` SET `pl_pos` = `pl_pos` - 1 WHERE `personallinks`.`iduser` = iduser AND `pl_pos` > _pos;    	
END$$



CREATE  PROCEDURE `load_objectsAdmin`(parent_idobject INT )
BEGIN
    declare root_parent_idobject INT;
    DROP TEMPORARY TABLE IF EXISTS `o_temp` ;
    CREATE TEMPORARY TABLE o_temp (	
	`idobject` INT NOT NULL,
	`parent_idobject` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

    CREATE TEMPORARY TABLE or_temp (    
    `idobject` INT NOT NULL,
     PRIMARY KEY (`idobject`)
    )ENGINE=MEMORY;

    DROP TEMPORARY TABLE IF EXISTS `o_temp_clone` ;
    CREATE TEMPORARY TABLE o_temp_clone (	
	`idobject` INT NOT NULL,
	`parent_idobject` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

    DROP TEMPORARY TABLE IF EXISTS `or_temp_clone` ;
    CREATE TEMPORARY TABLE or_temp_clone (	
	`idobject` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

   IF ( parent_idobject = 0 ) THEN

   	SELECT obj.`idobject` into root_parent_idobject from `objects` as obj where obj.`parent_idobject` = 0 AND ((o_iright - o_ileft) > 1);
	INSERT INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`idobject` <> root_parent_idobject AND ((o_iright - o_ileft) > 1);
	INSERT INTO `o_temp_clone` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`idobject` <> root_parent_idobject AND ((o_iright - o_ileft) > 1);
	DELETE FROM `o_temp` WHERE `o_temp`.`parent_idobject` IN ( SELECT `o_temp_clone`.`idobject` FROM `o_temp_clone` );
   ELSE
	INSERT INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`parent_idobject` = parent_idobject AND ((o_iright - o_ileft) > 1);
   END IF;	      

END$$

CREATE  PROCEDURE `load_objects_admin`(parent_id INT, depth INT, r_guid VARCHAR(36))
BEGIN
    declare idreport BIGINT DEFAULT -1;
    declare ileft INT DEFAULT -1;
    declare iright INT DEFAULT -1;

    DROP TEMPORARY TABLE IF EXISTS `admin_temp` ;
    CREATE TEMPORARY TABLE `admin_temp` (
    `idobject` INT NOT NULL,
    `o_guid` VARCHAR(36) NOT NULL,
    `r_guid` VARCHAR(36) NOT NULL,
    `at_rating` INT,
    `at_viewcount` INT,
    `at_msgcnt` INT,
     PRIMARY KEY(idobject)
    )ENGINE=MEMORY;

    CREATE INDEX o_guid_admin_temp ON admin_temp (o_guid);

    SELECT rep.`idreport` into idreport FROM `reports` as rep WHERE rep.`r_guid` = r_guid;

    SELECT obj.`o_ileft`, obj.`o_iright` into ileft, iright from `objects` as obj where obj.`idobject` = parent_id;
    INSERT INTO admin_temp SELECT obj.idobject, obj.o_guid, r_guid, NULL, NULL, NULL FROM `objects` as obj where obj.`o_ileft` BETWEEN ileft+1 AND iright AND o_depth < depth;

    UPDATE `admin_temp` as at, `avgreportdata` as ard
	SET `at_rating` =  `ard_rating`,
	    `at_viewcount` = `ard_viewcount`
	WHERE at.`o_guid` = ard.`o_guid` AND at.`r_guid` = ard.`r_guid`;

    UPDATE `admin_temp` as at, `objectsreports` as objrp
	SET `at_viewcount` = 0
	WHERE at.`idobject` = objrp.`idobject` AND objrp.`idreport` = idreport
	AND `at_viewcount` IS NULL;

    UPDATE `admin_temp` as at  
	SET `at_msgcnt` =  (SELECT IFNULL(COUNT(0),0) FROM `forummessages` as frmm WHERE frmm.`o_guid` = at.`o_guid` and frmm.`frmm_isdelete`=0);

END$$


CREATE  PROCEDURE `load_objecttree_search`()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS `o_temp` ;
    CREATE TEMPORARY TABLE o_temp (	
	`idobject` INT NOT NULL,
	`parent_idobject` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

    INSERT INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`o_useinsearch` = 1;
END$$

CREATE  PROCEDURE `load_reports`(o_guid CHAR(36), userights INT, users_id VARCHAR(250))
BEGIN
    declare idobject INT DEFAULT -1;

    DROP TEMPORARY TABLE IF EXISTS `r_temp` ;
	CREATE TEMPORARY TABLE `r_temp` (
	`idreport` INT NOT NULL
	)ENGINE=MEMORY;


    CREATE TEMPORARY TABLE rr_temp (    
    `idreport` INT NOT NULL,
     PRIMARY KEY (`idreport`)
    )ENGINE=MEMORY;

    SELECT obj.`idobject` into idobject from `objects` as obj where obj.`o_guid` = o_guid;

    IF ( idobject > 0 ) THEN
	INSERT INTO r_temp SELECT rep.`idreport` FROM `reports` as rep, `objectsreports` as orl WHERE orl.`idobject` = idobject AND orl.`idreport` = rep.`idreport`; 

	IF ( userights <> 0 ) THEN
	   	INSERT INTO `rr_temp` SELECT `idreport` FROM `r_temp`; 
		SET @query = CONCAT('DELETE FROM rr_temp WHERE rr_temp.`idreport` IN ( SELECT rr.`idreport` FROM `rightsreports` as rr, `r_temp` as rt WHERE rt.`idreport` = rr.`idreport` AND rr.`iduser` IN (',users_id,') AND rr.`deny_allow` = 0 );'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;

		SET @query = CONCAT('DELETE FROM rr_temp WHERE rr_temp.`idreport` NOT IN ( SELECT rr.`idreport` FROM `rightsreports` as rr, `r_temp` as rt WHERE rt.`idreport` = rr.`idreport` AND rr.`iduser` IN (',users_id,') AND rr.`deny_allow` = 1 );'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;

		SET @query = CONCAT('DELETE FROM rr_temp WHERE rr_temp.`idreport` NOT IN ( SELECT rr.`idreport` FROM `rightsreports` as rr, `r_temp` as rt WHERE rt.`idreport` = rr.`idreport` AND rr.`iduser` IN (',users_id,') AND rr.`deny_allow` = 2 );'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;

		DELETE FROM r_temp  WHERE r_temp.`idreport` NOT IN ( SELECT rr_temp.`idreport` FROM rr_temp );
	END IF;	  
    END IF;	      
END$$

CREATE  PROCEDURE `load_reportsAdmin`(o_guid CHAR(36))
BEGIN
    declare ileft BIGINT DEFAULT -1; 	
    declare iright BIGINT DEFAULT -1;

    DROP TEMPORARY TABLE IF EXISTS `r_temp` ;
	CREATE TEMPORARY TABLE `r_temp` (
	`idreport` INT NOT NULL
	)ENGINE=MEMORY;


    SELECT obj.`o_ileft`, obj.`o_iright` into  ileft, iright from `objects` as obj where obj.`o_guid` = o_guid;
    INSERT INTO r_temp SELECT DISTINCT orl.`idreport` FROM `objects` as obj, `objectsreports` as orl  WHERE obj.`idobject` = orl.`idobject` AND obj.`o_ileft` BETWEEN ileft AND iright; 
END$$

CREATE  PROCEDURE `move_personallinks`(iduser INT, pos INT, dest INT)
BEGIN
  declare max_pos INT DEFAULT null;
  IF ( (dest = 1) OR (dest = -1) ) THEN	
  	SELECT MAX(pl.`pl_pos`) into max_pos from `personallinks` as pl where pl.`iduser` = iduser;
	  IF ( ((pos + dest) > 0) AND ( (pos + dest) < (max_pos + 1) ) ) THEN	
	    UPDATE `personallinks` SET `pl_pos` = 0 WHERE `iduser` = iduser AND `pl_pos` = pos + dest;
	    UPDATE `personallinks` SET `pl_pos` = `pl_pos` + dest WHERE `iduser` = iduser AND `pl_pos` = pos;
	    UPDATE `personallinks` SET `pl_pos` = pos WHERE `iduser` = iduser AND `pl_pos` = 0;
	  END IF;	      
  END IF;	      

END$$

CREATE PROCEDURE createtree( users_id VARCHAR(250) )
BEGIN

DECLARE l_idobject INT;
DECLARE l_parent_idobject INT;
DECLARE l_o_ileft INT;
DECLARE l_o_iright INT;
DECLARE done INT default 0;
DECLARE cursor_roots Cursor for 
	SELECT `idobject`, `o_ileft`, `o_iright`  FROM chkrt_o_temp WHERE `o_useinsearch` = 1;

DECLARE cursor_tree Cursor for 
	SELECT `idobject`, `o_ileft`, `o_iright`  FROM chkrt_o_temp WHERE `parent_idobject` = 0 AND `o_useinsearch` = 0 ORDER BY o_ileft DESC;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

	DROP TEMPORARY TABLE IF EXISTS `chkrt_o_temp` ;
	CREATE TEMPORARY TABLE chkrt_o_temp (	
	`idobject` INT NOT NULL,
	`parent_idobject` INT NOT NULL,
	`ad0` INT NOT NULL,
	`ad1` INT NOT NULL,
	`ad2` INT NOT NULL,
	`root` INT NOT NULL,
	`o_ileft` INT NOT NULL,
	`o_iright` INT NOT NULL,
	`o_useinsearch` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;


	INSERT IGNORE INTO `chkrt_o_temp` 
		SELECT `idobject`, `parent_idobject`, 0, 0, 0, 1, `o_ileft`, `o_iright`, `o_useinsearch`  FROM `objects` WHERE `parent_idobject` <> 0;


		SET @query = CONCAT('UPDATE chkrt_o_temp INNER JOIN `rightsobjects` ON (chkrt_o_temp.idobject = rightsobjects.idobject) SET  ad0 = 1 WHERE rightsobjects.`iduser` IN (',users_id,') AND rightsobjects.`deny_allow` = 0;'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;

    SET @query = CONCAT('UPDATE chkrt_o_temp INNER JOIN `rightsobjects` ON (chkrt_o_temp.idobject = rightsobjects.idobject) SET  ad1 = 1 WHERE rightsobjects.`iduser` IN (',users_id,') AND rightsobjects.`deny_allow` = 1;'  ); 
		PREPARE zxc FROM @query;
		EXECUTE zxc;

    SET @query = CONCAT('UPDATE chkrt_o_temp INNER JOIN `rightsobjects` ON (chkrt_o_temp.idobject = rightsobjects.idobject) SET  ad2 = 1 WHERE rightsobjects.`iduser` IN (',users_id,') AND rightsobjects.`deny_allow` = 2;'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;


DELETE FROM chkrt_o_temp WHERE ad0 = 1 AND o_useinsearch = 0;
DELETE FROM chkrt_o_temp WHERE (ad1 < 1 OR ad2 < 1) AND o_useinsearch = 0;

OPEN cursor_roots;
WHILE done = 0 DO 
   FETCH cursor_roots INTO l_idobject,l_o_ileft,l_o_iright;
   SELECT COUNT(*) into @c FROM `chkrt_o_temp` WHERE `o_ileft` BETWEEN l_o_ileft AND l_o_iright AND `o_useinsearch` = 0;
   IF (@c = 0) THEN 
	DELETE FROM chkrt_o_temp WHERE idobject = l_idobject;
   END IF;
END WHILE;
Close cursor_roots; 


	DROP TEMPORARY TABLE IF EXISTS `chkrt_o_temp_id` ;
	CREATE TEMPORARY TABLE chkrt_o_temp_id (	
	`idobject` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;


	INSERT IGNORE INTO `chkrt_o_temp_id` 
		SELECT `idobject` FROM chkrt_o_temp;


UPDATE chkrt_o_temp RIGHT OUTER JOIN `chkrt_o_temp_id` ON (chkrt_o_temp.parent_idobject = chkrt_o_temp_id.idobject) 
SET root = 0;
UPDATE chkrt_o_temp SET parent_idobject = 0 WHERE root = 1;

SET done=0;

OPEN cursor_tree;
WHILE done = 0 DO 
   FETCH cursor_tree INTO l_idobject,l_o_ileft,l_o_iright;
      SET l_parent_idobject = 0;
   SELECT idobject INTO l_parent_idobject FROM `chkrt_o_temp` WHERE `idobject` <> l_idobject AND l_o_ileft BETWEEN `o_ileft` AND `o_iright` ORDER BY `o_ileft` DESC LIMIT 1;
   UPDATE `chkrt_o_temp` SET `parent_idobject` = l_parent_idobject WHERE `idobject`= l_idobject;
END WHILE;
Close cursor_tree; 

END$$

CREATE PROCEDURE load_objects(parent_idobject INT, userights INT, users_id VARCHAR(250) )
BEGIN
declare root_parent_idobject INT;
DROP TEMPORARY TABLE IF EXISTS `o_temp` ;
    CREATE TEMPORARY TABLE o_temp (	
	`idobject` INT NOT NULL,
	`parent_idobject` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;
  IF ( userights = 0 ) THEN
     IF ( parent_idobject = 0 ) THEN
        SELECT obj.`idobject` into root_parent_idobject from `objects` as obj where obj.`parent_idobject` = 0;
     ELSE   
        SET root_parent_idobject = parent_idobject;
     END IF;   
        INSERT IGNORE INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`parent_idobject` = root_parent_idobject;
  ELSE
        CALL createtree(users_id);
        INSERT IGNORE INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `chkrt_o_temp` as obj WHERE obj.`parent_idobject` = parent_idobject;
  END IF;
END$$

CREATE PROCEDURE load_objecttree(idobject INT, userights INT, users_id VARCHAR(250))
BEGIN
    declare parent_idobject INT DEFAULT idobject;

    DROP TEMPORARY TABLE IF EXISTS `o_temp_tree` ;
    CREATE TEMPORARY TABLE o_temp_tree (	
	`idpobject` INT NOT NULL,
	`parent_idobject` INT NOT NULL,
	 PRIMARY KEY (`idpobject`)
	)ENGINE=MEMORY;
	
  IF ( userights = 0 ) THEN
    WHILE ( parent_idobject <> 0 ) DO	
        SELECT obj.`parent_idobject` into parent_idobject from `objects` as obj where obj.`idobject` = parent_idobject;
        INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`parent_idobject` = parent_idobject AND obj.`parent_idobject` <> 0;
    END WHILE;
  ELSE
    CALL createtree(users_id);
    WHILE ( parent_idobject <> 0 ) DO	
        SELECT obj.`parent_idobject` into parent_idobject from `chkrt_o_temp` as obj where obj.`idobject` = parent_idobject;
        INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, obj.`parent_idobject` FROM `chkrt_o_temp` as obj WHERE obj.`parent_idobject` = parent_idobject AND obj.`parent_idobject` <> 0;
    END WHILE;
  END IF;

  CALL load_objects(0, userights, users_id);
END$$

CREATE PROCEDURE load_objectpath(idobject INT, userights INT, users_id VARCHAR(250))
BEGIN
    declare parent_idobject INT DEFAULT idobject;

    DROP TEMPORARY TABLE IF EXISTS `o_temp_tree` ;
    CREATE TEMPORARY TABLE o_temp_tree (	
        `idpobject` INT NOT NULL,
        `parent_idobject` INT NOT NULL,
        `o_ileft` INT NOT NULL,
        PRIMARY KEY (`idpobject`)
    )ENGINE=MEMORY;
	
  IF ( userights = 0 ) THEN
    WHILE ( parent_idobject <> 0 ) DO	
        INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, obj.`parent_idobject`, obj.`o_ileft` FROM `objects` as obj WHERE obj.`idobject` = parent_idobject AND obj.`parent_idobject` <> 0;
        SELECT obj.`parent_idobject` into parent_idobject from `objects` as obj where obj.`idobject` = parent_idobject;
    END WHILE;
  ELSE
    CALL createtree(users_id);
    WHILE ( parent_idobject <> 0 ) DO	
        INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, obj.`parent_idobject`, obj.`o_ileft` FROM `chkrt_o_temp` as obj WHERE obj.`idobject` = parent_idobject AND obj.`parent_idobject` <> 0;
        SELECT obj.`parent_idobject` into parent_idobject from `chkrt_o_temp` as obj where obj.`idobject` = parent_idobject;
    END WHILE;
  END IF;
END$$


DELIMITER ;

-- --------------------------------------------------------

--
-- Структура таблицы `avgreportdata`
--

CREATE TABLE IF NOT EXISTS `avgreportdata` (
  `o_guid` varchar(36) NOT NULL,
  `r_guid` varchar(36) NOT NULL,
  `ard_rating` float NOT NULL,
  `ard_viewcount` int(11) NOT NULL,
  `ard_msgcount` int(11) NOT NULL,
  PRIMARY KEY (`o_guid`,`r_guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `avgreportdata`
--
-- --------------------------------------------------------

--
-- Структура таблицы `files`
--

CREATE TABLE IF NOT EXISTS `files` (
  `idfile` bigint(11) NOT NULL AUTO_INCREMENT,
  `idforummessage` bigint(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(1000) NOT NULL,
  PRIMARY KEY (`idfile`),
  KEY `idforummessage` (`idforummessage`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

--
-- Дамп данных таблицы `files`
--


-- --------------------------------------------------------

--
-- Структура таблицы `forummessages`
--

CREATE TABLE IF NOT EXISTS `forummessages` (
  `idforummessage` bigint(11) NOT NULL AUTO_INCREMENT,
  `parent_idforummessage` bigint(11) NOT NULL,
  `o_guid` varchar(36) NOT NULL,
  `iduser` int(11) NOT NULL,
  `frmm_ileft` bigint(11) NOT NULL,
  `frmm_iright` bigint(11) NOT NULL,
  `frmm_depth` bigint(4) NOT NULL,
  `frmm_body` text,
  `frmm_datechange` datetime NOT NULL,
  `frmm_isdelete` tinyint(1) NOT NULL,
  PRIMARY KEY (`idforummessage`),
  KEY `o_guid` (`o_guid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=275 ;

--
-- Дамп данных таблицы `forummessages`
--

-- --------------------------------------------------------

--
-- Структура таблицы `languages`
--

CREATE TABLE IF NOT EXISTS `languages` (
  `idlanguage` int(11) NOT NULL,
  `lang_name` varchar(25) NOT NULL,
  `lang_short` varchar(5) NOT NULL,
  PRIMARY KEY (`idlanguage`),
  UNIQUE KEY `index_lang_short` (`lang_short`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `languages`
--

INSERT INTO `languages` (`idlanguage`, `lang_name`, `lang_short`) VALUES
(419, 'russian', 'ru-ru');

-- --------------------------------------------------------

--
-- Структура таблицы `links`
--

CREATE TABLE IF NOT EXISTS `links` (
  `idlink` int(11) NOT NULL AUTO_INCREMENT,
  `idsection` int(11) NOT NULL,
  `lnk_guid` char(36) NOT NULL,
  `lnk_pos` int(11) NOT NULL,
  `lnk_type` int(11) NOT NULL,
  PRIMARY KEY (`idlink`),
  KEY `idsection` (`idsection`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=723 ;

--
-- Дамп данных таблицы `links`
--


-- --------------------------------------------------------

--
-- Структура таблицы `objectlinks`
--

CREATE TABLE IF NOT EXISTS `objectlinks` (
  `iduser` int(11) NOT NULL,
  `idobject` int(11) NOT NULL,
  `ol_type` tinyint(4) NOT NULL,
  `ol_notificate` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`iduser`,`idobject`,`ol_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `objectlinks`
--

-- --------------------------------------------------------

--
-- Структура таблицы `objects`
--

CREATE TABLE IF NOT EXISTS `objects` (
  `idobject` int(11) NOT NULL AUTO_INCREMENT,
  `parent_idobject` int(11) NOT NULL,
  `o_ileft` int(11) NOT NULL,
  `o_iright` int(11) NOT NULL,
  `o_depth` int(11) NOT NULL,
  `o_name` varchar(500) NOT NULL,
  `o_guid` char(36) NOT NULL,
  `o_icon` int(11) NOT NULL,
  `o_datechange` datetime NOT NULL,
  `o_ver` varchar(10) NOT NULL DEFAULT ' ',
  `o_datever` date NOT NULL DEFAULT '0000-00-00',
  `o_statever` int(11) NOT NULL DEFAULT '0',
  `o_hasreports` tinyint(1) NOT NULL,
  `o_useinsearch` tinyint(1) NOT NULL,
  PRIMARY KEY (`idobject`),
  UNIQUE KEY `index_objects_o_guid` (`o_guid`),
  KEY `index_objects_parent_idobject` (`parent_idobject`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24178 ;

--
-- Дамп данных таблицы `objects`
--

-- --------------------------------------------------------

--
-- Структура таблицы `objectsreports`
--

CREATE TABLE IF NOT EXISTS `objectsreports` (
  `idobject` int(11) NOT NULL,
  `idreport` int(11) NOT NULL,
  `or_datechange` datetime NOT NULL,
  PRIMARY KEY (`idobject`,`idreport`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `objectsreports`
--


-- --------------------------------------------------------

--
-- Структура таблицы `personallinks`
--

CREATE TABLE IF NOT EXISTS `personallinks` (
  `idpersonallink` int(11) NOT NULL AUTO_INCREMENT,
  `iduser` int(11) NOT NULL,
  `o_guid` varchar(36) NOT NULL,
  `r_guid` varchar(36) NOT NULL,
  `pl_pos` int(11) NOT NULL,
  PRIMARY KEY (`idpersonallink`),
  KEY `iduser` (`iduser`),
  KEY `o_guid` (`o_guid`,`r_guid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=71 ;

--
-- Дамп данных таблицы `personallinks`
--


-- --------------------------------------------------------

--
-- Структура таблицы `portal`
--

CREATE TABLE IF NOT EXISTS `portal` (
  `guid` varchar(36) NOT NULL DEFAULT '-',
  PRIMARY KEY (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `portal`
--
INSERT INTO `portal` (`guid`) VALUES
('84b6567a-7777-0000-9999-8eaeac2347fc');

-- --------------------------------------------------------

--
-- Структура таблицы `reportdata`
--

CREATE TABLE IF NOT EXISTS `reportdata` (
  `idreportdata` int(11) NOT NULL AUTO_INCREMENT,
  `rd_session` char(32) NOT NULL,
  `o_guid` varchar(36) NOT NULL,
  `r_guid` varchar(36) NOT NULL,
  `iduser` int(11) NOT NULL,
  `rd_rating` int(11) NOT NULL,
  `rd_viewcount` int(11) NOT NULL,
  PRIMARY KEY (`idreportdata`),
  KEY `idobject` (`o_guid`,`r_guid`,`iduser`),
  KEY `o_guid` (`o_guid`,`r_guid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=717 ;

--
-- Дамп данных таблицы `reportdata`
--


-- --------------------------------------------------------

--
-- Структура таблицы `reportindex`
--

CREATE TABLE IF NOT EXISTS `reportindex` (
  `idobject` int(11) NOT NULL,
  `idreport` int(11) NOT NULL,
  `body` text,
  `ri_expirationdate` datetime DEFAULT NULL,
  `ri_creationdate` datetime DEFAULT NULL,
  PRIMARY KEY (`idobject`,`idreport`),
  FULLTEXT KEY `fti` (`body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `reportindex`
--

-- --------------------------------------------------------

--
-- Структура таблицы `reports`
--

CREATE TABLE IF NOT EXISTS `reports` (
  `idreport` int(11) NOT NULL AUTO_INCREMENT,
  `r_name` varchar(500) NOT NULL,
  `r_guid` char(36) NOT NULL,
  `r_position` int(11) NOT NULL,
  `r_expirationdate` datetime NOT NULL,
  `r_creationdate` datetime NOT NULL,
  `idlanguage` int(11) DEFAULT NULL,
  PRIMARY KEY (`idreport`),
  UNIQUE KEY `index_reports_r_guid` (`r_guid`),
  KEY `idlanguage` (`idlanguage`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2401 ;

--
-- Дамп данных таблицы `reports`
--

-- --------------------------------------------------------

--
-- Структура таблицы `reportsusersquery`
--

CREATE TABLE IF NOT EXISTS `reportsusersquery` (
  `o_guid` varchar(36) NOT NULL,
  `r_guid` varchar(36) NOT NULL,
  `iduser` int(11) NOT NULL,
  PRIMARY KEY (`o_guid`,`r_guid`,`iduser`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `reportsusersquery`
--


-- --------------------------------------------------------

--
-- Структура таблицы `resourcefromtables`
--

CREATE TABLE IF NOT EXISTS `resourcefromtables` (
  `idrecord` int(11) NOT NULL,
  `idtable` int(11) NOT NULL,
  `idlanguage` int(11) NOT NULL,
  `rft_columnname_text` varchar(500) NOT NULL,
  PRIMARY KEY (`idrecord`,`idtable`,`idlanguage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `resourcefromtables`
--

-- --------------------------------------------------------

--
-- Структура таблицы `rightsobjects`
--

CREATE TABLE IF NOT EXISTS `rightsobjects` (
  `iduser` int(11) NOT NULL,
  `idobject` int(11) NOT NULL,
  `deny_allow` tinyint(1) NOT NULL,
  PRIMARY KEY (`iduser`,`idobject`,`deny_allow`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `rightsobjects`
--


-- --------------------------------------------------------

--
-- Структура таблицы `rightsreports`
--

CREATE TABLE IF NOT EXISTS `rightsreports` (
  `iduser` int(11) NOT NULL,
  `idreport` int(11) NOT NULL,
  `deny_allow` tinyint(1) NOT NULL,
  PRIMARY KEY (`iduser`,`idreport`,`deny_allow`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `rightsreports`
--

-- --------------------------------------------------------

--
-- Структура таблицы `sections`
--

CREATE TABLE IF NOT EXISTS `sections` (
  `idsection` int(11) NOT NULL AUTO_INCREMENT,
  `sct_guid` char(36) NOT NULL,
  `sct_icon` varchar(500) NOT NULL,
  `sct_pos` int(11) NOT NULL,
  PRIMARY KEY (`idsection`),
  UNIQUE KEY `sct_guid` (`sct_guid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=71 ;

--
-- Дамп данных таблицы `sections`
--


-- --------------------------------------------------------

--
-- Структура таблицы `tables`
--

CREATE TABLE IF NOT EXISTS `tables` (
  `idtable` int(11) NOT NULL,
  `tbl_name` varchar(500) NOT NULL,
  PRIMARY KEY (`idtable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `tables`
--

INSERT INTO `tables` (`idtable`, `tbl_name`) VALUES
(0, 'language'),
(1, 'objects'),
(2, 'reports'),
(3, 'sections'),
(4, 'links'),
(5, 'statever');

-- --------------------------------------------------------

--
-- Структура таблицы `titleindex`
--

CREATE TABLE IF NOT EXISTS `titleindex` (
  `idobject` int(11) NOT NULL,
  `idreport` int(11) NOT NULL,
  `idlanguage` int(11) NOT NULL,
  `title` text NOT NULL,
  `o_title` text NOT NULL,
  PRIMARY KEY (`idobject`,`idreport`,`idlanguage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `titleindex`
--

-- --------------------------------------------------------

--
-- Структура таблицы `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `iduser` int(11) NOT NULL AUTO_INCREMENT,
  `user_hash` varchar(64) NOT NULL,
  `user_firstname` varchar(255) NOT NULL,
  `user_middlename` varchar(255) NOT NULL,
  `user_lastname` varchar(255) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_isgroup` tinyint(1) NOT NULL,
  `user_isadmin` tinyint(1) NOT NULL,
  `user_idlanguageinterface` int(11) NOT NULL,
  `user_idlanguagecontent` int(11) NOT NULL,
  `user_notification_answer` tinyint(1) NOT NULL,
  `user_notification_owner` tinyint(1) NOT NULL,
  `user_notification_autor` tinyint(1) NOT NULL,
  `user_notification_newversions` tinyint(1) NOT NULL,
  `user_isactive` tinyint(1) NOT NULL,
  PRIMARY KEY (`iduser`),
  UNIQUE KEY `index_users_user_login` (`user_hash`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=42 ;

--
-- Дамп данных таблицы `users`
--


-- --------------------------------------------------------

--
-- Структура таблицы `userslinks`
--

CREATE TABLE IF NOT EXISTS `userslinks` (
  `iduserlink` int(11) NOT NULL AUTO_INCREMENT,
  `iduser` int(11) NOT NULL,
  `idlink` int(11) NOT NULL,
  `ul_url` text NOT NULL,
  PRIMARY KEY (`iduserlink`),
  UNIQUE KEY `iduser` (`iduser`,`idlink`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2095 ;

--
-- Дамп данных таблицы `userslinks`
--

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
