 /* ************************************************************************************************************************************************************************
	narzędzia systemowe IReg i _db_globals
************************************************************************************************************************************************************************ */	

/* ************************************************************************************************************************************************************************
	_db_globals
************************************************************************************************************************************************************************ */	

DELIMITER $$
DROP FUNCTION IF EXISTS `_db_globals_Set_Value`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `_db_globals_Set_Value`(
	_MODE VARCHAR(250),
	_Key VARCHAR(250),
	_Value VARCHAR(250),
	_Descr VARCHAR(250),
	_VariableName VARCHAR(250)
) RETURNS VARCHAR(5250)
	DETERMINISTIC
/*
	'Inicjuje zmienna globalna'
	np.
	DO _db_globals_Set_Value('', 'HR_TOWR_RES_ACTIVE', 0, 'Aktywne towary jako zasoby', '?');
	DO _db_globals_Set_Value('', 'HR_TOWR_RES_ACTIVE', 0, 'Aktywne towary jako zasoby', NULL);
	a uzupelniamy VariableName 
	DO _db_globals_Set_Value('VariableName', 'HR_TOWR_RES_ACTIVE', NULL, 'Aktywne towary jako zasoby', '?');
*/	
BEGIN
	SET 
		_VariableName = 
				IF(_VariableName IN ('?', '@session.globals'),
					CONCAT('@session.globals.', _Key),
					NULLIF(_VariableName, '') 
				)
	;
	IF NOT (_MODE LIKE '%VariableName%') THEN
	--	Zwykly insert 
		INSERT INTO `_db_globals`
		SET
			`Key` = _Key,
			`Value` = _Value,
			`Descr` = NULLIF(_Descr, ''),
			`VariableName` = NULLIF(_VariableName, '')
		ON DUPLICATE KEY UPDATE 
			`Value` = _Value,
			`Descr` = IFNULL(NULLIF(_Descr, ''), Descr),
			`VariableName` = IFNULL(NULLIF(_VariableName, ''), VariableName)
		;
	ELSE
	--	TYLKO uzupelniamy VariableName (ew. Descr) bo IF NOT (_MODE LIKE '%VariableName%') THEN
		UPDATE `_db_globals` db 
		SET 
			db.VariableName = NULLIF(_VariableName, ''),
			db.Descr = NULLIF(_Descr, '')
		WHERE 
			db.Key = _Key
		;
	END IF;
	
	RETURN ROW_COUNT();
END $$
DELIMITER ;

DELIMITER $$ 
DROP FUNCTION IF EXISTS `_db_globals_Get_Value`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `_db_globals_Get_Value`(
	_MODE VARCHAR(250),
	_Key VARCHAR(38) 
) RETURNS VARCHAR(5250)
	DETERMINISTIC
/*
	Zwraca zmienna globalna z _db_globals
*/	
BEGIN
	SET 
		@_Value = NULL
	;
	SELECT 
		dg.Value 
	INTO 
		@_Value 
	FROM `_db_globals` dg
	WHERE 
		dg.Key = _Key
	;
	RETURN @_Value;
END $$
DELIMITER ;

DELIMITER $$ 
DROP FUNCTION IF EXISTS `_db_globals_Get_Value_Only_IFNULL`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `_db_globals_Get_Value_Only_IFNULL`(
	_MODE VARCHAR(250),
	_Key VARCHAR(38),
	_Current_Value VARCHAR(5250) 
) RETURNS VARCHAR(5250)
	DETERMINISTIC
/*
	Zwraca _Current_Value gdy nie jest NULL - gdy jest NULL to pobiera z bazy - ale nie zapisuje
	SET @xxx = _db_globals_Get_Value_Only_IFNULL('', 'HR_TOWR_RES_ACTIVE', @xxx);
*/	
BEGIN
	SET @_Value = IFNULL(_Current_Value, _db_globals_Get_Value(_MODE, _Key));
	RETURN @_Value;
END $$
DELIMITER ;

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `_db_globals_Get_VAR`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_db_globals_Get_VAR`(
	IN _MODE VARCHAR(250),
	IN _Key VARCHAR(38),
	OUT _Value VARCHAR(250),
	IN _DefaultValue VARCHAR(250),
	IN _Descr VARCHAR(250),
	IN _VariableName VARCHAR(250)
	)
	COMMENT 'Zwraca zmienna globalna z _db_globals'
/*
	Zwraca zmienna globalna z _db_globals
	np.
	CALL _db_globals_Get_VAR('', 'CONNECT_LIMIT_DATE_MAX', @session.globals.CONNECT_LIMIT_DATE_MAX, '2010-01-01', 'Data graniczna limitu połączeń', '?');
	CALL _db_globals_Get_VAR('VariableName', 'CONNECT_LIMIT_DATE_MAX', @session.globals.CONNECT_LIMIT_DATE_MAX, '2010-01-01', 'Data graniczna limitu połączeń', '?');
*/	
BEGIN
	SET _Value = _db_globals_Get_Value('', _Key);
	IF (_Value IS NULL) AND (NULLIF(_DefaultValue, '') IS NOT NULL) THEN
	--	Wstaw domyslna wartosc
		SET _Value = _DefaultValue;
		DO _db_globals_Set_Value('', _Key, _Value, _Descr, _VariableName);
	END IF;
	
	IF _MODE LIKE '%VariableName%' THEN
		DO _db_globals_Set_Value('VariableName', _Key, NULL, _Descr, _VariableName);
	END IF;
END $$
DELIMITER ;

DELIMITER $$ 
/* ************************************************************************************************************************************************************************
	IReg
************************************************************************************************************************************************************************ */	
DROP FUNCTION IF EXISTS `_IReg_Set_Value`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `_IReg_Set_Value`(
	_MODE VARCHAR(250),
	_GUID VARCHAR(250),
	_IdPath VARCHAR(250),
	_IdKey VARCHAR(250),
	_Period VARCHAR(250),
	_DataType VARCHAR(100),
	_VariableName VARCHAR(250),
	_VALUE VARCHAR(5250)
) RETURNS VARCHAR(5250)
	DETERMINISTIC
/*
	Wstawia Value do IRega
*/	
S_IReg_Set_Value: BEGIN
	IF _MODE = 'VariableName' THEN
	/*
		Tylko Update na VariableName o ile istnieje rekord!!!!
		z GUIDem lub wiele wartości
	*/
		IF _VariableName IS NOT NULL THEN
			IF _VariableName LIKE '@session.%.IR.' THEN
			--	jesli podaje tylko "@session.HT.IR." albo "@session.PL.IR." to łączek key z tym przedrostkiem
				SET _VariableName = CONCAT(_VariableName, _IdKey); 
			ELSE
				SET @.IReg.VariableName = _MODE; 
			END IF;
		END IF;
		IF NULLIF(_GUID, '') IS NOT NULL THEN
			UPDATE `IReg` r
			SET
				r.VariableName = NULLIF(_VariableName, '')
			WHERE
				TRUE
				AND r.GUID = _GUID
			;
		ELSE
			UPDATE `IReg` r
			SET
				r.VariableName = NULLIF(_VariableName, '')
			WHERE
				IdPath = _IdPath AND 
				IdKey = _IdKey AND 
				Rok = IFNULL(NULLIF(_Period, ''), 0)
			;
		END IF;
		RETURN ROW_COUNT();
		LEAVE S_IReg_Set_Value;
	END IF;
	
	IF NULLIF(_Value, '') IS NULL AND (_MODE != 'EDIT') THEN
		RETURN 'NULL';
	ELSE
		SET 
			_VariableName = IF(_VariableName LIKE '@session.%.IR.', CONCAT(_VariableName, _IdKey), _VariableName),
			_DataType = UPPER(_DataType)
		;
		IF IFNULL(_IdPath, '') = '' THEN
			SET _IdPath = 'INT\\SYSTEM\\MOTLAWA';
		ELSE
			IF _IdPath LIKE 'INT%' THEN
				SET _IdPath = _IdPath;
			ELSE
				SET _IdPath = CONCAT('INT\\SYSTEM\\MOTLAWA\\', _IdPath);
			END IF;
		END IF;
		
		IF @.IReg_FN.STRING IS NULL THEN
			DO _TriggerDisabled();	--	aby miec zmienne @.IReg_FN.xxx, @.Data_type_delphi.xxx etc
		END IF;
		
		CASE
			WHEN _DataType IN ('STRING', 'TEXT', 'STR', 'TXT') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.STRING,
					@.DataKey = @.DataKey.STRING,	
					@.Data_type_delphi = @.Data_type_delphi.STRING
				;
			WHEN _DataType IN ('BOOL', 'BOOLEAN') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.BOOLEAN,
					@.DataKey = @.DataKey.BOOLEAN,	
					@.Data_type_delphi = @.Data_type_delphi.BOOLEAN
				;
			WHEN _DataType IN ('INT', 'INTEGER', 'SMALLINT', 'WORD', 'LARGEINT') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.INTEGER,
					@.DataKey = @.DataKey.INTEGER,	
					@.Data_type_delphi = @.Data_type_delphi.INTEGER
				;
			WHEN _DataType IN ('FLOAT', 'DOUBLE') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.FLOAT,
					@.DataKey = @.DataKey.FLOAT,	
					@.Data_type_delphi = @.Data_type_delphi.FLOAT
				;
			WHEN _DataType IN ('DATE', 'DATETIME') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.DATE,
					@.DataKey = @.DataKey.DATE,	
					@.Data_type_delphi = @.Data_type_delphi.DATE
				;
			WHEN _DataType IN ('TIME') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.TIME,
					@.DataKey = @.DataKey.TIME,	
					@.Data_type_delphi = @.Data_type_delphi.TIME
				;
			WHEN _DataType IN ('MEMO') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.MEMO,
					@.DataKey = @.DataKey.MEMO,	
					@.Data_type_delphi = @.Data_type_delphi.MEMO
				;
			WHEN _DataType IN ('BLOB') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.BLOB,
					@.DataKey = @.DataKey.BLOB,	
					@.Data_type_delphi = @.Data_type_delphi.BLOB
				;
			ELSE	--	jako string
				SET 
					@.IReg_FN = @.IReg_FN.STRING,
					@.DataKey = @.DataKey.STRING,	
					@.Data_type_delphi = @.Data_type_delphi.STRING
				;
		END CASE;
		
		SET 
			@._DataType = _DataType,
			@._IdPath = _IdPath, 
			@._IdKey = _IdKey, 
			@._Period = IFNULL(NULLIF(_Period, ''), 0),
			@._Value = _Value
		;
		/*
		SET 
			@.IReg_FieldName := IFNULL(
					(SELECT dtm.IReg_FieldName FROM `_data_type_maps` dtm WHERE dtm.DataKey = @._DataType LIMIT 1)
					, ''
				)
		*/
		CASE
			WHEN @.IReg_FieldName IN ('ValueA', 'ValueD', 'ValueT', 'ValueX', 'ValueBlob') THEN 
				SET @._Value = @._Value; -- QUOTE(@._Value);
			WHEN @.IReg_FieldName IN ('ValueB') THEN 
				SET @._Value = _varBOOL(@._Value);
			ELSE	
				SET @._Value = @._Value;
		END CASE;
		IF _VariableName IS NOT NULL THEN
			IF _VariableName LIKE '@session.%.IR.' THEN
			--	jesli podaje tylko "@session.HT.IR." albo "@session.PL.IR." to łączek key z tym przedrostkiem
				SET _VariableName = CONCAT(_VariableName, _IdKey); 
			/*
			po co ?
			ELSE
				SET @.IReg.VariableName = _MODE; 
			*/
			END IF;
			
			/*
			po co ?
			UPDATE	`IReg` 
			SET
				VariableName = @.IReg.VariableName
			WHERE
				GUID = @.IReg.GUID
			;
			*/
		END IF;

		INSERT IGNORE INTO `IReg`
		SET
			GUID  = IFNULL(NULLIF(_GUID, ''), _Get_ID_xxxxx('', 38, @._IdPath, @._IdKey, @._Period, NULL, NULL, '')),
			IdPath = @._IdPath,
			IdKey = @._IdKey,
			Rok = @._Period,
			IdVal = @.Data_type_delphi,
			-- IdVal = IFNULL((SELECT dtm.Data_type_delphi FROM `_data_type_maps` dtm WHERE dtm.DataKey = @._DataType), DEFAULT(IdVal)),
			-- ValueA = IF((@.IReg_FieldName := IFNULL((SELECT dtm.IReg_FieldName FROM `_data_type_maps` dtm WHERE dtm.DataKey = @._DataType LIMIT 1), '')) = 'ValueA', @._Value, DEFAULT(ValueA)),
			ValueA = IF(@.IReg_FN = 'ValueA', @._Value, DEFAULT(ValueA)),
			ValueI = IF(@.IReg_FN = 'ValueI', @._Value, DEFAULT(ValueI)),	
			ValueB = IF(@.IReg_FN = 'ValueB', @._Value, DEFAULT(ValueB)),	
			ValueD = IF(@.IReg_FN = 'ValueD', @._Value, DEFAULT(ValueD)),	
			ValueT = IF(@.IReg_FN = 'ValueT', @._Value, DEFAULT(ValueT)),	
			ValueN = IF(@.IReg_FN = 'ValueN', @._Value, DEFAULT(ValueN)),	
			ValueX = IF(@.IReg_FN = 'ValueX', @._Value, DEFAULT(ValueX)),	
			ValueBlob = IF(@.IReg_FN = 'ValueBlob', @._Value, DEFAULT(ValueBlob)),
			VariableName = NULLIF(_VariableName, '')
		ON DUPLICATE KEY 
		UPDATE
			ValueA = IF(@.IReg_FN = 'ValueA', @._Value, ValueA),
			ValueI = IF(@.IReg_FN = 'ValueI', @._Value, ValueI),	
			ValueB = IF(@.IReg_FN = 'ValueB', @._Value, ValueB),	
			ValueD = IF(@.IReg_FN = 'ValueD', @._Value, ValueD),	
			ValueT = IF(@.IReg_FN = 'ValueT', @._Value, ValueT),	
			ValueN = IF(@.IReg_FN = 'ValueN', @._Value, ValueN),	
			ValueX = IF(@.IReg_FN = 'ValueX', @._Value, ValueX),	
			ValueBlob = IF(@.IReg_FN = 'ValueBlob', @._Value, ValueBlob),
			VariableName = IFNULL(NULLIF(_VariableName, ''), `IReg`.VariableName)
		;
		
		RETURN ROW_COUNT();
	END IF;
	
END $$
DELIMITER ;

DELIMITER $$ 
DROP FUNCTION IF EXISTS `_IReg_Get_Value`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `_IReg_Get_Value`(
	_MODE VARCHAR(250),
	_GUID VARCHAR(38),
	_IdPath VARCHAR(250),
	_IdKey VARCHAR(250),
	_Period VARCHAR(250),
	_DataType VARCHAR(100)
) RETURNS VARCHAR(5250)
	DETERMINISTIC
/*
	Zwraca Value z IRega
*/	
BEGIN
	IF NULLIF(_GUID, '') IS NOT NULL THEN
		SELECT 
			CASE dtm.IReg_FieldName 
				WHEN 'ValueA' THEN r.ValueA
				WHEN 'ValueI' THEN r.ValueI
				WHEN 'ValueB' THEN r.ValueB
				WHEN 'ValueD' THEN r.ValueD
				WHEN 'ValueT' THEN r.ValueT
				WHEN 'ValueN' THEN r.ValueN
				WHEN 'ValueX' THEN r.ValueX
				WHEN 'ValueBlob' THEN r.ValueBlob
				ELSE NULL
			END AS Reg_Value, -- DataKey,
			r.GUID
			-- , r.IdVal, r.*
		INTO 
			@_Value, @.IReg.GUID 
		FROM `IReg` r
		LEFT JOIN `_data_type_maps` dtm ON dtm.Data_type_delphi = IdVal
		WHERE
			TRUE
			AND r.GUID = _GUID
		LIMIT 1
		;
	ELSE
	
		SET 
			@_Value = NULL,
			_DataType = UPPER(_DataType)
		;
		IF IFNULL(_IdPath, '') = '' THEN
			SET _IdPath = 'INT\\SYSTEM\\MOTLAWA';
		ELSE
			IF _IdPath LIKE 'INT%' THEN
				SET _IdPath = _IdPath;
			ELSE
				SET _IdPath = CONCAT('INT\\SYSTEM\\MOTLAWA\\', _IdPath);
			END IF;
		END IF;
		
		IF @.IReg_FN.STRING IS NULL THEN
					-- set @wej.I := ifnull(@wej.I, 0) + 1;
			DO _TriggerDisabled();	--	aby miec zmienne @.IReg_FN.xxx, @.Data_type_delphi.xxx etc
		END IF;
		
		CASE
			WHEN _DataType IN ('STRING', 'TEXT', 'STR', 'TXT') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.STRING,
					@.DataKey = @.DataKey.STRING,	
					@.Data_type_delphi = @.Data_type_delphi.STRING
				;
			WHEN _DataType IN ('BOOL', 'BOOLEAN') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.BOOLEAN,
					@.DataKey = @.DataKey.BOOLEAN,	
					@.Data_type_delphi = @.Data_type_delphi.BOOLEAN
				;
			WHEN _DataType IN ('INT', 'INTEGER', 'SMALLINT', 'WORD', 'LARGEINT') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.INTEGER,
					@.DataKey = @.DataKey.INTEGER,	
					@.Data_type_delphi = @.Data_type_delphi.INTEGER
				;
			WHEN _DataType IN ('FLOAT', 'DOUBLE') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.FLOAT,
					@.DataKey = @.DataKey.FLOAT,	
					@.Data_type_delphi = @.Data_type_delphi.FLOAT
				;
			WHEN _DataType IN ('DATE', 'DATETIME') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.DATE,
					@.DataKey = @.DataKey.DATE,	
					@.Data_type_delphi = @.Data_type_delphi.DATE
				;
			WHEN _DataType IN ('TIME') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.TIME,
					@.DataKey = @.DataKey.TIME,	
					@.Data_type_delphi = @.Data_type_delphi.TIME
				;
			WHEN _DataType IN ('MEMO') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.MEMO,
					@.DataKey = @.DataKey.MEMO,	
					@.Data_type_delphi = @.Data_type_delphi.MEMO
				;
			WHEN _DataType IN ('BLOB') THEN 
				SET 
					@.IReg_FN = @.IReg_FN.BLOB,
					@.DataKey = @.DataKey.BLOB,	
					@.Data_type_delphi = @.Data_type_delphi.BLOB
				;
			ELSE	--	jako string
				SET 
					@.IReg_FN = @.IReg_FN.STRING,
					@.DataKey = @.DataKey.STRING,	
					@.Data_type_delphi = @.Data_type_delphi.STRING
				;
		END CASE;

		SET @._DataType = _DataType;	
		
		SELECT 
			CASE @.IReg_FN
				WHEN 'ValueA' THEN r.ValueA
				WHEN 'ValueI' THEN r.ValueI
				WHEN 'ValueB' THEN r.ValueB
				WHEN 'ValueD' THEN r.ValueD
				WHEN 'ValueT' THEN r.ValueT
				WHEN 'ValueN' THEN r.ValueN
				WHEN 'ValueX' THEN r.ValueX
				WHEN 'ValueBlob' THEN r.ValueBlob
				ELSE NULL
			END AS _Value,
			r.GUID
		INTO 
			@_Value, @.IReg.GUID 
		FROM `IReg` r
		WHERE
			IdPath = _IdPath AND 
			IdKey = _IdKey AND 
			Rok = IFNULL(NULLIF(_Period, ''), 0)
		LIMIT 1
		;
		
	END IF;
	RETURN @_Value;
END $$
DELIMITER ;

DELIMITER $$ 
DROP FUNCTION IF EXISTS `_IReg_Get_Value_Only_IFNULL`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `_IReg_Get_Value_Only_IFNULL`(
	_MODE VARCHAR(250),
	_GUID VARCHAR(38),
	_IdPath VARCHAR(250),
	_IdKey VARCHAR(250),
	_Period VARCHAR(250),
	_DataType VARCHAR(100),
	_Current_Value VARCHAR(5250)
) RETURNS VARCHAR(5250)
	DETERMINISTIC
/*
	Zwraca _Current_Value gdy nie jest NULL - gdy jest NULL to pobiera z bazy - ale nie zapisuje
*/	
BEGIN
	SET @_Value = IFNULL(_Current_Value, _IReg_Get_Value('', _GUID, _IdPath, _IdKey, _Period, _DataType));
	RETURN @_Value;
END $$
DELIMITER ;

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `_IReg_Get_VAR`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_IReg_Get_VAR`(
	IN _MODE VARCHAR(250),
	IN _GUID VARCHAR(38),
	IN _IdPath VARCHAR(250),
	IN _IdKey VARCHAR(250),
	IN _Period INTEGER,		--	_Rok
	IN _DataType VARCHAR(100),
	OUT _Value VARCHAR(250),
	IN _DefaultValue VARCHAR(250),
	IN _VariableName VARCHAR(250)
	)
	COMMENT 'Pobiera z IRega'
BEGIN
	SET _Value = _IReg_Get_Value('', _GUID, _IdPath, _IdKey, _Period, _DataType);
	IF (_Value IS NULL) AND (NULLIF(_DefaultValue, '') IS NOT NULL) THEN
	--	Wstaw domyslna wartosc
		SET _Value = _DefaultValue;
		DO _IReg_Set_Value('', _GUID, _IdPath, _IdKey, _Period, _DataType, _VariableName, _Value);
	END IF;
END $$
DELIMITER ;

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `_IReg_Get_VAR_2`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_IReg_Get_VAR_2`(
	IN _MODE VARCHAR(250),
	-- IN _GUID VARCHAR(38),	--	bez GUIDa - i tym sie rozni od `_IReg_Get_VAR`
	IN _IdPath VARCHAR(250),
	IN _IdKey VARCHAR(250),
	IN _Period INTEGER,		--	_Rok
	IN _DataType VARCHAR(100),
	OUT _Value VARCHAR(250),
	IN _DefaultValue VARCHAR(250),
	IN _VariableName VARCHAR(250)
	)
	COMMENT 'Pobiera z IRega ale bez GUIDa - i tym sie rozni od `_IReg_Get_VAR`'
BEGIN
	SET _Value = _IReg_Get_Value('', NULL, _IdPath, _IdKey, _Period, _DataType);
	IF (_Value IS NULL) AND (NULLIF(_DefaultValue, '') IS NOT NULL) THEN
	--	Wstaw domyslna wartosc
		SET _Value = _DefaultValue;
		DO _IReg_Set_Value('', NULL, _IdPath, _IdKey, _Period, _DataType, _VariableName, _Value);
	END IF;
END $$

DELIMITER ;

/*  Zmiany CVS
$Log$
*/
