 DELIMITER $$
/*****************************************************
REWIZJE
******************************************************
$Log: osoby.sql,v $
Revision 1.9  2016/07/28 10:23:51  apro
poprawka BOMBY usuwającej osoby z grupy ALL

*/


/* ************************************************************************************************************************************************************************
	BI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `osoby_bi`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `osoby_bi` BEFORE INSERT ON `osoby` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	
	SET NEW.Imiona = CONCAT(NEW.Imie1, ' ', NEW.Imie2);		
	
	IF @session.globals.Owner = 'ASTE' THEN  -- #42524
		SET NEW.last_shown_zest = CURDATE();
	END IF;
	
END;
$$

/* ************************************************************************************************************************************************************************
	AI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `osoby_ai`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `osoby_ai` AFTER INSERT ON `osoby` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	--
	IF IFNULL(@session.trigger.osoby, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.osoby = TRUE;
	
	--	Osoby do grupy sa z soba
	CALL AddOsobaToGrupyOsob(NEW.GUID, NEW.rodzaj);
	
	--	Osoby jako resources
	IF @session.globals.hr.HR_TOWR_RES_ACTIVE THEN LEAVE A; 
		CALL _res_aiu(0, NEW.IsRes, NEW.GUID, 'HR', 'osoby', NEW.stawka_h, NEW.idnuser, 0, 100.0, -1);
	END IF;
	
	--	Czy zakladac grupe jako ugrp dla oddzialu
	/*
	nie doko�czone !!!!
	SET @C_UGRP_SYNC_DIVISION = 0;
	CALL _Globals_Get('UGRP_SYNC_DIVISION', @C_UGRP_SYNC_DIVISION, @C_UGRP_SYNC_DIVISION);
	IF @C_UGRP_SYNC_DIVISION != 0 THEN
	--	Na razie NIC nie rob
		SET @C_UGRP_SYNC_DIVISION = @C_UGRP_SYNC_DIVISION;
		-- tutaj powinienem zrobic INSERT INTO erp_division_schema itd
	END IF;
	*/
	IF IFNULL(NEW.Rodzaj, 0) != 0 THEN
		IF IFNULL(NEW.NrSeriaDowoduOsobistego, '') != '' THEN
			CALL hr_cards_from_osoby_trigger('INSERT', NEW.NrSeriaDowoduOsobistego, 'ID', NEW.GUID, '', '', '');		
		END IF;
		IF IFNULL(NEW.NrPaszportu, '') != '' THEN
			CALL hr_cards_from_osoby_trigger('INSERT', NEW.NrPaszportu, 'PASSPORT', NEW.GUID, '', '', '');		
		END IF;

		IF IFNULL(NEW.KontrGUID, '') != '' THEN
			INSERT IGNORE INTO `hr_kontrah_links`
			(osobaGUID, kontrGUID, typ, kontekst)
			SELECT
				NEW.GUID, NEW.KontrGUID, 0, '(CRM)'
			FROM `kontrah` k
			WHERE
				k.GUID = NEW.KontrGUID
			;
		END IF;
		
			/*
			NIE u�ywamy tego w triggerze!!!  bo gdy kto� ma view zamiat os�b to mamy problem zak�adania osoby ciagle w 1 miejscu
		IF IFNULL(NEW.user_type, 0) != 0 THEN
			CALL _user_accounts_create_from_osoba('PARAMS', NEW.GUID, NEW.IDN, NEW.IdnUser, NEW.datas, NEW.user_type, NEW.administrator, '', ''); 
		END IF;
			*/
	END IF;
	
		
	SET @session.trigger.osoby = FALSE;
END;
$$

/* ************************************************************************************************************************************************************************
	BU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `osoby_bu`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `osoby_bu` BEFORE UPDATE ON `osoby` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	
	IF IFNULL(@session.trigger.osoby, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.osoby = TRUE;
/*
*/
	IF _IsDiff_C(NEW.Imie1, OLD.Imie1) OR _IsDiff_C(NEW.Imie2, OLD.Imie2) THEN
		SET NEW.Imiona = CONCAT(NEW.Imie1, ' ', NEW.Imie2);
	END IF;
	
	SET @session.trigger.osoby = FALSE;
END;
$$

/* ************************************************************************************************************************************************************************
	AU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `osoby_au`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `osoby_au` AFTER UPDATE ON `osoby` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	--
	IF IFNULL(@session.trigger.osoby, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.osoby = TRUE;
	
	IF @session.globals.hr.HR_TOWR_RES_ACTIVE THEN 
	--
		CALL _res_aiu(OLD.IsRes, NEW.IsRes, NEW.GUID, 'HR', 'osoby', NEW.stawka_h, NEW.idnuser, 0, 100.0, -1);
	END IF;

	
	IF _IsDiff_C(NEW.NrSeriaDowoduOsobistego, OLD.NrSeriaDowoduOsobistego) THEN
		CALL hr_cards_from_osoby_trigger('UPDATE', OLD.NrSeriaDowoduOsobistego, 'ID', NEW.GUID, '', '', '');		
		CALL hr_cards_from_osoby_trigger('INSERT', NEW.NrSeriaDowoduOsobistego, 'ID', NEW.GUID, '', '', '');		
	/*
		IF IFNULL(OLD.NrSeriaDowoduOsobistego, '') != '' THEN
			UPDATE `hr_cards`
			SET
				DateTo = NOW()
			WHERE
				ID = OLD.NrSeriaDowoduOsobistego
				AND
				OsobaGUID = NEW.GUID
				AND
				Kind = 'ID'
			;
		END IF;
		
		IF IFNULL(NEW.NrSeriaDowoduOsobistego, '') != '' THEN
			INSERT INTO `hr_cards`
				(`ID`, `Kind`, `OsobaGUID`)
				VALUES
				(NEW.NrSeriaDowoduOsobistego, 'ID', NEW.GUID)
			ON DUPLICATE KEY
			UPDATE
				`OsobaGuid` = NEW.GUID
				
			;
		END IF;
	*/	
	END IF;

	IF NOT(NEW.NrPaszportu <=> OLD.NrPaszportu) THEN
		CALL hr_cards_from_osoby_trigger('UPDATE', OLD.NrPaszportu, 'PASSPORT', NEW.GUID, '', '', '');		
		CALL hr_cards_from_osoby_trigger('INSERT', NEW.NrPaszportu, 'PASSPORT', NEW.GUID, '', '', '');		
	END IF;

	
			/*
			NIE u�ywamy tego w triggerze!!!  bo gdy kto� ma view zamiat os�b to mamy problem zak�adania osoby ciagle w 1 miejscu
	IF IFNULL(NEW.Rodzaj, 0) != 0 THEN
		IF NOT(NEW.user_type <=> OLD.user_type) AND IFNULL(NEW.user_type, 0) != 0 THEN
			CALL _user_accounts_create_from_osoba('PARAMS', NEW.GUID, NEW.IDN, NEW.IdnUser, NEW.datas, NEW.user_type, NEW.administrator, OLD.datas, ''); 
		END IF;
	END IF;
			*/
	
	IF NOT(NEW.user_type <=> OLD.user_type) OR NOT(NEW.administrator <=> OLD.administrator) OR NOT(NEW.Datas <=> OLD.Datas) OR NOT(NEW.IdnUser <=> OLD.IdnUser) OR NOT(NEW.user_inactive <=> OLD.user_inactive) THEN
		SET @_IDN_Log = NULL;
		IF NOT(NEW.user_type <=> OLD.user_type) THEN
			CALL osoby_AddToLog('INSERT', @_IDN_Log, NEW.GUID, 3, 'user_type', OLD.user_type, NEW.user_type, 'zmiana ust. uzytkownika');	
		END IF;
		IF NOT(NEW.administrator <=> OLD.administrator) THEN
			CALL osoby_AddToLog('INSERT', @_IDN_Log, NEW.GUID, 3, 'administrator', OLD.administrator, NEW.administrator, 'zmiana ust. uzytkownika');	
		END IF;
		IF NOT(NEW.Datas <=> OLD.Datas) THEN
			CALL osoby_AddToLog('INSERT', @_IDN_Log, NEW.GUID, 3, 'Datas', OLD.Datas, NEW.Datas, 'zmiana ust. uzytkownika');	
		END IF;
		IF NOT(NEW.IdnUser <=> OLD.IdnUser) THEN
			CALL osoby_AddToLog('INSERT', @_IDN_Log, NEW.GUID, 3, 'IdnUser', OLD.IdnUser, NEW.IdnUser, 'zmiana ust. uzytkownika');	
		END IF;
		IF NOT(NEW.user_inactive <=> OLD.user_inactive) THEN
			CALL osoby_AddToLog('INSERT', @_IDN_Log, NEW.GUID, 3, 'user_inactive', OLD.user_inactive, NEW.user_inactive, 'zmiana ust. uzytkownika');	
		END IF;
		
	END IF;
	
	SET @session.trigger.osoby = FALSE;
END;
$$

DROP TRIGGER IF EXISTS `osoby_ad`$$

/* ************************************************************************************************************************************************************************
	AD

	DYWERSJA!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CREATE DEFINER = `root`@`localhost` TRIGGER `osoby_ad` AFTER DELETE ON `osoby` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
--	INIT Lock trigger zmienna @session.trigger.xxx
	--	IF IFNULL(@session.trigger.osoby, FALSE) THEN LEAVE A; END IF;
	--	SET @session.trigger.osoby = TRUE;

	DELETE FROM `grupy_osob` WHERE osobaguid = OLD.GUID;
	DELETE FROM `grupy_osob` WHERE grupaguid = OLD.GUID;

--	FINAL UnLock trigger zmienna @session.trigger.xxx
	--	SET @session.trigger.osoby = FALSE;
END;
$$

*/


/* ************************************************************************************************************************************************************************
	Narz�dzia 
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `osoby_AddToLog`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `osoby_AddToLog`(
	IN _MODE VARCHAR(150),
	INOUT _IDN_Log INTEGER,
	IN _Key VARCHAR(38),
	IN _Czynnosc VARCHAR(38),
	IN _FieldName VARCHAR(250),
	IN _OldValue VARCHAR(250),
	IN _NewValue VARCHAR(250),
	IN _Info VARCHAR(250)
)
/*
	CALL osoby_AddToLog('INSERT', 0, _Key, _Czynnosc, 'User_type', _OldValue, _NewValue, 'zmiana ust. uzytkownika');
*/
S_osoby_AddToLog: BEGIN
	IF _MODE = 'INSERT' THEN
	
		IF IFNULL(@session.Registration.UserLogin, '') = '' THEN
			LEAVE S_osoby_AddToLog;
		END IF;
		
		IF _IDN_Log IS NULL THEN
			CALL log_Insert(
				'INSERT', _IDN_Log,
				@session.Registration.UserLogin, 
				303, 
				_Czynnosc, _Key, @session.Registration.IP, 
				_Info, -- Info
				''
			);		
		END IF;
		INSERT INTO `logpo`
			(idnng, fld_name, `old`, `new`) 
		VALUES
			(_IDN_Log, _FieldName, IFNULL(_OldValue, ''), IFNULL(_NewValue, ''))
		;

	END IF;
	
END;
$$

DROP PROCEDURE IF EXISTS `grupy_GrupaExists`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `grupy_GrupaExists`(
	IN _MODE VARCHAR(150),
	IN _GrupaNazwa VARCHAR(38),
	IN _GrupaSymbol VARCHAR(38),
	IN _PARAM_A VARCHAR(200),
	IN _PARAM_B VARCHAR(200)

)
/*
	CALL grupy_GrupaExists('', 'Nazwa Grupy ', '', '', ''); 

*/
BEGIN
		
	IF _MODE = '' THEN
		SET @_grupy_GrupaExists = '';
		SELECT
			o.GUID
		FROM `osoby` o
		WHERE
			(o.Nazwisko = _GrupaNazwa)
		LIMIT 1	
		INTO @_grupy_GrupaExists	
		;	
		SET @_grupy_GrupaExists = IFNULL(@_grupy_GrupaExists, '');
		IF @_grupy_GrupaExists != '' THEN
			INSERT IGNORE INTO `grupy_osob`
				(osobaGUID, grupaGUID)
				VALUES
				(@_grupy_GrupaExists, @_grupy_GrupaExists	)
			;
		END IF;
		-- Na koniec
		SELECT @_grupy_GrupaExists AS GrupaGUID;
	END IF;
END$$



DELIMITER ;


/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================


*/

