------------------------------------------------------------------------------------------------------------------------
-- Date Created: 28.02.2008
------------------------------------------------------------------------------------------------------------------------

DROP PROCEDURE WIZARD.ROLE_INSERT;
DROP PROCEDURE WIZARD.ROLE_UPDATE;
DROP PROCEDURE WIZARD.ROLE_DELETE;
DROP PROCEDURE WIZARD.ROLE_LOADBYID;
DROP PROCEDURE WIZARD.ROLE_LOADALL;
DROP PROCEDURE WIZARD.ROLE_LOADFORVIEW;

------------------------------------------------------------------------------------------------------------------------
-- 												Insert
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE WIZARD.ROLE_INSERT(
	INOUT inout_ID INTEGER,
	IN in_NAME VARCHAR( 40 ),
	IN in_DESCRIPTION CLOB,
	IN in_THROWNTIME TIMESTAMP,
	IN in_LOGINID INTEGER
	)
	MODIFIES SQL DATA
	LANGUAGE SQL
BEGIN ATOMIC
	DECLARE vLog_ID INTEGER;

	INSERT INTO WIZARD.ROLE (
			NAME,
			DESCRIPTION
		)
	VALUES (
			in_NAME,
			in_DESCRIPTION
		);

	SET inout_ID = IDENTITY_VAL_LOCAL();

	CALL WIZARD.EventLog_WizardInsert(
		vLog_ID,
		'Insert into ROLE table',
		in_THROWNTIME,
		in_LOGINID,
		 1 ,
		 1 ,
		inout_ID
		);
END;

------------------------------------------------------------------------------------------------------------------------
-- 												Update
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE WIZARD.ROLE_UPDATE(
	IN in_ID INTEGER,
	IN in_NAME VARCHAR( 40 ),
	IN in_DESCRIPTION CLOB,
	IN in_THROWNTIME TIMESTAMP,
	IN in_LOGINID INTEGER
	)
	MODIFIES SQL DATA
	LANGUAGE SQL
BEGIN ATOMIC
	DECLARE vLog_ID INTEGER;

	UPDATE WIZARD.ROLE SET
		NAME = in_NAME,
		DESCRIPTION = in_DESCRIPTION
	WHERE
		ID = in_ID;

	CALL WIZARD.EventLog_WizardInsert(
		vLog_ID,
		'Update of ROLE table',
		in_THROWNTIME,
		in_LOGINID,
		 2 ,
		 1 ,
		in_ID
		);
END;

------------------------------------------------------------------------------------------------------------------------
-- 													Delete
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE WIZARD.ROLE_DELETE(
	IN in_ID INTEGER,
	IN in_THROWNTIME TIMESTAMP,
	IN in_LOGINID INTEGER
	)
	MODIFIES SQL DATA
	LANGUAGE SQL
BEGIN ATOMIC
	DECLARE vLog_ID INTEGER;

	DELETE FROM WIZARD.ROLE
	WHERE
		ID = in_ID;

	CALL WIZARD.EventLog_WizardInsert(
		vLog_ID,
		'Delete from ROLE table',
		in_THROWNTIME,
		in_LOGINID,
		 3 ,
		 1 ,
		in_ID
		);
END;

------------------------------------------------------------------------------------------------------------------------
-- 													LoadByID
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE WIZARD.ROLE_LOADBYID(
	IN in_ID INTEGER
	)
	READS SQL DATA
	RESULT SETS  1 
	LANGUAGE SQL
BEGIN
	DECLARE c1 CURSOR WITH RETURN FOR
	SELECT
		ID,
		NAME,
		DESCRIPTION
	FROM
		WIZARD.ROLE
	WHERE
		ID = in_ID
	FOR FETCH ONLY;

	OPEN c1;
END;

------------------------------------------------------------------------------------------------------------------------
-- 													LoadAll
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE WIZARD.ROLE_LOADALL()
READS SQL DATA
RESULT SETS  1 
LANGUAGE SQL
BEGIN
	DECLARE c1 CURSOR WITH RETURN FOR
	SELECT
		ID,
		NAME,
		DESCRIPTION
	FROM
		WIZARD.ROLE
	FOR FETCH ONLY;

	OPEN c1;
END;

------------------------------------------------------------------------------------------------------------------------
-- 													LoadForView
------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE WIZARD.ROLE_LOADFORVIEW(
	IN in_startRowIndex	INTEGER,
	IN in_maximumRows INTEGER,
    IN in_OrderByStr VARCHAR( 255 ),
	IN in_FilterStr VARCHAR( 255 )
	)
READS SQL DATA
RESULT SETS  2 
LANGUAGE SQL
BEGIN
	DECLARE vSelectStatement VARCHAR( 2000 );
	DECLARE vSelectCountStatement VARCHAR( 2000 );
	
    DECLARE vFirstRowIndex, vLastRowIndex INTEGER;

	DECLARE c1 CURSOR WITH RETURN FOR vPreparedSelect;
	DECLARE c2 CURSOR WITH RETURN FOR vPreparedSelectCount;

    SET vSelectStatement = '
    	SELECT
			ID,
			NAME,
			DESCRIPTION
		FROM
			(SELECT
				ROW_NUMBER() OVER (ORDER BY ' || in_OrderByStr || ' ) AS RowNum,
				WIZARD.ROLE.ID,
				WIZARD.ROLE.NAME,
				WIZARD.ROLE.DESCRIPTION
			FROM
				WIZARD.ROLE	
        	WHERE ' || in_FilterStr || '
			ORDER BY ' || in_OrderByStr || ' 
        	)
    	WHERE
    		RowNum >= ? AND RowNum < ?
    	FOR FETCH ONLY';
	
	SET vSelectCountStatement = '
	SELECT COUNT (*) as RecordCount
	FROM
		WIZARD.ROLE	
	WHERE ' || in_FilterStr || '
    FOR FETCH ONLY';

    PREPARE vPreparedSelect FROM vSelectStatement;
	PREPARE vPreparedSelectCount FROM vSelectCountStatement;

	SET vFirstRowIndex = in_startRowIndex +  1 ;
    SET vLastRowIndex = vFirstRowIndex + in_maximumRows;
    OPEN c1 USING vFirstRowIndex, vLastRowIndex;

    OPEN c2;
END;