MySql_Function.sql

/**
 * @filesource MySQL_Function.sql
 *
 * @category freeSN
 * @copyright Copyright by mensch.coop e.G. 2009
 * @mailto	dev [at] mensch.coop
 * @version 0.4.200901
 * @link http://mensch.coop
 */


\. RightManagement/MySql_RMFunction.sql



DROP PROCEDURE IF EXISTS getMultimediaAuthorisationListID;
DELIMITER '$';
	CREATE PROCEDURE getMultimediaAuthorisationListID(IN var_MTextID int, OUT var_ReadAuthorisation int, OUT var_WriteAuthorisation int)
	BEGIN
		select AuthReadListID, AuthWriteListID into var_ReadAuthorisation, var_WriteAuthorisation
			from losp_BasisElement where ID=var_MTextID;
 	END;
$
DELIMITER  ;



--	--------------------------------------------------------
select 'CREATE FUNCTION confirmWriteRightAspirantTo(...)';
DROP FUNCTION IF EXISTS confirmWriteRightAspirantTo;
DELIMITER '$';
	CREATE FUNCTION confirmWriteRightAspirantTo(param_PartOfID int, param_AspirantProfileID int, param_RequestProfileID int, param_State varchar(128))
		RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_AuthWriteListID INT;

		IF (select removeMTextFromClipboard(param_RequestProfileID, param_PartOfID, param_AspirantProfileID, 'AspirantWriteRight', param_State)) THEN
			set var_AuthWriteListID=(select ListID from losp_BasisElement_writeauthorisation
							where ListID=(select AuthWriteListID from losp_BasisElement
								where ID=param_PartOfID) limit 1);
			IF var_AuthWriteListID!=0 THEN
				insert into losp_BasisElement_writeauthorisation (ListID, ProfileID) values(var_AuthWriteListID, param_AspirantProfileID);
				return 1;
			END IF;
			return 0;
		END IF;
		return 0;
 	END;
$
DELIMITER  ;




select 'MultimediaText_delete';
DROP FUNCTION IF EXISTS MultimediaText_delete;
DELIMITER '$';
	CREATE FUNCTION MultimediaText_delete(param_MTextID int, param_RequestProfileID int)
	RETURNS INT
	DETERMINISTIC
	BEGIN
		call checkBEAuth(param_MTextID, param_RequestProfileID, @r, @tmp_w);
		IF (@tmp_w) THEN
			DELETE FROM losp_BasisElement WHERE ID=param_MTextID;
			return 1;
		END IF;
		return NULL;
	END;
$
DELIMITER  ;


select 'Profile_delete';
DROP FUNCTION IF EXISTS Profile_delete;
DELIMITER '$';
	CREATE FUNCTION Profile_delete(param_ProfileID int, param_RequestProfileID int)
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE done INT DEFAULT 0;
		DECLARE var_MTextID INT DEFAULT 0;
		DECLARE var_tmp_MTextID INT DEFAULT 0;
		DECLARE var_tmp_Return INT DEFAULT 0;
		DECLARE cur1 CURSOR FOR SELECT ID from losp_BasisElement where Author_ProfileID=param_ProfileID;
		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

		select MTextID into var_MTextID from losp_BE_profile where MTextID=param_ProfileID;
		call checkBEAuth(var_MTextID, param_RequestProfileID, @r, @tmp_w);

		IF (@tmp_w) THEN
			OPEN cur1;
			REPEAT
				FETCH cur1 INTO var_tmp_MTextID;
				IF NOT done THEN
					select MultimediaText_delete(var_tmp_MTextID, param_RequestProfileID) into var_tmp_Return;
					IF (var_tmp_Return=NULL) THEN
						return 0;
					END IF;
				END IF;
			UNTIL done END REPEAT;
			CLOSE cur1;

			DELETE FROM losp_BE_profile WHERE MTextID=param_ProfileID;
			return 1;
		END IF;
		return NULL;
	END;
$
DELIMITER  ;

select 'Account_delete';
DROP FUNCTION IF EXISTS Account_delete;
DELIMITER '$';
	CREATE FUNCTION Account_delete(param_AccountID int, param_egal int)
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_ProfileID INT DEFAULT 0;
		DECLARE var_tmp_Return INT DEFAULT 0;
		select ProfileID into var_ProfileID from losp_be_account where ID=param_AccountID;

		IF (var_ProfileID) THEN
			select Profile_delete(var_ProfileID, var_ProfileID) into var_tmp_Return;
			IF (var_tmp_Return=NULL) THEN
				return 0;
			END IF;
		END IF;

		DELETE FROM losp_be_account WHERE ID=param_AccountID;

		return 1;
	END;
$
DELIMITER  ;

--	@new 100408 @f
select 'CREATE FUNCTION insertMTextIntoClipboard(...) RETURNS INT';
	DROP FUNCTION IF EXISTS insertMTextIntoClipboard;
	DELIMITER '$';
		CREATE FUNCTION insertMTextIntoClipboard(param_RequestProfileID int, param_PartOfID int, param_AttachID int, param_PartOfObjVar varchar(128), param_PartOfObjVarCategory varchar(128), param_State varchar(128))
		RETURNS INT
		DETERMINISTIC
		BEGIN
			DECLARE var_StateID INT;
			DECLARE var_CategoryID INT;
			DECLARE var_PartOfObjVarID INT;
			DECLARE var_DOOBLE_AttachID INT;

			set var_StateID=(select StateID from losp_BasisClipboard_state where State=param_State);
			set var_PartOfObjVarID=(select PartOfObjVarID from losp_BasisClipboard_objvar where PartOfObjVar=param_PartOfObjVar);
			set var_CategoryID=(select CategoryID from losp_BasisClipboard_category where CategoryName=param_PartOfObjVarCategory);
			set var_DOOBLE_AttachID=(select AttachID from losp_BasisClipboard where PartOfID=param_PartOfID and PartOfObjVarID=var_PartOfObjVarID and CategoryID=var_CategoryID and AttachID=param_AttachID);

			IF var_DOOBLE_AttachID THEN
				RETURN -1;
			ELSE 
				call checkBEAuth(param_AttachID, param_RequestProfileID,@tmp_r,@w);
				IF (@tmp_r) THEN
					INSERT INTO losp_BasisClipboard (PartOfID, AttachID, AttachStateID, PartOfObjVarID, LinkerProfileID, CategoryID)
						VALUES (param_PartOfID, param_AttachID, var_StateID, var_PartOfObjVarID, param_RequestProfileID, var_CategoryID);
					return 1;
				END IF;

				RETURN -2 ;
			END IF;

			RETURN -3 ;
		END;
	$
DELIMITER  ;
--	@old 100408 @f
--	select 'CREATE FUNCTION insertMTextIntoClipboard(...) RETURNS INT';
--	DROP FUNCTION IF EXISTS insertMTextIntoClipboard;
--	DELIMITER '$';
--		CREATE FUNCTION insertMTextIntoClipboard(param_RequestProfileID int, param_PartOfID int, param_AttachID int, param_PartOfObjVar varchar(128), param_PartOfObjVarCategory varchar(128), param_State varchar(128))
--		RETURNS INT
--		DETERMINISTIC
--		BEGIN
--			DECLARE var_StateID INT;
--			DECLARE var_CategoryID INT;
--			DECLARE var_PartOfObjVarID INT;
--			DECLARE var_BCWriteRight CHAR(1);
--			DECLARE var_BOOL BOOLEAN DEFAULT 0;
--
--			set var_StateID=(select StateID from losp_BasisClipboard_state where State=param_State);
--			set var_PartOfObjVarID=(select PartOfObjVarID from losp_BasisClipboard_objvar where PartOfObjVar=param_PartOfObjVar);
--			set var_CategoryID=(select CategoryID from losp_BasisClipboard_category where CategoryName=param_PartOfObjVarCategory);
--
--			call checkBEAuth(param_AttachID, param_RequestProfileID,@tmp_r,@w);
--			set var_BCWriteRight=(select WriteAuthorisaton from  losp_BEBasisClipboardsRightManagement where PartOfID=param_PartOfID and PartOfObjVarID=var_PartOfObjVarID and CategoryID=var_CategoryID);
--
--			IF ((var_BCWriteRight='a') OR  (var_BCWriteRight='u' and param_RequestProfileID>0)) THEN
--				set var_BOOL=true;
--			ELSEIF (var_BCWriteRight='r') THEN
--				call checkBEAuth(param_PartOfID, param_RequestProfileID,@r,@tmp_w);
--				IF (@tmp_w) THEN
--					set var_BOOL=true;
--				END IF;
--			END IF;
--
--			IF (var_BOOL=true) THEN
--				INSERT INTO losp_BasisClipboard (PartOfID, AttachID, AttachStateID, PartOfObjVarID, LinkerProfileID, CategoryID)
--					VALUES (param_PartOfID, param_AttachID, var_StateID, var_PartOfObjVarID, param_RequestProfileID, var_CategoryID);
--				return 1;
--			END IF;
--
--			RETURN 0 ;
--		END;
--	$
--	DELIMITER  ;


-- @new
select 'CREATE FUNCTION removeMTextFromClipboard(...)';
DROP FUNCTION IF EXISTS removeMTextFromClipboard;
DELIMITER '$';
	CREATE FUNCTION removeMTextFromClipboard(param_RequestProfileID int, param_PartOfID int, param_AttachID int, param_PartOfObjVar varchar(128), param_CategoryName varchar(128))
		RETURNS INT
	DETERMINISTIC
 	BEGIN
--		DECLARE var_StateID INT;
		DECLARE var_PartOfObjVarID INT;
		DECLARE var_tmp_ID INT;
		DECLARE var_CategoryID INT;
		-- set var_StateID=(select StateID from losp_BasisClipboard_state where State=param_State);
		set var_PartOfObjVarID=(select PartOfObjVarID from losp_BasisClipboard_objvar where PartOfObjVar=param_PartOfObjVar);
		set var_CategoryID=(select CategoryID from losp_BasisClipboard_category where CategoryName=param_CategoryName);
		call checkBEAuth(param_PartOfID,param_RequestProfileID,@r,@var_Profile_PartOf_w);
		call checkBEAuth(param_AttachID,param_RequestProfileID,@r,@var_Profile_Attach_w);

		IF (@var_Profile_PartOf_w || @var_Profile_Attach_w)  THEN
			set var_tmp_ID=(select PartOfID from losp_BasisClipboard WHERE PartOfID=param_PartOfID and AttachID=param_AttachID and PartOfObjVarID=var_PartOfObjVarID and CategoryID=var_CategoryID);
			IF (var_tmp_ID>=0) THEN
				DELETE FROM losp_BasisClipboard
					WHERE PartOfID=param_PartOfID
						and AttachID=param_AttachID
						and CategoryID=var_CategoryID
						and PartOfObjVarID=var_PartOfObjVarID;
				return 1;
			END IF;
		END IF;
		return 0;
 	END;
$
DELIMITER  ;