DELIMITER $$

/* ************************************************************************************************************************************************************************
	BI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_persons_bi`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_persons_bi` BEFORE INSERT ON `io_persons` FOR EACH ROW 
A: BEGIN

	IF _TriggerDisabled() THEN LEAVE A; END IF;
	
	/* ******************************************************************************
		TIMESTAMP
	****************************************************************************** */
	SET NEW.time_create = CURRENT_TIMESTAMP();
	
	/* ******************************************************************************
		Status
	****************************************************************************** */
	IF NEW.Status = 'NN' AND NEW.int_2_autoID = 0 AND IFNULL(NEW.NrPESEL, '') = '' AND IFNULL(NEW.NrDokumentu, '') = '' THEN
		SET 
			NEW.Status = 'F',
			NEW.Status_inf = '/AutoID-INSERT: Brak nr PESEL lub NrDokumentu '
		;
	END IF;
	
	IF IFNULL(NEW.NrDokumentu, '') = '' THEN
		SET NEW.TypDokumentu = '';
	END IF;
	
	
END;
$$

/* ************************************************************************************************************************************************************************
	AI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_persons_ai`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_persons_ai` AFTER INSERT ON `io_persons` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	/*
	IF IFNULL(@session.trigger.io_persons, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.io_persons = TRUE;
	*/

	IF NEW.Status = 'NN' AND NEW.int_2_autoID = 0 THEN
		INSERT IGNORE INTO `ta_AutoID_osoby`
		(int_2_autoID, ID, OsobaGUID, Nazwisko, Imie1, Imie2, NrPESEL, NrDokumentu, TypDokumentu)
		VALUES
		(0, NEW.ID, NEW.OsobaGUID, NEW.Nazwisko, NEW.Imie1, NEW.Imie2, NEW.NrPESEL, NEW.NrDokumentu, NEW.TypDokumentu)
		ON DUPLICATE KEY
		UPDATE
			Nazwisko = NEW.Nazwisko,
			Imie1 = NEW.Imie1,
			Imie2 = NEW.Imie2,
			NrDokumentu = NEW.NrDokumentu,
			TypDokumentu = NEW.TypDokumentu
		;
	END IF;
	
	/*
	SET @session.trigger.io_persons = FALSE;
	*/
END;
$$

/* ************************************************************************************************************************************************************************
	BU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_persons_bu`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_persons_bu` BEFORE UPDATE ON `io_persons` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	
	/* ******************************************************************************
		TIMESTAMP
	****************************************************************************** */
	-- SET NEW.time_read = CURRENT_TIMESTAMP();
	
	/* ******************************************************************************
		Status
	****************************************************************************** */
	IF NOT(NEW.int_2_autoID <=> OLD.int_2_autoID) THEN
		SET 
			NEW.Status_inf = CONCAT(NEW.Status_inf, IF(NEW.int_2_autoID != 0, '/Przetw.INT ', '/Przetw.AutoID ')),
			NEW.time_read = CURRENT_TIMESTAMP()
		;
	END IF;
	
	IF NOT(NEW.NrDokumentu <=> NEW.NrDokumentu) AND IFNULL(NEW.NrDokumentu, '') = '' THEN
		SET 
			NEW.TypDokumentu = '',
			NEW.time_read = CURRENT_TIMESTAMP()
		;
	END IF;
	
	IF NOT(NEW.Status <=> NEW.Status) AND IFNULL(OLD.Status, '') = 'NN' THEN
		SET 
			NEW.time_read = CURRENT_TIMESTAMP()
		;
	END IF;
	
	/*
	IF IFNULL(@session.trigger.io_persons, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.io_persons = TRUE;
	*/
	
	/*
	SET @session.trigger.io_persons = FALSE;
	*/
END;
$$

/* ************************************************************************************************************************************************************************
	AU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_persons_au`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_persons_au` AFTER UPDATE ON `io_persons` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	/*
	IF IFNULL(@session.trigger.io_persons, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.io_persons = TRUE;
	*/
	
	IF NEW.Status = 'NN' AND OLD.Status != 'NN' AND NEW.int_2_autoID = 0 AND (IFNULL(NEW.NrPESEL, '') != '' OR IFNULL(NEW.NrDokumentu, '') != '') THEN
		INSERT IGNORE INTO `ta_AutoID_osoby`
		(int_2_autoID, ID, OsobaGUID, Nazwisko, Imie1, Imie2, NrPESEL, NrDokumentu, TypDokumentu)
		VALUES
		(0, NEW.ID, NEW.OsobaGUID, NEW.Nazwisko, NEW.Imie1, NEW.Imie2, NEW.NrPESEL, NEW.NrDokumentu, NEW.TypDokumentu)
		ON DUPLICATE KEY
		UPDATE
			Nazwisko = NEW.Nazwisko,
			Imie1 = NEW.Imie1,
			Imie2 = NEW.Imie2,
			NrDokumentu = NEW.NrDokumentu,
			TypDokumentu = NEW.TypDokumentu,
			Status_Int = ''
		;
	END IF;

	
	/*
	SET @session.trigger.io_persons = FALSE;
	*/
END;
$$

/* ************************************************************************************************************************************************************************
	BD
************************************************************************************************************************************************************************ */	


/* ************************************************************************************************************************************************************************
	AD
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_persons_ad`$$
CREATE DEFINER = `root`@`localhost` TRIGGER `io_persons_ad` AFTER DELETE ON `io_persons` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;

	
END;
$$


/* ************************************************************************************************************************************************************************
	Narzędzia 
************************************************************************************************************************************************************************ */	


DELIMITER ;


DELIMITER $$

/* ************************************************************************************************************************************************************************
	BI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_term_events_bi`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_term_events_bi` BEFORE INSERT ON `io_term_events` FOR EACH ROW 
A: BEGIN

	IF _TriggerDisabled() THEN LEAVE A; END IF;

	/* ******************************************************************************
		TIMESTAMP
	****************************************************************************** */
	SET NEW.time_create = CURRENT_TIMESTAMP();
	
	/* ******************************************************************************
		Status
	****************************************************************************** */
	IF NOT (NEW.Tryb IN (1,2,4,5)) THEN
		SET 
			NEW.Status = 'Err',
			NEW.Status_inf = '/Tryb nieistotny'
		;
	END IF;
	
	/* ******************************************************************************
		Terminal
	****************************************************************************** */
	IF NOT EXISTS (SELECT IsGate FROM `io_terminals` WHERE ID = NEW.TID AND IsGate) THEN
		SET 
			NEW.Status = 'Err',
			NEW.Status_inf = CONCAT(NEW.Status_inf, '/Nieodpowiedni terminal')
		;
	END IF;
		
	
	/* ******************************************************************************
		OsobaGUID
	****************************************************************************** */
	IF IFNULL(NEW.OsobaGUID, '') = '' THEN
		SET NEW.OsobaGUID = 
			IFNULL(
			(SELECT p.OsobaGUID FROM `io_persons` p WHERE p.UID = NEW.UID),
			'')
		;
	END IF;
END;
$$

/* ************************************************************************************************************************************************************************
	AI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_term_events_ai`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_term_events_ai` AFTER INSERT ON `io_term_events` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	/*
	IF IFNULL(@session.trigger.io_term_events, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.io_term_events = TRUE;
	*/

	
	/*
	SET @session.trigger.io_term_events = FALSE;
	*/
END;
$$

/* ************************************************************************************************************************************************************************
	BU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_term_events_bu`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_term_events_bu` BEFORE UPDATE ON `io_term_events` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	
	/* ******************************************************************************
		TIMESTAMP
	****************************************************************************** */
	SET NEW.time_read = CURRENT_TIMESTAMP();
	
	/* ******************************************************************************
		Status
	****************************************************************************** */
	IF !(NEW.Tryb <=> OLD.Tryb) THEN
		IF NOT (NEW.Tryb IN (1,2,4,5)) THEN
			SET 
				NEW.Status = 'Err',
				NEW.Status_inf = CONCAT(NEW.Status_inf, '/Tryb nieistotny')
			;
			
		END IF;
	END IF;
	
	/* ******************************************************************************
		Terminal
	****************************************************************************** */
	IF !(NEW.TID <=> OLD.TID) THEN
		IF NOT EXISTS (SELECT IsGate FROM `io_terminals` WHERE ID = NEW.TID AND IsGate) THEN
			SET 
				NEW.Status = 'Err',
				NEW.Status_inf = CONCAT(NEW.Status_inf, '/Nieodpowiedni terminal')
			;
		END IF;
		
	END IF;
	
	/* ******************************************************************************
		OsobaGUID
	****************************************************************************** */
	IF IFNULL(NEW.OsobaGUID, '') = '' THEN
		SET NEW.OsobaGUID = 
			IFNULL(
			(SELECT p.OsobaGUID FROM `io_persons` p WHERE p.UID = NEW.UID),
			'')
		;
	END IF;
	
	
	/*
	IF IFNULL(@session.trigger.io_term_events, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.io_term_events = TRUE;
	*/
	
	/*
	SET @session.trigger.io_term_events = FALSE;
	*/
END;
$$

/* ************************************************************************************************************************************************************************
	AU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_term_events_au`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `io_term_events_au` AFTER UPDATE ON `io_term_events` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
	/*
	IF IFNULL(@session.trigger.io_term_events, FALSE) THEN 
		LEAVE A; 
	END IF;
	SET @session.trigger.io_term_events = TRUE;
	*/

	
	/*
	SET @session.trigger.io_term_events = FALSE;
	*/
END;
$$

/* ************************************************************************************************************************************************************************
	BD
************************************************************************************************************************************************************************ */	


/* ************************************************************************************************************************************************************************
	AD
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `io_term_events_ad`$$
CREATE DEFINER = `root`@`localhost` TRIGGER `io_term_events_ad` AFTER DELETE ON `io_term_events` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;

	
END;
$$


DELIMITER ;

DELIMITER $$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_osoby_AutoID_int` AS
	(
	SELECT
		o.*
	FROM ht.`osoby` o
	)
	;
	
$$

DELIMITER ;


DELIMITER $$

DROP PROCEDURE IF EXISTS `AutoID_CheckBuffOsob_Event`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AutoID_CheckBuffOsob_Event`(
	IN _LockUser VARCHAR(50)
)
/*
	Event do osob
*/
BEGIN
	CALL _Globals_Set("AutoID_CheckBuffOsob_Event/Start", NOW());
	CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('', '', 'opti_int', 60, '');	--	wrzuca prcowników lub działaczy z ht, ht_ship, offshore do bufora Auto_ID
	--	synchr.
	CALL AutoID_CheckBuffOsob('', 0, _LockUser, '');	--	autoID --> Int
	CALL AutoID_CheckBuffOsob('', 1, _LockUser, '');	--	Int --> autoID
	CALL _Globals_Set("AutoID_CheckBuffOsob_Event/Stop", NOW());
END$$

DROP PROCEDURE IF EXISTS `AutoID_CheckBuffTermEvents_Event`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AutoID_CheckBuffTermEvents_Event`(
	IN _LockUser VARCHAR(50)
)
/*
	Event do zdarzen na czytnikach
*/
BEGIN
	CALL Auto_ID_Read_Events('', '');
END$$

DROP PROCEDURE IF EXISTS `AutoID_Check_Osoby_Dzialacze_Int2Auto`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AutoID_Check_Osoby_Dzialacze_Int2Auto`(
	IN _MODE VARCHAR(150),
	IN _BASE VARCHAR(150),
	IN _BASE_IN VARCHAR(150),
	IN _MINUTE INTEGER,
	IN _SQL TEXT
)
/*
	CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('', '', 'opti_int', 10, '');
	CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('', 'ht', _BASE_IN, _MINUTE, _SQLTextINS);
*/
BEGIN
	DECLARE _SQLText, _SQLTextINS TEXT;
	IF _MODE = 'EXECUTE_SUB' THEN
		
		SET _SQLText = _SQL;
		SET _SQLText = REPLACE(_SQLText, '#BASE_IN#', _BASE_IN);
		SET _SQLText = REPLACE(_SQLText, '#BASE#', _BASE);
		SET _SQLText = REPLACE(_SQLText, '#MINUTE#', _MINUTE);
		SET @_SQLText = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
	END IF;
	
	IF _MODE = '' THEN
	--	Pracownicy	
		SET _SQLTextINS =
		"
			INSERT IGNORE INTO #BASE_IN#.`ta_AutoID_osoby`
				(int_2_autoID, ID, OsobaGUID, Nazwisko, Imie1, Imie2, NrPESEL)
			SELECT
				1, o.IDN, o.GUID, o.Nazwisko, o.Imie1, o.Imie2, o.NrPESEL
			FROM ht.`osoby` o
			INNER JOIN #BASE#.`Pracownicy` p ON p.GUID = o.GUID
			WHERE
				IFNULL(o.NrPESEL, '') != ''
				AND
				aLocalTime > DATE_ADD(NOW(), INTERVAL -#MINUTE# MINUTE)
			;
		";
	--	ht
		CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('EXECUTE_SUB', 'ht', _BASE_IN, _MINUTE, _SQLTextINS);
	--	ht_ship
		CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('EXECUTE_SUB', 'ht_ship', _BASE_IN, _MINUTE, _SQLTextINS);
	--	offshore
		CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('EXECUTE_SUB', 'offshore', _BASE_IN, _MINUTE, _SQLTextINS);
		
	--	Dzialacze
		SET _SQLTextINS =
		"
			INSERT IGNORE INTO #BASE_IN#.`ta_AutoID_osoby`
				(int_2_autoID, ID, OsobaGUID, Nazwisko, Imie1, Imie2, NrPESEL)
			SELECT
				1, o.IDN, o.GUID, o.Nazwisko, o.Imie1, o.Imie2, o.NrPESEL
			FROM ht.`osoby` o
			INNER JOIN #BASE#.hr_kontrah_links l ON OsobaGUID = o.GUID AND l.kontekst LIKE '%(KTR)%'
			WHERE
				IFNULL(o.NrPESEL, '') != ''
				AND
				aLocalTime > DATE_ADD(NOW(), INTERVAL -#MINUTE# MINUTE)
			;
		";
	--	ht
		CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('EXECUTE_SUB', 'ht', _BASE_IN, _MINUTE, _SQLTextINS);
	--	ht_ship
		CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('EXECUTE_SUB', 'ht_ship', _BASE_IN, _MINUTE, _SQLTextINS);
	--	offshore
		CALL AutoID_Check_Osoby_Dzialacze_Int2Auto('EXECUTE_SUB', 'offshore', _BASE_IN, _MINUTE, _SQLTextINS);
	END IF;

	
END$$

DROP PROCEDURE IF EXISTS `AutoID_CheckBuffOsob`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AutoID_CheckBuffOsob`(
	IN _MODE VARCHAR(150),
	IN _int_2_autoID BOOLEAN,	
	IN _LockUser VARCHAR(50),	
	IN _Param_A VARCHAR(50)
)
SAutoID_CheckBuffOsob: BEGIN
	/*
	CALL AutoID_CheckBuffOsob('', 0, '', '');	--	autoID --> Int
	CALL AutoID_CheckBuffOsob('', 1, '', '');	--	Int --> autoID
	*/
	
	-- DECLARE _TerminRealizacji DATETIME;
	DECLARE _Rec_Is_Locked BOOLEAN DEFAULT FALSE;
	
	SET _LockUser = IF(_LockUser = '', IFNULL(@session.CurrOsobaGUID, '<AutoID>'), _LockUser);
	
	--	Gdy zablokowane wejscie to exit
	CALL ht._lock_inf_tool('LOCK', 'AutoID_CheckBuffOsob_LOCK', 'AutoID_CheckBuffOsob_LOCK', _LockUser, _Rec_Is_Locked, @R2, '', '');
	-- CALL ht_test2._lock_inf_tool('LOCK', 'AutoID_CheckBuffOsob_LOCK', 'AutoID_CheckBuffOsob_LOCK', _LockUser, _Rec_Is_Locked, @R2, '', '');
	-- UPDATE `_db_globals` SET `value` = NOW() WHERE Key = 'AutoID_CheckBuffOsob_LOCK' AND `value` = '';
	SET @_Rec_Is_Locked = _Rec_Is_Locked;
	IF _Rec_Is_Locked THEN		--	zablokowany?
	
		LEAVE SAutoID_CheckBuffOsob;
	END IF;
	
	DROP TEMPORARY TABLE IF EXISTS `t_AutoID_osoby`;
	CREATE TEMPORARY TABLE `t_AutoID_osoby`
		SELECT
			ta.int_2_autoID,
			ta.ID, 
			ta.Nazwisko,
 			ta.Imie1,
			ta.Imie2,
			ta.OsobaGUID, 
			ta.OsobaGUID AS NewOsobaGUID, 
			ta.NrPESEL,
			ta.NrDokumentu,
			ta.TypDokumentu,
			_VarInt(0) AS Ile_podobienstw
		FROM `ta_AutoID_osoby` ta
		WHERE
			ta.int_2_autoID	= _int_2_autoID 
			AND
			ta.status_int = ''
	;
	ALTER TABLE `t_AutoID_osoby`
		ADD UNIQUE KEY (`ID`, `int_2_autoID`),
		ADD KEY `OsobaGUID` (`OsobaGUID`),
		ADD KEY `int_2_autoID` (`int_2_autoID`),
		ADD KEY `NrPESEL` (`NrPESEL`)
	;	
	
	IF _int_2_autoID THEN 
	--	Int --> autoID
		SET @tr = 'UPDATE `t_AutoID_osoby` t ...';
	ELSE	
	--	autoID --> Int
	--	Ustaw w `io_persons` status na "czytanie" (R)
		UPDATE `io_persons` p, `t_AutoID_osoby` t
		SET
			p.Status = 'R'
		WHERE
			p.ID = t.ID
		;
	END IF;
	-- select * from `t_AutoID_osoby` t;
	--	ZWOLNIJ blokade wejscia
	
	--	Usun przekazane do tempa
	DELETE ta FROM `ta_AutoID_osoby` ta, `t_AutoID_osoby` t WHERE t.ID = ta.ID AND t.int_2_autoID = ta.int_2_autoID;
	CALL ht._lock_inf_tool('UNLOCK', 'AutoID_CheckBuffOsob_LOCK', 'AutoID_CheckBuffOsob_LOCK', 'event', _Rec_Is_Locked, @R2, '', '');
	-- CALL ht_test2._lock_inf_tool('UNLOCK', 'AutoID_CheckBuffOsob_LOCK', 'AutoID_CheckBuffOsob_LOCK', 'event', _Rec_Is_Locked, @R2, '', '');
	
--	####################################################	
--	WYKONAJ zasadnicze dzialania
--	####################################################	
	IF _int_2_autoID THEN 
	--	####################################################	
	--	Int --> autoID
	--	####################################################	
	--	1. Wstaw do io_persons
		UPDATE `io_persons` SET TypDokumentu = '' WHERE IFNULL(NrDokumentu, '') = '' AND TypDokumentu != '';
		SET @tr = 'INSERT INTO `io_persons`';
		INSERT INTO `io_persons`
		(int_2_autoID, OsobaGUID, UID, Nazwisko, Imie1, Imie2, NrPESEL, STATUS)
			SELECT
				1, o.GUID, 0, o.Nazwisko, o.Imie1, o.Imie2, o.NrPESEL, 'N'
			FROM `t_AutoID_osoby` t, `v_osoby_AutoID_int` o
			WHERE
				o.GUID = t.OsobaGUID
		ON DUPLICATE KEY UPDATE
			io_persons.int_2_autoID = 
				IF((`io_persons`.Nazwisko != o.Nazwisko) OR (`io_persons`.Imie1 != o.Imie1) OR (`io_persons`.NrPESEL != o.NrPESEL),
					1,
					io_persons.int_2_autoID
				),	
			STATUS = 
				IF((`io_persons`.Nazwisko != o.Nazwisko) OR (`io_persons`.Imie1 != o.Imie1) OR (`io_persons`.NrPESEL != o.NrPESEL),
					'N',
					STATUS
				),	
			Nazwisko = o.Nazwisko, 
			Imie1 = o.Imie1, 
			Imie2 = o.Imie2,
			NrPESEL = o.NrPESEL
		;
	ELSE
	--	####################################################	
	--	autoID --> Int
	--	####################################################	
		UPDATE `t_AutoID_osoby` t
		SET
			t.OsobaGUID = '',
			t.NewOsobaGUID = ''
		;
		DROP TEMPORARY TABLE IF EXISTS `t_osoby_srch`;
		CREATE TEMPORARY TABLE `t_osoby_srch` 
			SELECT GUID, NrPesel, NrPaszportu, NrSeriaDowoduOsobistego, Nazwisko, Imie1, Imie2 FROM `v_osoby_AutoID_int` o
		;
		ALTER TABLE `t_osoby_srch` 
			ADD INDEX `NrPesel` (`NrPesel`),
			ADD INDEX `NrPaszportu` (`NrPaszportu`),
			ADD INDEX `NrSeriaDowoduOsobistego` (`NrSeriaDowoduOsobistego`),
			ADD INDEX `Nazwisko` (`Nazwisko`),
			ADD INDEX `Imie1` (`Imie1`),
			ADD INDEX `Imie2` (`Imie2`)
		;
		
	--	szukaj nr PESEL	
		UPDATE `t_AutoID_osoby` t
		SET
			t.OsobaGUID = IFNULL(
				(SELECT o.GUID FROM `t_osoby_srch` o WHERE o.NrPESEL = t.NrPESEL LIMIT 1),
				''
			)	
		WHERE
			IFNULL(t.OsobaGUID, '') = ''
			AND
			IFNULL(t.NrPESEL, '') != ''
		;
		
	--	szukaj nr dowodu	
		UPDATE `t_AutoID_osoby` t
		SET
			t.OsobaGUID = IFNULL(
				(SELECT o.GUID FROM `t_osoby_srch` o WHERE o.NrSeriaDowoduOsobistego = t.NrDokumentu LIMIT 1),
				''
			)	
		WHERE
			IFNULL(t.OsobaGUID, '') = ''
			AND
			IFNULL(t.NrDokumentu, '') != ''
			AND
			IFNULL(t.TypDokumentu, '') = 'D'
		;
	--	szukaj nr Paszportug
		UPDATE `t_AutoID_osoby` t
		SET
			t.OsobaGUID = IFNULL(
				(SELECT o.GUID FROM `t_osoby_srch` o WHERE o.NrPaszportu = t.NrDokumentu LIMIT 1),
				''
			)	
		WHERE
			IFNULL(t.OsobaGUID, '') = ''
			AND
			IFNULL(t.NrDokumentu, '') != ''
			AND
			IFNULL(t.TypDokumentu, '') = 'P'
		;
		
	--	Szukaj podobienstw : Nazwisko, imiona i wstaw ich liczbę do `Ile_podobienstw`
		UPDATE `t_AutoID_osoby` t
		SET
			Ile_podobienstw = _varInt(IFNULL((SELECT COUNT(*) FROM `t_osoby_srch` o WHERE o.Nazwisko = t.Nazwisko AND o.Imie1 = t.Imie1), 0))
		WHERE
			TRUE
			AND
			IFNULL(t.OsobaGUID, '') = ''
		;	
		DROP TEMPORARY TABLE IF EXISTS `t_osoby_srch`;
		
	--	Wstaw rekordy osób z podobienstwami spowrotem do `ta_AutoID_osoby` (dla Ile_podobienstw > 0)
		INSERT IGNORE INTO `ta_AutoID_osoby`
			(int_2_autoID, ID, OsobaGUID, Nazwisko, Imie1, Imie2, NrPESEL, NrDokumentu, TypDokumentu, status_int)
		SELECT
			ta.int_2_autoID,
			ta.ID, 
			ta.OsobaGUID, 
			ta.Nazwisko,
 			ta.Imie1,
			ta.Imie2,
			ta.NrPESEL,
			ta.NrDokumentu,
			ta.TypDokumentu,
			IF(IFNULL(ta.OsobaGUID, '') != '', 
				'update', 
				IF(ta.Ile_podobienstw > 0, 
					'?', 
					'create'
				)
			) AS status_int
		FROM `t_AutoID_osoby` ta
		WHERE
			TRUE
		ON DUPLICATE KEY
		UPDATE
			status_int = 
				IF(IFNULL(ta.OsobaGUID, '') != '', 
					'update', 
					IF(ta.Ile_podobienstw > 0, 
						'?', 
						'create'
					)
				)
		;	
		
		-- LEAVE SAutoID_CheckBuffOsob;
		CALL ta_AutoID_osoby_Tools('create', '', '');
		
		/*
	--	Wstaw osoby 	
		INSERT IGNORE INTO `v_osoby_AutoID_int` 
		(GUID, Rodzaj, User_type, Nazwisko, Imie1, Imie2, NrPESEL, NrSeriaDowoduOsobistego, NrPaszportu)
		SELECT
			t.NewOsobaGUID, 1, 0, t.Nazwisko, t.Imie1, t.Imie2, t.NrPESEL, 
			IF(IFNULL(t.TypDokumentu, '') = 'D', NrDokumentu, '') AS NrSeriaDowoduOsobistego,
			IF(IFNULL(t.TypDokumentu, '') = 'P', NrDokumentu, '') AS NrPaszportu
		FROM `t_AutoID_osoby` t
		WHERE
			IFNULL(t.NewOsobaGUID, '') != ''
		;
		
	--	Na koniec Ustaw w `io_persons` status na "zakonczono" (F)
		UPDATE `io_persons` p, `t_AutoID_osoby` t, `v_osoby_AutoID_int` o 
		SET
			p.Status = IF(IFNULL(t.OsobaGUID, '') = '', 'E', 'F'),
			p.OsobaGUID = IF(IFNULL(t.OsobaGUID, '') = '', p.OsobaGUID, t.OsobaGUID),
			p.Nazwisko = o.Nazwisko, 
			p.Imie1 = o.Imie1, 
			p.Imie2 = o.Imie2,
			p.NrPESEL = IF(IFNULL(p.NrPESEL, '') = '', IFNULL(o.NrPESEL, ''), p.NrPESEL)
			
		WHERE
			p.ID = t.ID
			AND
			o.GUID = t.OsobaGUID
		;
		*/
	END IF;
END$$
	
DROP PROCEDURE IF EXISTS `ta_AutoID_osoby_Tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ta_AutoID_osoby_Tools`(
	IN _MODE VARCHAR(150),
	IN _Param_A VARCHAR(50),
	IN _Param_B VARCHAR(50)
)
/*
	CALL ta_AutoID_osoby_Tools('create', '', '');
*/
S_ta_AutoID_osoby_Tools: BEGIN
	IF _MODE = 'Create' THEN
		--	Usun te ktore maja `status_int` = 'delete'
		DELETE ta FROM `ta_AutoID_osoby` ta WHERE ta.status_int = 'delete';
			
	--	Wstaw osoby 	
	--	Ustaw status i nowe GUIDY
		UPDATE `ta_AutoID_osoby` ta
		SET
			ta.OsobaGUID = CONCAT('ID',UUID())		--	czyli IDxxxxx  ID oznacza ze z przepustek
		WHERE
			ta.status_int = 'create'
		;
		INSERT IGNORE INTO `v_osoby_AutoID_int` 
			(GUID, Rodzaj, User_type, Nazwisko, Imie1, Imie2, NrPESEL, NrSeriaDowoduOsobistego, NrPaszportu)
			SELECT
				ta.OsobaGUID, 1, 0, ta.Nazwisko, ta.Imie1, ta.Imie2, ta.NrPESEL, 
				IF(IFNULL(ta.TypDokumentu, '') = 'D', ta.NrDokumentu, '') AS NrSeriaDowoduOsobistego,
				IF(IFNULL(ta.TypDokumentu, '') = 'P', ta.NrDokumentu, '') AS NrPaszportu
			FROM `ta_AutoID_osoby` ta
			WHERE
				ta.status_int = 'create'
		;
	--	Na koniec Ustaw w `io_persons` status na "zakonczono" (F)
		UPDATE `io_persons` p, `ta_AutoID_osoby` ta, `v_osoby_AutoID_int` o 
		SET
			p.Status = IF(IFNULL(ta.OsobaGUID, '') = '', 'E', 'F'),
			p.OsobaGUID = IF(IFNULL(ta.OsobaGUID, '') = '', p.OsobaGUID, ta.OsobaGUID),
			p.Nazwisko = o.Nazwisko, 
			p.Imie1 = o.Imie1, 
			p.Imie2 = o.Imie2,
			p.NrPESEL = IF(IFNULL(p.NrPESEL, '') = '' OR ta.status_int = 'update_int', IFNULL(o.NrPESEL, ''), p.NrPESEL)
			
		WHERE
			ta.status_int IN ('update', 'update_int', 'create')
			AND
			p.ID = ta.ID
			AND
			o.GUID = ta.OsobaGUID
		;
		DELETE ta FROM `ta_AutoID_osoby` ta WHERE ta.status_int IN ('update', 'update_int', 'create');
		
	END IF;
END$$

DROP PROCEDURE IF EXISTS `ta_AutoID_osoby_Verify`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ta_AutoID_osoby_Verify`(
	IN _MODE VARCHAR(150),
	IN _ID VARCHAR(50),
	IN _OsobaGUID VARCHAR(50),
	IN _Param_A VARCHAR(50)
)
/*
	CALL ta_AutoID_osoby_Verify('prepare', '', '', '');
	Select osoby do weryfikacji
		CALL ta_AutoID_osoby_Verify('select', '', '', '');
	Jeśli wybór osoby/co zrobić
		CALL ta_AutoID_osoby_Verify('select.osoby', '89', '', '');
	
	Ustaw osobę/Status
		CALL ta_AutoID_osoby_Verify('Update.one.rec', '89', '?', '');
		CALL ta_AutoID_osoby_Verify('Update.one.rec', '89', 'create', '');
		CALL ta_AutoID_osoby_Verify('Update.one.rec', '89', '{CF251D1A-3FAB-45FD-98CF-41F32359DA5F}', '');
	
	Zapisanie w docelowej tabeli!
		CALL ta_AutoID_osoby_Verify('After.Apply', '', '', '');
*/
S_ta_AutoID_osoby_Verify: BEGIN
	IF _MODE = 'prepare' THEN
		DROP TEMPORARY TABLE IF EXISTS `t_ta_AutoID_osoby`;
		CREATE TEMPORARY TABLE `t_ta_AutoID_osoby`
			SELECT 
				t.* 
			FROM `ta_AutoID_osoby` t
			WHERE
				status_int = '?'
		;
		
	END IF;
	
	IF _MODE = 'select' THEN
		SELECT 
			tt.* 
		FROM `t_ta_AutoID_osoby` tt
		;	
	END IF;
	
	
	IF _MODE = 'Update.one.rec' THEN
		UPDATE `t_ta_AutoID_osoby` tt
		SET
			tt.status_int = 
				IF(_OsobaGUID IN ('?', 'create'),
					_OsobaGUID,
					'update_int'
				),	
			tt.OsobaGUID = 
				IF(_OsobaGUID IN ('?', 'create'),
					'',
					_OsobaGUID
				)	
		WHERE
			ID = _ID
		;
	END IF;
	
	IF _MODE = 'After.Apply' THEN
		UPDATE `t_ta_AutoID_osoby` tt
		SET
			tt.status_int = IF(tt.status_int = 'update_int' AND tt.OsobaGUID = '', '?', tt.status_int)
		;
		UPDATE `ta_AutoID_osoby` t, `t_ta_AutoID_osoby` tt
		SET
			t.status_int = tt.status_int
		WHERE
			tt.status_int != '?'
			AND
			tt.ID = t.ID
		;
	END IF;
	
	IF _MODE = 'select.osoby' THEN
		SELECT 
			o.GUID AS OsobaGUID,
			_VarStr_NotNULL(CONCAT(o.Nazwisko, ' ', o.Imiona)) AS Opis,
			_VarStr_NotNULL(tt.NrPESEL) AS NrPESEL_HTC,
			IF(IFNULL(tt.TypDokumentu, '') = 'D', tt.NrDokumentu, '') AS NrSeriaDowoduOsobistego_HTC,
			IF(IFNULL(tt.TypDokumentu, '') = 'P', tt.NrDokumentu, '') AS NrPaszportu_HTC,
			_VarStr_NotNULL(o.NrPESEL) AS NrPESEL, o.NrNip,	o.NrSeriaDowoduOsobistego, o.NrPaszportu,
			o.ImieOjca, o.ImieMatki,
			IFNULL(o.DataUrodzenia, '') AS Data_urodzenia, 
			CONCAT(
				IF(o.DataUrodzenia IS NULL, 
					'-',
					IF(YEAR(o.DataUrodzenia) < 2000,
						IF(SUBSTR(tt.NrPESEL, 1, 6) = @6c := CONCAT(YEAR(o.DataUrodzenia) - 1900, LPAD(MONTH(o.DataUrodzenia), 2, '0'), LPAD(DAY(o.DataUrodzenia), 2, '0')),
							'Data urodzenia zgodna z PESELEM Karty HTC i bazie',
							IF(SUBSTR(o.NrPESEL, 1, 6) = @6c, 
								'Data urodzenia zgodna tylko z PESELEM w bazie',
								'Data urodzenia niezgodna!!!'
							)	
						),
						''	
					)	
				),
				IF(LENGTH(tt.NrPESEL) != 11, ' - za malo/duzo cyfr PESEL', '')	
			) AS Info2,
			_ConcatStr(
				_ConcatStr(
					i.info, 
					'', -- ht_ost_zlec,
					'/ ost. zlec.:'
				),	
				IF(Last_log > '0000-00-00 00:00:00', Last_log, ''),
				'/ ost. logow.:'
			) AS Info
		FROM `t_ta_AutoID_osoby` tt
		LEFT JOIN ht.`osoby` o ON o.Nazwisko = tt.Nazwisko AND tt.Imie1 LIKE CONCAT('%', o.Imie1, '%')
		LEFT JOIN narzedziownia.ta_ht_osoby_info i ON i.GUID = o.GUID
		-- ORDER BY LP
		WHERE
			ID = _ID
		/*
BYŁO		
		UNION
			
		SELECT 
			_VarStr('create') AS OsobaGUID,
			_VarStr('K r e u j   n o w ą   o s o b ę') AS Opis,
			_VarStr('') AS NrPESEL_HTC,
			_VarStr('') AS NrSeriaDowoduOsobistego_HTC,
			_VarStr('') AS NrPaszportu_HTC,
			_VarStr('') AS NrPESEL, _VarStr('') AS NrNip, _VarStr('') AS NrSeriaDowoduOsobistego, _VarStr('') AS NrPaszportu,
			_VarStr('') AS ImieOjca, _VarStr('') AS ImieMatki,
			_VarStr('') AS Data_urodzenia, 
			_VarStr('') AS Info2,
			_VarStr('') AS Info
		UNION
			
		SELECT 
			_VarStr('?') AS OsobaGUID,
			_VarStr('A n u l u j   z m i a n y') AS Opis,
			_VarStr('') AS NrPESEL_HTC,
			_VarStr('') AS NrSeriaDowoduOsobistego_HTC,
			_VarStr('') AS NrPaszportu_HTC,
			_VarStr('') AS NrPESEL, _VarStr('') AS NrNip, _VarStr('') AS NrSeriaDowoduOsobistego, _VarStr('') AS NrPaszportu,
			_VarStr('') AS ImieOjca, _VarStr('') AS ImieMatki,
			_VarStr('') AS Data_urodzenia, 
			_VarStr('') AS Info2,
			_VarStr('') AS Info
		*/	
		ORDER BY OsobaGUID, Opis	
		;	
	END IF;
	
END$$

/* ************************************************************************************************************************************************************************
	AutoID z bazy opti_int tabeli term_events do `erp_pass_we_wy`
************************************************************************************************************************************************************************ */	
DROP PROCEDURE IF EXISTS `Auto_ID_Read_Events`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Auto_ID_Read_Events`(
	IN _MODE VARCHAR(250),
	IN _Param_A VARCHAR(255)
)
	/*
	Wołana co dT aby z bazy opti_int tabeli term_events do `erp_pass_we_wy`
	CALL Auto_ID_Read_Events('', '');
	*/
S_Auto_ID_Read_Events: BEGIN 
	
	IF IFNULL(@session.Auto_ID_Read_Events, FALSE) THEN
		LEAVE S_Auto_ID_Read_Events;
	END IF;
	SET @session.Auto_ID_Read_Events = TRUE;
	
	IF EXISTS(SELECT * FROM `io_term_events` WHERE STATUS = 'N') THEN
	--	Najpierw Ustaw na F te ktore nie sa w trybie nas interesujacym 1,2,4,5
		UPDATE `io_term_events`
		SET
			`Status` = 'F'
		WHERE 
			NOT (Tryb IN (1,2,4,5))	--	 Podstawowe tryby: 1 to jest F1 (wejscie) 2 to jest F2 (wyjscie) 3 to jest DOSTP (dla trybu KD) 4 to jest F3 (wyjcie służbowe) 5 to jest F4 (powrót) Pozostałe osigalne po odpowiedniej konfiguracji terminala
		;
		
		UPDATE `io_term_events` e, `io_persons` p
		SET
			e.OsobaGUID = p.OsobaGUID,
			e.KartaNr = IF(IFNULL(e.KartaNr, '') = '', p.OsobaGUID, e.KartaNr)	--	Gdy kolumna KartaNr pusta to niech numerem będzie GUID osoby
		WHERE 
			e.UID = p.UID
			AND
			e.Status = 'N'
			-- AND	IFNULL(e.KartaNr, '') = ''
		;
		INSERT IGNORE INTO ht.`hr_cards`
		(ID, Kind, OsobaGUID)
			SELECT
				DISTINCT 
				e.KartaNr,
				'PASS',
				e.OsobaGUID
			FROM `io_term_events` e
			WHERE
				e.Status = 'N'
				AND
				IFNULL(e.KartaNr, '') != ''
				AND
				IFNULL(e.OsobaGUID, '') != ''
		;
		INSERT IGNORE INTO ht.`hr_cards`
		(ID, Kind, OsobaGUID)
			SELECT
				DISTINCT 
				e.UID,
				'PASS',
				e.OsobaGUID
			FROM `io_term_events` e
			WHERE
				e.Status = 'N'
				AND
				IFNULL(e.OsobaGUID, '') != ''
		;
		
		DROP TEMPORARY TABLE IF EXISTS `tterm_events`;		-- jmam
		CREATE TEMPORARY TABLE `tterm_events`
			SELECT 
				e.*
			FROM `io_term_events` e
			WHERE 
				e.Status = 'N'
				AND
				IFNULL(e.KartaNr, '') != ''
		;
		ALTER TABLE `tterm_events` ADD PRIMARY KEY ID(ID), ADD KEY TID(TID);
		
		UPDATE `io_term_events` e, `tterm_events` t
		SET
			e.Status = 'R'
		WHERE 
			t.ID = e.ID
			AND
			e.Status = 'N'
		;
		
		BEGIN
			DECLARE _id INTEGER;
			DECLARE _DzienCzas DATETIME;
			DECLARE _Dzien DATE;
			DECLARE _Czas TIME;
			DECLARE _UID INTEGER;
			DECLARE _KartaNr VARCHAR(38);
			DECLARE _OsobaGUID VARCHAR(38);
			DECLARE _GUID VARCHAR(38);
			DECLARE _TID INTEGER;
			DECLARE _Tryb INTEGER;
			DECLARE _Status INTEGER;
			-- DECLARE _cmd TEXT;
			DECLARE _error BOOL DEFAULT FALSE;
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE cterm_events CURSOR FOR
				SELECT 	
					e.ID, 
					_varDateTime(CONCAT(e.Dzien, ' ', e.Czas)) AS DzienCzas,
					e.Dzien, 
					e.Czas, 
					e.KartaNr,
					e.UID, 
					p.OsobaGUID, 
					e.GUID, 
					e.TID, 
					e.Tryb, 
					e.STATUS
				FROM `tterm_events` e
				INNER JOIN `io_persons` p ON p.UID = e.UID AND p.Status = 'F'
				ORDER BY DzienCzas
			;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN cterm_events;
			loop1: LOOP
				FETCH cterm_events INTO 
					_id,
					_DzienCzas,
					_Dzien,
					_Czas,
					_KartaNr,
					_UID,
					_OsobaGUID,
					_GUID,
					_TID,
					_Tryb,
					_Status
				;
				IF done THEN
					LEAVE loop1;
				END IF;
			--	Wykonaj zapis w 
			/*
				CALL pass_Insert_We_Wy('', 0, '2012-06/113', 0, '', '', '', '', '', '');
				CALL pass_Insert_We_Wy('', 0, '7655293', 0, '', '', '', '', '', '');

				CALL pass_Insert_We_Wy('', 0, '2012-06/113', 1, '', '', '', '', '', '');
				CALL pass_Insert_We_Wy('', 0, '7655293', 1, '', '', '', '', '', '');
				
				CALL pass_Insert_We_Wy('', 0, '2012-06/113', 1, '2012-07-04 13:30:00', '', '', '', '', '');
				CALL pass_Insert_We_Wy('', 0, '7655293', 1, '2012-07-04 13:30:00', '', '', '', '', '');
			*/	
	SET @X = CONCAT('CALL ht.pass_Insert_We_Wy() ', _id);
				CALL ht.pass_Insert_We_Wy('', 0, _KartaNr, IF(_Tryb IN (1,5), 0, 1), _DzienCzas, '', '', '', '', '');
			--	Wpisz F że zakończono przetwarzanie	
	SET @X = CONCAT('UPDATE `io_term_events` ', _id);
				UPDATE `io_term_events`
				SET
					STATUS = 'F'
				WHERE 
					`id` = _id
				;
			END LOOP;
			CLOSE cterm_events;
			
			-- jmam	
			-- DROP TEMPORARY TABLE IF EXISTS `tterm_events`;
		END;
	END IF;
	
	/* ***
	DELETE FROM `io_term_events` 
	WHERE 
		STATUS = 'F'
		AND
		_varDateTime(CONCAT(Dzien, ' ', Czas)) > DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
	;
	**** */
	SET @session.Auto_ID_Read_Events = FALSE;
	
END$$


DELIMITER ;


DELIMITER $$

DROP EVENT IF EXISTS `DoAutoID_CheckBuffOsob`$$
CREATE EVENT `DoAutoID_CheckBuffOsob`
	ON SCHEDULE EVERY 15 MINUTE 
	DO
	CALL AutoID_CheckBuffOsob_Event('event')
;	
$$
DROP EVENT IF EXISTS `DoAutoID_CheckBuffTermEvents`$$
CREATE EVENT `DoAutoID_CheckBuffTermEvents`
	ON SCHEDULE EVERY 15 MINUTE 
	DO
	CALL AutoID_CheckBuffTermEvents_Event('event')
;	
$$

DELIMITER ;

SET GLOBAL event_scheduler = ON;

